sql_creation.txt

CREATE TABLE `CLEANUPS` (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `DATE`    TEXT,
    `DELETED_IMAGES`    INTEGER
);

CREATE TABLE `DEVICES` (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `NAME`    TEXT
);

CREATE TABLE `IMAGES` (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `DESCRIPTION`    INTEGER,
    `ENABLED`    INTEGER DEFAULT 1,
    `DATE_START`    TEXT,
    `DATE_END`    TEXT,
    `INFINITE`    INTEGER DEFAULT 0,
    `FILENAME`    TEXT,
    `MARKED_FOR_DELETE`    INTEGER DEFAULT 0,
    `DELETE_DATE`    TEXT
);

CREATE TABLE `IMAGES_TO_DEVICES` (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `IMAGE_ID`    INTEGER,
    `DEVICE_ID`    INTEGER
);

CREATE TRIGGER DELETE_ACTIONS_AFTER_DEVICE_DELETE AFTER DELETE ON DEVICES
    begin
      DELETE FROM IMAGES_TO_DEVICES WHERE DEVICE_ID=old.ID;
    end;
    
CREATE TRIGGER DELETE_ACTIONS_AFTER_IMAGE_DELETE AFTER DELETE ON IMAGES
    begin
      DELETE FROM IMAGES_TO_DEVICES WHERE IMAGE_ID=old.ID;
    end;   
    
CREATE TRIGGER INSERT_DELETE_ON_MARK_TO_DELETE AFTER UPDATE OF MARKED_FOR_DELETE ON IMAGES
WHEN new.MARKED_FOR_DELETE=1
    begin
      UPDATE IMAGES SET DELETE_DATE=date('now') WHERE ID=old.ID;
      DELETE FROM IMAGES_TO_DEVICES WHERE IMAGE_ID=old.ID;
    end;    
    
CREATE TRIGGER REMOVE_DELETE_ON_UNMARK_TO_DELETE AFTER UPDATE OF MARKED_FOR_DELETE ON IMAGES
WHEN new.MARKED_FOR_DELETE=0
    begin
      UPDATE IMAGES SET DELETE_DATE=null WHERE ID=old.ID;
    end;