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