[m.savazzi]

I've started generating the SQL and the real DB and found several errors in the relationships.

using SQLServer Express and it's tools I've created the DB and is available for everyone to see. Also there is the SQL.

From that one I generated the MySQL

In my OneDrive you can find the SQL for SQLServer, the DB. Hoping the team will import this to enhance the library management

I looked at that only yesterday and I wanted to reply this evening to let you know about that relationships
Here's the logical view where I fixed relationship errors:
https://dl.dropboxusercontent.com/u/2219...gical3.pdf
Check if we found the same errors or if you missed something

phate89 can you check the new schemas and DBs? On the one drive you have the SQL for SQLExpress as well as for MySQL (both DB are free and have a nice tool to view relationships. I prefer SQLExpress in this phase)

I'm also trying to generate the SQLite scripts.

[m.savazzi]

found a great tool to convert from SQL Express to SQLite generating the file!


In the structure I've not put the views as they are not usefull now. Once the structure and relationship is locked we can add all the views to simplify the queries at runtime in Kodi


Ready to Rock

[phate89]

I started to look at the db. Here's some considerations:
1) The person -> asset relationship is wrong. You don't need to connect the person to the asset with a many-many relationship but the person's role to the asset because without it you know that the person is related to the asset but you don't know what is his role.
2) In several places you put a table like "assettypes" but there's no need for that. It's a pointless complication because the assettypes are defined by code so whether you decide to store the string version (movie, season, episode etc) or the numeric version they are defined by code and you don't need to store them in the database. Kodi already knows what to search for.
3) many to many relations between versions and videostream,audiostream and subtitles is pointless. Maybe for subtitles could be useful but I'm not actually sure of that too. A version can have several streams but the stream is related only to that version.
4) I don't understand the purpose of language table. I thought it was the list of possible languages for subtitles and streams and it was pointless (like point 2) but it's different and I don't understand the purpose of it.
5) assets needs a connection to the artworks. You need to connect art to the version in case the user have more than one version but the most used is the one of the asset.
6) I don't understand the purpose of versionbookmarks.
7) versionviewed relationships are both wrong. an user can have more versionviewed and a version can have more versionviewed so "versionviewed" needs 2 fields. the first with the version id, the second with the user id (like users favorites)

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

[zag]

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

^^ This

Keep it simple for now in SQLite as that's the db we currently use for everything.

[topfs2]

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

^^ This

Keep it simple for now in SQLite as that's the db we currently use for everything.

And for those interested in sql alternatives from my tests I think http://ejdb.org/ and mongodb (if we want remote) would be a very nice document store alternative.
Obviously I'm not saying that needs to be done first or anything, just thought some might be interested in the findings (I could publish some tests on github if someone wants to take a look).

I reckon there would be far more traction switching to nosql than sql by other devs perhaps but just put it out there

[m.savazzi]

I started to look at the db. Here's some considerations:
1) The person -> asset relationship is wrong. You don't need to connect the person to the asset with a many-many relationship but the person's role to the asset because without it you know that the person is related to the asset but you don't know what is his role.

I need a many to many as a Person can be an acton on one movie, a director in another one.
Also one asset (i.e. a movie) has multiple persons.
And each person has multiple roles

2) In several places you put a table like "assettypes" but there's no need for that. It's a pointless complication because the assettypes are defined by code so whether you decide to store the string version (movie, season, episode etc) or the numeric version they are defined by code and you don't need to store them in the database. Kodi already knows what to search for.

Is needed as the asset is generic. Asset refers to everything a fanart, a game, a movie, an episode.
AssetTypes allows to identify correctly the asset.
AssetTypes should be a table as Kodi can add more types as we go on (i.e. games) so we do not need to change anything there.

3) many to many relations between versions and videostream,audiostream and subtitles is pointless. Maybe for subtitles could be useful but I'm not actually sure of that too. A version can have several streams but the stream is related only to that version.

No is needed.
An audio stream is 18K AC3
Another audio stream is 5.1 DTS

Now this two audio streams can be part of multiple movies and a movie can have multiple audio streams.

Thus is a N:M relationship

4) I don't understand the purpose of language table. I thought it was the list of possible languages for subtitles and streams and it was pointless (like point 2) but it's different and I don't understand the purpose of it.

Same as above. Language is Italian, English, etc...
so you can have a video track that is in English, a subtitle that is italian,
So...

5) assets needs a connection to the artworks. You need to connect art to the version in case the user have more than one version but the most used is the one of the asset.

I think you're right...

6) I don't understand the purpose of versionbookmarks.

It was requested in the thread.
Immagine you have Avatar and Avatar Directors Cut. They are two versions of the same asset.
Now you can save bookmarks for Avatar and different Bookmarks for Avatars Directors Cut.

7) versionviewed relationships are both wrong. an user can have more versionviewed and a version can have more versionviewed so "versionviewed" needs 2 fields. the first with the version id, the second with the user id (like users favorites)

Not sure I got you.
The viewed state is boolean: yes/no.
The last played minute should be saved in the bookmarks.
Going back to Avatar example I can have viewed Avatar but not Avatar Directors Cut.
Will check if I have not done a mess.

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

I agree, I generated them for everyone to be able to access the DB with what they are used too. I will keep working on SQL Express

[m.savazzi]

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

^^ This

Keep it simple for now in SQLite as that's the db we currently use for everything.

SQLite does not have nice tools to view the structure and modify.

Also I've started all this effort because SQLite DOES not allow to use a remote SQL server like MySQL on a Nas and to have a share library.

Not only we want to enhance the library and the data Handling we must have a remote data server working well as on low power devices is super important and will enhance the performances of an order of magnitude.

[m.savazzi]

I think it's too early btw to start to create mysql/sqlite versions. These points are all logical problems that could be solved before to create the actual sql code. Also there are several places where there are tables to avoid redundancy that might add complexity without any tangible advantage.
The most important thing before to move forward is that we need kodi devs opinion if we're going in the right direction...

^^ This

Keep it simple for now in SQLite as that's the db we currently use for everything.

And for those interested in sql alternatives from my tests I think http://ejdb.org/ and mongodb (if we want remote) would be a very nice document store alternative.
Obviously I'm not saying that needs to be done first or anything, just thought some might be interested in the findings (I could publish some tests on github if someone wants to take a look).

I reckon there would be far more traction switching to nosql than sql by other devs perhaps but just put it out there

This is cool... I will study it.

But a library is a structured data set, very structured. So accesing JSON or nosql does not look to me a perfect fit.

Also on big libraries on HTPC devices with small computational power the overhead is terrible and the interface lags and is very slow.
As all the NAS (and not only) I know are able to run MySQL it is far better to put the DB on the NAS and have the HTPC handle only the data that is a result of the query.
Also in very large libraries instead of querying "give me all movie titles" and get back 100000 results you can use a paged query "give me all movie titles in pages of 100" so the ui will be far more fluid and smooth.
(just an example but there are several places where this will be a great advantage).

Now I'm not excluding SQLite for single implementation but we need to have both!

[phate89]

I need a many to many as a Person can be an acton on one movie, a director in another one.
Also one asset (i.e. a movie) has multiple persons.
And each person has multiple roles

I know you need many to many. That's not the problem. The problem is that you need many to many between "assets" and "r_persons_roles" (btw person -> people) because if you connect person with asset you don't know if that person in that movie is a director, is an actor, is a producer or all 3 of them since there's no connection between role and movie

Is needed as the asset is generic. Asset refers to everything a fanart, a game, a movie, an episode.
AssetTypes allows to identify correctly the asset.
AssetTypes should be a table as Kodi can add more types as we go on (i.e. games) so we do not need to change anything there.

No you don't need it. Instead of calling the table "assetID" you name it "AssetType" and you put it (for example) 1 for movie, 2 for tv shows, 3 for seasons 4 for episodes, 5 for music videos etc etc etc
Kodi already knows that 1 is movie so there's no need for a table saying that. It's the same for people roles

No is needed.
An audio stream is 18K AC3
Another audio stream is 5.1 DTS

Now this two audio streams can be part of multiple movies and a movie can have multiple audio streams.

Thus is a N:M relationship

Because you don't store track id, track title, length. If you add that doesn't work anymore. Also IMO adds unneeded complexity. But that's something only kodi devs can say.

Same as above. Language is Italian, English, etc...
so you can have a video track that is in English, a subtitle that is italian,
So...

If that's the purpose the relationships are wrong. not one audiostream/videostream -> many languages like it is now but one language -> many videostreams/audiostreams (in subtitles it's fine).
Also that's the same thing as assettype. You don't need a table. you know all the possible options and possible languages for a subtitle. You put in the column the language code ("it_IT") or a number that kodi knows. No need for a table, the languages are always the same.

It was requested in the thread.
Immagine you have Avatar and Avatar Directors Cut. They are two versions of the same asset.
Now you can save bookmarks for Avatar and different Bookmarks for Avatars Directors Cut.

Bookmarks are already version related so they are already referring to a specific bookmark version. Also bookmark to 2 versions at the same time doesn't make a lot of sense because they have different timings

Not sure I got you.
The viewed state is boolean: yes/no.
The last played minute should be saved in the bookmarks.
Going back to Avatar example I can have viewed Avatar but not Avatar Directors Cut.
Will check if I have not done a mess.

IMO you did in a couple of places. Many to many always looks like this: table_A ---< table_in_the_middle >--- table_B
It's the same with versionbookmarks, versions_videostreams, versions_audiostreams

[phate89]

This is cool... I will study it.

But a library is a structured data set, very structured. So accesing JSON or nosql does not look to me a perfect fit.

Also on big libraries on HTPC devices with small computational power the overhead is terrible and the interface lags and is very slow.
As all the NAS (and not only) I know are able to run MySQL it is far better to put the DB on the NAS and have the HTPC handle only the data that is a result of the query.
Also in very large libraries instead of querying "give me all movie titles" and get back 100000 results you can use a paged query "give me all movie titles in pages of 100" so the ui will be far more fluid and smooth.
(just an example but there are several places where this will be a great advantage).

Now I'm not excluding SQLite for single implementation but we need to have both!

IMO nosql in theory is the best for this. Here you have several assets with everyone a different options and it's very complicated db layout. ith nosql you have a lot more flexibility with this so it's easier.
The problem is that you have to give up network nas storing and things like that so I prefer SQL too

[m.savazzi]

I need a many to many as a Person can be an acton on one movie, a director in another one.
Also one asset (i.e. a movie) has multiple persons.
And each person has multiple roles

I know you need many to many. That's not the problem. The problem is that you need many to many between "assets" and "r_persons_roles" (btw person -> people) because if you connect person with asset you don't know if that person in that movie is a director, is an actor, is a producer or all 3 of them since there's no connection between role and movie

So I'm missing a connection? sorry I misunderstood you!
Ok will check and add

Is needed as the asset is generic. Asset refers to everything a fanart, a game, a movie, an episode.
AssetTypes allows to identify correctly the asset.
AssetTypes should be a table as Kodi can add more types as we go on (i.e. games) so we do not need to change anything there.

No you don't need it. Instead of calling the table "assetID" you name it "AssetType" and you put it (for example) 1 for movie, 2 for tv shows, 3 for seasons 4 for episodes, 5 for music videos etc etc etc
Kodi already knows that 1 is movie so there's no need for a table saying that. It's the same for people roles
[/quote]

I have not understood you. You mean we can use static types defined in code instead of a linked table?
Isn't hardcoding not nice?
You mean we can structure the ID link
1xxxxxxxxxx: Movie
2xxxxxxxxxx: Show
etc..?
and this will be done "manually" by the code?

I usually prefer soft linking but is easy to change... we just remove the table

No is needed.
An audio stream is 18K AC3
Another audio stream is 5.1 DTS

Now this two audio streams can be part of multiple movies and a movie can have multiple audio streams.

Thus is a N:M relationship

Because you don't store track id, track title, length. If you add that doesn't work anymore. Also IMO adds unneeded complexity. But that's something only kodi devs can say.

In reality I was trying to simplify. In this way the number of AudioTracks is very reduced as they are the formats.
Language (or description) is stored in the Language table.

This should make easier to create icons for the audio tracks and to standardize them. Today is quite a mess.

If those info are needed we have to add them and you're totally right is not anymore an N:M but a 1:N

Same as above. Language is Italian, English, etc...
so you can have a video track that is in English, a subtitle that is italian,
So...

If that's the purpose the relationships are wrong. not one audiostream/videostream -> many languages like it is now but one language -> many videostreams/audiostreams (in subtitles it's fine).
Also that's the same thing as assettype. You don't need a table. you know all the possible options and possible languages for a subtitle. You put in the column the language code ("it_IT") or a number that kodi knows. No need for a table, the languages are always the same.

Ops! I messed up the relationship Will fix it
Well languages are always the same, you're right, but scrapers from web return all possible bullshit so if Kodi workis on it to normalize it is great and I agree with you.
If kodi only reads the value from the scraper and saves it... and then the skin shows it .... then sorry no we need a table
You can have IT_it. Italian, Italiano etc...

It's easy to fix and does not make major difference

It was requested in the thread.
Immagine you have Avatar and Avatar Directors Cut. They are two versions of the same asset.
Now you can save bookmarks for Avatar and different Bookmarks for Avatars Directors Cut.

Bookmarks are already version related so they are already referring to a specific bookmark version. Also bookmark to 2 versions at the same time doesn't make a lot of sense because they have different timings

You are totally correct... so I have to remove a link?

Not sure I got you.
The viewed state is boolean: yes/no.
The last played minute should be saved in the bookmarks.
Going back to Avatar example I can have viewed Avatar but not Avatar Directors Cut.
Will check if I have not done a mess.

IMO you did in a couple of places. Many to many always looks like this: table_A ---< table_in_the_middle >--- table_B
It's the same with versionbookmarks, versions_videostreams, versions_audiostreams

Can you help me understand how it should be done in the correct way?

[phate89]

I know you need many to many. That's not the problem. The problem is that you need many to many between "assets" and "r_persons_roles" (btw person -> people) because if you connect person with asset you don't know if that person in that movie is a director, is an actor, is a producer or all 3 of them since there's no connection between role and movie

So I'm missing a connection? sorry I misunderstood you!
Ok will check and add

You also need to remove the old one. It's connected to the wrong table. you have to remove many to many connection between "assets" and "persons (with "r_assets_persons" in the middle) and add many to many between "assets" and "r_persons_roles" (with a table in the middle)

I have not understood you. You mean we can use static types defined in code instead of a linked table?
Isn't hardcoding not nice?
You mean we can structure the ID link
1xxxxxxxxxx: Movie
2xxxxxxxxxx: Show
etc..?
and this will be done "manually" by the code?

I usually prefer soft linking but is easy to change... we just remove the table

No that's not what I meant. You need an "assettype" column where you set the asset type.
That's not hardcoding. Kodi has a limited set of assets. It's like a "gender" or "month" field. You don't create a table only to store months or genders. You know there are 2 genders and 12 months so you store directly "november", or "11", or "m".
So to retrieve movies it's simpler:
SELECT * FROM assets WHERE assetType=1 (if you prefer numeric version)
SELECT * FROM assets WHERE assetType='movie' (if you prefer explicit version)

In reality I was trying to simplify. In this way the number of AudioTracks is very reduced as they are the formats.
Language (or description) is stored in the Language table.

This should make easier to create icons for the audio tracks and to standardize them. Today is quite a mess.

If those info are needed we have to add them and you're totally right is not anymore an N:M but a 1:N

I don't know if they're needed (we need kodi devs for that). But even if it's not needed is faster to retrieve a 1:N relationship. You have some redundancy but it's faster.

Well languages are always the same, you're right, but scrapers from web return all possible bullshit so if Kodi workis on it to normalize it is great and I agree with you.
If kodi only reads the value from the scraper and saves it... and then the skin shows it .... then sorry no we need a table
You can have IT_it. Italian, Italiano etc...

It's easy to fix and does not make major difference

Sure you can have various versions of Italian but only the code can keep consistency of that. You can't have in languages 3 different versions of italian. It's kodi/addon/plugin/scraper job to recognise the right language. In db you only need the parsed information.

You are totally correct... so I have to remove a link?
Can you help me understand how it should be done in the correct way?

Actually I think I confused bookmarks with favorites. They're 2 separate things.
But he relationship is wrong. This is the same for versionbookmarks and versionviewed (versions_audiostreams and versions_videostreams are correct my mistake).
You need in versionbookmarks:
Position
Name
VersionID (foreign key to the Version id)
UserID (foreign key to the user id)
And in versionviewed
Dateviewed
VersionID (foreign key to the Version id)
UserID (foreign key to the user id)

Another thing.Why you need a "favourites" and a "user_favourites"? Why not many to many between users and versions with a "favourites table? (like versionbookmarks)

[spcano01]

I am so excited to see this keep being worked on!!! I have 8 clients and a very large db on a NAS.

[xnappo]

This isn't meant to spam - but as a follower of this thread Emby for Kodi may be of interest to others:
http://forum.kodi.tv/showthread.php?tid=...pid2023488

[m.savazzi]

This isn't meant to spam - but as a follower of this thread Emby for Kodi may be of interest to others:
http://forum.kodi.tv/showthread.php?tid=...pid2023488

Thank you... sounds very interesting and, also, they will need to update to match the new db structure.