BEGIN IMMEDIATE;
CREATE TABLE orientation_candidates (
session_id TEXT NOT NULL REFERENCES sessions(id),
candidate_id TEXT NOT NULL,
scope TEXT NOT NULL,
semantic TEXT NOT NULL CHECK(json_valid(semantic)),
candidate TEXT NOT NULL CHECK(json_valid(candidate)),
PRIMARY KEY(session_id, candidate_id)
) STRICT;
CREATE TABLE orientation_revisions (
session_id TEXT NOT NULL REFERENCES sessions(id),
revision_id TEXT NOT NULL,
scope TEXT NOT NULL,
candidate_id TEXT NOT NULL,
operation_id TEXT NOT NULL,
request TEXT NOT NULL CHECK(json_valid(request)),
revision TEXT NOT NULL CHECK(json_valid(revision)),
event_id INTEGER NOT NULL UNIQUE REFERENCES events(seq),
PRIMARY KEY(session_id, revision_id),
UNIQUE(session_id, operation_id),
UNIQUE(session_id, scope, revision_id),
FOREIGN KEY(session_id, candidate_id) REFERENCES orientation_candidates(session_id, candidate_id)
) STRICT;
CREATE TABLE orientation_selections (
session_id TEXT NOT NULL,
scope TEXT NOT NULL,
revision_id TEXT NOT NULL,
PRIMARY KEY(session_id, scope),
FOREIGN KEY(session_id, scope, revision_id) REFERENCES orientation_revisions(session_id, scope, revision_id)
) STRICT;
CREATE TRIGGER orientation_candidates_no_update BEFORE UPDATE ON orientation_candidates
BEGIN SELECT RAISE(ABORT, 'orientation candidates are immutable'); END;
CREATE TRIGGER orientation_candidates_no_delete BEFORE DELETE ON orientation_candidates
BEGIN SELECT RAISE(ABORT, 'orientation candidates are immutable'); END;
CREATE TRIGGER orientation_revisions_no_update BEFORE UPDATE ON orientation_revisions
BEGIN SELECT RAISE(ABORT, 'orientation revisions are immutable'); END;
CREATE TRIGGER orientation_revisions_no_delete BEFORE DELETE ON orientation_revisions
BEGIN SELECT RAISE(ABORT, 'orientation revisions are immutable'); END;
PRAGMA user_version = 11;
COMMIT;