-- vim: nowrap
-- $HeadURL$ $LastChangedRevision$

-- database archicture revision
PRAGMA user_version = 6;
-- detect foreign key problems
PRAGMA foreign_keys=ON;

-- base type tables
CREATE TABLE logicals (
	    -- columns
	    logical CHAR, 
	    -- clauses
	    PRIMARY KEY (logical), 
	    CHECK(logical = 'true' OR logical = 'false')
	);
INSERT INTO logicals VALUES ('true');
INSERT INTO logicals VALUES ('false');

CREATE TABLE medium_types (
	    -- columns
	    medium_type_id CHAR,
	    -- clauses
            PRIMARY KEY (medium_type_id)
	);
INSERT INTO medium_types VALUES ('floating-dir');
INSERT INTO medium_types VALUES ('floating-tape');

CREATE TABLE dle_types (
	    -- columns
	    dle_type_id CHAR,
	    -- clauses
            PRIMARY KEY (dle_type_id)
	);
INSERT INTO dle_types VALUES ('dir');
INSERT INTO dle_types VALUES ('dir-one-fs');

-- I wanted to create host_types and then views called client_types
-- and server_types but views cannot be referenced by foreign keys
-- so I must just duplicate.
CREATE TABLE client_types (
	    -- columns
	    client_type_id CHAR,
	    -- clauses
            PRIMARY KEY (client_type_id)
	);
INSERT INTO client_types VALUES ('Linux');
INSERT INTO client_types VALUES ('Windows');

CREATE TABLE server_types (
	    -- columns
	    server_type_id CHAR,
	    -- clauses
            PRIMARY KEY (server_type_id)
	);
INSERT INTO server_types VALUES ('Linux');
INSERT INTO server_types VALUES ('Windows');

-- method tables
CREATE TABLE backup_methods (
	    -- columns
            backup_method_id CHAR,
            enabled CHAR NOT NULL,
            increment_support CHAR NOT NULL,
            backup_method_cmd CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (backup_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (increment_support) REFERENCES logicals(logical),
            UNIQUE (backup_method_cmd)
        );
INSERT INTO backup_methods VALUES ('rsync', 'true','false','Linux/Linux/backups/rsync');
INSERT INTO backup_methods VALUES ('rsync-one-fs', 'true','false','Linux/Linux/backups/rsync-one-fs');
INSERT INTO backup_methods VALUES ('rdiff-backup', 'true','false','Linux/Linux/backups/rdiff-backup');
INSERT INTO backup_methods VALUES ('rdiff-backup-one-fs', 'true','false','Linux/Linux/backups/rdiff-backup-one-fs');
INSERT INTO backup_methods VALUES ('unison', 'true', 'false', 'Linux/Linux/backups/unison');
INSERT INTO backup_methods VALUES ('unison-one-fs', 'true','false','Linux/Linux/backups/unison-one-fs');
INSERT INTO backup_methods VALUES ('noop', 'true','false','Linux/Linux/backups/noop');
INSERT INTO backup_methods VALUES ('rsync-over-btrfs', 'true', 'false', 'Linux/Linux/backups/rsync-over-btrfs');
INSERT INTO backup_methods VALUES ('rsync-over-btrfs-one-fs', 'true', 'false', 'Linux/Linux/backups/rsync-over-btrfs-one-fs');

CREATE TABLE backup_method_grants (
	    -- columns
	    backup_method_grant_id INTEGER,
            backup_method_id CHAR,
            server_type_id CHAR,
            client_type_id CHAR,
            medium_type_id CHAR,
            dle_type_id CHAR,
	    -- clauses
            PRIMARY KEY (backup_method_grant_id),
            FOREIGN KEY (backup_method_id) REFERENCES backup_methods(backup_method_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id),
            FOREIGN KEY (client_type_id) REFERENCES client_types(client_type_id),
            FOREIGN KEY (medium_type_id) REFERENCES medium_types(medium_type_id),
            FOREIGN KEY (dle_type_id) REFERENCES dle_types(dle_type_id)
	);
INSERT INTO backup_method_grants VALUES (NULL, 'rsync', 'Linux', 'Linux', 'floating-dir', 'dir');
INSERT INTO backup_method_grants VALUES (NULL, 'rsync-one-fs', 'Linux', 'Linux', 'floating-dir', 'dir-one-fs');
INSERT INTO backup_method_grants VALUES (NULL, 'rdiff-backup', 'Linux', 'Linux', 'floating-dir', 'dir');
INSERT INTO backup_method_grants VALUES (NULL, 'rdiff-backup-one-fs', 'Linux', 'Linux', 'floating-dir', 'dir-one-fs');
INSERT INTO backup_method_grants VALUES (NULL, 'unison', 'Linux', 'Linux', 'floating-dir', 'dir');
INSERT INTO backup_method_grants VALUES (NULL, 'unison-one-fs', 'Linux', 'Linux', 'floating-dir', 'dir-one-fs');
INSERT INTO backup_method_grants VALUES (NULL, 'noop', 'Linux', NULL, NULL, NULL);
INSERT INTO backup_method_grants VALUES (NULL,'rsync-over-btrfs','Linux','Linux','floating-dir','dir');
INSERT INTO backup_method_grants VALUES (NULL,'rsync-over-btrfs-one-fs','Linux','Linux','floating-dir','dir-one-fs');

CREATE TABLE medium_labelling_methods (
	    -- columns
	    medium_labelling_method_id CHAR,
            enabled CHAR NOT NULL,
            medium_labelling_method_cmd CHAR NOT NULL,
	    -- clauses
	    PRIMARY KEY (medium_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE(medium_labelling_method_cmd)
	);
INSERT INTO medium_labelling_methods VALUES ('df-in-map', 'true','Linux/medium-labellers/df-in-map');
INSERT INTO medium_labelling_methods VALUES ('ff-on-tape', 'true','Linux/medium-labellers/ff-on-tape');
INSERT INTO medium_labelling_methods VALUES ('sl-on-m', 'true','Linux/medium-labellers/sl-on-m');

CREATE TABLE medium_labelling_method_grants (
	    -- columns
	    medium_labelling_method_grant_id INTEGER,
            medium_labelling_method_id CHAR,
            server_type_id CHAR,
            medium_type_id CHAR,
	    -- clauses
            PRIMARY KEY (medium_labelling_method_grant_id),
            FOREIGN KEY (medium_labelling_method_id) REFERENCES medium_labelling_methods(medium_labelling_method_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id),
            FOREIGN KEY (medium_type_id) REFERENCES medium_types(medium_type_id)
	);
INSERT INTO medium_labelling_method_grants VALUES (NULL, 'df-in-map', 'Linux', 'floating-dir');
INSERT INTO medium_labelling_method_grants VALUES (NULL, 'ff-on-tape', 'Linux', 'floating-tape');
INSERT INTO medium_labelling_method_grants VALUES (NULL, 'sl-on-m', NULL, NULL);

CREATE TABLE dle_labelling_methods (
	    -- columns
            dle_labelling_method_id CHAR,
            enabled CHAR NOT NULL,
            dle_labelling_method_cmd CHAR NOT NULL,
	    -- clauses
	    PRIMARY KEY (dle_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (dle_labelling_method_id)
	);
INSERT INTO dle_labelling_methods VALUES ('df-in-dleap', 'true','Linux/Linux/dle-labellers/df-in-dleap');
INSERT INTO dle_labelling_methods VALUES('row-in-sqlite-db','true','Linux/Linux/dle-labellers/row-in-sqlite-db');

CREATE TABLE dle_labelling_method_grants (
	    -- columns
	    dle_labelling_method_grant_id INTEGER,
	    dle_labelling_method_id CHAR,
            server_type_id CHAR,
            client_type_id CHAR,
            dle_type_id CHAR,
	    -- clauses
            PRIMARY KEY (dle_labelling_method_grant_id),
            FOREIGN KEY (dle_labelling_method_id) REFERENCES dle_labelling_methods(dle_labelling_method_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id),
            FOREIGN KEY (client_type_id) REFERENCES client_types(client_type_id),
            FOREIGN KEY (dle_type_id) REFERENCES dle_types(dle_type_id)
	);
INSERT INTO dle_labelling_method_grants VALUES (NULL, 'df-in-dleap', 'Linux', 'Linux', 'dir');
INSERT INTO dle_labelling_method_grants VALUES (NULL, 'df-in-dleap', 'Linux', 'Linux', 'dir-one-fs');
INSERT INTO dle_labelling_method_grants VALUES(NULL,'row-in-sqlite-db','Linux','Linux','dir');
INSERT INTO dle_labelling_method_grants VALUES(NULL,'row-in-sqlite-db','Linux','Linux','dir-one-fs');

CREATE TABLE client_labelling_methods (
	    -- columns
            client_labelling_method_id CHAR,
            enabled CHAR NOT NULL,
            client_labelling_method_cmd CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (client_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (client_labelling_method_cmd)
	);
INSERT INTO client_labelling_methods VALUES ('df-in-cr', 'true', 'Linux/Linux/client-labellers/df-in-cr');

CREATE TABLE server_labelling_methods (
	    -- columns
            server_labelling_method_id CHAR,
            enabled CHAR NOT NULL,
            server_labelling_method_cmd CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (server_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (server_labelling_method_cmd)
	);
INSERT INTO server_labelling_methods VALUES ('df-in-sr', 'true', 'Linux/server-labellers/df-in-sr');

-- grants
CREATE TABLE client_labelling_method_grants (
	    -- columns
	    client_labelling_method_grant_id INTEGER,
            client_labelling_method_id CHAR,
            server_type_id CHAR,
            client_type_id CHAR,
	    -- clauses
            PRIMARY KEY (client_labelling_method_grant_id),
            FOREIGN KEY (client_labelling_method_id) REFERENCES client_labelling_methods(client_labelling_method_id),
            FOREIGN KEY (client_type_id) REFERENCES client_types(client_type_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id)
        );
INSERT INTO client_labelling_method_grants VALUES (NULL, 'df-in-cr',   'Linux', 'Linux'); 

CREATE TABLE server_labelling_method_grants (
	    -- columns
	    server_labelling_method_grant_id INTEGER,
            server_labelling_method_id CHAR,
            server_type_id CHAR,
	    -- clauses
            PRIMARY KEY (server_labelling_method_grant_id),
            FOREIGN KEY (server_labelling_method_id) REFERENCES server_labelling_methods(server_labelling_method_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id)
        );
INSERT INTO server_labelling_method_grants VALUES (NULL, 'df-in-sr', 'Linux');

CREATE TABLE accesspoint_inspector_methods (
	    -- columns
            accesspoint_inspector_method_id CHAR,
            enabled CHAR NOT NULL,
            accesspoint_inspector_method_cmd CHAR NOT NULL,
            -- clauses
            PRIMARY KEY (accesspoint_inspector_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (accesspoint_inspector_method_cmd)
	);
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-tmp', 'true','Linux/accesspoint-inspectors/find-map-in-tmp' );
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-mounts', 'true','Linux/accesspoint-inspectors/find-map-in-mounts' );
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-td', 'false','Linux/accesspoint-inspectors/find-map-in-td');
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-env-hints', 'true','Linux/accesspoint-inspectors/find-map-in-env-hints');

CREATE TABLE accesspoint_inspector_method_grants (
	    -- columns
	    accesspoint_inspector_method_grant_id INTEGER,
            accesspoint_inspector_method_id CHAR,
            server_type_id CHAR,
            medium_type_id CHAR,
            -- clauses
            PRIMARY KEY (accesspoint_inspector_method_grant_id),
            FOREIGN KEY (accesspoint_inspector_method_id) REFERENCES accesspoint_inspector_methods(accesspoint_inspector_method_id),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id),
            FOREIGN KEY (medium_type_id) REFERENCES medium_types(medium_type_id)
	);
INSERT INTO accesspoint_inspector_method_grants VALUES (NULL, 'find-map-in-tmp', 'Linux', 'floating-dir');
INSERT INTO accesspoint_inspector_method_grants VALUES (NULL, 'find-map-in-mounts', 'Linux', 'floating-dir');
INSERT INTO accesspoint_inspector_method_grants VALUES (NULL, 'find-map-in-td', 'Linux', 'floating-tape');
INSERT INTO accesspoint_inspector_method_grants VALUES (NULL, 'find-map-in-env-hints', 'Linux', 'floating-dir');

CREATE TABLE schedules (
            -- columns
            schedule_id CHAR,
            backup_schedule CHAR NOT NULL,
            check_schedule CHAR NOT NULL,
            -- clauses
            PRIMARY KEY (schedule_id)
    	);
INSERT INTO schedules VALUES('nightly','20 0 * * *','50 8 * * 1,3,5');
INSERT INTO schedules VALUES('daily','20 12 * * *','50 9 * * 1,3,5');
INSERT INTO schedules VALUES('hourly','30 7-22 * * *','50 10 * * 1,3,5');
INSERT INTO schedules VALUES('two-hourly','0 10,12,14,16,18,20 * * *','50 10 * * 1,3,5');
INSERT INTO schedules VALUES('weekly','40 22 * * 6','50 11 * * 1,3,5');

-- user-populated tables
CREATE TABLE clients (
	    -- columns
            client_id CHAR,
            enabled CHAR NOT NULL,
            client_type_id CHAR,
            client_hostname CHAR NOT NULL,
            client_labelling_method_id CHAR,
            client_label CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (client_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (client_type_id) REFERENCES client_types(client_type_id),
            UNIQUE (client_hostname),
            FOREIGN KEY (client_labelling_method_id) REFERENCES client_labelling_methods(client_labelling_method_id),
            UNIQUE (client_label)
        );

CREATE TABLE db_access_modes (
            -- columns
	    db_access_mode CHAR NOT NULL,
            -- clauses
            PRIMARY KEY (db_access_mode),
	    CHECK(db_access_mode = 'shared' OR db_access_mode = 'exclusive' OR db_access_mode = 'peeklocks')
	);
INSERT INTO db_access_modes VALUES ('shared');
INSERT INTO db_access_modes VALUES ('exclusive');
INSERT INTO db_access_modes VALUES ('peeklocks');

CREATE TABLE locks (
	    -- columns
	    lock_pid INTEGER NOT NULL,
            db_access_mode CHAR NOT NULL,
	    cmd CHAR NOT NULL,
            start_timestamp INTEGER NOT NULL,
	    -- clauses
            PRIMARY KEY (lock_pid),
            FOREIGN KEY (db_access_mode) REFERENCES db_access_modes(db_access_mode)
 	);

CREATE TABLE sets (
	    -- columns
            set_id CHAR,
            lock_pid INTEGER,
            enabled CHAR NOT NULL,
            schedule_id CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (set_id),
            FOREIGN KEY (lock_pid) REFERENCES locks(lock_pid),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (schedule_id) REFERENCES schedules(schedule_id)
        );

CREATE TABLE dles (
	    -- columns
            dle_id CHAR,
            lock_pid INTEGER,
            enabled CHAR NOT NULL,
            set_id CHAR,
            dle_type_id CHAR,
            client_id CHAR,
            dle_accesspoint CHAR NOT NULL,
            backup_method_id CHAR,
            dle_labelling_method_id CHAR,
            dle_label CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (dle_id),
            FOREIGN KEY (lock_pid) REFERENCES locks(lock_pid),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (set_id) REFERENCES sets(set_id),
            FOREIGN KEY (dle_type_id) REFERENCES dle_types(dle_type_id),
            FOREIGN KEY (client_id) REFERENCES clients(client_id),
            UNIQUE(client_id, dle_accesspoint),
            UNIQUE(dle_label),
            FOREIGN KEY (dle_labelling_method_id) REFERENCES dle_labelling_methods(dle_labelling_method_id),
            FOREIGN KEY (backup_method_id) REFERENCES backup_methods(backup_method_id)
	);

CREATE TABLE backup_filters (
            -- columns
            backup_filter CHAR NOT NULL,
            set_id CHAR,
            dle_id CHAR,
            backup_method_id CHAR,
            -- backup_filter+set_id+dle_id+backup_method_id would be primary key but that doesn't support NULL as
            -- a unique value, so we need to implement the constraint in a trigger. See the
            -- backup_filters_pre_insert trigger below.
            enabled CHAR NOT NULL,
            -- clauses
            FOREIGN KEY (set_id) REFERENCES sets(set_id),
            FOREIGN KEY (dle_id) REFERENCES dles(dle_id),
            FOREIGN KEY (backup_method_id) REFERENCES backup_methods(backup_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical)
);

CREATE TABLE media (
	    -- columns
            medium_id CHAR,
            lock_pid INTEGER,
            enabled CHAR NOT NULL,
            medium_type_id CHAR,
	    medium_labelling_method_id CHAR,
            medium_label CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (medium_id),
            FOREIGN KEY (lock_pid) REFERENCES locks(lock_pid),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (medium_type_id) REFERENCES medium_types(medium_type_id),
            FOREIGN KEY (medium_labelling_method_id) REFERENCES medium_labelling_methods(medium_labelling_method_id),
            UNIQUE(medium_label)
	);

-- other tables
CREATE TABLE servers (
	    -- columns
            server_id CHAR,
            enabled CHAR NOT NULL,
            server_type_id CHAR,
            server_hostname CHAR NOT NULL,
            server_labelling_method_id CHAR,
            server_label CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (server_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            FOREIGN KEY (server_type_id) REFERENCES server_types(server_type_id),
            UNIQUE (server_hostname),
            UNIQUE (server_label),
            FOREIGN KEY (server_labelling_method_id) REFERENCES server_labelling_methods(server_labelling_method_id)
        );

CREATE TABLE medium_grants (
	    -- columns
	    medium_grant_id INTEGER,
            medium_id CHAR,
	    set_id CHAR,
            dle_id CHAR,
            client_id CHAR,
            server_id CHAR,
	    -- clauses
            PRIMARY KEY (medium_grant_id),
            FOREIGN KEY (medium_id) REFERENCES media(medium_id),
            FOREIGN KEY (set_id) REFERENCES sets(set_id),
            FOREIGN KEY (dle_id) REFERENCES dles(dle_id),
            FOREIGN KEY (client_id) REFERENCES clients(client_id),
            FOREIGN KEY (server_id) REFERENCES servers(server_id)
	);

CREATE TABLE backups (
	    -- columns
            backup_timestamp INTEGER NOT NULL,
	    dle_id CHAR NOT NULL,
            -- set_id may be NULL
	    set_id CHAR,
            lock_pid INTEGER,
            start_timestamp INTEGER,
            end_timestamp INTEGER,
	    medium_id CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (backup_timestamp, dle_id),
            FOREIGN KEY (lock_pid) REFERENCES locks(lock_pid),
            FOREIGN KEY (dle_id) REFERENCES dles(dle_id),
            FOREIGN KEY (set_id) REFERENCES sets(set_id),
            FOREIGN KEY (medium_id) REFERENCES media(medium_id)
	);

CREATE TABLE backed_up_files (
	    -- columns
            backup_timestamp INTEGER NOT NULL,
            dle_id CHAR NOT NULL,
            rel_file CHAR NOT NULL,
	    -- clauses
            PRIMARY KEY (backup_timestamp, dle_id, rel_file),
            FOREIGN KEY (backup_timestamp, dle_id) REFERENCES backups(backup_timestamp, dle_id)
	);

-- views
-- medium_dle_applicable_grants lists all grants granting a DLE access to a medium for each DLE.
-- (Access may be granted by virtue of set_id, dle_id or client_id. Additionally, we only look
-- for grants related to the backup server we are on. (At one time I wondered if the database
-- should be sharable between backup servers, but that was turned out to be a redundant.)
CREATE VIEW medium_dle_applicable_grants AS
       SELECT media.medium_id, 
              dles.dle_id, 
              medium_grants.medium_grant_id
       FROM   media, dles, servers, medium_grants
       WHERE  medium_grants.medium_id = media.medium_id AND
              (medium_grants.set_id IS NULL OR medium_grants.set_id = dles.set_id) AND
              (medium_grants.dle_id IS NULL OR medium_grants.dle_id = dles.dle_id) AND
              (medium_grants.client_id IS NULL OR medium_grants.client_id = dles.client_id) AND
              (medium_grants.server_id IS NULL OR medium_grants.server_id = servers.server_id);

-- for all medium/DLE combinations indicate whether that DLE may be backed up on that medium.
CREATE VIEW medium_dle_compatibility AS
       SELECT media.medium_id, 
              dles.dle_id, 
              (
                  SELECT COUNT(*)>0 
                  FROM   medium_dle_applicable_grants 
                  WHERE  media.medium_id=medium_dle_applicable_grants.medium_id AND 
                         dles.dle_id=medium_dle_applicable_grants.dle_id
              ) AS compatible,
              DATETIME(COALESCE(
                      (SELECT MIN(backup_timestamp) 
                       FROM   backups 
                       WHERE backups.medium_id = media.medium_id), 0), 'unixepoch', 'localtime'
              ) AS medium_last_used_timestamp
              FROM media, dles;

-- for all medium/set combinations indicate whether that set may be backed up on that medium.
CREATE VIEW medium_set_compatibility AS 
       SELECT set_id,
              medium_id,
              -- get the number of DLEs in the set
              (
                  SELECT COUNT(*)
                  FROM   dles
                  WHERE dles.set_id=sets.set_id
              -- get the number of DLEs in the set that may be backed up the medium
              ) = COALESCE(
                      (SELECT SUM(compatible)
                       FROM   dles, medium_dle_compatibility
                       WHERE  dles.dle_id = medium_dle_compatibility.dle_id AND
                              dles.set_id = sets.set_id AND
                              medium_dle_compatibility.medium_id = media.medium_id),0
              ) AS compatible,
              DATETIME(COALESCE(
                      (SELECT MIN(backup_timestamp)
                       FROM   backups
                       WHERE backups.medium_id = media.medium_id), 0), 'unixepoch', 'localtime'
              ) AS medium_last_used_timestamp
              -- and if they are equal (actually, that is expressed above as '(...) = COALESCE(...,0)')
              -- then compatibility is 1 else 0 (the '(...) = (...)' takes care of that).
        FROM   sets, media;

-- triggers
CREATE TRIGGER backup_filters_pre_insert
BEFORE INSERT ON backup_filters
BEGIN
    SELECT RAISE(FAIL, "%{BACKUP_FILTER},%{SET_ID},%{DLE_ID},%{BACKUP_METHOD_ID}: a filter for combination is already defined") WHERE (
        SELECT COUNT(*)
        FROM backup_filters
        WHERE (backup_filter = NEW.backup_filter) AND (set_id = NEW.set_id OR (set_id IS NULL AND NEW.set_id IS NULL)) AND (dle_id = NEW.dle_id OR (dle_id IS NULL AND NEW.dle_id IS NULL)) AND (backup_method_id = NEW.backup_method_id OR (backup_method_id IS NULL AND NEW.backup_method_id IS NULL))
    ) == 1;
END
;

CREATE TRIGGER dles_pre_update_lock_pid
BEFORE UPDATE OF lock_pid ON dles
BEGIN
     SELECT RAISE(FAIL, "%{DLE_ID}: row locked") WHERE (
         SELECT COUNT(*)
         FROM dles
         WHERE dle_id = OLD.dle_id and
               lock_pid IS NOT NULL and
               NEW.lock_pid IS NOT NULL
     ) != 0;
     SELECT RAISE(FAIL, "%{DLE_ID}: row already unlocked") WHERE (
         SELECT COUNT(*)
         FROM dles
         WHERE dle_id = OLD.dle_id and
               lock_pid IS NULL and
               NEW.lock_pid IS NULL
     ) != 0;
END;
CREATE TRIGGER locks_pre_insert
BEFORE INSERT ON locks
BEGIN
    SELECT RAISE(FAIL, "another process has locked the database in exclusive access mode") WHERE (
        SELECT COUNT(*) 
        FROM locks
        WHERE db_access_mode = 'exclusive' AND
              (NEW.db_access_mode = 'shared' OR NEW.db_access_mode = 'exclusive')
    ) != 0;
    SELECT RAISE(FAIL, "other processes are accessing the database") WHERE (
        SELECT COUNT(*) 
        FROM locks
        WHERE NEW.db_access_mode = 'exclusive'
    ) != 0;
END;
CREATE TRIGGER sets_pre_insert
BEFORE INSERT ON sets
BEGIN
    -- primary key ensures this is not possible, but trigger gives nicer error message
    SELECT RAISE(FAIL, "%{SET_ID}: this set is already defined") WHERE (
        SELECT COUNT(*) 
        FROM sets 
        WHERE set_id = NEW.set_id
    ) == 1;
    SELECT RAISE(FAIL, "%{SCHEDULE_ID}: unknown schedule") WHERE (
        SELECT COUNT(*)
        FROM schedules
        WHERE schedules.schedule_id = NEW.schedule_id
    ) == 0;
END
;
CREATE TRIGGER sets_pre_delete
BEFORE DELETE ON sets
BEGIN
     SELECT RAISE(FAIL, "%{SET_ID}: this set is currently in use") WHERE (
         SELECT COUNT(*)
         FROM sets
         WHERE set_id = OLD.set_id and
               lock_pid IS NOT NULL
     ) != 0;
END;
CREATE TRIGGER clients_pre_insert
BEFORE INSERT ON clients
BEGIN
    -- primary key ensures this is not possible, but trigger gives nicer error message
    SELECT RAISE(FAIL, "%{CLIENT_ID}: this client is already defined") WHERE (
        SELECT COUNT(*) 
        FROM clients 
        WHERE client_id = NEW.client_id
    ) == 1;
    SELECT RAISE(FAIL, "%{CLIENT_LABELLING_METHOD_ID}: unknown client labelling method") WHERE (
        SELECT COUNT(*)
        FROM client_labelling_methods
        WHERE client_labelling_methods.client_labelling_method_id = NEW.client_labelling_method_id
    ) == 0;
    SELECT RAISE(FAIL, "%{CLIENT_LABELLING_METHOD_ID}: inappropriate, unknown or disabled client labelling method") WHERE (
        SELECT COUNT(*) 
        FROM client_labelling_method_grants 
        where client_labelling_method_id = NEW.client_labelling_method_id  and 
              (client_type_id IS NULL or client_type_id = NEW.client_type_id) and 
              (server_type_id IS NULL or server_type_id = (select server_type_id from servers))
    ) == 0;
END
;
CREATE TRIGGER servers_pre_insert
BEFORE INSERT ON servers
BEGIN
    -- primary key ensures this is not possible, but trigger gives nicer error message
    SELECT RAISE(FAIL, "one server already defined") WHERE (
        SELECT COUNT(*) 
        FROM servers
    ) == 1;
    SELECT RAISE(FAIL, "%{SERVER_LABELLING_METHOD_ID}: inappropriate, unknown or disabled server labelling method") WHERE (
        SELECT COUNT(*) 
        FROM server_labelling_method_grants 
        WHERE server_labelling_method_id = NEW.server_labelling_method_id AND 
              (server_type_id IS NULL OR server_type_id = NEW.server_type_id)
    ) == 0;
END
;
CREATE TRIGGER media_pre_insert
BEFORE INSERT ON media
BEGIN
    -- primary key ensures this is not possible, but trigger gives nicer error message
    SELECT RAISE(FAIL, "%{MEDIUM_ID}: this medium is already defined") WHERE (
        SELECT COUNT(*) 
        FROM media 
        WHERE medium_id = NEW.medium_id
    ) == 1;
    SELECT RAISE(FAIL, "%{MEDIUM_TYPE_ID}: unknown medium type") WHERE (
        SELECT COUNT(*)
        FROM medium_types
        WHERE medium_types.medium_type_id = NEW.medium_type_id
    ) == 0;
    SELECT RAISE(FAIL, "%{MEDIUM_LABELLING_METHOD_ID}: unknown medium labelling method") WHERE (
        SELECT COUNT(*)
        FROM medium_labelling_methods
        WHERE medium_labelling_methods.medium_labelling_method_id = NEW.medium_labelling_method_id
    ) == 0;
    SELECT RAISE(FAIL, "%{MEDIUM_LABELLING_METHOD_ID}: inappropriate, unknown or disabled medium labelling method") WHERE (
        SELECT COUNT(*) 
        FROM medium_labelling_method_grants, servers 
        WHERE medium_labelling_method_grants.medium_labelling_method_id = NEW.medium_labelling_method_id AND 
              (medium_labelling_method_grants.server_type_id IS NULL OR medium_labelling_method_grants.server_type_id = servers.server_type_id) AND 
              (medium_labelling_method_grants.medium_type_id IS NULL OR medium_labelling_method_grants.medium_type_id = NEW.medium_type_id)
    ) == 0;
END
;
CREATE TRIGGER media_pre_delete
BEFORE DELETE ON media
BEGIN
     SELECT RAISE(FAIL, "%{MEDIUM_ID}: this medium is currently in use") WHERE (
         SELECT COUNT(*)
         FROM media
         WHERE medium_id = OLD.medium_id and
               lock_pid IS NOT NULL
     ) != 0;
END;
CREATE TRIGGER dles_pre_insert
BEFORE INSERT ON dles
BEGIN
    -- nicer messages than defaults
    SELECT RAISE(FAIL, "%{DLE_ID}: this dle is already defined") WHERE (
        SELECT COUNT(*)
        FROM dles
        WHERE dle_id = NEW.dle_id
    ) == 1;
    SELECT RAISE(FAIL, "%{SET_ID}: unknown set") WHERE (
        SELECT COUNT(*)
        FROM sets
        WHERE sets.set_id = NEW.set_id
    ) == 0;
    SELECT RAISE(FAIL, "%{CLIENT_ID}: unknown client") WHERE (
        SELECT COUNT(*)
        FROM clients
        WHERE clients.client_id = NEW.client_id
    ) == 0;
    SELECT RAISE(FAIL, "%{CLIENT_ID}:%{DLE_ACCESSPOINT}: this client and dle accesspoint are already defined") WHERE (
        SELECT COUNT(*)
        FROM dles
        WHERE dles.client_id=NEW.client_id and dles.dle_accesspoint=NEW.dle_accesspoint
    ) == 1;
    SELECT RAISE(FAIL, "%{BACKUP_METHOD_ID}: unknown backup method") WHERE (
        SELECT COUNT(*)
        FROM backup_methods
        WHERE backup_methods.backup_method_id = NEW.backup_method_id
    ) == 0;
    SELECT RAISE(FAIL, "%{DLE_LABELLING_METHOD_ID}: unknown dle labelling method") WHERE (
        SELECT COUNT(*)
        FROM dle_labelling_methods
        WHERE dle_labelling_methods.dle_labelling_method_id = NEW.dle_labelling_method_id
    ) == 0;
    -- real constraints
    SELECT RAISE(FAIL, "%{DLE_LABELLING_METHOD_ID}: inappropriate, unknown or disabled dle labelling method") WHERE (
        SELECT COUNT(*)
        FROM dle_labelling_method_grants
        where dle_labelling_method_grants.dle_labelling_method_id = NEW.dle_labelling_method_id  and
              (dle_labelling_method_grants.dle_type_id IS NULL or dle_labelling_method_grants.dle_type_id = NEW.dle_type_id) and
              (dle_labelling_method_grants.server_type_id IS NULL or dle_labelling_method_grants.server_type_id = (select server_type_id from servers)) and
              (dle_labelling_method_grants.client_type_id IS NULL or dle_labelling_method_grants.client_type_id = (select client_type_id from clients where clients.client_id=NEW.client_id))
    ) == 0;
    SELECT RAISE(FAIL, "%{BACKUP_METHOD_ID}: inappropriate, unknown or disabled backup method") WHERE (
        SELECT COUNT(*)
        FROM backup_method_grants
        WHERE backup_method_grants.backup_method_id=NEW.backup_method_id and
        (backup_method_grants.server_type_id IS NULL or backup_method_grants.server_type_id=(select server_type_id from servers)) and
        (backup_method_grants.client_type_id IS NULL or backup_method_grants.client_type_id=(select client_type_id from clients where clients.client_id=NEW.client_id)) and
        (backup_method_grants.dle_type_id IS NULL or backup_method_grants.dle_type_id=NEW.dle_type_id)
    ) == 0;
END
;
CREATE TRIGGER dles_pre_delete
BEFORE DELETE ON dles
BEGIN
     SELECT RAISE(FAIL, "%{DLE_ID}: this dle is currently in use") WHERE (
         SELECT COUNT(*)
         FROM dles
         WHERE dle_id = OLD.dle_id and
               lock_pid IS NOT NULL
     ) != 0;
END;
CREATE TRIGGER sets_pre_update_lock_pid
BEFORE UPDATE OF lock_pid ON sets
BEGIN
     SELECT RAISE(FAIL, "%{SET_ID}: row locked") WHERE (
         SELECT COUNT(*)
         FROM sets
         WHERE set_id = OLD.set_id and
               lock_pid IS NOT NULL and
               NEW.lock_pid IS NOT NULL
     ) != 0;
     SELECT RAISE(FAIL, "%{SET_ID}: row already unlocked") WHERE (
         SELECT COUNT(*)
         FROM sets
         WHERE set_id = OLD.set_id and
               lock_pid IS NULL and
               NEW.lock_pid IS NULL
     ) != 0;
END;
CREATE TRIGGER media_pre_update_lock_pid
BEFORE UPDATE OF lock_pid ON media
BEGIN
     SELECT RAISE(FAIL, "%{MEDIUM_ID}: row locked") WHERE (
         SELECT COUNT(*)
         FROM media
         WHERE medium_id = OLD.medium_id and
               lock_pid IS NOT NULL and
               NEW.lock_pid IS NOT NULL
     ) != 0;
     SELECT RAISE(FAIL, "%{MEDIUM_ID}: row already unlocked") WHERE (
         SELECT COUNT(*)
         FROM media
         WHERE medium_id = OLD.medium_id and
               lock_pid IS NULL and
               NEW.lock_pid IS NULL
     ) != 0;
END;
CREATE TRIGGER backups_pre_update_lock_pid
BEFORE UPDATE OF lock_pid ON backups
BEGIN
     SELECT RAISE(FAIL, "%{BACKUP_TIMESTAMP}:%{DLE_ID} row locked") WHERE (
         SELECT COUNT(*)
         FROM backups
         WHERE backup_timestamp = OLD.backup_timestamp and
               dle_id = OLD.dle_id and
               lock_pid IS NOT NULL and
               NEW.lock_pid IS NOT NULL
     ) != 0;
     SELECT RAISE(FAIL, "%{BACKUP_TIMESTAMP}:%{DLE_ID}: row already unlocked") WHERE (
         SELECT COUNT(*)
         FROM backups
         WHERE backup_timestamp = OLD.backup_timestamp and
               dle_id = OLD.dle_id and
               lock_pid IS NULL and
               NEW.lock_pid IS NULL
     ) != 0;
END;
