Open-ILS/src/sql/Pg/075.schema.url_verify.sql

   1 /*
   2  * Copyright (C) 2012  Equinox Software, Inc.
   3  * Mike Rylander <miker@esilibrary.com>
   4  *
   5  * This program is free software; you can redistribute it and/or
   6  * modify it under the terms of the GNU General Public License
   7  * as published by the Free Software Foundation; either version 2
   8  * of the License, or (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  */
  16
  17 BEGIN;
  18
  19 DROP SCHEMA IF EXISTS url_verify CASCADE;
  20
  21 CREATE SCHEMA url_verify;
  22
  23 CREATE TABLE url_verify.session (
  24     id          SERIAL                      PRIMARY KEY,
  25     name        TEXT                        NOT NULL,
  26     owning_lib  INT                         NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  27     creator     INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  28     container   INT                         NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
  29     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  30     search      TEXT                        NOT NULL,
  31     CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
  32 );
  33
  34 CREATE TABLE url_verify.url_selector (
  35     id      SERIAL  PRIMARY KEY,
  36     xpath   TEXT    NOT NULL,
  37     session INT     NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  38     CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
  39 );
  40
  41 CREATE TABLE url_verify.url (
  42     id              SERIAL  PRIMARY KEY,
  43     redirect_from   INT     REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
  44     item            INT     REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
  45     session         INT     REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  46     url_selector    INT     REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
  47     tag             TEXT,
  48     subfield        TEXT,
  49     ord             INT,    -- ordinal position of this url within the record as found by url_selector, for later update
  50     full_url        TEXT    NOT NULL,
  51     scheme          TEXT,
  52     username        TEXT,
  53     password        TEXT,
  54     host            TEXT,
  55     domain          TEXT,
  56     tld             TEXT,
  57     port            TEXT,
  58     path            TEXT,
  59     page            TEXT,
  60     query           TEXT,
  61     fragment        TEXT,
  62     CONSTRAINT redirect_or_from_item CHECK (
  63         redirect_from IS NOT NULL OR (
  64             item         IS NOT NULL AND
  65             url_selector IS NOT NULL AND
  66             tag          IS NOT NULL AND
  67             subfield     IS NOT NULL AND
  68             ord          IS NOT NULL
  69         )
  70     )
  71 );
  72
  73 CREATE TABLE url_verify.verification_attempt (
  74     id          SERIAL                      PRIMARY KEY,
  75     usr         INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  76     session     INT                         NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
  77     start_time  TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  78     finish_time TIMESTAMP WITH TIME ZONE
  79 );
  80
  81 CREATE TABLE url_verify.url_verification (
  82     id          SERIAL                      PRIMARY KEY,
  83     url         INT                         NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
  84     attempt     INT                         NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
  85     req_time    TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  86     res_time    TIMESTAMP WITH TIME ZONE,
  87     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
  88     res_text    TEXT,
  89     redirect_to INT                         REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
  90 );
  91
  92 COMMIT;
  93