[m.savazzi]

The only tricky part to think about is how to generate really complex queries, like with smart playlists, that span accross services/repositories. I don't see any issues with queries that f.e. list all related media by a certain person (like for Jared Leto) as we simply query every asset repository one after another and list the results grouped by type (which makes sense IMO). What we need a solution for are queries against play counts and watched states etc, but I guess we could also prefetch related UUIDs from remote services and use these to filter in the according repo. Might be good if we could do some performance tests with our existing DB (first fetch IDs matchinig play count etc, then do a second query using all aggregated info).

I think we need to have the correct approach to solve the issue. Micro services is excellent when there is little interaction between the services, with Little I mean that the interactions are well defined and with a small data set underlining.
In this way if MicroService B needs an info from MicroService A will perform a MicroService A.getIMBDTitle (moveIMDBCode)

But if you already start to put a GetAllArtists that can return 300 complex records the advantage of micro services is crumbled.

If you have functionalities that may involve all the micro services... (like smart playlists)... at this point you have a major issue and is not working anymore.
To be more precise: you can still design it with micro services but you'll need to build a layer on top of the micro services to join the information from all of them that will be much more complex.

[phate89]

I always thought the main problem of the kodi db is the schema but it's not. It's the approach that is totally wrong.
First of all there are queries outside videodatabase.cpp and musicdatabase.cpp and this is (imho) wrong.
We often parse twice the info. For example if we have a tvshow with basic info parsed and we need details we parse everything again.
There's also no method to fine grain what we need to get. Or we parse info from views or we parse everything, even info we don't need.
We query views that create terrible performances in mysql (sqlite doesn't have this problem)

Sure we can fit a new schema in current code but that doesn't solve the real big issue...

[m.savazzi]

I always thought the main problem of the kodi db is the schema but it's not. It's the approach that is totally wrong.
First of all there are queries outside videodatabase.cpp and musicdatabase.cpp and this is (imho) wrong.
We often parse twice the info. For example if we have a tvshow with basic info parsed and we need details we parse everything again.
There's also no method to fine grain what we need to get. Or we parse info from views or we parse everything, even info we don't need.
We query views that create terrible performances in mysql (sqlite doesn't have this problem)

Sure we can fit a new schema in current code but that doesn't solve the real big issue...

100% with you.
As I wrote in my previous messages the correct approach is to have a DB Class to manage the db and the access not to prolifereate queries around.

My point on that was that if we want to have a step by step implementation with the new db schema is possible

M

[Ladida]

I have followed this thread with interested for a long time (part of my job is dbs and SQL queriys,hence the extra interest).

I´m in favour of having centralized db, and offload much of calculations as possible from Kodi core to the db. The aspect of services is interesting, but it seem to be a lot of work involved, and I´m not sure if the reward is worth it vs upgrading the current database.

Is it possible to break down the new database scheme into pieces that could be implemented separately in different Kodi releases? Then implement one part in v17, another in v18 etc (eat the elephant approach)

[Ladida]

My point on that was that if we want to have a step by step implementation with the new db schema is possible

M

This was posted while I was typing, absolutly agrees :-)

[m.savazzi]

Is it possible to break down the new database scheme into pieces that could be implemented separately in different Kodi releases? Then implement one part in v17, another in v18 etc (eat the elephant approach)

I would not suggest that as is complex to manage in terms of data conversion.

The ideal scenario on the data is:
1) create the new DB
2) migrate the data from old db to new one

If you do partial db implementations you'll have to redo multiple times.

[phate89]

Imho you can do it in parts and using the current method used to update the database.
You split the changes in smaller parts and do it one by one.
For example ratings in the new database are separate. Single change. You create a new table, move the ratings and change the queries to point to the rating table.
I chose this examplbe because it's actually the change I'm doing here (of course this time with the code to make the feature work)
https://github.com/xbmc/xbmc/pull/8080
After the first major changes (step 1 move music in the same db as video, step 2 drop the cXX nonsense, step 3 create versions table, step 4 merge persons and bookmarks between music and video) you can split the port in smaller changes and you can even wait for some change that isn't used yet. For example there isn't yet the code to handle multiple ratings? Let's wait to add a table rating..

[Razze]

Team,
I think there are two points here:
1) the current DB structure (considered as a whole) have issues and the library management (that is super core to Kodi) does not work well, does create duplicates, does not allow to be centralized on a nas, etc...
[...]

I'm not sure if I understand what you mean with "does not allow to be centralized on a nas"?

Not trying to rain down on your parade, but I can say on thing for sure the new one will have issues too. That's how things are
And duplicates don't have to be bad by definition (they probably are, but they don't have to be)

I always thought the main problem of the kodi db is the schema but it's not. It's the approach that is totally wrong.
First of all there are queries outside videodatabase.cpp and musicdatabase.cpp and this is (imho) wrong.
We often parse twice the info. For example if we have a tvshow with basic info parsed and we need details we parse everything again.
There's also no method to fine grain what we need to get. Or we parse info from views or we parse everything, even info we don't need.
We query views that create terrible performances in mysql (sqlite doesn't have this problem)

Sure we can fit a new schema in current code but that doesn't solve the real big issue...

If your really concerned about performance, try to have a look at the fileItem code, it's way to hard to understand for me with my limited C++.

Here is an example we're loading the whole list of songs not just the ones you can see. It''s actually no big deal for the database. That's not the part that makes Kodi "slow" it's converting these to fileItems, just in case they might get shown.

[m.savazzi]

Team,
I think there are two points here:
1) the current DB structure (considered as a whole) have issues and the library management (that is super core to Kodi) does not work well, does create duplicates, does not allow to be centralized on a nas, etc...
[...]

I'm not sure if I understand what you mean with "does not allow to be centralized on a nas"?

Not trying to rain down on your parade, but I can say on thing for sure the new one will have issues too. That's how things are
And duplicates don't have to be bad by definition (they probably are, but they don't have to be)

Razze,
SQLite is a file based access all the processing is done in memory. It cannot be done on a different machine. You can put the db files on the NAS but that will be even worst as the device will read them through the lan.
Also it does not cope well with concurrent access (again is not designed for that, is a single machine, single file db, is the evolution/competitor of Microsoft access )

What I meant with "on NAS" is that once we have MySQL we can use the MySQL engine present on any nas (or can be installed) to have it do all the db work and though network you only have queries and responses....
MUCH faster.

So the structure I propose (and the way to connect to it we discussed in the early pages) can be hosted on a MySQL or on a SQLite... in reality at that point can work with any db

[Razze]

Team,
I think there are two points here:
1) the current DB structure (considered as a whole) have issues and the library management (that is super core to Kodi) does not work well, does create duplicates, does not allow to be centralized on a nas, etc...
[...]

I'm not sure if I understand what you mean with "does not allow to be centralized on a nas"?

Not trying to rain down on your parade, but I can say on thing for sure the new one will have issues too. That's how things are
And duplicates don't have to be bad by definition (they probably are, but they don't have to be)

Razze,
SQLite is a file based access all the processing is done in memory. It cannot be done on a different machine. You can put the db files on the NAS but that will be even worst as the device will read them through the lan.
Also it does not cope well with concurrent access (again is not designed for that, is a single machine, single file db, is the evolution/competitor of Microsoft access )

What I meant with "on NAS" is that once we have MySQL we can use the MySQL engine present on any nas (or can be installed) to have it do all the db work and though network you only have queries and responses....
MUCH faster.

So the structure I propose (and the way to connect to it we discussed in the early pages) can be hosted on a MySQL or on a SQLite... in reality at that point can work with any db

I still don't understand how this is different to now, we already have MySQL and SQLite?
Just merging multiple databases to one shouldn't bring anything new to the table in that regard.

[m.savazzi]

I still don't understand how this is different to now, we already have MySQL and SQLite?
Just merging multiple databases to one shouldn't bring anything new to the table in that regard.

Simply that MySQL does not work. The queries are wrong and deadlock the execution. I've posted the issue several month ago, the answer was: MySQL is not developed till we fix the db as the db is local...

... it was from where everything started

[Razze]

I still don't understand how this is different to now, we already have MySQL and SQLite?
Just merging multiple databases to one shouldn't bring anything new to the table in that regard.

Simply that MySQL does not work. The queries are wrong and deadlock the execution. I've posted the issue several month ago, the answer was: MySQL is not developed till we fix the db as the db is local...

... it was from where everything started

Can you dig up that statement for me? I'm quiet interested in that.

[jjd-uk]

Possibly this one http://forum.kodi.tv/showthread.php?tid=199805

[Razze]

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.

[phate89]

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.

The only one I know is http://www.codesynthesis.com/products/od...nual.xhtml
I only have a basic knowledge so I don't know if it's able to replicate efficiently complex queries like the ones for smartplaylist in kodi