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 CREATE TABLE serial.item (
223 id SERIAL PRIMARY KEY,
225 REFERENCES actor.usr (id)
226 DEFERRABLE INITIALLY DEFERRED,
228 REFERENCES actor.usr (id)
229 DEFERRABLE INITIALLY DEFERRED,
230 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
231 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
232 issuance INT NOT NULL
233 REFERENCES serial.issuance (id)
235 DEFERRABLE INITIALLY DEFERRED,
237 REFERENCES serial.stream (id)
239 DEFERRABLE INITIALLY DEFERRED,
240 unit INT REFERENCES serial.unit (id)
242 DEFERRABLE INITIALLY DEFERRED,
243 uri INT REFERENCES asset.uri (id)
245 DEFERRABLE INITIALLY DEFERRED,
246 date_expected TIMESTAMP WITH TIME ZONE,
247 date_received TIMESTAMP WITH TIME ZONE,
248 status TEXT CONSTRAINT valid_status CHECK
250 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
251 ) DEFAULT 'Expected',
252 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
254 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
255 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
256 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
257 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
258 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
259 CREATE INDEX serial_item_status_idx ON serial.item (status);
261 CREATE TABLE serial.item_note (
262 id SERIAL PRIMARY KEY,
264 REFERENCES serial.item (id)
266 DEFERRABLE INITIALLY DEFERRED,
268 REFERENCES actor.usr (id)
269 DEFERRABLE INITIALLY DEFERRED,
270 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
271 pub BOOL NOT NULL DEFAULT FALSE,
275 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
277 CREATE TABLE serial.basic_summary (
278 id SERIAL PRIMARY KEY,
279 distribution INT NOT NULL
280 REFERENCES serial.distribution (id)
282 DEFERRABLE INITIALLY DEFERRED,
283 generated_coverage TEXT NOT NULL,
284 textual_holdings TEXT,
285 show_generated BOOL NOT NULL DEFAULT TRUE
287 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
289 CREATE TABLE serial.supplement_summary (
290 id SERIAL PRIMARY KEY,
291 distribution INT NOT NULL
292 REFERENCES serial.distribution (id)
294 DEFERRABLE INITIALLY DEFERRED,
295 generated_coverage TEXT NOT NULL,
296 textual_holdings TEXT,
297 show_generated BOOL NOT NULL DEFAULT TRUE
299 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
301 CREATE TABLE serial.index_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_index_summary_dist_idx ON serial.index_summary (distribution);