| 1 | CREATE TABLE roster_generation ( |
| 2 | singleton INTEGER PRIMARY KEY CHECK(singleton = 1), |
| 3 | generation TEXT NOT NULL CHECK(length(generation) = 43 AND generation NOT GLOB '*[^a-zA-Z0-9_-]*') |
| 4 | ); |
| 5 | CREATE TABLE roster_consumers ( |
| 6 | consumer_id TEXT PRIMARY KEY REFERENCES team_instances(consumer_id) ON DELETE RESTRICT, |
| 7 | team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE RESTRICT, |
| 8 | app TEXT NOT NULL, |
| 9 | origin TEXT NOT NULL, |
| 10 | enabled INTEGER NOT NULL CHECK(enabled IN (0,1)), |
| 11 | FOREIGN KEY(team_id,app) REFERENCES team_instances(team_id,app) ON DELETE RESTRICT |
| 12 | ); |
| 13 | CREATE TABLE roster_tokens ( |
| 14 | token_hash BLOB PRIMARY KEY CHECK(typeof(token_hash) = 'blob' AND length(token_hash) = 32), |
| 15 | consumer_id TEXT NOT NULL REFERENCES roster_consumers(consumer_id) ON DELETE RESTRICT, |
| 16 | slot TEXT NOT NULL CHECK(slot IN ('current','previous')), |
| 17 | issued_at INTEGER NOT NULL CHECK(typeof(issued_at) = 'integer' AND issued_at > 0), |
| 18 | expires_at INTEGER, |
| 19 | UNIQUE(consumer_id,slot), |
| 20 | CHECK((slot = 'current' AND expires_at IS NULL) OR |
| 21 | (slot = 'previous' AND typeof(expires_at) = 'integer' AND expires_at > issued_at AND expires_at - issued_at <= 300)) |
| 22 | ); |
| 23 | CREATE TABLE roster_operator_receipts ( |
| 24 | operation_id TEXT PRIMARY KEY CHECK(length(operation_id) BETWEEN 1 AND 128 AND operation_id NOT GLOB '*[^a-zA-Z0-9_-]*'), |
| 25 | sequence INTEGER NOT NULL CHECK(typeof(sequence) = 'integer' AND sequence > 0), |
| 26 | operation TEXT NOT NULL CHECK(operation IN ('bootstrap','provision','rotate','disable','restore')), |
| 27 | consumer_id TEXT CHECK(consumer_id IS NULL OR (length(consumer_id) BETWEEN 1 AND 128 AND consumer_id NOT GLOB '*[^a-zA-Z0-9_-]*')), |
| 28 | generation TEXT NOT NULL CHECK(length(generation) = 43 AND generation NOT GLOB '*[^a-zA-Z0-9_-]*'), |
| 29 | record_digest BLOB NOT NULL CHECK(typeof(record_digest) = 'blob' AND length(record_digest) = 32), |
| 30 | completed_at INTEGER NOT NULL CHECK(typeof(completed_at) = 'integer' AND completed_at > 0) |
| 31 | ); |
| 32 | CREATE TRIGGER memberships_epoch_insert AFTER INSERT ON memberships BEGIN |
| 33 | SELECT CASE WHEN (SELECT roster_epoch FROM teams WHERE id = NEW.team_id) = 9223372036854775807 THEN RAISE(ABORT,'roster epoch exhausted') END; |
| 34 | UPDATE teams SET roster_epoch = roster_epoch + 1 WHERE id = NEW.team_id; |
| 35 | END; |
| 36 | CREATE TRIGGER memberships_epoch_delete AFTER DELETE ON memberships BEGIN |
| 37 | SELECT CASE WHEN (SELECT roster_epoch FROM teams WHERE id = OLD.team_id) = 9223372036854775807 THEN RAISE(ABORT,'roster epoch exhausted') END; |
| 38 | UPDATE teams SET roster_epoch = roster_epoch + 1 WHERE id = OLD.team_id; |
| 39 | END; |
| 40 | CREATE TRIGGER memberships_epoch_update AFTER UPDATE ON memberships BEGIN |
| 41 | SELECT CASE WHEN EXISTS(SELECT 1 FROM teams WHERE id IN (OLD.team_id,NEW.team_id) AND roster_epoch = 9223372036854775807) THEN RAISE(ABORT,'roster epoch exhausted') END; |
| 42 | UPDATE teams SET roster_epoch = roster_epoch + 1 WHERE id IN (OLD.team_id,NEW.team_id); |
| 43 | END; |
| 44 | |
amadan