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,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry'' );' );
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', '' ));
486 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
487 temp := BTRIM(REPLACE( temp, family_name, '' ));
488 family_name := REPLACE( family_name, ',', '' );
490 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
491 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
493 first_given_name := temp;
494 second_given_name := '';
497 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
498 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
499 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
500 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
502 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
503 second_given_name := temp;
504 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
508 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
510 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
512 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
514 city_state_zip TEXT := $1;
519 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;
520 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
521 IF city_state_zip ~ ',' THEN
522 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
523 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
525 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
526 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
527 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
529 IF city_state_zip ~ E'^\\S+$' THEN
530 city := city_state_zip;
533 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
534 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
538 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
540 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
542 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
543 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
545 fullstring TEXT := $1;
555 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
556 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
559 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
561 IF fullstring ~ ',' THEN
562 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
563 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
565 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
566 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
567 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
569 IF fullstring ~ E'^\\S+$' THEN
570 scratch1 := fullstring;
573 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
574 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
579 IF scratch1 ~ '[\$]' THEN
580 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
581 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
583 IF scratch1 ~ '\s' THEN
584 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
585 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
592 IF scratch2 ~ '^\d' THEN
593 address1 := scratch2;
596 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
597 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
601 TRIM(BOTH ' ' FROM address1)
602 ,TRIM(BOTH ' ' FROM address2)
603 ,TRIM(BOTH ' ' FROM city)
604 ,TRIM(BOTH ' ' FROM state)
605 ,TRIM(BOTH ' ' FROM zip)
608 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
610 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
614 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
615 IF o::BIGINT < t THEN
622 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
624 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
626 migration_schema ALIAS FOR $1;
630 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
635 $$ LANGUAGE PLPGSQL STRICT STABLE;
637 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
639 migration_schema ALIAS FOR $1;
643 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
648 $$ LANGUAGE PLPGSQL STRICT STABLE;
650 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
652 migration_schema ALIAS FOR $1;
656 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
661 $$ LANGUAGE PLPGSQL STRICT STABLE;
663 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
665 migration_schema ALIAS FOR $1;
669 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
674 $$ LANGUAGE PLPGSQL STRICT STABLE;
676 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
678 migration_schema ALIAS FOR $1;
680 patron_table ALIAS FOR $2;
681 default_patron_profile ALIAS FOR $3;
684 sql_where1 TEXT := '';
685 sql_where2 TEXT := '';
686 sql_where3 TEXT := '';
689 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
691 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
693 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
694 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);
695 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);
696 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);
697 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,'') || ';';
698 --RAISE INFO 'sql = %', sql;
699 PERFORM migration_tools.exec( $1, sql );
701 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
703 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
705 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
708 $$ LANGUAGE PLPGSQL STRICT STABLE;
710 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
712 migration_schema ALIAS FOR $1;
714 item_table ALIAS FOR $2;
717 sql_where1 TEXT := '';
718 sql_where2 TEXT := '';
719 sql_where3 TEXT := '';
722 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
724 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
726 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 ';
727 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);
728 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);
729 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);
730 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,'') || ';';
731 --RAISE INFO 'sql = %', sql;
732 PERFORM migration_tools.exec( $1, sql );
735 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
737 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
740 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
742 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
744 migration_schema ALIAS FOR $1;
745 base_copy_location_map TEXT;
746 item_table ALIAS FOR $2;
749 sql_where1 TEXT := '';
750 sql_where2 TEXT := '';
751 sql_where3 TEXT := '';
754 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
756 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
758 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
759 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);
760 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);
761 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);
762 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,'') || ';';
763 --RAISE INFO 'sql = %', sql;
764 PERFORM migration_tools.exec( $1, sql );
767 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
769 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
772 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
774 -- 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
775 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
777 migration_schema ALIAS FOR $1;
779 circ_table ALIAS FOR $2;
780 item_table ALIAS FOR $3;
781 patron_table ALIAS FOR $4;
784 sql_where1 TEXT := '';
785 sql_where2 TEXT := '';
786 sql_where3 TEXT := '';
787 sql_where4 TEXT := '';
790 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
792 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
794 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 ';
795 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);
796 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);
797 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);
798 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);
799 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,'') || ';';
800 --RAISE INFO 'sql = %', sql;
801 PERFORM migration_tools.exec( $1, sql );
804 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
806 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
809 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
812 -- $barcode source barcode
813 -- $prefix prefix to add to barcode, NULL = add no prefix
814 -- $maxlen maximum length of barcode; default to 14 if left NULL
815 -- $pad padding string to apply to left of source barcode before adding
816 -- prefix and suffix; set to NULL or '' if no padding is desired
817 -- $suffix suffix to add to barcode, NULL = add no suffix
819 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
820 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
822 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
823 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
826 return unless defined $barcode;
828 $prefix = '' unless defined $prefix;
830 $pad = '0' unless defined $pad;
831 $suffix = '' unless defined $suffix;
833 # bail out if adding prefix and suffix would bring new barcode over max length
834 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
836 my $new_barcode = $barcode;
838 my $pad_length = $maxlen - length($prefix) - length($suffix);
839 if (length($barcode) < $pad_length) {
840 # assuming we always want padding on the left
841 # also assuming that it is possible to have the pad string be longer than 1 character
842 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
846 # bail out if adding prefix and suffix would bring new barcode over max length
847 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
849 return "$prefix$new_barcode$suffix";
850 $$ LANGUAGE PLPERLU STABLE;
852 -- remove previous version of this function
853 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
855 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
857 attempt_value ALIAS FOR $1;
858 datatype ALIAS FOR $2;
860 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
861 RETURN attempt_value;
863 WHEN OTHERS THEN RETURN NULL;
865 $$ LANGUAGE PLPGSQL STRICT STABLE;
867 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
869 attempt_value ALIAS FOR $1;
870 fail_value ALIAS FOR $2;
874 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
881 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
886 $$ LANGUAGE PLPGSQL STRICT STABLE;
888 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
890 attempt_value ALIAS FOR $1;
891 fail_value ALIAS FOR $2;
895 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
902 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
907 $$ LANGUAGE PLPGSQL STRICT STABLE;
909 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
911 attempt_value ALIAS FOR $1;
912 fail_value ALIAS FOR $2;
916 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
923 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
928 $$ LANGUAGE PLPGSQL STRICT STABLE;
930 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
932 attempt_value ALIAS FOR $1;
933 fail_value ALIAS FOR $2;
936 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
941 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
946 $$ LANGUAGE PLPGSQL STRICT STABLE;
948 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
950 attempt_value ALIAS FOR $1;
951 fail_value ALIAS FOR $2;
955 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
962 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
967 $$ LANGUAGE PLPGSQL STRICT STABLE;
969 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
971 attempt_value ALIAS FOR $1;
972 fail_value ALIAS FOR $2;
976 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
983 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
988 $$ LANGUAGE PLPGSQL STRICT STABLE;
990 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
992 attempt_value ALIAS FOR $1;
993 fail_value ALIAS FOR $2;
996 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
997 RAISE EXCEPTION 'too many digits';
1000 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;'
1007 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1012 $$ LANGUAGE PLPGSQL STRICT STABLE;
1014 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1016 attempt_value ALIAS FOR $1;
1017 fail_value ALIAS FOR $2;
1018 output NUMERIC(6,2);
1020 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1021 RAISE EXCEPTION 'too many digits';
1024 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;'
1031 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1036 $$ LANGUAGE PLPGSQL STRICT STABLE;
1038 -- add_codabar_checkdigit
1039 -- $barcode source barcode
1041 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1042 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1043 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1044 -- input string does not meet those requirements, it is returned unchanged.
1046 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1047 my $barcode = shift;
1049 return $barcode if $barcode !~ /^\d{13,14}$/;
1050 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1051 my @digits = split //, $barcode;
1053 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1054 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1055 my $remainder = $total % 10;
1056 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1057 return $barcode . $checkdigit;
1058 $$ LANGUAGE PLPERLU STRICT STABLE;
1060 -- add_code39mod43_checkdigit
1061 -- $barcode source barcode
1063 -- If the source string is 13 or 14 characters long and contains only valid
1064 -- Code 39 mod 43 characters, adds or replaces the 14th
1065 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1066 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1067 -- input string does not meet those requirements, it is returned unchanged.
1069 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1070 my $barcode = shift;
1072 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1073 $barcode = substr($barcode, 0, 13); # ignore 14th character
1075 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1076 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1079 $total += $nums{$_} foreach split(//, $barcode);
1080 my $remainder = $total % 43;
1081 my $checkdigit = $valid_chars[$remainder];
1082 return $barcode . $checkdigit;
1083 $$ LANGUAGE PLPERLU STRICT STABLE;
1085 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1088 areacode TEXT := $2;
1091 n_digits INTEGER := 0;
1094 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1095 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1096 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1097 IF n_digits = 7 AND areacode <> '' THEN
1098 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1099 output := (areacode || '-' || temp);
1106 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1108 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1109 my ($marcxml, $pos, $value) = @_;
1112 use MARC::File::XML;
1116 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1117 my $leader = $marc->leader();
1118 substr($leader, $pos, 1) = $value;
1119 $marc->leader($leader);
1120 $xml = $marc->as_xml_record;
1121 $xml =~ s/^<\?.+?\?>$//mo;
1123 $xml =~ s/>\s+</></sgo;
1126 $$ LANGUAGE PLPERLU STABLE;
1128 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1129 my ($marcxml, $pos, $value) = @_;
1132 use MARC::File::XML;
1136 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1137 my $f008 = $marc->field('008');
1140 my $field = $f008->data();
1141 substr($field, $pos, 1) = $value;
1142 $f008->update($field);
1143 $xml = $marc->as_xml_record;
1144 $xml =~ s/^<\?.+?\?>$//mo;
1146 $xml =~ s/>\s+</></sgo;
1150 $$ LANGUAGE PLPERLU STABLE;
1153 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1155 profile ALIAS FOR $1;
1157 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1159 $$ LANGUAGE PLPGSQL STRICT STABLE;
1162 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1164 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1166 $$ LANGUAGE PLPGSQL STRICT STABLE;
1169 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1171 my ($marcxml, $tags) = @_;
1174 use MARC::File::XML;
1179 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1180 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1182 my @incumbents = ();
1184 foreach my $field ( $marc->fields() ) {
1185 push @incumbents, $field->as_formatted();
1188 foreach $field ( $to_insert->fields() ) {
1189 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1190 $marc->insert_fields_ordered( ($field) );
1194 $xml = $marc->as_xml_record;
1195 $xml =~ s/^<\?.+?\?>$//mo;
1197 $xml =~ s/>\s+</></sgo;
1202 $$ LANGUAGE PLPERLU STABLE;
1204 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1208 -- First make sure the circ matrix is loaded and the circulations
1209 -- have been staged to the extent possible (but at the very least
1210 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1211 -- circ modifiers must also be in place.
1213 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1221 this_duration_rule INT;
1223 this_max_fine_rule INT;
1224 rcd config.rule_circ_duration%ROWTYPE;
1225 rrf config.rule_recurring_fine%ROWTYPE;
1226 rmf config.rule_max_fine%ROWTYPE;
1233 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1235 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1237 -- Fetch the correct rules for this circulation
1244 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1247 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1248 INTO circ_lib, target_copy, usr, is_renewal ;
1250 INTO this_duration_rule,
1254 recurring_fine_rule,
1256 FROM action.item_user_circ_test(
1262 SELECT INTO rcd * FROM config.rule_circ_duration
1263 WHERE id = this_duration_rule;
1264 SELECT INTO rrf * FROM config.rule_recurring_fine
1265 WHERE id = this_fine_rule;
1266 SELECT INTO rmf * FROM config.rule_max_fine
1267 WHERE id = this_max_fine_rule;
1269 -- Apply the rules to this circulation
1270 EXECUTE ('UPDATE ' || tablename || ' c
1272 duration_rule = rcd.name,
1273 recurring_fine_rule = rrf.name,
1274 max_fine_rule = rmf.name,
1275 duration = rcd.normal,
1276 recurring_fine = rrf.normal,
1279 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1282 renewal_remaining = rcd.max_renewals
1284 config.rule_circ_duration rcd,
1285 config.rule_recurring_fine rrf,
1286 config.rule_max_fine rmf,
1289 rcd.id = ' || this_duration_rule || ' AND
1290 rrf.id = ' || this_fine_rule || ' AND
1291 rmf.id = ' || this_max_fine_rule || ' AND
1292 ac.id = c.target_copy AND
1293 c.id = ' || circ || ';');
1295 -- Keep track of where we are in the process
1297 IF (n % 100 = 0) THEN
1298 RAISE INFO '%', n || ' of ' || n_circs
1299 || ' (' || (100*n/n_circs) || '%) circs updated.';
1307 $$ LANGUAGE plpgsql;
1309 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1313 -- First make sure the circ matrix is loaded and the circulations
1314 -- have been staged to the extent possible (but at the very least
1315 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1316 -- circ modifiers must also be in place.
1318 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1326 this_duration_rule INT;
1328 this_max_fine_rule INT;
1329 rcd config.rule_circ_duration%ROWTYPE;
1330 rrf config.rule_recurring_fine%ROWTYPE;
1331 rmf config.rule_max_fine%ROWTYPE;
1338 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1340 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1342 -- Fetch the correct rules for this circulation
1349 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1352 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1353 INTO circ_lib, target_copy, usr, is_renewal ;
1355 INTO this_duration_rule,
1361 FROM action.find_circ_matrix_matchpoint(
1367 SELECT INTO rcd * FROM config.rule_circ_duration
1368 WHERE id = this_duration_rule;
1369 SELECT INTO rrf * FROM config.rule_recurring_fine
1370 WHERE id = this_fine_rule;
1371 SELECT INTO rmf * FROM config.rule_max_fine
1372 WHERE id = this_max_fine_rule;
1374 -- Apply the rules to this circulation
1375 EXECUTE ('UPDATE ' || tablename || ' c
1377 duration_rule = rcd.name,
1378 recuring_fine_rule = rrf.name,
1379 max_fine_rule = rmf.name,
1380 duration = rcd.normal,
1381 recuring_fine = rrf.normal,
1384 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1387 renewal_remaining = rcd.max_renewals
1389 config.rule_circ_duration rcd,
1390 config.rule_recuring_fine rrf,
1391 config.rule_max_fine rmf,
1394 rcd.id = ' || this_duration_rule || ' AND
1395 rrf.id = ' || this_fine_rule || ' AND
1396 rmf.id = ' || this_max_fine_rule || ' AND
1397 ac.id = c.target_copy AND
1398 c.id = ' || circ || ';');
1400 -- Keep track of where we are in the process
1402 IF (n % 100 = 0) THEN
1403 RAISE INFO '%', n || ' of ' || n_circs
1404 || ' (' || (100*n/n_circs) || '%) circs updated.';
1412 $$ LANGUAGE plpgsql;
1414 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1418 -- First make sure the circ matrix is loaded and the circulations
1419 -- have been staged to the extent possible (but at the very least
1420 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1421 -- circ modifiers must also be in place.
1423 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1431 this_duration_rule INT;
1433 this_max_fine_rule INT;
1434 rcd config.rule_circ_duration%ROWTYPE;
1435 rrf config.rule_recurring_fine%ROWTYPE;
1436 rmf config.rule_max_fine%ROWTYPE;
1443 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1445 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1447 -- Fetch the correct rules for this circulation
1454 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1457 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1458 INTO circ_lib, target_copy, usr, is_renewal ;
1460 INTO this_duration_rule,
1463 (matchpoint).duration_rule,
1464 (matchpoint).recurring_fine_rule,
1465 (matchpoint).max_fine_rule
1466 FROM action.find_circ_matrix_matchpoint(
1472 SELECT INTO rcd * FROM config.rule_circ_duration
1473 WHERE id = this_duration_rule;
1474 SELECT INTO rrf * FROM config.rule_recurring_fine
1475 WHERE id = this_fine_rule;
1476 SELECT INTO rmf * FROM config.rule_max_fine
1477 WHERE id = this_max_fine_rule;
1479 -- Apply the rules to this circulation
1480 EXECUTE ('UPDATE ' || tablename || ' c
1482 duration_rule = rcd.name,
1483 recurring_fine_rule = rrf.name,
1484 max_fine_rule = rmf.name,
1485 duration = rcd.normal,
1486 recurring_fine = rrf.normal,
1489 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1492 renewal_remaining = rcd.max_renewals,
1493 grace_period = rrf.grace_period
1495 config.rule_circ_duration rcd,
1496 config.rule_recurring_fine rrf,
1497 config.rule_max_fine rmf,
1500 rcd.id = ' || this_duration_rule || ' AND
1501 rrf.id = ' || this_fine_rule || ' AND
1502 rmf.id = ' || this_max_fine_rule || ' AND
1503 ac.id = c.target_copy AND
1504 c.id = ' || circ || ';');
1506 -- Keep track of where we are in the process
1508 IF (n % 100 = 0) THEN
1509 RAISE INFO '%', n || ' of ' || n_circs
1510 || ' (' || (100*n/n_circs) || '%) circs updated.';
1518 $$ LANGUAGE plpgsql;
1520 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1524 -- First make sure the circ matrix is loaded and the circulations
1525 -- have been staged to the extent possible (but at the very least
1526 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1527 -- circ modifiers must also be in place.
1529 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1537 this_duration_rule INT;
1539 this_max_fine_rule INT;
1540 rcd config.rule_circ_duration%ROWTYPE;
1541 rrf config.rule_recurring_fine%ROWTYPE;
1542 rmf config.rule_max_fine%ROWTYPE;
1548 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1550 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1552 -- Fetch the correct rules for this circulation
1559 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1562 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1563 INTO circ_lib, target_copy, usr, is_renewal ;
1565 INTO this_duration_rule,
1568 (matchpoint).duration_rule,
1569 (matchpoint).recurring_fine_rule,
1570 (matchpoint).max_fine_rule
1571 FROM action.find_circ_matrix_matchpoint(
1577 SELECT INTO rcd * FROM config.rule_circ_duration
1578 WHERE id = this_duration_rule;
1579 SELECT INTO rrf * FROM config.rule_recurring_fine
1580 WHERE id = this_fine_rule;
1581 SELECT INTO rmf * FROM config.rule_max_fine
1582 WHERE id = this_max_fine_rule;
1584 -- Apply the rules to this circulation
1585 EXECUTE ('UPDATE ' || tablename || ' c
1587 duration_rule = rcd.name,
1588 recurring_fine_rule = rrf.name,
1589 max_fine_rule = rmf.name,
1590 duration = rcd.normal,
1591 recurring_fine = rrf.normal,
1594 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1597 renewal_remaining = rcd.max_renewals,
1598 grace_period = rrf.grace_period
1600 config.rule_circ_duration rcd,
1601 config.rule_recurring_fine rrf,
1602 config.rule_max_fine rmf,
1605 rcd.id = ' || this_duration_rule || ' AND
1606 rrf.id = ' || this_fine_rule || ' AND
1607 rmf.id = ' || this_max_fine_rule || ' AND
1608 ac.id = c.target_copy AND
1609 c.id = ' || circ || ';');
1611 -- Keep track of where we are in the process
1613 IF (n % 100 = 0) THEN
1614 RAISE INFO '%', n || ' of ' || n_circs
1615 || ' (' || (100*n/n_circs) || '%) circs updated.';
1623 $$ LANGUAGE plpgsql;
1628 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1630 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1631 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1633 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1634 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1637 c TEXT := schemaname || '.asset_copy_legacy';
1638 sc TEXT := schemaname || '.asset_stat_cat';
1639 sce TEXT := schemaname || '.asset_stat_cat_entry';
1640 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1646 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1648 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1650 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1651 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1652 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1659 $$ LANGUAGE plpgsql;
1661 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1663 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1664 -- This will assign standing penalties as needed.
1672 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1674 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1676 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1686 $$ LANGUAGE plpgsql;
1689 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1692 INSERT INTO metabib.metarecord (fingerprint, master_record)
1693 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1694 FROM biblio.record_entry b
1696 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)
1697 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1698 ORDER BY b.fingerprint, b.quality DESC;
1699 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1701 FROM biblio.record_entry r
1702 JOIN metabib.metarecord m USING (fingerprint)
1703 WHERE NOT r.deleted;
1706 $$ LANGUAGE plpgsql;
1709 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1712 INSERT INTO metabib.metarecord (fingerprint, master_record)
1713 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1714 FROM biblio.record_entry b
1716 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)
1717 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1718 ORDER BY b.fingerprint, b.quality DESC;
1719 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1721 FROM biblio.record_entry r
1722 JOIN metabib.metarecord m USING (fingerprint)
1724 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);
1727 $$ LANGUAGE plpgsql;
1730 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1732 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1733 -- Then SELECT migration_tools.create_cards('m_foo');
1736 u TEXT := schemaname || '.actor_usr_legacy';
1737 c TEXT := schemaname || '.actor_card';
1741 EXECUTE ('DELETE FROM ' || c || ';');
1742 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1743 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1749 $$ LANGUAGE plpgsql;
1752 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1754 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1756 my ($marcxml, $shortname) = @_;
1759 use MARC::File::XML;
1764 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1766 foreach my $field ( $marc->field('856') ) {
1767 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1768 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1769 $field->add_subfields( '9' => $shortname );
1770 $field->update( ind2 => '0');
1774 $xml = $marc->as_xml_record;
1775 $xml =~ s/^<\?.+?\?>$//mo;
1777 $xml =~ s/>\s+</></sgo;
1782 $$ LANGUAGE PLPERLU STABLE;
1784 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1786 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1788 my ($marcxml, $shortname) = @_;
1791 use MARC::File::XML;
1796 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1798 foreach my $field ( $marc->field('856') ) {
1799 if ( ! $field->as_string('9') ) {
1800 $field->add_subfields( '9' => $shortname );
1804 $xml = $marc->as_xml_record;
1805 $xml =~ s/^<\?.+?\?>$//mo;
1807 $xml =~ s/>\s+</></sgo;
1812 $$ LANGUAGE PLPERLU STABLE;
1815 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1827 -- Bail out if asked to change the label to ##URI##
1828 IF new_label = '##URI##' THEN
1832 -- Gather information
1833 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1834 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1835 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1837 -- Bail out if the label already is ##URI##
1838 IF old_label = '##URI##' THEN
1842 -- Bail out if the call number label is already correct
1843 IF new_volume = old_volume THEN
1847 -- Check whether we already have a destination volume available
1848 SELECT id INTO new_volume FROM asset.call_number
1851 owning_lib = owner AND
1852 label = new_label AND
1855 -- Create destination volume if needed
1857 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1858 VALUES (1, 1, bib, owner, new_label, cn_class);
1859 SELECT id INTO new_volume FROM asset.call_number
1862 owning_lib = owner AND
1863 label = new_label AND
1867 -- Move copy to destination
1868 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1870 -- Delete source volume if it is now empty
1871 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1873 DELETE FROM asset.call_number WHERE id = old_volume;
1878 $$ LANGUAGE plpgsql;
1880 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1885 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1889 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1890 $zipdata{$zip} = [$city, $state, $county];
1893 if (defined $zipdata{$input}) {
1894 my ($city, $state, $county) = @{$zipdata{$input}};
1895 return [$city, $state, $county];
1896 } elsif (defined $zipdata{substr $input, 0, 5}) {
1897 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1898 return [$city, $state, $county];
1900 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1903 $$ LANGUAGE PLPERLU STABLE;
1905 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1912 errors_found BOOLEAN;
1914 parent_shortname TEXT;
1920 type_parent_depth INT;
1925 errors_found := FALSE;
1927 -- Checking actor.org_unit_type
1929 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1931 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1932 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1934 IF type_parent IS NOT NULL THEN
1936 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1938 IF type_depth - type_parent_depth <> 1 THEN
1939 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1940 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1941 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1942 ou_type_name, type_depth, parent_type, type_parent_depth;
1943 errors_found := TRUE;
1951 -- Checking actor.org_unit
1953 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1955 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1956 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;
1957 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;
1958 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1959 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1960 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;
1961 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;
1963 IF ou_parent IS NOT NULL THEN
1965 IF (org_unit_depth - parent_depth <> 1) OR (
1966 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1968 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1969 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1970 errors_found := TRUE;
1977 IF NOT errors_found THEN
1978 RAISE INFO 'No errors found.';
1985 $$ LANGUAGE plpgsql;
1988 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1992 DELETE FROM asset.opac_visible_copies;
1994 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1996 cp.id, cp.circ_lib, cn.record
1999 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2000 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2001 JOIN asset.copy_location cl ON (cp.location = cl.id)
2002 JOIN config.copy_status cs ON (cp.status = cs.id)
2003 JOIN biblio.record_entry b ON (cn.record = b.id)
2012 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2016 $$ LANGUAGE plpgsql;
2019 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2025 old_owning_lib INTEGER;
2031 -- Gather information
2032 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2033 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2034 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2036 -- Bail out if the new_owning_lib is not the ID of an org_unit
2037 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2039 '% is not a valid actor.org_unit ID; no change made.',
2044 -- Bail out discreetly if the owning_lib is already correct
2045 IF new_owning_lib = old_owning_lib THEN
2049 -- Check whether we already have a destination volume available
2050 SELECT id INTO new_volume FROM asset.call_number
2053 owning_lib = new_owning_lib AND
2054 label = old_label AND
2057 -- Create destination volume if needed
2059 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2060 VALUES (1, 1, bib, new_owning_lib, old_label);
2061 SELECT id INTO new_volume FROM asset.call_number
2064 owning_lib = new_owning_lib AND
2065 label = old_label AND
2069 -- Move copy to destination
2070 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2072 -- Delete source volume if it is now empty
2073 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2075 DELETE FROM asset.call_number WHERE id = old_volume;
2080 $$ LANGUAGE plpgsql;
2083 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2085 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2088 new_owning_lib INTEGER;
2092 -- Parse the new_owner as an org unit ID or shortname
2093 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2094 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2095 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2096 ELSIF new_owner ~ E'^[0-9]+$' THEN
2097 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2100 E'You don\'t need to put the actor.org_unit ID in quotes; '
2101 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2102 new_owning_lib := new_owner::INTEGER;
2103 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2107 '% is not a valid actor.org_unit shortname or ID; no change made.',
2114 $$ LANGUAGE plpgsql;
2116 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2119 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2122 MARC::Charset->assume_unicode(1);
2127 my $r = MARC::Record->new_from_xml( $xml );
2128 my $output_xml = $r->as_xml_record();
2136 $func$ LANGUAGE PLPERLU;
2137 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2139 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2141 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2142 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2143 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2144 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2145 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2146 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2147 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2148 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2149 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2150 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2151 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2152 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2153 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2154 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2155 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2156 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2157 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2158 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2159 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2160 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2161 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2162 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2163 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2164 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2165 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2166 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2168 $FUNC$ LANGUAGE PLPGSQL;
2170 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2172 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2173 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2174 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2175 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2176 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2177 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2178 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2180 -- import any new circ rules
2181 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2182 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2183 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2184 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2186 -- and permission groups
2187 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2190 $FUNC$ LANGUAGE PLPGSQL;
2193 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$
2202 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2203 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2204 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2205 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2206 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2207 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2208 FOR name IN EXECUTE loopq LOOP
2209 EXECUTE existsq INTO ct USING name;
2211 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2212 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2213 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2214 EXECUTE copyst USING name;
2218 $FUNC$ LANGUAGE PLPGSQL;
2220 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2226 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2229 MARC::Charset->assume_unicode(1);
2231 my $target_xml = shift;
2232 my $source_xml = shift;
2238 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2242 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2247 my $source_id = $source->subfield('901', 'c');
2248 $source_id = $source->subfield('903', 'a') unless $source_id;
2249 my $target_id = $target->subfield('901', 'c');
2250 $target_id = $target->subfield('903', 'a') unless $target_id;
2252 my %existing_fields;
2253 foreach my $tag (@$tags) {
2254 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2255 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2256 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2258 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2262 my $xml = $target->as_xml_record;
2263 $xml =~ s/^<\?.+?\?>$//mo;
2265 $xml =~ s/>\s+</></sgo;
2269 $func$ LANGUAGE PLPERLU;
2270 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.';
2272 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2278 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2281 my $in_tags = shift;
2282 my $in_values = shift;
2284 # hack-and-slash parsing of array-passed-as-string;
2285 # this can go away once everybody is running Postgres 9.1+
2286 my $csv = Text::CSV->new({binary => 1});
2289 my $status = $csv->parse($in_tags);
2290 my $tags = [ $csv->fields() ];
2291 $in_values =~ s/^{//;
2292 $in_values =~ s/}$//;
2293 $status = $csv->parse($in_values);
2294 my $values = [ $csv->fields() ];
2296 my $marc = MARC::Record->new();
2298 $marc->leader('00000nam a22000007 4500');
2299 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2301 foreach my $i (0..$#$tags) {
2303 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2306 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2307 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2309 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2313 my $xml = $marc->as_xml_record;
2314 $xml =~ s/^<\?.+?\?>$//mo;
2316 $xml =~ s/>\s+</></sgo;
2320 $func$ LANGUAGE PLPERLU;
2321 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2322 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2323 The second argument is an array of text containing the values to plug into each field.
2324 If the value for a given field is NULL or the empty string, it is not inserted.
2327 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2329 my ($marcxml, $tag, $pos, $value) = @_;
2332 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2336 MARC::Charset->assume_unicode(1);
2338 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2339 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2340 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2341 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2345 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2347 foreach my $field ($marc->field($tag)) {
2348 $field->update("ind$pos" => $value);
2350 $xml = $marc->as_xml_record;
2351 $xml =~ s/^<\?.+?\?>$//mo;
2353 $xml =~ s/>\s+</></sgo;
2357 $func$ LANGUAGE PLPERLU;
2359 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2360 The first argument is a MARCXML string.
2361 The second argument is a MARC tag.
2362 The third argument is the indicator position, either 1 or 2.
2363 The fourth argument is the character to set the indicator value to.
2364 All occurences of the specified field will be changed.
2365 The function returns the revised MARCXML string.$$;
2367 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2372 first_name TEXT DEFAULT '',
2373 last_name TEXT DEFAULT ''
2374 ) RETURNS VOID AS $func$
2376 RAISE NOTICE '%', org ;
2377 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2378 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2379 FROM actor.org_unit aou, permission.grp_tree pgt
2380 WHERE aou.shortname = org
2381 AND pgt.name = perm_group;
2386 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2387 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2389 target_event_def ALIAS FOR $1;
2392 DROP TABLE IF EXISTS new_atevdefs;
2393 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2394 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2395 INSERT INTO action_trigger.event_definition (
2416 ,name || ' (clone of '||target_event_def||')'
2432 action_trigger.event_definition
2434 id = target_event_def
2436 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2437 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2438 INSERT INTO action_trigger.environment (
2444 currval('action_trigger.event_definition_id_seq')
2449 action_trigger.environment
2451 event_def = target_event_def
2453 INSERT INTO action_trigger.event_params (
2458 currval('action_trigger.event_definition_id_seq')
2462 action_trigger.event_params
2464 event_def = target_event_def
2467 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);
2469 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2471 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2472 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2474 target_event_def ALIAS FOR $1;
2476 new_interval ALIAS FOR $3;
2478 DROP TABLE IF EXISTS new_atevdefs;
2479 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2480 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2481 INSERT INTO action_trigger.event_definition (
2502 ,name || ' (clone of '||target_event_def||')'
2518 action_trigger.event_definition
2520 id = target_event_def
2522 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2523 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2524 INSERT INTO action_trigger.environment (
2530 currval('action_trigger.event_definition_id_seq')
2535 action_trigger.environment
2537 event_def = target_event_def
2539 INSERT INTO action_trigger.event_params (
2544 currval('action_trigger.event_definition_id_seq')
2548 action_trigger.event_params
2550 event_def = target_event_def
2553 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);
2555 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2557 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2561 use MARC::File::XML;
2566 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2567 $field = $marc->leader();
2570 $$ LANGUAGE PLPERLU STABLE;
2572 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2573 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2576 use MARC::File::XML;
2581 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2582 $field = $marc->field($tag);
2584 return $field->as_string($subfield,$delimiter);
2585 $$ LANGUAGE PLPERLU STABLE;
2587 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2588 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2591 use MARC::File::XML;
2596 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2597 @fields = $marc->field($tag);
2600 foreach my $field (@fields) {
2601 push @texts, $field->as_string($subfield,$delimiter);
2604 $$ LANGUAGE PLPERLU STABLE;
2606 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2607 SELECT action.find_hold_matrix_matchpoint(
2608 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2609 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2610 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2611 (SELECT usr FROM action.hold_request WHERE id = $1),
2612 (SELECT requestor FROM action.hold_request WHERE id = $1)
2616 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2617 SELECT action.hold_request_permit_test(
2618 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2619 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2620 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2621 (SELECT usr FROM action.hold_request WHERE id = $1),
2622 (SELECT requestor FROM action.hold_request WHERE id = $1)
2626 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2627 SELECT action.find_circ_matrix_matchpoint(
2628 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2629 (SELECT target_copy FROM action.circulation WHERE id = $1),
2630 (SELECT usr FROM action.circulation WHERE id = $1),
2632 NULLIF(phone_renewal,false),
2633 NULLIF(desk_renewal,false),
2634 NULLIF(opac_renewal,false),
2636 ) FROM action.circulation WHERE id = $1
2641 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2646 RAISE EXCEPTION 'assertion';
2649 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2651 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2657 RAISE EXCEPTION '%', msg;
2660 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2662 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2665 fail_msg ALIAS FOR $2;
2666 success_msg ALIAS FOR $3;
2669 RAISE EXCEPTION '%', fail_msg;
2673 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2675 -- push bib sequence and return starting value for reserved range
2676 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2678 bib_count ALIAS FOR $1;
2681 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2683 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2688 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2690 -- set a new salted password
2692 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2694 usr_id ALIAS FOR $1;
2695 plain_passwd ALIAS FOR $2;
2700 SELECT actor.create_salt('main') INTO plain_salt;
2702 SELECT MD5(plain_passwd) INTO md5_passwd;
2704 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2709 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2712 -- convenience functions for handling copy_location maps
2714 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2716 table_schema ALIAS FOR $1;
2717 table_name ALIAS FOR $2;
2718 org_shortname ALIAS FOR $3;
2719 org_range ALIAS FOR $4;
2725 EXECUTE 'SELECT EXISTS (
2727 FROM information_schema.columns
2728 WHERE table_schema = $1
2730 and column_name = ''desired_shelf''
2731 )' INTO proceed USING table_schema, table_name;
2733 RAISE EXCEPTION 'Missing column desired_shelf';
2736 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2738 RAISE EXCEPTION 'Cannot find org by shortname';
2741 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2743 EXECUTE 'ALTER TABLE '
2744 || quote_ident(table_name)
2745 || ' DROP COLUMN IF EXISTS x_shelf';
2746 EXECUTE 'ALTER TABLE '
2747 || quote_ident(table_name)
2748 || ' ADD COLUMN x_shelf INTEGER';
2750 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2751 || ' SET x_shelf = id FROM asset_copy_location b'
2752 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2753 || ' AND b.owning_lib = $1'
2754 || ' AND NOT b.deleted'
2757 FOREACH o IN ARRAY org_list LOOP
2758 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2759 || ' SET x_shelf = id FROM asset.copy_location b'
2760 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
2761 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
2762 || ' AND NOT b.deleted'
2766 EXECUTE 'SELECT migration_tools.assert(
2767 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
2768 ''Cannot find a desired location'',
2769 ''Found all desired locations''
2773 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2775 -- convenience functions for handling circmod maps
2777 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
2779 table_schema ALIAS FOR $1;
2780 table_name ALIAS FOR $2;
2783 EXECUTE 'SELECT EXISTS (
2785 FROM information_schema.columns
2786 WHERE table_schema = $1
2788 and column_name = ''desired_circmod''
2789 )' INTO proceed USING table_schema, table_name;
2791 RAISE EXCEPTION 'Missing column desired_circmod';
2794 EXECUTE 'ALTER TABLE '
2795 || quote_ident(table_name)
2796 || ' DROP COLUMN IF EXISTS x_circmod';
2797 EXECUTE 'ALTER TABLE '
2798 || quote_ident(table_name)
2799 || ' ADD COLUMN x_circmod TEXT';
2801 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2802 || ' SET x_circmod = code FROM config.circ_modifier b'
2803 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
2805 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2806 || ' SET x_circmod = code FROM config.circ_modifier b'
2807 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
2808 || ' AND x_circmod IS NULL';
2810 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2811 || ' SET x_circmod = code FROM config.circ_modifier b'
2812 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
2813 || ' AND x_circmod IS NULL';
2815 EXECUTE 'SELECT migration_tools.assert(
2816 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
2817 ''Cannot find a desired circulation modifier'',
2818 ''Found all desired circulation modifiers''
2822 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2824 -- convenience functions for handling item status maps
2826 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
2828 table_schema ALIAS FOR $1;
2829 table_name ALIAS FOR $2;
2832 EXECUTE 'SELECT EXISTS (
2834 FROM information_schema.columns
2835 WHERE table_schema = $1
2837 and column_name = ''desired_status''
2838 )' INTO proceed USING table_schema, table_name;
2840 RAISE EXCEPTION 'Missing column desired_status';
2843 EXECUTE 'ALTER TABLE '
2844 || quote_ident(table_name)
2845 || ' DROP COLUMN IF EXISTS x_status';
2846 EXECUTE 'ALTER TABLE '
2847 || quote_ident(table_name)
2848 || ' ADD COLUMN x_status INTEGER';
2850 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2851 || ' SET x_status = id FROM config.copy_status b'
2852 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2854 EXECUTE 'SELECT migration_tools.assert(
2855 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2856 ''Cannot find a desired copy status'',
2857 ''Found all desired copy statuses''
2861 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2863 -- convenience functions for handling org maps
2865 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2867 table_schema ALIAS FOR $1;
2868 table_name ALIAS FOR $2;
2871 EXECUTE 'SELECT EXISTS (
2873 FROM information_schema.columns
2874 WHERE table_schema = $1
2876 and column_name = ''desired_org''
2877 )' INTO proceed USING table_schema, table_name;
2879 RAISE EXCEPTION 'Missing column desired_org';
2882 EXECUTE 'ALTER TABLE '
2883 || quote_ident(table_name)
2884 || ' DROP COLUMN IF EXISTS x_org';
2885 EXECUTE 'ALTER TABLE '
2886 || quote_ident(table_name)
2887 || ' ADD COLUMN x_org INTEGER';
2889 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2890 || ' SET x_org = id FROM actor.org_unit b'
2891 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2893 EXECUTE 'SELECT migration_tools.assert(
2894 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2895 ''Cannot find a desired org unit'',
2896 ''Found all desired org units''
2900 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2902 -- convenience function for handling desired_not_migrate
2904 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2906 table_schema ALIAS FOR $1;
2907 table_name ALIAS FOR $2;
2910 EXECUTE 'SELECT EXISTS (
2912 FROM information_schema.columns
2913 WHERE table_schema = $1
2915 and column_name = ''desired_not_migrate''
2916 )' INTO proceed USING table_schema, table_name;
2918 RAISE EXCEPTION 'Missing column desired_not_migrate';
2921 EXECUTE 'ALTER TABLE '
2922 || quote_ident(table_name)
2923 || ' DROP COLUMN IF EXISTS x_migrate';
2924 EXECUTE 'ALTER TABLE '
2925 || quote_ident(table_name)
2926 || ' ADD COLUMN x_migrate BOOLEAN';
2928 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2929 || ' SET x_migrate = CASE'
2930 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2931 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2932 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2935 EXECUTE 'SELECT migration_tools.assert(
2936 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
2937 ''Not all desired_not_migrate values understood'',
2938 ''All desired_not_migrate values understood''
2942 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2944 -- convenience function for handling desired_profile
2946 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
2948 table_schema ALIAS FOR $1;
2949 table_name ALIAS FOR $2;
2952 EXECUTE 'SELECT EXISTS (
2954 FROM information_schema.columns
2955 WHERE table_schema = $1
2957 and column_name = ''desired_profile''
2958 )' INTO proceed USING table_schema, table_name;
2960 RAISE EXCEPTION 'Missing column desired_profile';
2963 EXECUTE 'ALTER TABLE '
2964 || quote_ident(table_name)
2965 || ' DROP COLUMN IF EXISTS x_profile';
2966 EXECUTE 'ALTER TABLE '
2967 || quote_ident(table_name)
2968 || ' ADD COLUMN x_profile INTEGER';
2970 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2971 || ' SET x_profile = id FROM permission.grp_tree b'
2972 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
2974 EXECUTE 'SELECT migration_tools.assert(
2975 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
2976 ''Cannot find a desired profile'',
2977 ''Found all desired profiles''
2981 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2983 -- convenience function for handling desired actor stat cats
2985 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2987 table_schema ALIAS FOR $1;
2988 table_name ALIAS FOR $2;
2989 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2990 org_shortname ALIAS FOR $4;
2998 SELECT 'desired_sc' || field_suffix INTO sc;
2999 SELECT 'desired_sce' || field_suffix INTO sce;
3001 EXECUTE 'SELECT EXISTS (
3003 FROM information_schema.columns
3004 WHERE table_schema = $1
3006 and column_name = $3
3007 )' INTO proceed USING table_schema, table_name, sc;
3009 RAISE EXCEPTION 'Missing column %', sc;
3011 EXECUTE 'SELECT EXISTS (
3013 FROM information_schema.columns
3014 WHERE table_schema = $1
3016 and column_name = $3
3017 )' INTO proceed USING table_schema, table_name, sce;
3019 RAISE EXCEPTION 'Missing column %', sce;
3022 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3024 RAISE EXCEPTION 'Cannot find org by shortname';
3026 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3028 -- caller responsible for their own truncates though we try to prevent duplicates
3029 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3034 ' || quote_ident(table_name) || '
3036 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3040 WHERE owner = ANY ($2)
3041 AND name = BTRIM('||sc||')
3046 WHERE owner = ANY ($2)
3047 AND name = BTRIM('||sc||')
3050 USING org, org_list;
3052 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3057 WHERE owner = ANY ($2)
3058 AND BTRIM('||sc||') = BTRIM(name))
3061 WHERE owner = ANY ($2)
3062 AND BTRIM('||sc||') = BTRIM(name))
3067 ' || quote_ident(table_name) || '
3069 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3070 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3073 FROM actor.stat_cat_entry
3077 WHERE owner = ANY ($2)
3078 AND BTRIM('||sc||') = BTRIM(name)
3079 ) AND value = BTRIM('||sce||')
3083 FROM actor_stat_cat_entry
3087 WHERE owner = ANY ($2)
3088 AND BTRIM('||sc||') = BTRIM(name)
3089 ) AND value = BTRIM('||sce||')
3092 USING org, org_list;
3094 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3096 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3098 table_schema ALIAS FOR $1;
3099 table_name ALIAS FOR $2;
3100 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3101 org_shortname ALIAS FOR $4;
3109 SELECT 'desired_sc' || field_suffix INTO sc;
3110 SELECT 'desired_sce' || field_suffix INTO sce;
3111 EXECUTE 'SELECT EXISTS (
3113 FROM information_schema.columns
3114 WHERE table_schema = $1
3116 and column_name = $3
3117 )' INTO proceed USING table_schema, table_name, sc;
3119 RAISE EXCEPTION 'Missing column %', sc;
3121 EXECUTE 'SELECT EXISTS (
3123 FROM information_schema.columns
3124 WHERE table_schema = $1
3126 and column_name = $3
3127 )' INTO proceed USING table_schema, table_name, sce;
3129 RAISE EXCEPTION 'Missing column %', sce;
3132 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3134 RAISE EXCEPTION 'Cannot find org by shortname';
3137 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3139 EXECUTE 'ALTER TABLE '
3140 || quote_ident(table_name)
3141 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3142 EXECUTE 'ALTER TABLE '
3143 || quote_ident(table_name)
3144 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3145 EXECUTE 'ALTER TABLE '
3146 || quote_ident(table_name)
3147 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3148 EXECUTE 'ALTER TABLE '
3149 || quote_ident(table_name)
3150 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3153 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3155 x_sc' || field_suffix || ' = id
3157 (SELECT id, name, owner FROM actor_stat_cat
3158 UNION SELECT id, name, owner FROM actor.stat_cat) u
3160 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3161 AND u.owner = ANY ($1);'
3164 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3166 x_sce' || field_suffix || ' = id
3168 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
3169 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
3171 u.stat_cat = x_sc' || field_suffix || '
3172 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3173 AND u.owner = ANY ($1);'
3176 EXECUTE 'SELECT migration_tools.assert(
3177 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3178 ''Cannot find a desired stat cat'',
3179 ''Found all desired stat cats''
3182 EXECUTE 'SELECT migration_tools.assert(
3183 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3184 ''Cannot find a desired stat cat entry'',
3185 ''Found all desired stat cat entries''
3189 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3191 -- convenience functions for adding shelving locations
3192 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
3193 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3199 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3202 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3203 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3204 IF return_id IS NOT NULL THEN
3212 $$ LANGUAGE plpgsql;
3214 -- may remove later but testing using this with new migration scripts and not loading acls until go live
3216 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
3217 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
3223 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
3226 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
3228 SELECT INTO return_id id FROM
3229 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
3230 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
3231 IF return_id IS NOT NULL THEN
3239 $$ LANGUAGE plpgsql;
3241 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
3242 DROP FUNCTION IF EXISTS add_sf9(TEXT,TEXT,TEXT);
3243 CREATE OR REPLACE FUNCTION add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
3251 use MARC::File::XML (BinaryEncoding => 'utf8');
3253 binmode(STDERR, ':bytes');
3254 binmode(STDOUT, ':utf8');
3255 binmode(STDERR, ':utf8');
3257 my $marc_xml = shift;
3258 my $matching_u_text = shift;
3259 my $new_9_to_set = shift;
3261 $marc_xml =~ s/(<leader>.........)./${1}a/;
3264 $marc_xml = MARC::Record->new_from_xml($marc_xml);
3267 #elog("could not parse $bibid: $@\n");
3268 import MARC::File::XML (BinaryEncoding => 'utf8');
3272 my @uris = $marc_xml->field('856');
3273 return unless @uris;
3275 foreach my $field (@uris) {
3276 my $sfu = $field->subfield('u');
3277 my $ind2 = $field->indicator('2');
3278 if (!defined $ind2) { next; }
3279 if ($ind2 ne '0') { next; }
3280 if (!defined $sfu) { next; }
3281 if ($sfu =~ m/$matching_u_text/) {
3282 $field->add_subfields( '9' => $new_9_to_set );
3287 return $marc_xml->as_xml_record();
3291 DROP FUNCTION IF EXISTS add_sf9(INTEGER, TEXT, TEXT, TEXT);
3292 CREATE OR REPLACE FUNCTION add_sf9(bib_id INTEGER, target_u_text TEXT, sf9_text TEXT, bib_table TEXT)
3301 SELECT marc FROM bib_table WHERE id = bib_id INTO source_xml;
3303 SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
3307 IF new_xml != source_xml THEN
3308 UPDATE bib_table SET marc = new_xml WHERE id = bib_id;
3315 $BODY$ LANGUAGE plpgsql;
3317 -- convenience function for linking to the item staging table
3319 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3321 table_schema ALIAS FOR $1;
3322 table_name ALIAS FOR $2;
3323 foreign_column_name ALIAS FOR $3;
3324 main_column_name ALIAS FOR $4;
3325 btrim_desired ALIAS FOR $5;
3328 EXECUTE 'SELECT EXISTS (
3330 FROM information_schema.columns
3331 WHERE table_schema = $1
3333 and column_name = $3
3334 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3336 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3339 EXECUTE 'SELECT EXISTS (
3341 FROM information_schema.columns
3342 WHERE table_schema = $1
3343 AND table_name = ''asset_copy_legacy''
3344 and column_name = $2
3345 )' INTO proceed USING table_schema, main_column_name;
3347 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
3350 EXECUTE 'ALTER TABLE '
3351 || quote_ident(table_name)
3352 || ' DROP COLUMN IF EXISTS x_item';
3353 EXECUTE 'ALTER TABLE '
3354 || quote_ident(table_name)
3355 || ' ADD COLUMN x_item BIGINT';
3357 IF btrim_desired THEN
3358 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3359 || ' SET x_item = id FROM asset_copy_legacy b'
3360 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3361 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3363 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3364 || ' SET x_item = id FROM asset_copy_legacy b'
3365 || ' WHERE a.' || quote_ident(foreign_column_name)
3366 || ' = b.' || quote_ident(main_column_name);
3369 --EXECUTE 'SELECT migration_tools.assert(
3370 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
3371 -- ''Cannot link every barcode'',
3372 -- ''Every barcode linked''
3376 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3378 -- convenience function for linking to the user staging table
3380 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3382 table_schema ALIAS FOR $1;
3383 table_name ALIAS FOR $2;
3384 foreign_column_name ALIAS FOR $3;
3385 main_column_name ALIAS FOR $4;
3386 btrim_desired ALIAS FOR $5;
3389 EXECUTE 'SELECT EXISTS (
3391 FROM information_schema.columns
3392 WHERE table_schema = $1
3394 and column_name = $3
3395 )' INTO proceed USING table_schema, table_name, foreign_column_name;
3397 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
3400 EXECUTE 'SELECT EXISTS (
3402 FROM information_schema.columns
3403 WHERE table_schema = $1
3404 AND table_name = ''actor_usr_legacy''
3405 and column_name = $2
3406 )' INTO proceed USING table_schema, main_column_name;
3408 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
3411 EXECUTE 'ALTER TABLE '
3412 || quote_ident(table_name)
3413 || ' DROP COLUMN IF EXISTS x_user';
3414 EXECUTE 'ALTER TABLE '
3415 || quote_ident(table_name)
3416 || ' ADD COLUMN x_user INTEGER';
3418 IF btrim_desired THEN
3419 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3420 || ' SET x_user = id FROM actor_usr_legacy b'
3421 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
3422 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
3424 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3425 || ' SET x_user = id FROM actor_usr_legacy b'
3426 || ' WHERE a.' || quote_ident(foreign_column_name)
3427 || ' = b.' || quote_ident(main_column_name);
3430 --EXECUTE 'SELECT migration_tools.assert(
3431 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
3432 -- ''Cannot link every barcode'',
3433 -- ''Every barcode linked''
3437 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3439 -- convenience function for linking two tables
3440 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
3441 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3443 table_schema ALIAS FOR $1;
3444 table_a ALIAS FOR $2;
3445 column_a ALIAS FOR $3;
3446 table_b ALIAS FOR $4;
3447 column_b ALIAS FOR $5;
3448 column_x ALIAS FOR $6;
3449 btrim_desired ALIAS FOR $7;
3452 EXECUTE 'SELECT EXISTS (
3454 FROM information_schema.columns
3455 WHERE table_schema = $1
3457 and column_name = $3
3458 )' INTO proceed USING table_schema, table_a, column_a;
3460 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3463 EXECUTE 'SELECT EXISTS (
3465 FROM information_schema.columns
3466 WHERE table_schema = $1
3468 and column_name = $3
3469 )' INTO proceed USING table_schema, table_b, column_b;
3471 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3474 EXECUTE 'ALTER TABLE '
3475 || quote_ident(table_b)
3476 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3477 EXECUTE 'ALTER TABLE '
3478 || quote_ident(table_b)
3479 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
3481 IF btrim_desired THEN
3482 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3483 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3484 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3485 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3487 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3488 || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
3489 || ' WHERE a.' || quote_ident(column_a)
3490 || ' = b.' || quote_ident(column_b);
3494 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3496 -- convenience function for linking two tables, but copying column w into column x instead of "id"
3497 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
3498 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
3500 table_schema ALIAS FOR $1;
3501 table_a ALIAS FOR $2;
3502 column_a ALIAS FOR $3;
3503 table_b ALIAS FOR $4;
3504 column_b ALIAS FOR $5;
3505 column_w ALIAS FOR $6;
3506 column_x ALIAS FOR $7;
3507 btrim_desired ALIAS FOR $8;
3510 EXECUTE 'SELECT EXISTS (
3512 FROM information_schema.columns
3513 WHERE table_schema = $1
3515 and column_name = $3
3516 )' INTO proceed USING table_schema, table_a, column_a;
3518 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3521 EXECUTE 'SELECT EXISTS (
3523 FROM information_schema.columns
3524 WHERE table_schema = $1
3526 and column_name = $3
3527 )' INTO proceed USING table_schema, table_b, column_b;
3529 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3532 EXECUTE 'ALTER TABLE '
3533 || quote_ident(table_b)
3534 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
3535 EXECUTE 'ALTER TABLE '
3536 || quote_ident(table_b)
3537 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
3539 IF btrim_desired THEN
3540 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3541 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3542 || ' WHERE BTRIM(a.' || quote_ident(column_a)
3543 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
3545 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3546 || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3547 || ' WHERE a.' || quote_ident(column_a)
3548 || ' = b.' || quote_ident(column_b);
3552 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3554 -- convenience function for handling desired asset stat cats
3556 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3558 table_schema ALIAS FOR $1;
3559 table_name ALIAS FOR $2;
3560 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3561 org_shortname ALIAS FOR $4;
3569 SELECT 'desired_sc' || field_suffix INTO sc;
3570 SELECT 'desired_sce' || field_suffix INTO sce;
3572 EXECUTE 'SELECT EXISTS (
3574 FROM information_schema.columns
3575 WHERE table_schema = $1
3577 and column_name = $3
3578 )' INTO proceed USING table_schema, table_name, sc;
3580 RAISE EXCEPTION 'Missing column %', sc;
3582 EXECUTE 'SELECT EXISTS (
3584 FROM information_schema.columns
3585 WHERE table_schema = $1
3587 and column_name = $3
3588 )' INTO proceed USING table_schema, table_name, sce;
3590 RAISE EXCEPTION 'Missing column %', sce;
3593 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3595 RAISE EXCEPTION 'Cannot find org by shortname';
3597 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3599 -- caller responsible for their own truncates though we try to prevent duplicates
3600 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3605 ' || quote_ident(table_name) || '
3607 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3611 WHERE owner = ANY ($2)
3612 AND name = BTRIM('||sc||')
3617 WHERE owner = ANY ($2)
3618 AND name = BTRIM('||sc||')
3621 USING org, org_list;
3623 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3628 WHERE owner = ANY ($2)
3629 AND BTRIM('||sc||') = BTRIM(name))
3632 WHERE owner = ANY ($2)
3633 AND BTRIM('||sc||') = BTRIM(name))
3638 ' || quote_ident(table_name) || '
3640 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3641 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3644 FROM asset.stat_cat_entry
3648 WHERE owner = ANY ($2)
3649 AND BTRIM('||sc||') = BTRIM(name)
3650 ) AND value = BTRIM('||sce||')
3654 FROM asset_stat_cat_entry
3658 WHERE owner = ANY ($2)
3659 AND BTRIM('||sc||') = BTRIM(name)
3660 ) AND value = BTRIM('||sce||')
3663 USING org, org_list;
3665 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3667 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3669 table_schema ALIAS FOR $1;
3670 table_name ALIAS FOR $2;
3671 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3672 org_shortname ALIAS FOR $4;
3680 SELECT 'desired_sc' || field_suffix INTO sc;
3681 SELECT 'desired_sce' || field_suffix INTO sce;
3682 EXECUTE 'SELECT EXISTS (
3684 FROM information_schema.columns
3685 WHERE table_schema = $1
3687 and column_name = $3
3688 )' INTO proceed USING table_schema, table_name, sc;
3690 RAISE EXCEPTION 'Missing column %', sc;
3692 EXECUTE 'SELECT EXISTS (
3694 FROM information_schema.columns
3695 WHERE table_schema = $1
3697 and column_name = $3
3698 )' INTO proceed USING table_schema, table_name, sce;
3700 RAISE EXCEPTION 'Missing column %', sce;
3703 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3705 RAISE EXCEPTION 'Cannot find org by shortname';
3708 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3710 EXECUTE 'ALTER TABLE '
3711 || quote_ident(table_name)
3712 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3713 EXECUTE 'ALTER TABLE '
3714 || quote_ident(table_name)
3715 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3716 EXECUTE 'ALTER TABLE '
3717 || quote_ident(table_name)
3718 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3719 EXECUTE 'ALTER TABLE '
3720 || quote_ident(table_name)
3721 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3724 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3726 x_sc' || field_suffix || ' = id
3728 (SELECT id, name, owner FROM asset_stat_cat
3729 UNION SELECT id, name, owner FROM asset.stat_cat) u
3731 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3732 AND u.owner = ANY ($1);'
3735 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3737 x_sce' || field_suffix || ' = id
3739 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3740 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3742 u.stat_cat = x_sc' || field_suffix || '
3743 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3744 AND u.owner = ANY ($1);'
3747 EXECUTE 'SELECT migration_tools.assert(
3748 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3749 ''Cannot find a desired stat cat'',
3750 ''Found all desired stat cats''
3753 EXECUTE 'SELECT migration_tools.assert(
3754 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3755 ''Cannot find a desired stat cat entry'',
3756 ''Found all desired stat cat entries''
3760 $$ LANGUAGE PLPGSQL STRICT VOLATILE;