Feature request: repurpose Rotten Tomatoes reviews

[burkasaurusrex]

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:

• Add an option to remove all reviews (there's some detail here)
• Repurpose the review panels to show aggregate Rotten Tomatoes critic and audience scores. So instead of showing a bunch of random individual critic reviews, it would be two simple reviews showing the aggregate with the fresh or rotten tomato icon.
• If it's possible to set additional custom icons for the reviews, it would be cool to show aggregate ratings from Metacritic, Letterboxd, etc. each as an individual review.

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.

[mynttt]

Sounds interesting. I'll look into it the next weeks and come back to you.

[burkasaurusrex]

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:

• [x] Find where tag_id maps to. See if it can be manipulated.
• [x] Make reliable 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.
• [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.
• [ ] Determine sources to add. Could include:
  • Metacritic
  • RottenTomato's critic and user ratings
  • Ratings by different demographics from IMDb's user ratings detail like male rating, female rating, US rating, Non-US rating, etc.
  • Letterboxd
  • Trakt
  • TMDb

[burkasaurusrex]

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)

[burkasaurusrex]

• [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 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.

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.

[burkasaurusrex]

• [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 reviewImages 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 ratingImages 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 reviewImages and the remaining default ratingImages.

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:

[mynttt]

@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 =)