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 );
27 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;
29 CREATE TABLE serial.subscription (
30 id SERIAL PRIMARY KEY,
31 owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
32 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
33 end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription
34 record_entry BIGINT REFERENCES biblio.record_entry (id)
36 DEFERRABLE INITIALLY DEFERRED,
37 expected_date_offset INTERVAL
38 -- acquisitions/business-side tables link to here
42 CREATE TABLE serial.caption_and_pattern (
43 id SERIAL PRIMARY KEY,
44 subscription INT NOT NULL
45 REFERENCES serial.subscription (id)
47 DEFERRABLE INITIALLY DEFERRED,
49 CONSTRAINT cap_type CHECK ( type in
50 ( 'basic', 'supplement', 'index' )),
51 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
52 active BOOL NOT NULL DEFAULT FALSE,
53 pattern_code TEXT NOT NULL, -- must contain JSON
67 --at least one distribution per org_unit holding issues
68 CREATE TABLE serial.distribution (
69 id SERIAL PRIMARY KEY,
70 record_entry BIGINT REFERENCES serial.record_entry (id)
72 DEFERRABLE INITIALLY DEFERRED,
73 subscription INT NOT NULL
74 REFERENCES serial.subscription (id)
76 DEFERRABLE INITIALLY DEFERRED,
77 holding_lib INT NOT NULL
78 REFERENCES actor.org_unit (id)
79 DEFERRABLE INITIALLY DEFERRED,
81 receive_call_number BIGINT REFERENCES asset.call_number (id)
82 DEFERRABLE INITIALLY DEFERRED,
83 receive_unit_template INT REFERENCES asset.copy_template (id)
84 DEFERRABLE INITIALLY DEFERRED,
85 bind_call_number BIGINT REFERENCES asset.call_number (id)
86 DEFERRABLE INITIALLY DEFERRED,
87 bind_unit_template INT REFERENCES asset.copy_template (id)
88 DEFERRABLE INITIALLY DEFERRED,
90 unit_label_suffix TEXT
92 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
94 CREATE TABLE serial.stream (
95 id SERIAL PRIMARY KEY,
96 distribution INT NOT NULL
97 REFERENCES serial.distribution (id)
99 DEFERRABLE INITIALLY DEFERRED,
103 CREATE UNIQUE INDEX label_once_per_dist
104 ON serial.stream (distribution, routing_label)
105 WHERE routing_label IS NOT NULL;
107 CREATE TABLE serial.routing_list_user (
108 id SERIAL PRIMARY KEY,
110 REFERENCES serial.stream
112 DEFERRABLE INITIALLY DEFERRED,
113 pos INT NOT NULL DEFAULT 1,
114 reader INT REFERENCES actor.usr
116 DEFERRABLE INITIALLY DEFERRED,
119 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
120 CONSTRAINT reader_or_dept CHECK
122 -- Recipient is a person or a department, but not both
123 (reader IS NOT NULL AND department IS NULL) OR
124 (reader IS NULL AND department IS NOT NULL)
128 CREATE TABLE serial.issuance (
129 id SERIAL PRIMARY KEY,
131 REFERENCES actor.usr (id)
132 DEFERRABLE INITIALLY DEFERRED,
134 REFERENCES actor.usr (id)
135 DEFERRABLE INITIALLY DEFERRED,
136 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
137 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
138 subscription INT NOT NULL
139 REFERENCES serial.subscription (id)
141 DEFERRABLE INITIALLY DEFERRED,
143 date_published TIMESTAMP WITH TIME ZONE,
145 holding_type TEXT CONSTRAINT valid_holding_type CHECK
148 OR holding_type IN ('basic','supplement','index')
151 -- TODO: add columns for separate enumeration/chronology values
154 CREATE TABLE serial.unit (
157 contents TEXT NOT NULL
158 ) INHERITS (asset.copy);
160 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
162 CREATE TABLE serial.item (
163 id SERIAL PRIMARY KEY,
165 REFERENCES actor.usr (id)
166 DEFERRABLE INITIALLY DEFERRED,
168 REFERENCES actor.usr (id)
169 DEFERRABLE INITIALLY DEFERRED,
170 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
171 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
172 issuance INT NOT NULL
173 REFERENCES serial.issuance (id)
175 DEFERRABLE INITIALLY DEFERRED,
177 REFERENCES serial.stream (id)
179 DEFERRABLE INITIALLY DEFERRED,
180 unit INT REFERENCES serial.unit (id)
182 DEFERRABLE INITIALLY DEFERRED,
183 uri INT REFERENCES asset.uri (id)
185 DEFERRABLE INITIALLY DEFERRED,
186 date_expected TIMESTAMP WITH TIME ZONE,
187 date_received TIMESTAMP WITH TIME ZONE
190 CREATE TABLE serial.item_note (
191 id SERIAL PRIMARY KEY,
193 REFERENCES serial.item (id)
195 DEFERRABLE INITIALLY DEFERRED,
197 REFERENCES actor.usr (id)
198 DEFERRABLE INITIALLY DEFERRED,
199 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
200 pub BOOL NOT NULL DEFAULT FALSE,
205 CREATE TABLE serial.bib_summary (
206 id SERIAL PRIMARY KEY,
207 distribution INT NOT NULL
208 REFERENCES serial.distribution (id)
210 DEFERRABLE INITIALLY DEFERRED,
211 generated_coverage TEXT NOT NULL,
212 textual_holdings TEXT
215 CREATE TABLE serial.sup_summary (
216 id SERIAL PRIMARY KEY,
217 distribution INT NOT NULL
218 REFERENCES serial.distribution (id)
220 DEFERRABLE INITIALLY DEFERRED,
221 generated_coverage TEXT NOT NULL,
222 textual_holdings TEXT
225 CREATE TABLE serial.index_summary (
226 id SERIAL PRIMARY KEY,
227 distribution INT NOT NULL
228 REFERENCES serial.distribution (id)
230 DEFERRABLE INITIALLY DEFERRED,
231 generated_coverage TEXT NOT NULL,
232 textual_holdings TEXT