[Starscream]

Hi garbear,

SQL is not the show stoper. it scales very well up to thousends of items in just milliseconds with no problems - even on a Pi.
The real problem is KODIs way to handle the result when it comes to the next action, like "play" or something similar.
Here you can find further information:
http://forum.kodi.tv/showthread.php?tid=213737

So do not give NoSQL a try. i worked with both (SQL and NoSQL) and NoSQL has just 1 advantage: it fits your needs, if you have unstructured data.
In any other case you should prefer a clean relational design.

And one more advantage on SQL: it works perfectly with all the other solutions inside KODI (MyMusic, MyVideo...).

Unfortunately the devs seem to have no interest to solve this issue. So i am still not able to use my Media Player to play media ;-)
KODI is just "movie" these days :-(

[topfs2]

So do not give NoSQL a try. i worked with both (SQL and NoSQL) and NoSQL has just 1 advantage: it fits your needs, if you have unstructured data.
In any other case you should prefer a clean relational design.

Well media is highly unstructured but relational atleast if you want the same db for music/movie/tv shows etc. The one which would fit the absolute best is a graph database though, however I haven't found one which is A) fast and B) embeddable. The one that came closest was sparql and redland rdf, but its messy and isn't really object oriented, so it doesn't fit applications to well IMO.
But yeah, you can have a SQL for media, you just need one table for each type but in our current design thats kindof annoying to manage. If we had an ORM layer it would be a bit better though.

Also don't tell the developer how _he_ should do it. Giving pointers is fine and all but ultimately the dev is going to do the work, and its very unlikely they would do the work if they don't like the approach. If you strongly feel that SQL is better then volunteer and do the work, show us its better. I personally don't think it would be unless you force in an ORM, and perhaps you know one in C++ which would make it awesome.

To further discuss on the media library these are some things I found when trying out a bunch of NoSQL solutions.

TBH I think that the dynamic query part is rather unnecessary, if you want a media item to the info views, then you wan the entire item and you have the ID generally, so a key value store is enough there.
For lists you need the id's in the given order, and then fetch each id (possibly projected to fit whats being rendered). So basically what you need is an index on a query.
There are cases were we need pure dynamic queries (json rpc for example) but a o(n^2) traversal is probably quick enough given that the remotes really should cache their results (also the queries in JSON rpc are really advanced with multiple joins and probably lead to worse than o(n^2) in SQL)

SQL and many NoSQL gives this dynamically but given the media library doesn't change overly often and depending on the nodes in the library we kindof know what queries will be called. So we could potentially have the entire media database as two key value stores, one for looking up index (url -> list of ids) and one for the media (id -> media). This simplistic view we could implement ourselves and could then not care what key-value store we are using (their apis are always the same ) making it very easy to have both local and remote databases.

I did a small trial on on different techs and the one that came out way ahead was ejdb, especially since the same code could potentially be used with mongodb. If Unqlite didn't have wierd bugs when I tried it was also top tier. But as said above, just a key value store gets you 90% of the way essentially

[Razze]

I think what he wanted to say is, that the database is not our problem when trying to handle huge amounts of data. Like music or games.
Our code is mostly choking on the FileItem representation and drawing the frontend.

[Starscream]

I think what he wanted to say is, that the database is not our problem when trying to handle huge amounts of data. Like music or games.
Our code is mostly choking on the FileItem representation and drawing the frontend.

You are right.
I never had the intention to tell garbear how to do his work on this project. Sorry for this misunderstanding!

Anyway: SQL is not the problem on huge data, handling of the resulting data is the true problem of KODI.
If you do a quick SELECT on a large DB (like music with 40k entries likue i did), you will see, that the system responds very quick with the data fetched - even on my installation where the db is installed on a small server and the used Pi has just a 100 MBit network interface.

And topfs2 is right too: using an ORM could solve the handling of the database, but leads sometimes to other problems.

I just would like to see all the media stuff in one database instead of having all the things in several places without any advantage.
But (ofc) it is up to you to implement the solution you prefer. My statement just should be a hint where the real problem is (regarding the delay).

[topfs2]

Aight, then I just misunderstood you Sorry for that.

The bottleneck for PI could very well be elsewere, Kodi in general hasn't really been designed from the start with embedded systems in mind. From the xbox days we had a fast hdd and code showcase this. To some extent many of these problems are addressed but wouldn't be surprised if many still exist.

And its very true that our fileitem handling is a mess, I recently tried to pull out all GUI stuff out of Kodi and fileitem was a rather gruesome sight
It doesn't help either that the GUI rendering is so intertwined with the rest of applications operations, heck we can't even fetch a directory without locking and possibly rendering GUI

I just would like to see all the media stuff in one database instead of having all the things in several places without any advantage.
But (ofc) it is up to you to implement the solution you prefer. My statement just should be a hint where the real problem is (regarding the delay).

Yeah agreed, this is the main thing IMO. Today its a pain to add new "content types" where ideally it should be so easy that we could almost do it in GUI in Kodi. So that user could create a new type "Home videos" which extends "movies" if they so desire. Not sure if its really needed to be that extendible but from code it would be awesome if they shared 90% of the same code, not as is today where Pictures, Music and Movies/TV Shows all use their own code.

Personally I'm not fluent enough in SQL to say if thats doable in that or not but it feels to me its too strict. I have gone courses on how to implement many of the queries in SQL but rarely used SQL in any project

[Razze]

Well it's doable for sure.

It might have some implications, based on decissions your forced to do. We could for example save only the base item (only essentials/hierarchy) and just attach generic properties to each hierarchy.

for music that could be

- album (id, path)
-- song (id, filepath)

everything else is a property. so userratings could be a propery etc.
It has some implications, for example that that table might get big, but I don't think we will hit problems.

It's just a quick example and probably not bullet proof, as it's just a glimpse into it.

[phate89]

Well it's doable for sure.

It might have some implications, based on decissions your forced to do. We could for example save only the base item (only essentials/hierarchy) and just attach generic properties to each hierarchy.

for music that could be

- album (id, path)
-- song (id, filepath)

everything else is a property. so userratings could be a propery etc.
It has some implications, for example that that table might get big, but I don't think we will hit problems.

It's just a quick example and probably not bullet proof, as it's just a glimpse into it.

Someone already tried to create a schema similar to this:
http://forum.kodi.tv/showthread.php?tid=73831
http://kodi.wiki/view/Database_Schema_4.0
http://kodi.wiki/view/Database_Schema_4.0/a
http://forum.kodi.tv/showthread.php?tid=155304

Also gearbear tried something with picture library but every attempt failed..
The problem is (imho) always the same. Changing the schema means also that you need a better code to interact with the library. It means you have to change how gui interacts with library etc etc
IT's a HUGE job that (at least I think so) noone can do it alone and needs also the full dev team attention because you need a clear vision of what the final result should be..

[topfs2]

everything else is a property. so userratings could be a propery etc.
It has some implications, for example that that table might get big, but I don't think we will hit problems.

It's just a quick example and probably not bullet proof, as it's just a glimpse into it.

The ways I've seen that accomplished you have a table which contains id, key, value triplet, is that how you meant to handle it? If so then your actually suggesting exactly librdf and sparql (which can be stored in sqlite). https://en.wikipedia.org/wiki/Triplestore

What I found with that is that you get problems when a properties can be of many types, string, integer, booleans or even arrays of these. And it gets even worse when properties are full objects

librdf and sparql solves this by forcing all properties values to be strings, which comes from rdf's xml nature. IMO this is terrible
EDIT2: And librdf/sparql solves full objects by the triplets object/value being an id, so its pointing to a new triplet group
EDIT3: And by terrible I mean how rdf does it feels terrible, perhaps its possible to do it not terrible. Very interesting in hearing thoughts on this!

Usually NoSQL solutions as document stores gives this for free, since they just store documents, which can have any depth and any complexity. Ofcourse queries gets really wierd due to this (the problem I have with mongodb) which is why I generally prefer NoSQLs which has a script query and forces map-reduce, like unqlite or couchdb. And then your back to that you could just as well have key value store with map reduce

EDIT: When I get back home I'll see if I can find my trials and push them to github, that could be a nice starting point if you want to try to do the same with sqlite?

[Razze]

Not one 100% sure if it's really a triplestore.

We actually use this construct in a mssql database at work. And yes, we're saving everything in a string, but they get parsed to the correct type as soon as they leave the db. Yes, it has the downside, that you can't search as fast over that column. Not sure if thats really something you can feel in performance.

But when you have done the property construct and implemented the basic code. You can just ask the property api and can handle every new field with pretty basic changes.

[a1rwulf]

We actually use this construct in a mssql database at work. And yes, we're saving everything in a string, but they get parsed to the correct type as soon as they leave the db. Yes, it has the downside, that you can't search as fast over that column. Not sure if thats really something you can feel in performance.

I once worked on a database with ~500mio data rows - you could feel the difference there (quite clearly).

[zag]

Be careful not to over engineer these things. At the end of the day the average user probably has less than 10,000 items in their kodi db.

[a1rwulf]

Of course ;-)

[Razze]

We actually use this construct in a mssql database at work. And yes, we're saving everything in a string, but they get parsed to the correct type as soon as they leave the db. Yes, it has the downside, that you can't search as fast over that column. Not sure if thats really something you can feel in performance.

I once worked on a database with ~500mio data rows - you could feel the difference there (quite clearly).

Well partition your tables. Then you should not feel it

But that's obviously no solution for Kodi

[a1rwulf]

Yeah it also depends a lot on how the actual rows look like.
But anyway it's unrelated to Kodi right now.

[natethomas]

FWIW, it seems like if someone is willing to be kind of a jerk about it, tearing apart and rebuilding how the frontend interacts with the db is something the team would be willing to get behind, based on the recent VideoPlayer stuff.