1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 production_tables TEXT[];
269 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
270 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
271 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
272 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
279 migration_schema ALIAS FOR $1;
280 production_table ALIAS FOR $2;
281 base_staging_table TEXT;
284 base_staging_table = REPLACE( production_table, '.', '_' );
285 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
286 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
300 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
301 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
303 IF suffix = before_comma THEN
307 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
308 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
309 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
311 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
313 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
315 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
317 full_name TEXT := $1;
319 family_name TEXT := '';
320 first_given_name TEXT := '';
321 second_given_name TEXT := '';
326 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
327 IF temp ilike '%MR.%' THEN
329 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
331 IF temp ilike '%MRS.%' THEN
333 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
335 IF temp ilike '%MS.%' THEN
337 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
339 IF temp ilike '%DR.%' THEN
341 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
343 IF temp ilike '%JR%' THEN
345 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
347 IF temp ilike '%JR,%' THEN
349 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
351 IF temp ilike '%SR%' THEN
353 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
355 IF temp ilike '%SR,%' THEN
357 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
359 IF temp ~ E'\\sII$' THEN
361 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
363 IF temp ~ E'\\sIII$' THEN
365 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
367 IF temp ~ E'\\sIV$' THEN
369 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
372 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
373 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
374 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
376 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
378 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
380 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
382 full_name TEXT := $1;
384 family_name TEXT := '';
385 first_given_name TEXT := '';
386 second_given_name TEXT := '';
391 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
392 IF temp ilike '%MR.%' THEN
394 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
396 IF temp ilike '%MRS.%' THEN
398 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
400 IF temp ilike '%MS.%' THEN
402 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
404 IF temp ilike '%DR.%' THEN
406 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
408 IF temp ilike '%JR.%' THEN
410 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
412 IF temp ilike '%JR,%' THEN
414 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
416 IF temp ilike '%SR.%' THEN
418 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
420 IF temp ilike '%SR,%' THEN
422 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
424 IF temp like '%III%' THEN
426 temp := REGEXP_REPLACE( temp, E'III', '' );
428 IF temp like '%II%' THEN
430 temp := REGEXP_REPLACE( temp, E'II', '' );
432 IF temp like '%IV%' THEN
434 temp := REGEXP_REPLACE( temp, E'IV', '' );
437 temp := REGEXP_REPLACE( temp, '\(\)', '');
438 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
439 family_name := REGEXP_REPLACE( family_name, ',', '' );
440 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
441 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
442 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
443 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
445 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
447 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
451 full_name TEXT := $1;
453 family_name TEXT := '';
454 first_given_name TEXT := '';
455 second_given_name TEXT := '';
459 temp := BTRIM(full_name);
460 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
461 --IF temp ~ '^\S{2,}\.' THEN
462 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
463 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
465 --IF temp ~ '\S{2,}\.$' THEN
466 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
467 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
469 IF temp ilike '%MR.%' THEN
471 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
473 IF temp ilike '%MRS.%' THEN
475 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
477 IF temp ilike '%MS.%' THEN
479 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
481 IF temp ilike '%DR.%' THEN
483 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
485 IF temp ilike '%JR.%' THEN
487 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
489 IF temp ilike '%JR,%' THEN
491 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
493 IF temp ilike '%SR.%' THEN
495 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
497 IF temp ilike '%SR,%' THEN
499 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
501 IF temp like '%III%' THEN
503 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
505 IF temp like '%II%' THEN
507 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
511 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
512 temp := BTRIM(REPLACE( temp, family_name, '' ));
513 family_name := REPLACE( family_name, ',', '' );
515 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
516 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
518 first_given_name := temp;
519 second_given_name := '';
522 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
523 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
524 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
525 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
527 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
528 second_given_name := temp;
529 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
533 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
535 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
537 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
539 city_state_zip TEXT := $1;
544 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;
545 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
546 IF city_state_zip ~ ',' THEN
547 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
548 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
550 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
551 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
552 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
554 IF city_state_zip ~ E'^\\S+$' THEN
555 city := city_state_zip;
558 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
559 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
563 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
565 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
567 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
568 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
570 fullstring TEXT := $1;
580 WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
581 THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
584 fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
586 IF fullstring ~ ',' THEN
587 state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
588 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
590 IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
591 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
592 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
594 IF fullstring ~ E'^\\S+$' THEN
595 scratch1 := fullstring;
598 state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
599 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
604 IF scratch1 ~ '[\$]' THEN
605 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
606 city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
608 IF scratch1 ~ '\s' THEN
609 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
610 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
617 IF scratch2 ~ '^\d' THEN
618 address1 := scratch2;
621 address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
622 address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
626 TRIM(BOTH ' ' FROM address1)
627 ,TRIM(BOTH ' ' FROM address2)
628 ,TRIM(BOTH ' ' FROM city)
629 ,TRIM(BOTH ' ' FROM state)
630 ,TRIM(BOTH ' ' FROM zip)
633 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
635 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
638 use Geo::StreetAddress::US;
640 my $a = Geo::StreetAddress::US->parse_location($address);
643 "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
644 ,"$a->{sec_unit_type} $a->{sec_unit_num}"
649 $$ LANGUAGE PLPERLU STABLE;
651 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
652 CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
653 INSERT INTO migration_tools.usps_suffixes VALUES
686 ('BOULEVARD','BLVD'),
790 ('EXPRESSWAY','EXPY'),
795 ('EXTENSIONS','EXTS'),
906 ('JUNCTIONS','JCTS'),
968 ('MOUNTAINS','MTNS'),
1093 ('STRAVENUE','STRA'),
1113 ('THROUGHWAY','TRWY'),
1120 ('TRAFFICWAY','TRFY'),
1139 ('TURNPIKE','TPKE'),
1141 ('UNDERPASS','UPAS'),
1155 ('VILLAGES','VLGS'),
1186 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
1191 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
1192 street1 := REGEXP_REPLACE( street1, ' ' || _r.suffix_from || '$', ' ' || _r.suffix_to, 'i');
1196 $$ LANGUAGE PLPGSQL STRICT STABLE;
1198 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1202 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
1203 IF o::BIGINT < t THEN
1210 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1212 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1214 migration_schema ALIAS FOR $1;
1218 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1223 $$ LANGUAGE PLPGSQL STRICT STABLE;
1225 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1227 migration_schema ALIAS FOR $1;
1231 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1236 $$ LANGUAGE PLPGSQL STRICT STABLE;
1238 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1240 migration_schema ALIAS FOR $1;
1244 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1249 $$ LANGUAGE PLPGSQL STRICT STABLE;
1251 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1253 migration_schema ALIAS FOR $1;
1257 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1262 $$ LANGUAGE PLPGSQL STRICT STABLE;
1264 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1266 migration_schema ALIAS FOR $1;
1268 patron_table ALIAS FOR $2;
1269 default_patron_profile ALIAS FOR $3;
1272 sql_where1 TEXT := '';
1273 sql_where2 TEXT := '';
1274 sql_where3 TEXT := '';
1277 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1279 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1281 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1282 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);
1283 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);
1284 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);
1285 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,'') || ';';
1286 --RAISE INFO 'sql = %', sql;
1287 PERFORM migration_tools.exec( $1, sql );
1289 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
1291 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1293 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1296 $$ LANGUAGE PLPGSQL STRICT STABLE;
1298 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1300 migration_schema ALIAS FOR $1;
1302 item_table ALIAS FOR $2;
1305 sql_where1 TEXT := '';
1306 sql_where2 TEXT := '';
1307 sql_where3 TEXT := '';
1310 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1312 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1314 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 ';
1315 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);
1316 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);
1317 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);
1318 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,'') || ';';
1319 --RAISE INFO 'sql = %', sql;
1320 PERFORM migration_tools.exec( $1, sql );
1323 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1325 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1328 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1330 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1332 migration_schema ALIAS FOR $1;
1333 base_copy_location_map TEXT;
1334 item_table ALIAS FOR $2;
1337 sql_where1 TEXT := '';
1338 sql_where2 TEXT := '';
1339 sql_where3 TEXT := '';
1342 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1344 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1346 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1347 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);
1348 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);
1349 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);
1350 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,'') || ';';
1351 --RAISE INFO 'sql = %', sql;
1352 PERFORM migration_tools.exec( $1, sql );
1355 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1357 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1360 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1362 -- 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
1363 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1365 migration_schema ALIAS FOR $1;
1367 circ_table ALIAS FOR $2;
1368 item_table ALIAS FOR $3;
1369 patron_table ALIAS FOR $4;
1372 sql_where1 TEXT := '';
1373 sql_where2 TEXT := '';
1374 sql_where3 TEXT := '';
1375 sql_where4 TEXT := '';
1378 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1380 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1382 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 ';
1383 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);
1384 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);
1385 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);
1386 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);
1387 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,'') || ';';
1388 --RAISE INFO 'sql = %', sql;
1389 PERFORM migration_tools.exec( $1, sql );
1392 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1394 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1397 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1400 -- $barcode source barcode
1401 -- $prefix prefix to add to barcode, NULL = add no prefix
1402 -- $maxlen maximum length of barcode; default to 14 if left NULL
1403 -- $pad padding string to apply to left of source barcode before adding
1404 -- prefix and suffix; set to NULL or '' if no padding is desired
1405 -- $suffix suffix to add to barcode, NULL = add no suffix
1407 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1408 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1410 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1411 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1414 return unless defined $barcode;
1416 $prefix = '' unless defined $prefix;
1418 $pad = '0' unless defined $pad;
1419 $suffix = '' unless defined $suffix;
1421 # bail out if adding prefix and suffix would bring new barcode over max length
1422 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1424 my $new_barcode = $barcode;
1426 my $pad_length = $maxlen - length($prefix) - length($suffix);
1427 if (length($barcode) < $pad_length) {
1428 # assuming we always want padding on the left
1429 # also assuming that it is possible to have the pad string be longer than 1 character
1430 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1434 # bail out if adding prefix and suffix would bring new barcode over max length
1435 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1437 return "$prefix$new_barcode$suffix";
1438 $$ LANGUAGE PLPERLU STABLE;
1440 -- remove previous version of this function
1441 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1443 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1445 attempt_value ALIAS FOR $1;
1446 datatype ALIAS FOR $2;
1448 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1449 RETURN attempt_value;
1451 WHEN OTHERS THEN RETURN NULL;
1453 $$ LANGUAGE PLPGSQL STRICT STABLE;
1455 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1457 attempt_value ALIAS FOR $1;
1458 fail_value ALIAS FOR $2;
1462 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1469 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1474 $$ LANGUAGE PLPGSQL STRICT STABLE;
1476 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1478 attempt_value ALIAS FOR $1;
1479 fail_value ALIAS FOR $2;
1483 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1490 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1495 $$ LANGUAGE PLPGSQL STRICT STABLE;
1497 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1499 attempt_value ALIAS FOR $1;
1500 fail_value ALIAS FOR $2;
1504 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1511 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1516 $$ LANGUAGE PLPGSQL STRICT STABLE;
1518 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1520 attempt_value ALIAS FOR $1;
1521 fail_value ALIAS FOR $2;
1524 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1529 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1534 $$ LANGUAGE PLPGSQL STRICT STABLE;
1536 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1538 attempt_value ALIAS FOR $1;
1539 fail_value ALIAS FOR $2;
1540 output NUMERIC(8,2);
1543 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1550 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1555 $$ LANGUAGE PLPGSQL STRICT STABLE;
1557 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1559 attempt_value ALIAS FOR $1;
1560 fail_value ALIAS FOR $2;
1561 output NUMERIC(6,2);
1564 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1571 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1576 $$ LANGUAGE PLPGSQL STRICT STABLE;
1578 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1580 attempt_value ALIAS FOR $1;
1581 fail_value ALIAS FOR $2;
1582 output NUMERIC(8,2);
1584 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1585 RAISE EXCEPTION 'too many digits';
1588 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;'
1595 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1600 $$ LANGUAGE PLPGSQL STRICT STABLE;
1602 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1604 attempt_value ALIAS FOR $1;
1605 fail_value ALIAS FOR $2;
1606 output NUMERIC(6,2);
1608 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1609 RAISE EXCEPTION 'too many digits';
1612 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;'
1619 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1624 $$ LANGUAGE PLPGSQL STRICT STABLE;
1626 -- add_codabar_checkdigit
1627 -- $barcode source barcode
1629 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1630 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1631 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
1632 -- input string does not meet those requirements, it is returned unchanged.
1634 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1635 my $barcode = shift;
1637 return $barcode if $barcode !~ /^\d{13,14}$/;
1638 $barcode = substr($barcode, 0, 13); # ignore 14th digit
1639 my @digits = split //, $barcode;
1641 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1642 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1643 my $remainder = $total % 10;
1644 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1645 return $barcode . $checkdigit;
1646 $$ LANGUAGE PLPERLU STRICT STABLE;
1648 -- add_code39mod43_checkdigit
1649 -- $barcode source barcode
1651 -- If the source string is 13 or 14 characters long and contains only valid
1652 -- Code 39 mod 43 characters, adds or replaces the 14th
1653 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1654 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
1655 -- input string does not meet those requirements, it is returned unchanged.
1657 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1658 my $barcode = shift;
1660 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1661 $barcode = substr($barcode, 0, 13); # ignore 14th character
1663 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1664 my %nums = map { $valid_chars[$_] => $_ } (0..42);
1667 $total += $nums{$_} foreach split(//, $barcode);
1668 my $remainder = $total % 43;
1669 my $checkdigit = $valid_chars[$remainder];
1670 return $barcode . $checkdigit;
1671 $$ LANGUAGE PLPERLU STRICT STABLE;
1673 -- add_mod16_checkdigit
1674 -- $barcode source barcode
1676 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1678 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1679 my $barcode = shift;
1681 my @digits = split //, $barcode;
1683 foreach $digit (@digits) {
1684 if ($digit =~ /[0-9]/) { $total += $digit;
1685 } elsif ($digit eq '-') { $total += 10;
1686 } elsif ($digit eq '$') { $total += 11;
1687 } elsif ($digit eq ':') { $total += 12;
1688 } elsif ($digit eq '/') { $total += 13;
1689 } elsif ($digit eq '.') { $total += 14;
1690 } elsif ($digit eq '+') { $total += 15;
1691 } elsif ($digit eq 'A') { $total += 16;
1692 } elsif ($digit eq 'B') { $total += 17;
1693 } elsif ($digit eq 'C') { $total += 18;
1694 } elsif ($digit eq 'D') { $total += 19;
1695 } else { die "invalid digit <$digit>";
1698 my $remainder = $total % 16;
1699 my $difference = 16 - $remainder;
1701 if ($difference < 10) { $checkdigit = $difference;
1702 } elsif ($difference == 10) { $checkdigit = '-';
1703 } elsif ($difference == 11) { $checkdigit = '$';
1704 } elsif ($difference == 12) { $checkdigit = ':';
1705 } elsif ($difference == 13) { $checkdigit = '/';
1706 } elsif ($difference == 14) { $checkdigit = '.';
1707 } elsif ($difference == 15) { $checkdigit = '+';
1708 } else { die "error calculating checkdigit";
1711 return $barcode . $checkdigit;
1712 $$ LANGUAGE PLPERLU STRICT STABLE;
1714 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1717 areacode TEXT := $2;
1720 n_digits INTEGER := 0;
1723 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1724 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1725 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1726 IF n_digits = 7 AND areacode <> '' THEN
1727 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1728 output := (areacode || '-' || temp);
1735 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1737 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1738 my ($marcxml, $pos, $value) = @_;
1741 use MARC::File::XML;
1745 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1746 my $leader = $marc->leader();
1747 substr($leader, $pos, 1) = $value;
1748 $marc->leader($leader);
1749 $xml = $marc->as_xml_record;
1750 $xml =~ s/^<\?.+?\?>$//mo;
1752 $xml =~ s/>\s+</></sgo;
1755 $$ LANGUAGE PLPERLU STABLE;
1757 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1758 my ($marcxml, $pos, $value) = @_;
1761 use MARC::File::XML;
1765 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1766 my $f008 = $marc->field('008');
1769 my $field = $f008->data();
1770 substr($field, $pos, 1) = $value;
1771 $f008->update($field);
1772 $xml = $marc->as_xml_record;
1773 $xml =~ s/^<\?.+?\?>$//mo;
1775 $xml =~ s/>\s+</></sgo;
1779 $$ LANGUAGE PLPERLU STABLE;
1782 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1784 profile ALIAS FOR $1;
1786 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1788 $$ LANGUAGE PLPGSQL STRICT STABLE;
1791 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1793 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1795 $$ LANGUAGE PLPGSQL STRICT STABLE;
1798 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1800 my ($marcxml, $tags) = @_;
1803 use MARC::File::XML;
1808 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1809 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1811 my @incumbents = ();
1813 foreach my $field ( $marc->fields() ) {
1814 push @incumbents, $field->as_formatted();
1817 foreach $field ( $to_insert->fields() ) {
1818 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1819 $marc->insert_fields_ordered( ($field) );
1823 $xml = $marc->as_xml_record;
1824 $xml =~ s/^<\?.+?\?>$//mo;
1826 $xml =~ s/>\s+</></sgo;
1831 $$ LANGUAGE PLPERLU STABLE;
1833 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1837 -- First make sure the circ matrix is loaded and the circulations
1838 -- have been staged to the extent possible (but at the very least
1839 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1840 -- circ modifiers must also be in place.
1842 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1850 this_duration_rule INT;
1852 this_max_fine_rule INT;
1853 rcd config.rule_circ_duration%ROWTYPE;
1854 rrf config.rule_recurring_fine%ROWTYPE;
1855 rmf config.rule_max_fine%ROWTYPE;
1862 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1864 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1866 -- Fetch the correct rules for this circulation
1873 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1876 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1877 INTO circ_lib, target_copy, usr, is_renewal ;
1879 INTO this_duration_rule,
1883 recurring_fine_rule,
1885 FROM action.item_user_circ_test(
1891 SELECT INTO rcd * FROM config.rule_circ_duration
1892 WHERE id = this_duration_rule;
1893 SELECT INTO rrf * FROM config.rule_recurring_fine
1894 WHERE id = this_fine_rule;
1895 SELECT INTO rmf * FROM config.rule_max_fine
1896 WHERE id = this_max_fine_rule;
1898 -- Apply the rules to this circulation
1899 EXECUTE ('UPDATE ' || tablename || ' c
1901 duration_rule = rcd.name,
1902 recurring_fine_rule = rrf.name,
1903 max_fine_rule = rmf.name,
1904 duration = rcd.normal,
1905 recurring_fine = rrf.normal,
1908 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1911 renewal_remaining = rcd.max_renewals
1913 config.rule_circ_duration rcd,
1914 config.rule_recurring_fine rrf,
1915 config.rule_max_fine rmf,
1918 rcd.id = ' || this_duration_rule || ' AND
1919 rrf.id = ' || this_fine_rule || ' AND
1920 rmf.id = ' || this_max_fine_rule || ' AND
1921 ac.id = c.target_copy AND
1922 c.id = ' || circ || ';');
1924 -- Keep track of where we are in the process
1926 IF (n % 100 = 0) THEN
1927 RAISE INFO '%', n || ' of ' || n_circs
1928 || ' (' || (100*n/n_circs) || '%) circs updated.';
1936 $$ LANGUAGE plpgsql;
1938 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1942 -- First make sure the circ matrix is loaded and the circulations
1943 -- have been staged to the extent possible (but at the very least
1944 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1945 -- circ modifiers must also be in place.
1947 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1955 this_duration_rule INT;
1957 this_max_fine_rule INT;
1958 rcd config.rule_circ_duration%ROWTYPE;
1959 rrf config.rule_recurring_fine%ROWTYPE;
1960 rmf config.rule_max_fine%ROWTYPE;
1967 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1969 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1971 -- Fetch the correct rules for this circulation
1978 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1981 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1982 INTO circ_lib, target_copy, usr, is_renewal ;
1984 INTO this_duration_rule,
1990 FROM action.find_circ_matrix_matchpoint(
1996 SELECT INTO rcd * FROM config.rule_circ_duration
1997 WHERE id = this_duration_rule;
1998 SELECT INTO rrf * FROM config.rule_recurring_fine
1999 WHERE id = this_fine_rule;
2000 SELECT INTO rmf * FROM config.rule_max_fine
2001 WHERE id = this_max_fine_rule;
2003 -- Apply the rules to this circulation
2004 EXECUTE ('UPDATE ' || tablename || ' c
2006 duration_rule = rcd.name,
2007 recuring_fine_rule = rrf.name,
2008 max_fine_rule = rmf.name,
2009 duration = rcd.normal,
2010 recuring_fine = rrf.normal,
2013 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2016 renewal_remaining = rcd.max_renewals
2018 config.rule_circ_duration rcd,
2019 config.rule_recuring_fine rrf,
2020 config.rule_max_fine rmf,
2023 rcd.id = ' || this_duration_rule || ' AND
2024 rrf.id = ' || this_fine_rule || ' AND
2025 rmf.id = ' || this_max_fine_rule || ' AND
2026 ac.id = c.target_copy AND
2027 c.id = ' || circ || ';');
2029 -- Keep track of where we are in the process
2031 IF (n % 100 = 0) THEN
2032 RAISE INFO '%', n || ' of ' || n_circs
2033 || ' (' || (100*n/n_circs) || '%) circs updated.';
2041 $$ LANGUAGE plpgsql;
2043 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2047 -- First make sure the circ matrix is loaded and the circulations
2048 -- have been staged to the extent possible (but at the very least
2049 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2050 -- circ modifiers must also be in place.
2052 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2060 this_duration_rule INT;
2062 this_max_fine_rule INT;
2063 rcd config.rule_circ_duration%ROWTYPE;
2064 rrf config.rule_recurring_fine%ROWTYPE;
2065 rmf config.rule_max_fine%ROWTYPE;
2072 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2074 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2076 -- Fetch the correct rules for this circulation
2083 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2086 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2087 INTO circ_lib, target_copy, usr, is_renewal ;
2089 INTO this_duration_rule,
2092 (matchpoint).duration_rule,
2093 (matchpoint).recurring_fine_rule,
2094 (matchpoint).max_fine_rule
2095 FROM action.find_circ_matrix_matchpoint(
2101 SELECT INTO rcd * FROM config.rule_circ_duration
2102 WHERE id = this_duration_rule;
2103 SELECT INTO rrf * FROM config.rule_recurring_fine
2104 WHERE id = this_fine_rule;
2105 SELECT INTO rmf * FROM config.rule_max_fine
2106 WHERE id = this_max_fine_rule;
2108 -- Apply the rules to this circulation
2109 EXECUTE ('UPDATE ' || tablename || ' c
2111 duration_rule = rcd.name,
2112 recurring_fine_rule = rrf.name,
2113 max_fine_rule = rmf.name,
2114 duration = rcd.normal,
2115 recurring_fine = rrf.normal,
2118 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2121 renewal_remaining = rcd.max_renewals,
2122 grace_period = rrf.grace_period
2124 config.rule_circ_duration rcd,
2125 config.rule_recurring_fine rrf,
2126 config.rule_max_fine rmf,
2129 rcd.id = ' || this_duration_rule || ' AND
2130 rrf.id = ' || this_fine_rule || ' AND
2131 rmf.id = ' || this_max_fine_rule || ' AND
2132 ac.id = c.target_copy AND
2133 c.id = ' || circ || ';');
2135 -- Keep track of where we are in the process
2137 IF (n % 100 = 0) THEN
2138 RAISE INFO '%', n || ' of ' || n_circs
2139 || ' (' || (100*n/n_circs) || '%) circs updated.';
2147 $$ LANGUAGE plpgsql;
2149 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2150 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2151 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2154 charge_lost_on_zero BOOLEAN;
2157 default_price NUMERIC;
2158 working_price NUMERIC;
2162 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2163 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2165 SELECT INTO charge_lost_on_zero value
2166 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2168 SELECT INTO min_price value
2169 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2171 SELECT INTO max_price value
2172 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2174 SELECT INTO default_price value
2175 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2177 SELECT INTO working_price price FROM asset.copy WHERE id = item;
2179 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2180 working_price := default_price;
2183 IF (max_price IS NOT NULL AND working_price > max_price) THEN
2184 working_price := max_price;
2187 IF (min_price IS NOT NULL AND working_price < min_price) THEN
2188 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2189 working_price := min_price;
2193 RETURN working_price;
2197 $$ LANGUAGE plpgsql;
2199 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2203 -- First make sure the circ matrix is loaded and the circulations
2204 -- have been staged to the extent possible (but at the very least
2205 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
2206 -- circ modifiers must also be in place.
2208 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2216 this_duration_rule INT;
2218 this_max_fine_rule INT;
2219 rcd config.rule_circ_duration%ROWTYPE;
2220 rrf config.rule_recurring_fine%ROWTYPE;
2221 rmf config.rule_max_fine%ROWTYPE;
2227 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2229 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2231 -- Fetch the correct rules for this circulation
2238 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2241 FROM ' || tablename || ' WHERE id = ' || circ || ';')
2242 INTO circ_lib, target_copy, usr, is_renewal ;
2244 INTO this_duration_rule,
2247 (matchpoint).duration_rule,
2248 (matchpoint).recurring_fine_rule,
2249 (matchpoint).max_fine_rule
2250 FROM action.find_circ_matrix_matchpoint(
2256 SELECT INTO rcd * FROM config.rule_circ_duration
2257 WHERE id = this_duration_rule;
2258 SELECT INTO rrf * FROM config.rule_recurring_fine
2259 WHERE id = this_fine_rule;
2260 SELECT INTO rmf * FROM config.rule_max_fine
2261 WHERE id = this_max_fine_rule;
2263 -- Apply the rules to this circulation
2264 EXECUTE ('UPDATE ' || tablename || ' c
2266 duration_rule = rcd.name,
2267 recurring_fine_rule = rrf.name,
2268 max_fine_rule = rmf.name,
2269 duration = rcd.normal,
2270 recurring_fine = rrf.normal,
2273 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2276 renewal_remaining = rcd.max_renewals,
2277 grace_period = rrf.grace_period
2279 config.rule_circ_duration rcd,
2280 config.rule_recurring_fine rrf,
2281 config.rule_max_fine rmf,
2284 rcd.id = ' || this_duration_rule || ' AND
2285 rrf.id = ' || this_fine_rule || ' AND
2286 rmf.id = ' || this_max_fine_rule || ' AND
2287 ac.id = c.target_copy AND
2288 c.id = ' || circ || ';');
2290 -- Keep track of where we are in the process
2292 IF (n % 100 = 0) THEN
2293 RAISE INFO '%', n || ' of ' || n_circs
2294 || ' (' || (100*n/n_circs) || '%) circs updated.';
2302 $$ LANGUAGE plpgsql;
2307 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2309 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2310 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2312 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2313 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2316 c TEXT := schemaname || '.asset_copy_legacy';
2317 sc TEXT := schemaname || '.asset_stat_cat';
2318 sce TEXT := schemaname || '.asset_stat_cat_entry';
2319 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2325 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2327 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2329 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2330 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2331 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2338 $$ LANGUAGE plpgsql;
2340 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2342 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2343 -- This will assign standing penalties as needed.
2351 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2353 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2355 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2365 $$ LANGUAGE plpgsql;
2368 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2371 INSERT INTO metabib.metarecord (fingerprint, master_record)
2372 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2373 FROM biblio.record_entry b
2375 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)
2376 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2377 ORDER BY b.fingerprint, b.quality DESC;
2378 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2380 FROM biblio.record_entry r
2381 JOIN metabib.metarecord m USING (fingerprint)
2382 WHERE NOT r.deleted;
2385 $$ LANGUAGE plpgsql;
2388 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2391 INSERT INTO metabib.metarecord (fingerprint, master_record)
2392 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2393 FROM biblio.record_entry b
2395 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)
2396 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2397 ORDER BY b.fingerprint, b.quality DESC;
2398 INSERT INTO metabib.metarecord_source_map (metarecord, source)
2400 FROM biblio.record_entry r
2401 JOIN metabib.metarecord m USING (fingerprint)
2403 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);
2406 $$ LANGUAGE plpgsql;
2409 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2411 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2412 -- Then SELECT migration_tools.create_cards('m_foo');
2415 u TEXT := schemaname || '.actor_usr_legacy';
2416 c TEXT := schemaname || '.actor_card';
2420 EXECUTE ('DELETE FROM ' || c || ';');
2421 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2422 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2428 $$ LANGUAGE plpgsql;
2431 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2433 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2435 my ($marcxml, $shortname) = @_;
2438 use MARC::File::XML;
2443 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2445 foreach my $field ( $marc->field('856') ) {
2446 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2447 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2448 $field->add_subfields( '9' => $shortname );
2449 $field->update( ind2 => '0');
2453 $xml = $marc->as_xml_record;
2454 $xml =~ s/^<\?.+?\?>$//mo;
2456 $xml =~ s/>\s+</></sgo;
2461 $$ LANGUAGE PLPERLU STABLE;
2463 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2465 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2467 my ($marcxml, $shortname) = @_;
2470 use MARC::File::XML;
2475 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2477 foreach my $field ( $marc->field('856') ) {
2478 if ( ! $field->as_string('9') ) {
2479 $field->add_subfields( '9' => $shortname );
2483 $xml = $marc->as_xml_record;
2484 $xml =~ s/^<\?.+?\?>$//mo;
2486 $xml =~ s/>\s+</></sgo;
2491 $$ LANGUAGE PLPERLU STABLE;
2494 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2506 -- Bail out if asked to change the label to ##URI##
2507 IF new_label = '##URI##' THEN
2511 -- Gather information
2512 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2513 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2514 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2516 -- Bail out if the label already is ##URI##
2517 IF old_label = '##URI##' THEN
2521 -- Bail out if the call number label is already correct
2522 IF new_volume = old_volume THEN
2526 -- Check whether we already have a destination volume available
2527 SELECT id INTO new_volume FROM asset.call_number
2530 owning_lib = owner AND
2531 label = new_label AND
2534 -- Create destination volume if needed
2536 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
2537 VALUES (1, 1, bib, owner, new_label, cn_class);
2538 SELECT id INTO new_volume FROM asset.call_number
2541 owning_lib = owner AND
2542 label = new_label AND
2546 -- Move copy to destination
2547 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2549 -- Delete source volume if it is now empty
2550 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2552 DELETE FROM asset.call_number WHERE id = old_volume;
2557 $$ LANGUAGE plpgsql;
2559 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2564 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2568 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2569 $zipdata{$zip} = [$city, $state, $county];
2572 if (defined $zipdata{$input}) {
2573 my ($city, $state, $county) = @{$zipdata{$input}};
2574 return [$city, $state, $county];
2575 } elsif (defined $zipdata{substr $input, 0, 5}) {
2576 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2577 return [$city, $state, $county];
2579 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2582 $$ LANGUAGE PLPERLU STABLE;
2584 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2591 errors_found BOOLEAN;
2593 parent_shortname TEXT;
2599 type_parent_depth INT;
2604 errors_found := FALSE;
2606 -- Checking actor.org_unit_type
2608 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2610 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2611 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2613 IF type_parent IS NOT NULL THEN
2615 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2617 IF type_depth - type_parent_depth <> 1 THEN
2618 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2619 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2620 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2621 ou_type_name, type_depth, parent_type, type_parent_depth;
2622 errors_found := TRUE;
2630 -- Checking actor.org_unit
2632 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2634 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2635 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;
2636 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;
2637 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2638 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2639 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;
2640 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;
2642 IF ou_parent IS NOT NULL THEN
2644 IF (org_unit_depth - parent_depth <> 1) OR (
2645 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2647 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
2648 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2649 errors_found := TRUE;
2656 IF NOT errors_found THEN
2657 RAISE INFO 'No errors found.';
2664 $$ LANGUAGE plpgsql;
2667 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2671 DELETE FROM asset.opac_visible_copies;
2673 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2675 cp.id, cp.circ_lib, cn.record
2678 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2679 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2680 JOIN asset.copy_location cl ON (cp.location = cl.id)
2681 JOIN config.copy_status cs ON (cp.status = cs.id)
2682 JOIN biblio.record_entry b ON (cn.record = b.id)
2691 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2695 $$ LANGUAGE plpgsql;
2698 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2704 old_owning_lib INTEGER;
2710 -- Gather information
2711 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2712 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2713 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2715 -- Bail out if the new_owning_lib is not the ID of an org_unit
2716 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2718 '% is not a valid actor.org_unit ID; no change made.',
2723 -- Bail out discreetly if the owning_lib is already correct
2724 IF new_owning_lib = old_owning_lib THEN
2728 -- Check whether we already have a destination volume available
2729 SELECT id INTO new_volume FROM asset.call_number
2732 owning_lib = new_owning_lib AND
2733 label = old_label AND
2736 -- Create destination volume if needed
2738 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
2739 VALUES (1, 1, bib, new_owning_lib, old_label);
2740 SELECT id INTO new_volume FROM asset.call_number
2743 owning_lib = new_owning_lib AND
2744 label = old_label AND
2748 -- Move copy to destination
2749 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2751 -- Delete source volume if it is now empty
2752 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2754 DELETE FROM asset.call_number WHERE id = old_volume;
2759 $$ LANGUAGE plpgsql;
2762 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2764 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2767 new_owning_lib INTEGER;
2771 -- Parse the new_owner as an org unit ID or shortname
2772 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2773 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2774 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2775 ELSIF new_owner ~ E'^[0-9]+$' THEN
2776 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2779 E'You don\'t need to put the actor.org_unit ID in quotes; '
2780 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2781 new_owning_lib := new_owner::INTEGER;
2782 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2786 '% is not a valid actor.org_unit shortname or ID; no change made.',
2793 $$ LANGUAGE plpgsql;
2795 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2798 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2801 MARC::Charset->assume_unicode(1);
2806 my $r = MARC::Record->new_from_xml( $xml );
2807 my $output_xml = $r->as_xml_record();
2815 $func$ LANGUAGE PLPERLU;
2816 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2818 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2820 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2821 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2822 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
2823 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2824 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2825 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
2826 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2827 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2828 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
2829 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2830 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2831 $$) TO '$$ || dir || $$/asset_copy_location'$$;
2832 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2833 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2834 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2835 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2836 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2837 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
2838 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2839 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2840 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
2841 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
2842 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
2843 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
2844 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
2845 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
2847 $FUNC$ LANGUAGE PLPGSQL;
2849 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2851 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2852 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2853 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2854 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2855 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2856 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2857 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2859 -- import any new circ rules
2860 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2861 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2862 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2863 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2865 -- and permission groups
2866 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2869 $FUNC$ LANGUAGE PLPGSQL;
2872 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$
2881 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2882 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2883 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2884 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2885 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2886 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2887 FOR name IN EXECUTE loopq LOOP
2888 EXECUTE existsq INTO ct USING name;
2890 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2891 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2892 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2893 EXECUTE copyst USING name;
2897 $FUNC$ LANGUAGE PLPGSQL;
2899 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2906 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2907 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
2908 FOR id IN EXECUTE loopq USING delimiter LOOP
2909 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2910 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2911 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2912 EXECUTE splitst USING id, delimiter;
2915 $FUNC$ LANGUAGE PLPGSQL;
2917 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2923 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2926 MARC::Charset->assume_unicode(1);
2928 my $target_xml = shift;
2929 my $source_xml = shift;
2935 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2939 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2944 my $source_id = $source->subfield('901', 'c');
2945 $source_id = $source->subfield('903', 'a') unless $source_id;
2946 my $target_id = $target->subfield('901', 'c');
2947 $target_id = $target->subfield('903', 'a') unless $target_id;
2949 my %existing_fields;
2950 foreach my $tag (@$tags) {
2951 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2952 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2953 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2955 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2959 my $xml = $target->as_xml_record;
2960 $xml =~ s/^<\?.+?\?>$//mo;
2962 $xml =~ s/>\s+</></sgo;
2966 $func$ LANGUAGE PLPERLU;
2967 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.';
2969 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2975 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2978 my $in_tags = shift;
2979 my $in_values = shift;
2981 # hack-and-slash parsing of array-passed-as-string;
2982 # this can go away once everybody is running Postgres 9.1+
2983 my $csv = Text::CSV->new({binary => 1});
2986 my $status = $csv->parse($in_tags);
2987 my $tags = [ $csv->fields() ];
2988 $in_values =~ s/^{//;
2989 $in_values =~ s/}$//;
2990 $status = $csv->parse($in_values);
2991 my $values = [ $csv->fields() ];
2993 my $marc = MARC::Record->new();
2995 $marc->leader('00000nam a22000007 4500');
2996 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2998 foreach my $i (0..$#$tags) {
3000 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3003 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3004 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3006 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3010 my $xml = $marc->as_xml_record;
3011 $xml =~ s/^<\?.+?\?>$//mo;
3013 $xml =~ s/>\s+</></sgo;
3017 $func$ LANGUAGE PLPERLU;
3018 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3019 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3020 The second argument is an array of text containing the values to plug into each field.
3021 If the value for a given field is NULL or the empty string, it is not inserted.
3024 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3026 my ($marcxml, $tag, $pos, $value) = @_;
3029 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3033 MARC::Charset->assume_unicode(1);
3035 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3036 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3037 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3038 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3042 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3044 foreach my $field ($marc->field($tag)) {
3045 $field->update("ind$pos" => $value);
3047 $xml = $marc->as_xml_record;
3048 $xml =~ s/^<\?.+?\?>$//mo;
3050 $xml =~ s/>\s+</></sgo;
3054 $func$ LANGUAGE PLPERLU;
3056 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3057 The first argument is a MARCXML string.
3058 The second argument is a MARC tag.
3059 The third argument is the indicator position, either 1 or 2.
3060 The fourth argument is the character to set the indicator value to.
3061 All occurences of the specified field will be changed.
3062 The function returns the revised MARCXML string.$$;
3064 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3069 first_name TEXT DEFAULT '',
3070 last_name TEXT DEFAULT ''
3071 ) RETURNS VOID AS $func$
3073 RAISE NOTICE '%', org ;
3074 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3075 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3076 FROM actor.org_unit aou, permission.grp_tree pgt
3077 WHERE aou.shortname = org
3078 AND pgt.name = perm_group;
3083 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3084 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3086 target_event_def ALIAS FOR $1;
3089 DROP TABLE IF EXISTS new_atevdefs;
3090 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3091 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3092 INSERT INTO action_trigger.event_definition (
3113 ,name || ' (clone of '||target_event_def||')'
3129 action_trigger.event_definition
3131 id = target_event_def
3133 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3134 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3135 INSERT INTO action_trigger.environment (
3141 currval('action_trigger.event_definition_id_seq')
3146 action_trigger.environment
3148 event_def = target_event_def
3150 INSERT INTO action_trigger.event_params (
3155 currval('action_trigger.event_definition_id_seq')
3159 action_trigger.event_params
3161 event_def = target_event_def
3164 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);
3166 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3168 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3169 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3171 target_event_def ALIAS FOR $1;
3173 new_interval ALIAS FOR $3;
3175 DROP TABLE IF EXISTS new_atevdefs;
3176 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3177 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3178 INSERT INTO action_trigger.event_definition (
3199 ,name || ' (clone of '||target_event_def||')'
3215 action_trigger.event_definition
3217 id = target_event_def
3219 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3220 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3221 INSERT INTO action_trigger.environment (
3227 currval('action_trigger.event_definition_id_seq')
3232 action_trigger.environment
3234 event_def = target_event_def
3236 INSERT INTO action_trigger.event_params (
3241 currval('action_trigger.event_definition_id_seq')
3245 action_trigger.event_params
3247 event_def = target_event_def
3250 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);
3252 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3254 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3255 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3258 target_event_defs ALIAS FOR $2;
3260 DROP TABLE IF EXISTS new_atevdefs;
3261 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3262 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3263 INSERT INTO action_trigger.event_definition (
3284 ,name || ' (clone of '||target_event_defs[i]||')'
3300 action_trigger.event_definition
3302 id = target_event_defs[i]
3304 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3305 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3306 INSERT INTO action_trigger.environment (
3312 currval('action_trigger.event_definition_id_seq')
3317 action_trigger.environment
3319 event_def = target_event_defs[i]
3321 INSERT INTO action_trigger.event_params (
3326 currval('action_trigger.event_definition_id_seq')
3330 action_trigger.event_params
3332 event_def = target_event_defs[i]
3335 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
3337 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3339 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
3341 action_trigger.event
3345 ,complete_time = NULL
3346 ,update_process = NULL
3348 ,template_output = NULL
3349 ,error_output = NULL
3350 ,async_output = NULL
3355 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
3359 use MARC::File::XML;
3364 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3365 $field = $marc->leader();
3368 $$ LANGUAGE PLPERLU STABLE;
3370 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
3371 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3374 use MARC::File::XML;
3379 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3380 $field = $marc->field($tag);
3382 return $field->as_string($subfield,$delimiter);
3383 $$ LANGUAGE PLPERLU STABLE;
3385 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
3386 my ($marcxml, $tag, $subfield, $delimiter) = @_;
3389 use MARC::File::XML;
3394 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3395 @fields = $marc->field($tag);
3398 foreach my $field (@fields) {
3399 push @texts, $field->as_string($subfield,$delimiter);
3402 $$ LANGUAGE PLPERLU STABLE;
3404 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
3405 SELECT action.find_hold_matrix_matchpoint(
3406 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3407 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3408 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3409 (SELECT usr FROM action.hold_request WHERE id = $1),
3410 (SELECT requestor FROM action.hold_request WHERE id = $1)
3414 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
3415 SELECT action.hold_request_permit_test(
3416 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
3417 (SELECT request_lib FROM action.hold_request WHERE id = $1),
3418 (SELECT current_copy FROM action.hold_request WHERE id = $1),
3419 (SELECT usr FROM action.hold_request WHERE id = $1),
3420 (SELECT requestor FROM action.hold_request WHERE id = $1)
3424 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
3425 SELECT action.find_circ_matrix_matchpoint(
3426 (SELECT circ_lib FROM action.circulation WHERE id = $1),
3427 (SELECT target_copy FROM action.circulation WHERE id = $1),
3428 (SELECT usr FROM action.circulation WHERE id = $1),
3430 NULLIF(phone_renewal,false),
3431 NULLIF(desk_renewal,false),
3432 NULLIF(opac_renewal,false),
3434 ) FROM action.circulation WHERE id = $1
3439 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
3444 RAISE EXCEPTION 'assertion';
3447 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3449 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
3455 RAISE EXCEPTION '%', msg;
3458 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3460 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
3463 fail_msg ALIAS FOR $2;
3464 success_msg ALIAS FOR $3;
3467 RAISE EXCEPTION '%', fail_msg;
3471 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3473 -- push bib sequence and return starting value for reserved range
3474 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
3476 bib_count ALIAS FOR $1;
3479 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
3481 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
3486 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3488 -- set a new salted password
3490 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
3492 usr_id ALIAS FOR $1;
3493 plain_passwd ALIAS FOR $2;
3498 SELECT actor.create_salt('main') INTO plain_salt;
3500 SELECT MD5(plain_passwd) INTO md5_passwd;
3502 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
3507 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3510 -- convenience functions for handling copy_location maps
3512 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
3514 table_schema ALIAS FOR $1;
3515 table_name ALIAS FOR $2;
3516 org_shortname ALIAS FOR $3;
3517 org_range ALIAS FOR $4;
3520 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
3521 -- though we'll still use the passed org for the full path traversal when needed
3522 x_org_found BOOLEAN;
3527 EXECUTE 'SELECT EXISTS (
3529 FROM information_schema.columns
3530 WHERE table_schema = $1
3532 and column_name = ''desired_shelf''
3533 )' INTO proceed USING table_schema, table_name;
3535 RAISE EXCEPTION 'Missing column desired_shelf';
3538 EXECUTE 'SELECT EXISTS (
3540 FROM information_schema.columns
3541 WHERE table_schema = $1
3543 and column_name = ''x_org''
3544 )' INTO x_org_found USING table_schema, table_name;
3546 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3548 RAISE EXCEPTION 'Cannot find org by shortname';
3551 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3553 EXECUTE 'ALTER TABLE '
3554 || quote_ident(table_name)
3555 || ' DROP COLUMN IF EXISTS x_shelf';
3556 EXECUTE 'ALTER TABLE '
3557 || quote_ident(table_name)
3558 || ' ADD COLUMN x_shelf INTEGER';
3561 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3562 || ' SET x_shelf = id FROM asset_copy_location b'
3563 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3564 || ' AND b.owning_lib = x_org'
3565 || ' AND NOT b.deleted';
3566 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3567 || ' SET x_shelf = id FROM asset.copy_location b'
3568 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3569 || ' AND b.owning_lib = x_org'
3570 || ' AND x_shelf IS NULL'
3571 || ' AND NOT b.deleted';
3573 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3574 || ' SET x_shelf = id FROM asset_copy_location b'
3575 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3576 || ' AND b.owning_lib = $1'
3577 || ' AND NOT b.deleted'
3579 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3580 || ' SET x_shelf = id FROM asset_copy_location b'
3581 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3582 || ' AND b.owning_lib = $1'
3583 || ' AND x_shelf IS NULL'
3584 || ' AND NOT b.deleted'
3588 FOREACH o IN ARRAY org_list LOOP
3589 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3590 || ' SET x_shelf = id FROM asset.copy_location b'
3591 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3592 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3593 || ' AND NOT b.deleted'
3597 EXECUTE 'SELECT migration_tools.assert(
3598 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3599 ''Cannot find a desired location'',
3600 ''Found all desired locations''
3604 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3606 -- convenience functions for handling circmod maps
3608 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3610 table_schema ALIAS FOR $1;
3611 table_name ALIAS FOR $2;
3614 EXECUTE 'SELECT EXISTS (
3616 FROM information_schema.columns
3617 WHERE table_schema = $1
3619 and column_name = ''desired_circmod''
3620 )' INTO proceed USING table_schema, table_name;
3622 RAISE EXCEPTION 'Missing column desired_circmod';
3625 EXECUTE 'ALTER TABLE '
3626 || quote_ident(table_name)
3627 || ' DROP COLUMN IF EXISTS x_circmod';
3628 EXECUTE 'ALTER TABLE '
3629 || quote_ident(table_name)
3630 || ' ADD COLUMN x_circmod TEXT';
3632 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3633 || ' SET x_circmod = code FROM config.circ_modifier b'
3634 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3636 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3637 || ' SET x_circmod = code FROM config.circ_modifier b'
3638 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3639 || ' AND x_circmod IS NULL';
3641 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3642 || ' SET x_circmod = code FROM config.circ_modifier b'
3643 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3644 || ' AND x_circmod IS NULL';
3646 EXECUTE 'SELECT migration_tools.assert(
3647 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3648 ''Cannot find a desired circulation modifier'',
3649 ''Found all desired circulation modifiers''
3653 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3655 -- convenience functions for handling item status maps
3657 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3659 table_schema ALIAS FOR $1;
3660 table_name ALIAS FOR $2;
3663 EXECUTE 'SELECT EXISTS (
3665 FROM information_schema.columns
3666 WHERE table_schema = $1
3668 and column_name = ''desired_status''
3669 )' INTO proceed USING table_schema, table_name;
3671 RAISE EXCEPTION 'Missing column desired_status';
3674 EXECUTE 'ALTER TABLE '
3675 || quote_ident(table_name)
3676 || ' DROP COLUMN IF EXISTS x_status';
3677 EXECUTE 'ALTER TABLE '
3678 || quote_ident(table_name)
3679 || ' ADD COLUMN x_status INTEGER';
3681 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3682 || ' SET x_status = id FROM config.copy_status b'
3683 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3685 EXECUTE 'SELECT migration_tools.assert(
3686 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3687 ''Cannot find a desired copy status'',
3688 ''Found all desired copy statuses''
3692 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3694 -- convenience functions for handling org maps
3696 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3698 table_schema ALIAS FOR $1;
3699 table_name ALIAS FOR $2;
3702 EXECUTE 'SELECT EXISTS (
3704 FROM information_schema.columns
3705 WHERE table_schema = $1
3707 and column_name = ''desired_org''
3708 )' INTO proceed USING table_schema, table_name;
3710 RAISE EXCEPTION 'Missing column desired_org';
3713 EXECUTE 'ALTER TABLE '
3714 || quote_ident(table_name)
3715 || ' DROP COLUMN IF EXISTS x_org';
3716 EXECUTE 'ALTER TABLE '
3717 || quote_ident(table_name)
3718 || ' ADD COLUMN x_org INTEGER';
3720 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3721 || ' SET x_org = id FROM actor.org_unit b'
3722 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3724 EXECUTE 'SELECT migration_tools.assert(
3725 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3726 ''Cannot find a desired org unit'',
3727 ''Found all desired org units''
3731 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3733 -- convenience function for handling desired_not_migrate
3735 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3737 table_schema ALIAS FOR $1;
3738 table_name ALIAS FOR $2;
3741 EXECUTE 'SELECT EXISTS (
3743 FROM information_schema.columns
3744 WHERE table_schema = $1
3746 and column_name = ''desired_not_migrate''
3747 )' INTO proceed USING table_schema, table_name;
3749 RAISE EXCEPTION 'Missing column desired_not_migrate';
3752 EXECUTE 'ALTER TABLE '
3753 || quote_ident(table_name)
3754 || ' DROP COLUMN IF EXISTS x_migrate';
3755 EXECUTE 'ALTER TABLE '
3756 || quote_ident(table_name)
3757 || ' ADD COLUMN x_migrate BOOLEAN';
3759 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3760 || ' SET x_migrate = CASE'
3761 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3762 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3763 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3764 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3765 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3766 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3769 EXECUTE 'SELECT migration_tools.assert(
3770 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3771 ''Not all desired_not_migrate values understood'',
3772 ''All desired_not_migrate values understood''
3776 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3778 -- convenience function for handling desired_not_migrate
3780 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3782 table_schema ALIAS FOR $1;
3783 table_name ALIAS FOR $2;
3786 EXECUTE 'SELECT EXISTS (
3788 FROM information_schema.columns
3789 WHERE table_schema = $1
3791 and column_name = ''desired_barred_or_blocked''
3792 )' INTO proceed USING table_schema, table_name;
3794 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
3797 EXECUTE 'ALTER TABLE '
3798 || quote_ident(table_name)
3799 || ' DROP COLUMN IF EXISTS x_barred';
3800 EXECUTE 'ALTER TABLE '
3801 || quote_ident(table_name)
3802 || ' ADD COLUMN x_barred BOOLEAN';
3804 EXECUTE 'ALTER TABLE '
3805 || quote_ident(table_name)
3806 || ' DROP COLUMN IF EXISTS x_blocked';
3807 EXECUTE 'ALTER TABLE '
3808 || quote_ident(table_name)
3809 || ' ADD COLUMN x_blocked BOOLEAN';
3811 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3812 || ' SET x_barred = CASE'
3813 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3814 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3815 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3816 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3819 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3820 || ' SET x_blocked = CASE'
3821 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3822 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3823 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3824 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3827 EXECUTE 'SELECT migration_tools.assert(
3828 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3829 ''Not all desired_barred_or_blocked values understood'',
3830 ''All desired_barred_or_blocked values understood''
3834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3836 -- convenience function for handling desired_profile
3838 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3840 table_schema ALIAS FOR $1;
3841 table_name ALIAS FOR $2;
3844 EXECUTE 'SELECT EXISTS (
3846 FROM information_schema.columns
3847 WHERE table_schema = $1
3849 and column_name = ''desired_profile''
3850 )' INTO proceed USING table_schema, table_name;
3852 RAISE EXCEPTION 'Missing column desired_profile';
3855 EXECUTE 'ALTER TABLE '
3856 || quote_ident(table_name)
3857 || ' DROP COLUMN IF EXISTS x_profile';
3858 EXECUTE 'ALTER TABLE '
3859 || quote_ident(table_name)
3860 || ' ADD COLUMN x_profile INTEGER';
3862 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3863 || ' SET x_profile = b.id FROM permission.grp_tree b'
3864 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3866 EXECUTE 'SELECT migration_tools.assert(
3867 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3868 ''Cannot find a desired profile'',
3869 ''Found all desired profiles''
3873 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3875 -- convenience function for handling desired actor stat cats
3877 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3879 table_schema ALIAS FOR $1;
3880 table_name ALIAS FOR $2;
3881 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3882 org_shortname ALIAS FOR $4;
3890 SELECT 'desired_sc' || field_suffix INTO sc;
3891 SELECT 'desired_sce' || field_suffix INTO sce;
3893 EXECUTE 'SELECT EXISTS (
3895 FROM information_schema.columns
3896 WHERE table_schema = $1
3898 and column_name = $3
3899 )' INTO proceed USING table_schema, table_name, sc;
3901 RAISE EXCEPTION 'Missing column %', sc;
3903 EXECUTE 'SELECT EXISTS (
3905 FROM information_schema.columns
3906 WHERE table_schema = $1
3908 and column_name = $3
3909 )' INTO proceed USING table_schema, table_name, sce;
3911 RAISE EXCEPTION 'Missing column %', sce;
3914 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3916 RAISE EXCEPTION 'Cannot find org by shortname';
3918 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3920 -- caller responsible for their own truncates though we try to prevent duplicates
3921 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3926 ' || quote_ident(table_name) || '
3928 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3932 WHERE owner = ANY ($2)
3933 AND name = BTRIM('||sc||')
3938 WHERE owner = ANY ($2)
3939 AND name = BTRIM('||sc||')
3942 USING org, org_list;
3944 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3949 WHERE owner = ANY ($2)
3950 AND BTRIM('||sc||') = BTRIM(name))
3953 WHERE owner = ANY ($2)
3954 AND BTRIM('||sc||') = BTRIM(name))
3959 ' || quote_ident(table_name) || '
3961 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3962 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3965 FROM actor.stat_cat_entry
3969 WHERE owner = ANY ($2)
3970 AND BTRIM('||sc||') = BTRIM(name)
3971 ) AND value = BTRIM('||sce||')
3972 AND owner = ANY ($2)
3976 FROM actor_stat_cat_entry
3980 WHERE owner = ANY ($2)
3981 AND BTRIM('||sc||') = BTRIM(name)
3982 ) AND value = BTRIM('||sce||')
3983 AND owner = ANY ($2)
3986 USING org, org_list;
3988 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3990 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3992 table_schema ALIAS FOR $1;
3993 table_name ALIAS FOR $2;
3994 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3995 org_shortname ALIAS FOR $4;
4003 SELECT 'desired_sc' || field_suffix INTO sc;
4004 SELECT 'desired_sce' || field_suffix INTO sce;
4005 EXECUTE 'SELECT EXISTS (
4007 FROM information_schema.columns
4008 WHERE table_schema = $1
4010 and column_name = $3
4011 )' INTO proceed USING table_schema, table_name, sc;
4013 RAISE EXCEPTION 'Missing column %', sc;
4015 EXECUTE 'SELECT EXISTS (
4017 FROM information_schema.columns
4018 WHERE table_schema = $1
4020 and column_name = $3
4021 )' INTO proceed USING table_schema, table_name, sce;
4023 RAISE EXCEPTION 'Missing column %', sce;
4026 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4028 RAISE EXCEPTION 'Cannot find org by shortname';
4031 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4033 EXECUTE 'ALTER TABLE '
4034 || quote_ident(table_name)
4035 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4036 EXECUTE 'ALTER TABLE '
4037 || quote_ident(table_name)
4038 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
4039 EXECUTE 'ALTER TABLE '
4040 || quote_ident(table_name)
4041 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
4042 EXECUTE 'ALTER TABLE '
4043 || quote_ident(table_name)
4044 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
4047 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4049 x_sc' || field_suffix || ' = id
4051 (SELECT id, name, owner FROM actor_stat_cat
4052 UNION SELECT id, name, owner FROM actor.stat_cat) u
4054 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
4055 AND u.owner = ANY ($1);'
4058 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
4060 x_sce' || field_suffix || ' = id
4062 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
4063 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
4065 u.stat_cat = x_sc' || field_suffix || '
4066 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
4067 AND u.owner = ANY ($1);'
4070 EXECUTE 'SELECT migration_tools.assert(
4071 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
4072 ''Cannot find a desired stat cat'',
4073 ''Found all desired stat cats''
4076 EXECUTE 'SELECT migration_tools.assert(
4077 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
4078 ''Cannot find a desired stat cat entry'',
4079 ''Found all desired stat cat entries''
4083 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4085 -- convenience functions for adding shelving locations
4086 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
4087 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4093 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4096 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4097 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4098 IF return_id IS NOT NULL THEN
4106 $$ LANGUAGE plpgsql;
4108 -- may remove later but testing using this with new migration scripts and not loading acls until go live
4110 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
4111 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
4117 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
4120 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
4122 SELECT INTO return_id id FROM
4123 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
4124 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
4125 IF return_id IS NOT NULL THEN
4133 $$ LANGUAGE plpgsql;
4135 -- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
4136 DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
4137 CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
4145 use MARC::File::XML (BinaryEncoding => 'utf8');
4147 binmode(STDERR, ':bytes');
4148 binmode(STDOUT, ':utf8');
4149 binmode(STDERR, ':utf8');
4151 my $marc_xml = shift;
4152 my $new_9_to_set = shift;
4154 $marc_xml =~ s/(<leader>.........)./${1}a/;
4157 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4160 #elog("could not parse $bibid: $@\n");
4161 import MARC::File::XML (BinaryEncoding => 'utf8');
4165 my @uris = $marc_xml->field('856');
4166 return $marc_xml->as_xml_record() unless @uris;
4168 foreach my $field (@uris) {
4169 my $ind1 = $field->indicator('1');
4170 if (!defined $ind1) { next; }
4171 if ($ind1 ne '1' && $ind1 ne '4') { next; }
4172 my $ind2 = $field->indicator('2');
4173 if (!defined $ind2) { next; }
4174 if ($ind2 ne '0' && $ind2 ne '1') { next; }
4175 $field->add_subfields( '9' => $new_9_to_set );
4178 return $marc_xml->as_xml_record();
4182 -- yet another subfield 9 function, this one only adds the $9 and forces
4183 -- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
4184 DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
4185 CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
4193 use MARC::File::XML (BinaryEncoding => 'utf8');
4195 binmode(STDERR, ':bytes');
4196 binmode(STDOUT, ':utf8');
4197 binmode(STDERR, ':utf8');
4199 my $marc_xml = shift;
4200 my $new_9_to_set = shift;
4202 $marc_xml =~ s/(<leader>.........)./${1}a/;
4205 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4208 #elog("could not parse $bibid: $@\n");
4209 import MARC::File::XML (BinaryEncoding => 'utf8');
4213 my @uris = $marc_xml->field('856');
4214 return $marc_xml->as_xml_record() unless @uris;
4216 foreach my $field (@uris) {
4217 my $ind1 = $field->indicator('1');
4218 if (!defined $ind1) { next; }
4219 if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
4220 my $ind2 = $field->indicator('2');
4221 if (!defined $ind2) { next; }
4222 if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
4223 $field->add_subfields( '9' => $new_9_to_set );
4226 return $marc_xml->as_xml_record();
4230 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
4231 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
4232 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
4240 use MARC::File::XML (BinaryEncoding => 'utf8');
4242 binmode(STDERR, ':bytes');
4243 binmode(STDOUT, ':utf8');
4244 binmode(STDERR, ':utf8');
4246 my $marc_xml = shift;
4247 my $matching_u_text = shift;
4248 my $new_9_to_set = shift;
4250 $marc_xml =~ s/(<leader>.........)./${1}a/;
4253 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4256 #elog("could not parse $bibid: $@\n");
4257 import MARC::File::XML (BinaryEncoding => 'utf8');
4261 my @uris = $marc_xml->field('856');
4262 return unless @uris;
4264 foreach my $field (@uris) {
4265 my $sfu = $field->subfield('u');
4266 my $ind2 = $field->indicator('2');
4267 if (!defined $ind2) { next; }
4268 if ($ind2 ne '0') { next; }
4269 if (!defined $sfu) { next; }
4270 if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
4271 $field->add_subfields( '9' => $new_9_to_set );
4276 return $marc_xml->as_xml_record();
4280 DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
4281 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
4290 EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
4292 SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
4295 new_xml = '$_$' || new_xml || '$_$';
4297 IF new_xml != source_xml THEN
4298 EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
4305 $BODY$ LANGUAGE plpgsql;
4308 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
4309 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
4317 use MARC::File::XML (BinaryEncoding => 'utf8');
4319 binmode(STDERR, ':bytes');
4320 binmode(STDOUT, ':utf8');
4321 binmode(STDERR, ':utf8');
4323 my $marc_xml = shift;
4326 $marc_xml =~ s/(<leader>.........)./${1}a/;
4329 $marc_xml = MARC::Record->new_from_xml($marc_xml);
4332 #elog("could not parse $bibid: $@\n");
4333 import MARC::File::XML (BinaryEncoding => 'utf8');
4337 my @fields = $marc_xml->field($tag);
4338 return $marc_xml->as_xml_record() unless @fields;
4340 $marc_xml->delete_fields(@fields);
4342 return $marc_xml->as_xml_record();
4346 -- convenience function for linking to the item staging table
4348 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4350 table_schema ALIAS FOR $1;
4351 table_name ALIAS FOR $2;
4352 foreign_column_name ALIAS FOR $3;
4353 main_column_name ALIAS FOR $4;
4354 btrim_desired ALIAS FOR $5;
4357 EXECUTE 'SELECT EXISTS (
4359 FROM information_schema.columns
4360 WHERE table_schema = $1
4362 and column_name = $3
4363 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4365 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4368 EXECUTE 'SELECT EXISTS (
4370 FROM information_schema.columns
4371 WHERE table_schema = $1
4372 AND table_name = ''asset_copy_legacy''
4373 and column_name = $2
4374 )' INTO proceed USING table_schema, main_column_name;
4376 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
4379 EXECUTE 'ALTER TABLE '
4380 || quote_ident(table_name)
4381 || ' DROP COLUMN IF EXISTS x_item';
4382 EXECUTE 'ALTER TABLE '
4383 || quote_ident(table_name)
4384 || ' ADD COLUMN x_item BIGINT';
4386 IF btrim_desired THEN
4387 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4388 || ' SET x_item = b.id FROM asset_copy_legacy b'
4389 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4390 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4392 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4393 || ' SET x_item = b.id FROM asset_copy_legacy b'
4394 || ' WHERE a.' || quote_ident(foreign_column_name)
4395 || ' = b.' || quote_ident(main_column_name);
4398 --EXECUTE 'SELECT migration_tools.assert(
4399 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
4400 -- ''Cannot link every barcode'',
4401 -- ''Every barcode linked''
4405 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4407 -- convenience function for linking to the user staging table
4409 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4411 table_schema ALIAS FOR $1;
4412 table_name ALIAS FOR $2;
4413 foreign_column_name ALIAS FOR $3;
4414 main_column_name ALIAS FOR $4;
4415 btrim_desired ALIAS FOR $5;
4418 EXECUTE 'SELECT EXISTS (
4420 FROM information_schema.columns
4421 WHERE table_schema = $1
4423 and column_name = $3
4424 )' INTO proceed USING table_schema, table_name, foreign_column_name;
4426 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
4429 EXECUTE 'SELECT EXISTS (
4431 FROM information_schema.columns
4432 WHERE table_schema = $1
4433 AND table_name = ''actor_usr_legacy''
4434 and column_name = $2
4435 )' INTO proceed USING table_schema, main_column_name;
4437 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
4440 EXECUTE 'ALTER TABLE '
4441 || quote_ident(table_name)
4442 || ' DROP COLUMN IF EXISTS x_user';
4443 EXECUTE 'ALTER TABLE '
4444 || quote_ident(table_name)
4445 || ' ADD COLUMN x_user INTEGER';
4447 IF btrim_desired THEN
4448 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4449 || ' SET x_user = b.id FROM actor_usr_legacy b'
4450 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
4451 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
4453 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
4454 || ' SET x_user = b.id FROM actor_usr_legacy b'
4455 || ' WHERE a.' || quote_ident(foreign_column_name)
4456 || ' = b.' || quote_ident(main_column_name);
4459 --EXECUTE 'SELECT migration_tools.assert(
4460 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
4461 -- ''Cannot link every barcode'',
4462 -- ''Every barcode linked''
4466 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4468 -- convenience function for linking two tables
4469 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
4470 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4472 table_schema ALIAS FOR $1;
4473 table_a ALIAS FOR $2;
4474 column_a ALIAS FOR $3;
4475 table_b ALIAS FOR $4;
4476 column_b ALIAS FOR $5;
4477 column_x ALIAS FOR $6;
4478 btrim_desired ALIAS FOR $7;
4481 EXECUTE 'SELECT EXISTS (
4483 FROM information_schema.columns
4484 WHERE table_schema = $1
4486 and column_name = $3
4487 )' INTO proceed USING table_schema, table_a, column_a;
4489 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4492 EXECUTE 'SELECT EXISTS (
4494 FROM information_schema.columns
4495 WHERE table_schema = $1
4497 and column_name = $3
4498 )' INTO proceed USING table_schema, table_b, column_b;
4500 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4503 EXECUTE 'ALTER TABLE '
4504 || quote_ident(table_b)
4505 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4506 EXECUTE 'ALTER TABLE '
4507 || quote_ident(table_b)
4508 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
4510 IF btrim_desired THEN
4511 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4512 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4513 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4514 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4516 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4517 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
4518 || ' WHERE a.' || quote_ident(column_a)
4519 || ' = b.' || quote_ident(column_b);
4523 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4525 -- convenience function for linking two tables, but copying column w into column x instead of "id"
4526 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
4527 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
4529 table_schema ALIAS FOR $1;
4530 table_a ALIAS FOR $2;
4531 column_a ALIAS FOR $3;
4532 table_b ALIAS FOR $4;
4533 column_b ALIAS FOR $5;
4534 column_w ALIAS FOR $6;
4535 column_x ALIAS FOR $7;
4536 btrim_desired ALIAS FOR $8;
4539 EXECUTE 'SELECT EXISTS (
4541 FROM information_schema.columns
4542 WHERE table_schema = $1
4544 and column_name = $3
4545 )' INTO proceed USING table_schema, table_a, column_a;
4547 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4550 EXECUTE 'SELECT EXISTS (
4552 FROM information_schema.columns
4553 WHERE table_schema = $1
4555 and column_name = $3
4556 )' INTO proceed USING table_schema, table_b, column_b;
4558 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4561 EXECUTE 'ALTER TABLE '
4562 || quote_ident(table_b)
4563 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
4564 EXECUTE 'ALTER TABLE '
4565 || quote_ident(table_b)
4566 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
4568 IF btrim_desired THEN
4569 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4570 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4571 || ' WHERE BTRIM(a.' || quote_ident(column_a)
4572 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
4574 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4575 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4576 || ' WHERE a.' || quote_ident(column_a)
4577 || ' = b.' || quote_ident(column_b);
4581 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4583 -- convenience function for linking two tables, but copying column w into column x instead of "id". Unlike handle_link2, this one won't drop the target column, and it also doesn't have a final boolean argument for btrim
4584 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
4585 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4587 table_schema ALIAS FOR $1;
4588 table_a ALIAS FOR $2;
4589 column_a ALIAS FOR $3;
4590 table_b ALIAS FOR $4;
4591 column_b ALIAS FOR $5;
4592 column_w ALIAS FOR $6;
4593 column_x ALIAS FOR $7;
4596 EXECUTE 'SELECT EXISTS (
4598 FROM information_schema.columns
4599 WHERE table_schema = $1
4601 and column_name = $3
4602 )' INTO proceed USING table_schema, table_a, column_a;
4604 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4607 EXECUTE 'SELECT EXISTS (
4609 FROM information_schema.columns
4610 WHERE table_schema = $1
4612 and column_name = $3
4613 )' INTO proceed USING table_schema, table_b, column_b;
4615 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4618 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4619 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4620 || ' WHERE a.' || quote_ident(column_a)
4621 || ' = b.' || quote_ident(column_b);
4624 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4626 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4628 table_schema ALIAS FOR $1;
4629 table_a ALIAS FOR $2;
4630 column_a ALIAS FOR $3;
4631 table_b ALIAS FOR $4;
4632 column_b ALIAS FOR $5;
4633 column_w ALIAS FOR $6;
4634 column_x ALIAS FOR $7;
4637 EXECUTE 'SELECT EXISTS (
4639 FROM information_schema.columns
4640 WHERE table_schema = $1
4642 and column_name = $3
4643 )' INTO proceed USING table_schema, table_a, column_a;
4645 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4648 EXECUTE 'SELECT EXISTS (
4650 FROM information_schema.columns
4651 WHERE table_schema = $1
4653 and column_name = $3
4654 )' INTO proceed USING table_schema, table_b, column_b;
4656 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4659 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4660 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4661 || ' WHERE a.' || quote_ident(column_a)
4662 || ' = b.' || quote_ident(column_b)
4663 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4666 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4668 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4670 table_schema ALIAS FOR $1;
4671 table_a ALIAS FOR $2;
4672 column_a ALIAS FOR $3;
4673 table_b ALIAS FOR $4;
4674 column_b ALIAS FOR $5;
4675 column_w ALIAS FOR $6;
4676 column_x ALIAS FOR $7;
4679 EXECUTE 'SELECT EXISTS (
4681 FROM information_schema.columns
4682 WHERE table_schema = $1
4684 and column_name = $3
4685 )' INTO proceed USING table_schema, table_a, column_a;
4687 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4690 EXECUTE 'SELECT EXISTS (
4692 FROM information_schema.columns
4693 WHERE table_schema = $1
4695 and column_name = $3
4696 )' INTO proceed USING table_schema, table_b, column_b;
4698 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4701 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4702 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4703 || ' WHERE a.' || quote_ident(column_a)
4704 || ' = b.' || quote_ident(column_b)
4705 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
4708 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4710 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4712 table_schema ALIAS FOR $1;
4713 table_a ALIAS FOR $2;
4714 column_a ALIAS FOR $3;
4715 table_b ALIAS FOR $4;
4716 column_b ALIAS FOR $5;
4717 column_w ALIAS FOR $6;
4718 column_x ALIAS FOR $7;
4721 EXECUTE 'SELECT EXISTS (
4723 FROM information_schema.columns
4724 WHERE table_schema = $1
4726 and column_name = $3
4727 )' INTO proceed USING table_schema, table_a, column_a;
4729 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4732 EXECUTE 'SELECT EXISTS (
4734 FROM information_schema.columns
4735 WHERE table_schema = $1
4737 and column_name = $3
4738 )' INTO proceed USING table_schema, table_b, column_b;
4740 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4743 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4744 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4745 || ' WHERE a.' || quote_ident(column_a)
4746 || ' = b.' || quote_ident(column_b)
4747 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
4750 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4752 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4754 table_schema ALIAS FOR $1;
4755 table_a ALIAS FOR $2;
4756 column_a ALIAS FOR $3;
4757 table_b ALIAS FOR $4;
4758 column_b ALIAS FOR $5;
4759 column_w ALIAS FOR $6;
4760 column_x ALIAS FOR $7;
4763 EXECUTE 'SELECT EXISTS (
4765 FROM information_schema.columns
4766 WHERE table_schema = $1
4768 and column_name = $3
4769 )' INTO proceed USING table_schema, table_a, column_a;
4771 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4774 EXECUTE 'SELECT EXISTS (
4776 FROM information_schema.columns
4777 WHERE table_schema = $1
4779 and column_name = $3
4780 )' INTO proceed USING table_schema, table_b, column_b;
4782 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4785 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4786 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
4787 || ' WHERE a.' || quote_ident(column_a)
4788 || ' = b.' || quote_ident(column_b)
4789 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
4792 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4794 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4796 table_schema ALIAS FOR $1;
4797 table_a ALIAS FOR $2;
4798 column_a ALIAS FOR $3;
4799 table_b ALIAS FOR $4;
4800 column_b ALIAS FOR $5;
4801 column_w ALIAS FOR $6;
4802 column_x ALIAS FOR $7;
4805 EXECUTE 'SELECT EXISTS (
4807 FROM information_schema.columns
4808 WHERE table_schema = $1
4810 and column_name = $3
4811 )' INTO proceed USING table_schema, table_a, column_a;
4813 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
4816 EXECUTE 'SELECT EXISTS (
4818 FROM information_schema.columns
4819 WHERE table_schema = $1
4821 and column_name = $3
4822 )' INTO proceed USING table_schema, table_b, column_b;
4824 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
4827 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
4828 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
4829 || ' WHERE a.' || quote_ident(column_a)
4830 || ' = b.' || quote_ident(column_b)
4831 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
4834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4836 -- convenience function for handling desired asset stat cats
4838 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4840 table_schema ALIAS FOR $1;
4841 table_name ALIAS FOR $2;
4842 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4843 org_shortname ALIAS FOR $4;
4851 SELECT 'desired_sc' || field_suffix INTO sc;
4852 SELECT 'desired_sce' || field_suffix INTO sce;
4854 EXECUTE 'SELECT EXISTS (
4856 FROM information_schema.columns
4857 WHERE table_schema = $1
4859 and column_name = $3
4860 )' INTO proceed USING table_schema, table_name, sc;
4862 RAISE EXCEPTION 'Missing column %', sc;
4864 EXECUTE 'SELECT EXISTS (
4866 FROM information_schema.columns
4867 WHERE table_schema = $1
4869 and column_name = $3
4870 )' INTO proceed USING table_schema, table_name, sce;
4872 RAISE EXCEPTION 'Missing column %', sce;
4875 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4877 RAISE EXCEPTION 'Cannot find org by shortname';
4879 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4881 -- caller responsible for their own truncates though we try to prevent duplicates
4882 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
4887 ' || quote_ident(table_name) || '
4889 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4893 WHERE owner = ANY ($2)
4894 AND name = BTRIM('||sc||')
4899 WHERE owner = ANY ($2)
4900 AND name = BTRIM('||sc||')
4903 USING org, org_list;
4905 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
4910 WHERE owner = ANY ($2)
4911 AND BTRIM('||sc||') = BTRIM(name))
4914 WHERE owner = ANY ($2)
4915 AND BTRIM('||sc||') = BTRIM(name))
4920 ' || quote_ident(table_name) || '
4922 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
4923 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
4926 FROM asset.stat_cat_entry
4930 WHERE owner = ANY ($2)
4931 AND BTRIM('||sc||') = BTRIM(name)
4932 ) AND value = BTRIM('||sce||')
4933 AND owner = ANY ($2)
4937 FROM asset_stat_cat_entry
4941 WHERE owner = ANY ($2)
4942 AND BTRIM('||sc||') = BTRIM(name)
4943 ) AND value = BTRIM('||sce||')
4944 AND owner = ANY ($2)
4947 USING org, org_list;
4949 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
4951 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
4953 table_schema ALIAS FOR $1;
4954 table_name ALIAS FOR $2;
4955 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
4956 org_shortname ALIAS FOR $4;
4964 SELECT 'desired_sc' || field_suffix INTO sc;
4965 SELECT 'desired_sce' || field_suffix INTO sce;
4966 EXECUTE 'SELECT EXISTS (
4968 FROM information_schema.columns
4969 WHERE table_schema = $1
4971 and column_name = $3
4972 )' INTO proceed USING table_schema, table_name, sc;
4974 RAISE EXCEPTION 'Missing column %', sc;
4976 EXECUTE 'SELECT EXISTS (
4978 FROM information_schema.columns
4979 WHERE table_schema = $1
4981 and column_name = $3
4982 )' INTO proceed USING table_schema, table_name, sce;
4984 RAISE EXCEPTION 'Missing column %', sce;
4987 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
4989 RAISE EXCEPTION 'Cannot find org by shortname';
4992 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
4994 EXECUTE 'ALTER TABLE '
4995 || quote_ident(table_name)
4996 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
4997 EXECUTE 'ALTER TABLE '
4998 || quote_ident(table_name)
4999 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
5000 EXECUTE 'ALTER TABLE '
5001 || quote_ident(table_name)
5002 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
5003 EXECUTE 'ALTER TABLE '
5004 || quote_ident(table_name)
5005 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
5008 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5010 x_sc' || field_suffix || ' = id
5012 (SELECT id, name, owner FROM asset_stat_cat
5013 UNION SELECT id, name, owner FROM asset.stat_cat) u
5015 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
5016 AND u.owner = ANY ($1);'
5019 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
5021 x_sce' || field_suffix || ' = id
5023 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
5024 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
5026 u.stat_cat = x_sc' || field_suffix || '
5027 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
5028 AND u.owner = ANY ($1);'
5031 EXECUTE 'SELECT migration_tools.assert(
5032 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
5033 ''Cannot find a desired stat cat'',
5034 ''Found all desired stat cats''
5037 EXECUTE 'SELECT migration_tools.assert(
5038 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
5039 ''Cannot find a desired stat cat entry'',
5040 ''Found all desired stat cat entries''
5044 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
5046 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
5047 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5054 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5056 AND table_schema = s_name
5057 AND (data_type='text' OR data_type='character varying')
5058 AND column_name like 'l_%'
5060 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5067 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
5068 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5075 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5077 AND table_schema = s_name
5078 AND (data_type='text' OR data_type='character varying')
5080 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
5087 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
5088 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5095 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5097 AND table_schema = s_name
5098 AND (data_type='text' OR data_type='character varying')
5099 AND column_name like 'l_%'
5101 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
5108 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
5109 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
5116 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
5118 AND table_schema = s_name
5119 AND (data_type='text' OR data_type='character varying')
5121 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');