sql/base/base.sql

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