WIP Database Redesign - A Proposal - Printable Version +- Kodi Community Forum (https://forum.kodi.tv) +-- Forum: Development (https://forum.kodi.tv/forumdisplay.php?fid=32) +--- Forum: Kodi Application (https://forum.kodi.tv/forumdisplay.php?fid=93) +--- Thread: WIP Database Redesign - A Proposal (/showthread.php?tid=155304) |
Database Redesign - A Proposal - DecK - 2013-02-08 As my post here got no traction at all, I've decided to take it a step further and work up a prototype for a new video database. The attached files consitiute that prototype. This prototype has the ability to ;
The design includes 25 tables, categorized into three broad groups as follows. Lookup Tables All these tables are of the form (ID,Name) 1/ NodeType - contains a list of media types, each entry in the library will be of one and only one of these types. Values in the sample database are 0 Unknown 1 Movie 2 Episode 3 Music Video 4 Season 5 TV Show 6 Set 7 Genre 8 Studio 9 Country 10 Tag 2/ BookmarkType - contains a list of bookmark types. 0 Standard 1 Resume 2 Episode 3/ StreamType - contains a list of stream types. 0 Video 1 Audio 2 Subtitles 4/ ArtType - contains a list of artwork types. 1 Poster 2 Thumb 3 Banner 4 Fanart 5 CDArt 6 ClearArt 7 Logo 5/ LinkType - contains a list of the types of links that can be created, either between entries in the library and other entries or between entries and people. 1 Node has Actor 2 Node has Director 3 Node has Producer 4 Node has Writer 5 Node has Genre 6 Node has Studio 7 Node has Country 8 Node has Tag 9 Node in Set 10 Tv Series has Season 11 Season has Episode 12 Movie Links TV Show 6/ Scraper Contains a list of all scarpers that are available to media sources. 7/ Profile Contains a list of all profiles. File Tables This set of tables is used to manage the information about the files and structures used to populate the library. 8/ Source Contains all File Sources. (Basically the records from the current Path table, both only those that are root sources).Has FK's to NodeType and Scraper. 9/ ProfileSourceAccess This table is used to control access to content within a source based on the current active profile. has FK's to Source and Profile. 10/ Directory Contains the set of directories under each source. Only relative paths are stored. Has FK to Source. 11/ File Contans 1 row for each file within a directory. (Different from today's files table, in that it doesn't merge stacked files and doesn't contain history data.) Has FK to Directory. 12/ FileSettings Contains the same info as the current settings table, but has a composite key of File and Profile to allow for per profile settings to be saved. 13/ StreamDetail Unchanged from the current structure except for a FK to StreamType. 14/ NodeLinkFile Used to join files to their coresponding item in the library tables. It also is used to store the data required for File stacking and File spliting. Library Tables The library tables contain all the data need to navigate between items (nodes) in the library, as well as manage the metadata for each node. 15/ Person A rationalized version of the current actor table, with the ability to store local thumbs in a more space efficient manner. 16/ Artwork A rationalized version of the current art table, with the ability to store local art in a more space efficient manner. 17/ ProfileHistory Used to store playcounts and last played data on a per profile basis. 18/ Bookmark Contains the same info as the current table, but has a composite key of File and Profile to allow for per profile bookmarks to be saved. 19/ Node This is the central table of the library sturcture. It contains a record for every node within the library. This table replaces all of the following tables (country, episode, genre, movie, musicvideo, seasons, set, studio, tag, tvshow). Only relatively common data is stored in this table. For attributes of a media type that are unique to that type (album on MusicVideo for example) an auxiliary node table is included. The PK on each aux table is the same as the PK for the related record in the Node table. The set of auxiliary node tables is as follows. 20/ NodeAuxEpisode 21/ NodeAuxMovie 22/ NodeAuxMusicVideo 23/ NodeAuxTvShow 24/ NodeLinkNode Contains all relationships between items in the library. Replaces all the link tables (except links to people). 25/ NodeLinkPerson Contains all relationships between items in the library and people. Replaces all the link to people tables. A Concrete Example Rather than just making this an academic discussion on the pros and cons of various DB designs, I have included a set of scripts to allow developers to migrate a copy of an existing DB to this new structure. All files can be found here.
Notes on the scripts. All new tables are prefixed DK_ in the sample. FK constraints are there only as aid to understanding. I have not added any indexes yet, so performance is not as good as it could be. There are lots more triggers to be written. Migration script will not migrate the following - (musicvideos, stacked files, last played, play count). Migrtation script is merely intended to give a developer a good enough working version of the new structrue. DO NOT USE IT ON YOUR LIVE DATA. The build script contains two sample views to aid in understanding the structure. Much more work to be done here. Not all legacy fields have been added to the NodeAux... tables. But I believe enough have in order to test the proposal. Comments on my taste in movies and or tv shows will be ignored. At this point I am looking for feedback of any kind. Thanks Declan RE: Database Redesign - A Proposal - pko66 - 2013-02-08 It seems a great idea to me, I find that the database is one of XBMC main features but, at the same time, it has a lot of shortcomings... this implementation seems to be better! a few comments/ideas: - One of the worst problems I find in XBMC today is that the interesting feature of multiple profiles is not very practical. The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that. - It would be great to keep watched status and even better metadata related no to file but to ID (episodeID or movieID). That would allow among other things: > keep metadata/watched status when moving files around > keep metadata/watched status when you have more than one version or update the version you have of a work (there are many reasons for multiple versions, for example, you change the file you have to a better quality one, or you keep a movie in 2 files, one dubbed in spanish and another in the original french language, and maybe sometime later you delete both and have another one with 2 audio streams, or you have a movie in 2 files, -CD1 & -CD2 and later you substitute them with a version in just one file, or you have a HD version to watch from powerful clients and a SD version to watch from android/xbox) So, for each profile, when a new movie/episode is added, its ID determines the watched status. Also, when a movie/episode is being marked as "watched", all other files with the same ID in the database would be marked as watched also for that profile. Some data will be related to the file (resolution, codec), some other to the ID (actors, year of production) RE: Database Redesign - A Proposal - jjd-uk - 2013-02-08 (2013-02-08, 16:36)pko66 Wrote: The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.Are you completely sure? Not used Profiles for a while but you used to have the choice I'm sure of using a common database across all Profiles where you want to things like Watched status in sync, or you could use independent databases where is Profile is for a different users so stuff like Watched status needs to remain separate. Taken from wiki Quote:Media Info Doesn't Shares with Default allow a common database with all Profiles allowed to update the database. RE: Database Redesign - A Proposal - bnevets27 - 2013-02-08 (2013-02-08, 16:51)jjd-uk Wrote:(2013-02-08, 16:36)pko66 Wrote: The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.Are you completely sure? I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time. RE: Database Redesign - A Proposal - DecK - 2013-02-08 (2013-02-08, 16:36)pko66 Wrote: The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.Yes, this approach would mean a single database for all profiles which in turn means a single library update for all profiles. (2013-02-08, 16:36)pko66 Wrote: It would be great to keep watched status and even better metadata related no to file but to ID (episodeID or movieID).Watched status is related to the library node, not the file in this model. Same thing goes for metadata. (2013-02-08, 17:24)bnevets27 Wrote: I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.That is correct, and is one of the main issues that I am trying to address here. RE: Database Redesign - A Proposal - narvatu - 2013-02-08 This proposal is only about VIDEO Database? What about music, photo and overcoming games? RE: Database Redesign - A Proposal - jjd-uk - 2013-02-08 (2013-02-08, 17:24)bnevets27 Wrote: I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis RE: Database Redesign - A Proposal - DecK - 2013-02-08 (2013-02-08, 17:55)jjd-uk Wrote: So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis? That is one of the things I want, yes. I use two profiles and currently that means seperate updates (effort dupplication) and seperate databases (data duplication). Both of these are bad things from a design perspective. This proposal would remove these duplications. Re: Database Redesign - A Proposal - Martijn - 2013-02-08 And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B Profile 3 part of source A and B Profile 3 wants to have different scraper settings than profile 2 or 1 RE: Database Redesign - A Proposal - DecK - 2013-02-08 (2013-02-08, 18:48)Martijn Wrote: And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B Use Case 1 above is already supported in this model. That is the purpose of the 'ProfileSourceAccess' table. In your example that table would look like this Code: ProfileID SourceID Use Case 2 is not one that I had considered quite frankly. It woud require a change in the design, but I believe that it may be possible. I do wonder, however, if this is a common requirement. Re: Database Redesign - A Proposal - Martijn - 2013-02-08 It is possible now so it should still be. RE: Database Redesign - A Proposal - bnevets27 - 2013-02-08 (2013-02-08, 17:55)jjd-uk Wrote:Yup(2013-02-08, 17:24)bnevets27 Wrote: I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis Actually all I want is two profiles for watched status. Really two different coloured check marks on the same profile would be a fine solution for me. RE: Database Redesign - A Proposal - DecK - 2013-02-08 (2013-02-08, 19:10)DecK Wrote:(2013-02-08, 18:48)Martijn Wrote: And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B (2013-02-08, 19:11)Martijn Wrote: It is possible now so it should still be. I need to correct myself, use case 2 above is indeed supported with this model. This would be achieved by having seperate source records for Proflie 3, which would point to the different scraper, but would reference the same location. Code: SCRAPER Table RE: Database Redesign - A Proposal - Martijn - 2013-02-08 you need to take in account scraper settings too. not only different scrapers but also different language setting / use folder name / dvd order RE: Database Redesign - A Proposal - DecK - 2013-02-08 Martijn What table/columns are those scraper settign currently stored in? I would manage these as fields in either the Scraper or Source table, depending on whether those settings are scraper or source specific. |