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 -- must create this rule explicitly; it is not inherited from asset.copy
166 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;
168 CREATE TABLE serial.item (
169 id SERIAL PRIMARY KEY,
171 REFERENCES actor.usr (id)
172 DEFERRABLE INITIALLY DEFERRED,
174 REFERENCES actor.usr (id)
175 DEFERRABLE INITIALLY DEFERRED,
176 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
177 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
178 issuance INT NOT NULL
179 REFERENCES serial.issuance (id)
181 DEFERRABLE INITIALLY DEFERRED,
183 REFERENCES serial.stream (id)
185 DEFERRABLE INITIALLY DEFERRED,
186 unit INT REFERENCES serial.unit (id)
188 DEFERRABLE INITIALLY DEFERRED,
189 uri INT REFERENCES asset.uri (id)
191 DEFERRABLE INITIALLY DEFERRED,
192 date_expected TIMESTAMP WITH TIME ZONE,
193 date_received TIMESTAMP WITH TIME ZONE
196 CREATE TABLE serial.item_note (
197 id SERIAL PRIMARY KEY,
199 REFERENCES serial.item (id)
201 DEFERRABLE INITIALLY DEFERRED,
203 REFERENCES actor.usr (id)
204 DEFERRABLE INITIALLY DEFERRED,
205 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
206 pub BOOL NOT NULL DEFAULT FALSE,
211 CREATE TABLE serial.bib_summary (
212 id SERIAL PRIMARY KEY,
213 distribution INT NOT NULL
214 REFERENCES serial.distribution (id)
216 DEFERRABLE INITIALLY DEFERRED,
217 generated_coverage TEXT NOT NULL,
218 textual_holdings TEXT
221 CREATE TABLE serial.sup_summary (
222 id SERIAL PRIMARY KEY,
223 distribution INT NOT NULL
224 REFERENCES serial.distribution (id)
226 DEFERRABLE INITIALLY DEFERRED,
227 generated_coverage TEXT NOT NULL,
228 textual_holdings TEXT
231 CREATE TABLE serial.index_summary (
232 id SERIAL PRIMARY KEY,
233 distribution INT NOT NULL
234 REFERENCES serial.distribution (id)
236 DEFERRABLE INITIALLY DEFERRED,
237 generated_coverage TEXT NOT NULL,
238 textual_holdings TEXT