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_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
301 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
302 IF temp ilike '%MR.%' THEN
304 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
306 IF temp ilike '%MRS.%' THEN
308 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
310 IF temp ilike '%MS.%' THEN
312 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
314 IF temp ilike '%DR.%' THEN
316 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
318 IF temp ilike '%JR%' THEN
320 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
322 IF temp ilike '%JR,%' THEN
324 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
326 IF temp ilike '%SR%' THEN
328 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
330 IF temp ilike '%SR,%' THEN
332 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
334 IF temp ~ E'\\sII$' THEN
336 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
338 IF temp ~ E'\\sIII$' THEN
340 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
342 IF temp ~ E'\\sIV$' THEN
344 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
347 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
348 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
349 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
351 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
353 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
355 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
357 full_name TEXT := $1;
359 family_name TEXT := '';
360 first_given_name TEXT := '';
361 second_given_name TEXT := '';
366 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
367 IF temp ilike '%MR.%' THEN
369 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
371 IF temp ilike '%MRS.%' THEN
373 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
375 IF temp ilike '%MS.%' THEN
377 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
379 IF temp ilike '%DR.%' THEN
381 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
383 IF temp ilike '%JR.%' THEN
385 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
387 IF temp ilike '%JR,%' THEN
389 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
391 IF temp ilike '%SR.%' THEN
393 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
395 IF temp ilike '%SR,%' THEN
397 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
399 IF temp like '%III%' THEN
401 temp := REGEXP_REPLACE( temp, E'III', '' );
403 IF temp like '%II%' THEN
405 temp := REGEXP_REPLACE( temp, E'II', '' );
407 IF temp like '%IV%' THEN
409 temp := REGEXP_REPLACE( temp, E'IV', '' );
412 temp := REGEXP_REPLACE( temp, '\(\)', '');
413 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
414 family_name := REGEXP_REPLACE( family_name, ',', '' );
415 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
416 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
417 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
418 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
420 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
422 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
424 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
426 full_name TEXT := $1;
428 family_name TEXT := '';
429 first_given_name TEXT := '';
430 second_given_name TEXT := '';
434 temp := BTRIM(full_name);
435 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
436 --IF temp ~ '^\S{2,}\.' THEN
437 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
438 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
440 --IF temp ~ '\S{2,}\.$' THEN
441 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
442 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
444 IF temp ilike '%MR.%' THEN
446 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
448 IF temp ilike '%MRS.%' THEN
450 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
452 IF temp ilike '%MS.%' THEN
454 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
456 IF temp ilike '%DR.%' THEN
458 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
460 IF temp ilike '%JR.%' THEN
462 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
464 IF temp ilike '%JR,%' THEN
466 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
468 IF temp ilike '%SR.%' THEN
470 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
472 IF temp ilike '%SR,%' THEN
474 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
476 IF temp like '%III%' THEN
478 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
480 IF temp like '%II%' THEN
482 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
486 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
487 temp := BTRIM(REPLACE( temp, family_name, '' ));
488 family_name := REPLACE( family_name, ',', '' );
490 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
491 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
493 first_given_name := temp;
494 second_given_name := '';
497 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
498 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
499 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
500 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
502 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
503 second_given_name := temp;
504 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
508 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
510 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
512 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
514 city_state_zip TEXT := $1;
519 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;
520 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
521 IF city_state_zip ~ ',' THEN
522 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
523 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
525 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
526 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
527 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
529 IF city_state_zip ~ E'^\\S+$' THEN
530 city := city_state_zip;
533 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
534 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
538 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
540 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
542 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
543 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
545 fullstring TEXT := $1;
555 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
556 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
559 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
561 IF fullstring ~ ',' THEN
562 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
563 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
565 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
566 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
567 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
569 IF fullstring ~ E'^\\S+$' THEN
570 scratch1 := fullstring;
573 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
574 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
579 IF scratch1 ~ '[\$]' THEN
580 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
581 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
583 IF scratch1 ~ '\s' THEN
584 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
585 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
592 IF scratch2 ~ '^\d' THEN
593 address1 := scratch2;
596 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
597 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
601 TRIM(BOTH ' ' FROM address1)
602 ,TRIM(BOTH ' ' FROM address2)
603 ,TRIM(BOTH ' ' FROM city)
604 ,TRIM(BOTH ' ' FROM state)
605 ,TRIM(BOTH ' ' FROM zip)
608 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
610 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
613 use Geo::StreetAddress::US;
615 my $a = Geo::StreetAddress::US->parse_location($address);
618 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
619 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
624 $$ LANGUAGE PLPERLU STABLE;
626 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
630 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
631 IF o::BIGINT < t THEN
638 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
640 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
642 migration_schema ALIAS FOR $1;
646 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
651 $$ LANGUAGE PLPGSQL STRICT STABLE;
653 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
655 migration_schema ALIAS FOR $1;
659 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
664 $$ LANGUAGE PLPGSQL STRICT STABLE;
666 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
668 migration_schema ALIAS FOR $1;
672 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
677 $$ LANGUAGE PLPGSQL STRICT STABLE;
679 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
681 migration_schema ALIAS FOR $1;
685 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
690 $$ LANGUAGE PLPGSQL STRICT STABLE;
692 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
694 migration_schema ALIAS FOR $1;
696 patron_table ALIAS FOR $2;
697 default_patron_profile ALIAS FOR $3;
700 sql_where1 TEXT := '';
701 sql_where2 TEXT := '';
702 sql_where3 TEXT := '';
705 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
707 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
709 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
710 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);
711 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);
712 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);
713 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,'') || ';';
714 --RAISE INFO 'sql = %', sql;
715 PERFORM migration_tools.exec( $1, sql );
717 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
719 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
721 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
724 $$ LANGUAGE PLPGSQL STRICT STABLE;
726 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
728 migration_schema ALIAS FOR $1;
730 item_table ALIAS FOR $2;
733 sql_where1 TEXT := '';
734 sql_where2 TEXT := '';
735 sql_where3 TEXT := '';
738 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
740 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
742 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 ';
743 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);
744 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);
745 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);
746 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,'') || ';';
747 --RAISE INFO 'sql = %', sql;
748 PERFORM migration_tools.exec( $1, sql );
751 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
753 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
756 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
758 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
760 migration_schema ALIAS FOR $1;
761 base_copy_location_map TEXT;
762 item_table ALIAS FOR $2;
765 sql_where1 TEXT := '';
766 sql_where2 TEXT := '';
767 sql_where3 TEXT := '';
770 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
772 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
774 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
775 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);
776 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);
777 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);
778 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,'') || ';';
779 --RAISE INFO 'sql = %', sql;
780 PERFORM migration_tools.exec( $1, sql );
783 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
785 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
788 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
790 -- 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
791 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
793 migration_schema ALIAS FOR $1;
795 circ_table ALIAS FOR $2;
796 item_table ALIAS FOR $3;
797 patron_table ALIAS FOR $4;
800 sql_where1 TEXT := '';
801 sql_where2 TEXT := '';
802 sql_where3 TEXT := '';
803 sql_where4 TEXT := '';
806 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
808 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
810 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 ';
811 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);
812 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);
813 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);
814 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);
815 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,'') || ';';
816 --RAISE INFO 'sql = %', sql;
817 PERFORM migration_tools.exec( $1, sql );
820 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
822 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
825 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
828 -- $barcode source barcode
829 -- $prefix prefix to add to barcode, NULL = add no prefix
830 -- $maxlen maximum length of barcode; default to 14 if left NULL
831 -- $pad padding string to apply to left of source barcode before adding
832 -- prefix and suffix; set to NULL or '' if no padding is desired
833 -- $suffix suffix to add to barcode, NULL = add no suffix
835 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
836 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
838 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
839 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
842 return unless defined $barcode;
844 $prefix = '' unless defined $prefix;
846 $pad = '0' unless defined $pad;
847 $suffix = '' unless defined $suffix;
849 # bail out if adding prefix and suffix would bring new barcode over max length
850 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
852 my $new_barcode = $barcode;
854 my $pad_length = $maxlen - length($prefix) - length($suffix);
855 if (length($barcode) < $pad_length) {
856 # assuming we always want padding on the left
857 # also assuming that it is possible to have the pad string be longer than 1 character
858 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
862 # bail out if adding prefix and suffix would bring new barcode over max length
863 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
865 return "$prefix$new_barcode$suffix";
866 $$ LANGUAGE PLPERLU STABLE;
868 -- remove previous version of this function
869 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
871 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
873 attempt_value ALIAS FOR $1;
874 datatype ALIAS FOR $2;
876 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
877 RETURN attempt_value;
879 WHEN OTHERS THEN RETURN NULL;
881 $$ LANGUAGE PLPGSQL STRICT STABLE;
883 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
885 attempt_value ALIAS FOR $1;
886 fail_value ALIAS FOR $2;
890 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
897 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
902 $$ LANGUAGE PLPGSQL STRICT STABLE;
904 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
906 attempt_value ALIAS FOR $1;
907 fail_value ALIAS FOR $2;
911 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
918 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
923 $$ LANGUAGE PLPGSQL STRICT STABLE;
925 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
927 attempt_value ALIAS FOR $1;
928 fail_value ALIAS FOR $2;
932 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
939 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
944 $$ LANGUAGE PLPGSQL STRICT STABLE;
946 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
948 attempt_value ALIAS FOR $1;
949 fail_value ALIAS FOR $2;
952 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
957 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
962 $$ LANGUAGE PLPGSQL STRICT STABLE;
964 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
966 attempt_value ALIAS FOR $1;
967 fail_value ALIAS FOR $2;
971 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
978 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
983 $$ LANGUAGE PLPGSQL STRICT STABLE;
985 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
987 attempt_value ALIAS FOR $1;
988 fail_value ALIAS FOR $2;
992 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
999 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1004 $$ LANGUAGE PLPGSQL STRICT STABLE;
1006 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1008 attempt_value ALIAS FOR $1;
1009 fail_value ALIAS FOR $2;
1010 output NUMERIC(8,2);
1012 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1013 RAISE EXCEPTION 'too many digits';
1016 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;'
1023 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1028 $$ LANGUAGE PLPGSQL STRICT STABLE;
1030 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1032 attempt_value ALIAS FOR $1;
1033 fail_value ALIAS FOR $2;
1034 output NUMERIC(6,2);
1036 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1037 RAISE EXCEPTION 'too many digits';
1040 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;'
1047 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1052 $$ LANGUAGE PLPGSQL STRICT STABLE;
1054 -- add_codabar_checkdigit
1055 -- $barcode source barcode
1057 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1058 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1059 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1060 -- input string does not meet those requirements, it is returned unchanged.
1062 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1063 my $barcode = shift;
1065 return $barcode if $barcode !~ /^\d{13,14}$/;
1066 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1067 my @digits = split //, $barcode;
1069 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1070 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1071 my $remainder = $total % 10;
1072 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1073 return $barcode . $checkdigit;
1074 $$ LANGUAGE PLPERLU STRICT STABLE;
1076 -- add_code39mod43_checkdigit
1077 -- $barcode source barcode
1079 -- If the source string is 13 or 14 characters long and contains only valid
1080 -- Code 39 mod 43 characters, adds or replaces the 14th
1081 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1082 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1083 -- input string does not meet those requirements, it is returned unchanged.
1085 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1086 my $barcode = shift;
1088 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1089 $barcode = substr($barcode, 0, 13); # ignore 14th character
1091 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1092 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1095 $total += $nums{$_} foreach split(//, $barcode);
1096 my $remainder = $total % 43;
1097 my $checkdigit = $valid_chars[$remainder];
1098 return $barcode . $checkdigit;
1099 $$ LANGUAGE PLPERLU STRICT STABLE;
1101 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1104 areacode TEXT := $2;
1107 n_digits INTEGER := 0;
1110 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1111 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1112 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1113 IF n_digits = 7 AND areacode <> '' THEN
1114 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1115 output := (areacode || '-' || temp);
1122 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1124 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1125 my ($marcxml, $pos, $value) = @_;
1128 use MARC::File::XML;
1132 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1133 my $leader = $marc->leader();
1134 substr($leader, $pos, 1) = $value;
1135 $marc->leader($leader);
1136 $xml = $marc->as_xml_record;
1137 $xml =~ s/^<\?.+?\?>$//mo;
1139 $xml =~ s/>\s+</></sgo;
1142 $$ LANGUAGE PLPERLU STABLE;
1144 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1145 my ($marcxml, $pos, $value) = @_;
1148 use MARC::File::XML;
1152 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1153 my $f008 = $marc->field('008');
1156 my $field = $f008->data();
1157 substr($field, $pos, 1) = $value;
1158 $f008->update($field);
1159 $xml = $marc->as_xml_record;
1160 $xml =~ s/^<\?.+?\?>$//mo;
1162 $xml =~ s/>\s+</></sgo;
1166 $$ LANGUAGE PLPERLU STABLE;
1169 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1171 profile ALIAS FOR $1;
1173 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1175 $$ LANGUAGE PLPGSQL STRICT STABLE;
1178 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1180 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1182 $$ LANGUAGE PLPGSQL STRICT STABLE;
1185 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1187 my ($marcxml, $tags) = @_;
1190 use MARC::File::XML;
1195 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1196 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1198 my @incumbents = ();
1200 foreach my $field ( $marc->fields() ) {
1201 push @incumbents, $field->as_formatted();
1204 foreach $field ( $to_insert->fields() ) {
1205 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1206 $marc->insert_fields_ordered( ($field) );
1210 $xml = $marc->as_xml_record;
1211 $xml =~ s/^<\?.+?\?>$//mo;
1213 $xml =~ s/>\s+</></sgo;
1218 $$ LANGUAGE PLPERLU STABLE;
1220 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1224 -- First make sure the circ matrix is loaded and the circulations
1225 -- have been staged to the extent possible (but at the very least
1226 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1227 -- circ modifiers must also be in place.
1229 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1237 this_duration_rule INT;
1239 this_max_fine_rule INT;
1240 rcd config.rule_circ_duration%ROWTYPE;
1241 rrf config.rule_recurring_fine%ROWTYPE;
1242 rmf config.rule_max_fine%ROWTYPE;
1249 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1251 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1253 -- Fetch the correct rules for this circulation
1260 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1263 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1264 INTO circ_lib, target_copy, usr, is_renewal ;
1266 INTO this_duration_rule,
1270 recurring_fine_rule,
1272 FROM action.item_user_circ_test(
1278 SELECT INTO rcd * FROM config.rule_circ_duration
1279 WHERE id = this_duration_rule;
1280 SELECT INTO rrf * FROM config.rule_recurring_fine
1281 WHERE id = this_fine_rule;
1282 SELECT INTO rmf * FROM config.rule_max_fine
1283 WHERE id = this_max_fine_rule;
1285 -- Apply the rules to this circulation
1286 EXECUTE ('UPDATE ' || tablename || ' c
1288 duration_rule = rcd.name,
1289 recurring_fine_rule = rrf.name,
1290 max_fine_rule = rmf.name,
1291 duration = rcd.normal,
1292 recurring_fine = rrf.normal,
1295 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1298 renewal_remaining = rcd.max_renewals
1300 config.rule_circ_duration rcd,
1301 config.rule_recurring_fine rrf,
1302 config.rule_max_fine rmf,
1305 rcd.id = ' || this_duration_rule || ' AND
1306 rrf.id = ' || this_fine_rule || ' AND
1307 rmf.id = ' || this_max_fine_rule || ' AND
1308 ac.id = c.target_copy AND
1309 c.id = ' || circ || ';');
1311 -- Keep track of where we are in the process
1313 IF (n % 100 = 0) THEN
1314 RAISE INFO '%', n || ' of ' || n_circs
1315 || ' (' || (100*n/n_circs) || '%) circs updated.';
1323 $$ LANGUAGE plpgsql;
1325 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1329 -- First make sure the circ matrix is loaded and the circulations
1330 -- have been staged to the extent possible (but at the very least
1331 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1332 -- circ modifiers must also be in place.
1334 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1342 this_duration_rule INT;
1344 this_max_fine_rule INT;
1345 rcd config.rule_circ_duration%ROWTYPE;
1346 rrf config.rule_recurring_fine%ROWTYPE;
1347 rmf config.rule_max_fine%ROWTYPE;
1354 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1356 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1358 -- Fetch the correct rules for this circulation
1365 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1368 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1369 INTO circ_lib, target_copy, usr, is_renewal ;
1371 INTO this_duration_rule,
1377 FROM action.find_circ_matrix_matchpoint(
1383 SELECT INTO rcd * FROM config.rule_circ_duration
1384 WHERE id = this_duration_rule;
1385 SELECT INTO rrf * FROM config.rule_recurring_fine
1386 WHERE id = this_fine_rule;
1387 SELECT INTO rmf * FROM config.rule_max_fine
1388 WHERE id = this_max_fine_rule;
1390 -- Apply the rules to this circulation
1391 EXECUTE ('UPDATE ' || tablename || ' c
1393 duration_rule = rcd.name,
1394 recuring_fine_rule = rrf.name,
1395 max_fine_rule = rmf.name,
1396 duration = rcd.normal,
1397 recuring_fine = rrf.normal,
1400 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1403 renewal_remaining = rcd.max_renewals
1405 config.rule_circ_duration rcd,
1406 config.rule_recuring_fine rrf,
1407 config.rule_max_fine rmf,
1410 rcd.id = ' || this_duration_rule || ' AND
1411 rrf.id = ' || this_fine_rule || ' AND
1412 rmf.id = ' || this_max_fine_rule || ' AND
1413 ac.id = c.target_copy AND
1414 c.id = ' || circ || ';');
1416 -- Keep track of where we are in the process
1418 IF (n % 100 = 0) THEN
1419 RAISE INFO '%', n || ' of ' || n_circs
1420 || ' (' || (100*n/n_circs) || '%) circs updated.';
1428 $$ LANGUAGE plpgsql;
1430 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1434 -- First make sure the circ matrix is loaded and the circulations
1435 -- have been staged to the extent possible (but at the very least
1436 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1437 -- circ modifiers must also be in place.
1439 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1447 this_duration_rule INT;
1449 this_max_fine_rule INT;
1450 rcd config.rule_circ_duration%ROWTYPE;
1451 rrf config.rule_recurring_fine%ROWTYPE;
1452 rmf config.rule_max_fine%ROWTYPE;
1459 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1461 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1463 -- Fetch the correct rules for this circulation
1470 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1473 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1474 INTO circ_lib, target_copy, usr, is_renewal ;
1476 INTO this_duration_rule,
1479 (matchpoint).duration_rule,
1480 (matchpoint).recurring_fine_rule,
1481 (matchpoint).max_fine_rule
1482 FROM action.find_circ_matrix_matchpoint(
1488 SELECT INTO rcd * FROM config.rule_circ_duration
1489 WHERE id = this_duration_rule;
1490 SELECT INTO rrf * FROM config.rule_recurring_fine
1491 WHERE id = this_fine_rule;
1492 SELECT INTO rmf * FROM config.rule_max_fine
1493 WHERE id = this_max_fine_rule;
1495 -- Apply the rules to this circulation
1496 EXECUTE ('UPDATE ' || tablename || ' c
1498 duration_rule = rcd.name,
1499 recurring_fine_rule = rrf.name,
1500 max_fine_rule = rmf.name,
1501 duration = rcd.normal,
1502 recurring_fine = rrf.normal,
1505 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1508 renewal_remaining = rcd.max_renewals,
1509 grace_period = rrf.grace_period
1511 config.rule_circ_duration rcd,
1512 config.rule_recurring_fine rrf,
1513 config.rule_max_fine rmf,
1516 rcd.id = ' || this_duration_rule || ' AND
1517 rrf.id = ' || this_fine_rule || ' AND
1518 rmf.id = ' || this_max_fine_rule || ' AND
1519 ac.id = c.target_copy AND
1520 c.id = ' || circ || ';');
1522 -- Keep track of where we are in the process
1524 IF (n % 100 = 0) THEN
1525 RAISE INFO '%', n || ' of ' || n_circs
1526 || ' (' || (100*n/n_circs) || '%) circs updated.';
1534 $$ LANGUAGE plpgsql;
1536 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1540 -- First make sure the circ matrix is loaded and the circulations
1541 -- have been staged to the extent possible (but at the very least
1542 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1543 -- circ modifiers must also be in place.
1545 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1553 this_duration_rule INT;
1555 this_max_fine_rule INT;
1556 rcd config.rule_circ_duration%ROWTYPE;
1557 rrf config.rule_recurring_fine%ROWTYPE;
1558 rmf config.rule_max_fine%ROWTYPE;
1564 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1566 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1568 -- Fetch the correct rules for this circulation
1575 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1578 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1579 INTO circ_lib, target_copy, usr, is_renewal ;
1581 INTO this_duration_rule,
1584 (matchpoint).duration_rule,
1585 (matchpoint).recurring_fine_rule,
1586 (matchpoint).max_fine_rule
1587 FROM action.find_circ_matrix_matchpoint(
1593 SELECT INTO rcd * FROM config.rule_circ_duration
1594 WHERE id = this_duration_rule;
1595 SELECT INTO rrf * FROM config.rule_recurring_fine
1596 WHERE id = this_fine_rule;
1597 SELECT INTO rmf * FROM config.rule_max_fine
1598 WHERE id = this_max_fine_rule;
1600 -- Apply the rules to this circulation
1601 EXECUTE ('UPDATE ' || tablename || ' c
1603 duration_rule = rcd.name,
1604 recurring_fine_rule = rrf.name,
1605 max_fine_rule = rmf.name,
1606 duration = rcd.normal,
1607 recurring_fine = rrf.normal,
1610 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1613 renewal_remaining = rcd.max_renewals,
1614 grace_period = rrf.grace_period
1616 config.rule_circ_duration rcd,
1617 config.rule_recurring_fine rrf,
1618 config.rule_max_fine rmf,
1621 rcd.id = ' || this_duration_rule || ' AND
1622 rrf.id = ' || this_fine_rule || ' AND
1623 rmf.id = ' || this_max_fine_rule || ' AND
1624 ac.id = c.target_copy AND
1625 c.id = ' || circ || ';');
1627 -- Keep track of where we are in the process
1629 IF (n % 100 = 0) THEN
1630 RAISE INFO '%', n || ' of ' || n_circs
1631 || ' (' || (100*n/n_circs) || '%) circs updated.';
1639 $$ LANGUAGE plpgsql;
1644 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1646 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1647 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1649 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1650 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1653 c TEXT := schemaname || '.asset_copy_legacy';
1654 sc TEXT := schemaname || '.asset_stat_cat';
1655 sce TEXT := schemaname || '.asset_stat_cat_entry';
1656 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1662 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1664 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1666 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1667 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1668 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1675 $$ LANGUAGE plpgsql;
1677 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1679 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1680 -- This will assign standing penalties as needed.
1688 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1690 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1692 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1702 $$ LANGUAGE plpgsql;
1705 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1708 INSERT INTO metabib.metarecord (fingerprint, master_record)
1709 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1710 FROM biblio.record_entry b
1712 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)
1713 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1714 ORDER BY b.fingerprint, b.quality DESC;
1715 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1717 FROM biblio.record_entry r
1718 JOIN metabib.metarecord m USING (fingerprint)
1719 WHERE NOT r.deleted;
1722 $$ LANGUAGE plpgsql;
1725 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1728 INSERT INTO metabib.metarecord (fingerprint, master_record)
1729 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1730 FROM biblio.record_entry b
1732 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)
1733 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1734 ORDER BY b.fingerprint, b.quality DESC;
1735 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1737 FROM biblio.record_entry r
1738 JOIN metabib.metarecord m USING (fingerprint)
1740 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);
1743 $$ LANGUAGE plpgsql;
1746 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1748 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1749 -- Then SELECT migration_tools.create_cards('m_foo');
1752 u TEXT := schemaname || '.actor_usr_legacy';
1753 c TEXT := schemaname || '.actor_card';
1757 EXECUTE ('DELETE FROM ' || c || ';');
1758 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1759 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1765 $$ LANGUAGE plpgsql;
1768 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1770 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1772 my ($marcxml, $shortname) = @_;
1775 use MARC::File::XML;
1780 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1782 foreach my $field ( $marc->field('856') ) {
1783 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1784 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1785 $field->add_subfields( '9' => $shortname );
1786 $field->update( ind2 => '0');
1790 $xml = $marc->as_xml_record;
1791 $xml =~ s/^<\?.+?\?>$//mo;
1793 $xml =~ s/>\s+</></sgo;
1798 $$ LANGUAGE PLPERLU STABLE;
1800 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1802 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1804 my ($marcxml, $shortname) = @_;
1807 use MARC::File::XML;
1812 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1814 foreach my $field ( $marc->field('856') ) {
1815 if ( ! $field->as_string('9') ) {
1816 $field->add_subfields( '9' => $shortname );
1820 $xml = $marc->as_xml_record;
1821 $xml =~ s/^<\?.+?\?>$//mo;
1823 $xml =~ s/>\s+</></sgo;
1828 $$ LANGUAGE PLPERLU STABLE;
1831 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1843 -- Bail out if asked to change the label to ##URI##
1844 IF new_label = '##URI##' THEN
1848 -- Gather information
1849 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1850 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1851 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1853 -- Bail out if the label already is ##URI##
1854 IF old_label = '##URI##' THEN
1858 -- Bail out if the call number label is already correct
1859 IF new_volume = old_volume THEN
1863 -- Check whether we already have a destination volume available
1864 SELECT id INTO new_volume FROM asset.call_number
1867 owning_lib = owner AND
1868 label = new_label AND
1871 -- Create destination volume if needed
1873 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1874 VALUES (1, 1, bib, owner, new_label, cn_class);
1875 SELECT id INTO new_volume FROM asset.call_number
1878 owning_lib = owner AND
1879 label = new_label AND
1883 -- Move copy to destination
1884 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1886 -- Delete source volume if it is now empty
1887 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1889 DELETE FROM asset.call_number WHERE id = old_volume;
1894 $$ LANGUAGE plpgsql;
1896 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1901 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1905 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1906 $zipdata{$zip} = [$city, $state, $county];
1909 if (defined $zipdata{$input}) {
1910 my ($city, $state, $county) = @{$zipdata{$input}};
1911 return [$city, $state, $county];
1912 } elsif (defined $zipdata{substr $input, 0, 5}) {
1913 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1914 return [$city, $state, $county];
1916 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1919 $$ LANGUAGE PLPERLU STABLE;
1921 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1928 errors_found BOOLEAN;
1930 parent_shortname TEXT;
1936 type_parent_depth INT;
1941 errors_found := FALSE;
1943 -- Checking actor.org_unit_type
1945 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1947 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1948 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1950 IF type_parent IS NOT NULL THEN
1952 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1954 IF type_depth - type_parent_depth <> 1 THEN
1955 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1956 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1957 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1958 ou_type_name, type_depth, parent_type, type_parent_depth;
1959 errors_found := TRUE;
1967 -- Checking actor.org_unit
1969 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1971 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1972 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;
1973 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;
1974 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1975 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1976 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;
1977 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;
1979 IF ou_parent IS NOT NULL THEN
1981 IF (org_unit_depth - parent_depth <> 1) OR (
1982 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1984 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1985 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1986 errors_found := TRUE;
1993 IF NOT errors_found THEN
1994 RAISE INFO 'No errors found.';
2001 $$ LANGUAGE plpgsql;
2004 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2008 DELETE FROM asset.opac_visible_copies;
2010 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2012 cp.id, cp.circ_lib, cn.record
2015 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2016 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2017 JOIN asset.copy_location cl ON (cp.location = cl.id)
2018 JOIN config.copy_status cs ON (cp.status = cs.id)
2019 JOIN biblio.record_entry b ON (cn.record = b.id)
2028 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2032 $$ LANGUAGE plpgsql;
2035 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2041 old_owning_lib INTEGER;
2047 -- Gather information
2048 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2049 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2050 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2052 -- Bail out if the new_owning_lib is not the ID of an org_unit
2053 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2055 '% is not a valid actor.org_unit ID; no change made.',
2060 -- Bail out discreetly if the owning_lib is already correct
2061 IF new_owning_lib = old_owning_lib THEN
2065 -- Check whether we already have a destination volume available
2066 SELECT id INTO new_volume FROM asset.call_number
2069 owning_lib = new_owning_lib AND
2070 label = old_label AND
2073 -- Create destination volume if needed
2075 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2076 VALUES (1, 1, bib, new_owning_lib, old_label);
2077 SELECT id INTO new_volume FROM asset.call_number
2080 owning_lib = new_owning_lib AND
2081 label = old_label AND
2085 -- Move copy to destination
2086 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2088 -- Delete source volume if it is now empty
2089 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2091 DELETE FROM asset.call_number WHERE id = old_volume;
2096 $$ LANGUAGE plpgsql;
2099 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2101 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2104 new_owning_lib INTEGER;
2108 -- Parse the new_owner as an org unit ID or shortname
2109 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2110 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2111 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2112 ELSIF new_owner ~ E'^[0-9]+$' THEN
2113 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2116 E'You don\'t need to put the actor.org_unit ID in quotes; '
2117 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2118 new_owning_lib := new_owner::INTEGER;
2119 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2123 '% is not a valid actor.org_unit shortname or ID; no change made.',
2130 $$ LANGUAGE plpgsql;
2132 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2135 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2138 MARC::Charset->assume_unicode(1);
2143 my $r = MARC::Record->new_from_xml( $xml );
2144 my $output_xml = $r->as_xml_record();
2152 $func$ LANGUAGE PLPERLU;
2153 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2155 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2157 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2158 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2159 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2160 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2161 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2162 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2163 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2164 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2165 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2166 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2167 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2168 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2169 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2170 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2171 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2172 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2173 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2174 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2175 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2176 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2177 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2178 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2179 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2180 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2181 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2182 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2184 $FUNC$ LANGUAGE PLPGSQL;
2186 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2188 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2189 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2190 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2191 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2192 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2193 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2194 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2196 -- import any new circ rules
2197 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2198 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2199 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2200 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2202 -- and permission groups
2203 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2206 $FUNC$ LANGUAGE PLPGSQL;
2209 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$
2218 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2219 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2220 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2221 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2222 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2223 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2224 FOR name IN EXECUTE loopq LOOP
2225 EXECUTE existsq INTO ct USING name;
2227 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2228 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2229 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2230 EXECUTE copyst USING name;
2234 $FUNC$ LANGUAGE PLPGSQL;
2236 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2242 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2245 MARC::Charset->assume_unicode(1);
2247 my $target_xml = shift;
2248 my $source_xml = shift;
2254 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2258 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2263 my $source_id = $source->subfield('901', 'c');
2264 $source_id = $source->subfield('903', 'a') unless $source_id;
2265 my $target_id = $target->subfield('901', 'c');
2266 $target_id = $target->subfield('903', 'a') unless $target_id;
2268 my %existing_fields;
2269 foreach my $tag (@$tags) {
2270 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2271 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2272 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2274 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2278 my $xml = $target->as_xml_record;
2279 $xml =~ s/^<\?.+?\?>$//mo;
2281 $xml =~ s/>\s+</></sgo;
2285 $func$ LANGUAGE PLPERLU;
2286 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.';
2288 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2294 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2297 my $in_tags = shift;
2298 my $in_values = shift;
2300 # hack-and-slash parsing of array-passed-as-string;
2301 # this can go away once everybody is running Postgres 9.1+
2302 my $csv = Text::CSV->new({binary => 1});
2305 my $status = $csv->parse($in_tags);
2306 my $tags = [ $csv->fields() ];
2307 $in_values =~ s/^{//;
2308 $in_values =~ s/}$//;
2309 $status = $csv->parse($in_values);
2310 my $values = [ $csv->fields() ];
2312 my $marc = MARC::Record->new();
2314 $marc->leader('00000nam a22000007 4500');
2315 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2317 foreach my $i (0..$#$tags) {
2319 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2322 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2323 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2325 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2329 my $xml = $marc->as_xml_record;
2330 $xml =~ s/^<\?.+?\?>$//mo;
2332 $xml =~ s/>\s+</></sgo;
2336 $func$ LANGUAGE PLPERLU;
2337 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2338 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2339 The second argument is an array of text containing the values to plug into each field.
2340 If the value for a given field is NULL or the empty string, it is not inserted.
2343 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2345 my ($marcxml, $tag, $pos, $value) = @_;
2348 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2352 MARC::Charset->assume_unicode(1);
2354 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2355 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2356 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2357 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2361 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2363 foreach my $field ($marc->field($tag)) {
2364 $field->update("ind$pos" => $value);
2366 $xml = $marc->as_xml_record;
2367 $xml =~ s/^<\?.+?\?>$//mo;
2369 $xml =~ s/>\s+</></sgo;
2373 $func$ LANGUAGE PLPERLU;
2375 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2376 The first argument is a MARCXML string.
2377 The second argument is a MARC tag.
2378 The third argument is the indicator position, either 1 or 2.
2379 The fourth argument is the character to set the indicator value to.
2380 All occurences of the specified field will be changed.
2381 The function returns the revised MARCXML string.$$;
2383 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2388 first_name TEXT DEFAULT '',
2389 last_name TEXT DEFAULT ''
2390 ) RETURNS VOID AS $func$
2392 RAISE NOTICE '%', org ;
2393 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2394 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2395 FROM actor.org_unit aou, permission.grp_tree pgt
2396 WHERE aou.shortname = org
2397 AND pgt.name = perm_group;
2402 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2403 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2405 target_event_def ALIAS FOR $1;
2408 DROP TABLE IF EXISTS new_atevdefs;
2409 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2410 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2411 INSERT INTO action_trigger.event_definition (
2432 ,name || ' (clone of '||target_event_def||')'
2448 action_trigger.event_definition
2450 id = target_event_def
2452 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2453 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2454 INSERT INTO action_trigger.environment (
2460 currval('action_trigger.event_definition_id_seq')
2465 action_trigger.environment
2467 event_def = target_event_def
2469 INSERT INTO action_trigger.event_params (
2474 currval('action_trigger.event_definition_id_seq')
2478 action_trigger.event_params
2480 event_def = target_event_def
2483 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);
2485 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2487 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2488 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2490 target_event_def ALIAS FOR $1;
2492 new_interval ALIAS FOR $3;
2494 DROP TABLE IF EXISTS new_atevdefs;
2495 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2496 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2497 INSERT INTO action_trigger.event_definition (
2518 ,name || ' (clone of '||target_event_def||')'
2534 action_trigger.event_definition
2536 id = target_event_def
2538 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2539 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2540 INSERT INTO action_trigger.environment (
2546 currval('action_trigger.event_definition_id_seq')
2551 action_trigger.environment
2553 event_def = target_event_def
2555 INSERT INTO action_trigger.event_params (
2560 currval('action_trigger.event_definition_id_seq')
2564 action_trigger.event_params
2566 event_def = target_event_def
2569 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);
2571 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2573 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2577 use MARC::File::XML;
2582 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2583 $field = $marc->leader();
2586 $$ LANGUAGE PLPERLU STABLE;
2588 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2589 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2592 use MARC::File::XML;
2597 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2598 $field = $marc->field($tag);
2600 return $field->as_string($subfield,$delimiter);
2601 $$ LANGUAGE PLPERLU STABLE;
2603 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2604 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2607 use MARC::File::XML;
2612 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2613 @fields = $marc->field($tag);
2616 foreach my $field (@fields) {
2617 push @texts, $field->as_string($subfield,$delimiter);
2620 $$ LANGUAGE PLPERLU STABLE;
2622 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2623 SELECT action.find_hold_matrix_matchpoint(
2624 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2625 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2626 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2627 (SELECT usr FROM action.hold_request WHERE id = $1),
2628 (SELECT requestor FROM action.hold_request WHERE id = $1)
2632 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2633 SELECT action.hold_request_permit_test(
2634 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2635 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2636 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2637 (SELECT usr FROM action.hold_request WHERE id = $1),
2638 (SELECT requestor FROM action.hold_request WHERE id = $1)
2642 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2643 SELECT action.find_circ_matrix_matchpoint(
2644 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2645 (SELECT target_copy FROM action.circulation WHERE id = $1),
2646 (SELECT usr FROM action.circulation WHERE id = $1),
2648 NULLIF(phone_renewal,false),
2649 NULLIF(desk_renewal,false),
2650 NULLIF(opac_renewal,false),
2652 ) FROM action.circulation WHERE id = $1
2657 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2662 RAISE EXCEPTION 'assertion';
2665 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2667 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2673 RAISE EXCEPTION '%', msg;
2676 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2678 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2681 fail_msg ALIAS FOR $2;
2682 success_msg ALIAS FOR $3;
2685 RAISE EXCEPTION '%', fail_msg;
2689 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2691 -- push bib sequence and return starting value for reserved range
2692 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2694 bib_count ALIAS FOR $1;
2697 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2699 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2704 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2706 -- set a new salted password
2708 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2710 usr_id ALIAS FOR $1;
2711 plain_passwd ALIAS FOR $2;
2716 SELECT actor.create_salt('main') INTO plain_salt;
2718 SELECT MD5(plain_passwd) INTO md5_passwd;
2720 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2725 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2728 -- convenience functions for handling copy_location maps
2730 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2732 table_schema ALIAS FOR $1;
2733 table_name ALIAS FOR $2;
2734 org_shortname ALIAS FOR $3;
2735 org_range ALIAS FOR $4;
2741 EXECUTE 'SELECT EXISTS (
2743 FROM information_schema.columns
2744 WHERE table_schema = $1
2746 and column_name = ''desired_shelf''
2747 )' INTO proceed USING table_schema, table_name;
2749 RAISE EXCEPTION 'Missing column desired_shelf';
2752 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2754 RAISE EXCEPTION 'Cannot find org by shortname';
2757 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2759 EXECUTE 'ALTER TABLE '
2760 || quote_ident(table_name)
2761 || ' DROP COLUMN IF EXISTS x_shelf';
2762 EXECUTE 'ALTER TABLE '
2763 || quote_ident(table_name)
2764 || ' ADD COLUMN x_shelf INTEGER';
2766 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2767 || ' SET x_shelf = id FROM asset_copy_location b'
2768 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2769 || ' AND b.owning_lib = $1'
2770 || ' AND NOT b.deleted'
2773 FOREACH o IN ARRAY org_list LOOP
2774 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2775 || ' SET x_shelf = id FROM asset.copy_location b'
2776 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2777 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2778 || ' AND NOT b.deleted'
2782 EXECUTE 'SELECT migration_tools.assert(
2783 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2784 ''Cannot find a desired location'',
2785 ''Found all desired locations''
2789 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2791 -- convenience functions for handling circmod maps
2793 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2795 table_schema ALIAS FOR $1;
2796 table_name ALIAS FOR $2;
2799 EXECUTE 'SELECT EXISTS (
2801 FROM information_schema.columns
2802 WHERE table_schema = $1
2804 and column_name = ''desired_circmod''
2805 )' INTO proceed USING table_schema, table_name;
2807 RAISE EXCEPTION 'Missing column desired_circmod';
2810 EXECUTE 'ALTER TABLE '
2811 || quote_ident(table_name)
2812 || ' DROP COLUMN IF EXISTS x_circmod';
2813 EXECUTE 'ALTER TABLE '
2814 || quote_ident(table_name)
2815 || ' ADD COLUMN x_circmod TEXT';
2817 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2818 || ' SET x_circmod = code FROM config.circ_modifier b'
2819 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
2821 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2822 || ' SET x_circmod = code FROM config.circ_modifier b'
2823 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2824 || ' AND x_circmod IS NULL';
2826 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2827 || ' SET x_circmod = code FROM config.circ_modifier b'
2828 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2829 || ' AND x_circmod IS NULL';
2831 EXECUTE 'SELECT migration_tools.assert(
2832 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2833 ''Cannot find a desired circulation modifier'',
2834 ''Found all desired circulation modifiers''
2838 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2840 -- convenience functions for handling item status maps
2842 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
2844 table_schema ALIAS FOR $1;
2845 table_name ALIAS FOR $2;
2848 EXECUTE 'SELECT EXISTS (
2850 FROM information_schema.columns
2851 WHERE table_schema = $1
2853 and column_name = ''desired_status''
2854 )' INTO proceed USING table_schema, table_name;
2856 RAISE EXCEPTION 'Missing column desired_status';
2859 EXECUTE 'ALTER TABLE '
2860 || quote_ident(table_name)
2861 || ' DROP COLUMN IF EXISTS x_status';
2862 EXECUTE 'ALTER TABLE '
2863 || quote_ident(table_name)
2864 || ' ADD COLUMN x_status INTEGER';
2866 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2867 || ' SET x_status = id FROM config.copy_status b'
2868 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2870 EXECUTE 'SELECT migration_tools.assert(
2871 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2872 ''Cannot find a desired copy status'',
2873 ''Found all desired copy statuses''
2877 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2879 -- convenience functions for handling org maps
2881 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2883 table_schema ALIAS FOR $1;
2884 table_name ALIAS FOR $2;
2887 EXECUTE 'SELECT EXISTS (
2889 FROM information_schema.columns
2890 WHERE table_schema = $1
2892 and column_name = ''desired_org''
2893 )' INTO proceed USING table_schema, table_name;
2895 RAISE EXCEPTION 'Missing column desired_org';
2898 EXECUTE 'ALTER TABLE '
2899 || quote_ident(table_name)
2900 || ' DROP COLUMN IF EXISTS x_org';
2901 EXECUTE 'ALTER TABLE '
2902 || quote_ident(table_name)
2903 || ' ADD COLUMN x_org INTEGER';
2905 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2906 || ' SET x_org = id FROM actor.org_unit b'
2907 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2909 EXECUTE 'SELECT migration_tools.assert(
2910 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2911 ''Cannot find a desired org unit'',
2912 ''Found all desired org units''
2916 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2918 -- convenience function for handling desired_not_migrate
2920 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2922 table_schema ALIAS FOR $1;
2923 table_name ALIAS FOR $2;
2926 EXECUTE 'SELECT EXISTS (
2928 FROM information_schema.columns
2929 WHERE table_schema = $1
2931 and column_name = ''desired_not_migrate''
2932 )' INTO proceed USING table_schema, table_name;
2934 RAISE EXCEPTION 'Missing column desired_not_migrate';
2937 EXECUTE 'ALTER TABLE '
2938 || quote_ident(table_name)
2939 || ' DROP COLUMN IF EXISTS x_migrate';
2940 EXECUTE 'ALTER TABLE '
2941 || quote_ident(table_name)
2942 || ' ADD COLUMN x_migrate BOOLEAN';
2944 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2945 || ' SET x_migrate = CASE'
2946 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2947 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
2948 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2949 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
2950 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2953 EXECUTE 'SELECT migration_tools.assert(
2954 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
2955 ''Not all desired_not_migrate values understood'',
2956 ''All desired_not_migrate values understood''
2960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2962 -- convenience function for handling desired_profile
2964 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
2966 table_schema ALIAS FOR $1;
2967 table_name ALIAS FOR $2;
2970 EXECUTE 'SELECT EXISTS (
2972 FROM information_schema.columns
2973 WHERE table_schema = $1
2975 and column_name = ''desired_profile''
2976 )' INTO proceed USING table_schema, table_name;
2978 RAISE EXCEPTION 'Missing column desired_profile';
2981 EXECUTE 'ALTER TABLE '
2982 || quote_ident(table_name)
2983 || ' DROP COLUMN IF EXISTS x_profile';
2984 EXECUTE 'ALTER TABLE '
2985 || quote_ident(table_name)
2986 || ' ADD COLUMN x_profile INTEGER';
2988 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2989 || ' SET x_profile = id FROM permission.grp_tree b'
2990 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
2992 EXECUTE 'SELECT migration_tools.assert(
2993 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
2994 ''Cannot find a desired profile'',
2995 ''Found all desired profiles''
2999 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3001 -- convenience function for handling desired actor stat cats
3003 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3005 table_schema ALIAS FOR $1;
3006 table_name ALIAS FOR $2;
3007 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3008 org_shortname ALIAS FOR $4;
3016 SELECT 'desired_sc' || field_suffix INTO sc;
3017 SELECT 'desired_sce' || field_suffix INTO sce;
3019 EXECUTE 'SELECT EXISTS (
3021 FROM information_schema.columns
3022 WHERE table_schema = $1
3024 and column_name = $3
3025 )' INTO proceed USING table_schema, table_name, sc;
3027 RAISE EXCEPTION 'Missing column %', sc;
3029 EXECUTE 'SELECT EXISTS (
3031 FROM information_schema.columns
3032 WHERE table_schema = $1
3034 and column_name = $3
3035 )' INTO proceed USING table_schema, table_name, sce;
3037 RAISE EXCEPTION 'Missing column %', sce;
3040 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3042 RAISE EXCEPTION 'Cannot find org by shortname';
3044 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3046 -- caller responsible for their own truncates though we try to prevent duplicates
3047 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3052 ' || quote_ident(table_name) || '
3054 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3058 WHERE owner = ANY ($2)
3059 AND name = BTRIM('||sc||')
3064 WHERE owner = ANY ($2)
3065 AND name = BTRIM('||sc||')
3068 USING org, org_list;
3070 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3075 WHERE owner = ANY ($2)
3076 AND BTRIM('||sc||') = BTRIM(name))
3079 WHERE owner = ANY ($2)
3080 AND BTRIM('||sc||') = BTRIM(name))
3085 ' || quote_ident(table_name) || '
3087 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3088 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3091 FROM actor.stat_cat_entry
3095 WHERE owner = ANY ($2)
3096 AND BTRIM('||sc||') = BTRIM(name)
3097 ) AND value = BTRIM('||sce||')
3101 FROM actor_stat_cat_entry
3105 WHERE owner = ANY ($2)
3106 AND BTRIM('||sc||') = BTRIM(name)
3107 ) AND value = BTRIM('||sce||')
3110 USING org, org_list;
3112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3114 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3116 table_schema ALIAS FOR $1;
3117 table_name ALIAS FOR $2;
3118 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3119 org_shortname ALIAS FOR $4;
3127 SELECT 'desired_sc' || field_suffix INTO sc;
3128 SELECT 'desired_sce' || field_suffix INTO sce;
3129 EXECUTE 'SELECT EXISTS (
3131 FROM information_schema.columns
3132 WHERE table_schema = $1
3134 and column_name = $3
3135 )' INTO proceed USING table_schema, table_name, sc;
3137 RAISE EXCEPTION 'Missing column %', sc;
3139 EXECUTE 'SELECT EXISTS (
3141 FROM information_schema.columns
3142 WHERE table_schema = $1
3144 and column_name = $3
3145 )' INTO proceed USING table_schema, table_name, sce;
3147 RAISE EXCEPTION 'Missing column %', sce;
3150 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3152 RAISE EXCEPTION 'Cannot find org by shortname';
3155 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3157 EXECUTE 'ALTER TABLE '
3158 || quote_ident(table_name)
3159 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3160 EXECUTE 'ALTER TABLE '
3161 || quote_ident(table_name)
3162 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3163 EXECUTE 'ALTER TABLE '
3164 || quote_ident(table_name)
3165 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3166 EXECUTE 'ALTER TABLE '
3167 || quote_ident(table_name)
3168 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3171 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3173 x_sc' || field_suffix || ' = id
3175 (SELECT id, name, owner FROM actor_stat_cat
3176 UNION SELECT id, name, owner FROM actor.stat_cat) u
3178 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3179 AND u.owner = ANY ($1);'
3182 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3184 x_sce' || field_suffix || ' = id
3186 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3187 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3189 u.stat_cat = x_sc' || field_suffix || '
3190 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3191 AND u.owner = ANY ($1);'
3194 EXECUTE 'SELECT migration_tools.assert(
3195 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3196 ''Cannot find a desired stat cat'',
3197 ''Found all desired stat cats''
3200 EXECUTE 'SELECT migration_tools.assert(
3201 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3202 ''Cannot find a desired stat cat entry'',
3203 ''Found all desired stat cat entries''
3207 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3209 -- convenience functions for adding shelving locations
3210 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3211 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3217 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3220 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3221 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3222 IF return_id IS NOT NULL THEN
3230 $$ LANGUAGE plpgsql;
3232 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3234 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3235 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3241 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3244 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3246 SELECT INTO return_id id FROM
3247 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3248 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3249 IF return_id IS NOT NULL THEN
3257 $$ LANGUAGE plpgsql;
3259 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3260 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3261 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3269 use MARC::File::XML (BinaryEncoding => 'utf8');
3271 binmode(STDERR, ':bytes');
3272 binmode(STDOUT, ':utf8');
3273 binmode(STDERR, ':utf8');
3275 my $marc_xml = shift;
3276 my $matching_u_text = shift;
3277 my $new_9_to_set = shift;
3279 $marc_xml =~ s/(<leader>.........)./${1}a/;
3282 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3285 #elog("could not parse $bibid: $@\n");
3286 import MARC::File::XML (BinaryEncoding => 'utf8');
3290 my @uris = $marc_xml->field('856');
3291 return unless @uris;
3293 foreach my $field (@uris) {
3294 my $sfu = $field->subfield('u');
3295 my $ind2 = $field->indicator('2');
3296 if (!defined $ind2) { next; }
3297 if ($ind2 ne '0') { next; }
3298 if (!defined $sfu) { next; }
3299 if ($sfu =~ m/$matching_u_text/) {
3300 $field->add_subfields( '9' => $new_9_to_set );
3305 return $marc_xml->as_xml_record();
3309 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3310 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3319 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3321 SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3324 new_xml = '$_$' || new_xml || '$_$';
3326 IF new_xml != source_xml THEN
3327 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3334 $BODY$ LANGUAGE plpgsql;
3336 -- convenience function for linking to the item staging table
3338 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3340 table_schema ALIAS FOR $1;
3341 table_name ALIAS FOR $2;
3342 foreign_column_name ALIAS FOR $3;
3343 main_column_name ALIAS FOR $4;
3344 btrim_desired ALIAS FOR $5;
3347 EXECUTE 'SELECT EXISTS (
3349 FROM information_schema.columns
3350 WHERE table_schema = $1
3352 and column_name = $3
3353 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3355 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3358 EXECUTE 'SELECT EXISTS (
3360 FROM information_schema.columns
3361 WHERE table_schema = $1
3362 AND table_name = ''asset_copy_legacy''
3363 and column_name = $2
3364 )' INTO proceed USING table_schema, main_column_name;
3366 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3369 EXECUTE 'ALTER TABLE '
3370 || quote_ident(table_name)
3371 || ' DROP COLUMN IF EXISTS x_item';
3372 EXECUTE 'ALTER TABLE '
3373 || quote_ident(table_name)
3374 || ' ADD COLUMN x_item BIGINT';
3376 IF btrim_desired THEN
3377 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3378 || ' SET x_item = b.id FROM asset_copy_legacy b'
3379 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3380 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3382 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3383 || ' SET x_item = b.id FROM asset_copy_legacy b'
3384 || ' WHERE a.' || quote_ident(foreign_column_name)
3385 || ' = b.' || quote_ident(main_column_name);
3388 --EXECUTE 'SELECT migration_tools.assert(
3389 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3390 -- ''Cannot link every barcode'',
3391 -- ''Every barcode linked''
3395 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3397 -- convenience function for linking to the user staging table
3399 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3401 table_schema ALIAS FOR $1;
3402 table_name ALIAS FOR $2;
3403 foreign_column_name ALIAS FOR $3;
3404 main_column_name ALIAS FOR $4;
3405 btrim_desired ALIAS FOR $5;
3408 EXECUTE 'SELECT EXISTS (
3410 FROM information_schema.columns
3411 WHERE table_schema = $1
3413 and column_name = $3
3414 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3416 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3419 EXECUTE 'SELECT EXISTS (
3421 FROM information_schema.columns
3422 WHERE table_schema = $1
3423 AND table_name = ''actor_usr_legacy''
3424 and column_name = $2
3425 )' INTO proceed USING table_schema, main_column_name;
3427 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3430 EXECUTE 'ALTER TABLE '
3431 || quote_ident(table_name)
3432 || ' DROP COLUMN IF EXISTS x_user';
3433 EXECUTE 'ALTER TABLE '
3434 || quote_ident(table_name)
3435 || ' ADD COLUMN x_user INTEGER';
3437 IF btrim_desired THEN
3438 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3439 || ' SET x_user = b.id FROM actor_usr_legacy b'
3440 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3441 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3443 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3444 || ' SET x_user = b.id FROM actor_usr_legacy b'
3445 || ' WHERE a.' || quote_ident(foreign_column_name)
3446 || ' = b.' || quote_ident(main_column_name);
3449 --EXECUTE 'SELECT migration_tools.assert(
3450 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3451 -- ''Cannot link every barcode'',
3452 -- ''Every barcode linked''
3456 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3458 -- convenience function for linking two tables
3459 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3460 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3462 table_schema ALIAS FOR $1;
3463 table_a ALIAS FOR $2;
3464 column_a ALIAS FOR $3;
3465 table_b ALIAS FOR $4;
3466 column_b ALIAS FOR $5;
3467 column_x ALIAS FOR $6;
3468 btrim_desired ALIAS FOR $7;
3471 EXECUTE 'SELECT EXISTS (
3473 FROM information_schema.columns
3474 WHERE table_schema = $1
3476 and column_name = $3
3477 )' INTO proceed USING table_schema, table_a, column_a;
3479 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3482 EXECUTE 'SELECT EXISTS (
3484 FROM information_schema.columns
3485 WHERE table_schema = $1
3487 and column_name = $3
3488 )' INTO proceed USING table_schema, table_b, column_b;
3490 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3493 EXECUTE 'ALTER TABLE '
3494 || quote_ident(table_b)
3495 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3496 EXECUTE 'ALTER TABLE '
3497 || quote_ident(table_b)
3498 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3500 IF btrim_desired THEN
3501 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3502 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3503 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3504 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3506 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3507 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3508 || ' WHERE a.' || quote_ident(column_a)
3509 || ' = b.' || quote_ident(column_b);
3513 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3515 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3516 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3517 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3519 table_schema ALIAS FOR $1;
3520 table_a ALIAS FOR $2;
3521 column_a ALIAS FOR $3;
3522 table_b ALIAS FOR $4;
3523 column_b ALIAS FOR $5;
3524 column_w ALIAS FOR $6;
3525 column_x ALIAS FOR $7;
3526 btrim_desired ALIAS FOR $8;
3529 EXECUTE 'SELECT EXISTS (
3531 FROM information_schema.columns
3532 WHERE table_schema = $1
3534 and column_name = $3
3535 )' INTO proceed USING table_schema, table_a, column_a;
3537 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3540 EXECUTE 'SELECT EXISTS (
3542 FROM information_schema.columns
3543 WHERE table_schema = $1
3545 and column_name = $3
3546 )' INTO proceed USING table_schema, table_b, column_b;
3548 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3551 EXECUTE 'ALTER TABLE '
3552 || quote_ident(table_b)
3553 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3554 EXECUTE 'ALTER TABLE '
3555 || quote_ident(table_b)
3556 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3558 IF btrim_desired THEN
3559 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3560 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3561 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3562 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3564 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3565 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3566 || ' WHERE a.' || quote_ident(column_a)
3567 || ' = b.' || quote_ident(column_b);
3571 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3573 -- convenience function for handling desired asset stat cats
3575 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3577 table_schema ALIAS FOR $1;
3578 table_name ALIAS FOR $2;
3579 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3580 org_shortname ALIAS FOR $4;
3588 SELECT 'desired_sc' || field_suffix INTO sc;
3589 SELECT 'desired_sce' || field_suffix INTO sce;
3591 EXECUTE 'SELECT EXISTS (
3593 FROM information_schema.columns
3594 WHERE table_schema = $1
3596 and column_name = $3
3597 )' INTO proceed USING table_schema, table_name, sc;
3599 RAISE EXCEPTION 'Missing column %', sc;
3601 EXECUTE 'SELECT EXISTS (
3603 FROM information_schema.columns
3604 WHERE table_schema = $1
3606 and column_name = $3
3607 )' INTO proceed USING table_schema, table_name, sce;
3609 RAISE EXCEPTION 'Missing column %', sce;
3612 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3614 RAISE EXCEPTION 'Cannot find org by shortname';
3616 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3618 -- caller responsible for their own truncates though we try to prevent duplicates
3619 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3624 ' || quote_ident(table_name) || '
3626 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3630 WHERE owner = ANY ($2)
3631 AND name = BTRIM('||sc||')
3636 WHERE owner = ANY ($2)
3637 AND name = BTRIM('||sc||')
3640 USING org, org_list;
3642 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3647 WHERE owner = ANY ($2)
3648 AND BTRIM('||sc||') = BTRIM(name))
3651 WHERE owner = ANY ($2)
3652 AND BTRIM('||sc||') = BTRIM(name))
3657 ' || quote_ident(table_name) || '
3659 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3660 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3663 FROM asset.stat_cat_entry
3667 WHERE owner = ANY ($2)
3668 AND BTRIM('||sc||') = BTRIM(name)
3669 ) AND value = BTRIM('||sce||')
3673 FROM asset_stat_cat_entry
3677 WHERE owner = ANY ($2)
3678 AND BTRIM('||sc||') = BTRIM(name)
3679 ) AND value = BTRIM('||sce||')
3682 USING org, org_list;
3684 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3686 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3688 table_schema ALIAS FOR $1;
3689 table_name ALIAS FOR $2;
3690 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3691 org_shortname ALIAS FOR $4;
3699 SELECT 'desired_sc' || field_suffix INTO sc;
3700 SELECT 'desired_sce' || field_suffix INTO sce;
3701 EXECUTE 'SELECT EXISTS (
3703 FROM information_schema.columns
3704 WHERE table_schema = $1
3706 and column_name = $3
3707 )' INTO proceed USING table_schema, table_name, sc;
3709 RAISE EXCEPTION 'Missing column %', sc;
3711 EXECUTE 'SELECT EXISTS (
3713 FROM information_schema.columns
3714 WHERE table_schema = $1
3716 and column_name = $3
3717 )' INTO proceed USING table_schema, table_name, sce;
3719 RAISE EXCEPTION 'Missing column %', sce;
3722 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3724 RAISE EXCEPTION 'Cannot find org by shortname';
3727 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3729 EXECUTE 'ALTER TABLE '
3730 || quote_ident(table_name)
3731 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3732 EXECUTE 'ALTER TABLE '
3733 || quote_ident(table_name)
3734 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3735 EXECUTE 'ALTER TABLE '
3736 || quote_ident(table_name)
3737 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3738 EXECUTE 'ALTER TABLE '
3739 || quote_ident(table_name)
3740 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3743 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3745 x_sc' || field_suffix || ' = id
3747 (SELECT id, name, owner FROM asset_stat_cat
3748 UNION SELECT id, name, owner FROM asset.stat_cat) u
3750 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3751 AND u.owner = ANY ($1);'
3754 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3756 x_sce' || field_suffix || ' = id
3758 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3759 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3761 u.stat_cat = x_sc' || field_suffix || '
3762 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3763 AND u.owner = ANY ($1);'
3766 EXECUTE 'SELECT migration_tools.assert(
3767 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3768 ''Cannot find a desired stat cat'',
3769 ''Found all desired stat cats''
3772 EXECUTE 'SELECT migration_tools.assert(
3773 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3774 ''Cannot find a desired stat cat entry'',
3775 ''Found all desired stat cat entries''
3779 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3781 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
3782 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3789 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3791 AND table_schema = s_name
3792 AND (data_type='text' OR data_type='character varying')
3793 AND column_name like 'l_%'
3795 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3802 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
3803 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3810 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3812 AND table_schema = s_name
3813 AND (data_type='text' OR data_type='character varying')
3814 AND column_name like 'l_%'
3816 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');