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 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2615     my ($marcxml) = @_;
2616
2617     use MARC::Record;
2618     use MARC::File::XML;
2619     use MARC::Field;
2620
2621     my $field;
2622     eval {
2623         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2624         $field = $marc->leader();
2625     };
2626     return $field;
2627 $$ LANGUAGE PLPERLU STABLE;
2628
2629 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2630     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2631
2632     use MARC::Record;
2633     use MARC::File::XML;
2634     use MARC::Field;
2635
2636     my $field;
2637     eval {
2638         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2639         $field = $marc->field($tag);
2640     };
2641     return $field->as_string($subfield,$delimiter);
2642 $$ LANGUAGE PLPERLU STABLE;
2643
2644 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2645     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2646
2647     use MARC::Record;
2648     use MARC::File::XML;
2649     use MARC::Field;
2650
2651     my @fields;
2652     eval {
2653         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2654         @fields = $marc->field($tag);
2655     };
2656     my @texts;
2657     foreach my $field (@fields) {
2658         push @texts, $field->as_string($subfield,$delimiter);
2659     }
2660     return \@texts;
2661 $$ LANGUAGE PLPERLU STABLE;
2662
2663 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2664     SELECT action.find_hold_matrix_matchpoint(
2665         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2666         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2667         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2668         (SELECT usr FROM action.hold_request WHERE id = $1),
2669         (SELECT requestor FROM action.hold_request WHERE id = $1)
2670     );
2671 $$ LANGUAGE SQL;
2672
2673 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2674     SELECT action.hold_request_permit_test(
2675         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2676         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2677         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2678         (SELECT usr FROM action.hold_request WHERE id = $1),
2679         (SELECT requestor FROM action.hold_request WHERE id = $1)
2680     );
2681 $$ LANGUAGE SQL;
2682
2683 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2684     SELECT action.find_circ_matrix_matchpoint(
2685         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2686         (SELECT target_copy FROM action.circulation WHERE id = $1),
2687         (SELECT usr FROM action.circulation WHERE id = $1),
2688         (SELECT COALESCE(
2689                 NULLIF(phone_renewal,false),
2690                 NULLIF(desk_renewal,false),
2691                 NULLIF(opac_renewal,false),
2692                 false
2693             ) FROM action.circulation WHERE id = $1
2694         )
2695     );
2696 $$ LANGUAGE SQL;
2697
2698 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2699     DECLARE
2700         test ALIAS FOR $1;
2701     BEGIN
2702         IF NOT test THEN
2703             RAISE EXCEPTION 'assertion';
2704         END IF;
2705     END;
2706 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2707
2708 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2709     DECLARE
2710         test ALIAS FOR $1;
2711         msg ALIAS FOR $2;
2712     BEGIN
2713         IF NOT test THEN
2714             RAISE EXCEPTION '%', msg;
2715         END IF;
2716     END;
2717 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2718
2719 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2720     DECLARE
2721         test ALIAS FOR $1;
2722         fail_msg ALIAS FOR $2;
2723         success_msg ALIAS FOR $3;
2724     BEGIN
2725         IF NOT test THEN
2726             RAISE EXCEPTION '%', fail_msg;
2727         END IF;
2728         RETURN success_msg;
2729     END;
2730 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2731
2732 -- push bib sequence and return starting value for reserved range
2733 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2734     DECLARE
2735         bib_count ALIAS FOR $1;
2736         output BIGINT;
2737     BEGIN
2738         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2739         FOR output IN
2740             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2741         LOOP
2742             RETURN output;
2743         END LOOP;
2744     END;
2745 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2746
2747 -- set a new salted password
2748
2749 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2750     DECLARE
2751         usr_id              ALIAS FOR $1;
2752         plain_passwd        ALIAS FOR $2;
2753         plain_salt          TEXT;
2754         md5_passwd          TEXT;
2755     BEGIN
2756
2757         SELECT actor.create_salt('main') INTO plain_salt;
2758
2759         SELECT MD5(plain_passwd) INTO md5_passwd;
2760
2761         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2762
2763         RETURN TRUE;
2764
2765     END;
2766 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2767
2768
2769 -- convenience functions for handling copy_location maps
2770
2771 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2772     DECLARE
2773         table_schema ALIAS FOR $1;
2774         table_name ALIAS FOR $2;
2775         org_shortname ALIAS FOR $3;
2776         org_range ALIAS FOR $4;
2777         proceed BOOLEAN;
2778         org INTEGER;
2779         org_list INTEGER[];
2780         o INTEGER;
2781     BEGIN
2782         EXECUTE 'SELECT EXISTS (
2783             SELECT 1
2784             FROM information_schema.columns
2785             WHERE table_schema = $1
2786             AND table_name = $2
2787             and column_name = ''desired_shelf''
2788         )' INTO proceed USING table_schema, table_name;
2789         IF NOT proceed THEN
2790             RAISE EXCEPTION 'Missing column desired_shelf';
2791         END IF;
2792
2793         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2794         IF org IS NULL THEN
2795             RAISE EXCEPTION 'Cannot find org by shortname';
2796         END IF;
2797
2798         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2799
2800         EXECUTE 'ALTER TABLE '
2801             || quote_ident(table_name)
2802             || ' DROP COLUMN IF EXISTS x_shelf';
2803         EXECUTE 'ALTER TABLE '
2804             || quote_ident(table_name)
2805             || ' ADD COLUMN x_shelf INTEGER';
2806
2807         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2808             || ' SET x_shelf = id FROM asset_copy_location b'
2809             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2810             || ' AND b.owning_lib = $1'
2811             || ' AND NOT b.deleted'
2812         USING org;
2813
2814         FOREACH o IN ARRAY org_list LOOP
2815             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2816                 || ' SET x_shelf = id FROM asset.copy_location b'
2817                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2818                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2819                 || ' AND NOT b.deleted'
2820             USING o;
2821         END LOOP;
2822
2823         EXECUTE 'SELECT migration_tools.assert(
2824             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2825             ''Cannot find a desired location'',
2826             ''Found all desired locations''
2827         );';
2828
2829     END;
2830 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2831
2832 -- convenience functions for handling circmod maps
2833
2834 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2835     DECLARE
2836         table_schema ALIAS FOR $1;
2837         table_name ALIAS FOR $2;
2838         proceed BOOLEAN;
2839     BEGIN
2840         EXECUTE 'SELECT EXISTS (
2841             SELECT 1
2842             FROM information_schema.columns
2843             WHERE table_schema = $1
2844             AND table_name = $2
2845             and column_name = ''desired_circmod''
2846         )' INTO proceed USING table_schema, table_name;
2847         IF NOT proceed THEN
2848             RAISE EXCEPTION 'Missing column desired_circmod';
2849         END IF;
2850
2851         EXECUTE 'ALTER TABLE '
2852             || quote_ident(table_name)
2853             || ' DROP COLUMN IF EXISTS x_circmod';
2854         EXECUTE 'ALTER TABLE '
2855             || quote_ident(table_name)
2856             || ' ADD COLUMN x_circmod TEXT';
2857
2858         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2859             || ' SET x_circmod = code FROM config.circ_modifier b'
2860             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
2861
2862         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2863             || ' SET x_circmod = code FROM config.circ_modifier b'
2864             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2865             || ' AND x_circmod IS NULL';
2866
2867         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2868             || ' SET x_circmod = code FROM config.circ_modifier b'
2869             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2870             || ' AND x_circmod IS NULL';
2871
2872         EXECUTE 'SELECT migration_tools.assert(
2873             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2874             ''Cannot find a desired circulation modifier'',
2875             ''Found all desired circulation modifiers''
2876         );';
2877
2878     END;
2879 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2880
2881 -- convenience functions for handling item status maps
2882
2883 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
2884     DECLARE
2885         table_schema ALIAS FOR $1;
2886         table_name ALIAS FOR $2;
2887         proceed BOOLEAN;
2888     BEGIN
2889         EXECUTE 'SELECT EXISTS (
2890             SELECT 1
2891             FROM information_schema.columns
2892             WHERE table_schema = $1
2893             AND table_name = $2
2894             and column_name = ''desired_status''
2895         )' INTO proceed USING table_schema, table_name;
2896         IF NOT proceed THEN
2897             RAISE EXCEPTION 'Missing column desired_status';
2898         END IF;
2899
2900         EXECUTE 'ALTER TABLE '
2901             || quote_ident(table_name)
2902             || ' DROP COLUMN IF EXISTS x_status';
2903         EXECUTE 'ALTER TABLE '
2904             || quote_ident(table_name)
2905             || ' ADD COLUMN x_status INTEGER';
2906
2907         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2908             || ' SET x_status = id FROM config.copy_status b'
2909             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2910
2911         EXECUTE 'SELECT migration_tools.assert(
2912             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2913             ''Cannot find a desired copy status'',
2914             ''Found all desired copy statuses''
2915         );';
2916
2917     END;
2918 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2919
2920 -- convenience functions for handling org maps
2921
2922 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2923     DECLARE
2924         table_schema ALIAS FOR $1;
2925         table_name ALIAS FOR $2;
2926         proceed BOOLEAN;
2927     BEGIN
2928         EXECUTE 'SELECT EXISTS (
2929             SELECT 1
2930             FROM information_schema.columns
2931             WHERE table_schema = $1
2932             AND table_name = $2
2933             and column_name = ''desired_org''
2934         )' INTO proceed USING table_schema, table_name;
2935         IF NOT proceed THEN
2936             RAISE EXCEPTION 'Missing column desired_org';
2937         END IF;
2938
2939         EXECUTE 'ALTER TABLE '
2940             || quote_ident(table_name)
2941             || ' DROP COLUMN IF EXISTS x_org';
2942         EXECUTE 'ALTER TABLE '
2943             || quote_ident(table_name)
2944             || ' ADD COLUMN x_org INTEGER';
2945
2946         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2947             || ' SET x_org = id FROM actor.org_unit b'
2948             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2949
2950         EXECUTE 'SELECT migration_tools.assert(
2951             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2952             ''Cannot find a desired org unit'',
2953             ''Found all desired org units''
2954         );';
2955
2956     END;
2957 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2958
2959 -- convenience function for handling desired_not_migrate
2960
2961 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2962     DECLARE
2963         table_schema ALIAS FOR $1;
2964         table_name ALIAS FOR $2;
2965         proceed BOOLEAN;
2966     BEGIN
2967         EXECUTE 'SELECT EXISTS (
2968             SELECT 1
2969             FROM information_schema.columns
2970             WHERE table_schema = $1
2971             AND table_name = $2
2972             and column_name = ''desired_not_migrate''
2973         )' INTO proceed USING table_schema, table_name;
2974         IF NOT proceed THEN
2975             RAISE EXCEPTION 'Missing column desired_not_migrate';
2976         END IF;
2977
2978         EXECUTE 'ALTER TABLE '
2979             || quote_ident(table_name)
2980             || ' DROP COLUMN IF EXISTS x_migrate';
2981         EXECUTE 'ALTER TABLE '
2982             || quote_ident(table_name)
2983             || ' ADD COLUMN x_migrate BOOLEAN';
2984
2985         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2986             || ' SET x_migrate = CASE'
2987             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2988             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
2989             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2990             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
2991             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2992             || ' END';
2993
2994         EXECUTE 'SELECT migration_tools.assert(
2995             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
2996             ''Not all desired_not_migrate values understood'',
2997             ''All desired_not_migrate values understood''
2998         );';
2999
3000     END;
3001 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3002
3003 -- convenience function for handling desired_profile
3004
3005 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3006     DECLARE
3007         table_schema ALIAS FOR $1;
3008         table_name ALIAS FOR $2;
3009         proceed BOOLEAN;
3010     BEGIN
3011         EXECUTE 'SELECT EXISTS (
3012             SELECT 1
3013             FROM information_schema.columns
3014             WHERE table_schema = $1
3015             AND table_name = $2
3016             and column_name = ''desired_profile''
3017         )' INTO proceed USING table_schema, table_name;
3018         IF NOT proceed THEN
3019             RAISE EXCEPTION 'Missing column desired_profile';
3020         END IF;
3021
3022         EXECUTE 'ALTER TABLE '
3023             || quote_ident(table_name)
3024             || ' DROP COLUMN IF EXISTS x_profile';
3025         EXECUTE 'ALTER TABLE '
3026             || quote_ident(table_name)
3027             || ' ADD COLUMN x_profile INTEGER';
3028
3029         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3030             || ' SET x_profile = id FROM permission.grp_tree b'
3031             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3032
3033         EXECUTE 'SELECT migration_tools.assert(
3034             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3035             ''Cannot find a desired profile'',
3036             ''Found all desired profiles''
3037         );';
3038
3039     END;
3040 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3041
3042 -- convenience function for handling desired actor stat cats
3043
3044 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3045     DECLARE
3046         table_schema ALIAS FOR $1;
3047         table_name ALIAS FOR $2;
3048         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3049         org_shortname ALIAS FOR $4;
3050         proceed BOOLEAN;
3051         org INTEGER;
3052         org_list INTEGER[];
3053         sc TEXT;
3054         sce TEXT;
3055     BEGIN
3056
3057         SELECT 'desired_sc' || field_suffix INTO sc;
3058         SELECT 'desired_sce' || field_suffix INTO sce;
3059
3060         EXECUTE 'SELECT EXISTS (
3061             SELECT 1
3062             FROM information_schema.columns
3063             WHERE table_schema = $1
3064             AND table_name = $2
3065             and column_name = $3
3066         )' INTO proceed USING table_schema, table_name, sc;
3067         IF NOT proceed THEN
3068             RAISE EXCEPTION 'Missing column %', sc;
3069         END IF;
3070         EXECUTE 'SELECT EXISTS (
3071             SELECT 1
3072             FROM information_schema.columns
3073             WHERE table_schema = $1
3074             AND table_name = $2
3075             and column_name = $3
3076         )' INTO proceed USING table_schema, table_name, sce;
3077         IF NOT proceed THEN
3078             RAISE EXCEPTION 'Missing column %', sce;
3079         END IF;
3080
3081         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3082         IF org IS NULL THEN
3083             RAISE EXCEPTION 'Cannot find org by shortname';
3084         END IF;
3085         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3086
3087         -- caller responsible for their own truncates though we try to prevent duplicates
3088         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3089             SELECT DISTINCT
3090                  $1
3091                 ,BTRIM('||sc||')
3092             FROM
3093                 ' || quote_ident(table_name) || '
3094             WHERE
3095                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3096                 AND NOT EXISTS (
3097                     SELECT id
3098                     FROM actor.stat_cat
3099                     WHERE owner = ANY ($2)
3100                     AND name = BTRIM('||sc||')
3101                 )
3102                 AND NOT EXISTS (
3103                     SELECT id
3104                     FROM actor_stat_cat
3105                     WHERE owner = ANY ($2)
3106                     AND name = BTRIM('||sc||')
3107                 )
3108             ORDER BY 2;'
3109         USING org, org_list;
3110
3111         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3112             SELECT DISTINCT
3113                 COALESCE(
3114                     (SELECT id
3115                         FROM actor.stat_cat
3116                         WHERE owner = ANY ($2)
3117                         AND BTRIM('||sc||') = BTRIM(name))
3118                    ,(SELECT id
3119                         FROM actor_stat_cat
3120                         WHERE owner = ANY ($2)
3121                         AND BTRIM('||sc||') = BTRIM(name))
3122                 )
3123                 ,$1
3124                 ,BTRIM('||sce||')
3125             FROM
3126                 ' || quote_ident(table_name) || '
3127             WHERE
3128                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3129                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3130                 AND NOT EXISTS (
3131                     SELECT id
3132                     FROM actor.stat_cat_entry
3133                     WHERE stat_cat = (
3134                         SELECT id
3135                         FROM actor.stat_cat
3136                         WHERE owner = ANY ($2)
3137                         AND BTRIM('||sc||') = BTRIM(name)
3138                     ) AND value = BTRIM('||sce||')
3139                 )
3140                 AND NOT EXISTS (
3141                     SELECT id
3142                     FROM actor_stat_cat_entry
3143                     WHERE stat_cat = (
3144                         SELECT id
3145                         FROM actor_stat_cat
3146                         WHERE owner = ANY ($2)
3147                         AND BTRIM('||sc||') = BTRIM(name)
3148                     ) AND value = BTRIM('||sce||')
3149                 )
3150             ORDER BY 1,3;'
3151         USING org, org_list;
3152     END;
3153 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3154
3155 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3156     DECLARE
3157         table_schema ALIAS FOR $1;
3158         table_name ALIAS FOR $2;
3159         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3160         org_shortname ALIAS FOR $4;
3161         proceed BOOLEAN;
3162         org INTEGER;
3163         org_list INTEGER[];
3164         o INTEGER;
3165         sc TEXT;
3166         sce TEXT;
3167     BEGIN
3168         SELECT 'desired_sc' || field_suffix INTO sc;
3169         SELECT 'desired_sce' || field_suffix INTO sce;
3170         EXECUTE 'SELECT EXISTS (
3171             SELECT 1
3172             FROM information_schema.columns
3173             WHERE table_schema = $1
3174             AND table_name = $2
3175             and column_name = $3
3176         )' INTO proceed USING table_schema, table_name, sc;
3177         IF NOT proceed THEN
3178             RAISE EXCEPTION 'Missing column %', sc;
3179         END IF;
3180         EXECUTE 'SELECT EXISTS (
3181             SELECT 1
3182             FROM information_schema.columns
3183             WHERE table_schema = $1
3184             AND table_name = $2
3185             and column_name = $3
3186         )' INTO proceed USING table_schema, table_name, sce;
3187         IF NOT proceed THEN
3188             RAISE EXCEPTION 'Missing column %', sce;
3189         END IF;
3190
3191         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3192         IF org IS NULL THEN
3193             RAISE EXCEPTION 'Cannot find org by shortname';
3194         END IF;
3195
3196         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3197
3198         EXECUTE 'ALTER TABLE '
3199             || quote_ident(table_name)
3200             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3201         EXECUTE 'ALTER TABLE '
3202             || quote_ident(table_name)
3203             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3204         EXECUTE 'ALTER TABLE '
3205             || quote_ident(table_name)
3206             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3207         EXECUTE 'ALTER TABLE '
3208             || quote_ident(table_name)
3209             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3210
3211
3212         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3213             SET
3214                 x_sc' || field_suffix || ' = id
3215             FROM
3216                 (SELECT id, name, owner FROM actor_stat_cat
3217                     UNION SELECT id, name, owner FROM actor.stat_cat) u
3218             WHERE
3219                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3220                 AND u.owner = ANY ($1);'
3221         USING org_list;
3222
3223         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3224             SET
3225                 x_sce' || field_suffix || ' = id
3226             FROM
3227                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3228                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3229             WHERE
3230                     u.stat_cat = x_sc' || field_suffix || '
3231                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3232                 AND u.owner = ANY ($1);'
3233         USING org_list;
3234
3235         EXECUTE 'SELECT migration_tools.assert(
3236             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3237             ''Cannot find a desired stat cat'',
3238             ''Found all desired stat cats''
3239         );';
3240
3241         EXECUTE 'SELECT migration_tools.assert(
3242             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3243             ''Cannot find a desired stat cat entry'',
3244             ''Found all desired stat cat entries''
3245         );';
3246
3247     END;
3248 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3249
3250 -- convenience functions for adding shelving locations
3251 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3252 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3253 DECLARE
3254     return_id   INT;
3255     d           INT;
3256     cur_id      INT;
3257 BEGIN
3258     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3259     WHILE d >= 0
3260     LOOP
3261         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3262         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3263         IF return_id IS NOT NULL THEN
3264                 RETURN return_id;
3265         END IF;
3266         d := d - 1;
3267     END LOOP;
3268
3269     RETURN NULL;
3270 END
3271 $$ LANGUAGE plpgsql;
3272
3273 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3274
3275 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3276 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3277 DECLARE
3278     return_id   INT;
3279     d           INT;
3280     cur_id      INT;
3281 BEGIN
3282     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3283     WHILE d >= 0
3284     LOOP
3285         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3286
3287         SELECT INTO return_id id FROM
3288             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3289             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3290         IF return_id IS NOT NULL THEN
3291                 RETURN return_id;
3292         END IF;
3293         d := d - 1;
3294     END LOOP;
3295
3296     RETURN NULL;
3297 END
3298 $$ LANGUAGE plpgsql;
3299
3300 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
3301 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
3302 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
3303  RETURNS TEXT
3304  LANGUAGE plperlu
3305 AS $function$
3306 use strict;
3307 use warnings;
3308
3309 use MARC::Record;
3310 use MARC::File::XML (BinaryEncoding => 'utf8');
3311
3312 binmode(STDERR, ':bytes');
3313 binmode(STDOUT, ':utf8');
3314 binmode(STDERR, ':utf8');
3315
3316 my $marc_xml = shift;
3317 my $new_9_to_set = shift;
3318
3319 $marc_xml =~ s/(<leader>.........)./${1}a/;
3320
3321 eval {
3322     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3323 };
3324 if ($@) {
3325     #elog("could not parse $bibid: $@\n");
3326     import MARC::File::XML (BinaryEncoding => 'utf8');
3327     return $marc_xml;
3328 }
3329
3330 my @uris = $marc_xml->field('856');
3331 return $marc_xml->as_xml_record() unless @uris;
3332
3333 foreach my $field (@uris) {
3334     my $ind1 = $field->indicator('1');
3335     if (!defined $ind1) { next; }
3336     if ($ind1 ne '1' && $ind1 ne '4') { next; }
3337     my $ind2 = $field->indicator('2');
3338     if (!defined $ind2) { next; }
3339     if ($ind2 ne '0' && $ind2 ne '1') { next; }
3340     $field->add_subfields( '9' => $new_9_to_set );
3341 }
3342
3343 return $marc_xml->as_xml_record();
3344
3345 $function$;
3346
3347 -- yet another subfield 9 function, this one only adds the $9 and forces
3348 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
3349 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
3350 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
3351  RETURNS TEXT
3352  LANGUAGE plperlu
3353 AS $function$
3354 use strict;
3355 use warnings;
3356
3357 use MARC::Record;
3358 use MARC::File::XML (BinaryEncoding => 'utf8');
3359
3360 binmode(STDERR, ':bytes');
3361 binmode(STDOUT, ':utf8');
3362 binmode(STDERR, ':utf8');
3363
3364 my $marc_xml = shift;
3365 my $new_9_to_set = shift;
3366
3367 $marc_xml =~ s/(<leader>.........)./${1}a/;
3368
3369 eval {
3370     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3371 };
3372 if ($@) {
3373     #elog("could not parse $bibid: $@\n");
3374     import MARC::File::XML (BinaryEncoding => 'utf8');
3375     return $marc_xml;
3376 }
3377
3378 my @uris = $marc_xml->field('856');
3379 return $marc_xml->as_xml_record() unless @uris;
3380
3381 foreach my $field (@uris) {
3382     my $ind1 = $field->indicator('1');
3383     if (!defined $ind1) { next; }
3384     if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
3385     my $ind2 = $field->indicator('2');
3386     if (!defined $ind2) { next; }
3387     if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
3388     $field->add_subfields( '9' => $new_9_to_set );
3389 }
3390
3391 return $marc_xml->as_xml_record();
3392
3393 $function$;
3394
3395 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3396 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3397 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3398  RETURNS TEXT
3399  LANGUAGE plperlu
3400 AS $function$
3401 use strict;
3402 use warnings;
3403
3404 use MARC::Record;
3405 use MARC::File::XML (BinaryEncoding => 'utf8');
3406
3407 binmode(STDERR, ':bytes');
3408 binmode(STDOUT, ':utf8');
3409 binmode(STDERR, ':utf8');
3410
3411 my $marc_xml = shift;
3412 my $matching_u_text = shift;
3413 my $new_9_to_set = shift;
3414
3415 $marc_xml =~ s/(<leader>.........)./${1}a/;
3416
3417 eval {
3418     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3419 };
3420 if ($@) {
3421     #elog("could not parse $bibid: $@\n");
3422     import MARC::File::XML (BinaryEncoding => 'utf8');
3423     return;
3424 }
3425
3426 my @uris = $marc_xml->field('856');
3427 return unless @uris;
3428
3429 foreach my $field (@uris) {
3430     my $sfu = $field->subfield('u');
3431     my $ind2 = $field->indicator('2');
3432     if (!defined $ind2) { next; }
3433     if ($ind2 ne '0') { next; }
3434     if (!defined $sfu) { next; }
3435     if ($sfu =~ m/$matching_u_text/) {
3436         $field->add_subfields( '9' => $new_9_to_set );
3437         last;
3438     }
3439 }
3440
3441 return $marc_xml->as_xml_record();
3442
3443 $function$;
3444
3445 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3446 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3447     RETURNS BOOLEAN AS
3448 $BODY$
3449 DECLARE
3450     source_xml    TEXT;
3451     new_xml       TEXT;
3452     r             BOOLEAN;
3453 BEGIN
3454
3455     EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3456
3457     SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3458
3459     r = FALSE;
3460         new_xml = '$_$' || new_xml || '$_$';
3461
3462     IF new_xml != source_xml THEN
3463         EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3464         r = TRUE;
3465     END IF;
3466
3467     RETURN r;
3468
3469 END;
3470 $BODY$ LANGUAGE plpgsql;
3471
3472 -- convenience function for linking to the item staging table
3473
3474 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3475     DECLARE
3476         table_schema ALIAS FOR $1;
3477         table_name ALIAS FOR $2;
3478         foreign_column_name ALIAS FOR $3;
3479         main_column_name ALIAS FOR $4;
3480         btrim_desired ALIAS FOR $5;
3481         proceed BOOLEAN;
3482     BEGIN
3483         EXECUTE 'SELECT EXISTS (
3484             SELECT 1
3485             FROM information_schema.columns
3486             WHERE table_schema = $1
3487             AND table_name = $2
3488             and column_name = $3
3489         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3490         IF NOT proceed THEN
3491             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3492         END IF;
3493
3494         EXECUTE 'SELECT EXISTS (
3495             SELECT 1
3496             FROM information_schema.columns
3497             WHERE table_schema = $1
3498             AND table_name = ''asset_copy_legacy''
3499             and column_name = $2
3500         )' INTO proceed USING table_schema, main_column_name;
3501         IF NOT proceed THEN
3502             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3503         END IF;
3504
3505         EXECUTE 'ALTER TABLE '
3506             || quote_ident(table_name)
3507             || ' DROP COLUMN IF EXISTS x_item';
3508         EXECUTE 'ALTER TABLE '
3509             || quote_ident(table_name)
3510             || ' ADD COLUMN x_item BIGINT';
3511
3512         IF btrim_desired THEN
3513             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3514                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3515                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3516                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3517         ELSE
3518             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3519                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3520                 || ' WHERE a.' || quote_ident(foreign_column_name)
3521                 || ' = b.' || quote_ident(main_column_name);
3522         END IF;
3523
3524         --EXECUTE 'SELECT migration_tools.assert(
3525         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3526         --    ''Cannot link every barcode'',
3527         --    ''Every barcode linked''
3528         --);';
3529
3530     END;
3531 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3532
3533 -- convenience function for linking to the user staging table
3534
3535 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3536     DECLARE
3537         table_schema ALIAS FOR $1;
3538         table_name ALIAS FOR $2;
3539         foreign_column_name ALIAS FOR $3;
3540         main_column_name ALIAS FOR $4;
3541         btrim_desired ALIAS FOR $5;
3542         proceed BOOLEAN;
3543     BEGIN
3544         EXECUTE 'SELECT EXISTS (
3545             SELECT 1
3546             FROM information_schema.columns
3547             WHERE table_schema = $1
3548             AND table_name = $2
3549             and column_name = $3
3550         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3551         IF NOT proceed THEN
3552             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3553         END IF;
3554
3555         EXECUTE 'SELECT EXISTS (
3556             SELECT 1
3557             FROM information_schema.columns
3558             WHERE table_schema = $1
3559             AND table_name = ''actor_usr_legacy''
3560             and column_name = $2
3561         )' INTO proceed USING table_schema, main_column_name;
3562         IF NOT proceed THEN
3563             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3564         END IF;
3565
3566         EXECUTE 'ALTER TABLE '
3567             || quote_ident(table_name)
3568             || ' DROP COLUMN IF EXISTS x_user';
3569         EXECUTE 'ALTER TABLE '
3570             || quote_ident(table_name)
3571             || ' ADD COLUMN x_user INTEGER';
3572
3573         IF btrim_desired THEN
3574             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3575                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3576                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3577                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3578         ELSE
3579             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3580                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3581                 || ' WHERE a.' || quote_ident(foreign_column_name)
3582                 || ' = b.' || quote_ident(main_column_name);
3583         END IF;
3584
3585         --EXECUTE 'SELECT migration_tools.assert(
3586         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3587         --    ''Cannot link every barcode'',
3588         --    ''Every barcode linked''
3589         --);';
3590
3591     END;
3592 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3593
3594 -- convenience function for linking two tables
3595 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3596 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3597     DECLARE
3598         table_schema ALIAS FOR $1;
3599         table_a ALIAS FOR $2;
3600         column_a ALIAS FOR $3;
3601         table_b ALIAS FOR $4;
3602         column_b ALIAS FOR $5;
3603         column_x ALIAS FOR $6;
3604         btrim_desired ALIAS FOR $7;
3605         proceed BOOLEAN;
3606     BEGIN
3607         EXECUTE 'SELECT EXISTS (
3608             SELECT 1
3609             FROM information_schema.columns
3610             WHERE table_schema = $1
3611             AND table_name = $2
3612             and column_name = $3
3613         )' INTO proceed USING table_schema, table_a, column_a;
3614         IF NOT proceed THEN
3615             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3616         END IF;
3617
3618         EXECUTE 'SELECT EXISTS (
3619             SELECT 1
3620             FROM information_schema.columns
3621             WHERE table_schema = $1
3622             AND table_name = $2
3623             and column_name = $3
3624         )' INTO proceed USING table_schema, table_b, column_b;
3625         IF NOT proceed THEN
3626             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3627         END IF;
3628
3629         EXECUTE 'ALTER TABLE '
3630             || quote_ident(table_b)
3631             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3632         EXECUTE 'ALTER TABLE '
3633             || quote_ident(table_b)
3634             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3635
3636         IF btrim_desired THEN
3637             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3638                 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3639                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3640                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3641         ELSE
3642             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3643                 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3644                 || ' WHERE a.' || quote_ident(column_a)
3645                 || ' = b.' || quote_ident(column_b);
3646         END IF;
3647
3648     END;
3649 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3650
3651 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3652 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3653 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3654     DECLARE
3655         table_schema ALIAS FOR $1;
3656         table_a ALIAS FOR $2;
3657         column_a ALIAS FOR $3;
3658         table_b ALIAS FOR $4;
3659         column_b ALIAS FOR $5;
3660         column_w ALIAS FOR $6;
3661         column_x ALIAS FOR $7;
3662         btrim_desired ALIAS FOR $8;
3663         proceed BOOLEAN;
3664     BEGIN
3665         EXECUTE 'SELECT EXISTS (
3666             SELECT 1
3667             FROM information_schema.columns
3668             WHERE table_schema = $1
3669             AND table_name = $2
3670             and column_name = $3
3671         )' INTO proceed USING table_schema, table_a, column_a;
3672         IF NOT proceed THEN
3673             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3674         END IF;
3675
3676         EXECUTE 'SELECT EXISTS (
3677             SELECT 1
3678             FROM information_schema.columns
3679             WHERE table_schema = $1
3680             AND table_name = $2
3681             and column_name = $3
3682         )' INTO proceed USING table_schema, table_b, column_b;
3683         IF NOT proceed THEN
3684             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3685         END IF;
3686
3687         EXECUTE 'ALTER TABLE '
3688             || quote_ident(table_b)
3689             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3690         EXECUTE 'ALTER TABLE '
3691             || quote_ident(table_b)
3692             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3693
3694         IF btrim_desired THEN
3695             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3696                 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3697                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3698                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3699         ELSE
3700             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3701                 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3702                 || ' WHERE a.' || quote_ident(column_a)
3703                 || ' = b.' || quote_ident(column_b);
3704         END IF;
3705
3706     END;
3707 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3708
3709 -- convenience function for handling desired asset stat cats
3710
3711 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3712     DECLARE
3713         table_schema ALIAS FOR $1;
3714         table_name ALIAS FOR $2;
3715         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3716         org_shortname ALIAS FOR $4;
3717         proceed BOOLEAN;
3718         org INTEGER;
3719         org_list INTEGER[];
3720         sc TEXT;
3721         sce TEXT;
3722     BEGIN
3723
3724         SELECT 'desired_sc' || field_suffix INTO sc;
3725         SELECT 'desired_sce' || field_suffix INTO sce;
3726
3727         EXECUTE 'SELECT EXISTS (
3728             SELECT 1
3729             FROM information_schema.columns
3730             WHERE table_schema = $1
3731             AND table_name = $2
3732             and column_name = $3
3733         )' INTO proceed USING table_schema, table_name, sc;
3734         IF NOT proceed THEN
3735             RAISE EXCEPTION 'Missing column %', sc;
3736         END IF;
3737         EXECUTE 'SELECT EXISTS (
3738             SELECT 1
3739             FROM information_schema.columns
3740             WHERE table_schema = $1
3741             AND table_name = $2
3742             and column_name = $3
3743         )' INTO proceed USING table_schema, table_name, sce;
3744         IF NOT proceed THEN
3745             RAISE EXCEPTION 'Missing column %', sce;
3746         END IF;
3747
3748         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3749         IF org IS NULL THEN
3750             RAISE EXCEPTION 'Cannot find org by shortname';
3751         END IF;
3752         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3753
3754         -- caller responsible for their own truncates though we try to prevent duplicates
3755         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3756             SELECT DISTINCT
3757                  $1
3758                 ,BTRIM('||sc||')
3759             FROM
3760                 ' || quote_ident(table_name) || '
3761             WHERE
3762                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3763                 AND NOT EXISTS (
3764                     SELECT id
3765                     FROM asset.stat_cat
3766                     WHERE owner = ANY ($2)
3767                     AND name = BTRIM('||sc||')
3768                 )
3769                 AND NOT EXISTS (
3770                     SELECT id
3771                     FROM asset_stat_cat
3772                     WHERE owner = ANY ($2)
3773                     AND name = BTRIM('||sc||')
3774                 )
3775             ORDER BY 2;'
3776         USING org, org_list;
3777
3778         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3779             SELECT DISTINCT
3780                 COALESCE(
3781                     (SELECT id
3782                         FROM asset.stat_cat
3783                         WHERE owner = ANY ($2)
3784                         AND BTRIM('||sc||') = BTRIM(name))
3785                    ,(SELECT id
3786                         FROM asset_stat_cat
3787                         WHERE owner = ANY ($2)
3788                         AND BTRIM('||sc||') = BTRIM(name))
3789                 )
3790                 ,$1
3791                 ,BTRIM('||sce||')
3792             FROM
3793                 ' || quote_ident(table_name) || '
3794             WHERE
3795                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3796                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3797                 AND NOT EXISTS (
3798                     SELECT id
3799                     FROM asset.stat_cat_entry
3800                     WHERE stat_cat = (
3801                         SELECT id
3802                         FROM asset.stat_cat
3803                         WHERE owner = ANY ($2)
3804                         AND BTRIM('||sc||') = BTRIM(name)
3805                     ) AND value = BTRIM('||sce||')
3806                 )
3807                 AND NOT EXISTS (
3808                     SELECT id
3809                     FROM asset_stat_cat_entry
3810                     WHERE stat_cat = (
3811                         SELECT id
3812                         FROM asset_stat_cat
3813                         WHERE owner = ANY ($2)
3814                         AND BTRIM('||sc||') = BTRIM(name)
3815                     ) AND value = BTRIM('||sce||')
3816                 )
3817             ORDER BY 1,3;'
3818         USING org, org_list;
3819     END;
3820 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3821
3822 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3823     DECLARE
3824         table_schema ALIAS FOR $1;
3825         table_name ALIAS FOR $2;
3826         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3827         org_shortname ALIAS FOR $4;
3828         proceed BOOLEAN;
3829         org INTEGER;
3830         org_list INTEGER[];
3831         o INTEGER;
3832         sc TEXT;
3833         sce TEXT;
3834     BEGIN
3835         SELECT 'desired_sc' || field_suffix INTO sc;
3836         SELECT 'desired_sce' || field_suffix INTO sce;
3837         EXECUTE 'SELECT EXISTS (
3838             SELECT 1
3839             FROM information_schema.columns
3840             WHERE table_schema = $1
3841             AND table_name = $2
3842             and column_name = $3
3843         )' INTO proceed USING table_schema, table_name, sc;
3844         IF NOT proceed THEN
3845             RAISE EXCEPTION 'Missing column %', sc;
3846         END IF;
3847         EXECUTE 'SELECT EXISTS (
3848             SELECT 1
3849             FROM information_schema.columns
3850             WHERE table_schema = $1
3851             AND table_name = $2
3852             and column_name = $3
3853         )' INTO proceed USING table_schema, table_name, sce;
3854         IF NOT proceed THEN
3855             RAISE EXCEPTION 'Missing column %', sce;
3856         END IF;
3857
3858         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3859         IF org IS NULL THEN
3860             RAISE EXCEPTION 'Cannot find org by shortname';
3861         END IF;
3862
3863         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3864
3865         EXECUTE 'ALTER TABLE '
3866             || quote_ident(table_name)
3867             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3868         EXECUTE 'ALTER TABLE '
3869             || quote_ident(table_name)
3870             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3871         EXECUTE 'ALTER TABLE '
3872             || quote_ident(table_name)
3873             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3874         EXECUTE 'ALTER TABLE '
3875             || quote_ident(table_name)
3876             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3877
3878
3879         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3880             SET
3881                 x_sc' || field_suffix || ' = id
3882             FROM
3883                 (SELECT id, name, owner FROM asset_stat_cat
3884                     UNION SELECT id, name, owner FROM asset.stat_cat) u
3885             WHERE
3886                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3887                 AND u.owner = ANY ($1);'
3888         USING org_list;
3889
3890         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3891             SET
3892                 x_sce' || field_suffix || ' = id
3893             FROM
3894                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3895                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3896             WHERE
3897                     u.stat_cat = x_sc' || field_suffix || '
3898                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3899                 AND u.owner = ANY ($1);'
3900         USING org_list;
3901
3902         EXECUTE 'SELECT migration_tools.assert(
3903             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3904             ''Cannot find a desired stat cat'',
3905             ''Found all desired stat cats''
3906         );';
3907
3908         EXECUTE 'SELECT migration_tools.assert(
3909             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3910             ''Cannot find a desired stat cat entry'',
3911             ''Found all desired stat cat entries''
3912         );';
3913
3914     END;
3915 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3916
3917 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
3918 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3919  LANGUAGE plpgsql
3920 AS $function$
3921 DECLARE
3922     c_name     TEXT;
3923 BEGIN
3924
3925     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3926             table_name = t_name
3927             AND table_schema = s_name
3928             AND (data_type='text' OR data_type='character varying')
3929             AND column_name like 'l_%'
3930     LOOP
3931        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3932     END LOOP;
3933
3934     RETURN TRUE;
3935 END
3936 $function$;
3937
3938 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
3939 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3940  LANGUAGE plpgsql
3941 AS $function$
3942 DECLARE
3943     c_name     TEXT;
3944 BEGIN
3945
3946     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3947             table_name = t_name
3948             AND table_schema = s_name
3949             AND (data_type='text' OR data_type='character varying')
3950             AND column_name like 'l_%'
3951     LOOP
3952        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3953     END LOOP;
3954
3955     RETURN TRUE;
3956 END
3957 $function$;
3958