world gone mad

I'm a GNOME user, but on my brother's suggestion I tried Amarok a month or so ago, and never looked back. I've used most of the popular music players for linux as well as iTunes & WMP, and I'd actually call Amarok the best on any platform. It's not even close.

One really nice feature is the database integration. I think every application should have this option, and not just because I work for a database company. Amarok can keep all its information in a MySQL database, and that includes the song lyrics it pulls down from the web.

My daughter's not feeling well and asleep, my wife is out of the house, so I'm in for the day. Inane OLAP it is!

A little poking around, and the data model is not bad. The main track information (be it ogg, mp3, aac, whatever) is in the 'tags' table, which looks like this:

mysql> describe tags;
+------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------+------+-----+---------+-------+
| url | varbinary(255) | YES | MUL | NULL | |
| dir | varbinary(255) | YES | | NULL | |
| createdate | int(11) | YES | | NULL | |
| modifydate | int(11) | YES | | NULL | |
| album | int(11) | YES | MUL | NULL | |
| artist | int(11) | YES | MUL | NULL | |
| composer | int(11) | YES | MUL | NULL | |
| genre | int(11) | YES | MUL | NULL | |
| title | varchar(255) | YES | | NULL | |
| year | int(11) | YES | MUL | NULL | |
| comment | text | YES | | NULL | |
| track | decimal(4,0) | YES | | NULL | |
| discnumber | int(11) | YES | | NULL | |
| bitrate | int(11) | YES | | NULL | |
| length | int(11) | YES | | NULL | |
| samplerate | int(11) | YES | | NULL | |
| filesize | int(11) | YES | | NULL | |
| filetype | int(11) | YES | | NULL | |
| sampler | tinyint(1) | YES | MUL | NULL | |
| bpm | float | YES | | NULL | |
| deviceid | int(11) | YES | | NULL | |
+------------+----------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

'url' here is misleading - it's a relative file path. And I'd like the song-genre relationship to be many-to-many, but now I'm getting picky. What genres do I like?

mysql> select g.name, sum(s.playcounter) from genre g, tags t, statistics s where g.id = t.genre and s.url = t.url group by t.genre order by sum(s.playcounter) desc limit 15;
+-------------+--------------------+
| name | sum(s.playcounter) |
+-------------+--------------------+
| | 79 |
| Rock | 57 |
| Jazz | 40 |
| Country | 25 |
| Other | 15 |
| Pop | 15 |
| Rap | 11 |
| Folk | 7 |
| Alternative | 6 |
| Blues | 6 |
| R&B | 5 |
| Death Metal | 3 |
| Ska | 3 |
| Hip Hop | 3 |
| Soul | 2 |
+-------------+--------------------+
15 rows in set (0.09 sec)

Huh. Lots of unknowns. I'll go through and tell Amarok a few things (the Toasters are always Ska, Al Green is soul, those kinds of things). The stats update.

mysql> select g.name, sum(s.playcounter) from genre g, tags t, statistics s where g.id = t.genre and s.url = t.url group by t.genre order by sum(s.playcounter) desc limit 15;
+---------------+--------------------+
| name | sum(s.playcounter) |
+---------------+--------------------+
| Jazz | 58 |
| Rock | 52 |
| | 37 |
| Country | 28 |
| Pop | 15 |
| Rap | 14 |
| Alternative | 11 |
| Folk | 9 |
| Other | 9 |
| Hip Hop | 8 |
| Instructional | 7 |
| Punk | 7 |
| Blues | 6 |
| Children's | 5 |
| R&B | 5 |
+---------------+--------------------+
15 rows in set (0.08 sec)

So here's where the lyrics are kept:

mysql> show create table lyrics \G
*************************** 1. row ***************************
Table: lyrics
Create Table: CREATE TABLE `lyrics` (
`url` varbinary(255) default NULL,
`deviceid` int(11) default NULL,
`lyrics` text,
UNIQUE KEY `lyrics_url` (`url`,`deviceid`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

OK, but then how do I search song lyrics? Well, it's MyISAM, so I add a fulltext index.

mysql> create FULLTEXT index lyrics_full on lyrics ( lyrics );
Query OK, 16 rows affected (0.10 sec)
Records: 16 Duplicates: 0 Warnings: 0

Yikes! 16 rows? Turns out it only pulls lyrics when you go to that tab. So I need to keep amarok playing with the Context->Lyrics tab showing for a while. That, in turn, requires a little hand-holding for when Amarok does not find an exact match for what grip called my track when it goes to Lyric.

Now I can do this:
mysql> select a.name, t.title from artist a, tags t, lyrics l where a.id = t.artist and l.url = t.url order by t.title;
+-------------------------+---------------------------------------------+
| name | title |
+-------------------------+---------------------------------------------+
| The Clash | Atom Tan |
| John Lennon | Beautiful Boy |
| Bob Dylan | Buckets of Rain |
| Buena Vista Social Club | De Camino a La Vereda |
| Cake | Dime |
| Naughty By Nature | Feel Me Flow |
| Sublime | Garden Grove |
| Cake | Haze of Love |
| Ray Charles | I Can't Stop Loving You |
| Tom Waits | I Wish I Was in New Orleans |
| Cake | Never There |
| The Police | On Any Other Day |
| Ramones | Rockaway Beach |
| Johnny Cash | Rusty Cage |
| Beastie Boys | Sabotage |
| The Rolling Stones | Shattered |
| The Rolling Stones | She's A Rainbow |
| Ramones | Sheena Is a Punk Rocker |
| Merle Haggard | Someday We'll Look Back |
| Johnny Cash | Southern Accents |
| Gogol Bordello | Start Wearing Purple |
| The Rolling Stones | Sweet Virginia |
| Squeeze | Tempted |
| Prince | The Cross |
| Nirvana | Tourette's |
+-------------------------+---------------------------------------------+
25 rows in set (0.01 sec)

So after some more listening, let's see who talks about love the most...

mysql> select a.name, count(*) from artist a, tags t, lyrics l where a.id = t.artist and t.url = l.url and match( l.lyrics ) against ('love') group by a.id order by count(*) DESC limit 5;
+----------------------------------+----------+
| name | count(*) |
+----------------------------------+----------+
| Elvis Costello & The Attractions | 2 |
| Cake | 2 |
| Tom Waits | 2 |
| The Clash | 1 |
| The Beatles | 1 |
+----------------------------------+----------+
5 rows in set (0.00 sec)

Not too conclusive - and points largely to the lack of R&B songs in the lyrics database. Back to data collection.