1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 production_tables TEXT[];
269 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
270 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
271 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
272 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
279 migration_schema ALIAS FOR $1;
280 production_table ALIAS FOR $2;
281 base_staging_table TEXT;
284 base_staging_table = REPLACE( production_table, '.', '_' );
285 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
286 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
301 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
302 IF temp ilike '%MR.%' THEN
304 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
306 IF temp ilike '%MRS.%' THEN
308 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
310 IF temp ilike '%MS.%' THEN
312 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
314 IF temp ilike '%DR.%' THEN
316 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
318 IF temp ilike '%JR%' THEN
320 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
322 IF temp ilike '%JR,%' THEN
324 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
326 IF temp ilike '%SR%' THEN
328 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
330 IF temp ilike '%SR,%' THEN
332 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
334 IF temp ~ E'\\sII$' THEN
336 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
338 IF temp ~ E'\\sIII$' THEN
340 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
342 IF temp ~ E'\\sIV$' THEN
344 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
347 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
348 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
349 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
351 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
353 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
355 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
357 full_name TEXT := $1;
359 family_name TEXT := '';
360 first_given_name TEXT := '';
361 second_given_name TEXT := '';
366 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
367 IF temp ilike '%MR.%' THEN
369 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
371 IF temp ilike '%MRS.%' THEN
373 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
375 IF temp ilike '%MS.%' THEN
377 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
379 IF temp ilike '%DR.%' THEN
381 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
383 IF temp ilike '%JR.%' THEN
385 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
387 IF temp ilike '%JR,%' THEN
389 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
391 IF temp ilike '%SR.%' THEN
393 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
395 IF temp ilike '%SR,%' THEN
397 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
399 IF temp like '%III%' THEN
401 temp := REGEXP_REPLACE( temp, E'III', '' );
403 IF temp like '%II%' THEN
405 temp := REGEXP_REPLACE( temp, E'II', '' );
407 IF temp like '%IV%' THEN
409 temp := REGEXP_REPLACE( temp, E'IV', '' );
412 temp := REGEXP_REPLACE( temp, '\(\)', '');
413 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
414 family_name := REGEXP_REPLACE( family_name, ',', '' );
415 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
416 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
417 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
418 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
420 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
422 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
424 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
426 full_name TEXT := $1;
428 family_name TEXT := '';
429 first_given_name TEXT := '';
430 second_given_name TEXT := '';
435 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
436 IF temp ilike '%MR.%' THEN
438 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
440 IF temp ilike '%MRS.%' THEN
442 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
444 IF temp ilike '%MS.%' THEN
446 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
448 IF temp ilike '%DR.%' THEN
450 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
452 IF temp ilike '%JR.%' THEN
454 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
456 IF temp ilike '%JR,%' THEN
458 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
460 IF temp ilike '%SR.%' THEN
462 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
464 IF temp ilike '%SR,%' THEN
466 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
468 IF temp like '%III%' THEN
470 temp := REGEXP_REPLACE( temp, E'III', '' );
472 IF temp like '%II%' THEN
474 temp := REGEXP_REPLACE( temp, E'II', '' );
476 IF temp like '%IV%' THEN
478 temp := REGEXP_REPLACE( temp, E'IV', '' );
483 family_name = BTRIM(REGEXP_REPLACE(temp,E'^(.*?,).*$',E'\\1'));
484 temp := REPLACE( temp, family_name, '' );
485 family_name := REPLACE( family_name, ',', '' );
486 first_given_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*?$',E'\\1') );
487 temp := REPLACE( temp, first_given_name, '' );
488 second_given_name := BTRIM(temp);
490 first_given_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*?$',E'\\1') );
491 temp := REPLACE( temp, first_given_name, '' );
492 family_name := BTRIM( REGEXP_REPLACE(temp,E'^.*?(\\S+)$',E'\\1') );
493 temp := REPLACE( temp, family_name, '' );
494 second_given_name := BTRIM(temp);
497 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
499 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
501 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
503 city_state_zip TEXT := $1;
508 zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
509 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
510 IF city_state_zip ~ ',' THEN
511 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
512 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
514 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
515 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
516 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
518 IF city_state_zip ~ E'^\\S+$' THEN
519 city := city_state_zip;
522 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
523 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
527 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
529 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
531 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
532 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
534 fullstring TEXT := $1;
544 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
545 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
548 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
550 IF fullstring ~ ',' THEN
551 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
552 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
554 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
555 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
556 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
558 IF fullstring ~ E'^\\S+$' THEN
559 scratch1 := fullstring;
562 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
563 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
568 IF scratch1 ~ '[\$]' THEN
569 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
570 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
572 IF scratch1 ~ '\s' THEN
573 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
574 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
581 IF scratch2 ~ '^\d' THEN
582 address1 := scratch2;
585 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
586 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
590 TRIM(BOTH ' ' FROM address1)
591 ,TRIM(BOTH ' ' FROM address2)
592 ,TRIM(BOTH ' ' FROM city)
593 ,TRIM(BOTH ' ' FROM state)
594 ,TRIM(BOTH ' ' FROM zip)
597 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
599 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
603 IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
604 IF o::BIGINT < t THEN
611 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
613 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
615 migration_schema ALIAS FOR $1;
619 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
624 $$ LANGUAGE PLPGSQL STRICT STABLE;
626 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
628 migration_schema ALIAS FOR $1;
632 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
637 $$ LANGUAGE PLPGSQL STRICT STABLE;
639 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
641 migration_schema ALIAS FOR $1;
645 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
650 $$ LANGUAGE PLPGSQL STRICT STABLE;
652 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
654 migration_schema ALIAS FOR $1;
658 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
663 $$ LANGUAGE PLPGSQL STRICT STABLE;
665 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
667 migration_schema ALIAS FOR $1;
669 patron_table ALIAS FOR $2;
670 default_patron_profile ALIAS FOR $3;
673 sql_where1 TEXT := '';
674 sql_where2 TEXT := '';
675 sql_where3 TEXT := '';
678 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
680 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
682 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
683 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
684 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
685 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
686 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
687 --RAISE INFO 'sql = %', sql;
688 PERFORM migration_tools.exec( $1, sql );
690 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
692 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
694 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
697 $$ LANGUAGE PLPGSQL STRICT STABLE;
699 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
701 migration_schema ALIAS FOR $1;
703 item_table ALIAS FOR $2;
706 sql_where1 TEXT := '';
707 sql_where2 TEXT := '';
708 sql_where3 TEXT := '';
711 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
713 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
715 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
716 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
717 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
718 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
719 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
720 --RAISE INFO 'sql = %', sql;
721 PERFORM migration_tools.exec( $1, sql );
724 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
726 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
729 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
731 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
733 migration_schema ALIAS FOR $1;
734 base_copy_location_map TEXT;
735 item_table ALIAS FOR $2;
738 sql_where1 TEXT := '';
739 sql_where2 TEXT := '';
740 sql_where3 TEXT := '';
743 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
745 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
747 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
748 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
749 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
750 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
751 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
752 --RAISE INFO 'sql = %', sql;
753 PERFORM migration_tools.exec( $1, sql );
756 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
758 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
761 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
763 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
764 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
766 migration_schema ALIAS FOR $1;
768 circ_table ALIAS FOR $2;
769 item_table ALIAS FOR $3;
770 patron_table ALIAS FOR $4;
773 sql_where1 TEXT := '';
774 sql_where2 TEXT := '';
775 sql_where3 TEXT := '';
776 sql_where4 TEXT := '';
779 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
781 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
783 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
784 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
785 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
786 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
787 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
788 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
789 --RAISE INFO 'sql = %', sql;
790 PERFORM migration_tools.exec( $1, sql );
793 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
795 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
798 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
801 -- $barcode source barcode
802 -- $prefix prefix to add to barcode, NULL = add no prefix
803 -- $maxlen maximum length of barcode; default to 14 if left NULL
804 -- $pad padding string to apply to left of source barcode before adding
805 -- prefix and suffix; set to NULL or '' if no padding is desired
806 -- $suffix suffix to add to barcode, NULL = add no suffix
808 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
809 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
811 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
812 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
815 return unless defined $barcode;
817 $prefix = '' unless defined $prefix;
819 $pad = '0' unless defined $pad;
820 $suffix = '' unless defined $suffix;
822 # bail out if adding prefix and suffix would bring new barcode over max length
823 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
825 my $new_barcode = $barcode;
827 my $pad_length = $maxlen - length($prefix) - length($suffix);
828 if (length($barcode) < $pad_length) {
829 # assuming we always want padding on the left
830 # also assuming that it is possible to have the pad string be longer than 1 character
831 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
835 # bail out if adding prefix and suffix would bring new barcode over max length
836 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
838 return "$prefix$new_barcode$suffix";
839 $$ LANGUAGE PLPERLU STABLE;
841 -- remove previous version of this function
842 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
844 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
846 attempt_value ALIAS FOR $1;
847 datatype ALIAS FOR $2;
849 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
850 RETURN attempt_value;
852 WHEN OTHERS THEN RETURN NULL;
854 $$ LANGUAGE PLPGSQL STRICT STABLE;
856 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
858 attempt_value ALIAS FOR $1;
859 fail_value ALIAS FOR $2;
863 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
870 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
875 $$ LANGUAGE PLPGSQL STRICT STABLE;
877 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
879 attempt_value ALIAS FOR $1;
880 fail_value ALIAS FOR $2;
884 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
891 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
896 $$ LANGUAGE PLPGSQL STRICT STABLE;
898 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
900 attempt_value ALIAS FOR $1;
901 fail_value ALIAS FOR $2;
905 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
912 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
917 $$ LANGUAGE PLPGSQL STRICT STABLE;
919 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
921 attempt_value ALIAS FOR $1;
922 fail_value ALIAS FOR $2;
925 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
930 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
935 $$ LANGUAGE PLPGSQL STRICT STABLE;
937 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
939 attempt_value ALIAS FOR $1;
940 fail_value ALIAS FOR $2;
944 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
951 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
956 $$ LANGUAGE PLPGSQL STRICT STABLE;
958 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
960 attempt_value ALIAS FOR $1;
961 fail_value ALIAS FOR $2;
965 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
972 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
977 $$ LANGUAGE PLPGSQL STRICT STABLE;
979 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
981 attempt_value ALIAS FOR $1;
982 fail_value ALIAS FOR $2;
985 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
986 RAISE EXCEPTION 'too many digits';
989 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
996 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1001 $$ LANGUAGE PLPGSQL STRICT STABLE;
1003 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1005 attempt_value ALIAS FOR $1;
1006 fail_value ALIAS FOR $2;
1007 output NUMERIC(6,2);
1009 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1010 RAISE EXCEPTION 'too many digits';
1013 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
1020 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1025 $$ LANGUAGE PLPGSQL STRICT STABLE;
1027 -- add_codabar_checkdigit
1028 -- $barcode source barcode
1030 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1031 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1032 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1033 -- input string does not meet those requirements, it is returned unchanged.
1035 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1036 my $barcode = shift;
1038 return $barcode if $barcode !~ /^\d{13,14}$/;
1039 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1040 my @digits = split //, $barcode;
1042 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1043 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1044 my $remainder = $total % 10;
1045 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1046 return $barcode . $checkdigit;
1047 $$ LANGUAGE PLPERLU STRICT STABLE;
1049 -- add_code39mod43_checkdigit
1050 -- $barcode source barcode
1052 -- If the source string is 13 or 14 characters long and contains only valid
1053 -- Code 39 mod 43 characters, adds or replaces the 14th
1054 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1055 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1056 -- input string does not meet those requirements, it is returned unchanged.
1058 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1059 my $barcode = shift;
1061 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1062 $barcode = substr($barcode, 0, 13); # ignore 14th character
1064 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1065 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1068 $total += $nums{$_} foreach split(//, $barcode);
1069 my $remainder = $total % 43;
1070 my $checkdigit = $valid_chars[$remainder];
1071 return $barcode . $checkdigit;
1072 $$ LANGUAGE PLPERLU STRICT STABLE;
1074 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1077 areacode TEXT := $2;
1080 n_digits INTEGER := 0;
1083 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1084 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1085 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1086 IF n_digits = 7 AND areacode <> '' THEN
1087 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1088 output := (areacode || '-' || temp);
1095 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1097 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1098 my ($marcxml, $pos, $value) = @_;
1101 use MARC::File::XML;
1105 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1106 my $leader = $marc->leader();
1107 substr($leader, $pos, 1) = $value;
1108 $marc->leader($leader);
1109 $xml = $marc->as_xml_record;
1110 $xml =~ s/^<\?.+?\?>$//mo;
1112 $xml =~ s/>\s+</></sgo;
1115 $$ LANGUAGE PLPERLU STABLE;
1117 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1118 my ($marcxml, $pos, $value) = @_;
1121 use MARC::File::XML;
1125 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1126 my $f008 = $marc->field('008');
1129 my $field = $f008->data();
1130 substr($field, $pos, 1) = $value;
1131 $f008->update($field);
1132 $xml = $marc->as_xml_record;
1133 $xml =~ s/^<\?.+?\?>$//mo;
1135 $xml =~ s/>\s+</></sgo;
1139 $$ LANGUAGE PLPERLU STABLE;
1142 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1144 profile ALIAS FOR $1;
1146 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1148 $$ LANGUAGE PLPGSQL STRICT STABLE;
1151 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1153 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1155 $$ LANGUAGE PLPGSQL STRICT STABLE;
1158 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1160 my ($marcxml, $tags) = @_;
1163 use MARC::File::XML;
1168 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1169 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1171 my @incumbents = ();
1173 foreach my $field ( $marc->fields() ) {
1174 push @incumbents, $field->as_formatted();
1177 foreach $field ( $to_insert->fields() ) {
1178 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1179 $marc->insert_fields_ordered( ($field) );
1183 $xml = $marc->as_xml_record;
1184 $xml =~ s/^<\?.+?\?>$//mo;
1186 $xml =~ s/>\s+</></sgo;
1191 $$ LANGUAGE PLPERLU STABLE;
1193 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1197 -- First make sure the circ matrix is loaded and the circulations
1198 -- have been staged to the extent possible (but at the very least
1199 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1200 -- circ modifiers must also be in place.
1202 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1210 this_duration_rule INT;
1212 this_max_fine_rule INT;
1213 rcd config.rule_circ_duration%ROWTYPE;
1214 rrf config.rule_recurring_fine%ROWTYPE;
1215 rmf config.rule_max_fine%ROWTYPE;
1222 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1224 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1226 -- Fetch the correct rules for this circulation
1233 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1236 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1237 INTO circ_lib, target_copy, usr, is_renewal ;
1239 INTO this_duration_rule,
1243 recurring_fine_rule,
1245 FROM action.item_user_circ_test(
1251 SELECT INTO rcd * FROM config.rule_circ_duration
1252 WHERE id = this_duration_rule;
1253 SELECT INTO rrf * FROM config.rule_recurring_fine
1254 WHERE id = this_fine_rule;
1255 SELECT INTO rmf * FROM config.rule_max_fine
1256 WHERE id = this_max_fine_rule;
1258 -- Apply the rules to this circulation
1259 EXECUTE ('UPDATE ' || tablename || ' c
1261 duration_rule = rcd.name,
1262 recurring_fine_rule = rrf.name,
1263 max_fine_rule = rmf.name,
1264 duration = rcd.normal,
1265 recurring_fine = rrf.normal,
1268 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1271 renewal_remaining = rcd.max_renewals
1273 config.rule_circ_duration rcd,
1274 config.rule_recurring_fine rrf,
1275 config.rule_max_fine rmf,
1278 rcd.id = ' || this_duration_rule || ' AND
1279 rrf.id = ' || this_fine_rule || ' AND
1280 rmf.id = ' || this_max_fine_rule || ' AND
1281 ac.id = c.target_copy AND
1282 c.id = ' || circ || ';');
1284 -- Keep track of where we are in the process
1286 IF (n % 100 = 0) THEN
1287 RAISE INFO '%', n || ' of ' || n_circs
1288 || ' (' || (100*n/n_circs) || '%) circs updated.';
1296 $$ LANGUAGE plpgsql;
1298 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1302 -- First make sure the circ matrix is loaded and the circulations
1303 -- have been staged to the extent possible (but at the very least
1304 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1305 -- circ modifiers must also be in place.
1307 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1315 this_duration_rule INT;
1317 this_max_fine_rule INT;
1318 rcd config.rule_circ_duration%ROWTYPE;
1319 rrf config.rule_recurring_fine%ROWTYPE;
1320 rmf config.rule_max_fine%ROWTYPE;
1327 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1329 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1331 -- Fetch the correct rules for this circulation
1338 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1341 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1342 INTO circ_lib, target_copy, usr, is_renewal ;
1344 INTO this_duration_rule,
1350 FROM action.find_circ_matrix_matchpoint(
1356 SELECT INTO rcd * FROM config.rule_circ_duration
1357 WHERE id = this_duration_rule;
1358 SELECT INTO rrf * FROM config.rule_recurring_fine
1359 WHERE id = this_fine_rule;
1360 SELECT INTO rmf * FROM config.rule_max_fine
1361 WHERE id = this_max_fine_rule;
1363 -- Apply the rules to this circulation
1364 EXECUTE ('UPDATE ' || tablename || ' c
1366 duration_rule = rcd.name,
1367 recuring_fine_rule = rrf.name,
1368 max_fine_rule = rmf.name,
1369 duration = rcd.normal,
1370 recuring_fine = rrf.normal,
1373 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1376 renewal_remaining = rcd.max_renewals
1378 config.rule_circ_duration rcd,
1379 config.rule_recuring_fine rrf,
1380 config.rule_max_fine rmf,
1383 rcd.id = ' || this_duration_rule || ' AND
1384 rrf.id = ' || this_fine_rule || ' AND
1385 rmf.id = ' || this_max_fine_rule || ' AND
1386 ac.id = c.target_copy AND
1387 c.id = ' || circ || ';');
1389 -- Keep track of where we are in the process
1391 IF (n % 100 = 0) THEN
1392 RAISE INFO '%', n || ' of ' || n_circs
1393 || ' (' || (100*n/n_circs) || '%) circs updated.';
1401 $$ LANGUAGE plpgsql;
1403 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1407 -- First make sure the circ matrix is loaded and the circulations
1408 -- have been staged to the extent possible (but at the very least
1409 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1410 -- circ modifiers must also be in place.
1412 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1420 this_duration_rule INT;
1422 this_max_fine_rule INT;
1423 rcd config.rule_circ_duration%ROWTYPE;
1424 rrf config.rule_recurring_fine%ROWTYPE;
1425 rmf config.rule_max_fine%ROWTYPE;
1432 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1434 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1436 -- Fetch the correct rules for this circulation
1443 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1446 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1447 INTO circ_lib, target_copy, usr, is_renewal ;
1449 INTO this_duration_rule,
1452 (matchpoint).duration_rule,
1453 (matchpoint).recurring_fine_rule,
1454 (matchpoint).max_fine_rule
1455 FROM action.find_circ_matrix_matchpoint(
1461 SELECT INTO rcd * FROM config.rule_circ_duration
1462 WHERE id = this_duration_rule;
1463 SELECT INTO rrf * FROM config.rule_recurring_fine
1464 WHERE id = this_fine_rule;
1465 SELECT INTO rmf * FROM config.rule_max_fine
1466 WHERE id = this_max_fine_rule;
1468 -- Apply the rules to this circulation
1469 EXECUTE ('UPDATE ' || tablename || ' c
1471 duration_rule = rcd.name,
1472 recurring_fine_rule = rrf.name,
1473 max_fine_rule = rmf.name,
1474 duration = rcd.normal,
1475 recurring_fine = rrf.normal,
1478 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1481 renewal_remaining = rcd.max_renewals,
1482 grace_period = rrf.grace_period
1484 config.rule_circ_duration rcd,
1485 config.rule_recurring_fine rrf,
1486 config.rule_max_fine rmf,
1489 rcd.id = ' || this_duration_rule || ' AND
1490 rrf.id = ' || this_fine_rule || ' AND
1491 rmf.id = ' || this_max_fine_rule || ' AND
1492 ac.id = c.target_copy AND
1493 c.id = ' || circ || ';');
1495 -- Keep track of where we are in the process
1497 IF (n % 100 = 0) THEN
1498 RAISE INFO '%', n || ' of ' || n_circs
1499 || ' (' || (100*n/n_circs) || '%) circs updated.';
1507 $$ LANGUAGE plpgsql;
1509 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1513 -- First make sure the circ matrix is loaded and the circulations
1514 -- have been staged to the extent possible (but at the very least
1515 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1516 -- circ modifiers must also be in place.
1518 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1526 this_duration_rule INT;
1528 this_max_fine_rule INT;
1529 rcd config.rule_circ_duration%ROWTYPE;
1530 rrf config.rule_recurring_fine%ROWTYPE;
1531 rmf config.rule_max_fine%ROWTYPE;
1537 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1539 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1541 -- Fetch the correct rules for this circulation
1548 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1551 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1552 INTO circ_lib, target_copy, usr, is_renewal ;
1554 INTO this_duration_rule,
1557 (matchpoint).duration_rule,
1558 (matchpoint).recurring_fine_rule,
1559 (matchpoint).max_fine_rule
1560 FROM action.find_circ_matrix_matchpoint(
1566 SELECT INTO rcd * FROM config.rule_circ_duration
1567 WHERE id = this_duration_rule;
1568 SELECT INTO rrf * FROM config.rule_recurring_fine
1569 WHERE id = this_fine_rule;
1570 SELECT INTO rmf * FROM config.rule_max_fine
1571 WHERE id = this_max_fine_rule;
1573 -- Apply the rules to this circulation
1574 EXECUTE ('UPDATE ' || tablename || ' c
1576 duration_rule = rcd.name,
1577 recurring_fine_rule = rrf.name,
1578 max_fine_rule = rmf.name,
1579 duration = rcd.normal,
1580 recurring_fine = rrf.normal,
1583 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1586 renewal_remaining = rcd.max_renewals,
1587 grace_period = rrf.grace_period
1589 config.rule_circ_duration rcd,
1590 config.rule_recurring_fine rrf,
1591 config.rule_max_fine rmf,
1594 rcd.id = ' || this_duration_rule || ' AND
1595 rrf.id = ' || this_fine_rule || ' AND
1596 rmf.id = ' || this_max_fine_rule || ' AND
1597 ac.id = c.target_copy AND
1598 c.id = ' || circ || ';');
1600 -- Keep track of where we are in the process
1602 IF (n % 100 = 0) THEN
1603 RAISE INFO '%', n || ' of ' || n_circs
1604 || ' (' || (100*n/n_circs) || '%) circs updated.';
1612 $$ LANGUAGE plpgsql;
1617 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1619 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1620 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1622 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1623 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1626 c TEXT := schemaname || '.asset_copy_legacy';
1627 sc TEXT := schemaname || '.asset_stat_cat';
1628 sce TEXT := schemaname || '.asset_stat_cat_entry';
1629 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1635 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1637 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1639 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1640 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1641 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1648 $$ LANGUAGE plpgsql;
1650 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1652 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1653 -- This will assign standing penalties as needed.
1661 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1663 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1665 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1675 $$ LANGUAGE plpgsql;
1678 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1681 INSERT INTO metabib.metarecord (fingerprint, master_record)
1682 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1683 FROM biblio.record_entry b
1685 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1686 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1687 ORDER BY b.fingerprint, b.quality DESC;
1688 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1690 FROM biblio.record_entry r
1691 JOIN metabib.metarecord m USING (fingerprint)
1692 WHERE NOT r.deleted;
1695 $$ LANGUAGE plpgsql;
1698 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1701 INSERT INTO metabib.metarecord (fingerprint, master_record)
1702 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1703 FROM biblio.record_entry b
1705 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1706 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1707 ORDER BY b.fingerprint, b.quality DESC;
1708 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1710 FROM biblio.record_entry r
1711 JOIN metabib.metarecord m USING (fingerprint)
1713 AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
1716 $$ LANGUAGE plpgsql;
1719 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1721 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1722 -- Then SELECT migration_tools.create_cards('m_foo');
1725 u TEXT := schemaname || '.actor_usr_legacy';
1726 c TEXT := schemaname || '.actor_card';
1730 EXECUTE ('DELETE FROM ' || c || ';');
1731 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1732 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1738 $$ LANGUAGE plpgsql;
1741 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1743 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1745 my ($marcxml, $shortname) = @_;
1748 use MARC::File::XML;
1753 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1755 foreach my $field ( $marc->field('856') ) {
1756 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1757 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1758 $field->add_subfields( '9' => $shortname );
1759 $field->update( ind2 => '0');
1763 $xml = $marc->as_xml_record;
1764 $xml =~ s/^<\?.+?\?>$//mo;
1766 $xml =~ s/>\s+</></sgo;
1771 $$ LANGUAGE PLPERLU STABLE;
1773 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1775 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1777 my ($marcxml, $shortname) = @_;
1780 use MARC::File::XML;
1785 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1787 foreach my $field ( $marc->field('856') ) {
1788 if ( ! $field->as_string('9') ) {
1789 $field->add_subfields( '9' => $shortname );
1793 $xml = $marc->as_xml_record;
1794 $xml =~ s/^<\?.+?\?>$//mo;
1796 $xml =~ s/>\s+</></sgo;
1801 $$ LANGUAGE PLPERLU STABLE;
1804 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1816 -- Bail out if asked to change the label to ##URI##
1817 IF new_label = '##URI##' THEN
1821 -- Gather information
1822 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1823 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1824 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1826 -- Bail out if the label already is ##URI##
1827 IF old_label = '##URI##' THEN
1831 -- Bail out if the call number label is already correct
1832 IF new_volume = old_volume THEN
1836 -- Check whether we already have a destination volume available
1837 SELECT id INTO new_volume FROM asset.call_number
1840 owning_lib = owner AND
1841 label = new_label AND
1844 -- Create destination volume if needed
1846 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1847 VALUES (1, 1, bib, owner, new_label, cn_class);
1848 SELECT id INTO new_volume FROM asset.call_number
1851 owning_lib = owner AND
1852 label = new_label AND
1856 -- Move copy to destination
1857 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1859 -- Delete source volume if it is now empty
1860 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1862 DELETE FROM asset.call_number WHERE id = old_volume;
1867 $$ LANGUAGE plpgsql;
1869 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1874 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1878 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1879 $zipdata{$zip} = [$city, $state, $county];
1882 if (defined $zipdata{$input}) {
1883 my ($city, $state, $county) = @{$zipdata{$input}};
1884 return [$city, $state, $county];
1885 } elsif (defined $zipdata{substr $input, 0, 5}) {
1886 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1887 return [$city, $state, $county];
1889 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1892 $$ LANGUAGE PLPERLU STABLE;
1894 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1901 errors_found BOOLEAN;
1903 parent_shortname TEXT;
1909 type_parent_depth INT;
1914 errors_found := FALSE;
1916 -- Checking actor.org_unit_type
1918 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1920 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1921 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1923 IF type_parent IS NOT NULL THEN
1925 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1927 IF type_depth - type_parent_depth <> 1 THEN
1928 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1929 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1930 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1931 ou_type_name, type_depth, parent_type, type_parent_depth;
1932 errors_found := TRUE;
1940 -- Checking actor.org_unit
1942 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1944 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1945 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
1946 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
1947 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1948 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1949 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
1950 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
1952 IF ou_parent IS NOT NULL THEN
1954 IF (org_unit_depth - parent_depth <> 1) OR (
1955 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1957 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1958 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1959 errors_found := TRUE;
1966 IF NOT errors_found THEN
1967 RAISE INFO 'No errors found.';
1974 $$ LANGUAGE plpgsql;
1977 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1981 DELETE FROM asset.opac_visible_copies;
1983 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1985 cp.id, cp.circ_lib, cn.record
1988 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1989 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1990 JOIN asset.copy_location cl ON (cp.location = cl.id)
1991 JOIN config.copy_status cs ON (cp.status = cs.id)
1992 JOIN biblio.record_entry b ON (cn.record = b.id)
2001 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2005 $$ LANGUAGE plpgsql;
2008 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2014 old_owning_lib INTEGER;
2020 -- Gather information
2021 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2022 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2023 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2025 -- Bail out if the new_owning_lib is not the ID of an org_unit
2026 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2028 '% is not a valid actor.org_unit ID; no change made.',
2033 -- Bail out discreetly if the owning_lib is already correct
2034 IF new_owning_lib = old_owning_lib THEN
2038 -- Check whether we already have a destination volume available
2039 SELECT id INTO new_volume FROM asset.call_number
2042 owning_lib = new_owning_lib AND
2043 label = old_label AND
2046 -- Create destination volume if needed
2048 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2049 VALUES (1, 1, bib, new_owning_lib, old_label);
2050 SELECT id INTO new_volume FROM asset.call_number
2053 owning_lib = new_owning_lib AND
2054 label = old_label AND
2058 -- Move copy to destination
2059 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2061 -- Delete source volume if it is now empty
2062 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2064 DELETE FROM asset.call_number WHERE id = old_volume;
2069 $$ LANGUAGE plpgsql;
2072 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2074 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2077 new_owning_lib INTEGER;
2081 -- Parse the new_owner as an org unit ID or shortname
2082 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2083 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2084 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2085 ELSIF new_owner ~ E'^[0-9]+$' THEN
2086 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2089 E'You don\'t need to put the actor.org_unit ID in quotes; '
2090 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2091 new_owning_lib := new_owner::INTEGER;
2092 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2096 '% is not a valid actor.org_unit shortname or ID; no change made.',
2103 $$ LANGUAGE plpgsql;
2105 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2108 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2111 MARC::Charset->assume_unicode(1);
2116 my $r = MARC::Record->new_from_xml( $xml );
2117 my $output_xml = $r->as_xml_record();
2125 $func$ LANGUAGE PLPERLU;
2126 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2128 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2130 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2131 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2132 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2133 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2134 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2135 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2136 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2137 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2138 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2139 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2140 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2141 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2142 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2143 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2144 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2145 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2146 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2147 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2148 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2149 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2150 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2151 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2152 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2153 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2154 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2155 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2157 $FUNC$ LANGUAGE PLPGSQL;
2159 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2161 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2162 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2163 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2164 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2165 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2166 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2167 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2169 -- import any new circ rules
2170 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2171 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2172 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2173 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2175 -- and permission groups
2176 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2179 $FUNC$ LANGUAGE PLPGSQL;
2182 CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$
2191 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2192 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2193 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2194 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2195 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2196 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2197 FOR name IN EXECUTE loopq LOOP
2198 EXECUTE existsq INTO ct USING name;
2200 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2201 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2202 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2203 EXECUTE copyst USING name;
2207 $FUNC$ LANGUAGE PLPGSQL;
2209 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2215 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2218 MARC::Charset->assume_unicode(1);
2220 my $target_xml = shift;
2221 my $source_xml = shift;
2227 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2231 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2236 my $source_id = $source->subfield('901', 'c');
2237 $source_id = $source->subfield('903', 'a') unless $source_id;
2238 my $target_id = $target->subfield('901', 'c');
2239 $target_id = $target->subfield('903', 'a') unless $target_id;
2241 my %existing_fields;
2242 foreach my $tag (@$tags) {
2243 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2244 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2245 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2247 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2251 my $xml = $target->as_xml_record;
2252 $xml =~ s/^<\?.+?\?>$//mo;
2254 $xml =~ s/>\s+</></sgo;
2258 $func$ LANGUAGE PLPERLU;
2259 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
2261 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2267 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2270 my $in_tags = shift;
2271 my $in_values = shift;
2273 # hack-and-slash parsing of array-passed-as-string;
2274 # this can go away once everybody is running Postgres 9.1+
2275 my $csv = Text::CSV->new({binary => 1});
2278 my $status = $csv->parse($in_tags);
2279 my $tags = [ $csv->fields() ];
2280 $in_values =~ s/^{//;
2281 $in_values =~ s/}$//;
2282 $status = $csv->parse($in_values);
2283 my $values = [ $csv->fields() ];
2285 my $marc = MARC::Record->new();
2287 $marc->leader('00000nam a22000007 4500');
2288 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2290 foreach my $i (0..$#$tags) {
2292 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2295 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2296 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2298 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2302 my $xml = $marc->as_xml_record;
2303 $xml =~ s/^<\?.+?\?>$//mo;
2305 $xml =~ s/>\s+</></sgo;
2309 $func$ LANGUAGE PLPERLU;
2310 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2311 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2312 The second argument is an array of text containing the values to plug into each field.
2313 If the value for a given field is NULL or the empty string, it is not inserted.
2316 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2318 my ($marcxml, $tag, $pos, $value) = @_;
2321 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2325 MARC::Charset->assume_unicode(1);
2327 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2328 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2329 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2330 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2334 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2336 foreach my $field ($marc->field($tag)) {
2337 $field->update("ind$pos" => $value);
2339 $xml = $marc->as_xml_record;
2340 $xml =~ s/^<\?.+?\?>$//mo;
2342 $xml =~ s/>\s+</></sgo;
2346 $func$ LANGUAGE PLPERLU;
2348 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2349 The first argument is a MARCXML string.
2350 The second argument is a MARC tag.
2351 The third argument is the indicator position, either 1 or 2.
2352 The fourth argument is the character to set the indicator value to.
2353 All occurences of the specified field will be changed.
2354 The function returns the revised MARCXML string.$$;
2356 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2361 first_name TEXT DEFAULT '',
2362 last_name TEXT DEFAULT ''
2363 ) RETURNS VOID AS $func$
2365 RAISE NOTICE '%', org ;
2366 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2367 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2368 FROM actor.org_unit aou, permission.grp_tree pgt
2369 WHERE aou.shortname = org
2370 AND pgt.name = perm_group;
2375 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2376 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2378 target_event_def ALIAS FOR $1;
2381 DROP TABLE IF EXISTS new_atevdefs;
2382 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2383 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2384 INSERT INTO action_trigger.event_definition (
2405 ,name || ' (clone of '||target_event_def||')'
2421 action_trigger.event_definition
2423 id = target_event_def
2425 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2426 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2427 INSERT INTO action_trigger.environment (
2433 currval('action_trigger.event_definition_id_seq')
2438 action_trigger.environment
2440 event_def = target_event_def
2442 INSERT INTO action_trigger.event_params (
2447 currval('action_trigger.event_definition_id_seq')
2451 action_trigger.event_params
2453 event_def = target_event_def
2456 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2458 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2460 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2461 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2463 target_event_def ALIAS FOR $1;
2465 new_interval ALIAS FOR $3;
2467 DROP TABLE IF EXISTS new_atevdefs;
2468 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2469 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2470 INSERT INTO action_trigger.event_definition (
2491 ,name || ' (clone of '||target_event_def||')'
2507 action_trigger.event_definition
2509 id = target_event_def
2511 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2512 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2513 INSERT INTO action_trigger.environment (
2519 currval('action_trigger.event_definition_id_seq')
2524 action_trigger.environment
2526 event_def = target_event_def
2528 INSERT INTO action_trigger.event_params (
2533 currval('action_trigger.event_definition_id_seq')
2537 action_trigger.event_params
2539 event_def = target_event_def
2542 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2544 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2546 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2547 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2550 use MARC::File::XML;
2555 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2556 $field = $marc->field($tag);
2558 return $field->as_string($subfield,$delimiter);
2559 $$ LANGUAGE PLPERLU STABLE;
2561 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2562 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2565 use MARC::File::XML;
2570 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2571 @fields = $marc->field($tag);
2574 foreach my $field (@fields) {
2575 push @texts, $field->as_string($subfield,$delimiter);
2578 $$ LANGUAGE PLPERLU STABLE;
2580 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2581 SELECT action.find_hold_matrix_matchpoint(
2582 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2583 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2584 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2585 (SELECT usr FROM action.hold_request WHERE id = $1),
2586 (SELECT requestor FROM action.hold_request WHERE id = $1)
2590 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2591 SELECT action.hold_request_permit_test(
2592 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2593 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2594 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2595 (SELECT usr FROM action.hold_request WHERE id = $1),
2596 (SELECT requestor FROM action.hold_request WHERE id = $1)
2600 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2601 SELECT action.find_circ_matrix_matchpoint(
2602 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2603 (SELECT target_copy FROM action.circulation WHERE id = $1),
2604 (SELECT usr FROM action.circulation WHERE id = $1),
2606 NULLIF(phone_renewal,false),
2607 NULLIF(desk_renewal,false),
2608 NULLIF(opac_renewal,false),
2610 ) FROM action.circulation WHERE id = $1
2615 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2620 RAISE EXCEPTION 'assertion';
2623 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2625 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2631 RAISE EXCEPTION '%', msg;
2634 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2636 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2639 fail_msg ALIAS FOR $2;
2640 success_msg ALIAS FOR $3;
2643 RAISE EXCEPTION '%', fail_msg;
2647 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2649 -- push bib sequence and return starting value for reserved range
2650 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2652 bib_count ALIAS FOR $1;
2655 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2657 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2662 $$ LANGUAGE PLPGSQL STRICT VOLATILE;