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