SQL - what is correct table & column for genre tag?

markmarz · March 9, 2017, 12:33pm

I’ve added ‘Musical’ to hundreds of Genre tags. Only a handful appear with this sql:

root@omv:/media/c32f97f7-2d5a-4dc1-905f-f328799918f4/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases# sqlite3 com.plexapp.plugins.library.db "SELECT title,tags_genre FROM metadata_items WHERE library_section_id=3 and tags_genre = 'Musical' ORDER BY title;"
Broadway Melody of 1940|Musical
Carolina Blues|Musical
Everything I Have Is Yours|Musical
The Student Prince|Musical
root@omv:/media/c32f97f7-2d5a-4dc1-905f-f328799918f4/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases#

Here’s an example of how I set the tag:

And here’s a sampling of successful matches using the Web client:

There must be someplace else in the database where this information is stored, but I haven’t been able to find it.

Thanks,
Mark

markmarz · March 9, 2017, 12:54pm

Well, I’m getting closer. The metadata_items table doesn’t conform to 1st normal form! Unbelievable.

This gets back more titles, but still only 69 out of 268:

Is there more trickery required or another place this data exists?

plex-linux · March 9, 2017, 9:04pm

I can help you a little with this. I had a similar problem about a year ago. The tags are actually in two places, one is where you have found it another is here. (mine was a little different as i was actually inserting tags from a flat file but the part that is the same is that tags are stored in two different areas

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’),‘’ );”.

here is the complete post:

forums.plex.tv/discussion/210603/i-would-like-to-update-the-collection-tags-for-my-movies-directly-not-in-plex#latest

here is the info that i believe applies to you:

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.

hopefully this is helpful to you.

by the way, I like your classic musical collection as I have many of the same

markmarz · March 9, 2017, 9:10pm

@plex-linux said:
I can help you a little with this. I had a similar problem about a year ago. The tags are actually in two places, one is where you have found it another is here. (mine was a little different as i was actually inserting tags from a flat file but the part that is the same is that tags are stored in two different areas

Thanks @plex-linux, that’s very helpful. But your post has convinced me to forget about it. hah-hah!!

Thanks again,
Mark

Topic		Replies	Views
Updated 'tags_genre' but old (non-existent) entries still showing? Dev/API Corner other-dev	3	131	December 21, 2019
Query database for Movie Genre/Tags General Discussions search	6	656	May 9, 2019
i would like to update the collection tags for my movies directly (not in plex). General Discussions	10	301	January 7, 2020
Genre information in database Metadata & Adding Files server-windows	2	160	July 22, 2020
Adding Tags through SQL - changes get lost Desktops & Laptops server-linux	3	394	March 13, 2019

SQL - what is correct table & column for genre tag?

Related topics