sql/base/99-deprecated.sql

   1 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
   2   DECLARE
   3     phone TEXT := $1;
   4     areacode TEXT := $2;
   5     temp TEXT := '';
   6     output TEXT := '';
   7     n_digits INTEGER := 0;
   8   BEGIN
   9     temp := phone;
  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);
  16     ELSE
  17       output := temp;
  18     END IF;
  19     RETURN output;
  20   END;
  21
  22 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  23
  24 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
  25   my ($marcxml, $pos, $value) = @_;
  26
  27   use MARC::Record;
  28   use MARC::File::XML;
  29
  30   my $xml = $marcxml;
  31   eval {
  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;
  38     $xml =~ s/\n//sgo;
  39     $xml =~ s/>\s+</></sgo;
  40   };
  41   return $xml;
  42 $$ LANGUAGE PLPERLU STABLE;
  43
  44 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
  45   my ($marcxml, $pos, $value) = @_;
  46
  47   use MARC::Record;
  48   use MARC::File::XML;
  49
  50   my $xml = $marcxml;
  51   eval {
  52     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
  53     my $f008 = $marc->field('008');
  54
  55     if ($f008) {
  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;
  61        $xml =~ s/\n//sgo;
  62        $xml =~ s/>\s+</></sgo;
  63     }
  64   };
  65   return $xml;
  66 $$ LANGUAGE PLPERLU STABLE;
  67
  68
  69 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
  70   DECLARE
  71     profile ALIAS FOR $1;
  72   BEGIN
  73     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
  74   END;
  75 $$ LANGUAGE PLPGSQL STRICT STABLE;
  76
  77
  78 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
  79   BEGIN
  80     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
  81   END;
  82 $$ LANGUAGE PLPGSQL STRICT STABLE;
  83
  84
  85 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
  86
  87   my ($marcxml, $tags) = @_;
  88
  89   use MARC::Record;
  90   use MARC::File::XML;
  91
  92   my $xml = $marcxml;
  93
  94   eval {
  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');
  97
  98     my @incumbents = ();
  99
 100     foreach my $field ( $marc->fields() ) {
 101       push @incumbents, $field->as_formatted();
 102     }
 103
 104     foreach $field ( $to_insert->fields() ) {
 105       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
 106         $marc->insert_fields_ordered( ($field) );
 107       }
 108     }
 109
 110     $xml = $marc->as_xml_record;
 111     $xml =~ s/^<\?.+?\?>$//mo;
 112     $xml =~ s/\n//sgo;
 113     $xml =~ s/>\s+</></sgo;
 114   };
 115
 116   return $xml;
 117
 118 $$ LANGUAGE PLPERLU STABLE;
 119
 120 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
 121
 122 -- Usage:
 123 --
 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.
 128 --
 129 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 130 --
 131
 132 DECLARE
 133   circ_lib             INT;
 134   target_copy          INT;
 135   usr                  INT;
 136   is_renewal           BOOLEAN;
 137   this_duration_rule   INT;
 138   this_fine_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;
 143   circ                 INT;
 144   n                    INT := 0;
 145   n_circs              INT;
 146
 147 BEGIN
 148
 149   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 150
 151   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 152
 153     -- Fetch the correct rules for this circulation
 154     EXECUTE ('
 155       SELECT
 156         circ_lib,
 157         target_copy,
 158         usr,
 159         CASE
 160           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 161           ELSE FALSE
 162         END
 163       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 164       INTO circ_lib, target_copy, usr, is_renewal ;
 165     SELECT
 166       INTO this_duration_rule,
 167            this_fine_rule,
 168            this_max_fine_rule
 169       duration_rule,
 170       recurring_fine_rule,
 171       max_fine_rule
 172       FROM action.item_user_circ_test(
 173         circ_lib,
 174         target_copy,
 175         usr,
 176         is_renewal
 177         );
 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;
 184
 185     -- Apply the rules to this circulation
 186     EXECUTE ('UPDATE ' || tablename || ' c
 187     SET
 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,
 193       max_fine =
 194         CASE rmf.is_percent
 195           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 196           ELSE rmf.amount
 197         END,
 198       renewal_remaining = rcd.max_renewals
 199     FROM
 200       config.rule_circ_duration rcd,
 201       config.rule_recurring_fine rrf,
 202       config.rule_max_fine rmf,
 203                         asset.copy ac
 204     WHERE
 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 || ';');
 210
 211     -- Keep track of where we are in the process
 212     n := n + 1;
 213     IF (n % 100 = 0) THEN
 214       RAISE INFO '%', n || ' of ' || n_circs
 215         || ' (' || (100*n/n_circs) || '%) circs updated.';
 216     END IF;
 217
 218   END LOOP;
 219
 220   RETURN;
 221 END;
 222
 223 $$ LANGUAGE plpgsql;
 224
 225 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
 226
 227 -- Usage:
 228 --
 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.
 233 --
 234 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 235 --
 236
 237 DECLARE
 238   circ_lib             INT;
 239   target_copy          INT;
 240   usr                  INT;
 241   is_renewal           BOOLEAN;
 242   this_duration_rule   INT;
 243   this_fine_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;
 248   circ                 INT;
 249   n                    INT := 0;
 250   n_circs              INT;
 251
 252 BEGIN
 253
 254   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 255
 256   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 257
 258     -- Fetch the correct rules for this circulation
 259     EXECUTE ('
 260       SELECT
 261         circ_lib,
 262         target_copy,
 263         usr,
 264         CASE
 265           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 266           ELSE FALSE
 267         END
 268       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 269       INTO circ_lib, target_copy, usr, is_renewal ;
 270     SELECT
 271       INTO this_duration_rule,
 272            this_fine_rule,
 273            this_max_fine_rule
 274       duration_rule,
 275       recuring_fine_rule,
 276       max_fine_rule
 277       FROM action.find_circ_matrix_matchpoint(
 278         circ_lib,
 279         target_copy,
 280         usr,
 281         is_renewal
 282         );
 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;
 289
 290     -- Apply the rules to this circulation
 291     EXECUTE ('UPDATE ' || tablename || ' c
 292     SET
 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,
 298       max_fine =
 299         CASE rmf.is_percent
 300           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 301           ELSE rmf.amount
 302         END,
 303       renewal_remaining = rcd.max_renewals
 304     FROM
 305       config.rule_circ_duration rcd,
 306       config.rule_recuring_fine rrf,
 307       config.rule_max_fine rmf,
 308                         asset.copy ac
 309     WHERE
 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 || ';');
 315
 316     -- Keep track of where we are in the process
 317     n := n + 1;
 318     IF (n % 100 = 0) THEN
 319       RAISE INFO '%', n || ' of ' || n_circs
 320         || ' (' || (100*n/n_circs) || '%) circs updated.';
 321     END IF;
 322
 323   END LOOP;
 324
 325   RETURN;
 326 END;
 327
 328 $$ LANGUAGE plpgsql;
 329
 330 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
 331
 332 -- Usage:
 333 --
 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.
 338 --
 339 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 340 --
 341
 342 DECLARE
 343   circ_lib             INT;
 344   target_copy          INT;
 345   usr                  INT;
 346   is_renewal           BOOLEAN;
 347   this_duration_rule   INT;
 348   this_fine_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;
 353   circ                 INT;
 354   n                    INT := 0;
 355   n_circs              INT;
 356
 357 BEGIN
 358
 359   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 360
 361   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 362
 363     -- Fetch the correct rules for this circulation
 364     EXECUTE ('
 365       SELECT
 366         circ_lib,
 367         target_copy,
 368         usr,
 369         CASE
 370           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 371           ELSE FALSE
 372         END
 373       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 374       INTO circ_lib, target_copy, usr, is_renewal ;
 375     SELECT
 376       INTO this_duration_rule,
 377            this_fine_rule,
 378            this_max_fine_rule
 379       (matchpoint).duration_rule,
 380       (matchpoint).recurring_fine_rule,
 381       (matchpoint).max_fine_rule
 382       FROM action.find_circ_matrix_matchpoint(
 383         circ_lib,
 384         target_copy,
 385         usr,
 386         is_renewal
 387         );
 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;
 394
 395     -- Apply the rules to this circulation
 396     EXECUTE ('UPDATE ' || tablename || ' c
 397     SET
 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,
 403       max_fine =
 404         CASE rmf.is_percent
 405           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 406           ELSE rmf.amount
 407         END,
 408       renewal_remaining = rcd.max_renewals,
 409       grace_period = rrf.grace_period
 410     FROM
 411       config.rule_circ_duration rcd,
 412       config.rule_recurring_fine rrf,
 413       config.rule_max_fine rmf,
 414                         asset.copy ac
 415     WHERE
 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 || ';');
 421
 422     -- Keep track of where we are in the process
 423     n := n + 1;
 424     IF (n % 100 = 0) THEN
 425       RAISE INFO '%', n || ' of ' || n_circs
 426         || ' (' || (100*n/n_circs) || '%) circs updated.';
 427     END IF;
 428
 429   END LOOP;
 430
 431   RETURN;
 432 END;
 433
 434 $$ LANGUAGE plpgsql;
 435
 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 $$
 439 DECLARE
 440     context_lib             INT;
 441     charge_lost_on_zero     BOOLEAN;
 442     min_price               NUMERIC;
 443     max_price               NUMERIC;
 444     default_price           NUMERIC;
 445     working_price           NUMERIC;
 446
 447 BEGIN
 448
 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;
 451
 452     SELECT INTO charge_lost_on_zero value
 453         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
 454
 455     SELECT INTO min_price value
 456         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
 457
 458     SELECT INTO max_price value
 459         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
 460
 461     SELECT INTO default_price value
 462         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
 463
 464     SELECT INTO working_price price FROM asset.copy WHERE id = item;
 465
 466     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
 467         working_price := default_price;
 468     END IF;
 469
 470     IF (max_price IS NOT NULL AND working_price > max_price) THEN
 471         working_price := max_price;
 472     END IF;
 473
 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;
 477         END IF;
 478     END IF;
 479
 480     RETURN working_price;
 481
 482 END;
 483
 484 $$ LANGUAGE plpgsql;
 485
 486 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
 487
 488 -- Usage:
 489 --
 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.
 494 --
 495 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
 496 --
 497
 498 DECLARE
 499   circ_lib             INT;
 500   target_copy          INT;
 501   usr                  INT;
 502   is_renewal           BOOLEAN;
 503   this_duration_rule   INT;
 504   this_fine_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;
 509   n                    INT := 0;
 510   n_circs              INT := 1;
 511
 512 BEGIN
 513
 514   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 515
 516   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 517
 518     -- Fetch the correct rules for this circulation
 519     EXECUTE ('
 520       SELECT
 521         circ_lib,
 522         target_copy,
 523         usr,
 524         CASE
 525           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 526           ELSE FALSE
 527         END
 528       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 529       INTO circ_lib, target_copy, usr, is_renewal ;
 530     SELECT
 531       INTO this_duration_rule,
 532            this_fine_rule,
 533            this_max_fine_rule
 534       (matchpoint).duration_rule,
 535       (matchpoint).recurring_fine_rule,
 536       (matchpoint).max_fine_rule
 537       FROM action.find_circ_matrix_matchpoint(
 538         circ_lib,
 539         target_copy,
 540         usr,
 541         is_renewal
 542         );
 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;
 549
 550     -- Apply the rules to this circulation
 551     EXECUTE ('UPDATE ' || tablename || ' c
 552     SET
 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,
 558       max_fine =
 559         CASE rmf.is_percent
 560           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
 561           ELSE rmf.amount
 562         END,
 563       renewal_remaining = rcd.max_renewals,
 564       grace_period = rrf.grace_period
 565     FROM
 566       config.rule_circ_duration rcd,
 567       config.rule_recurring_fine rrf,
 568       config.rule_max_fine rmf,
 569                         asset.copy ac
 570     WHERE
 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 || ';');
 576
 577     -- Keep track of where we are in the process
 578     n := n + 1;
 579     IF (n % 100 = 0) THEN
 580       RAISE INFO '%', n || ' of ' || n_circs
 581         || ' (' || (100*n/n_circs) || '%) circs updated.';
 582     END IF;
 583
 584   --END LOOP;
 585
 586   RETURN;
 587 END;
 588
 589 $$ LANGUAGE plpgsql;
 590
 591
 592
 593
 594 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
 595
 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');
 598
 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.
 601
 602 DECLARE
 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';
 607         stat_cat                                                 INT;
 608   stat_cat_entry       INT;
 609
 610 BEGIN
 611
 612   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
 613
 614                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
 615
 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 || ');');
 619
 620   END LOOP;
 621
 622   RETURN;
 623 END;
 624
 625 $$ LANGUAGE plpgsql;
 626
 627 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
 628
 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.
 631
 632 DECLARE
 633   org_unit  INT;
 634   usr       INT;
 635
 636 BEGIN
 637
 638   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
 639
 640     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
 641
 642       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
 643
 644     END LOOP;
 645
 646   END LOOP;
 647
 648   RETURN;
 649
 650 END;
 651
 652 $$ LANGUAGE plpgsql;
 653
 654
 655 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
 656
 657 BEGIN
 658   INSERT INTO metabib.metarecord (fingerprint, master_record)
 659     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
 660       FROM  biblio.record_entry b
 661       WHERE NOT b.deleted
 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)
 666     SELECT  m.id, r.id
 667       FROM  biblio.record_entry r
 668       JOIN  metabib.metarecord m USING (fingerprint)
 669      WHERE  NOT r.deleted;
 670 END;
 671
 672 $$ LANGUAGE plpgsql;
 673
 674
 675 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
 676
 677 BEGIN
 678   INSERT INTO metabib.metarecord (fingerprint, master_record)
 679     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
 680       FROM  biblio.record_entry b
 681       WHERE NOT b.deleted
 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)
 686     SELECT  m.id, r.id
 687       FROM  biblio.record_entry r
 688         JOIN metabib.metarecord m USING (fingerprint)
 689       WHERE NOT r.deleted
 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);
 691 END;
 692
 693 $$ LANGUAGE plpgsql;
 694
 695
 696 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
 697
 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');
 700
 701 DECLARE
 702         u                    TEXT := schemaname || '.actor_usr_legacy';
 703         c                    TEXT := schemaname || '.actor_card';
 704
 705 BEGIN
 706
 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;');
 710
 711   RETURN;
 712
 713 END;
 714
 715 $$ LANGUAGE plpgsql;
 716
 717
 718 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
 719
 720   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 721
 722   my ($marcxml, $shortname) = @_;
 723
 724   use MARC::Record;
 725   use MARC::File::XML;
 726
 727   my $xml = $marcxml;
 728
 729   eval {
 730     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 731
 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');
 737       }
 738     }
 739
 740     $xml = $marc->as_xml_record;
 741     $xml =~ s/^<\?.+?\?>$//mo;
 742     $xml =~ s/\n//sgo;
 743     $xml =~ s/>\s+</></sgo;
 744   };
 745
 746   return $xml;
 747
 748 $$ LANGUAGE PLPERLU STABLE;
 749
 750 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
 751
 752   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 753
 754   my ($marcxml, $shortname) = @_;
 755
 756   use MARC::Record;
 757   use MARC::File::XML;
 758
 759   my $xml = $marcxml;
 760
 761   eval {
 762     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 763
 764     foreach my $field ( $marc->field('856') ) {
 765       if ( ! $field->as_string('9') ) {
 766         $field->add_subfields( '9' => $shortname );
 767       }
 768     }
 769
 770     $xml = $marc->as_xml_record;
 771     $xml =~ s/^<\?.+?\?>$//mo;
 772     $xml =~ s/\n//sgo;
 773     $xml =~ s/>\s+</></sgo;
 774   };
 775
 776   return $xml;
 777
 778 $$ LANGUAGE PLPERLU STABLE;
 779
 780
 781 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
 782
 783 DECLARE
 784   old_volume   BIGINT;
 785   new_volume   BIGINT;
 786   bib          BIGINT;
 787   owner        INTEGER;
 788   old_label    TEXT;
 789   remainder    BIGINT;
 790
 791 BEGIN
 792
 793   -- Bail out if asked to change the label to ##URI##
 794   IF new_label = '##URI##' THEN
 795     RETURN;
 796   END IF;
 797
 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;
 802
 803   -- Bail out if the label already is ##URI##
 804   IF old_label = '##URI##' THEN
 805     RETURN;
 806   END IF;
 807
 808   -- Bail out if the call number label is already correct
 809   IF new_volume = old_volume THEN
 810     RETURN;
 811   END IF;
 812
 813   -- Check whether we already have a destination volume available
 814   SELECT id INTO new_volume FROM asset.call_number
 815     WHERE
 816       record = bib AND
 817       owning_lib = owner AND
 818       label = new_label AND
 819       NOT deleted;
 820
 821   -- Create destination volume if needed
 822   IF NOT FOUND THEN
 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
 826       WHERE
 827         record = bib AND
 828         owning_lib = owner AND
 829         label = new_label AND
 830         NOT deleted;
 831   END IF;
 832
 833   -- Move copy to destination
 834   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
 835
 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;
 838   IF NOT FOUND THEN
 839     DELETE FROM asset.call_number WHERE id = old_volume;
 840   END IF;
 841
 842 END;
 843
 844 $$ LANGUAGE plpgsql;
 845
 846 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
 847
 848         my $input = $_[0];
 849         my %zipdata;
 850
 851         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
 852
 853         while (<FH>) {
 854                 chomp;
 855                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
 856                 $zipdata{$zip} = [$city, $state, $county];
 857         }
 858
 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];
 865         } else {
 866                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
 867         }
 868
 869 $$ LANGUAGE PLPERLU STABLE;
 870
 871 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
 872
 873 DECLARE
 874   ou  INT;
 875         org_unit_depth INT;
 876         ou_parent INT;
 877         parent_depth INT;
 878   errors_found BOOLEAN;
 879         ou_shortname TEXT;
 880         parent_shortname TEXT;
 881         ou_type_name TEXT;
 882         parent_type TEXT;
 883         type_id INT;
 884         type_depth INT;
 885         type_parent INT;
 886         type_parent_depth INT;
 887         proper_parent TEXT;
 888
 889 BEGIN
 890
 891         errors_found := FALSE;
 892
 893 -- Checking actor.org_unit_type
 894
 895         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
 896
 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;
 899
 900                 IF type_parent IS NOT NULL THEN
 901
 902                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
 903
 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;
 910
 911                         END IF;
 912
 913                 END IF;
 914
 915         END LOOP;
 916
 917 -- Checking actor.org_unit
 918
 919   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
 920
 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;
 928
 929                 IF ou_parent IS NOT NULL THEN
 930
 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)
 933                         ) THEN
 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;
 937                         END IF;
 938
 939                 END IF;
 940
 941   END LOOP;
 942
 943         IF NOT errors_found THEN
 944                 RAISE INFO 'No errors found.';
 945         END IF;
 946
 947   RETURN;
 948
 949 END;
 950
 951 $$ LANGUAGE plpgsql;
 952
 953
 954 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
 955
 956 BEGIN
 957
 958         DELETE FROM asset.opac_visible_copies;
 959
 960         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
 961                 SELECT DISTINCT
 962                         cp.id, cp.circ_lib, cn.record
 963                 FROM
 964                         asset.copy cp
 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)
 970                 WHERE
 971                         NOT cp.deleted AND
 972                         NOT cn.deleted AND
 973                         NOT b.deleted AND
 974                         cs.opac_visible AND
 975                         cl.opac_visible AND
 976                         cp.opac_visible AND
 977                         a.opac_visible AND
 978                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
 979
 980 END;
 981
 982 $$ LANGUAGE plpgsql;
 983
 984
 985 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
 986
 987 DECLARE
 988   old_volume     BIGINT;
 989   new_volume     BIGINT;
 990   bib            BIGINT;
 991   old_owning_lib INTEGER;
 992         old_label      TEXT;
 993   remainder      BIGINT;
 994
 995 BEGIN
 996
 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;
1001
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
1004                 RAISE WARNING
1005                         '% is not a valid actor.org_unit ID; no change made.',
1006                                 new_owning_lib;
1007                 RETURN;
1008         END IF;
1009
1010   -- Bail out discreetly if the owning_lib is already correct
1011   IF new_owning_lib = old_owning_lib THEN
1012     RETURN;
1013   END IF;
1014
1015   -- Check whether we already have a destination volume available
1016   SELECT id INTO new_volume FROM asset.call_number
1017     WHERE
1018       record = bib AND
1019       owning_lib = new_owning_lib AND
1020       label = old_label AND
1021       NOT deleted;
1022
1023   -- Create destination volume if needed
1024   IF NOT FOUND THEN
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
1028       WHERE
1029         record = bib AND
1030         owning_lib = new_owning_lib AND
1031         label = old_label AND
1032         NOT deleted;
1033   END IF;
1034
1035   -- Move copy to destination
1036   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1037
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;
1040   IF NOT FOUND THEN
1041     DELETE FROM asset.call_number WHERE id = old_volume;
1042   END IF;
1043
1044 END;
1045
1046 $$ LANGUAGE plpgsql;
1047
1048
1049 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1050
1051 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1052
1053 DECLARE
1054         new_owning_lib  INTEGER;
1055
1056 BEGIN
1057
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
1064                         RAISE INFO
1065                                 '%',
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);
1070                 END IF;
1071         ELSE
1072                 RAISE WARNING
1073                         '% is not a valid actor.org_unit shortname or ID; no change made.',
1074                         new_owning_lib;
1075                 RETURN;
1076         END IF;
1077
1078 END;
1079
1080 $$ LANGUAGE plpgsql;
1081
1082 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1083
1084 use MARC::Record;
1085 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1086 use MARC::Charset;
1087
1088 MARC::Charset->assume_unicode(1);
1089
1090 my $xml = shift;
1091
1092 eval {
1093     my $r = MARC::Record->new_from_xml( $xml );
1094     my $output_xml = $r->as_xml_record();
1095 };
1096 if ($@) {
1097     return 0;
1098 } else {
1099     return 1;
1100 }
1101
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';
1104
1105 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1106 BEGIN
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'$$;
1133 END;
1134 $FUNC$ LANGUAGE PLPGSQL;
1135
1136 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1137 BEGIN
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'$$;
1145
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');
1151
1152    -- and permission groups
1153    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1154
1155 END;
1156 $FUNC$ LANGUAGE PLPGSQL;
1157
1158
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$
1160 DECLARE
1161     name TEXT;
1162     loopq TEXT;
1163     existsq TEXT;
1164     ct INTEGER;
1165     cols TEXT[];
1166     copyst TEXT;
1167 BEGIN
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;
1176        IF ct = 0 THEN
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;
1181        END IF;
1182     END LOOP;
1183 END;
1184 $FUNC$ LANGUAGE PLPGSQL;
1185
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$
1187 DECLARE
1188     id BIGINT;
1189     loopq TEXT;
1190     cols TEXT[];
1191     splitst TEXT;
1192 BEGIN
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;
1200     END LOOP;
1201 END;
1202 $FUNC$ LANGUAGE PLPGSQL;
1203
1204 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1205
1206 use strict;
1207 use warnings;
1208
1209 use MARC::Record;
1210 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1211 use MARC::Charset;
1212
1213 MARC::Charset->assume_unicode(1);
1214
1215 my $target_xml = shift;
1216 my $source_xml = shift;
1217 my $tags = shift;
1218
1219 my $target;
1220 my $source;
1221
1222 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1223 if ($@) {
1224     return;
1225 }
1226 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1227 if ($@) {
1228     return;
1229 }
1230
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;
1235
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);
1241     if (@to_add) {
1242         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1243     }
1244 }
1245
1246 my $xml = $target->as_xml_record;
1247 $xml =~ s/^<\?.+?\?>$//mo;
1248 $xml =~ s/\n//sgo;
1249 $xml =~ s/>\s+</></sgo;
1250
1251 return $xml;
1252
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.';
1255
1256 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1257
1258 use strict;
1259 use warnings;
1260
1261 use MARC::Record;
1262 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1263 use Text::CSV;
1264
1265 my $in_tags = shift;
1266 my $in_values = shift;
1267
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});
1271 $in_tags =~ s/^{//;
1272 $in_tags =~ s/}$//;
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() ];
1279
1280 my $marc = MARC::Record->new();
1281
1282 $marc->leader('00000nam a22000007  4500');
1283 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
1284
1285 foreach my $i (0..$#$tags) {
1286     my ($tag, $sf);
1287     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1288         $tag = $1;
1289         $sf = $2;
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})$/) {
1292         $tag = $1;
1293         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1294     }
1295 }
1296
1297 my $xml = $marc->as_xml_record;
1298 $xml =~ s/^<\?.+?\?>$//mo;
1299 $xml =~ s/\n//sgo;
1300 $xml =~ s/>\s+</></sgo;
1301
1302 return $xml;
1303
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.
1309 $$;
1310
1311 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1312
1313 my ($marcxml, $tag, $pos, $value) = @_;
1314
1315 use MARC::Record;
1316 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1317 use MARC::Charset;
1318 use strict;
1319
1320 MARC::Charset->assume_unicode(1);
1321
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 =~ /^.$/;
1326
1327 my $xml = $marcxml;
1328 eval {
1329     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1330
1331     foreach my $field ($marc->field($tag)) {
1332         $field->update("ind$pos" => $value);
1333     }
1334     $xml = $marc->as_xml_record;
1335     $xml =~ s/^<\?.+?\?>$//mo;
1336     $xml =~ s/\n//sgo;
1337     $xml =~ s/>\s+</></sgo;
1338 };
1339 return $xml;
1340
1341 $func$ LANGUAGE PLPERLU;
1342
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.$$;
1350
1351 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
1352     username TEXT,
1353     password TEXT,
1354     org TEXT,
1355     perm_group TEXT,
1356     first_name TEXT DEFAULT '',
1357     last_name TEXT DEFAULT ''
1358 ) RETURNS VOID AS $func$
1359 BEGIN
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;
1366 END
1367 $func$
1368 LANGUAGE PLPGSQL;
1369
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 $$
1372     DECLARE
1373         target_event_def ALIAS FOR $1;
1374         orgs ALIAS FOR $2;
1375     BEGIN
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 (
1380                 active
1381                 ,owner
1382                 ,name
1383                 ,hook
1384                 ,validator
1385                 ,reactor
1386                 ,cleanup_success
1387                 ,cleanup_failure
1388                 ,delay
1389                 ,max_delay
1390                 ,usr_field
1391                 ,opt_in_setting
1392                 ,delay_field
1393                 ,group_field
1394                 ,template
1395                 ,granularity
1396                 ,repeat_delay
1397             ) SELECT
1398                 'f'
1399                 ,orgs[i]
1400                 ,name || ' (clone of '||target_event_def||')'
1401                 ,hook
1402                 ,validator
1403                 ,reactor
1404                 ,cleanup_success
1405                 ,cleanup_failure
1406                 ,delay
1407                 ,max_delay
1408                 ,usr_field
1409                 ,opt_in_setting
1410                 ,delay_field
1411                 ,group_field
1412                 ,template
1413                 ,granularity
1414                 ,repeat_delay
1415             FROM
1416                 action_trigger.event_definition
1417             WHERE
1418                 id = target_event_def
1419             ;
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 (
1423                 event_def
1424                 ,path
1425                 ,collector
1426                 ,label
1427             ) SELECT
1428                 currval('action_trigger.event_definition_id_seq')
1429                 ,path
1430                 ,collector
1431                 ,label
1432             FROM
1433                 action_trigger.environment
1434             WHERE
1435                 event_def = target_event_def
1436             ;
1437             INSERT INTO action_trigger.event_params (
1438                 event_def
1439                 ,param
1440                 ,value
1441             ) SELECT
1442                 currval('action_trigger.event_definition_id_seq')
1443                 ,param
1444                 ,value
1445             FROM
1446                 action_trigger.event_params
1447             WHERE
1448                 event_def = target_event_def
1449             ;
1450         END LOOP;
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);
1452     END;
1453 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1454
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 $$
1457     DECLARE
1458         target_event_def ALIAS FOR $1;
1459         orgs ALIAS FOR $2;
1460         new_interval ALIAS FOR $3;
1461     BEGIN
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 (
1466                 active
1467                 ,owner
1468                 ,name
1469                 ,hook
1470                 ,validator
1471                 ,reactor
1472                 ,cleanup_success
1473                 ,cleanup_failure
1474                 ,delay
1475                 ,max_delay
1476                 ,usr_field
1477                 ,opt_in_setting
1478                 ,delay_field
1479                 ,group_field
1480                 ,template
1481                 ,granularity
1482                 ,repeat_delay
1483             ) SELECT
1484                 'f'
1485                 ,orgs[i]
1486                 ,name || ' (clone of '||target_event_def||')'
1487                 ,hook
1488                 ,validator
1489                 ,reactor
1490                 ,cleanup_success
1491                 ,cleanup_failure
1492                 ,new_interval
1493                 ,max_delay
1494                 ,usr_field
1495                 ,opt_in_setting
1496                 ,delay_field
1497                 ,group_field
1498                 ,template
1499                 ,granularity
1500                 ,repeat_delay
1501             FROM
1502                 action_trigger.event_definition
1503             WHERE
1504                 id = target_event_def
1505             ;
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 (
1509                 event_def
1510                 ,path
1511                 ,collector
1512                 ,label
1513             ) SELECT
1514                 currval('action_trigger.event_definition_id_seq')
1515                 ,path
1516                 ,collector
1517                 ,label
1518             FROM
1519                 action_trigger.environment
1520             WHERE
1521                 event_def = target_event_def
1522             ;
1523             INSERT INTO action_trigger.event_params (
1524                 event_def
1525                 ,param
1526                 ,value
1527             ) SELECT
1528                 currval('action_trigger.event_definition_id_seq')
1529                 ,param
1530                 ,value
1531             FROM
1532                 action_trigger.event_params
1533             WHERE
1534                 event_def = target_event_def
1535             ;
1536         END LOOP;
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);
1538     END;
1539 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1540
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 $$
1543     DECLARE
1544         org ALIAS FOR $1;
1545         target_event_defs ALIAS FOR $2;
1546     BEGIN
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 (
1551                 active
1552                 ,owner
1553                 ,name
1554                 ,hook
1555                 ,validator
1556                 ,reactor
1557                 ,cleanup_success
1558                 ,cleanup_failure
1559                 ,delay
1560                 ,max_delay
1561                 ,usr_field
1562                 ,opt_in_setting
1563                 ,delay_field
1564                 ,group_field
1565                 ,template
1566                 ,granularity
1567                 ,repeat_delay
1568             ) SELECT
1569                 'f'
1570                 ,org
1571                 ,name || ' (clone of '||target_event_defs[i]||')'
1572                 ,hook
1573                 ,validator
1574                 ,reactor
1575                 ,cleanup_success
1576                 ,cleanup_failure
1577                 ,delay
1578                 ,max_delay
1579                 ,usr_field
1580                 ,opt_in_setting
1581                 ,delay_field
1582                 ,group_field
1583                 ,template
1584                 ,granularity
1585                 ,repeat_delay
1586             FROM
1587                 action_trigger.event_definition
1588             WHERE
1589                 id = target_event_defs[i]
1590             ;
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 (
1594                 event_def
1595                 ,path
1596                 ,collector
1597                 ,label
1598             ) SELECT
1599                 currval('action_trigger.event_definition_id_seq')
1600                 ,path
1601                 ,collector
1602                 ,label
1603             FROM
1604                 action_trigger.environment
1605             WHERE
1606                 event_def = target_event_defs[i]
1607             ;
1608             INSERT INTO action_trigger.event_params (
1609                 event_def
1610                 ,param
1611                 ,value
1612             ) SELECT
1613                 currval('action_trigger.event_definition_id_seq')
1614                 ,param
1615                 ,value
1616             FROM
1617                 action_trigger.event_params
1618             WHERE
1619                 event_def = target_event_defs[i]
1620             ;
1621         END LOOP;
1622         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1623     END;
1624 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1625
1626 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
1627     UPDATE
1628         action_trigger.event
1629     SET
1630          start_time = NULL
1631         ,update_time = NULL
1632         ,complete_time = NULL
1633         ,update_process = NULL
1634         ,state = 'pending'
1635         ,template_output = NULL
1636         ,error_output = NULL
1637         ,async_output = NULL
1638     WHERE
1639         id = $1;
1640 $$ LANGUAGE SQL;
1641
1642 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
1643     my ($marcxml) = @_;
1644
1645     use MARC::Record;
1646     use MARC::File::XML;
1647     use MARC::Field;
1648
1649     my $field;
1650     eval {
1651         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1652         $field = $marc->leader();
1653     };
1654     return $field;
1655 $$ LANGUAGE PLPERLU STABLE;
1656
1657 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
1658     my ($marcxml, $tag, $subfield, $delimiter) = @_;
1659
1660     use MARC::Record;
1661     use MARC::File::XML;
1662     use MARC::Field;
1663
1664     my $field;
1665     eval {
1666         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1667         $field = $marc->field($tag);
1668     };
1669     return $field->as_string($subfield,$delimiter);
1670 $$ LANGUAGE PLPERLU STABLE;
1671
1672 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
1673     my ($marcxml, $tag, $subfield, $delimiter) = @_;
1674
1675     use MARC::Record;
1676     use MARC::File::XML;
1677     use MARC::Field;
1678
1679     my @fields;
1680     eval {
1681         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1682         @fields = $marc->field($tag);
1683     };
1684     my @texts;
1685     foreach my $field (@fields) {
1686         push @texts, $field->as_string($subfield,$delimiter);
1687     }
1688     return \@texts;
1689 $$ LANGUAGE PLPERLU STABLE;
1690
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) = @_;
1693
1694     use MARC::Record;
1695     use MARC::File::XML;
1696     use MARC::Field;
1697
1698     my @fields;
1699     eval {
1700         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1701         @fields = $marc->field($tag);
1702     };
1703     my @texts;
1704     foreach my $field (@fields) {
1705         if ($field->as_string() =~ qr/$match/) {
1706             push @texts, $field->as_string($subfield,$delimiter);
1707         }
1708     }
1709     return \@texts;
1710 $$ LANGUAGE PLPERLU STABLE;
1711
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)
1719     );
1720 $$ LANGUAGE SQL;
1721
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)
1729     );
1730 $$ LANGUAGE SQL;
1731
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),
1737         (SELECT COALESCE(
1738                 NULLIF(phone_renewal,false),
1739                 NULLIF(desk_renewal,false),
1740                 NULLIF(opac_renewal,false),
1741                 false
1742             ) FROM action.circulation WHERE id = $1
1743         )
1744     );
1745 $$ LANGUAGE SQL;
1746
1747 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
1748     DECLARE
1749         test ALIAS FOR $1;
1750     BEGIN
1751         IF NOT test THEN
1752             RAISE EXCEPTION 'assertion';
1753         END IF;
1754     END;
1755 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1756
1757 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
1758     DECLARE
1759         test ALIAS FOR $1;
1760         msg ALIAS FOR $2;
1761     BEGIN
1762         IF NOT test THEN
1763             RAISE EXCEPTION '%', msg;
1764         END IF;
1765     END;
1766 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1767
1768 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
1769     DECLARE
1770         test ALIAS FOR $1;
1771         fail_msg ALIAS FOR $2;
1772         success_msg ALIAS FOR $3;
1773     BEGIN
1774         IF NOT test THEN
1775             RAISE EXCEPTION '%', fail_msg;
1776         END IF;
1777         RETURN success_msg;
1778     END;
1779 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1780
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 $$
1783     DECLARE
1784         bib_count ALIAS FOR $1;
1785         output BIGINT;
1786     BEGIN
1787         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
1788         FOR output IN
1789             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
1790         LOOP
1791             RETURN output;
1792         END LOOP;
1793     END;
1794 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1795
1796 -- set a new salted password
1797
1798 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
1799     DECLARE
1800         usr_id              ALIAS FOR $1;
1801         plain_passwd        ALIAS FOR $2;
1802         plain_salt          TEXT;
1803         md5_passwd          TEXT;
1804     BEGIN
1805
1806         SELECT actor.create_salt('main') INTO plain_salt;
1807
1808         SELECT MD5(plain_passwd) INTO md5_passwd;
1809
1810         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
1811
1812         RETURN TRUE;
1813
1814     END;
1815 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1816
1817
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);
1821 $$ LANGUAGE SQL;
1822
1823 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
1824     DECLARE
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;
1830         proceed BOOLEAN;
1831         org INTEGER;
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;
1835         x_org INTEGER;
1836         org_list INTEGER[];
1837         o INTEGER;
1838         row_count NUMERIC;
1839     BEGIN
1840         EXECUTE 'SELECT EXISTS (
1841             SELECT 1
1842             FROM information_schema.columns
1843             WHERE table_schema = $1
1844             AND table_name = $2
1845             and column_name = ''desired_shelf''
1846         )' INTO proceed USING table_schema, table_name;
1847         IF NOT proceed THEN
1848             RAISE EXCEPTION 'Missing column desired_shelf';
1849         END IF;
1850
1851         EXECUTE 'SELECT EXISTS (
1852             SELECT 1
1853             FROM information_schema.columns
1854             WHERE table_schema = $1
1855             AND table_name = $2
1856             and column_name = ''x_org''
1857         )' INTO x_org_found USING table_schema, table_name;
1858
1859         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1860         IF org IS NULL THEN
1861             RAISE EXCEPTION 'Cannot find org by shortname';
1862         END IF;
1863
1864         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1865
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';
1872
1873         IF x_org_found THEN
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';
1886         ELSE
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'
1893             USING org;
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'
1900             USING org;
1901         END IF;
1902
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'
1910             USING o;
1911             GET DIAGNOSTICS row_count = ROW_COUNT;
1912             RAISE INFO 'Updated % rows', row_count;
1913         END LOOP;
1914
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''
1920             );';
1921         END IF;
1922
1923     END;
1924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1925
1926 -- convenience functions for handling circmod maps
1927
1928 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
1929     DECLARE
1930         table_schema ALIAS FOR $1;
1931         table_name ALIAS FOR $2;
1932         proceed BOOLEAN;
1933     BEGIN
1934         EXECUTE 'SELECT EXISTS (
1935             SELECT 1
1936             FROM information_schema.columns
1937             WHERE table_schema = $1
1938             AND table_name = $2
1939             and column_name = ''desired_circmod''
1940         )' INTO proceed USING table_schema, table_name;
1941         IF NOT proceed THEN
1942             RAISE EXCEPTION 'Missing column desired_circmod';
1943         END IF;
1944
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';
1951
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))';
1955
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';
1960
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';
1965
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''
1970         );';
1971
1972     END;
1973 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1974
1975 -- convenience functions for handling item status maps
1976
1977 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
1978     DECLARE
1979         table_schema ALIAS FOR $1;
1980         table_name ALIAS FOR $2;
1981         proceed BOOLEAN;
1982     BEGIN
1983         EXECUTE 'SELECT EXISTS (
1984             SELECT 1
1985             FROM information_schema.columns
1986             WHERE table_schema = $1
1987             AND table_name = $2
1988             and column_name = ''desired_status''
1989         )' INTO proceed USING table_schema, table_name;
1990         IF NOT proceed THEN
1991             RAISE EXCEPTION 'Missing column desired_status';
1992         END IF;
1993
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';
2000
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))';
2004
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''
2009         );';
2010
2011     END;
2012 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2013
2014 -- convenience functions for handling org maps
2015
2016 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
2017     DECLARE
2018         table_schema ALIAS FOR $1;
2019         table_name ALIAS FOR $2;
2020         proceed BOOLEAN;
2021     BEGIN
2022         EXECUTE 'SELECT EXISTS (
2023             SELECT 1
2024             FROM information_schema.columns
2025             WHERE table_schema = $1
2026             AND table_name = $2
2027             and column_name = ''desired_org''
2028         )' INTO proceed USING table_schema, table_name;
2029         IF NOT proceed THEN
2030             RAISE EXCEPTION 'Missing column desired_org';
2031         END IF;
2032
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';
2039
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)';
2043
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''
2048         );';
2049
2050     END;
2051 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2052
2053 -- convenience function for handling desired_not_migrate
2054
2055 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
2056     DECLARE
2057         table_schema ALIAS FOR $1;
2058         table_name ALIAS FOR $2;
2059         proceed BOOLEAN;
2060     BEGIN
2061         EXECUTE 'SELECT EXISTS (
2062             SELECT 1
2063             FROM information_schema.columns
2064             WHERE table_schema = $1
2065             AND table_name = $2
2066             and column_name = ''desired_not_migrate''
2067         )' INTO proceed USING table_schema, table_name;
2068         IF NOT proceed THEN
2069             RAISE EXCEPTION 'Missing column desired_not_migrate';
2070         END IF;
2071
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';
2078
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'
2087             || ' END';
2088
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''
2093         );';
2094
2095     END;
2096 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2097
2098 -- convenience function for handling desired_not_migrate
2099
2100 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
2101     DECLARE
2102         table_schema ALIAS FOR $1;
2103         table_name ALIAS FOR $2;
2104         proceed BOOLEAN;
2105     BEGIN
2106         EXECUTE 'SELECT EXISTS (
2107             SELECT 1
2108             FROM information_schema.columns
2109             WHERE table_schema = $1
2110             AND table_name = $2
2111             and column_name = ''desired_barred_or_blocked''
2112         )' INTO proceed USING table_schema, table_name;
2113         IF NOT proceed THEN
2114             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
2115         END IF;
2116
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';
2123
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';
2130
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'
2137             || ' END';
2138
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'
2145             || ' END';
2146
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''
2151         );';
2152
2153     END;
2154 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2155
2156 -- convenience function for handling desired_profile
2157
2158 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
2159     DECLARE
2160         table_schema ALIAS FOR $1;
2161         table_name ALIAS FOR $2;
2162         proceed BOOLEAN;
2163     BEGIN
2164         EXECUTE 'SELECT EXISTS (
2165             SELECT 1
2166             FROM information_schema.columns
2167             WHERE table_schema = $1
2168             AND table_name = $2
2169             and column_name = ''desired_profile''
2170         )' INTO proceed USING table_schema, table_name;
2171         IF NOT proceed THEN
2172             RAISE EXCEPTION 'Missing column desired_profile';
2173         END IF;
2174
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';
2181
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))';
2185
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''
2190         );';
2191
2192     END;
2193 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2194
2195 -- convenience function for handling desired actor stat cats
2196
2197 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2198     DECLARE
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;
2203         proceed BOOLEAN;
2204         org INTEGER;
2205         org_list INTEGER[];
2206         sc TEXT;
2207         sce TEXT;
2208     BEGIN
2209
2210         SELECT 'desired_sc' || field_suffix INTO sc;
2211         SELECT 'desired_sce' || field_suffix INTO sce;
2212
2213         EXECUTE 'SELECT EXISTS (
2214             SELECT 1
2215             FROM information_schema.columns
2216             WHERE table_schema = $1
2217             AND table_name = $2
2218             and column_name = $3
2219         )' INTO proceed USING table_schema, table_name, sc;
2220         IF NOT proceed THEN
2221             RAISE EXCEPTION 'Missing column %', sc;
2222         END IF;
2223         EXECUTE 'SELECT EXISTS (
2224             SELECT 1
2225             FROM information_schema.columns
2226             WHERE table_schema = $1
2227             AND table_name = $2
2228             and column_name = $3
2229         )' INTO proceed USING table_schema, table_name, sce;
2230         IF NOT proceed THEN
2231             RAISE EXCEPTION 'Missing column %', sce;
2232         END IF;
2233
2234         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2235         IF org IS NULL THEN
2236             RAISE EXCEPTION 'Cannot find org by shortname';
2237         END IF;
2238         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2239
2240         -- caller responsible for their own truncates though we try to prevent duplicates
2241         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
2242             SELECT DISTINCT
2243                  $1
2244                 ,BTRIM('||sc||')
2245             FROM
2246                 ' || quote_ident(table_name) || '
2247             WHERE
2248                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2249                 AND NOT EXISTS (
2250                     SELECT id
2251                     FROM actor.stat_cat
2252                     WHERE owner = ANY ($2)
2253                     AND name = BTRIM('||sc||')
2254                 )
2255                 AND NOT EXISTS (
2256                     SELECT id
2257                     FROM actor_stat_cat
2258                     WHERE owner = ANY ($2)
2259                     AND name = BTRIM('||sc||')
2260                 )
2261             ORDER BY 2;'
2262         USING org, org_list;
2263
2264         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
2265             SELECT DISTINCT
2266                 COALESCE(
2267                     (SELECT id
2268                         FROM actor.stat_cat
2269                         WHERE owner = ANY ($2)
2270                         AND BTRIM('||sc||') = BTRIM(name))
2271                    ,(SELECT id
2272                         FROM actor_stat_cat
2273                         WHERE owner = ANY ($2)
2274                         AND BTRIM('||sc||') = BTRIM(name))
2275                 )
2276                 ,$1
2277                 ,BTRIM('||sce||')
2278             FROM
2279                 ' || quote_ident(table_name) || '
2280             WHERE
2281                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2282                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
2283                 AND NOT EXISTS (
2284                     SELECT id
2285                     FROM actor.stat_cat_entry
2286                     WHERE stat_cat = (
2287                         SELECT id
2288                         FROM actor.stat_cat
2289                         WHERE owner = ANY ($2)
2290                         AND BTRIM('||sc||') = BTRIM(name)
2291                     ) AND value = BTRIM('||sce||')
2292                     AND owner = ANY ($2)
2293                 )
2294                 AND NOT EXISTS (
2295                     SELECT id
2296                     FROM actor_stat_cat_entry
2297                     WHERE stat_cat = (
2298                         SELECT id
2299                         FROM actor_stat_cat
2300                         WHERE owner = ANY ($2)
2301                         AND BTRIM('||sc||') = BTRIM(name)
2302                     ) AND value = BTRIM('||sce||')
2303                     AND owner = ANY ($2)
2304                 )
2305             ORDER BY 1,3;'
2306         USING org, org_list;
2307     END;
2308 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2309
2310 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2311     DECLARE
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;
2316         proceed BOOLEAN;
2317         org INTEGER;
2318         org_list INTEGER[];
2319         o INTEGER;
2320         sc TEXT;
2321         sce TEXT;
2322     BEGIN
2323         SELECT 'desired_sc' || field_suffix INTO sc;
2324         SELECT 'desired_sce' || field_suffix INTO sce;
2325         EXECUTE 'SELECT EXISTS (
2326             SELECT 1
2327             FROM information_schema.columns
2328             WHERE table_schema = $1
2329             AND table_name = $2
2330             and column_name = $3
2331         )' INTO proceed USING table_schema, table_name, sc;
2332         IF NOT proceed THEN
2333             RAISE EXCEPTION 'Missing column %', sc;
2334         END IF;
2335         EXECUTE 'SELECT EXISTS (
2336             SELECT 1
2337             FROM information_schema.columns
2338             WHERE table_schema = $1
2339             AND table_name = $2
2340             and column_name = $3
2341         )' INTO proceed USING table_schema, table_name, sce;
2342         IF NOT proceed THEN
2343             RAISE EXCEPTION 'Missing column %', sce;
2344         END IF;
2345
2346         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2347         IF org IS NULL THEN
2348             RAISE EXCEPTION 'Cannot find org by shortname';
2349         END IF;
2350
2351         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2352
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';
2365
2366
2367         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2368             SET
2369                 x_sc' || field_suffix || ' = id
2370             FROM
2371                 (SELECT id, name, owner FROM actor_stat_cat
2372                     UNION SELECT id, name, owner FROM actor.stat_cat) u
2373             WHERE
2374                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2375                 AND u.owner = ANY ($1);'
2376         USING org_list;
2377
2378         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2379             SET
2380                 x_sce' || field_suffix || ' = id
2381             FROM
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
2384             WHERE
2385                     u.stat_cat = x_sc' || field_suffix || '
2386                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2387                 AND u.owner = ANY ($1);'
2388         USING org_list;
2389
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''
2394         );';
2395
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''
2400         );';
2401
2402     END;
2403 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2404
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 $$
2408 DECLARE
2409     return_id   INT;
2410     d           INT;
2411     cur_id      INT;
2412 BEGIN
2413     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2414     WHILE d >= 0
2415     LOOP
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
2419                 RETURN return_id;
2420         END IF;
2421         d := d - 1;
2422     END LOOP;
2423
2424     RETURN NULL;
2425 END
2426 $$ LANGUAGE plpgsql;
2427
2428 -- may remove later but testing using this with new migration scripts and not loading acls until go live
2429
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 $$
2432 DECLARE
2433     return_id   INT;
2434     d           INT;
2435     cur_id      INT;
2436 BEGIN
2437     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2438     WHILE d >= 0
2439     LOOP
2440         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2441
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
2446                 RETURN return_id;
2447         END IF;
2448         d := d - 1;
2449     END LOOP;
2450
2451     RETURN NULL;
2452 END
2453 $$ LANGUAGE plpgsql;
2454
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')
2457     RETURNS BOOLEAN AS
2458 $BODY$
2459 DECLARE
2460         marc_xml        TEXT;
2461         new_marc        TEXT;
2462 BEGIN
2463         SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
2464
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;
2467
2468         RETURN true;
2469 END;
2470 $BODY$ LANGUAGE plpgsql;
2471
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)
2474  RETURNS TEXT
2475  LANGUAGE plperlu
2476 AS $function$
2477 use strict;
2478 use warnings;
2479
2480 use MARC::Record;
2481 use MARC::File::XML (BinaryEncoding => 'utf8');
2482
2483 binmode(STDERR, ':bytes');
2484 binmode(STDOUT, ':utf8');
2485 binmode(STDERR, ':utf8');
2486
2487 my $marc_xml = shift;
2488 my $new_9_to_set = shift;
2489 my $force = shift;
2490
2491 $marc_xml =~ s/(<leader>.........)./${1}a/;
2492
2493 eval {
2494     $marc_xml = MARC::Record->new_from_xml($marc_xml);
2495 };
2496 if ($@) {
2497     #elog("could not parse $bibid: $@\n");
2498     import MARC::File::XML (BinaryEncoding => 'utf8');
2499     return $marc_xml;
2500 }
2501
2502 my @uris = $marc_xml->field('856');
2503 return $marc_xml->as_xml_record() unless @uris;
2504
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 );
2515 }
2516
2517 return $marc_xml->as_xml_record();
2518
2519 $function$;
2520
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)
2523  RETURNS TEXT
2524  LANGUAGE plperlu
2525 AS $function$
2526 use strict;
2527 use warnings;
2528
2529 use MARC::Record;
2530 use MARC::File::XML (BinaryEncoding => 'utf8');
2531
2532 binmode(STDERR, ':bytes');
2533 binmode(STDOUT, ':utf8');
2534 binmode(STDERR, ':utf8');
2535
2536 my $marc_xml = shift;
2537 my $qualifying_match = shift;
2538 my $new_9_to_set = shift;
2539 my $force = shift;
2540
2541 $marc_xml =~ s/(<leader>.........)./${1}a/;
2542
2543 eval {
2544     $marc_xml = MARC::Record->new_from_xml($marc_xml);
2545 };
2546 if ($@) {
2547     #elog("could not parse $bibid: $@\n");
2548     import MARC::File::XML (BinaryEncoding => 'utf8');
2549     return $marc_xml;
2550 }
2551
2552 my @uris = $marc_xml->field('856');
2553 return $marc_xml->as_xml_record() unless @uris;
2554
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 );
2566     }
2567 }
2568
2569 return $marc_xml->as_xml_record();
2570
2571 $function$;
2572
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)
2575  RETURNS TEXT
2576  LANGUAGE plperlu
2577 AS $function$
2578 use strict;
2579 use warnings;
2580
2581 use MARC::Record;
2582 use MARC::File::XML (BinaryEncoding => 'utf8');
2583
2584 binmode(STDERR, ':bytes');
2585 binmode(STDOUT, ':utf8');
2586 binmode(STDERR, ':utf8');
2587
2588 my $marc_xml = shift;
2589 my $substring_old_value = shift;
2590 my $new_value = shift;
2591 my $fix_indicators = shift;
2592
2593 $marc_xml =~ s/(<leader>.........)./${1}a/;
2594
2595 eval {
2596     $marc_xml = MARC::Record->new_from_xml($marc_xml);
2597 };
2598 if ($@) {
2599     #elog("could not parse $bibid: $@\n");
2600     import MARC::File::XML (BinaryEncoding => 'utf8');
2601     return $marc_xml;
2602 }
2603
2604 my @uris = $marc_xml->field('856');
2605 return $marc_xml->as_xml_record() unless @uris;
2606
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');
2612         }
2613     }
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');
2618         }
2619     }
2620     if ($field->as_string('9') =~ qr/$substring_old_value/) {
2621         $field->delete_subfield('9');
2622         $field->add_subfields( '9' => $new_value );
2623     }
2624     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
2625 }
2626
2627 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
2628 $marc_xml->insert_fields_ordered( values( %hash ) );
2629
2630 return $marc_xml->as_xml_record();
2631
2632 $function$;
2633
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)
2636  RETURNS TEXT
2637  LANGUAGE plperlu
2638 AS $function$
2639 use strict;
2640 use warnings;
2641
2642 use MARC::Record;
2643 use MARC::File::XML (BinaryEncoding => 'utf8');
2644
2645 binmode(STDERR, ':bytes');
2646 binmode(STDOUT, ':utf8');
2647 binmode(STDERR, ':utf8');
2648
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;
2654
2655 $marc_xml =~ s/(<leader>.........)./${1}a/;
2656
2657 eval {
2658     $marc_xml = MARC::Record->new_from_xml($marc_xml);
2659 };
2660 if ($@) {
2661     #elog("could not parse $bibid: $@\n");
2662     import MARC::File::XML (BinaryEncoding => 'utf8');
2663     return $marc_xml;
2664 }
2665
2666 my @unqualified_uris = $marc_xml->field('856');
2667 my @uris = ();
2668 foreach my $field (@unqualified_uris) {
2669     if ($field->as_string() =~ qr/$qualifying_match/) {
2670         push @uris, $field;
2671     }
2672 }
2673 return $marc_xml->as_xml_record() unless @uris;
2674
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');
2680         }
2681     }
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');
2686         }
2687     }
2688     if ($field->as_string('9') =~ qr/$substring_old_value/) {
2689         $field->delete_subfield('9');
2690         $field->add_subfields( '9' => $new_value );
2691     }
2692     $marc_xml->delete_field($field); # -- we're going to dedup and add them back
2693 }
2694
2695 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
2696 $marc_xml->insert_fields_ordered( values( %hash ) );
2697
2698 return $marc_xml->as_xml_record();
2699
2700 $function$;
2701
2702 -- strip marc tag
2703 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
2704 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
2705  RETURNS TEXT
2706  LANGUAGE plperlu
2707 AS $function$
2708 use strict;
2709 use warnings;
2710
2711 use MARC::Record;
2712 use MARC::File::XML (BinaryEncoding => 'utf8');
2713
2714 binmode(STDERR, ':bytes');
2715 binmode(STDOUT, ':utf8');
2716 binmode(STDERR, ':utf8');
2717
2718 my $marc_xml = shift;
2719 my $tag = shift;
2720
2721 $marc_xml =~ s/(<leader>.........)./${1}a/;
2722
2723 eval {
2724     $marc_xml = MARC::Record->new_from_xml($marc_xml);
2725 };
2726 if ($@) {
2727     #elog("could not parse $bibid: $@\n");
2728     import MARC::File::XML (BinaryEncoding => 'utf8');
2729     return $marc_xml;
2730 }
2731
2732 my @fields = $marc_xml->field($tag);
2733 return $marc_xml->as_xml_record() unless @fields;
2734
2735 $marc_xml->delete_fields(@fields);
2736
2737 return $marc_xml->as_xml_record();
2738
2739 $function$;
2740
2741 -- convenience function for linking to the item staging table
2742
2743 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2744     DECLARE
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;
2750         proceed BOOLEAN;
2751     BEGIN
2752         EXECUTE 'SELECT EXISTS (
2753             SELECT 1
2754             FROM information_schema.columns
2755             WHERE table_schema = $1
2756             AND table_name = $2
2757             and column_name = $3
2758         )' INTO proceed USING table_schema, table_name, foreign_column_name;
2759         IF NOT proceed THEN
2760             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2761         END IF;
2762
2763         EXECUTE 'SELECT EXISTS (
2764             SELECT 1
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;
2770         IF NOT proceed THEN
2771             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
2772         END IF;
2773
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';
2780
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) || ')';
2786         ELSE
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);
2791         END IF;
2792
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''
2797         --);';
2798
2799     END;
2800 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2801
2802 -- convenience function for linking to the user staging table
2803
2804 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2805     DECLARE
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;
2811         proceed BOOLEAN;
2812     BEGIN
2813         EXECUTE 'SELECT EXISTS (
2814             SELECT 1
2815             FROM information_schema.columns
2816             WHERE table_schema = $1
2817             AND table_name = $2
2818             and column_name = $3
2819         )' INTO proceed USING table_schema, table_name, foreign_column_name;
2820         IF NOT proceed THEN
2821             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2822         END IF;
2823
2824         EXECUTE 'SELECT EXISTS (
2825             SELECT 1
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;
2831         IF NOT proceed THEN
2832             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
2833         END IF;
2834
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';
2841
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) || ')';
2847         ELSE
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);
2852         END IF;
2853
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''
2858         --);';
2859
2860     END;
2861 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2862
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 $$
2866     DECLARE
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;
2874         proceed BOOLEAN;
2875     BEGIN
2876         EXECUTE 'SELECT EXISTS (
2877             SELECT 1
2878             FROM information_schema.columns
2879             WHERE table_schema = $1
2880             AND table_name = $2
2881             and column_name = $3
2882         )' INTO proceed USING table_schema, table_a, column_a;
2883         IF NOT proceed THEN
2884             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2885         END IF;
2886
2887         EXECUTE 'SELECT EXISTS (
2888             SELECT 1
2889             FROM information_schema.columns
2890             WHERE table_schema = $1
2891             AND table_name = $2
2892             and column_name = $3
2893         )' INTO proceed USING table_schema, table_b, column_b;
2894         IF NOT proceed THEN
2895             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2896         END IF;
2897
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';
2904
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) || ')';
2910         ELSE
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);
2915         END IF;
2916
2917     END;
2918 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2919
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 $$
2923     DECLARE
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;
2932         proceed BOOLEAN;
2933     BEGIN
2934         EXECUTE 'SELECT EXISTS (
2935             SELECT 1
2936             FROM information_schema.columns
2937             WHERE table_schema = $1
2938             AND table_name = $2
2939             and column_name = $3
2940         )' INTO proceed USING table_schema, table_a, column_a;
2941         IF NOT proceed THEN
2942             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2943         END IF;
2944
2945         EXECUTE 'SELECT EXISTS (
2946             SELECT 1
2947             FROM information_schema.columns
2948             WHERE table_schema = $1
2949             AND table_name = $2
2950             and column_name = $3
2951         )' INTO proceed USING table_schema, table_b, column_b;
2952         IF NOT proceed THEN
2953             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2954         END IF;
2955
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';
2962
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) || ')';
2968         ELSE
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);
2973         END IF;
2974
2975     END;
2976 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2977
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 $$
2981     DECLARE
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;
2989         proceed BOOLEAN;
2990     BEGIN
2991         EXECUTE 'SELECT EXISTS (
2992             SELECT 1
2993             FROM information_schema.columns
2994             WHERE table_schema = $1
2995             AND table_name = $2
2996             and column_name = $3
2997         )' INTO proceed USING table_schema, table_a, column_a;
2998         IF NOT proceed THEN
2999             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3000         END IF;
3001
3002         EXECUTE 'SELECT EXISTS (
3003             SELECT 1
3004             FROM information_schema.columns
3005             WHERE table_schema = $1
3006             AND table_name = $2
3007             and column_name = $3
3008         )' INTO proceed USING table_schema, table_b, column_b;
3009         IF NOT proceed THEN
3010             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3011         END IF;
3012
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);
3017
3018     END;
3019 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3020
3021 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3022     DECLARE
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;
3030         proceed BOOLEAN;
3031     BEGIN
3032         EXECUTE 'SELECT EXISTS (
3033             SELECT 1
3034             FROM information_schema.columns
3035             WHERE table_schema = $1
3036             AND table_name = $2
3037             and column_name = $3
3038         )' INTO proceed USING table_schema, table_a, column_a;
3039         IF NOT proceed THEN
3040             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3041         END IF;
3042
3043         EXECUTE 'SELECT EXISTS (
3044             SELECT 1
3045             FROM information_schema.columns
3046             WHERE table_schema = $1
3047             AND table_name = $2
3048             and column_name = $3
3049         )' INTO proceed USING table_schema, table_b, column_b;
3050         IF NOT proceed THEN
3051             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3052         END IF;
3053
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';
3059
3060     END;
3061 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3062
3063 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3064     DECLARE
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;
3072         proceed BOOLEAN;
3073     BEGIN
3074         EXECUTE 'SELECT EXISTS (
3075             SELECT 1
3076             FROM information_schema.columns
3077             WHERE table_schema = $1
3078             AND table_name = $2
3079             and column_name = $3
3080         )' INTO proceed USING table_schema, table_a, column_a;
3081         IF NOT proceed THEN
3082             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3083         END IF;
3084
3085         EXECUTE 'SELECT EXISTS (
3086             SELECT 1
3087             FROM information_schema.columns
3088             WHERE table_schema = $1
3089             AND table_name = $2
3090             and column_name = $3
3091         )' INTO proceed USING table_schema, table_b, column_b;
3092         IF NOT proceed THEN
3093             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3094         END IF;
3095
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';
3101
3102     END;
3103 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3104
3105 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3106     DECLARE
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;
3114         proceed BOOLEAN;
3115     BEGIN
3116         EXECUTE 'SELECT EXISTS (
3117             SELECT 1
3118             FROM information_schema.columns
3119             WHERE table_schema = $1
3120             AND table_name = $2
3121             and column_name = $3
3122         )' INTO proceed USING table_schema, table_a, column_a;
3123         IF NOT proceed THEN
3124             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3125         END IF;
3126
3127         EXECUTE 'SELECT EXISTS (
3128             SELECT 1
3129             FROM information_schema.columns
3130             WHERE table_schema = $1
3131             AND table_name = $2
3132             and column_name = $3
3133         )' INTO proceed USING table_schema, table_b, column_b;
3134         IF NOT proceed THEN
3135             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3136         END IF;
3137
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';
3143
3144     END;
3145 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3146
3147 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3148     DECLARE
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;
3156         proceed BOOLEAN;
3157     BEGIN
3158         EXECUTE 'SELECT EXISTS (
3159             SELECT 1
3160             FROM information_schema.columns
3161             WHERE table_schema = $1
3162             AND table_name = $2
3163             and column_name = $3
3164         )' INTO proceed USING table_schema, table_a, column_a;
3165         IF NOT proceed THEN
3166             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3167         END IF;
3168
3169         EXECUTE 'SELECT EXISTS (
3170             SELECT 1
3171             FROM information_schema.columns
3172             WHERE table_schema = $1
3173             AND table_name = $2
3174             and column_name = $3
3175         )' INTO proceed USING table_schema, table_b, column_b;
3176         IF NOT proceed THEN
3177             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3178         END IF;
3179
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';
3185
3186     END;
3187 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3188
3189 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3190     DECLARE
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;
3198         proceed BOOLEAN;
3199     BEGIN
3200         EXECUTE 'SELECT EXISTS (
3201             SELECT 1
3202             FROM information_schema.columns
3203             WHERE table_schema = $1
3204             AND table_name = $2
3205             and column_name = $3
3206         )' INTO proceed USING table_schema, table_a, column_a;
3207         IF NOT proceed THEN
3208             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
3209         END IF;
3210
3211         EXECUTE 'SELECT EXISTS (
3212             SELECT 1
3213             FROM information_schema.columns
3214             WHERE table_schema = $1
3215             AND table_name = $2
3216             and column_name = $3
3217         )' INTO proceed USING table_schema, table_b, column_b;
3218         IF NOT proceed THEN
3219             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
3220         END IF;
3221
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';
3227
3228     END;
3229 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3230
3231 -- convenience function for handling desired asset stat cats
3232
3233 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3234     DECLARE
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;
3239         proceed BOOLEAN;
3240         org INTEGER;
3241         org_list INTEGER[];
3242         sc TEXT;
3243         sce TEXT;
3244     BEGIN
3245
3246         SELECT 'desired_sc' || field_suffix INTO sc;
3247         SELECT 'desired_sce' || field_suffix INTO sce;
3248
3249         EXECUTE 'SELECT EXISTS (
3250             SELECT 1
3251             FROM information_schema.columns
3252             WHERE table_schema = $1
3253             AND table_name = $2
3254             and column_name = $3
3255         )' INTO proceed USING table_schema, table_name, sc;
3256         IF NOT proceed THEN
3257             RAISE EXCEPTION 'Missing column %', sc;
3258         END IF;
3259         EXECUTE 'SELECT EXISTS (
3260             SELECT 1
3261             FROM information_schema.columns
3262             WHERE table_schema = $1
3263             AND table_name = $2
3264             and column_name = $3
3265         )' INTO proceed USING table_schema, table_name, sce;
3266         IF NOT proceed THEN
3267             RAISE EXCEPTION 'Missing column %', sce;
3268         END IF;
3269
3270         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3271         IF org IS NULL THEN
3272             RAISE EXCEPTION 'Cannot find org by shortname';
3273         END IF;
3274         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3275
3276         -- caller responsible for their own truncates though we try to prevent duplicates
3277         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
3278             SELECT DISTINCT
3279                  $1
3280                 ,BTRIM('||sc||')
3281             FROM
3282                 ' || quote_ident(table_name) || '
3283             WHERE
3284                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3285                 AND NOT EXISTS (
3286                     SELECT id
3287                     FROM asset.stat_cat
3288                     WHERE owner = ANY ($2)
3289                     AND name = BTRIM('||sc||')
3290                 )
3291                 AND NOT EXISTS (
3292                     SELECT id
3293                     FROM asset_stat_cat
3294                     WHERE owner = ANY ($2)
3295                     AND name = BTRIM('||sc||')
3296                 )
3297             ORDER BY 2;'
3298         USING org, org_list;
3299
3300         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
3301             SELECT DISTINCT
3302                 COALESCE(
3303                     (SELECT id
3304                         FROM asset.stat_cat
3305                         WHERE owner = ANY ($2)
3306                         AND BTRIM('||sc||') = BTRIM(name))
3307                    ,(SELECT id
3308                         FROM asset_stat_cat
3309                         WHERE owner = ANY ($2)
3310                         AND BTRIM('||sc||') = BTRIM(name))
3311                 )
3312                 ,$1
3313                 ,BTRIM('||sce||')
3314             FROM
3315                 ' || quote_ident(table_name) || '
3316             WHERE
3317                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3318                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3319                 AND NOT EXISTS (
3320                     SELECT id
3321                     FROM asset.stat_cat_entry
3322                     WHERE stat_cat = (
3323                         SELECT id
3324                         FROM asset.stat_cat
3325                         WHERE owner = ANY ($2)
3326                         AND BTRIM('||sc||') = BTRIM(name)
3327                     ) AND value = BTRIM('||sce||')
3328                     AND owner = ANY ($2)
3329                 )
3330                 AND NOT EXISTS (
3331                     SELECT id
3332                     FROM asset_stat_cat_entry
3333                     WHERE stat_cat = (
3334                         SELECT id
3335                         FROM asset_stat_cat
3336                         WHERE owner = ANY ($2)
3337                         AND BTRIM('||sc||') = BTRIM(name)
3338                     ) AND value = BTRIM('||sce||')
3339                     AND owner = ANY ($2)
3340                 )
3341             ORDER BY 1,3;'
3342         USING org, org_list;
3343     END;
3344 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3345
3346 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3347     DECLARE
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;
3352         proceed BOOLEAN;
3353         org INTEGER;
3354         org_list INTEGER[];
3355         o INTEGER;
3356         sc TEXT;
3357         sce TEXT;
3358     BEGIN
3359         SELECT 'desired_sc' || field_suffix INTO sc;
3360         SELECT 'desired_sce' || field_suffix INTO sce;
3361         EXECUTE 'SELECT EXISTS (
3362             SELECT 1
3363             FROM information_schema.columns
3364             WHERE table_schema = $1
3365             AND table_name = $2
3366             and column_name = $3
3367         )' INTO proceed USING table_schema, table_name, sc;
3368         IF NOT proceed THEN
3369             RAISE EXCEPTION 'Missing column %', sc;
3370         END IF;
3371         EXECUTE 'SELECT EXISTS (
3372             SELECT 1
3373             FROM information_schema.columns
3374             WHERE table_schema = $1
3375             AND table_name = $2
3376             and column_name = $3
3377         )' INTO proceed USING table_schema, table_name, sce;
3378         IF NOT proceed THEN
3379             RAISE EXCEPTION 'Missing column %', sce;
3380         END IF;
3381
3382         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3383         IF org IS NULL THEN
3384             RAISE EXCEPTION 'Cannot find org by shortname';
3385         END IF;
3386
3387         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3388
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';
3401
3402
3403         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3404             SET
3405                 x_sc' || field_suffix || ' = id
3406             FROM
3407                 (SELECT id, name, owner FROM asset_stat_cat
3408                     UNION SELECT id, name, owner FROM asset.stat_cat) u
3409             WHERE
3410                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
3411                 AND u.owner = ANY ($1);'
3412         USING org_list;
3413
3414         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
3415             SET
3416                 x_sce' || field_suffix || ' = id
3417             FROM
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
3420             WHERE
3421                     u.stat_cat = x_sc' || field_suffix || '
3422                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
3423                 AND u.owner = ANY ($1);'
3424         USING org_list;
3425
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''
3430         );';
3431
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''
3436         );';
3437
3438     END;
3439 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3440
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
3443  LANGUAGE plpgsql
3444 AS $function$
3445 DECLARE
3446     c_name     TEXT;
3447 BEGIN
3448
3449     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3450             table_name = t_name
3451             AND table_schema = s_name
3452             AND (data_type='text' OR data_type='character varying')
3453             AND column_name like 'l_%'
3454     LOOP
3455        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3456     END LOOP;
3457
3458     RETURN TRUE;
3459 END
3460 $function$;
3461
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
3464  LANGUAGE plpgsql
3465 AS $function$
3466 DECLARE
3467     c_name     TEXT;
3468 BEGIN
3469
3470     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3471             table_name = t_name
3472             AND table_schema = s_name
3473             AND (data_type='text' OR data_type='character varying')
3474     LOOP
3475        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
3476     END LOOP;
3477
3478     RETURN TRUE;
3479 END
3480 $function$;
3481
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
3484  LANGUAGE plpgsql
3485 AS $function$
3486 DECLARE
3487     c_name     TEXT;
3488 BEGIN
3489
3490     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3491             table_name = t_name
3492             AND table_schema = s_name
3493             AND (data_type='text' OR data_type='character varying')
3494             AND column_name like 'l_%'
3495     LOOP
3496        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3497     END LOOP;
3498
3499     RETURN TRUE;
3500 END
3501 $function$;
3502
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
3505  LANGUAGE plpgsql
3506 AS $function$
3507 DECLARE
3508     c_name     TEXT;
3509 BEGIN
3510
3511     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
3512             table_name = t_name
3513             AND table_schema = s_name
3514             AND (data_type='text' OR data_type='character varying')
3515     LOOP
3516        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
3517     END LOOP;
3518
3519     RETURN TRUE;
3520 END
3521 $function$;
3522
3523
3524 -- convenience function for handling item barcode collisions in asset_copy_legacy
3525
3526 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
3527 DECLARE
3528     x_barcode TEXT;
3529     x_id BIGINT;
3530     row_count NUMERIC;
3531     internal_collision_count NUMERIC := 0;
3532     incumbent_collision_count NUMERIC := 0;
3533 BEGIN
3534     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
3535     LOOP
3536         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
3537         LOOP
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;
3541         END LOOP;
3542     END LOOP;
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
3545     LOOP
3546         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
3547         LOOP
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;
3551         END LOOP;
3552     END LOOP;
3553     RAISE INFO '% incumbent collisions', incumbent_collision_count;
3554 END
3555 $function$ LANGUAGE plpgsql;
3556
3557 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
3558 -- this should be ran prior to populating actor_card
3559
3560 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
3561 DECLARE
3562     x_barcode TEXT;
3563     x_id BIGINT;
3564     row_count NUMERIC;
3565     internal_collision_count NUMERIC := 0;
3566     incumbent_barcode_collision_count NUMERIC := 0;
3567     incumbent_usrname_collision_count NUMERIC := 0;
3568 BEGIN
3569     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
3570     LOOP
3571         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3572         LOOP
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;
3576         END LOOP;
3577     END LOOP;
3578     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
3579
3580     FOR x_barcode IN
3581         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
3582     LOOP
3583         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3584         LOOP
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;
3588         END LOOP;
3589     END LOOP;
3590     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
3591
3592     FOR x_barcode IN
3593         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
3594     LOOP
3595         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
3596         LOOP
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;
3600         END LOOP;
3601     END LOOP;
3602     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
3603 END
3604 $function$ LANGUAGE plpgsql;