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 -- creates other child table so you can have more than one child table in a schema from a base table
265 CREATE OR REPLACE FUNCTION build_variant_staging_table(text, text, text)
271 migration_schema ALIAS FOR $1;
272 production_table ALIAS FOR $2;
273 base_staging_table ALIAS FOR $3;
276 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
277 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
278 PERFORM migration_tools.exec( $1, '
279 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
280 SELECT table_schema, table_name, column_name, data_type
281 FROM information_schema.columns
282 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
285 SELECT table_schema, table_name, column_name, data_type
286 FROM information_schema.columns
287 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
289 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
294 CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
296 migration_schema ALIAS FOR $1;
297 parent_table ALIAS FOR $2;
298 source_table ALIAS FOR $3;
302 column_list TEXT := '';
303 column_count INTEGER := 0;
305 create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
307 SELECT table_schema, table_name, column_name, data_type
308 FROM information_schema.columns
309 WHERE table_schema = migration_schema AND table_name = source_table
311 column_count := column_count + 1;
312 if column_count > 1 then
313 create_sql := create_sql || ', ';
314 column_list := column_list || ', ';
316 create_sql := create_sql || columns.column_name || ' ';
317 if columns.data_type = 'ARRAY' then
318 create_sql := create_sql || 'TEXT[]';
320 create_sql := create_sql || columns.data_type;
322 column_list := column_list || columns.column_name;
324 create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
325 --RAISE INFO 'create_sql = %', create_sql;
327 insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
328 --RAISE INFO 'insert_sql = %', insert_sql;
331 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
333 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
335 migration_schema ALIAS FOR $1;
336 production_tables TEXT[];
338 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
339 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
340 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
341 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
344 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
346 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
348 migration_schema ALIAS FOR $1;
349 production_table ALIAS FOR $2;
350 base_staging_table TEXT;
353 base_staging_table = REPLACE( production_table, '.', '_' );
354 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
355 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
357 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
359 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
361 full_name TEXT := $1;
363 family_name TEXT := '';
364 first_given_name TEXT := '';
365 second_given_name TEXT := '';
369 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
370 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
372 IF suffix = before_comma THEN
376 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
377 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
378 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
380 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
382 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
384 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
386 full_name TEXT := $1;
388 family_name TEXT := '';
389 first_given_name TEXT := '';
390 second_given_name TEXT := '';
395 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
396 IF temp ilike '%MR.%' THEN
398 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
400 IF temp ilike '%MRS.%' THEN
402 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
404 IF temp ilike '%MS.%' THEN
406 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
408 IF temp ilike '%DR.%' THEN
410 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
412 IF temp ilike '%JR%' THEN
414 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
416 IF temp ilike '%JR,%' THEN
418 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
420 IF temp ilike '%SR%' THEN
422 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
424 IF temp ilike '%SR,%' THEN
426 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
428 IF temp ~ E'\\sII$' THEN
430 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
432 IF temp ~ E'\\sIII$' THEN
434 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
436 IF temp ~ E'\\sIV$' THEN
438 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
441 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
442 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
443 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
445 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
447 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
451 full_name TEXT := $1;
453 family_name TEXT := '';
454 first_given_name TEXT := '';
455 second_given_name TEXT := '';
460 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
461 IF temp ilike '%MR.%' THEN
463 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
465 IF temp ilike '%MRS.%' THEN
467 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
469 IF temp ilike '%MS.%' THEN
471 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
473 IF temp ilike '%DR.%' THEN
475 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
477 IF temp ilike '%JR.%' THEN
479 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
481 IF temp ilike '%JR,%' THEN
483 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
485 IF temp ilike '%SR.%' THEN
487 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
489 IF temp ilike '%SR,%' THEN
491 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
493 IF temp like '%III%' THEN
495 temp := REGEXP_REPLACE( temp, E'III', '' );
497 IF temp like '%II%' THEN
499 temp := REGEXP_REPLACE( temp, E'II', '' );
501 IF temp like '%IV%' THEN
503 temp := REGEXP_REPLACE( temp, E'IV', '' );
506 temp := REGEXP_REPLACE( temp, '\(\)', '');
507 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
508 family_name := REGEXP_REPLACE( family_name, ',', '' );
509 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
510 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
511 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
512 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
514 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
516 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
518 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
520 full_name TEXT := $1;
522 family_name TEXT := '';
523 first_given_name TEXT := '';
524 second_given_name TEXT := '';
528 temp := BTRIM(full_name);
529 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
530 --IF temp ~ '^\S{2,}\.' THEN
531 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
532 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
534 --IF temp ~ '\S{2,}\.$' THEN
535 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
536 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
538 IF temp ilike '%MR.%' THEN
540 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
542 IF temp ilike '%MRS.%' THEN
544 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
546 IF temp ilike '%MS.%' THEN
548 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
550 IF temp ilike '%DR.%' THEN
552 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
554 IF temp ilike '%JR.%' THEN
556 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
558 IF temp ilike '%JR,%' THEN
560 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
562 IF temp ilike '%SR.%' THEN
564 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
566 IF temp ilike '%SR,%' THEN
568 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
570 IF temp like '%III%' THEN
572 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
574 IF temp like '%II%' THEN
576 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
580 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
581 temp := BTRIM(REPLACE( temp, family_name, '' ));
582 family_name := REPLACE( family_name, ',', '' );
584 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
585 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
587 first_given_name := temp;
588 second_given_name := '';
591 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
592 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
593 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
594 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
596 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
597 second_given_name := temp;
598 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
602 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
604 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
606 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
608 full_name TEXT := $1;
610 family_name TEXT := '';
611 first_given_name TEXT := '';
612 second_given_name TEXT := '';
616 temp := BTRIM(full_name);
617 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
618 --IF temp ~ '^\S{2,}\.' THEN
619 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
620 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
622 --IF temp ~ '\S{2,}\.$' THEN
623 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
624 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
626 IF temp ilike '%MR.%' THEN
628 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
630 IF temp ilike '%MRS.%' THEN
632 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
634 IF temp ilike '%MS.%' THEN
636 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
638 IF temp ilike '%DR.%' THEN
640 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
642 IF temp ilike '%JR.%' THEN
644 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
646 IF temp ilike '%JR,%' THEN
648 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
650 IF temp ilike '%SR.%' THEN
652 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
654 IF temp ilike '%SR,%' THEN
656 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
658 IF temp like '%III%' THEN
660 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
662 IF temp like '%II%' THEN
664 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
668 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
669 temp := BTRIM(REPLACE( temp, family_name, '' ));
670 family_name := REPLACE( family_name, ',', '' );
672 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
673 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
675 first_given_name := temp;
676 second_given_name := '';
679 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
680 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
681 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
682 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
684 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
685 second_given_name := temp;
686 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
690 family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
691 first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
692 second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
694 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
696 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
698 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
700 city_state_zip TEXT := $1;
705 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;
706 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
707 IF city_state_zip ~ ',' THEN
708 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
709 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
711 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
712 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
713 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
715 IF city_state_zip ~ E'^\\S+$' THEN
716 city := city_state_zip;
719 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
720 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
724 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
726 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
728 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
729 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
731 fullstring TEXT := $1;
741 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
742 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
745 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
747 IF fullstring ~ ',' THEN
748 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
749 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
751 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
752 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
753 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
755 IF fullstring ~ E'^\\S+$' THEN
756 scratch1 := fullstring;
759 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
760 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
765 IF scratch1 ~ '[\$]' THEN
766 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
767 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
769 IF scratch1 ~ '\s' THEN
770 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
771 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
778 IF scratch2 ~ '^\d' THEN
779 address1 := scratch2;
782 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
783 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
787 TRIM(BOTH ' ' FROM address1)
788 ,TRIM(BOTH ' ' FROM address2)
789 ,TRIM(BOTH ' ' FROM city)
790 ,TRIM(BOTH ' ' FROM state)
791 ,TRIM(BOTH ' ' FROM zip)
794 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
796 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
799 use Geo::StreetAddress::US;
801 my $a = Geo::StreetAddress::US->parse_location($address);
804 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
805 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
810 $$ LANGUAGE PLPERLU STABLE;
812 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
813 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
814 INSERT INTO migration_tools.usps_suffixes VALUES
847 ('BOULEVARD','BLVD'),
951 ('EXPRESSWAY','EXPY'),
956 ('EXTENSIONS','EXTS'),
1054 ('ISLANDS','SLNDS'),
1067 ('JUNCTIONS','JCTS'),
1127 ('MOTORWAY','MTWY'),
1129 ('MOUNTAINS','MTNS'),
1143 ('OVERPASS','OPAS'),
1148 ('PARKWAYS','PKWY'),
1254 ('STRAVENUE','STRA'),
1274 ('THROUGHWAY','TRWY'),
1281 ('TRAFFICWAY','TRFY'),
1300 ('TURNPIKE','TPKE'),
1302 ('UNDERPASS','UPAS'),
1316 ('VILLAGES','VLGS'),
1347 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1348 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1353 --RAISE INFO 'suffix = %', suffix;
1354 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1355 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1359 $$ LANGUAGE PLPGSQL STRICT STABLE;
1361 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1364 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1368 $$ LANGUAGE PLPGSQL STRICT STABLE;
1370 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1374 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
1375 IF o::BIGINT < t THEN
1382 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1384 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1386 migration_schema ALIAS FOR $1;
1390 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1395 $$ LANGUAGE PLPGSQL STRICT STABLE;
1397 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1399 migration_schema ALIAS FOR $1;
1403 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1408 $$ LANGUAGE PLPGSQL STRICT STABLE;
1410 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1412 migration_schema ALIAS FOR $1;
1416 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1421 $$ LANGUAGE PLPGSQL STRICT STABLE;
1423 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1425 migration_schema ALIAS FOR $1;
1429 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1434 $$ LANGUAGE PLPGSQL STRICT STABLE;
1436 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1438 migration_schema ALIAS FOR $1;
1440 patron_table ALIAS FOR $2;
1441 default_patron_profile ALIAS FOR $3;
1444 sql_where1 TEXT := '';
1445 sql_where2 TEXT := '';
1446 sql_where3 TEXT := '';
1449 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1451 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1453 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1454 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);
1455 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);
1456 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);
1457 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,'') || ';';
1458 --RAISE INFO 'sql = %', sql;
1459 PERFORM migration_tools.exec( $1, sql );
1461 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1463 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1465 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1468 $$ LANGUAGE PLPGSQL STRICT STABLE;
1470 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1472 migration_schema ALIAS FOR $1;
1474 item_table ALIAS FOR $2;
1477 sql_where1 TEXT := '';
1478 sql_where2 TEXT := '';
1479 sql_where3 TEXT := '';
1482 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1484 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1486 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 ';
1487 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);
1488 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);
1489 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);
1490 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,'') || ';';
1491 --RAISE INFO 'sql = %', sql;
1492 PERFORM migration_tools.exec( $1, sql );
1495 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1497 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1500 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1502 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1504 migration_schema ALIAS FOR $1;
1505 base_copy_location_map TEXT;
1506 item_table ALIAS FOR $2;
1509 sql_where1 TEXT := '';
1510 sql_where2 TEXT := '';
1511 sql_where3 TEXT := '';
1514 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1516 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1518 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1519 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);
1520 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);
1521 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);
1522 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,'') || ';';
1523 --RAISE INFO 'sql = %', sql;
1524 PERFORM migration_tools.exec( $1, sql );
1527 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1529 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1532 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1534 -- 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
1535 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1537 migration_schema ALIAS FOR $1;
1539 circ_table ALIAS FOR $2;
1540 item_table ALIAS FOR $3;
1541 patron_table ALIAS FOR $4;
1544 sql_where1 TEXT := '';
1545 sql_where2 TEXT := '';
1546 sql_where3 TEXT := '';
1547 sql_where4 TEXT := '';
1550 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1552 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1554 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 ';
1555 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);
1556 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);
1557 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);
1558 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);
1559 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,'') || ';';
1560 --RAISE INFO 'sql = %', sql;
1561 PERFORM migration_tools.exec( $1, sql );
1564 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1566 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1569 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1572 -- $barcode source barcode
1573 -- $prefix prefix to add to barcode, NULL = add no prefix
1574 -- $maxlen maximum length of barcode; default to 14 if left NULL
1575 -- $pad padding string to apply to left of source barcode before adding
1576 -- prefix and suffix; set to NULL or '' if no padding is desired
1577 -- $suffix suffix to add to barcode, NULL = add no suffix
1579 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1580 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1582 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1583 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1586 return unless defined $barcode;
1588 $prefix = '' unless defined $prefix;
1590 $pad = '0' unless defined $pad;
1591 $suffix = '' unless defined $suffix;
1593 # bail out if adding prefix and suffix would bring new barcode over max length
1594 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1596 my $new_barcode = $barcode;
1598 my $pad_length = $maxlen - length($prefix) - length($suffix);
1599 if (length($barcode) < $pad_length) {
1600 # assuming we always want padding on the left
1601 # also assuming that it is possible to have the pad string be longer than 1 character
1602 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1606 # bail out if adding prefix and suffix would bring new barcode over max length
1607 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1609 return "$prefix$new_barcode$suffix";
1610 $$ LANGUAGE PLPERLU STABLE;
1612 -- remove previous version of this function
1613 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1615 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1617 attempt_value ALIAS FOR $1;
1618 datatype ALIAS FOR $2;
1620 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1621 RETURN attempt_value;
1623 WHEN OTHERS THEN RETURN NULL;
1625 $$ LANGUAGE PLPGSQL STRICT STABLE;
1627 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1629 attempt_value ALIAS FOR $1;
1630 fail_value ALIAS FOR $2;
1634 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1641 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1646 $$ LANGUAGE PLPGSQL STRICT STABLE;
1648 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1650 attempt_value ALIAS FOR $1;
1651 fail_value ALIAS FOR $2;
1655 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1662 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1667 $$ LANGUAGE PLPGSQL STRICT STABLE;
1669 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1671 attempt_value ALIAS FOR $1;
1672 fail_value ALIAS FOR $2;
1676 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1683 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1688 $$ LANGUAGE PLPGSQL STRICT STABLE;
1690 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1692 attempt_value ALIAS FOR $1;
1693 fail_value ALIAS FOR $2;
1696 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1701 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1706 $$ LANGUAGE PLPGSQL STRICT STABLE;
1708 CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
1710 -- Expects the following table/columns:
1712 -- export_biblio_tsv:
1714 -- l_create_dt | 2007-03-07 09:03:09
1715 -- l_last_change_dt | 2015-01-23 11:18:54
1716 -- l_last_change_userid | 2
1717 -- l_material_cd | 10
1718 -- l_collection_cd | 13
1719 -- l_call_nmbr1 | Canada
1720 -- l_call_nmbr2 | ON
1721 -- l_call_nmbr3 | Ottawa 18
1722 -- l_title | Art and the courts : France ad England
1723 -- l_title_remainder | from 1259-1328
1724 -- l_responsibility_stmt |
1725 -- l_author | National Gallery of Canada
1732 -- l_flag_attention | 0
1734 -- export_biblio_field_tsv:
1740 -- l_subfield_cd | a
1741 -- l_field_data | Brieger, Peter Henry
1743 -- Map export_biblio_tsv as follows:
1744 -- l_call_nmbr? -> 099a
1747 -- l_title_remainder -> 245b
1748 -- l_responsibility_stmt -> 245c
1753 migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
1756 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
1757 array_agg(l_ind1_cd) as "ind1",
1758 array_agg(l_ind2_cd) as "ind2",
1759 array_agg(l_field_data) as "data"
1767 from export_biblio_field_tsv
1768 where l_bibid = x_bibid
1772 'a' as "l_subfield_cd",
1776 nullif(btrim(l_call_nmbr1),''),
1777 nullif(btrim(l_call_nmbr2),''),
1778 nullif(btrim(l_call_nmbr3),'')
1780 from export_biblio_tsv
1781 where l_bibid = x_bibid
1785 'a' as "l_subfield_cd",
1788 l_author as "l_field_data"
1789 from export_biblio_tsv
1790 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
1794 'a' as "l_subfield_cd",
1797 l_title as "l_field_data"
1798 from export_biblio_tsv
1799 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
1803 'b' as "l_subfield_cd",
1806 l_title_remainder as "l_field_data"
1807 from export_biblio_tsv
1808 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
1812 'a' as "l_subfield_cd",
1815 l_topic1 as "l_field_data"
1816 from export_biblio_tsv
1817 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
1821 'a' as "l_subfield_cd",
1824 l_topic2 as "l_field_data"
1825 from export_biblio_tsv
1826 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
1830 'a' as "l_subfield_cd",
1833 l_topic3 as "l_field_data"
1834 from export_biblio_tsv
1835 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
1839 'a' as "l_subfield_cd",
1842 l_topic4 as "l_field_data"
1843 from export_biblio_tsv
1844 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
1848 'a' as "l_subfield_cd",
1851 l_topic5 as "l_field_data"
1852 from export_biblio_tsv
1853 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
1857 '' as "l_subfield_cd",
1860 l_bibid as "l_field_data"
1861 from export_biblio_tsv
1862 where l_bibid = x_bibid
1867 $func$ LANGUAGE plpgsql;
1869 -- add koha holding tag to marc
1870 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);
1872 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)
1880 use MARC::File::XML (BinaryEncoding => 'utf8');
1882 binmode(STDERR, ':bytes');
1883 binmode(STDOUT, ':utf8');
1884 binmode(STDERR, ':utf8');
1886 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 ) = @_;
1888 $marc_xml =~ s/(<leader>.........)./${1}a/;
1891 $marc_xml = MARC::Record->new_from_xml($marc_xml);
1894 #elog("could not parse $bibid: $@\n");
1895 import MARC::File::XML (BinaryEncoding => 'utf8');
1899 my $new_field = new MARC::Field(
1902 'b' => $holdingbranch,
1908 if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
1909 if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
1910 if ($price) { $new_field->add_subfields('g' => $price); }
1911 if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
1912 if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
1913 if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
1914 if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
1915 if ($stack) { $new_field->add_subfields('j' => $stack); }
1916 if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
1917 if ($damaged) { $new_field->add_subfields('4' => $damaged); }
1918 if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
1919 if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
1920 if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
1921 if ($issues) { $new_field->add_subfields('l' => $issues); }
1922 if ($renewals) { $new_field->add_subfields('m' => $renewals); }
1923 if ($reserves) { $new_field->add_subfields('n' => $reserves); }
1924 if ($restricted) { $new_field->add_subfields('5' => $restricted); }
1925 if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
1926 if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
1927 if ($onloan) { $new_field->add_subfields('q' => $onloan); }
1928 if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
1929 if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
1930 if ($ccode) { $new_field->add_subfields('8' => $ccode); }
1931 if ($materials) { $new_field->add_subfields('3' => $materials); }
1932 if ($uri) { $new_field->add_subfields('u' => $uri); }
1933 if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
1934 if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
1935 if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
1937 $marc_xml->insert_grouped_field( $new_field );
1939 return $marc_xml->as_xml_record();
1943 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1945 attempt_value ALIAS FOR $1;
1946 fail_value ALIAS FOR $2;
1947 output NUMERIC(8,2);
1950 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1957 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1962 $$ LANGUAGE PLPGSQL STRICT STABLE;
1964 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1966 attempt_value ALIAS FOR $1;
1967 fail_value ALIAS FOR $2;
1968 output NUMERIC(6,2);
1971 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1978 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1983 $$ LANGUAGE PLPGSQL STRICT STABLE;
1985 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1987 attempt_value ALIAS FOR $1;
1988 fail_value ALIAS FOR $2;
1989 output NUMERIC(8,2);
1991 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1992 RAISE EXCEPTION 'too many digits';
1995 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;'
2002 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
2007 $$ LANGUAGE PLPGSQL STRICT STABLE;
2009 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
2011 attempt_value ALIAS FOR $1;
2012 fail_value ALIAS FOR $2;
2013 output NUMERIC(6,2);
2015 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
2016 RAISE EXCEPTION 'too many digits';
2019 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;'
2026 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
2031 $$ LANGUAGE PLPGSQL STRICT STABLE;
2033 -- add_codabar_checkdigit
2034 -- $barcode source barcode
2036 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
2037 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2038 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
2039 -- input string does not meet those requirements, it is returned unchanged.
2041 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
2042 my $barcode = shift;
2044 return $barcode if $barcode !~ /^\d{13,14}$/;
2045 $barcode = substr($barcode, 0, 13); # ignore 14th digit
2046 my @digits = split //, $barcode;
2048 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
2049 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
2050 my $remainder = $total % 10;
2051 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
2052 return $barcode . $checkdigit;
2053 $$ LANGUAGE PLPERLU STRICT STABLE;
2055 -- add_code39mod43_checkdigit
2056 -- $barcode source barcode
2058 -- If the source string is 13 or 14 characters long and contains only valid
2059 -- Code 39 mod 43 characters, adds or replaces the 14th
2060 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2061 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
2062 -- input string does not meet those requirements, it is returned unchanged.
2064 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
2065 my $barcode = shift;
2067 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
2068 $barcode = substr($barcode, 0, 13); # ignore 14th character
2070 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
2071 my %nums = map { $valid_chars[$_] => $_ } (0..42);
2074 $total += $nums{$_} foreach split(//, $barcode);
2075 my $remainder = $total % 43;
2076 my $checkdigit = $valid_chars[$remainder];
2077 return $barcode . $checkdigit;
2078 $$ LANGUAGE PLPERLU STRICT STABLE;
2080 -- add_mod16_checkdigit
2081 -- $barcode source barcode
2083 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
2085 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
2086 my $barcode = shift;
2088 my @digits = split //, $barcode;
2090 foreach $digit (@digits) {
2091 if ($digit =~ /[0-9]/) { $total += $digit;
2092 } elsif ($digit eq '-') { $total += 10;
2093 } elsif ($digit eq '$') { $total += 11;
2094 } elsif ($digit eq ':') { $total += 12;
2095 } elsif ($digit eq '/') { $total += 13;
2096 } elsif ($digit eq '.') { $total += 14;
2097 } elsif ($digit eq '+') { $total += 15;
2098 } elsif ($digit eq 'A') { $total += 16;
2099 } elsif ($digit eq 'B') { $total += 17;
2100 } elsif ($digit eq 'C') { $total += 18;
2101 } elsif ($digit eq 'D') { $total += 19;
2102 } else { die "invalid digit <$digit>";
2105 my $remainder = $total % 16;
2106 my $difference = 16 - $remainder;
2108 if ($difference < 10) { $checkdigit = $difference;
2109 } elsif ($difference == 10) { $checkdigit = '-';
2110 } elsif ($difference == 11) { $checkdigit = '$';
2111 } elsif ($difference == 12) { $checkdigit = ':';
2112 } elsif ($difference == 13) { $checkdigit = '/';
2113 } elsif ($difference == 14) { $checkdigit = '.';
2114 } elsif ($difference == 15) { $checkdigit = '+';
2115 } else { die "error calculating checkdigit";
2118 return $barcode . $checkdigit;
2119 $$ LANGUAGE PLPERLU STRICT STABLE;
2121 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
2124 areacode TEXT := $2;
2127 n_digits INTEGER := 0;
2130 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
2131 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
2132 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
2133 IF n_digits = 7 AND areacode <> '' THEN
2134 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
2135 output := (areacode || '-' || temp);
2142 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2144 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2145 my ($marcxml, $pos, $value) = @_;
2148 use MARC::File::XML;
2152 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2153 my $leader = $marc->leader();
2154 substr($leader, $pos, 1) = $value;
2155 $marc->leader($leader);
2156 $xml = $marc->as_xml_record;
2157 $xml =~ s/^<\?.+?\?>$//mo;
2159 $xml =~ s/>\s+</></sgo;
2162 $$ LANGUAGE PLPERLU STABLE;
2164 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
2165 my ($marcxml, $pos, $value) = @_;
2168 use MARC::File::XML;
2172 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2173 my $f008 = $marc->field('008');
2176 my $field = $f008->data();
2177 substr($field, $pos, 1) = $value;
2178 $f008->update($field);
2179 $xml = $marc->as_xml_record;
2180 $xml =~ s/^<\?.+?\?>$//mo;
2182 $xml =~ s/>\s+</></sgo;
2186 $$ LANGUAGE PLPERLU STABLE;
2189 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
2191 profile ALIAS FOR $1;
2193 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
2195 $$ LANGUAGE PLPGSQL STRICT STABLE;
2198 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
2200 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
2202 $$ LANGUAGE PLPGSQL STRICT STABLE;
2205 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
2207 my ($marcxml, $tags) = @_;
2210 use MARC::File::XML;
2215 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2216 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
2218 my @incumbents = ();
2220 foreach my $field ( $marc->fields() ) {
2221 push @incumbents, $field->as_formatted();
2224 foreach $field ( $to_insert->fields() ) {
2225 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
2226 $marc->insert_fields_ordered( ($field) );
2230 $xml = $marc->as_xml_record;
2231 $xml =~ s/^<\?.+?\?>$//mo;
2233 $xml =~ s/>\s+</></sgo;
2238 $$ LANGUAGE PLPERLU STABLE;
2240 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
2244 -- First make sure the circ matrix is loaded and the circulations
2245 -- have been staged to the extent possible (but at the very least
2246 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2247 -- circ modifiers must also be in place.
2249 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2257 this_duration_rule INT;
2259 this_max_fine_rule INT;
2260 rcd config.rule_circ_duration%ROWTYPE;
2261 rrf config.rule_recurring_fine%ROWTYPE;
2262 rmf config.rule_max_fine%ROWTYPE;
2269 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2271 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2273 -- Fetch the correct rules for this circulation
2280 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2283 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2284 INTO circ_lib, target_copy, usr, is_renewal ;
2286 INTO this_duration_rule,
2290 recurring_fine_rule,
2292 FROM action.item_user_circ_test(
2298 SELECT INTO rcd * FROM config.rule_circ_duration
2299 WHERE id = this_duration_rule;
2300 SELECT INTO rrf * FROM config.rule_recurring_fine
2301 WHERE id = this_fine_rule;
2302 SELECT INTO rmf * FROM config.rule_max_fine
2303 WHERE id = this_max_fine_rule;
2305 -- Apply the rules to this circulation
2306 EXECUTE ('UPDATE ' || tablename || ' c
2308 duration_rule = rcd.name,
2309 recurring_fine_rule = rrf.name,
2310 max_fine_rule = rmf.name,
2311 duration = rcd.normal,
2312 recurring_fine = rrf.normal,
2315 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2318 renewal_remaining = rcd.max_renewals
2320 config.rule_circ_duration rcd,
2321 config.rule_recurring_fine rrf,
2322 config.rule_max_fine rmf,
2325 rcd.id = ' || this_duration_rule || ' AND
2326 rrf.id = ' || this_fine_rule || ' AND
2327 rmf.id = ' || this_max_fine_rule || ' AND
2328 ac.id = c.target_copy AND
2329 c.id = ' || circ || ';');
2331 -- Keep track of where we are in the process
2333 IF (n % 100 = 0) THEN
2334 RAISE INFO '%', n || ' of ' || n_circs
2335 || ' (' || (100*n/n_circs) || '%) circs updated.';
2343 $$ LANGUAGE plpgsql;
2345 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2349 -- First make sure the circ matrix is loaded and the circulations
2350 -- have been staged to the extent possible (but at the very least
2351 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2352 -- circ modifiers must also be in place.
2354 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2362 this_duration_rule INT;
2364 this_max_fine_rule INT;
2365 rcd config.rule_circ_duration%ROWTYPE;
2366 rrf config.rule_recurring_fine%ROWTYPE;
2367 rmf config.rule_max_fine%ROWTYPE;
2374 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2376 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2378 -- Fetch the correct rules for this circulation
2385 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2388 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2389 INTO circ_lib, target_copy, usr, is_renewal ;
2391 INTO this_duration_rule,
2397 FROM action.find_circ_matrix_matchpoint(
2403 SELECT INTO rcd * FROM config.rule_circ_duration
2404 WHERE id = this_duration_rule;
2405 SELECT INTO rrf * FROM config.rule_recurring_fine
2406 WHERE id = this_fine_rule;
2407 SELECT INTO rmf * FROM config.rule_max_fine
2408 WHERE id = this_max_fine_rule;
2410 -- Apply the rules to this circulation
2411 EXECUTE ('UPDATE ' || tablename || ' c
2413 duration_rule = rcd.name,
2414 recuring_fine_rule = rrf.name,
2415 max_fine_rule = rmf.name,
2416 duration = rcd.normal,
2417 recuring_fine = rrf.normal,
2420 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2423 renewal_remaining = rcd.max_renewals
2425 config.rule_circ_duration rcd,
2426 config.rule_recuring_fine rrf,
2427 config.rule_max_fine rmf,
2430 rcd.id = ' || this_duration_rule || ' AND
2431 rrf.id = ' || this_fine_rule || ' AND
2432 rmf.id = ' || this_max_fine_rule || ' AND
2433 ac.id = c.target_copy AND
2434 c.id = ' || circ || ';');
2436 -- Keep track of where we are in the process
2438 IF (n % 100 = 0) THEN
2439 RAISE INFO '%', n || ' of ' || n_circs
2440 || ' (' || (100*n/n_circs) || '%) circs updated.';
2448 $$ LANGUAGE plpgsql;
2450 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2454 -- First make sure the circ matrix is loaded and the circulations
2455 -- have been staged to the extent possible (but at the very least
2456 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2457 -- circ modifiers must also be in place.
2459 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2467 this_duration_rule INT;
2469 this_max_fine_rule INT;
2470 rcd config.rule_circ_duration%ROWTYPE;
2471 rrf config.rule_recurring_fine%ROWTYPE;
2472 rmf config.rule_max_fine%ROWTYPE;
2479 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2481 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2483 -- Fetch the correct rules for this circulation
2490 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2493 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2494 INTO circ_lib, target_copy, usr, is_renewal ;
2496 INTO this_duration_rule,
2499 (matchpoint).duration_rule,
2500 (matchpoint).recurring_fine_rule,
2501 (matchpoint).max_fine_rule
2502 FROM action.find_circ_matrix_matchpoint(
2508 SELECT INTO rcd * FROM config.rule_circ_duration
2509 WHERE id = this_duration_rule;
2510 SELECT INTO rrf * FROM config.rule_recurring_fine
2511 WHERE id = this_fine_rule;
2512 SELECT INTO rmf * FROM config.rule_max_fine
2513 WHERE id = this_max_fine_rule;
2515 -- Apply the rules to this circulation
2516 EXECUTE ('UPDATE ' || tablename || ' c
2518 duration_rule = rcd.name,
2519 recurring_fine_rule = rrf.name,
2520 max_fine_rule = rmf.name,
2521 duration = rcd.normal,
2522 recurring_fine = rrf.normal,
2525 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2528 renewal_remaining = rcd.max_renewals,
2529 grace_period = rrf.grace_period
2531 config.rule_circ_duration rcd,
2532 config.rule_recurring_fine rrf,
2533 config.rule_max_fine rmf,
2536 rcd.id = ' || this_duration_rule || ' AND
2537 rrf.id = ' || this_fine_rule || ' AND
2538 rmf.id = ' || this_max_fine_rule || ' AND
2539 ac.id = c.target_copy AND
2540 c.id = ' || circ || ';');
2542 -- Keep track of where we are in the process
2544 IF (n % 100 = 0) THEN
2545 RAISE INFO '%', n || ' of ' || n_circs
2546 || ' (' || (100*n/n_circs) || '%) circs updated.';
2554 $$ LANGUAGE plpgsql;
2556 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2557 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2558 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2561 charge_lost_on_zero BOOLEAN;
2564 default_price NUMERIC;
2565 working_price NUMERIC;
2569 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2570 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2572 SELECT INTO charge_lost_on_zero value
2573 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2575 SELECT INTO min_price value
2576 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2578 SELECT INTO max_price value
2579 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2581 SELECT INTO default_price value
2582 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2584 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2586 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2587 working_price := default_price;
2590 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2591 working_price := max_price;
2594 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2595 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2596 working_price := min_price;
2600 RETURN working_price;
2604 $$ LANGUAGE plpgsql;
2606 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2610 -- First make sure the circ matrix is loaded and the circulations
2611 -- have been staged to the extent possible (but at the very least
2612 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2613 -- circ modifiers must also be in place.
2615 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2623 this_duration_rule INT;
2625 this_max_fine_rule INT;
2626 rcd config.rule_circ_duration%ROWTYPE;
2627 rrf config.rule_recurring_fine%ROWTYPE;
2628 rmf config.rule_max_fine%ROWTYPE;
2634 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2636 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2638 -- Fetch the correct rules for this circulation
2645 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2648 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2649 INTO circ_lib, target_copy, usr, is_renewal ;
2651 INTO this_duration_rule,
2654 (matchpoint).duration_rule,
2655 (matchpoint).recurring_fine_rule,
2656 (matchpoint).max_fine_rule
2657 FROM action.find_circ_matrix_matchpoint(
2663 SELECT INTO rcd * FROM config.rule_circ_duration
2664 WHERE id = this_duration_rule;
2665 SELECT INTO rrf * FROM config.rule_recurring_fine
2666 WHERE id = this_fine_rule;
2667 SELECT INTO rmf * FROM config.rule_max_fine
2668 WHERE id = this_max_fine_rule;
2670 -- Apply the rules to this circulation
2671 EXECUTE ('UPDATE ' || tablename || ' c
2673 duration_rule = rcd.name,
2674 recurring_fine_rule = rrf.name,
2675 max_fine_rule = rmf.name,
2676 duration = rcd.normal,
2677 recurring_fine = rrf.normal,
2680 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2683 renewal_remaining = rcd.max_renewals,
2684 grace_period = rrf.grace_period
2686 config.rule_circ_duration rcd,
2687 config.rule_recurring_fine rrf,
2688 config.rule_max_fine rmf,
2691 rcd.id = ' || this_duration_rule || ' AND
2692 rrf.id = ' || this_fine_rule || ' AND
2693 rmf.id = ' || this_max_fine_rule || ' AND
2694 ac.id = c.target_copy AND
2695 c.id = ' || circ || ';');
2697 -- Keep track of where we are in the process
2699 IF (n % 100 = 0) THEN
2700 RAISE INFO '%', n || ' of ' || n_circs
2701 || ' (' || (100*n/n_circs) || '%) circs updated.';
2709 $$ LANGUAGE plpgsql;
2714 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2716 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2717 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2719 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2720 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2723 c TEXT := schemaname || '.asset_copy_legacy';
2724 sc TEXT := schemaname || '.asset_stat_cat';
2725 sce TEXT := schemaname || '.asset_stat_cat_entry';
2726 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2732 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2734 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2736 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2737 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2738 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2745 $$ LANGUAGE plpgsql;
2747 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2749 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2750 -- This will assign standing penalties as needed.
2758 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2760 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2762 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2772 $$ LANGUAGE plpgsql;
2775 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2778 INSERT INTO metabib.metarecord (fingerprint, master_record)
2779 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2780 FROM biblio.record_entry b
2782 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)
2783 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2784 ORDER BY b.fingerprint, b.quality DESC;
2785 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2787 FROM biblio.record_entry r
2788 JOIN metabib.metarecord m USING (fingerprint)
2789 WHERE NOT r.deleted;
2792 $$ LANGUAGE plpgsql;
2795 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2798 INSERT INTO metabib.metarecord (fingerprint, master_record)
2799 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2800 FROM biblio.record_entry b
2802 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)
2803 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2804 ORDER BY b.fingerprint, b.quality DESC;
2805 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2807 FROM biblio.record_entry r
2808 JOIN metabib.metarecord m USING (fingerprint)
2810 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);
2813 $$ LANGUAGE plpgsql;
2816 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2818 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2819 -- Then SELECT migration_tools.create_cards('m_foo');
2822 u TEXT := schemaname || '.actor_usr_legacy';
2823 c TEXT := schemaname || '.actor_card';
2827 EXECUTE ('DELETE FROM ' || c || ';');
2828 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2829 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2835 $$ LANGUAGE plpgsql;
2838 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2840 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2842 my ($marcxml, $shortname) = @_;
2845 use MARC::File::XML;
2850 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2852 foreach my $field ( $marc->field('856') ) {
2853 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2854 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2855 $field->add_subfields( '9' => $shortname );
2856 $field->update( ind2 => '0');
2860 $xml = $marc->as_xml_record;
2861 $xml =~ s/^<\?.+?\?>$//mo;
2863 $xml =~ s/>\s+</></sgo;
2868 $$ LANGUAGE PLPERLU STABLE;
2870 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2872 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2874 my ($marcxml, $shortname) = @_;
2877 use MARC::File::XML;
2882 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2884 foreach my $field ( $marc->field('856') ) {
2885 if ( ! $field->as_string('9') ) {
2886 $field->add_subfields( '9' => $shortname );
2890 $xml = $marc->as_xml_record;
2891 $xml =~ s/^<\?.+?\?>$//mo;
2893 $xml =~ s/>\s+</></sgo;
2898 $$ LANGUAGE PLPERLU STABLE;
2901 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2913 -- Bail out if asked to change the label to ##URI##
2914 IF new_label = '##URI##' THEN
2918 -- Gather information
2919 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2920 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2921 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2923 -- Bail out if the label already is ##URI##
2924 IF old_label = '##URI##' THEN
2928 -- Bail out if the call number label is already correct
2929 IF new_volume = old_volume THEN
2933 -- Check whether we already have a destination volume available
2934 SELECT id INTO new_volume FROM asset.call_number
2937 owning_lib = owner AND
2938 label = new_label AND
2941 -- Create destination volume if needed
2943 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2944 VALUES (1, 1, bib, owner, new_label, cn_class);
2945 SELECT id INTO new_volume FROM asset.call_number
2948 owning_lib = owner AND
2949 label = new_label AND
2953 -- Move copy to destination
2954 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2956 -- Delete source volume if it is now empty
2957 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2959 DELETE FROM asset.call_number WHERE id = old_volume;
2964 $$ LANGUAGE plpgsql;
2966 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2971 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2975 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2976 $zipdata{$zip} = [$city, $state, $county];
2979 if (defined $zipdata{$input}) {
2980 my ($city, $state, $county) = @{$zipdata{$input}};
2981 return [$city, $state, $county];
2982 } elsif (defined $zipdata{substr $input, 0, 5}) {
2983 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2984 return [$city, $state, $county];
2986 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2989 $$ LANGUAGE PLPERLU STABLE;
2991 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2998 errors_found BOOLEAN;
3000 parent_shortname TEXT;
3006 type_parent_depth INT;
3011 errors_found := FALSE;
3013 -- Checking actor.org_unit_type
3015 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
3017 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
3018 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
3020 IF type_parent IS NOT NULL THEN
3022 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
3024 IF type_depth - type_parent_depth <> 1 THEN
3025 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
3026 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
3027 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
3028 ou_type_name, type_depth, parent_type, type_parent_depth;
3029 errors_found := TRUE;
3037 -- Checking actor.org_unit
3039 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
3041 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
3042 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;
3043 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;
3044 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
3045 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
3046 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;
3047 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;
3049 IF ou_parent IS NOT NULL THEN
3051 IF (org_unit_depth - parent_depth <> 1) OR (
3052 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
3054 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
3055 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
3056 errors_found := TRUE;
3063 IF NOT errors_found THEN
3064 RAISE INFO 'No errors found.';
3071 $$ LANGUAGE plpgsql;
3074 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
3078 DELETE FROM asset.opac_visible_copies;
3080 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
3082 cp.id, cp.circ_lib, cn.record
3085 JOIN asset.call_number cn ON (cn.id = cp.call_number)
3086 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3087 JOIN asset.copy_location cl ON (cp.location = cl.id)
3088 JOIN config.copy_status cs ON (cp.status = cs.id)
3089 JOIN biblio.record_entry b ON (cn.record = b.id)
3098 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
3102 $$ LANGUAGE plpgsql;
3105 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
3111 old_owning_lib INTEGER;
3117 -- Gather information
3118 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
3119 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
3120 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
3122 -- Bail out if the new_owning_lib is not the ID of an org_unit
3123 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
3125 '% is not a valid actor.org_unit ID; no change made.',
3130 -- Bail out discreetly if the owning_lib is already correct
3131 IF new_owning_lib = old_owning_lib THEN
3135 -- Check whether we already have a destination volume available
3136 SELECT id INTO new_volume FROM asset.call_number
3139 owning_lib = new_owning_lib AND
3140 label = old_label AND
3143 -- Create destination volume if needed
3145 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
3146 VALUES (1, 1, bib, new_owning_lib, old_label);
3147 SELECT id INTO new_volume FROM asset.call_number
3150 owning_lib = new_owning_lib AND
3151 label = old_label AND
3155 -- Move copy to destination
3156 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
3158 -- Delete source volume if it is now empty
3159 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
3161 DELETE FROM asset.call_number WHERE id = old_volume;
3166 $$ LANGUAGE plpgsql;
3169 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
3171 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
3174 new_owning_lib INTEGER;
3178 -- Parse the new_owner as an org unit ID or shortname
3179 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
3180 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
3181 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3182 ELSIF new_owner ~ E'^[0-9]+$' THEN
3183 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
3186 E'You don\'t need to put the actor.org_unit ID in quotes; '
3187 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
3188 new_owning_lib := new_owner::INTEGER;
3189 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3193 '% is not a valid actor.org_unit shortname or ID; no change made.',
3200 $$ LANGUAGE plpgsql;
3202 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
3205 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3208 MARC::Charset->assume_unicode(1);
3213 my $r = MARC::Record->new_from_xml( $xml );
3214 my $output_xml = $r->as_xml_record();
3222 $func$ LANGUAGE PLPERLU;
3223 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
3225 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3227 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3228 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3229 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
3230 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3231 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3232 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
3233 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3234 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3235 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
3236 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3237 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3238 $$) TO '$$ || dir || $$/asset_copy_location'$$;
3239 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3240 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3241 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3242 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3243 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3244 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
3245 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3246 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3247 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
3248 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
3249 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
3250 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
3251 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
3252 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
3254 $FUNC$ LANGUAGE PLPGSQL;
3256 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3258 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
3259 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
3260 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
3261 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
3262 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3263 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
3264 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
3266 -- import any new circ rules
3267 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3268 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3269 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3270 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3272 -- and permission groups
3273 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3276 $FUNC$ LANGUAGE PLPGSQL;
3279 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$
3288 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3289 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3290 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
3291 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3292 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3293 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3294 FOR name IN EXECUTE loopq LOOP
3295 EXECUTE existsq INTO ct USING name;
3297 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3298 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3299 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3300 EXECUTE copyst USING name;
3304 $FUNC$ LANGUAGE PLPGSQL;
3306 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3313 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3314 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;
3315 FOR id IN EXECUTE loopq USING delimiter LOOP
3316 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3317 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3318 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3319 EXECUTE splitst USING id, delimiter;
3322 $FUNC$ LANGUAGE PLPGSQL;
3324 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3330 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3333 MARC::Charset->assume_unicode(1);
3335 my $target_xml = shift;
3336 my $source_xml = shift;
3342 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3346 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3351 my $source_id = $source->subfield('901', 'c');
3352 $source_id = $source->subfield('903', 'a') unless $source_id;
3353 my $target_id = $target->subfield('901', 'c');
3354 $target_id = $target->subfield('903', 'a') unless $target_id;
3356 my %existing_fields;
3357 foreach my $tag (@$tags) {
3358 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3359 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3360 $target->insert_fields_ordered(map { $_->clone() } @to_add);
3362 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3366 my $xml = $target->as_xml_record;
3367 $xml =~ s/^<\?.+?\?>$//mo;
3369 $xml =~ s/>\s+</></sgo;
3373 $func$ LANGUAGE PLPERLU;
3374 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.';
3376 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3382 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3385 my $in_tags = shift;
3386 my $in_values = shift;
3388 # hack-and-slash parsing of array-passed-as-string;
3389 # this can go away once everybody is running Postgres 9.1+
3390 my $csv = Text::CSV->new({binary => 1});
3393 my $status = $csv->parse($in_tags);
3394 my $tags = [ $csv->fields() ];
3395 $in_values =~ s/^{//;
3396 $in_values =~ s/}$//;
3397 $status = $csv->parse($in_values);
3398 my $values = [ $csv->fields() ];
3400 my $marc = MARC::Record->new();
3402 $marc->leader('00000nam a22000007 4500');
3403 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3405 foreach my $i (0..$#$tags) {
3407 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3410 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3411 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3413 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3417 my $xml = $marc->as_xml_record;
3418 $xml =~ s/^<\?.+?\?>$//mo;
3420 $xml =~ s/>\s+</></sgo;
3424 $func$ LANGUAGE PLPERLU;
3425 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3426 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3427 The second argument is an array of text containing the values to plug into each field.
3428 If the value for a given field is NULL or the empty string, it is not inserted.
3431 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3437 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3440 my $in_tags = shift;
3441 my $in_ind1 = shift;
3442 my $in_ind2 = shift;
3443 my $in_values = shift;
3445 # hack-and-slash parsing of array-passed-as-string;
3446 # this can go away once everybody is running Postgres 9.1+
3447 my $csv = Text::CSV->new({binary => 1});
3450 my $status = $csv->parse($in_tags);
3451 my $tags = [ $csv->fields() ];
3454 $status = $csv->parse($in_ind1);
3455 my $ind1s = [ $csv->fields() ];
3458 $status = $csv->parse($in_ind2);
3459 my $ind2s = [ $csv->fields() ];
3460 $in_values =~ s/^{//;
3461 $in_values =~ s/}$//;
3462 $status = $csv->parse($in_values);
3463 my $values = [ $csv->fields() ];
3465 my $marc = MARC::Record->new();
3467 $marc->leader('00000nam a22000007 4500');
3468 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3470 foreach my $i (0..$#$tags) {
3472 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3475 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3476 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3478 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3482 my $xml = $marc->as_xml_record;
3483 $xml =~ s/^<\?.+?\?>$//mo;
3485 $xml =~ s/>\s+</></sgo;
3489 $func$ LANGUAGE PLPERLU;
3490 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.
3491 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3492 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3493 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3494 The fourth argument is an array of text containing the values to plug into each field.
3495 If the value for a given field is NULL or the empty string, it is not inserted.
3498 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3500 my ($marcxml, $tag, $pos, $value) = @_;
3503 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3507 MARC::Charset->assume_unicode(1);
3509 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3510 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3511 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3512 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3516 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3518 foreach my $field ($marc->field($tag)) {
3519 $field->update("ind$pos" => $value);
3521 $xml = $marc->as_xml_record;
3522 $xml =~ s/^<\?.+?\?>$//mo;
3524 $xml =~ s/>\s+</></sgo;
3528 $func$ LANGUAGE PLPERLU;
3530 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3531 The first argument is a MARCXML string.
3532 The second argument is a MARC tag.
3533 The third argument is the indicator position, either 1 or 2.
3534 The fourth argument is the character to set the indicator value to.
3535 All occurences of the specified field will be changed.
3536 The function returns the revised MARCXML string.$$;
3538 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3543 first_name TEXT DEFAULT '',
3544 last_name TEXT DEFAULT ''
3545 ) RETURNS VOID AS $func$
3547 RAISE NOTICE '%', org ;
3548 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3549 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3550 FROM actor.org_unit aou, permission.grp_tree pgt
3551 WHERE aou.shortname = org
3552 AND pgt.name = perm_group;
3557 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3558 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3560 target_event_def ALIAS FOR $1;
3563 DROP TABLE IF EXISTS new_atevdefs;
3564 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3565 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3566 INSERT INTO action_trigger.event_definition (
3587 ,name || ' (clone of '||target_event_def||')'
3603 action_trigger.event_definition
3605 id = target_event_def
3607 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3608 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3609 INSERT INTO action_trigger.environment (
3615 currval('action_trigger.event_definition_id_seq')
3620 action_trigger.environment
3622 event_def = target_event_def
3624 INSERT INTO action_trigger.event_params (
3629 currval('action_trigger.event_definition_id_seq')
3633 action_trigger.event_params
3635 event_def = target_event_def
3638 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);
3640 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3642 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3643 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3645 target_event_def ALIAS FOR $1;
3647 new_interval ALIAS FOR $3;
3649 DROP TABLE IF EXISTS new_atevdefs;
3650 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3651 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3652 INSERT INTO action_trigger.event_definition (
3673 ,name || ' (clone of '||target_event_def||')'
3689 action_trigger.event_definition
3691 id = target_event_def
3693 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3694 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3695 INSERT INTO action_trigger.environment (
3701 currval('action_trigger.event_definition_id_seq')
3706 action_trigger.environment
3708 event_def = target_event_def
3710 INSERT INTO action_trigger.event_params (
3715 currval('action_trigger.event_definition_id_seq')
3719 action_trigger.event_params
3721 event_def = target_event_def
3724 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);
3726 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3728 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3729 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3732 target_event_defs ALIAS FOR $2;
3734 DROP TABLE IF EXISTS new_atevdefs;
3735 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3736 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3737 INSERT INTO action_trigger.event_definition (
3758 ,name || ' (clone of '||target_event_defs[i]||')'
3774 action_trigger.event_definition
3776 id = target_event_defs[i]
3778 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3779 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3780 INSERT INTO action_trigger.environment (
3786 currval('action_trigger.event_definition_id_seq')
3791 action_trigger.environment
3793 event_def = target_event_defs[i]
3795 INSERT INTO action_trigger.event_params (
3800 currval('action_trigger.event_definition_id_seq')
3804 action_trigger.event_params
3806 event_def = target_event_defs[i]
3809 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3811 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3813 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3815 action_trigger.event
3819 ,complete_time = NULL
3820 ,update_process = NULL
3822 ,template_output = NULL
3823 ,error_output = NULL
3824 ,async_output = NULL
3829 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3833 use MARC::File::XML;
3838 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3839 $field = $marc->leader();
3842 $$ LANGUAGE PLPERLU STABLE;
3844 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3845 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3848 use MARC::File::XML;
3853 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3854 $field = $marc->field($tag);
3856 return $field->as_string($subfield,$delimiter) if $field;
3858 $$ LANGUAGE PLPERLU STABLE;
3860 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3861 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3864 use MARC::File::XML;
3869 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3870 @fields = $marc->field($tag);
3873 foreach my $field (@fields) {
3874 push @texts, $field->as_string($subfield,$delimiter);
3877 $$ LANGUAGE PLPERLU STABLE;
3879 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3880 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3883 use MARC::File::XML;
3888 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3889 @fields = $marc->field($tag);
3892 foreach my $field (@fields) {
3893 if ($field->as_string() =~ qr/$match/) {
3894 push @texts, $field->as_string($subfield,$delimiter);
3898 $$ LANGUAGE PLPERLU STABLE;
3900 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3901 SELECT action.find_hold_matrix_matchpoint(
3902 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3903 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3904 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3905 (SELECT usr FROM action.hold_request WHERE id = $1),
3906 (SELECT requestor FROM action.hold_request WHERE id = $1)
3910 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3911 SELECT action.hold_request_permit_test(
3912 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3913 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3914 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3915 (SELECT usr FROM action.hold_request WHERE id = $1),
3916 (SELECT requestor FROM action.hold_request WHERE id = $1)
3920 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3921 SELECT action.find_circ_matrix_matchpoint(
3922 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3923 (SELECT target_copy FROM action.circulation WHERE id = $1),
3924 (SELECT usr FROM action.circulation WHERE id = $1),
3926 NULLIF(phone_renewal,false),
3927 NULLIF(desk_renewal,false),
3928 NULLIF(opac_renewal,false),
3930 ) FROM action.circulation WHERE id = $1
3935 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3940 RAISE EXCEPTION 'assertion';
3943 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3945 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3951 RAISE EXCEPTION '%', msg;
3954 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3956 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3959 fail_msg ALIAS FOR $2;
3960 success_msg ALIAS FOR $3;
3963 RAISE EXCEPTION '%', fail_msg;
3967 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3969 -- push bib sequence and return starting value for reserved range
3970 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3972 bib_count ALIAS FOR $1;
3975 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3977 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3982 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3984 -- set a new salted password
3986 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3988 usr_id ALIAS FOR $1;
3989 plain_passwd ALIAS FOR $2;
3994 SELECT actor.create_salt('main') INTO plain_salt;
3996 SELECT MD5(plain_passwd) INTO md5_passwd;
3998 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
4003 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4006 -- convenience functions for handling copy_location maps
4007 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
4008 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
4011 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
4013 table_schema ALIAS FOR $1;
4014 table_name ALIAS FOR $2;
4015 org_shortname ALIAS FOR $3;
4016 org_range ALIAS FOR $4;
4017 make_assertion ALIAS FOR $5;
4020 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
4021 -- though we'll still use the passed org for the full path traversal when needed
4022 x_org_found BOOLEAN;
4028 EXECUTE 'SELECT EXISTS (
4030 FROM information_schema.columns
4031 WHERE table_schema = $1
4033 and column_name = ''desired_shelf''
4034 )' INTO proceed USING table_schema, table_name;
4036 RAISE EXCEPTION 'Missing column desired_shelf';
4039 EXECUTE 'SELECT EXISTS (
4041 FROM information_schema.columns
4042 WHERE table_schema = $1
4044 and column_name = ''x_org''
4045 )' INTO x_org_found USING table_schema, table_name;
4047 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4049 RAISE EXCEPTION 'Cannot find org by shortname';
4052 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4054 EXECUTE 'ALTER TABLE '
4055 || quote_ident(table_name)
4056 || ' DROP COLUMN IF EXISTS x_shelf';
4057 EXECUTE 'ALTER TABLE '
4058 || quote_ident(table_name)
4059 || ' ADD COLUMN x_shelf INTEGER';
4062 RAISE INFO 'Found x_org column';
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 = x_org'
4067 || ' AND NOT b.deleted';
4068 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4069 || ' SET x_shelf = b.id FROM asset.copy_location b'
4070 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4071 || ' AND b.owning_lib = x_org'
4072 || ' AND x_shelf IS NULL'
4073 || ' AND NOT b.deleted';
4075 RAISE INFO 'Did not find x_org column';
4076 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4077 || ' SET x_shelf = b.id FROM asset_copy_location b'
4078 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4079 || ' AND b.owning_lib = $1'
4080 || ' AND NOT b.deleted'
4082 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4083 || ' SET x_shelf = b.id FROM asset_copy_location b'
4084 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4085 || ' AND b.owning_lib = $1'
4086 || ' AND x_shelf IS NULL'
4087 || ' AND NOT b.deleted'
4091 FOREACH o IN ARRAY org_list LOOP
4092 RAISE INFO 'Considering org %', o;
4093 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4094 || ' SET x_shelf = b.id FROM asset.copy_location b'
4095 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4096 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
4097 || ' AND NOT b.deleted'
4099 GET DIAGNOSTICS row_count = ROW_COUNT;
4100 RAISE INFO 'Updated % rows', row_count;
4103 IF make_assertion THEN
4104 EXECUTE 'SELECT migration_tools.assert(
4105 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
4106 ''Cannot find a desired location'',
4107 ''Found all desired locations''
4112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4114 -- convenience functions for handling circmod maps
4116 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
4118 table_schema ALIAS FOR $1;
4119 table_name ALIAS FOR $2;
4122 EXECUTE 'SELECT EXISTS (
4124 FROM information_schema.columns
4125 WHERE table_schema = $1
4127 and column_name = ''desired_circmod''
4128 )' INTO proceed USING table_schema, table_name;
4130 RAISE EXCEPTION 'Missing column desired_circmod';
4133 EXECUTE 'ALTER TABLE '
4134 || quote_ident(table_name)
4135 || ' DROP COLUMN IF EXISTS x_circmod';
4136 EXECUTE 'ALTER TABLE '
4137 || quote_ident(table_name)
4138 || ' ADD COLUMN x_circmod TEXT';
4140 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4141 || ' SET x_circmod = code FROM config.circ_modifier b'
4142 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
4144 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4145 || ' SET x_circmod = code FROM config.circ_modifier b'
4146 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
4147 || ' AND x_circmod IS NULL';
4149 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4150 || ' SET x_circmod = code FROM config.circ_modifier b'
4151 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
4152 || ' AND x_circmod IS NULL';
4154 EXECUTE 'SELECT migration_tools.assert(
4155 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
4156 ''Cannot find a desired circulation modifier'',
4157 ''Found all desired circulation modifiers''
4161 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4163 -- convenience functions for handling item status maps
4165 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
4167 table_schema ALIAS FOR $1;
4168 table_name ALIAS FOR $2;
4171 EXECUTE 'SELECT EXISTS (
4173 FROM information_schema.columns
4174 WHERE table_schema = $1
4176 and column_name = ''desired_status''
4177 )' INTO proceed USING table_schema, table_name;
4179 RAISE EXCEPTION 'Missing column desired_status';
4182 EXECUTE 'ALTER TABLE '
4183 || quote_ident(table_name)
4184 || ' DROP COLUMN IF EXISTS x_status';
4185 EXECUTE 'ALTER TABLE '
4186 || quote_ident(table_name)
4187 || ' ADD COLUMN x_status INTEGER';
4189 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4190 || ' SET x_status = id FROM config.copy_status b'
4191 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
4193 EXECUTE 'SELECT migration_tools.assert(
4194 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
4195 ''Cannot find a desired copy status'',
4196 ''Found all desired copy statuses''
4200 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4202 -- convenience functions for handling org maps
4204 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
4206 table_schema ALIAS FOR $1;
4207 table_name ALIAS FOR $2;
4210 EXECUTE 'SELECT EXISTS (
4212 FROM information_schema.columns
4213 WHERE table_schema = $1
4215 and column_name = ''desired_org''
4216 )' INTO proceed USING table_schema, table_name;
4218 RAISE EXCEPTION 'Missing column desired_org';
4221 EXECUTE 'ALTER TABLE '
4222 || quote_ident(table_name)
4223 || ' DROP COLUMN IF EXISTS x_org';
4224 EXECUTE 'ALTER TABLE '
4225 || quote_ident(table_name)
4226 || ' ADD COLUMN x_org INTEGER';
4228 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4229 || ' SET x_org = b.id FROM actor.org_unit b'
4230 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
4232 EXECUTE 'SELECT migration_tools.assert(
4233 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
4234 ''Cannot find a desired org unit'',
4235 ''Found all desired org units''
4239 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4241 -- convenience function for handling desired_not_migrate
4243 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
4245 table_schema ALIAS FOR $1;
4246 table_name ALIAS FOR $2;
4249 EXECUTE 'SELECT EXISTS (
4251 FROM information_schema.columns
4252 WHERE table_schema = $1
4254 and column_name = ''desired_not_migrate''
4255 )' INTO proceed USING table_schema, table_name;
4257 RAISE EXCEPTION 'Missing column desired_not_migrate';
4260 EXECUTE 'ALTER TABLE '
4261 || quote_ident(table_name)
4262 || ' DROP COLUMN IF EXISTS x_migrate';
4263 EXECUTE 'ALTER TABLE '
4264 || quote_ident(table_name)
4265 || ' ADD COLUMN x_migrate BOOLEAN';
4267 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4268 || ' SET x_migrate = CASE'
4269 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4270 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4271 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4272 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4273 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4274 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4277 EXECUTE 'SELECT migration_tools.assert(
4278 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4279 ''Not all desired_not_migrate values understood'',
4280 ''All desired_not_migrate values understood''
4284 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4286 -- convenience function for handling desired_not_migrate
4288 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4290 table_schema ALIAS FOR $1;
4291 table_name ALIAS FOR $2;
4294 EXECUTE 'SELECT EXISTS (
4296 FROM information_schema.columns
4297 WHERE table_schema = $1
4299 and column_name = ''desired_barred_or_blocked''
4300 )' INTO proceed USING table_schema, table_name;
4302 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4305 EXECUTE 'ALTER TABLE '
4306 || quote_ident(table_name)
4307 || ' DROP COLUMN IF EXISTS x_barred';
4308 EXECUTE 'ALTER TABLE '
4309 || quote_ident(table_name)
4310 || ' ADD COLUMN x_barred BOOLEAN';
4312 EXECUTE 'ALTER TABLE '
4313 || quote_ident(table_name)
4314 || ' DROP COLUMN IF EXISTS x_blocked';
4315 EXECUTE 'ALTER TABLE '
4316 || quote_ident(table_name)
4317 || ' ADD COLUMN x_blocked BOOLEAN';
4319 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4320 || ' SET x_barred = CASE'
4321 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4322 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4323 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4324 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4327 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4328 || ' SET x_blocked = CASE'
4329 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4330 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4331 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4332 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4335 EXECUTE 'SELECT migration_tools.assert(
4336 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4337 ''Not all desired_barred_or_blocked values understood'',
4338 ''All desired_barred_or_blocked values understood''
4342 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4344 -- convenience function for handling desired_profile
4346 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4348 table_schema ALIAS FOR $1;
4349 table_name ALIAS FOR $2;
4352 EXECUTE 'SELECT EXISTS (
4354 FROM information_schema.columns
4355 WHERE table_schema = $1
4357 and column_name = ''desired_profile''
4358 )' INTO proceed USING table_schema, table_name;
4360 RAISE EXCEPTION 'Missing column desired_profile';
4363 EXECUTE 'ALTER TABLE '
4364 || quote_ident(table_name)
4365 || ' DROP COLUMN IF EXISTS x_profile';
4366 EXECUTE 'ALTER TABLE '
4367 || quote_ident(table_name)
4368 || ' ADD COLUMN x_profile INTEGER';
4370 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4371 || ' SET x_profile = b.id FROM permission.grp_tree b'
4372 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4374 EXECUTE 'SELECT migration_tools.assert(
4375 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4376 ''Cannot find a desired profile'',
4377 ''Found all desired profiles''
4381 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4383 -- convenience function for handling desired actor stat cats
4385 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4387 table_schema ALIAS FOR $1;
4388 table_name ALIAS FOR $2;
4389 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4390 org_shortname ALIAS FOR $4;
4398 SELECT 'desired_sc' || field_suffix INTO sc;
4399 SELECT 'desired_sce' || field_suffix INTO sce;
4401 EXECUTE 'SELECT EXISTS (
4403 FROM information_schema.columns
4404 WHERE table_schema = $1
4406 and column_name = $3
4407 )' INTO proceed USING table_schema, table_name, sc;
4409 RAISE EXCEPTION 'Missing column %', sc;
4411 EXECUTE 'SELECT EXISTS (
4413 FROM information_schema.columns
4414 WHERE table_schema = $1
4416 and column_name = $3
4417 )' INTO proceed USING table_schema, table_name, sce;
4419 RAISE EXCEPTION 'Missing column %', sce;
4422 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4424 RAISE EXCEPTION 'Cannot find org by shortname';
4426 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4428 -- caller responsible for their own truncates though we try to prevent duplicates
4429 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4434 ' || quote_ident(table_name) || '
4436 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4440 WHERE owner = ANY ($2)
4441 AND name = BTRIM('||sc||')
4446 WHERE owner = ANY ($2)
4447 AND name = BTRIM('||sc||')
4450 USING org, org_list;
4452 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4457 WHERE owner = ANY ($2)
4458 AND BTRIM('||sc||') = BTRIM(name))
4461 WHERE owner = ANY ($2)
4462 AND BTRIM('||sc||') = BTRIM(name))
4467 ' || quote_ident(table_name) || '
4469 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4470 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4473 FROM actor.stat_cat_entry
4477 WHERE owner = ANY ($2)
4478 AND BTRIM('||sc||') = BTRIM(name)
4479 ) AND value = BTRIM('||sce||')
4480 AND owner = ANY ($2)
4484 FROM actor_stat_cat_entry
4488 WHERE owner = ANY ($2)
4489 AND BTRIM('||sc||') = BTRIM(name)
4490 ) AND value = BTRIM('||sce||')
4491 AND owner = ANY ($2)
4494 USING org, org_list;
4496 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4498 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4500 table_schema ALIAS FOR $1;
4501 table_name ALIAS FOR $2;
4502 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4503 org_shortname ALIAS FOR $4;
4511 SELECT 'desired_sc' || field_suffix INTO sc;
4512 SELECT 'desired_sce' || field_suffix INTO sce;
4513 EXECUTE 'SELECT EXISTS (
4515 FROM information_schema.columns
4516 WHERE table_schema = $1
4518 and column_name = $3
4519 )' INTO proceed USING table_schema, table_name, sc;
4521 RAISE EXCEPTION 'Missing column %', sc;
4523 EXECUTE 'SELECT EXISTS (
4525 FROM information_schema.columns
4526 WHERE table_schema = $1
4528 and column_name = $3
4529 )' INTO proceed USING table_schema, table_name, sce;
4531 RAISE EXCEPTION 'Missing column %', sce;
4534 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4536 RAISE EXCEPTION 'Cannot find org by shortname';
4539 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4541 EXECUTE 'ALTER TABLE '
4542 || quote_ident(table_name)
4543 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4544 EXECUTE 'ALTER TABLE '
4545 || quote_ident(table_name)
4546 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4547 EXECUTE 'ALTER TABLE '
4548 || quote_ident(table_name)
4549 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4550 EXECUTE 'ALTER TABLE '
4551 || quote_ident(table_name)
4552 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4555 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4557 x_sc' || field_suffix || ' = id
4559 (SELECT id, name, owner FROM actor_stat_cat
4560 UNION SELECT id, name, owner FROM actor.stat_cat) u
4562 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4563 AND u.owner = ANY ($1);'
4566 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4568 x_sce' || field_suffix || ' = id
4570 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4571 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4573 u.stat_cat = x_sc' || field_suffix || '
4574 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4575 AND u.owner = ANY ($1);'
4578 EXECUTE 'SELECT migration_tools.assert(
4579 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4580 ''Cannot find a desired stat cat'',
4581 ''Found all desired stat cats''
4584 EXECUTE 'SELECT migration_tools.assert(
4585 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4586 ''Cannot find a desired stat cat entry'',
4587 ''Found all desired stat cat entries''
4591 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4593 -- convenience functions for adding shelving locations
4594 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4595 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4601 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4604 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4605 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4606 IF return_id IS NOT NULL THEN
4614 $$ LANGUAGE plpgsql;
4616 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4618 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4619 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4625 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4628 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4630 SELECT INTO return_id id FROM
4631 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4632 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4633 IF return_id IS NOT NULL THEN
4641 $$ LANGUAGE plpgsql;
4643 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4644 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4651 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4653 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4654 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4658 $BODY$ LANGUAGE plpgsql;
4660 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4661 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4669 use MARC::File::XML (BinaryEncoding => 'utf8');
4671 binmode(STDERR, ':bytes');
4672 binmode(STDOUT, ':utf8');
4673 binmode(STDERR, ':utf8');
4675 my $marc_xml = shift;
4676 my $new_9_to_set = shift;
4679 $marc_xml =~ s/(<leader>.........)./${1}a/;
4682 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4685 #elog("could not parse $bibid: $@\n");
4686 import MARC::File::XML (BinaryEncoding => 'utf8');
4690 my @uris = $marc_xml->field('856');
4691 return $marc_xml->as_xml_record() unless @uris;
4693 foreach my $field (@uris) {
4694 my $ind1 = $field->indicator('1');
4695 if (!defined $ind1) { next; }
4696 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4697 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4698 my $ind2 = $field->indicator('2');
4699 if (!defined $ind2) { next; }
4700 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4701 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4702 $field->add_subfields( '9' => $new_9_to_set );
4705 return $marc_xml->as_xml_record();
4709 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4710 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4718 use MARC::File::XML (BinaryEncoding => 'utf8');
4720 binmode(STDERR, ':bytes');
4721 binmode(STDOUT, ':utf8');
4722 binmode(STDERR, ':utf8');
4724 my $marc_xml = shift;
4725 my $qualifying_match = shift;
4726 my $new_9_to_set = shift;
4729 $marc_xml =~ s/(<leader>.........)./${1}a/;
4732 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4735 #elog("could not parse $bibid: $@\n");
4736 import MARC::File::XML (BinaryEncoding => 'utf8');
4740 my @uris = $marc_xml->field('856');
4741 return $marc_xml->as_xml_record() unless @uris;
4743 foreach my $field (@uris) {
4744 if ($field->as_string() =~ qr/$qualifying_match/) {
4745 my $ind1 = $field->indicator('1');
4746 if (!defined $ind1) { next; }
4747 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4748 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4749 my $ind2 = $field->indicator('2');
4750 if (!defined $ind2) { next; }
4751 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4752 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4753 $field->add_subfields( '9' => $new_9_to_set );
4757 return $marc_xml->as_xml_record();
4761 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4762 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4770 use MARC::File::XML (BinaryEncoding => 'utf8');
4772 binmode(STDERR, ':bytes');
4773 binmode(STDOUT, ':utf8');
4774 binmode(STDERR, ':utf8');
4776 my $marc_xml = shift;
4777 my $substring_old_value = shift;
4778 my $new_value = shift;
4779 my $fix_indicators = shift;
4781 $marc_xml =~ s/(<leader>.........)./${1}a/;
4784 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4787 #elog("could not parse $bibid: $@\n");
4788 import MARC::File::XML (BinaryEncoding => 'utf8');
4792 my @uris = $marc_xml->field('856');
4793 return $marc_xml->as_xml_record() unless @uris;
4795 foreach my $field (@uris) {
4796 my $ind1 = $field->indicator('1');
4797 if (defined $ind1) {
4798 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4799 $field->set_indicator(1,'4');
4802 my $ind2 = $field->indicator('2');
4803 if (defined $ind2) {
4804 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4805 $field->set_indicator(2,'0');
4808 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4809 $field->delete_subfield('9');
4810 $field->add_subfields( '9' => $new_value );
4812 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4815 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4816 $marc_xml->insert_fields_ordered( values( %hash ) );
4818 return $marc_xml->as_xml_record();
4822 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4823 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)
4831 use MARC::File::XML (BinaryEncoding => 'utf8');
4833 binmode(STDERR, ':bytes');
4834 binmode(STDOUT, ':utf8');
4835 binmode(STDERR, ':utf8');
4837 my $marc_xml = shift;
4838 my $qualifying_match = shift;
4839 my $substring_old_value = shift;
4840 my $new_value = shift;
4841 my $fix_indicators = shift;
4843 $marc_xml =~ s/(<leader>.........)./${1}a/;
4846 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4849 #elog("could not parse $bibid: $@\n");
4850 import MARC::File::XML (BinaryEncoding => 'utf8');
4854 my @unqualified_uris = $marc_xml->field('856');
4856 foreach my $field (@unqualified_uris) {
4857 if ($field->as_string() =~ qr/$qualifying_match/) {
4861 return $marc_xml->as_xml_record() unless @uris;
4863 foreach my $field (@uris) {
4864 my $ind1 = $field->indicator('1');
4865 if (defined $ind1) {
4866 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4867 $field->set_indicator(1,'4');
4870 my $ind2 = $field->indicator('2');
4871 if (defined $ind2) {
4872 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4873 $field->set_indicator(2,'0');
4876 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4877 $field->delete_subfield('9');
4878 $field->add_subfields( '9' => $new_value );
4880 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4883 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4884 $marc_xml->insert_fields_ordered( values( %hash ) );
4886 return $marc_xml->as_xml_record();
4891 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4892 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4900 use MARC::File::XML (BinaryEncoding => 'utf8');
4902 binmode(STDERR, ':bytes');
4903 binmode(STDOUT, ':utf8');
4904 binmode(STDERR, ':utf8');
4906 my $marc_xml = shift;
4909 $marc_xml =~ s/(<leader>.........)./${1}a/;
4912 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4915 #elog("could not parse $bibid: $@\n");
4916 import MARC::File::XML (BinaryEncoding => 'utf8');
4920 my @fields = $marc_xml->field($tag);
4921 return $marc_xml->as_xml_record() unless @fields;
4923 $marc_xml->delete_fields(@fields);
4925 return $marc_xml->as_xml_record();
4929 -- removes tags from record based on tag, subfield and evidence
4930 -- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
4931 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
4932 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence TEXT)
4940 use MARC::File::XML (BinaryEncoding => 'utf8');
4942 binmode(STDERR, ':bytes');
4943 binmode(STDOUT, ':utf8');
4944 binmode(STDERR, ':utf8');
4946 my $marc_xml = shift;
4948 my $subfield = shift;
4949 my $evidence = shift;
4951 $marc_xml =~ s/(<leader>.........)./${1}a/;
4954 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4957 #elog("could not parse $bibid: $@\n");
4958 import MARC::File::XML (BinaryEncoding => 'utf8');
4962 my @fields = $marc_xml->field($tag);
4963 return $marc_xml->as_xml_record() unless @fields;
4965 my @fields_to_delete;
4967 foreach my $f (@fields) {
4968 my $sf = lc($f->as_string($subfield));
4969 if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
4972 $marc_xml->delete_fields(@fields_to_delete);
4974 return $marc_xml->as_xml_record();
4979 -- consolidate marc tag
4980 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
4981 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
4989 use MARC::File::XML (BinaryEncoding => 'utf8');
4991 binmode(STDERR, ':bytes');
4992 binmode(STDOUT, ':utf8');
4993 binmode(STDERR, ':utf8');
4995 my $marc_xml = shift;
4998 $marc_xml =~ s/(<leader>.........)./${1}a/;
5001 $marc_xml = MARC::Record->new_from_xml($marc_xml);
5004 #elog("could not parse $bibid: $@\n");
5005 import MARC::File::XML (BinaryEncoding => 'utf8');
5009 my @fields = $marc_xml->field($tag);
5010 return $marc_xml->as_xml_record() unless @fields;
5012 my @combined_subfield_refs = ();
5013 my @combined_subfields = ();
5014 foreach my $field (@fields) {
5015 my @subfield_refs = $field->subfields();
5016 push @combined_subfield_refs, @subfield_refs;
5019 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
5021 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
5022 my ($code,$data) = @$tuple;
5023 unshift( @combined_subfields, $code, $data );
5026 $marc_xml->delete_fields(@fields);
5028 my $new_field = new MARC::Field(
5030 $fields[0]->indicator(1),
5031 $fields[0]->indicator(2),
5035 $marc_xml->insert_grouped_field( $new_field );
5037 return $marc_xml->as_xml_record();
5041 -- convenience function for linking to the item staging table
5043 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5045 table_schema ALIAS FOR $1;
5046 table_name ALIAS FOR $2;
5047 foreign_column_name ALIAS FOR $3;
5048 main_column_name ALIAS FOR $4;
5049 btrim_desired ALIAS FOR $5;
5052 EXECUTE 'SELECT EXISTS (
5054 FROM information_schema.columns
5055 WHERE table_schema = $1
5057 and column_name = $3
5058 )' INTO proceed USING table_schema, table_name, foreign_column_name;
5060 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5063 EXECUTE 'SELECT EXISTS (
5065 FROM information_schema.columns
5066 WHERE table_schema = $1
5067 AND table_name = ''asset_copy_legacy''
5068 and column_name = $2
5069 )' INTO proceed USING table_schema, main_column_name;
5071 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
5074 EXECUTE 'ALTER TABLE '
5075 || quote_ident(table_name)
5076 || ' DROP COLUMN IF EXISTS x_item';
5077 EXECUTE 'ALTER TABLE '
5078 || quote_ident(table_name)
5079 || ' ADD COLUMN x_item BIGINT';
5081 IF btrim_desired THEN
5082 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5083 || ' SET x_item = b.id FROM asset_copy_legacy b'
5084 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5085 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5087 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5088 || ' SET x_item = b.id FROM asset_copy_legacy b'
5089 || ' WHERE a.' || quote_ident(foreign_column_name)
5090 || ' = b.' || quote_ident(main_column_name);
5093 --EXECUTE 'SELECT migration_tools.assert(
5094 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
5095 -- ''Cannot link every barcode'',
5096 -- ''Every barcode linked''
5100 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5102 -- convenience function for linking to the user staging table
5104 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5106 table_schema ALIAS FOR $1;
5107 table_name ALIAS FOR $2;
5108 foreign_column_name ALIAS FOR $3;
5109 main_column_name ALIAS FOR $4;
5110 btrim_desired ALIAS FOR $5;
5113 EXECUTE 'SELECT EXISTS (
5115 FROM information_schema.columns
5116 WHERE table_schema = $1
5118 and column_name = $3
5119 )' INTO proceed USING table_schema, table_name, foreign_column_name;
5121 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5124 EXECUTE 'SELECT EXISTS (
5126 FROM information_schema.columns
5127 WHERE table_schema = $1
5128 AND table_name = ''actor_usr_legacy''
5129 and column_name = $2
5130 )' INTO proceed USING table_schema, main_column_name;
5132 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
5135 EXECUTE 'ALTER TABLE '
5136 || quote_ident(table_name)
5137 || ' DROP COLUMN IF EXISTS x_user';
5138 EXECUTE 'ALTER TABLE '
5139 || quote_ident(table_name)
5140 || ' ADD COLUMN x_user INTEGER';
5142 IF btrim_desired THEN
5143 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5144 || ' SET x_user = b.id FROM actor_usr_legacy b'
5145 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5146 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5148 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5149 || ' SET x_user = b.id FROM actor_usr_legacy b'
5150 || ' WHERE a.' || quote_ident(foreign_column_name)
5151 || ' = b.' || quote_ident(main_column_name);
5154 --EXECUTE 'SELECT migration_tools.assert(
5155 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
5156 -- ''Cannot link every barcode'',
5157 -- ''Every barcode linked''
5161 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5163 -- convenience function for linking two tables
5164 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
5165 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5167 table_schema ALIAS FOR $1;
5168 table_a ALIAS FOR $2;
5169 column_a ALIAS FOR $3;
5170 table_b ALIAS FOR $4;
5171 column_b ALIAS FOR $5;
5172 column_x ALIAS FOR $6;
5173 btrim_desired ALIAS FOR $7;
5176 EXECUTE 'SELECT EXISTS (
5178 FROM information_schema.columns
5179 WHERE table_schema = $1
5181 and column_name = $3
5182 )' INTO proceed USING table_schema, table_a, column_a;
5184 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5187 EXECUTE 'SELECT EXISTS (
5189 FROM information_schema.columns
5190 WHERE table_schema = $1
5192 and column_name = $3
5193 )' INTO proceed USING table_schema, table_b, column_b;
5195 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5198 EXECUTE 'ALTER TABLE '
5199 || quote_ident(table_b)
5200 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5201 EXECUTE 'ALTER TABLE '
5202 || quote_ident(table_b)
5203 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
5205 IF btrim_desired THEN
5206 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5207 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5208 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5209 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5211 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5212 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5213 || ' WHERE a.' || quote_ident(column_a)
5214 || ' = b.' || quote_ident(column_b);
5218 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5220 -- convenience function for linking two tables, but copying column w into column x instead of "id"
5221 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
5222 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5224 table_schema ALIAS FOR $1;
5225 table_a ALIAS FOR $2;
5226 column_a ALIAS FOR $3;
5227 table_b ALIAS FOR $4;
5228 column_b ALIAS FOR $5;
5229 column_w ALIAS FOR $6;
5230 column_x ALIAS FOR $7;
5231 btrim_desired ALIAS FOR $8;
5234 EXECUTE 'SELECT EXISTS (
5236 FROM information_schema.columns
5237 WHERE table_schema = $1
5239 and column_name = $3
5240 )' INTO proceed USING table_schema, table_a, column_a;
5242 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5245 EXECUTE 'SELECT EXISTS (
5247 FROM information_schema.columns
5248 WHERE table_schema = $1
5250 and column_name = $3
5251 )' INTO proceed USING table_schema, table_b, column_b;
5253 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5256 EXECUTE 'ALTER TABLE '
5257 || quote_ident(table_b)
5258 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5259 EXECUTE 'ALTER TABLE '
5260 || quote_ident(table_b)
5261 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
5263 IF btrim_desired THEN
5264 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5265 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5266 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5267 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5269 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5270 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5271 || ' WHERE a.' || quote_ident(column_a)
5272 || ' = b.' || quote_ident(column_b);
5276 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5278 -- 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
5279 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
5280 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5282 table_schema ALIAS FOR $1;
5283 table_a ALIAS FOR $2;
5284 column_a ALIAS FOR $3;
5285 table_b ALIAS FOR $4;
5286 column_b ALIAS FOR $5;
5287 column_w ALIAS FOR $6;
5288 column_x ALIAS FOR $7;
5291 EXECUTE 'SELECT EXISTS (
5293 FROM information_schema.columns
5294 WHERE table_schema = $1
5296 and column_name = $3
5297 )' INTO proceed USING table_schema, table_a, column_a;
5299 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
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_b, column_b;
5310 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5313 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5314 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5315 || ' WHERE a.' || quote_ident(column_a)
5316 || ' = b.' || quote_ident(column_b);
5319 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5321 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5323 table_schema ALIAS FOR $1;
5324 table_a ALIAS FOR $2;
5325 column_a ALIAS FOR $3;
5326 table_b ALIAS FOR $4;
5327 column_b ALIAS FOR $5;
5328 column_w ALIAS FOR $6;
5329 column_x ALIAS FOR $7;
5332 EXECUTE 'SELECT EXISTS (
5334 FROM information_schema.columns
5335 WHERE table_schema = $1
5337 and column_name = $3
5338 )' INTO proceed USING table_schema, table_a, column_a;
5340 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5343 EXECUTE 'SELECT EXISTS (
5345 FROM information_schema.columns
5346 WHERE table_schema = $1
5348 and column_name = $3
5349 )' INTO proceed USING table_schema, table_b, column_b;
5351 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5354 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5355 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5356 || ' WHERE a.' || quote_ident(column_a)
5357 || ' = b.' || quote_ident(column_b)
5358 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5361 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5363 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5365 table_schema ALIAS FOR $1;
5366 table_a ALIAS FOR $2;
5367 column_a ALIAS FOR $3;
5368 table_b ALIAS FOR $4;
5369 column_b ALIAS FOR $5;
5370 column_w ALIAS FOR $6;
5371 column_x ALIAS FOR $7;
5374 EXECUTE 'SELECT EXISTS (
5376 FROM information_schema.columns
5377 WHERE table_schema = $1
5379 and column_name = $3
5380 )' INTO proceed USING table_schema, table_a, column_a;
5382 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5385 EXECUTE 'SELECT EXISTS (
5387 FROM information_schema.columns
5388 WHERE table_schema = $1
5390 and column_name = $3
5391 )' INTO proceed USING table_schema, table_b, column_b;
5393 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5396 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5397 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5398 || ' WHERE a.' || quote_ident(column_a)
5399 || ' = b.' || quote_ident(column_b)
5400 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5403 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5405 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5407 table_schema ALIAS FOR $1;
5408 table_a ALIAS FOR $2;
5409 column_a ALIAS FOR $3;
5410 table_b ALIAS FOR $4;
5411 column_b ALIAS FOR $5;
5412 column_w ALIAS FOR $6;
5413 column_x ALIAS FOR $7;
5416 EXECUTE 'SELECT EXISTS (
5418 FROM information_schema.columns
5419 WHERE table_schema = $1
5421 and column_name = $3
5422 )' INTO proceed USING table_schema, table_a, column_a;
5424 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5427 EXECUTE 'SELECT EXISTS (
5429 FROM information_schema.columns
5430 WHERE table_schema = $1
5432 and column_name = $3
5433 )' INTO proceed USING table_schema, table_b, column_b;
5435 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5438 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5439 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5440 || ' WHERE a.' || quote_ident(column_a)
5441 || ' = b.' || quote_ident(column_b)
5442 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5445 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5447 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5449 table_schema ALIAS FOR $1;
5450 table_a ALIAS FOR $2;
5451 column_a ALIAS FOR $3;
5452 table_b ALIAS FOR $4;
5453 column_b ALIAS FOR $5;
5454 column_w ALIAS FOR $6;
5455 column_x ALIAS FOR $7;
5458 EXECUTE 'SELECT EXISTS (
5460 FROM information_schema.columns
5461 WHERE table_schema = $1
5463 and column_name = $3
5464 )' INTO proceed USING table_schema, table_a, column_a;
5466 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5469 EXECUTE 'SELECT EXISTS (
5471 FROM information_schema.columns
5472 WHERE table_schema = $1
5474 and column_name = $3
5475 )' INTO proceed USING table_schema, table_b, column_b;
5477 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5480 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5481 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5482 || ' WHERE a.' || quote_ident(column_a)
5483 || ' = b.' || quote_ident(column_b)
5484 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5487 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5489 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5491 table_schema ALIAS FOR $1;
5492 table_a ALIAS FOR $2;
5493 column_a ALIAS FOR $3;
5494 table_b ALIAS FOR $4;
5495 column_b ALIAS FOR $5;
5496 column_w ALIAS FOR $6;
5497 column_x ALIAS FOR $7;
5500 EXECUTE 'SELECT EXISTS (
5502 FROM information_schema.columns
5503 WHERE table_schema = $1
5505 and column_name = $3
5506 )' INTO proceed USING table_schema, table_a, column_a;
5508 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5511 EXECUTE 'SELECT EXISTS (
5513 FROM information_schema.columns
5514 WHERE table_schema = $1
5516 and column_name = $3
5517 )' INTO proceed USING table_schema, table_b, column_b;
5519 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5522 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5523 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5524 || ' WHERE a.' || quote_ident(column_a)
5525 || ' = b.' || quote_ident(column_b)
5526 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5529 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5531 -- convenience function for handling desired asset stat cats
5533 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5535 table_schema ALIAS FOR $1;
5536 table_name ALIAS FOR $2;
5537 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5538 org_shortname ALIAS FOR $4;
5546 SELECT 'desired_sc' || field_suffix INTO sc;
5547 SELECT 'desired_sce' || field_suffix INTO sce;
5549 EXECUTE 'SELECT EXISTS (
5551 FROM information_schema.columns
5552 WHERE table_schema = $1
5554 and column_name = $3
5555 )' INTO proceed USING table_schema, table_name, sc;
5557 RAISE EXCEPTION 'Missing column %', sc;
5559 EXECUTE 'SELECT EXISTS (
5561 FROM information_schema.columns
5562 WHERE table_schema = $1
5564 and column_name = $3
5565 )' INTO proceed USING table_schema, table_name, sce;
5567 RAISE EXCEPTION 'Missing column %', sce;
5570 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5572 RAISE EXCEPTION 'Cannot find org by shortname';
5574 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5576 -- caller responsible for their own truncates though we try to prevent duplicates
5577 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5582 ' || quote_ident(table_name) || '
5584 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5588 WHERE owner = ANY ($2)
5589 AND name = BTRIM('||sc||')
5594 WHERE owner = ANY ($2)
5595 AND name = BTRIM('||sc||')
5598 USING org, org_list;
5600 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5605 WHERE owner = ANY ($2)
5606 AND BTRIM('||sc||') = BTRIM(name))
5609 WHERE owner = ANY ($2)
5610 AND BTRIM('||sc||') = BTRIM(name))
5615 ' || quote_ident(table_name) || '
5617 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5618 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5621 FROM asset.stat_cat_entry
5625 WHERE owner = ANY ($2)
5626 AND BTRIM('||sc||') = BTRIM(name)
5627 ) AND value = BTRIM('||sce||')
5628 AND owner = ANY ($2)
5632 FROM asset_stat_cat_entry
5636 WHERE owner = ANY ($2)
5637 AND BTRIM('||sc||') = BTRIM(name)
5638 ) AND value = BTRIM('||sce||')
5639 AND owner = ANY ($2)
5642 USING org, org_list;
5644 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5646 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5648 table_schema ALIAS FOR $1;
5649 table_name ALIAS FOR $2;
5650 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5651 org_shortname ALIAS FOR $4;
5659 SELECT 'desired_sc' || field_suffix INTO sc;
5660 SELECT 'desired_sce' || field_suffix INTO sce;
5661 EXECUTE 'SELECT EXISTS (
5663 FROM information_schema.columns
5664 WHERE table_schema = $1
5666 and column_name = $3
5667 )' INTO proceed USING table_schema, table_name, sc;
5669 RAISE EXCEPTION 'Missing column %', sc;
5671 EXECUTE 'SELECT EXISTS (
5673 FROM information_schema.columns
5674 WHERE table_schema = $1
5676 and column_name = $3
5677 )' INTO proceed USING table_schema, table_name, sce;
5679 RAISE EXCEPTION 'Missing column %', sce;
5682 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5684 RAISE EXCEPTION 'Cannot find org by shortname';
5687 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5689 EXECUTE 'ALTER TABLE '
5690 || quote_ident(table_name)
5691 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5692 EXECUTE 'ALTER TABLE '
5693 || quote_ident(table_name)
5694 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5695 EXECUTE 'ALTER TABLE '
5696 || quote_ident(table_name)
5697 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5698 EXECUTE 'ALTER TABLE '
5699 || quote_ident(table_name)
5700 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5703 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5705 x_sc' || field_suffix || ' = id
5707 (SELECT id, name, owner FROM asset_stat_cat
5708 UNION SELECT id, name, owner FROM asset.stat_cat) u
5710 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5711 AND u.owner = ANY ($1);'
5714 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5716 x_sce' || field_suffix || ' = id
5718 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5719 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5721 u.stat_cat = x_sc' || field_suffix || '
5722 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5723 AND u.owner = ANY ($1);'
5726 EXECUTE 'SELECT migration_tools.assert(
5727 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5728 ''Cannot find a desired stat cat'',
5729 ''Found all desired stat cats''
5732 EXECUTE 'SELECT migration_tools.assert(
5733 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5734 ''Cannot find a desired stat cat entry'',
5735 ''Found all desired stat cat entries''
5739 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5741 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5742 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5749 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5751 AND table_schema = s_name
5752 AND (data_type='text' OR data_type='character varying')
5753 AND column_name like 'l_%'
5755 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5762 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5763 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5770 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5772 AND table_schema = s_name
5773 AND (data_type='text' OR data_type='character varying')
5775 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5782 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5783 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5790 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5792 AND table_schema = s_name
5793 AND (data_type='text' OR data_type='character varying')
5794 AND column_name like 'l_%'
5796 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5803 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5804 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5811 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5813 AND table_schema = s_name
5814 AND (data_type='text' OR data_type='character varying')
5816 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5824 -- convenience function for handling item barcode collisions in asset_copy_legacy
5826 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5831 internal_collision_count NUMERIC := 0;
5832 incumbent_collision_count NUMERIC := 0;
5834 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5836 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5838 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5839 GET DIAGNOSTICS row_count = ROW_COUNT;
5840 internal_collision_count := internal_collision_count + row_count;
5843 RAISE INFO '% internal collisions', internal_collision_count;
5844 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
5846 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5848 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5849 GET DIAGNOSTICS row_count = ROW_COUNT;
5850 incumbent_collision_count := incumbent_collision_count + row_count;
5853 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5855 $function$ LANGUAGE plpgsql;
5857 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5858 -- this should be ran prior to populating actor_card
5860 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5865 internal_collision_count NUMERIC := 0;
5866 incumbent_barcode_collision_count NUMERIC := 0;
5867 incumbent_usrname_collision_count NUMERIC := 0;
5869 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5871 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5873 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5874 GET DIAGNOSTICS row_count = ROW_COUNT;
5875 internal_collision_count := internal_collision_count + row_count;
5878 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5881 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5883 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5885 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5886 GET DIAGNOSTICS row_count = ROW_COUNT;
5887 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5890 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5893 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5895 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5897 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5898 GET DIAGNOSTICS row_count = ROW_COUNT;
5899 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5902 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5904 $function$ LANGUAGE plpgsql;
5906 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5908 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5913 internal_collision_count NUMERIC := 0;
5914 incumbent_collision_count NUMERIC := 0;
5916 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5918 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5920 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5921 GET DIAGNOSTICS row_count = ROW_COUNT;
5922 internal_collision_count := internal_collision_count + row_count;
5925 RAISE INFO '% internal collisions', internal_collision_count;
5926 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
5928 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5930 UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5931 GET DIAGNOSTICS row_count = ROW_COUNT;
5932 incumbent_collision_count := incumbent_collision_count + row_count;
5935 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5937 $function$ LANGUAGE plpgsql;
5939 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5940 -- this should be ran prior to populating actor_card
5942 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5947 internal_collision_count NUMERIC := 0;
5948 incumbent_barcode_collision_count NUMERIC := 0;
5949 incumbent_usrname_collision_count NUMERIC := 0;
5951 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5953 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5955 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5956 GET DIAGNOSTICS row_count = ROW_COUNT;
5957 internal_collision_count := internal_collision_count + row_count;
5960 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5963 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5965 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5967 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5968 GET DIAGNOSTICS row_count = ROW_COUNT;
5969 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5972 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5975 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5977 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5979 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5980 GET DIAGNOSTICS row_count = ROW_COUNT;
5981 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5984 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5986 $function$ LANGUAGE plpgsql;
5988 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5989 -- WARNING: Use at your own risk
5990 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5992 item_object asset.copy%ROWTYPE;
5993 user_object actor.usr%ROWTYPE;
5994 test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5995 result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5996 safe_to_delete BOOLEAN := FALSE;
5997 m action.found_circ_matrix_matchpoint;
5998 n action.found_circ_matrix_matchpoint;
5999 -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
6000 result_matchpoint INTEGER;
6002 SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
6003 RAISE INFO 'testing rule: %', test_rule_object;
6005 INSERT INTO actor.usr (
6015 COALESCE(test_rule_object.grp, 2),
6016 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6021 COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
6022 COALESCE(test_rule_object.juvenile_flag, FALSE)
6025 SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
6027 INSERT INTO asset.call_number (
6038 COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
6039 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6043 INSERT INTO asset.copy (
6055 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6056 COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
6058 currval('asset.call_number_id_seq'),
6060 COALESCE(test_rule_object.copy_location,1),
6063 COALESCE(test_rule_object.ref_flag,FALSE),
6064 test_rule_object.circ_modifier
6067 SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
6069 SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
6070 test_rule_object.org_unit,
6073 COALESCE(test_rule_object.is_renewal,FALSE)
6075 RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6076 test_rule_object.org_unit,
6079 COALESCE(test_rule_object.is_renewal,FALSE),
6085 -- disable the rule being tested to see if the outcome changes
6086 UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
6088 SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
6089 test_rule_object.org_unit,
6092 COALESCE(test_rule_object.is_renewal,FALSE)
6094 RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6095 test_rule_object.org_unit,
6098 COALESCE(test_rule_object.is_renewal,FALSE),
6104 -- FIXME: We could dig deeper and see if the referenced config.rule_*
6105 -- entries are effectively equivalent, but for now, let's assume no
6106 -- duplicate rules at that level
6108 (m.matchpoint).circulate = (n.matchpoint).circulate
6109 AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
6110 AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
6111 AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
6113 (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
6115 (m.matchpoint).hard_due_date IS NULL
6116 AND (n.matchpoint).hard_due_date IS NULL
6120 (m.matchpoint).renewals = (n.matchpoint).renewals
6122 (m.matchpoint).renewals IS NULL
6123 AND (n.matchpoint).renewals IS NULL
6127 (m.matchpoint).grace_period = (n.matchpoint).grace_period
6129 (m.matchpoint).grace_period IS NULL
6130 AND (n.matchpoint).grace_period IS NULL
6134 (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
6136 (m.matchpoint).total_copy_hold_ratio IS NULL
6137 AND (n.matchpoint).total_copy_hold_ratio IS NULL
6141 (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
6143 (m.matchpoint).available_copy_hold_ratio IS NULL
6144 AND (n.matchpoint).available_copy_hold_ratio IS NULL
6148 SELECT limit_set, fallthrough
6149 FROM config.circ_matrix_limit_set_map
6150 WHERE active and matchpoint = (m.matchpoint).id
6152 SELECT limit_set, fallthrough
6153 FROM config.circ_matrix_limit_set_map
6154 WHERE active and matchpoint = (n.matchpoint).id
6158 RAISE INFO 'rule has same outcome';
6159 safe_to_delete := TRUE;
6161 RAISE INFO 'rule has different outcome';
6162 safe_to_delete := FALSE;
6165 RAISE EXCEPTION 'rollback the temporary changes';
6167 EXCEPTION WHEN OTHERS THEN
6169 RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
6170 RETURN safe_to_delete;
6173 $func$ LANGUAGE plpgsql;