I have a couple of playlists that uses track ratings as the criterion, and have recently moved from iTunes to Plex (but on different machines).
So when Plex found all my music there were no ratings, so I did some poking around in the database schema to work.
I installed sqlite3 on my PMS server (FreeBSD).
Basically, here’s the SQL statement to add a rating for a given track as specified by the filename. It won’t add a rating if one exists already.
INSERT INTO metadata_item_settings (account_id,guid,rating) SELECT _ACCOUNT_,(SELECT metadata_items.guid FROM metadata_items LEFT JOIN metadata_item_settings ON metadata_items.guid = metadata_item_settings.guid LEFT JOIN media_items ON metadata_items.id = media_items.metadata_item_id LEFT JOIN media_parts ON media_items.id = media_parts.media_item_id WHERE media_parts.file LIKE '_FILENAME_',_RATING_ WHERE NOT EXISTS (SELECT 1 FROM metadata_item_settings WHERE guid = (SELECT metadata_items.guid FROM metadata_items LEFT JOIN metadata_item_settings ON metadata_items.guid = metadata_item_settings.guid LEFT JOIN media_items ON metadata_items.id = media_items.metadata_item_id LEFT JOIN media_parts ON media_items.id = media_parts.media_item_id WHERE media_parts.file LIKE '_FILENAME'));
Replace _RATING_ with a number between 0 and 10 and replace _FILENAME_ (twice) with the location of the file where PMS found it, and _ACCOUNT_ with the user’s account number (mine was 1).
The reason I use the file name is that I have script that parses an exported iTunes library (XML), gets the rating for each file, translates the filename to match the one PMS sees and runs the SQL statements.
Now I have a “Rated” playlist that automatically updates when I rate tracks on my Plex clients. The most exciting bit is that using Prism on iOS I can CarPlay my music sitting on my home server and just shuffle the playlist. Road-trip heaven.
but what we “really” need, is a script, which
loops recursive over a given directory,
reads the rating from the RATING tag of each FILE
puts the processed FILENAME into your SQL statement and
updates the Plex database.
That’s a great idea. I’d not thought of that. My workflow is:
Rate on phone and sync with iTunes
Export library to XML
Copy XML file to Plex server
Run script that parses the XML for filenames and iTunes ratings/loved flag, munges the filenames and updates the database
What I should be doing is:
3. Run script that parses the XML for filename and and ratings and updates the music files’ tag with the rating
4. Run script that reads the music files’ tags and updates the database
I wonder if Kodi can rescan its own media sources and update its DB using a rating tag.
In any case the new workflow means writing some and reading ALL of my files’ tags each run. It’d certainly give the disks a workout.
Reading the tag from the file is not a trivial/ fast task. To go through all your files everytime could take a while. This is 1 reason plex doesn’t already do this.
Now I think about it I could either create a table of each file’s mtime and only read the tags of those that had changed. Or if I run a periodic scan only read those files whose mtime is after the last scan.
Doesn’t PMS reads the files when indexing the first time (and when the user choses to refresh all metadata) ?
Isn’t this time a “good time” to read the ratings as well as all other information?
I see in the logs, PMS is using FFMPEG/FFPROBE to determine tags from the files.
I have written (poorly) autoplaylist scripts to get a lot of information from files this way and it worked (according to my scripting skills) … good.
It surely takes ages to complete, no question here
Yes it does. I got hung up on how to edit the data, I missed the OP’s comment
So when Plex found all my music there were no ratings
For my comment above, I thought the question was about having Plex constantly scanning files to keep the ratings in the file synced to PMS. I’m pretty sure PMS should be picking up the ratings on the initial scan. I’ll have to double check.
