sql/base/base.sql

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