pimseb wrote: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
Ok - I've had a look at this.... No great education to it really, just Googling and trial / error.
First of all, create two playlists in the Subsonic UI. I'll call one "Starred" and the other "Random 100" here, but you can call them whatever you want to.
Then go to yoursubsonicserver/db.view and do...
- Code: Select all
SELECT * FROM PLAYLIST
Make a note of the two "ID" numbers for your new playlists.
Then type
- Code: Select all
SELECT path FROM media_file where TYPE='MUSIC' limit 100
This will give you an idea of the file path syntax that you will need for the next step.
The following block of code will clear the specified playlist.
Then it will populate the playlist with all of your starred media files in a path which CONTAINS the path text that you enter, so it should find everything in subdirectories etc.
Obviously you'll need to remove the asterisks and fill in your own text.
- Code: Select all
DELETE FROM playlist_file WHERE PLAYLIST_ID = *YOUR STARRED PLAYLIST NUMBER*;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID) SELECT media_file_id, '*YOUR STARRED PLAYLIST NUMBER*' FROM starred_media_file join media_file on media_file.id = starred_media_file.media_file_id where path LIKE '*PATH TO YOUR MUSIC*%';
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=*YOUR STARRED PLAYLIST NUMBER*;
The following block of code will take a random selection of 100 items from your "Starred" playlist and insert them into your "Random 100" playlist.
- Code: Select all
DELETE FROM playlist_file WHERE PLAYLIST_ID = *YOUR RANDOM 100 PLAYLIST NUMBER*;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID) SELECT media_file_id, '*YOUR RANDOM 100 PLAYLIST NUMBER*' FROM (SELECT media_file_id FROM playlist_file where playlist_id = *YOUR STARRED PLAYLIST NUMBER*) order by rand() limit 100;
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=*YOUR RANDOM 100 PLAYLIST NUMBER*;
As for the automation stuff....
What I do is paste my composite SQL commands into a text file. Lets call it "databasequeries.txt"
I then have a shell script which runs some commands similar to below....
- Code: Select all
databasequeries=$(cat PATH/TO/MY/DATABASEQUERIESFILE)
sudo curl --cookie PATH/TO/MY/cookies.txt --data-urlencode query="$databasequeries" MYSERVERADDRESS/db.view
Not sure if you need the "sudo" bit, and probably all depends on your permissions/ownership etc. I'm not great with UNIX stuff.....
I then schedule the script to run as a UNIX "cron" job at 2am each day.
As for the cookie file.... You need to give the script a way of being able to log into your Subsonic server, as it can't enter the user/pass on your behalf. It's been a while since I did it, but I think I used a Chrome browser extension to capture the cookie of my login to my Subsonic server as a text file. I then saved the text file next to my shell script and specified its location in the path above.
It's late here.... hope that all makes sense?