whiskers.git/crates/whiskers-store/migrations/0006_forgetting_the

whiskers.git / crates / whiskers-store / migrations / 0006_forgetting_the_turn.sql

0006_forgetting_the_turn.sqlsource180 lines · 14.2 KB · raw

Version 6: forgetting reaches the conversation it came from; the log has an index by time and can be cleared by age; what waits to be thought about survives a restart.

Forgetting empties the turns a memory was told in

A memory is told in a turn of the conversation, and the turn has an identity (Entry.turn, on every line of it, and on the memory's mentions). Forgetting a memory writes those identities as tombstone_turn rows (never words), and the schema empties the turn's text wherever the identity is written, and in any line of the turn that arrives later. A memory told before turns had identities has none, and nothing of its conversation can be found: that is a fact about the data, not a rule here.

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 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;

What waits to be thought about

After a turn the memory work reads the exchange and decides what to remember; it can fail or be cut short, so the exchange is kept until it has been dealt with and survives a restart. Forgetting drops these (see above): they hold her 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;

The chat has been emptied for these forgettings

The identities of the forgotten memories this copy of the session has been emptied for (a JSON array). See 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 '[]';

The log by time

When a line was written, read from the line itself, so there is no second copy of it to disagree, and indexed: 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);

Whether a line still has its content (retention empties old ones into Expired), with an index of just those by time, so 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;

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;

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;

What was said in a turn of a forgotten memory is emptied: her words, what the model wrote and what was said back, what a look at her pictures found, and a memory proposed from it; the pictures she showed are let go (a picture nothing else uses is deleted with the last line that showed it, journal_picture_unused). The lines stay, with 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;

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;

99DROP TRIGGER tombstone_forgets;
100DROP TRIGGER journal_line_not_of_the_forgotten;

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;

Version 2's trigger, with the turns of forgotten memories as well: a line of such a turn that arrives after the 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;