Archive:Database Schema 4.0
Revision: 1400h - 25 Sep 10
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.
Objectives
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.
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.
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.
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.
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 should be stored as UNIX-equivalent time
- 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.
General Discussion
The current proposed schema is hybrid Entity-Attribute-Value / Class Relationship (EAV/CR) model. Think OO in the DB world. All content metadata we can expressed in an OO-like way utilising only 6 tables.
Classes and Relationship
The class and relationships currently defined are as follows:
class: object attributes: - title - filename relations none class: content : object attributes: - released - filename - rating - title relations none class: video : content : object attributes: - rating - onlineid - title - summary - onlineratingvotes - contentrating - filename - released - onlinerating - runtime relations none class: movie : video : content : object attributes: - rating - onlineid - runtime - outline - title - tagline - plot - onlineratingvotes - contentrating - summary - released - onlinerating - filename relations > movie_has_genre > movie_has_actor > movie_has_studio class: tvshow : video : content : object attributes: - rating - onlineid - runtime - title - plot - onlineratingvotes - contentrating - summary - released - onlinerating - filename relations > tvshow_has_studio > tvshow_has_actor > tvshow_has_genre class: episode : video : content : object attributes: - rating - onlineid - runtime - episode - title - onlineratingvotes - contentrating - summary - released - onlinerating - filename relations > episode_has_actor class: song : content : object attributes: - released - title - rating - filename relations none class: picture : object attributes: - height - width - filename - title relations none class: banner : picture : object attributes: - width - filename - title - height relations none class: cover : picture : object attributes: - width - filename - title - height relations none class: thumb : picture : object attributes: - width - filename - thumb - title - height relations none class: fanart : picture : object attributes: - width - filename - title - height relations none class: photo : picture : object attributes: - width - filename - title - height relations none class: organisation attributes: - name relations none class: person : organisation attributes: - name relations none class: actor : person : organisation attributes: - role - name relations none class: director : person : organisation attributes: - name relations none class: band : organisation attributes: - name relations > band_has_song class: studio : organisation attributes: - name relations none class: grouping attributes: none relations none class: genre : grouping attributes: - name relations none class: directory : grouping attributes: - path relations > directory_has_directory > directory_has_content class: movieset : grouping attributes: none relations > movieset_has_movie class: season : grouping attributes: - season relations > season_has_episode class: album : grouping attributes: none relations > album_has_studio > album_has_song class: playlist : grouping attributes: none relations none
You can see the inheritance at work here and the versatility provided by the EAV/CR structure.
The "hybrid" comes from the fact that there are still some legacy tables remaining that I haven't yet determined if they would be suitable in the EAV/CR portion and perhaps would be better suited at remaining a dedicated tables. The latest schema has been able to reduce all the link tables down to one - objectlinkdirent. This link table is the primary link between the metadata and the playable/viewable item.
Schema Draft
Table: objectTypes
-- ----------------------------------------------------- -- Table: objectTypes -- -- Description: -- Stores the name and ancestory of a particular object. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS objectTypes ( idObjectType INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idParentObjectType INTEGER NOT NULL DEFAULT 0 , stub VARCHAR(128) NOT NULL , name TEXT );
Table: attributeTypes
-- ----------------------------------------------------- -- Table: attributeTypes -- -- Description: -- Stores the name, type and inheritablity of a particular object type's -- attributes. -- -- The dataType maps to a specific data type: -- 0 = String -- 1 = Number -- 2 = Blob -- -- NOTE: dataPrecision would feasibly allow floats to be storable as an -- integer at the cost of conversion, though would save an -- additional field in the attributes table. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributeTypes ( idAttributeType INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idObjectType INTEGER NOT NULL , stub VARCHAR(128) NOT NULL , name TEXT NOT NULL , dataType INTEGER NOT NULL DEFAULT 0 , dataPrecision INTEGER NOT NULL DEFAULT 0 , inheritable INTEGER NOT NULL DEFAULT 0 , FOREIGN KEY (idObjectType) REFERENCES objectTypes (idObjectType) );
Table: relationshipTypes
-- ----------------------------------------------------- -- Table: relationshipTypes -- -- Description: -- Stores the inter-object relationships and if they're sequenced. Also -- determines if the relationship is inherited on either object type. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS relationshipTypes ( idRelationshipType INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , stub VARCHAR(128) NOT NULL , idObjectType1 INTEGER NOT NULL , idObjectType2 INTEGER NOT NULL , inheritableType1 INTEGER NOT NULL DEFAULT 0, inheritableType2 INTEGER NOT NULL DEFAULT 0, sequenced INTEGER NOT NULL DEFAULT 0 , FOREIGN KEY (idObjectType1) REFERENCES objectTypes(idObjectType) , FOREIGN KEY (idObjectType2) REFERENCES objectTypes(idObjectType) );
Table: objects
-- ----------------------------------------------------- -- Table: objects -- -- Description: -- Stores the name and ancestory of a particular object. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS objects ( idObject INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idObjectType INTEGER NOT NULL , stub VARCHAR(128) NOT NULL , name TEXT NOT NULL , FOREIGN KEY (idObjectType) REFERENCES objectTypes(idObjectType) );
Table: attributes
-- ----------------------------------------------------- -- Table: attributes -- -- Description: -- Stores the attribute and attribute value specific to a particular -- object. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS attributes ( idAttribute INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idObject INTEGER NOT NULL , idAttributeType INTEGER NOT NULL , valueString TEXT NULL DEFAULT "" , valueNumber INTEGER NULL DEFAULT 0 , valueBlob BLOB NULL , FOREIGN KEY (idObject) REFERENCES objects(idObject) , FOREIGN KEY (idAttributeType) REFERENCES attributeTypes(idAttributeType) );
Table: relationships
-- ----------------------------------------------------- -- Table: relationships -- -- Description: -- Stores the relationship and sequence (as appropriate) between two -- objects. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS relationships ( idRelationship INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idObject1 INTEGER NOT NULL , idObject2 INTEGER NOT NULL , idRelationshipType INTEGER NOT NULL , sequenceIndex INTEGER NOT NULL DEFAULT 0 , FOREIGN KEY (idObject1) REFERENCES objects(idObject) , FOREIGN KEY (idObject2) REFERENCES objects(idObject) , FOREIGN KEY (idRelationshipType) REFERENCES relationshipTypes(idRelationshipType) );
View: relationships
-- ----------------------------------------------------- -- View: relationships -- -- Description: -- A generic view of all relationships between objects types. -- -- ----------------------------------------------------- CREATE VIEW viewRelationshipsAll AS SELECT o1.idObject AS o1ID, o1.stub AS o1Stub, IFNULL(o1.name, o1.stub) AS o1Name, o1.idObjectType AS o1TypeID, ot1.name AS o1TypeName , o2.idObject AS o2ID, o2.stub AS o2Stub, IFNULL(o2.name, o2.stub) AS o2Name, o2.idObjectType AS o2TypeID, ot2.name AS o2TypeName, rt.stub AS rtName, r.sequenceIndex AS seqIndex FROM objects o1 INNER JOIN objectTypes ot1 ON o1.idObjectType=ot1.idObjectType INNER JOIN relationships r ON o1.idObject=r.idObject1 INNER JOIN objects o2 ON o2.idObject=r.idObject2 INNER JOIN objectTypes ot2 ON o2.idObjectType=ot2.idObjectType INNER JOIN relationshipTypes rt ON r.idRelationshipType=rt.idRelationshipType;
Table: queries
-- ----------------------------------------------------- -- Table: queries -- -- Description: -- Stores the specific queries used to generate dynamic views and the -- like. -- -- This will faciliate an experienced user to optimise these views -- -- NOTE: I have not a clear idea of how this table would be used -- directly but include because it's in my head at the moment. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS queries ( idQuery INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , stub TEXT NOT NULL , locked INTEGER NOT NULL DEFAULT 0 , query TEXT NOT NULL );
Table: dirents
-- ----------------------------------------------------- -- Table: dirents -- -- Description: -- Stores 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="" /> -- <stream id="type" type="int" value="" /> -- <stream id="videoCodec" type="string" value="" /> -- <stream id="videoAspect" type="double" value="" /> -- <stream id="videoWidth" type="int" value="" /> -- <stream id="videoHeight" type="int" value="" /> -- <stream id="audioCodec" type="int" value="" /> -- <stream id="audioChannels" type="string" value="" /> -- <stream id="audioLanguage" type="int" value="" /> -- <stream id="audioLanguage" type="string" value="" /> -- <stream id="subtitleLanguage" type="string" value="" /> -- </settings> -- -- -- NOTE: Combining the aforementioned fields into a single XML based -- field will remove the remove the need for the old "streams -- details" table as well as reduced the sparseness of the tables. -- -- I don't believe there are any current lookups (nor think there -- ever would be) on these values. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS dirents ( idDirent INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, idPath INTEGER NOT NULL , filename TEXT NOT NULL , url TEXT NULL , fileLength INTEGER NOT NULL DEFAULT 0 , fileHash VARCHAR(32) NULL , contentHash VARCHAR(32) NULL , settings TEXT NULL , FOREIGN KEY (idPath) REFERENCES paths(idPath) ); CREATE INDEX ixDPath ON dirents(idPath); CREATE INDEX ixDPathFilename ON dirents (idPath ASC, filename(255) ASC);
Table: objectlinkdirent
-- ----------------------------------------------------- -- Table: objectlinkdirent -- -- Description: -- Stores the primary link between an objects metadata and the -- playable/viewable item. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS objectlinkdirent ( idDirent INTEGER NOT NULL , idObject INTEGER NOT NULL , FOREIGN KEY (idDirent) REFERENCES dirents(idDirent) , FOREIGN KEY (idObject) REFERENCES objects(idObject) ); CREATE INDEX ixOLDDirent ON objectlinkdirent(idDirent); CREATE INDEX ixOLDObject ON objectlinkdirent(idObject);
Table: paths
-- ----------------------------------------------------- -- Table: path -- -- Description: -- Stores the source paths and any recursed directory paths during scraping. -- -- NOTE: -- All added source paths (ie non recursed paths) should have a scraper -- id. The scraper id may better replaced by a link table to facility -- multi-scraper support? -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS paths ( idPath INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , idParent INTEGER NOT NULL DEFAULT 0 , idScraper INTEGER NOT NULL DEFAULT 0 , path TEXT NULL DEFAULT NULL , hash VARCHAR(32) NULL DEFAULT NULL , noUpdate INTEGER NULL DEFAULT NULL , FOREIGN KEY (idScraper) REFERENCES scrapers(idScraper) ); CREATE INDEX ixPScraper ON paths(idScraper);
Table: scrapers
-- ----------------------------------------------------- -- Table: scrapers -- -- Description: -- Stores the scraper information which an added source path has been -- attributed with. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS scrapers ( idScraper INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , content TEXT NULL DEFAULT NULL , scraper TEXT NULL DEFAULT NULL , scanRecursive INTEGER NULL DEFAULT NULL , useFolderNames INTEGER NULL DEFAULT NULL , settings TEXT NULL DEFAULT NULL , noUpdate INTEGER NULL DEFAULT NULL );
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 TEXT NOT NULL ); INSERT INTO profile (name) VALUES ('masterprofile');
Table: bookmark
-- ----------------------------------------------------- -- Table: bookmark -- -- Description: -- Stores bookmarks associated with a playable item. -- -- ----------------------------------------------------- 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 , FOREIGN KEY (idDirent) REFERENCES dirents(idDirent) , FOREIGN KEY (idProfile) REFERENCES profiles(idProfile) ); CREATE INDEX ixBDirent ON bookmark(idDirent); CREATE INDEX ixBProfile ON bookmark(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 , idObject INTEGER NOT NULL , playCount INTEGER NULL DEFAULT NULL , lastPlayed VARCHAR(24) NULL DEFAULT NULL ); CREATE INDEX ixSProfileContent ON settings (idProfile ASC, idContent ASC); CREATE INDEX ixSContentProfile ON settings (idContent ASC, idProfile ASC);
Table: stacktimes
-- ----------------------------------------------------- -- Table: stacktimes -- -- Description: -- Stores how multiple files in a stack are stitched together. -- -- NOTE: -- Should be storing "times" in its native format (eg. integer or real) -- to reduce the number of conversions? -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS stacktimes ( idDirent INTEGER NULL DEFAULT NULL , times TEXT NULL DEFAULT NULL , FOREIGN KEY (idDirent) REFERENCES dirents(idDirent) ); CREATE INDEX ixSTDirent ON stacktimes (idDirent ASC);
Table: version
-- ----------------------------------------------------- -- Table: version -- -- Description: -- Stores the current version of the DB and the number of times it has -- been compressed (sqlite3) only. -- -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS version ( contentRevision INTEGER NULL DEFAULT NULL , contentCompressCount INTEGER NULL DEFAULT NULL );