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                  -> 001
1721
1722 RETURN
1723     migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
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             '001' as "l_tag",
1827             '' 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 -- add koha holding tag to marc
1840 DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
1841
1842 CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber TEXT)
1843  RETURNS TEXT
1844  LANGUAGE plperlu
1845 AS $function$
1846 use strict;
1847 use warnings;
1848
1849 use MARC::Record;
1850 use MARC::File::XML (BinaryEncoding => 'utf8');
1851
1852 binmode(STDERR, ':bytes');
1853 binmode(STDOUT, ':utf8');
1854 binmode(STDERR, ':utf8');
1855
1856 my ($marc_xml, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
1857
1858 $marc_xml =~ s/(<leader>.........)./${1}a/;
1859
1860 eval {
1861     $marc_xml = MARC::Record->new_from_xml($marc_xml);
1862 };
1863 if ($@) {
1864     #elog("could not parse $bibid: $@\n");
1865     import MARC::File::XML (BinaryEncoding => 'utf8');
1866     return $marc_xml;
1867 }
1868
1869 my $new_field = new MARC::Field(
1870     $tag, $ind1, $ind2,
1871     'a' => $homebranch,
1872     'b' => $holdingbranch,
1873     'c' => $location,
1874     'p' => $barcode,
1875     'y' => $itype
1876 );
1877
1878 if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
1879 if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
1880 if ($price) { $new_field->add_subfields('g' => $price); }
1881 if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
1882 if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
1883 if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
1884 if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
1885 if ($stack) { $new_field->add_subfields('j' => $stack); }
1886 if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
1887 if ($damaged) { $new_field->add_subfields('4' => $damaged); }
1888 if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
1889 if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
1890 if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
1891 if ($issues) { $new_field->add_subfields('l' => $issues); }
1892 if ($renewals) { $new_field->add_subfields('m' => $renewals); }
1893 if ($reserves) { $new_field->add_subfields('n' => $reserves); }
1894 if ($restricted) { $new_field->add_subfields('5' => $restricted); }
1895 if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
1896 if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
1897 if ($onloan) { $new_field->add_subfields('q' => $onloan); }
1898 if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
1899 if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
1900 if ($ccode) { $new_field->add_subfields('8' => $ccode); }
1901 if ($materials) { $new_field->add_subfields('3' => $materials); }
1902 if ($uri) { $new_field->add_subfields('u' => $uri); }
1903 if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
1904 if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
1905 if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
1906
1907 $marc_xml->insert_grouped_field( $new_field );
1908
1909 return $marc_xml->as_xml_record();
1910
1911 $function$;
1912
1913 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1914     DECLARE
1915         attempt_value ALIAS FOR $1;
1916         fail_value ALIAS FOR $2;
1917         output NUMERIC(8,2);
1918     BEGIN
1919         FOR output IN
1920             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1921         LOOP
1922             RETURN output;
1923         END LOOP;
1924     EXCEPTION
1925         WHEN OTHERS THEN
1926             FOR output IN
1927                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1928             LOOP
1929                 RETURN output;
1930             END LOOP;
1931     END;
1932 $$ LANGUAGE PLPGSQL STRICT STABLE;
1933
1934 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1935     DECLARE
1936         attempt_value ALIAS FOR $1;
1937         fail_value ALIAS FOR $2;
1938         output NUMERIC(6,2);
1939     BEGIN
1940         FOR output IN
1941             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1942         LOOP
1943             RETURN output;
1944         END LOOP;
1945     EXCEPTION
1946         WHEN OTHERS THEN
1947             FOR output IN
1948                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1949             LOOP
1950                 RETURN output;
1951             END LOOP;
1952     END;
1953 $$ LANGUAGE PLPGSQL STRICT STABLE;
1954
1955 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1956     DECLARE
1957         attempt_value ALIAS FOR $1;
1958         fail_value ALIAS FOR $2;
1959         output NUMERIC(8,2);
1960     BEGIN
1961         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1962             RAISE EXCEPTION 'too many digits';
1963         END IF;
1964         FOR output IN
1965             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;'
1966         LOOP
1967             RETURN output;
1968         END LOOP;
1969     EXCEPTION
1970         WHEN OTHERS THEN
1971             FOR output IN
1972                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1973             LOOP
1974                 RETURN output;
1975             END LOOP;
1976     END;
1977 $$ LANGUAGE PLPGSQL STRICT STABLE;
1978
1979 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1980     DECLARE
1981         attempt_value ALIAS FOR $1;
1982         fail_value ALIAS FOR $2;
1983         output NUMERIC(6,2);
1984     BEGIN
1985         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1986             RAISE EXCEPTION 'too many digits';
1987         END IF;
1988         FOR output IN
1989             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;'
1990         LOOP
1991             RETURN output;
1992         END LOOP;
1993     EXCEPTION
1994         WHEN OTHERS THEN
1995             FOR output IN
1996                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1997             LOOP
1998                 RETURN output;
1999             END LOOP;
2000     END;
2001 $$ LANGUAGE PLPGSQL STRICT STABLE;
2002
2003 -- add_codabar_checkdigit
2004 --   $barcode      source barcode
2005 --
2006 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
2007 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2008 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
2009 -- input string does not meet those requirements, it is returned unchanged.
2010 --
2011 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
2012     my $barcode = shift;
2013
2014     return $barcode if $barcode !~ /^\d{13,14}$/;
2015     $barcode = substr($barcode, 0, 13); # ignore 14th digit
2016     my @digits = split //, $barcode;
2017     my $total = 0;
2018     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
2019     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
2020     my $remainder = $total % 10;
2021     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
2022     return $barcode . $checkdigit;
2023 $$ LANGUAGE PLPERLU STRICT STABLE;
2024
2025 -- add_code39mod43_checkdigit
2026 --   $barcode      source barcode
2027 --
2028 -- If the source string is 13 or 14 characters long and contains only valid
2029 -- Code 39 mod 43 characters, adds or replaces the 14th
2030 -- character with a checkdigit computed according to the usual algorithm for library barcodes
2031 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
2032 -- input string does not meet those requirements, it is returned unchanged.
2033 --
2034 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
2035     my $barcode = shift;
2036
2037     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
2038     $barcode = substr($barcode, 0, 13); # ignore 14th character
2039
2040     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
2041     my %nums = map { $valid_chars[$_] => $_ } (0..42);
2042
2043     my $total = 0;
2044     $total += $nums{$_} foreach split(//, $barcode);
2045     my $remainder = $total % 43;
2046     my $checkdigit = $valid_chars[$remainder];
2047     return $barcode . $checkdigit;
2048 $$ LANGUAGE PLPERLU STRICT STABLE;
2049
2050 -- add_mod16_checkdigit
2051 --   $barcode      source barcode
2052 --
2053 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
2054
2055 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
2056     my $barcode = shift;
2057
2058     my @digits = split //, $barcode;
2059     my $total = 0;
2060     foreach $digit (@digits) {
2061         if ($digit =~ /[0-9]/) { $total += $digit;
2062         } elsif ($digit eq '-') { $total += 10;
2063         } elsif ($digit eq '$') { $total += 11;
2064         } elsif ($digit eq ':') { $total += 12;
2065         } elsif ($digit eq '/') { $total += 13;
2066         } elsif ($digit eq '.') { $total += 14;
2067         } elsif ($digit eq '+') { $total += 15;
2068         } elsif ($digit eq 'A') { $total += 16;
2069         } elsif ($digit eq 'B') { $total += 17;
2070         } elsif ($digit eq 'C') { $total += 18;
2071         } elsif ($digit eq 'D') { $total += 19;
2072         } else { die "invalid digit <$digit>";
2073         }
2074     }
2075     my $remainder = $total % 16;
2076     my $difference = 16 - $remainder;
2077     my $checkdigit;
2078     if ($difference < 10) { $checkdigit = $difference;
2079     } elsif ($difference == 10) { $checkdigit = '-';
2080     } elsif ($difference == 11) { $checkdigit = '$';
2081     } elsif ($difference == 12) { $checkdigit = ':';
2082     } elsif ($difference == 13) { $checkdigit = '/';
2083     } elsif ($difference == 14) { $checkdigit = '.';
2084     } elsif ($difference == 15) { $checkdigit = '+';
2085     } else { die "error calculating checkdigit";
2086     }
2087
2088     return $barcode . $checkdigit;
2089 $$ LANGUAGE PLPERLU STRICT STABLE;
2090
2091 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
2092   DECLARE
2093     phone TEXT := $1;
2094     areacode TEXT := $2;
2095     temp TEXT := '';
2096     output TEXT := '';
2097     n_digits INTEGER := 0;
2098   BEGIN
2099     temp := phone;
2100     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
2101     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
2102     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
2103     IF n_digits = 7 AND areacode <> '' THEN
2104       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
2105       output := (areacode || '-' || temp);
2106     ELSE
2107       output := temp;
2108     END IF;
2109     RETURN output;
2110   END;
2111
2112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2113
2114 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2115   my ($marcxml, $pos, $value) = @_;
2116
2117   use MARC::Record;
2118   use MARC::File::XML;
2119
2120   my $xml = $marcxml;
2121   eval {
2122     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2123     my $leader = $marc->leader();
2124     substr($leader, $pos, 1) = $value;
2125     $marc->leader($leader);
2126     $xml = $marc->as_xml_record;
2127     $xml =~ s/^<\?.+?\?>$//mo;
2128     $xml =~ s/\n//sgo;
2129     $xml =~ s/>\s+</></sgo;
2130   };
2131   return $xml;
2132 $$ LANGUAGE PLPERLU STABLE;
2133
2134 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
2135   my ($marcxml, $pos, $value) = @_;
2136
2137   use MARC::Record;
2138   use MARC::File::XML;
2139
2140   my $xml = $marcxml;
2141   eval {
2142     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2143     my $f008 = $marc->field('008');
2144
2145     if ($f008) {
2146        my $field = $f008->data();
2147        substr($field, $pos, 1) = $value;
2148        $f008->update($field);
2149        $xml = $marc->as_xml_record;
2150        $xml =~ s/^<\?.+?\?>$//mo;
2151        $xml =~ s/\n//sgo;
2152        $xml =~ s/>\s+</></sgo;
2153     }
2154   };
2155   return $xml;
2156 $$ LANGUAGE PLPERLU STABLE;
2157
2158
2159 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
2160   DECLARE
2161     profile ALIAS FOR $1;
2162   BEGIN
2163     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
2164   END;
2165 $$ LANGUAGE PLPGSQL STRICT STABLE;
2166
2167
2168 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
2169   BEGIN
2170     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
2171   END;
2172 $$ LANGUAGE PLPGSQL STRICT STABLE;
2173
2174
2175 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
2176
2177   my ($marcxml, $tags) = @_;
2178
2179   use MARC::Record;
2180   use MARC::File::XML;
2181
2182   my $xml = $marcxml;
2183
2184   eval {
2185     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2186     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
2187
2188     my @incumbents = ();
2189
2190     foreach my $field ( $marc->fields() ) {
2191       push @incumbents, $field->as_formatted();
2192     }
2193
2194     foreach $field ( $to_insert->fields() ) {
2195       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
2196         $marc->insert_fields_ordered( ($field) );
2197       }
2198     }
2199
2200     $xml = $marc->as_xml_record;
2201     $xml =~ s/^<\?.+?\?>$//mo;
2202     $xml =~ s/\n//sgo;
2203     $xml =~ s/>\s+</></sgo;
2204   };
2205
2206   return $xml;
2207
2208 $$ LANGUAGE PLPERLU STABLE;
2209
2210 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
2211
2212 -- Usage:
2213 --
2214 --   First make sure the circ matrix is loaded and the circulations
2215 --   have been staged to the extent possible (but at the very least
2216 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2217 --   circ modifiers must also be in place.
2218 --
2219 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2220 --
2221
2222 DECLARE
2223   circ_lib             INT;
2224   target_copy          INT;
2225   usr                  INT;
2226   is_renewal           BOOLEAN;
2227   this_duration_rule   INT;
2228   this_fine_rule       INT;
2229   this_max_fine_rule   INT;
2230   rcd                  config.rule_circ_duration%ROWTYPE;
2231   rrf                  config.rule_recurring_fine%ROWTYPE;
2232   rmf                  config.rule_max_fine%ROWTYPE;
2233   circ                 INT;
2234   n                    INT := 0;
2235   n_circs              INT;
2236
2237 BEGIN
2238
2239   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2240
2241   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2242
2243     -- Fetch the correct rules for this circulation
2244     EXECUTE ('
2245       SELECT
2246         circ_lib,
2247         target_copy,
2248         usr,
2249         CASE
2250           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2251           ELSE FALSE
2252         END
2253       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2254       INTO circ_lib, target_copy, usr, is_renewal ;
2255     SELECT
2256       INTO this_duration_rule,
2257            this_fine_rule,
2258            this_max_fine_rule
2259       duration_rule,
2260       recurring_fine_rule,
2261       max_fine_rule
2262       FROM action.item_user_circ_test(
2263         circ_lib,
2264         target_copy,
2265         usr,
2266         is_renewal
2267         );
2268     SELECT INTO rcd * FROM config.rule_circ_duration
2269       WHERE id = this_duration_rule;
2270     SELECT INTO rrf * FROM config.rule_recurring_fine
2271       WHERE id = this_fine_rule;
2272     SELECT INTO rmf * FROM config.rule_max_fine
2273       WHERE id = this_max_fine_rule;
2274
2275     -- Apply the rules to this circulation
2276     EXECUTE ('UPDATE ' || tablename || ' c
2277     SET
2278       duration_rule = rcd.name,
2279       recurring_fine_rule = rrf.name,
2280       max_fine_rule = rmf.name,
2281       duration = rcd.normal,
2282       recurring_fine = rrf.normal,
2283       max_fine =
2284         CASE rmf.is_percent
2285           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2286           ELSE rmf.amount
2287         END,
2288       renewal_remaining = rcd.max_renewals
2289     FROM
2290       config.rule_circ_duration rcd,
2291       config.rule_recurring_fine rrf,
2292       config.rule_max_fine rmf,
2293                         asset.copy ac
2294     WHERE
2295       rcd.id = ' || this_duration_rule || ' AND
2296       rrf.id = ' || this_fine_rule || ' AND
2297       rmf.id = ' || this_max_fine_rule || ' AND
2298                         ac.id = c.target_copy AND
2299       c.id = ' || circ || ';');
2300
2301     -- Keep track of where we are in the process
2302     n := n + 1;
2303     IF (n % 100 = 0) THEN
2304       RAISE INFO '%', n || ' of ' || n_circs
2305         || ' (' || (100*n/n_circs) || '%) circs updated.';
2306     END IF;
2307
2308   END LOOP;
2309
2310   RETURN;
2311 END;
2312
2313 $$ LANGUAGE plpgsql;
2314
2315 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
2316
2317 -- Usage:
2318 --
2319 --   First make sure the circ matrix is loaded and the circulations
2320 --   have been staged to the extent possible (but at the very least
2321 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2322 --   circ modifiers must also be in place.
2323 --
2324 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2325 --
2326
2327 DECLARE
2328   circ_lib             INT;
2329   target_copy          INT;
2330   usr                  INT;
2331   is_renewal           BOOLEAN;
2332   this_duration_rule   INT;
2333   this_fine_rule       INT;
2334   this_max_fine_rule   INT;
2335   rcd                  config.rule_circ_duration%ROWTYPE;
2336   rrf                  config.rule_recurring_fine%ROWTYPE;
2337   rmf                  config.rule_max_fine%ROWTYPE;
2338   circ                 INT;
2339   n                    INT := 0;
2340   n_circs              INT;
2341
2342 BEGIN
2343
2344   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2345
2346   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2347
2348     -- Fetch the correct rules for this circulation
2349     EXECUTE ('
2350       SELECT
2351         circ_lib,
2352         target_copy,
2353         usr,
2354         CASE
2355           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2356           ELSE FALSE
2357         END
2358       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2359       INTO circ_lib, target_copy, usr, is_renewal ;
2360     SELECT
2361       INTO this_duration_rule,
2362            this_fine_rule,
2363            this_max_fine_rule
2364       duration_rule,
2365       recuring_fine_rule,
2366       max_fine_rule
2367       FROM action.find_circ_matrix_matchpoint(
2368         circ_lib,
2369         target_copy,
2370         usr,
2371         is_renewal
2372         );
2373     SELECT INTO rcd * FROM config.rule_circ_duration
2374       WHERE id = this_duration_rule;
2375     SELECT INTO rrf * FROM config.rule_recurring_fine
2376       WHERE id = this_fine_rule;
2377     SELECT INTO rmf * FROM config.rule_max_fine
2378       WHERE id = this_max_fine_rule;
2379
2380     -- Apply the rules to this circulation
2381     EXECUTE ('UPDATE ' || tablename || ' c
2382     SET
2383       duration_rule = rcd.name,
2384       recuring_fine_rule = rrf.name,
2385       max_fine_rule = rmf.name,
2386       duration = rcd.normal,
2387       recuring_fine = rrf.normal,
2388       max_fine =
2389         CASE rmf.is_percent
2390           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2391           ELSE rmf.amount
2392         END,
2393       renewal_remaining = rcd.max_renewals
2394     FROM
2395       config.rule_circ_duration rcd,
2396       config.rule_recuring_fine rrf,
2397       config.rule_max_fine rmf,
2398                         asset.copy ac
2399     WHERE
2400       rcd.id = ' || this_duration_rule || ' AND
2401       rrf.id = ' || this_fine_rule || ' AND
2402       rmf.id = ' || this_max_fine_rule || ' AND
2403                         ac.id = c.target_copy AND
2404       c.id = ' || circ || ';');
2405
2406     -- Keep track of where we are in the process
2407     n := n + 1;
2408     IF (n % 100 = 0) THEN
2409       RAISE INFO '%', n || ' of ' || n_circs
2410         || ' (' || (100*n/n_circs) || '%) circs updated.';
2411     END IF;
2412
2413   END LOOP;
2414
2415   RETURN;
2416 END;
2417
2418 $$ LANGUAGE plpgsql;
2419
2420 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2421
2422 -- Usage:
2423 --
2424 --   First make sure the circ matrix is loaded and the circulations
2425 --   have been staged to the extent possible (but at the very least
2426 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2427 --   circ modifiers must also be in place.
2428 --
2429 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2430 --
2431
2432 DECLARE
2433   circ_lib             INT;
2434   target_copy          INT;
2435   usr                  INT;
2436   is_renewal           BOOLEAN;
2437   this_duration_rule   INT;
2438   this_fine_rule       INT;
2439   this_max_fine_rule   INT;
2440   rcd                  config.rule_circ_duration%ROWTYPE;
2441   rrf                  config.rule_recurring_fine%ROWTYPE;
2442   rmf                  config.rule_max_fine%ROWTYPE;
2443   circ                 INT;
2444   n                    INT := 0;
2445   n_circs              INT;
2446
2447 BEGIN
2448
2449   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2450
2451   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2452
2453     -- Fetch the correct rules for this circulation
2454     EXECUTE ('
2455       SELECT
2456         circ_lib,
2457         target_copy,
2458         usr,
2459         CASE
2460           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2461           ELSE FALSE
2462         END
2463       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2464       INTO circ_lib, target_copy, usr, is_renewal ;
2465     SELECT
2466       INTO this_duration_rule,
2467            this_fine_rule,
2468            this_max_fine_rule
2469       (matchpoint).duration_rule,
2470       (matchpoint).recurring_fine_rule,
2471       (matchpoint).max_fine_rule
2472       FROM action.find_circ_matrix_matchpoint(
2473         circ_lib,
2474         target_copy,
2475         usr,
2476         is_renewal
2477         );
2478     SELECT INTO rcd * FROM config.rule_circ_duration
2479       WHERE id = this_duration_rule;
2480     SELECT INTO rrf * FROM config.rule_recurring_fine
2481       WHERE id = this_fine_rule;
2482     SELECT INTO rmf * FROM config.rule_max_fine
2483       WHERE id = this_max_fine_rule;
2484
2485     -- Apply the rules to this circulation
2486     EXECUTE ('UPDATE ' || tablename || ' c
2487     SET
2488       duration_rule = rcd.name,
2489       recurring_fine_rule = rrf.name,
2490       max_fine_rule = rmf.name,
2491       duration = rcd.normal,
2492       recurring_fine = rrf.normal,
2493       max_fine =
2494         CASE rmf.is_percent
2495           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2496           ELSE rmf.amount
2497         END,
2498       renewal_remaining = rcd.max_renewals,
2499       grace_period = rrf.grace_period
2500     FROM
2501       config.rule_circ_duration rcd,
2502       config.rule_recurring_fine rrf,
2503       config.rule_max_fine rmf,
2504                         asset.copy ac
2505     WHERE
2506       rcd.id = ' || this_duration_rule || ' AND
2507       rrf.id = ' || this_fine_rule || ' AND
2508       rmf.id = ' || this_max_fine_rule || ' AND
2509                         ac.id = c.target_copy AND
2510       c.id = ' || circ || ';');
2511
2512     -- Keep track of where we are in the process
2513     n := n + 1;
2514     IF (n % 100 = 0) THEN
2515       RAISE INFO '%', n || ' of ' || n_circs
2516         || ' (' || (100*n/n_circs) || '%) circs updated.';
2517     END IF;
2518
2519   END LOOP;
2520
2521   RETURN;
2522 END;
2523
2524 $$ LANGUAGE plpgsql;
2525
2526 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2527 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2528 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2529 DECLARE
2530     context_lib             INT;
2531     charge_lost_on_zero     BOOLEAN;
2532     min_price               NUMERIC;
2533     max_price               NUMERIC;
2534     default_price           NUMERIC;
2535     working_price           NUMERIC;
2536
2537 BEGIN
2538
2539     SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2540         FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2541
2542     SELECT INTO charge_lost_on_zero value
2543         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2544
2545     SELECT INTO min_price value
2546         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2547
2548     SELECT INTO max_price value
2549         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2550
2551     SELECT INTO default_price value
2552         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2553
2554     SELECT INTO working_price price FROM asset.copy WHERE id = item;
2555
2556     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2557         working_price := default_price;
2558     END IF;
2559
2560     IF (max_price IS NOT NULL AND working_price > max_price) THEN
2561         working_price := max_price;
2562     END IF;
2563
2564     IF (min_price IS NOT NULL AND working_price < min_price) THEN
2565         IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2566             working_price := min_price;
2567         END IF;
2568     END IF;
2569
2570     RETURN working_price;
2571
2572 END;
2573
2574 $$ LANGUAGE plpgsql;
2575
2576 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2577
2578 -- Usage:
2579 --
2580 --   First make sure the circ matrix is loaded and the circulations
2581 --   have been staged to the extent possible (but at the very least
2582 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2583 --   circ modifiers must also be in place.
2584 --
2585 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2586 --
2587
2588 DECLARE
2589   circ_lib             INT;
2590   target_copy          INT;
2591   usr                  INT;
2592   is_renewal           BOOLEAN;
2593   this_duration_rule   INT;
2594   this_fine_rule       INT;
2595   this_max_fine_rule   INT;
2596   rcd                  config.rule_circ_duration%ROWTYPE;
2597   rrf                  config.rule_recurring_fine%ROWTYPE;
2598   rmf                  config.rule_max_fine%ROWTYPE;
2599   n                    INT := 0;
2600   n_circs              INT := 1;
2601
2602 BEGIN
2603
2604   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2605
2606   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2607
2608     -- Fetch the correct rules for this circulation
2609     EXECUTE ('
2610       SELECT
2611         circ_lib,
2612         target_copy,
2613         usr,
2614         CASE
2615           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2616           ELSE FALSE
2617         END
2618       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2619       INTO circ_lib, target_copy, usr, is_renewal ;
2620     SELECT
2621       INTO this_duration_rule,
2622            this_fine_rule,
2623            this_max_fine_rule
2624       (matchpoint).duration_rule,
2625       (matchpoint).recurring_fine_rule,
2626       (matchpoint).max_fine_rule
2627       FROM action.find_circ_matrix_matchpoint(
2628         circ_lib,
2629         target_copy,
2630         usr,
2631         is_renewal
2632         );
2633     SELECT INTO rcd * FROM config.rule_circ_duration
2634       WHERE id = this_duration_rule;
2635     SELECT INTO rrf * FROM config.rule_recurring_fine
2636       WHERE id = this_fine_rule;
2637     SELECT INTO rmf * FROM config.rule_max_fine
2638       WHERE id = this_max_fine_rule;
2639
2640     -- Apply the rules to this circulation
2641     EXECUTE ('UPDATE ' || tablename || ' c
2642     SET
2643       duration_rule = rcd.name,
2644       recurring_fine_rule = rrf.name,
2645       max_fine_rule = rmf.name,
2646       duration = rcd.normal,
2647       recurring_fine = rrf.normal,
2648       max_fine =
2649         CASE rmf.is_percent
2650           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2651           ELSE rmf.amount
2652         END,
2653       renewal_remaining = rcd.max_renewals,
2654       grace_period = rrf.grace_period
2655     FROM
2656       config.rule_circ_duration rcd,
2657       config.rule_recurring_fine rrf,
2658       config.rule_max_fine rmf,
2659                         asset.copy ac
2660     WHERE
2661       rcd.id = ' || this_duration_rule || ' AND
2662       rrf.id = ' || this_fine_rule || ' AND
2663       rmf.id = ' || this_max_fine_rule || ' AND
2664                         ac.id = c.target_copy AND
2665       c.id = ' || circ || ';');
2666
2667     -- Keep track of where we are in the process
2668     n := n + 1;
2669     IF (n % 100 = 0) THEN
2670       RAISE INFO '%', n || ' of ' || n_circs
2671         || ' (' || (100*n/n_circs) || '%) circs updated.';
2672     END IF;
2673
2674   --END LOOP;
2675
2676   RETURN;
2677 END;
2678
2679 $$ LANGUAGE plpgsql;
2680
2681
2682
2683
2684 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2685
2686 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2687 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2688
2689 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2690 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2691
2692 DECLARE
2693         c                    TEXT := schemaname || '.asset_copy_legacy';
2694         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
2695         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
2696         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2697         stat_cat                                                 INT;
2698   stat_cat_entry       INT;
2699
2700 BEGIN
2701
2702   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2703
2704                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2705
2706                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2707                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2708                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2709
2710   END LOOP;
2711
2712   RETURN;
2713 END;
2714
2715 $$ LANGUAGE plpgsql;
2716
2717 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2718
2719 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2720 --        This will assign standing penalties as needed.
2721
2722 DECLARE
2723   org_unit  INT;
2724   usr       INT;
2725
2726 BEGIN
2727
2728   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2729
2730     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2731
2732       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2733
2734     END LOOP;
2735
2736   END LOOP;
2737
2738   RETURN;
2739
2740 END;
2741
2742 $$ LANGUAGE plpgsql;
2743
2744
2745 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2746
2747 BEGIN
2748   INSERT INTO metabib.metarecord (fingerprint, master_record)
2749     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2750       FROM  biblio.record_entry b
2751       WHERE NOT b.deleted
2752         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)
2753         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2754       ORDER BY b.fingerprint, b.quality DESC;
2755   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2756     SELECT  m.id, r.id
2757       FROM  biblio.record_entry r
2758       JOIN  metabib.metarecord m USING (fingerprint)
2759      WHERE  NOT r.deleted;
2760 END;
2761
2762 $$ LANGUAGE plpgsql;
2763
2764
2765 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2766
2767 BEGIN
2768   INSERT INTO metabib.metarecord (fingerprint, master_record)
2769     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2770       FROM  biblio.record_entry b
2771       WHERE NOT b.deleted
2772         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)
2773         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2774       ORDER BY b.fingerprint, b.quality DESC;
2775   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2776     SELECT  m.id, r.id
2777       FROM  biblio.record_entry r
2778         JOIN metabib.metarecord m USING (fingerprint)
2779       WHERE NOT r.deleted
2780         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);
2781 END;
2782
2783 $$ LANGUAGE plpgsql;
2784
2785
2786 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2787
2788 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2789 --        Then SELECT migration_tools.create_cards('m_foo');
2790
2791 DECLARE
2792         u                    TEXT := schemaname || '.actor_usr_legacy';
2793         c                    TEXT := schemaname || '.actor_card';
2794
2795 BEGIN
2796
2797         EXECUTE ('DELETE FROM ' || c || ';');
2798         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2799         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2800
2801   RETURN;
2802
2803 END;
2804
2805 $$ LANGUAGE plpgsql;
2806
2807
2808 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2809
2810   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2811
2812   my ($marcxml, $shortname) = @_;
2813
2814   use MARC::Record;
2815   use MARC::File::XML;
2816
2817   my $xml = $marcxml;
2818
2819   eval {
2820     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2821
2822     foreach my $field ( $marc->field('856') ) {
2823       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2824            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2825         $field->add_subfields( '9' => $shortname );
2826                                 $field->update( ind2 => '0');
2827       }
2828     }
2829
2830     $xml = $marc->as_xml_record;
2831     $xml =~ s/^<\?.+?\?>$//mo;
2832     $xml =~ s/\n//sgo;
2833     $xml =~ s/>\s+</></sgo;
2834   };
2835
2836   return $xml;
2837
2838 $$ LANGUAGE PLPERLU STABLE;
2839
2840 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2841
2842   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2843
2844   my ($marcxml, $shortname) = @_;
2845
2846   use MARC::Record;
2847   use MARC::File::XML;
2848
2849   my $xml = $marcxml;
2850
2851   eval {
2852     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2853
2854     foreach my $field ( $marc->field('856') ) {
2855       if ( ! $field->as_string('9') ) {
2856         $field->add_subfields( '9' => $shortname );
2857       }
2858     }
2859
2860     $xml = $marc->as_xml_record;
2861     $xml =~ s/^<\?.+?\?>$//mo;
2862     $xml =~ s/\n//sgo;
2863     $xml =~ s/>\s+</></sgo;
2864   };
2865
2866   return $xml;
2867
2868 $$ LANGUAGE PLPERLU STABLE;
2869
2870
2871 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2872
2873 DECLARE
2874   old_volume   BIGINT;
2875   new_volume   BIGINT;
2876   bib          BIGINT;
2877   owner        INTEGER;
2878   old_label    TEXT;
2879   remainder    BIGINT;
2880
2881 BEGIN
2882
2883   -- Bail out if asked to change the label to ##URI##
2884   IF new_label = '##URI##' THEN
2885     RETURN;
2886   END IF;
2887
2888   -- Gather information
2889   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2890   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2891   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2892
2893   -- Bail out if the label already is ##URI##
2894   IF old_label = '##URI##' THEN
2895     RETURN;
2896   END IF;
2897
2898   -- Bail out if the call number label is already correct
2899   IF new_volume = old_volume THEN
2900     RETURN;
2901   END IF;
2902
2903   -- Check whether we already have a destination volume available
2904   SELECT id INTO new_volume FROM asset.call_number
2905     WHERE
2906       record = bib AND
2907       owning_lib = owner AND
2908       label = new_label AND
2909       NOT deleted;
2910
2911   -- Create destination volume if needed
2912   IF NOT FOUND THEN
2913     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2914       VALUES (1, 1, bib, owner, new_label, cn_class);
2915     SELECT id INTO new_volume FROM asset.call_number
2916       WHERE
2917         record = bib AND
2918         owning_lib = owner AND
2919         label = new_label AND
2920         NOT deleted;
2921   END IF;
2922
2923   -- Move copy to destination
2924   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2925
2926   -- Delete source volume if it is now empty
2927   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2928   IF NOT FOUND THEN
2929     DELETE FROM asset.call_number WHERE id = old_volume;
2930   END IF;
2931
2932 END;
2933
2934 $$ LANGUAGE plpgsql;
2935
2936 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2937
2938         my $input = $_[0];
2939         my %zipdata;
2940
2941         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2942
2943         while (<FH>) {
2944                 chomp;
2945                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2946                 $zipdata{$zip} = [$city, $state, $county];
2947         }
2948
2949         if (defined $zipdata{$input}) {
2950                 my ($city, $state, $county) = @{$zipdata{$input}};
2951                 return [$city, $state, $county];
2952         } elsif (defined $zipdata{substr $input, 0, 5}) {
2953                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2954                 return [$city, $state, $county];
2955         } else {
2956                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2957         }
2958
2959 $$ LANGUAGE PLPERLU STABLE;
2960
2961 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2962
2963 DECLARE
2964   ou  INT;
2965         org_unit_depth INT;
2966         ou_parent INT;
2967         parent_depth INT;
2968   errors_found BOOLEAN;
2969         ou_shortname TEXT;
2970         parent_shortname TEXT;
2971         ou_type_name TEXT;
2972         parent_type TEXT;
2973         type_id INT;
2974         type_depth INT;
2975         type_parent INT;
2976         type_parent_depth INT;
2977         proper_parent TEXT;
2978
2979 BEGIN
2980
2981         errors_found := FALSE;
2982
2983 -- Checking actor.org_unit_type
2984
2985         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2986
2987                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2988                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2989
2990                 IF type_parent IS NOT NULL THEN
2991
2992                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2993
2994                         IF type_depth - type_parent_depth <> 1 THEN
2995                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2996                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2997                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2998                                         ou_type_name, type_depth, parent_type, type_parent_depth;
2999                                 errors_found := TRUE;
3000
3001                         END IF;
3002
3003                 END IF;
3004
3005         END LOOP;
3006
3007 -- Checking actor.org_unit
3008
3009   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
3010
3011                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
3012                 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;
3013                 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;
3014                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
3015                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
3016                 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;
3017                 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;
3018
3019                 IF ou_parent IS NOT NULL THEN
3020
3021                         IF      (org_unit_depth - parent_depth <> 1) OR (
3022                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
3023                         ) THEN
3024                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
3025                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
3026                                 errors_found := TRUE;
3027                         END IF;
3028
3029                 END IF;
3030
3031   END LOOP;
3032
3033         IF NOT errors_found THEN
3034                 RAISE INFO 'No errors found.';
3035         END IF;
3036
3037   RETURN;
3038
3039 END;
3040
3041 $$ LANGUAGE plpgsql;
3042
3043
3044 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
3045
3046 BEGIN
3047
3048         DELETE FROM asset.opac_visible_copies;
3049
3050         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
3051                 SELECT DISTINCT
3052                         cp.id, cp.circ_lib, cn.record
3053                 FROM
3054                         asset.copy cp
3055                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
3056                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3057                         JOIN asset.copy_location cl ON (cp.location = cl.id)
3058                         JOIN config.copy_status cs ON (cp.status = cs.id)
3059                         JOIN biblio.record_entry b ON (cn.record = b.id)
3060                 WHERE
3061                         NOT cp.deleted AND
3062                         NOT cn.deleted AND
3063                         NOT b.deleted AND
3064                         cs.opac_visible AND
3065                         cl.opac_visible AND
3066                         cp.opac_visible AND
3067                         a.opac_visible AND
3068                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
3069
3070 END;
3071
3072 $$ LANGUAGE plpgsql;
3073
3074
3075 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
3076
3077 DECLARE
3078   old_volume     BIGINT;
3079   new_volume     BIGINT;
3080   bib            BIGINT;
3081   old_owning_lib INTEGER;
3082         old_label      TEXT;
3083   remainder      BIGINT;
3084
3085 BEGIN
3086
3087   -- Gather information
3088   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
3089   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
3090   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
3091
3092         -- Bail out if the new_owning_lib is not the ID of an org_unit
3093         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
3094                 RAISE WARNING
3095                         '% is not a valid actor.org_unit ID; no change made.',
3096                                 new_owning_lib;
3097                 RETURN;
3098         END IF;
3099
3100   -- Bail out discreetly if the owning_lib is already correct
3101   IF new_owning_lib = old_owning_lib THEN
3102     RETURN;
3103   END IF;
3104
3105   -- Check whether we already have a destination volume available
3106   SELECT id INTO new_volume FROM asset.call_number
3107     WHERE
3108       record = bib AND
3109       owning_lib = new_owning_lib AND
3110       label = old_label AND
3111       NOT deleted;
3112
3113   -- Create destination volume if needed
3114   IF NOT FOUND THEN
3115     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
3116       VALUES (1, 1, bib, new_owning_lib, old_label);
3117     SELECT id INTO new_volume FROM asset.call_number
3118       WHERE
3119         record = bib AND
3120         owning_lib = new_owning_lib AND
3121         label = old_label AND
3122         NOT deleted;
3123   END IF;
3124
3125   -- Move copy to destination
3126   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
3127
3128   -- Delete source volume if it is now empty
3129   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
3130   IF NOT FOUND THEN
3131     DELETE FROM asset.call_number WHERE id = old_volume;
3132   END IF;
3133
3134 END;
3135
3136 $$ LANGUAGE plpgsql;
3137
3138
3139 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
3140
3141 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
3142
3143 DECLARE
3144         new_owning_lib  INTEGER;
3145
3146 BEGIN
3147
3148         -- Parse the new_owner as an org unit ID or shortname
3149         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
3150                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
3151                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3152         ELSIF new_owner ~ E'^[0-9]+$' THEN
3153                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
3154                         RAISE INFO
3155                                 '%',
3156                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
3157                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
3158                         new_owning_lib := new_owner::INTEGER;
3159                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
3160                 END IF;
3161         ELSE
3162                 RAISE WARNING
3163                         '% is not a valid actor.org_unit shortname or ID; no change made.',
3164                         new_owning_lib;
3165                 RETURN;
3166         END IF;
3167
3168 END;
3169
3170 $$ LANGUAGE plpgsql;
3171
3172 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
3173
3174 use MARC::Record;
3175 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3176 use MARC::Charset;
3177
3178 MARC::Charset->assume_unicode(1);
3179
3180 my $xml = shift;
3181
3182 eval {
3183     my $r = MARC::Record->new_from_xml( $xml );
3184     my $output_xml = $r->as_xml_record();
3185 };
3186 if ($@) {
3187     return 0;
3188 } else {
3189     return 1;
3190 }
3191
3192 $func$ LANGUAGE PLPERLU;
3193 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
3194
3195 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
3196 BEGIN
3197    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
3198            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3199            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
3200    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
3201            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3202            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
3203    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
3204            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3205            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
3206    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
3207            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3208            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
3209    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
3210            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3211            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
3212    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
3213            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3214            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
3215    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
3216            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
3217            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
3218    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
3219    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
3220    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
3221    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
3222    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
3223 END;
3224 $FUNC$ LANGUAGE PLPGSQL;
3225
3226 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
3227 BEGIN
3228    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
3229    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
3230    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
3231    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
3232    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
3233    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
3234    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
3235
3236    -- import any new circ rules
3237    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
3238    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
3239    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
3240    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
3241
3242    -- and permission groups
3243    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
3244
3245 END;
3246 $FUNC$ LANGUAGE PLPGSQL;
3247
3248
3249 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$
3250 DECLARE
3251     name TEXT;
3252     loopq TEXT;
3253     existsq TEXT;
3254     ct INTEGER;
3255     cols TEXT[];
3256     copyst TEXT;
3257 BEGIN
3258     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
3259     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
3260     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
3261     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
3262     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
3263     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
3264     FOR name IN EXECUTE loopq LOOP
3265        EXECUTE existsq INTO ct USING name;
3266        IF ct = 0 THEN
3267            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
3268            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
3269                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
3270            EXECUTE copyst USING name;
3271        END IF;
3272     END LOOP;
3273 END;
3274 $FUNC$ LANGUAGE PLPGSQL;
3275
3276 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
3277 DECLARE
3278     id BIGINT;
3279     loopq TEXT;
3280     cols TEXT[];
3281     splitst TEXT;
3282 BEGIN
3283     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
3284     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;
3285     FOR id IN EXECUTE loopq USING delimiter LOOP
3286        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
3287        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
3288                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
3289        EXECUTE splitst USING id, delimiter;
3290     END LOOP;
3291 END;
3292 $FUNC$ LANGUAGE PLPGSQL;
3293
3294 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
3295
3296 use strict;
3297 use warnings;
3298
3299 use MARC::Record;
3300 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3301 use MARC::Charset;
3302
3303 MARC::Charset->assume_unicode(1);
3304
3305 my $target_xml = shift;
3306 my $source_xml = shift;
3307 my $tags = shift;
3308
3309 my $target;
3310 my $source;
3311
3312 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
3313 if ($@) {
3314     return;
3315 }
3316 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
3317 if ($@) {
3318     return;
3319 }
3320
3321 my $source_id = $source->subfield('901', 'c');
3322 $source_id = $source->subfield('903', 'a') unless $source_id;
3323 my $target_id = $target->subfield('901', 'c');
3324 $target_id = $target->subfield('903', 'a') unless $target_id;
3325
3326 my %existing_fields;
3327 foreach my $tag (@$tags) {
3328     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
3329     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
3330     $target->insert_fields_ordered(map { $_->clone() } @to_add);
3331     if (@to_add) {
3332         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
3333     }
3334 }
3335
3336 my $xml = $target->as_xml_record;
3337 $xml =~ s/^<\?.+?\?>$//mo;
3338 $xml =~ s/\n//sgo;
3339 $xml =~ s/>\s+</></sgo;
3340
3341 return $xml;
3342
3343 $func$ LANGUAGE PLPERLU;
3344 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.';
3345
3346 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
3347
3348 use strict;
3349 use warnings;
3350
3351 use MARC::Record;
3352 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3353 use Text::CSV;
3354
3355 my $in_tags = shift;
3356 my $in_values = shift;
3357
3358 # hack-and-slash parsing of array-passed-as-string;
3359 # this can go away once everybody is running Postgres 9.1+
3360 my $csv = Text::CSV->new({binary => 1});
3361 $in_tags =~ s/^{//;
3362 $in_tags =~ s/}$//;
3363 my $status = $csv->parse($in_tags);
3364 my $tags = [ $csv->fields() ];
3365 $in_values =~ s/^{//;
3366 $in_values =~ s/}$//;
3367 $status = $csv->parse($in_values);
3368 my $values = [ $csv->fields() ];
3369
3370 my $marc = MARC::Record->new();
3371
3372 $marc->leader('00000nam a22000007  4500');
3373 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3374
3375 foreach my $i (0..$#$tags) {
3376     my ($tag, $sf);
3377     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3378         $tag = $1;
3379         $sf = $2;
3380         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3381     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3382         $tag = $1;
3383         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3384     }
3385 }
3386
3387 my $xml = $marc->as_xml_record;
3388 $xml =~ s/^<\?.+?\?>$//mo;
3389 $xml =~ s/\n//sgo;
3390 $xml =~ s/>\s+</></sgo;
3391
3392 return $xml;
3393
3394 $func$ LANGUAGE PLPERLU;
3395 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3396 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3397 The second argument is an array of text containing the values to plug into each field.
3398 If the value for a given field is NULL or the empty string, it is not inserted.
3399 $$;
3400
3401 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
3402
3403 use strict;
3404 use warnings;
3405
3406 use MARC::Record;
3407 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3408 use Text::CSV;
3409
3410 my $in_tags = shift;
3411 my $in_ind1 = shift;
3412 my $in_ind2 = shift;
3413 my $in_values = shift;
3414
3415 # hack-and-slash parsing of array-passed-as-string;
3416 # this can go away once everybody is running Postgres 9.1+
3417 my $csv = Text::CSV->new({binary => 1});
3418 $in_tags =~ s/^{//;
3419 $in_tags =~ s/}$//;
3420 my $status = $csv->parse($in_tags);
3421 my $tags = [ $csv->fields() ];
3422 $in_ind1 =~ s/^{//;
3423 $in_ind1 =~ s/}$//;
3424 $status = $csv->parse($in_ind1);
3425 my $ind1s = [ $csv->fields() ];
3426 $in_ind2 =~ s/^{//;
3427 $in_ind2 =~ s/}$//;
3428 $status = $csv->parse($in_ind2);
3429 my $ind2s = [ $csv->fields() ];
3430 $in_values =~ s/^{//;
3431 $in_values =~ s/}$//;
3432 $status = $csv->parse($in_values);
3433 my $values = [ $csv->fields() ];
3434
3435 my $marc = MARC::Record->new();
3436
3437 $marc->leader('00000nam a22000007  4500');
3438 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
3439
3440 foreach my $i (0..$#$tags) {
3441     my ($tag, $sf);
3442     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3443         $tag = $1;
3444         $sf = $2;
3445         $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3446     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3447         $tag = $1;
3448         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3449     }
3450 }
3451
3452 my $xml = $marc->as_xml_record;
3453 $xml =~ s/^<\?.+?\?>$//mo;
3454 $xml =~ s/\n//sgo;
3455 $xml =~ s/>\s+</></sgo;
3456
3457 return $xml;
3458
3459 $func$ LANGUAGE PLPERLU;
3460 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.
3461 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3462 The second argument is an array of text containing the values to plug into indicator 1 for each field.
3463 The third argument is an array of text containing the values to plug into indicator 2 for each field.
3464 The fourth argument is an array of text containing the values to plug into each field.
3465 If the value for a given field is NULL or the empty string, it is not inserted.
3466 $$;
3467
3468 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3469
3470 my ($marcxml, $tag, $pos, $value) = @_;
3471
3472 use MARC::Record;
3473 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3474 use MARC::Charset;
3475 use strict;
3476
3477 MARC::Charset->assume_unicode(1);
3478
3479 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3480 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3481 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3482 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3483
3484 my $xml = $marcxml;
3485 eval {
3486     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3487
3488     foreach my $field ($marc->field($tag)) {
3489         $field->update("ind$pos" => $value);
3490     }
3491     $xml = $marc->as_xml_record;
3492     $xml =~ s/^<\?.+?\?>$//mo;
3493     $xml =~ s/\n//sgo;
3494     $xml =~ s/>\s+</></sgo;
3495 };
3496 return $xml;
3497
3498 $func$ LANGUAGE PLPERLU;
3499
3500 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3501 The first argument is a MARCXML string.
3502 The second argument is a MARC tag.
3503 The third argument is the indicator position, either 1 or 2.
3504 The fourth argument is the character to set the indicator value to.
3505 All occurences of the specified field will be changed.
3506 The function returns the revised MARCXML string.$$;
3507
3508 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3509     username TEXT,
3510     password TEXT,
3511     org TEXT,
3512     perm_group TEXT,
3513     first_name TEXT DEFAULT '',
3514     last_name TEXT DEFAULT ''
3515 ) RETURNS VOID AS $func$
3516 BEGIN
3517     RAISE NOTICE '%', org ;
3518     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3519     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3520     FROM   actor.org_unit aou, permission.grp_tree pgt
3521     WHERE  aou.shortname = org
3522     AND    pgt.name = perm_group;
3523 END
3524 $func$
3525 LANGUAGE PLPGSQL;
3526
3527 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3528 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3529     DECLARE
3530         target_event_def ALIAS FOR $1;
3531         orgs ALIAS FOR $2;
3532     BEGIN
3533         DROP TABLE IF EXISTS new_atevdefs;
3534         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3535         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3536             INSERT INTO action_trigger.event_definition (
3537                 active
3538                 ,owner
3539                 ,name
3540                 ,hook
3541                 ,validator
3542                 ,reactor
3543                 ,cleanup_success
3544                 ,cleanup_failure
3545                 ,delay
3546                 ,max_delay
3547                 ,usr_field
3548                 ,opt_in_setting
3549                 ,delay_field
3550                 ,group_field
3551                 ,template
3552                 ,granularity
3553                 ,repeat_delay
3554             ) SELECT
3555                 'f'
3556                 ,orgs[i]
3557                 ,name || ' (clone of '||target_event_def||')'
3558                 ,hook
3559                 ,validator
3560                 ,reactor
3561                 ,cleanup_success
3562                 ,cleanup_failure
3563                 ,delay
3564                 ,max_delay
3565                 ,usr_field
3566                 ,opt_in_setting
3567                 ,delay_field
3568                 ,group_field
3569                 ,template
3570                 ,granularity
3571                 ,repeat_delay
3572             FROM
3573                 action_trigger.event_definition
3574             WHERE
3575                 id = target_event_def
3576             ;
3577             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3578             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3579             INSERT INTO action_trigger.environment (
3580                 event_def
3581                 ,path
3582                 ,collector
3583                 ,label
3584             ) SELECT
3585                 currval('action_trigger.event_definition_id_seq')
3586                 ,path
3587                 ,collector
3588                 ,label
3589             FROM
3590                 action_trigger.environment
3591             WHERE
3592                 event_def = target_event_def
3593             ;
3594             INSERT INTO action_trigger.event_params (
3595                 event_def
3596                 ,param
3597                 ,value
3598             ) SELECT
3599                 currval('action_trigger.event_definition_id_seq')
3600                 ,param
3601                 ,value
3602             FROM
3603                 action_trigger.event_params
3604             WHERE
3605                 event_def = target_event_def
3606             ;
3607         END LOOP;
3608         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);
3609     END;
3610 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3611
3612 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3613 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3614     DECLARE
3615         target_event_def ALIAS FOR $1;
3616         orgs ALIAS FOR $2;
3617         new_interval ALIAS FOR $3;
3618     BEGIN
3619         DROP TABLE IF EXISTS new_atevdefs;
3620         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3621         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3622             INSERT INTO action_trigger.event_definition (
3623                 active
3624                 ,owner
3625                 ,name
3626                 ,hook
3627                 ,validator
3628                 ,reactor
3629                 ,cleanup_success
3630                 ,cleanup_failure
3631                 ,delay
3632                 ,max_delay
3633                 ,usr_field
3634                 ,opt_in_setting
3635                 ,delay_field
3636                 ,group_field
3637                 ,template
3638                 ,granularity
3639                 ,repeat_delay
3640             ) SELECT
3641                 'f'
3642                 ,orgs[i]
3643                 ,name || ' (clone of '||target_event_def||')'
3644                 ,hook
3645                 ,validator
3646                 ,reactor
3647                 ,cleanup_success
3648                 ,cleanup_failure
3649                 ,new_interval
3650                 ,max_delay
3651                 ,usr_field
3652                 ,opt_in_setting
3653                 ,delay_field
3654                 ,group_field
3655                 ,template
3656                 ,granularity
3657                 ,repeat_delay
3658             FROM
3659                 action_trigger.event_definition
3660             WHERE
3661                 id = target_event_def
3662             ;
3663             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3664             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3665             INSERT INTO action_trigger.environment (
3666                 event_def
3667                 ,path
3668                 ,collector
3669                 ,label
3670             ) SELECT
3671                 currval('action_trigger.event_definition_id_seq')
3672                 ,path
3673                 ,collector
3674                 ,label
3675             FROM
3676                 action_trigger.environment
3677             WHERE
3678                 event_def = target_event_def
3679             ;
3680             INSERT INTO action_trigger.event_params (
3681                 event_def
3682                 ,param
3683                 ,value
3684             ) SELECT
3685                 currval('action_trigger.event_definition_id_seq')
3686                 ,param
3687                 ,value
3688             FROM
3689                 action_trigger.event_params
3690             WHERE
3691                 event_def = target_event_def
3692             ;
3693         END LOOP;
3694         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);
3695     END;
3696 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3697
3698 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3699 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3700     DECLARE
3701         org ALIAS FOR $1;
3702         target_event_defs ALIAS FOR $2;
3703     BEGIN
3704         DROP TABLE IF EXISTS new_atevdefs;
3705         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3706         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3707             INSERT INTO action_trigger.event_definition (
3708                 active
3709                 ,owner
3710                 ,name
3711                 ,hook
3712                 ,validator
3713                 ,reactor
3714                 ,cleanup_success
3715                 ,cleanup_failure
3716                 ,delay
3717                 ,max_delay
3718                 ,usr_field
3719                 ,opt_in_setting
3720                 ,delay_field
3721                 ,group_field
3722                 ,template
3723                 ,granularity
3724                 ,repeat_delay
3725             ) SELECT
3726                 'f'
3727                 ,org
3728                 ,name || ' (clone of '||target_event_defs[i]||')'
3729                 ,hook
3730                 ,validator
3731                 ,reactor
3732                 ,cleanup_success
3733                 ,cleanup_failure
3734                 ,delay
3735                 ,max_delay
3736                 ,usr_field
3737                 ,opt_in_setting
3738                 ,delay_field
3739                 ,group_field
3740                 ,template
3741                 ,granularity
3742                 ,repeat_delay
3743             FROM
3744                 action_trigger.event_definition
3745             WHERE
3746                 id = target_event_defs[i]
3747             ;
3748             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3749             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3750             INSERT INTO action_trigger.environment (
3751                 event_def
3752                 ,path
3753                 ,collector
3754                 ,label
3755             ) SELECT
3756                 currval('action_trigger.event_definition_id_seq')
3757                 ,path
3758                 ,collector
3759                 ,label
3760             FROM
3761                 action_trigger.environment
3762             WHERE
3763                 event_def = target_event_defs[i]
3764             ;
3765             INSERT INTO action_trigger.event_params (
3766                 event_def
3767                 ,param
3768                 ,value
3769             ) SELECT
3770                 currval('action_trigger.event_definition_id_seq')
3771                 ,param
3772                 ,value
3773             FROM
3774                 action_trigger.event_params
3775             WHERE
3776                 event_def = target_event_defs[i]
3777             ;
3778         END LOOP;
3779         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3780     END;
3781 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3782
3783 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3784     UPDATE
3785         action_trigger.event
3786     SET
3787          start_time = NULL
3788         ,update_time = NULL
3789         ,complete_time = NULL
3790         ,update_process = NULL
3791         ,state = 'pending'
3792         ,template_output = NULL
3793         ,error_output = NULL
3794         ,async_output = NULL
3795     WHERE
3796         id = $1;
3797 $$ LANGUAGE SQL;
3798
3799 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3800     my ($marcxml) = @_;
3801
3802     use MARC::Record;
3803     use MARC::File::XML;
3804     use MARC::Field;
3805
3806     my $field;
3807     eval {
3808         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3809         $field = $marc->leader();
3810     };
3811     return $field;
3812 $$ LANGUAGE PLPERLU STABLE;
3813
3814 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3815     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3816
3817     use MARC::Record;
3818     use MARC::File::XML;
3819     use MARC::Field;
3820
3821     my $field;
3822     eval {
3823         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3824         $field = $marc->field($tag);
3825     };
3826     return $field->as_string($subfield,$delimiter) if $field;
3827     return;
3828 $$ LANGUAGE PLPERLU STABLE;
3829
3830 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3831     my ($marcxml, $tag, $subfield, $delimiter) = @_;
3832
3833     use MARC::Record;
3834     use MARC::File::XML;
3835     use MARC::Field;
3836
3837     my @fields;
3838     eval {
3839         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3840         @fields = $marc->field($tag);
3841     };
3842     my @texts;
3843     foreach my $field (@fields) {
3844         push @texts, $field->as_string($subfield,$delimiter);
3845     }
3846     return \@texts;
3847 $$ LANGUAGE PLPERLU STABLE;
3848
3849 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3850     my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
3851
3852     use MARC::Record;
3853     use MARC::File::XML;
3854     use MARC::Field;
3855
3856     my @fields;
3857     eval {
3858         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3859         @fields = $marc->field($tag);
3860     };
3861     my @texts;
3862     foreach my $field (@fields) {
3863         if ($field->as_string() =~ qr/$match/) {
3864             push @texts, $field->as_string($subfield,$delimiter);
3865         }
3866     }
3867     return \@texts;
3868 $$ LANGUAGE PLPERLU STABLE;
3869
3870 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3871     SELECT action.find_hold_matrix_matchpoint(
3872         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3873         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3874         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3875         (SELECT usr FROM action.hold_request WHERE id = $1),
3876         (SELECT requestor FROM action.hold_request WHERE id = $1)
3877     );
3878 $$ LANGUAGE SQL;
3879
3880 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3881     SELECT action.hold_request_permit_test(
3882         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3883         (SELECT request_lib FROM action.hold_request WHERE id = $1),
3884         (SELECT current_copy FROM action.hold_request WHERE id = $1),
3885         (SELECT usr FROM action.hold_request WHERE id = $1),
3886         (SELECT requestor FROM action.hold_request WHERE id = $1)
3887     );
3888 $$ LANGUAGE SQL;
3889
3890 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3891     SELECT action.find_circ_matrix_matchpoint(
3892         (SELECT circ_lib FROM action.circulation WHERE id = $1),
3893         (SELECT target_copy FROM action.circulation WHERE id = $1),
3894         (SELECT usr FROM action.circulation WHERE id = $1),
3895         (SELECT COALESCE(
3896                 NULLIF(phone_renewal,false),
3897                 NULLIF(desk_renewal,false),
3898                 NULLIF(opac_renewal,false),
3899                 false
3900             ) FROM action.circulation WHERE id = $1
3901         )
3902     );
3903 $$ LANGUAGE SQL;
3904
3905 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3906     DECLARE
3907         test ALIAS FOR $1;
3908     BEGIN
3909         IF NOT test THEN
3910             RAISE EXCEPTION 'assertion';
3911         END IF;
3912     END;
3913 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3914
3915 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3916     DECLARE
3917         test ALIAS FOR $1;
3918         msg ALIAS FOR $2;
3919     BEGIN
3920         IF NOT test THEN
3921             RAISE EXCEPTION '%', msg;
3922         END IF;
3923     END;
3924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3925
3926 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3927     DECLARE
3928         test ALIAS FOR $1;
3929         fail_msg ALIAS FOR $2;
3930         success_msg ALIAS FOR $3;
3931     BEGIN
3932         IF NOT test THEN
3933             RAISE EXCEPTION '%', fail_msg;
3934         END IF;
3935         RETURN success_msg;
3936     END;
3937 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3938
3939 -- push bib sequence and return starting value for reserved range
3940 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3941     DECLARE
3942         bib_count ALIAS FOR $1;
3943         output BIGINT;
3944     BEGIN
3945         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3946         FOR output IN
3947             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3948         LOOP
3949             RETURN output;
3950         END LOOP;
3951     END;
3952 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3953
3954 -- set a new salted password
3955
3956 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3957     DECLARE
3958         usr_id              ALIAS FOR $1;
3959         plain_passwd        ALIAS FOR $2;
3960         plain_salt          TEXT;
3961         md5_passwd          TEXT;
3962     BEGIN
3963
3964         SELECT actor.create_salt('main') INTO plain_salt;
3965
3966         SELECT MD5(plain_passwd) INTO md5_passwd;
3967
3968         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3969
3970         RETURN TRUE;
3971
3972     END;
3973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3974
3975
3976 -- convenience functions for handling copy_location maps
3977 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3978     SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
3979 $$ LANGUAGE SQL;
3980
3981 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
3982     DECLARE
3983         table_schema ALIAS FOR $1;
3984         table_name ALIAS FOR $2;
3985         org_shortname ALIAS FOR $3;
3986         org_range ALIAS FOR $4;
3987         make_assertion ALIAS FOR $5;
3988         proceed BOOLEAN;
3989         org INTEGER;
3990         -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3991         -- though we'll still use the passed org for the full path traversal when needed
3992         x_org_found BOOLEAN;
3993         x_org INTEGER;
3994         org_list INTEGER[];
3995         o INTEGER;
3996         row_count NUMERIC;
3997     BEGIN
3998         EXECUTE 'SELECT EXISTS (
3999             SELECT 1
4000             FROM information_schema.columns
4001             WHERE table_schema = $1
4002             AND table_name = $2
4003             and column_name = ''desired_shelf''
4004         )' INTO proceed USING table_schema, table_name;
4005         IF NOT proceed THEN
4006             RAISE EXCEPTION 'Missing column desired_shelf';
4007         END IF;
4008
4009         EXECUTE 'SELECT EXISTS (
4010             SELECT 1
4011             FROM information_schema.columns
4012             WHERE table_schema = $1
4013             AND table_name = $2
4014             and column_name = ''x_org''
4015         )' INTO x_org_found USING table_schema, table_name;
4016
4017         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4018         IF org IS NULL THEN
4019             RAISE EXCEPTION 'Cannot find org by shortname';
4020         END IF;
4021
4022         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4023
4024         EXECUTE 'ALTER TABLE '
4025             || quote_ident(table_name)
4026             || ' DROP COLUMN IF EXISTS x_shelf';
4027         EXECUTE 'ALTER TABLE '
4028             || quote_ident(table_name)
4029             || ' ADD COLUMN x_shelf INTEGER';
4030
4031         IF x_org_found THEN
4032             RAISE INFO 'Found x_org column';
4033             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4034                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4035                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4036                 || ' AND b.owning_lib = x_org'
4037                 || ' AND NOT b.deleted';
4038             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4039                 || ' SET x_shelf = b.id FROM asset.copy_location b'
4040                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4041                 || ' AND b.owning_lib = x_org'
4042                 || ' AND x_shelf IS NULL'
4043                 || ' AND NOT b.deleted';
4044         ELSE
4045             RAISE INFO 'Did not find x_org column';
4046             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4047                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4048                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4049                 || ' AND b.owning_lib = $1'
4050                 || ' AND NOT b.deleted'
4051             USING org;
4052             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4053                 || ' SET x_shelf = b.id FROM asset_copy_location b'
4054                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4055                 || ' AND b.owning_lib = $1'
4056                 || ' AND x_shelf IS NULL'
4057                 || ' AND NOT b.deleted'
4058             USING org;
4059         END IF;
4060
4061         FOREACH o IN ARRAY org_list LOOP
4062             RAISE INFO 'Considering org %', o;
4063             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4064                 || ' SET x_shelf = b.id FROM asset.copy_location b'
4065                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
4066                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
4067                 || ' AND NOT b.deleted'
4068             USING o;
4069             GET DIAGNOSTICS row_count = ROW_COUNT;
4070             RAISE INFO 'Updated % rows', row_count;
4071         END LOOP;
4072
4073         IF make_assertion THEN
4074             EXECUTE 'SELECT migration_tools.assert(
4075                 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
4076                 ''Cannot find a desired location'',
4077                 ''Found all desired locations''
4078             );';
4079         END IF;
4080
4081     END;
4082 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4083
4084 -- convenience functions for handling circmod maps
4085
4086 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
4087     DECLARE
4088         table_schema ALIAS FOR $1;
4089         table_name ALIAS FOR $2;
4090         proceed BOOLEAN;
4091     BEGIN
4092         EXECUTE 'SELECT EXISTS (
4093             SELECT 1
4094             FROM information_schema.columns
4095             WHERE table_schema = $1
4096             AND table_name = $2
4097             and column_name = ''desired_circmod''
4098         )' INTO proceed USING table_schema, table_name;
4099         IF NOT proceed THEN
4100             RAISE EXCEPTION 'Missing column desired_circmod';
4101         END IF;
4102
4103         EXECUTE 'ALTER TABLE '
4104             || quote_ident(table_name)
4105             || ' DROP COLUMN IF EXISTS x_circmod';
4106         EXECUTE 'ALTER TABLE '
4107             || quote_ident(table_name)
4108             || ' ADD COLUMN x_circmod TEXT';
4109
4110         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4111             || ' SET x_circmod = code FROM config.circ_modifier b'
4112             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
4113
4114         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4115             || ' SET x_circmod = code FROM config.circ_modifier b'
4116             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
4117             || ' AND x_circmod IS NULL';
4118
4119         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4120             || ' SET x_circmod = code FROM config.circ_modifier b'
4121             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
4122             || ' AND x_circmod IS NULL';
4123
4124         EXECUTE 'SELECT migration_tools.assert(
4125             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
4126             ''Cannot find a desired circulation modifier'',
4127             ''Found all desired circulation modifiers''
4128         );';
4129
4130     END;
4131 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4132
4133 -- convenience functions for handling item status maps
4134
4135 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
4136     DECLARE
4137         table_schema ALIAS FOR $1;
4138         table_name ALIAS FOR $2;
4139         proceed BOOLEAN;
4140     BEGIN
4141         EXECUTE 'SELECT EXISTS (
4142             SELECT 1
4143             FROM information_schema.columns
4144             WHERE table_schema = $1
4145             AND table_name = $2
4146             and column_name = ''desired_status''
4147         )' INTO proceed USING table_schema, table_name;
4148         IF NOT proceed THEN
4149             RAISE EXCEPTION 'Missing column desired_status';
4150         END IF;
4151
4152         EXECUTE 'ALTER TABLE '
4153             || quote_ident(table_name)
4154             || ' DROP COLUMN IF EXISTS x_status';
4155         EXECUTE 'ALTER TABLE '
4156             || quote_ident(table_name)
4157             || ' ADD COLUMN x_status INTEGER';
4158
4159         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4160             || ' SET x_status = id FROM config.copy_status b'
4161             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
4162
4163         EXECUTE 'SELECT migration_tools.assert(
4164             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
4165             ''Cannot find a desired copy status'',
4166             ''Found all desired copy statuses''
4167         );';
4168
4169     END;
4170 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4171
4172 -- convenience functions for handling org maps
4173
4174 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
4175     DECLARE
4176         table_schema ALIAS FOR $1;
4177         table_name ALIAS FOR $2;
4178         proceed BOOLEAN;
4179     BEGIN
4180         EXECUTE 'SELECT EXISTS (
4181             SELECT 1
4182             FROM information_schema.columns
4183             WHERE table_schema = $1
4184             AND table_name = $2
4185             and column_name = ''desired_org''
4186         )' INTO proceed USING table_schema, table_name;
4187         IF NOT proceed THEN
4188             RAISE EXCEPTION 'Missing column desired_org';
4189         END IF;
4190
4191         EXECUTE 'ALTER TABLE '
4192             || quote_ident(table_name)
4193             || ' DROP COLUMN IF EXISTS x_org';
4194         EXECUTE 'ALTER TABLE '
4195             || quote_ident(table_name)
4196             || ' ADD COLUMN x_org INTEGER';
4197
4198         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4199             || ' SET x_org = b.id FROM actor.org_unit b'
4200             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
4201
4202         EXECUTE 'SELECT migration_tools.assert(
4203             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
4204             ''Cannot find a desired org unit'',
4205             ''Found all desired org units''
4206         );';
4207
4208     END;
4209 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4210
4211 -- convenience function for handling desired_not_migrate
4212
4213 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
4214     DECLARE
4215         table_schema ALIAS FOR $1;
4216         table_name ALIAS FOR $2;
4217         proceed BOOLEAN;
4218     BEGIN
4219         EXECUTE 'SELECT EXISTS (
4220             SELECT 1
4221             FROM information_schema.columns
4222             WHERE table_schema = $1
4223             AND table_name = $2
4224             and column_name = ''desired_not_migrate''
4225         )' INTO proceed USING table_schema, table_name;
4226         IF NOT proceed THEN
4227             RAISE EXCEPTION 'Missing column desired_not_migrate';
4228         END IF;
4229
4230         EXECUTE 'ALTER TABLE '
4231             || quote_ident(table_name)
4232             || ' DROP COLUMN IF EXISTS x_migrate';
4233         EXECUTE 'ALTER TABLE '
4234             || quote_ident(table_name)
4235             || ' ADD COLUMN x_migrate BOOLEAN';
4236
4237         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4238             || ' SET x_migrate = CASE'
4239             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
4240             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
4241             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
4242             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
4243             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
4244             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
4245             || ' END';
4246
4247         EXECUTE 'SELECT migration_tools.assert(
4248             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
4249             ''Not all desired_not_migrate values understood'',
4250             ''All desired_not_migrate values understood''
4251         );';
4252
4253     END;
4254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4255
4256 -- convenience function for handling desired_not_migrate
4257
4258 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
4259     DECLARE
4260         table_schema ALIAS FOR $1;
4261         table_name ALIAS FOR $2;
4262         proceed BOOLEAN;
4263     BEGIN
4264         EXECUTE 'SELECT EXISTS (
4265             SELECT 1
4266             FROM information_schema.columns
4267             WHERE table_schema = $1
4268             AND table_name = $2
4269             and column_name = ''desired_barred_or_blocked''
4270         )' INTO proceed USING table_schema, table_name;
4271         IF NOT proceed THEN
4272             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
4273         END IF;
4274
4275         EXECUTE 'ALTER TABLE '
4276             || quote_ident(table_name)
4277             || ' DROP COLUMN IF EXISTS x_barred';
4278         EXECUTE 'ALTER TABLE '
4279             || quote_ident(table_name)
4280             || ' ADD COLUMN x_barred BOOLEAN';
4281
4282         EXECUTE 'ALTER TABLE '
4283             || quote_ident(table_name)
4284             || ' DROP COLUMN IF EXISTS x_blocked';
4285         EXECUTE 'ALTER TABLE '
4286             || quote_ident(table_name)
4287             || ' ADD COLUMN x_blocked BOOLEAN';
4288
4289         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4290             || ' SET x_barred = CASE'
4291             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
4292             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
4293             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4294             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4295             || ' END';
4296
4297         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4298             || ' SET x_blocked = CASE'
4299             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
4300             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
4301             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
4302             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
4303             || ' END';
4304
4305         EXECUTE 'SELECT migration_tools.assert(
4306             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
4307             ''Not all desired_barred_or_blocked values understood'',
4308             ''All desired_barred_or_blocked values understood''
4309         );';
4310
4311     END;
4312 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4313
4314 -- convenience function for handling desired_profile
4315
4316 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
4317     DECLARE
4318         table_schema ALIAS FOR $1;
4319         table_name ALIAS FOR $2;
4320         proceed BOOLEAN;
4321     BEGIN
4322         EXECUTE 'SELECT EXISTS (
4323             SELECT 1
4324             FROM information_schema.columns
4325             WHERE table_schema = $1
4326             AND table_name = $2
4327             and column_name = ''desired_profile''
4328         )' INTO proceed USING table_schema, table_name;
4329         IF NOT proceed THEN
4330             RAISE EXCEPTION 'Missing column desired_profile';
4331         END IF;
4332
4333         EXECUTE 'ALTER TABLE '
4334             || quote_ident(table_name)
4335             || ' DROP COLUMN IF EXISTS x_profile';
4336         EXECUTE 'ALTER TABLE '
4337             || quote_ident(table_name)
4338             || ' ADD COLUMN x_profile INTEGER';
4339
4340         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4341             || ' SET x_profile = b.id FROM permission.grp_tree b'
4342             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
4343
4344         EXECUTE 'SELECT migration_tools.assert(
4345             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
4346             ''Cannot find a desired profile'',
4347             ''Found all desired profiles''
4348         );';
4349
4350     END;
4351 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4352
4353 -- convenience function for handling desired actor stat cats
4354
4355 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4356     DECLARE
4357         table_schema ALIAS FOR $1;
4358         table_name ALIAS FOR $2;
4359         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4360         org_shortname ALIAS FOR $4;
4361         proceed BOOLEAN;
4362         org INTEGER;
4363         org_list INTEGER[];
4364         sc TEXT;
4365         sce TEXT;
4366     BEGIN
4367
4368         SELECT 'desired_sc' || field_suffix INTO sc;
4369         SELECT 'desired_sce' || field_suffix INTO sce;
4370
4371         EXECUTE 'SELECT EXISTS (
4372             SELECT 1
4373             FROM information_schema.columns
4374             WHERE table_schema = $1
4375             AND table_name = $2
4376             and column_name = $3
4377         )' INTO proceed USING table_schema, table_name, sc;
4378         IF NOT proceed THEN
4379             RAISE EXCEPTION 'Missing column %', sc;
4380         END IF;
4381         EXECUTE 'SELECT EXISTS (
4382             SELECT 1
4383             FROM information_schema.columns
4384             WHERE table_schema = $1
4385             AND table_name = $2
4386             and column_name = $3
4387         )' INTO proceed USING table_schema, table_name, sce;
4388         IF NOT proceed THEN
4389             RAISE EXCEPTION 'Missing column %', sce;
4390         END IF;
4391
4392         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4393         IF org IS NULL THEN
4394             RAISE EXCEPTION 'Cannot find org by shortname';
4395         END IF;
4396         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4397
4398         -- caller responsible for their own truncates though we try to prevent duplicates
4399         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
4400             SELECT DISTINCT
4401                  $1
4402                 ,BTRIM('||sc||')
4403             FROM
4404                 ' || quote_ident(table_name) || '
4405             WHERE
4406                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4407                 AND NOT EXISTS (
4408                     SELECT id
4409                     FROM actor.stat_cat
4410                     WHERE owner = ANY ($2)
4411                     AND name = BTRIM('||sc||')
4412                 )
4413                 AND NOT EXISTS (
4414                     SELECT id
4415                     FROM actor_stat_cat
4416                     WHERE owner = ANY ($2)
4417                     AND name = BTRIM('||sc||')
4418                 )
4419             ORDER BY 2;'
4420         USING org, org_list;
4421
4422         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
4423             SELECT DISTINCT
4424                 COALESCE(
4425                     (SELECT id
4426                         FROM actor.stat_cat
4427                         WHERE owner = ANY ($2)
4428                         AND BTRIM('||sc||') = BTRIM(name))
4429                    ,(SELECT id
4430                         FROM actor_stat_cat
4431                         WHERE owner = ANY ($2)
4432                         AND BTRIM('||sc||') = BTRIM(name))
4433                 )
4434                 ,$1
4435                 ,BTRIM('||sce||')
4436             FROM
4437                 ' || quote_ident(table_name) || '
4438             WHERE
4439                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4440                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4441                 AND NOT EXISTS (
4442                     SELECT id
4443                     FROM actor.stat_cat_entry
4444                     WHERE stat_cat = (
4445                         SELECT id
4446                         FROM actor.stat_cat
4447                         WHERE owner = ANY ($2)
4448                         AND BTRIM('||sc||') = BTRIM(name)
4449                     ) AND value = BTRIM('||sce||')
4450                     AND owner = ANY ($2)
4451                 )
4452                 AND NOT EXISTS (
4453                     SELECT id
4454                     FROM actor_stat_cat_entry
4455                     WHERE stat_cat = (
4456                         SELECT id
4457                         FROM actor_stat_cat
4458                         WHERE owner = ANY ($2)
4459                         AND BTRIM('||sc||') = BTRIM(name)
4460                     ) AND value = BTRIM('||sce||')
4461                     AND owner = ANY ($2)
4462                 )
4463             ORDER BY 1,3;'
4464         USING org, org_list;
4465     END;
4466 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4467
4468 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4469     DECLARE
4470         table_schema ALIAS FOR $1;
4471         table_name ALIAS FOR $2;
4472         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4473         org_shortname ALIAS FOR $4;
4474         proceed BOOLEAN;
4475         org INTEGER;
4476         org_list INTEGER[];
4477         o INTEGER;
4478         sc TEXT;
4479         sce TEXT;
4480     BEGIN
4481         SELECT 'desired_sc' || field_suffix INTO sc;
4482         SELECT 'desired_sce' || field_suffix INTO sce;
4483         EXECUTE 'SELECT EXISTS (
4484             SELECT 1
4485             FROM information_schema.columns
4486             WHERE table_schema = $1
4487             AND table_name = $2
4488             and column_name = $3
4489         )' INTO proceed USING table_schema, table_name, sc;
4490         IF NOT proceed THEN
4491             RAISE EXCEPTION 'Missing column %', sc;
4492         END IF;
4493         EXECUTE 'SELECT EXISTS (
4494             SELECT 1
4495             FROM information_schema.columns
4496             WHERE table_schema = $1
4497             AND table_name = $2
4498             and column_name = $3
4499         )' INTO proceed USING table_schema, table_name, sce;
4500         IF NOT proceed THEN
4501             RAISE EXCEPTION 'Missing column %', sce;
4502         END IF;
4503
4504         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4505         IF org IS NULL THEN
4506             RAISE EXCEPTION 'Cannot find org by shortname';
4507         END IF;
4508
4509         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4510
4511         EXECUTE 'ALTER TABLE '
4512             || quote_ident(table_name)
4513             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4514         EXECUTE 'ALTER TABLE '
4515             || quote_ident(table_name)
4516             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4517         EXECUTE 'ALTER TABLE '
4518             || quote_ident(table_name)
4519             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4520         EXECUTE 'ALTER TABLE '
4521             || quote_ident(table_name)
4522             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4523
4524
4525         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4526             SET
4527                 x_sc' || field_suffix || ' = id
4528             FROM
4529                 (SELECT id, name, owner FROM actor_stat_cat
4530                     UNION SELECT id, name, owner FROM actor.stat_cat) u
4531             WHERE
4532                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4533                 AND u.owner = ANY ($1);'
4534         USING org_list;
4535
4536         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4537             SET
4538                 x_sce' || field_suffix || ' = id
4539             FROM
4540                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4541                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4542             WHERE
4543                     u.stat_cat = x_sc' || field_suffix || '
4544                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4545                 AND u.owner = ANY ($1);'
4546         USING org_list;
4547
4548         EXECUTE 'SELECT migration_tools.assert(
4549             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4550             ''Cannot find a desired stat cat'',
4551             ''Found all desired stat cats''
4552         );';
4553
4554         EXECUTE 'SELECT migration_tools.assert(
4555             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4556             ''Cannot find a desired stat cat entry'',
4557             ''Found all desired stat cat entries''
4558         );';
4559
4560     END;
4561 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4562
4563 -- convenience functions for adding shelving locations
4564 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4565 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4566 DECLARE
4567     return_id   INT;
4568     d           INT;
4569     cur_id      INT;
4570 BEGIN
4571     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4572     WHILE d >= 0
4573     LOOP
4574         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4575         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4576         IF return_id IS NOT NULL THEN
4577                 RETURN return_id;
4578         END IF;
4579         d := d - 1;
4580     END LOOP;
4581
4582     RETURN NULL;
4583 END
4584 $$ LANGUAGE plpgsql;
4585
4586 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4587
4588 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4589 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4590 DECLARE
4591     return_id   INT;
4592     d           INT;
4593     cur_id      INT;
4594 BEGIN
4595     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4596     WHILE d >= 0
4597     LOOP
4598         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4599
4600         SELECT INTO return_id id FROM
4601             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4602             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4603         IF return_id IS NOT NULL THEN
4604                 RETURN return_id;
4605         END IF;
4606         d := d - 1;
4607     END LOOP;
4608
4609     RETURN NULL;
4610 END
4611 $$ LANGUAGE plpgsql;
4612
4613 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
4614 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
4615     RETURNS BOOLEAN AS
4616 $BODY$
4617 DECLARE
4618         marc_xml        TEXT;
4619         new_marc        TEXT;
4620 BEGIN
4621         SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
4622
4623         SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
4624         UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
4625
4626         RETURN true;
4627 END;
4628 $BODY$ LANGUAGE plpgsql;
4629
4630 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
4631 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
4632  RETURNS TEXT
4633  LANGUAGE plperlu
4634 AS $function$
4635 use strict;
4636 use warnings;
4637
4638 use MARC::Record;
4639 use MARC::File::XML (BinaryEncoding => 'utf8');
4640
4641 binmode(STDERR, ':bytes');
4642 binmode(STDOUT, ':utf8');
4643 binmode(STDERR, ':utf8');
4644
4645 my $marc_xml = shift;
4646 my $new_9_to_set = shift;
4647 my $force = shift;
4648
4649 $marc_xml =~ s/(<leader>.........)./${1}a/;
4650
4651 eval {
4652     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4653 };
4654 if ($@) {
4655     #elog("could not parse $bibid: $@\n");
4656     import MARC::File::XML (BinaryEncoding => 'utf8');
4657     return $marc_xml;
4658 }
4659
4660 my @uris = $marc_xml->field('856');
4661 return $marc_xml->as_xml_record() unless @uris;
4662
4663 foreach my $field (@uris) {
4664     my $ind1 = $field->indicator('1');
4665     if (!defined $ind1) { next; }
4666     if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4667         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4668     my $ind2 = $field->indicator('2');
4669     if (!defined $ind2) { next; }
4670     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4671     if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4672     $field->add_subfields( '9' => $new_9_to_set );
4673 }
4674
4675 return $marc_xml->as_xml_record();
4676
4677 $function$;
4678
4679 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
4680 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
4681  RETURNS TEXT
4682  LANGUAGE plperlu
4683 AS $function$
4684 use strict;
4685 use warnings;
4686
4687 use MARC::Record;
4688 use MARC::File::XML (BinaryEncoding => 'utf8');
4689
4690 binmode(STDERR, ':bytes');
4691 binmode(STDOUT, ':utf8');
4692 binmode(STDERR, ':utf8');
4693
4694 my $marc_xml = shift;
4695 my $qualifying_match = shift;
4696 my $new_9_to_set = shift;
4697 my $force = shift;
4698
4699 $marc_xml =~ s/(<leader>.........)./${1}a/;
4700
4701 eval {
4702     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4703 };
4704 if ($@) {
4705     #elog("could not parse $bibid: $@\n");
4706     import MARC::File::XML (BinaryEncoding => 'utf8');
4707     return $marc_xml;
4708 }
4709
4710 my @uris = $marc_xml->field('856');
4711 return $marc_xml->as_xml_record() unless @uris;
4712
4713 foreach my $field (@uris) {
4714     if ($field->as_string() =~ qr/$qualifying_match/) {
4715         my $ind1 = $field->indicator('1');
4716         if (!defined $ind1) { next; }
4717         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
4718         if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
4719         my $ind2 = $field->indicator('2');
4720         if (!defined $ind2) { next; }
4721         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
4722         if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
4723         $field->add_subfields( '9' => $new_9_to_set );
4724     }
4725 }
4726
4727 return $marc_xml->as_xml_record();
4728
4729 $function$;
4730
4731 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
4732 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
4733  RETURNS TEXT
4734  LANGUAGE plperlu
4735 AS $function$
4736 use strict;
4737 use warnings;
4738
4739 use MARC::Record;
4740 use MARC::File::XML (BinaryEncoding => 'utf8');
4741
4742 binmode(STDERR, ':bytes');
4743 binmode(STDOUT, ':utf8');
4744 binmode(STDERR, ':utf8');
4745
4746 my $marc_xml = shift;
4747 my $substring_old_value = shift;
4748 my $new_value = shift;
4749 my $fix_indicators = shift;
4750
4751 $marc_xml =~ s/(<leader>.........)./${1}a/;
4752
4753 eval {
4754     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4755 };
4756 if ($@) {
4757     #elog("could not parse $bibid: $@\n");
4758     import MARC::File::XML (BinaryEncoding => 'utf8');
4759     return $marc_xml;
4760 }
4761
4762 my @uris = $marc_xml->field('856');
4763 return $marc_xml->as_xml_record() unless @uris;
4764
4765 foreach my $field (@uris) {
4766     my $ind1 = $field->indicator('1');
4767     if (defined $ind1) {
4768             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4769             $field->set_indicator(1,'4');
4770         }
4771     }
4772     my $ind2 = $field->indicator('2');
4773     if (defined $ind2) {
4774         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4775             $field->set_indicator(2,'0');
4776         }
4777     }
4778     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4779         $field->delete_subfield('9');
4780         $field->add_subfields( '9' => $new_value );
4781     }
4782     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4783 }
4784
4785 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4786 $marc_xml->insert_fields_ordered( values( %hash ) );
4787
4788 return $marc_xml->as_xml_record();
4789
4790 $function$;
4791
4792 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
4793 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)
4794  RETURNS TEXT
4795  LANGUAGE plperlu
4796 AS $function$
4797 use strict;
4798 use warnings;
4799
4800 use MARC::Record;
4801 use MARC::File::XML (BinaryEncoding => 'utf8');
4802
4803 binmode(STDERR, ':bytes');
4804 binmode(STDOUT, ':utf8');
4805 binmode(STDERR, ':utf8');
4806
4807 my $marc_xml = shift;
4808 my $qualifying_match = shift;
4809 my $substring_old_value = shift;
4810 my $new_value = shift;
4811 my $fix_indicators = shift;
4812
4813 $marc_xml =~ s/(<leader>.........)./${1}a/;
4814
4815 eval {
4816     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4817 };
4818 if ($@) {
4819     #elog("could not parse $bibid: $@\n");
4820     import MARC::File::XML (BinaryEncoding => 'utf8');
4821     return $marc_xml;
4822 }
4823
4824 my @unqualified_uris = $marc_xml->field('856');
4825 my @uris = ();
4826 foreach my $field (@unqualified_uris) {
4827     if ($field->as_string() =~ qr/$qualifying_match/) {
4828         push @uris, $field;
4829     }
4830 }
4831 return $marc_xml->as_xml_record() unless @uris;
4832
4833 foreach my $field (@uris) {
4834     my $ind1 = $field->indicator('1');
4835     if (defined $ind1) {
4836             if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
4837             $field->set_indicator(1,'4');
4838         }
4839     }
4840     my $ind2 = $field->indicator('2');
4841     if (defined $ind2) {
4842         if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
4843             $field->set_indicator(2,'0');
4844         }
4845     }
4846     if ($field->as_string('9') =~ qr/$substring_old_value/) {
4847         $field->delete_subfield('9');
4848         $field->add_subfields( '9' => $new_value );
4849     }
4850     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
4851 }
4852
4853 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
4854 $marc_xml->insert_fields_ordered( values( %hash ) );
4855
4856 return $marc_xml->as_xml_record();
4857
4858 $function$;
4859
4860 -- strip marc tag
4861 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4862 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4863  RETURNS TEXT
4864  LANGUAGE plperlu
4865 AS $function$
4866 use strict;
4867 use warnings;
4868
4869 use MARC::Record;
4870 use MARC::File::XML (BinaryEncoding => 'utf8');
4871
4872 binmode(STDERR, ':bytes');
4873 binmode(STDOUT, ':utf8');
4874 binmode(STDERR, ':utf8');
4875
4876 my $marc_xml = shift;
4877 my $tag = shift;
4878
4879 $marc_xml =~ s/(<leader>.........)./${1}a/;
4880
4881 eval {
4882     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4883 };
4884 if ($@) {
4885     #elog("could not parse $bibid: $@\n");
4886     import MARC::File::XML (BinaryEncoding => 'utf8');
4887     return $marc_xml;
4888 }
4889
4890 my @fields = $marc_xml->field($tag);
4891 return $marc_xml->as_xml_record() unless @fields;
4892
4893 $marc_xml->delete_fields(@fields);
4894
4895 return $marc_xml->as_xml_record();
4896
4897 $function$;
4898
4899 -- removes tags from record based on tag, subfield and evidence
4900 -- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
4901 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
4902 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence TEXT)
4903  RETURNS TEXT
4904  LANGUAGE plperlu
4905 AS $function$
4906 use strict;
4907 use warnings;
4908
4909 use MARC::Record;
4910 use MARC::File::XML (BinaryEncoding => 'utf8');
4911
4912 binmode(STDERR, ':bytes');
4913 binmode(STDOUT, ':utf8');
4914 binmode(STDERR, ':utf8');
4915
4916 my $marc_xml = shift;
4917 my $tag = shift;
4918 my $subfield = shift;
4919 my $evidence = shift;
4920
4921 $marc_xml =~ s/(<leader>.........)./${1}a/;
4922
4923 eval {
4924     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4925 };
4926 if ($@) {
4927     #elog("could not parse $bibid: $@\n");
4928     import MARC::File::XML (BinaryEncoding => 'utf8');
4929     return $marc_xml;
4930 }
4931
4932 my @fields = $marc_xml->field($tag);
4933 return $marc_xml->as_xml_record() unless @fields;
4934
4935 my @fields_to_delete;
4936
4937 foreach my $f (@fields) {
4938     my $sf = lc($f->as_string($subfield));
4939     if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
4940 }
4941
4942 $marc_xml->delete_fields(@fields_to_delete);
4943
4944 return $marc_xml->as_xml_record();
4945
4946 $function$;
4947
4948
4949 -- consolidate marc tag
4950 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
4951 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
4952  RETURNS TEXT
4953  LANGUAGE plperlu
4954 AS $function$
4955 use strict;
4956 use warnings;
4957
4958 use MARC::Record;
4959 use MARC::File::XML (BinaryEncoding => 'utf8');
4960
4961 binmode(STDERR, ':bytes');
4962 binmode(STDOUT, ':utf8');
4963 binmode(STDERR, ':utf8');
4964
4965 my $marc_xml = shift;
4966 my $tag = shift;
4967
4968 $marc_xml =~ s/(<leader>.........)./${1}a/;
4969
4970 eval {
4971     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4972 };
4973 if ($@) {
4974     #elog("could not parse $bibid: $@\n");
4975     import MARC::File::XML (BinaryEncoding => 'utf8');
4976     return $marc_xml;
4977 }
4978
4979 my @fields = $marc_xml->field($tag);
4980 return $marc_xml->as_xml_record() unless @fields;
4981
4982 my @combined_subfield_refs = ();
4983 my @combined_subfields = ();
4984 foreach my $field (@fields) {
4985     my @subfield_refs = $field->subfields();
4986     push @combined_subfield_refs, @subfield_refs;
4987 }
4988
4989 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
4990
4991 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
4992     my ($code,$data) = @$tuple;
4993     unshift( @combined_subfields, $code, $data );
4994 }
4995
4996 $marc_xml->delete_fields(@fields);
4997
4998 my $new_field = new MARC::Field(
4999     $tag,
5000     $fields[0]->indicator(1),
5001     $fields[0]->indicator(2),
5002     @combined_subfields
5003 );
5004
5005 $marc_xml->insert_grouped_field( $new_field );
5006
5007 return $marc_xml->as_xml_record();
5008
5009 $function$;
5010
5011 -- convenience function for linking to the item staging table
5012
5013 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5014     DECLARE
5015         table_schema ALIAS FOR $1;
5016         table_name ALIAS FOR $2;
5017         foreign_column_name ALIAS FOR $3;
5018         main_column_name ALIAS FOR $4;
5019         btrim_desired ALIAS FOR $5;
5020         proceed BOOLEAN;
5021     BEGIN
5022         EXECUTE 'SELECT EXISTS (
5023             SELECT 1
5024             FROM information_schema.columns
5025             WHERE table_schema = $1
5026             AND table_name = $2
5027             and column_name = $3
5028         )' INTO proceed USING table_schema, table_name, foreign_column_name;
5029         IF NOT proceed THEN
5030             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5031         END IF;
5032
5033         EXECUTE 'SELECT EXISTS (
5034             SELECT 1
5035             FROM information_schema.columns
5036             WHERE table_schema = $1
5037             AND table_name = ''asset_copy_legacy''
5038             and column_name = $2
5039         )' INTO proceed USING table_schema, main_column_name;
5040         IF NOT proceed THEN
5041             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
5042         END IF;
5043
5044         EXECUTE 'ALTER TABLE '
5045             || quote_ident(table_name)
5046             || ' DROP COLUMN IF EXISTS x_item';
5047         EXECUTE 'ALTER TABLE '
5048             || quote_ident(table_name)
5049             || ' ADD COLUMN x_item BIGINT';
5050
5051         IF btrim_desired THEN
5052             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5053                 || ' SET x_item = b.id FROM asset_copy_legacy b'
5054                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5055                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5056         ELSE
5057             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5058                 || ' SET x_item = b.id FROM asset_copy_legacy b'
5059                 || ' WHERE a.' || quote_ident(foreign_column_name)
5060                 || ' = b.' || quote_ident(main_column_name);
5061         END IF;
5062
5063         --EXECUTE 'SELECT migration_tools.assert(
5064         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
5065         --    ''Cannot link every barcode'',
5066         --    ''Every barcode linked''
5067         --);';
5068
5069     END;
5070 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5071
5072 -- convenience function for linking to the user staging table
5073
5074 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5075     DECLARE
5076         table_schema ALIAS FOR $1;
5077         table_name ALIAS FOR $2;
5078         foreign_column_name ALIAS FOR $3;
5079         main_column_name ALIAS FOR $4;
5080         btrim_desired ALIAS FOR $5;
5081         proceed BOOLEAN;
5082     BEGIN
5083         EXECUTE 'SELECT EXISTS (
5084             SELECT 1
5085             FROM information_schema.columns
5086             WHERE table_schema = $1
5087             AND table_name = $2
5088             and column_name = $3
5089         )' INTO proceed USING table_schema, table_name, foreign_column_name;
5090         IF NOT proceed THEN
5091             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
5092         END IF;
5093
5094         EXECUTE 'SELECT EXISTS (
5095             SELECT 1
5096             FROM information_schema.columns
5097             WHERE table_schema = $1
5098             AND table_name = ''actor_usr_legacy''
5099             and column_name = $2
5100         )' INTO proceed USING table_schema, main_column_name;
5101         IF NOT proceed THEN
5102             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
5103         END IF;
5104
5105         EXECUTE 'ALTER TABLE '
5106             || quote_ident(table_name)
5107             || ' DROP COLUMN IF EXISTS x_user';
5108         EXECUTE 'ALTER TABLE '
5109             || quote_ident(table_name)
5110             || ' ADD COLUMN x_user INTEGER';
5111
5112         IF btrim_desired THEN
5113             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5114                 || ' SET x_user = b.id FROM actor_usr_legacy b'
5115                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
5116                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
5117         ELSE
5118             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
5119                 || ' SET x_user = b.id FROM actor_usr_legacy b'
5120                 || ' WHERE a.' || quote_ident(foreign_column_name)
5121                 || ' = b.' || quote_ident(main_column_name);
5122         END IF;
5123
5124         --EXECUTE 'SELECT migration_tools.assert(
5125         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
5126         --    ''Cannot link every barcode'',
5127         --    ''Every barcode linked''
5128         --);';
5129
5130     END;
5131 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5132
5133 -- convenience function for linking two tables
5134 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
5135 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5136     DECLARE
5137         table_schema ALIAS FOR $1;
5138         table_a ALIAS FOR $2;
5139         column_a ALIAS FOR $3;
5140         table_b ALIAS FOR $4;
5141         column_b ALIAS FOR $5;
5142         column_x ALIAS FOR $6;
5143         btrim_desired ALIAS FOR $7;
5144         proceed BOOLEAN;
5145     BEGIN
5146         EXECUTE 'SELECT EXISTS (
5147             SELECT 1
5148             FROM information_schema.columns
5149             WHERE table_schema = $1
5150             AND table_name = $2
5151             and column_name = $3
5152         )' INTO proceed USING table_schema, table_a, column_a;
5153         IF NOT proceed THEN
5154             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5155         END IF;
5156
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_b, column_b;
5164         IF NOT proceed THEN
5165             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5166         END IF;
5167
5168         EXECUTE 'ALTER TABLE '
5169             || quote_ident(table_b)
5170             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5171         EXECUTE 'ALTER TABLE '
5172             || quote_ident(table_b)
5173             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
5174
5175         IF btrim_desired THEN
5176             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5177                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5178                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5179                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5180         ELSE
5181             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5182                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
5183                 || ' WHERE a.' || quote_ident(column_a)
5184                 || ' = b.' || quote_ident(column_b);
5185         END IF;
5186
5187     END;
5188 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5189
5190 -- convenience function for linking two tables, but copying column w into column x instead of "id"
5191 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
5192 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
5193     DECLARE
5194         table_schema ALIAS FOR $1;
5195         table_a ALIAS FOR $2;
5196         column_a ALIAS FOR $3;
5197         table_b ALIAS FOR $4;
5198         column_b ALIAS FOR $5;
5199         column_w ALIAS FOR $6;
5200         column_x ALIAS FOR $7;
5201         btrim_desired ALIAS FOR $8;
5202         proceed BOOLEAN;
5203     BEGIN
5204         EXECUTE 'SELECT EXISTS (
5205             SELECT 1
5206             FROM information_schema.columns
5207             WHERE table_schema = $1
5208             AND table_name = $2
5209             and column_name = $3
5210         )' INTO proceed USING table_schema, table_a, column_a;
5211         IF NOT proceed THEN
5212             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5213         END IF;
5214
5215         EXECUTE 'SELECT EXISTS (
5216             SELECT 1
5217             FROM information_schema.columns
5218             WHERE table_schema = $1
5219             AND table_name = $2
5220             and column_name = $3
5221         )' INTO proceed USING table_schema, table_b, column_b;
5222         IF NOT proceed THEN
5223             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5224         END IF;
5225
5226         EXECUTE 'ALTER TABLE '
5227             || quote_ident(table_b)
5228             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
5229         EXECUTE 'ALTER TABLE '
5230             || quote_ident(table_b)
5231             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
5232
5233         IF btrim_desired THEN
5234             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5235                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5236                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
5237                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
5238         ELSE
5239             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5240                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5241                 || ' WHERE a.' || quote_ident(column_a)
5242                 || ' = b.' || quote_ident(column_b);
5243         END IF;
5244
5245     END;
5246 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5247
5248 -- 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
5249 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
5250 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5251     DECLARE
5252         table_schema ALIAS FOR $1;
5253         table_a ALIAS FOR $2;
5254         column_a ALIAS FOR $3;
5255         table_b ALIAS FOR $4;
5256         column_b ALIAS FOR $5;
5257         column_w ALIAS FOR $6;
5258         column_x ALIAS FOR $7;
5259         proceed BOOLEAN;
5260     BEGIN
5261         EXECUTE 'SELECT EXISTS (
5262             SELECT 1
5263             FROM information_schema.columns
5264             WHERE table_schema = $1
5265             AND table_name = $2
5266             and column_name = $3
5267         )' INTO proceed USING table_schema, table_a, column_a;
5268         IF NOT proceed THEN
5269             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5270         END IF;
5271
5272         EXECUTE 'SELECT EXISTS (
5273             SELECT 1
5274             FROM information_schema.columns
5275             WHERE table_schema = $1
5276             AND table_name = $2
5277             and column_name = $3
5278         )' INTO proceed USING table_schema, table_b, column_b;
5279         IF NOT proceed THEN
5280             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5281         END IF;
5282
5283         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5284             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5285             || ' WHERE a.' || quote_ident(column_a)
5286             || ' = b.' || quote_ident(column_b);
5287
5288     END;
5289 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5290
5291 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5292     DECLARE
5293         table_schema ALIAS FOR $1;
5294         table_a ALIAS FOR $2;
5295         column_a ALIAS FOR $3;
5296         table_b ALIAS FOR $4;
5297         column_b ALIAS FOR $5;
5298         column_w ALIAS FOR $6;
5299         column_x ALIAS FOR $7;
5300         proceed BOOLEAN;
5301     BEGIN
5302         EXECUTE 'SELECT EXISTS (
5303             SELECT 1
5304             FROM information_schema.columns
5305             WHERE table_schema = $1
5306             AND table_name = $2
5307             and column_name = $3
5308         )' INTO proceed USING table_schema, table_a, column_a;
5309         IF NOT proceed THEN
5310             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5311         END IF;
5312
5313         EXECUTE 'SELECT EXISTS (
5314             SELECT 1
5315             FROM information_schema.columns
5316             WHERE table_schema = $1
5317             AND table_name = $2
5318             and column_name = $3
5319         )' INTO proceed USING table_schema, table_b, column_b;
5320         IF NOT proceed THEN
5321             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5322         END IF;
5323
5324         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5325             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5326             || ' WHERE a.' || quote_ident(column_a)
5327             || ' = b.' || quote_ident(column_b)
5328             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5329
5330     END;
5331 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5332
5333 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5334     DECLARE
5335         table_schema ALIAS FOR $1;
5336         table_a ALIAS FOR $2;
5337         column_a ALIAS FOR $3;
5338         table_b ALIAS FOR $4;
5339         column_b ALIAS FOR $5;
5340         column_w ALIAS FOR $6;
5341         column_x ALIAS FOR $7;
5342         proceed BOOLEAN;
5343     BEGIN
5344         EXECUTE 'SELECT EXISTS (
5345             SELECT 1
5346             FROM information_schema.columns
5347             WHERE table_schema = $1
5348             AND table_name = $2
5349             and column_name = $3
5350         )' INTO proceed USING table_schema, table_a, column_a;
5351         IF NOT proceed THEN
5352             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5353         END IF;
5354
5355         EXECUTE 'SELECT EXISTS (
5356             SELECT 1
5357             FROM information_schema.columns
5358             WHERE table_schema = $1
5359             AND table_name = $2
5360             and column_name = $3
5361         )' INTO proceed USING table_schema, table_b, column_b;
5362         IF NOT proceed THEN
5363             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5364         END IF;
5365
5366         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5367             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5368             || ' WHERE a.' || quote_ident(column_a)
5369             || ' = b.' || quote_ident(column_b)
5370             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
5371
5372     END;
5373 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5374
5375 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5376     DECLARE
5377         table_schema ALIAS FOR $1;
5378         table_a ALIAS FOR $2;
5379         column_a ALIAS FOR $3;
5380         table_b ALIAS FOR $4;
5381         column_b ALIAS FOR $5;
5382         column_w ALIAS FOR $6;
5383         column_x ALIAS FOR $7;
5384         proceed BOOLEAN;
5385     BEGIN
5386         EXECUTE 'SELECT EXISTS (
5387             SELECT 1
5388             FROM information_schema.columns
5389             WHERE table_schema = $1
5390             AND table_name = $2
5391             and column_name = $3
5392         )' INTO proceed USING table_schema, table_a, column_a;
5393         IF NOT proceed THEN
5394             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5395         END IF;
5396
5397         EXECUTE 'SELECT EXISTS (
5398             SELECT 1
5399             FROM information_schema.columns
5400             WHERE table_schema = $1
5401             AND table_name = $2
5402             and column_name = $3
5403         )' INTO proceed USING table_schema, table_b, column_b;
5404         IF NOT proceed THEN
5405             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5406         END IF;
5407
5408         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5409             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5410             || ' WHERE a.' || quote_ident(column_a)
5411             || ' = b.' || quote_ident(column_b)
5412             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
5413
5414     END;
5415 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5416
5417 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5418     DECLARE
5419         table_schema ALIAS FOR $1;
5420         table_a ALIAS FOR $2;
5421         column_a ALIAS FOR $3;
5422         table_b ALIAS FOR $4;
5423         column_b ALIAS FOR $5;
5424         column_w ALIAS FOR $6;
5425         column_x ALIAS FOR $7;
5426         proceed BOOLEAN;
5427     BEGIN
5428         EXECUTE 'SELECT EXISTS (
5429             SELECT 1
5430             FROM information_schema.columns
5431             WHERE table_schema = $1
5432             AND table_name = $2
5433             and column_name = $3
5434         )' INTO proceed USING table_schema, table_a, column_a;
5435         IF NOT proceed THEN
5436             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5437         END IF;
5438
5439         EXECUTE 'SELECT EXISTS (
5440             SELECT 1
5441             FROM information_schema.columns
5442             WHERE table_schema = $1
5443             AND table_name = $2
5444             and column_name = $3
5445         )' INTO proceed USING table_schema, table_b, column_b;
5446         IF NOT proceed THEN
5447             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5448         END IF;
5449
5450         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5451             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
5452             || ' WHERE a.' || quote_ident(column_a)
5453             || ' = b.' || quote_ident(column_b)
5454             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
5455
5456     END;
5457 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5458
5459 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5460     DECLARE
5461         table_schema ALIAS FOR $1;
5462         table_a ALIAS FOR $2;
5463         column_a ALIAS FOR $3;
5464         table_b ALIAS FOR $4;
5465         column_b ALIAS FOR $5;
5466         column_w ALIAS FOR $6;
5467         column_x ALIAS FOR $7;
5468         proceed BOOLEAN;
5469     BEGIN
5470         EXECUTE 'SELECT EXISTS (
5471             SELECT 1
5472             FROM information_schema.columns
5473             WHERE table_schema = $1
5474             AND table_name = $2
5475             and column_name = $3
5476         )' INTO proceed USING table_schema, table_a, column_a;
5477         IF NOT proceed THEN
5478             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
5479         END IF;
5480
5481         EXECUTE 'SELECT EXISTS (
5482             SELECT 1
5483             FROM information_schema.columns
5484             WHERE table_schema = $1
5485             AND table_name = $2
5486             and column_name = $3
5487         )' INTO proceed USING table_schema, table_b, column_b;
5488         IF NOT proceed THEN
5489             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
5490         END IF;
5491
5492         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
5493             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
5494             || ' WHERE a.' || quote_ident(column_a)
5495             || ' = b.' || quote_ident(column_b)
5496             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
5497
5498     END;
5499 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5500
5501 -- convenience function for handling desired asset stat cats
5502
5503 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5504     DECLARE
5505         table_schema ALIAS FOR $1;
5506         table_name ALIAS FOR $2;
5507         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5508         org_shortname ALIAS FOR $4;
5509         proceed BOOLEAN;
5510         org INTEGER;
5511         org_list INTEGER[];
5512         sc TEXT;
5513         sce TEXT;
5514     BEGIN
5515
5516         SELECT 'desired_sc' || field_suffix INTO sc;
5517         SELECT 'desired_sce' || field_suffix INTO sce;
5518
5519         EXECUTE 'SELECT EXISTS (
5520             SELECT 1
5521             FROM information_schema.columns
5522             WHERE table_schema = $1
5523             AND table_name = $2
5524             and column_name = $3
5525         )' INTO proceed USING table_schema, table_name, sc;
5526         IF NOT proceed THEN
5527             RAISE EXCEPTION 'Missing column %', sc;
5528         END IF;
5529         EXECUTE 'SELECT EXISTS (
5530             SELECT 1
5531             FROM information_schema.columns
5532             WHERE table_schema = $1
5533             AND table_name = $2
5534             and column_name = $3
5535         )' INTO proceed USING table_schema, table_name, sce;
5536         IF NOT proceed THEN
5537             RAISE EXCEPTION 'Missing column %', sce;
5538         END IF;
5539
5540         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5541         IF org IS NULL THEN
5542             RAISE EXCEPTION 'Cannot find org by shortname';
5543         END IF;
5544         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5545
5546         -- caller responsible for their own truncates though we try to prevent duplicates
5547         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
5548             SELECT DISTINCT
5549                  $1
5550                 ,BTRIM('||sc||')
5551             FROM
5552                 ' || quote_ident(table_name) || '
5553             WHERE
5554                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5555                 AND NOT EXISTS (
5556                     SELECT id
5557                     FROM asset.stat_cat
5558                     WHERE owner = ANY ($2)
5559                     AND name = BTRIM('||sc||')
5560                 )
5561                 AND NOT EXISTS (
5562                     SELECT id
5563                     FROM asset_stat_cat
5564                     WHERE owner = ANY ($2)
5565                     AND name = BTRIM('||sc||')
5566                 )
5567             ORDER BY 2;'
5568         USING org, org_list;
5569
5570         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
5571             SELECT DISTINCT
5572                 COALESCE(
5573                     (SELECT id
5574                         FROM asset.stat_cat
5575                         WHERE owner = ANY ($2)
5576                         AND BTRIM('||sc||') = BTRIM(name))
5577                    ,(SELECT id
5578                         FROM asset_stat_cat
5579                         WHERE owner = ANY ($2)
5580                         AND BTRIM('||sc||') = BTRIM(name))
5581                 )
5582                 ,$1
5583                 ,BTRIM('||sce||')
5584             FROM
5585                 ' || quote_ident(table_name) || '
5586             WHERE
5587                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
5588                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
5589                 AND NOT EXISTS (
5590                     SELECT id
5591                     FROM asset.stat_cat_entry
5592                     WHERE stat_cat = (
5593                         SELECT id
5594                         FROM asset.stat_cat
5595                         WHERE owner = ANY ($2)
5596                         AND BTRIM('||sc||') = BTRIM(name)
5597                     ) AND value = BTRIM('||sce||')
5598                     AND owner = ANY ($2)
5599                 )
5600                 AND NOT EXISTS (
5601                     SELECT id
5602                     FROM asset_stat_cat_entry
5603                     WHERE stat_cat = (
5604                         SELECT id
5605                         FROM asset_stat_cat
5606                         WHERE owner = ANY ($2)
5607                         AND BTRIM('||sc||') = BTRIM(name)
5608                     ) AND value = BTRIM('||sce||')
5609                     AND owner = ANY ($2)
5610                 )
5611             ORDER BY 1,3;'
5612         USING org, org_list;
5613     END;
5614 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5615
5616 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
5617     DECLARE
5618         table_schema ALIAS FOR $1;
5619         table_name ALIAS FOR $2;
5620         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
5621         org_shortname ALIAS FOR $4;
5622         proceed BOOLEAN;
5623         org INTEGER;
5624         org_list INTEGER[];
5625         o INTEGER;
5626         sc TEXT;
5627         sce TEXT;
5628     BEGIN
5629         SELECT 'desired_sc' || field_suffix INTO sc;
5630         SELECT 'desired_sce' || field_suffix INTO sce;
5631         EXECUTE 'SELECT EXISTS (
5632             SELECT 1
5633             FROM information_schema.columns
5634             WHERE table_schema = $1
5635             AND table_name = $2
5636             and column_name = $3
5637         )' INTO proceed USING table_schema, table_name, sc;
5638         IF NOT proceed THEN
5639             RAISE EXCEPTION 'Missing column %', sc;
5640         END IF;
5641         EXECUTE 'SELECT EXISTS (
5642             SELECT 1
5643             FROM information_schema.columns
5644             WHERE table_schema = $1
5645             AND table_name = $2
5646             and column_name = $3
5647         )' INTO proceed USING table_schema, table_name, sce;
5648         IF NOT proceed THEN
5649             RAISE EXCEPTION 'Missing column %', sce;
5650         END IF;
5651
5652         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
5653         IF org IS NULL THEN
5654             RAISE EXCEPTION 'Cannot find org by shortname';
5655         END IF;
5656
5657         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
5658
5659         EXECUTE 'ALTER TABLE '
5660             || quote_ident(table_name)
5661             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
5662         EXECUTE 'ALTER TABLE '
5663             || quote_ident(table_name)
5664             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5665         EXECUTE 'ALTER TABLE '
5666             || quote_ident(table_name)
5667             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5668         EXECUTE 'ALTER TABLE '
5669             || quote_ident(table_name)
5670             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5671
5672
5673         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5674             SET
5675                 x_sc' || field_suffix || ' = id
5676             FROM
5677                 (SELECT id, name, owner FROM asset_stat_cat
5678                     UNION SELECT id, name, owner FROM asset.stat_cat) u
5679             WHERE
5680                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5681                 AND u.owner = ANY ($1);'
5682         USING org_list;
5683
5684         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5685             SET
5686                 x_sce' || field_suffix || ' = id
5687             FROM
5688                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5689                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5690             WHERE
5691                     u.stat_cat = x_sc' || field_suffix || '
5692                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5693                 AND u.owner = ANY ($1);'
5694         USING org_list;
5695
5696         EXECUTE 'SELECT migration_tools.assert(
5697             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5698             ''Cannot find a desired stat cat'',
5699             ''Found all desired stat cats''
5700         );';
5701
5702         EXECUTE 'SELECT migration_tools.assert(
5703             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5704             ''Cannot find a desired stat cat entry'',
5705             ''Found all desired stat cat entries''
5706         );';
5707
5708     END;
5709 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5710
5711 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5712 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5713  LANGUAGE plpgsql
5714 AS $function$
5715 DECLARE
5716     c_name     TEXT;
5717 BEGIN
5718
5719     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5720             table_name = t_name
5721             AND table_schema = s_name
5722             AND (data_type='text' OR data_type='character varying')
5723             AND column_name like 'l_%'
5724     LOOP
5725        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5726     END LOOP;
5727
5728     RETURN TRUE;
5729 END
5730 $function$;
5731
5732 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5733 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5734  LANGUAGE plpgsql
5735 AS $function$
5736 DECLARE
5737     c_name     TEXT;
5738 BEGIN
5739
5740     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5741             table_name = t_name
5742             AND table_schema = s_name
5743             AND (data_type='text' OR data_type='character varying')
5744     LOOP
5745        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5746     END LOOP;
5747
5748     RETURN TRUE;
5749 END
5750 $function$;
5751
5752 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5753 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5754  LANGUAGE plpgsql
5755 AS $function$
5756 DECLARE
5757     c_name     TEXT;
5758 BEGIN
5759
5760     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5761             table_name = t_name
5762             AND table_schema = s_name
5763             AND (data_type='text' OR data_type='character varying')
5764             AND column_name like 'l_%'
5765     LOOP
5766        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5767     END LOOP;
5768
5769     RETURN TRUE;
5770 END
5771 $function$;
5772
5773 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5774 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5775  LANGUAGE plpgsql
5776 AS $function$
5777 DECLARE
5778     c_name     TEXT;
5779 BEGIN
5780
5781     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5782             table_name = t_name
5783             AND table_schema = s_name
5784             AND (data_type='text' OR data_type='character varying')
5785     LOOP
5786        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5787     END LOOP;
5788
5789     RETURN TRUE;
5790 END
5791 $function$;
5792
5793
5794 -- convenience function for handling item barcode collisions in asset_copy_legacy
5795
5796 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5797 DECLARE
5798     x_barcode TEXT;
5799     x_id BIGINT;
5800     row_count NUMERIC;
5801     internal_collision_count NUMERIC := 0;
5802     incumbent_collision_count NUMERIC := 0;
5803 BEGIN
5804     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5805     LOOP
5806         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5807         LOOP
5808             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5809             GET DIAGNOSTICS row_count = ROW_COUNT;
5810             internal_collision_count := internal_collision_count + row_count;
5811         END LOOP;
5812     END LOOP;
5813     RAISE INFO '% internal collisions', internal_collision_count;
5814     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
5815     LOOP
5816         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5817         LOOP
5818             UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
5819             GET DIAGNOSTICS row_count = ROW_COUNT;
5820             incumbent_collision_count := incumbent_collision_count + row_count;
5821         END LOOP;
5822     END LOOP;
5823     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5824 END
5825 $function$ LANGUAGE plpgsql;
5826
5827 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5828 -- this should be ran prior to populating actor_card
5829
5830 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
5831 DECLARE
5832     x_barcode TEXT;
5833     x_id BIGINT;
5834     row_count NUMERIC;
5835     internal_collision_count NUMERIC := 0;
5836     incumbent_barcode_collision_count NUMERIC := 0;
5837     incumbent_usrname_collision_count NUMERIC := 0;
5838 BEGIN
5839     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5840     LOOP
5841         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5842         LOOP
5843             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5844             GET DIAGNOSTICS row_count = ROW_COUNT;
5845             internal_collision_count := internal_collision_count + row_count;
5846         END LOOP;
5847     END LOOP;
5848     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5849
5850     FOR x_barcode IN
5851         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5852     LOOP
5853         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5854         LOOP
5855             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
5856             GET DIAGNOSTICS row_count = ROW_COUNT;
5857             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5858         END LOOP;
5859     END LOOP;
5860     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5861
5862     FOR x_barcode IN
5863         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5864     LOOP
5865         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5866         LOOP
5867             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
5868             GET DIAGNOSTICS row_count = ROW_COUNT;
5869             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5870         END LOOP;
5871     END LOOP;
5872     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5873 END
5874 $function$ LANGUAGE plpgsql;
5875
5876 -- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
5877
5878 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5879 DECLARE
5880     x_barcode TEXT;
5881     x_id BIGINT;
5882     row_count NUMERIC;
5883     internal_collision_count NUMERIC := 0;
5884     incumbent_collision_count NUMERIC := 0;
5885 BEGIN
5886     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5887     LOOP
5888         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
5889         LOOP
5890             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
5891             GET DIAGNOSTICS row_count = ROW_COUNT;
5892             internal_collision_count := internal_collision_count + row_count;
5893         END LOOP;
5894     END LOOP;
5895     RAISE INFO '% internal collisions', internal_collision_count;
5896     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
5897     LOOP
5898         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
5899         LOOP
5900             UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
5901             GET DIAGNOSTICS row_count = ROW_COUNT;
5902             incumbent_collision_count := incumbent_collision_count + row_count;
5903         END LOOP;
5904     END LOOP;
5905     RAISE INFO '% incumbent collisions', incumbent_collision_count;
5906 END
5907 $function$ LANGUAGE plpgsql;
5908
5909 -- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
5910 -- this should be ran prior to populating actor_card
5911
5912 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
5913 DECLARE
5914     x_barcode TEXT;
5915     x_id BIGINT;
5916     row_count NUMERIC;
5917     internal_collision_count NUMERIC := 0;
5918     incumbent_barcode_collision_count NUMERIC := 0;
5919     incumbent_usrname_collision_count NUMERIC := 0;
5920 BEGIN
5921     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
5922     LOOP
5923         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5924         LOOP
5925             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
5926             GET DIAGNOSTICS row_count = ROW_COUNT;
5927             internal_collision_count := internal_collision_count + row_count;
5928         END LOOP;
5929     END LOOP;
5930     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
5931
5932     FOR x_barcode IN
5933         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
5934     LOOP
5935         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5936         LOOP
5937             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5938             GET DIAGNOSTICS row_count = ROW_COUNT;
5939             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
5940         END LOOP;
5941     END LOOP;
5942     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
5943
5944     FOR x_barcode IN
5945         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
5946     LOOP
5947         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
5948         LOOP
5949             UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
5950             GET DIAGNOSTICS row_count = ROW_COUNT;
5951             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
5952         END LOOP;
5953     END LOOP;
5954     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
5955 END
5956 $function$ LANGUAGE plpgsql;
5957
5958 CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
5959 -- WARNING: Use at your own risk
5960 -- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
5961 DECLARE
5962     item_object asset.copy%ROWTYPE;
5963     user_object actor.usr%ROWTYPE;
5964     test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5965     result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
5966     safe_to_delete BOOLEAN := FALSE;
5967     m action.found_circ_matrix_matchpoint;
5968     n action.found_circ_matrix_matchpoint;
5969     -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
5970     result_matchpoint INTEGER;
5971 BEGIN
5972     SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
5973     RAISE INFO 'testing rule: %', test_rule_object;
5974
5975     INSERT INTO actor.usr (
5976         profile,
5977         usrname,
5978         passwd,
5979         ident_type,
5980         first_given_name,
5981         family_name,
5982         home_ou,
5983         juvenile
5984     ) SELECT
5985         COALESCE(test_rule_object.grp, 2),
5986         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
5987         MD5(NOW()::TEXT),
5988         1,
5989         'Ima',
5990         'Test',
5991         COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
5992         COALESCE(test_rule_object.juvenile_flag, FALSE)
5993     ;
5994
5995     SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
5996
5997     INSERT INTO asset.call_number (
5998         creator,
5999         editor,
6000         record,
6001         owning_lib,
6002         label,
6003         label_class
6004     ) SELECT
6005         1,
6006         1,
6007         -1,
6008         COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
6009         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6010         1
6011     ;
6012
6013     INSERT INTO asset.copy (
6014         barcode,
6015         circ_lib,
6016         creator,
6017         call_number,
6018         editor,
6019         location,
6020         loan_duration,
6021         fine_level,
6022         ref,
6023         circ_modifier
6024     ) SELECT
6025         'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
6026         COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
6027         1,
6028         currval('asset.call_number_id_seq'),
6029         1,
6030         COALESCE(test_rule_object.copy_location,1),
6031         2,
6032         2,
6033         COALESCE(test_rule_object.ref_flag,FALSE),
6034         test_rule_object.circ_modifier
6035     ;
6036
6037     SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
6038
6039     SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
6040         test_rule_object.org_unit,
6041         item_object,
6042         user_object,
6043         COALESCE(test_rule_object.is_renewal,FALSE)
6044     );
6045     RAISE INFO '   action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6046         test_rule_object.org_unit,
6047         item_object.id,
6048         user_object.id,
6049         COALESCE(test_rule_object.is_renewal,FALSE),
6050         m.success,
6051         m.matchpoint,
6052         m.buildrows
6053     ;
6054
6055     --  disable the rule being tested to see if the outcome changes
6056     UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
6057
6058     SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
6059         test_rule_object.org_unit,
6060         item_object,
6061         user_object,
6062         COALESCE(test_rule_object.is_renewal,FALSE)
6063     );
6064     RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
6065         test_rule_object.org_unit,
6066         item_object.id,
6067         user_object.id,
6068         COALESCE(test_rule_object.is_renewal,FALSE),
6069         n.success,
6070         n.matchpoint,
6071         n.buildrows
6072     ;
6073
6074     -- FIXME: We could dig deeper and see if the referenced config.rule_*
6075     -- entries are effectively equivalent, but for now, let's assume no
6076     -- duplicate rules at that level
6077     IF (
6078             (m.matchpoint).circulate = (n.matchpoint).circulate
6079         AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
6080         AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
6081         AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
6082         AND (
6083                 (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
6084                 OR (
6085                         (m.matchpoint).hard_due_date IS NULL
6086                     AND (n.matchpoint).hard_due_date IS NULL
6087                 )
6088         )
6089         AND (
6090                 (m.matchpoint).renewals = (n.matchpoint).renewals
6091                 OR (
6092                         (m.matchpoint).renewals IS NULL
6093                     AND (n.matchpoint).renewals IS NULL
6094                 )
6095         )
6096         AND (
6097                 (m.matchpoint).grace_period = (n.matchpoint).grace_period
6098                 OR (
6099                         (m.matchpoint).grace_period IS NULL
6100                     AND (n.matchpoint).grace_period IS NULL
6101                 )
6102         )
6103         AND (
6104                 (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
6105                 OR (
6106                         (m.matchpoint).total_copy_hold_ratio IS NULL
6107                     AND (n.matchpoint).total_copy_hold_ratio IS NULL
6108                 )
6109         )
6110         AND (
6111                 (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
6112                 OR (
6113                         (m.matchpoint).available_copy_hold_ratio IS NULL
6114                     AND (n.matchpoint).available_copy_hold_ratio IS NULL
6115                 )
6116         )
6117         AND NOT EXISTS (
6118             SELECT limit_set, fallthrough
6119             FROM config.circ_matrix_limit_set_map
6120             WHERE active and matchpoint = (m.matchpoint).id
6121             EXCEPT
6122             SELECT limit_set, fallthrough
6123             FROM config.circ_matrix_limit_set_map
6124             WHERE active and matchpoint = (n.matchpoint).id
6125         )
6126
6127     ) THEN
6128         RAISE INFO 'rule has same outcome';
6129         safe_to_delete := TRUE;
6130     ELSE
6131         RAISE INFO 'rule has different outcome';
6132         safe_to_delete := FALSE;
6133     END IF;
6134
6135     RAISE EXCEPTION 'rollback the temporary changes';
6136
6137 EXCEPTION WHEN OTHERS THEN
6138
6139     RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
6140     RETURN safe_to_delete;
6141
6142 END;
6143 $func$ LANGUAGE plpgsql;
6144