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