Music managers deal with lots of data and metadata relating to one or more music libraries they manage.
Let’s take a look at the possible ways in which they might go about doing that.
For this purpose, I’ll look at the databases used by three music players, Amarok, Clementine and Rhythmbox.
Amarok, in its 1.x days, used to offer a choice between SQLite, MySQL, or PostgreSQL as the database backend.
In its 2.x form, these options have been whittled down to just MySQL.
Clementine, its cousin-by-fork, also whittled down its options, but selected SQLite, instead.
Rhythmbox is unrelated to the two projects and uses an XML data store.
I regularly use Amarok on my desktop, and Clementine on my laptop.
I have briefly used Rhythmbox on my laptop in the past, but quickly abandoned it for Clementine.
I’ll first begin with a description of Amarok’s database, then move on to the relatively simpler schema used by Clementine and Rhythmbox.
What a MySQL database looks like
MySQL is one of the programs that can be used to manage something called a relational database.
Relational databases are comprised of many tables/relations.
Each row/tuple in a relation/table represents a relationship between the columns/attributes of the relation.
Relational databases may be comprised of several tables, each expressing a new relation between the various attributes.
Each tuple in a relation must either have a unique attribute or set of attributes, called a key to identify it uniquely.
Finally, the SQL in the name refers to the Structured Query Language, a way to formulate all interactions (create/retrieve/update/delete) with the database management system.
MySQL uses a server process to guard access to the databases it manages.
The server operates on a “data directory” where each database lives in a sub-directory.
The MySQL server may have users and permissions defined, limiting or granting access to various databases in granular detail.
To manage each database, MySQL allows the database to specify its storage engine.
The storage engine is what processes the SQL queries and organizes how the data is stored and retrieved.
Depending on the storage engine, the contents of the database sub-directory can look quite different.
The default, InnoDB storage engine creates just a .frm file, one for each table, containing the structure and the data stored in that table.
The MyISAM storage engine, creates a .frm file to store the structure of the table, a .MYI file to store the indices of that table and a .MYD file to store the actual data.
There are other storage engines, but that goes a bit beyond the scope of this post.
What an Amarok database looks like
Knowing the above, let’s take a look at how Amarok stores its internal data in ~/.kde4/share/apps/amarok/mysqle/.
The file system looks familiar and contains a single database called amarok, composed of several tables.
Right away, we can tell that this is a MySQL database and probably uses the MyISAM storage engine.
In order to connect to this database, we need to launch a MySQL (mariaDB, I mean) server and connect through a MySQL/mariaDB client.
The config options for the server are helpfully recorded in a my.cnf file in the parent directory.
While it’s probably quite safe to connect to the live database, I’d rather not chance any corruption of the data.
Let’s make a copy of this data directory and connect to that instead.
Technically, I don’t really need the my.cnf file and can just start a server with default options, but I’ll try to play it by the book here.
Edit the my.cnf file so that the datadir option now points to the current location of the mysqle directory.
Now, start the MySQL/mariaDB server using the mysqld binary.
Avoid using relative paths in the command as different options seem to consider paths relative to different directories.
Using absolute paths makes each path unambiguously clear.
Some explanation of the options used is in order.
The --default-storage-engine option sets the storage engine to MyISAM.
This matches the contents of the my.cnf file.
The --datadir option sets the data directory to the new location of the mysqle directory.
Next, we specify a socket file, a pseudo-file used to communicate back and forth with the server process.
Next, we specify a pid file, a text file which will contain the process ID of the server.
It comes in handy when stopping the server—simply kill $(cat pidfile).
Now, we are down to the final two options.
The first, --skip-grant-tables, stops the server from bothering with enforcing permissions on various tables.
With this option on, anyone connecting to this server can do anything to any table.
This is considered a rather unsafe way of running things in production, but suits our purposes.
The last option, --skip-networking should always accompany --skip-grant-tables as matter of best practices as it prevents any connections from outside of localhost while the server is run in this vulnerable state.
Now, we can connect to this server using a mariaDB client, the mysql binary, and begin our dive.
What an Amarok database really looks like
The Amarok database is comprised of about a dozen tables.
I’ll present a guided tour through the information stored in this database.
Let’s start by walking through the devices table.
The devices table stores a unique ID for each device mount point ever seen by Amarok.
This includes USB drives, NFS shares, etc. and contains a lot of old, obsolete data—forgotten hard disks, old USB drives, things like that.
Next, knowing the mount point of each device, let’s look into the directories table.
This table stores the path to each directory where a playable music file was found.
The path stored is relative to the mount point and the mount point is referenced by the ID from the devices table.
Like the devices table, this table also has accumulated a lot of obsolete data and contains directories from devices that no longer exist.
Amarok seems to want to never forget old mount points, just in case they reappear.
While that does lead to a pleasant user experience (plug in an old hard drive and ratings, etc. are preserved and ready), it leads to quite a bit of cruft building up in the database.
Perhaps a middle path may be to define a duration of time after which entries are automatically retired, then deleted from the database.
Lastly, the directories table also records the last time point at which the directory was changed.
I don’t know why for certain; it may be to avoid scanning directories whose timestamps haven’t changed.
Next, the database records the actual paths to the songs in the directories above.
The path is cross-referenced by the device ID and the directory ID, but oddly, the relative path is remembered from the device mount point, not the directory.
This leads to redundant data, which could have been avoided.
This table also records a sort of checksum/hash for each URL, a uniqueid.
Amarok uses this uniqueid to identify a song, even if its location changes.
Just like a checksum, this uniqueid has its limits; if a song is moved, and its tags are edited, the checksum does not match anymore and the song cannot be identified anymore.
Now, we get to the metadata embedded in the file at the URL.
Amarok reads and records several components of the metadata embedded in the file and indexes many of them by uniqueness in their own tables.
Note that YEAR is indexed—metadata for the YEAR or DATE field is usually just a UTF-8 text field and beyond a recommendation that the format adhere to the ISO 8601 standard, there are no limitations on what this field can contain.
Now, this schema has an inherent assumption that is not necessarily true.
It assumes that all these fields should have unique values for a given song.
This doesn’t hold for at least four of the attributes,
Artist - Multiple artists may have collaborated on this song.
Album - The song may have appeared on multiple albums and re-releases.
Genre - The song may be considered as belonging to multiple genres.
Composer - Multiple composers may have collaborated on this song.
In my opinion, these four should not appear in this table.
This is a simple table, just listing the textual representation of each artist against a unique key.
A slightly more complex table, containing not just the album’s relationship to the artist, but also to the album artwork.
Here again, I have some issues.
Neither the artist, nor the album artwork can be considered unique.
In case of the artist, while there can be a designated album artist in the embedded tags, it’s entirely possible that several artists collaborated on this album.
Similarly, there can be multiple images associated with this album.
Hence, I think this table should just be a key and a value, like the artists table.
Like the artists table, this is a simple table of key-value pairs.
Again, this is just a simple key-value pairs table.
One odd thing with this table is that even though the schema clearly sets NULL as the default value for a missing composer, Amarok seems to never use this default.
Instead, a missing composer is mapped to an empty string.
Once again, this is a simple table containing key-value pairs.
This table indexes images available for album artwork by a unique ID.
The image could be found embedded in a track, in which case the path to the image simply contains the uniqueid from the urls table.
Otherwise, an absolute path to an image file is stored, for cases when the image is fetched from the internet, etc.
Like many of the other tables, this one’s also filled with non-existent paths from invalid devices.
Images are associated to a track through its album.
Labels and URLs_Labels
Labels are user-applied tags, called so to disambiguate them from the embedded tags already in the song files.
Labels are also fetched from last.fm and displayed when a song plays.
However, unless you selected the option to save the labels (look in the options for the labels widget in the context view), they are not saved.
The first table, labels, stores each individual label along with a unique id.
The second, urls_labels table contains multiple mappings between track urls and label ids.
In this way, unique pairs of track urls and label ids are saved.
In my library, I haven’t tagged/labeled any songs in this way, so my tables are empty.
Unlike tags, lyrics do seem to be saved after being fetched from the internet when a song is played.
Also, they are stored with the url id of the song serving as the key.
I saved the best for the last.
This table contains the juicy listening statistics associated with each URL.
Although there is a column for marking a record as deleted, it does not seem to be used.
Recording the create date seems to be redundant as this is already stored in the tracks table (curiously, some of the tracks have a NULL creation date; others have a different creation date in the tracks table).
However, the access date is useful, it marks the last time this track was played.
In addition to that, this table records the rating and playcount and the score as of the last play.
I wish this table also recorded the skip count, the number of times the song was played, but did not complete.
I also wish that instead of just recording the last access time here, Amarok would record all access times in a separate table, indexed by track url or unique id.
Having access to listening history locally can open the door to all sorts of data analysis.
Briefly, here is the description of the contents of the other tables.
This table contains the schema versions for some of the tables.
Each time the schema for any of the tables is updated, the version number should be bumped here.
Bookmarks and Bookmark groups
The groups table contains the types of bookmarks stored (position markers, playback ended markers, etc) and the bookmarks table contains the actual bookmarks themselves.
I don’t really use this feature, so I have none saved (apart from some playback ended markers that Amarok seems to have saved for a few tracks).
Playlist tracks, Playlists and Playlist groups
Playlist tracks contains the tracks along with ordering information for each playlist.
This table contains lots of redundant info (artist, album, etc.) which should have been avoided.
The playlist table simply contains the names of the playlists.
The playlist groups table is empty, but I assume it would contain the types of playlists if I used multiple types.
Podcast channels and Podcast episodes
Like the playlist tracks and playlists, these tables would probably contain data about podcast channels and the episodes.
I don’t use this feature, so my tables are empty.
These appear to be zombie tables, possibly with outdated schemas that were never deleted from the database.
What an SQLite database looks like
Now, onwards to Clementine.
Clementine only supports SQLite because the developers felt that they would rather have perfect support for one backend than flaky support for multiple ones.
Like MySQL, SQLite is a another program to store and manage a relational database through SQL queries and commands.
Unlike MySQL, however, an SQLite database is a single file, usually with a .db extension, which contains all the relations/tables in the database.
Also unlike MySQL, access to an SQLite database is simply a matter of having access to the .db file—there’s no mucking about with users and permissions.
To connect to the database, one simply launches the SQLite binary on the database file.
What a Clementine database looks like
Clementine stores its internal database in ~/.config/Clementine/clementine.db, right next to what I presume is a backup copy of the same database.
Like for Amarok, I made a copy of the database before connecting to it.
What a Clementine database really looks like
The tables in the Clementine database bear a passing resemblance to Amarok’s.
This makes sense, considering that the project forked from the Amarok 1.x series.
However the similarities end rather quickly.
Notice the abundance of tables assigned for various web-based services.
Clementine’s support of various web-based services makes Amarok 2.x’s offerings look positively bare-bones.
Also notice the _fts_* tables accompanying each table.
These are tables created by the FTS (full-text-search) plugin.
The tables contain unintelligible binary data and help speed up text searches across the several attributes stored in each table.
Once again, I’ll present a guided tour through the information stored in this database.
A minor rant on SQLite
I find it almost impossible to make SQLite console output look good.
In fact, next to the output that mariaDB/MySQL produces, it looks particularly shabby.
By default, tables aren’t aligned to columns and are printed without headers.
So, you turn on headers first.
At least you know what each column represents.
But you definitely want column alignment before this mess is readable.
So, you can turn on the column mode.
Ah, much bette… wait a minute!
Now the values are truncated.
The only way out (no joke) is apparently to specify the column widths manually.
“Screw that!,” you say after typing a .width statement once, “I’m no peasant!
I’ll use the explain mode instead.”
Oh what fresh hell is this.
Now, the table headers are truncated and the columns are not aligned.
You do a quickie through the five stages of grief, hit [accept your fate] and move on with your work.
Luckily for you, dear reader, I will manually format the output of what follows to have full headers, full values and column alignment.
Don’t you ever say I did nothing for you.
As with Amarok, there’s a devices table that sits atop the data hierarchy.
However, unlike Amarok, the devices table only seems to be there to store active devices, not a record of every hard disk the application has ever seen.
In my case, it’s empty (and SQLite just returns nothing if there is nothing to say).
The directories table contains the directories that Clementine was told to find music in.
This is basically the top-level directory of your music collection(s).
The subdirectories table seems to contain every directory under the root directory, regardless of whether it contained a playable file or not.
In fact, the first instance in this table is the root directory itself.
Even so, each subdirectory is cross-referenced by the id of the root directory from the directories table.
Lastly, the last modified date is stored for each subdirectory.
Unlike the multiple relations in the Amarok database, Clementine stores all its data in this one massive table.
It contains file system attributes, embedded tags, and listening statistics for each track in the local library.
Clementine clearly sees an advantage to storing library information in these monolithic chunks, because this schema is reused again and again.
The tables for Amazon cloud, Box, Dropbox, Google Drive, Magnatune, Seafile, Skydrive, Spotify, Subsonic, and Ubuntu are all designed identically.
At its most basic, an XML file is just a store of data.
It consists of elements, which form enclosing tags around a text field.
Elements may contain other elements (sub-elements) instead of, or in addition to a text field.
Elements may also contain attributes within the enclosing tags.
If you’ve seen HTML code, you’ve more or less seen the basic structure of an XML file.
What an XML database looks like
There’s obviously no single, universal way to organize data in XML form.
Even so, the format of data stored in XML form can be well-specified using XML schema definitions.
What Rhythmbox’s XML database looks like
This is how Rhythmbox sees its music collection:
Each song is stored as an entry with the attribute type set to song.
Each song contains the usual title, artist, album, genre, etc. tags as subelements of the entry.
Listening statistics are basically boiled down to first-seen, last-seen/played and a rating between 0 and 5 in steps of 0.5.
Album art, if found outside of the file, is also recorded in this XML file as an entry of type ignore.
I feel like this is an example of reusing an existing XML schema for a purpose that it wasn’t intended for.
Rhythmbox has a separate playlist view, which comes with its own XML file storing queries, but this post has gone on for too long already, so I’ll skip that.
In any case, I’m not interested in going down the XML path as I think databases have significant advantages to offer over what XML is capable of.
Perhaps it’s worth noting that Amarok’s scanner actually writes an XML file before it’s imported by the application database proper.
This allows the scanner to be run separately from and even simultaneously with the main application.
The three music players surveyed are typical of music players in linux today.
Amarok and Clementine use SQL databases for their database backend, while Rhythmbox uses an XML database.
Neither, incidentally, felt the need to use NoSQL databases as the benefits of scalability and freeform schemas don’t really do much for music managers.
The difference in the way Amarok and Clementine databases are organized illustrates a concept called database normalization.
Clementine’s monolithic database is in unnormalized form and contains much redundant data between tuples.
For instance, all songs from the same album have to list the album name repeatedly.
Due to such redundancies, such databases are supposed to have slower write times.
However, unnormalized databases are often used to improve read times—once a row is identified as the result of a query, all its fields are immediately available.
As I’ve indicated multiple times, Amarok’s normalized database is much closer to the schema I have in mind for implementing as a backend for my mythic music player.
If anything, it doesn’t go far enough in normalizing its contents to enable the sort of relationships I want to manage and query.
In my next post, I’ll begin the process of implementing a collection scanner.