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'' );' );
 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.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
 611     DECLARE
 612         n TEXT := o;
 613     BEGIN
 614         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
 615             IF o::BIGINT < t THEN
 616                 n = o::BIGINT + t;
 617             END IF;
 618         END IF;
 619
 620         RETURN n;
 621     END;
 622 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 623
 624 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
 625     DECLARE
 626         migration_schema ALIAS FOR $1;
 627         output TEXT;
 628     BEGIN
 629         FOR output IN
 630             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
 631         LOOP
 632             RETURN output;
 633         END LOOP;
 634     END;
 635 $$ LANGUAGE PLPGSQL STRICT STABLE;
 636
 637 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
 638     DECLARE
 639         migration_schema ALIAS FOR $1;
 640         output TEXT;
 641     BEGIN
 642         FOR output IN
 643             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
 644         LOOP
 645             RETURN output;
 646         END LOOP;
 647     END;
 648 $$ LANGUAGE PLPGSQL STRICT STABLE;
 649
 650 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
 651     DECLARE
 652         migration_schema ALIAS FOR $1;
 653         output TEXT;
 654     BEGIN
 655         FOR output IN
 656             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
 657         LOOP
 658             RETURN output;
 659         END LOOP;
 660     END;
 661 $$ LANGUAGE PLPGSQL STRICT STABLE;
 662
 663 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
 664     DECLARE
 665         migration_schema ALIAS FOR $1;
 666         output TEXT;
 667     BEGIN
 668         FOR output IN
 669             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
 670         LOOP
 671             RETURN output;
 672         END LOOP;
 673     END;
 674 $$ LANGUAGE PLPGSQL STRICT STABLE;
 675
 676 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
 677     DECLARE
 678         migration_schema ALIAS FOR $1;
 679         profile_map TEXT;
 680         patron_table ALIAS FOR $2;
 681         default_patron_profile ALIAS FOR $3;
 682         sql TEXT;
 683         sql_update TEXT;
 684         sql_where1 TEXT := '';
 685         sql_where2 TEXT := '';
 686         sql_where3 TEXT := '';
 687         output RECORD;
 688     BEGIN
 689         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
 690         FOR output IN
 691             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
 692         LOOP
 693             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
 694             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);
 695             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);
 696             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);
 697             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,'') || ';';
 698             --RAISE INFO 'sql = %', sql;
 699             PERFORM migration_tools.exec( $1, sql );
 700         END LOOP;
 701         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
 702         BEGIN
 703             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
 704         EXCEPTION
 705             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
 706         END;
 707     END;
 708 $$ LANGUAGE PLPGSQL STRICT STABLE;
 709
 710 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
 711     DECLARE
 712         migration_schema ALIAS FOR $1;
 713         field_map TEXT;
 714         item_table ALIAS FOR $2;
 715         sql TEXT;
 716         sql_update TEXT;
 717         sql_where1 TEXT := '';
 718         sql_where2 TEXT := '';
 719         sql_where3 TEXT := '';
 720         output RECORD;
 721     BEGIN
 722         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
 723         FOR output IN
 724             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 725         LOOP
 726             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 ';
 727             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);
 728             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);
 729             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);
 730             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,'') || ';';
 731             --RAISE INFO 'sql = %', sql;
 732             PERFORM migration_tools.exec( $1, sql );
 733         END LOOP;
 734         BEGIN
 735             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
 736         EXCEPTION
 737             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
 738         END;
 739     END;
 740 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 741
 742 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
 743     DECLARE
 744         migration_schema ALIAS FOR $1;
 745         base_copy_location_map TEXT;
 746         item_table ALIAS FOR $2;
 747         sql TEXT;
 748         sql_update TEXT;
 749         sql_where1 TEXT := '';
 750         sql_where2 TEXT := '';
 751         sql_where3 TEXT := '';
 752         output RECORD;
 753     BEGIN
 754         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
 755         FOR output IN
 756             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
 757         LOOP
 758             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
 759             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);
 760             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);
 761             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);
 762             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,'') || ';';
 763             --RAISE INFO 'sql = %', sql;
 764             PERFORM migration_tools.exec( $1, sql );
 765         END LOOP;
 766         BEGIN
 767             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
 768         EXCEPTION
 769             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
 770         END;
 771     END;
 772 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 773
 774 -- 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
 775 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
 776     DECLARE
 777         migration_schema ALIAS FOR $1;
 778         field_map TEXT;
 779         circ_table ALIAS FOR $2;
 780         item_table ALIAS FOR $3;
 781         patron_table ALIAS FOR $4;
 782         sql TEXT;
 783         sql_update TEXT;
 784         sql_where1 TEXT := '';
 785         sql_where2 TEXT := '';
 786         sql_where3 TEXT := '';
 787         sql_where4 TEXT := '';
 788         output RECORD;
 789     BEGIN
 790         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
 791         FOR output IN
 792             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 793         LOOP
 794             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 ';
 795             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);
 796             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);
 797             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);
 798             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);
 799             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,'') || ';';
 800             --RAISE INFO 'sql = %', sql;
 801             PERFORM migration_tools.exec( $1, sql );
 802         END LOOP;
 803         BEGIN
 804             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
 805         EXCEPTION
 806             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
 807         END;
 808     END;
 809 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 810
 811 -- expand_barcode
 812 --   $barcode      source barcode
 813 --   $prefix       prefix to add to barcode, NULL = add no prefix
 814 --   $maxlen       maximum length of barcode; default to 14 if left NULL
 815 --   $pad          padding string to apply to left of source barcode before adding
 816 --                 prefix and suffix; set to NULL or '' if no padding is desired
 817 --   $suffix       suffix to add to barcode, NULL = add no suffix
 818 --
 819 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
 820 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
 821 --
 822 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
 823     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
 824
 825     # default case
 826     return unless defined $barcode;
 827
 828     $prefix     = '' unless defined $prefix;
 829     $maxlen ||= 14;
 830     $pad        = '0' unless defined $pad;
 831     $suffix     = '' unless defined $suffix;
 832
 833     # bail out if adding prefix and suffix would bring new barcode over max length
 834     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
 835
 836     my $new_barcode = $barcode;
 837     if ($pad ne '') {
 838         my $pad_length = $maxlen - length($prefix) - length($suffix);
 839         if (length($barcode) < $pad_length) {
 840             # assuming we always want padding on the left
 841             # also assuming that it is possible to have the pad string be longer than 1 character
 842             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
 843         }
 844     }
 845
 846     # bail out if adding prefix and suffix would bring new barcode over max length
 847     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
 848
 849     return "$prefix$new_barcode$suffix";
 850 $$ LANGUAGE PLPERLU STABLE;
 851
 852 -- remove previous version of this function
 853 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
 854
 855 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
 856     DECLARE
 857         attempt_value ALIAS FOR $1;
 858         datatype ALIAS FOR $2;
 859     BEGIN
 860         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
 861         RETURN attempt_value;
 862     EXCEPTION
 863         WHEN OTHERS THEN RETURN NULL;
 864     END;
 865 $$ LANGUAGE PLPGSQL STRICT STABLE;
 866
 867 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
 868     DECLARE
 869         attempt_value ALIAS FOR $1;
 870         fail_value ALIAS FOR $2;
 871         output DATE;
 872     BEGIN
 873         FOR output IN
 874             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
 875         LOOP
 876             RETURN output;
 877         END LOOP;
 878     EXCEPTION
 879         WHEN OTHERS THEN
 880             FOR output IN
 881                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 882             LOOP
 883                 RETURN output;
 884             END LOOP;
 885     END;
 886 $$ LANGUAGE PLPGSQL STRICT STABLE;
 887
 888 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
 889     DECLARE
 890         attempt_value ALIAS FOR $1;
 891         fail_value ALIAS FOR $2;
 892         output TIMESTAMPTZ;
 893     BEGIN
 894         FOR output IN
 895             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
 896         LOOP
 897             RETURN output;
 898         END LOOP;
 899     EXCEPTION
 900         WHEN OTHERS THEN
 901             FOR output IN
 902                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
 903             LOOP
 904                 RETURN output;
 905             END LOOP;
 906     END;
 907 $$ LANGUAGE PLPGSQL STRICT STABLE;
 908
 909 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
 910     DECLARE
 911         attempt_value ALIAS FOR $1;
 912         fail_value ALIAS FOR $2;
 913         output DATE;
 914     BEGIN
 915         FOR output IN
 916             EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
 917         LOOP
 918             RETURN output;
 919         END LOOP;
 920     EXCEPTION
 921         WHEN OTHERS THEN
 922             FOR output IN
 923                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 924             LOOP
 925                 RETURN output;
 926             END LOOP;
 927     END;
 928 $$ LANGUAGE PLPGSQL STRICT STABLE;
 929
 930 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
 931     DECLARE
 932         attempt_value ALIAS FOR $1;
 933         fail_value ALIAS FOR $2;
 934         output TIMESTAMP;
 935     BEGIN
 936             output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
 937             RETURN output;
 938     EXCEPTION
 939         WHEN OTHERS THEN
 940             FOR output IN
 941                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
 942             LOOP
 943                 RETURN output;
 944             END LOOP;
 945     END;
 946 $$ LANGUAGE PLPGSQL STRICT STABLE;
 947
 948 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 949     DECLARE
 950         attempt_value ALIAS FOR $1;
 951         fail_value ALIAS FOR $2;
 952         output NUMERIC(8,2);
 953     BEGIN
 954         FOR output IN
 955             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
 956         LOOP
 957             RETURN output;
 958         END LOOP;
 959     EXCEPTION
 960         WHEN OTHERS THEN
 961             FOR output IN
 962                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 963             LOOP
 964                 RETURN output;
 965             END LOOP;
 966     END;
 967 $$ LANGUAGE PLPGSQL STRICT STABLE;
 968
 969 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 970     DECLARE
 971         attempt_value ALIAS FOR $1;
 972         fail_value ALIAS FOR $2;
 973         output NUMERIC(6,2);
 974     BEGIN
 975         FOR output IN
 976             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
 977         LOOP
 978             RETURN output;
 979         END LOOP;
 980     EXCEPTION
 981         WHEN OTHERS THEN
 982             FOR output IN
 983                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 984             LOOP
 985                 RETURN output;
 986             END LOOP;
 987     END;
 988 $$ LANGUAGE PLPGSQL STRICT STABLE;
 989
 990 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 991     DECLARE
 992         attempt_value ALIAS FOR $1;
 993         fail_value ALIAS FOR $2;
 994         output NUMERIC(8,2);
 995     BEGIN
 996         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
 997             RAISE EXCEPTION 'too many digits';
 998         END IF;
 999         FOR output IN
1000             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;'
1001         LOOP
1002             RETURN output;
1003         END LOOP;
1004     EXCEPTION
1005         WHEN OTHERS THEN
1006             FOR output IN
1007                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1008             LOOP
1009                 RETURN output;
1010             END LOOP;
1011     END;
1012 $$ LANGUAGE PLPGSQL STRICT STABLE;
1013
1014 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1015     DECLARE
1016         attempt_value ALIAS FOR $1;
1017         fail_value ALIAS FOR $2;
1018         output NUMERIC(6,2);
1019     BEGIN
1020         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1021             RAISE EXCEPTION 'too many digits';
1022         END IF;
1023         FOR output IN
1024             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;'
1025         LOOP
1026             RETURN output;
1027         END LOOP;
1028     EXCEPTION
1029         WHEN OTHERS THEN
1030             FOR output IN
1031                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1032             LOOP
1033                 RETURN output;
1034             END LOOP;
1035     END;
1036 $$ LANGUAGE PLPGSQL STRICT STABLE;
1037
1038 -- add_codabar_checkdigit
1039 --   $barcode      source barcode
1040 --
1041 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1042 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1043 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
1044 -- input string does not meet those requirements, it is returned unchanged.
1045 --
1046 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1047     my $barcode = shift;
1048
1049     return $barcode if $barcode !~ /^\d{13,14}$/;
1050     $barcode = substr($barcode, 0, 13); # ignore 14th digit
1051     my @digits = split //, $barcode;
1052     my $total = 0;
1053     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1054     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1055     my $remainder = $total % 10;
1056     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1057     return $barcode . $checkdigit;
1058 $$ LANGUAGE PLPERLU STRICT STABLE;
1059
1060 -- add_code39mod43_checkdigit
1061 --   $barcode      source barcode
1062 --
1063 -- If the source string is 13 or 14 characters long and contains only valid
1064 -- Code 39 mod 43 characters, adds or replaces the 14th
1065 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1066 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
1067 -- input string does not meet those requirements, it is returned unchanged.
1068 --
1069 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1070     my $barcode = shift;
1071
1072     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1073     $barcode = substr($barcode, 0, 13); # ignore 14th character
1074
1075     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1076     my %nums = map { $valid_chars[$_] => $_ } (0..42);
1077
1078     my $total = 0;
1079     $total += $nums{$_} foreach split(//, $barcode);
1080     my $remainder = $total % 43;
1081     my $checkdigit = $valid_chars[$remainder];
1082     return $barcode . $checkdigit;
1083 $$ LANGUAGE PLPERLU STRICT STABLE;
1084
1085 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1086   DECLARE
1087     phone TEXT := $1;
1088     areacode TEXT := $2;
1089     temp TEXT := '';
1090     output TEXT := '';
1091     n_digits INTEGER := 0;
1092   BEGIN
1093     temp := phone;
1094     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1095     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1096     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1097     IF n_digits = 7 AND areacode <> '' THEN
1098       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1099       output := (areacode || '-' || temp);
1100     ELSE
1101       output := temp;
1102     END IF;
1103     RETURN output;
1104   END;
1105
1106 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1107
1108 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1109   my ($marcxml, $pos, $value) = @_;
1110
1111   use MARC::Record;
1112   use MARC::File::XML;
1113
1114   my $xml = $marcxml;
1115   eval {
1116     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1117     my $leader = $marc->leader();
1118     substr($leader, $pos, 1) = $value;
1119     $marc->leader($leader);
1120     $xml = $marc->as_xml_record;
1121     $xml =~ s/^<\?.+?\?>$//mo;
1122     $xml =~ s/\n//sgo;
1123     $xml =~ s/>\s+</></sgo;
1124   };
1125   return $xml;
1126 $$ LANGUAGE PLPERLU STABLE;
1127
1128 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1129   my ($marcxml, $pos, $value) = @_;
1130
1131   use MARC::Record;
1132   use MARC::File::XML;
1133
1134   my $xml = $marcxml;
1135   eval {
1136     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1137     my $f008 = $marc->field('008');
1138
1139     if ($f008) {
1140        my $field = $f008->data();
1141        substr($field, $pos, 1) = $value;
1142        $f008->update($field);
1143        $xml = $marc->as_xml_record;
1144        $xml =~ s/^<\?.+?\?>$//mo;
1145        $xml =~ s/\n//sgo;
1146        $xml =~ s/>\s+</></sgo;
1147     }
1148   };
1149   return $xml;
1150 $$ LANGUAGE PLPERLU STABLE;
1151
1152
1153 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1154   DECLARE
1155     profile ALIAS FOR $1;
1156   BEGIN
1157     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1158   END;
1159 $$ LANGUAGE PLPGSQL STRICT STABLE;
1160
1161
1162 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1163   BEGIN
1164     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1165   END;
1166 $$ LANGUAGE PLPGSQL STRICT STABLE;
1167
1168
1169 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1170
1171   my ($marcxml, $tags) = @_;
1172
1173   use MARC::Record;
1174   use MARC::File::XML;
1175
1176   my $xml = $marcxml;
1177
1178   eval {
1179     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1180     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1181
1182     my @incumbents = ();
1183
1184     foreach my $field ( $marc->fields() ) {
1185       push @incumbents, $field->as_formatted();
1186     }
1187
1188     foreach $field ( $to_insert->fields() ) {
1189       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1190         $marc->insert_fields_ordered( ($field) );
1191       }
1192     }
1193
1194     $xml = $marc->as_xml_record;
1195     $xml =~ s/^<\?.+?\?>$//mo;
1196     $xml =~ s/\n//sgo;
1197     $xml =~ s/>\s+</></sgo;
1198   };
1199
1200   return $xml;
1201
1202 $$ LANGUAGE PLPERLU STABLE;
1203
1204 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1205
1206 -- Usage:
1207 --
1208 --   First make sure the circ matrix is loaded and the circulations
1209 --   have been staged to the extent possible (but at the very least
1210 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1211 --   circ modifiers must also be in place.
1212 --
1213 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1214 --
1215
1216 DECLARE
1217   circ_lib             INT;
1218   target_copy          INT;
1219   usr                  INT;
1220   is_renewal           BOOLEAN;
1221   this_duration_rule   INT;
1222   this_fine_rule       INT;
1223   this_max_fine_rule   INT;
1224   rcd                  config.rule_circ_duration%ROWTYPE;
1225   rrf                  config.rule_recurring_fine%ROWTYPE;
1226   rmf                  config.rule_max_fine%ROWTYPE;
1227   circ                 INT;
1228   n                    INT := 0;
1229   n_circs              INT;
1230
1231 BEGIN
1232
1233   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1234
1235   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1236
1237     -- Fetch the correct rules for this circulation
1238     EXECUTE ('
1239       SELECT
1240         circ_lib,
1241         target_copy,
1242         usr,
1243         CASE
1244           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1245           ELSE FALSE
1246         END
1247       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1248       INTO circ_lib, target_copy, usr, is_renewal ;
1249     SELECT
1250       INTO this_duration_rule,
1251            this_fine_rule,
1252            this_max_fine_rule
1253       duration_rule,
1254       recurring_fine_rule,
1255       max_fine_rule
1256       FROM action.item_user_circ_test(
1257         circ_lib,
1258         target_copy,
1259         usr,
1260         is_renewal
1261         );
1262     SELECT INTO rcd * FROM config.rule_circ_duration
1263       WHERE id = this_duration_rule;
1264     SELECT INTO rrf * FROM config.rule_recurring_fine
1265       WHERE id = this_fine_rule;
1266     SELECT INTO rmf * FROM config.rule_max_fine
1267       WHERE id = this_max_fine_rule;
1268
1269     -- Apply the rules to this circulation
1270     EXECUTE ('UPDATE ' || tablename || ' c
1271     SET
1272       duration_rule = rcd.name,
1273       recurring_fine_rule = rrf.name,
1274       max_fine_rule = rmf.name,
1275       duration = rcd.normal,
1276       recurring_fine = rrf.normal,
1277       max_fine =
1278         CASE rmf.is_percent
1279           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1280           ELSE rmf.amount
1281         END,
1282       renewal_remaining = rcd.max_renewals
1283     FROM
1284       config.rule_circ_duration rcd,
1285       config.rule_recurring_fine rrf,
1286       config.rule_max_fine rmf,
1287                         asset.copy ac
1288     WHERE
1289       rcd.id = ' || this_duration_rule || ' AND
1290       rrf.id = ' || this_fine_rule || ' AND
1291       rmf.id = ' || this_max_fine_rule || ' AND
1292                         ac.id = c.target_copy AND
1293       c.id = ' || circ || ';');
1294
1295     -- Keep track of where we are in the process
1296     n := n + 1;
1297     IF (n % 100 = 0) THEN
1298       RAISE INFO '%', n || ' of ' || n_circs
1299         || ' (' || (100*n/n_circs) || '%) circs updated.';
1300     END IF;
1301
1302   END LOOP;
1303
1304   RETURN;
1305 END;
1306
1307 $$ LANGUAGE plpgsql;
1308
1309 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1310
1311 -- Usage:
1312 --
1313 --   First make sure the circ matrix is loaded and the circulations
1314 --   have been staged to the extent possible (but at the very least
1315 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1316 --   circ modifiers must also be in place.
1317 --
1318 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1319 --
1320
1321 DECLARE
1322   circ_lib             INT;
1323   target_copy          INT;
1324   usr                  INT;
1325   is_renewal           BOOLEAN;
1326   this_duration_rule   INT;
1327   this_fine_rule       INT;
1328   this_max_fine_rule   INT;
1329   rcd                  config.rule_circ_duration%ROWTYPE;
1330   rrf                  config.rule_recurring_fine%ROWTYPE;
1331   rmf                  config.rule_max_fine%ROWTYPE;
1332   circ                 INT;
1333   n                    INT := 0;
1334   n_circs              INT;
1335
1336 BEGIN
1337
1338   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1339
1340   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1341
1342     -- Fetch the correct rules for this circulation
1343     EXECUTE ('
1344       SELECT
1345         circ_lib,
1346         target_copy,
1347         usr,
1348         CASE
1349           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1350           ELSE FALSE
1351         END
1352       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1353       INTO circ_lib, target_copy, usr, is_renewal ;
1354     SELECT
1355       INTO this_duration_rule,
1356            this_fine_rule,
1357            this_max_fine_rule
1358       duration_rule,
1359       recuring_fine_rule,
1360       max_fine_rule
1361       FROM action.find_circ_matrix_matchpoint(
1362         circ_lib,
1363         target_copy,
1364         usr,
1365         is_renewal
1366         );
1367     SELECT INTO rcd * FROM config.rule_circ_duration
1368       WHERE id = this_duration_rule;
1369     SELECT INTO rrf * FROM config.rule_recurring_fine
1370       WHERE id = this_fine_rule;
1371     SELECT INTO rmf * FROM config.rule_max_fine
1372       WHERE id = this_max_fine_rule;
1373
1374     -- Apply the rules to this circulation
1375     EXECUTE ('UPDATE ' || tablename || ' c
1376     SET
1377       duration_rule = rcd.name,
1378       recuring_fine_rule = rrf.name,
1379       max_fine_rule = rmf.name,
1380       duration = rcd.normal,
1381       recuring_fine = rrf.normal,
1382       max_fine =
1383         CASE rmf.is_percent
1384           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1385           ELSE rmf.amount
1386         END,
1387       renewal_remaining = rcd.max_renewals
1388     FROM
1389       config.rule_circ_duration rcd,
1390       config.rule_recuring_fine rrf,
1391       config.rule_max_fine rmf,
1392                         asset.copy ac
1393     WHERE
1394       rcd.id = ' || this_duration_rule || ' AND
1395       rrf.id = ' || this_fine_rule || ' AND
1396       rmf.id = ' || this_max_fine_rule || ' AND
1397                         ac.id = c.target_copy AND
1398       c.id = ' || circ || ';');
1399
1400     -- Keep track of where we are in the process
1401     n := n + 1;
1402     IF (n % 100 = 0) THEN
1403       RAISE INFO '%', n || ' of ' || n_circs
1404         || ' (' || (100*n/n_circs) || '%) circs updated.';
1405     END IF;
1406
1407   END LOOP;
1408
1409   RETURN;
1410 END;
1411
1412 $$ LANGUAGE plpgsql;
1413
1414 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1415
1416 -- Usage:
1417 --
1418 --   First make sure the circ matrix is loaded and the circulations
1419 --   have been staged to the extent possible (but at the very least
1420 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1421 --   circ modifiers must also be in place.
1422 --
1423 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1424 --
1425
1426 DECLARE
1427   circ_lib             INT;
1428   target_copy          INT;
1429   usr                  INT;
1430   is_renewal           BOOLEAN;
1431   this_duration_rule   INT;
1432   this_fine_rule       INT;
1433   this_max_fine_rule   INT;
1434   rcd                  config.rule_circ_duration%ROWTYPE;
1435   rrf                  config.rule_recurring_fine%ROWTYPE;
1436   rmf                  config.rule_max_fine%ROWTYPE;
1437   circ                 INT;
1438   n                    INT := 0;
1439   n_circs              INT;
1440
1441 BEGIN
1442
1443   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1444
1445   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1446
1447     -- Fetch the correct rules for this circulation
1448     EXECUTE ('
1449       SELECT
1450         circ_lib,
1451         target_copy,
1452         usr,
1453         CASE
1454           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1455           ELSE FALSE
1456         END
1457       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1458       INTO circ_lib, target_copy, usr, is_renewal ;
1459     SELECT
1460       INTO this_duration_rule,
1461            this_fine_rule,
1462            this_max_fine_rule
1463       (matchpoint).duration_rule,
1464       (matchpoint).recurring_fine_rule,
1465       (matchpoint).max_fine_rule
1466       FROM action.find_circ_matrix_matchpoint(
1467         circ_lib,
1468         target_copy,
1469         usr,
1470         is_renewal
1471         );
1472     SELECT INTO rcd * FROM config.rule_circ_duration
1473       WHERE id = this_duration_rule;
1474     SELECT INTO rrf * FROM config.rule_recurring_fine
1475       WHERE id = this_fine_rule;
1476     SELECT INTO rmf * FROM config.rule_max_fine
1477       WHERE id = this_max_fine_rule;
1478
1479     -- Apply the rules to this circulation
1480     EXECUTE ('UPDATE ' || tablename || ' c
1481     SET
1482       duration_rule = rcd.name,
1483       recurring_fine_rule = rrf.name,
1484       max_fine_rule = rmf.name,
1485       duration = rcd.normal,
1486       recurring_fine = rrf.normal,
1487       max_fine =
1488         CASE rmf.is_percent
1489           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1490           ELSE rmf.amount
1491         END,
1492       renewal_remaining = rcd.max_renewals,
1493       grace_period = rrf.grace_period
1494     FROM
1495       config.rule_circ_duration rcd,
1496       config.rule_recurring_fine rrf,
1497       config.rule_max_fine rmf,
1498                         asset.copy ac
1499     WHERE
1500       rcd.id = ' || this_duration_rule || ' AND
1501       rrf.id = ' || this_fine_rule || ' AND
1502       rmf.id = ' || this_max_fine_rule || ' AND
1503                         ac.id = c.target_copy AND
1504       c.id = ' || circ || ';');
1505
1506     -- Keep track of where we are in the process
1507     n := n + 1;
1508     IF (n % 100 = 0) THEN
1509       RAISE INFO '%', n || ' of ' || n_circs
1510         || ' (' || (100*n/n_circs) || '%) circs updated.';
1511     END IF;
1512
1513   END LOOP;
1514
1515   RETURN;
1516 END;
1517
1518 $$ LANGUAGE plpgsql;
1519
1520 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1521
1522 -- Usage:
1523 --
1524 --   First make sure the circ matrix is loaded and the circulations
1525 --   have been staged to the extent possible (but at the very least
1526 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1527 --   circ modifiers must also be in place.
1528 --
1529 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1530 --
1531
1532 DECLARE
1533   circ_lib             INT;
1534   target_copy          INT;
1535   usr                  INT;
1536   is_renewal           BOOLEAN;
1537   this_duration_rule   INT;
1538   this_fine_rule       INT;
1539   this_max_fine_rule   INT;
1540   rcd                  config.rule_circ_duration%ROWTYPE;
1541   rrf                  config.rule_recurring_fine%ROWTYPE;
1542   rmf                  config.rule_max_fine%ROWTYPE;
1543   n                    INT := 0;
1544   n_circs              INT := 1;
1545
1546 BEGIN
1547
1548   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1549
1550   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1551
1552     -- Fetch the correct rules for this circulation
1553     EXECUTE ('
1554       SELECT
1555         circ_lib,
1556         target_copy,
1557         usr,
1558         CASE
1559           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1560           ELSE FALSE
1561         END
1562       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1563       INTO circ_lib, target_copy, usr, is_renewal ;
1564     SELECT
1565       INTO this_duration_rule,
1566            this_fine_rule,
1567            this_max_fine_rule
1568       (matchpoint).duration_rule,
1569       (matchpoint).recurring_fine_rule,
1570       (matchpoint).max_fine_rule
1571       FROM action.find_circ_matrix_matchpoint(
1572         circ_lib,
1573         target_copy,
1574         usr,
1575         is_renewal
1576         );
1577     SELECT INTO rcd * FROM config.rule_circ_duration
1578       WHERE id = this_duration_rule;
1579     SELECT INTO rrf * FROM config.rule_recurring_fine
1580       WHERE id = this_fine_rule;
1581     SELECT INTO rmf * FROM config.rule_max_fine
1582       WHERE id = this_max_fine_rule;
1583
1584     -- Apply the rules to this circulation
1585     EXECUTE ('UPDATE ' || tablename || ' c
1586     SET
1587       duration_rule = rcd.name,
1588       recurring_fine_rule = rrf.name,
1589       max_fine_rule = rmf.name,
1590       duration = rcd.normal,
1591       recurring_fine = rrf.normal,
1592       max_fine =
1593         CASE rmf.is_percent
1594           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1595           ELSE rmf.amount
1596         END,
1597       renewal_remaining = rcd.max_renewals,
1598       grace_period = rrf.grace_period
1599     FROM
1600       config.rule_circ_duration rcd,
1601       config.rule_recurring_fine rrf,
1602       config.rule_max_fine rmf,
1603                         asset.copy ac
1604     WHERE
1605       rcd.id = ' || this_duration_rule || ' AND
1606       rrf.id = ' || this_fine_rule || ' AND
1607       rmf.id = ' || this_max_fine_rule || ' AND
1608                         ac.id = c.target_copy AND
1609       c.id = ' || circ || ';');
1610
1611     -- Keep track of where we are in the process
1612     n := n + 1;
1613     IF (n % 100 = 0) THEN
1614       RAISE INFO '%', n || ' of ' || n_circs
1615         || ' (' || (100*n/n_circs) || '%) circs updated.';
1616     END IF;
1617
1618   --END LOOP;
1619
1620   RETURN;
1621 END;
1622
1623 $$ LANGUAGE plpgsql;
1624
1625
1626
1627
1628 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1629
1630 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1631 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1632
1633 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1634 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1635
1636 DECLARE
1637         c                    TEXT := schemaname || '.asset_copy_legacy';
1638         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1639         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1640         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1641         stat_cat                                                 INT;
1642   stat_cat_entry       INT;
1643
1644 BEGIN
1645
1646   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1647
1648                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1649
1650                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1651                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1652                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1653
1654   END LOOP;
1655
1656   RETURN;
1657 END;
1658
1659 $$ LANGUAGE plpgsql;
1660
1661 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1662
1663 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1664 --        This will assign standing penalties as needed.
1665
1666 DECLARE
1667   org_unit  INT;
1668   usr       INT;
1669
1670 BEGIN
1671
1672   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1673
1674     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1675
1676       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1677
1678     END LOOP;
1679
1680   END LOOP;
1681
1682   RETURN;
1683
1684 END;
1685
1686 $$ LANGUAGE plpgsql;
1687
1688
1689 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1690
1691 BEGIN
1692   INSERT INTO metabib.metarecord (fingerprint, master_record)
1693     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1694       FROM  biblio.record_entry b
1695       WHERE NOT b.deleted
1696         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)
1697         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1698       ORDER BY b.fingerprint, b.quality DESC;
1699   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1700     SELECT  m.id, r.id
1701       FROM  biblio.record_entry r
1702       JOIN  metabib.metarecord m USING (fingerprint)
1703      WHERE  NOT r.deleted;
1704 END;
1705
1706 $$ LANGUAGE plpgsql;
1707
1708
1709 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1710
1711 BEGIN
1712   INSERT INTO metabib.metarecord (fingerprint, master_record)
1713     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1714       FROM  biblio.record_entry b
1715       WHERE NOT b.deleted
1716         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)
1717         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1718       ORDER BY b.fingerprint, b.quality DESC;
1719   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1720     SELECT  m.id, r.id
1721       FROM  biblio.record_entry r
1722         JOIN metabib.metarecord m USING (fingerprint)
1723       WHERE NOT r.deleted
1724         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);
1725 END;
1726
1727 $$ LANGUAGE plpgsql;
1728
1729
1730 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1731
1732 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1733 --        Then SELECT migration_tools.create_cards('m_foo');
1734
1735 DECLARE
1736         u                    TEXT := schemaname || '.actor_usr_legacy';
1737         c                    TEXT := schemaname || '.actor_card';
1738
1739 BEGIN
1740
1741         EXECUTE ('DELETE FROM ' || c || ';');
1742         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1743         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1744
1745   RETURN;
1746
1747 END;
1748
1749 $$ LANGUAGE plpgsql;
1750
1751
1752 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1753
1754   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1755
1756   my ($marcxml, $shortname) = @_;
1757
1758   use MARC::Record;
1759   use MARC::File::XML;
1760
1761   my $xml = $marcxml;
1762
1763   eval {
1764     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1765
1766     foreach my $field ( $marc->field('856') ) {
1767       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1768            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1769         $field->add_subfields( '9' => $shortname );
1770                                 $field->update( ind2 => '0');
1771       }
1772     }
1773
1774     $xml = $marc->as_xml_record;
1775     $xml =~ s/^<\?.+?\?>$//mo;
1776     $xml =~ s/\n//sgo;
1777     $xml =~ s/>\s+</></sgo;
1778   };
1779
1780   return $xml;
1781
1782 $$ LANGUAGE PLPERLU STABLE;
1783
1784 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1785
1786   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1787
1788   my ($marcxml, $shortname) = @_;
1789
1790   use MARC::Record;
1791   use MARC::File::XML;
1792
1793   my $xml = $marcxml;
1794
1795   eval {
1796     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1797
1798     foreach my $field ( $marc->field('856') ) {
1799       if ( ! $field->as_string('9') ) {
1800         $field->add_subfields( '9' => $shortname );
1801       }
1802     }
1803
1804     $xml = $marc->as_xml_record;
1805     $xml =~ s/^<\?.+?\?>$//mo;
1806     $xml =~ s/\n//sgo;
1807     $xml =~ s/>\s+</></sgo;
1808   };
1809
1810   return $xml;
1811
1812 $$ LANGUAGE PLPERLU STABLE;
1813
1814
1815 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1816
1817 DECLARE
1818   old_volume   BIGINT;
1819   new_volume   BIGINT;
1820   bib          BIGINT;
1821   owner        INTEGER;
1822   old_label    TEXT;
1823   remainder    BIGINT;
1824
1825 BEGIN
1826
1827   -- Bail out if asked to change the label to ##URI##
1828   IF new_label = '##URI##' THEN
1829     RETURN;
1830   END IF;
1831
1832   -- Gather information
1833   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1834   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1835   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1836
1837   -- Bail out if the label already is ##URI##
1838   IF old_label = '##URI##' THEN
1839     RETURN;
1840   END IF;
1841
1842   -- Bail out if the call number label is already correct
1843   IF new_volume = old_volume THEN
1844     RETURN;
1845   END IF;
1846
1847   -- Check whether we already have a destination volume available
1848   SELECT id INTO new_volume FROM asset.call_number
1849     WHERE
1850       record = bib AND
1851       owning_lib = owner AND
1852       label = new_label AND
1853       NOT deleted;
1854
1855   -- Create destination volume if needed
1856   IF NOT FOUND THEN
1857     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1858       VALUES (1, 1, bib, owner, new_label, cn_class);
1859     SELECT id INTO new_volume FROM asset.call_number
1860       WHERE
1861         record = bib AND
1862         owning_lib = owner AND
1863         label = new_label AND
1864         NOT deleted;
1865   END IF;
1866
1867   -- Move copy to destination
1868   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1869
1870   -- Delete source volume if it is now empty
1871   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1872   IF NOT FOUND THEN
1873     DELETE FROM asset.call_number WHERE id = old_volume;
1874   END IF;
1875
1876 END;
1877
1878 $$ LANGUAGE plpgsql;
1879
1880 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1881
1882         my $input = $_[0];
1883         my %zipdata;
1884
1885         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1886
1887         while (<FH>) {
1888                 chomp;
1889                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1890                 $zipdata{$zip} = [$city, $state, $county];
1891         }
1892
1893         if (defined $zipdata{$input}) {
1894                 my ($city, $state, $county) = @{$zipdata{$input}};
1895                 return [$city, $state, $county];
1896         } elsif (defined $zipdata{substr $input, 0, 5}) {
1897                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1898                 return [$city, $state, $county];
1899         } else {
1900                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1901         }
1902
1903 $$ LANGUAGE PLPERLU STABLE;
1904
1905 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1906
1907 DECLARE
1908   ou  INT;
1909         org_unit_depth INT;
1910         ou_parent INT;
1911         parent_depth INT;
1912   errors_found BOOLEAN;
1913         ou_shortname TEXT;
1914         parent_shortname TEXT;
1915         ou_type_name TEXT;
1916         parent_type TEXT;
1917         type_id INT;
1918         type_depth INT;
1919         type_parent INT;
1920         type_parent_depth INT;
1921         proper_parent TEXT;
1922
1923 BEGIN
1924
1925         errors_found := FALSE;
1926
1927 -- Checking actor.org_unit_type
1928
1929         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1930
1931                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1932                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1933
1934                 IF type_parent IS NOT NULL THEN
1935
1936                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1937
1938                         IF type_depth - type_parent_depth <> 1 THEN
1939                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1940                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1941                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1942                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1943                                 errors_found := TRUE;
1944
1945                         END IF;
1946
1947                 END IF;
1948
1949         END LOOP;
1950
1951 -- Checking actor.org_unit
1952
1953   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1954
1955                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1956                 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;
1957                 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;
1958                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1959                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1960                 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;
1961                 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;
1962
1963                 IF ou_parent IS NOT NULL THEN
1964
1965                         IF      (org_unit_depth - parent_depth <> 1) OR (
1966                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1967                         ) THEN
1968                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1969                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1970                                 errors_found := TRUE;
1971                         END IF;
1972
1973                 END IF;
1974
1975   END LOOP;
1976
1977         IF NOT errors_found THEN
1978                 RAISE INFO 'No errors found.';
1979         END IF;
1980
1981   RETURN;
1982
1983 END;
1984
1985 $$ LANGUAGE plpgsql;
1986
1987
1988 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1989
1990 BEGIN
1991
1992         DELETE FROM asset.opac_visible_copies;
1993
1994         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1995                 SELECT DISTINCT
1996                         cp.id, cp.circ_lib, cn.record
1997                 FROM
1998                         asset.copy cp
1999                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
2000                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2001                         JOIN asset.copy_location cl ON (cp.location = cl.id)
2002                         JOIN config.copy_status cs ON (cp.status = cs.id)
2003                         JOIN biblio.record_entry b ON (cn.record = b.id)
2004                 WHERE
2005                         NOT cp.deleted AND
2006                         NOT cn.deleted AND
2007                         NOT b.deleted AND
2008                         cs.opac_visible AND
2009                         cl.opac_visible AND
2010                         cp.opac_visible AND
2011                         a.opac_visible AND
2012                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2013
2014 END;
2015
2016 $$ LANGUAGE plpgsql;
2017
2018
2019 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2020
2021 DECLARE
2022   old_volume     BIGINT;
2023   new_volume     BIGINT;
2024   bib            BIGINT;
2025   old_owning_lib INTEGER;
2026         old_label      TEXT;
2027   remainder      BIGINT;
2028
2029 BEGIN
2030
2031   -- Gather information
2032   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2033   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2034   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2035
2036         -- Bail out if the new_owning_lib is not the ID of an org_unit
2037         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2038                 RAISE WARNING
2039                         '% is not a valid actor.org_unit ID; no change made.',
2040                                 new_owning_lib;
2041                 RETURN;
2042         END IF;
2043
2044   -- Bail out discreetly if the owning_lib is already correct
2045   IF new_owning_lib = old_owning_lib THEN
2046     RETURN;
2047   END IF;
2048
2049   -- Check whether we already have a destination volume available
2050   SELECT id INTO new_volume FROM asset.call_number
2051     WHERE
2052       record = bib AND
2053       owning_lib = new_owning_lib AND
2054       label = old_label AND
2055       NOT deleted;
2056
2057   -- Create destination volume if needed
2058   IF NOT FOUND THEN
2059     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2060       VALUES (1, 1, bib, new_owning_lib, old_label);
2061     SELECT id INTO new_volume FROM asset.call_number
2062       WHERE
2063         record = bib AND
2064         owning_lib = new_owning_lib AND
2065         label = old_label AND
2066         NOT deleted;
2067   END IF;
2068
2069   -- Move copy to destination
2070   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2071
2072   -- Delete source volume if it is now empty
2073   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2074   IF NOT FOUND THEN
2075     DELETE FROM asset.call_number WHERE id = old_volume;
2076   END IF;
2077
2078 END;
2079
2080 $$ LANGUAGE plpgsql;
2081
2082
2083 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2084
2085 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2086
2087 DECLARE
2088         new_owning_lib  INTEGER;
2089
2090 BEGIN
2091
2092         -- Parse the new_owner as an org unit ID or shortname
2093         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2094                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2095                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2096         ELSIF new_owner ~ E'^[0-9]+$' THEN
2097                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2098                         RAISE INFO
2099                                 '%',
2100                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2101                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2102                         new_owning_lib := new_owner::INTEGER;
2103                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2104                 END IF;
2105         ELSE
2106                 RAISE WARNING
2107                         '% is not a valid actor.org_unit shortname or ID; no change made.',
2108                         new_owning_lib;
2109                 RETURN;
2110         END IF;
2111
2112 END;
2113
2114 $$ LANGUAGE plpgsql;
2115
2116 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2117
2118 use MARC::Record;
2119 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2120 use MARC::Charset;
2121
2122 MARC::Charset->assume_unicode(1);
2123
2124 my $xml = shift;
2125
2126 eval {
2127     my $r = MARC::Record->new_from_xml( $xml );
2128     my $output_xml = $r->as_xml_record();
2129 };
2130 if ($@) {
2131     return 0;
2132 } else {
2133     return 1;
2134 }
2135
2136 $func$ LANGUAGE PLPERLU;
2137 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2138
2139 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2140 BEGIN
2141    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2142            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2143            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2144    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2145            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2146            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2147    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2148            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2149            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2150    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2151            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2152            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2153    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2154            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2155            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2156    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2157            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2158            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2159    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2160            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2161            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2162    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2163    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2164    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2165    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2166    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2167 END;
2168 $FUNC$ LANGUAGE PLPGSQL;
2169
2170 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2171 BEGIN
2172    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2173    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2174    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2175    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2176    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2177    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2178    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2179
2180    -- import any new circ rules
2181    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2182    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2183    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2184    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2185
2186    -- and permission groups
2187    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2188
2189 END;
2190 $FUNC$ LANGUAGE PLPGSQL;
2191
2192
2193 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$
2194 DECLARE
2195     name TEXT;
2196     loopq TEXT;
2197     existsq TEXT;
2198     ct INTEGER;
2199     cols TEXT[];
2200     copyst TEXT;
2201 BEGIN
2202     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2203     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2204     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2205     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2206     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2207     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2208     FOR name IN EXECUTE loopq LOOP
2209        EXECUTE existsq INTO ct USING name;
2210        IF ct = 0 THEN
2211            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2212            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2213                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2214            EXECUTE copyst USING name;
2215        END IF;
2216     END LOOP;
2217 END;
2218 $FUNC$ LANGUAGE PLPGSQL;
2219
2220 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2221
2222 use strict;
2223 use warnings;
2224
2225 use MARC::Record;
2226 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2227 use MARC::Charset;
2228
2229 MARC::Charset->assume_unicode(1);
2230
2231 my $target_xml = shift;
2232 my $source_xml = shift;
2233 my $tags = shift;
2234
2235 my $target;
2236 my $source;
2237
2238 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2239 if ($@) {
2240     return;
2241 }
2242 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2243 if ($@) {
2244     return;
2245 }
2246
2247 my $source_id = $source->subfield('901', 'c');
2248 $source_id = $source->subfield('903', 'a') unless $source_id;
2249 my $target_id = $target->subfield('901', 'c');
2250 $target_id = $target->subfield('903', 'a') unless $target_id;
2251
2252 my %existing_fields;
2253 foreach my $tag (@$tags) {
2254     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2255     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2256     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2257     if (@to_add) {
2258         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2259     }
2260 }
2261
2262 my $xml = $target->as_xml_record;
2263 $xml =~ s/^<\?.+?\?>$//mo;
2264 $xml =~ s/\n//sgo;
2265 $xml =~ s/>\s+</></sgo;
2266
2267 return $xml;
2268
2269 $func$ LANGUAGE PLPERLU;
2270 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.';
2271
2272 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2273
2274 use strict;
2275 use warnings;
2276
2277 use MARC::Record;
2278 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2279 use Text::CSV;
2280
2281 my $in_tags = shift;
2282 my $in_values = shift;
2283
2284 # hack-and-slash parsing of array-passed-as-string;
2285 # this can go away once everybody is running Postgres 9.1+
2286 my $csv = Text::CSV->new({binary => 1});
2287 $in_tags =~ s/^{//;
2288 $in_tags =~ s/}$//;
2289 my $status = $csv->parse($in_tags);
2290 my $tags = [ $csv->fields() ];
2291 $in_values =~ s/^{//;
2292 $in_values =~ s/}$//;
2293 $status = $csv->parse($in_values);
2294 my $values = [ $csv->fields() ];
2295
2296 my $marc = MARC::Record->new();
2297
2298 $marc->leader('00000nam a22000007  4500');
2299 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2300
2301 foreach my $i (0..$#$tags) {
2302     my ($tag, $sf);
2303     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2304         $tag = $1;
2305         $sf = $2;
2306         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2307     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2308         $tag = $1;
2309         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2310     }
2311 }
2312
2313 my $xml = $marc->as_xml_record;
2314 $xml =~ s/^<\?.+?\?>$//mo;
2315 $xml =~ s/\n//sgo;
2316 $xml =~ s/>\s+</></sgo;
2317
2318 return $xml;
2319
2320 $func$ LANGUAGE PLPERLU;
2321 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2322 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2323 The second argument is an array of text containing the values to plug into each field.
2324 If the value for a given field is NULL or the empty string, it is not inserted.
2325 $$;
2326
2327 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2328
2329 my ($marcxml, $tag, $pos, $value) = @_;
2330
2331 use MARC::Record;
2332 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2333 use MARC::Charset;
2334 use strict;
2335
2336 MARC::Charset->assume_unicode(1);
2337
2338 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2339 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2340 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2341 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2342
2343 my $xml = $marcxml;
2344 eval {
2345     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2346
2347     foreach my $field ($marc->field($tag)) {
2348         $field->update("ind$pos" => $value);
2349     }
2350     $xml = $marc->as_xml_record;
2351     $xml =~ s/^<\?.+?\?>$//mo;
2352     $xml =~ s/\n//sgo;
2353     $xml =~ s/>\s+</></sgo;
2354 };
2355 return $xml;
2356
2357 $func$ LANGUAGE PLPERLU;
2358
2359 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2360 The first argument is a MARCXML string.
2361 The second argument is a MARC tag.
2362 The third argument is the indicator position, either 1 or 2.
2363 The fourth argument is the character to set the indicator value to.
2364 All occurences of the specified field will be changed.
2365 The function returns the revised MARCXML string.$$;
2366
2367 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2368     username TEXT,
2369     password TEXT,
2370     org TEXT,
2371     perm_group TEXT,
2372     first_name TEXT DEFAULT '',
2373     last_name TEXT DEFAULT ''
2374 ) RETURNS VOID AS $func$
2375 BEGIN
2376     RAISE NOTICE '%', org ;
2377     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2378     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2379     FROM   actor.org_unit aou, permission.grp_tree pgt
2380     WHERE  aou.shortname = org
2381     AND    pgt.name = perm_group;
2382 END
2383 $func$
2384 LANGUAGE PLPGSQL;
2385
2386 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2387 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2388     DECLARE
2389         target_event_def ALIAS FOR $1;
2390         orgs ALIAS FOR $2;
2391     BEGIN
2392         DROP TABLE IF EXISTS new_atevdefs;
2393         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2394         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2395             INSERT INTO action_trigger.event_definition (
2396                 active
2397                 ,owner
2398                 ,name
2399                 ,hook
2400                 ,validator
2401                 ,reactor
2402                 ,cleanup_success
2403                 ,cleanup_failure
2404                 ,delay
2405                 ,max_delay
2406                 ,usr_field
2407                 ,opt_in_setting
2408                 ,delay_field
2409                 ,group_field
2410                 ,template
2411                 ,granularity
2412                 ,repeat_delay
2413             ) SELECT
2414                 'f'
2415                 ,orgs[i]
2416                 ,name || ' (clone of '||target_event_def||')'
2417                 ,hook
2418                 ,validator
2419                 ,reactor
2420                 ,cleanup_success
2421                 ,cleanup_failure
2422                 ,delay
2423                 ,max_delay
2424                 ,usr_field
2425                 ,opt_in_setting
2426                 ,delay_field
2427                 ,group_field
2428                 ,template
2429                 ,granularity
2430                 ,repeat_delay
2431             FROM
2432                 action_trigger.event_definition
2433             WHERE
2434                 id = target_event_def
2435             ;
2436             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2437             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2438             INSERT INTO action_trigger.environment (
2439                 event_def
2440                 ,path
2441                 ,collector
2442                 ,label
2443             ) SELECT
2444                 currval('action_trigger.event_definition_id_seq')
2445                 ,path
2446                 ,collector
2447                 ,label
2448             FROM
2449                 action_trigger.environment
2450             WHERE
2451                 event_def = target_event_def
2452             ;
2453             INSERT INTO action_trigger.event_params (
2454                 event_def
2455                 ,param
2456                 ,value
2457             ) SELECT
2458                 currval('action_trigger.event_definition_id_seq')
2459                 ,param
2460                 ,value
2461             FROM
2462                 action_trigger.event_params
2463             WHERE
2464                 event_def = target_event_def
2465             ;
2466         END LOOP;
2467         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);
2468     END;
2469 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2470
2471 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2472 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2473     DECLARE
2474         target_event_def ALIAS FOR $1;
2475         orgs ALIAS FOR $2;
2476         new_interval ALIAS FOR $3;
2477     BEGIN
2478         DROP TABLE IF EXISTS new_atevdefs;
2479         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2480         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2481             INSERT INTO action_trigger.event_definition (
2482                 active
2483                 ,owner
2484                 ,name
2485                 ,hook
2486                 ,validator
2487                 ,reactor
2488                 ,cleanup_success
2489                 ,cleanup_failure
2490                 ,delay
2491                 ,max_delay
2492                 ,usr_field
2493                 ,opt_in_setting
2494                 ,delay_field
2495                 ,group_field
2496                 ,template
2497                 ,granularity
2498                 ,repeat_delay
2499             ) SELECT
2500                 'f'
2501                 ,orgs[i]
2502                 ,name || ' (clone of '||target_event_def||')'
2503                 ,hook
2504                 ,validator
2505                 ,reactor
2506                 ,cleanup_success
2507                 ,cleanup_failure
2508                 ,new_interval
2509                 ,max_delay
2510                 ,usr_field
2511                 ,opt_in_setting
2512                 ,delay_field
2513                 ,group_field
2514                 ,template
2515                 ,granularity
2516                 ,repeat_delay
2517             FROM
2518                 action_trigger.event_definition
2519             WHERE
2520                 id = target_event_def
2521             ;
2522             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2523             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2524             INSERT INTO action_trigger.environment (
2525                 event_def
2526                 ,path
2527                 ,collector
2528                 ,label
2529             ) SELECT
2530                 currval('action_trigger.event_definition_id_seq')
2531                 ,path
2532                 ,collector
2533                 ,label
2534             FROM
2535                 action_trigger.environment
2536             WHERE
2537                 event_def = target_event_def
2538             ;
2539             INSERT INTO action_trigger.event_params (
2540                 event_def
2541                 ,param
2542                 ,value
2543             ) SELECT
2544                 currval('action_trigger.event_definition_id_seq')
2545                 ,param
2546                 ,value
2547             FROM
2548                 action_trigger.event_params
2549             WHERE
2550                 event_def = target_event_def
2551             ;
2552         END LOOP;
2553         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);
2554     END;
2555 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2556
2557 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2558     my ($marcxml) = @_;
2559
2560     use MARC::Record;
2561     use MARC::File::XML;
2562     use MARC::Field;
2563
2564     my $field;
2565     eval {
2566         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2567         $field = $marc->leader();
2568     };
2569     return $field;
2570 $$ LANGUAGE PLPERLU STABLE;
2571
2572 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2573     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2574
2575     use MARC::Record;
2576     use MARC::File::XML;
2577     use MARC::Field;
2578
2579     my $field;
2580     eval {
2581         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2582         $field = $marc->field($tag);
2583     };
2584     return $field->as_string($subfield,$delimiter);
2585 $$ LANGUAGE PLPERLU STABLE;
2586
2587 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2588     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2589
2590     use MARC::Record;
2591     use MARC::File::XML;
2592     use MARC::Field;
2593
2594     my @fields;
2595     eval {
2596         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2597         @fields = $marc->field($tag);
2598     };
2599     my @texts;
2600     foreach my $field (@fields) {
2601         push @texts, $field->as_string($subfield,$delimiter);
2602     }
2603     return \@texts;
2604 $$ LANGUAGE PLPERLU STABLE;
2605
2606 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2607     SELECT action.find_hold_matrix_matchpoint(
2608         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2609         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2610         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2611         (SELECT usr FROM action.hold_request WHERE id = $1),
2612         (SELECT requestor FROM action.hold_request WHERE id = $1)
2613     );
2614 $$ LANGUAGE SQL;
2615
2616 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2617     SELECT action.hold_request_permit_test(
2618         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2619         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2620         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2621         (SELECT usr FROM action.hold_request WHERE id = $1),
2622         (SELECT requestor FROM action.hold_request WHERE id = $1)
2623     );
2624 $$ LANGUAGE SQL;
2625
2626 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2627     SELECT action.find_circ_matrix_matchpoint(
2628         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2629         (SELECT target_copy FROM action.circulation WHERE id = $1),
2630         (SELECT usr FROM action.circulation WHERE id = $1),
2631         (SELECT COALESCE(
2632                 NULLIF(phone_renewal,false),
2633                 NULLIF(desk_renewal,false),
2634                 NULLIF(opac_renewal,false),
2635                 false
2636             ) FROM action.circulation WHERE id = $1
2637         )
2638     );
2639 $$ LANGUAGE SQL;
2640
2641 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2642     DECLARE
2643         test ALIAS FOR $1;
2644     BEGIN
2645         IF NOT test THEN
2646             RAISE EXCEPTION 'assertion';
2647         END IF;
2648     END;
2649 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2650
2651 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2652     DECLARE
2653         test ALIAS FOR $1;
2654         msg ALIAS FOR $2;
2655     BEGIN
2656         IF NOT test THEN
2657             RAISE EXCEPTION '%', msg;
2658         END IF;
2659     END;
2660 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2661
2662 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2663     DECLARE
2664         test ALIAS FOR $1;
2665         fail_msg ALIAS FOR $2;
2666         success_msg ALIAS FOR $3;
2667     BEGIN
2668         IF NOT test THEN
2669             RAISE EXCEPTION '%', fail_msg;
2670         END IF;
2671         RETURN success_msg;
2672     END;
2673 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2674
2675 -- push bib sequence and return starting value for reserved range
2676 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2677     DECLARE
2678         bib_count ALIAS FOR $1;
2679         output BIGINT;
2680     BEGIN
2681         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2682         FOR output IN
2683             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2684         LOOP
2685             RETURN output;
2686         END LOOP;
2687     END;
2688 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2689
2690 -- set a new salted password
2691
2692 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2693     DECLARE
2694         usr_id              ALIAS FOR $1;
2695         plain_passwd        ALIAS FOR $2;
2696         plain_salt          TEXT;
2697         md5_passwd          TEXT;
2698     BEGIN
2699
2700         SELECT actor.create_salt('main') INTO plain_salt;
2701
2702         SELECT MD5(plain_passwd) INTO md5_passwd;
2703
2704         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2705
2706         RETURN TRUE;
2707
2708     END;
2709 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2710
2711
2712 -- convenience functions for handling copy_location maps
2713
2714 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2715     DECLARE
2716         table_schema ALIAS FOR $1;
2717         table_name ALIAS FOR $2;
2718         org_shortname ALIAS FOR $3;
2719         org_range ALIAS FOR $4;
2720         proceed BOOLEAN;
2721         org INTEGER;
2722         org_list INTEGER[];
2723         o INTEGER;
2724     BEGIN
2725         EXECUTE 'SELECT EXISTS (
2726             SELECT 1
2727             FROM information_schema.columns
2728             WHERE table_schema = $1
2729             AND table_name = $2
2730             and column_name = ''desired_shelf''
2731         )' INTO proceed USING table_schema, table_name;
2732         IF NOT proceed THEN
2733             RAISE EXCEPTION 'Missing column desired_shelf';
2734         END IF;
2735
2736         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2737         IF org IS NULL THEN
2738             RAISE EXCEPTION 'Cannot find org by shortname';
2739         END IF;
2740
2741         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2742
2743         EXECUTE 'ALTER TABLE '
2744             || quote_ident(table_name)
2745             || ' DROP COLUMN IF EXISTS x_shelf';
2746         EXECUTE 'ALTER TABLE '
2747             || quote_ident(table_name)
2748             || ' ADD COLUMN x_shelf INTEGER';
2749
2750         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2751             || ' SET x_shelf = id FROM asset_copy_location b'
2752             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2753             || ' AND b.owning_lib = $1'
2754         USING org;
2755
2756         FOREACH o IN ARRAY org_list LOOP
2757             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2758                 || ' SET x_shelf = id FROM asset.copy_location b'
2759                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2760                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2761             USING o;
2762         END LOOP;
2763
2764         EXECUTE 'SELECT migration_tools.assert(
2765             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2766             ''Cannot find a desired location'',
2767             ''Found all desired locations''
2768         );';
2769
2770     END;
2771 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2772
2773 -- convenience functions for handling circmod maps
2774
2775 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2776     DECLARE
2777         table_schema ALIAS FOR $1;
2778         table_name ALIAS FOR $2;
2779         proceed BOOLEAN;
2780     BEGIN
2781         EXECUTE 'SELECT EXISTS (
2782             SELECT 1
2783             FROM information_schema.columns
2784             WHERE table_schema = $1
2785             AND table_name = $2
2786             and column_name = ''desired_circmod''
2787         )' INTO proceed USING table_schema, table_name;
2788         IF NOT proceed THEN
2789             RAISE EXCEPTION 'Missing column desired_circmod';
2790         END IF;
2791
2792         EXECUTE 'ALTER TABLE '
2793             || quote_ident(table_name)
2794             || ' DROP COLUMN IF EXISTS x_circmod';
2795         EXECUTE 'ALTER TABLE '
2796             || quote_ident(table_name)
2797             || ' ADD COLUMN x_circmod TEXT';
2798
2799         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2800             || ' SET x_circmod = code FROM config.circ_modifier b'
2801             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
2802
2803         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2804             || ' SET x_circmod = code FROM config.circ_modifier b'
2805             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2806             || ' AND x_circmod IS NULL';
2807
2808         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2809             || ' SET x_circmod = code FROM config.circ_modifier b'
2810             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2811             || ' AND x_circmod IS NULL';
2812
2813         EXECUTE 'SELECT migration_tools.assert(
2814             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2815             ''Cannot find a desired circulation modifier'',
2816             ''Found all desired circulation modifiers''
2817         );';
2818
2819     END;
2820 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2821
2822 -- convenience function for handling desired_not_migrate
2823
2824 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2825     DECLARE
2826         table_schema ALIAS FOR $1;
2827         table_name ALIAS FOR $2;
2828         proceed BOOLEAN;
2829     BEGIN
2830         EXECUTE 'SELECT EXISTS (
2831             SELECT 1
2832             FROM information_schema.columns
2833             WHERE table_schema = $1
2834             AND table_name = $2
2835             and column_name = ''desired_not_migrate''
2836         )' INTO proceed USING table_schema, table_name;
2837         IF NOT proceed THEN
2838             RAISE EXCEPTION 'Missing column desired_not_migrate';
2839         END IF;
2840
2841         EXECUTE 'ALTER TABLE '
2842             || quote_ident(table_name)
2843             || ' DROP COLUMN IF EXISTS x_migrate';
2844         EXECUTE 'ALTER TABLE '
2845             || quote_ident(table_name)
2846             || ' ADD COLUMN x_migrate BOOLEAN';
2847
2848         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2849             || ' SET x_migrate = CASE'
2850             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2851             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2852             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2853             || ' END';
2854
2855         EXECUTE 'SELECT migration_tools.assert(
2856             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
2857             ''Not all desired_not_migrate values understood'',
2858             ''All desired_not_migrate values understood''
2859         );';
2860
2861     END;
2862 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2863
2864 -- convenience function for handling desired_profile
2865
2866 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
2867     DECLARE
2868         table_schema ALIAS FOR $1;
2869         table_name ALIAS FOR $2;
2870         proceed BOOLEAN;
2871     BEGIN
2872         EXECUTE 'SELECT EXISTS (
2873             SELECT 1
2874             FROM information_schema.columns
2875             WHERE table_schema = $1
2876             AND table_name = $2
2877             and column_name = ''desired_profile''
2878         )' INTO proceed USING table_schema, table_name;
2879         IF NOT proceed THEN
2880             RAISE EXCEPTION 'Missing column desired_profile';
2881         END IF;
2882
2883         EXECUTE 'ALTER TABLE '
2884             || quote_ident(table_name)
2885             || ' DROP COLUMN IF EXISTS x_profile';
2886         EXECUTE 'ALTER TABLE '
2887             || quote_ident(table_name)
2888             || ' ADD COLUMN x_profile INTEGER';
2889
2890         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2891             || ' SET x_profile = id FROM permission.grp_tree b'
2892             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
2893
2894         EXECUTE 'SELECT migration_tools.assert(
2895             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
2896             ''Cannot find a desired profile'',
2897             ''Found all desired profiles''
2898         );';
2899
2900     END;
2901 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2902
2903 -- convenience function for handling desired actor stat cats
2904
2905 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2906     DECLARE
2907         table_schema ALIAS FOR $1;
2908         table_name ALIAS FOR $2;
2909         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2910         org_shortname ALIAS FOR $4;
2911         proceed BOOLEAN;
2912         org INTEGER;
2913         org_list INTEGER[];
2914         sc TEXT;
2915         sce TEXT;
2916     BEGIN
2917
2918         SELECT 'desired_sc' || field_suffix INTO sc;
2919         SELECT 'desired_sce' || field_suffix INTO sce;
2920
2921         EXECUTE 'SELECT EXISTS (
2922             SELECT 1
2923             FROM information_schema.columns
2924             WHERE table_schema = $1
2925             AND table_name = $2
2926             and column_name = $3
2927         )' INTO proceed USING table_schema, table_name, sc;
2928         IF NOT proceed THEN
2929             RAISE EXCEPTION 'Missing column %', sc;
2930         END IF;
2931         EXECUTE 'SELECT EXISTS (
2932             SELECT 1
2933             FROM information_schema.columns
2934             WHERE table_schema = $1
2935             AND table_name = $2
2936             and column_name = $3
2937         )' INTO proceed USING table_schema, table_name, sce;
2938         IF NOT proceed THEN
2939             RAISE EXCEPTION 'Missing column %', sce;
2940         END IF;
2941
2942         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2943         IF org IS NULL THEN
2944             RAISE EXCEPTION 'Cannot find org by shortname';
2945         END IF;
2946         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2947
2948         -- caller responsible for their own truncates though we try to prevent duplicates
2949         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
2950             SELECT DISTINCT
2951                  $1
2952                 ,BTRIM('||sc||')
2953             FROM
2954                 ' || quote_ident(table_name) || '
2955             WHERE
2956                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2957                 AND NOT EXISTS (
2958                     SELECT id
2959                     FROM actor.stat_cat
2960                     WHERE owner = ANY ($2)
2961                     AND name = BTRIM('||sc||')
2962                 )
2963                 AND NOT EXISTS (
2964                     SELECT id
2965                     FROM actor_stat_cat
2966                     WHERE owner = ANY ($2)
2967                     AND name = BTRIM('||sc||')
2968                 )
2969             ORDER BY 2;'
2970         USING org, org_list;
2971
2972         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
2973             SELECT DISTINCT
2974                 COALESCE(
2975                     (SELECT id
2976                         FROM actor.stat_cat
2977                         WHERE owner = ANY ($2)
2978                         AND BTRIM('||sc||') = BTRIM(name))
2979                    ,(SELECT id
2980                         FROM actor_stat_cat
2981                         WHERE owner = ANY ($2)
2982                         AND BTRIM('||sc||') = BTRIM(name))
2983                 )
2984                 ,$1
2985                 ,BTRIM('||sce||')
2986             FROM
2987                 ' || quote_ident(table_name) || '
2988             WHERE
2989                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2990                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
2991                 AND NOT EXISTS (
2992                     SELECT id
2993                     FROM actor.stat_cat_entry
2994                     WHERE stat_cat = (
2995                         SELECT id
2996                         FROM actor.stat_cat
2997                         WHERE owner = ANY ($2)
2998                         AND BTRIM('||sc||') = BTRIM(name)
2999                     ) AND value = BTRIM('||sce||')
3000                 )
3001                 AND NOT EXISTS (
3002                     SELECT id
3003                     FROM actor_stat_cat_entry
3004                     WHERE stat_cat = (
3005                         SELECT id
3006                         FROM actor_stat_cat
3007                         WHERE owner = ANY ($2)
3008                         AND BTRIM('||sc||') = BTRIM(name)
3009                     ) AND value = BTRIM('||sce||')
3010                 )
3011             ORDER BY 1,3;'
3012         USING org, org_list;
3013     END;
3014 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3015
3016 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3017     DECLARE
3018         table_schema ALIAS FOR $1;
3019         table_name ALIAS FOR $2;
3020         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3021         org_shortname ALIAS FOR $4;
3022         proceed BOOLEAN;
3023         org INTEGER;
3024         org_list INTEGER[];
3025         o INTEGER;
3026         sc TEXT;
3027         sce TEXT;
3028     BEGIN
3029         SELECT 'desired_sc' || field_suffix INTO sc;
3030         SELECT 'desired_sce' || field_suffix INTO sce;
3031         EXECUTE 'SELECT EXISTS (
3032             SELECT 1
3033             FROM information_schema.columns
3034             WHERE table_schema = $1
3035             AND table_name = $2
3036             and column_name = $3
3037         )' INTO proceed USING table_schema, table_name, sc;
3038         IF NOT proceed THEN
3039             RAISE EXCEPTION 'Missing column %', sc;
3040         END IF;
3041         EXECUTE 'SELECT EXISTS (
3042             SELECT 1
3043             FROM information_schema.columns
3044             WHERE table_schema = $1
3045             AND table_name = $2
3046             and column_name = $3
3047         )' INTO proceed USING table_schema, table_name, sce;
3048         IF NOT proceed THEN
3049             RAISE EXCEPTION 'Missing column %', sce;
3050         END IF;
3051
3052         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3053         IF org IS NULL THEN
3054             RAISE EXCEPTION 'Cannot find org by shortname';
3055         END IF;
3056
3057         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3058
3059         EXECUTE 'ALTER TABLE '
3060             || quote_ident(table_name)
3061             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3062         EXECUTE 'ALTER TABLE '
3063             || quote_ident(table_name)
3064             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3065         EXECUTE 'ALTER TABLE '
3066             || quote_ident(table_name)
3067             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3068         EXECUTE 'ALTER TABLE '
3069             || quote_ident(table_name)
3070             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3071
3072
3073         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3074             SET
3075                 x_sc' || field_suffix || ' = id
3076             FROM
3077                 (SELECT id, name, owner FROM actor_stat_cat
3078                     UNION SELECT id, name, owner FROM actor.stat_cat) u
3079             WHERE
3080                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3081                 AND u.owner = ANY ($1);'
3082         USING org_list;
3083
3084         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3085             SET
3086                 x_sce' || field_suffix || ' = id
3087             FROM
3088                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3089                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3090             WHERE
3091                     u.stat_cat = x_sc' || field_suffix || '
3092                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3093                 AND u.owner = ANY ($1);'
3094         USING org_list;
3095
3096         EXECUTE 'SELECT migration_tools.assert(
3097             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3098             ''Cannot find a desired stat cat'',
3099             ''Found all desired stat cats''
3100         );';
3101
3102         EXECUTE 'SELECT migration_tools.assert(
3103             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3104             ''Cannot find a desired stat cat entry'',
3105             ''Found all desired stat cat entries''
3106         );';
3107
3108     END;
3109 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3110