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 evergreen.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
42 CREATE INDEX serial_subscription_record_idx ON serial.subscription (record_entry);
43 CREATE INDEX serial_subscription_owner_idx ON serial.subscription (owning_lib);
45 CREATE TABLE serial.subscription_note (
46 id SERIAL PRIMARY KEY,
47 subscription INT NOT NULL
48 REFERENCES serial.subscription (id)
50 DEFERRABLE INITIALLY DEFERRED,
52 REFERENCES actor.usr (id)
53 DEFERRABLE INITIALLY DEFERRED,
54 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
55 pub BOOL NOT NULL DEFAULT FALSE,
59 CREATE INDEX serial_subscription_note_sub_idx ON serial.subscription_note (subscription);
61 CREATE TABLE serial.caption_and_pattern (
62 id SERIAL PRIMARY KEY,
63 subscription INT NOT NULL
64 REFERENCES serial.subscription (id)
66 DEFERRABLE INITIALLY DEFERRED,
68 CONSTRAINT cap_type CHECK ( type in
69 ( 'basic', 'supplement', 'index' )),
70 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
71 start_date TIMESTAMPTZ NOT NULL DEFAULT now(),
73 active BOOL NOT NULL DEFAULT FALSE,
74 pattern_code TEXT NOT NULL, -- must contain JSON
87 CREATE INDEX serial_caption_and_pattern_sub_idx ON serial.caption_and_pattern (subscription);
89 --at least one distribution per org_unit holding issues
90 CREATE TABLE serial.distribution (
91 id SERIAL PRIMARY KEY,
92 record_entry BIGINT REFERENCES serial.record_entry (id)
94 DEFERRABLE INITIALLY DEFERRED,
95 summary_method TEXT CONSTRAINT sdist_summary_method_check
96 CHECK (summary_method IS NULL
97 OR summary_method IN ( 'add_to_sre',
98 'merge_with_sre', 'use_sre_only',
100 subscription INT NOT NULL
101 REFERENCES serial.subscription (id)
103 DEFERRABLE INITIALLY DEFERRED,
104 holding_lib INT NOT NULL
105 REFERENCES actor.org_unit (id)
106 DEFERRABLE INITIALLY DEFERRED,
108 receive_call_number BIGINT REFERENCES asset.call_number (id)
109 DEFERRABLE INITIALLY DEFERRED,
110 receive_unit_template INT REFERENCES asset.copy_template (id)
111 DEFERRABLE INITIALLY DEFERRED,
112 bind_call_number BIGINT REFERENCES asset.call_number (id)
113 DEFERRABLE INITIALLY DEFERRED,
114 bind_unit_template INT REFERENCES asset.copy_template (id)
115 DEFERRABLE INITIALLY DEFERRED,
116 unit_label_prefix TEXT,
117 unit_label_suffix TEXT
119 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
120 CREATE INDEX serial_distribution_sub_idx ON serial.distribution (subscription);
121 CREATE INDEX serial_distribution_holding_lib_idx ON serial.distribution (holding_lib);
123 CREATE TABLE serial.distribution_note (
124 id SERIAL PRIMARY KEY,
125 distribution INT NOT NULL
126 REFERENCES serial.distribution (id)
128 DEFERRABLE INITIALLY DEFERRED,
130 REFERENCES actor.usr (id)
131 DEFERRABLE INITIALLY DEFERRED,
132 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
133 pub BOOL NOT NULL DEFAULT FALSE,
137 CREATE INDEX serial_distribution_note_dist_idx ON serial.distribution_note (distribution);
139 CREATE TABLE serial.stream (
140 id SERIAL PRIMARY KEY,
141 distribution INT NOT NULL
142 REFERENCES serial.distribution (id)
144 DEFERRABLE INITIALLY DEFERRED,
147 CREATE INDEX serial_stream_dist_idx ON serial.stream (distribution);
149 CREATE UNIQUE INDEX label_once_per_dist
150 ON serial.stream (distribution, routing_label)
151 WHERE routing_label IS NOT NULL;
153 CREATE TABLE serial.routing_list_user (
154 id SERIAL PRIMARY KEY,
156 REFERENCES serial.stream
158 DEFERRABLE INITIALLY DEFERRED,
159 pos INT NOT NULL DEFAULT 1,
160 reader INT REFERENCES actor.usr
162 DEFERRABLE INITIALLY DEFERRED,
165 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
166 CONSTRAINT reader_or_dept CHECK
168 -- Recipient is a person or a department, but not both
169 (reader IS NOT NULL AND department IS NULL) OR
170 (reader IS NULL AND department IS NOT NULL)
173 CREATE INDEX serial_routing_list_user_stream_idx ON serial.routing_list_user (stream);
174 CREATE INDEX serial_routing_list_user_reader_idx ON serial.routing_list_user (reader);
176 CREATE TABLE serial.issuance (
177 id SERIAL PRIMARY KEY,
179 REFERENCES actor.usr (id)
180 DEFERRABLE INITIALLY DEFERRED,
182 REFERENCES actor.usr (id)
183 DEFERRABLE INITIALLY DEFERRED,
184 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
185 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
186 subscription INT NOT NULL
187 REFERENCES serial.subscription (id)
189 DEFERRABLE INITIALLY DEFERRED,
191 date_published TIMESTAMP WITH TIME ZONE,
192 caption_and_pattern INT REFERENCES serial.caption_and_pattern (id)
193 DEFERRABLE INITIALLY DEFERRED,
195 holding_type TEXT CONSTRAINT valid_holding_type CHECK
198 OR holding_type IN ('basic','supplement','index')
200 holding_link_id INT -- probably defunct
201 -- TODO: add columns for separate enumeration/chronology values
203 CREATE INDEX serial_issuance_sub_idx ON serial.issuance (subscription);
204 CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance (caption_and_pattern);
205 CREATE INDEX serial_issuance_date_published_idx ON serial.issuance (date_published);
207 CREATE TABLE serial.unit (
209 detailed_contents TEXT NOT NULL,
210 summary_contents TEXT NOT NULL
211 ) INHERITS (asset.copy);
212 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
213 CREATE UNIQUE INDEX unit_barcode_key ON serial.unit (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
214 CREATE INDEX unit_cn_idx ON serial.unit (call_number);
215 CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number);
216 CREATE INDEX unit_creator_idx ON serial.unit ( creator );
217 CREATE INDEX unit_editor_idx ON serial.unit ( editor );
219 -- must create this rule explicitly; it is not inherited from asset.copy
220 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;
222 -- must create this trigger explicitly; it is not inherited from asset.copy
223 CREATE TRIGGER autogenerate_placeholder_barcode
224 BEFORE INSERT OR UPDATE ON serial.unit
225 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode()
228 -- must create this trigger explicitly; it is not inherited from asset.copy
229 CREATE TRIGGER sunit_status_changed_trig
230 BEFORE UPDATE ON serial.unit
231 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
234 CREATE TRIGGER sunit_created_trig
235 BEFORE INSERT ON serial.unit
236 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
238 CREATE TABLE serial.item (
239 id SERIAL PRIMARY KEY,
241 REFERENCES actor.usr (id)
242 DEFERRABLE INITIALLY DEFERRED,
244 REFERENCES actor.usr (id)
245 DEFERRABLE INITIALLY DEFERRED,
246 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
247 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
248 issuance INT NOT NULL
249 REFERENCES serial.issuance (id)
251 DEFERRABLE INITIALLY DEFERRED,
253 REFERENCES serial.stream (id)
255 DEFERRABLE INITIALLY DEFERRED,
256 unit INT REFERENCES serial.unit (id)
258 DEFERRABLE INITIALLY DEFERRED,
259 uri INT REFERENCES asset.uri (id)
261 DEFERRABLE INITIALLY DEFERRED,
262 date_expected TIMESTAMP WITH TIME ZONE,
263 date_received TIMESTAMP WITH TIME ZONE,
264 status TEXT CONSTRAINT valid_status CHECK
266 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
267 ) DEFAULT 'Expected',
268 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
270 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
271 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
272 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
273 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
274 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
275 CREATE INDEX serial_item_status_idx ON serial.item (status);
277 CREATE TABLE serial.item_note (
278 id SERIAL PRIMARY KEY,
280 REFERENCES serial.item (id)
282 DEFERRABLE INITIALLY DEFERRED,
284 REFERENCES actor.usr (id)
285 DEFERRABLE INITIALLY DEFERRED,
286 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
287 pub BOOL NOT NULL DEFAULT FALSE,
291 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
293 CREATE TABLE serial.basic_summary (
294 id SERIAL PRIMARY KEY,
295 distribution INT NOT NULL
296 REFERENCES serial.distribution (id)
298 DEFERRABLE INITIALLY DEFERRED,
299 generated_coverage TEXT NOT NULL,
300 textual_holdings TEXT,
301 show_generated BOOL NOT NULL DEFAULT TRUE
303 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
305 CREATE TABLE serial.supplement_summary (
306 id SERIAL PRIMARY KEY,
307 distribution INT NOT NULL
308 REFERENCES serial.distribution (id)
310 DEFERRABLE INITIALLY DEFERRED,
311 generated_coverage TEXT NOT NULL,
312 textual_holdings TEXT,
313 show_generated BOOL NOT NULL DEFAULT TRUE
315 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
317 CREATE TABLE serial.index_summary (
318 id SERIAL PRIMARY KEY,
319 distribution INT NOT NULL
320 REFERENCES serial.distribution (id)
322 DEFERRABLE INITIALLY DEFERRED,
323 generated_coverage TEXT NOT NULL,
324 textual_holdings TEXT,
325 show_generated BOOL NOT NULL DEFAULT TRUE
327 CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);