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         org_list INTEGER[];
3521         o INTEGER;
3522     BEGIN
3523         EXECUTE 'SELECT EXISTS (
3524             SELECT 1
3525             FROM information_schema.columns
3526             WHERE table_schema = $1
3527             AND table_name = $2
3528             and column_name = ''desired_shelf''
3529         )' INTO proceed USING table_schema, table_name;
3530         IF NOT proceed THEN
3531             RAISE EXCEPTION 'Missing column desired_shelf';
3532         END IF;
3533
3534         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3535         IF org IS NULL THEN
3536             RAISE EXCEPTION 'Cannot find org by shortname';
3537         END IF;
3538
3539         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3540
3541         EXECUTE 'ALTER TABLE '
3542             || quote_ident(table_name)
3543             || ' DROP COLUMN IF EXISTS x_shelf';
3544         EXECUTE 'ALTER TABLE '
3545             || quote_ident(table_name)
3546             || ' ADD COLUMN x_shelf INTEGER';
3547
3548         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3549             || ' SET x_shelf = id FROM asset_copy_location b'
3550             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3551             || ' AND b.owning_lib = $1'
3552             || ' AND NOT b.deleted'
3553         USING org;
3554
3555         FOREACH o IN ARRAY org_list LOOP
3556             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3557                 || ' SET x_shelf = id FROM asset.copy_location b'
3558                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3559                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3560                 || ' AND NOT b.deleted'
3561             USING o;
3562         END LOOP;
3563
3564         EXECUTE 'SELECT migration_tools.assert(
3565             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3566             ''Cannot find a desired location'',
3567             ''Found all desired locations''
3568         );';
3569
3570     END;
3571 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3572
3573 -- convenience functions for handling circmod maps
3574
3575 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3576     DECLARE
3577         table_schema ALIAS FOR $1;
3578         table_name ALIAS FOR $2;
3579         proceed BOOLEAN;
3580     BEGIN
3581         EXECUTE 'SELECT EXISTS (
3582             SELECT 1
3583             FROM information_schema.columns
3584             WHERE table_schema = $1
3585             AND table_name = $2
3586             and column_name = ''desired_circmod''
3587         )' INTO proceed USING table_schema, table_name;
3588         IF NOT proceed THEN
3589             RAISE EXCEPTION 'Missing column desired_circmod';
3590         END IF;
3591
3592         EXECUTE 'ALTER TABLE '
3593             || quote_ident(table_name)
3594             || ' DROP COLUMN IF EXISTS x_circmod';
3595         EXECUTE 'ALTER TABLE '
3596             || quote_ident(table_name)
3597             || ' ADD COLUMN x_circmod TEXT';
3598
3599         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3600             || ' SET x_circmod = code FROM config.circ_modifier b'
3601             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3602
3603         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3604             || ' SET x_circmod = code FROM config.circ_modifier b'
3605             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3606             || ' AND x_circmod IS NULL';
3607
3608         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3609             || ' SET x_circmod = code FROM config.circ_modifier b'
3610             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3611             || ' AND x_circmod IS NULL';
3612
3613         EXECUTE 'SELECT migration_tools.assert(
3614             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3615             ''Cannot find a desired circulation modifier'',
3616             ''Found all desired circulation modifiers''
3617         );';
3618
3619     END;
3620 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3621
3622 -- convenience functions for handling item status maps
3623
3624 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3625     DECLARE
3626         table_schema ALIAS FOR $1;
3627         table_name ALIAS FOR $2;
3628         proceed BOOLEAN;
3629     BEGIN
3630         EXECUTE 'SELECT EXISTS (
3631             SELECT 1
3632             FROM information_schema.columns
3633             WHERE table_schema = $1
3634             AND table_name = $2
3635             and column_name = ''desired_status''
3636         )' INTO proceed USING table_schema, table_name;
3637         IF NOT proceed THEN
3638             RAISE EXCEPTION 'Missing column desired_status';
3639         END IF;
3640
3641         EXECUTE 'ALTER TABLE '
3642             || quote_ident(table_name)
3643             || ' DROP COLUMN IF EXISTS x_status';
3644         EXECUTE 'ALTER TABLE '
3645             || quote_ident(table_name)
3646             || ' ADD COLUMN x_status INTEGER';
3647
3648         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3649             || ' SET x_status = id FROM config.copy_status b'
3650             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3651
3652         EXECUTE 'SELECT migration_tools.assert(
3653             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3654             ''Cannot find a desired copy status'',
3655             ''Found all desired copy statuses''
3656         );';
3657
3658     END;
3659 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3660
3661 -- convenience functions for handling org maps
3662
3663 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3664     DECLARE
3665         table_schema ALIAS FOR $1;
3666         table_name ALIAS FOR $2;
3667         proceed BOOLEAN;
3668     BEGIN
3669         EXECUTE 'SELECT EXISTS (
3670             SELECT 1
3671             FROM information_schema.columns
3672             WHERE table_schema = $1
3673             AND table_name = $2
3674             and column_name = ''desired_org''
3675         )' INTO proceed USING table_schema, table_name;
3676         IF NOT proceed THEN
3677             RAISE EXCEPTION 'Missing column desired_org';
3678         END IF;
3679
3680         EXECUTE 'ALTER TABLE '
3681             || quote_ident(table_name)
3682             || ' DROP COLUMN IF EXISTS x_org';
3683         EXECUTE 'ALTER TABLE '
3684             || quote_ident(table_name)
3685             || ' ADD COLUMN x_org INTEGER';
3686
3687         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3688             || ' SET x_org = id FROM actor.org_unit b'
3689             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3690
3691         EXECUTE 'SELECT migration_tools.assert(
3692             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3693             ''Cannot find a desired org unit'',
3694             ''Found all desired org units''
3695         );';
3696
3697     END;
3698 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3699
3700 -- convenience function for handling desired_not_migrate
3701
3702 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3703     DECLARE
3704         table_schema ALIAS FOR $1;
3705         table_name ALIAS FOR $2;
3706         proceed BOOLEAN;
3707     BEGIN
3708         EXECUTE 'SELECT EXISTS (
3709             SELECT 1
3710             FROM information_schema.columns
3711             WHERE table_schema = $1
3712             AND table_name = $2
3713             and column_name = ''desired_not_migrate''
3714         )' INTO proceed USING table_schema, table_name;
3715         IF NOT proceed THEN
3716             RAISE EXCEPTION 'Missing column desired_not_migrate';
3717         END IF;
3718
3719         EXECUTE 'ALTER TABLE '
3720             || quote_ident(table_name)
3721             || ' DROP COLUMN IF EXISTS x_migrate';
3722         EXECUTE 'ALTER TABLE '
3723             || quote_ident(table_name)
3724             || ' ADD COLUMN x_migrate BOOLEAN';
3725
3726         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3727             || ' SET x_migrate = CASE'
3728             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3729             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3730             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3731             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3732             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3733             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3734             || ' END';
3735
3736         EXECUTE 'SELECT migration_tools.assert(
3737             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3738             ''Not all desired_not_migrate values understood'',
3739             ''All desired_not_migrate values understood''
3740         );';
3741
3742     END;
3743 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3744
3745 -- convenience function for handling desired_not_migrate
3746
3747 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3748     DECLARE
3749         table_schema ALIAS FOR $1;
3750         table_name ALIAS FOR $2;
3751         proceed BOOLEAN;
3752     BEGIN
3753         EXECUTE 'SELECT EXISTS (
3754             SELECT 1
3755             FROM information_schema.columns
3756             WHERE table_schema = $1
3757             AND table_name = $2
3758             and column_name = ''desired_barred_or_blocked''
3759         )' INTO proceed USING table_schema, table_name;
3760         IF NOT proceed THEN
3761             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3762         END IF;
3763
3764         EXECUTE 'ALTER TABLE '
3765             || quote_ident(table_name)
3766             || ' DROP COLUMN IF EXISTS x_barred';
3767         EXECUTE 'ALTER TABLE '
3768             || quote_ident(table_name)
3769             || ' ADD COLUMN x_barred BOOLEAN';
3770
3771         EXECUTE 'ALTER TABLE '
3772             || quote_ident(table_name)
3773             || ' DROP COLUMN IF EXISTS x_blocked';
3774         EXECUTE 'ALTER TABLE '
3775             || quote_ident(table_name)
3776             || ' ADD COLUMN x_blocked BOOLEAN';
3777
3778         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3779             || ' SET x_barred = CASE'
3780             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3781             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3782             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3783             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3784             || ' END';
3785
3786         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3787             || ' SET x_blocked = CASE'
3788             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3789             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3790             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3791             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3792             || ' END';
3793
3794         EXECUTE 'SELECT migration_tools.assert(
3795             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3796             ''Not all desired_barred_or_blocked values understood'',
3797             ''All desired_barred_or_blocked values understood''
3798         );';
3799
3800     END;
3801 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3802
3803 -- convenience function for handling desired_profile
3804
3805 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3806     DECLARE
3807         table_schema ALIAS FOR $1;
3808         table_name ALIAS FOR $2;
3809         proceed BOOLEAN;
3810     BEGIN
3811         EXECUTE 'SELECT EXISTS (
3812             SELECT 1
3813             FROM information_schema.columns
3814             WHERE table_schema = $1
3815             AND table_name = $2
3816             and column_name = ''desired_profile''
3817         )' INTO proceed USING table_schema, table_name;
3818         IF NOT proceed THEN
3819             RAISE EXCEPTION 'Missing column desired_profile';
3820         END IF;
3821
3822         EXECUTE 'ALTER TABLE '
3823             || quote_ident(table_name)
3824             || ' DROP COLUMN IF EXISTS x_profile';
3825         EXECUTE 'ALTER TABLE '
3826             || quote_ident(table_name)
3827             || ' ADD COLUMN x_profile INTEGER';
3828
3829         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3830             || ' SET x_profile = b.id FROM permission.grp_tree b'
3831             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3832
3833         EXECUTE 'SELECT migration_tools.assert(
3834             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3835             ''Cannot find a desired profile'',
3836             ''Found all desired profiles''
3837         );';
3838
3839     END;
3840 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3841
3842 -- convenience function for handling desired actor stat cats
3843
3844 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3845     DECLARE
3846         table_schema ALIAS FOR $1;
3847         table_name ALIAS FOR $2;
3848         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3849         org_shortname ALIAS FOR $4;
3850         proceed BOOLEAN;
3851         org INTEGER;
3852         org_list INTEGER[];
3853         sc TEXT;
3854         sce TEXT;
3855     BEGIN
3856
3857         SELECT 'desired_sc' || field_suffix INTO sc;
3858         SELECT 'desired_sce' || field_suffix INTO sce;
3859
3860         EXECUTE 'SELECT EXISTS (
3861             SELECT 1
3862             FROM information_schema.columns
3863             WHERE table_schema = $1
3864             AND table_name = $2
3865             and column_name = $3
3866         )' INTO proceed USING table_schema, table_name, sc;
3867         IF NOT proceed THEN
3868             RAISE EXCEPTION 'Missing column %', sc;
3869         END IF;
3870         EXECUTE 'SELECT EXISTS (
3871             SELECT 1
3872             FROM information_schema.columns
3873             WHERE table_schema = $1
3874             AND table_name = $2
3875             and column_name = $3
3876         )' INTO proceed USING table_schema, table_name, sce;
3877         IF NOT proceed THEN
3878             RAISE EXCEPTION 'Missing column %', sce;
3879         END IF;
3880
3881         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3882         IF org IS NULL THEN
3883             RAISE EXCEPTION 'Cannot find org by shortname';
3884         END IF;
3885         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3886
3887         -- caller responsible for their own truncates though we try to prevent duplicates
3888         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3889             SELECT DISTINCT
3890                  $1
3891                 ,BTRIM('||sc||')
3892             FROM
3893                 ' || quote_ident(table_name) || '
3894             WHERE
3895                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3896                 AND NOT EXISTS (
3897                     SELECT id
3898                     FROM actor.stat_cat
3899                     WHERE owner = ANY ($2)
3900                     AND name = BTRIM('||sc||')
3901                 )
3902                 AND NOT EXISTS (
3903                     SELECT id
3904                     FROM actor_stat_cat
3905                     WHERE owner = ANY ($2)
3906                     AND name = BTRIM('||sc||')
3907                 )
3908             ORDER BY 2;'
3909         USING org, org_list;
3910
3911         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3912             SELECT DISTINCT
3913                 COALESCE(
3914                     (SELECT id
3915                         FROM actor.stat_cat
3916                         WHERE owner = ANY ($2)
3917                         AND BTRIM('||sc||') = BTRIM(name))
3918                    ,(SELECT id
3919                         FROM actor_stat_cat
3920                         WHERE owner = ANY ($2)
3921                         AND BTRIM('||sc||') = BTRIM(name))
3922                 )
3923                 ,$1
3924                 ,BTRIM('||sce||')
3925             FROM
3926                 ' || quote_ident(table_name) || '
3927             WHERE
3928                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3929                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3930                 AND NOT EXISTS (
3931                     SELECT id
3932                     FROM actor.stat_cat_entry
3933                     WHERE stat_cat = (
3934                         SELECT id
3935                         FROM actor.stat_cat
3936                         WHERE owner = ANY ($2)
3937                         AND BTRIM('||sc||') = BTRIM(name)
3938                     ) AND value = BTRIM('||sce||')
3939                     AND owner = ANY ($2)
3940                 )
3941                 AND NOT EXISTS (
3942                     SELECT id
3943                     FROM actor_stat_cat_entry
3944                     WHERE stat_cat = (
3945                         SELECT id
3946                         FROM actor_stat_cat
3947                         WHERE owner = ANY ($2)
3948                         AND BTRIM('||sc||') = BTRIM(name)
3949                     ) AND value = BTRIM('||sce||')
3950                     AND owner = ANY ($2)
3951                 )
3952             ORDER BY 1,3;'
3953         USING org, org_list;
3954     END;
3955 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3956
3957 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3958     DECLARE
3959         table_schema ALIAS FOR $1;
3960         table_name ALIAS FOR $2;
3961         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3962         org_shortname ALIAS FOR $4;
3963         proceed BOOLEAN;
3964         org INTEGER;
3965         org_list INTEGER[];
3966         o INTEGER;
3967         sc TEXT;
3968         sce TEXT;
3969     BEGIN
3970         SELECT 'desired_sc' || field_suffix INTO sc;
3971         SELECT 'desired_sce' || field_suffix INTO sce;
3972         EXECUTE 'SELECT EXISTS (
3973             SELECT 1
3974             FROM information_schema.columns
3975             WHERE table_schema = $1
3976             AND table_name = $2
3977             and column_name = $3
3978         )' INTO proceed USING table_schema, table_name, sc;
3979         IF NOT proceed THEN
3980             RAISE EXCEPTION 'Missing column %', sc;
3981         END IF;
3982         EXECUTE 'SELECT EXISTS (
3983             SELECT 1
3984             FROM information_schema.columns
3985             WHERE table_schema = $1
3986             AND table_name = $2
3987             and column_name = $3
3988         )' INTO proceed USING table_schema, table_name, sce;
3989         IF NOT proceed THEN
3990             RAISE EXCEPTION 'Missing column %', sce;
3991         END IF;
3992
3993         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3994         IF org IS NULL THEN
3995             RAISE EXCEPTION 'Cannot find org by shortname';
3996         END IF;
3997
3998         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3999
4000         EXECUTE 'ALTER TABLE '
4001             || quote_ident(table_name)
4002             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4003         EXECUTE 'ALTER TABLE '
4004             || quote_ident(table_name)
4005             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4006         EXECUTE 'ALTER TABLE '
4007             || quote_ident(table_name)
4008             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4009         EXECUTE 'ALTER TABLE '
4010             || quote_ident(table_name)
4011             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4012
4013
4014         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4015             SET
4016                 x_sc' || field_suffix || ' = id
4017             FROM
4018                 (SELECT id, name, owner FROM actor_stat_cat
4019                     UNION SELECT id, name, owner FROM actor.stat_cat) u
4020             WHERE
4021                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4022                 AND u.owner = ANY ($1);'
4023         USING org_list;
4024
4025         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4026             SET
4027                 x_sce' || field_suffix || ' = id
4028             FROM
4029                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4030                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4031             WHERE
4032                     u.stat_cat = x_sc' || field_suffix || '
4033                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4034                 AND u.owner = ANY ($1);'
4035         USING org_list;
4036
4037         EXECUTE 'SELECT migration_tools.assert(
4038             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4039             ''Cannot find a desired stat cat'',
4040             ''Found all desired stat cats''
4041         );';
4042
4043         EXECUTE 'SELECT migration_tools.assert(
4044             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4045             ''Cannot find a desired stat cat entry'',
4046             ''Found all desired stat cat entries''
4047         );';
4048
4049     END;
4050 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4051
4052 -- convenience functions for adding shelving locations
4053 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4054 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4055 DECLARE
4056     return_id   INT;
4057     d           INT;
4058     cur_id      INT;
4059 BEGIN
4060     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4061     WHILE d >= 0
4062     LOOP
4063         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4064         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4065         IF return_id IS NOT NULL THEN
4066                 RETURN return_id;
4067         END IF;
4068         d := d - 1;
4069     END LOOP;
4070
4071     RETURN NULL;
4072 END
4073 $$ LANGUAGE plpgsql;
4074
4075 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4076
4077 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4078 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4079 DECLARE
4080     return_id   INT;
4081     d           INT;
4082     cur_id      INT;
4083 BEGIN
4084     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4085     WHILE d >= 0
4086     LOOP
4087         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4088
4089         SELECT INTO return_id id FROM
4090             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4091             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4092         IF return_id IS NOT NULL THEN
4093                 RETURN return_id;
4094         END IF;
4095         d := d - 1;
4096     END LOOP;
4097
4098     RETURN NULL;
4099 END
4100 $$ LANGUAGE plpgsql;
4101
4102 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
4103 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
4104 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
4105  RETURNS TEXT
4106  LANGUAGE plperlu
4107 AS $function$
4108 use strict;
4109 use warnings;
4110
4111 use MARC::Record;
4112 use MARC::File::XML (BinaryEncoding => 'utf8');
4113
4114 binmode(STDERR, ':bytes');
4115 binmode(STDOUT, ':utf8');
4116 binmode(STDERR, ':utf8');
4117
4118 my $marc_xml = shift;
4119 my $new_9_to_set = shift;
4120
4121 $marc_xml =~ s/(<leader>.........)./${1}a/;
4122
4123 eval {
4124     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4125 };
4126 if ($@) {
4127     #elog("could not parse $bibid: $@\n");
4128     import MARC::File::XML (BinaryEncoding => 'utf8');
4129     return $marc_xml;
4130 }
4131
4132 my @uris = $marc_xml->field('856');
4133 return $marc_xml->as_xml_record() unless @uris;
4134
4135 foreach my $field (@uris) {
4136     my $ind1 = $field->indicator('1');
4137     if (!defined $ind1) { next; }
4138     if ($ind1 ne '1' && $ind1 ne '4') { next; }
4139     my $ind2 = $field->indicator('2');
4140     if (!defined $ind2) { next; }
4141     if ($ind2 ne '0' && $ind2 ne '1') { next; }
4142     $field->add_subfields( '9' => $new_9_to_set );
4143 }
4144
4145 return $marc_xml->as_xml_record();
4146
4147 $function$;
4148
4149 -- yet another subfield 9 function, this one only adds the $9 and forces
4150 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
4151 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
4152 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
4153  RETURNS TEXT
4154  LANGUAGE plperlu
4155 AS $function$
4156 use strict;
4157 use warnings;
4158
4159 use MARC::Record;
4160 use MARC::File::XML (BinaryEncoding => 'utf8');
4161
4162 binmode(STDERR, ':bytes');
4163 binmode(STDOUT, ':utf8');
4164 binmode(STDERR, ':utf8');
4165
4166 my $marc_xml = shift;
4167 my $new_9_to_set = shift;
4168
4169 $marc_xml =~ s/(<leader>.........)./${1}a/;
4170
4171 eval {
4172     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4173 };
4174 if ($@) {
4175     #elog("could not parse $bibid: $@\n");
4176     import MARC::File::XML (BinaryEncoding => 'utf8');
4177     return $marc_xml;
4178 }
4179
4180 my @uris = $marc_xml->field('856');
4181 return $marc_xml->as_xml_record() unless @uris;
4182
4183 foreach my $field (@uris) {
4184     my $ind1 = $field->indicator('1');
4185     if (!defined $ind1) { next; }
4186     if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
4187     my $ind2 = $field->indicator('2');
4188     if (!defined $ind2) { next; }
4189     if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
4190     $field->add_subfields( '9' => $new_9_to_set );
4191 }
4192
4193 return $marc_xml->as_xml_record();
4194
4195 $function$;
4196
4197 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
4198 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
4199 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
4200  RETURNS TEXT
4201  LANGUAGE plperlu
4202 AS $function$
4203 use strict;
4204 use warnings;
4205
4206 use MARC::Record;
4207 use MARC::File::XML (BinaryEncoding => 'utf8');
4208
4209 binmode(STDERR, ':bytes');
4210 binmode(STDOUT, ':utf8');
4211 binmode(STDERR, ':utf8');
4212
4213 my $marc_xml = shift;
4214 my $matching_u_text = shift;
4215 my $new_9_to_set = shift;
4216
4217 $marc_xml =~ s/(<leader>.........)./${1}a/;
4218
4219 eval {
4220     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4221 };
4222 if ($@) {
4223     #elog("could not parse $bibid: $@\n");
4224     import MARC::File::XML (BinaryEncoding => 'utf8');
4225     return;
4226 }
4227
4228 my @uris = $marc_xml->field('856');
4229 return unless @uris;
4230
4231 foreach my $field (@uris) {
4232     my $sfu = $field->subfield('u');
4233     my $ind2 = $field->indicator('2');
4234     if (!defined $ind2) { next; }
4235     if ($ind2 ne '0') { next; }
4236     if (!defined $sfu) { next; }
4237     if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
4238         $field->add_subfields( '9' => $new_9_to_set );
4239         last;
4240     }
4241 }
4242
4243 return $marc_xml->as_xml_record();
4244
4245 $function$;
4246
4247 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
4248 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
4249     RETURNS BOOLEAN AS
4250 $BODY$
4251 DECLARE
4252     source_xml    TEXT;
4253     new_xml       TEXT;
4254     r             BOOLEAN;
4255 BEGIN
4256
4257     EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
4258
4259     SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
4260
4261     r = FALSE;
4262         new_xml = '$_$' || new_xml || '$_$';
4263
4264     IF new_xml != source_xml THEN
4265         EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
4266         r = TRUE;
4267     END IF;
4268
4269     RETURN r;
4270
4271 END;
4272 $BODY$ LANGUAGE plpgsql;
4273
4274 -- strip marc tag
4275 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4276 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4277  RETURNS TEXT
4278  LANGUAGE plperlu
4279 AS $function$
4280 use strict;
4281 use warnings;
4282
4283 use MARC::Record;
4284 use MARC::File::XML (BinaryEncoding => 'utf8');
4285
4286 binmode(STDERR, ':bytes');
4287 binmode(STDOUT, ':utf8');
4288 binmode(STDERR, ':utf8');
4289
4290 my $marc_xml = shift;
4291 my $tag = shift;
4292
4293 $marc_xml =~ s/(<leader>.........)./${1}a/;
4294
4295 eval {
4296     $marc_xml = MARC::Record->new_from_xml($marc_xml);
4297 };
4298 if ($@) {
4299     #elog("could not parse $bibid: $@\n");
4300     import MARC::File::XML (BinaryEncoding => 'utf8');
4301     return $marc_xml;
4302 }
4303
4304 my @fields = $marc_xml->field($tag);
4305 return $marc_xml->as_xml_record() unless @fields;
4306
4307 $marc_xml->delete_fields(@fields);
4308
4309 return $marc_xml->as_xml_record();
4310
4311 $function$;
4312
4313 -- convenience function for linking to the item staging table
4314
4315 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4316     DECLARE
4317         table_schema ALIAS FOR $1;
4318         table_name ALIAS FOR $2;
4319         foreign_column_name ALIAS FOR $3;
4320         main_column_name ALIAS FOR $4;
4321         btrim_desired ALIAS FOR $5;
4322         proceed BOOLEAN;
4323     BEGIN
4324         EXECUTE 'SELECT EXISTS (
4325             SELECT 1
4326             FROM information_schema.columns
4327             WHERE table_schema = $1
4328             AND table_name = $2
4329             and column_name = $3
4330         )' INTO proceed USING table_schema, table_name, foreign_column_name;
4331         IF NOT proceed THEN
4332             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4333         END IF;
4334
4335         EXECUTE 'SELECT EXISTS (
4336             SELECT 1
4337             FROM information_schema.columns
4338             WHERE table_schema = $1
4339             AND table_name = ''asset_copy_legacy''
4340             and column_name = $2
4341         )' INTO proceed USING table_schema, main_column_name;
4342         IF NOT proceed THEN
4343             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4344         END IF;
4345
4346         EXECUTE 'ALTER TABLE '
4347             || quote_ident(table_name)
4348             || ' DROP COLUMN IF EXISTS x_item';
4349         EXECUTE 'ALTER TABLE '
4350             || quote_ident(table_name)
4351             || ' ADD COLUMN x_item BIGINT';
4352
4353         IF btrim_desired THEN
4354             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4355                 || ' SET x_item = b.id FROM asset_copy_legacy b'
4356                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4357                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4358         ELSE
4359             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4360                 || ' SET x_item = b.id FROM asset_copy_legacy b'
4361                 || ' WHERE a.' || quote_ident(foreign_column_name)
4362                 || ' = b.' || quote_ident(main_column_name);
4363         END IF;
4364
4365         --EXECUTE 'SELECT migration_tools.assert(
4366         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4367         --    ''Cannot link every barcode'',
4368         --    ''Every barcode linked''
4369         --);';
4370
4371     END;
4372 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4373
4374 -- convenience function for linking to the user staging table
4375
4376 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4377     DECLARE
4378         table_schema ALIAS FOR $1;
4379         table_name ALIAS FOR $2;
4380         foreign_column_name ALIAS FOR $3;
4381         main_column_name ALIAS FOR $4;
4382         btrim_desired ALIAS FOR $5;
4383         proceed BOOLEAN;
4384     BEGIN
4385         EXECUTE 'SELECT EXISTS (
4386             SELECT 1
4387             FROM information_schema.columns
4388             WHERE table_schema = $1
4389             AND table_name = $2
4390             and column_name = $3
4391         )' INTO proceed USING table_schema, table_name, foreign_column_name;
4392         IF NOT proceed THEN
4393             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4394         END IF;
4395
4396         EXECUTE 'SELECT EXISTS (
4397             SELECT 1
4398             FROM information_schema.columns
4399             WHERE table_schema = $1
4400             AND table_name = ''actor_usr_legacy''
4401             and column_name = $2
4402         )' INTO proceed USING table_schema, main_column_name;
4403         IF NOT proceed THEN
4404             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4405         END IF;
4406
4407         EXECUTE 'ALTER TABLE '
4408             || quote_ident(table_name)
4409             || ' DROP COLUMN IF EXISTS x_user';
4410         EXECUTE 'ALTER TABLE '
4411             || quote_ident(table_name)
4412             || ' ADD COLUMN x_user INTEGER';
4413
4414         IF btrim_desired THEN
4415             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4416                 || ' SET x_user = b.id FROM actor_usr_legacy b'
4417                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4418                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4419         ELSE
4420             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4421                 || ' SET x_user = b.id FROM actor_usr_legacy b'
4422                 || ' WHERE a.' || quote_ident(foreign_column_name)
4423                 || ' = b.' || quote_ident(main_column_name);
4424         END IF;
4425
4426         --EXECUTE 'SELECT migration_tools.assert(
4427         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4428         --    ''Cannot link every barcode'',
4429         --    ''Every barcode linked''
4430         --);';
4431
4432     END;
4433 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4434
4435 -- convenience function for linking two tables
4436 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4437 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4438     DECLARE
4439         table_schema ALIAS FOR $1;
4440         table_a ALIAS FOR $2;
4441         column_a ALIAS FOR $3;
4442         table_b ALIAS FOR $4;
4443         column_b ALIAS FOR $5;
4444         column_x ALIAS FOR $6;
4445         btrim_desired ALIAS FOR $7;
4446         proceed BOOLEAN;
4447     BEGIN
4448         EXECUTE 'SELECT EXISTS (
4449             SELECT 1
4450             FROM information_schema.columns
4451             WHERE table_schema = $1
4452             AND table_name = $2
4453             and column_name = $3
4454         )' INTO proceed USING table_schema, table_a, column_a;
4455         IF NOT proceed THEN
4456             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4457         END IF;
4458
4459         EXECUTE 'SELECT EXISTS (
4460             SELECT 1
4461             FROM information_schema.columns
4462             WHERE table_schema = $1
4463             AND table_name = $2
4464             and column_name = $3
4465         )' INTO proceed USING table_schema, table_b, column_b;
4466         IF NOT proceed THEN
4467             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4468         END IF;
4469
4470         EXECUTE 'ALTER TABLE '
4471             || quote_ident(table_b)
4472             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4473         EXECUTE 'ALTER TABLE '
4474             || quote_ident(table_b)
4475             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4476
4477         IF btrim_desired THEN
4478             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4479                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4480                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4481                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4482         ELSE
4483             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4484                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4485                 || ' WHERE a.' || quote_ident(column_a)
4486                 || ' = b.' || quote_ident(column_b);
4487         END IF;
4488
4489     END;
4490 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4491
4492 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4493 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4494 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4495     DECLARE
4496         table_schema ALIAS FOR $1;
4497         table_a ALIAS FOR $2;
4498         column_a ALIAS FOR $3;
4499         table_b ALIAS FOR $4;
4500         column_b ALIAS FOR $5;
4501         column_w ALIAS FOR $6;
4502         column_x ALIAS FOR $7;
4503         btrim_desired ALIAS FOR $8;
4504         proceed BOOLEAN;
4505     BEGIN
4506         EXECUTE 'SELECT EXISTS (
4507             SELECT 1
4508             FROM information_schema.columns
4509             WHERE table_schema = $1
4510             AND table_name = $2
4511             and column_name = $3
4512         )' INTO proceed USING table_schema, table_a, column_a;
4513         IF NOT proceed THEN
4514             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4515         END IF;
4516
4517         EXECUTE 'SELECT EXISTS (
4518             SELECT 1
4519             FROM information_schema.columns
4520             WHERE table_schema = $1
4521             AND table_name = $2
4522             and column_name = $3
4523         )' INTO proceed USING table_schema, table_b, column_b;
4524         IF NOT proceed THEN
4525             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4526         END IF;
4527
4528         EXECUTE 'ALTER TABLE '
4529             || quote_ident(table_b)
4530             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4531         EXECUTE 'ALTER TABLE '
4532             || quote_ident(table_b)
4533             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4534
4535         IF btrim_desired THEN
4536             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4537                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4538                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4539                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4540         ELSE
4541             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4542                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4543                 || ' WHERE a.' || quote_ident(column_a)
4544                 || ' = b.' || quote_ident(column_b);
4545         END IF;
4546
4547     END;
4548 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4549
4550 -- 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
4551 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4552 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4553     DECLARE
4554         table_schema ALIAS FOR $1;
4555         table_a ALIAS FOR $2;
4556         column_a ALIAS FOR $3;
4557         table_b ALIAS FOR $4;
4558         column_b ALIAS FOR $5;
4559         column_w ALIAS FOR $6;
4560         column_x ALIAS FOR $7;
4561         proceed BOOLEAN;
4562     BEGIN
4563         EXECUTE 'SELECT EXISTS (
4564             SELECT 1
4565             FROM information_schema.columns
4566             WHERE table_schema = $1
4567             AND table_name = $2
4568             and column_name = $3
4569         )' INTO proceed USING table_schema, table_a, column_a;
4570         IF NOT proceed THEN
4571             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4572         END IF;
4573
4574         EXECUTE 'SELECT EXISTS (
4575             SELECT 1
4576             FROM information_schema.columns
4577             WHERE table_schema = $1
4578             AND table_name = $2
4579             and column_name = $3
4580         )' INTO proceed USING table_schema, table_b, column_b;
4581         IF NOT proceed THEN
4582             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4583         END IF;
4584
4585         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4586             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4587             || ' WHERE a.' || quote_ident(column_a)
4588             || ' = b.' || quote_ident(column_b);
4589
4590     END;
4591 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4592
4593 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4594     DECLARE
4595         table_schema ALIAS FOR $1;
4596         table_a ALIAS FOR $2;
4597         column_a ALIAS FOR $3;
4598         table_b ALIAS FOR $4;
4599         column_b ALIAS FOR $5;
4600         column_w ALIAS FOR $6;
4601         column_x ALIAS FOR $7;
4602         proceed BOOLEAN;
4603     BEGIN
4604         EXECUTE 'SELECT EXISTS (
4605             SELECT 1
4606             FROM information_schema.columns
4607             WHERE table_schema = $1
4608             AND table_name = $2
4609             and column_name = $3
4610         )' INTO proceed USING table_schema, table_a, column_a;
4611         IF NOT proceed THEN
4612             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4613         END IF;
4614
4615         EXECUTE 'SELECT EXISTS (
4616             SELECT 1
4617             FROM information_schema.columns
4618             WHERE table_schema = $1
4619             AND table_name = $2
4620             and column_name = $3
4621         )' INTO proceed USING table_schema, table_b, column_b;
4622         IF NOT proceed THEN
4623             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4624         END IF;
4625
4626         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4627             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4628             || ' WHERE a.' || quote_ident(column_a)
4629             || ' = b.' || quote_ident(column_b)
4630             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4631
4632     END;
4633 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4634
4635 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4636     DECLARE
4637         table_schema ALIAS FOR $1;
4638         table_a ALIAS FOR $2;
4639         column_a ALIAS FOR $3;
4640         table_b ALIAS FOR $4;
4641         column_b ALIAS FOR $5;
4642         column_w ALIAS FOR $6;
4643         column_x ALIAS FOR $7;
4644         proceed BOOLEAN;
4645     BEGIN
4646         EXECUTE 'SELECT EXISTS (
4647             SELECT 1
4648             FROM information_schema.columns
4649             WHERE table_schema = $1
4650             AND table_name = $2
4651             and column_name = $3
4652         )' INTO proceed USING table_schema, table_a, column_a;
4653         IF NOT proceed THEN
4654             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4655         END IF;
4656
4657         EXECUTE 'SELECT EXISTS (
4658             SELECT 1
4659             FROM information_schema.columns
4660             WHERE table_schema = $1
4661             AND table_name = $2
4662             and column_name = $3
4663         )' INTO proceed USING table_schema, table_b, column_b;
4664         IF NOT proceed THEN
4665             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4666         END IF;
4667
4668         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4669             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4670             || ' WHERE a.' || quote_ident(column_a)
4671             || ' = b.' || quote_ident(column_b)
4672             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4673
4674     END;
4675 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4676
4677 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4678     DECLARE
4679         table_schema ALIAS FOR $1;
4680         table_a ALIAS FOR $2;
4681         column_a ALIAS FOR $3;
4682         table_b ALIAS FOR $4;
4683         column_b ALIAS FOR $5;
4684         column_w ALIAS FOR $6;
4685         column_x ALIAS FOR $7;
4686         proceed BOOLEAN;
4687     BEGIN
4688         EXECUTE 'SELECT EXISTS (
4689             SELECT 1
4690             FROM information_schema.columns
4691             WHERE table_schema = $1
4692             AND table_name = $2
4693             and column_name = $3
4694         )' INTO proceed USING table_schema, table_a, column_a;
4695         IF NOT proceed THEN
4696             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4697         END IF;
4698
4699         EXECUTE 'SELECT EXISTS (
4700             SELECT 1
4701             FROM information_schema.columns
4702             WHERE table_schema = $1
4703             AND table_name = $2
4704             and column_name = $3
4705         )' INTO proceed USING table_schema, table_b, column_b;
4706         IF NOT proceed THEN
4707             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4708         END IF;
4709
4710         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4711             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4712             || ' WHERE a.' || quote_ident(column_a)
4713             || ' = b.' || quote_ident(column_b)
4714             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4715
4716     END;
4717 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4718
4719 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4720     DECLARE
4721         table_schema ALIAS FOR $1;
4722         table_a ALIAS FOR $2;
4723         column_a ALIAS FOR $3;
4724         table_b ALIAS FOR $4;
4725         column_b ALIAS FOR $5;
4726         column_w ALIAS FOR $6;
4727         column_x ALIAS FOR $7;
4728         proceed BOOLEAN;
4729     BEGIN
4730         EXECUTE 'SELECT EXISTS (
4731             SELECT 1
4732             FROM information_schema.columns
4733             WHERE table_schema = $1
4734             AND table_name = $2
4735             and column_name = $3
4736         )' INTO proceed USING table_schema, table_a, column_a;
4737         IF NOT proceed THEN
4738             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4739         END IF;
4740
4741         EXECUTE 'SELECT EXISTS (
4742             SELECT 1
4743             FROM information_schema.columns
4744             WHERE table_schema = $1
4745             AND table_name = $2
4746             and column_name = $3
4747         )' INTO proceed USING table_schema, table_b, column_b;
4748         IF NOT proceed THEN
4749             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4750         END IF;
4751
4752         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4753             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4754             || ' WHERE a.' || quote_ident(column_a)
4755             || ' = b.' || quote_ident(column_b)
4756             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4757
4758     END;
4759 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4760
4761 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4762     DECLARE
4763         table_schema ALIAS FOR $1;
4764         table_a ALIAS FOR $2;
4765         column_a ALIAS FOR $3;
4766         table_b ALIAS FOR $4;
4767         column_b ALIAS FOR $5;
4768         column_w ALIAS FOR $6;
4769         column_x ALIAS FOR $7;
4770         proceed BOOLEAN;
4771     BEGIN
4772         EXECUTE 'SELECT EXISTS (
4773             SELECT 1
4774             FROM information_schema.columns
4775             WHERE table_schema = $1
4776             AND table_name = $2
4777             and column_name = $3
4778         )' INTO proceed USING table_schema, table_a, column_a;
4779         IF NOT proceed THEN
4780             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4781         END IF;
4782
4783         EXECUTE 'SELECT EXISTS (
4784             SELECT 1
4785             FROM information_schema.columns
4786             WHERE table_schema = $1
4787             AND table_name = $2
4788             and column_name = $3
4789         )' INTO proceed USING table_schema, table_b, column_b;
4790         IF NOT proceed THEN
4791             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4792         END IF;
4793
4794         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4795             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4796             || ' WHERE a.' || quote_ident(column_a)
4797             || ' = b.' || quote_ident(column_b)
4798             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4799
4800     END;
4801 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4802
4803 -- convenience function for handling desired asset stat cats
4804
4805 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4806     DECLARE
4807         table_schema ALIAS FOR $1;
4808         table_name ALIAS FOR $2;
4809         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4810         org_shortname ALIAS FOR $4;
4811         proceed BOOLEAN;
4812         org INTEGER;
4813         org_list INTEGER[];
4814         sc TEXT;
4815         sce TEXT;
4816     BEGIN
4817
4818         SELECT 'desired_sc' || field_suffix INTO sc;
4819         SELECT 'desired_sce' || field_suffix INTO sce;
4820
4821         EXECUTE 'SELECT EXISTS (
4822             SELECT 1
4823             FROM information_schema.columns
4824             WHERE table_schema = $1
4825             AND table_name = $2
4826             and column_name = $3
4827         )' INTO proceed USING table_schema, table_name, sc;
4828         IF NOT proceed THEN
4829             RAISE EXCEPTION 'Missing column %', sc;
4830         END IF;
4831         EXECUTE 'SELECT EXISTS (
4832             SELECT 1
4833             FROM information_schema.columns
4834             WHERE table_schema = $1
4835             AND table_name = $2
4836             and column_name = $3
4837         )' INTO proceed USING table_schema, table_name, sce;
4838         IF NOT proceed THEN
4839             RAISE EXCEPTION 'Missing column %', sce;
4840         END IF;
4841
4842         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4843         IF org IS NULL THEN
4844             RAISE EXCEPTION 'Cannot find org by shortname';
4845         END IF;
4846         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4847
4848         -- caller responsible for their own truncates though we try to prevent duplicates
4849         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4850             SELECT DISTINCT
4851                  $1
4852                 ,BTRIM('||sc||')
4853             FROM
4854                 ' || quote_ident(table_name) || '
4855             WHERE
4856                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4857                 AND NOT EXISTS (
4858                     SELECT id
4859                     FROM asset.stat_cat
4860                     WHERE owner = ANY ($2)
4861                     AND name = BTRIM('||sc||')
4862                 )
4863                 AND NOT EXISTS (
4864                     SELECT id
4865                     FROM asset_stat_cat
4866                     WHERE owner = ANY ($2)
4867                     AND name = BTRIM('||sc||')
4868                 )
4869             ORDER BY 2;'
4870         USING org, org_list;
4871
4872         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4873             SELECT DISTINCT
4874                 COALESCE(
4875                     (SELECT id
4876                         FROM asset.stat_cat
4877                         WHERE owner = ANY ($2)
4878                         AND BTRIM('||sc||') = BTRIM(name))
4879                    ,(SELECT id
4880                         FROM asset_stat_cat
4881                         WHERE owner = ANY ($2)
4882                         AND BTRIM('||sc||') = BTRIM(name))
4883                 )
4884                 ,$1
4885                 ,BTRIM('||sce||')
4886             FROM
4887                 ' || quote_ident(table_name) || '
4888             WHERE
4889                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4890                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4891                 AND NOT EXISTS (
4892                     SELECT id
4893                     FROM asset.stat_cat_entry
4894                     WHERE stat_cat = (
4895                         SELECT id
4896                         FROM asset.stat_cat
4897                         WHERE owner = ANY ($2)
4898                         AND BTRIM('||sc||') = BTRIM(name)
4899                     ) AND value = BTRIM('||sce||')
4900                     AND owner = ANY ($2)
4901                 )
4902                 AND NOT EXISTS (
4903                     SELECT id
4904                     FROM asset_stat_cat_entry
4905                     WHERE stat_cat = (
4906                         SELECT id
4907                         FROM asset_stat_cat
4908                         WHERE owner = ANY ($2)
4909                         AND BTRIM('||sc||') = BTRIM(name)
4910                     ) AND value = BTRIM('||sce||')
4911                     AND owner = ANY ($2)
4912                 )
4913             ORDER BY 1,3;'
4914         USING org, org_list;
4915     END;
4916 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4917
4918 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4919     DECLARE
4920         table_schema ALIAS FOR $1;
4921         table_name ALIAS FOR $2;
4922         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4923         org_shortname ALIAS FOR $4;
4924         proceed BOOLEAN;
4925         org INTEGER;
4926         org_list INTEGER[];
4927         o INTEGER;
4928         sc TEXT;
4929         sce TEXT;
4930     BEGIN
4931         SELECT 'desired_sc' || field_suffix INTO sc;
4932         SELECT 'desired_sce' || field_suffix INTO sce;
4933         EXECUTE 'SELECT EXISTS (
4934             SELECT 1
4935             FROM information_schema.columns
4936             WHERE table_schema = $1
4937             AND table_name = $2
4938             and column_name = $3
4939         )' INTO proceed USING table_schema, table_name, sc;
4940         IF NOT proceed THEN
4941             RAISE EXCEPTION 'Missing column %', sc;
4942         END IF;
4943         EXECUTE 'SELECT EXISTS (
4944             SELECT 1
4945             FROM information_schema.columns
4946             WHERE table_schema = $1
4947             AND table_name = $2
4948             and column_name = $3
4949         )' INTO proceed USING table_schema, table_name, sce;
4950         IF NOT proceed THEN
4951             RAISE EXCEPTION 'Missing column %', sce;
4952         END IF;
4953
4954         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4955         IF org IS NULL THEN
4956             RAISE EXCEPTION 'Cannot find org by shortname';
4957         END IF;
4958
4959         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4960
4961         EXECUTE 'ALTER TABLE '
4962             || quote_ident(table_name)
4963             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4964         EXECUTE 'ALTER TABLE '
4965             || quote_ident(table_name)
4966             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4967         EXECUTE 'ALTER TABLE '
4968             || quote_ident(table_name)
4969             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4970         EXECUTE 'ALTER TABLE '
4971             || quote_ident(table_name)
4972             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4973
4974
4975         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4976             SET
4977                 x_sc' || field_suffix || ' = id
4978             FROM
4979                 (SELECT id, name, owner FROM asset_stat_cat
4980                     UNION SELECT id, name, owner FROM asset.stat_cat) u
4981             WHERE
4982                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4983                 AND u.owner = ANY ($1);'
4984         USING org_list;
4985
4986         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4987             SET
4988                 x_sce' || field_suffix || ' = id
4989             FROM
4990                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4991                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4992             WHERE
4993                     u.stat_cat = x_sc' || field_suffix || '
4994                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4995                 AND u.owner = ANY ($1);'
4996         USING org_list;
4997
4998         EXECUTE 'SELECT migration_tools.assert(
4999             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5000             ''Cannot find a desired stat cat'',
5001             ''Found all desired stat cats''
5002         );';
5003
5004         EXECUTE 'SELECT migration_tools.assert(
5005             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5006             ''Cannot find a desired stat cat entry'',
5007             ''Found all desired stat cat entries''
5008         );';
5009
5010     END;
5011 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5012
5013 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5014 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5015  LANGUAGE plpgsql
5016 AS $function$
5017 DECLARE
5018     c_name     TEXT;
5019 BEGIN
5020
5021     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5022             table_name = t_name
5023             AND table_schema = s_name
5024             AND (data_type='text' OR data_type='character varying')
5025             AND column_name like 'l_%'
5026     LOOP
5027        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5028     END LOOP;
5029
5030     RETURN TRUE;
5031 END
5032 $function$;
5033
5034 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5035 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5036  LANGUAGE plpgsql
5037 AS $function$
5038 DECLARE
5039     c_name     TEXT;
5040 BEGIN
5041
5042     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5043             table_name = t_name
5044             AND table_schema = s_name
5045             AND (data_type='text' OR data_type='character varying')
5046     LOOP
5047        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5048     END LOOP;
5049
5050     RETURN TRUE;
5051 END
5052 $function$;
5053
5054 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5055 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5056  LANGUAGE plpgsql
5057 AS $function$
5058 DECLARE
5059     c_name     TEXT;
5060 BEGIN
5061
5062     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5063             table_name = t_name
5064             AND table_schema = s_name
5065             AND (data_type='text' OR data_type='character varying')
5066             AND column_name like 'l_%'
5067     LOOP
5068        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5069     END LOOP;
5070
5071     RETURN TRUE;
5072 END
5073 $function$;
5074
5075 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5076 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5077  LANGUAGE plpgsql
5078 AS $function$
5079 DECLARE
5080     c_name     TEXT;
5081 BEGIN
5082
5083     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5084             table_name = t_name
5085             AND table_schema = s_name
5086             AND (data_type='text' OR data_type='character varying')
5087     LOOP
5088        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5089     END LOOP;
5090
5091     RETURN TRUE;
5092 END
5093 $function$;