0002_forgetting_empties_the_log.sqlVersion 2: forgetting takes the memory's words out of the parents' log too.
A log line that names a memory's words (Remembered, PutAway, Restored, Recalled, and the description
of a picture it was made from) keeps its place, its time and who wrote it, and loses the words: they become
empty. The line finds the memory by its identity (gid), which the lines now carry. A line written before
they did is found by its exact words, the memory's current ones and its older ones. Forgot carries no words
at all. Both a forgetting that arrives and a line that arrives after it (from a device that had not heard of
the forgetting yet) are covered, so there is no order of arrival that leaves the words in the log.
The names of the pictures a forgotten memory had (names only, made from a time and a counter): what lets a description of one that arrives late be recognised. Holds no content.
A line written before Forgot stopped carrying words.
19UPDATE journal_line SET entry = json_remove(entry, '$.event.Forgot.fact') WHERE json_type(entry, '$.event.Forgot.fact') IS NOT NULL;
21DROP TRIGGER tombstone_deletes_the_fact;
Writing a tombstone empties the words from the log, then deletes the memory, and with it (ON DELETE CASCADE) every mention, revision, embedding and picture link. There is no state that holds both. The order matters: the log is emptied by what the memory still says.
26CREATE TRIGGER tombstone_forgets AFTER INSERT ON tombstone 27BEGIN 28 INSERT OR IGNORE INTO tombstone_picture (gid, picture_id) SELECT gid, picture_id FROM fact_picture WHERE gid = NEW.gid; 29 UPDATE journal_line SET entry = json_set(entry, '$.event.Remembered.fact', '') 30 WHERE json_type(entry, '$.event.Remembered.fact') = 'text' AND json_extract(entry, '$.event.Remembered.fact') <> '' 31 AND (json_extract(entry, '$.event.Remembered.gid') = NEW.gid 32 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))); 33 UPDATE journal_line SET entry = json_set(entry, '$.event.PutAway.fact', '') 34 WHERE json_type(entry, '$.event.PutAway.fact') = 'text' AND json_extract(entry, '$.event.PutAway.fact') <> '' 35 AND (json_extract(entry, '$.event.PutAway.gid') = NEW.gid 36 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))); 37 UPDATE journal_line SET entry = json_set(entry, '$.event.Restored.fact', '') 38 WHERE json_type(entry, '$.event.Restored.fact') = 'text' AND json_extract(entry, '$.event.Restored.fact') <> '' 39 AND (json_extract(entry, '$.event.Restored.gid') = NEW.gid 40 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))); 41 UPDATE journal_line SET entry = json_set(entry, '$.event.NotRemembered.fact', '') 42 WHERE json_type(entry, '$.event.NotRemembered.fact') = 'text' AND json_extract(entry, '$.event.NotRemembered.fact') <> '' 43 AND (json_extract(entry, '$.event.NotRemembered.gid') = NEW.gid 44 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))); 45 UPDATE journal_line SET entry = json_set(entry, '$.event.Recalled.facts', json(( 46 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) 47 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 48 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key))) 49 WHERE json_type(entry, '$.event.Recalled.facts') = 'array' 50 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 51 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key 52 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)))); 53 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 54 WHERE json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> '' 55 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.PictureSeen.pictures') p 56 WHERE p.value IN (SELECT picture_id FROM tombstone_picture WHERE gid = NEW.gid)); 57 DELETE FROM fact WHERE gid = NEW.gid; 58END;
A line that arrives after the memory was forgotten (from a device that had not heard of it yet) is emptied as it is written.
62CREATE TRIGGER journal_line_not_of_the_forgotten AFTER INSERT ON journal_line 63BEGIN 64 UPDATE journal_line SET entry = json_set(entry, '$.event.Remembered.fact', '') 65 WHERE position = NEW.position AND json_type(entry, '$.event.Remembered.fact') = 'text' AND json_extract(entry, '$.event.Remembered.fact') <> '' 66 AND json_extract(entry, '$.event.Remembered.gid') IN (SELECT gid FROM tombstone); 67 UPDATE journal_line SET entry = json_set(entry, '$.event.PutAway.fact', '') 68 WHERE position = NEW.position AND json_type(entry, '$.event.PutAway.fact') = 'text' AND json_extract(entry, '$.event.PutAway.fact') <> '' 69 AND json_extract(entry, '$.event.PutAway.gid') IN (SELECT gid FROM tombstone); 70 UPDATE journal_line SET entry = json_set(entry, '$.event.Restored.fact', '') 71 WHERE position = NEW.position AND json_type(entry, '$.event.Restored.fact') = 'text' AND json_extract(entry, '$.event.Restored.fact') <> '' 72 AND json_extract(entry, '$.event.Restored.gid') IN (SELECT gid FROM tombstone); 73 UPDATE journal_line SET entry = json_set(entry, '$.event.Recalled.facts', json(( 74 SELECT json_group_array(CASE WHEN g.value IN (SELECT gid FROM tombstone) THEN '' ELSE f.value END) 75 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 76 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key))) 77 WHERE position = NEW.position AND json_type(entry, '$.event.Recalled.facts') = 'array' 78 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.Recalled.facts') f 79 LEFT JOIN json_each(journal_line.entry, '$.event.Recalled.gids') g ON g.key = f.key 80 WHERE f.value <> '' AND (g.value IN (SELECT gid FROM tombstone))); 81 UPDATE journal_line SET entry = json_set(entry, '$.event.PictureSeen.description', '') 82 WHERE position = NEW.position AND json_type(entry, '$.event.PictureSeen.description') = 'text' AND json_extract(entry, '$.event.PictureSeen.description') <> '' 83 AND EXISTS (SELECT 1 FROM json_each(journal_line.entry, '$.event.PictureSeen.pictures') p 84 WHERE p.value IN (SELECT picture_id FROM tombstone_picture)); 85 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; 86END;