332 lines
15 KiB
SQL
332 lines
15 KiB
SQL
-- vim: ts=3
|
|
BEGIN TRANSACTION;
|
|
|
|
--
|
|
-- Set SQLite's journal mode.
|
|
--
|
|
-- journal_mode=memory - set to voletile RAM to avoid using RAMdisc for temp files
|
|
PRAGMA journal_mode=memory;
|
|
--
|
|
-- Set SQLite's vacuum mode.
|
|
--
|
|
-- auto_vacuum=none - to avoid using RAMdisc for temp files
|
|
PRAGMA auto_vacuum=full;
|
|
|
|
|
|
-- The QDB information for this custom database
|
|
CREATE TABLE _custom_info_ (
|
|
version INTEGER NOT NULL
|
|
);
|
|
INSERT INTO _custom_info_(version) VALUES(6);
|
|
|
|
|
|
--
|
|
-- This file is owned by the integrator. It can be customized in any way, and written or read in any way.
|
|
-- The MME itself does not use anything in the file. It is maintained purely by having the triggers create
|
|
-- and delete rows as the master tables have theirs inserted and deleted.
|
|
--
|
|
-- As there is no dependency from the MME on this file and it's contents, triggers can be created to handle
|
|
-- updates also.
|
|
--
|
|
|
|
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
---- @table mediastores_custom
|
|
---- The <fname>mediastores_extra</fname> table is an optional extension to the
|
|
---- <fname>mediastores</fname> table. It should have an <var>msid</var> column so
|
|
---- that it can be joined with the <var>library</var> table where
|
|
---- <var>msid</var>=<var>msid</var>.
|
|
----
|
|
---- You should create triggers so that when a row is added to or removed from the
|
|
---- <fname>mediastores</fname> table it is also added to or removed from this table.
|
|
----
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
---- This table could be used for mediastore renaming (change medium name)
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
-- CREATE TABLE mediastores_custom (
|
|
-- msid INTEGER PRIMARY KEY,
|
|
-- medianame TEXT DEFAULT '',
|
|
-- filecount INTEGER DEFAULT 0
|
|
-- );
|
|
|
|
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
---- @table library_custom
|
|
---- The <fname>library_custom</fname> table is an optional extension to the
|
|
---- <fname>library</fname> table. It should have an <var>fid</var> column so that it
|
|
---- can be joined with the <fname>library</fname> table where
|
|
---- <var>fid</var>=<var>fid</var>, adding this table's columns to the main
|
|
---- <fname>library</fname> table.
|
|
----
|
|
---- Some examples of columns that could be added to the <fname>library_custom</fname>
|
|
---- table are <var>rating</var> and <var>skip_count</var>. However, any user-defined
|
|
---- columns can be added to this table.
|
|
----
|
|
---- You should create triggers so that when a row is added to or removed from the
|
|
---- <fname>library</fname> table it is also added to or removed from this table.
|
|
----
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
---- This table could be used by projects for user-ratings ("5 stars"), etc.
|
|
---- *******************************************************************************
|
|
---- *******************************************************************************
|
|
-- CREATE TABLE library_custom (
|
|
-- fid INTEGER PRIMARY KEY
|
|
---- rating INTEGER,
|
|
---- skip_count INTEGER,
|
|
-- );
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table changed_fids
|
|
-- This table is used for report added, removed or updated file id's
|
|
-- after syncing.
|
|
-- The data is filled in by triggers which react on changes of table 'library'
|
|
-- and has to be removed manually (usually after the data has been read and
|
|
-- evaluated).
|
|
-- The data contained in this table is e. g. used by CoMMCo's MediaManager
|
|
-- component to detect, evaluate and report changes in the media DB after syncing.
|
|
-- Possible values for 'change':
|
|
-- 1 (= NCoMMCo::FILECHANGETYPE_INSERT): Inserted fid
|
|
-- 2 (= NCoMMCo::FILECHANGETYPE_UPDATE): Updated data corresponding to fid
|
|
-- 4 (= NCoMMCo::FILECHANGETYPE_DELETE): Removed fid
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
--CREATE TABLE changed_fids(
|
|
-- seqnr INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
-- fid INTEGER REFERENCES library,
|
|
-- change INTEGER
|
|
--);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table folders_custom
|
|
-- This table is used for sorting of files based on the basepath. When using the
|
|
-- original basepath of the folders table, the sorting is wrong because a trailing
|
|
-- slash '/' is used for string comparison (and maybe compared with a ' ' (space)
|
|
-- of another folder).
|
|
-- This table is updated with triggers whenever a folder was added or removed to
|
|
-- the folders table.
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE folders_custom (
|
|
folderid INTEGER PRIMARY KEY,
|
|
basepath TEXT DEFAULT ''
|
|
);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table playsessiondata
|
|
-- The <fname>playsessiondata</fname> table is available for storing
|
|
-- control context specific play session data.
|
|
--
|
|
-- @field oid An order identifier. This can be used to assign an arbitrary
|
|
-- order to the play session elements
|
|
-- using the SQL <const>ORDER BY</const> clause.
|
|
-- @field fid The track file ID (<var>fid</var>).
|
|
-- @field ccid The ID of the control context to which the session belongs.
|
|
-- Each control context can have only one play session.
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE playsessiondata (
|
|
oid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
fid INTEGER NOT NULL REFERENCES library,
|
|
ccid INTEGER NOT NULL
|
|
);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table categorysyncs
|
|
-- The <fname>categorysyncs</fname> table stores category sync options for external
|
|
-- devices like iPod.
|
|
--
|
|
-- @field syncId Unique identifier for a category sync job (clock, primary key)
|
|
-- @field msid Mediastore id of the category item
|
|
-- @field category Category string (custom types start with the unique prefix 'CUSTOM_')
|
|
-- @field basepath Explore path of the given category on the external device, e.g. Music/Artists/
|
|
--
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE categorysyncs (
|
|
jobId INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
syncId INTEGER NOT NULL,
|
|
msid INTEGER NOT NULL,
|
|
category INTEGER NOT NULL,
|
|
basepath TEXT DEFAULT ''
|
|
);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table categorydata
|
|
-- The <fname>categorydata</fname> table stores data from category syncs of external
|
|
-- devices like iPod.
|
|
--
|
|
-- @field uniqueId Unique identifier of a category sync data entry (clock, primary key)
|
|
-- @field syncId Unique identifier for a category sync job (foreign key)
|
|
-- @field msid Mediastore id of the category item
|
|
-- @field category Category string (custom types start with the unique prefix 'CUSTOM_')
|
|
-- @field folderIdx Index of the category item within its category folder
|
|
-- @field name Textual i.e. human readable representation of the category item
|
|
--
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE categorydata (
|
|
uniqueId INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
jobId INTEGER REFERENCES categorysyncs,
|
|
folderIdx INTEGER NOT NULL,
|
|
name TEXT DEFAULT ''
|
|
);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table mediastores_type_custom
|
|
-- The <fname>mediastores_type_custom</fname> table stores medium content data
|
|
-- related to each medium which is synced at least to 1st pass level.
|
|
-- Note: This data is stored only temporarily in this table. Based on this data
|
|
-- the <fname>mediastores</fname> columns
|
|
-- <fname>medium_content</fname> and <fname>content_type</fname> are set.
|
|
-- After the <fname>mediastores</fname> table has been updated the data in
|
|
-- <fname>mediastores_type_custom</fname> table is deleted again.
|
|
--
|
|
-- @field msid The mediastore ID.
|
|
-- @field ftype The type of the media track, which corresponds to the
|
|
-- <const>FTYPE_*</const> types defined in <fname sh>mme/meinterface.h</fname>:
|
|
-- * 0 = unknown
|
|
-- * 1 = audio
|
|
-- * 2 = video
|
|
-- * 3 = audio and video
|
|
-- * 4 = photo
|
|
-- @field format The format of the track, as defined by the
|
|
-- <xref xid="api/mme_format.html"><const>MME_FORMAT_*</const></xref> values.
|
|
--
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE mediastores_type_custom (
|
|
msid INTEGER DEFAULT 0,
|
|
ftype INTEGER DEFAULT 0,
|
|
format INTEGER DEFAULT 0,
|
|
FOREIGN KEY(msid) REFERENCES mediastores(msid)
|
|
);
|
|
|
|
|
|
-- *****************
|
|
-- Optional TRIGGERS
|
|
-- *****************
|
|
|
|
|
|
------ Library Custom Triggers
|
|
--CREATE TRIGGER library_custom_insert AFTER INSERT ON library
|
|
-- BEGIN
|
|
-- INSERT INTO library_custom(fid) VALUES(NEW.fid);
|
|
-- END;
|
|
|
|
|
|
--CREATE TRIGGER library_custom_delete DELETE ON library
|
|
-- BEGIN
|
|
-- DELETE FROM library_custom WHERE fid=OLD.fid;
|
|
-- END;
|
|
|
|
|
|
---- Mediastore Custom Triggers
|
|
--CREATE TRIGGER mediastores_custom_insert AFTER INSERT ON mediastores
|
|
-- BEGIN
|
|
-- INSERT INTO mediastores_custom(msid) VALUES(NEW.msid);
|
|
-- END;
|
|
|
|
|
|
--CREATE TRIGGER mediastores_custom_delete DELETE ON mediastores
|
|
-- BEGIN
|
|
-- DELETE FROM mediastores_custom WHERE msid=OLD.msid;
|
|
-- END;
|
|
|
|
|
|
-- Folders Custom Triggers
|
|
-- NBT Specific: folders_custom tigger is specified in mme_custom.sql
|
|
--CREATE TRIGGER folders_custom_insert AFTER INSERT ON folders
|
|
-- BEGIN
|
|
-- INSERT INTO folders_custom(folderid, basepath) VALUES(NEW.folderid, replace((SELECT basepath FROM folders WHERE folderid=NEW.folderid), "/", x'01'));
|
|
-- END;
|
|
|
|
|
|
--CREATE TRIGGER folders_custom_delete DELETE ON folders
|
|
-- BEGIN
|
|
-- DELETE FROM folders_custom WHERE folderid=OLD.folderid;
|
|
-- END;
|
|
|
|
|
|
-- *****************
|
|
-- REQUIRED TRIGGERS
|
|
-- *****************
|
|
-- changed_fids Triggers
|
|
-- NOTE: Value for column 'change' must correspond to enum value NCoMMCo::FILECHANGETYPE_INSERT
|
|
--CREATE TRIGGER library_deltas_insert AFTER INSERT ON library
|
|
-- BEGIN
|
|
-- INSERT INTO changed_fids(fid, change) VALUES(NEW.fid, 1);
|
|
-- END;
|
|
|
|
|
|
-- NOTE: Value for column 'change' must correspond to enum value NCoMMCo::FILECHANGETYPE_UPDATE
|
|
--CREATE TRIGGER library_deltas_change AFTER UPDATE OF artist_id, album_id,genre_id, title ON library
|
|
-- BEGIN
|
|
-- INSERT INTO changed_fids(fid, change) VALUES(NEW.fid, 2);
|
|
-- END;
|
|
|
|
|
|
-- NOTE: Value for column 'change' must correspond to enum value NCoMMCo::FILECHANGETYPE_DELETE
|
|
--CREATE TRIGGER library_deltas_delete AFTER DELETE ON library
|
|
-- BEGIN
|
|
-- INSERT INTO changed_fids(fid, change) VALUES(OLD.fid, 4);
|
|
-- END;
|
|
|
|
-- Reset medium content values after sync
|
|
CREATE TRIGGER tr_mediastores_type AFTER UPDATE OF syncflags ON mediastores WHEN ((NEW.syncflags = 1) OR ((NEW.syncflags & 4)=4))
|
|
BEGIN
|
|
UPDATE mediastores SET medium_content=0,content_type=0 WHERE msid=NEW.msid;
|
|
INSERT INTO mediastores_type_custom SELECT DISTINCT msid,ftype,format FROM library WHERE msid=NEW.msid AND ftype!=5;
|
|
DELETE FROM mediastores_type_custom;
|
|
END;
|
|
|
|
-- Reset medium content values after resync
|
|
CREATE TRIGGER tr_mediastores_type_lastsync AFTER UPDATE OF last_sync ON mediastores
|
|
BEGIN
|
|
UPDATE mediastores SET medium_content=0,content_type=0 WHERE msid=NEW.msid;
|
|
INSERT INTO mediastores_type_custom SELECT DISTINCT msid,ftype,format FROM library WHERE msid=NEW.msid AND ftype!=5;
|
|
DELETE FROM mediastores_type_custom;
|
|
END;
|
|
|
|
-- Trigger update of medium content values after dynamic content type change in library table
|
|
CREATE TRIGGER tr_library_ftype AFTER UPDATE OF ftype ON library WHEN OLD.ftype IS NOT NEW.ftype
|
|
BEGIN
|
|
UPDATE mediastores SET medium_content=0,content_type=0 WHERE msid=NEW.msid;
|
|
INSERT INTO mediastores_type_custom SELECT DISTINCT msid,ftype,format FROM library WHERE msid=NEW.msid AND ftype!=5;
|
|
DELETE FROM mediastores_type_custom;
|
|
END;
|
|
|
|
-- Derive medium content values from sync result
|
|
-- deriveContentTypeMediastores may not be overwritten with new integration.
|
|
CREATE TRIGGER tr_mediastores_type_custom AFTER INSERT ON mediastores_type_custom
|
|
BEGIN
|
|
UPDATE mediastores SET medium_content=deriveContentTypeMediastores(NEW.ftype,300000)|(SELECT medium_content FROM mediastores WHERE msid=NEW.msid), content_type=(NEW.format & 5)|(SELECT content_type from mediastores where msid=NEW.msid) where msid=NEW.msid;
|
|
UPDATE mediastores SET medium_content = CASE WHEN (select count(plid) from playlists where msid=NEW.msid)!=0 THEN medium_content|8 ELSE medium_content END WHERE msid=NEW.msid;
|
|
END;
|
|
|
|
-- Trigger update of medium content values after copied files in library table
|
|
CREATE TRIGGER tr_library_copiedfid AFTER UPDATE OF copied_fid ON library WHEN NEW.copied_fid!=0
|
|
BEGIN
|
|
UPDATE mediastores SET medium_content=0,content_type=0 WHERE msid=(select msid from library where fid=NEW.copied_fid);
|
|
INSERT INTO mediastores_type_custom SELECT DISTINCT msid,ftype,format FROM library WHERE msid=(select msid from library where fid=NEW.copied_fid) AND ftype!=5;
|
|
DELETE FROM mediastores_type_custom;
|
|
END;
|
|
|
|
COMMIT;
|