git.squeep.com Git - websub-hub/blob - src/db/sqlite/sql/schema/1.0.2/apply.sql

   1 BEGIN;
   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
   7         ;
   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
  16                 SELECT s.*
  17                 FROM subscription s JOIN topic t ON s.topic_id = t.id
  18                 WHERE
  19                         s.expires > strftime('%s', 'now')
  20                 AND
  21                         s.latest_content_delivered < t.content_updated
  22                 AND
  23                         s.delivery_next_attempt < strftime('%s', 'now')
  24                 AND
  25                         s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
  26         ;
  27
  28         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 2);
  29 COMMIT;