2 * Copyright (C) 2012 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
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.
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.
19 DROP SCHEMA IF EXISTS url_verify CASCADE;
21 CREATE SCHEMA url_verify;
23 CREATE TABLE url_verify.session (
24 id SERIAL PRIMARY KEY,
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(),
31 CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
34 CREATE TABLE url_verify.url_selector (
35 id SERIAL PRIMARY KEY,
37 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
38 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
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 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
48 ord INT, -- ordinal position of this url within the record as found by url_selector, for later update
49 full_url TEXT NOT NULL,
61 CONSTRAINT redirect_or_from_item CHECK (
62 redirect_from IS NOT NULL OR (
64 url_selector IS NOT NULL AND
66 subfield IS NOT NULL AND
72 CREATE TABLE url_verify.verification_attempt (
73 id SERIAL PRIMARY KEY,
74 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
75 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
76 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
77 finish_time TIMESTAMP WITH TIME ZONE
80 CREATE TABLE url_verify.url_verification (
81 id SERIAL PRIMARY KEY,
82 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
83 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
84 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
85 res_time TIMESTAMP WITH TIME ZONE,
86 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 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
91 CREATE TABLE url_verify.filter_set (
92 id SERIAL PRIMARY KEY,
94 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
95 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
96 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
98 CONSTRAINT uvfs_name_once_per_lib UNIQUE (name, owning_lib)