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,
32 session INT REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
36 full_url TEXT NOT NULL,
48 CONSTRAINT redirect_or_from_item CHECK (
49 redirect_from IS NOT NULL OR (
51 url_selector IS NOT NULL AND
53 subfield IS NOT NULL AND
59 CREATE TABLE url_verify.verification_attempt (
60 id SERIAL PRIMARY KEY,
61 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
62 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
63 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
64 finish_time TIMESTAMP WITH TIME ZONE
67 CREATE TABLE url_verify.url_verification (
68 id SERIAL PRIMARY KEY,
69 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
70 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
71 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
72 res_time TIMESTAMP WITH TIME ZONE,
73 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
75 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
78 CREATE TABLE config.filter_dialog_interface (
83 CREATE TABLE config.filter_dialog_filter_set (
84 id SERIAL PRIMARY KEY,
86 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
87 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
88 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
89 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
90 filters TEXT NOT NULL CHECK (evergreen.is_json(filters)),
91 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)