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

-- database archicture revision
PRAGMA user_version = 4;
-- 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 jusy 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');

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);

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');

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');

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 * * * *','50 10 * * 1,3,5');
INSERT INTO schedules VALUES('weekly','40 22,10 * * *','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 (
	    db_access_mode CHAR NOT NULL,
            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 (
    dle_id CHAR,
    backup_method_id CHAR,
    -- 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,
    backup_filter BLOB,
    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
-- set_media lists all enabled and granted media for each set.
CREATE VIEW set_media AS
       SELECT dles.set_id, 
              media.medium_id,
              DATETIME(COALESCE(
                                (SELECT MIN(backup_timestamp) FROM backups WHERE backups.medium_id = media.medium_id),
                                0), 'unixepoch', 'localtime') AS medium_last_used_timestamp
        -- Note that dles are *not* filtered down to one row but servers only contains one row, so we're
        -- left with the results being per-medium-per-dle. However, when we group per-medium below
        -- then they will become per-medium (and we will get a per-group-count(*), which we'll need
        -- to validate that the medium may be used on *all* dles within the set.
        FROM  media, dles, servers
        WHERE media.enabled = 'true' AND
              0 < (SELECT COUNT(*)
                   FROM medium_grants
                   -- The medium grant has to be about the medium under consideration
                   WHERE medium_grants.medium_id = media.medium_id AND
                       -- The medium grant has to be about the medium under consideration
                       (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))
        -- A medium is only valid for a set if the count of how many dles with the set it is
        -- allowed to be used for matches the count of how many dles there *are* in the set.
        GROUP BY media.medium_id
        HAVING COUNT(*) = (SELECT COUNT(*) FROM dles AS d2 WHERE d2.set_id = dles.set_id)
        ORDER BY set_id, medium_last_used_timestamp;

-- triggers
CREATE TRIGGER backup_filters_pre_insert
BEFORE INSERT ON backup_filters
BEGIN
    SELECT RAISE(FAIL, "%{dle_id},%{backup_method_id}: a filter for combination is already defined") WHERE (
        SELECT COUNT(*)
        FROM backup_filters
        WHERE (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;