1 -- The following DROP statements are outside of the transaction.
2 -- That way if one of the tables doesn't exist, the DROP will
3 -- fail but the rest of the script can still run.
5 DROP TABLE serial.bib_summary CASCADE;
7 DROP TABLE serial.index_summary CASCADE;
9 DROP TABLE serial.sup_summary CASCADE;
11 DROP TABLE serial.issuance CASCADE;
13 DROP TABLE serial.binding_unit CASCADE;
15 DROP TABLE serial.subscription CASCADE;
19 INSERT INTO config.upgrade_log (version) VALUES ('0288'); -- Scott McKellar
21 CREATE TABLE asset.copy_template (
22 id SERIAL PRIMARY KEY,
23 owning_lib INT NOT NULL
24 REFERENCES actor.org_unit (id)
25 DEFERRABLE INITIALLY DEFERRED,
26 creator BIGINT NOT NULL
27 REFERENCES actor.usr (id)
28 DEFERRABLE INITIALLY DEFERRED,
29 editor BIGINT NOT NULL
30 REFERENCES actor.usr (id)
31 DEFERRABLE INITIALLY DEFERRED,
32 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
33 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
35 -- columns above this point are attributes of the template itself
36 -- columns after this point are attributes of the copy this template modifies/creates
37 circ_lib INT REFERENCES actor.org_unit (id)
38 DEFERRABLE INITIALLY DEFERRED,
39 status INT REFERENCES config.copy_status (id)
40 DEFERRABLE INITIALLY DEFERRED,
41 location INT REFERENCES asset.copy_location (id)
42 DEFERRABLE INITIALLY DEFERRED,
43 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
44 loan_duration IS NULL OR loan_duration IN (1,2,3)),
45 fine_level INT CONSTRAINT valid_fine_level CHECK (
46 fine_level IS NULL OR loan_duration IN (1,2,3)),
52 deposit_amount NUMERIC(6,2),
62 CREATE TABLE serial.subscription (
63 id SERIAL PRIMARY KEY,
64 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
65 end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription
66 record_entry BIGINT REFERENCES serial.record_entry (id)
68 DEFERRABLE INITIALLY DEFERRED,
69 expected_date_offset INTERVAL
70 -- acquisitions/business-side tables link to here
73 --at least one distribution per org_unit holding issues
74 CREATE TABLE serial.distribution (
75 id SERIAL PRIMARY KEY,
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
96 CREATE TABLE serial.stream (
97 id SERIAL PRIMARY KEY,
98 distribution INT NOT NULL
99 REFERENCES serial.distribution (id)
101 DEFERRABLE INITIALLY DEFERRED,
105 CREATE UNIQUE INDEX label_once_per_dist
106 ON serial.stream (distribution, routing_label)
107 WHERE routing_label IS NOT NULL;
109 CREATE TABLE serial.routing_list_user (
110 id SERIAL PRIMARY KEY,
112 REFERENCES serial.stream
114 DEFERRABLE INITIALLY DEFERRED,
115 pos INT NOT NULL DEFAULT 1,
116 reader INT REFERENCES actor.usr
118 DEFERRABLE INITIALLY DEFERRED,
121 CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
122 CONSTRAINT reader_or_dept CHECK
124 -- Recipient is a person or a department, but not both
125 (reader IS NOT NULL AND department IS NULL) OR
126 (reader IS NULL AND department IS NOT NULL)
130 CREATE TABLE serial.issuance (
131 id SERIAL PRIMARY KEY,
133 REFERENCES actor.usr (id)
134 DEFERRABLE INITIALLY DEFERRED,
136 REFERENCES actor.usr (id)
137 DEFERRABLE INITIALLY DEFERRED,
138 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
139 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
140 subscription INT NOT NULL
141 REFERENCES serial.subscription (id)
143 DEFERRABLE INITIALLY DEFERRED,
145 date_published TIMESTAMP WITH TIME ZONE,
147 holding_type TEXT CONSTRAINT valid_holding_type CHECK
150 OR holding_type IN ('basic','supplement','index')
153 -- TODO: add columns for separate enumeration/chronology values
156 CREATE TABLE serial.unit (
159 contents TEXT NOT NULL
160 ) INHERITS (asset.copy);
162 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
164 CREATE TABLE serial.item (
165 id SERIAL PRIMARY KEY,
167 REFERENCES actor.usr (id)
168 DEFERRABLE INITIALLY DEFERRED,
170 REFERENCES actor.usr (id)
171 DEFERRABLE INITIALLY DEFERRED,
172 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
173 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
174 issuance INT NOT NULL
175 REFERENCES serial.issuance (id)
177 DEFERRABLE INITIALLY DEFERRED,
179 REFERENCES serial.stream (id)
181 DEFERRABLE INITIALLY DEFERRED,
182 unit INT REFERENCES serial.unit (id)
184 DEFERRABLE INITIALLY DEFERRED,
185 uri INT REFERENCES asset.uri (id)
187 DEFERRABLE INITIALLY DEFERRED,
188 date_expected TIMESTAMP WITH TIME ZONE,
189 date_received TIMESTAMP WITH TIME ZONE
192 CREATE TABLE serial.item_note (
193 id SERIAL PRIMARY KEY,
195 REFERENCES serial.item (id)
197 DEFERRABLE INITIALLY DEFERRED,
199 REFERENCES actor.usr (id)
200 DEFERRABLE INITIALLY DEFERRED,
201 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
202 pub BOOL NOT NULL DEFAULT FALSE,
207 CREATE TABLE serial.bib_summary (
208 id SERIAL PRIMARY KEY,
209 distribution INT NOT NULL
210 REFERENCES serial.distribution (id)
212 DEFERRABLE INITIALLY DEFERRED,
213 generated_coverage TEXT NOT NULL,
214 textual_holdings TEXT
217 CREATE TABLE serial.sup_summary (
218 id SERIAL PRIMARY KEY,
219 distribution INT NOT NULL
220 REFERENCES serial.distribution (id)
222 DEFERRABLE INITIALLY DEFERRED,
223 generated_coverage TEXT NOT NULL,
224 textual_holdings TEXT
227 CREATE TABLE serial.index_summary (
228 id SERIAL PRIMARY KEY,
229 distribution INT NOT NULL
230 REFERENCES serial.distribution (id)
232 DEFERRABLE INITIALLY DEFERRED,
233 generated_coverage TEXT NOT NULL,
234 textual_holdings TEXT