begriffs open source - libpw/blob - schema.sql

   1 PRAGMA foreign_keys = ON;
   2
   3 BEGIN;
   4
   5 CREATE TABLE login (
   6         id INTEGER PRIMARY KEY, -- internal use only
   7         -- case insensitive match for e.g. emails
   8         user TEXT COLLATE NOCASE UNIQUE NOT NULL,
   9         pass TEXT NOT NULL
  10                 CHECK ( -- bcrypt
  11                         -- $2<a/b/x/y>$[cost]$[22 character salt][31 character hash]
  12                         length(pass) >= 58 AND
  13                         -- corrected OpenBSD algorithm (from February 2014)
  14                         pass LIKE '$2b$%'
  15                 )
  16 ) STRICT;
  17
  18 CREATE TABLE token (
  19         login_id INTEGER NOT NULL,
  20         hash BLOB NOT NULL
  21                 CHECK(length(hash) == 32), -- sha256
  22         issued_at TEXT NOT NULL
  23                 DEFAULT (strftime('%FT%TZ', 'now'))
  24                 CHECK (strftime('%FT%TZ', issued_at) = issued_at), -- ISO-8601
  25         expires_at TEXT NOT NULL
  26                 DEFAULT (strftime('%FT%TZ', 'now', '+2 hours'))
  27                 CHECK (strftime('%FT%TZ', expires_at) = expires_at),
  28         redeemed INTEGER NOT NULL DEFAULT (FALSE)
  29                 CHECK (redeemed = FALSE OR redeemed = TRUE), -- boolean
  30         revoked INTEGER NOT NULL DEFAULT (FALSE)
  31                 CHECK (revoked = FALSE OR revoked = TRUE),
  32
  33         CHECK (CAST(strftime('%J', expires_at) AS REAL) >
  34                CAST(strftime('%J', issued_at) AS REAL)),
  35
  36         FOREIGN KEY(login_id) REFERENCES login(id)
  37                 ON DELETE CASCADE
  38                 ON UPDATE RESTRICT
  39 ) STRICT;
  40
  41 CREATE INDEX token_idx ON token (login_id, hash);
  42
  43 COMMIT;