[phate89]

Kodi or the user can decide which is the "parent". For me is the first one that is added and scraped.
Is very simple.

That's not simple. It's random. The first win... And it's a relationship that doesn't exist in the reality.

I reviewed the proposal and it does not work:
1) it does not support the scenario where each version is has to be considered a different asset (as I want)
2) it does not support multipart assets as the Art is bound to File, is not correct
3) it does not support multimovie file as the Art is bound to File, is not correct
4) Art itself IS an asset and should not be outside of the Asset element

New version is coming soon

No i always considered both methods and both works well with my changes. But I forgot one relationship in that revision. Here's the working one:
https://dl.dropboxusercontent.com/u/2219...gical2.pdf

[Fice]

And now to sth. completely different: Favourites!
What's your stand on moving the favourites into the db? Right now they're stored in an xml file and completely decoupled from the db.
That brings some issues:

1. They can get out of date: If i remove an item from db it will not be removed from the favs (i think, haven't checked ). If i change the fanart/thumb of an item, the item in the favourites will not be updated.
   
2. Little to no interaction with the item possible: there is only one action in the favourites xml: The one that's executed on enter. I would love to be able to queue my favourite items via context menu
   
3. when we have the metadata from the db available, we can add more advanced stuff like filters et. al. (There are a few feature requests, where the main problem is, that a huge list of favourites is not filterable).
   

The downside: Right now power users can add pretty much everything to the favs, like: calling scripts with custom parameters or what not.
If we want to keep that flexibility, we could merge the content of the favourite.xml (only content manually added for power users) and the favourites from the db (everything that can be added via kodi's gui) inside the favourite window.

[m.savazzi]

Kodi or the user can decide which is the "parent". For me is the first one that is added and scraped.
Is very simple.

That's not simple. It's random. The first win... And it's a relationship that doesn't exist in the reality.

Is not random is "user decides"
In other words a user can decide that when a Director's cut exists that is Always the parent.
OR
one can be elected to be the parent (aka default)

There is good flexibility

I reviewed the proposal and it does not work:
1) it does not support the scenario where each version is has to be considered a different asset (as I want)
2) it does not support multipart assets as the Art is bound to File, is not correct
3) it does not support multimovie file as the Art is bound to File, is not correct
4) Art itself IS an asset and should not be outside of the Asset element

New version is coming soon

No i always considered both methods and both works well with my changes. But I forgot one relationship in that revision. Here's the working one:
https://dl.dropboxusercontent.com/u/2219...gical2.pdf

Thank you for your good and detailed PM with explanations. Now is clear how it works.

I've sent you a doubt on the Viewed and Bookmarks tables bound to version and not to asset. I think we have an issue there.

---

And now to sth. completely different: Favourites!
What's your stand on moving the favourites into the db? Right now they're stored in an xml file and completely decoupled from the db.
That brings some issues:

1. They can get out of date: If i remove an item from db it will not be removed from the favs (i think, haven't checked ). If i change the fanart/thumb of an item, the item in the favourites will not be updated.
   
2. Little to no interaction with the item possible: there is only one action in the favourites xml: The one that's executed on enter. I would love to be able to queue my favourite items via context menu
   
3. when we have the metadata from the db available, we can add more advanced stuff like filters et. al. (There are a few feature requests, where the main problem is, that a huge list of favourites is not filterable).
   

The downside: Right now power users can add pretty much everything to the favs, like: calling scripts with custom parameters or what not.
If we want to keep that flexibility, we could merge the content of the favourite.xml (only content manually added for power users) and the favourites from the db (everything that can be added via kodi's gui) inside the favourite window.

No problem at all is VERY simple. Will add a Favorites table bound to Assets so you can have a nice Favorites management for everything, Movies, Episodes, Series, Seasons, Pictures, Music, Albums, Songs, Games, etc....

Question: do you just need a "flag" favorite or not or you want a favorite grouping?
(in the first case teh favorites table is just a list of AssetID in the second case we can add a text field that can be used for grouping)

[m.savazzi]

the fields I use in Game info tag:

• File path
  
• Title
  
• Platform
  
• Game Code (unique ID embedded in ROM)
  
• Region
  
• Publisher / Licensee
  
• Format (PAL/NTSC)
  
• Cartridge Type, e.g. "ROM+MBC5+RAM+BATT" or "CD"
  

I started with these fields because they can be populated directly from the ROM's data. you could get ideas about fields to add from thegamesdb.net (http://thegamesdb.net/game/1318/ ) like player count, release date, etc

keep in mind that another layout is needed for save states. the example save state database i created had fields for savestate path, rom path, rom CRC and gameclient used to create the savestate.

Please check v.1.0 of the model as I should have put everything in:


More in detail

• File path -> it is stored in the Paths and Files tables. In this way you may have 1 folder (games) with all the game images there and they will be detected correctly
  
• Title -> is in the Asset
  
• Platform -> added in Games properties. Is it a number? a string? which length?
  
• Game Code (unique ID embedded in ROM) -> added in Games properties. Is it a number? a string? which length?
  
• Region -> added in Games properties. Is it a number? a string? which length?
  
• Publisher / Licensee -> added a specific entity as a Publisher may have more games
  
• Format (PAL/NTSC) -> added a specific entity as there are few formats
  
• Cartridge Type, e.g. "ROM+MBC5+RAM+BATT" or "CD"-> added a specific entity as there are few formats
  
• savestate -> added a specific entity to handle savestate bound to user
  • file-> it is stored in the Paths and Files tables. In this way you may have multiple files for a saved state
    

Question: as savedstate is bound to a specific game and a user do you still need rom path and rom CRC/ One game can have multiple roms?

---

Link to folder with data model: http://1drv.ms/1lB02N4
Link to PDF view of the model: http://1drv.ms/1qiN5CF

[da-anda]

had a quick look (will have a closer one later) and I think we could combine some tables because they are basically the same, just used for different media:
- Film Studios / Music Labels
- video Bookmarks / game SaveStates
- stream info (subtitles, audio, video)

And IMO not a Person is linked to an Asset, but its Role, or am I wrong on that one

[m.savazzi]

had a quick look (will have a closer one later) and I think we could combine some tables because they are basically the same, just used for different media:
- Film Studios / Music Labels

If everyone is fine we can join this

- video Bookmarks / game SaveStates

These are very different. Bookmark is a position in a file (either byte or SMTPE) while SaveStates is a link to a file.
So I do not think we can join them

- stream info (subtitles, audio, video)

Well the three cannot be joined as an assed can have one audio and one video
multiple audios, multiple videos, etc...
Subtitle has different info too.

So we need three entities to manage all possible scenarios

And IMO not a Person is linked to an Asset, but its Role, or am I wrong on that one

You are right Role must be bound to both Person and Asset. Updated

[da-anda]

Ah, so your schema is an Object schema not a DB schema. My suggestions for combining where related to sharing the same DB table for certain objects

[phate89]

Well the three cannot be joined as an assed can have one audio and one video
multiple audios, multiple videos, etc...
Subtitle has different info too.

So we need three entities to manage all possible scenarios

I think he meant to join them as movie, episode, video are joined in asset. So you have a stream main with audio stream, video stream and subtitle stream. So a stream could be an audio, a video or a subtitle stream and One asset or version could be connected to 1,2,3,4 etc etc streams.

Another question related to subs. How external subtitle works with upnp? If I remember well somewhere I read we might need to store external subtitles in database to tackle the lack of external subtitles support of upnp. Is it true?
Also there is an old pending pull request from ace to add external audio support.
So we need to store these files? If the answer is yes we could simply connect streams to files so a version of an asset could have for example 2 audio streams, one internal and one external (external audio file) and 2 subtitle streams, one internal and one external (external subtitle file)

[m.savazzi]

Ah, so your schema is an Object schema not a DB schema. My suggestions for combining where related to sharing the same DB table for certain objects

Right now is the logical schema. Once we boild down this I will create the physical DB structure

---

Well the three cannot be joined as an assed can have one audio and one video
multiple audios, multiple videos, etc...
Subtitle has different info too.

So we need three entities to manage all possible scenarios

I think he meant to join them as movie, episode, video are joined in asset. So you have a stream main with audio stream, video stream and subtitle stream. So a stream could be an audio, a video or a subtitle stream and One asset or version could be connected to 1,2,3,4 etc etc streams.

Ok ,understood. I will check that but I think there are no common fields among the three so it would not create any advantage,.
I've already moved "Language" to it's own entity.

Another question related to subs. How external subtitle works with upnp? If I remember well somewhere I read we might need to store external subtitles in database to tackle the lack of external subtitles support of upnp. Is it true?

Here I need more info from UPnP team.
In current DB structure ALL files are stored in the DB associated to the library independently on where they are.
In other words is Kodi's file scraping process that should check for the different files and add them correctly to the DB.

So: DB supports scenario. In addition DB can provide a unified view of related assets. Immagine you have on the UPnP share the video and downloaded locally the Subtitle file. DB will allow you to see them as related
It's quite cool.

Also there is an old pending pull request from ace to add external audio support.
So we need to store these files? If the answer is yes we could simply connect streams to files so a version of an asset could have for example 2 audio streams, one internal and one external (external audio file) and 2 subtitle streams, one internal and one external (external subtitle file)

Yes multiple audio is native in the DB.
Assume you have file: movie foo.mkv with 1 audio
then you have movie foo (eng).ac3

In the DB you will have
asset 1: movie foo
version 1: bound to movie 1
file 1: bound to version 1- movie foo.mkv
audiotrack 1: bound to file 1, version 1 - all mkv audio details
file 2: bound to version 1- movie foo (eng).ac3
audiotrack 2: bound to file 2, version 1 - all ac3 properties

[spcano01]

Any update on this topic?! This is by far the most exciting feature request I've seen, and hope it makes 14 or 15 for sure!!

[zag]

The design is of course one thing and very welcome.

But the implementation is the hard bit after that

[takoi]

Have you guys thought about storing general information about tv shows such as how many season/episodes a tv show has, and actual episode info independent of whether the file or media exist? Probably the first main obstacle on how to Show missing episodes in TV Shows list is to get this info from somewhere. Currently the only thing that's stored is "episodeguide" which only the raw xml output of the scraper, for late scraping. Very odd to store this with the tv shows btw.

[pecinko]

This, IMHO, should be a problem for future addon/media manager to solve, not DB itself.

[phate89]

In theory even with your current layout you can store season information without a file. The db is not the issue.
But I don't think it's a good thing to do since kodi doesn't update everytime the info but only once.
For example I'm Italian and if I scrape the whole series when I add the first episode I will get the info there are at the moment I scrape the first time. They might be incomplete (old/missing artwork, less episode votes missing translated episode/plot). Probably it's not a big deal for someone but for example for foreign users that wants localized plot and tiles (and maybe provide it theirself) it is..
I think it's possible to get it with an addon.
There are 2 options. The first is a plugin that shows a list of tv shows you have in the db. When you select a tv show it will get data from thetvdb, compare them with files available in the database and shows missing. This is strictly to see what files are missing and quite easy and faster.
The second one is trickier and probably requires helix (Gotham can't add sources from code).
Everytime after an update (or manually) the addon runs and search for missing files. If files are missing it creates dummy files in addon_data folder and it scrape that folder so you will have them directly in the database. The problem with this approach is that you run twice the scan and it's harder to build but it's a lot more powerful (for example you could add a tag to dummy episodes and filter them easily).
I might try to write something like this if I found some time but it's not easy

[maikito26]

Just a thought but I'd love if in the future we could have better handling for multiples users watching simultaneously. This would mean separate databases for users, but being able to track progress for each one you are watching from.

An application of this would be if my wife is logged in on her profile and watching a tv show or movie and then I join her. Traditionally my profile and watched status would be untouched. But if I could 'Check in' from my phone and flag that I'm here and watching, it would update in my profile's library too. This way I can keep my progress separated from hers and still be able to watch together when time permits.