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