sql/base/base.sql

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