1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.copy_alert,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 parent_table ALIAS FOR $2;
268 source_table ALIAS FOR $3;
272 column_list TEXT := '';
273 column_count INTEGER := 0;
275 create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
277 SELECT table_schema, table_name, column_name, data_type
278 FROM information_schema.columns
279 WHERE table_schema = migration_schema AND table_name = source_table
281 column_count := column_count + 1;
282 if column_count > 1 then
283 create_sql := create_sql || ', ';
284 column_list := column_list || ', ';
286 create_sql := create_sql || columns.column_name || ' ';
287 if columns.data_type = 'ARRAY' then
288 create_sql := create_sql || 'TEXT[]';
290 create_sql := create_sql || columns.data_type;
292 column_list := column_list || columns.column_name;
294 create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
295 --RAISE INFO 'create_sql = %', create_sql;
297 insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
298 --RAISE INFO 'insert_sql = %', insert_sql;
301 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
303 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
305 migration_schema ALIAS FOR $1;
306 production_tables TEXT[];
308 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
309 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
310 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
311 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
314 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
316 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
318 migration_schema ALIAS FOR $1;
319 production_table ALIAS FOR $2;
320 base_staging_table TEXT;
323 base_staging_table = REPLACE( production_table, '.', '_' );
324 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
325 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
327 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
329 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
331 full_name TEXT := $1;
333 family_name TEXT := '';
334 first_given_name TEXT := '';
335 second_given_name TEXT := '';
339 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
340 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
342 IF suffix = before_comma THEN
346 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
347 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
348 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
350 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
354 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
356 full_name TEXT := $1;
358 family_name TEXT := '';
359 first_given_name TEXT := '';
360 second_given_name TEXT := '';
365 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
366 IF temp ilike '%MR.%' THEN
368 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
370 IF temp ilike '%MRS.%' THEN
372 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
374 IF temp ilike '%MS.%' THEN
376 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
378 IF temp ilike '%DR.%' THEN
380 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
382 IF temp ilike '%JR%' THEN
384 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
386 IF temp ilike '%JR,%' THEN
388 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
390 IF temp ilike '%SR%' THEN
392 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
394 IF temp ilike '%SR,%' THEN
396 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
398 IF temp ~ E'\\sII$' THEN
400 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
402 IF temp ~ E'\\sIII$' THEN
404 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
406 IF temp ~ E'\\sIV$' THEN
408 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
411 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
412 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
413 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
415 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
417 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
419 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
421 full_name TEXT := $1;
423 family_name TEXT := '';
424 first_given_name TEXT := '';
425 second_given_name TEXT := '';
430 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
431 IF temp ilike '%MR.%' THEN
433 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
435 IF temp ilike '%MRS.%' THEN
437 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
439 IF temp ilike '%MS.%' THEN
441 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
443 IF temp ilike '%DR.%' THEN
445 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
447 IF temp ilike '%JR.%' THEN
449 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
451 IF temp ilike '%JR,%' THEN
453 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
455 IF temp ilike '%SR.%' THEN
457 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
459 IF temp ilike '%SR,%' THEN
461 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
463 IF temp like '%III%' THEN
465 temp := REGEXP_REPLACE( temp, E'III', '' );
467 IF temp like '%II%' THEN
469 temp := REGEXP_REPLACE( temp, E'II', '' );
471 IF temp like '%IV%' THEN
473 temp := REGEXP_REPLACE( temp, E'IV', '' );
476 temp := REGEXP_REPLACE( temp, '\(\)', '');
477 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
478 family_name := REGEXP_REPLACE( family_name, ',', '' );
479 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
480 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
481 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
482 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
484 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
486 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
488 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
490 full_name TEXT := $1;
492 family_name TEXT := '';
493 first_given_name TEXT := '';
494 second_given_name TEXT := '';
498 temp := BTRIM(full_name);
499 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
500 --IF temp ~ '^\S{2,}\.' THEN
501 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
502 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
504 --IF temp ~ '\S{2,}\.$' THEN
505 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
506 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
508 IF temp ilike '%MR.%' THEN
510 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
512 IF temp ilike '%MRS.%' THEN
514 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
516 IF temp ilike '%MS.%' THEN
518 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
520 IF temp ilike '%DR.%' THEN
522 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
524 IF temp ilike '%JR.%' THEN
526 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
528 IF temp ilike '%JR,%' THEN
530 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
532 IF temp ilike '%SR.%' THEN
534 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
536 IF temp ilike '%SR,%' THEN
538 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
540 IF temp like '%III%' THEN
542 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
544 IF temp like '%II%' THEN
546 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
550 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
551 temp := BTRIM(REPLACE( temp, family_name, '' ));
552 family_name := REPLACE( family_name, ',', '' );
554 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
555 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
557 first_given_name := temp;
558 second_given_name := '';
561 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
562 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
563 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
564 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
566 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
567 second_given_name := temp;
568 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
572 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
574 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
576 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
578 full_name TEXT := $1;
580 family_name TEXT := '';
581 first_given_name TEXT := '';
582 second_given_name TEXT := '';
586 temp := BTRIM(full_name);
587 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
588 --IF temp ~ '^\S{2,}\.' THEN
589 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
590 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
592 --IF temp ~ '\S{2,}\.$' THEN
593 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
594 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
596 IF temp ilike '%MR.%' THEN
598 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
600 IF temp ilike '%MRS.%' THEN
602 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
604 IF temp ilike '%MS.%' THEN
606 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
608 IF temp ilike '%DR.%' THEN
610 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
612 IF temp ilike '%JR.%' THEN
614 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
616 IF temp ilike '%JR,%' THEN
618 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
620 IF temp ilike '%SR.%' THEN
622 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
624 IF temp ilike '%SR,%' THEN
626 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
628 IF temp like '%III%' THEN
630 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
632 IF temp like '%II%' THEN
634 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
638 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
639 temp := BTRIM(REPLACE( temp, family_name, '' ));
640 family_name := REPLACE( family_name, ',', '' );
642 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
643 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
645 first_given_name := temp;
646 second_given_name := '';
649 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
650 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
651 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
652 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
654 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
655 second_given_name := temp;
656 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
660 family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
661 first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
662 second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
664 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
666 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
670 city_state_zip TEXT := $1;
675 zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
676 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
677 IF city_state_zip ~ ',' THEN
678 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
679 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
681 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
682 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
683 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
685 IF city_state_zip ~ E'^\\S+$' THEN
686 city := city_state_zip;
689 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
690 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
694 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
696 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
698 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
699 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
701 fullstring TEXT := $1;
711 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
712 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
715 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
717 IF fullstring ~ ',' THEN
718 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
719 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
721 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
722 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
723 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
725 IF fullstring ~ E'^\\S+$' THEN
726 scratch1 := fullstring;
729 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
730 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
735 IF scratch1 ~ '[\$]' THEN
736 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
737 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
739 IF scratch1 ~ '\s' THEN
740 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
741 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
748 IF scratch2 ~ '^\d' THEN
749 address1 := scratch2;
752 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
753 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
757 TRIM(BOTH ' ' FROM address1)
758 ,TRIM(BOTH ' ' FROM address2)
759 ,TRIM(BOTH ' ' FROM city)
760 ,TRIM(BOTH ' ' FROM state)
761 ,TRIM(BOTH ' ' FROM zip)
764 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
766 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
769 use Geo::StreetAddress::US;
771 my $a = Geo::StreetAddress::US->parse_location($address);
774 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
775 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
780 $$ LANGUAGE PLPERLU STABLE;
782 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
783 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
784 INSERT INTO migration_tools.usps_suffixes VALUES
817 ('BOULEVARD','BLVD'),
921 ('EXPRESSWAY','EXPY'),
926 ('EXTENSIONS','EXTS'),
1024 ('ISLANDS','SLNDS'),
1037 ('JUNCTIONS','JCTS'),
1097 ('MOTORWAY','MTWY'),
1099 ('MOUNTAINS','MTNS'),
1113 ('OVERPASS','OPAS'),
1118 ('PARKWAYS','PKWY'),
1224 ('STRAVENUE','STRA'),
1244 ('THROUGHWAY','TRWY'),
1251 ('TRAFFICWAY','TRFY'),
1270 ('TURNPIKE','TPKE'),
1272 ('UNDERPASS','UPAS'),
1286 ('VILLAGES','VLGS'),
1317 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1318 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1323 --RAISE INFO 'suffix = %', suffix;
1324 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1325 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1329 $$ LANGUAGE PLPGSQL STRICT STABLE;
1331 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1334 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1338 $$ LANGUAGE PLPGSQL STRICT STABLE;
1340 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1344 IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
1345 IF o::BIGINT < t THEN
1352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1354 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1356 migration_schema ALIAS FOR $1;
1360 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1365 $$ LANGUAGE PLPGSQL STRICT STABLE;
1367 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1369 migration_schema ALIAS FOR $1;
1373 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1378 $$ LANGUAGE PLPGSQL STRICT STABLE;
1380 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1382 migration_schema ALIAS FOR $1;
1386 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1391 $$ LANGUAGE PLPGSQL STRICT STABLE;
1393 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1395 migration_schema ALIAS FOR $1;
1399 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1404 $$ LANGUAGE PLPGSQL STRICT STABLE;
1406 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1408 migration_schema ALIAS FOR $1;
1410 patron_table ALIAS FOR $2;
1411 default_patron_profile ALIAS FOR $3;
1414 sql_where1 TEXT := '';
1415 sql_where2 TEXT := '';
1416 sql_where3 TEXT := '';
1419 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1421 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1423 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1424 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1425 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1426 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1427 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1428 --RAISE INFO 'sql = %', sql;
1429 PERFORM migration_tools.exec( $1, sql );
1431 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1433 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1435 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1438 $$ LANGUAGE PLPGSQL STRICT STABLE;
1440 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1442 migration_schema ALIAS FOR $1;
1444 item_table ALIAS FOR $2;
1447 sql_where1 TEXT := '';
1448 sql_where2 TEXT := '';
1449 sql_where3 TEXT := '';
1452 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1454 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1456 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
1457 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1458 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1459 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1460 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1461 --RAISE INFO 'sql = %', sql;
1462 PERFORM migration_tools.exec( $1, sql );
1465 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1467 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1470 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1472 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1474 migration_schema ALIAS FOR $1;
1475 base_copy_location_map TEXT;
1476 item_table ALIAS FOR $2;
1479 sql_where1 TEXT := '';
1480 sql_where2 TEXT := '';
1481 sql_where3 TEXT := '';
1484 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1486 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1488 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1489 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1490 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1491 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1492 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1493 --RAISE INFO 'sql = %', sql;
1494 PERFORM migration_tools.exec( $1, sql );
1497 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1499 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1502 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1504 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
1505 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1507 migration_schema ALIAS FOR $1;
1509 circ_table ALIAS FOR $2;
1510 item_table ALIAS FOR $3;
1511 patron_table ALIAS FOR $4;
1514 sql_where1 TEXT := '';
1515 sql_where2 TEXT := '';
1516 sql_where3 TEXT := '';
1517 sql_where4 TEXT := '';
1520 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1522 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1524 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
1525 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
1526 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
1527 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
1528 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
1529 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
1530 --RAISE INFO 'sql = %', sql;
1531 PERFORM migration_tools.exec( $1, sql );
1534 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1536 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1539 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1542 -- $barcode source barcode
1543 -- $prefix prefix to add to barcode, NULL = add no prefix
1544 -- $maxlen maximum length of barcode; default to 14 if left NULL
1545 -- $pad padding string to apply to left of source barcode before adding
1546 -- prefix and suffix; set to NULL or '' if no padding is desired
1547 -- $suffix suffix to add to barcode, NULL = add no suffix
1549 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1550 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1552 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1553 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1556 return unless defined $barcode;
1558 $prefix = '' unless defined $prefix;
1560 $pad = '0' unless defined $pad;
1561 $suffix = '' unless defined $suffix;
1563 # bail out if adding prefix and suffix would bring new barcode over max length
1564 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1566 my $new_barcode = $barcode;
1568 my $pad_length = $maxlen - length($prefix) - length($suffix);
1569 if (length($barcode) < $pad_length) {
1570 # assuming we always want padding on the left
1571 # also assuming that it is possible to have the pad string be longer than 1 character
1572 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1576 # bail out if adding prefix and suffix would bring new barcode over max length
1577 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1579 return "$prefix$new_barcode$suffix";
1580 $$ LANGUAGE PLPERLU STABLE;
1582 -- remove previous version of this function
1583 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1585 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1587 attempt_value ALIAS FOR $1;
1588 datatype ALIAS FOR $2;
1590 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1591 RETURN attempt_value;
1593 WHEN OTHERS THEN RETURN NULL;
1595 $$ LANGUAGE PLPGSQL STRICT STABLE;
1597 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1599 attempt_value ALIAS FOR $1;
1600 fail_value ALIAS FOR $2;
1604 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1611 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1616 $$ LANGUAGE PLPGSQL STRICT STABLE;
1618 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1620 attempt_value ALIAS FOR $1;
1621 fail_value ALIAS FOR $2;
1625 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1632 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1637 $$ LANGUAGE PLPGSQL STRICT STABLE;
1639 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1641 attempt_value ALIAS FOR $1;
1642 fail_value ALIAS FOR $2;
1646 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1653 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1658 $$ LANGUAGE PLPGSQL STRICT STABLE;
1660 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1662 attempt_value ALIAS FOR $1;
1663 fail_value ALIAS FOR $2;
1666 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1671 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1676 $$ LANGUAGE PLPGSQL STRICT STABLE;
1678 CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
1680 -- Expects the following table/columns:
1682 -- export_biblio_tsv:
1684 -- l_create_dt | 2007-03-07 09:03:09
1685 -- l_last_change_dt | 2015-01-23 11:18:54
1686 -- l_last_change_userid | 2
1687 -- l_material_cd | 10
1688 -- l_collection_cd | 13
1689 -- l_call_nmbr1 | Canada
1690 -- l_call_nmbr2 | ON
1691 -- l_call_nmbr3 | Ottawa 18
1692 -- l_title | Art and the courts : France ad England
1693 -- l_title_remainder | from 1259-1328
1694 -- l_responsibility_stmt |
1695 -- l_author | National Gallery of Canada
1702 -- l_flag_attention | 0
1704 -- export_biblio_field_tsv:
1710 -- l_subfield_cd | a
1711 -- l_field_data | Brieger, Peter Henry
1713 -- Map export_biblio_tsv as follows:
1714 -- l_call_nmbr? -> 099a
1717 -- l_title_remainder -> 245b
1718 -- l_responsibility_stmt -> 245c
1723 migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data)
1726 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
1727 array_agg(l_ind1_cd) as "ind1",
1728 array_agg(l_ind2_cd) as "ind2",
1729 array_agg(l_field_data) as "data"
1737 from export_biblio_field_tsv
1738 where l_bibid = x_bibid
1742 'a' as "l_subfield_cd",
1746 nullif(btrim(l_call_nmbr1),''),
1747 nullif(btrim(l_call_nmbr2),''),
1748 nullif(btrim(l_call_nmbr3),'')
1750 from export_biblio_tsv
1751 where l_bibid = x_bibid
1755 'a' as "l_subfield_cd",
1758 l_author as "l_field_data"
1759 from export_biblio_tsv
1760 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
1764 'a' as "l_subfield_cd",
1767 l_title as "l_field_data"
1768 from export_biblio_tsv
1769 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
1773 'b' as "l_subfield_cd",
1776 l_title_remainder as "l_field_data"
1777 from export_biblio_tsv
1778 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
1782 'a' as "l_subfield_cd",
1785 l_topic1 as "l_field_data"
1786 from export_biblio_tsv
1787 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
1791 'a' as "l_subfield_cd",
1794 l_topic2 as "l_field_data"
1795 from export_biblio_tsv
1796 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
1800 'a' as "l_subfield_cd",
1803 l_topic3 as "l_field_data"
1804 from export_biblio_tsv
1805 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
1809 'a' as "l_subfield_cd",
1812 l_topic4 as "l_field_data"
1813 from export_biblio_tsv
1814 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
1818 'a' as "l_subfield_cd",
1821 l_topic5 as "l_field_data"
1822 from export_biblio_tsv
1823 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
1827 '' as "l_subfield_cd",
1830 l_bibid as "l_field_data"
1831 from export_biblio_tsv
1832 where l_bibid = x_bibid
1837 $func$ LANGUAGE plpgsql;
1839 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1841 attempt_value ALIAS FOR $1;
1842 fail_value ALIAS FOR $2;
1843 output NUMERIC(8,2);
1846 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1853 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1858 $$ LANGUAGE PLPGSQL STRICT STABLE;
1860 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1862 attempt_value ALIAS FOR $1;
1863 fail_value ALIAS FOR $2;
1864 output NUMERIC(6,2);
1867 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1874 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1879 $$ LANGUAGE PLPGSQL STRICT STABLE;
1881 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1883 attempt_value ALIAS FOR $1;
1884 fail_value ALIAS FOR $2;
1885 output NUMERIC(8,2);
1887 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1888 RAISE EXCEPTION 'too many digits';
1891 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;'
1898 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1903 $$ LANGUAGE PLPGSQL STRICT STABLE;
1905 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1907 attempt_value ALIAS FOR $1;
1908 fail_value ALIAS FOR $2;
1909 output NUMERIC(6,2);
1911 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1912 RAISE EXCEPTION 'too many digits';
1915 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;'
1922 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1927 $$ LANGUAGE PLPGSQL STRICT STABLE;
1929 -- add_codabar_checkdigit
1930 -- $barcode source barcode
1932 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1933 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1934 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1935 -- input string does not meet those requirements, it is returned unchanged.
1937 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1938 my $barcode = shift;
1940 return $barcode if $barcode !~ /^\d{13,14}$/;
1941 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1942 my @digits = split //, $barcode;
1944 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1945 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1946 my $remainder = $total % 10;
1947 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1948 return $barcode . $checkdigit;
1949 $$ LANGUAGE PLPERLU STRICT STABLE;
1951 -- add_code39mod43_checkdigit
1952 -- $barcode source barcode
1954 -- If the source string is 13 or 14 characters long and contains only valid
1955 -- Code 39 mod 43 characters, adds or replaces the 14th
1956 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1957 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1958 -- input string does not meet those requirements, it is returned unchanged.
1960 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1961 my $barcode = shift;
1963 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1964 $barcode = substr($barcode, 0, 13); # ignore 14th character
1966 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1967 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1970 $total += $nums{$_} foreach split(//, $barcode);
1971 my $remainder = $total % 43;
1972 my $checkdigit = $valid_chars[$remainder];
1973 return $barcode . $checkdigit;
1974 $$ LANGUAGE PLPERLU STRICT STABLE;
1976 -- add_mod16_checkdigit
1977 -- $barcode source barcode
1979 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1981 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1982 my $barcode = shift;
1984 my @digits = split //, $barcode;
1986 foreach $digit (@digits) {
1987 if ($digit =~ /[0-9]/) { $total += $digit;
1988 } elsif ($digit eq '-') { $total += 10;
1989 } elsif ($digit eq '$') { $total += 11;
1990 } elsif ($digit eq ':') { $total += 12;
1991 } elsif ($digit eq '/') { $total += 13;
1992 } elsif ($digit eq '.') { $total += 14;
1993 } elsif ($digit eq '+') { $total += 15;
1994 } elsif ($digit eq 'A') { $total += 16;
1995 } elsif ($digit eq 'B') { $total += 17;
1996 } elsif ($digit eq 'C') { $total += 18;
1997 } elsif ($digit eq 'D') { $total += 19;
1998 } else { die "invalid digit <$digit>";
2001 my $remainder = $total % 16;
2002 my $difference = 16 - $remainder;
2004 if ($difference < 10) { $checkdigit = $difference;
2005 } elsif ($difference == 10) { $checkdigit = '-';
2006 } elsif ($difference == 11) { $checkdigit = '$';
2007 } elsif ($difference == 12) { $checkdigit = ':';
2008 } elsif ($difference == 13) { $checkdigit = '/';
2009 } elsif ($difference == 14) { $checkdigit = '.';
2010 } elsif ($difference == 15) { $checkdigit = '+';
2011 } else { die "error calculating checkdigit";
2014 return $barcode . $checkdigit;
2015 $$ LANGUAGE PLPERLU STRICT STABLE;
2017 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
2020 areacode TEXT := $2;
2023 n_digits INTEGER := 0;
2026 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
2027 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
2028 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
2029 IF n_digits = 7 AND areacode <> '' THEN
2030 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
2031 output := (areacode || '-' || temp);
2038 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2040 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2041 my ($marcxml, $pos, $value) = @_;
2044 use MARC::File::XML;
2048 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2049 my $leader = $marc->leader();
2050 substr($leader, $pos, 1) = $value;
2051 $marc->leader($leader);
2052 $xml = $marc->as_xml_record;
2053 $xml =~ s/^<\?.+?\?>$//mo;
2055 $xml =~ s/>\s+</></sgo;
2058 $$ LANGUAGE PLPERLU STABLE;
2060 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
2061 my ($marcxml, $pos, $value) = @_;
2064 use MARC::File::XML;
2068 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2069 my $f008 = $marc->field('008');
2072 my $field = $f008->data();
2073 substr($field, $pos, 1) = $value;
2074 $f008->update($field);
2075 $xml = $marc->as_xml_record;
2076 $xml =~ s/^<\?.+?\?>$//mo;
2078 $xml =~ s/>\s+</></sgo;
2082 $$ LANGUAGE PLPERLU STABLE;
2085 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
2087 profile ALIAS FOR $1;
2089 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
2091 $$ LANGUAGE PLPGSQL STRICT STABLE;
2094 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
2096 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
2098 $$ LANGUAGE PLPGSQL STRICT STABLE;
2101 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
2103 my ($marcxml, $tags) = @_;
2106 use MARC::File::XML;
2111 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2112 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
2114 my @incumbents = ();
2116 foreach my $field ( $marc->fields() ) {
2117 push @incumbents, $field->as_formatted();
2120 foreach $field ( $to_insert->fields() ) {
2121 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
2122 $marc->insert_fields_ordered( ($field) );
2126 $xml = $marc->as_xml_record;
2127 $xml =~ s/^<\?.+?\?>$//mo;
2129 $xml =~ s/>\s+</></sgo;
2134 $$ LANGUAGE PLPERLU STABLE;
2136 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
2140 -- First make sure the circ matrix is loaded and the circulations
2141 -- have been staged to the extent possible (but at the very least
2142 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2143 -- circ modifiers must also be in place.
2145 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2153 this_duration_rule INT;
2155 this_max_fine_rule INT;
2156 rcd config.rule_circ_duration%ROWTYPE;
2157 rrf config.rule_recurring_fine%ROWTYPE;
2158 rmf config.rule_max_fine%ROWTYPE;
2165 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2167 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2169 -- Fetch the correct rules for this circulation
2176 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2179 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2180 INTO circ_lib, target_copy, usr, is_renewal ;
2182 INTO this_duration_rule,
2186 recurring_fine_rule,
2188 FROM action.item_user_circ_test(
2194 SELECT INTO rcd * FROM config.rule_circ_duration
2195 WHERE id = this_duration_rule;
2196 SELECT INTO rrf * FROM config.rule_recurring_fine
2197 WHERE id = this_fine_rule;
2198 SELECT INTO rmf * FROM config.rule_max_fine
2199 WHERE id = this_max_fine_rule;
2201 -- Apply the rules to this circulation
2202 EXECUTE ('UPDATE ' || tablename || ' c
2204 duration_rule = rcd.name,
2205 recurring_fine_rule = rrf.name,
2206 max_fine_rule = rmf.name,
2207 duration = rcd.normal,
2208 recurring_fine = rrf.normal,
2211 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2214 renewal_remaining = rcd.max_renewals
2216 config.rule_circ_duration rcd,
2217 config.rule_recurring_fine rrf,
2218 config.rule_max_fine rmf,
2221 rcd.id = ' || this_duration_rule || ' AND
2222 rrf.id = ' || this_fine_rule || ' AND
2223 rmf.id = ' || this_max_fine_rule || ' AND
2224 ac.id = c.target_copy AND
2225 c.id = ' || circ || ';');
2227 -- Keep track of where we are in the process
2229 IF (n % 100 = 0) THEN
2230 RAISE INFO '%', n || ' of ' || n_circs
2231 || ' (' || (100*n/n_circs) || '%) circs updated.';
2239 $$ LANGUAGE plpgsql;
2241 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2245 -- First make sure the circ matrix is loaded and the circulations
2246 -- have been staged to the extent possible (but at the very least
2247 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2248 -- circ modifiers must also be in place.
2250 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2258 this_duration_rule INT;
2260 this_max_fine_rule INT;
2261 rcd config.rule_circ_duration%ROWTYPE;
2262 rrf config.rule_recurring_fine%ROWTYPE;
2263 rmf config.rule_max_fine%ROWTYPE;
2270 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2272 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2274 -- Fetch the correct rules for this circulation
2281 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2284 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2285 INTO circ_lib, target_copy, usr, is_renewal ;
2287 INTO this_duration_rule,
2293 FROM action.find_circ_matrix_matchpoint(
2299 SELECT INTO rcd * FROM config.rule_circ_duration
2300 WHERE id = this_duration_rule;
2301 SELECT INTO rrf * FROM config.rule_recurring_fine
2302 WHERE id = this_fine_rule;
2303 SELECT INTO rmf * FROM config.rule_max_fine
2304 WHERE id = this_max_fine_rule;
2306 -- Apply the rules to this circulation
2307 EXECUTE ('UPDATE ' || tablename || ' c
2309 duration_rule = rcd.name,
2310 recuring_fine_rule = rrf.name,
2311 max_fine_rule = rmf.name,
2312 duration = rcd.normal,
2313 recuring_fine = rrf.normal,
2316 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2319 renewal_remaining = rcd.max_renewals
2321 config.rule_circ_duration rcd,
2322 config.rule_recuring_fine rrf,
2323 config.rule_max_fine rmf,
2326 rcd.id = ' || this_duration_rule || ' AND
2327 rrf.id = ' || this_fine_rule || ' AND
2328 rmf.id = ' || this_max_fine_rule || ' AND
2329 ac.id = c.target_copy AND
2330 c.id = ' || circ || ';');
2332 -- Keep track of where we are in the process
2334 IF (n % 100 = 0) THEN
2335 RAISE INFO '%', n || ' of ' || n_circs
2336 || ' (' || (100*n/n_circs) || '%) circs updated.';
2344 $$ LANGUAGE plpgsql;
2346 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2350 -- First make sure the circ matrix is loaded and the circulations
2351 -- have been staged to the extent possible (but at the very least
2352 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2353 -- circ modifiers must also be in place.
2355 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2363 this_duration_rule INT;
2365 this_max_fine_rule INT;
2366 rcd config.rule_circ_duration%ROWTYPE;
2367 rrf config.rule_recurring_fine%ROWTYPE;
2368 rmf config.rule_max_fine%ROWTYPE;
2375 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2377 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2379 -- Fetch the correct rules for this circulation
2386 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2389 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2390 INTO circ_lib, target_copy, usr, is_renewal ;
2392 INTO this_duration_rule,
2395 (matchpoint).duration_rule,
2396 (matchpoint).recurring_fine_rule,
2397 (matchpoint).max_fine_rule
2398 FROM action.find_circ_matrix_matchpoint(
2404 SELECT INTO rcd * FROM config.rule_circ_duration
2405 WHERE id = this_duration_rule;
2406 SELECT INTO rrf * FROM config.rule_recurring_fine
2407 WHERE id = this_fine_rule;
2408 SELECT INTO rmf * FROM config.rule_max_fine
2409 WHERE id = this_max_fine_rule;
2411 -- Apply the rules to this circulation
2412 EXECUTE ('UPDATE ' || tablename || ' c
2414 duration_rule = rcd.name,
2415 recurring_fine_rule = rrf.name,
2416 max_fine_rule = rmf.name,
2417 duration = rcd.normal,
2418 recurring_fine = rrf.normal,
2421 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2424 renewal_remaining = rcd.max_renewals,
2425 grace_period = rrf.grace_period
2427 config.rule_circ_duration rcd,
2428 config.rule_recurring_fine rrf,
2429 config.rule_max_fine rmf,
2432 rcd.id = ' || this_duration_rule || ' AND
2433 rrf.id = ' || this_fine_rule || ' AND
2434 rmf.id = ' || this_max_fine_rule || ' AND
2435 ac.id = c.target_copy AND
2436 c.id = ' || circ || ';');
2438 -- Keep track of where we are in the process
2440 IF (n % 100 = 0) THEN
2441 RAISE INFO '%', n || ' of ' || n_circs
2442 || ' (' || (100*n/n_circs) || '%) circs updated.';
2450 $$ LANGUAGE plpgsql;
2452 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2453 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2454 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2457 charge_lost_on_zero BOOLEAN;
2460 default_price NUMERIC;
2461 working_price NUMERIC;
2465 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2466 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2468 SELECT INTO charge_lost_on_zero value
2469 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2471 SELECT INTO min_price value
2472 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2474 SELECT INTO max_price value
2475 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2477 SELECT INTO default_price value
2478 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2480 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2482 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2483 working_price := default_price;
2486 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2487 working_price := max_price;
2490 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2491 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2492 working_price := min_price;
2496 RETURN working_price;
2500 $$ LANGUAGE plpgsql;
2502 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2506 -- First make sure the circ matrix is loaded and the circulations
2507 -- have been staged to the extent possible (but at the very least
2508 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2509 -- circ modifiers must also be in place.
2511 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2519 this_duration_rule INT;
2521 this_max_fine_rule INT;
2522 rcd config.rule_circ_duration%ROWTYPE;
2523 rrf config.rule_recurring_fine%ROWTYPE;
2524 rmf config.rule_max_fine%ROWTYPE;
2530 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2532 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2534 -- Fetch the correct rules for this circulation
2541 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2544 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2545 INTO circ_lib, target_copy, usr, is_renewal ;
2547 INTO this_duration_rule,
2550 (matchpoint).duration_rule,
2551 (matchpoint).recurring_fine_rule,
2552 (matchpoint).max_fine_rule
2553 FROM action.find_circ_matrix_matchpoint(
2559 SELECT INTO rcd * FROM config.rule_circ_duration
2560 WHERE id = this_duration_rule;
2561 SELECT INTO rrf * FROM config.rule_recurring_fine
2562 WHERE id = this_fine_rule;
2563 SELECT INTO rmf * FROM config.rule_max_fine
2564 WHERE id = this_max_fine_rule;
2566 -- Apply the rules to this circulation
2567 EXECUTE ('UPDATE ' || tablename || ' c
2569 duration_rule = rcd.name,
2570 recurring_fine_rule = rrf.name,
2571 max_fine_rule = rmf.name,
2572 duration = rcd.normal,
2573 recurring_fine = rrf.normal,
2576 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2579 renewal_remaining = rcd.max_renewals,
2580 grace_period = rrf.grace_period
2582 config.rule_circ_duration rcd,
2583 config.rule_recurring_fine rrf,
2584 config.rule_max_fine rmf,
2587 rcd.id = ' || this_duration_rule || ' AND
2588 rrf.id = ' || this_fine_rule || ' AND
2589 rmf.id = ' || this_max_fine_rule || ' AND
2590 ac.id = c.target_copy AND
2591 c.id = ' || circ || ';');
2593 -- Keep track of where we are in the process
2595 IF (n % 100 = 0) THEN
2596 RAISE INFO '%', n || ' of ' || n_circs
2597 || ' (' || (100*n/n_circs) || '%) circs updated.';
2605 $$ LANGUAGE plpgsql;
2610 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2612 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2613 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2615 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2616 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2619 c TEXT := schemaname || '.asset_copy_legacy';
2620 sc TEXT := schemaname || '.asset_stat_cat';
2621 sce TEXT := schemaname || '.asset_stat_cat_entry';
2622 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2628 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2630 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2632 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2633 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2634 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2641 $$ LANGUAGE plpgsql;
2643 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2645 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2646 -- This will assign standing penalties as needed.
2654 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2656 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2658 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2668 $$ LANGUAGE plpgsql;
2671 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2674 INSERT INTO metabib.metarecord (fingerprint, master_record)
2675 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2676 FROM biblio.record_entry b
2678 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)
2679 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2680 ORDER BY b.fingerprint, b.quality DESC;
2681 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2683 FROM biblio.record_entry r
2684 JOIN metabib.metarecord m USING (fingerprint)
2685 WHERE NOT r.deleted;
2688 $$ LANGUAGE plpgsql;
2691 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2694 INSERT INTO metabib.metarecord (fingerprint, master_record)
2695 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2696 FROM biblio.record_entry b
2698 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)
2699 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2700 ORDER BY b.fingerprint, b.quality DESC;
2701 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2703 FROM biblio.record_entry r
2704 JOIN metabib.metarecord m USING (fingerprint)
2706 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);
2709 $$ LANGUAGE plpgsql;
2712 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2714 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2715 -- Then SELECT migration_tools.create_cards('m_foo');
2718 u TEXT := schemaname || '.actor_usr_legacy';
2719 c TEXT := schemaname || '.actor_card';
2723 EXECUTE ('DELETE FROM ' || c || ';');
2724 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2725 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2731 $$ LANGUAGE plpgsql;
2734 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2736 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2738 my ($marcxml, $shortname) = @_;
2741 use MARC::File::XML;
2746 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2748 foreach my $field ( $marc->field('856') ) {
2749 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2750 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2751 $field->add_subfields( '9' => $shortname );
2752 $field->update( ind2 => '0');
2756 $xml = $marc->as_xml_record;
2757 $xml =~ s/^<\?.+?\?>$//mo;
2759 $xml =~ s/>\s+</></sgo;
2764 $$ LANGUAGE PLPERLU STABLE;
2766 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2768 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2770 my ($marcxml, $shortname) = @_;
2773 use MARC::File::XML;
2778 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2780 foreach my $field ( $marc->field('856') ) {
2781 if ( ! $field->as_string('9') ) {
2782 $field->add_subfields( '9' => $shortname );
2786 $xml = $marc->as_xml_record;
2787 $xml =~ s/^<\?.+?\?>$//mo;
2789 $xml =~ s/>\s+</></sgo;
2794 $$ LANGUAGE PLPERLU STABLE;
2797 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2809 -- Bail out if asked to change the label to ##URI##
2810 IF new_label = '##URI##' THEN
2814 -- Gather information
2815 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2816 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2817 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2819 -- Bail out if the label already is ##URI##
2820 IF old_label = '##URI##' THEN
2824 -- Bail out if the call number label is already correct
2825 IF new_volume = old_volume THEN
2829 -- Check whether we already have a destination volume available
2830 SELECT id INTO new_volume FROM asset.call_number
2833 owning_lib = owner AND
2834 label = new_label AND
2837 -- Create destination volume if needed
2839 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2840 VALUES (1, 1, bib, owner, new_label, cn_class);
2841 SELECT id INTO new_volume FROM asset.call_number
2844 owning_lib = owner AND
2845 label = new_label AND
2849 -- Move copy to destination
2850 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2852 -- Delete source volume if it is now empty
2853 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2855 DELETE FROM asset.call_number WHERE id = old_volume;
2860 $$ LANGUAGE plpgsql;
2862 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2867 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2871 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2872 $zipdata{$zip} = [$city, $state, $county];
2875 if (defined $zipdata{$input}) {
2876 my ($city, $state, $county) = @{$zipdata{$input}};
2877 return [$city, $state, $county];
2878 } elsif (defined $zipdata{substr $input, 0, 5}) {
2879 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2880 return [$city, $state, $county];
2882 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2885 $$ LANGUAGE PLPERLU STABLE;
2887 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2894 errors_found BOOLEAN;
2896 parent_shortname TEXT;
2902 type_parent_depth INT;
2907 errors_found := FALSE;
2909 -- Checking actor.org_unit_type
2911 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2913 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2914 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2916 IF type_parent IS NOT NULL THEN
2918 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2920 IF type_depth - type_parent_depth <> 1 THEN
2921 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2922 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2923 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2924 ou_type_name, type_depth, parent_type, type_parent_depth;
2925 errors_found := TRUE;
2933 -- Checking actor.org_unit
2935 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2937 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2938 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;
2939 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;
2940 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2941 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2942 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;
2943 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;
2945 IF ou_parent IS NOT NULL THEN
2947 IF (org_unit_depth - parent_depth <> 1) OR (
2948 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2950 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2951 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2952 errors_found := TRUE;
2959 IF NOT errors_found THEN
2960 RAISE INFO 'No errors found.';
2967 $$ LANGUAGE plpgsql;
2970 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2974 DELETE FROM asset.opac_visible_copies;
2976 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2978 cp.id, cp.circ_lib, cn.record
2981 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2982 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2983 JOIN asset.copy_location cl ON (cp.location = cl.id)
2984 JOIN config.copy_status cs ON (cp.status = cs.id)
2985 JOIN biblio.record_entry b ON (cn.record = b.id)
2994 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2998 $$ LANGUAGE plpgsql;
3001 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
3007 old_owning_lib INTEGER;
3013 -- Gather information
3014 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
3015 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
3016 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
3018 -- Bail out if the new_owning_lib is not the ID of an org_unit
3019 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
3021 '% is not a valid actor.org_unit ID; no change made.',
3026 -- Bail out discreetly if the owning_lib is already correct
3027 IF new_owning_lib = old_owning_lib THEN
3031 -- Check whether we already have a destination volume available
3032 SELECT id INTO new_volume FROM asset.call_number
3035 owning_lib = new_owning_lib AND
3036 label = old_label AND
3039 -- Create destination volume if needed
3041 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
3042 VALUES (1, 1, bib, new_owning_lib, old_label);
3043 SELECT id INTO new_volume FROM asset.call_number
3046 owning_lib = new_owning_lib AND
3047 label = old_label AND
3051 -- Move copy to destination
3052 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
3054 -- Delete source volume if it is now empty
3055 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
3057 DELETE FROM asset.call_number WHERE id = old_volume;
3062 $$ LANGUAGE plpgsql;
3065 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
3067 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
3070 new_owning_lib INTEGER;
3074 -- Parse the new_owner as an org unit ID or shortname
3075 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
3076 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
3077 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3078 ELSIF new_owner ~ E'^[0-9]+$' THEN
3079 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
3082 E'You don\'t need to put the actor.org_unit ID in quotes; '
3083 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
3084 new_owning_lib := new_owner::INTEGER;
3085 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3089 '% is not a valid actor.org_unit shortname or ID; no change made.',
3096 $$ LANGUAGE plpgsql;
3098 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
3101 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3104 MARC::Charset->assume_unicode(1);
3109 my $r = MARC::Record->new_from_xml( $xml );
3110 my $output_xml = $r->as_xml_record();
3118 $func$ LANGUAGE PLPERLU;
3119 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
3121 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3123 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3124 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3125 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
3126 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3127 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3128 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
3129 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3130 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3131 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
3132 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3133 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3134 $$) TO '$$ || dir || $$/asset_copy_location'$$;
3135 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3136 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3137 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3138 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3139 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3140 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
3141 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3142 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3143 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
3144 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
3145 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
3146 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
3147 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
3148 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
3150 $FUNC$ LANGUAGE PLPGSQL;
3152 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3154 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
3155 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
3156 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
3157 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
3158 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3159 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
3160 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
3162 -- import any new circ rules
3163 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3164 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3165 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3166 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3168 -- and permission groups
3169 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3172 $FUNC$ LANGUAGE PLPGSQL;
3175 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$
3184 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3185 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3186 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
3187 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3188 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3189 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3190 FOR name IN EXECUTE loopq LOOP
3191 EXECUTE existsq INTO ct USING name;
3193 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3194 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3195 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3196 EXECUTE copyst USING name;
3200 $FUNC$ LANGUAGE PLPGSQL;
3202 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3209 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3210 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;
3211 FOR id IN EXECUTE loopq USING delimiter LOOP
3212 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3213 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3214 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3215 EXECUTE splitst USING id, delimiter;
3218 $FUNC$ LANGUAGE PLPGSQL;
3220 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3226 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3229 MARC::Charset->assume_unicode(1);
3231 my $target_xml = shift;
3232 my $source_xml = shift;
3238 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3242 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3247 my $source_id = $source->subfield('901', 'c');
3248 $source_id = $source->subfield('903', 'a') unless $source_id;
3249 my $target_id = $target->subfield('901', 'c');
3250 $target_id = $target->subfield('903', 'a') unless $target_id;
3252 my %existing_fields;
3253 foreach my $tag (@$tags) {
3254 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3255 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3256 $target->insert_fields_ordered(map { $_->clone() } @to_add);
3258 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3262 my $xml = $target->as_xml_record;
3263 $xml =~ s/^<\?.+?\?>$//mo;
3265 $xml =~ s/>\s+</></sgo;
3269 $func$ LANGUAGE PLPERLU;
3270 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.';
3272 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3278 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3281 my $in_tags = shift;
3282 my $in_values = shift;
3284 # hack-and-slash parsing of array-passed-as-string;
3285 # this can go away once everybody is running Postgres 9.1+
3286 my $csv = Text::CSV->new({binary => 1});
3289 my $status = $csv->parse($in_tags);
3290 my $tags = [ $csv->fields() ];
3291 $in_values =~ s/^{//;
3292 $in_values =~ s/}$//;
3293 $status = $csv->parse($in_values);
3294 my $values = [ $csv->fields() ];
3296 my $marc = MARC::Record->new();
3298 $marc->leader('00000nam a22000007 4500');
3299 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3301 foreach my $i (0..$#$tags) {
3303 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3306 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3307 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3309 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3313 my $xml = $marc->as_xml_record;
3314 $xml =~ s/^<\?.+?\?>$//mo;
3316 $xml =~ s/>\s+</></sgo;
3320 $func$ LANGUAGE PLPERLU;
3321 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3322 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3323 The second argument is an array of text containing the values to plug into each field.
3324 If the value for a given field is NULL or the empty string, it is not inserted.
3327 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3333 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3336 my $in_tags = shift;
3337 my $in_ind1 = shift;
3338 my $in_ind2 = shift;
3339 my $in_values = shift;
3341 # hack-and-slash parsing of array-passed-as-string;
3342 # this can go away once everybody is running Postgres 9.1+
3343 my $csv = Text::CSV->new({binary => 1});
3346 my $status = $csv->parse($in_tags);
3347 my $tags = [ $csv->fields() ];
3350 $status = $csv->parse($in_ind1);
3351 my $ind1s = [ $csv->fields() ];
3354 $status = $csv->parse($in_ind2);
3355 my $ind2s = [ $csv->fields() ];
3356 $in_values =~ s/^{//;
3357 $in_values =~ s/}$//;
3358 $status = $csv->parse($in_values);
3359 my $values = [ $csv->fields() ];
3361 my $marc = MARC::Record->new();
3363 $marc->leader('00000nam a22000007 4500');
3364 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3366 foreach my $i (0..$#$tags) {
3368 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3371 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3372 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3374 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3378 my $xml = $marc->as_xml_record;
3379 $xml =~ s/^<\?.+?\?>$//mo;
3381 $xml =~ s/>\s+</></sgo;
3385 $func$ LANGUAGE PLPERLU;
3386 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.
3387 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3388 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3389 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3390 The fourth argument is an array of text containing the values to plug into each field.
3391 If the value for a given field is NULL or the empty string, it is not inserted.
3394 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3396 my ($marcxml, $tag, $pos, $value) = @_;
3399 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3403 MARC::Charset->assume_unicode(1);
3405 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3406 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3407 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3408 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3412 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3414 foreach my $field ($marc->field($tag)) {
3415 $field->update("ind$pos" => $value);
3417 $xml = $marc->as_xml_record;
3418 $xml =~ s/^<\?.+?\?>$//mo;
3420 $xml =~ s/>\s+</></sgo;
3424 $func$ LANGUAGE PLPERLU;
3426 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3427 The first argument is a MARCXML string.
3428 The second argument is a MARC tag.
3429 The third argument is the indicator position, either 1 or 2.
3430 The fourth argument is the character to set the indicator value to.
3431 All occurences of the specified field will be changed.
3432 The function returns the revised MARCXML string.$$;
3434 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3439 first_name TEXT DEFAULT '',
3440 last_name TEXT DEFAULT ''
3441 ) RETURNS VOID AS $func$
3443 RAISE NOTICE '%', org ;
3444 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3445 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3446 FROM actor.org_unit aou, permission.grp_tree pgt
3447 WHERE aou.shortname = org
3448 AND pgt.name = perm_group;
3453 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3454 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3456 target_event_def ALIAS FOR $1;
3459 DROP TABLE IF EXISTS new_atevdefs;
3460 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3461 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3462 INSERT INTO action_trigger.event_definition (
3483 ,name || ' (clone of '||target_event_def||')'
3499 action_trigger.event_definition
3501 id = target_event_def
3503 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3504 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3505 INSERT INTO action_trigger.environment (
3511 currval('action_trigger.event_definition_id_seq')
3516 action_trigger.environment
3518 event_def = target_event_def
3520 INSERT INTO action_trigger.event_params (
3525 currval('action_trigger.event_definition_id_seq')
3529 action_trigger.event_params
3531 event_def = target_event_def
3534 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);
3536 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3538 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3539 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3541 target_event_def ALIAS FOR $1;
3543 new_interval ALIAS FOR $3;
3545 DROP TABLE IF EXISTS new_atevdefs;
3546 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3547 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3548 INSERT INTO action_trigger.event_definition (
3569 ,name || ' (clone of '||target_event_def||')'
3585 action_trigger.event_definition
3587 id = target_event_def
3589 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3590 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3591 INSERT INTO action_trigger.environment (
3597 currval('action_trigger.event_definition_id_seq')
3602 action_trigger.environment
3604 event_def = target_event_def
3606 INSERT INTO action_trigger.event_params (
3611 currval('action_trigger.event_definition_id_seq')
3615 action_trigger.event_params
3617 event_def = target_event_def
3620 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);
3622 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3624 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3625 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3628 target_event_defs ALIAS FOR $2;
3630 DROP TABLE IF EXISTS new_atevdefs;
3631 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3632 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3633 INSERT INTO action_trigger.event_definition (
3654 ,name || ' (clone of '||target_event_defs[i]||')'
3670 action_trigger.event_definition
3672 id = target_event_defs[i]
3674 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3675 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3676 INSERT INTO action_trigger.environment (
3682 currval('action_trigger.event_definition_id_seq')
3687 action_trigger.environment
3689 event_def = target_event_defs[i]
3691 INSERT INTO action_trigger.event_params (
3696 currval('action_trigger.event_definition_id_seq')
3700 action_trigger.event_params
3702 event_def = target_event_defs[i]
3705 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3707 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3709 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3711 action_trigger.event
3715 ,complete_time = NULL
3716 ,update_process = NULL
3718 ,template_output = NULL
3719 ,error_output = NULL
3720 ,async_output = NULL
3725 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3729 use MARC::File::XML;
3734 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3735 $field = $marc->leader();
3738 $$ LANGUAGE PLPERLU STABLE;
3740 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3741 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3744 use MARC::File::XML;
3749 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3750 $field = $marc->field($tag);
3752 return $field->as_string($subfield,$delimiter);
3753 $$ LANGUAGE PLPERLU STABLE;
3755 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3756 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3759 use MARC::File::XML;
3764 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3765 @fields = $marc->field($tag);
3768 foreach my $field (@fields) {
3769 push @texts, $field->as_string($subfield,$delimiter);
3772 $$ LANGUAGE PLPERLU STABLE;
3774 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3775 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3778 use MARC::File::XML;
3783 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3784 @fields = $marc->field($tag);
3787 foreach my $field (@fields) {
3788 if ($field->as_string() =~ qr/$match/) {
3789 push @texts, $field->as_string($subfield,$delimiter);
3793 $$ LANGUAGE PLPERLU STABLE;
3795 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3796 SELECT action.find_hold_matrix_matchpoint(
3797 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3798 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3799 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3800 (SELECT usr FROM action.hold_request WHERE id = $1),
3801 (SELECT requestor FROM action.hold_request WHERE id = $1)
3805 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3806 SELECT action.hold_request_permit_test(
3807 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3808 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3809 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3810 (SELECT usr FROM action.hold_request WHERE id = $1),
3811 (SELECT requestor FROM action.hold_request WHERE id = $1)
3815 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3816 SELECT action.find_circ_matrix_matchpoint(
3817 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3818 (SELECT target_copy FROM action.circulation WHERE id = $1),
3819 (SELECT usr FROM action.circulation WHERE id = $1),
3821 NULLIF(phone_renewal,false),
3822 NULLIF(desk_renewal,false),
3823 NULLIF(opac_renewal,false),
3825 ) FROM action.circulation WHERE id = $1
3830 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3835 RAISE EXCEPTION 'assertion';
3838 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3840 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3846 RAISE EXCEPTION '%', msg;
3849 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3851 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3854 fail_msg ALIAS FOR $2;
3855 success_msg ALIAS FOR $3;
3858 RAISE EXCEPTION '%', fail_msg;
3862 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3864 -- push bib sequence and return starting value for reserved range
3865 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3867 bib_count ALIAS FOR $1;
3870 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3872 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3877 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3879 -- set a new salted password
3881 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3883 usr_id ALIAS FOR $1;
3884 plain_passwd ALIAS FOR $2;
3889 SELECT actor.create_salt('main') INTO plain_salt;
3891 SELECT MD5(plain_passwd) INTO md5_passwd;
3893 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3898 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3901 -- convenience functions for handling copy_location maps
3902 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3903 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3906 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3908 table_schema ALIAS FOR $1;
3909 table_name ALIAS FOR $2;
3910 org_shortname ALIAS FOR $3;
3911 org_range ALIAS FOR $4;
3912 make_assertion ALIAS FOR $5;
3915 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3916 -- though we'll still use the passed org for the full path traversal when needed
3917 x_org_found BOOLEAN;
3923 EXECUTE 'SELECT EXISTS (
3925 FROM information_schema.columns
3926 WHERE table_schema = $1
3928 and column_name = ''desired_shelf''
3929 )' INTO proceed USING table_schema, table_name;
3931 RAISE EXCEPTION 'Missing column desired_shelf';
3934 EXECUTE 'SELECT EXISTS (
3936 FROM information_schema.columns
3937 WHERE table_schema = $1
3939 and column_name = ''x_org''
3940 )' INTO x_org_found USING table_schema, table_name;
3942 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3944 RAISE EXCEPTION 'Cannot find org by shortname';
3947 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3949 EXECUTE 'ALTER TABLE '
3950 || quote_ident(table_name)
3951 || ' DROP COLUMN IF EXISTS x_shelf';
3952 EXECUTE 'ALTER TABLE '
3953 || quote_ident(table_name)
3954 || ' ADD COLUMN x_shelf INTEGER';
3957 RAISE INFO 'Found x_org column';
3958 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3959 || ' SET x_shelf = b.id FROM asset_copy_location b'
3960 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3961 || ' AND b.owning_lib = x_org'
3962 || ' AND NOT b.deleted';
3963 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3964 || ' SET x_shelf = b.id FROM asset.copy_location b'
3965 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3966 || ' AND b.owning_lib = x_org'
3967 || ' AND x_shelf IS NULL'
3968 || ' AND NOT b.deleted';
3970 RAISE INFO 'Did not find x_org column';
3971 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3972 || ' SET x_shelf = b.id FROM asset_copy_location b'
3973 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3974 || ' AND b.owning_lib = $1'
3975 || ' AND NOT b.deleted'
3977 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3978 || ' SET x_shelf = b.id FROM asset_copy_location b'
3979 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3980 || ' AND b.owning_lib = $1'
3981 || ' AND x_shelf IS NULL'
3982 || ' AND NOT b.deleted'
3986 FOREACH o IN ARRAY org_list LOOP
3987 RAISE INFO 'Considering org %', o;
3988 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3989 || ' SET x_shelf = b.id FROM asset.copy_location b'
3990 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3991 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3992 || ' AND NOT b.deleted'
3994 GET DIAGNOSTICS row_count = ROW_COUNT;
3995 RAISE INFO 'Updated % rows', row_count;
3998 IF make_assertion THEN
3999 EXECUTE 'SELECT migration_tools.assert(
4000 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
4001 ''Cannot find a desired location'',
4002 ''Found all desired locations''
4007 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4009 -- convenience functions for handling circmod maps
4011 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
4013 table_schema ALIAS FOR $1;
4014 table_name ALIAS FOR $2;
4017 EXECUTE 'SELECT EXISTS (
4019 FROM information_schema.columns
4020 WHERE table_schema = $1
4022 and column_name = ''desired_circmod''
4023 )' INTO proceed USING table_schema, table_name;
4025 RAISE EXCEPTION 'Missing column desired_circmod';
4028 EXECUTE 'ALTER TABLE '
4029 || quote_ident(table_name)
4030 || ' DROP COLUMN IF EXISTS x_circmod';
4031 EXECUTE 'ALTER TABLE '
4032 || quote_ident(table_name)
4033 || ' ADD COLUMN x_circmod TEXT';
4035 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4036 || ' SET x_circmod = code FROM config.circ_modifier b'
4037 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
4039 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4040 || ' SET x_circmod = code FROM config.circ_modifier b'
4041 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
4042 || ' AND x_circmod IS NULL';
4044 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4045 || ' SET x_circmod = code FROM config.circ_modifier b'
4046 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
4047 || ' AND x_circmod IS NULL';
4049 EXECUTE 'SELECT migration_tools.assert(
4050 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
4051 ''Cannot find a desired circulation modifier'',
4052 ''Found all desired circulation modifiers''
4056 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4058 -- convenience functions for handling item status maps
4060 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
4062 table_schema ALIAS FOR $1;
4063 table_name ALIAS FOR $2;
4066 EXECUTE 'SELECT EXISTS (
4068 FROM information_schema.columns
4069 WHERE table_schema = $1
4071 and column_name = ''desired_status''
4072 )' INTO proceed USING table_schema, table_name;
4074 RAISE EXCEPTION 'Missing column desired_status';
4077 EXECUTE 'ALTER TABLE '
4078 || quote_ident(table_name)
4079 || ' DROP COLUMN IF EXISTS x_status';
4080 EXECUTE 'ALTER TABLE '
4081 || quote_ident(table_name)
4082 || ' ADD COLUMN x_status INTEGER';
4084 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4085 || ' SET x_status = id FROM config.copy_status b'
4086 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
4088 EXECUTE 'SELECT migration_tools.assert(
4089 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
4090 ''Cannot find a desired copy status'',
4091 ''Found all desired copy statuses''
4095 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4097 -- convenience functions for handling org maps
4099 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
4101 table_schema ALIAS FOR $1;
4102 table_name ALIAS FOR $2;
4105 EXECUTE 'SELECT EXISTS (
4107 FROM information_schema.columns
4108 WHERE table_schema = $1
4110 and column_name = ''desired_org''
4111 )' INTO proceed USING table_schema, table_name;
4113 RAISE EXCEPTION 'Missing column desired_org';
4116 EXECUTE 'ALTER TABLE '
4117 || quote_ident(table_name)
4118 || ' DROP COLUMN IF EXISTS x_org';
4119 EXECUTE 'ALTER TABLE '
4120 || quote_ident(table_name)
4121 || ' ADD COLUMN x_org INTEGER';
4123 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4124 || ' SET x_org = b.id FROM actor.org_unit b'
4125 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
4127 EXECUTE 'SELECT migration_tools.assert(
4128 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
4129 ''Cannot find a desired org unit'',
4130 ''Found all desired org units''
4134 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4136 -- convenience function for handling desired_not_migrate
4138 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
4140 table_schema ALIAS FOR $1;
4141 table_name ALIAS FOR $2;
4144 EXECUTE 'SELECT EXISTS (
4146 FROM information_schema.columns
4147 WHERE table_schema = $1
4149 and column_name = ''desired_not_migrate''
4150 )' INTO proceed USING table_schema, table_name;
4152 RAISE EXCEPTION 'Missing column desired_not_migrate';
4155 EXECUTE 'ALTER TABLE '
4156 || quote_ident(table_name)
4157 || ' DROP COLUMN IF EXISTS x_migrate';
4158 EXECUTE 'ALTER TABLE '
4159 || quote_ident(table_name)
4160 || ' ADD COLUMN x_migrate BOOLEAN';
4162 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4163 || ' SET x_migrate = CASE'
4164 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4165 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4166 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4167 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4168 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4169 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4172 EXECUTE 'SELECT migration_tools.assert(
4173 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4174 ''Not all desired_not_migrate values understood'',
4175 ''All desired_not_migrate values understood''
4179 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4181 -- convenience function for handling desired_not_migrate
4183 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4185 table_schema ALIAS FOR $1;
4186 table_name ALIAS FOR $2;
4189 EXECUTE 'SELECT EXISTS (
4191 FROM information_schema.columns
4192 WHERE table_schema = $1
4194 and column_name = ''desired_barred_or_blocked''
4195 )' INTO proceed USING table_schema, table_name;
4197 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4200 EXECUTE 'ALTER TABLE '
4201 || quote_ident(table_name)
4202 || ' DROP COLUMN IF EXISTS x_barred';
4203 EXECUTE 'ALTER TABLE '
4204 || quote_ident(table_name)
4205 || ' ADD COLUMN x_barred BOOLEAN';
4207 EXECUTE 'ALTER TABLE '
4208 || quote_ident(table_name)
4209 || ' DROP COLUMN IF EXISTS x_blocked';
4210 EXECUTE 'ALTER TABLE '
4211 || quote_ident(table_name)
4212 || ' ADD COLUMN x_blocked BOOLEAN';
4214 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4215 || ' SET x_barred = CASE'
4216 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4217 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4218 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4219 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4222 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4223 || ' SET x_blocked = CASE'
4224 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4225 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4226 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4227 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4230 EXECUTE 'SELECT migration_tools.assert(
4231 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4232 ''Not all desired_barred_or_blocked values understood'',
4233 ''All desired_barred_or_blocked values understood''
4237 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4239 -- convenience function for handling desired_profile
4241 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4243 table_schema ALIAS FOR $1;
4244 table_name ALIAS FOR $2;
4247 EXECUTE 'SELECT EXISTS (
4249 FROM information_schema.columns
4250 WHERE table_schema = $1
4252 and column_name = ''desired_profile''
4253 )' INTO proceed USING table_schema, table_name;
4255 RAISE EXCEPTION 'Missing column desired_profile';
4258 EXECUTE 'ALTER TABLE '
4259 || quote_ident(table_name)
4260 || ' DROP COLUMN IF EXISTS x_profile';
4261 EXECUTE 'ALTER TABLE '
4262 || quote_ident(table_name)
4263 || ' ADD COLUMN x_profile INTEGER';
4265 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4266 || ' SET x_profile = b.id FROM permission.grp_tree b'
4267 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4269 EXECUTE 'SELECT migration_tools.assert(
4270 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4271 ''Cannot find a desired profile'',
4272 ''Found all desired profiles''
4276 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4278 -- convenience function for handling desired actor stat cats
4280 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4282 table_schema ALIAS FOR $1;
4283 table_name ALIAS FOR $2;
4284 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4285 org_shortname ALIAS FOR $4;
4293 SELECT 'desired_sc' || field_suffix INTO sc;
4294 SELECT 'desired_sce' || field_suffix INTO sce;
4296 EXECUTE 'SELECT EXISTS (
4298 FROM information_schema.columns
4299 WHERE table_schema = $1
4301 and column_name = $3
4302 )' INTO proceed USING table_schema, table_name, sc;
4304 RAISE EXCEPTION 'Missing column %', sc;
4306 EXECUTE 'SELECT EXISTS (
4308 FROM information_schema.columns
4309 WHERE table_schema = $1
4311 and column_name = $3
4312 )' INTO proceed USING table_schema, table_name, sce;
4314 RAISE EXCEPTION 'Missing column %', sce;
4317 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4319 RAISE EXCEPTION 'Cannot find org by shortname';
4321 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4323 -- caller responsible for their own truncates though we try to prevent duplicates
4324 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4329 ' || quote_ident(table_name) || '
4331 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4335 WHERE owner = ANY ($2)
4336 AND name = BTRIM('||sc||')
4341 WHERE owner = ANY ($2)
4342 AND name = BTRIM('||sc||')
4345 USING org, org_list;
4347 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4352 WHERE owner = ANY ($2)
4353 AND BTRIM('||sc||') = BTRIM(name))
4356 WHERE owner = ANY ($2)
4357 AND BTRIM('||sc||') = BTRIM(name))
4362 ' || quote_ident(table_name) || '
4364 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4365 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4368 FROM actor.stat_cat_entry
4372 WHERE owner = ANY ($2)
4373 AND BTRIM('||sc||') = BTRIM(name)
4374 ) AND value = BTRIM('||sce||')
4375 AND owner = ANY ($2)
4379 FROM actor_stat_cat_entry
4383 WHERE owner = ANY ($2)
4384 AND BTRIM('||sc||') = BTRIM(name)
4385 ) AND value = BTRIM('||sce||')
4386 AND owner = ANY ($2)
4389 USING org, org_list;
4391 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4393 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4395 table_schema ALIAS FOR $1;
4396 table_name ALIAS FOR $2;
4397 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4398 org_shortname ALIAS FOR $4;
4406 SELECT 'desired_sc' || field_suffix INTO sc;
4407 SELECT 'desired_sce' || field_suffix INTO sce;
4408 EXECUTE 'SELECT EXISTS (
4410 FROM information_schema.columns
4411 WHERE table_schema = $1
4413 and column_name = $3
4414 )' INTO proceed USING table_schema, table_name, sc;
4416 RAISE EXCEPTION 'Missing column %', sc;
4418 EXECUTE 'SELECT EXISTS (
4420 FROM information_schema.columns
4421 WHERE table_schema = $1
4423 and column_name = $3
4424 )' INTO proceed USING table_schema, table_name, sce;
4426 RAISE EXCEPTION 'Missing column %', sce;
4429 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4431 RAISE EXCEPTION 'Cannot find org by shortname';
4434 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4436 EXECUTE 'ALTER TABLE '
4437 || quote_ident(table_name)
4438 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4439 EXECUTE 'ALTER TABLE '
4440 || quote_ident(table_name)
4441 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4442 EXECUTE 'ALTER TABLE '
4443 || quote_ident(table_name)
4444 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4445 EXECUTE 'ALTER TABLE '
4446 || quote_ident(table_name)
4447 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4450 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4452 x_sc' || field_suffix || ' = id
4454 (SELECT id, name, owner FROM actor_stat_cat
4455 UNION SELECT id, name, owner FROM actor.stat_cat) u
4457 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4458 AND u.owner = ANY ($1);'
4461 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4463 x_sce' || field_suffix || ' = id
4465 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4466 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4468 u.stat_cat = x_sc' || field_suffix || '
4469 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4470 AND u.owner = ANY ($1);'
4473 EXECUTE 'SELECT migration_tools.assert(
4474 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4475 ''Cannot find a desired stat cat'',
4476 ''Found all desired stat cats''
4479 EXECUTE 'SELECT migration_tools.assert(
4480 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4481 ''Cannot find a desired stat cat entry'',
4482 ''Found all desired stat cat entries''
4486 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4488 -- convenience functions for adding shelving locations
4489 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4490 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4496 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4499 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4500 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4501 IF return_id IS NOT NULL THEN
4509 $$ LANGUAGE plpgsql;
4511 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4513 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4514 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4520 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4523 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4525 SELECT INTO return_id id FROM
4526 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4527 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4528 IF return_id IS NOT NULL THEN
4536 $$ LANGUAGE plpgsql;
4538 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4539 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4546 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4548 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4549 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4553 $BODY$ LANGUAGE plpgsql;
4555 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4556 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4564 use MARC::File::XML (BinaryEncoding => 'utf8');
4566 binmode(STDERR, ':bytes');
4567 binmode(STDOUT, ':utf8');
4568 binmode(STDERR, ':utf8');
4570 my $marc_xml = shift;
4571 my $new_9_to_set = shift;
4574 $marc_xml =~ s/(<leader>.........)./${1}a/;
4577 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4580 #elog("could not parse $bibid: $@\n");
4581 import MARC::File::XML (BinaryEncoding => 'utf8');
4585 my @uris = $marc_xml->field('856');
4586 return $marc_xml->as_xml_record() unless @uris;
4588 foreach my $field (@uris) {
4589 my $ind1 = $field->indicator('1');
4590 if (!defined $ind1) { next; }
4591 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4592 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4593 my $ind2 = $field->indicator('2');
4594 if (!defined $ind2) { next; }
4595 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4596 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4597 $field->add_subfields( '9' => $new_9_to_set );
4600 return $marc_xml->as_xml_record();
4604 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4605 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4613 use MARC::File::XML (BinaryEncoding => 'utf8');
4615 binmode(STDERR, ':bytes');
4616 binmode(STDOUT, ':utf8');
4617 binmode(STDERR, ':utf8');
4619 my $marc_xml = shift;
4620 my $qualifying_match = shift;
4621 my $new_9_to_set = shift;
4624 $marc_xml =~ s/(<leader>.........)./${1}a/;
4627 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4630 #elog("could not parse $bibid: $@\n");
4631 import MARC::File::XML (BinaryEncoding => 'utf8');
4635 my @uris = $marc_xml->field('856');
4636 return $marc_xml->as_xml_record() unless @uris;
4638 foreach my $field (@uris) {
4639 if ($field->as_string() =~ qr/$qualifying_match/) {
4640 my $ind1 = $field->indicator('1');
4641 if (!defined $ind1) { next; }
4642 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4643 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4644 my $ind2 = $field->indicator('2');
4645 if (!defined $ind2) { next; }
4646 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4647 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4648 $field->add_subfields( '9' => $new_9_to_set );
4652 return $marc_xml->as_xml_record();
4656 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4657 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4665 use MARC::File::XML (BinaryEncoding => 'utf8');
4667 binmode(STDERR, ':bytes');
4668 binmode(STDOUT, ':utf8');
4669 binmode(STDERR, ':utf8');
4671 my $marc_xml = shift;
4672 my $substring_old_value = shift;
4673 my $new_value = shift;
4674 my $fix_indicators = shift;
4676 $marc_xml =~ s/(<leader>.........)./${1}a/;
4679 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4682 #elog("could not parse $bibid: $@\n");
4683 import MARC::File::XML (BinaryEncoding => 'utf8');
4687 my @uris = $marc_xml->field('856');
4688 return $marc_xml->as_xml_record() unless @uris;
4690 foreach my $field (@uris) {
4691 my $ind1 = $field->indicator('1');
4692 if (defined $ind1) {
4693 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4694 $field->set_indicator(1,'4');
4697 my $ind2 = $field->indicator('2');
4698 if (defined $ind2) {
4699 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4700 $field->set_indicator(2,'0');
4703 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4704 $field->delete_subfield('9');
4705 $field->add_subfields( '9' => $new_value );
4707 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4710 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4711 $marc_xml->insert_fields_ordered( values( %hash ) );
4713 return $marc_xml->as_xml_record();
4717 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4718 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)
4726 use MARC::File::XML (BinaryEncoding => 'utf8');
4728 binmode(STDERR, ':bytes');
4729 binmode(STDOUT, ':utf8');
4730 binmode(STDERR, ':utf8');
4732 my $marc_xml = shift;
4733 my $qualifying_match = shift;
4734 my $substring_old_value = shift;
4735 my $new_value = shift;
4736 my $fix_indicators = shift;
4738 $marc_xml =~ s/(<leader>.........)./${1}a/;
4741 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4744 #elog("could not parse $bibid: $@\n");
4745 import MARC::File::XML (BinaryEncoding => 'utf8');
4749 my @unqualified_uris = $marc_xml->field('856');
4751 foreach my $field (@unqualified_uris) {
4752 if ($field->as_string() =~ qr/$qualifying_match/) {
4756 return $marc_xml->as_xml_record() unless @uris;
4758 foreach my $field (@uris) {
4759 my $ind1 = $field->indicator('1');
4760 if (defined $ind1) {
4761 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4762 $field->set_indicator(1,'4');
4765 my $ind2 = $field->indicator('2');
4766 if (defined $ind2) {
4767 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4768 $field->set_indicator(2,'0');
4771 if ($field->as_string('9') =~ qr/$substring_old_value/) {
4772 $field->delete_subfield('9');
4773 $field->add_subfields( '9' => $new_value );
4775 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4778 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4779 $marc_xml->insert_fields_ordered( values( %hash ) );
4781 return $marc_xml->as_xml_record();
4786 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4787 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4795 use MARC::File::XML (BinaryEncoding => 'utf8');
4797 binmode(STDERR, ':bytes');
4798 binmode(STDOUT, ':utf8');
4799 binmode(STDERR, ':utf8');
4801 my $marc_xml = shift;
4804 $marc_xml =~ s/(<leader>.........)./${1}a/;
4807 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4810 #elog("could not parse $bibid: $@\n");
4811 import MARC::File::XML (BinaryEncoding => 'utf8');
4815 my @fields = $marc_xml->field($tag);
4816 return $marc_xml->as_xml_record() unless @fields;
4818 $marc_xml->delete_fields(@fields);
4820 return $marc_xml->as_xml_record();
4824 -- consolidate marc tag
4825 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
4826 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
4834 use MARC::File::XML (BinaryEncoding => 'utf8');
4836 binmode(STDERR, ':bytes');
4837 binmode(STDOUT, ':utf8');
4838 binmode(STDERR, ':utf8');
4840 my $marc_xml = 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 @fields = $marc_xml->field($tag);
4855 return $marc_xml->as_xml_record() unless @fields;
4857 my @combined_subfield_refs = ();
4858 my @combined_subfields = ();
4859 foreach my $field (@fields) {
4860 my @subfield_refs = $field->subfields();
4861 push @combined_subfield_refs, @subfield_refs;
4864 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
4866 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
4867 my ($code,$data) = @$tuple;
4868 unshift( @combined_subfields, $code, $data );
4871 $marc_xml->delete_fields(@fields);
4873 my $new_field = new MARC::Field(
4875 $fields[0]->indicator(1),
4876 $fields[0]->indicator(2),
4880 $marc_xml->insert_grouped_field( $new_field );
4882 return $marc_xml->as_xml_record();
4886 -- convenience function for linking to the item staging table
4888 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4890 table_schema ALIAS FOR $1;
4891 table_name ALIAS FOR $2;
4892 foreign_column_name ALIAS FOR $3;
4893 main_column_name ALIAS FOR $4;
4894 btrim_desired ALIAS FOR $5;
4897 EXECUTE 'SELECT EXISTS (
4899 FROM information_schema.columns
4900 WHERE table_schema = $1
4902 and column_name = $3
4903 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4905 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4908 EXECUTE 'SELECT EXISTS (
4910 FROM information_schema.columns
4911 WHERE table_schema = $1
4912 AND table_name = ''asset_copy_legacy''
4913 and column_name = $2
4914 )' INTO proceed USING table_schema, main_column_name;
4916 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4919 EXECUTE 'ALTER TABLE '
4920 || quote_ident(table_name)
4921 || ' DROP COLUMN IF EXISTS x_item';
4922 EXECUTE 'ALTER TABLE '
4923 || quote_ident(table_name)
4924 || ' ADD COLUMN x_item BIGINT';
4926 IF btrim_desired THEN
4927 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4928 || ' SET x_item = b.id FROM asset_copy_legacy b'
4929 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4930 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4932 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4933 || ' SET x_item = b.id FROM asset_copy_legacy b'
4934 || ' WHERE a.' || quote_ident(foreign_column_name)
4935 || ' = b.' || quote_ident(main_column_name);
4938 --EXECUTE 'SELECT migration_tools.assert(
4939 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4940 -- ''Cannot link every barcode'',
4941 -- ''Every barcode linked''
4945 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4947 -- convenience function for linking to the user staging table
4949 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4951 table_schema ALIAS FOR $1;
4952 table_name ALIAS FOR $2;
4953 foreign_column_name ALIAS FOR $3;
4954 main_column_name ALIAS FOR $4;
4955 btrim_desired ALIAS FOR $5;
4958 EXECUTE 'SELECT EXISTS (
4960 FROM information_schema.columns
4961 WHERE table_schema = $1
4963 and column_name = $3
4964 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4966 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4969 EXECUTE 'SELECT EXISTS (
4971 FROM information_schema.columns
4972 WHERE table_schema = $1
4973 AND table_name = ''actor_usr_legacy''
4974 and column_name = $2
4975 )' INTO proceed USING table_schema, main_column_name;
4977 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4980 EXECUTE 'ALTER TABLE '
4981 || quote_ident(table_name)
4982 || ' DROP COLUMN IF EXISTS x_user';
4983 EXECUTE 'ALTER TABLE '
4984 || quote_ident(table_name)
4985 || ' ADD COLUMN x_user INTEGER';
4987 IF btrim_desired THEN
4988 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4989 || ' SET x_user = b.id FROM actor_usr_legacy b'
4990 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4991 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4993 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4994 || ' SET x_user = b.id FROM actor_usr_legacy b'
4995 || ' WHERE a.' || quote_ident(foreign_column_name)
4996 || ' = b.' || quote_ident(main_column_name);
4999 --EXECUTE 'SELECT migration_tools.assert(
5000 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
5001 -- ''Cannot link every barcode'',
5002 -- ''Every barcode linked''
5006 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5008 -- convenience function for linking two tables
5009 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
5010 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5012 table_schema ALIAS FOR $1;
5013 table_a ALIAS FOR $2;
5014 column_a ALIAS FOR $3;
5015 table_b ALIAS FOR $4;
5016 column_b ALIAS FOR $5;
5017 column_x ALIAS FOR $6;
5018 btrim_desired ALIAS FOR $7;
5021 EXECUTE 'SELECT EXISTS (
5023 FROM information_schema.columns
5024 WHERE table_schema = $1
5026 and column_name = $3
5027 )' INTO proceed USING table_schema, table_a, column_a;
5029 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5032 EXECUTE 'SELECT EXISTS (
5034 FROM information_schema.columns
5035 WHERE table_schema = $1
5037 and column_name = $3
5038 )' INTO proceed USING table_schema, table_b, column_b;
5040 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5043 EXECUTE 'ALTER TABLE '
5044 || quote_ident(table_b)
5045 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5046 EXECUTE 'ALTER TABLE '
5047 || quote_ident(table_b)
5048 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
5050 IF btrim_desired THEN
5051 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5052 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5053 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5054 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5056 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5057 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5058 || ' WHERE a.' || quote_ident(column_a)
5059 || ' = b.' || quote_ident(column_b);
5063 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5065 -- convenience function for linking two tables, but copying column w into column x instead of "id"
5066 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
5067 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5069 table_schema ALIAS FOR $1;
5070 table_a ALIAS FOR $2;
5071 column_a ALIAS FOR $3;
5072 table_b ALIAS FOR $4;
5073 column_b ALIAS FOR $5;
5074 column_w ALIAS FOR $6;
5075 column_x ALIAS FOR $7;
5076 btrim_desired ALIAS FOR $8;
5079 EXECUTE 'SELECT EXISTS (
5081 FROM information_schema.columns
5082 WHERE table_schema = $1
5084 and column_name = $3
5085 )' INTO proceed USING table_schema, table_a, column_a;
5087 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5090 EXECUTE 'SELECT EXISTS (
5092 FROM information_schema.columns
5093 WHERE table_schema = $1
5095 and column_name = $3
5096 )' INTO proceed USING table_schema, table_b, column_b;
5098 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5101 EXECUTE 'ALTER TABLE '
5102 || quote_ident(table_b)
5103 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5104 EXECUTE 'ALTER TABLE '
5105 || quote_ident(table_b)
5106 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
5108 IF btrim_desired THEN
5109 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5110 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5111 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5112 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5114 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5115 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5116 || ' WHERE a.' || quote_ident(column_a)
5117 || ' = b.' || quote_ident(column_b);
5121 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5123 -- 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
5124 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
5125 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5127 table_schema ALIAS FOR $1;
5128 table_a ALIAS FOR $2;
5129 column_a ALIAS FOR $3;
5130 table_b ALIAS FOR $4;
5131 column_b ALIAS FOR $5;
5132 column_w ALIAS FOR $6;
5133 column_x ALIAS FOR $7;
5136 EXECUTE 'SELECT EXISTS (
5138 FROM information_schema.columns
5139 WHERE table_schema = $1
5141 and column_name = $3
5142 )' INTO proceed USING table_schema, table_a, column_a;
5144 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5147 EXECUTE 'SELECT EXISTS (
5149 FROM information_schema.columns
5150 WHERE table_schema = $1
5152 and column_name = $3
5153 )' INTO proceed USING table_schema, table_b, column_b;
5155 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5158 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5159 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5160 || ' WHERE a.' || quote_ident(column_a)
5161 || ' = b.' || quote_ident(column_b);
5164 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5166 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5168 table_schema ALIAS FOR $1;
5169 table_a ALIAS FOR $2;
5170 column_a ALIAS FOR $3;
5171 table_b ALIAS FOR $4;
5172 column_b ALIAS FOR $5;
5173 column_w ALIAS FOR $6;
5174 column_x ALIAS FOR $7;
5177 EXECUTE 'SELECT EXISTS (
5179 FROM information_schema.columns
5180 WHERE table_schema = $1
5182 and column_name = $3
5183 )' INTO proceed USING table_schema, table_a, column_a;
5185 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5188 EXECUTE 'SELECT EXISTS (
5190 FROM information_schema.columns
5191 WHERE table_schema = $1
5193 and column_name = $3
5194 )' INTO proceed USING table_schema, table_b, column_b;
5196 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5199 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5200 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5201 || ' WHERE a.' || quote_ident(column_a)
5202 || ' = b.' || quote_ident(column_b)
5203 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5208 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5210 table_schema ALIAS FOR $1;
5211 table_a ALIAS FOR $2;
5212 column_a ALIAS FOR $3;
5213 table_b ALIAS FOR $4;
5214 column_b ALIAS FOR $5;
5215 column_w ALIAS FOR $6;
5216 column_x ALIAS FOR $7;
5219 EXECUTE 'SELECT EXISTS (
5221 FROM information_schema.columns
5222 WHERE table_schema = $1
5224 and column_name = $3
5225 )' INTO proceed USING table_schema, table_a, column_a;
5227 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5230 EXECUTE 'SELECT EXISTS (
5232 FROM information_schema.columns
5233 WHERE table_schema = $1
5235 and column_name = $3
5236 )' INTO proceed USING table_schema, table_b, column_b;
5238 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5241 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5242 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5243 || ' WHERE a.' || quote_ident(column_a)
5244 || ' = b.' || quote_ident(column_b)
5245 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5248 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5250 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5252 table_schema ALIAS FOR $1;
5253 table_a ALIAS FOR $2;
5254 column_a ALIAS FOR $3;
5255 table_b ALIAS FOR $4;
5256 column_b ALIAS FOR $5;
5257 column_w ALIAS FOR $6;
5258 column_x ALIAS FOR $7;
5261 EXECUTE 'SELECT EXISTS (
5263 FROM information_schema.columns
5264 WHERE table_schema = $1
5266 and column_name = $3
5267 )' INTO proceed USING table_schema, table_a, column_a;
5269 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5272 EXECUTE 'SELECT EXISTS (
5274 FROM information_schema.columns
5275 WHERE table_schema = $1
5277 and column_name = $3
5278 )' INTO proceed USING table_schema, table_b, column_b;
5280 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5283 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5284 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5285 || ' WHERE a.' || quote_ident(column_a)
5286 || ' = b.' || quote_ident(column_b)
5287 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5290 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5292 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5294 table_schema ALIAS FOR $1;
5295 table_a ALIAS FOR $2;
5296 column_a ALIAS FOR $3;
5297 table_b ALIAS FOR $4;
5298 column_b ALIAS FOR $5;
5299 column_w ALIAS FOR $6;
5300 column_x ALIAS FOR $7;
5303 EXECUTE 'SELECT EXISTS (
5305 FROM information_schema.columns
5306 WHERE table_schema = $1
5308 and column_name = $3
5309 )' INTO proceed USING table_schema, table_a, column_a;
5311 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5314 EXECUTE 'SELECT EXISTS (
5316 FROM information_schema.columns
5317 WHERE table_schema = $1
5319 and column_name = $3
5320 )' INTO proceed USING table_schema, table_b, column_b;
5322 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5325 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5326 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5327 || ' WHERE a.' || quote_ident(column_a)
5328 || ' = b.' || quote_ident(column_b)
5329 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5332 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5334 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5336 table_schema ALIAS FOR $1;
5337 table_a ALIAS FOR $2;
5338 column_a ALIAS FOR $3;
5339 table_b ALIAS FOR $4;
5340 column_b ALIAS FOR $5;
5341 column_w ALIAS FOR $6;
5342 column_x ALIAS FOR $7;
5345 EXECUTE 'SELECT EXISTS (
5347 FROM information_schema.columns
5348 WHERE table_schema = $1
5350 and column_name = $3
5351 )' INTO proceed USING table_schema, table_a, column_a;
5353 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5356 EXECUTE 'SELECT EXISTS (
5358 FROM information_schema.columns
5359 WHERE table_schema = $1
5361 and column_name = $3
5362 )' INTO proceed USING table_schema, table_b, column_b;
5364 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5367 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5368 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5369 || ' WHERE a.' || quote_ident(column_a)
5370 || ' = b.' || quote_ident(column_b)
5371 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5374 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5376 -- convenience function for handling desired asset stat cats
5378 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5380 table_schema ALIAS FOR $1;
5381 table_name ALIAS FOR $2;
5382 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5383 org_shortname ALIAS FOR $4;
5391 SELECT 'desired_sc' || field_suffix INTO sc;
5392 SELECT 'desired_sce' || field_suffix INTO sce;
5394 EXECUTE 'SELECT EXISTS (
5396 FROM information_schema.columns
5397 WHERE table_schema = $1
5399 and column_name = $3
5400 )' INTO proceed USING table_schema, table_name, sc;
5402 RAISE EXCEPTION 'Missing column %', sc;
5404 EXECUTE 'SELECT EXISTS (
5406 FROM information_schema.columns
5407 WHERE table_schema = $1
5409 and column_name = $3
5410 )' INTO proceed USING table_schema, table_name, sce;
5412 RAISE EXCEPTION 'Missing column %', sce;
5415 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5417 RAISE EXCEPTION 'Cannot find org by shortname';
5419 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5421 -- caller responsible for their own truncates though we try to prevent duplicates
5422 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5427 ' || quote_ident(table_name) || '
5429 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5433 WHERE owner = ANY ($2)
5434 AND name = BTRIM('||sc||')
5439 WHERE owner = ANY ($2)
5440 AND name = BTRIM('||sc||')
5443 USING org, org_list;
5445 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5450 WHERE owner = ANY ($2)
5451 AND BTRIM('||sc||') = BTRIM(name))
5454 WHERE owner = ANY ($2)
5455 AND BTRIM('||sc||') = BTRIM(name))
5460 ' || quote_ident(table_name) || '
5462 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5463 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5466 FROM asset.stat_cat_entry
5470 WHERE owner = ANY ($2)
5471 AND BTRIM('||sc||') = BTRIM(name)
5472 ) AND value = BTRIM('||sce||')
5473 AND owner = ANY ($2)
5477 FROM asset_stat_cat_entry
5481 WHERE owner = ANY ($2)
5482 AND BTRIM('||sc||') = BTRIM(name)
5483 ) AND value = BTRIM('||sce||')
5484 AND owner = ANY ($2)
5487 USING org, org_list;
5489 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5491 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5493 table_schema ALIAS FOR $1;
5494 table_name ALIAS FOR $2;
5495 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5496 org_shortname ALIAS FOR $4;
5504 SELECT 'desired_sc' || field_suffix INTO sc;
5505 SELECT 'desired_sce' || field_suffix INTO sce;
5506 EXECUTE 'SELECT EXISTS (
5508 FROM information_schema.columns
5509 WHERE table_schema = $1
5511 and column_name = $3
5512 )' INTO proceed USING table_schema, table_name, sc;
5514 RAISE EXCEPTION 'Missing column %', sc;
5516 EXECUTE 'SELECT EXISTS (
5518 FROM information_schema.columns
5519 WHERE table_schema = $1
5521 and column_name = $3
5522 )' INTO proceed USING table_schema, table_name, sce;
5524 RAISE EXCEPTION 'Missing column %', sce;
5527 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5529 RAISE EXCEPTION 'Cannot find org by shortname';
5532 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5534 EXECUTE 'ALTER TABLE '
5535 || quote_ident(table_name)
5536 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5537 EXECUTE 'ALTER TABLE '
5538 || quote_ident(table_name)
5539 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5540 EXECUTE 'ALTER TABLE '
5541 || quote_ident(table_name)
5542 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5543 EXECUTE 'ALTER TABLE '
5544 || quote_ident(table_name)
5545 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5548 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5550 x_sc' || field_suffix || ' = id
5552 (SELECT id, name, owner FROM asset_stat_cat
5553 UNION SELECT id, name, owner FROM asset.stat_cat) u
5555 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5556 AND u.owner = ANY ($1);'
5559 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5561 x_sce' || field_suffix || ' = id
5563 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5564 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5566 u.stat_cat = x_sc' || field_suffix || '
5567 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5568 AND u.owner = ANY ($1);'
5571 EXECUTE 'SELECT migration_tools.assert(
5572 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5573 ''Cannot find a desired stat cat'',
5574 ''Found all desired stat cats''
5577 EXECUTE 'SELECT migration_tools.assert(
5578 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5579 ''Cannot find a desired stat cat entry'',
5580 ''Found all desired stat cat entries''
5584 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5586 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5587 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5594 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5596 AND table_schema = s_name
5597 AND (data_type='text' OR data_type='character varying')
5598 AND column_name like 'l_%'
5600 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5607 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5608 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5615 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5617 AND table_schema = s_name
5618 AND (data_type='text' OR data_type='character varying')
5620 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5627 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5628 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5635 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5637 AND table_schema = s_name
5638 AND (data_type='text' OR data_type='character varying')
5639 AND column_name like 'l_%'
5641 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5648 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5649 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5656 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5658 AND table_schema = s_name
5659 AND (data_type='text' OR data_type='character varying')
5661 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5669 -- convenience function for handling item barcode collisions in asset_copy_legacy
5671 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5676 internal_collision_count NUMERIC := 0;
5677 incumbent_collision_count NUMERIC := 0;
5679 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5681 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5683 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5684 GET DIAGNOSTICS row_count = ROW_COUNT;
5685 internal_collision_count := internal_collision_count + row_count;
5688 RAISE INFO '% internal collisions', internal_collision_count;
5689 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
5691 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5693 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5694 GET DIAGNOSTICS row_count = ROW_COUNT;
5695 incumbent_collision_count := incumbent_collision_count + row_count;
5698 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5700 $function$ LANGUAGE plpgsql;
5702 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5703 -- this should be ran prior to populating actor_card
5705 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5710 internal_collision_count NUMERIC := 0;
5711 incumbent_barcode_collision_count NUMERIC := 0;
5712 incumbent_usrname_collision_count NUMERIC := 0;
5714 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5716 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5718 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5719 GET DIAGNOSTICS row_count = ROW_COUNT;
5720 internal_collision_count := internal_collision_count + row_count;
5723 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5726 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5728 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5730 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5731 GET DIAGNOSTICS row_count = ROW_COUNT;
5732 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5735 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5738 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5740 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5742 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5743 GET DIAGNOSTICS row_count = ROW_COUNT;
5744 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5747 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5749 $function$ LANGUAGE plpgsql;
5751 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5753 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5758 internal_collision_count NUMERIC := 0;
5759 incumbent_collision_count NUMERIC := 0;
5761 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5763 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5765 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5766 GET DIAGNOSTICS row_count = ROW_COUNT;
5767 internal_collision_count := internal_collision_count + row_count;
5770 RAISE INFO '% internal collisions', internal_collision_count;
5771 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
5773 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5775 UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5776 GET DIAGNOSTICS row_count = ROW_COUNT;
5777 incumbent_collision_count := incumbent_collision_count + row_count;
5780 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5782 $function$ LANGUAGE plpgsql;
5784 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5785 -- this should be ran prior to populating actor_card
5787 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5792 internal_collision_count NUMERIC := 0;
5793 incumbent_barcode_collision_count NUMERIC := 0;
5794 incumbent_usrname_collision_count NUMERIC := 0;
5796 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5798 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5800 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5801 GET DIAGNOSTICS row_count = ROW_COUNT;
5802 internal_collision_count := internal_collision_count + row_count;
5805 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5808 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5810 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5812 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5813 GET DIAGNOSTICS row_count = ROW_COUNT;
5814 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5817 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5820 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5822 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5824 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5825 GET DIAGNOSTICS row_count = ROW_COUNT;
5826 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5829 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5831 $function$ LANGUAGE plpgsql;
5833 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5834 -- WARNING: Use at your own risk
5835 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5837 item_object asset.copy%ROWTYPE;
5838 user_object actor.usr%ROWTYPE;
5839 test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5840 result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5841 safe_to_delete BOOLEAN := FALSE;
5842 m action.found_circ_matrix_matchpoint;
5843 result_matchpoint INTEGER;
5844 -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5846 SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5847 RAISE INFO 'testing rule: %', test_rule_object;
5849 INSERT INTO actor.usr (
5859 COALESCE(test_rule_object.grp, 2),
5860 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5865 COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5866 COALESCE(test_rule_object.juvenile_flag, FALSE)
5869 SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5871 INSERT INTO asset.call_number (
5882 COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
5883 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5887 INSERT INTO asset.copy (
5899 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5900 COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
5902 currval('asset.call_number_id_seq'),
5904 COALESCE(test_rule_object.copy_location,1),
5907 COALESCE(test_rule_object.ref_flag,FALSE),
5908 test_rule_object.circ_modifier
5911 SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
5913 SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
5914 test_rule_object.org_unit,
5917 COALESCE(test_rule_object.is_renewal,FALSE)
5919 RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
5920 test_rule_object.org_unit,
5923 COALESCE(test_rule_object.is_renewal,FALSE),
5929 FOR result_matchpoint IN SELECT UNNEST(m.buildrows)
5931 SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint;
5932 RAISE INFO 'considering rule: %', result_rule_object;
5933 IF result_rule_object.id = test_rule_object.id THEN
5934 RAISE INFO 'found self';
5937 IF (result_rule_object.circulate = test_rule_object.circulate
5938 AND result_rule_object.duration_rule = test_rule_object.duration_rule
5939 AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule
5940 AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule
5942 (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL)
5943 OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date)
5944 OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL)
5947 (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL)
5948 OR (result_rule_object.renewals = test_rule_object.renewals)
5949 OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL)
5952 (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL)
5953 OR (result_rule_object.grace_period = test_rule_object.grace_period)
5954 OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL)
5957 SELECT limit_set, fallthrough
5958 FROM config.circ_matrix_limit_set_map
5959 WHERE active and matchpoint = test_rule_object.id
5961 SELECT limit_set, fallthrough
5962 FROM config.circ_matrix_limit_set_map
5963 WHERE active and matchpoint = result_rule_object.id
5966 RAISE INFO 'rule has same outcome';
5967 safe_to_delete := TRUE;
5969 RAISE INFO 'rule has different outcome, bail now';
5970 RAISE EXCEPTION 'rollback the item and user tables';
5974 RAISE EXCEPTION 'rollback the item and user tables';
5976 EXCEPTION WHEN OTHERS THEN
5978 RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
5979 RETURN safe_to_delete;
5982 $func$ LANGUAGE plpgsql;