
/* create supplemental (tHarmony) table */
CREATE TABLE IF NOT EXISTS "tHarmony" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE , "fKey" TEXT, "fValue" BLOB); /* table structure */

INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (1,'GUID','DC1E300C-E7F1-4EF9-B5BF-40EDB6DC56C6'); /* unique Id in TEXT (STRING) form for identification the modified db file */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (2,'RecentFilenameText','fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ž_Repeat.ts'); /* full name of Recent File in TEXT form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (3,'RecentFilenameBLOB',X'66696C657365743A2F2F2F746D702F72616D66732F766F6C756D65732F484444202820496E7465726E616C20292F5245432FC5BE5F5265706561742E7473'); /* full name od Recent File in BLOB form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (4,'RecentFilenameNumber','9999'); /* the number of Recent File in db records */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (5,'AutoRecoveryEnable',1); /* 1 - enable Auto Recovery of Recent File db records */ 
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (6,'AutoRecoveryRaiseTrigger',zeroblob(16)); /* used for raise the recovery trigger by changing a value to a new value */

/* tParameter table "AFTER UPDATE" triggers */
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE '_m' OR old.fKey LIKE '__m' OR old.fKey LIKE '___m'  BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText')) - 2; END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE '_m' OR new.fKey LIKE '__m' OR new.fKey LIKE '___m' BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText')) - 2; END;

/* tParameter table "BEFORE UPDATE" triggers (raise tHarmony table trigger by updating tHarmony = setting the random value into it */
CREATE TRIGGER IF NOT EXISTS "main"."AutoRecoveryByUPDATE" BEFORE UPDATE ON tParameter BEGIN UPDATE tHarmony SET fValue = randomblob(16) WHERE fKey = 'AutoRecoveryRaiseTrigger'; END;
CREATE TRIGGER IF NOT EXISTS "main"."AutoRecoveryByINSERT" BEFORE INSERT ON tParameter BEGIN UPDATE tHarmony SET fValue = randomblob(16) WHERE fKey = 'AutoRecoveryRaiseTrigger'; END;
CREATE TRIGGER IF NOT EXISTS "main"."AutoRecoveryByDELETE" BEFORE DELETE ON tParameter BEGIN UPDATE tHarmony SET fValue = randomblob(16) WHERE fKey = 'AutoRecoveryRaiseTrigger'; END;

/* tHarmony table trigger (Auto Recovery handling) */
CREATE TRIGGER IF NOT EXISTS "main"."AutoRecoveryHandle" AFTER UPDATE ON tHarmony WHEN (CASE WHEN (((SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm') + 1 = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) AND ((SELECT fValue FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k') = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB')) AND ((SELECT fKey FROM tParameter WHERE id = (SELECT id FROM tParameter WHERE fKey = ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) + 1) = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'))) THEN 0 ELSE (SELECT fValue FROM tHarmony WHERE fKey = 'AutoRecoveryEnable') END) BEGIN

/* try to delete any previous db records regarding the Recent File in ANY way */
DELETE FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm';
DELETE FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k';
DELETE FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText');
DELETE FROM tParameter WHERE fValue = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB');

/* create new db records for Recent File */
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm',zeroblob(4));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k',(SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB'));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'),zeroblob(16));

/* abandon all subsequent "AFTER UPDATE" triggers */
SELECT RAISE(IGNORE);

END;

/* force to raise AutoRecoveryHandle trigger to create initial db records for Recent File */
UPDATE tHarmony SET fValue = randomblob(16) WHERE fKey = 'AutoRecoveryRaiseTrigger';

