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.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
25 my ($marcxml, $pos, $value) = @_;
32 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
33 my $leader = $marc->leader();
34 substr($leader, $pos, 1) = $value;
35 $marc->leader($leader);
36 $xml = $marc->as_xml_record;
37 $xml =~ s/^<\?.+?\?>$//mo;
39 $xml =~ s/>\s+</></sgo;
42 $$ LANGUAGE PLPERLU STABLE;
44 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
45 my ($marcxml, $pos, $value) = @_;
52 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
53 my $f008 = $marc->field('008');
56 my $field = $f008->data();
57 substr($field, $pos, 1) = $value;
58 $f008->update($field);
59 $xml = $marc->as_xml_record;
60 $xml =~ s/^<\?.+?\?>$//mo;
62 $xml =~ s/>\s+</></sgo;
66 $$ LANGUAGE PLPERLU STABLE;
69 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
73 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
75 $$ LANGUAGE PLPGSQL STRICT STABLE;
78 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
80 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
82 $$ LANGUAGE PLPGSQL STRICT STABLE;
85 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
87 my ($marcxml, $tags) = @_;
95 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
96 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
100 foreach my $field ( $marc->fields() ) {
101 push @incumbents, $field->as_formatted();
104 foreach $field ( $to_insert->fields() ) {
105 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
106 $marc->insert_fields_ordered( ($field) );
110 $xml = $marc->as_xml_record;
111 $xml =~ s/^<\?.+?\?>$//mo;
113 $xml =~ s/>\s+</></sgo;
118 $$ LANGUAGE PLPERLU STABLE;
120 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
124 -- First make sure the circ matrix is loaded and the circulations
125 -- have been staged to the extent possible (but at the very least
126 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
127 -- circ modifiers must also be in place.
129 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
137 this_duration_rule INT;
139 this_max_fine_rule INT;
140 rcd config.rule_circ_duration%ROWTYPE;
141 rrf config.rule_recurring_fine%ROWTYPE;
142 rmf config.rule_max_fine%ROWTYPE;
149 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
151 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
153 -- Fetch the correct rules for this circulation
160 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
163 FROM ' || tablename || ' WHERE id = ' || circ || ';')
164 INTO circ_lib, target_copy, usr, is_renewal ;
166 INTO this_duration_rule,
172 FROM action.item_user_circ_test(
178 SELECT INTO rcd * FROM config.rule_circ_duration
179 WHERE id = this_duration_rule;
180 SELECT INTO rrf * FROM config.rule_recurring_fine
181 WHERE id = this_fine_rule;
182 SELECT INTO rmf * FROM config.rule_max_fine
183 WHERE id = this_max_fine_rule;
185 -- Apply the rules to this circulation
186 EXECUTE ('UPDATE ' || tablename || ' c
188 duration_rule = rcd.name,
189 recurring_fine_rule = rrf.name,
190 max_fine_rule = rmf.name,
191 duration = rcd.normal,
192 recurring_fine = rrf.normal,
195 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
198 renewal_remaining = rcd.max_renewals
200 config.rule_circ_duration rcd,
201 config.rule_recurring_fine rrf,
202 config.rule_max_fine rmf,
205 rcd.id = ' || this_duration_rule || ' AND
206 rrf.id = ' || this_fine_rule || ' AND
207 rmf.id = ' || this_max_fine_rule || ' AND
208 ac.id = c.target_copy AND
209 c.id = ' || circ || ';');
211 -- Keep track of where we are in the process
213 IF (n % 100 = 0) THEN
214 RAISE INFO '%', n || ' of ' || n_circs
215 || ' (' || (100*n/n_circs) || '%) circs updated.';
225 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
229 -- First make sure the circ matrix is loaded and the circulations
230 -- have been staged to the extent possible (but at the very least
231 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
232 -- circ modifiers must also be in place.
234 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
242 this_duration_rule INT;
244 this_max_fine_rule INT;
245 rcd config.rule_circ_duration%ROWTYPE;
246 rrf config.rule_recurring_fine%ROWTYPE;
247 rmf config.rule_max_fine%ROWTYPE;
254 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
256 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
258 -- Fetch the correct rules for this circulation
265 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
268 FROM ' || tablename || ' WHERE id = ' || circ || ';')
269 INTO circ_lib, target_copy, usr, is_renewal ;
271 INTO this_duration_rule,
277 FROM action.find_circ_matrix_matchpoint(
283 SELECT INTO rcd * FROM config.rule_circ_duration
284 WHERE id = this_duration_rule;
285 SELECT INTO rrf * FROM config.rule_recurring_fine
286 WHERE id = this_fine_rule;
287 SELECT INTO rmf * FROM config.rule_max_fine
288 WHERE id = this_max_fine_rule;
290 -- Apply the rules to this circulation
291 EXECUTE ('UPDATE ' || tablename || ' c
293 duration_rule = rcd.name,
294 recuring_fine_rule = rrf.name,
295 max_fine_rule = rmf.name,
296 duration = rcd.normal,
297 recuring_fine = rrf.normal,
300 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
303 renewal_remaining = rcd.max_renewals
305 config.rule_circ_duration rcd,
306 config.rule_recuring_fine rrf,
307 config.rule_max_fine rmf,
310 rcd.id = ' || this_duration_rule || ' AND
311 rrf.id = ' || this_fine_rule || ' AND
312 rmf.id = ' || this_max_fine_rule || ' AND
313 ac.id = c.target_copy AND
314 c.id = ' || circ || ';');
316 -- Keep track of where we are in the process
318 IF (n % 100 = 0) THEN
319 RAISE INFO '%', n || ' of ' || n_circs
320 || ' (' || (100*n/n_circs) || '%) circs updated.';
330 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
334 -- First make sure the circ matrix is loaded and the circulations
335 -- have been staged to the extent possible (but at the very least
336 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
337 -- circ modifiers must also be in place.
339 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
347 this_duration_rule INT;
349 this_max_fine_rule INT;
350 rcd config.rule_circ_duration%ROWTYPE;
351 rrf config.rule_recurring_fine%ROWTYPE;
352 rmf config.rule_max_fine%ROWTYPE;
359 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
361 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
363 -- Fetch the correct rules for this circulation
370 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
373 FROM ' || tablename || ' WHERE id = ' || circ || ';')
374 INTO circ_lib, target_copy, usr, is_renewal ;
376 INTO this_duration_rule,
379 (matchpoint).duration_rule,
380 (matchpoint).recurring_fine_rule,
381 (matchpoint).max_fine_rule
382 FROM action.find_circ_matrix_matchpoint(
388 SELECT INTO rcd * FROM config.rule_circ_duration
389 WHERE id = this_duration_rule;
390 SELECT INTO rrf * FROM config.rule_recurring_fine
391 WHERE id = this_fine_rule;
392 SELECT INTO rmf * FROM config.rule_max_fine
393 WHERE id = this_max_fine_rule;
395 -- Apply the rules to this circulation
396 EXECUTE ('UPDATE ' || tablename || ' c
398 duration_rule = rcd.name,
399 recurring_fine_rule = rrf.name,
400 max_fine_rule = rmf.name,
401 duration = rcd.normal,
402 recurring_fine = rrf.normal,
405 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
408 renewal_remaining = rcd.max_renewals,
409 grace_period = rrf.grace_period
411 config.rule_circ_duration rcd,
412 config.rule_recurring_fine rrf,
413 config.rule_max_fine rmf,
416 rcd.id = ' || this_duration_rule || ' AND
417 rrf.id = ' || this_fine_rule || ' AND
418 rmf.id = ' || this_max_fine_rule || ' AND
419 ac.id = c.target_copy AND
420 c.id = ' || circ || ';');
422 -- Keep track of where we are in the process
424 IF (n % 100 = 0) THEN
425 RAISE INFO '%', n || ' of ' || n_circs
426 || ' (' || (100*n/n_circs) || '%) circs updated.';
436 -- TODO: make another version of the procedure below that can work with specified copy staging tables
437 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
438 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
441 charge_lost_on_zero BOOLEAN;
444 default_price NUMERIC;
445 working_price NUMERIC;
449 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
450 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
452 SELECT INTO charge_lost_on_zero value
453 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
455 SELECT INTO min_price value
456 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
458 SELECT INTO max_price value
459 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
461 SELECT INTO default_price value
462 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
464 SELECT INTO working_price price FROM asset.copy WHERE id = item;
466 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
467 working_price := default_price;
470 IF (max_price IS NOT NULL AND working_price > max_price) THEN
471 working_price := max_price;
474 IF (min_price IS NOT NULL AND working_price < min_price) THEN
475 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
476 working_price := min_price;
480 RETURN working_price;
486 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
490 -- First make sure the circ matrix is loaded and the circulations
491 -- have been staged to the extent possible (but at the very least
492 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
493 -- circ modifiers must also be in place.
495 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
503 this_duration_rule INT;
505 this_max_fine_rule INT;
506 rcd config.rule_circ_duration%ROWTYPE;
507 rrf config.rule_recurring_fine%ROWTYPE;
508 rmf config.rule_max_fine%ROWTYPE;
514 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
516 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
518 -- Fetch the correct rules for this circulation
525 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
528 FROM ' || tablename || ' WHERE id = ' || circ || ';')
529 INTO circ_lib, target_copy, usr, is_renewal ;
531 INTO this_duration_rule,
534 (matchpoint).duration_rule,
535 (matchpoint).recurring_fine_rule,
536 (matchpoint).max_fine_rule
537 FROM action.find_circ_matrix_matchpoint(
543 SELECT INTO rcd * FROM config.rule_circ_duration
544 WHERE id = this_duration_rule;
545 SELECT INTO rrf * FROM config.rule_recurring_fine
546 WHERE id = this_fine_rule;
547 SELECT INTO rmf * FROM config.rule_max_fine
548 WHERE id = this_max_fine_rule;
550 -- Apply the rules to this circulation
551 EXECUTE ('UPDATE ' || tablename || ' c
553 duration_rule = rcd.name,
554 recurring_fine_rule = rrf.name,
555 max_fine_rule = rmf.name,
556 duration = rcd.normal,
557 recurring_fine = rrf.normal,
560 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
563 renewal_remaining = rcd.max_renewals,
564 grace_period = rrf.grace_period
566 config.rule_circ_duration rcd,
567 config.rule_recurring_fine rrf,
568 config.rule_max_fine rmf,
571 rcd.id = ' || this_duration_rule || ' AND
572 rrf.id = ' || this_fine_rule || ' AND
573 rmf.id = ' || this_max_fine_rule || ' AND
574 ac.id = c.target_copy AND
575 c.id = ' || circ || ';');
577 -- Keep track of where we are in the process
579 IF (n % 100 = 0) THEN
580 RAISE INFO '%', n || ' of ' || n_circs
581 || ' (' || (100*n/n_circs) || '%) circs updated.';
594 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
596 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
597 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
599 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
600 -- TODO: Add a similar tool for actor stat cats, which behave differently.
603 c TEXT := schemaname || '.asset_copy_legacy';
604 sc TEXT := schemaname || '.asset_stat_cat';
605 sce TEXT := schemaname || '.asset_stat_cat_entry';
606 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
612 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
614 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
616 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
617 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
618 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
627 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
629 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
630 -- This will assign standing penalties as needed.
638 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
640 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
642 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
655 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
658 INSERT INTO metabib.metarecord (fingerprint, master_record)
659 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
660 FROM biblio.record_entry b
662 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)
663 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
664 ORDER BY b.fingerprint, b.quality DESC;
665 INSERT INTO metabib.metarecord_source_map (metarecord, source)
667 FROM biblio.record_entry r
668 JOIN metabib.metarecord m USING (fingerprint)
675 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
678 INSERT INTO metabib.metarecord (fingerprint, master_record)
679 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
680 FROM biblio.record_entry b
682 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)
683 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
684 ORDER BY b.fingerprint, b.quality DESC;
685 INSERT INTO metabib.metarecord_source_map (metarecord, source)
687 FROM biblio.record_entry r
688 JOIN metabib.metarecord m USING (fingerprint)
690 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);
696 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
698 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
699 -- Then SELECT migration_tools.create_cards('m_foo');
702 u TEXT := schemaname || '.actor_usr_legacy';
703 c TEXT := schemaname || '.actor_card';
707 EXECUTE ('DELETE FROM ' || c || ';');
708 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
709 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
718 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
720 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
722 my ($marcxml, $shortname) = @_;
730 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
732 foreach my $field ( $marc->field('856') ) {
733 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
734 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
735 $field->add_subfields( '9' => $shortname );
736 $field->update( ind2 => '0');
740 $xml = $marc->as_xml_record;
741 $xml =~ s/^<\?.+?\?>$//mo;
743 $xml =~ s/>\s+</></sgo;
748 $$ LANGUAGE PLPERLU STABLE;
750 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
752 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
754 my ($marcxml, $shortname) = @_;
762 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
764 foreach my $field ( $marc->field('856') ) {
765 if ( ! $field->as_string('9') ) {
766 $field->add_subfields( '9' => $shortname );
770 $xml = $marc->as_xml_record;
771 $xml =~ s/^<\?.+?\?>$//mo;
773 $xml =~ s/>\s+</></sgo;
778 $$ LANGUAGE PLPERLU STABLE;
781 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
793 -- Bail out if asked to change the label to ##URI##
794 IF new_label = '##URI##' THEN
798 -- Gather information
799 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
800 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
801 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
803 -- Bail out if the label already is ##URI##
804 IF old_label = '##URI##' THEN
808 -- Bail out if the call number label is already correct
809 IF new_volume = old_volume THEN
813 -- Check whether we already have a destination volume available
814 SELECT id INTO new_volume FROM asset.call_number
817 owning_lib = owner AND
818 label = new_label AND
821 -- Create destination volume if needed
823 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
824 VALUES (1, 1, bib, owner, new_label, cn_class);
825 SELECT id INTO new_volume FROM asset.call_number
828 owning_lib = owner AND
829 label = new_label AND
833 -- Move copy to destination
834 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
836 -- Delete source volume if it is now empty
837 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
839 DELETE FROM asset.call_number WHERE id = old_volume;
846 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
851 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
855 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
856 $zipdata{$zip} = [$city, $state, $county];
859 if (defined $zipdata{$input}) {
860 my ($city, $state, $county) = @{$zipdata{$input}};
861 return [$city, $state, $county];
862 } elsif (defined $zipdata{substr $input, 0, 5}) {
863 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
864 return [$city, $state, $county];
866 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
869 $$ LANGUAGE PLPERLU STABLE;
871 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
878 errors_found BOOLEAN;
880 parent_shortname TEXT;
886 type_parent_depth INT;
891 errors_found := FALSE;
893 -- Checking actor.org_unit_type
895 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
897 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
898 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
900 IF type_parent IS NOT NULL THEN
902 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
904 IF type_depth - type_parent_depth <> 1 THEN
905 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
906 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
907 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
908 ou_type_name, type_depth, parent_type, type_parent_depth;
909 errors_found := TRUE;
917 -- Checking actor.org_unit
919 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
921 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
922 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;
923 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;
924 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
925 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
926 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;
927 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;
929 IF ou_parent IS NOT NULL THEN
931 IF (org_unit_depth - parent_depth <> 1) OR (
932 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
934 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
935 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
936 errors_found := TRUE;
943 IF NOT errors_found THEN
944 RAISE INFO 'No errors found.';
954 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
958 DELETE FROM asset.opac_visible_copies;
960 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
962 cp.id, cp.circ_lib, cn.record
965 JOIN asset.call_number cn ON (cn.id = cp.call_number)
966 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
967 JOIN asset.copy_location cl ON (cp.location = cl.id)
968 JOIN config.copy_status cs ON (cp.status = cs.id)
969 JOIN biblio.record_entry b ON (cn.record = b.id)
978 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
985 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
991 old_owning_lib INTEGER;
997 -- Gather information
998 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
999 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1000 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1002 -- Bail out if the new_owning_lib is not the ID of an org_unit
1003 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1005 '% is not a valid actor.org_unit ID; no change made.',
1010 -- Bail out discreetly if the owning_lib is already correct
1011 IF new_owning_lib = old_owning_lib THEN
1015 -- Check whether we already have a destination volume available
1016 SELECT id INTO new_volume FROM asset.call_number
1019 owning_lib = new_owning_lib AND
1020 label = old_label AND
1023 -- Create destination volume if needed
1025 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1026 VALUES (1, 1, bib, new_owning_lib, old_label);
1027 SELECT id INTO new_volume FROM asset.call_number
1030 owning_lib = new_owning_lib AND
1031 label = old_label AND
1035 -- Move copy to destination
1036 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1038 -- Delete source volume if it is now empty
1039 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1041 DELETE FROM asset.call_number WHERE id = old_volume;
1046 $$ LANGUAGE plpgsql;
1049 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1051 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1054 new_owning_lib INTEGER;
1058 -- Parse the new_owner as an org unit ID or shortname
1059 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1060 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1061 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1062 ELSIF new_owner ~ E'^[0-9]+$' THEN
1063 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1066 E'You don\'t need to put the actor.org_unit ID in quotes; '
1067 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1068 new_owning_lib := new_owner::INTEGER;
1069 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1073 '% is not a valid actor.org_unit shortname or ID; no change made.',
1080 $$ LANGUAGE plpgsql;
1082 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1085 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1088 MARC::Charset->assume_unicode(1);
1093 my $r = MARC::Record->new_from_xml( $xml );
1094 my $output_xml = $r->as_xml_record();
1102 $func$ LANGUAGE PLPERLU;
1103 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1105 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1107 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1108 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1109 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1110 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1111 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1112 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1113 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1114 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1115 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1116 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1117 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1118 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1119 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1120 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1121 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1122 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1123 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1124 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1125 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1126 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1127 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1128 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1129 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1130 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1131 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1132 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1134 $FUNC$ LANGUAGE PLPGSQL;
1136 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1138 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1139 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1140 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1141 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1142 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1143 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
1144 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
1146 -- import any new circ rules
1147 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1148 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1149 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1150 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1152 -- and permission groups
1153 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1156 $FUNC$ LANGUAGE PLPGSQL;
1159 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$
1168 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1169 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1170 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
1171 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1172 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1173 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1174 FOR name IN EXECUTE loopq LOOP
1175 EXECUTE existsq INTO ct USING name;
1177 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1178 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1179 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1180 EXECUTE copyst USING name;
1184 $FUNC$ LANGUAGE PLPGSQL;
1186 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
1193 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
1194 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;
1195 FOR id IN EXECUTE loopq USING delimiter LOOP
1196 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
1197 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
1198 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
1199 EXECUTE splitst USING id, delimiter;
1202 $FUNC$ LANGUAGE PLPGSQL;
1204 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1210 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1213 MARC::Charset->assume_unicode(1);
1215 my $target_xml = shift;
1216 my $source_xml = shift;
1222 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1226 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1231 my $source_id = $source->subfield('901', 'c');
1232 $source_id = $source->subfield('903', 'a') unless $source_id;
1233 my $target_id = $target->subfield('901', 'c');
1234 $target_id = $target->subfield('903', 'a') unless $target_id;
1236 my %existing_fields;
1237 foreach my $tag (@$tags) {
1238 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1239 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1240 $target->insert_fields_ordered(map { $_->clone() } @to_add);
1242 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1246 my $xml = $target->as_xml_record;
1247 $xml =~ s/^<\?.+?\?>$//mo;
1249 $xml =~ s/>\s+</></sgo;
1253 $func$ LANGUAGE PLPERLU;
1254 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.';
1256 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1262 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1265 my $in_tags = shift;
1266 my $in_values = shift;
1268 # hack-and-slash parsing of array-passed-as-string;
1269 # this can go away once everybody is running Postgres 9.1+
1270 my $csv = Text::CSV->new({binary => 1});
1273 my $status = $csv->parse($in_tags);
1274 my $tags = [ $csv->fields() ];
1275 $in_values =~ s/^{//;
1276 $in_values =~ s/}$//;
1277 $status = $csv->parse($in_values);
1278 my $values = [ $csv->fields() ];
1280 my $marc = MARC::Record->new();
1282 $marc->leader('00000nam a22000007 4500');
1283 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
1285 foreach my $i (0..$#$tags) {
1287 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1290 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1291 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1293 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1297 my $xml = $marc->as_xml_record;
1298 $xml =~ s/^<\?.+?\?>$//mo;
1300 $xml =~ s/>\s+</></sgo;
1304 $func$ LANGUAGE PLPERLU;
1305 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
1306 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
1307 The second argument is an array of text containing the values to plug into each field.
1308 If the value for a given field is NULL or the empty string, it is not inserted.
1311 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1313 my ($marcxml, $tag, $pos, $value) = @_;
1316 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1320 MARC::Charset->assume_unicode(1);
1322 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
1323 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
1324 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
1325 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
1329 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1331 foreach my $field ($marc->field($tag)) {
1332 $field->update("ind$pos" => $value);
1334 $xml = $marc->as_xml_record;
1335 $xml =~ s/^<\?.+?\?>$//mo;
1337 $xml =~ s/>\s+</></sgo;
1341 $func$ LANGUAGE PLPERLU;
1343 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
1344 The first argument is a MARCXML string.
1345 The second argument is a MARC tag.
1346 The third argument is the indicator position, either 1 or 2.
1347 The fourth argument is the character to set the indicator value to.
1348 All occurences of the specified field will be changed.
1349 The function returns the revised MARCXML string.$$;
1351 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
1356 first_name TEXT DEFAULT '',
1357 last_name TEXT DEFAULT ''
1358 ) RETURNS VOID AS $func$
1360 RAISE NOTICE '%', org ;
1361 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
1362 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
1363 FROM actor.org_unit aou, permission.grp_tree pgt
1364 WHERE aou.shortname = org
1365 AND pgt.name = perm_group;
1370 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
1371 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
1373 target_event_def ALIAS FOR $1;
1376 DROP TABLE IF EXISTS new_atevdefs;
1377 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1378 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1379 INSERT INTO action_trigger.event_definition (
1400 ,name || ' (clone of '||target_event_def||')'
1416 action_trigger.event_definition
1418 id = target_event_def
1420 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1421 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1422 INSERT INTO action_trigger.environment (
1428 currval('action_trigger.event_definition_id_seq')
1433 action_trigger.environment
1435 event_def = target_event_def
1437 INSERT INTO action_trigger.event_params (
1442 currval('action_trigger.event_definition_id_seq')
1446 action_trigger.event_params
1448 event_def = target_event_def
1451 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);
1453 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1455 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
1456 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
1458 target_event_def ALIAS FOR $1;
1460 new_interval ALIAS FOR $3;
1462 DROP TABLE IF EXISTS new_atevdefs;
1463 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1464 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1465 INSERT INTO action_trigger.event_definition (
1486 ,name || ' (clone of '||target_event_def||')'
1502 action_trigger.event_definition
1504 id = target_event_def
1506 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1507 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1508 INSERT INTO action_trigger.environment (
1514 currval('action_trigger.event_definition_id_seq')
1519 action_trigger.environment
1521 event_def = target_event_def
1523 INSERT INTO action_trigger.event_params (
1528 currval('action_trigger.event_definition_id_seq')
1532 action_trigger.event_params
1534 event_def = target_event_def
1537 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);
1539 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1541 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
1542 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
1545 target_event_defs ALIAS FOR $2;
1547 DROP TABLE IF EXISTS new_atevdefs;
1548 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1549 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
1550 INSERT INTO action_trigger.event_definition (
1571 ,name || ' (clone of '||target_event_defs[i]||')'
1587 action_trigger.event_definition
1589 id = target_event_defs[i]
1591 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1592 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1593 INSERT INTO action_trigger.environment (
1599 currval('action_trigger.event_definition_id_seq')
1604 action_trigger.environment
1606 event_def = target_event_defs[i]
1608 INSERT INTO action_trigger.event_params (
1613 currval('action_trigger.event_definition_id_seq')
1617 action_trigger.event_params
1619 event_def = target_event_defs[i]
1622 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1624 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1626 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
1628 action_trigger.event
1632 ,complete_time = NULL
1633 ,update_process = NULL
1635 ,template_output = NULL
1636 ,error_output = NULL
1637 ,async_output = NULL
1642 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
1646 use MARC::File::XML;
1651 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1652 $field = $marc->leader();
1655 $$ LANGUAGE PLPERLU STABLE;
1657 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
1658 my ($marcxml, $tag, $subfield, $delimiter) = @_;
1661 use MARC::File::XML;
1666 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1667 $field = $marc->field($tag);
1669 return $field->as_string($subfield,$delimiter);
1670 $$ LANGUAGE PLPERLU STABLE;
1672 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
1673 my ($marcxml, $tag, $subfield, $delimiter) = @_;
1676 use MARC::File::XML;
1681 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1682 @fields = $marc->field($tag);
1685 foreach my $field (@fields) {
1686 push @texts, $field->as_string($subfield,$delimiter);
1689 $$ LANGUAGE PLPERLU STABLE;
1691 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
1692 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
1695 use MARC::File::XML;
1700 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1701 @fields = $marc->field($tag);
1704 foreach my $field (@fields) {
1705 if ($field->as_string() =~ qr/$match/) {
1706 push @texts, $field->as_string($subfield,$delimiter);
1710 $$ LANGUAGE PLPERLU STABLE;
1712 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
1713 SELECT action.find_hold_matrix_matchpoint(
1714 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1715 (SELECT request_lib FROM action.hold_request WHERE id = $1),
1716 (SELECT current_copy FROM action.hold_request WHERE id = $1),
1717 (SELECT usr FROM action.hold_request WHERE id = $1),
1718 (SELECT requestor FROM action.hold_request WHERE id = $1)
1722 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
1723 SELECT action.hold_request_permit_test(
1724 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1725 (SELECT request_lib FROM action.hold_request WHERE id = $1),
1726 (SELECT current_copy FROM action.hold_request WHERE id = $1),
1727 (SELECT usr FROM action.hold_request WHERE id = $1),
1728 (SELECT requestor FROM action.hold_request WHERE id = $1)
1732 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
1733 SELECT action.find_circ_matrix_matchpoint(
1734 (SELECT circ_lib FROM action.circulation WHERE id = $1),
1735 (SELECT target_copy FROM action.circulation WHERE id = $1),
1736 (SELECT usr FROM action.circulation WHERE id = $1),
1738 NULLIF(phone_renewal,false),
1739 NULLIF(desk_renewal,false),
1740 NULLIF(opac_renewal,false),
1742 ) FROM action.circulation WHERE id = $1
1747 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
1752 RAISE EXCEPTION 'assertion';
1755 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1757 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
1763 RAISE EXCEPTION '%', msg;
1766 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1768 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
1771 fail_msg ALIAS FOR $2;
1772 success_msg ALIAS FOR $3;
1775 RAISE EXCEPTION '%', fail_msg;
1779 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1781 -- push bib sequence and return starting value for reserved range
1782 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
1784 bib_count ALIAS FOR $1;
1787 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
1789 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
1794 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1796 -- set a new salted password
1798 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
1800 usr_id ALIAS FOR $1;
1801 plain_passwd ALIAS FOR $2;
1806 SELECT actor.create_salt('main') INTO plain_salt;
1808 SELECT MD5(plain_passwd) INTO md5_passwd;
1810 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
1815 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1818 -- convenience functions for handling copy_location maps
1819 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1820 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
1823 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
1825 table_schema ALIAS FOR $1;
1826 table_name ALIAS FOR $2;
1827 org_shortname ALIAS FOR $3;
1828 org_range ALIAS FOR $4;
1829 make_assertion ALIAS FOR $5;
1832 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
1833 -- though we'll still use the passed org for the full path traversal when needed
1834 x_org_found BOOLEAN;
1840 EXECUTE 'SELECT EXISTS (
1842 FROM information_schema.columns
1843 WHERE table_schema = $1
1845 and column_name = ''desired_shelf''
1846 )' INTO proceed USING table_schema, table_name;
1848 RAISE EXCEPTION 'Missing column desired_shelf';
1851 EXECUTE 'SELECT EXISTS (
1853 FROM information_schema.columns
1854 WHERE table_schema = $1
1856 and column_name = ''x_org''
1857 )' INTO x_org_found USING table_schema, table_name;
1859 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1861 RAISE EXCEPTION 'Cannot find org by shortname';
1864 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1866 EXECUTE 'ALTER TABLE '
1867 || quote_ident(table_name)
1868 || ' DROP COLUMN IF EXISTS x_shelf';
1869 EXECUTE 'ALTER TABLE '
1870 || quote_ident(table_name)
1871 || ' ADD COLUMN x_shelf INTEGER';
1874 RAISE INFO 'Found x_org column';
1875 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1876 || ' SET x_shelf = b.id FROM asset_copy_location b'
1877 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1878 || ' AND b.owning_lib = x_org'
1879 || ' AND NOT b.deleted';
1880 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1881 || ' SET x_shelf = b.id FROM asset.copy_location b'
1882 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1883 || ' AND b.owning_lib = x_org'
1884 || ' AND x_shelf IS NULL'
1885 || ' AND NOT b.deleted';
1887 RAISE INFO 'Did not find x_org column';
1888 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1889 || ' SET x_shelf = b.id FROM asset_copy_location b'
1890 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1891 || ' AND b.owning_lib = $1'
1892 || ' AND NOT b.deleted'
1894 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1895 || ' SET x_shelf = b.id FROM asset_copy_location b'
1896 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1897 || ' AND b.owning_lib = $1'
1898 || ' AND x_shelf IS NULL'
1899 || ' AND NOT b.deleted'
1903 FOREACH o IN ARRAY org_list LOOP
1904 RAISE INFO 'Considering org %', o;
1905 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1906 || ' SET x_shelf = b.id FROM asset.copy_location b'
1907 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1908 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
1909 || ' AND NOT b.deleted'
1911 GET DIAGNOSTICS row_count = ROW_COUNT;
1912 RAISE INFO 'Updated % rows', row_count;
1915 IF make_assertion THEN
1916 EXECUTE 'SELECT migration_tools.assert(
1917 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
1918 ''Cannot find a desired location'',
1919 ''Found all desired locations''
1924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1926 -- convenience functions for handling circmod maps
1928 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
1930 table_schema ALIAS FOR $1;
1931 table_name ALIAS FOR $2;
1934 EXECUTE 'SELECT EXISTS (
1936 FROM information_schema.columns
1937 WHERE table_schema = $1
1939 and column_name = ''desired_circmod''
1940 )' INTO proceed USING table_schema, table_name;
1942 RAISE EXCEPTION 'Missing column desired_circmod';
1945 EXECUTE 'ALTER TABLE '
1946 || quote_ident(table_name)
1947 || ' DROP COLUMN IF EXISTS x_circmod';
1948 EXECUTE 'ALTER TABLE '
1949 || quote_ident(table_name)
1950 || ' ADD COLUMN x_circmod TEXT';
1952 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1953 || ' SET x_circmod = code FROM config.circ_modifier b'
1954 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
1956 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1957 || ' SET x_circmod = code FROM config.circ_modifier b'
1958 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
1959 || ' AND x_circmod IS NULL';
1961 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1962 || ' SET x_circmod = code FROM config.circ_modifier b'
1963 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
1964 || ' AND x_circmod IS NULL';
1966 EXECUTE 'SELECT migration_tools.assert(
1967 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
1968 ''Cannot find a desired circulation modifier'',
1969 ''Found all desired circulation modifiers''
1973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1975 -- convenience functions for handling item status maps
1977 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
1979 table_schema ALIAS FOR $1;
1980 table_name ALIAS FOR $2;
1983 EXECUTE 'SELECT EXISTS (
1985 FROM information_schema.columns
1986 WHERE table_schema = $1
1988 and column_name = ''desired_status''
1989 )' INTO proceed USING table_schema, table_name;
1991 RAISE EXCEPTION 'Missing column desired_status';
1994 EXECUTE 'ALTER TABLE '
1995 || quote_ident(table_name)
1996 || ' DROP COLUMN IF EXISTS x_status';
1997 EXECUTE 'ALTER TABLE '
1998 || quote_ident(table_name)
1999 || ' ADD COLUMN x_status INTEGER';
2001 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2002 || ' SET x_status = id FROM config.copy_status b'
2003 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
2005 EXECUTE 'SELECT migration_tools.assert(
2006 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
2007 ''Cannot find a desired copy status'',
2008 ''Found all desired copy statuses''
2012 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2014 -- convenience functions for handling org maps
2016 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2018 table_schema ALIAS FOR $1;
2019 table_name ALIAS FOR $2;
2022 EXECUTE 'SELECT EXISTS (
2024 FROM information_schema.columns
2025 WHERE table_schema = $1
2027 and column_name = ''desired_org''
2028 )' INTO proceed USING table_schema, table_name;
2030 RAISE EXCEPTION 'Missing column desired_org';
2033 EXECUTE 'ALTER TABLE '
2034 || quote_ident(table_name)
2035 || ' DROP COLUMN IF EXISTS x_org';
2036 EXECUTE 'ALTER TABLE '
2037 || quote_ident(table_name)
2038 || ' ADD COLUMN x_org INTEGER';
2040 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2041 || ' SET x_org = b.id FROM actor.org_unit b'
2042 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
2044 EXECUTE 'SELECT migration_tools.assert(
2045 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
2046 ''Cannot find a desired org unit'',
2047 ''Found all desired org units''
2051 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2053 -- convenience function for handling desired_not_migrate
2055 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2057 table_schema ALIAS FOR $1;
2058 table_name ALIAS FOR $2;
2061 EXECUTE 'SELECT EXISTS (
2063 FROM information_schema.columns
2064 WHERE table_schema = $1
2066 and column_name = ''desired_not_migrate''
2067 )' INTO proceed USING table_schema, table_name;
2069 RAISE EXCEPTION 'Missing column desired_not_migrate';
2072 EXECUTE 'ALTER TABLE '
2073 || quote_ident(table_name)
2074 || ' DROP COLUMN IF EXISTS x_migrate';
2075 EXECUTE 'ALTER TABLE '
2076 || quote_ident(table_name)
2077 || ' ADD COLUMN x_migrate BOOLEAN';
2079 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2080 || ' SET x_migrate = CASE'
2081 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
2082 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
2083 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
2084 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
2085 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
2086 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
2089 EXECUTE 'SELECT migration_tools.assert(
2090 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
2091 ''Not all desired_not_migrate values understood'',
2092 ''All desired_not_migrate values understood''
2096 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2098 -- convenience function for handling desired_not_migrate
2100 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
2102 table_schema ALIAS FOR $1;
2103 table_name ALIAS FOR $2;
2106 EXECUTE 'SELECT EXISTS (
2108 FROM information_schema.columns
2109 WHERE table_schema = $1
2111 and column_name = ''desired_barred_or_blocked''
2112 )' INTO proceed USING table_schema, table_name;
2114 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
2117 EXECUTE 'ALTER TABLE '
2118 || quote_ident(table_name)
2119 || ' DROP COLUMN IF EXISTS x_barred';
2120 EXECUTE 'ALTER TABLE '
2121 || quote_ident(table_name)
2122 || ' ADD COLUMN x_barred BOOLEAN';
2124 EXECUTE 'ALTER TABLE '
2125 || quote_ident(table_name)
2126 || ' DROP COLUMN IF EXISTS x_blocked';
2127 EXECUTE 'ALTER TABLE '
2128 || quote_ident(table_name)
2129 || ' ADD COLUMN x_blocked BOOLEAN';
2131 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2132 || ' SET x_barred = CASE'
2133 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
2134 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
2135 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
2136 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
2139 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2140 || ' SET x_blocked = CASE'
2141 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
2142 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
2143 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
2144 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
2147 EXECUTE 'SELECT migration_tools.assert(
2148 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
2149 ''Not all desired_barred_or_blocked values understood'',
2150 ''All desired_barred_or_blocked values understood''
2154 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2156 -- convenience function for handling desired_profile
2158 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
2160 table_schema ALIAS FOR $1;
2161 table_name ALIAS FOR $2;
2164 EXECUTE 'SELECT EXISTS (
2166 FROM information_schema.columns
2167 WHERE table_schema = $1
2169 and column_name = ''desired_profile''
2170 )' INTO proceed USING table_schema, table_name;
2172 RAISE EXCEPTION 'Missing column desired_profile';
2175 EXECUTE 'ALTER TABLE '
2176 || quote_ident(table_name)
2177 || ' DROP COLUMN IF EXISTS x_profile';
2178 EXECUTE 'ALTER TABLE '
2179 || quote_ident(table_name)
2180 || ' ADD COLUMN x_profile INTEGER';
2182 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2183 || ' SET x_profile = b.id FROM permission.grp_tree b'
2184 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
2186 EXECUTE 'SELECT migration_tools.assert(
2187 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
2188 ''Cannot find a desired profile'',
2189 ''Found all desired profiles''
2193 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2195 -- convenience function for handling desired actor stat cats
2197 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2199 table_schema ALIAS FOR $1;
2200 table_name ALIAS FOR $2;
2201 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2202 org_shortname ALIAS FOR $4;
2210 SELECT 'desired_sc' || field_suffix INTO sc;
2211 SELECT 'desired_sce' || field_suffix INTO sce;
2213 EXECUTE 'SELECT EXISTS (
2215 FROM information_schema.columns
2216 WHERE table_schema = $1
2218 and column_name = $3
2219 )' INTO proceed USING table_schema, table_name, sc;
2221 RAISE EXCEPTION 'Missing column %', sc;
2223 EXECUTE 'SELECT EXISTS (
2225 FROM information_schema.columns
2226 WHERE table_schema = $1
2228 and column_name = $3
2229 )' INTO proceed USING table_schema, table_name, sce;
2231 RAISE EXCEPTION 'Missing column %', sce;
2234 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2236 RAISE EXCEPTION 'Cannot find org by shortname';
2238 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2240 -- caller responsible for their own truncates though we try to prevent duplicates
2241 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
2246 ' || quote_ident(table_name) || '
2248 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2252 WHERE owner = ANY ($2)
2253 AND name = BTRIM('||sc||')
2258 WHERE owner = ANY ($2)
2259 AND name = BTRIM('||sc||')
2262 USING org, org_list;
2264 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
2269 WHERE owner = ANY ($2)
2270 AND BTRIM('||sc||') = BTRIM(name))
2273 WHERE owner = ANY ($2)
2274 AND BTRIM('||sc||') = BTRIM(name))
2279 ' || quote_ident(table_name) || '
2281 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2282 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
2285 FROM actor.stat_cat_entry
2289 WHERE owner = ANY ($2)
2290 AND BTRIM('||sc||') = BTRIM(name)
2291 ) AND value = BTRIM('||sce||')
2292 AND owner = ANY ($2)
2296 FROM actor_stat_cat_entry
2300 WHERE owner = ANY ($2)
2301 AND BTRIM('||sc||') = BTRIM(name)
2302 ) AND value = BTRIM('||sce||')
2303 AND owner = ANY ($2)
2306 USING org, org_list;
2308 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2310 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2312 table_schema ALIAS FOR $1;
2313 table_name ALIAS FOR $2;
2314 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2315 org_shortname ALIAS FOR $4;
2323 SELECT 'desired_sc' || field_suffix INTO sc;
2324 SELECT 'desired_sce' || field_suffix INTO sce;
2325 EXECUTE 'SELECT EXISTS (
2327 FROM information_schema.columns
2328 WHERE table_schema = $1
2330 and column_name = $3
2331 )' INTO proceed USING table_schema, table_name, sc;
2333 RAISE EXCEPTION 'Missing column %', sc;
2335 EXECUTE 'SELECT EXISTS (
2337 FROM information_schema.columns
2338 WHERE table_schema = $1
2340 and column_name = $3
2341 )' INTO proceed USING table_schema, table_name, sce;
2343 RAISE EXCEPTION 'Missing column %', sce;
2346 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2348 RAISE EXCEPTION 'Cannot find org by shortname';
2351 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2353 EXECUTE 'ALTER TABLE '
2354 || quote_ident(table_name)
2355 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
2356 EXECUTE 'ALTER TABLE '
2357 || quote_ident(table_name)
2358 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
2359 EXECUTE 'ALTER TABLE '
2360 || quote_ident(table_name)
2361 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
2362 EXECUTE 'ALTER TABLE '
2363 || quote_ident(table_name)
2364 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
2367 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2369 x_sc' || field_suffix || ' = id
2371 (SELECT id, name, owner FROM actor_stat_cat
2372 UNION SELECT id, name, owner FROM actor.stat_cat) u
2374 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2375 AND u.owner = ANY ($1);'
2378 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2380 x_sce' || field_suffix || ' = id
2382 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
2383 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
2385 u.stat_cat = x_sc' || field_suffix || '
2386 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2387 AND u.owner = ANY ($1);'
2390 EXECUTE 'SELECT migration_tools.assert(
2391 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
2392 ''Cannot find a desired stat cat'',
2393 ''Found all desired stat cats''
2396 EXECUTE 'SELECT migration_tools.assert(
2397 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
2398 ''Cannot find a desired stat cat entry'',
2399 ''Found all desired stat cat entries''
2403 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2405 -- convenience functions for adding shelving locations
2406 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
2407 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2413 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2416 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2417 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2418 IF return_id IS NOT NULL THEN
2426 $$ LANGUAGE plpgsql;
2428 -- may remove later but testing using this with new migration scripts and not loading acls until go live
2430 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
2431 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2437 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2440 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2442 SELECT INTO return_id id FROM
2443 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
2444 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2445 IF return_id IS NOT NULL THEN
2453 $$ LANGUAGE plpgsql;
2455 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
2456 CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
2463 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
2465 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
2466 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
2470 $BODY$ LANGUAGE plpgsql;
2472 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
2473 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
2481 use MARC::File::XML (BinaryEncoding => 'utf8');
2483 binmode(STDERR, ':bytes');
2484 binmode(STDOUT, ':utf8');
2485 binmode(STDERR, ':utf8');
2487 my $marc_xml = shift;
2488 my $new_9_to_set = shift;
2491 $marc_xml =~ s/(<leader>.........)./${1}a/;
2494 $marc_xml = MARC::Record->new_from_xml($marc_xml);
2497 #elog("could not parse $bibid: $@\n");
2498 import MARC::File::XML (BinaryEncoding => 'utf8');
2502 my @uris = $marc_xml->field('856');
2503 return $marc_xml->as_xml_record() unless @uris;
2505 foreach my $field (@uris) {
2506 my $ind1 = $field->indicator('1');
2507 if (!defined $ind1) { next; }
2508 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
2509 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
2510 my $ind2 = $field->indicator('2');
2511 if (!defined $ind2) { next; }
2512 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
2513 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
2514 $field->add_subfields( '9' => $new_9_to_set );
2517 return $marc_xml->as_xml_record();
2521 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
2522 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
2530 use MARC::File::XML (BinaryEncoding => 'utf8');
2532 binmode(STDERR, ':bytes');
2533 binmode(STDOUT, ':utf8');
2534 binmode(STDERR, ':utf8');
2536 my $marc_xml = shift;
2537 my $qualifying_match = shift;
2538 my $new_9_to_set = shift;
2541 $marc_xml =~ s/(<leader>.........)./${1}a/;
2544 $marc_xml = MARC::Record->new_from_xml($marc_xml);
2547 #elog("could not parse $bibid: $@\n");
2548 import MARC::File::XML (BinaryEncoding => 'utf8');
2552 my @uris = $marc_xml->field('856');
2553 return $marc_xml->as_xml_record() unless @uris;
2555 foreach my $field (@uris) {
2556 if ($field->as_string() =~ qr/$qualifying_match/) {
2557 my $ind1 = $field->indicator('1');
2558 if (!defined $ind1) { next; }
2559 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
2560 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
2561 my $ind2 = $field->indicator('2');
2562 if (!defined $ind2) { next; }
2563 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
2564 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
2565 $field->add_subfields( '9' => $new_9_to_set );
2569 return $marc_xml->as_xml_record();
2573 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
2574 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
2582 use MARC::File::XML (BinaryEncoding => 'utf8');
2584 binmode(STDERR, ':bytes');
2585 binmode(STDOUT, ':utf8');
2586 binmode(STDERR, ':utf8');
2588 my $marc_xml = shift;
2589 my $substring_old_value = shift;
2590 my $new_value = shift;
2591 my $fix_indicators = shift;
2593 $marc_xml =~ s/(<leader>.........)./${1}a/;
2596 $marc_xml = MARC::Record->new_from_xml($marc_xml);
2599 #elog("could not parse $bibid: $@\n");
2600 import MARC::File::XML (BinaryEncoding => 'utf8');
2604 my @uris = $marc_xml->field('856');
2605 return $marc_xml->as_xml_record() unless @uris;
2607 foreach my $field (@uris) {
2608 my $ind1 = $field->indicator('1');
2609 if (defined $ind1) {
2610 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
2611 $field->set_indicator(1,'4');
2614 my $ind2 = $field->indicator('2');
2615 if (defined $ind2) {
2616 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
2617 $field->set_indicator(2,'0');
2620 if ($field->as_string('9') =~ qr/$substring_old_value/) {
2621 $field->delete_subfield('9');
2622 $field->add_subfields( '9' => $new_value );
2624 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
2627 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
2628 $marc_xml->insert_fields_ordered( values( %hash ) );
2630 return $marc_xml->as_xml_record();
2634 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
2635 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
2643 use MARC::File::XML (BinaryEncoding => 'utf8');
2645 binmode(STDERR, ':bytes');
2646 binmode(STDOUT, ':utf8');
2647 binmode(STDERR, ':utf8');
2649 my $marc_xml = shift;
2650 my $qualifying_match = shift;
2651 my $substring_old_value = shift;
2652 my $new_value = shift;
2653 my $fix_indicators = shift;
2655 $marc_xml =~ s/(<leader>.........)./${1}a/;
2658 $marc_xml = MARC::Record->new_from_xml($marc_xml);
2661 #elog("could not parse $bibid: $@\n");
2662 import MARC::File::XML (BinaryEncoding => 'utf8');
2666 my @unqualified_uris = $marc_xml->field('856');
2668 foreach my $field (@unqualified_uris) {
2669 if ($field->as_string() =~ qr/$qualifying_match/) {
2673 return $marc_xml->as_xml_record() unless @uris;
2675 foreach my $field (@uris) {
2676 my $ind1 = $field->indicator('1');
2677 if (defined $ind1) {
2678 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
2679 $field->set_indicator(1,'4');
2682 my $ind2 = $field->indicator('2');
2683 if (defined $ind2) {
2684 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
2685 $field->set_indicator(2,'0');
2688 if ($field->as_string('9') =~ qr/$substring_old_value/) {
2689 $field->delete_subfield('9');
2690 $field->add_subfields( '9' => $new_value );
2692 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
2695 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
2696 $marc_xml->insert_fields_ordered( values( %hash ) );
2698 return $marc_xml->as_xml_record();
2703 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
2704 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
2712 use MARC::File::XML (BinaryEncoding => 'utf8');
2714 binmode(STDERR, ':bytes');
2715 binmode(STDOUT, ':utf8');
2716 binmode(STDERR, ':utf8');
2718 my $marc_xml = shift;
2721 $marc_xml =~ s/(<leader>.........)./${1}a/;
2724 $marc_xml = MARC::Record->new_from_xml($marc_xml);
2727 #elog("could not parse $bibid: $@\n");
2728 import MARC::File::XML (BinaryEncoding => 'utf8');
2732 my @fields = $marc_xml->field($tag);
2733 return $marc_xml->as_xml_record() unless @fields;
2735 $marc_xml->delete_fields(@fields);
2737 return $marc_xml->as_xml_record();
2741 -- convenience function for linking to the item staging table
2743 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2745 table_schema ALIAS FOR $1;
2746 table_name ALIAS FOR $2;
2747 foreign_column_name ALIAS FOR $3;
2748 main_column_name ALIAS FOR $4;
2749 btrim_desired ALIAS FOR $5;
2752 EXECUTE 'SELECT EXISTS (
2754 FROM information_schema.columns
2755 WHERE table_schema = $1
2757 and column_name = $3
2758 )' INTO proceed USING table_schema, table_name, foreign_column_name;
2760 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2763 EXECUTE 'SELECT EXISTS (
2765 FROM information_schema.columns
2766 WHERE table_schema = $1
2767 AND table_name = ''asset_copy_legacy''
2768 and column_name = $2
2769 )' INTO proceed USING table_schema, main_column_name;
2771 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
2774 EXECUTE 'ALTER TABLE '
2775 || quote_ident(table_name)
2776 || ' DROP COLUMN IF EXISTS x_item';
2777 EXECUTE 'ALTER TABLE '
2778 || quote_ident(table_name)
2779 || ' ADD COLUMN x_item BIGINT';
2781 IF btrim_desired THEN
2782 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2783 || ' SET x_item = b.id FROM asset_copy_legacy b'
2784 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2785 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2787 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2788 || ' SET x_item = b.id FROM asset_copy_legacy b'
2789 || ' WHERE a.' || quote_ident(foreign_column_name)
2790 || ' = b.' || quote_ident(main_column_name);
2793 --EXECUTE 'SELECT migration_tools.assert(
2794 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
2795 -- ''Cannot link every barcode'',
2796 -- ''Every barcode linked''
2800 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2802 -- convenience function for linking to the user staging table
2804 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2806 table_schema ALIAS FOR $1;
2807 table_name ALIAS FOR $2;
2808 foreign_column_name ALIAS FOR $3;
2809 main_column_name ALIAS FOR $4;
2810 btrim_desired ALIAS FOR $5;
2813 EXECUTE 'SELECT EXISTS (
2815 FROM information_schema.columns
2816 WHERE table_schema = $1
2818 and column_name = $3
2819 )' INTO proceed USING table_schema, table_name, foreign_column_name;
2821 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2824 EXECUTE 'SELECT EXISTS (
2826 FROM information_schema.columns
2827 WHERE table_schema = $1
2828 AND table_name = ''actor_usr_legacy''
2829 and column_name = $2
2830 )' INTO proceed USING table_schema, main_column_name;
2832 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
2835 EXECUTE 'ALTER TABLE '
2836 || quote_ident(table_name)
2837 || ' DROP COLUMN IF EXISTS x_user';
2838 EXECUTE 'ALTER TABLE '
2839 || quote_ident(table_name)
2840 || ' ADD COLUMN x_user INTEGER';
2842 IF btrim_desired THEN
2843 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2844 || ' SET x_user = b.id FROM actor_usr_legacy b'
2845 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2846 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2848 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2849 || ' SET x_user = b.id FROM actor_usr_legacy b'
2850 || ' WHERE a.' || quote_ident(foreign_column_name)
2851 || ' = b.' || quote_ident(main_column_name);
2854 --EXECUTE 'SELECT migration_tools.assert(
2855 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
2856 -- ''Cannot link every barcode'',
2857 -- ''Every barcode linked''
2861 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2863 -- convenience function for linking two tables
2864 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
2865 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2867 table_schema ALIAS FOR $1;
2868 table_a ALIAS FOR $2;
2869 column_a ALIAS FOR $3;
2870 table_b ALIAS FOR $4;
2871 column_b ALIAS FOR $5;
2872 column_x ALIAS FOR $6;
2873 btrim_desired ALIAS FOR $7;
2876 EXECUTE 'SELECT EXISTS (
2878 FROM information_schema.columns
2879 WHERE table_schema = $1
2881 and column_name = $3
2882 )' INTO proceed USING table_schema, table_a, column_a;
2884 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2887 EXECUTE 'SELECT EXISTS (
2889 FROM information_schema.columns
2890 WHERE table_schema = $1
2892 and column_name = $3
2893 )' INTO proceed USING table_schema, table_b, column_b;
2895 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2898 EXECUTE 'ALTER TABLE '
2899 || quote_ident(table_b)
2900 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2901 EXECUTE 'ALTER TABLE '
2902 || quote_ident(table_b)
2903 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
2905 IF btrim_desired THEN
2906 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2907 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2908 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2909 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2911 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2912 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2913 || ' WHERE a.' || quote_ident(column_a)
2914 || ' = b.' || quote_ident(column_b);
2918 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2920 -- convenience function for linking two tables, but copying column w into column x instead of "id"
2921 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
2922 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2924 table_schema ALIAS FOR $1;
2925 table_a ALIAS FOR $2;
2926 column_a ALIAS FOR $3;
2927 table_b ALIAS FOR $4;
2928 column_b ALIAS FOR $5;
2929 column_w ALIAS FOR $6;
2930 column_x ALIAS FOR $7;
2931 btrim_desired ALIAS FOR $8;
2934 EXECUTE 'SELECT EXISTS (
2936 FROM information_schema.columns
2937 WHERE table_schema = $1
2939 and column_name = $3
2940 )' INTO proceed USING table_schema, table_a, column_a;
2942 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2945 EXECUTE 'SELECT EXISTS (
2947 FROM information_schema.columns
2948 WHERE table_schema = $1
2950 and column_name = $3
2951 )' INTO proceed USING table_schema, table_b, column_b;
2953 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2956 EXECUTE 'ALTER TABLE '
2957 || quote_ident(table_b)
2958 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2959 EXECUTE 'ALTER TABLE '
2960 || quote_ident(table_b)
2961 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
2963 IF btrim_desired THEN
2964 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2965 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2966 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2967 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2969 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2970 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2971 || ' WHERE a.' || quote_ident(column_a)
2972 || ' = b.' || quote_ident(column_b);
2976 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2978 -- 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
2979 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
2980 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2982 table_schema ALIAS FOR $1;
2983 table_a ALIAS FOR $2;
2984 column_a ALIAS FOR $3;
2985 table_b ALIAS FOR $4;
2986 column_b ALIAS FOR $5;
2987 column_w ALIAS FOR $6;
2988 column_x ALIAS FOR $7;
2991 EXECUTE 'SELECT EXISTS (
2993 FROM information_schema.columns
2994 WHERE table_schema = $1
2996 and column_name = $3
2997 )' INTO proceed USING table_schema, table_a, column_a;
2999 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3002 EXECUTE 'SELECT EXISTS (
3004 FROM information_schema.columns
3005 WHERE table_schema = $1
3007 and column_name = $3
3008 )' INTO proceed USING table_schema, table_b, column_b;
3010 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3013 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3014 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3015 || ' WHERE a.' || quote_ident(column_a)
3016 || ' = b.' || quote_ident(column_b);
3019 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3021 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3023 table_schema ALIAS FOR $1;
3024 table_a ALIAS FOR $2;
3025 column_a ALIAS FOR $3;
3026 table_b ALIAS FOR $4;
3027 column_b ALIAS FOR $5;
3028 column_w ALIAS FOR $6;
3029 column_x ALIAS FOR $7;
3032 EXECUTE 'SELECT EXISTS (
3034 FROM information_schema.columns
3035 WHERE table_schema = $1
3037 and column_name = $3
3038 )' INTO proceed USING table_schema, table_a, column_a;
3040 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3043 EXECUTE 'SELECT EXISTS (
3045 FROM information_schema.columns
3046 WHERE table_schema = $1
3048 and column_name = $3
3049 )' INTO proceed USING table_schema, table_b, column_b;
3051 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3054 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3055 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3056 || ' WHERE a.' || quote_ident(column_a)
3057 || ' = b.' || quote_ident(column_b)
3058 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
3061 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3063 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3065 table_schema ALIAS FOR $1;
3066 table_a ALIAS FOR $2;
3067 column_a ALIAS FOR $3;
3068 table_b ALIAS FOR $4;
3069 column_b ALIAS FOR $5;
3070 column_w ALIAS FOR $6;
3071 column_x ALIAS FOR $7;
3074 EXECUTE 'SELECT EXISTS (
3076 FROM information_schema.columns
3077 WHERE table_schema = $1
3079 and column_name = $3
3080 )' INTO proceed USING table_schema, table_a, column_a;
3082 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3085 EXECUTE 'SELECT EXISTS (
3087 FROM information_schema.columns
3088 WHERE table_schema = $1
3090 and column_name = $3
3091 )' INTO proceed USING table_schema, table_b, column_b;
3093 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3096 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3097 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3098 || ' WHERE a.' || quote_ident(column_a)
3099 || ' = b.' || quote_ident(column_b)
3100 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
3103 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3105 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3107 table_schema ALIAS FOR $1;
3108 table_a ALIAS FOR $2;
3109 column_a ALIAS FOR $3;
3110 table_b ALIAS FOR $4;
3111 column_b ALIAS FOR $5;
3112 column_w ALIAS FOR $6;
3113 column_x ALIAS FOR $7;
3116 EXECUTE 'SELECT EXISTS (
3118 FROM information_schema.columns
3119 WHERE table_schema = $1
3121 and column_name = $3
3122 )' INTO proceed USING table_schema, table_a, column_a;
3124 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3127 EXECUTE 'SELECT EXISTS (
3129 FROM information_schema.columns
3130 WHERE table_schema = $1
3132 and column_name = $3
3133 )' INTO proceed USING table_schema, table_b, column_b;
3135 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3138 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3139 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3140 || ' WHERE a.' || quote_ident(column_a)
3141 || ' = b.' || quote_ident(column_b)
3142 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
3145 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3147 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3149 table_schema ALIAS FOR $1;
3150 table_a ALIAS FOR $2;
3151 column_a ALIAS FOR $3;
3152 table_b ALIAS FOR $4;
3153 column_b ALIAS FOR $5;
3154 column_w ALIAS FOR $6;
3155 column_x ALIAS FOR $7;
3158 EXECUTE 'SELECT EXISTS (
3160 FROM information_schema.columns
3161 WHERE table_schema = $1
3163 and column_name = $3
3164 )' INTO proceed USING table_schema, table_a, column_a;
3166 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3169 EXECUTE 'SELECT EXISTS (
3171 FROM information_schema.columns
3172 WHERE table_schema = $1
3174 and column_name = $3
3175 )' INTO proceed USING table_schema, table_b, column_b;
3177 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3180 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3181 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
3182 || ' WHERE a.' || quote_ident(column_a)
3183 || ' = b.' || quote_ident(column_b)
3184 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
3187 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3189 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3191 table_schema ALIAS FOR $1;
3192 table_a ALIAS FOR $2;
3193 column_a ALIAS FOR $3;
3194 table_b ALIAS FOR $4;
3195 column_b ALIAS FOR $5;
3196 column_w ALIAS FOR $6;
3197 column_x ALIAS FOR $7;
3200 EXECUTE 'SELECT EXISTS (
3202 FROM information_schema.columns
3203 WHERE table_schema = $1
3205 and column_name = $3
3206 )' INTO proceed USING table_schema, table_a, column_a;
3208 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3211 EXECUTE 'SELECT EXISTS (
3213 FROM information_schema.columns
3214 WHERE table_schema = $1
3216 and column_name = $3
3217 )' INTO proceed USING table_schema, table_b, column_b;
3219 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3222 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
3223 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
3224 || ' WHERE a.' || quote_ident(column_a)
3225 || ' = b.' || quote_ident(column_b)
3226 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
3229 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3231 -- convenience function for handling desired asset stat cats
3233 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3235 table_schema ALIAS FOR $1;
3236 table_name ALIAS FOR $2;
3237 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3238 org_shortname ALIAS FOR $4;
3246 SELECT 'desired_sc' || field_suffix INTO sc;
3247 SELECT 'desired_sce' || field_suffix INTO sce;
3249 EXECUTE 'SELECT EXISTS (
3251 FROM information_schema.columns
3252 WHERE table_schema = $1
3254 and column_name = $3
3255 )' INTO proceed USING table_schema, table_name, sc;
3257 RAISE EXCEPTION 'Missing column %', sc;
3259 EXECUTE 'SELECT EXISTS (
3261 FROM information_schema.columns
3262 WHERE table_schema = $1
3264 and column_name = $3
3265 )' INTO proceed USING table_schema, table_name, sce;
3267 RAISE EXCEPTION 'Missing column %', sce;
3270 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3272 RAISE EXCEPTION 'Cannot find org by shortname';
3274 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3276 -- caller responsible for their own truncates though we try to prevent duplicates
3277 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3282 ' || quote_ident(table_name) || '
3284 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3288 WHERE owner = ANY ($2)
3289 AND name = BTRIM('||sc||')
3294 WHERE owner = ANY ($2)
3295 AND name = BTRIM('||sc||')
3298 USING org, org_list;
3300 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3305 WHERE owner = ANY ($2)
3306 AND BTRIM('||sc||') = BTRIM(name))
3309 WHERE owner = ANY ($2)
3310 AND BTRIM('||sc||') = BTRIM(name))
3315 ' || quote_ident(table_name) || '
3317 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3318 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3321 FROM asset.stat_cat_entry
3325 WHERE owner = ANY ($2)
3326 AND BTRIM('||sc||') = BTRIM(name)
3327 ) AND value = BTRIM('||sce||')
3328 AND owner = ANY ($2)
3332 FROM asset_stat_cat_entry
3336 WHERE owner = ANY ($2)
3337 AND BTRIM('||sc||') = BTRIM(name)
3338 ) AND value = BTRIM('||sce||')
3339 AND owner = ANY ($2)
3342 USING org, org_list;
3344 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3346 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3348 table_schema ALIAS FOR $1;
3349 table_name ALIAS FOR $2;
3350 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3351 org_shortname ALIAS FOR $4;
3359 SELECT 'desired_sc' || field_suffix INTO sc;
3360 SELECT 'desired_sce' || field_suffix INTO sce;
3361 EXECUTE 'SELECT EXISTS (
3363 FROM information_schema.columns
3364 WHERE table_schema = $1
3366 and column_name = $3
3367 )' INTO proceed USING table_schema, table_name, sc;
3369 RAISE EXCEPTION 'Missing column %', sc;
3371 EXECUTE 'SELECT EXISTS (
3373 FROM information_schema.columns
3374 WHERE table_schema = $1
3376 and column_name = $3
3377 )' INTO proceed USING table_schema, table_name, sce;
3379 RAISE EXCEPTION 'Missing column %', sce;
3382 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3384 RAISE EXCEPTION 'Cannot find org by shortname';
3387 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3389 EXECUTE 'ALTER TABLE '
3390 || quote_ident(table_name)
3391 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
3392 EXECUTE 'ALTER TABLE '
3393 || quote_ident(table_name)
3394 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
3395 EXECUTE 'ALTER TABLE '
3396 || quote_ident(table_name)
3397 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
3398 EXECUTE 'ALTER TABLE '
3399 || quote_ident(table_name)
3400 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
3403 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3405 x_sc' || field_suffix || ' = id
3407 (SELECT id, name, owner FROM asset_stat_cat
3408 UNION SELECT id, name, owner FROM asset.stat_cat) u
3410 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3411 AND u.owner = ANY ($1);'
3414 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3416 x_sce' || field_suffix || ' = id
3418 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
3419 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
3421 u.stat_cat = x_sc' || field_suffix || '
3422 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3423 AND u.owner = ANY ($1);'
3426 EXECUTE 'SELECT migration_tools.assert(
3427 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
3428 ''Cannot find a desired stat cat'',
3429 ''Found all desired stat cats''
3432 EXECUTE 'SELECT migration_tools.assert(
3433 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
3434 ''Cannot find a desired stat cat entry'',
3435 ''Found all desired stat cat entries''
3439 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3441 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
3442 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3449 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3451 AND table_schema = s_name
3452 AND (data_type='text' OR data_type='character varying')
3453 AND column_name like 'l_%'
3455 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3462 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
3463 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3470 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3472 AND table_schema = s_name
3473 AND (data_type='text' OR data_type='character varying')
3475 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3482 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
3483 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3490 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3492 AND table_schema = s_name
3493 AND (data_type='text' OR data_type='character varying')
3494 AND column_name like 'l_%'
3496 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3503 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
3504 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
3511 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3513 AND table_schema = s_name
3514 AND (data_type='text' OR data_type='character varying')
3516 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3524 -- convenience function for handling item barcode collisions in asset_copy_legacy
3526 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
3531 internal_collision_count NUMERIC := 0;
3532 incumbent_collision_count NUMERIC := 0;
3534 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
3536 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
3538 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
3539 GET DIAGNOSTICS row_count = ROW_COUNT;
3540 internal_collision_count := internal_collision_count + row_count;
3543 RAISE INFO '% internal collisions', internal_collision_count;
3544 FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
3546 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
3548 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
3549 GET DIAGNOSTICS row_count = ROW_COUNT;
3550 incumbent_collision_count := incumbent_collision_count + row_count;
3553 RAISE INFO '% incumbent collisions', incumbent_collision_count;
3555 $function$ LANGUAGE plpgsql;
3557 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
3558 -- this should be ran prior to populating actor_card
3560 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
3565 internal_collision_count NUMERIC := 0;
3566 incumbent_barcode_collision_count NUMERIC := 0;
3567 incumbent_usrname_collision_count NUMERIC := 0;
3569 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
3571 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3573 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
3574 GET DIAGNOSTICS row_count = ROW_COUNT;
3575 internal_collision_count := internal_collision_count + row_count;
3578 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
3581 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
3583 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3585 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
3586 GET DIAGNOSTICS row_count = ROW_COUNT;
3587 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
3590 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
3593 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
3595 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3597 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
3598 GET DIAGNOSTICS row_count = ROW_COUNT;
3599 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
3602 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
3604 $function$ LANGUAGE plpgsql;