Please allow me to provide more details an ALL the request you made so far:
Original design concepts:
1) To be very quick
2) To remove all replicated information
3) To create a relational structure
4) there is a file table that includes all files independently from what they are
5) files are grouped in libraries
6) users can filter libraries
7) an asset is composed by different files (i.e. multipart movie)
8) assets can be: movie, episode, pictures, music, thumbnail, module, etc...
9) each specific asset does have it's own set of specific properties
10) rating/stars are linked to assets
11) series/seasons are a grouping of assets
12) watched is linked to assets
13) 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
14) CRC32 is used to identify univokely a file
15) There is one table Persons for every person. Their role is specified in a separate table (same person is a composer, actor, writer director). Asset Thumbnail linked to Person is the art.
16) We need to have 1 DB for everything, thumbnails, music, pictures, games, etc… in this way the code will be more efficient, data will be smaller, etc…
Requests:
1) 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.
a. Added: Games are just an asset. Please let me know which properties we need specific for a game
2) title, description, thumbnail, tag, "category"
a. Correct is done like that.
b. Note that there is a AssetType table to allow to filter assets. (That is a table so here we can have everything, Movie, Picture, Fanart, Poster, CDArt, Thumbnail, Song, SeriesArt, etc…
c. Thumbnail is, at all effects, an Art element. So they will not have a separate file
d. The Asset2Assets table will create the link between assets, for example the link between the movie and it’s fanart/clearart/cdart, it’s songs, it’s
3) 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.
a. There is a Tags folder that is bound to assets and to types. This will allow to have all tags in a single table and to apply the correct ones to the different assets (i.e. it can be used for genere in movies and for EXIF tags for pictures)
4) 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?
a. RelatedAssets, is the table Asset2Asset. This will lead to maximum flexibility so you can have a single view where a movie is related to the TV series, to the fanart, to the soundtrack, etc…
5) 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.
a. Done, great idea
6) 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?
a. Native in the model. You can apply all the tags you want.
7) 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.
a. We can easily add a new table for specific audio contents.
8) 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
a. There is a bookmarks table with a time field that is bound to the Asset. What that times means depends from the player. We can add an “integer” position if we need. If we want we can bound that to users to have the per user bookmark.
b. Also we can add a Bookmark type so that we can separate between user bookmars, automatic bookmarks (lat played position) etc…
9) 3.Fields for the number of audio channels, bitrate, bitdepth and language for music to enable smart playlists to filter on those properties.
a. These are properties of the Song asset.
10) 4.Replay gain field for music videos. This is however not yet supported by the player.
a. What is this_
11) 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?
a. Tags management is a good solution present in the structure
12) Movies/mature/teen/child: where different profiles would have access to either mature and everything below, teen and everything below, or just child.
a. Can be easily added with a link amongs Tags and users or with Ratings and Users. From a data structure everything is there. Is more related to the application logic.
13) If you guys are revamping the database, can i humbly request that you use meaningful field names. EG the movie table in the current videos78 database has fields
a. All fields must have a “speaking name”. No C01 or similar.
14) is to make smart playlists more and more powerful, so user can create their own library nodes, skins can show content in some sort of widget on the homescreen etc.
a. This data structure will allow a lot of filtering to happen at DB level, having much less data going around and freeing up power for Smart Playlists to work better. The example of parental rating will be handled directly at DB level filtering assets.
15) a. Profiles which have separate databases so each profile can have it's own watched status etc, however the downside of this is that metadata needs to be scraped for each profile.
or
b. Profiles which share the master database so metadata is only scraped the once, however it's then not possible to have separate watched status for each Profile.
The ideal solution in my opinion would be:
1. Profiles are assigned the sources that they will be allowed to access along with a permission level.
2. Metadata is scraped the once so a single metadata store is always maintained and can be initiated by any Profile that has permission, thus metadata is shared between all profiles that have access to a particular source.
3. Each profile to be configurable as to whether user actions should be tracked on a per profile basis, so for example the database could assign watched status per profile.
a. DB should support the ideal model. NO duplicated info. Only one DB and only ONE scraping. Users are stored in the DB and are bound to content with their permission.
b. Right now I’ve bound users to libraries but we can bound users to EACH asset if we want allowing to have a granularity down to level one (you can choose with fanart is shown to who). NOTE: this is at Database level, application should manage all of that
16) but very functional. fields can be repurposed without renaming them. if a dynamic db is wanted, this part will only get worse.
a. We will NOT have repurposing of fields. With SQL is easy enough to rename a column if needed.
b. Repurposing or hybrid data columns are not meaningfull in a DB like this one. Is not simple but is not that big to have to add such huge complexity to code and developers. Approach is keep it simple -> column name should be reasonable. Queries and code will be much better and readable
17) Watched status was only meant to be one example, for sure resume points, bookmarks and other stuff like that should have the ability to be set on a per profile basis in the database, whilst keeping a single metadata (by that I mean posters, movie info etc) store.
a. Done, and native from the DB
18) Well yes but I prefer to add a reminder, as for watched status in previous post there's was a good proposal about a "log" table to handle this particular thing. So listing what should be tied to profile or not or both may be a good start Smile Anyway since I repeat myself I'll stop but defining the target shows the path, building a path without target have few chance to arrive to correct target Wink
a. We can add that. Explain better what you’d like to see as Log. For example I like the idea that the “last played” smart playlist changes depending on the User that has logged on
19) What we have to take into account:
- restrict media to certain shares/folders
- restrict by meta-data like PG ratings or don't let kids listen to songs with explicit lyrics
- what other restrictions for profiles are required?
- not only filter the media itself according to the rules, but also things like genres (don't show "adult" genre to kids)
- how to do this in a well performing way without having to do (much) postprocessing of the items - so can it be done on DB level?
a. All of this is in the DB strcutre. And Yes the idea is to move all possible filtering and sorting at DB level that is, for sure, more performing than locally.
b. Local layer should only do presentation and postprocessing, not Data functions (filtering, ordering, grouping, etc…) that’s DB stuff
20) The main question about profiles is about external access (as I tried to talk a lot in previous thread), right now you need to log in in Xbmc to activate a profile then all DB access are tied to the profile.
Since it seems one of the future direction is all about central database or replication it's important to decide how those profiles will be used from a remote point of view and internally.
Because profiles are not only tied to database but also addons, cache and tons of things, it's important to know the target first.
21) We can add ALL profiles and info in the DB if we want to avoid having external XML files. At this point XBMC will start and open the main connection to DB, load users and we can move on from there. There is no need to have a per user DB… even on SQL Lite the DB is just ONE with everything we need in there
a. For sure the current DB handing with thousands of connections IS NOT good.
22) "if a new schema happens then it will only happen via incremental changes."
"I'm not saying we don't need a new DB schema. All I'm saying is that it is too big of a task to come with a completely new DB schema that doesn't match anything that is in the current DB schema and replace the whole thing including updating old databases etc. We have to do it in (little) steps and IMO one of them is to get the current schema into something more generic that will be easier to migrate and that will also be easier to maintain in the meantime."
a. NO. Working only on small changes will never lead to the correct design as you will always remain bound to the outdated (or sub optimal) initial design. What we are doing is a new DB focused on performance and efficiency for all platforms.
b. The correct approach is NOT to modify existing DB but to build a simple Migration procedure that will be executed ONCE and will extract the (good) data from the existing DB and will insert it in the NEW DB. After that missing information will be integrated in the new DB. This will need to be really transactional!