Archive:Database Schema 4.0/a: Difference between revisions
m (moved Database Schema 4.0a to Database Schema 4.0/a) |
No edit summary |
||
Line 1: | Line 1: | ||
{{DISPLAYTITLE:Database Schema 4.0a}} | |||
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. | 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. | ||
Revision as of 02:30, 27 November 2011
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 (i.e., it has to play nice with MySQL and other databases, such as PostgreSQL, too).
The current state of the schema (which we are currently 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 | idCollection | 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 collection via content.idCollection (primary) and the mapping table "collectionlinkcontent" (secondary).
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: collection
-- ----------------------------------------------------- -- Table: collection -- -- Description: -- A collection is simply a naturally occuring collection of content - albums, -- seasons, shows, artists. -- -- A top-level collection has a null idParent. -- -- A sub-level collection should always have a valid parent ID defined. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS collection ( idCollection INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER FOREIGN KEY REFERENCES collection ON DELETE SET NULL ON UPDATE RESTRICT name TEXT NULL DEFAULT NULL , localization INTEGER , description TEXT NULL DEFAULT NULL ); INSERT INTO collection (name, localization) VALUES ('Movies', 20342) ; INSERT INTO collection (name, localization) VALUES ('TV Shows', 20343) ; INSERT INTO collection (name, localization) VALUES ('Music Videos', 20451) ; INSERT INTO collection (name, localization) VALUES ('Documentaries', 20452) ;
Table: contentlinkfile
-- ----------------------------------------------------- -- Table contentlinkfile -- -- Description: -- Connects content to one or more (stacked) files. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS contentlinkfile ( idContent INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, idFile INTEGER NOT NULL FOREIGN KEY REFERENCES file ON DELETE CASCADE ON UPDATE RESTRICT, PRIMARY KEY (idContent, idFile) )
Table: content
-- ----------------------------------------------------- -- Table content -- -- Description: -- The content table provides all content specific metadata that is scrapable. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS content ( idContent INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idCollection INTEGER NOT NULL FOREIGN KEY REFERENCES collection ON DELETE RESTRICT ON UPDATE RESTRICT -- primary collection title TEXT NOT NULL , plot TEXT NULL DEFAULT NULL , plotSummary TEXT NULL DEFAULT NULL , tagline TEXT NULL DEFAULT NULL , onlineRatingVotes INTEGER NULL DEFAULT NULL , onlineRating DOUBLE NULL DEFAULT NULL , released UNSIGNED BIGINT NULL DEFAULT NULL , -- full date runTime INTEGER NULL DEFAULT NULL , contentRating VARCHAR(16) NULL DEFAULT NULL , originalTitle TEXT NULL DEFAULT NULL , thumb INTEGER FOREIGN KEY REFERENCES image ON DELETE SET NULL ON UPDATE RESTRICT, -- current thumb fanart INTEGER FOREIGN KEY REFERENCES image ON DELETE SET NULL ON UPDATE RESTRICT, -- current fan art trailerUrl TEXT NULL DEFAULT NULL , onlineSource TEXT NULL DEFAULT NULL , onlineID TEXT NULL DEFAULT NULL , season INTEGER NULL DEFAULT NULL , episode INTEGER NULL DEFAULT NULL , dateAdded UNSIGNED BIGINT NULL DEFAULT 0 ); CREATE INDEX ix_content_episodeSeason ON content (episode, season) ; CREATE INDEX ix_content_title ON content (title)
Table: collectionlinkcontent
-- ----------------------------------------------------- -- Table: collectionlinkcontent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS collectionlinkcontent ( idCollection INTEGER NOT NULL FOREIGN KEY REFERENCES collection ON DELETE CASCADE ON UPDATE RESTRICT, idContent INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT ); CREATE INDEX ix_collectionlinkcontent_1 ON collectionlinkcontent (idCollection ASC, idContent ASC) ; CREATE INDEX ix_collectionlinkcontent_2 ON collectionlinkcontent (idContent ASC, idCollection ASC) ;
Table: contentlinkcontent
-- ----------------------------------------------------- -- Table: contentlinkcontent -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS contentlinkcontent ( idParent INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, idChild INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, PRIMARY KEY (idParent, idChild) ); CREATE INDEX ix_contentlinkcontent_2 ON contentlinkcontent (idChild ASC, idParent ASC) ;
Table: file
-- ----------------------------------------------------- -- Table: file -- -- Description: -- The file 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. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS file ( idFile INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idPath INTEGER NOT NULL FOREIGN KEY REFERENCES path ON DELETE CASCADE ON UPDATE RESTRICT, filename VARCHAR(512) NOT NULL, url TEXT, nameHash VARCHAR(64), contentHash VARCHAR(64), ); CREATE UNIQUE INDEX ix_file_PathFilename ON file (idPath ASC, filename ASC) ; CREATE INDEX ix_file_contentHash ON file (contentHash)
Table: filesettings
-- ----------------------------------------------------- -- Table: filesettings -- -- Description: -- The file table handles the media settings for a file. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS filesettings ( idFile INTEGER PRIMARY KEY FOREIGN KEY REFERENCES file ON DELETE CASCADE ON UPDATE RESTRICT, deinterlace INTEGER, viewmode INTEGER, zoomamount DOUBLE, pixelratio DOUBLE, subtitlestream INTEGER, subtitledelay INTEGER, subtitleson TINYINT, brightness DOUBLE, contrast DOUBLE, gamma DOUBLE, volumeamplification DOUBLE, audiodelay DOUBLE, outputtoallspeakers DOUBLE, crop TINYINT, cropleft INTEGER, cropright INTEGER, croptop INTEGER, cropbottom INTEGER, sharpness DOUBLE, noisereduction DOUBLE, nonlinstretch INTEGER );
Table: path
-- ----------------------------------------------------- -- Table: path -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS path ( idPath INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER FOREIGN KEY REFERENCES path ON DELETE CASCADE ON UPDATE RESTRICT, idScraper INTEGER , -- TODO: allow multiple scrapers path VARCHAR(512) NULL DEFAULT NULL , hash TEXT NULL DEFAULT NULL , noUpdate TINYINT NOT NULL DEFAULT 0 ); CREATE INDEX ix_path ON path (path ASC) ;
Table: scraper
-- ----------------------------------------------------- -- Table: scraper -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS scraper ( idScraper INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , name TEXT NOT NULL , description TEXT NOT NULL , scanRecursive TINYINT NOT NULL DEFAULT 0 , useFolderNames TINYINT NOT NULL DEFAULT 0 , settings TEXT , noUpdate TINYINT NOT NULL DEFAULT 0 , );
Table: bookmark
-- ----------------------------------------------------- -- Table: bookmark -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS bookmark ( idContent INTEGER NOT NULL REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, idProfile INTEGER NOT NULL REFERENCES profile ON DELETE CASCADE ON UPDATE RESTRICT, 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 , PRIMARY KEY ( idCOntent, idProfile ) );
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 PRIMARY KEY (idProfile, idContent) ); CREATE INDEX ix_settings_ContentProfile ON settings (idContent, idProfile) ;
Table: stacktimes
-- ----------------------------------------------------- -- Table: stacktimes -- TODO: what's this table for? file stacking? -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS stacktimes ( idFile INTEGER PRIMARY KEY FOREIGN KEY REFERENCES file, times TEXT NOT NULL );
Table: streamdetails
-- ----------------------------------------------------- -- Table: streamdetails -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS streamdetails ( idFile INTEGER NOT NULL FOREIGN KEY REFERENCES file ON DELETE CASCADE ON UPDATE RESTRICT, streamType INTEGER NOT 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 , PRIMARY KEY (idFile, streamType) );
Table: image
-- ----------------------------------------------------- -- Table: image -- -- Description: -- The thumbs table stores a link or the raw binary data -- representing a thumbnail or fanart. -- -- The "type" specifier indicates whether it is fanart (0) or thumb (1) -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS image ( idThumb INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idContent INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, type INTEGER NOT NULL, width INTEGER, height INTEGER, url TEXT, thumb BLOB -- cached image content );
Table: attribute
-- ----------------------------------------------------- -- Table: attribute -- -- Description: -- The attributes table lists all available attributes that can be associated -- with "content" metadata. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attribute ( idAttribute INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , name VARCHAR(128) NOT NULL, detail TEXT NULL DEFAULT NULL ); INSERT INTO attribute (name) VALUES ('Actors') ; INSERT INTO attribute (name) VALUES ('Directors') ; INSERT INTO attribute (name) VALUES ('Genres') ; INSERT INTO attribute (name) VALUES ('Networks') ; INSERT INTO attribute (name) VALUES ('Studios') ; INSERT INTO attribute (name) VALUES ('Writers') ;
Table: attributevalue
-- ----------------------------------------------------- -- Table: attributevalue -- -- Description: -- Possible attribute values that can be associated with content. -- E.g., genre 'horror', or writer 'John Grisham' -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributevalue ( idValue INTEGER PRIMARY KEY AUTO INCREMENT, idAttribute INTEGER NOT NULL FOREIGN KEY REFERENCES attribute ON DELETE CASCADE ON UPDATE RESTRICT, name VARCHAR(128) NOT NULL, detail TEXT NULL DEFAULT NULL );
Table: attributelinkcontent
-- ----------------------------------------------------- -- Table: attributelinkcontent -- -- Description: -- Links attributevalue data (e.g., an actor) to content. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributeslinkcontent ( idContent INTEGER NOT NULL FOREIGN KEY REFERENCES content ON DELETE CASCADE ON UPDATE RESTRICT, idValue INTEGER NOT NULL FOREIGN KEY REFERENCES attributevalue ON DELETE CASCADE ON UPDATE RESTRICT, link TEXT NULL DEFAULT NULL, -- used for actor roles PRIMARY KEY (idContent, idValue, link) -- an actor can have multiple roles in a production );
Table: version
-- ----------------------------------------------------- -- Table: version -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS version ( videoRevision INTEGER NOT NULL, videoCompressCount INTEGER NOT NULL DEFAULT 0 );