git.squeep.com Git - websub-hub/blob - src/db/postgres/sql/schema/1.0.0/apply.sql

   1 BEGIN;
   2         CREATE TABLE topic (
   3                 id UUID PRIMARY KEY DEFAULT uuid_generate_v1(), -- v1 timebased is best for indexes
   4                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   5                 url TEXT NOT NULL UNIQUE,
   6                 lease_seconds_preferred INTERVAL,
   7                 lease_seconds_min INTERVAL,
   8                 lease_seconds_max INTERVAL,
   9                 publisher_validation_url TEXT,
  10                 content_hash_algorithm VARCHAR(16) NOT NULL DEFAULT 'sha512',
  11                 -- end of topic config/behavior values
  12
  13                 is_active BOOLEAN DEFAULT false, -- will be active after first successful fetch
  14                 is_deleted BOOLEAN DEFAULT false, -- topic deletion pending on 'denied' notification to active subscriptions
  15
  16                 last_publish TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of latest publish notification
  17                 content_fetch_next_attempt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of next content update attempt
  18                 content_fetch_attempts_since_success INTEGER NOT NULL DEFAULT 0,
  19
  20                 content_updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz, -- time of latest content update
  21                 content BYTEA,
  22                 content_hash TEXT,
  23                 content_type VARCHAR(255)
  24         );
  25         CREATE INDEX topic_content_updated_idx ON topic(content_updated);
  26         CREATE INDEX topic_content_fetch_next_attempt_idx ON topic(content_fetch_next_attempt); -- sort out which need updates
  27
  28         CREATE TABLE topic_fetch_in_progress (
  29                 id UUID PRIMARY KEY NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  30                 claimant UUID NOT NULL,
  31                 claimed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  32                 claim_expires TIMESTAMP WITH TIME ZONE NOT NULL
  33         );
  34         CREATE INDEX topic_fetch_in_progress_claim_expires_idx ON topic_fetch_in_progress(claim_expires);
  35
  36         CREATE VIEW topic_fetch_in_progress_active AS
  37                 SELECT *
  38                 FROM topic_fetch_in_progress
  39                 WHERE claim_expires >= now()
  40         ;
  41
  42         CREATE VIEW topic_fetch_needed AS
  43                 SELECT *
  44                 FROM topic
  45                 WHERE
  46                         is_deleted = false
  47                 AND
  48                         content_fetch_next_attempt <= now()
  49                 AND
  50                         id NOT IN (SELECT id FROM topic_fetch_in_progress_active)
  51                 ORDER BY last_publish ASC
  52         ;
  53
  54         -- send notices when topic is updated, for any nodes caching content
  55
  56         CREATE OR REPLACE FUNCTION topic_changed()
  57                 RETURNS TRIGGER
  58                 LANGUAGE plpgsql
  59         AS $$
  60                 DECLARE
  61                         payload varchar;
  62                 BEGIN
  63                         payload = CAST(NEW.id AS text);
  64                         PERFORM pg_notify('topic_changed', payload);
  65                         RETURN NEW;
  66                 END;
  67         $$
  68         ;
  69
  70         CREATE TRIGGER topic_changed
  71         AFTER UPDATE ON topic
  72         FOR EACH ROW
  73                 EXECUTE PROCEDURE topic_changed()
  74         ;
  75
  76         --
  77
  78         CREATE TABLE subscription (
  79                 id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
  80                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  81
  82                 topic_id UUID NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  83                 callback TEXT NOT NULL,
  84                 CONSTRAINT subscription_reference UNIQUE (topic_id, callback),
  85
  86                 verified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  87                 expires TIMESTAMP WITH TIME ZONE NOT NULL,
  88
  89                 secret VARCHAR(199),
  90                 signature_algorithm VARCHAR(16) DEFAULT 'sha512',
  91                 http_remote_addr TEXT,
  92                 http_from TEXT,
  93
  94                 content_delivered TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  95                 delivery_attempts_since_success INTEGER NOT NULL DEFAULT 0,
  96                 delivery_next_attempt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
  97         );
  98         CREATE INDEX subscription_content_delivered_idx ON subscription(content_delivered);
  99         CREATE INDEX subscription_expires_idx ON subscription(expires);
 100
 101         CREATE TABLE subscription_delivery_in_progress (
 102                 id UUID PRIMARY KEY NOT NULL REFERENCES subscription(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 103                 claimant UUID NOT NULL,
 104                 claimed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 105                 claim_expires TIMESTAMP WITH TIME ZONE NOT NULL
 106         );
 107         CREATE INDEX subscription_delivery_in_progress_claim_expires_idx ON subscription_delivery_in_progress(claim_expires);
 108
 109         CREATE VIEW subscription_delivery_in_progress_active AS
 110                 SELECT *
 111                 FROM subscription_delivery_in_progress
 112                 WHERE claim_expires >= now()
 113         ;
 114
 115         CREATE VIEW subscription_delivery_needed AS
 116                 SELECT s.*
 117                 FROM subscription s JOIN topic t ON s.topic_id = t.id
 118                 WHERE
 119                         s.expires > now()
 120                 AND
 121                         s.content_delivered < t.content_updated
 122                 AND
 123                         s.delivery_next_attempt < now()
 124                 AND
 125                         s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
 126         ;
 127
 128         --
 129
 130         CREATE TABLE verification (
 131                 id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
 132                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 133
 134                 topic_id UUID NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 135                 callback TEXT NOT NULL, -- hub.callback
 136                 -- no unique constraint, multiple verifications can be pending
 137
 138                 secret VARCHAR(199),
 139                 signature_algorithm VARCHAR(16) DEFAULT 'sha512',
 140                 http_remote_addr TEXT,
 141                 http_from TEXT,
 142
 143                 mode TEXT NOT NULL, -- hub.mode
 144                 reason TEXT, -- denials may have a reason
 145                 lease_seconds INTEGER NOT NULL, -- 68 years should be long enough
 146                 is_publisher_validated BOOLEAN NOT NULL DEFAULT false,
 147                 request_id TEXT, -- client request which created this verification, null if server-generated (ie topic delete)
 148
 149                 next_attempt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 150                 attempts INTEGER NOT NULL DEFAULT 0
 151         );
 152         CREATE INDEX verification_reference_idx ON verification(topic_id, callback, created);
 153
 154         CREATE TABLE verification_in_progress (
 155                 id UUID PRIMARY KEY NOT NULL REFERENCES verification(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 156
 157                 topic_id UUID NOT NULL REFERENCES topic(id),
 158                 callback TEXT NOT NULL,
 159                 CONSTRAINT verification_in_progress_reference UNIQUE (topic_id, callback),
 160
 161                 claimant UUID NOT NULL,
 162                 claimed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 163                 claim_expires TIMESTAMP WITH TIME ZONE NOT NULL
 164         );
 165         CREATE INDEX verification_in_progress_claim_expires_idx ON verification_in_progress(claim_expires);
 166
 167         CREATE VIEW verification_in_progress_active AS
 168                 SELECT *
 169                 FROM verification_in_progress
 170                 WHERE claim_expires >= now()
 171         ;
 172
 173         CREATE VIEW verification_needed AS
 174                 SELECT *
 175                 FROM verification
 176                 WHERE
 177                         (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY (topic_id, callback))
 178                 AND
 179                         (topic_id, callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active)
 180                 AND
 181                         next_attempt <= now()
 182         ;
 183
 184         --
 185
 186         CREATE TABLE authentication (
 187                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 188                 last_authentication TIMESTAMP WITH TIME ZONE,
 189                 identifier TEXT NOT NULL PRIMARY KEY,
 190                 credential TEXT
 191         );
 192
 193         -- Update schema version
 194         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
 195
 196 COMMIT;