Archive:Database Schema 4.0/a: Difference between revisions
>Dbrobins (Revive historical version of the schema for comparison) |
>Dbrobins (Eliminated "classification" ("collection" suffices). Rename "dirent" to "file". Add foreign keys where appropriate.) |
||
Line 1: | Line 1: | ||
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. | ||
== Introduction == | == 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 ( | 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 | 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. | ||
Line 42: | Line 40: | ||
| | | idCollection | idParent | classification | | ||
+ | +--------------+----------+------------------------+ | ||
| 1 | | 1 | 0 | Movies | | ||
| 2 | | 2 | 1 | Home Movies | | ||
| 3 | | 3 | 2 | Birthdays | | ||
| 4 | | 4 | 2 | Christmases | | ||
| 5 | | 5 | 2 | Mum & Dad's Adventures | | ||
| 6 | | 6 | 1 | Tv Movies | | ||
| 7 | | 7 | 0 | Tv Shows | | ||
| 8 | | 8 | 7 | Cartoons | | ||
| 9 | | 9 | 7 | Documentaries | | ||
| 10 | | 10 | 0 | Music Videos | | ||
</pre> | </pre> | ||
* 2.7. Content is associated to a particular | * 2.7. Content is associated to a particular collection via content.idCollection (primary) and the mapping table "collectionlinkcontent" (secondary). | ||
Line 131: | Line 129: | ||
=== Table: | === Table: collection === | ||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: collection | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- | -- A collection is simply a naturally occuring collection of content - albums, | ||
-- | -- seasons, shows, artists. | ||
-- | -- | ||
-- A top-level | -- A top-level collection has a null idParent. | ||
-- | -- | ||
-- A sub-level | -- A sub-level collection should always have a valid parent ID defined. | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS collection ( | ||
idCollection INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , | |||
idParent | idParent INTEGER REFERENCES collection | ||
name | name TEXT NULL DEFAULT NULL , | ||
localization | localization INTEGER , | ||
description | description TEXT NULL DEFAULT NULL | ||
); | ); | ||
INSERT INTO | INSERT INTO collection (name, localization) VALUES ('Movies', 20342) ; | ||
INSERT INTO | INSERT INTO collection (name, localization) VALUES ('TV Shows', 20343) ; | ||
INSERT INTO | INSERT INTO collection (name, localization) VALUES ('Music Videos', 20451) ; | ||
INSERT INTO | INSERT INTO collection (name, localization) VALUES ('Documentaries', 20452) ; | ||
</pre> | </pre> | ||
=== Table: contentlinkfile === | |||
=== Table: | |||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table | -- Table contentlinkfile | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- | -- Connects content to one or more (stacked) files. | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS contentlinkfile ( | ||
idContent INTEGER NOT NULL REFERENCES content, | |||
idFile INTEGER NOT NULL REFERENCES file, | |||
PRIMARY KEY (idContent, idFile) | |||
) | |||
) | |||
</pre> | </pre> | ||
Line 196: | Line 182: | ||
-- Description: | -- Description: | ||
-- The content table provides all content specific metadata that is scrapable. | -- The content table provides all content specific metadata that is scrapable. | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS content ( | CREATE TABLE IF NOT EXISTS content ( | ||
idContent INTEGER NOT NULL PRIMARY KEY | idContent INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, | ||
idCollection INTEGER NOT NULL | idCollection INTEGER NOT NULL REFERENCES collection -- primary collection | ||
title TEXT | title TEXT NOT NULL , | ||
plot TEXT NULL DEFAULT NULL , | plot TEXT NULL DEFAULT NULL , | ||
plotSummary TEXT NULL DEFAULT NULL , | plotSummary TEXT NULL DEFAULT NULL , | ||
tagline TEXT NULL DEFAULT NULL , | tagline TEXT NULL DEFAULT NULL , | ||
onlineRatingVotes | onlineRatingVotes INTEGER NULL DEFAULT NULL , | ||
onlineRating | onlineRating DOUBLE NULL DEFAULT NULL , | ||
released UNSIGNED BIGINT | released UNSIGNED BIGINT NULL DEFAULT NULL , -- full date | ||
runTime INTEGER NULL DEFAULT NULL , | |||
contentRating VARCHAR(16) NULL DEFAULT NULL , | |||
runTime | |||
contentRating | |||
originalTitle TEXT NULL DEFAULT NULL , | originalTitle TEXT NULL DEFAULT NULL , | ||
thumb INTEGER REFERENCES image , -- current thumb | |||
fanart INTEGER REFERENCES image , -- current fan art | |||
trailerUrl TEXT NULL DEFAULT NULL , | trailerUrl TEXT NULL DEFAULT NULL , | ||
onlineSource TEXT NULL DEFAULT NULL , | |||
onlineID TEXT NULL DEFAULT NULL , | |||
season INTEGER NULL DEFAULT NULL , | season INTEGER NULL DEFAULT NULL , | ||
episode INTEGER NULL DEFAULT NULL , | episode INTEGER NULL DEFAULT NULL , | ||
dateAdded UNSIGNED BIGINT NULL DEFAULT 0 | dateAdded UNSIGNED BIGINT NULL DEFAULT 0 | ||
); | ); | ||
CREATE INDEX | CREATE INDEX ix_content_episodeSeason ON content (episode, season) ; | ||
CREATE INDEX ix_content_title ON content (title) | |||
</pre> | </pre> | ||
=== Table: | === Table: collectionlinkcontent === | ||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: collectionlinkcontent | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS collectionlinkcontent ( | ||
idCollection INTEGER NOT NULL REFERENCES collection, | |||
idContent INTEGER NOT NULL REFERENCES content | |||
); | ); | ||
CREATE INDEX | CREATE INDEX ix_collectionlinkcontent_1 ON collectionlinkcontent (idCollection ASC, idContent ASC) ; | ||
CREATE INDEX ix_collectionlinkcontent_2 ON collectionlinkcontent (idContent ASC, idCollection ASC) ; | |||
CREATE INDEX | |||
</pre> | </pre> | ||
Line 272: | Line 237: | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS contentlinkcontent ( | CREATE TABLE IF NOT EXISTS contentlinkcontent ( | ||
idParent INTEGER NOT NULL | idParent INTEGER NOT NULL REFERENCES content, | ||
idChild INTEGER NOT NULL | idChild INTEGER NOT NULL REFERENCES content | ||
); | ); | ||
CREATE INDEX | CREATE INDEX ix_contentlinkcontent_1 ON contentlinkcontent (idParent ASC, idChild ASC) ; | ||
CREATE INDEX | CREATE INDEX ix_contentlinkcontent_2 ON contentlinkcontent (idChild ASC, idParent ASC) ; | ||
</pre> | </pre> | ||
=== Table: | === Table: file === | ||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: file | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- The | -- 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 | -- 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 | -- folder. A folder in this table thus represents a folder that has a | ||
-- single playable item contained within. | -- single playable item contained within. | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS file ( | ||
idFile INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, | |||
idPath INTEGER NOT NULL , | idPath INTEGER NOT NULL REFERENCES path, | ||
filename VARCHAR(512) NOT NULL, | |||
url TEXT, | |||
nameHash VARCHAR(64), | |||
contentHash VARCHAR(64), | |||
idNext INTEGER REFERENCES file -- next file in stacked collection? | |||
); | ); | ||
CREATE INDEX | CREATE UNIQUE INDEX ix_file_PathFilename ON file (idPath ASC, filename ASC) ; | ||
CREATE INDEX ix_file_contentHash ON file (contentHash) | |||
</pre> | </pre> | ||
=== Table: | |||
=== Table: filesettings === | |||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: file | ||
-- | |||
-- Description: | |||
-- The file table handles the media settings for a file. | |||
-- | |||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS file ( | ||
idFile INTEGER PRIMARY KEY REFERENCES file, | |||
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 | |||
); | ); | ||
</pre> | </pre> | ||
Line 359: | Line 318: | ||
CREATE TABLE IF NOT EXISTS path ( | CREATE TABLE IF NOT EXISTS path ( | ||
idPath INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , | idPath INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , | ||
idParent INTEGER | idParent INTEGER REFERENCES path, | ||
idScraper INTEGER | idScraper INTEGER , -- TODO: allow multiple scrapers | ||
path VARCHAR(512) NULL DEFAULT NULL , | path VARCHAR(512) NULL DEFAULT NULL , | ||
hash TEXT NULL DEFAULT NULL , | hash TEXT NULL DEFAULT NULL , | ||
noUpdate TINYINT NULL | noUpdate TINYINT NOT NULL DEFAULT 0 | ||
); | ); | ||
CREATE INDEX | CREATE INDEX ix_path ON path (path ASC) ; | ||
</pre> | </pre> | ||
=== Table: | === Table: scraper === | ||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: scraper | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS scraper ( | ||
idScraper INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , | |||
name TEXT NOT NULL , | |||
description TEXT NOT NULL , | |||
scanRecursive | scanRecursive TINYINT NOT NULL DEFAULT 0 , | ||
useFolderNames TINYINT NULL | useFolderNames TINYINT NOT NULL DEFAULT 0 , | ||
settings TEXT | settings TEXT , | ||
noUpdate TINYINT NULL | noUpdate TINYINT NOT NULL DEFAULT 0 , | ||
); | ); | ||
</pre> | </pre> | ||
Line 395: | Line 354: | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS bookmark ( | CREATE TABLE IF NOT EXISTS bookmark ( | ||
idContent INTEGER NOT NULL REFERENCES content, | |||
idProfile INTEGER NOT NULL REFERENCES profile, | |||
idProfile INTEGER NOT NULL , | |||
timeInSeconds DOUBLE NULL DEFAULT NULL , | timeInSeconds DOUBLE NULL DEFAULT NULL , | ||
totalTimeInSeconds DOUBLE NULL DEFAULT NULL , | totalTimeInSeconds DOUBLE NULL DEFAULT NULL , | ||
Line 403: | Line 361: | ||
player TEXT NULL DEFAULT NULL , | player TEXT NULL DEFAULT NULL , | ||
playerState TEXT NULL DEFAULT NULL , | playerState TEXT NULL DEFAULT NULL , | ||
PRIMARY KEY ( idCOntent, idProfile ) | |||
); | ); | ||
</pre> | </pre> | ||
Line 427: | Line 383: | ||
playCount INTEGER NULL DEFAULT NULL , | playCount INTEGER NULL DEFAULT NULL , | ||
lastPlayed VARCHAR(24) NULL DEFAULT NULL | lastPlayed VARCHAR(24) NULL DEFAULT NULL | ||
PRIMARY KEY (idProfile, idContent) | |||
); | ); | ||
CREATE INDEX | CREATE INDEX ix_settings_ContentProfile ON settings (idContent, idProfile) ; | ||
</pre> | </pre> | ||
Line 439: | Line 396: | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: stacktimes | -- Table: stacktimes | ||
-- TODO: what's this table for? file stacking? | |||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS stacktimes ( | CREATE TABLE IF NOT EXISTS stacktimes ( | ||
idFile INTEGER PRIMARY KEY REFERENCES file, | |||
times TEXT | times TEXT NOT NULL | ||
); | ); | ||
</pre> | </pre> | ||
Line 456: | Line 413: | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS streamdetails ( | CREATE TABLE IF NOT EXISTS streamdetails ( | ||
idFile INTEGER NOT NULL REFERENCES file ON DELETE CASCADE ON UPDATE RESTRICT, | |||
streamType INTEGER | streamType INTEGER NOT NULL, | ||
videoCodec TEXT NULL DEFAULT NULL , | videoCodec TEXT NULL DEFAULT NULL , | ||
videoAspect DOUBLE NULL DEFAULT NULL , | videoAspect DOUBLE NULL DEFAULT NULL , | ||
Line 465: | Line 422: | ||
audioChannels INTEGER NULL DEFAULT NULL , | audioChannels INTEGER NULL DEFAULT NULL , | ||
audioLanguage TEXT NULL DEFAULT NULL , | audioLanguage TEXT NULL DEFAULT NULL , | ||
subtitleLanguage TEXT NULL DEFAULT NULL | subtitleLanguage TEXT NULL DEFAULT NULL , | ||
PRIMARY KEY (idFile, streamType) | |||
); | ); | ||
</pre> | </pre> | ||
=== Table: | === Table: image === | ||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: image | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- The thumbs table stores the raw binary data representing a thumbnail or | -- The thumbs table stores a link or the raw binary data | ||
-- representing a thumbnail or fanart. | |||
-- | -- | ||
-- The "type" specifier indicates whether it is fanart or thumb | -- The "type" specifier indicates whether it is fanart (0) or thumb (1) | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS image ( | ||
idThumb INTEGER | idThumb INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, | ||
type INTEGER | idContent INTEGER NOT NULL REFERENCES content | ||
width INTEGER | type INTEGER NOT NULL, | ||
height INTEGER | width INTEGER, | ||
thumb BLOB | height INTEGER, | ||
url TEXT, | |||
thumb BLOB -- cached image content | |||
); | ); | ||
</pre> | </pre> | ||
=== Table: attribute === | |||
=== Table: | |||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: attribute | ||
-- | |||
-- Description: | |||
-- The attributes table lists all available attributes that can be associated | |||
-- with "content" metadata. | |||
-- | |||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | 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') ; | |||
</pre> | </pre> | ||
=== Table: attributevalue === | |||
=== Table: | |||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: attributevalue | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- | -- Possible attribute values that can be associated with content. | ||
-- | -- E.g., genre 'horror', or writer 'John Grisham' | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS attributevalue ( | ||
idAttribute INTEGER NOT NULL | idValue INTEGER PRIMARY KEY AUTO INCREMENT, | ||
idAttribute INTEGER NOT NULL REFERENCES attribute, | |||
name VARCHAR(128) NOT NULL, | name VARCHAR(128) NOT NULL, | ||
detail TEXT NULL DEFAULT NULL | detail TEXT NULL DEFAULT NULL | ||
); | ); | ||
</pre> | </pre> | ||
=== Table: attributelinkcontent === | |||
=== Table: | |||
<pre> | <pre> | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
-- Table: | -- Table: attributelinkcontent | ||
-- | -- | ||
-- Description: | -- Description: | ||
-- | -- Links attributevalue data (e.g., an actor) to content. | ||
-- | -- | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS attributeslinkcontent ( | CREATE TABLE IF NOT EXISTS attributeslinkcontent ( | ||
idContent INTEGER NOT NULL REFERENCES content, | |||
idValue INTEGER NOT NULL REFERENCES attributevalue, | |||
link TEXT NULL DEFAULT NULL, -- used for actor roles | |||
PRIMARY KEY (idContent, idValue, link) -- an actor can have multiple roles in a production | |||
); | ); | ||
</pre> | </pre> | ||
Line 580: | Line 522: | ||
-- ----------------------------------------------------- | -- ----------------------------------------------------- | ||
CREATE TABLE IF NOT EXISTS version ( | CREATE TABLE IF NOT EXISTS version ( | ||
videoRevision INTEGER | videoRevision INTEGER NOT NULL, | ||
videoCompressCount INTEGER NULL | videoCompressCount INTEGER NOT NULL DEFAULT 0 | ||
); | ); | ||
</pre> | </pre> |
Revision as of 18:52, 24 September 2010
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 REFERENCES collection 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 REFERENCES content, idFile INTEGER NOT NULL REFERENCES file, 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 REFERENCES collection -- 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 REFERENCES image , -- current thumb fanart INTEGER REFERENCES image , -- 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 REFERENCES collection, idContent INTEGER NOT NULL REFERENCES content ); 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 REFERENCES content, idChild INTEGER NOT NULL REFERENCES content ); CREATE INDEX ix_contentlinkcontent_1 ON contentlinkcontent (idParent ASC, idChild ASC) ; 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 REFERENCES path, filename VARCHAR(512) NOT NULL, url TEXT, nameHash VARCHAR(64), contentHash VARCHAR(64), idNext INTEGER REFERENCES file -- next file in stacked collection? ); CREATE UNIQUE INDEX ix_file_PathFilename ON file (idPath ASC, filename ASC) ; CREATE INDEX ix_file_contentHash ON file (contentHash)
Table: filesettings
-- ----------------------------------------------------- -- Table: file -- -- Description: -- The file table handles the media settings for a file. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS file ( idFile INTEGER PRIMARY KEY REFERENCES file, 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 REFERENCES path, 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, idProfile INTEGER NOT NULL REFERENCES profile, 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 REFERENCES file, times TEXT NOT NULL );
Table: streamdetails
-- ----------------------------------------------------- -- Table: streamdetails -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS streamdetails ( idFile INTEGER NOT NULL 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 REFERENCES content 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 REFERENCES attribute, 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 REFERENCES content, idValue INTEGER NOT NULL REFERENCES attributevalue, 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 );