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 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
133 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
134 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
135 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
138 CREATE TABLE acq.purchase_order (
139 id SERIAL PRIMARY KEY,
140 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
141 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
142 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
143 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
144 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
145 state TEXT NOT NULL DEFAULT 'new'
147 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
148 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
149 CREATE INDEX po_state_idx ON acq.purchase_order (state);
151 CREATE TABLE acq.po_note (
152 id SERIAL PRIMARY KEY,
153 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
154 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
155 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
156 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
157 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
160 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
162 CREATE TABLE acq.lineitem (
163 id BIGSERIAL PRIMARY KEY,
164 selector INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
165 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
166 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
167 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
168 expected_recv_time TIMESTAMP WITH TIME ZONE,
169 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
170 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
172 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
174 item_count INT NOT NULL DEFAULT 0,
175 state TEXT NOT NULL DEFAULT 'new',
176 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
178 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
179 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
181 CREATE TABLE acq.lineitem_note (
182 id SERIAL PRIMARY KEY,
183 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
184 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
185 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
186 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
187 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
190 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
192 CREATE TABLE acq.lineitem_detail (
193 id BIGSERIAL PRIMARY KEY,
194 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
195 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
196 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
197 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
200 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
201 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
202 recv_time TIMESTAMP WITH TIME ZONE
205 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
207 CREATE TABLE acq.lineitem_attr_definition (
208 id BIGSERIAL PRIMARY KEY,
210 description TEXT NOT NULL,
211 remove TEXT NOT NULL DEFAULT '',
212 ident BOOL NOT NULL DEFAULT FALSE
215 CREATE TABLE acq.lineitem_marc_attr_definition (
216 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
218 ) INHERITS (acq.lineitem_attr_definition);
220 CREATE TABLE acq.lineitem_provider_attr_definition (
221 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
223 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
224 ) INHERITS (acq.lineitem_attr_definition);
226 CREATE TABLE acq.lineitem_generated_attr_definition (
227 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
229 ) INHERITS (acq.lineitem_attr_definition);
231 CREATE TABLE acq.lineitem_usr_attr_definition (
232 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
233 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
234 ) INHERITS (acq.lineitem_attr_definition);
236 CREATE TABLE acq.lineitem_local_attr_definition (
237 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
238 ) INHERITS (acq.lineitem_attr_definition);
240 CREATE TABLE acq.lineitem_attr (
241 id BIGSERIAL PRIMARY KEY,
242 definition BIGINT NOT NULL,
243 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
244 attr_type TEXT NOT NULL,
245 attr_name TEXT NOT NULL,
246 attr_value TEXT NOT NULL
249 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
250 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
251 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
257 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
258 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)]');
259 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
260 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
261 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
262 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
263 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
264 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
265 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
266 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
267 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
273 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
274 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
278 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
279 SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
282 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
283 SELECT public.extract_marc_field('authority.record_entry', $1, $2);
287 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
290 Suggested vendor fields:
298 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
306 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
308 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
310 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
311 IF (atype = 'lineitem_provider_attr_definition') THEN
312 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
313 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
316 IF (atype = 'lineitem_provider_attr_definition') THEN
317 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
318 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
319 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
320 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
321 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
324 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
326 IF (value IS NOT NULL AND value <> '') THEN
327 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
328 VALUES (NEW.id, adef.id, atype, adef.code, value);
339 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
341 IF TG_OP = 'UPDATE' THEN
342 DELETE FROM acq.lineitem_attr
343 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
346 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
352 CREATE TRIGGER cleanup_lineitem_trigger
353 BEFORE UPDATE OR DELETE ON acq.lineitem
354 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
356 CREATE TRIGGER ingest_lineitem_trigger
357 AFTER INSERT OR UPDATE ON acq.lineitem
358 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
360 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
364 IF from_ex = to_ex THEN
368 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
373 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
384 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
385 SELECT $3 * acq.exchange_ratio($1, $2);
388 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
389 SELECT funding_source,
390 SUM(amount) AS amount
391 FROM acq.funding_source_credit
394 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
395 SELECT funding_source,
396 SUM(amount)::NUMERIC(100,2) AS amount
398 SELECT funding_source,
399 SUM(a.amount)::NUMERIC(100,2) AS amount
400 FROM acq.fund_allocation a
401 WHERE a.percent IS NULL
404 SELECT funding_source,
405 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
406 FROM acq.fund_allocation a
407 WHERE a.amount IS NULL
412 CREATE OR REPLACE VIEW acq.funding_source_balance AS
413 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
414 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
415 FROM acq.funding_source_credit_total c
416 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
419 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
421 SUM(amount)::NUMERIC(100,2) AS amount
424 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
425 FROM acq.fund_allocation a
426 JOIN acq.fund f ON (a.fund = f.id)
427 JOIN acq.funding_source s ON (a.funding_source = s.id)
428 WHERE a.percent IS NULL
432 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
433 FROM acq.fund_allocation a
434 JOIN acq.fund f ON (a.fund = f.id)
435 JOIN acq.funding_source s ON (a.funding_source = s.id)
436 WHERE a.amount IS NULL
441 CREATE OR REPLACE VIEW acq.fund_debit_total AS
444 SUM(amount) AS amount
448 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
450 SUM(amount) AS amount
451 FROM acq.fund_debit_total
452 WHERE encumbrance IS TRUE
455 CREATE OR REPLACE VIEW acq.fund_spent_total AS
457 SUM(amount) AS amount
458 FROM acq.fund_debit_total
459 WHERE encumbrance IS FALSE
462 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
464 c.amount - COALESCE(d.amount,0.0) AS amount
465 FROM acq.fund_allocation_total c
466 LEFT JOIN acq.fund_debit_total d USING (fund);
468 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
470 c.amount - COALESCE(d.amount,0.0) AS amount
471 FROM acq.fund_allocation_total c
472 LEFT JOIN acq.fund_spent_total d USING (fund);