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

   1 --
   2 BEGIN;
   3
   4 CREATE TABLE topic (
   5         id INTEGER PRIMARY KEY AUTOINCREMENT,
   6         created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
   7         url TEXT NOT NULL UNIQUE,
   8         lease_seconds_preferred INTEGER, -- default if client doesn't specify
   9         lease_seconds_min INTEGER, -- limit to client requested
  10         lease_seconds_max INTEGER, -- limit to client requested
  11         publisher_validation_url TEXT,
  12         content_hash_algorithm TEXT CHECK (length(content_hash_algorithm) <= 16) NOT NULL DEFAULT 'sha512',
  13
  14         is_active BOOLEAN NOT NULL DEFAULT 0 CHECK (is_active IN (0, 1)), -- will be active after first successful fetch
  15         is_deleted BOOLEAN NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)), -- topic deletion pending on 'denied' notification to active subscriptions
  16
  17         last_publish INTEGER NOT NULL DEFAULT 0,
  18         content_fetch_next_attempt INTEGER NOT NULL DEFAULT 0, -- time of next content update attempt
  19         content_fetch_attempts_since_success INTEGER NOT NULL DEFAULT 0,
  20
  21         content_updated INTEGER NOT NULL DEFAULT 0,
  22         content LONGBLOB,
  23         content_hash TEXT,
  24         content_type TEXT CHECK (length(content_type) <= 255)
  25 );
  26 CREATE INDEX topic_content_updated_idx ON topic(content_updated);
  27 CREATE INDEX topic_content_fetch_next_attempt_idx ON topic(content_fetch_next_attempt);
  28
  29 CREATE TABLE topic_fetch_in_progress (
  30         id INTEGER NOT NULL PRIMARY KEY REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  31         claimant TEXT NOT NULL,
  32         claimed INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
  33         claim_expires INTEGER NOT NULL
  34 );
  35 CREATE INDEX topic_fetch_in_progress_claim_expires_idx ON topic_fetch_in_progress(claim_expires);
  36
  37 CREATE VIEW topic_fetch_in_progress_active AS
  38         SELECT *
  39         FROM topic_fetch_in_progress
  40         WHERE claim_expires >= (strftime('%s', 'now'))
  41 ;
  42
  43 CREATE VIEW topic_fetch_needed AS
  44         SELECT *
  45         FROM topic
  46         WHERE
  47                 is_deleted = false
  48         AND
  49                 content_fetch_next_attempt <= (strftime('%s', 'now'))
  50         AND
  51                 id NOT IN (SELECT id FROM topic_fetch_in_progress_active)
  52         ORDER BY last_publish ASC
  53 ;
  54
  55 --
  56
  57 CREATE TABLE subscription (
  58         id INTEGER PRIMARY KEY AUTOINCREMENT,
  59         created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
  60
  61         topic_id INTEGER NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  62         callback TEXT NOT NULL,
  63         -- Not sure why this does not work inline here; punting to unique index.
  64         -- CONSTRAINT subscription_reference UNIQUE (topic_id, callback),
  65
  66         verified INTEGER NOT NULL DEFAULT 0,
  67         expires INTEGER NOT NULL,
  68
  69         secret TEXT CHECK (length(secret) <= 199),
  70         signature_algorithm TEXT DEFAULT 'sha512' CHECK (length(signature_algorithm) <= 16),
  71         http_remote_addr TEXT,
  72         http_from TEXT,
  73
  74         content_delivered INTEGER NOT NULL DEFAULT 0,
  75         delivery_attempts_since_success INTEGER NOT NULL DEFAULT 0,
  76         delivery_next_attempt INTEGER NOT NULL DEFAULT 0
  77 );
  78 CREATE UNIQUE INDEX subscription_unique_idx ON subscription(topic_id, callback);
  79 CREATE INDEX subscription_content_delivered_idx ON subscription(content_delivered);
  80 CREATE INDEX subscription_expires_idx ON subscription(expires);
  81
  82 CREATE TABLE subscription_delivery_in_progress (
  83         id INTEGER NOT NULL PRIMARY KEY REFERENCES subscription(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  84         claimant TEXT NOT NULL,
  85         claimed INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
  86         claim_expires INTEGER NOT NULL
  87 );
  88 CREATE INDEX subscription_delivery_in_progress_claim_expires_idx ON subscription_delivery_in_progress(claim_expires);
  89
  90 CREATE VIEW subscription_delivery_in_progress_active AS
  91         SELECT *
  92         FROM subscription_delivery_in_progress
  93         WHERE claim_expires >= (strftime('%s', 'now'))
  94 ;
  95
  96 CREATE VIEW subscription_delivery_needed AS
  97         SELECT s.*
  98         FROM subscription s JOIN topic t ON s.topic_id = t.id
  99         WHERE
 100                 s.expires > (strftime('%s', 'now'))
 101         AND
 102                 s.content_delivered < t.content_updated
 103         AND
 104                 s.delivery_next_attempt < (strftime('%s', 'now'))
 105         AND
 106                 s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
 107 ;
 108
 109 --
 110
 111 CREATE TABLE verification (
 112         id INTEGER PRIMARY KEY AUTOINCREMENT,
 113         created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
 114
 115         topic_id INTEGER NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 116         callback TEXT NOT NULL, -- hub.callback
 117         -- no unique constraint, multiple verifications can be pending
 118
 119         secret TEXT CHECK(length(secret) <= 199),
 120         signature_algorithm TEXT DEFAULT 'sha512' CHECK (length(signature_algorithm) <= 16),
 121         http_remote_addr TEXT,
 122         http_from TEXT,
 123
 124         mode TEXT NOT NULL, -- hub.mode
 125         reason TEXT, -- denials may have a reason
 126         lease_seconds INTEGER NOT NULL, -- 68 years shuold be long enough
 127         is_publisher_validated BOOLEAN NOT NULL DEFAULT 0 CHECK(is_publisher_validated IN (0, 1)),
 128         request_id TEXT, -- client request which created this verification, null if server-generated (ie topic delete)
 129
 130         next_attempt INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
 131         attempts INTEGER NOT NULL DEFAULT 0
 132 );
 133 CREATE INDEX verification_reference_idx ON verification(topic_id, callback, created);
 134
 135 CREATE TABLE verification_in_progress (
 136         id INTEGER NOT NULL PRIMARY KEY REFERENCES verification(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 137
 138         topic_id INTEGER NOT NULL REFERENCES topic(id),
 139         callback TEXT NOT NULL,
 140         -- CONSTRAINT verification_in_progress_reference UNIQUE (topic_id, callback),
 141
 142         claimant TEXT NOT NULL,
 143         claimed INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
 144         claim_expires INTEGER NOT NULL
 145 );
 146 CREATE UNIQUE INDEX verification_in_progress_unique_idx ON verification_in_progress(topic_id, callback);
 147 CREATE INDEX verification_in_progress_claim_expires_idx ON verification_in_progress(claim_expires);
 148
 149 CREATE VIEW verification_in_progress_active AS
 150         SELECT *
 151         FROM verification_in_progress
 152         WHERE claim_expires >= (strftime('%s', 'now'))
 153 ;
 154
 155 CREATE VIEW verification_needed AS
 156         SELECT *
 157         FROM verification
 158         WHERE
 159                 (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback)
 160         AND
 161                 (topic_id, callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active)
 162         AND
 163                 next_attempt <= (strftime('%s', 'now'))
 164 ;
 165
 166 --
 167
 168 CREATE TABLE authentication (
 169         created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
 170         last_authentication INTEGER,
 171         identifier TEXT NOT NULL PRIMARY KEY,
 172         credential TEXT
 173 );
 174
 175 --
 176
 177 INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
 178
 179 COMMIT;