[Montellese]

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.

They use a completely separate database structure (as they are not Kodi specific) which they don't have to change. I think xnappo's point was that it might be worth looking at how they are doing things on the database structure side and maybe get some ideas from there.

[m.savazzi]

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)

Done and updated

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)

Removed table and added field

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.

I've fixed cardinality and left the table to allow ease of expansion. If not needed is simple to remove

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)

should be fixed now

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)

I've updated it to be as versionbookmarks. Look if it works.

Version 1.3 is updated

[m.savazzi]

As always here: http://1drv.ms/1IO2Jn3

there is the logical view, MySQL script, SQL Server scipt, SQLite db

please let me know if everything is ok now and we could proceed.

[zag]

Took a quick look at the mysql version, noticed BirdthPlace was spelt wrong.

Also not sure if r_persons_bands makes sense, since both persons and bands are plural. I'm not sure what that table is for though. To save me reading this whole thread, what does the r_ stand for? I'm a big fan of making databases simple and obvious to even the most novice users

[hideki]

I don't know much about databases, but shouldn't you stick to some name conventions like single vs plural names (like RoleType vs AssetTypes)? I don't mean to be rude, just my 2¢.

To save me reading this whole thread, what does the r_ stand for? I'm a big fan of making databases simple and obvious to even the most novice users

It's a relationship / link / junction table for connecting many-to-many relationships.

[m.savazzi]

Took a quick look at the mysql version, noticed BirdthPlace was spelt wrong.

will check and correct if wrong, thank you for pointing out

Also not sure if r_persons_bands makes sense, since both persons and bands are plural. I'm not sure what that table is for though. To save me reading this whole thread, what does the r_ stand for? I'm a big fan of making databases simple and obvious to even the most novice users

r_ are the relationship tables. the plurals mean that is a M:N relationship. One band can have multiple persons and a person can be part of multiple bands

[m.savazzi]

I don't know much about databases, but shouldn't you stick to some name conventions like single vs plural names (like RoleType vs AssetTypes)? I don't mean to be rude, just my 2¢.

To save me reading this whole thread, what does the r_ stand for? I'm a big fan of making databases simple and obvious to even the most novice users

It's a relationship / link / junction table for connecting many-to-many relationships.

Not rude at all. In theory I should have done it... if is a 1: relationship is Singular
if is a M: relationship is plural

so 1:M relationship among asset and video will be r_Asset_Videos
an M:N relationship among asset and video will be r_Assets_Videos

As I've changed some of the relationships due to suggestions is possible I've messed up some names... will check

[phate89]

Reviewed again. My considerations:
1) VersionViewed relationships are both wrong. in versionviewed you need 2 foreign keys, username with the user id and VersionID with the version id. It needs to look like this:
Version --------< Versionviewed >-------- Users
2) Favorites and versionbookmarks are both wrong. they both miss "VersionID" foreign key and the relationship to version is wrong. They need to look like this:
Version --------< Favorites >-------- Users
Version --------< versionbookmarks >-------- Users
3) Imho it's better the current database solution for bookmarks and watched state. Not 2 separate tables (Versionviewed and versionbookmarks) but only one with a new column named "type". If type=0 is a watched bookmark, if type is 1 it's a regular bookmark and if type 2 it's an episode bookmark. You already need a "type" column because of episode bookmarks so in this way you can drop a table :
http://kodi.wiki/view/Databases#bookmark
4) A video file could be both a tv show and a movie..
5) The table between videostream and version imho is pointless. I understand there's a chance you can have 2 video files with the video that have same length,same track id, same codec, same resolution, same bitrate but what are the chances? 1:10000? it's better to have Version -------< Videostreams without table in the middle. In this way it makes the database a lot simpler (and the same consideration stands for audiostream and subtitles) with only a remote chance of redundancy.
6) Why assets has a size? art has a size (missing), videostream has a size but artwork doesn't.
7) r_Assets_Person_Roles is connected to the wrong table. As the name suggests needs to be connected to Person_roles not to Roles. It needs to look like this:
Assets -------------< r_Assets_Person_Roles >------------- r_Persons_Roles

Also the new gsoc about better profiles support (http://forum.kodi.tv/showthread.php?tid=222006) is actually really connected to this work so maybe you should keep an eye on that.

[RockerC]

Also the new gsoc about better profiles support (http://forum.kodi.tv/showthread.php?tid=222006) is actually really connected to this work so maybe you should keep an eye on that.

Yes please also try to for-fill additional requirement for improved user profile support, checkout http://forum.kodi.tv/showthread.php?tid=228415

[Razze]

I would really like to see something like an externalRef-table

I find it very confusing, that it depends on the scrapper the user uses which id will be in the id fields for tvshows or movies. (correct me if I'm wrong)
So you can get the TVDBID field on one system and end up getting an TMDB id.
It would be much better to have an context to the id we're using and also why don't have multiple externalIds for an item?

So I would propose something like this for an externalRef-table:
internalId, internalIdType,externalId, externalIdType

Both of the types may actually link to their own tables, but to keep it simple for this writeup i'll show it like it's not.

So internalId would be the id the item has in the internal table. internalIdType defines to which table it links, so for e.g. 'show' or 'movie'.
ExternalId could then be imdb, tvdb, tmdb or whatever. And externalIdType would clarify which it type it is so for e.g. 'imdb' or 'tmdb'

In the long run, kodi could also drop the tvdbid and the imdbid form the internal tables and give out an autoincrement internally and then populate the externalRef-table.

[spcano01]

Any update? Really hoping this was possible to test in 16 alphas!

[m.savazzi]

I was just wondering how it could be put in 16 Alpha.

The structure is now ready... the team should just pick it up and update the db and queries...

But how?

M

[m.savazzi]

I find it very confusing, that it depends on the scrapper the user uses which id will be in the id fields for tvshows or movies. (correct me if I'm wrong)

I theory that should not happen. In the DB we use 1 key for the scraping. To add a reference key table is easy, if needed.

M

[phate89]

I was just wondering how it could be put in 16 Alpha.

The structure is now ready... the team should just pick it up and update the db and queries...

But how?

M

The structure is not ready yet. My points of 1.3 version still stands:

Reviewed again. My considerations:
1) VersionViewed relationships are both wrong. in versionviewed you need 2 foreign keys, username with the user id and VersionID with the version id. It needs to look like this:
Version --------< Versionviewed >-------- Users
2) Favorites and versionbookmarks are both wrong. they both miss "VersionID" foreign key and the relationship to version is wrong. They need to look like this:
Version --------< Favorites >-------- Users
Version --------< versionbookmarks >-------- Users
3) Imho it's better the current database solution for bookmarks and watched state. Not 2 separate tables (Versionviewed and versionbookmarks) but only one with a new column named "type". If type=0 is a watched bookmark, if type is 1 it's a regular bookmark and if type 2 it's an episode bookmark. You already need a "type" column because of episode bookmarks so in this way you can drop a table :
http://kodi.wiki/view/Databases#bookmark
4) A video file could be both a tv show and a movie..
5) The table between videostream and version imho is pointless. I understand there's a chance you can have 2 video files with the video that have same length,same track id, same codec, same resolution, same bitrate but what are the chances? 1:10000? it's better to have Version -------< Videostreams without table in the middle. In this way it makes the database a lot simpler (and the same consideration stands for audiostream and subtitles) with only a remote chance of redundancy.
6) Why assets has a size? art has a size (missing), videostream has a size but artwork doesn't.
7) r_Assets_Person_Roles is connected to the wrong table. As the name suggests needs to be connected to Person_roles not to Roles. It needs to look like this:
Assets -------------< r_Assets_Person_Roles >------------- r_Persons_Roles

Also the new gsoc about better profiles support (http://forum.kodi.tv/showthread.php?tid=222006) is actually really connected to this work so maybe you should keep an eye on that.

Plus imho instead of an assets table adding a column called "asset_type" in bookmarks, favorites etc etc is a better solution. All the new tables in the current db uses this approach. so if we want all the ratings for a movie we search "WHERE movieID=1839 AND asset_type='movie'".
Also we can add a rating table so we could store more than one rating at once. Kodi don't support it yet but I have a pr ready for this.

[phate89]

If you want i took the mysql version and changed it with workbench with my suggestions:
https://dl.dropboxusercontent.com/u/2219...dified.mwb (it needs mysql workbench but you can see the schema graphically ordered)
This was your version if you want to compare it:
https://dl.dropboxusercontent.com/u/2219...%201.3.mwb

I fixed the versionviewed, versionbookmarks and favorites foreign keys, I added the rating folder, remved the tables between streams and version, added a default version foreign key in asset and fixed the assets_person_roles relationship.