Subsonic smart playlists via db / useful db commands

Tutorials, tips and tricks.

Moderator: moderators

Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Tue Jan 03, 2017 3:49 pm

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 :D



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
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby acroyear » Tue Jan 03, 2017 9:49 pm

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.
--
Joe Shelby
http://subfireplayer.net/
User avatar
acroyear
 
Posts: 507
Joined: Wed Mar 27, 2013 8:05 pm
Location: Northern, VA

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Wed Jan 04, 2017 12:31 pm

acroyear wrote: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.
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby acroyear » Wed Jan 04, 2017 1:54 pm

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
--
Joe Shelby
http://subfireplayer.net/
User avatar
acroyear
 
Posts: 507
Joined: Wed Mar 27, 2013 8:05 pm
Location: Northern, VA

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Tue Feb 21, 2017 3:18 pm

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.
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby eddik » Wed Mar 08, 2017 11:15 am

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?
eddik
 
Posts: 5
Joined: Wed Mar 08, 2017 11:12 am

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Mon Mar 13, 2017 4:16 pm

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!
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby eddik » Tue Mar 14, 2017 11:54 am

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..
eddik
 
Posts: 5
Joined: Wed Mar 08, 2017 11:12 am

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Tue Mar 14, 2017 12:33 pm

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
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Tue Apr 25, 2017 11:52 am

eddik wrote: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 :D
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby eddik » Wed Apr 26, 2017 10:42 am

Thank you, that worked!
eddik
 
Posts: 5
Joined: Wed Mar 08, 2017 11:12 am

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Wed May 03, 2017 7:28 am

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
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Fri Sep 22, 2017 3:32 pm

eddik wrote: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)
Last edited by mwpmorris on Fri Sep 22, 2017 3:37 pm, edited 1 time in total.
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm

Re: Subsonic smart playlists via db / useful db commands

Postby mwpmorris » Fri Sep 22, 2017 3:36 pm

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;
mwpmorris
 
Posts: 10
Joined: Tue Jan 03, 2017 2:21 pm


Return to Tutorials

Who is online

Users browsing this forum: No registered users and 1 guest