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