1 DROP SCHEMA IF EXISTS url_verify CASCADE;
3 CREATE SCHEMA url_verify;
5 CREATE TABLE url_verify.session (
8 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
9 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
10 container INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
11 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
13 CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
16 CREATE TABLE url_verify.url_selector (
17 id SERIAL PRIMARY KEY,
19 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
20 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
23 CREATE TABLE url_verify.url (
24 id SERIAL PRIMARY KEY,
25 redirect_from INT REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
26 item INT REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
27 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
31 full_url TEXT NOT NULL,
43 CONSTRAINT redirect_or_from_item CHECK (
44 redirect_from IS NOT NULL OR (
46 url_selector IS NOT NULL AND
48 subfield IS NOT NULL AND
54 CREATE TABLE url_verify.verification_attempt (
55 id SERIAL PRIMARY KEY,
56 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
57 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
58 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
59 finish_time TIMESTAMP WITH TIME ZONE
62 CREATE TABLE url_verify.url_verification (
63 id SERIAL PRIMARY KEY,
64 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
65 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
66 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
67 res_time TIMESTAMP WITH TIME ZONE,
68 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
70 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
73 CREATE TABLE url_verify.filter_set (
74 id SERIAL PRIMARY KEY,
76 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
77 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
78 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
80 CONSTRAINT uvfs_name_once_per_lib UNIQUE (name, owning_lib)