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