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.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
1680 -- Expects the following table/columns:
1682 -- export_biblio_tsv:
1684 -- l_create_dt | 2007-03-07 09:03:09
1685 -- l_last_change_dt | 2015-01-23 11:18:54
1686 -- l_last_change_userid | 2
1687 -- l_material_cd | 10
1688 -- l_collection_cd | 13
1689 -- l_call_nmbr1 | Canada
1690 -- l_call_nmbr2 | ON
1691 -- l_call_nmbr3 | Ottawa 18
1692 -- l_title | Art and the courts : France ad England
1693 -- l_title_remainder | from 1259-1328
1694 -- l_responsibility_stmt |
1695 -- l_author | National Gallery of Canada
1702 -- l_flag_attention | 0
1704 -- export_biblio_field_tsv:
1710 -- l_subfield_cd | a
1711 -- l_field_data | Brieger, Peter Henry
1713 -- Map export_biblio_tsv as follows:
1714 -- l_call_nmbr? -> 099a
1717 -- l_title_remainder -> 245b
1718 -- l_responsibility_stmt -> 245c
1723 migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
1726 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
1727 array_agg(l_ind1_cd) as "ind1",
1728 array_agg(l_ind2_cd) as "ind2",
1729 array_agg(l_field_data) as "data"
1737 from export_biblio_field_tsv
1738 where l_bibid = x_bibid
1742 'a' as "l_subfield_cd",
1746 nullif(btrim(l_call_nmbr1),''),
1747 nullif(btrim(l_call_nmbr2),''),
1748 nullif(btrim(l_call_nmbr3),'')
1750 from export_biblio_tsv
1751 where l_bibid = x_bibid
1755 'a' as "l_subfield_cd",
1758 l_author as "l_field_data"
1759 from export_biblio_tsv
1760 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
1764 'a' as "l_subfield_cd",
1767 l_title as "l_field_data"
1768 from export_biblio_tsv
1769 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
1773 'b' as "l_subfield_cd",
1776 l_title_remainder as "l_field_data"
1777 from export_biblio_tsv
1778 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
1782 'a' as "l_subfield_cd",
1785 l_topic1 as "l_field_data"
1786 from export_biblio_tsv
1787 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
1791 'a' as "l_subfield_cd",
1794 l_topic2 as "l_field_data"
1795 from export_biblio_tsv
1796 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
1800 'a' as "l_subfield_cd",
1803 l_topic3 as "l_field_data"
1804 from export_biblio_tsv
1805 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
1809 'a' as "l_subfield_cd",
1812 l_topic4 as "l_field_data"
1813 from export_biblio_tsv
1814 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
1818 'a' as "l_subfield_cd",
1821 l_topic5 as "l_field_data"
1822 from export_biblio_tsv
1823 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
1827 '' as "l_subfield_cd",
1830 l_bibid as "l_field_data"
1831 from export_biblio_tsv
1832 where l_bibid = x_bibid
1837 $func$ LANGUAGE plpgsql;
1839 -- add koha holding tag to marc
1840 DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
1842 CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber TEXT)
1850 use MARC::File::XML (BinaryEncoding => 'utf8');
1852 binmode(STDERR, ':bytes');
1853 binmode(STDOUT, ':utf8');
1854 binmode(STDERR, ':utf8');
1856 my ($marc_xml, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
1858 $marc_xml =~ s/(<leader>.........)./${1}a/;
1861 $marc_xml = MARC::Record->new_from_xml($marc_xml);
1864 #elog("could not parse $bibid: $@\n");
1865 import MARC::File::XML (BinaryEncoding => 'utf8');
1869 my $new_field = new MARC::Field(
1872 'b' => $holdingbranch,
1878 if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
1879 if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
1880 if ($price) { $new_field->add_subfields('g' => $price); }
1881 if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
1882 if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
1883 if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
1884 if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
1885 if ($stack) { $new_field->add_subfields('j' => $stack); }
1886 if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
1887 if ($damaged) { $new_field->add_subfields('4' => $damaged); }
1888 if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
1889 if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
1890 if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
1891 if ($issues) { $new_field->add_subfields('l' => $issues); }
1892 if ($renewals) { $new_field->add_subfields('m' => $renewals); }
1893 if ($reserves) { $new_field->add_subfields('n' => $reserves); }
1894 if ($restricted) { $new_field->add_subfields('5' => $restricted); }
1895 if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
1896 if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
1897 if ($onloan) { $new_field->add_subfields('q' => $onloan); }
1898 if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
1899 if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
1900 if ($ccode) { $new_field->add_subfields('8' => $ccode); }
1901 if ($materials) { $new_field->add_subfields('3' => $materials); }
1902 if ($uri) { $new_field->add_subfields('u' => $uri); }
1903 if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
1904 if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
1905 if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
1907 $marc_xml->insert_grouped_field( $new_field );
1909 return $marc_xml->as_xml_record();
1913 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1915 attempt_value ALIAS FOR $1;
1916 fail_value ALIAS FOR $2;
1917 output NUMERIC(8,2);
1920 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1927 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1932 $$ LANGUAGE PLPGSQL STRICT STABLE;
1934 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1936 attempt_value ALIAS FOR $1;
1937 fail_value ALIAS FOR $2;
1938 output NUMERIC(6,2);
1941 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1948 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1953 $$ LANGUAGE PLPGSQL STRICT STABLE;
1955 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1957 attempt_value ALIAS FOR $1;
1958 fail_value ALIAS FOR $2;
1959 output NUMERIC(8,2);
1961 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1962 RAISE EXCEPTION 'too many digits';
1965 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;'
1972 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1977 $$ LANGUAGE PLPGSQL STRICT STABLE;
1979 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1981 attempt_value ALIAS FOR $1;
1982 fail_value ALIAS FOR $2;
1983 output NUMERIC(6,2);
1985 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1986 RAISE EXCEPTION 'too many digits';
1989 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;'
1996 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
2001 $$ LANGUAGE PLPGSQL STRICT STABLE;
2003 -- add_codabar_checkdigit
2004 -- $barcode source barcode
2006 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
2007 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2008 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
2009 -- input string does not meet those requirements, it is returned unchanged.
2011 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
2012 my $barcode = shift;
2014 return $barcode if $barcode !~ /^\d{13,14}$/;
2015 $barcode = substr($barcode, 0, 13); # ignore 14th digit
2016 my @digits = split //, $barcode;
2018 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
2019 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
2020 my $remainder = $total % 10;
2021 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
2022 return $barcode . $checkdigit;
2023 $$ LANGUAGE PLPERLU STRICT STABLE;
2025 -- add_code39mod43_checkdigit
2026 -- $barcode source barcode
2028 -- If the source string is 13 or 14 characters long and contains only valid
2029 -- Code 39 mod 43 characters, adds or replaces the 14th
2030 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2031 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
2032 -- input string does not meet those requirements, it is returned unchanged.
2034 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
2035 my $barcode = shift;
2037 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
2038 $barcode = substr($barcode, 0, 13); # ignore 14th character
2040 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
2041 my %nums = map { $valid_chars[$_] => $_ } (0..42);
2044 $total += $nums{$_} foreach split(//, $barcode);
2045 my $remainder = $total % 43;
2046 my $checkdigit = $valid_chars[$remainder];
2047 return $barcode . $checkdigit;
2048 $$ LANGUAGE PLPERLU STRICT STABLE;
2050 -- add_mod16_checkdigit
2051 -- $barcode source barcode
2053 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
2055 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
2056 my $barcode = shift;
2058 my @digits = split //, $barcode;
2060 foreach $digit (@digits) {
2061 if ($digit =~ /[0-9]/) { $total += $digit;
2062 } elsif ($digit eq '-') { $total += 10;
2063 } elsif ($digit eq '$') { $total += 11;
2064 } elsif ($digit eq ':') { $total += 12;
2065 } elsif ($digit eq '/') { $total += 13;
2066 } elsif ($digit eq '.') { $total += 14;
2067 } elsif ($digit eq '+') { $total += 15;
2068 } elsif ($digit eq 'A') { $total += 16;
2069 } elsif ($digit eq 'B') { $total += 17;
2070 } elsif ($digit eq 'C') { $total += 18;
2071 } elsif ($digit eq 'D') { $total += 19;
2072 } else { die "invalid digit <$digit>";
2075 my $remainder = $total % 16;
2076 my $difference = 16 - $remainder;
2078 if ($difference < 10) { $checkdigit = $difference;
2079 } elsif ($difference == 10) { $checkdigit = '-';
2080 } elsif ($difference == 11) { $checkdigit = '$';
2081 } elsif ($difference == 12) { $checkdigit = ':';
2082 } elsif ($difference == 13) { $checkdigit = '/';
2083 } elsif ($difference == 14) { $checkdigit = '.';
2084 } elsif ($difference == 15) { $checkdigit = '+';
2085 } else { die "error calculating checkdigit";
2088 return $barcode . $checkdigit;
2089 $$ LANGUAGE PLPERLU STRICT STABLE;
2091 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
2094 areacode TEXT := $2;
2097 n_digits INTEGER := 0;
2100 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
2101 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
2102 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
2103 IF n_digits = 7 AND areacode <> '' THEN
2104 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
2105 output := (areacode || '-' || temp);
2112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2114 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2115 my ($marcxml, $pos, $value) = @_;
2118 use MARC::File::XML;
2122 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2123 my $leader = $marc->leader();
2124 substr($leader, $pos, 1) = $value;
2125 $marc->leader($leader);
2126 $xml = $marc->as_xml_record;
2127 $xml =~ s/^<\?.+?\?>$//mo;
2129 $xml =~ s/>\s+</></sgo;
2132 $$ LANGUAGE PLPERLU STABLE;
2134 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
2135 my ($marcxml, $pos, $value) = @_;
2138 use MARC::File::XML;
2142 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2143 my $f008 = $marc->field('008');
2146 my $field = $f008->data();
2147 substr($field, $pos, 1) = $value;
2148 $f008->update($field);
2149 $xml = $marc->as_xml_record;
2150 $xml =~ s/^<\?.+?\?>$//mo;
2152 $xml =~ s/>\s+</></sgo;
2156 $$ LANGUAGE PLPERLU STABLE;
2159 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
2161 profile ALIAS FOR $1;
2163 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
2165 $$ LANGUAGE PLPGSQL STRICT STABLE;
2168 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
2170 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
2172 $$ LANGUAGE PLPGSQL STRICT STABLE;
2175 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
2177 my ($marcxml, $tags) = @_;
2180 use MARC::File::XML;
2185 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2186 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
2188 my @incumbents = ();
2190 foreach my $field ( $marc->fields() ) {
2191 push @incumbents, $field->as_formatted();
2194 foreach $field ( $to_insert->fields() ) {
2195 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
2196 $marc->insert_fields_ordered( ($field) );
2200 $xml = $marc->as_xml_record;
2201 $xml =~ s/^<\?.+?\?>$//mo;
2203 $xml =~ s/>\s+</></sgo;
2208 $$ LANGUAGE PLPERLU STABLE;
2210 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
2214 -- First make sure the circ matrix is loaded and the circulations
2215 -- have been staged to the extent possible (but at the very least
2216 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2217 -- circ modifiers must also be in place.
2219 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2227 this_duration_rule INT;
2229 this_max_fine_rule INT;
2230 rcd config.rule_circ_duration%ROWTYPE;
2231 rrf config.rule_recurring_fine%ROWTYPE;
2232 rmf config.rule_max_fine%ROWTYPE;
2239 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2241 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2243 -- Fetch the correct rules for this circulation
2250 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2253 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2254 INTO circ_lib, target_copy, usr, is_renewal ;
2256 INTO this_duration_rule,
2260 recurring_fine_rule,
2262 FROM action.item_user_circ_test(
2268 SELECT INTO rcd * FROM config.rule_circ_duration
2269 WHERE id = this_duration_rule;
2270 SELECT INTO rrf * FROM config.rule_recurring_fine
2271 WHERE id = this_fine_rule;
2272 SELECT INTO rmf * FROM config.rule_max_fine
2273 WHERE id = this_max_fine_rule;
2275 -- Apply the rules to this circulation
2276 EXECUTE ('UPDATE ' || tablename || ' c
2278 duration_rule = rcd.name,
2279 recurring_fine_rule = rrf.name,
2280 max_fine_rule = rmf.name,
2281 duration = rcd.normal,
2282 recurring_fine = rrf.normal,
2285 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2288 renewal_remaining = rcd.max_renewals
2290 config.rule_circ_duration rcd,
2291 config.rule_recurring_fine rrf,
2292 config.rule_max_fine rmf,
2295 rcd.id = ' || this_duration_rule || ' AND
2296 rrf.id = ' || this_fine_rule || ' AND
2297 rmf.id = ' || this_max_fine_rule || ' AND
2298 ac.id = c.target_copy AND
2299 c.id = ' || circ || ';');
2301 -- Keep track of where we are in the process
2303 IF (n % 100 = 0) THEN
2304 RAISE INFO '%', n || ' of ' || n_circs
2305 || ' (' || (100*n/n_circs) || '%) circs updated.';
2313 $$ LANGUAGE plpgsql;
2315 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2319 -- First make sure the circ matrix is loaded and the circulations
2320 -- have been staged to the extent possible (but at the very least
2321 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2322 -- circ modifiers must also be in place.
2324 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2332 this_duration_rule INT;
2334 this_max_fine_rule INT;
2335 rcd config.rule_circ_duration%ROWTYPE;
2336 rrf config.rule_recurring_fine%ROWTYPE;
2337 rmf config.rule_max_fine%ROWTYPE;
2344 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2346 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2348 -- Fetch the correct rules for this circulation
2355 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2358 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2359 INTO circ_lib, target_copy, usr, is_renewal ;
2361 INTO this_duration_rule,
2367 FROM action.find_circ_matrix_matchpoint(
2373 SELECT INTO rcd * FROM config.rule_circ_duration
2374 WHERE id = this_duration_rule;
2375 SELECT INTO rrf * FROM config.rule_recurring_fine
2376 WHERE id = this_fine_rule;
2377 SELECT INTO rmf * FROM config.rule_max_fine
2378 WHERE id = this_max_fine_rule;
2380 -- Apply the rules to this circulation
2381 EXECUTE ('UPDATE ' || tablename || ' c
2383 duration_rule = rcd.name,
2384 recuring_fine_rule = rrf.name,
2385 max_fine_rule = rmf.name,
2386 duration = rcd.normal,
2387 recuring_fine = rrf.normal,
2390 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2393 renewal_remaining = rcd.max_renewals
2395 config.rule_circ_duration rcd,
2396 config.rule_recuring_fine rrf,
2397 config.rule_max_fine rmf,
2400 rcd.id = ' || this_duration_rule || ' AND
2401 rrf.id = ' || this_fine_rule || ' AND
2402 rmf.id = ' || this_max_fine_rule || ' AND
2403 ac.id = c.target_copy AND
2404 c.id = ' || circ || ';');
2406 -- Keep track of where we are in the process
2408 IF (n % 100 = 0) THEN
2409 RAISE INFO '%', n || ' of ' || n_circs
2410 || ' (' || (100*n/n_circs) || '%) circs updated.';
2418 $$ LANGUAGE plpgsql;
2420 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2424 -- First make sure the circ matrix is loaded and the circulations
2425 -- have been staged to the extent possible (but at the very least
2426 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2427 -- circ modifiers must also be in place.
2429 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2437 this_duration_rule INT;
2439 this_max_fine_rule INT;
2440 rcd config.rule_circ_duration%ROWTYPE;
2441 rrf config.rule_recurring_fine%ROWTYPE;
2442 rmf config.rule_max_fine%ROWTYPE;
2449 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2451 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2453 -- Fetch the correct rules for this circulation
2460 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2463 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2464 INTO circ_lib, target_copy, usr, is_renewal ;
2466 INTO this_duration_rule,
2469 (matchpoint).duration_rule,
2470 (matchpoint).recurring_fine_rule,
2471 (matchpoint).max_fine_rule
2472 FROM action.find_circ_matrix_matchpoint(
2478 SELECT INTO rcd * FROM config.rule_circ_duration
2479 WHERE id = this_duration_rule;
2480 SELECT INTO rrf * FROM config.rule_recurring_fine
2481 WHERE id = this_fine_rule;
2482 SELECT INTO rmf * FROM config.rule_max_fine
2483 WHERE id = this_max_fine_rule;
2485 -- Apply the rules to this circulation
2486 EXECUTE ('UPDATE ' || tablename || ' c
2488 duration_rule = rcd.name,
2489 recurring_fine_rule = rrf.name,
2490 max_fine_rule = rmf.name,
2491 duration = rcd.normal,
2492 recurring_fine = rrf.normal,
2495 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2498 renewal_remaining = rcd.max_renewals,
2499 grace_period = rrf.grace_period
2501 config.rule_circ_duration rcd,
2502 config.rule_recurring_fine rrf,
2503 config.rule_max_fine rmf,
2506 rcd.id = ' || this_duration_rule || ' AND
2507 rrf.id = ' || this_fine_rule || ' AND
2508 rmf.id = ' || this_max_fine_rule || ' AND
2509 ac.id = c.target_copy AND
2510 c.id = ' || circ || ';');
2512 -- Keep track of where we are in the process
2514 IF (n % 100 = 0) THEN
2515 RAISE INFO '%', n || ' of ' || n_circs
2516 || ' (' || (100*n/n_circs) || '%) circs updated.';
2524 $$ LANGUAGE plpgsql;
2526 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2527 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2528 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2531 charge_lost_on_zero BOOLEAN;
2534 default_price NUMERIC;
2535 working_price NUMERIC;
2539 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2540 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2542 SELECT INTO charge_lost_on_zero value
2543 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2545 SELECT INTO min_price value
2546 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2548 SELECT INTO max_price value
2549 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2551 SELECT INTO default_price value
2552 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2554 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2556 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2557 working_price := default_price;
2560 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2561 working_price := max_price;
2564 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2565 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2566 working_price := min_price;
2570 RETURN working_price;
2574 $$ LANGUAGE plpgsql;
2576 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2580 -- First make sure the circ matrix is loaded and the circulations
2581 -- have been staged to the extent possible (but at the very least
2582 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2583 -- circ modifiers must also be in place.
2585 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2593 this_duration_rule INT;
2595 this_max_fine_rule INT;
2596 rcd config.rule_circ_duration%ROWTYPE;
2597 rrf config.rule_recurring_fine%ROWTYPE;
2598 rmf config.rule_max_fine%ROWTYPE;
2604 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2606 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2608 -- Fetch the correct rules for this circulation
2615 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2618 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2619 INTO circ_lib, target_copy, usr, is_renewal ;
2621 INTO this_duration_rule,
2624 (matchpoint).duration_rule,
2625 (matchpoint).recurring_fine_rule,
2626 (matchpoint).max_fine_rule
2627 FROM action.find_circ_matrix_matchpoint(
2633 SELECT INTO rcd * FROM config.rule_circ_duration
2634 WHERE id = this_duration_rule;
2635 SELECT INTO rrf * FROM config.rule_recurring_fine
2636 WHERE id = this_fine_rule;
2637 SELECT INTO rmf * FROM config.rule_max_fine
2638 WHERE id = this_max_fine_rule;
2640 -- Apply the rules to this circulation
2641 EXECUTE ('UPDATE ' || tablename || ' c
2643 duration_rule = rcd.name,
2644 recurring_fine_rule = rrf.name,
2645 max_fine_rule = rmf.name,
2646 duration = rcd.normal,
2647 recurring_fine = rrf.normal,
2650 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2653 renewal_remaining = rcd.max_renewals,
2654 grace_period = rrf.grace_period
2656 config.rule_circ_duration rcd,
2657 config.rule_recurring_fine rrf,
2658 config.rule_max_fine rmf,
2661 rcd.id = ' || this_duration_rule || ' AND
2662 rrf.id = ' || this_fine_rule || ' AND
2663 rmf.id = ' || this_max_fine_rule || ' AND
2664 ac.id = c.target_copy AND
2665 c.id = ' || circ || ';');
2667 -- Keep track of where we are in the process
2669 IF (n % 100 = 0) THEN
2670 RAISE INFO '%', n || ' of ' || n_circs
2671 || ' (' || (100*n/n_circs) || '%) circs updated.';
2679 $$ LANGUAGE plpgsql;
2684 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2686 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2687 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2689 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2690 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2693 c TEXT := schemaname || '.asset_copy_legacy';
2694 sc TEXT := schemaname || '.asset_stat_cat';
2695 sce TEXT := schemaname || '.asset_stat_cat_entry';
2696 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2702 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2704 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2706 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2707 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2708 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2715 $$ LANGUAGE plpgsql;
2717 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2719 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2720 -- This will assign standing penalties as needed.
2728 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2730 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2732 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2742 $$ LANGUAGE plpgsql;
2745 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2748 INSERT INTO metabib.metarecord (fingerprint, master_record)
2749 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2750 FROM biblio.record_entry b
2752 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)
2753 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2754 ORDER BY b.fingerprint, b.quality DESC;
2755 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2757 FROM biblio.record_entry r
2758 JOIN metabib.metarecord m USING (fingerprint)
2759 WHERE NOT r.deleted;
2762 $$ LANGUAGE plpgsql;
2765 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2768 INSERT INTO metabib.metarecord (fingerprint, master_record)
2769 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2770 FROM biblio.record_entry b
2772 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)
2773 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2774 ORDER BY b.fingerprint, b.quality DESC;
2775 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2777 FROM biblio.record_entry r
2778 JOIN metabib.metarecord m USING (fingerprint)
2780 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);
2783 $$ LANGUAGE plpgsql;
2786 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2788 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2789 -- Then SELECT migration_tools.create_cards('m_foo');
2792 u TEXT := schemaname || '.actor_usr_legacy';
2793 c TEXT := schemaname || '.actor_card';
2797 EXECUTE ('DELETE FROM ' || c || ';');
2798 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2799 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2805 $$ LANGUAGE plpgsql;
2808 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2810 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2812 my ($marcxml, $shortname) = @_;
2815 use MARC::File::XML;
2820 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2822 foreach my $field ( $marc->field('856') ) {
2823 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2824 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2825 $field->add_subfields( '9' => $shortname );
2826 $field->update( ind2 => '0');
2830 $xml = $marc->as_xml_record;
2831 $xml =~ s/^<\?.+?\?>$//mo;
2833 $xml =~ s/>\s+</></sgo;
2838 $$ LANGUAGE PLPERLU STABLE;
2840 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2842 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2844 my ($marcxml, $shortname) = @_;
2847 use MARC::File::XML;
2852 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2854 foreach my $field ( $marc->field('856') ) {
2855 if ( ! $field->as_string('9') ) {
2856 $field->add_subfields( '9' => $shortname );
2860 $xml = $marc->as_xml_record;
2861 $xml =~ s/^<\?.+?\?>$//mo;
2863 $xml =~ s/>\s+</></sgo;
2868 $$ LANGUAGE PLPERLU STABLE;
2871 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2883 -- Bail out if asked to change the label to ##URI##
2884 IF new_label = '##URI##' THEN
2888 -- Gather information
2889 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2890 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2891 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2893 -- Bail out if the label already is ##URI##
2894 IF old_label = '##URI##' THEN
2898 -- Bail out if the call number label is already correct
2899 IF new_volume = old_volume THEN
2903 -- Check whether we already have a destination volume available
2904 SELECT id INTO new_volume FROM asset.call_number
2907 owning_lib = owner AND
2908 label = new_label AND
2911 -- Create destination volume if needed
2913 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2914 VALUES (1, 1, bib, owner, new_label, cn_class);
2915 SELECT id INTO new_volume FROM asset.call_number
2918 owning_lib = owner AND
2919 label = new_label AND
2923 -- Move copy to destination
2924 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2926 -- Delete source volume if it is now empty
2927 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2929 DELETE FROM asset.call_number WHERE id = old_volume;
2934 $$ LANGUAGE plpgsql;
2936 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2941 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2945 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2946 $zipdata{$zip} = [$city, $state, $county];
2949 if (defined $zipdata{$input}) {
2950 my ($city, $state, $county) = @{$zipdata{$input}};
2951 return [$city, $state, $county];
2952 } elsif (defined $zipdata{substr $input, 0, 5}) {
2953 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2954 return [$city, $state, $county];
2956 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2959 $$ LANGUAGE PLPERLU STABLE;
2961 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2968 errors_found BOOLEAN;
2970 parent_shortname TEXT;
2976 type_parent_depth INT;
2981 errors_found := FALSE;
2983 -- Checking actor.org_unit_type
2985 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2987 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2988 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2990 IF type_parent IS NOT NULL THEN
2992 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2994 IF type_depth - type_parent_depth <> 1 THEN
2995 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2996 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2997 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2998 ou_type_name, type_depth, parent_type, type_parent_depth;
2999 errors_found := TRUE;
3007 -- Checking actor.org_unit
3009 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
3011 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
3012 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;
3013 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;
3014 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
3015 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
3016 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;
3017 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;
3019 IF ou_parent IS NOT NULL THEN
3021 IF (org_unit_depth - parent_depth <> 1) OR (
3022 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
3024 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
3025 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
3026 errors_found := TRUE;
3033 IF NOT errors_found THEN
3034 RAISE INFO 'No errors found.';
3041 $$ LANGUAGE plpgsql;
3044 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
3048 DELETE FROM asset.opac_visible_copies;
3050 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
3052 cp.id, cp.circ_lib, cn.record
3055 JOIN asset.call_number cn ON (cn.id = cp.call_number)
3056 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3057 JOIN asset.copy_location cl ON (cp.location = cl.id)
3058 JOIN config.copy_status cs ON (cp.status = cs.id)
3059 JOIN biblio.record_entry b ON (cn.record = b.id)
3068 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
3072 $$ LANGUAGE plpgsql;
3075 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
3081 old_owning_lib INTEGER;
3087 -- Gather information
3088 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
3089 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
3090 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
3092 -- Bail out if the new_owning_lib is not the ID of an org_unit
3093 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
3095 '% is not a valid actor.org_unit ID; no change made.',
3100 -- Bail out discreetly if the owning_lib is already correct
3101 IF new_owning_lib = old_owning_lib THEN
3105 -- Check whether we already have a destination volume available
3106 SELECT id INTO new_volume FROM asset.call_number
3109 owning_lib = new_owning_lib AND
3110 label = old_label AND
3113 -- Create destination volume if needed
3115 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
3116 VALUES (1, 1, bib, new_owning_lib, old_label);
3117 SELECT id INTO new_volume FROM asset.call_number
3120 owning_lib = new_owning_lib AND
3121 label = old_label AND
3125 -- Move copy to destination
3126 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
3128 -- Delete source volume if it is now empty
3129 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
3131 DELETE FROM asset.call_number WHERE id = old_volume;
3136 $$ LANGUAGE plpgsql;
3139 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
3141 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
3144 new_owning_lib INTEGER;
3148 -- Parse the new_owner as an org unit ID or shortname
3149 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
3150 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
3151 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3152 ELSIF new_owner ~ E'^[0-9]+$' THEN
3153 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
3156 E'You don\'t need to put the actor.org_unit ID in quotes; '
3157 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
3158 new_owning_lib := new_owner::INTEGER;
3159 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3163 '% is not a valid actor.org_unit shortname or ID; no change made.',
3170 $$ LANGUAGE plpgsql;
3172 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
3175 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3178 MARC::Charset->assume_unicode(1);
3183 my $r = MARC::Record->new_from_xml( $xml );
3184 my $output_xml = $r->as_xml_record();
3192 $func$ LANGUAGE PLPERLU;
3193 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
3195 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3197 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3198 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3199 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
3200 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3201 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3202 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
3203 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3204 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3205 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
3206 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3207 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3208 $$) TO '$$ || dir || $$/asset_copy_location'$$;
3209 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3210 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3211 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3212 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3213 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3214 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
3215 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3216 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3217 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
3218 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
3219 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
3220 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
3221 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
3222 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
3224 $FUNC$ LANGUAGE PLPGSQL;
3226 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3228 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
3229 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
3230 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
3231 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
3232 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3233 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
3234 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
3236 -- import any new circ rules
3237 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3238 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3239 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3240 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3242 -- and permission groups
3243 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3246 $FUNC$ LANGUAGE PLPGSQL;
3249 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$
3258 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3259 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3260 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
3261 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3262 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3263 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3264 FOR name IN EXECUTE loopq LOOP
3265 EXECUTE existsq INTO ct USING name;
3267 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3268 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3269 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3270 EXECUTE copyst USING name;
3274 $FUNC$ LANGUAGE PLPGSQL;
3276 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3283 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3284 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;
3285 FOR id IN EXECUTE loopq USING delimiter LOOP
3286 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3287 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3288 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3289 EXECUTE splitst USING id, delimiter;
3292 $FUNC$ LANGUAGE PLPGSQL;
3294 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3300 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3303 MARC::Charset->assume_unicode(1);
3305 my $target_xml = shift;
3306 my $source_xml = shift;
3312 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3316 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3321 my $source_id = $source->subfield('901', 'c');
3322 $source_id = $source->subfield('903', 'a') unless $source_id;
3323 my $target_id = $target->subfield('901', 'c');
3324 $target_id = $target->subfield('903', 'a') unless $target_id;
3326 my %existing_fields;
3327 foreach my $tag (@$tags) {
3328 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3329 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3330 $target->insert_fields_ordered(map { $_->clone() } @to_add);
3332 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3336 my $xml = $target->as_xml_record;
3337 $xml =~ s/^<\?.+?\?>$//mo;
3339 $xml =~ s/>\s+</></sgo;
3343 $func$ LANGUAGE PLPERLU;
3344 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.';
3346 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3352 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3355 my $in_tags = shift;
3356 my $in_values = shift;
3358 # hack-and-slash parsing of array-passed-as-string;
3359 # this can go away once everybody is running Postgres 9.1+
3360 my $csv = Text::CSV->new({binary => 1});
3363 my $status = $csv->parse($in_tags);
3364 my $tags = [ $csv->fields() ];
3365 $in_values =~ s/^{//;
3366 $in_values =~ s/}$//;
3367 $status = $csv->parse($in_values);
3368 my $values = [ $csv->fields() ];
3370 my $marc = MARC::Record->new();
3372 $marc->leader('00000nam a22000007 4500');
3373 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3375 foreach my $i (0..$#$tags) {
3377 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3380 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3381 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3383 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3387 my $xml = $marc->as_xml_record;
3388 $xml =~ s/^<\?.+?\?>$//mo;
3390 $xml =~ s/>\s+</></sgo;
3394 $func$ LANGUAGE PLPERLU;
3395 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3396 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3397 The second argument is an array of text containing the values to plug into each field.
3398 If the value for a given field is NULL or the empty string, it is not inserted.
3401 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3407 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3410 my $in_tags = shift;
3411 my $in_ind1 = shift;
3412 my $in_ind2 = shift;
3413 my $in_values = shift;
3415 # hack-and-slash parsing of array-passed-as-string;
3416 # this can go away once everybody is running Postgres 9.1+
3417 my $csv = Text::CSV->new({binary => 1});
3420 my $status = $csv->parse($in_tags);
3421 my $tags = [ $csv->fields() ];
3424 $status = $csv->parse($in_ind1);
3425 my $ind1s = [ $csv->fields() ];
3428 $status = $csv->parse($in_ind2);
3429 my $ind2s = [ $csv->fields() ];
3430 $in_values =~ s/^{//;
3431 $in_values =~ s/}$//;
3432 $status = $csv->parse($in_values);
3433 my $values = [ $csv->fields() ];
3435 my $marc = MARC::Record->new();
3437 $marc->leader('00000nam a22000007 4500');
3438 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3440 foreach my $i (0..$#$tags) {
3442 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3445 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3446 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3448 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3452 my $xml = $marc->as_xml_record;
3453 $xml =~ s/^<\?.+?\?>$//mo;
3455 $xml =~ s/>\s+</></sgo;
3459 $func$ LANGUAGE PLPERLU;
3460 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.
3461 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3462 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3463 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3464 The fourth argument is an array of text containing the values to plug into each field.
3465 If the value for a given field is NULL or the empty string, it is not inserted.
3468 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3470 my ($marcxml, $tag, $pos, $value) = @_;
3473 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3477 MARC::Charset->assume_unicode(1);
3479 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3480 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3481 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3482 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3486 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3488 foreach my $field ($marc->field($tag)) {
3489 $field->update("ind$pos" => $value);
3491 $xml = $marc->as_xml_record;
3492 $xml =~ s/^<\?.+?\?>$//mo;
3494 $xml =~ s/>\s+</></sgo;
3498 $func$ LANGUAGE PLPERLU;
3500 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3501 The first argument is a MARCXML string.
3502 The second argument is a MARC tag.
3503 The third argument is the indicator position, either 1 or 2.
3504 The fourth argument is the character to set the indicator value to.
3505 All occurences of the specified field will be changed.
3506 The function returns the revised MARCXML string.$$;
3508 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3513 first_name TEXT DEFAULT '',
3514 last_name TEXT DEFAULT ''
3515 ) RETURNS VOID AS $func$
3517 RAISE NOTICE '%', org ;
3518 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3519 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3520 FROM actor.org_unit aou, permission.grp_tree pgt
3521 WHERE aou.shortname = org
3522 AND pgt.name = perm_group;
3527 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3528 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3530 target_event_def ALIAS FOR $1;
3533 DROP TABLE IF EXISTS new_atevdefs;
3534 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3535 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3536 INSERT INTO action_trigger.event_definition (
3557 ,name || ' (clone of '||target_event_def||')'
3573 action_trigger.event_definition
3575 id = target_event_def
3577 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3578 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3579 INSERT INTO action_trigger.environment (
3585 currval('action_trigger.event_definition_id_seq')
3590 action_trigger.environment
3592 event_def = target_event_def
3594 INSERT INTO action_trigger.event_params (
3599 currval('action_trigger.event_definition_id_seq')
3603 action_trigger.event_params
3605 event_def = target_event_def
3608 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);
3610 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3612 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3613 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3615 target_event_def ALIAS FOR $1;
3617 new_interval ALIAS FOR $3;
3619 DROP TABLE IF EXISTS new_atevdefs;
3620 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3621 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3622 INSERT INTO action_trigger.event_definition (
3643 ,name || ' (clone of '||target_event_def||')'
3659 action_trigger.event_definition
3661 id = target_event_def
3663 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3664 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3665 INSERT INTO action_trigger.environment (
3671 currval('action_trigger.event_definition_id_seq')
3676 action_trigger.environment
3678 event_def = target_event_def
3680 INSERT INTO action_trigger.event_params (
3685 currval('action_trigger.event_definition_id_seq')
3689 action_trigger.event_params
3691 event_def = target_event_def
3694 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);
3696 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3698 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3699 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3702 target_event_defs ALIAS FOR $2;
3704 DROP TABLE IF EXISTS new_atevdefs;
3705 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3706 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3707 INSERT INTO action_trigger.event_definition (
3728 ,name || ' (clone of '||target_event_defs[i]||')'
3744 action_trigger.event_definition
3746 id = target_event_defs[i]
3748 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3749 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3750 INSERT INTO action_trigger.environment (
3756 currval('action_trigger.event_definition_id_seq')
3761 action_trigger.environment
3763 event_def = target_event_defs[i]
3765 INSERT INTO action_trigger.event_params (
3770 currval('action_trigger.event_definition_id_seq')
3774 action_trigger.event_params
3776 event_def = target_event_defs[i]
3779 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3781 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3783 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3785 action_trigger.event
3789 ,complete_time = NULL
3790 ,update_process = NULL
3792 ,template_output = NULL
3793 ,error_output = NULL
3794 ,async_output = NULL
3799 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3803 use MARC::File::XML;
3808 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3809 $field = $marc->leader();
3812 $$ LANGUAGE PLPERLU STABLE;
3814 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3815 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3818 use MARC::File::XML;
3823 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3824 $field = $marc->field($tag);
3826 return $field->as_string($subfield,$delimiter) if $field;
3828 $$ LANGUAGE PLPERLU STABLE;
3830 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3831 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3834 use MARC::File::XML;
3839 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3840 @fields = $marc->field($tag);
3843 foreach my $field (@fields) {
3844 push @texts, $field->as_string($subfield,$delimiter);
3847 $$ LANGUAGE PLPERLU STABLE;
3849 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3850 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3853 use MARC::File::XML;
3858 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3859 @fields = $marc->field($tag);
3862 foreach my $field (@fields) {
3863 if ($field->as_string() =~ qr/$match/) {
3864 push @texts, $field->as_string($subfield,$delimiter);
3868 $$ LANGUAGE PLPERLU STABLE;
3870 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3871 SELECT action.find_hold_matrix_matchpoint(
3872 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3873 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3874 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3875 (SELECT usr FROM action.hold_request WHERE id = $1),
3876 (SELECT requestor FROM action.hold_request WHERE id = $1)
3880 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3881 SELECT action.hold_request_permit_test(
3882 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3883 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3884 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3885 (SELECT usr FROM action.hold_request WHERE id = $1),
3886 (SELECT requestor FROM action.hold_request WHERE id = $1)
3890 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3891 SELECT action.find_circ_matrix_matchpoint(
3892 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3893 (SELECT target_copy FROM action.circulation WHERE id = $1),
3894 (SELECT usr FROM action.circulation WHERE id = $1),
3896 NULLIF(phone_renewal,false),
3897 NULLIF(desk_renewal,false),
3898 NULLIF(opac_renewal,false),
3900 ) FROM action.circulation WHERE id = $1
3905 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3910 RAISE EXCEPTION 'assertion';
3913 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3915 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3921 RAISE EXCEPTION '%', msg;
3924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3926 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3929 fail_msg ALIAS FOR $2;
3930 success_msg ALIAS FOR $3;
3933 RAISE EXCEPTION '%', fail_msg;
3937 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3939 -- push bib sequence and return starting value for reserved range
3940 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3942 bib_count ALIAS FOR $1;
3945 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3947 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3952 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3954 -- set a new salted password
3956 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3958 usr_id ALIAS FOR $1;
3959 plain_passwd ALIAS FOR $2;
3964 SELECT actor.create_salt('main') INTO plain_salt;
3966 SELECT MD5(plain_passwd) INTO md5_passwd;
3968 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3976 -- convenience functions for handling copy_location maps
3977 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3978 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3981 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3983 table_schema ALIAS FOR $1;
3984 table_name ALIAS FOR $2;
3985 org_shortname ALIAS FOR $3;
3986 org_range ALIAS FOR $4;
3987 make_assertion ALIAS FOR $5;
3990 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3991 -- though we'll still use the passed org for the full path traversal when needed
3992 x_org_found BOOLEAN;
3998 EXECUTE 'SELECT EXISTS (
4000 FROM information_schema.columns
4001 WHERE table_schema = $1
4003 and column_name = ''desired_shelf''
4004 )' INTO proceed USING table_schema, table_name;
4006 RAISE EXCEPTION 'Missing column desired_shelf';
4009 EXECUTE 'SELECT EXISTS (
4011 FROM information_schema.columns
4012 WHERE table_schema = $1
4014 and column_name = ''x_org''
4015 )' INTO x_org_found USING table_schema, table_name;
4017 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4019 RAISE EXCEPTION 'Cannot find org by shortname';
4022 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4024 EXECUTE 'ALTER TABLE '
4025 || quote_ident(table_name)
4026 || ' DROP COLUMN IF EXISTS x_shelf';
4027 EXECUTE 'ALTER TABLE '
4028 || quote_ident(table_name)
4029 || ' ADD COLUMN x_shelf INTEGER';
4032 RAISE INFO 'Found x_org column';
4033 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4034 || ' SET x_shelf = b.id FROM asset_copy_location b'
4035 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4036 || ' AND b.owning_lib = x_org'
4037 || ' AND NOT b.deleted';
4038 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4039 || ' SET x_shelf = b.id FROM asset.copy_location b'
4040 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4041 || ' AND b.owning_lib = x_org'
4042 || ' AND x_shelf IS NULL'
4043 || ' AND NOT b.deleted';
4045 RAISE INFO 'Did not find x_org column';
4046 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4047 || ' SET x_shelf = b.id FROM asset_copy_location b'
4048 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4049 || ' AND b.owning_lib = $1'
4050 || ' AND NOT b.deleted'
4052 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4053 || ' SET x_shelf = b.id FROM asset_copy_location b'
4054 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4055 || ' AND b.owning_lib = $1'
4056 || ' AND x_shelf IS NULL'
4057 || ' AND NOT b.deleted'
4061 FOREACH o IN ARRAY org_list LOOP
4062 RAISE INFO 'Considering org %', o;
4063 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4064 || ' SET x_shelf = b.id FROM asset.copy_location b'
4065 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4066 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
4067 || ' AND NOT b.deleted'
4069 GET DIAGNOSTICS row_count = ROW_COUNT;
4070 RAISE INFO 'Updated % rows', row_count;
4073 IF make_assertion THEN
4074 EXECUTE 'SELECT migration_tools.assert(
4075 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
4076 ''Cannot find a desired location'',
4077 ''Found all desired locations''
4082 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4084 -- convenience functions for handling circmod maps
4086 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
4088 table_schema ALIAS FOR $1;
4089 table_name ALIAS FOR $2;
4092 EXECUTE 'SELECT EXISTS (
4094 FROM information_schema.columns
4095 WHERE table_schema = $1
4097 and column_name = ''desired_circmod''
4098 )' INTO proceed USING table_schema, table_name;
4100 RAISE EXCEPTION 'Missing column desired_circmod';
4103 EXECUTE 'ALTER TABLE '
4104 || quote_ident(table_name)
4105 || ' DROP COLUMN IF EXISTS x_circmod';
4106 EXECUTE 'ALTER TABLE '
4107 || quote_ident(table_name)
4108 || ' ADD COLUMN x_circmod TEXT';
4110 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4111 || ' SET x_circmod = code FROM config.circ_modifier b'
4112 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
4114 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4115 || ' SET x_circmod = code FROM config.circ_modifier b'
4116 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
4117 || ' AND x_circmod IS NULL';
4119 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4120 || ' SET x_circmod = code FROM config.circ_modifier b'
4121 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
4122 || ' AND x_circmod IS NULL';
4124 EXECUTE 'SELECT migration_tools.assert(
4125 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
4126 ''Cannot find a desired circulation modifier'',
4127 ''Found all desired circulation modifiers''
4131 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4133 -- convenience functions for handling item status maps
4135 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
4137 table_schema ALIAS FOR $1;
4138 table_name ALIAS FOR $2;
4141 EXECUTE 'SELECT EXISTS (
4143 FROM information_schema.columns
4144 WHERE table_schema = $1
4146 and column_name = ''desired_status''
4147 )' INTO proceed USING table_schema, table_name;
4149 RAISE EXCEPTION 'Missing column desired_status';
4152 EXECUTE 'ALTER TABLE '
4153 || quote_ident(table_name)
4154 || ' DROP COLUMN IF EXISTS x_status';
4155 EXECUTE 'ALTER TABLE '
4156 || quote_ident(table_name)
4157 || ' ADD COLUMN x_status INTEGER';
4159 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4160 || ' SET x_status = id FROM config.copy_status b'
4161 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
4163 EXECUTE 'SELECT migration_tools.assert(
4164 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
4165 ''Cannot find a desired copy status'',
4166 ''Found all desired copy statuses''
4170 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4172 -- convenience functions for handling org maps
4174 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
4176 table_schema ALIAS FOR $1;
4177 table_name ALIAS FOR $2;
4180 EXECUTE 'SELECT EXISTS (
4182 FROM information_schema.columns
4183 WHERE table_schema = $1
4185 and column_name = ''desired_org''
4186 )' INTO proceed USING table_schema, table_name;
4188 RAISE EXCEPTION 'Missing column desired_org';
4191 EXECUTE 'ALTER TABLE '
4192 || quote_ident(table_name)
4193 || ' DROP COLUMN IF EXISTS x_org';
4194 EXECUTE 'ALTER TABLE '
4195 || quote_ident(table_name)
4196 || ' ADD COLUMN x_org INTEGER';
4198 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4199 || ' SET x_org = b.id FROM actor.org_unit b'
4200 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
4202 EXECUTE 'SELECT migration_tools.assert(
4203 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
4204 ''Cannot find a desired org unit'',
4205 ''Found all desired org units''
4209 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4211 -- convenience function for handling desired_not_migrate
4213 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
4215 table_schema ALIAS FOR $1;
4216 table_name ALIAS FOR $2;
4219 EXECUTE 'SELECT EXISTS (
4221 FROM information_schema.columns
4222 WHERE table_schema = $1
4224 and column_name = ''desired_not_migrate''
4225 )' INTO proceed USING table_schema, table_name;
4227 RAISE EXCEPTION 'Missing column desired_not_migrate';
4230 EXECUTE 'ALTER TABLE '
4231 || quote_ident(table_name)
4232 || ' DROP COLUMN IF EXISTS x_migrate';
4233 EXECUTE 'ALTER TABLE '
4234 || quote_ident(table_name)
4235 || ' ADD COLUMN x_migrate BOOLEAN';
4237 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4238 || ' SET x_migrate = CASE'
4239 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4240 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4241 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4242 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4243 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4244 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4247 EXECUTE 'SELECT migration_tools.assert(
4248 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4249 ''Not all desired_not_migrate values understood'',
4250 ''All desired_not_migrate values understood''
4254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4256 -- convenience function for handling desired_not_migrate
4258 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4260 table_schema ALIAS FOR $1;
4261 table_name ALIAS FOR $2;
4264 EXECUTE 'SELECT EXISTS (
4266 FROM information_schema.columns
4267 WHERE table_schema = $1
4269 and column_name = ''desired_barred_or_blocked''
4270 )' INTO proceed USING table_schema, table_name;
4272 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4275 EXECUTE 'ALTER TABLE '
4276 || quote_ident(table_name)
4277 || ' DROP COLUMN IF EXISTS x_barred';
4278 EXECUTE 'ALTER TABLE '
4279 || quote_ident(table_name)
4280 || ' ADD COLUMN x_barred BOOLEAN';
4282 EXECUTE 'ALTER TABLE '
4283 || quote_ident(table_name)
4284 || ' DROP COLUMN IF EXISTS x_blocked';
4285 EXECUTE 'ALTER TABLE '
4286 || quote_ident(table_name)
4287 || ' ADD COLUMN x_blocked BOOLEAN';
4289 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4290 || ' SET x_barred = CASE'
4291 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4292 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4293 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4294 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4297 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4298 || ' SET x_blocked = CASE'
4299 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4300 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4301 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4302 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4305 EXECUTE 'SELECT migration_tools.assert(
4306 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4307 ''Not all desired_barred_or_blocked values understood'',
4308 ''All desired_barred_or_blocked values understood''
4312 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4314 -- convenience function for handling desired_profile
4316 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4318 table_schema ALIAS FOR $1;
4319 table_name ALIAS FOR $2;
4322 EXECUTE 'SELECT EXISTS (
4324 FROM information_schema.columns
4325 WHERE table_schema = $1
4327 and column_name = ''desired_profile''
4328 )' INTO proceed USING table_schema, table_name;
4330 RAISE EXCEPTION 'Missing column desired_profile';
4333 EXECUTE 'ALTER TABLE '
4334 || quote_ident(table_name)
4335 || ' DROP COLUMN IF EXISTS x_profile';
4336 EXECUTE 'ALTER TABLE '
4337 || quote_ident(table_name)
4338 || ' ADD COLUMN x_profile INTEGER';
4340 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4341 || ' SET x_profile = b.id FROM permission.grp_tree b'
4342 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4344 EXECUTE 'SELECT migration_tools.assert(
4345 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4346 ''Cannot find a desired profile'',
4347 ''Found all desired profiles''
4351 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4353 -- convenience function for handling desired actor stat cats
4355 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4357 table_schema ALIAS FOR $1;
4358 table_name ALIAS FOR $2;
4359 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4360 org_shortname ALIAS FOR $4;
4368 SELECT 'desired_sc' || field_suffix INTO sc;
4369 SELECT 'desired_sce' || field_suffix INTO sce;
4371 EXECUTE 'SELECT EXISTS (
4373 FROM information_schema.columns
4374 WHERE table_schema = $1
4376 and column_name = $3
4377 )' INTO proceed USING table_schema, table_name, sc;
4379 RAISE EXCEPTION 'Missing column %', sc;
4381 EXECUTE 'SELECT EXISTS (
4383 FROM information_schema.columns
4384 WHERE table_schema = $1
4386 and column_name = $3
4387 )' INTO proceed USING table_schema, table_name, sce;
4389 RAISE EXCEPTION 'Missing column %', sce;
4392 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4394 RAISE EXCEPTION 'Cannot find org by shortname';
4396 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4398 -- caller responsible for their own truncates though we try to prevent duplicates
4399 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4404 ' || quote_ident(table_name) || '
4406 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4410 WHERE owner = ANY ($2)
4411 AND name = BTRIM('||sc||')
4416 WHERE owner = ANY ($2)
4417 AND name = BTRIM('||sc||')
4420 USING org, org_list;
4422 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4427 WHERE owner = ANY ($2)
4428 AND BTRIM('||sc||') = BTRIM(name))
4431 WHERE owner = ANY ($2)
4432 AND BTRIM('||sc||') = BTRIM(name))
4437 ' || quote_ident(table_name) || '
4439 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4440 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4443 FROM actor.stat_cat_entry
4447 WHERE owner = ANY ($2)
4448 AND BTRIM('||sc||') = BTRIM(name)
4449 ) AND value = BTRIM('||sce||')
4450 AND owner = ANY ($2)
4454 FROM actor_stat_cat_entry
4458 WHERE owner = ANY ($2)
4459 AND BTRIM('||sc||') = BTRIM(name)
4460 ) AND value = BTRIM('||sce||')
4461 AND owner = ANY ($2)
4464 USING org, org_list;
4466 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4468 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4470 table_schema ALIAS FOR $1;
4471 table_name ALIAS FOR $2;
4472 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4473 org_shortname ALIAS FOR $4;
4481 SELECT 'desired_sc' || field_suffix INTO sc;
4482 SELECT 'desired_sce' || field_suffix INTO sce;
4483 EXECUTE 'SELECT EXISTS (
4485 FROM information_schema.columns
4486 WHERE table_schema = $1
4488 and column_name = $3
4489 )' INTO proceed USING table_schema, table_name, sc;
4491 RAISE EXCEPTION 'Missing column %', sc;
4493 EXECUTE 'SELECT EXISTS (
4495 FROM information_schema.columns
4496 WHERE table_schema = $1
4498 and column_name = $3
4499 )' INTO proceed USING table_schema, table_name, sce;
4501 RAISE EXCEPTION 'Missing column %', sce;
4504 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4506 RAISE EXCEPTION 'Cannot find org by shortname';
4509 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4511 EXECUTE 'ALTER TABLE '
4512 || quote_ident(table_name)
4513 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4514 EXECUTE 'ALTER TABLE '
4515 || quote_ident(table_name)
4516 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4517 EXECUTE 'ALTER TABLE '
4518 || quote_ident(table_name)
4519 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4520 EXECUTE 'ALTER TABLE '
4521 || quote_ident(table_name)
4522 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4525 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4527 x_sc' || field_suffix || ' = id
4529 (SELECT id, name, owner FROM actor_stat_cat
4530 UNION SELECT id, name, owner FROM actor.stat_cat) u
4532 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4533 AND u.owner = ANY ($1);'
4536 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4538 x_sce' || field_suffix || ' = id
4540 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4541 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4543 u.stat_cat = x_sc' || field_suffix || '
4544 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4545 AND u.owner = ANY ($1);'
4548 EXECUTE 'SELECT migration_tools.assert(
4549 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4550 ''Cannot find a desired stat cat'',
4551 ''Found all desired stat cats''
4554 EXECUTE 'SELECT migration_tools.assert(
4555 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4556 ''Cannot find a desired stat cat entry'',
4557 ''Found all desired stat cat entries''
4561 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4563 -- convenience functions for adding shelving locations
4564 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4565 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4571 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4574 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4575 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4576 IF return_id IS NOT NULL THEN
4584 $$ LANGUAGE plpgsql;
4586 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4588 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4589 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4595 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4598 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4600 SELECT INTO return_id id FROM
4601 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4602 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4603 IF return_id IS NOT NULL THEN
4611 $$ LANGUAGE plpgsql;
4613 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4614 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4621 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4623 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4624 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4628 $BODY$ LANGUAGE plpgsql;
4630 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4631 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4639 use MARC::File::XML (BinaryEncoding => 'utf8');
4641 binmode(STDERR, ':bytes');
4642 binmode(STDOUT, ':utf8');
4643 binmode(STDERR, ':utf8');
4645 my $marc_xml = shift;
4646 my $new_9_to_set = shift;
4649 $marc_xml =~ s/(<leader>.........)./${1}a/;
4652 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4655 #elog("could not parse $bibid: $@\n");
4656 import MARC::File::XML (BinaryEncoding => 'utf8');
4660 my @uris = $marc_xml->field('856');
4661 return $marc_xml->as_xml_record() unless @uris;
4663 foreach my $field (@uris) {
4664 my $ind1 = $field->indicator('1');
4665 if (!defined $ind1) { next; }
4666 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4667 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4668 my $ind2 = $field->indicator('2');
4669 if (!defined $ind2) { next; }
4670 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4671 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4672 $field->add_subfields( '9' => $new_9_to_set );
4675 return $marc_xml->as_xml_record();
4679 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4680 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4688 use MARC::File::XML (BinaryEncoding => 'utf8');
4690 binmode(STDERR, ':bytes');
4691 binmode(STDOUT, ':utf8');
4692 binmode(STDERR, ':utf8');
4694 my $marc_xml = shift;
4695 my $qualifying_match = shift;
4696 my $new_9_to_set = shift;
4699 $marc_xml =~ s/(<leader>.........)./${1}a/;
4702 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4705 #elog("could not parse $bibid: $@\n");
4706 import MARC::File::XML (BinaryEncoding => 'utf8');
4710 my @uris = $marc_xml->field('856');
4711 return $marc_xml->as_xml_record() unless @uris;
4713 foreach my $field (@uris) {
4714 if ($field->as_string() =~ qr/$qualifying_match/) {
4715 my $ind1 = $field->indicator('1');
4716 if (!defined $ind1) { next; }
4717 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4718 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4719 my $ind2 = $field->indicator('2');
4720 if (!defined $ind2) { next; }
4721 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4722 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4723 $field->add_subfields( '9' => $new_9_to_set );
4727 return $marc_xml->as_xml_record();
4731 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4732 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4740 use MARC::File::XML (BinaryEncoding => 'utf8');
4742 binmode(STDERR, ':bytes');
4743 binmode(STDOUT, ':utf8');
4744 binmode(STDERR, ':utf8');
4746 my $marc_xml = shift;
4747 my $substring_old_value = shift;
4748 my $new_value = shift;
4749 my $fix_indicators = shift;
4751 $marc_xml =~ s/(<leader>.........)./${1}a/;
4754 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4757 #elog("could not parse $bibid: $@\n");
4758 import MARC::File::XML (BinaryEncoding => 'utf8');
4762 my @uris = $marc_xml->field('856');
4763 return $marc_xml->as_xml_record() unless @uris;
4765 foreach my $field (@uris) {
4766 my $ind1 = $field->indicator('1');
4767 if (defined $ind1) {
4768 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4769 $field->set_indicator(1,'4');
4772 my $ind2 = $field->indicator('2');
4773 if (defined $ind2) {
4774 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4775 $field->set_indicator(2,'0');
4778 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4779 $field->delete_subfield('9');
4780 $field->add_subfields( '9' => $new_value );
4782 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4785 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4786 $marc_xml->insert_fields_ordered( values( %hash ) );
4788 return $marc_xml->as_xml_record();
4792 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4793 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)
4801 use MARC::File::XML (BinaryEncoding => 'utf8');
4803 binmode(STDERR, ':bytes');
4804 binmode(STDOUT, ':utf8');
4805 binmode(STDERR, ':utf8');
4807 my $marc_xml = shift;
4808 my $qualifying_match = shift;
4809 my $substring_old_value = shift;
4810 my $new_value = shift;
4811 my $fix_indicators = shift;
4813 $marc_xml =~ s/(<leader>.........)./${1}a/;
4816 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4819 #elog("could not parse $bibid: $@\n");
4820 import MARC::File::XML (BinaryEncoding => 'utf8');
4824 my @unqualified_uris = $marc_xml->field('856');
4826 foreach my $field (@unqualified_uris) {
4827 if ($field->as_string() =~ qr/$qualifying_match/) {
4831 return $marc_xml->as_xml_record() unless @uris;
4833 foreach my $field (@uris) {
4834 my $ind1 = $field->indicator('1');
4835 if (defined $ind1) {
4836 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4837 $field->set_indicator(1,'4');
4840 my $ind2 = $field->indicator('2');
4841 if (defined $ind2) {
4842 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4843 $field->set_indicator(2,'0');
4846 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4847 $field->delete_subfield('9');
4848 $field->add_subfields( '9' => $new_value );
4850 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4853 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4854 $marc_xml->insert_fields_ordered( values( %hash ) );
4856 return $marc_xml->as_xml_record();
4861 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4862 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4870 use MARC::File::XML (BinaryEncoding => 'utf8');
4872 binmode(STDERR, ':bytes');
4873 binmode(STDOUT, ':utf8');
4874 binmode(STDERR, ':utf8');
4876 my $marc_xml = shift;
4879 $marc_xml =~ s/(<leader>.........)./${1}a/;
4882 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4885 #elog("could not parse $bibid: $@\n");
4886 import MARC::File::XML (BinaryEncoding => 'utf8');
4890 my @fields = $marc_xml->field($tag);
4891 return $marc_xml->as_xml_record() unless @fields;
4893 $marc_xml->delete_fields(@fields);
4895 return $marc_xml->as_xml_record();
4899 -- removes tags from record based on tag, subfield and evidence
4900 -- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
4901 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
4902 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence TEXT)
4910 use MARC::File::XML (BinaryEncoding => 'utf8');
4912 binmode(STDERR, ':bytes');
4913 binmode(STDOUT, ':utf8');
4914 binmode(STDERR, ':utf8');
4916 my $marc_xml = shift;
4918 my $subfield = shift;
4919 my $evidence = shift;
4921 $marc_xml =~ s/(<leader>.........)./${1}a/;
4924 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4927 #elog("could not parse $bibid: $@\n");
4928 import MARC::File::XML (BinaryEncoding => 'utf8');
4932 my @fields = $marc_xml->field($tag);
4933 return $marc_xml->as_xml_record() unless @fields;
4935 my @fields_to_delete;
4937 foreach my $f (@fields) {
4938 my $sf = lc($f->as_string($subfield));
4939 if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
4942 $marc_xml->delete_fields(@fields_to_delete);
4944 return $marc_xml->as_xml_record();
4949 -- consolidate marc tag
4950 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
4951 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
4959 use MARC::File::XML (BinaryEncoding => 'utf8');
4961 binmode(STDERR, ':bytes');
4962 binmode(STDOUT, ':utf8');
4963 binmode(STDERR, ':utf8');
4965 my $marc_xml = shift;
4968 $marc_xml =~ s/(<leader>.........)./${1}a/;
4971 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4974 #elog("could not parse $bibid: $@\n");
4975 import MARC::File::XML (BinaryEncoding => 'utf8');
4979 my @fields = $marc_xml->field($tag);
4980 return $marc_xml->as_xml_record() unless @fields;
4982 my @combined_subfield_refs = ();
4983 my @combined_subfields = ();
4984 foreach my $field (@fields) {
4985 my @subfield_refs = $field->subfields();
4986 push @combined_subfield_refs, @subfield_refs;
4989 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
4991 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
4992 my ($code,$data) = @$tuple;
4993 unshift( @combined_subfields, $code, $data );
4996 $marc_xml->delete_fields(@fields);
4998 my $new_field = new MARC::Field(
5000 $fields[0]->indicator(1),
5001 $fields[0]->indicator(2),
5005 $marc_xml->insert_grouped_field( $new_field );
5007 return $marc_xml->as_xml_record();
5011 -- convenience function for linking to the item staging table
5013 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5015 table_schema ALIAS FOR $1;
5016 table_name ALIAS FOR $2;
5017 foreign_column_name ALIAS FOR $3;
5018 main_column_name ALIAS FOR $4;
5019 btrim_desired ALIAS FOR $5;
5022 EXECUTE 'SELECT EXISTS (
5024 FROM information_schema.columns
5025 WHERE table_schema = $1
5027 and column_name = $3
5028 )' INTO proceed USING table_schema, table_name, foreign_column_name;
5030 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5033 EXECUTE 'SELECT EXISTS (
5035 FROM information_schema.columns
5036 WHERE table_schema = $1
5037 AND table_name = ''asset_copy_legacy''
5038 and column_name = $2
5039 )' INTO proceed USING table_schema, main_column_name;
5041 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
5044 EXECUTE 'ALTER TABLE '
5045 || quote_ident(table_name)
5046 || ' DROP COLUMN IF EXISTS x_item';
5047 EXECUTE 'ALTER TABLE '
5048 || quote_ident(table_name)
5049 || ' ADD COLUMN x_item BIGINT';
5051 IF btrim_desired THEN
5052 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5053 || ' SET x_item = b.id FROM asset_copy_legacy b'
5054 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5055 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5057 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5058 || ' SET x_item = b.id FROM asset_copy_legacy b'
5059 || ' WHERE a.' || quote_ident(foreign_column_name)
5060 || ' = b.' || quote_ident(main_column_name);
5063 --EXECUTE 'SELECT migration_tools.assert(
5064 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
5065 -- ''Cannot link every barcode'',
5066 -- ''Every barcode linked''
5070 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5072 -- convenience function for linking to the user staging table
5074 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5076 table_schema ALIAS FOR $1;
5077 table_name ALIAS FOR $2;
5078 foreign_column_name ALIAS FOR $3;
5079 main_column_name ALIAS FOR $4;
5080 btrim_desired ALIAS FOR $5;
5083 EXECUTE 'SELECT EXISTS (
5085 FROM information_schema.columns
5086 WHERE table_schema = $1
5088 and column_name = $3
5089 )' INTO proceed USING table_schema, table_name, foreign_column_name;
5091 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5094 EXECUTE 'SELECT EXISTS (
5096 FROM information_schema.columns
5097 WHERE table_schema = $1
5098 AND table_name = ''actor_usr_legacy''
5099 and column_name = $2
5100 )' INTO proceed USING table_schema, main_column_name;
5102 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
5105 EXECUTE 'ALTER TABLE '
5106 || quote_ident(table_name)
5107 || ' DROP COLUMN IF EXISTS x_user';
5108 EXECUTE 'ALTER TABLE '
5109 || quote_ident(table_name)
5110 || ' ADD COLUMN x_user INTEGER';
5112 IF btrim_desired THEN
5113 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5114 || ' SET x_user = b.id FROM actor_usr_legacy b'
5115 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5116 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5118 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5119 || ' SET x_user = b.id FROM actor_usr_legacy b'
5120 || ' WHERE a.' || quote_ident(foreign_column_name)
5121 || ' = b.' || quote_ident(main_column_name);
5124 --EXECUTE 'SELECT migration_tools.assert(
5125 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
5126 -- ''Cannot link every barcode'',
5127 -- ''Every barcode linked''
5131 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5133 -- convenience function for linking two tables
5134 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
5135 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5137 table_schema ALIAS FOR $1;
5138 table_a ALIAS FOR $2;
5139 column_a ALIAS FOR $3;
5140 table_b ALIAS FOR $4;
5141 column_b ALIAS FOR $5;
5142 column_x ALIAS FOR $6;
5143 btrim_desired ALIAS FOR $7;
5146 EXECUTE 'SELECT EXISTS (
5148 FROM information_schema.columns
5149 WHERE table_schema = $1
5151 and column_name = $3
5152 )' INTO proceed USING table_schema, table_a, column_a;
5154 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5157 EXECUTE 'SELECT EXISTS (
5159 FROM information_schema.columns
5160 WHERE table_schema = $1
5162 and column_name = $3
5163 )' INTO proceed USING table_schema, table_b, column_b;
5165 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5168 EXECUTE 'ALTER TABLE '
5169 || quote_ident(table_b)
5170 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5171 EXECUTE 'ALTER TABLE '
5172 || quote_ident(table_b)
5173 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
5175 IF btrim_desired THEN
5176 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5177 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5178 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5179 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5181 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5182 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5183 || ' WHERE a.' || quote_ident(column_a)
5184 || ' = b.' || quote_ident(column_b);
5188 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5190 -- convenience function for linking two tables, but copying column w into column x instead of "id"
5191 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
5192 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5194 table_schema ALIAS FOR $1;
5195 table_a ALIAS FOR $2;
5196 column_a ALIAS FOR $3;
5197 table_b ALIAS FOR $4;
5198 column_b ALIAS FOR $5;
5199 column_w ALIAS FOR $6;
5200 column_x ALIAS FOR $7;
5201 btrim_desired ALIAS FOR $8;
5204 EXECUTE 'SELECT EXISTS (
5206 FROM information_schema.columns
5207 WHERE table_schema = $1
5209 and column_name = $3
5210 )' INTO proceed USING table_schema, table_a, column_a;
5212 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5215 EXECUTE 'SELECT EXISTS (
5217 FROM information_schema.columns
5218 WHERE table_schema = $1
5220 and column_name = $3
5221 )' INTO proceed USING table_schema, table_b, column_b;
5223 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5226 EXECUTE 'ALTER TABLE '
5227 || quote_ident(table_b)
5228 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5229 EXECUTE 'ALTER TABLE '
5230 || quote_ident(table_b)
5231 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
5233 IF btrim_desired THEN
5234 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5235 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5236 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5237 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5239 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5240 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5241 || ' WHERE a.' || quote_ident(column_a)
5242 || ' = b.' || quote_ident(column_b);
5246 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5248 -- 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
5249 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
5250 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5252 table_schema ALIAS FOR $1;
5253 table_a ALIAS FOR $2;
5254 column_a ALIAS FOR $3;
5255 table_b ALIAS FOR $4;
5256 column_b ALIAS FOR $5;
5257 column_w ALIAS FOR $6;
5258 column_x ALIAS FOR $7;
5261 EXECUTE 'SELECT EXISTS (
5263 FROM information_schema.columns
5264 WHERE table_schema = $1
5266 and column_name = $3
5267 )' INTO proceed USING table_schema, table_a, column_a;
5269 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5272 EXECUTE 'SELECT EXISTS (
5274 FROM information_schema.columns
5275 WHERE table_schema = $1
5277 and column_name = $3
5278 )' INTO proceed USING table_schema, table_b, column_b;
5280 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5283 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5284 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5285 || ' WHERE a.' || quote_ident(column_a)
5286 || ' = b.' || quote_ident(column_b);
5289 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5291 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5293 table_schema ALIAS FOR $1;
5294 table_a ALIAS FOR $2;
5295 column_a ALIAS FOR $3;
5296 table_b ALIAS FOR $4;
5297 column_b ALIAS FOR $5;
5298 column_w ALIAS FOR $6;
5299 column_x ALIAS FOR $7;
5302 EXECUTE 'SELECT EXISTS (
5304 FROM information_schema.columns
5305 WHERE table_schema = $1
5307 and column_name = $3
5308 )' INTO proceed USING table_schema, table_a, column_a;
5310 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5313 EXECUTE 'SELECT EXISTS (
5315 FROM information_schema.columns
5316 WHERE table_schema = $1
5318 and column_name = $3
5319 )' INTO proceed USING table_schema, table_b, column_b;
5321 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5324 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5325 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5326 || ' WHERE a.' || quote_ident(column_a)
5327 || ' = b.' || quote_ident(column_b)
5328 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5331 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5333 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5335 table_schema ALIAS FOR $1;
5336 table_a ALIAS FOR $2;
5337 column_a ALIAS FOR $3;
5338 table_b ALIAS FOR $4;
5339 column_b ALIAS FOR $5;
5340 column_w ALIAS FOR $6;
5341 column_x ALIAS FOR $7;
5344 EXECUTE 'SELECT EXISTS (
5346 FROM information_schema.columns
5347 WHERE table_schema = $1
5349 and column_name = $3
5350 )' INTO proceed USING table_schema, table_a, column_a;
5352 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5355 EXECUTE 'SELECT EXISTS (
5357 FROM information_schema.columns
5358 WHERE table_schema = $1
5360 and column_name = $3
5361 )' INTO proceed USING table_schema, table_b, column_b;
5363 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5366 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5367 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5368 || ' WHERE a.' || quote_ident(column_a)
5369 || ' = b.' || quote_ident(column_b)
5370 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5373 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5375 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5377 table_schema ALIAS FOR $1;
5378 table_a ALIAS FOR $2;
5379 column_a ALIAS FOR $3;
5380 table_b ALIAS FOR $4;
5381 column_b ALIAS FOR $5;
5382 column_w ALIAS FOR $6;
5383 column_x ALIAS FOR $7;
5386 EXECUTE 'SELECT EXISTS (
5388 FROM information_schema.columns
5389 WHERE table_schema = $1
5391 and column_name = $3
5392 )' INTO proceed USING table_schema, table_a, column_a;
5394 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5397 EXECUTE 'SELECT EXISTS (
5399 FROM information_schema.columns
5400 WHERE table_schema = $1
5402 and column_name = $3
5403 )' INTO proceed USING table_schema, table_b, column_b;
5405 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5408 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5409 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5410 || ' WHERE a.' || quote_ident(column_a)
5411 || ' = b.' || quote_ident(column_b)
5412 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5415 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5417 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5419 table_schema ALIAS FOR $1;
5420 table_a ALIAS FOR $2;
5421 column_a ALIAS FOR $3;
5422 table_b ALIAS FOR $4;
5423 column_b ALIAS FOR $5;
5424 column_w ALIAS FOR $6;
5425 column_x ALIAS FOR $7;
5428 EXECUTE 'SELECT EXISTS (
5430 FROM information_schema.columns
5431 WHERE table_schema = $1
5433 and column_name = $3
5434 )' INTO proceed USING table_schema, table_a, column_a;
5436 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5439 EXECUTE 'SELECT EXISTS (
5441 FROM information_schema.columns
5442 WHERE table_schema = $1
5444 and column_name = $3
5445 )' INTO proceed USING table_schema, table_b, column_b;
5447 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5450 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5451 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5452 || ' WHERE a.' || quote_ident(column_a)
5453 || ' = b.' || quote_ident(column_b)
5454 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5457 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5459 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5461 table_schema ALIAS FOR $1;
5462 table_a ALIAS FOR $2;
5463 column_a ALIAS FOR $3;
5464 table_b ALIAS FOR $4;
5465 column_b ALIAS FOR $5;
5466 column_w ALIAS FOR $6;
5467 column_x ALIAS FOR $7;
5470 EXECUTE 'SELECT EXISTS (
5472 FROM information_schema.columns
5473 WHERE table_schema = $1
5475 and column_name = $3
5476 )' INTO proceed USING table_schema, table_a, column_a;
5478 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5481 EXECUTE 'SELECT EXISTS (
5483 FROM information_schema.columns
5484 WHERE table_schema = $1
5486 and column_name = $3
5487 )' INTO proceed USING table_schema, table_b, column_b;
5489 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5492 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5493 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5494 || ' WHERE a.' || quote_ident(column_a)
5495 || ' = b.' || quote_ident(column_b)
5496 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5499 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5501 -- convenience function for handling desired asset stat cats
5503 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5505 table_schema ALIAS FOR $1;
5506 table_name ALIAS FOR $2;
5507 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5508 org_shortname ALIAS FOR $4;
5516 SELECT 'desired_sc' || field_suffix INTO sc;
5517 SELECT 'desired_sce' || field_suffix INTO sce;
5519 EXECUTE 'SELECT EXISTS (
5521 FROM information_schema.columns
5522 WHERE table_schema = $1
5524 and column_name = $3
5525 )' INTO proceed USING table_schema, table_name, sc;
5527 RAISE EXCEPTION 'Missing column %', sc;
5529 EXECUTE 'SELECT EXISTS (
5531 FROM information_schema.columns
5532 WHERE table_schema = $1
5534 and column_name = $3
5535 )' INTO proceed USING table_schema, table_name, sce;
5537 RAISE EXCEPTION 'Missing column %', sce;
5540 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5542 RAISE EXCEPTION 'Cannot find org by shortname';
5544 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5546 -- caller responsible for their own truncates though we try to prevent duplicates
5547 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5552 ' || quote_ident(table_name) || '
5554 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5558 WHERE owner = ANY ($2)
5559 AND name = BTRIM('||sc||')
5564 WHERE owner = ANY ($2)
5565 AND name = BTRIM('||sc||')
5568 USING org, org_list;
5570 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5575 WHERE owner = ANY ($2)
5576 AND BTRIM('||sc||') = BTRIM(name))
5579 WHERE owner = ANY ($2)
5580 AND BTRIM('||sc||') = BTRIM(name))
5585 ' || quote_ident(table_name) || '
5587 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5588 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5591 FROM asset.stat_cat_entry
5595 WHERE owner = ANY ($2)
5596 AND BTRIM('||sc||') = BTRIM(name)
5597 ) AND value = BTRIM('||sce||')
5598 AND owner = ANY ($2)
5602 FROM asset_stat_cat_entry
5606 WHERE owner = ANY ($2)
5607 AND BTRIM('||sc||') = BTRIM(name)
5608 ) AND value = BTRIM('||sce||')
5609 AND owner = ANY ($2)
5612 USING org, org_list;
5614 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5616 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5618 table_schema ALIAS FOR $1;
5619 table_name ALIAS FOR $2;
5620 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5621 org_shortname ALIAS FOR $4;
5629 SELECT 'desired_sc' || field_suffix INTO sc;
5630 SELECT 'desired_sce' || field_suffix INTO sce;
5631 EXECUTE 'SELECT EXISTS (
5633 FROM information_schema.columns
5634 WHERE table_schema = $1
5636 and column_name = $3
5637 )' INTO proceed USING table_schema, table_name, sc;
5639 RAISE EXCEPTION 'Missing column %', sc;
5641 EXECUTE 'SELECT EXISTS (
5643 FROM information_schema.columns
5644 WHERE table_schema = $1
5646 and column_name = $3
5647 )' INTO proceed USING table_schema, table_name, sce;
5649 RAISE EXCEPTION 'Missing column %', sce;
5652 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5654 RAISE EXCEPTION 'Cannot find org by shortname';
5657 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5659 EXECUTE 'ALTER TABLE '
5660 || quote_ident(table_name)
5661 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5662 EXECUTE 'ALTER TABLE '
5663 || quote_ident(table_name)
5664 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5665 EXECUTE 'ALTER TABLE '
5666 || quote_ident(table_name)
5667 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5668 EXECUTE 'ALTER TABLE '
5669 || quote_ident(table_name)
5670 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5673 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5675 x_sc' || field_suffix || ' = id
5677 (SELECT id, name, owner FROM asset_stat_cat
5678 UNION SELECT id, name, owner FROM asset.stat_cat) u
5680 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5681 AND u.owner = ANY ($1);'
5684 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5686 x_sce' || field_suffix || ' = id
5688 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5689 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5691 u.stat_cat = x_sc' || field_suffix || '
5692 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5693 AND u.owner = ANY ($1);'
5696 EXECUTE 'SELECT migration_tools.assert(
5697 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5698 ''Cannot find a desired stat cat'',
5699 ''Found all desired stat cats''
5702 EXECUTE 'SELECT migration_tools.assert(
5703 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5704 ''Cannot find a desired stat cat entry'',
5705 ''Found all desired stat cat entries''
5709 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5711 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5712 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5719 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5721 AND table_schema = s_name
5722 AND (data_type='text' OR data_type='character varying')
5723 AND column_name like 'l_%'
5725 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5732 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5733 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5740 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5742 AND table_schema = s_name
5743 AND (data_type='text' OR data_type='character varying')
5745 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5752 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5753 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5760 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5762 AND table_schema = s_name
5763 AND (data_type='text' OR data_type='character varying')
5764 AND column_name like 'l_%'
5766 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5773 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5774 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5781 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5783 AND table_schema = s_name
5784 AND (data_type='text' OR data_type='character varying')
5786 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5794 -- convenience function for handling item barcode collisions in asset_copy_legacy
5796 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5801 internal_collision_count NUMERIC := 0;
5802 incumbent_collision_count NUMERIC := 0;
5804 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5806 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5808 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5809 GET DIAGNOSTICS row_count = ROW_COUNT;
5810 internal_collision_count := internal_collision_count + row_count;
5813 RAISE INFO '% internal collisions', internal_collision_count;
5814 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
5816 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5818 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5819 GET DIAGNOSTICS row_count = ROW_COUNT;
5820 incumbent_collision_count := incumbent_collision_count + row_count;
5823 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5825 $function$ LANGUAGE plpgsql;
5827 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5828 -- this should be ran prior to populating actor_card
5830 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5835 internal_collision_count NUMERIC := 0;
5836 incumbent_barcode_collision_count NUMERIC := 0;
5837 incumbent_usrname_collision_count NUMERIC := 0;
5839 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5841 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5843 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5844 GET DIAGNOSTICS row_count = ROW_COUNT;
5845 internal_collision_count := internal_collision_count + row_count;
5848 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5851 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5853 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5855 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5856 GET DIAGNOSTICS row_count = ROW_COUNT;
5857 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5860 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5863 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5865 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5867 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5868 GET DIAGNOSTICS row_count = ROW_COUNT;
5869 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5872 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5874 $function$ LANGUAGE plpgsql;
5876 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5878 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5883 internal_collision_count NUMERIC := 0;
5884 incumbent_collision_count NUMERIC := 0;
5886 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5888 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5890 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5891 GET DIAGNOSTICS row_count = ROW_COUNT;
5892 internal_collision_count := internal_collision_count + row_count;
5895 RAISE INFO '% internal collisions', internal_collision_count;
5896 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
5898 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5900 UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5901 GET DIAGNOSTICS row_count = ROW_COUNT;
5902 incumbent_collision_count := incumbent_collision_count + row_count;
5905 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5907 $function$ LANGUAGE plpgsql;
5909 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5910 -- this should be ran prior to populating actor_card
5912 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5917 internal_collision_count NUMERIC := 0;
5918 incumbent_barcode_collision_count NUMERIC := 0;
5919 incumbent_usrname_collision_count NUMERIC := 0;
5921 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5923 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5925 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5926 GET DIAGNOSTICS row_count = ROW_COUNT;
5927 internal_collision_count := internal_collision_count + row_count;
5930 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5933 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5935 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5937 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5938 GET DIAGNOSTICS row_count = ROW_COUNT;
5939 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5942 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5945 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5947 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5949 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5950 GET DIAGNOSTICS row_count = ROW_COUNT;
5951 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5954 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5956 $function$ LANGUAGE plpgsql;
5958 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5959 -- WARNING: Use at your own risk
5960 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5962 item_object asset.copy%ROWTYPE;
5963 user_object actor.usr%ROWTYPE;
5964 test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5965 result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5966 safe_to_delete BOOLEAN := FALSE;
5967 m action.found_circ_matrix_matchpoint;
5968 n action.found_circ_matrix_matchpoint;
5969 -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5970 result_matchpoint INTEGER;
5972 SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5973 RAISE INFO 'testing rule: %', test_rule_object;
5975 INSERT INTO actor.usr (
5985 COALESCE(test_rule_object.grp, 2),
5986 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5991 COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5992 COALESCE(test_rule_object.juvenile_flag, FALSE)
5995 SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5997 INSERT INTO asset.call_number (
6008 COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
6009 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6013 INSERT INTO asset.copy (
6025 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6026 COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
6028 currval('asset.call_number_id_seq'),
6030 COALESCE(test_rule_object.copy_location,1),
6033 COALESCE(test_rule_object.ref_flag,FALSE),
6034 test_rule_object.circ_modifier
6037 SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
6039 SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
6040 test_rule_object.org_unit,
6043 COALESCE(test_rule_object.is_renewal,FALSE)
6045 RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6046 test_rule_object.org_unit,
6049 COALESCE(test_rule_object.is_renewal,FALSE),
6055 -- disable the rule being tested to see if the outcome changes
6056 UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
6058 SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
6059 test_rule_object.org_unit,
6062 COALESCE(test_rule_object.is_renewal,FALSE)
6064 RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6065 test_rule_object.org_unit,
6068 COALESCE(test_rule_object.is_renewal,FALSE),
6074 -- FIXME: We could dig deeper and see if the referenced config.rule_*
6075 -- entries are effectively equivalent, but for now, let's assume no
6076 -- duplicate rules at that level
6078 (m.matchpoint).circulate = (n.matchpoint).circulate
6079 AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
6080 AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
6081 AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
6083 (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
6085 (m.matchpoint).hard_due_date IS NULL
6086 AND (n.matchpoint).hard_due_date IS NULL
6090 (m.matchpoint).renewals = (n.matchpoint).renewals
6092 (m.matchpoint).renewals IS NULL
6093 AND (n.matchpoint).renewals IS NULL
6097 (m.matchpoint).grace_period = (n.matchpoint).grace_period
6099 (m.matchpoint).grace_period IS NULL
6100 AND (n.matchpoint).grace_period IS NULL
6104 (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
6106 (m.matchpoint).total_copy_hold_ratio IS NULL
6107 AND (n.matchpoint).total_copy_hold_ratio IS NULL
6111 (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
6113 (m.matchpoint).available_copy_hold_ratio IS NULL
6114 AND (n.matchpoint).available_copy_hold_ratio IS NULL
6118 SELECT limit_set, fallthrough
6119 FROM config.circ_matrix_limit_set_map
6120 WHERE active and matchpoint = (m.matchpoint).id
6122 SELECT limit_set, fallthrough
6123 FROM config.circ_matrix_limit_set_map
6124 WHERE active and matchpoint = (n.matchpoint).id
6128 RAISE INFO 'rule has same outcome';
6129 safe_to_delete := TRUE;
6131 RAISE INFO 'rule has different outcome';
6132 safe_to_delete := FALSE;
6135 RAISE EXCEPTION 'rollback the temporary changes';
6137 EXCEPTION WHEN OTHERS THEN
6139 RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
6140 RETURN safe_to_delete;
6143 $func$ LANGUAGE plpgsql;