Skip to content
Source
Sign inSign up
pingdotgg/t3code
Open History to see the last commit

0001_cache.sql

160 lines8.4 KB

1-- Primary D1 owns policy and accounting. Back up policies separately from cache data.
2CREATE TABLE deployment (
3 id INTEGER PRIMARY KEY CHECK (id = 1),
4 enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
5 writes_enabled INTEGER NOT NULL DEFAULT 1 CHECK (writes_enabled IN (0, 1)),
6 byte_limit INTEGER NOT NULL DEFAULT 8000000000 CHECK (byte_limit > 0),
7 entry_limit INTEGER NOT NULL DEFAULT 20000 CHECK (entry_limit > 0),
8 association_limit INTEGER NOT NULL DEFAULT 20000 CHECK (association_limit > 0),
9 retention_high_water_seconds INTEGER NOT NULL DEFAULT 604800,
10 charged_bytes INTEGER NOT NULL DEFAULT 0 CHECK (charged_bytes >= 0),
11 entry_count INTEGER NOT NULL DEFAULT 0 CHECK (entry_count >= 0),
12 association_count INTEGER NOT NULL DEFAULT 0 CHECK (association_count >= 0)
13);
14INSERT INTO deployment (id) VALUES (1);
15CREATE TABLE scopes (
16 scope_id TEXT PRIMARY KEY,
17 endpoint TEXT NOT NULL UNIQUE,
18 repository TEXT NOT NULL,
19 repository_id TEXT NOT NULL,
20 repository_owner_id TEXT NOT NULL,
21 branch TEXT NOT NULL,
22 enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
23 writes_enabled INTEGER NOT NULL DEFAULT 1 CHECK (writes_enabled IN (0, 1)),
24 policy_version INTEGER NOT NULL DEFAULT 1,
25 retention_seconds INTEGER NOT NULL DEFAULT 604800 CHECK (retention_seconds > 0),
26 byte_limit INTEGER NOT NULL DEFAULT 8000000000 CHECK (byte_limit > 0),
27 entry_limit INTEGER NOT NULL DEFAULT 20000 CHECK (entry_limit > 0),
28 association_limit INTEGER NOT NULL DEFAULT 20000 CHECK (association_limit > 0),
29 charged_bytes INTEGER NOT NULL DEFAULT 0 CHECK (charged_bytes >= 0),
30 entry_count INTEGER NOT NULL DEFAULT 0 CHECK (entry_count >= 0),
31 association_count INTEGER NOT NULL DEFAULT 0 CHECK (association_count >= 0)
32);
33CREATE TABLE generations (
34 generation_id TEXT PRIMARY KEY,
35 scope_id TEXT NOT NULL REFERENCES scopes(scope_id),
36 state TEXT NOT NULL CHECK (state IN ('uploading', 'ready', 'retired', 'deleting')),
37 policy_version INTEGER NOT NULL,
38 token_exp INTEGER NOT NULL,
39 lease_until INTEGER NOT NULL,
40 gc_after INTEGER NOT NULL,
41 gc_claim TEXT,
42 key BLOB,
43 secondary_key BLOB,
44 value_object TEXT NOT NULL UNIQUE,
45 blob_object TEXT NOT NULL UNIQUE,
46 blob_id TEXT,
47 upload_id TEXT,
48 value_size INTEGER NOT NULL DEFAULT 0 CHECK (value_size >= 0),
49 blob_size INTEGER NOT NULL DEFAULT 0 CHECK (blob_size >= 0),
50 charged_bytes INTEGER NOT NULL CHECK (charged_bytes >= 0),
51 expires_at INTEGER,
52 retired_at INTEGER,
53 UNIQUE (scope_id, generation_id)
54);
55CREATE UNIQUE INDEX generations_blob ON generations(scope_id, blob_id) WHERE blob_id IS NOT NULL;
56CREATE INDEX generations_gc ON generations(gc_after);
57CREATE TABLE entries (
58 scope_id TEXT NOT NULL REFERENCES scopes(scope_id),
59 key BLOB NOT NULL CHECK (typeof(key) = 'blob'),
60 generation_id TEXT NOT NULL,
61 PRIMARY KEY (scope_id, key),
62 FOREIGN KEY (scope_id, generation_id) REFERENCES generations(scope_id, generation_id) ON DELETE CASCADE
63) WITHOUT ROWID;
64CREATE INDEX entries_generation ON entries(generation_id);
65CREATE TABLE associations (
66 scope_id TEXT NOT NULL REFERENCES scopes(scope_id),
67 secondary_key BLOB NOT NULL CHECK (typeof(secondary_key) = 'blob'),
68 target_key BLOB NOT NULL CHECK (typeof(target_key) = 'blob'),
69 PRIMARY KEY (scope_id, secondary_key)
70) WITHOUT ROWID;
71CREATE INDEX associations_target ON associations(scope_id, target_key);
72CREATE TABLE maintenance (id INTEGER PRIMARY KEY CHECK (id = 1), scope_id TEXT NOT NULL, secondary_key BLOB NOT NULL);
73INSERT INTO maintenance VALUES (1, '', X'');
74​
75-- Parenthesize CASE expressions so the D1 REST parser preserves each trigger body.
76-- Lifecycle rules must never shorten the life of an already published generation.
77CREATE TRIGGER initial_retention AFTER INSERT ON scopes BEGIN
78 UPDATE deployment SET retention_high_water_seconds = max(retention_high_water_seconds, NEW.retention_seconds);
79END;
80CREATE TRIGGER increased_retention AFTER UPDATE OF retention_seconds ON scopes BEGIN
81 UPDATE deployment SET retention_high_water_seconds = max(retention_high_water_seconds, NEW.retention_seconds);
82END;
83CREATE TRIGGER scope_limit BEFORE INSERT ON scopes
84WHEN NOT EXISTS (SELECT 1 FROM scopes WHERE scope_id = NEW.scope_id)
85BEGIN
86 SELECT (CASE WHEN (SELECT count(*) FROM scopes) >= 100 THEN RAISE(ABORT, 'cache_scope_limit') END);
87END;
88​
89-- Direct policy edits must invalidate uploads that captured the previous policy.
90CREATE TRIGGER changed_policy AFTER UPDATE OF endpoint, repository_id, repository_owner_id, branch,
91 enabled, writes_enabled, retention_seconds, byte_limit, entry_limit, association_limit ON scopes
92WHEN NEW.policy_version = OLD.policy_version
93BEGIN
94 UPDATE scopes SET policy_version = OLD.policy_version + 1 WHERE scope_id = NEW.scope_id;
95END;
96​
97CREATE TRIGGER reserve_capacity BEFORE INSERT ON generations BEGIN
98 SELECT (CASE WHEN NOT EXISTS (
99 SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id
100 AND s.enabled = 1 AND s.writes_enabled = 1 AND d.enabled = 1 AND d.writes_enabled = 1
101 AND s.policy_version = NEW.policy_version AND NEW.token_exp > unixepoch()
102 AND s.charged_bytes + NEW.charged_bytes <= s.byte_limit
103 AND d.charged_bytes + NEW.charged_bytes <= d.byte_limit
104 ) THEN RAISE(ABORT, 'cache_admission_denied') END);
105END;
106CREATE TRIGGER charge_generation AFTER INSERT ON generations BEGIN
107 UPDATE scopes SET charged_bytes = charged_bytes + NEW.charged_bytes WHERE scope_id = NEW.scope_id;
108 UPDATE deployment SET charged_bytes = charged_bytes + NEW.charged_bytes;
109END;
110CREATE TRIGGER adjust_generation AFTER UPDATE OF charged_bytes ON generations BEGIN
111 UPDATE scopes SET charged_bytes = charged_bytes + NEW.charged_bytes - OLD.charged_bytes WHERE scope_id = NEW.scope_id;
112 UPDATE deployment SET charged_bytes = charged_bytes + NEW.charged_bytes - OLD.charged_bytes;
113END;
114CREATE TRIGGER release_generation AFTER DELETE ON generations BEGIN
115 UPDATE scopes SET charged_bytes = charged_bytes - OLD.charged_bytes WHERE scope_id = OLD.scope_id;
116 UPDATE deployment SET charged_bytes = charged_bytes - OLD.charged_bytes;
117END;
118​
119CREATE TRIGGER count_entry BEFORE INSERT ON entries
120WHEN NOT EXISTS (SELECT 1 FROM entries WHERE scope_id = NEW.scope_id AND key = NEW.key)
121BEGIN
122 SELECT (CASE WHEN EXISTS (
123 SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id
124 AND (s.entry_count >= s.entry_limit OR d.entry_count >= d.entry_limit)
125 ) THEN RAISE(ABORT, 'cache_entry_limit') END);
126 UPDATE scopes SET entry_count = entry_count + 1 WHERE scope_id = NEW.scope_id;
127 UPDATE deployment SET entry_count = entry_count + 1;
128END;
129CREATE TRIGGER uncount_entry AFTER DELETE ON entries BEGIN
130 UPDATE scopes SET entry_count = entry_count - 1 WHERE scope_id = OLD.scope_id;
131 UPDATE deployment SET entry_count = entry_count - 1;
132END;
133CREATE TRIGGER count_association BEFORE INSERT ON associations
134WHEN NOT EXISTS (SELECT 1 FROM associations WHERE scope_id = NEW.scope_id AND secondary_key = NEW.secondary_key)
135BEGIN
136 SELECT (CASE WHEN EXISTS (
137 SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id
138 AND (s.association_count >= s.association_limit OR d.association_count >= d.association_limit)
139 ) THEN RAISE(ABORT, 'cache_association_limit') END);
140 UPDATE scopes SET association_count = association_count + 1 WHERE scope_id = NEW.scope_id;
141 UPDATE deployment SET association_count = association_count + 1;
142END;
143CREATE TRIGGER uncount_association AFTER DELETE ON associations BEGIN
144 UPDATE scopes SET association_count = association_count - 1 WHERE scope_id = OLD.scope_id;
145 UPDATE deployment SET association_count = association_count - 1;
146END;
147​
148-- All publication mutations inherit the guarded state change's transaction.
149-- A zero-row guard fires no trigger. A quota failure rolls every mutation back.
150CREATE TRIGGER publish_generation AFTER UPDATE OF state ON generations
151WHEN OLD.state = 'uploading' AND NEW.state = 'ready'
152BEGIN
153 UPDATE generations SET state = 'retired', retired_at = unixepoch(),
154 gc_after = (CASE WHEN expires_at > unixepoch() THEN unixepoch() + 600 ELSE unixepoch() END)
155 WHERE generation_id = (SELECT generation_id FROM entries WHERE scope_id = NEW.scope_id AND key = NEW.key) AND state = 'ready';
156 INSERT INTO entries (scope_id, key, generation_id) VALUES (NEW.scope_id, NEW.key, NEW.generation_id)
157 ON CONFLICT (scope_id, key) DO UPDATE SET generation_id = excluded.generation_id;
158 INSERT INTO associations (scope_id, secondary_key, target_key) VALUES (NEW.scope_id, NEW.secondary_key, NEW.key)
159 ON CONFLICT (scope_id, secondary_key) DO UPDATE SET target_key = excluded.target_key;
160END;