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 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1209
1210 -- Usage:
1211 --
1212 --   First make sure the circ matrix is loaded and the circulations
1213 --   have been staged to the extent possible (but at the very least
1214 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1215 --   circ modifiers must also be in place.
1216 --
1217 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1218 --
1219
1220 DECLARE
1221   circ_lib             INT;
1222   target_copy          INT;
1223   usr                  INT;
1224   is_renewal           BOOLEAN;
1225   this_duration_rule   INT;
1226   this_fine_rule       INT;
1227   this_max_fine_rule   INT;
1228   rcd                  config.rule_circ_duration%ROWTYPE;
1229   rrf                  config.rule_recurring_fine%ROWTYPE;
1230   rmf                  config.rule_max_fine%ROWTYPE;
1231   n                    INT := 0;
1232   n_circs              INT := 1;
1233
1234 BEGIN
1235
1236   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1237
1238   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1239
1240     -- Fetch the correct rules for this circulation
1241     EXECUTE ('
1242       SELECT
1243         circ_lib,
1244         target_copy,
1245         usr,
1246         CASE
1247           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1248           ELSE FALSE
1249         END
1250       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1251       INTO circ_lib, target_copy, usr, is_renewal ;
1252     SELECT
1253       INTO this_duration_rule,
1254            this_fine_rule,
1255            this_max_fine_rule
1256       duration_rule,
1257       recurring_fine_rule,
1258       max_fine_rule
1259       FROM action.item_user_circ_test(
1260         circ_lib,
1261         target_copy,
1262         usr,
1263         is_renewal
1264         );
1265     SELECT INTO rcd * FROM config.rule_circ_duration
1266       WHERE id = this_duration_rule;
1267     SELECT INTO rrf * FROM config.rule_recurring_fine
1268       WHERE id = this_fine_rule;
1269     SELECT INTO rmf * FROM config.rule_max_fine
1270       WHERE id = this_max_fine_rule;
1271
1272     -- Apply the rules to this circulation
1273     EXECUTE ('UPDATE ' || tablename || ' c
1274     SET
1275       duration_rule = rcd.name,
1276       recurring_fine_rule = rrf.name,
1277       max_fine_rule = rmf.name,
1278       duration = rcd.normal,
1279       recurring_fine = rrf.normal,
1280       max_fine =
1281         CASE rmf.is_percent
1282           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1283           ELSE rmf.amount
1284         END,
1285       renewal_remaining = rcd.max_renewals
1286     FROM
1287       config.rule_circ_duration rcd,
1288       config.rule_recurring_fine rrf,
1289       config.rule_max_fine rmf,
1290                         asset.copy ac
1291     WHERE
1292       rcd.id = ' || this_duration_rule || ' AND
1293       rrf.id = ' || this_fine_rule || ' AND
1294       rmf.id = ' || this_max_fine_rule || ' AND
1295                         ac.id = c.target_copy AND
1296       c.id = ' || circ || ';');
1297
1298     -- Keep track of where we are in the process
1299     n := n + 1;
1300     IF (n % 100 = 0) THEN
1301       RAISE INFO '%', n || ' of ' || n_circs
1302         || ' (' || (100*n/n_circs) || '%) circs updated.';
1303     END IF;
1304
1305   --END LOOP;
1306
1307   RETURN;
1308 END;
1309
1310 $$ LANGUAGE plpgsql;
1311
1312
1313
1314
1315 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1316
1317 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1318 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1319
1320 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1321 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1322
1323 DECLARE
1324         c                    TEXT := schemaname || '.asset_copy_legacy';
1325         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1326         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1327         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1328         stat_cat                                                 INT;
1329   stat_cat_entry       INT;
1330
1331 BEGIN
1332
1333   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1334
1335                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1336
1337                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1338                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1339                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1340
1341   END LOOP;
1342
1343   RETURN;
1344 END;
1345
1346 $$ LANGUAGE plpgsql;
1347
1348 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1349
1350 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1351 --        This will assign standing penalties as needed.
1352
1353 DECLARE
1354   org_unit  INT;
1355   usr       INT;
1356
1357 BEGIN
1358
1359   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1360
1361     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1362
1363       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1364
1365     END LOOP;
1366
1367   END LOOP;
1368
1369   RETURN;
1370
1371 END;
1372
1373 $$ LANGUAGE plpgsql;
1374
1375
1376 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1377
1378 BEGIN
1379   INSERT INTO metabib.metarecord (fingerprint, master_record)
1380     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1381       FROM  biblio.record_entry b
1382       WHERE NOT b.deleted
1383         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)
1384         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1385       ORDER BY b.fingerprint, b.quality DESC;
1386   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1387     SELECT  m.id, r.id
1388       FROM  biblio.record_entry r
1389       JOIN  metabib.metarecord m USING (fingerprint)
1390      WHERE  NOT r.deleted;
1391 END;
1392
1393 $$ LANGUAGE plpgsql;
1394
1395
1396 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1397
1398 BEGIN
1399   INSERT INTO metabib.metarecord (fingerprint, master_record)
1400     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1401       FROM  biblio.record_entry b
1402       WHERE NOT b.deleted
1403         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)
1404         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1405       ORDER BY b.fingerprint, b.quality DESC;
1406   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1407     SELECT  m.id, r.id
1408       FROM  biblio.record_entry r
1409         JOIN metabib.metarecord m USING (fingerprint)
1410       WHERE NOT r.deleted
1411         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);
1412 END;
1413
1414 $$ LANGUAGE plpgsql;
1415
1416
1417 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1418
1419 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1420 --        Then SELECT migration_tools.create_cards('m_foo');
1421
1422 DECLARE
1423         u                    TEXT := schemaname || '.actor_usr_legacy';
1424         c                    TEXT := schemaname || '.actor_card';
1425
1426 BEGIN
1427
1428         EXECUTE ('DELETE FROM ' || c || ';');
1429         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1430         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1431
1432   RETURN;
1433
1434 END;
1435
1436 $$ LANGUAGE plpgsql;
1437
1438
1439 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1440
1441   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1442
1443   my ($marcxml, $shortname) = @_;
1444
1445   use MARC::Record;
1446   use MARC::File::XML;
1447
1448   my $xml = $marcxml;
1449
1450   eval {
1451     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1452
1453     foreach my $field ( $marc->field('856') ) {
1454       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1455            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1456         $field->add_subfields( '9' => $shortname );
1457                                 $field->update( ind2 => '0');
1458       }
1459     }
1460
1461     $xml = $marc->as_xml_record;
1462     $xml =~ s/^<\?.+?\?>$//mo;
1463     $xml =~ s/\n//sgo;
1464     $xml =~ s/>\s+</></sgo;
1465   };
1466
1467   return $xml;
1468
1469 $$ LANGUAGE PLPERLU STABLE;
1470
1471 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1472
1473 DECLARE
1474   old_volume   BIGINT;
1475   new_volume   BIGINT;
1476   bib          BIGINT;
1477   owner        INTEGER;
1478   old_label    TEXT;
1479   remainder    BIGINT;
1480
1481 BEGIN
1482
1483   -- Bail out if asked to change the label to ##URI##
1484   IF new_label = '##URI##' THEN
1485     RETURN;
1486   END IF;
1487
1488   -- Gather information
1489   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1490   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1491   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1492
1493   -- Bail out if the label already is ##URI##
1494   IF old_label = '##URI##' THEN
1495     RETURN;
1496   END IF;
1497
1498   -- Bail out if the call number label is already correct
1499   IF new_volume = old_volume THEN
1500     RETURN;
1501   END IF;
1502
1503   -- Check whether we already have a destination volume available
1504   SELECT id INTO new_volume FROM asset.call_number
1505     WHERE
1506       record = bib AND
1507       owning_lib = owner AND
1508       label = new_label AND
1509       NOT deleted;
1510
1511   -- Create destination volume if needed
1512   IF NOT FOUND THEN
1513     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1514       VALUES (1, 1, bib, owner, new_label, cn_class);
1515     SELECT id INTO new_volume FROM asset.call_number
1516       WHERE
1517         record = bib AND
1518         owning_lib = owner AND
1519         label = new_label AND
1520         NOT deleted;
1521   END IF;
1522
1523   -- Move copy to destination
1524   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1525
1526   -- Delete source volume if it is now empty
1527   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1528   IF NOT FOUND THEN
1529     DELETE FROM asset.call_number WHERE id = old_volume;
1530   END IF;
1531
1532 END;
1533
1534 $$ LANGUAGE plpgsql;
1535
1536 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1537
1538         my $input = $_[0];
1539         my %zipdata;
1540
1541         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1542
1543         while (<FH>) {
1544                 chomp;
1545                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1546                 $zipdata{$zip} = [$city, $state, $county];
1547         }
1548
1549         if (defined $zipdata{$input}) {
1550                 my ($city, $state, $county) = @{$zipdata{$input}};
1551                 return [$city, $state, $county];
1552         } elsif (defined $zipdata{substr $input, 0, 5}) {
1553                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1554                 return [$city, $state, $county];
1555         } else {
1556                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1557         }
1558
1559 $$ LANGUAGE PLPERLU STABLE;
1560
1561 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1562
1563 DECLARE
1564   ou  INT;
1565         org_unit_depth INT;
1566         ou_parent INT;
1567         parent_depth INT;
1568   errors_found BOOLEAN;
1569         ou_shortname TEXT;
1570         parent_shortname TEXT;
1571         ou_type_name TEXT;
1572         parent_type TEXT;
1573         type_id INT;
1574         type_depth INT;
1575         type_parent INT;
1576         type_parent_depth INT;
1577         proper_parent TEXT;
1578
1579 BEGIN
1580
1581         errors_found := FALSE;
1582
1583 -- Checking actor.org_unit_type
1584
1585         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1586
1587                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1588                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1589
1590                 IF type_parent IS NOT NULL THEN
1591
1592                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1593
1594                         IF type_depth - type_parent_depth <> 1 THEN
1595                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1596                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1597                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1598                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1599                                 errors_found := TRUE;
1600
1601                         END IF;
1602
1603                 END IF;
1604
1605         END LOOP;
1606
1607 -- Checking actor.org_unit
1608
1609   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1610
1611                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1612                 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;
1613                 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;
1614                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1615                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1616                 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;
1617                 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;
1618
1619                 IF ou_parent IS NOT NULL THEN
1620
1621                         IF      (org_unit_depth - parent_depth <> 1) OR (
1622                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1623                         ) THEN
1624                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1625                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1626                                 errors_found := TRUE;
1627                         END IF;
1628
1629                 END IF;
1630
1631   END LOOP;
1632
1633         IF NOT errors_found THEN
1634                 RAISE INFO 'No errors found.';
1635         END IF;
1636
1637   RETURN;
1638
1639 END;
1640
1641 $$ LANGUAGE plpgsql;
1642
1643
1644 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1645
1646 BEGIN
1647
1648         DELETE FROM asset.opac_visible_copies;
1649
1650         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1651                 SELECT DISTINCT
1652                         cp.id, cp.circ_lib, cn.record
1653                 FROM
1654                         asset.copy cp
1655                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1656                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1657                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1658                         JOIN config.copy_status cs ON (cp.status = cs.id)
1659                         JOIN biblio.record_entry b ON (cn.record = b.id)
1660                 WHERE
1661                         NOT cp.deleted AND
1662                         NOT cn.deleted AND
1663                         NOT b.deleted AND
1664                         cs.opac_visible AND
1665                         cl.opac_visible AND
1666                         cp.opac_visible AND
1667                         a.opac_visible AND
1668                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1669
1670 END;
1671
1672 $$ LANGUAGE plpgsql;
1673
1674
1675 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1676
1677 DECLARE
1678   old_volume     BIGINT;
1679   new_volume     BIGINT;
1680   bib            BIGINT;
1681   old_owning_lib INTEGER;
1682         old_label      TEXT;
1683   remainder      BIGINT;
1684
1685 BEGIN
1686
1687   -- Gather information
1688   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1689   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1690   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1691
1692         -- Bail out if the new_owning_lib is not the ID of an org_unit
1693         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1694                 RAISE WARNING
1695                         '% is not a valid actor.org_unit ID; no change made.',
1696                                 new_owning_lib;
1697                 RETURN;
1698         END IF;
1699
1700   -- Bail out discreetly if the owning_lib is already correct
1701   IF new_owning_lib = old_owning_lib THEN
1702     RETURN;
1703   END IF;
1704
1705   -- Check whether we already have a destination volume available
1706   SELECT id INTO new_volume FROM asset.call_number
1707     WHERE
1708       record = bib AND
1709       owning_lib = new_owning_lib AND
1710       label = old_label AND
1711       NOT deleted;
1712
1713   -- Create destination volume if needed
1714   IF NOT FOUND THEN
1715     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1716       VALUES (1, 1, bib, new_owning_lib, old_label);
1717     SELECT id INTO new_volume FROM asset.call_number
1718       WHERE
1719         record = bib AND
1720         owning_lib = new_owning_lib AND
1721         label = old_label AND
1722         NOT deleted;
1723   END IF;
1724
1725   -- Move copy to destination
1726   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1727
1728   -- Delete source volume if it is now empty
1729   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1730   IF NOT FOUND THEN
1731     DELETE FROM asset.call_number WHERE id = old_volume;
1732   END IF;
1733
1734 END;
1735
1736 $$ LANGUAGE plpgsql;
1737
1738
1739 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1740
1741 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1742
1743 DECLARE
1744         new_owning_lib  INTEGER;
1745
1746 BEGIN
1747
1748         -- Parse the new_owner as an org unit ID or shortname
1749         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1750                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1751                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1752         ELSIF new_owner ~ E'^[0-9]+$' THEN
1753                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1754                         RAISE INFO
1755                                 '%',
1756                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1757                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1758                         new_owning_lib := new_owner::INTEGER;
1759                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1760                 END IF;
1761         ELSE
1762                 RAISE WARNING
1763                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1764                         new_owning_lib;
1765                 RETURN;
1766         END IF;
1767
1768 END;
1769
1770 $$ LANGUAGE plpgsql;
1771
1772 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1773
1774 use MARC::Record;
1775 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1776 use MARC::Charset;
1777
1778 MARC::Charset->assume_unicode(1);
1779
1780 my $xml = shift;
1781
1782 eval {
1783     my $r = MARC::Record->new_from_xml( $xml );
1784     my $output_xml = $r->as_xml_record();
1785 };
1786 if ($@) {
1787     return 0;
1788 } else {
1789     return 1;
1790 }
1791
1792 $func$ LANGUAGE PLPERLU;
1793 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1794
1795 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1796 BEGIN
1797    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1798            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1799            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1800    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1801            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1802            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1803    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1804            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1805            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1806    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1807            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1808            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1809    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1810            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1811            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1812    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1813            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1814            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
1815    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1816            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1817            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
1818    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
1819    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
1820    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
1821    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
1822    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
1823 END;
1824 $FUNC$ LANGUAGE PLPGSQL;
1825
1826 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1827 BEGIN
1828    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
1829    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
1830    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
1831    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
1832    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1833    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
1834    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
1835
1836    -- import any new circ rules
1837    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1838    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1839    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1840    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1841
1842    -- and permission groups
1843    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1844
1845 END;
1846 $FUNC$ LANGUAGE PLPGSQL;
1847
1848
1849 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$
1850 DECLARE
1851     name TEXT;
1852     loopq TEXT;
1853     existsq TEXT;
1854     ct INTEGER;
1855     cols TEXT[];
1856     copyst TEXT;
1857 BEGIN
1858     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1859     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1860     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
1861     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1862     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1863     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1864     FOR name IN EXECUTE loopq LOOP
1865        EXECUTE existsq INTO ct USING name;
1866        IF ct = 0 THEN
1867            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1868            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1869                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1870            EXECUTE copyst USING name;
1871        END IF;
1872     END LOOP;
1873 END;
1874 $FUNC$ LANGUAGE PLPGSQL;
1875
1876 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1877
1878 use strict;
1879 use warnings;
1880
1881 use MARC::Record;
1882 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1883 use MARC::Charset;
1884
1885 MARC::Charset->assume_unicode(1);
1886
1887 my $target_xml = shift;
1888 my $source_xml = shift;
1889 my $tags = shift;
1890
1891 my $target;
1892 my $source;
1893
1894 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1895 if ($@) {
1896     return;
1897 }
1898 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1899 if ($@) {
1900     return;
1901 }
1902
1903 my $source_id = $source->subfield('901', 'c');
1904 $source_id = $source->subfield('903', 'a') unless $source_id;
1905 my $target_id = $target->subfield('901', 'c');
1906 $target_id = $target->subfield('903', 'a') unless $target_id;
1907
1908 my %existing_fields;
1909 foreach my $tag (@$tags) {
1910     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1911     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1912     $target->insert_fields_ordered(map { $_->clone() } @to_add);
1913     if (@to_add) {
1914         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1915     }
1916 }
1917
1918 my $xml = $target->as_xml_record;
1919 $xml =~ s/^<\?.+?\?>$//mo;
1920 $xml =~ s/\n//sgo;
1921 $xml =~ s/>\s+</></sgo;
1922
1923 return $xml;
1924
1925 $func$ LANGUAGE PLPERLU;
1926 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.';
1927
1928 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1929
1930 use strict;
1931 use warnings;
1932
1933 use MARC::Record;
1934 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1935 use Text::CSV;
1936
1937 my $in_tags = shift;
1938 my $in_values = shift;
1939
1940 # hack-and-slash parsing of array-passed-as-string;
1941 # this can go away once everybody is running Postgres 9.1+
1942 my $csv = Text::CSV->new({binary => 1});
1943 $in_tags =~ s/^{//;
1944 $in_tags =~ s/}$//;
1945 my $status = $csv->parse($in_tags);
1946 my $tags = [ $csv->fields() ];
1947 $in_values =~ s/^{//;
1948 $in_values =~ s/}$//;
1949 $status = $csv->parse($in_values);
1950 my $values = [ $csv->fields() ];
1951
1952 my $marc = MARC::Record->new();
1953
1954 $marc->leader('00000nam a22000007  4500');
1955 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
1956
1957 foreach my $i (0..$#$tags) {
1958     my ($tag, $sf);
1959     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1960         $tag = $1;
1961         $sf = $2;
1962         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1963     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1964         $tag = $1;
1965         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1966     }
1967 }
1968
1969 my $xml = $marc->as_xml_record;
1970 $xml =~ s/^<\?.+?\?>$//mo;
1971 $xml =~ s/\n//sgo;
1972 $xml =~ s/>\s+</></sgo;
1973
1974 return $xml;
1975
1976 $func$ LANGUAGE PLPERLU;
1977 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
1978 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
1979 The second argument is an array of text containing the values to plug into each field.
1980 If the value for a given field is NULL or the empty string, it is not inserted.
1981 $$;
1982
1983 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1984
1985 my ($marcxml, $tag, $pos, $value) = @_;
1986
1987 use MARC::Record;
1988 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1989 use MARC::Charset;
1990 use strict;
1991
1992 MARC::Charset->assume_unicode(1);
1993
1994 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
1995 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
1996 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
1997 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
1998
1999 my $xml = $marcxml;
2000 eval {
2001     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2002
2003     foreach my $field ($marc->field($tag)) {
2004         $field->update("ind$pos" => $value);
2005     }
2006     $xml = $marc->as_xml_record;
2007     $xml =~ s/^<\?.+?\?>$//mo;
2008     $xml =~ s/\n//sgo;
2009     $xml =~ s/>\s+</></sgo;
2010 };
2011 return $xml;
2012
2013 $func$ LANGUAGE PLPERLU;
2014
2015 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2016 The first argument is a MARCXML string.
2017 The second argument is a MARC tag.
2018 The third argument is the indicator position, either 1 or 2.
2019 The fourth argument is the character to set the indicator value to.
2020 All occurences of the specified field will be changed.
2021 The function returns the revised MARCXML string.$$;
2022
2023 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2024     username TEXT,
2025     password TEXT,
2026     org TEXT,
2027     perm_group TEXT,
2028     first_name TEXT DEFAULT '',
2029     last_name TEXT DEFAULT ''
2030 ) RETURNS VOID AS $func$
2031 BEGIN
2032     RAISE NOTICE '%', org ;
2033     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2034     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2035     FROM   actor.org_unit aou, permission.grp_tree pgt
2036     WHERE  aou.shortname = org
2037     AND    pgt.name = perm_group;
2038 END
2039 $func$
2040 LANGUAGE PLPGSQL;
2041
2042 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2043 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2044     DECLARE
2045         target_event_def ALIAS FOR $1;
2046         orgs ALIAS FOR $2;
2047     BEGIN
2048         DROP TABLE IF EXISTS new_atevdefs;
2049         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2050         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2051             INSERT INTO action_trigger.event_definition (
2052                 active
2053                 ,owner
2054                 ,name
2055                 ,hook
2056                 ,validator
2057                 ,reactor
2058                 ,cleanup_success
2059                 ,cleanup_failure
2060                 ,delay
2061                 ,max_delay
2062                 ,usr_field
2063                 ,opt_in_setting
2064                 ,delay_field
2065                 ,group_field
2066                 ,template
2067                 ,granularity
2068                 ,repeat_delay
2069             ) SELECT
2070                 'f'
2071                 ,orgs[i]
2072                 ,name || ' (clone of '||target_event_def||')'
2073                 ,hook
2074                 ,validator
2075                 ,reactor
2076                 ,cleanup_success
2077                 ,cleanup_failure
2078                 ,delay
2079                 ,max_delay
2080                 ,usr_field
2081                 ,opt_in_setting
2082                 ,delay_field
2083                 ,group_field
2084                 ,template
2085                 ,granularity
2086                 ,repeat_delay
2087             FROM
2088                 action_trigger.event_definition
2089             WHERE
2090                 id = target_event_def
2091             ;
2092             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2093             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2094             INSERT INTO action_trigger.environment (
2095                 event_def
2096                 ,path
2097                 ,collector
2098                 ,label
2099             ) SELECT
2100                 currval('action_trigger.event_definition_id_seq')
2101                 ,path
2102                 ,collector
2103                 ,label
2104             FROM
2105                 action_trigger.environment
2106             WHERE
2107                 event_def = target_event_def
2108             ;
2109             INSERT INTO action_trigger.event_params (
2110                 event_def
2111                 ,param
2112                 ,value
2113             ) SELECT
2114                 currval('action_trigger.event_definition_id_seq')
2115                 ,param
2116                 ,value
2117             FROM
2118                 action_trigger.event_params
2119             WHERE
2120                 event_def = target_event_def
2121             ;
2122         END LOOP;
2123         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);
2124     END;
2125 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2126