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,
24 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
25 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
26 CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
27 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
28 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
30 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;
32 CREATE TABLE serial.subscription (
33 id SERIAL PRIMARY KEY,
34 owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
35 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
36 end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription
37 record_entry BIGINT REFERENCES biblio.record_entry (id)
39 DEFERRABLE INITIALLY DEFERRED,
40 expected_date_offset INTERVAL
41 -- acquisitions/business-side tables link to here
45 CREATE TABLE serial.caption_and_pattern (
46 id SERIAL PRIMARY KEY,
47 subscription INT NOT NULL
48 REFERENCES serial.subscription (id)
50 DEFERRABLE INITIALLY DEFERRED,
52 CONSTRAINT cap_type CHECK ( type in
53 ( 'basic', 'supplement', 'index' )),
54 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
55 active BOOL NOT NULL DEFAULT FALSE,
56 pattern_code TEXT NOT NULL, -- must contain JSON
70 --at least one distribution per org_unit holding issues
71 CREATE TABLE serial.distribution (
72 id SERIAL PRIMARY KEY,
73 record_entry BIGINT REFERENCES serial.record_entry (id)
75 DEFERRABLE INITIALLY DEFERRED,
76 subscription INT NOT NULL
77 REFERENCES serial.subscription (id)
79 DEFERRABLE INITIALLY DEFERRED,
80 holding_lib INT NOT NULL
81 REFERENCES actor.org_unit (id)
82 DEFERRABLE INITIALLY DEFERRED,
84 receive_call_number BIGINT REFERENCES asset.call_number (id)
85 DEFERRABLE INITIALLY DEFERRED,
86 receive_unit_template INT REFERENCES asset.copy_template (id)
87 DEFERRABLE INITIALLY DEFERRED,
88 bind_call_number BIGINT REFERENCES asset.call_number (id)
89 DEFERRABLE INITIALLY DEFERRED,
90 bind_unit_template INT REFERENCES asset.copy_template (id)
91 DEFERRABLE INITIALLY DEFERRED,
93 unit_label_suffix TEXT
95 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
97 CREATE TABLE serial.stream (
98 id SERIAL PRIMARY KEY,
99 distribution INT NOT NULL
100 REFERENCES serial.distribution (id)
102 DEFERRABLE INITIALLY DEFERRED,
106 CREATE UNIQUE INDEX label_once_per_dist
107 ON serial.stream (distribution, routing_label)
108 WHERE routing_label IS NOT NULL;
110 CREATE TABLE serial.routing_list_user (
111 id SERIAL PRIMARY KEY,
113 REFERENCES serial.stream
115 DEFERRABLE INITIALLY DEFERRED,
116 pos INT NOT NULL DEFAULT 1,
117 reader INT REFERENCES actor.usr
119 DEFERRABLE INITIALLY DEFERRED,
122 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
123 CONSTRAINT reader_or_dept CHECK
125 -- Recipient is a person or a department, but not both
126 (reader IS NOT NULL AND department IS NULL) OR
127 (reader IS NULL AND department IS NOT NULL)
131 CREATE TABLE serial.issuance (
132 id SERIAL PRIMARY KEY,
134 REFERENCES actor.usr (id)
135 DEFERRABLE INITIALLY DEFERRED,
137 REFERENCES actor.usr (id)
138 DEFERRABLE INITIALLY DEFERRED,
139 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
140 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
141 subscription INT NOT NULL
142 REFERENCES serial.subscription (id)
144 DEFERRABLE INITIALLY DEFERRED,
146 date_published TIMESTAMP WITH TIME ZONE,
148 holding_type TEXT CONSTRAINT valid_holding_type CHECK
151 OR holding_type IN ('basic','supplement','index')
154 -- TODO: add columns for separate enumeration/chronology values
157 CREATE TABLE serial.unit (
160 contents TEXT NOT NULL
161 ) INHERITS (asset.copy);
163 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
165 CREATE TABLE serial.item (
166 id SERIAL PRIMARY KEY,
168 REFERENCES actor.usr (id)
169 DEFERRABLE INITIALLY DEFERRED,
171 REFERENCES actor.usr (id)
172 DEFERRABLE INITIALLY DEFERRED,
173 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
174 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
175 issuance INT NOT NULL
176 REFERENCES serial.issuance (id)
178 DEFERRABLE INITIALLY DEFERRED,
180 REFERENCES serial.stream (id)
182 DEFERRABLE INITIALLY DEFERRED,
183 unit INT REFERENCES serial.unit (id)
185 DEFERRABLE INITIALLY DEFERRED,
186 uri INT REFERENCES asset.uri (id)
188 DEFERRABLE INITIALLY DEFERRED,
189 date_expected TIMESTAMP WITH TIME ZONE,
190 date_received TIMESTAMP WITH TIME ZONE
193 CREATE TABLE serial.item_note (
194 id SERIAL PRIMARY KEY,
196 REFERENCES serial.item (id)
198 DEFERRABLE INITIALLY DEFERRED,
200 REFERENCES actor.usr (id)
201 DEFERRABLE INITIALLY DEFERRED,
202 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
203 pub BOOL NOT NULL DEFAULT FALSE,
208 CREATE TABLE serial.bib_summary (
209 id SERIAL PRIMARY KEY,
210 distribution INT NOT NULL
211 REFERENCES serial.distribution (id)
213 DEFERRABLE INITIALLY DEFERRED,
214 generated_coverage TEXT NOT NULL,
215 textual_holdings TEXT
218 CREATE TABLE serial.sup_summary (
219 id SERIAL PRIMARY KEY,
220 distribution INT NOT NULL
221 REFERENCES serial.distribution (id)
223 DEFERRABLE INITIALLY DEFERRED,
224 generated_coverage TEXT NOT NULL,
225 textual_holdings TEXT
228 CREATE TABLE serial.index_summary (
229 id SERIAL PRIMARY KEY,
230 distribution INT NOT NULL
231 REFERENCES serial.distribution (id)
233 DEFERRABLE INITIALLY DEFERRED,
234 generated_coverage TEXT NOT NULL,
235 textual_holdings TEXT