3 DROP SCHEMA serial CASCADE;
9 CREATE TABLE serial.record_entry (
10 id BIGSERIAL PRIMARY KEY,
11 record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
12 owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
13 creator INT NOT NULL DEFAULT 1,
14 editor INT NOT NULL DEFAULT 1,
16 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
17 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
18 active BOOL NOT NULL DEFAULT TRUE,
19 deleted BOOL NOT NULL DEFAULT FALSE,
21 last_xact_id TEXT NOT NULL
23 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
24 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
25 CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
27 CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id;
29 CREATE TABLE serial.subscription (
30 id SERIAL PRIMARY KEY,
31 callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
32 uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
33 start_date DATE NOT NULL,
34 end_date DATE -- interpret NULL as current subscription
37 CREATE TABLE serial.binding_unit (
38 id SERIAL PRIMARY KEY,
39 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
41 CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
44 CREATE TABLE serial.issuance (
45 id SERIAL PRIMARY KEY,
46 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
48 location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED,
49 binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
53 CREATE TABLE serial.bib_summary (
54 id SERIAL PRIMARY KEY,
55 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
56 generated_coverage TEXT NOT NULL,
60 CREATE TABLE serial.sup_summary (
61 id SERIAL PRIMARY KEY,
62 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
63 generated_coverage TEXT NOT NULL,
67 CREATE TABLE serial.index_summary (
68 id SERIAL PRIMARY KEY,
69 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
70 generated_coverage TEXT NOT NULL,