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.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2353 DECLARE
2354     id BIGINT;
2355     loopq TEXT;
2356     cols TEXT[];
2357     splitst TEXT;
2358 BEGIN
2359     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2360     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
2361     FOR id IN EXECUTE loopq USING delimiter LOOP
2362        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2363        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2364                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2365        EXECUTE splitst USING id, delimiter;
2366     END LOOP;
2367 END;
2368 $FUNC$ LANGUAGE PLPGSQL;
2369
2370 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2371
2372 use strict;
2373 use warnings;
2374
2375 use MARC::Record;
2376 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2377 use MARC::Charset;
2378
2379 MARC::Charset->assume_unicode(1);
2380
2381 my $target_xml = shift;
2382 my $source_xml = shift;
2383 my $tags = shift;
2384
2385 my $target;
2386 my $source;
2387
2388 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2389 if ($@) {
2390     return;
2391 }
2392 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2393 if ($@) {
2394     return;
2395 }
2396
2397 my $source_id = $source->subfield('901', 'c');
2398 $source_id = $source->subfield('903', 'a') unless $source_id;
2399 my $target_id = $target->subfield('901', 'c');
2400 $target_id = $target->subfield('903', 'a') unless $target_id;
2401
2402 my %existing_fields;
2403 foreach my $tag (@$tags) {
2404     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2405     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2406     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2407     if (@to_add) {
2408         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2409     }
2410 }
2411
2412 my $xml = $target->as_xml_record;
2413 $xml =~ s/^<\?.+?\?>$//mo;
2414 $xml =~ s/\n//sgo;
2415 $xml =~ s/>\s+</></sgo;
2416
2417 return $xml;
2418
2419 $func$ LANGUAGE PLPERLU;
2420 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.';
2421
2422 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2423
2424 use strict;
2425 use warnings;
2426
2427 use MARC::Record;
2428 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2429 use Text::CSV;
2430
2431 my $in_tags = shift;
2432 my $in_values = shift;
2433
2434 # hack-and-slash parsing of array-passed-as-string;
2435 # this can go away once everybody is running Postgres 9.1+
2436 my $csv = Text::CSV->new({binary => 1});
2437 $in_tags =~ s/^{//;
2438 $in_tags =~ s/}$//;
2439 my $status = $csv->parse($in_tags);
2440 my $tags = [ $csv->fields() ];
2441 $in_values =~ s/^{//;
2442 $in_values =~ s/}$//;
2443 $status = $csv->parse($in_values);
2444 my $values = [ $csv->fields() ];
2445
2446 my $marc = MARC::Record->new();
2447
2448 $marc->leader('00000nam a22000007  4500');
2449 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2450
2451 foreach my $i (0..$#$tags) {
2452     my ($tag, $sf);
2453     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2454         $tag = $1;
2455         $sf = $2;
2456         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2457     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2458         $tag = $1;
2459         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2460     }
2461 }
2462
2463 my $xml = $marc->as_xml_record;
2464 $xml =~ s/^<\?.+?\?>$//mo;
2465 $xml =~ s/\n//sgo;
2466 $xml =~ s/>\s+</></sgo;
2467
2468 return $xml;
2469
2470 $func$ LANGUAGE PLPERLU;
2471 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2472 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2473 The second argument is an array of text containing the values to plug into each field.
2474 If the value for a given field is NULL or the empty string, it is not inserted.
2475 $$;
2476
2477 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2478
2479 my ($marcxml, $tag, $pos, $value) = @_;
2480
2481 use MARC::Record;
2482 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2483 use MARC::Charset;
2484 use strict;
2485
2486 MARC::Charset->assume_unicode(1);
2487
2488 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2489 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2490 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2491 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2492
2493 my $xml = $marcxml;
2494 eval {
2495     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2496
2497     foreach my $field ($marc->field($tag)) {
2498         $field->update("ind$pos" => $value);
2499     }
2500     $xml = $marc->as_xml_record;
2501     $xml =~ s/^<\?.+?\?>$//mo;
2502     $xml =~ s/\n//sgo;
2503     $xml =~ s/>\s+</></sgo;
2504 };
2505 return $xml;
2506
2507 $func$ LANGUAGE PLPERLU;
2508
2509 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2510 The first argument is a MARCXML string.
2511 The second argument is a MARC tag.
2512 The third argument is the indicator position, either 1 or 2.
2513 The fourth argument is the character to set the indicator value to.
2514 All occurences of the specified field will be changed.
2515 The function returns the revised MARCXML string.$$;
2516
2517 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2518     username TEXT,
2519     password TEXT,
2520     org TEXT,
2521     perm_group TEXT,
2522     first_name TEXT DEFAULT '',
2523     last_name TEXT DEFAULT ''
2524 ) RETURNS VOID AS $func$
2525 BEGIN
2526     RAISE NOTICE '%', org ;
2527     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2528     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2529     FROM   actor.org_unit aou, permission.grp_tree pgt
2530     WHERE  aou.shortname = org
2531     AND    pgt.name = perm_group;
2532 END
2533 $func$
2534 LANGUAGE PLPGSQL;
2535
2536 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2537 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2538     DECLARE
2539         target_event_def ALIAS FOR $1;
2540         orgs ALIAS FOR $2;
2541     BEGIN
2542         DROP TABLE IF EXISTS new_atevdefs;
2543         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2544         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2545             INSERT INTO action_trigger.event_definition (
2546                 active
2547                 ,owner
2548                 ,name
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             ) SELECT
2564                 'f'
2565                 ,orgs[i]
2566                 ,name || ' (clone of '||target_event_def||')'
2567                 ,hook
2568                 ,validator
2569                 ,reactor
2570                 ,cleanup_success
2571                 ,cleanup_failure
2572                 ,delay
2573                 ,max_delay
2574                 ,usr_field
2575                 ,opt_in_setting
2576                 ,delay_field
2577                 ,group_field
2578                 ,template
2579                 ,granularity
2580                 ,repeat_delay
2581             FROM
2582                 action_trigger.event_definition
2583             WHERE
2584                 id = target_event_def
2585             ;
2586             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2587             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2588             INSERT INTO action_trigger.environment (
2589                 event_def
2590                 ,path
2591                 ,collector
2592                 ,label
2593             ) SELECT
2594                 currval('action_trigger.event_definition_id_seq')
2595                 ,path
2596                 ,collector
2597                 ,label
2598             FROM
2599                 action_trigger.environment
2600             WHERE
2601                 event_def = target_event_def
2602             ;
2603             INSERT INTO action_trigger.event_params (
2604                 event_def
2605                 ,param
2606                 ,value
2607             ) SELECT
2608                 currval('action_trigger.event_definition_id_seq')
2609                 ,param
2610                 ,value
2611             FROM
2612                 action_trigger.event_params
2613             WHERE
2614                 event_def = target_event_def
2615             ;
2616         END LOOP;
2617         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);
2618     END;
2619 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2620
2621 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2622 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2623     DECLARE
2624         target_event_def ALIAS FOR $1;
2625         orgs ALIAS FOR $2;
2626         new_interval ALIAS FOR $3;
2627     BEGIN
2628         DROP TABLE IF EXISTS new_atevdefs;
2629         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2630         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2631             INSERT INTO action_trigger.event_definition (
2632                 active
2633                 ,owner
2634                 ,name
2635                 ,hook
2636                 ,validator
2637                 ,reactor
2638                 ,cleanup_success
2639                 ,cleanup_failure
2640                 ,delay
2641                 ,max_delay
2642                 ,usr_field
2643                 ,opt_in_setting
2644                 ,delay_field
2645                 ,group_field
2646                 ,template
2647                 ,granularity
2648                 ,repeat_delay
2649             ) SELECT
2650                 'f'
2651                 ,orgs[i]
2652                 ,name || ' (clone of '||target_event_def||')'
2653                 ,hook
2654                 ,validator
2655                 ,reactor
2656                 ,cleanup_success
2657                 ,cleanup_failure
2658                 ,new_interval
2659                 ,max_delay
2660                 ,usr_field
2661                 ,opt_in_setting
2662                 ,delay_field
2663                 ,group_field
2664                 ,template
2665                 ,granularity
2666                 ,repeat_delay
2667             FROM
2668                 action_trigger.event_definition
2669             WHERE
2670                 id = target_event_def
2671             ;
2672             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2673             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2674             INSERT INTO action_trigger.environment (
2675                 event_def
2676                 ,path
2677                 ,collector
2678                 ,label
2679             ) SELECT
2680                 currval('action_trigger.event_definition_id_seq')
2681                 ,path
2682                 ,collector
2683                 ,label
2684             FROM
2685                 action_trigger.environment
2686             WHERE
2687                 event_def = target_event_def
2688             ;
2689             INSERT INTO action_trigger.event_params (
2690                 event_def
2691                 ,param
2692                 ,value
2693             ) SELECT
2694                 currval('action_trigger.event_definition_id_seq')
2695                 ,param
2696                 ,value
2697             FROM
2698                 action_trigger.event_params
2699             WHERE
2700                 event_def = target_event_def
2701             ;
2702         END LOOP;
2703         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);
2704     END;
2705 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2706
2707 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
2708 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
2709     DECLARE
2710         org ALIAS FOR $1;
2711         target_event_defs ALIAS FOR $2;
2712     BEGIN
2713         DROP TABLE IF EXISTS new_atevdefs;
2714         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2715         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
2716             INSERT INTO action_trigger.event_definition (
2717                 active
2718                 ,owner
2719                 ,name
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             ) SELECT
2735                 'f'
2736                 ,org
2737                 ,name || ' (clone of '||target_event_defs[i]||')'
2738                 ,hook
2739                 ,validator
2740                 ,reactor
2741                 ,cleanup_success
2742                 ,cleanup_failure
2743                 ,delay
2744                 ,max_delay
2745                 ,usr_field
2746                 ,opt_in_setting
2747                 ,delay_field
2748                 ,group_field
2749                 ,template
2750                 ,granularity
2751                 ,repeat_delay
2752             FROM
2753                 action_trigger.event_definition
2754             WHERE
2755                 id = target_event_defs[i]
2756             ;
2757             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2758             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2759             INSERT INTO action_trigger.environment (
2760                 event_def
2761                 ,path
2762                 ,collector
2763                 ,label
2764             ) SELECT
2765                 currval('action_trigger.event_definition_id_seq')
2766                 ,path
2767                 ,collector
2768                 ,label
2769             FROM
2770                 action_trigger.environment
2771             WHERE
2772                 event_def = target_event_defs[i]
2773             ;
2774             INSERT INTO action_trigger.event_params (
2775                 event_def
2776                 ,param
2777                 ,value
2778             ) SELECT
2779                 currval('action_trigger.event_definition_id_seq')
2780                 ,param
2781                 ,value
2782             FROM
2783                 action_trigger.event_params
2784             WHERE
2785                 event_def = target_event_defs[i]
2786             ;
2787         END LOOP;
2788         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2789     END;
2790 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2791
2792 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
2793     UPDATE
2794         action_trigger.event
2795     SET
2796          start_time = NULL
2797         ,update_time = NULL
2798         ,complete_time = NULL
2799         ,update_process = NULL
2800         ,state = 'pending'
2801         ,template_output = NULL
2802         ,error_output = NULL
2803         ,async_output = NULL
2804     WHERE
2805         id = $1;
2806 $$ LANGUAGE SQL;
2807
2808 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2809     my ($marcxml) = @_;
2810
2811     use MARC::Record;
2812     use MARC::File::XML;
2813     use MARC::Field;
2814
2815     my $field;
2816     eval {
2817         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2818         $field = $marc->leader();
2819     };
2820     return $field;
2821 $$ LANGUAGE PLPERLU STABLE;
2822
2823 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2824     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2825
2826     use MARC::Record;
2827     use MARC::File::XML;
2828     use MARC::Field;
2829
2830     my $field;
2831     eval {
2832         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2833         $field = $marc->field($tag);
2834     };
2835     return $field->as_string($subfield,$delimiter);
2836 $$ LANGUAGE PLPERLU STABLE;
2837
2838 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2839     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2840
2841     use MARC::Record;
2842     use MARC::File::XML;
2843     use MARC::Field;
2844
2845     my @fields;
2846     eval {
2847         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2848         @fields = $marc->field($tag);
2849     };
2850     my @texts;
2851     foreach my $field (@fields) {
2852         push @texts, $field->as_string($subfield,$delimiter);
2853     }
2854     return \@texts;
2855 $$ LANGUAGE PLPERLU STABLE;
2856
2857 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2858     SELECT action.find_hold_matrix_matchpoint(
2859         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2860         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2861         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2862         (SELECT usr FROM action.hold_request WHERE id = $1),
2863         (SELECT requestor FROM action.hold_request WHERE id = $1)
2864     );
2865 $$ LANGUAGE SQL;
2866
2867 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2868     SELECT action.hold_request_permit_test(
2869         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2870         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2871         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2872         (SELECT usr FROM action.hold_request WHERE id = $1),
2873         (SELECT requestor FROM action.hold_request WHERE id = $1)
2874     );
2875 $$ LANGUAGE SQL;
2876
2877 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2878     SELECT action.find_circ_matrix_matchpoint(
2879         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2880         (SELECT target_copy FROM action.circulation WHERE id = $1),
2881         (SELECT usr FROM action.circulation WHERE id = $1),
2882         (SELECT COALESCE(
2883                 NULLIF(phone_renewal,false),
2884                 NULLIF(desk_renewal,false),
2885                 NULLIF(opac_renewal,false),
2886                 false
2887             ) FROM action.circulation WHERE id = $1
2888         )
2889     );
2890 $$ LANGUAGE SQL;
2891
2892 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2893     DECLARE
2894         test ALIAS FOR $1;
2895     BEGIN
2896         IF NOT test THEN
2897             RAISE EXCEPTION 'assertion';
2898         END IF;
2899     END;
2900 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2901
2902 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2903     DECLARE
2904         test ALIAS FOR $1;
2905         msg ALIAS FOR $2;
2906     BEGIN
2907         IF NOT test THEN
2908             RAISE EXCEPTION '%', msg;
2909         END IF;
2910     END;
2911 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2912
2913 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2914     DECLARE
2915         test ALIAS FOR $1;
2916         fail_msg ALIAS FOR $2;
2917         success_msg ALIAS FOR $3;
2918     BEGIN
2919         IF NOT test THEN
2920             RAISE EXCEPTION '%', fail_msg;
2921         END IF;
2922         RETURN success_msg;
2923     END;
2924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2925
2926 -- push bib sequence and return starting value for reserved range
2927 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2928     DECLARE
2929         bib_count ALIAS FOR $1;
2930         output BIGINT;
2931     BEGIN
2932         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2933         FOR output IN
2934             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2935         LOOP
2936             RETURN output;
2937         END LOOP;
2938     END;
2939 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2940
2941 -- set a new salted password
2942
2943 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2944     DECLARE
2945         usr_id              ALIAS FOR $1;
2946         plain_passwd        ALIAS FOR $2;
2947         plain_salt          TEXT;
2948         md5_passwd          TEXT;
2949     BEGIN
2950
2951         SELECT actor.create_salt('main') INTO plain_salt;
2952
2953         SELECT MD5(plain_passwd) INTO md5_passwd;
2954
2955         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2956
2957         RETURN TRUE;
2958
2959     END;
2960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2961
2962
2963 -- convenience functions for handling copy_location maps
2964
2965 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2966     DECLARE
2967         table_schema ALIAS FOR $1;
2968         table_name ALIAS FOR $2;
2969         org_shortname ALIAS FOR $3;
2970         org_range ALIAS FOR $4;
2971         proceed BOOLEAN;
2972         org INTEGER;
2973         org_list INTEGER[];
2974         o INTEGER;
2975     BEGIN
2976         EXECUTE 'SELECT EXISTS (
2977             SELECT 1
2978             FROM information_schema.columns
2979             WHERE table_schema = $1
2980             AND table_name = $2
2981             and column_name = ''desired_shelf''
2982         )' INTO proceed USING table_schema, table_name;
2983         IF NOT proceed THEN
2984             RAISE EXCEPTION 'Missing column desired_shelf';
2985         END IF;
2986
2987         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2988         IF org IS NULL THEN
2989             RAISE EXCEPTION 'Cannot find org by shortname';
2990         END IF;
2991
2992         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2993
2994         EXECUTE 'ALTER TABLE '
2995             || quote_ident(table_name)
2996             || ' DROP COLUMN IF EXISTS x_shelf';
2997         EXECUTE 'ALTER TABLE '
2998             || quote_ident(table_name)
2999             || ' ADD COLUMN x_shelf INTEGER';
3000
3001         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3002             || ' SET x_shelf = id FROM asset_copy_location b'
3003             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3004             || ' AND b.owning_lib = $1'
3005             || ' AND NOT b.deleted'
3006         USING org;
3007
3008         FOREACH o IN ARRAY org_list LOOP
3009             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3010                 || ' SET x_shelf = id FROM asset.copy_location b'
3011                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3012                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3013                 || ' AND NOT b.deleted'
3014             USING o;
3015         END LOOP;
3016
3017         EXECUTE 'SELECT migration_tools.assert(
3018             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3019             ''Cannot find a desired location'',
3020             ''Found all desired locations''
3021         );';
3022
3023     END;
3024 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3025
3026 -- convenience functions for handling circmod maps
3027
3028 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3029     DECLARE
3030         table_schema ALIAS FOR $1;
3031         table_name ALIAS FOR $2;
3032         proceed BOOLEAN;
3033     BEGIN
3034         EXECUTE 'SELECT EXISTS (
3035             SELECT 1
3036             FROM information_schema.columns
3037             WHERE table_schema = $1
3038             AND table_name = $2
3039             and column_name = ''desired_circmod''
3040         )' INTO proceed USING table_schema, table_name;
3041         IF NOT proceed THEN
3042             RAISE EXCEPTION 'Missing column desired_circmod';
3043         END IF;
3044
3045         EXECUTE 'ALTER TABLE '
3046             || quote_ident(table_name)
3047             || ' DROP COLUMN IF EXISTS x_circmod';
3048         EXECUTE 'ALTER TABLE '
3049             || quote_ident(table_name)
3050             || ' ADD COLUMN x_circmod TEXT';
3051
3052         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3053             || ' SET x_circmod = code FROM config.circ_modifier b'
3054             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3055
3056         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3057             || ' SET x_circmod = code FROM config.circ_modifier b'
3058             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3059             || ' AND x_circmod IS NULL';
3060
3061         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3062             || ' SET x_circmod = code FROM config.circ_modifier b'
3063             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3064             || ' AND x_circmod IS NULL';
3065
3066         EXECUTE 'SELECT migration_tools.assert(
3067             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3068             ''Cannot find a desired circulation modifier'',
3069             ''Found all desired circulation modifiers''
3070         );';
3071
3072     END;
3073 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3074
3075 -- convenience functions for handling item status maps
3076
3077 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3078     DECLARE
3079         table_schema ALIAS FOR $1;
3080         table_name ALIAS FOR $2;
3081         proceed BOOLEAN;
3082     BEGIN
3083         EXECUTE 'SELECT EXISTS (
3084             SELECT 1
3085             FROM information_schema.columns
3086             WHERE table_schema = $1
3087             AND table_name = $2
3088             and column_name = ''desired_status''
3089         )' INTO proceed USING table_schema, table_name;
3090         IF NOT proceed THEN
3091             RAISE EXCEPTION 'Missing column desired_status';
3092         END IF;
3093
3094         EXECUTE 'ALTER TABLE '
3095             || quote_ident(table_name)
3096             || ' DROP COLUMN IF EXISTS x_status';
3097         EXECUTE 'ALTER TABLE '
3098             || quote_ident(table_name)
3099             || ' ADD COLUMN x_status INTEGER';
3100
3101         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3102             || ' SET x_status = id FROM config.copy_status b'
3103             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3104
3105         EXECUTE 'SELECT migration_tools.assert(
3106             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3107             ''Cannot find a desired copy status'',
3108             ''Found all desired copy statuses''
3109         );';
3110
3111     END;
3112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3113
3114 -- convenience functions for handling org maps
3115
3116 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3117     DECLARE
3118         table_schema ALIAS FOR $1;
3119         table_name ALIAS FOR $2;
3120         proceed BOOLEAN;
3121     BEGIN
3122         EXECUTE 'SELECT EXISTS (
3123             SELECT 1
3124             FROM information_schema.columns
3125             WHERE table_schema = $1
3126             AND table_name = $2
3127             and column_name = ''desired_org''
3128         )' INTO proceed USING table_schema, table_name;
3129         IF NOT proceed THEN
3130             RAISE EXCEPTION 'Missing column desired_org';
3131         END IF;
3132
3133         EXECUTE 'ALTER TABLE '
3134             || quote_ident(table_name)
3135             || ' DROP COLUMN IF EXISTS x_org';
3136         EXECUTE 'ALTER TABLE '
3137             || quote_ident(table_name)
3138             || ' ADD COLUMN x_org INTEGER';
3139
3140         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3141             || ' SET x_org = id FROM actor.org_unit b'
3142             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3143
3144         EXECUTE 'SELECT migration_tools.assert(
3145             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3146             ''Cannot find a desired org unit'',
3147             ''Found all desired org units''
3148         );';
3149
3150     END;
3151 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3152
3153 -- convenience function for handling desired_not_migrate
3154
3155 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3156     DECLARE
3157         table_schema ALIAS FOR $1;
3158         table_name ALIAS FOR $2;
3159         proceed BOOLEAN;
3160     BEGIN
3161         EXECUTE 'SELECT EXISTS (
3162             SELECT 1
3163             FROM information_schema.columns
3164             WHERE table_schema = $1
3165             AND table_name = $2
3166             and column_name = ''desired_not_migrate''
3167         )' INTO proceed USING table_schema, table_name;
3168         IF NOT proceed THEN
3169             RAISE EXCEPTION 'Missing column desired_not_migrate';
3170         END IF;
3171
3172         EXECUTE 'ALTER TABLE '
3173             || quote_ident(table_name)
3174             || ' DROP COLUMN IF EXISTS x_migrate';
3175         EXECUTE 'ALTER TABLE '
3176             || quote_ident(table_name)
3177             || ' ADD COLUMN x_migrate BOOLEAN';
3178
3179         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3180             || ' SET x_migrate = CASE'
3181             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3182             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3183             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3184             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3185             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3186             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3187             || ' END';
3188
3189         EXECUTE 'SELECT migration_tools.assert(
3190             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3191             ''Not all desired_not_migrate values understood'',
3192             ''All desired_not_migrate values understood''
3193         );';
3194
3195     END;
3196 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3197
3198 -- convenience function for handling desired_not_migrate
3199
3200 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3201     DECLARE
3202         table_schema ALIAS FOR $1;
3203         table_name ALIAS FOR $2;
3204         proceed BOOLEAN;
3205     BEGIN
3206         EXECUTE 'SELECT EXISTS (
3207             SELECT 1
3208             FROM information_schema.columns
3209             WHERE table_schema = $1
3210             AND table_name = $2
3211             and column_name = ''desired_barred_or_blocked''
3212         )' INTO proceed USING table_schema, table_name;
3213         IF NOT proceed THEN
3214             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3215         END IF;
3216
3217         EXECUTE 'ALTER TABLE '
3218             || quote_ident(table_name)
3219             || ' DROP COLUMN IF EXISTS x_barred';
3220         EXECUTE 'ALTER TABLE '
3221             || quote_ident(table_name)
3222             || ' ADD COLUMN x_barred BOOLEAN';
3223
3224         EXECUTE 'ALTER TABLE '
3225             || quote_ident(table_name)
3226             || ' DROP COLUMN IF EXISTS x_blocked';
3227         EXECUTE 'ALTER TABLE '
3228             || quote_ident(table_name)
3229             || ' ADD COLUMN x_blocked BOOLEAN';
3230
3231         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3232             || ' SET x_barred = CASE'
3233             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3234             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3235             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3236             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3237             || ' END';
3238
3239         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3240             || ' SET x_blocked = CASE'
3241             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3242             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3243             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3244             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3245             || ' END';
3246
3247         EXECUTE 'SELECT migration_tools.assert(
3248             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3249             ''Not all desired_barred_or_blocked values understood'',
3250             ''All desired_barred_or_blocked values understood''
3251         );';
3252
3253     END;
3254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3255
3256 -- convenience function for handling desired_profile
3257
3258 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3259     DECLARE
3260         table_schema ALIAS FOR $1;
3261         table_name ALIAS FOR $2;
3262         proceed BOOLEAN;
3263     BEGIN
3264         EXECUTE 'SELECT EXISTS (
3265             SELECT 1
3266             FROM information_schema.columns
3267             WHERE table_schema = $1
3268             AND table_name = $2
3269             and column_name = ''desired_profile''
3270         )' INTO proceed USING table_schema, table_name;
3271         IF NOT proceed THEN
3272             RAISE EXCEPTION 'Missing column desired_profile';
3273         END IF;
3274
3275         EXECUTE 'ALTER TABLE '
3276             || quote_ident(table_name)
3277             || ' DROP COLUMN IF EXISTS x_profile';
3278         EXECUTE 'ALTER TABLE '
3279             || quote_ident(table_name)
3280             || ' ADD COLUMN x_profile INTEGER';
3281
3282         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3283             || ' SET x_profile = b.id FROM permission.grp_tree b'
3284             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3285
3286         EXECUTE 'SELECT migration_tools.assert(
3287             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3288             ''Cannot find a desired profile'',
3289             ''Found all desired profiles''
3290         );';
3291
3292     END;
3293 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3294
3295 -- convenience function for handling desired actor stat cats
3296
3297 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3298     DECLARE
3299         table_schema ALIAS FOR $1;
3300         table_name ALIAS FOR $2;
3301         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3302         org_shortname ALIAS FOR $4;
3303         proceed BOOLEAN;
3304         org INTEGER;
3305         org_list INTEGER[];
3306         sc TEXT;
3307         sce TEXT;
3308     BEGIN
3309
3310         SELECT 'desired_sc' || field_suffix INTO sc;
3311         SELECT 'desired_sce' || field_suffix INTO sce;
3312
3313         EXECUTE 'SELECT EXISTS (
3314             SELECT 1
3315             FROM information_schema.columns
3316             WHERE table_schema = $1
3317             AND table_name = $2
3318             and column_name = $3
3319         )' INTO proceed USING table_schema, table_name, sc;
3320         IF NOT proceed THEN
3321             RAISE EXCEPTION 'Missing column %', sc;
3322         END IF;
3323         EXECUTE 'SELECT EXISTS (
3324             SELECT 1
3325             FROM information_schema.columns
3326             WHERE table_schema = $1
3327             AND table_name = $2
3328             and column_name = $3
3329         )' INTO proceed USING table_schema, table_name, sce;
3330         IF NOT proceed THEN
3331             RAISE EXCEPTION 'Missing column %', sce;
3332         END IF;
3333
3334         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3335         IF org IS NULL THEN
3336             RAISE EXCEPTION 'Cannot find org by shortname';
3337         END IF;
3338         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3339
3340         -- caller responsible for their own truncates though we try to prevent duplicates
3341         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3342             SELECT DISTINCT
3343                  $1
3344                 ,BTRIM('||sc||')
3345             FROM
3346                 ' || quote_ident(table_name) || '
3347             WHERE
3348                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3349                 AND NOT EXISTS (
3350                     SELECT id
3351                     FROM actor.stat_cat
3352                     WHERE owner = ANY ($2)
3353                     AND name = BTRIM('||sc||')
3354                 )
3355                 AND NOT EXISTS (
3356                     SELECT id
3357                     FROM actor_stat_cat
3358                     WHERE owner = ANY ($2)
3359                     AND name = BTRIM('||sc||')
3360                 )
3361             ORDER BY 2;'
3362         USING org, org_list;
3363
3364         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3365             SELECT DISTINCT
3366                 COALESCE(
3367                     (SELECT id
3368                         FROM actor.stat_cat
3369                         WHERE owner = ANY ($2)
3370                         AND BTRIM('||sc||') = BTRIM(name))
3371                    ,(SELECT id
3372                         FROM actor_stat_cat
3373                         WHERE owner = ANY ($2)
3374                         AND BTRIM('||sc||') = BTRIM(name))
3375                 )
3376                 ,$1
3377                 ,BTRIM('||sce||')
3378             FROM
3379                 ' || quote_ident(table_name) || '
3380             WHERE
3381                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3382                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3383                 AND NOT EXISTS (
3384                     SELECT id
3385                     FROM actor.stat_cat_entry
3386                     WHERE stat_cat = (
3387                         SELECT id
3388                         FROM actor.stat_cat
3389                         WHERE owner = ANY ($2)
3390                         AND BTRIM('||sc||') = BTRIM(name)
3391                     ) AND value = BTRIM('||sce||')
3392                     AND owner = ANY ($2)
3393                 )
3394                 AND NOT EXISTS (
3395                     SELECT id
3396                     FROM actor_stat_cat_entry
3397                     WHERE stat_cat = (
3398                         SELECT id
3399                         FROM actor_stat_cat
3400                         WHERE owner = ANY ($2)
3401                         AND BTRIM('||sc||') = BTRIM(name)
3402                     ) AND value = BTRIM('||sce||')
3403                     AND owner = ANY ($2)
3404                 )
3405             ORDER BY 1,3;'
3406         USING org, org_list;
3407     END;
3408 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3409
3410 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3411     DECLARE
3412         table_schema ALIAS FOR $1;
3413         table_name ALIAS FOR $2;
3414         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3415         org_shortname ALIAS FOR $4;
3416         proceed BOOLEAN;
3417         org INTEGER;
3418         org_list INTEGER[];
3419         o INTEGER;
3420         sc TEXT;
3421         sce TEXT;
3422     BEGIN
3423         SELECT 'desired_sc' || field_suffix INTO sc;
3424         SELECT 'desired_sce' || field_suffix INTO sce;
3425         EXECUTE 'SELECT EXISTS (
3426             SELECT 1
3427             FROM information_schema.columns
3428             WHERE table_schema = $1
3429             AND table_name = $2
3430             and column_name = $3
3431         )' INTO proceed USING table_schema, table_name, sc;
3432         IF NOT proceed THEN
3433             RAISE EXCEPTION 'Missing column %', sc;
3434         END IF;
3435         EXECUTE 'SELECT EXISTS (
3436             SELECT 1
3437             FROM information_schema.columns
3438             WHERE table_schema = $1
3439             AND table_name = $2
3440             and column_name = $3
3441         )' INTO proceed USING table_schema, table_name, sce;
3442         IF NOT proceed THEN
3443             RAISE EXCEPTION 'Missing column %', sce;
3444         END IF;
3445
3446         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3447         IF org IS NULL THEN
3448             RAISE EXCEPTION 'Cannot find org by shortname';
3449         END IF;
3450
3451         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3452
3453         EXECUTE 'ALTER TABLE '
3454             || quote_ident(table_name)
3455             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3456         EXECUTE 'ALTER TABLE '
3457             || quote_ident(table_name)
3458             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3459         EXECUTE 'ALTER TABLE '
3460             || quote_ident(table_name)
3461             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3462         EXECUTE 'ALTER TABLE '
3463             || quote_ident(table_name)
3464             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3465
3466
3467         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3468             SET
3469                 x_sc' || field_suffix || ' = id
3470             FROM
3471                 (SELECT id, name, owner FROM actor_stat_cat
3472                     UNION SELECT id, name, owner FROM actor.stat_cat) u
3473             WHERE
3474                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3475                 AND u.owner = ANY ($1);'
3476         USING org_list;
3477
3478         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3479             SET
3480                 x_sce' || field_suffix || ' = id
3481             FROM
3482                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3483                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3484             WHERE
3485                     u.stat_cat = x_sc' || field_suffix || '
3486                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3487                 AND u.owner = ANY ($1);'
3488         USING org_list;
3489
3490         EXECUTE 'SELECT migration_tools.assert(
3491             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3492             ''Cannot find a desired stat cat'',
3493             ''Found all desired stat cats''
3494         );';
3495
3496         EXECUTE 'SELECT migration_tools.assert(
3497             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3498             ''Cannot find a desired stat cat entry'',
3499             ''Found all desired stat cat entries''
3500         );';
3501
3502     END;
3503 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3504
3505 -- convenience functions for adding shelving locations
3506 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3507 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3508 DECLARE
3509     return_id   INT;
3510     d           INT;
3511     cur_id      INT;
3512 BEGIN
3513     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3514     WHILE d >= 0
3515     LOOP
3516         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3517         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3518         IF return_id IS NOT NULL THEN
3519                 RETURN return_id;
3520         END IF;
3521         d := d - 1;
3522     END LOOP;
3523
3524     RETURN NULL;
3525 END
3526 $$ LANGUAGE plpgsql;
3527
3528 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3529
3530 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3531 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3532 DECLARE
3533     return_id   INT;
3534     d           INT;
3535     cur_id      INT;
3536 BEGIN
3537     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3538     WHILE d >= 0
3539     LOOP
3540         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3541
3542         SELECT INTO return_id id FROM
3543             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3544             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3545         IF return_id IS NOT NULL THEN
3546                 RETURN return_id;
3547         END IF;
3548         d := d - 1;
3549     END LOOP;
3550
3551     RETURN NULL;
3552 END
3553 $$ LANGUAGE plpgsql;
3554
3555 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
3556 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
3557 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
3558  RETURNS TEXT
3559  LANGUAGE plperlu
3560 AS $function$
3561 use strict;
3562 use warnings;
3563
3564 use MARC::Record;
3565 use MARC::File::XML (BinaryEncoding => 'utf8');
3566
3567 binmode(STDERR, ':bytes');
3568 binmode(STDOUT, ':utf8');
3569 binmode(STDERR, ':utf8');
3570
3571 my $marc_xml = shift;
3572 my $new_9_to_set = shift;
3573
3574 $marc_xml =~ s/(<leader>.........)./${1}a/;
3575
3576 eval {
3577     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3578 };
3579 if ($@) {
3580     #elog("could not parse $bibid: $@\n");
3581     import MARC::File::XML (BinaryEncoding => 'utf8');
3582     return $marc_xml;
3583 }
3584
3585 my @uris = $marc_xml->field('856');
3586 return $marc_xml->as_xml_record() unless @uris;
3587
3588 foreach my $field (@uris) {
3589     my $ind1 = $field->indicator('1');
3590     if (!defined $ind1) { next; }
3591     if ($ind1 ne '1' && $ind1 ne '4') { next; }
3592     my $ind2 = $field->indicator('2');
3593     if (!defined $ind2) { next; }
3594     if ($ind2 ne '0' && $ind2 ne '1') { next; }
3595     $field->add_subfields( '9' => $new_9_to_set );
3596 }
3597
3598 return $marc_xml->as_xml_record();
3599
3600 $function$;
3601
3602 -- yet another subfield 9 function, this one only adds the $9 and forces
3603 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
3604 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
3605 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
3606  RETURNS TEXT
3607  LANGUAGE plperlu
3608 AS $function$
3609 use strict;
3610 use warnings;
3611
3612 use MARC::Record;
3613 use MARC::File::XML (BinaryEncoding => 'utf8');
3614
3615 binmode(STDERR, ':bytes');
3616 binmode(STDOUT, ':utf8');
3617 binmode(STDERR, ':utf8');
3618
3619 my $marc_xml = shift;
3620 my $new_9_to_set = shift;
3621
3622 $marc_xml =~ s/(<leader>.........)./${1}a/;
3623
3624 eval {
3625     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3626 };
3627 if ($@) {
3628     #elog("could not parse $bibid: $@\n");
3629     import MARC::File::XML (BinaryEncoding => 'utf8');
3630     return $marc_xml;
3631 }
3632
3633 my @uris = $marc_xml->field('856');
3634 return $marc_xml->as_xml_record() unless @uris;
3635
3636 foreach my $field (@uris) {
3637     my $ind1 = $field->indicator('1');
3638     if (!defined $ind1) { next; }
3639     if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
3640     my $ind2 = $field->indicator('2');
3641     if (!defined $ind2) { next; }
3642     if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
3643     $field->add_subfields( '9' => $new_9_to_set );
3644 }
3645
3646 return $marc_xml->as_xml_record();
3647
3648 $function$;
3649
3650 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3651 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3652 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3653  RETURNS TEXT
3654  LANGUAGE plperlu
3655 AS $function$
3656 use strict;
3657 use warnings;
3658
3659 use MARC::Record;
3660 use MARC::File::XML (BinaryEncoding => 'utf8');
3661
3662 binmode(STDERR, ':bytes');
3663 binmode(STDOUT, ':utf8');
3664 binmode(STDERR, ':utf8');
3665
3666 my $marc_xml = shift;
3667 my $matching_u_text = shift;
3668 my $new_9_to_set = shift;
3669
3670 $marc_xml =~ s/(<leader>.........)./${1}a/;
3671
3672 eval {
3673     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3674 };
3675 if ($@) {
3676     #elog("could not parse $bibid: $@\n");
3677     import MARC::File::XML (BinaryEncoding => 'utf8');
3678     return;
3679 }
3680
3681 my @uris = $marc_xml->field('856');
3682 return unless @uris;
3683
3684 foreach my $field (@uris) {
3685     my $sfu = $field->subfield('u');
3686     my $ind2 = $field->indicator('2');
3687     if (!defined $ind2) { next; }
3688     if ($ind2 ne '0') { next; }
3689     if (!defined $sfu) { next; }
3690     if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
3691         $field->add_subfields( '9' => $new_9_to_set );
3692         last;
3693     }
3694 }
3695
3696 return $marc_xml->as_xml_record();
3697
3698 $function$;
3699
3700 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3701 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3702     RETURNS BOOLEAN AS
3703 $BODY$
3704 DECLARE
3705     source_xml    TEXT;
3706     new_xml       TEXT;
3707     r             BOOLEAN;
3708 BEGIN
3709
3710     EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3711
3712     SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3713
3714     r = FALSE;
3715         new_xml = '$_$' || new_xml || '$_$';
3716
3717     IF new_xml != source_xml THEN
3718         EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3719         r = TRUE;
3720     END IF;
3721
3722     RETURN r;
3723
3724 END;
3725 $BODY$ LANGUAGE plpgsql;
3726
3727 -- strip marc tag
3728 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
3729 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
3730  RETURNS TEXT
3731  LANGUAGE plperlu
3732 AS $function$
3733 use strict;
3734 use warnings;
3735
3736 use MARC::Record;
3737 use MARC::File::XML (BinaryEncoding => 'utf8');
3738
3739 binmode(STDERR, ':bytes');
3740 binmode(STDOUT, ':utf8');
3741 binmode(STDERR, ':utf8');
3742
3743 my $marc_xml = shift;
3744 my $tag = shift;
3745
3746 $marc_xml =~ s/(<leader>.........)./${1}a/;
3747
3748 eval {
3749     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3750 };
3751 if ($@) {
3752     #elog("could not parse $bibid: $@\n");
3753     import MARC::File::XML (BinaryEncoding => 'utf8');
3754     return $marc_xml;
3755 }
3756
3757 my @fields = $marc_xml->field($tag);
3758 return $marc_xml->as_xml_record() unless @fields;
3759
3760 $marc_xml->delete_fields(@fields);
3761
3762 return $marc_xml->as_xml_record();
3763
3764 $function$;
3765
3766 -- convenience function for linking to the item staging table
3767
3768 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3769     DECLARE
3770         table_schema ALIAS FOR $1;
3771         table_name ALIAS FOR $2;
3772         foreign_column_name ALIAS FOR $3;
3773         main_column_name ALIAS FOR $4;
3774         btrim_desired ALIAS FOR $5;
3775         proceed BOOLEAN;
3776     BEGIN
3777         EXECUTE 'SELECT EXISTS (
3778             SELECT 1
3779             FROM information_schema.columns
3780             WHERE table_schema = $1
3781             AND table_name = $2
3782             and column_name = $3
3783         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3784         IF NOT proceed THEN
3785             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3786         END IF;
3787
3788         EXECUTE 'SELECT EXISTS (
3789             SELECT 1
3790             FROM information_schema.columns
3791             WHERE table_schema = $1
3792             AND table_name = ''asset_copy_legacy''
3793             and column_name = $2
3794         )' INTO proceed USING table_schema, main_column_name;
3795         IF NOT proceed THEN
3796             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3797         END IF;
3798
3799         EXECUTE 'ALTER TABLE '
3800             || quote_ident(table_name)
3801             || ' DROP COLUMN IF EXISTS x_item';
3802         EXECUTE 'ALTER TABLE '
3803             || quote_ident(table_name)
3804             || ' ADD COLUMN x_item BIGINT';
3805
3806         IF btrim_desired THEN
3807             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3808                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3809                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3810                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3811         ELSE
3812             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3813                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3814                 || ' WHERE a.' || quote_ident(foreign_column_name)
3815                 || ' = b.' || quote_ident(main_column_name);
3816         END IF;
3817
3818         --EXECUTE 'SELECT migration_tools.assert(
3819         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3820         --    ''Cannot link every barcode'',
3821         --    ''Every barcode linked''
3822         --);';
3823
3824     END;
3825 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3826
3827 -- convenience function for linking to the user staging table
3828
3829 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3830     DECLARE
3831         table_schema ALIAS FOR $1;
3832         table_name ALIAS FOR $2;
3833         foreign_column_name ALIAS FOR $3;
3834         main_column_name ALIAS FOR $4;
3835         btrim_desired ALIAS FOR $5;
3836         proceed BOOLEAN;
3837     BEGIN
3838         EXECUTE 'SELECT EXISTS (
3839             SELECT 1
3840             FROM information_schema.columns
3841             WHERE table_schema = $1
3842             AND table_name = $2
3843             and column_name = $3
3844         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3845         IF NOT proceed THEN
3846             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3847         END IF;
3848
3849         EXECUTE 'SELECT EXISTS (
3850             SELECT 1
3851             FROM information_schema.columns
3852             WHERE table_schema = $1
3853             AND table_name = ''actor_usr_legacy''
3854             and column_name = $2
3855         )' INTO proceed USING table_schema, main_column_name;
3856         IF NOT proceed THEN
3857             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3858         END IF;
3859
3860         EXECUTE 'ALTER TABLE '
3861             || quote_ident(table_name)
3862             || ' DROP COLUMN IF EXISTS x_user';
3863         EXECUTE 'ALTER TABLE '
3864             || quote_ident(table_name)
3865             || ' ADD COLUMN x_user INTEGER';
3866
3867         IF btrim_desired THEN
3868             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3869                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3870                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3871                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3872         ELSE
3873             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3874                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3875                 || ' WHERE a.' || quote_ident(foreign_column_name)
3876                 || ' = b.' || quote_ident(main_column_name);
3877         END IF;
3878
3879         --EXECUTE 'SELECT migration_tools.assert(
3880         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3881         --    ''Cannot link every barcode'',
3882         --    ''Every barcode linked''
3883         --);';
3884
3885     END;
3886 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3887
3888 -- convenience function for linking two tables
3889 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3890 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3891     DECLARE
3892         table_schema ALIAS FOR $1;
3893         table_a ALIAS FOR $2;
3894         column_a ALIAS FOR $3;
3895         table_b ALIAS FOR $4;
3896         column_b ALIAS FOR $5;
3897         column_x ALIAS FOR $6;
3898         btrim_desired ALIAS FOR $7;
3899         proceed BOOLEAN;
3900     BEGIN
3901         EXECUTE 'SELECT EXISTS (
3902             SELECT 1
3903             FROM information_schema.columns
3904             WHERE table_schema = $1
3905             AND table_name = $2
3906             and column_name = $3
3907         )' INTO proceed USING table_schema, table_a, column_a;
3908         IF NOT proceed THEN
3909             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3910         END IF;
3911
3912         EXECUTE 'SELECT EXISTS (
3913             SELECT 1
3914             FROM information_schema.columns
3915             WHERE table_schema = $1
3916             AND table_name = $2
3917             and column_name = $3
3918         )' INTO proceed USING table_schema, table_b, column_b;
3919         IF NOT proceed THEN
3920             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3921         END IF;
3922
3923         EXECUTE 'ALTER TABLE '
3924             || quote_ident(table_b)
3925             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3926         EXECUTE 'ALTER TABLE '
3927             || quote_ident(table_b)
3928             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3929
3930         IF btrim_desired THEN
3931             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3932                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3933                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3934                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3935         ELSE
3936             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3937                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
3938                 || ' WHERE a.' || quote_ident(column_a)
3939                 || ' = b.' || quote_ident(column_b);
3940         END IF;
3941
3942     END;
3943 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3944
3945 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3946 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3947 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3948     DECLARE
3949         table_schema ALIAS FOR $1;
3950         table_a ALIAS FOR $2;
3951         column_a ALIAS FOR $3;
3952         table_b ALIAS FOR $4;
3953         column_b ALIAS FOR $5;
3954         column_w ALIAS FOR $6;
3955         column_x ALIAS FOR $7;
3956         btrim_desired ALIAS FOR $8;
3957         proceed BOOLEAN;
3958     BEGIN
3959         EXECUTE 'SELECT EXISTS (
3960             SELECT 1
3961             FROM information_schema.columns
3962             WHERE table_schema = $1
3963             AND table_name = $2
3964             and column_name = $3
3965         )' INTO proceed USING table_schema, table_a, column_a;
3966         IF NOT proceed THEN
3967             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3968         END IF;
3969
3970         EXECUTE 'SELECT EXISTS (
3971             SELECT 1
3972             FROM information_schema.columns
3973             WHERE table_schema = $1
3974             AND table_name = $2
3975             and column_name = $3
3976         )' INTO proceed USING table_schema, table_b, column_b;
3977         IF NOT proceed THEN
3978             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3979         END IF;
3980
3981         EXECUTE 'ALTER TABLE '
3982             || quote_ident(table_b)
3983             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3984         EXECUTE 'ALTER TABLE '
3985             || quote_ident(table_b)
3986             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3987
3988         IF btrim_desired THEN
3989             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3990                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3991                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3992                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3993         ELSE
3994             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3995                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3996                 || ' WHERE a.' || quote_ident(column_a)
3997                 || ' = b.' || quote_ident(column_b);
3998         END IF;
3999
4000     END;
4001 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4002
4003 -- 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
4004 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4005 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4006     DECLARE
4007         table_schema ALIAS FOR $1;
4008         table_a ALIAS FOR $2;
4009         column_a ALIAS FOR $3;
4010         table_b ALIAS FOR $4;
4011         column_b ALIAS FOR $5;
4012         column_w ALIAS FOR $6;
4013         column_x ALIAS FOR $7;
4014         proceed BOOLEAN;
4015     BEGIN
4016         EXECUTE 'SELECT EXISTS (
4017             SELECT 1
4018             FROM information_schema.columns
4019             WHERE table_schema = $1
4020             AND table_name = $2
4021             and column_name = $3
4022         )' INTO proceed USING table_schema, table_a, column_a;
4023         IF NOT proceed THEN
4024             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4025         END IF;
4026
4027         EXECUTE 'SELECT EXISTS (
4028             SELECT 1
4029             FROM information_schema.columns
4030             WHERE table_schema = $1
4031             AND table_name = $2
4032             and column_name = $3
4033         )' INTO proceed USING table_schema, table_b, column_b;
4034         IF NOT proceed THEN
4035             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4036         END IF;
4037
4038         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4039             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4040             || ' WHERE a.' || quote_ident(column_a)
4041             || ' = b.' || quote_ident(column_b);
4042
4043     END;
4044 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4045
4046 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4047     DECLARE
4048         table_schema ALIAS FOR $1;
4049         table_a ALIAS FOR $2;
4050         column_a ALIAS FOR $3;
4051         table_b ALIAS FOR $4;
4052         column_b ALIAS FOR $5;
4053         column_w ALIAS FOR $6;
4054         column_x ALIAS FOR $7;
4055         proceed BOOLEAN;
4056     BEGIN
4057         EXECUTE 'SELECT EXISTS (
4058             SELECT 1
4059             FROM information_schema.columns
4060             WHERE table_schema = $1
4061             AND table_name = $2
4062             and column_name = $3
4063         )' INTO proceed USING table_schema, table_a, column_a;
4064         IF NOT proceed THEN
4065             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4066         END IF;
4067
4068         EXECUTE 'SELECT EXISTS (
4069             SELECT 1
4070             FROM information_schema.columns
4071             WHERE table_schema = $1
4072             AND table_name = $2
4073             and column_name = $3
4074         )' INTO proceed USING table_schema, table_b, column_b;
4075         IF NOT proceed THEN
4076             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4077         END IF;
4078
4079         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4080             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4081             || ' WHERE a.' || quote_ident(column_a)
4082             || ' = b.' || quote_ident(column_b)
4083             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4084
4085     END;
4086 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4087
4088 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4089     DECLARE
4090         table_schema ALIAS FOR $1;
4091         table_a ALIAS FOR $2;
4092         column_a ALIAS FOR $3;
4093         table_b ALIAS FOR $4;
4094         column_b ALIAS FOR $5;
4095         column_w ALIAS FOR $6;
4096         column_x ALIAS FOR $7;
4097         proceed BOOLEAN;
4098     BEGIN
4099         EXECUTE 'SELECT EXISTS (
4100             SELECT 1
4101             FROM information_schema.columns
4102             WHERE table_schema = $1
4103             AND table_name = $2
4104             and column_name = $3
4105         )' INTO proceed USING table_schema, table_a, column_a;
4106         IF NOT proceed THEN
4107             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4108         END IF;
4109
4110         EXECUTE 'SELECT EXISTS (
4111             SELECT 1
4112             FROM information_schema.columns
4113             WHERE table_schema = $1
4114             AND table_name = $2
4115             and column_name = $3
4116         )' INTO proceed USING table_schema, table_b, column_b;
4117         IF NOT proceed THEN
4118             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4119         END IF;
4120
4121         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4122             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4123             || ' WHERE a.' || quote_ident(column_a)
4124             || ' = b.' || quote_ident(column_b)
4125             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4126
4127     END;
4128 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4129
4130 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4131     DECLARE
4132         table_schema ALIAS FOR $1;
4133         table_a ALIAS FOR $2;
4134         column_a ALIAS FOR $3;
4135         table_b ALIAS FOR $4;
4136         column_b ALIAS FOR $5;
4137         column_w ALIAS FOR $6;
4138         column_x ALIAS FOR $7;
4139         proceed BOOLEAN;
4140     BEGIN
4141         EXECUTE 'SELECT EXISTS (
4142             SELECT 1
4143             FROM information_schema.columns
4144             WHERE table_schema = $1
4145             AND table_name = $2
4146             and column_name = $3
4147         )' INTO proceed USING table_schema, table_a, column_a;
4148         IF NOT proceed THEN
4149             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4150         END IF;
4151
4152         EXECUTE 'SELECT EXISTS (
4153             SELECT 1
4154             FROM information_schema.columns
4155             WHERE table_schema = $1
4156             AND table_name = $2
4157             and column_name = $3
4158         )' INTO proceed USING table_schema, table_b, column_b;
4159         IF NOT proceed THEN
4160             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4161         END IF;
4162
4163         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4164             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4165             || ' WHERE a.' || quote_ident(column_a)
4166             || ' = b.' || quote_ident(column_b)
4167             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4168
4169     END;
4170 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4171
4172 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4173     DECLARE
4174         table_schema ALIAS FOR $1;
4175         table_a ALIAS FOR $2;
4176         column_a ALIAS FOR $3;
4177         table_b ALIAS FOR $4;
4178         column_b ALIAS FOR $5;
4179         column_w ALIAS FOR $6;
4180         column_x ALIAS FOR $7;
4181         proceed BOOLEAN;
4182     BEGIN
4183         EXECUTE 'SELECT EXISTS (
4184             SELECT 1
4185             FROM information_schema.columns
4186             WHERE table_schema = $1
4187             AND table_name = $2
4188             and column_name = $3
4189         )' INTO proceed USING table_schema, table_a, column_a;
4190         IF NOT proceed THEN
4191             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4192         END IF;
4193
4194         EXECUTE 'SELECT EXISTS (
4195             SELECT 1
4196             FROM information_schema.columns
4197             WHERE table_schema = $1
4198             AND table_name = $2
4199             and column_name = $3
4200         )' INTO proceed USING table_schema, table_b, column_b;
4201         IF NOT proceed THEN
4202             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4203         END IF;
4204
4205         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4206             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4207             || ' WHERE a.' || quote_ident(column_a)
4208             || ' = b.' || quote_ident(column_b)
4209             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4210
4211     END;
4212 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4213
4214 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4215     DECLARE
4216         table_schema ALIAS FOR $1;
4217         table_a ALIAS FOR $2;
4218         column_a ALIAS FOR $3;
4219         table_b ALIAS FOR $4;
4220         column_b ALIAS FOR $5;
4221         column_w ALIAS FOR $6;
4222         column_x ALIAS FOR $7;
4223         proceed BOOLEAN;
4224     BEGIN
4225         EXECUTE 'SELECT EXISTS (
4226             SELECT 1
4227             FROM information_schema.columns
4228             WHERE table_schema = $1
4229             AND table_name = $2
4230             and column_name = $3
4231         )' INTO proceed USING table_schema, table_a, column_a;
4232         IF NOT proceed THEN
4233             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4234         END IF;
4235
4236         EXECUTE 'SELECT EXISTS (
4237             SELECT 1
4238             FROM information_schema.columns
4239             WHERE table_schema = $1
4240             AND table_name = $2
4241             and column_name = $3
4242         )' INTO proceed USING table_schema, table_b, column_b;
4243         IF NOT proceed THEN
4244             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4245         END IF;
4246
4247         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4248             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4249             || ' WHERE a.' || quote_ident(column_a)
4250             || ' = b.' || quote_ident(column_b)
4251             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4252
4253     END;
4254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4255
4256 -- convenience function for handling desired asset stat cats
4257
4258 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4259     DECLARE
4260         table_schema ALIAS FOR $1;
4261         table_name ALIAS FOR $2;
4262         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4263         org_shortname ALIAS FOR $4;
4264         proceed BOOLEAN;
4265         org INTEGER;
4266         org_list INTEGER[];
4267         sc TEXT;
4268         sce TEXT;
4269     BEGIN
4270
4271         SELECT 'desired_sc' || field_suffix INTO sc;
4272         SELECT 'desired_sce' || field_suffix INTO sce;
4273
4274         EXECUTE 'SELECT EXISTS (
4275             SELECT 1
4276             FROM information_schema.columns
4277             WHERE table_schema = $1
4278             AND table_name = $2
4279             and column_name = $3
4280         )' INTO proceed USING table_schema, table_name, sc;
4281         IF NOT proceed THEN
4282             RAISE EXCEPTION 'Missing column %', sc;
4283         END IF;
4284         EXECUTE 'SELECT EXISTS (
4285             SELECT 1
4286             FROM information_schema.columns
4287             WHERE table_schema = $1
4288             AND table_name = $2
4289             and column_name = $3
4290         )' INTO proceed USING table_schema, table_name, sce;
4291         IF NOT proceed THEN
4292             RAISE EXCEPTION 'Missing column %', sce;
4293         END IF;
4294
4295         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4296         IF org IS NULL THEN
4297             RAISE EXCEPTION 'Cannot find org by shortname';
4298         END IF;
4299         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4300
4301         -- caller responsible for their own truncates though we try to prevent duplicates
4302         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4303             SELECT DISTINCT
4304                  $1
4305                 ,BTRIM('||sc||')
4306             FROM
4307                 ' || quote_ident(table_name) || '
4308             WHERE
4309                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4310                 AND NOT EXISTS (
4311                     SELECT id
4312                     FROM asset.stat_cat
4313                     WHERE owner = ANY ($2)
4314                     AND name = BTRIM('||sc||')
4315                 )
4316                 AND NOT EXISTS (
4317                     SELECT id
4318                     FROM asset_stat_cat
4319                     WHERE owner = ANY ($2)
4320                     AND name = BTRIM('||sc||')
4321                 )
4322             ORDER BY 2;'
4323         USING org, org_list;
4324
4325         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4326             SELECT DISTINCT
4327                 COALESCE(
4328                     (SELECT id
4329                         FROM asset.stat_cat
4330                         WHERE owner = ANY ($2)
4331                         AND BTRIM('||sc||') = BTRIM(name))
4332                    ,(SELECT id
4333                         FROM asset_stat_cat
4334                         WHERE owner = ANY ($2)
4335                         AND BTRIM('||sc||') = BTRIM(name))
4336                 )
4337                 ,$1
4338                 ,BTRIM('||sce||')
4339             FROM
4340                 ' || quote_ident(table_name) || '
4341             WHERE
4342                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4343                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4344                 AND NOT EXISTS (
4345                     SELECT id
4346                     FROM asset.stat_cat_entry
4347                     WHERE stat_cat = (
4348                         SELECT id
4349                         FROM asset.stat_cat
4350                         WHERE owner = ANY ($2)
4351                         AND BTRIM('||sc||') = BTRIM(name)
4352                     ) AND value = BTRIM('||sce||')
4353                     AND owner = ANY ($2)
4354                 )
4355                 AND NOT EXISTS (
4356                     SELECT id
4357                     FROM asset_stat_cat_entry
4358                     WHERE stat_cat = (
4359                         SELECT id
4360                         FROM asset_stat_cat
4361                         WHERE owner = ANY ($2)
4362                         AND BTRIM('||sc||') = BTRIM(name)
4363                     ) AND value = BTRIM('||sce||')
4364                     AND owner = ANY ($2)
4365                 )
4366             ORDER BY 1,3;'
4367         USING org, org_list;
4368     END;
4369 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4370
4371 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4372     DECLARE
4373         table_schema ALIAS FOR $1;
4374         table_name ALIAS FOR $2;
4375         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4376         org_shortname ALIAS FOR $4;
4377         proceed BOOLEAN;
4378         org INTEGER;
4379         org_list INTEGER[];
4380         o INTEGER;
4381         sc TEXT;
4382         sce TEXT;
4383     BEGIN
4384         SELECT 'desired_sc' || field_suffix INTO sc;
4385         SELECT 'desired_sce' || field_suffix INTO sce;
4386         EXECUTE 'SELECT EXISTS (
4387             SELECT 1
4388             FROM information_schema.columns
4389             WHERE table_schema = $1
4390             AND table_name = $2
4391             and column_name = $3
4392         )' INTO proceed USING table_schema, table_name, sc;
4393         IF NOT proceed THEN
4394             RAISE EXCEPTION 'Missing column %', sc;
4395         END IF;
4396         EXECUTE 'SELECT EXISTS (
4397             SELECT 1
4398             FROM information_schema.columns
4399             WHERE table_schema = $1
4400             AND table_name = $2
4401             and column_name = $3
4402         )' INTO proceed USING table_schema, table_name, sce;
4403         IF NOT proceed THEN
4404             RAISE EXCEPTION 'Missing column %', sce;
4405         END IF;
4406
4407         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4408         IF org IS NULL THEN
4409             RAISE EXCEPTION 'Cannot find org by shortname';
4410         END IF;
4411
4412         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4413
4414         EXECUTE 'ALTER TABLE '
4415             || quote_ident(table_name)
4416             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4417         EXECUTE 'ALTER TABLE '
4418             || quote_ident(table_name)
4419             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4420         EXECUTE 'ALTER TABLE '
4421             || quote_ident(table_name)
4422             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4423         EXECUTE 'ALTER TABLE '
4424             || quote_ident(table_name)
4425             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4426
4427
4428         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4429             SET
4430                 x_sc' || field_suffix || ' = id
4431             FROM
4432                 (SELECT id, name, owner FROM asset_stat_cat
4433                     UNION SELECT id, name, owner FROM asset.stat_cat) u
4434             WHERE
4435                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4436                 AND u.owner = ANY ($1);'
4437         USING org_list;
4438
4439         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4440             SET
4441                 x_sce' || field_suffix || ' = id
4442             FROM
4443                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
4444                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
4445             WHERE
4446                     u.stat_cat = x_sc' || field_suffix || '
4447                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4448                 AND u.owner = ANY ($1);'
4449         USING org_list;
4450
4451         EXECUTE 'SELECT migration_tools.assert(
4452             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4453             ''Cannot find a desired stat cat'',
4454             ''Found all desired stat cats''
4455         );';
4456
4457         EXECUTE 'SELECT migration_tools.assert(
4458             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4459             ''Cannot find a desired stat cat entry'',
4460             ''Found all desired stat cat entries''
4461         );';
4462
4463     END;
4464 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4465
4466 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
4467 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4468  LANGUAGE plpgsql
4469 AS $function$
4470 DECLARE
4471     c_name     TEXT;
4472 BEGIN
4473
4474     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4475             table_name = t_name
4476             AND table_schema = s_name
4477             AND (data_type='text' OR data_type='character varying')
4478             AND column_name like 'l_%'
4479     LOOP
4480        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4481     END LOOP;
4482
4483     RETURN TRUE;
4484 END
4485 $function$;
4486
4487 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
4488 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4489  LANGUAGE plpgsql
4490 AS $function$
4491 DECLARE
4492     c_name     TEXT;
4493 BEGIN
4494
4495     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4496             table_name = t_name
4497             AND table_schema = s_name
4498             AND (data_type='text' OR data_type='character varying')
4499     LOOP
4500        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
4501     END LOOP;
4502
4503     RETURN TRUE;
4504 END
4505 $function$;
4506
4507 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
4508 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4509  LANGUAGE plpgsql
4510 AS $function$
4511 DECLARE
4512     c_name     TEXT;
4513 BEGIN
4514
4515     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4516             table_name = t_name
4517             AND table_schema = s_name
4518             AND (data_type='text' OR data_type='character varying')
4519             AND column_name like 'l_%'
4520     LOOP
4521        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
4522     END LOOP;
4523
4524     RETURN TRUE;
4525 END
4526 $function$;
4527
4528 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
4529 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
4530  LANGUAGE plpgsql
4531 AS $function$
4532 DECLARE
4533     c_name     TEXT;
4534 BEGIN
4535
4536     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
4537             table_name = t_name
4538             AND table_schema = s_name
4539             AND (data_type='text' OR data_type='character varying')
4540     LOOP
4541        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
4542     END LOOP;
4543
4544     RETURN TRUE;
4545 END
4546 $function$;