3 DROP SCHEMA IF EXISTS 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 );
26 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
27 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
29 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;
31 CREATE TABLE serial.subscription (
32 id SERIAL PRIMARY KEY,
33 owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
34 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
35 end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription
36 record_entry BIGINT REFERENCES biblio.record_entry (id)
38 DEFERRABLE INITIALLY DEFERRED,
39 expected_date_offset INTERVAL
40 -- acquisitions/business-side tables link to here
44 CREATE TABLE serial.caption_and_pattern (
45 id SERIAL PRIMARY KEY,
46 subscription INT NOT NULL
47 REFERENCES serial.subscription (id)
49 DEFERRABLE INITIALLY DEFERRED,
51 CONSTRAINT cap_type CHECK ( type in
52 ( 'basic', 'supplement', 'index' )),
53 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
54 active BOOL NOT NULL DEFAULT FALSE,
55 pattern_code TEXT NOT NULL, -- must contain JSON
69 --at least one distribution per org_unit holding issues
70 CREATE TABLE serial.distribution (
71 id SERIAL PRIMARY KEY,
72 record_entry BIGINT REFERENCES serial.record_entry (id)
74 DEFERRABLE INITIALLY DEFERRED,
75 subscription INT NOT NULL
76 REFERENCES serial.subscription (id)
78 DEFERRABLE INITIALLY DEFERRED,
79 holding_lib INT NOT NULL
80 REFERENCES actor.org_unit (id)
81 DEFERRABLE INITIALLY DEFERRED,
83 receive_call_number BIGINT REFERENCES asset.call_number (id)
84 DEFERRABLE INITIALLY DEFERRED,
85 receive_unit_template INT REFERENCES asset.copy_template (id)
86 DEFERRABLE INITIALLY DEFERRED,
87 bind_call_number BIGINT REFERENCES asset.call_number (id)
88 DEFERRABLE INITIALLY DEFERRED,
89 bind_unit_template INT REFERENCES asset.copy_template (id)
90 DEFERRABLE INITIALLY DEFERRED,
92 unit_label_suffix TEXT
94 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
96 CREATE TABLE serial.stream (
97 id SERIAL PRIMARY KEY,
98 distribution INT NOT NULL
99 REFERENCES serial.distribution (id)
101 DEFERRABLE INITIALLY DEFERRED,
105 CREATE UNIQUE INDEX label_once_per_dist
106 ON serial.stream (distribution, routing_label)
107 WHERE routing_label IS NOT NULL;
109 CREATE TABLE serial.routing_list_user (
110 id SERIAL PRIMARY KEY,
112 REFERENCES serial.stream
114 DEFERRABLE INITIALLY DEFERRED,
115 pos INT NOT NULL DEFAULT 1,
116 reader INT REFERENCES actor.usr
118 DEFERRABLE INITIALLY DEFERRED,
121 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
122 CONSTRAINT reader_or_dept CHECK
124 -- Recipient is a person or a department, but not both
125 (reader IS NOT NULL AND department IS NULL) OR
126 (reader IS NULL AND department IS NOT NULL)
130 CREATE TABLE serial.issuance (
131 id SERIAL PRIMARY KEY,
133 REFERENCES actor.usr (id)
134 DEFERRABLE INITIALLY DEFERRED,
136 REFERENCES actor.usr (id)
137 DEFERRABLE INITIALLY DEFERRED,
138 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
139 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
140 subscription INT NOT NULL
141 REFERENCES serial.subscription (id)
143 DEFERRABLE INITIALLY DEFERRED,
145 date_published TIMESTAMP WITH TIME ZONE,
147 holding_type TEXT CONSTRAINT valid_holding_type CHECK
150 OR holding_type IN ('basic','supplement','index')
153 -- TODO: add columns for separate enumeration/chronology values
156 CREATE TABLE serial.unit (
159 contents TEXT NOT NULL
160 ) INHERITS (asset.copy);
162 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
164 -- must create this rule explicitly; it is not inherited from asset.copy
165 CREATE RULE protect_serial_unit_delete AS ON DELETE TO serial.unit DO INSTEAD UPDATE serial.unit SET deleted = TRUE WHERE OLD.id = serial.unit.id;
167 CREATE TABLE serial.item (
168 id SERIAL PRIMARY KEY,
170 REFERENCES actor.usr (id)
171 DEFERRABLE INITIALLY DEFERRED,
173 REFERENCES actor.usr (id)
174 DEFERRABLE INITIALLY DEFERRED,
175 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
176 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
177 issuance INT NOT NULL
178 REFERENCES serial.issuance (id)
180 DEFERRABLE INITIALLY DEFERRED,
182 REFERENCES serial.stream (id)
184 DEFERRABLE INITIALLY DEFERRED,
185 unit INT REFERENCES serial.unit (id)
187 DEFERRABLE INITIALLY DEFERRED,
188 uri INT REFERENCES asset.uri (id)
190 DEFERRABLE INITIALLY DEFERRED,
191 date_expected TIMESTAMP WITH TIME ZONE,
192 date_received TIMESTAMP WITH TIME ZONE
195 CREATE TABLE serial.item_note (
196 id SERIAL PRIMARY KEY,
198 REFERENCES serial.item (id)
200 DEFERRABLE INITIALLY DEFERRED,
202 REFERENCES actor.usr (id)
203 DEFERRABLE INITIALLY DEFERRED,
204 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
205 pub BOOL NOT NULL DEFAULT FALSE,
210 CREATE TABLE serial.bib_summary (
211 id SERIAL PRIMARY KEY,
212 distribution INT NOT NULL
213 REFERENCES serial.distribution (id)
215 DEFERRABLE INITIALLY DEFERRED,
216 generated_coverage TEXT NOT NULL,
217 textual_holdings TEXT
220 CREATE TABLE serial.sup_summary (
221 id SERIAL PRIMARY KEY,
222 distribution INT NOT NULL
223 REFERENCES serial.distribution (id)
225 DEFERRABLE INITIALLY DEFERRED,
226 generated_coverage TEXT NOT NULL,
227 textual_holdings TEXT
230 CREATE TABLE serial.index_summary (
231 id SERIAL PRIMARY KEY,
232 distribution INT NOT NULL
233 REFERENCES serial.distribution (id)
235 DEFERRABLE INITIALLY DEFERRED,
236 generated_coverage TEXT NOT NULL,
237 textual_holdings TEXT