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 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
655 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
656 IF o::BIGINT < t THEN
663 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
665 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
667 migration_schema ALIAS FOR $1;
671 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
676 $$ LANGUAGE PLPGSQL STRICT STABLE;
678 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
680 migration_schema ALIAS FOR $1;
684 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
689 $$ LANGUAGE PLPGSQL STRICT STABLE;
691 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
693 migration_schema ALIAS FOR $1;
697 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
702 $$ LANGUAGE PLPGSQL STRICT STABLE;
704 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
706 migration_schema ALIAS FOR $1;
710 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
715 $$ LANGUAGE PLPGSQL STRICT STABLE;
717 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
719 migration_schema ALIAS FOR $1;
721 patron_table ALIAS FOR $2;
722 default_patron_profile ALIAS FOR $3;
725 sql_where1 TEXT := '';
726 sql_where2 TEXT := '';
727 sql_where3 TEXT := '';
730 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
732 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
734 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
735 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);
736 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);
737 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);
738 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,'') || ';';
739 --RAISE INFO 'sql = %', sql;
740 PERFORM migration_tools.exec( $1, sql );
742 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
744 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
746 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
749 $$ LANGUAGE PLPGSQL STRICT STABLE;
751 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
753 migration_schema ALIAS FOR $1;
755 item_table ALIAS FOR $2;
758 sql_where1 TEXT := '';
759 sql_where2 TEXT := '';
760 sql_where3 TEXT := '';
763 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
765 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
767 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 ';
768 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);
769 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);
770 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);
771 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,'') || ';';
772 --RAISE INFO 'sql = %', sql;
773 PERFORM migration_tools.exec( $1, sql );
776 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
778 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
781 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
783 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
785 migration_schema ALIAS FOR $1;
786 base_copy_location_map TEXT;
787 item_table ALIAS FOR $2;
790 sql_where1 TEXT := '';
791 sql_where2 TEXT := '';
792 sql_where3 TEXT := '';
795 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
797 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
799 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
800 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);
801 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);
802 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);
803 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,'') || ';';
804 --RAISE INFO 'sql = %', sql;
805 PERFORM migration_tools.exec( $1, sql );
808 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
810 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
813 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
815 -- 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
816 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
818 migration_schema ALIAS FOR $1;
820 circ_table ALIAS FOR $2;
821 item_table ALIAS FOR $3;
822 patron_table ALIAS FOR $4;
825 sql_where1 TEXT := '';
826 sql_where2 TEXT := '';
827 sql_where3 TEXT := '';
828 sql_where4 TEXT := '';
831 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
833 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
835 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 ';
836 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);
837 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);
838 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);
839 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);
840 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,'') || ';';
841 --RAISE INFO 'sql = %', sql;
842 PERFORM migration_tools.exec( $1, sql );
845 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
847 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
850 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
853 -- $barcode source barcode
854 -- $prefix prefix to add to barcode, NULL = add no prefix
855 -- $maxlen maximum length of barcode; default to 14 if left NULL
856 -- $pad padding string to apply to left of source barcode before adding
857 -- prefix and suffix; set to NULL or '' if no padding is desired
858 -- $suffix suffix to add to barcode, NULL = add no suffix
860 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
861 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
863 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
864 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
867 return unless defined $barcode;
869 $prefix = '' unless defined $prefix;
871 $pad = '0' unless defined $pad;
872 $suffix = '' unless defined $suffix;
874 # bail out if adding prefix and suffix would bring new barcode over max length
875 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
877 my $new_barcode = $barcode;
879 my $pad_length = $maxlen - length($prefix) - length($suffix);
880 if (length($barcode) < $pad_length) {
881 # assuming we always want padding on the left
882 # also assuming that it is possible to have the pad string be longer than 1 character
883 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
887 # bail out if adding prefix and suffix would bring new barcode over max length
888 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
890 return "$prefix$new_barcode$suffix";
891 $$ LANGUAGE PLPERLU STABLE;
893 -- remove previous version of this function
894 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
896 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
898 attempt_value ALIAS FOR $1;
899 datatype ALIAS FOR $2;
901 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
902 RETURN attempt_value;
904 WHEN OTHERS THEN RETURN NULL;
906 $$ LANGUAGE PLPGSQL STRICT STABLE;
908 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
910 attempt_value ALIAS FOR $1;
911 fail_value ALIAS FOR $2;
915 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
922 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
927 $$ LANGUAGE PLPGSQL STRICT STABLE;
929 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
931 attempt_value ALIAS FOR $1;
932 fail_value ALIAS FOR $2;
936 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
943 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
948 $$ LANGUAGE PLPGSQL STRICT STABLE;
950 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
952 attempt_value ALIAS FOR $1;
953 fail_value ALIAS FOR $2;
957 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
964 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
969 $$ LANGUAGE PLPGSQL STRICT STABLE;
971 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
973 attempt_value ALIAS FOR $1;
974 fail_value ALIAS FOR $2;
977 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
982 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
987 $$ LANGUAGE PLPGSQL STRICT STABLE;
989 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
991 attempt_value ALIAS FOR $1;
992 fail_value ALIAS FOR $2;
996 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1003 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1008 $$ LANGUAGE PLPGSQL STRICT STABLE;
1010 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1012 attempt_value ALIAS FOR $1;
1013 fail_value ALIAS FOR $2;
1014 output NUMERIC(6,2);
1017 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1024 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1029 $$ LANGUAGE PLPGSQL STRICT STABLE;
1031 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1033 attempt_value ALIAS FOR $1;
1034 fail_value ALIAS FOR $2;
1035 output NUMERIC(8,2);
1037 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1038 RAISE EXCEPTION 'too many digits';
1041 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;'
1048 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1053 $$ LANGUAGE PLPGSQL STRICT STABLE;
1055 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1057 attempt_value ALIAS FOR $1;
1058 fail_value ALIAS FOR $2;
1059 output NUMERIC(6,2);
1061 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1062 RAISE EXCEPTION 'too many digits';
1065 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;'
1072 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1077 $$ LANGUAGE PLPGSQL STRICT STABLE;
1079 -- add_codabar_checkdigit
1080 -- $barcode source barcode
1082 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1083 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1084 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1085 -- input string does not meet those requirements, it is returned unchanged.
1087 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1088 my $barcode = shift;
1090 return $barcode if $barcode !~ /^\d{13,14}$/;
1091 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1092 my @digits = split //, $barcode;
1094 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1095 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1096 my $remainder = $total % 10;
1097 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1098 return $barcode . $checkdigit;
1099 $$ LANGUAGE PLPERLU STRICT STABLE;
1101 -- add_code39mod43_checkdigit
1102 -- $barcode source barcode
1104 -- If the source string is 13 or 14 characters long and contains only valid
1105 -- Code 39 mod 43 characters, adds or replaces the 14th
1106 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1107 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1108 -- input string does not meet those requirements, it is returned unchanged.
1110 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1111 my $barcode = shift;
1113 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1114 $barcode = substr($barcode, 0, 13); # ignore 14th character
1116 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1117 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1120 $total += $nums{$_} foreach split(//, $barcode);
1121 my $remainder = $total % 43;
1122 my $checkdigit = $valid_chars[$remainder];
1123 return $barcode . $checkdigit;
1124 $$ LANGUAGE PLPERLU STRICT STABLE;
1126 -- add_mod16_checkdigit
1127 -- $barcode source barcode
1129 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1131 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1132 my $barcode = shift;
1134 my @digits = split //, $barcode;
1136 foreach $digit (@digits) {
1137 if ($digit =~ /[0-9]/) { $total += $digit;
1138 } elsif ($digit eq '-') { $total += 10;
1139 } elsif ($digit eq '$') { $total += 11;
1140 } elsif ($digit eq ':') { $total += 12;
1141 } elsif ($digit eq '/') { $total += 13;
1142 } elsif ($digit eq '.') { $total += 14;
1143 } elsif ($digit eq '+') { $total += 15;
1144 } elsif ($digit eq 'A') { $total += 16;
1145 } elsif ($digit eq 'B') { $total += 17;
1146 } elsif ($digit eq 'C') { $total += 18;
1147 } elsif ($digit eq 'D') { $total += 19;
1148 } else { die "invalid digit <$digit>";
1151 my $remainder = $total % 16;
1152 my $difference = 16 - $remainder;
1154 if ($difference < 10) { $checkdigit = $difference;
1155 } elsif ($difference == 10) { $checkdigit = '-';
1156 } elsif ($difference == 11) { $checkdigit = '$';
1157 } elsif ($difference == 12) { $checkdigit = ':';
1158 } elsif ($difference == 13) { $checkdigit = '/';
1159 } elsif ($difference == 14) { $checkdigit = '.';
1160 } elsif ($difference == 15) { $checkdigit = '+';
1161 } else { die "error calculating checkdigit";
1164 return $barcode . $checkdigit;
1165 $$ LANGUAGE PLPERLU STRICT STABLE;
1167 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1170 areacode TEXT := $2;
1173 n_digits INTEGER := 0;
1176 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1177 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1178 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1179 IF n_digits = 7 AND areacode <> '' THEN
1180 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1181 output := (areacode || '-' || temp);
1188 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1190 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1191 my ($marcxml, $pos, $value) = @_;
1194 use MARC::File::XML;
1198 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1199 my $leader = $marc->leader();
1200 substr($leader, $pos, 1) = $value;
1201 $marc->leader($leader);
1202 $xml = $marc->as_xml_record;
1203 $xml =~ s/^<\?.+?\?>$//mo;
1205 $xml =~ s/>\s+</></sgo;
1208 $$ LANGUAGE PLPERLU STABLE;
1210 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1211 my ($marcxml, $pos, $value) = @_;
1214 use MARC::File::XML;
1218 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1219 my $f008 = $marc->field('008');
1222 my $field = $f008->data();
1223 substr($field, $pos, 1) = $value;
1224 $f008->update($field);
1225 $xml = $marc->as_xml_record;
1226 $xml =~ s/^<\?.+?\?>$//mo;
1228 $xml =~ s/>\s+</></sgo;
1232 $$ LANGUAGE PLPERLU STABLE;
1235 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1237 profile ALIAS FOR $1;
1239 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1241 $$ LANGUAGE PLPGSQL STRICT STABLE;
1244 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1246 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1248 $$ LANGUAGE PLPGSQL STRICT STABLE;
1251 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1253 my ($marcxml, $tags) = @_;
1256 use MARC::File::XML;
1261 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1262 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1264 my @incumbents = ();
1266 foreach my $field ( $marc->fields() ) {
1267 push @incumbents, $field->as_formatted();
1270 foreach $field ( $to_insert->fields() ) {
1271 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1272 $marc->insert_fields_ordered( ($field) );
1276 $xml = $marc->as_xml_record;
1277 $xml =~ s/^<\?.+?\?>$//mo;
1279 $xml =~ s/>\s+</></sgo;
1284 $$ LANGUAGE PLPERLU STABLE;
1286 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1290 -- First make sure the circ matrix is loaded and the circulations
1291 -- have been staged to the extent possible (but at the very least
1292 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1293 -- circ modifiers must also be in place.
1295 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1303 this_duration_rule INT;
1305 this_max_fine_rule INT;
1306 rcd config.rule_circ_duration%ROWTYPE;
1307 rrf config.rule_recurring_fine%ROWTYPE;
1308 rmf config.rule_max_fine%ROWTYPE;
1315 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1317 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1319 -- Fetch the correct rules for this circulation
1326 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1329 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1330 INTO circ_lib, target_copy, usr, is_renewal ;
1332 INTO this_duration_rule,
1336 recurring_fine_rule,
1338 FROM action.item_user_circ_test(
1344 SELECT INTO rcd * FROM config.rule_circ_duration
1345 WHERE id = this_duration_rule;
1346 SELECT INTO rrf * FROM config.rule_recurring_fine
1347 WHERE id = this_fine_rule;
1348 SELECT INTO rmf * FROM config.rule_max_fine
1349 WHERE id = this_max_fine_rule;
1351 -- Apply the rules to this circulation
1352 EXECUTE ('UPDATE ' || tablename || ' c
1354 duration_rule = rcd.name,
1355 recurring_fine_rule = rrf.name,
1356 max_fine_rule = rmf.name,
1357 duration = rcd.normal,
1358 recurring_fine = rrf.normal,
1361 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1364 renewal_remaining = rcd.max_renewals
1366 config.rule_circ_duration rcd,
1367 config.rule_recurring_fine rrf,
1368 config.rule_max_fine rmf,
1371 rcd.id = ' || this_duration_rule || ' AND
1372 rrf.id = ' || this_fine_rule || ' AND
1373 rmf.id = ' || this_max_fine_rule || ' AND
1374 ac.id = c.target_copy AND
1375 c.id = ' || circ || ';');
1377 -- Keep track of where we are in the process
1379 IF (n % 100 = 0) THEN
1380 RAISE INFO '%', n || ' of ' || n_circs
1381 || ' (' || (100*n/n_circs) || '%) circs updated.';
1389 $$ LANGUAGE plpgsql;
1391 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1395 -- First make sure the circ matrix is loaded and the circulations
1396 -- have been staged to the extent possible (but at the very least
1397 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1398 -- circ modifiers must also be in place.
1400 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1408 this_duration_rule INT;
1410 this_max_fine_rule INT;
1411 rcd config.rule_circ_duration%ROWTYPE;
1412 rrf config.rule_recurring_fine%ROWTYPE;
1413 rmf config.rule_max_fine%ROWTYPE;
1420 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1422 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1424 -- Fetch the correct rules for this circulation
1431 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1434 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1435 INTO circ_lib, target_copy, usr, is_renewal ;
1437 INTO this_duration_rule,
1443 FROM action.find_circ_matrix_matchpoint(
1449 SELECT INTO rcd * FROM config.rule_circ_duration
1450 WHERE id = this_duration_rule;
1451 SELECT INTO rrf * FROM config.rule_recurring_fine
1452 WHERE id = this_fine_rule;
1453 SELECT INTO rmf * FROM config.rule_max_fine
1454 WHERE id = this_max_fine_rule;
1456 -- Apply the rules to this circulation
1457 EXECUTE ('UPDATE ' || tablename || ' c
1459 duration_rule = rcd.name,
1460 recuring_fine_rule = rrf.name,
1461 max_fine_rule = rmf.name,
1462 duration = rcd.normal,
1463 recuring_fine = rrf.normal,
1466 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1469 renewal_remaining = rcd.max_renewals
1471 config.rule_circ_duration rcd,
1472 config.rule_recuring_fine rrf,
1473 config.rule_max_fine rmf,
1476 rcd.id = ' || this_duration_rule || ' AND
1477 rrf.id = ' || this_fine_rule || ' AND
1478 rmf.id = ' || this_max_fine_rule || ' AND
1479 ac.id = c.target_copy AND
1480 c.id = ' || circ || ';');
1482 -- Keep track of where we are in the process
1484 IF (n % 100 = 0) THEN
1485 RAISE INFO '%', n || ' of ' || n_circs
1486 || ' (' || (100*n/n_circs) || '%) circs updated.';
1494 $$ LANGUAGE plpgsql;
1496 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1500 -- First make sure the circ matrix is loaded and the circulations
1501 -- have been staged to the extent possible (but at the very least
1502 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1503 -- circ modifiers must also be in place.
1505 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1513 this_duration_rule INT;
1515 this_max_fine_rule INT;
1516 rcd config.rule_circ_duration%ROWTYPE;
1517 rrf config.rule_recurring_fine%ROWTYPE;
1518 rmf config.rule_max_fine%ROWTYPE;
1525 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1527 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1529 -- Fetch the correct rules for this circulation
1536 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1539 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1540 INTO circ_lib, target_copy, usr, is_renewal ;
1542 INTO this_duration_rule,
1545 (matchpoint).duration_rule,
1546 (matchpoint).recurring_fine_rule,
1547 (matchpoint).max_fine_rule
1548 FROM action.find_circ_matrix_matchpoint(
1554 SELECT INTO rcd * FROM config.rule_circ_duration
1555 WHERE id = this_duration_rule;
1556 SELECT INTO rrf * FROM config.rule_recurring_fine
1557 WHERE id = this_fine_rule;
1558 SELECT INTO rmf * FROM config.rule_max_fine
1559 WHERE id = this_max_fine_rule;
1561 -- Apply the rules to this circulation
1562 EXECUTE ('UPDATE ' || tablename || ' c
1564 duration_rule = rcd.name,
1565 recurring_fine_rule = rrf.name,
1566 max_fine_rule = rmf.name,
1567 duration = rcd.normal,
1568 recurring_fine = rrf.normal,
1571 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1574 renewal_remaining = rcd.max_renewals,
1575 grace_period = rrf.grace_period
1577 config.rule_circ_duration rcd,
1578 config.rule_recurring_fine rrf,
1579 config.rule_max_fine rmf,
1582 rcd.id = ' || this_duration_rule || ' AND
1583 rrf.id = ' || this_fine_rule || ' AND
1584 rmf.id = ' || this_max_fine_rule || ' AND
1585 ac.id = c.target_copy AND
1586 c.id = ' || circ || ';');
1588 -- Keep track of where we are in the process
1590 IF (n % 100 = 0) THEN
1591 RAISE INFO '%', n || ' of ' || n_circs
1592 || ' (' || (100*n/n_circs) || '%) circs updated.';
1600 $$ LANGUAGE plpgsql;
1602 -- TODO: make another version of the procedure below that can work with specified copy staging tables
1603 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
1604 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
1607 charge_lost_on_zero BOOLEAN;
1610 default_price NUMERIC;
1611 working_price NUMERIC;
1615 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
1616 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
1618 SELECT INTO charge_lost_on_zero value
1619 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
1621 SELECT INTO min_price value
1622 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
1624 SELECT INTO max_price value
1625 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
1627 SELECT INTO default_price value
1628 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
1630 SELECT INTO working_price price FROM asset.copy WHERE id = item;
1632 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
1633 working_price := default_price;
1636 IF (max_price IS NOT NULL AND working_price > max_price) THEN
1637 working_price := max_price;
1640 IF (min_price IS NOT NULL AND working_price < min_price) THEN
1641 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
1642 working_price := min_price;
1646 RETURN working_price;
1650 $$ LANGUAGE plpgsql;
1652 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1656 -- First make sure the circ matrix is loaded and the circulations
1657 -- have been staged to the extent possible (but at the very least
1658 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1659 -- circ modifiers must also be in place.
1661 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1669 this_duration_rule INT;
1671 this_max_fine_rule INT;
1672 rcd config.rule_circ_duration%ROWTYPE;
1673 rrf config.rule_recurring_fine%ROWTYPE;
1674 rmf config.rule_max_fine%ROWTYPE;
1680 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1682 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1684 -- Fetch the correct rules for this circulation
1691 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1694 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1695 INTO circ_lib, target_copy, usr, is_renewal ;
1697 INTO this_duration_rule,
1700 (matchpoint).duration_rule,
1701 (matchpoint).recurring_fine_rule,
1702 (matchpoint).max_fine_rule
1703 FROM action.find_circ_matrix_matchpoint(
1709 SELECT INTO rcd * FROM config.rule_circ_duration
1710 WHERE id = this_duration_rule;
1711 SELECT INTO rrf * FROM config.rule_recurring_fine
1712 WHERE id = this_fine_rule;
1713 SELECT INTO rmf * FROM config.rule_max_fine
1714 WHERE id = this_max_fine_rule;
1716 -- Apply the rules to this circulation
1717 EXECUTE ('UPDATE ' || tablename || ' c
1719 duration_rule = rcd.name,
1720 recurring_fine_rule = rrf.name,
1721 max_fine_rule = rmf.name,
1722 duration = rcd.normal,
1723 recurring_fine = rrf.normal,
1726 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
1729 renewal_remaining = rcd.max_renewals,
1730 grace_period = rrf.grace_period
1732 config.rule_circ_duration rcd,
1733 config.rule_recurring_fine rrf,
1734 config.rule_max_fine rmf,
1737 rcd.id = ' || this_duration_rule || ' AND
1738 rrf.id = ' || this_fine_rule || ' AND
1739 rmf.id = ' || this_max_fine_rule || ' AND
1740 ac.id = c.target_copy AND
1741 c.id = ' || circ || ';');
1743 -- Keep track of where we are in the process
1745 IF (n % 100 = 0) THEN
1746 RAISE INFO '%', n || ' of ' || n_circs
1747 || ' (' || (100*n/n_circs) || '%) circs updated.';
1755 $$ LANGUAGE plpgsql;
1760 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1762 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1763 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1765 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1766 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1769 c TEXT := schemaname || '.asset_copy_legacy';
1770 sc TEXT := schemaname || '.asset_stat_cat';
1771 sce TEXT := schemaname || '.asset_stat_cat_entry';
1772 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1778 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1780 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1782 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1783 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1784 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1791 $$ LANGUAGE plpgsql;
1793 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1795 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1796 -- This will assign standing penalties as needed.
1804 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1806 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1808 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1818 $$ LANGUAGE plpgsql;
1821 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1824 INSERT INTO metabib.metarecord (fingerprint, master_record)
1825 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1826 FROM biblio.record_entry b
1828 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)
1829 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1830 ORDER BY b.fingerprint, b.quality DESC;
1831 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1833 FROM biblio.record_entry r
1834 JOIN metabib.metarecord m USING (fingerprint)
1835 WHERE NOT r.deleted;
1838 $$ LANGUAGE plpgsql;
1841 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1844 INSERT INTO metabib.metarecord (fingerprint, master_record)
1845 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1846 FROM biblio.record_entry b
1848 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)
1849 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1850 ORDER BY b.fingerprint, b.quality DESC;
1851 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1853 FROM biblio.record_entry r
1854 JOIN metabib.metarecord m USING (fingerprint)
1856 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);
1859 $$ LANGUAGE plpgsql;
1862 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1864 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1865 -- Then SELECT migration_tools.create_cards('m_foo');
1868 u TEXT := schemaname || '.actor_usr_legacy';
1869 c TEXT := schemaname || '.actor_card';
1873 EXECUTE ('DELETE FROM ' || c || ';');
1874 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1875 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1881 $$ LANGUAGE plpgsql;
1884 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1886 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1888 my ($marcxml, $shortname) = @_;
1891 use MARC::File::XML;
1896 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1898 foreach my $field ( $marc->field('856') ) {
1899 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1900 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1901 $field->add_subfields( '9' => $shortname );
1902 $field->update( ind2 => '0');
1906 $xml = $marc->as_xml_record;
1907 $xml =~ s/^<\?.+?\?>$//mo;
1909 $xml =~ s/>\s+</></sgo;
1914 $$ LANGUAGE PLPERLU STABLE;
1916 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1918 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1920 my ($marcxml, $shortname) = @_;
1923 use MARC::File::XML;
1928 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1930 foreach my $field ( $marc->field('856') ) {
1931 if ( ! $field->as_string('9') ) {
1932 $field->add_subfields( '9' => $shortname );
1936 $xml = $marc->as_xml_record;
1937 $xml =~ s/^<\?.+?\?>$//mo;
1939 $xml =~ s/>\s+</></sgo;
1944 $$ LANGUAGE PLPERLU STABLE;
1947 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1959 -- Bail out if asked to change the label to ##URI##
1960 IF new_label = '##URI##' THEN
1964 -- Gather information
1965 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1966 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1967 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1969 -- Bail out if the label already is ##URI##
1970 IF old_label = '##URI##' THEN
1974 -- Bail out if the call number label is already correct
1975 IF new_volume = old_volume THEN
1979 -- Check whether we already have a destination volume available
1980 SELECT id INTO new_volume FROM asset.call_number
1983 owning_lib = owner AND
1984 label = new_label AND
1987 -- Create destination volume if needed
1989 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1990 VALUES (1, 1, bib, owner, new_label, cn_class);
1991 SELECT id INTO new_volume FROM asset.call_number
1994 owning_lib = owner AND
1995 label = new_label AND
1999 -- Move copy to destination
2000 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2002 -- Delete source volume if it is now empty
2003 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2005 DELETE FROM asset.call_number WHERE id = old_volume;
2010 $$ LANGUAGE plpgsql;
2012 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2017 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2021 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2022 $zipdata{$zip} = [$city, $state, $county];
2025 if (defined $zipdata{$input}) {
2026 my ($city, $state, $county) = @{$zipdata{$input}};
2027 return [$city, $state, $county];
2028 } elsif (defined $zipdata{substr $input, 0, 5}) {
2029 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2030 return [$city, $state, $county];
2032 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2035 $$ LANGUAGE PLPERLU STABLE;
2037 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2044 errors_found BOOLEAN;
2046 parent_shortname TEXT;
2052 type_parent_depth INT;
2057 errors_found := FALSE;
2059 -- Checking actor.org_unit_type
2061 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2063 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2064 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2066 IF type_parent IS NOT NULL THEN
2068 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2070 IF type_depth - type_parent_depth <> 1 THEN
2071 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2072 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2073 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2074 ou_type_name, type_depth, parent_type, type_parent_depth;
2075 errors_found := TRUE;
2083 -- Checking actor.org_unit
2085 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2087 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2088 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;
2089 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;
2090 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2091 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2092 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;
2093 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;
2095 IF ou_parent IS NOT NULL THEN
2097 IF (org_unit_depth - parent_depth <> 1) OR (
2098 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2100 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2101 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2102 errors_found := TRUE;
2109 IF NOT errors_found THEN
2110 RAISE INFO 'No errors found.';
2117 $$ LANGUAGE plpgsql;
2120 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2124 DELETE FROM asset.opac_visible_copies;
2126 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2128 cp.id, cp.circ_lib, cn.record
2131 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2132 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2133 JOIN asset.copy_location cl ON (cp.location = cl.id)
2134 JOIN config.copy_status cs ON (cp.status = cs.id)
2135 JOIN biblio.record_entry b ON (cn.record = b.id)
2144 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2148 $$ LANGUAGE plpgsql;
2151 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2157 old_owning_lib INTEGER;
2163 -- Gather information
2164 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2165 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2166 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2168 -- Bail out if the new_owning_lib is not the ID of an org_unit
2169 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2171 '% is not a valid actor.org_unit ID; no change made.',
2176 -- Bail out discreetly if the owning_lib is already correct
2177 IF new_owning_lib = old_owning_lib THEN
2181 -- Check whether we already have a destination volume available
2182 SELECT id INTO new_volume FROM asset.call_number
2185 owning_lib = new_owning_lib AND
2186 label = old_label AND
2189 -- Create destination volume if needed
2191 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2192 VALUES (1, 1, bib, new_owning_lib, old_label);
2193 SELECT id INTO new_volume FROM asset.call_number
2196 owning_lib = new_owning_lib AND
2197 label = old_label AND
2201 -- Move copy to destination
2202 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2204 -- Delete source volume if it is now empty
2205 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2207 DELETE FROM asset.call_number WHERE id = old_volume;
2212 $$ LANGUAGE plpgsql;
2215 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2217 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2220 new_owning_lib INTEGER;
2224 -- Parse the new_owner as an org unit ID or shortname
2225 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2226 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2227 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2228 ELSIF new_owner ~ E'^[0-9]+$' THEN
2229 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2232 E'You don\'t need to put the actor.org_unit ID in quotes; '
2233 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2234 new_owning_lib := new_owner::INTEGER;
2235 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2239 '% is not a valid actor.org_unit shortname or ID; no change made.',
2246 $$ LANGUAGE plpgsql;
2248 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2251 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2254 MARC::Charset->assume_unicode(1);
2259 my $r = MARC::Record->new_from_xml( $xml );
2260 my $output_xml = $r->as_xml_record();
2268 $func$ LANGUAGE PLPERLU;
2269 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2271 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2273 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2274 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2275 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2276 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2277 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2278 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2279 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2280 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2281 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2282 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2283 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2284 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2285 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2286 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2287 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2288 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2289 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2290 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2291 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2292 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2293 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2294 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2295 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2296 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2297 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2298 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2300 $FUNC$ LANGUAGE PLPGSQL;
2302 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2304 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2305 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2306 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2307 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2308 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2309 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2310 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2312 -- import any new circ rules
2313 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2314 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2315 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2316 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2318 -- and permission groups
2319 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2322 $FUNC$ LANGUAGE PLPGSQL;
2325 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$
2334 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2335 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2336 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2337 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2338 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2339 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2340 FOR name IN EXECUTE loopq LOOP
2341 EXECUTE existsq INTO ct USING name;
2343 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2344 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2345 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2346 EXECUTE copyst USING name;
2350 $FUNC$ LANGUAGE PLPGSQL;
2352 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2358 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2361 MARC::Charset->assume_unicode(1);
2363 my $target_xml = shift;
2364 my $source_xml = shift;
2370 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2374 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2379 my $source_id = $source->subfield('901', 'c');
2380 $source_id = $source->subfield('903', 'a') unless $source_id;
2381 my $target_id = $target->subfield('901', 'c');
2382 $target_id = $target->subfield('903', 'a') unless $target_id;
2384 my %existing_fields;
2385 foreach my $tag (@$tags) {
2386 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2387 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2388 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2390 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2394 my $xml = $target->as_xml_record;
2395 $xml =~ s/^<\?.+?\?>$//mo;
2397 $xml =~ s/>\s+</></sgo;
2401 $func$ LANGUAGE PLPERLU;
2402 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.';
2404 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2410 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2413 my $in_tags = shift;
2414 my $in_values = shift;
2416 # hack-and-slash parsing of array-passed-as-string;
2417 # this can go away once everybody is running Postgres 9.1+
2418 my $csv = Text::CSV->new({binary => 1});
2421 my $status = $csv->parse($in_tags);
2422 my $tags = [ $csv->fields() ];
2423 $in_values =~ s/^{//;
2424 $in_values =~ s/}$//;
2425 $status = $csv->parse($in_values);
2426 my $values = [ $csv->fields() ];
2428 my $marc = MARC::Record->new();
2430 $marc->leader('00000nam a22000007 4500');
2431 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2433 foreach my $i (0..$#$tags) {
2435 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2438 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2439 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2441 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2445 my $xml = $marc->as_xml_record;
2446 $xml =~ s/^<\?.+?\?>$//mo;
2448 $xml =~ s/>\s+</></sgo;
2452 $func$ LANGUAGE PLPERLU;
2453 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2454 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2455 The second argument is an array of text containing the values to plug into each field.
2456 If the value for a given field is NULL or the empty string, it is not inserted.
2459 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2461 my ($marcxml, $tag, $pos, $value) = @_;
2464 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2468 MARC::Charset->assume_unicode(1);
2470 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2471 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2472 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2473 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2477 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2479 foreach my $field ($marc->field($tag)) {
2480 $field->update("ind$pos" => $value);
2482 $xml = $marc->as_xml_record;
2483 $xml =~ s/^<\?.+?\?>$//mo;
2485 $xml =~ s/>\s+</></sgo;
2489 $func$ LANGUAGE PLPERLU;
2491 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2492 The first argument is a MARCXML string.
2493 The second argument is a MARC tag.
2494 The third argument is the indicator position, either 1 or 2.
2495 The fourth argument is the character to set the indicator value to.
2496 All occurences of the specified field will be changed.
2497 The function returns the revised MARCXML string.$$;
2499 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2504 first_name TEXT DEFAULT '',
2505 last_name TEXT DEFAULT ''
2506 ) RETURNS VOID AS $func$
2508 RAISE NOTICE '%', org ;
2509 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2510 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2511 FROM actor.org_unit aou, permission.grp_tree pgt
2512 WHERE aou.shortname = org
2513 AND pgt.name = perm_group;
2518 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2519 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2521 target_event_def ALIAS FOR $1;
2524 DROP TABLE IF EXISTS new_atevdefs;
2525 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2526 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2527 INSERT INTO action_trigger.event_definition (
2548 ,name || ' (clone of '||target_event_def||')'
2564 action_trigger.event_definition
2566 id = target_event_def
2568 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2569 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2570 INSERT INTO action_trigger.environment (
2576 currval('action_trigger.event_definition_id_seq')
2581 action_trigger.environment
2583 event_def = target_event_def
2585 INSERT INTO action_trigger.event_params (
2590 currval('action_trigger.event_definition_id_seq')
2594 action_trigger.event_params
2596 event_def = target_event_def
2599 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);
2601 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2603 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2604 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2606 target_event_def ALIAS FOR $1;
2608 new_interval ALIAS FOR $3;
2610 DROP TABLE IF EXISTS new_atevdefs;
2611 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2612 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2613 INSERT INTO action_trigger.event_definition (
2634 ,name || ' (clone of '||target_event_def||')'
2650 action_trigger.event_definition
2652 id = target_event_def
2654 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2655 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2656 INSERT INTO action_trigger.environment (
2662 currval('action_trigger.event_definition_id_seq')
2667 action_trigger.environment
2669 event_def = target_event_def
2671 INSERT INTO action_trigger.event_params (
2676 currval('action_trigger.event_definition_id_seq')
2680 action_trigger.event_params
2682 event_def = target_event_def
2685 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);
2687 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2689 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
2690 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
2693 target_event_defs ALIAS FOR $2;
2695 DROP TABLE IF EXISTS new_atevdefs;
2696 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2697 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
2698 INSERT INTO action_trigger.event_definition (
2719 ,name || ' (clone of '||target_event_defs[i]||')'
2735 action_trigger.event_definition
2737 id = target_event_defs[i]
2739 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2740 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2741 INSERT INTO action_trigger.environment (
2747 currval('action_trigger.event_definition_id_seq')
2752 action_trigger.environment
2754 event_def = target_event_defs[i]
2756 INSERT INTO action_trigger.event_params (
2761 currval('action_trigger.event_definition_id_seq')
2765 action_trigger.event_params
2767 event_def = target_event_defs[i]
2770 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2772 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2774 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
2776 action_trigger.event
2780 ,complete_time = NULL
2781 ,update_process = NULL
2783 ,template_output = NULL
2784 ,error_output = NULL
2785 ,async_output = NULL
2790 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2794 use MARC::File::XML;
2799 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2800 $field = $marc->leader();
2803 $$ LANGUAGE PLPERLU STABLE;
2805 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2806 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2809 use MARC::File::XML;
2814 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2815 $field = $marc->field($tag);
2817 return $field->as_string($subfield,$delimiter);
2818 $$ LANGUAGE PLPERLU STABLE;
2820 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2821 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2824 use MARC::File::XML;
2829 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2830 @fields = $marc->field($tag);
2833 foreach my $field (@fields) {
2834 push @texts, $field->as_string($subfield,$delimiter);
2837 $$ LANGUAGE PLPERLU STABLE;
2839 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2840 SELECT action.find_hold_matrix_matchpoint(
2841 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2842 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2843 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2844 (SELECT usr FROM action.hold_request WHERE id = $1),
2845 (SELECT requestor FROM action.hold_request WHERE id = $1)
2849 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2850 SELECT action.hold_request_permit_test(
2851 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2852 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2853 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2854 (SELECT usr FROM action.hold_request WHERE id = $1),
2855 (SELECT requestor FROM action.hold_request WHERE id = $1)
2859 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2860 SELECT action.find_circ_matrix_matchpoint(
2861 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2862 (SELECT target_copy FROM action.circulation WHERE id = $1),
2863 (SELECT usr FROM action.circulation WHERE id = $1),
2865 NULLIF(phone_renewal,false),
2866 NULLIF(desk_renewal,false),
2867 NULLIF(opac_renewal,false),
2869 ) FROM action.circulation WHERE id = $1
2874 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2879 RAISE EXCEPTION 'assertion';
2882 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2884 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2890 RAISE EXCEPTION '%', msg;
2893 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2895 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2898 fail_msg ALIAS FOR $2;
2899 success_msg ALIAS FOR $3;
2902 RAISE EXCEPTION '%', fail_msg;
2906 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2908 -- push bib sequence and return starting value for reserved range
2909 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2911 bib_count ALIAS FOR $1;
2914 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2916 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2921 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2923 -- set a new salted password
2925 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2927 usr_id ALIAS FOR $1;
2928 plain_passwd ALIAS FOR $2;
2933 SELECT actor.create_salt('main') INTO plain_salt;
2935 SELECT MD5(plain_passwd) INTO md5_passwd;
2937 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2942 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2945 -- convenience functions for handling copy_location maps
2947 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2949 table_schema ALIAS FOR $1;
2950 table_name ALIAS FOR $2;
2951 org_shortname ALIAS FOR $3;
2952 org_range ALIAS FOR $4;
2958 EXECUTE 'SELECT EXISTS (
2960 FROM information_schema.columns
2961 WHERE table_schema = $1
2963 and column_name = ''desired_shelf''
2964 )' INTO proceed USING table_schema, table_name;
2966 RAISE EXCEPTION 'Missing column desired_shelf';
2969 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2971 RAISE EXCEPTION 'Cannot find org by shortname';
2974 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2976 EXECUTE 'ALTER TABLE '
2977 || quote_ident(table_name)
2978 || ' DROP COLUMN IF EXISTS x_shelf';
2979 EXECUTE 'ALTER TABLE '
2980 || quote_ident(table_name)
2981 || ' ADD COLUMN x_shelf INTEGER';
2983 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2984 || ' SET x_shelf = id FROM asset_copy_location b'
2985 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2986 || ' AND b.owning_lib = $1'
2987 || ' AND NOT b.deleted'
2990 FOREACH o IN ARRAY org_list LOOP
2991 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2992 || ' SET x_shelf = id FROM asset.copy_location b'
2993 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2994 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2995 || ' AND NOT b.deleted'
2999 EXECUTE 'SELECT migration_tools.assert(
3000 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3001 ''Cannot find a desired location'',
3002 ''Found all desired locations''
3006 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3008 -- convenience functions for handling circmod maps
3010 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3012 table_schema ALIAS FOR $1;
3013 table_name ALIAS FOR $2;
3016 EXECUTE 'SELECT EXISTS (
3018 FROM information_schema.columns
3019 WHERE table_schema = $1
3021 and column_name = ''desired_circmod''
3022 )' INTO proceed USING table_schema, table_name;
3024 RAISE EXCEPTION 'Missing column desired_circmod';
3027 EXECUTE 'ALTER TABLE '
3028 || quote_ident(table_name)
3029 || ' DROP COLUMN IF EXISTS x_circmod';
3030 EXECUTE 'ALTER TABLE '
3031 || quote_ident(table_name)
3032 || ' ADD COLUMN x_circmod TEXT';
3034 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3035 || ' SET x_circmod = code FROM config.circ_modifier b'
3036 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3038 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3039 || ' SET x_circmod = code FROM config.circ_modifier b'
3040 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3041 || ' AND x_circmod IS NULL';
3043 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3044 || ' SET x_circmod = code FROM config.circ_modifier b'
3045 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3046 || ' AND x_circmod IS NULL';
3048 EXECUTE 'SELECT migration_tools.assert(
3049 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3050 ''Cannot find a desired circulation modifier'',
3051 ''Found all desired circulation modifiers''
3055 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3057 -- convenience functions for handling item status maps
3059 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3061 table_schema ALIAS FOR $1;
3062 table_name ALIAS FOR $2;
3065 EXECUTE 'SELECT EXISTS (
3067 FROM information_schema.columns
3068 WHERE table_schema = $1
3070 and column_name = ''desired_status''
3071 )' INTO proceed USING table_schema, table_name;
3073 RAISE EXCEPTION 'Missing column desired_status';
3076 EXECUTE 'ALTER TABLE '
3077 || quote_ident(table_name)
3078 || ' DROP COLUMN IF EXISTS x_status';
3079 EXECUTE 'ALTER TABLE '
3080 || quote_ident(table_name)
3081 || ' ADD COLUMN x_status INTEGER';
3083 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3084 || ' SET x_status = id FROM config.copy_status b'
3085 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3087 EXECUTE 'SELECT migration_tools.assert(
3088 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3089 ''Cannot find a desired copy status'',
3090 ''Found all desired copy statuses''
3094 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3096 -- convenience functions for handling org maps
3098 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3100 table_schema ALIAS FOR $1;
3101 table_name ALIAS FOR $2;
3104 EXECUTE 'SELECT EXISTS (
3106 FROM information_schema.columns
3107 WHERE table_schema = $1
3109 and column_name = ''desired_org''
3110 )' INTO proceed USING table_schema, table_name;
3112 RAISE EXCEPTION 'Missing column desired_org';
3115 EXECUTE 'ALTER TABLE '
3116 || quote_ident(table_name)
3117 || ' DROP COLUMN IF EXISTS x_org';
3118 EXECUTE 'ALTER TABLE '
3119 || quote_ident(table_name)
3120 || ' ADD COLUMN x_org INTEGER';
3122 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3123 || ' SET x_org = id FROM actor.org_unit b'
3124 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3126 EXECUTE 'SELECT migration_tools.assert(
3127 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3128 ''Cannot find a desired org unit'',
3129 ''Found all desired org units''
3133 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3135 -- convenience function for handling desired_not_migrate
3137 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3139 table_schema ALIAS FOR $1;
3140 table_name ALIAS FOR $2;
3143 EXECUTE 'SELECT EXISTS (
3145 FROM information_schema.columns
3146 WHERE table_schema = $1
3148 and column_name = ''desired_not_migrate''
3149 )' INTO proceed USING table_schema, table_name;
3151 RAISE EXCEPTION 'Missing column desired_not_migrate';
3154 EXECUTE 'ALTER TABLE '
3155 || quote_ident(table_name)
3156 || ' DROP COLUMN IF EXISTS x_migrate';
3157 EXECUTE 'ALTER TABLE '
3158 || quote_ident(table_name)
3159 || ' ADD COLUMN x_migrate BOOLEAN';
3161 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3162 || ' SET x_migrate = CASE'
3163 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3164 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3165 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3166 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3167 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3168 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3171 EXECUTE 'SELECT migration_tools.assert(
3172 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3173 ''Not all desired_not_migrate values understood'',
3174 ''All desired_not_migrate values understood''
3178 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3180 -- convenience function for handling desired_not_migrate
3182 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3184 table_schema ALIAS FOR $1;
3185 table_name ALIAS FOR $2;
3188 EXECUTE 'SELECT EXISTS (
3190 FROM information_schema.columns
3191 WHERE table_schema = $1
3193 and column_name = ''desired_barred_or_blocked''
3194 )' INTO proceed USING table_schema, table_name;
3196 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3199 EXECUTE 'ALTER TABLE '
3200 || quote_ident(table_name)
3201 || ' DROP COLUMN IF EXISTS x_barred';
3202 EXECUTE 'ALTER TABLE '
3203 || quote_ident(table_name)
3204 || ' ADD COLUMN x_barred BOOLEAN';
3206 EXECUTE 'ALTER TABLE '
3207 || quote_ident(table_name)
3208 || ' DROP COLUMN IF EXISTS x_blocked';
3209 EXECUTE 'ALTER TABLE '
3210 || quote_ident(table_name)
3211 || ' ADD COLUMN x_blocked BOOLEAN';
3213 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3214 || ' SET x_barred = CASE'
3215 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3216 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3217 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3218 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3221 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3222 || ' SET x_blocked = CASE'
3223 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3224 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3225 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3226 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3229 EXECUTE 'SELECT migration_tools.assert(
3230 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3231 ''Not all desired_barred_or_blocked values understood'',
3232 ''All desired_barred_or_blocked values understood''
3236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3238 -- convenience function for handling desired_profile
3240 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3242 table_schema ALIAS FOR $1;
3243 table_name ALIAS FOR $2;
3246 EXECUTE 'SELECT EXISTS (
3248 FROM information_schema.columns
3249 WHERE table_schema = $1
3251 and column_name = ''desired_profile''
3252 )' INTO proceed USING table_schema, table_name;
3254 RAISE EXCEPTION 'Missing column desired_profile';
3257 EXECUTE 'ALTER TABLE '
3258 || quote_ident(table_name)
3259 || ' DROP COLUMN IF EXISTS x_profile';
3260 EXECUTE 'ALTER TABLE '
3261 || quote_ident(table_name)
3262 || ' ADD COLUMN x_profile INTEGER';
3264 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3265 || ' SET x_profile = b.id FROM permission.grp_tree b'
3266 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3268 EXECUTE 'SELECT migration_tools.assert(
3269 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3270 ''Cannot find a desired profile'',
3271 ''Found all desired profiles''
3275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3277 -- convenience function for handling desired actor stat cats
3279 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3281 table_schema ALIAS FOR $1;
3282 table_name ALIAS FOR $2;
3283 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3284 org_shortname ALIAS FOR $4;
3292 SELECT 'desired_sc' || field_suffix INTO sc;
3293 SELECT 'desired_sce' || field_suffix INTO sce;
3295 EXECUTE 'SELECT EXISTS (
3297 FROM information_schema.columns
3298 WHERE table_schema = $1
3300 and column_name = $3
3301 )' INTO proceed USING table_schema, table_name, sc;
3303 RAISE EXCEPTION 'Missing column %', sc;
3305 EXECUTE 'SELECT EXISTS (
3307 FROM information_schema.columns
3308 WHERE table_schema = $1
3310 and column_name = $3
3311 )' INTO proceed USING table_schema, table_name, sce;
3313 RAISE EXCEPTION 'Missing column %', sce;
3316 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3318 RAISE EXCEPTION 'Cannot find org by shortname';
3320 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3322 -- caller responsible for their own truncates though we try to prevent duplicates
3323 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3328 ' || quote_ident(table_name) || '
3330 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3334 WHERE owner = ANY ($2)
3335 AND name = BTRIM('||sc||')
3340 WHERE owner = ANY ($2)
3341 AND name = BTRIM('||sc||')
3344 USING org, org_list;
3346 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3351 WHERE owner = ANY ($2)
3352 AND BTRIM('||sc||') = BTRIM(name))
3355 WHERE owner = ANY ($2)
3356 AND BTRIM('||sc||') = BTRIM(name))
3361 ' || quote_ident(table_name) || '
3363 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3364 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3367 FROM actor.stat_cat_entry
3371 WHERE owner = ANY ($2)
3372 AND BTRIM('||sc||') = BTRIM(name)
3373 ) AND value = BTRIM('||sce||')
3374 AND owner = ANY ($2)
3378 FROM actor_stat_cat_entry
3382 WHERE owner = ANY ($2)
3383 AND BTRIM('||sc||') = BTRIM(name)
3384 ) AND value = BTRIM('||sce||')
3385 AND owner = ANY ($2)
3388 USING org, org_list;
3390 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3392 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3394 table_schema ALIAS FOR $1;
3395 table_name ALIAS FOR $2;
3396 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3397 org_shortname ALIAS FOR $4;
3405 SELECT 'desired_sc' || field_suffix INTO sc;
3406 SELECT 'desired_sce' || field_suffix INTO sce;
3407 EXECUTE 'SELECT EXISTS (
3409 FROM information_schema.columns
3410 WHERE table_schema = $1
3412 and column_name = $3
3413 )' INTO proceed USING table_schema, table_name, sc;
3415 RAISE EXCEPTION 'Missing column %', sc;
3417 EXECUTE 'SELECT EXISTS (
3419 FROM information_schema.columns
3420 WHERE table_schema = $1
3422 and column_name = $3
3423 )' INTO proceed USING table_schema, table_name, sce;
3425 RAISE EXCEPTION 'Missing column %', sce;
3428 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3430 RAISE EXCEPTION 'Cannot find org by shortname';
3433 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3435 EXECUTE 'ALTER TABLE '
3436 || quote_ident(table_name)
3437 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3438 EXECUTE 'ALTER TABLE '
3439 || quote_ident(table_name)
3440 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3441 EXECUTE 'ALTER TABLE '
3442 || quote_ident(table_name)
3443 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3444 EXECUTE 'ALTER TABLE '
3445 || quote_ident(table_name)
3446 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3449 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3451 x_sc' || field_suffix || ' = id
3453 (SELECT id, name, owner FROM actor_stat_cat
3454 UNION SELECT id, name, owner FROM actor.stat_cat) u
3456 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3457 AND u.owner = ANY ($1);'
3460 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3462 x_sce' || field_suffix || ' = id
3464 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3465 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3467 u.stat_cat = x_sc' || field_suffix || '
3468 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3469 AND u.owner = ANY ($1);'
3472 EXECUTE 'SELECT migration_tools.assert(
3473 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3474 ''Cannot find a desired stat cat'',
3475 ''Found all desired stat cats''
3478 EXECUTE 'SELECT migration_tools.assert(
3479 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3480 ''Cannot find a desired stat cat entry'',
3481 ''Found all desired stat cat entries''
3485 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3487 -- convenience functions for adding shelving locations
3488 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3489 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3495 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3498 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3499 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3500 IF return_id IS NOT NULL THEN
3508 $$ LANGUAGE plpgsql;
3510 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3512 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3513 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3519 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3522 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3524 SELECT INTO return_id id FROM
3525 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3526 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3527 IF return_id IS NOT NULL THEN
3535 $$ LANGUAGE plpgsql;
3537 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
3538 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
3539 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
3547 use MARC::File::XML (BinaryEncoding => 'utf8');
3549 binmode(STDERR, ':bytes');
3550 binmode(STDOUT, ':utf8');
3551 binmode(STDERR, ':utf8');
3553 my $marc_xml = shift;
3554 my $new_9_to_set = shift;
3556 $marc_xml =~ s/(<leader>.........)./${1}a/;
3559 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3562 #elog("could not parse $bibid: $@\n");
3563 import MARC::File::XML (BinaryEncoding => 'utf8');
3567 my @uris = $marc_xml->field('856');
3568 return $marc_xml->as_xml_record() unless @uris;
3570 foreach my $field (@uris) {
3571 my $ind1 = $field->indicator('1');
3572 if (!defined $ind1) { next; }
3573 if ($ind1 ne '1' && $ind1 ne '4') { next; }
3574 my $ind2 = $field->indicator('2');
3575 if (!defined $ind2) { next; }
3576 if ($ind2 ne '0' && $ind2 ne '1') { next; }
3577 $field->add_subfields( '9' => $new_9_to_set );
3580 return $marc_xml->as_xml_record();
3584 -- yet another subfield 9 function, this one only adds the $9 and forces
3585 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
3586 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
3587 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
3595 use MARC::File::XML (BinaryEncoding => 'utf8');
3597 binmode(STDERR, ':bytes');
3598 binmode(STDOUT, ':utf8');
3599 binmode(STDERR, ':utf8');
3601 my $marc_xml = shift;
3602 my $new_9_to_set = shift;
3604 $marc_xml =~ s/(<leader>.........)./${1}a/;
3607 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3610 #elog("could not parse $bibid: $@\n");
3611 import MARC::File::XML (BinaryEncoding => 'utf8');
3615 my @uris = $marc_xml->field('856');
3616 return $marc_xml->as_xml_record() unless @uris;
3618 foreach my $field (@uris) {
3619 my $ind1 = $field->indicator('1');
3620 if (!defined $ind1) { next; }
3621 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
3622 my $ind2 = $field->indicator('2');
3623 if (!defined $ind2) { next; }
3624 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
3625 $field->add_subfields( '9' => $new_9_to_set );
3628 return $marc_xml->as_xml_record();
3632 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3633 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3634 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3642 use MARC::File::XML (BinaryEncoding => 'utf8');
3644 binmode(STDERR, ':bytes');
3645 binmode(STDOUT, ':utf8');
3646 binmode(STDERR, ':utf8');
3648 my $marc_xml = shift;
3649 my $matching_u_text = shift;
3650 my $new_9_to_set = shift;
3652 $marc_xml =~ s/(<leader>.........)./${1}a/;
3655 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3658 #elog("could not parse $bibid: $@\n");
3659 import MARC::File::XML (BinaryEncoding => 'utf8');
3663 my @uris = $marc_xml->field('856');
3664 return unless @uris;
3666 foreach my $field (@uris) {
3667 my $sfu = $field->subfield('u');
3668 my $ind2 = $field->indicator('2');
3669 if (!defined $ind2) { next; }
3670 if ($ind2 ne '0') { next; }
3671 if (!defined $sfu) { next; }
3672 if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
3673 $field->add_subfields( '9' => $new_9_to_set );
3678 return $marc_xml->as_xml_record();
3682 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3683 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3692 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3694 SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3697 new_xml = '$_$' || new_xml || '$_$';
3699 IF new_xml != source_xml THEN
3700 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3707 $BODY$ LANGUAGE plpgsql;
3710 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
3711 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
3719 use MARC::File::XML (BinaryEncoding => 'utf8');
3721 binmode(STDERR, ':bytes');
3722 binmode(STDOUT, ':utf8');
3723 binmode(STDERR, ':utf8');
3725 my $marc_xml = shift;
3728 $marc_xml =~ s/(<leader>.........)./${1}a/;
3731 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3734 #elog("could not parse $bibid: $@\n");
3735 import MARC::File::XML (BinaryEncoding => 'utf8');
3739 my @fields = $marc_xml->field($tag);
3740 return $marc_xml->as_xml_record() unless @fields;
3742 $marc_xml->delete_fields(@fields);
3744 return $marc_xml->as_xml_record();
3748 -- convenience function for linking to the item staging table
3750 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3752 table_schema ALIAS FOR $1;
3753 table_name ALIAS FOR $2;
3754 foreign_column_name ALIAS FOR $3;
3755 main_column_name ALIAS FOR $4;
3756 btrim_desired ALIAS FOR $5;
3759 EXECUTE 'SELECT EXISTS (
3761 FROM information_schema.columns
3762 WHERE table_schema = $1
3764 and column_name = $3
3765 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3767 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3770 EXECUTE 'SELECT EXISTS (
3772 FROM information_schema.columns
3773 WHERE table_schema = $1
3774 AND table_name = ''asset_copy_legacy''
3775 and column_name = $2
3776 )' INTO proceed USING table_schema, main_column_name;
3778 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3781 EXECUTE 'ALTER TABLE '
3782 || quote_ident(table_name)
3783 || ' DROP COLUMN IF EXISTS x_item';
3784 EXECUTE 'ALTER TABLE '
3785 || quote_ident(table_name)
3786 || ' ADD COLUMN x_item BIGINT';
3788 IF btrim_desired THEN
3789 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3790 || ' SET x_item = b.id FROM asset_copy_legacy b'
3791 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3792 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3794 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3795 || ' SET x_item = b.id FROM asset_copy_legacy b'
3796 || ' WHERE a.' || quote_ident(foreign_column_name)
3797 || ' = b.' || quote_ident(main_column_name);
3800 --EXECUTE 'SELECT migration_tools.assert(
3801 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3802 -- ''Cannot link every barcode'',
3803 -- ''Every barcode linked''
3807 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3809 -- convenience function for linking to the user staging table
3811 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3813 table_schema ALIAS FOR $1;
3814 table_name ALIAS FOR $2;
3815 foreign_column_name ALIAS FOR $3;
3816 main_column_name ALIAS FOR $4;
3817 btrim_desired ALIAS FOR $5;
3820 EXECUTE 'SELECT EXISTS (
3822 FROM information_schema.columns
3823 WHERE table_schema = $1
3825 and column_name = $3
3826 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3828 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3831 EXECUTE 'SELECT EXISTS (
3833 FROM information_schema.columns
3834 WHERE table_schema = $1
3835 AND table_name = ''actor_usr_legacy''
3836 and column_name = $2
3837 )' INTO proceed USING table_schema, main_column_name;
3839 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3842 EXECUTE 'ALTER TABLE '
3843 || quote_ident(table_name)
3844 || ' DROP COLUMN IF EXISTS x_user';
3845 EXECUTE 'ALTER TABLE '
3846 || quote_ident(table_name)
3847 || ' ADD COLUMN x_user INTEGER';
3849 IF btrim_desired THEN
3850 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3851 || ' SET x_user = b.id FROM actor_usr_legacy b'
3852 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3853 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3855 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3856 || ' SET x_user = b.id FROM actor_usr_legacy b'
3857 || ' WHERE a.' || quote_ident(foreign_column_name)
3858 || ' = b.' || quote_ident(main_column_name);
3861 --EXECUTE 'SELECT migration_tools.assert(
3862 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3863 -- ''Cannot link every barcode'',
3864 -- ''Every barcode linked''
3868 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3870 -- convenience function for linking two tables
3871 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3872 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3874 table_schema ALIAS FOR $1;
3875 table_a ALIAS FOR $2;
3876 column_a ALIAS FOR $3;
3877 table_b ALIAS FOR $4;
3878 column_b ALIAS FOR $5;
3879 column_x ALIAS FOR $6;
3880 btrim_desired ALIAS FOR $7;
3883 EXECUTE 'SELECT EXISTS (
3885 FROM information_schema.columns
3886 WHERE table_schema = $1
3888 and column_name = $3
3889 )' INTO proceed USING table_schema, table_a, column_a;
3891 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3894 EXECUTE 'SELECT EXISTS (
3896 FROM information_schema.columns
3897 WHERE table_schema = $1
3899 and column_name = $3
3900 )' INTO proceed USING table_schema, table_b, column_b;
3902 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3905 EXECUTE 'ALTER TABLE '
3906 || quote_ident(table_b)
3907 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3908 EXECUTE 'ALTER TABLE '
3909 || quote_ident(table_b)
3910 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3912 IF btrim_desired THEN
3913 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3914 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3915 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3916 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3918 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3919 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3920 || ' WHERE a.' || quote_ident(column_a)
3921 || ' = b.' || quote_ident(column_b);
3925 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3927 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3928 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3929 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3931 table_schema ALIAS FOR $1;
3932 table_a ALIAS FOR $2;
3933 column_a ALIAS FOR $3;
3934 table_b ALIAS FOR $4;
3935 column_b ALIAS FOR $5;
3936 column_w ALIAS FOR $6;
3937 column_x ALIAS FOR $7;
3938 btrim_desired ALIAS FOR $8;
3941 EXECUTE 'SELECT EXISTS (
3943 FROM information_schema.columns
3944 WHERE table_schema = $1
3946 and column_name = $3
3947 )' INTO proceed USING table_schema, table_a, column_a;
3949 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3952 EXECUTE 'SELECT EXISTS (
3954 FROM information_schema.columns
3955 WHERE table_schema = $1
3957 and column_name = $3
3958 )' INTO proceed USING table_schema, table_b, column_b;
3960 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3963 EXECUTE 'ALTER TABLE '
3964 || quote_ident(table_b)
3965 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3966 EXECUTE 'ALTER TABLE '
3967 || quote_ident(table_b)
3968 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3970 IF btrim_desired THEN
3971 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3972 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3973 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3974 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3976 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3977 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3978 || ' WHERE a.' || quote_ident(column_a)
3979 || ' = b.' || quote_ident(column_b);
3983 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3985 -- 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
3986 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
3987 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3989 table_schema ALIAS FOR $1;
3990 table_a ALIAS FOR $2;
3991 column_a ALIAS FOR $3;
3992 table_b ALIAS FOR $4;
3993 column_b ALIAS FOR $5;
3994 column_w ALIAS FOR $6;
3995 column_x ALIAS FOR $7;
3998 EXECUTE 'SELECT EXISTS (
4000 FROM information_schema.columns
4001 WHERE table_schema = $1
4003 and column_name = $3
4004 )' INTO proceed USING table_schema, table_a, column_a;
4006 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4009 EXECUTE 'SELECT EXISTS (
4011 FROM information_schema.columns
4012 WHERE table_schema = $1
4014 and column_name = $3
4015 )' INTO proceed USING table_schema, table_b, column_b;
4017 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4020 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4021 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4022 || ' WHERE a.' || quote_ident(column_a)
4023 || ' = b.' || quote_ident(column_b);
4026 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4028 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4030 table_schema ALIAS FOR $1;
4031 table_a ALIAS FOR $2;
4032 column_a ALIAS FOR $3;
4033 table_b ALIAS FOR $4;
4034 column_b ALIAS FOR $5;
4035 column_w ALIAS FOR $6;
4036 column_x ALIAS FOR $7;
4039 EXECUTE 'SELECT EXISTS (
4041 FROM information_schema.columns
4042 WHERE table_schema = $1
4044 and column_name = $3
4045 )' INTO proceed USING table_schema, table_a, column_a;
4047 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4050 EXECUTE 'SELECT EXISTS (
4052 FROM information_schema.columns
4053 WHERE table_schema = $1
4055 and column_name = $3
4056 )' INTO proceed USING table_schema, table_b, column_b;
4058 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4061 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4062 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4063 || ' WHERE a.' || quote_ident(column_a)
4064 || ' = b.' || quote_ident(column_b)
4065 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4068 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4070 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4072 table_schema ALIAS FOR $1;
4073 table_a ALIAS FOR $2;
4074 column_a ALIAS FOR $3;
4075 table_b ALIAS FOR $4;
4076 column_b ALIAS FOR $5;
4077 column_w ALIAS FOR $6;
4078 column_x ALIAS FOR $7;
4081 EXECUTE 'SELECT EXISTS (
4083 FROM information_schema.columns
4084 WHERE table_schema = $1
4086 and column_name = $3
4087 )' INTO proceed USING table_schema, table_a, column_a;
4089 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4092 EXECUTE 'SELECT EXISTS (
4094 FROM information_schema.columns
4095 WHERE table_schema = $1
4097 and column_name = $3
4098 )' INTO proceed USING table_schema, table_b, column_b;
4100 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4103 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4104 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4105 || ' WHERE a.' || quote_ident(column_a)
4106 || ' = b.' || quote_ident(column_b)
4107 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4110 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4112 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4114 table_schema ALIAS FOR $1;
4115 table_a ALIAS FOR $2;
4116 column_a ALIAS FOR $3;
4117 table_b ALIAS FOR $4;
4118 column_b ALIAS FOR $5;
4119 column_w ALIAS FOR $6;
4120 column_x ALIAS FOR $7;
4123 EXECUTE 'SELECT EXISTS (
4125 FROM information_schema.columns
4126 WHERE table_schema = $1
4128 and column_name = $3
4129 )' INTO proceed USING table_schema, table_a, column_a;
4131 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4134 EXECUTE 'SELECT EXISTS (
4136 FROM information_schema.columns
4137 WHERE table_schema = $1
4139 and column_name = $3
4140 )' INTO proceed USING table_schema, table_b, column_b;
4142 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4145 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4146 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4147 || ' WHERE a.' || quote_ident(column_a)
4148 || ' = b.' || quote_ident(column_b)
4149 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4152 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4154 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4156 table_schema ALIAS FOR $1;
4157 table_a ALIAS FOR $2;
4158 column_a ALIAS FOR $3;
4159 table_b ALIAS FOR $4;
4160 column_b ALIAS FOR $5;
4161 column_w ALIAS FOR $6;
4162 column_x ALIAS FOR $7;
4165 EXECUTE 'SELECT EXISTS (
4167 FROM information_schema.columns
4168 WHERE table_schema = $1
4170 and column_name = $3
4171 )' INTO proceed USING table_schema, table_a, column_a;
4173 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4176 EXECUTE 'SELECT EXISTS (
4178 FROM information_schema.columns
4179 WHERE table_schema = $1
4181 and column_name = $3
4182 )' INTO proceed USING table_schema, table_b, column_b;
4184 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4187 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4188 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4189 || ' WHERE a.' || quote_ident(column_a)
4190 || ' = b.' || quote_ident(column_b)
4191 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4194 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4196 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4198 table_schema ALIAS FOR $1;
4199 table_a ALIAS FOR $2;
4200 column_a ALIAS FOR $3;
4201 table_b ALIAS FOR $4;
4202 column_b ALIAS FOR $5;
4203 column_w ALIAS FOR $6;
4204 column_x ALIAS FOR $7;
4207 EXECUTE 'SELECT EXISTS (
4209 FROM information_schema.columns
4210 WHERE table_schema = $1
4212 and column_name = $3
4213 )' INTO proceed USING table_schema, table_a, column_a;
4215 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4218 EXECUTE 'SELECT EXISTS (
4220 FROM information_schema.columns
4221 WHERE table_schema = $1
4223 and column_name = $3
4224 )' INTO proceed USING table_schema, table_b, column_b;
4226 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4229 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4230 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4231 || ' WHERE a.' || quote_ident(column_a)
4232 || ' = b.' || quote_ident(column_b)
4233 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4238 -- convenience function for handling desired asset stat cats
4240 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4242 table_schema ALIAS FOR $1;
4243 table_name ALIAS FOR $2;
4244 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4245 org_shortname ALIAS FOR $4;
4253 SELECT 'desired_sc' || field_suffix INTO sc;
4254 SELECT 'desired_sce' || field_suffix INTO sce;
4256 EXECUTE 'SELECT EXISTS (
4258 FROM information_schema.columns
4259 WHERE table_schema = $1
4261 and column_name = $3
4262 )' INTO proceed USING table_schema, table_name, sc;
4264 RAISE EXCEPTION 'Missing column %', sc;
4266 EXECUTE 'SELECT EXISTS (
4268 FROM information_schema.columns
4269 WHERE table_schema = $1
4271 and column_name = $3
4272 )' INTO proceed USING table_schema, table_name, sce;
4274 RAISE EXCEPTION 'Missing column %', sce;
4277 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4279 RAISE EXCEPTION 'Cannot find org by shortname';
4281 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4283 -- caller responsible for their own truncates though we try to prevent duplicates
4284 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4289 ' || quote_ident(table_name) || '
4291 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4295 WHERE owner = ANY ($2)
4296 AND name = BTRIM('||sc||')
4301 WHERE owner = ANY ($2)
4302 AND name = BTRIM('||sc||')
4305 USING org, org_list;
4307 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4312 WHERE owner = ANY ($2)
4313 AND BTRIM('||sc||') = BTRIM(name))
4316 WHERE owner = ANY ($2)
4317 AND BTRIM('||sc||') = BTRIM(name))
4322 ' || quote_ident(table_name) || '
4324 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4325 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4328 FROM asset.stat_cat_entry
4332 WHERE owner = ANY ($2)
4333 AND BTRIM('||sc||') = BTRIM(name)
4334 ) AND value = BTRIM('||sce||')
4335 AND owner = ANY ($2)
4339 FROM asset_stat_cat_entry
4343 WHERE owner = ANY ($2)
4344 AND BTRIM('||sc||') = BTRIM(name)
4345 ) AND value = BTRIM('||sce||')
4346 AND owner = ANY ($2)
4349 USING org, org_list;
4351 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4353 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4355 table_schema ALIAS FOR $1;
4356 table_name ALIAS FOR $2;
4357 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4358 org_shortname ALIAS FOR $4;
4366 SELECT 'desired_sc' || field_suffix INTO sc;
4367 SELECT 'desired_sce' || field_suffix INTO sce;
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, sc;
4376 RAISE EXCEPTION 'Missing column %', sc;
4378 EXECUTE 'SELECT EXISTS (
4380 FROM information_schema.columns
4381 WHERE table_schema = $1
4383 and column_name = $3
4384 )' INTO proceed USING table_schema, table_name, sce;
4386 RAISE EXCEPTION 'Missing column %', sce;
4389 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4391 RAISE EXCEPTION 'Cannot find org by shortname';
4394 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4396 EXECUTE 'ALTER TABLE '
4397 || quote_ident(table_name)
4398 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4399 EXECUTE 'ALTER TABLE '
4400 || quote_ident(table_name)
4401 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4402 EXECUTE 'ALTER TABLE '
4403 || quote_ident(table_name)
4404 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4405 EXECUTE 'ALTER TABLE '
4406 || quote_ident(table_name)
4407 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4410 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4412 x_sc' || field_suffix || ' = id
4414 (SELECT id, name, owner FROM asset_stat_cat
4415 UNION SELECT id, name, owner FROM asset.stat_cat) u
4417 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4418 AND u.owner = ANY ($1);'
4421 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4423 x_sce' || field_suffix || ' = id
4425 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4426 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4428 u.stat_cat = x_sc' || field_suffix || '
4429 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4430 AND u.owner = ANY ($1);'
4433 EXECUTE 'SELECT migration_tools.assert(
4434 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4435 ''Cannot find a desired stat cat'',
4436 ''Found all desired stat cats''
4439 EXECUTE 'SELECT migration_tools.assert(
4440 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4441 ''Cannot find a desired stat cat entry'',
4442 ''Found all desired stat cat entries''
4446 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4448 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
4449 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4456 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4458 AND table_schema = s_name
4459 AND (data_type='text' OR data_type='character varying')
4460 AND column_name like 'l_%'
4462 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4469 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
4470 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4477 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4479 AND table_schema = s_name
4480 AND (data_type='text' OR data_type='character varying')
4482 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4489 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
4490 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4497 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4499 AND table_schema = s_name
4500 AND (data_type='text' OR data_type='character varying')
4501 AND column_name like 'l_%'
4503 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
4510 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
4511 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4518 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4520 AND table_schema = s_name
4521 AND (data_type='text' OR data_type='character varying')
4523 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');