Supported way to run SQL querys

[xe` 42]

I realise this is not every day request but the single last feature I run Kodi with mysql for is to maintain the ability to run arbitrary sql querys.

 

I started a long time ago with a  simple need to find duplicates in a way that matched my setup but I quickly found it was extraordinarily useful for a myriad of one of and recurring checkups and reports.

 

With mysql this is easy but with Emby and sqlite it is less so.

 

I am in early days exploring the database but it looks liked it will be in most cases even easier to query than Kodi (albeit slower).

 

However since sqlite isnt multi-user the only safe way to query it is to shut Emby down, take a copy and run against that.

 

 

So to get to the point, is there, or could there be, a way to query Emby server directly for arbitrary sql querys.

[PenkethBoy 2063]

Only if they remove the exclusive access to the db as you have found

 

But that makes it slower again

 

So not likely

 

I do the same as you just shut it down, make a copy of db if needed and run your queries

[Carlo 4330]

Same, and this bums me out as well as I'm a SQL guy.

With that said Sqlite is perfect for this type of application (and it's cross OS abilities) and it handles the load really well.

[xe` 42]

I find it oddly good to find out I am not alone in doing this.

 

So as of now there is no direct way to do these calls, I suspect a feature request like this would be too edge case?

[Carlo 4330]

The amount of people who would find it useful to execute SQL against a running database will be small for sure.

[PenkethBoy 2063]

Use the API instead - you can do most things that way as well on a live db if you wish

 

Look at the swagger (built into the server) docs to get an idea of what you can do

 

Thats what the Emby for Kodi addon does

[Luke 37132]

You can get a small performance boost by opening the database in exclusive mode, so that's why we're doing that.

[xe` 42]

Do we know of anyway to perform querys without having to shut down Emby and take a copy of the sqlite dbase. I realise there is an API but thats a whole new thing to learn in a life with not  enough time to start with.

 

Is there such a thing as direct RO opening of sqlite when Emby has it already open in exclusive mode?

[Luke 37132]

There is no way due to the reason I mentioned above.

[xe` 42]

Not the answer I was hoping for but at least it is definitive. Thanks

[Luke 37132]

It doesn't have to always be that way, I'm just saying that's the way it currently is.

[xe` 42]

Thinking about ways this could be done without significant dev work for what is an edge case.

 

The crux of the issue is the steps and downtime needed to obtain a copy of the sqlie file.

 

Is there a scenario where a backup of the sqlite file (only) could be triggered without an Emby reboot (via GUI or API) that doesn't impinge on the commercial backup addon offering (which I assume backups up way more).

[PenkethBoy 2063]

If your emby server is not doing a library scan etc - so not updating - you can check via the emby api

 

You can copy the db, -shm and -wal files to another location

 

on opening the copy db the journal changes (in the shm and wal files) will be combined into the db 

 

Then you have a copy to play with....

[xe` 42]

Yeah but isnt that an inherently risky idea. Its going to take a finite amount of time to copy the few gigs of data meanwhile outwith the control of this process the database could be required for legitimate writes. There be dragons here i think.

[PenkethBoy 2063]

Nope - you are overthinking it - used it for ages

 

you are making a copy not moving it

 

Only thing is if a library scan or similar is running then yes there would be difference between "versions"

 

But my db's are less than 200mb - so copy is almost instant

 

Might be worth doing a compaction on your db if its GB in size

[Carlo 4330]

Use the API instead - you can do most things that way as well on a live db if you wish

 

Look at the swagger (built into the server) docs to get an idea of what you can do

 

Thats what the Emby for Kodi addon does

True and even though I'm a programmer, I'd still rather just work in pure SQL if the info I want to access is contained in the DB.  No API will ever be better then pure SQL.  Obviously with pure SQL access I could also program things as well.

 

You can get a small performance boost by opening the database in exclusive mode, so that's why we're doing that.

Any chance you could make exclusive mode an option (even if well hidden)?

This is one of the few things I like better in Plex over Emby is that they don't use exclusive mode and you can run queries against the DB. The boost in speed isn't that much that I personally would care.  The benefit for me outweigh the speed cost.

 

I use DB data for all kinds of things from reporting to being able to query the DB to give me the 10 largest H.264 based files in the system so they can be converted outside of Emby/Plex to H.265 to being able to query daily stats to comparing the current EPG to a list of movies I want to record that I don't have or have an SD version and want to replace it.

 

Almost all of it is well written sql that is read only and usually limited, but these types of quick queries can provide a lot of really useful info that can be used outside of Plex/Emby.

 

Alternately (not preferred but still quite useful) would be a task AND API call that could be used to create a copy of the DB that could be used for this type of purpose as well.

 

Would you consider either of these two things?

[PenkethBoy 2063]

"I use DB data for all kinds of things from reporting to being able to query the DB to give me the 10 largest H.264 based files in the system so they can be converted outside of Emby/Plex to H.265 to being able to query daily stats to comparing the current EPG to a list of movies I want to record that I don't have or have an SD version and want to replace it."

 

You can do the same with the api - i do it to dump stuff to json, html or excel reports

[Carlo 4330]

To be honest I've been lazy looking at the APIs (plex or emby) in this regard as I just haven't had the need.

Can you easily via API get back a list of 10 MP4 files that use H.264 sorted biggest to smallest size wise?  Super simple via SQL and run in 1 or 2 seconds.

 

Typically APIs don't give you that kind of fine grain control over the query OR take a long time to run.

How about something like sync all movies user A has watched to user B?  Again really simple and a fast update in SQL but could take quite a while via APIs (in general) as each movie/show might require an individual update.

 

Using something like the above is really cool for having an account for a wife and husband and then a shared account.  So when you watch things together you use the shared account and then can run SQL to mark each individual user's account as watched.  Vise versa where if both have individually watched something then the shared user get's marked as watched.  These are the type of things I'd NEVER expect to have implemented in Emby directly as they are sort of ONE-OFF type things but I custom program stuff like this for my media clients to make their life easier.  Plex is much easier in this regard as you can do it while the system is running but with Emby you have to schedule down time to perform.

Edited January 21, 2019 by cayars

[PenkethBoy 2063]

not done all those variations but yes all doable as you have access to all the data

 

so you just need to pull back info analyse it and post it back if you updated it

 

Yes direct access to db will always be quicker - although we are talking small time periods in either case - and if its only an occasional thing - does not matter is it takes a few seconds

 

API is not slow in EMby - with the added advantage if your script can "see" the server no need for tools to work on diff OS's etc

 

I can do both SQL and the API - in general i use the API though as once you get over the learning curve i can deal with more exotic manipulation of data easier that way

 

For TV episodes by size, codec, container

 

Edited January 21, 2019 by PenkethBoy

[Carlo 4330]

I'll probably have to dive into the API at some point soon.  I'd just rather spend 5 minutes on SQL then 1/2 hour to an hour in C# doing the same thing via API.

But one works now while the other doesn't on the live system so...

 

Thanks for that graphic.

[xe` 42]

I might put some effort into learning the API because from the screenshot above it seems to suggest it can handle IMDB and TVDB id seperatly.

 

The fact they are stored naively in the database as one single concatenated delimited field feels hacky to me and I am sure it is going to be a pain to work around. (although I am sure there are reason and ways for both)

[Luke 37132]

Yes I think that would be better anyway.

[PenkethBoy 2063]

Yes they are returned as separate items 

 

snip of JSON file

"ProviderIds":  {
                                          "Imdb":  "tt1413492",
                                          "Tmdb":  "429351"
                                      },
                      "IsFolder":  false,
                      "Type":  "Movie",

[xe` 42]

I have decided that in the first instance at least I am going to stick to SQL. This is a shorter jump for me and I need to deprecate Kodi SQL ASAP.

 

Later I can look to port the new SQL to API calls.

 

So given this, is anyone interested in team working on SQL and posting interesting querys that could benefit others. If so where would be the correct place.

[Carlo 4330]

Probably the best spot would be https://emby.media/community/index.php?/forum/71-tools-and-utilities/

Tools and Utilities section of the site.  That's where I would go to look for things like this.