563 lines
28 KiB
PL/PgSQL
563 lines
28 KiB
PL/PgSQL
BEGIN;
|
|
--
|
|
-- 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.
|
|
--
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- The QDB information for this database
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE _qdb_info_custom_ (
|
|
version INTEGER NOT NULL
|
|
);
|
|
|
|
INSERT INTO _qdb_info_custom_(version) VALUES(3006);
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table folders_custom
|
|
-- Holds a display name for each folder.
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
|
|
CREATE TABLE fbmcontent_custom(
|
|
fbmuserid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
fbm INTEGER,
|
|
userid INTEGER,
|
|
fid INTEGER,
|
|
folderid INTEGER,
|
|
idtype INTEGER,
|
|
objectid BLOB,
|
|
textcontent TEXT,
|
|
unique (fbm, userid)
|
|
);
|
|
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 0);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 0);
|
|
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 1);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 1);
|
|
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 2);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 2);
|
|
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 3);
|
|
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 3);
|
|
|
|
CREATE TABLE actplaylist_oe (
|
|
genitemid integer DEFAULT 0 NOT NULL,
|
|
instid integer DEFAULT 0,
|
|
sessionid integer DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE favourites_custom (
|
|
fid integer REFERENCES library NOT NULL,
|
|
msid integer REFERENCES mediastores NOT NULL,
|
|
userid integer DEFAULT 0,
|
|
favourite integer DEFAULT 0,
|
|
unique (fid, msid, userid, favourite)
|
|
);
|
|
|
|
CREATE TABLE bookmarks_custom (
|
|
bookmarkid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
msid integer REFERENCES mediastores,
|
|
Userid integer,
|
|
fid integer REFERENCES library,
|
|
time integer,
|
|
type integer,
|
|
unique (msid, Userid,fid)
|
|
);
|
|
|
|
DROP TABLE IF EXISTS folders_custom;
|
|
|
|
CREATE TABLE folders_custom(
|
|
folderid INTEGER PRIMARY KEY,
|
|
basepath TEXT DEFAULT '',
|
|
displayname TEXT,
|
|
date_added INTEGER DEFAULT 0 NOT NULL,
|
|
source INTEGER DEFAULT 0 NOT NULL,
|
|
toc TEXT,
|
|
contenttype INTEGER DEFAULT 0 NOT NULL,
|
|
toplevelfolderid INTEGER DEFAULT 0 NOT NULL
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE Accounts
|
|
-- remarks: account is unique by providerid+loginname
|
|
-- creates mapping between internal profileid and unique account identifier
|
|
-- *******************************************************************************
|
|
CREATE TABLE profiles(
|
|
profileid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
loginname TEXT DEFAULT NULL,
|
|
providerid INTEGER REFERENCES providers,
|
|
unique (loginname,providerid)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE account in-dependent channel info
|
|
-- remarks: channel is globally unique by channelid + providerid
|
|
-- account dependent channel info (user_channels custom table) is currently not needed
|
|
-- *******************************************************************************
|
|
CREATE TABLE channels(
|
|
genchannelid INTEGER PRIMARY KEY AUTOINCREMENT, --generated channelid
|
|
channelid TEXT DEFAULT NULL,
|
|
providerid INTEGER DEFAULT 0 NOT NULL, --is required since channel is globally unique by itemid + providerid
|
|
type INTEGER DEFAULT 0 NOT NULL,
|
|
coverlargeURI TEXT, --download URL/local path
|
|
covermediumURI TEXT, --download URL/local path
|
|
coversmallURI TEXT, --download URL/local path
|
|
coverlargesize INTEGER DEFAULT 0 NOT NULL,
|
|
covermediumsize INTEGER DEFAULT 0 NOT NULL,
|
|
coversmallsize INTEGER DEFAULT 0 NOT NULL,
|
|
name TEXT DEFAULT NULL,
|
|
artistname TEXT DEFAULT NULL,
|
|
albumname TEXT DEFAULT NULL,
|
|
amountforplayback INTEGER DEFAULT 0 NOT NULL,
|
|
version INTEGER DEFAULT 0 NOT NULL,
|
|
unique (channelid,providerid)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE account dependent channel info: defines which channels a user has
|
|
-- *******************************************************************************
|
|
CREATE TABLE user_channels(
|
|
profileid INTEGER DEFAULT 0 NOT NULL,
|
|
genchannelid INTEGER DEFAULT 0 NOT NULL,
|
|
visibility INTEGER DEFAULT 0 NOT NULL,
|
|
unique (profileid,genchannelid)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE account in-dependent channel tracklists
|
|
-- (the channel window will be handled by the App, therefore it can be shared between all users)
|
|
-- *******************************************************************************
|
|
CREATE TABLE items(
|
|
genitemid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
itemid TEXT DEFAULT NULL,
|
|
providerid INTEGER DEFAULT 0 NOT NULL, --download, delete
|
|
status INTEGER DEFAULT 0 NOT NULL, --maps to DMMNBTAuxiliaryInputTypes::eItemStatus subset (eItemStatus_Available or eItemStatus_Undefined)
|
|
filename TEXT DEFAULT NULL,
|
|
size INTEGER DEFAULT 0 NOT NULL,
|
|
gencollectionid INTEGER DEFAULT 0 NOT NULL,
|
|
unique (itemid,providerid)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE account in-dependent channels' items references
|
|
-- *******************************************************************************
|
|
CREATE TABLE channel_items(
|
|
genchannelid INTEGER DEFAULT 0 NOT NULL,
|
|
genitemid INTEGER DEFAULT 0 NOT NULL,
|
|
unique (genchannelid, genitemid)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE browser app cache
|
|
-- *******************************************************************************
|
|
CREATE TABLE oe_appcache(
|
|
genkeyid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL,
|
|
size INTEGER DEFAULT 0 NOT NULL,
|
|
unique (key)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE cover cache
|
|
-- *******************************************************************************
|
|
CREATE TABLE oe_covercache(
|
|
coverid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
file TEXT NOT NULL,
|
|
size INTEGER DEFAULT 0 NOT NULL,
|
|
unique (file)
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- * OE CollectionID table
|
|
-- *******************************************************************************
|
|
CREATE TABLE oe_collectionids(
|
|
gencollectionid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
collectionid TEXT NOT NULL,
|
|
providerid INTEGER DEFAULT 0 NOT NULL,
|
|
coverlargeid INTEGER DEFAULT 0 NOT NULL,
|
|
covermediumid INTEGER DEFAULT 0 NOT NULL,
|
|
coversmallid INTEGER DEFAULT 0 NOT NULL,
|
|
unique (collectionid, providerid)
|
|
);
|
|
|
|
CREATE TABLE playlists_custom(
|
|
plid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
type INTEGER DEFAULT 0 NOT NULL,
|
|
date_added INTEGER DEFAULT 0 NOT NULL,
|
|
profileid INTEGER REFERENCES profiles,
|
|
date_modified INTEGER DEFAULT 0 NOT NULL,
|
|
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores
|
|
);
|
|
|
|
CREATE TRIGGER playlists_custom_insert AFTER INSERT ON playlists_custom
|
|
BEGIN
|
|
UPDATE playlists_custom SET date_modified=strftime('%s','now') WHERE plid=NEW.plid;
|
|
END;
|
|
|
|
CREATE TABLE playlistdata_custom(
|
|
plid INTEGER NOT NULL REFERENCES playlists,
|
|
fid INTEGER NOT NULL REFERENCES library,
|
|
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores
|
|
);
|
|
|
|
CREATE TABLE software_info (
|
|
version TEXT
|
|
);
|
|
|
|
INSERT INTO software_info(version) VALUES('not known');
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table categorysyncs_custom
|
|
-- This 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_custom (
|
|
jobId INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
syncId INTEGER NOT NULL,
|
|
msid INTEGER NOT NULL,
|
|
category TEXT DEFAULT '',
|
|
basepath TEXT DEFAULT ''
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table categorydata_custom
|
|
-- This 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_custom (
|
|
uniqueId INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
jobId INTEGER REFERENCES categorysyncs_custom,
|
|
folderIdx INTEGER NOT NULL,
|
|
name TEXT DEFAULT '',
|
|
name_normal TEXT DEFAULT NULL,
|
|
msid INTEGER DEFAULT 0 NOT NULL,
|
|
name_asia TEXT DEFAULT NULL
|
|
|
|
);
|
|
|
|
CREATE TABLE metadataselection(
|
|
tocid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
toc TEXT,
|
|
folderid INTEGER,
|
|
folderMetaDataID TEXT,
|
|
folderOrigin INTEGER,
|
|
mediumid INTEGER,
|
|
mediumMetaDataID TEXT,
|
|
mediumOrigin INTEGER,
|
|
unique (toc)
|
|
);
|
|
|
|
CREATE TABLE odl_album(
|
|
albumid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
tocid INTEGER REFERENCES metadataselection,
|
|
metaDataID TEXT,
|
|
artist TEXT,
|
|
name TEXT,
|
|
unique (tocid,metaDataID)
|
|
);
|
|
|
|
CREATE TABLE odl_track(
|
|
trackid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
albumid INTEGER REFERENCES odl_album,
|
|
artist TEXT,
|
|
title TEXT,
|
|
genre TEXT
|
|
);
|
|
|
|
CREATE TABLE pendingtags(
|
|
ambigioustag INTEGER,
|
|
buttonpressed INTEGER,
|
|
name TEXT,
|
|
artist TEXT,
|
|
album TEXT,
|
|
genre TEXT,
|
|
itunessongid INTEGER NOT NULL,
|
|
itunesstorefrontit INTEGER NOT NULL,
|
|
stationfrequency TEXT,
|
|
staticcallletters TEXT,
|
|
timestamp TEXT,
|
|
podcastfeedurl INTEGER,
|
|
stationurl TEXT,
|
|
programnumber INTEGER,
|
|
itunesaffiliateid TEXT,
|
|
itunesstationid TEXT,
|
|
unknowndata TEXT
|
|
);
|
|
|
|
CREATE TABLE features(
|
|
feature TEXT,
|
|
available INTEGER
|
|
);
|
|
|
|
CREATE TABLE usbdevicedetails(
|
|
deviceserialno TEXT,
|
|
msid INTEGER,
|
|
lastseen INTEGER
|
|
);
|
|
|
|
INSERT INTO features(feature, available) VALUES('fep', 0);
|
|
|
|
-- *******************************************************************************
|
|
-- * Coverflow Custom Table
|
|
-- *******************************************************************************
|
|
CREATE TABLE album_cover(
|
|
coverid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
album_id INTEGER REFERENCES library_albums,
|
|
album_artist_id INTEGER DEFAULT 1 NOT NULL,
|
|
artist_id INTEGER DEFAULT 1 NOT NULL,
|
|
msid INTEGER DEFAULT 0 NOT NULL,
|
|
fid INTEGER DEFAULT 0 NOT NULL,
|
|
state INTEGER DEFAULT 0 NOT NULL,
|
|
unique (album_id, album_artist_id, artist_id, msid)
|
|
);
|
|
|
|
-- Fid Deletion Trigger
|
|
CREATE TRIGGER album_fid_delete AFTER DELETE ON library
|
|
BEGIN
|
|
DELETE FROM album_cover WHERE fid=OLD.fid;
|
|
END;
|
|
|
|
CREATE TRIGGER album_image_prune AFTER DELETE ON mdi_image_cache
|
|
BEGIN
|
|
UPDATE album_cover SET state=0 WHERE fid=OLD.fid AND OLD.profile_index=-1;
|
|
END;
|
|
|
|
-- *****************
|
|
-- Optional TRIGGERS
|
|
-- *****************
|
|
-- Folders custom
|
|
CREATE TRIGGER folders_custom_insert AFTER INSERT ON folders
|
|
BEGIN
|
|
INSERT INTO folders_custom(folderid, basepath, date_added) VALUES(NEW.folderid, replace((SELECT basepath FROM folders WHERE folderid=NEW.folderid), '/', x'01'), ((strftime('%s','now')+30)/60)*60);
|
|
UPDATE folders_custom SET toplevelfolderid =
|
|
CASE WHEN ( select ((select length(basepath) from folders where folderid=(select parentid from folders where folderid=NEW.folderid)) > 1) )
|
|
THEN (select toplevelfolderid from folders_custom where folderid = (select parentid from folders where folderid=NEW.folderid))
|
|
ELSE (select NEW.folderid)
|
|
END WHERE folderid=NEW.folderid;
|
|
|
|
|
|
|
|
END;
|
|
|
|
CREATE TRIGGER folders_update_contenttype AFTER UPDATE OF contenttype ON library
|
|
BEGIN
|
|
UPDATE folders_custom SET contenttype = (contenttype | NEW.contenttype) where (folderid = NEW.folderid) OR (folderid = (select toplevelfolderid from folders_custom where folders_custom.folderid = NEW.folderid));
|
|
END;
|
|
|
|
|
|
CREATE TRIGGER folders_custom_delete DELETE ON folders
|
|
BEGIN
|
|
DELETE FROM folders_custom WHERE folderid=OLD.folderid;
|
|
UPDATE fbmcontent_custom SET fid=0, folderid=0, idtype=0, objectid=null, textcontent=null WHERE folderid=OLD.folderid;
|
|
END;
|
|
|
|
|
|
CREATE TRIGGER folders_custom_update_displayname UPDATE OF displayname ON folders_custom
|
|
BEGIN
|
|
UPDATE folders_custom SET basepath = (SELECT basepath FROM folders_custom WHERE folderid = (SELECT parentid FROM folders WHERE folderid = new.folderid))
|
|
|| new.displayname
|
|
|| SUBSTR(basepath, (SELECT LENGTH(old.basepath) FROM folders_custom WHERE folderid = new.folderid),1000000)
|
|
WHERE folderid IN (SELECT folderid FROM folders WHERE (msid = (SELECT msid FROM folders WHERE folderid=new.folderid)) AND (SUBSTR(basepath,1,(SELECT LENGTH(basepath) FROM folders WHERE folderid = new.folderid)) == (SELECT basepath FROM folders WHERE folderid = new.folderid)))
|
|
;
|
|
END;
|
|
|
|
-- playlistdata_custom
|
|
CREATE TRIGGER playlistdata_custom_delete DELETE ON library
|
|
BEGIN
|
|
DELETE FROM playlistdata_custom WHERE fid=OLD.fid;
|
|
UPDATE fbmcontent_custom SET fid=0, folderid=0, idtype=0, objectid=null, textcontent=null WHERE fid=OLD.fid;
|
|
END;
|
|
|
|
CREATE INDEX library_index_msid ON library(msid);
|
|
CREATE INDEX playlistdata_fid_index ON playlistdata(fid);
|
|
CREATE INDEX folders_indexbasepath ON folders(basepath);
|
|
CREATE INDEX mdi_image_cache_index_fid on mdi_image_cache(fid);
|
|
|
|
--custom table cleanup upon MME pruning
|
|
CREATE TRIGGER customtable_trigger_delete DELETE ON mediastores
|
|
BEGIN
|
|
-- DELETE FROM categorydata_custom WHERE jobId IN (SELECT jobId FROM categorysyncs_custom WHERE msid=OLD.msid);
|
|
DELETE FROM categorydata_custom WHERE msid=OLD.msid;
|
|
DELETE FROM categorysyncs_custom WHERE msid=OLD.msid;
|
|
DELETE FROM usbdevicedetails WHERE msid=OLD.msid;
|
|
DELETE FROM album_cover WHERE msid=OLD.msid;
|
|
DELETE FROM favourites_custom WHERE msid=OLD.msid;
|
|
DELETE FROM playlists_custom WHERE msid=OLD.msid;
|
|
DELETE FROM playlistdata_custom WHERE msid=OLD.msid;
|
|
END;
|
|
|
|
|
|
-- odl_album
|
|
CREATE TRIGGER odl_album_delete DELETE ON metadataselection
|
|
BEGIN
|
|
DELETE FROM odl_album WHERE tocid=OLD.tocid;
|
|
END;
|
|
|
|
-- odl_track
|
|
CREATE TRIGGER odl_track DELETE ON odl_album
|
|
BEGIN
|
|
DELETE FROM odl_track WHERE albumid=OLD.albumid;
|
|
END;
|
|
|
|
-- set ext_image flag in mdi_image_cache in order to be able to remove(unlink) MME covers as well from the cache
|
|
CREATE TRIGGER mdi_image_cache_update AFTER INSERT ON mdi_image_cache
|
|
BEGIN
|
|
UPDATE mdi_image_cache SET ext_image=1 WHERE insertion_sequence=NEW.insertion_sequence;
|
|
END;
|
|
|
|
-- delete/update metadataselection ETS entries
|
|
CREATE TRIGGER metadataselection_ETS_delete DELETE ON folders
|
|
BEGIN
|
|
DELETE FROM metadataselection WHERE folderid=OLD.folderid AND mediumMetaDataID ISNULL;
|
|
UPDATE metadataselection SET folderid=NULL, folderMetaDataID=NULL, folderOrigin=NULL WHERE folderid=OLD.folderid; --remove selection for remaining entries that could not be deleted
|
|
END;
|
|
|
|
-- delete/update metadataselection CDA entries
|
|
CREATE TRIGGER metadataselection_CDA_delete DELETE ON mediastores
|
|
BEGIN
|
|
DELETE FROM metadataselection WHERE mediumid=OLD.msid AND folderMetaDataID ISNULL;
|
|
UPDATE metadataselection SET mediumid=NULL, mediumMetaDataID=NULL, mediumOrigin=NULL WHERE mediumid=OLD.msid; --remove selection for remaining entries that could not be deleted
|
|
END;
|
|
|
|
CREATE TRIGGER channel_items_delete DELETE ON items
|
|
BEGIN
|
|
DELETE FROM channel_items WHERE genitemid=OLD.genitemid;
|
|
END;
|
|
|
|
CREATE TRIGGER user_channels_delete DELETE ON channels
|
|
BEGIN
|
|
DELETE FROM user_channels WHERE genchannelid=OLD.genchannelid;
|
|
END;
|
|
|
|
CREATE TRIGGER categorydata_custom_insert AFTER INSERT ON categorydata_custom WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE categorydata_custom SET name_asia=convertStringToAsiaString(NEW.name, (select available from features where feature='fep')), name_normal=normalizeString(NEW.name, (select available from features where feature='fep'), 1) WHERE uniqueId=NEW.uniqueId;
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS mediastore_update_pcslotid AFTER UPDATE ON mediastores WHEN NEW.slotid != 0
|
|
BEGIN
|
|
UPDATE mediastores SET pcslotid = NEW.slotid WHERE rowid=NEW.rowid;
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS mediastore_insert_pcslotid AFTER INSERT ON mediastores WHEN NEW.slotid != 0
|
|
BEGIN
|
|
UPDATE mediastores SET pcslotid = NEW.slotid WHERE rowid=NEW.rowid;
|
|
END;
|
|
|
|
-- Chinese to asia trigger
|
|
|
|
CREATE TRIGGER library_insert_asia AFTER INSERT ON library WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library SET title_asia=convertStringToAsiaString(NEW.title, (select available from features where feature='fep')), title_normal=normalizeString(NEW.title, (select available from features where feature='fep'), 1) WHERE fid=NEW.fid;
|
|
END;
|
|
|
|
CREATE TRIGGER library_update_asia AFTER UPDATE OF title ON library WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library SET title_asia=convertStringToAsiaString(NEW.title, (select available from features where feature='fep')), title_normal=normalizeString(NEW.title, (select available from features where feature='fep'), 1) WHERE fid=NEW.fid;
|
|
END;
|
|
|
|
CREATE TRIGGER library_genres_insert_asia AFTER INSERT ON library_genres WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library_genres SET genre_asia=convertStringToAsiaString(NEW.genre, (select available from features where feature='fep')), genre_normal=normalizeString(NEW.genre, (select available from features where feature='fep'), 1) WHERE genre_id=NEW.genre_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_artists_insert_asia AFTER INSERT ON library_artists WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library_artists SET artist_asia=convertStringToAsiaString(NEW.artist, (select available from features where feature='fep')), artist_normal=normalizeString(NEW.artist, (select available from features where feature='fep'), 1) WHERE artist_id=NEW.artist_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_albums_insert_asia AFTER INSERT ON library_albums WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library_albums SET album_asia=convertStringToAsiaString(NEW.album, (select available from features where feature='fep')), album_normal=normalizeString(NEW.album, (select available from features where feature='fep'), 1) WHERE album_id=NEW.album_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_composers_insert_asia AFTER INSERT ON library_composers WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
|
|
BEGIN
|
|
UPDATE library_composers SET composer_asia=convertStringToAsiaString(NEW.composer, (select available from features where feature='fep')), composer_normal=normalizeString(NEW.composer, (select available from features where feature='fep'), 1) WHERE composer_id=NEW.composer_id;
|
|
END;
|
|
|
|
CREATE TRIGGER playlistdata_delete DELETE ON library
|
|
BEGIN
|
|
DELETE FROM playlistdata WHERE fid=OLD.fid;
|
|
END;
|
|
|
|
CREATE TRIGGER library_update_yomi_artist AFTER UPDATE OF artist_secret_id ON library WHEN ((select available from features where feature='fep')=3)
|
|
BEGIN
|
|
UPDATE library_artists SET artist_asia=(select case when artist_secret != '' AND artist_secret IS NOT NULL then normalizeString(artist_secret, 3, 0) else normalizeString(artist, 3, 0) end from (select artist_secret, artist from library_artists_secret, library_artists where artist_secret_id=NEW.artist_secret_id and artist_id=NEW.artist_id)) where artist_id=NEW.artist_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_update_yomi_album AFTER UPDATE OF album_secret_id ON library WHEN ((select available from features where feature='fep')=3)
|
|
BEGIN
|
|
UPDATE library_albums SET album_asia=(select case when album_secret != '' AND album_secret IS NOT NULL then normalizeString(album_secret, 3, 0) else normalizeString(album, 3, 0) end from (select album_secret, album from library_albums_secret, library_albums where album_secret_id=NEW.album_secret_id and album_id=NEW.album_id)) where album_id=NEW.album_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_update_yomi_title AFTER UPDATE OF title_secret ON library WHEN ((select available from features where feature='fep')=3)
|
|
BEGIN
|
|
UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end) WHERE fid=NEW.fid;
|
|
END;
|
|
|
|
CREATE TRIGGER library_insert_yomi AFTER INSERT ON library WHEN ((select available from features where feature='fep')=3)
|
|
BEGIN
|
|
UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end), title_normal=normalizeString(NEW.title, 3, 1) WHERE fid=NEW.fid;
|
|
UPDATE library_artists SET artist_asia=(select case when artist_secret != '' AND artist_secret IS NOT NULL then normalizeString(artist_secret, 3, 0) else normalizeString(artist, 3, 0) end from (select artist_secret, artist from library_artists_secret, library_artists where artist_secret_id=NEW.artist_secret_id and artist_id=NEW.artist_id)) where artist_id=NEW.artist_id;
|
|
UPDATE library_albums SET album_asia=(select case when album_secret != '' AND album_secret IS NOT NULL then normalizeString(album_secret, 3, 0) else normalizeString(album, 3, 0) end from (select album_secret, album from library_albums_secret, library_albums where album_secret_id=NEW.album_secret_id and album_id=NEW.album_id)) where album_id=NEW.album_id;
|
|
END;
|
|
|
|
CREATE TRIGGER library_update_yomi AFTER UPDATE OF title ON library WHEN ((select available from features where feature='fep')=3)
|
|
BEGIN
|
|
UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end), title_normal=normalizeString(NEW.title, 3, 1) WHERE fid=NEW.fid;
|
|
END;
|
|
|
|
COMMIT;
|