[Razze]

That's actually the best I could find so far.
But haven't really done anything with that and wanted to ask in the team if anyone has any experience with orms for cpp. Before digging into it more.

I had a look at the sqlite/mysql implementation in our codebase some month ago. Anything we can replace and outsource with a well tested good alternative is a win in my books.

[m.savazzi]

I'm really wondering why the solution would be to redo the database and not move to a ORM, that supports both mysql and sqlite (as well as maybe others)

I've started to look into that some time ago, I'm not sure so far which is the best ORM for C++ or if the rest of the team also things this would be a good thing.
But it's something I want to work on.

In addition to the posts they have added I discovered some queries that have a sequence of where in () and other clauses that mess up MySQL with infinite (= extremy high, minutes) time of execution

With you point if you have a messy DB the ORM becomes very complex. The idea of the new DB structure was to have it simple and clean (as much as possible due to the objective) so that the ORM structure will be nice and clean

[Razze]

I don't think it's that messy. It could be way worse and yes it can improve.

But much of the mysql pain you mention is probably due to the mapping.
See: https://github.com/xbmc/xbmc/blob/master...ataset.cpp for some fun.
And maybe a decent mapper would also keep the connection alive correctly with mysql?

And as I said improving the fileItems would probably improve much more things.

[thetazzbot]

This may not be specifically relevant to Kodi Core, but as a general practice...

I like to create a database abstraction layer where I keep all my SQL queries. This helps me build applications in a Model View Controller paradigm, which, if you think about it, addons for Kodi are all about MVC.

For example, Ive seen addons that have tons of pure sql queries strewn throughout. This makes the code look unnecessarily messy and difficult to maintain, especially when it comes time to move that data from one database to another, or onward to the cloud.

Additionally, the abstraction layer would be versioned in the same way that the database is. Aka MusicDB52.py -> Music52.db

Then a factory method returns the current/correct instance of the database abstraction layer.

From a Kodi core perspective, some of this this paradigm may already be in place. But I still see pure SQL queries in a lot of .cpp code.

[Razze]

But I still see pure SQL queries in a lot of .cpp code.

Most of this code is bundled in VideoDatabase.cpp or MusicDatabase.cpp or similar.
I do not know of sql outside of these, as some said here. If there really is it should be moved, but I can hardly imagine.
Those cpp files are way too long btw so splitting them is actually something I would like. But it's quite hard due to the way the database connection is handled.

Those are also not really "pure" sql queries as they get converted at runtime in some instances. Like if you use cast in sqlite it will be translated to something else for mysql.

[phate89]

But I still see pure SQL queries in a lot of .cpp code.

Most of this code is bundled in VideoDatabase.cpp or MusicDatabase.cpp or similar.
I do not know of sql outside of these, as some said here. If there really is it should be moved, but I can hardly imagine.
Those cpp files are way too long btw so splitting them is actually something I would like. But it's quite hard due to the way the database connection is handled.

Those are also not really "pure" sql queries as they get converted at runtime in some instances. Like if you use cast in sqlite it will be translated to something else for mysql.

Smartplaylist.cpp is full of pieces of query that are passed as filter to video database..

[m.savazzi]

I don't think it's that messy. It could be way worse and yes it can improve.

But much of the mysql pain you mention is probably due to the mapping.
See: https://github.com/xbmc/xbmc/blob/master...ataset.cpp for some fun.
And maybe a decent mapper would also keep the connection alive correctly with mysql?

And as I said improving the fileItems would probably improve much more things.

Not intend to offend anyone
My perception from reading the code is that is messy because has been written in subsequent stratifications with the DB changes so the queries got more and more complex, built dynamically, without a real control.
They end up in bad queries with useless where clauses and very unefficient.

[m.savazzi]

This may not be specifically relevant to Kodi Core, but as a general practice...

I like to create a database abstraction layer where I keep all my SQL queries. This helps me build applications in a Model View Controller paradigm, which, if you think about it, addons for Kodi are all about MVC.

Absolutely agree... that's always a good design practice!

[m.savazzi]

But I still see pure SQL queries in a lot of .cpp code.

Most of this code is bundled in VideoDatabase.cpp or MusicDatabase.cpp or similar.
I do not know of sql outside of these, as some said here. If there really is it should be moved, but I can hardly imagine.
Those cpp files are way too long btw so splitting them is actually something I would like. But it's quite hard due to the way the database connection is handled.

Those are also not really "pure" sql queries as they get converted at runtime in some instances. Like if you use cast in sqlite it will be translated to something else for mysql.

This is where I say is messy is a good example of how not to do... the end result is not predictable

[Razze]

Yes, but this is all pretty abstracted from the database schema. So changing the schema, which isn't the biggest problem isn't going to solve the big problem. (focusing on database/interraction only)

[m.savazzi]

Yes, but this is all pretty abstracted from the database schema. So changing the schema, which isn't the biggest problem isn't going to solve the big problem. (focusing on database/interraction only)

Razze,
correct, but is a core part of the solution. Having as simple as possible DB will make the code more simple and more efficient.
Instead if you keep a clumsy db the code will be messy.

[spcano01]

Could this not be done as emby has been done? I have that as well, but has issues with my synology. It seems that we have choices on the backend now, so maybe that is an option?

This doesn't seem to be getting the traction it needs to be even considered by team kodi anytime soon.

[m.savazzi]

Could this not be done as emby has been done? I have that as well, but has issues with my synology. It seems that we have choices on the backend now, so maybe that is an option?

This doesn't seem to be getting the traction it needs to be even considered by team kodi anytime soon.

What you mean with Emby ?

[spcano01]

Someone correct me if I'm wrong, but it's a 3rd party add on that overrides kodi dB with its own. It allows for some coil parental controls, and other things.

Seems to me that if they can have their database take over with an add-on, this can too. Just another option besides trying to corral cats.

[xnappo]

In Emby for Kodi - we don't take override the DB, we just sync the Emby DB to the Kodi DB. So we are overriding the scraper, not the db. The complexity of the current DB made doing this difficult (mostly the way interrelated items work on the TV side).

Of course we are watching this thread closely because it will be a lot of work for us if the Kodi DB changes

P.S. @spcano01 - come over to the Emby forum and we can help with your NAS - should definitely work.