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
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 CREATE TABLE serial.item (
229 id SERIAL PRIMARY KEY,
231 REFERENCES actor.usr (id)
232 DEFERRABLE INITIALLY DEFERRED,
234 REFERENCES actor.usr (id)
235 DEFERRABLE INITIALLY DEFERRED,
236 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
237 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
238 issuance INT NOT NULL
239 REFERENCES serial.issuance (id)
241 DEFERRABLE INITIALLY DEFERRED,
243 REFERENCES serial.stream (id)
245 DEFERRABLE INITIALLY DEFERRED,
246 unit INT REFERENCES serial.unit (id)
248 DEFERRABLE INITIALLY DEFERRED,
249 uri INT REFERENCES asset.uri (id)
251 DEFERRABLE INITIALLY DEFERRED,
252 date_expected TIMESTAMP WITH TIME ZONE,
253 date_received TIMESTAMP WITH TIME ZONE,
254 status TEXT CONSTRAINT valid_status CHECK
256 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
257 ) DEFAULT 'Expected',
258 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
260 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
261 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
262 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
263 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
264 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
265 CREATE INDEX serial_item_status_idx ON serial.item (status);
267 CREATE TABLE serial.item_note (
268 id SERIAL PRIMARY KEY,
270 REFERENCES serial.item (id)
272 DEFERRABLE INITIALLY DEFERRED,
274 REFERENCES actor.usr (id)
275 DEFERRABLE INITIALLY DEFERRED,
276 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
277 pub BOOL NOT NULL DEFAULT FALSE,
281 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
283 CREATE TABLE serial.basic_summary (
284 id SERIAL PRIMARY KEY,
285 distribution INT NOT NULL
286 REFERENCES serial.distribution (id)
288 DEFERRABLE INITIALLY DEFERRED,
289 generated_coverage TEXT NOT NULL,
290 textual_holdings TEXT,
291 show_generated BOOL NOT NULL DEFAULT TRUE
293 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
295 CREATE TABLE serial.supplement_summary (
296 id SERIAL PRIMARY KEY,
297 distribution INT NOT NULL
298 REFERENCES serial.distribution (id)
300 DEFERRABLE INITIALLY DEFERRED,
301 generated_coverage TEXT NOT NULL,
302 textual_holdings TEXT,
303 show_generated BOOL NOT NULL DEFAULT TRUE
305 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
307 CREATE TABLE serial.index_summary (
308 id SERIAL PRIMARY KEY,
309 distribution INT NOT NULL
310 REFERENCES serial.distribution (id)
312 DEFERRABLE INITIALLY DEFERRED,
313 generated_coverage TEXT NOT NULL,
314 textual_holdings TEXT,
315 show_generated BOOL NOT NULL DEFAULT TRUE
317 CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);