Archive:Database Schema 4.0/a

This is the version of Archive: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.

Below you can find a diagram of the Database Scheme (mostly based on 4.0a)

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.
2. This mapping is achieved by the contentlinkdirent table whereby metadata (ie. "content" table) is mapped to the file/path (ie. "dirent" table).
3. N:N (content:file/path) mapping, is possible with this configuration.
4. 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.

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.

ALSO

1. Classification should be expandable to add new values making possible finer grouping/classification of media.
2. Classification is achieved by the classification table whereby an official list of classifications already exists but can be expanded by the user.
3. The official classifications are "Movies", "Tv Shows", "Music Videos", and "Documentaries".
4. 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").
5. 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

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.

Arbitrary key/value pairs (or attributes) for additional information that is not stored in the main content metadata should be supported.

1. 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.
2. All values are stored in a string representation regardless of their type.
3. Current supported attributes include "Actors", "Directores", "Genres", "Networks" "Studios", and "Writers".

Content specific parameters such as watched, last played, and bookmarks should be stored per-profile.

1. These parameters are stored in the "bookmarks" and "settings" tables both of which carry the idProfile attribute for linking to a profile.
2. Watched can be extraced from playCount (ie. playCount>0).

Content-content linking enables connections such as:

1. The mapping is done by the parent/child link table "contentlinkcontent".
2. 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
);