1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- rss-kakapo: queue each toot once, not once per inbox.
--
-- toot_queue held a full copy of a toot (sender, date, status id and the
-- rendered content) for every inbox it was to be delivered to, so a feed
-- followed from many instances stored its every post that many times over
-- until all were sent. The toot is in toots already, written in the same
-- transaction (dal.PublishItem); a delivery now only says which toot goes to
-- which inbox, and the sender and content are read from there.
--
-- A delivery refers to its toot by status_id, which idx_140 keeps unique, and
-- goes with it: purging a feed's old toots, or deleting its account, drops
-- what was still to be sent of them. idx_170 makes that cascade a lookup
-- instead of a scan of the queue.
CREATE TABLE toot_deliveries
(
    id        INTEGER PRIMARY KEY,
    status_id TEXT NOT NULL,
    to_inbox  TEXT NOT NULL,
    FOREIGN KEY (status_id) REFERENCES toots (status_id) ON DELETE CASCADE
);
CREATE INDEX idx_170 ON toot_deliveries (status_id);

INSERT INTO toot_deliveries (status_id, to_inbox)
SELECT status_id, to_inbox FROM toot_queue ORDER BY id;

DROP TABLE toot_queue;