sql/base/base.sql

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