• 1(current)
  • 2
  • 3
  • 4
  • 5
  • 20
New DB Structure for all libraries + DBs [update 2015 06 13 / Released SQL 1.3]
#1
Link to folder with all info and files: http://1drv.ms/1IO2Jn3



Following suggestion I have opened a new thread to discuss the DB.

The original point was to have the possibility of using different DB servers as shared library among different XBMC.
In addition to optimize how the DB is managed (real transactions, one connection to db non thousands, etc..).

This lead to me thinking a new, simple but very flexible db structure to host all possible contents.

I will post the project and model online. I'm using the best (and free) data modeler:
http://www.casestudio.com/enu/default.aspx
TOAD Data Modeler



Here is the summary of first draft of the logical of the new db:

Ok here is the first part of the model:
http://1drv.ms/1Aj0KCz

The movies table still miss a lot of fields and I have not added yet the season-series, also there is a separate are for the TV-EPG

but the core is there...

Some of the key concepts:
1) there is a file table that includes all files independently from what they are
2) files are grouped in libraries
3) users can filter libraries
4) an asset is composed by different files (i.e. multipart movie)
5) assets can be: movie, episode, pictures, music, thumbnail, module, etc...
6) each specific asset does have it's own set of specific properties
7) rating/stars are linked to assets
8) series/seasons are a grouping of assets
9) watched is linked to assets
10) there is a path table where we can store any path format and the format is speficied in path/type. In this way we can have Linux based path/samba/URI etc... also path substitution becomes very easy

CRC32 is used to identify univokely a file

I think this base structure takes in account everything I've read so far in the different posts GSOC and in the code.

For pictures I've already added the EXIF information and we can easily add an Album level as a grouping of pictures like a season/series is a grouping of episodes.
For movies Sets are just a grouping of movies.
If you think I'm useful please use the +/- button to raise my reputation
Reply
#2
Some additional info:
This is a logical view, once we nail down it enough I will convert it in a physical DB.

I'm using inheritance, maybe is obvious but this means that the "movie", "addon", "episode", etc.. are specifications of the "asset" table.
In the phisical model there will be an Assets table with all the common fields and then detailed tables one per each of the childs.
The information can be easily aggregated from a view to be extracted with high efficiency.
I do prefer this model to the model where you define 100 cXX columns whose content changes depending on the "type" of the row. It's more complex to manage, less readable on tables and also does not make an efficent use of indexes and the same column can contain very different data.

In previous thread:
(2014-07-28, 15:58)RockerC Wrote: Oh, and with RetroPlayer being mainlined sooner or later might as well consider adding stand-alone games and game ROMs launched via emulators into the scope of the database model right away as well.

That's very easy to add, and shows very well how flexible is the proposed model.
A game IS an Asset as is based on one or more files.
At logical level we need just to add a "child" to Assets called Game where we put the specific game fields, if you give me them I can add it to the model.
At physical level we will just have one additional table

(2014-07-28, 15:58)RockerC Wrote: Would also be nice if music and music videos could be combined, or at least linked and share information. Just like movies and tv shows / episodes could be linked, or how movies and movie soundtracks could be linked. And how actors / actresses and movies / tv shows / episodes are linked today.

Even this is easy, as all Music, Music Videos, Soundtrack are assets at logical level we will have a bind between child and parent, at physical we will have a AssetRelationships table composed of 2 columns that are AssetIDs of the parent and of the child, this table will create the relationship among all possible assets.
So you can have the Music, bound to the MusicVideo, bound to the movies that uses that song and to the games that uses the same song.
The DB structure will allow that… the point is where you get the info from Smile but this is not my problem…

My objective is to provide the quickest, most flexible and easiest DB structure both for Adding information and for extracting it.
If you think I'm useful please use the +/- button to raise my reputation
Reply
#3
IMO assets should share basic attributes like title, description, thumbnail, tag, "category" and maybe more, so that there is a common interface you could use to access basic data for any asset - even in mixed views. But I think your concept/model is supposed to do this already.

While I mentioned categories above. IMO we also need a flexible categorization for assets, while there should be a way to have global categories (probably using tags for this scenario) and media specific ones. Media specific categories would be movie genres, music genres, game types (adventure, shooter, jumn'n'run, ...) and so on.


How would you treat an Asset group, like a TV-Show, Season or Album? And the interdependencies, like the soundtrack of a movie? Simply add a "movie" db field to an album and link it to a move asset in the later case? Or rather a more generic approach like a "relatedAssets" table?

Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.
Reply
#4
here is second model (sorry is small)..

http://1drv.ms/1nZOhy7

Added Games, VideoClip, Song
Them People, Actors, Singer
and the grouping MusicGroup, Season, TV Series



(2014-07-29, 21:25)da-anda Wrote: IMO assets should share basic attributes like title, description, thumbnail, tag, "category" and maybe more, so that there is a common interface you could use to access basic data for any asset - even in mixed views. But I think your concept/model is supposed to do this already.

I do agree, I need to understand which are common properties among all childs


(2014-07-29, 21:25)da-anda Wrote: While I mentioned categories above. IMO we also need a flexible categorization for assets, while there should be a way to have global categories (probably using tags for this scenario) and media specific ones. Media specific categories would be movie genres, music genres, game types (adventure, shooter, jumn'n'run, ...) and so on.

On categories and genres I was thinking to have a global table for everything as they are usually a limited number per each type with an enumerable property that specifies if is a game genre or a videoclip genre or a movie/tv show genre.

Indexing on that property would make quick to have snapshot selects *all genere for type X"

(2014-07-29, 21:25)da-anda Wrote: How would you treat an Asset group, like a TV-Show, Season or Album? And the interdependencies, like the soundtrack of a movie? Simply add a "movie" db field to an album and link it to a move asset in the later case? Or rather a more generic approach like a "relatedAssets" table?
TV-Show, Season, Album, Sets are all grouping of Assets.

For interdependencies I wrote above I would go for relatedAsset is just a table with 2 asset id parent (i.e. movie) and child (i.e. soundtrack)
Is more flexible as a song can be part of multiple movies, of a game, etc...
Again indexes and a view will make queries very nice

(2014-07-29, 21:25)da-anda Wrote: Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.

Added them in latest version. I use inheritance here too.
There is a table Person then there are the Childs that are Actor, Singer... I do not know if we need to add Director or Staff kind of table

Note that I added a Table MusicGroup to group People Smile

(2014-07-29, 22:12)m.savazzi Wrote: here is second model (sorry is small)..

http://1drv.ms/1nZOhy7

(2014-07-29, 21:25)da-anda Wrote: IMO assets should share basic attributes like title, description, thumbnail, tag, "category" and maybe more, so that there is a common interface you could use to access basic data for any asset - even in mixed views. But I think your concept/model is supposed to do this already.

I do agree, I need to understand which are common properties among all childs


(2014-07-29, 21:25)da-anda Wrote: While I mentioned categories above. IMO we also need a flexible categorization for assets, while there should be a way to have global categories (probably using tags for this scenario) and media specific ones. Media specific categories would be movie genres, music genres, game types (adventure, shooter, jumn'n'run, ...) and so on.

On categories and genres I was thinking to have a global table for everything as they are usually a limited number per each type with an enumerable property that specifies if is a game genre or a videoclip genre or a movie/tv show genre.

Indexing on that property would make quick to have snapshot selects *all genere for type X"

(2014-07-29, 21:25)da-anda Wrote: How would you treat an Asset group, like a TV-Show, Season or Album? And the interdependencies, like the soundtrack of a movie? Simply add a "movie" db field to an album and link it to a move asset in the later case? Or rather a more generic approach like a "relatedAssets" table?
TV-Show, Season, Album, Sets are all grouping of Assets.

For interdependencies I wrote above I would go for relatedAsset is just a table with 2 asset id parent (i.e. movie) and child (i.e. soundtrack)
Is more flexible as a song can be part of multiple movies, of a game, etc...
Again indexes and a view will make queries very nice

(2014-07-29, 21:25)da-anda Wrote: Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.

Added them in latest version. I use inheritance here too.
There is a table Person then there are the Childs that are Actor, Singer... I do not know if we need to add Director or Staff kind of table

Note that I added a Table MusicGroup to group People Smile
If you think I'm useful please use the +/- button to raise my reputation
Reply
#5
I don't know if it's already in your schema (the 2nd one is really impossible to read). How will you handle splitting of categories? It is one of the biggest limits of the current design.
If an user wants to split for example tv shows in tv series, anime, documentary and movies in movies, cartoons, anime you can only filter (usually by bath) and you have to manually configure everything because there's no actual separation.. Is there a solution for this problem?
And unother thing. Today services like netflix or spotify are more popular day by day and the request for integration of online contents like this is growing. Is there something to add to the db structure to make this easier?
Reply
#6
Might I suggest some db changes for your consideration:

  1. There have been some threads discussing getting proper support for non-music audio content, like audio books and podcasts, that don't belong in the music library. This might be a good time to lay the foundation by introducing tables for such content. This type of content requires some fields that doesn't exist for music:
    • Resume position. An audiobook might span multiple files so they would need a flattening feature, like multi-part movies, and the resume function need to keep track of the position on a per-book rather than on a per-file basis.
  2. There are two interpretations what the "lastplayed" field for video content actually means; The start of playback, or completion of playback. Maybe this field should be split into two to allow for both valid use-cases. More info here: http://trac.xbmc.org/ticket/14441
  3. Fields for the number of audio channels, bitrate, bitdepth and language for music to enable smart playlists to filter on those properties.
  4. Replay gain field for music videos. This is however not yet supported by the player.
Reply
#7
As the second schema is completely unreadable I'll leave a short comment on the first one.

One very basic and important thing I'm missing is the possibility for user-defined media types like "home videos", "documentaries", "concerts" etc. which don't exactly fit into movies, tvshows or musicvideos (i.e. they can have different/additional properties). If we go with a new DB schema this must be a possibility without having to add new tables to the database schema "manually" (i.e. through code changes). Obviously this is not possible with the current code and it will also be tricky to support scrapers for these user-defined media types but it has been requested so many times over so many years now that IMO it is a must for any new DB schema approach.
Always read the online manual (wiki), FAQ (wiki) and search the forum before posting.
Do not e-mail Team Kodi members directly asking for support. Read/follow the forum rules (wiki).
Please read the pages on troubleshooting (wiki) and bug reporting (wiki) before reporting issues.
Reply
#8
Montellese, would it be possible to dynamically create/update the DB schema based on an XML schema description? (or several descriptions). As I don't think it's possible in C++ to use magic functions, the aggregated extra metadata which is not part of a generic Asset object, could be stored as array/vector/whateverC++HasToOffer and would be accessed via someAsset->getMetadata('foo.bar'). Does this sound reasonable?

(2014-07-29, 22:12)m.savazzi Wrote:
(2014-07-29, 21:25)da-anda Wrote: How would you treat an Asset group, like a TV-Show, Season or Album? And the interdependencies, like the soundtrack of a movie? Simply add a "movie" db field to an album and link it to a move asset in the later case? Or rather a more generic approach like a "relatedAssets" table?
TV-Show, Season, Album, Sets are all grouping of Assets.
Yes, but what is grouping them? Another Asset or a special group type?

(2014-07-29, 22:12)m.savazzi Wrote: For interdependencies I wrote above I would go for relatedAsset is just a table with 2 asset id parent (i.e. movie) and child (i.e. soundtrack)
Is more flexible as a song can be part of multiple movies, of a game, etc...
you probably also have to store some meta information along with the relation table. An asset could have several interdependencies to the same asset type but for different relations.

(2014-07-29, 22:12)m.savazzi Wrote:
(2014-07-29, 21:25)da-anda Wrote: Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.

Added them in latest version. I use inheritance here too.
There is a table Person then there are the Childs that are Actor, Singer... I do not know if we need to add Director or Staff kind of table
This won't work out with my example given above. You would end up with 5 Jaret Letos in the database while there must only be one. So persons HAVE to be unique entities and have several related "activities" (maybe a native can find a better word for these relations). An "activity" would then be Actor, Singer, Director and hold the metadata related to that activity as well as the relation to the according asset.

(2014-07-29, 22:12)m.savazzi Wrote: Note that I added a Table MusicGroup to group People Smile
Nice, but note that MusicGroups/Bands also need meta data - so they are probably also just Assets, or derive from the same table/object than asset groups
Reply
#9
Oh, I'd probably also add a "history" table which is tracking playback stats (drop "viewed" and call it "history" which is more generic and can also be used for audio). So instead of incrementing a playcount value, add an entry to the history. This history could then be enriched with additional meta-data (begin[date], finished[date], progress, user/profile, guests...)
Reply
#10
(2014-07-30, 09:25)da-anda Wrote: Montellese, would it be possible to dynamically create/update the DB schema based on an XML schema description? (or several descriptions). As I don't think it's possible in C++ to use magic functions, the aggregated extra metadata which is not part of a generic Asset object, could be stored as array/vector/whateverC++HasToOffer and would be accessed via someAsset->getMetadata('foo.bar'). Does this sound reasonable?

Creating the DB schema on an XML schema description is certainly somehow possible (garbear has done something similar in that direction) but updating the schema will be very hard to get right (especially since every RDBMS supports different ALTER TABLE features and functionalities).

The getMetadata() stuff wouldn't be such a big deal (not easy but also not impossible).
Always read the online manual (wiki), FAQ (wiki) and search the forum before posting.
Do not e-mail Team Kodi members directly asking for support. Read/follow the forum rules (wiki).
Please read the pages on troubleshooting (wiki) and bug reporting (wiki) before reporting issues.
Reply
#11
Again for reference, much looks to have been discussed in detail before and some work even started here:

http://forum.xbmc.org/showthread.php?tid=128054

http://forum.xbmc.org/showthread.php?tid=165024

http://forum.xbmc.org/showthread.php?tid=141169
https://github.com/garbear/xbmc/commits/photolibrary

http://forum.xbmc.org/showthread.php?tid=155304
https://github.com/declankenny/Video_Database_Proposal

http://forum.xbmc.org/showthread.php?tid=73831
http://wiki.xbmc.org/index.php?title=Dat...Schema_4.0
http://wiki.xbmc.org/index.php?title=Dat...hema_4.0/a

http://forum.xbmc.org/showthread.php?tid=165929

http://forum.xbmc.org/showthread.php?tid=105147
http://forum.xbmc.org/showthread.php?tid=54061

http://forum.xbmc.org/showthread.php?tid=51605

http://forum.xbmc.org/showthread.php?tid=37449

http://forum.xbmc.org/showthread.php?tid=199805

As far as I can tell none of these previous ideas and concepts have yet made it into mainline.
Reply
#12
(2014-07-29, 23:22)phate89 Wrote: I don't know if it's already in your schema (the 2nd one is really impossible to read). How will you handle splitting of categories? It is one of the biggest limits of the current design.
If an user wants to split for example tv shows in tv series, anime, documentary and movies in movies, cartoons, anime you can only filter (usually by bath) and you have to manually configure everything because there's no actual separation.. Is there a solution for this problem?
I believe post-processing "category" node filtering might already cover that http://forum.xbmc.org/showthread.php?tid=51605 and http://wiki.xbmc.org/index.php?title=Custom_video_nodes

(2014-07-29, 23:22)phate89 Wrote: And unother thing. Today services like netflix or spotify are more popular day by day and the request for integration of online contents like this is growing. Is there something to add to the db structure to make this easier?
Checkout the ideas here and http://forum.xbmc.org/showthread.php?tid=105147 and here http://forum.xbmc.org/showthread.php?tid=165024

(2014-07-29, 22:12)m.savazzi Wrote:
(2014-07-29, 21:25)da-anda Wrote: Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.
Added them in latest version. I use inheritance here too.
There is a table Person then there are the Childs that are Actor, Singer... I do not know if we need to add Director or Staff kind of table

Note that I added a Table MusicGroup to group People Smile
Bikeshedding this now but Director for movies and Composer for (classical) music should be on the same level as Actors, Singers, and Music Groups IMHO. Many movie entusiasts also like to follow Writters and Sceenplay writters. Though bike shed color should not really be that important at this early phase or?
Reply
#13
(2014-07-30, 15:46)RockerC Wrote: I believe post-processing "category" node filtering might already cover that http://forum.xbmc.org/showthread.php?tid=51605 and http://wiki.xbmc.org/index.php?title=Custom_video_nodes
I know you can do it somehow right now. I created this guide to do that. But first of all it's not possible to do it with all media, only with movies and tv shows. second it's not the same thing as actual splitted content in db... Right now the user have to configure everything manually because there's no actual separation in the db..
Reply
#14
This feature is actually asked a lot (really a lot) from remote users.

They do not understand that they can hack skins to do things like that, but that remote have no way to reproduce this segmentation apart from recreating the full internals of XBMC and adding complex configuration for users on a small mobile device.
Reply
#15
(2014-07-30, 09:45)da-anda Wrote: Oh, I'd probably also add a "history" table which is tracking playback stats (drop "viewed" and call it "history" which is more generic and can also be used for audio). So instead of incrementing a playcount value, add an entry to the history. This history could then be enriched with additional meta-data (begin[date], finished[date], progress, user/profile, guests...)

Ok,
will change it

(2014-07-30, 10:28)Montellese Wrote:
(2014-07-30, 09:25)da-anda Wrote: Montellese, would it be possible to dynamically create/update the DB schema based on an XML schema description? (or several descriptions). As I don't think it's possible in C++ to use magic functions, the aggregated extra metadata which is not part of a generic Asset object, could be stored as array/vector/whateverC++HasToOffer and would be accessed via someAsset->getMetadata('foo.bar'). Does this sound reasonable?

Creating the DB schema on an XML schema description is certainly somehow possible (garbear has done something similar in that direction) but updating the schema will be very hard to get right (especially since every RDBMS supports different ALTER TABLE features and functionalities).

I've already done that in Ember Media Manager. Is quite straight forward.

The major point is which abstraction layer we use. I will check if ODBC allow a "cross DB" Alter Table syntax to avoid having the code to manage it.
Btu this is a second step, once the DB is nailed down.

M

(2014-07-30, 16:45)Tolriq Wrote: This feature is actually asked a lot (really a lot) from remote users.

They do not understand that they can hack skins to do things like that, but that remote have no way to reproduce this segmentation apart from recreating the full internals of XBMC and adding complex configuration for users on a small mobile device.

Sorry what do you mean with remote?

On the DB the segmentation is done at data level, much easier, quicker and does not require any configuration on the client apart creating users.
If we want to introduce segmentation based on tags, metadata, etc... is trivial as on a DB is very easy to achieve it.

(2014-07-30, 15:46)RockerC Wrote:
(2014-07-29, 23:22)phate89 Wrote: I don't know if it's already in your schema (the 2nd one is really impossible to read). How will you handle splitting of categories? It is one of the biggest limits of the current design.
If an user wants to split for example tv shows in tv series, anime, documentary and movies in movies, cartoons, anime you can only filter (usually by bath) and you have to manually configure everything because there's no actual separation.. Is there a solution for this problem?
I believe post-processing "category" node filtering might already cover that http://forum.xbmc.org/showthread.php?tid=51605 and http://wiki.xbmc.org/index.php?title=Custom_video_nodes

(2014-07-29, 23:22)phate89 Wrote: And unother thing. Today services like netflix or spotify are more popular day by day and the request for integration of online contents like this is growing. Is there something to add to the db structure to make this easier?
Checkout the ideas here and http://forum.xbmc.org/showthread.php?tid=105147 and here http://forum.xbmc.org/showthread.php?tid=165024

Thanks, will do!

(2014-07-30, 15:46)RockerC Wrote:
(2014-07-29, 22:12)m.savazzi Wrote:
(2014-07-29, 21:25)da-anda Wrote: Your model should also contain persons, be it artists, directors, actors or whatever, while the person itself is neutral, so does not have a specific context. Reason is, the same person could be componist, director, actor, singer, drummer, whatever (Jaret Leto f.e.). So the linking MM table needs to hold the context specific info.
Added them in latest version. I use inheritance here too.
There is a table Person then there are the Childs that are Actor, Singer... I do not know if we need to add Director or Staff kind of table

Note that I added a Table MusicGroup to group People Smile
Bikeshedding this now but Director for movies and Composer for (classical) music should be on the same level as Actors, Singers, and Music Groups IMHO. Many movie entusiasts also like to follow Writters and Sceenplay writters. Though bike shed color should not really be that important at this early phase or?

I do agree. Director, Composer, Writers, Screenplay are childs of Person and can be bound to any asset.

My question was: do they have any specific field for them?
If they have I will create a separate logic element.
If they have not then we need a "type" field in person that will contain "Actor, Director, Compser, Singer, etc..."

(2014-07-30, 08:09)Montellese Wrote: As the second schema is completely unreadable I'll leave a short comment on the first one.

Sorry for this!
I found a nasty story on TOAD Data Modeler:
Quote:Yes, the Toad Data Modeler FREEWARE will only allow less than 25 objects, and will not let you save your model. If you want unlock these abilities, you will need to purchase the commercial version of Toad Modeler. The Freeware is only a free, small sample of what the full, purchased version of the product can do with it's full capabilities.

Will look for a REAL free tool to support us!

M
If you think I'm useful please use the +/- button to raise my reputation
Reply
  • 1(current)
  • 2
  • 3
  • 4
  • 5
  • 20

Logout Mark Read Team Forum Stats Members Help
New DB Structure for all libraries + DBs [update 2015 06 13 / Released SQL 1.3]3