sql/base/base.sql

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