git.squeep.com Git - webmention-receiver/blob - src/db/postgres/sql/schema/1.0.0/apply.sql

   1 BEGIN;
   2         CREATE TABLE account (
   3                 account_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
   4                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   5                 profile_url TEXT NOT NULL UNIQUE
   6         );
   7         COMMENT ON TABLE account IS $docstring$
   8 Persistant mapping of IndieAuth profile to local account identifier.
   9 $docstring$;
  10
  11         CREATE TABLE channel (
  12                 channel_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
  13                 account_id UUID NOT NULL REFERENCES account(account_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  14                 uid TEXT NOT NULL CHECK (uid != 'global'),
  15                 name TEXT NOT NULL,
  16                 CONSTRAINT account_uid_unique UNIQUE (account_id, uid)
  17         );
  18         CREATE INDEX channel_account_id_idx ON channel(account_id);
  19         CREATE INDEX channel_uid_idx ON channel(uid);
  20         COMMENT ON TABLE channel IS $docstring$
  21 N.B. uid will be a duplicate of channel_id except for system channels (e.g. 'notifications').
  22 $docstring$;
  23
  24         CREATE TABLE channel_rank (
  25                 channel_id UUID NOT NULL REFERENCES channel(channel_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  26                 rank INTEGER NOT NULL,
  27                 PRIMARY KEY (channel_id, rank)
  28         );
  29         COMMENT ON TABLE channel_rank IS $docstring$
  30 $docstring$;
  31
  32         CREATE TABLE channel_item_unread (
  33                 channel_id UUID NOT NULL REFERENCES channel(channel_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  34                 item_id UUID NOT NULL, -- REFERENCES ...
  35                 PRIMARY KEY (channel_id, item_id)
  36         );
  37         COMMENT ON TABLE channel_item_unread IS $docstring$
  38 $docstring$;
  39
  40         CREATE VIEW channel_item_unread_count AS
  41                 SELECT channel_id, COUNT(*) AS unread
  42                 FROM channel_item_unread
  43                 GROUP BY channel_id;
  44
  45         -- CREATE TABLE channel_source ...
  46
  47 --
  48
  49 --      CREATE TABLE feed (
  50 --              feed_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
  51 --              created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  52 --              is_active BOOLEAN NOT NULL DEFAULT true,
  53
  54 --              url TEXT NOT NULL UNIQUE,
  55 --              site_url TEXT,
  56 --              hub_url TEXT,
  57
  58 --              title TEXT,
  59 --              description TEXT,
  60 --              image_url TEXT,
  61
  62 --              last_fetch_success TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  63 --              last_fetch_attempt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  64 --              next_fetch_attempt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  65 --              fetch_attempts_since_success INTEGER NOT NULL DEFAULT 0,
  66 --              last_fetch_failure_reason TEXT,
  67 --              content_last_modified TEXT,
  68 --              content_etag TEXT,
  69
  70 --              websub_enabled BOOLEAN NOT NULL DEFAULT true,
  71 --              websub_lease_expires TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  72 --              websub_last_delivery TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  73 --              websub_secret TEXT
  74 --      );
  75 --      CREATE INDEX feed_next_fetch_attempt_idx ON feed(next_fetch_attempt);
  76
  77 --      CREATE TABLE feed_fetch_in_progress (
  78 --              feed_id UUID PRIMARY KEY NOT NULL REFERENCES feed(feed_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  79 --              claimant UUID NOT NULL,
  80 --              claimed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  81 --              claim_expires TIMESTAMP WITH TIME ZONE NOT NULL
  82 --      );
  83 --      CREATE INDEX feed_fetch_in_progress_claim_expires_idx ON feed_fetch_in_progress(claim_expires);
  84
  85 --      CREATE VIEW feed_fetch_in_progress_active AS
  86 --              SELECT *
  87 --              FROM feed_fetch_in_progress
  88 --              WHERE claim_expires >= now()
  89 --      ;
  90
  91 --      CREATE VIEW feed_fetch_needed AS
  92 --              SELECT *
  93 --              FROM feed
  94 --              WHERE
  95 --                      is_active = true
  96 --              AND
  97 --                      next_fetch_attempt <= now()
  98 --              AND
  99 --                      feed_id NOT IN (SELECT feed_id FROM feed_fetch_in_progress_active)
 100 --      ;
 101
 102 -- --
 103
 104 --      CREATE TABLE account_feed_override (
 105 --              account_id UUID PRIMARY KEY NOT NULL REFERENCES account(account_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 106 --              feed_id UUID NOT NULL REFERENCES feed(feed_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 107
 108 --              title TEXT,
 109 --              image_url TEXT
 110 --      );
 111
 112
 113 -- --
 114
 115 --      CREATE TABLE category (
 116 --              category_id UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v1(),
 117 --              name TEXT NOT NULL
 118 --      );
 119 --      CREATE INDEX category_name_idx ON category(name);
 120
 121 --      CREATE TABLE account_feed_category (
 122 --              account_id UUID PRIMARY KEY NOT NULL REFERENCES account(account_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 123 --              feed_id UUID NOT NULL REFERENCES feed(feed_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 124 --              category_id UUID NOT NULL REFERENCES category(category_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
 125 --      );
 126 --      CREATE INDEX account_feed_category_feed_id_idx ON account_feed_category(feed_id);
 127 --      CREATE INDEX account_feed_category_category_id_idx ON account_feed_category(category_id);
 128 --      CREATE UNIQUE INDEX account_feed_category_unique_idx ON account_feed_category(account_id, feed_id, category_id);
 129
 130 -- --
 131
 132 --      CREATE TABLE entry (
 133 --              entry_id UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v1(),
 134 --              feed_id UUID NOT NULL REFERENCES feed(feed_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 135 --              ingested TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
 136 --              published TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
 137 --              updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
 138
 139 --              guid TEXT NOT NULL,
 140 --              link TEXT,
 141 --              title TEXT,
 142 --              author TEXT,
 143 --              content TEXT
 144 --      );
 145 --      CREATE INDEX entry_feed_id_idx ON entry(feed_id);
 146 --      CREATE INDEX entry_published_idx ON entry(published);
 147 --      CREATE INDEX entry_updated_idx ON entry(updated) WHERE updated != '-infinity'::timestamptz;
 148 --      CREATE INDEX entry_feed_id_guid_idx ON entry(feed_id, guid);
 149
 150 -- --
 151
 152 --      CREATE TABLE account_entry_unread (
 153 --              account_id UUID PRIMARY KEY NOT NULL REFERENCES account(account_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 154 --              entry_id UUID NOT NULL REFERENCES entry(entry_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
 155 --      );
 156
 157 -- --
 158
 159 --      CREATE TABLE tag (
 160 --              tag_id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
 161 --              name TEXT NOT NULL
 162 --      );
 163 --      CREATE INDEX tag_name ON tag(name);
 164
 165 --      CREATE TABLE account_entry_tag (
 166 --              account_id UUID PRIMARY KEY NOT NULL REFERENCES account(account_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 167 --              entry_id UUID NOT NULL REFERENCES entry(entry_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 168 --              tag_id UUID NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
 169 --      );
 170 --      CREATE INDEX account_entry_tag_entry_id_idx ON account_entry_tag(entry_id);
 171 --      CREATE INDEX account_entry_tag_tag_id_idx ON account_entry_tag(tag_id);
 172
 173 --
 174         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
 175 COMMIT;