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