Archive:Database Schema 4.0: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Firnsy
>Firnsy
Line 185: Line 185:
-- Due to the data being scraped from the internet and the high state of flux of
-- Due to the data being scraped from the internet and the high state of flux of
-- these APIs, the fields cXX are used.
-- these APIs, the fields cXX are used.
--
-- Collection to Content is a 1:N relationship only (i.e. each content type can
-- belong to one and only one collection).
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS content (
CREATE  TABLE IF NOT EXISTS content (
   idContent          INTEGER          NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idContent          INTEGER          NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
  idCollection      INTEGER          NOT NULL  DEFAULT 0 ,
   title              TEXT            NULL      DEFAULT NULL ,
   title              TEXT            NULL      DEFAULT NULL ,
   plot              TEXT            NULL      DEFAULT NULL ,
   plot              TEXT            NULL      DEFAULT NULL ,
Line 214: Line 219:
CREATE INDEX ixEpisodeSeason ON videocontent (episode ASC, season ASC) ;
CREATE INDEX ixEpisodeSeason ON videocontent (episode ASC, season ASC) ;
</pre>
</pre>


=== Table: classificationlinkcollection ===
=== Table: classificationlinkcollection ===

Revision as of 02:18, 30 May 2010

Revision: 2040h - 29 May 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.
  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


| idClassification | 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 classification via the mapping table "classificationlinkcontent".


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.


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.

  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".


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.

  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: classification

-- -----------------------------------------------------
-- Table: classification
--
-- Description:
-- The classification table provides both the top level and lower level 
-- classification descriptions that can be applied to content.
--
-- A top-level classification is defined as having its parent ID
-- (idClassificationParent) set to the value zero ("0").
--
-- A low-level classification should always have a valid parent ID defined.
--
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS classification (
  idClassification  INTEGER       NOT NULL  PRIMARY KEY AUTOINCREMENT ,
  idParent          INTEGER       NOT NULL  DEFAULT 0 ,
  name              VARCHAR(128)  NOT NULL ,
  localization      INTEGER       NOT NULL  DEFAULT 0 ,
  description       TEXT          NULL      DEFAULT NULL
);

INSERT INTO classification (idParent, name, localization) VALUES (0, 'Movies', 20342) ;
INSERT INTO classification (idParent, name, localization) VALUES (0, 'Tv Shows', 20343) ;
INSERT INTO classification (idParent, name, localization) VALUES (0, 'Music Videos', 20451) ;
INSERT INTO classification (idParent, name, localization) VALUES (0, 'Documentaries', 20452) ;


Table: collections

-- -----------------------------------------------------
-- Table: collections
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS collections (
  idCollection  INTEGER  NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
  idParent      INTEGER  NOT NULL  DEFAULT 0 ,
  name          TEXT     NULL      DEFAULT NULL ,
  description   TEXT     NULL      DEFAULT NULL
);


Table: content

-- -----------------------------------------------------
-- Table content
--
-- Description:
-- The content table provides all content specific metadata that is scrapable.
--
-- Due to the data being scraped from the internet and the high state of flux of
-- these APIs, the fields cXX are used.
--
-- Collection to Content is a 1:N relationship only (i.e. each content type can
-- belong to one and only one collection). 
--
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS content (
  idContent          INTEGER          NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
  idCollection       INTEGER          NOT NULL  DEFAULT 0 ,
  title              TEXT             NULL      DEFAULT NULL ,
  plot               TEXT             NULL      DEFAULT NULL ,
  plotSummary        TEXT             NULL      DEFAULT NULL ,
  tagline            TEXT             NULL      DEFAULT NULL ,
  onlineRatingVotes  TEXT             NULL      DEFAULT NULL ,
  onlineRating       TEXT             NULL      DEFAULT NULL ,
  released           UNSIGNED BIGINT  NULL      DEFAULT 0 ,
  onlineSource       TEXT             NULL      DEFAULT NULL ,
  onlineID           TEXT             NULL      DEFAULT NULL ,
  runTime            TEXT             NULL      DEFAULT NULL ,
  contentRating      TEXT             NULL      DEFAULT NULL ,
  originalTitle      TEXT             NULL      DEFAULT NULL ,

  thumbnails         TEXT             NULL      DEFAULT NULL ,
  trailerUrl         TEXT             NULL      DEFAULT NULL ,
  fanartUrl          TEXT             NULL      DEFAULT NULL ,

  season             INTEGER          NULL      DEFAULT NULL ,
  episode            INTEGER          NULL      DEFAULT NULL ,
  epgUrl             TEXT             NULL      DEFAULT NULL ,

  dateAdded          UNSIGNED BIGINT  NULL      DEFAULT 0
);

CREATE INDEX ixEpisodeSeason ON videocontent (episode ASC, season ASC) ;

Table: classificationlinkcollection

-- -----------------------------------------------------
-- Table: classificationlinkcollection
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS classificationlinkcollection (
  idClassification  INTEGER  NOT NULL ,
  idCollection      INTEGER  NOT NULL
);

CREATE INDEX ixClassificationCollection ON classificationlinkcollection (idClassification ASC, idCollection ASC) ;
CREATE INDEX ixCollectionClassification ON classificationlinkcollection (idCollection ASC, idClassification ASC) ;


Table: classificationlinkcontent

-- -----------------------------------------------------
-- Table: classificationlinkcontent
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS classificationlinkcontent (
  idClassification  INTEGER  NOT NULL ,
  idContent         INTEGER  NOT NULL
);

CREATE INDEX ixClassificationContent ON classificationlinkcontent (idClassification ASC, idContent ASC) ;
CREATE INDEX ixContentClassification ON classificationlinkcontent (idContent ASC, idClassification ASC) ;


Table: collectionlinkcontent

-- -----------------------------------------------------
-- Table: collectionlinkcontent
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS collectionlinkcontent (
  idCollection  INTEGER  NOT NULL ,
  idContent     INTEGER  NOT NULL
);

CREATE INDEX ixCollectionContent ON collectionlinkcontent (idCollection ASC, idContent ASC) ;
CREATE INDEX ixContentCollection ON collectionlinkcontent (idContent ASC, idCollection ASC) ;


Table: contentlinkcontent

-- -----------------------------------------------------
-- Table: contentlinkcontent
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS contentlinkcontent (
  idParent  INTEGER  NOT NULL  DEFAULT 0,
  idChild   INTEGER  NOT NULL
);

CREATE INDEX ixParentChild ON contentlinkcontent (idParent ASC, idChild ASC) ;
CREATE INDEX ixChildParent ON contentlinkcontent (idChild ASC, idParent ASC) ;


Table: dirents

-- -----------------------------------------------------
-- Table: dirents
--
-- Description:
-- The dirents 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.
--
-- 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="" />
-- </settings>
--
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS dirents (
  idDirent           INTEGER       NOT NULL  PRIMARY KEY  AUTOINCREMENT,
  idPath             INTEGER       NOT NULL ,
  direntFilename     VARCHAR(512)  NOT NULL ,
  direntURL          TEXT          NULL ,
  direntFileHash     VARCHAR(32)   NULL ,
  direntContentHash  VARCHAR(32)   NULL ,
  settings           TEXT          NULL ,
);

CREATE INDEX ixPathFilename ON dirents (idPath ASC, direntFilename ASC) ;


Table: contentlinkdirent

-- -----------------------------------------------------
-- Table: contentlinkdirent
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS contentlinkdirent (
  idDirent   INTEGER  NOT NULL ,
  idContent  INTEGER  NOT NULL
);

CREATE INDEX ixContentDirent ON contentlinkdirent (idContent ASC, idDirent ASC) ;
CREATE INDEX ixDirentContent ON contentlinkdirent (idDirent ASC, idContent ASC) ;


Table: path

-- -----------------------------------------------------
-- Table: path
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS path (
  idPath          INTEGER       NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
  idParent        INTEGER       NOT NULL  DEFAULT 0 ,
  idScraper       INTEGER       NOT NULL  DEFAULT 0 ,
  path            VARCHAR(512)  NULL      DEFAULT NULL ,
  hash            TEXT          NULL      DEFAULT NULL ,
  noUpdate        TINYINT       NULL      DEFAULT NULL
);

CREATE INDEX ixPath ON path (path ASC) ;


Table: scrapers

-- -----------------------------------------------------
-- Table: scrapers
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS scrapers (
  idSraper        INTEGER       NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
  content         TEXT          NULL      DEFAULT NULL ,
  scraper         TEXT          NULL      DEFAULT NULL ,
  scanRecursive   INTEGER       NULL      DEFAULT NULL ,
  useFolderNames  TINYINT       NULL      DEFAULT NULL ,
  settings        TEXT          NULL      DEFAULT NULL ,
  noUpdate        TINYINT       NULL      DEFAULT NULL
);


Table: bookmark

-- -----------------------------------------------------
-- Table: bookmark
-- -----------------------------------------------------
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
);

CREATE INDEX ixBookmark ON bookmark (idDirent ASC) ;


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
);

CREATE INDEX ixProfileContent ON settings (idProfile ASC, idContent ASC) ;
CREATE INDEX ixContentProfile ON settings (idContent ASC, idProfile ASC) ;


Table: stacktimes

-- -----------------------------------------------------
-- Table: stacktimes
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS stacktimes (
  idDirent  INTEGER  NULL  DEFAULT NULL ,
  times     TEXT     NULL  DEFAULT NULL
);

CREATE INDEX ixStackTimes ON stacktimes (idDirent ASC) ;


Table: streamdetails

-- -----------------------------------------------------
-- Table: streamdetails
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS streamdetails (
  idDirent          INTEGER NOT NULL ,
  streamType        INTEGER NULL      DEFAULT 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
);

CREATE INDEX ixStreamDetails ON streamdetails (idDirent ASC) ;


Table: thumbs

-- -----------------------------------------------------
-- Table: thumbs
--
-- Description:
-- The thumbs table stores the raw binary data representing a thumbnail or
-- fanart.
--
-- The "type" specifier indicates whether it is fanart or thumb
--
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS thumbs (
  idThumb   INTEGER  NOT NULL  PRIMARY KEY  AUTOINCREMENT,
  type      INTEGER  NOT NULL  DEFAULT 0,
  width     INTEGER  NOT NULL  DEFAULT 0,
  height    INTEGER  NOT NULL  DEFAULT 0
  thumb     BLOB     NULL
);


Table: thumbslinkcontent

-- -----------------------------------------------------
-- Table: thumbslinkcontent
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS thumbslinkcontent (
  idThumb    INTEGER  NOT NULL ,
  idContent  INTEGER  NOT NULL
);

CREATE INDEX ixThumbContent ON thumbslinkcontent (idThumb ASC, idContent ASC) ;
CREATE INDEX ixContentThumb ON thumbslinkcontent (idContent ASC, idThumb ASC) ;


Table: thumbslinkattributes

-- -----------------------------------------------------
-- Table: thumbslinkattributes
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS thumbslinkattributes (
  idThumb      INTEGER  NOT NULL ,
  idAttribute  INTEGER  NOT NULL
);

CREATE INDEX ixThumbAttribute ON thumbslinkattributes (idThumb ASC, idAttribute ASC) ;
CREATE INDEX ixAttributeThumb ON thumbslinkattributes (idAttribute ASC, idThumb ASC) ;


Table: attributes

-- -----------------------------------------------------
-- Table: attributes
-- 
-- Description:
-- The attributes table lists all available attributes that can be associated 
-- with "content" metadata.
--
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS attributes (
  idAttribute  INTEGER       NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
  name         VARCHAR(128)  NOT NULL,
  detail       TEXT          NULL      DEFAULT NULL
);

INSERT INTO attributes (name) VALUES ('Actors') ;
INSERT INTO attributes (name) VALUES ('Directors') ;
INSERT INTO attributes (name) VALUES ('Genres') ;
INSERT INTO attributes (name) VALUES ('Networks') ;
INSERT INTO attributes (name) VALUES ('Studios') ;
INSERT INTO attributes (name) VALUES ('Writers') ;


Table: attributeslinkcontent

-- -----------------------------------------------------
-- Table: attributeslinkcontent
-- 
-- Description:
-- The attributeslinkcontent table links a attribute/value pair to a particular
-- content.
-- 
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS attributeslinkcontent (
  idAttribute  INTEGER  NOT NULL ,
  idContent    INTEGER  NOT NULL ,
  value        TEXT     NULL      DEFAULT NULL
);

CREATE INDEX ixAttributeContent ON attributeslinkcontent (idAttribute ASC, idContent ASC) ;
CREATE INDEX ixContentAttribute ON attributeslinkcontent (idContent ASC, idAttribute ASC) ;


Table: version

-- -----------------------------------------------------
-- Table: version
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS version (
  videoRevision       INTEGER  NULL  DEFAULT NULL ,
  videoCompressCount  INTEGER  NULL  DEFAULT NULL 
);