1 DROP SCHEMA acq CASCADE;
11 CREATE TABLE acq.currency_type (
12 code TEXT PRIMARY KEY,
16 -- Use the ISO 4217 abbreviations for currency codes
17 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
18 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
19 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
21 CREATE TABLE acq.exchange_rate (
22 id SERIAL PRIMARY KEY,
23 from_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
24 to_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
25 ratio NUMERIC NOT NULL,
26 CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
29 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
30 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
32 CREATE TABLE acq.provider (
33 id SERIAL PRIMARY KEY,
35 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
36 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
38 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
41 CREATE TABLE acq.provider_address (
42 id SERIAL PRIMARY KEY,
43 valid BOOL NOT NULL DEFAULT TRUE,
45 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
46 street1 TEXT NOT NULL,
51 country TEXT NOT NULL,
52 post_code TEXT NOT NULL
55 CREATE TABLE acq.provider_contact (
56 id SERIAL PRIMARY KEY,
57 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
59 role TEXT, -- free-form.. e.g. "our sales guy"
64 CREATE TABLE acq.provider_contact_address (
65 id SERIAL PRIMARY KEY,
66 valid BOOL NOT NULL DEFAULT TRUE,
68 contact INT NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
69 street1 TEXT NOT NULL,
74 country TEXT NOT NULL,
75 post_code TEXT NOT NULL
79 CREATE TABLE acq.funding_source (
80 id SERIAL PRIMARY KEY,
82 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
83 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
85 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
88 CREATE TABLE acq.funding_source_credit (
89 id SERIAL PRIMARY KEY,
90 funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
91 amount NUMERIC NOT NULL,
95 CREATE TABLE acq.fund (
96 id SERIAL PRIMARY KEY,
97 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
99 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
100 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
102 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
105 CREATE TABLE acq.fund_debit (
106 id SERIAL PRIMARY KEY,
107 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
108 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
109 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
110 amount NUMERIC NOT NULL,
111 encumbrance BOOL NOT NULL DEFAULT TRUE,
112 debit_type TEXT NOT NULL,
113 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED
116 CREATE TABLE acq.fund_allocation (
117 id SERIAL PRIMARY KEY,
118 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
119 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
121 percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
122 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
124 CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
128 CREATE TABLE acq.picklist (
129 id SERIAL PRIMARY KEY,
130 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
131 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
132 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
133 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
135 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
136 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
137 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
140 CREATE TABLE acq.purchase_order (
141 id SERIAL PRIMARY KEY,
142 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
143 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
144 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
145 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
146 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
147 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
148 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
149 state TEXT NOT NULL DEFAULT 'new'
151 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
152 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
153 CREATE INDEX po_state_idx ON acq.purchase_order (state);
155 CREATE TABLE acq.po_note (
156 id SERIAL PRIMARY KEY,
157 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
158 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
159 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
160 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
161 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
164 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
166 CREATE TABLE acq.lineitem (
167 id BIGSERIAL PRIMARY KEY,
168 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
169 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
170 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
171 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
172 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
173 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
174 expected_recv_time TIMESTAMP WITH TIME ZONE,
175 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
176 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
178 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
180 item_count INT NOT NULL DEFAULT 0,
181 state TEXT NOT NULL DEFAULT 'new',
182 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
184 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
185 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
187 CREATE TABLE acq.lineitem_note (
188 id SERIAL PRIMARY KEY,
189 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
190 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
191 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
192 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
193 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
196 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
198 CREATE TABLE acq.lineitem_detail (
199 id BIGSERIAL PRIMARY KEY,
200 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
201 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
202 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
203 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
206 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
207 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
208 recv_time TIMESTAMP WITH TIME ZONE
211 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
213 CREATE TABLE acq.lineitem_attr_definition (
214 id BIGSERIAL PRIMARY KEY,
216 description TEXT NOT NULL,
217 remove TEXT NOT NULL DEFAULT '',
218 ident BOOL NOT NULL DEFAULT FALSE
221 CREATE TABLE acq.lineitem_marc_attr_definition (
222 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
224 ) INHERITS (acq.lineitem_attr_definition);
226 CREATE TABLE acq.lineitem_provider_attr_definition (
227 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
229 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
230 ) INHERITS (acq.lineitem_attr_definition);
232 CREATE TABLE acq.lineitem_generated_attr_definition (
233 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
235 ) INHERITS (acq.lineitem_attr_definition);
237 CREATE TABLE acq.lineitem_usr_attr_definition (
238 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
239 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
240 ) INHERITS (acq.lineitem_attr_definition);
242 CREATE TABLE acq.lineitem_local_attr_definition (
243 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
244 ) INHERITS (acq.lineitem_attr_definition);
246 CREATE TABLE acq.lineitem_attr (
247 id BIGSERIAL PRIMARY KEY,
248 definition BIGINT NOT NULL,
249 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
250 attr_type TEXT NOT NULL,
251 attr_name TEXT NOT NULL,
252 attr_value TEXT NOT NULL
255 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
256 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
257 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
263 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
264 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
265 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
266 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
267 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
268 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
269 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
270 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
271 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
272 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
273 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
279 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
280 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
284 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
285 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
288 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
289 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
293 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
296 Suggested vendor fields:
304 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
312 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
314 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
316 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
317 IF (atype = 'lineitem_provider_attr_definition') THEN
318 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
319 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
322 IF (atype = 'lineitem_provider_attr_definition') THEN
323 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
324 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
325 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
326 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
327 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
330 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
332 IF (value IS NOT NULL AND value <> '') THEN
333 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
334 VALUES (NEW.id, adef.id, atype, adef.code, value);
345 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
347 IF TG_OP = 'UPDATE' THEN
348 DELETE FROM acq.lineitem_attr
349 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
352 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
358 CREATE TRIGGER cleanup_lineitem_trigger
359 BEFORE UPDATE OR DELETE ON acq.lineitem
360 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
362 CREATE TRIGGER ingest_lineitem_trigger
363 AFTER INSERT OR UPDATE ON acq.lineitem
364 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
366 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
370 IF from_ex = to_ex THEN
374 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
379 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
390 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
391 SELECT $3 * acq.exchange_ratio($1, $2);
394 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
395 SELECT funding_source,
396 SUM(amount) AS amount
397 FROM acq.funding_source_credit
400 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
401 SELECT funding_source,
402 SUM(amount)::NUMERIC(100,2) AS amount
404 SELECT funding_source,
405 SUM(a.amount)::NUMERIC(100,2) AS amount
406 FROM acq.fund_allocation a
407 WHERE a.percent IS NULL
410 SELECT funding_source,
411 SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
412 FROM acq.fund_allocation a
413 WHERE a.amount IS NULL
418 CREATE OR REPLACE VIEW acq.funding_source_balance AS
419 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
420 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
421 FROM acq.funding_source_credit_total c
422 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
425 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
427 SUM(amount)::NUMERIC(100,2) AS amount
430 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
431 FROM acq.fund_allocation a
432 JOIN acq.fund f ON (a.fund = f.id)
433 JOIN acq.funding_source s ON (a.funding_source = s.id)
434 WHERE a.percent IS NULL
438 SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
439 FROM acq.fund_allocation a
440 JOIN acq.fund f ON (a.fund = f.id)
441 JOIN acq.funding_source s ON (a.funding_source = s.id)
442 WHERE a.amount IS NULL
447 CREATE OR REPLACE VIEW acq.fund_debit_total AS
450 SUM(amount) AS amount
454 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
456 SUM(amount) AS amount
457 FROM acq.fund_debit_total
458 WHERE encumbrance IS TRUE
461 CREATE OR REPLACE VIEW acq.fund_spent_total AS
463 SUM(amount) AS amount
464 FROM acq.fund_debit_total
465 WHERE encumbrance IS FALSE
468 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
470 c.amount - COALESCE(d.amount,0.0) AS amount
471 FROM acq.fund_allocation_total c
472 LEFT JOIN acq.fund_debit_total d USING (fund);
474 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
476 c.amount - COALESCE(d.amount,0.0) AS amount
477 FROM acq.fund_allocation_total c
478 LEFT JOIN acq.fund_spent_total d USING (fund);