0019_canonical_visibility_index.up.sql (5105B)
1 ALTER TABLE radroots_runtime_events 2 ADD COLUMN last_mutation_epoch INTEGER NOT NULL DEFAULT 0 CHECK (last_mutation_epoch >= 0); 3 4 CREATE INDEX radroots_runtime_events_mutation_idx 5 ON radroots_runtime_events(source_generation, last_mutation_epoch, source_sequence); 6 7 CREATE TABLE radroots_runtime_visibility_progress ( 8 source_generation BLOB PRIMARY KEY NOT NULL 9 REFERENCES radroots_runtime_source_generations(generation), 10 schema_version INTEGER NOT NULL DEFAULT 1 CHECK (schema_version = 1), 11 backfill_target INTEGER NOT NULL CHECK (backfill_target >= 0), 12 backfill_sequence INTEGER NOT NULL DEFAULT 0 CHECK (backfill_sequence >= 0), 13 replay_epoch INTEGER NOT NULL CHECK (replay_epoch >= 0), 14 replay_sequence INTEGER NOT NULL CHECK (replay_sequence >= 0), 15 ready INTEGER NOT NULL DEFAULT 0 CHECK (ready IN (0, 1)), 16 CHECK (backfill_sequence <= backfill_target) 17 ) STRICT, WITHOUT ROWID; 18 19 CREATE TABLE radroots_runtime_visibility_events ( 20 source_generation BLOB NOT NULL 21 REFERENCES radroots_runtime_source_generations(generation), 22 source_sequence INTEGER NOT NULL CHECK (source_sequence > 0), 23 event_id BLOB NOT NULL UNIQUE REFERENCES radroots_runtime_events(event_id), 24 last_mutation_epoch INTEGER NOT NULL CHECK (last_mutation_epoch >= 0), 25 author BLOB NOT NULL CHECK (length(author) = 32), 26 created_at BLOB NOT NULL CHECK (length(created_at) = 8), 27 admission_stage TEXT NOT NULL CHECK (admission_stage IN ('raw', 'verified', 'visible')), 28 head_coordinate BLOB, 29 deletion_coordinate TEXT, 30 ephemeral INTEGER NOT NULL CHECK (ephemeral IN (0, 1)), 31 deletion_request INTEGER NOT NULL CHECK (deletion_request IN (0, 1)), 32 PRIMARY KEY (source_generation, source_sequence) 33 ) STRICT, WITHOUT ROWID; 34 35 CREATE INDEX radroots_runtime_visibility_events_head_idx 36 ON radroots_runtime_visibility_events( 37 source_generation, head_coordinate, created_at DESC, event_id ASC 38 ); 39 40 CREATE INDEX radroots_runtime_visibility_events_stage_idx 41 ON radroots_runtime_visibility_events(source_generation, admission_stage, source_sequence); 42 43 CREATE TABLE radroots_runtime_visibility_deletion_events ( 44 source_generation BLOB NOT NULL, 45 target_event BLOB NOT NULL CHECK (length(target_event) = 32), 46 author BLOB NOT NULL CHECK (length(author) = 32), 47 request_id BLOB NOT NULL 48 REFERENCES radroots_runtime_visibility_events(event_id) ON DELETE CASCADE, 49 PRIMARY KEY (source_generation, target_event, author, request_id) 50 ) STRICT, WITHOUT ROWID; 51 52 CREATE INDEX radroots_runtime_visibility_deletion_events_request_idx 53 ON radroots_runtime_visibility_deletion_events(request_id); 54 55 CREATE TABLE radroots_runtime_visibility_deletion_addresses ( 56 source_generation BLOB NOT NULL, 57 target_coordinate TEXT NOT NULL, 58 author BLOB NOT NULL CHECK (length(author) = 32), 59 created_at BLOB NOT NULL CHECK (length(created_at) = 8), 60 request_id BLOB NOT NULL 61 REFERENCES radroots_runtime_visibility_events(event_id) ON DELETE CASCADE, 62 PRIMARY KEY (source_generation, target_coordinate, author, created_at, request_id) 63 ) STRICT, WITHOUT ROWID; 64 65 CREATE INDEX radroots_runtime_visibility_deletion_addresses_request_idx 66 ON radroots_runtime_visibility_deletion_addresses(request_id); 67 68 DROP TRIGGER radroots_runtime_events_mutation_insert; 69 DROP TRIGGER radroots_runtime_events_mutation_update; 70 71 CREATE TRIGGER radroots_runtime_events_mutation_insert 72 AFTER INSERT ON radroots_runtime_events 73 BEGIN 74 SELECT CASE WHEN ( 75 SELECT mutation_epoch FROM radroots_runtime_source_generations 76 WHERE generation = NEW.source_generation 77 ) = 9223372036854775807 78 THEN RAISE(ABORT, 'runtime event mutation epoch overflow') END; 79 UPDATE radroots_runtime_source_generations 80 SET mutation_epoch = mutation_epoch + 1 81 WHERE generation = NEW.source_generation; 82 UPDATE radroots_runtime_events SET last_mutation_epoch = ( 83 SELECT mutation_epoch FROM radroots_runtime_source_generations 84 WHERE generation = NEW.source_generation 85 ) WHERE event_id = NEW.event_id; 86 UPDATE radroots_runtime_visibility_progress SET ready = 0 87 WHERE source_generation = NEW.source_generation; 88 END; 89 90 CREATE TRIGGER radroots_runtime_events_mutation_update 91 AFTER UPDATE OF admission_stage, admitted_contract_id, admitted_registry_version 92 ON radroots_runtime_events 93 WHEN NEW.admission_stage IS NOT OLD.admission_stage 94 OR NEW.admitted_contract_id IS NOT OLD.admitted_contract_id 95 OR NEW.admitted_registry_version IS NOT OLD.admitted_registry_version 96 BEGIN 97 SELECT CASE WHEN ( 98 SELECT mutation_epoch FROM radroots_runtime_source_generations 99 WHERE generation = NEW.source_generation 100 ) = 9223372036854775807 101 THEN RAISE(ABORT, 'runtime event mutation epoch overflow') END; 102 UPDATE radroots_runtime_source_generations 103 SET mutation_epoch = mutation_epoch + 1 104 WHERE generation = NEW.source_generation; 105 UPDATE radroots_runtime_events SET last_mutation_epoch = ( 106 SELECT mutation_epoch FROM radroots_runtime_source_generations 107 WHERE generation = NEW.source_generation 108 ) WHERE event_id = NEW.event_id; 109 UPDATE radroots_runtime_visibility_progress SET ready = 0 110 WHERE source_generation = NEW.source_generation; 111 END;