hi,

When I was using Amarok 1.4 I had an ugly shell script that gave me a list of the albums in my collection by querying the MySQL database:

mysql --skip-column-names -D amarok -u viewer << \Eof
select concat(lpad(count(*), 2, '0'), ' ', floor(sum(t.length)/60), ",", artist.name, "; ", album.name)
from tags t, artist artist , album album
where t.bitrate > 128 and artist.id = t.artist and album.id = t.album
group by t.album
having sum(t.length) > 10 * 60
order by artist.name, album.name;
Eof

I only want to consider tracks with a bitrate higher than 128 kbit/s and my definition of having an album was just that I had more than 10 minutes of music from the album. I just ignored compilations from different artists.

Can you please give me a hint on how I might write an external script do the same thing using Amarok 2.6?

My goal is to store, in my telephone, a text file of the albums that I own so that I can check the list in the shop before buying a new CD. I'd like to create this list in a cron job and then upload it to my phone once a day.

thanks, Stuart

I wrote a script in Python to extract album art from the database, which is here. You ought to be able to adapt that to do what you're looking to do.

ok, thanks. So I still need to read the MySQL database directly. I will try to write a SQL join to get the list of all the albums with more than 10 minutes of music at more than 128 kbits/s. I found an (old?) copy of the database schema here http://gitorious.org/amarok/amarok/blob ... schema.png but I'll look for a program to extract the schema from the database for me (http://how-to.linuxcareer.com/using-mys ... -on-amarok).

People say that http://quickgit.kde.org/?p=amarok.git&a ... s/database has a more up to date schema but it seems to be down.

I am using an external MySQL server so I'll just connect to it and avoid having to copy the database and run a MySQL server just for my program.

The database schema in the git repo is here. I don't know where you got that URL from, but there's nothing at that file path in the git repo.

In regards to Quickgit being down - it is perfectly fine. However you do appear to have uncovered a bug in the version of GitPHP we currently have in use. I will be investigating this (with my KDE Sysadmin hat on).

Update: I have filed a bug report regarding this with GitPHP upstream - http://www.gitphp.org/issues/121. Thank you for reporting this.

I made a first attempt a retrieving a list of all complete albums where all tracks have at least a minimum bitrate. The SQL is:

Code: Select all

select artists.name, albums.name, min(tracks.bitrate), count(*)
          from artists, albums, tracks
          where
            albums.artist = artists.id
            and tracks.artist = artists.id
            and tracks.album = albums.id
            and albums.name <> ''
            and tracks.bitrate >= %s
         group by albums.id
         having count(*) >= max(tracks.tracknumber)
            and count(*) >= %s
         order by artists.name, albums.name


You can find this program at github
https://github.com/stuart12/python-scripts/blob/master/amarok_extract

Let me know if you see how I can do better. If I have a track twice and and am missing a track, the album would be printed. How could I avoid that I wonder? How can I check that I have each track number at least once?