git.squeep.com Git - websub-hub/blob - src/db/postgres/sql/schema/1.0.2/revert.sql

   1 BEGIN;
   2         DROP INDEX subscription_latest_content_delivered_idx;
   3         CREATE INDEX subscription_content_delivered_idx ON subscription(content_delivered);
   4         DROP VIEW subscription_delivery_needed;
   5         ALTER TABLE subscription
   6                 DROP COLUMN latest_content_delivered
   7         ;
   8         CREATE OR REPLACE VIEW subscription_delivery_needed AS
   9                 SELECT s.*
  10                 FROM subscription s JOIN topic t ON s.topic_id = t.id
  11                 WHERE
  12                         s.expires > now()
  13                 AND
  14                         s.content_delivered < t.content_updated
  15                 AND
  16                         s.delivery_next_attempt < now()
  17                 AND
  18                         s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
  19         ;
  20         DELETE FROM _meta_schema_version WHERE major = 1 AND minor = 0 AND patch = 2;
  21 COMMIT;