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.

Loosely coupled metadata <-> file/path mappings.

 * 1) 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.

 * 1) 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

 * 1) Critical information for sorting and/or filtering,
 * 2) Then additional information as required for display.

Mapping from file/path listings to metadata.

 * 1) It should be possible for a user to view a top-level classification node (eg. "Movies") using a filesystem representative view.

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.

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: -- -- --   --   --   --   --   --   --   --   --   --   --   --   --   --   --   --   --  <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="" /> -- -- -- -- 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 );