sql/base/base.sql

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