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.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
3203
3204 use MARC::Record;
3205 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3206 use MARC::Charset;
3207
3208 MARC::Charset->assume_unicode(1);
3209
3210 my $xml = shift;
3211
3212 eval {
3213     my $r = MARC::Record->new_from_xml( $xml );
3214     my $output_xml = $r->as_xml_record();
3215 };
3216 if ($@) {
3217     return 0;
3218 } else {
3219     return 1;
3220 }
3221
3222 $func$ LANGUAGE PLPERLU;
3223 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
3224
3225 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3226 BEGIN
3227    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3228            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3229            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
3230    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3231            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3232            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
3233    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3234            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3235            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
3236    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3237            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3238            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
3239    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3240            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3241            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
3242    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3243            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3244            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
3245    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3246            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3247            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
3248    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
3249    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
3250    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
3251    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
3252    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
3253 END;
3254 $FUNC$ LANGUAGE PLPGSQL;
3255
3256 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3257 BEGIN
3258    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
3259    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
3260    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
3261    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
3262    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
3263    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
3264    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
3265
3266    -- import any new circ rules
3267    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3268    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3269    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3270    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3271
3272    -- and permission groups
3273    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3274
3275 END;
3276 $FUNC$ LANGUAGE PLPGSQL;
3277
3278
3279 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$
3280 DECLARE
3281     name TEXT;
3282     loopq TEXT;
3283     existsq TEXT;
3284     ct INTEGER;
3285     cols TEXT[];
3286     copyst TEXT;
3287 BEGIN
3288     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3289     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3290     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
3291     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3292     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3293     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3294     FOR name IN EXECUTE loopq LOOP
3295        EXECUTE existsq INTO ct USING name;
3296        IF ct = 0 THEN
3297            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3298            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3299                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3300            EXECUTE copyst USING name;
3301        END IF;
3302     END LOOP;
3303 END;
3304 $FUNC$ LANGUAGE PLPGSQL;
3305
3306 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3307 DECLARE
3308     id BIGINT;
3309     loopq TEXT;
3310     cols TEXT[];
3311     splitst TEXT;
3312 BEGIN
3313     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3314     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;
3315     FOR id IN EXECUTE loopq USING delimiter LOOP
3316        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3317        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3318                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3319        EXECUTE splitst USING id, delimiter;
3320     END LOOP;
3321 END;
3322 $FUNC$ LANGUAGE PLPGSQL;
3323
3324 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3325
3326 use strict;
3327 use warnings;
3328
3329 use MARC::Record;
3330 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3331 use MARC::Charset;
3332
3333 MARC::Charset->assume_unicode(1);
3334
3335 my $target_xml = shift;
3336 my $source_xml = shift;
3337 my $tags = shift;
3338
3339 my $target;
3340 my $source;
3341
3342 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3343 if ($@) {
3344     return;
3345 }
3346 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3347 if ($@) {
3348     return;
3349 }
3350
3351 my $source_id = $source->subfield('901', 'c');
3352 $source_id = $source->subfield('903', 'a') unless $source_id;
3353 my $target_id = $target->subfield('901', 'c');
3354 $target_id = $target->subfield('903', 'a') unless $target_id;
3355
3356 my %existing_fields;
3357 foreach my $tag (@$tags) {
3358     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3359     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3360     $target->insert_fields_ordered(map { $_->clone() } @to_add);
3361     if (@to_add) {
3362         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3363     }
3364 }
3365
3366 my $xml = $target->as_xml_record;
3367 $xml =~ s/^<\?.+?\?>$//mo;
3368 $xml =~ s/\n//sgo;
3369 $xml =~ s/>\s+</></sgo;
3370
3371 return $xml;
3372
3373 $func$ LANGUAGE PLPERLU;
3374 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
3375
3376 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3377
3378 use strict;
3379 use warnings;
3380
3381 use MARC::Record;
3382 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3383 use Text::CSV;
3384
3385 my $in_tags = shift;
3386 my $in_values = shift;
3387
3388 # hack-and-slash parsing of array-passed-as-string;
3389 # this can go away once everybody is running Postgres 9.1+
3390 my $csv = Text::CSV->new({binary => 1});
3391 $in_tags =~ s/^{//;
3392 $in_tags =~ s/}$//;
3393 my $status = $csv->parse($in_tags);
3394 my $tags = [ $csv->fields() ];
3395 $in_values =~ s/^{//;
3396 $in_values =~ s/}$//;
3397 $status = $csv->parse($in_values);
3398 my $values = [ $csv->fields() ];
3399
3400 my $marc = MARC::Record->new();
3401
3402 $marc->leader('00000nam a22000007  4500');
3403 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3404
3405 foreach my $i (0..$#$tags) {
3406     my ($tag, $sf);
3407     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3408         $tag = $1;
3409         $sf = $2;
3410         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3411     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3412         $tag = $1;
3413         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3414     }
3415 }
3416
3417 my $xml = $marc->as_xml_record;
3418 $xml =~ s/^<\?.+?\?>$//mo;
3419 $xml =~ s/\n//sgo;
3420 $xml =~ s/>\s+</></sgo;
3421
3422 return $xml;
3423
3424 $func$ LANGUAGE PLPERLU;
3425 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3426 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3427 The second argument is an array of text containing the values to plug into each field.
3428 If the value for a given field is NULL or the empty string, it is not inserted.
3429 $$;
3430
3431 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3432
3433 use strict;
3434 use warnings;
3435
3436 use MARC::Record;
3437 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3438 use Text::CSV;
3439
3440 my $in_tags = shift;
3441 my $in_ind1 = shift;
3442 my $in_ind2 = shift;
3443 my $in_values = shift;
3444
3445 # hack-and-slash parsing of array-passed-as-string;
3446 # this can go away once everybody is running Postgres 9.1+
3447 my $csv = Text::CSV->new({binary => 1});
3448 $in_tags =~ s/^{//;
3449 $in_tags =~ s/}$//;
3450 my $status = $csv->parse($in_tags);
3451 my $tags = [ $csv->fields() ];
3452 $in_ind1 =~ s/^{//;
3453 $in_ind1 =~ s/}$//;
3454 $status = $csv->parse($in_ind1);
3455 my $ind1s = [ $csv->fields() ];
3456 $in_ind2 =~ s/^{//;
3457 $in_ind2 =~ s/}$//;
3458 $status = $csv->parse($in_ind2);
3459 my $ind2s = [ $csv->fields() ];
3460 $in_values =~ s/^{//;
3461 $in_values =~ s/}$//;
3462 $status = $csv->parse($in_values);
3463 my $values = [ $csv->fields() ];
3464
3465 my $marc = MARC::Record->new();
3466
3467 $marc->leader('00000nam a22000007  4500');
3468 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3469
3470 foreach my $i (0..$#$tags) {
3471     my ($tag, $sf);
3472     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3473         $tag = $1;
3474         $sf = $2;
3475         $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3476     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3477         $tag = $1;
3478         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3479     }
3480 }
3481
3482 my $xml = $marc->as_xml_record;
3483 $xml =~ s/^<\?.+?\?>$//mo;
3484 $xml =~ s/\n//sgo;
3485 $xml =~ s/>\s+</></sgo;
3486
3487 return $xml;
3488
3489 $func$ LANGUAGE PLPERLU;
3490 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3491 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3492 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3493 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3494 The fourth argument is an array of text containing the values to plug into each field.
3495 If the value for a given field is NULL or the empty string, it is not inserted.
3496 $$;
3497
3498 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3499
3500 my ($marcxml, $tag, $pos, $value) = @_;
3501
3502 use MARC::Record;
3503 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3504 use MARC::Charset;
3505 use strict;
3506
3507 MARC::Charset->assume_unicode(1);
3508
3509 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3510 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3511 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3512 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3513
3514 my $xml = $marcxml;
3515 eval {
3516     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3517
3518     foreach my $field ($marc->field($tag)) {
3519         $field->update("ind$pos" => $value);
3520     }
3521     $xml = $marc->as_xml_record;
3522     $xml =~ s/^<\?.+?\?>$//mo;
3523     $xml =~ s/\n//sgo;
3524     $xml =~ s/>\s+</></sgo;
3525 };
3526 return $xml;
3527
3528 $func$ LANGUAGE PLPERLU;
3529
3530 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3531 The first argument is a MARCXML string.
3532 The second argument is a MARC tag.
3533 The third argument is the indicator position, either 1 or 2.
3534 The fourth argument is the character to set the indicator value to.
3535 All occurences of the specified field will be changed.
3536 The function returns the revised MARCXML string.$$;
3537
3538 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3539     username TEXT,
3540     password TEXT,
3541     org TEXT,
3542     perm_group TEXT,
3543     first_name TEXT DEFAULT '',
3544     last_name TEXT DEFAULT ''
3545 ) RETURNS VOID AS $func$
3546 BEGIN
3547     RAISE NOTICE '%', org ;
3548     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3549     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3550     FROM   actor.org_unit aou, permission.grp_tree pgt
3551     WHERE  aou.shortname = org
3552     AND    pgt.name = perm_group;
3553 END
3554 $func$
3555 LANGUAGE PLPGSQL;
3556
3557 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3558 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3559     DECLARE
3560         target_event_def ALIAS FOR $1;
3561         orgs ALIAS FOR $2;
3562     BEGIN
3563         DROP TABLE IF EXISTS new_atevdefs;
3564         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3565         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3566             INSERT INTO action_trigger.event_definition (
3567                 active
3568                 ,owner
3569                 ,name
3570                 ,hook
3571                 ,validator
3572                 ,reactor
3573                 ,cleanup_success
3574                 ,cleanup_failure
3575                 ,delay
3576                 ,max_delay
3577                 ,usr_field
3578                 ,opt_in_setting
3579                 ,delay_field
3580                 ,group_field
3581                 ,template
3582                 ,granularity
3583                 ,repeat_delay
3584             ) SELECT
3585                 'f'
3586                 ,orgs[i]
3587                 ,name || ' (clone of '||target_event_def||')'
3588                 ,hook
3589                 ,validator
3590                 ,reactor
3591                 ,cleanup_success
3592                 ,cleanup_failure
3593                 ,delay
3594                 ,max_delay
3595                 ,usr_field
3596                 ,opt_in_setting
3597                 ,delay_field
3598                 ,group_field
3599                 ,template
3600                 ,granularity
3601                 ,repeat_delay
3602             FROM
3603                 action_trigger.event_definition
3604             WHERE
3605                 id = target_event_def
3606             ;
3607             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3608             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3609             INSERT INTO action_trigger.environment (
3610                 event_def
3611                 ,path
3612                 ,collector
3613                 ,label
3614             ) SELECT
3615                 currval('action_trigger.event_definition_id_seq')
3616                 ,path
3617                 ,collector
3618                 ,label
3619             FROM
3620                 action_trigger.environment
3621             WHERE
3622                 event_def = target_event_def
3623             ;
3624             INSERT INTO action_trigger.event_params (
3625                 event_def
3626                 ,param
3627                 ,value
3628             ) SELECT
3629                 currval('action_trigger.event_definition_id_seq')
3630                 ,param
3631                 ,value
3632             FROM
3633                 action_trigger.event_params
3634             WHERE
3635                 event_def = target_event_def
3636             ;
3637         END LOOP;
3638         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);
3639     END;
3640 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3641
3642 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3643 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3644     DECLARE
3645         target_event_def ALIAS FOR $1;
3646         orgs ALIAS FOR $2;
3647         new_interval ALIAS FOR $3;
3648     BEGIN
3649         DROP TABLE IF EXISTS new_atevdefs;
3650         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3651         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3652             INSERT INTO action_trigger.event_definition (
3653                 active
3654                 ,owner
3655                 ,name
3656                 ,hook
3657                 ,validator
3658                 ,reactor
3659                 ,cleanup_success
3660                 ,cleanup_failure
3661                 ,delay
3662                 ,max_delay
3663                 ,usr_field
3664                 ,opt_in_setting
3665                 ,delay_field
3666                 ,group_field
3667                 ,template
3668                 ,granularity
3669                 ,repeat_delay
3670             ) SELECT
3671                 'f'
3672                 ,orgs[i]
3673                 ,name || ' (clone of '||target_event_def||')'
3674                 ,hook
3675                 ,validator
3676                 ,reactor
3677                 ,cleanup_success
3678                 ,cleanup_failure
3679                 ,new_interval
3680                 ,max_delay
3681                 ,usr_field
3682                 ,opt_in_setting
3683                 ,delay_field
3684                 ,group_field
3685                 ,template
3686                 ,granularity
3687                 ,repeat_delay
3688             FROM
3689                 action_trigger.event_definition
3690             WHERE
3691                 id = target_event_def
3692             ;
3693             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3694             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3695             INSERT INTO action_trigger.environment (
3696                 event_def
3697                 ,path
3698                 ,collector
3699                 ,label
3700             ) SELECT
3701                 currval('action_trigger.event_definition_id_seq')
3702                 ,path
3703                 ,collector
3704                 ,label
3705             FROM
3706                 action_trigger.environment
3707             WHERE
3708                 event_def = target_event_def
3709             ;
3710             INSERT INTO action_trigger.event_params (
3711                 event_def
3712                 ,param
3713                 ,value
3714             ) SELECT
3715                 currval('action_trigger.event_definition_id_seq')
3716                 ,param
3717                 ,value
3718             FROM
3719                 action_trigger.event_params
3720             WHERE
3721                 event_def = target_event_def
3722             ;
3723         END LOOP;
3724         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);
3725     END;
3726 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3727
3728 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3729 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3730     DECLARE
3731         org ALIAS FOR $1;
3732         target_event_defs ALIAS FOR $2;
3733     BEGIN
3734         DROP TABLE IF EXISTS new_atevdefs;
3735         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3736         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3737             INSERT INTO action_trigger.event_definition (
3738                 active
3739                 ,owner
3740                 ,name
3741                 ,hook
3742                 ,validator
3743                 ,reactor
3744                 ,cleanup_success
3745                 ,cleanup_failure
3746                 ,delay
3747                 ,max_delay
3748                 ,usr_field
3749                 ,opt_in_setting
3750                 ,delay_field
3751                 ,group_field
3752                 ,template
3753                 ,granularity
3754                 ,repeat_delay
3755             ) SELECT
3756                 'f'
3757                 ,org
3758                 ,name || ' (clone of '||target_event_defs[i]||')'
3759                 ,hook
3760                 ,validator
3761                 ,reactor
3762                 ,cleanup_success
3763                 ,cleanup_failure
3764                 ,delay
3765                 ,max_delay
3766                 ,usr_field
3767                 ,opt_in_setting
3768                 ,delay_field
3769                 ,group_field
3770                 ,template
3771                 ,granularity
3772                 ,repeat_delay
3773             FROM
3774                 action_trigger.event_definition
3775             WHERE
3776                 id = target_event_defs[i]
3777             ;
3778             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3779             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3780             INSERT INTO action_trigger.environment (
3781                 event_def
3782                 ,path
3783                 ,collector
3784                 ,label
3785             ) SELECT
3786                 currval('action_trigger.event_definition_id_seq')
3787                 ,path
3788                 ,collector
3789                 ,label
3790             FROM
3791                 action_trigger.environment
3792             WHERE
3793                 event_def = target_event_defs[i]
3794             ;
3795             INSERT INTO action_trigger.event_params (
3796                 event_def
3797                 ,param
3798                 ,value
3799             ) SELECT
3800                 currval('action_trigger.event_definition_id_seq')
3801                 ,param
3802                 ,value
3803             FROM
3804                 action_trigger.event_params
3805             WHERE
3806                 event_def = target_event_defs[i]
3807             ;
3808         END LOOP;
3809         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3810     END;
3811 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3812
3813 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3814     UPDATE
3815         action_trigger.event
3816     SET
3817          start_time = NULL
3818         ,update_time = NULL
3819         ,complete_time = NULL
3820         ,update_process = NULL
3821         ,state = 'pending'
3822         ,template_output = NULL
3823         ,error_output = NULL
3824         ,async_output = NULL
3825     WHERE
3826         id = $1;
3827 $$ LANGUAGE SQL;
3828
3829 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3830     my ($marcxml) = @_;
3831
3832     use MARC::Record;
3833     use MARC::File::XML;
3834     use MARC::Field;
3835
3836     my $field;
3837     eval {
3838         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3839         $field = $marc->leader();
3840     };
3841     return $field;
3842 $$ LANGUAGE PLPERLU STABLE;
3843
3844 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3845     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3846
3847     use MARC::Record;
3848     use MARC::File::XML;
3849     use MARC::Field;
3850
3851     my $field;
3852     eval {
3853         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3854         $field = $marc->field($tag);
3855     };
3856     return $field->as_string($subfield,$delimiter) if $field;
3857     return;
3858 $$ LANGUAGE PLPERLU STABLE;
3859
3860 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3861     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3862
3863     use MARC::Record;
3864     use MARC::File::XML;
3865     use MARC::Field;
3866
3867     my @fields;
3868     eval {
3869         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3870         @fields = $marc->field($tag);
3871     };
3872     my @texts;
3873     foreach my $field (@fields) {
3874         push @texts, $field->as_string($subfield,$delimiter);
3875     }
3876     return \@texts;
3877 $$ LANGUAGE PLPERLU STABLE;
3878
3879 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3880     my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3881
3882     use MARC::Record;
3883     use MARC::File::XML;
3884     use MARC::Field;
3885
3886     my @fields;
3887     eval {
3888         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3889         @fields = $marc->field($tag);
3890     };
3891     my @texts;
3892     foreach my $field (@fields) {
3893         if ($field->as_string() =~ qr/$match/) {
3894             push @texts, $field->as_string($subfield,$delimiter);
3895         }
3896     }
3897     return \@texts;
3898 $$ LANGUAGE PLPERLU STABLE;
3899
3900 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3901     SELECT action.find_hold_matrix_matchpoint(
3902         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3903         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3904         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3905         (SELECT usr FROM action.hold_request WHERE id = $1),
3906         (SELECT requestor FROM action.hold_request WHERE id = $1)
3907     );
3908 $$ LANGUAGE SQL;
3909
3910 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3911     SELECT action.hold_request_permit_test(
3912         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3913         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3914         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3915         (SELECT usr FROM action.hold_request WHERE id = $1),
3916         (SELECT requestor FROM action.hold_request WHERE id = $1)
3917     );
3918 $$ LANGUAGE SQL;
3919
3920 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3921     SELECT action.find_circ_matrix_matchpoint(
3922         (SELECT circ_lib FROM action.circulation WHERE id = $1),
3923         (SELECT target_copy FROM action.circulation WHERE id = $1),
3924         (SELECT usr FROM action.circulation WHERE id = $1),
3925         (SELECT COALESCE(
3926                 NULLIF(phone_renewal,false),
3927                 NULLIF(desk_renewal,false),
3928                 NULLIF(opac_renewal,false),
3929                 false
3930             ) FROM action.circulation WHERE id = $1
3931         )
3932     );
3933 $$ LANGUAGE SQL;
3934
3935 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3936     DECLARE
3937         test ALIAS FOR $1;
3938     BEGIN
3939         IF NOT test THEN
3940             RAISE EXCEPTION 'assertion';
3941         END IF;
3942     END;
3943 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3944
3945 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3946     DECLARE
3947         test ALIAS FOR $1;
3948         msg ALIAS FOR $2;
3949     BEGIN
3950         IF NOT test THEN
3951             RAISE EXCEPTION '%', msg;
3952         END IF;
3953     END;
3954 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3955
3956 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3957     DECLARE
3958         test ALIAS FOR $1;
3959         fail_msg ALIAS FOR $2;
3960         success_msg ALIAS FOR $3;
3961     BEGIN
3962         IF NOT test THEN
3963             RAISE EXCEPTION '%', fail_msg;
3964         END IF;
3965         RETURN success_msg;
3966     END;
3967 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3968
3969 -- push bib sequence and return starting value for reserved range
3970 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3971     DECLARE
3972         bib_count ALIAS FOR $1;
3973         output BIGINT;
3974     BEGIN
3975         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3976         FOR output IN
3977             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3978         LOOP
3979             RETURN output;
3980         END LOOP;
3981     END;
3982 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3983
3984 -- set a new salted password
3985
3986 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3987     DECLARE
3988         usr_id              ALIAS FOR $1;
3989         plain_passwd        ALIAS FOR $2;
3990         plain_salt          TEXT;
3991         md5_passwd          TEXT;
3992     BEGIN
3993
3994         SELECT actor.create_salt('main') INTO plain_salt;
3995
3996         SELECT MD5(plain_passwd) INTO md5_passwd;
3997
3998         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3999
4000         RETURN TRUE;
4001
4002     END;
4003 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4004
4005
4006 -- convenience functions for handling copy_location maps
4007 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
4008     SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
4009 $$ LANGUAGE SQL;
4010
4011 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
4012     DECLARE
4013         table_schema ALIAS FOR $1;
4014         table_name ALIAS FOR $2;
4015         org_shortname ALIAS FOR $3;
4016         org_range ALIAS FOR $4;
4017         make_assertion ALIAS FOR $5;
4018         proceed BOOLEAN;
4019         org INTEGER;
4020         -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
4021         -- though we'll still use the passed org for the full path traversal when needed
4022         x_org_found BOOLEAN;
4023         x_org INTEGER;
4024         org_list INTEGER[];
4025         o INTEGER;
4026         row_count NUMERIC;
4027     BEGIN
4028         EXECUTE 'SELECT EXISTS (
4029             SELECT 1
4030             FROM information_schema.columns
4031             WHERE table_schema = $1
4032             AND table_name = $2
4033             and column_name = ''desired_shelf''
4034         )' INTO proceed USING table_schema, table_name;
4035         IF NOT proceed THEN
4036             RAISE EXCEPTION 'Missing column desired_shelf';
4037         END IF;
4038
4039         EXECUTE 'SELECT EXISTS (
4040             SELECT 1
4041             FROM information_schema.columns
4042             WHERE table_schema = $1
4043             AND table_name = $2
4044             and column_name = ''x_org''
4045         )' INTO x_org_found USING table_schema, table_name;
4046
4047         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4048         IF org IS NULL THEN
4049             RAISE EXCEPTION 'Cannot find org by shortname';
4050         END IF;
4051
4052         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4053
4054         EXECUTE 'ALTER TABLE '
4055             || quote_ident(table_name)
4056             || ' DROP COLUMN IF EXISTS x_shelf';
4057         EXECUTE 'ALTER TABLE '
4058             || quote_ident(table_name)
4059             || ' ADD COLUMN x_shelf INTEGER';
4060
4061         IF x_org_found THEN
4062             RAISE INFO 'Found x_org column';
4063             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4064                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4065                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4066                 || ' AND b.owning_lib = x_org'
4067                 || ' AND NOT b.deleted';
4068             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4069                 || ' SET x_shelf = b.id FROM asset.copy_location b'
4070                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4071                 || ' AND b.owning_lib = x_org'
4072                 || ' AND x_shelf IS NULL'
4073                 || ' AND NOT b.deleted';
4074         ELSE
4075             RAISE INFO 'Did not find x_org column';
4076             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4077                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4078                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4079                 || ' AND b.owning_lib = $1'
4080                 || ' AND NOT b.deleted'
4081             USING org;
4082             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4083                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4084                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4085                 || ' AND b.owning_lib = $1'
4086                 || ' AND x_shelf IS NULL'
4087                 || ' AND NOT b.deleted'
4088             USING org;
4089         END IF;
4090
4091         FOREACH o IN ARRAY org_list LOOP
4092             RAISE INFO 'Considering org %', o;
4093             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4094                 || ' SET x_shelf = b.id FROM asset.copy_location b'
4095                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4096                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
4097                 || ' AND NOT b.deleted'
4098             USING o;
4099             GET DIAGNOSTICS row_count = ROW_COUNT;
4100             RAISE INFO 'Updated % rows', row_count;
4101         END LOOP;
4102
4103         IF make_assertion THEN
4104             EXECUTE 'SELECT migration_tools.assert(
4105                 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
4106                 ''Cannot find a desired location'',
4107                 ''Found all desired locations''
4108             );';
4109         END IF;
4110
4111     END;
4112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4113
4114 -- convenience functions for handling circmod maps
4115
4116 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
4117     DECLARE
4118         table_schema ALIAS FOR $1;
4119         table_name ALIAS FOR $2;
4120         proceed BOOLEAN;
4121     BEGIN
4122         EXECUTE 'SELECT EXISTS (
4123             SELECT 1
4124             FROM information_schema.columns
4125             WHERE table_schema = $1
4126             AND table_name = $2
4127             and column_name = ''desired_circmod''
4128         )' INTO proceed USING table_schema, table_name;
4129         IF NOT proceed THEN
4130             RAISE EXCEPTION 'Missing column desired_circmod';
4131         END IF;
4132
4133         EXECUTE 'ALTER TABLE '
4134             || quote_ident(table_name)
4135             || ' DROP COLUMN IF EXISTS x_circmod';
4136         EXECUTE 'ALTER TABLE '
4137             || quote_ident(table_name)
4138             || ' ADD COLUMN x_circmod TEXT';
4139
4140         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4141             || ' SET x_circmod = code FROM config.circ_modifier b'
4142             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
4143
4144         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4145             || ' SET x_circmod = code FROM config.circ_modifier b'
4146             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
4147             || ' AND x_circmod IS NULL';
4148
4149         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4150             || ' SET x_circmod = code FROM config.circ_modifier b'
4151             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
4152             || ' AND x_circmod IS NULL';
4153
4154         EXECUTE 'SELECT migration_tools.assert(
4155             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
4156             ''Cannot find a desired circulation modifier'',
4157             ''Found all desired circulation modifiers''
4158         );';
4159
4160     END;
4161 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4162
4163 -- convenience functions for handling item status maps
4164
4165 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
4166     DECLARE
4167         table_schema ALIAS FOR $1;
4168         table_name ALIAS FOR $2;
4169         proceed BOOLEAN;
4170     BEGIN
4171         EXECUTE 'SELECT EXISTS (
4172             SELECT 1
4173             FROM information_schema.columns
4174             WHERE table_schema = $1
4175             AND table_name = $2
4176             and column_name = ''desired_status''
4177         )' INTO proceed USING table_schema, table_name;
4178         IF NOT proceed THEN
4179             RAISE EXCEPTION 'Missing column desired_status';
4180         END IF;
4181
4182         EXECUTE 'ALTER TABLE '
4183             || quote_ident(table_name)
4184             || ' DROP COLUMN IF EXISTS x_status';
4185         EXECUTE 'ALTER TABLE '
4186             || quote_ident(table_name)
4187             || ' ADD COLUMN x_status INTEGER';
4188
4189         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4190             || ' SET x_status = id FROM config.copy_status b'
4191             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
4192
4193         EXECUTE 'SELECT migration_tools.assert(
4194             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
4195             ''Cannot find a desired copy status'',
4196             ''Found all desired copy statuses''
4197         );';
4198
4199     END;
4200 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4201
4202 -- convenience functions for handling org maps
4203
4204 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
4205     DECLARE
4206         table_schema ALIAS FOR $1;
4207         table_name ALIAS FOR $2;
4208         proceed BOOLEAN;
4209     BEGIN
4210         EXECUTE 'SELECT EXISTS (
4211             SELECT 1
4212             FROM information_schema.columns
4213             WHERE table_schema = $1
4214             AND table_name = $2
4215             and column_name = ''desired_org''
4216         )' INTO proceed USING table_schema, table_name;
4217         IF NOT proceed THEN
4218             RAISE EXCEPTION 'Missing column desired_org';
4219         END IF;
4220
4221         EXECUTE 'ALTER TABLE '
4222             || quote_ident(table_name)
4223             || ' DROP COLUMN IF EXISTS x_org';
4224         EXECUTE 'ALTER TABLE '
4225             || quote_ident(table_name)
4226             || ' ADD COLUMN x_org INTEGER';
4227
4228         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4229             || ' SET x_org = b.id FROM actor.org_unit b'
4230             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
4231
4232         EXECUTE 'SELECT migration_tools.assert(
4233             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
4234             ''Cannot find a desired org unit'',
4235             ''Found all desired org units''
4236         );';
4237
4238     END;
4239 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4240
4241 -- convenience function for handling desired_not_migrate
4242
4243 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
4244     DECLARE
4245         table_schema ALIAS FOR $1;
4246         table_name ALIAS FOR $2;
4247         proceed BOOLEAN;
4248     BEGIN
4249         EXECUTE 'SELECT EXISTS (
4250             SELECT 1
4251             FROM information_schema.columns
4252             WHERE table_schema = $1
4253             AND table_name = $2
4254             and column_name = ''desired_not_migrate''
4255         )' INTO proceed USING table_schema, table_name;
4256         IF NOT proceed THEN
4257             RAISE EXCEPTION 'Missing column desired_not_migrate';
4258         END IF;
4259
4260         EXECUTE 'ALTER TABLE '
4261             || quote_ident(table_name)
4262             || ' DROP COLUMN IF EXISTS x_migrate';
4263         EXECUTE 'ALTER TABLE '
4264             || quote_ident(table_name)
4265             || ' ADD COLUMN x_migrate BOOLEAN';
4266
4267         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4268             || ' SET x_migrate = CASE'
4269             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4270             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4271             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4272             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4273             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4274             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4275             || ' END';
4276
4277         EXECUTE 'SELECT migration_tools.assert(
4278             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4279             ''Not all desired_not_migrate values understood'',
4280             ''All desired_not_migrate values understood''
4281         );';
4282
4283     END;
4284 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4285
4286 -- convenience function for handling desired_not_migrate
4287
4288 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4289     DECLARE
4290         table_schema ALIAS FOR $1;
4291         table_name ALIAS FOR $2;
4292         proceed BOOLEAN;
4293     BEGIN
4294         EXECUTE 'SELECT EXISTS (
4295             SELECT 1
4296             FROM information_schema.columns
4297             WHERE table_schema = $1
4298             AND table_name = $2
4299             and column_name = ''desired_barred_or_blocked''
4300         )' INTO proceed USING table_schema, table_name;
4301         IF NOT proceed THEN
4302             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4303         END IF;
4304
4305         EXECUTE 'ALTER TABLE '
4306             || quote_ident(table_name)
4307             || ' DROP COLUMN IF EXISTS x_barred';
4308         EXECUTE 'ALTER TABLE '
4309             || quote_ident(table_name)
4310             || ' ADD COLUMN x_barred BOOLEAN';
4311
4312         EXECUTE 'ALTER TABLE '
4313             || quote_ident(table_name)
4314             || ' DROP COLUMN IF EXISTS x_blocked';
4315         EXECUTE 'ALTER TABLE '
4316             || quote_ident(table_name)
4317             || ' ADD COLUMN x_blocked BOOLEAN';
4318
4319         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4320             || ' SET x_barred = CASE'
4321             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4322             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4323             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4324             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4325             || ' END';
4326
4327         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4328             || ' SET x_blocked = CASE'
4329             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4330             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4331             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4332             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4333             || ' END';
4334
4335         EXECUTE 'SELECT migration_tools.assert(
4336             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4337             ''Not all desired_barred_or_blocked values understood'',
4338             ''All desired_barred_or_blocked values understood''
4339         );';
4340
4341     END;
4342 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4343
4344 -- convenience function for handling desired_profile
4345
4346 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4347     DECLARE
4348         table_schema ALIAS FOR $1;
4349         table_name ALIAS FOR $2;
4350         proceed BOOLEAN;
4351     BEGIN
4352         EXECUTE 'SELECT EXISTS (
4353             SELECT 1
4354             FROM information_schema.columns
4355             WHERE table_schema = $1
4356             AND table_name = $2
4357             and column_name = ''desired_profile''
4358         )' INTO proceed USING table_schema, table_name;
4359         IF NOT proceed THEN
4360             RAISE EXCEPTION 'Missing column desired_profile';
4361         END IF;
4362
4363         EXECUTE 'ALTER TABLE '
4364             || quote_ident(table_name)
4365             || ' DROP COLUMN IF EXISTS x_profile';
4366         EXECUTE 'ALTER TABLE '
4367             || quote_ident(table_name)
4368             || ' ADD COLUMN x_profile INTEGER';
4369
4370         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4371             || ' SET x_profile = b.id FROM permission.grp_tree b'
4372             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4373
4374         EXECUTE 'SELECT migration_tools.assert(
4375             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4376             ''Cannot find a desired profile'',
4377             ''Found all desired profiles''
4378         );';
4379
4380     END;
4381 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4382
4383 -- convenience function for handling desired actor stat cats
4384
4385 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4386     DECLARE
4387         table_schema ALIAS FOR $1;
4388         table_name ALIAS FOR $2;
4389         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4390         org_shortname ALIAS FOR $4;
4391         proceed BOOLEAN;
4392         org INTEGER;
4393         org_list INTEGER[];
4394         sc TEXT;
4395         sce TEXT;
4396     BEGIN
4397
4398         SELECT 'desired_sc' || field_suffix INTO sc;
4399         SELECT 'desired_sce' || field_suffix INTO sce;
4400
4401         EXECUTE 'SELECT EXISTS (
4402             SELECT 1
4403             FROM information_schema.columns
4404             WHERE table_schema = $1
4405             AND table_name = $2
4406             and column_name = $3
4407         )' INTO proceed USING table_schema, table_name, sc;
4408         IF NOT proceed THEN
4409             RAISE EXCEPTION 'Missing column %', sc;
4410         END IF;
4411         EXECUTE 'SELECT EXISTS (
4412             SELECT 1
4413             FROM information_schema.columns
4414             WHERE table_schema = $1
4415             AND table_name = $2
4416             and column_name = $3
4417         )' INTO proceed USING table_schema, table_name, sce;
4418         IF NOT proceed THEN
4419             RAISE EXCEPTION 'Missing column %', sce;
4420         END IF;
4421
4422         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4423         IF org IS NULL THEN
4424             RAISE EXCEPTION 'Cannot find org by shortname';
4425         END IF;
4426         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4427
4428         -- caller responsible for their own truncates though we try to prevent duplicates
4429         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4430             SELECT DISTINCT
4431                  $1
4432                 ,BTRIM('||sc||')
4433             FROM
4434                 ' || quote_ident(table_name) || '
4435             WHERE
4436                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4437                 AND NOT EXISTS (
4438                     SELECT id
4439                     FROM actor.stat_cat
4440                     WHERE owner = ANY ($2)
4441                     AND name = BTRIM('||sc||')
4442                 )
4443                 AND NOT EXISTS (
4444                     SELECT id
4445                     FROM actor_stat_cat
4446                     WHERE owner = ANY ($2)
4447                     AND name = BTRIM('||sc||')
4448                 )
4449             ORDER BY 2;'
4450         USING org, org_list;
4451
4452         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4453             SELECT DISTINCT
4454                 COALESCE(
4455                     (SELECT id
4456                         FROM actor.stat_cat
4457                         WHERE owner = ANY ($2)
4458                         AND BTRIM('||sc||') = BTRIM(name))
4459                    ,(SELECT id
4460                         FROM actor_stat_cat
4461                         WHERE owner = ANY ($2)
4462                         AND BTRIM('||sc||') = BTRIM(name))
4463                 )
4464                 ,$1
4465                 ,BTRIM('||sce||')
4466             FROM
4467                 ' || quote_ident(table_name) || '
4468             WHERE
4469                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4470                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4471                 AND NOT EXISTS (
4472                     SELECT id
4473                     FROM actor.stat_cat_entry
4474                     WHERE stat_cat = (
4475                         SELECT id
4476                         FROM actor.stat_cat
4477                         WHERE owner = ANY ($2)
4478                         AND BTRIM('||sc||') = BTRIM(name)
4479                     ) AND value = BTRIM('||sce||')
4480                     AND owner = ANY ($2)
4481                 )
4482                 AND NOT EXISTS (
4483                     SELECT id
4484                     FROM actor_stat_cat_entry
4485                     WHERE stat_cat = (
4486                         SELECT id
4487                         FROM actor_stat_cat
4488                         WHERE owner = ANY ($2)
4489                         AND BTRIM('||sc||') = BTRIM(name)
4490                     ) AND value = BTRIM('||sce||')
4491                     AND owner = ANY ($2)
4492                 )
4493             ORDER BY 1,3;'
4494         USING org, org_list;
4495     END;
4496 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4497
4498 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4499     DECLARE
4500         table_schema ALIAS FOR $1;
4501         table_name ALIAS FOR $2;
4502         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4503         org_shortname ALIAS FOR $4;
4504         proceed BOOLEAN;
4505         org INTEGER;
4506         org_list INTEGER[];
4507         o INTEGER;
4508         sc TEXT;
4509         sce TEXT;
4510     BEGIN
4511         SELECT 'desired_sc' || field_suffix INTO sc;
4512         SELECT 'desired_sce' || field_suffix INTO sce;
4513         EXECUTE 'SELECT EXISTS (
4514             SELECT 1
4515             FROM information_schema.columns
4516             WHERE table_schema = $1
4517             AND table_name = $2
4518             and column_name = $3
4519         )' INTO proceed USING table_schema, table_name, sc;
4520         IF NOT proceed THEN
4521             RAISE EXCEPTION 'Missing column %', sc;
4522         END IF;
4523         EXECUTE 'SELECT EXISTS (
4524             SELECT 1
4525             FROM information_schema.columns
4526             WHERE table_schema = $1
4527             AND table_name = $2
4528             and column_name = $3
4529         )' INTO proceed USING table_schema, table_name, sce;
4530         IF NOT proceed THEN
4531             RAISE EXCEPTION 'Missing column %', sce;
4532         END IF;
4533
4534         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4535         IF org IS NULL THEN
4536             RAISE EXCEPTION 'Cannot find org by shortname';
4537         END IF;
4538
4539         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4540
4541         EXECUTE 'ALTER TABLE '
4542             || quote_ident(table_name)
4543             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4544         EXECUTE 'ALTER TABLE '
4545             || quote_ident(table_name)
4546             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4547         EXECUTE 'ALTER TABLE '
4548             || quote_ident(table_name)
4549             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4550         EXECUTE 'ALTER TABLE '
4551             || quote_ident(table_name)
4552             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4553
4554
4555         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4556             SET
4557                 x_sc' || field_suffix || ' = id
4558             FROM
4559                 (SELECT id, name, owner FROM actor_stat_cat
4560                     UNION SELECT id, name, owner FROM actor.stat_cat) u
4561             WHERE
4562                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4563                 AND u.owner = ANY ($1);'
4564         USING org_list;
4565
4566         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4567             SET
4568                 x_sce' || field_suffix || ' = id
4569             FROM
4570                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4571                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4572             WHERE
4573                     u.stat_cat = x_sc' || field_suffix || '
4574                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4575                 AND u.owner = ANY ($1);'
4576         USING org_list;
4577
4578         EXECUTE 'SELECT migration_tools.assert(
4579             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4580             ''Cannot find a desired stat cat'',
4581             ''Found all desired stat cats''
4582         );';
4583
4584         EXECUTE 'SELECT migration_tools.assert(
4585             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4586             ''Cannot find a desired stat cat entry'',
4587             ''Found all desired stat cat entries''
4588         );';
4589
4590     END;
4591 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4592
4593 -- convenience functions for adding shelving locations
4594 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4595 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4596 DECLARE
4597     return_id   INT;
4598     d           INT;
4599     cur_id      INT;
4600 BEGIN
4601     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4602     WHILE d >= 0
4603     LOOP
4604         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4605         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4606         IF return_id IS NOT NULL THEN
4607                 RETURN return_id;
4608         END IF;
4609         d := d - 1;
4610     END LOOP;
4611
4612     RETURN NULL;
4613 END
4614 $$ LANGUAGE plpgsql;
4615
4616 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4617
4618 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4619 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4620 DECLARE
4621     return_id   INT;
4622     d           INT;
4623     cur_id      INT;
4624 BEGIN
4625     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4626     WHILE d >= 0
4627     LOOP
4628         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4629
4630         SELECT INTO return_id id FROM
4631             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4632             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4633         IF return_id IS NOT NULL THEN
4634                 RETURN return_id;
4635         END IF;
4636         d := d - 1;
4637     END LOOP;
4638
4639     RETURN NULL;
4640 END
4641 $$ LANGUAGE plpgsql;
4642
4643 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4644 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4645     RETURNS BOOLEAN AS
4646 $BODY$
4647 DECLARE
4648         marc_xml        TEXT;
4649         new_marc        TEXT;
4650 BEGIN
4651         SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4652
4653         SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4654         UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4655
4656         RETURN true;
4657 END;
4658 $BODY$ LANGUAGE plpgsql;
4659
4660 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4661 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4662  RETURNS TEXT
4663  LANGUAGE plperlu
4664 AS $function$
4665 use strict;
4666 use warnings;
4667
4668 use MARC::Record;
4669 use MARC::File::XML (BinaryEncoding => 'utf8');
4670
4671 binmode(STDERR, ':bytes');
4672 binmode(STDOUT, ':utf8');
4673 binmode(STDERR, ':utf8');
4674
4675 my $marc_xml = shift;
4676 my $new_9_to_set = shift;
4677 my $force = shift;
4678
4679 $marc_xml =~ s/(<leader>.........)./${1}a/;
4680
4681 eval {
4682     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4683 };
4684 if ($@) {
4685     #elog("could not parse $bibid: $@\n");
4686     import MARC::File::XML (BinaryEncoding => 'utf8');
4687     return $marc_xml;
4688 }
4689
4690 my @uris = $marc_xml->field('856');
4691 return $marc_xml->as_xml_record() unless @uris;
4692
4693 foreach my $field (@uris) {
4694     my $ind1 = $field->indicator('1');
4695     if (!defined $ind1) { next; }
4696     if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4697         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4698     my $ind2 = $field->indicator('2');
4699     if (!defined $ind2) { next; }
4700     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4701     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4702     $field->add_subfields( '9' => $new_9_to_set );
4703 }
4704
4705 return $marc_xml->as_xml_record();
4706
4707 $function$;
4708
4709 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4710 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4711  RETURNS TEXT
4712  LANGUAGE plperlu
4713 AS $function$
4714 use strict;
4715 use warnings;
4716
4717 use MARC::Record;
4718 use MARC::File::XML (BinaryEncoding => 'utf8');
4719
4720 binmode(STDERR, ':bytes');
4721 binmode(STDOUT, ':utf8');
4722 binmode(STDERR, ':utf8');
4723
4724 my $marc_xml = shift;
4725 my $qualifying_match = shift;
4726 my $new_9_to_set = shift;
4727 my $force = shift;
4728
4729 $marc_xml =~ s/(<leader>.........)./${1}a/;
4730
4731 eval {
4732     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4733 };
4734 if ($@) {
4735     #elog("could not parse $bibid: $@\n");
4736     import MARC::File::XML (BinaryEncoding => 'utf8');
4737     return $marc_xml;
4738 }
4739
4740 my @uris = $marc_xml->field('856');
4741 return $marc_xml->as_xml_record() unless @uris;
4742
4743 foreach my $field (@uris) {
4744     if ($field->as_string() =~ qr/$qualifying_match/) {
4745         my $ind1 = $field->indicator('1');
4746         if (!defined $ind1) { next; }
4747         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4748         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4749         my $ind2 = $field->indicator('2');
4750         if (!defined $ind2) { next; }
4751         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4752         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4753         $field->add_subfields( '9' => $new_9_to_set );
4754     }
4755 }
4756
4757 return $marc_xml->as_xml_record();
4758
4759 $function$;
4760
4761 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4762 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4763  RETURNS TEXT
4764  LANGUAGE plperlu
4765 AS $function$
4766 use strict;
4767 use warnings;
4768
4769 use MARC::Record;
4770 use MARC::File::XML (BinaryEncoding => 'utf8');
4771
4772 binmode(STDERR, ':bytes');
4773 binmode(STDOUT, ':utf8');
4774 binmode(STDERR, ':utf8');
4775
4776 my $marc_xml = shift;
4777 my $substring_old_value = shift;
4778 my $new_value = shift;
4779 my $fix_indicators = shift;
4780
4781 $marc_xml =~ s/(<leader>.........)./${1}a/;
4782
4783 eval {
4784     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4785 };
4786 if ($@) {
4787     #elog("could not parse $bibid: $@\n");
4788     import MARC::File::XML (BinaryEncoding => 'utf8');
4789     return $marc_xml;
4790 }
4791
4792 my @uris = $marc_xml->field('856');
4793 return $marc_xml->as_xml_record() unless @uris;
4794
4795 foreach my $field (@uris) {
4796     my $ind1 = $field->indicator('1');
4797     if (defined $ind1) {
4798             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4799             $field->set_indicator(1,'4');
4800         }
4801     }
4802     my $ind2 = $field->indicator('2');
4803     if (defined $ind2) {
4804         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4805             $field->set_indicator(2,'0');
4806         }
4807     }
4808     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4809         $field->delete_subfield('9');
4810         $field->add_subfields( '9' => $new_value );
4811     }
4812     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4813 }
4814
4815 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4816 $marc_xml->insert_fields_ordered( values( %hash ) );
4817
4818 return $marc_xml->as_xml_record();
4819
4820 $function$;
4821
4822 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4823 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4824  RETURNS TEXT
4825  LANGUAGE plperlu
4826 AS $function$
4827 use strict;
4828 use warnings;
4829
4830 use MARC::Record;
4831 use MARC::File::XML (BinaryEncoding => 'utf8');
4832
4833 binmode(STDERR, ':bytes');
4834 binmode(STDOUT, ':utf8');
4835 binmode(STDERR, ':utf8');
4836
4837 my $marc_xml = shift;
4838 my $qualifying_match = shift;
4839 my $substring_old_value = shift;
4840 my $new_value = shift;
4841 my $fix_indicators = shift;
4842
4843 $marc_xml =~ s/(<leader>.........)./${1}a/;
4844
4845 eval {
4846     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4847 };
4848 if ($@) {
4849     #elog("could not parse $bibid: $@\n");
4850     import MARC::File::XML (BinaryEncoding => 'utf8');
4851     return $marc_xml;
4852 }
4853
4854 my @unqualified_uris = $marc_xml->field('856');
4855 my @uris = ();
4856 foreach my $field (@unqualified_uris) {
4857     if ($field->as_string() =~ qr/$qualifying_match/) {
4858         push @uris, $field;
4859     }
4860 }
4861 return $marc_xml->as_xml_record() unless @uris;
4862
4863 foreach my $field (@uris) {
4864     my $ind1 = $field->indicator('1');
4865     if (defined $ind1) {
4866             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4867             $field->set_indicator(1,'4');
4868         }
4869     }
4870     my $ind2 = $field->indicator('2');
4871     if (defined $ind2) {
4872         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4873             $field->set_indicator(2,'0');
4874         }
4875     }
4876     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4877         $field->delete_subfield('9');
4878         $field->add_subfields( '9' => $new_value );
4879     }
4880     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4881 }
4882
4883 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4884 $marc_xml->insert_fields_ordered( values( %hash ) );
4885
4886 return $marc_xml->as_xml_record();
4887
4888 $function$;
4889
4890 -- strip marc tag
4891 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4892 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4893  RETURNS TEXT
4894  LANGUAGE plperlu
4895 AS $function$
4896 use strict;
4897 use warnings;
4898
4899 use MARC::Record;
4900 use MARC::File::XML (BinaryEncoding => 'utf8');
4901
4902 binmode(STDERR, ':bytes');
4903 binmode(STDOUT, ':utf8');
4904 binmode(STDERR, ':utf8');
4905
4906 my $marc_xml = shift;
4907 my $tag = shift;
4908
4909 $marc_xml =~ s/(<leader>.........)./${1}a/;
4910
4911 eval {
4912     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4913 };
4914 if ($@) {
4915     #elog("could not parse $bibid: $@\n");
4916     import MARC::File::XML (BinaryEncoding => 'utf8');
4917     return $marc_xml;
4918 }
4919
4920 my @fields = $marc_xml->field($tag);
4921 return $marc_xml->as_xml_record() unless @fields;
4922
4923 $marc_xml->delete_fields(@fields);
4924
4925 return $marc_xml->as_xml_record();
4926
4927 $function$;
4928
4929 -- removes tags from record based on tag, subfield and evidence
4930 -- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
4931 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
4932 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence TEXT)
4933  RETURNS TEXT
4934  LANGUAGE plperlu
4935 AS $function$
4936 use strict;
4937 use warnings;
4938
4939 use MARC::Record;
4940 use MARC::File::XML (BinaryEncoding => 'utf8');
4941
4942 binmode(STDERR, ':bytes');
4943 binmode(STDOUT, ':utf8');
4944 binmode(STDERR, ':utf8');
4945
4946 my $marc_xml = shift;
4947 my $tag = shift;
4948 my $subfield = shift;
4949 my $evidence = shift;
4950
4951 $marc_xml =~ s/(<leader>.........)./${1}a/;
4952
4953 eval {
4954     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4955 };
4956 if ($@) {
4957     #elog("could not parse $bibid: $@\n");
4958     import MARC::File::XML (BinaryEncoding => 'utf8');
4959     return $marc_xml;
4960 }
4961
4962 my @fields = $marc_xml->field($tag);
4963 return $marc_xml->as_xml_record() unless @fields;
4964
4965 my @fields_to_delete;
4966
4967 foreach my $f (@fields) {
4968     my $sf = lc($f->as_string($subfield));
4969     if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
4970 }
4971
4972 $marc_xml->delete_fields(@fields_to_delete);
4973
4974 return $marc_xml->as_xml_record();
4975
4976 $function$;
4977
4978
4979 -- consolidate marc tag
4980 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
4981 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
4982  RETURNS TEXT
4983  LANGUAGE plperlu
4984 AS $function$
4985 use strict;
4986 use warnings;
4987
4988 use MARC::Record;
4989 use MARC::File::XML (BinaryEncoding => 'utf8');
4990
4991 binmode(STDERR, ':bytes');
4992 binmode(STDOUT, ':utf8');
4993 binmode(STDERR, ':utf8');
4994
4995 my $marc_xml = shift;
4996 my $tag = shift;
4997
4998 $marc_xml =~ s/(<leader>.........)./${1}a/;
4999
5000 eval {
5001     $marc_xml = MARC::Record->new_from_xml($marc_xml);
5002 };
5003 if ($@) {
5004     #elog("could not parse $bibid: $@\n");
5005     import MARC::File::XML (BinaryEncoding => 'utf8');
5006     return $marc_xml;
5007 }
5008
5009 my @fields = $marc_xml->field($tag);
5010 return $marc_xml->as_xml_record() unless @fields;
5011
5012 my @combined_subfield_refs = ();
5013 my @combined_subfields = ();
5014 foreach my $field (@fields) {
5015     my @subfield_refs = $field->subfields();
5016     push @combined_subfield_refs, @subfield_refs;
5017 }
5018
5019 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
5020
5021 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
5022     my ($code,$data) = @$tuple;
5023     unshift( @combined_subfields, $code, $data );
5024 }
5025
5026 $marc_xml->delete_fields(@fields);
5027
5028 my $new_field = new MARC::Field(
5029     $tag,
5030     $fields[0]->indicator(1),
5031     $fields[0]->indicator(2),
5032     @combined_subfields
5033 );
5034
5035 $marc_xml->insert_grouped_field( $new_field );
5036
5037 return $marc_xml->as_xml_record();
5038
5039 $function$;
5040
5041 -- convenience function for linking to the item staging table
5042
5043 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5044     DECLARE
5045         table_schema ALIAS FOR $1;
5046         table_name ALIAS FOR $2;
5047         foreign_column_name ALIAS FOR $3;
5048         main_column_name ALIAS FOR $4;
5049         btrim_desired ALIAS FOR $5;
5050         proceed BOOLEAN;
5051     BEGIN
5052         EXECUTE 'SELECT EXISTS (
5053             SELECT 1
5054             FROM information_schema.columns
5055             WHERE table_schema = $1
5056             AND table_name = $2
5057             and column_name = $3
5058         )' INTO proceed USING table_schema, table_name, foreign_column_name;
5059         IF NOT proceed THEN
5060             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5061         END IF;
5062
5063         EXECUTE 'SELECT EXISTS (
5064             SELECT 1
5065             FROM information_schema.columns
5066             WHERE table_schema = $1
5067             AND table_name = ''asset_copy_legacy''
5068             and column_name = $2
5069         )' INTO proceed USING table_schema, main_column_name;
5070         IF NOT proceed THEN
5071             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
5072         END IF;
5073
5074         EXECUTE 'ALTER TABLE '
5075             || quote_ident(table_name)
5076             || ' DROP COLUMN IF EXISTS x_item';
5077         EXECUTE 'ALTER TABLE '
5078             || quote_ident(table_name)
5079             || ' ADD COLUMN x_item BIGINT';
5080
5081         IF btrim_desired THEN
5082             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5083                 || ' SET x_item = b.id FROM asset_copy_legacy b'
5084                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5085                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5086         ELSE
5087             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5088                 || ' SET x_item = b.id FROM asset_copy_legacy b'
5089                 || ' WHERE a.' || quote_ident(foreign_column_name)
5090                 || ' = b.' || quote_ident(main_column_name);
5091         END IF;
5092
5093         --EXECUTE 'SELECT migration_tools.assert(
5094         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
5095         --    ''Cannot link every barcode'',
5096         --    ''Every barcode linked''
5097         --);';
5098
5099     END;
5100 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5101
5102 -- convenience function for linking to the user staging table
5103
5104 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5105     DECLARE
5106         table_schema ALIAS FOR $1;
5107         table_name ALIAS FOR $2;
5108         foreign_column_name ALIAS FOR $3;
5109         main_column_name ALIAS FOR $4;
5110         btrim_desired ALIAS FOR $5;
5111         proceed BOOLEAN;
5112     BEGIN
5113         EXECUTE 'SELECT EXISTS (
5114             SELECT 1
5115             FROM information_schema.columns
5116             WHERE table_schema = $1
5117             AND table_name = $2
5118             and column_name = $3
5119         )' INTO proceed USING table_schema, table_name, foreign_column_name;
5120         IF NOT proceed THEN
5121             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5122         END IF;
5123
5124         EXECUTE 'SELECT EXISTS (
5125             SELECT 1
5126             FROM information_schema.columns
5127             WHERE table_schema = $1
5128             AND table_name = ''actor_usr_legacy''
5129             and column_name = $2
5130         )' INTO proceed USING table_schema, main_column_name;
5131         IF NOT proceed THEN
5132             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
5133         END IF;
5134
5135         EXECUTE 'ALTER TABLE '
5136             || quote_ident(table_name)
5137             || ' DROP COLUMN IF EXISTS x_user';
5138         EXECUTE 'ALTER TABLE '
5139             || quote_ident(table_name)
5140             || ' ADD COLUMN x_user INTEGER';
5141
5142         IF btrim_desired THEN
5143             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5144                 || ' SET x_user = b.id FROM actor_usr_legacy b'
5145                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5146                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5147         ELSE
5148             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5149                 || ' SET x_user = b.id FROM actor_usr_legacy b'
5150                 || ' WHERE a.' || quote_ident(foreign_column_name)
5151                 || ' = b.' || quote_ident(main_column_name);
5152         END IF;
5153
5154         --EXECUTE 'SELECT migration_tools.assert(
5155         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
5156         --    ''Cannot link every barcode'',
5157         --    ''Every barcode linked''
5158         --);';
5159
5160     END;
5161 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5162
5163 -- convenience function for linking two tables
5164 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
5165 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5166     DECLARE
5167         table_schema ALIAS FOR $1;
5168         table_a ALIAS FOR $2;
5169         column_a ALIAS FOR $3;
5170         table_b ALIAS FOR $4;
5171         column_b ALIAS FOR $5;
5172         column_x ALIAS FOR $6;
5173         btrim_desired ALIAS FOR $7;
5174         proceed BOOLEAN;
5175     BEGIN
5176         EXECUTE 'SELECT EXISTS (
5177             SELECT 1
5178             FROM information_schema.columns
5179             WHERE table_schema = $1
5180             AND table_name = $2
5181             and column_name = $3
5182         )' INTO proceed USING table_schema, table_a, column_a;
5183         IF NOT proceed THEN
5184             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5185         END IF;
5186
5187         EXECUTE 'SELECT EXISTS (
5188             SELECT 1
5189             FROM information_schema.columns
5190             WHERE table_schema = $1
5191             AND table_name = $2
5192             and column_name = $3
5193         )' INTO proceed USING table_schema, table_b, column_b;
5194         IF NOT proceed THEN
5195             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5196         END IF;
5197
5198         EXECUTE 'ALTER TABLE '
5199             || quote_ident(table_b)
5200             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5201         EXECUTE 'ALTER TABLE '
5202             || quote_ident(table_b)
5203             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
5204
5205         IF btrim_desired THEN
5206             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5207                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5208                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5209                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5210         ELSE
5211             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5212                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5213                 || ' WHERE a.' || quote_ident(column_a)
5214                 || ' = b.' || quote_ident(column_b);
5215         END IF;
5216
5217     END;
5218 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5219
5220 -- convenience function for linking two tables, but copying column w into column x instead of "id"
5221 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
5222 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5223     DECLARE
5224         table_schema ALIAS FOR $1;
5225         table_a ALIAS FOR $2;
5226         column_a ALIAS FOR $3;
5227         table_b ALIAS FOR $4;
5228         column_b ALIAS FOR $5;
5229         column_w ALIAS FOR $6;
5230         column_x ALIAS FOR $7;
5231         btrim_desired ALIAS FOR $8;
5232         proceed BOOLEAN;
5233     BEGIN
5234         EXECUTE 'SELECT EXISTS (
5235             SELECT 1
5236             FROM information_schema.columns
5237             WHERE table_schema = $1
5238             AND table_name = $2
5239             and column_name = $3
5240         )' INTO proceed USING table_schema, table_a, column_a;
5241         IF NOT proceed THEN
5242             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5243         END IF;
5244
5245         EXECUTE 'SELECT EXISTS (
5246             SELECT 1
5247             FROM information_schema.columns
5248             WHERE table_schema = $1
5249             AND table_name = $2
5250             and column_name = $3
5251         )' INTO proceed USING table_schema, table_b, column_b;
5252         IF NOT proceed THEN
5253             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5254         END IF;
5255
5256         EXECUTE 'ALTER TABLE '
5257             || quote_ident(table_b)
5258             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5259         EXECUTE 'ALTER TABLE '
5260             || quote_ident(table_b)
5261             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
5262
5263         IF btrim_desired THEN
5264             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5265                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5266                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5267                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5268         ELSE
5269             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5270                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5271                 || ' WHERE a.' || quote_ident(column_a)
5272                 || ' = b.' || quote_ident(column_b);
5273         END IF;
5274
5275     END;
5276 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5277
5278 -- 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
5279 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
5280 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5281     DECLARE
5282         table_schema ALIAS FOR $1;
5283         table_a ALIAS FOR $2;
5284         column_a ALIAS FOR $3;
5285         table_b ALIAS FOR $4;
5286         column_b ALIAS FOR $5;
5287         column_w ALIAS FOR $6;
5288         column_x ALIAS FOR $7;
5289         proceed BOOLEAN;
5290     BEGIN
5291         EXECUTE 'SELECT EXISTS (
5292             SELECT 1
5293             FROM information_schema.columns
5294             WHERE table_schema = $1
5295             AND table_name = $2
5296             and column_name = $3
5297         )' INTO proceed USING table_schema, table_a, column_a;
5298         IF NOT proceed THEN
5299             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5300         END IF;
5301
5302         EXECUTE 'SELECT EXISTS (
5303             SELECT 1
5304             FROM information_schema.columns
5305             WHERE table_schema = $1
5306             AND table_name = $2
5307             and column_name = $3
5308         )' INTO proceed USING table_schema, table_b, column_b;
5309         IF NOT proceed THEN
5310             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5311         END IF;
5312
5313         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5314             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5315             || ' WHERE a.' || quote_ident(column_a)
5316             || ' = b.' || quote_ident(column_b);
5317
5318     END;
5319 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5320
5321 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5322     DECLARE
5323         table_schema ALIAS FOR $1;
5324         table_a ALIAS FOR $2;
5325         column_a ALIAS FOR $3;
5326         table_b ALIAS FOR $4;
5327         column_b ALIAS FOR $5;
5328         column_w ALIAS FOR $6;
5329         column_x ALIAS FOR $7;
5330         proceed BOOLEAN;
5331     BEGIN
5332         EXECUTE 'SELECT EXISTS (
5333             SELECT 1
5334             FROM information_schema.columns
5335             WHERE table_schema = $1
5336             AND table_name = $2
5337             and column_name = $3
5338         )' INTO proceed USING table_schema, table_a, column_a;
5339         IF NOT proceed THEN
5340             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5341         END IF;
5342
5343         EXECUTE 'SELECT EXISTS (
5344             SELECT 1
5345             FROM information_schema.columns
5346             WHERE table_schema = $1
5347             AND table_name = $2
5348             and column_name = $3
5349         )' INTO proceed USING table_schema, table_b, column_b;
5350         IF NOT proceed THEN
5351             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5352         END IF;
5353
5354         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5355             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5356             || ' WHERE a.' || quote_ident(column_a)
5357             || ' = b.' || quote_ident(column_b)
5358             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5359
5360     END;
5361 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5362
5363 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5364     DECLARE
5365         table_schema ALIAS FOR $1;
5366         table_a ALIAS FOR $2;
5367         column_a ALIAS FOR $3;
5368         table_b ALIAS FOR $4;
5369         column_b ALIAS FOR $5;
5370         column_w ALIAS FOR $6;
5371         column_x ALIAS FOR $7;
5372         proceed BOOLEAN;
5373     BEGIN
5374         EXECUTE 'SELECT EXISTS (
5375             SELECT 1
5376             FROM information_schema.columns
5377             WHERE table_schema = $1
5378             AND table_name = $2
5379             and column_name = $3
5380         )' INTO proceed USING table_schema, table_a, column_a;
5381         IF NOT proceed THEN
5382             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5383         END IF;
5384
5385         EXECUTE 'SELECT EXISTS (
5386             SELECT 1
5387             FROM information_schema.columns
5388             WHERE table_schema = $1
5389             AND table_name = $2
5390             and column_name = $3
5391         )' INTO proceed USING table_schema, table_b, column_b;
5392         IF NOT proceed THEN
5393             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5394         END IF;
5395
5396         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5397             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5398             || ' WHERE a.' || quote_ident(column_a)
5399             || ' = b.' || quote_ident(column_b)
5400             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5401
5402     END;
5403 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5404
5405 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5406     DECLARE
5407         table_schema ALIAS FOR $1;
5408         table_a ALIAS FOR $2;
5409         column_a ALIAS FOR $3;
5410         table_b ALIAS FOR $4;
5411         column_b ALIAS FOR $5;
5412         column_w ALIAS FOR $6;
5413         column_x ALIAS FOR $7;
5414         proceed BOOLEAN;
5415     BEGIN
5416         EXECUTE 'SELECT EXISTS (
5417             SELECT 1
5418             FROM information_schema.columns
5419             WHERE table_schema = $1
5420             AND table_name = $2
5421             and column_name = $3
5422         )' INTO proceed USING table_schema, table_a, column_a;
5423         IF NOT proceed THEN
5424             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5425         END IF;
5426
5427         EXECUTE 'SELECT EXISTS (
5428             SELECT 1
5429             FROM information_schema.columns
5430             WHERE table_schema = $1
5431             AND table_name = $2
5432             and column_name = $3
5433         )' INTO proceed USING table_schema, table_b, column_b;
5434         IF NOT proceed THEN
5435             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5436         END IF;
5437
5438         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5439             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5440             || ' WHERE a.' || quote_ident(column_a)
5441             || ' = b.' || quote_ident(column_b)
5442             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5443
5444     END;
5445 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5446
5447 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5448     DECLARE
5449         table_schema ALIAS FOR $1;
5450         table_a ALIAS FOR $2;
5451         column_a ALIAS FOR $3;
5452         table_b ALIAS FOR $4;
5453         column_b ALIAS FOR $5;
5454         column_w ALIAS FOR $6;
5455         column_x ALIAS FOR $7;
5456         proceed BOOLEAN;
5457     BEGIN
5458         EXECUTE 'SELECT EXISTS (
5459             SELECT 1
5460             FROM information_schema.columns
5461             WHERE table_schema = $1
5462             AND table_name = $2
5463             and column_name = $3
5464         )' INTO proceed USING table_schema, table_a, column_a;
5465         IF NOT proceed THEN
5466             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5467         END IF;
5468
5469         EXECUTE 'SELECT EXISTS (
5470             SELECT 1
5471             FROM information_schema.columns
5472             WHERE table_schema = $1
5473             AND table_name = $2
5474             and column_name = $3
5475         )' INTO proceed USING table_schema, table_b, column_b;
5476         IF NOT proceed THEN
5477             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5478         END IF;
5479
5480         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5481             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5482             || ' WHERE a.' || quote_ident(column_a)
5483             || ' = b.' || quote_ident(column_b)
5484             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5485
5486     END;
5487 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5488
5489 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5490     DECLARE
5491         table_schema ALIAS FOR $1;
5492         table_a ALIAS FOR $2;
5493         column_a ALIAS FOR $3;
5494         table_b ALIAS FOR $4;
5495         column_b ALIAS FOR $5;
5496         column_w ALIAS FOR $6;
5497         column_x ALIAS FOR $7;
5498         proceed BOOLEAN;
5499     BEGIN
5500         EXECUTE 'SELECT EXISTS (
5501             SELECT 1
5502             FROM information_schema.columns
5503             WHERE table_schema = $1
5504             AND table_name = $2
5505             and column_name = $3
5506         )' INTO proceed USING table_schema, table_a, column_a;
5507         IF NOT proceed THEN
5508             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5509         END IF;
5510
5511         EXECUTE 'SELECT EXISTS (
5512             SELECT 1
5513             FROM information_schema.columns
5514             WHERE table_schema = $1
5515             AND table_name = $2
5516             and column_name = $3
5517         )' INTO proceed USING table_schema, table_b, column_b;
5518         IF NOT proceed THEN
5519             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5520         END IF;
5521
5522         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5523             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5524             || ' WHERE a.' || quote_ident(column_a)
5525             || ' = b.' || quote_ident(column_b)
5526             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5527
5528     END;
5529 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5530
5531 -- convenience function for handling desired asset stat cats
5532
5533 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5534     DECLARE
5535         table_schema ALIAS FOR $1;
5536         table_name ALIAS FOR $2;
5537         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5538         org_shortname ALIAS FOR $4;
5539         proceed BOOLEAN;
5540         org INTEGER;
5541         org_list INTEGER[];
5542         sc TEXT;
5543         sce TEXT;
5544     BEGIN
5545
5546         SELECT 'desired_sc' || field_suffix INTO sc;
5547         SELECT 'desired_sce' || field_suffix INTO sce;
5548
5549         EXECUTE 'SELECT EXISTS (
5550             SELECT 1
5551             FROM information_schema.columns
5552             WHERE table_schema = $1
5553             AND table_name = $2
5554             and column_name = $3
5555         )' INTO proceed USING table_schema, table_name, sc;
5556         IF NOT proceed THEN
5557             RAISE EXCEPTION 'Missing column %', sc;
5558         END IF;
5559         EXECUTE 'SELECT EXISTS (
5560             SELECT 1
5561             FROM information_schema.columns
5562             WHERE table_schema = $1
5563             AND table_name = $2
5564             and column_name = $3
5565         )' INTO proceed USING table_schema, table_name, sce;
5566         IF NOT proceed THEN
5567             RAISE EXCEPTION 'Missing column %', sce;
5568         END IF;
5569
5570         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5571         IF org IS NULL THEN
5572             RAISE EXCEPTION 'Cannot find org by shortname';
5573         END IF;
5574         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5575
5576         -- caller responsible for their own truncates though we try to prevent duplicates
5577         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5578             SELECT DISTINCT
5579                  $1
5580                 ,BTRIM('||sc||')
5581             FROM
5582                 ' || quote_ident(table_name) || '
5583             WHERE
5584                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5585                 AND NOT EXISTS (
5586                     SELECT id
5587                     FROM asset.stat_cat
5588                     WHERE owner = ANY ($2)
5589                     AND name = BTRIM('||sc||')
5590                 )
5591                 AND NOT EXISTS (
5592                     SELECT id
5593                     FROM asset_stat_cat
5594                     WHERE owner = ANY ($2)
5595                     AND name = BTRIM('||sc||')
5596                 )
5597             ORDER BY 2;'
5598         USING org, org_list;
5599
5600         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5601             SELECT DISTINCT
5602                 COALESCE(
5603                     (SELECT id
5604                         FROM asset.stat_cat
5605                         WHERE owner = ANY ($2)
5606                         AND BTRIM('||sc||') = BTRIM(name))
5607                    ,(SELECT id
5608                         FROM asset_stat_cat
5609                         WHERE owner = ANY ($2)
5610                         AND BTRIM('||sc||') = BTRIM(name))
5611                 )
5612                 ,$1
5613                 ,BTRIM('||sce||')
5614             FROM
5615                 ' || quote_ident(table_name) || '
5616             WHERE
5617                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5618                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5619                 AND NOT EXISTS (
5620                     SELECT id
5621                     FROM asset.stat_cat_entry
5622                     WHERE stat_cat = (
5623                         SELECT id
5624                         FROM asset.stat_cat
5625                         WHERE owner = ANY ($2)
5626                         AND BTRIM('||sc||') = BTRIM(name)
5627                     ) AND value = BTRIM('||sce||')
5628                     AND owner = ANY ($2)
5629                 )
5630                 AND NOT EXISTS (
5631                     SELECT id
5632                     FROM asset_stat_cat_entry
5633                     WHERE stat_cat = (
5634                         SELECT id
5635                         FROM asset_stat_cat
5636                         WHERE owner = ANY ($2)
5637                         AND BTRIM('||sc||') = BTRIM(name)
5638                     ) AND value = BTRIM('||sce||')
5639                     AND owner = ANY ($2)
5640                 )
5641             ORDER BY 1,3;'
5642         USING org, org_list;
5643     END;
5644 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5645
5646 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5647     DECLARE
5648         table_schema ALIAS FOR $1;
5649         table_name ALIAS FOR $2;
5650         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5651         org_shortname ALIAS FOR $4;
5652         proceed BOOLEAN;
5653         org INTEGER;
5654         org_list INTEGER[];
5655         o INTEGER;
5656         sc TEXT;
5657         sce TEXT;
5658     BEGIN
5659         SELECT 'desired_sc' || field_suffix INTO sc;
5660         SELECT 'desired_sce' || field_suffix INTO sce;
5661         EXECUTE 'SELECT EXISTS (
5662             SELECT 1
5663             FROM information_schema.columns
5664             WHERE table_schema = $1
5665             AND table_name = $2
5666             and column_name = $3
5667         )' INTO proceed USING table_schema, table_name, sc;
5668         IF NOT proceed THEN
5669             RAISE EXCEPTION 'Missing column %', sc;
5670         END IF;
5671         EXECUTE 'SELECT EXISTS (
5672             SELECT 1
5673             FROM information_schema.columns
5674             WHERE table_schema = $1
5675             AND table_name = $2
5676             and column_name = $3
5677         )' INTO proceed USING table_schema, table_name, sce;
5678         IF NOT proceed THEN
5679             RAISE EXCEPTION 'Missing column %', sce;
5680         END IF;
5681
5682         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5683         IF org IS NULL THEN
5684             RAISE EXCEPTION 'Cannot find org by shortname';
5685         END IF;
5686
5687         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5688
5689         EXECUTE 'ALTER TABLE '
5690             || quote_ident(table_name)
5691             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5692         EXECUTE 'ALTER TABLE '
5693             || quote_ident(table_name)
5694             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5695         EXECUTE 'ALTER TABLE '
5696             || quote_ident(table_name)
5697             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5698         EXECUTE 'ALTER TABLE '
5699             || quote_ident(table_name)
5700             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5701
5702
5703         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5704             SET
5705                 x_sc' || field_suffix || ' = id
5706             FROM
5707                 (SELECT id, name, owner FROM asset_stat_cat
5708                     UNION SELECT id, name, owner FROM asset.stat_cat) u
5709             WHERE
5710                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5711                 AND u.owner = ANY ($1);'
5712         USING org_list;
5713
5714         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5715             SET
5716                 x_sce' || field_suffix || ' = id
5717             FROM
5718                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5719                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5720             WHERE
5721                     u.stat_cat = x_sc' || field_suffix || '
5722                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5723                 AND u.owner = ANY ($1);'
5724         USING org_list;
5725
5726         EXECUTE 'SELECT migration_tools.assert(
5727             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5728             ''Cannot find a desired stat cat'',
5729             ''Found all desired stat cats''
5730         );';
5731
5732         EXECUTE 'SELECT migration_tools.assert(
5733             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5734             ''Cannot find a desired stat cat entry'',
5735             ''Found all desired stat cat entries''
5736         );';
5737
5738     END;
5739 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5740
5741 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5742 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5743  LANGUAGE plpgsql
5744 AS $function$
5745 DECLARE
5746     c_name     TEXT;
5747 BEGIN
5748
5749     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5750             table_name = t_name
5751             AND table_schema = s_name
5752             AND (data_type='text' OR data_type='character varying')
5753             AND column_name like 'l_%'
5754     LOOP
5755        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5756     END LOOP;
5757
5758     RETURN TRUE;
5759 END
5760 $function$;
5761
5762 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5763 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5764  LANGUAGE plpgsql
5765 AS $function$
5766 DECLARE
5767     c_name     TEXT;
5768 BEGIN
5769
5770     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5771             table_name = t_name
5772             AND table_schema = s_name
5773             AND (data_type='text' OR data_type='character varying')
5774     LOOP
5775        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5776     END LOOP;
5777
5778     RETURN TRUE;
5779 END
5780 $function$;
5781
5782 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5783 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5784  LANGUAGE plpgsql
5785 AS $function$
5786 DECLARE
5787     c_name     TEXT;
5788 BEGIN
5789
5790     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5791             table_name = t_name
5792             AND table_schema = s_name
5793             AND (data_type='text' OR data_type='character varying')
5794             AND column_name like 'l_%'
5795     LOOP
5796        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5797     END LOOP;
5798
5799     RETURN TRUE;
5800 END
5801 $function$;
5802
5803 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5804 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5805  LANGUAGE plpgsql
5806 AS $function$
5807 DECLARE
5808     c_name     TEXT;
5809 BEGIN
5810
5811     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5812             table_name = t_name
5813             AND table_schema = s_name
5814             AND (data_type='text' OR data_type='character varying')
5815     LOOP
5816        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5817     END LOOP;
5818
5819     RETURN TRUE;
5820 END
5821 $function$;
5822
5823
5824 -- convenience function for handling item barcode collisions in asset_copy_legacy
5825
5826 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5827 DECLARE
5828     x_barcode TEXT;
5829     x_id BIGINT;
5830     row_count NUMERIC;
5831     internal_collision_count NUMERIC := 0;
5832     incumbent_collision_count NUMERIC := 0;
5833 BEGIN
5834     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5835     LOOP
5836         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5837         LOOP
5838             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5839             GET DIAGNOSTICS row_count = ROW_COUNT;
5840             internal_collision_count := internal_collision_count + row_count;
5841         END LOOP;
5842     END LOOP;
5843     RAISE INFO '% internal collisions', internal_collision_count;
5844     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
5845     LOOP
5846         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5847         LOOP
5848             UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5849             GET DIAGNOSTICS row_count = ROW_COUNT;
5850             incumbent_collision_count := incumbent_collision_count + row_count;
5851         END LOOP;
5852     END LOOP;
5853     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5854 END
5855 $function$ LANGUAGE plpgsql;
5856
5857 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5858 -- this should be ran prior to populating actor_card
5859
5860 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5861 DECLARE
5862     x_barcode TEXT;
5863     x_id BIGINT;
5864     row_count NUMERIC;
5865     internal_collision_count NUMERIC := 0;
5866     incumbent_barcode_collision_count NUMERIC := 0;
5867     incumbent_usrname_collision_count NUMERIC := 0;
5868 BEGIN
5869     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5870     LOOP
5871         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5872         LOOP
5873             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5874             GET DIAGNOSTICS row_count = ROW_COUNT;
5875             internal_collision_count := internal_collision_count + row_count;
5876         END LOOP;
5877     END LOOP;
5878     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5879
5880     FOR x_barcode IN
5881         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5882     LOOP
5883         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5884         LOOP
5885             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5886             GET DIAGNOSTICS row_count = ROW_COUNT;
5887             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5888         END LOOP;
5889     END LOOP;
5890     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5891
5892     FOR x_barcode IN
5893         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5894     LOOP
5895         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5896         LOOP
5897             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5898             GET DIAGNOSTICS row_count = ROW_COUNT;
5899             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5900         END LOOP;
5901     END LOOP;
5902     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5903 END
5904 $function$ LANGUAGE plpgsql;
5905
5906 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5907
5908 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5909 DECLARE
5910     x_barcode TEXT;
5911     x_id BIGINT;
5912     row_count NUMERIC;
5913     internal_collision_count NUMERIC := 0;
5914     incumbent_collision_count NUMERIC := 0;
5915 BEGIN
5916     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5917     LOOP
5918         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5919         LOOP
5920             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5921             GET DIAGNOSTICS row_count = ROW_COUNT;
5922             internal_collision_count := internal_collision_count + row_count;
5923         END LOOP;
5924     END LOOP;
5925     RAISE INFO '% internal collisions', internal_collision_count;
5926     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
5927     LOOP
5928         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5929         LOOP
5930             UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5931             GET DIAGNOSTICS row_count = ROW_COUNT;
5932             incumbent_collision_count := incumbent_collision_count + row_count;
5933         END LOOP;
5934     END LOOP;
5935     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5936 END
5937 $function$ LANGUAGE plpgsql;
5938
5939 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5940 -- this should be ran prior to populating actor_card
5941
5942 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5943 DECLARE
5944     x_barcode TEXT;
5945     x_id BIGINT;
5946     row_count NUMERIC;
5947     internal_collision_count NUMERIC := 0;
5948     incumbent_barcode_collision_count NUMERIC := 0;
5949     incumbent_usrname_collision_count NUMERIC := 0;
5950 BEGIN
5951     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5952     LOOP
5953         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5954         LOOP
5955             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5956             GET DIAGNOSTICS row_count = ROW_COUNT;
5957             internal_collision_count := internal_collision_count + row_count;
5958         END LOOP;
5959     END LOOP;
5960     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5961
5962     FOR x_barcode IN
5963         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5964     LOOP
5965         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5966         LOOP
5967             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5968             GET DIAGNOSTICS row_count = ROW_COUNT;
5969             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5970         END LOOP;
5971     END LOOP;
5972     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5973
5974     FOR x_barcode IN
5975         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5976     LOOP
5977         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5978         LOOP
5979             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5980             GET DIAGNOSTICS row_count = ROW_COUNT;
5981             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5982         END LOOP;
5983     END LOOP;
5984     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5985 END
5986 $function$ LANGUAGE plpgsql;
5987
5988 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5989 -- WARNING: Use at your own risk
5990 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5991 DECLARE
5992     item_object asset.copy%ROWTYPE;
5993     user_object actor.usr%ROWTYPE;
5994     test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5995     result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5996     safe_to_delete BOOLEAN := FALSE;
5997     m action.found_circ_matrix_matchpoint;
5998     n action.found_circ_matrix_matchpoint;
5999     -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
6000     result_matchpoint INTEGER;
6001 BEGIN
6002     SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
6003     RAISE INFO 'testing rule: %', test_rule_object;
6004
6005     INSERT INTO actor.usr (
6006         profile,
6007         usrname,
6008         passwd,
6009         ident_type,
6010         first_given_name,
6011         family_name,
6012         home_ou,
6013         juvenile
6014     ) SELECT
6015         COALESCE(test_rule_object.grp, 2),
6016         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6017         MD5(NOW()::TEXT),
6018         1,
6019         'Ima',
6020         'Test',
6021         COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
6022         COALESCE(test_rule_object.juvenile_flag, FALSE)
6023     ;
6024
6025     SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
6026
6027     INSERT INTO asset.call_number (
6028         creator,
6029         editor,
6030         record,
6031         owning_lib,
6032         label,
6033         label_class
6034     ) SELECT
6035         1,
6036         1,
6037         -1,
6038         COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
6039         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6040         1
6041     ;
6042
6043     INSERT INTO asset.copy (
6044         barcode,
6045         circ_lib,
6046         creator,
6047         call_number,
6048         editor,
6049         location,
6050         loan_duration,
6051         fine_level,
6052         ref,
6053         circ_modifier
6054     ) SELECT
6055         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6056         COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
6057         1,
6058         currval('asset.call_number_id_seq'),
6059         1,
6060         COALESCE(test_rule_object.copy_location,1),
6061         2,
6062         2,
6063         COALESCE(test_rule_object.ref_flag,FALSE),
6064         test_rule_object.circ_modifier
6065     ;
6066
6067     SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
6068
6069     SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
6070         test_rule_object.org_unit,
6071         item_object,
6072         user_object,
6073         COALESCE(test_rule_object.is_renewal,FALSE)
6074     );
6075     RAISE INFO '   action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6076         test_rule_object.org_unit,
6077         item_object.id,
6078         user_object.id,
6079         COALESCE(test_rule_object.is_renewal,FALSE),
6080         m.success,
6081         m.matchpoint,
6082         m.buildrows
6083     ;
6084
6085     --  disable the rule being tested to see if the outcome changes
6086     UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
6087
6088     SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
6089         test_rule_object.org_unit,
6090         item_object,
6091         user_object,
6092         COALESCE(test_rule_object.is_renewal,FALSE)
6093     );
6094     RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6095         test_rule_object.org_unit,
6096         item_object.id,
6097         user_object.id,
6098         COALESCE(test_rule_object.is_renewal,FALSE),
6099         n.success,
6100         n.matchpoint,
6101         n.buildrows
6102     ;
6103
6104     -- FIXME: We could dig deeper and see if the referenced config.rule_*
6105     -- entries are effectively equivalent, but for now, let's assume no
6106     -- duplicate rules at that level
6107     IF (
6108             (m.matchpoint).circulate = (n.matchpoint).circulate
6109         AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
6110         AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
6111         AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
6112         AND (
6113                 (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
6114                 OR (
6115                         (m.matchpoint).hard_due_date IS NULL
6116                     AND (n.matchpoint).hard_due_date IS NULL
6117                 )
6118         )
6119         AND (
6120                 (m.matchpoint).renewals = (n.matchpoint).renewals
6121                 OR (
6122                         (m.matchpoint).renewals IS NULL
6123                     AND (n.matchpoint).renewals IS NULL
6124                 )
6125         )
6126         AND (
6127                 (m.matchpoint).grace_period = (n.matchpoint).grace_period
6128                 OR (
6129                         (m.matchpoint).grace_period IS NULL
6130                     AND (n.matchpoint).grace_period IS NULL
6131                 )
6132         )
6133         AND (
6134                 (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
6135                 OR (
6136                         (m.matchpoint).total_copy_hold_ratio IS NULL
6137                     AND (n.matchpoint).total_copy_hold_ratio IS NULL
6138                 )
6139         )
6140         AND (
6141                 (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
6142                 OR (
6143                         (m.matchpoint).available_copy_hold_ratio IS NULL
6144                     AND (n.matchpoint).available_copy_hold_ratio IS NULL
6145                 )
6146         )
6147         AND NOT EXISTS (
6148             SELECT limit_set, fallthrough
6149             FROM config.circ_matrix_limit_set_map
6150             WHERE active and matchpoint = (m.matchpoint).id
6151             EXCEPT
6152             SELECT limit_set, fallthrough
6153             FROM config.circ_matrix_limit_set_map
6154             WHERE active and matchpoint = (n.matchpoint).id
6155         )
6156
6157     ) THEN
6158         RAISE INFO 'rule has same outcome';
6159         safe_to_delete := TRUE;
6160     ELSE
6161         RAISE INFO 'rule has different outcome';
6162         safe_to_delete := FALSE;
6163     END IF;
6164
6165     RAISE EXCEPTION 'rollback the temporary changes';
6166
6167 EXCEPTION WHEN OTHERS THEN
6168
6169     RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
6170     RETURN safe_to_delete;
6171
6172 END;
6173 $func$ LANGUAGE plpgsql;
6174