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)
38 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
39 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
42 CREATE TABLE url_verify.url (
43 id SERIAL PRIMARY KEY,
44 redirect_from INT REFERENCES url_verify.url(id)
45 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
46 item INT REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
47 session INT REFERENCES url_verify.session (id)
48 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
52 ord INT, -- ordinal position of this url within the record as found by url_selector, for later update
53 full_url TEXT NOT NULL,
65 CONSTRAINT redirect_or_from_item CHECK (
66 redirect_from IS NOT NULL OR (
68 url_selector IS NOT NULL AND
70 subfield IS NOT NULL AND
76 CREATE TABLE url_verify.verification_attempt (
77 id SERIAL PRIMARY KEY,
78 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
79 session INT NOT NULL REFERENCES url_verify.session (id)
80 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
81 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
82 finish_time TIMESTAMP WITH TIME ZONE
85 CREATE TABLE url_verify.url_verification (
86 id SERIAL PRIMARY KEY,
87 url INT NOT NULL REFERENCES url_verify.url (id)
88 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
89 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
90 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
91 res_time TIMESTAMP WITH TIME ZONE,
92 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
94 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected