How to combine watch history from two servers using sqlite?

Plex Media Server Metadata & Adding Files
1

Hi, I’m looking to combine the watch history of two servers, a local one, and a remote one. Now that I have gigabit internet, I am planning on ditching the remote one, but that is how I shared to family/friends, so I’d like to preserve their watch history.

I found this article: Move Viewstate/Ratings from One Install to Another | Plex Support but this is to move viewstates, like to a new install.

When executing the command, I get Already exists errors. This is probably because it’s trying to overwrite the items rather than append them to the database. I just learned what sqlite was, and it seems to be possible to increment the table instead of just overwrite it to save the watch history from both servers??

Please help, thank you

2

What you’re trying to do, in theory, is right but because of how PMS builds the data as Items are played, isn’t possible with simple INSERT INTO statements.

You’re going to need some programming logic to query first then decide whether to INSERT or UPDATE records on a one-by-one basis.

This is custom work no matter how you view it.
You would be better off to start with a no-viewstate server and then import (which is how the server exists after being freshly built but prior to first media being played).

3

Pretty simple scripting work and fortunately sqllite supports UPSERT

https://www.sqlite.org/lang_UPSERT.html

4

Right, but there are thousands of view history items in each database. And starting over, means one is always lost.

I don’t believe UPSERT is what I’m looking from a quick look at that page. I don’t want to update existing records on Server A, but rather leave them alone.

The records I’m importing from Server B, just need to be inserted after, rather than on top of. Basically, if Server A has 1,000 records, and Server B has 2,000, when inserting from B > A, instead of starting at 1, the 2000 records should just start from 1,001.

5

It’s not a simple matter of what SQLite supports as much as it’s a matter of how PMS uses it.

I understand what you’re trying to do.

To add those records, it’s not just inserting, you still have the task of mating those records to MEDIA_ITEM_ID and other fields.

One condition I know PMS enforces; when importing watch status, the receiving database must have been matched by the same agent

I must aplogize but this is where I will step back as this is beyond the scope of what I can help with. I understand databases at the basic level and can support that but this type of manipulation is beyond my expertise and what I can support here.

6

To add those records, it’s not just inserting, you still have the task of mating those records to MEDIA_ITEM_ID and other fields.

I believe you don’t have to do this. Looking at the actual database itself, the main condition seems to be that the agent is the same so that you end up with a guid like such com.plexapp.agents.imdb://tt0848228?lang=en For unmatched movies, it is indeed harder and requires linking IDs but I’m not too worried about those.

I am indeed using the same agent, so that’s not a issue.

The main 3 variables seem to be:

id: Which I want to enumerate
account_id: Which I believe is a global ID assigned to each user by Plex
guid: The movie/show the watch history is associated with

So if I can just figure out how to renumber the id column, I think it’ll work. Either way, I’ll see where I can get with trial and error and some tutorials.

7

Upsert is just a convenience operation that can simply the code. It’s not particularly significant beyond that.

8

The following should work-

$ sqlite3 com.plexapp.plugins.library.db
then

sqlite> .mode insert
sqlite> .output watch.sql
sqlite> SELECT account_id,
       guid,
       rating,
       view_offset,
       view_count,
       last_viewed_at,
       created_at,
       updated_at,
       skip_count,
       last_skipped_at,
       changed_at,
       extra_data,
       last_rated_at
  FROM metadata_item_settings;

will give you a file watch.sql with the auto incrementing index value removed

INSERT INTO "table" VALUES(133633,'plex://movie/5d77684e61141d001fb18417',NULL,NULL,1,'2020-09-14 23:46:29','2020-09-14 22:38:37','2020-09-14 23:46:29',0,NULL,3304454,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776b56594b2b001e6d6f40',NULL,NULL,1,'2020-09-18 22:44:18','2020-09-18 21:30:35','2020-09-18 22:44:18',0,NULL,3362344,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776b0c9ab544002150686d',NULL,NULL,1,'2020-09-20 13:00:24','2020-09-20 10:24:52','2020-09-20 13:00:24',0,NULL,3452489,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776d3bfb0d55001f59acea',NULL,NULL,1,'202I0-10-01 17:11:49','2020-09-20 13:38:28','2020-10-01 17:11:49',0,NULL,4118422,'',NULL);

Do that for the local and remote DBs
combine the watch.sql files into one and rename “table” to “metadata_item_settings”

empty all the watched info from the DESTINATION DB

sqlite3 com.plexapp.plugins.library.db "DELETE FROM metadata_item_views;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM metadata_item_settings;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_bandwidth;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_media;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_resources;"
sqlite3 com.plexapp.plugins.library.db "DROP INDEX index_title_sort_naturalsort;"

and them import the combined watch.sql file

Done !

Now make sure you create a copy of the DBs before doing any of this

9

First, thank you SO SO much!

Ok got it so far.

INSERT INTO “table” VALUES(133633,‘plex://movie/5d77684e61141d001fb18417’,NULL,NULL,1,‘2020-09-14 23:46:29’,‘2020-09-14 22:38:37’,‘2020-09-14 23:46:29’,0,NULL,3304454,‘’,NULL);
INSERT INTO “table” VALUES(133633,‘plex://movie/5d776b56594b2b001e6d6f40’,NULL,NULL,1,‘2020-09-18 22:44:18’,‘2020-09-18 21:30:35’,‘2020-09-18 22:44:18’,0,NULL,3362344,‘’,NULL);
INSERT INTO “table” VALUES(133633,‘plex://movie/5d776b0c9ab544002150686d’,NULL,NULL,1,‘2020-09-20 13:00:24’,‘2020-09-20 10:24:52’,‘2020-09-20 13:00:24’,0,NULL,3452489,‘’,NULL);
INSERT INTO “table” VALUES(133633,‘plex://movie/5d776d3bfb0d55001f59acea’,NULL,NULL,1,‘202I0-10-01 17:11:49’,‘2020-09-20 13:38:28’,‘2020-10-01 17:11:49’,0,NULL,4118422,‘’,NULL);

Can I ask what this is?? Why are you inserting 4 random viewstate entries?

combine the watch.sql files into one and rename “table” to “metadata_item_settings”

Erm… complete noob. How do I combine them? Isn’t this the hard part (ie. how do I combine and append vs combine and overwrite)

sqlite3 com.plexapp.plugins.library.db “DELETE FROM metadata_item_views;”
sqlite3 com.plexapp.plugins.library.db “DELETE FROM metadata_item_settings;”
sqlite3 com.plexapp.plugins.library.db “DELETE FROM statistics_bandwidth;”
sqlite3 com.plexapp.plugins.library.db “DELETE FROM statistics_media;”
sqlite3 com.plexapp.plugins.library.db “DELETE FROM statistics_resources;”
sqlite3 com.plexapp.plugins.library.db “DROP INDEX index_title_sort_naturalsort;”

I think I understand the general logic, you are just exporting watch history into .sql, then re-importing after deleting that table. But why is it necessary to delete metadata_item_views, statistics_bandwidth, statistics_media, statistics_resources. Aren’t these unrelated? Plex makes no mention of these in their moving viewstates article: https://support.plex.tv/articles/201154527-move-viewstate-ratings-from-one-install-to-another/

I will try it on backup databases when I get the time, just trying to learn in the meantime.

10

That was just a sample of what should be in your watch.sql

11

Open one in a text editor -
copy everything to the clipboard

Then open the other one in text editor -
Paste values from 1st after the last row of text in the 2nd one

12

that from my general db clean up script
You omit them

13

and why this should work -

We removed the incrementing value from each insert row
So now there are no overlapping index values to get in the way.

14

So I followed the steps you listed and am running into some issues. When trying to import the .sql file back into the Plex database, I get the following error:

Error: near line 377: table metadata_item_settings has 14 columns but 13 values were supplied for every single line in my .sql file. I’m guessing this is because the index column is not supplied.

and them import the combined watch.sql file

Could I get some help on this step? I just followed this to try to import it: https://support.plex.tv/articles/201154527-move-viewstate-ratings-from-one-install-to-another/

But I’m guessing that’s not the right way to import when you don’t have index values.

15

I ended up needing to use this recently, so there’s a few more steps -
After you combine the two exports, you get lines like -

INSERT INTO “table” VALUES(133633,‘plex://movie/5d77684e61141d001fb18417’,NULL,NULL,1,‘2020-09-14 23:46:29’,‘2020-09-14 22:38:37’,‘2020-09-14 23:46:29’,0,NULL,3304454,’’,NULL);

You need to do two search and replace commands -
edit combined and
search and replace "table" with "metadata_item_views"
and
search and replace VALUES( with VALUES(NULL,

The 1st one add the correct table name to the insert command, and the 2nd one add a NULL so sqlite3 will autoincrement the index field

16

Thanks, that worked perfectly!

17

Reading through the SQLite documentation because I was curious about the mode you selected, you should be able to remove one of your clean up steps by using

sqlite> .mode insert metadata_item_views

That should give you your insert command with the proper table name that you want, according to the documentation. The documentation also seems to make it appear to be mandatory to add that, though the way you wrote your example I’m not sure if you just forgot it or if leaving it out gives a default value of table or that was something you did. Not a big deal, just one less thing that would need to be updated afterwards. The insert mode is a cool option, so it was good to stumble upon this thread and find that out.

Thanks.

-Shark2k