sql/base/base.sql

   1 -- Copyright 2009-2012, Equinox Software, Inc.
   2 --
   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.
   7 --
   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.
  12 --
  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.
  16
  17 --------------------------------------------------------------------------
  18 -- An example of how to use:
  19 --
  20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
  21 -- \i base.sql
  22 -- SELECT migration_tools.init('foo');
  23 -- SELECT migration_tools.build('foo');
  24 -- SELECT * FROM foo.fields_requiring_mapping;
  25 -- \d foo.actor_usr
  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')
  29
  30 CREATE SCHEMA migration_tools;
  31
  32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
  33     DECLARE
  34         migration_schema ALIAS FOR $1;
  35         output  RECORD;
  36     BEGIN
  37         FOR output IN
  38             EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
  39         LOOP
  40             RETURN output.tables;
  41         END LOOP;
  42     END;
  43 $$ LANGUAGE PLPGSQL STRICT STABLE;
  44
  45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
  46     DECLARE
  47         migration_schema ALIAS FOR $1;
  48         output TEXT;
  49     BEGIN
  50         FOR output IN
  51             EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
  52         LOOP
  53             RETURN output;
  54         END LOOP;
  55     END;
  56 $$ LANGUAGE PLPGSQL STRICT STABLE;
  57
  58
  59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
  60     DECLARE
  61         migration_schema ALIAS FOR $1;
  62         sql ALIAS FOR $2;
  63         nrows ALIAS FOR $3;
  64     BEGIN
  65         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
  66     END;
  67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  68
  69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
  70     DECLARE
  71         migration_schema ALIAS FOR $1;
  72         sql ALIAS FOR $2;
  73         nrows INTEGER;
  74     BEGIN
  75         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  76         --RAISE INFO '%', sql;
  77         EXECUTE sql;
  78         GET DIAGNOSTICS nrows = ROW_COUNT;
  79         PERFORM migration_tools.log(migration_schema,sql,nrows);
  80     EXCEPTION
  81         WHEN OTHERS THEN
  82             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
  83     END;
  84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  85
  86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
  87     DECLARE
  88         migration_schema ALIAS FOR $1;
  89         sql ALIAS FOR $2;
  90         nrows INTEGER;
  91     BEGIN
  92         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  93         RAISE INFO 'debug_exec sql = %', sql;
  94         EXECUTE sql;
  95         GET DIAGNOSTICS nrows = ROW_COUNT;
  96         PERFORM migration_tools.log(migration_schema,sql,nrows);
  97     EXCEPTION
  98         WHEN OTHERS THEN
  99             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 100     END;
 101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 102
 103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
 104     DECLARE
 105         migration_schema ALIAS FOR $1;
 106         sql TEXT;
 107     BEGIN
 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 ( '''' );';
 111         BEGIN
 112             SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
 113             EXECUTE sql;
 114         EXCEPTION
 115             WHEN OTHERS THEN
 116                 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 117         END;
 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 (
 126             id SERIAL,
 127             perm_grp_id INTEGER,
 128             transcribed_perm_group TEXT,
 129             legacy_field1 TEXT,
 130             legacy_value1 TEXT,
 131             legacy_field2 TEXT,
 132             legacy_value2 TEXT,
 133             legacy_field3 TEXT,
 134             legacy_value3 TEXT
 135         );' );
 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 (
 139             id SERIAL,
 140             evergreen_field TEXT,
 141             evergreen_value TEXT,
 142             evergreen_datatype TEXT,
 143             legacy_field1 TEXT,
 144             legacy_value1 TEXT,
 145             legacy_field2 TEXT,
 146             legacy_value2 TEXT,
 147             legacy_field3 TEXT,
 148             legacy_value3 TEXT
 149         );' );
 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 (
 156             id SERIAL,
 157             location INTEGER,
 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,
 163             legacy_field1 TEXT,
 164             legacy_value1 TEXT,
 165             legacy_field2 TEXT,
 166             legacy_value2 TEXT,
 167             legacy_field3 TEXT,
 168             legacy_value3 TEXT
 169         );' );
 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 (
 177             id SERIAL,
 178             circulate BOOLEAN,
 179             loan_period TEXT,
 180             max_renewals TEXT,
 181             max_out TEXT,
 182             fine_amount TEXT,
 183             fine_interval TEXT,
 184             max_fine TEXT,
 185             item_field1 TEXT,
 186             item_value1 TEXT,
 187             item_field2 TEXT,
 188             item_value2 TEXT,
 189             patron_field1 TEXT,
 190             patron_value1 TEXT,
 191             patron_field2 TEXT,
 192             patron_value2 TEXT
 193         );' );
 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'' );' );
 199
 200         BEGIN
 201             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
 202         EXCEPTION
 203             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
 204         END;
 205     END;
 206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 207
 208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
 209     DECLARE
 210         migration_schema ALIAS FOR $1;
 211         production_tables TEXT[];
 212     BEGIN
 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 );' );
 223     END;
 224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 225
 226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
 227     DECLARE
 228         migration_schema ALIAS FOR $1;
 229         production_tables ALIAS FOR $2;
 230     BEGIN
 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]);
 234         END LOOP;
 235     END;
 236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 237
 238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
 239     DECLARE
 240         migration_schema ALIAS FOR $1;
 241         production_table ALIAS FOR $2;
 242         base_staging_table TEXT;
 243         columns RECORD;
 244     BEGIN
 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;
 253         ' );
 254         FOR columns IN
 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
 258         LOOP
 259             PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
 260         END LOOP;
 261     END;
 262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 263
 264 CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
 265     DECLARE
 266         migration_schema ALIAS FOR $1;
 267         parent_table ALIAS FOR $2;
 268         source_table ALIAS FOR $3;
 269         columns RECORD;
 270         create_sql TEXT;
 271         insert_sql TEXT;
 272         column_list TEXT := '';
 273         column_count INTEGER := 0;
 274     BEGIN
 275         create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
 276         FOR columns IN
 277             SELECT table_schema, table_name, column_name, data_type
 278             FROM information_schema.columns
 279             WHERE table_schema = migration_schema AND table_name = source_table
 280         LOOP
 281             column_count := column_count + 1;
 282             if column_count > 1 then
 283                 create_sql := create_sql || ', ';
 284                 column_list := column_list || ', ';
 285             end if;
 286             create_sql := create_sql || columns.column_name || ' ';
 287             if columns.data_type = 'ARRAY' then
 288                 create_sql := create_sql || 'TEXT[]';
 289             else
 290                 create_sql := create_sql || columns.data_type;
 291             end if;
 292             column_list := column_list || columns.column_name;
 293         END LOOP;
 294         create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
 295         --RAISE INFO 'create_sql = %', create_sql;
 296         EXECUTE create_sql;
 297         insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
 298         --RAISE INFO 'insert_sql = %', insert_sql;
 299         EXECUTE insert_sql;
 300     END;
 301 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 302
 303 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
 304     DECLARE
 305         migration_schema ALIAS FOR $1;
 306         production_tables TEXT[];
 307     BEGIN
 308         --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
 309         SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
 310         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
 311             PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
 312         END LOOP;
 313     END;
 314 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 315
 316 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
 317     DECLARE
 318         migration_schema ALIAS FOR $1;
 319         production_table ALIAS FOR $2;
 320         base_staging_table TEXT;
 321         columns RECORD;
 322     BEGIN
 323         base_staging_table = REPLACE( production_table, '.', '_' );
 324         --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
 325         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
 326     END;
 327 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 328
 329 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
 330     DECLARE
 331         full_name TEXT := $1;
 332         before_comma TEXT;
 333         family_name TEXT := '';
 334         first_given_name TEXT := '';
 335         second_given_name TEXT := '';
 336         suffix TEXT := '';
 337         prefix TEXT := '';
 338     BEGIN
 339         before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
 340         suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
 341
 342         IF suffix = before_comma THEN
 343             suffix := '';
 344         END IF;
 345
 346         family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
 347         first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
 348         second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
 349
 350         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 351     END;
 352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 353
 354 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
 355     DECLARE
 356         full_name TEXT := $1;
 357         temp TEXT;
 358         family_name TEXT := '';
 359         first_given_name TEXT := '';
 360         second_given_name TEXT := '';
 361         suffix TEXT := '';
 362         prefix TEXT := '';
 363     BEGIN
 364         temp := full_name;
 365         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 366         IF temp ilike '%MR.%' THEN
 367             prefix := 'Mr.';
 368             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
 369         END IF;
 370         IF temp ilike '%MRS.%' THEN
 371             prefix := 'Mrs.';
 372             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
 373         END IF;
 374         IF temp ilike '%MS.%' THEN
 375             prefix := 'Ms.';
 376             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
 377         END IF;
 378         IF temp ilike '%DR.%' THEN
 379             prefix := 'Dr.';
 380             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
 381         END IF;
 382         IF temp ilike '%JR%' THEN
 383             suffix := 'Jr.';
 384             temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
 385         END IF;
 386         IF temp ilike '%JR,%' THEN
 387             suffix := 'Jr.';
 388             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
 389         END IF;
 390         IF temp ilike '%SR%' THEN
 391             suffix := 'Sr.';
 392             temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
 393         END IF;
 394         IF temp ilike '%SR,%' THEN
 395             suffix := 'Sr.';
 396             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
 397         END IF;
 398         IF temp ~ E'\\sII$' THEN
 399             suffix := 'II';
 400             temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
 401         END IF;
 402         IF temp ~ E'\\sIII$' THEN
 403             suffix := 'III';
 404             temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
 405         END IF;
 406         IF temp ~ E'\\sIV$' THEN
 407             suffix := 'IV';
 408             temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
 409         END IF;
 410
 411         family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
 412         first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
 413         second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE ''  END );
 414
 415         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 416     END;
 417 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 418
 419 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
 420     DECLARE
 421         full_name TEXT := $1;
 422         temp TEXT;
 423         family_name TEXT := '';
 424         first_given_name TEXT := '';
 425         second_given_name TEXT := '';
 426         suffix TEXT := '';
 427         prefix TEXT := '';
 428     BEGIN
 429         temp := full_name;
 430         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 431         IF temp ilike '%MR.%' THEN
 432             prefix := 'Mr.';
 433             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
 434         END IF;
 435         IF temp ilike '%MRS.%' THEN
 436             prefix := 'Mrs.';
 437             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
 438         END IF;
 439         IF temp ilike '%MS.%' THEN
 440             prefix := 'Ms.';
 441             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
 442         END IF;
 443         IF temp ilike '%DR.%' THEN
 444             prefix := 'Dr.';
 445             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
 446         END IF;
 447         IF temp ilike '%JR.%' THEN
 448             suffix := 'Jr.';
 449             temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
 450         END IF;
 451         IF temp ilike '%JR,%' THEN
 452             suffix := 'Jr.';
 453             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
 454         END IF;
 455         IF temp ilike '%SR.%' THEN
 456             suffix := 'Sr.';
 457             temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
 458         END IF;
 459         IF temp ilike '%SR,%' THEN
 460             suffix := 'Sr.';
 461             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
 462         END IF;
 463         IF temp like '%III%' THEN
 464             suffix := 'III';
 465             temp := REGEXP_REPLACE( temp, E'III', '' );
 466         END IF;
 467         IF temp like '%II%' THEN
 468             suffix := 'II';
 469             temp := REGEXP_REPLACE( temp, E'II', '' );
 470         END IF;
 471         IF temp like '%IV%' THEN
 472             suffix := 'IV';
 473             temp := REGEXP_REPLACE( temp, E'IV', '' );
 474         END IF;
 475
 476         temp := REGEXP_REPLACE( temp, '\(\)', '');
 477         family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
 478         family_name := REGEXP_REPLACE( family_name, ',', '' );
 479         first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
 480         first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
 481         second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
 482         second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
 483
 484         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 485     END;
 486 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 487
 488 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
 489     DECLARE
 490         full_name TEXT := $1;
 491         temp TEXT;
 492         family_name TEXT := '';
 493         first_given_name TEXT := '';
 494         second_given_name TEXT := '';
 495         suffix TEXT := '';
 496         prefix TEXT := '';
 497     BEGIN
 498         temp := BTRIM(full_name);
 499         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 500         --IF temp ~ '^\S{2,}\.' THEN
 501         --    prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
 502         --    temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
 503         --END IF;
 504         --IF temp ~ '\S{2,}\.$' THEN
 505         --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
 506         --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
 507         --END IF;
 508         IF temp ilike '%MR.%' THEN
 509             prefix := 'Mr.';
 510             temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
 511         END IF;
 512         IF temp ilike '%MRS.%' THEN
 513             prefix := 'Mrs.';
 514             temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
 515         END IF;
 516         IF temp ilike '%MS.%' THEN
 517             prefix := 'Ms.';
 518             temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
 519         END IF;
 520         IF temp ilike '%DR.%' THEN
 521             prefix := 'Dr.';
 522             temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
 523         END IF;
 524         IF temp ilike '%JR.%' THEN
 525             suffix := 'Jr.';
 526             temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
 527         END IF;
 528         IF temp ilike '%JR,%' THEN
 529             suffix := 'Jr.';
 530             temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
 531         END IF;
 532         IF temp ilike '%SR.%' THEN
 533             suffix := 'Sr.';
 534             temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
 535         END IF;
 536         IF temp ilike '%SR,%' THEN
 537             suffix := 'Sr.';
 538             temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
 539         END IF;
 540         IF temp like '%III%' THEN
 541             suffix := 'III';
 542             temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
 543         END IF;
 544         IF temp like '%II%' THEN
 545             suffix := 'II';
 546             temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
 547         END IF;
 548
 549         IF temp ~ ',' THEN
 550             family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
 551             temp := BTRIM(REPLACE( temp, family_name, '' ));
 552             family_name := REPLACE( family_name, ',', '' );
 553             IF temp ~ ' ' THEN
 554                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
 555                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
 556             ELSE
 557                 first_given_name := temp;
 558                 second_given_name := '';
 559             END IF;
 560         ELSE
 561             IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
 562                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
 563                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
 564                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
 565             ELSE
 566                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
 567                 second_given_name := temp;
 568                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
 569             END IF;
 570         END IF;
 571
 572         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 573     END;
 574 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 575
 576 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
 577     DECLARE
 578         full_name TEXT := $1;
 579         temp TEXT;
 580         family_name TEXT := '';
 581         first_given_name TEXT := '';
 582         second_given_name TEXT := '';
 583         suffix TEXT := '';
 584         prefix TEXT := '';
 585     BEGIN
 586         temp := BTRIM(full_name);
 587         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 588         --IF temp ~ '^\S{2,}\.' THEN
 589         --    prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
 590         --    temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
 591         --END IF;
 592         --IF temp ~ '\S{2,}\.$' THEN
 593         --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
 594         --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
 595         --END IF;
 596         IF temp ilike '%MR.%' THEN
 597             prefix := 'Mr.';
 598             temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
 599         END IF;
 600         IF temp ilike '%MRS.%' THEN
 601             prefix := 'Mrs.';
 602             temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
 603         END IF;
 604         IF temp ilike '%MS.%' THEN
 605             prefix := 'Ms.';
 606             temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
 607         END IF;
 608         IF temp ilike '%DR.%' THEN
 609             prefix := 'Dr.';
 610             temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
 611         END IF;
 612         IF temp ilike '%JR.%' THEN
 613             suffix := 'Jr.';
 614             temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
 615         END IF;
 616         IF temp ilike '%JR,%' THEN
 617             suffix := 'Jr.';
 618             temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
 619         END IF;
 620         IF temp ilike '%SR.%' THEN
 621             suffix := 'Sr.';
 622             temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
 623         END IF;
 624         IF temp ilike '%SR,%' THEN
 625             suffix := 'Sr.';
 626             temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
 627         END IF;
 628         IF temp like '%III%' THEN
 629             suffix := 'III';
 630             temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
 631         END IF;
 632         IF temp like '%II%' THEN
 633             suffix := 'II';
 634             temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
 635         END IF;
 636
 637         IF temp ~ ',' THEN
 638             family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
 639             temp := BTRIM(REPLACE( temp, family_name, '' ));
 640             family_name := REPLACE( family_name, ',', '' );
 641             IF temp ~ ' ' THEN
 642                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
 643                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
 644             ELSE
 645                 first_given_name := temp;
 646                 second_given_name := '';
 647             END IF;
 648         ELSE
 649             IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
 650                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
 651                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
 652                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
 653             ELSE
 654                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
 655                 second_given_name := temp;
 656                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
 657             END IF;
 658         END IF;
 659
 660         family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
 661         first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
 662         second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
 663
 664         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 665     END;
 666 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 667
 668 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
 669     DECLARE
 670         city_state_zip TEXT := $1;
 671         city TEXT := '';
 672         state TEXT := '';
 673         zip TEXT := '';
 674     BEGIN
 675         zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
 676         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 677         IF city_state_zip ~ ',' THEN
 678             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
 679             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
 680         ELSE
 681             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
 682                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
 683                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
 684             ELSE
 685                 IF city_state_zip ~ E'^\\S+$'  THEN
 686                     city := city_state_zip;
 687                     state := 'N/A';
 688                 ELSE
 689                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 690                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 691                 END IF;
 692             END IF;
 693         END IF;
 694         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
 695     END;
 696 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 697
 698 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
 699 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
 700     DECLARE
 701         fullstring TEXT := $1;
 702         address1 TEXT := '';
 703         address2 TEXT := '';
 704         scratch1 TEXT := '';
 705         scratch2 TEXT := '';
 706         city TEXT := '';
 707         state TEXT := '';
 708         zip TEXT := '';
 709     BEGIN
 710         zip := CASE
 711             WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
 712             THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
 713             ELSE ''
 714         END;
 715         fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 716
 717         IF fullstring ~ ',' THEN
 718             state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
 719             scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
 720         ELSE
 721             IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
 722                 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
 723                 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
 724             ELSE
 725                 IF fullstring ~ E'^\\S+$'  THEN
 726                     scratch1 := fullstring;
 727                     state := 'N/A';
 728                 ELSE
 729                     state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 730                     scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 731                 END IF;
 732             END IF;
 733         END IF;
 734
 735         IF scratch1 ~ '[\$]' THEN
 736             scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
 737             city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
 738         ELSE
 739             IF scratch1 ~ '\s' THEN
 740                 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
 741                 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
 742             ELSE
 743                 scratch2 := 'N/A';
 744                 city := scratch1;
 745             END IF;
 746         END IF;
 747
 748         IF scratch2 ~ '^\d' THEN
 749             address1 := scratch2;
 750             address2 := '';
 751         ELSE
 752             address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
 753             address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
 754         END IF;
 755
 756         RETURN ARRAY[
 757              TRIM(BOTH ' ' FROM address1)
 758             ,TRIM(BOTH ' ' FROM address2)
 759             ,TRIM(BOTH ' ' FROM city)
 760             ,TRIM(BOTH ' ' FROM state)
 761             ,TRIM(BOTH ' ' FROM zip)
 762         ];
 763     END;
 764 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 765
 766 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
 767     my ($address) = @_;
 768
 769     use Geo::StreetAddress::US;
 770
 771     my $a = Geo::StreetAddress::US->parse_location($address);
 772
 773     return [
 774          "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
 775         ,"$a->{sec_unit_type} $a->{sec_unit_num}"
 776         ,$a->{city}
 777         ,$a->{state}
 778         ,$a->{zip}
 779     ];
 780 $$ LANGUAGE PLPERLU STABLE;
 781
 782 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
 783 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
 784 INSERT INTO migration_tools.usps_suffixes VALUES
 785     ('ALLEE','ALY'),
 786     ('ALLEY','ALY'),
 787     ('ALLY','ALY'),
 788     ('ALY','ALY'),
 789     ('ANEX','ANX'),
 790     ('ANNEX','ANX'),
 791     ('ANNX','ANX'),
 792     ('ANX','ANX'),
 793     ('ARCADE','ARC'),
 794     ('ARC','ARC'),
 795     ('AV','AVE'),
 796     ('AVE','AVE'),
 797     ('AVEN','AVE'),
 798     ('AVENU','AVE'),
 799     ('AVENUE','AVE'),
 800     ('AVN','AVE'),
 801     ('AVNUE','AVE'),
 802     ('BAYOO','BYU'),
 803     ('BAYOU','BYU'),
 804     ('BCH','BCH'),
 805     ('BEACH','BCH'),
 806     ('BEND','BND'),
 807     ('BLF','BLF'),
 808     ('BLUF','BLF'),
 809     ('BLUFF','BLF'),
 810     ('BLUFFS','BLFS'),
 811     ('BLVD','BLVD'),
 812     ('BND','BND'),
 813     ('BOT','BTM'),
 814     ('BOTTM','BTM'),
 815     ('BOTTOM','BTM'),
 816     ('BOUL','BLVD'),
 817     ('BOULEVARD','BLVD'),
 818     ('BOULV','BLVD'),
 819     ('BRANCH','BR'),
 820     ('BR','BR'),
 821     ('BRDGE','BRG'),
 822     ('BRG','BRG'),
 823     ('BRIDGE','BRG'),
 824     ('BRK','BRK'),
 825     ('BRNCH','BR'),
 826     ('BROOK','BRK'),
 827     ('BROOKS','BRKS'),
 828     ('BTM','BTM'),
 829     ('BURG','BG'),
 830     ('BURGS','BGS'),
 831     ('BYPA','BYP'),
 832     ('BYPAS','BYP'),
 833     ('BYPASS','BYP'),
 834     ('BYP','BYP'),
 835     ('BYPS','BYP'),
 836     ('CAMP','CP'),
 837     ('CANYN','CYN'),
 838     ('CANYON','CYN'),
 839     ('CAPE','CPE'),
 840     ('CAUSEWAY','CSWY'),
 841     ('CAUSWAY','CSWY'),
 842     ('CEN','CTR'),
 843     ('CENT','CTR'),
 844     ('CENTER','CTR'),
 845     ('CENTERS','CTRS'),
 846     ('CENTR','CTR'),
 847     ('CENTRE','CTR'),
 848     ('CIRC','CIR'),
 849     ('CIR','CIR'),
 850     ('CIRCL','CIR'),
 851     ('CIRCLE','CIR'),
 852     ('CIRCLES','CIRS'),
 853     ('CK','CRK'),
 854     ('CLB','CLB'),
 855     ('CLF','CLF'),
 856     ('CLFS','CLFS'),
 857     ('CLIFF','CLF'),
 858     ('CLIFFS','CLFS'),
 859     ('CLUB','CLB'),
 860     ('CMP','CP'),
 861     ('CNTER','CTR'),
 862     ('CNTR','CTR'),
 863     ('CNYN','CYN'),
 864     ('COMMON','CMN'),
 865     ('COR','COR'),
 866     ('CORNER','COR'),
 867     ('CORNERS','CORS'),
 868     ('CORS','CORS'),
 869     ('COURSE','CRSE'),
 870     ('COURT','CT'),
 871     ('COURTS','CTS'),
 872     ('COVE','CV'),
 873     ('COVES','CVS'),
 874     ('CP','CP'),
 875     ('CPE','CPE'),
 876     ('CRCL','CIR'),
 877     ('CRCLE','CIR'),
 878     ('CR','CRK'),
 879     ('CRECENT','CRES'),
 880     ('CREEK','CRK'),
 881     ('CRESCENT','CRES'),
 882     ('CRES','CRES'),
 883     ('CRESENT','CRES'),
 884     ('CREST','CRST'),
 885     ('CRK','CRK'),
 886     ('CROSSING','XING'),
 887     ('CROSSROAD','XRD'),
 888     ('CRSCNT','CRES'),
 889     ('CRSE','CRSE'),
 890     ('CRSENT','CRES'),
 891     ('CRSNT','CRES'),
 892     ('CRSSING','XING'),
 893     ('CRSSNG','XING'),
 894     ('CRT','CT'),
 895     ('CSWY','CSWY'),
 896     ('CT','CT'),
 897     ('CTR','CTR'),
 898     ('CTS','CTS'),
 899     ('CURVE','CURV'),
 900     ('CV','CV'),
 901     ('CYN','CYN'),
 902     ('DALE','DL'),
 903     ('DAM','DM'),
 904     ('DIV','DV'),
 905     ('DIVIDE','DV'),
 906     ('DL','DL'),
 907     ('DM','DM'),
 908     ('DR','DR'),
 909     ('DRIV','DR'),
 910     ('DRIVE','DR'),
 911     ('DRIVES','DRS'),
 912     ('DRV','DR'),
 913     ('DVD','DV'),
 914     ('DV','DV'),
 915     ('ESTATE','EST'),
 916     ('ESTATES','ESTS'),
 917     ('EST','EST'),
 918     ('ESTS','ESTS'),
 919     ('EXP','EXPY'),
 920     ('EXPRESS','EXPY'),
 921     ('EXPRESSWAY','EXPY'),
 922     ('EXPR','EXPY'),
 923     ('EXPW','EXPY'),
 924     ('EXPY','EXPY'),
 925     ('EXTENSION','EXT'),
 926     ('EXTENSIONS','EXTS'),
 927     ('EXT','EXT'),
 928     ('EXTN','EXT'),
 929     ('EXTNSN','EXT'),
 930     ('EXTS','EXTS'),
 931     ('FALL','FALL'),
 932     ('FALLS','FLS'),
 933     ('FERRY','FRY'),
 934     ('FIELD','FLD'),
 935     ('FIELDS','FLDS'),
 936     ('FLAT','FLT'),
 937     ('FLATS','FLTS'),
 938     ('FLD','FLD'),
 939     ('FLDS','FLDS'),
 940     ('FLS','FLS'),
 941     ('FLT','FLT'),
 942     ('FLTS','FLTS'),
 943     ('FORD','FRD'),
 944     ('FORDS','FRDS'),
 945     ('FOREST','FRST'),
 946     ('FORESTS','FRST'),
 947     ('FORGE','FRG'),
 948     ('FORGES','FRGS'),
 949     ('FORG','FRG'),
 950     ('FORK','FRK'),
 951     ('FORKS','FRKS'),
 952     ('FORT','FT'),
 953     ('FRD','FRD'),
 954     ('FREEWAY','FWY'),
 955     ('FREEWY','FWY'),
 956     ('FRG','FRG'),
 957     ('FRK','FRK'),
 958     ('FRKS','FRKS'),
 959     ('FRRY','FRY'),
 960     ('FRST','FRST'),
 961     ('FRT','FT'),
 962     ('FRWAY','FWY'),
 963     ('FRWY','FWY'),
 964     ('FRY','FRY'),
 965     ('FT','FT'),
 966     ('FWY','FWY'),
 967     ('GARDEN','GDN'),
 968     ('GARDENS','GDNS'),
 969     ('GARDN','GDN'),
 970     ('GATEWAY','GTWY'),
 971     ('GATEWY','GTWY'),
 972     ('GATWAY','GTWY'),
 973     ('GDN','GDN'),
 974     ('GDNS','GDNS'),
 975     ('GLEN','GLN'),
 976     ('GLENS','GLNS'),
 977     ('GLN','GLN'),
 978     ('GRDEN','GDN'),
 979     ('GRDN','GDN'),
 980     ('GRDNS','GDNS'),
 981     ('GREEN','GRN'),
 982     ('GREENS','GRNS'),
 983     ('GRN','GRN'),
 984     ('GROVE','GRV'),
 985     ('GROVES','GRVS'),
 986     ('GROV','GRV'),
 987     ('GRV','GRV'),
 988     ('GTWAY','GTWY'),
 989     ('GTWY','GTWY'),
 990     ('HARB','HBR'),
 991     ('HARBOR','HBR'),
 992     ('HARBORS','HBRS'),
 993     ('HARBR','HBR'),
 994     ('HAVEN','HVN'),
 995     ('HAVN','HVN'),
 996     ('HBR','HBR'),
 997     ('HEIGHT','HTS'),
 998     ('HEIGHTS','HTS'),
 999     ('HGTS','HTS'),
1000     ('HIGHWAY','HWY'),
1001     ('HIGHWY','HWY'),
1002     ('HILL','HL'),
1003     ('HILLS','HLS'),
1004     ('HIWAY','HWY'),
1005     ('HIWY','HWY'),
1006     ('HL','HL'),
1007     ('HLLW','HOLW'),
1008     ('HLS','HLS'),
1009     ('HOLLOW','HOLW'),
1010     ('HOLLOWS','HOLW'),
1011     ('HOLW','HOLW'),
1012     ('HOLWS','HOLW'),
1013     ('HRBOR','HBR'),
1014     ('HT','HTS'),
1015     ('HTS','HTS'),
1016     ('HVN','HVN'),
1017     ('HWAY','HWY'),
1018     ('HWY','HWY'),
1019     ('INLET','INLT'),
1020     ('INLT','INLT'),
1021     ('IS','IS'),
1022     ('ISLAND','IS'),
1023     ('ISLANDS','ISS'),
1024     ('ISLANDS','SLNDS'),
1025     ('ISLANDS','SS'),
1026     ('ISLE','ISLE'),
1027     ('ISLES','ISLE'),
1028     ('ISLND','IS'),
1029     ('I','SLNDS'),
1030     ('ISS','ISS'),
1031     ('JCTION','JCT'),
1032     ('JCT','JCT'),
1033     ('JCTN','JCT'),
1034     ('JCTNS','JCTS'),
1035     ('JCTS','JCTS'),
1036     ('JUNCTION','JCT'),
1037     ('JUNCTIONS','JCTS'),
1038     ('JUNCTN','JCT'),
1039     ('JUNCTON','JCT'),
1040     ('KEY','KY'),
1041     ('KEYS','KYS'),
1042     ('KNL','KNL'),
1043     ('KNLS','KNLS'),
1044     ('KNOL','KNL'),
1045     ('KNOLL','KNL'),
1046     ('KNOLLS','KNLS'),
1047     ('KY','KY'),
1048     ('KYS','KYS'),
1049     ('LAKE','LK'),
1050     ('LAKES','LKS'),
1051     ('LA','LN'),
1052     ('LANDING','LNDG'),
1053     ('LAND','LAND'),
1054     ('LANE','LN'),
1055     ('LANES','LN'),
1056     ('LCK','LCK'),
1057     ('LCKS','LCKS'),
1058     ('LDGE','LDG'),
1059     ('LDG','LDG'),
1060     ('LF','LF'),
1061     ('LGT','LGT'),
1062     ('LIGHT','LGT'),
1063     ('LIGHTS','LGTS'),
1064     ('LK','LK'),
1065     ('LKS','LKS'),
1066     ('LNDG','LNDG'),
1067     ('LNDNG','LNDG'),
1068     ('LN','LN'),
1069     ('LOAF','LF'),
1070     ('LOCK','LCK'),
1071     ('LOCKS','LCKS'),
1072     ('LODGE','LDG'),
1073     ('LODG','LDG'),
1074     ('LOOP','LOOP'),
1075     ('LOOPS','LOOP'),
1076     ('MALL','MALL'),
1077     ('MANOR','MNR'),
1078     ('MANORS','MNRS'),
1079     ('MDW','MDW'),
1080     ('MDWS','MDWS'),
1081     ('MEADOW','MDW'),
1082     ('MEADOWS','MDWS'),
1083     ('MEDOWS','MDWS'),
1084     ('MEWS','MEWS'),
1085     ('MILL','ML'),
1086     ('MILLS','MLS'),
1087     ('MISSION','MSN'),
1088     ('MISSN','MSN'),
1089     ('ML','ML'),
1090     ('MLS','MLS'),
1091     ('MNR','MNR'),
1092     ('MNRS','MNRS'),
1093     ('MNTAIN','MTN'),
1094     ('MNT','MT'),
1095     ('MNTN','MTN'),
1096     ('MNTNS','MTNS'),
1097     ('MOTORWAY','MTWY'),
1098     ('MOUNTAIN','MTN'),
1099     ('MOUNTAINS','MTNS'),
1100     ('MOUNTIN','MTN'),
1101     ('MOUNT','MT'),
1102     ('MSN','MSN'),
1103     ('MSSN','MSN'),
1104     ('MTIN','MTN'),
1105     ('MT','MT'),
1106     ('MTN','MTN'),
1107     ('NCK','NCK'),
1108     ('NECK','NCK'),
1109     ('ORCHARD','ORCH'),
1110     ('ORCH','ORCH'),
1111     ('ORCHRD','ORCH'),
1112     ('OVAL','OVAL'),
1113     ('OVERPASS','OPAS'),
1114     ('OVL','OVAL'),
1115     ('PARK','PARK'),
1116     ('PARKS','PARK'),
1117     ('PARKWAY','PKWY'),
1118     ('PARKWAYS','PKWY'),
1119     ('PARKWY','PKWY'),
1120     ('PASSAGE','PSGE'),
1121     ('PASS','PASS'),
1122     ('PATH','PATH'),
1123     ('PATHS','PATH'),
1124     ('PIKE','PIKE'),
1125     ('PIKES','PIKE'),
1126     ('PINE','PNE'),
1127     ('PINES','PNES'),
1128     ('PK','PARK'),
1129     ('PKWAY','PKWY'),
1130     ('PKWY','PKWY'),
1131     ('PKWYS','PKWY'),
1132     ('PKY','PKWY'),
1133     ('PLACE','PL'),
1134     ('PLAINES','PLNS'),
1135     ('PLAIN','PLN'),
1136     ('PLAINS','PLNS'),
1137     ('PLAZA','PLZ'),
1138     ('PLN','PLN'),
1139     ('PLNS','PLNS'),
1140     ('PL','PL'),
1141     ('PLZA','PLZ'),
1142     ('PLZ','PLZ'),
1143     ('PNES','PNES'),
1144     ('POINT','PT'),
1145     ('POINTS','PTS'),
1146     ('PORT','PRT'),
1147     ('PORTS','PRTS'),
1148     ('PRAIRIE','PR'),
1149     ('PRARIE','PR'),
1150     ('PRK','PARK'),
1151     ('PR','PR'),
1152     ('PRR','PR'),
1153     ('PRT','PRT'),
1154     ('PRTS','PRTS'),
1155     ('PT','PT'),
1156     ('PTS','PTS'),
1157     ('RADIAL','RADL'),
1158     ('RADIEL','RADL'),
1159     ('RADL','RADL'),
1160     ('RAD','RADL'),
1161     ('RAMP','RAMP'),
1162     ('RANCHES','RNCH'),
1163     ('RANCH','RNCH'),
1164     ('RAPID','RPD'),
1165     ('RAPIDS','RPDS'),
1166     ('RDGE','RDG'),
1167     ('RDG','RDG'),
1168     ('RDGS','RDGS'),
1169     ('RD','RD'),
1170     ('RDS','RDS'),
1171     ('REST','RST'),
1172     ('RIDGE','RDG'),
1173     ('RIDGES','RDGS'),
1174     ('RIVER','RIV'),
1175     ('RIV','RIV'),
1176     ('RIVR','RIV'),
1177     ('RNCH','RNCH'),
1178     ('RNCHS','RNCH'),
1179     ('ROAD','RD'),
1180     ('ROADS','RDS'),
1181     ('ROUTE','RTE'),
1182     ('ROW','ROW'),
1183     ('RPD','RPD'),
1184     ('RPDS','RPDS'),
1185     ('RST','RST'),
1186     ('RUE','RUE'),
1187     ('RUN','RUN'),
1188     ('RVR','RIV'),
1189     ('SHL','SHL'),
1190     ('SHLS','SHLS'),
1191     ('SHOAL','SHL'),
1192     ('SHOALS','SHLS'),
1193     ('SHOAR','SHR'),
1194     ('SHOARS','SHRS'),
1195     ('SHORE','SHR'),
1196     ('SHORES','SHRS'),
1197     ('SHR','SHR'),
1198     ('SHRS','SHRS'),
1199     ('SKYWAY','SKWY'),
1200     ('SMT','SMT'),
1201     ('SPG','SPG'),
1202     ('SPGS','SPGS'),
1203     ('SPNG','SPG'),
1204     ('SPNGS','SPGS'),
1205     ('SPRING','SPG'),
1206     ('SPRINGS','SPGS'),
1207     ('SPRNG','SPG'),
1208     ('SPRNGS','SPGS'),
1209     ('SPUR','SPUR'),
1210     ('SPURS','SPUR'),
1211     ('SQRE','SQ'),
1212     ('SQR','SQ'),
1213     ('SQRS','SQS'),
1214     ('SQ','SQ'),
1215     ('SQUARE','SQ'),
1216     ('SQUARES','SQS'),
1217     ('SQU','SQ'),
1218     ('STA','STA'),
1219     ('STATION','STA'),
1220     ('STATN','STA'),
1221     ('STN','STA'),
1222     ('STRA','STRA'),
1223     ('STRAVEN','STRA'),
1224     ('STRAVENUE','STRA'),
1225     ('STRAVE','STRA'),
1226     ('STRAVN','STRA'),
1227     ('STRAV','STRA'),
1228     ('STREAM','STRM'),
1229     ('STREETS','STS'),
1230     ('STREET','ST'),
1231     ('STREME','STRM'),
1232     ('STRM','STRM'),
1233     ('STR','ST'),
1234     ('STRT','ST'),
1235     ('STRVN','STRA'),
1236     ('STRVNUE','STRA'),
1237     ('ST','ST'),
1238     ('SUMIT','SMT'),
1239     ('SUMITT','SMT'),
1240     ('SUMMIT','SMT'),
1241     ('TERRACE','TER'),
1242     ('TERR','TER'),
1243     ('TER','TER'),
1244     ('THROUGHWAY','TRWY'),
1245     ('TPKE','TPKE'),
1246     ('TPK','TPKE'),
1247     ('TRACES','TRCE'),
1248     ('TRACE','TRCE'),
1249     ('TRACKS','TRAK'),
1250     ('TRACK','TRAK'),
1251     ('TRAFFICWAY','TRFY'),
1252     ('TRAILS','TRL'),
1253     ('TRAIL','TRL'),
1254     ('TRAK','TRAK'),
1255     ('TRCE','TRCE'),
1256     ('TRFY','TRFY'),
1257     ('TRKS','TRAK'),
1258     ('TRK','TRAK'),
1259     ('TRLS','TRL'),
1260     ('TRL','TRL'),
1261     ('TRNPK','TPKE'),
1262     ('TRPK','TPKE'),
1263     ('TR','TRL'),
1264     ('TUNEL','TUNL'),
1265     ('TUNLS','TUNL'),
1266     ('TUNL','TUNL'),
1267     ('TUNNELS','TUNL'),
1268     ('TUNNEL','TUNL'),
1269     ('TUNNL','TUNL'),
1270     ('TURNPIKE','TPKE'),
1271     ('TURNPK','TPKE'),
1272     ('UNDERPASS','UPAS'),
1273     ('UNIONS','UNS'),
1274     ('UNION','UN'),
1275     ('UN','UN'),
1276     ('VALLEYS','VLYS'),
1277     ('VALLEY','VLY'),
1278     ('VALLY','VLY'),
1279     ('VDCT','IA'),
1280     ('VIADCT','VIA'),
1281     ('VIADUCT','IA'),
1282     ('VIADUCT','VIA'),
1283     ('VIA','VIA'),
1284     ('VIEWS','VWS'),
1285     ('VIEW','VW'),
1286     ('VILLAGES','VLGS'),
1287     ('VILLAGE','VLG'),
1288     ('VILLAG','VLG'),
1289     ('VILLE','VL'),
1290     ('VILLG','VLG'),
1291     ('VILLIAGE','VLG'),
1292     ('VILL','VLG'),
1293     ('VISTA','VIS'),
1294     ('VIST','VIS'),
1295     ('VIS','VIS'),
1296     ('VLGS','VLGS'),
1297     ('VLG','VLG'),
1298     ('VLLY','VLY'),
1299     ('VL','VL'),
1300     ('VLYS','VLYS'),
1301     ('VLY','VLY'),
1302     ('VSTA','VIS'),
1303     ('VST','VIS'),
1304     ('VWS','VWS'),
1305     ('VW','VW'),
1306     ('WALKS','WALK'),
1307     ('WALK','WALK'),
1308     ('WALL','WALL'),
1309     ('WAYS','WAYS'),
1310     ('WAY','WAY'),
1311     ('WELLS','WLS'),
1312     ('WELL','WL'),
1313     ('WLS','WLS'),
1314     ('WY','WAY'),
1315     ('XING','XING');
1316
1317 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
1318 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1319     DECLARE
1320         suffix TEXT := $1;
1321                 _r RECORD;
1322     BEGIN
1323         --RAISE INFO 'suffix = %', suffix;
1324                 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1325                         suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
1326                 END LOOP;
1327                 RETURN suffix;
1328     END;
1329 $$ LANGUAGE PLPGSQL STRICT STABLE;
1330
1331 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1332     BEGIN
1333                 RETURN CASE
1334             WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
1335             ELSE $1
1336         END;
1337     END;
1338 $$ LANGUAGE PLPGSQL STRICT STABLE;
1339
1340 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1341     DECLARE
1342         n TEXT := o;
1343     BEGIN
1344         IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807.  May also want to consider the case where folks want to add prefixes to non-numeric barcodes
1345             IF o::BIGINT < t THEN
1346                 n = o::BIGINT + t;
1347             END IF;
1348         END IF;
1349
1350         RETURN n;
1351     END;
1352 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1353
1354 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1355     DECLARE
1356         migration_schema ALIAS FOR $1;
1357         output TEXT;
1358     BEGIN
1359         FOR output IN
1360             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1361         LOOP
1362             RETURN output;
1363         END LOOP;
1364     END;
1365 $$ LANGUAGE PLPGSQL STRICT STABLE;
1366
1367 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1368     DECLARE
1369         migration_schema ALIAS FOR $1;
1370         output TEXT;
1371     BEGIN
1372         FOR output IN
1373             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1374         LOOP
1375             RETURN output;
1376         END LOOP;
1377     END;
1378 $$ LANGUAGE PLPGSQL STRICT STABLE;
1379
1380 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1381     DECLARE
1382         migration_schema ALIAS FOR $1;
1383         output TEXT;
1384     BEGIN
1385         FOR output IN
1386             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1387         LOOP
1388             RETURN output;
1389         END LOOP;
1390     END;
1391 $$ LANGUAGE PLPGSQL STRICT STABLE;
1392
1393 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1394     DECLARE
1395         migration_schema ALIAS FOR $1;
1396         output TEXT;
1397     BEGIN
1398         FOR output IN
1399             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1400         LOOP
1401             RETURN output;
1402         END LOOP;
1403     END;
1404 $$ LANGUAGE PLPGSQL STRICT STABLE;
1405
1406 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1407     DECLARE
1408         migration_schema ALIAS FOR $1;
1409         profile_map TEXT;
1410         patron_table ALIAS FOR $2;
1411         default_patron_profile ALIAS FOR $3;
1412         sql TEXT;
1413         sql_update TEXT;
1414         sql_where1 TEXT := '';
1415         sql_where2 TEXT := '';
1416         sql_where3 TEXT := '';
1417         output RECORD;
1418     BEGIN
1419         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1420         FOR output IN
1421             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1422         LOOP
1423             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1424             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1425             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1426             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1427             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1428             --RAISE INFO 'sql = %', sql;
1429             PERFORM migration_tools.exec( $1, sql );
1430         END LOOP;
1431         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
1432         BEGIN
1433             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1434         EXCEPTION
1435             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1436         END;
1437     END;
1438 $$ LANGUAGE PLPGSQL STRICT STABLE;
1439
1440 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1441     DECLARE
1442         migration_schema ALIAS FOR $1;
1443         field_map TEXT;
1444         item_table ALIAS FOR $2;
1445         sql TEXT;
1446         sql_update TEXT;
1447         sql_where1 TEXT := '';
1448         sql_where2 TEXT := '';
1449         sql_where3 TEXT := '';
1450         output RECORD;
1451     BEGIN
1452         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1453         FOR output IN
1454             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1455         LOOP
1456             sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
1457             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1458             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1459             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1460             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1461             --RAISE INFO 'sql = %', sql;
1462             PERFORM migration_tools.exec( $1, sql );
1463         END LOOP;
1464         BEGIN
1465             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1466         EXCEPTION
1467             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1468         END;
1469     END;
1470 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1471
1472 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1473     DECLARE
1474         migration_schema ALIAS FOR $1;
1475         base_copy_location_map TEXT;
1476         item_table ALIAS FOR $2;
1477         sql TEXT;
1478         sql_update TEXT;
1479         sql_where1 TEXT := '';
1480         sql_where2 TEXT := '';
1481         sql_where3 TEXT := '';
1482         output RECORD;
1483     BEGIN
1484         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1485         FOR output IN
1486             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1487         LOOP
1488             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1489             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
1490             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
1491             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
1492             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
1493             --RAISE INFO 'sql = %', sql;
1494             PERFORM migration_tools.exec( $1, sql );
1495         END LOOP;
1496         BEGIN
1497             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1498         EXCEPTION
1499             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1500         END;
1501     END;
1502 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1503
1504 -- circulate       loan period     max renewals    max out fine amount     fine interval   max fine        item field 1    item value 1    item field 2    item value 2    patron field 1  patron value 1  patron field 2  patron value 2
1505 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1506     DECLARE
1507         migration_schema ALIAS FOR $1;
1508         field_map TEXT;
1509         circ_table ALIAS FOR $2;
1510         item_table ALIAS FOR $3;
1511         patron_table ALIAS FOR $4;
1512         sql TEXT;
1513         sql_update TEXT;
1514         sql_where1 TEXT := '';
1515         sql_where2 TEXT := '';
1516         sql_where3 TEXT := '';
1517         sql_where4 TEXT := '';
1518         output RECORD;
1519     BEGIN
1520         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1521         FOR output IN
1522             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1523         LOOP
1524             sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
1525             sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
1526             sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
1527             sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
1528             sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
1529             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
1530             --RAISE INFO 'sql = %', sql;
1531             PERFORM migration_tools.exec( $1, sql );
1532         END LOOP;
1533         BEGIN
1534             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1535         EXCEPTION
1536             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1537         END;
1538     END;
1539 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1540
1541 -- expand_barcode
1542 --   $barcode      source barcode
1543 --   $prefix       prefix to add to barcode, NULL = add no prefix
1544 --   $maxlen       maximum length of barcode; default to 14 if left NULL
1545 --   $pad          padding string to apply to left of source barcode before adding
1546 --                 prefix and suffix; set to NULL or '' if no padding is desired
1547 --   $suffix       suffix to add to barcode, NULL = add no suffix
1548 --
1549 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1550 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1551 --
1552 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1553     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1554
1555     # default case
1556     return unless defined $barcode;
1557
1558     $prefix     = '' unless defined $prefix;
1559     $maxlen ||= 14;
1560     $pad        = '0' unless defined $pad;
1561     $suffix     = '' unless defined $suffix;
1562
1563     # bail out if adding prefix and suffix would bring new barcode over max length
1564     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1565
1566     my $new_barcode = $barcode;
1567     if ($pad ne '') {
1568         my $pad_length = $maxlen - length($prefix) - length($suffix);
1569         if (length($barcode) < $pad_length) {
1570             # assuming we always want padding on the left
1571             # also assuming that it is possible to have the pad string be longer than 1 character
1572             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1573         }
1574     }
1575
1576     # bail out if adding prefix and suffix would bring new barcode over max length
1577     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1578
1579     return "$prefix$new_barcode$suffix";
1580 $$ LANGUAGE PLPERLU STABLE;
1581
1582 -- remove previous version of this function
1583 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1584
1585 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1586     DECLARE
1587         attempt_value ALIAS FOR $1;
1588         datatype ALIAS FOR $2;
1589     BEGIN
1590         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1591         RETURN attempt_value;
1592     EXCEPTION
1593         WHEN OTHERS THEN RETURN NULL;
1594     END;
1595 $$ LANGUAGE PLPGSQL STRICT STABLE;
1596
1597 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1598     DECLARE
1599         attempt_value ALIAS FOR $1;
1600         fail_value ALIAS FOR $2;
1601         output DATE;
1602     BEGIN
1603         FOR output IN
1604             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1605         LOOP
1606             RETURN output;
1607         END LOOP;
1608     EXCEPTION
1609         WHEN OTHERS THEN
1610             FOR output IN
1611                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1612             LOOP
1613                 RETURN output;
1614             END LOOP;
1615     END;
1616 $$ LANGUAGE PLPGSQL STRICT STABLE;
1617
1618 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1619     DECLARE
1620         attempt_value ALIAS FOR $1;
1621         fail_value ALIAS FOR $2;
1622         output TIMESTAMPTZ;
1623     BEGIN
1624         FOR output IN
1625             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1626         LOOP
1627             RETURN output;
1628         END LOOP;
1629     EXCEPTION
1630         WHEN OTHERS THEN
1631             FOR output IN
1632                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1633             LOOP
1634                 RETURN output;
1635             END LOOP;
1636     END;
1637 $$ LANGUAGE PLPGSQL STRICT STABLE;
1638
1639 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1640     DECLARE
1641         attempt_value ALIAS FOR $1;
1642         fail_value ALIAS FOR $2;
1643         output DATE;
1644     BEGIN
1645         FOR output IN
1646             EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1647         LOOP
1648             RETURN output;
1649         END LOOP;
1650     EXCEPTION
1651         WHEN OTHERS THEN
1652             FOR output IN
1653                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1654             LOOP
1655                 RETURN output;
1656             END LOOP;
1657     END;
1658 $$ LANGUAGE PLPGSQL STRICT STABLE;
1659
1660 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1661     DECLARE
1662         attempt_value ALIAS FOR $1;
1663         fail_value ALIAS FOR $2;
1664         output TIMESTAMP;
1665     BEGIN
1666             output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1667             RETURN output;
1668     EXCEPTION
1669         WHEN OTHERS THEN
1670             FOR output IN
1671                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1672             LOOP
1673                 RETURN output;
1674             END LOOP;
1675     END;
1676 $$ LANGUAGE PLPGSQL STRICT STABLE;
1677
1678 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1679     DECLARE
1680         attempt_value ALIAS FOR $1;
1681         fail_value ALIAS FOR $2;
1682         output NUMERIC(8,2);
1683     BEGIN
1684         FOR output IN
1685             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1686         LOOP
1687             RETURN output;
1688         END LOOP;
1689     EXCEPTION
1690         WHEN OTHERS THEN
1691             FOR output IN
1692                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1693             LOOP
1694                 RETURN output;
1695             END LOOP;
1696     END;
1697 $$ LANGUAGE PLPGSQL STRICT STABLE;
1698
1699 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1700     DECLARE
1701         attempt_value ALIAS FOR $1;
1702         fail_value ALIAS FOR $2;
1703         output NUMERIC(6,2);
1704     BEGIN
1705         FOR output IN
1706             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1707         LOOP
1708             RETURN output;
1709         END LOOP;
1710     EXCEPTION
1711         WHEN OTHERS THEN
1712             FOR output IN
1713                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1714             LOOP
1715                 RETURN output;
1716             END LOOP;
1717     END;
1718 $$ LANGUAGE PLPGSQL STRICT STABLE;
1719
1720 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1721     DECLARE
1722         attempt_value ALIAS FOR $1;
1723         fail_value ALIAS FOR $2;
1724         output NUMERIC(8,2);
1725     BEGIN
1726         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1727             RAISE EXCEPTION 'too many digits';
1728         END IF;
1729         FOR output IN
1730             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;'
1731         LOOP
1732             RETURN output;
1733         END LOOP;
1734     EXCEPTION
1735         WHEN OTHERS THEN
1736             FOR output IN
1737                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1738             LOOP
1739                 RETURN output;
1740             END LOOP;
1741     END;
1742 $$ LANGUAGE PLPGSQL STRICT STABLE;
1743
1744 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1745     DECLARE
1746         attempt_value ALIAS FOR $1;
1747         fail_value ALIAS FOR $2;
1748         output NUMERIC(6,2);
1749     BEGIN
1750         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1751             RAISE EXCEPTION 'too many digits';
1752         END IF;
1753         FOR output IN
1754             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;'
1755         LOOP
1756             RETURN output;
1757         END LOOP;
1758     EXCEPTION
1759         WHEN OTHERS THEN
1760             FOR output IN
1761                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1762             LOOP
1763                 RETURN output;
1764             END LOOP;
1765     END;
1766 $$ LANGUAGE PLPGSQL STRICT STABLE;
1767
1768 -- add_codabar_checkdigit
1769 --   $barcode      source barcode
1770 --
1771 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1772 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1773 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
1774 -- input string does not meet those requirements, it is returned unchanged.
1775 --
1776 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1777     my $barcode = shift;
1778
1779     return $barcode if $barcode !~ /^\d{13,14}$/;
1780     $barcode = substr($barcode, 0, 13); # ignore 14th digit
1781     my @digits = split //, $barcode;
1782     my $total = 0;
1783     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1784     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1785     my $remainder = $total % 10;
1786     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1787     return $barcode . $checkdigit;
1788 $$ LANGUAGE PLPERLU STRICT STABLE;
1789
1790 -- add_code39mod43_checkdigit
1791 --   $barcode      source barcode
1792 --
1793 -- If the source string is 13 or 14 characters long and contains only valid
1794 -- Code 39 mod 43 characters, adds or replaces the 14th
1795 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1796 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
1797 -- input string does not meet those requirements, it is returned unchanged.
1798 --
1799 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1800     my $barcode = shift;
1801
1802     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1803     $barcode = substr($barcode, 0, 13); # ignore 14th character
1804
1805     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1806     my %nums = map { $valid_chars[$_] => $_ } (0..42);
1807
1808     my $total = 0;
1809     $total += $nums{$_} foreach split(//, $barcode);
1810     my $remainder = $total % 43;
1811     my $checkdigit = $valid_chars[$remainder];
1812     return $barcode . $checkdigit;
1813 $$ LANGUAGE PLPERLU STRICT STABLE;
1814
1815 -- add_mod16_checkdigit
1816 --   $barcode      source barcode
1817 --
1818 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1819
1820 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1821     my $barcode = shift;
1822
1823     my @digits = split //, $barcode;
1824     my $total = 0;
1825     foreach $digit (@digits) {
1826         if ($digit =~ /[0-9]/) { $total += $digit;
1827         } elsif ($digit eq '-') { $total += 10;
1828         } elsif ($digit eq '$') { $total += 11;
1829         } elsif ($digit eq ':') { $total += 12;
1830         } elsif ($digit eq '/') { $total += 13;
1831         } elsif ($digit eq '.') { $total += 14;
1832         } elsif ($digit eq '+') { $total += 15;
1833         } elsif ($digit eq 'A') { $total += 16;
1834         } elsif ($digit eq 'B') { $total += 17;
1835         } elsif ($digit eq 'C') { $total += 18;
1836         } elsif ($digit eq 'D') { $total += 19;
1837         } else { die "invalid digit <$digit>";
1838         }
1839     }
1840     my $remainder = $total % 16;
1841     my $difference = 16 - $remainder;
1842     my $checkdigit;
1843     if ($difference < 10) { $checkdigit = $difference;
1844     } elsif ($difference == 10) { $checkdigit = '-';
1845     } elsif ($difference == 11) { $checkdigit = '$';
1846     } elsif ($difference == 12) { $checkdigit = ':';
1847     } elsif ($difference == 13) { $checkdigit = '/';
1848     } elsif ($difference == 14) { $checkdigit = '.';
1849     } elsif ($difference == 15) { $checkdigit = '+';
1850     } else { die "error calculating checkdigit";
1851     }
1852
1853     return $barcode . $checkdigit;
1854 $$ LANGUAGE PLPERLU STRICT STABLE;
1855
1856 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1857   DECLARE
1858     phone TEXT := $1;
1859     areacode TEXT := $2;
1860     temp TEXT := '';
1861     output TEXT := '';
1862     n_digits INTEGER := 0;
1863   BEGIN
1864     temp := phone;
1865     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1866     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1867     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1868     IF n_digits = 7 AND areacode <> '' THEN
1869       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1870       output := (areacode || '-' || temp);
1871     ELSE
1872       output := temp;
1873     END IF;
1874     RETURN output;
1875   END;
1876
1877 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1878
1879 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1880   my ($marcxml, $pos, $value) = @_;
1881
1882   use MARC::Record;
1883   use MARC::File::XML;
1884
1885   my $xml = $marcxml;
1886   eval {
1887     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1888     my $leader = $marc->leader();
1889     substr($leader, $pos, 1) = $value;
1890     $marc->leader($leader);
1891     $xml = $marc->as_xml_record;
1892     $xml =~ s/^<\?.+?\?>$//mo;
1893     $xml =~ s/\n//sgo;
1894     $xml =~ s/>\s+</></sgo;
1895   };
1896   return $xml;
1897 $$ LANGUAGE PLPERLU STABLE;
1898
1899 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1900   my ($marcxml, $pos, $value) = @_;
1901
1902   use MARC::Record;
1903   use MARC::File::XML;
1904
1905   my $xml = $marcxml;
1906   eval {
1907     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1908     my $f008 = $marc->field('008');
1909
1910     if ($f008) {
1911        my $field = $f008->data();
1912        substr($field, $pos, 1) = $value;
1913        $f008->update($field);
1914        $xml = $marc->as_xml_record;
1915        $xml =~ s/^<\?.+?\?>$//mo;
1916        $xml =~ s/\n//sgo;
1917        $xml =~ s/>\s+</></sgo;
1918     }
1919   };
1920   return $xml;
1921 $$ LANGUAGE PLPERLU STABLE;
1922
1923
1924 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1925   DECLARE
1926     profile ALIAS FOR $1;
1927   BEGIN
1928     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1929   END;
1930 $$ LANGUAGE PLPGSQL STRICT STABLE;
1931
1932
1933 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1934   BEGIN
1935     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1936   END;
1937 $$ LANGUAGE PLPGSQL STRICT STABLE;
1938
1939
1940 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1941
1942   my ($marcxml, $tags) = @_;
1943
1944   use MARC::Record;
1945   use MARC::File::XML;
1946
1947   my $xml = $marcxml;
1948
1949   eval {
1950     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1951     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1952
1953     my @incumbents = ();
1954
1955     foreach my $field ( $marc->fields() ) {
1956       push @incumbents, $field->as_formatted();
1957     }
1958
1959     foreach $field ( $to_insert->fields() ) {
1960       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1961         $marc->insert_fields_ordered( ($field) );
1962       }
1963     }
1964
1965     $xml = $marc->as_xml_record;
1966     $xml =~ s/^<\?.+?\?>$//mo;
1967     $xml =~ s/\n//sgo;
1968     $xml =~ s/>\s+</></sgo;
1969   };
1970
1971   return $xml;
1972
1973 $$ LANGUAGE PLPERLU STABLE;
1974
1975 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1976
1977 -- Usage:
1978 --
1979 --   First make sure the circ matrix is loaded and the circulations
1980 --   have been staged to the extent possible (but at the very least
1981 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1982 --   circ modifiers must also be in place.
1983 --
1984 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1985 --
1986
1987 DECLARE
1988   circ_lib             INT;
1989   target_copy          INT;
1990   usr                  INT;
1991   is_renewal           BOOLEAN;
1992   this_duration_rule   INT;
1993   this_fine_rule       INT;
1994   this_max_fine_rule   INT;
1995   rcd                  config.rule_circ_duration%ROWTYPE;
1996   rrf                  config.rule_recurring_fine%ROWTYPE;
1997   rmf                  config.rule_max_fine%ROWTYPE;
1998   circ                 INT;
1999   n                    INT := 0;
2000   n_circs              INT;
2001
2002 BEGIN
2003
2004   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2005
2006   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2007
2008     -- Fetch the correct rules for this circulation
2009     EXECUTE ('
2010       SELECT
2011         circ_lib,
2012         target_copy,
2013         usr,
2014         CASE
2015           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2016           ELSE FALSE
2017         END
2018       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2019       INTO circ_lib, target_copy, usr, is_renewal ;
2020     SELECT
2021       INTO this_duration_rule,
2022            this_fine_rule,
2023            this_max_fine_rule
2024       duration_rule,
2025       recurring_fine_rule,
2026       max_fine_rule
2027       FROM action.item_user_circ_test(
2028         circ_lib,
2029         target_copy,
2030         usr,
2031         is_renewal
2032         );
2033     SELECT INTO rcd * FROM config.rule_circ_duration
2034       WHERE id = this_duration_rule;
2035     SELECT INTO rrf * FROM config.rule_recurring_fine
2036       WHERE id = this_fine_rule;
2037     SELECT INTO rmf * FROM config.rule_max_fine
2038       WHERE id = this_max_fine_rule;
2039
2040     -- Apply the rules to this circulation
2041     EXECUTE ('UPDATE ' || tablename || ' c
2042     SET
2043       duration_rule = rcd.name,
2044       recurring_fine_rule = rrf.name,
2045       max_fine_rule = rmf.name,
2046       duration = rcd.normal,
2047       recurring_fine = rrf.normal,
2048       max_fine =
2049         CASE rmf.is_percent
2050           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2051           ELSE rmf.amount
2052         END,
2053       renewal_remaining = rcd.max_renewals
2054     FROM
2055       config.rule_circ_duration rcd,
2056       config.rule_recurring_fine rrf,
2057       config.rule_max_fine rmf,
2058                         asset.copy ac
2059     WHERE
2060       rcd.id = ' || this_duration_rule || ' AND
2061       rrf.id = ' || this_fine_rule || ' AND
2062       rmf.id = ' || this_max_fine_rule || ' AND
2063                         ac.id = c.target_copy AND
2064       c.id = ' || circ || ';');
2065
2066     -- Keep track of where we are in the process
2067     n := n + 1;
2068     IF (n % 100 = 0) THEN
2069       RAISE INFO '%', n || ' of ' || n_circs
2070         || ' (' || (100*n/n_circs) || '%) circs updated.';
2071     END IF;
2072
2073   END LOOP;
2074
2075   RETURN;
2076 END;
2077
2078 $$ LANGUAGE plpgsql;
2079
2080 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2081
2082 -- Usage:
2083 --
2084 --   First make sure the circ matrix is loaded and the circulations
2085 --   have been staged to the extent possible (but at the very least
2086 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2087 --   circ modifiers must also be in place.
2088 --
2089 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2090 --
2091
2092 DECLARE
2093   circ_lib             INT;
2094   target_copy          INT;
2095   usr                  INT;
2096   is_renewal           BOOLEAN;
2097   this_duration_rule   INT;
2098   this_fine_rule       INT;
2099   this_max_fine_rule   INT;
2100   rcd                  config.rule_circ_duration%ROWTYPE;
2101   rrf                  config.rule_recurring_fine%ROWTYPE;
2102   rmf                  config.rule_max_fine%ROWTYPE;
2103   circ                 INT;
2104   n                    INT := 0;
2105   n_circs              INT;
2106
2107 BEGIN
2108
2109   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2110
2111   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2112
2113     -- Fetch the correct rules for this circulation
2114     EXECUTE ('
2115       SELECT
2116         circ_lib,
2117         target_copy,
2118         usr,
2119         CASE
2120           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2121           ELSE FALSE
2122         END
2123       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2124       INTO circ_lib, target_copy, usr, is_renewal ;
2125     SELECT
2126       INTO this_duration_rule,
2127            this_fine_rule,
2128            this_max_fine_rule
2129       duration_rule,
2130       recuring_fine_rule,
2131       max_fine_rule
2132       FROM action.find_circ_matrix_matchpoint(
2133         circ_lib,
2134         target_copy,
2135         usr,
2136         is_renewal
2137         );
2138     SELECT INTO rcd * FROM config.rule_circ_duration
2139       WHERE id = this_duration_rule;
2140     SELECT INTO rrf * FROM config.rule_recurring_fine
2141       WHERE id = this_fine_rule;
2142     SELECT INTO rmf * FROM config.rule_max_fine
2143       WHERE id = this_max_fine_rule;
2144
2145     -- Apply the rules to this circulation
2146     EXECUTE ('UPDATE ' || tablename || ' c
2147     SET
2148       duration_rule = rcd.name,
2149       recuring_fine_rule = rrf.name,
2150       max_fine_rule = rmf.name,
2151       duration = rcd.normal,
2152       recuring_fine = rrf.normal,
2153       max_fine =
2154         CASE rmf.is_percent
2155           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2156           ELSE rmf.amount
2157         END,
2158       renewal_remaining = rcd.max_renewals
2159     FROM
2160       config.rule_circ_duration rcd,
2161       config.rule_recuring_fine rrf,
2162       config.rule_max_fine rmf,
2163                         asset.copy ac
2164     WHERE
2165       rcd.id = ' || this_duration_rule || ' AND
2166       rrf.id = ' || this_fine_rule || ' AND
2167       rmf.id = ' || this_max_fine_rule || ' AND
2168                         ac.id = c.target_copy AND
2169       c.id = ' || circ || ';');
2170
2171     -- Keep track of where we are in the process
2172     n := n + 1;
2173     IF (n % 100 = 0) THEN
2174       RAISE INFO '%', n || ' of ' || n_circs
2175         || ' (' || (100*n/n_circs) || '%) circs updated.';
2176     END IF;
2177
2178   END LOOP;
2179
2180   RETURN;
2181 END;
2182
2183 $$ LANGUAGE plpgsql;
2184
2185 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2186
2187 -- Usage:
2188 --
2189 --   First make sure the circ matrix is loaded and the circulations
2190 --   have been staged to the extent possible (but at the very least
2191 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2192 --   circ modifiers must also be in place.
2193 --
2194 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2195 --
2196
2197 DECLARE
2198   circ_lib             INT;
2199   target_copy          INT;
2200   usr                  INT;
2201   is_renewal           BOOLEAN;
2202   this_duration_rule   INT;
2203   this_fine_rule       INT;
2204   this_max_fine_rule   INT;
2205   rcd                  config.rule_circ_duration%ROWTYPE;
2206   rrf                  config.rule_recurring_fine%ROWTYPE;
2207   rmf                  config.rule_max_fine%ROWTYPE;
2208   circ                 INT;
2209   n                    INT := 0;
2210   n_circs              INT;
2211
2212 BEGIN
2213
2214   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2215
2216   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2217
2218     -- Fetch the correct rules for this circulation
2219     EXECUTE ('
2220       SELECT
2221         circ_lib,
2222         target_copy,
2223         usr,
2224         CASE
2225           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2226           ELSE FALSE
2227         END
2228       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2229       INTO circ_lib, target_copy, usr, is_renewal ;
2230     SELECT
2231       INTO this_duration_rule,
2232            this_fine_rule,
2233            this_max_fine_rule
2234       (matchpoint).duration_rule,
2235       (matchpoint).recurring_fine_rule,
2236       (matchpoint).max_fine_rule
2237       FROM action.find_circ_matrix_matchpoint(
2238         circ_lib,
2239         target_copy,
2240         usr,
2241         is_renewal
2242         );
2243     SELECT INTO rcd * FROM config.rule_circ_duration
2244       WHERE id = this_duration_rule;
2245     SELECT INTO rrf * FROM config.rule_recurring_fine
2246       WHERE id = this_fine_rule;
2247     SELECT INTO rmf * FROM config.rule_max_fine
2248       WHERE id = this_max_fine_rule;
2249
2250     -- Apply the rules to this circulation
2251     EXECUTE ('UPDATE ' || tablename || ' c
2252     SET
2253       duration_rule = rcd.name,
2254       recurring_fine_rule = rrf.name,
2255       max_fine_rule = rmf.name,
2256       duration = rcd.normal,
2257       recurring_fine = rrf.normal,
2258       max_fine =
2259         CASE rmf.is_percent
2260           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2261           ELSE rmf.amount
2262         END,
2263       renewal_remaining = rcd.max_renewals,
2264       grace_period = rrf.grace_period
2265     FROM
2266       config.rule_circ_duration rcd,
2267       config.rule_recurring_fine rrf,
2268       config.rule_max_fine rmf,
2269                         asset.copy ac
2270     WHERE
2271       rcd.id = ' || this_duration_rule || ' AND
2272       rrf.id = ' || this_fine_rule || ' AND
2273       rmf.id = ' || this_max_fine_rule || ' AND
2274                         ac.id = c.target_copy AND
2275       c.id = ' || circ || ';');
2276
2277     -- Keep track of where we are in the process
2278     n := n + 1;
2279     IF (n % 100 = 0) THEN
2280       RAISE INFO '%', n || ' of ' || n_circs
2281         || ' (' || (100*n/n_circs) || '%) circs updated.';
2282     END IF;
2283
2284   END LOOP;
2285
2286   RETURN;
2287 END;
2288
2289 $$ LANGUAGE plpgsql;
2290
2291 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2292 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2293 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2294 DECLARE
2295     context_lib             INT;
2296     charge_lost_on_zero     BOOLEAN;
2297     min_price               NUMERIC;
2298     max_price               NUMERIC;
2299     default_price           NUMERIC;
2300     working_price           NUMERIC;
2301
2302 BEGIN
2303
2304     SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2305         FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2306
2307     SELECT INTO charge_lost_on_zero value
2308         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2309
2310     SELECT INTO min_price value
2311         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2312
2313     SELECT INTO max_price value
2314         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2315
2316     SELECT INTO default_price value
2317         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2318
2319     SELECT INTO working_price price FROM asset.copy WHERE id = item;
2320
2321     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2322         working_price := default_price;
2323     END IF;
2324
2325     IF (max_price IS NOT NULL AND working_price > max_price) THEN
2326         working_price := max_price;
2327     END IF;
2328
2329     IF (min_price IS NOT NULL AND working_price < min_price) THEN
2330         IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2331             working_price := min_price;
2332         END IF;
2333     END IF;
2334
2335     RETURN working_price;
2336
2337 END;
2338
2339 $$ LANGUAGE plpgsql;
2340
2341 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2342
2343 -- Usage:
2344 --
2345 --   First make sure the circ matrix is loaded and the circulations
2346 --   have been staged to the extent possible (but at the very least
2347 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2348 --   circ modifiers must also be in place.
2349 --
2350 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2351 --
2352
2353 DECLARE
2354   circ_lib             INT;
2355   target_copy          INT;
2356   usr                  INT;
2357   is_renewal           BOOLEAN;
2358   this_duration_rule   INT;
2359   this_fine_rule       INT;
2360   this_max_fine_rule   INT;
2361   rcd                  config.rule_circ_duration%ROWTYPE;
2362   rrf                  config.rule_recurring_fine%ROWTYPE;
2363   rmf                  config.rule_max_fine%ROWTYPE;
2364   n                    INT := 0;
2365   n_circs              INT := 1;
2366
2367 BEGIN
2368
2369   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2370
2371   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2372
2373     -- Fetch the correct rules for this circulation
2374     EXECUTE ('
2375       SELECT
2376         circ_lib,
2377         target_copy,
2378         usr,
2379         CASE
2380           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2381           ELSE FALSE
2382         END
2383       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2384       INTO circ_lib, target_copy, usr, is_renewal ;
2385     SELECT
2386       INTO this_duration_rule,
2387            this_fine_rule,
2388            this_max_fine_rule
2389       (matchpoint).duration_rule,
2390       (matchpoint).recurring_fine_rule,
2391       (matchpoint).max_fine_rule
2392       FROM action.find_circ_matrix_matchpoint(
2393         circ_lib,
2394         target_copy,
2395         usr,
2396         is_renewal
2397         );
2398     SELECT INTO rcd * FROM config.rule_circ_duration
2399       WHERE id = this_duration_rule;
2400     SELECT INTO rrf * FROM config.rule_recurring_fine
2401       WHERE id = this_fine_rule;
2402     SELECT INTO rmf * FROM config.rule_max_fine
2403       WHERE id = this_max_fine_rule;
2404
2405     -- Apply the rules to this circulation
2406     EXECUTE ('UPDATE ' || tablename || ' c
2407     SET
2408       duration_rule = rcd.name,
2409       recurring_fine_rule = rrf.name,
2410       max_fine_rule = rmf.name,
2411       duration = rcd.normal,
2412       recurring_fine = rrf.normal,
2413       max_fine =
2414         CASE rmf.is_percent
2415           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2416           ELSE rmf.amount
2417         END,
2418       renewal_remaining = rcd.max_renewals,
2419       grace_period = rrf.grace_period
2420     FROM
2421       config.rule_circ_duration rcd,
2422       config.rule_recurring_fine rrf,
2423       config.rule_max_fine rmf,
2424                         asset.copy ac
2425     WHERE
2426       rcd.id = ' || this_duration_rule || ' AND
2427       rrf.id = ' || this_fine_rule || ' AND
2428       rmf.id = ' || this_max_fine_rule || ' AND
2429                         ac.id = c.target_copy AND
2430       c.id = ' || circ || ';');
2431
2432     -- Keep track of where we are in the process
2433     n := n + 1;
2434     IF (n % 100 = 0) THEN
2435       RAISE INFO '%', n || ' of ' || n_circs
2436         || ' (' || (100*n/n_circs) || '%) circs updated.';
2437     END IF;
2438
2439   --END LOOP;
2440
2441   RETURN;
2442 END;
2443
2444 $$ LANGUAGE plpgsql;
2445
2446
2447
2448
2449 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2450
2451 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2452 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2453
2454 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2455 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2456
2457 DECLARE
2458         c                    TEXT := schemaname || '.asset_copy_legacy';
2459         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
2460         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
2461         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2462         stat_cat                                                 INT;
2463   stat_cat_entry       INT;
2464
2465 BEGIN
2466
2467   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2468
2469                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2470
2471                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2472                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2473                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2474
2475   END LOOP;
2476
2477   RETURN;
2478 END;
2479
2480 $$ LANGUAGE plpgsql;
2481
2482 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2483
2484 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2485 --        This will assign standing penalties as needed.
2486
2487 DECLARE
2488   org_unit  INT;
2489   usr       INT;
2490
2491 BEGIN
2492
2493   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2494
2495     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2496
2497       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2498
2499     END LOOP;
2500
2501   END LOOP;
2502
2503   RETURN;
2504
2505 END;
2506
2507 $$ LANGUAGE plpgsql;
2508
2509
2510 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2511
2512 BEGIN
2513   INSERT INTO metabib.metarecord (fingerprint, master_record)
2514     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2515       FROM  biblio.record_entry b
2516       WHERE NOT b.deleted
2517         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)
2518         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2519       ORDER BY b.fingerprint, b.quality DESC;
2520   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2521     SELECT  m.id, r.id
2522       FROM  biblio.record_entry r
2523       JOIN  metabib.metarecord m USING (fingerprint)
2524      WHERE  NOT r.deleted;
2525 END;
2526
2527 $$ LANGUAGE plpgsql;
2528
2529
2530 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2531
2532 BEGIN
2533   INSERT INTO metabib.metarecord (fingerprint, master_record)
2534     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2535       FROM  biblio.record_entry b
2536       WHERE NOT b.deleted
2537         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)
2538         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2539       ORDER BY b.fingerprint, b.quality DESC;
2540   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2541     SELECT  m.id, r.id
2542       FROM  biblio.record_entry r
2543         JOIN metabib.metarecord m USING (fingerprint)
2544       WHERE NOT r.deleted
2545         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);
2546 END;
2547
2548 $$ LANGUAGE plpgsql;
2549
2550
2551 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2552
2553 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2554 --        Then SELECT migration_tools.create_cards('m_foo');
2555
2556 DECLARE
2557         u                    TEXT := schemaname || '.actor_usr_legacy';
2558         c                    TEXT := schemaname || '.actor_card';
2559
2560 BEGIN
2561
2562         EXECUTE ('DELETE FROM ' || c || ';');
2563         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2564         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2565
2566   RETURN;
2567
2568 END;
2569
2570 $$ LANGUAGE plpgsql;
2571
2572
2573 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2574
2575   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2576
2577   my ($marcxml, $shortname) = @_;
2578
2579   use MARC::Record;
2580   use MARC::File::XML;
2581
2582   my $xml = $marcxml;
2583
2584   eval {
2585     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2586
2587     foreach my $field ( $marc->field('856') ) {
2588       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2589            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2590         $field->add_subfields( '9' => $shortname );
2591                                 $field->update( ind2 => '0');
2592       }
2593     }
2594
2595     $xml = $marc->as_xml_record;
2596     $xml =~ s/^<\?.+?\?>$//mo;
2597     $xml =~ s/\n//sgo;
2598     $xml =~ s/>\s+</></sgo;
2599   };
2600
2601   return $xml;
2602
2603 $$ LANGUAGE PLPERLU STABLE;
2604
2605 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2606
2607   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2608
2609   my ($marcxml, $shortname) = @_;
2610
2611   use MARC::Record;
2612   use MARC::File::XML;
2613
2614   my $xml = $marcxml;
2615
2616   eval {
2617     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2618
2619     foreach my $field ( $marc->field('856') ) {
2620       if ( ! $field->as_string('9') ) {
2621         $field->add_subfields( '9' => $shortname );
2622       }
2623     }
2624
2625     $xml = $marc->as_xml_record;
2626     $xml =~ s/^<\?.+?\?>$//mo;
2627     $xml =~ s/\n//sgo;
2628     $xml =~ s/>\s+</></sgo;
2629   };
2630
2631   return $xml;
2632
2633 $$ LANGUAGE PLPERLU STABLE;
2634
2635
2636 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2637
2638 DECLARE
2639   old_volume   BIGINT;
2640   new_volume   BIGINT;
2641   bib          BIGINT;
2642   owner        INTEGER;
2643   old_label    TEXT;
2644   remainder    BIGINT;
2645
2646 BEGIN
2647
2648   -- Bail out if asked to change the label to ##URI##
2649   IF new_label = '##URI##' THEN
2650     RETURN;
2651   END IF;
2652
2653   -- Gather information
2654   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2655   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2656   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2657
2658   -- Bail out if the label already is ##URI##
2659   IF old_label = '##URI##' THEN
2660     RETURN;
2661   END IF;
2662
2663   -- Bail out if the call number label is already correct
2664   IF new_volume = old_volume THEN
2665     RETURN;
2666   END IF;
2667
2668   -- Check whether we already have a destination volume available
2669   SELECT id INTO new_volume FROM asset.call_number
2670     WHERE
2671       record = bib AND
2672       owning_lib = owner AND
2673       label = new_label AND
2674       NOT deleted;
2675
2676   -- Create destination volume if needed
2677   IF NOT FOUND THEN
2678     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2679       VALUES (1, 1, bib, owner, new_label, cn_class);
2680     SELECT id INTO new_volume FROM asset.call_number
2681       WHERE
2682         record = bib AND
2683         owning_lib = owner AND
2684         label = new_label AND
2685         NOT deleted;
2686   END IF;
2687
2688   -- Move copy to destination
2689   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2690
2691   -- Delete source volume if it is now empty
2692   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2693   IF NOT FOUND THEN
2694     DELETE FROM asset.call_number WHERE id = old_volume;
2695   END IF;
2696
2697 END;
2698
2699 $$ LANGUAGE plpgsql;
2700
2701 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2702
2703         my $input = $_[0];
2704         my %zipdata;
2705
2706         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2707
2708         while (<FH>) {
2709                 chomp;
2710                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2711                 $zipdata{$zip} = [$city, $state, $county];
2712         }
2713
2714         if (defined $zipdata{$input}) {
2715                 my ($city, $state, $county) = @{$zipdata{$input}};
2716                 return [$city, $state, $county];
2717         } elsif (defined $zipdata{substr $input, 0, 5}) {
2718                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2719                 return [$city, $state, $county];
2720         } else {
2721                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2722         }
2723
2724 $$ LANGUAGE PLPERLU STABLE;
2725
2726 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2727
2728 DECLARE
2729   ou  INT;
2730         org_unit_depth INT;
2731         ou_parent INT;
2732         parent_depth INT;
2733   errors_found BOOLEAN;
2734         ou_shortname TEXT;
2735         parent_shortname TEXT;
2736         ou_type_name TEXT;
2737         parent_type TEXT;
2738         type_id INT;
2739         type_depth INT;
2740         type_parent INT;
2741         type_parent_depth INT;
2742         proper_parent TEXT;
2743
2744 BEGIN
2745
2746         errors_found := FALSE;
2747
2748 -- Checking actor.org_unit_type
2749
2750         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2751
2752                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2753                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2754
2755                 IF type_parent IS NOT NULL THEN
2756
2757                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2758
2759                         IF type_depth - type_parent_depth <> 1 THEN
2760                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2761                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2762                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2763                                         ou_type_name, type_depth, parent_type, type_parent_depth;
2764                                 errors_found := TRUE;
2765
2766                         END IF;
2767
2768                 END IF;
2769
2770         END LOOP;
2771
2772 -- Checking actor.org_unit
2773
2774   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2775
2776                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2777                 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;
2778                 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;
2779                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2780                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2781                 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;
2782                 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;
2783
2784                 IF ou_parent IS NOT NULL THEN
2785
2786                         IF      (org_unit_depth - parent_depth <> 1) OR (
2787                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2788                         ) THEN
2789                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2790                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2791                                 errors_found := TRUE;
2792                         END IF;
2793
2794                 END IF;
2795
2796   END LOOP;
2797
2798         IF NOT errors_found THEN
2799                 RAISE INFO 'No errors found.';
2800         END IF;
2801
2802   RETURN;
2803
2804 END;
2805
2806 $$ LANGUAGE plpgsql;
2807
2808
2809 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2810
2811 BEGIN
2812
2813         DELETE FROM asset.opac_visible_copies;
2814
2815         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2816                 SELECT DISTINCT
2817                         cp.id, cp.circ_lib, cn.record
2818                 FROM
2819                         asset.copy cp
2820                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
2821                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2822                         JOIN asset.copy_location cl ON (cp.location = cl.id)
2823                         JOIN config.copy_status cs ON (cp.status = cs.id)
2824                         JOIN biblio.record_entry b ON (cn.record = b.id)
2825                 WHERE
2826                         NOT cp.deleted AND
2827                         NOT cn.deleted AND
2828                         NOT b.deleted AND
2829                         cs.opac_visible AND
2830                         cl.opac_visible AND
2831                         cp.opac_visible AND
2832                         a.opac_visible AND
2833                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2834
2835 END;
2836
2837 $$ LANGUAGE plpgsql;
2838
2839
2840 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2841
2842 DECLARE
2843   old_volume     BIGINT;
2844   new_volume     BIGINT;
2845   bib            BIGINT;
2846   old_owning_lib INTEGER;
2847         old_label      TEXT;
2848   remainder      BIGINT;
2849
2850 BEGIN
2851
2852   -- Gather information
2853   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2854   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2855   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2856
2857         -- Bail out if the new_owning_lib is not the ID of an org_unit
2858         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2859                 RAISE WARNING
2860                         '% is not a valid actor.org_unit ID; no change made.',
2861                                 new_owning_lib;
2862                 RETURN;
2863         END IF;
2864
2865   -- Bail out discreetly if the owning_lib is already correct
2866   IF new_owning_lib = old_owning_lib THEN
2867     RETURN;
2868   END IF;
2869
2870   -- Check whether we already have a destination volume available
2871   SELECT id INTO new_volume FROM asset.call_number
2872     WHERE
2873       record = bib AND
2874       owning_lib = new_owning_lib AND
2875       label = old_label AND
2876       NOT deleted;
2877
2878   -- Create destination volume if needed
2879   IF NOT FOUND THEN
2880     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2881       VALUES (1, 1, bib, new_owning_lib, old_label);
2882     SELECT id INTO new_volume FROM asset.call_number
2883       WHERE
2884         record = bib AND
2885         owning_lib = new_owning_lib AND
2886         label = old_label AND
2887         NOT deleted;
2888   END IF;
2889
2890   -- Move copy to destination
2891   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2892
2893   -- Delete source volume if it is now empty
2894   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2895   IF NOT FOUND THEN
2896     DELETE FROM asset.call_number WHERE id = old_volume;
2897   END IF;
2898
2899 END;
2900
2901 $$ LANGUAGE plpgsql;
2902
2903
2904 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2905
2906 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2907
2908 DECLARE
2909         new_owning_lib  INTEGER;
2910
2911 BEGIN
2912
2913         -- Parse the new_owner as an org unit ID or shortname
2914         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2915                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2916                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2917         ELSIF new_owner ~ E'^[0-9]+$' THEN
2918                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2919                         RAISE INFO
2920                                 '%',
2921                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2922                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2923                         new_owning_lib := new_owner::INTEGER;
2924                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2925                 END IF;
2926         ELSE
2927                 RAISE WARNING
2928                         '% is not a valid actor.org_unit shortname or ID; no change made.',
2929                         new_owning_lib;
2930                 RETURN;
2931         END IF;
2932
2933 END;
2934
2935 $$ LANGUAGE plpgsql;
2936
2937 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2938
2939 use MARC::Record;
2940 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2941 use MARC::Charset;
2942
2943 MARC::Charset->assume_unicode(1);
2944
2945 my $xml = shift;
2946
2947 eval {
2948     my $r = MARC::Record->new_from_xml( $xml );
2949     my $output_xml = $r->as_xml_record();
2950 };
2951 if ($@) {
2952     return 0;
2953 } else {
2954     return 1;
2955 }
2956
2957 $func$ LANGUAGE PLPERLU;
2958 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2959
2960 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2961 BEGIN
2962    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2963            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2964            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2965    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2966            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2967            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2968    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2969            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2970            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2971    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2972            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2973            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2974    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2975            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2976            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2977    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2978            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2979            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2980    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2981            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2982            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2983    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2984    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2985    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2986    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2987    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2988 END;
2989 $FUNC$ LANGUAGE PLPGSQL;
2990
2991 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2992 BEGIN
2993    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2994    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2995    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2996    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2997    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2998    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2999    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
3000
3001    -- import any new circ rules
3002    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3003    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3004    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3005    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3006
3007    -- and permission groups
3008    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3009
3010 END;
3011 $FUNC$ LANGUAGE PLPGSQL;
3012
3013
3014 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$
3015 DECLARE
3016     name TEXT;
3017     loopq TEXT;
3018     existsq TEXT;
3019     ct INTEGER;
3020     cols TEXT[];
3021     copyst TEXT;
3022 BEGIN
3023     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3024     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3025     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
3026     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3027     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3028     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3029     FOR name IN EXECUTE loopq LOOP
3030        EXECUTE existsq INTO ct USING name;
3031        IF ct = 0 THEN
3032            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3033            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3034                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3035            EXECUTE copyst USING name;
3036        END IF;
3037     END LOOP;
3038 END;
3039 $FUNC$ LANGUAGE PLPGSQL;
3040
3041 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3042 DECLARE
3043     id BIGINT;
3044     loopq TEXT;
3045     cols TEXT[];
3046     splitst TEXT;
3047 BEGIN
3048     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3049     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;
3050     FOR id IN EXECUTE loopq USING delimiter LOOP
3051        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3052        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3053                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3054        EXECUTE splitst USING id, delimiter;
3055     END LOOP;
3056 END;
3057 $FUNC$ LANGUAGE PLPGSQL;
3058
3059 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3060
3061 use strict;
3062 use warnings;
3063
3064 use MARC::Record;
3065 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3066 use MARC::Charset;
3067
3068 MARC::Charset->assume_unicode(1);
3069
3070 my $target_xml = shift;
3071 my $source_xml = shift;
3072 my $tags = shift;
3073
3074 my $target;
3075 my $source;
3076
3077 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3078 if ($@) {
3079     return;
3080 }
3081 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3082 if ($@) {
3083     return;
3084 }
3085
3086 my $source_id = $source->subfield('901', 'c');
3087 $source_id = $source->subfield('903', 'a') unless $source_id;
3088 my $target_id = $target->subfield('901', 'c');
3089 $target_id = $target->subfield('903', 'a') unless $target_id;
3090
3091 my %existing_fields;
3092 foreach my $tag (@$tags) {
3093     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3094     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3095     $target->insert_fields_ordered(map { $_->clone() } @to_add);
3096     if (@to_add) {
3097         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3098     }
3099 }
3100
3101 my $xml = $target->as_xml_record;
3102 $xml =~ s/^<\?.+?\?>$//mo;
3103 $xml =~ s/\n//sgo;
3104 $xml =~ s/>\s+</></sgo;
3105
3106 return $xml;
3107
3108 $func$ LANGUAGE PLPERLU;
3109 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
3110
3111 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3112
3113 use strict;
3114 use warnings;
3115
3116 use MARC::Record;
3117 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3118 use Text::CSV;
3119
3120 my $in_tags = shift;
3121 my $in_values = shift;
3122
3123 # hack-and-slash parsing of array-passed-as-string;
3124 # this can go away once everybody is running Postgres 9.1+
3125 my $csv = Text::CSV->new({binary => 1});
3126 $in_tags =~ s/^{//;
3127 $in_tags =~ s/}$//;
3128 my $status = $csv->parse($in_tags);
3129 my $tags = [ $csv->fields() ];
3130 $in_values =~ s/^{//;
3131 $in_values =~ s/}$//;
3132 $status = $csv->parse($in_values);
3133 my $values = [ $csv->fields() ];
3134
3135 my $marc = MARC::Record->new();
3136
3137 $marc->leader('00000nam a22000007  4500');
3138 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3139
3140 foreach my $i (0..$#$tags) {
3141     my ($tag, $sf);
3142     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3143         $tag = $1;
3144         $sf = $2;
3145         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3146     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3147         $tag = $1;
3148         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3149     }
3150 }
3151
3152 my $xml = $marc->as_xml_record;
3153 $xml =~ s/^<\?.+?\?>$//mo;
3154 $xml =~ s/\n//sgo;
3155 $xml =~ s/>\s+</></sgo;
3156
3157 return $xml;
3158
3159 $func$ LANGUAGE PLPERLU;
3160 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3161 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3162 The second argument is an array of text containing the values to plug into each field.
3163 If the value for a given field is NULL or the empty string, it is not inserted.
3164 $$;
3165
3166 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3167
3168 use strict;
3169 use warnings;
3170
3171 use MARC::Record;
3172 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3173 use Text::CSV;
3174
3175 my $in_tags = shift;
3176 my $in_ind1 = shift;
3177 my $in_ind2 = shift;
3178 my $in_values = shift;
3179
3180 # hack-and-slash parsing of array-passed-as-string;
3181 # this can go away once everybody is running Postgres 9.1+
3182 my $csv = Text::CSV->new({binary => 1});
3183 $in_tags =~ s/^{//;
3184 $in_tags =~ s/}$//;
3185 my $status = $csv->parse($in_tags);
3186 my $tags = [ $csv->fields() ];
3187 $in_ind1 =~ s/^{//;
3188 $in_ind1 =~ s/}$//;
3189 $status = $csv->parse($in_ind1);
3190 my $ind1s = [ $csv->fields() ];
3191 $in_ind2 =~ s/^{//;
3192 $in_ind2 =~ s/}$//;
3193 $status = $csv->parse($in_ind2);
3194 my $ind2s = [ $csv->fields() ];
3195 $in_values =~ s/^{//;
3196 $in_values =~ s/}$//;
3197 $status = $csv->parse($in_values);
3198 my $values = [ $csv->fields() ];
3199
3200 my $marc = MARC::Record->new();
3201
3202 $marc->leader('00000nam a22000007  4500');
3203 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3204
3205 foreach my $i (0..$#$tags) {
3206     my ($tag, $sf);
3207     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3208         $tag = $1;
3209         $sf = $2;
3210         $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3211     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3212         $tag = $1;
3213         $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3214     }
3215 }
3216
3217 my $xml = $marc->as_xml_record;
3218 $xml =~ s/^<\?.+?\?>$//mo;
3219 $xml =~ s/\n//sgo;
3220 $xml =~ s/>\s+</></sgo;
3221
3222 return $xml;
3223
3224 $func$ LANGUAGE PLPERLU;
3225 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3226 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3227 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3228 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3229 The fourth argument is an array of text containing the values to plug into each field.
3230 If the value for a given field is NULL or the empty string, it is not inserted.
3231 $$;
3232
3233 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3234
3235 my ($marcxml, $tag, $pos, $value) = @_;
3236
3237 use MARC::Record;
3238 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3239 use MARC::Charset;
3240 use strict;
3241
3242 MARC::Charset->assume_unicode(1);
3243
3244 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3245 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3246 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3247 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3248
3249 my $xml = $marcxml;
3250 eval {
3251     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3252
3253     foreach my $field ($marc->field($tag)) {
3254         $field->update("ind$pos" => $value);
3255     }
3256     $xml = $marc->as_xml_record;
3257     $xml =~ s/^<\?.+?\?>$//mo;
3258     $xml =~ s/\n//sgo;
3259     $xml =~ s/>\s+</></sgo;
3260 };
3261 return $xml;
3262
3263 $func$ LANGUAGE PLPERLU;
3264
3265 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3266 The first argument is a MARCXML string.
3267 The second argument is a MARC tag.
3268 The third argument is the indicator position, either 1 or 2.
3269 The fourth argument is the character to set the indicator value to.
3270 All occurences of the specified field will be changed.
3271 The function returns the revised MARCXML string.$$;
3272
3273 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3274     username TEXT,
3275     password TEXT,
3276     org TEXT,
3277     perm_group TEXT,
3278     first_name TEXT DEFAULT '',
3279     last_name TEXT DEFAULT ''
3280 ) RETURNS VOID AS $func$
3281 BEGIN
3282     RAISE NOTICE '%', org ;
3283     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3284     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3285     FROM   actor.org_unit aou, permission.grp_tree pgt
3286     WHERE  aou.shortname = org
3287     AND    pgt.name = perm_group;
3288 END
3289 $func$
3290 LANGUAGE PLPGSQL;
3291
3292 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3293 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3294     DECLARE
3295         target_event_def ALIAS FOR $1;
3296         orgs ALIAS FOR $2;
3297     BEGIN
3298         DROP TABLE IF EXISTS new_atevdefs;
3299         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3300         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3301             INSERT INTO action_trigger.event_definition (
3302                 active
3303                 ,owner
3304                 ,name
3305                 ,hook
3306                 ,validator
3307                 ,reactor
3308                 ,cleanup_success
3309                 ,cleanup_failure
3310                 ,delay
3311                 ,max_delay
3312                 ,usr_field
3313                 ,opt_in_setting
3314                 ,delay_field
3315                 ,group_field
3316                 ,template
3317                 ,granularity
3318                 ,repeat_delay
3319             ) SELECT
3320                 'f'
3321                 ,orgs[i]
3322                 ,name || ' (clone of '||target_event_def||')'
3323                 ,hook
3324                 ,validator
3325                 ,reactor
3326                 ,cleanup_success
3327                 ,cleanup_failure
3328                 ,delay
3329                 ,max_delay
3330                 ,usr_field
3331                 ,opt_in_setting
3332                 ,delay_field
3333                 ,group_field
3334                 ,template
3335                 ,granularity
3336                 ,repeat_delay
3337             FROM
3338                 action_trigger.event_definition
3339             WHERE
3340                 id = target_event_def
3341             ;
3342             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3343             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3344             INSERT INTO action_trigger.environment (
3345                 event_def
3346                 ,path
3347                 ,collector
3348                 ,label
3349             ) SELECT
3350                 currval('action_trigger.event_definition_id_seq')
3351                 ,path
3352                 ,collector
3353                 ,label
3354             FROM
3355                 action_trigger.environment
3356             WHERE
3357                 event_def = target_event_def
3358             ;
3359             INSERT INTO action_trigger.event_params (
3360                 event_def
3361                 ,param
3362                 ,value
3363             ) SELECT
3364                 currval('action_trigger.event_definition_id_seq')
3365                 ,param
3366                 ,value
3367             FROM
3368                 action_trigger.event_params
3369             WHERE
3370                 event_def = target_event_def
3371             ;
3372         END LOOP;
3373         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);
3374     END;
3375 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3376
3377 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3378 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3379     DECLARE
3380         target_event_def ALIAS FOR $1;
3381         orgs ALIAS FOR $2;
3382         new_interval ALIAS FOR $3;
3383     BEGIN
3384         DROP TABLE IF EXISTS new_atevdefs;
3385         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3386         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3387             INSERT INTO action_trigger.event_definition (
3388                 active
3389                 ,owner
3390                 ,name
3391                 ,hook
3392                 ,validator
3393                 ,reactor
3394                 ,cleanup_success
3395                 ,cleanup_failure
3396                 ,delay
3397                 ,max_delay
3398                 ,usr_field
3399                 ,opt_in_setting
3400                 ,delay_field
3401                 ,group_field
3402                 ,template
3403                 ,granularity
3404                 ,repeat_delay
3405             ) SELECT
3406                 'f'
3407                 ,orgs[i]
3408                 ,name || ' (clone of '||target_event_def||')'
3409                 ,hook
3410                 ,validator
3411                 ,reactor
3412                 ,cleanup_success
3413                 ,cleanup_failure
3414                 ,new_interval
3415                 ,max_delay
3416                 ,usr_field
3417                 ,opt_in_setting
3418                 ,delay_field
3419                 ,group_field
3420                 ,template
3421                 ,granularity
3422                 ,repeat_delay
3423             FROM
3424                 action_trigger.event_definition
3425             WHERE
3426                 id = target_event_def
3427             ;
3428             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3429             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3430             INSERT INTO action_trigger.environment (
3431                 event_def
3432                 ,path
3433                 ,collector
3434                 ,label
3435             ) SELECT
3436                 currval('action_trigger.event_definition_id_seq')
3437                 ,path
3438                 ,collector
3439                 ,label
3440             FROM
3441                 action_trigger.environment
3442             WHERE
3443                 event_def = target_event_def
3444             ;
3445             INSERT INTO action_trigger.event_params (
3446                 event_def
3447                 ,param
3448                 ,value
3449             ) SELECT
3450                 currval('action_trigger.event_definition_id_seq')
3451                 ,param
3452                 ,value
3453             FROM
3454                 action_trigger.event_params
3455             WHERE
3456                 event_def = target_event_def
3457             ;
3458         END LOOP;
3459         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);
3460     END;
3461 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3462
3463 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3464 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3465     DECLARE
3466         org ALIAS FOR $1;
3467         target_event_defs ALIAS FOR $2;
3468     BEGIN
3469         DROP TABLE IF EXISTS new_atevdefs;
3470         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3471         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3472             INSERT INTO action_trigger.event_definition (
3473                 active
3474                 ,owner
3475                 ,name
3476                 ,hook
3477                 ,validator
3478                 ,reactor
3479                 ,cleanup_success
3480                 ,cleanup_failure
3481                 ,delay
3482                 ,max_delay
3483                 ,usr_field
3484                 ,opt_in_setting
3485                 ,delay_field
3486                 ,group_field
3487                 ,template
3488                 ,granularity
3489                 ,repeat_delay
3490             ) SELECT
3491                 'f'
3492                 ,org
3493                 ,name || ' (clone of '||target_event_defs[i]||')'
3494                 ,hook
3495                 ,validator
3496                 ,reactor
3497                 ,cleanup_success
3498                 ,cleanup_failure
3499                 ,delay
3500                 ,max_delay
3501                 ,usr_field
3502                 ,opt_in_setting
3503                 ,delay_field
3504                 ,group_field
3505                 ,template
3506                 ,granularity
3507                 ,repeat_delay
3508             FROM
3509                 action_trigger.event_definition
3510             WHERE
3511                 id = target_event_defs[i]
3512             ;
3513             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3514             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3515             INSERT INTO action_trigger.environment (
3516                 event_def
3517                 ,path
3518                 ,collector
3519                 ,label
3520             ) SELECT
3521                 currval('action_trigger.event_definition_id_seq')
3522                 ,path
3523                 ,collector
3524                 ,label
3525             FROM
3526                 action_trigger.environment
3527             WHERE
3528                 event_def = target_event_defs[i]
3529             ;
3530             INSERT INTO action_trigger.event_params (
3531                 event_def
3532                 ,param
3533                 ,value
3534             ) SELECT
3535                 currval('action_trigger.event_definition_id_seq')
3536                 ,param
3537                 ,value
3538             FROM
3539                 action_trigger.event_params
3540             WHERE
3541                 event_def = target_event_defs[i]
3542             ;
3543         END LOOP;
3544         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3545     END;
3546 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3547
3548 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3549     UPDATE
3550         action_trigger.event
3551     SET
3552          start_time = NULL
3553         ,update_time = NULL
3554         ,complete_time = NULL
3555         ,update_process = NULL
3556         ,state = 'pending'
3557         ,template_output = NULL
3558         ,error_output = NULL
3559         ,async_output = NULL
3560     WHERE
3561         id = $1;
3562 $$ LANGUAGE SQL;
3563
3564 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3565     my ($marcxml) = @_;
3566
3567     use MARC::Record;
3568     use MARC::File::XML;
3569     use MARC::Field;
3570
3571     my $field;
3572     eval {
3573         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3574         $field = $marc->leader();
3575     };
3576     return $field;
3577 $$ LANGUAGE PLPERLU STABLE;
3578
3579 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3580     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3581
3582     use MARC::Record;
3583     use MARC::File::XML;
3584     use MARC::Field;
3585
3586     my $field;
3587     eval {
3588         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3589         $field = $marc->field($tag);
3590     };
3591     return $field->as_string($subfield,$delimiter);
3592 $$ LANGUAGE PLPERLU STABLE;
3593
3594 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3595     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3596
3597     use MARC::Record;
3598     use MARC::File::XML;
3599     use MARC::Field;
3600
3601     my @fields;
3602     eval {
3603         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3604         @fields = $marc->field($tag);
3605     };
3606     my @texts;
3607     foreach my $field (@fields) {
3608         push @texts, $field->as_string($subfield,$delimiter);
3609     }
3610     return \@texts;
3611 $$ LANGUAGE PLPERLU STABLE;
3612
3613 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3614     my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3615
3616     use MARC::Record;
3617     use MARC::File::XML;
3618     use MARC::Field;
3619
3620     my @fields;
3621     eval {
3622         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3623         @fields = $marc->field($tag);
3624     };
3625     my @texts;
3626     foreach my $field (@fields) {
3627         if ($field->as_string() =~ qr/$match/) {
3628             push @texts, $field->as_string($subfield,$delimiter);
3629         }
3630     }
3631     return \@texts;
3632 $$ LANGUAGE PLPERLU STABLE;
3633
3634 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3635     SELECT action.find_hold_matrix_matchpoint(
3636         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3637         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3638         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3639         (SELECT usr FROM action.hold_request WHERE id = $1),
3640         (SELECT requestor FROM action.hold_request WHERE id = $1)
3641     );
3642 $$ LANGUAGE SQL;
3643
3644 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3645     SELECT action.hold_request_permit_test(
3646         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3647         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3648         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3649         (SELECT usr FROM action.hold_request WHERE id = $1),
3650         (SELECT requestor FROM action.hold_request WHERE id = $1)
3651     );
3652 $$ LANGUAGE SQL;
3653
3654 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3655     SELECT action.find_circ_matrix_matchpoint(
3656         (SELECT circ_lib FROM action.circulation WHERE id = $1),
3657         (SELECT target_copy FROM action.circulation WHERE id = $1),
3658         (SELECT usr FROM action.circulation WHERE id = $1),
3659         (SELECT COALESCE(
3660                 NULLIF(phone_renewal,false),
3661                 NULLIF(desk_renewal,false),
3662                 NULLIF(opac_renewal,false),
3663                 false
3664             ) FROM action.circulation WHERE id = $1
3665         )
3666     );
3667 $$ LANGUAGE SQL;
3668
3669 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3670     DECLARE
3671         test ALIAS FOR $1;
3672     BEGIN
3673         IF NOT test THEN
3674             RAISE EXCEPTION 'assertion';
3675         END IF;
3676     END;
3677 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3678
3679 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3680     DECLARE
3681         test ALIAS FOR $1;
3682         msg ALIAS FOR $2;
3683     BEGIN
3684         IF NOT test THEN
3685             RAISE EXCEPTION '%', msg;
3686         END IF;
3687     END;
3688 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3689
3690 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3691     DECLARE
3692         test ALIAS FOR $1;
3693         fail_msg ALIAS FOR $2;
3694         success_msg ALIAS FOR $3;
3695     BEGIN
3696         IF NOT test THEN
3697             RAISE EXCEPTION '%', fail_msg;
3698         END IF;
3699         RETURN success_msg;
3700     END;
3701 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3702
3703 -- push bib sequence and return starting value for reserved range
3704 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3705     DECLARE
3706         bib_count ALIAS FOR $1;
3707         output BIGINT;
3708     BEGIN
3709         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3710         FOR output IN
3711             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3712         LOOP
3713             RETURN output;
3714         END LOOP;
3715     END;
3716 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3717
3718 -- set a new salted password
3719
3720 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3721     DECLARE
3722         usr_id              ALIAS FOR $1;
3723         plain_passwd        ALIAS FOR $2;
3724         plain_salt          TEXT;
3725         md5_passwd          TEXT;
3726     BEGIN
3727
3728         SELECT actor.create_salt('main') INTO plain_salt;
3729
3730         SELECT MD5(plain_passwd) INTO md5_passwd;
3731
3732         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3733
3734         RETURN TRUE;
3735
3736     END;
3737 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3738
3739
3740 -- convenience functions for handling copy_location maps
3741 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3742     SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3743 $$ LANGUAGE SQL;
3744
3745 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3746     DECLARE
3747         table_schema ALIAS FOR $1;
3748         table_name ALIAS FOR $2;
3749         org_shortname ALIAS FOR $3;
3750         org_range ALIAS FOR $4;
3751         make_assertion ALIAS FOR $5;
3752         proceed BOOLEAN;
3753         org INTEGER;
3754         -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3755         -- though we'll still use the passed org for the full path traversal when needed
3756         x_org_found BOOLEAN;
3757         x_org INTEGER;
3758         org_list INTEGER[];
3759         o INTEGER;
3760         row_count NUMERIC;
3761     BEGIN
3762         EXECUTE 'SELECT EXISTS (
3763             SELECT 1
3764             FROM information_schema.columns
3765             WHERE table_schema = $1
3766             AND table_name = $2
3767             and column_name = ''desired_shelf''
3768         )' INTO proceed USING table_schema, table_name;
3769         IF NOT proceed THEN
3770             RAISE EXCEPTION 'Missing column desired_shelf';
3771         END IF;
3772
3773         EXECUTE 'SELECT EXISTS (
3774             SELECT 1
3775             FROM information_schema.columns
3776             WHERE table_schema = $1
3777             AND table_name = $2
3778             and column_name = ''x_org''
3779         )' INTO x_org_found USING table_schema, table_name;
3780
3781         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3782         IF org IS NULL THEN
3783             RAISE EXCEPTION 'Cannot find org by shortname';
3784         END IF;
3785
3786         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3787
3788         EXECUTE 'ALTER TABLE '
3789             || quote_ident(table_name)
3790             || ' DROP COLUMN IF EXISTS x_shelf';
3791         EXECUTE 'ALTER TABLE '
3792             || quote_ident(table_name)
3793             || ' ADD COLUMN x_shelf INTEGER';
3794
3795         IF x_org_found THEN
3796             RAISE INFO 'Found x_org column';
3797             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3798                 || ' SET x_shelf = b.id FROM asset_copy_location b'
3799                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3800                 || ' AND b.owning_lib = x_org'
3801                 || ' AND NOT b.deleted';
3802             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3803                 || ' SET x_shelf = b.id FROM asset.copy_location b'
3804                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3805                 || ' AND b.owning_lib = x_org'
3806                 || ' AND x_shelf IS NULL'
3807                 || ' AND NOT b.deleted';
3808         ELSE
3809             RAISE INFO 'Did not find x_org column';
3810             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3811                 || ' SET x_shelf = b.id FROM asset_copy_location b'
3812                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3813                 || ' AND b.owning_lib = $1'
3814                 || ' AND NOT b.deleted'
3815             USING org;
3816             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3817                 || ' SET x_shelf = b.id FROM asset_copy_location b'
3818                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3819                 || ' AND b.owning_lib = $1'
3820                 || ' AND x_shelf IS NULL'
3821                 || ' AND NOT b.deleted'
3822             USING org;
3823         END IF;
3824
3825         FOREACH o IN ARRAY org_list LOOP
3826             RAISE INFO 'Considering org %', o;
3827             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3828                 || ' SET x_shelf = b.id FROM asset.copy_location b'
3829                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3830                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3831                 || ' AND NOT b.deleted'
3832             USING o;
3833             GET DIAGNOSTICS row_count = ROW_COUNT;
3834             RAISE INFO 'Updated % rows', row_count;
3835         END LOOP;
3836
3837         IF make_assertion THEN
3838             EXECUTE 'SELECT migration_tools.assert(
3839                 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3840                 ''Cannot find a desired location'',
3841                 ''Found all desired locations''
3842             );';
3843         END IF;
3844
3845     END;
3846 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3847
3848 -- convenience functions for handling circmod maps
3849
3850 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3851     DECLARE
3852         table_schema ALIAS FOR $1;
3853         table_name ALIAS FOR $2;
3854         proceed BOOLEAN;
3855     BEGIN
3856         EXECUTE 'SELECT EXISTS (
3857             SELECT 1
3858             FROM information_schema.columns
3859             WHERE table_schema = $1
3860             AND table_name = $2
3861             and column_name = ''desired_circmod''
3862         )' INTO proceed USING table_schema, table_name;
3863         IF NOT proceed THEN
3864             RAISE EXCEPTION 'Missing column desired_circmod';
3865         END IF;
3866
3867         EXECUTE 'ALTER TABLE '
3868             || quote_ident(table_name)
3869             || ' DROP COLUMN IF EXISTS x_circmod';
3870         EXECUTE 'ALTER TABLE '
3871             || quote_ident(table_name)
3872             || ' ADD COLUMN x_circmod TEXT';
3873
3874         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3875             || ' SET x_circmod = code FROM config.circ_modifier b'
3876             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3877
3878         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3879             || ' SET x_circmod = code FROM config.circ_modifier b'
3880             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3881             || ' AND x_circmod IS NULL';
3882
3883         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3884             || ' SET x_circmod = code FROM config.circ_modifier b'
3885             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3886             || ' AND x_circmod IS NULL';
3887
3888         EXECUTE 'SELECT migration_tools.assert(
3889             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3890             ''Cannot find a desired circulation modifier'',
3891             ''Found all desired circulation modifiers''
3892         );';
3893
3894     END;
3895 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3896
3897 -- convenience functions for handling item status maps
3898
3899 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3900     DECLARE
3901         table_schema ALIAS FOR $1;
3902         table_name ALIAS FOR $2;
3903         proceed BOOLEAN;
3904     BEGIN
3905         EXECUTE 'SELECT EXISTS (
3906             SELECT 1
3907             FROM information_schema.columns
3908             WHERE table_schema = $1
3909             AND table_name = $2
3910             and column_name = ''desired_status''
3911         )' INTO proceed USING table_schema, table_name;
3912         IF NOT proceed THEN
3913             RAISE EXCEPTION 'Missing column desired_status';
3914         END IF;
3915
3916         EXECUTE 'ALTER TABLE '
3917             || quote_ident(table_name)
3918             || ' DROP COLUMN IF EXISTS x_status';
3919         EXECUTE 'ALTER TABLE '
3920             || quote_ident(table_name)
3921             || ' ADD COLUMN x_status INTEGER';
3922
3923         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3924             || ' SET x_status = id FROM config.copy_status b'
3925             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3926
3927         EXECUTE 'SELECT migration_tools.assert(
3928             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3929             ''Cannot find a desired copy status'',
3930             ''Found all desired copy statuses''
3931         );';
3932
3933     END;
3934 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3935
3936 -- convenience functions for handling org maps
3937
3938 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3939     DECLARE
3940         table_schema ALIAS FOR $1;
3941         table_name ALIAS FOR $2;
3942         proceed BOOLEAN;
3943     BEGIN
3944         EXECUTE 'SELECT EXISTS (
3945             SELECT 1
3946             FROM information_schema.columns
3947             WHERE table_schema = $1
3948             AND table_name = $2
3949             and column_name = ''desired_org''
3950         )' INTO proceed USING table_schema, table_name;
3951         IF NOT proceed THEN
3952             RAISE EXCEPTION 'Missing column desired_org';
3953         END IF;
3954
3955         EXECUTE 'ALTER TABLE '
3956             || quote_ident(table_name)
3957             || ' DROP COLUMN IF EXISTS x_org';
3958         EXECUTE 'ALTER TABLE '
3959             || quote_ident(table_name)
3960             || ' ADD COLUMN x_org INTEGER';
3961
3962         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3963             || ' SET x_org = b.id FROM actor.org_unit b'
3964             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3965
3966         EXECUTE 'SELECT migration_tools.assert(
3967             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3968             ''Cannot find a desired org unit'',
3969             ''Found all desired org units''
3970         );';
3971
3972     END;
3973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3974
3975 -- convenience function for handling desired_not_migrate
3976
3977 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3978     DECLARE
3979         table_schema ALIAS FOR $1;
3980         table_name ALIAS FOR $2;
3981         proceed BOOLEAN;
3982     BEGIN
3983         EXECUTE 'SELECT EXISTS (
3984             SELECT 1
3985             FROM information_schema.columns
3986             WHERE table_schema = $1
3987             AND table_name = $2
3988             and column_name = ''desired_not_migrate''
3989         )' INTO proceed USING table_schema, table_name;
3990         IF NOT proceed THEN
3991             RAISE EXCEPTION 'Missing column desired_not_migrate';
3992         END IF;
3993
3994         EXECUTE 'ALTER TABLE '
3995             || quote_ident(table_name)
3996             || ' DROP COLUMN IF EXISTS x_migrate';
3997         EXECUTE 'ALTER TABLE '
3998             || quote_ident(table_name)
3999             || ' ADD COLUMN x_migrate BOOLEAN';
4000
4001         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4002             || ' SET x_migrate = CASE'
4003             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4004             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4005             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4006             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4007             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4008             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4009             || ' END';
4010
4011         EXECUTE 'SELECT migration_tools.assert(
4012             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4013             ''Not all desired_not_migrate values understood'',
4014             ''All desired_not_migrate values understood''
4015         );';
4016
4017     END;
4018 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4019
4020 -- convenience function for handling desired_not_migrate
4021
4022 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4023     DECLARE
4024         table_schema ALIAS FOR $1;
4025         table_name ALIAS FOR $2;
4026         proceed BOOLEAN;
4027     BEGIN
4028         EXECUTE 'SELECT EXISTS (
4029             SELECT 1
4030             FROM information_schema.columns
4031             WHERE table_schema = $1
4032             AND table_name = $2
4033             and column_name = ''desired_barred_or_blocked''
4034         )' INTO proceed USING table_schema, table_name;
4035         IF NOT proceed THEN
4036             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4037         END IF;
4038
4039         EXECUTE 'ALTER TABLE '
4040             || quote_ident(table_name)
4041             || ' DROP COLUMN IF EXISTS x_barred';
4042         EXECUTE 'ALTER TABLE '
4043             || quote_ident(table_name)
4044             || ' ADD COLUMN x_barred BOOLEAN';
4045
4046         EXECUTE 'ALTER TABLE '
4047             || quote_ident(table_name)
4048             || ' DROP COLUMN IF EXISTS x_blocked';
4049         EXECUTE 'ALTER TABLE '
4050             || quote_ident(table_name)
4051             || ' ADD COLUMN x_blocked BOOLEAN';
4052
4053         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4054             || ' SET x_barred = CASE'
4055             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4056             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4057             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4058             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4059             || ' END';
4060
4061         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4062             || ' SET x_blocked = CASE'
4063             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4064             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4065             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4066             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4067             || ' END';
4068
4069         EXECUTE 'SELECT migration_tools.assert(
4070             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4071             ''Not all desired_barred_or_blocked values understood'',
4072             ''All desired_barred_or_blocked values understood''
4073         );';
4074
4075     END;
4076 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4077
4078 -- convenience function for handling desired_profile
4079
4080 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4081     DECLARE
4082         table_schema ALIAS FOR $1;
4083         table_name ALIAS FOR $2;
4084         proceed BOOLEAN;
4085     BEGIN
4086         EXECUTE 'SELECT EXISTS (
4087             SELECT 1
4088             FROM information_schema.columns
4089             WHERE table_schema = $1
4090             AND table_name = $2
4091             and column_name = ''desired_profile''
4092         )' INTO proceed USING table_schema, table_name;
4093         IF NOT proceed THEN
4094             RAISE EXCEPTION 'Missing column desired_profile';
4095         END IF;
4096
4097         EXECUTE 'ALTER TABLE '
4098             || quote_ident(table_name)
4099             || ' DROP COLUMN IF EXISTS x_profile';
4100         EXECUTE 'ALTER TABLE '
4101             || quote_ident(table_name)
4102             || ' ADD COLUMN x_profile INTEGER';
4103
4104         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4105             || ' SET x_profile = b.id FROM permission.grp_tree b'
4106             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4107
4108         EXECUTE 'SELECT migration_tools.assert(
4109             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4110             ''Cannot find a desired profile'',
4111             ''Found all desired profiles''
4112         );';
4113
4114     END;
4115 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4116
4117 -- convenience function for handling desired actor stat cats
4118
4119 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4120     DECLARE
4121         table_schema ALIAS FOR $1;
4122         table_name ALIAS FOR $2;
4123         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4124         org_shortname ALIAS FOR $4;
4125         proceed BOOLEAN;
4126         org INTEGER;
4127         org_list INTEGER[];
4128         sc TEXT;
4129         sce TEXT;
4130     BEGIN
4131
4132         SELECT 'desired_sc' || field_suffix INTO sc;
4133         SELECT 'desired_sce' || field_suffix INTO sce;
4134
4135         EXECUTE 'SELECT EXISTS (
4136             SELECT 1
4137             FROM information_schema.columns
4138             WHERE table_schema = $1
4139             AND table_name = $2
4140             and column_name = $3
4141         )' INTO proceed USING table_schema, table_name, sc;
4142         IF NOT proceed THEN
4143             RAISE EXCEPTION 'Missing column %', sc;
4144         END IF;
4145         EXECUTE 'SELECT EXISTS (
4146             SELECT 1
4147             FROM information_schema.columns
4148             WHERE table_schema = $1
4149             AND table_name = $2
4150             and column_name = $3
4151         )' INTO proceed USING table_schema, table_name, sce;
4152         IF NOT proceed THEN
4153             RAISE EXCEPTION 'Missing column %', sce;
4154         END IF;
4155
4156         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4157         IF org IS NULL THEN
4158             RAISE EXCEPTION 'Cannot find org by shortname';
4159         END IF;
4160         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4161
4162         -- caller responsible for their own truncates though we try to prevent duplicates
4163         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4164             SELECT DISTINCT
4165                  $1
4166                 ,BTRIM('||sc||')
4167             FROM
4168                 ' || quote_ident(table_name) || '
4169             WHERE
4170                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4171                 AND NOT EXISTS (
4172                     SELECT id
4173                     FROM actor.stat_cat
4174                     WHERE owner = ANY ($2)
4175                     AND name = BTRIM('||sc||')
4176                 )
4177                 AND NOT EXISTS (
4178                     SELECT id
4179                     FROM actor_stat_cat
4180                     WHERE owner = ANY ($2)
4181                     AND name = BTRIM('||sc||')
4182                 )
4183             ORDER BY 2;'
4184         USING org, org_list;
4185
4186         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4187             SELECT DISTINCT
4188                 COALESCE(
4189                     (SELECT id
4190                         FROM actor.stat_cat
4191                         WHERE owner = ANY ($2)
4192                         AND BTRIM('||sc||') = BTRIM(name))
4193                    ,(SELECT id
4194                         FROM actor_stat_cat
4195                         WHERE owner = ANY ($2)
4196                         AND BTRIM('||sc||') = BTRIM(name))
4197                 )
4198                 ,$1
4199                 ,BTRIM('||sce||')
4200             FROM
4201                 ' || quote_ident(table_name) || '
4202             WHERE
4203                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4204                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4205                 AND NOT EXISTS (
4206                     SELECT id
4207                     FROM actor.stat_cat_entry
4208                     WHERE stat_cat = (
4209                         SELECT id
4210                         FROM actor.stat_cat
4211                         WHERE owner = ANY ($2)
4212                         AND BTRIM('||sc||') = BTRIM(name)
4213                     ) AND value = BTRIM('||sce||')
4214                     AND owner = ANY ($2)
4215                 )
4216                 AND NOT EXISTS (
4217                     SELECT id
4218                     FROM actor_stat_cat_entry
4219                     WHERE stat_cat = (
4220                         SELECT id
4221                         FROM actor_stat_cat
4222                         WHERE owner = ANY ($2)
4223                         AND BTRIM('||sc||') = BTRIM(name)
4224                     ) AND value = BTRIM('||sce||')
4225                     AND owner = ANY ($2)
4226                 )
4227             ORDER BY 1,3;'
4228         USING org, org_list;
4229     END;
4230 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4231
4232 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4233     DECLARE
4234         table_schema ALIAS FOR $1;
4235         table_name ALIAS FOR $2;
4236         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4237         org_shortname ALIAS FOR $4;
4238         proceed BOOLEAN;
4239         org INTEGER;
4240         org_list INTEGER[];
4241         o INTEGER;
4242         sc TEXT;
4243         sce TEXT;
4244     BEGIN
4245         SELECT 'desired_sc' || field_suffix INTO sc;
4246         SELECT 'desired_sce' || field_suffix INTO sce;
4247         EXECUTE 'SELECT EXISTS (
4248             SELECT 1
4249             FROM information_schema.columns
4250             WHERE table_schema = $1
4251             AND table_name = $2
4252             and column_name = $3
4253         )' INTO proceed USING table_schema, table_name, sc;
4254         IF NOT proceed THEN
4255             RAISE EXCEPTION 'Missing column %', sc;
4256         END IF;
4257         EXECUTE 'SELECT EXISTS (
4258             SELECT 1
4259             FROM information_schema.columns
4260             WHERE table_schema = $1
4261             AND table_name = $2
4262             and column_name = $3
4263         )' INTO proceed USING table_schema, table_name, sce;
4264         IF NOT proceed THEN
4265             RAISE EXCEPTION 'Missing column %', sce;
4266         END IF;
4267
4268         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4269         IF org IS NULL THEN
4270             RAISE EXCEPTION 'Cannot find org by shortname';
4271         END IF;
4272
4273         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4274
4275         EXECUTE 'ALTER TABLE '
4276             || quote_ident(table_name)
4277             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4278         EXECUTE 'ALTER TABLE '
4279             || quote_ident(table_name)
4280             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4281         EXECUTE 'ALTER TABLE '
4282             || quote_ident(table_name)
4283             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4284         EXECUTE 'ALTER TABLE '
4285             || quote_ident(table_name)
4286             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4287
4288
4289         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4290             SET
4291                 x_sc' || field_suffix || ' = id
4292             FROM
4293                 (SELECT id, name, owner FROM actor_stat_cat
4294                     UNION SELECT id, name, owner FROM actor.stat_cat) u
4295             WHERE
4296                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4297                 AND u.owner = ANY ($1);'
4298         USING org_list;
4299
4300         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4301             SET
4302                 x_sce' || field_suffix || ' = id
4303             FROM
4304                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4305                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4306             WHERE
4307                     u.stat_cat = x_sc' || field_suffix || '
4308                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4309                 AND u.owner = ANY ($1);'
4310         USING org_list;
4311
4312         EXECUTE 'SELECT migration_tools.assert(
4313             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4314             ''Cannot find a desired stat cat'',
4315             ''Found all desired stat cats''
4316         );';
4317
4318         EXECUTE 'SELECT migration_tools.assert(
4319             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4320             ''Cannot find a desired stat cat entry'',
4321             ''Found all desired stat cat entries''
4322         );';
4323
4324     END;
4325 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4326
4327 -- convenience functions for adding shelving locations
4328 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4329 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4330 DECLARE
4331     return_id   INT;
4332     d           INT;
4333     cur_id      INT;
4334 BEGIN
4335     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4336     WHILE d >= 0
4337     LOOP
4338         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4339         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4340         IF return_id IS NOT NULL THEN
4341                 RETURN return_id;
4342         END IF;
4343         d := d - 1;
4344     END LOOP;
4345
4346     RETURN NULL;
4347 END
4348 $$ LANGUAGE plpgsql;
4349
4350 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4351
4352 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4353 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4354 DECLARE
4355     return_id   INT;
4356     d           INT;
4357     cur_id      INT;
4358 BEGIN
4359     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4360     WHILE d >= 0
4361     LOOP
4362         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4363
4364         SELECT INTO return_id id FROM
4365             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4366             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4367         IF return_id IS NOT NULL THEN
4368                 RETURN return_id;
4369         END IF;
4370         d := d - 1;
4371     END LOOP;
4372
4373     RETURN NULL;
4374 END
4375 $$ LANGUAGE plpgsql;
4376
4377 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4378 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4379     RETURNS BOOLEAN AS
4380 $BODY$
4381 DECLARE
4382         marc_xml        TEXT;
4383         new_marc        TEXT;
4384 BEGIN
4385         SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4386
4387         SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4388         UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4389
4390         RETURN true;
4391 END;
4392 $BODY$ LANGUAGE plpgsql;
4393
4394 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4395 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4396  RETURNS TEXT
4397  LANGUAGE plperlu
4398 AS $function$
4399 use strict;
4400 use warnings;
4401
4402 use MARC::Record;
4403 use MARC::File::XML (BinaryEncoding => 'utf8');
4404
4405 binmode(STDERR, ':bytes');
4406 binmode(STDOUT, ':utf8');
4407 binmode(STDERR, ':utf8');
4408
4409 my $marc_xml = shift;
4410 my $new_9_to_set = shift;
4411 my $force = shift;
4412
4413 $marc_xml =~ s/(<leader>.........)./${1}a/;
4414
4415 eval {
4416     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4417 };
4418 if ($@) {
4419     #elog("could not parse $bibid: $@\n");
4420     import MARC::File::XML (BinaryEncoding => 'utf8');
4421     return $marc_xml;
4422 }
4423
4424 my @uris = $marc_xml->field('856');
4425 return $marc_xml->as_xml_record() unless @uris;
4426
4427 foreach my $field (@uris) {
4428     my $ind1 = $field->indicator('1');
4429     if (!defined $ind1) { next; }
4430     if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4431         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4432     my $ind2 = $field->indicator('2');
4433     if (!defined $ind2) { next; }
4434     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4435     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4436     $field->add_subfields( '9' => $new_9_to_set );
4437 }
4438
4439 return $marc_xml->as_xml_record();
4440
4441 $function$;
4442
4443 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4444 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4445  RETURNS TEXT
4446  LANGUAGE plperlu
4447 AS $function$
4448 use strict;
4449 use warnings;
4450
4451 use MARC::Record;
4452 use MARC::File::XML (BinaryEncoding => 'utf8');
4453
4454 binmode(STDERR, ':bytes');
4455 binmode(STDOUT, ':utf8');
4456 binmode(STDERR, ':utf8');
4457
4458 my $marc_xml = shift;
4459 my $qualifying_match = shift;
4460 my $new_9_to_set = shift;
4461 my $force = shift;
4462
4463 $marc_xml =~ s/(<leader>.........)./${1}a/;
4464
4465 eval {
4466     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4467 };
4468 if ($@) {
4469     #elog("could not parse $bibid: $@\n");
4470     import MARC::File::XML (BinaryEncoding => 'utf8');
4471     return $marc_xml;
4472 }
4473
4474 my @uris = $marc_xml->field('856');
4475 return $marc_xml->as_xml_record() unless @uris;
4476
4477 foreach my $field (@uris) {
4478     if ($field->as_string() =~ qr/$qualifying_match/) {
4479         my $ind1 = $field->indicator('1');
4480         if (!defined $ind1) { next; }
4481         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4482         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4483         my $ind2 = $field->indicator('2');
4484         if (!defined $ind2) { next; }
4485         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4486         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4487         $field->add_subfields( '9' => $new_9_to_set );
4488     }
4489 }
4490
4491 return $marc_xml->as_xml_record();
4492
4493 $function$;
4494
4495 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4496 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4497  RETURNS TEXT
4498  LANGUAGE plperlu
4499 AS $function$
4500 use strict;
4501 use warnings;
4502
4503 use MARC::Record;
4504 use MARC::File::XML (BinaryEncoding => 'utf8');
4505
4506 binmode(STDERR, ':bytes');
4507 binmode(STDOUT, ':utf8');
4508 binmode(STDERR, ':utf8');
4509
4510 my $marc_xml = shift;
4511 my $substring_old_value = shift;
4512 my $new_value = shift;
4513 my $fix_indicators = shift;
4514
4515 $marc_xml =~ s/(<leader>.........)./${1}a/;
4516
4517 eval {
4518     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4519 };
4520 if ($@) {
4521     #elog("could not parse $bibid: $@\n");
4522     import MARC::File::XML (BinaryEncoding => 'utf8');
4523     return $marc_xml;
4524 }
4525
4526 my @uris = $marc_xml->field('856');
4527 return $marc_xml->as_xml_record() unless @uris;
4528
4529 foreach my $field (@uris) {
4530     my $ind1 = $field->indicator('1');
4531     if (defined $ind1) {
4532             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4533             $field->set_indicator(1,'4');
4534         }
4535     }
4536     my $ind2 = $field->indicator('2');
4537     if (defined $ind2) {
4538         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4539             $field->set_indicator(2,'0');
4540         }
4541     }
4542     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4543         $field->delete_subfield('9');
4544         $field->add_subfields( '9' => $new_value );
4545     }
4546     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4547 }
4548
4549 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4550 $marc_xml->insert_fields_ordered( values( %hash ) );
4551
4552 return $marc_xml->as_xml_record();
4553
4554 $function$;
4555
4556 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4557 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4558  RETURNS TEXT
4559  LANGUAGE plperlu
4560 AS $function$
4561 use strict;
4562 use warnings;
4563
4564 use MARC::Record;
4565 use MARC::File::XML (BinaryEncoding => 'utf8');
4566
4567 binmode(STDERR, ':bytes');
4568 binmode(STDOUT, ':utf8');
4569 binmode(STDERR, ':utf8');
4570
4571 my $marc_xml = shift;
4572 my $qualifying_match = shift;
4573 my $substring_old_value = shift;
4574 my $new_value = shift;
4575 my $fix_indicators = shift;
4576
4577 $marc_xml =~ s/(<leader>.........)./${1}a/;
4578
4579 eval {
4580     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4581 };
4582 if ($@) {
4583     #elog("could not parse $bibid: $@\n");
4584     import MARC::File::XML (BinaryEncoding => 'utf8');
4585     return $marc_xml;
4586 }
4587
4588 my @unqualified_uris = $marc_xml->field('856');
4589 my @uris = ();
4590 foreach my $field (@unqualified_uris) {
4591     if ($field->as_string() =~ qr/$qualifying_match/) {
4592         push @uris, $field;
4593     }
4594 }
4595 return $marc_xml->as_xml_record() unless @uris;
4596
4597 foreach my $field (@uris) {
4598     my $ind1 = $field->indicator('1');
4599     if (defined $ind1) {
4600             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4601             $field->set_indicator(1,'4');
4602         }
4603     }
4604     my $ind2 = $field->indicator('2');
4605     if (defined $ind2) {
4606         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4607             $field->set_indicator(2,'0');
4608         }
4609     }
4610     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4611         $field->delete_subfield('9');
4612         $field->add_subfields( '9' => $new_value );
4613     }
4614     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4615 }
4616
4617 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4618 $marc_xml->insert_fields_ordered( values( %hash ) );
4619
4620 return $marc_xml->as_xml_record();
4621
4622 $function$;
4623
4624 -- strip marc tag
4625 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4626 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4627  RETURNS TEXT
4628  LANGUAGE plperlu
4629 AS $function$
4630 use strict;
4631 use warnings;
4632
4633 use MARC::Record;
4634 use MARC::File::XML (BinaryEncoding => 'utf8');
4635
4636 binmode(STDERR, ':bytes');
4637 binmode(STDOUT, ':utf8');
4638 binmode(STDERR, ':utf8');
4639
4640 my $marc_xml = shift;
4641 my $tag = shift;
4642
4643 $marc_xml =~ s/(<leader>.........)./${1}a/;
4644
4645 eval {
4646     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4647 };
4648 if ($@) {
4649     #elog("could not parse $bibid: $@\n");
4650     import MARC::File::XML (BinaryEncoding => 'utf8');
4651     return $marc_xml;
4652 }
4653
4654 my @fields = $marc_xml->field($tag);
4655 return $marc_xml->as_xml_record() unless @fields;
4656
4657 $marc_xml->delete_fields(@fields);
4658
4659 return $marc_xml->as_xml_record();
4660
4661 $function$;
4662
4663 -- convenience function for linking to the item staging table
4664
4665 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4666     DECLARE
4667         table_schema ALIAS FOR $1;
4668         table_name ALIAS FOR $2;
4669         foreign_column_name ALIAS FOR $3;
4670         main_column_name ALIAS FOR $4;
4671         btrim_desired ALIAS FOR $5;
4672         proceed BOOLEAN;
4673     BEGIN
4674         EXECUTE 'SELECT EXISTS (
4675             SELECT 1
4676             FROM information_schema.columns
4677             WHERE table_schema = $1
4678             AND table_name = $2
4679             and column_name = $3
4680         )' INTO proceed USING table_schema, table_name, foreign_column_name;
4681         IF NOT proceed THEN
4682             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4683         END IF;
4684
4685         EXECUTE 'SELECT EXISTS (
4686             SELECT 1
4687             FROM information_schema.columns
4688             WHERE table_schema = $1
4689             AND table_name = ''asset_copy_legacy''
4690             and column_name = $2
4691         )' INTO proceed USING table_schema, main_column_name;
4692         IF NOT proceed THEN
4693             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4694         END IF;
4695
4696         EXECUTE 'ALTER TABLE '
4697             || quote_ident(table_name)
4698             || ' DROP COLUMN IF EXISTS x_item';
4699         EXECUTE 'ALTER TABLE '
4700             || quote_ident(table_name)
4701             || ' ADD COLUMN x_item BIGINT';
4702
4703         IF btrim_desired THEN
4704             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4705                 || ' SET x_item = b.id FROM asset_copy_legacy b'
4706                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4707                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4708         ELSE
4709             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4710                 || ' SET x_item = b.id FROM asset_copy_legacy b'
4711                 || ' WHERE a.' || quote_ident(foreign_column_name)
4712                 || ' = b.' || quote_ident(main_column_name);
4713         END IF;
4714
4715         --EXECUTE 'SELECT migration_tools.assert(
4716         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4717         --    ''Cannot link every barcode'',
4718         --    ''Every barcode linked''
4719         --);';
4720
4721     END;
4722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4723
4724 -- convenience function for linking to the user staging table
4725
4726 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4727     DECLARE
4728         table_schema ALIAS FOR $1;
4729         table_name ALIAS FOR $2;
4730         foreign_column_name ALIAS FOR $3;
4731         main_column_name ALIAS FOR $4;
4732         btrim_desired ALIAS FOR $5;
4733         proceed BOOLEAN;
4734     BEGIN
4735         EXECUTE 'SELECT EXISTS (
4736             SELECT 1
4737             FROM information_schema.columns
4738             WHERE table_schema = $1
4739             AND table_name = $2
4740             and column_name = $3
4741         )' INTO proceed USING table_schema, table_name, foreign_column_name;
4742         IF NOT proceed THEN
4743             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4744         END IF;
4745
4746         EXECUTE 'SELECT EXISTS (
4747             SELECT 1
4748             FROM information_schema.columns
4749             WHERE table_schema = $1
4750             AND table_name = ''actor_usr_legacy''
4751             and column_name = $2
4752         )' INTO proceed USING table_schema, main_column_name;
4753         IF NOT proceed THEN
4754             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4755         END IF;
4756
4757         EXECUTE 'ALTER TABLE '
4758             || quote_ident(table_name)
4759             || ' DROP COLUMN IF EXISTS x_user';
4760         EXECUTE 'ALTER TABLE '
4761             || quote_ident(table_name)
4762             || ' ADD COLUMN x_user INTEGER';
4763
4764         IF btrim_desired THEN
4765             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4766                 || ' SET x_user = b.id FROM actor_usr_legacy b'
4767                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4768                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4769         ELSE
4770             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4771                 || ' SET x_user = b.id FROM actor_usr_legacy b'
4772                 || ' WHERE a.' || quote_ident(foreign_column_name)
4773                 || ' = b.' || quote_ident(main_column_name);
4774         END IF;
4775
4776         --EXECUTE 'SELECT migration_tools.assert(
4777         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4778         --    ''Cannot link every barcode'',
4779         --    ''Every barcode linked''
4780         --);';
4781
4782     END;
4783 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4784
4785 -- convenience function for linking two tables
4786 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4787 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4788     DECLARE
4789         table_schema ALIAS FOR $1;
4790         table_a ALIAS FOR $2;
4791         column_a ALIAS FOR $3;
4792         table_b ALIAS FOR $4;
4793         column_b ALIAS FOR $5;
4794         column_x ALIAS FOR $6;
4795         btrim_desired ALIAS FOR $7;
4796         proceed BOOLEAN;
4797     BEGIN
4798         EXECUTE 'SELECT EXISTS (
4799             SELECT 1
4800             FROM information_schema.columns
4801             WHERE table_schema = $1
4802             AND table_name = $2
4803             and column_name = $3
4804         )' INTO proceed USING table_schema, table_a, column_a;
4805         IF NOT proceed THEN
4806             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4807         END IF;
4808
4809         EXECUTE 'SELECT EXISTS (
4810             SELECT 1
4811             FROM information_schema.columns
4812             WHERE table_schema = $1
4813             AND table_name = $2
4814             and column_name = $3
4815         )' INTO proceed USING table_schema, table_b, column_b;
4816         IF NOT proceed THEN
4817             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4818         END IF;
4819
4820         EXECUTE 'ALTER TABLE '
4821             || quote_ident(table_b)
4822             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4823         EXECUTE 'ALTER TABLE '
4824             || quote_ident(table_b)
4825             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4826
4827         IF btrim_desired THEN
4828             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4829                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4830                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4831                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4832         ELSE
4833             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4834                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4835                 || ' WHERE a.' || quote_ident(column_a)
4836                 || ' = b.' || quote_ident(column_b);
4837         END IF;
4838
4839     END;
4840 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4841
4842 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4843 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4844 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4845     DECLARE
4846         table_schema ALIAS FOR $1;
4847         table_a ALIAS FOR $2;
4848         column_a ALIAS FOR $3;
4849         table_b ALIAS FOR $4;
4850         column_b ALIAS FOR $5;
4851         column_w ALIAS FOR $6;
4852         column_x ALIAS FOR $7;
4853         btrim_desired ALIAS FOR $8;
4854         proceed BOOLEAN;
4855     BEGIN
4856         EXECUTE 'SELECT EXISTS (
4857             SELECT 1
4858             FROM information_schema.columns
4859             WHERE table_schema = $1
4860             AND table_name = $2
4861             and column_name = $3
4862         )' INTO proceed USING table_schema, table_a, column_a;
4863         IF NOT proceed THEN
4864             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4865         END IF;
4866
4867         EXECUTE 'SELECT EXISTS (
4868             SELECT 1
4869             FROM information_schema.columns
4870             WHERE table_schema = $1
4871             AND table_name = $2
4872             and column_name = $3
4873         )' INTO proceed USING table_schema, table_b, column_b;
4874         IF NOT proceed THEN
4875             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4876         END IF;
4877
4878         EXECUTE 'ALTER TABLE '
4879             || quote_ident(table_b)
4880             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4881         EXECUTE 'ALTER TABLE '
4882             || quote_ident(table_b)
4883             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4884
4885         IF btrim_desired THEN
4886             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4887                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4888                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4889                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4890         ELSE
4891             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4892                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4893                 || ' WHERE a.' || quote_ident(column_a)
4894                 || ' = b.' || quote_ident(column_b);
4895         END IF;
4896
4897     END;
4898 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4899
4900 -- 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
4901 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4902 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4903     DECLARE
4904         table_schema ALIAS FOR $1;
4905         table_a ALIAS FOR $2;
4906         column_a ALIAS FOR $3;
4907         table_b ALIAS FOR $4;
4908         column_b ALIAS FOR $5;
4909         column_w ALIAS FOR $6;
4910         column_x ALIAS FOR $7;
4911         proceed BOOLEAN;
4912     BEGIN
4913         EXECUTE 'SELECT EXISTS (
4914             SELECT 1
4915             FROM information_schema.columns
4916             WHERE table_schema = $1
4917             AND table_name = $2
4918             and column_name = $3
4919         )' INTO proceed USING table_schema, table_a, column_a;
4920         IF NOT proceed THEN
4921             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4922         END IF;
4923
4924         EXECUTE 'SELECT EXISTS (
4925             SELECT 1
4926             FROM information_schema.columns
4927             WHERE table_schema = $1
4928             AND table_name = $2
4929             and column_name = $3
4930         )' INTO proceed USING table_schema, table_b, column_b;
4931         IF NOT proceed THEN
4932             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4933         END IF;
4934
4935         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4936             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4937             || ' WHERE a.' || quote_ident(column_a)
4938             || ' = b.' || quote_ident(column_b);
4939
4940     END;
4941 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4942
4943 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4944     DECLARE
4945         table_schema ALIAS FOR $1;
4946         table_a ALIAS FOR $2;
4947         column_a ALIAS FOR $3;
4948         table_b ALIAS FOR $4;
4949         column_b ALIAS FOR $5;
4950         column_w ALIAS FOR $6;
4951         column_x ALIAS FOR $7;
4952         proceed BOOLEAN;
4953     BEGIN
4954         EXECUTE 'SELECT EXISTS (
4955             SELECT 1
4956             FROM information_schema.columns
4957             WHERE table_schema = $1
4958             AND table_name = $2
4959             and column_name = $3
4960         )' INTO proceed USING table_schema, table_a, column_a;
4961         IF NOT proceed THEN
4962             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4963         END IF;
4964
4965         EXECUTE 'SELECT EXISTS (
4966             SELECT 1
4967             FROM information_schema.columns
4968             WHERE table_schema = $1
4969             AND table_name = $2
4970             and column_name = $3
4971         )' INTO proceed USING table_schema, table_b, column_b;
4972         IF NOT proceed THEN
4973             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4974         END IF;
4975
4976         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4977             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4978             || ' WHERE a.' || quote_ident(column_a)
4979             || ' = b.' || quote_ident(column_b)
4980             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4981
4982     END;
4983 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4984
4985 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4986     DECLARE
4987         table_schema ALIAS FOR $1;
4988         table_a ALIAS FOR $2;
4989         column_a ALIAS FOR $3;
4990         table_b ALIAS FOR $4;
4991         column_b ALIAS FOR $5;
4992         column_w ALIAS FOR $6;
4993         column_x ALIAS FOR $7;
4994         proceed BOOLEAN;
4995     BEGIN
4996         EXECUTE 'SELECT EXISTS (
4997             SELECT 1
4998             FROM information_schema.columns
4999             WHERE table_schema = $1
5000             AND table_name = $2
5001             and column_name = $3
5002         )' INTO proceed USING table_schema, table_a, column_a;
5003         IF NOT proceed THEN
5004             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5005         END IF;
5006
5007         EXECUTE 'SELECT EXISTS (
5008             SELECT 1
5009             FROM information_schema.columns
5010             WHERE table_schema = $1
5011             AND table_name = $2
5012             and column_name = $3
5013         )' INTO proceed USING table_schema, table_b, column_b;
5014         IF NOT proceed THEN
5015             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5016         END IF;
5017
5018         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5019             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5020             || ' WHERE a.' || quote_ident(column_a)
5021             || ' = b.' || quote_ident(column_b)
5022             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5023
5024     END;
5025 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5026
5027 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5028     DECLARE
5029         table_schema ALIAS FOR $1;
5030         table_a ALIAS FOR $2;
5031         column_a ALIAS FOR $3;
5032         table_b ALIAS FOR $4;
5033         column_b ALIAS FOR $5;
5034         column_w ALIAS FOR $6;
5035         column_x ALIAS FOR $7;
5036         proceed BOOLEAN;
5037     BEGIN
5038         EXECUTE 'SELECT EXISTS (
5039             SELECT 1
5040             FROM information_schema.columns
5041             WHERE table_schema = $1
5042             AND table_name = $2
5043             and column_name = $3
5044         )' INTO proceed USING table_schema, table_a, column_a;
5045         IF NOT proceed THEN
5046             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5047         END IF;
5048
5049         EXECUTE 'SELECT EXISTS (
5050             SELECT 1
5051             FROM information_schema.columns
5052             WHERE table_schema = $1
5053             AND table_name = $2
5054             and column_name = $3
5055         )' INTO proceed USING table_schema, table_b, column_b;
5056         IF NOT proceed THEN
5057             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5058         END IF;
5059
5060         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5061             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5062             || ' WHERE a.' || quote_ident(column_a)
5063             || ' = b.' || quote_ident(column_b)
5064             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5065
5066     END;
5067 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5068
5069 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5070     DECLARE
5071         table_schema ALIAS FOR $1;
5072         table_a ALIAS FOR $2;
5073         column_a ALIAS FOR $3;
5074         table_b ALIAS FOR $4;
5075         column_b ALIAS FOR $5;
5076         column_w ALIAS FOR $6;
5077         column_x ALIAS FOR $7;
5078         proceed BOOLEAN;
5079     BEGIN
5080         EXECUTE 'SELECT EXISTS (
5081             SELECT 1
5082             FROM information_schema.columns
5083             WHERE table_schema = $1
5084             AND table_name = $2
5085             and column_name = $3
5086         )' INTO proceed USING table_schema, table_a, column_a;
5087         IF NOT proceed THEN
5088             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5089         END IF;
5090
5091         EXECUTE 'SELECT EXISTS (
5092             SELECT 1
5093             FROM information_schema.columns
5094             WHERE table_schema = $1
5095             AND table_name = $2
5096             and column_name = $3
5097         )' INTO proceed USING table_schema, table_b, column_b;
5098         IF NOT proceed THEN
5099             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5100         END IF;
5101
5102         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5103             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5104             || ' WHERE a.' || quote_ident(column_a)
5105             || ' = b.' || quote_ident(column_b)
5106             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5107
5108     END;
5109 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5110
5111 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5112     DECLARE
5113         table_schema ALIAS FOR $1;
5114         table_a ALIAS FOR $2;
5115         column_a ALIAS FOR $3;
5116         table_b ALIAS FOR $4;
5117         column_b ALIAS FOR $5;
5118         column_w ALIAS FOR $6;
5119         column_x ALIAS FOR $7;
5120         proceed BOOLEAN;
5121     BEGIN
5122         EXECUTE 'SELECT EXISTS (
5123             SELECT 1
5124             FROM information_schema.columns
5125             WHERE table_schema = $1
5126             AND table_name = $2
5127             and column_name = $3
5128         )' INTO proceed USING table_schema, table_a, column_a;
5129         IF NOT proceed THEN
5130             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5131         END IF;
5132
5133         EXECUTE 'SELECT EXISTS (
5134             SELECT 1
5135             FROM information_schema.columns
5136             WHERE table_schema = $1
5137             AND table_name = $2
5138             and column_name = $3
5139         )' INTO proceed USING table_schema, table_b, column_b;
5140         IF NOT proceed THEN
5141             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5142         END IF;
5143
5144         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5145             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5146             || ' WHERE a.' || quote_ident(column_a)
5147             || ' = b.' || quote_ident(column_b)
5148             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5149
5150     END;
5151 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5152
5153 -- convenience function for handling desired asset stat cats
5154
5155 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5156     DECLARE
5157         table_schema ALIAS FOR $1;
5158         table_name ALIAS FOR $2;
5159         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5160         org_shortname ALIAS FOR $4;
5161         proceed BOOLEAN;
5162         org INTEGER;
5163         org_list INTEGER[];
5164         sc TEXT;
5165         sce TEXT;
5166     BEGIN
5167
5168         SELECT 'desired_sc' || field_suffix INTO sc;
5169         SELECT 'desired_sce' || field_suffix INTO sce;
5170
5171         EXECUTE 'SELECT EXISTS (
5172             SELECT 1
5173             FROM information_schema.columns
5174             WHERE table_schema = $1
5175             AND table_name = $2
5176             and column_name = $3
5177         )' INTO proceed USING table_schema, table_name, sc;
5178         IF NOT proceed THEN
5179             RAISE EXCEPTION 'Missing column %', sc;
5180         END IF;
5181         EXECUTE 'SELECT EXISTS (
5182             SELECT 1
5183             FROM information_schema.columns
5184             WHERE table_schema = $1
5185             AND table_name = $2
5186             and column_name = $3
5187         )' INTO proceed USING table_schema, table_name, sce;
5188         IF NOT proceed THEN
5189             RAISE EXCEPTION 'Missing column %', sce;
5190         END IF;
5191
5192         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5193         IF org IS NULL THEN
5194             RAISE EXCEPTION 'Cannot find org by shortname';
5195         END IF;
5196         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5197
5198         -- caller responsible for their own truncates though we try to prevent duplicates
5199         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5200             SELECT DISTINCT
5201                  $1
5202                 ,BTRIM('||sc||')
5203             FROM
5204                 ' || quote_ident(table_name) || '
5205             WHERE
5206                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5207                 AND NOT EXISTS (
5208                     SELECT id
5209                     FROM asset.stat_cat
5210                     WHERE owner = ANY ($2)
5211                     AND name = BTRIM('||sc||')
5212                 )
5213                 AND NOT EXISTS (
5214                     SELECT id
5215                     FROM asset_stat_cat
5216                     WHERE owner = ANY ($2)
5217                     AND name = BTRIM('||sc||')
5218                 )
5219             ORDER BY 2;'
5220         USING org, org_list;
5221
5222         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5223             SELECT DISTINCT
5224                 COALESCE(
5225                     (SELECT id
5226                         FROM asset.stat_cat
5227                         WHERE owner = ANY ($2)
5228                         AND BTRIM('||sc||') = BTRIM(name))
5229                    ,(SELECT id
5230                         FROM asset_stat_cat
5231                         WHERE owner = ANY ($2)
5232                         AND BTRIM('||sc||') = BTRIM(name))
5233                 )
5234                 ,$1
5235                 ,BTRIM('||sce||')
5236             FROM
5237                 ' || quote_ident(table_name) || '
5238             WHERE
5239                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5240                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5241                 AND NOT EXISTS (
5242                     SELECT id
5243                     FROM asset.stat_cat_entry
5244                     WHERE stat_cat = (
5245                         SELECT id
5246                         FROM asset.stat_cat
5247                         WHERE owner = ANY ($2)
5248                         AND BTRIM('||sc||') = BTRIM(name)
5249                     ) AND value = BTRIM('||sce||')
5250                     AND owner = ANY ($2)
5251                 )
5252                 AND NOT EXISTS (
5253                     SELECT id
5254                     FROM asset_stat_cat_entry
5255                     WHERE stat_cat = (
5256                         SELECT id
5257                         FROM asset_stat_cat
5258                         WHERE owner = ANY ($2)
5259                         AND BTRIM('||sc||') = BTRIM(name)
5260                     ) AND value = BTRIM('||sce||')
5261                     AND owner = ANY ($2)
5262                 )
5263             ORDER BY 1,3;'
5264         USING org, org_list;
5265     END;
5266 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5267
5268 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5269     DECLARE
5270         table_schema ALIAS FOR $1;
5271         table_name ALIAS FOR $2;
5272         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5273         org_shortname ALIAS FOR $4;
5274         proceed BOOLEAN;
5275         org INTEGER;
5276         org_list INTEGER[];
5277         o INTEGER;
5278         sc TEXT;
5279         sce TEXT;
5280     BEGIN
5281         SELECT 'desired_sc' || field_suffix INTO sc;
5282         SELECT 'desired_sce' || field_suffix INTO sce;
5283         EXECUTE 'SELECT EXISTS (
5284             SELECT 1
5285             FROM information_schema.columns
5286             WHERE table_schema = $1
5287             AND table_name = $2
5288             and column_name = $3
5289         )' INTO proceed USING table_schema, table_name, sc;
5290         IF NOT proceed THEN
5291             RAISE EXCEPTION 'Missing column %', sc;
5292         END IF;
5293         EXECUTE 'SELECT EXISTS (
5294             SELECT 1
5295             FROM information_schema.columns
5296             WHERE table_schema = $1
5297             AND table_name = $2
5298             and column_name = $3
5299         )' INTO proceed USING table_schema, table_name, sce;
5300         IF NOT proceed THEN
5301             RAISE EXCEPTION 'Missing column %', sce;
5302         END IF;
5303
5304         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5305         IF org IS NULL THEN
5306             RAISE EXCEPTION 'Cannot find org by shortname';
5307         END IF;
5308
5309         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5310
5311         EXECUTE 'ALTER TABLE '
5312             || quote_ident(table_name)
5313             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5314         EXECUTE 'ALTER TABLE '
5315             || quote_ident(table_name)
5316             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5317         EXECUTE 'ALTER TABLE '
5318             || quote_ident(table_name)
5319             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5320         EXECUTE 'ALTER TABLE '
5321             || quote_ident(table_name)
5322             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5323
5324
5325         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5326             SET
5327                 x_sc' || field_suffix || ' = id
5328             FROM
5329                 (SELECT id, name, owner FROM asset_stat_cat
5330                     UNION SELECT id, name, owner FROM asset.stat_cat) u
5331             WHERE
5332                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5333                 AND u.owner = ANY ($1);'
5334         USING org_list;
5335
5336         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5337             SET
5338                 x_sce' || field_suffix || ' = id
5339             FROM
5340                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5341                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5342             WHERE
5343                     u.stat_cat = x_sc' || field_suffix || '
5344                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5345                 AND u.owner = ANY ($1);'
5346         USING org_list;
5347
5348         EXECUTE 'SELECT migration_tools.assert(
5349             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5350             ''Cannot find a desired stat cat'',
5351             ''Found all desired stat cats''
5352         );';
5353
5354         EXECUTE 'SELECT migration_tools.assert(
5355             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5356             ''Cannot find a desired stat cat entry'',
5357             ''Found all desired stat cat entries''
5358         );';
5359
5360     END;
5361 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5362
5363 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5364 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5365  LANGUAGE plpgsql
5366 AS $function$
5367 DECLARE
5368     c_name     TEXT;
5369 BEGIN
5370
5371     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5372             table_name = t_name
5373             AND table_schema = s_name
5374             AND (data_type='text' OR data_type='character varying')
5375             AND column_name like 'l_%'
5376     LOOP
5377        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5378     END LOOP;
5379
5380     RETURN TRUE;
5381 END
5382 $function$;
5383
5384 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5385 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5386  LANGUAGE plpgsql
5387 AS $function$
5388 DECLARE
5389     c_name     TEXT;
5390 BEGIN
5391
5392     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5393             table_name = t_name
5394             AND table_schema = s_name
5395             AND (data_type='text' OR data_type='character varying')
5396     LOOP
5397        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5398     END LOOP;
5399
5400     RETURN TRUE;
5401 END
5402 $function$;
5403
5404 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5405 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5406  LANGUAGE plpgsql
5407 AS $function$
5408 DECLARE
5409     c_name     TEXT;
5410 BEGIN
5411
5412     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5413             table_name = t_name
5414             AND table_schema = s_name
5415             AND (data_type='text' OR data_type='character varying')
5416             AND column_name like 'l_%'
5417     LOOP
5418        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5419     END LOOP;
5420
5421     RETURN TRUE;
5422 END
5423 $function$;
5424
5425 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5426 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5427  LANGUAGE plpgsql
5428 AS $function$
5429 DECLARE
5430     c_name     TEXT;
5431 BEGIN
5432
5433     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5434             table_name = t_name
5435             AND table_schema = s_name
5436             AND (data_type='text' OR data_type='character varying')
5437     LOOP
5438        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5439     END LOOP;
5440
5441     RETURN TRUE;
5442 END
5443 $function$;
5444
5445
5446 -- convenience function for handling item barcode collisions in asset_copy_legacy
5447
5448 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5449 DECLARE
5450     x_barcode TEXT;
5451     x_id BIGINT;
5452     row_count NUMERIC;
5453     internal_collision_count NUMERIC := 0;
5454     incumbent_collision_count NUMERIC := 0;
5455 BEGIN
5456     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5457     LOOP
5458         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5459         LOOP
5460             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5461             GET DIAGNOSTICS row_count = ROW_COUNT;
5462             internal_collision_count := internal_collision_count + row_count;
5463         END LOOP;
5464     END LOOP;
5465     RAISE INFO '% internal collisions', internal_collision_count;
5466     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
5467     LOOP
5468         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5469         LOOP
5470             UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5471             GET DIAGNOSTICS row_count = ROW_COUNT;
5472             incumbent_collision_count := incumbent_collision_count + row_count;
5473         END LOOP;
5474     END LOOP;
5475     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5476 END
5477 $function$ LANGUAGE plpgsql;
5478
5479 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5480 -- this should be ran prior to populating actor_card
5481
5482 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5483 DECLARE
5484     x_barcode TEXT;
5485     x_id BIGINT;
5486     row_count NUMERIC;
5487     internal_collision_count NUMERIC := 0;
5488     incumbent_barcode_collision_count NUMERIC := 0;
5489     incumbent_usrname_collision_count NUMERIC := 0;
5490 BEGIN
5491     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5492     LOOP
5493         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5494         LOOP
5495             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5496             GET DIAGNOSTICS row_count = ROW_COUNT;
5497             internal_collision_count := internal_collision_count + row_count;
5498         END LOOP;
5499     END LOOP;
5500     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5501
5502     FOR x_barcode IN
5503         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5504     LOOP
5505         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5506         LOOP
5507             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5508             GET DIAGNOSTICS row_count = ROW_COUNT;
5509             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5510         END LOOP;
5511     END LOOP;
5512     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5513
5514     FOR x_barcode IN
5515         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5516     LOOP
5517         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5518         LOOP
5519             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5520             GET DIAGNOSTICS row_count = ROW_COUNT;
5521             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5522         END LOOP;
5523     END LOOP;
5524     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5525 END
5526 $function$ LANGUAGE plpgsql;
5527
5528 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5529
5530 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5531 DECLARE
5532     x_barcode TEXT;
5533     x_id BIGINT;
5534     row_count NUMERIC;
5535     internal_collision_count NUMERIC := 0;
5536     incumbent_collision_count NUMERIC := 0;
5537 BEGIN
5538     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5539     LOOP
5540         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5541         LOOP
5542             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5543             GET DIAGNOSTICS row_count = ROW_COUNT;
5544             internal_collision_count := internal_collision_count + row_count;
5545         END LOOP;
5546     END LOOP;
5547     RAISE INFO '% internal collisions', internal_collision_count;
5548     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
5549     LOOP
5550         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5551         LOOP
5552             UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5553             GET DIAGNOSTICS row_count = ROW_COUNT;
5554             incumbent_collision_count := incumbent_collision_count + row_count;
5555         END LOOP;
5556     END LOOP;
5557     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5558 END
5559 $function$ LANGUAGE plpgsql;
5560
5561 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5562 -- this should be ran prior to populating actor_card
5563
5564 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5565 DECLARE
5566     x_barcode TEXT;
5567     x_id BIGINT;
5568     row_count NUMERIC;
5569     internal_collision_count NUMERIC := 0;
5570     incumbent_barcode_collision_count NUMERIC := 0;
5571     incumbent_usrname_collision_count NUMERIC := 0;
5572 BEGIN
5573     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5574     LOOP
5575         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5576         LOOP
5577             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5578             GET DIAGNOSTICS row_count = ROW_COUNT;
5579             internal_collision_count := internal_collision_count + row_count;
5580         END LOOP;
5581     END LOOP;
5582     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5583
5584     FOR x_barcode IN
5585         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5586     LOOP
5587         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5588         LOOP
5589             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5590             GET DIAGNOSTICS row_count = ROW_COUNT;
5591             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5592         END LOOP;
5593     END LOOP;
5594     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5595
5596     FOR x_barcode IN
5597         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5598     LOOP
5599         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5600         LOOP
5601             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5602             GET DIAGNOSTICS row_count = ROW_COUNT;
5603             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5604         END LOOP;
5605     END LOOP;
5606     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5607 END
5608 $function$ LANGUAGE plpgsql;
5609
5610 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5611 -- WARNING: Use at your own risk
5612 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5613 DECLARE
5614     item_object asset.copy%ROWTYPE;
5615     user_object actor.usr%ROWTYPE;
5616     test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5617     result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5618     safe_to_delete BOOLEAN := FALSE;
5619     m action.found_circ_matrix_matchpoint;
5620     result_matchpoint INTEGER;
5621     -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5622 BEGIN
5623     SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5624     RAISE INFO 'testing rule: %', test_rule_object;
5625
5626     INSERT INTO actor.usr (
5627         profile,
5628         usrname,
5629         passwd,
5630         ident_type,
5631         first_given_name,
5632         family_name,
5633         home_ou,
5634         juvenile
5635     ) SELECT
5636         COALESCE(test_rule_object.grp, 2),
5637         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5638         MD5(NOW()::TEXT),
5639         1,
5640         'Ima',
5641         'Test',
5642         COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5643         COALESCE(test_rule_object.juvenile_flag, FALSE)
5644     ;
5645
5646     SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5647
5648     INSERT INTO asset.call_number (
5649         creator,
5650         editor,
5651         record,
5652         owning_lib,
5653         label,
5654         label_class
5655     ) SELECT
5656         1,
5657         1,
5658         -1,
5659         COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
5660         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5661         1
5662     ;
5663
5664     INSERT INTO asset.copy (
5665         barcode,
5666         circ_lib,
5667         creator,
5668         call_number,
5669         editor,
5670         location,
5671         loan_duration,
5672         fine_level,
5673         ref,
5674         circ_modifier
5675     ) SELECT
5676         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5677         COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
5678         1,
5679         currval('asset.call_number_id_seq'),
5680         1,
5681         COALESCE(test_rule_object.copy_location,1),
5682         2,
5683         2,
5684         COALESCE(test_rule_object.ref_flag,FALSE),
5685         test_rule_object.circ_modifier
5686     ;
5687
5688     SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
5689
5690     SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
5691         test_rule_object.org_unit,
5692         item_object,
5693         user_object,
5694         COALESCE(test_rule_object.is_renewal,FALSE)
5695     );
5696     RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
5697         test_rule_object.org_unit,
5698         item_object.id,
5699         user_object.id,
5700         COALESCE(test_rule_object.is_renewal,FALSE),
5701         m.success,
5702         (m.matchpoint).id,
5703         m.buildrows
5704     ;
5705
5706     FOR result_matchpoint IN SELECT UNNEST(m.buildrows)
5707     LOOP
5708         SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint;
5709         RAISE INFO 'considering rule: %', result_rule_object;
5710         IF result_rule_object.id = test_rule_object.id THEN
5711             RAISE INFO 'found self';
5712             CONTINUE;
5713         END IF;
5714         IF (result_rule_object.circulate = test_rule_object.circulate
5715             AND result_rule_object.duration_rule = test_rule_object.duration_rule
5716             AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule
5717             AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule
5718             AND (
5719                 (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL)
5720                 OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date)
5721                 OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL)
5722             )
5723             AND (
5724                 (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL)
5725                 OR (result_rule_object.renewals = test_rule_object.renewals)
5726                 OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL)
5727             )
5728             AND (
5729                 (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL)
5730                 OR (result_rule_object.grace_period = test_rule_object.grace_period)
5731                 OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL)
5732             )
5733             AND NOT EXISTS (
5734                 SELECT limit_set, fallthrough
5735                 FROM config.circ_matrix_limit_set_map
5736                 WHERE active and matchpoint = test_rule_object.id
5737                 EXCEPT
5738                 SELECT limit_set, fallthrough
5739                 FROM config.circ_matrix_limit_set_map
5740                 WHERE active and matchpoint = result_rule_object.id
5741             )
5742         ) THEN
5743             RAISE INFO 'rule has same outcome';
5744             safe_to_delete := TRUE;
5745         ELSE
5746             RAISE INFO 'rule has different outcome, bail now';
5747             RAISE EXCEPTION 'rollback the item and user tables';
5748         END IF;
5749     END LOOP;
5750
5751     RAISE EXCEPTION 'rollback the item and user tables';
5752
5753 EXCEPTION WHEN OTHERS THEN
5754
5755     RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
5756     RETURN safe_to_delete;
5757
5758 END;
5759 $func$ LANGUAGE plpgsql;
5760