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 session INT REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
46 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
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,
62 CONSTRAINT redirect_or_from_item CHECK (
63 redirect_from IS NOT NULL OR (
65 url_selector IS NOT NULL AND
67 subfield IS NOT NULL AND
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
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
89 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected