3 SELECT evergreen.upgrade_deps_block_check('0752', :eg_version);
5 INSERT INTO container.biblio_record_entry_bucket_type (code, label) VALUES ('url_verify', 'URL Verification Queue');
7 DROP SCHEMA IF EXISTS url_verify CASCADE;
9 CREATE SCHEMA url_verify;
11 CREATE TABLE url_verify.session (
12 id SERIAL PRIMARY KEY,
14 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
15 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
16 container INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
17 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
19 CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
22 CREATE TABLE url_verify.url_selector (
23 id SERIAL PRIMARY KEY,
25 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
26 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
29 CREATE TABLE url_verify.url (
30 id SERIAL PRIMARY KEY,
31 redirect_from INT REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
32 item INT REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
33 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
34 session INT REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
38 full_url TEXT NOT NULL,
50 CONSTRAINT redirect_or_from_item CHECK (
51 redirect_from IS NOT NULL OR (
53 url_selector IS NOT NULL AND
55 subfield IS NOT NULL AND
61 CREATE TABLE url_verify.verification_attempt (
62 id SERIAL PRIMARY KEY,
63 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
64 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
65 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
66 finish_time TIMESTAMP WITH TIME ZONE
69 CREATE TABLE url_verify.url_verification (
70 id SERIAL PRIMARY KEY,
71 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
72 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
73 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
74 res_time TIMESTAMP WITH TIME ZONE,
75 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
77 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
80 CREATE TABLE config.filter_dialog_interface (
85 CREATE TABLE config.filter_dialog_filter_set (
86 id SERIAL PRIMARY KEY,
88 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
89 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
90 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
91 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
92 filters TEXT NOT NULL CHECK (is_json(filters)),
93 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)