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.copy_alert,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 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.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 parent_table ALIAS FOR $2;
268 source_table ALIAS FOR $3;
272 column_list TEXT := '';
273 column_count INTEGER := 0;
275 create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
277 SELECT table_schema, table_name, column_name, data_type
278 FROM information_schema.columns
279 WHERE table_schema = migration_schema AND table_name = source_table
281 column_count := column_count + 1;
282 if column_count > 1 then
283 create_sql := create_sql || ', ';
284 column_list := column_list || ', ';
286 create_sql := create_sql || columns.column_name || ' ';
287 if columns.data_type = 'ARRAY' then
288 create_sql := create_sql || 'TEXT[]';
290 create_sql := create_sql || columns.data_type;
292 column_list := column_list || columns.column_name;
294 create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
295 --RAISE INFO 'create_sql = %', create_sql;
297 insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
298 --RAISE INFO 'insert_sql = %', insert_sql;
301 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
303 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
305 migration_schema ALIAS FOR $1;
306 production_tables TEXT[];
308 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
309 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
310 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
311 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
314 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
316 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
318 migration_schema ALIAS FOR $1;
319 production_table ALIAS FOR $2;
320 base_staging_table TEXT;
323 base_staging_table = REPLACE( production_table, '.', '_' );
324 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
325 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
327 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
329 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
331 full_name TEXT := $1;
333 family_name TEXT := '';
334 first_given_name TEXT := '';
335 second_given_name TEXT := '';
339 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
340 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
342 IF suffix = before_comma THEN
346 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
347 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
348 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
350 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
354 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
356 full_name TEXT := $1;
358 family_name TEXT := '';
359 first_given_name TEXT := '';
360 second_given_name TEXT := '';
365 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
366 IF temp ilike '%MR.%' THEN
368 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
370 IF temp ilike '%MRS.%' THEN
372 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
374 IF temp ilike '%MS.%' THEN
376 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
378 IF temp ilike '%DR.%' THEN
380 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
382 IF temp ilike '%JR%' THEN
384 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
386 IF temp ilike '%JR,%' THEN
388 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
390 IF temp ilike '%SR%' THEN
392 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
394 IF temp ilike '%SR,%' THEN
396 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
398 IF temp ~ E'\\sII$' THEN
400 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
402 IF temp ~ E'\\sIII$' THEN
404 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
406 IF temp ~ E'\\sIV$' THEN
408 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
411 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
412 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
413 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
415 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
417 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
419 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
421 full_name TEXT := $1;
423 family_name TEXT := '';
424 first_given_name TEXT := '';
425 second_given_name TEXT := '';
430 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
431 IF temp ilike '%MR.%' THEN
433 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
435 IF temp ilike '%MRS.%' THEN
437 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
439 IF temp ilike '%MS.%' THEN
441 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
443 IF temp ilike '%DR.%' THEN
445 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
447 IF temp ilike '%JR.%' THEN
449 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
451 IF temp ilike '%JR,%' THEN
453 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
455 IF temp ilike '%SR.%' THEN
457 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
459 IF temp ilike '%SR,%' THEN
461 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
463 IF temp like '%III%' THEN
465 temp := REGEXP_REPLACE( temp, E'III', '' );
467 IF temp like '%II%' THEN
469 temp := REGEXP_REPLACE( temp, E'II', '' );
471 IF temp like '%IV%' THEN
473 temp := REGEXP_REPLACE( temp, E'IV', '' );
476 temp := REGEXP_REPLACE( temp, '\(\)', '');
477 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
478 family_name := REGEXP_REPLACE( family_name, ',', '' );
479 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
480 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
481 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
482 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
484 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
486 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
488 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
490 full_name TEXT := $1;
492 family_name TEXT := '';
493 first_given_name TEXT := '';
494 second_given_name TEXT := '';
498 temp := BTRIM(full_name);
499 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
500 --IF temp ~ '^\S{2,}\.' THEN
501 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
502 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
504 --IF temp ~ '\S{2,}\.$' THEN
505 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
506 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
508 IF temp ilike '%MR.%' THEN
510 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
512 IF temp ilike '%MRS.%' THEN
514 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
516 IF temp ilike '%MS.%' THEN
518 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
520 IF temp ilike '%DR.%' THEN
522 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
524 IF temp ilike '%JR.%' THEN
526 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
528 IF temp ilike '%JR,%' THEN
530 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
532 IF temp ilike '%SR.%' THEN
534 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
536 IF temp ilike '%SR,%' THEN
538 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
540 IF temp like '%III%' THEN
542 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
544 IF temp like '%II%' THEN
546 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
550 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
551 temp := BTRIM(REPLACE( temp, family_name, '' ));
552 family_name := REPLACE( family_name, ',', '' );
554 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
555 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
557 first_given_name := temp;
558 second_given_name := '';
561 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
562 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
563 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
564 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
566 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
567 second_given_name := temp;
568 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
572 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
574 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
576 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
578 full_name TEXT := $1;
580 family_name TEXT := '';
581 first_given_name TEXT := '';
582 second_given_name TEXT := '';
586 temp := BTRIM(full_name);
587 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
588 --IF temp ~ '^\S{2,}\.' THEN
589 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
590 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
592 --IF temp ~ '\S{2,}\.$' THEN
593 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
594 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
596 IF temp ilike '%MR.%' THEN
598 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
600 IF temp ilike '%MRS.%' THEN
602 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
604 IF temp ilike '%MS.%' THEN
606 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
608 IF temp ilike '%DR.%' THEN
610 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
612 IF temp ilike '%JR.%' THEN
614 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
616 IF temp ilike '%JR,%' THEN
618 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
620 IF temp ilike '%SR.%' THEN
622 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
624 IF temp ilike '%SR,%' THEN
626 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
628 IF temp like '%III%' THEN
630 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
632 IF temp like '%II%' THEN
634 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
638 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
639 temp := BTRIM(REPLACE( temp, family_name, '' ));
640 family_name := REPLACE( family_name, ',', '' );
642 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
643 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
645 first_given_name := temp;
646 second_given_name := '';
649 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
650 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
651 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
652 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
654 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
655 second_given_name := temp;
656 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
660 family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
661 first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
662 second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
664 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
666 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
670 city_state_zip TEXT := $1;
675 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;
676 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
677 IF city_state_zip ~ ',' THEN
678 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
679 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
681 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
682 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
683 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
685 IF city_state_zip ~ E'^\\S+$' THEN
686 city := city_state_zip;
689 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
690 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
694 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
696 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
698 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
699 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
701 fullstring TEXT := $1;
711 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
712 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
715 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
717 IF fullstring ~ ',' THEN
718 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
719 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
721 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
722 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
723 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
725 IF fullstring ~ E'^\\S+$' THEN
726 scratch1 := fullstring;
729 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
730 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
735 IF scratch1 ~ '[\$]' THEN
736 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
737 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
739 IF scratch1 ~ '\s' THEN
740 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
741 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
748 IF scratch2 ~ '^\d' THEN
749 address1 := scratch2;
752 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
753 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
757 TRIM(BOTH ' ' FROM address1)
758 ,TRIM(BOTH ' ' FROM address2)
759 ,TRIM(BOTH ' ' FROM city)
760 ,TRIM(BOTH ' ' FROM state)
761 ,TRIM(BOTH ' ' FROM zip)
764 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
766 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
769 use Geo::StreetAddress::US;
771 my $a = Geo::StreetAddress::US->parse_location($address);
774 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
775 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
780 $$ LANGUAGE PLPERLU STABLE;
782 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
783 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
784 INSERT INTO migration_tools.usps_suffixes VALUES
817 ('BOULEVARD','BLVD'),
921 ('EXPRESSWAY','EXPY'),
926 ('EXTENSIONS','EXTS'),
1024 ('ISLANDS','SLNDS'),
1037 ('JUNCTIONS','JCTS'),
1097 ('MOTORWAY','MTWY'),
1099 ('MOUNTAINS','MTNS'),
1113 ('OVERPASS','OPAS'),
1118 ('PARKWAYS','PKWY'),
1224 ('STRAVENUE','STRA'),
1244 ('THROUGHWAY','TRWY'),
1251 ('TRAFFICWAY','TRFY'),
1270 ('TURNPIKE','TPKE'),
1272 ('UNDERPASS','UPAS'),
1286 ('VILLAGES','VLGS'),
1317 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1318 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1323 --RAISE INFO 'suffix = %', suffix;
1324 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1325 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1329 $$ LANGUAGE PLPGSQL STRICT STABLE;
1331 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1334 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1338 $$ LANGUAGE PLPGSQL STRICT STABLE;
1340 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1344 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
1345 IF o::BIGINT < t THEN
1352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1354 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1356 migration_schema ALIAS FOR $1;
1360 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1365 $$ LANGUAGE PLPGSQL STRICT STABLE;
1367 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1369 migration_schema ALIAS FOR $1;
1373 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1378 $$ LANGUAGE PLPGSQL STRICT STABLE;
1380 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1382 migration_schema ALIAS FOR $1;
1386 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1391 $$ LANGUAGE PLPGSQL STRICT STABLE;
1393 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1395 migration_schema ALIAS FOR $1;
1399 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1404 $$ LANGUAGE PLPGSQL STRICT STABLE;
1406 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1408 migration_schema ALIAS FOR $1;
1410 patron_table ALIAS FOR $2;
1411 default_patron_profile ALIAS FOR $3;
1414 sql_where1 TEXT := '';
1415 sql_where2 TEXT := '';
1416 sql_where3 TEXT := '';
1419 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1421 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1423 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1424 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);
1425 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);
1426 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);
1427 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,'') || ';';
1428 --RAISE INFO 'sql = %', sql;
1429 PERFORM migration_tools.exec( $1, sql );
1431 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1433 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1435 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1438 $$ LANGUAGE PLPGSQL STRICT STABLE;
1440 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1442 migration_schema ALIAS FOR $1;
1444 item_table ALIAS FOR $2;
1447 sql_where1 TEXT := '';
1448 sql_where2 TEXT := '';
1449 sql_where3 TEXT := '';
1452 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1454 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1456 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 ';
1457 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);
1458 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);
1459 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);
1460 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,'') || ';';
1461 --RAISE INFO 'sql = %', sql;
1462 PERFORM migration_tools.exec( $1, sql );
1465 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1467 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1470 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1472 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1474 migration_schema ALIAS FOR $1;
1475 base_copy_location_map TEXT;
1476 item_table ALIAS FOR $2;
1479 sql_where1 TEXT := '';
1480 sql_where2 TEXT := '';
1481 sql_where3 TEXT := '';
1484 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1486 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1488 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1489 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);
1490 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);
1491 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);
1492 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,'') || ';';
1493 --RAISE INFO 'sql = %', sql;
1494 PERFORM migration_tools.exec( $1, sql );
1497 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1499 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1502 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1504 -- 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
1505 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1507 migration_schema ALIAS FOR $1;
1509 circ_table ALIAS FOR $2;
1510 item_table ALIAS FOR $3;
1511 patron_table ALIAS FOR $4;
1514 sql_where1 TEXT := '';
1515 sql_where2 TEXT := '';
1516 sql_where3 TEXT := '';
1517 sql_where4 TEXT := '';
1520 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1522 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1524 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 ';
1525 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);
1526 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);
1527 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);
1528 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);
1529 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,'') || ';';
1530 --RAISE INFO 'sql = %', sql;
1531 PERFORM migration_tools.exec( $1, sql );
1534 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1536 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1539 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1542 -- $barcode source barcode
1543 -- $prefix prefix to add to barcode, NULL = add no prefix
1544 -- $maxlen maximum length of barcode; default to 14 if left NULL
1545 -- $pad padding string to apply to left of source barcode before adding
1546 -- prefix and suffix; set to NULL or '' if no padding is desired
1547 -- $suffix suffix to add to barcode, NULL = add no suffix
1549 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1550 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1552 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1553 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1556 return unless defined $barcode;
1558 $prefix = '' unless defined $prefix;
1560 $pad = '0' unless defined $pad;
1561 $suffix = '' unless defined $suffix;
1563 # bail out if adding prefix and suffix would bring new barcode over max length
1564 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1566 my $new_barcode = $barcode;
1568 my $pad_length = $maxlen - length($prefix) - length($suffix);
1569 if (length($barcode) < $pad_length) {
1570 # assuming we always want padding on the left
1571 # also assuming that it is possible to have the pad string be longer than 1 character
1572 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1576 # bail out if adding prefix and suffix would bring new barcode over max length
1577 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1579 return "$prefix$new_barcode$suffix";
1580 $$ LANGUAGE PLPERLU STABLE;
1582 -- remove previous version of this function
1583 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1585 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1587 attempt_value ALIAS FOR $1;
1588 datatype ALIAS FOR $2;
1590 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1591 RETURN attempt_value;
1593 WHEN OTHERS THEN RETURN NULL;
1595 $$ LANGUAGE PLPGSQL STRICT STABLE;
1597 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1599 attempt_value ALIAS FOR $1;
1600 fail_value ALIAS FOR $2;
1604 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1611 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1616 $$ LANGUAGE PLPGSQL STRICT STABLE;
1618 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1620 attempt_value ALIAS FOR $1;
1621 fail_value ALIAS FOR $2;
1625 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1632 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1637 $$ LANGUAGE PLPGSQL STRICT STABLE;
1639 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1641 attempt_value ALIAS FOR $1;
1642 fail_value ALIAS FOR $2;
1646 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1653 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1658 $$ LANGUAGE PLPGSQL STRICT STABLE;
1660 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1662 attempt_value ALIAS FOR $1;
1663 fail_value ALIAS FOR $2;
1666 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1671 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1676 $$ LANGUAGE PLPGSQL STRICT STABLE;
1678 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1680 attempt_value ALIAS FOR $1;
1681 fail_value ALIAS FOR $2;
1682 output NUMERIC(8,2);
1685 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1692 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1697 $$ LANGUAGE PLPGSQL STRICT STABLE;
1699 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1701 attempt_value ALIAS FOR $1;
1702 fail_value ALIAS FOR $2;
1703 output NUMERIC(6,2);
1706 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1713 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1718 $$ LANGUAGE PLPGSQL STRICT STABLE;
1720 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1722 attempt_value ALIAS FOR $1;
1723 fail_value ALIAS FOR $2;
1724 output NUMERIC(8,2);
1726 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1727 RAISE EXCEPTION 'too many digits';
1730 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;'
1737 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1742 $$ LANGUAGE PLPGSQL STRICT STABLE;
1744 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1746 attempt_value ALIAS FOR $1;
1747 fail_value ALIAS FOR $2;
1748 output NUMERIC(6,2);
1750 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1751 RAISE EXCEPTION 'too many digits';
1754 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;'
1761 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1766 $$ LANGUAGE PLPGSQL STRICT STABLE;
1768 -- add_codabar_checkdigit
1769 -- $barcode source barcode
1771 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1772 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1773 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1774 -- input string does not meet those requirements, it is returned unchanged.
1776 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1777 my $barcode = shift;
1779 return $barcode if $barcode !~ /^\d{13,14}$/;
1780 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1781 my @digits = split //, $barcode;
1783 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1784 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1785 my $remainder = $total % 10;
1786 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1787 return $barcode . $checkdigit;
1788 $$ LANGUAGE PLPERLU STRICT STABLE;
1790 -- add_code39mod43_checkdigit
1791 -- $barcode source barcode
1793 -- If the source string is 13 or 14 characters long and contains only valid
1794 -- Code 39 mod 43 characters, adds or replaces the 14th
1795 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1796 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1797 -- input string does not meet those requirements, it is returned unchanged.
1799 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1800 my $barcode = shift;
1802 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1803 $barcode = substr($barcode, 0, 13); # ignore 14th character
1805 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1806 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1809 $total += $nums{$_} foreach split(//, $barcode);
1810 my $remainder = $total % 43;
1811 my $checkdigit = $valid_chars[$remainder];
1812 return $barcode . $checkdigit;
1813 $$ LANGUAGE PLPERLU STRICT STABLE;
1815 -- add_mod16_checkdigit
1816 -- $barcode source barcode
1818 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1820 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1821 my $barcode = shift;
1823 my @digits = split //, $barcode;
1825 foreach $digit (@digits) {
1826 if ($digit =~ /[0-9]/) { $total += $digit;
1827 } elsif ($digit eq '-') { $total += 10;
1828 } elsif ($digit eq '$') { $total += 11;
1829 } elsif ($digit eq ':') { $total += 12;
1830 } elsif ($digit eq '/') { $total += 13;
1831 } elsif ($digit eq '.') { $total += 14;
1832 } elsif ($digit eq '+') { $total += 15;
1833 } elsif ($digit eq 'A') { $total += 16;
1834 } elsif ($digit eq 'B') { $total += 17;
1835 } elsif ($digit eq 'C') { $total += 18;
1836 } elsif ($digit eq 'D') { $total += 19;
1837 } else { die "invalid digit <$digit>";
1840 my $remainder = $total % 16;
1841 my $difference = 16 - $remainder;
1843 if ($difference < 10) { $checkdigit = $difference;
1844 } elsif ($difference == 10) { $checkdigit = '-';
1845 } elsif ($difference == 11) { $checkdigit = '$';
1846 } elsif ($difference == 12) { $checkdigit = ':';
1847 } elsif ($difference == 13) { $checkdigit = '/';
1848 } elsif ($difference == 14) { $checkdigit = '.';
1849 } elsif ($difference == 15) { $checkdigit = '+';
1850 } else { die "error calculating checkdigit";
1853 return $barcode . $checkdigit;
1854 $$ LANGUAGE PLPERLU STRICT STABLE;
1856 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1859 areacode TEXT := $2;
1862 n_digits INTEGER := 0;
1865 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1866 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1867 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1868 IF n_digits = 7 AND areacode <> '' THEN
1869 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1870 output := (areacode || '-' || temp);
1877 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1879 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1880 my ($marcxml, $pos, $value) = @_;
1883 use MARC::File::XML;
1887 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1888 my $leader = $marc->leader();
1889 substr($leader, $pos, 1) = $value;
1890 $marc->leader($leader);
1891 $xml = $marc->as_xml_record;
1892 $xml =~ s/^<\?.+?\?>$//mo;
1894 $xml =~ s/>\s+</></sgo;
1897 $$ LANGUAGE PLPERLU STABLE;
1899 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1900 my ($marcxml, $pos, $value) = @_;
1903 use MARC::File::XML;
1907 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1908 my $f008 = $marc->field('008');
1911 my $field = $f008->data();
1912 substr($field, $pos, 1) = $value;
1913 $f008->update($field);
1914 $xml = $marc->as_xml_record;
1915 $xml =~ s/^<\?.+?\?>$//mo;
1917 $xml =~ s/>\s+</></sgo;
1921 $$ LANGUAGE PLPERLU STABLE;
1924 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1926 profile ALIAS FOR $1;
1928 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1930 $$ LANGUAGE PLPGSQL STRICT STABLE;
1933 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1935 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1937 $$ LANGUAGE PLPGSQL STRICT STABLE;
1940 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1942 my ($marcxml, $tags) = @_;
1945 use MARC::File::XML;
1950 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1951 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1953 my @incumbents = ();
1955 foreach my $field ( $marc->fields() ) {
1956 push @incumbents, $field->as_formatted();
1959 foreach $field ( $to_insert->fields() ) {
1960 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1961 $marc->insert_fields_ordered( ($field) );
1965 $xml = $marc->as_xml_record;
1966 $xml =~ s/^<\?.+?\?>$//mo;
1968 $xml =~ s/>\s+</></sgo;
1973 $$ LANGUAGE PLPERLU STABLE;
1975 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1979 -- First make sure the circ matrix is loaded and the circulations
1980 -- have been staged to the extent possible (but at the very least
1981 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1982 -- circ modifiers must also be in place.
1984 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1992 this_duration_rule INT;
1994 this_max_fine_rule INT;
1995 rcd config.rule_circ_duration%ROWTYPE;
1996 rrf config.rule_recurring_fine%ROWTYPE;
1997 rmf config.rule_max_fine%ROWTYPE;
2004 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2006 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2008 -- Fetch the correct rules for this circulation
2015 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2018 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2019 INTO circ_lib, target_copy, usr, is_renewal ;
2021 INTO this_duration_rule,
2025 recurring_fine_rule,
2027 FROM action.item_user_circ_test(
2033 SELECT INTO rcd * FROM config.rule_circ_duration
2034 WHERE id = this_duration_rule;
2035 SELECT INTO rrf * FROM config.rule_recurring_fine
2036 WHERE id = this_fine_rule;
2037 SELECT INTO rmf * FROM config.rule_max_fine
2038 WHERE id = this_max_fine_rule;
2040 -- Apply the rules to this circulation
2041 EXECUTE ('UPDATE ' || tablename || ' c
2043 duration_rule = rcd.name,
2044 recurring_fine_rule = rrf.name,
2045 max_fine_rule = rmf.name,
2046 duration = rcd.normal,
2047 recurring_fine = rrf.normal,
2050 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2053 renewal_remaining = rcd.max_renewals
2055 config.rule_circ_duration rcd,
2056 config.rule_recurring_fine rrf,
2057 config.rule_max_fine rmf,
2060 rcd.id = ' || this_duration_rule || ' AND
2061 rrf.id = ' || this_fine_rule || ' AND
2062 rmf.id = ' || this_max_fine_rule || ' AND
2063 ac.id = c.target_copy AND
2064 c.id = ' || circ || ';');
2066 -- Keep track of where we are in the process
2068 IF (n % 100 = 0) THEN
2069 RAISE INFO '%', n || ' of ' || n_circs
2070 || ' (' || (100*n/n_circs) || '%) circs updated.';
2078 $$ LANGUAGE plpgsql;
2080 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2084 -- First make sure the circ matrix is loaded and the circulations
2085 -- have been staged to the extent possible (but at the very least
2086 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2087 -- circ modifiers must also be in place.
2089 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2097 this_duration_rule INT;
2099 this_max_fine_rule INT;
2100 rcd config.rule_circ_duration%ROWTYPE;
2101 rrf config.rule_recurring_fine%ROWTYPE;
2102 rmf config.rule_max_fine%ROWTYPE;
2109 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2111 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2113 -- Fetch the correct rules for this circulation
2120 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2123 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2124 INTO circ_lib, target_copy, usr, is_renewal ;
2126 INTO this_duration_rule,
2132 FROM action.find_circ_matrix_matchpoint(
2138 SELECT INTO rcd * FROM config.rule_circ_duration
2139 WHERE id = this_duration_rule;
2140 SELECT INTO rrf * FROM config.rule_recurring_fine
2141 WHERE id = this_fine_rule;
2142 SELECT INTO rmf * FROM config.rule_max_fine
2143 WHERE id = this_max_fine_rule;
2145 -- Apply the rules to this circulation
2146 EXECUTE ('UPDATE ' || tablename || ' c
2148 duration_rule = rcd.name,
2149 recuring_fine_rule = rrf.name,
2150 max_fine_rule = rmf.name,
2151 duration = rcd.normal,
2152 recuring_fine = rrf.normal,
2155 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2158 renewal_remaining = rcd.max_renewals
2160 config.rule_circ_duration rcd,
2161 config.rule_recuring_fine rrf,
2162 config.rule_max_fine rmf,
2165 rcd.id = ' || this_duration_rule || ' AND
2166 rrf.id = ' || this_fine_rule || ' AND
2167 rmf.id = ' || this_max_fine_rule || ' AND
2168 ac.id = c.target_copy AND
2169 c.id = ' || circ || ';');
2171 -- Keep track of where we are in the process
2173 IF (n % 100 = 0) THEN
2174 RAISE INFO '%', n || ' of ' || n_circs
2175 || ' (' || (100*n/n_circs) || '%) circs updated.';
2183 $$ LANGUAGE plpgsql;
2185 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2189 -- First make sure the circ matrix is loaded and the circulations
2190 -- have been staged to the extent possible (but at the very least
2191 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2192 -- circ modifiers must also be in place.
2194 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2202 this_duration_rule INT;
2204 this_max_fine_rule INT;
2205 rcd config.rule_circ_duration%ROWTYPE;
2206 rrf config.rule_recurring_fine%ROWTYPE;
2207 rmf config.rule_max_fine%ROWTYPE;
2214 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2216 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2218 -- Fetch the correct rules for this circulation
2225 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2228 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2229 INTO circ_lib, target_copy, usr, is_renewal ;
2231 INTO this_duration_rule,
2234 (matchpoint).duration_rule,
2235 (matchpoint).recurring_fine_rule,
2236 (matchpoint).max_fine_rule
2237 FROM action.find_circ_matrix_matchpoint(
2243 SELECT INTO rcd * FROM config.rule_circ_duration
2244 WHERE id = this_duration_rule;
2245 SELECT INTO rrf * FROM config.rule_recurring_fine
2246 WHERE id = this_fine_rule;
2247 SELECT INTO rmf * FROM config.rule_max_fine
2248 WHERE id = this_max_fine_rule;
2250 -- Apply the rules to this circulation
2251 EXECUTE ('UPDATE ' || tablename || ' c
2253 duration_rule = rcd.name,
2254 recurring_fine_rule = rrf.name,
2255 max_fine_rule = rmf.name,
2256 duration = rcd.normal,
2257 recurring_fine = rrf.normal,
2260 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2263 renewal_remaining = rcd.max_renewals,
2264 grace_period = rrf.grace_period
2266 config.rule_circ_duration rcd,
2267 config.rule_recurring_fine rrf,
2268 config.rule_max_fine rmf,
2271 rcd.id = ' || this_duration_rule || ' AND
2272 rrf.id = ' || this_fine_rule || ' AND
2273 rmf.id = ' || this_max_fine_rule || ' AND
2274 ac.id = c.target_copy AND
2275 c.id = ' || circ || ';');
2277 -- Keep track of where we are in the process
2279 IF (n % 100 = 0) THEN
2280 RAISE INFO '%', n || ' of ' || n_circs
2281 || ' (' || (100*n/n_circs) || '%) circs updated.';
2289 $$ LANGUAGE plpgsql;
2291 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2292 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2293 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2296 charge_lost_on_zero BOOLEAN;
2299 default_price NUMERIC;
2300 working_price NUMERIC;
2304 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2305 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2307 SELECT INTO charge_lost_on_zero value
2308 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2310 SELECT INTO min_price value
2311 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2313 SELECT INTO max_price value
2314 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2316 SELECT INTO default_price value
2317 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2319 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2321 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2322 working_price := default_price;
2325 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2326 working_price := max_price;
2329 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2330 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2331 working_price := min_price;
2335 RETURN working_price;
2339 $$ LANGUAGE plpgsql;
2341 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2345 -- First make sure the circ matrix is loaded and the circulations
2346 -- have been staged to the extent possible (but at the very least
2347 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2348 -- circ modifiers must also be in place.
2350 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2358 this_duration_rule INT;
2360 this_max_fine_rule INT;
2361 rcd config.rule_circ_duration%ROWTYPE;
2362 rrf config.rule_recurring_fine%ROWTYPE;
2363 rmf config.rule_max_fine%ROWTYPE;
2369 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2371 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2373 -- Fetch the correct rules for this circulation
2380 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2383 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2384 INTO circ_lib, target_copy, usr, is_renewal ;
2386 INTO this_duration_rule,
2389 (matchpoint).duration_rule,
2390 (matchpoint).recurring_fine_rule,
2391 (matchpoint).max_fine_rule
2392 FROM action.find_circ_matrix_matchpoint(
2398 SELECT INTO rcd * FROM config.rule_circ_duration
2399 WHERE id = this_duration_rule;
2400 SELECT INTO rrf * FROM config.rule_recurring_fine
2401 WHERE id = this_fine_rule;
2402 SELECT INTO rmf * FROM config.rule_max_fine
2403 WHERE id = this_max_fine_rule;
2405 -- Apply the rules to this circulation
2406 EXECUTE ('UPDATE ' || tablename || ' c
2408 duration_rule = rcd.name,
2409 recurring_fine_rule = rrf.name,
2410 max_fine_rule = rmf.name,
2411 duration = rcd.normal,
2412 recurring_fine = rrf.normal,
2415 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2418 renewal_remaining = rcd.max_renewals,
2419 grace_period = rrf.grace_period
2421 config.rule_circ_duration rcd,
2422 config.rule_recurring_fine rrf,
2423 config.rule_max_fine rmf,
2426 rcd.id = ' || this_duration_rule || ' AND
2427 rrf.id = ' || this_fine_rule || ' AND
2428 rmf.id = ' || this_max_fine_rule || ' AND
2429 ac.id = c.target_copy AND
2430 c.id = ' || circ || ';');
2432 -- Keep track of where we are in the process
2434 IF (n % 100 = 0) THEN
2435 RAISE INFO '%', n || ' of ' || n_circs
2436 || ' (' || (100*n/n_circs) || '%) circs updated.';
2444 $$ LANGUAGE plpgsql;
2449 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2451 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2452 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2454 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2455 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2458 c TEXT := schemaname || '.asset_copy_legacy';
2459 sc TEXT := schemaname || '.asset_stat_cat';
2460 sce TEXT := schemaname || '.asset_stat_cat_entry';
2461 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2467 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2469 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2471 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2472 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2473 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2480 $$ LANGUAGE plpgsql;
2482 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2484 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2485 -- This will assign standing penalties as needed.
2493 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2495 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2497 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2507 $$ LANGUAGE plpgsql;
2510 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2513 INSERT INTO metabib.metarecord (fingerprint, master_record)
2514 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2515 FROM biblio.record_entry b
2517 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)
2518 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2519 ORDER BY b.fingerprint, b.quality DESC;
2520 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2522 FROM biblio.record_entry r
2523 JOIN metabib.metarecord m USING (fingerprint)
2524 WHERE NOT r.deleted;
2527 $$ LANGUAGE plpgsql;
2530 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2533 INSERT INTO metabib.metarecord (fingerprint, master_record)
2534 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2535 FROM biblio.record_entry b
2537 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)
2538 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2539 ORDER BY b.fingerprint, b.quality DESC;
2540 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2542 FROM biblio.record_entry r
2543 JOIN metabib.metarecord m USING (fingerprint)
2545 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);
2548 $$ LANGUAGE plpgsql;
2551 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2553 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2554 -- Then SELECT migration_tools.create_cards('m_foo');
2557 u TEXT := schemaname || '.actor_usr_legacy';
2558 c TEXT := schemaname || '.actor_card';
2562 EXECUTE ('DELETE FROM ' || c || ';');
2563 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2564 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2570 $$ LANGUAGE plpgsql;
2573 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2575 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2577 my ($marcxml, $shortname) = @_;
2580 use MARC::File::XML;
2585 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2587 foreach my $field ( $marc->field('856') ) {
2588 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2589 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2590 $field->add_subfields( '9' => $shortname );
2591 $field->update( ind2 => '0');
2595 $xml = $marc->as_xml_record;
2596 $xml =~ s/^<\?.+?\?>$//mo;
2598 $xml =~ s/>\s+</></sgo;
2603 $$ LANGUAGE PLPERLU STABLE;
2605 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2607 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2609 my ($marcxml, $shortname) = @_;
2612 use MARC::File::XML;
2617 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2619 foreach my $field ( $marc->field('856') ) {
2620 if ( ! $field->as_string('9') ) {
2621 $field->add_subfields( '9' => $shortname );
2625 $xml = $marc->as_xml_record;
2626 $xml =~ s/^<\?.+?\?>$//mo;
2628 $xml =~ s/>\s+</></sgo;
2633 $$ LANGUAGE PLPERLU STABLE;
2636 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2648 -- Bail out if asked to change the label to ##URI##
2649 IF new_label = '##URI##' THEN
2653 -- Gather information
2654 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2655 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2656 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2658 -- Bail out if the label already is ##URI##
2659 IF old_label = '##URI##' THEN
2663 -- Bail out if the call number label is already correct
2664 IF new_volume = old_volume THEN
2668 -- Check whether we already have a destination volume available
2669 SELECT id INTO new_volume FROM asset.call_number
2672 owning_lib = owner AND
2673 label = new_label AND
2676 -- Create destination volume if needed
2678 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2679 VALUES (1, 1, bib, owner, new_label, cn_class);
2680 SELECT id INTO new_volume FROM asset.call_number
2683 owning_lib = owner AND
2684 label = new_label AND
2688 -- Move copy to destination
2689 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2691 -- Delete source volume if it is now empty
2692 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2694 DELETE FROM asset.call_number WHERE id = old_volume;
2699 $$ LANGUAGE plpgsql;
2701 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2706 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2710 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2711 $zipdata{$zip} = [$city, $state, $county];
2714 if (defined $zipdata{$input}) {
2715 my ($city, $state, $county) = @{$zipdata{$input}};
2716 return [$city, $state, $county];
2717 } elsif (defined $zipdata{substr $input, 0, 5}) {
2718 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2719 return [$city, $state, $county];
2721 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2724 $$ LANGUAGE PLPERLU STABLE;
2726 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2733 errors_found BOOLEAN;
2735 parent_shortname TEXT;
2741 type_parent_depth INT;
2746 errors_found := FALSE;
2748 -- Checking actor.org_unit_type
2750 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2752 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2753 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2755 IF type_parent IS NOT NULL THEN
2757 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2759 IF type_depth - type_parent_depth <> 1 THEN
2760 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2761 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2762 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2763 ou_type_name, type_depth, parent_type, type_parent_depth;
2764 errors_found := TRUE;
2772 -- Checking actor.org_unit
2774 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2776 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2777 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;
2778 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;
2779 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2780 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2781 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;
2782 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;
2784 IF ou_parent IS NOT NULL THEN
2786 IF (org_unit_depth - parent_depth <> 1) OR (
2787 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2789 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2790 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2791 errors_found := TRUE;
2798 IF NOT errors_found THEN
2799 RAISE INFO 'No errors found.';
2806 $$ LANGUAGE plpgsql;
2809 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2813 DELETE FROM asset.opac_visible_copies;
2815 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2817 cp.id, cp.circ_lib, cn.record
2820 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2821 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2822 JOIN asset.copy_location cl ON (cp.location = cl.id)
2823 JOIN config.copy_status cs ON (cp.status = cs.id)
2824 JOIN biblio.record_entry b ON (cn.record = b.id)
2833 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2837 $$ LANGUAGE plpgsql;
2840 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2846 old_owning_lib INTEGER;
2852 -- Gather information
2853 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2854 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2855 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2857 -- Bail out if the new_owning_lib is not the ID of an org_unit
2858 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2860 '% is not a valid actor.org_unit ID; no change made.',
2865 -- Bail out discreetly if the owning_lib is already correct
2866 IF new_owning_lib = old_owning_lib THEN
2870 -- Check whether we already have a destination volume available
2871 SELECT id INTO new_volume FROM asset.call_number
2874 owning_lib = new_owning_lib AND
2875 label = old_label AND
2878 -- Create destination volume if needed
2880 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2881 VALUES (1, 1, bib, new_owning_lib, old_label);
2882 SELECT id INTO new_volume FROM asset.call_number
2885 owning_lib = new_owning_lib AND
2886 label = old_label AND
2890 -- Move copy to destination
2891 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2893 -- Delete source volume if it is now empty
2894 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2896 DELETE FROM asset.call_number WHERE id = old_volume;
2901 $$ LANGUAGE plpgsql;
2904 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2906 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2909 new_owning_lib INTEGER;
2913 -- Parse the new_owner as an org unit ID or shortname
2914 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2915 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2916 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2917 ELSIF new_owner ~ E'^[0-9]+$' THEN
2918 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2921 E'You don\'t need to put the actor.org_unit ID in quotes; '
2922 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2923 new_owning_lib := new_owner::INTEGER;
2924 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2928 '% is not a valid actor.org_unit shortname or ID; no change made.',
2935 $$ LANGUAGE plpgsql;
2937 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2940 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2943 MARC::Charset->assume_unicode(1);
2948 my $r = MARC::Record->new_from_xml( $xml );
2949 my $output_xml = $r->as_xml_record();
2957 $func$ LANGUAGE PLPERLU;
2958 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2960 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2962 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2963 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2964 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2965 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2966 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2967 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2968 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2969 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2970 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2971 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2972 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2973 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2974 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2975 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2976 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2977 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2978 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2979 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2980 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2981 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2982 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2983 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2984 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2985 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2986 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2987 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2989 $FUNC$ LANGUAGE PLPGSQL;
2991 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2993 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2994 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2995 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2996 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2997 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2998 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2999 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
3001 -- import any new circ rules
3002 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3003 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3004 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3005 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3007 -- and permission groups
3008 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3011 $FUNC$ LANGUAGE PLPGSQL;
3014 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$
3023 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3024 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3025 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
3026 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3027 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3028 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3029 FOR name IN EXECUTE loopq LOOP
3030 EXECUTE existsq INTO ct USING name;
3032 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3033 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3034 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3035 EXECUTE copyst USING name;
3039 $FUNC$ LANGUAGE PLPGSQL;
3041 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3048 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3049 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
3050 FOR id IN EXECUTE loopq USING delimiter LOOP
3051 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3052 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3053 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3054 EXECUTE splitst USING id, delimiter;
3057 $FUNC$ LANGUAGE PLPGSQL;
3059 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3065 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3068 MARC::Charset->assume_unicode(1);
3070 my $target_xml = shift;
3071 my $source_xml = shift;
3077 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3081 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3086 my $source_id = $source->subfield('901', 'c');
3087 $source_id = $source->subfield('903', 'a') unless $source_id;
3088 my $target_id = $target->subfield('901', 'c');
3089 $target_id = $target->subfield('903', 'a') unless $target_id;
3091 my %existing_fields;
3092 foreach my $tag (@$tags) {
3093 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3094 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3095 $target->insert_fields_ordered(map { $_->clone() } @to_add);
3097 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3101 my $xml = $target->as_xml_record;
3102 $xml =~ s/^<\?.+?\?>$//mo;
3104 $xml =~ s/>\s+</></sgo;
3108 $func$ LANGUAGE PLPERLU;
3109 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.';
3111 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3117 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3120 my $in_tags = shift;
3121 my $in_values = shift;
3123 # hack-and-slash parsing of array-passed-as-string;
3124 # this can go away once everybody is running Postgres 9.1+
3125 my $csv = Text::CSV->new({binary => 1});
3128 my $status = $csv->parse($in_tags);
3129 my $tags = [ $csv->fields() ];
3130 $in_values =~ s/^{//;
3131 $in_values =~ s/}$//;
3132 $status = $csv->parse($in_values);
3133 my $values = [ $csv->fields() ];
3135 my $marc = MARC::Record->new();
3137 $marc->leader('00000nam a22000007 4500');
3138 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3140 foreach my $i (0..$#$tags) {
3142 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3145 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3146 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3148 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3152 my $xml = $marc->as_xml_record;
3153 $xml =~ s/^<\?.+?\?>$//mo;
3155 $xml =~ s/>\s+</></sgo;
3159 $func$ LANGUAGE PLPERLU;
3160 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3161 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3162 The second argument is an array of text containing the values to plug into each field.
3163 If the value for a given field is NULL or the empty string, it is not inserted.
3166 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3172 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3175 my $in_tags = shift;
3176 my $in_ind1 = shift;
3177 my $in_ind2 = shift;
3178 my $in_values = shift;
3180 # hack-and-slash parsing of array-passed-as-string;
3181 # this can go away once everybody is running Postgres 9.1+
3182 my $csv = Text::CSV->new({binary => 1});
3185 my $status = $csv->parse($in_tags);
3186 my $tags = [ $csv->fields() ];
3189 $status = $csv->parse($in_ind1);
3190 my $ind1s = [ $csv->fields() ];
3193 $status = $csv->parse($in_ind2);
3194 my $ind2s = [ $csv->fields() ];
3195 $in_values =~ s/^{//;
3196 $in_values =~ s/}$//;
3197 $status = $csv->parse($in_values);
3198 my $values = [ $csv->fields() ];
3200 my $marc = MARC::Record->new();
3202 $marc->leader('00000nam a22000007 4500');
3203 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3205 foreach my $i (0..$#$tags) {
3207 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3210 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3211 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3213 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3217 my $xml = $marc->as_xml_record;
3218 $xml =~ s/^<\?.+?\?>$//mo;
3220 $xml =~ s/>\s+</></sgo;
3224 $func$ LANGUAGE PLPERLU;
3225 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3226 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3227 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3228 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3229 The fourth argument is an array of text containing the values to plug into each field.
3230 If the value for a given field is NULL or the empty string, it is not inserted.
3233 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3235 my ($marcxml, $tag, $pos, $value) = @_;
3238 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3242 MARC::Charset->assume_unicode(1);
3244 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3245 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3246 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3247 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3251 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3253 foreach my $field ($marc->field($tag)) {
3254 $field->update("ind$pos" => $value);
3256 $xml = $marc->as_xml_record;
3257 $xml =~ s/^<\?.+?\?>$//mo;
3259 $xml =~ s/>\s+</></sgo;
3263 $func$ LANGUAGE PLPERLU;
3265 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3266 The first argument is a MARCXML string.
3267 The second argument is a MARC tag.
3268 The third argument is the indicator position, either 1 or 2.
3269 The fourth argument is the character to set the indicator value to.
3270 All occurences of the specified field will be changed.
3271 The function returns the revised MARCXML string.$$;
3273 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3278 first_name TEXT DEFAULT '',
3279 last_name TEXT DEFAULT ''
3280 ) RETURNS VOID AS $func$
3282 RAISE NOTICE '%', org ;
3283 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3284 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3285 FROM actor.org_unit aou, permission.grp_tree pgt
3286 WHERE aou.shortname = org
3287 AND pgt.name = perm_group;
3292 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3293 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3295 target_event_def ALIAS FOR $1;
3298 DROP TABLE IF EXISTS new_atevdefs;
3299 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3300 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3301 INSERT INTO action_trigger.event_definition (
3322 ,name || ' (clone of '||target_event_def||')'
3338 action_trigger.event_definition
3340 id = target_event_def
3342 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3343 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3344 INSERT INTO action_trigger.environment (
3350 currval('action_trigger.event_definition_id_seq')
3355 action_trigger.environment
3357 event_def = target_event_def
3359 INSERT INTO action_trigger.event_params (
3364 currval('action_trigger.event_definition_id_seq')
3368 action_trigger.event_params
3370 event_def = target_event_def
3373 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);
3375 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3377 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3378 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3380 target_event_def ALIAS FOR $1;
3382 new_interval ALIAS FOR $3;
3384 DROP TABLE IF EXISTS new_atevdefs;
3385 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3386 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3387 INSERT INTO action_trigger.event_definition (
3408 ,name || ' (clone of '||target_event_def||')'
3424 action_trigger.event_definition
3426 id = target_event_def
3428 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3429 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3430 INSERT INTO action_trigger.environment (
3436 currval('action_trigger.event_definition_id_seq')
3441 action_trigger.environment
3443 event_def = target_event_def
3445 INSERT INTO action_trigger.event_params (
3450 currval('action_trigger.event_definition_id_seq')
3454 action_trigger.event_params
3456 event_def = target_event_def
3459 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);
3461 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3463 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3464 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3467 target_event_defs ALIAS FOR $2;
3469 DROP TABLE IF EXISTS new_atevdefs;
3470 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3471 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3472 INSERT INTO action_trigger.event_definition (
3493 ,name || ' (clone of '||target_event_defs[i]||')'
3509 action_trigger.event_definition
3511 id = target_event_defs[i]
3513 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3514 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3515 INSERT INTO action_trigger.environment (
3521 currval('action_trigger.event_definition_id_seq')
3526 action_trigger.environment
3528 event_def = target_event_defs[i]
3530 INSERT INTO action_trigger.event_params (
3535 currval('action_trigger.event_definition_id_seq')
3539 action_trigger.event_params
3541 event_def = target_event_defs[i]
3544 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3546 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3548 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3550 action_trigger.event
3554 ,complete_time = NULL
3555 ,update_process = NULL
3557 ,template_output = NULL
3558 ,error_output = NULL
3559 ,async_output = NULL
3564 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3568 use MARC::File::XML;
3573 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3574 $field = $marc->leader();
3577 $$ LANGUAGE PLPERLU STABLE;
3579 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3580 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3583 use MARC::File::XML;
3588 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3589 $field = $marc->field($tag);
3591 return $field->as_string($subfield,$delimiter);
3592 $$ LANGUAGE PLPERLU STABLE;
3594 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3595 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3598 use MARC::File::XML;
3603 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3604 @fields = $marc->field($tag);
3607 foreach my $field (@fields) {
3608 push @texts, $field->as_string($subfield,$delimiter);
3611 $$ LANGUAGE PLPERLU STABLE;
3613 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3614 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3617 use MARC::File::XML;
3622 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3623 @fields = $marc->field($tag);
3626 foreach my $field (@fields) {
3627 if ($field->as_string() =~ qr/$match/) {
3628 push @texts, $field->as_string($subfield,$delimiter);
3632 $$ LANGUAGE PLPERLU STABLE;
3634 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3635 SELECT action.find_hold_matrix_matchpoint(
3636 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3637 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3638 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3639 (SELECT usr FROM action.hold_request WHERE id = $1),
3640 (SELECT requestor FROM action.hold_request WHERE id = $1)
3644 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3645 SELECT action.hold_request_permit_test(
3646 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3647 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3648 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3649 (SELECT usr FROM action.hold_request WHERE id = $1),
3650 (SELECT requestor FROM action.hold_request WHERE id = $1)
3654 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3655 SELECT action.find_circ_matrix_matchpoint(
3656 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3657 (SELECT target_copy FROM action.circulation WHERE id = $1),
3658 (SELECT usr FROM action.circulation WHERE id = $1),
3660 NULLIF(phone_renewal,false),
3661 NULLIF(desk_renewal,false),
3662 NULLIF(opac_renewal,false),
3664 ) FROM action.circulation WHERE id = $1
3669 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3674 RAISE EXCEPTION 'assertion';
3677 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3679 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3685 RAISE EXCEPTION '%', msg;
3688 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3690 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3693 fail_msg ALIAS FOR $2;
3694 success_msg ALIAS FOR $3;
3697 RAISE EXCEPTION '%', fail_msg;
3701 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3703 -- push bib sequence and return starting value for reserved range
3704 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3706 bib_count ALIAS FOR $1;
3709 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3711 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3716 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3718 -- set a new salted password
3720 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3722 usr_id ALIAS FOR $1;
3723 plain_passwd ALIAS FOR $2;
3728 SELECT actor.create_salt('main') INTO plain_salt;
3730 SELECT MD5(plain_passwd) INTO md5_passwd;
3732 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3737 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3740 -- convenience functions for handling copy_location maps
3741 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3742 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3745 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3747 table_schema ALIAS FOR $1;
3748 table_name ALIAS FOR $2;
3749 org_shortname ALIAS FOR $3;
3750 org_range ALIAS FOR $4;
3751 make_assertion ALIAS FOR $5;
3754 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3755 -- though we'll still use the passed org for the full path traversal when needed
3756 x_org_found BOOLEAN;
3762 EXECUTE 'SELECT EXISTS (
3764 FROM information_schema.columns
3765 WHERE table_schema = $1
3767 and column_name = ''desired_shelf''
3768 )' INTO proceed USING table_schema, table_name;
3770 RAISE EXCEPTION 'Missing column desired_shelf';
3773 EXECUTE 'SELECT EXISTS (
3775 FROM information_schema.columns
3776 WHERE table_schema = $1
3778 and column_name = ''x_org''
3779 )' INTO x_org_found USING table_schema, table_name;
3781 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3783 RAISE EXCEPTION 'Cannot find org by shortname';
3786 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3788 EXECUTE 'ALTER TABLE '
3789 || quote_ident(table_name)
3790 || ' DROP COLUMN IF EXISTS x_shelf';
3791 EXECUTE 'ALTER TABLE '
3792 || quote_ident(table_name)
3793 || ' ADD COLUMN x_shelf INTEGER';
3796 RAISE INFO 'Found x_org column';
3797 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3798 || ' SET x_shelf = b.id FROM asset_copy_location b'
3799 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3800 || ' AND b.owning_lib = x_org'
3801 || ' AND NOT b.deleted';
3802 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3803 || ' SET x_shelf = b.id FROM asset.copy_location b'
3804 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3805 || ' AND b.owning_lib = x_org'
3806 || ' AND x_shelf IS NULL'
3807 || ' AND NOT b.deleted';
3809 RAISE INFO 'Did not find x_org column';
3810 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3811 || ' SET x_shelf = b.id FROM asset_copy_location b'
3812 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3813 || ' AND b.owning_lib = $1'
3814 || ' AND NOT b.deleted'
3816 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3817 || ' SET x_shelf = b.id FROM asset_copy_location b'
3818 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3819 || ' AND b.owning_lib = $1'
3820 || ' AND x_shelf IS NULL'
3821 || ' AND NOT b.deleted'
3825 FOREACH o IN ARRAY org_list LOOP
3826 RAISE INFO 'Considering org %', o;
3827 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3828 || ' SET x_shelf = b.id FROM asset.copy_location b'
3829 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3830 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3831 || ' AND NOT b.deleted'
3833 GET DIAGNOSTICS row_count = ROW_COUNT;
3834 RAISE INFO 'Updated % rows', row_count;
3837 IF make_assertion THEN
3838 EXECUTE 'SELECT migration_tools.assert(
3839 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3840 ''Cannot find a desired location'',
3841 ''Found all desired locations''
3846 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3848 -- convenience functions for handling circmod maps
3850 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3852 table_schema ALIAS FOR $1;
3853 table_name ALIAS FOR $2;
3856 EXECUTE 'SELECT EXISTS (
3858 FROM information_schema.columns
3859 WHERE table_schema = $1
3861 and column_name = ''desired_circmod''
3862 )' INTO proceed USING table_schema, table_name;
3864 RAISE EXCEPTION 'Missing column desired_circmod';
3867 EXECUTE 'ALTER TABLE '
3868 || quote_ident(table_name)
3869 || ' DROP COLUMN IF EXISTS x_circmod';
3870 EXECUTE 'ALTER TABLE '
3871 || quote_ident(table_name)
3872 || ' ADD COLUMN x_circmod TEXT';
3874 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3875 || ' SET x_circmod = code FROM config.circ_modifier b'
3876 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3878 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3879 || ' SET x_circmod = code FROM config.circ_modifier b'
3880 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3881 || ' AND x_circmod IS NULL';
3883 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3884 || ' SET x_circmod = code FROM config.circ_modifier b'
3885 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3886 || ' AND x_circmod IS NULL';
3888 EXECUTE 'SELECT migration_tools.assert(
3889 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3890 ''Cannot find a desired circulation modifier'',
3891 ''Found all desired circulation modifiers''
3895 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3897 -- convenience functions for handling item status maps
3899 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3901 table_schema ALIAS FOR $1;
3902 table_name ALIAS FOR $2;
3905 EXECUTE 'SELECT EXISTS (
3907 FROM information_schema.columns
3908 WHERE table_schema = $1
3910 and column_name = ''desired_status''
3911 )' INTO proceed USING table_schema, table_name;
3913 RAISE EXCEPTION 'Missing column desired_status';
3916 EXECUTE 'ALTER TABLE '
3917 || quote_ident(table_name)
3918 || ' DROP COLUMN IF EXISTS x_status';
3919 EXECUTE 'ALTER TABLE '
3920 || quote_ident(table_name)
3921 || ' ADD COLUMN x_status INTEGER';
3923 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3924 || ' SET x_status = id FROM config.copy_status b'
3925 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3927 EXECUTE 'SELECT migration_tools.assert(
3928 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3929 ''Cannot find a desired copy status'',
3930 ''Found all desired copy statuses''
3934 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3936 -- convenience functions for handling org maps
3938 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3940 table_schema ALIAS FOR $1;
3941 table_name ALIAS FOR $2;
3944 EXECUTE 'SELECT EXISTS (
3946 FROM information_schema.columns
3947 WHERE table_schema = $1
3949 and column_name = ''desired_org''
3950 )' INTO proceed USING table_schema, table_name;
3952 RAISE EXCEPTION 'Missing column desired_org';
3955 EXECUTE 'ALTER TABLE '
3956 || quote_ident(table_name)
3957 || ' DROP COLUMN IF EXISTS x_org';
3958 EXECUTE 'ALTER TABLE '
3959 || quote_ident(table_name)
3960 || ' ADD COLUMN x_org INTEGER';
3962 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3963 || ' SET x_org = b.id FROM actor.org_unit b'
3964 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3966 EXECUTE 'SELECT migration_tools.assert(
3967 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3968 ''Cannot find a desired org unit'',
3969 ''Found all desired org units''
3973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3975 -- convenience function for handling desired_not_migrate
3977 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3979 table_schema ALIAS FOR $1;
3980 table_name ALIAS FOR $2;
3983 EXECUTE 'SELECT EXISTS (
3985 FROM information_schema.columns
3986 WHERE table_schema = $1
3988 and column_name = ''desired_not_migrate''
3989 )' INTO proceed USING table_schema, table_name;
3991 RAISE EXCEPTION 'Missing column desired_not_migrate';
3994 EXECUTE 'ALTER TABLE '
3995 || quote_ident(table_name)
3996 || ' DROP COLUMN IF EXISTS x_migrate';
3997 EXECUTE 'ALTER TABLE '
3998 || quote_ident(table_name)
3999 || ' ADD COLUMN x_migrate BOOLEAN';
4001 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4002 || ' SET x_migrate = CASE'
4003 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4004 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4005 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4006 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4007 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4008 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4011 EXECUTE 'SELECT migration_tools.assert(
4012 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4013 ''Not all desired_not_migrate values understood'',
4014 ''All desired_not_migrate values understood''
4018 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4020 -- convenience function for handling desired_not_migrate
4022 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4024 table_schema ALIAS FOR $1;
4025 table_name ALIAS FOR $2;
4028 EXECUTE 'SELECT EXISTS (
4030 FROM information_schema.columns
4031 WHERE table_schema = $1
4033 and column_name = ''desired_barred_or_blocked''
4034 )' INTO proceed USING table_schema, table_name;
4036 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4039 EXECUTE 'ALTER TABLE '
4040 || quote_ident(table_name)
4041 || ' DROP COLUMN IF EXISTS x_barred';
4042 EXECUTE 'ALTER TABLE '
4043 || quote_ident(table_name)
4044 || ' ADD COLUMN x_barred BOOLEAN';
4046 EXECUTE 'ALTER TABLE '
4047 || quote_ident(table_name)
4048 || ' DROP COLUMN IF EXISTS x_blocked';
4049 EXECUTE 'ALTER TABLE '
4050 || quote_ident(table_name)
4051 || ' ADD COLUMN x_blocked BOOLEAN';
4053 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4054 || ' SET x_barred = CASE'
4055 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4056 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4057 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4058 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4061 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4062 || ' SET x_blocked = CASE'
4063 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4064 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4065 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4066 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4069 EXECUTE 'SELECT migration_tools.assert(
4070 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4071 ''Not all desired_barred_or_blocked values understood'',
4072 ''All desired_barred_or_blocked values understood''
4076 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4078 -- convenience function for handling desired_profile
4080 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4082 table_schema ALIAS FOR $1;
4083 table_name ALIAS FOR $2;
4086 EXECUTE 'SELECT EXISTS (
4088 FROM information_schema.columns
4089 WHERE table_schema = $1
4091 and column_name = ''desired_profile''
4092 )' INTO proceed USING table_schema, table_name;
4094 RAISE EXCEPTION 'Missing column desired_profile';
4097 EXECUTE 'ALTER TABLE '
4098 || quote_ident(table_name)
4099 || ' DROP COLUMN IF EXISTS x_profile';
4100 EXECUTE 'ALTER TABLE '
4101 || quote_ident(table_name)
4102 || ' ADD COLUMN x_profile INTEGER';
4104 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4105 || ' SET x_profile = b.id FROM permission.grp_tree b'
4106 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4108 EXECUTE 'SELECT migration_tools.assert(
4109 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4110 ''Cannot find a desired profile'',
4111 ''Found all desired profiles''
4115 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4117 -- convenience function for handling desired actor stat cats
4119 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4121 table_schema ALIAS FOR $1;
4122 table_name ALIAS FOR $2;
4123 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4124 org_shortname ALIAS FOR $4;
4132 SELECT 'desired_sc' || field_suffix INTO sc;
4133 SELECT 'desired_sce' || field_suffix INTO sce;
4135 EXECUTE 'SELECT EXISTS (
4137 FROM information_schema.columns
4138 WHERE table_schema = $1
4140 and column_name = $3
4141 )' INTO proceed USING table_schema, table_name, sc;
4143 RAISE EXCEPTION 'Missing column %', sc;
4145 EXECUTE 'SELECT EXISTS (
4147 FROM information_schema.columns
4148 WHERE table_schema = $1
4150 and column_name = $3
4151 )' INTO proceed USING table_schema, table_name, sce;
4153 RAISE EXCEPTION 'Missing column %', sce;
4156 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4158 RAISE EXCEPTION 'Cannot find org by shortname';
4160 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4162 -- caller responsible for their own truncates though we try to prevent duplicates
4163 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4168 ' || quote_ident(table_name) || '
4170 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4174 WHERE owner = ANY ($2)
4175 AND name = BTRIM('||sc||')
4180 WHERE owner = ANY ($2)
4181 AND name = BTRIM('||sc||')
4184 USING org, org_list;
4186 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4191 WHERE owner = ANY ($2)
4192 AND BTRIM('||sc||') = BTRIM(name))
4195 WHERE owner = ANY ($2)
4196 AND BTRIM('||sc||') = BTRIM(name))
4201 ' || quote_ident(table_name) || '
4203 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4204 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4207 FROM actor.stat_cat_entry
4211 WHERE owner = ANY ($2)
4212 AND BTRIM('||sc||') = BTRIM(name)
4213 ) AND value = BTRIM('||sce||')
4214 AND owner = ANY ($2)
4218 FROM actor_stat_cat_entry
4222 WHERE owner = ANY ($2)
4223 AND BTRIM('||sc||') = BTRIM(name)
4224 ) AND value = BTRIM('||sce||')
4225 AND owner = ANY ($2)
4228 USING org, org_list;
4230 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4232 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4234 table_schema ALIAS FOR $1;
4235 table_name ALIAS FOR $2;
4236 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4237 org_shortname ALIAS FOR $4;
4245 SELECT 'desired_sc' || field_suffix INTO sc;
4246 SELECT 'desired_sce' || field_suffix INTO sce;
4247 EXECUTE 'SELECT EXISTS (
4249 FROM information_schema.columns
4250 WHERE table_schema = $1
4252 and column_name = $3
4253 )' INTO proceed USING table_schema, table_name, sc;
4255 RAISE EXCEPTION 'Missing column %', sc;
4257 EXECUTE 'SELECT EXISTS (
4259 FROM information_schema.columns
4260 WHERE table_schema = $1
4262 and column_name = $3
4263 )' INTO proceed USING table_schema, table_name, sce;
4265 RAISE EXCEPTION 'Missing column %', sce;
4268 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4270 RAISE EXCEPTION 'Cannot find org by shortname';
4273 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4275 EXECUTE 'ALTER TABLE '
4276 || quote_ident(table_name)
4277 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4278 EXECUTE 'ALTER TABLE '
4279 || quote_ident(table_name)
4280 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4281 EXECUTE 'ALTER TABLE '
4282 || quote_ident(table_name)
4283 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4284 EXECUTE 'ALTER TABLE '
4285 || quote_ident(table_name)
4286 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4289 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4291 x_sc' || field_suffix || ' = id
4293 (SELECT id, name, owner FROM actor_stat_cat
4294 UNION SELECT id, name, owner FROM actor.stat_cat) u
4296 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4297 AND u.owner = ANY ($1);'
4300 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4302 x_sce' || field_suffix || ' = id
4304 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4305 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4307 u.stat_cat = x_sc' || field_suffix || '
4308 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4309 AND u.owner = ANY ($1);'
4312 EXECUTE 'SELECT migration_tools.assert(
4313 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4314 ''Cannot find a desired stat cat'',
4315 ''Found all desired stat cats''
4318 EXECUTE 'SELECT migration_tools.assert(
4319 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4320 ''Cannot find a desired stat cat entry'',
4321 ''Found all desired stat cat entries''
4325 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4327 -- convenience functions for adding shelving locations
4328 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4329 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4335 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4338 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4339 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4340 IF return_id IS NOT NULL THEN
4348 $$ LANGUAGE plpgsql;
4350 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4352 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4353 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4359 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4362 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4364 SELECT INTO return_id id FROM
4365 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4366 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4367 IF return_id IS NOT NULL THEN
4375 $$ LANGUAGE plpgsql;
4377 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4378 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4385 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4387 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4388 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4392 $BODY$ LANGUAGE plpgsql;
4394 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4395 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4403 use MARC::File::XML (BinaryEncoding => 'utf8');
4405 binmode(STDERR, ':bytes');
4406 binmode(STDOUT, ':utf8');
4407 binmode(STDERR, ':utf8');
4409 my $marc_xml = shift;
4410 my $new_9_to_set = shift;
4413 $marc_xml =~ s/(<leader>.........)./${1}a/;
4416 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4419 #elog("could not parse $bibid: $@\n");
4420 import MARC::File::XML (BinaryEncoding => 'utf8');
4424 my @uris = $marc_xml->field('856');
4425 return $marc_xml->as_xml_record() unless @uris;
4427 foreach my $field (@uris) {
4428 my $ind1 = $field->indicator('1');
4429 if (!defined $ind1) { next; }
4430 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4431 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4432 my $ind2 = $field->indicator('2');
4433 if (!defined $ind2) { next; }
4434 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4435 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4436 $field->add_subfields( '9' => $new_9_to_set );
4439 return $marc_xml->as_xml_record();
4443 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4444 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4452 use MARC::File::XML (BinaryEncoding => 'utf8');
4454 binmode(STDERR, ':bytes');
4455 binmode(STDOUT, ':utf8');
4456 binmode(STDERR, ':utf8');
4458 my $marc_xml = shift;
4459 my $qualifying_match = shift;
4460 my $new_9_to_set = shift;
4463 $marc_xml =~ s/(<leader>.........)./${1}a/;
4466 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4469 #elog("could not parse $bibid: $@\n");
4470 import MARC::File::XML (BinaryEncoding => 'utf8');
4474 my @uris = $marc_xml->field('856');
4475 return $marc_xml->as_xml_record() unless @uris;
4477 foreach my $field (@uris) {
4478 if ($field->as_string() =~ qr/$qualifying_match/) {
4479 my $ind1 = $field->indicator('1');
4480 if (!defined $ind1) { next; }
4481 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4482 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4483 my $ind2 = $field->indicator('2');
4484 if (!defined $ind2) { next; }
4485 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4486 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4487 $field->add_subfields( '9' => $new_9_to_set );
4491 return $marc_xml->as_xml_record();
4495 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4496 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4504 use MARC::File::XML (BinaryEncoding => 'utf8');
4506 binmode(STDERR, ':bytes');
4507 binmode(STDOUT, ':utf8');
4508 binmode(STDERR, ':utf8');
4510 my $marc_xml = shift;
4511 my $substring_old_value = shift;
4512 my $new_value = shift;
4513 my $fix_indicators = shift;
4515 $marc_xml =~ s/(<leader>.........)./${1}a/;
4518 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4521 #elog("could not parse $bibid: $@\n");
4522 import MARC::File::XML (BinaryEncoding => 'utf8');
4526 my @uris = $marc_xml->field('856');
4527 return $marc_xml->as_xml_record() unless @uris;
4529 foreach my $field (@uris) {
4530 my $ind1 = $field->indicator('1');
4531 if (defined $ind1) {
4532 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4533 $field->set_indicator(1,'4');
4536 my $ind2 = $field->indicator('2');
4537 if (defined $ind2) {
4538 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4539 $field->set_indicator(2,'0');
4542 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4543 $field->delete_subfield('9');
4544 $field->add_subfields( '9' => $new_value );
4546 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4549 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4550 $marc_xml->insert_fields_ordered( values( %hash ) );
4552 return $marc_xml->as_xml_record();
4556 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4557 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4565 use MARC::File::XML (BinaryEncoding => 'utf8');
4567 binmode(STDERR, ':bytes');
4568 binmode(STDOUT, ':utf8');
4569 binmode(STDERR, ':utf8');
4571 my $marc_xml = shift;
4572 my $qualifying_match = shift;
4573 my $substring_old_value = shift;
4574 my $new_value = shift;
4575 my $fix_indicators = shift;
4577 $marc_xml =~ s/(<leader>.........)./${1}a/;
4580 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4583 #elog("could not parse $bibid: $@\n");
4584 import MARC::File::XML (BinaryEncoding => 'utf8');
4588 my @unqualified_uris = $marc_xml->field('856');
4590 foreach my $field (@unqualified_uris) {
4591 if ($field->as_string() =~ qr/$qualifying_match/) {
4595 return $marc_xml->as_xml_record() unless @uris;
4597 foreach my $field (@uris) {
4598 my $ind1 = $field->indicator('1');
4599 if (defined $ind1) {
4600 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4601 $field->set_indicator(1,'4');
4604 my $ind2 = $field->indicator('2');
4605 if (defined $ind2) {
4606 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4607 $field->set_indicator(2,'0');
4610 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4611 $field->delete_subfield('9');
4612 $field->add_subfields( '9' => $new_value );
4614 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4617 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4618 $marc_xml->insert_fields_ordered( values( %hash ) );
4620 return $marc_xml->as_xml_record();
4625 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4626 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4634 use MARC::File::XML (BinaryEncoding => 'utf8');
4636 binmode(STDERR, ':bytes');
4637 binmode(STDOUT, ':utf8');
4638 binmode(STDERR, ':utf8');
4640 my $marc_xml = shift;
4643 $marc_xml =~ s/(<leader>.........)./${1}a/;
4646 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4649 #elog("could not parse $bibid: $@\n");
4650 import MARC::File::XML (BinaryEncoding => 'utf8');
4654 my @fields = $marc_xml->field($tag);
4655 return $marc_xml->as_xml_record() unless @fields;
4657 $marc_xml->delete_fields(@fields);
4659 return $marc_xml->as_xml_record();
4663 -- convenience function for linking to the item staging table
4665 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4667 table_schema ALIAS FOR $1;
4668 table_name ALIAS FOR $2;
4669 foreign_column_name ALIAS FOR $3;
4670 main_column_name ALIAS FOR $4;
4671 btrim_desired ALIAS FOR $5;
4674 EXECUTE 'SELECT EXISTS (
4676 FROM information_schema.columns
4677 WHERE table_schema = $1
4679 and column_name = $3
4680 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4682 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4685 EXECUTE 'SELECT EXISTS (
4687 FROM information_schema.columns
4688 WHERE table_schema = $1
4689 AND table_name = ''asset_copy_legacy''
4690 and column_name = $2
4691 )' INTO proceed USING table_schema, main_column_name;
4693 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4696 EXECUTE 'ALTER TABLE '
4697 || quote_ident(table_name)
4698 || ' DROP COLUMN IF EXISTS x_item';
4699 EXECUTE 'ALTER TABLE '
4700 || quote_ident(table_name)
4701 || ' ADD COLUMN x_item BIGINT';
4703 IF btrim_desired THEN
4704 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4705 || ' SET x_item = b.id FROM asset_copy_legacy b'
4706 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4707 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4709 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4710 || ' SET x_item = b.id FROM asset_copy_legacy b'
4711 || ' WHERE a.' || quote_ident(foreign_column_name)
4712 || ' = b.' || quote_ident(main_column_name);
4715 --EXECUTE 'SELECT migration_tools.assert(
4716 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4717 -- ''Cannot link every barcode'',
4718 -- ''Every barcode linked''
4722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4724 -- convenience function for linking to the user staging table
4726 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4728 table_schema ALIAS FOR $1;
4729 table_name ALIAS FOR $2;
4730 foreign_column_name ALIAS FOR $3;
4731 main_column_name ALIAS FOR $4;
4732 btrim_desired ALIAS FOR $5;
4735 EXECUTE 'SELECT EXISTS (
4737 FROM information_schema.columns
4738 WHERE table_schema = $1
4740 and column_name = $3
4741 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4743 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4746 EXECUTE 'SELECT EXISTS (
4748 FROM information_schema.columns
4749 WHERE table_schema = $1
4750 AND table_name = ''actor_usr_legacy''
4751 and column_name = $2
4752 )' INTO proceed USING table_schema, main_column_name;
4754 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4757 EXECUTE 'ALTER TABLE '
4758 || quote_ident(table_name)
4759 || ' DROP COLUMN IF EXISTS x_user';
4760 EXECUTE 'ALTER TABLE '
4761 || quote_ident(table_name)
4762 || ' ADD COLUMN x_user INTEGER';
4764 IF btrim_desired THEN
4765 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4766 || ' SET x_user = b.id FROM actor_usr_legacy b'
4767 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4768 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4770 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4771 || ' SET x_user = b.id FROM actor_usr_legacy b'
4772 || ' WHERE a.' || quote_ident(foreign_column_name)
4773 || ' = b.' || quote_ident(main_column_name);
4776 --EXECUTE 'SELECT migration_tools.assert(
4777 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4778 -- ''Cannot link every barcode'',
4779 -- ''Every barcode linked''
4783 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4785 -- convenience function for linking two tables
4786 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4787 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4789 table_schema ALIAS FOR $1;
4790 table_a ALIAS FOR $2;
4791 column_a ALIAS FOR $3;
4792 table_b ALIAS FOR $4;
4793 column_b ALIAS FOR $5;
4794 column_x ALIAS FOR $6;
4795 btrim_desired ALIAS FOR $7;
4798 EXECUTE 'SELECT EXISTS (
4800 FROM information_schema.columns
4801 WHERE table_schema = $1
4803 and column_name = $3
4804 )' INTO proceed USING table_schema, table_a, column_a;
4806 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4809 EXECUTE 'SELECT EXISTS (
4811 FROM information_schema.columns
4812 WHERE table_schema = $1
4814 and column_name = $3
4815 )' INTO proceed USING table_schema, table_b, column_b;
4817 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4820 EXECUTE 'ALTER TABLE '
4821 || quote_ident(table_b)
4822 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4823 EXECUTE 'ALTER TABLE '
4824 || quote_ident(table_b)
4825 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4827 IF btrim_desired THEN
4828 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4829 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4830 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4831 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4833 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4834 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4835 || ' WHERE a.' || quote_ident(column_a)
4836 || ' = b.' || quote_ident(column_b);
4840 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4842 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4843 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4844 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4846 table_schema ALIAS FOR $1;
4847 table_a ALIAS FOR $2;
4848 column_a ALIAS FOR $3;
4849 table_b ALIAS FOR $4;
4850 column_b ALIAS FOR $5;
4851 column_w ALIAS FOR $6;
4852 column_x ALIAS FOR $7;
4853 btrim_desired ALIAS FOR $8;
4856 EXECUTE 'SELECT EXISTS (
4858 FROM information_schema.columns
4859 WHERE table_schema = $1
4861 and column_name = $3
4862 )' INTO proceed USING table_schema, table_a, column_a;
4864 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4867 EXECUTE 'SELECT EXISTS (
4869 FROM information_schema.columns
4870 WHERE table_schema = $1
4872 and column_name = $3
4873 )' INTO proceed USING table_schema, table_b, column_b;
4875 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4878 EXECUTE 'ALTER TABLE '
4879 || quote_ident(table_b)
4880 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4881 EXECUTE 'ALTER TABLE '
4882 || quote_ident(table_b)
4883 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4885 IF btrim_desired THEN
4886 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4887 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4888 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4889 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4891 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4892 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4893 || ' WHERE a.' || quote_ident(column_a)
4894 || ' = b.' || quote_ident(column_b);
4898 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4900 -- 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
4901 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4902 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4904 table_schema ALIAS FOR $1;
4905 table_a ALIAS FOR $2;
4906 column_a ALIAS FOR $3;
4907 table_b ALIAS FOR $4;
4908 column_b ALIAS FOR $5;
4909 column_w ALIAS FOR $6;
4910 column_x ALIAS FOR $7;
4913 EXECUTE 'SELECT EXISTS (
4915 FROM information_schema.columns
4916 WHERE table_schema = $1
4918 and column_name = $3
4919 )' INTO proceed USING table_schema, table_a, column_a;
4921 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4924 EXECUTE 'SELECT EXISTS (
4926 FROM information_schema.columns
4927 WHERE table_schema = $1
4929 and column_name = $3
4930 )' INTO proceed USING table_schema, table_b, column_b;
4932 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4935 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4936 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4937 || ' WHERE a.' || quote_ident(column_a)
4938 || ' = b.' || quote_ident(column_b);
4941 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4943 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4945 table_schema ALIAS FOR $1;
4946 table_a ALIAS FOR $2;
4947 column_a ALIAS FOR $3;
4948 table_b ALIAS FOR $4;
4949 column_b ALIAS FOR $5;
4950 column_w ALIAS FOR $6;
4951 column_x ALIAS FOR $7;
4954 EXECUTE 'SELECT EXISTS (
4956 FROM information_schema.columns
4957 WHERE table_schema = $1
4959 and column_name = $3
4960 )' INTO proceed USING table_schema, table_a, column_a;
4962 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4965 EXECUTE 'SELECT EXISTS (
4967 FROM information_schema.columns
4968 WHERE table_schema = $1
4970 and column_name = $3
4971 )' INTO proceed USING table_schema, table_b, column_b;
4973 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4976 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4977 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4978 || ' WHERE a.' || quote_ident(column_a)
4979 || ' = b.' || quote_ident(column_b)
4980 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4983 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4985 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4987 table_schema ALIAS FOR $1;
4988 table_a ALIAS FOR $2;
4989 column_a ALIAS FOR $3;
4990 table_b ALIAS FOR $4;
4991 column_b ALIAS FOR $5;
4992 column_w ALIAS FOR $6;
4993 column_x ALIAS FOR $7;
4996 EXECUTE 'SELECT EXISTS (
4998 FROM information_schema.columns
4999 WHERE table_schema = $1
5001 and column_name = $3
5002 )' INTO proceed USING table_schema, table_a, column_a;
5004 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5007 EXECUTE 'SELECT EXISTS (
5009 FROM information_schema.columns
5010 WHERE table_schema = $1
5012 and column_name = $3
5013 )' INTO proceed USING table_schema, table_b, column_b;
5015 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5018 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5019 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5020 || ' WHERE a.' || quote_ident(column_a)
5021 || ' = b.' || quote_ident(column_b)
5022 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5025 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5027 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5029 table_schema ALIAS FOR $1;
5030 table_a ALIAS FOR $2;
5031 column_a ALIAS FOR $3;
5032 table_b ALIAS FOR $4;
5033 column_b ALIAS FOR $5;
5034 column_w ALIAS FOR $6;
5035 column_x ALIAS FOR $7;
5038 EXECUTE 'SELECT EXISTS (
5040 FROM information_schema.columns
5041 WHERE table_schema = $1
5043 and column_name = $3
5044 )' INTO proceed USING table_schema, table_a, column_a;
5046 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5049 EXECUTE 'SELECT EXISTS (
5051 FROM information_schema.columns
5052 WHERE table_schema = $1
5054 and column_name = $3
5055 )' INTO proceed USING table_schema, table_b, column_b;
5057 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5060 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5061 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5062 || ' WHERE a.' || quote_ident(column_a)
5063 || ' = b.' || quote_ident(column_b)
5064 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5067 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5069 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5071 table_schema ALIAS FOR $1;
5072 table_a ALIAS FOR $2;
5073 column_a ALIAS FOR $3;
5074 table_b ALIAS FOR $4;
5075 column_b ALIAS FOR $5;
5076 column_w ALIAS FOR $6;
5077 column_x ALIAS FOR $7;
5080 EXECUTE 'SELECT EXISTS (
5082 FROM information_schema.columns
5083 WHERE table_schema = $1
5085 and column_name = $3
5086 )' INTO proceed USING table_schema, table_a, column_a;
5088 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5091 EXECUTE 'SELECT EXISTS (
5093 FROM information_schema.columns
5094 WHERE table_schema = $1
5096 and column_name = $3
5097 )' INTO proceed USING table_schema, table_b, column_b;
5099 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5102 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5103 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5104 || ' WHERE a.' || quote_ident(column_a)
5105 || ' = b.' || quote_ident(column_b)
5106 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5109 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5111 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5113 table_schema ALIAS FOR $1;
5114 table_a ALIAS FOR $2;
5115 column_a ALIAS FOR $3;
5116 table_b ALIAS FOR $4;
5117 column_b ALIAS FOR $5;
5118 column_w ALIAS FOR $6;
5119 column_x ALIAS FOR $7;
5122 EXECUTE 'SELECT EXISTS (
5124 FROM information_schema.columns
5125 WHERE table_schema = $1
5127 and column_name = $3
5128 )' INTO proceed USING table_schema, table_a, column_a;
5130 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5133 EXECUTE 'SELECT EXISTS (
5135 FROM information_schema.columns
5136 WHERE table_schema = $1
5138 and column_name = $3
5139 )' INTO proceed USING table_schema, table_b, column_b;
5141 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5144 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5145 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5146 || ' WHERE a.' || quote_ident(column_a)
5147 || ' = b.' || quote_ident(column_b)
5148 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5151 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5153 -- convenience function for handling desired asset stat cats
5155 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5157 table_schema ALIAS FOR $1;
5158 table_name ALIAS FOR $2;
5159 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5160 org_shortname ALIAS FOR $4;
5168 SELECT 'desired_sc' || field_suffix INTO sc;
5169 SELECT 'desired_sce' || field_suffix INTO sce;
5171 EXECUTE 'SELECT EXISTS (
5173 FROM information_schema.columns
5174 WHERE table_schema = $1
5176 and column_name = $3
5177 )' INTO proceed USING table_schema, table_name, sc;
5179 RAISE EXCEPTION 'Missing column %', sc;
5181 EXECUTE 'SELECT EXISTS (
5183 FROM information_schema.columns
5184 WHERE table_schema = $1
5186 and column_name = $3
5187 )' INTO proceed USING table_schema, table_name, sce;
5189 RAISE EXCEPTION 'Missing column %', sce;
5192 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5194 RAISE EXCEPTION 'Cannot find org by shortname';
5196 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5198 -- caller responsible for their own truncates though we try to prevent duplicates
5199 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5204 ' || quote_ident(table_name) || '
5206 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5210 WHERE owner = ANY ($2)
5211 AND name = BTRIM('||sc||')
5216 WHERE owner = ANY ($2)
5217 AND name = BTRIM('||sc||')
5220 USING org, org_list;
5222 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5227 WHERE owner = ANY ($2)
5228 AND BTRIM('||sc||') = BTRIM(name))
5231 WHERE owner = ANY ($2)
5232 AND BTRIM('||sc||') = BTRIM(name))
5237 ' || quote_ident(table_name) || '
5239 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5240 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5243 FROM asset.stat_cat_entry
5247 WHERE owner = ANY ($2)
5248 AND BTRIM('||sc||') = BTRIM(name)
5249 ) AND value = BTRIM('||sce||')
5250 AND owner = ANY ($2)
5254 FROM asset_stat_cat_entry
5258 WHERE owner = ANY ($2)
5259 AND BTRIM('||sc||') = BTRIM(name)
5260 ) AND value = BTRIM('||sce||')
5261 AND owner = ANY ($2)
5264 USING org, org_list;
5266 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5268 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5270 table_schema ALIAS FOR $1;
5271 table_name ALIAS FOR $2;
5272 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5273 org_shortname ALIAS FOR $4;
5281 SELECT 'desired_sc' || field_suffix INTO sc;
5282 SELECT 'desired_sce' || field_suffix INTO sce;
5283 EXECUTE 'SELECT EXISTS (
5285 FROM information_schema.columns
5286 WHERE table_schema = $1
5288 and column_name = $3
5289 )' INTO proceed USING table_schema, table_name, sc;
5291 RAISE EXCEPTION 'Missing column %', sc;
5293 EXECUTE 'SELECT EXISTS (
5295 FROM information_schema.columns
5296 WHERE table_schema = $1
5298 and column_name = $3
5299 )' INTO proceed USING table_schema, table_name, sce;
5301 RAISE EXCEPTION 'Missing column %', sce;
5304 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5306 RAISE EXCEPTION 'Cannot find org by shortname';
5309 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5311 EXECUTE 'ALTER TABLE '
5312 || quote_ident(table_name)
5313 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5314 EXECUTE 'ALTER TABLE '
5315 || quote_ident(table_name)
5316 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5317 EXECUTE 'ALTER TABLE '
5318 || quote_ident(table_name)
5319 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5320 EXECUTE 'ALTER TABLE '
5321 || quote_ident(table_name)
5322 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5325 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5327 x_sc' || field_suffix || ' = id
5329 (SELECT id, name, owner FROM asset_stat_cat
5330 UNION SELECT id, name, owner FROM asset.stat_cat) u
5332 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5333 AND u.owner = ANY ($1);'
5336 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5338 x_sce' || field_suffix || ' = id
5340 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5341 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5343 u.stat_cat = x_sc' || field_suffix || '
5344 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5345 AND u.owner = ANY ($1);'
5348 EXECUTE 'SELECT migration_tools.assert(
5349 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5350 ''Cannot find a desired stat cat'',
5351 ''Found all desired stat cats''
5354 EXECUTE 'SELECT migration_tools.assert(
5355 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5356 ''Cannot find a desired stat cat entry'',
5357 ''Found all desired stat cat entries''
5361 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5363 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5364 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5371 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5373 AND table_schema = s_name
5374 AND (data_type='text' OR data_type='character varying')
5375 AND column_name like 'l_%'
5377 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5384 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5385 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5392 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5394 AND table_schema = s_name
5395 AND (data_type='text' OR data_type='character varying')
5397 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5404 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5405 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5412 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5414 AND table_schema = s_name
5415 AND (data_type='text' OR data_type='character varying')
5416 AND column_name like 'l_%'
5418 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5425 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5426 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5433 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5435 AND table_schema = s_name
5436 AND (data_type='text' OR data_type='character varying')
5438 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5446 -- convenience function for handling item barcode collisions in asset_copy_legacy
5448 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5453 internal_collision_count NUMERIC := 0;
5454 incumbent_collision_count NUMERIC := 0;
5456 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5458 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5460 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5461 GET DIAGNOSTICS row_count = ROW_COUNT;
5462 internal_collision_count := internal_collision_count + row_count;
5465 RAISE INFO '% internal collisions', internal_collision_count;
5466 FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
5468 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5470 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5471 GET DIAGNOSTICS row_count = ROW_COUNT;
5472 incumbent_collision_count := incumbent_collision_count + row_count;
5475 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5477 $function$ LANGUAGE plpgsql;
5479 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5480 -- this should be ran prior to populating actor_card
5482 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5487 internal_collision_count NUMERIC := 0;
5488 incumbent_barcode_collision_count NUMERIC := 0;
5489 incumbent_usrname_collision_count NUMERIC := 0;
5491 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5493 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5495 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5496 GET DIAGNOSTICS row_count = ROW_COUNT;
5497 internal_collision_count := internal_collision_count + row_count;
5500 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5503 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5505 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5507 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5508 GET DIAGNOSTICS row_count = ROW_COUNT;
5509 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5512 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5515 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5517 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5519 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5520 GET DIAGNOSTICS row_count = ROW_COUNT;
5521 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5524 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5526 $function$ LANGUAGE plpgsql;
5528 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5530 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5535 internal_collision_count NUMERIC := 0;
5536 incumbent_collision_count NUMERIC := 0;
5538 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5540 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5542 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5543 GET DIAGNOSTICS row_count = ROW_COUNT;
5544 internal_collision_count := internal_collision_count + row_count;
5547 RAISE INFO '% internal collisions', internal_collision_count;
5548 FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
5550 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5552 UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5553 GET DIAGNOSTICS row_count = ROW_COUNT;
5554 incumbent_collision_count := incumbent_collision_count + row_count;
5557 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5559 $function$ LANGUAGE plpgsql;
5561 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5562 -- this should be ran prior to populating actor_card
5564 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5569 internal_collision_count NUMERIC := 0;
5570 incumbent_barcode_collision_count NUMERIC := 0;
5571 incumbent_usrname_collision_count NUMERIC := 0;
5573 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5575 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5577 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5578 GET DIAGNOSTICS row_count = ROW_COUNT;
5579 internal_collision_count := internal_collision_count + row_count;
5582 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5585 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5587 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5589 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5590 GET DIAGNOSTICS row_count = ROW_COUNT;
5591 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5594 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5597 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5599 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5601 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5602 GET DIAGNOSTICS row_count = ROW_COUNT;
5603 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5606 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5608 $function$ LANGUAGE plpgsql;
5610 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5611 -- WARNING: Use at your own risk
5612 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5614 item_object asset.copy%ROWTYPE;
5615 user_object actor.usr%ROWTYPE;
5616 test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5617 result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5618 safe_to_delete BOOLEAN := FALSE;
5619 m action.found_circ_matrix_matchpoint;
5620 result_matchpoint INTEGER;
5621 -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5623 SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5624 RAISE INFO 'testing rule: %', test_rule_object;
5626 INSERT INTO actor.usr (
5636 COALESCE(test_rule_object.grp, 2),
5637 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5642 COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5643 COALESCE(test_rule_object.juvenile_flag, FALSE)
5646 SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5648 INSERT INTO asset.call_number (
5659 COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
5660 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5664 INSERT INTO asset.copy (
5676 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5677 COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
5679 currval('asset.call_number_id_seq'),
5681 COALESCE(test_rule_object.copy_location,1),
5684 COALESCE(test_rule_object.ref_flag,FALSE),
5685 test_rule_object.circ_modifier
5688 SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
5690 SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
5691 test_rule_object.org_unit,
5694 COALESCE(test_rule_object.is_renewal,FALSE)
5696 RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
5697 test_rule_object.org_unit,
5700 COALESCE(test_rule_object.is_renewal,FALSE),
5706 FOR result_matchpoint IN SELECT UNNEST(m.buildrows)
5708 SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint;
5709 RAISE INFO 'considering rule: %', result_rule_object;
5710 IF result_rule_object.id = test_rule_object.id THEN
5711 RAISE INFO 'found self';
5714 IF (result_rule_object.circulate = test_rule_object.circulate
5715 AND result_rule_object.duration_rule = test_rule_object.duration_rule
5716 AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule
5717 AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule
5719 (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL)
5720 OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date)
5721 OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL)
5724 (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL)
5725 OR (result_rule_object.renewals = test_rule_object.renewals)
5726 OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL)
5729 (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL)
5730 OR (result_rule_object.grace_period = test_rule_object.grace_period)
5731 OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL)
5734 SELECT limit_set, fallthrough
5735 FROM config.circ_matrix_limit_set_map
5736 WHERE active and matchpoint = test_rule_object.id
5738 SELECT limit_set, fallthrough
5739 FROM config.circ_matrix_limit_set_map
5740 WHERE active and matchpoint = result_rule_object.id
5743 RAISE INFO 'rule has same outcome';
5744 safe_to_delete := TRUE;
5746 RAISE INFO 'rule has different outcome, bail now';
5747 RAISE EXCEPTION 'rollback the item and user tables';
5751 RAISE EXCEPTION 'rollback the item and user tables';
5753 EXCEPTION WHEN OTHERS THEN
5755 RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
5756 RETURN safe_to_delete;
5759 $func$ LANGUAGE plpgsql;