Lattice of Convenience - MP3 Playlists

Hopefully everyone can live in the future someday.

We do a lot with MP3 playlists. I run Airsonic for streaming around the house and in the car, and we have a playlist-based FM transmitter setup, etc. So I have scripts which run every night and generate playlists based on star ratings and other things (GET THE LED OUT ANYONE?).

Previously what I've done is dump the contents of a bunch of Smart Playlists in Clementine to a file and use those files to generate the randomized 200 track daily playlists. The downside to that is that every time I add music or change star ratings, I'd have to refresh these "base" files like some kind of animal. I had base playlists for "3+ stars", "4+ Stars" and "5 Stars", among others.

Today I decided to fix all that. Clementine uses a SQLite3 database, so now I'm just querying it instead, and it seems to be working well. For example, my "5 star" playlist in Clementine results in 10800 or so tracks. The same one built from the DB ends up with a couple hundred more tracks, but is pretty close. I'm not entirely sure what the difference there is just yet, but "close enough". What it looks like to me is I probably need to enable Samba case sensitivity.

The DB records ratings as decimal numbers from 0.0 (Zero stars) through 1.0 (5 Stars). So to build a "4-Star +" playlist, searching for rating >= "0.8", you get ratings like this:

1
1.10000002384186
0.800000011920929
0.800000011920929
0.800000011920929
1.10000002384186
1.10000002384186
0.800000011920929
0.800000011920929
1
1
1
1

! Caveat: Prior to Clementine 1.4.0rc1-533-gf4e70face there was a bug where it was possible to give a song a higher than 5 star rating (higher than 1.0 in the DB) as you can see above, so know that if you have Clementine from the repositories, it's likely you have that bug. For instance in the UI, if you want to show all 5 star songs, use "Rating is Greater Than 4.5 Stars" rather than "Rating is Equal to 5 Stars".

Now I can just have a cron job to copy the master Clementine DB once a day to my server and drop it in next to the playlist generation scripts.

The downside to all this is speed. When using the Clementine-Generated base playlists, I could be sure all the files actually exist on disk. However while Clementine will only show you files that exist in the UI, it doesn't seem to do a very good job of cleaning the database of stale files which no longer exist. So if you move or rename files, the old DB entries stick around unless you purge it completely and start over from scratch. That means I have to test every single file as I add it to the playlist, which takes time. It takes about 5-8 seconds to generate my 200 track 5-Star M3U file.

The 5-Star.sh script is below if you'd like to play along at home:


#!/bin/bash

rm /Volumes/Filestore/CDs/playlists/5\ Stars.m3u

i=1

while [ $i -le 200 ]
do
 file=$(sqlite3 ./clementine.db "select filename from songs where rating > "0.9" order by random() limit 1;" | awk -F "file://" '{print $2}')

 ### Clementine data encodes special characters and accent marks and stuff so I'm using
 ### Joel Parker Henderson's urldecode.sh to undo that: https://gist.github.com/cdown/1163649
 
 data=$(urldecode.sh "$file")
 if [ -f "$data" ]
 then
  ### Have to escape leading brackets because grep treated it as a range and would allow duplicates ###
  ### Can't do that in "data" because \[ isn't in the filename so they'll fail ###

  escaped=$(echo "$data" | sed 's/\[/\\[/g')
  #echo "$escaped"

  ### Avoid duplicates
  match=$(grep -i "$escaped" /Volumes/Filestore/CDs/playlists/5\ Stars.m3u)
  if [ -z "$match" ]
  then
   echo "$data" >> /Volumes/Filestore/CDs/playlists/5\ Stars.m3u
   ((i++))
  fi
 fi
done

For the 3+ and 4+ lists, I repeat this main block, but instead each rating dumps into a text file that I randomize into an .m3u at the end. So for the 3-Star + script below, I collect 130 5-star tracks, 45 4-star, and 25 3-star, push them out to a temp file and then cat temp.m3u | sort -R > "./3 Star +.m3u". I could do all this by creating a new table in the database and stuffing tracks into that, but this was faster for me to write and it works well enough:


#!/bin/bash

rm /Volumes/Filestore/CDs/playlists/3\ Stars\ +.m3u

i=1

while [ $i -le 130 ]
do
 file=$(sqlite3 ./clementine.db "select filename from songs where rating > "0.9" order by random() limit 1;" | awk -F "file://" '{print $2}')

 ### Clementine data encodes special characters and accent marks and stuff so I'm using
 ### Joel Parker Henderson's urldecode.sh to undo that: https://gist.github.com/cdown/1163649
 
 data=$(urldecode.sh "$file")
 if [ -f "$data" ]
 then
  ### Have to escape leading brackets because grep treated it as a range and would allow duplicates ###
  ### Can't do that in "data" because \[ isn't in the filename so they'll fail ###

  escaped=$(echo "$data" | sed 's/\[/\\[/g')
  #echo "$escaped"

  ### Avoid duplicates
  match=$(grep -i "$escaped" ./3-star-tmp.m3u)
  if [ -z "$match" ]
  then
   echo "$data" >> ./3-star-tmp.m3u
   ((i++))
  fi
 fi
done

i=1

while [ $i -le 45 ]
do
  file=$(sqlite3 ./clementine.db "select filename from songs where rating >= "0.8" and rating

  ### Clementine data encodes special characters and accent marks and stuff so I'm using
  ### Joel Parker Henderson's urldecode.sh to undo that: https://gist.github.com/cdown/1163649

  data=$(urldecode.sh "$file")
  if [ -f "$data" ]
  then
   ### Have to escape leading brackets because grep treated it as a range and would allow duplicates ###
   ### Can't do that in "data" because \[ isn't in the filename so they'll fail ###

   escaped=$(echo "$data" | sed 's/\[/\\[/g')
   #echo "$escaped"

   ### Avoid duplicates
   match=$(grep -i "$escaped" ./3-star-tmp.m3u)
   if [ -z "$match" ]
   then
    echo "$data" >> ./3-star-tmp.m3u
    ((i++))
   fi
  fi
done

i=1

while [ $i -le 25 ]
do
  file=$(sqlite3 ./clementine.db "select filename from songs where rating >= "0.6" and rating

  ### Clementine data encodes special characters and accent marks and stuff so I'm using
  ### Joel Parker Henderson's urldecode.sh to undo that: https://gist.github.com/cdown/1163649

  data=$(urldecode.sh "$file")
  if [ -f "$data" ]
  then
   ### Have to escape leading brackets because grep treated it as a range and would allow duplicates ###
   ### Can't do that in "data" because \[ isn't in the filename so they'll fail ###

   escaped=$(echo "$data" | sed 's/\[/\\[/g')
   #echo "$escaped"

   ### Avoid duplicates
   match=$(grep -i "$escaped" ./3-star-tmp.m3u)
   if [ -z "$match" ]
   then
    echo "$data" >> ./3-star-tmp.m3u
    ((i++))
   fi
  fi
done

cat ./3-star-tmp.m3u | sort -R > /Volumes/Filestore/CDs/playlists/3\ Stars\ +.m3u

rm ./3-star-tmp.m3u