git.squeep.com Git - squeep-indie-auther/blob - src/db/sqlite/sql/schema/1.0.0/apply.sql

   1 BEGIN;
   2
   3         CREATE TABLE almanac (
   4                 event TEXT NOT NULL PRIMARY KEY CHECK (typeof(event) = 'text'),
   5                 epoch INTEGER NOT NULL DEFAULT 0 CHECK (typeof(epoch) = 'integer')
   6         );
   7
   8         CREATE TABLE authentication (
   9                 identifier_id INTEGER NOT NULL PRIMARY KEY,
  10                 created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created) = 'integer'),
  11                 last_authentication INTEGER NOT NULL DEFAULT 0 CHECK (typeof(last_authentication) = 'integer'),
  12                 identifier TEXT NOT NULL UNIQUE CHECK (typeof(identifier) = 'text'),
  13                 credential TEXT CHECK (typeof(credential) IN ('text', 'null'))
  14         );
  15
  16         CREATE TABLE resource (
  17                 resource_id TEXT NOT NULL PRIMARY KEY CHECK (typeof(resource_id) = 'text'),
  18                 description TEXT NOT NULL DEFAULT '' CHECK (typeof(description) = 'text'),
  19                 created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created) = 'integer'),
  20                 secret TEXT NOT NULL CHECK (typeof(secret) = 'text')
  21         );
  22
  23         CREATE TABLE profile (
  24                 profile_id INTEGER NOT NULL PRIMARY KEY,
  25                 identifier_id INTEGER NOT NULL REFERENCES authentication(identifier_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  26                 profile TEXT NOT NULL CHECK (typeof(profile) = 'text'),
  27                 UNIQUE (identifier_id, profile) ON CONFLICT IGNORE
  28         );
  29         CREATE INDEX profile_identifier_id_idx ON profile(identifier_id);
  30         CREATE INDEX profile_profile_idx ON profile(profile);
  31
  32         CREATE TABLE scope (
  33                 scope_id INTEGER NOT NULL PRIMARY KEY,
  34                 scope TEXT NOT NULL UNIQUE CHECK (typeof(scope) = 'text'),
  35                 description TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '' CHECK (typeof(description) = 'text'),
  36                 application TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '' CHECK (typeof(application) = 'text'),
  37                 is_permanent INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_permanent) = 'integer' AND is_permanent IN (0, 1)),
  38                 is_manually_added INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_manually_added) = 'integer' AND is_manually_added IN (0, 1))
  39         );
  40
  41         CREATE TABLE profile_scope (
  42                 profile_id INTEGER NOT NULL REFERENCES profile(profile_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  43                 scope_id INTEGER NOT NULL REFERENCES scope(scope_id) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  44                 PRIMARY KEY (profile_id, scope_id)
  45         );
  46         CREATE INDEX profile_scope_scope_id_idx ON profile_scope(scope_id);
  47
  48         CREATE TABLE token (
  49                 code_id TEXT NOT NULL PRIMARY KEY CHECK (typeof(code_id) = 'text'),
  50                 profile_id INTEGER NOT NULL REFERENCES profile (profile_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  51                 created INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK (typeof(created) = 'integer'),
  52                 expires INTEGER CHECK (typeof(expires) IN ('integer', 'null')),
  53                 refresh_expires INTEGER CHECK (typeof(refresh_expires) IN ('integer', 'null')),
  54                 refreshed INTEGER CHECK (typeof(refreshed) IN ('integer', 'null')),
  55                 duration INTEGER CHECK (typeof(duration) IN ('integer', 'null')),
  56                 refresh_duration INTEGER CHECK (typeof(refresh_duration) IN ('integer', 'null')),
  57                 refresh_count INTEGER NOT NULL DEFAULT 0 CHECK (typeof(refresh_count) = 'integer'),
  58                 is_revoked INTEGER NOT NULL DEFAULT 0 CHECK (typeof(is_revoked) = 'integer' AND is_revoked IN (0, 1)),
  59                 is_token INTEGER NOT NULL CHECK (typeof(is_token) = 'integer' AND is_token IN (0, 1)),
  60                 client_id TEXT NOT NULL CHECK (typeof(client_id) = 'text'),
  61                 resource TEXT CHECK (typeof(resource) IN ('integer', 'null')),
  62                 profile_data TEXT,
  63                 CONSTRAINT expires_needs_duration CHECK (expires IS NULL OR duration IS NOT NULL),
  64                 CONSTRAINT refresh_expires_needs_refresh_duration CHECK (refresh_expires IS NULL OR refresh_duration IS NOT NULL)
  65         );
  66         CREATE INDEX token_profile_id_idx ON token(profile_id);
  67         CREATE INDEX token_created_idx ON token(created);
  68         CREATE INDEX token_expires_idx ON token(expires) WHERE expires IS NOT NULL;
  69         CREATE INDEX token_refresh_expires_idx ON token(refresh_expires) WHERE refresh_expires IS NOT NULL;
  70
  71         CREATE TABLE token_scope (
  72                 code_id TEXT NOT NULL REFERENCES token(code_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  73                 scope_id INTEGER NOT NULL REFERENCES scope(scope_id) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  74                 PRIMARY KEY (code_id, scope_id)
  75         );
  76         CREATE INDEX token_scope_scope_id_idx ON token_scope(scope_id);
  77
  78         -- Update schema version
  79         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
  80
  81         -- Seed scope data
  82         INSERT INTO scope (scope, description, application, is_permanent) VALUES
  83                 ('profile', 'Access detailed profile information, including name, image, and url.', 'IndieAuth', 1),
  84                 ('email', 'Include email address with detailed profile information.', 'IndieAuth', 1),
  85                 ('create', 'Create new items.', 'MicroPub', 1),
  86                 ('draft', 'All created items are drafts.', 'MicroPub', 1),
  87                 ('update', 'Edit existing items.', 'MicroPub', 1),
  88                 ('delete', 'Remove items.', 'MicroPub', 1),
  89                 ('media', 'Allow file uploads.', 'MicroPub', 1),
  90                 ('read', 'Read access to channels.', 'MicroSub', 1),
  91                 ('follow', 'Management of following list.', 'MicroSub', 1),
  92                 ('mute', 'Management of user muting.', 'MicroSub', 1),
  93                 ('block', 'Management of user blocking.', 'MicroSub', 1),
  94                 ('channels', 'Management of channels.', 'MicroSub', 1)
  95         ;
  96
  97 COMMIT;