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

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

-- base type tables
CREATE TABLE logicals (
	    -- columns
	    logical TEXT, 
	    -- 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 TEXT,
	    -- 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 TEXT,
	    -- 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 TEXT,
	    -- 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 TEXT,
	    -- 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 TEXT,
            enabled TEXT NOT NULL,
            increment_support TEXT NOT NULL,
            backup_method_cmd TEXT 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)
        );
-- CREATE VIEW enabled_backup_methods AS SELECT backup_method_id FROM backup_methods WHERE enabled = 'true';
INSERT INTO backup_methods VALUES ('rsync', 'false','false','S=Linux/backups/C=Linux/rsync');
INSERT INTO backup_methods VALUES ('rsync-one-fs', 'false','false','S=Linux/backups/C=Linux/rsync-one-fs');
INSERT INTO backup_methods VALUES ('rdiff-backup', 'true','true','S=Linux/backups/C=Linux/rdiff-backup');
INSERT INTO backup_methods VALUES ('rdiff-backup-one-fs', 'true','true','S=Linux/backups/C=Linux/rdiff-backup-one-fs');
INSERT INTO backup_methods VALUES ('unison', 'true','false','S=Linux/backups/C=Linux/unison');
INSERT INTO backup_methods VALUES ('unison-one-fs', 'true','false','S=Linux/backups/C=Linux/unison-one-fs');
INSERT INTO backup_methods VALUES ('noop', 'true','false','S=Linux/backups/C=all/noop');

CREATE TABLE backup_method_grants (
	    -- columns
	    backup_method_grant_id INTEGER,
            backup_method_id TEXT,
            server_type_id TEXT,
            client_type_id TEXT,
            medium_type_id TEXT,
            dle_type_id TEXT,
	    -- 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 TEXT,
            enabled TEXT NOT NULL,
            medium_labelling_method_cmd TEXT NOT NULL,
	    -- clauses
	    PRIMARY KEY (medium_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE(medium_labelling_method_cmd)
	);
-- CREATE VIEW enabled_medium_labelling_methods AS SELECT medium_labelling_method_id FROM medium_labelling_methods WHERE enabled = 'true';
INSERT INTO medium_labelling_methods VALUES ('df-in-map', 'true','S=Linux/medium-labellers/df-in-map');
INSERT INTO medium_labelling_methods VALUES ('hf-in-map', 'true','S=Windows/medium-labellers/hf-in-map');
INSERT INTO medium_labelling_methods VALUES ('ff-on-tape', 'true','S=Linux/medium-labellers/ff-on-tape');
INSERT INTO medium_labelling_methods VALUES ('sl-on-m', 'true','S=Linux/medium-labellers/sl-on-m');

CREATE TABLE medium_labelling_method_grants (
	    -- columns
	    medium_labelling_method_grant_id INTEGER,
            medium_labelling_method_id TEXT,
            server_type_id TEXT,
            medium_type_id TEXT,
	    -- 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, 'hf-in-map', 'Windows', '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 TEXT,
            enabled TEXT NOT NULL,
            dle_labelling_method_cmd TEXT NOT NULL,
	    -- clauses
	    PRIMARY KEY (dle_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (dle_labelling_method_id)
	);
-- CREATE VIEW enabled_dle_labelling_methods AS SELECT dle_labelling_method_id FROM dle_labelling_methods WHERE enabled = 'true';
INSERT INTO dle_labelling_methods VALUES ('df-in-dleap', 'true','S=Linux/dle-labellers/C=Linux/df-in-dleap');

CREATE TABLE dle_labelling_method_grants (
	    -- columns
	    dle_labelling_method_grant_id INTEGER,
	    dle_labelling_method_id TEXT,
            server_type_id TEXT,
            client_type_id TEXT,
            dle_type_id TEXT,
	    -- 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 TEXT,
            enabled TEXT NOT NULL,
            client_labelling_method_cmd TEXT NOT NULL,
	    -- clauses
            PRIMARY KEY (client_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (client_labelling_method_cmd)
	);
-- CREATE VIEW enabled_client_labelling_methods AS SELECT client_labelling_method_id FROM client_labelling_methods WHERE enabled = 'true';
INSERT INTO client_labelling_methods VALUES ('df-in-cr', 'true', 'S=Linux/client-labellers/C=Linux/df-in-cr');
INSERT INTO client_labelling_methods VALUES ('hf-in-cr', 'true', 'S=Linux/client-labellers/C=Windows/hf-in-cr');

CREATE TABLE server_labelling_methods (
	    -- columns
            server_labelling_method_id TEXT,
            enabled TEXT NOT NULL,
            server_labelling_method_cmd TEXT NOT NULL,
	    -- clauses
            PRIMARY KEY (server_labelling_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (server_labelling_method_cmd)
	);
-- CREATE VIEW enabled_server_labelling_methods AS SELECT server_labelling_method_id FROM server_labelling_methods WHERE enabled = 'true';
INSERT INTO server_labelling_methods VALUES ('df-in-sr', 'true', 'S=Linux/server-labellers/df-in-sr');
INSERT INTO server_labelling_methods VALUES ('hf-in-sr', 'true', 'S=Windows/server-labellers/hf-in-sr');

CREATE TABLE client_labelling_method_grants (
	    -- columns
	    client_labelling_method_grant_id INTEGER,
            client_labelling_method_id TEXT,
            server_type_id TEXT,
            client_type_id TEXT,
	    -- 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');           
INSERT INTO client_labelling_method_grants VALUES (NULL, 'hf-in-cr', 'Linux', 'Windows');           

CREATE TABLE server_labelling_method_grants (
	    -- columns
	    server_labelling_method_grant_id INTEGER,
            server_labelling_method_id TEXT,
            server_type_id TEXT,
	    -- 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');           
INSERT INTO server_labelling_method_grants VALUES (NULL, 'hf-in-sr', 'Windows');           

CREATE TABLE accesspoint_inspector_methods (
	    -- columns
            accesspoint_inspector_method_id TEXT,
            enabled TEXT NOT NULL,
            accesspoint_inspector_method_cmd TEXT NOT NULL,
            -- clauses
            PRIMARY KEY (accesspoint_inspector_method_id),
            FOREIGN KEY (enabled) REFERENCES logicals(logical),
            UNIQUE (accesspoint_inspector_method_cmd)
	);
-- CREATE VIEW enabled_accesspoint_inspector_methods AS SELECT accesspoint_inspector_method_id FROM accesspoint_inspector_methods WHERE enabled = 'true';
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-tmp', 'true','S=Linux/accesspoint-inspectors/find-map-in-tmp' );
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-mounts', 'true','S=Linux/accesspoint-inspectors/find-map-in-mounts' );
INSERT INTO accesspoint_inspector_methods VALUES ('find-map-in-td', 'false','S=Linux/accesspoint-inspectors/find-map-in-td');
CREATE TABLE accesspoint_inspector_method_grants (
	    -- columns
	    accesspoint_inspector_method_grant_id INTEGER,
            accesspoint_inspector_method_id TEXT,
            server_type_id TEXT,
            medium_type_id TEXT,
            -- 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');

CREATE TABLE schedules (
            -- columns
            schedule_id TEXT,
            backup_schedule TEXT NOT NULL,
            check_schedule TEXT 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 TEXT,
            enabled TEXT NOT NULL,
            client_type_id TEXT,
            client_hostname TEXT NOT NULL,
            client_labelling_method_id TEXT,
            client_label TEXT 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 VIEW enabled_clients AS SELECT client_id, client_type_id, client_hostname, client_label, client_labelling_method_id FROM clients WHERE enabled = 'true';

CREATE TABLE db_access_modes (
	    db_access_mode TEXT 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 TEXT NOT NULL,
	    cmd TEXT 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 TEXT,
            lock_pid INTEGER,
            enabled TEXT NOT NULL,
            schedule_id TEXT 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 VIEW enabled_sets AS SELECT set_id, lock_pid, schedule_id FROM sets WHERE enabled = 'true';

CREATE TABLE dles (
	    -- columns
            dle_id TEXT,
            lock_pid INTEGER,
            enabled TEXT NOT NULL,
            set_id TEXT,
            dle_type_id TEXT,
            client_id TEXT,
            dle_accesspoint TEXT NOT NULL,
            backup_method_id TEXT,
            dle_labelling_method_id TEXT,
            dle_label TEXT 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 VIEW enabled_dles AS SELECT dle_id, set_id, dle_type_id, client_id, dle_accesspoint, dle_label, dle_labelling_method_id, backup_method_id WHERE enabled = 'true';

CREATE TABLE media (
	    -- columns
            medium_id TEXT,
            lock_pid INTEGER,
            enabled TEXT NOT NULL,
            medium_type_id TEXT,
	    medium_labelling_method_id TEXT,
            medium_label TEXT 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)
	);
-- CREATE VIEW enabled_media AS SELECT medium_id, medium_type_id, medium_labelling_method_id, medium_label FROM media WHERE enabled = 'true';

-- other tables
CREATE TABLE servers (
	    -- columns
            server_id TEXT,
            enabled TEXT NOT NULL,
            server_type_id TEXT,
            server_hostname TEXT NOT NULL,
            server_labelling_method_id TEXT,
            server_label TEXT 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 VIEW enabled_servers AS SELECT server_id, server_type_id, server_hostname, server_label, server_labelling_method_id FROM servers WHERE enabled = 'true';

CREATE TABLE medium_grants (
	    -- columns
	    medium_labelling_method_grant_id INTEGER,
            medium_id TEXT,
	    set_id TEXT,
            dle_id TEXT,
            client_id TEXT,
            server_id TEXT,
	    -- clauses
            PRIMARY KEY (medium_labelling_method_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 TEXT NOT NULL,
            -- set_id may be NULL
	    set_id TEXT,
            lock_pid INTEGER,
            start_timestamp INTEGER,
            end_timestamp INTEGER,
	    medium_id TEXT 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 TEXT NOT NULL,
            rel_file TEXT NOT NULL,
	    -- clauses
            PRIMARY KEY (backup_timestamp, dle_id, rel_file),
            FOREIGN KEY (backup_timestamp, dle_id) REFERENCES backups(backup_timestamp, dle_id)
	);

-- CREATE VIEW dle_last_backed_ups AS SELECT dle_id, MAX(backup_timestamp) AS dle_last_backed_up_timestamp FROM backups GROUP BY dle_id ORDER BY dle_id;
-- CREATE VIEW medium_last_useds AS SELECT medium_id, MAX(backup_timestamp) AS medium_last_used_timestamp FROM backups GROUP BY medium_id ORDER BY medium_id;

-- clients triggers
CREATE TRIGGER dles_pre_update_lock_pid
BEFORE UPDATE OF lock_pid ON dles
BEGIN
     SELECT RAISE(FAIL, "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, "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, "this set is already defined") WHERE (
        SELECT COUNT(*) 
        FROM sets 
        WHERE set_id = NEW.set_id
    ) == 1;
    SELECT RAISE(FAIL, "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, "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, "this client is already defined") WHERE (
        SELECT COUNT(*) 
        FROM clients 
        WHERE client_id = NEW.client_id
    ) == 1;
    SELECT RAISE(FAIL, "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, "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, "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, "this medium is already defined") WHERE (
        SELECT COUNT(*) 
        FROM media 
        WHERE medium_id = NEW.medium_id
    ) == 1;
    SELECT RAISE(FAIL, "unknown medium type") WHERE (
        SELECT COUNT(*)
        FROM medium_types
        WHERE medium_types.medium_type_id = NEW.medium_type_id
    ) == 0;
    SELECT RAISE(FAIL, "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, "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, "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, "this dle is already defined") WHERE (
        SELECT COUNT(*)
        FROM dles
        WHERE dle_id = NEW.dle_id
    ) == 1;
    SELECT RAISE(FAIL, "unknown set") WHERE (
        SELECT COUNT(*)
        FROM sets
        WHERE sets.set_id = NEW.set_id
    ) == 0;
    SELECT RAISE(FAIL, "unknown client") WHERE (
        SELECT COUNT(*)
        FROM clients
        WHERE clients.client_id = NEW.client_id
    ) == 0;
    SELECT RAISE(FAIL, "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, "unknown backup method") WHERE (
        SELECT COUNT(*)
        FROM backup_methods
        WHERE backup_methods.backup_method_id = NEW.backup_method_id
    ) == 0;
    SELECT RAISE(FAIL, "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, "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, "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, "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, "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, "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, "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, "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, "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, "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;