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 -- add_mod16_checkdigit
1102 -- $barcode source barcode
1104 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1106 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1107 my $barcode = shift;
1109 my @digits = split //, $barcode;
1111 foreach $digit (@digits) {
1112 if ($digit =~ /[0-9]/) { $total += $digit;
1113 } elsif ($digit eq '-') { $total += 10;
1114 } elsif ($digit eq '$') { $total += 11;
1115 } elsif ($digit eq ':') { $total += 12;
1116 } elsif ($digit eq '/') { $total += 13;
1117 } elsif ($digit eq '.') { $total += 14;
1118 } elsif ($digit eq '+') { $total += 15;
1119 } elsif ($digit eq 'A') { $total += 16;
1120 } elsif ($digit eq 'B') { $total += 17;
1121 } elsif ($digit eq 'C') { $total += 18;
1122 } elsif ($digit eq 'D') { $total += 19;
1123 } else { die "invalid digit <$digit>";
1126 my $remainder = $total % 16;
1127 my $difference = 16 - $remainder;
1129 if ($difference < 10) { $checkdigit = $difference;
1130 } elsif ($difference == 10) { $checkdigit = '-';
1131 } elsif ($difference == 11) { $checkdigit = '$';
1132 } elsif ($difference == 12) { $checkdigit = ':';
1133 } elsif ($difference == 13) { $checkdigit = '/';
1134 } elsif ($difference == 14) { $checkdigit = '.';
1135 } elsif ($difference == 15) { $checkdigit = '+';
1136 } elsif ($difference == 16) { $checkdigit = 'A';
1137 } elsif ($difference == 17) { $checkdigit = 'B';
1138 } elsif ($difference == 18) { $checkdigit = 'C';
1139 } elsif ($difference == 19) { $checkdigit = 'D';
1140 } else { die "error calculating checkdigit";
1143 return $barcode . $checkdigit;
1144 $$ LANGUAGE PLPERLU STRICT STABLE;
1146 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1149 areacode TEXT := $2;
1152 n_digits INTEGER := 0;
1155 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1156 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1157 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1158 IF n_digits = 7 AND areacode <> '' THEN
1159 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1160 output := (areacode || '-' || temp);
1167 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1169 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1170 my ($marcxml, $pos, $value) = @_;
1173 use MARC::File::XML;
1177 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1178 my $leader = $marc->leader();
1179 substr($leader, $pos, 1) = $value;
1180 $marc->leader($leader);
1181 $xml = $marc->as_xml_record;
1182 $xml =~ s/^<\?.+?\?>$//mo;
1184 $xml =~ s/>\s+</></sgo;
1187 $$ LANGUAGE PLPERLU STABLE;
1189 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1190 my ($marcxml, $pos, $value) = @_;
1193 use MARC::File::XML;
1197 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1198 my $f008 = $marc->field('008');
1201 my $field = $f008->data();
1202 substr($field, $pos, 1) = $value;
1203 $f008->update($field);
1204 $xml = $marc->as_xml_record;
1205 $xml =~ s/^<\?.+?\?>$//mo;
1207 $xml =~ s/>\s+</></sgo;
1211 $$ LANGUAGE PLPERLU STABLE;
1214 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1216 profile ALIAS FOR $1;
1218 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1220 $$ LANGUAGE PLPGSQL STRICT STABLE;
1223 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1225 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1227 $$ LANGUAGE PLPGSQL STRICT STABLE;
1230 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1232 my ($marcxml, $tags) = @_;
1235 use MARC::File::XML;
1240 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1241 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1243 my @incumbents = ();
1245 foreach my $field ( $marc->fields() ) {
1246 push @incumbents, $field->as_formatted();
1249 foreach $field ( $to_insert->fields() ) {
1250 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1251 $marc->insert_fields_ordered( ($field) );
1255 $xml = $marc->as_xml_record;
1256 $xml =~ s/^<\?.+?\?>$//mo;
1258 $xml =~ s/>\s+</></sgo;
1263 $$ LANGUAGE PLPERLU STABLE;
1265 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1269 -- First make sure the circ matrix is loaded and the circulations
1270 -- have been staged to the extent possible (but at the very least
1271 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1272 -- circ modifiers must also be in place.
1274 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1282 this_duration_rule INT;
1284 this_max_fine_rule INT;
1285 rcd config.rule_circ_duration%ROWTYPE;
1286 rrf config.rule_recurring_fine%ROWTYPE;
1287 rmf config.rule_max_fine%ROWTYPE;
1294 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1296 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1298 -- Fetch the correct rules for this circulation
1305 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1308 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1309 INTO circ_lib, target_copy, usr, is_renewal ;
1311 INTO this_duration_rule,
1315 recurring_fine_rule,
1317 FROM action.item_user_circ_test(
1323 SELECT INTO rcd * FROM config.rule_circ_duration
1324 WHERE id = this_duration_rule;
1325 SELECT INTO rrf * FROM config.rule_recurring_fine
1326 WHERE id = this_fine_rule;
1327 SELECT INTO rmf * FROM config.rule_max_fine
1328 WHERE id = this_max_fine_rule;
1330 -- Apply the rules to this circulation
1331 EXECUTE ('UPDATE ' || tablename || ' c
1333 duration_rule = rcd.name,
1334 recurring_fine_rule = rrf.name,
1335 max_fine_rule = rmf.name,
1336 duration = rcd.normal,
1337 recurring_fine = rrf.normal,
1340 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1343 renewal_remaining = rcd.max_renewals
1345 config.rule_circ_duration rcd,
1346 config.rule_recurring_fine rrf,
1347 config.rule_max_fine rmf,
1350 rcd.id = ' || this_duration_rule || ' AND
1351 rrf.id = ' || this_fine_rule || ' AND
1352 rmf.id = ' || this_max_fine_rule || ' AND
1353 ac.id = c.target_copy AND
1354 c.id = ' || circ || ';');
1356 -- Keep track of where we are in the process
1358 IF (n % 100 = 0) THEN
1359 RAISE INFO '%', n || ' of ' || n_circs
1360 || ' (' || (100*n/n_circs) || '%) circs updated.';
1368 $$ LANGUAGE plpgsql;
1370 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1374 -- First make sure the circ matrix is loaded and the circulations
1375 -- have been staged to the extent possible (but at the very least
1376 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1377 -- circ modifiers must also be in place.
1379 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1387 this_duration_rule INT;
1389 this_max_fine_rule INT;
1390 rcd config.rule_circ_duration%ROWTYPE;
1391 rrf config.rule_recurring_fine%ROWTYPE;
1392 rmf config.rule_max_fine%ROWTYPE;
1399 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1401 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1403 -- Fetch the correct rules for this circulation
1410 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1413 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1414 INTO circ_lib, target_copy, usr, is_renewal ;
1416 INTO this_duration_rule,
1422 FROM action.find_circ_matrix_matchpoint(
1428 SELECT INTO rcd * FROM config.rule_circ_duration
1429 WHERE id = this_duration_rule;
1430 SELECT INTO rrf * FROM config.rule_recurring_fine
1431 WHERE id = this_fine_rule;
1432 SELECT INTO rmf * FROM config.rule_max_fine
1433 WHERE id = this_max_fine_rule;
1435 -- Apply the rules to this circulation
1436 EXECUTE ('UPDATE ' || tablename || ' c
1438 duration_rule = rcd.name,
1439 recuring_fine_rule = rrf.name,
1440 max_fine_rule = rmf.name,
1441 duration = rcd.normal,
1442 recuring_fine = rrf.normal,
1445 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1448 renewal_remaining = rcd.max_renewals
1450 config.rule_circ_duration rcd,
1451 config.rule_recuring_fine rrf,
1452 config.rule_max_fine rmf,
1455 rcd.id = ' || this_duration_rule || ' AND
1456 rrf.id = ' || this_fine_rule || ' AND
1457 rmf.id = ' || this_max_fine_rule || ' AND
1458 ac.id = c.target_copy AND
1459 c.id = ' || circ || ';');
1461 -- Keep track of where we are in the process
1463 IF (n % 100 = 0) THEN
1464 RAISE INFO '%', n || ' of ' || n_circs
1465 || ' (' || (100*n/n_circs) || '%) circs updated.';
1473 $$ LANGUAGE plpgsql;
1475 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1479 -- First make sure the circ matrix is loaded and the circulations
1480 -- have been staged to the extent possible (but at the very least
1481 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1482 -- circ modifiers must also be in place.
1484 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1492 this_duration_rule INT;
1494 this_max_fine_rule INT;
1495 rcd config.rule_circ_duration%ROWTYPE;
1496 rrf config.rule_recurring_fine%ROWTYPE;
1497 rmf config.rule_max_fine%ROWTYPE;
1504 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1506 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1508 -- Fetch the correct rules for this circulation
1515 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1518 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1519 INTO circ_lib, target_copy, usr, is_renewal ;
1521 INTO this_duration_rule,
1524 (matchpoint).duration_rule,
1525 (matchpoint).recurring_fine_rule,
1526 (matchpoint).max_fine_rule
1527 FROM action.find_circ_matrix_matchpoint(
1533 SELECT INTO rcd * FROM config.rule_circ_duration
1534 WHERE id = this_duration_rule;
1535 SELECT INTO rrf * FROM config.rule_recurring_fine
1536 WHERE id = this_fine_rule;
1537 SELECT INTO rmf * FROM config.rule_max_fine
1538 WHERE id = this_max_fine_rule;
1540 -- Apply the rules to this circulation
1541 EXECUTE ('UPDATE ' || tablename || ' c
1543 duration_rule = rcd.name,
1544 recurring_fine_rule = rrf.name,
1545 max_fine_rule = rmf.name,
1546 duration = rcd.normal,
1547 recurring_fine = rrf.normal,
1550 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1553 renewal_remaining = rcd.max_renewals,
1554 grace_period = rrf.grace_period
1556 config.rule_circ_duration rcd,
1557 config.rule_recurring_fine rrf,
1558 config.rule_max_fine rmf,
1561 rcd.id = ' || this_duration_rule || ' AND
1562 rrf.id = ' || this_fine_rule || ' AND
1563 rmf.id = ' || this_max_fine_rule || ' AND
1564 ac.id = c.target_copy AND
1565 c.id = ' || circ || ';');
1567 -- Keep track of where we are in the process
1569 IF (n % 100 = 0) THEN
1570 RAISE INFO '%', n || ' of ' || n_circs
1571 || ' (' || (100*n/n_circs) || '%) circs updated.';
1579 $$ LANGUAGE plpgsql;
1581 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1585 -- First make sure the circ matrix is loaded and the circulations
1586 -- have been staged to the extent possible (but at the very least
1587 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1588 -- circ modifiers must also be in place.
1590 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1598 this_duration_rule INT;
1600 this_max_fine_rule INT;
1601 rcd config.rule_circ_duration%ROWTYPE;
1602 rrf config.rule_recurring_fine%ROWTYPE;
1603 rmf config.rule_max_fine%ROWTYPE;
1609 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1611 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1613 -- Fetch the correct rules for this circulation
1620 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1623 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1624 INTO circ_lib, target_copy, usr, is_renewal ;
1626 INTO this_duration_rule,
1629 (matchpoint).duration_rule,
1630 (matchpoint).recurring_fine_rule,
1631 (matchpoint).max_fine_rule
1632 FROM action.find_circ_matrix_matchpoint(
1638 SELECT INTO rcd * FROM config.rule_circ_duration
1639 WHERE id = this_duration_rule;
1640 SELECT INTO rrf * FROM config.rule_recurring_fine
1641 WHERE id = this_fine_rule;
1642 SELECT INTO rmf * FROM config.rule_max_fine
1643 WHERE id = this_max_fine_rule;
1645 -- Apply the rules to this circulation
1646 EXECUTE ('UPDATE ' || tablename || ' c
1648 duration_rule = rcd.name,
1649 recurring_fine_rule = rrf.name,
1650 max_fine_rule = rmf.name,
1651 duration = rcd.normal,
1652 recurring_fine = rrf.normal,
1655 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1658 renewal_remaining = rcd.max_renewals,
1659 grace_period = rrf.grace_period
1661 config.rule_circ_duration rcd,
1662 config.rule_recurring_fine rrf,
1663 config.rule_max_fine rmf,
1666 rcd.id = ' || this_duration_rule || ' AND
1667 rrf.id = ' || this_fine_rule || ' AND
1668 rmf.id = ' || this_max_fine_rule || ' AND
1669 ac.id = c.target_copy AND
1670 c.id = ' || circ || ';');
1672 -- Keep track of where we are in the process
1674 IF (n % 100 = 0) THEN
1675 RAISE INFO '%', n || ' of ' || n_circs
1676 || ' (' || (100*n/n_circs) || '%) circs updated.';
1684 $$ LANGUAGE plpgsql;
1689 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1691 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1692 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1694 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1695 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1698 c TEXT := schemaname || '.asset_copy_legacy';
1699 sc TEXT := schemaname || '.asset_stat_cat';
1700 sce TEXT := schemaname || '.asset_stat_cat_entry';
1701 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1707 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1709 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1711 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1712 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1713 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1720 $$ LANGUAGE plpgsql;
1722 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1724 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1725 -- This will assign standing penalties as needed.
1733 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1735 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1737 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1747 $$ LANGUAGE plpgsql;
1750 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1753 INSERT INTO metabib.metarecord (fingerprint, master_record)
1754 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1755 FROM biblio.record_entry b
1757 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)
1758 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1759 ORDER BY b.fingerprint, b.quality DESC;
1760 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1762 FROM biblio.record_entry r
1763 JOIN metabib.metarecord m USING (fingerprint)
1764 WHERE NOT r.deleted;
1767 $$ LANGUAGE plpgsql;
1770 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1773 INSERT INTO metabib.metarecord (fingerprint, master_record)
1774 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1775 FROM biblio.record_entry b
1777 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)
1778 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1779 ORDER BY b.fingerprint, b.quality DESC;
1780 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1782 FROM biblio.record_entry r
1783 JOIN metabib.metarecord m USING (fingerprint)
1785 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);
1788 $$ LANGUAGE plpgsql;
1791 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1793 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1794 -- Then SELECT migration_tools.create_cards('m_foo');
1797 u TEXT := schemaname || '.actor_usr_legacy';
1798 c TEXT := schemaname || '.actor_card';
1802 EXECUTE ('DELETE FROM ' || c || ';');
1803 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1804 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1810 $$ LANGUAGE plpgsql;
1813 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1815 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1817 my ($marcxml, $shortname) = @_;
1820 use MARC::File::XML;
1825 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1827 foreach my $field ( $marc->field('856') ) {
1828 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1829 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1830 $field->add_subfields( '9' => $shortname );
1831 $field->update( ind2 => '0');
1835 $xml = $marc->as_xml_record;
1836 $xml =~ s/^<\?.+?\?>$//mo;
1838 $xml =~ s/>\s+</></sgo;
1843 $$ LANGUAGE PLPERLU STABLE;
1845 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1847 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1849 my ($marcxml, $shortname) = @_;
1852 use MARC::File::XML;
1857 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1859 foreach my $field ( $marc->field('856') ) {
1860 if ( ! $field->as_string('9') ) {
1861 $field->add_subfields( '9' => $shortname );
1865 $xml = $marc->as_xml_record;
1866 $xml =~ s/^<\?.+?\?>$//mo;
1868 $xml =~ s/>\s+</></sgo;
1873 $$ LANGUAGE PLPERLU STABLE;
1876 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1888 -- Bail out if asked to change the label to ##URI##
1889 IF new_label = '##URI##' THEN
1893 -- Gather information
1894 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1895 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1896 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1898 -- Bail out if the label already is ##URI##
1899 IF old_label = '##URI##' THEN
1903 -- Bail out if the call number label is already correct
1904 IF new_volume = old_volume THEN
1908 -- Check whether we already have a destination volume available
1909 SELECT id INTO new_volume FROM asset.call_number
1912 owning_lib = owner AND
1913 label = new_label AND
1916 -- Create destination volume if needed
1918 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1919 VALUES (1, 1, bib, owner, new_label, cn_class);
1920 SELECT id INTO new_volume FROM asset.call_number
1923 owning_lib = owner AND
1924 label = new_label AND
1928 -- Move copy to destination
1929 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1931 -- Delete source volume if it is now empty
1932 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1934 DELETE FROM asset.call_number WHERE id = old_volume;
1939 $$ LANGUAGE plpgsql;
1941 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1946 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1950 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1951 $zipdata{$zip} = [$city, $state, $county];
1954 if (defined $zipdata{$input}) {
1955 my ($city, $state, $county) = @{$zipdata{$input}};
1956 return [$city, $state, $county];
1957 } elsif (defined $zipdata{substr $input, 0, 5}) {
1958 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1959 return [$city, $state, $county];
1961 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1964 $$ LANGUAGE PLPERLU STABLE;
1966 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1973 errors_found BOOLEAN;
1975 parent_shortname TEXT;
1981 type_parent_depth INT;
1986 errors_found := FALSE;
1988 -- Checking actor.org_unit_type
1990 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1992 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1993 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1995 IF type_parent IS NOT NULL THEN
1997 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1999 IF type_depth - type_parent_depth <> 1 THEN
2000 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2001 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2002 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2003 ou_type_name, type_depth, parent_type, type_parent_depth;
2004 errors_found := TRUE;
2012 -- Checking actor.org_unit
2014 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2016 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2017 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;
2018 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;
2019 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2020 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2021 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;
2022 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;
2024 IF ou_parent IS NOT NULL THEN
2026 IF (org_unit_depth - parent_depth <> 1) OR (
2027 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2029 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2030 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2031 errors_found := TRUE;
2038 IF NOT errors_found THEN
2039 RAISE INFO 'No errors found.';
2046 $$ LANGUAGE plpgsql;
2049 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2053 DELETE FROM asset.opac_visible_copies;
2055 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2057 cp.id, cp.circ_lib, cn.record
2060 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2061 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2062 JOIN asset.copy_location cl ON (cp.location = cl.id)
2063 JOIN config.copy_status cs ON (cp.status = cs.id)
2064 JOIN biblio.record_entry b ON (cn.record = b.id)
2073 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2077 $$ LANGUAGE plpgsql;
2080 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2086 old_owning_lib INTEGER;
2092 -- Gather information
2093 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2094 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2095 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2097 -- Bail out if the new_owning_lib is not the ID of an org_unit
2098 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2100 '% is not a valid actor.org_unit ID; no change made.',
2105 -- Bail out discreetly if the owning_lib is already correct
2106 IF new_owning_lib = old_owning_lib THEN
2110 -- Check whether we already have a destination volume available
2111 SELECT id INTO new_volume FROM asset.call_number
2114 owning_lib = new_owning_lib AND
2115 label = old_label AND
2118 -- Create destination volume if needed
2120 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2121 VALUES (1, 1, bib, new_owning_lib, old_label);
2122 SELECT id INTO new_volume FROM asset.call_number
2125 owning_lib = new_owning_lib AND
2126 label = old_label AND
2130 -- Move copy to destination
2131 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2133 -- Delete source volume if it is now empty
2134 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2136 DELETE FROM asset.call_number WHERE id = old_volume;
2141 $$ LANGUAGE plpgsql;
2144 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2146 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2149 new_owning_lib INTEGER;
2153 -- Parse the new_owner as an org unit ID or shortname
2154 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2155 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2156 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2157 ELSIF new_owner ~ E'^[0-9]+$' THEN
2158 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2161 E'You don\'t need to put the actor.org_unit ID in quotes; '
2162 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2163 new_owning_lib := new_owner::INTEGER;
2164 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2168 '% is not a valid actor.org_unit shortname or ID; no change made.',
2175 $$ LANGUAGE plpgsql;
2177 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2180 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2183 MARC::Charset->assume_unicode(1);
2188 my $r = MARC::Record->new_from_xml( $xml );
2189 my $output_xml = $r->as_xml_record();
2197 $func$ LANGUAGE PLPERLU;
2198 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2200 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2202 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2203 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2204 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2205 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2206 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2207 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2208 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2209 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2210 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2211 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2212 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2213 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2214 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2215 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2216 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2217 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2218 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2219 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2220 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2221 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2222 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2223 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2224 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2225 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2226 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2227 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2229 $FUNC$ LANGUAGE PLPGSQL;
2231 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2233 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2234 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2235 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2236 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2237 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2238 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2239 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2241 -- import any new circ rules
2242 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2243 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2244 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2245 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2247 -- and permission groups
2248 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2251 $FUNC$ LANGUAGE PLPGSQL;
2254 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$
2263 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2264 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2265 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2266 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2267 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2268 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2269 FOR name IN EXECUTE loopq LOOP
2270 EXECUTE existsq INTO ct USING name;
2272 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2273 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2274 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2275 EXECUTE copyst USING name;
2279 $FUNC$ LANGUAGE PLPGSQL;
2281 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2287 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2290 MARC::Charset->assume_unicode(1);
2292 my $target_xml = shift;
2293 my $source_xml = shift;
2299 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2303 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2308 my $source_id = $source->subfield('901', 'c');
2309 $source_id = $source->subfield('903', 'a') unless $source_id;
2310 my $target_id = $target->subfield('901', 'c');
2311 $target_id = $target->subfield('903', 'a') unless $target_id;
2313 my %existing_fields;
2314 foreach my $tag (@$tags) {
2315 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2316 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2317 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2319 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2323 my $xml = $target->as_xml_record;
2324 $xml =~ s/^<\?.+?\?>$//mo;
2326 $xml =~ s/>\s+</></sgo;
2330 $func$ LANGUAGE PLPERLU;
2331 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.';
2333 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2339 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2342 my $in_tags = shift;
2343 my $in_values = shift;
2345 # hack-and-slash parsing of array-passed-as-string;
2346 # this can go away once everybody is running Postgres 9.1+
2347 my $csv = Text::CSV->new({binary => 1});
2350 my $status = $csv->parse($in_tags);
2351 my $tags = [ $csv->fields() ];
2352 $in_values =~ s/^{//;
2353 $in_values =~ s/}$//;
2354 $status = $csv->parse($in_values);
2355 my $values = [ $csv->fields() ];
2357 my $marc = MARC::Record->new();
2359 $marc->leader('00000nam a22000007 4500');
2360 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2362 foreach my $i (0..$#$tags) {
2364 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2367 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2368 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2370 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2374 my $xml = $marc->as_xml_record;
2375 $xml =~ s/^<\?.+?\?>$//mo;
2377 $xml =~ s/>\s+</></sgo;
2381 $func$ LANGUAGE PLPERLU;
2382 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2383 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2384 The second argument is an array of text containing the values to plug into each field.
2385 If the value for a given field is NULL or the empty string, it is not inserted.
2388 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2390 my ($marcxml, $tag, $pos, $value) = @_;
2393 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2397 MARC::Charset->assume_unicode(1);
2399 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2400 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2401 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2402 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2406 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2408 foreach my $field ($marc->field($tag)) {
2409 $field->update("ind$pos" => $value);
2411 $xml = $marc->as_xml_record;
2412 $xml =~ s/^<\?.+?\?>$//mo;
2414 $xml =~ s/>\s+</></sgo;
2418 $func$ LANGUAGE PLPERLU;
2420 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2421 The first argument is a MARCXML string.
2422 The second argument is a MARC tag.
2423 The third argument is the indicator position, either 1 or 2.
2424 The fourth argument is the character to set the indicator value to.
2425 All occurences of the specified field will be changed.
2426 The function returns the revised MARCXML string.$$;
2428 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2433 first_name TEXT DEFAULT '',
2434 last_name TEXT DEFAULT ''
2435 ) RETURNS VOID AS $func$
2437 RAISE NOTICE '%', org ;
2438 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2439 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2440 FROM actor.org_unit aou, permission.grp_tree pgt
2441 WHERE aou.shortname = org
2442 AND pgt.name = perm_group;
2447 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2448 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2450 target_event_def ALIAS FOR $1;
2453 DROP TABLE IF EXISTS new_atevdefs;
2454 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2455 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2456 INSERT INTO action_trigger.event_definition (
2477 ,name || ' (clone of '||target_event_def||')'
2493 action_trigger.event_definition
2495 id = target_event_def
2497 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2498 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2499 INSERT INTO action_trigger.environment (
2505 currval('action_trigger.event_definition_id_seq')
2510 action_trigger.environment
2512 event_def = target_event_def
2514 INSERT INTO action_trigger.event_params (
2519 currval('action_trigger.event_definition_id_seq')
2523 action_trigger.event_params
2525 event_def = target_event_def
2528 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);
2530 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2532 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2533 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2535 target_event_def ALIAS FOR $1;
2537 new_interval ALIAS FOR $3;
2539 DROP TABLE IF EXISTS new_atevdefs;
2540 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2541 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2542 INSERT INTO action_trigger.event_definition (
2563 ,name || ' (clone of '||target_event_def||')'
2579 action_trigger.event_definition
2581 id = target_event_def
2583 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2584 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2585 INSERT INTO action_trigger.environment (
2591 currval('action_trigger.event_definition_id_seq')
2596 action_trigger.environment
2598 event_def = target_event_def
2600 INSERT INTO action_trigger.event_params (
2605 currval('action_trigger.event_definition_id_seq')
2609 action_trigger.event_params
2611 event_def = target_event_def
2614 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);
2616 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2618 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2622 use MARC::File::XML;
2627 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2628 $field = $marc->leader();
2631 $$ LANGUAGE PLPERLU STABLE;
2633 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2634 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2637 use MARC::File::XML;
2642 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2643 $field = $marc->field($tag);
2645 return $field->as_string($subfield,$delimiter);
2646 $$ LANGUAGE PLPERLU STABLE;
2648 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2649 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2652 use MARC::File::XML;
2657 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2658 @fields = $marc->field($tag);
2661 foreach my $field (@fields) {
2662 push @texts, $field->as_string($subfield,$delimiter);
2665 $$ LANGUAGE PLPERLU STABLE;
2667 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2668 SELECT action.find_hold_matrix_matchpoint(
2669 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2670 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2671 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2672 (SELECT usr FROM action.hold_request WHERE id = $1),
2673 (SELECT requestor FROM action.hold_request WHERE id = $1)
2677 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2678 SELECT action.hold_request_permit_test(
2679 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2680 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2681 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2682 (SELECT usr FROM action.hold_request WHERE id = $1),
2683 (SELECT requestor FROM action.hold_request WHERE id = $1)
2687 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2688 SELECT action.find_circ_matrix_matchpoint(
2689 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2690 (SELECT target_copy FROM action.circulation WHERE id = $1),
2691 (SELECT usr FROM action.circulation WHERE id = $1),
2693 NULLIF(phone_renewal,false),
2694 NULLIF(desk_renewal,false),
2695 NULLIF(opac_renewal,false),
2697 ) FROM action.circulation WHERE id = $1
2702 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2707 RAISE EXCEPTION 'assertion';
2710 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2712 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2718 RAISE EXCEPTION '%', msg;
2721 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2723 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2726 fail_msg ALIAS FOR $2;
2727 success_msg ALIAS FOR $3;
2730 RAISE EXCEPTION '%', fail_msg;
2734 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2736 -- push bib sequence and return starting value for reserved range
2737 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2739 bib_count ALIAS FOR $1;
2742 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2744 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2749 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2751 -- set a new salted password
2753 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2755 usr_id ALIAS FOR $1;
2756 plain_passwd ALIAS FOR $2;
2761 SELECT actor.create_salt('main') INTO plain_salt;
2763 SELECT MD5(plain_passwd) INTO md5_passwd;
2765 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2770 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2773 -- convenience functions for handling copy_location maps
2775 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2777 table_schema ALIAS FOR $1;
2778 table_name ALIAS FOR $2;
2779 org_shortname ALIAS FOR $3;
2780 org_range ALIAS FOR $4;
2786 EXECUTE 'SELECT EXISTS (
2788 FROM information_schema.columns
2789 WHERE table_schema = $1
2791 and column_name = ''desired_shelf''
2792 )' INTO proceed USING table_schema, table_name;
2794 RAISE EXCEPTION 'Missing column desired_shelf';
2797 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2799 RAISE EXCEPTION 'Cannot find org by shortname';
2802 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2804 EXECUTE 'ALTER TABLE '
2805 || quote_ident(table_name)
2806 || ' DROP COLUMN IF EXISTS x_shelf';
2807 EXECUTE 'ALTER TABLE '
2808 || quote_ident(table_name)
2809 || ' ADD COLUMN x_shelf INTEGER';
2811 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2812 || ' SET x_shelf = id FROM asset_copy_location b'
2813 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2814 || ' AND b.owning_lib = $1'
2815 || ' AND NOT b.deleted'
2818 FOREACH o IN ARRAY org_list LOOP
2819 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2820 || ' SET x_shelf = id FROM asset.copy_location b'
2821 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2822 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2823 || ' AND NOT b.deleted'
2827 EXECUTE 'SELECT migration_tools.assert(
2828 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2829 ''Cannot find a desired location'',
2830 ''Found all desired locations''
2834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2836 -- convenience functions for handling circmod maps
2838 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2840 table_schema ALIAS FOR $1;
2841 table_name ALIAS FOR $2;
2844 EXECUTE 'SELECT EXISTS (
2846 FROM information_schema.columns
2847 WHERE table_schema = $1
2849 and column_name = ''desired_circmod''
2850 )' INTO proceed USING table_schema, table_name;
2852 RAISE EXCEPTION 'Missing column desired_circmod';
2855 EXECUTE 'ALTER TABLE '
2856 || quote_ident(table_name)
2857 || ' DROP COLUMN IF EXISTS x_circmod';
2858 EXECUTE 'ALTER TABLE '
2859 || quote_ident(table_name)
2860 || ' ADD COLUMN x_circmod TEXT';
2862 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2863 || ' SET x_circmod = code FROM config.circ_modifier b'
2864 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
2866 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2867 || ' SET x_circmod = code FROM config.circ_modifier b'
2868 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2869 || ' AND x_circmod IS NULL';
2871 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2872 || ' SET x_circmod = code FROM config.circ_modifier b'
2873 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2874 || ' AND x_circmod IS NULL';
2876 EXECUTE 'SELECT migration_tools.assert(
2877 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2878 ''Cannot find a desired circulation modifier'',
2879 ''Found all desired circulation modifiers''
2883 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2885 -- convenience functions for handling item status maps
2887 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
2889 table_schema ALIAS FOR $1;
2890 table_name ALIAS FOR $2;
2893 EXECUTE 'SELECT EXISTS (
2895 FROM information_schema.columns
2896 WHERE table_schema = $1
2898 and column_name = ''desired_status''
2899 )' INTO proceed USING table_schema, table_name;
2901 RAISE EXCEPTION 'Missing column desired_status';
2904 EXECUTE 'ALTER TABLE '
2905 || quote_ident(table_name)
2906 || ' DROP COLUMN IF EXISTS x_status';
2907 EXECUTE 'ALTER TABLE '
2908 || quote_ident(table_name)
2909 || ' ADD COLUMN x_status INTEGER';
2911 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2912 || ' SET x_status = id FROM config.copy_status b'
2913 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2915 EXECUTE 'SELECT migration_tools.assert(
2916 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2917 ''Cannot find a desired copy status'',
2918 ''Found all desired copy statuses''
2922 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2924 -- convenience functions for handling org maps
2926 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2928 table_schema ALIAS FOR $1;
2929 table_name ALIAS FOR $2;
2932 EXECUTE 'SELECT EXISTS (
2934 FROM information_schema.columns
2935 WHERE table_schema = $1
2937 and column_name = ''desired_org''
2938 )' INTO proceed USING table_schema, table_name;
2940 RAISE EXCEPTION 'Missing column desired_org';
2943 EXECUTE 'ALTER TABLE '
2944 || quote_ident(table_name)
2945 || ' DROP COLUMN IF EXISTS x_org';
2946 EXECUTE 'ALTER TABLE '
2947 || quote_ident(table_name)
2948 || ' ADD COLUMN x_org INTEGER';
2950 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2951 || ' SET x_org = id FROM actor.org_unit b'
2952 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2954 EXECUTE 'SELECT migration_tools.assert(
2955 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2956 ''Cannot find a desired org unit'',
2957 ''Found all desired org units''
2961 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2963 -- convenience function for handling desired_not_migrate
2965 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2967 table_schema ALIAS FOR $1;
2968 table_name ALIAS FOR $2;
2971 EXECUTE 'SELECT EXISTS (
2973 FROM information_schema.columns
2974 WHERE table_schema = $1
2976 and column_name = ''desired_not_migrate''
2977 )' INTO proceed USING table_schema, table_name;
2979 RAISE EXCEPTION 'Missing column desired_not_migrate';
2982 EXECUTE 'ALTER TABLE '
2983 || quote_ident(table_name)
2984 || ' DROP COLUMN IF EXISTS x_migrate';
2985 EXECUTE 'ALTER TABLE '
2986 || quote_ident(table_name)
2987 || ' ADD COLUMN x_migrate BOOLEAN';
2989 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2990 || ' SET x_migrate = CASE'
2991 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2992 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
2993 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2994 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
2995 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2998 EXECUTE 'SELECT migration_tools.assert(
2999 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3000 ''Not all desired_not_migrate values understood'',
3001 ''All desired_not_migrate values understood''
3005 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3007 -- convenience function for handling desired_profile
3009 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3011 table_schema ALIAS FOR $1;
3012 table_name ALIAS FOR $2;
3015 EXECUTE 'SELECT EXISTS (
3017 FROM information_schema.columns
3018 WHERE table_schema = $1
3020 and column_name = ''desired_profile''
3021 )' INTO proceed USING table_schema, table_name;
3023 RAISE EXCEPTION 'Missing column desired_profile';
3026 EXECUTE 'ALTER TABLE '
3027 || quote_ident(table_name)
3028 || ' DROP COLUMN IF EXISTS x_profile';
3029 EXECUTE 'ALTER TABLE '
3030 || quote_ident(table_name)
3031 || ' ADD COLUMN x_profile INTEGER';
3033 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3034 || ' SET x_profile = id FROM permission.grp_tree b'
3035 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3037 EXECUTE 'SELECT migration_tools.assert(
3038 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3039 ''Cannot find a desired profile'',
3040 ''Found all desired profiles''
3044 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3046 -- convenience function for handling desired actor stat cats
3048 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3050 table_schema ALIAS FOR $1;
3051 table_name ALIAS FOR $2;
3052 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3053 org_shortname ALIAS FOR $4;
3061 SELECT 'desired_sc' || field_suffix INTO sc;
3062 SELECT 'desired_sce' || field_suffix INTO sce;
3064 EXECUTE 'SELECT EXISTS (
3066 FROM information_schema.columns
3067 WHERE table_schema = $1
3069 and column_name = $3
3070 )' INTO proceed USING table_schema, table_name, sc;
3072 RAISE EXCEPTION 'Missing column %', sc;
3074 EXECUTE 'SELECT EXISTS (
3076 FROM information_schema.columns
3077 WHERE table_schema = $1
3079 and column_name = $3
3080 )' INTO proceed USING table_schema, table_name, sce;
3082 RAISE EXCEPTION 'Missing column %', sce;
3085 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3087 RAISE EXCEPTION 'Cannot find org by shortname';
3089 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3091 -- caller responsible for their own truncates though we try to prevent duplicates
3092 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3097 ' || quote_ident(table_name) || '
3099 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3103 WHERE owner = ANY ($2)
3104 AND name = BTRIM('||sc||')
3109 WHERE owner = ANY ($2)
3110 AND name = BTRIM('||sc||')
3113 USING org, org_list;
3115 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3120 WHERE owner = ANY ($2)
3121 AND BTRIM('||sc||') = BTRIM(name))
3124 WHERE owner = ANY ($2)
3125 AND BTRIM('||sc||') = BTRIM(name))
3130 ' || quote_ident(table_name) || '
3132 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3133 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3136 FROM actor.stat_cat_entry
3140 WHERE owner = ANY ($2)
3141 AND BTRIM('||sc||') = BTRIM(name)
3142 ) AND value = BTRIM('||sce||')
3146 FROM actor_stat_cat_entry
3150 WHERE owner = ANY ($2)
3151 AND BTRIM('||sc||') = BTRIM(name)
3152 ) AND value = BTRIM('||sce||')
3155 USING org, org_list;
3157 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3159 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3161 table_schema ALIAS FOR $1;
3162 table_name ALIAS FOR $2;
3163 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3164 org_shortname ALIAS FOR $4;
3172 SELECT 'desired_sc' || field_suffix INTO sc;
3173 SELECT 'desired_sce' || field_suffix INTO sce;
3174 EXECUTE 'SELECT EXISTS (
3176 FROM information_schema.columns
3177 WHERE table_schema = $1
3179 and column_name = $3
3180 )' INTO proceed USING table_schema, table_name, sc;
3182 RAISE EXCEPTION 'Missing column %', sc;
3184 EXECUTE 'SELECT EXISTS (
3186 FROM information_schema.columns
3187 WHERE table_schema = $1
3189 and column_name = $3
3190 )' INTO proceed USING table_schema, table_name, sce;
3192 RAISE EXCEPTION 'Missing column %', sce;
3195 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3197 RAISE EXCEPTION 'Cannot find org by shortname';
3200 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3202 EXECUTE 'ALTER TABLE '
3203 || quote_ident(table_name)
3204 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3205 EXECUTE 'ALTER TABLE '
3206 || quote_ident(table_name)
3207 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3208 EXECUTE 'ALTER TABLE '
3209 || quote_ident(table_name)
3210 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3211 EXECUTE 'ALTER TABLE '
3212 || quote_ident(table_name)
3213 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3216 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3218 x_sc' || field_suffix || ' = id
3220 (SELECT id, name, owner FROM actor_stat_cat
3221 UNION SELECT id, name, owner FROM actor.stat_cat) u
3223 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3224 AND u.owner = ANY ($1);'
3227 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3229 x_sce' || field_suffix || ' = id
3231 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3232 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3234 u.stat_cat = x_sc' || field_suffix || '
3235 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3236 AND u.owner = ANY ($1);'
3239 EXECUTE 'SELECT migration_tools.assert(
3240 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3241 ''Cannot find a desired stat cat'',
3242 ''Found all desired stat cats''
3245 EXECUTE 'SELECT migration_tools.assert(
3246 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3247 ''Cannot find a desired stat cat entry'',
3248 ''Found all desired stat cat entries''
3252 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3254 -- convenience functions for adding shelving locations
3255 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3256 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3262 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3265 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3266 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3267 IF return_id IS NOT NULL THEN
3275 $$ LANGUAGE plpgsql;
3277 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3279 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3280 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3286 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3289 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3291 SELECT INTO return_id id FROM
3292 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3293 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3294 IF return_id IS NOT NULL THEN
3302 $$ LANGUAGE plpgsql;
3304 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3305 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3306 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3314 use MARC::File::XML (BinaryEncoding => 'utf8');
3316 binmode(STDERR, ':bytes');
3317 binmode(STDOUT, ':utf8');
3318 binmode(STDERR, ':utf8');
3320 my $marc_xml = shift;
3321 my $matching_u_text = shift;
3322 my $new_9_to_set = shift;
3324 $marc_xml =~ s/(<leader>.........)./${1}a/;
3327 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3330 #elog("could not parse $bibid: $@\n");
3331 import MARC::File::XML (BinaryEncoding => 'utf8');
3335 my @uris = $marc_xml->field('856');
3336 return unless @uris;
3338 foreach my $field (@uris) {
3339 my $sfu = $field->subfield('u');
3340 my $ind2 = $field->indicator('2');
3341 if (!defined $ind2) { next; }
3342 if ($ind2 ne '0') { next; }
3343 if (!defined $sfu) { next; }
3344 if ($sfu =~ m/$matching_u_text/) {
3345 $field->add_subfields( '9' => $new_9_to_set );
3350 return $marc_xml->as_xml_record();
3354 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3355 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3364 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3366 SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3369 new_xml = '$_$' || new_xml || '$_$';
3371 IF new_xml != source_xml THEN
3372 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3379 $BODY$ LANGUAGE plpgsql;
3381 -- convenience function for linking to the item staging table
3383 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3385 table_schema ALIAS FOR $1;
3386 table_name ALIAS FOR $2;
3387 foreign_column_name ALIAS FOR $3;
3388 main_column_name ALIAS FOR $4;
3389 btrim_desired ALIAS FOR $5;
3392 EXECUTE 'SELECT EXISTS (
3394 FROM information_schema.columns
3395 WHERE table_schema = $1
3397 and column_name = $3
3398 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3400 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3403 EXECUTE 'SELECT EXISTS (
3405 FROM information_schema.columns
3406 WHERE table_schema = $1
3407 AND table_name = ''asset_copy_legacy''
3408 and column_name = $2
3409 )' INTO proceed USING table_schema, main_column_name;
3411 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3414 EXECUTE 'ALTER TABLE '
3415 || quote_ident(table_name)
3416 || ' DROP COLUMN IF EXISTS x_item';
3417 EXECUTE 'ALTER TABLE '
3418 || quote_ident(table_name)
3419 || ' ADD COLUMN x_item BIGINT';
3421 IF btrim_desired THEN
3422 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3423 || ' SET x_item = b.id FROM asset_copy_legacy b'
3424 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3425 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3427 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3428 || ' SET x_item = b.id FROM asset_copy_legacy b'
3429 || ' WHERE a.' || quote_ident(foreign_column_name)
3430 || ' = b.' || quote_ident(main_column_name);
3433 --EXECUTE 'SELECT migration_tools.assert(
3434 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3435 -- ''Cannot link every barcode'',
3436 -- ''Every barcode linked''
3440 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3442 -- convenience function for linking to the user staging table
3444 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3446 table_schema ALIAS FOR $1;
3447 table_name ALIAS FOR $2;
3448 foreign_column_name ALIAS FOR $3;
3449 main_column_name ALIAS FOR $4;
3450 btrim_desired ALIAS FOR $5;
3453 EXECUTE 'SELECT EXISTS (
3455 FROM information_schema.columns
3456 WHERE table_schema = $1
3458 and column_name = $3
3459 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3461 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3464 EXECUTE 'SELECT EXISTS (
3466 FROM information_schema.columns
3467 WHERE table_schema = $1
3468 AND table_name = ''actor_usr_legacy''
3469 and column_name = $2
3470 )' INTO proceed USING table_schema, main_column_name;
3472 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3475 EXECUTE 'ALTER TABLE '
3476 || quote_ident(table_name)
3477 || ' DROP COLUMN IF EXISTS x_user';
3478 EXECUTE 'ALTER TABLE '
3479 || quote_ident(table_name)
3480 || ' ADD COLUMN x_user INTEGER';
3482 IF btrim_desired THEN
3483 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3484 || ' SET x_user = b.id FROM actor_usr_legacy b'
3485 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3486 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3488 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3489 || ' SET x_user = b.id FROM actor_usr_legacy b'
3490 || ' WHERE a.' || quote_ident(foreign_column_name)
3491 || ' = b.' || quote_ident(main_column_name);
3494 --EXECUTE 'SELECT migration_tools.assert(
3495 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3496 -- ''Cannot link every barcode'',
3497 -- ''Every barcode linked''
3501 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3503 -- convenience function for linking two tables
3504 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3505 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3507 table_schema ALIAS FOR $1;
3508 table_a ALIAS FOR $2;
3509 column_a ALIAS FOR $3;
3510 table_b ALIAS FOR $4;
3511 column_b ALIAS FOR $5;
3512 column_x ALIAS FOR $6;
3513 btrim_desired ALIAS FOR $7;
3516 EXECUTE 'SELECT EXISTS (
3518 FROM information_schema.columns
3519 WHERE table_schema = $1
3521 and column_name = $3
3522 )' INTO proceed USING table_schema, table_a, column_a;
3524 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3527 EXECUTE 'SELECT EXISTS (
3529 FROM information_schema.columns
3530 WHERE table_schema = $1
3532 and column_name = $3
3533 )' INTO proceed USING table_schema, table_b, column_b;
3535 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3538 EXECUTE 'ALTER TABLE '
3539 || quote_ident(table_b)
3540 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3541 EXECUTE 'ALTER TABLE '
3542 || quote_ident(table_b)
3543 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3545 IF btrim_desired THEN
3546 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3547 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3548 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3549 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3551 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3552 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3553 || ' WHERE a.' || quote_ident(column_a)
3554 || ' = b.' || quote_ident(column_b);
3558 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3560 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3561 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3562 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3564 table_schema ALIAS FOR $1;
3565 table_a ALIAS FOR $2;
3566 column_a ALIAS FOR $3;
3567 table_b ALIAS FOR $4;
3568 column_b ALIAS FOR $5;
3569 column_w ALIAS FOR $6;
3570 column_x ALIAS FOR $7;
3571 btrim_desired ALIAS FOR $8;
3574 EXECUTE 'SELECT EXISTS (
3576 FROM information_schema.columns
3577 WHERE table_schema = $1
3579 and column_name = $3
3580 )' INTO proceed USING table_schema, table_a, column_a;
3582 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3585 EXECUTE 'SELECT EXISTS (
3587 FROM information_schema.columns
3588 WHERE table_schema = $1
3590 and column_name = $3
3591 )' INTO proceed USING table_schema, table_b, column_b;
3593 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3596 EXECUTE 'ALTER TABLE '
3597 || quote_ident(table_b)
3598 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3599 EXECUTE 'ALTER TABLE '
3600 || quote_ident(table_b)
3601 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3603 IF btrim_desired THEN
3604 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3605 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3606 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3607 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3609 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3610 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3611 || ' WHERE a.' || quote_ident(column_a)
3612 || ' = b.' || quote_ident(column_b);
3616 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3618 -- convenience function for handling desired asset stat cats
3620 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3622 table_schema ALIAS FOR $1;
3623 table_name ALIAS FOR $2;
3624 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3625 org_shortname ALIAS FOR $4;
3633 SELECT 'desired_sc' || field_suffix INTO sc;
3634 SELECT 'desired_sce' || field_suffix INTO sce;
3636 EXECUTE 'SELECT EXISTS (
3638 FROM information_schema.columns
3639 WHERE table_schema = $1
3641 and column_name = $3
3642 )' INTO proceed USING table_schema, table_name, sc;
3644 RAISE EXCEPTION 'Missing column %', sc;
3646 EXECUTE 'SELECT EXISTS (
3648 FROM information_schema.columns
3649 WHERE table_schema = $1
3651 and column_name = $3
3652 )' INTO proceed USING table_schema, table_name, sce;
3654 RAISE EXCEPTION 'Missing column %', sce;
3657 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3659 RAISE EXCEPTION 'Cannot find org by shortname';
3661 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3663 -- caller responsible for their own truncates though we try to prevent duplicates
3664 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3669 ' || quote_ident(table_name) || '
3671 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3675 WHERE owner = ANY ($2)
3676 AND name = BTRIM('||sc||')
3681 WHERE owner = ANY ($2)
3682 AND name = BTRIM('||sc||')
3685 USING org, org_list;
3687 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3692 WHERE owner = ANY ($2)
3693 AND BTRIM('||sc||') = BTRIM(name))
3696 WHERE owner = ANY ($2)
3697 AND BTRIM('||sc||') = BTRIM(name))
3702 ' || quote_ident(table_name) || '
3704 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3705 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3708 FROM asset.stat_cat_entry
3712 WHERE owner = ANY ($2)
3713 AND BTRIM('||sc||') = BTRIM(name)
3714 ) AND value = BTRIM('||sce||')
3718 FROM asset_stat_cat_entry
3722 WHERE owner = ANY ($2)
3723 AND BTRIM('||sc||') = BTRIM(name)
3724 ) AND value = BTRIM('||sce||')
3727 USING org, org_list;
3729 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3731 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3733 table_schema ALIAS FOR $1;
3734 table_name ALIAS FOR $2;
3735 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3736 org_shortname ALIAS FOR $4;
3744 SELECT 'desired_sc' || field_suffix INTO sc;
3745 SELECT 'desired_sce' || field_suffix INTO sce;
3746 EXECUTE 'SELECT EXISTS (
3748 FROM information_schema.columns
3749 WHERE table_schema = $1
3751 and column_name = $3
3752 )' INTO proceed USING table_schema, table_name, sc;
3754 RAISE EXCEPTION 'Missing column %', sc;
3756 EXECUTE 'SELECT EXISTS (
3758 FROM information_schema.columns
3759 WHERE table_schema = $1
3761 and column_name = $3
3762 )' INTO proceed USING table_schema, table_name, sce;
3764 RAISE EXCEPTION 'Missing column %', sce;
3767 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3769 RAISE EXCEPTION 'Cannot find org by shortname';
3772 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3774 EXECUTE 'ALTER TABLE '
3775 || quote_ident(table_name)
3776 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3777 EXECUTE 'ALTER TABLE '
3778 || quote_ident(table_name)
3779 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3780 EXECUTE 'ALTER TABLE '
3781 || quote_ident(table_name)
3782 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3783 EXECUTE 'ALTER TABLE '
3784 || quote_ident(table_name)
3785 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3788 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3790 x_sc' || field_suffix || ' = id
3792 (SELECT id, name, owner FROM asset_stat_cat
3793 UNION SELECT id, name, owner FROM asset.stat_cat) u
3795 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3796 AND u.owner = ANY ($1);'
3799 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3801 x_sce' || field_suffix || ' = id
3803 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3804 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3806 u.stat_cat = x_sc' || field_suffix || '
3807 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3808 AND u.owner = ANY ($1);'
3811 EXECUTE 'SELECT migration_tools.assert(
3812 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3813 ''Cannot find a desired stat cat'',
3814 ''Found all desired stat cats''
3817 EXECUTE 'SELECT migration_tools.assert(
3818 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3819 ''Cannot find a desired stat cat entry'',
3820 ''Found all desired stat cat entries''
3824 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3826 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
3827 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3834 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3836 AND table_schema = s_name
3837 AND (data_type='text' OR data_type='character varying')
3838 AND column_name like 'l_%'
3840 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3847 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
3848 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3855 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3857 AND table_schema = s_name
3858 AND (data_type='text' OR data_type='character varying')
3859 AND column_name like 'l_%'
3861 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');