oficina / docs Public

Download this file

1	CREATE TABLE docs_principal_preflight (ok INTEGER CHECK(ok=1));
2	INSERT INTO docs_principal_preflight SELECT CASE WHEN
3	NOT EXISTS(SELECT 1 FROM people WHERE id <= 0 OR id >= 4503599627370496)
4	AND NOT EXISTS(SELECT 1 FROM documents d WHERE d.owner_id < 0 OR (d.owner_id > 0 AND NOT EXISTS(SELECT 1 FROM people p WHERE p.id=d.owner_id)))
5	AND NOT EXISTS(SELECT 1 FROM grants g WHERE g.created_by < 0 OR (g.created_by > 0 AND NOT EXISTS(SELECT 1 FROM people p WHERE p.id=g.created_by)))
6	AND NOT EXISTS(SELECT 1 FROM pragma_foreign_key_check)
7	AND (SELECT foreign_keys FROM pragma_foreign_keys)=1
8	THEN 1 ELSE 0 END;
9	DROP TABLE docs_principal_preflight;
10	CREATE TABLE docs_principals (
11	id INTEGER PRIMARY KEY,
12	kind TEXT NOT NULL CHECK((kind='local' AND id BETWEEN 1 AND 4503599627370495) OR (kind='home' AND id BETWEEN 4503599627370496 AND 9007199254740991)),
13	created_at DATETIME NOT NULL
14	);
15	INSERT INTO docs_principals SELECT id,'local',created_at FROM people;
16	CREATE TABLE docs_home_identities (
17	issuer TEXT NOT NULL,
18	subject TEXT NOT NULL,
19	principal_id INTEGER NOT NULL UNIQUE REFERENCES docs_principals(id),
20	PRIMARY KEY(issuer,subject)
21	);
22	CREATE TABLE docs_home_allocator (
23	singleton INTEGER PRIMARY KEY CHECK(singleton=1),
24	high_water INTEGER NOT NULL CHECK(typeof(high_water)='integer' AND high_water BETWEEN 4503599627370495 AND 9007199254740991)
25	);
26	INSERT INTO docs_home_allocator VALUES(1,4503599627370495);
27	CREATE TRIGGER docs_people_mirror AFTER INSERT ON people BEGIN
28	SELECT CASE WHEN NEW.id < 1 OR NEW.id >= 4503599627370496 THEN RAISE(ABORT,'docs principal: refused') END;
29	INSERT INTO docs_principals VALUES(NEW.id,'local',NEW.created_at);
30	END;
31	CREATE TRIGGER docs_people_keep_id BEFORE UPDATE OF id ON people WHEN NEW.id != OLD.id BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
32	CREATE TRIGGER docs_people_keep_row BEFORE DELETE ON people BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
33	CREATE TRIGGER docs_principals_keep_identity BEFORE UPDATE OF id,kind ON docs_principals WHEN NEW.id != OLD.id OR NEW.kind != OLD.kind BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
34	CREATE TRIGGER docs_principals_keep_row BEFORE DELETE ON docs_principals BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
35	CREATE TRIGGER docs_allocator_monotonic BEFORE UPDATE ON docs_home_allocator WHEN NEW.singleton != OLD.singleton OR NEW.high_water <= OLD.high_water BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
36	CREATE TRIGGER docs_allocator_keep_row BEFORE DELETE ON docs_home_allocator BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
37	CREATE TRIGGER docs_home_identity_kind BEFORE INSERT ON docs_home_identities WHEN NOT EXISTS(SELECT 1 FROM docs_principals WHERE id=NEW.principal_id AND kind='home') BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
38	CREATE TRIGGER docs_home_identity_unique BEFORE INSERT ON docs_home_identities WHEN EXISTS(SELECT 1 FROM docs_identity_links WHERE issuer=NEW.issuer AND subject=NEW.subject) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
39	CREATE TRIGGER docs_local_identity_unique BEFORE INSERT ON docs_identity_links WHEN EXISTS(SELECT 1 FROM docs_home_identities WHERE issuer=NEW.issuer AND subject=NEW.subject) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
40	CREATE TRIGGER docs_home_identities_keep_update BEFORE UPDATE ON docs_home_identities BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
41	CREATE TRIGGER docs_home_identities_keep_delete BEFORE DELETE ON docs_home_identities BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
42	CREATE TRIGGER docs_identity_links_keep_update BEFORE UPDATE ON docs_identity_links BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
43	CREATE TRIGGER docs_identity_links_keep_delete BEFORE DELETE ON docs_identity_links BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
44	CREATE TRIGGER docs_documents_principal_insert BEFORE INSERT ON documents WHEN NEW.owner_id < 0 OR (NEW.owner_id > 0 AND NOT EXISTS(SELECT 1 FROM docs_principals WHERE id=NEW.owner_id)) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
45	CREATE TRIGGER docs_documents_principal_update BEFORE UPDATE OF owner_id ON documents WHEN NEW.owner_id < 0 OR (NEW.owner_id > 0 AND NOT EXISTS(SELECT 1 FROM docs_principals WHERE id=NEW.owner_id)) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
46	CREATE TRIGGER docs_grants_principal_insert BEFORE INSERT ON grants WHEN NEW.created_by < 0 OR (NEW.created_by > 0 AND NOT EXISTS(SELECT 1 FROM docs_principals WHERE id=NEW.created_by)) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
47	CREATE TRIGGER docs_grants_principal_update BEFORE UPDATE OF created_by ON grants WHEN NEW.created_by < 0 OR (NEW.created_by > 0 AND NOT EXISTS(SELECT 1 FROM docs_principals WHERE id=NEW.created_by)) BEGIN SELECT RAISE(ABORT,'docs principal: refused'); END;
48