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 UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.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 UNLOGGED 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.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
578 city_state_zip TEXT := $1;
583 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;
584 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
585 IF city_state_zip ~ ',' THEN
586 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
587 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
589 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
590 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
591 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
593 IF city_state_zip ~ E'^\\S+$' THEN
594 city := city_state_zip;
597 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
598 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
602 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
604 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
606 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
607 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
609 fullstring TEXT := $1;
619 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
620 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
623 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
625 IF fullstring ~ ',' THEN
626 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
627 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
629 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
630 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
631 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
633 IF fullstring ~ E'^\\S+$' THEN
634 scratch1 := fullstring;
637 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
638 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
643 IF scratch1 ~ '[\$]' THEN
644 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
645 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
647 IF scratch1 ~ '\s' THEN
648 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
649 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
656 IF scratch2 ~ '^\d' THEN
657 address1 := scratch2;
660 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
661 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
665 TRIM(BOTH ' ' FROM address1)
666 ,TRIM(BOTH ' ' FROM address2)
667 ,TRIM(BOTH ' ' FROM city)
668 ,TRIM(BOTH ' ' FROM state)
669 ,TRIM(BOTH ' ' FROM zip)
672 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
674 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
677 use Geo::StreetAddress::US;
679 my $a = Geo::StreetAddress::US->parse_location($address);
682 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
683 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
688 $$ LANGUAGE PLPERLU STABLE;
690 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
691 CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
692 INSERT INTO migration_tools.usps_suffixes VALUES
725 ('BOULEVARD','BLVD'),
829 ('EXPRESSWAY','EXPY'),
834 ('EXTENSIONS','EXTS'),
945 ('JUNCTIONS','JCTS'),
1005 ('MOTORWAY','MTWY'),
1007 ('MOUNTAINS','MTNS'),
1021 ('OVERPASS','OPAS'),
1026 ('PARKWAYS','PKWY'),
1132 ('STRAVENUE','STRA'),
1152 ('THROUGHWAY','TRWY'),
1159 ('TRAFFICWAY','TRFY'),
1178 ('TURNPIKE','TPKE'),
1180 ('UNDERPASS','UPAS'),
1194 ('VILLAGES','VLGS'),
1225 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1226 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1231 --RAISE INFO 'suffix = %', suffix;
1232 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1233 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1237 $$ LANGUAGE PLPGSQL STRICT STABLE;
1239 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1242 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1246 $$ LANGUAGE PLPGSQL STRICT STABLE;
1248 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1252 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
1253 IF o::BIGINT < t THEN
1260 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1262 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1264 migration_schema ALIAS FOR $1;
1268 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1273 $$ LANGUAGE PLPGSQL STRICT STABLE;
1275 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1277 migration_schema ALIAS FOR $1;
1281 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1286 $$ LANGUAGE PLPGSQL STRICT STABLE;
1288 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1290 migration_schema ALIAS FOR $1;
1294 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1299 $$ LANGUAGE PLPGSQL STRICT STABLE;
1301 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1303 migration_schema ALIAS FOR $1;
1307 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1312 $$ LANGUAGE PLPGSQL STRICT STABLE;
1314 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1316 migration_schema ALIAS FOR $1;
1318 patron_table ALIAS FOR $2;
1319 default_patron_profile ALIAS FOR $3;
1322 sql_where1 TEXT := '';
1323 sql_where2 TEXT := '';
1324 sql_where3 TEXT := '';
1327 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1329 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1331 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1332 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);
1333 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);
1334 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);
1335 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,'') || ';';
1336 --RAISE INFO 'sql = %', sql;
1337 PERFORM migration_tools.exec( $1, sql );
1339 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1341 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1343 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1346 $$ LANGUAGE PLPGSQL STRICT STABLE;
1348 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1350 migration_schema ALIAS FOR $1;
1352 item_table ALIAS FOR $2;
1355 sql_where1 TEXT := '';
1356 sql_where2 TEXT := '';
1357 sql_where3 TEXT := '';
1360 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1362 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1364 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 ';
1365 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);
1366 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);
1367 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);
1368 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,'') || ';';
1369 --RAISE INFO 'sql = %', sql;
1370 PERFORM migration_tools.exec( $1, sql );
1373 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1375 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1378 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1380 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1382 migration_schema ALIAS FOR $1;
1383 base_copy_location_map TEXT;
1384 item_table ALIAS FOR $2;
1387 sql_where1 TEXT := '';
1388 sql_where2 TEXT := '';
1389 sql_where3 TEXT := '';
1392 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1394 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1396 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1397 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);
1398 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);
1399 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);
1400 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,'') || ';';
1401 --RAISE INFO 'sql = %', sql;
1402 PERFORM migration_tools.exec( $1, sql );
1405 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1407 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1410 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1412 -- 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
1413 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1415 migration_schema ALIAS FOR $1;
1417 circ_table ALIAS FOR $2;
1418 item_table ALIAS FOR $3;
1419 patron_table ALIAS FOR $4;
1422 sql_where1 TEXT := '';
1423 sql_where2 TEXT := '';
1424 sql_where3 TEXT := '';
1425 sql_where4 TEXT := '';
1428 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1430 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1432 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 ';
1433 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);
1434 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);
1435 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);
1436 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);
1437 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,'') || ';';
1438 --RAISE INFO 'sql = %', sql;
1439 PERFORM migration_tools.exec( $1, sql );
1442 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1444 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1447 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1450 -- $barcode source barcode
1451 -- $prefix prefix to add to barcode, NULL = add no prefix
1452 -- $maxlen maximum length of barcode; default to 14 if left NULL
1453 -- $pad padding string to apply to left of source barcode before adding
1454 -- prefix and suffix; set to NULL or '' if no padding is desired
1455 -- $suffix suffix to add to barcode, NULL = add no suffix
1457 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1458 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1460 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1461 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1464 return unless defined $barcode;
1466 $prefix = '' unless defined $prefix;
1468 $pad = '0' unless defined $pad;
1469 $suffix = '' unless defined $suffix;
1471 # bail out if adding prefix and suffix would bring new barcode over max length
1472 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1474 my $new_barcode = $barcode;
1476 my $pad_length = $maxlen - length($prefix) - length($suffix);
1477 if (length($barcode) < $pad_length) {
1478 # assuming we always want padding on the left
1479 # also assuming that it is possible to have the pad string be longer than 1 character
1480 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1484 # bail out if adding prefix and suffix would bring new barcode over max length
1485 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1487 return "$prefix$new_barcode$suffix";
1488 $$ LANGUAGE PLPERLU STABLE;
1490 -- remove previous version of this function
1491 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1493 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1495 attempt_value ALIAS FOR $1;
1496 datatype ALIAS FOR $2;
1498 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1499 RETURN attempt_value;
1501 WHEN OTHERS THEN RETURN NULL;
1503 $$ LANGUAGE PLPGSQL STRICT STABLE;
1505 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1507 attempt_value ALIAS FOR $1;
1508 fail_value ALIAS FOR $2;
1512 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1519 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1524 $$ LANGUAGE PLPGSQL STRICT STABLE;
1526 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1528 attempt_value ALIAS FOR $1;
1529 fail_value ALIAS FOR $2;
1533 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1540 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1545 $$ LANGUAGE PLPGSQL STRICT STABLE;
1547 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1549 attempt_value ALIAS FOR $1;
1550 fail_value ALIAS FOR $2;
1554 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1561 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1566 $$ LANGUAGE PLPGSQL STRICT STABLE;
1568 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1570 attempt_value ALIAS FOR $1;
1571 fail_value ALIAS FOR $2;
1574 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1579 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1584 $$ LANGUAGE PLPGSQL STRICT STABLE;
1586 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1588 attempt_value ALIAS FOR $1;
1589 fail_value ALIAS FOR $2;
1590 output NUMERIC(8,2);
1593 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1600 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1605 $$ LANGUAGE PLPGSQL STRICT STABLE;
1607 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1609 attempt_value ALIAS FOR $1;
1610 fail_value ALIAS FOR $2;
1611 output NUMERIC(6,2);
1614 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1621 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1626 $$ LANGUAGE PLPGSQL STRICT STABLE;
1628 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1630 attempt_value ALIAS FOR $1;
1631 fail_value ALIAS FOR $2;
1632 output NUMERIC(8,2);
1634 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1635 RAISE EXCEPTION 'too many digits';
1638 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;'
1645 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1650 $$ LANGUAGE PLPGSQL STRICT STABLE;
1652 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1654 attempt_value ALIAS FOR $1;
1655 fail_value ALIAS FOR $2;
1656 output NUMERIC(6,2);
1658 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1659 RAISE EXCEPTION 'too many digits';
1662 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;'
1669 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1674 $$ LANGUAGE PLPGSQL STRICT STABLE;
1676 -- add_codabar_checkdigit
1677 -- $barcode source barcode
1679 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1680 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1681 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1682 -- input string does not meet those requirements, it is returned unchanged.
1684 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1685 my $barcode = shift;
1687 return $barcode if $barcode !~ /^\d{13,14}$/;
1688 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1689 my @digits = split //, $barcode;
1691 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1692 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1693 my $remainder = $total % 10;
1694 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1695 return $barcode . $checkdigit;
1696 $$ LANGUAGE PLPERLU STRICT STABLE;
1698 -- add_code39mod43_checkdigit
1699 -- $barcode source barcode
1701 -- If the source string is 13 or 14 characters long and contains only valid
1702 -- Code 39 mod 43 characters, adds or replaces the 14th
1703 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1704 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1705 -- input string does not meet those requirements, it is returned unchanged.
1707 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1708 my $barcode = shift;
1710 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1711 $barcode = substr($barcode, 0, 13); # ignore 14th character
1713 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1714 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1717 $total += $nums{$_} foreach split(//, $barcode);
1718 my $remainder = $total % 43;
1719 my $checkdigit = $valid_chars[$remainder];
1720 return $barcode . $checkdigit;
1721 $$ LANGUAGE PLPERLU STRICT STABLE;
1723 -- add_mod16_checkdigit
1724 -- $barcode source barcode
1726 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1728 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1729 my $barcode = shift;
1731 my @digits = split //, $barcode;
1733 foreach $digit (@digits) {
1734 if ($digit =~ /[0-9]/) { $total += $digit;
1735 } elsif ($digit eq '-') { $total += 10;
1736 } elsif ($digit eq '$') { $total += 11;
1737 } elsif ($digit eq ':') { $total += 12;
1738 } elsif ($digit eq '/') { $total += 13;
1739 } elsif ($digit eq '.') { $total += 14;
1740 } elsif ($digit eq '+') { $total += 15;
1741 } elsif ($digit eq 'A') { $total += 16;
1742 } elsif ($digit eq 'B') { $total += 17;
1743 } elsif ($digit eq 'C') { $total += 18;
1744 } elsif ($digit eq 'D') { $total += 19;
1745 } else { die "invalid digit <$digit>";
1748 my $remainder = $total % 16;
1749 my $difference = 16 - $remainder;
1751 if ($difference < 10) { $checkdigit = $difference;
1752 } elsif ($difference == 10) { $checkdigit = '-';
1753 } elsif ($difference == 11) { $checkdigit = '$';
1754 } elsif ($difference == 12) { $checkdigit = ':';
1755 } elsif ($difference == 13) { $checkdigit = '/';
1756 } elsif ($difference == 14) { $checkdigit = '.';
1757 } elsif ($difference == 15) { $checkdigit = '+';
1758 } else { die "error calculating checkdigit";
1761 return $barcode . $checkdigit;
1762 $$ LANGUAGE PLPERLU STRICT STABLE;
1764 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1767 areacode TEXT := $2;
1770 n_digits INTEGER := 0;
1773 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1774 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1775 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1776 IF n_digits = 7 AND areacode <> '' THEN
1777 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1778 output := (areacode || '-' || temp);
1785 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1787 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1788 my ($marcxml, $pos, $value) = @_;
1791 use MARC::File::XML;
1795 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1796 my $leader = $marc->leader();
1797 substr($leader, $pos, 1) = $value;
1798 $marc->leader($leader);
1799 $xml = $marc->as_xml_record;
1800 $xml =~ s/^<\?.+?\?>$//mo;
1802 $xml =~ s/>\s+</></sgo;
1805 $$ LANGUAGE PLPERLU STABLE;
1807 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1808 my ($marcxml, $pos, $value) = @_;
1811 use MARC::File::XML;
1815 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1816 my $f008 = $marc->field('008');
1819 my $field = $f008->data();
1820 substr($field, $pos, 1) = $value;
1821 $f008->update($field);
1822 $xml = $marc->as_xml_record;
1823 $xml =~ s/^<\?.+?\?>$//mo;
1825 $xml =~ s/>\s+</></sgo;
1829 $$ LANGUAGE PLPERLU STABLE;
1832 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1834 profile ALIAS FOR $1;
1836 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1838 $$ LANGUAGE PLPGSQL STRICT STABLE;
1841 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1843 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1845 $$ LANGUAGE PLPGSQL STRICT STABLE;
1848 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1850 my ($marcxml, $tags) = @_;
1853 use MARC::File::XML;
1858 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1859 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1861 my @incumbents = ();
1863 foreach my $field ( $marc->fields() ) {
1864 push @incumbents, $field->as_formatted();
1867 foreach $field ( $to_insert->fields() ) {
1868 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1869 $marc->insert_fields_ordered( ($field) );
1873 $xml = $marc->as_xml_record;
1874 $xml =~ s/^<\?.+?\?>$//mo;
1876 $xml =~ s/>\s+</></sgo;
1881 $$ LANGUAGE PLPERLU STABLE;
1883 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1887 -- First make sure the circ matrix is loaded and the circulations
1888 -- have been staged to the extent possible (but at the very least
1889 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1890 -- circ modifiers must also be in place.
1892 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1900 this_duration_rule INT;
1902 this_max_fine_rule INT;
1903 rcd config.rule_circ_duration%ROWTYPE;
1904 rrf config.rule_recurring_fine%ROWTYPE;
1905 rmf config.rule_max_fine%ROWTYPE;
1912 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1914 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1916 -- Fetch the correct rules for this circulation
1923 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1926 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1927 INTO circ_lib, target_copy, usr, is_renewal ;
1929 INTO this_duration_rule,
1933 recurring_fine_rule,
1935 FROM action.item_user_circ_test(
1941 SELECT INTO rcd * FROM config.rule_circ_duration
1942 WHERE id = this_duration_rule;
1943 SELECT INTO rrf * FROM config.rule_recurring_fine
1944 WHERE id = this_fine_rule;
1945 SELECT INTO rmf * FROM config.rule_max_fine
1946 WHERE id = this_max_fine_rule;
1948 -- Apply the rules to this circulation
1949 EXECUTE ('UPDATE ' || tablename || ' c
1951 duration_rule = rcd.name,
1952 recurring_fine_rule = rrf.name,
1953 max_fine_rule = rmf.name,
1954 duration = rcd.normal,
1955 recurring_fine = rrf.normal,
1958 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1961 renewal_remaining = rcd.max_renewals
1963 config.rule_circ_duration rcd,
1964 config.rule_recurring_fine rrf,
1965 config.rule_max_fine rmf,
1968 rcd.id = ' || this_duration_rule || ' AND
1969 rrf.id = ' || this_fine_rule || ' AND
1970 rmf.id = ' || this_max_fine_rule || ' AND
1971 ac.id = c.target_copy AND
1972 c.id = ' || circ || ';');
1974 -- Keep track of where we are in the process
1976 IF (n % 100 = 0) THEN
1977 RAISE INFO '%', n || ' of ' || n_circs
1978 || ' (' || (100*n/n_circs) || '%) circs updated.';
1986 $$ LANGUAGE plpgsql;
1988 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1992 -- First make sure the circ matrix is loaded and the circulations
1993 -- have been staged to the extent possible (but at the very least
1994 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1995 -- circ modifiers must also be in place.
1997 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2005 this_duration_rule INT;
2007 this_max_fine_rule INT;
2008 rcd config.rule_circ_duration%ROWTYPE;
2009 rrf config.rule_recurring_fine%ROWTYPE;
2010 rmf config.rule_max_fine%ROWTYPE;
2017 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2019 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2021 -- Fetch the correct rules for this circulation
2028 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2031 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2032 INTO circ_lib, target_copy, usr, is_renewal ;
2034 INTO this_duration_rule,
2040 FROM action.find_circ_matrix_matchpoint(
2046 SELECT INTO rcd * FROM config.rule_circ_duration
2047 WHERE id = this_duration_rule;
2048 SELECT INTO rrf * FROM config.rule_recurring_fine
2049 WHERE id = this_fine_rule;
2050 SELECT INTO rmf * FROM config.rule_max_fine
2051 WHERE id = this_max_fine_rule;
2053 -- Apply the rules to this circulation
2054 EXECUTE ('UPDATE ' || tablename || ' c
2056 duration_rule = rcd.name,
2057 recuring_fine_rule = rrf.name,
2058 max_fine_rule = rmf.name,
2059 duration = rcd.normal,
2060 recuring_fine = rrf.normal,
2063 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2066 renewal_remaining = rcd.max_renewals
2068 config.rule_circ_duration rcd,
2069 config.rule_recuring_fine rrf,
2070 config.rule_max_fine rmf,
2073 rcd.id = ' || this_duration_rule || ' AND
2074 rrf.id = ' || this_fine_rule || ' AND
2075 rmf.id = ' || this_max_fine_rule || ' AND
2076 ac.id = c.target_copy AND
2077 c.id = ' || circ || ';');
2079 -- Keep track of where we are in the process
2081 IF (n % 100 = 0) THEN
2082 RAISE INFO '%', n || ' of ' || n_circs
2083 || ' (' || (100*n/n_circs) || '%) circs updated.';
2091 $$ LANGUAGE plpgsql;
2093 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2097 -- First make sure the circ matrix is loaded and the circulations
2098 -- have been staged to the extent possible (but at the very least
2099 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2100 -- circ modifiers must also be in place.
2102 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2110 this_duration_rule INT;
2112 this_max_fine_rule INT;
2113 rcd config.rule_circ_duration%ROWTYPE;
2114 rrf config.rule_recurring_fine%ROWTYPE;
2115 rmf config.rule_max_fine%ROWTYPE;
2122 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2124 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2126 -- Fetch the correct rules for this circulation
2133 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2136 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2137 INTO circ_lib, target_copy, usr, is_renewal ;
2139 INTO this_duration_rule,
2142 (matchpoint).duration_rule,
2143 (matchpoint).recurring_fine_rule,
2144 (matchpoint).max_fine_rule
2145 FROM action.find_circ_matrix_matchpoint(
2151 SELECT INTO rcd * FROM config.rule_circ_duration
2152 WHERE id = this_duration_rule;
2153 SELECT INTO rrf * FROM config.rule_recurring_fine
2154 WHERE id = this_fine_rule;
2155 SELECT INTO rmf * FROM config.rule_max_fine
2156 WHERE id = this_max_fine_rule;
2158 -- Apply the rules to this circulation
2159 EXECUTE ('UPDATE ' || tablename || ' c
2161 duration_rule = rcd.name,
2162 recurring_fine_rule = rrf.name,
2163 max_fine_rule = rmf.name,
2164 duration = rcd.normal,
2165 recurring_fine = rrf.normal,
2168 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2171 renewal_remaining = rcd.max_renewals,
2172 grace_period = rrf.grace_period
2174 config.rule_circ_duration rcd,
2175 config.rule_recurring_fine rrf,
2176 config.rule_max_fine rmf,
2179 rcd.id = ' || this_duration_rule || ' AND
2180 rrf.id = ' || this_fine_rule || ' AND
2181 rmf.id = ' || this_max_fine_rule || ' AND
2182 ac.id = c.target_copy AND
2183 c.id = ' || circ || ';');
2185 -- Keep track of where we are in the process
2187 IF (n % 100 = 0) THEN
2188 RAISE INFO '%', n || ' of ' || n_circs
2189 || ' (' || (100*n/n_circs) || '%) circs updated.';
2197 $$ LANGUAGE plpgsql;
2199 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2200 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2201 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2204 charge_lost_on_zero BOOLEAN;
2207 default_price NUMERIC;
2208 working_price NUMERIC;
2212 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2213 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2215 SELECT INTO charge_lost_on_zero value
2216 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2218 SELECT INTO min_price value
2219 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2221 SELECT INTO max_price value
2222 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2224 SELECT INTO default_price value
2225 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2227 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2229 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2230 working_price := default_price;
2233 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2234 working_price := max_price;
2237 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2238 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2239 working_price := min_price;
2243 RETURN working_price;
2247 $$ LANGUAGE plpgsql;
2249 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2253 -- First make sure the circ matrix is loaded and the circulations
2254 -- have been staged to the extent possible (but at the very least
2255 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2256 -- circ modifiers must also be in place.
2258 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2266 this_duration_rule INT;
2268 this_max_fine_rule INT;
2269 rcd config.rule_circ_duration%ROWTYPE;
2270 rrf config.rule_recurring_fine%ROWTYPE;
2271 rmf config.rule_max_fine%ROWTYPE;
2277 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2279 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2281 -- Fetch the correct rules for this circulation
2288 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2291 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2292 INTO circ_lib, target_copy, usr, is_renewal ;
2294 INTO this_duration_rule,
2297 (matchpoint).duration_rule,
2298 (matchpoint).recurring_fine_rule,
2299 (matchpoint).max_fine_rule
2300 FROM action.find_circ_matrix_matchpoint(
2306 SELECT INTO rcd * FROM config.rule_circ_duration
2307 WHERE id = this_duration_rule;
2308 SELECT INTO rrf * FROM config.rule_recurring_fine
2309 WHERE id = this_fine_rule;
2310 SELECT INTO rmf * FROM config.rule_max_fine
2311 WHERE id = this_max_fine_rule;
2313 -- Apply the rules to this circulation
2314 EXECUTE ('UPDATE ' || tablename || ' c
2316 duration_rule = rcd.name,
2317 recurring_fine_rule = rrf.name,
2318 max_fine_rule = rmf.name,
2319 duration = rcd.normal,
2320 recurring_fine = rrf.normal,
2323 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2326 renewal_remaining = rcd.max_renewals,
2327 grace_period = rrf.grace_period
2329 config.rule_circ_duration rcd,
2330 config.rule_recurring_fine rrf,
2331 config.rule_max_fine rmf,
2334 rcd.id = ' || this_duration_rule || ' AND
2335 rrf.id = ' || this_fine_rule || ' AND
2336 rmf.id = ' || this_max_fine_rule || ' AND
2337 ac.id = c.target_copy AND
2338 c.id = ' || circ || ';');
2340 -- Keep track of where we are in the process
2342 IF (n % 100 = 0) THEN
2343 RAISE INFO '%', n || ' of ' || n_circs
2344 || ' (' || (100*n/n_circs) || '%) circs updated.';
2352 $$ LANGUAGE plpgsql;
2357 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2359 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2360 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2362 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2363 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2366 c TEXT := schemaname || '.asset_copy_legacy';
2367 sc TEXT := schemaname || '.asset_stat_cat';
2368 sce TEXT := schemaname || '.asset_stat_cat_entry';
2369 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2375 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2377 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2379 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2380 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2381 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2388 $$ LANGUAGE plpgsql;
2390 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2392 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2393 -- This will assign standing penalties as needed.
2401 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2403 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2405 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2415 $$ LANGUAGE plpgsql;
2418 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2421 INSERT INTO metabib.metarecord (fingerprint, master_record)
2422 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2423 FROM biblio.record_entry b
2425 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)
2426 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2427 ORDER BY b.fingerprint, b.quality DESC;
2428 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2430 FROM biblio.record_entry r
2431 JOIN metabib.metarecord m USING (fingerprint)
2432 WHERE NOT r.deleted;
2435 $$ LANGUAGE plpgsql;
2438 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2441 INSERT INTO metabib.metarecord (fingerprint, master_record)
2442 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2443 FROM biblio.record_entry b
2445 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)
2446 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2447 ORDER BY b.fingerprint, b.quality DESC;
2448 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2450 FROM biblio.record_entry r
2451 JOIN metabib.metarecord m USING (fingerprint)
2453 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);
2456 $$ LANGUAGE plpgsql;
2459 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2461 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2462 -- Then SELECT migration_tools.create_cards('m_foo');
2465 u TEXT := schemaname || '.actor_usr_legacy';
2466 c TEXT := schemaname || '.actor_card';
2470 EXECUTE ('DELETE FROM ' || c || ';');
2471 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2472 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2478 $$ LANGUAGE plpgsql;
2481 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2483 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2485 my ($marcxml, $shortname) = @_;
2488 use MARC::File::XML;
2493 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2495 foreach my $field ( $marc->field('856') ) {
2496 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2497 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2498 $field->add_subfields( '9' => $shortname );
2499 $field->update( ind2 => '0');
2503 $xml = $marc->as_xml_record;
2504 $xml =~ s/^<\?.+?\?>$//mo;
2506 $xml =~ s/>\s+</></sgo;
2511 $$ LANGUAGE PLPERLU STABLE;
2513 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2515 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2517 my ($marcxml, $shortname) = @_;
2520 use MARC::File::XML;
2525 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2527 foreach my $field ( $marc->field('856') ) {
2528 if ( ! $field->as_string('9') ) {
2529 $field->add_subfields( '9' => $shortname );
2533 $xml = $marc->as_xml_record;
2534 $xml =~ s/^<\?.+?\?>$//mo;
2536 $xml =~ s/>\s+</></sgo;
2541 $$ LANGUAGE PLPERLU STABLE;
2544 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2556 -- Bail out if asked to change the label to ##URI##
2557 IF new_label = '##URI##' THEN
2561 -- Gather information
2562 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2563 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2564 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2566 -- Bail out if the label already is ##URI##
2567 IF old_label = '##URI##' THEN
2571 -- Bail out if the call number label is already correct
2572 IF new_volume = old_volume THEN
2576 -- Check whether we already have a destination volume available
2577 SELECT id INTO new_volume FROM asset.call_number
2580 owning_lib = owner AND
2581 label = new_label AND
2584 -- Create destination volume if needed
2586 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2587 VALUES (1, 1, bib, owner, new_label, cn_class);
2588 SELECT id INTO new_volume FROM asset.call_number
2591 owning_lib = owner AND
2592 label = new_label AND
2596 -- Move copy to destination
2597 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2599 -- Delete source volume if it is now empty
2600 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2602 DELETE FROM asset.call_number WHERE id = old_volume;
2607 $$ LANGUAGE plpgsql;
2609 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2614 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2618 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2619 $zipdata{$zip} = [$city, $state, $county];
2622 if (defined $zipdata{$input}) {
2623 my ($city, $state, $county) = @{$zipdata{$input}};
2624 return [$city, $state, $county];
2625 } elsif (defined $zipdata{substr $input, 0, 5}) {
2626 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2627 return [$city, $state, $county];
2629 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2632 $$ LANGUAGE PLPERLU STABLE;
2634 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2641 errors_found BOOLEAN;
2643 parent_shortname TEXT;
2649 type_parent_depth INT;
2654 errors_found := FALSE;
2656 -- Checking actor.org_unit_type
2658 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2660 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2661 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2663 IF type_parent IS NOT NULL THEN
2665 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2667 IF type_depth - type_parent_depth <> 1 THEN
2668 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2669 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2670 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2671 ou_type_name, type_depth, parent_type, type_parent_depth;
2672 errors_found := TRUE;
2680 -- Checking actor.org_unit
2682 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2684 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2685 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;
2686 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;
2687 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2688 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2689 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;
2690 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;
2692 IF ou_parent IS NOT NULL THEN
2694 IF (org_unit_depth - parent_depth <> 1) OR (
2695 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2697 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2698 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2699 errors_found := TRUE;
2706 IF NOT errors_found THEN
2707 RAISE INFO 'No errors found.';
2714 $$ LANGUAGE plpgsql;
2717 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2721 DELETE FROM asset.opac_visible_copies;
2723 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2725 cp.id, cp.circ_lib, cn.record
2728 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2729 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2730 JOIN asset.copy_location cl ON (cp.location = cl.id)
2731 JOIN config.copy_status cs ON (cp.status = cs.id)
2732 JOIN biblio.record_entry b ON (cn.record = b.id)
2741 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2745 $$ LANGUAGE plpgsql;
2748 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2754 old_owning_lib INTEGER;
2760 -- Gather information
2761 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2762 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2763 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2765 -- Bail out if the new_owning_lib is not the ID of an org_unit
2766 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2768 '% is not a valid actor.org_unit ID; no change made.',
2773 -- Bail out discreetly if the owning_lib is already correct
2774 IF new_owning_lib = old_owning_lib THEN
2778 -- Check whether we already have a destination volume available
2779 SELECT id INTO new_volume FROM asset.call_number
2782 owning_lib = new_owning_lib AND
2783 label = old_label AND
2786 -- Create destination volume if needed
2788 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2789 VALUES (1, 1, bib, new_owning_lib, old_label);
2790 SELECT id INTO new_volume FROM asset.call_number
2793 owning_lib = new_owning_lib AND
2794 label = old_label AND
2798 -- Move copy to destination
2799 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2801 -- Delete source volume if it is now empty
2802 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2804 DELETE FROM asset.call_number WHERE id = old_volume;
2809 $$ LANGUAGE plpgsql;
2812 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2814 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2817 new_owning_lib INTEGER;
2821 -- Parse the new_owner as an org unit ID or shortname
2822 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2823 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2824 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2825 ELSIF new_owner ~ E'^[0-9]+$' THEN
2826 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2829 E'You don\'t need to put the actor.org_unit ID in quotes; '
2830 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2831 new_owning_lib := new_owner::INTEGER;
2832 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2836 '% is not a valid actor.org_unit shortname or ID; no change made.',
2843 $$ LANGUAGE plpgsql;
2845 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2848 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2851 MARC::Charset->assume_unicode(1);
2856 my $r = MARC::Record->new_from_xml( $xml );
2857 my $output_xml = $r->as_xml_record();
2865 $func$ LANGUAGE PLPERLU;
2866 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2868 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2870 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2871 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2872 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2873 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2874 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2875 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2876 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2877 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2878 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2879 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2880 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2881 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2882 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2883 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2884 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2885 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2886 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2887 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2888 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2889 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2890 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2891 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2892 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2893 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2894 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2895 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2897 $FUNC$ LANGUAGE PLPGSQL;
2899 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2901 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2902 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2903 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2904 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2905 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2906 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2907 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2909 -- import any new circ rules
2910 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2911 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2912 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2913 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2915 -- and permission groups
2916 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2919 $FUNC$ LANGUAGE PLPGSQL;
2922 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$
2931 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2932 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2933 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2934 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2935 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2936 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2937 FOR name IN EXECUTE loopq LOOP
2938 EXECUTE existsq INTO ct USING name;
2940 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2941 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2942 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2943 EXECUTE copyst USING name;
2947 $FUNC$ LANGUAGE PLPGSQL;
2949 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2956 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2957 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;
2958 FOR id IN EXECUTE loopq USING delimiter LOOP
2959 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2960 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2961 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2962 EXECUTE splitst USING id, delimiter;
2965 $FUNC$ LANGUAGE PLPGSQL;
2967 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2973 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2976 MARC::Charset->assume_unicode(1);
2978 my $target_xml = shift;
2979 my $source_xml = shift;
2985 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2989 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2994 my $source_id = $source->subfield('901', 'c');
2995 $source_id = $source->subfield('903', 'a') unless $source_id;
2996 my $target_id = $target->subfield('901', 'c');
2997 $target_id = $target->subfield('903', 'a') unless $target_id;
2999 my %existing_fields;
3000 foreach my $tag (@$tags) {
3001 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3002 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3003 $target->insert_fields_ordered(map { $_->clone() } @to_add);
3005 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3009 my $xml = $target->as_xml_record;
3010 $xml =~ s/^<\?.+?\?>$//mo;
3012 $xml =~ s/>\s+</></sgo;
3016 $func$ LANGUAGE PLPERLU;
3017 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.';
3019 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3025 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3028 my $in_tags = shift;
3029 my $in_values = shift;
3031 # hack-and-slash parsing of array-passed-as-string;
3032 # this can go away once everybody is running Postgres 9.1+
3033 my $csv = Text::CSV->new({binary => 1});
3036 my $status = $csv->parse($in_tags);
3037 my $tags = [ $csv->fields() ];
3038 $in_values =~ s/^{//;
3039 $in_values =~ s/}$//;
3040 $status = $csv->parse($in_values);
3041 my $values = [ $csv->fields() ];
3043 my $marc = MARC::Record->new();
3045 $marc->leader('00000nam a22000007 4500');
3046 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
3048 foreach my $i (0..$#$tags) {
3050 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3053 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3054 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3056 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3060 my $xml = $marc->as_xml_record;
3061 $xml =~ s/^<\?.+?\?>$//mo;
3063 $xml =~ s/>\s+</></sgo;
3067 $func$ LANGUAGE PLPERLU;
3068 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3069 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3070 The second argument is an array of text containing the values to plug into each field.
3071 If the value for a given field is NULL or the empty string, it is not inserted.
3074 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3076 my ($marcxml, $tag, $pos, $value) = @_;
3079 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3083 MARC::Charset->assume_unicode(1);
3085 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3086 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3087 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3088 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3092 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3094 foreach my $field ($marc->field($tag)) {
3095 $field->update("ind$pos" => $value);
3097 $xml = $marc->as_xml_record;
3098 $xml =~ s/^<\?.+?\?>$//mo;
3100 $xml =~ s/>\s+</></sgo;
3104 $func$ LANGUAGE PLPERLU;
3106 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3107 The first argument is a MARCXML string.
3108 The second argument is a MARC tag.
3109 The third argument is the indicator position, either 1 or 2.
3110 The fourth argument is the character to set the indicator value to.
3111 All occurences of the specified field will be changed.
3112 The function returns the revised MARCXML string.$$;
3114 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3119 first_name TEXT DEFAULT '',
3120 last_name TEXT DEFAULT ''
3121 ) RETURNS VOID AS $func$
3123 RAISE NOTICE '%', org ;
3124 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3125 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3126 FROM actor.org_unit aou, permission.grp_tree pgt
3127 WHERE aou.shortname = org
3128 AND pgt.name = perm_group;
3133 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3134 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3136 target_event_def ALIAS FOR $1;
3139 DROP TABLE IF EXISTS new_atevdefs;
3140 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3141 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3142 INSERT INTO action_trigger.event_definition (
3163 ,name || ' (clone of '||target_event_def||')'
3179 action_trigger.event_definition
3181 id = target_event_def
3183 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3184 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3185 INSERT INTO action_trigger.environment (
3191 currval('action_trigger.event_definition_id_seq')
3196 action_trigger.environment
3198 event_def = target_event_def
3200 INSERT INTO action_trigger.event_params (
3205 currval('action_trigger.event_definition_id_seq')
3209 action_trigger.event_params
3211 event_def = target_event_def
3214 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);
3216 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3218 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3219 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3221 target_event_def ALIAS FOR $1;
3223 new_interval ALIAS FOR $3;
3225 DROP TABLE IF EXISTS new_atevdefs;
3226 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3227 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3228 INSERT INTO action_trigger.event_definition (
3249 ,name || ' (clone of '||target_event_def||')'
3265 action_trigger.event_definition
3267 id = target_event_def
3269 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3270 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3271 INSERT INTO action_trigger.environment (
3277 currval('action_trigger.event_definition_id_seq')
3282 action_trigger.environment
3284 event_def = target_event_def
3286 INSERT INTO action_trigger.event_params (
3291 currval('action_trigger.event_definition_id_seq')
3295 action_trigger.event_params
3297 event_def = target_event_def
3300 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);
3302 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3304 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3305 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3308 target_event_defs ALIAS FOR $2;
3310 DROP TABLE IF EXISTS new_atevdefs;
3311 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3312 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3313 INSERT INTO action_trigger.event_definition (
3334 ,name || ' (clone of '||target_event_defs[i]||')'
3350 action_trigger.event_definition
3352 id = target_event_defs[i]
3354 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3355 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3356 INSERT INTO action_trigger.environment (
3362 currval('action_trigger.event_definition_id_seq')
3367 action_trigger.environment
3369 event_def = target_event_defs[i]
3371 INSERT INTO action_trigger.event_params (
3376 currval('action_trigger.event_definition_id_seq')
3380 action_trigger.event_params
3382 event_def = target_event_defs[i]
3385 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3387 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3389 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3391 action_trigger.event
3395 ,complete_time = NULL
3396 ,update_process = NULL
3398 ,template_output = NULL
3399 ,error_output = NULL
3400 ,async_output = NULL
3405 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3409 use MARC::File::XML;
3414 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3415 $field = $marc->leader();
3418 $$ LANGUAGE PLPERLU STABLE;
3420 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3421 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3424 use MARC::File::XML;
3429 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3430 $field = $marc->field($tag);
3432 return $field->as_string($subfield,$delimiter);
3433 $$ LANGUAGE PLPERLU STABLE;
3435 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3436 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3439 use MARC::File::XML;
3444 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3445 @fields = $marc->field($tag);
3448 foreach my $field (@fields) {
3449 push @texts, $field->as_string($subfield,$delimiter);
3452 $$ LANGUAGE PLPERLU STABLE;
3454 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3455 SELECT action.find_hold_matrix_matchpoint(
3456 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3457 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3458 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3459 (SELECT usr FROM action.hold_request WHERE id = $1),
3460 (SELECT requestor FROM action.hold_request WHERE id = $1)
3464 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3465 SELECT action.hold_request_permit_test(
3466 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3467 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3468 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3469 (SELECT usr FROM action.hold_request WHERE id = $1),
3470 (SELECT requestor FROM action.hold_request WHERE id = $1)
3474 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3475 SELECT action.find_circ_matrix_matchpoint(
3476 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3477 (SELECT target_copy FROM action.circulation WHERE id = $1),
3478 (SELECT usr FROM action.circulation WHERE id = $1),
3480 NULLIF(phone_renewal,false),
3481 NULLIF(desk_renewal,false),
3482 NULLIF(opac_renewal,false),
3484 ) FROM action.circulation WHERE id = $1
3489 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3494 RAISE EXCEPTION 'assertion';
3497 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3499 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3505 RAISE EXCEPTION '%', msg;
3508 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3510 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3513 fail_msg ALIAS FOR $2;
3514 success_msg ALIAS FOR $3;
3517 RAISE EXCEPTION '%', fail_msg;
3521 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3523 -- push bib sequence and return starting value for reserved range
3524 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3526 bib_count ALIAS FOR $1;
3529 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3531 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3536 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3538 -- set a new salted password
3540 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3542 usr_id ALIAS FOR $1;
3543 plain_passwd ALIAS FOR $2;
3548 SELECT actor.create_salt('main') INTO plain_salt;
3550 SELECT MD5(plain_passwd) INTO md5_passwd;
3552 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3557 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3560 -- convenience functions for handling copy_location maps
3562 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3564 table_schema ALIAS FOR $1;
3565 table_name ALIAS FOR $2;
3566 org_shortname ALIAS FOR $3;
3567 org_range ALIAS FOR $4;
3570 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3571 -- though we'll still use the passed org for the full path traversal when needed
3572 x_org_found BOOLEAN;
3577 EXECUTE 'SELECT EXISTS (
3579 FROM information_schema.columns
3580 WHERE table_schema = $1
3582 and column_name = ''desired_shelf''
3583 )' INTO proceed USING table_schema, table_name;
3585 RAISE EXCEPTION 'Missing column desired_shelf';
3588 EXECUTE 'SELECT EXISTS (
3590 FROM information_schema.columns
3591 WHERE table_schema = $1
3593 and column_name = ''x_org''
3594 )' INTO x_org_found USING table_schema, table_name;
3596 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3598 RAISE EXCEPTION 'Cannot find org by shortname';
3601 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3603 EXECUTE 'ALTER TABLE '
3604 || quote_ident(table_name)
3605 || ' DROP COLUMN IF EXISTS x_shelf';
3606 EXECUTE 'ALTER TABLE '
3607 || quote_ident(table_name)
3608 || ' ADD COLUMN x_shelf INTEGER';
3611 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3612 || ' SET x_shelf = id FROM asset_copy_location b'
3613 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3614 || ' AND b.owning_lib = x_org'
3615 || ' AND NOT b.deleted';
3616 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3617 || ' SET x_shelf = id FROM asset.copy_location b'
3618 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3619 || ' AND b.owning_lib = x_org'
3620 || ' AND x_shelf IS NULL'
3621 || ' AND NOT b.deleted';
3623 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3624 || ' SET x_shelf = id FROM asset_copy_location b'
3625 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3626 || ' AND b.owning_lib = $1'
3627 || ' AND NOT b.deleted'
3629 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3630 || ' SET x_shelf = id FROM asset_copy_location b'
3631 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3632 || ' AND b.owning_lib = $1'
3633 || ' AND x_shelf IS NULL'
3634 || ' AND NOT b.deleted'
3638 FOREACH o IN ARRAY org_list LOOP
3639 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3640 || ' SET x_shelf = id FROM asset.copy_location b'
3641 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3642 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3643 || ' AND NOT b.deleted'
3647 EXECUTE 'SELECT migration_tools.assert(
3648 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3649 ''Cannot find a desired location'',
3650 ''Found all desired locations''
3654 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3656 -- convenience functions for handling circmod maps
3658 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3660 table_schema ALIAS FOR $1;
3661 table_name ALIAS FOR $2;
3664 EXECUTE 'SELECT EXISTS (
3666 FROM information_schema.columns
3667 WHERE table_schema = $1
3669 and column_name = ''desired_circmod''
3670 )' INTO proceed USING table_schema, table_name;
3672 RAISE EXCEPTION 'Missing column desired_circmod';
3675 EXECUTE 'ALTER TABLE '
3676 || quote_ident(table_name)
3677 || ' DROP COLUMN IF EXISTS x_circmod';
3678 EXECUTE 'ALTER TABLE '
3679 || quote_ident(table_name)
3680 || ' ADD COLUMN x_circmod TEXT';
3682 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3683 || ' SET x_circmod = code FROM config.circ_modifier b'
3684 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3686 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3687 || ' SET x_circmod = code FROM config.circ_modifier b'
3688 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3689 || ' AND x_circmod IS NULL';
3691 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3692 || ' SET x_circmod = code FROM config.circ_modifier b'
3693 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3694 || ' AND x_circmod IS NULL';
3696 EXECUTE 'SELECT migration_tools.assert(
3697 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3698 ''Cannot find a desired circulation modifier'',
3699 ''Found all desired circulation modifiers''
3703 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3705 -- convenience functions for handling item status maps
3707 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3709 table_schema ALIAS FOR $1;
3710 table_name ALIAS FOR $2;
3713 EXECUTE 'SELECT EXISTS (
3715 FROM information_schema.columns
3716 WHERE table_schema = $1
3718 and column_name = ''desired_status''
3719 )' INTO proceed USING table_schema, table_name;
3721 RAISE EXCEPTION 'Missing column desired_status';
3724 EXECUTE 'ALTER TABLE '
3725 || quote_ident(table_name)
3726 || ' DROP COLUMN IF EXISTS x_status';
3727 EXECUTE 'ALTER TABLE '
3728 || quote_ident(table_name)
3729 || ' ADD COLUMN x_status INTEGER';
3731 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3732 || ' SET x_status = id FROM config.copy_status b'
3733 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3735 EXECUTE 'SELECT migration_tools.assert(
3736 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3737 ''Cannot find a desired copy status'',
3738 ''Found all desired copy statuses''
3742 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3744 -- convenience functions for handling org maps
3746 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3748 table_schema ALIAS FOR $1;
3749 table_name ALIAS FOR $2;
3752 EXECUTE 'SELECT EXISTS (
3754 FROM information_schema.columns
3755 WHERE table_schema = $1
3757 and column_name = ''desired_org''
3758 )' INTO proceed USING table_schema, table_name;
3760 RAISE EXCEPTION 'Missing column desired_org';
3763 EXECUTE 'ALTER TABLE '
3764 || quote_ident(table_name)
3765 || ' DROP COLUMN IF EXISTS x_org';
3766 EXECUTE 'ALTER TABLE '
3767 || quote_ident(table_name)
3768 || ' ADD COLUMN x_org INTEGER';
3770 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3771 || ' SET x_org = id FROM actor.org_unit b'
3772 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3774 EXECUTE 'SELECT migration_tools.assert(
3775 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3776 ''Cannot find a desired org unit'',
3777 ''Found all desired org units''
3781 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3783 -- convenience function for handling desired_not_migrate
3785 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3787 table_schema ALIAS FOR $1;
3788 table_name ALIAS FOR $2;
3791 EXECUTE 'SELECT EXISTS (
3793 FROM information_schema.columns
3794 WHERE table_schema = $1
3796 and column_name = ''desired_not_migrate''
3797 )' INTO proceed USING table_schema, table_name;
3799 RAISE EXCEPTION 'Missing column desired_not_migrate';
3802 EXECUTE 'ALTER TABLE '
3803 || quote_ident(table_name)
3804 || ' DROP COLUMN IF EXISTS x_migrate';
3805 EXECUTE 'ALTER TABLE '
3806 || quote_ident(table_name)
3807 || ' ADD COLUMN x_migrate BOOLEAN';
3809 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3810 || ' SET x_migrate = CASE'
3811 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3812 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3813 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3814 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3815 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3816 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3819 EXECUTE 'SELECT migration_tools.assert(
3820 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3821 ''Not all desired_not_migrate values understood'',
3822 ''All desired_not_migrate values understood''
3826 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3828 -- convenience function for handling desired_not_migrate
3830 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3832 table_schema ALIAS FOR $1;
3833 table_name ALIAS FOR $2;
3836 EXECUTE 'SELECT EXISTS (
3838 FROM information_schema.columns
3839 WHERE table_schema = $1
3841 and column_name = ''desired_barred_or_blocked''
3842 )' INTO proceed USING table_schema, table_name;
3844 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3847 EXECUTE 'ALTER TABLE '
3848 || quote_ident(table_name)
3849 || ' DROP COLUMN IF EXISTS x_barred';
3850 EXECUTE 'ALTER TABLE '
3851 || quote_ident(table_name)
3852 || ' ADD COLUMN x_barred BOOLEAN';
3854 EXECUTE 'ALTER TABLE '
3855 || quote_ident(table_name)
3856 || ' DROP COLUMN IF EXISTS x_blocked';
3857 EXECUTE 'ALTER TABLE '
3858 || quote_ident(table_name)
3859 || ' ADD COLUMN x_blocked BOOLEAN';
3861 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3862 || ' SET x_barred = CASE'
3863 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3864 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3865 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3866 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3869 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3870 || ' SET x_blocked = CASE'
3871 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3872 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3873 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3874 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3877 EXECUTE 'SELECT migration_tools.assert(
3878 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3879 ''Not all desired_barred_or_blocked values understood'',
3880 ''All desired_barred_or_blocked values understood''
3884 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3886 -- convenience function for handling desired_profile
3888 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3890 table_schema ALIAS FOR $1;
3891 table_name ALIAS FOR $2;
3894 EXECUTE 'SELECT EXISTS (
3896 FROM information_schema.columns
3897 WHERE table_schema = $1
3899 and column_name = ''desired_profile''
3900 )' INTO proceed USING table_schema, table_name;
3902 RAISE EXCEPTION 'Missing column desired_profile';
3905 EXECUTE 'ALTER TABLE '
3906 || quote_ident(table_name)
3907 || ' DROP COLUMN IF EXISTS x_profile';
3908 EXECUTE 'ALTER TABLE '
3909 || quote_ident(table_name)
3910 || ' ADD COLUMN x_profile INTEGER';
3912 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3913 || ' SET x_profile = b.id FROM permission.grp_tree b'
3914 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3916 EXECUTE 'SELECT migration_tools.assert(
3917 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3918 ''Cannot find a desired profile'',
3919 ''Found all desired profiles''
3923 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3925 -- convenience function for handling desired actor stat cats
3927 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3929 table_schema ALIAS FOR $1;
3930 table_name ALIAS FOR $2;
3931 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3932 org_shortname ALIAS FOR $4;
3940 SELECT 'desired_sc' || field_suffix INTO sc;
3941 SELECT 'desired_sce' || field_suffix INTO sce;
3943 EXECUTE 'SELECT EXISTS (
3945 FROM information_schema.columns
3946 WHERE table_schema = $1
3948 and column_name = $3
3949 )' INTO proceed USING table_schema, table_name, sc;
3951 RAISE EXCEPTION 'Missing column %', sc;
3953 EXECUTE 'SELECT EXISTS (
3955 FROM information_schema.columns
3956 WHERE table_schema = $1
3958 and column_name = $3
3959 )' INTO proceed USING table_schema, table_name, sce;
3961 RAISE EXCEPTION 'Missing column %', sce;
3964 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3966 RAISE EXCEPTION 'Cannot find org by shortname';
3968 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3970 -- caller responsible for their own truncates though we try to prevent duplicates
3971 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3976 ' || quote_ident(table_name) || '
3978 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3982 WHERE owner = ANY ($2)
3983 AND name = BTRIM('||sc||')
3988 WHERE owner = ANY ($2)
3989 AND name = BTRIM('||sc||')
3992 USING org, org_list;
3994 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3999 WHERE owner = ANY ($2)
4000 AND BTRIM('||sc||') = BTRIM(name))
4003 WHERE owner = ANY ($2)
4004 AND BTRIM('||sc||') = BTRIM(name))
4009 ' || quote_ident(table_name) || '
4011 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4012 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4015 FROM actor.stat_cat_entry
4019 WHERE owner = ANY ($2)
4020 AND BTRIM('||sc||') = BTRIM(name)
4021 ) AND value = BTRIM('||sce||')
4022 AND owner = ANY ($2)
4026 FROM actor_stat_cat_entry
4030 WHERE owner = ANY ($2)
4031 AND BTRIM('||sc||') = BTRIM(name)
4032 ) AND value = BTRIM('||sce||')
4033 AND owner = ANY ($2)
4036 USING org, org_list;
4038 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4040 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4042 table_schema ALIAS FOR $1;
4043 table_name ALIAS FOR $2;
4044 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4045 org_shortname ALIAS FOR $4;
4053 SELECT 'desired_sc' || field_suffix INTO sc;
4054 SELECT 'desired_sce' || field_suffix INTO sce;
4055 EXECUTE 'SELECT EXISTS (
4057 FROM information_schema.columns
4058 WHERE table_schema = $1
4060 and column_name = $3
4061 )' INTO proceed USING table_schema, table_name, sc;
4063 RAISE EXCEPTION 'Missing column %', sc;
4065 EXECUTE 'SELECT EXISTS (
4067 FROM information_schema.columns
4068 WHERE table_schema = $1
4070 and column_name = $3
4071 )' INTO proceed USING table_schema, table_name, sce;
4073 RAISE EXCEPTION 'Missing column %', sce;
4076 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4078 RAISE EXCEPTION 'Cannot find org by shortname';
4081 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4083 EXECUTE 'ALTER TABLE '
4084 || quote_ident(table_name)
4085 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4086 EXECUTE 'ALTER TABLE '
4087 || quote_ident(table_name)
4088 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4089 EXECUTE 'ALTER TABLE '
4090 || quote_ident(table_name)
4091 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4092 EXECUTE 'ALTER TABLE '
4093 || quote_ident(table_name)
4094 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4097 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4099 x_sc' || field_suffix || ' = id
4101 (SELECT id, name, owner FROM actor_stat_cat
4102 UNION SELECT id, name, owner FROM actor.stat_cat) u
4104 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4105 AND u.owner = ANY ($1);'
4108 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4110 x_sce' || field_suffix || ' = id
4112 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4113 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4115 u.stat_cat = x_sc' || field_suffix || '
4116 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4117 AND u.owner = ANY ($1);'
4120 EXECUTE 'SELECT migration_tools.assert(
4121 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4122 ''Cannot find a desired stat cat'',
4123 ''Found all desired stat cats''
4126 EXECUTE 'SELECT migration_tools.assert(
4127 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4128 ''Cannot find a desired stat cat entry'',
4129 ''Found all desired stat cat entries''
4133 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4135 -- convenience functions for adding shelving locations
4136 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4137 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4143 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4146 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4147 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4148 IF return_id IS NOT NULL THEN
4156 $$ LANGUAGE plpgsql;
4158 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4160 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4161 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4167 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4170 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4172 SELECT INTO return_id id FROM
4173 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4174 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4175 IF return_id IS NOT NULL THEN
4183 $$ LANGUAGE plpgsql;
4185 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
4186 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
4187 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
4195 use MARC::File::XML (BinaryEncoding => 'utf8');
4197 binmode(STDERR, ':bytes');
4198 binmode(STDOUT, ':utf8');
4199 binmode(STDERR, ':utf8');
4201 my $marc_xml = shift;
4202 my $new_9_to_set = shift;
4204 $marc_xml =~ s/(<leader>.........)./${1}a/;
4207 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4210 #elog("could not parse $bibid: $@\n");
4211 import MARC::File::XML (BinaryEncoding => 'utf8');
4215 my @uris = $marc_xml->field('856');
4216 return $marc_xml->as_xml_record() unless @uris;
4218 foreach my $field (@uris) {
4219 my $ind1 = $field->indicator('1');
4220 if (!defined $ind1) { next; }
4221 if ($ind1 ne '1' && $ind1 ne '4') { next; }
4222 my $ind2 = $field->indicator('2');
4223 if (!defined $ind2) { next; }
4224 if ($ind2 ne '0' && $ind2 ne '1') { next; }
4225 $field->add_subfields( '9' => $new_9_to_set );
4228 return $marc_xml->as_xml_record();
4232 -- yet another subfield 9 function, this one only adds the $9 and forces
4233 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
4234 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
4235 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
4243 use MARC::File::XML (BinaryEncoding => 'utf8');
4245 binmode(STDERR, ':bytes');
4246 binmode(STDOUT, ':utf8');
4247 binmode(STDERR, ':utf8');
4249 my $marc_xml = shift;
4250 my $new_9_to_set = shift;
4252 $marc_xml =~ s/(<leader>.........)./${1}a/;
4255 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4258 #elog("could not parse $bibid: $@\n");
4259 import MARC::File::XML (BinaryEncoding => 'utf8');
4263 my @uris = $marc_xml->field('856');
4264 return $marc_xml->as_xml_record() unless @uris;
4266 foreach my $field (@uris) {
4267 my $ind1 = $field->indicator('1');
4268 if (!defined $ind1) { next; }
4269 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
4270 my $ind2 = $field->indicator('2');
4271 if (!defined $ind2) { next; }
4272 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
4273 $field->add_subfields( '9' => $new_9_to_set );
4276 return $marc_xml->as_xml_record();
4281 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4282 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4290 use MARC::File::XML (BinaryEncoding => 'utf8');
4292 binmode(STDERR, ':bytes');
4293 binmode(STDOUT, ':utf8');
4294 binmode(STDERR, ':utf8');
4296 my $marc_xml = shift;
4299 $marc_xml =~ s/(<leader>.........)./${1}a/;
4302 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4305 #elog("could not parse $bibid: $@\n");
4306 import MARC::File::XML (BinaryEncoding => 'utf8');
4310 my @fields = $marc_xml->field($tag);
4311 return $marc_xml->as_xml_record() unless @fields;
4313 $marc_xml->delete_fields(@fields);
4315 return $marc_xml->as_xml_record();
4319 -- convenience function for linking to the item staging table
4321 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4323 table_schema ALIAS FOR $1;
4324 table_name ALIAS FOR $2;
4325 foreign_column_name ALIAS FOR $3;
4326 main_column_name ALIAS FOR $4;
4327 btrim_desired ALIAS FOR $5;
4330 EXECUTE 'SELECT EXISTS (
4332 FROM information_schema.columns
4333 WHERE table_schema = $1
4335 and column_name = $3
4336 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4338 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4341 EXECUTE 'SELECT EXISTS (
4343 FROM information_schema.columns
4344 WHERE table_schema = $1
4345 AND table_name = ''asset_copy_legacy''
4346 and column_name = $2
4347 )' INTO proceed USING table_schema, main_column_name;
4349 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4352 EXECUTE 'ALTER TABLE '
4353 || quote_ident(table_name)
4354 || ' DROP COLUMN IF EXISTS x_item';
4355 EXECUTE 'ALTER TABLE '
4356 || quote_ident(table_name)
4357 || ' ADD COLUMN x_item BIGINT';
4359 IF btrim_desired THEN
4360 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4361 || ' SET x_item = b.id FROM asset_copy_legacy b'
4362 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4363 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4365 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4366 || ' SET x_item = b.id FROM asset_copy_legacy b'
4367 || ' WHERE a.' || quote_ident(foreign_column_name)
4368 || ' = b.' || quote_ident(main_column_name);
4371 --EXECUTE 'SELECT migration_tools.assert(
4372 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4373 -- ''Cannot link every barcode'',
4374 -- ''Every barcode linked''
4378 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4380 -- convenience function for linking to the user staging table
4382 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4384 table_schema ALIAS FOR $1;
4385 table_name ALIAS FOR $2;
4386 foreign_column_name ALIAS FOR $3;
4387 main_column_name ALIAS FOR $4;
4388 btrim_desired ALIAS FOR $5;
4391 EXECUTE 'SELECT EXISTS (
4393 FROM information_schema.columns
4394 WHERE table_schema = $1
4396 and column_name = $3
4397 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4399 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4402 EXECUTE 'SELECT EXISTS (
4404 FROM information_schema.columns
4405 WHERE table_schema = $1
4406 AND table_name = ''actor_usr_legacy''
4407 and column_name = $2
4408 )' INTO proceed USING table_schema, main_column_name;
4410 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4413 EXECUTE 'ALTER TABLE '
4414 || quote_ident(table_name)
4415 || ' DROP COLUMN IF EXISTS x_user';
4416 EXECUTE 'ALTER TABLE '
4417 || quote_ident(table_name)
4418 || ' ADD COLUMN x_user INTEGER';
4420 IF btrim_desired THEN
4421 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4422 || ' SET x_user = b.id FROM actor_usr_legacy b'
4423 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4424 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4426 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4427 || ' SET x_user = b.id FROM actor_usr_legacy b'
4428 || ' WHERE a.' || quote_ident(foreign_column_name)
4429 || ' = b.' || quote_ident(main_column_name);
4432 --EXECUTE 'SELECT migration_tools.assert(
4433 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4434 -- ''Cannot link every barcode'',
4435 -- ''Every barcode linked''
4439 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4441 -- convenience function for linking two tables
4442 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4443 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4445 table_schema ALIAS FOR $1;
4446 table_a ALIAS FOR $2;
4447 column_a ALIAS FOR $3;
4448 table_b ALIAS FOR $4;
4449 column_b ALIAS FOR $5;
4450 column_x ALIAS FOR $6;
4451 btrim_desired ALIAS FOR $7;
4454 EXECUTE 'SELECT EXISTS (
4456 FROM information_schema.columns
4457 WHERE table_schema = $1
4459 and column_name = $3
4460 )' INTO proceed USING table_schema, table_a, column_a;
4462 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4465 EXECUTE 'SELECT EXISTS (
4467 FROM information_schema.columns
4468 WHERE table_schema = $1
4470 and column_name = $3
4471 )' INTO proceed USING table_schema, table_b, column_b;
4473 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4476 EXECUTE 'ALTER TABLE '
4477 || quote_ident(table_b)
4478 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4479 EXECUTE 'ALTER TABLE '
4480 || quote_ident(table_b)
4481 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4483 IF btrim_desired THEN
4484 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4485 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4486 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4487 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4489 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4490 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4491 || ' WHERE a.' || quote_ident(column_a)
4492 || ' = b.' || quote_ident(column_b);
4496 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4498 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4499 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4500 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4502 table_schema ALIAS FOR $1;
4503 table_a ALIAS FOR $2;
4504 column_a ALIAS FOR $3;
4505 table_b ALIAS FOR $4;
4506 column_b ALIAS FOR $5;
4507 column_w ALIAS FOR $6;
4508 column_x ALIAS FOR $7;
4509 btrim_desired ALIAS FOR $8;
4512 EXECUTE 'SELECT EXISTS (
4514 FROM information_schema.columns
4515 WHERE table_schema = $1
4517 and column_name = $3
4518 )' INTO proceed USING table_schema, table_a, column_a;
4520 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4523 EXECUTE 'SELECT EXISTS (
4525 FROM information_schema.columns
4526 WHERE table_schema = $1
4528 and column_name = $3
4529 )' INTO proceed USING table_schema, table_b, column_b;
4531 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4534 EXECUTE 'ALTER TABLE '
4535 || quote_ident(table_b)
4536 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4537 EXECUTE 'ALTER TABLE '
4538 || quote_ident(table_b)
4539 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4541 IF btrim_desired THEN
4542 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4543 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4544 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4545 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4547 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4548 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4549 || ' WHERE a.' || quote_ident(column_a)
4550 || ' = b.' || quote_ident(column_b);
4554 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4556 -- 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
4557 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4558 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4560 table_schema ALIAS FOR $1;
4561 table_a ALIAS FOR $2;
4562 column_a ALIAS FOR $3;
4563 table_b ALIAS FOR $4;
4564 column_b ALIAS FOR $5;
4565 column_w ALIAS FOR $6;
4566 column_x ALIAS FOR $7;
4569 EXECUTE 'SELECT EXISTS (
4571 FROM information_schema.columns
4572 WHERE table_schema = $1
4574 and column_name = $3
4575 )' INTO proceed USING table_schema, table_a, column_a;
4577 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4580 EXECUTE 'SELECT EXISTS (
4582 FROM information_schema.columns
4583 WHERE table_schema = $1
4585 and column_name = $3
4586 )' INTO proceed USING table_schema, table_b, column_b;
4588 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4591 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4592 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4593 || ' WHERE a.' || quote_ident(column_a)
4594 || ' = b.' || quote_ident(column_b);
4597 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4599 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4601 table_schema ALIAS FOR $1;
4602 table_a ALIAS FOR $2;
4603 column_a ALIAS FOR $3;
4604 table_b ALIAS FOR $4;
4605 column_b ALIAS FOR $5;
4606 column_w ALIAS FOR $6;
4607 column_x ALIAS FOR $7;
4610 EXECUTE 'SELECT EXISTS (
4612 FROM information_schema.columns
4613 WHERE table_schema = $1
4615 and column_name = $3
4616 )' INTO proceed USING table_schema, table_a, column_a;
4618 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4621 EXECUTE 'SELECT EXISTS (
4623 FROM information_schema.columns
4624 WHERE table_schema = $1
4626 and column_name = $3
4627 )' INTO proceed USING table_schema, table_b, column_b;
4629 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4632 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4633 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4634 || ' WHERE a.' || quote_ident(column_a)
4635 || ' = b.' || quote_ident(column_b)
4636 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4639 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4641 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4643 table_schema ALIAS FOR $1;
4644 table_a ALIAS FOR $2;
4645 column_a ALIAS FOR $3;
4646 table_b ALIAS FOR $4;
4647 column_b ALIAS FOR $5;
4648 column_w ALIAS FOR $6;
4649 column_x ALIAS FOR $7;
4652 EXECUTE 'SELECT EXISTS (
4654 FROM information_schema.columns
4655 WHERE table_schema = $1
4657 and column_name = $3
4658 )' INTO proceed USING table_schema, table_a, column_a;
4660 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4663 EXECUTE 'SELECT EXISTS (
4665 FROM information_schema.columns
4666 WHERE table_schema = $1
4668 and column_name = $3
4669 )' INTO proceed USING table_schema, table_b, column_b;
4671 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4674 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4675 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4676 || ' WHERE a.' || quote_ident(column_a)
4677 || ' = b.' || quote_ident(column_b)
4678 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4681 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4683 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4685 table_schema ALIAS FOR $1;
4686 table_a ALIAS FOR $2;
4687 column_a ALIAS FOR $3;
4688 table_b ALIAS FOR $4;
4689 column_b ALIAS FOR $5;
4690 column_w ALIAS FOR $6;
4691 column_x ALIAS FOR $7;
4694 EXECUTE 'SELECT EXISTS (
4696 FROM information_schema.columns
4697 WHERE table_schema = $1
4699 and column_name = $3
4700 )' INTO proceed USING table_schema, table_a, column_a;
4702 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4705 EXECUTE 'SELECT EXISTS (
4707 FROM information_schema.columns
4708 WHERE table_schema = $1
4710 and column_name = $3
4711 )' INTO proceed USING table_schema, table_b, column_b;
4713 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4716 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4717 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4718 || ' WHERE a.' || quote_ident(column_a)
4719 || ' = b.' || quote_ident(column_b)
4720 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4723 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4725 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4727 table_schema ALIAS FOR $1;
4728 table_a ALIAS FOR $2;
4729 column_a ALIAS FOR $3;
4730 table_b ALIAS FOR $4;
4731 column_b ALIAS FOR $5;
4732 column_w ALIAS FOR $6;
4733 column_x ALIAS FOR $7;
4736 EXECUTE 'SELECT EXISTS (
4738 FROM information_schema.columns
4739 WHERE table_schema = $1
4741 and column_name = $3
4742 )' INTO proceed USING table_schema, table_a, column_a;
4744 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4747 EXECUTE 'SELECT EXISTS (
4749 FROM information_schema.columns
4750 WHERE table_schema = $1
4752 and column_name = $3
4753 )' INTO proceed USING table_schema, table_b, column_b;
4755 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4758 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4759 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4760 || ' WHERE a.' || quote_ident(column_a)
4761 || ' = b.' || quote_ident(column_b)
4762 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4765 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4767 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4769 table_schema ALIAS FOR $1;
4770 table_a ALIAS FOR $2;
4771 column_a ALIAS FOR $3;
4772 table_b ALIAS FOR $4;
4773 column_b ALIAS FOR $5;
4774 column_w ALIAS FOR $6;
4775 column_x ALIAS FOR $7;
4778 EXECUTE 'SELECT EXISTS (
4780 FROM information_schema.columns
4781 WHERE table_schema = $1
4783 and column_name = $3
4784 )' INTO proceed USING table_schema, table_a, column_a;
4786 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4789 EXECUTE 'SELECT EXISTS (
4791 FROM information_schema.columns
4792 WHERE table_schema = $1
4794 and column_name = $3
4795 )' INTO proceed USING table_schema, table_b, column_b;
4797 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4800 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4801 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4802 || ' WHERE a.' || quote_ident(column_a)
4803 || ' = b.' || quote_ident(column_b)
4804 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4807 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4809 -- convenience function for handling desired asset stat cats
4811 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4813 table_schema ALIAS FOR $1;
4814 table_name ALIAS FOR $2;
4815 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4816 org_shortname ALIAS FOR $4;
4824 SELECT 'desired_sc' || field_suffix INTO sc;
4825 SELECT 'desired_sce' || field_suffix INTO sce;
4827 EXECUTE 'SELECT EXISTS (
4829 FROM information_schema.columns
4830 WHERE table_schema = $1
4832 and column_name = $3
4833 )' INTO proceed USING table_schema, table_name, sc;
4835 RAISE EXCEPTION 'Missing column %', sc;
4837 EXECUTE 'SELECT EXISTS (
4839 FROM information_schema.columns
4840 WHERE table_schema = $1
4842 and column_name = $3
4843 )' INTO proceed USING table_schema, table_name, sce;
4845 RAISE EXCEPTION 'Missing column %', sce;
4848 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4850 RAISE EXCEPTION 'Cannot find org by shortname';
4852 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4854 -- caller responsible for their own truncates though we try to prevent duplicates
4855 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4860 ' || quote_ident(table_name) || '
4862 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4866 WHERE owner = ANY ($2)
4867 AND name = BTRIM('||sc||')
4872 WHERE owner = ANY ($2)
4873 AND name = BTRIM('||sc||')
4876 USING org, org_list;
4878 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4883 WHERE owner = ANY ($2)
4884 AND BTRIM('||sc||') = BTRIM(name))
4887 WHERE owner = ANY ($2)
4888 AND BTRIM('||sc||') = BTRIM(name))
4893 ' || quote_ident(table_name) || '
4895 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4896 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4899 FROM asset.stat_cat_entry
4903 WHERE owner = ANY ($2)
4904 AND BTRIM('||sc||') = BTRIM(name)
4905 ) AND value = BTRIM('||sce||')
4906 AND owner = ANY ($2)
4910 FROM asset_stat_cat_entry
4914 WHERE owner = ANY ($2)
4915 AND BTRIM('||sc||') = BTRIM(name)
4916 ) AND value = BTRIM('||sce||')
4917 AND owner = ANY ($2)
4920 USING org, org_list;
4922 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4924 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4926 table_schema ALIAS FOR $1;
4927 table_name ALIAS FOR $2;
4928 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4929 org_shortname ALIAS FOR $4;
4937 SELECT 'desired_sc' || field_suffix INTO sc;
4938 SELECT 'desired_sce' || field_suffix INTO sce;
4939 EXECUTE 'SELECT EXISTS (
4941 FROM information_schema.columns
4942 WHERE table_schema = $1
4944 and column_name = $3
4945 )' INTO proceed USING table_schema, table_name, sc;
4947 RAISE EXCEPTION 'Missing column %', sc;
4949 EXECUTE 'SELECT EXISTS (
4951 FROM information_schema.columns
4952 WHERE table_schema = $1
4954 and column_name = $3
4955 )' INTO proceed USING table_schema, table_name, sce;
4957 RAISE EXCEPTION 'Missing column %', sce;
4960 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4962 RAISE EXCEPTION 'Cannot find org by shortname';
4965 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4967 EXECUTE 'ALTER TABLE '
4968 || quote_ident(table_name)
4969 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4970 EXECUTE 'ALTER TABLE '
4971 || quote_ident(table_name)
4972 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4973 EXECUTE 'ALTER TABLE '
4974 || quote_ident(table_name)
4975 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4976 EXECUTE 'ALTER TABLE '
4977 || quote_ident(table_name)
4978 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4981 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4983 x_sc' || field_suffix || ' = id
4985 (SELECT id, name, owner FROM asset_stat_cat
4986 UNION SELECT id, name, owner FROM asset.stat_cat) u
4988 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4989 AND u.owner = ANY ($1);'
4992 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4994 x_sce' || field_suffix || ' = id
4996 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4997 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4999 u.stat_cat = x_sc' || field_suffix || '
5000 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5001 AND u.owner = ANY ($1);'
5004 EXECUTE 'SELECT migration_tools.assert(
5005 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5006 ''Cannot find a desired stat cat'',
5007 ''Found all desired stat cats''
5010 EXECUTE 'SELECT migration_tools.assert(
5011 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5012 ''Cannot find a desired stat cat entry'',
5013 ''Found all desired stat cat entries''
5017 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5019 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5020 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5027 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5029 AND table_schema = s_name
5030 AND (data_type='text' OR data_type='character varying')
5031 AND column_name like 'l_%'
5033 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5040 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5041 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5048 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5050 AND table_schema = s_name
5051 AND (data_type='text' OR data_type='character varying')
5053 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5060 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5061 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5068 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5070 AND table_schema = s_name
5071 AND (data_type='text' OR data_type='character varying')
5072 AND column_name like 'l_%'
5074 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5081 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5082 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5089 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5091 AND table_schema = s_name
5092 AND (data_type='text' OR data_type='character varying')
5094 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');