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
43 CREATE TABLE serial.subscription_note (
44 id SERIAL PRIMARY KEY,
45 subscription INT NOT NULL
46 REFERENCES serial.subscription (id)
48 DEFERRABLE INITIALLY DEFERRED,
50 REFERENCES actor.usr (id)
51 DEFERRABLE INITIALLY DEFERRED,
52 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
53 pub BOOL NOT NULL DEFAULT FALSE,
58 CREATE TABLE serial.caption_and_pattern (
59 id SERIAL PRIMARY KEY,
60 subscription INT NOT NULL
61 REFERENCES serial.subscription (id)
63 DEFERRABLE INITIALLY DEFERRED,
65 CONSTRAINT cap_type CHECK ( type in
66 ( 'basic', 'supplement', 'index' )),
67 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
68 active BOOL NOT NULL DEFAULT FALSE,
69 pattern_code TEXT NOT NULL, -- must contain JSON
83 --at least one distribution per org_unit holding issues
84 CREATE TABLE serial.distribution (
85 id SERIAL PRIMARY KEY,
86 record_entry BIGINT REFERENCES serial.record_entry (id)
88 DEFERRABLE INITIALLY DEFERRED,
89 subscription INT NOT NULL
90 REFERENCES serial.subscription (id)
92 DEFERRABLE INITIALLY DEFERRED,
93 holding_lib INT NOT NULL
94 REFERENCES actor.org_unit (id)
95 DEFERRABLE INITIALLY DEFERRED,
97 receive_call_number BIGINT REFERENCES asset.call_number (id)
98 DEFERRABLE INITIALLY DEFERRED,
99 receive_unit_template INT REFERENCES asset.copy_template (id)
100 DEFERRABLE INITIALLY DEFERRED,
101 bind_call_number BIGINT REFERENCES asset.call_number (id)
102 DEFERRABLE INITIALLY DEFERRED,
103 bind_unit_template INT REFERENCES asset.copy_template (id)
104 DEFERRABLE INITIALLY DEFERRED,
105 unit_label_prefix TEXT,
106 unit_label_suffix TEXT
108 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
110 CREATE TABLE serial.distribution_note (
111 id SERIAL PRIMARY KEY,
112 distribution INT NOT NULL
113 REFERENCES serial.distribution (id)
115 DEFERRABLE INITIALLY DEFERRED,
117 REFERENCES actor.usr (id)
118 DEFERRABLE INITIALLY DEFERRED,
119 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
120 pub BOOL NOT NULL DEFAULT FALSE,
125 CREATE TABLE serial.stream (
126 id SERIAL PRIMARY KEY,
127 distribution INT NOT NULL
128 REFERENCES serial.distribution (id)
130 DEFERRABLE INITIALLY DEFERRED,
134 CREATE UNIQUE INDEX label_once_per_dist
135 ON serial.stream (distribution, routing_label)
136 WHERE routing_label IS NOT NULL;
138 CREATE TABLE serial.routing_list_user (
139 id SERIAL PRIMARY KEY,
141 REFERENCES serial.stream
143 DEFERRABLE INITIALLY DEFERRED,
144 pos INT NOT NULL DEFAULT 1,
145 reader INT REFERENCES actor.usr
147 DEFERRABLE INITIALLY DEFERRED,
150 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
151 CONSTRAINT reader_or_dept CHECK
153 -- Recipient is a person or a department, but not both
154 (reader IS NOT NULL AND department IS NULL) OR
155 (reader IS NULL AND department IS NOT NULL)
159 CREATE TABLE serial.issuance (
160 id SERIAL PRIMARY KEY,
162 REFERENCES actor.usr (id)
163 DEFERRABLE INITIALLY DEFERRED,
165 REFERENCES actor.usr (id)
166 DEFERRABLE INITIALLY DEFERRED,
167 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
168 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
169 subscription INT NOT NULL
170 REFERENCES serial.subscription (id)
172 DEFERRABLE INITIALLY DEFERRED,
174 date_published TIMESTAMP WITH TIME ZONE,
175 caption_and_pattern INT REFERENCES serial.caption_and_pattern (id)
176 DEFERRABLE INITIALLY DEFERRED,
178 holding_type TEXT CONSTRAINT valid_holding_type CHECK
181 OR holding_type IN ('basic','supplement','index')
183 holding_link_id INT -- probably defunct
184 -- TODO: add columns for separate enumeration/chronology values
187 CREATE TABLE serial.unit (
189 detailed_contents TEXT NOT NULL,
190 summary_contents TEXT NOT NULL
191 ) INHERITS (asset.copy);
193 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
195 -- must create this rule explicitly; it is not inherited from asset.copy
196 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;
198 CREATE TABLE serial.item (
199 id SERIAL PRIMARY KEY,
201 REFERENCES actor.usr (id)
202 DEFERRABLE INITIALLY DEFERRED,
204 REFERENCES actor.usr (id)
205 DEFERRABLE INITIALLY DEFERRED,
206 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
207 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
208 issuance INT NOT NULL
209 REFERENCES serial.issuance (id)
211 DEFERRABLE INITIALLY DEFERRED,
213 REFERENCES serial.stream (id)
215 DEFERRABLE INITIALLY DEFERRED,
216 unit INT REFERENCES serial.unit (id)
218 DEFERRABLE INITIALLY DEFERRED,
219 uri INT REFERENCES asset.uri (id)
221 DEFERRABLE INITIALLY DEFERRED,
222 date_expected TIMESTAMP WITH TIME ZONE,
223 date_received TIMESTAMP WITH TIME ZONE
224 status TEXT CONSTRAINT valid_status CHECK
226 status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
227 ) DEFAULT 'Expected',
228 shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
231 CREATE TABLE serial.item_note (
232 id SERIAL PRIMARY KEY,
234 REFERENCES serial.item (id)
236 DEFERRABLE INITIALLY DEFERRED,
238 REFERENCES actor.usr (id)
239 DEFERRABLE INITIALLY DEFERRED,
240 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
241 pub BOOL NOT NULL DEFAULT FALSE,
246 CREATE TABLE serial.basic_summary (
247 id SERIAL PRIMARY KEY,
248 distribution INT NOT NULL
249 REFERENCES serial.distribution (id)
251 DEFERRABLE INITIALLY DEFERRED,
252 generated_coverage TEXT NOT NULL,
253 textual_holdings TEXT
256 CREATE TABLE serial.supplement_summary (
257 id SERIAL PRIMARY KEY,
258 distribution INT NOT NULL
259 REFERENCES serial.distribution (id)
261 DEFERRABLE INITIALLY DEFERRED,
262 generated_coverage TEXT NOT NULL,
263 textual_holdings TEXT
266 CREATE TABLE serial.index_summary (
267 id SERIAL PRIMARY KEY,
268 distribution INT NOT NULL
269 REFERENCES serial.distribution (id)
271 DEFERRABLE INITIALLY DEFERRED,
272 generated_coverage TEXT NOT NULL,
273 textual_holdings TEXT