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