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