1-- Version 2: forgetting takes the memory's words out of the parents' log too.
2--
3-- A log line that names a memory's words (`Remembered`, `PutAway`, `Restored`, `Recalled`, and the description
4-- of a picture it was made from) keeps its place, its time and who wrote it, and loses the words: they become
5-- empty. The line finds the memory by its identity (`gid`), which the lines now carry. A line written before
6-- they did is found by its exact words, the memory's current ones and its older ones. `Forgot` carries no words
7-- at all. Both a forgetting that arrives and a line that arrives after it (from a device that had not heard of
8-- the forgetting yet) are covered, so there is no order of arrival that leaves the words in the log.
9
10-- The names of the pictures a forgotten memory had (names only, made from a time and a counter): what lets a
11-- description of one that arrives late be recognised. Holds no content.
12CREATE TABLE tombstone_picture (
13    gid TEXT NOT NULL,
14    picture_id TEXT NOT NULL,
15    PRIMARY KEY (gid, picture_id)
16) STRICT, WITHOUT ROWID;
17
18-- 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;
20
21DROP TRIGGER tombstone_deletes_the_fact;
22
23-- Writing a tombstone empties the words from the log, then deletes the memory, and with it (ON DELETE CASCADE)
24-- every mention, revision, embedding and picture link. There is no state that holds both. The order matters:
25-- 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;
59
60-- A line that arrives after the memory was forgotten (from a device that had not heard of it yet) is emptied as
61-- 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;