1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 production_tables TEXT[];
269 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
270 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
271 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
272 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
279 migration_schema ALIAS FOR $1;
280 production_table ALIAS FOR $2;
281 base_staging_table TEXT;
284 base_staging_table = REPLACE( production_table, '.', '_' );
285 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
286 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
300 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
301 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
303 IF suffix = before_comma THEN
307 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
308 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
309 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
311 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
313 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
315 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
317 full_name TEXT := $1;
319 family_name TEXT := '';
320 first_given_name TEXT := '';
321 second_given_name TEXT := '';
326 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
327 IF temp ilike '%MR.%' THEN
329 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
331 IF temp ilike '%MRS.%' THEN
333 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
335 IF temp ilike '%MS.%' THEN
337 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
339 IF temp ilike '%DR.%' THEN
341 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
343 IF temp ilike '%JR%' THEN
345 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
347 IF temp ilike '%JR,%' THEN
349 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
351 IF temp ilike '%SR%' THEN
353 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
355 IF temp ilike '%SR,%' THEN
357 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
359 IF temp ~ E'\\sII$' THEN
361 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
363 IF temp ~ E'\\sIII$' THEN
365 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
367 IF temp ~ E'\\sIV$' THEN
369 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
372 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
373 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
374 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
376 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
378 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
380 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
382 full_name TEXT := $1;
384 family_name TEXT := '';
385 first_given_name TEXT := '';
386 second_given_name TEXT := '';
391 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
392 IF temp ilike '%MR.%' THEN
394 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
396 IF temp ilike '%MRS.%' THEN
398 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
400 IF temp ilike '%MS.%' THEN
402 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
404 IF temp ilike '%DR.%' THEN
406 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
408 IF temp ilike '%JR.%' THEN
410 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
412 IF temp ilike '%JR,%' THEN
414 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
416 IF temp ilike '%SR.%' THEN
418 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
420 IF temp ilike '%SR,%' THEN
422 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
424 IF temp like '%III%' THEN
426 temp := REGEXP_REPLACE( temp, E'III', '' );
428 IF temp like '%II%' THEN
430 temp := REGEXP_REPLACE( temp, E'II', '' );
432 IF temp like '%IV%' THEN
434 temp := REGEXP_REPLACE( temp, E'IV', '' );
437 temp := REGEXP_REPLACE( temp, '\(\)', '');
438 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
439 family_name := REGEXP_REPLACE( family_name, ',', '' );
440 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
441 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
442 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
443 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
445 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
447 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
451 full_name TEXT := $1;
453 family_name TEXT := '';
454 first_given_name TEXT := '';
455 second_given_name TEXT := '';
459 temp := BTRIM(full_name);
460 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
461 --IF temp ~ '^\S{2,}\.' THEN
462 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
463 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
465 --IF temp ~ '\S{2,}\.$' THEN
466 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
467 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
469 IF temp ilike '%MR.%' THEN
471 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
473 IF temp ilike '%MRS.%' THEN
475 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
477 IF temp ilike '%MS.%' THEN
479 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
481 IF temp ilike '%DR.%' THEN
483 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
485 IF temp ilike '%JR.%' THEN
487 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
489 IF temp ilike '%JR,%' THEN
491 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
493 IF temp ilike '%SR.%' THEN
495 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
497 IF temp ilike '%SR,%' THEN
499 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
501 IF temp like '%III%' THEN
503 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
505 IF temp like '%II%' THEN
507 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
511 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
512 temp := BTRIM(REPLACE( temp, family_name, '' ));
513 family_name := REPLACE( family_name, ',', '' );
515 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
516 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
518 first_given_name := temp;
519 second_given_name := '';
522 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
523 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
524 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
525 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
527 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
528 second_given_name := temp;
529 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
533 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
535 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
537 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
539 city_state_zip TEXT := $1;
544 zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
545 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
546 IF city_state_zip ~ ',' THEN
547 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
548 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
550 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
551 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
552 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
554 IF city_state_zip ~ E'^\\S+$' THEN
555 city := city_state_zip;
558 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
559 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
563 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
565 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
567 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
568 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
570 fullstring TEXT := $1;
580 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
581 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
584 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
586 IF fullstring ~ ',' THEN
587 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
588 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
590 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
591 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
592 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
594 IF fullstring ~ E'^\\S+$' THEN
595 scratch1 := fullstring;
598 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
599 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
604 IF scratch1 ~ '[\$]' THEN
605 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
606 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
608 IF scratch1 ~ '\s' THEN
609 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
610 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
617 IF scratch2 ~ '^\d' THEN
618 address1 := scratch2;
621 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
622 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
626 TRIM(BOTH ' ' FROM address1)
627 ,TRIM(BOTH ' ' FROM address2)
628 ,TRIM(BOTH ' ' FROM city)
629 ,TRIM(BOTH ' ' FROM state)
630 ,TRIM(BOTH ' ' FROM zip)
633 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
635 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
638 use Geo::StreetAddress::US;
640 my $a = Geo::StreetAddress::US->parse_location($address);
643 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
644 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
649 $$ LANGUAGE PLPERLU STABLE;
651 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
652 CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
653 INSERT INTO migration_tools.usps_suffixes VALUES
686 ('BOULEVARD','BLVD'),
790 ('EXPRESSWAY','EXPY'),
795 ('EXTENSIONS','EXTS'),
906 ('JUNCTIONS','JCTS'),
968 ('MOUNTAINS','MTNS'),
1093 ('STRAVENUE','STRA'),
1113 ('THROUGHWAY','TRWY'),
1120 ('TRAFFICWAY','TRFY'),
1139 ('TURNPIKE','TPKE'),
1141 ('UNDERPASS','UPAS'),
1155 ('VILLAGES','VLGS'),
1186 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1187 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1192 --RAISE INFO 'suffix = %', suffix;
1193 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1194 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1198 $$ LANGUAGE PLPGSQL STRICT STABLE;
1200 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1203 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1207 $$ LANGUAGE PLPGSQL STRICT STABLE;
1209 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1213 IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
1214 IF o::BIGINT < t THEN
1221 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1223 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1225 migration_schema ALIAS FOR $1;
1229 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1234 $$ LANGUAGE PLPGSQL STRICT STABLE;
1236 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1238 migration_schema ALIAS FOR $1;
1242 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1247 $$ LANGUAGE PLPGSQL STRICT STABLE;
1249 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1251 migration_schema ALIAS FOR $1;
1255 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1260 $$ LANGUAGE PLPGSQL STRICT STABLE;
1262 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1264 migration_schema ALIAS FOR $1;
1268 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1273 $$ LANGUAGE PLPGSQL STRICT STABLE;
1275 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1277 migration_schema ALIAS FOR $1;
1279 patron_table ALIAS FOR $2;
1280 default_patron_profile ALIAS FOR $3;
1283 sql_where1 TEXT := '';
1284 sql_where2 TEXT := '';
1285 sql_where3 TEXT := '';
1288 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1290 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1292 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1293 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1294 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1295 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1296 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1297 --RAISE INFO 'sql = %', sql;
1298 PERFORM migration_tools.exec( $1, sql );
1300 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1302 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1304 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1307 $$ LANGUAGE PLPGSQL STRICT STABLE;
1309 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1311 migration_schema ALIAS FOR $1;
1313 item_table ALIAS FOR $2;
1316 sql_where1 TEXT := '';
1317 sql_where2 TEXT := '';
1318 sql_where3 TEXT := '';
1321 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1323 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1325 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
1326 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1327 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1328 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1329 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1330 --RAISE INFO 'sql = %', sql;
1331 PERFORM migration_tools.exec( $1, sql );
1334 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1336 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1339 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1341 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1343 migration_schema ALIAS FOR $1;
1344 base_copy_location_map TEXT;
1345 item_table ALIAS FOR $2;
1348 sql_where1 TEXT := '';
1349 sql_where2 TEXT := '';
1350 sql_where3 TEXT := '';
1353 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1355 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1357 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1358 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1359 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1360 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1361 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1362 --RAISE INFO 'sql = %', sql;
1363 PERFORM migration_tools.exec( $1, sql );
1366 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1368 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1371 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1373 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
1374 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1376 migration_schema ALIAS FOR $1;
1378 circ_table ALIAS FOR $2;
1379 item_table ALIAS FOR $3;
1380 patron_table ALIAS FOR $4;
1383 sql_where1 TEXT := '';
1384 sql_where2 TEXT := '';
1385 sql_where3 TEXT := '';
1386 sql_where4 TEXT := '';
1389 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1391 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1393 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
1394 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
1395 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
1396 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
1397 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
1398 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
1399 --RAISE INFO 'sql = %', sql;
1400 PERFORM migration_tools.exec( $1, sql );
1403 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1405 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1408 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1411 -- $barcode source barcode
1412 -- $prefix prefix to add to barcode, NULL = add no prefix
1413 -- $maxlen maximum length of barcode; default to 14 if left NULL
1414 -- $pad padding string to apply to left of source barcode before adding
1415 -- prefix and suffix; set to NULL or '' if no padding is desired
1416 -- $suffix suffix to add to barcode, NULL = add no suffix
1418 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1419 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1421 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1422 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1425 return unless defined $barcode;
1427 $prefix = '' unless defined $prefix;
1429 $pad = '0' unless defined $pad;
1430 $suffix = '' unless defined $suffix;
1432 # bail out if adding prefix and suffix would bring new barcode over max length
1433 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1435 my $new_barcode = $barcode;
1437 my $pad_length = $maxlen - length($prefix) - length($suffix);
1438 if (length($barcode) < $pad_length) {
1439 # assuming we always want padding on the left
1440 # also assuming that it is possible to have the pad string be longer than 1 character
1441 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1445 # bail out if adding prefix and suffix would bring new barcode over max length
1446 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1448 return "$prefix$new_barcode$suffix";
1449 $$ LANGUAGE PLPERLU STABLE;
1451 -- remove previous version of this function
1452 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1454 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1456 attempt_value ALIAS FOR $1;
1457 datatype ALIAS FOR $2;
1459 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1460 RETURN attempt_value;
1462 WHEN OTHERS THEN RETURN NULL;
1464 $$ LANGUAGE PLPGSQL STRICT STABLE;
1466 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1468 attempt_value ALIAS FOR $1;
1469 fail_value ALIAS FOR $2;
1473 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1480 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1485 $$ LANGUAGE PLPGSQL STRICT STABLE;
1487 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1489 attempt_value ALIAS FOR $1;
1490 fail_value ALIAS FOR $2;
1494 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1501 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1506 $$ LANGUAGE PLPGSQL STRICT STABLE;
1508 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1510 attempt_value ALIAS FOR $1;
1511 fail_value ALIAS FOR $2;
1515 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1522 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1527 $$ LANGUAGE PLPGSQL STRICT STABLE;
1529 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1531 attempt_value ALIAS FOR $1;
1532 fail_value ALIAS FOR $2;
1535 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1540 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1545 $$ LANGUAGE PLPGSQL STRICT STABLE;
1547 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1549 attempt_value ALIAS FOR $1;
1550 fail_value ALIAS FOR $2;
1551 output NUMERIC(8,2);
1554 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1561 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1566 $$ LANGUAGE PLPGSQL STRICT STABLE;
1568 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1570 attempt_value ALIAS FOR $1;
1571 fail_value ALIAS FOR $2;
1572 output NUMERIC(6,2);
1575 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1582 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1587 $$ LANGUAGE PLPGSQL STRICT STABLE;
1589 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1591 attempt_value ALIAS FOR $1;
1592 fail_value ALIAS FOR $2;
1593 output NUMERIC(8,2);
1595 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1596 RAISE EXCEPTION 'too many digits';
1599 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
1606 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1611 $$ LANGUAGE PLPGSQL STRICT STABLE;
1613 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1615 attempt_value ALIAS FOR $1;
1616 fail_value ALIAS FOR $2;
1617 output NUMERIC(6,2);
1619 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1620 RAISE EXCEPTION 'too many digits';
1623 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
1630 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1635 $$ LANGUAGE PLPGSQL STRICT STABLE;
1637 -- add_codabar_checkdigit
1638 -- $barcode source barcode
1640 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1641 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1642 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1643 -- input string does not meet those requirements, it is returned unchanged.
1645 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1646 my $barcode = shift;
1648 return $barcode if $barcode !~ /^\d{13,14}$/;
1649 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1650 my @digits = split //, $barcode;
1652 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1653 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1654 my $remainder = $total % 10;
1655 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1656 return $barcode . $checkdigit;
1657 $$ LANGUAGE PLPERLU STRICT STABLE;
1659 -- add_code39mod43_checkdigit
1660 -- $barcode source barcode
1662 -- If the source string is 13 or 14 characters long and contains only valid
1663 -- Code 39 mod 43 characters, adds or replaces the 14th
1664 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1665 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1666 -- input string does not meet those requirements, it is returned unchanged.
1668 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1669 my $barcode = shift;
1671 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1672 $barcode = substr($barcode, 0, 13); # ignore 14th character
1674 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1675 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1678 $total += $nums{$_} foreach split(//, $barcode);
1679 my $remainder = $total % 43;
1680 my $checkdigit = $valid_chars[$remainder];
1681 return $barcode . $checkdigit;
1682 $$ LANGUAGE PLPERLU STRICT STABLE;
1684 -- add_mod16_checkdigit
1685 -- $barcode source barcode
1687 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1689 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1690 my $barcode = shift;
1692 my @digits = split //, $barcode;
1694 foreach $digit (@digits) {
1695 if ($digit =~ /[0-9]/) { $total += $digit;
1696 } elsif ($digit eq '-') { $total += 10;
1697 } elsif ($digit eq '$') { $total += 11;
1698 } elsif ($digit eq ':') { $total += 12;
1699 } elsif ($digit eq '/') { $total += 13;
1700 } elsif ($digit eq '.') { $total += 14;
1701 } elsif ($digit eq '+') { $total += 15;
1702 } elsif ($digit eq 'A') { $total += 16;
1703 } elsif ($digit eq 'B') { $total += 17;
1704 } elsif ($digit eq 'C') { $total += 18;
1705 } elsif ($digit eq 'D') { $total += 19;
1706 } else { die "invalid digit <$digit>";
1709 my $remainder = $total % 16;
1710 my $difference = 16 - $remainder;
1712 if ($difference < 10) { $checkdigit = $difference;
1713 } elsif ($difference == 10) { $checkdigit = '-';
1714 } elsif ($difference == 11) { $checkdigit = '$';
1715 } elsif ($difference == 12) { $checkdigit = ':';
1716 } elsif ($difference == 13) { $checkdigit = '/';
1717 } elsif ($difference == 14) { $checkdigit = '.';
1718 } elsif ($difference == 15) { $checkdigit = '+';
1719 } else { die "error calculating checkdigit";
1722 return $barcode . $checkdigit;
1723 $$ LANGUAGE PLPERLU STRICT STABLE;
1725 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1728 areacode TEXT := $2;
1731 n_digits INTEGER := 0;
1734 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1735 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1736 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1737 IF n_digits = 7 AND areacode <> '' THEN
1738 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1739 output := (areacode || '-' || temp);
1746 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1748 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1749 my ($marcxml, $pos, $value) = @_;
1752 use MARC::File::XML;
1756 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1757 my $leader = $marc->leader();
1758 substr($leader, $pos, 1) = $value;
1759 $marc->leader($leader);
1760 $xml = $marc->as_xml_record;
1761 $xml =~ s/^<\?.+?\?>$//mo;
1763 $xml =~ s/>\s+</></sgo;
1766 $$ LANGUAGE PLPERLU STABLE;
1768 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1769 my ($marcxml, $pos, $value) = @_;
1772 use MARC::File::XML;
1776 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1777 my $f008 = $marc->field('008');
1780 my $field = $f008->data();
1781 substr($field, $pos, 1) = $value;
1782 $f008->update($field);
1783 $xml = $marc->as_xml_record;
1784 $xml =~ s/^<\?.+?\?>$//mo;
1786 $xml =~ s/>\s+</></sgo;
1790 $$ LANGUAGE PLPERLU STABLE;
1793 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1795 profile ALIAS FOR $1;
1797 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1799 $$ LANGUAGE PLPGSQL STRICT STABLE;
1802 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1804 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1806 $$ LANGUAGE PLPGSQL STRICT STABLE;
1809 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1811 my ($marcxml, $tags) = @_;
1814 use MARC::File::XML;
1819 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1820 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1822 my @incumbents = ();
1824 foreach my $field ( $marc->fields() ) {
1825 push @incumbents, $field->as_formatted();
1828 foreach $field ( $to_insert->fields() ) {
1829 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1830 $marc->insert_fields_ordered( ($field) );
1834 $xml = $marc->as_xml_record;
1835 $xml =~ s/^<\?.+?\?>$//mo;
1837 $xml =~ s/>\s+</></sgo;
1842 $$ LANGUAGE PLPERLU STABLE;
1844 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1848 -- First make sure the circ matrix is loaded and the circulations
1849 -- have been staged to the extent possible (but at the very least
1850 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1851 -- circ modifiers must also be in place.
1853 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1861 this_duration_rule INT;
1863 this_max_fine_rule INT;
1864 rcd config.rule_circ_duration%ROWTYPE;
1865 rrf config.rule_recurring_fine%ROWTYPE;
1866 rmf config.rule_max_fine%ROWTYPE;
1873 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1875 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1877 -- Fetch the correct rules for this circulation
1884 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1887 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1888 INTO circ_lib, target_copy, usr, is_renewal ;
1890 INTO this_duration_rule,
1894 recurring_fine_rule,
1896 FROM action.item_user_circ_test(
1902 SELECT INTO rcd * FROM config.rule_circ_duration
1903 WHERE id = this_duration_rule;
1904 SELECT INTO rrf * FROM config.rule_recurring_fine
1905 WHERE id = this_fine_rule;
1906 SELECT INTO rmf * FROM config.rule_max_fine
1907 WHERE id = this_max_fine_rule;
1909 -- Apply the rules to this circulation
1910 EXECUTE ('UPDATE ' || tablename || ' c
1912 duration_rule = rcd.name,
1913 recurring_fine_rule = rrf.name,
1914 max_fine_rule = rmf.name,
1915 duration = rcd.normal,
1916 recurring_fine = rrf.normal,
1919 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1922 renewal_remaining = rcd.max_renewals
1924 config.rule_circ_duration rcd,
1925 config.rule_recurring_fine rrf,
1926 config.rule_max_fine rmf,
1929 rcd.id = ' || this_duration_rule || ' AND
1930 rrf.id = ' || this_fine_rule || ' AND
1931 rmf.id = ' || this_max_fine_rule || ' AND
1932 ac.id = c.target_copy AND
1933 c.id = ' || circ || ';');
1935 -- Keep track of where we are in the process
1937 IF (n % 100 = 0) THEN
1938 RAISE INFO '%', n || ' of ' || n_circs
1939 || ' (' || (100*n/n_circs) || '%) circs updated.';
1947 $$ LANGUAGE plpgsql;
1949 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1953 -- First make sure the circ matrix is loaded and the circulations
1954 -- have been staged to the extent possible (but at the very least
1955 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1956 -- circ modifiers must also be in place.
1958 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1966 this_duration_rule INT;
1968 this_max_fine_rule INT;
1969 rcd config.rule_circ_duration%ROWTYPE;
1970 rrf config.rule_recurring_fine%ROWTYPE;
1971 rmf config.rule_max_fine%ROWTYPE;
1978 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1980 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1982 -- Fetch the correct rules for this circulation
1989 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1992 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1993 INTO circ_lib, target_copy, usr, is_renewal ;
1995 INTO this_duration_rule,
2001 FROM action.find_circ_matrix_matchpoint(
2007 SELECT INTO rcd * FROM config.rule_circ_duration
2008 WHERE id = this_duration_rule;
2009 SELECT INTO rrf * FROM config.rule_recurring_fine
2010 WHERE id = this_fine_rule;
2011 SELECT INTO rmf * FROM config.rule_max_fine
2012 WHERE id = this_max_fine_rule;
2014 -- Apply the rules to this circulation
2015 EXECUTE ('UPDATE ' || tablename || ' c
2017 duration_rule = rcd.name,
2018 recuring_fine_rule = rrf.name,
2019 max_fine_rule = rmf.name,
2020 duration = rcd.normal,
2021 recuring_fine = rrf.normal,
2024 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2027 renewal_remaining = rcd.max_renewals
2029 config.rule_circ_duration rcd,
2030 config.rule_recuring_fine rrf,
2031 config.rule_max_fine rmf,
2034 rcd.id = ' || this_duration_rule || ' AND
2035 rrf.id = ' || this_fine_rule || ' AND
2036 rmf.id = ' || this_max_fine_rule || ' AND
2037 ac.id = c.target_copy AND
2038 c.id = ' || circ || ';');
2040 -- Keep track of where we are in the process
2042 IF (n % 100 = 0) THEN
2043 RAISE INFO '%', n || ' of ' || n_circs
2044 || ' (' || (100*n/n_circs) || '%) circs updated.';
2052 $$ LANGUAGE plpgsql;
2054 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2058 -- First make sure the circ matrix is loaded and the circulations
2059 -- have been staged to the extent possible (but at the very least
2060 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2061 -- circ modifiers must also be in place.
2063 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2071 this_duration_rule INT;
2073 this_max_fine_rule INT;
2074 rcd config.rule_circ_duration%ROWTYPE;
2075 rrf config.rule_recurring_fine%ROWTYPE;
2076 rmf config.rule_max_fine%ROWTYPE;
2083 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2085 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2087 -- Fetch the correct rules for this circulation
2094 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2097 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2098 INTO circ_lib, target_copy, usr, is_renewal ;
2100 INTO this_duration_rule,
2103 (matchpoint).duration_rule,
2104 (matchpoint).recurring_fine_rule,
2105 (matchpoint).max_fine_rule
2106 FROM action.find_circ_matrix_matchpoint(
2112 SELECT INTO rcd * FROM config.rule_circ_duration
2113 WHERE id = this_duration_rule;
2114 SELECT INTO rrf * FROM config.rule_recurring_fine
2115 WHERE id = this_fine_rule;
2116 SELECT INTO rmf * FROM config.rule_max_fine
2117 WHERE id = this_max_fine_rule;
2119 -- Apply the rules to this circulation
2120 EXECUTE ('UPDATE ' || tablename || ' c
2122 duration_rule = rcd.name,
2123 recurring_fine_rule = rrf.name,
2124 max_fine_rule = rmf.name,
2125 duration = rcd.normal,
2126 recurring_fine = rrf.normal,
2129 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2132 renewal_remaining = rcd.max_renewals,
2133 grace_period = rrf.grace_period
2135 config.rule_circ_duration rcd,
2136 config.rule_recurring_fine rrf,
2137 config.rule_max_fine rmf,
2140 rcd.id = ' || this_duration_rule || ' AND
2141 rrf.id = ' || this_fine_rule || ' AND
2142 rmf.id = ' || this_max_fine_rule || ' AND
2143 ac.id = c.target_copy AND
2144 c.id = ' || circ || ';');
2146 -- Keep track of where we are in the process
2148 IF (n % 100 = 0) THEN
2149 RAISE INFO '%', n || ' of ' || n_circs
2150 || ' (' || (100*n/n_circs) || '%) circs updated.';
2158 $$ LANGUAGE plpgsql;
2160 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2161 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2162 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2165 charge_lost_on_zero BOOLEAN;
2168 default_price NUMERIC;
2169 working_price NUMERIC;
2173 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2174 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2176 SELECT INTO charge_lost_on_zero value
2177 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2179 SELECT INTO min_price value
2180 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2182 SELECT INTO max_price value
2183 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2185 SELECT INTO default_price value
2186 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2188 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2190 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2191 working_price := default_price;
2194 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2195 working_price := max_price;
2198 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2199 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2200 working_price := min_price;
2204 RETURN working_price;
2208 $$ LANGUAGE plpgsql;
2210 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2214 -- First make sure the circ matrix is loaded and the circulations
2215 -- have been staged to the extent possible (but at the very least
2216 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2217 -- circ modifiers must also be in place.
2219 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2227 this_duration_rule INT;
2229 this_max_fine_rule INT;
2230 rcd config.rule_circ_duration%ROWTYPE;
2231 rrf config.rule_recurring_fine%ROWTYPE;
2232 rmf config.rule_max_fine%ROWTYPE;
2238 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2240 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2242 -- Fetch the correct rules for this circulation
2249 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2252 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2253 INTO circ_lib, target_copy, usr, is_renewal ;
2255 INTO this_duration_rule,
2258 (matchpoint).duration_rule,
2259 (matchpoint).recurring_fine_rule,
2260 (matchpoint).max_fine_rule
2261 FROM action.find_circ_matrix_matchpoint(
2267 SELECT INTO rcd * FROM config.rule_circ_duration
2268 WHERE id = this_duration_rule;
2269 SELECT INTO rrf * FROM config.rule_recurring_fine
2270 WHERE id = this_fine_rule;
2271 SELECT INTO rmf * FROM config.rule_max_fine
2272 WHERE id = this_max_fine_rule;
2274 -- Apply the rules to this circulation
2275 EXECUTE ('UPDATE ' || tablename || ' c
2277 duration_rule = rcd.name,
2278 recurring_fine_rule = rrf.name,
2279 max_fine_rule = rmf.name,
2280 duration = rcd.normal,
2281 recurring_fine = rrf.normal,
2284 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2287 renewal_remaining = rcd.max_renewals,
2288 grace_period = rrf.grace_period
2290 config.rule_circ_duration rcd,
2291 config.rule_recurring_fine rrf,
2292 config.rule_max_fine rmf,
2295 rcd.id = ' || this_duration_rule || ' AND
2296 rrf.id = ' || this_fine_rule || ' AND
2297 rmf.id = ' || this_max_fine_rule || ' AND
2298 ac.id = c.target_copy AND
2299 c.id = ' || circ || ';');
2301 -- Keep track of where we are in the process
2303 IF (n % 100 = 0) THEN
2304 RAISE INFO '%', n || ' of ' || n_circs
2305 || ' (' || (100*n/n_circs) || '%) circs updated.';
2313 $$ LANGUAGE plpgsql;
2318 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2320 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2321 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2323 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2324 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2327 c TEXT := schemaname || '.asset_copy_legacy';
2328 sc TEXT := schemaname || '.asset_stat_cat';
2329 sce TEXT := schemaname || '.asset_stat_cat_entry';
2330 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2336 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2338 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2340 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2341 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2342 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2349 $$ LANGUAGE plpgsql;
2351 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2353 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2354 -- This will assign standing penalties as needed.
2362 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2364 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2366 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2376 $$ LANGUAGE plpgsql;
2379 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2382 INSERT INTO metabib.metarecord (fingerprint, master_record)
2383 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2384 FROM biblio.record_entry b
2386 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
2387 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2388 ORDER BY b.fingerprint, b.quality DESC;
2389 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2391 FROM biblio.record_entry r
2392 JOIN metabib.metarecord m USING (fingerprint)
2393 WHERE NOT r.deleted;
2396 $$ LANGUAGE plpgsql;
2399 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2402 INSERT INTO metabib.metarecord (fingerprint, master_record)
2403 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2404 FROM biblio.record_entry b
2406 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
2407 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2408 ORDER BY b.fingerprint, b.quality DESC;
2409 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2411 FROM biblio.record_entry r
2412 JOIN metabib.metarecord m USING (fingerprint)
2414 AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
2417 $$ LANGUAGE plpgsql;
2420 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2422 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2423 -- Then SELECT migration_tools.create_cards('m_foo');
2426 u TEXT := schemaname || '.actor_usr_legacy';
2427 c TEXT := schemaname || '.actor_card';
2431 EXECUTE ('DELETE FROM ' || c || ';');
2432 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2433 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2439 $$ LANGUAGE plpgsql;
2442 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2444 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2446 my ($marcxml, $shortname) = @_;
2449 use MARC::File::XML;
2454 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2456 foreach my $field ( $marc->field('856') ) {
2457 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2458 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2459 $field->add_subfields( '9' => $shortname );
2460 $field->update( ind2 => '0');
2464 $xml = $marc->as_xml_record;
2465 $xml =~ s/^<\?.+?\?>$//mo;
2467 $xml =~ s/>\s+</></sgo;
2472 $$ LANGUAGE PLPERLU STABLE;
2474 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2476 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2478 my ($marcxml, $shortname) = @_;
2481 use MARC::File::XML;
2486 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2488 foreach my $field ( $marc->field('856') ) {
2489 if ( ! $field->as_string('9') ) {
2490 $field->add_subfields( '9' => $shortname );
2494 $xml = $marc->as_xml_record;
2495 $xml =~ s/^<\?.+?\?>$//mo;
2497 $xml =~ s/>\s+</></sgo;
2502 $$ LANGUAGE PLPERLU STABLE;
2505 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2517 -- Bail out if asked to change the label to ##URI##
2518 IF new_label = '##URI##' THEN
2522 -- Gather information
2523 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2524 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2525 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2527 -- Bail out if the label already is ##URI##
2528 IF old_label = '##URI##' THEN
2532 -- Bail out if the call number label is already correct
2533 IF new_volume = old_volume THEN
2537 -- Check whether we already have a destination volume available
2538 SELECT id INTO new_volume FROM asset.call_number
2541 owning_lib = owner AND
2542 label = new_label AND
2545 -- Create destination volume if needed
2547 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2548 VALUES (1, 1, bib, owner, new_label, cn_class);
2549 SELECT id INTO new_volume FROM asset.call_number
2552 owning_lib = owner AND
2553 label = new_label AND
2557 -- Move copy to destination
2558 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2560 -- Delete source volume if it is now empty
2561 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2563 DELETE FROM asset.call_number WHERE id = old_volume;
2568 $$ LANGUAGE plpgsql;
2570 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2575 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2579 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2580 $zipdata{$zip} = [$city, $state, $county];
2583 if (defined $zipdata{$input}) {
2584 my ($city, $state, $county) = @{$zipdata{$input}};
2585 return [$city, $state, $county];
2586 } elsif (defined $zipdata{substr $input, 0, 5}) {
2587 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2588 return [$city, $state, $county];
2590 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2593 $$ LANGUAGE PLPERLU STABLE;
2595 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2602 errors_found BOOLEAN;
2604 parent_shortname TEXT;
2610 type_parent_depth INT;
2615 errors_found := FALSE;
2617 -- Checking actor.org_unit_type
2619 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2621 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2622 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2624 IF type_parent IS NOT NULL THEN
2626 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2628 IF type_depth - type_parent_depth <> 1 THEN
2629 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2630 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2631 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2632 ou_type_name, type_depth, parent_type, type_parent_depth;
2633 errors_found := TRUE;
2641 -- Checking actor.org_unit
2643 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2645 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2646 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
2647 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
2648 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2649 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2650 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
2651 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
2653 IF ou_parent IS NOT NULL THEN
2655 IF (org_unit_depth - parent_depth <> 1) OR (
2656 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2658 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2659 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2660 errors_found := TRUE;
2667 IF NOT errors_found THEN
2668 RAISE INFO 'No errors found.';
2675 $$ LANGUAGE plpgsql;
2678 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2682 DELETE FROM asset.opac_visible_copies;
2684 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2686 cp.id, cp.circ_lib, cn.record
2689 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2690 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2691 JOIN asset.copy_location cl ON (cp.location = cl.id)
2692 JOIN config.copy_status cs ON (cp.status = cs.id)
2693 JOIN biblio.record_entry b ON (cn.record = b.id)
2702 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2706 $$ LANGUAGE plpgsql;
2709 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2715 old_owning_lib INTEGER;
2721 -- Gather information
2722 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2723 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2724 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2726 -- Bail out if the new_owning_lib is not the ID of an org_unit
2727 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2729 '% is not a valid actor.org_unit ID; no change made.',
2734 -- Bail out discreetly if the owning_lib is already correct
2735 IF new_owning_lib = old_owning_lib THEN
2739 -- Check whether we already have a destination volume available
2740 SELECT id INTO new_volume FROM asset.call_number
2743 owning_lib = new_owning_lib AND
2744 label = old_label AND
2747 -- Create destination volume if needed
2749 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2750 VALUES (1, 1, bib, new_owning_lib, old_label);
2751 SELECT id INTO new_volume FROM asset.call_number
2754 owning_lib = new_owning_lib AND
2755 label = old_label AND
2759 -- Move copy to destination
2760 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2762 -- Delete source volume if it is now empty
2763 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2765 DELETE FROM asset.call_number WHERE id = old_volume;
2770 $$ LANGUAGE plpgsql;
2773 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2775 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2778 new_owning_lib INTEGER;
2782 -- Parse the new_owner as an org unit ID or shortname
2783 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2784 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2785 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2786 ELSIF new_owner ~ E'^[0-9]+$' THEN
2787 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2790 E'You don\'t need to put the actor.org_unit ID in quotes; '
2791 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2792 new_owning_lib := new_owner::INTEGER;
2793 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2797 '% is not a valid actor.org_unit shortname or ID; no change made.',
2804 $$ LANGUAGE plpgsql;
2806 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2809 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2812 MARC::Charset->assume_unicode(1);
2817 my $r = MARC::Record->new_from_xml( $xml );
2818 my $output_xml = $r->as_xml_record();
2826 $func$ LANGUAGE PLPERLU;
2827 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2829 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2831 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2832 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2833 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2834 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2835 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2836 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2837 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2838 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2839 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2840 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2841 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2842 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2843 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2844 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2845 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2846 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2847 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2848 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2849 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2850 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2851 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2852 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2853 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2854 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2855 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2856 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2858 $FUNC$ LANGUAGE PLPGSQL;
2860 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2862 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2863 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2864 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2865 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2866 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2867 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2868 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2870 -- import any new circ rules
2871 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2872 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2873 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2874 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2876 -- and permission groups
2877 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2880 $FUNC$ LANGUAGE PLPGSQL;
2883 CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$
2892 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2893 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2894 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2895 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2896 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2897 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2898 FOR name IN EXECUTE loopq LOOP
2899 EXECUTE existsq INTO ct USING name;
2901 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2902 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2903 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2904 EXECUTE copyst USING name;
2908 $FUNC$ LANGUAGE PLPGSQL;
2910 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2917 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2918 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
2919 FOR id IN EXECUTE loopq USING delimiter LOOP
2920 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2921 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2922 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2923 EXECUTE splitst USING id, delimiter;
2926 $FUNC$ LANGUAGE PLPGSQL;
2928 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2934 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2937 MARC::Charset->assume_unicode(1);
2939 my $target_xml = shift;
2940 my $source_xml = shift;
2946 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2950 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2955 my $source_id = $source->subfield('901', 'c');
2956 $source_id = $source->subfield('903', 'a') unless $source_id;
2957 my $target_id = $target->subfield('901', 'c');
2958 $target_id = $target->subfield('903', 'a') unless $target_id;
2960 my %existing_fields;
2961 foreach my $tag (@$tags) {
2962 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2963 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2964 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2966 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2970 my $xml = $target->as_xml_record;
2971 $xml =~ s/^<\?.+?\?>$//mo;
2973 $xml =~ s/>\s+</></sgo;
2977 $func$ LANGUAGE PLPERLU;
2978 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
2980 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2986 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2989 my $in_tags = shift;
2990 my $in_values = shift;
2992 # hack-and-slash parsing of array-passed-as-string;
2993 # this can go away once everybody is running Postgres 9.1+
2994 my $csv = Text::CSV->new({binary => 1});
2997 my $status = $csv->parse($in_tags);
2998 my $tags = [ $csv->fields() ];
2999 $in_values =~ s/^{//;
3000 $in_values =~ s/}$//;
3001 $status = $csv->parse($in_values);
3002 my $values = [ $csv->fields() ];
3004 my $marc = MARC::Record->new();
3006 $marc->leader('00000nam a22000007 4500');
3007 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3009 foreach my $i (0..$#$tags) {
3011 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3014 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3015 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3017 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3021 my $xml = $marc->as_xml_record;
3022 $xml =~ s/^<\?.+?\?>$//mo;
3024 $xml =~ s/>\s+</></sgo;
3028 $func$ LANGUAGE PLPERLU;
3029 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3030 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3031 The second argument is an array of text containing the values to plug into each field.
3032 If the value for a given field is NULL or the empty string, it is not inserted.
3035 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3037 my ($marcxml, $tag, $pos, $value) = @_;
3040 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3044 MARC::Charset->assume_unicode(1);
3046 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3047 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3048 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3049 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3053 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3055 foreach my $field ($marc->field($tag)) {
3056 $field->update("ind$pos" => $value);
3058 $xml = $marc->as_xml_record;
3059 $xml =~ s/^<\?.+?\?>$//mo;
3061 $xml =~ s/>\s+</></sgo;
3065 $func$ LANGUAGE PLPERLU;
3067 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3068 The first argument is a MARCXML string.
3069 The second argument is a MARC tag.
3070 The third argument is the indicator position, either 1 or 2.
3071 The fourth argument is the character to set the indicator value to.
3072 All occurences of the specified field will be changed.
3073 The function returns the revised MARCXML string.$$;
3075 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3080 first_name TEXT DEFAULT '',
3081 last_name TEXT DEFAULT ''
3082 ) RETURNS VOID AS $func$
3084 RAISE NOTICE '%', org ;
3085 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3086 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3087 FROM actor.org_unit aou, permission.grp_tree pgt
3088 WHERE aou.shortname = org
3089 AND pgt.name = perm_group;
3094 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3095 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3097 target_event_def ALIAS FOR $1;
3100 DROP TABLE IF EXISTS new_atevdefs;
3101 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3102 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3103 INSERT INTO action_trigger.event_definition (
3124 ,name || ' (clone of '||target_event_def||')'
3140 action_trigger.event_definition
3142 id = target_event_def
3144 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3145 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3146 INSERT INTO action_trigger.environment (
3152 currval('action_trigger.event_definition_id_seq')
3157 action_trigger.environment
3159 event_def = target_event_def
3161 INSERT INTO action_trigger.event_params (
3166 currval('action_trigger.event_definition_id_seq')
3170 action_trigger.event_params
3172 event_def = target_event_def
3175 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3177 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3179 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3180 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3182 target_event_def ALIAS FOR $1;
3184 new_interval ALIAS FOR $3;
3186 DROP TABLE IF EXISTS new_atevdefs;
3187 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3188 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3189 INSERT INTO action_trigger.event_definition (
3210 ,name || ' (clone of '||target_event_def||')'
3226 action_trigger.event_definition
3228 id = target_event_def
3230 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3231 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3232 INSERT INTO action_trigger.environment (
3238 currval('action_trigger.event_definition_id_seq')
3243 action_trigger.environment
3245 event_def = target_event_def
3247 INSERT INTO action_trigger.event_params (
3252 currval('action_trigger.event_definition_id_seq')
3256 action_trigger.event_params
3258 event_def = target_event_def
3261 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3263 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3265 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3266 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3269 target_event_defs ALIAS FOR $2;
3271 DROP TABLE IF EXISTS new_atevdefs;
3272 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3273 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3274 INSERT INTO action_trigger.event_definition (
3295 ,name || ' (clone of '||target_event_defs[i]||')'
3311 action_trigger.event_definition
3313 id = target_event_defs[i]
3315 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3316 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3317 INSERT INTO action_trigger.environment (
3323 currval('action_trigger.event_definition_id_seq')
3328 action_trigger.environment
3330 event_def = target_event_defs[i]
3332 INSERT INTO action_trigger.event_params (
3337 currval('action_trigger.event_definition_id_seq')
3341 action_trigger.event_params
3343 event_def = target_event_defs[i]
3346 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3348 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3350 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3352 action_trigger.event
3356 ,complete_time = NULL
3357 ,update_process = NULL
3359 ,template_output = NULL
3360 ,error_output = NULL
3361 ,async_output = NULL
3366 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3370 use MARC::File::XML;
3375 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3376 $field = $marc->leader();
3379 $$ LANGUAGE PLPERLU STABLE;
3381 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3382 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3385 use MARC::File::XML;
3390 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3391 $field = $marc->field($tag);
3393 return $field->as_string($subfield,$delimiter);
3394 $$ LANGUAGE PLPERLU STABLE;
3396 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3397 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3400 use MARC::File::XML;
3405 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3406 @fields = $marc->field($tag);
3409 foreach my $field (@fields) {
3410 push @texts, $field->as_string($subfield,$delimiter);
3413 $$ LANGUAGE PLPERLU STABLE;
3415 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3416 SELECT action.find_hold_matrix_matchpoint(
3417 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3418 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3419 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3420 (SELECT usr FROM action.hold_request WHERE id = $1),
3421 (SELECT requestor FROM action.hold_request WHERE id = $1)
3425 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3426 SELECT action.hold_request_permit_test(
3427 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3428 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3429 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3430 (SELECT usr FROM action.hold_request WHERE id = $1),
3431 (SELECT requestor FROM action.hold_request WHERE id = $1)
3435 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3436 SELECT action.find_circ_matrix_matchpoint(
3437 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3438 (SELECT target_copy FROM action.circulation WHERE id = $1),
3439 (SELECT usr FROM action.circulation WHERE id = $1),
3441 NULLIF(phone_renewal,false),
3442 NULLIF(desk_renewal,false),
3443 NULLIF(opac_renewal,false),
3445 ) FROM action.circulation WHERE id = $1
3450 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3455 RAISE EXCEPTION 'assertion';
3458 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3460 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3466 RAISE EXCEPTION '%', msg;
3469 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3471 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3474 fail_msg ALIAS FOR $2;
3475 success_msg ALIAS FOR $3;
3478 RAISE EXCEPTION '%', fail_msg;
3482 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3484 -- push bib sequence and return starting value for reserved range
3485 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3487 bib_count ALIAS FOR $1;
3490 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3492 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3497 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3499 -- set a new salted password
3501 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3503 usr_id ALIAS FOR $1;
3504 plain_passwd ALIAS FOR $2;
3509 SELECT actor.create_salt('main') INTO plain_salt;
3511 SELECT MD5(plain_passwd) INTO md5_passwd;
3513 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3518 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3521 -- convenience functions for handling copy_location maps
3523 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3525 table_schema ALIAS FOR $1;
3526 table_name ALIAS FOR $2;
3527 org_shortname ALIAS FOR $3;
3528 org_range ALIAS FOR $4;
3531 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3532 -- though we'll still use the passed org for the full path traversal when needed
3533 x_org_found BOOLEAN;
3538 EXECUTE 'SELECT EXISTS (
3540 FROM information_schema.columns
3541 WHERE table_schema = $1
3543 and column_name = ''desired_shelf''
3544 )' INTO proceed USING table_schema, table_name;
3546 RAISE EXCEPTION 'Missing column desired_shelf';
3549 EXECUTE 'SELECT EXISTS (
3551 FROM information_schema.columns
3552 WHERE table_schema = $1
3554 and column_name = ''x_org''
3555 )' INTO x_org_found USING table_schema, table_name;
3557 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3559 RAISE EXCEPTION 'Cannot find org by shortname';
3562 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3564 EXECUTE 'ALTER TABLE '
3565 || quote_ident(table_name)
3566 || ' DROP COLUMN IF EXISTS x_shelf';
3567 EXECUTE 'ALTER TABLE '
3568 || quote_ident(table_name)
3569 || ' ADD COLUMN x_shelf INTEGER';
3572 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3573 || ' SET x_shelf = id FROM asset_copy_location b'
3574 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3575 || ' AND b.owning_lib = x_org'
3576 || ' AND NOT b.deleted';
3577 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3578 || ' SET x_shelf = id FROM asset.copy_location b'
3579 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3580 || ' AND b.owning_lib = x_org'
3581 || ' AND x_shelf IS NULL'
3582 || ' AND NOT b.deleted';
3584 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3585 || ' SET x_shelf = id FROM asset_copy_location b'
3586 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3587 || ' AND b.owning_lib = $1'
3588 || ' AND NOT b.deleted'
3590 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3591 || ' SET x_shelf = id FROM asset_copy_location b'
3592 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3593 || ' AND b.owning_lib = $1'
3594 || ' AND x_shelf IS NULL'
3595 || ' AND NOT b.deleted'
3599 FOREACH o IN ARRAY org_list LOOP
3600 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3601 || ' SET x_shelf = id FROM asset.copy_location b'
3602 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3603 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3604 || ' AND NOT b.deleted'
3608 EXECUTE 'SELECT migration_tools.assert(
3609 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3610 ''Cannot find a desired location'',
3611 ''Found all desired locations''
3615 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3617 -- convenience functions for handling circmod maps
3619 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3621 table_schema ALIAS FOR $1;
3622 table_name ALIAS FOR $2;
3625 EXECUTE 'SELECT EXISTS (
3627 FROM information_schema.columns
3628 WHERE table_schema = $1
3630 and column_name = ''desired_circmod''
3631 )' INTO proceed USING table_schema, table_name;
3633 RAISE EXCEPTION 'Missing column desired_circmod';
3636 EXECUTE 'ALTER TABLE '
3637 || quote_ident(table_name)
3638 || ' DROP COLUMN IF EXISTS x_circmod';
3639 EXECUTE 'ALTER TABLE '
3640 || quote_ident(table_name)
3641 || ' ADD COLUMN x_circmod TEXT';
3643 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3644 || ' SET x_circmod = code FROM config.circ_modifier b'
3645 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3647 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3648 || ' SET x_circmod = code FROM config.circ_modifier b'
3649 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3650 || ' AND x_circmod IS NULL';
3652 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3653 || ' SET x_circmod = code FROM config.circ_modifier b'
3654 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3655 || ' AND x_circmod IS NULL';
3657 EXECUTE 'SELECT migration_tools.assert(
3658 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3659 ''Cannot find a desired circulation modifier'',
3660 ''Found all desired circulation modifiers''
3664 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3666 -- convenience functions for handling item status maps
3668 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3670 table_schema ALIAS FOR $1;
3671 table_name ALIAS FOR $2;
3674 EXECUTE 'SELECT EXISTS (
3676 FROM information_schema.columns
3677 WHERE table_schema = $1
3679 and column_name = ''desired_status''
3680 )' INTO proceed USING table_schema, table_name;
3682 RAISE EXCEPTION 'Missing column desired_status';
3685 EXECUTE 'ALTER TABLE '
3686 || quote_ident(table_name)
3687 || ' DROP COLUMN IF EXISTS x_status';
3688 EXECUTE 'ALTER TABLE '
3689 || quote_ident(table_name)
3690 || ' ADD COLUMN x_status INTEGER';
3692 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3693 || ' SET x_status = id FROM config.copy_status b'
3694 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3696 EXECUTE 'SELECT migration_tools.assert(
3697 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3698 ''Cannot find a desired copy status'',
3699 ''Found all desired copy statuses''
3703 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3705 -- convenience functions for handling org maps
3707 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3709 table_schema ALIAS FOR $1;
3710 table_name ALIAS FOR $2;
3713 EXECUTE 'SELECT EXISTS (
3715 FROM information_schema.columns
3716 WHERE table_schema = $1
3718 and column_name = ''desired_org''
3719 )' INTO proceed USING table_schema, table_name;
3721 RAISE EXCEPTION 'Missing column desired_org';
3724 EXECUTE 'ALTER TABLE '
3725 || quote_ident(table_name)
3726 || ' DROP COLUMN IF EXISTS x_org';
3727 EXECUTE 'ALTER TABLE '
3728 || quote_ident(table_name)
3729 || ' ADD COLUMN x_org INTEGER';
3731 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3732 || ' SET x_org = id FROM actor.org_unit b'
3733 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3735 EXECUTE 'SELECT migration_tools.assert(
3736 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3737 ''Cannot find a desired org unit'',
3738 ''Found all desired org units''
3742 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3744 -- convenience function for handling desired_not_migrate
3746 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3748 table_schema ALIAS FOR $1;
3749 table_name ALIAS FOR $2;
3752 EXECUTE 'SELECT EXISTS (
3754 FROM information_schema.columns
3755 WHERE table_schema = $1
3757 and column_name = ''desired_not_migrate''
3758 )' INTO proceed USING table_schema, table_name;
3760 RAISE EXCEPTION 'Missing column desired_not_migrate';
3763 EXECUTE 'ALTER TABLE '
3764 || quote_ident(table_name)
3765 || ' DROP COLUMN IF EXISTS x_migrate';
3766 EXECUTE 'ALTER TABLE '
3767 || quote_ident(table_name)
3768 || ' ADD COLUMN x_migrate BOOLEAN';
3770 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3771 || ' SET x_migrate = CASE'
3772 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3773 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3774 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3775 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3776 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3777 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3780 EXECUTE 'SELECT migration_tools.assert(
3781 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3782 ''Not all desired_not_migrate values understood'',
3783 ''All desired_not_migrate values understood''
3787 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3789 -- convenience function for handling desired_not_migrate
3791 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3793 table_schema ALIAS FOR $1;
3794 table_name ALIAS FOR $2;
3797 EXECUTE 'SELECT EXISTS (
3799 FROM information_schema.columns
3800 WHERE table_schema = $1
3802 and column_name = ''desired_barred_or_blocked''
3803 )' INTO proceed USING table_schema, table_name;
3805 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3808 EXECUTE 'ALTER TABLE '
3809 || quote_ident(table_name)
3810 || ' DROP COLUMN IF EXISTS x_barred';
3811 EXECUTE 'ALTER TABLE '
3812 || quote_ident(table_name)
3813 || ' ADD COLUMN x_barred BOOLEAN';
3815 EXECUTE 'ALTER TABLE '
3816 || quote_ident(table_name)
3817 || ' DROP COLUMN IF EXISTS x_blocked';
3818 EXECUTE 'ALTER TABLE '
3819 || quote_ident(table_name)
3820 || ' ADD COLUMN x_blocked BOOLEAN';
3822 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3823 || ' SET x_barred = CASE'
3824 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3825 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3826 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3827 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3830 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3831 || ' SET x_blocked = CASE'
3832 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3833 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3834 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3835 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3838 EXECUTE 'SELECT migration_tools.assert(
3839 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3840 ''Not all desired_barred_or_blocked values understood'',
3841 ''All desired_barred_or_blocked values understood''
3845 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3847 -- convenience function for handling desired_profile
3849 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3851 table_schema ALIAS FOR $1;
3852 table_name ALIAS FOR $2;
3855 EXECUTE 'SELECT EXISTS (
3857 FROM information_schema.columns
3858 WHERE table_schema = $1
3860 and column_name = ''desired_profile''
3861 )' INTO proceed USING table_schema, table_name;
3863 RAISE EXCEPTION 'Missing column desired_profile';
3866 EXECUTE 'ALTER TABLE '
3867 || quote_ident(table_name)
3868 || ' DROP COLUMN IF EXISTS x_profile';
3869 EXECUTE 'ALTER TABLE '
3870 || quote_ident(table_name)
3871 || ' ADD COLUMN x_profile INTEGER';
3873 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3874 || ' SET x_profile = b.id FROM permission.grp_tree b'
3875 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3877 EXECUTE 'SELECT migration_tools.assert(
3878 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3879 ''Cannot find a desired profile'',
3880 ''Found all desired profiles''
3884 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3886 -- convenience function for handling desired actor stat cats
3888 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3890 table_schema ALIAS FOR $1;
3891 table_name ALIAS FOR $2;
3892 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3893 org_shortname ALIAS FOR $4;
3901 SELECT 'desired_sc' || field_suffix INTO sc;
3902 SELECT 'desired_sce' || field_suffix INTO sce;
3904 EXECUTE 'SELECT EXISTS (
3906 FROM information_schema.columns
3907 WHERE table_schema = $1
3909 and column_name = $3
3910 )' INTO proceed USING table_schema, table_name, sc;
3912 RAISE EXCEPTION 'Missing column %', sc;
3914 EXECUTE 'SELECT EXISTS (
3916 FROM information_schema.columns
3917 WHERE table_schema = $1
3919 and column_name = $3
3920 )' INTO proceed USING table_schema, table_name, sce;
3922 RAISE EXCEPTION 'Missing column %', sce;
3925 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3927 RAISE EXCEPTION 'Cannot find org by shortname';
3929 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3931 -- caller responsible for their own truncates though we try to prevent duplicates
3932 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3937 ' || quote_ident(table_name) || '
3939 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3943 WHERE owner = ANY ($2)
3944 AND name = BTRIM('||sc||')
3949 WHERE owner = ANY ($2)
3950 AND name = BTRIM('||sc||')
3953 USING org, org_list;
3955 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3960 WHERE owner = ANY ($2)
3961 AND BTRIM('||sc||') = BTRIM(name))
3964 WHERE owner = ANY ($2)
3965 AND BTRIM('||sc||') = BTRIM(name))
3970 ' || quote_ident(table_name) || '
3972 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3973 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3976 FROM actor.stat_cat_entry
3980 WHERE owner = ANY ($2)
3981 AND BTRIM('||sc||') = BTRIM(name)
3982 ) AND value = BTRIM('||sce||')
3983 AND owner = ANY ($2)
3987 FROM actor_stat_cat_entry
3991 WHERE owner = ANY ($2)
3992 AND BTRIM('||sc||') = BTRIM(name)
3993 ) AND value = BTRIM('||sce||')
3994 AND owner = ANY ($2)
3997 USING org, org_list;
3999 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4001 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4003 table_schema ALIAS FOR $1;
4004 table_name ALIAS FOR $2;
4005 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4006 org_shortname ALIAS FOR $4;
4014 SELECT 'desired_sc' || field_suffix INTO sc;
4015 SELECT 'desired_sce' || field_suffix INTO sce;
4016 EXECUTE 'SELECT EXISTS (
4018 FROM information_schema.columns
4019 WHERE table_schema = $1
4021 and column_name = $3
4022 )' INTO proceed USING table_schema, table_name, sc;
4024 RAISE EXCEPTION 'Missing column %', sc;
4026 EXECUTE 'SELECT EXISTS (
4028 FROM information_schema.columns
4029 WHERE table_schema = $1
4031 and column_name = $3
4032 )' INTO proceed USING table_schema, table_name, sce;
4034 RAISE EXCEPTION 'Missing column %', sce;
4037 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4039 RAISE EXCEPTION 'Cannot find org by shortname';
4042 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4044 EXECUTE 'ALTER TABLE '
4045 || quote_ident(table_name)
4046 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4047 EXECUTE 'ALTER TABLE '
4048 || quote_ident(table_name)
4049 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4050 EXECUTE 'ALTER TABLE '
4051 || quote_ident(table_name)
4052 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4053 EXECUTE 'ALTER TABLE '
4054 || quote_ident(table_name)
4055 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4058 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4060 x_sc' || field_suffix || ' = id
4062 (SELECT id, name, owner FROM actor_stat_cat
4063 UNION SELECT id, name, owner FROM actor.stat_cat) u
4065 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4066 AND u.owner = ANY ($1);'
4069 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4071 x_sce' || field_suffix || ' = id
4073 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4074 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4076 u.stat_cat = x_sc' || field_suffix || '
4077 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4078 AND u.owner = ANY ($1);'
4081 EXECUTE 'SELECT migration_tools.assert(
4082 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4083 ''Cannot find a desired stat cat'',
4084 ''Found all desired stat cats''
4087 EXECUTE 'SELECT migration_tools.assert(
4088 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4089 ''Cannot find a desired stat cat entry'',
4090 ''Found all desired stat cat entries''
4094 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4096 -- convenience functions for adding shelving locations
4097 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4098 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4104 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4107 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4108 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4109 IF return_id IS NOT NULL THEN
4117 $$ LANGUAGE plpgsql;
4119 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4121 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4122 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4128 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4131 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4133 SELECT INTO return_id id FROM
4134 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4135 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4136 IF return_id IS NOT NULL THEN
4144 $$ LANGUAGE plpgsql;
4146 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
4147 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
4148 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
4156 use MARC::File::XML (BinaryEncoding => 'utf8');
4158 binmode(STDERR, ':bytes');
4159 binmode(STDOUT, ':utf8');
4160 binmode(STDERR, ':utf8');
4162 my $marc_xml = shift;
4163 my $new_9_to_set = shift;
4165 $marc_xml =~ s/(<leader>.........)./${1}a/;
4168 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4171 #elog("could not parse $bibid: $@\n");
4172 import MARC::File::XML (BinaryEncoding => 'utf8');
4176 my @uris = $marc_xml->field('856');
4177 return $marc_xml->as_xml_record() unless @uris;
4179 foreach my $field (@uris) {
4180 my $ind1 = $field->indicator('1');
4181 if (!defined $ind1) { next; }
4182 if ($ind1 ne '1' && $ind1 ne '4') { next; }
4183 my $ind2 = $field->indicator('2');
4184 if (!defined $ind2) { next; }
4185 if ($ind2 ne '0' && $ind2 ne '1') { next; }
4186 $field->add_subfields( '9' => $new_9_to_set );
4189 return $marc_xml->as_xml_record();
4193 -- yet another subfield 9 function, this one only adds the $9 and forces
4194 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
4195 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
4196 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
4204 use MARC::File::XML (BinaryEncoding => 'utf8');
4206 binmode(STDERR, ':bytes');
4207 binmode(STDOUT, ':utf8');
4208 binmode(STDERR, ':utf8');
4210 my $marc_xml = shift;
4211 my $new_9_to_set = shift;
4213 $marc_xml =~ s/(<leader>.........)./${1}a/;
4216 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4219 #elog("could not parse $bibid: $@\n");
4220 import MARC::File::XML (BinaryEncoding => 'utf8');
4224 my @uris = $marc_xml->field('856');
4225 return $marc_xml->as_xml_record() unless @uris;
4227 foreach my $field (@uris) {
4228 my $ind1 = $field->indicator('1');
4229 if (!defined $ind1) { next; }
4230 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
4231 my $ind2 = $field->indicator('2');
4232 if (!defined $ind2) { next; }
4233 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
4234 $field->add_subfields( '9' => $new_9_to_set );
4237 return $marc_xml->as_xml_record();
4241 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
4242 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
4243 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
4251 use MARC::File::XML (BinaryEncoding => 'utf8');
4253 binmode(STDERR, ':bytes');
4254 binmode(STDOUT, ':utf8');
4255 binmode(STDERR, ':utf8');
4257 my $marc_xml = shift;
4258 my $matching_u_text = shift;
4259 my $new_9_to_set = shift;
4261 $marc_xml =~ s/(<leader>.........)./${1}a/;
4264 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4267 #elog("could not parse $bibid: $@\n");
4268 import MARC::File::XML (BinaryEncoding => 'utf8');
4272 my @uris = $marc_xml->field('856');
4273 return unless @uris;
4275 foreach my $field (@uris) {
4276 my $sfu = $field->subfield('u');
4277 my $ind2 = $field->indicator('2');
4278 if (!defined $ind2) { next; }
4279 if ($ind2 ne '0') { next; }
4280 if (!defined $sfu) { next; }
4281 if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
4282 $field->add_subfields( '9' => $new_9_to_set );
4287 return $marc_xml->as_xml_record();
4291 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
4292 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
4301 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
4303 SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
4306 new_xml = '$_$' || new_xml || '$_$';
4308 IF new_xml != source_xml THEN
4309 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
4316 $BODY$ LANGUAGE plpgsql;
4319 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4320 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4328 use MARC::File::XML (BinaryEncoding => 'utf8');
4330 binmode(STDERR, ':bytes');
4331 binmode(STDOUT, ':utf8');
4332 binmode(STDERR, ':utf8');
4334 my $marc_xml = shift;
4337 $marc_xml =~ s/(<leader>.........)./${1}a/;
4340 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4343 #elog("could not parse $bibid: $@\n");
4344 import MARC::File::XML (BinaryEncoding => 'utf8');
4348 my @fields = $marc_xml->field($tag);
4349 return $marc_xml->as_xml_record() unless @fields;
4351 $marc_xml->delete_fields(@fields);
4353 return $marc_xml->as_xml_record();
4357 -- convenience function for linking to the item staging table
4359 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4361 table_schema ALIAS FOR $1;
4362 table_name ALIAS FOR $2;
4363 foreign_column_name ALIAS FOR $3;
4364 main_column_name ALIAS FOR $4;
4365 btrim_desired ALIAS FOR $5;
4368 EXECUTE 'SELECT EXISTS (
4370 FROM information_schema.columns
4371 WHERE table_schema = $1
4373 and column_name = $3
4374 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4376 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4379 EXECUTE 'SELECT EXISTS (
4381 FROM information_schema.columns
4382 WHERE table_schema = $1
4383 AND table_name = ''asset_copy_legacy''
4384 and column_name = $2
4385 )' INTO proceed USING table_schema, main_column_name;
4387 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4390 EXECUTE 'ALTER TABLE '
4391 || quote_ident(table_name)
4392 || ' DROP COLUMN IF EXISTS x_item';
4393 EXECUTE 'ALTER TABLE '
4394 || quote_ident(table_name)
4395 || ' ADD COLUMN x_item BIGINT';
4397 IF btrim_desired THEN
4398 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4399 || ' SET x_item = b.id FROM asset_copy_legacy b'
4400 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4401 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4403 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4404 || ' SET x_item = b.id FROM asset_copy_legacy b'
4405 || ' WHERE a.' || quote_ident(foreign_column_name)
4406 || ' = b.' || quote_ident(main_column_name);
4409 --EXECUTE 'SELECT migration_tools.assert(
4410 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4411 -- ''Cannot link every barcode'',
4412 -- ''Every barcode linked''
4416 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4418 -- convenience function for linking to the user staging table
4420 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4422 table_schema ALIAS FOR $1;
4423 table_name ALIAS FOR $2;
4424 foreign_column_name ALIAS FOR $3;
4425 main_column_name ALIAS FOR $4;
4426 btrim_desired ALIAS FOR $5;
4429 EXECUTE 'SELECT EXISTS (
4431 FROM information_schema.columns
4432 WHERE table_schema = $1
4434 and column_name = $3
4435 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4437 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4440 EXECUTE 'SELECT EXISTS (
4442 FROM information_schema.columns
4443 WHERE table_schema = $1
4444 AND table_name = ''actor_usr_legacy''
4445 and column_name = $2
4446 )' INTO proceed USING table_schema, main_column_name;
4448 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4451 EXECUTE 'ALTER TABLE '
4452 || quote_ident(table_name)
4453 || ' DROP COLUMN IF EXISTS x_user';
4454 EXECUTE 'ALTER TABLE '
4455 || quote_ident(table_name)
4456 || ' ADD COLUMN x_user INTEGER';
4458 IF btrim_desired THEN
4459 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4460 || ' SET x_user = b.id FROM actor_usr_legacy b'
4461 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4462 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4464 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4465 || ' SET x_user = b.id FROM actor_usr_legacy b'
4466 || ' WHERE a.' || quote_ident(foreign_column_name)
4467 || ' = b.' || quote_ident(main_column_name);
4470 --EXECUTE 'SELECT migration_tools.assert(
4471 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4472 -- ''Cannot link every barcode'',
4473 -- ''Every barcode linked''
4477 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4479 -- convenience function for linking two tables
4480 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4481 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4483 table_schema ALIAS FOR $1;
4484 table_a ALIAS FOR $2;
4485 column_a ALIAS FOR $3;
4486 table_b ALIAS FOR $4;
4487 column_b ALIAS FOR $5;
4488 column_x ALIAS FOR $6;
4489 btrim_desired ALIAS FOR $7;
4492 EXECUTE 'SELECT EXISTS (
4494 FROM information_schema.columns
4495 WHERE table_schema = $1
4497 and column_name = $3
4498 )' INTO proceed USING table_schema, table_a, column_a;
4500 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4503 EXECUTE 'SELECT EXISTS (
4505 FROM information_schema.columns
4506 WHERE table_schema = $1
4508 and column_name = $3
4509 )' INTO proceed USING table_schema, table_b, column_b;
4511 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4514 EXECUTE 'ALTER TABLE '
4515 || quote_ident(table_b)
4516 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4517 EXECUTE 'ALTER TABLE '
4518 || quote_ident(table_b)
4519 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4521 IF btrim_desired THEN
4522 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4523 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4524 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4525 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4527 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4528 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4529 || ' WHERE a.' || quote_ident(column_a)
4530 || ' = b.' || quote_ident(column_b);
4534 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4536 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4537 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4538 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4540 table_schema ALIAS FOR $1;
4541 table_a ALIAS FOR $2;
4542 column_a ALIAS FOR $3;
4543 table_b ALIAS FOR $4;
4544 column_b ALIAS FOR $5;
4545 column_w ALIAS FOR $6;
4546 column_x ALIAS FOR $7;
4547 btrim_desired ALIAS FOR $8;
4550 EXECUTE 'SELECT EXISTS (
4552 FROM information_schema.columns
4553 WHERE table_schema = $1
4555 and column_name = $3
4556 )' INTO proceed USING table_schema, table_a, column_a;
4558 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4561 EXECUTE 'SELECT EXISTS (
4563 FROM information_schema.columns
4564 WHERE table_schema = $1
4566 and column_name = $3
4567 )' INTO proceed USING table_schema, table_b, column_b;
4569 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4572 EXECUTE 'ALTER TABLE '
4573 || quote_ident(table_b)
4574 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4575 EXECUTE 'ALTER TABLE '
4576 || quote_ident(table_b)
4577 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4579 IF btrim_desired THEN
4580 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4581 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4582 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4583 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4585 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4586 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4587 || ' WHERE a.' || quote_ident(column_a)
4588 || ' = b.' || quote_ident(column_b);
4592 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4594 -- convenience function for linking two tables, but copying column w into column x instead of "id". Unlike handle_link2, this one won't drop the target column, and it also doesn't have a final boolean argument for btrim
4595 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4596 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4598 table_schema ALIAS FOR $1;
4599 table_a ALIAS FOR $2;
4600 column_a ALIAS FOR $3;
4601 table_b ALIAS FOR $4;
4602 column_b ALIAS FOR $5;
4603 column_w ALIAS FOR $6;
4604 column_x ALIAS FOR $7;
4607 EXECUTE 'SELECT EXISTS (
4609 FROM information_schema.columns
4610 WHERE table_schema = $1
4612 and column_name = $3
4613 )' INTO proceed USING table_schema, table_a, column_a;
4615 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4618 EXECUTE 'SELECT EXISTS (
4620 FROM information_schema.columns
4621 WHERE table_schema = $1
4623 and column_name = $3
4624 )' INTO proceed USING table_schema, table_b, column_b;
4626 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4629 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4630 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4631 || ' WHERE a.' || quote_ident(column_a)
4632 || ' = b.' || quote_ident(column_b);
4635 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4637 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4639 table_schema ALIAS FOR $1;
4640 table_a ALIAS FOR $2;
4641 column_a ALIAS FOR $3;
4642 table_b ALIAS FOR $4;
4643 column_b ALIAS FOR $5;
4644 column_w ALIAS FOR $6;
4645 column_x ALIAS FOR $7;
4648 EXECUTE 'SELECT EXISTS (
4650 FROM information_schema.columns
4651 WHERE table_schema = $1
4653 and column_name = $3
4654 )' INTO proceed USING table_schema, table_a, column_a;
4656 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4659 EXECUTE 'SELECT EXISTS (
4661 FROM information_schema.columns
4662 WHERE table_schema = $1
4664 and column_name = $3
4665 )' INTO proceed USING table_schema, table_b, column_b;
4667 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4670 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4671 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4672 || ' WHERE a.' || quote_ident(column_a)
4673 || ' = b.' || quote_ident(column_b)
4674 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4677 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4679 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4681 table_schema ALIAS FOR $1;
4682 table_a ALIAS FOR $2;
4683 column_a ALIAS FOR $3;
4684 table_b ALIAS FOR $4;
4685 column_b ALIAS FOR $5;
4686 column_w ALIAS FOR $6;
4687 column_x ALIAS FOR $7;
4690 EXECUTE 'SELECT EXISTS (
4692 FROM information_schema.columns
4693 WHERE table_schema = $1
4695 and column_name = $3
4696 )' INTO proceed USING table_schema, table_a, column_a;
4698 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4701 EXECUTE 'SELECT EXISTS (
4703 FROM information_schema.columns
4704 WHERE table_schema = $1
4706 and column_name = $3
4707 )' INTO proceed USING table_schema, table_b, column_b;
4709 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4712 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4713 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4714 || ' WHERE a.' || quote_ident(column_a)
4715 || ' = b.' || quote_ident(column_b)
4716 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4719 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4721 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4723 table_schema ALIAS FOR $1;
4724 table_a ALIAS FOR $2;
4725 column_a ALIAS FOR $3;
4726 table_b ALIAS FOR $4;
4727 column_b ALIAS FOR $5;
4728 column_w ALIAS FOR $6;
4729 column_x ALIAS FOR $7;
4732 EXECUTE 'SELECT EXISTS (
4734 FROM information_schema.columns
4735 WHERE table_schema = $1
4737 and column_name = $3
4738 )' INTO proceed USING table_schema, table_a, column_a;
4740 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4743 EXECUTE 'SELECT EXISTS (
4745 FROM information_schema.columns
4746 WHERE table_schema = $1
4748 and column_name = $3
4749 )' INTO proceed USING table_schema, table_b, column_b;
4751 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4754 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4755 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4756 || ' WHERE a.' || quote_ident(column_a)
4757 || ' = b.' || quote_ident(column_b)
4758 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4761 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4763 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4765 table_schema ALIAS FOR $1;
4766 table_a ALIAS FOR $2;
4767 column_a ALIAS FOR $3;
4768 table_b ALIAS FOR $4;
4769 column_b ALIAS FOR $5;
4770 column_w ALIAS FOR $6;
4771 column_x ALIAS FOR $7;
4774 EXECUTE 'SELECT EXISTS (
4776 FROM information_schema.columns
4777 WHERE table_schema = $1
4779 and column_name = $3
4780 )' INTO proceed USING table_schema, table_a, column_a;
4782 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4785 EXECUTE 'SELECT EXISTS (
4787 FROM information_schema.columns
4788 WHERE table_schema = $1
4790 and column_name = $3
4791 )' INTO proceed USING table_schema, table_b, column_b;
4793 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4796 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4797 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4798 || ' WHERE a.' || quote_ident(column_a)
4799 || ' = b.' || quote_ident(column_b)
4800 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4803 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4805 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4807 table_schema ALIAS FOR $1;
4808 table_a ALIAS FOR $2;
4809 column_a ALIAS FOR $3;
4810 table_b ALIAS FOR $4;
4811 column_b ALIAS FOR $5;
4812 column_w ALIAS FOR $6;
4813 column_x ALIAS FOR $7;
4816 EXECUTE 'SELECT EXISTS (
4818 FROM information_schema.columns
4819 WHERE table_schema = $1
4821 and column_name = $3
4822 )' INTO proceed USING table_schema, table_a, column_a;
4824 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4827 EXECUTE 'SELECT EXISTS (
4829 FROM information_schema.columns
4830 WHERE table_schema = $1
4832 and column_name = $3
4833 )' INTO proceed USING table_schema, table_b, column_b;
4835 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4838 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4839 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4840 || ' WHERE a.' || quote_ident(column_a)
4841 || ' = b.' || quote_ident(column_b)
4842 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4845 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4847 -- convenience function for handling desired asset stat cats
4849 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4851 table_schema ALIAS FOR $1;
4852 table_name ALIAS FOR $2;
4853 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4854 org_shortname ALIAS FOR $4;
4862 SELECT 'desired_sc' || field_suffix INTO sc;
4863 SELECT 'desired_sce' || field_suffix INTO sce;
4865 EXECUTE 'SELECT EXISTS (
4867 FROM information_schema.columns
4868 WHERE table_schema = $1
4870 and column_name = $3
4871 )' INTO proceed USING table_schema, table_name, sc;
4873 RAISE EXCEPTION 'Missing column %', sc;
4875 EXECUTE 'SELECT EXISTS (
4877 FROM information_schema.columns
4878 WHERE table_schema = $1
4880 and column_name = $3
4881 )' INTO proceed USING table_schema, table_name, sce;
4883 RAISE EXCEPTION 'Missing column %', sce;
4886 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4888 RAISE EXCEPTION 'Cannot find org by shortname';
4890 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4892 -- caller responsible for their own truncates though we try to prevent duplicates
4893 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4898 ' || quote_ident(table_name) || '
4900 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4904 WHERE owner = ANY ($2)
4905 AND name = BTRIM('||sc||')
4910 WHERE owner = ANY ($2)
4911 AND name = BTRIM('||sc||')
4914 USING org, org_list;
4916 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4921 WHERE owner = ANY ($2)
4922 AND BTRIM('||sc||') = BTRIM(name))
4925 WHERE owner = ANY ($2)
4926 AND BTRIM('||sc||') = BTRIM(name))
4931 ' || quote_ident(table_name) || '
4933 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4934 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4937 FROM asset.stat_cat_entry
4941 WHERE owner = ANY ($2)
4942 AND BTRIM('||sc||') = BTRIM(name)
4943 ) AND value = BTRIM('||sce||')
4944 AND owner = ANY ($2)
4948 FROM asset_stat_cat_entry
4952 WHERE owner = ANY ($2)
4953 AND BTRIM('||sc||') = BTRIM(name)
4954 ) AND value = BTRIM('||sce||')
4955 AND owner = ANY ($2)
4958 USING org, org_list;
4960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4962 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4964 table_schema ALIAS FOR $1;
4965 table_name ALIAS FOR $2;
4966 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4967 org_shortname ALIAS FOR $4;
4975 SELECT 'desired_sc' || field_suffix INTO sc;
4976 SELECT 'desired_sce' || field_suffix INTO sce;
4977 EXECUTE 'SELECT EXISTS (
4979 FROM information_schema.columns
4980 WHERE table_schema = $1
4982 and column_name = $3
4983 )' INTO proceed USING table_schema, table_name, sc;
4985 RAISE EXCEPTION 'Missing column %', sc;
4987 EXECUTE 'SELECT EXISTS (
4989 FROM information_schema.columns
4990 WHERE table_schema = $1
4992 and column_name = $3
4993 )' INTO proceed USING table_schema, table_name, sce;
4995 RAISE EXCEPTION 'Missing column %', sce;
4998 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5000 RAISE EXCEPTION 'Cannot find org by shortname';
5003 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5005 EXECUTE 'ALTER TABLE '
5006 || quote_ident(table_name)
5007 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5008 EXECUTE 'ALTER TABLE '
5009 || quote_ident(table_name)
5010 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5011 EXECUTE 'ALTER TABLE '
5012 || quote_ident(table_name)
5013 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5014 EXECUTE 'ALTER TABLE '
5015 || quote_ident(table_name)
5016 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5019 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5021 x_sc' || field_suffix || ' = id
5023 (SELECT id, name, owner FROM asset_stat_cat
5024 UNION SELECT id, name, owner FROM asset.stat_cat) u
5026 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5027 AND u.owner = ANY ($1);'
5030 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5032 x_sce' || field_suffix || ' = id
5034 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5035 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5037 u.stat_cat = x_sc' || field_suffix || '
5038 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5039 AND u.owner = ANY ($1);'
5042 EXECUTE 'SELECT migration_tools.assert(
5043 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5044 ''Cannot find a desired stat cat'',
5045 ''Found all desired stat cats''
5048 EXECUTE 'SELECT migration_tools.assert(
5049 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5050 ''Cannot find a desired stat cat entry'',
5051 ''Found all desired stat cat entries''
5055 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5057 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5058 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5065 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5067 AND table_schema = s_name
5068 AND (data_type='text' OR data_type='character varying')
5069 AND column_name like 'l_%'
5071 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5078 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5079 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5086 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5088 AND table_schema = s_name
5089 AND (data_type='text' OR data_type='character varying')
5091 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5098 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5099 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5106 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5108 AND table_schema = s_name
5109 AND (data_type='text' OR data_type='character varying')
5110 AND column_name like 'l_%'
5112 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5119 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5120 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5127 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5129 AND table_schema = s_name
5130 AND (data_type='text' OR data_type='character varying')
5132 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');