git.squeep.com Git - websub-hub/blob - src/db/postgres/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 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
   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         CREATE OR REPLACE VIEW subscription_delivery_needed AS
  15                 SELECT s.*
  16                 FROM subscription s JOIN topic t ON s.topic_id = t.id
  17                 WHERE
  18                         s.expires > now()
  19                 AND
  20                         s.latest_content_delivered < t.content_updated
  21                 AND
  22                         s.delivery_next_attempt < now()
  23                 AND
  24                         s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
  25         ;
  26
  27         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 2);
  28 COMMIT;