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     } elsif ($difference == 16) { $checkdigit = 'A';
1137     } elsif ($difference == 17) { $checkdigit = 'B';
1138     } elsif ($difference == 18) { $checkdigit = 'C';
1139     } elsif ($difference == 19) { $checkdigit = 'D';
1140     } else { die "error calculating checkdigit";
1141     }
1142
1143     return $barcode . $checkdigit;
1144 $$ LANGUAGE PLPERLU STRICT STABLE;
1145
1146 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1147   DECLARE
1148     phone TEXT := $1;
1149     areacode TEXT := $2;
1150     temp TEXT := '';
1151     output TEXT := '';
1152     n_digits INTEGER := 0;
1153   BEGIN
1154     temp := phone;
1155     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1156     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1157     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1158     IF n_digits = 7 AND areacode <> '' THEN
1159       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1160       output := (areacode || '-' || temp);
1161     ELSE
1162       output := temp;
1163     END IF;
1164     RETURN output;
1165   END;
1166
1167 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1168
1169 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1170   my ($marcxml, $pos, $value) = @_;
1171
1172   use MARC::Record;
1173   use MARC::File::XML;
1174
1175   my $xml = $marcxml;
1176   eval {
1177     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1178     my $leader = $marc->leader();
1179     substr($leader, $pos, 1) = $value;
1180     $marc->leader($leader);
1181     $xml = $marc->as_xml_record;
1182     $xml =~ s/^<\?.+?\?>$//mo;
1183     $xml =~ s/\n//sgo;
1184     $xml =~ s/>\s+</></sgo;
1185   };
1186   return $xml;
1187 $$ LANGUAGE PLPERLU STABLE;
1188
1189 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1190   my ($marcxml, $pos, $value) = @_;
1191
1192   use MARC::Record;
1193   use MARC::File::XML;
1194
1195   my $xml = $marcxml;
1196   eval {
1197     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1198     my $f008 = $marc->field('008');
1199
1200     if ($f008) {
1201        my $field = $f008->data();
1202        substr($field, $pos, 1) = $value;
1203        $f008->update($field);
1204        $xml = $marc->as_xml_record;
1205        $xml =~ s/^<\?.+?\?>$//mo;
1206        $xml =~ s/\n//sgo;
1207        $xml =~ s/>\s+</></sgo;
1208     }
1209   };
1210   return $xml;
1211 $$ LANGUAGE PLPERLU STABLE;
1212
1213
1214 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1215   DECLARE
1216     profile ALIAS FOR $1;
1217   BEGIN
1218     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1219   END;
1220 $$ LANGUAGE PLPGSQL STRICT STABLE;
1221
1222
1223 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1224   BEGIN
1225     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1226   END;
1227 $$ LANGUAGE PLPGSQL STRICT STABLE;
1228
1229
1230 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1231
1232   my ($marcxml, $tags) = @_;
1233
1234   use MARC::Record;
1235   use MARC::File::XML;
1236
1237   my $xml = $marcxml;
1238
1239   eval {
1240     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1241     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1242
1243     my @incumbents = ();
1244
1245     foreach my $field ( $marc->fields() ) {
1246       push @incumbents, $field->as_formatted();
1247     }
1248
1249     foreach $field ( $to_insert->fields() ) {
1250       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1251         $marc->insert_fields_ordered( ($field) );
1252       }
1253     }
1254
1255     $xml = $marc->as_xml_record;
1256     $xml =~ s/^<\?.+?\?>$//mo;
1257     $xml =~ s/\n//sgo;
1258     $xml =~ s/>\s+</></sgo;
1259   };
1260
1261   return $xml;
1262
1263 $$ LANGUAGE PLPERLU STABLE;
1264
1265 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1266
1267 -- Usage:
1268 --
1269 --   First make sure the circ matrix is loaded and the circulations
1270 --   have been staged to the extent possible (but at the very least
1271 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1272 --   circ modifiers must also be in place.
1273 --
1274 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1275 --
1276
1277 DECLARE
1278   circ_lib             INT;
1279   target_copy          INT;
1280   usr                  INT;
1281   is_renewal           BOOLEAN;
1282   this_duration_rule   INT;
1283   this_fine_rule       INT;
1284   this_max_fine_rule   INT;
1285   rcd                  config.rule_circ_duration%ROWTYPE;
1286   rrf                  config.rule_recurring_fine%ROWTYPE;
1287   rmf                  config.rule_max_fine%ROWTYPE;
1288   circ                 INT;
1289   n                    INT := 0;
1290   n_circs              INT;
1291
1292 BEGIN
1293
1294   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1295
1296   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1297
1298     -- Fetch the correct rules for this circulation
1299     EXECUTE ('
1300       SELECT
1301         circ_lib,
1302         target_copy,
1303         usr,
1304         CASE
1305           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1306           ELSE FALSE
1307         END
1308       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1309       INTO circ_lib, target_copy, usr, is_renewal ;
1310     SELECT
1311       INTO this_duration_rule,
1312            this_fine_rule,
1313            this_max_fine_rule
1314       duration_rule,
1315       recurring_fine_rule,
1316       max_fine_rule
1317       FROM action.item_user_circ_test(
1318         circ_lib,
1319         target_copy,
1320         usr,
1321         is_renewal
1322         );
1323     SELECT INTO rcd * FROM config.rule_circ_duration
1324       WHERE id = this_duration_rule;
1325     SELECT INTO rrf * FROM config.rule_recurring_fine
1326       WHERE id = this_fine_rule;
1327     SELECT INTO rmf * FROM config.rule_max_fine
1328       WHERE id = this_max_fine_rule;
1329
1330     -- Apply the rules to this circulation
1331     EXECUTE ('UPDATE ' || tablename || ' c
1332     SET
1333       duration_rule = rcd.name,
1334       recurring_fine_rule = rrf.name,
1335       max_fine_rule = rmf.name,
1336       duration = rcd.normal,
1337       recurring_fine = rrf.normal,
1338       max_fine =
1339         CASE rmf.is_percent
1340           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1341           ELSE rmf.amount
1342         END,
1343       renewal_remaining = rcd.max_renewals
1344     FROM
1345       config.rule_circ_duration rcd,
1346       config.rule_recurring_fine rrf,
1347       config.rule_max_fine rmf,
1348                         asset.copy ac
1349     WHERE
1350       rcd.id = ' || this_duration_rule || ' AND
1351       rrf.id = ' || this_fine_rule || ' AND
1352       rmf.id = ' || this_max_fine_rule || ' AND
1353                         ac.id = c.target_copy AND
1354       c.id = ' || circ || ';');
1355
1356     -- Keep track of where we are in the process
1357     n := n + 1;
1358     IF (n % 100 = 0) THEN
1359       RAISE INFO '%', n || ' of ' || n_circs
1360         || ' (' || (100*n/n_circs) || '%) circs updated.';
1361     END IF;
1362
1363   END LOOP;
1364
1365   RETURN;
1366 END;
1367
1368 $$ LANGUAGE plpgsql;
1369
1370 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1371
1372 -- Usage:
1373 --
1374 --   First make sure the circ matrix is loaded and the circulations
1375 --   have been staged to the extent possible (but at the very least
1376 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1377 --   circ modifiers must also be in place.
1378 --
1379 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1380 --
1381
1382 DECLARE
1383   circ_lib             INT;
1384   target_copy          INT;
1385   usr                  INT;
1386   is_renewal           BOOLEAN;
1387   this_duration_rule   INT;
1388   this_fine_rule       INT;
1389   this_max_fine_rule   INT;
1390   rcd                  config.rule_circ_duration%ROWTYPE;
1391   rrf                  config.rule_recurring_fine%ROWTYPE;
1392   rmf                  config.rule_max_fine%ROWTYPE;
1393   circ                 INT;
1394   n                    INT := 0;
1395   n_circs              INT;
1396
1397 BEGIN
1398
1399   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1400
1401   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1402
1403     -- Fetch the correct rules for this circulation
1404     EXECUTE ('
1405       SELECT
1406         circ_lib,
1407         target_copy,
1408         usr,
1409         CASE
1410           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1411           ELSE FALSE
1412         END
1413       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1414       INTO circ_lib, target_copy, usr, is_renewal ;
1415     SELECT
1416       INTO this_duration_rule,
1417            this_fine_rule,
1418            this_max_fine_rule
1419       duration_rule,
1420       recuring_fine_rule,
1421       max_fine_rule
1422       FROM action.find_circ_matrix_matchpoint(
1423         circ_lib,
1424         target_copy,
1425         usr,
1426         is_renewal
1427         );
1428     SELECT INTO rcd * FROM config.rule_circ_duration
1429       WHERE id = this_duration_rule;
1430     SELECT INTO rrf * FROM config.rule_recurring_fine
1431       WHERE id = this_fine_rule;
1432     SELECT INTO rmf * FROM config.rule_max_fine
1433       WHERE id = this_max_fine_rule;
1434
1435     -- Apply the rules to this circulation
1436     EXECUTE ('UPDATE ' || tablename || ' c
1437     SET
1438       duration_rule = rcd.name,
1439       recuring_fine_rule = rrf.name,
1440       max_fine_rule = rmf.name,
1441       duration = rcd.normal,
1442       recuring_fine = rrf.normal,
1443       max_fine =
1444         CASE rmf.is_percent
1445           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1446           ELSE rmf.amount
1447         END,
1448       renewal_remaining = rcd.max_renewals
1449     FROM
1450       config.rule_circ_duration rcd,
1451       config.rule_recuring_fine rrf,
1452       config.rule_max_fine rmf,
1453                         asset.copy ac
1454     WHERE
1455       rcd.id = ' || this_duration_rule || ' AND
1456       rrf.id = ' || this_fine_rule || ' AND
1457       rmf.id = ' || this_max_fine_rule || ' AND
1458                         ac.id = c.target_copy AND
1459       c.id = ' || circ || ';');
1460
1461     -- Keep track of where we are in the process
1462     n := n + 1;
1463     IF (n % 100 = 0) THEN
1464       RAISE INFO '%', n || ' of ' || n_circs
1465         || ' (' || (100*n/n_circs) || '%) circs updated.';
1466     END IF;
1467
1468   END LOOP;
1469
1470   RETURN;
1471 END;
1472
1473 $$ LANGUAGE plpgsql;
1474
1475 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1476
1477 -- Usage:
1478 --
1479 --   First make sure the circ matrix is loaded and the circulations
1480 --   have been staged to the extent possible (but at the very least
1481 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1482 --   circ modifiers must also be in place.
1483 --
1484 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1485 --
1486
1487 DECLARE
1488   circ_lib             INT;
1489   target_copy          INT;
1490   usr                  INT;
1491   is_renewal           BOOLEAN;
1492   this_duration_rule   INT;
1493   this_fine_rule       INT;
1494   this_max_fine_rule   INT;
1495   rcd                  config.rule_circ_duration%ROWTYPE;
1496   rrf                  config.rule_recurring_fine%ROWTYPE;
1497   rmf                  config.rule_max_fine%ROWTYPE;
1498   circ                 INT;
1499   n                    INT := 0;
1500   n_circs              INT;
1501
1502 BEGIN
1503
1504   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1505
1506   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1507
1508     -- Fetch the correct rules for this circulation
1509     EXECUTE ('
1510       SELECT
1511         circ_lib,
1512         target_copy,
1513         usr,
1514         CASE
1515           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1516           ELSE FALSE
1517         END
1518       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1519       INTO circ_lib, target_copy, usr, is_renewal ;
1520     SELECT
1521       INTO this_duration_rule,
1522            this_fine_rule,
1523            this_max_fine_rule
1524       (matchpoint).duration_rule,
1525       (matchpoint).recurring_fine_rule,
1526       (matchpoint).max_fine_rule
1527       FROM action.find_circ_matrix_matchpoint(
1528         circ_lib,
1529         target_copy,
1530         usr,
1531         is_renewal
1532         );
1533     SELECT INTO rcd * FROM config.rule_circ_duration
1534       WHERE id = this_duration_rule;
1535     SELECT INTO rrf * FROM config.rule_recurring_fine
1536       WHERE id = this_fine_rule;
1537     SELECT INTO rmf * FROM config.rule_max_fine
1538       WHERE id = this_max_fine_rule;
1539
1540     -- Apply the rules to this circulation
1541     EXECUTE ('UPDATE ' || tablename || ' c
1542     SET
1543       duration_rule = rcd.name,
1544       recurring_fine_rule = rrf.name,
1545       max_fine_rule = rmf.name,
1546       duration = rcd.normal,
1547       recurring_fine = rrf.normal,
1548       max_fine =
1549         CASE rmf.is_percent
1550           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1551           ELSE rmf.amount
1552         END,
1553       renewal_remaining = rcd.max_renewals,
1554       grace_period = rrf.grace_period
1555     FROM
1556       config.rule_circ_duration rcd,
1557       config.rule_recurring_fine rrf,
1558       config.rule_max_fine rmf,
1559                         asset.copy ac
1560     WHERE
1561       rcd.id = ' || this_duration_rule || ' AND
1562       rrf.id = ' || this_fine_rule || ' AND
1563       rmf.id = ' || this_max_fine_rule || ' AND
1564                         ac.id = c.target_copy AND
1565       c.id = ' || circ || ';');
1566
1567     -- Keep track of where we are in the process
1568     n := n + 1;
1569     IF (n % 100 = 0) THEN
1570       RAISE INFO '%', n || ' of ' || n_circs
1571         || ' (' || (100*n/n_circs) || '%) circs updated.';
1572     END IF;
1573
1574   END LOOP;
1575
1576   RETURN;
1577 END;
1578
1579 $$ LANGUAGE plpgsql;
1580
1581 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1582
1583 -- Usage:
1584 --
1585 --   First make sure the circ matrix is loaded and the circulations
1586 --   have been staged to the extent possible (but at the very least
1587 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1588 --   circ modifiers must also be in place.
1589 --
1590 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1591 --
1592
1593 DECLARE
1594   circ_lib             INT;
1595   target_copy          INT;
1596   usr                  INT;
1597   is_renewal           BOOLEAN;
1598   this_duration_rule   INT;
1599   this_fine_rule       INT;
1600   this_max_fine_rule   INT;
1601   rcd                  config.rule_circ_duration%ROWTYPE;
1602   rrf                  config.rule_recurring_fine%ROWTYPE;
1603   rmf                  config.rule_max_fine%ROWTYPE;
1604   n                    INT := 0;
1605   n_circs              INT := 1;
1606
1607 BEGIN
1608
1609   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1610
1611   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1612
1613     -- Fetch the correct rules for this circulation
1614     EXECUTE ('
1615       SELECT
1616         circ_lib,
1617         target_copy,
1618         usr,
1619         CASE
1620           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1621           ELSE FALSE
1622         END
1623       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1624       INTO circ_lib, target_copy, usr, is_renewal ;
1625     SELECT
1626       INTO this_duration_rule,
1627            this_fine_rule,
1628            this_max_fine_rule
1629       (matchpoint).duration_rule,
1630       (matchpoint).recurring_fine_rule,
1631       (matchpoint).max_fine_rule
1632       FROM action.find_circ_matrix_matchpoint(
1633         circ_lib,
1634         target_copy,
1635         usr,
1636         is_renewal
1637         );
1638     SELECT INTO rcd * FROM config.rule_circ_duration
1639       WHERE id = this_duration_rule;
1640     SELECT INTO rrf * FROM config.rule_recurring_fine
1641       WHERE id = this_fine_rule;
1642     SELECT INTO rmf * FROM config.rule_max_fine
1643       WHERE id = this_max_fine_rule;
1644
1645     -- Apply the rules to this circulation
1646     EXECUTE ('UPDATE ' || tablename || ' c
1647     SET
1648       duration_rule = rcd.name,
1649       recurring_fine_rule = rrf.name,
1650       max_fine_rule = rmf.name,
1651       duration = rcd.normal,
1652       recurring_fine = rrf.normal,
1653       max_fine =
1654         CASE rmf.is_percent
1655           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1656           ELSE rmf.amount
1657         END,
1658       renewal_remaining = rcd.max_renewals,
1659       grace_period = rrf.grace_period
1660     FROM
1661       config.rule_circ_duration rcd,
1662       config.rule_recurring_fine rrf,
1663       config.rule_max_fine rmf,
1664                         asset.copy ac
1665     WHERE
1666       rcd.id = ' || this_duration_rule || ' AND
1667       rrf.id = ' || this_fine_rule || ' AND
1668       rmf.id = ' || this_max_fine_rule || ' AND
1669                         ac.id = c.target_copy AND
1670       c.id = ' || circ || ';');
1671
1672     -- Keep track of where we are in the process
1673     n := n + 1;
1674     IF (n % 100 = 0) THEN
1675       RAISE INFO '%', n || ' of ' || n_circs
1676         || ' (' || (100*n/n_circs) || '%) circs updated.';
1677     END IF;
1678
1679   --END LOOP;
1680
1681   RETURN;
1682 END;
1683
1684 $$ LANGUAGE plpgsql;
1685
1686
1687
1688
1689 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1690
1691 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1692 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1693
1694 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1695 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1696
1697 DECLARE
1698         c                    TEXT := schemaname || '.asset_copy_legacy';
1699         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1700         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1701         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1702         stat_cat                                                 INT;
1703   stat_cat_entry       INT;
1704
1705 BEGIN
1706
1707   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1708
1709                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1710
1711                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1712                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1713                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1714
1715   END LOOP;
1716
1717   RETURN;
1718 END;
1719
1720 $$ LANGUAGE plpgsql;
1721
1722 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1723
1724 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1725 --        This will assign standing penalties as needed.
1726
1727 DECLARE
1728   org_unit  INT;
1729   usr       INT;
1730
1731 BEGIN
1732
1733   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1734
1735     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1736
1737       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1738
1739     END LOOP;
1740
1741   END LOOP;
1742
1743   RETURN;
1744
1745 END;
1746
1747 $$ LANGUAGE plpgsql;
1748
1749
1750 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1751
1752 BEGIN
1753   INSERT INTO metabib.metarecord (fingerprint, master_record)
1754     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1755       FROM  biblio.record_entry b
1756       WHERE NOT b.deleted
1757         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)
1758         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1759       ORDER BY b.fingerprint, b.quality DESC;
1760   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1761     SELECT  m.id, r.id
1762       FROM  biblio.record_entry r
1763       JOIN  metabib.metarecord m USING (fingerprint)
1764      WHERE  NOT r.deleted;
1765 END;
1766
1767 $$ LANGUAGE plpgsql;
1768
1769
1770 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1771
1772 BEGIN
1773   INSERT INTO metabib.metarecord (fingerprint, master_record)
1774     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1775       FROM  biblio.record_entry b
1776       WHERE NOT b.deleted
1777         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)
1778         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1779       ORDER BY b.fingerprint, b.quality DESC;
1780   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1781     SELECT  m.id, r.id
1782       FROM  biblio.record_entry r
1783         JOIN metabib.metarecord m USING (fingerprint)
1784       WHERE NOT r.deleted
1785         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);
1786 END;
1787
1788 $$ LANGUAGE plpgsql;
1789
1790
1791 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1792
1793 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1794 --        Then SELECT migration_tools.create_cards('m_foo');
1795
1796 DECLARE
1797         u                    TEXT := schemaname || '.actor_usr_legacy';
1798         c                    TEXT := schemaname || '.actor_card';
1799
1800 BEGIN
1801
1802         EXECUTE ('DELETE FROM ' || c || ';');
1803         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1804         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1805
1806   RETURN;
1807
1808 END;
1809
1810 $$ LANGUAGE plpgsql;
1811
1812
1813 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1814
1815   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1816
1817   my ($marcxml, $shortname) = @_;
1818
1819   use MARC::Record;
1820   use MARC::File::XML;
1821
1822   my $xml = $marcxml;
1823
1824   eval {
1825     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1826
1827     foreach my $field ( $marc->field('856') ) {
1828       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1829            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1830         $field->add_subfields( '9' => $shortname );
1831                                 $field->update( ind2 => '0');
1832       }
1833     }
1834
1835     $xml = $marc->as_xml_record;
1836     $xml =~ s/^<\?.+?\?>$//mo;
1837     $xml =~ s/\n//sgo;
1838     $xml =~ s/>\s+</></sgo;
1839   };
1840
1841   return $xml;
1842
1843 $$ LANGUAGE PLPERLU STABLE;
1844
1845 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1846
1847   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1848
1849   my ($marcxml, $shortname) = @_;
1850
1851   use MARC::Record;
1852   use MARC::File::XML;
1853
1854   my $xml = $marcxml;
1855
1856   eval {
1857     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1858
1859     foreach my $field ( $marc->field('856') ) {
1860       if ( ! $field->as_string('9') ) {
1861         $field->add_subfields( '9' => $shortname );
1862       }
1863     }
1864
1865     $xml = $marc->as_xml_record;
1866     $xml =~ s/^<\?.+?\?>$//mo;
1867     $xml =~ s/\n//sgo;
1868     $xml =~ s/>\s+</></sgo;
1869   };
1870
1871   return $xml;
1872
1873 $$ LANGUAGE PLPERLU STABLE;
1874
1875
1876 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1877
1878 DECLARE
1879   old_volume   BIGINT;
1880   new_volume   BIGINT;
1881   bib          BIGINT;
1882   owner        INTEGER;
1883   old_label    TEXT;
1884   remainder    BIGINT;
1885
1886 BEGIN
1887
1888   -- Bail out if asked to change the label to ##URI##
1889   IF new_label = '##URI##' THEN
1890     RETURN;
1891   END IF;
1892
1893   -- Gather information
1894   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1895   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1896   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1897
1898   -- Bail out if the label already is ##URI##
1899   IF old_label = '##URI##' THEN
1900     RETURN;
1901   END IF;
1902
1903   -- Bail out if the call number label is already correct
1904   IF new_volume = old_volume THEN
1905     RETURN;
1906   END IF;
1907
1908   -- Check whether we already have a destination volume available
1909   SELECT id INTO new_volume FROM asset.call_number
1910     WHERE
1911       record = bib AND
1912       owning_lib = owner AND
1913       label = new_label AND
1914       NOT deleted;
1915
1916   -- Create destination volume if needed
1917   IF NOT FOUND THEN
1918     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1919       VALUES (1, 1, bib, owner, new_label, cn_class);
1920     SELECT id INTO new_volume FROM asset.call_number
1921       WHERE
1922         record = bib AND
1923         owning_lib = owner AND
1924         label = new_label AND
1925         NOT deleted;
1926   END IF;
1927
1928   -- Move copy to destination
1929   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1930
1931   -- Delete source volume if it is now empty
1932   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1933   IF NOT FOUND THEN
1934     DELETE FROM asset.call_number WHERE id = old_volume;
1935   END IF;
1936
1937 END;
1938
1939 $$ LANGUAGE plpgsql;
1940
1941 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1942
1943         my $input = $_[0];
1944         my %zipdata;
1945
1946         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1947
1948         while (<FH>) {
1949                 chomp;
1950                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1951                 $zipdata{$zip} = [$city, $state, $county];
1952         }
1953
1954         if (defined $zipdata{$input}) {
1955                 my ($city, $state, $county) = @{$zipdata{$input}};
1956                 return [$city, $state, $county];
1957         } elsif (defined $zipdata{substr $input, 0, 5}) {
1958                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1959                 return [$city, $state, $county];
1960         } else {
1961                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1962         }
1963
1964 $$ LANGUAGE PLPERLU STABLE;
1965
1966 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1967
1968 DECLARE
1969   ou  INT;
1970         org_unit_depth INT;
1971         ou_parent INT;
1972         parent_depth INT;
1973   errors_found BOOLEAN;
1974         ou_shortname TEXT;
1975         parent_shortname TEXT;
1976         ou_type_name TEXT;
1977         parent_type TEXT;
1978         type_id INT;
1979         type_depth INT;
1980         type_parent INT;
1981         type_parent_depth INT;
1982         proper_parent TEXT;
1983
1984 BEGIN
1985
1986         errors_found := FALSE;
1987
1988 -- Checking actor.org_unit_type
1989
1990         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1991
1992                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1993                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1994
1995                 IF type_parent IS NOT NULL THEN
1996
1997                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1998
1999                         IF type_depth - type_parent_depth <> 1 THEN
2000                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2001                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2002                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2003                                         ou_type_name, type_depth, parent_type, type_parent_depth;
2004                                 errors_found := TRUE;
2005
2006                         END IF;
2007
2008                 END IF;
2009
2010         END LOOP;
2011
2012 -- Checking actor.org_unit
2013
2014   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2015
2016                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2017                 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;
2018                 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;
2019                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2020                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2021                 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;
2022                 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;
2023
2024                 IF ou_parent IS NOT NULL THEN
2025
2026                         IF      (org_unit_depth - parent_depth <> 1) OR (
2027                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2028                         ) THEN
2029                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2030                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2031                                 errors_found := TRUE;
2032                         END IF;
2033
2034                 END IF;
2035
2036   END LOOP;
2037
2038         IF NOT errors_found THEN
2039                 RAISE INFO 'No errors found.';
2040         END IF;
2041
2042   RETURN;
2043
2044 END;
2045
2046 $$ LANGUAGE plpgsql;
2047
2048
2049 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2050
2051 BEGIN
2052
2053         DELETE FROM asset.opac_visible_copies;
2054
2055         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2056                 SELECT DISTINCT
2057                         cp.id, cp.circ_lib, cn.record
2058                 FROM
2059                         asset.copy cp
2060                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
2061                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2062                         JOIN asset.copy_location cl ON (cp.location = cl.id)
2063                         JOIN config.copy_status cs ON (cp.status = cs.id)
2064                         JOIN biblio.record_entry b ON (cn.record = b.id)
2065                 WHERE
2066                         NOT cp.deleted AND
2067                         NOT cn.deleted AND
2068                         NOT b.deleted AND
2069                         cs.opac_visible AND
2070                         cl.opac_visible AND
2071                         cp.opac_visible AND
2072                         a.opac_visible AND
2073                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2074
2075 END;
2076
2077 $$ LANGUAGE plpgsql;
2078
2079
2080 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2081
2082 DECLARE
2083   old_volume     BIGINT;
2084   new_volume     BIGINT;
2085   bib            BIGINT;
2086   old_owning_lib INTEGER;
2087         old_label      TEXT;
2088   remainder      BIGINT;
2089
2090 BEGIN
2091
2092   -- Gather information
2093   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2094   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2095   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2096
2097         -- Bail out if the new_owning_lib is not the ID of an org_unit
2098         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2099                 RAISE WARNING
2100                         '% is not a valid actor.org_unit ID; no change made.',
2101                                 new_owning_lib;
2102                 RETURN;
2103         END IF;
2104
2105   -- Bail out discreetly if the owning_lib is already correct
2106   IF new_owning_lib = old_owning_lib THEN
2107     RETURN;
2108   END IF;
2109
2110   -- Check whether we already have a destination volume available
2111   SELECT id INTO new_volume FROM asset.call_number
2112     WHERE
2113       record = bib AND
2114       owning_lib = new_owning_lib AND
2115       label = old_label AND
2116       NOT deleted;
2117
2118   -- Create destination volume if needed
2119   IF NOT FOUND THEN
2120     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2121       VALUES (1, 1, bib, new_owning_lib, old_label);
2122     SELECT id INTO new_volume FROM asset.call_number
2123       WHERE
2124         record = bib AND
2125         owning_lib = new_owning_lib AND
2126         label = old_label AND
2127         NOT deleted;
2128   END IF;
2129
2130   -- Move copy to destination
2131   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2132
2133   -- Delete source volume if it is now empty
2134   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2135   IF NOT FOUND THEN
2136     DELETE FROM asset.call_number WHERE id = old_volume;
2137   END IF;
2138
2139 END;
2140
2141 $$ LANGUAGE plpgsql;
2142
2143
2144 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2145
2146 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2147
2148 DECLARE
2149         new_owning_lib  INTEGER;
2150
2151 BEGIN
2152
2153         -- Parse the new_owner as an org unit ID or shortname
2154         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2155                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2156                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2157         ELSIF new_owner ~ E'^[0-9]+$' THEN
2158                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2159                         RAISE INFO
2160                                 '%',
2161                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2162                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2163                         new_owning_lib := new_owner::INTEGER;
2164                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2165                 END IF;
2166         ELSE
2167                 RAISE WARNING
2168                         '% is not a valid actor.org_unit shortname or ID; no change made.',
2169                         new_owning_lib;
2170                 RETURN;
2171         END IF;
2172
2173 END;
2174
2175 $$ LANGUAGE plpgsql;
2176
2177 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2178
2179 use MARC::Record;
2180 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2181 use MARC::Charset;
2182
2183 MARC::Charset->assume_unicode(1);
2184
2185 my $xml = shift;
2186
2187 eval {
2188     my $r = MARC::Record->new_from_xml( $xml );
2189     my $output_xml = $r->as_xml_record();
2190 };
2191 if ($@) {
2192     return 0;
2193 } else {
2194     return 1;
2195 }
2196
2197 $func$ LANGUAGE PLPERLU;
2198 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2199
2200 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2201 BEGIN
2202    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2203            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2204            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2205    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2206            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2207            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2208    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2209            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2210            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2211    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2212            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2213            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2214    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2215            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2216            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2217    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2218            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2219            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2220    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2221            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2222            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2223    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2224    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2225    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2226    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2227    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2228 END;
2229 $FUNC$ LANGUAGE PLPGSQL;
2230
2231 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2232 BEGIN
2233    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2234    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2235    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2236    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2237    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2238    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2239    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2240
2241    -- import any new circ rules
2242    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2243    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2244    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2245    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2246
2247    -- and permission groups
2248    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2249
2250 END;
2251 $FUNC$ LANGUAGE PLPGSQL;
2252
2253
2254 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$
2255 DECLARE
2256     name TEXT;
2257     loopq TEXT;
2258     existsq TEXT;
2259     ct INTEGER;
2260     cols TEXT[];
2261     copyst TEXT;
2262 BEGIN
2263     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2264     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2265     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2266     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2267     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2268     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2269     FOR name IN EXECUTE loopq LOOP
2270        EXECUTE existsq INTO ct USING name;
2271        IF ct = 0 THEN
2272            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2273            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2274                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2275            EXECUTE copyst USING name;
2276        END IF;
2277     END LOOP;
2278 END;
2279 $FUNC$ LANGUAGE PLPGSQL;
2280
2281 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2282
2283 use strict;
2284 use warnings;
2285
2286 use MARC::Record;
2287 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2288 use MARC::Charset;
2289
2290 MARC::Charset->assume_unicode(1);
2291
2292 my $target_xml = shift;
2293 my $source_xml = shift;
2294 my $tags = shift;
2295
2296 my $target;
2297 my $source;
2298
2299 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2300 if ($@) {
2301     return;
2302 }
2303 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2304 if ($@) {
2305     return;
2306 }
2307
2308 my $source_id = $source->subfield('901', 'c');
2309 $source_id = $source->subfield('903', 'a') unless $source_id;
2310 my $target_id = $target->subfield('901', 'c');
2311 $target_id = $target->subfield('903', 'a') unless $target_id;
2312
2313 my %existing_fields;
2314 foreach my $tag (@$tags) {
2315     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2316     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2317     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2318     if (@to_add) {
2319         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2320     }
2321 }
2322
2323 my $xml = $target->as_xml_record;
2324 $xml =~ s/^<\?.+?\?>$//mo;
2325 $xml =~ s/\n//sgo;
2326 $xml =~ s/>\s+</></sgo;
2327
2328 return $xml;
2329
2330 $func$ LANGUAGE PLPERLU;
2331 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.';
2332
2333 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2334
2335 use strict;
2336 use warnings;
2337
2338 use MARC::Record;
2339 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2340 use Text::CSV;
2341
2342 my $in_tags = shift;
2343 my $in_values = shift;
2344
2345 # hack-and-slash parsing of array-passed-as-string;
2346 # this can go away once everybody is running Postgres 9.1+
2347 my $csv = Text::CSV->new({binary => 1});
2348 $in_tags =~ s/^{//;
2349 $in_tags =~ s/}$//;
2350 my $status = $csv->parse($in_tags);
2351 my $tags = [ $csv->fields() ];
2352 $in_values =~ s/^{//;
2353 $in_values =~ s/}$//;
2354 $status = $csv->parse($in_values);
2355 my $values = [ $csv->fields() ];
2356
2357 my $marc = MARC::Record->new();
2358
2359 $marc->leader('00000nam a22000007  4500');
2360 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2361
2362 foreach my $i (0..$#$tags) {
2363     my ($tag, $sf);
2364     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2365         $tag = $1;
2366         $sf = $2;
2367         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2368     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2369         $tag = $1;
2370         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2371     }
2372 }
2373
2374 my $xml = $marc->as_xml_record;
2375 $xml =~ s/^<\?.+?\?>$//mo;
2376 $xml =~ s/\n//sgo;
2377 $xml =~ s/>\s+</></sgo;
2378
2379 return $xml;
2380
2381 $func$ LANGUAGE PLPERLU;
2382 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2383 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2384 The second argument is an array of text containing the values to plug into each field.
2385 If the value for a given field is NULL or the empty string, it is not inserted.
2386 $$;
2387
2388 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2389
2390 my ($marcxml, $tag, $pos, $value) = @_;
2391
2392 use MARC::Record;
2393 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2394 use MARC::Charset;
2395 use strict;
2396
2397 MARC::Charset->assume_unicode(1);
2398
2399 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2400 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2401 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2402 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2403
2404 my $xml = $marcxml;
2405 eval {
2406     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2407
2408     foreach my $field ($marc->field($tag)) {
2409         $field->update("ind$pos" => $value);
2410     }
2411     $xml = $marc->as_xml_record;
2412     $xml =~ s/^<\?.+?\?>$//mo;
2413     $xml =~ s/\n//sgo;
2414     $xml =~ s/>\s+</></sgo;
2415 };
2416 return $xml;
2417
2418 $func$ LANGUAGE PLPERLU;
2419
2420 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2421 The first argument is a MARCXML string.
2422 The second argument is a MARC tag.
2423 The third argument is the indicator position, either 1 or 2.
2424 The fourth argument is the character to set the indicator value to.
2425 All occurences of the specified field will be changed.
2426 The function returns the revised MARCXML string.$$;
2427
2428 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2429     username TEXT,
2430     password TEXT,
2431     org TEXT,
2432     perm_group TEXT,
2433     first_name TEXT DEFAULT '',
2434     last_name TEXT DEFAULT ''
2435 ) RETURNS VOID AS $func$
2436 BEGIN
2437     RAISE NOTICE '%', org ;
2438     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2439     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2440     FROM   actor.org_unit aou, permission.grp_tree pgt
2441     WHERE  aou.shortname = org
2442     AND    pgt.name = perm_group;
2443 END
2444 $func$
2445 LANGUAGE PLPGSQL;
2446
2447 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2448 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2449     DECLARE
2450         target_event_def ALIAS FOR $1;
2451         orgs ALIAS FOR $2;
2452     BEGIN
2453         DROP TABLE IF EXISTS new_atevdefs;
2454         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2455         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2456             INSERT INTO action_trigger.event_definition (
2457                 active
2458                 ,owner
2459                 ,name
2460                 ,hook
2461                 ,validator
2462                 ,reactor
2463                 ,cleanup_success
2464                 ,cleanup_failure
2465                 ,delay
2466                 ,max_delay
2467                 ,usr_field
2468                 ,opt_in_setting
2469                 ,delay_field
2470                 ,group_field
2471                 ,template
2472                 ,granularity
2473                 ,repeat_delay
2474             ) SELECT
2475                 'f'
2476                 ,orgs[i]
2477                 ,name || ' (clone of '||target_event_def||')'
2478                 ,hook
2479                 ,validator
2480                 ,reactor
2481                 ,cleanup_success
2482                 ,cleanup_failure
2483                 ,delay
2484                 ,max_delay
2485                 ,usr_field
2486                 ,opt_in_setting
2487                 ,delay_field
2488                 ,group_field
2489                 ,template
2490                 ,granularity
2491                 ,repeat_delay
2492             FROM
2493                 action_trigger.event_definition
2494             WHERE
2495                 id = target_event_def
2496             ;
2497             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2498             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2499             INSERT INTO action_trigger.environment (
2500                 event_def
2501                 ,path
2502                 ,collector
2503                 ,label
2504             ) SELECT
2505                 currval('action_trigger.event_definition_id_seq')
2506                 ,path
2507                 ,collector
2508                 ,label
2509             FROM
2510                 action_trigger.environment
2511             WHERE
2512                 event_def = target_event_def
2513             ;
2514             INSERT INTO action_trigger.event_params (
2515                 event_def
2516                 ,param
2517                 ,value
2518             ) SELECT
2519                 currval('action_trigger.event_definition_id_seq')
2520                 ,param
2521                 ,value
2522             FROM
2523                 action_trigger.event_params
2524             WHERE
2525                 event_def = target_event_def
2526             ;
2527         END LOOP;
2528         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);
2529     END;
2530 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2531
2532 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2533 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2534     DECLARE
2535         target_event_def ALIAS FOR $1;
2536         orgs ALIAS FOR $2;
2537         new_interval ALIAS FOR $3;
2538     BEGIN
2539         DROP TABLE IF EXISTS new_atevdefs;
2540         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2541         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2542             INSERT INTO action_trigger.event_definition (
2543                 active
2544                 ,owner
2545                 ,name
2546                 ,hook
2547                 ,validator
2548                 ,reactor
2549                 ,cleanup_success
2550                 ,cleanup_failure
2551                 ,delay
2552                 ,max_delay
2553                 ,usr_field
2554                 ,opt_in_setting
2555                 ,delay_field
2556                 ,group_field
2557                 ,template
2558                 ,granularity
2559                 ,repeat_delay
2560             ) SELECT
2561                 'f'
2562                 ,orgs[i]
2563                 ,name || ' (clone of '||target_event_def||')'
2564                 ,hook
2565                 ,validator
2566                 ,reactor
2567                 ,cleanup_success
2568                 ,cleanup_failure
2569                 ,new_interval
2570                 ,max_delay
2571                 ,usr_field
2572                 ,opt_in_setting
2573                 ,delay_field
2574                 ,group_field
2575                 ,template
2576                 ,granularity
2577                 ,repeat_delay
2578             FROM
2579                 action_trigger.event_definition
2580             WHERE
2581                 id = target_event_def
2582             ;
2583             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2584             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2585             INSERT INTO action_trigger.environment (
2586                 event_def
2587                 ,path
2588                 ,collector
2589                 ,label
2590             ) SELECT
2591                 currval('action_trigger.event_definition_id_seq')
2592                 ,path
2593                 ,collector
2594                 ,label
2595             FROM
2596                 action_trigger.environment
2597             WHERE
2598                 event_def = target_event_def
2599             ;
2600             INSERT INTO action_trigger.event_params (
2601                 event_def
2602                 ,param
2603                 ,value
2604             ) SELECT
2605                 currval('action_trigger.event_definition_id_seq')
2606                 ,param
2607                 ,value
2608             FROM
2609                 action_trigger.event_params
2610             WHERE
2611                 event_def = target_event_def
2612             ;
2613         END LOOP;
2614         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);
2615     END;
2616 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2617
2618 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2619     my ($marcxml) = @_;
2620
2621     use MARC::Record;
2622     use MARC::File::XML;
2623     use MARC::Field;
2624
2625     my $field;
2626     eval {
2627         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2628         $field = $marc->leader();
2629     };
2630     return $field;
2631 $$ LANGUAGE PLPERLU STABLE;
2632
2633 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2634     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2635
2636     use MARC::Record;
2637     use MARC::File::XML;
2638     use MARC::Field;
2639
2640     my $field;
2641     eval {
2642         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2643         $field = $marc->field($tag);
2644     };
2645     return $field->as_string($subfield,$delimiter);
2646 $$ LANGUAGE PLPERLU STABLE;
2647
2648 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2649     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2650
2651     use MARC::Record;
2652     use MARC::File::XML;
2653     use MARC::Field;
2654
2655     my @fields;
2656     eval {
2657         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2658         @fields = $marc->field($tag);
2659     };
2660     my @texts;
2661     foreach my $field (@fields) {
2662         push @texts, $field->as_string($subfield,$delimiter);
2663     }
2664     return \@texts;
2665 $$ LANGUAGE PLPERLU STABLE;
2666
2667 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2668     SELECT action.find_hold_matrix_matchpoint(
2669         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2670         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2671         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2672         (SELECT usr FROM action.hold_request WHERE id = $1),
2673         (SELECT requestor FROM action.hold_request WHERE id = $1)
2674     );
2675 $$ LANGUAGE SQL;
2676
2677 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2678     SELECT action.hold_request_permit_test(
2679         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2680         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2681         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2682         (SELECT usr FROM action.hold_request WHERE id = $1),
2683         (SELECT requestor FROM action.hold_request WHERE id = $1)
2684     );
2685 $$ LANGUAGE SQL;
2686
2687 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2688     SELECT action.find_circ_matrix_matchpoint(
2689         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2690         (SELECT target_copy FROM action.circulation WHERE id = $1),
2691         (SELECT usr FROM action.circulation WHERE id = $1),
2692         (SELECT COALESCE(
2693                 NULLIF(phone_renewal,false),
2694                 NULLIF(desk_renewal,false),
2695                 NULLIF(opac_renewal,false),
2696                 false
2697             ) FROM action.circulation WHERE id = $1
2698         )
2699     );
2700 $$ LANGUAGE SQL;
2701
2702 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2703     DECLARE
2704         test ALIAS FOR $1;
2705     BEGIN
2706         IF NOT test THEN
2707             RAISE EXCEPTION 'assertion';
2708         END IF;
2709     END;
2710 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2711
2712 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2713     DECLARE
2714         test ALIAS FOR $1;
2715         msg ALIAS FOR $2;
2716     BEGIN
2717         IF NOT test THEN
2718             RAISE EXCEPTION '%', msg;
2719         END IF;
2720     END;
2721 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2722
2723 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2724     DECLARE
2725         test ALIAS FOR $1;
2726         fail_msg ALIAS FOR $2;
2727         success_msg ALIAS FOR $3;
2728     BEGIN
2729         IF NOT test THEN
2730             RAISE EXCEPTION '%', fail_msg;
2731         END IF;
2732         RETURN success_msg;
2733     END;
2734 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2735
2736 -- push bib sequence and return starting value for reserved range
2737 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2738     DECLARE
2739         bib_count ALIAS FOR $1;
2740         output BIGINT;
2741     BEGIN
2742         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2743         FOR output IN
2744             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2745         LOOP
2746             RETURN output;
2747         END LOOP;
2748     END;
2749 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2750
2751 -- set a new salted password
2752
2753 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2754     DECLARE
2755         usr_id              ALIAS FOR $1;
2756         plain_passwd        ALIAS FOR $2;
2757         plain_salt          TEXT;
2758         md5_passwd          TEXT;
2759     BEGIN
2760
2761         SELECT actor.create_salt('main') INTO plain_salt;
2762
2763         SELECT MD5(plain_passwd) INTO md5_passwd;
2764
2765         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2766
2767         RETURN TRUE;
2768
2769     END;
2770 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2771
2772
2773 -- convenience functions for handling copy_location maps
2774
2775 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2776     DECLARE
2777         table_schema ALIAS FOR $1;
2778         table_name ALIAS FOR $2;
2779         org_shortname ALIAS FOR $3;
2780         org_range ALIAS FOR $4;
2781         proceed BOOLEAN;
2782         org INTEGER;
2783         org_list INTEGER[];
2784         o INTEGER;
2785     BEGIN
2786         EXECUTE 'SELECT EXISTS (
2787             SELECT 1
2788             FROM information_schema.columns
2789             WHERE table_schema = $1
2790             AND table_name = $2
2791             and column_name = ''desired_shelf''
2792         )' INTO proceed USING table_schema, table_name;
2793         IF NOT proceed THEN
2794             RAISE EXCEPTION 'Missing column desired_shelf';
2795         END IF;
2796
2797         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2798         IF org IS NULL THEN
2799             RAISE EXCEPTION 'Cannot find org by shortname';
2800         END IF;
2801
2802         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2803
2804         EXECUTE 'ALTER TABLE '
2805             || quote_ident(table_name)
2806             || ' DROP COLUMN IF EXISTS x_shelf';
2807         EXECUTE 'ALTER TABLE '
2808             || quote_ident(table_name)
2809             || ' ADD COLUMN x_shelf INTEGER';
2810
2811         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2812             || ' SET x_shelf = id FROM asset_copy_location b'
2813             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2814             || ' AND b.owning_lib = $1'
2815             || ' AND NOT b.deleted'
2816         USING org;
2817
2818         FOREACH o IN ARRAY org_list LOOP
2819             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2820                 || ' SET x_shelf = id FROM asset.copy_location b'
2821                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2822                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2823                 || ' AND NOT b.deleted'
2824             USING o;
2825         END LOOP;
2826
2827         EXECUTE 'SELECT migration_tools.assert(
2828             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2829             ''Cannot find a desired location'',
2830             ''Found all desired locations''
2831         );';
2832
2833     END;
2834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2835
2836 -- convenience functions for handling circmod maps
2837
2838 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2839     DECLARE
2840         table_schema ALIAS FOR $1;
2841         table_name ALIAS FOR $2;
2842         proceed BOOLEAN;
2843     BEGIN
2844         EXECUTE 'SELECT EXISTS (
2845             SELECT 1
2846             FROM information_schema.columns
2847             WHERE table_schema = $1
2848             AND table_name = $2
2849             and column_name = ''desired_circmod''
2850         )' INTO proceed USING table_schema, table_name;
2851         IF NOT proceed THEN
2852             RAISE EXCEPTION 'Missing column desired_circmod';
2853         END IF;
2854
2855         EXECUTE 'ALTER TABLE '
2856             || quote_ident(table_name)
2857             || ' DROP COLUMN IF EXISTS x_circmod';
2858         EXECUTE 'ALTER TABLE '
2859             || quote_ident(table_name)
2860             || ' ADD COLUMN x_circmod TEXT';
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.code))';
2865
2866         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2867             || ' SET x_circmod = code FROM config.circ_modifier b'
2868             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2869             || ' AND x_circmod IS NULL';
2870
2871         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2872             || ' SET x_circmod = code FROM config.circ_modifier b'
2873             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2874             || ' AND x_circmod IS NULL';
2875
2876         EXECUTE 'SELECT migration_tools.assert(
2877             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2878             ''Cannot find a desired circulation modifier'',
2879             ''Found all desired circulation modifiers''
2880         );';
2881
2882     END;
2883 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2884
2885 -- convenience functions for handling item status maps
2886
2887 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
2888     DECLARE
2889         table_schema ALIAS FOR $1;
2890         table_name ALIAS FOR $2;
2891         proceed BOOLEAN;
2892     BEGIN
2893         EXECUTE 'SELECT EXISTS (
2894             SELECT 1
2895             FROM information_schema.columns
2896             WHERE table_schema = $1
2897             AND table_name = $2
2898             and column_name = ''desired_status''
2899         )' INTO proceed USING table_schema, table_name;
2900         IF NOT proceed THEN
2901             RAISE EXCEPTION 'Missing column desired_status';
2902         END IF;
2903
2904         EXECUTE 'ALTER TABLE '
2905             || quote_ident(table_name)
2906             || ' DROP COLUMN IF EXISTS x_status';
2907         EXECUTE 'ALTER TABLE '
2908             || quote_ident(table_name)
2909             || ' ADD COLUMN x_status INTEGER';
2910
2911         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2912             || ' SET x_status = id FROM config.copy_status b'
2913             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2914
2915         EXECUTE 'SELECT migration_tools.assert(
2916             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2917             ''Cannot find a desired copy status'',
2918             ''Found all desired copy statuses''
2919         );';
2920
2921     END;
2922 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2923
2924 -- convenience functions for handling org maps
2925
2926 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2927     DECLARE
2928         table_schema ALIAS FOR $1;
2929         table_name ALIAS FOR $2;
2930         proceed BOOLEAN;
2931     BEGIN
2932         EXECUTE 'SELECT EXISTS (
2933             SELECT 1
2934             FROM information_schema.columns
2935             WHERE table_schema = $1
2936             AND table_name = $2
2937             and column_name = ''desired_org''
2938         )' INTO proceed USING table_schema, table_name;
2939         IF NOT proceed THEN
2940             RAISE EXCEPTION 'Missing column desired_org';
2941         END IF;
2942
2943         EXECUTE 'ALTER TABLE '
2944             || quote_ident(table_name)
2945             || ' DROP COLUMN IF EXISTS x_org';
2946         EXECUTE 'ALTER TABLE '
2947             || quote_ident(table_name)
2948             || ' ADD COLUMN x_org INTEGER';
2949
2950         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2951             || ' SET x_org = id FROM actor.org_unit b'
2952             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2953
2954         EXECUTE 'SELECT migration_tools.assert(
2955             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2956             ''Cannot find a desired org unit'',
2957             ''Found all desired org units''
2958         );';
2959
2960     END;
2961 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2962
2963 -- convenience function for handling desired_not_migrate
2964
2965 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2966     DECLARE
2967         table_schema ALIAS FOR $1;
2968         table_name ALIAS FOR $2;
2969         proceed BOOLEAN;
2970     BEGIN
2971         EXECUTE 'SELECT EXISTS (
2972             SELECT 1
2973             FROM information_schema.columns
2974             WHERE table_schema = $1
2975             AND table_name = $2
2976             and column_name = ''desired_not_migrate''
2977         )' INTO proceed USING table_schema, table_name;
2978         IF NOT proceed THEN
2979             RAISE EXCEPTION 'Missing column desired_not_migrate';
2980         END IF;
2981
2982         EXECUTE 'ALTER TABLE '
2983             || quote_ident(table_name)
2984             || ' DROP COLUMN IF EXISTS x_migrate';
2985         EXECUTE 'ALTER TABLE '
2986             || quote_ident(table_name)
2987             || ' ADD COLUMN x_migrate BOOLEAN';
2988
2989         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2990             || ' SET x_migrate = CASE'
2991             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2992             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
2993             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2994             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
2995             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2996             || ' END';
2997
2998         EXECUTE 'SELECT migration_tools.assert(
2999             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3000             ''Not all desired_not_migrate values understood'',
3001             ''All desired_not_migrate values understood''
3002         );';
3003
3004     END;
3005 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3006
3007 -- convenience function for handling desired_profile
3008
3009 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3010     DECLARE
3011         table_schema ALIAS FOR $1;
3012         table_name ALIAS FOR $2;
3013         proceed BOOLEAN;
3014     BEGIN
3015         EXECUTE 'SELECT EXISTS (
3016             SELECT 1
3017             FROM information_schema.columns
3018             WHERE table_schema = $1
3019             AND table_name = $2
3020             and column_name = ''desired_profile''
3021         )' INTO proceed USING table_schema, table_name;
3022         IF NOT proceed THEN
3023             RAISE EXCEPTION 'Missing column desired_profile';
3024         END IF;
3025
3026         EXECUTE 'ALTER TABLE '
3027             || quote_ident(table_name)
3028             || ' DROP COLUMN IF EXISTS x_profile';
3029         EXECUTE 'ALTER TABLE '
3030             || quote_ident(table_name)
3031             || ' ADD COLUMN x_profile INTEGER';
3032
3033         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3034             || ' SET x_profile = id FROM permission.grp_tree b'
3035             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3036
3037         EXECUTE 'SELECT migration_tools.assert(
3038             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3039             ''Cannot find a desired profile'',
3040             ''Found all desired profiles''
3041         );';
3042
3043     END;
3044 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3045
3046 -- convenience function for handling desired actor stat cats
3047
3048 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3049     DECLARE
3050         table_schema ALIAS FOR $1;
3051         table_name ALIAS FOR $2;
3052         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3053         org_shortname ALIAS FOR $4;
3054         proceed BOOLEAN;
3055         org INTEGER;
3056         org_list INTEGER[];
3057         sc TEXT;
3058         sce TEXT;
3059     BEGIN
3060
3061         SELECT 'desired_sc' || field_suffix INTO sc;
3062         SELECT 'desired_sce' || field_suffix INTO sce;
3063
3064         EXECUTE 'SELECT EXISTS (
3065             SELECT 1
3066             FROM information_schema.columns
3067             WHERE table_schema = $1
3068             AND table_name = $2
3069             and column_name = $3
3070         )' INTO proceed USING table_schema, table_name, sc;
3071         IF NOT proceed THEN
3072             RAISE EXCEPTION 'Missing column %', sc;
3073         END IF;
3074         EXECUTE 'SELECT EXISTS (
3075             SELECT 1
3076             FROM information_schema.columns
3077             WHERE table_schema = $1
3078             AND table_name = $2
3079             and column_name = $3
3080         )' INTO proceed USING table_schema, table_name, sce;
3081         IF NOT proceed THEN
3082             RAISE EXCEPTION 'Missing column %', sce;
3083         END IF;
3084
3085         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3086         IF org IS NULL THEN
3087             RAISE EXCEPTION 'Cannot find org by shortname';
3088         END IF;
3089         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3090
3091         -- caller responsible for their own truncates though we try to prevent duplicates
3092         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3093             SELECT DISTINCT
3094                  $1
3095                 ,BTRIM('||sc||')
3096             FROM
3097                 ' || quote_ident(table_name) || '
3098             WHERE
3099                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3100                 AND NOT EXISTS (
3101                     SELECT id
3102                     FROM actor.stat_cat
3103                     WHERE owner = ANY ($2)
3104                     AND name = BTRIM('||sc||')
3105                 )
3106                 AND NOT EXISTS (
3107                     SELECT id
3108                     FROM actor_stat_cat
3109                     WHERE owner = ANY ($2)
3110                     AND name = BTRIM('||sc||')
3111                 )
3112             ORDER BY 2;'
3113         USING org, org_list;
3114
3115         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3116             SELECT DISTINCT
3117                 COALESCE(
3118                     (SELECT id
3119                         FROM actor.stat_cat
3120                         WHERE owner = ANY ($2)
3121                         AND BTRIM('||sc||') = BTRIM(name))
3122                    ,(SELECT id
3123                         FROM actor_stat_cat
3124                         WHERE owner = ANY ($2)
3125                         AND BTRIM('||sc||') = BTRIM(name))
3126                 )
3127                 ,$1
3128                 ,BTRIM('||sce||')
3129             FROM
3130                 ' || quote_ident(table_name) || '
3131             WHERE
3132                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3133                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3134                 AND NOT EXISTS (
3135                     SELECT id
3136                     FROM actor.stat_cat_entry
3137                     WHERE stat_cat = (
3138                         SELECT id
3139                         FROM actor.stat_cat
3140                         WHERE owner = ANY ($2)
3141                         AND BTRIM('||sc||') = BTRIM(name)
3142                     ) AND value = BTRIM('||sce||')
3143                 )
3144                 AND NOT EXISTS (
3145                     SELECT id
3146                     FROM actor_stat_cat_entry
3147                     WHERE stat_cat = (
3148                         SELECT id
3149                         FROM actor_stat_cat
3150                         WHERE owner = ANY ($2)
3151                         AND BTRIM('||sc||') = BTRIM(name)
3152                     ) AND value = BTRIM('||sce||')
3153                 )
3154             ORDER BY 1,3;'
3155         USING org, org_list;
3156     END;
3157 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3158
3159 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3160     DECLARE
3161         table_schema ALIAS FOR $1;
3162         table_name ALIAS FOR $2;
3163         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3164         org_shortname ALIAS FOR $4;
3165         proceed BOOLEAN;
3166         org INTEGER;
3167         org_list INTEGER[];
3168         o INTEGER;
3169         sc TEXT;
3170         sce TEXT;
3171     BEGIN
3172         SELECT 'desired_sc' || field_suffix INTO sc;
3173         SELECT 'desired_sce' || field_suffix INTO sce;
3174         EXECUTE 'SELECT EXISTS (
3175             SELECT 1
3176             FROM information_schema.columns
3177             WHERE table_schema = $1
3178             AND table_name = $2
3179             and column_name = $3
3180         )' INTO proceed USING table_schema, table_name, sc;
3181         IF NOT proceed THEN
3182             RAISE EXCEPTION 'Missing column %', sc;
3183         END IF;
3184         EXECUTE 'SELECT EXISTS (
3185             SELECT 1
3186             FROM information_schema.columns
3187             WHERE table_schema = $1
3188             AND table_name = $2
3189             and column_name = $3
3190         )' INTO proceed USING table_schema, table_name, sce;
3191         IF NOT proceed THEN
3192             RAISE EXCEPTION 'Missing column %', sce;
3193         END IF;
3194
3195         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3196         IF org IS NULL THEN
3197             RAISE EXCEPTION 'Cannot find org by shortname';
3198         END IF;
3199
3200         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3201
3202         EXECUTE 'ALTER TABLE '
3203             || quote_ident(table_name)
3204             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3205         EXECUTE 'ALTER TABLE '
3206             || quote_ident(table_name)
3207             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3208         EXECUTE 'ALTER TABLE '
3209             || quote_ident(table_name)
3210             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3211         EXECUTE 'ALTER TABLE '
3212             || quote_ident(table_name)
3213             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3214
3215
3216         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3217             SET
3218                 x_sc' || field_suffix || ' = id
3219             FROM
3220                 (SELECT id, name, owner FROM actor_stat_cat
3221                     UNION SELECT id, name, owner FROM actor.stat_cat) u
3222             WHERE
3223                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3224                 AND u.owner = ANY ($1);'
3225         USING org_list;
3226
3227         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3228             SET
3229                 x_sce' || field_suffix || ' = id
3230             FROM
3231                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3232                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3233             WHERE
3234                     u.stat_cat = x_sc' || field_suffix || '
3235                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3236                 AND u.owner = ANY ($1);'
3237         USING org_list;
3238
3239         EXECUTE 'SELECT migration_tools.assert(
3240             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3241             ''Cannot find a desired stat cat'',
3242             ''Found all desired stat cats''
3243         );';
3244
3245         EXECUTE 'SELECT migration_tools.assert(
3246             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3247             ''Cannot find a desired stat cat entry'',
3248             ''Found all desired stat cat entries''
3249         );';
3250
3251     END;
3252 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3253
3254 -- convenience functions for adding shelving locations
3255 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3256 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3257 DECLARE
3258     return_id   INT;
3259     d           INT;
3260     cur_id      INT;
3261 BEGIN
3262     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3263     WHILE d >= 0
3264     LOOP
3265         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3266         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3267         IF return_id IS NOT NULL THEN
3268                 RETURN return_id;
3269         END IF;
3270         d := d - 1;
3271     END LOOP;
3272
3273     RETURN NULL;
3274 END
3275 $$ LANGUAGE plpgsql;
3276
3277 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3278
3279 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3280 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3281 DECLARE
3282     return_id   INT;
3283     d           INT;
3284     cur_id      INT;
3285 BEGIN
3286     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3287     WHILE d >= 0
3288     LOOP
3289         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3290
3291         SELECT INTO return_id id FROM
3292             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3293             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3294         IF return_id IS NOT NULL THEN
3295                 RETURN return_id;
3296         END IF;
3297         d := d - 1;
3298     END LOOP;
3299
3300     RETURN NULL;
3301 END
3302 $$ LANGUAGE plpgsql;
3303
3304 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3305 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
3306 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3307  RETURNS TEXT
3308  LANGUAGE plperlu
3309 AS $function$
3310 use strict;
3311 use warnings;
3312
3313 use MARC::Record;
3314 use MARC::File::XML (BinaryEncoding => 'utf8');
3315
3316 binmode(STDERR, ':bytes');
3317 binmode(STDOUT, ':utf8');
3318 binmode(STDERR, ':utf8');
3319
3320 my $marc_xml = shift;
3321 my $matching_u_text = shift;
3322 my $new_9_to_set = shift;
3323
3324 $marc_xml =~ s/(<leader>.........)./${1}a/;
3325
3326 eval {
3327     $marc_xml = MARC::Record->new_from_xml($marc_xml);
3328 };
3329 if ($@) {
3330     #elog("could not parse $bibid: $@\n");
3331     import MARC::File::XML (BinaryEncoding => 'utf8');
3332     return;
3333 }
3334
3335 my @uris = $marc_xml->field('856');
3336 return unless @uris;
3337
3338 foreach my $field (@uris) {
3339     my $sfu = $field->subfield('u');
3340     my $ind2 = $field->indicator('2');
3341     if (!defined $ind2) { next; }
3342     if ($ind2 ne '0') { next; }
3343     if (!defined $sfu) { next; }
3344     if ($sfu =~ m/$matching_u_text/) {
3345         $field->add_subfields( '9' => $new_9_to_set );
3346         last;
3347     }
3348 }
3349
3350 return $marc_xml->as_xml_record();
3351
3352 $function$;
3353
3354 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
3355 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
3356     RETURNS BOOLEAN AS
3357 $BODY$
3358 DECLARE
3359     source_xml    TEXT;
3360     new_xml       TEXT;
3361     r             BOOLEAN;
3362 BEGIN
3363
3364     EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
3365
3366     SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3367
3368     r = FALSE;
3369         new_xml = '$_$' || new_xml || '$_$';
3370
3371     IF new_xml != source_xml THEN
3372         EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
3373         r = TRUE;
3374     END IF;
3375
3376     RETURN r;
3377
3378 END;
3379 $BODY$ LANGUAGE plpgsql;
3380
3381 -- convenience function for linking to the item staging table
3382
3383 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3384     DECLARE
3385         table_schema ALIAS FOR $1;
3386         table_name ALIAS FOR $2;
3387         foreign_column_name ALIAS FOR $3;
3388         main_column_name ALIAS FOR $4;
3389         btrim_desired ALIAS FOR $5;
3390         proceed BOOLEAN;
3391     BEGIN
3392         EXECUTE 'SELECT EXISTS (
3393             SELECT 1
3394             FROM information_schema.columns
3395             WHERE table_schema = $1
3396             AND table_name = $2
3397             and column_name = $3
3398         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3399         IF NOT proceed THEN
3400             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3401         END IF;
3402
3403         EXECUTE 'SELECT EXISTS (
3404             SELECT 1
3405             FROM information_schema.columns
3406             WHERE table_schema = $1
3407             AND table_name = ''asset_copy_legacy''
3408             and column_name = $2
3409         )' INTO proceed USING table_schema, main_column_name;
3410         IF NOT proceed THEN
3411             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3412         END IF;
3413
3414         EXECUTE 'ALTER TABLE '
3415             || quote_ident(table_name)
3416             || ' DROP COLUMN IF EXISTS x_item';
3417         EXECUTE 'ALTER TABLE '
3418             || quote_ident(table_name)
3419             || ' ADD COLUMN x_item BIGINT';
3420
3421         IF btrim_desired THEN
3422             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3423                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3424                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3425                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3426         ELSE
3427             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3428                 || ' SET x_item = b.id FROM asset_copy_legacy b'
3429                 || ' WHERE a.' || quote_ident(foreign_column_name)
3430                 || ' = b.' || quote_ident(main_column_name);
3431         END IF;
3432
3433         --EXECUTE 'SELECT migration_tools.assert(
3434         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3435         --    ''Cannot link every barcode'',
3436         --    ''Every barcode linked''
3437         --);';
3438
3439     END;
3440 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3441
3442 -- convenience function for linking to the user staging table
3443
3444 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3445     DECLARE
3446         table_schema ALIAS FOR $1;
3447         table_name ALIAS FOR $2;
3448         foreign_column_name ALIAS FOR $3;
3449         main_column_name ALIAS FOR $4;
3450         btrim_desired ALIAS FOR $5;
3451         proceed BOOLEAN;
3452     BEGIN
3453         EXECUTE 'SELECT EXISTS (
3454             SELECT 1
3455             FROM information_schema.columns
3456             WHERE table_schema = $1
3457             AND table_name = $2
3458             and column_name = $3
3459         )' INTO proceed USING table_schema, table_name, foreign_column_name;
3460         IF NOT proceed THEN
3461             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3462         END IF;
3463
3464         EXECUTE 'SELECT EXISTS (
3465             SELECT 1
3466             FROM information_schema.columns
3467             WHERE table_schema = $1
3468             AND table_name = ''actor_usr_legacy''
3469             and column_name = $2
3470         )' INTO proceed USING table_schema, main_column_name;
3471         IF NOT proceed THEN
3472             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3473         END IF;
3474
3475         EXECUTE 'ALTER TABLE '
3476             || quote_ident(table_name)
3477             || ' DROP COLUMN IF EXISTS x_user';
3478         EXECUTE 'ALTER TABLE '
3479             || quote_ident(table_name)
3480             || ' ADD COLUMN x_user INTEGER';
3481
3482         IF btrim_desired THEN
3483             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3484                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3485                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3486                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3487         ELSE
3488             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3489                 || ' SET x_user = b.id FROM actor_usr_legacy b'
3490                 || ' WHERE a.' || quote_ident(foreign_column_name)
3491                 || ' = b.' || quote_ident(main_column_name);
3492         END IF;
3493
3494         --EXECUTE 'SELECT migration_tools.assert(
3495         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3496         --    ''Cannot link every barcode'',
3497         --    ''Every barcode linked''
3498         --);';
3499
3500     END;
3501 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3502
3503 -- convenience function for linking two tables
3504 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3505 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3506     DECLARE
3507         table_schema ALIAS FOR $1;
3508         table_a ALIAS FOR $2;
3509         column_a ALIAS FOR $3;
3510         table_b ALIAS FOR $4;
3511         column_b ALIAS FOR $5;
3512         column_x ALIAS FOR $6;
3513         btrim_desired ALIAS FOR $7;
3514         proceed BOOLEAN;
3515     BEGIN
3516         EXECUTE 'SELECT EXISTS (
3517             SELECT 1
3518             FROM information_schema.columns
3519             WHERE table_schema = $1
3520             AND table_name = $2
3521             and column_name = $3
3522         )' INTO proceed USING table_schema, table_a, column_a;
3523         IF NOT proceed THEN
3524             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3525         END IF;
3526
3527         EXECUTE 'SELECT EXISTS (
3528             SELECT 1
3529             FROM information_schema.columns
3530             WHERE table_schema = $1
3531             AND table_name = $2
3532             and column_name = $3
3533         )' INTO proceed USING table_schema, table_b, column_b;
3534         IF NOT proceed THEN
3535             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3536         END IF;
3537
3538         EXECUTE 'ALTER TABLE '
3539             || quote_ident(table_b)
3540             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3541         EXECUTE 'ALTER TABLE '
3542             || quote_ident(table_b)
3543             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3544
3545         IF btrim_desired THEN
3546             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3547                 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3548                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3549                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3550         ELSE
3551             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3552                 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3553                 || ' WHERE a.' || quote_ident(column_a)
3554                 || ' = b.' || quote_ident(column_b);
3555         END IF;
3556
3557     END;
3558 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3559
3560 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3561 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3562 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3563     DECLARE
3564         table_schema ALIAS FOR $1;
3565         table_a ALIAS FOR $2;
3566         column_a ALIAS FOR $3;
3567         table_b ALIAS FOR $4;
3568         column_b ALIAS FOR $5;
3569         column_w ALIAS FOR $6;
3570         column_x ALIAS FOR $7;
3571         btrim_desired ALIAS FOR $8;
3572         proceed BOOLEAN;
3573     BEGIN
3574         EXECUTE 'SELECT EXISTS (
3575             SELECT 1
3576             FROM information_schema.columns
3577             WHERE table_schema = $1
3578             AND table_name = $2
3579             and column_name = $3
3580         )' INTO proceed USING table_schema, table_a, column_a;
3581         IF NOT proceed THEN
3582             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3583         END IF;
3584
3585         EXECUTE 'SELECT EXISTS (
3586             SELECT 1
3587             FROM information_schema.columns
3588             WHERE table_schema = $1
3589             AND table_name = $2
3590             and column_name = $3
3591         )' INTO proceed USING table_schema, table_b, column_b;
3592         IF NOT proceed THEN
3593             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3594         END IF;
3595
3596         EXECUTE 'ALTER TABLE '
3597             || quote_ident(table_b)
3598             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3599         EXECUTE 'ALTER TABLE '
3600             || quote_ident(table_b)
3601             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3602
3603         IF btrim_desired THEN
3604             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3605                 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3606                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3607                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3608         ELSE
3609             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3610                 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3611                 || ' WHERE a.' || quote_ident(column_a)
3612                 || ' = b.' || quote_ident(column_b);
3613         END IF;
3614
3615     END;
3616 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3617
3618 -- convenience function for handling desired asset stat cats
3619
3620 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3621     DECLARE
3622         table_schema ALIAS FOR $1;
3623         table_name ALIAS FOR $2;
3624         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3625         org_shortname ALIAS FOR $4;
3626         proceed BOOLEAN;
3627         org INTEGER;
3628         org_list INTEGER[];
3629         sc TEXT;
3630         sce TEXT;
3631     BEGIN
3632
3633         SELECT 'desired_sc' || field_suffix INTO sc;
3634         SELECT 'desired_sce' || field_suffix INTO sce;
3635
3636         EXECUTE 'SELECT EXISTS (
3637             SELECT 1
3638             FROM information_schema.columns
3639             WHERE table_schema = $1
3640             AND table_name = $2
3641             and column_name = $3
3642         )' INTO proceed USING table_schema, table_name, sc;
3643         IF NOT proceed THEN
3644             RAISE EXCEPTION 'Missing column %', sc;
3645         END IF;
3646         EXECUTE 'SELECT EXISTS (
3647             SELECT 1
3648             FROM information_schema.columns
3649             WHERE table_schema = $1
3650             AND table_name = $2
3651             and column_name = $3
3652         )' INTO proceed USING table_schema, table_name, sce;
3653         IF NOT proceed THEN
3654             RAISE EXCEPTION 'Missing column %', sce;
3655         END IF;
3656
3657         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3658         IF org IS NULL THEN
3659             RAISE EXCEPTION 'Cannot find org by shortname';
3660         END IF;
3661         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3662
3663         -- caller responsible for their own truncates though we try to prevent duplicates
3664         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3665             SELECT DISTINCT
3666                  $1
3667                 ,BTRIM('||sc||')
3668             FROM
3669                 ' || quote_ident(table_name) || '
3670             WHERE
3671                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3672                 AND NOT EXISTS (
3673                     SELECT id
3674                     FROM asset.stat_cat
3675                     WHERE owner = ANY ($2)
3676                     AND name = BTRIM('||sc||')
3677                 )
3678                 AND NOT EXISTS (
3679                     SELECT id
3680                     FROM asset_stat_cat
3681                     WHERE owner = ANY ($2)
3682                     AND name = BTRIM('||sc||')
3683                 )
3684             ORDER BY 2;'
3685         USING org, org_list;
3686
3687         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3688             SELECT DISTINCT
3689                 COALESCE(
3690                     (SELECT id
3691                         FROM asset.stat_cat
3692                         WHERE owner = ANY ($2)
3693                         AND BTRIM('||sc||') = BTRIM(name))
3694                    ,(SELECT id
3695                         FROM asset_stat_cat
3696                         WHERE owner = ANY ($2)
3697                         AND BTRIM('||sc||') = BTRIM(name))
3698                 )
3699                 ,$1
3700                 ,BTRIM('||sce||')
3701             FROM
3702                 ' || quote_ident(table_name) || '
3703             WHERE
3704                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3705                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3706                 AND NOT EXISTS (
3707                     SELECT id
3708                     FROM asset.stat_cat_entry
3709                     WHERE stat_cat = (
3710                         SELECT id
3711                         FROM asset.stat_cat
3712                         WHERE owner = ANY ($2)
3713                         AND BTRIM('||sc||') = BTRIM(name)
3714                     ) AND value = BTRIM('||sce||')
3715                 )
3716                 AND NOT EXISTS (
3717                     SELECT id
3718                     FROM asset_stat_cat_entry
3719                     WHERE stat_cat = (
3720                         SELECT id
3721                         FROM asset_stat_cat
3722                         WHERE owner = ANY ($2)
3723                         AND BTRIM('||sc||') = BTRIM(name)
3724                     ) AND value = BTRIM('||sce||')
3725                 )
3726             ORDER BY 1,3;'
3727         USING org, org_list;
3728     END;
3729 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3730
3731 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3732     DECLARE
3733         table_schema ALIAS FOR $1;
3734         table_name ALIAS FOR $2;
3735         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3736         org_shortname ALIAS FOR $4;
3737         proceed BOOLEAN;
3738         org INTEGER;
3739         org_list INTEGER[];
3740         o INTEGER;
3741         sc TEXT;
3742         sce TEXT;
3743     BEGIN
3744         SELECT 'desired_sc' || field_suffix INTO sc;
3745         SELECT 'desired_sce' || field_suffix INTO sce;
3746         EXECUTE 'SELECT EXISTS (
3747             SELECT 1
3748             FROM information_schema.columns
3749             WHERE table_schema = $1
3750             AND table_name = $2
3751             and column_name = $3
3752         )' INTO proceed USING table_schema, table_name, sc;
3753         IF NOT proceed THEN
3754             RAISE EXCEPTION 'Missing column %', sc;
3755         END IF;
3756         EXECUTE 'SELECT EXISTS (
3757             SELECT 1
3758             FROM information_schema.columns
3759             WHERE table_schema = $1
3760             AND table_name = $2
3761             and column_name = $3
3762         )' INTO proceed USING table_schema, table_name, sce;
3763         IF NOT proceed THEN
3764             RAISE EXCEPTION 'Missing column %', sce;
3765         END IF;
3766
3767         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3768         IF org IS NULL THEN
3769             RAISE EXCEPTION 'Cannot find org by shortname';
3770         END IF;
3771
3772         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3773
3774         EXECUTE 'ALTER TABLE '
3775             || quote_ident(table_name)
3776             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3777         EXECUTE 'ALTER TABLE '
3778             || quote_ident(table_name)
3779             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3780         EXECUTE 'ALTER TABLE '
3781             || quote_ident(table_name)
3782             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3783         EXECUTE 'ALTER TABLE '
3784             || quote_ident(table_name)
3785             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3786
3787
3788         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3789             SET
3790                 x_sc' || field_suffix || ' = id
3791             FROM
3792                 (SELECT id, name, owner FROM asset_stat_cat
3793                     UNION SELECT id, name, owner FROM asset.stat_cat) u
3794             WHERE
3795                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3796                 AND u.owner = ANY ($1);'
3797         USING org_list;
3798
3799         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3800             SET
3801                 x_sce' || field_suffix || ' = id
3802             FROM
3803                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3804                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3805             WHERE
3806                     u.stat_cat = x_sc' || field_suffix || '
3807                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3808                 AND u.owner = ANY ($1);'
3809         USING org_list;
3810
3811         EXECUTE 'SELECT migration_tools.assert(
3812             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3813             ''Cannot find a desired stat cat'',
3814             ''Found all desired stat cats''
3815         );';
3816
3817         EXECUTE 'SELECT migration_tools.assert(
3818             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3819             ''Cannot find a desired stat cat entry'',
3820             ''Found all desired stat cat entries''
3821         );';
3822
3823     END;
3824 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3825
3826 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
3827 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3828  LANGUAGE plpgsql
3829 AS $function$
3830 DECLARE
3831     c_name     TEXT;
3832 BEGIN
3833
3834     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3835             table_name = t_name
3836             AND table_schema = s_name
3837             AND (data_type='text' OR data_type='character varying')
3838             AND column_name like 'l_%'
3839     LOOP
3840        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3841     END LOOP;
3842
3843     RETURN TRUE;
3844 END
3845 $function$;
3846
3847 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
3848 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3849  LANGUAGE plpgsql
3850 AS $function$
3851 DECLARE
3852     c_name     TEXT;
3853 BEGIN
3854
3855     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3856             table_name = t_name
3857             AND table_schema = s_name
3858             AND (data_type='text' OR data_type='character varying')
3859             AND column_name like 'l_%'
3860     LOOP
3861        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3862     END LOOP;
3863
3864     RETURN TRUE;
3865 END
3866 $function$;
3867