Last week one of my coworkers told me about Synergy (which lets you use a single mouse and keyboard with multiple computers across a network, and is cross-platform so I can use it on my laptop and my work computer) so I have been able to use my laptop more at work and I would rather have all my podcasts in a single location anyhow. Since my laptop is running Ubuntu I needed a Linux alternative to iTunes. I have been trying to find an alternative to Apple’s iTunes for managing my podcasts for quite awhile and ever since their, recent, upgrade to iTunes 7 my search has taken on a new life. I have heard that quite a few people that listen to Podcasts with iTunes have been having problems since the upgrade.
So I started, last week, using Amarok. One of the things that I didn’t like about it is that I can’t figure out how to list all the Podcasts that I have, yet, to listen to in one place. I know that they have some interface that lists them in an HTML page under the content tab but I can’t find a way to send them to my media device from there. So I found where the program stores it’s smart playlist information (~/.kde/share/apps/amarok/smartplaylistbrowser_save.xml) and I found a discussion online with how to interface with the SQL in the program and I wrote a query to pull up Podcasts that I haven’t listened to yet. The query is listed below. I am having problems with the filename portion though. It seems that Amarok saves the podcasts to directories based on the “Album name” and these have spaces in them that are encoded in the file location as %20’s. This wouldn’t be a problem except that SQLite does not have a Replace function so I can’t figure out how to replace the ‘%20’ with a space manually. Here is the code that lists them in case anyone is interested. And here is the SQL:
SELECT substr(podcastepisodes.localurl, 8, length(podcastepisodes.localurl)-7) as url, 0 as deviceid, podcastchannels.title as name, podcastepisodes.composer as name, podcastepisodes.composer as name, 'Podcast' as name, podcastepisodes.title as title, '2006' as name, podcastepisodes.comment as comment, 0 as track, 0 as bitrate, 0 as discnumber, podcastepisodes.length as length, 0 as samplerate, podcastepisodes.size as filesize, 0 as sampler, podcastepisodes.filetype as filetype, 0 as bpm FROM podcastchannels, podcastepisodes WHERE podcastepisodes.parent = podcastchannels.url AND podcastepisodes.localurl LIKE 'file%' AND isNew ORDER BY createdate DESC;