Generate Amarok Album List with MySQL

There’s no option in Amarok to export a listing of your collection. Also the script collection2html available via Amarok’s Script Installer is always crashing. My collection database is stored by MySQL, which I used to export the desired list via PhpMyAdmin.

As I’m not that much of an SQL expert, I took a look at the queries of that collection2html script by Marcelo Bovo and modified them to my taste. Here’s the resulting query:

SELECT artist.name, album.name, SEC_TO_TIME( SUM( length ) ) , COUNT( track ) , year.name, genre.name
FROM tags, album, artist, year, genre
WHERE tags.album = album.id
AND tags.artist = artist.id
AND tags.year = year.id
AND tags.genre = genre.id
AND album.name <> ""
GROUP BY tags.album
ORDER BY artist.name, year.name, album.name;

And that’s part of the output (csv), that can be exported to several formats via PhpMyAdmin:

"name";"name";"SEC_TO_TIME( SUM( length ) )";"COUNT( track )";"name";"name"
;"Metallica-The Ultimate Tribute";"00:03:24";"1";"2004";"Metal"
"A Sides";"Metalheadz Platinum-METPLA006";"00:13:18";"2";"2006";"Drum & Bass"
"Aborted";"The Purity Of Perversion";"00:31:28";"9";"1999";"Death/Grind"
"Aborted";"The Archaic Abattoir";"00:36:26";"10";"2005";"Brutal Death Metal"
"Aborted";"After Forever";"00:57:57";"12";"2007";"Heavy Metal"
"Adam Green";"Garfield";"00:43:16";"14";"2002";"Rock"
"Adam Green";"Friends of Mine";"00:33:02";"15";"2003";"Alternative"
"Adam Green";"Gemstones";"00:31:44";"15";"2005";"Alternative"
"Adam Green";"Jacket Full of Danger";"00:30:33";"15";"2006";"Rock"

One downside of this method is, albums containing several artists (samplers etc.) are not handled separately. But it’s good enough for me for the moment.