rekey.sql

CREATE TABLE IF NOT EXISTS principals (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, kvno INTEGER NOT NULL, message TEXT, downloadcount INTEGER NOT NULL DEFAULT 0, commitcount INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS acl (principal INTEGER NOT NULL, hostname TEXT NOT NULL, completed INTEGER NOT NULL DEFAULT 0, attempted INTEGER NOT NULL DEFAULT 0, UNIQUE (principal, hostname));
CREATE TABLE IF NOT EXISTS keys (principal INTEGER NOT NULL, enctype INTEGER NOT NULL, key BLOB, UNIQUE (principal, enctype));
CREATE TRIGGER IF NOT EXISTS delete_principal_check_ref BEFORE DELETE ON principals FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "principals" violates foreign key constraint') where (select principal from acl where principal = OLD.id) IS NOT NULL; SELECT RAISE(ROLLBACK, 'delete on table "principals" violates foreign key constraint') where (select principal from keys where principal = OLD.id) IS NOT NULL; END;
CREATE TRIGGER IF NOT EXISTS insert_acl_check_ref BEFORE INSERT ON acl FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "acl" violates foreign key constraint') where (select id from principals where NEW.principal = id) IS NULL; END;
CREATE TRIGGER IF NOT EXISTS insert_keys_check_ref BEFORE INSERT ON keys FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "keys" violates foreign key constraint') where (select id from principals where NEW.principal = id) IS NULL; END;
CREATE TRIGGER IF NOT EXISTS update_acl_immutables BEFORE UPDATE OF principal,hostname ON acl FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update of table "acl" violates immutability constraint'); END;
CREATE TRIGGER IF NOT EXISTS update_key_immutables BEFORE UPDATE ON keys FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update of table "keys" violates immutability constraint'); END;