Plex and the not-imported mp3 ratings

Plex is nice, but some things are incomprehensible. One is, your lovingly rated mp3 songs lose their rating when imported into Plex. And no way to fix this (except re-rating manually). Until now. So (on linux) change your path to /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases. There we admire the structure of the database with help of sqlite3 com.plexapp.plugins.library.db. After some digging around with .table and .schema and looking into tables while changing the rating of a test song we find in a current version of Plex the offending table metadata_item_settings. There the rating is saved in the rating column (10 = 5 stars, 8 = 4 stars, …). Not so nice, songs only have an entry in this table, when some of their metadata has been changed already (I have not investigated further). So the job is: find a song to be rated in this table and set the rating column accordingly or if not found, insert a row in this table with the rating. Inserting is even less nice as we don’t know nothing about the meaning of the other columns (especially updated_at). So from here on, you are on your own. The key seems to be the guid column which is linked to the metadata_items table which is linked to media_items which is linked to media_parts table where we find the file column containing the absolute path to the mp3 file. So from knowing the path to the file we now know the way to get to the rating.

What we have to do is create for each song a script consisting of

UPDATE metadata_item_settings SET rating = 6 WHERE guid = (SELECT d.guid FROM metadata_items d, media_items i, media_parts p WHERE d.id = i.metadata_item_id AND i.id = p.media_item_id AND file = ‘/Music/Yello/Zebra/08 Tremendous Pain.mp3’);

INSERT INTO metadata_item_settings (account_id, guid, rating, created_at, updated_at, changed_at)
SELECT 1,guid,6,date(‘now’),date(‘now’),14000 FROM metadata_items d, media_items i, media_parts p WHERE d.id = i.metadata_item_id AND i.id = p.media_item_id AND  file = ‘/Music/Yello/Zebra/08 Tremendous Pain.mp3’ AND (Select Changes() = 0);

We try an UPDATE and if this hasn’t worked out (the Changes()-part) we do an INSERT. So the path and rating (6) is twice in the script, 14000 is an updated_at higher than any of my values, you have to find your own.  Pasted into sqlite3 the rating shows in Plex.

As I created the script from the Netjukebox database here my SELECT to create the statements as template for your part of the job (if you have a list of your rated mp3s you have to massage for example Excel to spill out the stuff above for each song). The informed may ask Netjukebox? There are no ratings as well. I’ve redone the import for Netjukebox to read mp3-tags and create favorite lists with the rating. But that’s another story.

SELECT concat(“update metadata_item_settings set rating = 6 where guid = (SELECT d.guid FROM metadata_items d, media_items i, media_parts p WHERE d.id = i.metadata_item_id AND i.id = p.media_item_id AND file = ‘/Music/”, replace(relative_file,””,”””), “‘);
INSERT INTO metadata_item_settings (account_id, guid, rating, created_at, updated_at, changed_at)
SELECT 1,guid,6,date(‘now’),date(‘now’),14000 FROM metadata_items d, media_items i, media_parts p WHERE d.id = i.metadata_item_id AND i.id = p.media_item_id AND file = ‘/Music/”, replace(relative_file,””,”””), “‘ AND (Select Changes() = 0);
“) FROM favorite f, favoriteitem i, track t WHERE f.favorite_id = i.favorite_id AND i.track_id = t.track_id AND name = ‘3 Stars’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s