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.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2359 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2360 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
2361 FOR id IN EXECUTE loopq USING delimiter LOOP
2362 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2363 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2364 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2365 EXECUTE splitst USING id, delimiter;
2368 $FUNC$ LANGUAGE PLPGSQL;
2370 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2376 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2379 MARC::Charset->assume_unicode(1);
2381 my $target_xml = shift;
2382 my $source_xml = shift;
2388 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2392 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2397 my $source_id = $source->subfield('901', 'c');
2398 $source_id = $source->subfield('903', 'a') unless $source_id;
2399 my $target_id = $target->subfield('901', 'c');
2400 $target_id = $target->subfield('903', 'a') unless $target_id;
2402 my %existing_fields;
2403 foreach my $tag (@$tags) {
2404 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2405 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2406 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2408 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2412 my $xml = $target->as_xml_record;
2413 $xml =~ s/^<\?.+?\?>$//mo;
2415 $xml =~ s/>\s+</></sgo;
2419 $func$ LANGUAGE PLPERLU;
2420 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.';
2422 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2428 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2431 my $in_tags = shift;
2432 my $in_values = shift;
2434 # hack-and-slash parsing of array-passed-as-string;
2435 # this can go away once everybody is running Postgres 9.1+
2436 my $csv = Text::CSV->new({binary => 1});
2439 my $status = $csv->parse($in_tags);
2440 my $tags = [ $csv->fields() ];
2441 $in_values =~ s/^{//;
2442 $in_values =~ s/}$//;
2443 $status = $csv->parse($in_values);
2444 my $values = [ $csv->fields() ];
2446 my $marc = MARC::Record->new();
2448 $marc->leader('00000nam a22000007 4500');
2449 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2451 foreach my $i (0..$#$tags) {
2453 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2456 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2457 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2459 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2463 my $xml = $marc->as_xml_record;
2464 $xml =~ s/^<\?.+?\?>$//mo;
2466 $xml =~ s/>\s+</></sgo;
2470 $func$ LANGUAGE PLPERLU;
2471 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2472 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2473 The second argument is an array of text containing the values to plug into each field.
2474 If the value for a given field is NULL or the empty string, it is not inserted.
2477 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2479 my ($marcxml, $tag, $pos, $value) = @_;
2482 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2486 MARC::Charset->assume_unicode(1);
2488 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2489 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2490 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2491 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2495 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2497 foreach my $field ($marc->field($tag)) {
2498 $field->update("ind$pos" => $value);
2500 $xml = $marc->as_xml_record;
2501 $xml =~ s/^<\?.+?\?>$//mo;
2503 $xml =~ s/>\s+</></sgo;
2507 $func$ LANGUAGE PLPERLU;
2509 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2510 The first argument is a MARCXML string.
2511 The second argument is a MARC tag.
2512 The third argument is the indicator position, either 1 or 2.
2513 The fourth argument is the character to set the indicator value to.
2514 All occurences of the specified field will be changed.
2515 The function returns the revised MARCXML string.$$;
2517 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2522 first_name TEXT DEFAULT '',
2523 last_name TEXT DEFAULT ''
2524 ) RETURNS VOID AS $func$
2526 RAISE NOTICE '%', org ;
2527 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2528 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2529 FROM actor.org_unit aou, permission.grp_tree pgt
2530 WHERE aou.shortname = org
2531 AND pgt.name = perm_group;
2536 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2537 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2539 target_event_def ALIAS FOR $1;
2542 DROP TABLE IF EXISTS new_atevdefs;
2543 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2544 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2545 INSERT INTO action_trigger.event_definition (
2566 ,name || ' (clone of '||target_event_def||')'
2582 action_trigger.event_definition
2584 id = target_event_def
2586 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2587 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2588 INSERT INTO action_trigger.environment (
2594 currval('action_trigger.event_definition_id_seq')
2599 action_trigger.environment
2601 event_def = target_event_def
2603 INSERT INTO action_trigger.event_params (
2608 currval('action_trigger.event_definition_id_seq')
2612 action_trigger.event_params
2614 event_def = target_event_def
2617 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);
2619 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2621 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2622 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2624 target_event_def ALIAS FOR $1;
2626 new_interval ALIAS FOR $3;
2628 DROP TABLE IF EXISTS new_atevdefs;
2629 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2630 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2631 INSERT INTO action_trigger.event_definition (
2652 ,name || ' (clone of '||target_event_def||')'
2668 action_trigger.event_definition
2670 id = target_event_def
2672 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2673 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2674 INSERT INTO action_trigger.environment (
2680 currval('action_trigger.event_definition_id_seq')
2685 action_trigger.environment
2687 event_def = target_event_def
2689 INSERT INTO action_trigger.event_params (
2694 currval('action_trigger.event_definition_id_seq')
2698 action_trigger.event_params
2700 event_def = target_event_def
2703 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);
2705 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2707 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
2708 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
2711 target_event_defs ALIAS FOR $2;
2713 DROP TABLE IF EXISTS new_atevdefs;
2714 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2715 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
2716 INSERT INTO action_trigger.event_definition (
2737 ,name || ' (clone of '||target_event_defs[i]||')'
2753 action_trigger.event_definition
2755 id = target_event_defs[i]
2757 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2758 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2759 INSERT INTO action_trigger.environment (
2765 currval('action_trigger.event_definition_id_seq')
2770 action_trigger.environment
2772 event_def = target_event_defs[i]
2774 INSERT INTO action_trigger.event_params (
2779 currval('action_trigger.event_definition_id_seq')
2783 action_trigger.event_params
2785 event_def = target_event_defs[i]
2788 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2790 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2792 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
2794 action_trigger.event
2798 ,complete_time = NULL
2799 ,update_process = NULL
2801 ,template_output = NULL
2802 ,error_output = NULL
2803 ,async_output = NULL
2808 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2812 use MARC::File::XML;
2817 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2818 $field = $marc->leader();
2821 $$ LANGUAGE PLPERLU STABLE;
2823 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2824 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2827 use MARC::File::XML;
2832 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2833 $field = $marc->field($tag);
2835 return $field->as_string($subfield,$delimiter);
2836 $$ LANGUAGE PLPERLU STABLE;
2838 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2839 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2842 use MARC::File::XML;
2847 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2848 @fields = $marc->field($tag);
2851 foreach my $field (@fields) {
2852 push @texts, $field->as_string($subfield,$delimiter);
2855 $$ LANGUAGE PLPERLU STABLE;
2857 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2858 SELECT action.find_hold_matrix_matchpoint(
2859 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2860 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2861 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2862 (SELECT usr FROM action.hold_request WHERE id = $1),
2863 (SELECT requestor FROM action.hold_request WHERE id = $1)
2867 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2868 SELECT action.hold_request_permit_test(
2869 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2870 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2871 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2872 (SELECT usr FROM action.hold_request WHERE id = $1),
2873 (SELECT requestor FROM action.hold_request WHERE id = $1)
2877 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2878 SELECT action.find_circ_matrix_matchpoint(
2879 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2880 (SELECT target_copy FROM action.circulation WHERE id = $1),
2881 (SELECT usr FROM action.circulation WHERE id = $1),
2883 NULLIF(phone_renewal,false),
2884 NULLIF(desk_renewal,false),
2885 NULLIF(opac_renewal,false),
2887 ) FROM action.circulation WHERE id = $1
2892 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2897 RAISE EXCEPTION 'assertion';
2900 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2902 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2908 RAISE EXCEPTION '%', msg;
2911 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2913 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2916 fail_msg ALIAS FOR $2;
2917 success_msg ALIAS FOR $3;
2920 RAISE EXCEPTION '%', fail_msg;
2924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2926 -- push bib sequence and return starting value for reserved range
2927 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2929 bib_count ALIAS FOR $1;
2932 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2934 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2939 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2941 -- set a new salted password
2943 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2945 usr_id ALIAS FOR $1;
2946 plain_passwd ALIAS FOR $2;
2951 SELECT actor.create_salt('main') INTO plain_salt;
2953 SELECT MD5(plain_passwd) INTO md5_passwd;
2955 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2963 -- convenience functions for handling copy_location maps
2965 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2967 table_schema ALIAS FOR $1;
2968 table_name ALIAS FOR $2;
2969 org_shortname ALIAS FOR $3;
2970 org_range ALIAS FOR $4;
2976 EXECUTE 'SELECT EXISTS (
2978 FROM information_schema.columns
2979 WHERE table_schema = $1
2981 and column_name = ''desired_shelf''
2982 )' INTO proceed USING table_schema, table_name;
2984 RAISE EXCEPTION 'Missing column desired_shelf';
2987 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2989 RAISE EXCEPTION 'Cannot find org by shortname';
2992 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2994 EXECUTE 'ALTER TABLE '
2995 || quote_ident(table_name)
2996 || ' DROP COLUMN IF EXISTS x_shelf';
2997 EXECUTE 'ALTER TABLE '
2998 || quote_ident(table_name)
2999 || ' ADD COLUMN x_shelf INTEGER';
3001 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3002 || ' SET x_shelf = id FROM asset_copy_location b'
3003 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3004 || ' AND b.owning_lib = $1'
3005 || ' AND NOT b.deleted'
3008 FOREACH o IN ARRAY org_list LOOP
3009 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3010 || ' SET x_shelf = id FROM asset.copy_location b'
3011 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3012 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3013 || ' AND NOT b.deleted'
3017 EXECUTE 'SELECT migration_tools.assert(
3018 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3019 ''Cannot find a desired location'',
3020 ''Found all desired locations''
3024 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3026 -- convenience functions for handling circmod maps
3028 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3030 table_schema ALIAS FOR $1;
3031 table_name ALIAS FOR $2;
3034 EXECUTE 'SELECT EXISTS (
3036 FROM information_schema.columns
3037 WHERE table_schema = $1
3039 and column_name = ''desired_circmod''
3040 )' INTO proceed USING table_schema, table_name;
3042 RAISE EXCEPTION 'Missing column desired_circmod';
3045 EXECUTE 'ALTER TABLE '
3046 || quote_ident(table_name)
3047 || ' DROP COLUMN IF EXISTS x_circmod';
3048 EXECUTE 'ALTER TABLE '
3049 || quote_ident(table_name)
3050 || ' ADD COLUMN x_circmod TEXT';
3052 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3053 || ' SET x_circmod = code FROM config.circ_modifier b'
3054 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3056 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3057 || ' SET x_circmod = code FROM config.circ_modifier b'
3058 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3059 || ' AND x_circmod IS NULL';
3061 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3062 || ' SET x_circmod = code FROM config.circ_modifier b'
3063 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3064 || ' AND x_circmod IS NULL';
3066 EXECUTE 'SELECT migration_tools.assert(
3067 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3068 ''Cannot find a desired circulation modifier'',
3069 ''Found all desired circulation modifiers''
3073 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3075 -- convenience functions for handling item status maps
3077 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3079 table_schema ALIAS FOR $1;
3080 table_name ALIAS FOR $2;
3083 EXECUTE 'SELECT EXISTS (
3085 FROM information_schema.columns
3086 WHERE table_schema = $1
3088 and column_name = ''desired_status''
3089 )' INTO proceed USING table_schema, table_name;
3091 RAISE EXCEPTION 'Missing column desired_status';
3094 EXECUTE 'ALTER TABLE '
3095 || quote_ident(table_name)
3096 || ' DROP COLUMN IF EXISTS x_status';
3097 EXECUTE 'ALTER TABLE '
3098 || quote_ident(table_name)
3099 || ' ADD COLUMN x_status INTEGER';
3101 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3102 || ' SET x_status = id FROM config.copy_status b'
3103 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3105 EXECUTE 'SELECT migration_tools.assert(
3106 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3107 ''Cannot find a desired copy status'',
3108 ''Found all desired copy statuses''
3112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3114 -- convenience functions for handling org maps
3116 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3118 table_schema ALIAS FOR $1;
3119 table_name ALIAS FOR $2;
3122 EXECUTE 'SELECT EXISTS (
3124 FROM information_schema.columns
3125 WHERE table_schema = $1
3127 and column_name = ''desired_org''
3128 )' INTO proceed USING table_schema, table_name;
3130 RAISE EXCEPTION 'Missing column desired_org';
3133 EXECUTE 'ALTER TABLE '
3134 || quote_ident(table_name)
3135 || ' DROP COLUMN IF EXISTS x_org';
3136 EXECUTE 'ALTER TABLE '
3137 || quote_ident(table_name)
3138 || ' ADD COLUMN x_org INTEGER';
3140 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3141 || ' SET x_org = id FROM actor.org_unit b'
3142 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3144 EXECUTE 'SELECT migration_tools.assert(
3145 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3146 ''Cannot find a desired org unit'',
3147 ''Found all desired org units''
3151 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3153 -- convenience function for handling desired_not_migrate
3155 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3157 table_schema ALIAS FOR $1;
3158 table_name ALIAS FOR $2;
3161 EXECUTE 'SELECT EXISTS (
3163 FROM information_schema.columns
3164 WHERE table_schema = $1
3166 and column_name = ''desired_not_migrate''
3167 )' INTO proceed USING table_schema, table_name;
3169 RAISE EXCEPTION 'Missing column desired_not_migrate';
3172 EXECUTE 'ALTER TABLE '
3173 || quote_ident(table_name)
3174 || ' DROP COLUMN IF EXISTS x_migrate';
3175 EXECUTE 'ALTER TABLE '
3176 || quote_ident(table_name)
3177 || ' ADD COLUMN x_migrate BOOLEAN';
3179 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3180 || ' SET x_migrate = CASE'
3181 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3182 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3183 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3184 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3185 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3186 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3189 EXECUTE 'SELECT migration_tools.assert(
3190 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3191 ''Not all desired_not_migrate values understood'',
3192 ''All desired_not_migrate values understood''
3196 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3198 -- convenience function for handling desired_not_migrate
3200 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3202 table_schema ALIAS FOR $1;
3203 table_name ALIAS FOR $2;
3206 EXECUTE 'SELECT EXISTS (
3208 FROM information_schema.columns
3209 WHERE table_schema = $1
3211 and column_name = ''desired_barred_or_blocked''
3212 )' INTO proceed USING table_schema, table_name;
3214 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3217 EXECUTE 'ALTER TABLE '
3218 || quote_ident(table_name)
3219 || ' DROP COLUMN IF EXISTS x_barred';
3220 EXECUTE 'ALTER TABLE '
3221 || quote_ident(table_name)
3222 || ' ADD COLUMN x_barred BOOLEAN';
3224 EXECUTE 'ALTER TABLE '
3225 || quote_ident(table_name)
3226 || ' DROP COLUMN IF EXISTS x_blocked';
3227 EXECUTE 'ALTER TABLE '
3228 || quote_ident(table_name)
3229 || ' ADD COLUMN x_blocked BOOLEAN';
3231 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3232 || ' SET x_barred = CASE'
3233 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3234 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3235 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3236 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3239 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3240 || ' SET x_blocked = CASE'
3241 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3242 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3243 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3244 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3247 EXECUTE 'SELECT migration_tools.assert(
3248 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3249 ''Not all desired_barred_or_blocked values understood'',
3250 ''All desired_barred_or_blocked values understood''
3254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3256 -- convenience function for handling desired_profile
3258 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3260 table_schema ALIAS FOR $1;
3261 table_name ALIAS FOR $2;
3264 EXECUTE 'SELECT EXISTS (
3266 FROM information_schema.columns
3267 WHERE table_schema = $1
3269 and column_name = ''desired_profile''
3270 )' INTO proceed USING table_schema, table_name;
3272 RAISE EXCEPTION 'Missing column desired_profile';
3275 EXECUTE 'ALTER TABLE '
3276 || quote_ident(table_name)
3277 || ' DROP COLUMN IF EXISTS x_profile';
3278 EXECUTE 'ALTER TABLE '
3279 || quote_ident(table_name)
3280 || ' ADD COLUMN x_profile INTEGER';
3282 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3283 || ' SET x_profile = b.id FROM permission.grp_tree b'
3284 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3286 EXECUTE 'SELECT migration_tools.assert(
3287 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3288 ''Cannot find a desired profile'',
3289 ''Found all desired profiles''
3293 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3295 -- convenience function for handling desired actor stat cats
3297 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3299 table_schema ALIAS FOR $1;
3300 table_name ALIAS FOR $2;
3301 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3302 org_shortname ALIAS FOR $4;
3310 SELECT 'desired_sc' || field_suffix INTO sc;
3311 SELECT 'desired_sce' || field_suffix INTO sce;
3313 EXECUTE 'SELECT EXISTS (
3315 FROM information_schema.columns
3316 WHERE table_schema = $1
3318 and column_name = $3
3319 )' INTO proceed USING table_schema, table_name, sc;
3321 RAISE EXCEPTION 'Missing column %', sc;
3323 EXECUTE 'SELECT EXISTS (
3325 FROM information_schema.columns
3326 WHERE table_schema = $1
3328 and column_name = $3
3329 )' INTO proceed USING table_schema, table_name, sce;
3331 RAISE EXCEPTION 'Missing column %', sce;
3334 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3336 RAISE EXCEPTION 'Cannot find org by shortname';
3338 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3340 -- caller responsible for their own truncates though we try to prevent duplicates
3341 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3346 ' || quote_ident(table_name) || '
3348 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3352 WHERE owner = ANY ($2)
3353 AND name = BTRIM('||sc||')
3358 WHERE owner = ANY ($2)
3359 AND name = BTRIM('||sc||')
3362 USING org, org_list;
3364 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3369 WHERE owner = ANY ($2)
3370 AND BTRIM('||sc||') = BTRIM(name))
3373 WHERE owner = ANY ($2)
3374 AND BTRIM('||sc||') = BTRIM(name))
3379 ' || quote_ident(table_name) || '
3381 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3382 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3385 FROM actor.stat_cat_entry
3389 WHERE owner = ANY ($2)
3390 AND BTRIM('||sc||') = BTRIM(name)
3391 ) AND value = BTRIM('||sce||')
3392 AND owner = ANY ($2)
3396 FROM actor_stat_cat_entry
3400 WHERE owner = ANY ($2)
3401 AND BTRIM('||sc||') = BTRIM(name)
3402 ) AND value = BTRIM('||sce||')
3403 AND owner = ANY ($2)
3406 USING org, org_list;
3408 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3410 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3412 table_schema ALIAS FOR $1;
3413 table_name ALIAS FOR $2;
3414 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3415 org_shortname ALIAS FOR $4;
3423 SELECT 'desired_sc' || field_suffix INTO sc;
3424 SELECT 'desired_sce' || field_suffix INTO sce;
3425 EXECUTE 'SELECT EXISTS (
3427 FROM information_schema.columns
3428 WHERE table_schema = $1
3430 and column_name = $3
3431 )' INTO proceed USING table_schema, table_name, sc;
3433 RAISE EXCEPTION 'Missing column %', sc;
3435 EXECUTE 'SELECT EXISTS (
3437 FROM information_schema.columns
3438 WHERE table_schema = $1
3440 and column_name = $3
3441 )' INTO proceed USING table_schema, table_name, sce;
3443 RAISE EXCEPTION 'Missing column %', sce;
3446 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3448 RAISE EXCEPTION 'Cannot find org by shortname';
3451 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3453 EXECUTE 'ALTER TABLE '
3454 || quote_ident(table_name)
3455 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3456 EXECUTE 'ALTER TABLE '
3457 || quote_ident(table_name)
3458 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3459 EXECUTE 'ALTER TABLE '
3460 || quote_ident(table_name)
3461 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3462 EXECUTE 'ALTER TABLE '
3463 || quote_ident(table_name)
3464 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3467 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3469 x_sc' || field_suffix || ' = id
3471 (SELECT id, name, owner FROM actor_stat_cat
3472 UNION SELECT id, name, owner FROM actor.stat_cat) u
3474 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3475 AND u.owner = ANY ($1);'
3478 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3480 x_sce' || field_suffix || ' = id
3482 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3483 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3485 u.stat_cat = x_sc' || field_suffix || '
3486 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3487 AND u.owner = ANY ($1);'
3490 EXECUTE 'SELECT migration_tools.assert(
3491 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3492 ''Cannot find a desired stat cat'',
3493 ''Found all desired stat cats''
3496 EXECUTE 'SELECT migration_tools.assert(
3497 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3498 ''Cannot find a desired stat cat entry'',
3499 ''Found all desired stat cat entries''
3503 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3505 -- convenience functions for adding shelving locations
3506 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3507 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3513 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3516 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3517 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3518 IF return_id IS NOT NULL THEN
3526 $$ LANGUAGE plpgsql;
3528 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3530 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3531 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3537 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3540 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3542 SELECT INTO return_id id FROM
3543 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3544 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3545 IF return_id IS NOT NULL THEN
3553 $$ LANGUAGE plpgsql;
3555 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
3556 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
3557 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
3565 use MARC::File::XML (BinaryEncoding => 'utf8');
3567 binmode(STDERR, ':bytes');
3568 binmode(STDOUT, ':utf8');
3569 binmode(STDERR, ':utf8');
3571 my $marc_xml = shift;
3572 my $new_9_to_set = shift;
3574 $marc_xml =~ s/(<leader>.........)./${1}a/;
3577 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3580 #elog("could not parse $bibid: $@\n");
3581 import MARC::File::XML (BinaryEncoding => 'utf8');
3585 my @uris = $marc_xml->field('856');
3586 return $marc_xml->as_xml_record() unless @uris;
3588 foreach my $field (@uris) {
3589 my $ind1 = $field->indicator('1');
3590 if (!defined $ind1) { next; }
3591 if ($ind1 ne '1' && $ind1 ne '4') { next; }
3592 my $ind2 = $field->indicator('2');
3593 if (!defined $ind2) { next; }
3594 if ($ind2 ne '0' && $ind2 ne '1') { next; }
3595 $field->add_subfields( '9' => $new_9_to_set );
3598 return $marc_xml->as_xml_record();
3602 -- yet another subfield 9 function, this one only adds the $9 and forces
3603 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
3604 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
3605 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
3613 use MARC::File::XML (BinaryEncoding => 'utf8');
3615 binmode(STDERR, ':bytes');
3616 binmode(STDOUT, ':utf8');
3617 binmode(STDERR, ':utf8');
3619 my $marc_xml = shift;
3620 my $new_9_to_set = shift;
3622 $marc_xml =~ s/(<leader>.........)./${1}a/;
3625 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3628 #elog("could not parse $bibid: $@\n");
3629 import MARC::File::XML (BinaryEncoding => 'utf8');
3633 my @uris = $marc_xml->field('856');
3634 return $marc_xml->as_xml_record() unless @uris;
3636 foreach my $field (@uris) {
3637 my $ind1 = $field->indicator('1');
3638 if (!defined $ind1) { next; }
3639 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
3640 my $ind2 = $field->indicator('2');
3641 if (!defined $ind2) { next; }
3642 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
3643 $field->add_subfields( '9' => $new_9_to_set );
3646 return $marc_xml->as_xml_record();
3650 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3651 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3652 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3660 use MARC::File::XML (BinaryEncoding => 'utf8');
3662 binmode(STDERR, ':bytes');
3663 binmode(STDOUT, ':utf8');
3664 binmode(STDERR, ':utf8');
3666 my $marc_xml = shift;
3667 my $matching_u_text = shift;
3668 my $new_9_to_set = shift;
3670 $marc_xml =~ s/(<leader>.........)./${1}a/;
3673 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3676 #elog("could not parse $bibid: $@\n");
3677 import MARC::File::XML (BinaryEncoding => 'utf8');
3681 my @uris = $marc_xml->field('856');
3682 return unless @uris;
3684 foreach my $field (@uris) {
3685 my $sfu = $field->subfield('u');
3686 my $ind2 = $field->indicator('2');
3687 if (!defined $ind2) { next; }
3688 if ($ind2 ne '0') { next; }
3689 if (!defined $sfu) { next; }
3690 if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
3691 $field->add_subfields( '9' => $new_9_to_set );
3696 return $marc_xml->as_xml_record();
3700 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3701 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3710 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3712 SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3715 new_xml = '$_$' || new_xml || '$_$';
3717 IF new_xml != source_xml THEN
3718 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3725 $BODY$ LANGUAGE plpgsql;
3728 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
3729 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
3737 use MARC::File::XML (BinaryEncoding => 'utf8');
3739 binmode(STDERR, ':bytes');
3740 binmode(STDOUT, ':utf8');
3741 binmode(STDERR, ':utf8');
3743 my $marc_xml = shift;
3746 $marc_xml =~ s/(<leader>.........)./${1}a/;
3749 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3752 #elog("could not parse $bibid: $@\n");
3753 import MARC::File::XML (BinaryEncoding => 'utf8');
3757 my @fields = $marc_xml->field($tag);
3758 return $marc_xml->as_xml_record() unless @fields;
3760 $marc_xml->delete_fields(@fields);
3762 return $marc_xml->as_xml_record();
3766 -- convenience function for linking to the item staging table
3768 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3770 table_schema ALIAS FOR $1;
3771 table_name ALIAS FOR $2;
3772 foreign_column_name ALIAS FOR $3;
3773 main_column_name ALIAS FOR $4;
3774 btrim_desired ALIAS FOR $5;
3777 EXECUTE 'SELECT EXISTS (
3779 FROM information_schema.columns
3780 WHERE table_schema = $1
3782 and column_name = $3
3783 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3785 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3788 EXECUTE 'SELECT EXISTS (
3790 FROM information_schema.columns
3791 WHERE table_schema = $1
3792 AND table_name = ''asset_copy_legacy''
3793 and column_name = $2
3794 )' INTO proceed USING table_schema, main_column_name;
3796 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3799 EXECUTE 'ALTER TABLE '
3800 || quote_ident(table_name)
3801 || ' DROP COLUMN IF EXISTS x_item';
3802 EXECUTE 'ALTER TABLE '
3803 || quote_ident(table_name)
3804 || ' ADD COLUMN x_item BIGINT';
3806 IF btrim_desired THEN
3807 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3808 || ' SET x_item = b.id FROM asset_copy_legacy b'
3809 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3810 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3812 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3813 || ' SET x_item = b.id FROM asset_copy_legacy b'
3814 || ' WHERE a.' || quote_ident(foreign_column_name)
3815 || ' = b.' || quote_ident(main_column_name);
3818 --EXECUTE 'SELECT migration_tools.assert(
3819 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3820 -- ''Cannot link every barcode'',
3821 -- ''Every barcode linked''
3825 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3827 -- convenience function for linking to the user staging table
3829 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3831 table_schema ALIAS FOR $1;
3832 table_name ALIAS FOR $2;
3833 foreign_column_name ALIAS FOR $3;
3834 main_column_name ALIAS FOR $4;
3835 btrim_desired ALIAS FOR $5;
3838 EXECUTE 'SELECT EXISTS (
3840 FROM information_schema.columns
3841 WHERE table_schema = $1
3843 and column_name = $3
3844 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3846 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3849 EXECUTE 'SELECT EXISTS (
3851 FROM information_schema.columns
3852 WHERE table_schema = $1
3853 AND table_name = ''actor_usr_legacy''
3854 and column_name = $2
3855 )' INTO proceed USING table_schema, main_column_name;
3857 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3860 EXECUTE 'ALTER TABLE '
3861 || quote_ident(table_name)
3862 || ' DROP COLUMN IF EXISTS x_user';
3863 EXECUTE 'ALTER TABLE '
3864 || quote_ident(table_name)
3865 || ' ADD COLUMN x_user INTEGER';
3867 IF btrim_desired THEN
3868 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3869 || ' SET x_user = b.id FROM actor_usr_legacy b'
3870 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3871 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3873 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3874 || ' SET x_user = b.id FROM actor_usr_legacy b'
3875 || ' WHERE a.' || quote_ident(foreign_column_name)
3876 || ' = b.' || quote_ident(main_column_name);
3879 --EXECUTE 'SELECT migration_tools.assert(
3880 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3881 -- ''Cannot link every barcode'',
3882 -- ''Every barcode linked''
3886 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3888 -- convenience function for linking two tables
3889 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3890 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3892 table_schema ALIAS FOR $1;
3893 table_a ALIAS FOR $2;
3894 column_a ALIAS FOR $3;
3895 table_b ALIAS FOR $4;
3896 column_b ALIAS FOR $5;
3897 column_x ALIAS FOR $6;
3898 btrim_desired ALIAS FOR $7;
3901 EXECUTE 'SELECT EXISTS (
3903 FROM information_schema.columns
3904 WHERE table_schema = $1
3906 and column_name = $3
3907 )' INTO proceed USING table_schema, table_a, column_a;
3909 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3912 EXECUTE 'SELECT EXISTS (
3914 FROM information_schema.columns
3915 WHERE table_schema = $1
3917 and column_name = $3
3918 )' INTO proceed USING table_schema, table_b, column_b;
3920 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3923 EXECUTE 'ALTER TABLE '
3924 || quote_ident(table_b)
3925 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3926 EXECUTE 'ALTER TABLE '
3927 || quote_ident(table_b)
3928 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3930 IF btrim_desired THEN
3931 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3932 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3933 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3934 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3936 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3937 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3938 || ' WHERE a.' || quote_ident(column_a)
3939 || ' = b.' || quote_ident(column_b);
3943 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3945 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3946 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3947 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3949 table_schema ALIAS FOR $1;
3950 table_a ALIAS FOR $2;
3951 column_a ALIAS FOR $3;
3952 table_b ALIAS FOR $4;
3953 column_b ALIAS FOR $5;
3954 column_w ALIAS FOR $6;
3955 column_x ALIAS FOR $7;
3956 btrim_desired ALIAS FOR $8;
3959 EXECUTE 'SELECT EXISTS (
3961 FROM information_schema.columns
3962 WHERE table_schema = $1
3964 and column_name = $3
3965 )' INTO proceed USING table_schema, table_a, column_a;
3967 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3970 EXECUTE 'SELECT EXISTS (
3972 FROM information_schema.columns
3973 WHERE table_schema = $1
3975 and column_name = $3
3976 )' INTO proceed USING table_schema, table_b, column_b;
3978 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3981 EXECUTE 'ALTER TABLE '
3982 || quote_ident(table_b)
3983 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3984 EXECUTE 'ALTER TABLE '
3985 || quote_ident(table_b)
3986 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3988 IF btrim_desired THEN
3989 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3990 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3991 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3992 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3994 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3995 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3996 || ' WHERE a.' || quote_ident(column_a)
3997 || ' = b.' || quote_ident(column_b);
4001 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4003 -- 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
4004 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4005 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4007 table_schema ALIAS FOR $1;
4008 table_a ALIAS FOR $2;
4009 column_a ALIAS FOR $3;
4010 table_b ALIAS FOR $4;
4011 column_b ALIAS FOR $5;
4012 column_w ALIAS FOR $6;
4013 column_x ALIAS FOR $7;
4016 EXECUTE 'SELECT EXISTS (
4018 FROM information_schema.columns
4019 WHERE table_schema = $1
4021 and column_name = $3
4022 )' INTO proceed USING table_schema, table_a, column_a;
4024 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4027 EXECUTE 'SELECT EXISTS (
4029 FROM information_schema.columns
4030 WHERE table_schema = $1
4032 and column_name = $3
4033 )' INTO proceed USING table_schema, table_b, column_b;
4035 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4038 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4039 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4040 || ' WHERE a.' || quote_ident(column_a)
4041 || ' = b.' || quote_ident(column_b);
4044 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4046 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4048 table_schema ALIAS FOR $1;
4049 table_a ALIAS FOR $2;
4050 column_a ALIAS FOR $3;
4051 table_b ALIAS FOR $4;
4052 column_b ALIAS FOR $5;
4053 column_w ALIAS FOR $6;
4054 column_x ALIAS FOR $7;
4057 EXECUTE 'SELECT EXISTS (
4059 FROM information_schema.columns
4060 WHERE table_schema = $1
4062 and column_name = $3
4063 )' INTO proceed USING table_schema, table_a, column_a;
4065 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4068 EXECUTE 'SELECT EXISTS (
4070 FROM information_schema.columns
4071 WHERE table_schema = $1
4073 and column_name = $3
4074 )' INTO proceed USING table_schema, table_b, column_b;
4076 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4079 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4080 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4081 || ' WHERE a.' || quote_ident(column_a)
4082 || ' = b.' || quote_ident(column_b)
4083 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4086 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4088 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4090 table_schema ALIAS FOR $1;
4091 table_a ALIAS FOR $2;
4092 column_a ALIAS FOR $3;
4093 table_b ALIAS FOR $4;
4094 column_b ALIAS FOR $5;
4095 column_w ALIAS FOR $6;
4096 column_x ALIAS FOR $7;
4099 EXECUTE 'SELECT EXISTS (
4101 FROM information_schema.columns
4102 WHERE table_schema = $1
4104 and column_name = $3
4105 )' INTO proceed USING table_schema, table_a, column_a;
4107 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4110 EXECUTE 'SELECT EXISTS (
4112 FROM information_schema.columns
4113 WHERE table_schema = $1
4115 and column_name = $3
4116 )' INTO proceed USING table_schema, table_b, column_b;
4118 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4121 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4122 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4123 || ' WHERE a.' || quote_ident(column_a)
4124 || ' = b.' || quote_ident(column_b)
4125 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4128 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4130 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4132 table_schema ALIAS FOR $1;
4133 table_a ALIAS FOR $2;
4134 column_a ALIAS FOR $3;
4135 table_b ALIAS FOR $4;
4136 column_b ALIAS FOR $5;
4137 column_w ALIAS FOR $6;
4138 column_x ALIAS FOR $7;
4141 EXECUTE 'SELECT EXISTS (
4143 FROM information_schema.columns
4144 WHERE table_schema = $1
4146 and column_name = $3
4147 )' INTO proceed USING table_schema, table_a, column_a;
4149 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4152 EXECUTE 'SELECT EXISTS (
4154 FROM information_schema.columns
4155 WHERE table_schema = $1
4157 and column_name = $3
4158 )' INTO proceed USING table_schema, table_b, column_b;
4160 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4163 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4164 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4165 || ' WHERE a.' || quote_ident(column_a)
4166 || ' = b.' || quote_ident(column_b)
4167 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4170 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4172 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4174 table_schema ALIAS FOR $1;
4175 table_a ALIAS FOR $2;
4176 column_a ALIAS FOR $3;
4177 table_b ALIAS FOR $4;
4178 column_b ALIAS FOR $5;
4179 column_w ALIAS FOR $6;
4180 column_x ALIAS FOR $7;
4183 EXECUTE 'SELECT EXISTS (
4185 FROM information_schema.columns
4186 WHERE table_schema = $1
4188 and column_name = $3
4189 )' INTO proceed USING table_schema, table_a, column_a;
4191 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4194 EXECUTE 'SELECT EXISTS (
4196 FROM information_schema.columns
4197 WHERE table_schema = $1
4199 and column_name = $3
4200 )' INTO proceed USING table_schema, table_b, column_b;
4202 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4205 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4206 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4207 || ' WHERE a.' || quote_ident(column_a)
4208 || ' = b.' || quote_ident(column_b)
4209 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4212 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4214 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4216 table_schema ALIAS FOR $1;
4217 table_a ALIAS FOR $2;
4218 column_a ALIAS FOR $3;
4219 table_b ALIAS FOR $4;
4220 column_b ALIAS FOR $5;
4221 column_w ALIAS FOR $6;
4222 column_x ALIAS FOR $7;
4225 EXECUTE 'SELECT EXISTS (
4227 FROM information_schema.columns
4228 WHERE table_schema = $1
4230 and column_name = $3
4231 )' INTO proceed USING table_schema, table_a, column_a;
4233 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4236 EXECUTE 'SELECT EXISTS (
4238 FROM information_schema.columns
4239 WHERE table_schema = $1
4241 and column_name = $3
4242 )' INTO proceed USING table_schema, table_b, column_b;
4244 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4247 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4248 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4249 || ' WHERE a.' || quote_ident(column_a)
4250 || ' = b.' || quote_ident(column_b)
4251 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4256 -- convenience function for handling desired asset stat cats
4258 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4260 table_schema ALIAS FOR $1;
4261 table_name ALIAS FOR $2;
4262 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4263 org_shortname ALIAS FOR $4;
4271 SELECT 'desired_sc' || field_suffix INTO sc;
4272 SELECT 'desired_sce' || field_suffix INTO sce;
4274 EXECUTE 'SELECT EXISTS (
4276 FROM information_schema.columns
4277 WHERE table_schema = $1
4279 and column_name = $3
4280 )' INTO proceed USING table_schema, table_name, sc;
4282 RAISE EXCEPTION 'Missing column %', sc;
4284 EXECUTE 'SELECT EXISTS (
4286 FROM information_schema.columns
4287 WHERE table_schema = $1
4289 and column_name = $3
4290 )' INTO proceed USING table_schema, table_name, sce;
4292 RAISE EXCEPTION 'Missing column %', sce;
4295 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4297 RAISE EXCEPTION 'Cannot find org by shortname';
4299 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4301 -- caller responsible for their own truncates though we try to prevent duplicates
4302 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4307 ' || quote_ident(table_name) || '
4309 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4313 WHERE owner = ANY ($2)
4314 AND name = BTRIM('||sc||')
4319 WHERE owner = ANY ($2)
4320 AND name = BTRIM('||sc||')
4323 USING org, org_list;
4325 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4330 WHERE owner = ANY ($2)
4331 AND BTRIM('||sc||') = BTRIM(name))
4334 WHERE owner = ANY ($2)
4335 AND BTRIM('||sc||') = BTRIM(name))
4340 ' || quote_ident(table_name) || '
4342 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4343 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4346 FROM asset.stat_cat_entry
4350 WHERE owner = ANY ($2)
4351 AND BTRIM('||sc||') = BTRIM(name)
4352 ) AND value = BTRIM('||sce||')
4353 AND owner = ANY ($2)
4357 FROM asset_stat_cat_entry
4361 WHERE owner = ANY ($2)
4362 AND BTRIM('||sc||') = BTRIM(name)
4363 ) AND value = BTRIM('||sce||')
4364 AND owner = ANY ($2)
4367 USING org, org_list;
4369 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4371 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4373 table_schema ALIAS FOR $1;
4374 table_name ALIAS FOR $2;
4375 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4376 org_shortname ALIAS FOR $4;
4384 SELECT 'desired_sc' || field_suffix INTO sc;
4385 SELECT 'desired_sce' || field_suffix INTO sce;
4386 EXECUTE 'SELECT EXISTS (
4388 FROM information_schema.columns
4389 WHERE table_schema = $1
4391 and column_name = $3
4392 )' INTO proceed USING table_schema, table_name, sc;
4394 RAISE EXCEPTION 'Missing column %', sc;
4396 EXECUTE 'SELECT EXISTS (
4398 FROM information_schema.columns
4399 WHERE table_schema = $1
4401 and column_name = $3
4402 )' INTO proceed USING table_schema, table_name, sce;
4404 RAISE EXCEPTION 'Missing column %', sce;
4407 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4409 RAISE EXCEPTION 'Cannot find org by shortname';
4412 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4414 EXECUTE 'ALTER TABLE '
4415 || quote_ident(table_name)
4416 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4417 EXECUTE 'ALTER TABLE '
4418 || quote_ident(table_name)
4419 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4420 EXECUTE 'ALTER TABLE '
4421 || quote_ident(table_name)
4422 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4423 EXECUTE 'ALTER TABLE '
4424 || quote_ident(table_name)
4425 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4428 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4430 x_sc' || field_suffix || ' = id
4432 (SELECT id, name, owner FROM asset_stat_cat
4433 UNION SELECT id, name, owner FROM asset.stat_cat) u
4435 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4436 AND u.owner = ANY ($1);'
4439 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4441 x_sce' || field_suffix || ' = id
4443 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4444 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4446 u.stat_cat = x_sc' || field_suffix || '
4447 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4448 AND u.owner = ANY ($1);'
4451 EXECUTE 'SELECT migration_tools.assert(
4452 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4453 ''Cannot find a desired stat cat'',
4454 ''Found all desired stat cats''
4457 EXECUTE 'SELECT migration_tools.assert(
4458 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4459 ''Cannot find a desired stat cat entry'',
4460 ''Found all desired stat cat entries''
4464 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4466 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
4467 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4474 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4476 AND table_schema = s_name
4477 AND (data_type='text' OR data_type='character varying')
4478 AND column_name like 'l_%'
4480 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4487 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
4488 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4495 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4497 AND table_schema = s_name
4498 AND (data_type='text' OR data_type='character varying')
4500 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4507 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
4508 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4515 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4517 AND table_schema = s_name
4518 AND (data_type='text' OR data_type='character varying')
4519 AND column_name like 'l_%'
4521 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
4528 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
4529 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4536 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4538 AND table_schema = s_name
4539 AND (data_type='text' OR data_type='character varying')
4541 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');