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.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,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,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'' );' );
 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 );' );
 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 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.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
 356     DECLARE
 357         city_state_zip TEXT := $1;
 358         city TEXT := '';
 359         state TEXT := '';
 360         zip TEXT := '';
 361     BEGIN
 362         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;
 363         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 364         IF city_state_zip ~ ',' THEN
 365             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
 366             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
 367         ELSE
 368             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN
 369                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' );
 370                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' );
 371             ELSE
 372                 IF city_state_zip ~ E'^\\S+$'  THEN
 373                     city := city_state_zip;
 374                     state := 'N/A';
 375                 ELSE
 376                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 377                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 378                 END IF;
 379             END IF;
 380         END IF;
 381         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
 382     END;
 383 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 384
 385 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
 386     DECLARE
 387         n TEXT := o;
 388     BEGIN
 389         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
 390             IF o::BIGINT < t THEN
 391                 n = o::BIGINT + t;
 392             END IF;
 393         END IF;
 394
 395         RETURN n;
 396     END;
 397 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 398
 399 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
 400     DECLARE
 401         migration_schema ALIAS FOR $1;
 402         output TEXT;
 403     BEGIN
 404         FOR output IN
 405             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
 406         LOOP
 407             RETURN output;
 408         END LOOP;
 409     END;
 410 $$ LANGUAGE PLPGSQL STRICT STABLE;
 411
 412 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
 413     DECLARE
 414         migration_schema ALIAS FOR $1;
 415         output TEXT;
 416     BEGIN
 417         FOR output IN
 418             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
 419         LOOP
 420             RETURN output;
 421         END LOOP;
 422     END;
 423 $$ LANGUAGE PLPGSQL STRICT STABLE;
 424
 425 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
 426     DECLARE
 427         migration_schema ALIAS FOR $1;
 428         output TEXT;
 429     BEGIN
 430         FOR output IN
 431             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
 432         LOOP
 433             RETURN output;
 434         END LOOP;
 435     END;
 436 $$ LANGUAGE PLPGSQL STRICT STABLE;
 437
 438 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
 439     DECLARE
 440         migration_schema ALIAS FOR $1;
 441         output TEXT;
 442     BEGIN
 443         FOR output IN
 444             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
 445         LOOP
 446             RETURN output;
 447         END LOOP;
 448     END;
 449 $$ LANGUAGE PLPGSQL STRICT STABLE;
 450
 451 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
 452     DECLARE
 453         migration_schema ALIAS FOR $1;
 454         profile_map TEXT;
 455         patron_table ALIAS FOR $2;
 456         default_patron_profile ALIAS FOR $3;
 457         sql TEXT;
 458         sql_update TEXT;
 459         sql_where1 TEXT := '';
 460         sql_where2 TEXT := '';
 461         sql_where3 TEXT := '';
 462         output RECORD;
 463     BEGIN
 464         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
 465         FOR output IN
 466             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
 467         LOOP
 468             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
 469             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);
 470             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);
 471             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);
 472             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,'') || ';';
 473             --RAISE INFO 'sql = %', sql;
 474             PERFORM migration_tools.exec( $1, sql );
 475         END LOOP;
 476         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
 477         BEGIN
 478             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
 479         EXCEPTION
 480             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
 481         END;
 482     END;
 483 $$ LANGUAGE PLPGSQL STRICT STABLE;
 484
 485 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
 486     DECLARE
 487         migration_schema ALIAS FOR $1;
 488         field_map TEXT;
 489         item_table ALIAS FOR $2;
 490         sql TEXT;
 491         sql_update TEXT;
 492         sql_where1 TEXT := '';
 493         sql_where2 TEXT := '';
 494         sql_where3 TEXT := '';
 495         output RECORD;
 496     BEGIN
 497         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
 498         FOR output IN
 499             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 500         LOOP
 501             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 ';
 502             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);
 503             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);
 504             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);
 505             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,'') || ';';
 506             --RAISE INFO 'sql = %', sql;
 507             PERFORM migration_tools.exec( $1, sql );
 508         END LOOP;
 509         BEGIN
 510             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
 511         EXCEPTION
 512             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
 513         END;
 514     END;
 515 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 516
 517 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
 518     DECLARE
 519         migration_schema ALIAS FOR $1;
 520         base_copy_location_map TEXT;
 521         item_table ALIAS FOR $2;
 522         sql TEXT;
 523         sql_update TEXT;
 524         sql_where1 TEXT := '';
 525         sql_where2 TEXT := '';
 526         sql_where3 TEXT := '';
 527         output RECORD;
 528     BEGIN
 529         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
 530         FOR output IN
 531             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
 532         LOOP
 533             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
 534             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);
 535             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);
 536             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);
 537             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,'') || ';';
 538             --RAISE INFO 'sql = %', sql;
 539             PERFORM migration_tools.exec( $1, sql );
 540         END LOOP;
 541         BEGIN
 542             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
 543         EXCEPTION
 544             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
 545         END;
 546     END;
 547 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 548
 549 -- 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
 550 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
 551     DECLARE
 552         migration_schema ALIAS FOR $1;
 553         field_map TEXT;
 554         circ_table ALIAS FOR $2;
 555         item_table ALIAS FOR $3;
 556         patron_table ALIAS FOR $4;
 557         sql TEXT;
 558         sql_update TEXT;
 559         sql_where1 TEXT := '';
 560         sql_where2 TEXT := '';
 561         sql_where3 TEXT := '';
 562         sql_where4 TEXT := '';
 563         output RECORD;
 564     BEGIN
 565         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
 566         FOR output IN
 567             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 568         LOOP
 569             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 ';
 570             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);
 571             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);
 572             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);
 573             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);
 574             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,'') || ';';
 575             --RAISE INFO 'sql = %', sql;
 576             PERFORM migration_tools.exec( $1, sql );
 577         END LOOP;
 578         BEGIN
 579             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
 580         EXCEPTION
 581             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
 582         END;
 583     END;
 584 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 585
 586 -- expand_barcode
 587 --   $barcode      source barcode
 588 --   $prefix       prefix to add to barcode, NULL = add no prefix
 589 --   $maxlen       maximum length of barcode; default to 14 if left NULL
 590 --   $pad          padding string to apply to left of source barcode before adding
 591 --                 prefix and suffix; set to NULL or '' if no padding is desired
 592 --   $suffix       suffix to add to barcode, NULL = add no suffix
 593 --
 594 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
 595 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
 596 --
 597 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
 598     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
 599
 600     # default case
 601     return unless defined $barcode;
 602
 603     $prefix     = '' unless defined $prefix;
 604     $maxlen ||= 14;
 605     $pad        = '0' unless defined $pad;
 606     $suffix     = '' unless defined $suffix;
 607
 608     # bail out if adding prefix and suffix would bring new barcode over max length
 609     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
 610
 611     my $new_barcode = $barcode;
 612     if ($pad ne '') {
 613         my $pad_length = $maxlen - length($prefix) - length($suffix);
 614         if (length($barcode) < $pad_length) {
 615             # assuming we always want padding on the left
 616             # also assuming that it is possible to have the pad string be longer than 1 character
 617             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
 618         }
 619     }
 620
 621     # bail out if adding prefix and suffix would bring new barcode over max length
 622     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
 623
 624     return "$prefix$new_barcode$suffix";
 625 $$ LANGUAGE PLPERLU STABLE;
 626
 627 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
 628     DECLARE
 629         attempt_value ALIAS FOR $1;
 630         datatype ALIAS FOR $2;
 631         fail_value ALIAS FOR $3;
 632         output RECORD;
 633     BEGIN
 634         FOR output IN
 635             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
 636         LOOP
 637             RETURN output;
 638         END LOOP;
 639     EXCEPTION
 640         WHEN OTHERS THEN
 641             FOR output IN
 642                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;'
 643             LOOP
 644                 RETURN output;
 645             END LOOP;
 646     END;
 647 $$ LANGUAGE PLPGSQL STRICT STABLE;
 648
 649 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
 650     DECLARE
 651         attempt_value ALIAS FOR $1;
 652         fail_value ALIAS FOR $2;
 653         output DATE;
 654     BEGIN
 655         FOR output IN
 656             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
 657         LOOP
 658             RETURN output;
 659         END LOOP;
 660     EXCEPTION
 661         WHEN OTHERS THEN
 662             FOR output IN
 663                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 664             LOOP
 665                 RETURN output;
 666             END LOOP;
 667     END;
 668 $$ LANGUAGE PLPGSQL STRICT STABLE;
 669
 670 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
 671     DECLARE
 672         attempt_value ALIAS FOR $1;
 673         fail_value ALIAS FOR $2;
 674         output TIMESTAMPTZ;
 675     BEGIN
 676         FOR output IN
 677             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
 678         LOOP
 679             RETURN output;
 680         END LOOP;
 681     EXCEPTION
 682         WHEN OTHERS THEN
 683             FOR output IN
 684                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
 685             LOOP
 686                 RETURN output;
 687             END LOOP;
 688     END;
 689 $$ LANGUAGE PLPGSQL STRICT STABLE;
 690
 691 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 692     DECLARE
 693         attempt_value ALIAS FOR $1;
 694         fail_value ALIAS FOR $2;
 695         output NUMERIC(8,2);
 696     BEGIN
 697         FOR output IN
 698             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
 699         LOOP
 700             RETURN output;
 701         END LOOP;
 702     EXCEPTION
 703         WHEN OTHERS THEN
 704             FOR output IN
 705                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 706             LOOP
 707                 RETURN output;
 708             END LOOP;
 709     END;
 710 $$ LANGUAGE PLPGSQL STRICT STABLE;
 711
 712 -- add_codabar_checkdigit
 713 --   $barcode      source barcode
 714 --
 715 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
 716 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 717 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
 718 -- input string does not meet those requirements, it is returned unchanged.
 719 --
 720 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
 721     my $barcode = shift;
 722
 723     return $barcode if $barcode !~ /^\d{13,14}$/;
 724     $barcode = substr($barcode, 0, 13); # ignore 14th digit
 725     my @digits = split //, $barcode;
 726     my $total = 0;
 727     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
 728     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
 729     my $remainder = $total % 10;
 730     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
 731     return $barcode . $checkdigit;
 732 $$ LANGUAGE PLPERLU STRICT STABLE;
 733
 734 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
 735   DECLARE
 736     phone TEXT := $1;
 737     areacode TEXT := $2;
 738     temp TEXT := '';
 739     output TEXT := '';
 740     n_digits INTEGER := 0;
 741   BEGIN
 742     temp := phone;
 743     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
 744     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
 745     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
 746     IF n_digits = 7 AND areacode <> '' THEN
 747       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
 748       output := (areacode || '-' || temp);
 749     ELSE
 750       output := temp;
 751     END IF;
 752     RETURN output;
 753   END;
 754
 755 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 756
 757 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
 758   my ($marcxml, $pos, $value) = @_;
 759
 760   use MARC::Record;
 761   use MARC::File::XML;
 762
 763   my $xml = $marcxml;
 764   eval {
 765     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 766     my $leader = $marc->leader();
 767     substr($leader, $pos, 1) = $value;
 768     $marc->leader($leader);
 769     $xml = $marc->as_xml_record;
 770     $xml =~ s/^<\?.+?\?>$//mo;
 771     $xml =~ s/\n//sgo;
 772     $xml =~ s/>\s+</></sgo;
 773   };
 774   return $xml;
 775 $$ LANGUAGE PLPERLU STABLE;
 776
 777 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
 778   my ($marcxml, $pos, $value) = @_;
 779
 780   use MARC::Record;
 781   use MARC::File::XML;
 782
 783   my $xml = $marcxml;
 784   eval {
 785     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 786     my $f008 = $marc->field('008');
 787
 788     if ($f008) {
 789        my $field = $f008->data();
 790        substr($field, $pos, 1) = $value;
 791        $f008->update($field);
 792        $xml = $marc->as_xml_record;
 793        $xml =~ s/^<\?.+?\?>$//mo;
 794        $xml =~ s/\n//sgo;
 795        $xml =~ s/>\s+</></sgo;
 796     }
 797   };
 798   return $xml;
 799 $$ LANGUAGE PLPERLU STABLE;
 800
 801
 802 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
 803   DECLARE
 804     profile ALIAS FOR $1;
 805   BEGIN
 806     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
 807   END;
 808 $$ LANGUAGE PLPGSQL STRICT STABLE;
 809
 810
 811 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
 812   BEGIN
 813     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
 814   END;
 815 $$ LANGUAGE PLPGSQL STRICT STABLE;
 816
 817
 818 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
 819
 820   my ($marcxml, $tags) = @_;
 821
 822   use MARC::Record;
 823   use MARC::File::XML;
 824
 825   my $xml = $marcxml;
 826
 827   eval {
 828     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 829     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
 830
 831     my @incumbents = ();
 832
 833     foreach my $field ( $marc->fields() ) {
 834       push @incumbents, $field->as_formatted();
 835     }
 836
 837     foreach $field ( $to_insert->fields() ) {
 838       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
 839         $marc->insert_fields_ordered( ($field) );
 840       }
 841     }
 842
 843     $xml = $marc->as_xml_record;
 844     $xml =~ s/^<\?.+?\?>$//mo;
 845     $xml =~ s/\n//sgo;
 846     $xml =~ s/>\s+</></sgo;
 847   };
 848
 849   return $xml;
 850
 851 $$ LANGUAGE PLPERLU STABLE;
 852
 853 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
 854
 855 -- Usage:
 856 --
 857 --   First make sure the circ matrix is loaded and the circulations
 858 --   have been staged to the extent possible (but at the very least
 859 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 860 --   circ modifiers must also be in place.
 861 --
 862 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 863 --
 864
 865 DECLARE
 866   circ_lib             INT;
 867   target_copy          INT;
 868   usr                  INT;
 869   is_renewal           BOOLEAN;
 870   this_duration_rule   INT;
 871   this_fine_rule       INT;
 872   this_max_fine_rule   INT;
 873   rcd                  config.rule_circ_duration%ROWTYPE;
 874   rrf                  config.rule_recurring_fine%ROWTYPE;
 875   rmf                  config.rule_max_fine%ROWTYPE;
 876   circ                 INT;
 877   n                    INT := 0;
 878   n_circs              INT;
 879
 880 BEGIN
 881
 882   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 883
 884   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 885
 886     -- Fetch the correct rules for this circulation
 887     EXECUTE ('
 888       SELECT
 889         circ_lib,
 890         target_copy,
 891         usr,
 892         CASE
 893           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 894           ELSE FALSE
 895         END
 896       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 897       INTO circ_lib, target_copy, usr, is_renewal ;
 898     SELECT
 899       INTO this_duration_rule,
 900            this_fine_rule,
 901            this_max_fine_rule
 902       duration_rule,
 903       recurring_fine_rule,
 904       max_fine_rule
 905       FROM action.item_user_circ_test(
 906         circ_lib,
 907         target_copy,
 908         usr,
 909         is_renewal
 910         );
 911     SELECT INTO rcd * FROM config.rule_circ_duration
 912       WHERE id = this_duration_rule;
 913     SELECT INTO rrf * FROM config.rule_recurring_fine
 914       WHERE id = this_fine_rule;
 915     SELECT INTO rmf * FROM config.rule_max_fine
 916       WHERE id = this_max_fine_rule;
 917
 918     -- Apply the rules to this circulation
 919     EXECUTE ('UPDATE ' || tablename || ' c
 920     SET
 921       duration_rule = rcd.name,
 922       recurring_fine_rule = rrf.name,
 923       max_fine_rule = rmf.name,
 924       duration = rcd.normal,
 925       recurring_fine = rrf.normal,
 926       max_fine =
 927         CASE rmf.is_percent
 928           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 929           ELSE rmf.amount
 930         END,
 931       renewal_remaining = rcd.max_renewals
 932     FROM
 933       config.rule_circ_duration rcd,
 934       config.rule_recurring_fine rrf,
 935       config.rule_max_fine rmf,
 936                         asset.copy ac
 937     WHERE
 938       rcd.id = ' || this_duration_rule || ' AND
 939       rrf.id = ' || this_fine_rule || ' AND
 940       rmf.id = ' || this_max_fine_rule || ' AND
 941                         ac.id = c.target_copy AND
 942       c.id = ' || circ || ';');
 943
 944     -- Keep track of where we are in the process
 945     n := n + 1;
 946     IF (n % 100 = 0) THEN
 947       RAISE INFO '%', n || ' of ' || n_circs
 948         || ' (' || (100*n/n_circs) || '%) circs updated.';
 949     END IF;
 950
 951   END LOOP;
 952
 953   RETURN;
 954 END;
 955
 956 $$ LANGUAGE plpgsql;
 957
 958 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
 959
 960 -- Usage:
 961 --
 962 --   First make sure the circ matrix is loaded and the circulations
 963 --   have been staged to the extent possible (but at the very least
 964 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 965 --   circ modifiers must also be in place.
 966 --
 967 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 968 --
 969
 970 DECLARE
 971   circ_lib             INT;
 972   target_copy          INT;
 973   usr                  INT;
 974   is_renewal           BOOLEAN;
 975   this_duration_rule   INT;
 976   this_fine_rule       INT;
 977   this_max_fine_rule   INT;
 978   rcd                  config.rule_circ_duration%ROWTYPE;
 979   rrf                  config.rule_recurring_fine%ROWTYPE;
 980   rmf                  config.rule_max_fine%ROWTYPE;
 981   circ                 INT;
 982   n                    INT := 0;
 983   n_circs              INT;
 984
 985 BEGIN
 986
 987   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 988
 989   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 990
 991     -- Fetch the correct rules for this circulation
 992     EXECUTE ('
 993       SELECT
 994         circ_lib,
 995         target_copy,
 996         usr,
 997         CASE
 998           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 999           ELSE FALSE
1000         END
1001       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1002       INTO circ_lib, target_copy, usr, is_renewal ;
1003     SELECT
1004       INTO this_duration_rule,
1005            this_fine_rule,
1006            this_max_fine_rule
1007       duration_rule,
1008       recuring_fine_rule,
1009       max_fine_rule
1010       FROM action.find_circ_matrix_matchpoint(
1011         circ_lib,
1012         target_copy,
1013         usr,
1014         is_renewal
1015         );
1016     SELECT INTO rcd * FROM config.rule_circ_duration
1017       WHERE id = this_duration_rule;
1018     SELECT INTO rrf * FROM config.rule_recurring_fine
1019       WHERE id = this_fine_rule;
1020     SELECT INTO rmf * FROM config.rule_max_fine
1021       WHERE id = this_max_fine_rule;
1022
1023     -- Apply the rules to this circulation
1024     EXECUTE ('UPDATE ' || tablename || ' c
1025     SET
1026       duration_rule = rcd.name,
1027       recuring_fine_rule = rrf.name,
1028       max_fine_rule = rmf.name,
1029       duration = rcd.normal,
1030       recuring_fine = rrf.normal,
1031       max_fine =
1032         CASE rmf.is_percent
1033           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1034           ELSE rmf.amount
1035         END,
1036       renewal_remaining = rcd.max_renewals
1037     FROM
1038       config.rule_circ_duration rcd,
1039       config.rule_recuring_fine rrf,
1040       config.rule_max_fine rmf,
1041                         asset.copy ac
1042     WHERE
1043       rcd.id = ' || this_duration_rule || ' AND
1044       rrf.id = ' || this_fine_rule || ' AND
1045       rmf.id = ' || this_max_fine_rule || ' AND
1046                         ac.id = c.target_copy AND
1047       c.id = ' || circ || ';');
1048
1049     -- Keep track of where we are in the process
1050     n := n + 1;
1051     IF (n % 100 = 0) THEN
1052       RAISE INFO '%', n || ' of ' || n_circs
1053         || ' (' || (100*n/n_circs) || '%) circs updated.';
1054     END IF;
1055
1056   END LOOP;
1057
1058   RETURN;
1059 END;
1060
1061 $$ LANGUAGE plpgsql;
1062
1063 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1064
1065 -- Usage:
1066 --
1067 --   First make sure the circ matrix is loaded and the circulations
1068 --   have been staged to the extent possible (but at the very least
1069 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1070 --   circ modifiers must also be in place.
1071 --
1072 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1073 --
1074
1075 DECLARE
1076   circ_lib             INT;
1077   target_copy          INT;
1078   usr                  INT;
1079   is_renewal           BOOLEAN;
1080   this_duration_rule   INT;
1081   this_fine_rule       INT;
1082   this_max_fine_rule   INT;
1083   rcd                  config.rule_circ_duration%ROWTYPE;
1084   rrf                  config.rule_recurring_fine%ROWTYPE;
1085   rmf                  config.rule_max_fine%ROWTYPE;
1086   circ                 INT;
1087   n                    INT := 0;
1088   n_circs              INT;
1089
1090 BEGIN
1091
1092   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1093
1094   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1095
1096     -- Fetch the correct rules for this circulation
1097     EXECUTE ('
1098       SELECT
1099         circ_lib,
1100         target_copy,
1101         usr,
1102         CASE
1103           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1104           ELSE FALSE
1105         END
1106       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1107       INTO circ_lib, target_copy, usr, is_renewal ;
1108     SELECT
1109       INTO this_duration_rule,
1110            this_fine_rule,
1111            this_max_fine_rule
1112       (matchpoint).duration_rule,
1113       (matchpoint).recurring_fine_rule,
1114       (matchpoint).max_fine_rule
1115       FROM action.find_circ_matrix_matchpoint(
1116         circ_lib,
1117         target_copy,
1118         usr,
1119         is_renewal
1120         );
1121     SELECT INTO rcd * FROM config.rule_circ_duration
1122       WHERE id = this_duration_rule;
1123     SELECT INTO rrf * FROM config.rule_recurring_fine
1124       WHERE id = this_fine_rule;
1125     SELECT INTO rmf * FROM config.rule_max_fine
1126       WHERE id = this_max_fine_rule;
1127
1128     -- Apply the rules to this circulation
1129     EXECUTE ('UPDATE ' || tablename || ' c
1130     SET
1131       duration_rule = rcd.name,
1132       recurring_fine_rule = rrf.name,
1133       max_fine_rule = rmf.name,
1134       duration = rcd.normal,
1135       recurring_fine = rrf.normal,
1136       max_fine =
1137         CASE rmf.is_percent
1138           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1139           ELSE rmf.amount
1140         END,
1141       renewal_remaining = rcd.max_renewals,
1142       grace_period = rrf.grace_period
1143     FROM
1144       config.rule_circ_duration rcd,
1145       config.rule_recurring_fine rrf,
1146       config.rule_max_fine rmf,
1147                         asset.copy ac
1148     WHERE
1149       rcd.id = ' || this_duration_rule || ' AND
1150       rrf.id = ' || this_fine_rule || ' AND
1151       rmf.id = ' || this_max_fine_rule || ' AND
1152                         ac.id = c.target_copy AND
1153       c.id = ' || circ || ';');
1154
1155     -- Keep track of where we are in the process
1156     n := n + 1;
1157     IF (n % 100 = 0) THEN
1158       RAISE INFO '%', n || ' of ' || n_circs
1159         || ' (' || (100*n/n_circs) || '%) circs updated.';
1160     END IF;
1161
1162   END LOOP;
1163
1164   RETURN;
1165 END;
1166
1167 $$ LANGUAGE plpgsql;
1168
1169
1170
1171
1172 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1173
1174 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1175 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1176
1177 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1178 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1179
1180 DECLARE
1181         c                    TEXT := schemaname || '.asset_copy_legacy';
1182         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1183         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1184         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1185         stat_cat                                                 INT;
1186   stat_cat_entry       INT;
1187
1188 BEGIN
1189
1190   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1191
1192                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1193
1194                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1195                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1196                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1197
1198   END LOOP;
1199
1200   RETURN;
1201 END;
1202
1203 $$ LANGUAGE plpgsql;
1204
1205 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1206
1207 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1208 --        This will assign standing penalties as needed.
1209
1210 DECLARE
1211   org_unit  INT;
1212   usr       INT;
1213
1214 BEGIN
1215
1216   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1217
1218     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1219
1220       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1221
1222     END LOOP;
1223
1224   END LOOP;
1225
1226   RETURN;
1227
1228 END;
1229
1230 $$ LANGUAGE plpgsql;
1231
1232
1233 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1234
1235 BEGIN
1236   INSERT INTO metabib.metarecord (fingerprint, master_record)
1237     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1238       FROM  biblio.record_entry b
1239       WHERE NOT b.deleted
1240         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)
1241         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1242       ORDER BY b.fingerprint, b.quality DESC;
1243   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1244     SELECT  m.id, r.id
1245       FROM  biblio.record_entry r
1246       JOIN  metabib.metarecord m USING (fingerprint)
1247      WHERE  NOT r.deleted;
1248 END;
1249
1250 $$ LANGUAGE plpgsql;
1251
1252
1253 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1254
1255 BEGIN
1256   INSERT INTO metabib.metarecord (fingerprint, master_record)
1257     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1258       FROM  biblio.record_entry b
1259       WHERE NOT b.deleted
1260         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)
1261         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1262       ORDER BY b.fingerprint, b.quality DESC;
1263   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1264     SELECT  m.id, r.id
1265       FROM  biblio.record_entry r
1266         JOIN metabib.metarecord m USING (fingerprint)
1267       WHERE NOT r.deleted
1268         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);
1269 END;
1270
1271 $$ LANGUAGE plpgsql;
1272
1273
1274 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1275
1276 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1277 --        Then SELECT migration_tools.create_cards('m_foo');
1278
1279 DECLARE
1280         u                    TEXT := schemaname || '.actor_usr_legacy';
1281         c                    TEXT := schemaname || '.actor_card';
1282
1283 BEGIN
1284
1285         EXECUTE ('DELETE FROM ' || c || ';');
1286         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1287         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1288
1289   RETURN;
1290
1291 END;
1292
1293 $$ LANGUAGE plpgsql;
1294
1295
1296 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1297
1298   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1299
1300   my ($marcxml, $shortname) = @_;
1301
1302   use MARC::Record;
1303   use MARC::File::XML;
1304
1305   my $xml = $marcxml;
1306
1307   eval {
1308     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1309
1310     foreach my $field ( $marc->field('856') ) {
1311       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1312            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1313         $field->add_subfields( '9' => $shortname );
1314                                 $field->update( ind2 => '0');
1315       }
1316     }
1317
1318     $xml = $marc->as_xml_record;
1319     $xml =~ s/^<\?.+?\?>$//mo;
1320     $xml =~ s/\n//sgo;
1321     $xml =~ s/>\s+</></sgo;
1322   };
1323
1324   return $xml;
1325
1326 $$ LANGUAGE PLPERLU STABLE;
1327
1328 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1329
1330 DECLARE
1331   old_volume   BIGINT;
1332   new_volume   BIGINT;
1333   bib          BIGINT;
1334   owner        INTEGER;
1335   old_label    TEXT;
1336   remainder    BIGINT;
1337
1338 BEGIN
1339
1340   -- Bail out if asked to change the label to ##URI##
1341   IF new_label = '##URI##' THEN
1342     RETURN;
1343   END IF;
1344
1345   -- Gather information
1346   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1347   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1348   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1349
1350   -- Bail out if the label already is ##URI##
1351   IF old_label = '##URI##' THEN
1352     RETURN;
1353   END IF;
1354
1355   -- Bail out if the call number label is already correct
1356   IF new_volume = old_volume THEN
1357     RETURN;
1358   END IF;
1359
1360   -- Check whether we already have a destination volume available
1361   SELECT id INTO new_volume FROM asset.call_number
1362     WHERE
1363       record = bib AND
1364       owning_lib = owner AND
1365       label = new_label AND
1366       NOT deleted;
1367
1368   -- Create destination volume if needed
1369   IF NOT FOUND THEN
1370     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1371       VALUES (1, 1, bib, owner, new_label, cn_class);
1372     SELECT id INTO new_volume FROM asset.call_number
1373       WHERE
1374         record = bib AND
1375         owning_lib = owner AND
1376         label = new_label AND
1377         NOT deleted;
1378   END IF;
1379
1380   -- Move copy to destination
1381   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1382
1383   -- Delete source volume if it is now empty
1384   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1385   IF NOT FOUND THEN
1386     DELETE FROM asset.call_number WHERE id = old_volume;
1387   END IF;
1388
1389 END;
1390
1391 $$ LANGUAGE plpgsql;
1392
1393 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1394
1395         my $input = $_[0];
1396         my %zipdata;
1397
1398         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1399
1400         while (<FH>) {
1401                 chomp;
1402                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1403                 $zipdata{$zip} = [$city, $state, $county];
1404         }
1405
1406         if (defined $zipdata{$input}) {
1407                 my ($city, $state, $county) = @{$zipdata{$input}};
1408                 return [$city, $state, $county];
1409         } elsif (defined $zipdata{substr $input, 0, 5}) {
1410                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1411                 return [$city, $state, $county];
1412         } else {
1413                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1414         }
1415
1416 $$ LANGUAGE PLPERLU STABLE;
1417
1418 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1419
1420 DECLARE
1421   ou  INT;
1422         org_unit_depth INT;
1423         ou_parent INT;
1424         parent_depth INT;
1425   errors_found BOOLEAN;
1426         ou_shortname TEXT;
1427         parent_shortname TEXT;
1428         ou_type_name TEXT;
1429         parent_type TEXT;
1430         type_id INT;
1431         type_depth INT;
1432         type_parent INT;
1433         type_parent_depth INT;
1434         proper_parent TEXT;
1435
1436 BEGIN
1437
1438         errors_found := FALSE;
1439
1440 -- Checking actor.org_unit_type
1441
1442         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1443
1444                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1445                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1446
1447                 IF type_parent IS NOT NULL THEN
1448
1449                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1450
1451                         IF type_depth - type_parent_depth <> 1 THEN
1452                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1453                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1454                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1455                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1456                                 errors_found := TRUE;
1457
1458                         END IF;
1459
1460                 END IF;
1461
1462         END LOOP;
1463
1464 -- Checking actor.org_unit
1465
1466   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1467
1468                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1469                 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;
1470                 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;
1471                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1472                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1473                 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;
1474                 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;
1475
1476                 IF ou_parent IS NOT NULL THEN
1477
1478                         IF      (org_unit_depth - parent_depth <> 1) OR (
1479                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1480                         ) THEN
1481                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1482                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1483                                 errors_found := TRUE;
1484                         END IF;
1485
1486                 END IF;
1487
1488   END LOOP;
1489
1490         IF NOT errors_found THEN
1491                 RAISE INFO 'No errors found.';
1492         END IF;
1493
1494   RETURN;
1495
1496 END;
1497
1498 $$ LANGUAGE plpgsql;
1499
1500
1501 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1502
1503 BEGIN
1504
1505         DELETE FROM asset.opac_visible_copies;
1506
1507         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1508                 SELECT DISTINCT
1509                         cp.id, cp.circ_lib, cn.record
1510                 FROM
1511                         asset.copy cp
1512                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1513                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1514                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1515                         JOIN config.copy_status cs ON (cp.status = cs.id)
1516                         JOIN biblio.record_entry b ON (cn.record = b.id)
1517                 WHERE
1518                         NOT cp.deleted AND
1519                         NOT cn.deleted AND
1520                         NOT b.deleted AND
1521                         cs.opac_visible AND
1522                         cl.opac_visible AND
1523                         cp.opac_visible AND
1524                         a.opac_visible AND
1525                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1526
1527 END;
1528
1529 $$ LANGUAGE plpgsql;
1530
1531
1532 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1533
1534 DECLARE
1535   old_volume     BIGINT;
1536   new_volume     BIGINT;
1537   bib            BIGINT;
1538   old_owning_lib INTEGER;
1539         old_label      TEXT;
1540   remainder      BIGINT;
1541
1542 BEGIN
1543
1544   -- Gather information
1545   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1546   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1547   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1548
1549         -- Bail out if the new_owning_lib is not the ID of an org_unit
1550         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1551                 RAISE WARNING
1552                         '% is not a valid actor.org_unit ID; no change made.',
1553                                 new_owning_lib;
1554                 RETURN;
1555         END IF;
1556
1557   -- Bail out discreetly if the owning_lib is already correct
1558   IF new_owning_lib = old_owning_lib THEN
1559     RETURN;
1560   END IF;
1561
1562   -- Check whether we already have a destination volume available
1563   SELECT id INTO new_volume FROM asset.call_number
1564     WHERE
1565       record = bib AND
1566       owning_lib = new_owning_lib AND
1567       label = old_label AND
1568       NOT deleted;
1569
1570   -- Create destination volume if needed
1571   IF NOT FOUND THEN
1572     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1573       VALUES (1, 1, bib, new_owning_lib, old_label);
1574     SELECT id INTO new_volume FROM asset.call_number
1575       WHERE
1576         record = bib AND
1577         owning_lib = new_owning_lib AND
1578         label = old_label AND
1579         NOT deleted;
1580   END IF;
1581
1582   -- Move copy to destination
1583   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1584
1585   -- Delete source volume if it is now empty
1586   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1587   IF NOT FOUND THEN
1588     DELETE FROM asset.call_number WHERE id = old_volume;
1589   END IF;
1590
1591 END;
1592
1593 $$ LANGUAGE plpgsql;
1594
1595
1596 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1597
1598 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1599
1600 DECLARE
1601         new_owning_lib  INTEGER;
1602
1603 BEGIN
1604
1605         -- Parse the new_owner as an org unit ID or shortname
1606         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1607                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1608                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1609         ELSIF new_owner ~ E'^[0-9]+$' THEN
1610                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1611                         RAISE INFO
1612                                 '%',
1613                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1614                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1615                         new_owning_lib := new_owner::INTEGER;
1616                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1617                 END IF;
1618         ELSE
1619                 RAISE WARNING
1620                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1621                         new_owning_lib;
1622                 RETURN;
1623         END IF;
1624
1625 END;
1626
1627 $$ LANGUAGE plpgsql;
1628
1629 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1630
1631 use MARC::Record;
1632 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1633 use MARC::Charset;
1634
1635 MARC::Charset->assume_unicode(1);
1636
1637 my $xml = shift;
1638
1639 eval { my $r = MARC::Record->new_from_xml( $xml ); };
1640 if ($@) {
1641     return 0;
1642 } else {
1643     return 1;
1644 }
1645
1646 $func$ LANGUAGE PLPERLU;
1647 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1648
1649 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1650 BEGIN
1651    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1652            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1653            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1654    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1655            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1656            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1657    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1658            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1659            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1660    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1661            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1662            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1663    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1664            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1665            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1666    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1667            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1668            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
1669    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1670            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1671            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
1672    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
1673    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
1674    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
1675    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
1676    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
1677 END;
1678 $FUNC$ LANGUAGE PLPGSQL;
1679
1680 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1681 BEGIN
1682    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
1683    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
1684    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
1685    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
1686    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1687    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
1688    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
1689
1690    -- import any new circ rules
1691    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1692    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1693    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1694    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1695
1696    -- and permission groups
1697    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1698
1699 END;
1700 $FUNC$ LANGUAGE PLPGSQL;
1701
1702
1703 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$
1704 DECLARE
1705     name TEXT;
1706     loopq TEXT;
1707     existsq TEXT;
1708     ct INTEGER;
1709     cols TEXT[];
1710     copyst TEXT;
1711 BEGIN
1712     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1713     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1714     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
1715     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1716     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1717     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1718     FOR name IN EXECUTE loopq LOOP
1719        EXECUTE existsq INTO ct USING name;
1720        IF ct = 0 THEN
1721            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1722            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1723                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1724            EXECUTE copyst USING name;
1725        END IF;
1726     END LOOP;
1727 END;
1728 $FUNC$ LANGUAGE PLPGSQL;
1729
1730 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1731
1732 use strict;
1733 use warnings;
1734
1735 use MARC::Record;
1736 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1737 use MARC::Charset;
1738
1739 MARC::Charset->assume_unicode(1);
1740
1741 my $target_xml = shift;
1742 my $source_xml = shift;
1743 my $tags = shift;
1744
1745 my $target;
1746 my $source;
1747
1748 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1749 if ($@) {
1750     return;
1751 }
1752 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1753 if ($@) {
1754     return;
1755 }
1756
1757 my $source_id = $source->subfield('901', 'c');
1758 $source_id = $source->subfield('903', 'a') unless $source_id;
1759 my $target_id = $target->subfield('901', 'c');
1760 $target_id = $target->subfield('903', 'a') unless $target_id;
1761
1762 my %existing_fields;
1763 foreach my $tag (@$tags) {
1764     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1765     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1766     $target->insert_fields_ordered(map { $_->clone() } @to_add);
1767     if (@to_add) {
1768         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1769     }
1770 }
1771
1772 my $xml = $target->as_xml_record;
1773 $xml =~ s/^<\?.+?\?>$//mo;
1774 $xml =~ s/\n//sgo;
1775 $xml =~ s/>\s+</></sgo;
1776
1777 return $xml;
1778
1779 $func$ LANGUAGE PLPERLU;
1780 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.';
1781
1782 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1783
1784 use strict;
1785 use warnings;
1786
1787 use MARC::Record;
1788 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1789 use Text::CSV;
1790
1791 my $in_tags = shift;
1792 my $in_values = shift;
1793
1794 # hack-and-slash parsing of array-passed-as-string;
1795 # this can go away once everybody is running Postgres 9.1+
1796 my $csv = Text::CSV->new({binary => 1});
1797 $in_tags =~ s/^{//;
1798 $in_tags =~ s/}$//;
1799 my $status = $csv->parse($in_tags);
1800 my $tags = [ $csv->fields() ];
1801 $in_values =~ s/^{//;
1802 $in_values =~ s/}$//;
1803 $status = $csv->parse($in_values);
1804 my $values = [ $csv->fields() ];
1805
1806 my $marc = MARC::Record->new();
1807
1808 $marc->leader('00000nam a22000007  4500');
1809 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
1810
1811 foreach my $i (0..$#$tags) {
1812     my ($tag, $sf);
1813     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1814         $tag = $1;
1815         $sf = $2;
1816         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1817     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1818         $tag = $1;
1819         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1820     }
1821 }
1822
1823 my $xml = $marc->as_xml_record;
1824 $xml =~ s/^<\?.+?\?>$//mo;
1825 $xml =~ s/\n//sgo;
1826 $xml =~ s/>\s+</></sgo;
1827
1828 return $xml;
1829
1830 $func$ LANGUAGE PLPERLU;
1831 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
1832 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
1833 The second argument is an array of text containing the values to plug into each field.
1834 If the value for a given field is NULL or the empty string, it is not inserted.
1835 $$;
1836
1837 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1838
1839 my ($marcxml, $tag, $pos, $value) = @_;
1840
1841 use MARC::Record;
1842 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1843 use MARC::Charset;
1844 use strict;
1845
1846 MARC::Charset->assume_unicode(1);
1847
1848 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
1849 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
1850 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
1851 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
1852
1853 my $xml = $marcxml;
1854 eval {
1855     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1856
1857     foreach my $field ($marc->field($tag)) {
1858         $field->update("ind$pos" => $value);
1859     }
1860     $xml = $marc->as_xml_record;
1861     $xml =~ s/^<\?.+?\?>$//mo;
1862     $xml =~ s/\n//sgo;
1863     $xml =~ s/>\s+</></sgo;
1864 };
1865 return $xml;
1866
1867 $func$ LANGUAGE PLPERLU;
1868
1869 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
1870 The first argument is a MARCXML string.
1871 The second argument is a MARC tag.
1872 The third argument is the indicator position, either 1 or 2.
1873 The fourth argument is the character to set the indicator value to.
1874 All occurences of the specified field will be changed.
1875 The function returns the revised MARCXML string.$$;
1876
1877 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
1878     username TEXT,
1879     password TEXT,
1880     org TEXT,
1881     perm_group TEXT,
1882     first_name TEXT DEFAULT '',
1883     last_name TEXT DEFAULT ''
1884 ) RETURNS VOID AS $func$
1885 BEGIN
1886     RAISE NOTICE '%', org ;
1887     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
1888     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
1889     FROM   actor.org_unit aou, permission.grp_tree pgt
1890     WHERE  aou.shortname = org
1891     AND    pgt.name = perm_group;
1892 END
1893 $func$
1894 LANGUAGE PLPGSQL;