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,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,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_limit_set_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 UNLOGGED 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.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
 356     DECLARE
 357         full_name TEXT := $1;
 358         temp TEXT;
 359         family_name TEXT := '';
 360         first_given_name TEXT := '';
 361         second_given_name TEXT := '';
 362         suffix TEXT := '';
 363         prefix TEXT := '';
 364     BEGIN
 365         temp := full_name;
 366         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 367         IF temp ilike '%MR.%' THEN
 368             prefix := 'Mr.';
 369             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
 370         END IF;
 371         IF temp ilike '%MRS.%' THEN
 372             prefix := 'Mrs.';
 373             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
 374         END IF;
 375         IF temp ilike '%MS.%' THEN
 376             prefix := 'Ms.';
 377             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
 378         END IF;
 379         IF temp ilike '%DR.%' THEN
 380             prefix := 'Dr.';
 381             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
 382         END IF;
 383         IF temp ilike '%JR.%' THEN
 384             suffix := 'Jr.';
 385             temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
 386         END IF;
 387         IF temp ilike '%JR,%' THEN
 388             suffix := 'Jr.';
 389             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
 390         END IF;
 391         IF temp ilike '%SR.%' THEN
 392             suffix := 'Sr.';
 393             temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
 394         END IF;
 395         IF temp ilike '%SR,%' THEN
 396             suffix := 'Sr.';
 397             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
 398         END IF;
 399         IF temp like '%III%' THEN
 400             suffix := 'III';
 401             temp := REGEXP_REPLACE( temp, E'III', '' );
 402         END IF;
 403         IF temp like '%II%' THEN
 404             suffix := 'II';
 405             temp := REGEXP_REPLACE( temp, E'II', '' );
 406         END IF;
 407         IF temp like '%IV%' THEN
 408             suffix := 'IV';
 409             temp := REGEXP_REPLACE( temp, E'IV', '' );
 410         END IF;
 411
 412         temp := REGEXP_REPLACE( temp, '\(\)', '');
 413         family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
 414         family_name := REGEXP_REPLACE( family_name, ',', '' );
 415         first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
 416         first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
 417         second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
 418         second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
 419
 420         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 421     END;
 422 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 423
 424
 425 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
 426     DECLARE
 427         city_state_zip TEXT := $1;
 428         city TEXT := '';
 429         state TEXT := '';
 430         zip TEXT := '';
 431     BEGIN
 432         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;
 433         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 434         IF city_state_zip ~ ',' THEN
 435             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
 436             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
 437         ELSE
 438             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
 439                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
 440                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
 441             ELSE
 442                 IF city_state_zip ~ E'^\\S+$'  THEN
 443                     city := city_state_zip;
 444                     state := 'N/A';
 445                 ELSE
 446                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 447                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 448                 END IF;
 449             END IF;
 450         END IF;
 451         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
 452     END;
 453 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 454
 455 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
 456 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
 457     DECLARE
 458         fullstring TEXT := $1;
 459         address1 TEXT := '';
 460         address2 TEXT := '';
 461         scratch1 TEXT := '';
 462         scratch2 TEXT := '';
 463         city TEXT := '';
 464         state TEXT := '';
 465         zip TEXT := '';
 466     BEGIN
 467         zip := CASE
 468             WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
 469             THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
 470             ELSE ''
 471         END;
 472         fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 473
 474         IF fullstring ~ ',' THEN
 475             state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
 476             scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
 477         ELSE
 478             IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
 479                 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
 480                 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
 481             ELSE
 482                 IF fullstring ~ E'^\\S+$'  THEN
 483                     scratch1 := fullstring;
 484                     state := 'N/A';
 485                 ELSE
 486                     state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 487                     scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 488                 END IF;
 489             END IF;
 490         END IF;
 491
 492         IF scratch1 ~ '[\$]' THEN
 493             scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
 494             city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
 495         ELSE
 496             IF scratch1 ~ '\s' THEN
 497                 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
 498                 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
 499             ELSE
 500                 scratch2 := 'N/A';
 501                 city := scratch1;
 502             END IF;
 503         END IF;
 504
 505         IF scratch2 ~ '^\d' THEN
 506             address1 := scratch2;
 507             address2 := '';
 508         ELSE
 509             address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
 510             address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
 511         END IF;
 512
 513         RETURN ARRAY[
 514              TRIM(BOTH ' ' FROM address1)
 515             ,TRIM(BOTH ' ' FROM address2)
 516             ,TRIM(BOTH ' ' FROM city)
 517             ,TRIM(BOTH ' ' FROM state)
 518             ,TRIM(BOTH ' ' FROM zip)
 519         ];
 520     END;
 521 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 522
 523 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
 524     DECLARE
 525         n TEXT := o;
 526     BEGIN
 527         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
 528             IF o::BIGINT < t THEN
 529                 n = o::BIGINT + t;
 530             END IF;
 531         END IF;
 532
 533         RETURN n;
 534     END;
 535 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 536
 537 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
 538     DECLARE
 539         migration_schema ALIAS FOR $1;
 540         output TEXT;
 541     BEGIN
 542         FOR output IN
 543             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
 544         LOOP
 545             RETURN output;
 546         END LOOP;
 547     END;
 548 $$ LANGUAGE PLPGSQL STRICT STABLE;
 549
 550 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
 551     DECLARE
 552         migration_schema ALIAS FOR $1;
 553         output TEXT;
 554     BEGIN
 555         FOR output IN
 556             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
 557         LOOP
 558             RETURN output;
 559         END LOOP;
 560     END;
 561 $$ LANGUAGE PLPGSQL STRICT STABLE;
 562
 563 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
 564     DECLARE
 565         migration_schema ALIAS FOR $1;
 566         output TEXT;
 567     BEGIN
 568         FOR output IN
 569             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
 570         LOOP
 571             RETURN output;
 572         END LOOP;
 573     END;
 574 $$ LANGUAGE PLPGSQL STRICT STABLE;
 575
 576 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
 577     DECLARE
 578         migration_schema ALIAS FOR $1;
 579         output TEXT;
 580     BEGIN
 581         FOR output IN
 582             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
 583         LOOP
 584             RETURN output;
 585         END LOOP;
 586     END;
 587 $$ LANGUAGE PLPGSQL STRICT STABLE;
 588
 589 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
 590     DECLARE
 591         migration_schema ALIAS FOR $1;
 592         profile_map TEXT;
 593         patron_table ALIAS FOR $2;
 594         default_patron_profile ALIAS FOR $3;
 595         sql TEXT;
 596         sql_update TEXT;
 597         sql_where1 TEXT := '';
 598         sql_where2 TEXT := '';
 599         sql_where3 TEXT := '';
 600         output RECORD;
 601     BEGIN
 602         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
 603         FOR output IN
 604             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
 605         LOOP
 606             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
 607             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);
 608             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);
 609             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);
 610             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,'') || ';';
 611             --RAISE INFO 'sql = %', sql;
 612             PERFORM migration_tools.exec( $1, sql );
 613         END LOOP;
 614         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
 615         BEGIN
 616             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
 617         EXCEPTION
 618             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
 619         END;
 620     END;
 621 $$ LANGUAGE PLPGSQL STRICT STABLE;
 622
 623 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
 624     DECLARE
 625         migration_schema ALIAS FOR $1;
 626         field_map TEXT;
 627         item_table ALIAS FOR $2;
 628         sql TEXT;
 629         sql_update TEXT;
 630         sql_where1 TEXT := '';
 631         sql_where2 TEXT := '';
 632         sql_where3 TEXT := '';
 633         output RECORD;
 634     BEGIN
 635         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
 636         FOR output IN
 637             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 638         LOOP
 639             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 ';
 640             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);
 641             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);
 642             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);
 643             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,'') || ';';
 644             --RAISE INFO 'sql = %', sql;
 645             PERFORM migration_tools.exec( $1, sql );
 646         END LOOP;
 647         BEGIN
 648             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
 649         EXCEPTION
 650             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
 651         END;
 652     END;
 653 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 654
 655 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
 656     DECLARE
 657         migration_schema ALIAS FOR $1;
 658         base_copy_location_map TEXT;
 659         item_table ALIAS FOR $2;
 660         sql TEXT;
 661         sql_update TEXT;
 662         sql_where1 TEXT := '';
 663         sql_where2 TEXT := '';
 664         sql_where3 TEXT := '';
 665         output RECORD;
 666     BEGIN
 667         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
 668         FOR output IN
 669             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
 670         LOOP
 671             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
 672             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);
 673             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);
 674             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);
 675             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,'') || ';';
 676             --RAISE INFO 'sql = %', sql;
 677             PERFORM migration_tools.exec( $1, sql );
 678         END LOOP;
 679         BEGIN
 680             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
 681         EXCEPTION
 682             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
 683         END;
 684     END;
 685 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 686
 687 -- 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
 688 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
 689     DECLARE
 690         migration_schema ALIAS FOR $1;
 691         field_map TEXT;
 692         circ_table ALIAS FOR $2;
 693         item_table ALIAS FOR $3;
 694         patron_table ALIAS FOR $4;
 695         sql TEXT;
 696         sql_update TEXT;
 697         sql_where1 TEXT := '';
 698         sql_where2 TEXT := '';
 699         sql_where3 TEXT := '';
 700         sql_where4 TEXT := '';
 701         output RECORD;
 702     BEGIN
 703         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
 704         FOR output IN
 705             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
 706         LOOP
 707             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 ';
 708             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);
 709             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);
 710             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);
 711             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);
 712             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,'') || ';';
 713             --RAISE INFO 'sql = %', sql;
 714             PERFORM migration_tools.exec( $1, sql );
 715         END LOOP;
 716         BEGIN
 717             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
 718         EXCEPTION
 719             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
 720         END;
 721     END;
 722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 723
 724 -- expand_barcode
 725 --   $barcode      source barcode
 726 --   $prefix       prefix to add to barcode, NULL = add no prefix
 727 --   $maxlen       maximum length of barcode; default to 14 if left NULL
 728 --   $pad          padding string to apply to left of source barcode before adding
 729 --                 prefix and suffix; set to NULL or '' if no padding is desired
 730 --   $suffix       suffix to add to barcode, NULL = add no suffix
 731 --
 732 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
 733 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
 734 --
 735 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
 736     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
 737
 738     # default case
 739     return unless defined $barcode;
 740
 741     $prefix     = '' unless defined $prefix;
 742     $maxlen ||= 14;
 743     $pad        = '0' unless defined $pad;
 744     $suffix     = '' unless defined $suffix;
 745
 746     # bail out if adding prefix and suffix would bring new barcode over max length
 747     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
 748
 749     my $new_barcode = $barcode;
 750     if ($pad ne '') {
 751         my $pad_length = $maxlen - length($prefix) - length($suffix);
 752         if (length($barcode) < $pad_length) {
 753             # assuming we always want padding on the left
 754             # also assuming that it is possible to have the pad string be longer than 1 character
 755             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
 756         }
 757     }
 758
 759     # bail out if adding prefix and suffix would bring new barcode over max length
 760     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
 761
 762     return "$prefix$new_barcode$suffix";
 763 $$ LANGUAGE PLPERLU STABLE;
 764
 765 -- remove previous version of this function
 766 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
 767
 768 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
 769     DECLARE
 770         attempt_value ALIAS FOR $1;
 771         datatype ALIAS FOR $2;
 772     BEGIN
 773         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
 774         RETURN attempt_value;
 775     EXCEPTION
 776         WHEN OTHERS THEN RETURN NULL;
 777     END;
 778 $$ LANGUAGE PLPGSQL STRICT STABLE;
 779
 780 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
 781     DECLARE
 782         attempt_value ALIAS FOR $1;
 783         fail_value ALIAS FOR $2;
 784         output DATE;
 785     BEGIN
 786         FOR output IN
 787             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
 788         LOOP
 789             RETURN output;
 790         END LOOP;
 791     EXCEPTION
 792         WHEN OTHERS THEN
 793             FOR output IN
 794                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 795             LOOP
 796                 RETURN output;
 797             END LOOP;
 798     END;
 799 $$ LANGUAGE PLPGSQL STRICT STABLE;
 800
 801 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
 802     DECLARE
 803         attempt_value ALIAS FOR $1;
 804         fail_value ALIAS FOR $2;
 805         output TIMESTAMPTZ;
 806     BEGIN
 807         FOR output IN
 808             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
 809         LOOP
 810             RETURN output;
 811         END LOOP;
 812     EXCEPTION
 813         WHEN OTHERS THEN
 814             FOR output IN
 815                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
 816             LOOP
 817                 RETURN output;
 818             END LOOP;
 819     END;
 820 $$ LANGUAGE PLPGSQL STRICT STABLE;
 821
 822 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
 823     DECLARE
 824         attempt_value ALIAS FOR $1;
 825         fail_value ALIAS FOR $2;
 826         output DATE;
 827     BEGIN
 828         FOR output IN
 829             EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
 830         LOOP
 831             RETURN output;
 832         END LOOP;
 833     EXCEPTION
 834         WHEN OTHERS THEN
 835             FOR output IN
 836                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
 837             LOOP
 838                 RETURN output;
 839             END LOOP;
 840     END;
 841 $$ LANGUAGE PLPGSQL STRICT STABLE;
 842
 843 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 844     DECLARE
 845         attempt_value ALIAS FOR $1;
 846         fail_value ALIAS FOR $2;
 847         output NUMERIC(8,2);
 848     BEGIN
 849         FOR output IN
 850             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
 851         LOOP
 852             RETURN output;
 853         END LOOP;
 854     EXCEPTION
 855         WHEN OTHERS THEN
 856             FOR output IN
 857                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 858             LOOP
 859                 RETURN output;
 860             END LOOP;
 861     END;
 862 $$ LANGUAGE PLPGSQL STRICT STABLE;
 863
 864 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 865     DECLARE
 866         attempt_value ALIAS FOR $1;
 867         fail_value ALIAS FOR $2;
 868         output NUMERIC(6,2);
 869     BEGIN
 870         FOR output IN
 871             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
 872         LOOP
 873             RETURN output;
 874         END LOOP;
 875     EXCEPTION
 876         WHEN OTHERS THEN
 877             FOR output IN
 878                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 879             LOOP
 880                 RETURN output;
 881             END LOOP;
 882     END;
 883 $$ LANGUAGE PLPGSQL STRICT STABLE;
 884
 885 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 886     DECLARE
 887         attempt_value ALIAS FOR $1;
 888         fail_value ALIAS FOR $2;
 889         output NUMERIC(8,2);
 890     BEGIN
 891         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
 892             RAISE EXCEPTION 'too many digits';
 893         END IF;
 894         FOR output IN
 895             EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
 896         LOOP
 897             RETURN output;
 898         END LOOP;
 899     EXCEPTION
 900         WHEN OTHERS THEN
 901             FOR output IN
 902                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 903             LOOP
 904                 RETURN output;
 905             END LOOP;
 906     END;
 907 $$ LANGUAGE PLPGSQL STRICT STABLE;
 908
 909 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 910     DECLARE
 911         attempt_value ALIAS FOR $1;
 912         fail_value ALIAS FOR $2;
 913         output NUMERIC(6,2);
 914     BEGIN
 915         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
 916             RAISE EXCEPTION 'too many digits';
 917         END IF;
 918         FOR output IN
 919             EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
 920         LOOP
 921             RETURN output;
 922         END LOOP;
 923     EXCEPTION
 924         WHEN OTHERS THEN
 925             FOR output IN
 926                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 927             LOOP
 928                 RETURN output;
 929             END LOOP;
 930     END;
 931 $$ LANGUAGE PLPGSQL STRICT STABLE;
 932
 933 -- add_codabar_checkdigit
 934 --   $barcode      source barcode
 935 --
 936 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
 937 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 938 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
 939 -- input string does not meet those requirements, it is returned unchanged.
 940 --
 941 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
 942     my $barcode = shift;
 943
 944     return $barcode if $barcode !~ /^\d{13,14}$/;
 945     $barcode = substr($barcode, 0, 13); # ignore 14th digit
 946     my @digits = split //, $barcode;
 947     my $total = 0;
 948     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
 949     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
 950     my $remainder = $total % 10;
 951     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
 952     return $barcode . $checkdigit;
 953 $$ LANGUAGE PLPERLU STRICT STABLE;
 954
 955 -- add_code39mod43_checkdigit
 956 --   $barcode      source barcode
 957 --
 958 -- If the source string is 13 or 14 characters long and contains only valid
 959 -- Code 39 mod 43 characters, adds or replaces the 14th
 960 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 961 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
 962 -- input string does not meet those requirements, it is returned unchanged.
 963 --
 964 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
 965     my $barcode = shift;
 966
 967     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
 968     $barcode = substr($barcode, 0, 13); # ignore 14th character
 969
 970     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
 971     my %nums = map { $valid_chars[$_] => $_ } (0..42);
 972
 973     my $total = 0;
 974     $total += $nums{$_} foreach split(//, $barcode);
 975     my $remainder = $total % 43;
 976     my $checkdigit = $valid_chars[$remainder];
 977     return $barcode . $checkdigit;
 978 $$ LANGUAGE PLPERLU STRICT STABLE;
 979
 980 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
 981   DECLARE
 982     phone TEXT := $1;
 983     areacode TEXT := $2;
 984     temp TEXT := '';
 985     output TEXT := '';
 986     n_digits INTEGER := 0;
 987   BEGIN
 988     temp := phone;
 989     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
 990     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
 991     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
 992     IF n_digits = 7 AND areacode <> '' THEN
 993       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
 994       output := (areacode || '-' || temp);
 995     ELSE
 996       output := temp;
 997     END IF;
 998     RETURN output;
 999   END;
1000
1001 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1002
1003 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1004   my ($marcxml, $pos, $value) = @_;
1005
1006   use MARC::Record;
1007   use MARC::File::XML;
1008
1009   my $xml = $marcxml;
1010   eval {
1011     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1012     my $leader = $marc->leader();
1013     substr($leader, $pos, 1) = $value;
1014     $marc->leader($leader);
1015     $xml = $marc->as_xml_record;
1016     $xml =~ s/^<\?.+?\?>$//mo;
1017     $xml =~ s/\n//sgo;
1018     $xml =~ s/>\s+</></sgo;
1019   };
1020   return $xml;
1021 $$ LANGUAGE PLPERLU STABLE;
1022
1023 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1024   my ($marcxml, $pos, $value) = @_;
1025
1026   use MARC::Record;
1027   use MARC::File::XML;
1028
1029   my $xml = $marcxml;
1030   eval {
1031     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1032     my $f008 = $marc->field('008');
1033
1034     if ($f008) {
1035        my $field = $f008->data();
1036        substr($field, $pos, 1) = $value;
1037        $f008->update($field);
1038        $xml = $marc->as_xml_record;
1039        $xml =~ s/^<\?.+?\?>$//mo;
1040        $xml =~ s/\n//sgo;
1041        $xml =~ s/>\s+</></sgo;
1042     }
1043   };
1044   return $xml;
1045 $$ LANGUAGE PLPERLU STABLE;
1046
1047
1048 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1049   DECLARE
1050     profile ALIAS FOR $1;
1051   BEGIN
1052     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1053   END;
1054 $$ LANGUAGE PLPGSQL STRICT STABLE;
1055
1056
1057 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1058   BEGIN
1059     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1060   END;
1061 $$ LANGUAGE PLPGSQL STRICT STABLE;
1062
1063
1064 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1065
1066   my ($marcxml, $tags) = @_;
1067
1068   use MARC::Record;
1069   use MARC::File::XML;
1070
1071   my $xml = $marcxml;
1072
1073   eval {
1074     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1075     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1076
1077     my @incumbents = ();
1078
1079     foreach my $field ( $marc->fields() ) {
1080       push @incumbents, $field->as_formatted();
1081     }
1082
1083     foreach $field ( $to_insert->fields() ) {
1084       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1085         $marc->insert_fields_ordered( ($field) );
1086       }
1087     }
1088
1089     $xml = $marc->as_xml_record;
1090     $xml =~ s/^<\?.+?\?>$//mo;
1091     $xml =~ s/\n//sgo;
1092     $xml =~ s/>\s+</></sgo;
1093   };
1094
1095   return $xml;
1096
1097 $$ LANGUAGE PLPERLU STABLE;
1098
1099 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1100
1101 -- Usage:
1102 --
1103 --   First make sure the circ matrix is loaded and the circulations
1104 --   have been staged to the extent possible (but at the very least
1105 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1106 --   circ modifiers must also be in place.
1107 --
1108 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1109 --
1110
1111 DECLARE
1112   circ_lib             INT;
1113   target_copy          INT;
1114   usr                  INT;
1115   is_renewal           BOOLEAN;
1116   this_duration_rule   INT;
1117   this_fine_rule       INT;
1118   this_max_fine_rule   INT;
1119   rcd                  config.rule_circ_duration%ROWTYPE;
1120   rrf                  config.rule_recurring_fine%ROWTYPE;
1121   rmf                  config.rule_max_fine%ROWTYPE;
1122   circ                 INT;
1123   n                    INT := 0;
1124   n_circs              INT;
1125
1126 BEGIN
1127
1128   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1129
1130   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1131
1132     -- Fetch the correct rules for this circulation
1133     EXECUTE ('
1134       SELECT
1135         circ_lib,
1136         target_copy,
1137         usr,
1138         CASE
1139           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1140           ELSE FALSE
1141         END
1142       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1143       INTO circ_lib, target_copy, usr, is_renewal ;
1144     SELECT
1145       INTO this_duration_rule,
1146            this_fine_rule,
1147            this_max_fine_rule
1148       duration_rule,
1149       recurring_fine_rule,
1150       max_fine_rule
1151       FROM action.item_user_circ_test(
1152         circ_lib,
1153         target_copy,
1154         usr,
1155         is_renewal
1156         );
1157     SELECT INTO rcd * FROM config.rule_circ_duration
1158       WHERE id = this_duration_rule;
1159     SELECT INTO rrf * FROM config.rule_recurring_fine
1160       WHERE id = this_fine_rule;
1161     SELECT INTO rmf * FROM config.rule_max_fine
1162       WHERE id = this_max_fine_rule;
1163
1164     -- Apply the rules to this circulation
1165     EXECUTE ('UPDATE ' || tablename || ' c
1166     SET
1167       duration_rule = rcd.name,
1168       recurring_fine_rule = rrf.name,
1169       max_fine_rule = rmf.name,
1170       duration = rcd.normal,
1171       recurring_fine = rrf.normal,
1172       max_fine =
1173         CASE rmf.is_percent
1174           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1175           ELSE rmf.amount
1176         END,
1177       renewal_remaining = rcd.max_renewals
1178     FROM
1179       config.rule_circ_duration rcd,
1180       config.rule_recurring_fine rrf,
1181       config.rule_max_fine rmf,
1182                         asset.copy ac
1183     WHERE
1184       rcd.id = ' || this_duration_rule || ' AND
1185       rrf.id = ' || this_fine_rule || ' AND
1186       rmf.id = ' || this_max_fine_rule || ' AND
1187                         ac.id = c.target_copy AND
1188       c.id = ' || circ || ';');
1189
1190     -- Keep track of where we are in the process
1191     n := n + 1;
1192     IF (n % 100 = 0) THEN
1193       RAISE INFO '%', n || ' of ' || n_circs
1194         || ' (' || (100*n/n_circs) || '%) circs updated.';
1195     END IF;
1196
1197   END LOOP;
1198
1199   RETURN;
1200 END;
1201
1202 $$ LANGUAGE plpgsql;
1203
1204 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1205
1206 -- Usage:
1207 --
1208 --   First make sure the circ matrix is loaded and the circulations
1209 --   have been staged to the extent possible (but at the very least
1210 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1211 --   circ modifiers must also be in place.
1212 --
1213 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1214 --
1215
1216 DECLARE
1217   circ_lib             INT;
1218   target_copy          INT;
1219   usr                  INT;
1220   is_renewal           BOOLEAN;
1221   this_duration_rule   INT;
1222   this_fine_rule       INT;
1223   this_max_fine_rule   INT;
1224   rcd                  config.rule_circ_duration%ROWTYPE;
1225   rrf                  config.rule_recurring_fine%ROWTYPE;
1226   rmf                  config.rule_max_fine%ROWTYPE;
1227   circ                 INT;
1228   n                    INT := 0;
1229   n_circs              INT;
1230
1231 BEGIN
1232
1233   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1234
1235   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1236
1237     -- Fetch the correct rules for this circulation
1238     EXECUTE ('
1239       SELECT
1240         circ_lib,
1241         target_copy,
1242         usr,
1243         CASE
1244           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1245           ELSE FALSE
1246         END
1247       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1248       INTO circ_lib, target_copy, usr, is_renewal ;
1249     SELECT
1250       INTO this_duration_rule,
1251            this_fine_rule,
1252            this_max_fine_rule
1253       duration_rule,
1254       recuring_fine_rule,
1255       max_fine_rule
1256       FROM action.find_circ_matrix_matchpoint(
1257         circ_lib,
1258         target_copy,
1259         usr,
1260         is_renewal
1261         );
1262     SELECT INTO rcd * FROM config.rule_circ_duration
1263       WHERE id = this_duration_rule;
1264     SELECT INTO rrf * FROM config.rule_recurring_fine
1265       WHERE id = this_fine_rule;
1266     SELECT INTO rmf * FROM config.rule_max_fine
1267       WHERE id = this_max_fine_rule;
1268
1269     -- Apply the rules to this circulation
1270     EXECUTE ('UPDATE ' || tablename || ' c
1271     SET
1272       duration_rule = rcd.name,
1273       recuring_fine_rule = rrf.name,
1274       max_fine_rule = rmf.name,
1275       duration = rcd.normal,
1276       recuring_fine = rrf.normal,
1277       max_fine =
1278         CASE rmf.is_percent
1279           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1280           ELSE rmf.amount
1281         END,
1282       renewal_remaining = rcd.max_renewals
1283     FROM
1284       config.rule_circ_duration rcd,
1285       config.rule_recuring_fine rrf,
1286       config.rule_max_fine rmf,
1287                         asset.copy ac
1288     WHERE
1289       rcd.id = ' || this_duration_rule || ' AND
1290       rrf.id = ' || this_fine_rule || ' AND
1291       rmf.id = ' || this_max_fine_rule || ' AND
1292                         ac.id = c.target_copy AND
1293       c.id = ' || circ || ';');
1294
1295     -- Keep track of where we are in the process
1296     n := n + 1;
1297     IF (n % 100 = 0) THEN
1298       RAISE INFO '%', n || ' of ' || n_circs
1299         || ' (' || (100*n/n_circs) || '%) circs updated.';
1300     END IF;
1301
1302   END LOOP;
1303
1304   RETURN;
1305 END;
1306
1307 $$ LANGUAGE plpgsql;
1308
1309 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1310
1311 -- Usage:
1312 --
1313 --   First make sure the circ matrix is loaded and the circulations
1314 --   have been staged to the extent possible (but at the very least
1315 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1316 --   circ modifiers must also be in place.
1317 --
1318 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1319 --
1320
1321 DECLARE
1322   circ_lib             INT;
1323   target_copy          INT;
1324   usr                  INT;
1325   is_renewal           BOOLEAN;
1326   this_duration_rule   INT;
1327   this_fine_rule       INT;
1328   this_max_fine_rule   INT;
1329   rcd                  config.rule_circ_duration%ROWTYPE;
1330   rrf                  config.rule_recurring_fine%ROWTYPE;
1331   rmf                  config.rule_max_fine%ROWTYPE;
1332   circ                 INT;
1333   n                    INT := 0;
1334   n_circs              INT;
1335
1336 BEGIN
1337
1338   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1339
1340   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1341
1342     -- Fetch the correct rules for this circulation
1343     EXECUTE ('
1344       SELECT
1345         circ_lib,
1346         target_copy,
1347         usr,
1348         CASE
1349           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1350           ELSE FALSE
1351         END
1352       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1353       INTO circ_lib, target_copy, usr, is_renewal ;
1354     SELECT
1355       INTO this_duration_rule,
1356            this_fine_rule,
1357            this_max_fine_rule
1358       (matchpoint).duration_rule,
1359       (matchpoint).recurring_fine_rule,
1360       (matchpoint).max_fine_rule
1361       FROM action.find_circ_matrix_matchpoint(
1362         circ_lib,
1363         target_copy,
1364         usr,
1365         is_renewal
1366         );
1367     SELECT INTO rcd * FROM config.rule_circ_duration
1368       WHERE id = this_duration_rule;
1369     SELECT INTO rrf * FROM config.rule_recurring_fine
1370       WHERE id = this_fine_rule;
1371     SELECT INTO rmf * FROM config.rule_max_fine
1372       WHERE id = this_max_fine_rule;
1373
1374     -- Apply the rules to this circulation
1375     EXECUTE ('UPDATE ' || tablename || ' c
1376     SET
1377       duration_rule = rcd.name,
1378       recurring_fine_rule = rrf.name,
1379       max_fine_rule = rmf.name,
1380       duration = rcd.normal,
1381       recurring_fine = rrf.normal,
1382       max_fine =
1383         CASE rmf.is_percent
1384           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1385           ELSE rmf.amount
1386         END,
1387       renewal_remaining = rcd.max_renewals,
1388       grace_period = rrf.grace_period
1389     FROM
1390       config.rule_circ_duration rcd,
1391       config.rule_recurring_fine rrf,
1392       config.rule_max_fine rmf,
1393                         asset.copy ac
1394     WHERE
1395       rcd.id = ' || this_duration_rule || ' AND
1396       rrf.id = ' || this_fine_rule || ' AND
1397       rmf.id = ' || this_max_fine_rule || ' AND
1398                         ac.id = c.target_copy AND
1399       c.id = ' || circ || ';');
1400
1401     -- Keep track of where we are in the process
1402     n := n + 1;
1403     IF (n % 100 = 0) THEN
1404       RAISE INFO '%', n || ' of ' || n_circs
1405         || ' (' || (100*n/n_circs) || '%) circs updated.';
1406     END IF;
1407
1408   END LOOP;
1409
1410   RETURN;
1411 END;
1412
1413 $$ LANGUAGE plpgsql;
1414
1415 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1416
1417 -- Usage:
1418 --
1419 --   First make sure the circ matrix is loaded and the circulations
1420 --   have been staged to the extent possible (but at the very least
1421 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1422 --   circ modifiers must also be in place.
1423 --
1424 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1425 --
1426
1427 DECLARE
1428   circ_lib             INT;
1429   target_copy          INT;
1430   usr                  INT;
1431   is_renewal           BOOLEAN;
1432   this_duration_rule   INT;
1433   this_fine_rule       INT;
1434   this_max_fine_rule   INT;
1435   rcd                  config.rule_circ_duration%ROWTYPE;
1436   rrf                  config.rule_recurring_fine%ROWTYPE;
1437   rmf                  config.rule_max_fine%ROWTYPE;
1438   n                    INT := 0;
1439   n_circs              INT := 1;
1440
1441 BEGIN
1442
1443   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1444
1445   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1446
1447     -- Fetch the correct rules for this circulation
1448     EXECUTE ('
1449       SELECT
1450         circ_lib,
1451         target_copy,
1452         usr,
1453         CASE
1454           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1455           ELSE FALSE
1456         END
1457       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1458       INTO circ_lib, target_copy, usr, is_renewal ;
1459     SELECT
1460       INTO this_duration_rule,
1461            this_fine_rule,
1462            this_max_fine_rule
1463       (matchpoint).duration_rule,
1464       (matchpoint).recurring_fine_rule,
1465       (matchpoint).max_fine_rule
1466       FROM action.find_circ_matrix_matchpoint(
1467         circ_lib,
1468         target_copy,
1469         usr,
1470         is_renewal
1471         );
1472     SELECT INTO rcd * FROM config.rule_circ_duration
1473       WHERE id = this_duration_rule;
1474     SELECT INTO rrf * FROM config.rule_recurring_fine
1475       WHERE id = this_fine_rule;
1476     SELECT INTO rmf * FROM config.rule_max_fine
1477       WHERE id = this_max_fine_rule;
1478
1479     -- Apply the rules to this circulation
1480     EXECUTE ('UPDATE ' || tablename || ' c
1481     SET
1482       duration_rule = rcd.name,
1483       recurring_fine_rule = rrf.name,
1484       max_fine_rule = rmf.name,
1485       duration = rcd.normal,
1486       recurring_fine = rrf.normal,
1487       max_fine =
1488         CASE rmf.is_percent
1489           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1490           ELSE rmf.amount
1491         END,
1492       renewal_remaining = rcd.max_renewals,
1493       grace_period = rrf.grace_period
1494     FROM
1495       config.rule_circ_duration rcd,
1496       config.rule_recurring_fine rrf,
1497       config.rule_max_fine rmf,
1498                         asset.copy ac
1499     WHERE
1500       rcd.id = ' || this_duration_rule || ' AND
1501       rrf.id = ' || this_fine_rule || ' AND
1502       rmf.id = ' || this_max_fine_rule || ' AND
1503                         ac.id = c.target_copy AND
1504       c.id = ' || circ || ';');
1505
1506     -- Keep track of where we are in the process
1507     n := n + 1;
1508     IF (n % 100 = 0) THEN
1509       RAISE INFO '%', n || ' of ' || n_circs
1510         || ' (' || (100*n/n_circs) || '%) circs updated.';
1511     END IF;
1512
1513   --END LOOP;
1514
1515   RETURN;
1516 END;
1517
1518 $$ LANGUAGE plpgsql;
1519
1520
1521
1522
1523 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1524
1525 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1526 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1527
1528 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1529 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1530
1531 DECLARE
1532         c                    TEXT := schemaname || '.asset_copy_legacy';
1533         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1534         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1535         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1536         stat_cat                                                 INT;
1537   stat_cat_entry       INT;
1538
1539 BEGIN
1540
1541   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1542
1543                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1544
1545                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1546                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1547                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1548
1549   END LOOP;
1550
1551   RETURN;
1552 END;
1553
1554 $$ LANGUAGE plpgsql;
1555
1556 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1557
1558 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1559 --        This will assign standing penalties as needed.
1560
1561 DECLARE
1562   org_unit  INT;
1563   usr       INT;
1564
1565 BEGIN
1566
1567   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1568
1569     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1570
1571       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1572
1573     END LOOP;
1574
1575   END LOOP;
1576
1577   RETURN;
1578
1579 END;
1580
1581 $$ LANGUAGE plpgsql;
1582
1583
1584 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1585
1586 BEGIN
1587   INSERT INTO metabib.metarecord (fingerprint, master_record)
1588     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1589       FROM  biblio.record_entry b
1590       WHERE NOT b.deleted
1591         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)
1592         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1593       ORDER BY b.fingerprint, b.quality DESC;
1594   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1595     SELECT  m.id, r.id
1596       FROM  biblio.record_entry r
1597       JOIN  metabib.metarecord m USING (fingerprint)
1598      WHERE  NOT r.deleted;
1599 END;
1600
1601 $$ LANGUAGE plpgsql;
1602
1603
1604 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1605
1606 BEGIN
1607   INSERT INTO metabib.metarecord (fingerprint, master_record)
1608     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1609       FROM  biblio.record_entry b
1610       WHERE NOT b.deleted
1611         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)
1612         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1613       ORDER BY b.fingerprint, b.quality DESC;
1614   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1615     SELECT  m.id, r.id
1616       FROM  biblio.record_entry r
1617         JOIN metabib.metarecord m USING (fingerprint)
1618       WHERE NOT r.deleted
1619         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);
1620 END;
1621
1622 $$ LANGUAGE plpgsql;
1623
1624
1625 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1626
1627 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1628 --        Then SELECT migration_tools.create_cards('m_foo');
1629
1630 DECLARE
1631         u                    TEXT := schemaname || '.actor_usr_legacy';
1632         c                    TEXT := schemaname || '.actor_card';
1633
1634 BEGIN
1635
1636         EXECUTE ('DELETE FROM ' || c || ';');
1637         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1638         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1639
1640   RETURN;
1641
1642 END;
1643
1644 $$ LANGUAGE plpgsql;
1645
1646
1647 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1648
1649   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1650
1651   my ($marcxml, $shortname) = @_;
1652
1653   use MARC::Record;
1654   use MARC::File::XML;
1655
1656   my $xml = $marcxml;
1657
1658   eval {
1659     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1660
1661     foreach my $field ( $marc->field('856') ) {
1662       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1663            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1664         $field->add_subfields( '9' => $shortname );
1665                                 $field->update( ind2 => '0');
1666       }
1667     }
1668
1669     $xml = $marc->as_xml_record;
1670     $xml =~ s/^<\?.+?\?>$//mo;
1671     $xml =~ s/\n//sgo;
1672     $xml =~ s/>\s+</></sgo;
1673   };
1674
1675   return $xml;
1676
1677 $$ LANGUAGE PLPERLU STABLE;
1678
1679 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1680
1681   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1682
1683   my ($marcxml, $shortname) = @_;
1684
1685   use MARC::Record;
1686   use MARC::File::XML;
1687
1688   my $xml = $marcxml;
1689
1690   eval {
1691     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1692
1693     foreach my $field ( $marc->field('856') ) {
1694       if ( ! $field->as_string('9') ) {
1695         $field->add_subfields( '9' => $shortname );
1696       }
1697     }
1698
1699     $xml = $marc->as_xml_record;
1700     $xml =~ s/^<\?.+?\?>$//mo;
1701     $xml =~ s/\n//sgo;
1702     $xml =~ s/>\s+</></sgo;
1703   };
1704
1705   return $xml;
1706
1707 $$ LANGUAGE PLPERLU STABLE;
1708
1709
1710 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1711
1712 DECLARE
1713   old_volume   BIGINT;
1714   new_volume   BIGINT;
1715   bib          BIGINT;
1716   owner        INTEGER;
1717   old_label    TEXT;
1718   remainder    BIGINT;
1719
1720 BEGIN
1721
1722   -- Bail out if asked to change the label to ##URI##
1723   IF new_label = '##URI##' THEN
1724     RETURN;
1725   END IF;
1726
1727   -- Gather information
1728   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1729   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1730   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1731
1732   -- Bail out if the label already is ##URI##
1733   IF old_label = '##URI##' THEN
1734     RETURN;
1735   END IF;
1736
1737   -- Bail out if the call number label is already correct
1738   IF new_volume = old_volume THEN
1739     RETURN;
1740   END IF;
1741
1742   -- Check whether we already have a destination volume available
1743   SELECT id INTO new_volume FROM asset.call_number
1744     WHERE
1745       record = bib AND
1746       owning_lib = owner AND
1747       label = new_label AND
1748       NOT deleted;
1749
1750   -- Create destination volume if needed
1751   IF NOT FOUND THEN
1752     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1753       VALUES (1, 1, bib, owner, new_label, cn_class);
1754     SELECT id INTO new_volume FROM asset.call_number
1755       WHERE
1756         record = bib AND
1757         owning_lib = owner AND
1758         label = new_label AND
1759         NOT deleted;
1760   END IF;
1761
1762   -- Move copy to destination
1763   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1764
1765   -- Delete source volume if it is now empty
1766   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1767   IF NOT FOUND THEN
1768     DELETE FROM asset.call_number WHERE id = old_volume;
1769   END IF;
1770
1771 END;
1772
1773 $$ LANGUAGE plpgsql;
1774
1775 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1776
1777         my $input = $_[0];
1778         my %zipdata;
1779
1780         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1781
1782         while (<FH>) {
1783                 chomp;
1784                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1785                 $zipdata{$zip} = [$city, $state, $county];
1786         }
1787
1788         if (defined $zipdata{$input}) {
1789                 my ($city, $state, $county) = @{$zipdata{$input}};
1790                 return [$city, $state, $county];
1791         } elsif (defined $zipdata{substr $input, 0, 5}) {
1792                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1793                 return [$city, $state, $county];
1794         } else {
1795                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1796         }
1797
1798 $$ LANGUAGE PLPERLU STABLE;
1799
1800 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1801
1802 DECLARE
1803   ou  INT;
1804         org_unit_depth INT;
1805         ou_parent INT;
1806         parent_depth INT;
1807   errors_found BOOLEAN;
1808         ou_shortname TEXT;
1809         parent_shortname TEXT;
1810         ou_type_name TEXT;
1811         parent_type TEXT;
1812         type_id INT;
1813         type_depth INT;
1814         type_parent INT;
1815         type_parent_depth INT;
1816         proper_parent TEXT;
1817
1818 BEGIN
1819
1820         errors_found := FALSE;
1821
1822 -- Checking actor.org_unit_type
1823
1824         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1825
1826                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1827                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1828
1829                 IF type_parent IS NOT NULL THEN
1830
1831                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1832
1833                         IF type_depth - type_parent_depth <> 1 THEN
1834                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1835                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1836                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1837                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1838                                 errors_found := TRUE;
1839
1840                         END IF;
1841
1842                 END IF;
1843
1844         END LOOP;
1845
1846 -- Checking actor.org_unit
1847
1848   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1849
1850                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1851                 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;
1852                 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;
1853                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1854                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1855                 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;
1856                 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;
1857
1858                 IF ou_parent IS NOT NULL THEN
1859
1860                         IF      (org_unit_depth - parent_depth <> 1) OR (
1861                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1862                         ) THEN
1863                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1864                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1865                                 errors_found := TRUE;
1866                         END IF;
1867
1868                 END IF;
1869
1870   END LOOP;
1871
1872         IF NOT errors_found THEN
1873                 RAISE INFO 'No errors found.';
1874         END IF;
1875
1876   RETURN;
1877
1878 END;
1879
1880 $$ LANGUAGE plpgsql;
1881
1882
1883 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1884
1885 BEGIN
1886
1887         DELETE FROM asset.opac_visible_copies;
1888
1889         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1890                 SELECT DISTINCT
1891                         cp.id, cp.circ_lib, cn.record
1892                 FROM
1893                         asset.copy cp
1894                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1895                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1896                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1897                         JOIN config.copy_status cs ON (cp.status = cs.id)
1898                         JOIN biblio.record_entry b ON (cn.record = b.id)
1899                 WHERE
1900                         NOT cp.deleted AND
1901                         NOT cn.deleted AND
1902                         NOT b.deleted AND
1903                         cs.opac_visible AND
1904                         cl.opac_visible AND
1905                         cp.opac_visible AND
1906                         a.opac_visible AND
1907                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1908
1909 END;
1910
1911 $$ LANGUAGE plpgsql;
1912
1913
1914 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1915
1916 DECLARE
1917   old_volume     BIGINT;
1918   new_volume     BIGINT;
1919   bib            BIGINT;
1920   old_owning_lib INTEGER;
1921         old_label      TEXT;
1922   remainder      BIGINT;
1923
1924 BEGIN
1925
1926   -- Gather information
1927   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1928   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1929   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1930
1931         -- Bail out if the new_owning_lib is not the ID of an org_unit
1932         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1933                 RAISE WARNING
1934                         '% is not a valid actor.org_unit ID; no change made.',
1935                                 new_owning_lib;
1936                 RETURN;
1937         END IF;
1938
1939   -- Bail out discreetly if the owning_lib is already correct
1940   IF new_owning_lib = old_owning_lib THEN
1941     RETURN;
1942   END IF;
1943
1944   -- Check whether we already have a destination volume available
1945   SELECT id INTO new_volume FROM asset.call_number
1946     WHERE
1947       record = bib AND
1948       owning_lib = new_owning_lib AND
1949       label = old_label AND
1950       NOT deleted;
1951
1952   -- Create destination volume if needed
1953   IF NOT FOUND THEN
1954     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1955       VALUES (1, 1, bib, new_owning_lib, old_label);
1956     SELECT id INTO new_volume FROM asset.call_number
1957       WHERE
1958         record = bib AND
1959         owning_lib = new_owning_lib AND
1960         label = old_label AND
1961         NOT deleted;
1962   END IF;
1963
1964   -- Move copy to destination
1965   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1966
1967   -- Delete source volume if it is now empty
1968   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1969   IF NOT FOUND THEN
1970     DELETE FROM asset.call_number WHERE id = old_volume;
1971   END IF;
1972
1973 END;
1974
1975 $$ LANGUAGE plpgsql;
1976
1977
1978 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1979
1980 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1981
1982 DECLARE
1983         new_owning_lib  INTEGER;
1984
1985 BEGIN
1986
1987         -- Parse the new_owner as an org unit ID or shortname
1988         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1989                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1990                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1991         ELSIF new_owner ~ E'^[0-9]+$' THEN
1992                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1993                         RAISE INFO
1994                                 '%',
1995                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
1996                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1997                         new_owning_lib := new_owner::INTEGER;
1998                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1999                 END IF;
2000         ELSE
2001                 RAISE WARNING
2002                         '% is not a valid actor.org_unit shortname or ID; no change made.',
2003                         new_owning_lib;
2004                 RETURN;
2005         END IF;
2006
2007 END;
2008
2009 $$ LANGUAGE plpgsql;
2010
2011 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2012
2013 use MARC::Record;
2014 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2015 use MARC::Charset;
2016
2017 MARC::Charset->assume_unicode(1);
2018
2019 my $xml = shift;
2020
2021 eval {
2022     my $r = MARC::Record->new_from_xml( $xml );
2023     my $output_xml = $r->as_xml_record();
2024 };
2025 if ($@) {
2026     return 0;
2027 } else {
2028     return 1;
2029 }
2030
2031 $func$ LANGUAGE PLPERLU;
2032 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2033
2034 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2035 BEGIN
2036    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2037            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2038            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2039    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2040            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2041            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2042    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2043            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2044            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2045    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2046            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2047            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2048    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2049            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2050            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2051    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2052            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2053            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2054    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2055            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2056            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2057    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2058    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2059    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2060    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2061    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2062 END;
2063 $FUNC$ LANGUAGE PLPGSQL;
2064
2065 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2066 BEGIN
2067    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2068    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2069    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2070    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2071    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2072    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2073    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2074
2075    -- import any new circ rules
2076    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2077    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2078    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2079    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2080
2081    -- and permission groups
2082    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2083
2084 END;
2085 $FUNC$ LANGUAGE PLPGSQL;
2086
2087
2088 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$
2089 DECLARE
2090     name TEXT;
2091     loopq TEXT;
2092     existsq TEXT;
2093     ct INTEGER;
2094     cols TEXT[];
2095     copyst TEXT;
2096 BEGIN
2097     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2098     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2099     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2100     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2101     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2102     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2103     FOR name IN EXECUTE loopq LOOP
2104        EXECUTE existsq INTO ct USING name;
2105        IF ct = 0 THEN
2106            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2107            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2108                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2109            EXECUTE copyst USING name;
2110        END IF;
2111     END LOOP;
2112 END;
2113 $FUNC$ LANGUAGE PLPGSQL;
2114
2115 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2116
2117 use strict;
2118 use warnings;
2119
2120 use MARC::Record;
2121 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2122 use MARC::Charset;
2123
2124 MARC::Charset->assume_unicode(1);
2125
2126 my $target_xml = shift;
2127 my $source_xml = shift;
2128 my $tags = shift;
2129
2130 my $target;
2131 my $source;
2132
2133 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2134 if ($@) {
2135     return;
2136 }
2137 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2138 if ($@) {
2139     return;
2140 }
2141
2142 my $source_id = $source->subfield('901', 'c');
2143 $source_id = $source->subfield('903', 'a') unless $source_id;
2144 my $target_id = $target->subfield('901', 'c');
2145 $target_id = $target->subfield('903', 'a') unless $target_id;
2146
2147 my %existing_fields;
2148 foreach my $tag (@$tags) {
2149     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2150     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2151     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2152     if (@to_add) {
2153         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2154     }
2155 }
2156
2157 my $xml = $target->as_xml_record;
2158 $xml =~ s/^<\?.+?\?>$//mo;
2159 $xml =~ s/\n//sgo;
2160 $xml =~ s/>\s+</></sgo;
2161
2162 return $xml;
2163
2164 $func$ LANGUAGE PLPERLU;
2165 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.';
2166
2167 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2168
2169 use strict;
2170 use warnings;
2171
2172 use MARC::Record;
2173 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2174 use Text::CSV;
2175
2176 my $in_tags = shift;
2177 my $in_values = shift;
2178
2179 # hack-and-slash parsing of array-passed-as-string;
2180 # this can go away once everybody is running Postgres 9.1+
2181 my $csv = Text::CSV->new({binary => 1});
2182 $in_tags =~ s/^{//;
2183 $in_tags =~ s/}$//;
2184 my $status = $csv->parse($in_tags);
2185 my $tags = [ $csv->fields() ];
2186 $in_values =~ s/^{//;
2187 $in_values =~ s/}$//;
2188 $status = $csv->parse($in_values);
2189 my $values = [ $csv->fields() ];
2190
2191 my $marc = MARC::Record->new();
2192
2193 $marc->leader('00000nam a22000007  4500');
2194 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2195
2196 foreach my $i (0..$#$tags) {
2197     my ($tag, $sf);
2198     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2199         $tag = $1;
2200         $sf = $2;
2201         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2202     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2203         $tag = $1;
2204         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2205     }
2206 }
2207
2208 my $xml = $marc->as_xml_record;
2209 $xml =~ s/^<\?.+?\?>$//mo;
2210 $xml =~ s/\n//sgo;
2211 $xml =~ s/>\s+</></sgo;
2212
2213 return $xml;
2214
2215 $func$ LANGUAGE PLPERLU;
2216 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2217 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2218 The second argument is an array of text containing the values to plug into each field.
2219 If the value for a given field is NULL or the empty string, it is not inserted.
2220 $$;
2221
2222 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2223
2224 my ($marcxml, $tag, $pos, $value) = @_;
2225
2226 use MARC::Record;
2227 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2228 use MARC::Charset;
2229 use strict;
2230
2231 MARC::Charset->assume_unicode(1);
2232
2233 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2234 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2235 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2236 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2237
2238 my $xml = $marcxml;
2239 eval {
2240     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2241
2242     foreach my $field ($marc->field($tag)) {
2243         $field->update("ind$pos" => $value);
2244     }
2245     $xml = $marc->as_xml_record;
2246     $xml =~ s/^<\?.+?\?>$//mo;
2247     $xml =~ s/\n//sgo;
2248     $xml =~ s/>\s+</></sgo;
2249 };
2250 return $xml;
2251
2252 $func$ LANGUAGE PLPERLU;
2253
2254 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2255 The first argument is a MARCXML string.
2256 The second argument is a MARC tag.
2257 The third argument is the indicator position, either 1 or 2.
2258 The fourth argument is the character to set the indicator value to.
2259 All occurences of the specified field will be changed.
2260 The function returns the revised MARCXML string.$$;
2261
2262 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2263     username TEXT,
2264     password TEXT,
2265     org TEXT,
2266     perm_group TEXT,
2267     first_name TEXT DEFAULT '',
2268     last_name TEXT DEFAULT ''
2269 ) RETURNS VOID AS $func$
2270 BEGIN
2271     RAISE NOTICE '%', org ;
2272     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2273     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2274     FROM   actor.org_unit aou, permission.grp_tree pgt
2275     WHERE  aou.shortname = org
2276     AND    pgt.name = perm_group;
2277 END
2278 $func$
2279 LANGUAGE PLPGSQL;
2280
2281 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2282 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2283     DECLARE
2284         target_event_def ALIAS FOR $1;
2285         orgs ALIAS FOR $2;
2286     BEGIN
2287         DROP TABLE IF EXISTS new_atevdefs;
2288         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2289         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2290             INSERT INTO action_trigger.event_definition (
2291                 active
2292                 ,owner
2293                 ,name
2294                 ,hook
2295                 ,validator
2296                 ,reactor
2297                 ,cleanup_success
2298                 ,cleanup_failure
2299                 ,delay
2300                 ,max_delay
2301                 ,usr_field
2302                 ,opt_in_setting
2303                 ,delay_field
2304                 ,group_field
2305                 ,template
2306                 ,granularity
2307                 ,repeat_delay
2308             ) SELECT
2309                 'f'
2310                 ,orgs[i]
2311                 ,name || ' (clone of '||target_event_def||')'
2312                 ,hook
2313                 ,validator
2314                 ,reactor
2315                 ,cleanup_success
2316                 ,cleanup_failure
2317                 ,delay
2318                 ,max_delay
2319                 ,usr_field
2320                 ,opt_in_setting
2321                 ,delay_field
2322                 ,group_field
2323                 ,template
2324                 ,granularity
2325                 ,repeat_delay
2326             FROM
2327                 action_trigger.event_definition
2328             WHERE
2329                 id = target_event_def
2330             ;
2331             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2332             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2333             INSERT INTO action_trigger.environment (
2334                 event_def
2335                 ,path
2336                 ,collector
2337                 ,label
2338             ) SELECT
2339                 currval('action_trigger.event_definition_id_seq')
2340                 ,path
2341                 ,collector
2342                 ,label
2343             FROM
2344                 action_trigger.environment
2345             WHERE
2346                 event_def = target_event_def
2347             ;
2348             INSERT INTO action_trigger.event_params (
2349                 event_def
2350                 ,param
2351                 ,value
2352             ) SELECT
2353                 currval('action_trigger.event_definition_id_seq')
2354                 ,param
2355                 ,value
2356             FROM
2357                 action_trigger.event_params
2358             WHERE
2359                 event_def = target_event_def
2360             ;
2361         END LOOP;
2362         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);
2363     END;
2364 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2365
2366 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2367 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2368     DECLARE
2369         target_event_def ALIAS FOR $1;
2370         orgs ALIAS FOR $2;
2371         new_interval ALIAS FOR $3;
2372     BEGIN
2373         DROP TABLE IF EXISTS new_atevdefs;
2374         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2375         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2376             INSERT INTO action_trigger.event_definition (
2377                 active
2378                 ,owner
2379                 ,name
2380                 ,hook
2381                 ,validator
2382                 ,reactor
2383                 ,cleanup_success
2384                 ,cleanup_failure
2385                 ,delay
2386                 ,max_delay
2387                 ,usr_field
2388                 ,opt_in_setting
2389                 ,delay_field
2390                 ,group_field
2391                 ,template
2392                 ,granularity
2393                 ,repeat_delay
2394             ) SELECT
2395                 'f'
2396                 ,orgs[i]
2397                 ,name || ' (clone of '||target_event_def||')'
2398                 ,hook
2399                 ,validator
2400                 ,reactor
2401                 ,cleanup_success
2402                 ,cleanup_failure
2403                 ,new_interval
2404                 ,max_delay
2405                 ,usr_field
2406                 ,opt_in_setting
2407                 ,delay_field
2408                 ,group_field
2409                 ,template
2410                 ,granularity
2411                 ,repeat_delay
2412             FROM
2413                 action_trigger.event_definition
2414             WHERE
2415                 id = target_event_def
2416             ;
2417             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2418             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2419             INSERT INTO action_trigger.environment (
2420                 event_def
2421                 ,path
2422                 ,collector
2423                 ,label
2424             ) SELECT
2425                 currval('action_trigger.event_definition_id_seq')
2426                 ,path
2427                 ,collector
2428                 ,label
2429             FROM
2430                 action_trigger.environment
2431             WHERE
2432                 event_def = target_event_def
2433             ;
2434             INSERT INTO action_trigger.event_params (
2435                 event_def
2436                 ,param
2437                 ,value
2438             ) SELECT
2439                 currval('action_trigger.event_definition_id_seq')
2440                 ,param
2441                 ,value
2442             FROM
2443                 action_trigger.event_params
2444             WHERE
2445                 event_def = target_event_def
2446             ;
2447         END LOOP;
2448         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);
2449     END;
2450 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2451
2452 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2453     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2454
2455     use MARC::Record;
2456     use MARC::File::XML;
2457     use MARC::Field;
2458
2459     my $field;
2460     eval {
2461         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2462         $field = $marc->field($tag);
2463     };
2464     return $field->as_string($subfield,$delimiter);
2465 $$ LANGUAGE PLPERLU STABLE;
2466
2467 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2468     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2469
2470     use MARC::Record;
2471     use MARC::File::XML;
2472     use MARC::Field;
2473
2474     my @fields;
2475     eval {
2476         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2477         @fields = $marc->field($tag);
2478     };
2479     my @texts;
2480     foreach my $field (@fields) {
2481         push @texts, $field->as_string($subfield,$delimiter);
2482     }
2483     return \@texts;
2484 $$ LANGUAGE PLPERLU STABLE;
2485
2486 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2487     SELECT action.find_hold_matrix_matchpoint(
2488         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2489         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2490         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2491         (SELECT usr FROM action.hold_request WHERE id = $1),
2492         (SELECT requestor FROM action.hold_request WHERE id = $1)
2493     );
2494 $$ LANGUAGE SQL;
2495
2496 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2497     SELECT action.hold_request_permit_test(
2498         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2499         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2500         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2501         (SELECT usr FROM action.hold_request WHERE id = $1),
2502         (SELECT requestor FROM action.hold_request WHERE id = $1)
2503     );
2504 $$ LANGUAGE SQL;
2505
2506 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2507     SELECT action.find_circ_matrix_matchpoint(
2508         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2509         (SELECT target_copy FROM action.circulation WHERE id = $1),
2510         (SELECT usr FROM action.circulation WHERE id = $1),
2511         (SELECT COALESCE(
2512                 NULLIF(phone_renewal,false),
2513                 NULLIF(desk_renewal,false),
2514                 NULLIF(opac_renewal,false),
2515                 false
2516             ) FROM action.circulation WHERE id = $1
2517         )
2518     );
2519 $$ LANGUAGE SQL;
2520
2521 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2522     DECLARE
2523         test ALIAS FOR $1;
2524     BEGIN
2525         IF NOT test THEN
2526             RAISE EXCEPTION 'assertion';
2527         END IF;
2528     END;
2529 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2530
2531 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2532     DECLARE
2533         test ALIAS FOR $1;
2534         msg ALIAS FOR $2;
2535     BEGIN
2536         IF NOT test THEN
2537             RAISE EXCEPTION '%', msg;
2538         END IF;
2539     END;
2540 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2541
2542 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2543     DECLARE
2544         test ALIAS FOR $1;
2545         fail_msg ALIAS FOR $2;
2546         success_msg ALIAS FOR $3;
2547     BEGIN
2548         IF NOT test THEN
2549             RAISE EXCEPTION '%', fail_msg;
2550         END IF;
2551         RETURN success_msg;
2552     END;
2553 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2554