OK, it feels a little creepy answering my own post, but I thought someone might found what I discovered useful.
The collection database can be queried through dcop using a command something like this:
Code:
$ dcop amarok collection query "SELECT ... FROM ..."
There's a partial page on the amarok wiki describing the collection database, but it's apparently a little out of date, and at any rate incomplete. To explore the database you can use the sqlite3 command line client. The version of sqlite3 which ships with ubuntu dapper doesn't like the newer format of collections.db which amarok 1.4.2, so you'll need to build the new version of sqlite from source or something. It's probably prudent to work on a copy of the collection.db file:
Code:
$ cd /tmp
$ cp ~/.kde/share/apps/amarok/collection.db .
$ sqlite3 collection.db
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> .tables
admin devices lyrics related_artists
album directories playlists statistics
amazon embed podcastchannels tags
artist genre podcastepisodes uniqueid
composer images podcastfolders year
The structure of individual tables can be found using the .schema command:
Code:
sqlite> .schema podcastchannels
CREATE TABLE podcastchannels (url VARCHAR(1024) UNIQUE,title VARCHAR(255),weblink VARCHAR(1024),image VARCHAR(1024),comment TEXT,copyright VARCHAR(255),parent INTEGER,directory VARCHAR(255),autoscan BOOL, fetchtype INTEGER, autotransfer BOOL, haspurge BOOL, purgecount INTEGER );
CREATE INDEX url_podchannel ON podcastchannels( url );
sqlite> .schema podcastepisodes
CREATE TABLE podcastepisodes (id INTEGER PRIMARY KEY , url VARCHAR(1024) UNIQUE,localurl VARCHAR(1024),parent VARCHAR(1024),guid VARCHAR(1024),title VARCHAR(255),subtitle VARCHAR(255),composer VARCHAR(255),comment TEXT,filetype VARCHAR(255),createdate VARCHAR(255),length INTEGER,size INTEGER,isNew BOOL );
CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );
CREATE INDEX url_podepisode ON podcastepisodes( url );
So a typical dcop query you might use to find all podcasts with downloaded episodes might look like this:
Code:
$ query="SELECT podcastepisodes.id,
podcastchannels.title,
podcastepisodes.title,
podcastepisodes.localurl
FROM podcastchannels, podcastepisodes
WHERE podcastepisodes.parent = podcastchannels.url
AND podcastepisodes.localurl LIKE 'file%';"
$ dcop amarok collection query "$query"
Enjoy
