3 DROP SCHEMA serial CASCADE;
5 CREATE TABLE asset.uri (
10 active BOOL NOT NULL DEFAULT TRUE
13 ALTER TABLE asset.call_number ADD COLUMN uri INT REFERENCES asset.uri (id) DEFERRABLE INITIALLY DEFERRED;
19 CREATE TABLE serial.subscription (
20 id SERIAL PRIMARY KEY,
21 callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
22 uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
23 start_date DATE NOT NULL,
24 end_date DATE NOT NULL
27 CREATE TABLE serial.binding_unit (
28 id SERIAL PRIMARY KEY,
29 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
31 CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
34 CREATE TABLE serial.issuance (
35 id SERIAL PRIMARY KEY,
36 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
37 target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
38 binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
42 CREATE TABLE serial.bib_summary (
43 id SERIAL PRIMARY KEY,
44 call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 generated_coverage TEXT NOT NULL,
49 CREATE TABLE serial.sup_summary (
50 id SERIAL PRIMARY KEY,
51 call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52 generated_coverage TEXT NOT NULL,
56 CREATE TABLE serial.index_summary (
57 id SERIAL PRIMARY KEY,
58 call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
59 generated_coverage TEXT NOT NULL,