1 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
10 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
11 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
12 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
13 IF n_digits = 7 AND areacode <> '' THEN
14 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
15 output := (areacode || '-' || temp);
22 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
24 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
28 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
30 $$ LANGUAGE PLPGSQL STRICT STABLE;
33 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
35 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
37 $$ LANGUAGE PLPGSQL STRICT STABLE;
40 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
44 -- First make sure the circ matrix is loaded and the circulations
45 -- have been staged to the extent possible (but at the very least
46 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
47 -- circ modifiers must also be in place.
49 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
57 this_duration_rule INT;
59 this_max_fine_rule INT;
60 rcd config.rule_circ_duration%ROWTYPE;
61 rrf config.rule_recurring_fine%ROWTYPE;
62 rmf config.rule_max_fine%ROWTYPE;
69 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
71 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
73 -- Fetch the correct rules for this circulation
80 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
83 FROM ' || tablename || ' WHERE id = ' || circ || ';')
84 INTO circ_lib, target_copy, usr, is_renewal ;
86 INTO this_duration_rule,
92 FROM action.item_user_circ_test(
98 SELECT INTO rcd * FROM config.rule_circ_duration
99 WHERE id = this_duration_rule;
100 SELECT INTO rrf * FROM config.rule_recurring_fine
101 WHERE id = this_fine_rule;
102 SELECT INTO rmf * FROM config.rule_max_fine
103 WHERE id = this_max_fine_rule;
105 -- Apply the rules to this circulation
106 EXECUTE ('UPDATE ' || tablename || ' c
108 duration_rule = rcd.name,
109 recurring_fine_rule = rrf.name,
110 max_fine_rule = rmf.name,
111 duration = rcd.normal,
112 recurring_fine = rrf.normal,
115 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
118 renewal_remaining = rcd.max_renewals
120 config.rule_circ_duration rcd,
121 config.rule_recurring_fine rrf,
122 config.rule_max_fine rmf,
125 rcd.id = ' || this_duration_rule || ' AND
126 rrf.id = ' || this_fine_rule || ' AND
127 rmf.id = ' || this_max_fine_rule || ' AND
128 ac.id = c.target_copy AND
129 c.id = ' || circ || ';');
131 -- Keep track of where we are in the process
133 IF (n % 100 = 0) THEN
134 RAISE INFO '%', n || ' of ' || n_circs
135 || ' (' || (100*n/n_circs) || '%) circs updated.';
145 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
149 -- First make sure the circ matrix is loaded and the circulations
150 -- have been staged to the extent possible (but at the very least
151 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
152 -- circ modifiers must also be in place.
154 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
162 this_duration_rule INT;
164 this_max_fine_rule INT;
165 rcd config.rule_circ_duration%ROWTYPE;
166 rrf config.rule_recurring_fine%ROWTYPE;
167 rmf config.rule_max_fine%ROWTYPE;
174 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
176 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
178 -- Fetch the correct rules for this circulation
185 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
188 FROM ' || tablename || ' WHERE id = ' || circ || ';')
189 INTO circ_lib, target_copy, usr, is_renewal ;
191 INTO this_duration_rule,
197 FROM action.find_circ_matrix_matchpoint(
203 SELECT INTO rcd * FROM config.rule_circ_duration
204 WHERE id = this_duration_rule;
205 SELECT INTO rrf * FROM config.rule_recurring_fine
206 WHERE id = this_fine_rule;
207 SELECT INTO rmf * FROM config.rule_max_fine
208 WHERE id = this_max_fine_rule;
210 -- Apply the rules to this circulation
211 EXECUTE ('UPDATE ' || tablename || ' c
213 duration_rule = rcd.name,
214 recuring_fine_rule = rrf.name,
215 max_fine_rule = rmf.name,
216 duration = rcd.normal,
217 recuring_fine = rrf.normal,
220 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
223 renewal_remaining = rcd.max_renewals
225 config.rule_circ_duration rcd,
226 config.rule_recuring_fine rrf,
227 config.rule_max_fine rmf,
230 rcd.id = ' || this_duration_rule || ' AND
231 rrf.id = ' || this_fine_rule || ' AND
232 rmf.id = ' || this_max_fine_rule || ' AND
233 ac.id = c.target_copy AND
234 c.id = ' || circ || ';');
236 -- Keep track of where we are in the process
238 IF (n % 100 = 0) THEN
239 RAISE INFO '%', n || ' of ' || n_circs
240 || ' (' || (100*n/n_circs) || '%) circs updated.';
250 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
254 -- First make sure the circ matrix is loaded and the circulations
255 -- have been staged to the extent possible (but at the very least
256 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
257 -- circ modifiers must also be in place.
259 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
267 this_duration_rule INT;
269 this_max_fine_rule INT;
270 rcd config.rule_circ_duration%ROWTYPE;
271 rrf config.rule_recurring_fine%ROWTYPE;
272 rmf config.rule_max_fine%ROWTYPE;
279 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
281 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
283 -- Fetch the correct rules for this circulation
290 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
293 FROM ' || tablename || ' WHERE id = ' || circ || ';')
294 INTO circ_lib, target_copy, usr, is_renewal ;
296 INTO this_duration_rule,
299 (matchpoint).duration_rule,
300 (matchpoint).recurring_fine_rule,
301 (matchpoint).max_fine_rule
302 FROM action.find_circ_matrix_matchpoint(
308 SELECT INTO rcd * FROM config.rule_circ_duration
309 WHERE id = this_duration_rule;
310 SELECT INTO rrf * FROM config.rule_recurring_fine
311 WHERE id = this_fine_rule;
312 SELECT INTO rmf * FROM config.rule_max_fine
313 WHERE id = this_max_fine_rule;
315 -- Apply the rules to this circulation
316 EXECUTE ('UPDATE ' || tablename || ' c
318 duration_rule = rcd.name,
319 recurring_fine_rule = rrf.name,
320 max_fine_rule = rmf.name,
321 duration = rcd.normal,
322 recurring_fine = rrf.normal,
325 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
328 renewal_remaining = rcd.max_renewals,
329 grace_period = rrf.grace_period
331 config.rule_circ_duration rcd,
332 config.rule_recurring_fine rrf,
333 config.rule_max_fine rmf,
336 rcd.id = ' || this_duration_rule || ' AND
337 rrf.id = ' || this_fine_rule || ' AND
338 rmf.id = ' || this_max_fine_rule || ' AND
339 ac.id = c.target_copy AND
340 c.id = ' || circ || ';');
342 -- Keep track of where we are in the process
344 IF (n % 100 = 0) THEN
345 RAISE INFO '%', n || ' of ' || n_circs
346 || ' (' || (100*n/n_circs) || '%) circs updated.';
356 -- TODO: make another version of the procedure below that can work with specified copy staging tables
357 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
358 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
361 charge_lost_on_zero BOOLEAN;
364 default_price NUMERIC;
365 working_price NUMERIC;
369 SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
370 FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
372 SELECT INTO charge_lost_on_zero value
373 FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
375 SELECT INTO min_price value
376 FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
378 SELECT INTO max_price value
379 FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
381 SELECT INTO default_price value
382 FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
384 SELECT INTO working_price price FROM asset.copy WHERE id = item;
386 IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
387 working_price := default_price;
390 IF (max_price IS NOT NULL AND working_price > max_price) THEN
391 working_price := max_price;
394 IF (min_price IS NOT NULL AND working_price < min_price) THEN
395 IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
396 working_price := min_price;
400 RETURN working_price;
406 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
410 -- First make sure the circ matrix is loaded and the circulations
411 -- have been staged to the extent possible (but at the very least
412 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
413 -- circ modifiers must also be in place.
415 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
423 this_duration_rule INT;
425 this_max_fine_rule INT;
426 rcd config.rule_circ_duration%ROWTYPE;
427 rrf config.rule_recurring_fine%ROWTYPE;
428 rmf config.rule_max_fine%ROWTYPE;
434 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
436 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
438 -- Fetch the correct rules for this circulation
445 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
448 FROM ' || tablename || ' WHERE id = ' || circ || ';')
449 INTO circ_lib, target_copy, usr, is_renewal ;
451 INTO this_duration_rule,
454 (matchpoint).duration_rule,
455 (matchpoint).recurring_fine_rule,
456 (matchpoint).max_fine_rule
457 FROM action.find_circ_matrix_matchpoint(
463 SELECT INTO rcd * FROM config.rule_circ_duration
464 WHERE id = this_duration_rule;
465 SELECT INTO rrf * FROM config.rule_recurring_fine
466 WHERE id = this_fine_rule;
467 SELECT INTO rmf * FROM config.rule_max_fine
468 WHERE id = this_max_fine_rule;
470 -- Apply the rules to this circulation
471 EXECUTE ('UPDATE ' || tablename || ' c
473 duration_rule = rcd.name,
474 recurring_fine_rule = rrf.name,
475 max_fine_rule = rmf.name,
476 duration = rcd.normal,
477 recurring_fine = rrf.normal,
480 WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
483 renewal_remaining = rcd.max_renewals,
484 grace_period = rrf.grace_period
486 config.rule_circ_duration rcd,
487 config.rule_recurring_fine rrf,
488 config.rule_max_fine rmf,
491 rcd.id = ' || this_duration_rule || ' AND
492 rrf.id = ' || this_fine_rule || ' AND
493 rmf.id = ' || this_max_fine_rule || ' AND
494 ac.id = c.target_copy AND
495 c.id = ' || circ || ';');
497 -- Keep track of where we are in the process
499 IF (n % 100 = 0) THEN
500 RAISE INFO '%', n || ' of ' || n_circs
501 || ' (' || (100*n/n_circs) || '%) circs updated.';
514 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
516 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
517 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
519 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
520 -- TODO: Add a similar tool for actor stat cats, which behave differently.
523 c TEXT := schemaname || '.asset_copy_legacy';
524 sc TEXT := schemaname || '.asset_stat_cat';
525 sce TEXT := schemaname || '.asset_stat_cat_entry';
526 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
532 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
534 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
536 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
537 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
538 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
547 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
549 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
550 -- This will assign standing penalties as needed.
558 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
560 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
562 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
575 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
578 INSERT INTO metabib.metarecord (fingerprint, master_record)
579 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
580 FROM biblio.record_entry b
582 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)
583 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
584 ORDER BY b.fingerprint, b.quality DESC;
585 INSERT INTO metabib.metarecord_source_map (metarecord, source)
587 FROM biblio.record_entry r
588 JOIN metabib.metarecord m USING (fingerprint)
595 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
598 INSERT INTO metabib.metarecord (fingerprint, master_record)
599 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
600 FROM biblio.record_entry b
602 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)
603 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
604 ORDER BY b.fingerprint, b.quality DESC;
605 INSERT INTO metabib.metarecord_source_map (metarecord, source)
607 FROM biblio.record_entry r
608 JOIN metabib.metarecord m USING (fingerprint)
610 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);
616 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
618 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
619 -- Then SELECT migration_tools.create_cards('m_foo');
622 u TEXT := schemaname || '.actor_usr_legacy';
623 c TEXT := schemaname || '.actor_card';
627 EXECUTE ('DELETE FROM ' || c || ';');
628 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
629 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
638 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
640 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
642 my ($marcxml, $shortname) = @_;
650 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
652 foreach my $field ( $marc->field('856') ) {
653 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
654 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
655 $field->add_subfields( '9' => $shortname );
656 $field->update( ind2 => '0');
660 $xml = $marc->as_xml_record;
661 $xml =~ s/^<\?.+?\?>$//mo;
663 $xml =~ s/>\s+</></sgo;
668 $$ LANGUAGE PLPERLU STABLE;
670 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
672 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
674 my ($marcxml, $shortname) = @_;
682 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
684 foreach my $field ( $marc->field('856') ) {
685 if ( ! $field->as_string('9') ) {
686 $field->add_subfields( '9' => $shortname );
690 $xml = $marc->as_xml_record;
691 $xml =~ s/^<\?.+?\?>$//mo;
693 $xml =~ s/>\s+</></sgo;
698 $$ LANGUAGE PLPERLU STABLE;
701 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
713 -- Bail out if asked to change the label to ##URI##
714 IF new_label = '##URI##' THEN
718 -- Gather information
719 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
720 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
721 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
723 -- Bail out if the label already is ##URI##
724 IF old_label = '##URI##' THEN
728 -- Bail out if the call number label is already correct
729 IF new_volume = old_volume THEN
733 -- Check whether we already have a destination volume available
734 SELECT id INTO new_volume FROM asset.call_number
737 owning_lib = owner AND
738 label = new_label AND
741 -- Create destination volume if needed
743 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
744 VALUES (1, 1, bib, owner, new_label, cn_class);
745 SELECT id INTO new_volume FROM asset.call_number
748 owning_lib = owner AND
749 label = new_label AND
753 -- Move copy to destination
754 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
756 -- Delete source volume if it is now empty
757 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
759 DELETE FROM asset.call_number WHERE id = old_volume;
766 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
771 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
775 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
776 $zipdata{$zip} = [$city, $state, $county];
779 if (defined $zipdata{$input}) {
780 my ($city, $state, $county) = @{$zipdata{$input}};
781 return [$city, $state, $county];
782 } elsif (defined $zipdata{substr $input, 0, 5}) {
783 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
784 return [$city, $state, $county];
786 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
789 $$ LANGUAGE PLPERLU STABLE;
791 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
798 errors_found BOOLEAN;
800 parent_shortname TEXT;
806 type_parent_depth INT;
811 errors_found := FALSE;
813 -- Checking actor.org_unit_type
815 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
817 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
818 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
820 IF type_parent IS NOT NULL THEN
822 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
824 IF type_depth - type_parent_depth <> 1 THEN
825 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
826 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
827 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
828 ou_type_name, type_depth, parent_type, type_parent_depth;
829 errors_found := TRUE;
837 -- Checking actor.org_unit
839 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
841 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
842 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;
843 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;
844 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
845 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
846 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;
847 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;
849 IF ou_parent IS NOT NULL THEN
851 IF (org_unit_depth - parent_depth <> 1) OR (
852 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
854 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
855 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
856 errors_found := TRUE;
863 IF NOT errors_found THEN
864 RAISE INFO 'No errors found.';
874 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
878 DELETE FROM asset.opac_visible_copies;
880 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
882 cp.id, cp.circ_lib, cn.record
885 JOIN asset.call_number cn ON (cn.id = cp.call_number)
886 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
887 JOIN asset.copy_location cl ON (cp.location = cl.id)
888 JOIN config.copy_status cs ON (cp.status = cs.id)
889 JOIN biblio.record_entry b ON (cn.record = b.id)
898 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
905 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
911 old_owning_lib INTEGER;
917 -- Gather information
918 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
919 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
920 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
922 -- Bail out if the new_owning_lib is not the ID of an org_unit
923 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
925 '% is not a valid actor.org_unit ID; no change made.',
930 -- Bail out discreetly if the owning_lib is already correct
931 IF new_owning_lib = old_owning_lib THEN
935 -- Check whether we already have a destination volume available
936 SELECT id INTO new_volume FROM asset.call_number
939 owning_lib = new_owning_lib AND
940 label = old_label AND
943 -- Create destination volume if needed
945 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
946 VALUES (1, 1, bib, new_owning_lib, old_label);
947 SELECT id INTO new_volume FROM asset.call_number
950 owning_lib = new_owning_lib AND
951 label = old_label AND
955 -- Move copy to destination
956 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
958 -- Delete source volume if it is now empty
959 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
961 DELETE FROM asset.call_number WHERE id = old_volume;
969 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
971 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
974 new_owning_lib INTEGER;
978 -- Parse the new_owner as an org unit ID or shortname
979 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
980 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
981 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
982 ELSIF new_owner ~ E'^[0-9]+$' THEN
983 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
986 E'You don\'t need to put the actor.org_unit ID in quotes; '
987 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
988 new_owning_lib := new_owner::INTEGER;
989 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
993 '% is not a valid actor.org_unit shortname or ID; no change made.',
1000 $$ LANGUAGE plpgsql;
1002 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1004 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1005 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1006 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1007 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1008 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1009 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1010 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1011 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1012 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1013 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1014 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1015 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1016 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1017 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1018 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1019 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1020 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1021 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1022 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1023 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1024 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1025 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1026 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1027 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1028 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1029 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1031 $FUNC$ LANGUAGE PLPGSQL;
1033 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1035 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1036 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1037 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1038 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1039 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1040 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
1041 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
1043 -- import any new circ rules
1044 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1045 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1046 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1047 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1049 -- and permission groups
1050 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1053 $FUNC$ LANGUAGE PLPGSQL;
1056 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$
1065 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1066 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1067 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
1068 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1069 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1070 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1071 FOR name IN EXECUTE loopq LOOP
1072 EXECUTE existsq INTO ct USING name;
1074 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1075 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1076 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1077 EXECUTE copyst USING name;
1081 $FUNC$ LANGUAGE PLPGSQL;
1083 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
1090 loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
1091 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;
1092 FOR id IN EXECUTE loopq USING delimiter LOOP
1093 RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
1094 splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
1095 ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
1096 EXECUTE splitst USING id, delimiter;
1099 $FUNC$ LANGUAGE PLPGSQL;
1101 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
1102 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
1104 target_event_def ALIAS FOR $1;
1107 DROP TABLE IF EXISTS new_atevdefs;
1108 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1109 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1110 INSERT INTO action_trigger.event_definition (
1131 ,name || ' (clone of '||target_event_def||')'
1147 action_trigger.event_definition
1149 id = target_event_def
1151 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1152 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1153 INSERT INTO action_trigger.environment (
1159 currval('action_trigger.event_definition_id_seq')
1164 action_trigger.environment
1166 event_def = target_event_def
1168 INSERT INTO action_trigger.event_params (
1173 currval('action_trigger.event_definition_id_seq')
1177 action_trigger.event_params
1179 event_def = target_event_def
1182 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);
1184 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1186 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
1187 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
1189 target_event_def ALIAS FOR $1;
1191 new_interval ALIAS FOR $3;
1193 DROP TABLE IF EXISTS new_atevdefs;
1194 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1195 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1196 INSERT INTO action_trigger.event_definition (
1217 ,name || ' (clone of '||target_event_def||')'
1233 action_trigger.event_definition
1235 id = target_event_def
1237 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1238 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1239 INSERT INTO action_trigger.environment (
1245 currval('action_trigger.event_definition_id_seq')
1250 action_trigger.environment
1252 event_def = target_event_def
1254 INSERT INTO action_trigger.event_params (
1259 currval('action_trigger.event_definition_id_seq')
1263 action_trigger.event_params
1265 event_def = target_event_def
1268 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);
1270 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1272 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
1273 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
1276 target_event_defs ALIAS FOR $2;
1278 DROP TABLE IF EXISTS new_atevdefs;
1279 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1280 FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
1281 INSERT INTO action_trigger.event_definition (
1302 ,name || ' (clone of '||target_event_defs[i]||')'
1318 action_trigger.event_definition
1320 id = target_event_defs[i]
1322 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1323 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1324 INSERT INTO action_trigger.environment (
1330 currval('action_trigger.event_definition_id_seq')
1335 action_trigger.environment
1337 event_def = target_event_defs[i]
1339 INSERT INTO action_trigger.event_params (
1344 currval('action_trigger.event_definition_id_seq')
1348 action_trigger.event_params
1350 event_def = target_event_defs[i]
1353 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1355 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1357 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
1359 action_trigger.event
1363 ,complete_time = NULL
1364 ,update_process = NULL
1366 ,template_output = NULL
1367 ,error_output = NULL
1368 ,async_output = NULL
1373 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
1374 SELECT action.find_hold_matrix_matchpoint(
1375 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1376 (SELECT request_lib FROM action.hold_request WHERE id = $1),
1377 (SELECT current_copy FROM action.hold_request WHERE id = $1),
1378 (SELECT usr FROM action.hold_request WHERE id = $1),
1379 (SELECT requestor FROM action.hold_request WHERE id = $1)
1383 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
1384 SELECT action.hold_request_permit_test(
1385 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1386 (SELECT request_lib FROM action.hold_request WHERE id = $1),
1387 (SELECT current_copy FROM action.hold_request WHERE id = $1),
1388 (SELECT usr FROM action.hold_request WHERE id = $1),
1389 (SELECT requestor FROM action.hold_request WHERE id = $1)
1393 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
1394 SELECT action.find_circ_matrix_matchpoint(
1395 (SELECT circ_lib FROM action.circulation WHERE id = $1),
1396 (SELECT target_copy FROM action.circulation WHERE id = $1),
1397 (SELECT usr FROM action.circulation WHERE id = $1),
1399 NULLIF(phone_renewal,false),
1400 NULLIF(desk_renewal,false),
1401 NULLIF(opac_renewal,false),
1403 ) FROM action.circulation WHERE id = $1
1408 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
1413 RAISE EXCEPTION 'assertion';
1416 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1418 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
1424 RAISE EXCEPTION '%', msg;
1427 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1429 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
1432 fail_msg ALIAS FOR $2;
1433 success_msg ALIAS FOR $3;
1436 RAISE EXCEPTION '%', fail_msg;
1440 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1442 -- push bib sequence and return starting value for reserved range
1443 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
1445 bib_count ALIAS FOR $1;
1448 PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
1450 SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
1455 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1457 -- set a new salted password
1459 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
1461 usr_id ALIAS FOR $1;
1462 plain_passwd ALIAS FOR $2;
1467 SELECT actor.create_salt('main') INTO plain_salt;
1469 SELECT MD5(plain_passwd) INTO md5_passwd;
1471 PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
1476 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1479 -- convenience functions for handling copy_location maps
1480 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1481 SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
1484 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
1486 table_schema ALIAS FOR $1;
1487 table_name ALIAS FOR $2;
1488 org_shortname ALIAS FOR $3;
1489 org_range ALIAS FOR $4;
1490 make_assertion ALIAS FOR $5;
1493 -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
1494 -- though we'll still use the passed org for the full path traversal when needed
1495 x_org_found BOOLEAN;
1501 EXECUTE 'SELECT EXISTS (
1503 FROM information_schema.columns
1504 WHERE table_schema = $1
1506 and column_name = ''desired_shelf''
1507 )' INTO proceed USING table_schema, table_name;
1509 RAISE EXCEPTION 'Missing column desired_shelf';
1512 EXECUTE 'SELECT EXISTS (
1514 FROM information_schema.columns
1515 WHERE table_schema = $1
1517 and column_name = ''x_org''
1518 )' INTO x_org_found USING table_schema, table_name;
1520 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1522 RAISE EXCEPTION 'Cannot find org by shortname';
1525 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1527 EXECUTE 'ALTER TABLE '
1528 || quote_ident(table_name)
1529 || ' DROP COLUMN IF EXISTS x_shelf';
1530 EXECUTE 'ALTER TABLE '
1531 || quote_ident(table_name)
1532 || ' ADD COLUMN x_shelf INTEGER';
1535 RAISE INFO 'Found x_org column';
1536 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1537 || ' SET x_shelf = b.id FROM asset_copy_location b'
1538 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1539 || ' AND b.owning_lib = x_org'
1540 || ' AND NOT b.deleted';
1541 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1542 || ' SET x_shelf = b.id FROM asset.copy_location b'
1543 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1544 || ' AND b.owning_lib = x_org'
1545 || ' AND x_shelf IS NULL'
1546 || ' AND NOT b.deleted';
1548 RAISE INFO 'Did not find x_org column';
1549 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1550 || ' SET x_shelf = b.id FROM asset_copy_location b'
1551 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1552 || ' AND b.owning_lib = $1'
1553 || ' AND NOT b.deleted'
1555 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1556 || ' SET x_shelf = b.id FROM asset_copy_location b'
1557 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1558 || ' AND b.owning_lib = $1'
1559 || ' AND x_shelf IS NULL'
1560 || ' AND NOT b.deleted'
1564 FOREACH o IN ARRAY org_list LOOP
1565 RAISE INFO 'Considering org %', o;
1566 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1567 || ' SET x_shelf = b.id FROM asset.copy_location b'
1568 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1569 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
1570 || ' AND NOT b.deleted'
1572 GET DIAGNOSTICS row_count = ROW_COUNT;
1573 RAISE INFO 'Updated % rows', row_count;
1576 IF make_assertion THEN
1577 EXECUTE 'SELECT migration_tools.assert(
1578 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
1579 ''Cannot find a desired location'',
1580 ''Found all desired locations''
1585 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1587 -- convenience functions for handling circmod maps
1589 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
1591 table_schema ALIAS FOR $1;
1592 table_name ALIAS FOR $2;
1595 EXECUTE 'SELECT EXISTS (
1597 FROM information_schema.columns
1598 WHERE table_schema = $1
1600 and column_name = ''desired_circmod''
1601 )' INTO proceed USING table_schema, table_name;
1603 RAISE EXCEPTION 'Missing column desired_circmod';
1606 EXECUTE 'ALTER TABLE '
1607 || quote_ident(table_name)
1608 || ' DROP COLUMN IF EXISTS x_circmod';
1609 EXECUTE 'ALTER TABLE '
1610 || quote_ident(table_name)
1611 || ' ADD COLUMN x_circmod TEXT';
1613 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1614 || ' SET x_circmod = code FROM config.circ_modifier b'
1615 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
1617 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1618 || ' SET x_circmod = code FROM config.circ_modifier b'
1619 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
1620 || ' AND x_circmod IS NULL';
1622 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1623 || ' SET x_circmod = code FROM config.circ_modifier b'
1624 || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
1625 || ' AND x_circmod IS NULL';
1627 EXECUTE 'SELECT migration_tools.assert(
1628 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
1629 ''Cannot find a desired circulation modifier'',
1630 ''Found all desired circulation modifiers''
1634 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1636 -- convenience functions for handling item status maps
1638 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
1640 table_schema ALIAS FOR $1;
1641 table_name ALIAS FOR $2;
1644 EXECUTE 'SELECT EXISTS (
1646 FROM information_schema.columns
1647 WHERE table_schema = $1
1649 and column_name = ''desired_status''
1650 )' INTO proceed USING table_schema, table_name;
1652 RAISE EXCEPTION 'Missing column desired_status';
1655 EXECUTE 'ALTER TABLE '
1656 || quote_ident(table_name)
1657 || ' DROP COLUMN IF EXISTS x_status';
1658 EXECUTE 'ALTER TABLE '
1659 || quote_ident(table_name)
1660 || ' ADD COLUMN x_status INTEGER';
1662 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1663 || ' SET x_status = id FROM config.copy_status b'
1664 || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
1666 EXECUTE 'SELECT migration_tools.assert(
1667 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
1668 ''Cannot find a desired copy status'',
1669 ''Found all desired copy statuses''
1673 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1675 -- convenience functions for handling org maps
1677 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
1679 table_schema ALIAS FOR $1;
1680 table_name ALIAS FOR $2;
1683 EXECUTE 'SELECT EXISTS (
1685 FROM information_schema.columns
1686 WHERE table_schema = $1
1688 and column_name = ''desired_org''
1689 )' INTO proceed USING table_schema, table_name;
1691 RAISE EXCEPTION 'Missing column desired_org';
1694 EXECUTE 'ALTER TABLE '
1695 || quote_ident(table_name)
1696 || ' DROP COLUMN IF EXISTS x_org';
1697 EXECUTE 'ALTER TABLE '
1698 || quote_ident(table_name)
1699 || ' ADD COLUMN x_org INTEGER';
1701 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1702 || ' SET x_org = b.id FROM actor.org_unit b'
1703 || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
1705 EXECUTE 'SELECT migration_tools.assert(
1706 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
1707 ''Cannot find a desired org unit'',
1708 ''Found all desired org units''
1712 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1714 -- convenience function for handling desired_not_migrate
1716 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
1718 table_schema ALIAS FOR $1;
1719 table_name ALIAS FOR $2;
1722 EXECUTE 'SELECT EXISTS (
1724 FROM information_schema.columns
1725 WHERE table_schema = $1
1727 and column_name = ''desired_not_migrate''
1728 )' INTO proceed USING table_schema, table_name;
1730 RAISE EXCEPTION 'Missing column desired_not_migrate';
1733 EXECUTE 'ALTER TABLE '
1734 || quote_ident(table_name)
1735 || ' DROP COLUMN IF EXISTS x_migrate';
1736 EXECUTE 'ALTER TABLE '
1737 || quote_ident(table_name)
1738 || ' ADD COLUMN x_migrate BOOLEAN';
1740 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1741 || ' SET x_migrate = CASE'
1742 || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
1743 || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
1744 || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
1745 || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
1746 || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
1747 || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
1750 EXECUTE 'SELECT migration_tools.assert(
1751 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
1752 ''Not all desired_not_migrate values understood'',
1753 ''All desired_not_migrate values understood''
1757 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1759 -- convenience function for handling desired_not_migrate
1761 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
1763 table_schema ALIAS FOR $1;
1764 table_name ALIAS FOR $2;
1767 EXECUTE 'SELECT EXISTS (
1769 FROM information_schema.columns
1770 WHERE table_schema = $1
1772 and column_name = ''desired_barred_or_blocked''
1773 )' INTO proceed USING table_schema, table_name;
1775 RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
1778 EXECUTE 'ALTER TABLE '
1779 || quote_ident(table_name)
1780 || ' DROP COLUMN IF EXISTS x_barred';
1781 EXECUTE 'ALTER TABLE '
1782 || quote_ident(table_name)
1783 || ' ADD COLUMN x_barred BOOLEAN';
1785 EXECUTE 'ALTER TABLE '
1786 || quote_ident(table_name)
1787 || ' DROP COLUMN IF EXISTS x_blocked';
1788 EXECUTE 'ALTER TABLE '
1789 || quote_ident(table_name)
1790 || ' ADD COLUMN x_blocked BOOLEAN';
1792 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1793 || ' SET x_barred = CASE'
1794 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
1795 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
1796 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
1797 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
1800 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1801 || ' SET x_blocked = CASE'
1802 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
1803 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
1804 || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
1805 || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
1808 EXECUTE 'SELECT migration_tools.assert(
1809 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
1810 ''Not all desired_barred_or_blocked values understood'',
1811 ''All desired_barred_or_blocked values understood''
1815 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1817 -- convenience function for handling desired_profile
1819 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
1821 table_schema ALIAS FOR $1;
1822 table_name ALIAS FOR $2;
1825 EXECUTE 'SELECT EXISTS (
1827 FROM information_schema.columns
1828 WHERE table_schema = $1
1830 and column_name = ''desired_profile''
1831 )' INTO proceed USING table_schema, table_name;
1833 RAISE EXCEPTION 'Missing column desired_profile';
1836 EXECUTE 'ALTER TABLE '
1837 || quote_ident(table_name)
1838 || ' DROP COLUMN IF EXISTS x_profile';
1839 EXECUTE 'ALTER TABLE '
1840 || quote_ident(table_name)
1841 || ' ADD COLUMN x_profile INTEGER';
1843 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1844 || ' SET x_profile = b.id FROM permission.grp_tree b'
1845 || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
1847 EXECUTE 'SELECT migration_tools.assert(
1848 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
1849 ''Cannot find a desired profile'',
1850 ''Found all desired profiles''
1854 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1856 -- convenience function for handling desired actor stat cats
1858 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1860 table_schema ALIAS FOR $1;
1861 table_name ALIAS FOR $2;
1862 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
1863 org_shortname ALIAS FOR $4;
1871 SELECT 'desired_sc' || field_suffix INTO sc;
1872 SELECT 'desired_sce' || field_suffix INTO sce;
1874 EXECUTE 'SELECT EXISTS (
1876 FROM information_schema.columns
1877 WHERE table_schema = $1
1879 and column_name = $3
1880 )' INTO proceed USING table_schema, table_name, sc;
1882 RAISE EXCEPTION 'Missing column %', sc;
1884 EXECUTE 'SELECT EXISTS (
1886 FROM information_schema.columns
1887 WHERE table_schema = $1
1889 and column_name = $3
1890 )' INTO proceed USING table_schema, table_name, sce;
1892 RAISE EXCEPTION 'Missing column %', sce;
1895 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1897 RAISE EXCEPTION 'Cannot find org by shortname';
1899 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1901 -- caller responsible for their own truncates though we try to prevent duplicates
1902 EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
1907 ' || quote_ident(table_name) || '
1909 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
1913 WHERE owner = ANY ($2)
1914 AND name = BTRIM('||sc||')
1919 WHERE owner = ANY ($2)
1920 AND name = BTRIM('||sc||')
1923 USING org, org_list;
1925 EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
1930 WHERE owner = ANY ($2)
1931 AND BTRIM('||sc||') = BTRIM(name))
1934 WHERE owner = ANY ($2)
1935 AND BTRIM('||sc||') = BTRIM(name))
1940 ' || quote_ident(table_name) || '
1942 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
1943 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
1946 FROM actor.stat_cat_entry
1950 WHERE owner = ANY ($2)
1951 AND BTRIM('||sc||') = BTRIM(name)
1952 ) AND value = BTRIM('||sce||')
1953 AND owner = ANY ($2)
1957 FROM actor_stat_cat_entry
1961 WHERE owner = ANY ($2)
1962 AND BTRIM('||sc||') = BTRIM(name)
1963 ) AND value = BTRIM('||sce||')
1964 AND owner = ANY ($2)
1967 USING org, org_list;
1969 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1971 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1973 table_schema ALIAS FOR $1;
1974 table_name ALIAS FOR $2;
1975 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
1976 org_shortname ALIAS FOR $4;
1984 SELECT 'desired_sc' || field_suffix INTO sc;
1985 SELECT 'desired_sce' || field_suffix INTO sce;
1986 EXECUTE 'SELECT EXISTS (
1988 FROM information_schema.columns
1989 WHERE table_schema = $1
1991 and column_name = $3
1992 )' INTO proceed USING table_schema, table_name, sc;
1994 RAISE EXCEPTION 'Missing column %', sc;
1996 EXECUTE 'SELECT EXISTS (
1998 FROM information_schema.columns
1999 WHERE table_schema = $1
2001 and column_name = $3
2002 )' INTO proceed USING table_schema, table_name, sce;
2004 RAISE EXCEPTION 'Missing column %', sce;
2007 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2009 RAISE EXCEPTION 'Cannot find org by shortname';
2012 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2014 EXECUTE 'ALTER TABLE '
2015 || quote_ident(table_name)
2016 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
2017 EXECUTE 'ALTER TABLE '
2018 || quote_ident(table_name)
2019 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
2020 EXECUTE 'ALTER TABLE '
2021 || quote_ident(table_name)
2022 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
2023 EXECUTE 'ALTER TABLE '
2024 || quote_ident(table_name)
2025 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
2028 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2030 x_sc' || field_suffix || ' = id
2032 (SELECT id, name, owner FROM actor_stat_cat
2033 UNION SELECT id, name, owner FROM actor.stat_cat) u
2035 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2036 AND u.owner = ANY ($1);'
2039 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2041 x_sce' || field_suffix || ' = id
2043 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
2044 UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
2046 u.stat_cat = x_sc' || field_suffix || '
2047 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2048 AND u.owner = ANY ($1);'
2051 EXECUTE 'SELECT migration_tools.assert(
2052 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
2053 ''Cannot find a desired stat cat'',
2054 ''Found all desired stat cats''
2057 EXECUTE 'SELECT migration_tools.assert(
2058 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
2059 ''Cannot find a desired stat cat entry'',
2060 ''Found all desired stat cat entries''
2064 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2066 -- convenience functions for adding shelving locations
2067 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
2068 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2074 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2077 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2078 SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2079 IF return_id IS NOT NULL THEN
2087 $$ LANGUAGE plpgsql;
2089 -- may remove later but testing using this with new migration scripts and not loading acls until go live
2091 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
2092 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2098 SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2101 SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2103 SELECT INTO return_id id FROM
2104 (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
2105 WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2106 IF return_id IS NOT NULL THEN
2114 $$ LANGUAGE plpgsql;
2116 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2118 table_schema ALIAS FOR $1;
2119 table_name ALIAS FOR $2;
2120 foreign_column_name ALIAS FOR $3;
2121 main_column_name ALIAS FOR $4;
2122 btrim_desired ALIAS FOR $5;
2125 EXECUTE 'SELECT EXISTS (
2127 FROM information_schema.columns
2128 WHERE table_schema = $1
2130 and column_name = $3
2131 )' INTO proceed USING table_schema, table_name, foreign_column_name;
2133 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2136 EXECUTE 'SELECT EXISTS (
2138 FROM information_schema.columns
2139 WHERE table_schema = $1
2140 AND table_name = ''asset_copy_legacy''
2141 and column_name = $2
2142 )' INTO proceed USING table_schema, main_column_name;
2144 RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
2147 EXECUTE 'ALTER TABLE '
2148 || quote_ident(table_name)
2149 || ' DROP COLUMN IF EXISTS x_item';
2150 EXECUTE 'ALTER TABLE '
2151 || quote_ident(table_name)
2152 || ' ADD COLUMN x_item BIGINT';
2154 IF btrim_desired THEN
2155 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2156 || ' SET x_item = b.id FROM asset_copy_legacy b'
2157 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2158 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2160 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2161 || ' SET x_item = b.id FROM asset_copy_legacy b'
2162 || ' WHERE a.' || quote_ident(foreign_column_name)
2163 || ' = b.' || quote_ident(main_column_name);
2166 --EXECUTE 'SELECT migration_tools.assert(
2167 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
2168 -- ''Cannot link every barcode'',
2169 -- ''Every barcode linked''
2173 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2175 -- convenience function for linking to the user staging table
2177 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2179 table_schema ALIAS FOR $1;
2180 table_name ALIAS FOR $2;
2181 foreign_column_name ALIAS FOR $3;
2182 main_column_name ALIAS FOR $4;
2183 btrim_desired ALIAS FOR $5;
2186 EXECUTE 'SELECT EXISTS (
2188 FROM information_schema.columns
2189 WHERE table_schema = $1
2191 and column_name = $3
2192 )' INTO proceed USING table_schema, table_name, foreign_column_name;
2194 RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2197 EXECUTE 'SELECT EXISTS (
2199 FROM information_schema.columns
2200 WHERE table_schema = $1
2201 AND table_name = ''actor_usr_legacy''
2202 and column_name = $2
2203 )' INTO proceed USING table_schema, main_column_name;
2205 RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
2208 EXECUTE 'ALTER TABLE '
2209 || quote_ident(table_name)
2210 || ' DROP COLUMN IF EXISTS x_user';
2211 EXECUTE 'ALTER TABLE '
2212 || quote_ident(table_name)
2213 || ' ADD COLUMN x_user INTEGER';
2215 IF btrim_desired THEN
2216 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2217 || ' SET x_user = b.id FROM actor_usr_legacy b'
2218 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2219 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2221 EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2222 || ' SET x_user = b.id FROM actor_usr_legacy b'
2223 || ' WHERE a.' || quote_ident(foreign_column_name)
2224 || ' = b.' || quote_ident(main_column_name);
2227 --EXECUTE 'SELECT migration_tools.assert(
2228 -- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
2229 -- ''Cannot link every barcode'',
2230 -- ''Every barcode linked''
2234 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2236 -- convenience function for linking two tables
2237 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
2238 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2240 table_schema ALIAS FOR $1;
2241 table_a ALIAS FOR $2;
2242 column_a ALIAS FOR $3;
2243 table_b ALIAS FOR $4;
2244 column_b ALIAS FOR $5;
2245 column_x ALIAS FOR $6;
2246 btrim_desired ALIAS FOR $7;
2249 EXECUTE 'SELECT EXISTS (
2251 FROM information_schema.columns
2252 WHERE table_schema = $1
2254 and column_name = $3
2255 )' INTO proceed USING table_schema, table_a, column_a;
2257 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2260 EXECUTE 'SELECT EXISTS (
2262 FROM information_schema.columns
2263 WHERE table_schema = $1
2265 and column_name = $3
2266 )' INTO proceed USING table_schema, table_b, column_b;
2268 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2271 EXECUTE 'ALTER TABLE '
2272 || quote_ident(table_b)
2273 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2274 EXECUTE 'ALTER TABLE '
2275 || quote_ident(table_b)
2276 || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
2278 IF btrim_desired THEN
2279 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2280 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2281 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2282 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2284 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2285 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2286 || ' WHERE a.' || quote_ident(column_a)
2287 || ' = b.' || quote_ident(column_b);
2291 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2293 -- convenience function for linking two tables, but copying column w into column x instead of "id"
2294 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
2295 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2297 table_schema ALIAS FOR $1;
2298 table_a ALIAS FOR $2;
2299 column_a ALIAS FOR $3;
2300 table_b ALIAS FOR $4;
2301 column_b ALIAS FOR $5;
2302 column_w ALIAS FOR $6;
2303 column_x ALIAS FOR $7;
2304 btrim_desired ALIAS FOR $8;
2307 EXECUTE 'SELECT EXISTS (
2309 FROM information_schema.columns
2310 WHERE table_schema = $1
2312 and column_name = $3
2313 )' INTO proceed USING table_schema, table_a, column_a;
2315 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2318 EXECUTE 'SELECT EXISTS (
2320 FROM information_schema.columns
2321 WHERE table_schema = $1
2323 and column_name = $3
2324 )' INTO proceed USING table_schema, table_b, column_b;
2326 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2329 EXECUTE 'ALTER TABLE '
2330 || quote_ident(table_b)
2331 || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2332 EXECUTE 'ALTER TABLE '
2333 || quote_ident(table_b)
2334 || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
2336 IF btrim_desired THEN
2337 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2338 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2339 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2340 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2342 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2343 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2344 || ' WHERE a.' || quote_ident(column_a)
2345 || ' = b.' || quote_ident(column_b);
2349 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2351 -- 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
2352 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
2353 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2355 table_schema ALIAS FOR $1;
2356 table_a ALIAS FOR $2;
2357 column_a ALIAS FOR $3;
2358 table_b ALIAS FOR $4;
2359 column_b ALIAS FOR $5;
2360 column_w ALIAS FOR $6;
2361 column_x ALIAS FOR $7;
2364 EXECUTE 'SELECT EXISTS (
2366 FROM information_schema.columns
2367 WHERE table_schema = $1
2369 and column_name = $3
2370 )' INTO proceed USING table_schema, table_a, column_a;
2372 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2375 EXECUTE 'SELECT EXISTS (
2377 FROM information_schema.columns
2378 WHERE table_schema = $1
2380 and column_name = $3
2381 )' INTO proceed USING table_schema, table_b, column_b;
2383 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2386 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2387 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2388 || ' WHERE a.' || quote_ident(column_a)
2389 || ' = b.' || quote_ident(column_b);
2392 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2394 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2396 table_schema ALIAS FOR $1;
2397 table_a ALIAS FOR $2;
2398 column_a ALIAS FOR $3;
2399 table_b ALIAS FOR $4;
2400 column_b ALIAS FOR $5;
2401 column_w ALIAS FOR $6;
2402 column_x ALIAS FOR $7;
2405 EXECUTE 'SELECT EXISTS (
2407 FROM information_schema.columns
2408 WHERE table_schema = $1
2410 and column_name = $3
2411 )' INTO proceed USING table_schema, table_a, column_a;
2413 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2416 EXECUTE 'SELECT EXISTS (
2418 FROM information_schema.columns
2419 WHERE table_schema = $1
2421 and column_name = $3
2422 )' INTO proceed USING table_schema, table_b, column_b;
2424 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2427 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2428 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2429 || ' WHERE a.' || quote_ident(column_a)
2430 || ' = b.' || quote_ident(column_b)
2431 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
2434 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2436 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2438 table_schema ALIAS FOR $1;
2439 table_a ALIAS FOR $2;
2440 column_a ALIAS FOR $3;
2441 table_b ALIAS FOR $4;
2442 column_b ALIAS FOR $5;
2443 column_w ALIAS FOR $6;
2444 column_x ALIAS FOR $7;
2447 EXECUTE 'SELECT EXISTS (
2449 FROM information_schema.columns
2450 WHERE table_schema = $1
2452 and column_name = $3
2453 )' INTO proceed USING table_schema, table_a, column_a;
2455 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2458 EXECUTE 'SELECT EXISTS (
2460 FROM information_schema.columns
2461 WHERE table_schema = $1
2463 and column_name = $3
2464 )' INTO proceed USING table_schema, table_b, column_b;
2466 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2469 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2470 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2471 || ' WHERE a.' || quote_ident(column_a)
2472 || ' = b.' || quote_ident(column_b)
2473 || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
2476 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2478 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2480 table_schema ALIAS FOR $1;
2481 table_a ALIAS FOR $2;
2482 column_a ALIAS FOR $3;
2483 table_b ALIAS FOR $4;
2484 column_b ALIAS FOR $5;
2485 column_w ALIAS FOR $6;
2486 column_x ALIAS FOR $7;
2489 EXECUTE 'SELECT EXISTS (
2491 FROM information_schema.columns
2492 WHERE table_schema = $1
2494 and column_name = $3
2495 )' INTO proceed USING table_schema, table_a, column_a;
2497 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2500 EXECUTE 'SELECT EXISTS (
2502 FROM information_schema.columns
2503 WHERE table_schema = $1
2505 and column_name = $3
2506 )' INTO proceed USING table_schema, table_b, column_b;
2508 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2511 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2512 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2513 || ' WHERE a.' || quote_ident(column_a)
2514 || ' = b.' || quote_ident(column_b)
2515 || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
2518 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2520 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2522 table_schema ALIAS FOR $1;
2523 table_a ALIAS FOR $2;
2524 column_a ALIAS FOR $3;
2525 table_b ALIAS FOR $4;
2526 column_b ALIAS FOR $5;
2527 column_w ALIAS FOR $6;
2528 column_x ALIAS FOR $7;
2531 EXECUTE 'SELECT EXISTS (
2533 FROM information_schema.columns
2534 WHERE table_schema = $1
2536 and column_name = $3
2537 )' INTO proceed USING table_schema, table_a, column_a;
2539 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2542 EXECUTE 'SELECT EXISTS (
2544 FROM information_schema.columns
2545 WHERE table_schema = $1
2547 and column_name = $3
2548 )' INTO proceed USING table_schema, table_b, column_b;
2550 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2553 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2554 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2555 || ' WHERE a.' || quote_ident(column_a)
2556 || ' = b.' || quote_ident(column_b)
2557 || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
2560 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2562 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2564 table_schema ALIAS FOR $1;
2565 table_a ALIAS FOR $2;
2566 column_a ALIAS FOR $3;
2567 table_b ALIAS FOR $4;
2568 column_b ALIAS FOR $5;
2569 column_w ALIAS FOR $6;
2570 column_x ALIAS FOR $7;
2573 EXECUTE 'SELECT EXISTS (
2575 FROM information_schema.columns
2576 WHERE table_schema = $1
2578 and column_name = $3
2579 )' INTO proceed USING table_schema, table_a, column_a;
2581 RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2584 EXECUTE 'SELECT EXISTS (
2586 FROM information_schema.columns
2587 WHERE table_schema = $1
2589 and column_name = $3
2590 )' INTO proceed USING table_schema, table_b, column_b;
2592 RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2595 EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2596 || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
2597 || ' WHERE a.' || quote_ident(column_a)
2598 || ' = b.' || quote_ident(column_b)
2599 || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
2602 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2604 -- convenience function for handling desired asset stat cats
2606 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2608 table_schema ALIAS FOR $1;
2609 table_name ALIAS FOR $2;
2610 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2611 org_shortname ALIAS FOR $4;
2619 SELECT 'desired_sc' || field_suffix INTO sc;
2620 SELECT 'desired_sce' || field_suffix INTO sce;
2622 EXECUTE 'SELECT EXISTS (
2624 FROM information_schema.columns
2625 WHERE table_schema = $1
2627 and column_name = $3
2628 )' INTO proceed USING table_schema, table_name, sc;
2630 RAISE EXCEPTION 'Missing column %', sc;
2632 EXECUTE 'SELECT EXISTS (
2634 FROM information_schema.columns
2635 WHERE table_schema = $1
2637 and column_name = $3
2638 )' INTO proceed USING table_schema, table_name, sce;
2640 RAISE EXCEPTION 'Missing column %', sce;
2643 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2645 RAISE EXCEPTION 'Cannot find org by shortname';
2647 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2649 -- caller responsible for their own truncates though we try to prevent duplicates
2650 EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
2655 ' || quote_ident(table_name) || '
2657 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2661 WHERE owner = ANY ($2)
2662 AND name = BTRIM('||sc||')
2667 WHERE owner = ANY ($2)
2668 AND name = BTRIM('||sc||')
2671 USING org, org_list;
2673 EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
2678 WHERE owner = ANY ($2)
2679 AND BTRIM('||sc||') = BTRIM(name))
2682 WHERE owner = ANY ($2)
2683 AND BTRIM('||sc||') = BTRIM(name))
2688 ' || quote_ident(table_name) || '
2690 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2691 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
2694 FROM asset.stat_cat_entry
2698 WHERE owner = ANY ($2)
2699 AND BTRIM('||sc||') = BTRIM(name)
2700 ) AND value = BTRIM('||sce||')
2701 AND owner = ANY ($2)
2705 FROM asset_stat_cat_entry
2709 WHERE owner = ANY ($2)
2710 AND BTRIM('||sc||') = BTRIM(name)
2711 ) AND value = BTRIM('||sce||')
2712 AND owner = ANY ($2)
2715 USING org, org_list;
2717 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2719 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2721 table_schema ALIAS FOR $1;
2722 table_name ALIAS FOR $2;
2723 field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2724 org_shortname ALIAS FOR $4;
2732 SELECT 'desired_sc' || field_suffix INTO sc;
2733 SELECT 'desired_sce' || field_suffix INTO sce;
2734 EXECUTE 'SELECT EXISTS (
2736 FROM information_schema.columns
2737 WHERE table_schema = $1
2739 and column_name = $3
2740 )' INTO proceed USING table_schema, table_name, sc;
2742 RAISE EXCEPTION 'Missing column %', sc;
2744 EXECUTE 'SELECT EXISTS (
2746 FROM information_schema.columns
2747 WHERE table_schema = $1
2749 and column_name = $3
2750 )' INTO proceed USING table_schema, table_name, sce;
2752 RAISE EXCEPTION 'Missing column %', sce;
2755 SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2757 RAISE EXCEPTION 'Cannot find org by shortname';
2760 SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2762 EXECUTE 'ALTER TABLE '
2763 || quote_ident(table_name)
2764 || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
2765 EXECUTE 'ALTER TABLE '
2766 || quote_ident(table_name)
2767 || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
2768 EXECUTE 'ALTER TABLE '
2769 || quote_ident(table_name)
2770 || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
2771 EXECUTE 'ALTER TABLE '
2772 || quote_ident(table_name)
2773 || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
2776 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2778 x_sc' || field_suffix || ' = id
2780 (SELECT id, name, owner FROM asset_stat_cat
2781 UNION SELECT id, name, owner FROM asset.stat_cat) u
2783 BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2784 AND u.owner = ANY ($1);'
2787 EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2789 x_sce' || field_suffix || ' = id
2791 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
2792 UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
2794 u.stat_cat = x_sc' || field_suffix || '
2795 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2796 AND u.owner = ANY ($1);'
2799 EXECUTE 'SELECT migration_tools.assert(
2800 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
2801 ''Cannot find a desired stat cat'',
2802 ''Found all desired stat cats''
2805 EXECUTE 'SELECT migration_tools.assert(
2806 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
2807 ''Cannot find a desired stat cat entry'',
2808 ''Found all desired stat cat entries''
2812 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2814 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
2815 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2822 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2824 AND table_schema = s_name
2825 AND (data_type='text' OR data_type='character varying')
2826 AND column_name like 'l_%'
2828 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
2835 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
2836 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2843 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2845 AND table_schema = s_name
2846 AND (data_type='text' OR data_type='character varying')
2848 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
2855 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
2856 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2863 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2865 AND table_schema = s_name
2866 AND (data_type='text' OR data_type='character varying')
2867 AND column_name like 'l_%'
2869 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
2876 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
2877 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2884 FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2886 AND table_schema = s_name
2887 AND (data_type='text' OR data_type='character varying')
2889 EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
2897 -- convenience function for handling item barcode collisions in asset_copy_legacy
2899 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
2904 internal_collision_count NUMERIC := 0;
2905 incumbent_collision_count NUMERIC := 0;
2907 FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
2909 FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
2911 UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
2912 GET DIAGNOSTICS row_count = ROW_COUNT;
2913 internal_collision_count := internal_collision_count + row_count;
2916 RAISE INFO '% internal collisions', internal_collision_count;
2917 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
2919 FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
2921 UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
2922 GET DIAGNOSTICS row_count = ROW_COUNT;
2923 incumbent_collision_count := incumbent_collision_count + row_count;
2926 RAISE INFO '% incumbent collisions', incumbent_collision_count;
2928 $function$ LANGUAGE plpgsql;
2930 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
2931 -- this should be ran prior to populating actor_card
2933 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
2938 internal_collision_count NUMERIC := 0;
2939 incumbent_barcode_collision_count NUMERIC := 0;
2940 incumbent_usrname_collision_count NUMERIC := 0;
2942 FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
2944 FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2946 UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
2947 GET DIAGNOSTICS row_count = ROW_COUNT;
2948 internal_collision_count := internal_collision_count + row_count;
2951 RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
2954 SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
2956 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2958 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
2959 GET DIAGNOSTICS row_count = ROW_COUNT;
2960 incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
2963 RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
2966 SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
2968 FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2970 UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
2971 GET DIAGNOSTICS row_count = ROW_COUNT;
2972 incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
2975 RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
2977 $function$ LANGUAGE plpgsql;