Bembel-B Blog

2007/10/31

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 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.

Advertisements

2007/10/17

My 3rd very own custom Rockbox build for Sansa e200

So here’s the binary release and patch log of my 3rd custom Rockbox build for Sansa e200 of todays SVN sources (Revision 15168). No patches have been added since my last build. Rockbox now features full multicore support and packed i2s transactions for PortalPlayer 502x targets. I also rebuilt the bootloader and used the latest Sansapatcher v0.5 to install it, as there are reports of problems booting the OF with older bootloaders.

SanDisk Sansa e200

UPDATE: Take a peek at my latest additions including Rockbox builds in my Rockbox category

The problem with missing special chars (German Umlauts) in the default font chicago12.fnt of SaagarsBigArt Theme can be circumvented by switching to nimbus-13.fnt.

Great news is the ongoing development of an USB stack for Rockbox on PortalPlayer targets. Hopefully we’ll soon be able to access the Sansa flash drive(s) without the need to boot the OF.

You may download my Rockbox build here.

For reasons mentioned above, I built the bootloader. The latest Sansapatcher v0.5 for x86 Linux can be downloaded here. For other versions refer to the manual. This is how I installed the bootloader image:

sudo ./sansapatcher -a /home/scheff/src/rockbox-15168/build/PP5022.mi4

These patches have been updated at the tracker since my 2nd build:
multifont-20071011.patch
customlist-20071015.patch
customline-20071015.patch

patch log:

FS#3045 – Album art display on WPS
patch -p0 < /home/scheff/downloads/album_art_20071005.patch

FS#5697 – bmp resize patch
patch -p1 < /home/scheff/downloads/bmp_resize-071005.patch

FS#7542 – This patch moves all the playlist creation from the root dir to the PLAYLISTS directory.
patch -p0 < /home/scheff/downloads/playlist_folder.patch

FS#7565 – support for the TPA tag
patch -p1 < /home/scheff/downloads/50-id3TPA.patch

FS#6800 – Sansa e200 backlight behaviour
patch -p0 < /home/scheff/downloads/lcddimmer20070915.patch

FS#6796 – Put ymargin into scrollinfo
patch -p1 < /home/scheff/downloads/ymargin-scrollinfo_20071002.patch

FS#2954 – Scrolling text margin for the wps
**
Hunk #1 FAILED at 143.
1 out of 2 hunks FAILED — saving rejects to file apps/gui/list.c.rej
Hunk #1 FAILED at 48.
1 out of 1 hunk FAILED — saving rejects to file firmware/export/scroll_engine.h.rej
** fix (failed function in list.c has been removed with revision 15125. ymargin patch changed the vanilla code in scroll_engine.h.)

FS#4733 – Multifont
patch -p1 < /home/scheff/downloads/multifont-20071011.patch

FS#5899 – Custom List/Menu x/y coordinates and width
patch -p1 < /home/scheff/downloads/customlist-20071015.patch

FS#5900 – Custom WPS Line with x/y/width coordinates and font face/color settings
patch -p1 < /home/scheff/downloads/customline-20071015.patch
**
Hunk #3 FAILED at 832.
1 out of 4 hunks FAILED — saving rejects to file apps/gui/wps_parser.c.rej
** fix (add the function after changed vanilla code line)

FS#4988 – Optionally keep backlight on while plugins are running
patch -p0 < /home/scheff/downloads/plugin-backlight_070303.patch

FS#6960 – Random (album) selection in Database
patch -p0 < /home/scheff/downloads/random_album.patch

FS#6884 – Sansa E200 m3u/m3u8 to pla playlist converter
patch -p0 < /home/scheff/downloads/sansa_playlist_converter_V3.2.diff

FS#6505 – Slider progressbar
patch -p0 < /home/scheff/downloads/progressbar_slider_20070622.patch
**
Hunk #1 FAILED at 287.
1 out of 3 hunks FAILED — saving rejects to file apps/gui/wps_parser.c.rej
** fix (albumart patch changed the vanilla code)

ChangeLog

[2008-01-20: Links to Rockbox category. Update pointing to latest builds.]
[2009-04-26: Fix box.net links.]

2007/10/09

My second very own custom Rockbox build for Sansa e200

Here you’ll find the binary release and patch log of my 2nd custom Rockbox build for Sansa e200 of todays SVN sources (Revision 15044). The customization is focused on getting prettier WPS screens and some Sansa specific tweaks.

SanDisk Sansa e200

UPDATE: Take a peek at my latest additions including Rockbox builds in my Rockbox category

The patch selection got a little smaller, as I gave up on some less important patches. The only one missing I’d like to have compiled is the DC Bias patch. The ones I’ll probably leave out also in future are striked out.

I’ve only done a quick test with this 2nd build at the time of writing. I’ve been using my 1st build quite intensively and with success. The only flaw I noticed was one single crash, when the backlight dimmed out in WPS while pressing buttons.
A non-build related problem is missing special chars (German Umlauts) in the default font chicago12.fnt of SaagarsBigArt Theme.

You may download my Rockbox build here.

These patches have been updated at the tracker since my 1st build:
sansa_playlist_converter_V3.2.diff
customline-20071008-c.patch
custom_list_position-20071004.patch
multifont-20071008.patch
scroll-margins_20071006-2.patch
ymargin-scrollinfo_20071002.patch
autorock.diff
bmp_resize-071005.patch
album_art_20071005.patch

So finally, here’s the log of patching:

FS#3045 – Album art display on WPS
patch -p0 < /home/scheff/downloads/album_art_20071005.patch

FS#5697 – bmp resize patch
patch -p1 < /home/scheff/downloads/bmp_resize-071005.patch

FS#7542 – This patch moves all the playlist creation from the root dir to the PLAYLISTS directory.
patch -p0 < /home/scheff/downloads/playlist_folder.patch

FS#7538 – Custom user splash screen.
found out, that you’d have to apply autorock.diff before custom_splashV7.1.2.diff, so trying:
patch -p0 < /home/scheff/downloads/autorock.diff
**
Hunk #1 FAILED at 571.
1 out of 1 hunk FAILED — saving rejects to file apps/menus/display_menu.c.rej
** skip (browse_fonts variable is missing now in svn. Omit this unimportant eyecandy patch.)

FS#7565 – support for the TPA tag
patch -p1 < /home/scheff/downloads/50-id3TPA.patch

FS#6800 – Sansa e200 backlight behaviour
patch -p0 < /home/scheff/downloads/lcddimmer20070915.patch

FS#6796 – Put ymargin into scrollinfo
patch -p1 < /home/scheff/downloads/ymargin-scrollinfo_20071002.patch

FS#2954 – Scrolling text margin for the wps
patch -p0 < /home/scheff/downloads/scroll-margins_20071006-2.patch
**
Hunk #1 FAILED at 48.
1 out of 1 hunk FAILED — saving rejects to file firmware/export/scroll_engine.h.rej
** manually fix (ymargin patch changed the vanilla code)

FS#4733 – Multifont
patch -p1 < /home/scheff/downloads/multifont-20071008.patch

FS#5899 – Custom List/Menu x/y coordinates and width
patch -p1 < /home/scheff/downloads/custom_list_position-20071004.patch

FS#5900 – Custom WPS Line with x/y/width coordinates and font face/color settings
patch -p1 < /home/scheff/downloads/customline-20071008-c.patch
**
Hunk #3 FAILED at 832.
1 out of 4 hunks FAILED — saving rejects to file apps/gui/wps_parser.c.rej
** manually fix (add the function after changed vanilla code line)

FS#4988 – Optionally keep backlight on while plugins are running
patch -p0 < /home/scheff/downloads/plugin-backlight_070303.patch

FS#7286 – correct DC-bias for output signals (dsp, test_codec)
patch -p0 < /home/scheff/downloads/dc_bias_v3.patch
** skip (same patch failed last time already)

Recording Enhancements Pack
** skip (obsolete)

FS#6960 – Random (album) selection in Database
patch -p0 < /home/scheff/downloads/random_album.patch

FS#6884 – Sansa E200 m3u/m3u8 to pla playlist converter
patch -p0 < /home/scheff/downloads/sansa_playlist_converter_V3.2.diff

FS#7114 – HTML_Parser. Creates a txt file readable in the viewer
** skip (omit this unneeded patch, which would have required manual fixing)

FS#6505 – Slider progressbar
patch -p0 < /home/scheff/downloads/progressbar_slider_20070622.patch
**
Hunk #1 FAILED at 287.
1 out of 3 hunks FAILED — saving rejects to file apps/gui/wps_parser.c.rej
** manually fix (albumart patch changed the vanilla code)

I also built the bootloader, but an update isn’t required; you can still use the one available at rockbox.org.

For the first time I took a look at rbutilqt, the Rockbox Utility. Very handy thing! You may download the dynamic version for Ubuntu 7.04, but you may get problems because of missing QT libraries (as it’s not statically linked).
Here’s the build process and installation:

cd /home/scheff/src/rockbox-15044/rbutil/rbutilqt
qmake-qt4
make
lrelease rbutilqt.pro
mkdir /home/scheff/opt/rbutilqt
cp -p rbutilqt /home/scheff/opt/rbutilqt
cp -p *.qm /home/scheff/opt/rbutilqt
ln -s /home/scheff/opt/rbutilqt/rbutilqt /home/scheff/bin/rbutilqt

ChangeLog

[2008-01-20: Links to Rockbox category. Update pointing to latest builds.]

Blog at WordPress.com.