Open burkasaurusrex opened 2 years ago
Sounds interesting. I'll look into it the next weeks and come back to you.
Had some free time so I started poking around on this for a bit. I'll document here as I learn more.
It looks like Plex stores the reviews in the taggings
table with this sqlite schema:
0|id|INTEGER
1|metadata_item_id|integer
2|tag_id|integer
3|index|integer
4|text|varchar(255)
5|time_offset|integer
6|end_time_offset|integer
7|thumb_url|varchar(255)
8|created_at|datetime
9|extra_data|varchar(255)
There's a lot of other types of rows in this table, but here's what a sample review row looks like:
2941514|274760|54280|8|With this fifth and final go-round, it's clear who the best Bond is. It's Craig, Daniel Craig.||||2022-07-11 02:07:46|at%3Aimage=rottentomatoes%3A%2F%2Fimage%2Ereview%2Efresh&at%3Alink=https%3A%2F%2Fwww%2Ebostonglobe%2Ecom%2F2021%2F10%2F06%2Farts%2Fno-time-die-007-is-craig-daniel-craig%2F&at%3Asource=Boston%20Globe
Corresponding rendered review:
Inferred mapping:
# | Field | Type | Note |
---|---|---|---|
0 | id | integer | Appears to be an auto increment ID |
1 | metadata_item_id | integer | Maps to ratingKey |
2 | tag_id | integer | Appears to be an ID of the contributor (e.g. a unique ID for Mark Feeney above) |
3 | index | integer | Appears to be the index of the order reviews should be rendered |
4 | text | varchar(255) | Review text |
5 | time_offset | integer | blank |
6 | end_time_offset | integer | blank |
7 | thumb_url | varchar(255) | blank |
8 | created_at | datetime | Unclear if this is time of the review or time added to the database |
9 | extra_data | varchar(255) | Url encoded string |
Url decoded example:
at:image=rottentomatoes://image.review.fresh&at:link=https://www.bostonglobe.com/2021/10/06/arts/no-time-die-007-is-craig-daniel-craig/&at:source=Boston Globe
Further investigation to do:
tag_id
maps to. See if it can be manipulated.WHERE
clause to identify reviews in taggings
table (there doesn't seem to be a tagging_type
column strangely enough). I'm guessing something like WHERE extra_data LIKE '%at\%3Aimage%' ESCAPE '\' AND extra_data LIKE '%at\%3Asource%' ESCAPE '\'
would work (and allow a missing link). Or looks like the Plex sqlite has FTS capabilities, might be able to use that instead.rottentomatoes://image.review.fresh
is stored. See if the image store can be manipulated to take other logos. If so, it would be easier to identify Plex-native reviews and delete them by: 1) copying the native images to a different name (e.g. image.RT.fresh
), 2) using new image in artificial ratings, 3) delete any review using original image.
8 created_at datetime Unclear if this is time of the review or time added to the database
Confirmed that this is the timestamp of when the review is inserted into the database (local time)
- [x] Find where
tag_id
maps to. See if it can be manipulated.
No surprise, tag_id
maps to the tags
table:
0|id|integer
1|metadata_item_id|integer
2|tag|varchar(255)
3|tag_type|integer
4|user_thumb_url|varchar(255)
5|user_art_url|varchar(255)
6|user_music_url|varchar(255)
7|created_at|datetime
8|updated_at|datetime
9|tag_value|integer
10|extra_data|varchar(255)
11|key|varchar(255)
12|parent_id|integer
The corresponding row to the example above:
id = 54280
metadata_item_id =
tag = Mark Feeney
tag_type = 10
user_thumb_url =
user_art_url =
user_music_url =
created_at = 2016-11-26 03:36:39
updated_at = 2016-11-26 03:36:39
tag_value =
extra_data =
key =
parent_id =
- [x] Make reliable
WHERE
clause to identify reviews intaggings
table (there doesn't seem to be atagging_type
column strangely enough). I'm guessing something likeWHERE extra_data LIKE '%at\%3Aimage%' ESCAPE '\' AND extra_data LIKE '%at\%3Asource%' ESCAPE '\'
would work (and allow a missing link). Or looks like the Plex sqlite has FTS capabilities, might be able to use that instead.
When joined with the taggings
table, the tags.tag_type
column is probably a more reliable way to identify reviews. Could potentially use tags.extra_data
to denote artificial reviews more reliably.
- [x] Find where
rottentomatoes://image.review.fresh
is stored. See if the image store can be manipulated to take other logos. If so, it would be easier to identify Plex-native reviews and delete them by: 1) copying the native images to a different name (e.g.image.RT.fresh
), 2) using new image in artificial ratings, 3) delete any review using original image.
Unfortunately, these images appear to be rendered by each client instead of served as an image from the server. So I don't think there's a reliable way to add / rename images.
The only two built-in reviewImage
s appear to be rottentomatoes://image.review.fresh
and rottentomatoes://image.review.rotten
.
However, image keys look similar to ratingImage
and audienceRatingImage
. Here's the list of images ratingImage
s I could find in the webapp javascript. I tested how each ratingImage
rendered as a reviewImage
and a few other permultations on web, iOS:
Image Key | Web | iOS | Note |
---|---|---|---|
rottentomatoes://image.rating.certified | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.rating.ripe | :x: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.rating.fresh | :heavy_check_mark: | :x: | Inferred alias of *.ripe |
rottentomatoes://image.rating.rotten | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.rating.upright | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.rating.spilled | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
themoviedb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
imdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
thetvdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
igdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.review.certified | :heavy_check_mark: | :x: | |
rottentomatoes://image.review.ripe | :x: | :x: | |
rottentomatoes://image.review.fresh | :heavy_check_mark: | :heavy_check_mark: | Default reviewImage |
rottentomatoes://image.review.rotten | :heavy_check_mark: | :heavy_check_mark: | Default reviewImage |
rottentomatoes://image.review.upright | :heavy_check_mark: | :x: | |
rottentomatoes://image.review.spilled | :heavy_check_mark: | :x: | |
themoviedb://image.review | :x: | :x: | |
imdb://image.review | :x: | :x: | |
thetvdb://image.review | :x: | :x: | |
igdb://image.review | :x: | :x: | |
https://* | :x: | :x: | Any http link to a png |
So here's the image keys that should be used. Basically the two default reviewImage
s and the remaining default ratingImage
s.
Image Key | Web | iOS | Note |
---|---|---|---|
rottentomatoes://image.rating.certified | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.review.fresh | :heavy_check_mark: | :heavy_check_mark: | Default reviewImage |
rottentomatoes://image.review.rotten | :heavy_check_mark: | :heavy_check_mark: | Default reviewImage |
rottentomatoes://image.rating.upright | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
rottentomatoes://image.rating.spilled | :heavy_check_mark: | :heavy_check_mark: | Default ratingImage |
themoviedb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
imdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
thetvdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
igdb://image.rating | :x: | :heavy_check_mark: | Default ratingImage |
Where the web app fails to render the image, it does seem to degrade gracefully:
@burkasaurusrex This is some very good insight! Especially regarding the rating images which I always speculated over but never verified how they are actually implemented. I'll try to reply to you with a longer text over the next weeks as right now I'm kinda busy with other things =)
I'm not sure how long ago Plex added it, but it looks like the new metadata agent is adding individual critic reviews from Rotten Tomatoes to media. I generally think a random smattering of the critic reviews is kind of worthless, but it would be cool to repurpose them for something useful. Given that this tool does an awesome job interfacing directly with the Plex DB, I thought it might be a natural home.
Some ideas:
I haven't done any research on if Plex is already storing Rotten Tomatoes IDs / slugs or if Rotten Tomatoes does a daily dump like you've leveraged from IMDb. If it's interesting, happy to start looking into it. Thanks again for the great tool.