Created
May 9, 2018 21:11
-
-
Save bradcordeiro/1e0b63447811e7c982f942e821fb2d57 to your computer and use it in GitHub Desktop.
Avid Media Composer _SearchDB_ Schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE preference ( -- Table of DB preference for project. | |
key TEXT UNIQUE, -- Unique string identifier | |
value TEXT NOT NULL -- Preference value. | |
); | |
CREATE TABLE host ( -- Table of all hosts for project. | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
name TEXT UNIQUE -- Unique string identifier | |
); | |
CREATE TABLE bin ( -- Table of all bins in a single project. | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
name TEXT NOT NULL, -- Name of bin as a canonical project relative path. | |
uid TEXT, -- Internal CompMan UUID | |
type INTEGER NOT NULL, -- Bin: 0, Script: 1 | |
modified INTEGER NOT NULL, -- Modified time (in seconds since unixepoch, 1970-01-01) | |
binindexerid INTEGER, -- Must match an existing binindexer.id (or NULL). | |
hostid INTEGER, -- Must match an existing host.id (or NULL). Only set for 'other' bins. | |
FOREIGN KEY (binindexerid) REFERENCES binindexer(id) | |
ON DELETE SET NULL -- Deleting binindexer will set all associated bin.binindexerids to 0. | |
ON UPDATE CASCADE, -- Updating binindexer.id will also update all associated bin.binindexerids. | |
FOREIGN KEY (hostid) REFERENCES host(id) | |
ON DELETE SET NULL -- Deleting host will set all associated bin.hostids to NULL. | |
ON UPDATE CASCADE -- Updating host.id will also update all associated bin.hostid. | |
); | |
CREATE TABLE mob ( -- Table of all mobs in a single project. | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
smpteid TEXT UNIQUE, -- The full SMPTE identifier | |
name TEXT NOT NULL, -- Name of mob. | |
type INTEGER NOT NULL, -- Equivalent CompMan mob type values. | |
modified INTEGER NOT NULL, -- Modified time (in seconds since unixepoch, 1970-01-01) | |
mobindexerid INTEGER, -- Must match an existing mobindexer.id (or NULL). | |
FOREIGN KEY (mobindexerid) REFERENCES mobindexer(id) | |
ON DELETE SET NULL -- Deleting mob will set all associated mob.mobindexerids to 0. | |
ON UPDATE CASCADE -- Updating mob.id will also update all associated mob.mobindexerids. | |
); | |
CREATE TABLE mobref ( -- Table of all mob references in a single project. | |
mobid INTEGER, -- Must match an existing mob.id. | |
refmobid INTEGER, -- Must match an existing mob.id. | |
tracklist TEXT, -- e.g. "V1A1A2D1" | |
offsetinsecs INTEGER NOT NULL, -- Offset from the beginning of the clip (0 for master clip) | |
durationinsecs INTEGER NOT NULL, -- Duration from offset | |
PRIMARY KEY (mobid, refmobid), -- This combination is unique (??] | |
FOREIGN KEY (mobid) REFERENCES mob(id) | |
ON DELETE CASCADE -- Deleting mob will delete all associated mobrefs | |
ON UPDATE CASCADE, -- Updating mob.id will also update all associated mobref.mobid. | |
FOREIGN KEY (refmobid) REFERENCES mob(id) | |
ON DELETE CASCADE -- Deleting mob will delete all associated mobrefs | |
ON UPDATE CASCADE -- Updating mob.id will also update all associated mobref.refmobid. | |
); | |
CREATE TABLE binmob ( -- Table of all mobs in for each bin in a single project. | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Desclared to match proposed schema diagram but not really required. | |
binid INTEGER, -- Must match existing bin.id. | |
mobid INTEGER, -- Must match existing mob.id. | |
name TEXT, -- Name of this bin/mob | |
modified INTEGER NOT NULL, -- Modified time (in seconds since unixepoch, 1970-01-01) | |
FOREIGN KEY (binid) REFERENCES bin(id) | |
ON DELETE CASCADE -- Deleting bin will delete all associated binmobs | |
ON UPDATE CASCADE, -- Updating bin.id will also update all associated binmob.binid | |
FOREIGN KEY (mobid) REFERENCES mob(id) | |
ON DELETE CASCADE -- Deleting mob will delete all associated binmobs | |
ON UPDATE CASCADE -- Updating mob.id will also update all associated binmob.mobid. | |
); | |
CREATE TABLE bincolumn ( -- Table of bin columns in the project | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
custom INTEGER NOT NULL, -- 0 for system and 1 for custom. | |
name TEXT NOT NULL, -- Name of the bin column | |
UNIQUE(custom, name) -- This combination is unique (??] | |
); | |
CREATE TABLE columndata ( -- Table of all bin columndata in a single project. | |
binmobid INTEGER, -- Must match an existing binmob.id. | |
bincolumnid INTEGER, -- Must match an existing bincolumn.id | |
value TEXT, -- bin column value | |
UNIQUE (binmobid, bincolumnid), -- This combination is unique (??] | |
FOREIGN KEY (binmobid) REFERENCES binmob(id) | |
ON DELETE CASCADE -- Deleting binmob will delete all associated columndata | |
ON UPDATE CASCADE, -- Updating binmob.id will also update all associated columndata.binmobid. | |
FOREIGN KEY (bincolumnid) REFERENCES bincolumn(id) | |
ON DELETE CASCADE -- Deleting bincolumn will delete all associated mobrefs | |
ON UPDATE CASCADE -- Updating bincolumn.id will also update all associated columndata.bincolumnid. | |
); | |
CREATE TABLE status ( -- Table of indexer status | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
name TEXT UNIQUE NOT NULL -- short description of indexing status | |
); | |
CREATE TABLE indexer ( -- Table of indexers in the project | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
name TEXT NOT NULL, -- Index service name | |
hostid INTEGER NOT NULL, -- Must match an existing host.id (or NULL). Only set for 'other' bins. | |
FOREIGN KEY (hostid) REFERENCES host(id) | |
ON DELETE CASCADE -- Deleting host will delete all associated bins. | |
ON UPDATE CASCADE -- Updating host.id will also update all associated bin.hostid. | |
); | |
CREATE TABLE mobindexer ( -- Log of all mob indexer operations in a single project | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
mobid INTEGER NOT NULL, -- Must match existing mob.id | |
indexerid INTEGER NOT NULL, -- Must match existing indexer.id | |
mobmodified INTEGER NOT NULL, -- Value of mob.modified when indexing started | |
starttime TEXT, -- Start time as a string (YYYY-MM-DD HH??.SSS) | |
stoptime TEXT, -- Stop time as a string (YYYY-MM-DD HH??.SSS) | |
patfile TEXT, -- Canonical project relative path to phonetic pat file. | |
statusid INTEGER NOT NULL, -- started, stopped (with appended error message), or completed | |
FOREIGN KEY (mobid) REFERENCES mob(id) | |
ON DELETE CASCADE -- Deleting mob will delete all associated mobindexers | |
ON UPDATE CASCADE, -- Updating mob.id will also update all associated mobindexer.mobid. | |
FOREIGN KEY (indexerid) REFERENCES indexer(id) | |
ON DELETE CASCADE -- Deleting indexer will delete all associated mobindexers | |
ON UPDATE CASCADE, -- Updating mob.id will also update all associated mobindexer.indexerids. | |
FOREIGN KEY (statusid) REFERENCES status(id) | |
ON DELETE CASCADE -- Deleting status will delete all associated mobindexers | |
ON UPDATE CASCADE -- Updating status.id will also update all associated mobindexer.statusids. | |
); | |
CREATE TABLE binindexer ( -- Log of all bin indexer operations in a single project | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
binid INTEGER, -- Must match existing bin.id | |
indexerid INTEGER, -- Must match existing indexer.id | |
binmodified INTEGER NOT NULL, -- Value of bin.modified when indexing started | |
starttime TEXT, -- Start time as a string (YYYY-MM-DD HH??.SSS) | |
stoptime TEXT, -- Stop time as a string (YYYY-MM-DD HH??.SSS) | |
statusid INTEGER NOT NULL, -- started, stopped (with appended error message), or completed | |
FOREIGN KEY (binid) REFERENCES bin(id) | |
ON DELETE CASCADE -- Deleting bin will delete all associated binindexers | |
ON UPDATE CASCADE, -- Updating bin.id will also update all associated binindexer.binid. | |
FOREIGN KEY (indexerid) REFERENCES indexer(id) | |
ON DELETE CASCADE -- Deleting indexer will delete all associated binindexers | |
ON UPDATE CASCADE, -- Updating bin.id will also update all associated binindexer.indexerids. | |
FOREIGN KEY (statusid) REFERENCES status(id) | |
ON DELETE CASCADE -- Deleting status will delete all associated binindexers | |
ON UPDATE CASCADE -- Updating status.id will also update all associated binindexer.statusids. | |
); | |
CREATE VIRTUAL TABLE script USING fts3( -- Table of all scripts in a single project | |
content TEXT -- Block of text in the script. | |
); | |
CREATE TABLE 'script_content'(docid INTEGER PRIMARY KEY, 'c0content'); | |
CREATE TABLE 'script_segments'(blockid INTEGER PRIMARY KEY, block BLOB); | |
CREATE TABLE 'script_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)); | |
CREATE TABLE patcleanup ( -- Table of indexers in the project | |
id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique identifier within the database. | |
pat TEXT NOT NULL -- pat file to be removed | |
); | |
CREATE TABLE markersdata ( -- Table of all bin markersdata in a single project. | |
binmobid INTEGER, -- Must match an existing binmob.id. | |
name TEXT, -- Marker (user) name. | |
comment TEXT, -- Marker comment. | |
color INTEGER, -- Marker color. | |
length INTEGER, -- Marker length. | |
start TEXT, -- Marker start TC. | |
end TEXT, -- Marker end TC. | |
track TEXT, -- Marker track name. | |
part TEXT, -- Marker part. | |
pos INTEGER, -- Marker pos. | |
UNIQUE (binmobid, name, comment, color, length, start, end, track, pos), -- This combination is unique. | |
FOREIGN KEY (binmobid) REFERENCES binmob(id) | |
ON DELETE CASCADE -- Deleting binmob will delete all associated columndata | |
ON UPDATE CASCADE -- Updating binmob.id will also update all associated columndata.binmobid. | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment