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