1 -- Copyright 2009-2012, Equinox Software, Inc.
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.
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.
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.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
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')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
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 ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
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 (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
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 (
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,
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 (
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'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
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 );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
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]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
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
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 production_tables TEXT[];
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]);
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
279 migration_schema ALIAS FOR $1;
280 production_table ALIAS FOR $2;
281 base_staging_table TEXT;
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 || ';' );
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
301 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
302 IF temp ilike '%MR.%' THEN
304 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
306 IF temp ilike '%MRS.%' THEN
308 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
310 IF temp ilike '%MS.%' THEN
312 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
314 IF temp ilike '%DR.%' THEN
316 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
318 IF temp ilike '%JR%' THEN
320 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
322 IF temp ilike '%JR,%' THEN
324 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
326 IF temp ilike '%SR%' THEN
328 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
330 IF temp ilike '%SR,%' THEN
332 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
334 IF temp ~ E'\\sII$' THEN
336 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
338 IF temp ~ E'\\sIII$' THEN
340 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
342 IF temp ~ E'\\sIV$' THEN
344 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
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 );
351 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
353 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
355 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
357 full_name TEXT := $1;
359 family_name TEXT := '';
360 first_given_name TEXT := '';
361 second_given_name TEXT := '';
366 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
367 IF temp ilike '%MR.%' THEN
369 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
371 IF temp ilike '%MRS.%' THEN
373 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
375 IF temp ilike '%MS.%' THEN
377 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
379 IF temp ilike '%DR.%' THEN
381 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
383 IF temp ilike '%JR.%' THEN
385 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
387 IF temp ilike '%JR,%' THEN
389 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
391 IF temp ilike '%SR.%' THEN
393 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
395 IF temp ilike '%SR,%' THEN
397 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
399 IF temp like '%III%' THEN
401 temp := REGEXP_REPLACE( temp, E'III', '' );
403 IF temp like '%II%' THEN
405 temp := REGEXP_REPLACE( temp, E'II', '' );
407 IF temp like '%IV%' THEN
409 temp := REGEXP_REPLACE( temp, E'IV', '' );
412 temp := REGEXP_REPLACE( temp, '\(\)', '');
413 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
414 family_name := REGEXP_REPLACE( family_name, ',', '' );
415 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
416 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
417 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
418 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
420 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
422 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
424 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
426 full_name TEXT := $1;
428 family_name TEXT := '';
429 first_given_name TEXT := '';
430 second_given_name TEXT := '';
434 temp := BTRIM(full_name);
435 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
436 --IF temp ~ '^\S{2,}\.' THEN
437 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
438 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
440 --IF temp ~ '\S{2,}\.$' THEN
441 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
442 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
444 IF temp ilike '%MR.%' THEN
446 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
448 IF temp ilike '%MRS.%' THEN
450 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
452 IF temp ilike '%MS.%' THEN
454 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
456 IF temp ilike '%DR.%' THEN
458 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
460 IF temp ilike '%JR.%' THEN
462 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
464 IF temp ilike '%JR,%' THEN
466 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
468 IF temp ilike '%SR.%' THEN
470 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
472 IF temp ilike '%SR,%' THEN
474 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
476 IF temp like '%III%' THEN
478 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
480 IF temp like '%II%' THEN
482 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
484 IF temp like '%IV%' THEN
486 temp := BTRIM(REGEXP_REPLACE( temp, E'IV', '' ));
490 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
491 temp := BTRIM(REPLACE( temp, family_name, '' ));
492 family_name := REPLACE( family_name, ',', '' );
494 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
495 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
497 first_given_name := temp;
498 second_given_name := '';
501 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
502 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
503 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
504 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
506 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
507 second_given_name := temp;
508 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
512 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
514 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
516 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
518 city_state_zip TEXT := $1;
523 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;
524 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
525 IF city_state_zip ~ ',' THEN
526 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
527 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
529 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
530 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
531 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
533 IF city_state_zip ~ E'^\\S+$' THEN
534 city := city_state_zip;
537 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
538 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
542 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
544 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
546 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
547 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
549 fullstring TEXT := $1;
559 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
560 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
563 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
565 IF fullstring ~ ',' THEN
566 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
567 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
569 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
570 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
571 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
573 IF fullstring ~ E'^\\S+$' THEN
574 scratch1 := fullstring;
577 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
578 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
583 IF scratch1 ~ '[\$]' THEN
584 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
585 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
587 IF scratch1 ~ '\s' THEN
588 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
589 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
596 IF scratch2 ~ '^\d' THEN
597 address1 := scratch2;
600 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
601 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
605 TRIM(BOTH ' ' FROM address1)
606 ,TRIM(BOTH ' ' FROM address2)
607 ,TRIM(BOTH ' ' FROM city)
608 ,TRIM(BOTH ' ' FROM state)
609 ,TRIM(BOTH ' ' FROM zip)
612 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
614 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
618 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
619 IF o::BIGINT < t THEN
626 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
628 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
630 migration_schema ALIAS FOR $1;
634 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
639 $$ LANGUAGE PLPGSQL STRICT STABLE;
641 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
643 migration_schema ALIAS FOR $1;
647 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
652 $$ LANGUAGE PLPGSQL STRICT STABLE;
654 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
656 migration_schema ALIAS FOR $1;
660 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
665 $$ LANGUAGE PLPGSQL STRICT STABLE;
667 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
669 migration_schema ALIAS FOR $1;
673 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
678 $$ LANGUAGE PLPGSQL STRICT STABLE;
680 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
682 migration_schema ALIAS FOR $1;
684 patron_table ALIAS FOR $2;
685 default_patron_profile ALIAS FOR $3;
688 sql_where1 TEXT := '';
689 sql_where2 TEXT := '';
690 sql_where3 TEXT := '';
693 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
695 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
697 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
698 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);
699 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);
700 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);
701 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,'') || ';';
702 --RAISE INFO 'sql = %', sql;
703 PERFORM migration_tools.exec( $1, sql );
705 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
707 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
709 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
712 $$ LANGUAGE PLPGSQL STRICT STABLE;
714 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
716 migration_schema ALIAS FOR $1;
718 item_table ALIAS FOR $2;
721 sql_where1 TEXT := '';
722 sql_where2 TEXT := '';
723 sql_where3 TEXT := '';
726 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
728 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
730 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 ';
731 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);
732 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);
733 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);
734 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,'') || ';';
735 --RAISE INFO 'sql = %', sql;
736 PERFORM migration_tools.exec( $1, sql );
739 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
741 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
744 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
746 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
748 migration_schema ALIAS FOR $1;
749 base_copy_location_map TEXT;
750 item_table ALIAS FOR $2;
753 sql_where1 TEXT := '';
754 sql_where2 TEXT := '';
755 sql_where3 TEXT := '';
758 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
760 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
762 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
763 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);
764 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);
765 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);
766 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,'') || ';';
767 --RAISE INFO 'sql = %', sql;
768 PERFORM migration_tools.exec( $1, sql );
771 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
773 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
776 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
778 -- 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
779 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
781 migration_schema ALIAS FOR $1;
783 circ_table ALIAS FOR $2;
784 item_table ALIAS FOR $3;
785 patron_table ALIAS FOR $4;
788 sql_where1 TEXT := '';
789 sql_where2 TEXT := '';
790 sql_where3 TEXT := '';
791 sql_where4 TEXT := '';
794 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
796 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
798 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 ';
799 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);
800 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);
801 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);
802 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);
803 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,'') || ';';
804 --RAISE INFO 'sql = %', sql;
805 PERFORM migration_tools.exec( $1, sql );
808 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
810 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
813 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
816 -- $barcode source barcode
817 -- $prefix prefix to add to barcode, NULL = add no prefix
818 -- $maxlen maximum length of barcode; default to 14 if left NULL
819 -- $pad padding string to apply to left of source barcode before adding
820 -- prefix and suffix; set to NULL or '' if no padding is desired
821 -- $suffix suffix to add to barcode, NULL = add no suffix
823 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
824 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
826 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
827 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
830 return unless defined $barcode;
832 $prefix = '' unless defined $prefix;
834 $pad = '0' unless defined $pad;
835 $suffix = '' unless defined $suffix;
837 # bail out if adding prefix and suffix would bring new barcode over max length
838 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
840 my $new_barcode = $barcode;
842 my $pad_length = $maxlen - length($prefix) - length($suffix);
843 if (length($barcode) < $pad_length) {
844 # assuming we always want padding on the left
845 # also assuming that it is possible to have the pad string be longer than 1 character
846 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
850 # bail out if adding prefix and suffix would bring new barcode over max length
851 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
853 return "$prefix$new_barcode$suffix";
854 $$ LANGUAGE PLPERLU STABLE;
856 -- remove previous version of this function
857 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
859 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
861 attempt_value ALIAS FOR $1;
862 datatype ALIAS FOR $2;
864 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
865 RETURN attempt_value;
867 WHEN OTHERS THEN RETURN NULL;
869 $$ LANGUAGE PLPGSQL STRICT STABLE;
871 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
873 attempt_value ALIAS FOR $1;
874 fail_value ALIAS FOR $2;
878 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
885 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
890 $$ LANGUAGE PLPGSQL STRICT STABLE;
892 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
894 attempt_value ALIAS FOR $1;
895 fail_value ALIAS FOR $2;
899 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
906 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
911 $$ LANGUAGE PLPGSQL STRICT STABLE;
913 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
915 attempt_value ALIAS FOR $1;
916 fail_value ALIAS FOR $2;
920 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
927 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
932 $$ LANGUAGE PLPGSQL STRICT STABLE;
934 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
936 attempt_value ALIAS FOR $1;
937 fail_value ALIAS FOR $2;
940 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
945 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
950 $$ LANGUAGE PLPGSQL STRICT STABLE;
952 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
954 attempt_value ALIAS FOR $1;
955 fail_value ALIAS FOR $2;
959 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
966 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
971 $$ LANGUAGE PLPGSQL STRICT STABLE;
973 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
975 attempt_value ALIAS FOR $1;
976 fail_value ALIAS FOR $2;
980 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
987 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
992 $$ LANGUAGE PLPGSQL STRICT STABLE;
994 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
996 attempt_value ALIAS FOR $1;
997 fail_value ALIAS FOR $2;
1000 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1001 RAISE EXCEPTION 'too many digits';
1004 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;'
1011 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1016 $$ LANGUAGE PLPGSQL STRICT STABLE;
1018 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1020 attempt_value ALIAS FOR $1;
1021 fail_value ALIAS FOR $2;
1022 output NUMERIC(6,2);
1024 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1025 RAISE EXCEPTION 'too many digits';
1028 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;'
1035 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1040 $$ LANGUAGE PLPGSQL STRICT STABLE;
1042 -- add_codabar_checkdigit
1043 -- $barcode source barcode
1045 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1046 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1047 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1048 -- input string does not meet those requirements, it is returned unchanged.
1050 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1051 my $barcode = shift;
1053 return $barcode if $barcode !~ /^\d{13,14}$/;
1054 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1055 my @digits = split //, $barcode;
1057 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1058 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1059 my $remainder = $total % 10;
1060 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1061 return $barcode . $checkdigit;
1062 $$ LANGUAGE PLPERLU STRICT STABLE;
1064 -- add_code39mod43_checkdigit
1065 -- $barcode source barcode
1067 -- If the source string is 13 or 14 characters long and contains only valid
1068 -- Code 39 mod 43 characters, adds or replaces the 14th
1069 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1070 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1071 -- input string does not meet those requirements, it is returned unchanged.
1073 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1074 my $barcode = shift;
1076 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1077 $barcode = substr($barcode, 0, 13); # ignore 14th character
1079 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1080 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1083 $total += $nums{$_} foreach split(//, $barcode);
1084 my $remainder = $total % 43;
1085 my $checkdigit = $valid_chars[$remainder];
1086 return $barcode . $checkdigit;
1087 $$ LANGUAGE PLPERLU STRICT STABLE;
1089 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1092 areacode TEXT := $2;
1095 n_digits INTEGER := 0;
1098 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1099 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1100 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1101 IF n_digits = 7 AND areacode <> '' THEN
1102 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1103 output := (areacode || '-' || temp);
1110 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1112 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1113 my ($marcxml, $pos, $value) = @_;
1116 use MARC::File::XML;
1120 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1121 my $leader = $marc->leader();
1122 substr($leader, $pos, 1) = $value;
1123 $marc->leader($leader);
1124 $xml = $marc->as_xml_record;
1125 $xml =~ s/^<\?.+?\?>$//mo;
1127 $xml =~ s/>\s+</></sgo;
1130 $$ LANGUAGE PLPERLU STABLE;
1132 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1133 my ($marcxml, $pos, $value) = @_;
1136 use MARC::File::XML;
1140 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1141 my $f008 = $marc->field('008');
1144 my $field = $f008->data();
1145 substr($field, $pos, 1) = $value;
1146 $f008->update($field);
1147 $xml = $marc->as_xml_record;
1148 $xml =~ s/^<\?.+?\?>$//mo;
1150 $xml =~ s/>\s+</></sgo;
1154 $$ LANGUAGE PLPERLU STABLE;
1157 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1159 profile ALIAS FOR $1;
1161 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1163 $$ LANGUAGE PLPGSQL STRICT STABLE;
1166 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1168 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1170 $$ LANGUAGE PLPGSQL STRICT STABLE;
1173 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1175 my ($marcxml, $tags) = @_;
1178 use MARC::File::XML;
1183 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1184 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1186 my @incumbents = ();
1188 foreach my $field ( $marc->fields() ) {
1189 push @incumbents, $field->as_formatted();
1192 foreach $field ( $to_insert->fields() ) {
1193 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1194 $marc->insert_fields_ordered( ($field) );
1198 $xml = $marc->as_xml_record;
1199 $xml =~ s/^<\?.+?\?>$//mo;
1201 $xml =~ s/>\s+</></sgo;
1206 $$ LANGUAGE PLPERLU STABLE;
1208 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1212 -- First make sure the circ matrix is loaded and the circulations
1213 -- have been staged to the extent possible (but at the very least
1214 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1215 -- circ modifiers must also be in place.
1217 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1225 this_duration_rule INT;
1227 this_max_fine_rule INT;
1228 rcd config.rule_circ_duration%ROWTYPE;
1229 rrf config.rule_recurring_fine%ROWTYPE;
1230 rmf config.rule_max_fine%ROWTYPE;
1237 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1239 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1241 -- Fetch the correct rules for this circulation
1248 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1251 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1252 INTO circ_lib, target_copy, usr, is_renewal ;
1254 INTO this_duration_rule,
1258 recurring_fine_rule,
1260 FROM action.item_user_circ_test(
1266 SELECT INTO rcd * FROM config.rule_circ_duration
1267 WHERE id = this_duration_rule;
1268 SELECT INTO rrf * FROM config.rule_recurring_fine
1269 WHERE id = this_fine_rule;
1270 SELECT INTO rmf * FROM config.rule_max_fine
1271 WHERE id = this_max_fine_rule;
1273 -- Apply the rules to this circulation
1274 EXECUTE ('UPDATE ' || tablename || ' c
1276 duration_rule = rcd.name,
1277 recurring_fine_rule = rrf.name,
1278 max_fine_rule = rmf.name,
1279 duration = rcd.normal,
1280 recurring_fine = rrf.normal,
1283 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1286 renewal_remaining = rcd.max_renewals
1288 config.rule_circ_duration rcd,
1289 config.rule_recurring_fine rrf,
1290 config.rule_max_fine rmf,
1293 rcd.id = ' || this_duration_rule || ' AND
1294 rrf.id = ' || this_fine_rule || ' AND
1295 rmf.id = ' || this_max_fine_rule || ' AND
1296 ac.id = c.target_copy AND
1297 c.id = ' || circ || ';');
1299 -- Keep track of where we are in the process
1301 IF (n % 100 = 0) THEN
1302 RAISE INFO '%', n || ' of ' || n_circs
1303 || ' (' || (100*n/n_circs) || '%) circs updated.';
1311 $$ LANGUAGE plpgsql;
1313 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1317 -- First make sure the circ matrix is loaded and the circulations
1318 -- have been staged to the extent possible (but at the very least
1319 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1320 -- circ modifiers must also be in place.
1322 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1330 this_duration_rule INT;
1332 this_max_fine_rule INT;
1333 rcd config.rule_circ_duration%ROWTYPE;
1334 rrf config.rule_recurring_fine%ROWTYPE;
1335 rmf config.rule_max_fine%ROWTYPE;
1342 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1344 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1346 -- Fetch the correct rules for this circulation
1353 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1356 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1357 INTO circ_lib, target_copy, usr, is_renewal ;
1359 INTO this_duration_rule,
1365 FROM action.find_circ_matrix_matchpoint(
1371 SELECT INTO rcd * FROM config.rule_circ_duration
1372 WHERE id = this_duration_rule;
1373 SELECT INTO rrf * FROM config.rule_recurring_fine
1374 WHERE id = this_fine_rule;
1375 SELECT INTO rmf * FROM config.rule_max_fine
1376 WHERE id = this_max_fine_rule;
1378 -- Apply the rules to this circulation
1379 EXECUTE ('UPDATE ' || tablename || ' c
1381 duration_rule = rcd.name,
1382 recuring_fine_rule = rrf.name,
1383 max_fine_rule = rmf.name,
1384 duration = rcd.normal,
1385 recuring_fine = rrf.normal,
1388 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1391 renewal_remaining = rcd.max_renewals
1393 config.rule_circ_duration rcd,
1394 config.rule_recuring_fine rrf,
1395 config.rule_max_fine rmf,
1398 rcd.id = ' || this_duration_rule || ' AND
1399 rrf.id = ' || this_fine_rule || ' AND
1400 rmf.id = ' || this_max_fine_rule || ' AND
1401 ac.id = c.target_copy AND
1402 c.id = ' || circ || ';');
1404 -- Keep track of where we are in the process
1406 IF (n % 100 = 0) THEN
1407 RAISE INFO '%', n || ' of ' || n_circs
1408 || ' (' || (100*n/n_circs) || '%) circs updated.';
1416 $$ LANGUAGE plpgsql;
1418 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1422 -- First make sure the circ matrix is loaded and the circulations
1423 -- have been staged to the extent possible (but at the very least
1424 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1425 -- circ modifiers must also be in place.
1427 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1435 this_duration_rule INT;
1437 this_max_fine_rule INT;
1438 rcd config.rule_circ_duration%ROWTYPE;
1439 rrf config.rule_recurring_fine%ROWTYPE;
1440 rmf config.rule_max_fine%ROWTYPE;
1447 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1449 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1451 -- Fetch the correct rules for this circulation
1458 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1461 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1462 INTO circ_lib, target_copy, usr, is_renewal ;
1464 INTO this_duration_rule,
1467 (matchpoint).duration_rule,
1468 (matchpoint).recurring_fine_rule,
1469 (matchpoint).max_fine_rule
1470 FROM action.find_circ_matrix_matchpoint(
1476 SELECT INTO rcd * FROM config.rule_circ_duration
1477 WHERE id = this_duration_rule;
1478 SELECT INTO rrf * FROM config.rule_recurring_fine
1479 WHERE id = this_fine_rule;
1480 SELECT INTO rmf * FROM config.rule_max_fine
1481 WHERE id = this_max_fine_rule;
1483 -- Apply the rules to this circulation
1484 EXECUTE ('UPDATE ' || tablename || ' c
1486 duration_rule = rcd.name,
1487 recurring_fine_rule = rrf.name,
1488 max_fine_rule = rmf.name,
1489 duration = rcd.normal,
1490 recurring_fine = rrf.normal,
1493 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1496 renewal_remaining = rcd.max_renewals,
1497 grace_period = rrf.grace_period
1499 config.rule_circ_duration rcd,
1500 config.rule_recurring_fine rrf,
1501 config.rule_max_fine rmf,
1504 rcd.id = ' || this_duration_rule || ' AND
1505 rrf.id = ' || this_fine_rule || ' AND
1506 rmf.id = ' || this_max_fine_rule || ' AND
1507 ac.id = c.target_copy AND
1508 c.id = ' || circ || ';');
1510 -- Keep track of where we are in the process
1512 IF (n % 100 = 0) THEN
1513 RAISE INFO '%', n || ' of ' || n_circs
1514 || ' (' || (100*n/n_circs) || '%) circs updated.';
1522 $$ LANGUAGE plpgsql;
1524 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1528 -- First make sure the circ matrix is loaded and the circulations
1529 -- have been staged to the extent possible (but at the very least
1530 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1531 -- circ modifiers must also be in place.
1533 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1541 this_duration_rule INT;
1543 this_max_fine_rule INT;
1544 rcd config.rule_circ_duration%ROWTYPE;
1545 rrf config.rule_recurring_fine%ROWTYPE;
1546 rmf config.rule_max_fine%ROWTYPE;
1552 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1554 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1556 -- Fetch the correct rules for this circulation
1563 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1566 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1567 INTO circ_lib, target_copy, usr, is_renewal ;
1569 INTO this_duration_rule,
1572 (matchpoint).duration_rule,
1573 (matchpoint).recurring_fine_rule,
1574 (matchpoint).max_fine_rule
1575 FROM action.find_circ_matrix_matchpoint(
1581 SELECT INTO rcd * FROM config.rule_circ_duration
1582 WHERE id = this_duration_rule;
1583 SELECT INTO rrf * FROM config.rule_recurring_fine
1584 WHERE id = this_fine_rule;
1585 SELECT INTO rmf * FROM config.rule_max_fine
1586 WHERE id = this_max_fine_rule;
1588 -- Apply the rules to this circulation
1589 EXECUTE ('UPDATE ' || tablename || ' c
1591 duration_rule = rcd.name,
1592 recurring_fine_rule = rrf.name,
1593 max_fine_rule = rmf.name,
1594 duration = rcd.normal,
1595 recurring_fine = rrf.normal,
1598 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1601 renewal_remaining = rcd.max_renewals,
1602 grace_period = rrf.grace_period
1604 config.rule_circ_duration rcd,
1605 config.rule_recurring_fine rrf,
1606 config.rule_max_fine rmf,
1609 rcd.id = ' || this_duration_rule || ' AND
1610 rrf.id = ' || this_fine_rule || ' AND
1611 rmf.id = ' || this_max_fine_rule || ' AND
1612 ac.id = c.target_copy AND
1613 c.id = ' || circ || ';');
1615 -- Keep track of where we are in the process
1617 IF (n % 100 = 0) THEN
1618 RAISE INFO '%', n || ' of ' || n_circs
1619 || ' (' || (100*n/n_circs) || '%) circs updated.';
1627 $$ LANGUAGE plpgsql;
1632 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1634 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1635 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1637 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1638 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1641 c TEXT := schemaname || '.asset_copy_legacy';
1642 sc TEXT := schemaname || '.asset_stat_cat';
1643 sce TEXT := schemaname || '.asset_stat_cat_entry';
1644 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1650 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1652 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1654 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1655 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1656 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1663 $$ LANGUAGE plpgsql;
1665 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1667 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1668 -- This will assign standing penalties as needed.
1676 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1678 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1680 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1690 $$ LANGUAGE plpgsql;
1693 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1696 INSERT INTO metabib.metarecord (fingerprint, master_record)
1697 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1698 FROM biblio.record_entry b
1700 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)
1701 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1702 ORDER BY b.fingerprint, b.quality DESC;
1703 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1705 FROM biblio.record_entry r
1706 JOIN metabib.metarecord m USING (fingerprint)
1707 WHERE NOT r.deleted;
1710 $$ LANGUAGE plpgsql;
1713 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1716 INSERT INTO metabib.metarecord (fingerprint, master_record)
1717 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1718 FROM biblio.record_entry b
1720 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)
1721 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1722 ORDER BY b.fingerprint, b.quality DESC;
1723 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1725 FROM biblio.record_entry r
1726 JOIN metabib.metarecord m USING (fingerprint)
1728 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);
1731 $$ LANGUAGE plpgsql;
1734 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1736 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1737 -- Then SELECT migration_tools.create_cards('m_foo');
1740 u TEXT := schemaname || '.actor_usr_legacy';
1741 c TEXT := schemaname || '.actor_card';
1745 EXECUTE ('DELETE FROM ' || c || ';');
1746 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1747 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1753 $$ LANGUAGE plpgsql;
1756 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1758 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1760 my ($marcxml, $shortname) = @_;
1763 use MARC::File::XML;
1768 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1770 foreach my $field ( $marc->field('856') ) {
1771 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1772 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1773 $field->add_subfields( '9' => $shortname );
1774 $field->update( ind2 => '0');
1778 $xml = $marc->as_xml_record;
1779 $xml =~ s/^<\?.+?\?>$//mo;
1781 $xml =~ s/>\s+</></sgo;
1786 $$ LANGUAGE PLPERLU STABLE;
1788 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1790 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1792 my ($marcxml, $shortname) = @_;
1795 use MARC::File::XML;
1800 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1802 foreach my $field ( $marc->field('856') ) {
1803 if ( ! $field->as_string('9') ) {
1804 $field->add_subfields( '9' => $shortname );
1808 $xml = $marc->as_xml_record;
1809 $xml =~ s/^<\?.+?\?>$//mo;
1811 $xml =~ s/>\s+</></sgo;
1816 $$ LANGUAGE PLPERLU STABLE;
1819 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1831 -- Bail out if asked to change the label to ##URI##
1832 IF new_label = '##URI##' THEN
1836 -- Gather information
1837 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1838 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1839 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1841 -- Bail out if the label already is ##URI##
1842 IF old_label = '##URI##' THEN
1846 -- Bail out if the call number label is already correct
1847 IF new_volume = old_volume THEN
1851 -- Check whether we already have a destination volume available
1852 SELECT id INTO new_volume FROM asset.call_number
1855 owning_lib = owner AND
1856 label = new_label AND
1859 -- Create destination volume if needed
1861 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1862 VALUES (1, 1, bib, owner, new_label, cn_class);
1863 SELECT id INTO new_volume FROM asset.call_number
1866 owning_lib = owner AND
1867 label = new_label AND
1871 -- Move copy to destination
1872 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1874 -- Delete source volume if it is now empty
1875 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1877 DELETE FROM asset.call_number WHERE id = old_volume;
1882 $$ LANGUAGE plpgsql;
1884 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1889 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1893 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1894 $zipdata{$zip} = [$city, $state, $county];
1897 if (defined $zipdata{$input}) {
1898 my ($city, $state, $county) = @{$zipdata{$input}};
1899 return [$city, $state, $county];
1900 } elsif (defined $zipdata{substr $input, 0, 5}) {
1901 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1902 return [$city, $state, $county];
1904 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1907 $$ LANGUAGE PLPERLU STABLE;
1909 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1916 errors_found BOOLEAN;
1918 parent_shortname TEXT;
1924 type_parent_depth INT;
1929 errors_found := FALSE;
1931 -- Checking actor.org_unit_type
1933 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1935 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1936 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1938 IF type_parent IS NOT NULL THEN
1940 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1942 IF type_depth - type_parent_depth <> 1 THEN
1943 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1944 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1945 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1946 ou_type_name, type_depth, parent_type, type_parent_depth;
1947 errors_found := TRUE;
1955 -- Checking actor.org_unit
1957 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1959 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1960 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;
1961 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;
1962 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1963 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1964 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;
1965 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;
1967 IF ou_parent IS NOT NULL THEN
1969 IF (org_unit_depth - parent_depth <> 1) OR (
1970 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1972 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1973 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1974 errors_found := TRUE;
1981 IF NOT errors_found THEN
1982 RAISE INFO 'No errors found.';
1989 $$ LANGUAGE plpgsql;
1992 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1996 DELETE FROM asset.opac_visible_copies;
1998 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2000 cp.id, cp.circ_lib, cn.record
2003 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2004 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2005 JOIN asset.copy_location cl ON (cp.location = cl.id)
2006 JOIN config.copy_status cs ON (cp.status = cs.id)
2007 JOIN biblio.record_entry b ON (cn.record = b.id)
2016 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2020 $$ LANGUAGE plpgsql;
2023 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2029 old_owning_lib INTEGER;
2035 -- Gather information
2036 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2037 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2038 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2040 -- Bail out if the new_owning_lib is not the ID of an org_unit
2041 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2043 '% is not a valid actor.org_unit ID; no change made.',
2048 -- Bail out discreetly if the owning_lib is already correct
2049 IF new_owning_lib = old_owning_lib THEN
2053 -- Check whether we already have a destination volume available
2054 SELECT id INTO new_volume FROM asset.call_number
2057 owning_lib = new_owning_lib AND
2058 label = old_label AND
2061 -- Create destination volume if needed
2063 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2064 VALUES (1, 1, bib, new_owning_lib, old_label);
2065 SELECT id INTO new_volume FROM asset.call_number
2068 owning_lib = new_owning_lib AND
2069 label = old_label AND
2073 -- Move copy to destination
2074 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2076 -- Delete source volume if it is now empty
2077 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2079 DELETE FROM asset.call_number WHERE id = old_volume;
2084 $$ LANGUAGE plpgsql;
2087 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2089 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2092 new_owning_lib INTEGER;
2096 -- Parse the new_owner as an org unit ID or shortname
2097 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2098 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2099 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2100 ELSIF new_owner ~ E'^[0-9]+$' THEN
2101 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2104 E'You don\'t need to put the actor.org_unit ID in quotes; '
2105 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2106 new_owning_lib := new_owner::INTEGER;
2107 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2111 '% is not a valid actor.org_unit shortname or ID; no change made.',
2118 $$ LANGUAGE plpgsql;
2120 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2123 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2126 MARC::Charset->assume_unicode(1);
2131 my $r = MARC::Record->new_from_xml( $xml );
2132 my $output_xml = $r->as_xml_record();
2140 $func$ LANGUAGE PLPERLU;
2141 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2143 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2145 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2146 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2147 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2148 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2149 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2150 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2151 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2152 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2153 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2154 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2155 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2156 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2157 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2158 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2159 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2160 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2161 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2162 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2163 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2164 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2165 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2166 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2167 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2168 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2169 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2170 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2172 $FUNC$ LANGUAGE PLPGSQL;
2174 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2176 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2177 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2178 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2179 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2180 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2181 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2182 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2184 -- import any new circ rules
2185 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2186 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2187 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2188 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2190 -- and permission groups
2191 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2194 $FUNC$ LANGUAGE PLPGSQL;
2197 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$
2206 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2207 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2208 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2209 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2210 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2211 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2212 FOR name IN EXECUTE loopq LOOP
2213 EXECUTE existsq INTO ct USING name;
2215 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2216 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2217 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2218 EXECUTE copyst USING name;
2222 $FUNC$ LANGUAGE PLPGSQL;
2224 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2230 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2233 MARC::Charset->assume_unicode(1);
2235 my $target_xml = shift;
2236 my $source_xml = shift;
2242 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2246 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2251 my $source_id = $source->subfield('901', 'c');
2252 $source_id = $source->subfield('903', 'a') unless $source_id;
2253 my $target_id = $target->subfield('901', 'c');
2254 $target_id = $target->subfield('903', 'a') unless $target_id;
2256 my %existing_fields;
2257 foreach my $tag (@$tags) {
2258 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2259 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2260 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2262 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2266 my $xml = $target->as_xml_record;
2267 $xml =~ s/^<\?.+?\?>$//mo;
2269 $xml =~ s/>\s+</></sgo;
2273 $func$ LANGUAGE PLPERLU;
2274 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.';
2276 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2282 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2285 my $in_tags = shift;
2286 my $in_values = shift;
2288 # hack-and-slash parsing of array-passed-as-string;
2289 # this can go away once everybody is running Postgres 9.1+
2290 my $csv = Text::CSV->new({binary => 1});
2293 my $status = $csv->parse($in_tags);
2294 my $tags = [ $csv->fields() ];
2295 $in_values =~ s/^{//;
2296 $in_values =~ s/}$//;
2297 $status = $csv->parse($in_values);
2298 my $values = [ $csv->fields() ];
2300 my $marc = MARC::Record->new();
2302 $marc->leader('00000nam a22000007 4500');
2303 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2305 foreach my $i (0..$#$tags) {
2307 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2310 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2311 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2313 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2317 my $xml = $marc->as_xml_record;
2318 $xml =~ s/^<\?.+?\?>$//mo;
2320 $xml =~ s/>\s+</></sgo;
2324 $func$ LANGUAGE PLPERLU;
2325 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2326 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2327 The second argument is an array of text containing the values to plug into each field.
2328 If the value for a given field is NULL or the empty string, it is not inserted.
2331 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2333 my ($marcxml, $tag, $pos, $value) = @_;
2336 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2340 MARC::Charset->assume_unicode(1);
2342 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2343 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2344 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2345 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2349 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2351 foreach my $field ($marc->field($tag)) {
2352 $field->update("ind$pos" => $value);
2354 $xml = $marc->as_xml_record;
2355 $xml =~ s/^<\?.+?\?>$//mo;
2357 $xml =~ s/>\s+</></sgo;
2361 $func$ LANGUAGE PLPERLU;
2363 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2364 The first argument is a MARCXML string.
2365 The second argument is a MARC tag.
2366 The third argument is the indicator position, either 1 or 2.
2367 The fourth argument is the character to set the indicator value to.
2368 All occurences of the specified field will be changed.
2369 The function returns the revised MARCXML string.$$;
2371 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2376 first_name TEXT DEFAULT '',
2377 last_name TEXT DEFAULT ''
2378 ) RETURNS VOID AS $func$
2380 RAISE NOTICE '%', org ;
2381 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2382 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2383 FROM actor.org_unit aou, permission.grp_tree pgt
2384 WHERE aou.shortname = org
2385 AND pgt.name = perm_group;
2390 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2391 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2393 target_event_def ALIAS FOR $1;
2396 DROP TABLE IF EXISTS new_atevdefs;
2397 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2398 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2399 INSERT INTO action_trigger.event_definition (
2420 ,name || ' (clone of '||target_event_def||')'
2436 action_trigger.event_definition
2438 id = target_event_def
2440 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2441 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2442 INSERT INTO action_trigger.environment (
2448 currval('action_trigger.event_definition_id_seq')
2453 action_trigger.environment
2455 event_def = target_event_def
2457 INSERT INTO action_trigger.event_params (
2462 currval('action_trigger.event_definition_id_seq')
2466 action_trigger.event_params
2468 event_def = target_event_def
2471 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);
2473 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2475 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2476 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2478 target_event_def ALIAS FOR $1;
2480 new_interval ALIAS FOR $3;
2482 DROP TABLE IF EXISTS new_atevdefs;
2483 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2484 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2485 INSERT INTO action_trigger.event_definition (
2506 ,name || ' (clone of '||target_event_def||')'
2522 action_trigger.event_definition
2524 id = target_event_def
2526 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2527 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2528 INSERT INTO action_trigger.environment (
2534 currval('action_trigger.event_definition_id_seq')
2539 action_trigger.environment
2541 event_def = target_event_def
2543 INSERT INTO action_trigger.event_params (
2548 currval('action_trigger.event_definition_id_seq')
2552 action_trigger.event_params
2554 event_def = target_event_def
2557 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);
2559 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2561 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2562 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2565 use MARC::File::XML;
2570 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2571 $field = $marc->field($tag);
2573 return $field->as_string($subfield,$delimiter);
2574 $$ LANGUAGE PLPERLU STABLE;
2576 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2577 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2580 use MARC::File::XML;
2585 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2586 @fields = $marc->field($tag);
2589 foreach my $field (@fields) {
2590 push @texts, $field->as_string($subfield,$delimiter);
2593 $$ LANGUAGE PLPERLU STABLE;
2595 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2596 SELECT action.find_hold_matrix_matchpoint(
2597 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2598 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2599 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2600 (SELECT usr FROM action.hold_request WHERE id = $1),
2601 (SELECT requestor FROM action.hold_request WHERE id = $1)
2605 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2606 SELECT action.hold_request_permit_test(
2607 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2608 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2609 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2610 (SELECT usr FROM action.hold_request WHERE id = $1),
2611 (SELECT requestor FROM action.hold_request WHERE id = $1)
2615 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2616 SELECT action.find_circ_matrix_matchpoint(
2617 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2618 (SELECT target_copy FROM action.circulation WHERE id = $1),
2619 (SELECT usr FROM action.circulation WHERE id = $1),
2621 NULLIF(phone_renewal,false),
2622 NULLIF(desk_renewal,false),
2623 NULLIF(opac_renewal,false),
2625 ) FROM action.circulation WHERE id = $1
2630 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2635 RAISE EXCEPTION 'assertion';
2638 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2640 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2646 RAISE EXCEPTION '%', msg;
2649 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2651 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2654 fail_msg ALIAS FOR $2;
2655 success_msg ALIAS FOR $3;
2658 RAISE EXCEPTION '%', fail_msg;
2662 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2664 -- push bib sequence and return starting value for reserved range
2665 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2667 bib_count ALIAS FOR $1;
2670 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2672 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2677 $$ LANGUAGE PLPGSQL STRICT VOLATILE;