0006_forgetting_the_turn.sql1-- Version 6: forgetting reaches the conversation it came from; the log has an index by time and can be cleared by 2-- age; what waits to be thought about survives a restart. 3-- 4-- ## Forgetting empties the turns a memory was told in 5-- 6-- A memory is told in a turn of the conversation, and the turn has an identity (`Entry.turn`, on every line of it, and 7-- on the memory's mentions). Forgetting a memory writes those identities as `tombstone_turn` rows (never words), and 8-- the schema empties the turn's text wherever the identity is written, and in any line of the turn that arrives later. 9-- A memory told before turns had identities has none, and nothing of its conversation can be found: that is a 10-- fact about the data, not a rule here. 11 12-- A mention names the turn she said the memory in. It is part of the key: the same moment on the same device in two 13-- turns is two mentions. The table is rebuilt because a key cannot be altered. 14CREATE TABLE fact_mention_with_turn ( 15 gid TEXT NOT NULL REFERENCES fact (gid) ON DELETE CASCADE, 16 at_ms INTEGER NOT NULL, 17 device TEXT NOT NULL, 18 turn TEXT NOT NULL DEFAULT '', 19 PRIMARY KEY (gid, at_ms, device, turn) 20) STRICT, WITHOUT ROWID; 21INSERT INTO fact_mention_with_turn (gid, at_ms, device) SELECT gid, at_ms, device FROM fact_mention; 22DROP TABLE fact_mention; 23ALTER TABLE fact_mention_with_turn RENAME TO fact_mention; 24 25-- ## What waits to be thought about 26-- 27-- After a turn the memory work reads the exchange and decides what to remember; it can fail or be cut short, so the 28-- exchange is kept until it has been dealt with and survives a restart. Forgetting drops these (see above): they hold her 29-- words. 30CREATE TABLE pending_reflection ( 31 id INTEGER PRIMARY KEY AUTOINCREMENT, 32 turn TEXT NOT NULL, 33 heard TEXT NOT NULL, 34 said TEXT NOT NULL, 35 description TEXT, 36 -- The names of the pictures she showed, as a JSON array. 37 pictures TEXT NOT NULL DEFAULT '[]', 38 queued_at_ms INTEGER NOT NULL 39) STRICT; 40 41-- ## The chat has been emptied for these forgettings 42-- 43-- The identities of the forgotten memories this copy of the session has been emptied for (a JSON array). See 44-- `ChatState::merge`: a copy that was not emptied for a forgetting another was emptied for cannot win. 45ALTER TABLE chat_session ADD COLUMN scrubbed TEXT NOT NULL DEFAULT '[]'; 46 47-- ## The log by time 48-- 49-- When a line was written, read from the line itself, so there is no second copy of it to disagree, and indexed: 50-- the parents' view reads a day at a time, and clearing by age finds the oldest lines without reading the rest. 51ALTER TABLE journal_line ADD COLUMN at_ms INTEGER GENERATED ALWAYS AS (json_extract(entry, '$.at_ms')) VIRTUAL; 52CREATE INDEX journal_line_by_time ON journal_line (at_ms); 53-- Whether a line still has its content (retention empties old ones into `Expired`), with an index of just those by time, so 54-- that clearing the log by age looks at lines that have something to clear and not at every line already cleared. 55ALTER TABLE journal_line ADD COLUMN live INTEGER GENERATED ALWAYS AS (json_extract(entry, '$.event') IS NOT 'Expired') VIRTUAL; 56CREATE INDEX journal_line_live_by_time ON journal_line (at_ms) WHERE live; 57-- The turn a line belongs to, likewise: forgetting a memory finds its turns' lines by it. 58ALTER TABLE journal_line ADD COLUMN turn TEXT GENERATED ALWAYS AS (json_extract(entry, '$.turn')) VIRTUAL; 59CREATE INDEX journal_line_by_turn ON journal_line (turn) WHERE turn IS NOT NULL; 60 61-- The turns of what the parents forgot, by identity: what a tombstone carries to every device. No content. 62CREATE TABLE tombstone_turn ( 63 gid TEXT NOT NULL, 64 turn TEXT NOT NULL CHECK (turn <> ''), 65 PRIMARY KEY (gid, turn) 66) STRICT, WITHOUT ROWID; 67 68-- What was said in a turn of a forgotten memory is emptied: her words, what the model wrote and what was said back, 69-- what a look at her pictures found, and a memory proposed from it; the pictures she showed are let go (a picture 70-- nothing else uses is deleted with the last line that showed it, `journal_picture_unused`). The lines stay, with 71-- their time, their writer and how the turn ended, so the log still shows that the turn happened. 72CREATE TRIGGER tombstone_turn_empties_the_turn AFTER INSERT ON tombstone_turn 73BEGIN 74 UPDATE journal_line SET entry = json_set(entry, '$.event.Heard.text', '') 75 WHERE turn = NEW.turn AND json_type(entry, '$.event.Heard.text') = 'text' AND json_extract(entry, '$.event.Heard.text') <> ''; 76 UPDATE journal_line SET entry = json_set(entry, '$.event.Heard.pictures', json('[]')) 77 WHERE turn = NEW.turn AND json_array_length(entry, '$.event.Heard.pictures') > 0; 78 UPDATE journal_line SET entry = json_set(entry, '$.event.ModelWrote.text', '') 79 WHERE turn = NEW.turn AND json_type(entry, '$.event.ModelWrote.text') = 'text' AND json_extract(entry, '$.event.ModelWrote.text') <> ''; 80 UPDATE journal_line SET entry = json_set(entry, '$.event.Said.text', '') 81 WHERE turn = NEW.turn AND json_type(entry, '$.event.Said.text') = 'text' AND json_extract(entry, '$.event.Said.text') <> ''; 82 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 83 WHERE turn = NEW.turn AND json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> ''; 84 UPDATE journal_line SET entry = json_set(entry, '$.event.NotRemembered.fact', '') 85 WHERE turn = NEW.turn AND json_type(entry, '$.event.NotRemembered.fact') = 'text' AND json_extract(entry, '$.event.NotRemembered.fact') <> ''; 86 DELETE FROM journal_picture WHERE (device, seq) IN (SELECT device, seq FROM journal_line WHERE turn = NEW.turn); 87 DELETE FROM pending_reflection WHERE turn = NEW.turn; 88END; 89 90-- A picture goes with the last line that showed it, as it goes with the last memory that has it. 91CREATE TRIGGER journal_picture_unused AFTER DELETE ON journal_picture 92BEGIN 93 DELETE FROM picture 94 WHERE id = OLD.picture_id 95 AND NOT EXISTS (SELECT 1 FROM fact_picture WHERE picture_id = OLD.picture_id) 96 AND NOT EXISTS (SELECT 1 FROM journal_picture WHERE picture_id = OLD.picture_id); 97END; 98 99DROP TRIGGER tombstone_forgets; 100DROP TRIGGER journal_line_not_of_the_forgotten; 101 102-- Version 2's trigger, with one statement more: a parent forgetting drops what waits to be reflected on. 103CREATE TRIGGER tombstone_forgets AFTER INSERT ON tombstone 104BEGIN 105 INSERT OR IGNORE INTO tombstone_picture (gid, picture_id) SELECT gid, picture_id FROM fact_picture WHERE gid = NEW.gid; 106 UPDATE journal_line SET entry = json_set(entry, '$.event.Remembered.fact', '') 107 WHERE json_type(entry, '$.event.Remembered.fact') = 'text' AND json_extract(entry, '$.event.Remembered.fact') <> '' 108 AND (json_extract(entry, '$.event.Remembered.gid') = NEW.gid 109 OR (COALESCE(json_extract(entry, '$.event.Remembered.gid'), '') = '' AND json_extract(entry, '$.event.Remembered.fact') IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid))); 110 UPDATE journal_line SET entry = json_set(entry, '$.event.PutAway.fact', '') 111 WHERE json_type(entry, '$.event.PutAway.fact') = 'text' AND json_extract(entry, '$.event.PutAway.fact') <> '' 112 AND (json_extract(entry, '$.event.PutAway.gid') = NEW.gid 113 OR (COALESCE(json_extract(entry, '$.event.PutAway.gid'), '') = '' AND json_extract(entry, '$.event.PutAway.fact') IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid))); 114 UPDATE journal_line SET entry = json_set(entry, '$.event.Restored.fact', '') 115 WHERE json_type(entry, '$.event.Restored.fact') = 'text' AND json_extract(entry, '$.event.Restored.fact') <> '' 116 AND (json_extract(entry, '$.event.Restored.gid') = NEW.gid 117 OR (COALESCE(json_extract(entry, '$.event.Restored.gid'), '') = '' AND json_extract(entry, '$.event.Restored.fact') IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid))); 118 UPDATE journal_line SET entry = json_set(entry, '$.event.NotRemembered.fact', '') 119 WHERE json_type(entry, '$.event.NotRemembered.fact') = 'text' AND json_extract(entry, '$.event.NotRemembered.fact') <> '' 120 AND (json_extract(entry, '$.event.NotRemembered.gid') = NEW.gid 121 OR (COALESCE(json_extract(entry, '$.event.NotRemembered.gid'), '') = '' AND json_extract(entry, '$.event.NotRemembered.fact') IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid))); 122 UPDATE journal_line SET entry = json_set(entry, '$.event.Recalled.facts', json(( 123 SELECT json_group_array(CASE WHEN g.value = NEW.gid OR (g.value IS NULL AND f.value IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid)) THEN '' ELSE f.value END) 124 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 125 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key))) 126 WHERE json_type(entry, '$.event.Recalled.facts') = 'array' 127 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 128 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key 129 WHERE f.value <> '' AND (g.value = NEW.gid OR (g.value IS NULL AND f.value IN (SELECT text FROM fact WHERE gid = NEW.gid UNION SELECT text FROM fact_revision WHERE gid = NEW.gid)))); 130 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 131 WHERE json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> '' 132 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.PictureSeen.pictures') p 133 WHERE p.value IN (SELECT picture_id FROM tombstone_picture WHERE gid = NEW.gid)); 134 -- A parent forgot it (a copy dropped as a duplicate has no time, and is not that): what waits to be thought about 135 -- is dropped, whole, since a waiting exchange that said the memory again would learn it back. What was said in the 136 -- turns the memory was told in is emptied when their identities are written (`tombstone_turn`), not here. 137 DELETE FROM pending_reflection WHERE NEW.at_ms <> 0; 138 DELETE FROM fact WHERE gid = NEW.gid; 139END; 140 141-- Version 2's trigger, with the turns of forgotten memories as well: a line of such a turn that arrives after the 142-- forgetting (from a device that had not heard of it) is emptied as it is written. 143CREATE TRIGGER journal_line_not_of_the_forgotten AFTER INSERT ON journal_line 144BEGIN 145 UPDATE journal_line SET entry = json_set(entry, '$.event.Remembered.fact', '') 146 WHERE position = NEW.position AND json_type(entry, '$.event.Remembered.fact') = 'text' AND json_extract(entry, '$.event.Remembered.fact') <> '' 147 AND json_extract(entry, '$.event.Remembered.gid') IN (SELECT gid FROM tombstone); 148 UPDATE journal_line SET entry = json_set(entry, '$.event.PutAway.fact', '') 149 WHERE position = NEW.position AND json_type(entry, '$.event.PutAway.fact') = 'text' AND json_extract(entry, '$.event.PutAway.fact') <> '' 150 AND json_extract(entry, '$.event.PutAway.gid') IN (SELECT gid FROM tombstone); 151 UPDATE journal_line SET entry = json_set(entry, '$.event.Restored.fact', '') 152 WHERE position = NEW.position AND json_type(entry, '$.event.Restored.fact') = 'text' AND json_extract(entry, '$.event.Restored.fact') <> '' 153 AND json_extract(entry, '$.event.Restored.gid') IN (SELECT gid FROM tombstone); 154 UPDATE journal_line SET entry = json_set(entry, '$.event.Recalled.facts', json(( 155 SELECT json_group_array(CASE WHEN g.value IN (SELECT gid FROM tombstone) THEN '' ELSE f.value END) 156 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 157 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key))) 158 WHERE position = NEW.position AND json_type(entry, '$.event.Recalled.facts') = 'array' 159 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 160 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key 161 WHERE f.value <> '' AND (g.value IN (SELECT gid FROM tombstone))); 162 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 163 WHERE position = NEW.position AND json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> '' 164 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.PictureSeen.pictures') p 165 WHERE p.value IN (SELECT picture_id FROM tombstone_picture)); 166 UPDATE journal_line SET entry = json_remove(entry, '$.event.Forgot.fact') WHERE position = NEW.position AND json_type(entry, '$.event.Forgot.fact') IS NOT NULL; 167 UPDATE journal_line SET entry = json_set(entry, '$.event.Heard.text', '') 168 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_type(entry, '$.event.Heard.text') = 'text' AND json_extract(entry, '$.event.Heard.text') <> ''; 169 UPDATE journal_line SET entry = json_set(entry, '$.event.Heard.pictures', json('[]')) 170 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_array_length(entry, '$.event.Heard.pictures') > 0; 171 UPDATE journal_line SET entry = json_set(entry, '$.event.ModelWrote.text', '') 172 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_type(entry, '$.event.ModelWrote.text') = 'text' AND json_extract(entry, '$.event.ModelWrote.text') <> ''; 173 UPDATE journal_line SET entry = json_set(entry, '$.event.Said.text', '') 174 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_type(entry, '$.event.Said.text') = 'text' AND json_extract(entry, '$.event.Said.text') <> ''; 175 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 176 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> ''; 177 UPDATE journal_line SET entry = json_set(entry, '$.event.NotRemembered.fact', '') 178 WHERE position = NEW.position AND turn IN (SELECT turn FROM tombstone_turn) AND json_type(entry, '$.event.NotRemembered.fact') = 'text' AND json_extract(entry, '$.event.NotRemembered.fact') <> ''; 179END; 180