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,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
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 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 ilike '%III%' THEN
401 temp := REGEXP_REPLACE( temp, E'III', '', 'i' );
403 IF temp ilike '%II%' THEN
405 temp := REGEXP_REPLACE( temp, E'II', '', 'i' );
407 IF temp ilike '%IV%' THEN
409 temp := REGEXP_REPLACE( temp, E'IV', '', 'i' );
412 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
413 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
414 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
416 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
418 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
421 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
423 city_state_zip TEXT := $1;
428 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;
429 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
430 IF city_state_zip ~ ',' THEN
431 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
432 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
434 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
435 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
436 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
438 IF city_state_zip ~ E'^\\S+$' THEN
439 city := city_state_zip;
442 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
443 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
447 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
449 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
451 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
455 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
456 IF o::BIGINT < t THEN
463 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
465 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
467 migration_schema ALIAS FOR $1;
471 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
476 $$ LANGUAGE PLPGSQL STRICT STABLE;
478 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
480 migration_schema ALIAS FOR $1;
484 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
489 $$ LANGUAGE PLPGSQL STRICT STABLE;
491 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
493 migration_schema ALIAS FOR $1;
497 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
502 $$ LANGUAGE PLPGSQL STRICT STABLE;
504 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
506 migration_schema ALIAS FOR $1;
510 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
515 $$ LANGUAGE PLPGSQL STRICT STABLE;
517 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
519 migration_schema ALIAS FOR $1;
521 patron_table ALIAS FOR $2;
522 default_patron_profile ALIAS FOR $3;
525 sql_where1 TEXT := '';
526 sql_where2 TEXT := '';
527 sql_where3 TEXT := '';
530 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
532 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
534 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
535 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);
536 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);
537 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);
538 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,'') || ';';
539 --RAISE INFO 'sql = %', sql;
540 PERFORM migration_tools.exec( $1, sql );
542 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
544 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
546 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
549 $$ LANGUAGE PLPGSQL STRICT STABLE;
551 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
553 migration_schema ALIAS FOR $1;
555 item_table ALIAS FOR $2;
558 sql_where1 TEXT := '';
559 sql_where2 TEXT := '';
560 sql_where3 TEXT := '';
563 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
565 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
567 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 ';
568 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);
569 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);
570 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);
571 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,'') || ';';
572 --RAISE INFO 'sql = %', sql;
573 PERFORM migration_tools.exec( $1, sql );
576 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
578 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
581 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
583 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
585 migration_schema ALIAS FOR $1;
586 base_copy_location_map TEXT;
587 item_table ALIAS FOR $2;
590 sql_where1 TEXT := '';
591 sql_where2 TEXT := '';
592 sql_where3 TEXT := '';
595 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
597 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
599 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
600 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);
601 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);
602 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);
603 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,'') || ';';
604 --RAISE INFO 'sql = %', sql;
605 PERFORM migration_tools.exec( $1, sql );
608 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
610 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
613 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
615 -- 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
616 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
618 migration_schema ALIAS FOR $1;
620 circ_table ALIAS FOR $2;
621 item_table ALIAS FOR $3;
622 patron_table ALIAS FOR $4;
625 sql_where1 TEXT := '';
626 sql_where2 TEXT := '';
627 sql_where3 TEXT := '';
628 sql_where4 TEXT := '';
631 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
633 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
635 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 ';
636 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);
637 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);
638 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);
639 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);
640 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,'') || ';';
641 --RAISE INFO 'sql = %', sql;
642 PERFORM migration_tools.exec( $1, sql );
645 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
647 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
650 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
653 -- $barcode source barcode
654 -- $prefix prefix to add to barcode, NULL = add no prefix
655 -- $maxlen maximum length of barcode; default to 14 if left NULL
656 -- $pad padding string to apply to left of source barcode before adding
657 -- prefix and suffix; set to NULL or '' if no padding is desired
658 -- $suffix suffix to add to barcode, NULL = add no suffix
660 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
661 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
663 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
664 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
667 return unless defined $barcode;
669 $prefix = '' unless defined $prefix;
671 $pad = '0' unless defined $pad;
672 $suffix = '' unless defined $suffix;
674 # bail out if adding prefix and suffix would bring new barcode over max length
675 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
677 my $new_barcode = $barcode;
679 my $pad_length = $maxlen - length($prefix) - length($suffix);
680 if (length($barcode) < $pad_length) {
681 # assuming we always want padding on the left
682 # also assuming that it is possible to have the pad string be longer than 1 character
683 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
687 # bail out if adding prefix and suffix would bring new barcode over max length
688 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
690 return "$prefix$new_barcode$suffix";
691 $$ LANGUAGE PLPERLU STABLE;
693 -- remove previous version of this function
694 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
696 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
698 attempt_value ALIAS FOR $1;
699 datatype ALIAS FOR $2;
701 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
702 RETURN attempt_value;
704 WHEN OTHERS THEN RETURN NULL;
706 $$ LANGUAGE PLPGSQL STRICT STABLE;
708 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
710 attempt_value ALIAS FOR $1;
711 fail_value ALIAS FOR $2;
715 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
722 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
727 $$ LANGUAGE PLPGSQL STRICT STABLE;
729 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
731 attempt_value ALIAS FOR $1;
732 fail_value ALIAS FOR $2;
736 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
743 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
748 $$ LANGUAGE PLPGSQL STRICT STABLE;
750 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
752 attempt_value ALIAS FOR $1;
753 fail_value ALIAS FOR $2;
757 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
764 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
769 $$ LANGUAGE PLPGSQL STRICT STABLE;
771 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
773 attempt_value ALIAS FOR $1;
774 fail_value ALIAS FOR $2;
778 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
785 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
790 $$ LANGUAGE PLPGSQL STRICT STABLE;
792 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
794 attempt_value ALIAS FOR $1;
795 fail_value ALIAS FOR $2;
799 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
806 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
811 $$ LANGUAGE PLPGSQL STRICT STABLE;
813 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
815 attempt_value ALIAS FOR $1;
816 fail_value ALIAS FOR $2;
819 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
820 RAISE EXCEPTION 'too many digits';
823 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;'
830 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
835 $$ LANGUAGE PLPGSQL STRICT STABLE;
837 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
839 attempt_value ALIAS FOR $1;
840 fail_value ALIAS FOR $2;
843 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
844 RAISE EXCEPTION 'too many digits';
847 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;'
854 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
859 $$ LANGUAGE PLPGSQL STRICT STABLE;
861 -- add_codabar_checkdigit
862 -- $barcode source barcode
864 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
865 -- character with a checkdigit computed according to the usual algorithm for library barcodes
866 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
867 -- input string does not meet those requirements, it is returned unchanged.
869 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
872 return $barcode if $barcode !~ /^\d{13,14}$/;
873 $barcode = substr($barcode, 0, 13); # ignore 14th digit
874 my @digits = split //, $barcode;
876 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
877 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
878 my $remainder = $total % 10;
879 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
880 return $barcode . $checkdigit;
881 $$ LANGUAGE PLPERLU STRICT STABLE;
883 -- add_code39mod43_checkdigit
884 -- $barcode source barcode
886 -- If the source string is 13 or 14 characters long and contains only valid
887 -- Code 39 mod 43 characters, adds or replaces the 14th
888 -- character with a checkdigit computed according to the usual algorithm for library barcodes
889 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
890 -- input string does not meet those requirements, it is returned unchanged.
892 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
895 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
896 $barcode = substr($barcode, 0, 13); # ignore 14th character
898 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
899 my %nums = map { $valid_chars[$_] => $_ } (0..42);
902 $total += $nums{$_} foreach split(//, $barcode);
903 my $remainder = $total % 43;
904 my $checkdigit = $valid_chars[$remainder];
905 return $barcode . $checkdigit;
906 $$ LANGUAGE PLPERLU STRICT STABLE;
908 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
914 n_digits INTEGER := 0;
917 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
918 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
919 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
920 IF n_digits = 7 AND areacode <> '' THEN
921 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
922 output := (areacode || '-' || temp);
929 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
931 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
932 my ($marcxml, $pos, $value) = @_;
939 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
940 my $leader = $marc->leader();
941 substr($leader, $pos, 1) = $value;
942 $marc->leader($leader);
943 $xml = $marc->as_xml_record;
944 $xml =~ s/^<\?.+?\?>$//mo;
946 $xml =~ s/>\s+</></sgo;
949 $$ LANGUAGE PLPERLU STABLE;
951 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
952 my ($marcxml, $pos, $value) = @_;
959 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
960 my $f008 = $marc->field('008');
963 my $field = $f008->data();
964 substr($field, $pos, 1) = $value;
965 $f008->update($field);
966 $xml = $marc->as_xml_record;
967 $xml =~ s/^<\?.+?\?>$//mo;
969 $xml =~ s/>\s+</></sgo;
973 $$ LANGUAGE PLPERLU STABLE;
976 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
978 profile ALIAS FOR $1;
980 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
982 $$ LANGUAGE PLPGSQL STRICT STABLE;
985 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
987 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
989 $$ LANGUAGE PLPGSQL STRICT STABLE;
992 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
994 my ($marcxml, $tags) = @_;
1002 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1003 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1005 my @incumbents = ();
1007 foreach my $field ( $marc->fields() ) {
1008 push @incumbents, $field->as_formatted();
1011 foreach $field ( $to_insert->fields() ) {
1012 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1013 $marc->insert_fields_ordered( ($field) );
1017 $xml = $marc->as_xml_record;
1018 $xml =~ s/^<\?.+?\?>$//mo;
1020 $xml =~ s/>\s+</></sgo;
1025 $$ LANGUAGE PLPERLU STABLE;
1027 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1031 -- First make sure the circ matrix is loaded and the circulations
1032 -- have been staged to the extent possible (but at the very least
1033 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1034 -- circ modifiers must also be in place.
1036 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1044 this_duration_rule INT;
1046 this_max_fine_rule INT;
1047 rcd config.rule_circ_duration%ROWTYPE;
1048 rrf config.rule_recurring_fine%ROWTYPE;
1049 rmf config.rule_max_fine%ROWTYPE;
1056 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1058 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1060 -- Fetch the correct rules for this circulation
1067 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1070 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1071 INTO circ_lib, target_copy, usr, is_renewal ;
1073 INTO this_duration_rule,
1077 recurring_fine_rule,
1079 FROM action.item_user_circ_test(
1085 SELECT INTO rcd * FROM config.rule_circ_duration
1086 WHERE id = this_duration_rule;
1087 SELECT INTO rrf * FROM config.rule_recurring_fine
1088 WHERE id = this_fine_rule;
1089 SELECT INTO rmf * FROM config.rule_max_fine
1090 WHERE id = this_max_fine_rule;
1092 -- Apply the rules to this circulation
1093 EXECUTE ('UPDATE ' || tablename || ' c
1095 duration_rule = rcd.name,
1096 recurring_fine_rule = rrf.name,
1097 max_fine_rule = rmf.name,
1098 duration = rcd.normal,
1099 recurring_fine = rrf.normal,
1102 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1105 renewal_remaining = rcd.max_renewals
1107 config.rule_circ_duration rcd,
1108 config.rule_recurring_fine rrf,
1109 config.rule_max_fine rmf,
1112 rcd.id = ' || this_duration_rule || ' AND
1113 rrf.id = ' || this_fine_rule || ' AND
1114 rmf.id = ' || this_max_fine_rule || ' AND
1115 ac.id = c.target_copy AND
1116 c.id = ' || circ || ';');
1118 -- Keep track of where we are in the process
1120 IF (n % 100 = 0) THEN
1121 RAISE INFO '%', n || ' of ' || n_circs
1122 || ' (' || (100*n/n_circs) || '%) circs updated.';
1130 $$ LANGUAGE plpgsql;
1132 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1136 -- First make sure the circ matrix is loaded and the circulations
1137 -- have been staged to the extent possible (but at the very least
1138 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1139 -- circ modifiers must also be in place.
1141 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1149 this_duration_rule INT;
1151 this_max_fine_rule INT;
1152 rcd config.rule_circ_duration%ROWTYPE;
1153 rrf config.rule_recurring_fine%ROWTYPE;
1154 rmf config.rule_max_fine%ROWTYPE;
1161 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1163 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1165 -- Fetch the correct rules for this circulation
1172 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1175 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1176 INTO circ_lib, target_copy, usr, is_renewal ;
1178 INTO this_duration_rule,
1184 FROM action.find_circ_matrix_matchpoint(
1190 SELECT INTO rcd * FROM config.rule_circ_duration
1191 WHERE id = this_duration_rule;
1192 SELECT INTO rrf * FROM config.rule_recurring_fine
1193 WHERE id = this_fine_rule;
1194 SELECT INTO rmf * FROM config.rule_max_fine
1195 WHERE id = this_max_fine_rule;
1197 -- Apply the rules to this circulation
1198 EXECUTE ('UPDATE ' || tablename || ' c
1200 duration_rule = rcd.name,
1201 recuring_fine_rule = rrf.name,
1202 max_fine_rule = rmf.name,
1203 duration = rcd.normal,
1204 recuring_fine = rrf.normal,
1207 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1210 renewal_remaining = rcd.max_renewals
1212 config.rule_circ_duration rcd,
1213 config.rule_recuring_fine rrf,
1214 config.rule_max_fine rmf,
1217 rcd.id = ' || this_duration_rule || ' AND
1218 rrf.id = ' || this_fine_rule || ' AND
1219 rmf.id = ' || this_max_fine_rule || ' AND
1220 ac.id = c.target_copy AND
1221 c.id = ' || circ || ';');
1223 -- Keep track of where we are in the process
1225 IF (n % 100 = 0) THEN
1226 RAISE INFO '%', n || ' of ' || n_circs
1227 || ' (' || (100*n/n_circs) || '%) circs updated.';
1235 $$ LANGUAGE plpgsql;
1237 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1241 -- First make sure the circ matrix is loaded and the circulations
1242 -- have been staged to the extent possible (but at the very least
1243 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1244 -- circ modifiers must also be in place.
1246 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1254 this_duration_rule INT;
1256 this_max_fine_rule INT;
1257 rcd config.rule_circ_duration%ROWTYPE;
1258 rrf config.rule_recurring_fine%ROWTYPE;
1259 rmf config.rule_max_fine%ROWTYPE;
1266 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1268 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1270 -- Fetch the correct rules for this circulation
1277 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1280 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1281 INTO circ_lib, target_copy, usr, is_renewal ;
1283 INTO this_duration_rule,
1286 (matchpoint).duration_rule,
1287 (matchpoint).recurring_fine_rule,
1288 (matchpoint).max_fine_rule
1289 FROM action.find_circ_matrix_matchpoint(
1295 SELECT INTO rcd * FROM config.rule_circ_duration
1296 WHERE id = this_duration_rule;
1297 SELECT INTO rrf * FROM config.rule_recurring_fine
1298 WHERE id = this_fine_rule;
1299 SELECT INTO rmf * FROM config.rule_max_fine
1300 WHERE id = this_max_fine_rule;
1302 -- Apply the rules to this circulation
1303 EXECUTE ('UPDATE ' || tablename || ' c
1305 duration_rule = rcd.name,
1306 recurring_fine_rule = rrf.name,
1307 max_fine_rule = rmf.name,
1308 duration = rcd.normal,
1309 recurring_fine = rrf.normal,
1312 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1315 renewal_remaining = rcd.max_renewals,
1316 grace_period = rrf.grace_period
1318 config.rule_circ_duration rcd,
1319 config.rule_recurring_fine rrf,
1320 config.rule_max_fine rmf,
1323 rcd.id = ' || this_duration_rule || ' AND
1324 rrf.id = ' || this_fine_rule || ' AND
1325 rmf.id = ' || this_max_fine_rule || ' AND
1326 ac.id = c.target_copy AND
1327 c.id = ' || circ || ';');
1329 -- Keep track of where we are in the process
1331 IF (n % 100 = 0) THEN
1332 RAISE INFO '%', n || ' of ' || n_circs
1333 || ' (' || (100*n/n_circs) || '%) circs updated.';
1341 $$ LANGUAGE plpgsql;
1343 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1347 -- First make sure the circ matrix is loaded and the circulations
1348 -- have been staged to the extent possible (but at the very least
1349 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1350 -- circ modifiers must also be in place.
1352 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1360 this_duration_rule INT;
1362 this_max_fine_rule INT;
1363 rcd config.rule_circ_duration%ROWTYPE;
1364 rrf config.rule_recurring_fine%ROWTYPE;
1365 rmf config.rule_max_fine%ROWTYPE;
1371 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1373 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1375 -- Fetch the correct rules for this circulation
1382 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1385 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1386 INTO circ_lib, target_copy, usr, is_renewal ;
1388 INTO this_duration_rule,
1391 (matchpoint).duration_rule,
1392 (matchpoint).recurring_fine_rule,
1393 (matchpoint).max_fine_rule
1394 FROM action.find_circ_matrix_matchpoint(
1400 SELECT INTO rcd * FROM config.rule_circ_duration
1401 WHERE id = this_duration_rule;
1402 SELECT INTO rrf * FROM config.rule_recurring_fine
1403 WHERE id = this_fine_rule;
1404 SELECT INTO rmf * FROM config.rule_max_fine
1405 WHERE id = this_max_fine_rule;
1407 -- Apply the rules to this circulation
1408 EXECUTE ('UPDATE ' || tablename || ' c
1410 duration_rule = rcd.name,
1411 recurring_fine_rule = rrf.name,
1412 max_fine_rule = rmf.name,
1413 duration = rcd.normal,
1414 recurring_fine = rrf.normal,
1417 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1420 renewal_remaining = rcd.max_renewals,
1421 grace_period = rrf.grace_period
1423 config.rule_circ_duration rcd,
1424 config.rule_recurring_fine rrf,
1425 config.rule_max_fine rmf,
1428 rcd.id = ' || this_duration_rule || ' AND
1429 rrf.id = ' || this_fine_rule || ' AND
1430 rmf.id = ' || this_max_fine_rule || ' AND
1431 ac.id = c.target_copy AND
1432 c.id = ' || circ || ';');
1434 -- Keep track of where we are in the process
1436 IF (n % 100 = 0) THEN
1437 RAISE INFO '%', n || ' of ' || n_circs
1438 || ' (' || (100*n/n_circs) || '%) circs updated.';
1446 $$ LANGUAGE plpgsql;
1451 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1453 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1454 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1456 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1457 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1460 c TEXT := schemaname || '.asset_copy_legacy';
1461 sc TEXT := schemaname || '.asset_stat_cat';
1462 sce TEXT := schemaname || '.asset_stat_cat_entry';
1463 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1469 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1471 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1473 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1474 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1475 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1482 $$ LANGUAGE plpgsql;
1484 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1486 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1487 -- This will assign standing penalties as needed.
1495 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1497 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1499 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1509 $$ LANGUAGE plpgsql;
1512 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1515 INSERT INTO metabib.metarecord (fingerprint, master_record)
1516 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1517 FROM biblio.record_entry b
1519 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)
1520 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1521 ORDER BY b.fingerprint, b.quality DESC;
1522 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1524 FROM biblio.record_entry r
1525 JOIN metabib.metarecord m USING (fingerprint)
1526 WHERE NOT r.deleted;
1529 $$ LANGUAGE plpgsql;
1532 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1535 INSERT INTO metabib.metarecord (fingerprint, master_record)
1536 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1537 FROM biblio.record_entry b
1539 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)
1540 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1541 ORDER BY b.fingerprint, b.quality DESC;
1542 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1544 FROM biblio.record_entry r
1545 JOIN metabib.metarecord m USING (fingerprint)
1547 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);
1550 $$ LANGUAGE plpgsql;
1553 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1555 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1556 -- Then SELECT migration_tools.create_cards('m_foo');
1559 u TEXT := schemaname || '.actor_usr_legacy';
1560 c TEXT := schemaname || '.actor_card';
1564 EXECUTE ('DELETE FROM ' || c || ';');
1565 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1566 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1572 $$ LANGUAGE plpgsql;
1575 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1577 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1579 my ($marcxml, $shortname) = @_;
1582 use MARC::File::XML;
1587 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1589 foreach my $field ( $marc->field('856') ) {
1590 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1591 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1592 $field->add_subfields( '9' => $shortname );
1593 $field->update( ind2 => '0');
1597 $xml = $marc->as_xml_record;
1598 $xml =~ s/^<\?.+?\?>$//mo;
1600 $xml =~ s/>\s+</></sgo;
1605 $$ LANGUAGE PLPERLU STABLE;
1607 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1609 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1611 my ($marcxml, $shortname) = @_;
1614 use MARC::File::XML;
1619 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1621 foreach my $field ( $marc->field('856') ) {
1622 if ( ! $field->as_string('9') ) {
1623 $field->add_subfields( '9' => $shortname );
1627 $xml = $marc->as_xml_record;
1628 $xml =~ s/^<\?.+?\?>$//mo;
1630 $xml =~ s/>\s+</></sgo;
1635 $$ LANGUAGE PLPERLU STABLE;
1638 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1650 -- Bail out if asked to change the label to ##URI##
1651 IF new_label = '##URI##' THEN
1655 -- Gather information
1656 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1657 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1658 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1660 -- Bail out if the label already is ##URI##
1661 IF old_label = '##URI##' THEN
1665 -- Bail out if the call number label is already correct
1666 IF new_volume = old_volume THEN
1670 -- Check whether we already have a destination volume available
1671 SELECT id INTO new_volume FROM asset.call_number
1674 owning_lib = owner AND
1675 label = new_label AND
1678 -- Create destination volume if needed
1680 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1681 VALUES (1, 1, bib, owner, new_label, cn_class);
1682 SELECT id INTO new_volume FROM asset.call_number
1685 owning_lib = owner AND
1686 label = new_label AND
1690 -- Move copy to destination
1691 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1693 -- Delete source volume if it is now empty
1694 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1696 DELETE FROM asset.call_number WHERE id = old_volume;
1701 $$ LANGUAGE plpgsql;
1703 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1708 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1712 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1713 $zipdata{$zip} = [$city, $state, $county];
1716 if (defined $zipdata{$input}) {
1717 my ($city, $state, $county) = @{$zipdata{$input}};
1718 return [$city, $state, $county];
1719 } elsif (defined $zipdata{substr $input, 0, 5}) {
1720 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1721 return [$city, $state, $county];
1723 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1726 $$ LANGUAGE PLPERLU STABLE;
1728 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1735 errors_found BOOLEAN;
1737 parent_shortname TEXT;
1743 type_parent_depth INT;
1748 errors_found := FALSE;
1750 -- Checking actor.org_unit_type
1752 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1754 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1755 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1757 IF type_parent IS NOT NULL THEN
1759 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1761 IF type_depth - type_parent_depth <> 1 THEN
1762 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1763 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1764 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1765 ou_type_name, type_depth, parent_type, type_parent_depth;
1766 errors_found := TRUE;
1774 -- Checking actor.org_unit
1776 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1778 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1779 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;
1780 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;
1781 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1782 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1783 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;
1784 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;
1786 IF ou_parent IS NOT NULL THEN
1788 IF (org_unit_depth - parent_depth <> 1) OR (
1789 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1791 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1792 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1793 errors_found := TRUE;
1800 IF NOT errors_found THEN
1801 RAISE INFO 'No errors found.';
1808 $$ LANGUAGE plpgsql;
1811 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1815 DELETE FROM asset.opac_visible_copies;
1817 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1819 cp.id, cp.circ_lib, cn.record
1822 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1823 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1824 JOIN asset.copy_location cl ON (cp.location = cl.id)
1825 JOIN config.copy_status cs ON (cp.status = cs.id)
1826 JOIN biblio.record_entry b ON (cn.record = b.id)
1835 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1839 $$ LANGUAGE plpgsql;
1842 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1848 old_owning_lib INTEGER;
1854 -- Gather information
1855 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1856 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1857 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1859 -- Bail out if the new_owning_lib is not the ID of an org_unit
1860 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1862 '% is not a valid actor.org_unit ID; no change made.',
1867 -- Bail out discreetly if the owning_lib is already correct
1868 IF new_owning_lib = old_owning_lib THEN
1872 -- Check whether we already have a destination volume available
1873 SELECT id INTO new_volume FROM asset.call_number
1876 owning_lib = new_owning_lib AND
1877 label = old_label AND
1880 -- Create destination volume if needed
1882 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1883 VALUES (1, 1, bib, new_owning_lib, old_label);
1884 SELECT id INTO new_volume FROM asset.call_number
1887 owning_lib = new_owning_lib AND
1888 label = old_label AND
1892 -- Move copy to destination
1893 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1895 -- Delete source volume if it is now empty
1896 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1898 DELETE FROM asset.call_number WHERE id = old_volume;
1903 $$ LANGUAGE plpgsql;
1906 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1908 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1911 new_owning_lib INTEGER;
1915 -- Parse the new_owner as an org unit ID or shortname
1916 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1917 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1918 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1919 ELSIF new_owner ~ E'^[0-9]+$' THEN
1920 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1923 E'You don\'t need to put the actor.org_unit ID in quotes; '
1924 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1925 new_owning_lib := new_owner::INTEGER;
1926 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1930 '% is not a valid actor.org_unit shortname or ID; no change made.',
1937 $$ LANGUAGE plpgsql;
1939 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1942 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1945 MARC::Charset->assume_unicode(1);
1950 my $r = MARC::Record->new_from_xml( $xml );
1951 my $output_xml = $r->as_xml_record();
1959 $func$ LANGUAGE PLPERLU;
1960 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1962 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1964 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1965 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1966 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1967 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1968 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1969 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1970 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1971 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1972 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1973 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1974 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1975 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1976 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1977 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1978 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1979 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1980 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1981 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1982 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1983 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1984 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1985 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1986 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1987 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1988 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1989 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1991 $FUNC$ LANGUAGE PLPGSQL;
1993 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1995 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1996 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1997 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1998 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1999 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2000 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2001 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2003 -- import any new circ rules
2004 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2005 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2006 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2007 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2009 -- and permission groups
2010 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2013 $FUNC$ LANGUAGE PLPGSQL;
2016 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$
2025 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2026 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2027 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2028 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2029 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2030 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2031 FOR name IN EXECUTE loopq LOOP
2032 EXECUTE existsq INTO ct USING name;
2034 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2035 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2036 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2037 EXECUTE copyst USING name;
2041 $FUNC$ LANGUAGE PLPGSQL;
2043 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2049 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2052 MARC::Charset->assume_unicode(1);
2054 my $target_xml = shift;
2055 my $source_xml = shift;
2061 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2065 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2070 my $source_id = $source->subfield('901', 'c');
2071 $source_id = $source->subfield('903', 'a') unless $source_id;
2072 my $target_id = $target->subfield('901', 'c');
2073 $target_id = $target->subfield('903', 'a') unless $target_id;
2075 my %existing_fields;
2076 foreach my $tag (@$tags) {
2077 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2078 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2079 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2081 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2085 my $xml = $target->as_xml_record;
2086 $xml =~ s/^<\?.+?\?>$//mo;
2088 $xml =~ s/>\s+</></sgo;
2092 $func$ LANGUAGE PLPERLU;
2093 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.';
2095 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2101 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2104 my $in_tags = shift;
2105 my $in_values = shift;
2107 # hack-and-slash parsing of array-passed-as-string;
2108 # this can go away once everybody is running Postgres 9.1+
2109 my $csv = Text::CSV->new({binary => 1});
2112 my $status = $csv->parse($in_tags);
2113 my $tags = [ $csv->fields() ];
2114 $in_values =~ s/^{//;
2115 $in_values =~ s/}$//;
2116 $status = $csv->parse($in_values);
2117 my $values = [ $csv->fields() ];
2119 my $marc = MARC::Record->new();
2121 $marc->leader('00000nam a22000007 4500');
2122 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2124 foreach my $i (0..$#$tags) {
2126 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2129 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2130 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2132 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2136 my $xml = $marc->as_xml_record;
2137 $xml =~ s/^<\?.+?\?>$//mo;
2139 $xml =~ s/>\s+</></sgo;
2143 $func$ LANGUAGE PLPERLU;
2144 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2145 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2146 The second argument is an array of text containing the values to plug into each field.
2147 If the value for a given field is NULL or the empty string, it is not inserted.
2150 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2152 my ($marcxml, $tag, $pos, $value) = @_;
2155 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2159 MARC::Charset->assume_unicode(1);
2161 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2162 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2163 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2164 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2168 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2170 foreach my $field ($marc->field($tag)) {
2171 $field->update("ind$pos" => $value);
2173 $xml = $marc->as_xml_record;
2174 $xml =~ s/^<\?.+?\?>$//mo;
2176 $xml =~ s/>\s+</></sgo;
2180 $func$ LANGUAGE PLPERLU;
2182 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2183 The first argument is a MARCXML string.
2184 The second argument is a MARC tag.
2185 The third argument is the indicator position, either 1 or 2.
2186 The fourth argument is the character to set the indicator value to.
2187 All occurences of the specified field will be changed.
2188 The function returns the revised MARCXML string.$$;
2190 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2195 first_name TEXT DEFAULT '',
2196 last_name TEXT DEFAULT ''
2197 ) RETURNS VOID AS $func$
2199 RAISE NOTICE '%', org ;
2200 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2201 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2202 FROM actor.org_unit aou, permission.grp_tree pgt
2203 WHERE aou.shortname = org
2204 AND pgt.name = perm_group;
2209 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2210 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2212 target_event_def ALIAS FOR $1;
2215 DROP TABLE IF EXISTS new_atevdefs;
2216 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2217 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2218 INSERT INTO action_trigger.event_definition (
2239 ,name || ' (clone of '||target_event_def||')'
2255 action_trigger.event_definition
2257 id = target_event_def
2259 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2260 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2261 INSERT INTO action_trigger.environment (
2267 currval('action_trigger.event_definition_id_seq')
2272 action_trigger.environment
2274 event_def = target_event_def
2276 INSERT INTO action_trigger.event_params (
2281 currval('action_trigger.event_definition_id_seq')
2285 action_trigger.event_params
2287 event_def = target_event_def
2290 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);
2292 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2294 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2295 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2297 target_event_def ALIAS FOR $1;
2299 new_interval ALIAS FOR $3;
2301 DROP TABLE IF EXISTS new_atevdefs;
2302 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2303 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2304 INSERT INTO action_trigger.event_definition (
2325 ,name || ' (clone of '||target_event_def||')'
2341 action_trigger.event_definition
2343 id = target_event_def
2345 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2346 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2347 INSERT INTO action_trigger.environment (
2353 currval('action_trigger.event_definition_id_seq')
2358 action_trigger.environment
2360 event_def = target_event_def
2362 INSERT INTO action_trigger.event_params (
2367 currval('action_trigger.event_definition_id_seq')
2371 action_trigger.event_params
2373 event_def = target_event_def
2376 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);
2378 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2380 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2381 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2384 use MARC::File::XML;
2389 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2390 $field = $marc->field($tag);
2392 return $field->as_string($subfield,$delimiter);
2393 $$ LANGUAGE PLPERLU STABLE;
2395 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2396 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2399 use MARC::File::XML;
2404 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2405 @fields = $marc->field($tag);
2408 foreach my $field (@fields) {
2409 push @texts, $field->as_string($subfield,$delimiter);
2412 $$ LANGUAGE PLPERLU STABLE;
2414 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2415 SELECT action.find_hold_matrix_matchpoint(
2416 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2417 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2418 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2419 (SELECT usr FROM action.hold_request WHERE id = $1),
2420 (SELECT requestor FROM action.hold_request WHERE id = $1)
2424 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2425 SELECT action.hold_request_permit_test(
2426 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2427 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2428 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2429 (SELECT usr FROM action.hold_request WHERE id = $1),
2430 (SELECT requestor FROM action.hold_request WHERE id = $1)
2434 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2435 SELECT action.find_circ_matrix_matchpoint(
2436 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2437 (SELECT target_copy FROM action.circulation WHERE id = $1),
2438 (SELECT usr FROM action.circulation WHERE id = $1),
2440 NULLIF(phone_renewal,false),
2441 NULLIF(desk_renewal,false),
2442 NULLIF(opac_renewal,false),
2444 ) FROM action.circulation WHERE id = $1