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_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
 844     DECLARE
 845         attempt_value ALIAS FOR $1;
 846         fail_value ALIAS FOR $2;
 847         output TIMESTAMP;
 848     BEGIN
 849             output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
 850             RETURN output;
 851     EXCEPTION
 852         WHEN OTHERS THEN
 853             FOR output IN
 854                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
 855             LOOP
 856                 RETURN output;
 857             END LOOP;
 858     END;
 859 $$ LANGUAGE PLPGSQL STRICT STABLE;
 860
 861 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 862     DECLARE
 863         attempt_value ALIAS FOR $1;
 864         fail_value ALIAS FOR $2;
 865         output NUMERIC(8,2);
 866     BEGIN
 867         FOR output IN
 868             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
 869         LOOP
 870             RETURN output;
 871         END LOOP;
 872     EXCEPTION
 873         WHEN OTHERS THEN
 874             FOR output IN
 875                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 876             LOOP
 877                 RETURN output;
 878             END LOOP;
 879     END;
 880 $$ LANGUAGE PLPGSQL STRICT STABLE;
 881
 882 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 883     DECLARE
 884         attempt_value ALIAS FOR $1;
 885         fail_value ALIAS FOR $2;
 886         output NUMERIC(6,2);
 887     BEGIN
 888         FOR output IN
 889             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
 890         LOOP
 891             RETURN output;
 892         END LOOP;
 893     EXCEPTION
 894         WHEN OTHERS THEN
 895             FOR output IN
 896                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 897             LOOP
 898                 RETURN output;
 899             END LOOP;
 900     END;
 901 $$ LANGUAGE PLPGSQL STRICT STABLE;
 902
 903 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
 904     DECLARE
 905         attempt_value ALIAS FOR $1;
 906         fail_value ALIAS FOR $2;
 907         output NUMERIC(8,2);
 908     BEGIN
 909         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
 910             RAISE EXCEPTION 'too many digits';
 911         END IF;
 912         FOR output IN
 913             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;'
 914         LOOP
 915             RETURN output;
 916         END LOOP;
 917     EXCEPTION
 918         WHEN OTHERS THEN
 919             FOR output IN
 920                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
 921             LOOP
 922                 RETURN output;
 923             END LOOP;
 924     END;
 925 $$ LANGUAGE PLPGSQL STRICT STABLE;
 926
 927 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
 928     DECLARE
 929         attempt_value ALIAS FOR $1;
 930         fail_value ALIAS FOR $2;
 931         output NUMERIC(6,2);
 932     BEGIN
 933         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
 934             RAISE EXCEPTION 'too many digits';
 935         END IF;
 936         FOR output IN
 937             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;'
 938         LOOP
 939             RETURN output;
 940         END LOOP;
 941     EXCEPTION
 942         WHEN OTHERS THEN
 943             FOR output IN
 944                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
 945             LOOP
 946                 RETURN output;
 947             END LOOP;
 948     END;
 949 $$ LANGUAGE PLPGSQL STRICT STABLE;
 950
 951 -- add_codabar_checkdigit
 952 --   $barcode      source barcode
 953 --
 954 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
 955 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 956 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
 957 -- input string does not meet those requirements, it is returned unchanged.
 958 --
 959 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
 960     my $barcode = shift;
 961
 962     return $barcode if $barcode !~ /^\d{13,14}$/;
 963     $barcode = substr($barcode, 0, 13); # ignore 14th digit
 964     my @digits = split //, $barcode;
 965     my $total = 0;
 966     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
 967     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
 968     my $remainder = $total % 10;
 969     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
 970     return $barcode . $checkdigit;
 971 $$ LANGUAGE PLPERLU STRICT STABLE;
 972
 973 -- add_code39mod43_checkdigit
 974 --   $barcode      source barcode
 975 --
 976 -- If the source string is 13 or 14 characters long and contains only valid
 977 -- Code 39 mod 43 characters, adds or replaces the 14th
 978 -- character with a checkdigit computed according to the usual algorithm for library barcodes
 979 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
 980 -- input string does not meet those requirements, it is returned unchanged.
 981 --
 982 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
 983     my $barcode = shift;
 984
 985     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
 986     $barcode = substr($barcode, 0, 13); # ignore 14th character
 987
 988     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
 989     my %nums = map { $valid_chars[$_] => $_ } (0..42);
 990
 991     my $total = 0;
 992     $total += $nums{$_} foreach split(//, $barcode);
 993     my $remainder = $total % 43;
 994     my $checkdigit = $valid_chars[$remainder];
 995     return $barcode . $checkdigit;
 996 $$ LANGUAGE PLPERLU STRICT STABLE;
 997
 998 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
 999   DECLARE
1000     phone TEXT := $1;
1001     areacode TEXT := $2;
1002     temp TEXT := '';
1003     output TEXT := '';
1004     n_digits INTEGER := 0;
1005   BEGIN
1006     temp := phone;
1007     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1008     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1009     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1010     IF n_digits = 7 AND areacode <> '' THEN
1011       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1012       output := (areacode || '-' || temp);
1013     ELSE
1014       output := temp;
1015     END IF;
1016     RETURN output;
1017   END;
1018
1019 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1020
1021 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1022   my ($marcxml, $pos, $value) = @_;
1023
1024   use MARC::Record;
1025   use MARC::File::XML;
1026
1027   my $xml = $marcxml;
1028   eval {
1029     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1030     my $leader = $marc->leader();
1031     substr($leader, $pos, 1) = $value;
1032     $marc->leader($leader);
1033     $xml = $marc->as_xml_record;
1034     $xml =~ s/^<\?.+?\?>$//mo;
1035     $xml =~ s/\n//sgo;
1036     $xml =~ s/>\s+</></sgo;
1037   };
1038   return $xml;
1039 $$ LANGUAGE PLPERLU STABLE;
1040
1041 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1042   my ($marcxml, $pos, $value) = @_;
1043
1044   use MARC::Record;
1045   use MARC::File::XML;
1046
1047   my $xml = $marcxml;
1048   eval {
1049     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1050     my $f008 = $marc->field('008');
1051
1052     if ($f008) {
1053        my $field = $f008->data();
1054        substr($field, $pos, 1) = $value;
1055        $f008->update($field);
1056        $xml = $marc->as_xml_record;
1057        $xml =~ s/^<\?.+?\?>$//mo;
1058        $xml =~ s/\n//sgo;
1059        $xml =~ s/>\s+</></sgo;
1060     }
1061   };
1062   return $xml;
1063 $$ LANGUAGE PLPERLU STABLE;
1064
1065
1066 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1067   DECLARE
1068     profile ALIAS FOR $1;
1069   BEGIN
1070     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1071   END;
1072 $$ LANGUAGE PLPGSQL STRICT STABLE;
1073
1074
1075 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1076   BEGIN
1077     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1078   END;
1079 $$ LANGUAGE PLPGSQL STRICT STABLE;
1080
1081
1082 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1083
1084   my ($marcxml, $tags) = @_;
1085
1086   use MARC::Record;
1087   use MARC::File::XML;
1088
1089   my $xml = $marcxml;
1090
1091   eval {
1092     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1093     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1094
1095     my @incumbents = ();
1096
1097     foreach my $field ( $marc->fields() ) {
1098       push @incumbents, $field->as_formatted();
1099     }
1100
1101     foreach $field ( $to_insert->fields() ) {
1102       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1103         $marc->insert_fields_ordered( ($field) );
1104       }
1105     }
1106
1107     $xml = $marc->as_xml_record;
1108     $xml =~ s/^<\?.+?\?>$//mo;
1109     $xml =~ s/\n//sgo;
1110     $xml =~ s/>\s+</></sgo;
1111   };
1112
1113   return $xml;
1114
1115 $$ LANGUAGE PLPERLU STABLE;
1116
1117 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1118
1119 -- Usage:
1120 --
1121 --   First make sure the circ matrix is loaded and the circulations
1122 --   have been staged to the extent possible (but at the very least
1123 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1124 --   circ modifiers must also be in place.
1125 --
1126 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1127 --
1128
1129 DECLARE
1130   circ_lib             INT;
1131   target_copy          INT;
1132   usr                  INT;
1133   is_renewal           BOOLEAN;
1134   this_duration_rule   INT;
1135   this_fine_rule       INT;
1136   this_max_fine_rule   INT;
1137   rcd                  config.rule_circ_duration%ROWTYPE;
1138   rrf                  config.rule_recurring_fine%ROWTYPE;
1139   rmf                  config.rule_max_fine%ROWTYPE;
1140   circ                 INT;
1141   n                    INT := 0;
1142   n_circs              INT;
1143
1144 BEGIN
1145
1146   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1147
1148   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1149
1150     -- Fetch the correct rules for this circulation
1151     EXECUTE ('
1152       SELECT
1153         circ_lib,
1154         target_copy,
1155         usr,
1156         CASE
1157           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1158           ELSE FALSE
1159         END
1160       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1161       INTO circ_lib, target_copy, usr, is_renewal ;
1162     SELECT
1163       INTO this_duration_rule,
1164            this_fine_rule,
1165            this_max_fine_rule
1166       duration_rule,
1167       recurring_fine_rule,
1168       max_fine_rule
1169       FROM action.item_user_circ_test(
1170         circ_lib,
1171         target_copy,
1172         usr,
1173         is_renewal
1174         );
1175     SELECT INTO rcd * FROM config.rule_circ_duration
1176       WHERE id = this_duration_rule;
1177     SELECT INTO rrf * FROM config.rule_recurring_fine
1178       WHERE id = this_fine_rule;
1179     SELECT INTO rmf * FROM config.rule_max_fine
1180       WHERE id = this_max_fine_rule;
1181
1182     -- Apply the rules to this circulation
1183     EXECUTE ('UPDATE ' || tablename || ' c
1184     SET
1185       duration_rule = rcd.name,
1186       recurring_fine_rule = rrf.name,
1187       max_fine_rule = rmf.name,
1188       duration = rcd.normal,
1189       recurring_fine = rrf.normal,
1190       max_fine =
1191         CASE rmf.is_percent
1192           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1193           ELSE rmf.amount
1194         END,
1195       renewal_remaining = rcd.max_renewals
1196     FROM
1197       config.rule_circ_duration rcd,
1198       config.rule_recurring_fine rrf,
1199       config.rule_max_fine rmf,
1200                         asset.copy ac
1201     WHERE
1202       rcd.id = ' || this_duration_rule || ' AND
1203       rrf.id = ' || this_fine_rule || ' AND
1204       rmf.id = ' || this_max_fine_rule || ' AND
1205                         ac.id = c.target_copy AND
1206       c.id = ' || circ || ';');
1207
1208     -- Keep track of where we are in the process
1209     n := n + 1;
1210     IF (n % 100 = 0) THEN
1211       RAISE INFO '%', n || ' of ' || n_circs
1212         || ' (' || (100*n/n_circs) || '%) circs updated.';
1213     END IF;
1214
1215   END LOOP;
1216
1217   RETURN;
1218 END;
1219
1220 $$ LANGUAGE plpgsql;
1221
1222 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1223
1224 -- Usage:
1225 --
1226 --   First make sure the circ matrix is loaded and the circulations
1227 --   have been staged to the extent possible (but at the very least
1228 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1229 --   circ modifiers must also be in place.
1230 --
1231 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1232 --
1233
1234 DECLARE
1235   circ_lib             INT;
1236   target_copy          INT;
1237   usr                  INT;
1238   is_renewal           BOOLEAN;
1239   this_duration_rule   INT;
1240   this_fine_rule       INT;
1241   this_max_fine_rule   INT;
1242   rcd                  config.rule_circ_duration%ROWTYPE;
1243   rrf                  config.rule_recurring_fine%ROWTYPE;
1244   rmf                  config.rule_max_fine%ROWTYPE;
1245   circ                 INT;
1246   n                    INT := 0;
1247   n_circs              INT;
1248
1249 BEGIN
1250
1251   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1252
1253   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1254
1255     -- Fetch the correct rules for this circulation
1256     EXECUTE ('
1257       SELECT
1258         circ_lib,
1259         target_copy,
1260         usr,
1261         CASE
1262           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1263           ELSE FALSE
1264         END
1265       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1266       INTO circ_lib, target_copy, usr, is_renewal ;
1267     SELECT
1268       INTO this_duration_rule,
1269            this_fine_rule,
1270            this_max_fine_rule
1271       duration_rule,
1272       recuring_fine_rule,
1273       max_fine_rule
1274       FROM action.find_circ_matrix_matchpoint(
1275         circ_lib,
1276         target_copy,
1277         usr,
1278         is_renewal
1279         );
1280     SELECT INTO rcd * FROM config.rule_circ_duration
1281       WHERE id = this_duration_rule;
1282     SELECT INTO rrf * FROM config.rule_recurring_fine
1283       WHERE id = this_fine_rule;
1284     SELECT INTO rmf * FROM config.rule_max_fine
1285       WHERE id = this_max_fine_rule;
1286
1287     -- Apply the rules to this circulation
1288     EXECUTE ('UPDATE ' || tablename || ' c
1289     SET
1290       duration_rule = rcd.name,
1291       recuring_fine_rule = rrf.name,
1292       max_fine_rule = rmf.name,
1293       duration = rcd.normal,
1294       recuring_fine = rrf.normal,
1295       max_fine =
1296         CASE rmf.is_percent
1297           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1298           ELSE rmf.amount
1299         END,
1300       renewal_remaining = rcd.max_renewals
1301     FROM
1302       config.rule_circ_duration rcd,
1303       config.rule_recuring_fine rrf,
1304       config.rule_max_fine rmf,
1305                         asset.copy ac
1306     WHERE
1307       rcd.id = ' || this_duration_rule || ' AND
1308       rrf.id = ' || this_fine_rule || ' AND
1309       rmf.id = ' || this_max_fine_rule || ' AND
1310                         ac.id = c.target_copy AND
1311       c.id = ' || circ || ';');
1312
1313     -- Keep track of where we are in the process
1314     n := n + 1;
1315     IF (n % 100 = 0) THEN
1316       RAISE INFO '%', n || ' of ' || n_circs
1317         || ' (' || (100*n/n_circs) || '%) circs updated.';
1318     END IF;
1319
1320   END LOOP;
1321
1322   RETURN;
1323 END;
1324
1325 $$ LANGUAGE plpgsql;
1326
1327 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1328
1329 -- Usage:
1330 --
1331 --   First make sure the circ matrix is loaded and the circulations
1332 --   have been staged to the extent possible (but at the very least
1333 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1334 --   circ modifiers must also be in place.
1335 --
1336 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1337 --
1338
1339 DECLARE
1340   circ_lib             INT;
1341   target_copy          INT;
1342   usr                  INT;
1343   is_renewal           BOOLEAN;
1344   this_duration_rule   INT;
1345   this_fine_rule       INT;
1346   this_max_fine_rule   INT;
1347   rcd                  config.rule_circ_duration%ROWTYPE;
1348   rrf                  config.rule_recurring_fine%ROWTYPE;
1349   rmf                  config.rule_max_fine%ROWTYPE;
1350   circ                 INT;
1351   n                    INT := 0;
1352   n_circs              INT;
1353
1354 BEGIN
1355
1356   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1357
1358   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1359
1360     -- Fetch the correct rules for this circulation
1361     EXECUTE ('
1362       SELECT
1363         circ_lib,
1364         target_copy,
1365         usr,
1366         CASE
1367           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1368           ELSE FALSE
1369         END
1370       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1371       INTO circ_lib, target_copy, usr, is_renewal ;
1372     SELECT
1373       INTO this_duration_rule,
1374            this_fine_rule,
1375            this_max_fine_rule
1376       (matchpoint).duration_rule,
1377       (matchpoint).recurring_fine_rule,
1378       (matchpoint).max_fine_rule
1379       FROM action.find_circ_matrix_matchpoint(
1380         circ_lib,
1381         target_copy,
1382         usr,
1383         is_renewal
1384         );
1385     SELECT INTO rcd * FROM config.rule_circ_duration
1386       WHERE id = this_duration_rule;
1387     SELECT INTO rrf * FROM config.rule_recurring_fine
1388       WHERE id = this_fine_rule;
1389     SELECT INTO rmf * FROM config.rule_max_fine
1390       WHERE id = this_max_fine_rule;
1391
1392     -- Apply the rules to this circulation
1393     EXECUTE ('UPDATE ' || tablename || ' c
1394     SET
1395       duration_rule = rcd.name,
1396       recurring_fine_rule = rrf.name,
1397       max_fine_rule = rmf.name,
1398       duration = rcd.normal,
1399       recurring_fine = rrf.normal,
1400       max_fine =
1401         CASE rmf.is_percent
1402           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1403           ELSE rmf.amount
1404         END,
1405       renewal_remaining = rcd.max_renewals,
1406       grace_period = rrf.grace_period
1407     FROM
1408       config.rule_circ_duration rcd,
1409       config.rule_recurring_fine rrf,
1410       config.rule_max_fine rmf,
1411                         asset.copy ac
1412     WHERE
1413       rcd.id = ' || this_duration_rule || ' AND
1414       rrf.id = ' || this_fine_rule || ' AND
1415       rmf.id = ' || this_max_fine_rule || ' AND
1416                         ac.id = c.target_copy AND
1417       c.id = ' || circ || ';');
1418
1419     -- Keep track of where we are in the process
1420     n := n + 1;
1421     IF (n % 100 = 0) THEN
1422       RAISE INFO '%', n || ' of ' || n_circs
1423         || ' (' || (100*n/n_circs) || '%) circs updated.';
1424     END IF;
1425
1426   END LOOP;
1427
1428   RETURN;
1429 END;
1430
1431 $$ LANGUAGE plpgsql;
1432
1433 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1434
1435 -- Usage:
1436 --
1437 --   First make sure the circ matrix is loaded and the circulations
1438 --   have been staged to the extent possible (but at the very least
1439 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1440 --   circ modifiers must also be in place.
1441 --
1442 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1443 --
1444
1445 DECLARE
1446   circ_lib             INT;
1447   target_copy          INT;
1448   usr                  INT;
1449   is_renewal           BOOLEAN;
1450   this_duration_rule   INT;
1451   this_fine_rule       INT;
1452   this_max_fine_rule   INT;
1453   rcd                  config.rule_circ_duration%ROWTYPE;
1454   rrf                  config.rule_recurring_fine%ROWTYPE;
1455   rmf                  config.rule_max_fine%ROWTYPE;
1456   n                    INT := 0;
1457   n_circs              INT := 1;
1458
1459 BEGIN
1460
1461   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1462
1463   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1464
1465     -- Fetch the correct rules for this circulation
1466     EXECUTE ('
1467       SELECT
1468         circ_lib,
1469         target_copy,
1470         usr,
1471         CASE
1472           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1473           ELSE FALSE
1474         END
1475       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1476       INTO circ_lib, target_copy, usr, is_renewal ;
1477     SELECT
1478       INTO this_duration_rule,
1479            this_fine_rule,
1480            this_max_fine_rule
1481       (matchpoint).duration_rule,
1482       (matchpoint).recurring_fine_rule,
1483       (matchpoint).max_fine_rule
1484       FROM action.find_circ_matrix_matchpoint(
1485         circ_lib,
1486         target_copy,
1487         usr,
1488         is_renewal
1489         );
1490     SELECT INTO rcd * FROM config.rule_circ_duration
1491       WHERE id = this_duration_rule;
1492     SELECT INTO rrf * FROM config.rule_recurring_fine
1493       WHERE id = this_fine_rule;
1494     SELECT INTO rmf * FROM config.rule_max_fine
1495       WHERE id = this_max_fine_rule;
1496
1497     -- Apply the rules to this circulation
1498     EXECUTE ('UPDATE ' || tablename || ' c
1499     SET
1500       duration_rule = rcd.name,
1501       recurring_fine_rule = rrf.name,
1502       max_fine_rule = rmf.name,
1503       duration = rcd.normal,
1504       recurring_fine = rrf.normal,
1505       max_fine =
1506         CASE rmf.is_percent
1507           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1508           ELSE rmf.amount
1509         END,
1510       renewal_remaining = rcd.max_renewals,
1511       grace_period = rrf.grace_period
1512     FROM
1513       config.rule_circ_duration rcd,
1514       config.rule_recurring_fine rrf,
1515       config.rule_max_fine rmf,
1516                         asset.copy ac
1517     WHERE
1518       rcd.id = ' || this_duration_rule || ' AND
1519       rrf.id = ' || this_fine_rule || ' AND
1520       rmf.id = ' || this_max_fine_rule || ' AND
1521                         ac.id = c.target_copy AND
1522       c.id = ' || circ || ';');
1523
1524     -- Keep track of where we are in the process
1525     n := n + 1;
1526     IF (n % 100 = 0) THEN
1527       RAISE INFO '%', n || ' of ' || n_circs
1528         || ' (' || (100*n/n_circs) || '%) circs updated.';
1529     END IF;
1530
1531   --END LOOP;
1532
1533   RETURN;
1534 END;
1535
1536 $$ LANGUAGE plpgsql;
1537
1538
1539
1540
1541 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1542
1543 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1544 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1545
1546 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1547 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1548
1549 DECLARE
1550         c                    TEXT := schemaname || '.asset_copy_legacy';
1551         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1552         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1553         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1554         stat_cat                                                 INT;
1555   stat_cat_entry       INT;
1556
1557 BEGIN
1558
1559   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1560
1561                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1562
1563                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1564                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1565                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1566
1567   END LOOP;
1568
1569   RETURN;
1570 END;
1571
1572 $$ LANGUAGE plpgsql;
1573
1574 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1575
1576 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1577 --        This will assign standing penalties as needed.
1578
1579 DECLARE
1580   org_unit  INT;
1581   usr       INT;
1582
1583 BEGIN
1584
1585   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1586
1587     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1588
1589       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1590
1591     END LOOP;
1592
1593   END LOOP;
1594
1595   RETURN;
1596
1597 END;
1598
1599 $$ LANGUAGE plpgsql;
1600
1601
1602 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1603
1604 BEGIN
1605   INSERT INTO metabib.metarecord (fingerprint, master_record)
1606     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1607       FROM  biblio.record_entry b
1608       WHERE NOT b.deleted
1609         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)
1610         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1611       ORDER BY b.fingerprint, b.quality DESC;
1612   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1613     SELECT  m.id, r.id
1614       FROM  biblio.record_entry r
1615       JOIN  metabib.metarecord m USING (fingerprint)
1616      WHERE  NOT r.deleted;
1617 END;
1618
1619 $$ LANGUAGE plpgsql;
1620
1621
1622 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1623
1624 BEGIN
1625   INSERT INTO metabib.metarecord (fingerprint, master_record)
1626     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1627       FROM  biblio.record_entry b
1628       WHERE NOT b.deleted
1629         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)
1630         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1631       ORDER BY b.fingerprint, b.quality DESC;
1632   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1633     SELECT  m.id, r.id
1634       FROM  biblio.record_entry r
1635         JOIN metabib.metarecord m USING (fingerprint)
1636       WHERE NOT r.deleted
1637         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);
1638 END;
1639
1640 $$ LANGUAGE plpgsql;
1641
1642
1643 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1644
1645 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1646 --        Then SELECT migration_tools.create_cards('m_foo');
1647
1648 DECLARE
1649         u                    TEXT := schemaname || '.actor_usr_legacy';
1650         c                    TEXT := schemaname || '.actor_card';
1651
1652 BEGIN
1653
1654         EXECUTE ('DELETE FROM ' || c || ';');
1655         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1656         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1657
1658   RETURN;
1659
1660 END;
1661
1662 $$ LANGUAGE plpgsql;
1663
1664
1665 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1666
1667   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1668
1669   my ($marcxml, $shortname) = @_;
1670
1671   use MARC::Record;
1672   use MARC::File::XML;
1673
1674   my $xml = $marcxml;
1675
1676   eval {
1677     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1678
1679     foreach my $field ( $marc->field('856') ) {
1680       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1681            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1682         $field->add_subfields( '9' => $shortname );
1683                                 $field->update( ind2 => '0');
1684       }
1685     }
1686
1687     $xml = $marc->as_xml_record;
1688     $xml =~ s/^<\?.+?\?>$//mo;
1689     $xml =~ s/\n//sgo;
1690     $xml =~ s/>\s+</></sgo;
1691   };
1692
1693   return $xml;
1694
1695 $$ LANGUAGE PLPERLU STABLE;
1696
1697 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1698
1699   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1700
1701   my ($marcxml, $shortname) = @_;
1702
1703   use MARC::Record;
1704   use MARC::File::XML;
1705
1706   my $xml = $marcxml;
1707
1708   eval {
1709     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1710
1711     foreach my $field ( $marc->field('856') ) {
1712       if ( ! $field->as_string('9') ) {
1713         $field->add_subfields( '9' => $shortname );
1714       }
1715     }
1716
1717     $xml = $marc->as_xml_record;
1718     $xml =~ s/^<\?.+?\?>$//mo;
1719     $xml =~ s/\n//sgo;
1720     $xml =~ s/>\s+</></sgo;
1721   };
1722
1723   return $xml;
1724
1725 $$ LANGUAGE PLPERLU STABLE;
1726
1727
1728 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1729
1730 DECLARE
1731   old_volume   BIGINT;
1732   new_volume   BIGINT;
1733   bib          BIGINT;
1734   owner        INTEGER;
1735   old_label    TEXT;
1736   remainder    BIGINT;
1737
1738 BEGIN
1739
1740   -- Bail out if asked to change the label to ##URI##
1741   IF new_label = '##URI##' THEN
1742     RETURN;
1743   END IF;
1744
1745   -- Gather information
1746   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1747   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1748   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1749
1750   -- Bail out if the label already is ##URI##
1751   IF old_label = '##URI##' THEN
1752     RETURN;
1753   END IF;
1754
1755   -- Bail out if the call number label is already correct
1756   IF new_volume = old_volume THEN
1757     RETURN;
1758   END IF;
1759
1760   -- Check whether we already have a destination volume available
1761   SELECT id INTO new_volume FROM asset.call_number
1762     WHERE
1763       record = bib AND
1764       owning_lib = owner AND
1765       label = new_label AND
1766       NOT deleted;
1767
1768   -- Create destination volume if needed
1769   IF NOT FOUND THEN
1770     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1771       VALUES (1, 1, bib, owner, new_label, cn_class);
1772     SELECT id INTO new_volume FROM asset.call_number
1773       WHERE
1774         record = bib AND
1775         owning_lib = owner AND
1776         label = new_label AND
1777         NOT deleted;
1778   END IF;
1779
1780   -- Move copy to destination
1781   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1782
1783   -- Delete source volume if it is now empty
1784   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1785   IF NOT FOUND THEN
1786     DELETE FROM asset.call_number WHERE id = old_volume;
1787   END IF;
1788
1789 END;
1790
1791 $$ LANGUAGE plpgsql;
1792
1793 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1794
1795         my $input = $_[0];
1796         my %zipdata;
1797
1798         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1799
1800         while (<FH>) {
1801                 chomp;
1802                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1803                 $zipdata{$zip} = [$city, $state, $county];
1804         }
1805
1806         if (defined $zipdata{$input}) {
1807                 my ($city, $state, $county) = @{$zipdata{$input}};
1808                 return [$city, $state, $county];
1809         } elsif (defined $zipdata{substr $input, 0, 5}) {
1810                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1811                 return [$city, $state, $county];
1812         } else {
1813                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1814         }
1815
1816 $$ LANGUAGE PLPERLU STABLE;
1817
1818 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1819
1820 DECLARE
1821   ou  INT;
1822         org_unit_depth INT;
1823         ou_parent INT;
1824         parent_depth INT;
1825   errors_found BOOLEAN;
1826         ou_shortname TEXT;
1827         parent_shortname TEXT;
1828         ou_type_name TEXT;
1829         parent_type TEXT;
1830         type_id INT;
1831         type_depth INT;
1832         type_parent INT;
1833         type_parent_depth INT;
1834         proper_parent TEXT;
1835
1836 BEGIN
1837
1838         errors_found := FALSE;
1839
1840 -- Checking actor.org_unit_type
1841
1842         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1843
1844                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1845                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1846
1847                 IF type_parent IS NOT NULL THEN
1848
1849                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1850
1851                         IF type_depth - type_parent_depth <> 1 THEN
1852                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1853                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1854                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1855                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1856                                 errors_found := TRUE;
1857
1858                         END IF;
1859
1860                 END IF;
1861
1862         END LOOP;
1863
1864 -- Checking actor.org_unit
1865
1866   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1867
1868                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1869                 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;
1870                 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;
1871                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1872                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1873                 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;
1874                 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;
1875
1876                 IF ou_parent IS NOT NULL THEN
1877
1878                         IF      (org_unit_depth - parent_depth <> 1) OR (
1879                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1880                         ) THEN
1881                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1882                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1883                                 errors_found := TRUE;
1884                         END IF;
1885
1886                 END IF;
1887
1888   END LOOP;
1889
1890         IF NOT errors_found THEN
1891                 RAISE INFO 'No errors found.';
1892         END IF;
1893
1894   RETURN;
1895
1896 END;
1897
1898 $$ LANGUAGE plpgsql;
1899
1900
1901 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1902
1903 BEGIN
1904
1905         DELETE FROM asset.opac_visible_copies;
1906
1907         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1908                 SELECT DISTINCT
1909                         cp.id, cp.circ_lib, cn.record
1910                 FROM
1911                         asset.copy cp
1912                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1913                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1914                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1915                         JOIN config.copy_status cs ON (cp.status = cs.id)
1916                         JOIN biblio.record_entry b ON (cn.record = b.id)
1917                 WHERE
1918                         NOT cp.deleted AND
1919                         NOT cn.deleted AND
1920                         NOT b.deleted AND
1921                         cs.opac_visible AND
1922                         cl.opac_visible AND
1923                         cp.opac_visible AND
1924                         a.opac_visible AND
1925                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1926
1927 END;
1928
1929 $$ LANGUAGE plpgsql;
1930
1931
1932 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1933
1934 DECLARE
1935   old_volume     BIGINT;
1936   new_volume     BIGINT;
1937   bib            BIGINT;
1938   old_owning_lib INTEGER;
1939         old_label      TEXT;
1940   remainder      BIGINT;
1941
1942 BEGIN
1943
1944   -- Gather information
1945   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1946   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1947   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1948
1949         -- Bail out if the new_owning_lib is not the ID of an org_unit
1950         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1951                 RAISE WARNING
1952                         '% is not a valid actor.org_unit ID; no change made.',
1953                                 new_owning_lib;
1954                 RETURN;
1955         END IF;
1956
1957   -- Bail out discreetly if the owning_lib is already correct
1958   IF new_owning_lib = old_owning_lib THEN
1959     RETURN;
1960   END IF;
1961
1962   -- Check whether we already have a destination volume available
1963   SELECT id INTO new_volume FROM asset.call_number
1964     WHERE
1965       record = bib AND
1966       owning_lib = new_owning_lib AND
1967       label = old_label AND
1968       NOT deleted;
1969
1970   -- Create destination volume if needed
1971   IF NOT FOUND THEN
1972     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1973       VALUES (1, 1, bib, new_owning_lib, old_label);
1974     SELECT id INTO new_volume FROM asset.call_number
1975       WHERE
1976         record = bib AND
1977         owning_lib = new_owning_lib AND
1978         label = old_label AND
1979         NOT deleted;
1980   END IF;
1981
1982   -- Move copy to destination
1983   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1984
1985   -- Delete source volume if it is now empty
1986   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1987   IF NOT FOUND THEN
1988     DELETE FROM asset.call_number WHERE id = old_volume;
1989   END IF;
1990
1991 END;
1992
1993 $$ LANGUAGE plpgsql;
1994
1995
1996 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1997
1998 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1999
2000 DECLARE
2001         new_owning_lib  INTEGER;
2002
2003 BEGIN
2004
2005         -- Parse the new_owner as an org unit ID or shortname
2006         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2007                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2008                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2009         ELSIF new_owner ~ E'^[0-9]+$' THEN
2010                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2011                         RAISE INFO
2012                                 '%',
2013                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2014                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2015                         new_owning_lib := new_owner::INTEGER;
2016                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2017                 END IF;
2018         ELSE
2019                 RAISE WARNING
2020                         '% is not a valid actor.org_unit shortname or ID; no change made.',
2021                         new_owning_lib;
2022                 RETURN;
2023         END IF;
2024
2025 END;
2026
2027 $$ LANGUAGE plpgsql;
2028
2029 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2030
2031 use MARC::Record;
2032 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2033 use MARC::Charset;
2034
2035 MARC::Charset->assume_unicode(1);
2036
2037 my $xml = shift;
2038
2039 eval {
2040     my $r = MARC::Record->new_from_xml( $xml );
2041     my $output_xml = $r->as_xml_record();
2042 };
2043 if ($@) {
2044     return 0;
2045 } else {
2046     return 1;
2047 }
2048
2049 $func$ LANGUAGE PLPERLU;
2050 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2051
2052 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2053 BEGIN
2054    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2055            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2056            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2057    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2058            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2059            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2060    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2061            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2062            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2063    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2064            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2065            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2066    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2067            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2068            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2069    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2070            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2071            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2072    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2073            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2074            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2075    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2076    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2077    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2078    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2079    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2080 END;
2081 $FUNC$ LANGUAGE PLPGSQL;
2082
2083 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2084 BEGIN
2085    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2086    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2087    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2088    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2089    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2090    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2091    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2092
2093    -- import any new circ rules
2094    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2095    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2096    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2097    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2098
2099    -- and permission groups
2100    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2101
2102 END;
2103 $FUNC$ LANGUAGE PLPGSQL;
2104
2105
2106 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$
2107 DECLARE
2108     name TEXT;
2109     loopq TEXT;
2110     existsq TEXT;
2111     ct INTEGER;
2112     cols TEXT[];
2113     copyst TEXT;
2114 BEGIN
2115     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2116     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2117     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2118     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2119     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2120     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2121     FOR name IN EXECUTE loopq LOOP
2122        EXECUTE existsq INTO ct USING name;
2123        IF ct = 0 THEN
2124            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2125            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2126                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2127            EXECUTE copyst USING name;
2128        END IF;
2129     END LOOP;
2130 END;
2131 $FUNC$ LANGUAGE PLPGSQL;
2132
2133 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2134
2135 use strict;
2136 use warnings;
2137
2138 use MARC::Record;
2139 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2140 use MARC::Charset;
2141
2142 MARC::Charset->assume_unicode(1);
2143
2144 my $target_xml = shift;
2145 my $source_xml = shift;
2146 my $tags = shift;
2147
2148 my $target;
2149 my $source;
2150
2151 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2152 if ($@) {
2153     return;
2154 }
2155 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2156 if ($@) {
2157     return;
2158 }
2159
2160 my $source_id = $source->subfield('901', 'c');
2161 $source_id = $source->subfield('903', 'a') unless $source_id;
2162 my $target_id = $target->subfield('901', 'c');
2163 $target_id = $target->subfield('903', 'a') unless $target_id;
2164
2165 my %existing_fields;
2166 foreach my $tag (@$tags) {
2167     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2168     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2169     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2170     if (@to_add) {
2171         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2172     }
2173 }
2174
2175 my $xml = $target->as_xml_record;
2176 $xml =~ s/^<\?.+?\?>$//mo;
2177 $xml =~ s/\n//sgo;
2178 $xml =~ s/>\s+</></sgo;
2179
2180 return $xml;
2181
2182 $func$ LANGUAGE PLPERLU;
2183 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.';
2184
2185 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2186
2187 use strict;
2188 use warnings;
2189
2190 use MARC::Record;
2191 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2192 use Text::CSV;
2193
2194 my $in_tags = shift;
2195 my $in_values = shift;
2196
2197 # hack-and-slash parsing of array-passed-as-string;
2198 # this can go away once everybody is running Postgres 9.1+
2199 my $csv = Text::CSV->new({binary => 1});
2200 $in_tags =~ s/^{//;
2201 $in_tags =~ s/}$//;
2202 my $status = $csv->parse($in_tags);
2203 my $tags = [ $csv->fields() ];
2204 $in_values =~ s/^{//;
2205 $in_values =~ s/}$//;
2206 $status = $csv->parse($in_values);
2207 my $values = [ $csv->fields() ];
2208
2209 my $marc = MARC::Record->new();
2210
2211 $marc->leader('00000nam a22000007  4500');
2212 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2213
2214 foreach my $i (0..$#$tags) {
2215     my ($tag, $sf);
2216     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2217         $tag = $1;
2218         $sf = $2;
2219         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2220     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2221         $tag = $1;
2222         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2223     }
2224 }
2225
2226 my $xml = $marc->as_xml_record;
2227 $xml =~ s/^<\?.+?\?>$//mo;
2228 $xml =~ s/\n//sgo;
2229 $xml =~ s/>\s+</></sgo;
2230
2231 return $xml;
2232
2233 $func$ LANGUAGE PLPERLU;
2234 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2235 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2236 The second argument is an array of text containing the values to plug into each field.
2237 If the value for a given field is NULL or the empty string, it is not inserted.
2238 $$;
2239
2240 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2241
2242 my ($marcxml, $tag, $pos, $value) = @_;
2243
2244 use MARC::Record;
2245 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2246 use MARC::Charset;
2247 use strict;
2248
2249 MARC::Charset->assume_unicode(1);
2250
2251 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2252 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2253 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2254 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2255
2256 my $xml = $marcxml;
2257 eval {
2258     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2259
2260     foreach my $field ($marc->field($tag)) {
2261         $field->update("ind$pos" => $value);
2262     }
2263     $xml = $marc->as_xml_record;
2264     $xml =~ s/^<\?.+?\?>$//mo;
2265     $xml =~ s/\n//sgo;
2266     $xml =~ s/>\s+</></sgo;
2267 };
2268 return $xml;
2269
2270 $func$ LANGUAGE PLPERLU;
2271
2272 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2273 The first argument is a MARCXML string.
2274 The second argument is a MARC tag.
2275 The third argument is the indicator position, either 1 or 2.
2276 The fourth argument is the character to set the indicator value to.
2277 All occurences of the specified field will be changed.
2278 The function returns the revised MARCXML string.$$;
2279
2280 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2281     username TEXT,
2282     password TEXT,
2283     org TEXT,
2284     perm_group TEXT,
2285     first_name TEXT DEFAULT '',
2286     last_name TEXT DEFAULT ''
2287 ) RETURNS VOID AS $func$
2288 BEGIN
2289     RAISE NOTICE '%', org ;
2290     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2291     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2292     FROM   actor.org_unit aou, permission.grp_tree pgt
2293     WHERE  aou.shortname = org
2294     AND    pgt.name = perm_group;
2295 END
2296 $func$
2297 LANGUAGE PLPGSQL;
2298
2299 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2300 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2301     DECLARE
2302         target_event_def ALIAS FOR $1;
2303         orgs ALIAS FOR $2;
2304     BEGIN
2305         DROP TABLE IF EXISTS new_atevdefs;
2306         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2307         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2308             INSERT INTO action_trigger.event_definition (
2309                 active
2310                 ,owner
2311                 ,name
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             ) SELECT
2327                 'f'
2328                 ,orgs[i]
2329                 ,name || ' (clone of '||target_event_def||')'
2330                 ,hook
2331                 ,validator
2332                 ,reactor
2333                 ,cleanup_success
2334                 ,cleanup_failure
2335                 ,delay
2336                 ,max_delay
2337                 ,usr_field
2338                 ,opt_in_setting
2339                 ,delay_field
2340                 ,group_field
2341                 ,template
2342                 ,granularity
2343                 ,repeat_delay
2344             FROM
2345                 action_trigger.event_definition
2346             WHERE
2347                 id = target_event_def
2348             ;
2349             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2350             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2351             INSERT INTO action_trigger.environment (
2352                 event_def
2353                 ,path
2354                 ,collector
2355                 ,label
2356             ) SELECT
2357                 currval('action_trigger.event_definition_id_seq')
2358                 ,path
2359                 ,collector
2360                 ,label
2361             FROM
2362                 action_trigger.environment
2363             WHERE
2364                 event_def = target_event_def
2365             ;
2366             INSERT INTO action_trigger.event_params (
2367                 event_def
2368                 ,param
2369                 ,value
2370             ) SELECT
2371                 currval('action_trigger.event_definition_id_seq')
2372                 ,param
2373                 ,value
2374             FROM
2375                 action_trigger.event_params
2376             WHERE
2377                 event_def = target_event_def
2378             ;
2379         END LOOP;
2380         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);
2381     END;
2382 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2383
2384 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2385 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2386     DECLARE
2387         target_event_def ALIAS FOR $1;
2388         orgs ALIAS FOR $2;
2389         new_interval ALIAS FOR $3;
2390     BEGIN
2391         DROP TABLE IF EXISTS new_atevdefs;
2392         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2393         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2394             INSERT INTO action_trigger.event_definition (
2395                 active
2396                 ,owner
2397                 ,name
2398                 ,hook
2399                 ,validator
2400                 ,reactor
2401                 ,cleanup_success
2402                 ,cleanup_failure
2403                 ,delay
2404                 ,max_delay
2405                 ,usr_field
2406                 ,opt_in_setting
2407                 ,delay_field
2408                 ,group_field
2409                 ,template
2410                 ,granularity
2411                 ,repeat_delay
2412             ) SELECT
2413                 'f'
2414                 ,orgs[i]
2415                 ,name || ' (clone of '||target_event_def||')'
2416                 ,hook
2417                 ,validator
2418                 ,reactor
2419                 ,cleanup_success
2420                 ,cleanup_failure
2421                 ,new_interval
2422                 ,max_delay
2423                 ,usr_field
2424                 ,opt_in_setting
2425                 ,delay_field
2426                 ,group_field
2427                 ,template
2428                 ,granularity
2429                 ,repeat_delay
2430             FROM
2431                 action_trigger.event_definition
2432             WHERE
2433                 id = target_event_def
2434             ;
2435             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2436             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2437             INSERT INTO action_trigger.environment (
2438                 event_def
2439                 ,path
2440                 ,collector
2441                 ,label
2442             ) SELECT
2443                 currval('action_trigger.event_definition_id_seq')
2444                 ,path
2445                 ,collector
2446                 ,label
2447             FROM
2448                 action_trigger.environment
2449             WHERE
2450                 event_def = target_event_def
2451             ;
2452             INSERT INTO action_trigger.event_params (
2453                 event_def
2454                 ,param
2455                 ,value
2456             ) SELECT
2457                 currval('action_trigger.event_definition_id_seq')
2458                 ,param
2459                 ,value
2460             FROM
2461                 action_trigger.event_params
2462             WHERE
2463                 event_def = target_event_def
2464             ;
2465         END LOOP;
2466         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);
2467     END;
2468 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2469
2470 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2471     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2472
2473     use MARC::Record;
2474     use MARC::File::XML;
2475     use MARC::Field;
2476
2477     my $field;
2478     eval {
2479         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2480         $field = $marc->field($tag);
2481     };
2482     return $field->as_string($subfield,$delimiter);
2483 $$ LANGUAGE PLPERLU STABLE;
2484
2485 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2486     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2487
2488     use MARC::Record;
2489     use MARC::File::XML;
2490     use MARC::Field;
2491
2492     my @fields;
2493     eval {
2494         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2495         @fields = $marc->field($tag);
2496     };
2497     my @texts;
2498     foreach my $field (@fields) {
2499         push @texts, $field->as_string($subfield,$delimiter);
2500     }
2501     return \@texts;
2502 $$ LANGUAGE PLPERLU STABLE;
2503
2504 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2505     SELECT action.find_hold_matrix_matchpoint(
2506         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2507         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2508         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2509         (SELECT usr FROM action.hold_request WHERE id = $1),
2510         (SELECT requestor FROM action.hold_request WHERE id = $1)
2511     );
2512 $$ LANGUAGE SQL;
2513
2514 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2515     SELECT action.hold_request_permit_test(
2516         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2517         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2518         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2519         (SELECT usr FROM action.hold_request WHERE id = $1),
2520         (SELECT requestor FROM action.hold_request WHERE id = $1)
2521     );
2522 $$ LANGUAGE SQL;
2523
2524 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2525     SELECT action.find_circ_matrix_matchpoint(
2526         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2527         (SELECT target_copy FROM action.circulation WHERE id = $1),
2528         (SELECT usr FROM action.circulation WHERE id = $1),
2529         (SELECT COALESCE(
2530                 NULLIF(phone_renewal,false),
2531                 NULLIF(desk_renewal,false),
2532                 NULLIF(opac_renewal,false),
2533                 false
2534             ) FROM action.circulation WHERE id = $1
2535         )
2536     );
2537 $$ LANGUAGE SQL;
2538
2539 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2540     DECLARE
2541         test ALIAS FOR $1;
2542     BEGIN
2543         IF NOT test THEN
2544             RAISE EXCEPTION 'assertion';
2545         END IF;
2546     END;
2547 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2548
2549 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2550     DECLARE
2551         test ALIAS FOR $1;
2552         msg ALIAS FOR $2;
2553     BEGIN
2554         IF NOT test THEN
2555             RAISE EXCEPTION '%', msg;
2556         END IF;
2557     END;
2558 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2559
2560 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2561     DECLARE
2562         test ALIAS FOR $1;
2563         fail_msg ALIAS FOR $2;
2564         success_msg ALIAS FOR $3;
2565     BEGIN
2566         IF NOT test THEN
2567             RAISE EXCEPTION '%', fail_msg;
2568         END IF;
2569         RETURN success_msg;
2570     END;
2571 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2572