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(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::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_conditional (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.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1473
1474   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1475
1476   my ($marcxml, $shortname) = @_;
1477
1478   use MARC::Record;
1479   use MARC::File::XML;
1480
1481   my $xml = $marcxml;
1482
1483   eval {
1484     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1485
1486     foreach my $field ( $marc->field('856') ) {
1487       if ( ! $field->as_string('9') ) {
1488         $field->add_subfields( '9' => $shortname );
1489       }
1490     }
1491
1492     $xml = $marc->as_xml_record;
1493     $xml =~ s/^<\?.+?\?>$//mo;
1494     $xml =~ s/\n//sgo;
1495     $xml =~ s/>\s+</></sgo;
1496   };
1497
1498   return $xml;
1499
1500 $$ LANGUAGE PLPERLU STABLE;
1501
1502
1503 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1504
1505 DECLARE
1506   old_volume   BIGINT;
1507   new_volume   BIGINT;
1508   bib          BIGINT;
1509   owner        INTEGER;
1510   old_label    TEXT;
1511   remainder    BIGINT;
1512
1513 BEGIN
1514
1515   -- Bail out if asked to change the label to ##URI##
1516   IF new_label = '##URI##' THEN
1517     RETURN;
1518   END IF;
1519
1520   -- Gather information
1521   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1522   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1523   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1524
1525   -- Bail out if the label already is ##URI##
1526   IF old_label = '##URI##' THEN
1527     RETURN;
1528   END IF;
1529
1530   -- Bail out if the call number label is already correct
1531   IF new_volume = old_volume THEN
1532     RETURN;
1533   END IF;
1534
1535   -- Check whether we already have a destination volume available
1536   SELECT id INTO new_volume FROM asset.call_number
1537     WHERE
1538       record = bib AND
1539       owning_lib = owner AND
1540       label = new_label AND
1541       NOT deleted;
1542
1543   -- Create destination volume if needed
1544   IF NOT FOUND THEN
1545     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1546       VALUES (1, 1, bib, owner, new_label, cn_class);
1547     SELECT id INTO new_volume FROM asset.call_number
1548       WHERE
1549         record = bib AND
1550         owning_lib = owner AND
1551         label = new_label AND
1552         NOT deleted;
1553   END IF;
1554
1555   -- Move copy to destination
1556   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1557
1558   -- Delete source volume if it is now empty
1559   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1560   IF NOT FOUND THEN
1561     DELETE FROM asset.call_number WHERE id = old_volume;
1562   END IF;
1563
1564 END;
1565
1566 $$ LANGUAGE plpgsql;
1567
1568 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1569
1570         my $input = $_[0];
1571         my %zipdata;
1572
1573         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1574
1575         while (<FH>) {
1576                 chomp;
1577                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1578                 $zipdata{$zip} = [$city, $state, $county];
1579         }
1580
1581         if (defined $zipdata{$input}) {
1582                 my ($city, $state, $county) = @{$zipdata{$input}};
1583                 return [$city, $state, $county];
1584         } elsif (defined $zipdata{substr $input, 0, 5}) {
1585                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1586                 return [$city, $state, $county];
1587         } else {
1588                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1589         }
1590
1591 $$ LANGUAGE PLPERLU STABLE;
1592
1593 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1594
1595 DECLARE
1596   ou  INT;
1597         org_unit_depth INT;
1598         ou_parent INT;
1599         parent_depth INT;
1600   errors_found BOOLEAN;
1601         ou_shortname TEXT;
1602         parent_shortname TEXT;
1603         ou_type_name TEXT;
1604         parent_type TEXT;
1605         type_id INT;
1606         type_depth INT;
1607         type_parent INT;
1608         type_parent_depth INT;
1609         proper_parent TEXT;
1610
1611 BEGIN
1612
1613         errors_found := FALSE;
1614
1615 -- Checking actor.org_unit_type
1616
1617         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1618
1619                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1620                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1621
1622                 IF type_parent IS NOT NULL THEN
1623
1624                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1625
1626                         IF type_depth - type_parent_depth <> 1 THEN
1627                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1628                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1629                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1630                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1631                                 errors_found := TRUE;
1632
1633                         END IF;
1634
1635                 END IF;
1636
1637         END LOOP;
1638
1639 -- Checking actor.org_unit
1640
1641   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1642
1643                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1644                 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;
1645                 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;
1646                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1647                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1648                 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;
1649                 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;
1650
1651                 IF ou_parent IS NOT NULL THEN
1652
1653                         IF      (org_unit_depth - parent_depth <> 1) OR (
1654                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1655                         ) THEN
1656                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1657                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1658                                 errors_found := TRUE;
1659                         END IF;
1660
1661                 END IF;
1662
1663   END LOOP;
1664
1665         IF NOT errors_found THEN
1666                 RAISE INFO 'No errors found.';
1667         END IF;
1668
1669   RETURN;
1670
1671 END;
1672
1673 $$ LANGUAGE plpgsql;
1674
1675
1676 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1677
1678 BEGIN
1679
1680         DELETE FROM asset.opac_visible_copies;
1681
1682         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1683                 SELECT DISTINCT
1684                         cp.id, cp.circ_lib, cn.record
1685                 FROM
1686                         asset.copy cp
1687                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1688                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1689                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1690                         JOIN config.copy_status cs ON (cp.status = cs.id)
1691                         JOIN biblio.record_entry b ON (cn.record = b.id)
1692                 WHERE
1693                         NOT cp.deleted AND
1694                         NOT cn.deleted AND
1695                         NOT b.deleted AND
1696                         cs.opac_visible AND
1697                         cl.opac_visible AND
1698                         cp.opac_visible AND
1699                         a.opac_visible AND
1700                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1701
1702 END;
1703
1704 $$ LANGUAGE plpgsql;
1705
1706
1707 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1708
1709 DECLARE
1710   old_volume     BIGINT;
1711   new_volume     BIGINT;
1712   bib            BIGINT;
1713   old_owning_lib INTEGER;
1714         old_label      TEXT;
1715   remainder      BIGINT;
1716
1717 BEGIN
1718
1719   -- Gather information
1720   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1721   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1722   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1723
1724         -- Bail out if the new_owning_lib is not the ID of an org_unit
1725         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1726                 RAISE WARNING
1727                         '% is not a valid actor.org_unit ID; no change made.',
1728                                 new_owning_lib;
1729                 RETURN;
1730         END IF;
1731
1732   -- Bail out discreetly if the owning_lib is already correct
1733   IF new_owning_lib = old_owning_lib THEN
1734     RETURN;
1735   END IF;
1736
1737   -- Check whether we already have a destination volume available
1738   SELECT id INTO new_volume FROM asset.call_number
1739     WHERE
1740       record = bib AND
1741       owning_lib = new_owning_lib AND
1742       label = old_label AND
1743       NOT deleted;
1744
1745   -- Create destination volume if needed
1746   IF NOT FOUND THEN
1747     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1748       VALUES (1, 1, bib, new_owning_lib, old_label);
1749     SELECT id INTO new_volume FROM asset.call_number
1750       WHERE
1751         record = bib AND
1752         owning_lib = new_owning_lib AND
1753         label = old_label AND
1754         NOT deleted;
1755   END IF;
1756
1757   -- Move copy to destination
1758   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1759
1760   -- Delete source volume if it is now empty
1761   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1762   IF NOT FOUND THEN
1763     DELETE FROM asset.call_number WHERE id = old_volume;
1764   END IF;
1765
1766 END;
1767
1768 $$ LANGUAGE plpgsql;
1769
1770
1771 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1772
1773 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1774
1775 DECLARE
1776         new_owning_lib  INTEGER;
1777
1778 BEGIN
1779
1780         -- Parse the new_owner as an org unit ID or shortname
1781         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1782                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1783                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1784         ELSIF new_owner ~ E'^[0-9]+$' THEN
1785                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1786                         RAISE INFO
1787                                 '%',
1788                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1789                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1790                         new_owning_lib := new_owner::INTEGER;
1791                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1792                 END IF;
1793         ELSE
1794                 RAISE WARNING
1795                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1796                         new_owning_lib;
1797                 RETURN;
1798         END IF;
1799
1800 END;
1801
1802 $$ LANGUAGE plpgsql;
1803
1804 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1805
1806 use MARC::Record;
1807 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1808 use MARC::Charset;
1809
1810 MARC::Charset->assume_unicode(1);
1811
1812 my $xml = shift;
1813
1814 eval {
1815     my $r = MARC::Record->new_from_xml( $xml );
1816     my $output_xml = $r->as_xml_record();
1817 };
1818 if ($@) {
1819     return 0;
1820 } else {
1821     return 1;
1822 }
1823
1824 $func$ LANGUAGE PLPERLU;
1825 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1826
1827 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1828 BEGIN
1829    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1830            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1831            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1832    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1833            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1834            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1835    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1836            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1837            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1838    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1839            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1840            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1841    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1842            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1843            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1844    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1845            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1846            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
1847    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1848            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1849            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
1850    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
1851    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
1852    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
1853    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
1854    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
1855 END;
1856 $FUNC$ LANGUAGE PLPGSQL;
1857
1858 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1859 BEGIN
1860    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
1861    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
1862    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
1863    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
1864    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1865    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
1866    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
1867
1868    -- import any new circ rules
1869    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1870    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1871    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1872    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1873
1874    -- and permission groups
1875    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1876
1877 END;
1878 $FUNC$ LANGUAGE PLPGSQL;
1879
1880
1881 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$
1882 DECLARE
1883     name TEXT;
1884     loopq TEXT;
1885     existsq TEXT;
1886     ct INTEGER;
1887     cols TEXT[];
1888     copyst TEXT;
1889 BEGIN
1890     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1891     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1892     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
1893     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1894     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1895     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1896     FOR name IN EXECUTE loopq LOOP
1897        EXECUTE existsq INTO ct USING name;
1898        IF ct = 0 THEN
1899            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1900            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1901                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1902            EXECUTE copyst USING name;
1903        END IF;
1904     END LOOP;
1905 END;
1906 $FUNC$ LANGUAGE PLPGSQL;
1907
1908 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1909
1910 use strict;
1911 use warnings;
1912
1913 use MARC::Record;
1914 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1915 use MARC::Charset;
1916
1917 MARC::Charset->assume_unicode(1);
1918
1919 my $target_xml = shift;
1920 my $source_xml = shift;
1921 my $tags = shift;
1922
1923 my $target;
1924 my $source;
1925
1926 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1927 if ($@) {
1928     return;
1929 }
1930 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1931 if ($@) {
1932     return;
1933 }
1934
1935 my $source_id = $source->subfield('901', 'c');
1936 $source_id = $source->subfield('903', 'a') unless $source_id;
1937 my $target_id = $target->subfield('901', 'c');
1938 $target_id = $target->subfield('903', 'a') unless $target_id;
1939
1940 my %existing_fields;
1941 foreach my $tag (@$tags) {
1942     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1943     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1944     $target->insert_fields_ordered(map { $_->clone() } @to_add);
1945     if (@to_add) {
1946         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1947     }
1948 }
1949
1950 my $xml = $target->as_xml_record;
1951 $xml =~ s/^<\?.+?\?>$//mo;
1952 $xml =~ s/\n//sgo;
1953 $xml =~ s/>\s+</></sgo;
1954
1955 return $xml;
1956
1957 $func$ LANGUAGE PLPERLU;
1958 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.';
1959
1960 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1961
1962 use strict;
1963 use warnings;
1964
1965 use MARC::Record;
1966 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1967 use Text::CSV;
1968
1969 my $in_tags = shift;
1970 my $in_values = shift;
1971
1972 # hack-and-slash parsing of array-passed-as-string;
1973 # this can go away once everybody is running Postgres 9.1+
1974 my $csv = Text::CSV->new({binary => 1});
1975 $in_tags =~ s/^{//;
1976 $in_tags =~ s/}$//;
1977 my $status = $csv->parse($in_tags);
1978 my $tags = [ $csv->fields() ];
1979 $in_values =~ s/^{//;
1980 $in_values =~ s/}$//;
1981 $status = $csv->parse($in_values);
1982 my $values = [ $csv->fields() ];
1983
1984 my $marc = MARC::Record->new();
1985
1986 $marc->leader('00000nam a22000007  4500');
1987 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
1988
1989 foreach my $i (0..$#$tags) {
1990     my ($tag, $sf);
1991     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1992         $tag = $1;
1993         $sf = $2;
1994         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1995     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1996         $tag = $1;
1997         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1998     }
1999 }
2000
2001 my $xml = $marc->as_xml_record;
2002 $xml =~ s/^<\?.+?\?>$//mo;
2003 $xml =~ s/\n//sgo;
2004 $xml =~ s/>\s+</></sgo;
2005
2006 return $xml;
2007
2008 $func$ LANGUAGE PLPERLU;
2009 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2010 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2011 The second argument is an array of text containing the values to plug into each field.
2012 If the value for a given field is NULL or the empty string, it is not inserted.
2013 $$;
2014
2015 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2016
2017 my ($marcxml, $tag, $pos, $value) = @_;
2018
2019 use MARC::Record;
2020 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2021 use MARC::Charset;
2022 use strict;
2023
2024 MARC::Charset->assume_unicode(1);
2025
2026 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2027 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2028 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2029 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2030
2031 my $xml = $marcxml;
2032 eval {
2033     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2034
2035     foreach my $field ($marc->field($tag)) {
2036         $field->update("ind$pos" => $value);
2037     }
2038     $xml = $marc->as_xml_record;
2039     $xml =~ s/^<\?.+?\?>$//mo;
2040     $xml =~ s/\n//sgo;
2041     $xml =~ s/>\s+</></sgo;
2042 };
2043 return $xml;
2044
2045 $func$ LANGUAGE PLPERLU;
2046
2047 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2048 The first argument is a MARCXML string.
2049 The second argument is a MARC tag.
2050 The third argument is the indicator position, either 1 or 2.
2051 The fourth argument is the character to set the indicator value to.
2052 All occurences of the specified field will be changed.
2053 The function returns the revised MARCXML string.$$;
2054
2055 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2056     username TEXT,
2057     password TEXT,
2058     org TEXT,
2059     perm_group TEXT,
2060     first_name TEXT DEFAULT '',
2061     last_name TEXT DEFAULT ''
2062 ) RETURNS VOID AS $func$
2063 BEGIN
2064     RAISE NOTICE '%', org ;
2065     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2066     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2067     FROM   actor.org_unit aou, permission.grp_tree pgt
2068     WHERE  aou.shortname = org
2069     AND    pgt.name = perm_group;
2070 END
2071 $func$
2072 LANGUAGE PLPGSQL;
2073
2074 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2075 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2076     DECLARE
2077         target_event_def ALIAS FOR $1;
2078         orgs ALIAS FOR $2;
2079     BEGIN
2080         DROP TABLE IF EXISTS new_atevdefs;
2081         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2082         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2083             INSERT INTO action_trigger.event_definition (
2084                 active
2085                 ,owner
2086                 ,name
2087                 ,hook
2088                 ,validator
2089                 ,reactor
2090                 ,cleanup_success
2091                 ,cleanup_failure
2092                 ,delay
2093                 ,max_delay
2094                 ,usr_field
2095                 ,opt_in_setting
2096                 ,delay_field
2097                 ,group_field
2098                 ,template
2099                 ,granularity
2100                 ,repeat_delay
2101             ) SELECT
2102                 'f'
2103                 ,orgs[i]
2104                 ,name || ' (clone of '||target_event_def||')'
2105                 ,hook
2106                 ,validator
2107                 ,reactor
2108                 ,cleanup_success
2109                 ,cleanup_failure
2110                 ,delay
2111                 ,max_delay
2112                 ,usr_field
2113                 ,opt_in_setting
2114                 ,delay_field
2115                 ,group_field
2116                 ,template
2117                 ,granularity
2118                 ,repeat_delay
2119             FROM
2120                 action_trigger.event_definition
2121             WHERE
2122                 id = target_event_def
2123             ;
2124             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2125             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2126             INSERT INTO action_trigger.environment (
2127                 event_def
2128                 ,path
2129                 ,collector
2130                 ,label
2131             ) SELECT
2132                 currval('action_trigger.event_definition_id_seq')
2133                 ,path
2134                 ,collector
2135                 ,label
2136             FROM
2137                 action_trigger.environment
2138             WHERE
2139                 event_def = target_event_def
2140             ;
2141             INSERT INTO action_trigger.event_params (
2142                 event_def
2143                 ,param
2144                 ,value
2145             ) SELECT
2146                 currval('action_trigger.event_definition_id_seq')
2147                 ,param
2148                 ,value
2149             FROM
2150                 action_trigger.event_params
2151             WHERE
2152                 event_def = target_event_def
2153             ;
2154         END LOOP;
2155         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);
2156     END;
2157 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2158
2159 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2160 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2161     DECLARE
2162         target_event_def ALIAS FOR $1;
2163         orgs ALIAS FOR $2;
2164         new_interval ALIAS FOR $3;
2165     BEGIN
2166         DROP TABLE IF EXISTS new_atevdefs;
2167         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2168         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2169             INSERT INTO action_trigger.event_definition (
2170                 active
2171                 ,owner
2172                 ,name
2173                 ,hook
2174                 ,validator
2175                 ,reactor
2176                 ,cleanup_success
2177                 ,cleanup_failure
2178                 ,delay
2179                 ,max_delay
2180                 ,usr_field
2181                 ,opt_in_setting
2182                 ,delay_field
2183                 ,group_field
2184                 ,template
2185                 ,granularity
2186                 ,repeat_delay
2187             ) SELECT
2188                 'f'
2189                 ,orgs[i]
2190                 ,name || ' (clone of '||target_event_def||')'
2191                 ,hook
2192                 ,validator
2193                 ,reactor
2194                 ,cleanup_success
2195                 ,cleanup_failure
2196                 ,new_interval
2197                 ,max_delay
2198                 ,usr_field
2199                 ,opt_in_setting
2200                 ,delay_field
2201                 ,group_field
2202                 ,template
2203                 ,granularity
2204                 ,repeat_delay
2205             FROM
2206                 action_trigger.event_definition
2207             WHERE
2208                 id = target_event_def
2209             ;
2210             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2211             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2212             INSERT INTO action_trigger.environment (
2213                 event_def
2214                 ,path
2215                 ,collector
2216                 ,label
2217             ) SELECT
2218                 currval('action_trigger.event_definition_id_seq')
2219                 ,path
2220                 ,collector
2221                 ,label
2222             FROM
2223                 action_trigger.environment
2224             WHERE
2225                 event_def = target_event_def
2226             ;
2227             INSERT INTO action_trigger.event_params (
2228                 event_def
2229                 ,param
2230                 ,value
2231             ) SELECT
2232                 currval('action_trigger.event_definition_id_seq')
2233                 ,param
2234                 ,value
2235             FROM
2236                 action_trigger.event_params
2237             WHERE
2238                 event_def = target_event_def
2239             ;
2240         END LOOP;
2241         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);
2242     END;
2243 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2244
2245 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2246     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2247
2248     use MARC::Record;
2249     use MARC::File::XML;
2250     use MARC::Field;
2251
2252     my $field;
2253     eval {
2254         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2255         $field = $marc->field($tag);
2256     };
2257     return $field->as_string($subfield,$delimiter);
2258 $$ LANGUAGE PLPERLU STABLE;
2259
2260 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2261     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2262
2263     use MARC::Record;
2264     use MARC::File::XML;
2265     use MARC::Field;
2266
2267     my @fields;
2268     eval {
2269         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2270         @fields = $marc->field($tag);
2271     };
2272     my @texts;
2273     foreach my $field (@fields) {
2274         push @texts, $field->as_string($subfield,$delimiter);
2275     }
2276     return \@texts;
2277 $$ LANGUAGE PLPERLU STABLE;
2278
2279 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2280     SELECT action.find_hold_matrix_matchpoint(
2281         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2282         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2283         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2284         (SELECT usr FROM action.hold_request WHERE id = $1),
2285         (SELECT requestor FROM action.hold_request WHERE id = $1)
2286     );
2287 $$ LANGUAGE SQL;
2288
2289 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2290     SELECT action.find_circ_matrix_matchpoint(
2291         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2292         (SELECT target_copy FROM action.circulation WHERE id = $1),
2293         (SELECT usr FROM action.circulation WHERE id = $1),
2294         (SELECT COALESCE(
2295                 NULLIF(phone_renewal,false),
2296                 NULLIF(desk_renewal,false),
2297                 NULLIF(opac_renewal,false),
2298                 false
2299             ) FROM action.circulation WHERE id = $1
2300         )
2301     );
2302 $$ LANGUAGE SQL;