The aim is very simple: to export my collection db to some kind of readable format at artist-album base. I mean something like this:

Artist1 name
Album11 name
Album12 name
Artist2 name
Album21 name
Album22 name
...

Is there some tool to do the job?

Well, you could write a script to query the database for this information, using either the sqlite3 client or the `dcop amarok collection query` dcop call.

...though amaroK\'s database is already plenty usable. Its the point of a database.

Post edited by: eean, at: 2005/09/22 22:31

Indeed, there is another this forum thread with SQL example to get output in this form:

Artist1 name
Album11 name
Artist1 name
Album12 name
Artist2 name
Album21 name
Artist2 name
Album22 name

I have not enough fantasy to modifythose SQL-select the way to eliminate multiple outputs for the same artist: probably with such db structure (I mean tie role of the tags table) it is impossible.

I have the same need and I used the following script:

[code:1]#!/bin/bash

HEADER=\' \'
FOOTER=\'\'
SQLQUERY=\'select distinct artist.name as artist, album.name as album from artist, album, tags where tags.album = album.id and tags.artist = artist.id ;\'
DBFILE=~/.kde/share/apps/amarok/collection.db
OUTPUTFILE=/tmp/collection.html

if [ ! -x /usr/bin/tidy ] ; then
echo "You need to install tidy: apt-get install tidy"
exit 1
fi

if [ ! -x /usr/bin/sqlite3 ] ; then
echo "You need to install sqlite3: apt-get install sqlite3"
exit 1
fi

(echo $HEADER ; \\
sqlite3 -html -header $DBFILE "$SQLQUERY" ; \\
echo $FOOTER ) | \\
tidy > $OUTPUTFILE
[/code:1]

The script is available here: http://alban.apinc.org/files/amarok/collection.sh

Andrew wrote:

SQL-select the way to eliminate multiple outputs for the same artist

You need to use the keyword DISTINCT in the SQL query: "select distinct ..."

Hi,

you can try this (it\'s database independent):
[code:1]OUTPUT_FILE=/tmp/collection.html
echo \"\" >$OUTPUT_FILE
dcop amarok collection query \"select distinct \'\' from artist, album, tags where tags.artist=artist.id and tags.album=album.id order by artist.name\" >>$OUTPUT_FILE
echo \"

Artist	Album
\',artist.name,\'	\',album.name,\'

\" >>$OUTPUT_FILE[/code:1]

Regards,
Andreas