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,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 := BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') );
414 second_given_name := BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') );
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_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
752 attempt_value ALIAS FOR $1;
753 fail_value ALIAS FOR $2;
757 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
764 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
769 $$ LANGUAGE PLPGSQL STRICT STABLE;
771 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
773 attempt_value ALIAS FOR $1;
774 fail_value ALIAS FOR $2;
778 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
785 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
790 $$ LANGUAGE PLPGSQL STRICT STABLE;
792 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
794 attempt_value ALIAS FOR $1;
795 fail_value ALIAS FOR $2;
798 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
799 RAISE EXCEPTION 'too many digits';
802 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;'
809 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
814 $$ LANGUAGE PLPGSQL STRICT STABLE;
816 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
818 attempt_value ALIAS FOR $1;
819 fail_value ALIAS FOR $2;
822 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
823 RAISE EXCEPTION 'too many digits';
826 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;'
833 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
838 $$ LANGUAGE PLPGSQL STRICT STABLE;
840 -- add_codabar_checkdigit
841 -- $barcode source barcode
843 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
844 -- character with a checkdigit computed according to the usual algorithm for library barcodes
845 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
846 -- input string does not meet those requirements, it is returned unchanged.
848 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
851 return $barcode if $barcode !~ /^\d{13,14}$/;
852 $barcode = substr($barcode, 0, 13); # ignore 14th digit
853 my @digits = split //, $barcode;
855 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
856 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
857 my $remainder = $total % 10;
858 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
859 return $barcode . $checkdigit;
860 $$ LANGUAGE PLPERLU STRICT STABLE;
862 -- add_code39mod43_checkdigit
863 -- $barcode source barcode
865 -- If the source string is 13 or 14 characters long and contains only valid
866 -- Code 39 mod 43 characters, adds or replaces the 14th
867 -- character with a checkdigit computed according to the usual algorithm for library barcodes
868 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
869 -- input string does not meet those requirements, it is returned unchanged.
871 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
874 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
875 $barcode = substr($barcode, 0, 13); # ignore 14th character
877 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
878 my %nums = map { $valid_chars[$_] => $_ } (0..42);
881 $total += $nums{$_} foreach split(//, $barcode);
882 my $remainder = $total % 43;
883 my $checkdigit = $valid_chars[$remainder];
884 return $barcode . $checkdigit;
885 $$ LANGUAGE PLPERLU STRICT STABLE;
887 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
893 n_digits INTEGER := 0;
896 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
897 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
898 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
899 IF n_digits = 7 AND areacode <> '' THEN
900 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
901 output := (areacode || '-' || temp);
908 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
910 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
911 my ($marcxml, $pos, $value) = @_;
918 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
919 my $leader = $marc->leader();
920 substr($leader, $pos, 1) = $value;
921 $marc->leader($leader);
922 $xml = $marc->as_xml_record;
923 $xml =~ s/^<\?.+?\?>$//mo;
925 $xml =~ s/>\s+</></sgo;
928 $$ LANGUAGE PLPERLU STABLE;
930 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
931 my ($marcxml, $pos, $value) = @_;
938 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
939 my $f008 = $marc->field('008');
942 my $field = $f008->data();
943 substr($field, $pos, 1) = $value;
944 $f008->update($field);
945 $xml = $marc->as_xml_record;
946 $xml =~ s/^<\?.+?\?>$//mo;
948 $xml =~ s/>\s+</></sgo;
952 $$ LANGUAGE PLPERLU STABLE;
955 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
957 profile ALIAS FOR $1;
959 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
961 $$ LANGUAGE PLPGSQL STRICT STABLE;
964 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
966 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
968 $$ LANGUAGE PLPGSQL STRICT STABLE;
971 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
973 my ($marcxml, $tags) = @_;
981 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
982 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
986 foreach my $field ( $marc->fields() ) {
987 push @incumbents, $field->as_formatted();
990 foreach $field ( $to_insert->fields() ) {
991 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
992 $marc->insert_fields_ordered( ($field) );
996 $xml = $marc->as_xml_record;
997 $xml =~ s/^<\?.+?\?>$//mo;
999 $xml =~ s/>\s+</></sgo;
1004 $$ LANGUAGE PLPERLU STABLE;
1006 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1010 -- First make sure the circ matrix is loaded and the circulations
1011 -- have been staged to the extent possible (but at the very least
1012 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1013 -- circ modifiers must also be in place.
1015 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1023 this_duration_rule INT;
1025 this_max_fine_rule INT;
1026 rcd config.rule_circ_duration%ROWTYPE;
1027 rrf config.rule_recurring_fine%ROWTYPE;
1028 rmf config.rule_max_fine%ROWTYPE;
1035 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1037 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1039 -- Fetch the correct rules for this circulation
1046 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1049 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1050 INTO circ_lib, target_copy, usr, is_renewal ;
1052 INTO this_duration_rule,
1056 recurring_fine_rule,
1058 FROM action.item_user_circ_test(
1064 SELECT INTO rcd * FROM config.rule_circ_duration
1065 WHERE id = this_duration_rule;
1066 SELECT INTO rrf * FROM config.rule_recurring_fine
1067 WHERE id = this_fine_rule;
1068 SELECT INTO rmf * FROM config.rule_max_fine
1069 WHERE id = this_max_fine_rule;
1071 -- Apply the rules to this circulation
1072 EXECUTE ('UPDATE ' || tablename || ' c
1074 duration_rule = rcd.name,
1075 recurring_fine_rule = rrf.name,
1076 max_fine_rule = rmf.name,
1077 duration = rcd.normal,
1078 recurring_fine = rrf.normal,
1081 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1084 renewal_remaining = rcd.max_renewals
1086 config.rule_circ_duration rcd,
1087 config.rule_recurring_fine rrf,
1088 config.rule_max_fine rmf,
1091 rcd.id = ' || this_duration_rule || ' AND
1092 rrf.id = ' || this_fine_rule || ' AND
1093 rmf.id = ' || this_max_fine_rule || ' AND
1094 ac.id = c.target_copy AND
1095 c.id = ' || circ || ';');
1097 -- Keep track of where we are in the process
1099 IF (n % 100 = 0) THEN
1100 RAISE INFO '%', n || ' of ' || n_circs
1101 || ' (' || (100*n/n_circs) || '%) circs updated.';
1109 $$ LANGUAGE plpgsql;
1111 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1115 -- First make sure the circ matrix is loaded and the circulations
1116 -- have been staged to the extent possible (but at the very least
1117 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1118 -- circ modifiers must also be in place.
1120 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1128 this_duration_rule INT;
1130 this_max_fine_rule INT;
1131 rcd config.rule_circ_duration%ROWTYPE;
1132 rrf config.rule_recurring_fine%ROWTYPE;
1133 rmf config.rule_max_fine%ROWTYPE;
1140 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1142 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1144 -- Fetch the correct rules for this circulation
1151 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1154 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1155 INTO circ_lib, target_copy, usr, is_renewal ;
1157 INTO this_duration_rule,
1163 FROM action.find_circ_matrix_matchpoint(
1169 SELECT INTO rcd * FROM config.rule_circ_duration
1170 WHERE id = this_duration_rule;
1171 SELECT INTO rrf * FROM config.rule_recurring_fine
1172 WHERE id = this_fine_rule;
1173 SELECT INTO rmf * FROM config.rule_max_fine
1174 WHERE id = this_max_fine_rule;
1176 -- Apply the rules to this circulation
1177 EXECUTE ('UPDATE ' || tablename || ' c
1179 duration_rule = rcd.name,
1180 recuring_fine_rule = rrf.name,
1181 max_fine_rule = rmf.name,
1182 duration = rcd.normal,
1183 recuring_fine = rrf.normal,
1186 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1189 renewal_remaining = rcd.max_renewals
1191 config.rule_circ_duration rcd,
1192 config.rule_recuring_fine rrf,
1193 config.rule_max_fine rmf,
1196 rcd.id = ' || this_duration_rule || ' AND
1197 rrf.id = ' || this_fine_rule || ' AND
1198 rmf.id = ' || this_max_fine_rule || ' AND
1199 ac.id = c.target_copy AND
1200 c.id = ' || circ || ';');
1202 -- Keep track of where we are in the process
1204 IF (n % 100 = 0) THEN
1205 RAISE INFO '%', n || ' of ' || n_circs
1206 || ' (' || (100*n/n_circs) || '%) circs updated.';
1214 $$ LANGUAGE plpgsql;
1216 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1220 -- First make sure the circ matrix is loaded and the circulations
1221 -- have been staged to the extent possible (but at the very least
1222 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1223 -- circ modifiers must also be in place.
1225 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1233 this_duration_rule INT;
1235 this_max_fine_rule INT;
1236 rcd config.rule_circ_duration%ROWTYPE;
1237 rrf config.rule_recurring_fine%ROWTYPE;
1238 rmf config.rule_max_fine%ROWTYPE;
1245 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1247 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1249 -- Fetch the correct rules for this circulation
1256 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1259 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1260 INTO circ_lib, target_copy, usr, is_renewal ;
1262 INTO this_duration_rule,
1265 (matchpoint).duration_rule,
1266 (matchpoint).recurring_fine_rule,
1267 (matchpoint).max_fine_rule
1268 FROM action.find_circ_matrix_matchpoint(
1274 SELECT INTO rcd * FROM config.rule_circ_duration
1275 WHERE id = this_duration_rule;
1276 SELECT INTO rrf * FROM config.rule_recurring_fine
1277 WHERE id = this_fine_rule;
1278 SELECT INTO rmf * FROM config.rule_max_fine
1279 WHERE id = this_max_fine_rule;
1281 -- Apply the rules to this circulation
1282 EXECUTE ('UPDATE ' || tablename || ' c
1284 duration_rule = rcd.name,
1285 recurring_fine_rule = rrf.name,
1286 max_fine_rule = rmf.name,
1287 duration = rcd.normal,
1288 recurring_fine = rrf.normal,
1291 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1294 renewal_remaining = rcd.max_renewals,
1295 grace_period = rrf.grace_period
1297 config.rule_circ_duration rcd,
1298 config.rule_recurring_fine rrf,
1299 config.rule_max_fine rmf,
1302 rcd.id = ' || this_duration_rule || ' AND
1303 rrf.id = ' || this_fine_rule || ' AND
1304 rmf.id = ' || this_max_fine_rule || ' AND
1305 ac.id = c.target_copy AND
1306 c.id = ' || circ || ';');
1308 -- Keep track of where we are in the process
1310 IF (n % 100 = 0) THEN
1311 RAISE INFO '%', n || ' of ' || n_circs
1312 || ' (' || (100*n/n_circs) || '%) circs updated.';
1320 $$ LANGUAGE plpgsql;
1322 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1326 -- First make sure the circ matrix is loaded and the circulations
1327 -- have been staged to the extent possible (but at the very least
1328 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1329 -- circ modifiers must also be in place.
1331 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1339 this_duration_rule INT;
1341 this_max_fine_rule INT;
1342 rcd config.rule_circ_duration%ROWTYPE;
1343 rrf config.rule_recurring_fine%ROWTYPE;
1344 rmf config.rule_max_fine%ROWTYPE;
1350 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1352 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1354 -- Fetch the correct rules for this circulation
1361 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1364 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1365 INTO circ_lib, target_copy, usr, is_renewal ;
1367 INTO this_duration_rule,
1370 (matchpoint).duration_rule,
1371 (matchpoint).recurring_fine_rule,
1372 (matchpoint).max_fine_rule
1373 FROM action.find_circ_matrix_matchpoint(
1379 SELECT INTO rcd * FROM config.rule_circ_duration
1380 WHERE id = this_duration_rule;
1381 SELECT INTO rrf * FROM config.rule_recurring_fine
1382 WHERE id = this_fine_rule;
1383 SELECT INTO rmf * FROM config.rule_max_fine
1384 WHERE id = this_max_fine_rule;
1386 -- Apply the rules to this circulation
1387 EXECUTE ('UPDATE ' || tablename || ' c
1389 duration_rule = rcd.name,
1390 recurring_fine_rule = rrf.name,
1391 max_fine_rule = rmf.name,
1392 duration = rcd.normal,
1393 recurring_fine = rrf.normal,
1396 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1399 renewal_remaining = rcd.max_renewals,
1400 grace_period = rrf.grace_period
1402 config.rule_circ_duration rcd,
1403 config.rule_recurring_fine rrf,
1404 config.rule_max_fine rmf,
1407 rcd.id = ' || this_duration_rule || ' AND
1408 rrf.id = ' || this_fine_rule || ' AND
1409 rmf.id = ' || this_max_fine_rule || ' AND
1410 ac.id = c.target_copy AND
1411 c.id = ' || circ || ';');
1413 -- Keep track of where we are in the process
1415 IF (n % 100 = 0) THEN
1416 RAISE INFO '%', n || ' of ' || n_circs
1417 || ' (' || (100*n/n_circs) || '%) circs updated.';
1425 $$ LANGUAGE plpgsql;
1430 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1432 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1433 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1435 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1436 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1439 c TEXT := schemaname || '.asset_copy_legacy';
1440 sc TEXT := schemaname || '.asset_stat_cat';
1441 sce TEXT := schemaname || '.asset_stat_cat_entry';
1442 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1448 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1450 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1452 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1453 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1454 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1461 $$ LANGUAGE plpgsql;
1463 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1465 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1466 -- This will assign standing penalties as needed.
1474 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1476 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1478 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1488 $$ LANGUAGE plpgsql;
1491 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1494 INSERT INTO metabib.metarecord (fingerprint, master_record)
1495 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1496 FROM biblio.record_entry b
1498 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)
1499 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1500 ORDER BY b.fingerprint, b.quality DESC;
1501 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1503 FROM biblio.record_entry r
1504 JOIN metabib.metarecord m USING (fingerprint)
1505 WHERE NOT r.deleted;
1508 $$ LANGUAGE plpgsql;
1511 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1514 INSERT INTO metabib.metarecord (fingerprint, master_record)
1515 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1516 FROM biblio.record_entry b
1518 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)
1519 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1520 ORDER BY b.fingerprint, b.quality DESC;
1521 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1523 FROM biblio.record_entry r
1524 JOIN metabib.metarecord m USING (fingerprint)
1526 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);
1529 $$ LANGUAGE plpgsql;
1532 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1534 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1535 -- Then SELECT migration_tools.create_cards('m_foo');
1538 u TEXT := schemaname || '.actor_usr_legacy';
1539 c TEXT := schemaname || '.actor_card';
1543 EXECUTE ('DELETE FROM ' || c || ';');
1544 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1545 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1551 $$ LANGUAGE plpgsql;
1554 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1556 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1558 my ($marcxml, $shortname) = @_;
1561 use MARC::File::XML;
1566 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1568 foreach my $field ( $marc->field('856') ) {
1569 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1570 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1571 $field->add_subfields( '9' => $shortname );
1572 $field->update( ind2 => '0');
1576 $xml = $marc->as_xml_record;
1577 $xml =~ s/^<\?.+?\?>$//mo;
1579 $xml =~ s/>\s+</></sgo;
1584 $$ LANGUAGE PLPERLU STABLE;
1586 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1588 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1590 my ($marcxml, $shortname) = @_;
1593 use MARC::File::XML;
1598 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1600 foreach my $field ( $marc->field('856') ) {
1601 if ( ! $field->as_string('9') ) {
1602 $field->add_subfields( '9' => $shortname );
1606 $xml = $marc->as_xml_record;
1607 $xml =~ s/^<\?.+?\?>$//mo;
1609 $xml =~ s/>\s+</></sgo;
1614 $$ LANGUAGE PLPERLU STABLE;
1617 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1629 -- Bail out if asked to change the label to ##URI##
1630 IF new_label = '##URI##' THEN
1634 -- Gather information
1635 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1636 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1637 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1639 -- Bail out if the label already is ##URI##
1640 IF old_label = '##URI##' THEN
1644 -- Bail out if the call number label is already correct
1645 IF new_volume = old_volume THEN
1649 -- Check whether we already have a destination volume available
1650 SELECT id INTO new_volume FROM asset.call_number
1653 owning_lib = owner AND
1654 label = new_label AND
1657 -- Create destination volume if needed
1659 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1660 VALUES (1, 1, bib, owner, new_label, cn_class);
1661 SELECT id INTO new_volume FROM asset.call_number
1664 owning_lib = owner AND
1665 label = new_label AND
1669 -- Move copy to destination
1670 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1672 -- Delete source volume if it is now empty
1673 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1675 DELETE FROM asset.call_number WHERE id = old_volume;
1680 $$ LANGUAGE plpgsql;
1682 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1687 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1691 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1692 $zipdata{$zip} = [$city, $state, $county];
1695 if (defined $zipdata{$input}) {
1696 my ($city, $state, $county) = @{$zipdata{$input}};
1697 return [$city, $state, $county];
1698 } elsif (defined $zipdata{substr $input, 0, 5}) {
1699 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1700 return [$city, $state, $county];
1702 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1705 $$ LANGUAGE PLPERLU STABLE;
1707 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1714 errors_found BOOLEAN;
1716 parent_shortname TEXT;
1722 type_parent_depth INT;
1727 errors_found := FALSE;
1729 -- Checking actor.org_unit_type
1731 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1733 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1734 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1736 IF type_parent IS NOT NULL THEN
1738 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1740 IF type_depth - type_parent_depth <> 1 THEN
1741 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1742 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1743 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1744 ou_type_name, type_depth, parent_type, type_parent_depth;
1745 errors_found := TRUE;
1753 -- Checking actor.org_unit
1755 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1757 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1758 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;
1759 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;
1760 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1761 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1762 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;
1763 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;
1765 IF ou_parent IS NOT NULL THEN
1767 IF (org_unit_depth - parent_depth <> 1) OR (
1768 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1770 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1771 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1772 errors_found := TRUE;
1779 IF NOT errors_found THEN
1780 RAISE INFO 'No errors found.';
1787 $$ LANGUAGE plpgsql;
1790 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1794 DELETE FROM asset.opac_visible_copies;
1796 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1798 cp.id, cp.circ_lib, cn.record
1801 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1802 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1803 JOIN asset.copy_location cl ON (cp.location = cl.id)
1804 JOIN config.copy_status cs ON (cp.status = cs.id)
1805 JOIN biblio.record_entry b ON (cn.record = b.id)
1814 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1818 $$ LANGUAGE plpgsql;
1821 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1827 old_owning_lib INTEGER;
1833 -- Gather information
1834 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1835 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1836 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1838 -- Bail out if the new_owning_lib is not the ID of an org_unit
1839 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1841 '% is not a valid actor.org_unit ID; no change made.',
1846 -- Bail out discreetly if the owning_lib is already correct
1847 IF new_owning_lib = old_owning_lib THEN
1851 -- Check whether we already have a destination volume available
1852 SELECT id INTO new_volume FROM asset.call_number
1855 owning_lib = new_owning_lib AND
1856 label = old_label AND
1859 -- Create destination volume if needed
1861 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1862 VALUES (1, 1, bib, new_owning_lib, old_label);
1863 SELECT id INTO new_volume FROM asset.call_number
1866 owning_lib = new_owning_lib AND
1867 label = old_label AND
1871 -- Move copy to destination
1872 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1874 -- Delete source volume if it is now empty
1875 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1877 DELETE FROM asset.call_number WHERE id = old_volume;
1882 $$ LANGUAGE plpgsql;
1885 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1887 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1890 new_owning_lib INTEGER;
1894 -- Parse the new_owner as an org unit ID or shortname
1895 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1896 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1897 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1898 ELSIF new_owner ~ E'^[0-9]+$' THEN
1899 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1902 E'You don\'t need to put the actor.org_unit ID in quotes; '
1903 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1904 new_owning_lib := new_owner::INTEGER;
1905 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1909 '% is not a valid actor.org_unit shortname or ID; no change made.',
1916 $$ LANGUAGE plpgsql;
1918 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1921 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1924 MARC::Charset->assume_unicode(1);
1929 my $r = MARC::Record->new_from_xml( $xml );
1930 my $output_xml = $r->as_xml_record();
1938 $func$ LANGUAGE PLPERLU;
1939 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1941 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1943 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1944 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1945 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1946 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1947 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1948 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1949 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1950 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1951 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1952 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1953 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1954 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1955 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1956 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1957 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1958 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1959 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1960 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1961 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1962 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1963 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1964 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1965 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1966 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1967 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1968 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1970 $FUNC$ LANGUAGE PLPGSQL;
1972 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1974 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1975 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1976 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1977 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1978 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1979 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
1980 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
1982 -- import any new circ rules
1983 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1984 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1985 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1986 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1988 -- and permission groups
1989 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1992 $FUNC$ LANGUAGE PLPGSQL;
1995 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$
2004 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2005 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2006 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2007 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2008 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2009 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2010 FOR name IN EXECUTE loopq LOOP
2011 EXECUTE existsq INTO ct USING name;
2013 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2014 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2015 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2016 EXECUTE copyst USING name;
2020 $FUNC$ LANGUAGE PLPGSQL;
2022 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2028 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2031 MARC::Charset->assume_unicode(1);
2033 my $target_xml = shift;
2034 my $source_xml = shift;
2040 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2044 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2049 my $source_id = $source->subfield('901', 'c');
2050 $source_id = $source->subfield('903', 'a') unless $source_id;
2051 my $target_id = $target->subfield('901', 'c');
2052 $target_id = $target->subfield('903', 'a') unless $target_id;
2054 my %existing_fields;
2055 foreach my $tag (@$tags) {
2056 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2057 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2058 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2060 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2064 my $xml = $target->as_xml_record;
2065 $xml =~ s/^<\?.+?\?>$//mo;
2067 $xml =~ s/>\s+</></sgo;
2071 $func$ LANGUAGE PLPERLU;
2072 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.';
2074 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2080 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2083 my $in_tags = shift;
2084 my $in_values = shift;
2086 # hack-and-slash parsing of array-passed-as-string;
2087 # this can go away once everybody is running Postgres 9.1+
2088 my $csv = Text::CSV->new({binary => 1});
2091 my $status = $csv->parse($in_tags);
2092 my $tags = [ $csv->fields() ];
2093 $in_values =~ s/^{//;
2094 $in_values =~ s/}$//;
2095 $status = $csv->parse($in_values);
2096 my $values = [ $csv->fields() ];
2098 my $marc = MARC::Record->new();
2100 $marc->leader('00000nam a22000007 4500');
2101 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2103 foreach my $i (0..$#$tags) {
2105 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2108 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2109 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2111 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2115 my $xml = $marc->as_xml_record;
2116 $xml =~ s/^<\?.+?\?>$//mo;
2118 $xml =~ s/>\s+</></sgo;
2122 $func$ LANGUAGE PLPERLU;
2123 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2124 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2125 The second argument is an array of text containing the values to plug into each field.
2126 If the value for a given field is NULL or the empty string, it is not inserted.
2129 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2131 my ($marcxml, $tag, $pos, $value) = @_;
2134 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2138 MARC::Charset->assume_unicode(1);
2140 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2141 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2142 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2143 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2147 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2149 foreach my $field ($marc->field($tag)) {
2150 $field->update("ind$pos" => $value);
2152 $xml = $marc->as_xml_record;
2153 $xml =~ s/^<\?.+?\?>$//mo;
2155 $xml =~ s/>\s+</></sgo;
2159 $func$ LANGUAGE PLPERLU;
2161 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2162 The first argument is a MARCXML string.
2163 The second argument is a MARC tag.
2164 The third argument is the indicator position, either 1 or 2.
2165 The fourth argument is the character to set the indicator value to.
2166 All occurences of the specified field will be changed.
2167 The function returns the revised MARCXML string.$$;
2169 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2174 first_name TEXT DEFAULT '',
2175 last_name TEXT DEFAULT ''
2176 ) RETURNS VOID AS $func$
2178 RAISE NOTICE '%', org ;
2179 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2180 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2181 FROM actor.org_unit aou, permission.grp_tree pgt
2182 WHERE aou.shortname = org
2183 AND pgt.name = perm_group;
2188 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2189 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2191 target_event_def ALIAS FOR $1;
2194 DROP TABLE IF EXISTS new_atevdefs;
2195 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2196 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2197 INSERT INTO action_trigger.event_definition (
2218 ,name || ' (clone of '||target_event_def||')'
2234 action_trigger.event_definition
2236 id = target_event_def
2238 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2239 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2240 INSERT INTO action_trigger.environment (
2246 currval('action_trigger.event_definition_id_seq')
2251 action_trigger.environment
2253 event_def = target_event_def
2255 INSERT INTO action_trigger.event_params (
2260 currval('action_trigger.event_definition_id_seq')
2264 action_trigger.event_params
2266 event_def = target_event_def
2269 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);
2271 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2273 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2274 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2276 target_event_def ALIAS FOR $1;
2278 new_interval ALIAS FOR $3;
2280 DROP TABLE IF EXISTS new_atevdefs;
2281 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2282 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2283 INSERT INTO action_trigger.event_definition (
2304 ,name || ' (clone of '||target_event_def||')'
2320 action_trigger.event_definition
2322 id = target_event_def
2324 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2325 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2326 INSERT INTO action_trigger.environment (
2332 currval('action_trigger.event_definition_id_seq')
2337 action_trigger.environment
2339 event_def = target_event_def
2341 INSERT INTO action_trigger.event_params (
2346 currval('action_trigger.event_definition_id_seq')
2350 action_trigger.event_params
2352 event_def = target_event_def
2355 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);
2357 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2359 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2360 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2363 use MARC::File::XML;
2368 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2369 $field = $marc->field($tag);
2371 return $field->as_string($subfield,$delimiter);
2372 $$ LANGUAGE PLPERLU STABLE;
2374 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2375 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2378 use MARC::File::XML;
2383 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2384 @fields = $marc->field($tag);
2387 foreach my $field (@fields) {
2388 push @texts, $field->as_string($subfield,$delimiter);
2391 $$ LANGUAGE PLPERLU STABLE;
2393 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2394 SELECT action.find_hold_matrix_matchpoint(
2395 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2396 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2397 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2398 (SELECT usr FROM action.hold_request WHERE id = $1),
2399 (SELECT requestor FROM action.hold_request WHERE id = $1)
2403 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2404 SELECT action.hold_request_permit_test(
2405 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2406 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2407 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2408 (SELECT usr FROM action.hold_request WHERE id = $1),
2409 (SELECT requestor FROM action.hold_request WHERE id = $1)
2413 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2414 SELECT action.find_circ_matrix_matchpoint(
2415 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2416 (SELECT target_copy FROM action.circulation WHERE id = $1),
2417 (SELECT usr FROM action.circulation WHERE id = $1),
2419 NULLIF(phone_renewal,false),
2420 NULLIF(desk_renewal,false),
2421 NULLIF(opac_renewal,false),
2423 ) FROM action.circulation WHERE id = $1