SlimServerDatabaseStructure
From SqueezeboxWiki
Contents |
SlimServer database structure
Connecting to the proper MySql instance
SqueezeCenter uses MySql, and will install and set up an instance of the MySql daemon by default. You can, if you wish, have SqueezeCenter use an existing MySql instance, which you may have previously installed.
default access
mysql -P9092 -h127.0.0.1 slimserver
The exact specification is controled by the 'server.pref's file, which on most Linux systems is located in the /var/lib/squeezecenter/prefs directory. So the full file path is /var/lib/squeezecenter/prefs/server.prefs
The applicable lines are typically:
dbsource: dbi:mysql:hostname=127.0.0.1;port=9092;database=%s dbusername: slimserver
So far, it's not clear what the name of the database schema is, or how it is specified, but it seems to be hardcoded to the value "slimserver".
Main tables
albums
The albums table contains all real albums. Besides this it also contains a "No Album" entry which represents tracks that don't belong to any album. The name of the "No Album" entry will differ depending on the selected language in SlimServer. The tracks that doesn't belong to any real albums will be related to the "No Album" entry.
contributors
The contributors table contains all composers, conductors, artists, album artists, bands and track artists that have contributed to the music on any track in your library.
The contributors table also contains a special entry named "Various Artists" that represents all the artists that exist on a compilation album. Note that all the individual artists on compilation albums also exist as separate entries in the contributors table. There is no relation in the database between the "Various Artists" entry and the tracks and albums tables.
tracks
The tracks table contains all tracks in your music library. These have the "audio" column set to 1 and the "remote" column set to 0. The tracks table also contains an entry for all Internet radio stations you have listened to since the last rescan. The Internet radio station entries have the "remote" column set to 1.
The tracks table also contains some entries that aren't really separate songs. There will be one entry for each directory in your library; these entries will have "audio" set to 0 and "content_type" set to "dir". There will be one entry for each playlist, which will also have "audio" set to 0 and content_type set to the type of playlist. Note that the current playlist is also represented this way even though it doesn't exist as a physical m3u file on the disk. The current playlist has "content_type" set to "cpl".
- Some interesting columns in tracks table (these are not all columns, just the most interesting ones)
- title - The track title
- titlesort - The version of the track title that's used for sorting
- url - The url to the track
- Typically something like: file:///mnt/music/First%20Album/01%20First%20Song.flac
- Is url encoded, can be unencoded to a path with http://urldecode.org
- audio - Indicates if the entry represents a music file
- 1 - music file
- null - not music file
- content_type - The type of entry, see above, it typically either indicates file format or playlist format
- tracknum - The track number on an album
- timestamp - The last modification time of the music file
- filesize - The size of the file in bytes
- year - Relation to years table
- secs - The length of the track in number of seconds
- bitrate - The bit rate of the track, for example 457489 for a 457kbs track
- samplerate - The sample rate of the track, for example 44100
- samplesize - The sample size of the track, for example 16
- channels - Number of audio channels used by the track
- bpm - The bpm of the track, not filled unless you have tagged your music with BPM tags
- disc - The disc number this track is available on
- remote - 1 if this isn't a local track but a remote stream, for example an internet radio station
- lossless - 1 if this is a lossless compressed track, else 0
- lyrics - The lyrics of the track, not filled unless you have tagged your music with lyrics information
- album - Relation to albums table
tracks_persistent
The tracks_persistent table contains additional statistics information about a track, such as play count, rating and last played time. This table will survive a full rescan as long as you have musicbrainz tags or haven't moved or renamed a music file. During scanning the entries in the tracks table will be re-connected to the saved entries in the tracks_persistent table.
The tracks_persistent table was added in SqueezeCenter 7.1.
genres
The genres table contains all the genres in your music library.
years
The years table contains an entry for each year specified in a track tag.
comments
The comments table contains all the comment tags in the tracks in your library. There will be one entry for each comment tag. If you have specified several comment tags in a single track, there will be a separate entry for each comment.
Many to many associations
contributor_track
The contributor_track table contains the relation between a track and a contributor. Each entry also has a role attribute indicating the role the contributor had. Some examples of existing roles are:
- Artist -> 1
- Composer -> 2
- Conductor -> 3
- Band -> 4
- Album artist -> 5
- Track artist -> 6
Contributors and tracks are joined with:
contributor_track.contributor = contributors.id and contributor_track.track = tracks.id
contributor_album
The contributor_album table contains the relation between an album and a contributor. Like the contributor_track table, the contributor_album table also contains a role attribute indicating the role the contributor had on one or several tracks on an album.
The contributor_album table is just a shortcut table to get better performance when browsing from artists to albums. All the information already exists in the contributor_track table but is compiled together to a summary view in the contributor_album table.
Contributors and albums are joined with:
contributor_album.album = albums.id and contributor_album.contributor = contributors.id
genre_track
The genre_track table contains the relation between genres and tracks. This is the table to use if you need to know which genres a track belongs to, or which tracks exist within a specific genre.
Genres and tracks are joined with:
genre_track.genre = genres.id and genre_track.track = tracks.id
playlist_track
The playlist_track table contains the relation between playlists and tracks. This table is used if you need to know the tracks in a specific playlist.
Playlists and tracks these are joined with:
playlist_track.playlist = tracks.id and playlist_track.track = tracks.url
Many to one associations
Relation between track and album
There is no separate relation table for the relation between albums and tracks; instead this relation is represented with the "album" column in the tracks table.
Albums and tracks are joined with:
tracks.album = albums.id
Relation between album and year
There is no separate relation table for the relation between years and albums; instead this relation is represented with the "year" column in the albums table.
Years and albums are joined with:
albums.year = years.id
Relation between track and year
There is no separate relation table for the relation between years and tracks; instead this relation is represented with the "year" column in the tracks table.
Years and tracks are joined with:
tracks.year = years.id
Relation between track and comment
There is no separate relation table for the relation between tracks and comments; instead this relation is represented with the "track" column in the comments table.
Tracks and comments are joined with:
comments.track = tracks.id
Relation between album and main artist
There is a special relation between albums and main artists. Note that this relation can be a bit random on compilation albums.
Albums and main artists are joined with:
albums.contributor = contributors.id
Extra tables
There are a number of extra table in the database that contain some different kinds of state information. These tables follow below and they do not contain any music library information; they are just needed to make SlimServer work.
metainformation
Contains some different meta information about the database, for example the last time a rescan was performed, and an indication whether scanning is currently in progress.
dbix_migration
Contains the current database structure version. This is used to determine whether the database structure needs to be updated when upgrading to a new SlimServer version.
progress
New in SlimServer 7.0 (i.e. SqueezeCenter). Contains the progress of the currently active scanning process. This is used for the progress bar during the scanning operation.
unreadable_tracks
Is this used somewhere?
rescans
Is this used somewhere?
pluginversion
Is this used somehwere?
Database creation
The SlimServer database is created automatically at first SlimServer startup. The database is created with the schema_*_up.sql scripts in the SQL/mysql directory below the SlimServer installation directory. The scripts will run in sequence, starting with the script with a number greater than the current database structure version.
As an example: if the current database version is 3, the schema_4_up.sql and schema_5_up.sql scripts will be executed. If the database doesn't exist it will execute all the *_up.sql scripts.
Sample queries
The following sections lists a number of different sample SQL statements that retrieve different kinds of information from the database. Please note that the corresponding statements used directly by SlimServer might be different than the statements shown below; these are just samples.
Get artists in the database
The simple way to retrieve all contributors independent of roles in the database is a query like this
select * from contributors
order by namesort
However, you should be aware of that if you have removed, re-tagged or renamed some track in your library and used the rescanning option "New and changed files", there could be artists in the result that no longer have any tracks in the SlimServer database. The above query will also return the special "Various Artists" entry that doesn't correspond to a single artist.
A query like this will solve this problem:
select contributors.* from contributors, contributor_album
where
contributors.id = contributor_album.contributor
group by contributors.id
order by contributors.namesort
If you only wanted the contributors that have the role "artist", "album artist" or "track artist", you would instead use a query like:
select contributors.* from contributors, contributor_album
where
contributors.id = contributor_album.contributor and
contributor_album.role in (1, 5, 6)
group by contributors.id
order by contributors.namesort
In the same way you can get all the composers with a query like:
select contributors.* from contributors, contributor_album
where
contributors.id = contributor_album.contributor and
contributor_album.role = 2
group by contributors.id
order by contributors.namesort
If you want to ignore artists that only exist on compilation albums, you would use something like:
select contributors.* from contributors, contributor_album, albums
where
contributors.id = contributor_album.contributor and
contributor_album.role in (1,5,6) and
contributor_album.album = albums.id and
albums.compilation is null
group by contributors.id
order by contributors.namesort
If you only want to list only those artists that exist on compilation albums, you would use something like:
select contributors.* from contributors, contributor_album, albums
where
contributors.id = contributor_album.contributor and
contributor_album.role in (1,5,6) and
contributor_album.album = albums.id and
albums.compilation = 1
group by contributors.id
order by contributors.namesort
Get albums in the database
The easiest way to get all albums in the database is with a query like this:
select * from albums
order by titlesort
However, you should be aware that if you have removed, re-tagged or renamed some track in your library and used the rescanning option "New and changed files", there could be albums in the result that no longer have any tracks in the SlimServer database. The query will also return the special "No Artist" entry that doesn't correspond to a single artist.
The following query only returns the albums that really have tracks in the database:
select albums.* from albums, tracks
where
tracks.album = albums.id and
tracks.audio = 1
group by albums.id
order by albums.titlesort
If you want to ignore the compilation albums, you would instead use something like this:
select albums.* from albums, tracks
where
tracks.album = albums.id and
tracks.audio = 1 and
albums.compilation is null
group by albums.id
order by albums.titlesort
Or if you only want the compilation albums:
select albums.* from albums, tracks
where
tracks.album = albums.id and
tracks.audio = 1 and
albums.compilation = 1
group by albums.id
order by albums.titlesort
To retrieve all albums within the genres 'Pop' or 'Rock' you would use a query like this:
select albums.* from albums, tracks, genre_track, genres
where
tracks.album = albums.id and
tracks.id = genre_track.track and
genre_track.genre = genres.id and
genres.name in ('Pop','Rock') and
tracks.audio = 1
group by albums.id
order by albums.titlesort
To retrieve all albums from year 2000 and forward you would use a query like:
select * from albums
where
year >= 2000
order by titlesort
Ordering albums retreived from the database
For queries that return albums it also gets interesting to order the result in various ways.
All albums ordered by the album title sort tag and then disc number:
select * from albums
order by titlesort, disc
All albums ordered by the year in descending order:
select * from albums
order by year desc, titlesort, disc
All albums ordered by main artist and then year in descending order:
select albums.* from albums
left join contributors on
albums.contributor = contributors.id
group by albums.id
order by contributors.namesort, albums.year desc, albums.disc
The reason we need to use the "left join" syntax in the above statement is that there can be albums without a main artist.
Get tracks in the database
To get all locally stored tracks in the database you can use a query like this:
select * from tracks
where
audio = 1
order by titlesort
The above query returns the tracks ordered by the titlesort field; you would probably also want the tracks ordered by album. To accomplish this you would instead use something like:
select tracks.* from tracks, albums
where
tracks.album = albums.id and
tracks.audio = 1
group by tracks.id
order by albums.titlesort, tracks.disc, tracks.tracknum
Or maybe even order the albums by the main artist with something like:
select tracks.* from tracks
join albums on
tracks.album = albums.id
left join contributors on
albums.contributor = contributors.id
where
tracks.audio = 1
group by tracks.id
order by contributors.namesort, albums.titlesort, tracks.disc, tracks.tracknum
