Subsonic smart playlists via db / useful db commands

[mwpmorris]

Hi,

I've been evaluating a few different streaming server options over the past few months. I'm coming over from iTunes / syncing which is becoming too cumbersome for me. I wanted a service that would give me one of iTunes more useful features - smart playlists. This is not available as standard in Subsonic, but with a bit of SQL manipulation I managed to get a solution.

I took inspiration from this Reddit thread https://www.reddit.com/r/subsonic/comments/49vhsh/example_playlist_management_using_the_db/
The commands relate to Madsonic however, so I had to figure out the correct syntax for the db version that Subsonic uses.

Personally, I have 8 playlists: "Starred", "Last 1 month", "Last 3 months", "Last 6 months", "Last 12 moths", "Last 24 months", "Music videos" and "Synced".

I manually created the playlists in the Subsonic GUI and then typed the following into mysubsonicserver.com/db.view

Code: Select all

SELECT * FROM PLAYLIST

This gives the index numbers of my playlists, which I then need to use for subsequent commands to populate these playlists.

This first one populates a playlist called "Starred" (playlist_id 0) with all of my starred media files:

Code: Select all

DELETE from PLAYLIST_FILE where playlist_id = 0
insert into PLAYLIST_FILE (playlist_id, media_file_id) select 0, media_file_id from starred_media_file;

Then variations of the following are issued to add items into a playlist by the "modification" date of the source file. "The 30 + 1" bit refers to the amount of days prior to todays date to include in the playlist. I did write an additional shell script to make sure all my file mod dates were equal to the date that they were added to iTunes. I'll include that later on:

Code: Select all

DELETE FROM playlist_file WHERE PLAYLIST_ID = 1;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '1'
FROM media_file WHERE DATEDIFF('day', "CHANGED", CURRENT_DATE) <= 30 + 1 AND TYPE='MUSIC' OR DATEDIFF('day', "CHANGED", CURRENT_DATE) <= 30 + 1 AND TYPE='VIDEO'
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=1;

As you can see, I include music videos in my playlists. You can delete that if needs be.

The next one copies all video files into my "music videos" playlist (index 6)

Code: Select all

DELETE FROM playlist_file WHERE PLAYLIST_ID = 6;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '6'
FROM media_file WHERE TYPE='VIDEO'
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=6;

I did have an issue whereby it was difficult for me to get all of my previously starred (or "loved" in iTunes) items to show up as "starred" in Subsonic. It's easy in Madsonic - import a .m3u playlist, "select all" then "star all" (or similar). Not so easy in Subsonic, so it needed additional db work:

Code: Select all

DELETE FROM starred_media_file;
INSERT INTO starred_media_file (MEDIA_FILE_ID, USERNAME, CREATED) SELECT MEDIA_FILE_ID,'your_user_name',NOW FROM playlist_file WHERE PLAYLIST_ID = 10;


This command clears all currently "starred" items, then takes the items from a playlist (id 10 in this instance) and "stars" them.

Hope this helps someone out. Took me a long time to figure out and I could have done with a tutorial like this



Shell script below to scan iTunes XML file and change created / modified dates according to "added" date in the iTunes XML. I use on OSX with a symlink mounted iTunes library residing on a NAS.

Use at your own risk!!!

Paste the script into a text file, save it as script.sh. Then go to terminal and

Code: Select all

sh script.sh path_to_your_itunes_library_xml

Code: Select all

#!/bin/bash

counter=0
while read line
do
    name=$line

    fulldate=`echo $name | sed -n 's:.*\<key\>Date\ Added\<\/key\>\<date\>\(.*\)\<\/date\>.*:\1:p' | cut -b 1-10` # finds the date text between the "<key>Date Added</key><date>" and "</date>" tags
    fullpath=`echo $name | sed -n 's:.*\<key\>Location\<\/key\>\<string\>file\:\/\/\(.*\)\<\/string\>.*:\1:p'` # filds the filepath relevant to the date above between the "<key>Location</key><string>file://" and "</string>" text
   
   if [ ! -z "$fulldate" ] # If the variable is NOT empty
   then
      year=`echo $fulldate | cut -b 1-4` # process the date text into workable day, month, year
      month=`echo $fulldate | cut -b 6-7`
      day=`echo $fulldate | cut -b 9-10`
    fi

    if [ ! -z "$fullpath" ] # If a valid filepath has been found
    then
       parsedpath=`printf "%b\n" "${fullpath//%/\\x}" | sed 's/#38;//g'` # converts the %20 and others to a form that bash recognises, the "sed" deals with "&" characters which the conversion misses. The "g" option is global - replaces all instaces       
        setfile -d "$month/$day/$year 01:00:00" -m "$month/$day/$year 01:00:00" "$parsedpath"
        # "setfile" above is a Mac OSX command which enables full modification of the "created" and "modified" dates. You can use the "touch" command as an alternative - commented out below      
        #touch -t "$year""$month""$day"0100.00 '$fullpath'
        #touch -t "$year""$month""$day"0100.00 "$parsedpath"

        counter=$((counter+1))
      # echo $counter, "PATH:" $parsedpath # uncomment these to get verbose output
        # echo "DAY:"$day "MONTH:"$month "YEAR:"$year
    fi
done < $1

echo Processed "$counter" files

[acroyear]

Interesting stuff. I'll 'bookmark' this, and maybe someday add some code into SubFire Radio (a "smart" playlist randomizer, but only provides features available through the public API) that can read/write to db.view. Mod time means little to me for my collection and interests, but it would make a good experiment to try to add before getting more detailed in other parts of the DB that aren't yet exported in the API.

If you haven't seen subfire radio, it is at http://subfireplayer.net/radio/ .

----

Bah. Updated: db.view doesn't send a CORS header, nor does it acknowledge the REST api's parameters nor the existing session from another tab (which I can use to get past any HTTPS cert issues), so I can't use this as an option for adding new capabilities now.

[mwpmorris]

Interesting stuff. I'll 'bookmark' this, and maybe someday add some code into SubFire Radio (a "smart" playlist randomizer, but only provides features available through the public API) that can read/write to db.view. Mod time means little to me for my collection and interests, but it would make a good experiment to try to add before getting more detailed in other parts of the DB that aren't yet exported in the API.

If you haven't seen subfire radio, it is at http://subfireplayer.net/radio/ .

----

Bah. Updated: db.view doesn't send a CORS header, nor does it acknowledge the REST api's parameters nor the existing session from another tab (which I can use to get past any HTTPS cert issues), so I can't use this as an option for adding new capabilities now.

Hi, yes I do remember finding subfireplayer in my initial research trying to find a solution. The "date added" thing is important for me. I'm pretty busy and tend to pick up music on the run after hearing bits on the radio etc. I was finding that things often disappeared in my large library. Using the dated playlists helps a lot with that.

[acroyear]

Now, I might be able to simulate that by looking through the most-recent-albums list, which I *think* is modified even if a song is added to an existing album, and then filtering the songs within it (the song entries have the created date within) to get you that kind of a list. Last year and two might still be a somewhat expensive operation, but certainly possible. I'd have to know to stop looking for more albums once I ran out of songs inside the date range.

Lemme think on that for a bit, thanks for the brainstorming idea...

J

[mwpmorris]

If anyone is interested, I've automated my database commands so that they update every day - furthering the iTunes like "live updating" feature.

Code: Select all

curl --cookie /PATH/TO/YOUR/COOKIEFILE.txt -d query="SQL QUERY - URLENCODED" http://path.to.subsonic.server:port/db.view

I run the above command as a cron job every day so that dated playlist stay updated.

You need to capture the cookie file with a Chrome extension in order to avoid authentication issues to your server.

[eddik]

This is very interesting, I have been testing with different playslists based on Genre, Age, Playcount etc.

Now I would like to automate my requests. How do you make the URLENCODED SQL QUERY?

[mwpmorris]

Hi, sorry for the late reply - I don't seem to be getting notifications when a response is posted.

I used an online tool to URL encode my database queries. Think it was http://www.urlencoder.org/
Good luck!

[eddik]

Got it to work. Great stuff! Thanks!

I made a few playlists based on Genre, Play_count, etc.

Have you tested nested queries (or whatever the name is in sql)? I was thinking of a playlist which finds albums with only 2-3 songs on it.

SELECT * FROM ALBUM WHERE SONG_COUNT < 4 ORDER BY PLAY_COUNT DESC limit 500;

But I don't know how to link this to the media_file table? I am unfortunately not so good in SQL..

[mwpmorris]

Hmmm... I'm a beginner at SQL. All the info that I found above was as a result of a lot of Googling and trial & error.

I always Googled "HSQLDB" followed by my query, so I found this relating to nested queries, seems relevant: http://stackoverflow.com/questions/15781962/how-to-execute-a-nested-query-in-spring-hsqldb

One thing to look out for is a lot of info that I found online relates to a newer version of HSQLDB than Subsonic runs. So like I said, a lot of trial & error.

Good luck, and post back with your results. I'd like to expand this hack if possible.

[mwpmorris]

Got it to work. Great stuff! Thanks!

I made a few playlists based on Genre, Play_count, etc.

Have you tested nested queries (or whatever the name is in sql)? I was thinking of a playlist which finds albums with only 2-3 songs on it.

SELECT * FROM ALBUM WHERE SONG_COUNT < 4 ORDER BY PLAY_COUNT DESC limit 500;

But I don't know how to link this to the media_file table? I am unfortunately not so good in SQL..

Hi, I've been playing around with your question this morning - it's been on the back of my mind since you posted it. I think I've found a solution using the HSQLDB "join" function.

Code: Select all

SELECT media_file.id FROM ALBUM JOIN media_file on album.path = media_file.parent_path WHERE SONG_COUNT < 4 ORDER BY PLAY_COUNT DESC limit 500

Should return the id for the media files which are present on an album with less than 4 tracks. Hope it helps

[eddik]

Thank you, that worked!

[mwpmorris]

I've done a bit of extra work on this. The following syntax can be used to avoid "media_file" items if present in another specified playlist (number 9 in this example):

Code: Select all

AND ID NOT IN (SELECT media_file_id FROM playlist_file where playlist_id = 9)

In context:

Code: Select all

DELETE FROM playlist_file WHERE PLAYLIST_ID = 5;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '5'
FROM media_file WHERE DATEDIFF('day', "CHANGED", CURRENT_DATE) <= 730 + 1 AND TYPE='MUSIC' AND ID NOT IN (SELECT media_file_id FROM playlist_file where playlist_id = 9) OR DATEDIFF('day', "CHANGED", CURRENT_DATE) <= 730 + 1 AND TYPE='VIDEO' AND ID NOT IN (SELECT media_file_id FROM playlist_file where playlist_id = 9)
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=5;

The above selects all music and video items added in the past 2 years and adds to playlist "5" while avoiding all items which are present in the playlist with id "9". Playlist "9" is my "Kids music" playlist for my 6 year old. This way I can have dated playlists as my examples above which avoid all the Disney music that he likes.

[mwpmorris]

This is very interesting, I have been testing with different playslists based on Genre, Age, Playcount etc.

Now I would like to automate my requests. How do you make the URLENCODED SQL QUERY?

I've tweaked my script a bit and found out that you can urlencode your sql query in-line with the cURL command.

My syntax is:

Code: Select all

sudo curl --cookie PATH/TO/MY/cookies.txt --data-urlencode query="$databasequeries" MYSERVERADDRESS/db.view

You might notice that I'm reading in my database queries from a separate text file which I find is easier to maintain, with the following command issued prior to the one above:

Code: Select all

databasequeries=$(cat PATH/TO/MY/DATABASEQUERIESFILE)

[mwpmorris]

Some further tweaking to the SQL commands....

The following will correctly fill in the "file_count" field in the "playlists" table with the new total of files contained in the playlist #5:

Code: Select all

update playlist set file_count=(select count(*) from playlist_file where playlist_id=5) where id=5;


The following fills in the "duration_seconds" field correctly in playlist #5:

Code: Select all

update playlist set duration_seconds=(select sum(duration_seconds) FROM playlist_file JOIN media_file on playlist_file.media_file_id = media_file.id WHERE playlist_id=5) where id=5;

The following updates the "changed" field in playlist #5:

Code: Select all

update playlist set changed=(current_timestamp) where id=5;

[pimseb]

This is a very interesting post. I'm new in subsonic, I installed it today. Thanks to mwpmorris and this post I've been able to take all my stared songs from Synology audio station and import them into Subsonic
I really don't know how you found all these database scripts but would it be possible to do the following :
- create a playlist with starred songs, of mp3s placed in a given parrent directory (and its subdirectories)
- take only 100 of these songs randomly and create a playlist to make it smaller than a full playlist
- automate this everyday, so when I go into my car, the playlist changes every morning. For this last step I think I might use the curl command you've posted. But I don't understand what is this "cookiefile"
Thank you again