Avid Media Composer _SearchDB_ Schema

SearchDB.txt

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