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,
56 alert BOOL NOT NULL DEFAULT FALSE,
60 CREATE INDEX serial_subscription_note_sub_idx ON serial.subscription_note (subscription);
62 CREATE TABLE serial.caption_and_pattern (
63 id SERIAL PRIMARY KEY,
64 subscription INT NOT NULL
65 REFERENCES serial.subscription (id)
67 DEFERRABLE INITIALLY DEFERRED,
69 CONSTRAINT cap_type CHECK ( type in
70 ( 'basic', 'supplement', 'index' )),
71 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
72 start_date TIMESTAMPTZ NOT NULL DEFAULT now(),
74 active BOOL NOT NULL DEFAULT FALSE,
75 pattern_code TEXT NOT NULL, -- must contain JSON
88 CREATE INDEX serial_caption_and_pattern_sub_idx ON serial.caption_and_pattern (subscription);
90 --at least one distribution per org_unit holding issues
91 CREATE TABLE serial.distribution (
92 id SERIAL PRIMARY KEY,
93 record_entry BIGINT REFERENCES serial.record_entry (id)
95 DEFERRABLE INITIALLY DEFERRED,
96 summary_method TEXT CONSTRAINT sdist_summary_method_check
97 CHECK (summary_method IS NULL
98 OR summary_method IN ( 'add_to_sre',
99 'merge_with_sre', 'use_sre_only',
101 subscription INT NOT NULL
102 REFERENCES serial.subscription (id)
104 DEFERRABLE INITIALLY DEFERRED,
105 holding_lib INT NOT NULL
106 REFERENCES actor.org_unit (id)
107 DEFERRABLE INITIALLY DEFERRED,
109 display_grouping TEXT NOT NULL DEFAULT 'chron'
110 CHECK (display_grouping IN ('enum', 'chron')),
111 receive_call_number BIGINT REFERENCES asset.call_number (id)
112 DEFERRABLE INITIALLY DEFERRED,
113 receive_unit_template INT REFERENCES asset.copy_template (id)
114 DEFERRABLE INITIALLY DEFERRED,
115 bind_call_number BIGINT REFERENCES asset.call_number (id)
116 DEFERRABLE INITIALLY DEFERRED,
117 bind_unit_template INT REFERENCES asset.copy_template (id)
118 DEFERRABLE INITIALLY DEFERRED,
119 unit_label_prefix TEXT,
120 unit_label_suffix TEXT
122 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
123 CREATE INDEX serial_distribution_sub_idx ON serial.distribution (subscription);
124 CREATE INDEX serial_distribution_holding_lib_idx ON serial.distribution (holding_lib);
126 CREATE TABLE serial.distribution_note (
127 id SERIAL PRIMARY KEY,
128 distribution INT NOT NULL
129 REFERENCES serial.distribution (id)
131 DEFERRABLE INITIALLY DEFERRED,
133 REFERENCES actor.usr (id)
134 DEFERRABLE INITIALLY DEFERRED,
135 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
136 pub BOOL NOT NULL DEFAULT FALSE,
137 alert BOOL NOT NULL DEFAULT FALSE,
141 CREATE INDEX serial_distribution_note_dist_idx ON serial.distribution_note (distribution);
143 CREATE TABLE serial.stream (
144 id SERIAL PRIMARY KEY,
145 distribution INT NOT NULL
146 REFERENCES serial.distribution (id)
148 DEFERRABLE INITIALLY DEFERRED,
151 CREATE INDEX serial_stream_dist_idx ON serial.stream (distribution);
153 CREATE UNIQUE INDEX label_once_per_dist
154 ON serial.stream (distribution, routing_label)
155 WHERE routing_label IS NOT NULL;
157 CREATE TABLE serial.routing_list_user (
158 id SERIAL PRIMARY KEY,
160 REFERENCES serial.stream
162 DEFERRABLE INITIALLY DEFERRED,
163 pos INT NOT NULL DEFAULT 1,
164 reader INT REFERENCES actor.usr
166 DEFERRABLE INITIALLY DEFERRED,
169 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
170 CONSTRAINT reader_or_dept CHECK
172 -- Recipient is a person or a department, but not both
173 (reader IS NOT NULL AND department IS NULL) OR
174 (reader IS NULL AND department IS NOT NULL)
177 CREATE INDEX serial_routing_list_user_stream_idx ON serial.routing_list_user (stream);
178 CREATE INDEX serial_routing_list_user_reader_idx ON serial.routing_list_user (reader);
180 CREATE TABLE serial.issuance (
181 id SERIAL PRIMARY KEY,
183 REFERENCES actor.usr (id)
184 DEFERRABLE INITIALLY DEFERRED,
186 REFERENCES actor.usr (id)
187 DEFERRABLE INITIALLY DEFERRED,
188 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
189 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
190 subscription INT NOT NULL
191 REFERENCES serial.subscription (id)
193 DEFERRABLE INITIALLY DEFERRED,
195 date_published TIMESTAMP WITH TIME ZONE,
196 caption_and_pattern INT REFERENCES serial.caption_and_pattern (id)
197 DEFERRABLE INITIALLY DEFERRED,
198 holding_code TEXT CONSTRAINT issuance_holding_code_check CHECK (
199 holding_code IS NULL OR could_be_serial_holding_code(holding_code)
201 holding_type TEXT CONSTRAINT valid_holding_type CHECK
204 OR holding_type IN ('basic','supplement','index')
206 holding_link_id INT -- probably defunct
207 -- TODO: add columns for separate enumeration/chronology values
209 ALTER TABLE serial.issuance ADD CHECK (holding_code IS NULL OR evergreen.is_json(holding_code));
210 CREATE INDEX serial_issuance_sub_idx ON serial.issuance (subscription);
211 CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance (caption_and_pattern);
212 CREATE INDEX serial_issuance_date_published_idx ON serial.issuance (date_published);
214 CREATE TABLE serial.unit (
216 detailed_contents TEXT NOT NULL,
217 summary_contents TEXT NOT NULL
218 ) INHERITS (asset.copy);
219 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
220 CREATE UNIQUE INDEX unit_barcode_key ON serial.unit (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
221 CREATE INDEX unit_cn_idx ON serial.unit (call_number);
222 CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number);
223 CREATE INDEX unit_creator_idx ON serial.unit ( creator );
224 CREATE INDEX unit_editor_idx ON serial.unit ( editor );
226 -- must create this rule explicitly; it is not inherited from asset.copy
227 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;
229 -- must create this trigger explicitly; it is not inherited from asset.copy
230 CREATE TRIGGER autogenerate_placeholder_barcode
231 BEFORE INSERT OR UPDATE ON serial.unit
232 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode()
235 -- must create this trigger explicitly; it is not inherited from asset.copy
236 CREATE TRIGGER sunit_status_changed_trig
237 BEFORE UPDATE ON serial.unit
238 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
241 CREATE TRIGGER sunit_created_trig
242 BEFORE INSERT ON serial.unit
243 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
245 CREATE TABLE serial.item (
246 id SERIAL PRIMARY KEY,
248 REFERENCES actor.usr (id)
249 DEFERRABLE INITIALLY DEFERRED,
251 REFERENCES actor.usr (id)
252 DEFERRABLE INITIALLY DEFERRED,
253 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
254 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
255 issuance INT NOT NULL
256 REFERENCES serial.issuance (id)
258 DEFERRABLE INITIALLY DEFERRED,
260 REFERENCES serial.stream (id)
262 DEFERRABLE INITIALLY DEFERRED,
263 unit INT REFERENCES serial.unit (id)
265 DEFERRABLE INITIALLY DEFERRED,
266 uri INT REFERENCES asset.uri (id)
268 DEFERRABLE INITIALLY DEFERRED,
269 date_expected TIMESTAMP WITH TIME ZONE,
270 date_received TIMESTAMP WITH TIME ZONE,
271 status TEXT CONSTRAINT valid_status CHECK
273 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
274 ) DEFAULT 'Expected',
275 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
277 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
278 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
279 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
280 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
281 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
282 CREATE INDEX serial_item_status_idx ON serial.item (status);
284 CREATE TABLE serial.item_note (
285 id SERIAL PRIMARY KEY,
287 REFERENCES serial.item (id)
289 DEFERRABLE INITIALLY DEFERRED,
291 REFERENCES actor.usr (id)
292 DEFERRABLE INITIALLY DEFERRED,
293 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
294 pub BOOL NOT NULL DEFAULT FALSE,
295 alert BOOL NOT NULL DEFAULT FALSE,
299 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
301 CREATE TABLE serial.basic_summary (
302 id SERIAL PRIMARY KEY,
303 distribution INT NOT NULL
304 REFERENCES serial.distribution (id)
306 DEFERRABLE INITIALLY DEFERRED,
307 generated_coverage TEXT NOT NULL,
308 textual_holdings TEXT,
309 show_generated BOOL NOT NULL DEFAULT TRUE
311 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
313 CREATE TABLE serial.supplement_summary (
314 id SERIAL PRIMARY KEY,
315 distribution INT NOT NULL
316 REFERENCES serial.distribution (id)
318 DEFERRABLE INITIALLY DEFERRED,
319 generated_coverage TEXT NOT NULL,
320 textual_holdings TEXT,
321 show_generated BOOL NOT NULL DEFAULT TRUE
323 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
325 CREATE TABLE serial.index_summary (
326 id SERIAL PRIMARY KEY,
327 distribution INT NOT NULL
328 REFERENCES serial.distribution (id)
330 DEFERRABLE INITIALLY DEFERRED,
331 generated_coverage TEXT NOT NULL,
332 textual_holdings TEXT,
333 show_generated BOOL NOT NULL DEFAULT TRUE
335 CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);
337 CREATE VIEW serial.any_summary AS
339 'basic' AS summary_type, id, distribution,
340 generated_coverage, textual_holdings, show_generated
341 FROM serial.basic_summary
344 'index' AS summary_type, id, distribution,
345 generated_coverage, textual_holdings, show_generated
346 FROM serial.index_summary
349 'supplement' AS summary_type, id, distribution,
350 generated_coverage, textual_holdings, show_generated
351 FROM serial.supplement_summary ;
354 CREATE TABLE serial.materialized_holding_code (
355 id BIGSERIAL PRIMARY KEY,
356 issuance INTEGER NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE,
361 CREATE OR REPLACE FUNCTION serial.materialize_holding_code() RETURNS TRIGGER
368 if (not defined $_TD->{new}{holding_code}) {
369 elog(WARNING, 'NULL in "holding_code" column of serial.issuance allowed for now, but may not be useful');
373 # Do nothing if holding_code has not changed...
375 if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) {
376 # ... unless the following internal flag is set.
378 my $flag_rv = spi_exec_query(q{
379 SELECT * FROM config.internal_flag
380 WHERE name = 'serial.rematerialize_on_same_holding_code' AND enabled
382 return unless $flag_rv->{processed};
386 my $holding_code = (new JSON::XS)->decode($_TD->{new}{holding_code});
388 my $field = new MARC::Field('999', @$holding_code); # tag doesnt matter
390 my $dstmt = spi_prepare(
391 'DELETE FROM serial.materialized_holding_code WHERE issuance = $1',
394 spi_exec_prepared($dstmt, $_TD->{new}{id});
396 my $istmt = spi_prepare(
398 INSERT INTO serial.materialized_holding_code (
399 issuance, subfield, value
400 ) VALUES ($1, $2, $3)
404 foreach ($field->subfields) {
415 $func$ LANGUAGE 'plperlu';
417 CREATE INDEX assist_holdings_display
418 ON serial.materialized_holding_code (issuance, subfield);
420 CREATE TRIGGER materialize_holding_code
421 AFTER INSERT OR UPDATE ON serial.issuance
422 FOR EACH ROW EXECUTE PROCEDURE serial.materialize_holding_code() ;