1 -- Evergreen DB patch 0577.schema.vandelay-item-import-copy-loc-ancestors.sql
3 -- Ingest items copy location inheritance
7 -- check whether patch can be applied
8 SELECT evergreen.upgrade_deps_block_check('0577', :eg_version); -- berick
10 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
35 attr_set vandelay.import_item%ROWTYPE;
41 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
45 attr_set.definition := attr_def.id;
47 -- Build the combined XPath
51 WHEN attr_def.owning_lib IS NULL THEN 'null()'
52 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
53 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
58 WHEN attr_def.circ_lib IS NULL THEN 'null()'
59 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
60 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
65 WHEN attr_def.call_number IS NULL THEN 'null()'
66 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
67 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
72 WHEN attr_def.copy_number IS NULL THEN 'null()'
73 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
74 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
79 WHEN attr_def.status IS NULL THEN 'null()'
80 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
81 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
86 WHEN attr_def.location IS NULL THEN 'null()'
87 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
88 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
93 WHEN attr_def.circulate IS NULL THEN 'null()'
94 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
95 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
100 WHEN attr_def.deposit IS NULL THEN 'null()'
101 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
102 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
107 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
108 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
109 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
114 WHEN attr_def.ref IS NULL THEN 'null()'
115 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
116 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
121 WHEN attr_def.holdable IS NULL THEN 'null()'
122 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
123 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
128 WHEN attr_def.price IS NULL THEN 'null()'
129 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
130 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
135 WHEN attr_def.barcode IS NULL THEN 'null()'
136 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
137 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
142 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
143 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
144 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
149 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
150 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
151 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
156 WHEN attr_def.alert_message IS NULL THEN 'null()'
157 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
158 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
163 WHEN attr_def.opac_visible IS NULL THEN 'null()'
164 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
165 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
170 WHEN attr_def.pub_note IS NULL THEN 'null()'
171 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
172 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
176 WHEN attr_def.priv_note IS NULL THEN 'null()'
177 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
178 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
185 call_number || '|' ||
186 copy_number || '|' ||
191 deposit_amount || '|' ||
196 circ_modifier || '|' ||
197 circ_as_type || '|' ||
198 alert_message || '|' ||
203 -- RAISE NOTICE 'XPath: %', xpath;
207 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
208 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
209 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
210 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
213 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
214 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
216 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
217 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
219 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
220 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
221 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
224 -- search up the org unit tree for a matching copy location
226 WITH RECURSIVE anscestor_depth AS (
230 FROM actor.org_unit ou
231 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
232 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
237 FROM actor.org_unit ou
238 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
239 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
240 ) SELECT cpl.id INTO attr_set.location
241 FROM anscestor_depth a
242 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
243 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
244 ORDER BY a.depth DESC
247 attr_set.circulate :=
248 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
249 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
252 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
253 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
256 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
257 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
259 attr_set.opac_visible :=
260 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
261 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
264 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
265 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
267 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
268 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
269 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
271 attr_set.call_number := tmp_attr_set.cn; -- TEXT
272 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
273 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
274 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
275 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
276 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
277 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
278 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
280 RETURN NEXT attr_set;