Open-ILS/src/sql/Pg/upgrade/XXXX.schema.url_verify.sql

   1 BEGIN;
   2
   3 INSERT INTO container.biblio_record_entry_bucket_type (code, label) VALUES ('url_verify', 'URL Verification Queue');
   4
   5 DROP SCHEMA IF EXISTS url_verify CASCADE;
   6
   7 CREATE SCHEMA url_verify;
   8
   9 CREATE TABLE url_verify.session (
  10     id          SERIAL                      PRIMARY KEY,
  11     name        TEXT                        NOT NULL,
  12     owning_lib  INT                         NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  13     creator     INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  14     container   INT                         NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
  15     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  16     search      TEXT                        NOT NULL,
  17     CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
  18 );
  19
  20 CREATE TABLE url_verify.url_selector (
  21     id      SERIAL  PRIMARY KEY,
  22     xpath   TEXT    NOT NULL,
  23     session INT     NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  24     CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
  25 );
  26
  27 CREATE TABLE url_verify.url (
  28     id              SERIAL  PRIMARY KEY,
  29     redirect_from   INT     REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
  30     item            INT     REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
  31     url_selector    INT     REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
  32     session         INT     REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  33     tag             TEXT,
  34     subfield        TEXT,
  35     ord             INT,
  36     full_url        TEXT    NOT NULL,
  37     scheme          TEXT,
  38     username        TEXT,
  39     password        TEXT,
  40     host            TEXT,
  41     domain          TEXT,
  42     tld             TEXT,
  43     port            TEXT,
  44     path            TEXT,
  45     page            TEXT,
  46     query           TEXT,
  47     fragment        TEXT,
  48     CONSTRAINT redirect_or_from_item CHECK (
  49         redirect_from IS NOT NULL OR (
  50             item         IS NOT NULL AND
  51             url_selector IS NOT NULL AND
  52             tag          IS NOT NULL AND
  53             subfield     IS NOT NULL AND
  54             ord          IS NOT NULL
  55         )
  56     )
  57 );
  58
  59 CREATE TABLE url_verify.verification_attempt (
  60     id          SERIAL                      PRIMARY KEY,
  61     usr         INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  62     session     INT                         NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  63     start_time  TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  64     finish_time TIMESTAMP WITH TIME ZONE
  65 );
  66
  67 CREATE TABLE url_verify.url_verification (
  68     id          SERIAL                      PRIMARY KEY,
  69     url         INT                         NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
  70     attempt     INT                         NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
  71     req_time    TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  72     res_time    TIMESTAMP WITH TIME ZONE,
  73     res_code    INT                         CHECK (res_code BETWEEN 100 AND 999), -- we know > 599 will never be valid HTTP code, but we use 9XX for other stuff
  74     res_text    TEXT,
  75     redirect_to INT                         REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
  76 );
  77
  78 CREATE TABLE config.filter_dialog_interface (
  79     key         TEXT                        PRIMARY KEY,
  80     description TEXT
  81 );
  82
  83 CREATE TABLE config.filter_dialog_filter_set (
  84     id          SERIAL                      PRIMARY KEY,
  85     name        TEXT                        NOT NULL,
  86     owning_lib  INT                         NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  87     creator     INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  88     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  89     interface   TEXT                        NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
  90     filters     TEXT                        NOT NULL CHECK (evergreen.is_json(filters)),
  91     CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
  92 );
  93
  94 COMMIT;