1 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
10 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
11 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
12 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
13 IF n_digits = 7 AND areacode <> '' THEN
14 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
15 output := (areacode || '-' || temp);
22 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
24 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
28 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
30 $$ LANGUAGE PLPGSQL STRICT STABLE;
32 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
58 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
60 migration_schema ALIAS FOR $1;
64 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
69 $$ LANGUAGE PLPGSQL STRICT STABLE;
71 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
73 migration_schema ALIAS FOR $1;
75 patron_table ALIAS FOR $2;
76 default_patron_profile ALIAS FOR $3;
79 sql_where1 TEXT := '';
80 sql_where2 TEXT := '';
81 sql_where3 TEXT := '';
84 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
86 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
88 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
89 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);
90 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);
91 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);
92 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,'') || ';';
93 --RAISE INFO 'sql = %', sql;
94 PERFORM migration_tools.exec( $1, sql );
96 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
98 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
100 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
103 $$ LANGUAGE PLPGSQL STRICT STABLE;
105 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
107 migration_schema ALIAS FOR $1;
109 item_table ALIAS FOR $2;
112 sql_where1 TEXT := '';
113 sql_where2 TEXT := '';
114 sql_where3 TEXT := '';
117 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
119 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
121 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 ';
122 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);
123 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);
124 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);
125 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,'') || ';';
126 --RAISE INFO 'sql = %', sql;
127 PERFORM migration_tools.exec( $1, sql );
130 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
132 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
135 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
137 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
139 migration_schema ALIAS FOR $1;
140 base_copy_location_map TEXT;
141 item_table ALIAS FOR $2;
144 sql_where1 TEXT := '';
145 sql_where2 TEXT := '';
146 sql_where3 TEXT := '';
149 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
151 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
153 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
154 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);
155 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);
156 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);
157 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,'') || ';';
158 --RAISE INFO 'sql = %', sql;
159 PERFORM migration_tools.exec( $1, sql );
162 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
164 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
167 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
169 -- 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
170 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
172 migration_schema ALIAS FOR $1;
174 circ_table ALIAS FOR $2;
175 item_table ALIAS FOR $3;
176 patron_table ALIAS FOR $4;
179 sql_where1 TEXT := '';
180 sql_where2 TEXT := '';
181 sql_where3 TEXT := '';
182 sql_where4 TEXT := '';
185 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
187 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
189 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 ';
190 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);
191 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);
192 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);
193 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);
194 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,'') || ';';
195 --RAISE INFO 'sql = %', sql;
196 PERFORM migration_tools.exec( $1, sql );
199 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
201 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
204 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
206 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
210 -- First make sure the circ matrix is loaded and the circulations
211 -- have been staged to the extent possible (but at the very least
212 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
213 -- circ modifiers must also be in place.
215 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
223 this_duration_rule INT;
225 this_max_fine_rule INT;
226 rcd config.rule_circ_duration%ROWTYPE;
227 rrf config.rule_recurring_fine%ROWTYPE;
228 rmf config.rule_max_fine%ROWTYPE;
235 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
237 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
239 -- Fetch the correct rules for this circulation
246 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
249 FROM ' || tablename || ' WHERE id = ' || circ || ';')
250 INTO circ_lib, target_copy, usr, is_renewal ;
252 INTO this_duration_rule,
258 FROM action.find_circ_matrix_matchpoint(
264 SELECT INTO rcd * FROM config.rule_circ_duration
265 WHERE id = this_duration_rule;
266 SELECT INTO rrf * FROM config.rule_recurring_fine
267 WHERE id = this_fine_rule;
268 SELECT INTO rmf * FROM config.rule_max_fine
269 WHERE id = this_max_fine_rule;
271 -- Apply the rules to this circulation
272 EXECUTE ('UPDATE ' || tablename || ' c
274 duration_rule = rcd.name,
275 recuring_fine_rule = rrf.name,
276 max_fine_rule = rmf.name,
277 duration = rcd.normal,
278 recuring_fine = rrf.normal,
281 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
284 renewal_remaining = rcd.max_renewals
286 config.rule_circ_duration rcd,
287 config.rule_recuring_fine rrf,
288 config.rule_max_fine rmf,
291 rcd.id = ' || this_duration_rule || ' AND
292 rrf.id = ' || this_fine_rule || ' AND
293 rmf.id = ' || this_max_fine_rule || ' AND
294 ac.id = c.target_copy AND
295 c.id = ' || circ || ';');
297 -- Keep track of where we are in the process
299 IF (n % 100 = 0) THEN
300 RAISE INFO '%', n || ' of ' || n_circs
301 || ' (' || (100*n/n_circs) || '%) circs updated.';
311 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
315 -- First make sure the circ matrix is loaded and the circulations
316 -- have been staged to the extent possible (but at the very least
317 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
318 -- circ modifiers must also be in place.
320 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
328 this_duration_rule INT;
330 this_max_fine_rule INT;
331 rcd config.rule_circ_duration%ROWTYPE;
332 rrf config.rule_recurring_fine%ROWTYPE;
333 rmf config.rule_max_fine%ROWTYPE;
340 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
342 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
344 -- Fetch the correct rules for this circulation
351 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
354 FROM ' || tablename || ' WHERE id = ' || circ || ';')
355 INTO circ_lib, target_copy, usr, is_renewal ;
357 INTO this_duration_rule,
360 (matchpoint).duration_rule,
361 (matchpoint).recurring_fine_rule,
362 (matchpoint).max_fine_rule
363 FROM action.find_circ_matrix_matchpoint(
369 SELECT INTO rcd * FROM config.rule_circ_duration
370 WHERE id = this_duration_rule;
371 SELECT INTO rrf * FROM config.rule_recurring_fine
372 WHERE id = this_fine_rule;
373 SELECT INTO rmf * FROM config.rule_max_fine
374 WHERE id = this_max_fine_rule;
376 -- Apply the rules to this circulation
377 EXECUTE ('UPDATE ' || tablename || ' c
379 duration_rule = rcd.name,
380 recurring_fine_rule = rrf.name,
381 max_fine_rule = rmf.name,
382 duration = rcd.normal,
383 recurring_fine = rrf.normal,
386 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
389 renewal_remaining = rcd.max_renewals,
390 grace_period = rrf.grace_period
392 config.rule_circ_duration rcd,
393 config.rule_recurring_fine rrf,
394 config.rule_max_fine rmf,
397 rcd.id = ' || this_duration_rule || ' AND
398 rrf.id = ' || this_fine_rule || ' AND
399 rmf.id = ' || this_max_fine_rule || ' AND
400 ac.id = c.target_copy AND
401 c.id = ' || circ || ';');
403 -- Keep track of where we are in the process
405 IF (n % 100 = 0) THEN
406 RAISE INFO '%', n || ' of ' || n_circs
407 || ' (' || (100*n/n_circs) || '%) circs updated.';
417 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
419 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
421 my ($marcxml, $shortname) = @_;
429 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
431 foreach my $field ( $marc->field('856') ) {
432 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
433 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
434 $field->add_subfields( '9' => $shortname );
435 $field->update( ind2 => '0');
439 $xml = $marc->as_xml_record;
440 $xml =~ s/^<\?.+?\?>$//mo;
442 $xml =~ s/>\s+</></sgo;
447 $$ LANGUAGE PLPERLU STABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
451 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
453 my ($marcxml, $shortname) = @_;
461 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
463 foreach my $field ( $marc->field('856') ) {
464 if ( ! $field->as_string('9') ) {
465 $field->add_subfields( '9' => $shortname );
469 $xml = $marc->as_xml_record;
470 $xml =~ s/^<\?.+?\?>$//mo;
472 $xml =~ s/>\s+</></sgo;
477 $$ LANGUAGE PLPERLU STABLE;
479 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
483 DELETE FROM asset.opac_visible_copies;
485 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
487 cp.id, cp.circ_lib, cn.record
490 JOIN asset.call_number cn ON (cn.id = cp.call_number)
491 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
492 JOIN asset.copy_location cl ON (cp.location = cl.id)
493 JOIN config.copy_status cs ON (cp.status = cs.id)
494 JOIN biblio.record_entry b ON (cn.record = b.id)
503 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
509 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
511 migration_schema ALIAS FOR $1;
515 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
517 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
519 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
521 migration_schema ALIAS FOR $1;
525 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
526 --RAISE INFO '%', sql;
528 GET DIAGNOSTICS nrows = ROW_COUNT;
529 PERFORM migration_tools.log(migration_schema,sql,nrows);
532 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
534 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
536 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
538 migration_schema ALIAS FOR $1;
542 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
543 RAISE INFO 'debug_exec sql = %', sql;
545 GET DIAGNOSTICS nrows = ROW_COUNT;
546 PERFORM migration_tools.log(migration_schema,sql,nrows);
549 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
551 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
553 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
555 migration_schema ALIAS FOR $1;
556 production_tables TEXT[];
558 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
559 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
560 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
561 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
564 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
566 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
568 migration_schema ALIAS FOR $1;
569 production_table ALIAS FOR $2;
570 base_staging_table TEXT;
573 base_staging_table = REPLACE( production_table, '.', '_' );
574 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
575 EXECUTE 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';';
577 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
579 -- push bib sequence and return starting value for reserved range
580 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
582 bib_count ALIAS FOR $1;
585 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
587 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
592 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
594 -- convenience function for linking two tables
595 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
596 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
598 table_schema ALIAS FOR $1;
599 table_a ALIAS FOR $2;
600 column_a ALIAS FOR $3;
601 table_b ALIAS FOR $4;
602 column_b ALIAS FOR $5;
603 column_x ALIAS FOR $6;
604 btrim_desired ALIAS FOR $7;
607 EXECUTE 'SELECT EXISTS (
609 FROM information_schema.columns
610 WHERE table_schema = $1
613 )' INTO proceed USING table_schema, table_a, column_a;
615 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
618 EXECUTE 'SELECT EXISTS (
620 FROM information_schema.columns
621 WHERE table_schema = $1
624 )' INTO proceed USING table_schema, table_b, column_b;
626 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
629 EXECUTE 'ALTER TABLE '
630 || quote_ident(table_b)
631 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
632 EXECUTE 'ALTER TABLE '
633 || quote_ident(table_b)
634 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
636 IF btrim_desired THEN
637 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
638 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
639 || ' WHERE BTRIM(a.' || quote_ident(column_a)
640 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
642 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
643 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
644 || ' WHERE a.' || quote_ident(column_a)
645 || ' = b.' || quote_ident(column_b);
649 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
651 -- convenience function for linking two tables, but copying column w into column x instead of "id"
652 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
653 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
655 table_schema ALIAS FOR $1;
656 table_a ALIAS FOR $2;
657 column_a ALIAS FOR $3;
658 table_b ALIAS FOR $4;
659 column_b ALIAS FOR $5;
660 column_w ALIAS FOR $6;
661 column_x ALIAS FOR $7;
662 btrim_desired ALIAS FOR $8;
665 EXECUTE 'SELECT EXISTS (
667 FROM information_schema.columns
668 WHERE table_schema = $1
671 )' INTO proceed USING table_schema, table_a, column_a;
673 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
676 EXECUTE 'SELECT EXISTS (
678 FROM information_schema.columns
679 WHERE table_schema = $1
682 )' INTO proceed USING table_schema, table_b, column_b;
684 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
687 EXECUTE 'ALTER TABLE '
688 || quote_ident(table_b)
689 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
690 EXECUTE 'ALTER TABLE '
691 || quote_ident(table_b)
692 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
694 IF btrim_desired THEN
695 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
696 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
697 || ' WHERE BTRIM(a.' || quote_ident(column_a)
698 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
700 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
701 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
702 || ' WHERE a.' || quote_ident(column_a)
703 || ' = b.' || quote_ident(column_b);
707 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
709 -- 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
710 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
711 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
713 table_schema ALIAS FOR $1;
714 table_a ALIAS FOR $2;
715 column_a ALIAS FOR $3;
716 table_b ALIAS FOR $4;
717 column_b ALIAS FOR $5;
718 column_w ALIAS FOR $6;
719 column_x ALIAS FOR $7;
722 EXECUTE 'SELECT EXISTS (
724 FROM information_schema.columns
725 WHERE table_schema = $1
728 )' INTO proceed USING table_schema, table_a, column_a;
730 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
733 EXECUTE 'SELECT EXISTS (
735 FROM information_schema.columns
736 WHERE table_schema = $1
739 )' INTO proceed USING table_schema, table_b, column_b;
741 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
744 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
745 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
746 || ' WHERE a.' || quote_ident(column_a)
747 || ' = b.' || quote_ident(column_b);
750 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
752 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
754 table_schema ALIAS FOR $1;
755 table_a ALIAS FOR $2;
756 column_a ALIAS FOR $3;
757 table_b ALIAS FOR $4;
758 column_b ALIAS FOR $5;
759 column_w ALIAS FOR $6;
760 column_x ALIAS FOR $7;
763 EXECUTE 'SELECT EXISTS (
765 FROM information_schema.columns
766 WHERE table_schema = $1
769 )' INTO proceed USING table_schema, table_a, column_a;
771 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
774 EXECUTE 'SELECT EXISTS (
776 FROM information_schema.columns
777 WHERE table_schema = $1
780 )' INTO proceed USING table_schema, table_b, column_b;
782 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
785 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
786 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
787 || ' WHERE a.' || quote_ident(column_a)
788 || ' = b.' || quote_ident(column_b)
789 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
792 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
794 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
796 table_schema ALIAS FOR $1;
797 table_a ALIAS FOR $2;
798 column_a ALIAS FOR $3;
799 table_b ALIAS FOR $4;
800 column_b ALIAS FOR $5;
801 column_w ALIAS FOR $6;
802 column_x ALIAS FOR $7;
805 EXECUTE 'SELECT EXISTS (
807 FROM information_schema.columns
808 WHERE table_schema = $1
811 )' INTO proceed USING table_schema, table_a, column_a;
813 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
816 EXECUTE 'SELECT EXISTS (
818 FROM information_schema.columns
819 WHERE table_schema = $1
822 )' INTO proceed USING table_schema, table_b, column_b;
824 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
827 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
828 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
829 || ' WHERE a.' || quote_ident(column_a)
830 || ' = b.' || quote_ident(column_b)
831 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
834 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
836 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
838 table_schema ALIAS FOR $1;
839 table_a ALIAS FOR $2;
840 column_a ALIAS FOR $3;
841 table_b ALIAS FOR $4;
842 column_b ALIAS FOR $5;
843 column_w ALIAS FOR $6;
844 column_x ALIAS FOR $7;
847 EXECUTE 'SELECT EXISTS (
849 FROM information_schema.columns
850 WHERE table_schema = $1
853 )' INTO proceed USING table_schema, table_a, column_a;
855 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
858 EXECUTE 'SELECT EXISTS (
860 FROM information_schema.columns
861 WHERE table_schema = $1
864 )' INTO proceed USING table_schema, table_b, column_b;
866 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
869 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
870 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
871 || ' WHERE a.' || quote_ident(column_a)
872 || ' = b.' || quote_ident(column_b)
873 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
876 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
878 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
880 table_schema ALIAS FOR $1;
881 table_a ALIAS FOR $2;
882 column_a ALIAS FOR $3;
883 table_b ALIAS FOR $4;
884 column_b ALIAS FOR $5;
885 column_w ALIAS FOR $6;
886 column_x ALIAS FOR $7;
889 EXECUTE 'SELECT EXISTS (
891 FROM information_schema.columns
892 WHERE table_schema = $1
895 )' INTO proceed USING table_schema, table_a, column_a;
897 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
900 EXECUTE 'SELECT EXISTS (
902 FROM information_schema.columns
903 WHERE table_schema = $1
906 )' INTO proceed USING table_schema, table_b, column_b;
908 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
911 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
912 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
913 || ' WHERE a.' || quote_ident(column_a)
914 || ' = b.' || quote_ident(column_b)
915 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
918 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
920 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
922 table_schema ALIAS FOR $1;
923 table_a ALIAS FOR $2;
924 column_a ALIAS FOR $3;
925 table_b ALIAS FOR $4;
926 column_b ALIAS FOR $5;
927 column_w ALIAS FOR $6;
928 column_x ALIAS FOR $7;
931 EXECUTE 'SELECT EXISTS (
933 FROM information_schema.columns
934 WHERE table_schema = $1
937 )' INTO proceed USING table_schema, table_a, column_a;
939 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
942 EXECUTE 'SELECT EXISTS (
944 FROM information_schema.columns
945 WHERE table_schema = $1
948 )' INTO proceed USING table_schema, table_b, column_b;
950 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
953 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
954 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
955 || ' WHERE a.' || quote_ident(column_a)
956 || ' = b.' || quote_ident(column_b)
957 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;