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;