lib

Core libraries for Radroots
git clone https://radroots.dev/git/lib.git
Log | Files | Refs | README

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;