Updating the PLEX database outside Plex - how?

[HeartWare42]

I would like to do a little batch update of the Plex DB using direct Sqlite (updating summary and collection data on several hundred entries).

I am aware that I need to stop the Plex Server before manipulating the database file directly, but would like to know if the following statement (executed on the database file - which one is it? Name?):

update metadata_items set summary=’’, tags_collection=’|’ where title=’’

is enough to update these two fields? Especially the collection data, as collections are now more than just simple registrations with the new collection view. Are there any other tables that need to be updated when manipulating collections directly in the database?

I am using Plex Server 1.12.0.4829 on a Synology NAS if that has any bearing on the matter.

[plex-linux]

so I have not done this on summary data but, I have done it on collection data. I don’t know how much experience you have with databases but be careful or you will be starting over.
I would recommend that you not only stop the plex server but that you make a copy before working on the actual database.

by the way here is my experience with doing this (it may be of some help in modifying collection tags for media items):
forums.plex.tv/discussion/210603/i-would-like-to-update-the-collection-tags-for-my-movies-directly-not-in-plex#latest

Please be careful and understand what you are doing before attempting anything.

these are the sqlite statements i used:

“INSERT INTO taggings (id,metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (176,2,126,4,’’,’’,datetime(‘now’),’’ );”.

this can be modified to drop the id, this actually makes it easier as you don’t need to worry about unique vales and sqlite will auto increment the id column/field

“INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2,126,4,’’,’’,datetime(‘now’),’’ );”.

[HeartWare42]

Thank you - that helps a lot. I have one question, however: In the taggings table, you set the ‘index’ field to 4. How is that value determined?

[plex-linux]

take a look at this example:

This is the final sql code (small sample) I have used to update my movie collection tags :
–2137 1100010100 1100010100 1100010100
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45590,1,‘’,‘’,datetime(‘now’),‘’);
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45591,2,‘’,‘’,datetime(‘now’),‘’);
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45595,3,‘’,‘’,datetime(‘now’),‘’);
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45597,4,‘’,‘’,datetime(‘now’),‘’);
UPDATE metadata_items SET user_fields = ‘lockedFields=16’ WHERE ID =2137;
–1597 0000000010 0000000010 0000000010
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (1597,45610,1,‘’,‘’,datetime(‘now’),‘’);
UPDATE metadata_items SET user_fields = ‘lockedFields=16’ WHERE ID =1597;
–1558 0000100010 0000100010 0000100010
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (1558,45594,1,‘’,‘’,datetime(‘now’),‘’);
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (1558,45610,2,‘’,‘’,datetime(‘now’),‘’);
UPDATE metadata_items SET user_fields = ‘lockedFields=16’ WHERE ID =1558;
–2722 0100000000 0100000000 0100000000

so movie (agent cody banks represented in the database by 2137) 2137 and tag 45590(45590 = ash-ama short for my two dauthers ) but in this case i had four collection tags (ash-ama and disney,etc) so i run the same sql statement, incrementing the index tag for each collection tag

just info for me to check the process
–2137 1100010100 1100010100 1100010100

first collection tag for movie 2137 agent cody banks
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45590,1,‘’,‘’,datetime(‘now’),‘’);

second collection tag for movie 2137 agent cody banks
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45591,2,‘’,‘’,datetime(‘now’),‘’);

third collection tag for movie 2137 agent cody banks
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45595,3,‘’,‘’,datetime(‘now’),‘’);

fourthcollection tag for movie 2137 agent cody banks
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (2137,45597,4,‘’,‘’,datetime(‘now’),‘’);

lock the collection tags so plex agent does not overwrite them
UPDATE metadata_items SET user_fields = ‘lockedFields=16’ WHERE ID =2137;

just info for me to check the process
–1597 0000000010 0000000010 0000000010

**first collection tag for movie 1597 (some movie) by the way this movie only had one tag **
INSERT INTO taggings (metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (1597,45610,1,‘’,‘’,datetime(‘now’),‘’);

lock the collection tags so plex agent does not overwrite them
UPDATE metadata_items SET user_fields = ‘lockedFields=16’ WHERE ID =1597;

by the way I generated the sqlite statements using some php code so it was all automated

[HeartWare42]

Thanks. Just to finish off, my further investigation has revealed the following, documented here for use by others who may be in similar situation:

1. Index appears to be 0-based, not 1-based (may not matter in the end)
2. Index is “per-tag-type”, ie. all Director tags (tag_type=4) have different index numbering from all Collection tags (tag_type=2).
3. In the table tags (which contain ALL tage for ALL media in the database), the field tag_type can have the following values (these are just the ones I have determined):
   • 1 = Genre
   • 2 = Collection
   • 4 = Director
   • 8 = Country
4. The user_fields field in metadata_items (which contains all the media in the database, but not the individual files, ie. if there are 2 versions of “Cody Banks”, then only 1 entry here - the “master entry”) can contain a “lockedFields=x|y|z” value, where x, y and z are field numbers that are locked. The field numbers that I have determined are as follows:
   • 1 = Title
   • 2 = Sort Title
   • 3 = Original Title
   • 4 = Studio
   • 5 = Rating
   • 6 = Tagline
   • 7 = Summary
   • 8 = Content Rating
   • 13 / 14 = Originally Available
   • 15 = Genres
   • 16 = Collections
   • 17 = Directors
   • 18 = Writers
   • 24 = Countries
5. The metadata_type field specifies the type of this metadata_item. I have determined the following values:
   • 1 = (Home) Movie
   • 2 = TV Show
   • 3 = Season
   • 4 = Episode(?)
   • 8 = Music
   • 9 = Music
   • 10 = Music
   • 18 = Collection
   I do not (yet) know the reason for the multiple values used in library type Music. For Library Type 3 and 4, the “parent_id” field links to the TV Show (for type 3) or season (for type 4), field “id” in the same table (metadata_items).

Feel free to add to the lists any new values for the tag_type, metadata_type, and lockedfields that you may have determined… I will then add to the list to keep an updated documentation for future use.

HOWEVER: Please beware that these values are subject to change without notice. They are valid as of Plex Server v1.12.0.4829 (March 2018).

[plex-linux]

good luck, seems that you have what you need.

[HeartWare42]

Yeah - and it ALMOST works

I can update the files’ information, incl. collections, but the changes are not reflected in the Collections view (actually, I can’t seem to find where the data for the collections - thumbnail and description, for example - are stored).

I had to re-scan a library from scratch after moving it to a new location (that’s what I am trying to “fix”), and all the collections are still visible after re-scanning (but without contents, of course). After my update, the collections are still there in the collection view, but still with 0 movies in them, so there must be a link table somewhere that I don’t update.

I have verified that I insert the proper (I think) entries in the “taggings” table using the following SQL:

SELECT T.id,
       I.title,
       T2.tag,
       T.created_at
  FROM taggings T
  inner join Tags T2 on T2.id=T.tag_id
  inner join metadata_items I on I.id=T.metadata_item_id
  where I.library_section_id=7 and T2.tag_type=2
  order by T.created_at DESC

If anyone could assist me in this, it would be greatly appreciated…

[daeks]

Use https://github.com/pkkid/python-plexapi - it communicates with “official” API

[HeartWare42]

Thank you for joining the discussion, but unfortunately that doesn’t help me. I can’t see how the collection data is updated in the database (or I can’t figure it out from the py files).

[plex-linux]

you may want to try pinging @MovieFan.Plex as he has in the past written programs that interact with the plex database (before he was a plex employee?, i think) he may have some insight in where the tags are located that you need.

[HeartWare42]

Thank you - I have sent him a message. Let’s see what he replies…

[anon18523487]

@HeartWare42 said:
Thank you - I have sent him a message. Let’s see what he replies…

Got your message. This is not something I’ve not looked into doing manually. A hint I can offer is that the collection item is also an entry in the metadata_items table. You need to generate this item as well. This can be tricky as it will require you to generate some data. You can check the database for any current collections you have to see what it looks like.

[HeartWare42]

Thank you for your insights, @“MovieFan.Plex”. I already am updating the tags_collection field in metadata_items (as well as creating records in tags and taggings tables). But there must be some data somewhere that I haven’t found, as I can’t seem to find the text that I can enter as description of a collection in the collection view, and perhaps also a link table between this table (with the description) and the metadata_items IDs. Or perhaps some values in the tags/taggings table(s) that aren’t immediately obvious…

[anon18523487]

Not the tag_collection field. An actual entry in metadata_items, just as if it was a movie.

If you already have some collections, you can find these with:

select * from metadata_items where guid like ‘collection:%’;

[HeartWare42]

Ahhh! Okay. That’s probably the thing I was looking for (the way to link the Collection tags to a Collection). So the Collection is now also a “mediadata_item”. I’ll look into this tomorrow after work and let you know the results.

Thank you for your help…

[anon18523487]

Yes. The tag/taggings tables are just for the label. This is also used for the old collection filter. You need an entry in metadata_items for it to show up in the new “Collection View”. This is also where you add the summary and custom poster/backgrounds.

[HeartWare42]

@“MovieFan.Plex”

Fortunately, all the Collection MetadataItems still exists in my database, but just for future reference:

If I need to create a Collection MetadataItem, I am unsure as to what to put in the following fields in the metadata_items table:

absolute_index => Link to matching collection entry in table [tags]
hash
changed_at
resources_changed_at

Could you explain these fields’ intended values for a newly created item?

My Plex is currently recording, so I haven’t tried my update yet. Will report back later…

[anon18523487]

absolute_index => Link to matching collection entry in table [tags]
Correct

hash
This is a SHA256 hash of the guid. This is used to identify the disc folder where poster and background images for this item are stored.

changed_at
Date when this entry was last changed

resources_changed_at
Date when the last tagged item in the collection changed

[HeartWare42]

@“MovieFan.Plex”:
Once again, thank you. I have now completed my update and everything seems to be in order. All the collections show the correct number of “attached” movies, and when I click on them, I get the movies I expect listed in the Collection Detail View. I’ll write up a documentation for future reference over the weekend…

A couple of questions, though:

The changed_at and resources_changed_at are integer fields - not datetime fields. How are the integer values used here computed from a datetime? I currently use datetime(‘now’) for many of the datetime fields when creating records. Is there a similar SQLite function that I can use for these integer time stamp fields, or do I need to calculate them in code?

The SHA256 value - is it over the complete guid database field (“collection://5ae581bf-8253-4165-9f1e-0a19b638822e”), or only of the guid part (“5ae581bf-8253-4165-9f1e-0a19b638822e”)? Is there a built-in function in SQLite to compute this, or do I need to calculate it in code?

[anon18523487]

Oh sorry on the date things. I was going off memory. Those are actually indexed values (last +1). It’s intent is to track what changes are connected to other changes, but they’re not really used now.

The hash is based on the entire content. There is no sql function I’m aware of. You’ll have to generate that programmatically.