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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- rss-kakapo: a permanent ledger of every feed item ever seen.
--
-- An item is identified by its guid and by its link (normalised, see
-- itemLinkKey); it is new only if neither was seen before for the account.
-- Rows are never purged with old posts: forgetting an item is what let a feed
-- that re-dates its archive get it tooted a second time. They go only with
-- the account.
--
--   post_time: the item's date, or when it was first seen if it has none;
--     an item is tooted once post_time has come. Always in UTC, so that two
--     equal times are also equal as text.
--   status: 0 to be tooted, 1 tooted (or stored without broadcasting, when
--     the feed was followed), 2 never to be tooted (it was there before we
--     started remembering, too old, or over the burst cap).
--   post_guid_hash: the item's key in feed_posts and toots.
--   link, title, description: what the toot is made of, kept only until it
--     is tooted.
CREATE TABLE feed_items
(
    id             INTEGER PRIMARY KEY,
    account_id     INTEGER  NOT NULL,
    guid           TEXT     NOT NULL,
    link_key       TEXT     NOT NULL,
    post_guid_hash INTEGER  NOT NULL,
    post_time      DATETIME NOT NULL,
    first_seen     DATETIME NOT NULL,
    status         INTEGER  NOT NULL,
    link           TEXT     NOT NULL DEFAULT '',
    title          TEXT     NOT NULL DEFAULT '',
    description    TEXT     NOT NULL DEFAULT '',
    FOREIGN KEY (account_id) REFERENCES accounts (id)
);
CREATE INDEX idx_150 ON feed_items (account_id, guid);
CREATE INDEX idx_151 ON feed_items (account_id, link_key);
CREATE INDEX idx_152 ON feed_items (status, post_time);
CREATE INDEX idx_153 ON feed_items (account_id, post_time);

-- items_baselined: 0 until the account's feed has been read into feed_items
-- once. Every account that exists when this runs starts at 0, and its first
-- read keeps the old rules, so nothing is tooted that would not have been.
ALTER TABLE accounts ADD COLUMN items_baselined INTEGER NOT NULL DEFAULT 0;