Note: This is a simple ‘Week-end adventure’, to make a tedious task into automated, fast and reliable one. The output may not be completely bug free.
Problem Description:
I have ‘a lot’ of songs in my media library and I use SongBird player to listen to them. There are some ‘Not so good’ songs in the library too, which I never like to listen and dont want SongBird to repeat it ever in future. I wanted to pick all such songs and delete them from my library (from the file system actually) so no media player can play them again! 
Options:
- Listen to each and every song and delete them from disk manually (play from windows explorer and delete if song is bad – repeat this for all songs)!
- Not possible.
- Find a media player which allows to delete song from disk, in one click, from within the media player UI.
- I am so addicted to SongBird, can’t move to something else. One of the major reasons I am stuck with SongBird is native Last.fm integration. So, this was not feasible for me.
- Play songs in SongBird, when any bad song is played, mark the song with Rating 1, move to next song and periodically delete all such songs with Rating = 1.
- I could update my playlist to exclude Rating 1 song (I use Smart Playlist in SongBird to do this.. - so from the time when song is marked with 1 rating and its actually deleted, its never played!)
- This way, I will not have to spend too much time while actually listening to the song. All I do is, when a song is played (from the playlist), if I don’t like it, I would just set the rating to 1.
- This way, if I mark the song with Rating = 1 on my iPod, it will be deleted periodically too!
- The way it works is, I mark the song as rating 1 in iPod, sync the iPod with iTunes and next time I start SongBird, it imports iTunes library with all the ratings! Happy happy
- The way it works is, I mark the song as rating 1 in iPod, sync the iPod with iTunes and next time I start SongBird, it imports iTunes library with all the ratings! Happy happy
So, last option from list above, seemed the best. But missing part was “Periodically delete all songs with rating 1”, how to do that!. I searched in SongBird help and closest I could find was,
- Sort Library view in Ascending order of rating. [So, all rating one songs are listed together]
- Right click on each and every such song and click on “Show File”. This will open Explorer window where you can see the actual MP3/WAV/MP4 file and hit delete key.
- Once its deleted from disk, delete the song from library too (this step is completely optional, but if you like things clean, you would like to do that).
Imagine 52 songs with rating 1 are to be processed, above procedure would take at least 6 minutes, and make me so bored that I will need a coffee. That was the birth of my wish to create a small utility / script that goes through the songbird library and deletes all rating 1 songs. So, I started the research…
- First task was to find out where SongBird stores the data about the library (where it stores that xyz song has 1 rating and file on disk for that song is at abc path).
- I started the procmon tool, set the filter to give me data about only songbird.exe process and set rating for a song to 1. This made songbird write to a .db file in my Appdata\Roaming\.
- I went to the location (C:\Users\<username>\appdata\Roaming\Songbird2\Profiles\<randomID>.default\db\main@library.songbirdnest.com.db) and opened the file in notepad.
- From the signature (first few bytes of the file – which read as, “SQLite format 3”) of the file, I could see that songbird stores the data in a SQLite Database.
- I wanted to see what is the schema of the database in order to see if it is technically feasible to do what I wanted to. I searched for a free GUI based SQLite Database Viewer tool and found SQLite Administrator here. What an awesome tool. Anyways, I saw that a table called, “media_items” stored basic details about all the songs.
- “content_url” field stores URL of the song (in whacky style – URL style, but that can be dealt later), table has primary key of media_item_id.
- This table does not store Rating information yet. So, I continued my search. Reverse Engineering how they store Rating information was a little bit tricky but I found success after fondling for some time.
- Next table, I found interesting was “Properties”,
- Though this table did not store much of data, but looked like it property_id will be referenced by some other table. So, in my case, Number 28 in Property_ID field was important to me. I wrote it down in scratch pad and continued my search.
- The one that actually stored the rating information was “Resource_Properties”. When I looked at it first time, it did not look like it had, but then in depth review said something more!
- So, with this, I got all the information that I wanted. The conclusion was,
- Query all the rows from resource_properties with property_id = 28 AND obj=1 AND obj_searchable=1 AND obj_sortable=1
- For all such records, pick media_item_id number and query media_items table for that media, get the content_url for that song, and go to the file system and DELETE it!
So, here came another set of choices. Where to write this code so that it is fast, flexible (change can be done without much hassle), simple. I had few choices.
- C# (Use the power of .NET)
- I would love to write code in C# for this, but few problems I could see,
- Deployment (if I had to give this to somebody else, they have to have .net framework 3.5 – if i’d use LINQ/System.core, and I’d have to deploy all the client libraries such as reading SQLite libraries or any other third parties – I did not want to get into that mess)
- Changing just small thing would need me to recompile
- I would love to write code in C# for this, but few problems I could see,
- Batch Script
- I’ve been learning few advanced batch script techniques at work to make keep my life away from hell.
- Songbird AddOn (more info here)
- This would take lot of time because I dont know what language they are written. I rejected this option right away.
After a few minutes of thoughts, I chose Batch script to be the way to go. The task was though, if it’d been .NET I could have found some client library to access database. I had to find something similar for command line or write a small tool that does SQLite part and returns the output! Here’s how the research went.
- I searched for a tool that can interact with SQLite databases from command line. And I got sqlite3.exe from here. Played with this tool and found that I can actually open a sqlite database and query tables from command line!! How great! But the problem was, I had to do everything from SQLite shell, which looked something as follows. Yellow highlighted rectangles shows shell prompt where one can enter commands and get output,
- So, from batch file, this would be of no use if I had to automate it 100% (because a batch script can not enter input into some other shell!).
- I continued my research to see if there was anyway sqlite.exe could take all the commands from command line or may be input file and give me output without entering into its shell (only then it was useful for my purpose of calling it from batch script.)
- And I found something from a webpage,
- Above looked promising to me. So, I went ahead to solve another big problem.
- Next problem was to convert URL styled paths to normal paths which DOS understands. I knew the formula to convert them but I wanted to automate them. While searching for command line search-replace utility, I found ssr.exe here.
- That was all I needed from outside. I took all these things and developed a final batch script that does the task for me, which is as follows,
Code for Batch Script:
@echo off
REM Generate Songs.csv file
sqlite3 "C:\Users\Jigar Mehta\appdata\Roaming\Songbird2\Profiles\dv86zu0s.default\db\main@library.songbirdnest.com.db" "select media_item_id, content_url from media_items" > songs.csv
REM Convert URL style path to file system path
ssr 0 "file:///" "" Songs.csv
ssr 0 "/" "\\" Songs.csv
ssr 0 "%%20" " " Songs.csv
ssr 0 "|" "," Songs.csv
REM Query Songbird database for All Songs with Rating = 1, and write path to all such files to a temporary file
sqlite3 "C:\Users\Jigar Mehta\appdata\Roaming\Songbird2\Profiles\dv86zu0s.default\db\main@library.songbirdnest.com.db" "select * from resource_properties where obj like '1' AND obj_searchable like '1'" | findstr "|28|" > tobeDeleted.txt
if exist tobedeleted.txt (
for /F "delims=|" %%i in (tobeDeleted.txt) do (
type songs.csv | findstr "^%%i," >> deleteList.txt
)
)
REM Actually DELETE the file from the disk.
if exist deletelist.txt (
for /F "tokens=2 delims=," %%j in (deleteList.txt) do (
echo Deleting : %%j
del "%%j"
)
)
REM Remove temporary files created during the process.
if exist deletelist.txt del /Q deleteList.txt > nul 2>&1
if exist tobeDeleted.txt del /Q tobeDeleted.txt > nul 2>&1
if exist songs.csv del /Q songs.csv > nul 2>&1
I have uploaded all the files (Batch script + Other tools like sqlite3.exe and ssr.exe used by batch script). Please download them from here.
If you want to use the script, please make sure you change path to your database file in the batch script. HTH,
Stay tuned.. 