5 id INTEGER PRIMARY KEY AUTOINCREMENT
,
6 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
7 url
TEXT NOT NULL UNIQUE,
8 lease_seconds_preferred
INTEGER, -- default if client doesn't specify
9 lease_seconds_min
INTEGER, -- limit to client requested
10 lease_seconds_max
INTEGER, -- limit to client requested
11 publisher_validation_url
TEXT,
12 content_hash_algorithm
TEXT CHECK (length(content_hash_algorithm
) <= 16) NOT NULL DEFAULT 'sha512',
14 is_active
BOOLEAN NOT NULL DEFAULT 0 CHECK (is_active
IN (0, 1)), -- will be active after first successful fetch
15 is_deleted
BOOLEAN NOT NULL DEFAULT 0 CHECK (is_deleted
IN (0, 1)), -- topic deletion pending on 'denied' notification to active subscriptions
17 last_publish
INTEGER NOT NULL DEFAULT 0,
18 content_fetch_next_attempt
INTEGER NOT NULL DEFAULT 0, -- time of next content update attempt
19 content_fetch_attempts_since_success
INTEGER NOT NULL DEFAULT 0,
21 content_updated
INTEGER NOT NULL DEFAULT 0,
24 content_type
TEXT CHECK (length(content_type
) <= 255)
26 CREATE INDEX topic_content_updated_idx
ON topic(content_updated
);
27 CREATE INDEX topic_content_fetch_next_attempt_idx
ON topic(content_fetch_next_attempt
);
29 CREATE TABLE topic_fetch_in_progress (
30 id INTEGER NOT NULL PRIMARY KEY REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
31 claimant
TEXT NOT NULL,
32 claimed
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
33 claim_expires
INTEGER NOT NULL
35 CREATE INDEX topic_fetch_in_progress_claim_expires_idx
ON topic_fetch_in_progress(claim_expires
);
37 CREATE VIEW topic_fetch_in_progress_active
AS
39 FROM topic_fetch_in_progress
40 WHERE claim_expires
>= (strftime('%s', 'now'))
43 CREATE VIEW topic_fetch_needed
AS
49 content_fetch_next_attempt
<= (strftime('%s', 'now'))
51 id NOT IN (SELECT id FROM topic_fetch_in_progress_active
)
52 ORDER BY last_publish
ASC
57 CREATE TABLE subscription (
58 id INTEGER PRIMARY KEY AUTOINCREMENT
,
59 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
61 topic_id
INTEGER NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
62 callback
TEXT NOT NULL,
63 -- Not sure why this does not work inline here; punting to unique index.
64 -- CONSTRAINT subscription_reference UNIQUE (topic_id, callback),
66 verified
INTEGER NOT NULL DEFAULT 0,
67 expires
INTEGER NOT NULL,
69 secret
TEXT CHECK (length(secret
) <= 199),
70 signature_algorithm
TEXT DEFAULT 'sha512' CHECK (length(signature_algorithm
) <= 16),
71 http_remote_addr
TEXT,
74 content_delivered
INTEGER NOT NULL DEFAULT 0,
75 delivery_attempts_since_success
INTEGER NOT NULL DEFAULT 0,
76 delivery_next_attempt
INTEGER NOT NULL DEFAULT 0
78 CREATE UNIQUE INDEX subscription_unique_idx
ON subscription(topic_id
, callback
);
79 CREATE INDEX subscription_content_delivered_idx
ON subscription(content_delivered
);
80 CREATE INDEX subscription_expires_idx
ON subscription(expires
);
82 CREATE TABLE subscription_delivery_in_progress (
83 id INTEGER NOT NULL PRIMARY KEY REFERENCES subscription(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
84 claimant
TEXT NOT NULL,
85 claimed
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
86 claim_expires
INTEGER NOT NULL
88 CREATE INDEX subscription_delivery_in_progress_claim_expires_idx
ON subscription_delivery_in_progress(claim_expires
);
90 CREATE VIEW subscription_delivery_in_progress_active
AS
92 FROM subscription_delivery_in_progress
93 WHERE claim_expires
>= (strftime('%s', 'now'))
96 CREATE VIEW subscription_delivery_needed
AS
98 FROM subscription s
JOIN topic t
ON s.topic_id
= t.
id
100 s.expires
> (strftime('%s', 'now'))
102 s.content_delivered
< t.content_updated
104 s.delivery_next_attempt
< (strftime('%s', 'now'))
106 s.
id NOT IN (SELECT id FROM subscription_delivery_in_progress_active
)
111 CREATE TABLE verification (
112 id INTEGER PRIMARY KEY AUTOINCREMENT
,
113 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
115 topic_id
INTEGER NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
116 callback
TEXT NOT NULL, -- hub.callback
117 -- no unique constraint, multiple verifications can be pending
119 secret
TEXT CHECK(length(secret
) <= 199),
120 signature_algorithm
TEXT DEFAULT 'sha512' CHECK (length(signature_algorithm
) <= 16),
121 http_remote_addr
TEXT,
124 mode TEXT NOT NULL, -- hub.mode
125 reason
TEXT, -- denials may have a reason
126 lease_seconds
INTEGER NOT NULL, -- 68 years shuold be long enough
127 is_publisher_validated
BOOLEAN NOT NULL DEFAULT 0 CHECK(is_publisher_validated
IN (0, 1)),
128 request_id
TEXT, -- client request which created this verification, null if server-generated (ie topic delete)
130 next_attempt
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
131 attempts
INTEGER NOT NULL DEFAULT 0
133 CREATE INDEX verification_reference_idx
ON verification(topic_id
, callback
, created
);
135 CREATE TABLE verification_in_progress (
136 id INTEGER NOT NULL PRIMARY KEY REFERENCES verification(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
138 topic_id
INTEGER NOT NULL REFERENCES topic(id),
139 callback
TEXT NOT NULL,
140 -- CONSTRAINT verification_in_progress_reference UNIQUE (topic_id, callback),
142 claimant
TEXT NOT NULL,
143 claimed
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
144 claim_expires
INTEGER NOT NULL
146 CREATE UNIQUE INDEX verification_in_progress_unique_idx
ON verification_in_progress(topic_id
, callback
);
147 CREATE INDEX verification_in_progress_claim_expires_idx
ON verification_in_progress(claim_expires
);
149 CREATE VIEW verification_in_progress_active
AS
151 FROM verification_in_progress
152 WHERE claim_expires
>= (strftime('%s', 'now'))
155 CREATE VIEW verification_needed
AS
159 (topic_id
, callback
, created
) IN (SELECT topic_id
, callback
, max(created
) AS created
FROM verification
GROUP BY topic_id
, callback
)
161 (topic_id
, callback
) NOT IN (SELECT topic_id
, callback
FROM verification_in_progress_active
)
163 next_attempt
<= (strftime('%s', 'now'))
168 CREATE TABLE authentication (
169 created
INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
170 last_authentication
INTEGER,
171 identifier
TEXT NOT NULL PRIMARY KEY,
177 INSERT INTO _meta_schema_version (major
, minor
, patch
) VALUES (1, 0, 0);