Help retrieving Watched status from Library.db

[djon 16]

I'm constantly running out of space on my harddisk, so I'm trying to make a script that each night searches through the Emby library for watched Series episodes and then deletes the watched episodes that are over for instance 1 week old.

 

I'm using Kodi with Emby library and only the default profile, so no different users or anything like that.

 

I've been looking in the Library.db with DB Browser for SQLite and have found by using this select statement I get the individual episodes:

 

select * from typedbaseitems where unratedtype = 'Series' and mediatype = 'Video'

 

I just can't seem to figure out how to retrieve the Watched information for the episodes. I can see DateLastSaved but that doesn't really tell me if and when the episode was watched through the end. 

 

Can someone point me in the right direction?

[mastrmind11 722]

I'm fairly certain Emby can be set to update the show's watched state in its corresponding .nfo file, in which case you can just parse the .nfo files and remove the corresponding shows.

[Luke 42083]

Hi @@djon, if you're running the stable release, user data is in a separate database, userdata_v2.db. If you're on beta or dev it's been merged into library,db and we haven't yet documented a process on how to extract it.

 

I would suggest using our backup and restore plugin which you can find in the plugin catalog.

[djon 16]

I am on version Version 3.0.5911.0

 

Looking in the Userdata_v2.db I can see the playCount and a key. What does the key field correspond to in library.db, so I can join them and find the filepath?

[Luke 42083]

Sorry, in your old version the key will not be in library db. Newer versions of emby server have a UserDataKey column that you could join them with.

[djon 16]

Okay I upgraded to the latest Emby now.

 

Now I can't open Library.db as it requires a Password as it's apparently SQL Cipher encrypted. What's the password?

[Happy2Play 9783]

Okay I upgraded to the latest Emby now.

 

Now I can't open Library.db as it requires a Password as it's apparently SQL Cipher encrypted. What's the password?

Had no problem opening it with SQLite Database Browser Portable.

[djon 16]

 

 

Huh, well I can't...

[djon 16]

Ok, I can only read the db when Emby is shut down. When it's running it apparently locks the db in the new version of Emby. Any way around this?

Edited December 7, 2016 by djon

[Luke 42083]

No, not in 3.0.8500.

[djon 16]

In what version then? :-)

[Luke 42083]

3.0.8500 opens the database in exclusive mode which is what is causing that error. The beta is not doing that although it possible to be added back again. Did you go through the forced upgrade that 3.0.8500 presented you with? You should get that done, because the beta has enough changes that we're no longer going to do the upgrade and instead we're just going to rescan the library entirely (if you're coming from a really old version like you are).

 

you should backup library.db and userdata_v2.db throughout this process in case you need to retry. on the other hand with the beta you could consider deleting library.db and letting it rescan your library. again if you make backups you can try and see what produces the best result.

 

please note, i'm only presenting you with information, not trying to encourage you to install pre-release as it is a work in progress so your transition might not be perfectly seamless. but since you're running sql queries i assume you know what you're doing, so if you make backups of all the databases it shouldn't be hard to experiment.

[djon 16]

I don't remember 3.0.8500 presenting me with anything. I just chose Upgrade and it did everything by itself, including upgrading the databases. Is that what you mean?

 

I think I'll stall my plans and see if the next stable version locks the DB or not. Not much point in making my solution if it won't work in the near future anyway.

[CBers 7452]

You could try the PORTABLE version of Emby Server.