sql/base/base.sql

   1 -- Copyright 2009-2012, Equinox Software, Inc.
   2 --
   3 -- This program is free software; you can redistribute it and/or
   4 -- modify it under the terms of the GNU General Public License
   5 -- as published by the Free Software Foundation; either version 2
   6 -- of the License, or (at your option) any later version.
   7 --
   8 -- This program is distributed in the hope that it will be useful,
   9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
  10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11 -- GNU General Public License for more details.
  12 --
  13 -- You should have received a copy of the GNU General Public License
  14 -- along with this program; if not, write to the Free Software
  15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  16
  17 --------------------------------------------------------------------------
  18 -- An example of how to use:
  19 --
  20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
  21 -- \i base.sql
  22 -- SELECT migration_tools.init('foo');
  23 -- SELECT migration_tools.build('foo');
  24 -- SELECT * FROM foo.fields_requiring_mapping;
  25 -- \d foo.actor_usr
  26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS foo.asset_copy;
  27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
  28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
  29
  30 CREATE SCHEMA migration_tools;
  31
  32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
  33     DECLARE
  34         migration_schema ALIAS FOR $1;
  35         output  RECORD;
  36     BEGIN
  37         FOR output IN
  38             EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
  39         LOOP
  40             RETURN output.tables;
  41         END LOOP;
  42     END;
  43 $$ LANGUAGE PLPGSQL STRICT STABLE;
  44
  45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
  46     DECLARE
  47         migration_schema ALIAS FOR $1;
  48         output TEXT;
  49     BEGIN
  50         FOR output IN
  51             EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
  52         LOOP
  53             RETURN output;
  54         END LOOP;
  55     END;
  56 $$ LANGUAGE PLPGSQL STRICT STABLE;
  57
  58
  59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
  60     DECLARE
  61         migration_schema ALIAS FOR $1;
  62         sql ALIAS FOR $2;
  63         nrows ALIAS FOR $3;
  64     BEGIN
  65         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
  66     END;
  67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  68
  69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
  70     DECLARE
  71         migration_schema ALIAS FOR $1;
  72         sql ALIAS FOR $2;
  73         nrows INTEGER;
  74     BEGIN
  75         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  76         --RAISE INFO '%', sql;
  77         EXECUTE sql;
  78         GET DIAGNOSTICS nrows = ROW_COUNT;
  79         PERFORM migration_tools.log(migration_schema,sql,nrows);
  80     EXCEPTION
  81         WHEN OTHERS THEN
  82             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
  83     END;
  84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  85
  86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
  87     DECLARE
  88         migration_schema ALIAS FOR $1;
  89         sql ALIAS FOR $2;
  90         nrows INTEGER;
  91     BEGIN
  92         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  93         RAISE INFO 'debug_exec sql = %', sql;
  94         EXECUTE sql;
  95         GET DIAGNOSTICS nrows = ROW_COUNT;
  96         PERFORM migration_tools.log(migration_schema,sql,nrows);
  97     EXCEPTION
  98         WHEN OTHERS THEN
  99             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 100     END;
 101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 102
 103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
 104     DECLARE
 105         migration_schema ALIAS FOR $1;
 106         sql TEXT;
 107     BEGIN
 108         EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
 109         EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
 110         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
 111         BEGIN
 112             SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
 113             EXECUTE sql;
 114         EXCEPTION
 115             WHEN OTHERS THEN
 116                 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 117         END;
 118         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
 119         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
 120         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,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,prefix,suffix );' );
 223     END;
 224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 225
 226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
 227     DECLARE
 228         migration_schema ALIAS FOR $1;
 229         production_tables ALIAS FOR $2;
 230     BEGIN
 231         --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
 232         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
 233             PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
 234         END LOOP;
 235     END;
 236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 237
 238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
 239     DECLARE
 240         migration_schema ALIAS FOR $1;
 241         production_table ALIAS FOR $2;
 242         base_staging_table TEXT;
 243         columns RECORD;
 244     BEGIN
 245         base_staging_table = REPLACE( production_table, '.', '_' );
 246         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
 247         PERFORM migration_tools.exec( $1, 'CREATE 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(REPLACE(REPLACE(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 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 706     DECLARE
 707         attempt_value ALIAS FOR $1;
 708         fail_value ALIAS FOR $2;
 709         output NUMERIC(6,2);
 710     BEGIN
 711         FOR output IN
 712             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
 713         LOOP
 714             RETURN output;
 715         END LOOP;
 716     EXCEPTION
 717         WHEN OTHERS THEN
 718             FOR output IN
 719                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 720             LOOP
 721                 RETURN output;
 722             END LOOP;
 723     END;
 724 $$ LANGUAGE PLPGSQL STRICT STABLE;
 725
 726 -- add_codabar_checkdigit
 727 --   $barcode      source barcode
 728 --
 729 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
 730 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 731 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
 732 -- input string does not meet those requirements, it is returned unchanged.
 733 --
 734 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
 735     my $barcode = shift;
 736
 737     return $barcode if $barcode !~ /^\d{13,14}$/;
 738     $barcode = substr($barcode, 0, 13); # ignore 14th digit
 739     my @digits = split //, $barcode;
 740     my $total = 0;
 741     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
 742     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
 743     my $remainder = $total % 10;
 744     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
 745     return $barcode . $checkdigit;
 746 $$ LANGUAGE PLPERLU STRICT STABLE;
 747
 748 -- add_code39mod43_checkdigit
 749 --   $barcode      source barcode
 750 --
 751 -- If the source string is 13 or 14 characters long and contains only valid
 752 -- Code 39 mod 43 characters, adds or replaces the 14th
 753 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 754 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
 755 -- input string does not meet those requirements, it is returned unchanged.
 756 --
 757 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
 758     my $barcode = shift;
 759
 760     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
 761     $barcode = substr($barcode, 0, 13); # ignore 14th character
 762
 763     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
 764     my %nums = map { $valid_chars[$_] => $_ } (0..42);
 765
 766     my $total = 0;
 767     $total += $nums{$_} foreach split(//, $barcode);
 768     my $remainder = $total % 43;
 769     my $checkdigit = $valid_chars[$remainder];
 770     return $barcode . $checkdigit;
 771 $$ LANGUAGE PLPERLU STRICT STABLE;
 772
 773 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
 774   DECLARE
 775     phone TEXT := $1;
 776     areacode TEXT := $2;
 777     temp TEXT := '';
 778     output TEXT := '';
 779     n_digits INTEGER := 0;
 780   BEGIN
 781     temp := phone;
 782     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
 783     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
 784     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
 785     IF n_digits = 7 AND areacode <> '' THEN
 786       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
 787       output := (areacode || '-' || temp);
 788     ELSE
 789       output := temp;
 790     END IF;
 791     RETURN output;
 792   END;
 793
 794 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 795
 796 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
 797   my ($marcxml, $pos, $value) = @_;
 798
 799   use MARC::Record;
 800   use MARC::File::XML;
 801
 802   my $xml = $marcxml;
 803   eval {
 804     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 805     my $leader = $marc->leader();
 806     substr($leader, $pos, 1) = $value;
 807     $marc->leader($leader);
 808     $xml = $marc->as_xml_record;
 809     $xml =~ s/^<\?.+?\?>$//mo;
 810     $xml =~ s/\n//sgo;
 811     $xml =~ s/>\s+</></sgo;
 812   };
 813   return $xml;
 814 $$ LANGUAGE PLPERLU STABLE;
 815
 816 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
 817   my ($marcxml, $pos, $value) = @_;
 818
 819   use MARC::Record;
 820   use MARC::File::XML;
 821
 822   my $xml = $marcxml;
 823   eval {
 824     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 825     my $f008 = $marc->field('008');
 826
 827     if ($f008) {
 828        my $field = $f008->data();
 829        substr($field, $pos, 1) = $value;
 830        $f008->update($field);
 831        $xml = $marc->as_xml_record;
 832        $xml =~ s/^<\?.+?\?>$//mo;
 833        $xml =~ s/\n//sgo;
 834        $xml =~ s/>\s+</></sgo;
 835     }
 836   };
 837   return $xml;
 838 $$ LANGUAGE PLPERLU STABLE;
 839
 840
 841 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
 842   DECLARE
 843     profile ALIAS FOR $1;
 844   BEGIN
 845     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
 846   END;
 847 $$ LANGUAGE PLPGSQL STRICT STABLE;
 848
 849
 850 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
 851   BEGIN
 852     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
 853   END;
 854 $$ LANGUAGE PLPGSQL STRICT STABLE;
 855
 856
 857 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
 858
 859   my ($marcxml, $tags) = @_;
 860
 861   use MARC::Record;
 862   use MARC::File::XML;
 863
 864   my $xml = $marcxml;
 865
 866   eval {
 867     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 868     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
 869
 870     my @incumbents = ();
 871
 872     foreach my $field ( $marc->fields() ) {
 873       push @incumbents, $field->as_formatted();
 874     }
 875
 876     foreach $field ( $to_insert->fields() ) {
 877       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
 878         $marc->insert_fields_ordered( ($field) );
 879       }
 880     }
 881
 882     $xml = $marc->as_xml_record;
 883     $xml =~ s/^<\?.+?\?>$//mo;
 884     $xml =~ s/\n//sgo;
 885     $xml =~ s/>\s+</></sgo;
 886   };
 887
 888   return $xml;
 889
 890 $$ LANGUAGE PLPERLU STABLE;
 891
 892 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
 893
 894 -- Usage:
 895 --
 896 --   First make sure the circ matrix is loaded and the circulations
 897 --   have been staged to the extent possible (but at the very least
 898 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 899 --   circ modifiers must also be in place.
 900 --
 901 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 902 --
 903
 904 DECLARE
 905   circ_lib             INT;
 906   target_copy          INT;
 907   usr                  INT;
 908   is_renewal           BOOLEAN;
 909   this_duration_rule   INT;
 910   this_fine_rule       INT;
 911   this_max_fine_rule   INT;
 912   rcd                  config.rule_circ_duration%ROWTYPE;
 913   rrf                  config.rule_recurring_fine%ROWTYPE;
 914   rmf                  config.rule_max_fine%ROWTYPE;
 915   circ                 INT;
 916   n                    INT := 0;
 917   n_circs              INT;
 918
 919 BEGIN
 920
 921   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 922
 923   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 924
 925     -- Fetch the correct rules for this circulation
 926     EXECUTE ('
 927       SELECT
 928         circ_lib,
 929         target_copy,
 930         usr,
 931         CASE
 932           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 933           ELSE FALSE
 934         END
 935       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 936       INTO circ_lib, target_copy, usr, is_renewal ;
 937     SELECT
 938       INTO this_duration_rule,
 939            this_fine_rule,
 940            this_max_fine_rule
 941       duration_rule,
 942       recurring_fine_rule,
 943       max_fine_rule
 944       FROM action.item_user_circ_test(
 945         circ_lib,
 946         target_copy,
 947         usr,
 948         is_renewal
 949         );
 950     SELECT INTO rcd * FROM config.rule_circ_duration
 951       WHERE id = this_duration_rule;
 952     SELECT INTO rrf * FROM config.rule_recurring_fine
 953       WHERE id = this_fine_rule;
 954     SELECT INTO rmf * FROM config.rule_max_fine
 955       WHERE id = this_max_fine_rule;
 956
 957     -- Apply the rules to this circulation
 958     EXECUTE ('UPDATE ' || tablename || ' c
 959     SET
 960       duration_rule = rcd.name,
 961       recurring_fine_rule = rrf.name,
 962       max_fine_rule = rmf.name,
 963       duration = rcd.normal,
 964       recurring_fine = rrf.normal,
 965       max_fine =
 966         CASE rmf.is_percent
 967           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 968           ELSE rmf.amount
 969         END,
 970       renewal_remaining = rcd.max_renewals
 971     FROM
 972       config.rule_circ_duration rcd,
 973       config.rule_recurring_fine rrf,
 974       config.rule_max_fine rmf,
 975                         asset.copy ac
 976     WHERE
 977       rcd.id = ' || this_duration_rule || ' AND
 978       rrf.id = ' || this_fine_rule || ' AND
 979       rmf.id = ' || this_max_fine_rule || ' AND
 980                         ac.id = c.target_copy AND
 981       c.id = ' || circ || ';');
 982
 983     -- Keep track of where we are in the process
 984     n := n + 1;
 985     IF (n % 100 = 0) THEN
 986       RAISE INFO '%', n || ' of ' || n_circs
 987         || ' (' || (100*n/n_circs) || '%) circs updated.';
 988     END IF;
 989
 990   END LOOP;
 991
 992   RETURN;
 993 END;
 994
 995 $$ LANGUAGE plpgsql;
 996
 997 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
 998
 999 -- Usage:
1000 --
1001 --   First make sure the circ matrix is loaded and the circulations
1002 --   have been staged to the extent possible (but at the very least
1003 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1004 --   circ modifiers must also be in place.
1005 --
1006 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1007 --
1008
1009 DECLARE
1010   circ_lib             INT;
1011   target_copy          INT;
1012   usr                  INT;
1013   is_renewal           BOOLEAN;
1014   this_duration_rule   INT;
1015   this_fine_rule       INT;
1016   this_max_fine_rule   INT;
1017   rcd                  config.rule_circ_duration%ROWTYPE;
1018   rrf                  config.rule_recurring_fine%ROWTYPE;
1019   rmf                  config.rule_max_fine%ROWTYPE;
1020   circ                 INT;
1021   n                    INT := 0;
1022   n_circs              INT;
1023
1024 BEGIN
1025
1026   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1027
1028   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1029
1030     -- Fetch the correct rules for this circulation
1031     EXECUTE ('
1032       SELECT
1033         circ_lib,
1034         target_copy,
1035         usr,
1036         CASE
1037           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1038           ELSE FALSE
1039         END
1040       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1041       INTO circ_lib, target_copy, usr, is_renewal ;
1042     SELECT
1043       INTO this_duration_rule,
1044            this_fine_rule,
1045            this_max_fine_rule
1046       duration_rule,
1047       recuring_fine_rule,
1048       max_fine_rule
1049       FROM action.find_circ_matrix_matchpoint(
1050         circ_lib,
1051         target_copy,
1052         usr,
1053         is_renewal
1054         );
1055     SELECT INTO rcd * FROM config.rule_circ_duration
1056       WHERE id = this_duration_rule;
1057     SELECT INTO rrf * FROM config.rule_recurring_fine
1058       WHERE id = this_fine_rule;
1059     SELECT INTO rmf * FROM config.rule_max_fine
1060       WHERE id = this_max_fine_rule;
1061
1062     -- Apply the rules to this circulation
1063     EXECUTE ('UPDATE ' || tablename || ' c
1064     SET
1065       duration_rule = rcd.name,
1066       recuring_fine_rule = rrf.name,
1067       max_fine_rule = rmf.name,
1068       duration = rcd.normal,
1069       recuring_fine = rrf.normal,
1070       max_fine =
1071         CASE rmf.is_percent
1072           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1073           ELSE rmf.amount
1074         END,
1075       renewal_remaining = rcd.max_renewals
1076     FROM
1077       config.rule_circ_duration rcd,
1078       config.rule_recuring_fine rrf,
1079       config.rule_max_fine rmf,
1080                         asset.copy ac
1081     WHERE
1082       rcd.id = ' || this_duration_rule || ' AND
1083       rrf.id = ' || this_fine_rule || ' AND
1084       rmf.id = ' || this_max_fine_rule || ' AND
1085                         ac.id = c.target_copy AND
1086       c.id = ' || circ || ';');
1087
1088     -- Keep track of where we are in the process
1089     n := n + 1;
1090     IF (n % 100 = 0) THEN
1091       RAISE INFO '%', n || ' of ' || n_circs
1092         || ' (' || (100*n/n_circs) || '%) circs updated.';
1093     END IF;
1094
1095   END LOOP;
1096
1097   RETURN;
1098 END;
1099
1100 $$ LANGUAGE plpgsql;
1101
1102 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1103
1104 -- Usage:
1105 --
1106 --   First make sure the circ matrix is loaded and the circulations
1107 --   have been staged to the extent possible (but at the very least
1108 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1109 --   circ modifiers must also be in place.
1110 --
1111 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1112 --
1113
1114 DECLARE
1115   circ_lib             INT;
1116   target_copy          INT;
1117   usr                  INT;
1118   is_renewal           BOOLEAN;
1119   this_duration_rule   INT;
1120   this_fine_rule       INT;
1121   this_max_fine_rule   INT;
1122   rcd                  config.rule_circ_duration%ROWTYPE;
1123   rrf                  config.rule_recurring_fine%ROWTYPE;
1124   rmf                  config.rule_max_fine%ROWTYPE;
1125   circ                 INT;
1126   n                    INT := 0;
1127   n_circs              INT;
1128
1129 BEGIN
1130
1131   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1132
1133   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1134
1135     -- Fetch the correct rules for this circulation
1136     EXECUTE ('
1137       SELECT
1138         circ_lib,
1139         target_copy,
1140         usr,
1141         CASE
1142           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1143           ELSE FALSE
1144         END
1145       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1146       INTO circ_lib, target_copy, usr, is_renewal ;
1147     SELECT
1148       INTO this_duration_rule,
1149            this_fine_rule,
1150            this_max_fine_rule
1151       (matchpoint).duration_rule,
1152       (matchpoint).recurring_fine_rule,
1153       (matchpoint).max_fine_rule
1154       FROM action.find_circ_matrix_matchpoint(
1155         circ_lib,
1156         target_copy,
1157         usr,
1158         is_renewal
1159         );
1160     SELECT INTO rcd * FROM config.rule_circ_duration
1161       WHERE id = this_duration_rule;
1162     SELECT INTO rrf * FROM config.rule_recurring_fine
1163       WHERE id = this_fine_rule;
1164     SELECT INTO rmf * FROM config.rule_max_fine
1165       WHERE id = this_max_fine_rule;
1166
1167     -- Apply the rules to this circulation
1168     EXECUTE ('UPDATE ' || tablename || ' c
1169     SET
1170       duration_rule = rcd.name,
1171       recurring_fine_rule = rrf.name,
1172       max_fine_rule = rmf.name,
1173       duration = rcd.normal,
1174       recurring_fine = rrf.normal,
1175       max_fine =
1176         CASE rmf.is_percent
1177           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1178           ELSE rmf.amount
1179         END,
1180       renewal_remaining = rcd.max_renewals,
1181       grace_period = rrf.grace_period
1182     FROM
1183       config.rule_circ_duration rcd,
1184       config.rule_recurring_fine rrf,
1185       config.rule_max_fine rmf,
1186                         asset.copy ac
1187     WHERE
1188       rcd.id = ' || this_duration_rule || ' AND
1189       rrf.id = ' || this_fine_rule || ' AND
1190       rmf.id = ' || this_max_fine_rule || ' AND
1191                         ac.id = c.target_copy AND
1192       c.id = ' || circ || ';');
1193
1194     -- Keep track of where we are in the process
1195     n := n + 1;
1196     IF (n % 100 = 0) THEN
1197       RAISE INFO '%', n || ' of ' || n_circs
1198         || ' (' || (100*n/n_circs) || '%) circs updated.';
1199     END IF;
1200
1201   END LOOP;
1202
1203   RETURN;
1204 END;
1205
1206 $$ LANGUAGE plpgsql;
1207
1208
1209
1210
1211 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1212
1213 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1214 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1215
1216 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1217 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1218
1219 DECLARE
1220         c                    TEXT := schemaname || '.asset_copy_legacy';
1221         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1222         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1223         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1224         stat_cat                                                 INT;
1225   stat_cat_entry       INT;
1226
1227 BEGIN
1228
1229   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1230
1231                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1232
1233                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1234                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1235                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1236
1237   END LOOP;
1238
1239   RETURN;
1240 END;
1241
1242 $$ LANGUAGE plpgsql;
1243
1244 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1245
1246 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1247 --        This will assign standing penalties as needed.
1248
1249 DECLARE
1250   org_unit  INT;
1251   usr       INT;
1252
1253 BEGIN
1254
1255   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1256
1257     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1258
1259       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1260
1261     END LOOP;
1262
1263   END LOOP;
1264
1265   RETURN;
1266
1267 END;
1268
1269 $$ LANGUAGE plpgsql;
1270
1271
1272 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1273
1274 BEGIN
1275   INSERT INTO metabib.metarecord (fingerprint, master_record)
1276     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1277       FROM  biblio.record_entry b
1278       WHERE NOT b.deleted
1279         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)
1280         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1281       ORDER BY b.fingerprint, b.quality DESC;
1282   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1283     SELECT  m.id, r.id
1284       FROM  biblio.record_entry r
1285       JOIN  metabib.metarecord m USING (fingerprint)
1286      WHERE  NOT r.deleted;
1287 END;
1288
1289 $$ LANGUAGE plpgsql;
1290
1291
1292 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1293
1294 BEGIN
1295   INSERT INTO metabib.metarecord (fingerprint, master_record)
1296     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1297       FROM  biblio.record_entry b
1298       WHERE NOT b.deleted
1299         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)
1300         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1301       ORDER BY b.fingerprint, b.quality DESC;
1302   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1303     SELECT  m.id, r.id
1304       FROM  biblio.record_entry r
1305         JOIN metabib.metarecord m USING (fingerprint)
1306       WHERE NOT r.deleted
1307         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);
1308 END;
1309
1310 $$ LANGUAGE plpgsql;
1311
1312
1313 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1314
1315 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1316 --        Then SELECT migration_tools.create_cards('m_foo');
1317
1318 DECLARE
1319         u                    TEXT := schemaname || '.actor_usr_legacy';
1320         c                    TEXT := schemaname || '.actor_card';
1321
1322 BEGIN
1323
1324         EXECUTE ('DELETE FROM ' || c || ';');
1325         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1326         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1327
1328   RETURN;
1329
1330 END;
1331
1332 $$ LANGUAGE plpgsql;
1333
1334
1335 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1336
1337   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1338
1339   my ($marcxml, $shortname) = @_;
1340
1341   use MARC::Record;
1342   use MARC::File::XML;
1343
1344   my $xml = $marcxml;
1345
1346   eval {
1347     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1348
1349     foreach my $field ( $marc->field('856') ) {
1350       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1351            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1352         $field->add_subfields( '9' => $shortname );
1353                                 $field->update( ind2 => '0');
1354       }
1355     }
1356
1357     $xml = $marc->as_xml_record;
1358     $xml =~ s/^<\?.+?\?>$//mo;
1359     $xml =~ s/\n//sgo;
1360     $xml =~ s/>\s+</></sgo;
1361   };
1362
1363   return $xml;
1364
1365 $$ LANGUAGE PLPERLU STABLE;
1366
1367 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1368
1369 DECLARE
1370   old_volume   BIGINT;
1371   new_volume   BIGINT;
1372   bib          BIGINT;
1373   owner        INTEGER;
1374   old_label    TEXT;
1375   remainder    BIGINT;
1376
1377 BEGIN
1378
1379   -- Bail out if asked to change the label to ##URI##
1380   IF new_label = '##URI##' THEN
1381     RETURN;
1382   END IF;
1383
1384   -- Gather information
1385   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1386   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1387   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1388
1389   -- Bail out if the label already is ##URI##
1390   IF old_label = '##URI##' THEN
1391     RETURN;
1392   END IF;
1393
1394   -- Bail out if the call number label is already correct
1395   IF new_volume = old_volume THEN
1396     RETURN;
1397   END IF;
1398
1399   -- Check whether we already have a destination volume available
1400   SELECT id INTO new_volume FROM asset.call_number
1401     WHERE
1402       record = bib AND
1403       owning_lib = owner AND
1404       label = new_label AND
1405       NOT deleted;
1406
1407   -- Create destination volume if needed
1408   IF NOT FOUND THEN
1409     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1410       VALUES (1, 1, bib, owner, new_label, cn_class);
1411     SELECT id INTO new_volume FROM asset.call_number
1412       WHERE
1413         record = bib AND
1414         owning_lib = owner AND
1415         label = new_label AND
1416         NOT deleted;
1417   END IF;
1418
1419   -- Move copy to destination
1420   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1421
1422   -- Delete source volume if it is now empty
1423   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1424   IF NOT FOUND THEN
1425     DELETE FROM asset.call_number WHERE id = old_volume;
1426   END IF;
1427
1428 END;
1429
1430 $$ LANGUAGE plpgsql;
1431
1432 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1433
1434         my $input = $_[0];
1435         my %zipdata;
1436
1437         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1438
1439         while (<FH>) {
1440                 chomp;
1441                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1442                 $zipdata{$zip} = [$city, $state, $county];
1443         }
1444
1445         if (defined $zipdata{$input}) {
1446                 my ($city, $state, $county) = @{$zipdata{$input}};
1447                 return [$city, $state, $county];
1448         } elsif (defined $zipdata{substr $input, 0, 5}) {
1449                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1450                 return [$city, $state, $county];
1451         } else {
1452                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1453         }
1454
1455 $$ LANGUAGE PLPERLU STABLE;
1456
1457 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1458
1459 DECLARE
1460   ou  INT;
1461         org_unit_depth INT;
1462         ou_parent INT;
1463         parent_depth INT;
1464   errors_found BOOLEAN;
1465         ou_shortname TEXT;
1466         parent_shortname TEXT;
1467         ou_type_name TEXT;
1468         parent_type TEXT;
1469         type_id INT;
1470         type_depth INT;
1471         type_parent INT;
1472         type_parent_depth INT;
1473         proper_parent TEXT;
1474
1475 BEGIN
1476
1477         errors_found := FALSE;
1478
1479 -- Checking actor.org_unit_type
1480
1481         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1482
1483                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1484                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1485
1486                 IF type_parent IS NOT NULL THEN
1487
1488                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1489
1490                         IF type_depth - type_parent_depth <> 1 THEN
1491                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1492                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1493                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1494                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1495                                 errors_found := TRUE;
1496
1497                         END IF;
1498
1499                 END IF;
1500
1501         END LOOP;
1502
1503 -- Checking actor.org_unit
1504
1505   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1506
1507                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1508                 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;
1509                 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;
1510                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1511                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1512                 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;
1513                 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;
1514
1515                 IF ou_parent IS NOT NULL THEN
1516
1517                         IF      (org_unit_depth - parent_depth <> 1) OR (
1518                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1519                         ) THEN
1520                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1521                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1522                                 errors_found := TRUE;
1523                         END IF;
1524
1525                 END IF;
1526
1527   END LOOP;
1528
1529         IF NOT errors_found THEN
1530                 RAISE INFO 'No errors found.';
1531         END IF;
1532
1533   RETURN;
1534
1535 END;
1536
1537 $$ LANGUAGE plpgsql;
1538
1539
1540 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1541
1542 BEGIN
1543
1544         DELETE FROM asset.opac_visible_copies;
1545
1546         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1547                 SELECT DISTINCT
1548                         cp.id, cp.circ_lib, cn.record
1549                 FROM
1550                         asset.copy cp
1551                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1552                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1553                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1554                         JOIN config.copy_status cs ON (cp.status = cs.id)
1555                         JOIN biblio.record_entry b ON (cn.record = b.id)
1556                 WHERE
1557                         NOT cp.deleted AND
1558                         NOT cn.deleted AND
1559                         NOT b.deleted AND
1560                         cs.opac_visible AND
1561                         cl.opac_visible AND
1562                         cp.opac_visible AND
1563                         a.opac_visible AND
1564                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1565
1566 END;
1567
1568 $$ LANGUAGE plpgsql;
1569
1570
1571 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1572
1573 DECLARE
1574   old_volume     BIGINT;
1575   new_volume     BIGINT;
1576   bib            BIGINT;
1577   old_owning_lib INTEGER;
1578         old_label      TEXT;
1579   remainder      BIGINT;
1580
1581 BEGIN
1582
1583   -- Gather information
1584   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1585   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1586   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1587
1588         -- Bail out if the new_owning_lib is not the ID of an org_unit
1589         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1590                 RAISE WARNING
1591                         '% is not a valid actor.org_unit ID; no change made.',
1592                                 new_owning_lib;
1593                 RETURN;
1594         END IF;
1595
1596   -- Bail out discreetly if the owning_lib is already correct
1597   IF new_owning_lib = old_owning_lib THEN
1598     RETURN;
1599   END IF;
1600
1601   -- Check whether we already have a destination volume available
1602   SELECT id INTO new_volume FROM asset.call_number
1603     WHERE
1604       record = bib AND
1605       owning_lib = new_owning_lib AND
1606       label = old_label AND
1607       NOT deleted;
1608
1609   -- Create destination volume if needed
1610   IF NOT FOUND THEN
1611     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1612       VALUES (1, 1, bib, new_owning_lib, old_label);
1613     SELECT id INTO new_volume FROM asset.call_number
1614       WHERE
1615         record = bib AND
1616         owning_lib = new_owning_lib AND
1617         label = old_label AND
1618         NOT deleted;
1619   END IF;
1620
1621   -- Move copy to destination
1622   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1623
1624   -- Delete source volume if it is now empty
1625   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1626   IF NOT FOUND THEN
1627     DELETE FROM asset.call_number WHERE id = old_volume;
1628   END IF;
1629
1630 END;
1631
1632 $$ LANGUAGE plpgsql;
1633
1634
1635 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1636
1637 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1638
1639 DECLARE
1640         new_owning_lib  INTEGER;
1641
1642 BEGIN
1643
1644         -- Parse the new_owner as an org unit ID or shortname
1645         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1646                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1647                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1648         ELSIF new_owner ~ E'^[0-9]+$' THEN
1649                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1650                         RAISE INFO
1651                                 '%',
1652                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1653                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1654                         new_owning_lib := new_owner::INTEGER;
1655                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1656                 END IF;
1657         ELSE
1658                 RAISE WARNING
1659                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1660                         new_owning_lib;
1661                 RETURN;
1662         END IF;
1663
1664 END;
1665
1666 $$ LANGUAGE plpgsql;
1667
1668 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1669
1670 use MARC::Record;
1671 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1672 use MARC::Charset;
1673
1674 MARC::Charset->assume_unicode(1);
1675
1676 my $xml = shift;
1677
1678 eval {
1679     my $r = MARC::Record->new_from_xml( $xml );
1680     my $output_xml = $r->as_xml_record();
1681 };
1682 if ($@) {
1683     return 0;
1684 } else {
1685     return 1;
1686 }
1687
1688 $func$ LANGUAGE PLPERLU;
1689 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1690
1691 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1692 BEGIN
1693    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1694            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1695            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1696    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1697            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1698            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1699    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1700            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1701            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1702    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1703            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1704            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1705    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1706            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1707            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1708    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1709            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1710            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
1711    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1712            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1713            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
1714    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
1715    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
1716    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
1717    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
1718    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
1719 END;
1720 $FUNC$ LANGUAGE PLPGSQL;
1721
1722 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1723 BEGIN
1724    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
1725    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
1726    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
1727    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
1728    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1729    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
1730    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
1731
1732    -- import any new circ rules
1733    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1734    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1735    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1736    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1737
1738    -- and permission groups
1739    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1740
1741 END;
1742 $FUNC$ LANGUAGE PLPGSQL;
1743
1744
1745 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$
1746 DECLARE
1747     name TEXT;
1748     loopq TEXT;
1749     existsq TEXT;
1750     ct INTEGER;
1751     cols TEXT[];
1752     copyst TEXT;
1753 BEGIN
1754     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1755     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1756     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
1757     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1758     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1759     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1760     FOR name IN EXECUTE loopq LOOP
1761        EXECUTE existsq INTO ct USING name;
1762        IF ct = 0 THEN
1763            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1764            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1765                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1766            EXECUTE copyst USING name;
1767        END IF;
1768     END LOOP;
1769 END;
1770 $FUNC$ LANGUAGE PLPGSQL;
1771
1772 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1773
1774 use strict;
1775 use warnings;
1776
1777 use MARC::Record;
1778 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1779 use MARC::Charset;
1780
1781 MARC::Charset->assume_unicode(1);
1782
1783 my $target_xml = shift;
1784 my $source_xml = shift;
1785 my $tags = shift;
1786
1787 my $target;
1788 my $source;
1789
1790 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1791 if ($@) {
1792     return;
1793 }
1794 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1795 if ($@) {
1796     return;
1797 }
1798
1799 my $source_id = $source->subfield('901', 'c');
1800 $source_id = $source->subfield('903', 'a') unless $source_id;
1801 my $target_id = $target->subfield('901', 'c');
1802 $target_id = $target->subfield('903', 'a') unless $target_id;
1803
1804 my %existing_fields;
1805 foreach my $tag (@$tags) {
1806     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1807     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1808     $target->insert_fields_ordered(map { $_->clone() } @to_add);
1809     if (@to_add) {
1810         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1811     }
1812 }
1813
1814 my $xml = $target->as_xml_record;
1815 $xml =~ s/^<\?.+?\?>$//mo;
1816 $xml =~ s/\n//sgo;
1817 $xml =~ s/>\s+</></sgo;
1818
1819 return $xml;
1820
1821 $func$ LANGUAGE PLPERLU;
1822 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.';
1823
1824 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1825
1826 use strict;
1827 use warnings;
1828
1829 use MARC::Record;
1830 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1831 use Text::CSV;
1832
1833 my $in_tags = shift;
1834 my $in_values = shift;
1835
1836 # hack-and-slash parsing of array-passed-as-string;
1837 # this can go away once everybody is running Postgres 9.1+
1838 my $csv = Text::CSV->new({binary => 1});
1839 $in_tags =~ s/^{//;
1840 $in_tags =~ s/}$//;
1841 my $status = $csv->parse($in_tags);
1842 my $tags = [ $csv->fields() ];
1843 $in_values =~ s/^{//;
1844 $in_values =~ s/}$//;
1845 $status = $csv->parse($in_values);
1846 my $values = [ $csv->fields() ];
1847
1848 my $marc = MARC::Record->new();
1849
1850 $marc->leader('00000nam a22000007  4500');
1851 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
1852
1853 foreach my $i (0..$#$tags) {
1854     my ($tag, $sf);
1855     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1856         $tag = $1;
1857         $sf = $2;
1858         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1859     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1860         $tag = $1;
1861         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1862     }
1863 }
1864
1865 my $xml = $marc->as_xml_record;
1866 $xml =~ s/^<\?.+?\?>$//mo;
1867 $xml =~ s/\n//sgo;
1868 $xml =~ s/>\s+</></sgo;
1869
1870 return $xml;
1871
1872 $func$ LANGUAGE PLPERLU;
1873 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
1874 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
1875 The second argument is an array of text containing the values to plug into each field.
1876 If the value for a given field is NULL or the empty string, it is not inserted.
1877 $$;
1878
1879 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1880
1881 my ($marcxml, $tag, $pos, $value) = @_;
1882
1883 use MARC::Record;
1884 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1885 use MARC::Charset;
1886 use strict;
1887
1888 MARC::Charset->assume_unicode(1);
1889
1890 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
1891 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
1892 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
1893 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
1894
1895 my $xml = $marcxml;
1896 eval {
1897     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1898
1899     foreach my $field ($marc->field($tag)) {
1900         $field->update("ind$pos" => $value);
1901     }
1902     $xml = $marc->as_xml_record;
1903     $xml =~ s/^<\?.+?\?>$//mo;
1904     $xml =~ s/\n//sgo;
1905     $xml =~ s/>\s+</></sgo;
1906 };
1907 return $xml;
1908
1909 $func$ LANGUAGE PLPERLU;
1910
1911 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
1912 The first argument is a MARCXML string.
1913 The second argument is a MARC tag.
1914 The third argument is the indicator position, either 1 or 2.
1915 The fourth argument is the character to set the indicator value to.
1916 All occurences of the specified field will be changed.
1917 The function returns the revised MARCXML string.$$;
1918
1919 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
1920     username TEXT,
1921     password TEXT,
1922     org TEXT,
1923     perm_group TEXT,
1924     first_name TEXT DEFAULT '',
1925     last_name TEXT DEFAULT ''
1926 ) RETURNS VOID AS $func$
1927 BEGIN
1928     RAISE NOTICE '%', org ;
1929     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
1930     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
1931     FROM   actor.org_unit aou, permission.grp_tree pgt
1932     WHERE  aou.shortname = org
1933     AND    pgt.name = perm_group;
1934 END
1935 $func$
1936 LANGUAGE PLPGSQL;
1937
1938 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
1939 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
1940     DECLARE
1941         target_event_def ALIAS FOR $1;
1942         orgs ALIAS FOR $2;
1943     BEGIN
1944         DROP TABLE IF EXISTS new_atevdefs;
1945         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1946         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1947             INSERT INTO action_trigger.event_definition (
1948                 active
1949                 ,owner
1950                 ,name
1951                 ,hook
1952                 ,validator
1953                 ,reactor
1954                 ,cleanup_success
1955                 ,cleanup_failure
1956                 ,delay
1957                 ,max_delay
1958                 ,usr_field
1959                 ,opt_in_setting
1960                 ,delay_field
1961                 ,group_field
1962                 ,template
1963                 ,granularity
1964                 ,repeat_delay
1965             ) SELECT
1966                 'f'
1967                 ,orgs[i]
1968                 ,name || ' (clone of '||target_event_def||')'
1969                 ,hook
1970                 ,validator
1971                 ,reactor
1972                 ,cleanup_success
1973                 ,cleanup_failure
1974                 ,delay
1975                 ,max_delay
1976                 ,usr_field
1977                 ,opt_in_setting
1978                 ,delay_field
1979                 ,group_field
1980                 ,template
1981                 ,granularity
1982                 ,repeat_delay
1983             FROM
1984                 action_trigger.event_definition
1985             WHERE
1986                 id = target_event_def
1987             ;
1988             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1989             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1990             INSERT INTO action_trigger.environment (
1991                 event_def
1992                 ,path
1993                 ,collector
1994                 ,label
1995             ) SELECT
1996                 currval('action_trigger.event_definition_id_seq')
1997                 ,path
1998                 ,collector
1999                 ,label
2000             FROM
2001                 action_trigger.environment
2002             WHERE
2003                 event_def = target_event_def
2004             ;
2005             INSERT INTO action_trigger.event_params (
2006                 event_def
2007                 ,param
2008                 ,value
2009             ) SELECT
2010                 currval('action_trigger.event_definition_id_seq')
2011                 ,param
2012                 ,value
2013             FROM
2014                 action_trigger.event_params
2015             WHERE
2016                 event_def = target_event_def
2017             ;
2018         END LOOP;
2019         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2020     END;
2021 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2022