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 display_grouping TEXT NOT NULL DEFAULT 'chron'
109 CHECK (display_grouping IN ('enum', 'chron')),
110 receive_call_number BIGINT REFERENCES asset.call_number (id)
111 DEFERRABLE INITIALLY DEFERRED,
112 receive_unit_template INT REFERENCES asset.copy_template (id)
113 DEFERRABLE INITIALLY DEFERRED,
114 bind_call_number BIGINT REFERENCES asset.call_number (id)
115 DEFERRABLE INITIALLY DEFERRED,
116 bind_unit_template INT REFERENCES asset.copy_template (id)
117 DEFERRABLE INITIALLY DEFERRED,
118 unit_label_prefix TEXT,
119 unit_label_suffix TEXT
121 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
122 CREATE INDEX serial_distribution_sub_idx ON serial.distribution (subscription);
123 CREATE INDEX serial_distribution_holding_lib_idx ON serial.distribution (holding_lib);
125 CREATE TABLE serial.distribution_note (
126 id SERIAL PRIMARY KEY,
127 distribution INT NOT NULL
128 REFERENCES serial.distribution (id)
130 DEFERRABLE INITIALLY DEFERRED,
132 REFERENCES actor.usr (id)
133 DEFERRABLE INITIALLY DEFERRED,
134 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
135 pub BOOL NOT NULL DEFAULT FALSE,
139 CREATE INDEX serial_distribution_note_dist_idx ON serial.distribution_note (distribution);
141 CREATE TABLE serial.stream (
142 id SERIAL PRIMARY KEY,
143 distribution INT NOT NULL
144 REFERENCES serial.distribution (id)
146 DEFERRABLE INITIALLY DEFERRED,
149 CREATE INDEX serial_stream_dist_idx ON serial.stream (distribution);
151 CREATE UNIQUE INDEX label_once_per_dist
152 ON serial.stream (distribution, routing_label)
153 WHERE routing_label IS NOT NULL;
155 CREATE TABLE serial.routing_list_user (
156 id SERIAL PRIMARY KEY,
158 REFERENCES serial.stream
160 DEFERRABLE INITIALLY DEFERRED,
161 pos INT NOT NULL DEFAULT 1,
162 reader INT REFERENCES actor.usr
164 DEFERRABLE INITIALLY DEFERRED,
167 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
168 CONSTRAINT reader_or_dept CHECK
170 -- Recipient is a person or a department, but not both
171 (reader IS NOT NULL AND department IS NULL) OR
172 (reader IS NULL AND department IS NOT NULL)
175 CREATE INDEX serial_routing_list_user_stream_idx ON serial.routing_list_user (stream);
176 CREATE INDEX serial_routing_list_user_reader_idx ON serial.routing_list_user (reader);
178 CREATE TABLE serial.issuance (
179 id SERIAL PRIMARY KEY,
181 REFERENCES actor.usr (id)
182 DEFERRABLE INITIALLY DEFERRED,
184 REFERENCES actor.usr (id)
185 DEFERRABLE INITIALLY DEFERRED,
186 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
187 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
188 subscription INT NOT NULL
189 REFERENCES serial.subscription (id)
191 DEFERRABLE INITIALLY DEFERRED,
193 date_published TIMESTAMP WITH TIME ZONE,
194 caption_and_pattern INT REFERENCES serial.caption_and_pattern (id)
195 DEFERRABLE INITIALLY DEFERRED,
196 holding_code TEXT CONSTRAINT issuance_holding_code_check CHECK (
197 holding_code IS NULL OR could_be_serial_holding_code(holding_code)
199 holding_type TEXT CONSTRAINT valid_holding_type CHECK
202 OR holding_type IN ('basic','supplement','index')
204 holding_link_id INT -- probably defunct
205 -- TODO: add columns for separate enumeration/chronology values
207 ALTER TABLE serial.issuance ADD CHECK (holding_code IS NULL OR evergreen.is_json(holding_code));
208 CREATE INDEX serial_issuance_sub_idx ON serial.issuance (subscription);
209 CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance (caption_and_pattern);
210 CREATE INDEX serial_issuance_date_published_idx ON serial.issuance (date_published);
212 CREATE TABLE serial.unit (
214 detailed_contents TEXT NOT NULL,
215 summary_contents TEXT NOT NULL
216 ) INHERITS (asset.copy);
217 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
218 CREATE UNIQUE INDEX unit_barcode_key ON serial.unit (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
219 CREATE INDEX unit_cn_idx ON serial.unit (call_number);
220 CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number);
221 CREATE INDEX unit_creator_idx ON serial.unit ( creator );
222 CREATE INDEX unit_editor_idx ON serial.unit ( editor );
224 -- must create this rule explicitly; it is not inherited from asset.copy
225 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;
227 -- must create this trigger explicitly; it is not inherited from asset.copy
228 CREATE TRIGGER autogenerate_placeholder_barcode
229 BEFORE INSERT OR UPDATE ON serial.unit
230 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode()
233 -- must create this trigger explicitly; it is not inherited from asset.copy
234 CREATE TRIGGER sunit_status_changed_trig
235 BEFORE UPDATE ON serial.unit
236 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
239 CREATE TRIGGER sunit_created_trig
240 BEFORE INSERT ON serial.unit
241 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
243 CREATE TABLE serial.item (
244 id SERIAL PRIMARY KEY,
246 REFERENCES actor.usr (id)
247 DEFERRABLE INITIALLY DEFERRED,
249 REFERENCES actor.usr (id)
250 DEFERRABLE INITIALLY DEFERRED,
251 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
252 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
253 issuance INT NOT NULL
254 REFERENCES serial.issuance (id)
256 DEFERRABLE INITIALLY DEFERRED,
258 REFERENCES serial.stream (id)
260 DEFERRABLE INITIALLY DEFERRED,
261 unit INT REFERENCES serial.unit (id)
263 DEFERRABLE INITIALLY DEFERRED,
264 uri INT REFERENCES asset.uri (id)
266 DEFERRABLE INITIALLY DEFERRED,
267 date_expected TIMESTAMP WITH TIME ZONE,
268 date_received TIMESTAMP WITH TIME ZONE,
269 status TEXT CONSTRAINT valid_status CHECK
271 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
272 ) DEFAULT 'Expected',
273 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
275 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
276 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
277 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
278 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
279 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
280 CREATE INDEX serial_item_status_idx ON serial.item (status);
282 CREATE TABLE serial.item_note (
283 id SERIAL PRIMARY KEY,
285 REFERENCES serial.item (id)
287 DEFERRABLE INITIALLY DEFERRED,
289 REFERENCES actor.usr (id)
290 DEFERRABLE INITIALLY DEFERRED,
291 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
292 pub BOOL NOT NULL DEFAULT FALSE,
296 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
298 CREATE TABLE serial.basic_summary (
299 id SERIAL PRIMARY KEY,
300 distribution INT NOT NULL
301 REFERENCES serial.distribution (id)
303 DEFERRABLE INITIALLY DEFERRED,
304 generated_coverage TEXT NOT NULL,
305 textual_holdings TEXT,
306 show_generated BOOL NOT NULL DEFAULT TRUE
308 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
310 CREATE TABLE serial.supplement_summary (
311 id SERIAL PRIMARY KEY,
312 distribution INT NOT NULL
313 REFERENCES serial.distribution (id)
315 DEFERRABLE INITIALLY DEFERRED,
316 generated_coverage TEXT NOT NULL,
317 textual_holdings TEXT,
318 show_generated BOOL NOT NULL DEFAULT TRUE
320 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
322 CREATE TABLE serial.index_summary (
323 id SERIAL PRIMARY KEY,
324 distribution INT NOT NULL
325 REFERENCES serial.distribution (id)
327 DEFERRABLE INITIALLY DEFERRED,
328 generated_coverage TEXT NOT NULL,
329 textual_holdings TEXT,
330 show_generated BOOL NOT NULL DEFAULT TRUE
332 CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);
334 CREATE VIEW serial.any_summary AS
336 'basic' AS summary_type, id, distribution,
337 generated_coverage, textual_holdings, show_generated
338 FROM serial.basic_summary
341 'index' AS summary_type, id, distribution,
342 generated_coverage, textual_holdings, show_generated
343 FROM serial.index_summary
346 'supplement' AS summary_type, id, distribution,
347 generated_coverage, textual_holdings, show_generated
348 FROM serial.supplement_summary ;
351 CREATE TABLE serial.materialized_holding_code (
352 id BIGSERIAL PRIMARY KEY,
353 issuance INTEGER NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE,
358 CREATE OR REPLACE FUNCTION serial.materialize_holding_code() RETURNS TRIGGER
365 if (not defined $_TD->{new}{holding_code}) {
366 elog(WARNING, 'NULL in "holding_code" column of serial.issuance allowed for now, but may not be useful');
370 # Do nothing if holding_code has not changed...
372 if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) {
373 # ... unless the following internal flag is set.
375 my $flag_rv = spi_exec_query(q{
376 SELECT * FROM config.internal_flag
377 WHERE name = 'serial.rematerialize_on_same_holding_code' AND enabled
379 return unless $flag_rv->{processed};
383 my $holding_code = (new JSON::XS)->decode($_TD->{new}{holding_code});
385 my $field = new MARC::Field('999', @$holding_code); # tag doesnt matter
387 my $dstmt = spi_prepare(
388 'DELETE FROM serial.materialized_holding_code WHERE issuance = $1',
391 spi_exec_prepared($dstmt, $_TD->{new}{id});
393 my $istmt = spi_prepare(
395 INSERT INTO serial.materialized_holding_code (
396 issuance, subfield, value
397 ) VALUES ($1, $2, $3)
401 foreach ($field->subfields) {
412 $func$ LANGUAGE 'plperlu';
414 CREATE INDEX assist_holdings_display
415 ON serial.materialized_holding_code (issuance, subfield);
417 CREATE TRIGGER materialize_holding_code
418 AFTER INSERT OR UPDATE ON serial.issuance
419 FOR EACH ROW EXECUTE PROCEDURE serial.materialize_holding_code() ;