lib

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

0022_projection_cache_budget.up.sql (6060B)


      1 CREATE TABLE radroots_runtime_projection_cache_budget (
      2   singleton INTEGER PRIMARY KEY CHECK(singleton=1),
      3   value_bytes INTEGER NOT NULL CHECK(value_bytes>=0),
      4   protected_generations INTEGER NOT NULL CHECK(protected_generations>=0),
      5   legacy_snapshots INTEGER NOT NULL CHECK(legacy_snapshots>=0),
      6   max_value_bytes INTEGER CHECK(max_value_bytes>0),
      7   max_protected_generations INTEGER CHECK(max_protected_generations>0),
      8   max_legacy_snapshots INTEGER CHECK(max_legacy_snapshots>0),
      9   legacy_age_ms INTEGER CHECK(legacy_age_ms>0),
     10   batch_value_ceiling INTEGER CHECK(batch_value_ceiling>=0),
     11   owner_time_ms INTEGER NOT NULL DEFAULT 0 CHECK(owner_time_ms>=0)
     12 ) STRICT;
     13 CREATE TABLE radroots_runtime_projection_cache_owners (
     14   projection_id TEXT PRIMARY KEY CHECK(length(projection_id) BETWEEN 1 AND 128)
     15 ) STRICT, WITHOUT ROWID;
     16 INSERT INTO radroots_runtime_projection_cache_budget(singleton,value_bytes,protected_generations,legacy_snapshots)
     17 SELECT 1,
     18   (SELECT coalesce(sum(length(value)),0) FROM radroots_runtime_projection_documents)
     19     +(SELECT coalesce(sum(length(value)),0) FROM radroots_runtime_projection_snapshots),
     20   (SELECT count(*) FROM radroots_runtime_projection_generations WHERE phase!='retired')
     21     +(SELECT count(*) FROM radroots_runtime_projection_checkpoints AS c WHERE NOT EXISTS
     22        (SELECT 1 FROM radroots_runtime_projection_generations AS g WHERE g.projection_id=c.projection_id AND g.generation=c.projection_generation)),
     23   (SELECT count(*) FROM radroots_runtime_projection_snapshots);
     24 CREATE INDEX radroots_runtime_projection_snapshots_generation_created_idx
     25 ON radroots_runtime_projection_snapshots(projection_id,generation,created_at_unix_ms,snapshot_id);
     26 CREATE TRIGGER radroots_runtime_projection_cache_document_insert AFTER INSERT ON radroots_runtime_projection_documents
     27 BEGIN
     28   UPDATE radroots_runtime_projection_cache_budget SET value_bytes=value_bytes+(length(NEW.value)) WHERE singleton=1;
     29 END;
     30 CREATE TRIGGER radroots_runtime_projection_cache_document_delete AFTER DELETE ON radroots_runtime_projection_documents
     31 BEGIN
     32   UPDATE radroots_runtime_projection_cache_budget SET value_bytes=value_bytes+(-length(OLD.value)) WHERE singleton=1;
     33 END;
     34 CREATE TRIGGER radroots_runtime_projection_cache_document_update AFTER UPDATE OF value ON radroots_runtime_projection_documents
     35 BEGIN
     36   UPDATE radroots_runtime_projection_cache_budget SET value_bytes=value_bytes+(length(NEW.value)-length(OLD.value)) WHERE singleton=1;
     37 END;
     38 CREATE TRIGGER radroots_runtime_projection_cache_snapshot_insert AFTER INSERT ON radroots_runtime_projection_snapshots
     39 BEGIN
     40   UPDATE radroots_runtime_projection_cache_budget SET value_bytes=value_bytes+(length(NEW.value)),legacy_snapshots=legacy_snapshots+(1) WHERE singleton=1;
     41 END;
     42 CREATE TRIGGER radroots_runtime_projection_cache_snapshot_delete AFTER DELETE ON radroots_runtime_projection_snapshots
     43 BEGIN
     44   UPDATE radroots_runtime_projection_cache_budget SET value_bytes=value_bytes+(-length(OLD.value)),legacy_snapshots=legacy_snapshots+(-1) WHERE singleton=1;
     45 END;
     46 CREATE TRIGGER radroots_runtime_projection_cache_generation_insert AFTER INSERT ON radroots_runtime_projection_generations
     47 BEGIN
     48   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations+(NEW.phase!='retired')-(EXISTS(SELECT 1 FROM radroots_runtime_projection_checkpoints WHERE projection_id=NEW.projection_id AND projection_generation=NEW.generation)) WHERE singleton=1;
     49 END;
     50 CREATE TRIGGER radroots_runtime_projection_cache_generation_delete AFTER DELETE ON radroots_runtime_projection_generations
     51 BEGIN
     52   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations-(OLD.phase!='retired')+(EXISTS(SELECT 1 FROM radroots_runtime_projection_checkpoints WHERE projection_id=OLD.projection_id AND projection_generation=OLD.generation)) WHERE singleton=1;
     53 END;
     54 CREATE TRIGGER radroots_runtime_projection_cache_generation_update AFTER UPDATE OF phase ON radroots_runtime_projection_generations
     55 BEGIN
     56   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations+(NEW.phase!='retired')-(OLD.phase!='retired') WHERE singleton=1;
     57 END;
     58 CREATE TRIGGER radroots_runtime_projection_cache_checkpoint_insert AFTER INSERT ON radroots_runtime_projection_checkpoints
     59 BEGIN
     60   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations+NOT(EXISTS(SELECT 1 FROM radroots_runtime_projection_generations WHERE projection_id=NEW.projection_id AND generation=NEW.projection_generation)) WHERE singleton=1;
     61 END;
     62 CREATE TRIGGER radroots_runtime_projection_cache_checkpoint_delete AFTER DELETE ON radroots_runtime_projection_checkpoints
     63 BEGIN
     64   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations-NOT(EXISTS(SELECT 1 FROM radroots_runtime_projection_generations WHERE projection_id=OLD.projection_id AND generation=OLD.projection_generation)) WHERE singleton=1;
     65 END;
     66 CREATE TRIGGER radroots_runtime_projection_cache_checkpoint_update AFTER UPDATE OF projection_generation ON radroots_runtime_projection_checkpoints
     67 BEGIN
     68   UPDATE radroots_runtime_projection_cache_budget SET protected_generations=protected_generations+NOT(EXISTS(SELECT 1 FROM radroots_runtime_projection_generations WHERE projection_id=NEW.projection_id AND generation=NEW.projection_generation))-NOT(EXISTS(SELECT 1 FROM radroots_runtime_projection_generations WHERE projection_id=OLD.projection_id AND generation=OLD.projection_generation)) WHERE singleton=1;
     69 END;
     70 CREATE TRIGGER radroots_runtime_projection_cache_capacity_guard BEFORE UPDATE ON radroots_runtime_projection_cache_budget
     71 WHEN (NEW.value_bytes>OLD.value_bytes AND NEW.value_bytes>coalesce(NEW.batch_value_ceiling,NEW.max_value_bytes))
     72   OR (NEW.protected_generations>OLD.protected_generations AND NEW.protected_generations>NEW.max_protected_generations)
     73   OR (NEW.legacy_snapshots>OLD.legacy_snapshots AND NEW.legacy_snapshots>NEW.max_legacy_snapshots)
     74 BEGIN SELECT RAISE(ABORT,'projection cache capacity exceeded'); END;