Bembel-B Blog


Generate Amarok Album List with MySQL

Filed under: album list,Amarok,collection,database,MySQL,PhpMyAdmin,software — FrankZabbath @ 18:52

Amarok Logo

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,, SEC_TO_TIME( SUM( length ) ) , COUNT( track ) ,,
FROM tags, album, artist, year, genre
WHERE tags.album =
AND tags.artist =
AND tags.year =
AND tags.genre =
AND <> ""
GROUP BY tags.album

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.


Ubuntu Feisty Fixes Done and Pending

A few things when switching to Ubuntu weren’t working out of the box as I’d liked. Some still aren’t. The maybe not that trivial or obvious fixes are listed here.

Ubuntu Logo

Evolution Spamassassin Plugin

The bogofilter plugin must be disabled, else spamassassin plugin won’t work. Only one of the two spamfilter plugins may be enabled.
I’ve installed spamassassin and spamc packages with service spamd running.

GnuCash HBCI Support

The Ubuntu and Debian packages don’t include HBCI/FinTS support used for online banking. I had already installed aqbanking and QBankManager, but meanwhile there’s the apt repo which provides the GnuCash HBCI functionality for Debian Etch and Ubuntu Feisty.
This should install all required packages for GnuCash HBCI from geole:

aptitude install gnucash-aqbanking-hbci

Amarok German Localization

After I installed language-pack-kde-de Amarok speaks German.

Hide Gnome Printer Applet

The tray icons for installed printers are permanently shown and eat up my precious screen space. I think the following shouldThis will get the behaviour I know from Fedora, that the icons are only displayed when I ran a print job. But I haven’t tested that yet!
System > Settings > Sessions: untick “print queue applet”

Azureus with Sun Java

I’ve installed Sun JDK (also containing the JRE) and set /usr/bin/java to point to the Sun version. Azureus for Sun Java installed via apt-get was crashing regularly. Using the binary release from the Azureus project website did the trick.
I even rebuilt the Ubuntu Azureus package, but the result was as unstable as with the apt version.

Things Still Unresolved

  • Automount for smb shares (preferably with cifs)
  • Improve keyboard configuration like inputrc, tab completition, vim as setup in Fedora
  • A working ssh-agent and gpg-agent setup (preferably with Seahorse)
  • Crackling sound with Amarok and web streams (xine engine)


[070523 Gnome Printer Applet works as expected]


Amarok Bluetooth Remote for Sony Ericsson K750i Mobile Phone

A few weeks ago I purchased a shiny new mobile phone, the Sony Ericsson K750i, to replace my good ole Sony Ericsson T610. A nice feature of the K750i are the freely configurable Bluetooth Remotes via BT HID, which are also available on other models.

Sony Ericsson K750i

So I wanted to remote control my favoured music player Amarok. I found a ready made HID configuration by tnt, but unfortunately the keystrokes where not the default Amarok global hotkeys’ ones and also conflict with the default Gnome shortcuts. With the help of Stefan Tomanek’s great website and the HID Usage Tables Reference (page 53) I modified the Remote Control keys to match the Amarok defaults.

A ready to use .hid file is now available from my share.

If I wasn’t lazy right now, I would provide the new HID profile ready to use – an uncompressed tar archive containing the GUI screen as JPEG image and the HID configuration as a UNIX style XML file. But I’ll have to add the GPL stuff first. So for now here’s the diff of the key codes:

[scheff@p512o amarok-hid]$ diff *kcf*
<         <KEYBOARD MODIFIERS = "0A" USAGEID = "56"/> <!-- REWIND -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "50"/> <!-- REWIND -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "06"/> <!-- PLAY -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "4A"/> <!-- PLAY -->
<         <KEYBOARD MODIFIERS = "0A" USAGEID = "57"/> <!-- FAST FORWARD -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "4F"/> <!-- FAST FORWARD -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "1D"/> <!-- PREV -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "4B"/> <!-- PREV -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "19"/> <!-- STOP -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "16"/> <!-- STOP -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "05"/> <!-- NEXT -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "4E"/> <!-- NEXT -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "10"/> <!-- MUTE -->
>         <KEYBOARD MODIFIERS = "05" USAGEID = "10"/> <!-- MUTE -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "57"/> <!-- VOL UP -->
>         <KEYBOARD MODIFIERS = "01" USAGEID = "57"/> <!-- VOL UP -->
<         <KEYBOARD MODIFIERS = "08" USAGEID = "56"/> <!-- VOL DOWN -->
>         <KEYBOARD MODIFIERS = "01" USAGEID = "56"/> <!-- VOL DOWN -->
\ Kein Zeilenumbruch am Dateiende.

To get this running on my Fedora Core 5 box, all I had to do was uploading the HID configuration to my mobile:

obex_push 5 00:11:22:33:44:55 AmaroK.hid

Then on the phone start the remote choosing my FC5 box as target and connect my HID Server with:

hidd --connect 00:11:22:33:44:55

To remove the connection again, use:

hidd --unplug 00:11:22:33:44:55

Don’t know if that’s necessary, but I had paired phone and PC before.

Have fun! And may the power-source be with you! :]


[070429 Fix minor typo. Add download link. Add photo.]

Blog at