Monday, May 23, 2011

Migrating Playcount info in Clementine

Recently discovered Clementine, a fork of Amarok which used to be my music player of choice when I had a functioning linux box. It's been great. As it happens, I had to dump my database and re-scan my music collection - which causes all playcounts and scores to be lost (and other stats which I care less about). Here are the steps I took to restore them. Probably is a way to do this with less steps.

  1. I used a free trial of RazorSQL for this - it's ok. Nothing to scream about, IMO, compared to PgAdmin III which I'm used to for this sort of thing.
  2. Extract the data from your db backup with a query like: SELECT title,artist,album,playcount,score FROM songs WHERE score > 0;
  3. Export the data to a tab-separated file
  4. Convert it to a bunch of update statements with a regex
    1. First replace all ' with ''
    2. Then s/{.*}\t{.*}\t{.*}\t{.*}\t{.*}/UPDATE songs SET playcount = \4, score = \5 WHERE title='\1' AND artist='\2' AND album='\3';/
  5. Run the resulting statements on your live database.  I believe Clementine needs to not be running when you do this.