I have edited the database (after stopping plex) and changed the tags both trough cli (example “sqlite> UPDATE metadata_items SET tags_collection = ‘clock|bat|cat|dog’ WHERE ID = 362;”) and through a sqlite editor. I can verify that the collection tags have been changed but when i go back into plex after restarting plex the tag changes are not there. However when I run a query i see that the changes are still in the database. What am I missing?
I can modify some fields in the database that show up in plex (for example I can modify the user_fields = ‘lockedFields=16’ and this locks the collection tag (tags_collection). I’m guessing that i need to modify the tag_collection and some other field possibly in another table
So I have been able to modify the tags as needed. They where indeed in a different table.
First I had to insert the collection tags into the ‘tag’ table.
Second, I had to insert the following “INSERT INTO taggings (id,metadata_item_id,tag_id,‘index’,text,thumb_url,created_at,extra_data) VALUES (176,2,126,4,’’,’’,datetime(‘now’),’’ );”.
After doing this the tag do show up in plex, however the tags do not show up in the table= metadata_items, colum=tags_collection. I am wondering if a refresh will fix this or will i need to modify the metadata_item table, tags_collection column.
obviously modifying the database in this manner is not supported and entail risk of corrupting the database. I would recommend doing this on a copy of the database and not work on the plex database directly.
“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’),’’ );”.
Just so you don’t think you’re talking to yourself. I am following along.
You’re basically adding 1 collection at a time. Why are you doing this outside of Plex? Identifying all the values you need to generate that SQL statement seems more work than using PlexWeb.
I have actually set up a separate sever with just a hand full of movies in it. I am only doing one at a time for testing (much easier to work with and less chance to mess it up). When I feel that i have gotten the process right, i will do it on my real server. As my movie database has around 2500 movies, i will be using a text file to import multiple collection tags for all the movies at one time. (originally I had tagged all the movies by hand through the Plex web. But had a issue (twice) with the database and decided to start from scratch (twice) and don’t want to enter the info by hand again for a third time) Also for work I use a lamp stack and figured it would be fairly easy to import all the tags from a csv file once I figured out the tables and column structures where the info was stored.
looks like my latest method has worked. I have been able to create the entries in the database that i needed. Only one thing is still puzzling me. What is the purpose of tags_collection in the metadata_items table. I does not appear to have any real use at this point, as any changes there does not seem to matter. I would like to know for sure that this tags_collection column/field is not needed/important, if any one know please respond. thanks
tags_collection is a summary of the tags. It is used in some cases as a quick lookup so you don’t need to run a join to identify the values. I’m not sure exactly where they are used.
thanks for the answer, I thought that might be the case
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
everything seems to have gone well ( the tags are all in place and locked so that updates don’t remove them.)
