lib

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

0020_indexed_visibility_reads.up.sql (3675B)


      1 ALTER TABLE radroots_runtime_visibility_progress
      2 ADD COLUMN raw_events INTEGER NOT NULL DEFAULT 0 CHECK (raw_events >= 0);
      3 ALTER TABLE radroots_runtime_visibility_progress
      4 ADD COLUMN verified_events INTEGER NOT NULL DEFAULT 0 CHECK (verified_events >= 0);
      5 ALTER TABLE radroots_runtime_visibility_progress
      6 ADD COLUMN visible_events INTEGER NOT NULL DEFAULT 0 CHECK (visible_events >= 0);
      7 
      8 -- A one-time forward upgrade rebuilds membership through bounded source work.
      9 -- No event payload is decoded or updated by migration.
     10 UPDATE radroots_runtime_visibility_progress
     11 SET backfill_target = (SELECT sequence_head FROM radroots_runtime_source_generations
     12                        WHERE generation = source_generation),
     13     backfill_sequence = 0, replay_epoch = 0,
     14     replay_sequence = 9223372036854775807, ready = 0;
     15 
     16 CREATE TABLE radroots_runtime_visibility_members (
     17   source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation),
     18   source_sequence INTEGER NOT NULL CHECK (source_sequence > 0),
     19   event_id BLOB NOT NULL UNIQUE REFERENCES radroots_runtime_visibility_events(event_id),
     20   admission_stage TEXT NOT NULL CHECK (admission_stage IN ('raw', 'verified', 'visible')),
     21   visible INTEGER NOT NULL CHECK (visible IN (0, 1)),
     22   PRIMARY KEY (source_generation, source_sequence)
     23 ) STRICT, WITHOUT ROWID;
     24 
     25 CREATE INDEX radroots_runtime_visibility_members_page_idx
     26 ON radroots_runtime_visibility_members(source_generation, visible, source_sequence);
     27 
     28 CREATE UNIQUE INDEX radroots_runtime_visibility_members_event_idx
     29 ON radroots_runtime_visibility_members(source_generation, event_id);
     30 
     31 CREATE TABLE radroots_runtime_visibility_heads (
     32   source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation),
     33   head_coordinate BLOB NOT NULL,
     34   source_sequence INTEGER NOT NULL CHECK (source_sequence > 0),
     35   event_id BLOB NOT NULL REFERENCES radroots_runtime_visibility_events(event_id),
     36   PRIMARY KEY (source_generation, head_coordinate)
     37 ) STRICT, WITHOUT ROWID;
     38 
     39 CREATE TABLE radroots_runtime_visibility_pending (
     40   source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation),
     41   target_type INTEGER NOT NULL CHECK (target_type IN (0, 1)),
     42   target_key BLOB NOT NULL CHECK (
     43     (target_type = 0 AND length(target_key) = 32) OR
     44     (target_type = 1 AND length(target_key) BETWEEN 1 AND 4096)),
     45   PRIMARY KEY (source_generation, target_type, target_key)
     46 ) STRICT, WITHOUT ROWID;
     47 
     48 CREATE TRIGGER radroots_runtime_visibility_members_count_insert
     49 AFTER INSERT ON radroots_runtime_visibility_members
     50 BEGIN
     51   UPDATE radroots_runtime_visibility_progress
     52   SET raw_events = raw_events + 1,
     53       verified_events = verified_events + (NEW.admission_stage != 'raw'),
     54       visible_events = visible_events + NEW.visible
     55   WHERE source_generation = NEW.source_generation;
     56 END;
     57 
     58 CREATE TRIGGER radroots_runtime_visibility_members_count_update
     59 AFTER UPDATE OF admission_stage, visible ON radroots_runtime_visibility_members
     60 BEGIN
     61   UPDATE radroots_runtime_visibility_progress
     62   SET verified_events = verified_events + (NEW.admission_stage != 'raw') - (OLD.admission_stage != 'raw'),
     63       visible_events = visible_events + NEW.visible - OLD.visible
     64   WHERE source_generation = NEW.source_generation;
     65 END;
     66 
     67 CREATE TRIGGER radroots_runtime_visibility_empty_source
     68 AFTER INSERT ON radroots_runtime_source_generations
     69 WHEN NEW.sequence_head = 0 AND NEW.mutation_epoch = 0
     70 BEGIN
     71   INSERT INTO radroots_runtime_visibility_progress
     72   (source_generation, backfill_target, backfill_sequence, replay_epoch, replay_sequence, ready)
     73   VALUES (NEW.generation, 0, 0, 0, 9223372036854775807, 1);
     74 END;