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 } else { die "error calculating checkdigit";
1139 return $barcode . $checkdigit;
1140 $$ LANGUAGE PLPERLU STRICT STABLE;
1142 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1145 areacode TEXT := $2;
1148 n_digits INTEGER := 0;
1151 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1152 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1153 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1154 IF n_digits = 7 AND areacode <> '' THEN
1155 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1156 output := (areacode || '-' || temp);
1163 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1165 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1166 my ($marcxml, $pos, $value) = @_;
1169 use MARC::File::XML;
1173 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1174 my $leader = $marc->leader();
1175 substr($leader, $pos, 1) = $value;
1176 $marc->leader($leader);
1177 $xml = $marc->as_xml_record;
1178 $xml =~ s/^<\?.+?\?>$//mo;
1180 $xml =~ s/>\s+</></sgo;
1183 $$ LANGUAGE PLPERLU STABLE;
1185 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1186 my ($marcxml, $pos, $value) = @_;
1189 use MARC::File::XML;
1193 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1194 my $f008 = $marc->field('008');
1197 my $field = $f008->data();
1198 substr($field, $pos, 1) = $value;
1199 $f008->update($field);
1200 $xml = $marc->as_xml_record;
1201 $xml =~ s/^<\?.+?\?>$//mo;
1203 $xml =~ s/>\s+</></sgo;
1207 $$ LANGUAGE PLPERLU STABLE;
1210 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1212 profile ALIAS FOR $1;
1214 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1216 $$ LANGUAGE PLPGSQL STRICT STABLE;
1219 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1221 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1223 $$ LANGUAGE PLPGSQL STRICT STABLE;
1226 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1228 my ($marcxml, $tags) = @_;
1231 use MARC::File::XML;
1236 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1237 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1239 my @incumbents = ();
1241 foreach my $field ( $marc->fields() ) {
1242 push @incumbents, $field->as_formatted();
1245 foreach $field ( $to_insert->fields() ) {
1246 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1247 $marc->insert_fields_ordered( ($field) );
1251 $xml = $marc->as_xml_record;
1252 $xml =~ s/^<\?.+?\?>$//mo;
1254 $xml =~ s/>\s+</></sgo;
1259 $$ LANGUAGE PLPERLU STABLE;
1261 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1265 -- First make sure the circ matrix is loaded and the circulations
1266 -- have been staged to the extent possible (but at the very least
1267 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1268 -- circ modifiers must also be in place.
1270 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1278 this_duration_rule INT;
1280 this_max_fine_rule INT;
1281 rcd config.rule_circ_duration%ROWTYPE;
1282 rrf config.rule_recurring_fine%ROWTYPE;
1283 rmf config.rule_max_fine%ROWTYPE;
1290 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1292 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1294 -- Fetch the correct rules for this circulation
1301 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1304 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1305 INTO circ_lib, target_copy, usr, is_renewal ;
1307 INTO this_duration_rule,
1311 recurring_fine_rule,
1313 FROM action.item_user_circ_test(
1319 SELECT INTO rcd * FROM config.rule_circ_duration
1320 WHERE id = this_duration_rule;
1321 SELECT INTO rrf * FROM config.rule_recurring_fine
1322 WHERE id = this_fine_rule;
1323 SELECT INTO rmf * FROM config.rule_max_fine
1324 WHERE id = this_max_fine_rule;
1326 -- Apply the rules to this circulation
1327 EXECUTE ('UPDATE ' || tablename || ' c
1329 duration_rule = rcd.name,
1330 recurring_fine_rule = rrf.name,
1331 max_fine_rule = rmf.name,
1332 duration = rcd.normal,
1333 recurring_fine = rrf.normal,
1336 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1339 renewal_remaining = rcd.max_renewals
1341 config.rule_circ_duration rcd,
1342 config.rule_recurring_fine rrf,
1343 config.rule_max_fine rmf,
1346 rcd.id = ' || this_duration_rule || ' AND
1347 rrf.id = ' || this_fine_rule || ' AND
1348 rmf.id = ' || this_max_fine_rule || ' AND
1349 ac.id = c.target_copy AND
1350 c.id = ' || circ || ';');
1352 -- Keep track of where we are in the process
1354 IF (n % 100 = 0) THEN
1355 RAISE INFO '%', n || ' of ' || n_circs
1356 || ' (' || (100*n/n_circs) || '%) circs updated.';
1364 $$ LANGUAGE plpgsql;
1366 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1370 -- First make sure the circ matrix is loaded and the circulations
1371 -- have been staged to the extent possible (but at the very least
1372 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1373 -- circ modifiers must also be in place.
1375 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1383 this_duration_rule INT;
1385 this_max_fine_rule INT;
1386 rcd config.rule_circ_duration%ROWTYPE;
1387 rrf config.rule_recurring_fine%ROWTYPE;
1388 rmf config.rule_max_fine%ROWTYPE;
1395 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1397 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1399 -- Fetch the correct rules for this circulation
1406 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1409 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1410 INTO circ_lib, target_copy, usr, is_renewal ;
1412 INTO this_duration_rule,
1418 FROM action.find_circ_matrix_matchpoint(
1424 SELECT INTO rcd * FROM config.rule_circ_duration
1425 WHERE id = this_duration_rule;
1426 SELECT INTO rrf * FROM config.rule_recurring_fine
1427 WHERE id = this_fine_rule;
1428 SELECT INTO rmf * FROM config.rule_max_fine
1429 WHERE id = this_max_fine_rule;
1431 -- Apply the rules to this circulation
1432 EXECUTE ('UPDATE ' || tablename || ' c
1434 duration_rule = rcd.name,
1435 recuring_fine_rule = rrf.name,
1436 max_fine_rule = rmf.name,
1437 duration = rcd.normal,
1438 recuring_fine = rrf.normal,
1441 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1444 renewal_remaining = rcd.max_renewals
1446 config.rule_circ_duration rcd,
1447 config.rule_recuring_fine rrf,
1448 config.rule_max_fine rmf,
1451 rcd.id = ' || this_duration_rule || ' AND
1452 rrf.id = ' || this_fine_rule || ' AND
1453 rmf.id = ' || this_max_fine_rule || ' AND
1454 ac.id = c.target_copy AND
1455 c.id = ' || circ || ';');
1457 -- Keep track of where we are in the process
1459 IF (n % 100 = 0) THEN
1460 RAISE INFO '%', n || ' of ' || n_circs
1461 || ' (' || (100*n/n_circs) || '%) circs updated.';
1469 $$ LANGUAGE plpgsql;
1471 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1475 -- First make sure the circ matrix is loaded and the circulations
1476 -- have been staged to the extent possible (but at the very least
1477 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1478 -- circ modifiers must also be in place.
1480 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1488 this_duration_rule INT;
1490 this_max_fine_rule INT;
1491 rcd config.rule_circ_duration%ROWTYPE;
1492 rrf config.rule_recurring_fine%ROWTYPE;
1493 rmf config.rule_max_fine%ROWTYPE;
1500 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1502 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1504 -- Fetch the correct rules for this circulation
1511 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1514 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1515 INTO circ_lib, target_copy, usr, is_renewal ;
1517 INTO this_duration_rule,
1520 (matchpoint).duration_rule,
1521 (matchpoint).recurring_fine_rule,
1522 (matchpoint).max_fine_rule
1523 FROM action.find_circ_matrix_matchpoint(
1529 SELECT INTO rcd * FROM config.rule_circ_duration
1530 WHERE id = this_duration_rule;
1531 SELECT INTO rrf * FROM config.rule_recurring_fine
1532 WHERE id = this_fine_rule;
1533 SELECT INTO rmf * FROM config.rule_max_fine
1534 WHERE id = this_max_fine_rule;
1536 -- Apply the rules to this circulation
1537 EXECUTE ('UPDATE ' || tablename || ' c
1539 duration_rule = rcd.name,
1540 recurring_fine_rule = rrf.name,
1541 max_fine_rule = rmf.name,
1542 duration = rcd.normal,
1543 recurring_fine = rrf.normal,
1546 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1549 renewal_remaining = rcd.max_renewals,
1550 grace_period = rrf.grace_period
1552 config.rule_circ_duration rcd,
1553 config.rule_recurring_fine rrf,
1554 config.rule_max_fine rmf,
1557 rcd.id = ' || this_duration_rule || ' AND
1558 rrf.id = ' || this_fine_rule || ' AND
1559 rmf.id = ' || this_max_fine_rule || ' AND
1560 ac.id = c.target_copy AND
1561 c.id = ' || circ || ';');
1563 -- Keep track of where we are in the process
1565 IF (n % 100 = 0) THEN
1566 RAISE INFO '%', n || ' of ' || n_circs
1567 || ' (' || (100*n/n_circs) || '%) circs updated.';
1575 $$ LANGUAGE plpgsql;
1577 -- TODO: make another version of the procedure below that can work with specified copy staging tables
1578 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
1579 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
1582 charge_lost_on_zero BOOLEAN;
1585 default_price NUMERIC;
1586 working_price NUMERIC;
1590 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
1591 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
1593 SELECT INTO charge_lost_on_zero value
1594 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
1596 SELECT INTO min_price value
1597 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
1599 SELECT INTO max_price value
1600 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
1602 SELECT INTO default_price value
1603 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
1605 SELECT INTO working_price price FROM asset.copy WHERE id = item;
1607 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
1608 working_price := default_price;
1611 IF (max_price IS NOT NULL AND working_price > max_price) THEN
1612 working_price := max_price;
1615 IF (min_price IS NOT NULL AND working_price < min_price) THEN
1616 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
1617 working_price := min_price;
1621 RETURN working_price;
1625 $$ LANGUAGE plpgsql;
1627 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1631 -- First make sure the circ matrix is loaded and the circulations
1632 -- have been staged to the extent possible (but at the very least
1633 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1634 -- circ modifiers must also be in place.
1636 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1644 this_duration_rule INT;
1646 this_max_fine_rule INT;
1647 rcd config.rule_circ_duration%ROWTYPE;
1648 rrf config.rule_recurring_fine%ROWTYPE;
1649 rmf config.rule_max_fine%ROWTYPE;
1655 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1657 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1659 -- Fetch the correct rules for this circulation
1666 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1669 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1670 INTO circ_lib, target_copy, usr, is_renewal ;
1672 INTO this_duration_rule,
1675 (matchpoint).duration_rule,
1676 (matchpoint).recurring_fine_rule,
1677 (matchpoint).max_fine_rule
1678 FROM action.find_circ_matrix_matchpoint(
1684 SELECT INTO rcd * FROM config.rule_circ_duration
1685 WHERE id = this_duration_rule;
1686 SELECT INTO rrf * FROM config.rule_recurring_fine
1687 WHERE id = this_fine_rule;
1688 SELECT INTO rmf * FROM config.rule_max_fine
1689 WHERE id = this_max_fine_rule;
1691 -- Apply the rules to this circulation
1692 EXECUTE ('UPDATE ' || tablename || ' c
1694 duration_rule = rcd.name,
1695 recurring_fine_rule = rrf.name,
1696 max_fine_rule = rmf.name,
1697 duration = rcd.normal,
1698 recurring_fine = rrf.normal,
1701 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
1704 renewal_remaining = rcd.max_renewals,
1705 grace_period = rrf.grace_period
1707 config.rule_circ_duration rcd,
1708 config.rule_recurring_fine rrf,
1709 config.rule_max_fine rmf,
1712 rcd.id = ' || this_duration_rule || ' AND
1713 rrf.id = ' || this_fine_rule || ' AND
1714 rmf.id = ' || this_max_fine_rule || ' AND
1715 ac.id = c.target_copy AND
1716 c.id = ' || circ || ';');
1718 -- Keep track of where we are in the process
1720 IF (n % 100 = 0) THEN
1721 RAISE INFO '%', n || ' of ' || n_circs
1722 || ' (' || (100*n/n_circs) || '%) circs updated.';
1730 $$ LANGUAGE plpgsql;
1735 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1737 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1738 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1740 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1741 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1744 c TEXT := schemaname || '.asset_copy_legacy';
1745 sc TEXT := schemaname || '.asset_stat_cat';
1746 sce TEXT := schemaname || '.asset_stat_cat_entry';
1747 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1753 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1755 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1757 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1758 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1759 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1766 $$ LANGUAGE plpgsql;
1768 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1770 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1771 -- This will assign standing penalties as needed.
1779 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1781 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1783 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1793 $$ LANGUAGE plpgsql;
1796 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1799 INSERT INTO metabib.metarecord (fingerprint, master_record)
1800 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1801 FROM biblio.record_entry b
1803 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)
1804 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1805 ORDER BY b.fingerprint, b.quality DESC;
1806 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1808 FROM biblio.record_entry r
1809 JOIN metabib.metarecord m USING (fingerprint)
1810 WHERE NOT r.deleted;
1813 $$ LANGUAGE plpgsql;
1816 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1819 INSERT INTO metabib.metarecord (fingerprint, master_record)
1820 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1821 FROM biblio.record_entry b
1823 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)
1824 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1825 ORDER BY b.fingerprint, b.quality DESC;
1826 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1828 FROM biblio.record_entry r
1829 JOIN metabib.metarecord m USING (fingerprint)
1831 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);
1834 $$ LANGUAGE plpgsql;
1837 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1839 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1840 -- Then SELECT migration_tools.create_cards('m_foo');
1843 u TEXT := schemaname || '.actor_usr_legacy';
1844 c TEXT := schemaname || '.actor_card';
1848 EXECUTE ('DELETE FROM ' || c || ';');
1849 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1850 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1856 $$ LANGUAGE plpgsql;
1859 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1861 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1863 my ($marcxml, $shortname) = @_;
1866 use MARC::File::XML;
1871 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1873 foreach my $field ( $marc->field('856') ) {
1874 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1875 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1876 $field->add_subfields( '9' => $shortname );
1877 $field->update( ind2 => '0');
1881 $xml = $marc->as_xml_record;
1882 $xml =~ s/^<\?.+?\?>$//mo;
1884 $xml =~ s/>\s+</></sgo;
1889 $$ LANGUAGE PLPERLU STABLE;
1891 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1893 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1895 my ($marcxml, $shortname) = @_;
1898 use MARC::File::XML;
1903 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1905 foreach my $field ( $marc->field('856') ) {
1906 if ( ! $field->as_string('9') ) {
1907 $field->add_subfields( '9' => $shortname );
1911 $xml = $marc->as_xml_record;
1912 $xml =~ s/^<\?.+?\?>$//mo;
1914 $xml =~ s/>\s+</></sgo;
1919 $$ LANGUAGE PLPERLU STABLE;
1922 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1934 -- Bail out if asked to change the label to ##URI##
1935 IF new_label = '##URI##' THEN
1939 -- Gather information
1940 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1941 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1942 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1944 -- Bail out if the label already is ##URI##
1945 IF old_label = '##URI##' THEN
1949 -- Bail out if the call number label is already correct
1950 IF new_volume = old_volume THEN
1954 -- Check whether we already have a destination volume available
1955 SELECT id INTO new_volume FROM asset.call_number
1958 owning_lib = owner AND
1959 label = new_label AND
1962 -- Create destination volume if needed
1964 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1965 VALUES (1, 1, bib, owner, new_label, cn_class);
1966 SELECT id INTO new_volume FROM asset.call_number
1969 owning_lib = owner AND
1970 label = new_label AND
1974 -- Move copy to destination
1975 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1977 -- Delete source volume if it is now empty
1978 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1980 DELETE FROM asset.call_number WHERE id = old_volume;
1985 $$ LANGUAGE plpgsql;
1987 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1992 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1996 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1997 $zipdata{$zip} = [$city, $state, $county];
2000 if (defined $zipdata{$input}) {
2001 my ($city, $state, $county) = @{$zipdata{$input}};
2002 return [$city, $state, $county];
2003 } elsif (defined $zipdata{substr $input, 0, 5}) {
2004 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2005 return [$city, $state, $county];
2007 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2010 $$ LANGUAGE PLPERLU STABLE;
2012 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2019 errors_found BOOLEAN;
2021 parent_shortname TEXT;
2027 type_parent_depth INT;
2032 errors_found := FALSE;
2034 -- Checking actor.org_unit_type
2036 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2038 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2039 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2041 IF type_parent IS NOT NULL THEN
2043 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2045 IF type_depth - type_parent_depth <> 1 THEN
2046 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2047 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2048 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2049 ou_type_name, type_depth, parent_type, type_parent_depth;
2050 errors_found := TRUE;
2058 -- Checking actor.org_unit
2060 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2062 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2063 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;
2064 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;
2065 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2066 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2067 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;
2068 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;
2070 IF ou_parent IS NOT NULL THEN
2072 IF (org_unit_depth - parent_depth <> 1) OR (
2073 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2075 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2076 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2077 errors_found := TRUE;
2084 IF NOT errors_found THEN
2085 RAISE INFO 'No errors found.';
2092 $$ LANGUAGE plpgsql;
2095 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2099 DELETE FROM asset.opac_visible_copies;
2101 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2103 cp.id, cp.circ_lib, cn.record
2106 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2107 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2108 JOIN asset.copy_location cl ON (cp.location = cl.id)
2109 JOIN config.copy_status cs ON (cp.status = cs.id)
2110 JOIN biblio.record_entry b ON (cn.record = b.id)
2119 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2123 $$ LANGUAGE plpgsql;
2126 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2132 old_owning_lib INTEGER;
2138 -- Gather information
2139 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2140 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2141 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2143 -- Bail out if the new_owning_lib is not the ID of an org_unit
2144 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2146 '% is not a valid actor.org_unit ID; no change made.',
2151 -- Bail out discreetly if the owning_lib is already correct
2152 IF new_owning_lib = old_owning_lib THEN
2156 -- Check whether we already have a destination volume available
2157 SELECT id INTO new_volume FROM asset.call_number
2160 owning_lib = new_owning_lib AND
2161 label = old_label AND
2164 -- Create destination volume if needed
2166 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2167 VALUES (1, 1, bib, new_owning_lib, old_label);
2168 SELECT id INTO new_volume FROM asset.call_number
2171 owning_lib = new_owning_lib AND
2172 label = old_label AND
2176 -- Move copy to destination
2177 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2179 -- Delete source volume if it is now empty
2180 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2182 DELETE FROM asset.call_number WHERE id = old_volume;
2187 $$ LANGUAGE plpgsql;
2190 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2192 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2195 new_owning_lib INTEGER;
2199 -- Parse the new_owner as an org unit ID or shortname
2200 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2201 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2202 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2203 ELSIF new_owner ~ E'^[0-9]+$' THEN
2204 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2207 E'You don\'t need to put the actor.org_unit ID in quotes; '
2208 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2209 new_owning_lib := new_owner::INTEGER;
2210 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2214 '% is not a valid actor.org_unit shortname or ID; no change made.',
2221 $$ LANGUAGE plpgsql;
2223 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2226 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2229 MARC::Charset->assume_unicode(1);
2234 my $r = MARC::Record->new_from_xml( $xml );
2235 my $output_xml = $r->as_xml_record();
2243 $func$ LANGUAGE PLPERLU;
2244 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2246 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2248 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2249 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2250 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2251 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2252 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2253 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2254 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2255 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2256 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2257 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2258 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2259 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2260 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2261 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2262 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2263 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2264 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2265 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2266 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2267 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2268 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2269 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2270 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2271 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2272 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2273 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2275 $FUNC$ LANGUAGE PLPGSQL;
2277 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2279 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2280 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2281 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2282 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2283 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2284 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2285 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2287 -- import any new circ rules
2288 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2289 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2290 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2291 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2293 -- and permission groups
2294 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2297 $FUNC$ LANGUAGE PLPGSQL;
2300 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$
2309 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2310 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2311 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2312 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2313 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2314 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2315 FOR name IN EXECUTE loopq LOOP
2316 EXECUTE existsq INTO ct USING name;
2318 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2319 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2320 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2321 EXECUTE copyst USING name;
2325 $FUNC$ LANGUAGE PLPGSQL;
2327 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2333 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2336 MARC::Charset->assume_unicode(1);
2338 my $target_xml = shift;
2339 my $source_xml = shift;
2345 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2349 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2354 my $source_id = $source->subfield('901', 'c');
2355 $source_id = $source->subfield('903', 'a') unless $source_id;
2356 my $target_id = $target->subfield('901', 'c');
2357 $target_id = $target->subfield('903', 'a') unless $target_id;
2359 my %existing_fields;
2360 foreach my $tag (@$tags) {
2361 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2362 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2363 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2365 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2369 my $xml = $target->as_xml_record;
2370 $xml =~ s/^<\?.+?\?>$//mo;
2372 $xml =~ s/>\s+</></sgo;
2376 $func$ LANGUAGE PLPERLU;
2377 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.';
2379 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2385 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2388 my $in_tags = shift;
2389 my $in_values = shift;
2391 # hack-and-slash parsing of array-passed-as-string;
2392 # this can go away once everybody is running Postgres 9.1+
2393 my $csv = Text::CSV->new({binary => 1});
2396 my $status = $csv->parse($in_tags);
2397 my $tags = [ $csv->fields() ];
2398 $in_values =~ s/^{//;
2399 $in_values =~ s/}$//;
2400 $status = $csv->parse($in_values);
2401 my $values = [ $csv->fields() ];
2403 my $marc = MARC::Record->new();
2405 $marc->leader('00000nam a22000007 4500');
2406 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2408 foreach my $i (0..$#$tags) {
2410 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2413 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2414 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2416 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2420 my $xml = $marc->as_xml_record;
2421 $xml =~ s/^<\?.+?\?>$//mo;
2423 $xml =~ s/>\s+</></sgo;
2427 $func$ LANGUAGE PLPERLU;
2428 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2429 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2430 The second argument is an array of text containing the values to plug into each field.
2431 If the value for a given field is NULL or the empty string, it is not inserted.
2434 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2436 my ($marcxml, $tag, $pos, $value) = @_;
2439 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2443 MARC::Charset->assume_unicode(1);
2445 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2446 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2447 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2448 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2452 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2454 foreach my $field ($marc->field($tag)) {
2455 $field->update("ind$pos" => $value);
2457 $xml = $marc->as_xml_record;
2458 $xml =~ s/^<\?.+?\?>$//mo;
2460 $xml =~ s/>\s+</></sgo;
2464 $func$ LANGUAGE PLPERLU;
2466 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2467 The first argument is a MARCXML string.
2468 The second argument is a MARC tag.
2469 The third argument is the indicator position, either 1 or 2.
2470 The fourth argument is the character to set the indicator value to.
2471 All occurences of the specified field will be changed.
2472 The function returns the revised MARCXML string.$$;
2474 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2479 first_name TEXT DEFAULT '',
2480 last_name TEXT DEFAULT ''
2481 ) RETURNS VOID AS $func$
2483 RAISE NOTICE '%', org ;
2484 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2485 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2486 FROM actor.org_unit aou, permission.grp_tree pgt
2487 WHERE aou.shortname = org
2488 AND pgt.name = perm_group;
2493 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2494 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2496 target_event_def ALIAS FOR $1;
2499 DROP TABLE IF EXISTS new_atevdefs;
2500 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2501 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2502 INSERT INTO action_trigger.event_definition (
2523 ,name || ' (clone of '||target_event_def||')'
2539 action_trigger.event_definition
2541 id = target_event_def
2543 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2544 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2545 INSERT INTO action_trigger.environment (
2551 currval('action_trigger.event_definition_id_seq')
2556 action_trigger.environment
2558 event_def = target_event_def
2560 INSERT INTO action_trigger.event_params (
2565 currval('action_trigger.event_definition_id_seq')
2569 action_trigger.event_params
2571 event_def = target_event_def
2574 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);
2576 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2578 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2579 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2581 target_event_def ALIAS FOR $1;
2583 new_interval ALIAS FOR $3;
2585 DROP TABLE IF EXISTS new_atevdefs;
2586 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2587 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2588 INSERT INTO action_trigger.event_definition (
2609 ,name || ' (clone of '||target_event_def||')'
2625 action_trigger.event_definition
2627 id = target_event_def
2629 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2630 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2631 INSERT INTO action_trigger.environment (
2637 currval('action_trigger.event_definition_id_seq')
2642 action_trigger.environment
2644 event_def = target_event_def
2646 INSERT INTO action_trigger.event_params (
2651 currval('action_trigger.event_definition_id_seq')
2655 action_trigger.event_params
2657 event_def = target_event_def
2660 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);
2662 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2664 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
2665 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
2668 target_event_defs ALIAS FOR $2;
2670 DROP TABLE IF EXISTS new_atevdefs;
2671 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2672 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
2673 INSERT INTO action_trigger.event_definition (
2694 ,name || ' (clone of '||target_event_defs[i]||')'
2710 action_trigger.event_definition
2712 id = target_event_defs[i]
2714 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2715 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2716 INSERT INTO action_trigger.environment (
2722 currval('action_trigger.event_definition_id_seq')
2727 action_trigger.environment
2729 event_def = target_event_defs[i]
2731 INSERT INTO action_trigger.event_params (
2736 currval('action_trigger.event_definition_id_seq')
2740 action_trigger.event_params
2742 event_def = target_event_defs[i]
2745 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2747 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2749 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
2751 action_trigger.event
2755 ,complete_time = NULL
2756 ,update_process = NULL
2758 ,template_output = NULL
2759 ,error_output = NULL
2760 ,async_output = NULL
2765 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2769 use MARC::File::XML;
2774 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2775 $field = $marc->leader();
2778 $$ LANGUAGE PLPERLU STABLE;
2780 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2781 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2784 use MARC::File::XML;
2789 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2790 $field = $marc->field($tag);
2792 return $field->as_string($subfield,$delimiter);
2793 $$ LANGUAGE PLPERLU STABLE;
2795 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2796 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2799 use MARC::File::XML;
2804 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2805 @fields = $marc->field($tag);
2808 foreach my $field (@fields) {
2809 push @texts, $field->as_string($subfield,$delimiter);
2812 $$ LANGUAGE PLPERLU STABLE;
2814 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2815 SELECT action.find_hold_matrix_matchpoint(
2816 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2817 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2818 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2819 (SELECT usr FROM action.hold_request WHERE id = $1),
2820 (SELECT requestor FROM action.hold_request WHERE id = $1)
2824 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2825 SELECT action.hold_request_permit_test(
2826 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2827 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2828 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2829 (SELECT usr FROM action.hold_request WHERE id = $1),
2830 (SELECT requestor FROM action.hold_request WHERE id = $1)
2834 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2835 SELECT action.find_circ_matrix_matchpoint(
2836 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2837 (SELECT target_copy FROM action.circulation WHERE id = $1),
2838 (SELECT usr FROM action.circulation WHERE id = $1),
2840 NULLIF(phone_renewal,false),
2841 NULLIF(desk_renewal,false),
2842 NULLIF(opac_renewal,false),
2844 ) FROM action.circulation WHERE id = $1
2849 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2854 RAISE EXCEPTION 'assertion';
2857 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2859 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2865 RAISE EXCEPTION '%', msg;
2868 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2870 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2873 fail_msg ALIAS FOR $2;
2874 success_msg ALIAS FOR $3;
2877 RAISE EXCEPTION '%', fail_msg;
2881 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2883 -- push bib sequence and return starting value for reserved range
2884 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2886 bib_count ALIAS FOR $1;
2889 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2891 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2896 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2898 -- set a new salted password
2900 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2902 usr_id ALIAS FOR $1;
2903 plain_passwd ALIAS FOR $2;
2908 SELECT actor.create_salt('main') INTO plain_salt;
2910 SELECT MD5(plain_passwd) INTO md5_passwd;
2912 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2917 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2920 -- convenience functions for handling copy_location maps
2922 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2924 table_schema ALIAS FOR $1;
2925 table_name ALIAS FOR $2;
2926 org_shortname ALIAS FOR $3;
2927 org_range ALIAS FOR $4;
2933 EXECUTE 'SELECT EXISTS (
2935 FROM information_schema.columns
2936 WHERE table_schema = $1
2938 and column_name = ''desired_shelf''
2939 )' INTO proceed USING table_schema, table_name;
2941 RAISE EXCEPTION 'Missing column desired_shelf';
2944 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2946 RAISE EXCEPTION 'Cannot find org by shortname';
2949 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2951 EXECUTE 'ALTER TABLE '
2952 || quote_ident(table_name)
2953 || ' DROP COLUMN IF EXISTS x_shelf';
2954 EXECUTE 'ALTER TABLE '
2955 || quote_ident(table_name)
2956 || ' ADD COLUMN x_shelf INTEGER';
2958 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2959 || ' SET x_shelf = id FROM asset_copy_location b'
2960 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2961 || ' AND b.owning_lib = $1'
2962 || ' AND NOT b.deleted'
2965 FOREACH o IN ARRAY org_list LOOP
2966 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2967 || ' SET x_shelf = id FROM asset.copy_location b'
2968 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2969 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2970 || ' AND NOT b.deleted'
2974 EXECUTE 'SELECT migration_tools.assert(
2975 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2976 ''Cannot find a desired location'',
2977 ''Found all desired locations''
2981 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2983 -- convenience functions for handling circmod maps
2985 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2987 table_schema ALIAS FOR $1;
2988 table_name ALIAS FOR $2;
2991 EXECUTE 'SELECT EXISTS (
2993 FROM information_schema.columns
2994 WHERE table_schema = $1
2996 and column_name = ''desired_circmod''
2997 )' INTO proceed USING table_schema, table_name;
2999 RAISE EXCEPTION 'Missing column desired_circmod';
3002 EXECUTE 'ALTER TABLE '
3003 || quote_ident(table_name)
3004 || ' DROP COLUMN IF EXISTS x_circmod';
3005 EXECUTE 'ALTER TABLE '
3006 || quote_ident(table_name)
3007 || ' ADD COLUMN x_circmod TEXT';
3009 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3010 || ' SET x_circmod = code FROM config.circ_modifier b'
3011 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3013 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3014 || ' SET x_circmod = code FROM config.circ_modifier b'
3015 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3016 || ' AND x_circmod IS NULL';
3018 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3019 || ' SET x_circmod = code FROM config.circ_modifier b'
3020 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3021 || ' AND x_circmod IS NULL';
3023 EXECUTE 'SELECT migration_tools.assert(
3024 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3025 ''Cannot find a desired circulation modifier'',
3026 ''Found all desired circulation modifiers''
3030 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3032 -- convenience functions for handling item status maps
3034 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3036 table_schema ALIAS FOR $1;
3037 table_name ALIAS FOR $2;
3040 EXECUTE 'SELECT EXISTS (
3042 FROM information_schema.columns
3043 WHERE table_schema = $1
3045 and column_name = ''desired_status''
3046 )' INTO proceed USING table_schema, table_name;
3048 RAISE EXCEPTION 'Missing column desired_status';
3051 EXECUTE 'ALTER TABLE '
3052 || quote_ident(table_name)
3053 || ' DROP COLUMN IF EXISTS x_status';
3054 EXECUTE 'ALTER TABLE '
3055 || quote_ident(table_name)
3056 || ' ADD COLUMN x_status INTEGER';
3058 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3059 || ' SET x_status = id FROM config.copy_status b'
3060 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3062 EXECUTE 'SELECT migration_tools.assert(
3063 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3064 ''Cannot find a desired copy status'',
3065 ''Found all desired copy statuses''
3069 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3071 -- convenience functions for handling org maps
3073 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3075 table_schema ALIAS FOR $1;
3076 table_name ALIAS FOR $2;
3079 EXECUTE 'SELECT EXISTS (
3081 FROM information_schema.columns
3082 WHERE table_schema = $1
3084 and column_name = ''desired_org''
3085 )' INTO proceed USING table_schema, table_name;
3087 RAISE EXCEPTION 'Missing column desired_org';
3090 EXECUTE 'ALTER TABLE '
3091 || quote_ident(table_name)
3092 || ' DROP COLUMN IF EXISTS x_org';
3093 EXECUTE 'ALTER TABLE '
3094 || quote_ident(table_name)
3095 || ' ADD COLUMN x_org INTEGER';
3097 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3098 || ' SET x_org = id FROM actor.org_unit b'
3099 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3101 EXECUTE 'SELECT migration_tools.assert(
3102 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3103 ''Cannot find a desired org unit'',
3104 ''Found all desired org units''
3108 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3110 -- convenience function for handling desired_not_migrate
3112 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3114 table_schema ALIAS FOR $1;
3115 table_name ALIAS FOR $2;
3118 EXECUTE 'SELECT EXISTS (
3120 FROM information_schema.columns
3121 WHERE table_schema = $1
3123 and column_name = ''desired_not_migrate''
3124 )' INTO proceed USING table_schema, table_name;
3126 RAISE EXCEPTION 'Missing column desired_not_migrate';
3129 EXECUTE 'ALTER TABLE '
3130 || quote_ident(table_name)
3131 || ' DROP COLUMN IF EXISTS x_migrate';
3132 EXECUTE 'ALTER TABLE '
3133 || quote_ident(table_name)
3134 || ' ADD COLUMN x_migrate BOOLEAN';
3136 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3137 || ' SET x_migrate = CASE'
3138 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3139 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3140 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3141 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3142 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3143 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3146 EXECUTE 'SELECT migration_tools.assert(
3147 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3148 ''Not all desired_not_migrate values understood'',
3149 ''All desired_not_migrate values understood''
3153 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3155 -- convenience function for handling desired_profile
3157 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3159 table_schema ALIAS FOR $1;
3160 table_name ALIAS FOR $2;
3163 EXECUTE 'SELECT EXISTS (
3165 FROM information_schema.columns
3166 WHERE table_schema = $1
3168 and column_name = ''desired_profile''
3169 )' INTO proceed USING table_schema, table_name;
3171 RAISE EXCEPTION 'Missing column desired_profile';
3174 EXECUTE 'ALTER TABLE '
3175 || quote_ident(table_name)
3176 || ' DROP COLUMN IF EXISTS x_profile';
3177 EXECUTE 'ALTER TABLE '
3178 || quote_ident(table_name)
3179 || ' ADD COLUMN x_profile INTEGER';
3181 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3182 || ' SET x_profile = id FROM permission.grp_tree b'
3183 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3185 EXECUTE 'SELECT migration_tools.assert(
3186 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3187 ''Cannot find a desired profile'',
3188 ''Found all desired profiles''
3192 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3194 -- convenience function for handling desired actor stat cats
3196 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3198 table_schema ALIAS FOR $1;
3199 table_name ALIAS FOR $2;
3200 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3201 org_shortname ALIAS FOR $4;
3209 SELECT 'desired_sc' || field_suffix INTO sc;
3210 SELECT 'desired_sce' || field_suffix INTO sce;
3212 EXECUTE 'SELECT EXISTS (
3214 FROM information_schema.columns
3215 WHERE table_schema = $1
3217 and column_name = $3
3218 )' INTO proceed USING table_schema, table_name, sc;
3220 RAISE EXCEPTION 'Missing column %', sc;
3222 EXECUTE 'SELECT EXISTS (
3224 FROM information_schema.columns
3225 WHERE table_schema = $1
3227 and column_name = $3
3228 )' INTO proceed USING table_schema, table_name, sce;
3230 RAISE EXCEPTION 'Missing column %', sce;
3233 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3235 RAISE EXCEPTION 'Cannot find org by shortname';
3237 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3239 -- caller responsible for their own truncates though we try to prevent duplicates
3240 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3245 ' || quote_ident(table_name) || '
3247 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3251 WHERE owner = ANY ($2)
3252 AND name = BTRIM('||sc||')
3257 WHERE owner = ANY ($2)
3258 AND name = BTRIM('||sc||')
3261 USING org, org_list;
3263 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3268 WHERE owner = ANY ($2)
3269 AND BTRIM('||sc||') = BTRIM(name))
3272 WHERE owner = ANY ($2)
3273 AND BTRIM('||sc||') = BTRIM(name))
3278 ' || quote_ident(table_name) || '
3280 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3281 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3284 FROM actor.stat_cat_entry
3288 WHERE owner = ANY ($2)
3289 AND BTRIM('||sc||') = BTRIM(name)
3290 ) AND value = BTRIM('||sce||')
3294 FROM actor_stat_cat_entry
3298 WHERE owner = ANY ($2)
3299 AND BTRIM('||sc||') = BTRIM(name)
3300 ) AND value = BTRIM('||sce||')
3303 USING org, org_list;
3305 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3307 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3309 table_schema ALIAS FOR $1;
3310 table_name ALIAS FOR $2;
3311 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3312 org_shortname ALIAS FOR $4;
3320 SELECT 'desired_sc' || field_suffix INTO sc;
3321 SELECT 'desired_sce' || field_suffix INTO sce;
3322 EXECUTE 'SELECT EXISTS (
3324 FROM information_schema.columns
3325 WHERE table_schema = $1
3327 and column_name = $3
3328 )' INTO proceed USING table_schema, table_name, sc;
3330 RAISE EXCEPTION 'Missing column %', sc;
3332 EXECUTE 'SELECT EXISTS (
3334 FROM information_schema.columns
3335 WHERE table_schema = $1
3337 and column_name = $3
3338 )' INTO proceed USING table_schema, table_name, sce;
3340 RAISE EXCEPTION 'Missing column %', sce;
3343 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3345 RAISE EXCEPTION 'Cannot find org by shortname';
3348 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3350 EXECUTE 'ALTER TABLE '
3351 || quote_ident(table_name)
3352 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3353 EXECUTE 'ALTER TABLE '
3354 || quote_ident(table_name)
3355 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3356 EXECUTE 'ALTER TABLE '
3357 || quote_ident(table_name)
3358 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3359 EXECUTE 'ALTER TABLE '
3360 || quote_ident(table_name)
3361 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3364 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3366 x_sc' || field_suffix || ' = id
3368 (SELECT id, name, owner FROM actor_stat_cat
3369 UNION SELECT id, name, owner FROM actor.stat_cat) u
3371 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3372 AND u.owner = ANY ($1);'
3375 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3377 x_sce' || field_suffix || ' = id
3379 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3380 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3382 u.stat_cat = x_sc' || field_suffix || '
3383 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3384 AND u.owner = ANY ($1);'
3387 EXECUTE 'SELECT migration_tools.assert(
3388 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3389 ''Cannot find a desired stat cat'',
3390 ''Found all desired stat cats''
3393 EXECUTE 'SELECT migration_tools.assert(
3394 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3395 ''Cannot find a desired stat cat entry'',
3396 ''Found all desired stat cat entries''
3400 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3402 -- convenience functions for adding shelving locations
3403 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3404 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3410 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3413 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3414 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3415 IF return_id IS NOT NULL THEN
3423 $$ LANGUAGE plpgsql;
3425 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3427 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3428 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3434 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3437 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3439 SELECT INTO return_id id FROM
3440 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3441 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3442 IF return_id IS NOT NULL THEN
3450 $$ LANGUAGE plpgsql;
3452 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
3453 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
3454 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
3462 use MARC::File::XML (BinaryEncoding => 'utf8');
3464 binmode(STDERR, ':bytes');
3465 binmode(STDOUT, ':utf8');
3466 binmode(STDERR, ':utf8');
3468 my $marc_xml = shift;
3469 my $new_9_to_set = shift;
3471 $marc_xml =~ s/(<leader>.........)./${1}a/;
3474 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3477 #elog("could not parse $bibid: $@\n");
3478 import MARC::File::XML (BinaryEncoding => 'utf8');
3482 my @uris = $marc_xml->field('856');
3483 return $marc_xml->as_xml_record() unless @uris;
3485 foreach my $field (@uris) {
3486 my $ind1 = $field->indicator('1');
3487 if (!defined $ind1) { next; }
3488 if ($ind1 ne '1' && $ind1 ne '4') { next; }
3489 my $ind2 = $field->indicator('2');
3490 if (!defined $ind2) { next; }
3491 if ($ind2 ne '0' && $ind2 ne '1') { next; }
3492 $field->add_subfields( '9' => $new_9_to_set );
3495 return $marc_xml->as_xml_record();
3499 -- yet another subfield 9 function, this one only adds the $9 and forces
3500 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
3501 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
3502 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
3510 use MARC::File::XML (BinaryEncoding => 'utf8');
3512 binmode(STDERR, ':bytes');
3513 binmode(STDOUT, ':utf8');
3514 binmode(STDERR, ':utf8');
3516 my $marc_xml = shift;
3517 my $new_9_to_set = shift;
3519 $marc_xml =~ s/(<leader>.........)./${1}a/;
3522 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3525 #elog("could not parse $bibid: $@\n");
3526 import MARC::File::XML (BinaryEncoding => 'utf8');
3530 my @uris = $marc_xml->field('856');
3531 return $marc_xml->as_xml_record() unless @uris;
3533 foreach my $field (@uris) {
3534 my $ind1 = $field->indicator('1');
3535 if (!defined $ind1) { next; }
3536 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
3537 my $ind2 = $field->indicator('2');
3538 if (!defined $ind2) { next; }
3539 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
3540 $field->add_subfields( '9' => $new_9_to_set );
3543 return $marc_xml->as_xml_record();
3547 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3548 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3549 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3557 use MARC::File::XML (BinaryEncoding => 'utf8');
3559 binmode(STDERR, ':bytes');
3560 binmode(STDOUT, ':utf8');
3561 binmode(STDERR, ':utf8');
3563 my $marc_xml = shift;
3564 my $matching_u_text = shift;
3565 my $new_9_to_set = shift;
3567 $marc_xml =~ s/(<leader>.........)./${1}a/;
3570 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3573 #elog("could not parse $bibid: $@\n");
3574 import MARC::File::XML (BinaryEncoding => 'utf8');
3578 my @uris = $marc_xml->field('856');
3579 return unless @uris;
3581 foreach my $field (@uris) {
3582 my $sfu = $field->subfield('u');
3583 my $ind2 = $field->indicator('2');
3584 if (!defined $ind2) { next; }
3585 if ($ind2 ne '0') { next; }
3586 if (!defined $sfu) { next; }
3587 if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
3588 $field->add_subfields( '9' => $new_9_to_set );
3593 return $marc_xml->as_xml_record();
3597 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3598 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3607 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3609 SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3612 new_xml = '$_$' || new_xml || '$_$';
3614 IF new_xml != source_xml THEN
3615 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3622 $BODY$ LANGUAGE plpgsql;
3625 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
3626 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
3634 use MARC::File::XML (BinaryEncoding => 'utf8');
3636 binmode(STDERR, ':bytes');
3637 binmode(STDOUT, ':utf8');
3638 binmode(STDERR, ':utf8');
3640 my $marc_xml = shift;
3643 $marc_xml =~ s/(<leader>.........)./${1}a/;
3646 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3649 #elog("could not parse $bibid: $@\n");
3650 import MARC::File::XML (BinaryEncoding => 'utf8');
3654 my @fields = $marc_xml->field($tag);
3655 return $marc_xml->as_xml_record() unless @fields;
3657 $marc_xml->delete_fields(@fields);
3659 return $marc_xml->as_xml_record();
3663 -- convenience function for linking to the item staging table
3665 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3667 table_schema ALIAS FOR $1;
3668 table_name ALIAS FOR $2;
3669 foreign_column_name ALIAS FOR $3;
3670 main_column_name ALIAS FOR $4;
3671 btrim_desired ALIAS FOR $5;
3674 EXECUTE 'SELECT EXISTS (
3676 FROM information_schema.columns
3677 WHERE table_schema = $1
3679 and column_name = $3
3680 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3682 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3685 EXECUTE 'SELECT EXISTS (
3687 FROM information_schema.columns
3688 WHERE table_schema = $1
3689 AND table_name = ''asset_copy_legacy''
3690 and column_name = $2
3691 )' INTO proceed USING table_schema, main_column_name;
3693 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3696 EXECUTE 'ALTER TABLE '
3697 || quote_ident(table_name)
3698 || ' DROP COLUMN IF EXISTS x_item';
3699 EXECUTE 'ALTER TABLE '
3700 || quote_ident(table_name)
3701 || ' ADD COLUMN x_item BIGINT';
3703 IF btrim_desired THEN
3704 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3705 || ' SET x_item = b.id FROM asset_copy_legacy b'
3706 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3707 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3709 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3710 || ' SET x_item = b.id FROM asset_copy_legacy b'
3711 || ' WHERE a.' || quote_ident(foreign_column_name)
3712 || ' = b.' || quote_ident(main_column_name);
3715 --EXECUTE 'SELECT migration_tools.assert(
3716 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3717 -- ''Cannot link every barcode'',
3718 -- ''Every barcode linked''
3722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3724 -- convenience function for linking to the user staging table
3726 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3728 table_schema ALIAS FOR $1;
3729 table_name ALIAS FOR $2;
3730 foreign_column_name ALIAS FOR $3;
3731 main_column_name ALIAS FOR $4;
3732 btrim_desired ALIAS FOR $5;
3735 EXECUTE 'SELECT EXISTS (
3737 FROM information_schema.columns
3738 WHERE table_schema = $1
3740 and column_name = $3
3741 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3743 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3746 EXECUTE 'SELECT EXISTS (
3748 FROM information_schema.columns
3749 WHERE table_schema = $1
3750 AND table_name = ''actor_usr_legacy''
3751 and column_name = $2
3752 )' INTO proceed USING table_schema, main_column_name;
3754 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3757 EXECUTE 'ALTER TABLE '
3758 || quote_ident(table_name)
3759 || ' DROP COLUMN IF EXISTS x_user';
3760 EXECUTE 'ALTER TABLE '
3761 || quote_ident(table_name)
3762 || ' ADD COLUMN x_user INTEGER';
3764 IF btrim_desired THEN
3765 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3766 || ' SET x_user = b.id FROM actor_usr_legacy b'
3767 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3768 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3770 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3771 || ' SET x_user = b.id FROM actor_usr_legacy b'
3772 || ' WHERE a.' || quote_ident(foreign_column_name)
3773 || ' = b.' || quote_ident(main_column_name);
3776 --EXECUTE 'SELECT migration_tools.assert(
3777 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3778 -- ''Cannot link every barcode'',
3779 -- ''Every barcode linked''
3783 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3785 -- convenience function for linking two tables
3786 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3787 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3789 table_schema ALIAS FOR $1;
3790 table_a ALIAS FOR $2;
3791 column_a ALIAS FOR $3;
3792 table_b ALIAS FOR $4;
3793 column_b ALIAS FOR $5;
3794 column_x ALIAS FOR $6;
3795 btrim_desired ALIAS FOR $7;
3798 EXECUTE 'SELECT EXISTS (
3800 FROM information_schema.columns
3801 WHERE table_schema = $1
3803 and column_name = $3
3804 )' INTO proceed USING table_schema, table_a, column_a;
3806 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3809 EXECUTE 'SELECT EXISTS (
3811 FROM information_schema.columns
3812 WHERE table_schema = $1
3814 and column_name = $3
3815 )' INTO proceed USING table_schema, table_b, column_b;
3817 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3820 EXECUTE 'ALTER TABLE '
3821 || quote_ident(table_b)
3822 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3823 EXECUTE 'ALTER TABLE '
3824 || quote_ident(table_b)
3825 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3827 IF btrim_desired THEN
3828 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3829 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3830 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3831 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3833 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3834 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3835 || ' WHERE a.' || quote_ident(column_a)
3836 || ' = b.' || quote_ident(column_b);
3840 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3842 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3843 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3844 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3846 table_schema ALIAS FOR $1;
3847 table_a ALIAS FOR $2;
3848 column_a ALIAS FOR $3;
3849 table_b ALIAS FOR $4;
3850 column_b ALIAS FOR $5;
3851 column_w ALIAS FOR $6;
3852 column_x ALIAS FOR $7;
3853 btrim_desired ALIAS FOR $8;
3856 EXECUTE 'SELECT EXISTS (
3858 FROM information_schema.columns
3859 WHERE table_schema = $1
3861 and column_name = $3
3862 )' INTO proceed USING table_schema, table_a, column_a;
3864 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3867 EXECUTE 'SELECT EXISTS (
3869 FROM information_schema.columns
3870 WHERE table_schema = $1
3872 and column_name = $3
3873 )' INTO proceed USING table_schema, table_b, column_b;
3875 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3878 EXECUTE 'ALTER TABLE '
3879 || quote_ident(table_b)
3880 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3881 EXECUTE 'ALTER TABLE '
3882 || quote_ident(table_b)
3883 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3885 IF btrim_desired THEN
3886 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3887 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3888 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3889 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3891 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3892 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3893 || ' WHERE a.' || quote_ident(column_a)
3894 || ' = b.' || quote_ident(column_b);
3898 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3900 -- convenience function for linking two tables, but copying column w into column x instead of "id". Unlike handle_link2, this one won't drop the target column, and it also doesn't have a final boolean argument for btrim
3901 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
3902 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3904 table_schema ALIAS FOR $1;
3905 table_a ALIAS FOR $2;
3906 column_a ALIAS FOR $3;
3907 table_b ALIAS FOR $4;
3908 column_b ALIAS FOR $5;
3909 column_w ALIAS FOR $6;
3910 column_x ALIAS FOR $7;
3913 EXECUTE 'SELECT EXISTS (
3915 FROM information_schema.columns
3916 WHERE table_schema = $1
3918 and column_name = $3
3919 )' INTO proceed USING table_schema, table_a, column_a;
3921 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3924 EXECUTE 'SELECT EXISTS (
3926 FROM information_schema.columns
3927 WHERE table_schema = $1
3929 and column_name = $3
3930 )' INTO proceed USING table_schema, table_b, column_b;
3932 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3935 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3936 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3937 || ' WHERE a.' || quote_ident(column_a)
3938 || ' = b.' || quote_ident(column_b);
3941 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3943 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3945 table_schema ALIAS FOR $1;
3946 table_a ALIAS FOR $2;
3947 column_a ALIAS FOR $3;
3948 table_b ALIAS FOR $4;
3949 column_b ALIAS FOR $5;
3950 column_w ALIAS FOR $6;
3951 column_x ALIAS FOR $7;
3954 EXECUTE 'SELECT EXISTS (
3956 FROM information_schema.columns
3957 WHERE table_schema = $1
3959 and column_name = $3
3960 )' INTO proceed USING table_schema, table_a, column_a;
3962 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3965 EXECUTE 'SELECT EXISTS (
3967 FROM information_schema.columns
3968 WHERE table_schema = $1
3970 and column_name = $3
3971 )' INTO proceed USING table_schema, table_b, column_b;
3973 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3976 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3977 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3978 || ' WHERE a.' || quote_ident(column_a)
3979 || ' = b.' || quote_ident(column_b)
3980 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
3983 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3985 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3987 table_schema ALIAS FOR $1;
3988 table_a ALIAS FOR $2;
3989 column_a ALIAS FOR $3;
3990 table_b ALIAS FOR $4;
3991 column_b ALIAS FOR $5;
3992 column_w ALIAS FOR $6;
3993 column_x ALIAS FOR $7;
3996 EXECUTE 'SELECT EXISTS (
3998 FROM information_schema.columns
3999 WHERE table_schema = $1
4001 and column_name = $3
4002 )' INTO proceed USING table_schema, table_a, column_a;
4004 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4007 EXECUTE 'SELECT EXISTS (
4009 FROM information_schema.columns
4010 WHERE table_schema = $1
4012 and column_name = $3
4013 )' INTO proceed USING table_schema, table_b, column_b;
4015 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4018 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4019 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4020 || ' WHERE a.' || quote_ident(column_a)
4021 || ' = b.' || quote_ident(column_b)
4022 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4025 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4027 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4029 table_schema ALIAS FOR $1;
4030 table_a ALIAS FOR $2;
4031 column_a ALIAS FOR $3;
4032 table_b ALIAS FOR $4;
4033 column_b ALIAS FOR $5;
4034 column_w ALIAS FOR $6;
4035 column_x ALIAS FOR $7;
4038 EXECUTE 'SELECT EXISTS (
4040 FROM information_schema.columns
4041 WHERE table_schema = $1
4043 and column_name = $3
4044 )' INTO proceed USING table_schema, table_a, column_a;
4046 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4049 EXECUTE 'SELECT EXISTS (
4051 FROM information_schema.columns
4052 WHERE table_schema = $1
4054 and column_name = $3
4055 )' INTO proceed USING table_schema, table_b, column_b;
4057 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4060 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4061 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4062 || ' WHERE a.' || quote_ident(column_a)
4063 || ' = b.' || quote_ident(column_b)
4064 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4067 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4069 -- convenience function for handling desired asset stat cats
4071 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4073 table_schema ALIAS FOR $1;
4074 table_name ALIAS FOR $2;
4075 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4076 org_shortname ALIAS FOR $4;
4084 SELECT 'desired_sc' || field_suffix INTO sc;
4085 SELECT 'desired_sce' || field_suffix INTO sce;
4087 EXECUTE 'SELECT EXISTS (
4089 FROM information_schema.columns
4090 WHERE table_schema = $1
4092 and column_name = $3
4093 )' INTO proceed USING table_schema, table_name, sc;
4095 RAISE EXCEPTION 'Missing column %', sc;
4097 EXECUTE 'SELECT EXISTS (
4099 FROM information_schema.columns
4100 WHERE table_schema = $1
4102 and column_name = $3
4103 )' INTO proceed USING table_schema, table_name, sce;
4105 RAISE EXCEPTION 'Missing column %', sce;
4108 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4110 RAISE EXCEPTION 'Cannot find org by shortname';
4112 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4114 -- caller responsible for their own truncates though we try to prevent duplicates
4115 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4120 ' || quote_ident(table_name) || '
4122 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4126 WHERE owner = ANY ($2)
4127 AND name = BTRIM('||sc||')
4132 WHERE owner = ANY ($2)
4133 AND name = BTRIM('||sc||')
4136 USING org, org_list;
4138 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4143 WHERE owner = ANY ($2)
4144 AND BTRIM('||sc||') = BTRIM(name))
4147 WHERE owner = ANY ($2)
4148 AND BTRIM('||sc||') = BTRIM(name))
4153 ' || quote_ident(table_name) || '
4155 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4156 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4159 FROM asset.stat_cat_entry
4163 WHERE owner = ANY ($2)
4164 AND BTRIM('||sc||') = BTRIM(name)
4165 ) AND value = BTRIM('||sce||')
4169 FROM asset_stat_cat_entry
4173 WHERE owner = ANY ($2)
4174 AND BTRIM('||sc||') = BTRIM(name)
4175 ) AND value = BTRIM('||sce||')
4178 USING org, org_list;
4180 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4182 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4184 table_schema ALIAS FOR $1;
4185 table_name ALIAS FOR $2;
4186 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4187 org_shortname ALIAS FOR $4;
4195 SELECT 'desired_sc' || field_suffix INTO sc;
4196 SELECT 'desired_sce' || field_suffix INTO sce;
4197 EXECUTE 'SELECT EXISTS (
4199 FROM information_schema.columns
4200 WHERE table_schema = $1
4202 and column_name = $3
4203 )' INTO proceed USING table_schema, table_name, sc;
4205 RAISE EXCEPTION 'Missing column %', sc;
4207 EXECUTE 'SELECT EXISTS (
4209 FROM information_schema.columns
4210 WHERE table_schema = $1
4212 and column_name = $3
4213 )' INTO proceed USING table_schema, table_name, sce;
4215 RAISE EXCEPTION 'Missing column %', sce;
4218 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4220 RAISE EXCEPTION 'Cannot find org by shortname';
4223 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4225 EXECUTE 'ALTER TABLE '
4226 || quote_ident(table_name)
4227 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4228 EXECUTE 'ALTER TABLE '
4229 || quote_ident(table_name)
4230 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4231 EXECUTE 'ALTER TABLE '
4232 || quote_ident(table_name)
4233 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4234 EXECUTE 'ALTER TABLE '
4235 || quote_ident(table_name)
4236 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4239 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4241 x_sc' || field_suffix || ' = id
4243 (SELECT id, name, owner FROM asset_stat_cat
4244 UNION SELECT id, name, owner FROM asset.stat_cat) u
4246 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4247 AND u.owner = ANY ($1);'
4250 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4252 x_sce' || field_suffix || ' = id
4254 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4255 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4257 u.stat_cat = x_sc' || field_suffix || '
4258 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4259 AND u.owner = ANY ($1);'
4262 EXECUTE 'SELECT migration_tools.assert(
4263 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4264 ''Cannot find a desired stat cat'',
4265 ''Found all desired stat cats''
4268 EXECUTE 'SELECT migration_tools.assert(
4269 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4270 ''Cannot find a desired stat cat entry'',
4271 ''Found all desired stat cat entries''
4275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4277 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
4278 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4285 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4287 AND table_schema = s_name
4288 AND (data_type='text' OR data_type='character varying')
4289 AND column_name like 'l_%'
4291 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4298 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
4299 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4306 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4308 AND table_schema = s_name
4309 AND (data_type='text' OR data_type='character varying')
4311 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4318 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
4319 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4326 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4328 AND table_schema = s_name
4329 AND (data_type='text' OR data_type='character varying')
4330 AND column_name like 'l_%'
4332 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
4339 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
4340 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4347 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4349 AND table_schema = s_name
4350 AND (data_type='text' OR data_type='character varying')
4352 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');