3 INSERT INTO container.biblio_record_entry_bucket_type (code, label) VALUES ('url_verify', 'URL Verification Queue');
5 DROP SCHEMA IF EXISTS url_verify CASCADE;
7 CREATE SCHEMA url_verify;
9 CREATE TABLE url_verify.session (
10 id SERIAL PRIMARY KEY,
12 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
13 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
14 container INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
15 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
17 CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
20 CREATE TABLE url_verify.url_selector (
21 id SERIAL PRIMARY KEY,
23 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
24 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
27 CREATE TABLE url_verify.url (
28 id SERIAL PRIMARY KEY,
29 redirect_from INT REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
30 item INT REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
31 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
35 full_url TEXT NOT NULL,
47 CONSTRAINT redirect_or_from_item CHECK (
48 redirect_from IS NOT NULL OR (
50 url_selector IS NOT NULL AND
52 subfield IS NOT NULL AND
58 CREATE TABLE url_verify.verification_attempt (
59 id SERIAL PRIMARY KEY,
60 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
61 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
62 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
63 finish_time TIMESTAMP WITH TIME ZONE
66 CREATE TABLE url_verify.url_verification (
67 id SERIAL PRIMARY KEY,
68 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
69 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
70 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
71 res_time TIMESTAMP WITH TIME ZONE,
72 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
74 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
77 CREATE TABLE url_verify.filter_set (
78 id SERIAL PRIMARY KEY,
80 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
81 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
82 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
84 CONSTRAINT uvfs_name_once_per_lib UNIQUE (name, owning_lib)