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;
425 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
427 city_state_zip TEXT := $1;
432 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;
433 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
434 IF city_state_zip ~ ',' THEN
435 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
436 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
438 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
439 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
440 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
442 IF city_state_zip ~ E'^\\S+$' THEN
443 city := city_state_zip;
446 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
447 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
451 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
453 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
455 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
456 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
458 fullstring TEXT := $1;
468 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
469 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
472 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
474 IF fullstring ~ ',' THEN
475 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
476 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
478 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
479 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
480 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
482 IF fullstring ~ E'^\\S+$' THEN
483 scratch1 := fullstring;
486 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
487 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
492 IF scratch1 ~ '[\$]' THEN
493 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
494 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
496 IF scratch1 ~ '\s' THEN
497 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
498 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
505 IF scratch2 ~ '^\d' THEN
506 address1 := scratch2;
509 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
510 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
514 TRIM(BOTH ' ' FROM address1)
515 ,TRIM(BOTH ' ' FROM address2)
516 ,TRIM(BOTH ' ' FROM city)
517 ,TRIM(BOTH ' ' FROM state)
518 ,TRIM(BOTH ' ' FROM zip)
521 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
523 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
527 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
528 IF o::BIGINT < t THEN
535 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
537 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
539 migration_schema ALIAS FOR $1;
543 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
548 $$ LANGUAGE PLPGSQL STRICT STABLE;
550 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
552 migration_schema ALIAS FOR $1;
556 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
561 $$ LANGUAGE PLPGSQL STRICT STABLE;
563 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
565 migration_schema ALIAS FOR $1;
569 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
574 $$ LANGUAGE PLPGSQL STRICT STABLE;
576 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
578 migration_schema ALIAS FOR $1;
582 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
587 $$ LANGUAGE PLPGSQL STRICT STABLE;
589 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
591 migration_schema ALIAS FOR $1;
593 patron_table ALIAS FOR $2;
594 default_patron_profile ALIAS FOR $3;
597 sql_where1 TEXT := '';
598 sql_where2 TEXT := '';
599 sql_where3 TEXT := '';
602 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
604 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
606 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
607 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);
608 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);
609 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);
610 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,'') || ';';
611 --RAISE INFO 'sql = %', sql;
612 PERFORM migration_tools.exec( $1, sql );
614 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
616 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
618 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
621 $$ LANGUAGE PLPGSQL STRICT STABLE;
623 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
625 migration_schema ALIAS FOR $1;
627 item_table ALIAS FOR $2;
630 sql_where1 TEXT := '';
631 sql_where2 TEXT := '';
632 sql_where3 TEXT := '';
635 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
637 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
639 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 ';
640 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);
641 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);
642 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);
643 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,'') || ';';
644 --RAISE INFO 'sql = %', sql;
645 PERFORM migration_tools.exec( $1, sql );
648 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
650 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
653 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
655 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
657 migration_schema ALIAS FOR $1;
658 base_copy_location_map TEXT;
659 item_table ALIAS FOR $2;
662 sql_where1 TEXT := '';
663 sql_where2 TEXT := '';
664 sql_where3 TEXT := '';
667 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
669 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
671 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
672 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);
673 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);
674 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);
675 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,'') || ';';
676 --RAISE INFO 'sql = %', sql;
677 PERFORM migration_tools.exec( $1, sql );
680 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
682 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
685 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
687 -- 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
688 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
690 migration_schema ALIAS FOR $1;
692 circ_table ALIAS FOR $2;
693 item_table ALIAS FOR $3;
694 patron_table ALIAS FOR $4;
697 sql_where1 TEXT := '';
698 sql_where2 TEXT := '';
699 sql_where3 TEXT := '';
700 sql_where4 TEXT := '';
703 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
705 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
707 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 ';
708 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);
709 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);
710 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);
711 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);
712 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,'') || ';';
713 --RAISE INFO 'sql = %', sql;
714 PERFORM migration_tools.exec( $1, sql );
717 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
719 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
722 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
725 -- $barcode source barcode
726 -- $prefix prefix to add to barcode, NULL = add no prefix
727 -- $maxlen maximum length of barcode; default to 14 if left NULL
728 -- $pad padding string to apply to left of source barcode before adding
729 -- prefix and suffix; set to NULL or '' if no padding is desired
730 -- $suffix suffix to add to barcode, NULL = add no suffix
732 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
733 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
735 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
736 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
739 return unless defined $barcode;
741 $prefix = '' unless defined $prefix;
743 $pad = '0' unless defined $pad;
744 $suffix = '' unless defined $suffix;
746 # bail out if adding prefix and suffix would bring new barcode over max length
747 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
749 my $new_barcode = $barcode;
751 my $pad_length = $maxlen - length($prefix) - length($suffix);
752 if (length($barcode) < $pad_length) {
753 # assuming we always want padding on the left
754 # also assuming that it is possible to have the pad string be longer than 1 character
755 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
759 # bail out if adding prefix and suffix would bring new barcode over max length
760 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
762 return "$prefix$new_barcode$suffix";
763 $$ LANGUAGE PLPERLU STABLE;
765 -- remove previous version of this function
766 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
768 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
770 attempt_value ALIAS FOR $1;
771 datatype ALIAS FOR $2;
773 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
774 RETURN attempt_value;
776 WHEN OTHERS THEN RETURN NULL;
778 $$ LANGUAGE PLPGSQL STRICT STABLE;
780 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
782 attempt_value ALIAS FOR $1;
783 fail_value ALIAS FOR $2;
787 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
794 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
799 $$ LANGUAGE PLPGSQL STRICT STABLE;
801 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
803 attempt_value ALIAS FOR $1;
804 fail_value ALIAS FOR $2;
808 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
815 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
820 $$ LANGUAGE PLPGSQL STRICT STABLE;
822 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
824 attempt_value ALIAS FOR $1;
825 fail_value ALIAS FOR $2;
829 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
836 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
841 $$ LANGUAGE PLPGSQL STRICT STABLE;
843 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
845 attempt_value ALIAS FOR $1;
846 fail_value ALIAS FOR $2;
849 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
854 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
859 $$ LANGUAGE PLPGSQL STRICT STABLE;
861 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
863 attempt_value ALIAS FOR $1;
864 fail_value ALIAS FOR $2;
868 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
875 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
880 $$ LANGUAGE PLPGSQL STRICT STABLE;
882 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
884 attempt_value ALIAS FOR $1;
885 fail_value ALIAS FOR $2;
889 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
896 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
901 $$ LANGUAGE PLPGSQL STRICT STABLE;
903 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
905 attempt_value ALIAS FOR $1;
906 fail_value ALIAS FOR $2;
909 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
910 RAISE EXCEPTION 'too many digits';
913 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;'
920 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
925 $$ LANGUAGE PLPGSQL STRICT STABLE;
927 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
929 attempt_value ALIAS FOR $1;
930 fail_value ALIAS FOR $2;
933 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
934 RAISE EXCEPTION 'too many digits';
937 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;'
944 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
949 $$ LANGUAGE PLPGSQL STRICT STABLE;
951 -- add_codabar_checkdigit
952 -- $barcode source barcode
954 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
955 -- character with a checkdigit computed according to the usual algorithm for library barcodes
956 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
957 -- input string does not meet those requirements, it is returned unchanged.
959 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
962 return $barcode if $barcode !~ /^\d{13,14}$/;
963 $barcode = substr($barcode, 0, 13); # ignore 14th digit
964 my @digits = split //, $barcode;
966 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
967 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
968 my $remainder = $total % 10;
969 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
970 return $barcode . $checkdigit;
971 $$ LANGUAGE PLPERLU STRICT STABLE;
973 -- add_code39mod43_checkdigit
974 -- $barcode source barcode
976 -- If the source string is 13 or 14 characters long and contains only valid
977 -- Code 39 mod 43 characters, adds or replaces the 14th
978 -- character with a checkdigit computed according to the usual algorithm for library barcodes
979 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
980 -- input string does not meet those requirements, it is returned unchanged.
982 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
985 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
986 $barcode = substr($barcode, 0, 13); # ignore 14th character
988 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
989 my %nums = map { $valid_chars[$_] => $_ } (0..42);
992 $total += $nums{$_} foreach split(//, $barcode);
993 my $remainder = $total % 43;
994 my $checkdigit = $valid_chars[$remainder];
995 return $barcode . $checkdigit;
996 $$ LANGUAGE PLPERLU STRICT STABLE;
998 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1001 areacode TEXT := $2;
1004 n_digits INTEGER := 0;
1007 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1008 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1009 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1010 IF n_digits = 7 AND areacode <> '' THEN
1011 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1012 output := (areacode || '-' || temp);
1019 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1021 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1022 my ($marcxml, $pos, $value) = @_;
1025 use MARC::File::XML;
1029 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1030 my $leader = $marc->leader();
1031 substr($leader, $pos, 1) = $value;
1032 $marc->leader($leader);
1033 $xml = $marc->as_xml_record;
1034 $xml =~ s/^<\?.+?\?>$//mo;
1036 $xml =~ s/>\s+</></sgo;
1039 $$ LANGUAGE PLPERLU STABLE;
1041 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1042 my ($marcxml, $pos, $value) = @_;
1045 use MARC::File::XML;
1049 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1050 my $f008 = $marc->field('008');
1053 my $field = $f008->data();
1054 substr($field, $pos, 1) = $value;
1055 $f008->update($field);
1056 $xml = $marc->as_xml_record;
1057 $xml =~ s/^<\?.+?\?>$//mo;
1059 $xml =~ s/>\s+</></sgo;
1063 $$ LANGUAGE PLPERLU STABLE;
1066 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1068 profile ALIAS FOR $1;
1070 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1072 $$ LANGUAGE PLPGSQL STRICT STABLE;
1075 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1077 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1079 $$ LANGUAGE PLPGSQL STRICT STABLE;
1082 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1084 my ($marcxml, $tags) = @_;
1087 use MARC::File::XML;
1092 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1093 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1095 my @incumbents = ();
1097 foreach my $field ( $marc->fields() ) {
1098 push @incumbents, $field->as_formatted();
1101 foreach $field ( $to_insert->fields() ) {
1102 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1103 $marc->insert_fields_ordered( ($field) );
1107 $xml = $marc->as_xml_record;
1108 $xml =~ s/^<\?.+?\?>$//mo;
1110 $xml =~ s/>\s+</></sgo;
1115 $$ LANGUAGE PLPERLU STABLE;
1117 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1121 -- First make sure the circ matrix is loaded and the circulations
1122 -- have been staged to the extent possible (but at the very least
1123 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1124 -- circ modifiers must also be in place.
1126 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1134 this_duration_rule INT;
1136 this_max_fine_rule INT;
1137 rcd config.rule_circ_duration%ROWTYPE;
1138 rrf config.rule_recurring_fine%ROWTYPE;
1139 rmf config.rule_max_fine%ROWTYPE;
1146 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1148 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1150 -- Fetch the correct rules for this circulation
1157 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1160 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1161 INTO circ_lib, target_copy, usr, is_renewal ;
1163 INTO this_duration_rule,
1167 recurring_fine_rule,
1169 FROM action.item_user_circ_test(
1175 SELECT INTO rcd * FROM config.rule_circ_duration
1176 WHERE id = this_duration_rule;
1177 SELECT INTO rrf * FROM config.rule_recurring_fine
1178 WHERE id = this_fine_rule;
1179 SELECT INTO rmf * FROM config.rule_max_fine
1180 WHERE id = this_max_fine_rule;
1182 -- Apply the rules to this circulation
1183 EXECUTE ('UPDATE ' || tablename || ' c
1185 duration_rule = rcd.name,
1186 recurring_fine_rule = rrf.name,
1187 max_fine_rule = rmf.name,
1188 duration = rcd.normal,
1189 recurring_fine = rrf.normal,
1192 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1195 renewal_remaining = rcd.max_renewals
1197 config.rule_circ_duration rcd,
1198 config.rule_recurring_fine rrf,
1199 config.rule_max_fine rmf,
1202 rcd.id = ' || this_duration_rule || ' AND
1203 rrf.id = ' || this_fine_rule || ' AND
1204 rmf.id = ' || this_max_fine_rule || ' AND
1205 ac.id = c.target_copy AND
1206 c.id = ' || circ || ';');
1208 -- Keep track of where we are in the process
1210 IF (n % 100 = 0) THEN
1211 RAISE INFO '%', n || ' of ' || n_circs
1212 || ' (' || (100*n/n_circs) || '%) circs updated.';
1220 $$ LANGUAGE plpgsql;
1222 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1226 -- First make sure the circ matrix is loaded and the circulations
1227 -- have been staged to the extent possible (but at the very least
1228 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1229 -- circ modifiers must also be in place.
1231 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1239 this_duration_rule INT;
1241 this_max_fine_rule INT;
1242 rcd config.rule_circ_duration%ROWTYPE;
1243 rrf config.rule_recurring_fine%ROWTYPE;
1244 rmf config.rule_max_fine%ROWTYPE;
1251 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1253 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1255 -- Fetch the correct rules for this circulation
1262 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1265 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1266 INTO circ_lib, target_copy, usr, is_renewal ;
1268 INTO this_duration_rule,
1274 FROM action.find_circ_matrix_matchpoint(
1280 SELECT INTO rcd * FROM config.rule_circ_duration
1281 WHERE id = this_duration_rule;
1282 SELECT INTO rrf * FROM config.rule_recurring_fine
1283 WHERE id = this_fine_rule;
1284 SELECT INTO rmf * FROM config.rule_max_fine
1285 WHERE id = this_max_fine_rule;
1287 -- Apply the rules to this circulation
1288 EXECUTE ('UPDATE ' || tablename || ' c
1290 duration_rule = rcd.name,
1291 recuring_fine_rule = rrf.name,
1292 max_fine_rule = rmf.name,
1293 duration = rcd.normal,
1294 recuring_fine = rrf.normal,
1297 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1300 renewal_remaining = rcd.max_renewals
1302 config.rule_circ_duration rcd,
1303 config.rule_recuring_fine rrf,
1304 config.rule_max_fine rmf,
1307 rcd.id = ' || this_duration_rule || ' AND
1308 rrf.id = ' || this_fine_rule || ' AND
1309 rmf.id = ' || this_max_fine_rule || ' AND
1310 ac.id = c.target_copy AND
1311 c.id = ' || circ || ';');
1313 -- Keep track of where we are in the process
1315 IF (n % 100 = 0) THEN
1316 RAISE INFO '%', n || ' of ' || n_circs
1317 || ' (' || (100*n/n_circs) || '%) circs updated.';
1325 $$ LANGUAGE plpgsql;
1327 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1331 -- First make sure the circ matrix is loaded and the circulations
1332 -- have been staged to the extent possible (but at the very least
1333 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1334 -- circ modifiers must also be in place.
1336 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1344 this_duration_rule INT;
1346 this_max_fine_rule INT;
1347 rcd config.rule_circ_duration%ROWTYPE;
1348 rrf config.rule_recurring_fine%ROWTYPE;
1349 rmf config.rule_max_fine%ROWTYPE;
1356 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1358 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1360 -- Fetch the correct rules for this circulation
1367 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1370 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1371 INTO circ_lib, target_copy, usr, is_renewal ;
1373 INTO this_duration_rule,
1376 (matchpoint).duration_rule,
1377 (matchpoint).recurring_fine_rule,
1378 (matchpoint).max_fine_rule
1379 FROM action.find_circ_matrix_matchpoint(
1385 SELECT INTO rcd * FROM config.rule_circ_duration
1386 WHERE id = this_duration_rule;
1387 SELECT INTO rrf * FROM config.rule_recurring_fine
1388 WHERE id = this_fine_rule;
1389 SELECT INTO rmf * FROM config.rule_max_fine
1390 WHERE id = this_max_fine_rule;
1392 -- Apply the rules to this circulation
1393 EXECUTE ('UPDATE ' || tablename || ' c
1395 duration_rule = rcd.name,
1396 recurring_fine_rule = rrf.name,
1397 max_fine_rule = rmf.name,
1398 duration = rcd.normal,
1399 recurring_fine = rrf.normal,
1402 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1405 renewal_remaining = rcd.max_renewals,
1406 grace_period = rrf.grace_period
1408 config.rule_circ_duration rcd,
1409 config.rule_recurring_fine rrf,
1410 config.rule_max_fine rmf,
1413 rcd.id = ' || this_duration_rule || ' AND
1414 rrf.id = ' || this_fine_rule || ' AND
1415 rmf.id = ' || this_max_fine_rule || ' AND
1416 ac.id = c.target_copy AND
1417 c.id = ' || circ || ';');
1419 -- Keep track of where we are in the process
1421 IF (n % 100 = 0) THEN
1422 RAISE INFO '%', n || ' of ' || n_circs
1423 || ' (' || (100*n/n_circs) || '%) circs updated.';
1431 $$ LANGUAGE plpgsql;
1433 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1437 -- First make sure the circ matrix is loaded and the circulations
1438 -- have been staged to the extent possible (but at the very least
1439 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1440 -- circ modifiers must also be in place.
1442 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1450 this_duration_rule INT;
1452 this_max_fine_rule INT;
1453 rcd config.rule_circ_duration%ROWTYPE;
1454 rrf config.rule_recurring_fine%ROWTYPE;
1455 rmf config.rule_max_fine%ROWTYPE;
1461 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1463 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1465 -- Fetch the correct rules for this circulation
1472 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1475 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1476 INTO circ_lib, target_copy, usr, is_renewal ;
1478 INTO this_duration_rule,
1481 (matchpoint).duration_rule,
1482 (matchpoint).recurring_fine_rule,
1483 (matchpoint).max_fine_rule
1484 FROM action.find_circ_matrix_matchpoint(
1490 SELECT INTO rcd * FROM config.rule_circ_duration
1491 WHERE id = this_duration_rule;
1492 SELECT INTO rrf * FROM config.rule_recurring_fine
1493 WHERE id = this_fine_rule;
1494 SELECT INTO rmf * FROM config.rule_max_fine
1495 WHERE id = this_max_fine_rule;
1497 -- Apply the rules to this circulation
1498 EXECUTE ('UPDATE ' || tablename || ' c
1500 duration_rule = rcd.name,
1501 recurring_fine_rule = rrf.name,
1502 max_fine_rule = rmf.name,
1503 duration = rcd.normal,
1504 recurring_fine = rrf.normal,
1507 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1510 renewal_remaining = rcd.max_renewals,
1511 grace_period = rrf.grace_period
1513 config.rule_circ_duration rcd,
1514 config.rule_recurring_fine rrf,
1515 config.rule_max_fine rmf,
1518 rcd.id = ' || this_duration_rule || ' AND
1519 rrf.id = ' || this_fine_rule || ' AND
1520 rmf.id = ' || this_max_fine_rule || ' AND
1521 ac.id = c.target_copy AND
1522 c.id = ' || circ || ';');
1524 -- Keep track of where we are in the process
1526 IF (n % 100 = 0) THEN
1527 RAISE INFO '%', n || ' of ' || n_circs
1528 || ' (' || (100*n/n_circs) || '%) circs updated.';
1536 $$ LANGUAGE plpgsql;
1541 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1543 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1544 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1546 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1547 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1550 c TEXT := schemaname || '.asset_copy_legacy';
1551 sc TEXT := schemaname || '.asset_stat_cat';
1552 sce TEXT := schemaname || '.asset_stat_cat_entry';
1553 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1559 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1561 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1563 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1564 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1565 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1572 $$ LANGUAGE plpgsql;
1574 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1576 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1577 -- This will assign standing penalties as needed.
1585 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1587 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1589 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1599 $$ LANGUAGE plpgsql;
1602 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1605 INSERT INTO metabib.metarecord (fingerprint, master_record)
1606 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1607 FROM biblio.record_entry b
1609 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)
1610 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1611 ORDER BY b.fingerprint, b.quality DESC;
1612 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1614 FROM biblio.record_entry r
1615 JOIN metabib.metarecord m USING (fingerprint)
1616 WHERE NOT r.deleted;
1619 $$ LANGUAGE plpgsql;
1622 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1625 INSERT INTO metabib.metarecord (fingerprint, master_record)
1626 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1627 FROM biblio.record_entry b
1629 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)
1630 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1631 ORDER BY b.fingerprint, b.quality DESC;
1632 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1634 FROM biblio.record_entry r
1635 JOIN metabib.metarecord m USING (fingerprint)
1637 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);
1640 $$ LANGUAGE plpgsql;
1643 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1645 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1646 -- Then SELECT migration_tools.create_cards('m_foo');
1649 u TEXT := schemaname || '.actor_usr_legacy';
1650 c TEXT := schemaname || '.actor_card';
1654 EXECUTE ('DELETE FROM ' || c || ';');
1655 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1656 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1662 $$ LANGUAGE plpgsql;
1665 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1667 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1669 my ($marcxml, $shortname) = @_;
1672 use MARC::File::XML;
1677 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1679 foreach my $field ( $marc->field('856') ) {
1680 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1681 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1682 $field->add_subfields( '9' => $shortname );
1683 $field->update( ind2 => '0');
1687 $xml = $marc->as_xml_record;
1688 $xml =~ s/^<\?.+?\?>$//mo;
1690 $xml =~ s/>\s+</></sgo;
1695 $$ LANGUAGE PLPERLU STABLE;
1697 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1699 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1701 my ($marcxml, $shortname) = @_;
1704 use MARC::File::XML;
1709 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1711 foreach my $field ( $marc->field('856') ) {
1712 if ( ! $field->as_string('9') ) {
1713 $field->add_subfields( '9' => $shortname );
1717 $xml = $marc->as_xml_record;
1718 $xml =~ s/^<\?.+?\?>$//mo;
1720 $xml =~ s/>\s+</></sgo;
1725 $$ LANGUAGE PLPERLU STABLE;
1728 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1740 -- Bail out if asked to change the label to ##URI##
1741 IF new_label = '##URI##' THEN
1745 -- Gather information
1746 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1747 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1748 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1750 -- Bail out if the label already is ##URI##
1751 IF old_label = '##URI##' THEN
1755 -- Bail out if the call number label is already correct
1756 IF new_volume = old_volume THEN
1760 -- Check whether we already have a destination volume available
1761 SELECT id INTO new_volume FROM asset.call_number
1764 owning_lib = owner AND
1765 label = new_label AND
1768 -- Create destination volume if needed
1770 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1771 VALUES (1, 1, bib, owner, new_label, cn_class);
1772 SELECT id INTO new_volume FROM asset.call_number
1775 owning_lib = owner AND
1776 label = new_label AND
1780 -- Move copy to destination
1781 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1783 -- Delete source volume if it is now empty
1784 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1786 DELETE FROM asset.call_number WHERE id = old_volume;
1791 $$ LANGUAGE plpgsql;
1793 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1798 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1802 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1803 $zipdata{$zip} = [$city, $state, $county];
1806 if (defined $zipdata{$input}) {
1807 my ($city, $state, $county) = @{$zipdata{$input}};
1808 return [$city, $state, $county];
1809 } elsif (defined $zipdata{substr $input, 0, 5}) {
1810 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1811 return [$city, $state, $county];
1813 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1816 $$ LANGUAGE PLPERLU STABLE;
1818 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1825 errors_found BOOLEAN;
1827 parent_shortname TEXT;
1833 type_parent_depth INT;
1838 errors_found := FALSE;
1840 -- Checking actor.org_unit_type
1842 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1844 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1845 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1847 IF type_parent IS NOT NULL THEN
1849 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1851 IF type_depth - type_parent_depth <> 1 THEN
1852 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1853 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1854 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1855 ou_type_name, type_depth, parent_type, type_parent_depth;
1856 errors_found := TRUE;
1864 -- Checking actor.org_unit
1866 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1868 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1869 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;
1870 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;
1871 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1872 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1873 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;
1874 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;
1876 IF ou_parent IS NOT NULL THEN
1878 IF (org_unit_depth - parent_depth <> 1) OR (
1879 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1881 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1882 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1883 errors_found := TRUE;
1890 IF NOT errors_found THEN
1891 RAISE INFO 'No errors found.';
1898 $$ LANGUAGE plpgsql;
1901 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1905 DELETE FROM asset.opac_visible_copies;
1907 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1909 cp.id, cp.circ_lib, cn.record
1912 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1913 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1914 JOIN asset.copy_location cl ON (cp.location = cl.id)
1915 JOIN config.copy_status cs ON (cp.status = cs.id)
1916 JOIN biblio.record_entry b ON (cn.record = b.id)
1925 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1929 $$ LANGUAGE plpgsql;
1932 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1938 old_owning_lib INTEGER;
1944 -- Gather information
1945 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1946 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1947 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1949 -- Bail out if the new_owning_lib is not the ID of an org_unit
1950 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1952 '% is not a valid actor.org_unit ID; no change made.',
1957 -- Bail out discreetly if the owning_lib is already correct
1958 IF new_owning_lib = old_owning_lib THEN
1962 -- Check whether we already have a destination volume available
1963 SELECT id INTO new_volume FROM asset.call_number
1966 owning_lib = new_owning_lib AND
1967 label = old_label AND
1970 -- Create destination volume if needed
1972 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1973 VALUES (1, 1, bib, new_owning_lib, old_label);
1974 SELECT id INTO new_volume FROM asset.call_number
1977 owning_lib = new_owning_lib AND
1978 label = old_label AND
1982 -- Move copy to destination
1983 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1985 -- Delete source volume if it is now empty
1986 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1988 DELETE FROM asset.call_number WHERE id = old_volume;
1993 $$ LANGUAGE plpgsql;
1996 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1998 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2001 new_owning_lib INTEGER;
2005 -- Parse the new_owner as an org unit ID or shortname
2006 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2007 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2008 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2009 ELSIF new_owner ~ E'^[0-9]+$' THEN
2010 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2013 E'You don\'t need to put the actor.org_unit ID in quotes; '
2014 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2015 new_owning_lib := new_owner::INTEGER;
2016 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2020 '% is not a valid actor.org_unit shortname or ID; no change made.',
2027 $$ LANGUAGE plpgsql;
2029 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2032 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2035 MARC::Charset->assume_unicode(1);
2040 my $r = MARC::Record->new_from_xml( $xml );
2041 my $output_xml = $r->as_xml_record();
2049 $func$ LANGUAGE PLPERLU;
2050 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2052 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2054 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2055 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2056 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2057 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2058 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2059 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2060 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2061 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2062 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2063 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2064 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2065 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2066 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2067 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2068 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2069 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2070 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2071 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2072 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2073 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2074 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2075 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2076 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2077 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2078 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2079 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2081 $FUNC$ LANGUAGE PLPGSQL;
2083 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2085 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2086 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2087 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2088 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2089 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2090 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2091 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2093 -- import any new circ rules
2094 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2095 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2096 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2097 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2099 -- and permission groups
2100 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2103 $FUNC$ LANGUAGE PLPGSQL;
2106 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$
2115 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2116 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2117 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2118 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2119 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2120 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2121 FOR name IN EXECUTE loopq LOOP
2122 EXECUTE existsq INTO ct USING name;
2124 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2125 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2126 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2127 EXECUTE copyst USING name;
2131 $FUNC$ LANGUAGE PLPGSQL;
2133 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2139 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2142 MARC::Charset->assume_unicode(1);
2144 my $target_xml = shift;
2145 my $source_xml = shift;
2151 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2155 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2160 my $source_id = $source->subfield('901', 'c');
2161 $source_id = $source->subfield('903', 'a') unless $source_id;
2162 my $target_id = $target->subfield('901', 'c');
2163 $target_id = $target->subfield('903', 'a') unless $target_id;
2165 my %existing_fields;
2166 foreach my $tag (@$tags) {
2167 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2168 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2169 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2171 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2175 my $xml = $target->as_xml_record;
2176 $xml =~ s/^<\?.+?\?>$//mo;
2178 $xml =~ s/>\s+</></sgo;
2182 $func$ LANGUAGE PLPERLU;
2183 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.';
2185 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2191 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2194 my $in_tags = shift;
2195 my $in_values = shift;
2197 # hack-and-slash parsing of array-passed-as-string;
2198 # this can go away once everybody is running Postgres 9.1+
2199 my $csv = Text::CSV->new({binary => 1});
2202 my $status = $csv->parse($in_tags);
2203 my $tags = [ $csv->fields() ];
2204 $in_values =~ s/^{//;
2205 $in_values =~ s/}$//;
2206 $status = $csv->parse($in_values);
2207 my $values = [ $csv->fields() ];
2209 my $marc = MARC::Record->new();
2211 $marc->leader('00000nam a22000007 4500');
2212 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2214 foreach my $i (0..$#$tags) {
2216 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2219 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2220 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2222 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2226 my $xml = $marc->as_xml_record;
2227 $xml =~ s/^<\?.+?\?>$//mo;
2229 $xml =~ s/>\s+</></sgo;
2233 $func$ LANGUAGE PLPERLU;
2234 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2235 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2236 The second argument is an array of text containing the values to plug into each field.
2237 If the value for a given field is NULL or the empty string, it is not inserted.
2240 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2242 my ($marcxml, $tag, $pos, $value) = @_;
2245 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2249 MARC::Charset->assume_unicode(1);
2251 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2252 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2253 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2254 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2258 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2260 foreach my $field ($marc->field($tag)) {
2261 $field->update("ind$pos" => $value);
2263 $xml = $marc->as_xml_record;
2264 $xml =~ s/^<\?.+?\?>$//mo;
2266 $xml =~ s/>\s+</></sgo;
2270 $func$ LANGUAGE PLPERLU;
2272 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2273 The first argument is a MARCXML string.
2274 The second argument is a MARC tag.
2275 The third argument is the indicator position, either 1 or 2.
2276 The fourth argument is the character to set the indicator value to.
2277 All occurences of the specified field will be changed.
2278 The function returns the revised MARCXML string.$$;
2280 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2285 first_name TEXT DEFAULT '',
2286 last_name TEXT DEFAULT ''
2287 ) RETURNS VOID AS $func$
2289 RAISE NOTICE '%', org ;
2290 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2291 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2292 FROM actor.org_unit aou, permission.grp_tree pgt
2293 WHERE aou.shortname = org
2294 AND pgt.name = perm_group;
2299 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2300 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2302 target_event_def ALIAS FOR $1;
2305 DROP TABLE IF EXISTS new_atevdefs;
2306 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2307 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2308 INSERT INTO action_trigger.event_definition (
2329 ,name || ' (clone of '||target_event_def||')'
2345 action_trigger.event_definition
2347 id = target_event_def
2349 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2350 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2351 INSERT INTO action_trigger.environment (
2357 currval('action_trigger.event_definition_id_seq')
2362 action_trigger.environment
2364 event_def = target_event_def
2366 INSERT INTO action_trigger.event_params (
2371 currval('action_trigger.event_definition_id_seq')
2375 action_trigger.event_params
2377 event_def = target_event_def
2380 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);
2382 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2384 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2385 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2387 target_event_def ALIAS FOR $1;
2389 new_interval ALIAS FOR $3;
2391 DROP TABLE IF EXISTS new_atevdefs;
2392 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2393 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2394 INSERT INTO action_trigger.event_definition (
2415 ,name || ' (clone of '||target_event_def||')'
2431 action_trigger.event_definition
2433 id = target_event_def
2435 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2436 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2437 INSERT INTO action_trigger.environment (
2443 currval('action_trigger.event_definition_id_seq')
2448 action_trigger.environment
2450 event_def = target_event_def
2452 INSERT INTO action_trigger.event_params (
2457 currval('action_trigger.event_definition_id_seq')
2461 action_trigger.event_params
2463 event_def = target_event_def
2466 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);
2468 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2470 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2471 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2474 use MARC::File::XML;
2479 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2480 $field = $marc->field($tag);
2482 return $field->as_string($subfield,$delimiter);
2483 $$ LANGUAGE PLPERLU STABLE;
2485 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2486 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2489 use MARC::File::XML;
2494 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2495 @fields = $marc->field($tag);
2498 foreach my $field (@fields) {
2499 push @texts, $field->as_string($subfield,$delimiter);
2502 $$ LANGUAGE PLPERLU STABLE;
2504 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2505 SELECT action.find_hold_matrix_matchpoint(
2506 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2507 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2508 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2509 (SELECT usr FROM action.hold_request WHERE id = $1),
2510 (SELECT requestor FROM action.hold_request WHERE id = $1)
2514 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2515 SELECT action.hold_request_permit_test(
2516 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2517 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2518 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2519 (SELECT usr FROM action.hold_request WHERE id = $1),
2520 (SELECT requestor FROM action.hold_request WHERE id = $1)
2524 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2525 SELECT action.find_circ_matrix_matchpoint(
2526 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2527 (SELECT target_copy FROM action.circulation WHERE id = $1),
2528 (SELECT usr FROM action.circulation WHERE id = $1),
2530 NULLIF(phone_renewal,false),
2531 NULLIF(desk_renewal,false),
2532 NULLIF(opac_renewal,false),
2534 ) FROM action.circulation WHERE id = $1
2539 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2544 RAISE EXCEPTION 'assertion';
2547 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2549 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2555 RAISE EXCEPTION '%', msg;
2558 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2560 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2563 fail_msg ALIAS FOR $2;
2564 success_msg ALIAS FOR $3;
2567 RAISE EXCEPTION '%', fail_msg;
2571 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2573 -- push bib sequence and return starting value for reserved range
2574 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2576 bib_count ALIAS FOR $1;
2579 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2581 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2586 $$ LANGUAGE PLPGSQL STRICT VOLATILE;