Archive:Database Schema 4.0/a
Revision: 2040h - 29 May 10
This is the version of Database Schema 4.0 before the EAV/CR schema modification. It's being retained for comparison, and because there are concerns this revision has some great EAV/CR abstractions -- such as content and attributes (sets, actors, directors, etc.) -- that the 4.0 revision abstracts too far and essentially builds a database with/within a database, leaving the C++ client to do the work of a DBMS.
Introduction
The following schema is being designed to be both scalable and adaptable to whatever the future can throw at it whilst remaining as stable and portable as possible (ie it has to play nice with mysql too).
The current state of the schema (which we are currently most happiest with) is being released for a community review to ensure any potential "gotchas" are caught and addressed early.
Loosely coupled metadata <-> file/path mappings.
- When a file/path is moved then it should not destroy the metadata associated with it. The metadata should be able to be remapped to the new file/path.
- This mapping is achieved by the contentlinkdirent table whereby metadata (ie. "content" table) is mapped to the file/path (ie. "dirent" table).
- N:N (content:file/path) mapping, is possible with this configuration.
- The map table also supports a parent content ID for allowing hierarchical structures for content. For example metadata for a Tv Show, Season, Episode hierarchy can be supported by this map table.
Arbitrary classification "nodes" of media.
- It should be possible that a user can have as many "Tv Show" nodes as they like, separating out for example "Documentaries" from anything else.
ALSO
- Classification should be expandable to add new values making possible finer grouping/classification of media.
- Classification is achieved by the classification table whereby an official list of classifications already exists but can be expanded by the user.
- The official classifications are "Movies", "Tv Shows", "Music Videos", and "Documentaries".
- The classification table also supports a parent classification ID for allowing heirachial structures. A top level classification will have a parent ID of zero ("0").
- Tracking the parent ID allows the storage of a loose singly linked list. For example below is a simple heirachy and how how it would be stored in the classification table.
Movies <---+-- Home Movies <-+-- Birthdays | +-- Holidays | +-- Mum & Dad's Adventures +-- Tv Movies Tv Shows <-+-- Cartoons +-- Documentaries Music Videos | idClassification | idParent | classification | +------------------+----------+------------------------+ | 1 | 0 | Movies | | 2 | 1 | Home Movies | | 3 | 2 | Birthdays | | 4 | 2 | Christmases | | 5 | 2 | Mum & Dad's Adventures | | 6 | 1 | Tv Movies | | 7 | 0 | Tv Shows | | 8 | 7 | Cartoons | | 9 | 7 | Documentaries | | 10 | 0 | Music Videos |
- 2.7. Content is associated to a particular classification via the mapping table "classificationlinkcontent".
Retrieval of metadata should be conducted in 2 passes
- Critical information for sorting and/or filtering,
- Then additional information as required for display.
Mapping from file/path listings to metadata.
- It should be possible for a user to view a top-level classification node (eg. "Movies") using a filesystem representative view.
Reliable identification when disk content and library content differs.
Arbitrary key/value pairs (or attributes) for additional information that is not stored in the main content metadata should be supported.
- These attributes are achieved by using both the "attributes" and "attributeslinkcontent" tables. The "attributes" table defines arbitrary keys that can be assigned values via the "attributeslinkcontent" tables.
- All values are stored in a string representation regardless of their type.
- Current supported attributes include "Actors", "Directores", "Genres", "Networks" "Studios", and "Writers".
Everything gets added to the library, whether we can find online information or not.
Content specific parameters such as watched, last played, and bookmarks should be stored per-profile.
- These parameters are stored in the "bookmarks" and "settings" tables both of which carry the idProfile attribute for linking to a profile.
- Watched can be extraced from playCount (ie. playCount>0).
Content-content linking enables connections such as:
- The mapping is done by the parent/child link table "contentlinkcontent".
- The largest linkage depth will typically be 3 (e.g. item 9.1. above). - The content linking needs some more brainstorming
Examples of content-content linking are:
- Tv Show > Season/Episode
- Movie > Soundtrack
- Music > Music Video
- Home Movies > Photos
NOTES: The following notes should be considered when viewing the DB schema.
- SQLite treats VARCHAR as TEXT.
- DOUBLE is used where any precision is required since SQLite will store any form of precision (ie. real number) as a 64-bit DOUBLE, so we might as well be consistent.
- Date/Time strings are stored as UNIX time (ie. 64-bit UNSIGNED)
- INTEGER is required for sqlite3 to be recognised as an auto-incremental row and so we default all INT usage to INTEGER which is recognised by both.
Schema Draft
Table: profile
-- ----------------------------------------------------- -- Table: profile -- -- Description: -- The profile table provides a representation of individual profiles. A number -- of settings and user specific configurations can be attributed to individual -- profiles. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS profile ( idProfile INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , name VARCHAR(128) NOT NULL ); INSERT INTO profile (name) VALUES ('masterprofile') ;
Table: classification
-- ----------------------------------------------------- -- Table: classification -- -- Description: -- The classification table provides both the top level and lower level -- classification descriptions that can be applied to content. -- -- A top-level classification is defined as having its parent ID -- (idCParent) set to the value zero ("0"). -- -- A sub-level classification should always have a valid parent ID defined. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS classification ( idClassification INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER NOT NULL DEFAULT 0 , name VARCHAR(128) NOT NULL , localization INTEGER NOT NULL DEFAULT 0 , description TEXT NULL DEFAULT NULL ); INSERT INTO classification (idParent, name, localization) VALUES (0, 'Movies', 20342) ; INSERT INTO classification (idParent, name, localization) VALUES (0, 'Tv Shows', 20343) ; INSERT INTO classification (idParent, name, localization) VALUES (0, 'Music Videos', 20451) ; INSERT INTO classification (idParent, name, localization) VALUES (0, 'Documentaries', 20452) ;
Table: collections
-- ----------------------------------------------------- -- Table: collections -- -- Description: -- A collection is simply a naturally occuring collection of content - albums, -- seasons, shows, artists, rather than an "unnaturally" occuring collection of -- content that has N:N relationships (sets, genre, keywords, actors, directors, -- etc). -- -- A top-level collection is defined as having its parent ID -- (idParent) set to the value zero ("0"). -- -- A sub-level collection should always have a valid parent ID defined. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS collections ( idCollection INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER NOT NULL DEFAULT 0 , name TEXT NULL DEFAULT NULL , description TEXT NULL DEFAULT NULL );
Table: content
-- ----------------------------------------------------- -- Table content -- -- Description: -- The content table provides all content specific metadata that is scrapable. -- -- Due to the data being scraped from the internet and the high state of flux of -- these APIs, the fields cXX are used. -- -- Collection to Content is a 1:N relationship only (i.e. each content type can -- belong to one and only one collection). -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS content ( idContent INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idCollection INTEGER NOT NULL DEFAULT 0 , title TEXT NULL DEFAULT NULL , plot TEXT NULL DEFAULT NULL , plotSummary TEXT NULL DEFAULT NULL , tagline TEXT NULL DEFAULT NULL , onlineRatingVotes TEXT NULL DEFAULT NULL , onlineRating TEXT NULL DEFAULT NULL , released UNSIGNED BIGINT NULL DEFAULT 0 , onlineSource TEXT NULL DEFAULT NULL , onlineID TEXT NULL DEFAULT NULL , runTime TEXT NULL DEFAULT NULL , contentRating TEXT NULL DEFAULT NULL , originalTitle TEXT NULL DEFAULT NULL , thumbnails TEXT NULL DEFAULT NULL , trailerUrl TEXT NULL DEFAULT NULL , fanartUrl TEXT NULL DEFAULT NULL , season INTEGER NULL DEFAULT NULL , episode INTEGER NULL DEFAULT NULL , epgUrl TEXT NULL DEFAULT NULL , dateAdded UNSIGNED BIGINT NULL DEFAULT 0 ); CREATE INDEX ixEpisodeSeason ON content (episode ASC, season ASC) ;
Table: classificationlinkcollection
-- ----------------------------------------------------- -- Table: classificationlinkcollection -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS classificationlinkcollection ( idClassification INTEGER NOT NULL , idCollection INTEGER NOT NULL ); CREATE INDEX ixClassificationCollection ON classificationlinkcollection (idClassification ASC, idCollection ASC) ; CREATE INDEX ixCollectionClassification ON classificationlinkcollection (idCollection ASC, idClassification ASC) ;
Table: classificationlinkcontent
-- ----------------------------------------------------- -- Table: classificationlinkcontent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS classificationlinkcontent ( idClassification INTEGER NOT NULL , idContent INTEGER NOT NULL ); CREATE INDEX ixClassificationContent ON classificationlinkcontent (idClassification ASC, idContent ASC) ; CREATE INDEX ixContentClassification ON classificationlinkcontent (idContent ASC, idClassification ASC) ;
Table: contentlinkcontent
-- ----------------------------------------------------- -- Table: contentlinkcontent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS contentlinkcontent ( idParent INTEGER NOT NULL DEFAULT 0, idChild INTEGER NOT NULL ); CREATE INDEX ixParentChild ON contentlinkcontent (idParent ASC, idChild ASC) ; CREATE INDEX ixChildParent ON contentlinkcontent (idChild ASC, idParent ASC) ;
Table: dirents
-- ----------------------------------------------------- -- Table: dirents -- -- Description: -- The dirents table handles the filesystem representation of a playable item. -- -- Each row represents a directory entry taking the form of either a file or -- folder. A folder in this table thus represents a folder that has a -- single playable item contained within. -- -- The settings attributes takes an XML structure that defines attributes like -- crop position, zoom, etc. Supported attributes are: -- -- <settings> -- <setting id="deinterlace" type="int" value="" /> -- <setting id="viewmode" type="int" value="" /> -- <setting id="zoomamount" type="double" value="" /> -- <setting id="pixelratio" type="double" value="" /> -- <setting id="audiostream" type="int" value="" /> -- <setting id="subtitlestream" type="int" value="" /> -- <setting id="subtitledelay" type="double" value="" /> -- <setting id="subtitleson" type="bool" value="" /> -- <setting id="brightness" type="double" value="" /> -- <setting id="contrast" type="double" value="" /> -- <setting id="gamma" type="double" value="" /> -- <setting id="volumeamplification" type="double" value="" /> -- <setting id="audiodelay" type="double" value="" /> -- <setting id="outputtoallspeakers" type="double" value="" /> -- <setting id="crop" type="bool" value="" /> -- <setting id="cropleft" type="int" value="" /> -- <setting id="cropright" type="int" value="" /> -- <setting id="cropTop" type="int" value="" /> -- <setting id="cropbottom" type="int" value="" /> -- <setting id="sharpness" type="double" value="" /> -- <setting id="noisereduction" type="double" value="" /> -- <setting id="nonlinstretch" type="int" value="" /> -- </settings> -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS dirents ( idDirent INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idPath INTEGER NOT NULL , direntFilename VARCHAR(512) NOT NULL , direntURL TEXT NULL , direntFileHash VARCHAR(32) NULL , direntContentHash VARCHAR(32) NULL , settings TEXT NULL ); CREATE INDEX ixPathFilename ON dirents (idPath ASC, direntFilename ASC) ;
Table: contentlinkdirent
-- ----------------------------------------------------- -- Table: contentlinkdirent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS contentlinkdirent ( idDirent INTEGER NOT NULL , idContent INTEGER NOT NULL ); CREATE INDEX ixContentDirent ON contentlinkdirent (idContent ASC, idDirent ASC) ; CREATE INDEX ixDirentContent ON contentlinkdirent (idDirent ASC, idContent ASC) ;
Table: path
-- ----------------------------------------------------- -- Table: path -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS path ( idPath INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER NOT NULL DEFAULT 0 , idScraper INTEGER NOT NULL DEFAULT 0 , path VARCHAR(512) NULL DEFAULT NULL , hash TEXT NULL DEFAULT NULL , noUpdate TINYINT NULL DEFAULT NULL ); CREATE INDEX ixPath ON path (path ASC) ;
Table: scrapers
-- ----------------------------------------------------- -- Table: scrapers -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS scrapers ( idSraper INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , content TEXT NULL DEFAULT NULL , scraper TEXT NULL DEFAULT NULL , scanRecursive INTEGER NULL DEFAULT NULL , useFolderNames TINYINT NULL DEFAULT NULL , settings TEXT NULL DEFAULT NULL , noUpdate TINYINT NULL DEFAULT NULL );
Table: bookmark
-- ----------------------------------------------------- -- Table: bookmark -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS bookmark ( idBookmark INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idDirent INTEGER NOT NULL , idProfile INTEGER NOT NULL , timeInSeconds DOUBLE NULL DEFAULT NULL , totalTimeInSeconds DOUBLE NULL DEFAULT NULL , thumbnailImage TEXT NULL DEFAULT NULL , player TEXT NULL DEFAULT NULL , playerState TEXT NULL DEFAULT NULL , type INTEGER NULL DEFAULT NULL ); CREATE INDEX ixBookmark ON bookmark (idDirent ASC) ;
Table: settings
-- ----------------------------------------------------- -- Table: settings -- -- Description: -- The settings table stores all associated settings for a content entry per -- profile. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS settings ( idProfile INTEGER NOT NULL , idContent INTEGER NOT NULL , playCount INTEGER NULL DEFAULT NULL , lastPlayed VARCHAR(24) NULL DEFAULT NULL ); CREATE INDEX ixProfileContent ON settings (idProfile ASC, idContent ASC) ; CREATE INDEX ixContentProfile ON settings (idContent ASC, idProfile ASC) ;
Table: stacktimes
-- ----------------------------------------------------- -- Table: stacktimes -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS stacktimes ( idDirent INTEGER NULL DEFAULT NULL , times TEXT NULL DEFAULT NULL ); CREATE INDEX ixStackTimes ON stacktimes (idDirent ASC) ;
Table: streamdetails
-- ----------------------------------------------------- -- Table: streamdetails -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS streamdetails ( idDirent INTEGER NOT NULL , streamType INTEGER NULL DEFAULT NULL , videoCodec TEXT NULL DEFAULT NULL , videoAspect DOUBLE NULL DEFAULT NULL , videoWidth INTEGER NULL DEFAULT NULL , videoHeight INTEGER NULL DEFAULT NULL , audioCodec TEXT NULL DEFAULT NULL , audioChannels INTEGER NULL DEFAULT NULL , audioLanguage TEXT NULL DEFAULT NULL , subtitleLanguage TEXT NULL DEFAULT NULL ); CREATE INDEX ixStreamDetails ON streamdetails (idDirent ASC) ;
Table: thumbs
-- ----------------------------------------------------- -- Table: thumbs -- -- Description: -- The thumbs table stores the raw binary data representing a thumbnail or -- fanart. -- -- The "type" specifier indicates whether it is fanart or thumb -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS thumbs ( idThumb INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, type INTEGER NOT NULL DEFAULT 0, width INTEGER NOT NULL DEFAULT 0, height INTEGER NOT NULL DEFAULT 0, thumb BLOB NULL );
Table: thumbslinkcontent
-- ----------------------------------------------------- -- Table: thumbslinkcontent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS thumbslinkcontent ( idThumb INTEGER NOT NULL , idContent INTEGER NOT NULL ); CREATE INDEX ixThumbContent ON thumbslinkcontent (idThumb ASC, idContent ASC) ; CREATE INDEX ixContentThumb ON thumbslinkcontent (idContent ASC, idThumb ASC) ;
Table: thumbslinkattributes
-- ----------------------------------------------------- -- Table: thumbslinkattributes -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS thumbslinkattributes ( idThumb INTEGER NOT NULL , idAttribute INTEGER NOT NULL ); CREATE INDEX ixThumbAttribute ON thumbslinkattributes (idThumb ASC, idAttribute ASC) ; CREATE INDEX ixAttributeThumb ON thumbslinkattributes (idAttribute ASC, idThumb ASC) ;
Table: attributes
-- ----------------------------------------------------- -- Table: attributes -- -- Description: -- The attributes table lists all available attributes that can be associated -- with "content" metadata. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributes ( idAttribute INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , name VARCHAR(128) NOT NULL, detail TEXT NULL DEFAULT NULL ); INSERT INTO attributes (name) VALUES ('Actors') ; INSERT INTO attributes (name) VALUES ('Directors') ; INSERT INTO attributes (name) VALUES ('Genres') ; INSERT INTO attributes (name) VALUES ('Networks') ; INSERT INTO attributes (name) VALUES ('Studios') ; INSERT INTO attributes (name) VALUES ('Writers') ;
Table: attributeslinkcontent
-- ----------------------------------------------------- -- Table: attributeslinkcontent -- -- Description: -- The attributeslinkcontent table links a attribute/value pair to a particular -- content. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributeslinkcontent ( idAttribute INTEGER NOT NULL , idContent INTEGER NOT NULL , value TEXT NULL DEFAULT NULL ); CREATE INDEX ixAttributeContent ON attributeslinkcontent (idAttribute ASC, idContent ASC) ; CREATE INDEX ixContentAttribute ON attributeslinkcontent (idContent ASC, idAttribute ASC) ;
Table: version
-- ----------------------------------------------------- -- Table: version -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS version ( videoRevision INTEGER NULL DEFAULT NULL , videoCompressCount INTEGER NULL DEFAULT NULL );