Archive:Database Schema 4.0: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>NedBot
m (Robot: Cosmetic changes)
No edit summary
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
{{mininav|[[Development]]}}
'''Revision: 1400h - 25 Sep 10'''
'''Revision: 1400h - 25 Sep 10'''


Line 745: Line 747:
);
);
</pre>
</pre>
[[Category:Development]]

Revision as of 08:12, 2 September 2013

Home icon grey.png   ▶ Development ▶ 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.

  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.

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