sql/base/base.sql

   1 -- Copyright 2009-2012, Equinox Software, Inc.
   2 --
   3 -- This program is free software; you can redistribute it and/or
   4 -- modify it under the terms of the GNU General Public License
   5 -- as published by the Free Software Foundation; either version 2
   6 -- of the License, or (at your option) any later version.
   7 --
   8 -- This program is distributed in the hope that it will be useful,
   9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
  10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11 -- GNU General Public License for more details.
  12 --
  13 -- You should have received a copy of the GNU General Public License
  14 -- along with this program; if not, write to the Free Software
  15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  16
  17 --------------------------------------------------------------------------
  18 -- An example of how to use:
  19 --
  20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
  21 -- \i base.sql
  22 -- SELECT migration_tools.init('foo');
  23 -- SELECT migration_tools.build('foo');
  24 -- SELECT * FROM foo.fields_requiring_mapping;
  25 -- \d foo.actor_usr
  26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS foo.asset_copy;
  27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
  28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
  29
  30 CREATE SCHEMA migration_tools;
  31
  32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
  33     DECLARE
  34         migration_schema ALIAS FOR $1;
  35         output  RECORD;
  36     BEGIN
  37         FOR output IN
  38             EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
  39         LOOP
  40             RETURN output.tables;
  41         END LOOP;
  42     END;
  43 $$ LANGUAGE PLPGSQL STRICT STABLE;
  44
  45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
  46     DECLARE
  47         migration_schema ALIAS FOR $1;
  48         output TEXT;
  49     BEGIN
  50         FOR output IN
  51             EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
  52         LOOP
  53             RETURN output;
  54         END LOOP;
  55     END;
  56 $$ LANGUAGE PLPGSQL STRICT STABLE;
  57
  58
  59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
  60     DECLARE
  61         migration_schema ALIAS FOR $1;
  62         sql ALIAS FOR $2;
  63         nrows ALIAS FOR $3;
  64     BEGIN
  65         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
  66     END;
  67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  68
  69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
  70     DECLARE
  71         migration_schema ALIAS FOR $1;
  72         sql ALIAS FOR $2;
  73         nrows INTEGER;
  74     BEGIN
  75         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  76         --RAISE INFO '%', sql;
  77         EXECUTE sql;
  78         GET DIAGNOSTICS nrows = ROW_COUNT;
  79         PERFORM migration_tools.log(migration_schema,sql,nrows);
  80     EXCEPTION
  81         WHEN OTHERS THEN
  82             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
  83     END;
  84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  85
  86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
  87     DECLARE
  88         migration_schema ALIAS FOR $1;
  89         sql ALIAS FOR $2;
  90         nrows INTEGER;
  91     BEGIN
  92         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
  93         RAISE INFO 'debug_exec sql = %', sql;
  94         EXECUTE sql;
  95         GET DIAGNOSTICS nrows = ROW_COUNT;
  96         PERFORM migration_tools.log(migration_schema,sql,nrows);
  97     EXCEPTION
  98         WHEN OTHERS THEN
  99             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 100     END;
 101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 102
 103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
 104     DECLARE
 105         migration_schema ALIAS FOR $1;
 106         sql TEXT;
 107     BEGIN
 108         EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
 109         EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
 110         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
 111         BEGIN
 112             SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
 113             EXECUTE sql;
 114         EXCEPTION
 115             WHEN OTHERS THEN
 116                 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
 117         END;
 118         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
 119         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
 120         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment'' );' );
 121         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
 122         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
 123         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
 124         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
 125         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
 126             id SERIAL,
 127             perm_grp_id INTEGER,
 128             transcribed_perm_group TEXT,
 129             legacy_field1 TEXT,
 130             legacy_value1 TEXT,
 131             legacy_field2 TEXT,
 132             legacy_value2 TEXT,
 133             legacy_field3 TEXT,
 134             legacy_value3 TEXT
 135         );' );
 136         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
 137         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
 138         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
 139             id SERIAL,
 140             evergreen_field TEXT,
 141             evergreen_value TEXT,
 142             evergreen_datatype TEXT,
 143             legacy_field1 TEXT,
 144             legacy_value1 TEXT,
 145             legacy_field2 TEXT,
 146             legacy_value2 TEXT,
 147             legacy_field3 TEXT,
 148             legacy_value3 TEXT
 149         );' );
 150         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
 151         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
 152         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
 153         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
 154         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
 155         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
 156             id SERIAL,
 157             location INTEGER,
 158             holdable BOOLEAN NOT NULL DEFAULT TRUE,
 159             hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
 160             opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
 161             circulate BOOLEAN NOT NULL DEFAULT TRUE,
 162             transcribed_location TEXT,
 163             legacy_field1 TEXT,
 164             legacy_value1 TEXT,
 165             legacy_field2 TEXT,
 166             legacy_value2 TEXT,
 167             legacy_field3 TEXT,
 168             legacy_value3 TEXT
 169         );' );
 170         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
 171         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
 172         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
 173         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
 174         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
 175         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
 176         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
 177             id SERIAL,
 178             circulate BOOLEAN,
 179             loan_period TEXT,
 180             max_renewals TEXT,
 181             max_out TEXT,
 182             fine_amount TEXT,
 183             fine_interval TEXT,
 184             max_fine TEXT,
 185             item_field1 TEXT,
 186             item_value1 TEXT,
 187             item_field2 TEXT,
 188             item_value2 TEXT,
 189             patron_field1 TEXT,
 190             patron_value1 TEXT,
 191             patron_field2 TEXT,
 192             patron_value2 TEXT
 193         );' );
 194         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
 195         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
 196         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
 197         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
 198         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
 199
 200         BEGIN
 201             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
 202         EXCEPTION
 203             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
 204         END;
 205     END;
 206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 207
 208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
 209     DECLARE
 210         migration_schema ALIAS FOR $1;
 211         production_tables TEXT[];
 212     BEGIN
 213         --RAISE INFO 'In migration_tools.build(%)', migration_schema;
 214         SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
 215         PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
 216         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
 217         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
 218         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
 219         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
 220         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
 221         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
 222         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' );
 223     END;
 224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 225
 226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
 227     DECLARE
 228         migration_schema ALIAS FOR $1;
 229         production_tables ALIAS FOR $2;
 230     BEGIN
 231         --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
 232         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
 233             PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
 234         END LOOP;
 235     END;
 236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 237
 238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
 239     DECLARE
 240         migration_schema ALIAS FOR $1;
 241         production_table ALIAS FOR $2;
 242         base_staging_table TEXT;
 243         columns RECORD;
 244     BEGIN
 245         base_staging_table = REPLACE( production_table, '.', '_' );
 246         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
 247         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
 248         PERFORM migration_tools.exec( $1, '
 249             INSERT INTO ' || migration_schema || '.fields_requiring_mapping
 250                 SELECT table_schema, table_name, column_name, data_type
 251                 FROM information_schema.columns
 252                 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
 253         ' );
 254         FOR columns IN
 255             SELECT table_schema, table_name, column_name, data_type
 256             FROM information_schema.columns
 257             WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
 258         LOOP
 259             PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
 260         END LOOP;
 261     END;
 262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 263
 264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
 265     DECLARE
 266         migration_schema ALIAS FOR $1;
 267         production_tables TEXT[];
 268     BEGIN
 269         --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
 270         SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
 271         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
 272             PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
 273         END LOOP;
 274     END;
 275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 276
 277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
 278     DECLARE
 279         migration_schema ALIAS FOR $1;
 280         production_table ALIAS FOR $2;
 281         base_staging_table TEXT;
 282         columns RECORD;
 283     BEGIN
 284         base_staging_table = REPLACE( production_table, '.', '_' );
 285         --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
 286         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
 287     END;
 288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 289
 290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
 291     DECLARE
 292         full_name TEXT := $1;
 293         temp TEXT;
 294         family_name TEXT := '';
 295         first_given_name TEXT := '';
 296         second_given_name TEXT := '';
 297         suffix TEXT := '';
 298         prefix TEXT := '';
 299     BEGIN
 300         temp := full_name;
 301         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 302         IF temp ilike '%MR.%' THEN
 303             prefix := 'Mr.';
 304             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
 305         END IF;
 306         IF temp ilike '%MRS.%' THEN
 307             prefix := 'Mrs.';
 308             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
 309         END IF;
 310         IF temp ilike '%MS.%' THEN
 311             prefix := 'Ms.';
 312             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
 313         END IF;
 314         IF temp ilike '%DR.%' THEN
 315             prefix := 'Dr.';
 316             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
 317         END IF;
 318         IF temp ilike '%JR%' THEN
 319             suffix := 'Jr.';
 320             temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
 321         END IF;
 322         IF temp ilike '%JR,%' THEN
 323             suffix := 'Jr.';
 324             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
 325         END IF;
 326         IF temp ilike '%SR%' THEN
 327             suffix := 'Sr.';
 328             temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
 329         END IF;
 330         IF temp ilike '%SR,%' THEN
 331             suffix := 'Sr.';
 332             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
 333         END IF;
 334         IF temp ~ E'\\sII$' THEN
 335             suffix := 'II';
 336             temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
 337         END IF;
 338         IF temp ~ E'\\sIII$' THEN
 339             suffix := 'III';
 340             temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
 341         END IF;
 342         IF temp ~ E'\\sIV$' THEN
 343             suffix := 'IV';
 344             temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
 345         END IF;
 346
 347         family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
 348         first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
 349         second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE ''  END );
 350
 351         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 352     END;
 353 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 354
 355 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
 356     DECLARE
 357         city_state_zip TEXT := $1;
 358         city TEXT := '';
 359         state TEXT := '';
 360         zip TEXT := '';
 361     BEGIN
 362         zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
 363         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 364         IF city_state_zip ~ ',' THEN
 365             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
 366             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
 367         ELSE
 368             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN
 369                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' );
 370                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' );
 371             ELSE
 372                 IF city_state_zip ~ E'^\\S+$'  THEN
 373                     city := city_state_zip;
 374                     state := 'N/A';
 375                 ELSE
 376                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 377                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 378                 END IF;
 379             END IF;
 380         END IF;
 381         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
 382     END;
 383 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 384
 385 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
 386     DECLARE
 387         n TEXT := o;
 388     BEGIN
 389         IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807.  May also want to consider the case where folks want to add prefixes to non-numeric barcodes
 390             IF o::BIGINT < t THEN
 391                 n = o::BIGINT + t;
 392             END IF;
 393         END IF;
 394
 395         RETURN n;
 396     END;
 397 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 398
 399 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
 400     DECLARE
 401         migration_schema ALIAS FOR $1;
 402         output TEXT;
 403     BEGIN
 404         FOR output IN
 405             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
 406         LOOP
 407             RETURN output;
 408         END LOOP;
 409     END;
 410 $$ LANGUAGE PLPGSQL STRICT STABLE;
 411
 412 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
 413     DECLARE
 414         migration_schema ALIAS FOR $1;
 415         output TEXT;
 416     BEGIN
 417         FOR output IN
 418             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
 419         LOOP
 420             RETURN output;
 421         END LOOP;
 422     END;
 423 $$ LANGUAGE PLPGSQL STRICT STABLE;
 424
 425 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
 426     DECLARE
 427         migration_schema ALIAS FOR $1;
 428         output TEXT;
 429     BEGIN
 430         FOR output IN
 431             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
 432         LOOP
 433             RETURN output;
 434         END LOOP;
 435     END;
 436 $$ LANGUAGE PLPGSQL STRICT STABLE;
 437
 438 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
 439     DECLARE
 440         migration_schema ALIAS FOR $1;
 441         output TEXT;
 442     BEGIN
 443         FOR output IN
 444             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
 445         LOOP
 446             RETURN output;
 447         END LOOP;
 448     END;
 449 $$ LANGUAGE PLPGSQL STRICT STABLE;
 450
 451 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
 452     DECLARE
 453         migration_schema ALIAS FOR $1;
 454         profile_map TEXT;
 455         patron_table ALIAS FOR $2;
 456         default_patron_profile ALIAS FOR $3;
 457         sql TEXT;
 458         sql_update TEXT;
 459         sql_where1 TEXT := '';
 460         sql_where2 TEXT := '';
 461         sql_where3 TEXT := '';
 462         output RECORD;
 463     BEGIN
 464         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
 465         FOR output IN
 466             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
 467         LOOP
 468             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
 469             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
 470             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
 471             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
 472             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
 473             --RAISE INFO 'sql = %', sql;
 474             PERFORM migration_tools.exec( $1, sql );
 475         END LOOP;
 476         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
 477         BEGIN
 478             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
 479         EXCEPTION
 480             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
 481         END;
 482     END;
 483 $$ LANGUAGE PLPGSQL STRICT STABLE;
 484
 485 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
 486     DECLARE
 487         migration_schema ALIAS FOR $1;
 488         field_map TEXT;
 489         item_table ALIAS FOR $2;
 490         sql TEXT;
 491         sql_update TEXT;
 492         sql_where1 TEXT := '';
 493         sql_where2 TEXT := '';
 494         sql_where3 TEXT := '';
 495         output RECORD;
 496     BEGIN
 497         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
 498         FOR output IN
 499             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 500         LOOP
 501             sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
 502             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
 503             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
 504             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
 505             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
 506             --RAISE INFO 'sql = %', sql;
 507             PERFORM migration_tools.exec( $1, sql );
 508         END LOOP;
 509         BEGIN
 510             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
 511         EXCEPTION
 512             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
 513         END;
 514     END;
 515 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 516
 517 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
 518     DECLARE
 519         migration_schema ALIAS FOR $1;
 520         base_copy_location_map TEXT;
 521         item_table ALIAS FOR $2;
 522         sql TEXT;
 523         sql_update TEXT;
 524         sql_where1 TEXT := '';
 525         sql_where2 TEXT := '';
 526         sql_where3 TEXT := '';
 527         output RECORD;
 528     BEGIN
 529         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
 530         FOR output IN
 531             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
 532         LOOP
 533             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
 534             sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
 535             sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
 536             sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
 537             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
 538             --RAISE INFO 'sql = %', sql;
 539             PERFORM migration_tools.exec( $1, sql );
 540         END LOOP;
 541         BEGIN
 542             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
 543         EXCEPTION
 544             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
 545         END;
 546     END;
 547 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 548
 549 -- circulate       loan period     max renewals    max out fine amount     fine interval   max fine        item field 1    item value 1    item field 2    item value 2    patron field 1  patron value 1  patron field 2  patron value 2
 550 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
 551     DECLARE
 552         migration_schema ALIAS FOR $1;
 553         field_map TEXT;
 554         circ_table ALIAS FOR $2;
 555         item_table ALIAS FOR $3;
 556         patron_table ALIAS FOR $4;
 557         sql TEXT;
 558         sql_update TEXT;
 559         sql_where1 TEXT := '';
 560         sql_where2 TEXT := '';
 561         sql_where3 TEXT := '';
 562         sql_where4 TEXT := '';
 563         output RECORD;
 564     BEGIN
 565         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
 566         FOR output IN
 567             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 568         LOOP
 569             sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
 570             sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
 571             sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
 572             sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
 573             sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
 574             sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> ''  THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
 575             --RAISE INFO 'sql = %', sql;
 576             PERFORM migration_tools.exec( $1, sql );
 577         END LOOP;
 578         BEGIN
 579             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
 580         EXCEPTION
 581             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
 582         END;
 583     END;
 584 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 585
 586 -- expand_barcode
 587 --   $barcode      source barcode
 588 --   $prefix       prefix to add to barcode, NULL = add no prefix
 589 --   $maxlen       maximum length of barcode; default to 14 if left NULL
 590 --   $pad          padding string to apply to left of source barcode before adding
 591 --                 prefix and suffix; set to NULL or '' if no padding is desired
 592 --   $suffix       suffix to add to barcode, NULL = add no suffix
 593 --
 594 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
 595 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
 596 --
 597 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
 598     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
 599
 600     # default case
 601     return unless defined $barcode;
 602
 603     $prefix     = '' unless defined $prefix;
 604     $maxlen ||= 14;
 605     $pad        = '0' unless defined $pad;
 606     $suffix     = '' unless defined $suffix;
 607
 608     # bail out if adding prefix and suffix would bring new barcode over max length
 609     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
 610
 611     my $new_barcode = $barcode;
 612     if ($pad ne '') {
 613         my $pad_length = $maxlen - length($prefix) - length($suffix);
 614         if (length($barcode) < $pad_length) {
 615             # assuming we always want padding on the left
 616             # also assuming that it is possible to have the pad string be longer than 1 character
 617             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
 618         }
 619     }
 620
 621     # bail out if adding prefix and suffix would bring new barcode over max length
 622     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
 623
 624     return "$prefix$new_barcode$suffix";
 625 $$ LANGUAGE PLPERLU STABLE;
 626
 627 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
 628     DECLARE
 629         attempt_value ALIAS FOR $1;
 630         datatype ALIAS FOR $2;
 631         fail_value ALIAS FOR $3;
 632         output RECORD;
 633     BEGIN
 634         FOR output IN
 635             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
 636         LOOP
 637             RETURN output;
 638         END LOOP;
 639     EXCEPTION
 640         WHEN OTHERS THEN
 641             FOR output IN
 642                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;'
 643             LOOP
 644                 RETURN output;
 645             END LOOP;
 646     END;
 647 $$ LANGUAGE PLPGSQL STRICT STABLE;
 648
 649 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
 650     DECLARE
 651         attempt_value ALIAS FOR $1;
 652         fail_value ALIAS FOR $2;
 653         output DATE;
 654     BEGIN
 655         FOR output IN
 656             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
 657         LOOP
 658             RETURN output;
 659         END LOOP;
 660     EXCEPTION
 661         WHEN OTHERS THEN
 662             FOR output IN
 663                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 664             LOOP
 665                 RETURN output;
 666             END LOOP;
 667     END;
 668 $$ LANGUAGE PLPGSQL STRICT STABLE;
 669
 670 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
 671     DECLARE
 672         attempt_value ALIAS FOR $1;
 673         fail_value ALIAS FOR $2;
 674         output TIMESTAMPTZ;
 675     BEGIN
 676         FOR output IN
 677             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
 678         LOOP
 679             RETURN output;
 680         END LOOP;
 681     EXCEPTION
 682         WHEN OTHERS THEN
 683             FOR output IN
 684                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
 685             LOOP
 686                 RETURN output;
 687             END LOOP;
 688     END;
 689 $$ LANGUAGE PLPGSQL STRICT STABLE;
 690
 691 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 692     DECLARE
 693         attempt_value ALIAS FOR $1;
 694         fail_value ALIAS FOR $2;
 695         output NUMERIC(8,2);
 696     BEGIN
 697         FOR output IN
 698             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
 699         LOOP
 700             RETURN output;
 701         END LOOP;
 702     EXCEPTION
 703         WHEN OTHERS THEN
 704             FOR output IN
 705                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 706             LOOP
 707                 RETURN output;
 708             END LOOP;
 709     END;
 710 $$ LANGUAGE PLPGSQL STRICT STABLE;
 711
 712 -- add_codabar_checkdigit
 713 --   $barcode      source barcode
 714 --
 715 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
 716 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 717 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
 718 -- input string does not meet those requirements, it is returned unchanged.
 719 --
 720 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
 721     my $barcode = shift;
 722
 723     return $barcode if $barcode !~ /^\d{13,14}$/;
 724     $barcode = substr($barcode, 0, 13); # ignore 14th digit
 725     my @digits = split //, $barcode;
 726     my $total = 0;
 727     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
 728     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
 729     my $remainder = $total % 10;
 730     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
 731     return $barcode . $checkdigit;
 732 $$ LANGUAGE PLPERLU STRICT STABLE;
 733
 734 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
 735   DECLARE
 736     phone TEXT := $1;
 737     areacode TEXT := $2;
 738     temp TEXT := '';
 739     output TEXT := '';
 740     n_digits INTEGER := 0;
 741   BEGIN
 742     temp := phone;
 743     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
 744     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
 745     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
 746     IF n_digits = 7 AND areacode <> '' THEN
 747       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
 748       output := (areacode || '-' || temp);
 749     ELSE
 750       output := temp;
 751     END IF;
 752     RETURN output;
 753   END;
 754
 755 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 756
 757 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
 758   my ($marcxml, $pos, $value) = @_;
 759
 760   use MARC::Record;
 761   use MARC::File::XML;
 762
 763   my $xml = $marcxml;
 764   eval {
 765     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 766     my $leader = $marc->leader();
 767     substr($leader, $pos, 1) = $value;
 768     $marc->leader($leader);
 769     $xml = $marc->as_xml_record;
 770     $xml =~ s/^<\?.+?\?>$//mo;
 771     $xml =~ s/\n//sgo;
 772     $xml =~ s/>\s+</></sgo;
 773   };
 774   return $xml;
 775 $$ LANGUAGE PLPERLU STABLE;
 776
 777 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
 778   my ($marcxml, $pos, $value) = @_;
 779
 780   use MARC::Record;
 781   use MARC::File::XML;
 782
 783   my $xml = $marcxml;
 784   eval {
 785     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 786     my $f008 = $marc->field('008');
 787
 788     if ($f008) {
 789        my $field = $f008->data();
 790        substr($field, $pos, 1) = $value;
 791        $f008->update($field);
 792        $xml = $marc->as_xml_record;
 793        $xml =~ s/^<\?.+?\?>$//mo;
 794        $xml =~ s/\n//sgo;
 795        $xml =~ s/>\s+</></sgo;
 796     }
 797   };
 798   return $xml;
 799 $$ LANGUAGE PLPERLU STABLE;
 800
 801
 802 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
 803   DECLARE
 804     profile ALIAS FOR $1;
 805   BEGIN
 806     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
 807   END;
 808 $$ LANGUAGE PLPGSQL STRICT STABLE;
 809
 810
 811 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
 812   BEGIN
 813     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
 814   END;
 815 $$ LANGUAGE PLPGSQL STRICT STABLE;
 816
 817
 818 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
 819
 820   my ($marcxml, $tags) = @_;
 821
 822   use MARC::Record;
 823   use MARC::File::XML;
 824
 825   my $xml = $marcxml;
 826
 827   eval {
 828     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 829     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
 830
 831     my @incumbents = ();
 832
 833     foreach my $field ( $marc->fields() ) {
 834       push @incumbents, $field->as_formatted();
 835     }
 836
 837     foreach $field ( $to_insert->fields() ) {
 838       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
 839         $marc->insert_fields_ordered( ($field) );
 840       }
 841     }
 842
 843     $xml = $marc->as_xml_record;
 844     $xml =~ s/^<\?.+?\?>$//mo;
 845     $xml =~ s/\n//sgo;
 846     $xml =~ s/>\s+</></sgo;
 847   };
 848
 849   return $xml;
 850
 851 $$ LANGUAGE PLPERLU STABLE;
 852
 853 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
 854
 855 -- Usage:
 856 --
 857 --   First make sure the circ matrix is loaded and the circulations
 858 --   have been staged to the extent possible (but at the very least
 859 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 860 --   circ modifiers must also be in place.
 861 --
 862 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 863 --
 864
 865 DECLARE
 866   circ_lib             INT;
 867   target_copy          INT;
 868   usr                  INT;
 869   is_renewal           BOOLEAN;
 870   this_duration_rule   INT;
 871   this_fine_rule       INT;
 872   this_max_fine_rule   INT;
 873   rcd                  config.rule_circ_duration%ROWTYPE;
 874   rrf                  config.rule_recurring_fine%ROWTYPE;
 875   rmf                  config.rule_max_fine%ROWTYPE;
 876   circ                 INT;
 877   n                    INT := 0;
 878   n_circs              INT;
 879
 880 BEGIN
 881
 882   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 883
 884   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 885
 886     -- Fetch the correct rules for this circulation
 887     EXECUTE ('
 888       SELECT
 889         circ_lib,
 890         target_copy,
 891         usr,
 892         CASE
 893           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 894           ELSE FALSE
 895         END
 896       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 897       INTO circ_lib, target_copy, usr, is_renewal ;
 898     SELECT
 899       INTO this_duration_rule,
 900            this_fine_rule,
 901            this_max_fine_rule
 902       duration_rule,
 903       recurring_fine_rule,
 904       max_fine_rule
 905       FROM action.find_circ_matrix_matchpoint(
 906         circ_lib,
 907         target_copy,
 908         usr,
 909         is_renewal
 910         );
 911     SELECT INTO rcd * FROM config.rule_circ_duration
 912       WHERE id = this_duration_rule;
 913     SELECT INTO rrf * FROM config.rule_recurring_fine
 914       WHERE id = this_fine_rule;
 915     SELECT INTO rmf * FROM config.rule_max_fine
 916       WHERE id = this_max_fine_rule;
 917
 918     -- Apply the rules to this circulation
 919     EXECUTE ('UPDATE ' || tablename || ' c
 920     SET
 921       duration_rule = rcd.name,
 922       recurring_fine_rule = rrf.name,
 923       max_fine_rule = rmf.name,
 924       duration = rcd.normal,
 925       recurring_fine = rrf.normal,
 926       max_fine =
 927         CASE rmf.is_percent
 928           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 929           ELSE rmf.amount
 930         END,
 931       renewal_remaining = rcd.max_renewals
 932     FROM
 933       config.rule_circ_duration rcd,
 934       config.rule_recurring_fine rrf,
 935       config.rule_max_fine rmf,
 936                         asset.copy ac
 937     WHERE
 938       rcd.id = ' || this_duration_rule || ' AND
 939       rrf.id = ' || this_fine_rule || ' AND
 940       rmf.id = ' || this_max_fine_rule || ' AND
 941                         ac.id = c.target_copy AND
 942       c.id = ' || circ || ';');
 943
 944     -- Keep track of where we are in the process
 945     n := n + 1;
 946     IF (n % 100 = 0) THEN
 947       RAISE INFO '%', n || ' of ' || n_circs
 948         || ' (' || (100*n/n_circs) || '%) circs updated.';
 949     END IF;
 950
 951   END LOOP;
 952
 953   RETURN;
 954 END;
 955
 956 $$ LANGUAGE plpgsql;
 957
 958 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
 959
 960 -- Usage:
 961 --
 962 --   First make sure the circ matrix is loaded and the circulations
 963 --   have been staged to the extent possible (but at the very least
 964 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 965 --   circ modifiers must also be in place.
 966 --
 967 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 968 --
 969
 970 DECLARE
 971   circ_lib             INT;
 972   target_copy          INT;
 973   usr                  INT;
 974   is_renewal           BOOLEAN;
 975   this_duration_rule   INT;
 976   this_fine_rule       INT;
 977   this_max_fine_rule   INT;
 978   rcd                  config.rule_circ_duration%ROWTYPE;
 979   rrf                  config.rule_recurring_fine%ROWTYPE;
 980   rmf                  config.rule_max_fine%ROWTYPE;
 981   circ                 INT;
 982   n                    INT := 0;
 983   n_circs              INT;
 984
 985 BEGIN
 986
 987   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 988
 989   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 990
 991     -- Fetch the correct rules for this circulation
 992     EXECUTE ('
 993       SELECT
 994         circ_lib,
 995         target_copy,
 996         usr,
 997         CASE
 998           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 999           ELSE FALSE
1000         END
1001       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1002       INTO circ_lib, target_copy, usr, is_renewal ;
1003     SELECT
1004       INTO this_duration_rule,
1005            this_fine_rule,
1006            this_max_fine_rule
1007       duration_rule,
1008       recuring_fine_rule,
1009       max_fine_rule
1010       FROM action.find_circ_matrix_matchpoint(
1011         circ_lib,
1012         target_copy,
1013         usr,
1014         is_renewal
1015         );
1016     SELECT INTO rcd * FROM config.rule_circ_duration
1017       WHERE id = this_duration_rule;
1018     SELECT INTO rrf * FROM config.rule_recurring_fine
1019       WHERE id = this_fine_rule;
1020     SELECT INTO rmf * FROM config.rule_max_fine
1021       WHERE id = this_max_fine_rule;
1022
1023     -- Apply the rules to this circulation
1024     EXECUTE ('UPDATE ' || tablename || ' c
1025     SET
1026       duration_rule = rcd.name,
1027       recuring_fine_rule = rrf.name,
1028       max_fine_rule = rmf.name,
1029       duration = rcd.normal,
1030       recuring_fine = rrf.normal,
1031       max_fine =
1032         CASE rmf.is_percent
1033           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1034           ELSE rmf.amount
1035         END,
1036       renewal_remaining = rcd.max_renewals
1037     FROM
1038       config.rule_circ_duration rcd,
1039       config.rule_recuring_fine rrf,
1040       config.rule_max_fine rmf,
1041                         asset.copy ac
1042     WHERE
1043       rcd.id = ' || this_duration_rule || ' AND
1044       rrf.id = ' || this_fine_rule || ' AND
1045       rmf.id = ' || this_max_fine_rule || ' AND
1046                         ac.id = c.target_copy AND
1047       c.id = ' || circ || ';');
1048
1049     -- Keep track of where we are in the process
1050     n := n + 1;
1051     IF (n % 100 = 0) THEN
1052       RAISE INFO '%', n || ' of ' || n_circs
1053         || ' (' || (100*n/n_circs) || '%) circs updated.';
1054     END IF;
1055
1056   END LOOP;
1057
1058   RETURN;
1059 END;
1060
1061 $$ LANGUAGE plpgsql;
1062
1063 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1064
1065 -- Usage:
1066 --
1067 --   First make sure the circ matrix is loaded and the circulations
1068 --   have been staged to the extent possible (but at the very least
1069 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1070 --   circ modifiers must also be in place.
1071 --
1072 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1073 --
1074
1075 DECLARE
1076   circ_lib             INT;
1077   target_copy          INT;
1078   usr                  INT;
1079   is_renewal           BOOLEAN;
1080   this_duration_rule   INT;
1081   this_fine_rule       INT;
1082   this_max_fine_rule   INT;
1083   rcd                  config.rule_circ_duration%ROWTYPE;
1084   rrf                  config.rule_recurring_fine%ROWTYPE;
1085   rmf                  config.rule_max_fine%ROWTYPE;
1086   circ                 INT;
1087   n                    INT := 0;
1088   n_circs              INT;
1089
1090 BEGIN
1091
1092   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1093
1094   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1095
1096     -- Fetch the correct rules for this circulation
1097     EXECUTE ('
1098       SELECT
1099         circ_lib,
1100         target_copy,
1101         usr,
1102         CASE
1103           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1104           ELSE FALSE
1105         END
1106       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1107       INTO circ_lib, target_copy, usr, is_renewal ;
1108     SELECT
1109       INTO this_duration_rule,
1110            this_fine_rule,
1111            this_max_fine_rule
1112       (matchpoint).duration_rule,
1113       (matchpoint).recurring_fine_rule,
1114       (matchpoint).max_fine_rule
1115       FROM action.find_circ_matrix_matchpoint(
1116         circ_lib,
1117         target_copy,
1118         usr,
1119         is_renewal
1120         );
1121     SELECT INTO rcd * FROM config.rule_circ_duration
1122       WHERE id = this_duration_rule;
1123     SELECT INTO rrf * FROM config.rule_recurring_fine
1124       WHERE id = this_fine_rule;
1125     SELECT INTO rmf * FROM config.rule_max_fine
1126       WHERE id = this_max_fine_rule;
1127
1128     -- Apply the rules to this circulation
1129     EXECUTE ('UPDATE ' || tablename || ' c
1130     SET
1131       duration_rule = rcd.name,
1132       recurring_fine_rule = rrf.name,
1133       max_fine_rule = rmf.name,
1134       duration = rcd.normal,
1135       recurring_fine = rrf.normal,
1136       max_fine =
1137         CASE rmf.is_percent
1138           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1139           ELSE rmf.amount
1140         END,
1141       renewal_remaining = rcd.max_renewals,
1142       grace_period = rrf.grace_period
1143     FROM
1144       config.rule_circ_duration rcd,
1145       config.rule_recurring_fine rrf,
1146       config.rule_max_fine rmf,
1147                         asset.copy ac
1148     WHERE
1149       rcd.id = ' || this_duration_rule || ' AND
1150       rrf.id = ' || this_fine_rule || ' AND
1151       rmf.id = ' || this_max_fine_rule || ' AND
1152                         ac.id = c.target_copy AND
1153       c.id = ' || circ || ';');
1154
1155     -- Keep track of where we are in the process
1156     n := n + 1;
1157     IF (n % 100 = 0) THEN
1158       RAISE INFO '%', n || ' of ' || n_circs
1159         || ' (' || (100*n/n_circs) || '%) circs updated.';
1160     END IF;
1161
1162   END LOOP;
1163
1164   RETURN;
1165 END;
1166
1167 $$ LANGUAGE plpgsql;
1168
1169
1170
1171
1172 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1173
1174 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1175 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1176
1177 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1178 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1179
1180 DECLARE
1181         c                    TEXT := schemaname || '.asset_copy_legacy';
1182         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1183         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1184         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1185         stat_cat                                                 INT;
1186   stat_cat_entry       INT;
1187
1188 BEGIN
1189
1190   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1191
1192                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1193
1194                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1195                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1196                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1197
1198   END LOOP;
1199
1200   RETURN;
1201 END;
1202
1203 $$ LANGUAGE plpgsql;
1204
1205 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1206
1207 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1208 --        This will assign standing penalties as needed.
1209
1210 DECLARE
1211   org_unit  INT;
1212   usr       INT;
1213
1214 BEGIN
1215
1216   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1217
1218     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1219
1220       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1221
1222     END LOOP;
1223
1224   END LOOP;
1225
1226   RETURN;
1227
1228 END;
1229
1230 $$ LANGUAGE plpgsql;
1231
1232
1233 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1234
1235 BEGIN
1236   INSERT INTO metabib.metarecord (fingerprint, master_record)
1237     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1238       FROM  biblio.record_entry b
1239       WHERE NOT b.deleted
1240         AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1241         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1242       ORDER BY b.fingerprint, b.quality DESC;
1243   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1244     SELECT  m.id, r.id
1245       FROM  biblio.record_entry r
1246       JOIN  metabib.metarecord m USING (fingerprint)
1247      WHERE  NOT r.deleted;
1248 END;
1249
1250 $$ LANGUAGE plpgsql;
1251
1252
1253 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1254
1255 BEGIN
1256   INSERT INTO metabib.metarecord (fingerprint, master_record)
1257     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1258       FROM  biblio.record_entry b
1259       WHERE NOT b.deleted
1260         AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1261         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1262       ORDER BY b.fingerprint, b.quality DESC;
1263   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1264     SELECT  m.id, r.id
1265       FROM  biblio.record_entry r
1266         JOIN metabib.metarecord m USING (fingerprint)
1267       WHERE NOT r.deleted
1268         AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
1269 END;
1270
1271 $$ LANGUAGE plpgsql;
1272
1273
1274 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1275
1276 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1277 --        Then SELECT migration_tools.create_cards('m_foo');
1278
1279 DECLARE
1280         u                    TEXT := schemaname || '.actor_usr_legacy';
1281         c                    TEXT := schemaname || '.actor_card';
1282
1283 BEGIN
1284
1285         EXECUTE ('DELETE FROM ' || c || ';');
1286         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1287         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1288
1289   RETURN;
1290
1291 END;
1292
1293 $$ LANGUAGE plpgsql;
1294
1295
1296 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1297
1298   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1299
1300   my ($marcxml, $shortname) = @_;
1301
1302   use MARC::Record;
1303   use MARC::File::XML;
1304
1305   my $xml = $marcxml;
1306
1307   eval {
1308     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1309
1310     foreach my $field ( $marc->field('856') ) {
1311       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1312            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1313         $field->add_subfields( '9' => $shortname );
1314                                 $field->update( ind2 => '0');
1315       }
1316     }
1317
1318     $xml = $marc->as_xml_record;
1319     $xml =~ s/^<\?.+?\?>$//mo;
1320     $xml =~ s/\n//sgo;
1321     $xml =~ s/>\s+</></sgo;
1322   };
1323
1324   return $xml;
1325
1326 $$ LANGUAGE PLPERLU STABLE;
1327
1328 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT) RETURNS VOID AS $$
1329
1330 DECLARE
1331   old_volume   BIGINT;
1332   new_volume   BIGINT;
1333   bib          BIGINT;
1334   owner        INTEGER;
1335   old_label    TEXT;
1336   remainder    BIGINT;
1337
1338 BEGIN
1339
1340   -- Bail out if asked to change the label to ##URI##
1341   IF new_label = '##URI##' THEN
1342     RETURN;
1343   END IF;
1344
1345   -- Gather information
1346   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1347   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1348   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1349
1350   -- Bail out if the label already is ##URI##
1351   IF old_label = '##URI##' THEN
1352     RETURN;
1353   END IF;
1354
1355   -- Bail out if the call number label is already correct
1356   IF new_volume = old_volume THEN
1357     RETURN;
1358   END IF;
1359
1360   -- Check whether we already have a destination volume available
1361   SELECT id INTO new_volume FROM asset.call_number
1362     WHERE
1363       record = bib AND
1364       owning_lib = owner AND
1365       label = new_label AND
1366       NOT deleted;
1367
1368   -- Create destination volume if needed
1369   IF NOT FOUND THEN
1370     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1371       VALUES (1, 1, bib, owner, new_label);
1372     SELECT id INTO new_volume FROM asset.call_number
1373       WHERE
1374         record = bib AND
1375         owning_lib = owner AND
1376         label = new_label AND
1377         NOT deleted;
1378   END IF;
1379
1380   -- Move copy to destination
1381   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1382
1383   -- Delete source volume if it is now empty
1384   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1385   IF NOT FOUND THEN
1386     DELETE FROM asset.call_number WHERE id = old_volume;
1387   END IF;
1388
1389 END;
1390
1391 $$ LANGUAGE plpgsql;
1392
1393 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1394
1395         my $input = $_[0];
1396         my %zipdata;
1397
1398         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1399
1400         while (<FH>) {
1401                 chomp;
1402                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1403                 $zipdata{$zip} = [$city, $state, $county];
1404         }
1405
1406         if (defined $zipdata{$input}) {
1407                 my ($city, $state, $county) = @{$zipdata{$input}};
1408                 return [$city, $state, $county];
1409         } elsif (defined $zipdata{substr $input, 0, 5}) {
1410                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1411                 return [$city, $state, $county];
1412         } else {
1413                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1414         }
1415
1416 $$ LANGUAGE PLPERLU STABLE;
1417
1418 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1419
1420 DECLARE
1421   ou  INT;
1422         org_unit_depth INT;
1423         ou_parent INT;
1424         parent_depth INT;
1425   errors_found BOOLEAN;
1426         ou_shortname TEXT;
1427         parent_shortname TEXT;
1428         ou_type_name TEXT;
1429         parent_type TEXT;
1430         type_id INT;
1431         type_depth INT;
1432         type_parent INT;
1433         type_parent_depth INT;
1434         proper_parent TEXT;
1435
1436 BEGIN
1437
1438         errors_found := FALSE;
1439
1440 -- Checking actor.org_unit_type
1441
1442         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1443
1444                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1445                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1446
1447                 IF type_parent IS NOT NULL THEN
1448
1449                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1450
1451                         IF type_depth - type_parent_depth <> 1 THEN
1452                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1453                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1454                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1455                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1456                                 errors_found := TRUE;
1457
1458                         END IF;
1459
1460                 END IF;
1461
1462         END LOOP;
1463
1464 -- Checking actor.org_unit
1465
1466   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1467
1468                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1469                 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
1470                 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
1471                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1472                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1473                 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
1474                 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
1475
1476                 IF ou_parent IS NOT NULL THEN
1477
1478                         IF      (org_unit_depth - parent_depth <> 1) OR (
1479                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1480                         ) THEN
1481                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1482                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1483                                 errors_found := TRUE;
1484                         END IF;
1485
1486                 END IF;
1487
1488   END LOOP;
1489
1490         IF NOT errors_found THEN
1491                 RAISE INFO 'No errors found.';
1492         END IF;
1493
1494   RETURN;
1495
1496 END;
1497
1498 $$ LANGUAGE plpgsql;
1499
1500
1501 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1502
1503 BEGIN
1504
1505         DELETE FROM asset.opac_visible_copies;
1506
1507         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1508                 SELECT DISTINCT
1509                         cp.id, cp.circ_lib, cn.record
1510                 FROM
1511                         asset.copy cp
1512                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1513                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1514                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1515                         JOIN config.copy_status cs ON (cp.status = cs.id)
1516                         JOIN biblio.record_entry b ON (cn.record = b.id)
1517                 WHERE
1518                         NOT cp.deleted AND
1519                         NOT cn.deleted AND
1520                         NOT b.deleted AND
1521                         cs.opac_visible AND
1522                         cl.opac_visible AND
1523                         cp.opac_visible AND
1524                         a.opac_visible AND
1525                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1526
1527 END;
1528
1529 $$ LANGUAGE plpgsql;
1530
1531
1532 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1533
1534 DECLARE
1535   old_volume     BIGINT;
1536   new_volume     BIGINT;
1537   bib            BIGINT;
1538   old_owning_lib INTEGER;
1539         old_label      TEXT;
1540   remainder      BIGINT;
1541
1542 BEGIN
1543
1544   -- Gather information
1545   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1546   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1547   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1548
1549         -- Bail out if the new_owning_lib is not the ID of an org_unit
1550         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1551                 RAISE WARNING
1552                         '% is not a valid actor.org_unit ID; no change made.',
1553                                 new_owning_lib;
1554                 RETURN;
1555         END IF;
1556
1557   -- Bail out discreetly if the owning_lib is already correct
1558   IF new_owning_lib = old_owning_lib THEN
1559     RETURN;
1560   END IF;
1561
1562   -- Check whether we already have a destination volume available
1563   SELECT id INTO new_volume FROM asset.call_number
1564     WHERE
1565       record = bib AND
1566       owning_lib = new_owning_lib AND
1567       label = old_label AND
1568       NOT deleted;
1569
1570   -- Create destination volume if needed
1571   IF NOT FOUND THEN
1572     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1573       VALUES (1, 1, bib, new_owning_lib, old_label);
1574     SELECT id INTO new_volume FROM asset.call_number
1575       WHERE
1576         record = bib AND
1577         owning_lib = new_owning_lib AND
1578         label = old_label AND
1579         NOT deleted;
1580   END IF;
1581
1582   -- Move copy to destination
1583   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1584
1585   -- Delete source volume if it is now empty
1586   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1587   IF NOT FOUND THEN
1588     DELETE FROM asset.call_number WHERE id = old_volume;
1589   END IF;
1590
1591 END;
1592
1593 $$ LANGUAGE plpgsql;
1594
1595
1596 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1597
1598 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1599
1600 DECLARE
1601         new_owning_lib  INTEGER;
1602
1603 BEGIN
1604
1605         -- Parse the new_owner as an org unit ID or shortname
1606         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1607                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1608                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1609         ELSIF new_owner ~ E'^[0-9]+$' THEN
1610                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1611                         RAISE INFO
1612                                 '%',
1613                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1614                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1615                         new_owning_lib := new_owner::INTEGER;
1616                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1617                 END IF;
1618         ELSE
1619                 RAISE WARNING
1620                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1621                         new_owning_lib;
1622                 RETURN;
1623         END IF;
1624
1625 END;
1626
1627 $$ LANGUAGE plpgsql;
1628
1629 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1630
1631 use MARC::Record;
1632 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1633 use MARC::Charset;
1634
1635 MARC::Charset->assume_unicode(1);
1636
1637 my $xml = shift;
1638
1639 eval { my $r = MARC::Record->new_from_xml( $xml ); };
1640 if ($@) {
1641     return 0;
1642 } else {
1643     return 1;
1644 }
1645
1646 $func$ LANGUAGE PLPERLU;
1647 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1648
1649 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1650 BEGIN
1651    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1652            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1653            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1654    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1655            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1656            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1657    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1658            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1659            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1660    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1661            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1662            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1663    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1664            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1665            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1666 END;
1667 $FUNC$ LANGUAGE PLPGSQL;
1668