Bulk edit of 'Watch Status'

[Pittiplatsch 2]

Hello again,

 

I'm taking up a challenge of migrating a pretty large collection from a former database to Emby, including the 'Watch Status' of the media. I already automated some stuff, but I wonder if there is a way of batch processing the 'Watch Status' in Emby after the library was scanned and the media added. I've to do it just once, so if there is no build-in feature for it, I'm quite open for all sort of hacks. I mean something like modifying the underlying sqlite databases directly or something like this.

 

I'm open for any suggestions.

 

Thanks a lot in advance,
Peter

[Luke 42080]

Hi, in the web app you can use the multi select tool to bulk mark watched

[zigzagtshirt 55]

Hi, in the web app you can use the multi select tool to bulk mark watched

 

 

I mean something like modifying the underlying sqlite databases directly or something like this.

 

 

 

Can you please address the part of his question where he asks about modifying the databases?  This would be useful for all kinds of batch queries/updates.  It would help me do a number of things more easily.  It is simply not practical to rely on the GUI for this.

[CBers 7451]

I'm taking up a challenge of migrating a pretty large collection from a former database to Emby, including the 'Watch Status' of the media. I already automated some stuff, but I wonder if there is a way of batch processing the 'Watch Status' in Emby after the library was scanned and the media added. I've to do it just once, so if there is no build-in feature for it, I'm quite open for all sort of hacks. I mean something like modifying the underlying sqlite databases directly or something like this.

 

Depending on where your "former database" is from, can't you use Trakt to upload the WATCHED status to from your "former database" and then download to Emby?

[Pittiplatsch 2]

Hi, in the web app you can use the multi select tool to bulk mark watched

 

Sorry for overseeing this pretty nifty feature @@Luke. First I was quite a bit confused, because I tried it in the 'List View' with no result. It seems to be not working there. Switching to one of the other views did the trick.

 

But honestly in my situation I mentioned above this isn't that helpful. Except there is something like a select all available for the Multi-Select tool? Otherwise it would be thousands of clicks, especially because I've to do it for multiple users. One mistake I might do and I've to start all over again.

 

Can you please address the part of his question where he asks about modifying the databases?  This would be useful for all kinds of batch queries/updates.  It would help me do a number of things more easily.  It is simply not practical to rely on the GUI for this.

 

I must confess this would be a really dirty hack I wasn't sure asking for such things, but I'm open for it if there's a way to achieve something. I'll already tried some stuff out yesterday very quickly, but this wasn't successful in the first place. I'll need to have a closer look to it anyway, but I would appreciate any hints.

 

Depending on where your "former database" is from, can't you use Trakt to upload the WATCHED status to from your "former database" and then download to Emby?

 

Thanks @@CBers, but sadly I'm coming from a pretty old version of PVD and there is actually no way of syncing something to Trakt.tv as far as I know.

 

Thank you all,

Peter

Edited July 17, 2017 by Pittiplatsch

[PenkethBoy 2068]

@@Pittiplatsch

 

Using a sqlite db reader like - DB Browser for Sqlite - its free - would allow you to do what you need - IF you have reasonable SQL skills

 

A few hints

 

Watched status is stored in the "userdata" table

 

Use a "sql Join" to the "TypedbaseItems" table via fields "UserDataKey" and "key" - you will need to filter on Typedbaseitems.type (to limit to video,movies etc) and userdata.userid (for different users)

 

Then match against your existing data set to find the watched status of a film etc - by film title perhaps?

 

Then run an update sql query to populate the Emby db

 

Dont do this with a live copy of the db for obvious reasons as it would be easy to mess up your emby db

 

Simple really 

[CBers 7451]

Thanks @@CBers, but sadly I'm coming from a pretty old version of PVD and there is actually no way of syncing something to Trakt.tv as far as I know.

 

 

PVD?

[Pittiplatsch 2]

PVD?

 

Personal Video Database: http://www.videodb.info

 

It was quite nice in the beginning, but in the end very frustrating due to misconceptions and unreliable behavior.

[Pittiplatsch 2]

@@Pittiplatsch

 

Using a sqlite db reader like - DB Browser for Sqlite - its free - would allow you to do what you need - IF you have reasonable SQL skills

 

A few hints

 

Watched status is stored in the "userdata" table

 

Use a "sql Join" to the "TypedbaseItems" table via fields "UserDataKey" and "key" - you will need to filter on Typedbaseitems.type (to limit to video,movies etc) and userdata.userid (for different users)

 

Then match against your existing data set to find the watched status of a film etc - by film title perhaps?

 

Then run an update sql query to populate the Emby db

 

Dont do this with a live copy of the db for obvious reasons as it would be easy to mess up your emby db

 

Simple really 

 

Yeah, I'm a bit familiar with SQL so I'll give it try. Many thanks for it. I've a busy week, but I'll try it as soon as I can. I'll keep you posted.

Edited July 18, 2017 by Pittiplatsch

[PenkethBoy 2068]

if you get stuck - i might be able to help 

[zigzagtshirt 55]

@@Pittiplatsch

 

Using a sqlite db reader like - DB Browser for Sqlite - its free - would allow you to do what you need - IF you have reasonable SQL skills

 

A few hints

 

Watched status is stored in the "userdata" table

 

Use a "sql Join" to the "TypedbaseItems" table via fields "UserDataKey" and "key" - you will need to filter on Typedbaseitems.type (to limit to video,movies etc) and userdata.userid (for different users)

 

Then match against your existing data set to find the watched status of a film etc - by film title perhaps?

 

Then run an update sql query to populate the Emby db

 

Dont do this with a live copy of the db for obvious reasons as it would be easy to mess up your emby db

 

Simple really 

 

I will also give this a try for my own needs.  I know SQL so hopefully it goes smoothly.

 

Many thanks for the very useful reply.  Not sure why any of the Devs wouldn't jump in and point us in the right direction. 

[PenkethBoy 2068]

possibly as they do not want support requests when people "play" directly with the db 

[zigzagtshirt 55]

possibly as they do not want support requests when people "play" directly with the db 

 

Meh.  

 

They should give us more credit.  We aren't dumb.  And not every average Joe will have any reason to want to jump into the db and start messing with things.

 

Just make it clear that they won't support you if you break it.  

[CBers 7451]

Just make it clear that they won't support you if you break it.

Just to make this clear, if you break it, you won't be supported

[rhodges 49]

You can probably do this with the webapi. I found it a little obtuse when I needed the same thing, so I wrote a personal plugin when I migrated. I don't have the plugin anymore and honestly, I probably should have just spent the time understanding the api to do it. Of course, this would take more time than meddling in the database.

[Pittiplatsch 2]

if you get stuck - i might be able to help 

 

Many thanks for your offer.

 

I think I got it working. Since the 'userdata' table is populated on-the-fly with user interaction instead getting default entries when the library is scanned and media is added, you have to 'insert' the data instead 'updating' it via SQL. I'll begin with the migration procedure from a empty database anyway, so I'll clear already existing 'userdata' to avoid any inconsistencies. After this I'll add my already seen media to the library and 'insert' the required information via SQL. I've to do the hole thing only once, so I can live with the approach.

[Pittiplatsch 2]

You can probably do this with the webapi. I found it a little obtuse when I needed the same thing, so I wrote a personal plugin when I migrated. I don't have the plugin anymore and honestly, I probably should have just spent the time understanding the api to do it. Of course, this would take more time than meddling in the database.

 

You are right actually. This would be a much safer and properly the right way to do it. I had a quick look at the API, but the documentation is a bit fragmented - it seems to be partly outdated too - and not very beginner-friendly. I think it is possible to achieve the same result as well. I do speak Python, and with this I assume it could be done quite fast. But I also think it will be a much more time consuming process than modifying the database directly. For me it is only a single SQL line per user and I only need it once in the beginning of the migration.

 

So thank you for your suggestion. I'll have a look at the API stuff anyway, because you'll never know what kind of tools your are going to need someday.