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

   1 BEGIN;
   2
   3         -- core data
   4         CREATE TABLE link (
   5                 id TEXT NOT NULL PRIMARY KEY,
   6                 url TEXT NOT NULL UNIQUE,
   7                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   8                 last_access TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
   9                 accesses INTEGER NOT NULL DEFAULT 0,
  10                 expires TIMESTAMP WITH TIME ZONE,
  11                 auth_token TEXT
  12         );
  13
  14         -- send notices to invalidate cacheable link data
  15         CREATE OR REPLACE FUNCTION cache_invalidation_link()
  16                 RETURNS TRIGGER
  17                 LANGUAGE plpgsql
  18                 AS $$
  19                 DECLARE
  20                         payload varchar;
  21                 BEGIN
  22                         IF NEW.url != OLD.url OR NEW.expires != OLD.expires
  23                         THEN
  24                                 payload = 'link|' || CAST(NEW.id AS text);
  25                                 PERFORM pg_notify('cache_invalidation', payload);
  26                         END IF;
  27                         RETURN NEW;
  28                 END;
  29                 $$
  30         ;
  31         CREATE TRIGGER cache_invalidation_link
  32                 AFTER UPDATE
  33                 ON link
  34                 FOR EACH ROW
  35                         EXECUTE PROCEDURE cache_invalidation();
  36
  37         -- better auth TBD
  38         CREATE TABLE auth (
  39                 id TEXT NOT NULL PRIMARY KEY,
  40                 secret TEXT NOT NULL,
  41                 password TEXT
  42         );
  43
  44         -- migration complete
  45         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
  46 COMMIT;