2 -- track when content was delivered as separate from latest content delivered
3 -- content_delivered continues to be the time the content was delivered, but becomes only informational
4 -- latest_content_delivered is date on topic content delivered
5 ALTER TABLE subscription
6 ADD COLUMN latest_content_delivered
INTEGER NOT NULL DEFAULT 0
8 CREATE INDEX subscription_latest_content_delivered_idx
ON subscription(latest_content_delivered
);
9 -- migrate existing values
10 UPDATE subscription
SET latest_content_delivered
= content_delivered
;
11 -- no need for this index
12 DROP INDEX subscription_content_delivered_idx
;
13 -- update the view to use latest_cotnent_delivered
14 DROP VIEW subscription_delivery_needed
;
15 CREATE VIEW subscription_delivery_needed
AS
17 FROM subscription s
JOIN topic t
ON s.topic_id
= t.
id
19 s.expires
> strftime('%s', 'now')
21 s.latest_content_delivered
< t.content_updated
23 s.delivery_next_attempt
< strftime('%s', 'now')
25 s.
id NOT IN (SELECT id FROM subscription_delivery_in_progress_active
)
28 INSERT INTO _meta_schema_version (major
, minor
, patch
) VALUES (1, 0, 2);