Archive:Database Schema 4.0: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Firnsy
No edit summary
No edit summary
(11 intermediate revisions by 4 users not shown)
Line 1: Line 1:
'''Revision: 2040h - 29 May 10'''
{{mininav|[[Development]]}}
 
'''Revision: 1400h - 25 Sep 10'''


== Introduction ==
== Introduction ==


As mentioned some weeks ago we have been busy working on a completely new DB schema to replace the current one (which has served as well to date). The new 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 :laugh:).
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.
 


The current state of the schema (which we are currently most happiest with) is being posted for a community review to ensure any potential "gotchas" are caught and addressed early.
== Objectives ==


I'll keep the first few posts up to date with any changes we identify along the way, with the latest revision on top. [COLOR="red"]Please keep all posts focussed and on topic, remember this is the DEV forum.[/COLOR]
=== 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.


The initial (read loose) requirements for this DB schema are listed as follows
=== Retrieval of metadata should be conducted in 2 passes ===
with a small description provided about each.
# 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. ===


=== Loosely coupled metadata <-> file/path mappings. ===
=== Arbitrary key/value pairs (or attributes) for additional information that is not stored in the main content metadata should be supported. ===


# 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.
=== Everything gets added to the library, whether we can find online information or not. ===
# This mapping is achieved by the contentlinkdirent table whereby metadata (ie. "content" table) is mapped to the file/path (ie. "dirent" table).
# N:N (content:file/path) mapping, is possible with this configuration.
# 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.


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


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


ALSO
== Classes and Relationship ==
The class and relationships currently defined are as follows:


# Classification should be expandable to add new values making possible finer    grouping/classification of media.
# Classification is achieved by the classification table whereby an official list of classifications already exists but can be expanded by the user.
# The official classifications are "Movies", "Tv Shows", "Music Videos", and "Documentaries".
# 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").
# 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.
<pre>
<pre>
Movies <---+-- Home Movies <-+-- Birthdays
class: object
          |                +-- Holidays
attributes:
          |                +-- Mum & Dad's Adventures
  - title
          +-- Tv Movies
  - filename
relations
  none


Tv Shows <-+-- Cartoons
class: content : object
          +-- Documentaries
attributes:
  - released
  - filename
  - rating
  - title
relations
  none


Music Videos
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


| 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          |
</pre>
* 2.7. Content is associated to a particular classification via the mapping table "classificationlinkcontent".


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


=== Retrieval of metadata should be conducted in 2 passes ===
# Critical information for sorting and/or filtering,
# Then additional information as required for display.


class: picture : object
attributes:
  - height
  - width
  - filename
  - title
relations
  none


class: banner : picture : object
attributes:
  - width
  - filename
  - title
  - height
relations
  none


=== Mapping from file/path listings to metadata. ===
class: cover : picture : object
# It should be possible for a user to view a top-level classification node (eg. "Movies") using a filesystem representative view.
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


=== Reliable identification when disk content and library content differs. ===
class: photo : picture : object
attributes:
  - width
  - filename
  - title
  - height
relations
  none




class: organisation
attributes:
  - name
relations
  none


=== Arbitrary key/value pairs (or attributes) for additional information that is not stored in the main content metadata should be supported. ===
class: person : organisation
# 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.
attributes:
# All values are stored in a string representation regardless of their type.
  - name
# Current supported attributes include "Actors", "Directores", "Genres", "Networks" "Studios", and "Writers".
relations
  none


class: actor : person : organisation
attributes:
  - role
  - name
relations
  none


class: director : person : organisation
attributes:
  - name
relations
  none


=== Everything gets added to the library, whether we can find online information or not. ===
class: band : organisation
attributes:
  - name
relations
  > band_has_song


class: studio : organisation
attributes:
  - name
relations
  none




=== Content specific parameters such as watched, last played, and bookmarks should be stored per-profile. ===
class: grouping
# These parameters are stored in the "bookmarks" and "settings" tables both of which carry the idProfile attribute for linking to a profile.
attributes:
# Watched can be extraced from playCount (ie. playCount>0).
  none
   
relations
  none


class: genre : grouping
attributes:
  - name
relations
  none


=== Content-content linking enables connections such as: ===
class: directory : grouping
# The mapping is done by the parent/child link table "contentlinkcontent".
attributes:
# The largest linkage depth will typically be 3 (e.g. item 9.1. above). - ''The content linking needs some more brainstorming''
  - path
relations
  > directory_has_directory
  > directory_has_content


Examples of content-content linking are:
class: movieset : grouping
* Tv Show > Season/Episode
attributes:
* Movie > Soundtrack
  none
* Music > Music Video
relations
* Home Movies > Photos
  > movieset_has_movie


class: season : grouping
attributes:
  - season
relations
  > season_has_episode


class: album : grouping
attributes:
  none
relations
  > album_has_studio
  > album_has_song


'''NOTES:'''
class: playlist : grouping
The following notes should be considered when viewing the DB schema.
attributes:
* SQLite treats VARCHAR as TEXT.
  none
* 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.
relations
* Date/Time strings are stored as UNIX time (ie. 64-bit UNSIGNED)
  none
* 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.
</pre>


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 ==
== Schema Draft ==
=== Table: profile ===
 
=== Table: objectTypes ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: profile
-- Table: objectTypes
--
--
-- Description:
-- Description:
-- The profile table provides a representation of individual profiles. A number
-- Stores the name and ancestory of a particular object.
-- of settings and user specific configurations can be attributed to individual
-- profiles.
--
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS profile (
CREATE  TABLE IF NOT EXISTS objectTypes (
   idProfile  INTEGER      NOT NULL  PRIMARY KEY AUTOINCREMENT ,
   idObjectType        INTEGER      NOT NULL  PRIMARY KEY AUTOINCREMENT,
   name       VARCHAR(128)  NOT NULL
   idParentObjectType  INTEGER       NOT NULL  DEFAULT 0 ,
  stub                VARCHAR(128)  NOT NULL ,
  name                TEXT
);
);
INSERT INTO profile (name) VALUES ('masterprofile') ;
</pre>
</pre>


 
=== Table: attributeTypes ===
=== Table: classification ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: classification
-- Table: attributeTypes
--
--
-- Description:
-- Description:
-- The classification table provides both the top level and lower level
-- Stores the name, type and inheritablity of a particular object type's
-- classification descriptions that can be applied to content.
-- attributes.
--
--
-- A top-level classification is defined as having its parent ID
-- The dataType maps to a specific data type:
-- (idClassificationParent) set to the value zero ("0").
--  0 = String
--   1 = Number
--  2 = Blob
--
--
-- A low-level classification should always have a valid parent ID defined.
-- 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 classification (
CREATE  TABLE IF NOT EXISTS attributeTypes (
   idClassification INTEGER      NOT NULL  PRIMARY KEY AUTOINCREMENT ,
   idAttributeType INTEGER      NOT NULL  PRIMARY KEY AUTOINCREMENT ,
   idParent          INTEGER      NOT NULL DEFAULT 0 ,
   idObjectType    INTEGER      NOT NULL ,
   name              VARCHAR(128)  NOT NULL ,
   stub            VARCHAR(128)  NOT NULL ,
   localization      INTEGER      NOT NULL  DEFAULT 0 ,
   name            TEXT          NOT NULL ,
   description       TEXT          NULL      DEFAULT 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)
);
);
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) ;
</pre>
</pre>


 
=== Table: relationshipTypes ===
 
=== Table: collections ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: collections
-- 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 collections (
CREATE  TABLE IF NOT EXISTS relationshipTypes (
   idCollection INTEGER NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idRelationshipType INTEGER       NOT NULL  PRIMARY KEY AUTOINCREMENT ,
   idParent      INTEGER  NOT NULL  DEFAULT 0 ,
  stub                VARCHAR(128)  NOT NULL ,
   name          TEXT    NULL     DEFAULT NULL ,
  idObjectType1      INTEGER      NOT NULL ,
   description   TEXT    NULL      DEFAULT 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)
);
);
</pre>
</pre>


 
=== Table: objects ===
 
=== Table: content ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table content
-- Table: objects
--
--
-- Description:
-- Description:
-- The content table provides all content specific metadata that is scrapable.
-- Stores the name and ancestory of a particular object.
--
--
-- Due to the data being scraped from the internet and the high state of flux of
-- these APIs, the fields cXX are used.
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS content (
CREATE  TABLE IF NOT EXISTS objects (
   idContent          INTEGER         NOT NULL  PRIMARY KEY   AUTOINCREMENT ,
   idObject      INTEGER       NOT NULL  PRIMARY KEY AUTOINCREMENT ,
   title              TEXT            NULL      DEFAULT NULL ,
   idObjectType INTEGER       NOT NULL ,
  plot              TEXT            NULL      DEFAULT NULL ,
   stub          VARCHAR(128) NOT NULL ,
  plotSummary        TEXT            NULL      DEFAULT NULL ,
   name         TEXT          NOT NULL ,
  tagline            TEXT            NULL      DEFAULT NULL ,
          
  onlineRatingVotes TEXT            NULL      DEFAULT NULL ,
   FOREIGN KEY (idObjectType) REFERENCES objectTypes(idObjectType)
  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) ;
</pre>
</pre>


 
=== Table: attributes ===
 
=== Table: classificationlinkcollection ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: classificationlinkcollection
-- Table: attributes
--
-- Description:
-- Stores the attribute and attribute value specific to a particular
-- object.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS classificationlinkcollection (
CREATE  TABLE IF NOT EXISTS attributes (
   idClassification INTEGER  NOT NULL ,
   idAttribute      INTEGER      NOT NULL PRIMARY KEY AUTOINCREMENT ,
   idCollection     INTEGER  NOT NULL
  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)
);
);
CREATE INDEX ixClassificationCollection ON classificationlinkcollection (idClassification ASC, idCollection ASC) ;
CREATE INDEX ixCollectionClassification ON classificationlinkcollection (idCollection ASC, idClassification ASC) ;
</pre>
</pre>


 
=== Table: relationships ===
 
=== Table: classificationlinkcontent ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: classificationlinkcontent
-- Table: relationships
--
-- Description:
-- Stores the relationship and sequence (as appropriate) between two
-- objects.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS classificationlinkcontent (
CREATE  TABLE IF NOT EXISTS relationships (
   idClassification INTEGER  NOT NULL ,
   idRelationship      INTEGER      NOT NULL PRIMARY KEY  AUTOINCREMENT ,
   idContent        INTEGER NOT NULL
  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)
);
);
CREATE INDEX ixClassificationContent ON classificationlinkcontent (idClassification ASC, idContent ASC) ;
CREATE INDEX ixContentClassification ON classificationlinkcontent (idContent ASC, idClassification ASC) ;
</pre>
</pre>


 
=== View: relationships ===
 
=== Table: collectionlinkcontent ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: collectionlinkcontent
-- View: relationships
--
-- Description:
-- A generic view of all relationships between objects types.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS collectionlinkcontent (
CREATE VIEW viewRelationshipsAll
  idCollection  INTEGER  NOT NULL ,
AS
  idContent    INTEGER  NOT NULL
SELECT o1.idObject        AS o1ID,
);
        o1.stub            AS o1Stub,
 
        IFNULL(o1.name,
CREATE INDEX ixCollectionContent ON collectionlinkcontent (idCollection ASC, idContent ASC) ;
              o1.stub)    AS o1Name,
CREATE INDEX ixContentCollection ON collectionlinkcontent (idContent ASC, idCollection ASC) ;
        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;
</pre>
</pre>


 
=== Table: queries ===
 
=== Table: contentlinkcontent ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: contentlinkcontent
-- 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 contentlinkcontent (
CREATE  TABLE IF NOT EXISTS queries (
   idParent INTEGER NOT NULL  DEFAULT 0,
   idQuery    INTEGER    NOT NULL PRIMARY KEY AUTOINCREMENT ,
   idChild  INTEGER  NOT NULL
  stub        TEXT        NOT NULL ,
  locked      INTEGER     NOT NULL  DEFAULT 0 ,
   query      TEXT        NOT NULL
);
);
CREATE INDEX ixParentChild ON contentlinkcontent (idParent ASC, idChild ASC) ;
CREATE INDEX ixChildParent ON contentlinkcontent (idChild ASC, idParent ASC) ;
</pre>
</pre>


=== Table: dirents ===
=== Table: dirents ===
Line 294: Line 500:
--
--
-- Description:
-- Description:
-- The dirents table handles the filesystem representation of a playable item.
-- Stores the filesystem representation of a playable item.
--
--
-- Each row represents a directory entry taking the form of either a file or
-- Each row represents a directory entry taking the form of either a
-- folder. A folder in this table thus represents a folder that has a
-- file or folder. A folder in this table thus represents a folder that
-- single playable item contained within.
-- has a single playable item contained within.
--
--
-- The settings attributes takes an XML structure that defines attributes like
-- The settings attributes takes an XML structure that defines
-- crop position, zoom, etc. Supported attributes are:
-- attributes like crop position, zoom, etc. Supported attributes are:
--
--
-- <settings>
-- <settings>
Line 326: Line 532:
--  <setting id="noisereduction" type="double" value="" />
--  <setting id="noisereduction" type="double" value="" />
--  <setting id="nonlinstretch" type="int" 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>
-- </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 (
CREATE  TABLE IF NOT EXISTS dirents (
   idDirent           INTEGER      NOT NULL  PRIMARY KEY  AUTOINCREMENT,
   idDirent     INTEGER      NOT NULL  PRIMARY KEY  AUTOINCREMENT,
   idPath             INTEGER      NOT NULL ,
   idPath       INTEGER      NOT NULL ,
   direntFilename     VARCHAR(512)  NOT NULL ,
   filename     TEXT          NOT NULL ,
   direntURL         TEXT          NULL ,
   url         TEXT          NULL ,
   direntFileHash     VARCHAR(32)  NULL ,
   fileLength  INTEGER      NOT NULL  DEFAULT 0 ,
   direntContentHash VARCHAR(32)  NULL ,
  fileHash     VARCHAR(32)  NULL ,
   settings           TEXT          NULL ,
   contentHash VARCHAR(32)  NULL ,
   settings     TEXT          NULL ,
 
  FOREIGN KEY (idPath) REFERENCES paths(idPath)
);
);


CREATE INDEX ixPathFilename ON dirents (idPath ASC, direntFilename ASC) ;
CREATE INDEX ixDPath ON dirents(idPath);
CREATE INDEX ixDPathFilename ON dirents (idPath ASC, filename(255) ASC);
</pre>
</pre>


 
=== Table: objectlinkdirent ===
 
=== Table: contentlinkdirent ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: contentlinkdirent
-- Table: objectlinkdirent
--
-- Description:
-- Stores the primary link between an objects metadata and the
-- playable/viewable item.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS contentlinkdirent (
CREATE  TABLE IF NOT EXISTS objectlinkdirent (
   idDirent   INTEGER  NOT NULL ,
   idDirent INTEGER  NOT NULL ,
   idContent INTEGER  NOT NULL
   idObject INTEGER  NOT NULL ,
 
  FOREIGN KEY (idDirent) REFERENCES dirents(idDirent) ,
  FOREIGN KEY (idObject) REFERENCES objects(idObject)
);
);


CREATE INDEX ixContentDirent ON contentlinkdirent (idContent ASC, idDirent ASC) ;
CREATE INDEX ixOLDDirent ON objectlinkdirent(idDirent);
CREATE INDEX ixDirentContent ON contentlinkdirent (idDirent ASC, idContent ASC) ;
CREATE INDEX ixOLDObject ON objectlinkdirent(idObject);
</pre>
</pre>


 
=== Table: paths ===
 
=== Table: path ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: path
-- 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 path (
CREATE  TABLE IF NOT EXISTS paths (
   idPath          INTEGER       NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idPath          INTEGER     NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idParent        INTEGER       NOT NULL  DEFAULT 0 ,
   idParent        INTEGER     NOT NULL  DEFAULT 0 ,
   idScraper      INTEGER       NOT NULL  DEFAULT 0 ,
   idScraper      INTEGER     NOT NULL  DEFAULT 0 ,
   path            VARCHAR(512)  NULL      DEFAULT NULL ,
   path            TEXT        NULL      DEFAULT NULL ,
   hash            TEXT          NULL      DEFAULT NULL ,
   hash            VARCHAR(32)  NULL      DEFAULT NULL ,
   noUpdate        TINYINT      NULL      DEFAULT NULL
   noUpdate        INTEGER      NULL      DEFAULT NULL ,
);
 
  FOREIGN KEY (idScraper) REFERENCES scrapers(idScraper) );


CREATE INDEX ixPath ON path (path ASC) ;
CREATE INDEX ixPScraper ON paths(idScraper);
</pre>
</pre>


=== Table: scrapers ===
=== Table: scrapers ===
Line 383: Line 624:
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: scrapers
-- Table: scrapers
--
-- Description:
-- Stores the scraper information which an added source path has been
-- attributed with.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS scrapers (
CREATE  TABLE IF NOT EXISTS scrapers (
   idSraper        INTEGER      NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idScraper      INTEGER      NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   content        TEXT          NULL      DEFAULT NULL ,
   content        TEXT          NULL      DEFAULT NULL ,
   scraper        TEXT          NULL      DEFAULT NULL ,
   scraper        TEXT          NULL      DEFAULT NULL ,
   scanRecursive  INTEGER      NULL      DEFAULT NULL ,
   scanRecursive  INTEGER      NULL      DEFAULT NULL ,
   useFolderNames  TINYINT       NULL      DEFAULT NULL ,
   useFolderNames  INTEGER       NULL      DEFAULT NULL ,
   settings        TEXT          NULL      DEFAULT NULL ,
   settings        TEXT          NULL      DEFAULT NULL ,
   noUpdate        TINYINT       NULL      DEFAULT NULL
   noUpdate        INTEGER       NULL      DEFAULT NULL
);
);
</pre>
</pre>


=== Table: profile ===
<pre>
-- -----------------------------------------------------
-- 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');
</pre>


=== Table: bookmark ===
=== Table: bookmark ===
Line 401: Line 664:
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: bookmark
-- Table: bookmark
--
-- Description:
-- Stores bookmarks associated with a playable item.
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS bookmark (
CREATE  TABLE IF NOT EXISTS bookmark (
   idBookmark          INTEGER NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idBookmark          INTEGER NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   idDirent            INTEGER NOT NULL ,
   idDirent            INTEGER NOT NULL ,
   idProfile          INTEGER NOT NULL ,
   idProfile          INTEGER NOT NULL ,
   timeInSeconds      DOUBLE  NULL      DEFAULT NULL ,
   timeInSeconds      DOUBLE  NULL      DEFAULT NULL ,
   totalTimeInSeconds  DOUBLE  NULL      DEFAULT NULL ,
   totalTimeInSeconds  DOUBLE  NULL      DEFAULT NULL ,
Line 411: Line 678:
   player              TEXT    NULL      DEFAULT NULL ,
   player              TEXT    NULL      DEFAULT NULL ,
   playerState        TEXT    NULL      DEFAULT NULL ,
   playerState        TEXT    NULL      DEFAULT NULL ,
   type                INTEGER NULL      DEFAULT NULL
   type                INTEGER NULL      DEFAULT NULL ,
 
  FOREIGN KEY (idDirent) REFERENCES dirents(idDirent) ,
  FOREIGN KEY (idProfile) REFERENCES profiles(idProfile)
);
);


CREATE INDEX ixBookmark ON bookmark (idDirent ASC) ;
CREATE INDEX ixBDirent ON bookmark(idDirent);
CREATE INDEX ixBProfile ON bookmark(idProfile);
</pre>
</pre>


=== Table: settings ===
=== Table: settings ===
Line 425: Line 694:
--
--
-- Description:
-- Description:
-- The settings table stores all associated settings for a content entry per
-- The settings table stores all associated settings for a content entry
-- profile.
-- per profile.
--
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS settings (
CREATE  TABLE IF NOT EXISTS settings (
   idProfile  INTEGER      NOT NULL ,
   idProfile  INTEGER      NOT NULL ,
   idContent  INTEGER      NOT NULL ,
   idObject    INTEGER      NOT NULL ,
 
   playCount  INTEGER      NULL      DEFAULT NULL ,
   playCount  INTEGER      NULL      DEFAULT NULL ,
   lastPlayed  VARCHAR(24)  NULL      DEFAULT NULL
   lastPlayed  VARCHAR(24)  NULL      DEFAULT NULL
);
);


CREATE INDEX ixProfileContent ON settings (idProfile ASC, idContent ASC) ;
CREATE INDEX ixSProfileContent ON settings (idProfile ASC, idContent ASC);
CREATE INDEX ixContentProfile ON settings (idContent ASC, idProfile ASC) ;
CREATE INDEX ixSContentProfile ON settings (idContent ASC, idProfile ASC);
</pre>
</pre>


=== Table: stacktimes ===
=== Table: stacktimes ===
Line 447: Line 713:
-- -----------------------------------------------------
-- -----------------------------------------------------
-- 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) ;
</pre>
=== Table: streamdetails ===
<pre>
-- -----------------------------------------------------
-- 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) ;
</pre>
=== Table: thumbs ===
<pre>
-- -----------------------------------------------------
-- Table: thumbs
--
--
-- Description:
-- Description:
-- The thumbs table stores the raw binary data representing a thumbnail or
-- Stores how multiple files in a stack are stitched together.
-- fanart.
--
--
-- The "type" specifier indicates whether it is fanart or thumb
-- NOTE:
-- Should be storing "times" in its native format (eg. integer or real)
-- to reduce the number of conversions?
--
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS thumbs (
CREATE  TABLE IF NOT EXISTS stacktimes (
   idThumb  INTEGER  NOT NULL  PRIMARY KEY AUTOINCREMENT,
   idDirent INTEGER  NULL  DEFAULT NULL ,
  type      INTEGER  NOT NULL  DEFAULT 0,
   times    TEXT     NULL  DEFAULT NULL ,
   width     INTEGER  NOT NULL  DEFAULT 0,
  height    INTEGER  NOT NULL  DEFAULT 0
  thumb    BLOB    NULL
);
</pre>
 
 


=== Table: thumbslinkcontent ===
  FOREIGN KEY (idDirent) REFERENCES dirents(idDirent)
<pre>
-- -----------------------------------------------------
-- 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 ixSTDirent ON stacktimes (idDirent ASC);
CREATE INDEX ixContentThumb ON thumbslinkcontent (idContent ASC, idThumb ASC) ;
</pre>
</pre>


 
=== Table: version ===
 
=== Table: thumbslinkattributes ===
<pre>
<pre>
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table: thumbslinkattributes
-- Table: version
-- -----------------------------------------------------
--
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) ;
</pre>
 
 
 
=== Table: attributes ===
<pre>
-- -----------------------------------------------------
-- Table: attributes
--  
-- Description:
-- Description:
-- The attributes table lists all available attributes that can be associated
-- Stores the current version of the DB and the number of times it has
-- with "content" metadata.
-- been compressed (sqlite3) only.
--
--
-- -----------------------------------------------------
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS attributes (
CREATE  TABLE IF NOT EXISTS version (
   idAttribute INTEGER      NOT NULL  PRIMARY KEY  AUTOINCREMENT ,
   contentRevision      INTEGER NULL  DEFAULT NULL ,
  name        VARCHAR(128)  NOT NULL,
   contentCompressCount INTEGER  NULL  DEFAULT 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') ;
</pre>
 
 
 
=== Table: attributeslinkcontent ===
<pre>
-- -----------------------------------------------------
-- 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) ;
</pre>
</pre>


 
[[Category:Development]]
 
=== Table: version ===
<pre>
-- -----------------------------------------------------
-- Table: version
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS version (
  videoRevision      INTEGER  NULL  DEFAULT NULL ,
  videoCompressCount  INTEGER  NULL  DEFAULT NULL
);
</pre>

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
);
Retrieved from "https://kodi.wiki/index.php?title=Archive:Database_Schema_4.0&oldid=63929"