1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.copy_alert,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 -- creates other child table so you can have more than one child table in a schema from a base table
265 CREATE OR REPLACE FUNCTION build_variant_staging_table(text, text, text)
271 migration_schema ALIAS FOR $1;
272 production_table ALIAS FOR $2;
273 base_staging_table ALIAS FOR $3;
276 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
277 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
278 PERFORM migration_tools.exec( $1, '
279 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
280 SELECT table_schema, table_name, column_name, data_type
281 FROM information_schema.columns
282 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
285 SELECT table_schema, table_name, column_name, data_type
286 FROM information_schema.columns
287 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
289 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
294 CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
296 migration_schema ALIAS FOR $1;
297 parent_table ALIAS FOR $2;
298 source_table ALIAS FOR $3;
302 column_list TEXT := '';
303 column_count INTEGER := 0;
305 create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
307 SELECT table_schema, table_name, column_name, data_type
308 FROM information_schema.columns
309 WHERE table_schema = migration_schema AND table_name = source_table
311 column_count := column_count + 1;
312 if column_count > 1 then
313 create_sql := create_sql || ', ';
314 column_list := column_list || ', ';
316 create_sql := create_sql || columns.column_name || ' ';
317 if columns.data_type = 'ARRAY' then
318 create_sql := create_sql || 'TEXT[]';
320 create_sql := create_sql || columns.data_type;
322 column_list := column_list || columns.column_name;
324 create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
325 --RAISE INFO 'create_sql = %', create_sql;
327 insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
328 --RAISE INFO 'insert_sql = %', insert_sql;
331 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
333 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
335 migration_schema ALIAS FOR $1;
336 production_tables TEXT[];
338 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
339 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
340 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
341 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
344 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
346 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
348 migration_schema ALIAS FOR $1;
349 production_table ALIAS FOR $2;
350 base_staging_table TEXT;
353 base_staging_table = REPLACE( production_table, '.', '_' );
354 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
355 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
357 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
359 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
361 full_name TEXT := $1;
363 family_name TEXT := '';
364 first_given_name TEXT := '';
365 second_given_name TEXT := '';
369 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
370 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
372 IF suffix = before_comma THEN
376 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
377 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
378 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
380 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
382 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
384 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
386 full_name TEXT := $1;
388 family_name TEXT := '';
389 first_given_name TEXT := '';
390 second_given_name TEXT := '';
395 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
396 IF temp ilike '%MR.%' THEN
398 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
400 IF temp ilike '%MRS.%' THEN
402 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
404 IF temp ilike '%MS.%' THEN
406 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
408 IF temp ilike '%DR.%' THEN
410 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
412 IF temp ilike '%JR%' THEN
414 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
416 IF temp ilike '%JR,%' THEN
418 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
420 IF temp ilike '%SR%' THEN
422 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
424 IF temp ilike '%SR,%' THEN
426 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
428 IF temp ~ E'\\sII$' THEN
430 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
432 IF temp ~ E'\\sIII$' THEN
434 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
436 IF temp ~ E'\\sIV$' THEN
438 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
441 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
442 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
443 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
445 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
447 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
451 full_name TEXT := $1;
453 family_name TEXT := '';
454 first_given_name TEXT := '';
455 second_given_name TEXT := '';
460 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
461 IF temp ilike '%MR.%' THEN
463 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
465 IF temp ilike '%MRS.%' THEN
467 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
469 IF temp ilike '%MS.%' THEN
471 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
473 IF temp ilike '%DR.%' THEN
475 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
477 IF temp ilike '%JR.%' THEN
479 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
481 IF temp ilike '%JR,%' THEN
483 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
485 IF temp ilike '%SR.%' THEN
487 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
489 IF temp ilike '%SR,%' THEN
491 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
493 IF temp like '%III%' THEN
495 temp := REGEXP_REPLACE( temp, E'III', '' );
497 IF temp like '%II%' THEN
499 temp := REGEXP_REPLACE( temp, E'II', '' );
501 IF temp like '%IV%' THEN
503 temp := REGEXP_REPLACE( temp, E'IV', '' );
506 temp := REGEXP_REPLACE( temp, '\(\)', '');
507 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
508 family_name := REGEXP_REPLACE( family_name, ',', '' );
509 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
510 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
511 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
512 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
514 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
516 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
518 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
520 full_name TEXT := $1;
522 family_name TEXT := '';
523 first_given_name TEXT := '';
524 second_given_name TEXT := '';
528 temp := BTRIM(full_name);
529 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
530 --IF temp ~ '^\S{2,}\.' THEN
531 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
532 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
534 --IF temp ~ '\S{2,}\.$' THEN
535 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
536 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
538 IF temp ilike '%MR.%' THEN
540 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
542 IF temp ilike '%MRS.%' THEN
544 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
546 IF temp ilike '%MS.%' THEN
548 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
550 IF temp ilike '%DR.%' THEN
552 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
554 IF temp ilike '%JR.%' THEN
556 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
558 IF temp ilike '%JR,%' THEN
560 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
562 IF temp ilike '%SR.%' THEN
564 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
566 IF temp ilike '%SR,%' THEN
568 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
570 IF temp like '%III%' THEN
572 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
574 IF temp like '%II%' THEN
576 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
580 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
581 temp := BTRIM(REPLACE( temp, family_name, '' ));
582 family_name := REPLACE( family_name, ',', '' );
584 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
585 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
587 first_given_name := temp;
588 second_given_name := '';
591 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
592 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
593 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
594 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
596 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
597 second_given_name := temp;
598 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
602 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
604 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
606 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
608 full_name TEXT := $1;
610 family_name TEXT := '';
611 first_given_name TEXT := '';
612 second_given_name TEXT := '';
616 temp := BTRIM(full_name);
617 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
618 --IF temp ~ '^\S{2,}\.' THEN
619 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
620 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
622 --IF temp ~ '\S{2,}\.$' THEN
623 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
624 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
626 IF temp ilike '%MR.%' THEN
628 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
630 IF temp ilike '%MRS.%' THEN
632 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
634 IF temp ilike '%MS.%' THEN
636 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
638 IF temp ilike '%DR.%' THEN
640 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
642 IF temp ilike '%JR.%' THEN
644 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
646 IF temp ilike '%JR,%' THEN
648 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
650 IF temp ilike '%SR.%' THEN
652 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
654 IF temp ilike '%SR,%' THEN
656 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
658 IF temp like '%III%' THEN
660 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
662 IF temp like '%II%' THEN
664 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
668 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
669 temp := BTRIM(REPLACE( temp, family_name, '' ));
670 family_name := REPLACE( family_name, ',', '' );
672 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
673 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
675 first_given_name := temp;
676 second_given_name := '';
679 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
680 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
681 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
682 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
684 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
685 second_given_name := temp;
686 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
690 family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
691 first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
692 second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
694 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
696 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
698 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
700 city_state_zip TEXT := $1;
705 zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
706 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
707 IF city_state_zip ~ ',' THEN
708 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
709 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
711 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
712 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
713 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
715 IF city_state_zip ~ E'^\\S+$' THEN
716 city := city_state_zip;
719 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
720 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
724 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
726 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
728 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
729 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
731 fullstring TEXT := $1;
741 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
742 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
745 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
747 IF fullstring ~ ',' THEN
748 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
749 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
751 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
752 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
753 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
755 IF fullstring ~ E'^\\S+$' THEN
756 scratch1 := fullstring;
759 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
760 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
765 IF scratch1 ~ '[\$]' THEN
766 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
767 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
769 IF scratch1 ~ '\s' THEN
770 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
771 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
778 IF scratch2 ~ '^\d' THEN
779 address1 := scratch2;
782 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
783 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
787 TRIM(BOTH ' ' FROM address1)
788 ,TRIM(BOTH ' ' FROM address2)
789 ,TRIM(BOTH ' ' FROM city)
790 ,TRIM(BOTH ' ' FROM state)
791 ,TRIM(BOTH ' ' FROM zip)
794 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
796 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
799 use Geo::StreetAddress::US;
801 my $a = Geo::StreetAddress::US->parse_location($address);
804 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
805 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
810 $$ LANGUAGE PLPERLU STABLE;
812 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
813 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
814 INSERT INTO migration_tools.usps_suffixes VALUES
847 ('BOULEVARD','BLVD'),
951 ('EXPRESSWAY','EXPY'),
956 ('EXTENSIONS','EXTS'),
1054 ('ISLANDS','SLNDS'),
1067 ('JUNCTIONS','JCTS'),
1127 ('MOTORWAY','MTWY'),
1129 ('MOUNTAINS','MTNS'),
1143 ('OVERPASS','OPAS'),
1148 ('PARKWAYS','PKWY'),
1254 ('STRAVENUE','STRA'),
1274 ('THROUGHWAY','TRWY'),
1281 ('TRAFFICWAY','TRFY'),
1300 ('TURNPIKE','TPKE'),
1302 ('UNDERPASS','UPAS'),
1316 ('VILLAGES','VLGS'),
1347 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1348 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1353 --RAISE INFO 'suffix = %', suffix;
1354 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1355 suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1359 $$ LANGUAGE PLPGSQL STRICT STABLE;
1361 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1364 WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1368 $$ LANGUAGE PLPGSQL STRICT STABLE;
1370 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1374 IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
1375 IF o::BIGINT < t THEN
1382 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1384 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1386 migration_schema ALIAS FOR $1;
1390 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1395 $$ LANGUAGE PLPGSQL STRICT STABLE;
1397 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1399 migration_schema ALIAS FOR $1;
1403 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1408 $$ LANGUAGE PLPGSQL STRICT STABLE;
1410 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1412 migration_schema ALIAS FOR $1;
1416 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1421 $$ LANGUAGE PLPGSQL STRICT STABLE;
1423 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1425 migration_schema ALIAS FOR $1;
1429 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1434 $$ LANGUAGE PLPGSQL STRICT STABLE;
1436 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1438 migration_schema ALIAS FOR $1;
1440 patron_table ALIAS FOR $2;
1441 default_patron_profile ALIAS FOR $3;
1444 sql_where1 TEXT := '';
1445 sql_where2 TEXT := '';
1446 sql_where3 TEXT := '';
1449 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1451 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1453 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1454 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1455 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1456 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1457 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1458 --RAISE INFO 'sql = %', sql;
1459 PERFORM migration_tools.exec( $1, sql );
1461 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1463 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1465 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1468 $$ LANGUAGE PLPGSQL STRICT STABLE;
1470 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1472 migration_schema ALIAS FOR $1;
1474 item_table ALIAS FOR $2;
1477 sql_where1 TEXT := '';
1478 sql_where2 TEXT := '';
1479 sql_where3 TEXT := '';
1482 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1484 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1486 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
1487 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1488 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1489 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1490 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1491 --RAISE INFO 'sql = %', sql;
1492 PERFORM migration_tools.exec( $1, sql );
1495 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1497 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1500 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1502 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1504 migration_schema ALIAS FOR $1;
1505 base_copy_location_map TEXT;
1506 item_table ALIAS FOR $2;
1509 sql_where1 TEXT := '';
1510 sql_where2 TEXT := '';
1511 sql_where3 TEXT := '';
1514 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1516 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1518 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1519 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1520 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1521 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1522 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1523 --RAISE INFO 'sql = %', sql;
1524 PERFORM migration_tools.exec( $1, sql );
1527 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1529 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1532 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1534 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
1535 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1537 migration_schema ALIAS FOR $1;
1539 circ_table ALIAS FOR $2;
1540 item_table ALIAS FOR $3;
1541 patron_table ALIAS FOR $4;
1544 sql_where1 TEXT := '';
1545 sql_where2 TEXT := '';
1546 sql_where3 TEXT := '';
1547 sql_where4 TEXT := '';
1550 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1552 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1554 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
1555 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
1556 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
1557 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
1558 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
1559 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
1560 --RAISE INFO 'sql = %', sql;
1561 PERFORM migration_tools.exec( $1, sql );
1564 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1566 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1569 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1572 -- $barcode source barcode
1573 -- $prefix prefix to add to barcode, NULL = add no prefix
1574 -- $maxlen maximum length of barcode; default to 14 if left NULL
1575 -- $pad padding string to apply to left of source barcode before adding
1576 -- prefix and suffix; set to NULL or '' if no padding is desired
1577 -- $suffix suffix to add to barcode, NULL = add no suffix
1579 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1580 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1582 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1583 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1586 return unless defined $barcode;
1588 $prefix = '' unless defined $prefix;
1590 $pad = '0' unless defined $pad;
1591 $suffix = '' unless defined $suffix;
1593 # bail out if adding prefix and suffix would bring new barcode over max length
1594 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1596 my $new_barcode = $barcode;
1598 my $pad_length = $maxlen - length($prefix) - length($suffix);
1599 if (length($barcode) < $pad_length) {
1600 # assuming we always want padding on the left
1601 # also assuming that it is possible to have the pad string be longer than 1 character
1602 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1606 # bail out if adding prefix and suffix would bring new barcode over max length
1607 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1609 return "$prefix$new_barcode$suffix";
1610 $$ LANGUAGE PLPERLU STABLE;
1612 -- remove previous version of this function
1613 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1615 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1617 attempt_value ALIAS FOR $1;
1618 datatype ALIAS FOR $2;
1620 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1621 RETURN attempt_value;
1623 WHEN OTHERS THEN RETURN NULL;
1625 $$ LANGUAGE PLPGSQL STRICT STABLE;
1627 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1629 attempt_value ALIAS FOR $1;
1630 fail_value ALIAS FOR $2;
1634 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1641 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1646 $$ LANGUAGE PLPGSQL STRICT STABLE;
1648 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1650 attempt_value ALIAS FOR $1;
1651 fail_value ALIAS FOR $2;
1655 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1662 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1667 $$ LANGUAGE PLPGSQL STRICT STABLE;
1669 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1671 attempt_value ALIAS FOR $1;
1672 fail_value ALIAS FOR $2;
1676 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1683 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1688 $$ LANGUAGE PLPGSQL STRICT STABLE;
1690 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1692 attempt_value ALIAS FOR $1;
1693 fail_value ALIAS FOR $2;
1696 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1701 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1706 $$ LANGUAGE PLPGSQL STRICT STABLE;
1708 CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
1710 -- Expects the following table/columns:
1712 -- export_biblio_tsv:
1714 -- l_create_dt | 2007-03-07 09:03:09
1715 -- l_last_change_dt | 2015-01-23 11:18:54
1716 -- l_last_change_userid | 2
1717 -- l_material_cd | 10
1718 -- l_collection_cd | 13
1719 -- l_call_nmbr1 | Canada
1720 -- l_call_nmbr2 | ON
1721 -- l_call_nmbr3 | Ottawa 18
1722 -- l_title | Art and the courts : France ad England
1723 -- l_title_remainder | from 1259-1328
1724 -- l_responsibility_stmt |
1725 -- l_author | National Gallery of Canada
1732 -- l_flag_attention | 0
1734 -- export_biblio_field_tsv:
1740 -- l_subfield_cd | a
1741 -- l_field_data | Brieger, Peter Henry
1743 -- Map export_biblio_tsv as follows:
1744 -- l_call_nmbr? -> 099a
1747 -- l_title_remainder -> 245b
1748 -- l_responsibility_stmt -> 245c
1753 migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
1756 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
1757 array_agg(l_ind1_cd) as "ind1",
1758 array_agg(l_ind2_cd) as "ind2",
1759 array_agg(l_field_data) as "data"
1767 from export_biblio_field_tsv
1768 where l_bibid = x_bibid
1772 'a' as "l_subfield_cd",
1776 nullif(btrim(l_call_nmbr1),''),
1777 nullif(btrim(l_call_nmbr2),''),
1778 nullif(btrim(l_call_nmbr3),'')
1780 from export_biblio_tsv
1781 where l_bibid = x_bibid
1785 'a' as "l_subfield_cd",
1788 l_author as "l_field_data"
1789 from export_biblio_tsv
1790 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
1794 'a' as "l_subfield_cd",
1797 l_title as "l_field_data"
1798 from export_biblio_tsv
1799 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
1803 'b' as "l_subfield_cd",
1806 l_title_remainder as "l_field_data"
1807 from export_biblio_tsv
1808 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
1812 'a' as "l_subfield_cd",
1815 l_topic1 as "l_field_data"
1816 from export_biblio_tsv
1817 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
1821 'a' as "l_subfield_cd",
1824 l_topic2 as "l_field_data"
1825 from export_biblio_tsv
1826 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
1830 'a' as "l_subfield_cd",
1833 l_topic3 as "l_field_data"
1834 from export_biblio_tsv
1835 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
1839 'a' as "l_subfield_cd",
1842 l_topic4 as "l_field_data"
1843 from export_biblio_tsv
1844 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
1848 'a' as "l_subfield_cd",
1851 l_topic5 as "l_field_data"
1852 from export_biblio_tsv
1853 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
1857 '' as "l_subfield_cd",
1860 l_bibid as "l_field_data"
1861 from export_biblio_tsv
1862 where l_bibid = x_bibid
1867 $func$ LANGUAGE plpgsql;
1869 -- add koha holding tag to marc
1870 DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
1872 CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber TEXT)
1880 use MARC::File::XML (BinaryEncoding => 'utf8');
1882 binmode(STDERR, ':bytes');
1883 binmode(STDOUT, ':utf8');
1884 binmode(STDERR, ':utf8');
1886 my ($marc_xml, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
1888 $marc_xml =~ s/(<leader>.........)./${1}a/;
1891 $marc_xml = MARC::Record->new_from_xml($marc_xml);
1894 #elog("could not parse $bibid: $@\n");
1895 import MARC::File::XML (BinaryEncoding => 'utf8');
1899 my $new_field = new MARC::Field(
1902 'b' => $holdingbranch,
1908 if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
1909 if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
1910 if ($price) { $new_field->add_subfields('g' => $price); }
1911 if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
1912 if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
1913 if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
1914 if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
1915 if ($stack) { $new_field->add_subfields('j' => $stack); }
1916 if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
1917 if ($damaged) { $new_field->add_subfields('4' => $damaged); }
1918 if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
1919 if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
1920 if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
1921 if ($issues) { $new_field->add_subfields('l' => $issues); }
1922 if ($renewals) { $new_field->add_subfields('m' => $renewals); }
1923 if ($reserves) { $new_field->add_subfields('n' => $reserves); }
1924 if ($restricted) { $new_field->add_subfields('5' => $restricted); }
1925 if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
1926 if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
1927 if ($onloan) { $new_field->add_subfields('q' => $onloan); }
1928 if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
1929 if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
1930 if ($ccode) { $new_field->add_subfields('8' => $ccode); }
1931 if ($materials) { $new_field->add_subfields('3' => $materials); }
1932 if ($uri) { $new_field->add_subfields('u' => $uri); }
1933 if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
1934 if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
1935 if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
1937 $marc_xml->insert_grouped_field( $new_field );
1939 return $marc_xml->as_xml_record();
1943 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1945 attempt_value ALIAS FOR $1;
1946 fail_value ALIAS FOR $2;
1947 output NUMERIC(8,2);
1950 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1957 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1962 $$ LANGUAGE PLPGSQL STRICT STABLE;
1964 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1966 attempt_value ALIAS FOR $1;
1967 fail_value ALIAS FOR $2;
1968 output NUMERIC(6,2);
1971 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1978 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1983 $$ LANGUAGE PLPGSQL STRICT STABLE;
1985 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1987 attempt_value ALIAS FOR $1;
1988 fail_value ALIAS FOR $2;
1989 output NUMERIC(8,2);
1991 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1992 RAISE EXCEPTION 'too many digits';
1995 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
2002 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
2007 $$ LANGUAGE PLPGSQL STRICT STABLE;
2009 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
2011 attempt_value ALIAS FOR $1;
2012 fail_value ALIAS FOR $2;
2013 output NUMERIC(6,2);
2015 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
2016 RAISE EXCEPTION 'too many digits';
2019 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
2026 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
2031 $$ LANGUAGE PLPGSQL STRICT STABLE;
2033 -- add_codabar_checkdigit
2034 -- $barcode source barcode
2036 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
2037 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2038 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
2039 -- input string does not meet those requirements, it is returned unchanged.
2041 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
2042 my $barcode = shift;
2044 return $barcode if $barcode !~ /^\d{13,14}$/;
2045 $barcode = substr($barcode, 0, 13); # ignore 14th digit
2046 my @digits = split //, $barcode;
2048 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
2049 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
2050 my $remainder = $total % 10;
2051 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
2052 return $barcode . $checkdigit;
2053 $$ LANGUAGE PLPERLU STRICT STABLE;
2055 -- add_code39mod43_checkdigit
2056 -- $barcode source barcode
2058 -- If the source string is 13 or 14 characters long and contains only valid
2059 -- Code 39 mod 43 characters, adds or replaces the 14th
2060 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2061 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
2062 -- input string does not meet those requirements, it is returned unchanged.
2064 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
2065 my $barcode = shift;
2067 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
2068 $barcode = substr($barcode, 0, 13); # ignore 14th character
2070 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
2071 my %nums = map { $valid_chars[$_] => $_ } (0..42);
2074 $total += $nums{$_} foreach split(//, $barcode);
2075 my $remainder = $total % 43;
2076 my $checkdigit = $valid_chars[$remainder];
2077 return $barcode . $checkdigit;
2078 $$ LANGUAGE PLPERLU STRICT STABLE;
2080 -- add_mod16_checkdigit
2081 -- $barcode source barcode
2083 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
2085 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
2086 my $barcode = shift;
2088 my @digits = split //, $barcode;
2090 foreach $digit (@digits) {
2091 if ($digit =~ /[0-9]/) { $total += $digit;
2092 } elsif ($digit eq '-') { $total += 10;
2093 } elsif ($digit eq '$') { $total += 11;
2094 } elsif ($digit eq ':') { $total += 12;
2095 } elsif ($digit eq '/') { $total += 13;
2096 } elsif ($digit eq '.') { $total += 14;
2097 } elsif ($digit eq '+') { $total += 15;
2098 } elsif ($digit eq 'A') { $total += 16;
2099 } elsif ($digit eq 'B') { $total += 17;
2100 } elsif ($digit eq 'C') { $total += 18;
2101 } elsif ($digit eq 'D') { $total += 19;
2102 } else { die "invalid digit <$digit>";
2105 my $remainder = $total % 16;
2106 my $difference = 16 - $remainder;
2108 if ($difference < 10) { $checkdigit = $difference;
2109 } elsif ($difference == 10) { $checkdigit = '-';
2110 } elsif ($difference == 11) { $checkdigit = '$';
2111 } elsif ($difference == 12) { $checkdigit = ':';
2112 } elsif ($difference == 13) { $checkdigit = '/';
2113 } elsif ($difference == 14) { $checkdigit = '.';
2114 } elsif ($difference == 15) { $checkdigit = '+';
2115 } else { die "error calculating checkdigit";
2118 return $barcode . $checkdigit;
2119 $$ LANGUAGE PLPERLU STRICT STABLE;
2121 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
2124 areacode TEXT := $2;
2127 n_digits INTEGER := 0;
2130 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
2131 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
2132 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
2133 IF n_digits = 7 AND areacode <> '' THEN
2134 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
2135 output := (areacode || '-' || temp);
2142 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2144 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2145 my ($marcxml, $pos, $value) = @_;
2148 use MARC::File::XML;
2152 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2153 my $leader = $marc->leader();
2154 substr($leader, $pos, 1) = $value;
2155 $marc->leader($leader);
2156 $xml = $marc->as_xml_record;
2157 $xml =~ s/^<\?.+?\?>$//mo;
2159 $xml =~ s/>\s+</></sgo;
2162 $$ LANGUAGE PLPERLU STABLE;
2164 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
2165 my ($marcxml, $pos, $value) = @_;
2168 use MARC::File::XML;
2172 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2173 my $f008 = $marc->field('008');
2176 my $field = $f008->data();
2177 substr($field, $pos, 1) = $value;
2178 $f008->update($field);
2179 $xml = $marc->as_xml_record;
2180 $xml =~ s/^<\?.+?\?>$//mo;
2182 $xml =~ s/>\s+</></sgo;
2186 $$ LANGUAGE PLPERLU STABLE;
2189 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
2191 profile ALIAS FOR $1;
2193 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
2195 $$ LANGUAGE PLPGSQL STRICT STABLE;
2198 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
2200 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
2202 $$ LANGUAGE PLPGSQL STRICT STABLE;
2205 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
2207 my ($marcxml, $tags) = @_;
2210 use MARC::File::XML;
2215 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2216 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
2218 my @incumbents = ();
2220 foreach my $field ( $marc->fields() ) {
2221 push @incumbents, $field->as_formatted();
2224 foreach $field ( $to_insert->fields() ) {
2225 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
2226 $marc->insert_fields_ordered( ($field) );
2230 $xml = $marc->as_xml_record;
2231 $xml =~ s/^<\?.+?\?>$//mo;
2233 $xml =~ s/>\s+</></sgo;
2238 $$ LANGUAGE PLPERLU STABLE;
2240 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
2244 -- First make sure the circ matrix is loaded and the circulations
2245 -- have been staged to the extent possible (but at the very least
2246 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2247 -- circ modifiers must also be in place.
2249 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2257 this_duration_rule INT;
2259 this_max_fine_rule INT;
2260 rcd config.rule_circ_duration%ROWTYPE;
2261 rrf config.rule_recurring_fine%ROWTYPE;
2262 rmf config.rule_max_fine%ROWTYPE;
2269 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2271 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2273 -- Fetch the correct rules for this circulation
2280 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2283 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2284 INTO circ_lib, target_copy, usr, is_renewal ;
2286 INTO this_duration_rule,
2290 recurring_fine_rule,
2292 FROM action.item_user_circ_test(
2298 SELECT INTO rcd * FROM config.rule_circ_duration
2299 WHERE id = this_duration_rule;
2300 SELECT INTO rrf * FROM config.rule_recurring_fine
2301 WHERE id = this_fine_rule;
2302 SELECT INTO rmf * FROM config.rule_max_fine
2303 WHERE id = this_max_fine_rule;
2305 -- Apply the rules to this circulation
2306 EXECUTE ('UPDATE ' || tablename || ' c
2308 duration_rule = rcd.name,
2309 recurring_fine_rule = rrf.name,
2310 max_fine_rule = rmf.name,
2311 duration = rcd.normal,
2312 recurring_fine = rrf.normal,
2315 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2318 renewal_remaining = rcd.max_renewals
2320 config.rule_circ_duration rcd,
2321 config.rule_recurring_fine rrf,
2322 config.rule_max_fine rmf,
2325 rcd.id = ' || this_duration_rule || ' AND
2326 rrf.id = ' || this_fine_rule || ' AND
2327 rmf.id = ' || this_max_fine_rule || ' AND
2328 ac.id = c.target_copy AND
2329 c.id = ' || circ || ';');
2331 -- Keep track of where we are in the process
2333 IF (n % 100 = 0) THEN
2334 RAISE INFO '%', n || ' of ' || n_circs
2335 || ' (' || (100*n/n_circs) || '%) circs updated.';
2343 $$ LANGUAGE plpgsql;
2345 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2349 -- First make sure the circ matrix is loaded and the circulations
2350 -- have been staged to the extent possible (but at the very least
2351 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2352 -- circ modifiers must also be in place.
2354 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2362 this_duration_rule INT;
2364 this_max_fine_rule INT;
2365 rcd config.rule_circ_duration%ROWTYPE;
2366 rrf config.rule_recurring_fine%ROWTYPE;
2367 rmf config.rule_max_fine%ROWTYPE;
2374 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2376 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2378 -- Fetch the correct rules for this circulation
2385 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2388 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2389 INTO circ_lib, target_copy, usr, is_renewal ;
2391 INTO this_duration_rule,
2397 FROM action.find_circ_matrix_matchpoint(
2403 SELECT INTO rcd * FROM config.rule_circ_duration
2404 WHERE id = this_duration_rule;
2405 SELECT INTO rrf * FROM config.rule_recurring_fine
2406 WHERE id = this_fine_rule;
2407 SELECT INTO rmf * FROM config.rule_max_fine
2408 WHERE id = this_max_fine_rule;
2410 -- Apply the rules to this circulation
2411 EXECUTE ('UPDATE ' || tablename || ' c
2413 duration_rule = rcd.name,
2414 recuring_fine_rule = rrf.name,
2415 max_fine_rule = rmf.name,
2416 duration = rcd.normal,
2417 recuring_fine = rrf.normal,
2420 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2423 renewal_remaining = rcd.max_renewals
2425 config.rule_circ_duration rcd,
2426 config.rule_recuring_fine rrf,
2427 config.rule_max_fine rmf,
2430 rcd.id = ' || this_duration_rule || ' AND
2431 rrf.id = ' || this_fine_rule || ' AND
2432 rmf.id = ' || this_max_fine_rule || ' AND
2433 ac.id = c.target_copy AND
2434 c.id = ' || circ || ';');
2436 -- Keep track of where we are in the process
2438 IF (n % 100 = 0) THEN
2439 RAISE INFO '%', n || ' of ' || n_circs
2440 || ' (' || (100*n/n_circs) || '%) circs updated.';
2448 $$ LANGUAGE plpgsql;
2450 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2454 -- First make sure the circ matrix is loaded and the circulations
2455 -- have been staged to the extent possible (but at the very least
2456 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2457 -- circ modifiers must also be in place.
2459 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2467 this_duration_rule INT;
2469 this_max_fine_rule INT;
2470 rcd config.rule_circ_duration%ROWTYPE;
2471 rrf config.rule_recurring_fine%ROWTYPE;
2472 rmf config.rule_max_fine%ROWTYPE;
2479 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2481 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2483 -- Fetch the correct rules for this circulation
2490 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2493 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2494 INTO circ_lib, target_copy, usr, is_renewal ;
2496 INTO this_duration_rule,
2499 (matchpoint).duration_rule,
2500 (matchpoint).recurring_fine_rule,
2501 (matchpoint).max_fine_rule
2502 FROM action.find_circ_matrix_matchpoint(
2508 SELECT INTO rcd * FROM config.rule_circ_duration
2509 WHERE id = this_duration_rule;
2510 SELECT INTO rrf * FROM config.rule_recurring_fine
2511 WHERE id = this_fine_rule;
2512 SELECT INTO rmf * FROM config.rule_max_fine
2513 WHERE id = this_max_fine_rule;
2515 -- Apply the rules to this circulation
2516 EXECUTE ('UPDATE ' || tablename || ' c
2518 duration_rule = rcd.name,
2519 recurring_fine_rule = rrf.name,
2520 max_fine_rule = rmf.name,
2521 duration = rcd.normal,
2522 recurring_fine = rrf.normal,
2525 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2528 renewal_remaining = rcd.max_renewals,
2529 grace_period = rrf.grace_period
2531 config.rule_circ_duration rcd,
2532 config.rule_recurring_fine rrf,
2533 config.rule_max_fine rmf,
2536 rcd.id = ' || this_duration_rule || ' AND
2537 rrf.id = ' || this_fine_rule || ' AND
2538 rmf.id = ' || this_max_fine_rule || ' AND
2539 ac.id = c.target_copy AND
2540 c.id = ' || circ || ';');
2542 -- Keep track of where we are in the process
2544 IF (n % 100 = 0) THEN
2545 RAISE INFO '%', n || ' of ' || n_circs
2546 || ' (' || (100*n/n_circs) || '%) circs updated.';
2554 $$ LANGUAGE plpgsql;
2556 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2557 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2558 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2561 charge_lost_on_zero BOOLEAN;
2564 default_price NUMERIC;
2565 working_price NUMERIC;
2569 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2570 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2572 SELECT INTO charge_lost_on_zero value
2573 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2575 SELECT INTO min_price value
2576 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2578 SELECT INTO max_price value
2579 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2581 SELECT INTO default_price value
2582 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2584 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2586 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2587 working_price := default_price;
2590 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2591 working_price := max_price;
2594 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2595 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2596 working_price := min_price;
2600 RETURN working_price;
2604 $$ LANGUAGE plpgsql;
2606 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2610 -- First make sure the circ matrix is loaded and the circulations
2611 -- have been staged to the extent possible (but at the very least
2612 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2613 -- circ modifiers must also be in place.
2615 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2623 this_duration_rule INT;
2625 this_max_fine_rule INT;
2626 rcd config.rule_circ_duration%ROWTYPE;
2627 rrf config.rule_recurring_fine%ROWTYPE;
2628 rmf config.rule_max_fine%ROWTYPE;
2634 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2636 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2638 -- Fetch the correct rules for this circulation
2645 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2648 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2649 INTO circ_lib, target_copy, usr, is_renewal ;
2651 INTO this_duration_rule,
2654 (matchpoint).duration_rule,
2655 (matchpoint).recurring_fine_rule,
2656 (matchpoint).max_fine_rule
2657 FROM action.find_circ_matrix_matchpoint(
2663 SELECT INTO rcd * FROM config.rule_circ_duration
2664 WHERE id = this_duration_rule;
2665 SELECT INTO rrf * FROM config.rule_recurring_fine
2666 WHERE id = this_fine_rule;
2667 SELECT INTO rmf * FROM config.rule_max_fine
2668 WHERE id = this_max_fine_rule;
2670 -- Apply the rules to this circulation
2671 EXECUTE ('UPDATE ' || tablename || ' c
2673 duration_rule = rcd.name,
2674 recurring_fine_rule = rrf.name,
2675 max_fine_rule = rmf.name,
2676 duration = rcd.normal,
2677 recurring_fine = rrf.normal,
2680 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2683 renewal_remaining = rcd.max_renewals,
2684 grace_period = rrf.grace_period
2686 config.rule_circ_duration rcd,
2687 config.rule_recurring_fine rrf,
2688 config.rule_max_fine rmf,
2691 rcd.id = ' || this_duration_rule || ' AND
2692 rrf.id = ' || this_fine_rule || ' AND
2693 rmf.id = ' || this_max_fine_rule || ' AND
2694 ac.id = c.target_copy AND
2695 c.id = ' || circ || ';');
2697 -- Keep track of where we are in the process
2699 IF (n % 100 = 0) THEN
2700 RAISE INFO '%', n || ' of ' || n_circs
2701 || ' (' || (100*n/n_circs) || '%) circs updated.';
2709 $$ LANGUAGE plpgsql;
2714 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2716 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2717 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2719 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2720 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2723 c TEXT := schemaname || '.asset_copy_legacy';
2724 sc TEXT := schemaname || '.asset_stat_cat';
2725 sce TEXT := schemaname || '.asset_stat_cat_entry';
2726 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2732 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2734 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2736 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2737 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2738 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2745 $$ LANGUAGE plpgsql;
2747 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2749 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2750 -- This will assign standing penalties as needed.
2758 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2760 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2762 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2772 $$ LANGUAGE plpgsql;
2775 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2778 INSERT INTO metabib.metarecord (fingerprint, master_record)
2779 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2780 FROM biblio.record_entry b
2782 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
2783 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2784 ORDER BY b.fingerprint, b.quality DESC;
2785 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2787 FROM biblio.record_entry r
2788 JOIN metabib.metarecord m USING (fingerprint)
2789 WHERE NOT r.deleted;
2792 $$ LANGUAGE plpgsql;
2795 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2798 INSERT INTO metabib.metarecord (fingerprint, master_record)
2799 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2800 FROM biblio.record_entry b
2802 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
2803 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2804 ORDER BY b.fingerprint, b.quality DESC;
2805 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2807 FROM biblio.record_entry r
2808 JOIN metabib.metarecord m USING (fingerprint)
2810 AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
2813 $$ LANGUAGE plpgsql;
2816 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2818 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2819 -- Then SELECT migration_tools.create_cards('m_foo');
2822 u TEXT := schemaname || '.actor_usr_legacy';
2823 c TEXT := schemaname || '.actor_card';
2827 EXECUTE ('DELETE FROM ' || c || ';');
2828 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2829 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2835 $$ LANGUAGE plpgsql;
2838 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2840 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2842 my ($marcxml, $shortname) = @_;
2845 use MARC::File::XML;
2850 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2852 foreach my $field ( $marc->field('856') ) {
2853 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2854 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2855 $field->add_subfields( '9' => $shortname );
2856 $field->update( ind2 => '0');
2860 $xml = $marc->as_xml_record;
2861 $xml =~ s/^<\?.+?\?>$//mo;
2863 $xml =~ s/>\s+</></sgo;
2868 $$ LANGUAGE PLPERLU STABLE;
2870 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2872 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2874 my ($marcxml, $shortname) = @_;
2877 use MARC::File::XML;
2882 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2884 foreach my $field ( $marc->field('856') ) {
2885 if ( ! $field->as_string('9') ) {
2886 $field->add_subfields( '9' => $shortname );
2890 $xml = $marc->as_xml_record;
2891 $xml =~ s/^<\?.+?\?>$//mo;
2893 $xml =~ s/>\s+</></sgo;
2898 $$ LANGUAGE PLPERLU STABLE;
2901 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2913 -- Bail out if asked to change the label to ##URI##
2914 IF new_label = '##URI##' THEN
2918 -- Gather information
2919 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2920 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2921 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2923 -- Bail out if the label already is ##URI##
2924 IF old_label = '##URI##' THEN
2928 -- Bail out if the call number label is already correct
2929 IF new_volume = old_volume THEN
2933 -- Check whether we already have a destination volume available
2934 SELECT id INTO new_volume FROM asset.call_number
2937 owning_lib = owner AND
2938 label = new_label AND
2941 -- Create destination volume if needed
2943 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2944 VALUES (1, 1, bib, owner, new_label, cn_class);
2945 SELECT id INTO new_volume FROM asset.call_number
2948 owning_lib = owner AND
2949 label = new_label AND
2953 -- Move copy to destination
2954 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2956 -- Delete source volume if it is now empty
2957 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2959 DELETE FROM asset.call_number WHERE id = old_volume;
2964 $$ LANGUAGE plpgsql;
2966 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2971 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2975 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2976 $zipdata{$zip} = [$city, $state, $county];
2979 if (defined $zipdata{$input}) {
2980 my ($city, $state, $county) = @{$zipdata{$input}};
2981 return [$city, $state, $county];
2982 } elsif (defined $zipdata{substr $input, 0, 5}) {
2983 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2984 return [$city, $state, $county];
2986 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2989 $$ LANGUAGE PLPERLU STABLE;
2991 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2998 errors_found BOOLEAN;
3000 parent_shortname TEXT;
3006 type_parent_depth INT;
3011 errors_found := FALSE;
3013 -- Checking actor.org_unit_type
3015 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
3017 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
3018 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
3020 IF type_parent IS NOT NULL THEN
3022 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
3024 IF type_depth - type_parent_depth <> 1 THEN
3025 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
3026 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
3027 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
3028 ou_type_name, type_depth, parent_type, type_parent_depth;
3029 errors_found := TRUE;
3037 -- Checking actor.org_unit
3039 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
3041 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
3042 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
3043 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
3044 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
3045 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
3046 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
3047 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
3049 IF ou_parent IS NOT NULL THEN
3051 IF (org_unit_depth - parent_depth <> 1) OR (
3052 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
3054 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
3055 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
3056 errors_found := TRUE;
3063 IF NOT errors_found THEN
3064 RAISE INFO 'No errors found.';
3071 $$ LANGUAGE plpgsql;
3074 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
3078 DELETE FROM asset.opac_visible_copies;
3080 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
3082 cp.id, cp.circ_lib, cn.record
3085 JOIN asset.call_number cn ON (cn.id = cp.call_number)
3086 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3087 JOIN asset.copy_location cl ON (cp.location = cl.id)
3088 JOIN config.copy_status cs ON (cp.status = cs.id)
3089 JOIN biblio.record_entry b ON (cn.record = b.id)
3098 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
3102 $$ LANGUAGE plpgsql;
3105 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
3111 old_owning_lib INTEGER;
3117 -- Gather information
3118 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
3119 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
3120 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
3122 -- Bail out if the new_owning_lib is not the ID of an org_unit
3123 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
3125 '% is not a valid actor.org_unit ID; no change made.',
3130 -- Bail out discreetly if the owning_lib is already correct
3131 IF new_owning_lib = old_owning_lib THEN
3135 -- Check whether we already have a destination volume available
3136 SELECT id INTO new_volume FROM asset.call_number
3139 owning_lib = new_owning_lib AND
3140 label = old_label AND
3143 -- Create destination volume if needed
3145 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
3146 VALUES (1, 1, bib, new_owning_lib, old_label);
3147 SELECT id INTO new_volume FROM asset.call_number
3150 owning_lib = new_owning_lib AND
3151 label = old_label AND
3155 -- Move copy to destination
3156 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
3158 -- Delete source volume if it is now empty
3159 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
3161 DELETE FROM asset.call_number WHERE id = old_volume;
3166 $$ LANGUAGE plpgsql;
3169 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
3171 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
3174 new_owning_lib INTEGER;
3178 -- Parse the new_owner as an org unit ID or shortname
3179 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
3180 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
3181 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3182 ELSIF new_owner ~ E'^[0-9]+$' THEN
3183 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
3186 E'You don\'t need to put the actor.org_unit ID in quotes; '
3187 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
3188 new_owning_lib := new_owner::INTEGER;
3189 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3193 '% is not a valid actor.org_unit shortname or ID; no change made.',
3200 $$ LANGUAGE plpgsql;
3202 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3204 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3205 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3206 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
3207 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3208 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3209 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
3210 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3211 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3212 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
3213 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3214 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3215 $$) TO '$$ || dir || $$/asset_copy_location'$$;
3216 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3217 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3218 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3219 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3220 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3221 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
3222 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3223 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3224 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
3225 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
3226 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
3227 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
3228 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
3229 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
3231 $FUNC$ LANGUAGE PLPGSQL;
3233 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3235 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
3236 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
3237 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
3238 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
3239 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
3240 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
3241 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
3243 -- import any new circ rules
3244 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3245 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3246 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3247 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3249 -- and permission groups
3250 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3253 $FUNC$ LANGUAGE PLPGSQL;
3256 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$
3265 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3266 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3267 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
3268 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3269 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3270 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3271 FOR name IN EXECUTE loopq LOOP
3272 EXECUTE existsq INTO ct USING name;
3274 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3275 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3276 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3277 EXECUTE copyst USING name;
3281 $FUNC$ LANGUAGE PLPGSQL;
3283 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3290 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3291 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;
3292 FOR id IN EXECUTE loopq USING delimiter LOOP
3293 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3294 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3295 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3296 EXECUTE splitst USING id, delimiter;
3299 $FUNC$ LANGUAGE PLPGSQL;
3301 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3302 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3304 target_event_def ALIAS FOR $1;
3307 DROP TABLE IF EXISTS new_atevdefs;
3308 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3309 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3310 INSERT INTO action_trigger.event_definition (
3331 ,name || ' (clone of '||target_event_def||')'
3347 action_trigger.event_definition
3349 id = target_event_def
3351 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3352 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3353 INSERT INTO action_trigger.environment (
3359 currval('action_trigger.event_definition_id_seq')
3364 action_trigger.environment
3366 event_def = target_event_def
3368 INSERT INTO action_trigger.event_params (
3373 currval('action_trigger.event_definition_id_seq')
3377 action_trigger.event_params
3379 event_def = target_event_def
3382 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);
3384 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3386 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3387 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3389 target_event_def ALIAS FOR $1;
3391 new_interval ALIAS FOR $3;
3393 DROP TABLE IF EXISTS new_atevdefs;
3394 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3395 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3396 INSERT INTO action_trigger.event_definition (
3417 ,name || ' (clone of '||target_event_def||')'
3433 action_trigger.event_definition
3435 id = target_event_def
3437 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3438 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3439 INSERT INTO action_trigger.environment (
3445 currval('action_trigger.event_definition_id_seq')
3450 action_trigger.environment
3452 event_def = target_event_def
3454 INSERT INTO action_trigger.event_params (
3459 currval('action_trigger.event_definition_id_seq')
3463 action_trigger.event_params
3465 event_def = target_event_def
3468 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);
3470 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3472 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3473 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3476 target_event_defs ALIAS FOR $2;
3478 DROP TABLE IF EXISTS new_atevdefs;
3479 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3480 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3481 INSERT INTO action_trigger.event_definition (
3502 ,name || ' (clone of '||target_event_defs[i]||')'
3518 action_trigger.event_definition
3520 id = target_event_defs[i]
3522 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3523 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3524 INSERT INTO action_trigger.environment (
3530 currval('action_trigger.event_definition_id_seq')
3535 action_trigger.environment
3537 event_def = target_event_defs[i]
3539 INSERT INTO action_trigger.event_params (
3544 currval('action_trigger.event_definition_id_seq')
3548 action_trigger.event_params
3550 event_def = target_event_defs[i]
3553 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3555 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3557 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3559 action_trigger.event
3563 ,complete_time = NULL
3564 ,update_process = NULL
3566 ,template_output = NULL
3567 ,error_output = NULL
3568 ,async_output = NULL
3573 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3574 SELECT action.find_hold_matrix_matchpoint(
3575 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3576 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3577 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3578 (SELECT usr FROM action.hold_request WHERE id = $1),
3579 (SELECT requestor FROM action.hold_request WHERE id = $1)
3583 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3584 SELECT action.hold_request_permit_test(
3585 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3586 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3587 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3588 (SELECT usr FROM action.hold_request WHERE id = $1),
3589 (SELECT requestor FROM action.hold_request WHERE id = $1)
3593 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3594 SELECT action.find_circ_matrix_matchpoint(
3595 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3596 (SELECT target_copy FROM action.circulation WHERE id = $1),
3597 (SELECT usr FROM action.circulation WHERE id = $1),
3599 NULLIF(phone_renewal,false),
3600 NULLIF(desk_renewal,false),
3601 NULLIF(opac_renewal,false),
3603 ) FROM action.circulation WHERE id = $1
3608 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3613 RAISE EXCEPTION 'assertion';
3616 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3618 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3624 RAISE EXCEPTION '%', msg;
3627 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3629 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3632 fail_msg ALIAS FOR $2;
3633 success_msg ALIAS FOR $3;
3636 RAISE EXCEPTION '%', fail_msg;
3640 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3642 -- push bib sequence and return starting value for reserved range
3643 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3645 bib_count ALIAS FOR $1;
3648 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3650 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3655 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3657 -- set a new salted password
3659 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3661 usr_id ALIAS FOR $1;
3662 plain_passwd ALIAS FOR $2;
3667 SELECT actor.create_salt('main') INTO plain_salt;
3669 SELECT MD5(plain_passwd) INTO md5_passwd;
3671 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3676 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3679 -- convenience functions for handling copy_location maps
3680 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3681 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3684 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3686 table_schema ALIAS FOR $1;
3687 table_name ALIAS FOR $2;
3688 org_shortname ALIAS FOR $3;
3689 org_range ALIAS FOR $4;
3690 make_assertion ALIAS FOR $5;
3693 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3694 -- though we'll still use the passed org for the full path traversal when needed
3695 x_org_found BOOLEAN;
3701 EXECUTE 'SELECT EXISTS (
3703 FROM information_schema.columns
3704 WHERE table_schema = $1
3706 and column_name = ''desired_shelf''
3707 )' INTO proceed USING table_schema, table_name;
3709 RAISE EXCEPTION 'Missing column desired_shelf';
3712 EXECUTE 'SELECT EXISTS (
3714 FROM information_schema.columns
3715 WHERE table_schema = $1
3717 and column_name = ''x_org''
3718 )' INTO x_org_found USING table_schema, table_name;
3720 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3722 RAISE EXCEPTION 'Cannot find org by shortname';
3725 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3727 EXECUTE 'ALTER TABLE '
3728 || quote_ident(table_name)
3729 || ' DROP COLUMN IF EXISTS x_shelf';
3730 EXECUTE 'ALTER TABLE '
3731 || quote_ident(table_name)
3732 || ' ADD COLUMN x_shelf INTEGER';
3735 RAISE INFO 'Found x_org column';
3736 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3737 || ' SET x_shelf = b.id FROM asset_copy_location b'
3738 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3739 || ' AND b.owning_lib = x_org'
3740 || ' AND NOT b.deleted';
3741 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3742 || ' SET x_shelf = b.id FROM asset.copy_location b'
3743 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3744 || ' AND b.owning_lib = x_org'
3745 || ' AND x_shelf IS NULL'
3746 || ' AND NOT b.deleted';
3748 RAISE INFO 'Did not find x_org column';
3749 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3750 || ' SET x_shelf = b.id FROM asset_copy_location b'
3751 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3752 || ' AND b.owning_lib = $1'
3753 || ' AND NOT b.deleted'
3755 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3756 || ' SET x_shelf = b.id FROM asset_copy_location b'
3757 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3758 || ' AND b.owning_lib = $1'
3759 || ' AND x_shelf IS NULL'
3760 || ' AND NOT b.deleted'
3764 FOREACH o IN ARRAY org_list LOOP
3765 RAISE INFO 'Considering org %', o;
3766 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3767 || ' SET x_shelf = b.id FROM asset.copy_location b'
3768 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3769 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3770 || ' AND NOT b.deleted'
3772 GET DIAGNOSTICS row_count = ROW_COUNT;
3773 RAISE INFO 'Updated % rows', row_count;
3776 IF make_assertion THEN
3777 EXECUTE 'SELECT migration_tools.assert(
3778 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3779 ''Cannot find a desired location'',
3780 ''Found all desired locations''
3785 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3787 -- convenience functions for handling circmod maps
3789 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3791 table_schema ALIAS FOR $1;
3792 table_name ALIAS FOR $2;
3795 EXECUTE 'SELECT EXISTS (
3797 FROM information_schema.columns
3798 WHERE table_schema = $1
3800 and column_name = ''desired_circmod''
3801 )' INTO proceed USING table_schema, table_name;
3803 RAISE EXCEPTION 'Missing column desired_circmod';
3806 EXECUTE 'ALTER TABLE '
3807 || quote_ident(table_name)
3808 || ' DROP COLUMN IF EXISTS x_circmod';
3809 EXECUTE 'ALTER TABLE '
3810 || quote_ident(table_name)
3811 || ' ADD COLUMN x_circmod TEXT';
3813 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3814 || ' SET x_circmod = code FROM config.circ_modifier b'
3815 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3817 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3818 || ' SET x_circmod = code FROM config.circ_modifier b'
3819 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3820 || ' AND x_circmod IS NULL';
3822 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3823 || ' SET x_circmod = code FROM config.circ_modifier b'
3824 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3825 || ' AND x_circmod IS NULL';
3827 EXECUTE 'SELECT migration_tools.assert(
3828 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3829 ''Cannot find a desired circulation modifier'',
3830 ''Found all desired circulation modifiers''
3834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3836 -- convenience functions for handling item status maps
3838 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3840 table_schema ALIAS FOR $1;
3841 table_name ALIAS FOR $2;
3844 EXECUTE 'SELECT EXISTS (
3846 FROM information_schema.columns
3847 WHERE table_schema = $1
3849 and column_name = ''desired_status''
3850 )' INTO proceed USING table_schema, table_name;
3852 RAISE EXCEPTION 'Missing column desired_status';
3855 EXECUTE 'ALTER TABLE '
3856 || quote_ident(table_name)
3857 || ' DROP COLUMN IF EXISTS x_status';
3858 EXECUTE 'ALTER TABLE '
3859 || quote_ident(table_name)
3860 || ' ADD COLUMN x_status INTEGER';
3862 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3863 || ' SET x_status = id FROM config.copy_status b'
3864 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3866 EXECUTE 'SELECT migration_tools.assert(
3867 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3868 ''Cannot find a desired copy status'',
3869 ''Found all desired copy statuses''
3873 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3875 -- convenience functions for handling org maps
3877 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3879 table_schema ALIAS FOR $1;
3880 table_name ALIAS FOR $2;
3883 EXECUTE 'SELECT EXISTS (
3885 FROM information_schema.columns
3886 WHERE table_schema = $1
3888 and column_name = ''desired_org''
3889 )' INTO proceed USING table_schema, table_name;
3891 RAISE EXCEPTION 'Missing column desired_org';
3894 EXECUTE 'ALTER TABLE '
3895 || quote_ident(table_name)
3896 || ' DROP COLUMN IF EXISTS x_org';
3897 EXECUTE 'ALTER TABLE '
3898 || quote_ident(table_name)
3899 || ' ADD COLUMN x_org INTEGER';
3901 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3902 || ' SET x_org = b.id FROM actor.org_unit b'
3903 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3905 EXECUTE 'SELECT migration_tools.assert(
3906 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3907 ''Cannot find a desired org unit'',
3908 ''Found all desired org units''
3912 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3914 -- convenience function for handling desired_not_migrate
3916 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3918 table_schema ALIAS FOR $1;
3919 table_name ALIAS FOR $2;
3922 EXECUTE 'SELECT EXISTS (
3924 FROM information_schema.columns
3925 WHERE table_schema = $1
3927 and column_name = ''desired_not_migrate''
3928 )' INTO proceed USING table_schema, table_name;
3930 RAISE EXCEPTION 'Missing column desired_not_migrate';
3933 EXECUTE 'ALTER TABLE '
3934 || quote_ident(table_name)
3935 || ' DROP COLUMN IF EXISTS x_migrate';
3936 EXECUTE 'ALTER TABLE '
3937 || quote_ident(table_name)
3938 || ' ADD COLUMN x_migrate BOOLEAN';
3940 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3941 || ' SET x_migrate = CASE'
3942 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3943 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3944 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3945 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3946 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3947 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3950 EXECUTE 'SELECT migration_tools.assert(
3951 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3952 ''Not all desired_not_migrate values understood'',
3953 ''All desired_not_migrate values understood''
3957 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3959 -- convenience function for handling desired_not_migrate
3961 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3963 table_schema ALIAS FOR $1;
3964 table_name ALIAS FOR $2;
3967 EXECUTE 'SELECT EXISTS (
3969 FROM information_schema.columns
3970 WHERE table_schema = $1
3972 and column_name = ''desired_barred_or_blocked''
3973 )' INTO proceed USING table_schema, table_name;
3975 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3978 EXECUTE 'ALTER TABLE '
3979 || quote_ident(table_name)
3980 || ' DROP COLUMN IF EXISTS x_barred';
3981 EXECUTE 'ALTER TABLE '
3982 || quote_ident(table_name)
3983 || ' ADD COLUMN x_barred BOOLEAN';
3985 EXECUTE 'ALTER TABLE '
3986 || quote_ident(table_name)
3987 || ' DROP COLUMN IF EXISTS x_blocked';
3988 EXECUTE 'ALTER TABLE '
3989 || quote_ident(table_name)
3990 || ' ADD COLUMN x_blocked BOOLEAN';
3992 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3993 || ' SET x_barred = CASE'
3994 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3995 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3996 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3997 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4000 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4001 || ' SET x_blocked = CASE'
4002 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4003 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4004 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4005 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4008 EXECUTE 'SELECT migration_tools.assert(
4009 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4010 ''Not all desired_barred_or_blocked values understood'',
4011 ''All desired_barred_or_blocked values understood''
4015 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4017 -- convenience function for handling desired_profile
4019 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4021 table_schema ALIAS FOR $1;
4022 table_name ALIAS FOR $2;
4025 EXECUTE 'SELECT EXISTS (
4027 FROM information_schema.columns
4028 WHERE table_schema = $1
4030 and column_name = ''desired_profile''
4031 )' INTO proceed USING table_schema, table_name;
4033 RAISE EXCEPTION 'Missing column desired_profile';
4036 EXECUTE 'ALTER TABLE '
4037 || quote_ident(table_name)
4038 || ' DROP COLUMN IF EXISTS x_profile';
4039 EXECUTE 'ALTER TABLE '
4040 || quote_ident(table_name)
4041 || ' ADD COLUMN x_profile INTEGER';
4043 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4044 || ' SET x_profile = b.id FROM permission.grp_tree b'
4045 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4047 EXECUTE 'SELECT migration_tools.assert(
4048 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4049 ''Cannot find a desired profile'',
4050 ''Found all desired profiles''
4054 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4056 -- convenience function for handling desired actor stat cats
4058 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4060 table_schema ALIAS FOR $1;
4061 table_name ALIAS FOR $2;
4062 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4063 org_shortname ALIAS FOR $4;
4071 SELECT 'desired_sc' || field_suffix INTO sc;
4072 SELECT 'desired_sce' || field_suffix INTO sce;
4074 EXECUTE 'SELECT EXISTS (
4076 FROM information_schema.columns
4077 WHERE table_schema = $1
4079 and column_name = $3
4080 )' INTO proceed USING table_schema, table_name, sc;
4082 RAISE EXCEPTION 'Missing column %', sc;
4084 EXECUTE 'SELECT EXISTS (
4086 FROM information_schema.columns
4087 WHERE table_schema = $1
4089 and column_name = $3
4090 )' INTO proceed USING table_schema, table_name, sce;
4092 RAISE EXCEPTION 'Missing column %', sce;
4095 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4097 RAISE EXCEPTION 'Cannot find org by shortname';
4099 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4101 -- caller responsible for their own truncates though we try to prevent duplicates
4102 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4107 ' || quote_ident(table_name) || '
4109 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4113 WHERE owner = ANY ($2)
4114 AND name = BTRIM('||sc||')
4119 WHERE owner = ANY ($2)
4120 AND name = BTRIM('||sc||')
4123 USING org, org_list;
4125 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4130 WHERE owner = ANY ($2)
4131 AND BTRIM('||sc||') = BTRIM(name))
4134 WHERE owner = ANY ($2)
4135 AND BTRIM('||sc||') = BTRIM(name))
4140 ' || quote_ident(table_name) || '
4142 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4143 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4146 FROM actor.stat_cat_entry
4150 WHERE owner = ANY ($2)
4151 AND BTRIM('||sc||') = BTRIM(name)
4152 ) AND value = BTRIM('||sce||')
4153 AND owner = ANY ($2)
4157 FROM actor_stat_cat_entry
4161 WHERE owner = ANY ($2)
4162 AND BTRIM('||sc||') = BTRIM(name)
4163 ) AND value = BTRIM('||sce||')
4164 AND owner = ANY ($2)
4167 USING org, org_list;
4169 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4171 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4173 table_schema ALIAS FOR $1;
4174 table_name ALIAS FOR $2;
4175 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4176 org_shortname ALIAS FOR $4;
4184 SELECT 'desired_sc' || field_suffix INTO sc;
4185 SELECT 'desired_sce' || field_suffix INTO sce;
4186 EXECUTE 'SELECT EXISTS (
4188 FROM information_schema.columns
4189 WHERE table_schema = $1
4191 and column_name = $3
4192 )' INTO proceed USING table_schema, table_name, sc;
4194 RAISE EXCEPTION 'Missing column %', sc;
4196 EXECUTE 'SELECT EXISTS (
4198 FROM information_schema.columns
4199 WHERE table_schema = $1
4201 and column_name = $3
4202 )' INTO proceed USING table_schema, table_name, sce;
4204 RAISE EXCEPTION 'Missing column %', sce;
4207 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4209 RAISE EXCEPTION 'Cannot find org by shortname';
4212 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4214 EXECUTE 'ALTER TABLE '
4215 || quote_ident(table_name)
4216 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4217 EXECUTE 'ALTER TABLE '
4218 || quote_ident(table_name)
4219 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4220 EXECUTE 'ALTER TABLE '
4221 || quote_ident(table_name)
4222 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4223 EXECUTE 'ALTER TABLE '
4224 || quote_ident(table_name)
4225 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4228 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4230 x_sc' || field_suffix || ' = id
4232 (SELECT id, name, owner FROM actor_stat_cat
4233 UNION SELECT id, name, owner FROM actor.stat_cat) u
4235 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4236 AND u.owner = ANY ($1);'
4239 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4241 x_sce' || field_suffix || ' = id
4243 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4244 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4246 u.stat_cat = x_sc' || field_suffix || '
4247 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4248 AND u.owner = ANY ($1);'
4251 EXECUTE 'SELECT migration_tools.assert(
4252 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4253 ''Cannot find a desired stat cat'',
4254 ''Found all desired stat cats''
4257 EXECUTE 'SELECT migration_tools.assert(
4258 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4259 ''Cannot find a desired stat cat entry'',
4260 ''Found all desired stat cat entries''
4264 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4266 -- convenience functions for adding shelving locations
4267 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4268 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4274 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4277 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4278 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4279 IF return_id IS NOT NULL THEN
4287 $$ LANGUAGE plpgsql;
4289 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4291 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4292 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4298 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4301 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4303 SELECT INTO return_id id FROM
4304 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4305 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4306 IF return_id IS NOT NULL THEN
4314 $$ LANGUAGE plpgsql;
4318 -- convenience function for linking to the item staging table
4320 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4322 table_schema ALIAS FOR $1;
4323 table_name ALIAS FOR $2;
4324 foreign_column_name ALIAS FOR $3;
4325 main_column_name ALIAS FOR $4;
4326 btrim_desired ALIAS FOR $5;
4329 EXECUTE 'SELECT EXISTS (
4331 FROM information_schema.columns
4332 WHERE table_schema = $1
4334 and column_name = $3
4335 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4337 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4340 EXECUTE 'SELECT EXISTS (
4342 FROM information_schema.columns
4343 WHERE table_schema = $1
4344 AND table_name = ''asset_copy_legacy''
4345 and column_name = $2
4346 )' INTO proceed USING table_schema, main_column_name;
4348 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4351 EXECUTE 'ALTER TABLE '
4352 || quote_ident(table_name)
4353 || ' DROP COLUMN IF EXISTS x_item';
4354 EXECUTE 'ALTER TABLE '
4355 || quote_ident(table_name)
4356 || ' ADD COLUMN x_item BIGINT';
4358 IF btrim_desired THEN
4359 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4360 || ' SET x_item = b.id FROM asset_copy_legacy b'
4361 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4362 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4364 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4365 || ' SET x_item = b.id FROM asset_copy_legacy b'
4366 || ' WHERE a.' || quote_ident(foreign_column_name)
4367 || ' = b.' || quote_ident(main_column_name);
4370 --EXECUTE 'SELECT migration_tools.assert(
4371 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4372 -- ''Cannot link every barcode'',
4373 -- ''Every barcode linked''
4377 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4379 -- convenience function for linking to the user staging table
4381 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4383 table_schema ALIAS FOR $1;
4384 table_name ALIAS FOR $2;
4385 foreign_column_name ALIAS FOR $3;
4386 main_column_name ALIAS FOR $4;
4387 btrim_desired ALIAS FOR $5;
4390 EXECUTE 'SELECT EXISTS (
4392 FROM information_schema.columns
4393 WHERE table_schema = $1
4395 and column_name = $3
4396 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4398 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4401 EXECUTE 'SELECT EXISTS (
4403 FROM information_schema.columns
4404 WHERE table_schema = $1
4405 AND table_name = ''actor_usr_legacy''
4406 and column_name = $2
4407 )' INTO proceed USING table_schema, main_column_name;
4409 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4412 EXECUTE 'ALTER TABLE '
4413 || quote_ident(table_name)
4414 || ' DROP COLUMN IF EXISTS x_user';
4415 EXECUTE 'ALTER TABLE '
4416 || quote_ident(table_name)
4417 || ' ADD COLUMN x_user INTEGER';
4419 IF btrim_desired THEN
4420 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4421 || ' SET x_user = b.id FROM actor_usr_legacy b'
4422 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4423 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4425 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4426 || ' SET x_user = b.id FROM actor_usr_legacy b'
4427 || ' WHERE a.' || quote_ident(foreign_column_name)
4428 || ' = b.' || quote_ident(main_column_name);
4431 --EXECUTE 'SELECT migration_tools.assert(
4432 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4433 -- ''Cannot link every barcode'',
4434 -- ''Every barcode linked''
4438 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4440 -- convenience function for linking two tables
4441 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4442 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4444 table_schema ALIAS FOR $1;
4445 table_a ALIAS FOR $2;
4446 column_a ALIAS FOR $3;
4447 table_b ALIAS FOR $4;
4448 column_b ALIAS FOR $5;
4449 column_x ALIAS FOR $6;
4450 btrim_desired ALIAS FOR $7;
4453 EXECUTE 'SELECT EXISTS (
4455 FROM information_schema.columns
4456 WHERE table_schema = $1
4458 and column_name = $3
4459 )' INTO proceed USING table_schema, table_a, column_a;
4461 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4464 EXECUTE 'SELECT EXISTS (
4466 FROM information_schema.columns
4467 WHERE table_schema = $1
4469 and column_name = $3
4470 )' INTO proceed USING table_schema, table_b, column_b;
4472 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4475 EXECUTE 'ALTER TABLE '
4476 || quote_ident(table_b)
4477 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4478 EXECUTE 'ALTER TABLE '
4479 || quote_ident(table_b)
4480 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4482 IF btrim_desired THEN
4483 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4484 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4485 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4486 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4488 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4489 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4490 || ' WHERE a.' || quote_ident(column_a)
4491 || ' = b.' || quote_ident(column_b);
4495 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4497 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4498 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4499 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4501 table_schema ALIAS FOR $1;
4502 table_a ALIAS FOR $2;
4503 column_a ALIAS FOR $3;
4504 table_b ALIAS FOR $4;
4505 column_b ALIAS FOR $5;
4506 column_w ALIAS FOR $6;
4507 column_x ALIAS FOR $7;
4508 btrim_desired ALIAS FOR $8;
4511 EXECUTE 'SELECT EXISTS (
4513 FROM information_schema.columns
4514 WHERE table_schema = $1
4516 and column_name = $3
4517 )' INTO proceed USING table_schema, table_a, column_a;
4519 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4522 EXECUTE 'SELECT EXISTS (
4524 FROM information_schema.columns
4525 WHERE table_schema = $1
4527 and column_name = $3
4528 )' INTO proceed USING table_schema, table_b, column_b;
4530 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4533 EXECUTE 'ALTER TABLE '
4534 || quote_ident(table_b)
4535 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4536 EXECUTE 'ALTER TABLE '
4537 || quote_ident(table_b)
4538 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4540 IF btrim_desired THEN
4541 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4542 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4543 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4544 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4546 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4547 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4548 || ' WHERE a.' || quote_ident(column_a)
4549 || ' = b.' || quote_ident(column_b);
4553 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4555 -- 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
4556 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4557 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4559 table_schema ALIAS FOR $1;
4560 table_a ALIAS FOR $2;
4561 column_a ALIAS FOR $3;
4562 table_b ALIAS FOR $4;
4563 column_b ALIAS FOR $5;
4564 column_w ALIAS FOR $6;
4565 column_x ALIAS FOR $7;
4568 EXECUTE 'SELECT EXISTS (
4570 FROM information_schema.columns
4571 WHERE table_schema = $1
4573 and column_name = $3
4574 )' INTO proceed USING table_schema, table_a, column_a;
4576 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4579 EXECUTE 'SELECT EXISTS (
4581 FROM information_schema.columns
4582 WHERE table_schema = $1
4584 and column_name = $3
4585 )' INTO proceed USING table_schema, table_b, column_b;
4587 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4590 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4591 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4592 || ' WHERE a.' || quote_ident(column_a)
4593 || ' = b.' || quote_ident(column_b);
4596 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4598 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4600 table_schema ALIAS FOR $1;
4601 table_a ALIAS FOR $2;
4602 column_a ALIAS FOR $3;
4603 table_b ALIAS FOR $4;
4604 column_b ALIAS FOR $5;
4605 column_w ALIAS FOR $6;
4606 column_x ALIAS FOR $7;
4609 EXECUTE 'SELECT EXISTS (
4611 FROM information_schema.columns
4612 WHERE table_schema = $1
4614 and column_name = $3
4615 )' INTO proceed USING table_schema, table_a, column_a;
4617 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4620 EXECUTE 'SELECT EXISTS (
4622 FROM information_schema.columns
4623 WHERE table_schema = $1
4625 and column_name = $3
4626 )' INTO proceed USING table_schema, table_b, column_b;
4628 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4631 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4632 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4633 || ' WHERE a.' || quote_ident(column_a)
4634 || ' = b.' || quote_ident(column_b)
4635 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4638 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4640 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4642 table_schema ALIAS FOR $1;
4643 table_a ALIAS FOR $2;
4644 column_a ALIAS FOR $3;
4645 table_b ALIAS FOR $4;
4646 column_b ALIAS FOR $5;
4647 column_w ALIAS FOR $6;
4648 column_x ALIAS FOR $7;
4651 EXECUTE 'SELECT EXISTS (
4653 FROM information_schema.columns
4654 WHERE table_schema = $1
4656 and column_name = $3
4657 )' INTO proceed USING table_schema, table_a, column_a;
4659 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4662 EXECUTE 'SELECT EXISTS (
4664 FROM information_schema.columns
4665 WHERE table_schema = $1
4667 and column_name = $3
4668 )' INTO proceed USING table_schema, table_b, column_b;
4670 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4673 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4674 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4675 || ' WHERE a.' || quote_ident(column_a)
4676 || ' = b.' || quote_ident(column_b)
4677 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4680 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4682 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4684 table_schema ALIAS FOR $1;
4685 table_a ALIAS FOR $2;
4686 column_a ALIAS FOR $3;
4687 table_b ALIAS FOR $4;
4688 column_b ALIAS FOR $5;
4689 column_w ALIAS FOR $6;
4690 column_x ALIAS FOR $7;
4693 EXECUTE 'SELECT EXISTS (
4695 FROM information_schema.columns
4696 WHERE table_schema = $1
4698 and column_name = $3
4699 )' INTO proceed USING table_schema, table_a, column_a;
4701 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4704 EXECUTE 'SELECT EXISTS (
4706 FROM information_schema.columns
4707 WHERE table_schema = $1
4709 and column_name = $3
4710 )' INTO proceed USING table_schema, table_b, column_b;
4712 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4715 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4716 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4717 || ' WHERE a.' || quote_ident(column_a)
4718 || ' = b.' || quote_ident(column_b)
4719 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4724 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4726 table_schema ALIAS FOR $1;
4727 table_a ALIAS FOR $2;
4728 column_a ALIAS FOR $3;
4729 table_b ALIAS FOR $4;
4730 column_b ALIAS FOR $5;
4731 column_w ALIAS FOR $6;
4732 column_x ALIAS FOR $7;
4735 EXECUTE 'SELECT EXISTS (
4737 FROM information_schema.columns
4738 WHERE table_schema = $1
4740 and column_name = $3
4741 )' INTO proceed USING table_schema, table_a, column_a;
4743 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4746 EXECUTE 'SELECT EXISTS (
4748 FROM information_schema.columns
4749 WHERE table_schema = $1
4751 and column_name = $3
4752 )' INTO proceed USING table_schema, table_b, column_b;
4754 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4757 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4758 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4759 || ' WHERE a.' || quote_ident(column_a)
4760 || ' = b.' || quote_ident(column_b)
4761 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4764 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4766 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4768 table_schema ALIAS FOR $1;
4769 table_a ALIAS FOR $2;
4770 column_a ALIAS FOR $3;
4771 table_b ALIAS FOR $4;
4772 column_b ALIAS FOR $5;
4773 column_w ALIAS FOR $6;
4774 column_x ALIAS FOR $7;
4777 EXECUTE 'SELECT EXISTS (
4779 FROM information_schema.columns
4780 WHERE table_schema = $1
4782 and column_name = $3
4783 )' INTO proceed USING table_schema, table_a, column_a;
4785 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4788 EXECUTE 'SELECT EXISTS (
4790 FROM information_schema.columns
4791 WHERE table_schema = $1
4793 and column_name = $3
4794 )' INTO proceed USING table_schema, table_b, column_b;
4796 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4799 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4800 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4801 || ' WHERE a.' || quote_ident(column_a)
4802 || ' = b.' || quote_ident(column_b)
4803 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4806 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4808 -- convenience function for handling desired asset stat cats
4810 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4812 table_schema ALIAS FOR $1;
4813 table_name ALIAS FOR $2;
4814 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4815 org_shortname ALIAS FOR $4;
4823 SELECT 'desired_sc' || field_suffix INTO sc;
4824 SELECT 'desired_sce' || field_suffix INTO sce;
4826 EXECUTE 'SELECT EXISTS (
4828 FROM information_schema.columns
4829 WHERE table_schema = $1
4831 and column_name = $3
4832 )' INTO proceed USING table_schema, table_name, sc;
4834 RAISE EXCEPTION 'Missing column %', sc;
4836 EXECUTE 'SELECT EXISTS (
4838 FROM information_schema.columns
4839 WHERE table_schema = $1
4841 and column_name = $3
4842 )' INTO proceed USING table_schema, table_name, sce;
4844 RAISE EXCEPTION 'Missing column %', sce;
4847 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4849 RAISE EXCEPTION 'Cannot find org by shortname';
4851 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4853 -- caller responsible for their own truncates though we try to prevent duplicates
4854 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4859 ' || quote_ident(table_name) || '
4861 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4865 WHERE owner = ANY ($2)
4866 AND name = BTRIM('||sc||')
4871 WHERE owner = ANY ($2)
4872 AND name = BTRIM('||sc||')
4875 USING org, org_list;
4877 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4882 WHERE owner = ANY ($2)
4883 AND BTRIM('||sc||') = BTRIM(name))
4886 WHERE owner = ANY ($2)
4887 AND BTRIM('||sc||') = BTRIM(name))
4892 ' || quote_ident(table_name) || '
4894 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4895 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4898 FROM asset.stat_cat_entry
4902 WHERE owner = ANY ($2)
4903 AND BTRIM('||sc||') = BTRIM(name)
4904 ) AND value = BTRIM('||sce||')
4905 AND owner = ANY ($2)
4909 FROM asset_stat_cat_entry
4913 WHERE owner = ANY ($2)
4914 AND BTRIM('||sc||') = BTRIM(name)
4915 ) AND value = BTRIM('||sce||')
4916 AND owner = ANY ($2)
4919 USING org, org_list;
4921 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4923 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4925 table_schema ALIAS FOR $1;
4926 table_name ALIAS FOR $2;
4927 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4928 org_shortname ALIAS FOR $4;
4936 SELECT 'desired_sc' || field_suffix INTO sc;
4937 SELECT 'desired_sce' || field_suffix INTO sce;
4938 EXECUTE 'SELECT EXISTS (
4940 FROM information_schema.columns
4941 WHERE table_schema = $1
4943 and column_name = $3
4944 )' INTO proceed USING table_schema, table_name, sc;
4946 RAISE EXCEPTION 'Missing column %', sc;
4948 EXECUTE 'SELECT EXISTS (
4950 FROM information_schema.columns
4951 WHERE table_schema = $1
4953 and column_name = $3
4954 )' INTO proceed USING table_schema, table_name, sce;
4956 RAISE EXCEPTION 'Missing column %', sce;
4959 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4961 RAISE EXCEPTION 'Cannot find org by shortname';
4964 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4966 EXECUTE 'ALTER TABLE '
4967 || quote_ident(table_name)
4968 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4969 EXECUTE 'ALTER TABLE '
4970 || quote_ident(table_name)
4971 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4972 EXECUTE 'ALTER TABLE '
4973 || quote_ident(table_name)
4974 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4975 EXECUTE 'ALTER TABLE '
4976 || quote_ident(table_name)
4977 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4980 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4982 x_sc' || field_suffix || ' = id
4984 (SELECT id, name, owner FROM asset_stat_cat
4985 UNION SELECT id, name, owner FROM asset.stat_cat) u
4987 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4988 AND u.owner = ANY ($1);'
4991 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4993 x_sce' || field_suffix || ' = id
4995 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4996 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4998 u.stat_cat = x_sc' || field_suffix || '
4999 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5000 AND u.owner = ANY ($1);'
5003 EXECUTE 'SELECT migration_tools.assert(
5004 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5005 ''Cannot find a desired stat cat'',
5006 ''Found all desired stat cats''
5009 EXECUTE 'SELECT migration_tools.assert(
5010 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5011 ''Cannot find a desired stat cat entry'',
5012 ''Found all desired stat cat entries''
5016 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5018 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5019 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5026 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5028 AND table_schema = s_name
5029 AND (data_type='text' OR data_type='character varying')
5030 AND column_name like 'l_%'
5032 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5039 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5040 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5047 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5049 AND table_schema = s_name
5050 AND (data_type='text' OR data_type='character varying')
5052 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5059 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5060 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5067 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5069 AND table_schema = s_name
5070 AND (data_type='text' OR data_type='character varying')
5071 AND column_name like 'l_%'
5073 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5080 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5081 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5088 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5090 AND table_schema = s_name
5091 AND (data_type='text' OR data_type='character varying')
5093 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5101 -- convenience function for handling item barcode collisions in asset_copy_legacy
5103 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5108 internal_collision_count NUMERIC := 0;
5109 incumbent_collision_count NUMERIC := 0;
5111 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5113 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5115 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5116 GET DIAGNOSTICS row_count = ROW_COUNT;
5117 internal_collision_count := internal_collision_count + row_count;
5120 RAISE INFO '% internal collisions', internal_collision_count;
5121 FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
5123 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5125 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5126 GET DIAGNOSTICS row_count = ROW_COUNT;
5127 incumbent_collision_count := incumbent_collision_count + row_count;
5130 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5132 $function$ LANGUAGE plpgsql;
5134 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5135 -- this should be ran prior to populating actor_card
5137 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5142 internal_collision_count NUMERIC := 0;
5143 incumbent_barcode_collision_count NUMERIC := 0;
5144 incumbent_usrname_collision_count NUMERIC := 0;
5146 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5148 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5150 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5151 GET DIAGNOSTICS row_count = ROW_COUNT;
5152 internal_collision_count := internal_collision_count + row_count;
5155 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5158 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5160 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5162 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5163 GET DIAGNOSTICS row_count = ROW_COUNT;
5164 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5167 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5170 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5172 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5174 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5175 GET DIAGNOSTICS row_count = ROW_COUNT;
5176 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5179 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5181 $function$ LANGUAGE plpgsql;
5183 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5185 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5190 internal_collision_count NUMERIC := 0;
5191 incumbent_collision_count NUMERIC := 0;
5193 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5195 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5197 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5198 GET DIAGNOSTICS row_count = ROW_COUNT;
5199 internal_collision_count := internal_collision_count + row_count;
5202 RAISE INFO '% internal collisions', internal_collision_count;
5203 FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
5205 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5207 UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5208 GET DIAGNOSTICS row_count = ROW_COUNT;
5209 incumbent_collision_count := incumbent_collision_count + row_count;
5212 RAISE INFO '% incumbent collisions', incumbent_collision_count;
5214 $function$ LANGUAGE plpgsql;
5216 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5217 -- this should be ran prior to populating actor_card
5219 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5224 internal_collision_count NUMERIC := 0;
5225 incumbent_barcode_collision_count NUMERIC := 0;
5226 incumbent_usrname_collision_count NUMERIC := 0;
5228 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5230 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5232 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5233 GET DIAGNOSTICS row_count = ROW_COUNT;
5234 internal_collision_count := internal_collision_count + row_count;
5237 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5240 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5242 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5244 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5245 GET DIAGNOSTICS row_count = ROW_COUNT;
5246 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5249 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5252 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5254 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5256 UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5257 GET DIAGNOSTICS row_count = ROW_COUNT;
5258 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5261 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5263 $function$ LANGUAGE plpgsql;
5265 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5266 -- WARNING: Use at your own risk
5267 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5269 item_object asset.copy%ROWTYPE;
5270 user_object actor.usr%ROWTYPE;
5271 test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5272 result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5273 safe_to_delete BOOLEAN := FALSE;
5274 m action.found_circ_matrix_matchpoint;
5275 n action.found_circ_matrix_matchpoint;
5276 -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5277 result_matchpoint INTEGER;
5279 SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5280 RAISE INFO 'testing rule: %', test_rule_object;
5282 INSERT INTO actor.usr (
5292 COALESCE(test_rule_object.grp, 2),
5293 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5298 COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5299 COALESCE(test_rule_object.juvenile_flag, FALSE)
5302 SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5304 INSERT INTO asset.call_number (
5315 COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
5316 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5320 INSERT INTO asset.copy (
5332 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5333 COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
5335 currval('asset.call_number_id_seq'),
5337 COALESCE(test_rule_object.copy_location,1),
5340 COALESCE(test_rule_object.ref_flag,FALSE),
5341 test_rule_object.circ_modifier
5344 SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
5346 SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
5347 test_rule_object.org_unit,
5350 COALESCE(test_rule_object.is_renewal,FALSE)
5352 RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
5353 test_rule_object.org_unit,
5356 COALESCE(test_rule_object.is_renewal,FALSE),
5362 -- disable the rule being tested to see if the outcome changes
5363 UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
5365 SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
5366 test_rule_object.org_unit,
5369 COALESCE(test_rule_object.is_renewal,FALSE)
5371 RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
5372 test_rule_object.org_unit,
5375 COALESCE(test_rule_object.is_renewal,FALSE),
5381 -- FIXME: We could dig deeper and see if the referenced config.rule_*
5382 -- entries are effectively equivalent, but for now, let's assume no
5383 -- duplicate rules at that level
5385 (m.matchpoint).circulate = (n.matchpoint).circulate
5386 AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
5387 AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
5388 AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
5390 (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
5392 (m.matchpoint).hard_due_date IS NULL
5393 AND (n.matchpoint).hard_due_date IS NULL
5397 (m.matchpoint).renewals = (n.matchpoint).renewals
5399 (m.matchpoint).renewals IS NULL
5400 AND (n.matchpoint).renewals IS NULL
5404 (m.matchpoint).grace_period = (n.matchpoint).grace_period
5406 (m.matchpoint).grace_period IS NULL
5407 AND (n.matchpoint).grace_period IS NULL
5411 (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
5413 (m.matchpoint).total_copy_hold_ratio IS NULL
5414 AND (n.matchpoint).total_copy_hold_ratio IS NULL
5418 (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
5420 (m.matchpoint).available_copy_hold_ratio IS NULL
5421 AND (n.matchpoint).available_copy_hold_ratio IS NULL
5425 SELECT limit_set, fallthrough
5426 FROM config.circ_matrix_limit_set_map
5427 WHERE active and matchpoint = (m.matchpoint).id
5429 SELECT limit_set, fallthrough
5430 FROM config.circ_matrix_limit_set_map
5431 WHERE active and matchpoint = (n.matchpoint).id
5435 RAISE INFO 'rule has same outcome';
5436 safe_to_delete := TRUE;
5438 RAISE INFO 'rule has different outcome';
5439 safe_to_delete := FALSE;
5442 RAISE EXCEPTION 'rollback the temporary changes';
5444 EXCEPTION WHEN OTHERS THEN
5446 RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
5447 RETURN safe_to_delete;
5450 $func$ LANGUAGE plpgsql;