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.is_staff_profile (INT) RETURNS BOOLEAN AS $$
  25   DECLARE
  26     profile ALIAS FOR $1;
  27   BEGIN
  28     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
  29   END;
  30 $$ LANGUAGE PLPGSQL STRICT STABLE;
  31
  32
  33 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
  34   BEGIN
  35     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
  36   END;
  37 $$ LANGUAGE PLPGSQL STRICT STABLE;
  38
  39
  40 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
  41
  42 -- Usage:
  43 --
  44 --   First make sure the circ matrix is loaded and the circulations
  45 --   have been staged to the extent possible (but at the very least
  46 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
  47 --   circ modifiers must also be in place.
  48 --
  49 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
  50 --
  51
  52 DECLARE
  53   circ_lib             INT;
  54   target_copy          INT;
  55   usr                  INT;
  56   is_renewal           BOOLEAN;
  57   this_duration_rule   INT;
  58   this_fine_rule       INT;
  59   this_max_fine_rule   INT;
  60   rcd                  config.rule_circ_duration%ROWTYPE;
  61   rrf                  config.rule_recurring_fine%ROWTYPE;
  62   rmf                  config.rule_max_fine%ROWTYPE;
  63   circ                 INT;
  64   n                    INT := 0;
  65   n_circs              INT;
  66
  67 BEGIN
  68
  69   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
  70
  71   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
  72
  73     -- Fetch the correct rules for this circulation
  74     EXECUTE ('
  75       SELECT
  76         circ_lib,
  77         target_copy,
  78         usr,
  79         CASE
  80           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
  81           ELSE FALSE
  82         END
  83       FROM ' || tablename || ' WHERE id = ' || circ || ';')
  84       INTO circ_lib, target_copy, usr, is_renewal ;
  85     SELECT
  86       INTO this_duration_rule,
  87            this_fine_rule,
  88            this_max_fine_rule
  89       duration_rule,
  90       recurring_fine_rule,
  91       max_fine_rule
  92       FROM action.item_user_circ_test(
  93         circ_lib,
  94         target_copy,
  95         usr,
  96         is_renewal
  97         );
  98     SELECT INTO rcd * FROM config.rule_circ_duration
  99       WHERE id = this_duration_rule;
 100     SELECT INTO rrf * FROM config.rule_recurring_fine
 101       WHERE id = this_fine_rule;
 102     SELECT INTO rmf * FROM config.rule_max_fine
 103       WHERE id = this_max_fine_rule;
 104
 105     -- Apply the rules to this circulation
 106     EXECUTE ('UPDATE ' || tablename || ' c
 107     SET
 108       duration_rule = rcd.name,
 109       recurring_fine_rule = rrf.name,
 110       max_fine_rule = rmf.name,
 111       duration = rcd.normal,
 112       recurring_fine = rrf.normal,
 113       max_fine =
 114         CASE rmf.is_percent
 115           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 116           ELSE rmf.amount
 117         END,
 118       renewal_remaining = rcd.max_renewals
 119     FROM
 120       config.rule_circ_duration rcd,
 121       config.rule_recurring_fine rrf,
 122       config.rule_max_fine rmf,
 123                         asset.copy ac
 124     WHERE
 125       rcd.id = ' || this_duration_rule || ' AND
 126       rrf.id = ' || this_fine_rule || ' AND
 127       rmf.id = ' || this_max_fine_rule || ' AND
 128                         ac.id = c.target_copy AND
 129       c.id = ' || circ || ';');
 130
 131     -- Keep track of where we are in the process
 132     n := n + 1;
 133     IF (n % 100 = 0) THEN
 134       RAISE INFO '%', n || ' of ' || n_circs
 135         || ' (' || (100*n/n_circs) || '%) circs updated.';
 136     END IF;
 137
 138   END LOOP;
 139
 140   RETURN;
 141 END;
 142
 143 $$ LANGUAGE plpgsql;
 144
 145 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
 146
 147 -- Usage:
 148 --
 149 --   First make sure the circ matrix is loaded and the circulations
 150 --   have been staged to the extent possible (but at the very least
 151 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 152 --   circ modifiers must also be in place.
 153 --
 154 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 155 --
 156
 157 DECLARE
 158   circ_lib             INT;
 159   target_copy          INT;
 160   usr                  INT;
 161   is_renewal           BOOLEAN;
 162   this_duration_rule   INT;
 163   this_fine_rule       INT;
 164   this_max_fine_rule   INT;
 165   rcd                  config.rule_circ_duration%ROWTYPE;
 166   rrf                  config.rule_recurring_fine%ROWTYPE;
 167   rmf                  config.rule_max_fine%ROWTYPE;
 168   circ                 INT;
 169   n                    INT := 0;
 170   n_circs              INT;
 171
 172 BEGIN
 173
 174   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 175
 176   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 177
 178     -- Fetch the correct rules for this circulation
 179     EXECUTE ('
 180       SELECT
 181         circ_lib,
 182         target_copy,
 183         usr,
 184         CASE
 185           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 186           ELSE FALSE
 187         END
 188       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 189       INTO circ_lib, target_copy, usr, is_renewal ;
 190     SELECT
 191       INTO this_duration_rule,
 192            this_fine_rule,
 193            this_max_fine_rule
 194       duration_rule,
 195       recuring_fine_rule,
 196       max_fine_rule
 197       FROM action.find_circ_matrix_matchpoint(
 198         circ_lib,
 199         target_copy,
 200         usr,
 201         is_renewal
 202         );
 203     SELECT INTO rcd * FROM config.rule_circ_duration
 204       WHERE id = this_duration_rule;
 205     SELECT INTO rrf * FROM config.rule_recurring_fine
 206       WHERE id = this_fine_rule;
 207     SELECT INTO rmf * FROM config.rule_max_fine
 208       WHERE id = this_max_fine_rule;
 209
 210     -- Apply the rules to this circulation
 211     EXECUTE ('UPDATE ' || tablename || ' c
 212     SET
 213       duration_rule = rcd.name,
 214       recuring_fine_rule = rrf.name,
 215       max_fine_rule = rmf.name,
 216       duration = rcd.normal,
 217       recuring_fine = rrf.normal,
 218       max_fine =
 219         CASE rmf.is_percent
 220           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 221           ELSE rmf.amount
 222         END,
 223       renewal_remaining = rcd.max_renewals
 224     FROM
 225       config.rule_circ_duration rcd,
 226       config.rule_recuring_fine rrf,
 227       config.rule_max_fine rmf,
 228                         asset.copy ac
 229     WHERE
 230       rcd.id = ' || this_duration_rule || ' AND
 231       rrf.id = ' || this_fine_rule || ' AND
 232       rmf.id = ' || this_max_fine_rule || ' AND
 233                         ac.id = c.target_copy AND
 234       c.id = ' || circ || ';');
 235
 236     -- Keep track of where we are in the process
 237     n := n + 1;
 238     IF (n % 100 = 0) THEN
 239       RAISE INFO '%', n || ' of ' || n_circs
 240         || ' (' || (100*n/n_circs) || '%) circs updated.';
 241     END IF;
 242
 243   END LOOP;
 244
 245   RETURN;
 246 END;
 247
 248 $$ LANGUAGE plpgsql;
 249
 250 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
 251
 252 -- Usage:
 253 --
 254 --   First make sure the circ matrix is loaded and the circulations
 255 --   have been staged to the extent possible (but at the very least
 256 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 257 --   circ modifiers must also be in place.
 258 --
 259 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
 260 --
 261
 262 DECLARE
 263   circ_lib             INT;
 264   target_copy          INT;
 265   usr                  INT;
 266   is_renewal           BOOLEAN;
 267   this_duration_rule   INT;
 268   this_fine_rule       INT;
 269   this_max_fine_rule   INT;
 270   rcd                  config.rule_circ_duration%ROWTYPE;
 271   rrf                  config.rule_recurring_fine%ROWTYPE;
 272   rmf                  config.rule_max_fine%ROWTYPE;
 273   circ                 INT;
 274   n                    INT := 0;
 275   n_circs              INT;
 276
 277 BEGIN
 278
 279   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 280
 281   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 282
 283     -- Fetch the correct rules for this circulation
 284     EXECUTE ('
 285       SELECT
 286         circ_lib,
 287         target_copy,
 288         usr,
 289         CASE
 290           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 291           ELSE FALSE
 292         END
 293       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 294       INTO circ_lib, target_copy, usr, is_renewal ;
 295     SELECT
 296       INTO this_duration_rule,
 297            this_fine_rule,
 298            this_max_fine_rule
 299       (matchpoint).duration_rule,
 300       (matchpoint).recurring_fine_rule,
 301       (matchpoint).max_fine_rule
 302       FROM action.find_circ_matrix_matchpoint(
 303         circ_lib,
 304         target_copy,
 305         usr,
 306         is_renewal
 307         );
 308     SELECT INTO rcd * FROM config.rule_circ_duration
 309       WHERE id = this_duration_rule;
 310     SELECT INTO rrf * FROM config.rule_recurring_fine
 311       WHERE id = this_fine_rule;
 312     SELECT INTO rmf * FROM config.rule_max_fine
 313       WHERE id = this_max_fine_rule;
 314
 315     -- Apply the rules to this circulation
 316     EXECUTE ('UPDATE ' || tablename || ' c
 317     SET
 318       duration_rule = rcd.name,
 319       recurring_fine_rule = rrf.name,
 320       max_fine_rule = rmf.name,
 321       duration = rcd.normal,
 322       recurring_fine = rrf.normal,
 323       max_fine =
 324         CASE rmf.is_percent
 325           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
 326           ELSE rmf.amount
 327         END,
 328       renewal_remaining = rcd.max_renewals,
 329       grace_period = rrf.grace_period
 330     FROM
 331       config.rule_circ_duration rcd,
 332       config.rule_recurring_fine rrf,
 333       config.rule_max_fine rmf,
 334                         asset.copy ac
 335     WHERE
 336       rcd.id = ' || this_duration_rule || ' AND
 337       rrf.id = ' || this_fine_rule || ' AND
 338       rmf.id = ' || this_max_fine_rule || ' AND
 339                         ac.id = c.target_copy AND
 340       c.id = ' || circ || ';');
 341
 342     -- Keep track of where we are in the process
 343     n := n + 1;
 344     IF (n % 100 = 0) THEN
 345       RAISE INFO '%', n || ' of ' || n_circs
 346         || ' (' || (100*n/n_circs) || '%) circs updated.';
 347     END IF;
 348
 349   END LOOP;
 350
 351   RETURN;
 352 END;
 353
 354 $$ LANGUAGE plpgsql;
 355
 356 -- TODO: make another version of the procedure below that can work with specified copy staging tables
 357 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
 358 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
 359 DECLARE
 360     context_lib             INT;
 361     charge_lost_on_zero     BOOLEAN;
 362     min_price               NUMERIC;
 363     max_price               NUMERIC;
 364     default_price           NUMERIC;
 365     working_price           NUMERIC;
 366
 367 BEGIN
 368
 369     SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
 370         FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
 371
 372     SELECT INTO charge_lost_on_zero value
 373         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
 374
 375     SELECT INTO min_price value
 376         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
 377
 378     SELECT INTO max_price value
 379         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
 380
 381     SELECT INTO default_price value
 382         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
 383
 384     SELECT INTO working_price price FROM asset.copy WHERE id = item;
 385
 386     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
 387         working_price := default_price;
 388     END IF;
 389
 390     IF (max_price IS NOT NULL AND working_price > max_price) THEN
 391         working_price := max_price;
 392     END IF;
 393
 394     IF (min_price IS NOT NULL AND working_price < min_price) THEN
 395         IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
 396             working_price := min_price;
 397         END IF;
 398     END IF;
 399
 400     RETURN working_price;
 401
 402 END;
 403
 404 $$ LANGUAGE plpgsql;
 405
 406 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
 407
 408 -- Usage:
 409 --
 410 --   First make sure the circ matrix is loaded and the circulations
 411 --   have been staged to the extent possible (but at the very least
 412 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
 413 --   circ modifiers must also be in place.
 414 --
 415 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
 416 --
 417
 418 DECLARE
 419   circ_lib             INT;
 420   target_copy          INT;
 421   usr                  INT;
 422   is_renewal           BOOLEAN;
 423   this_duration_rule   INT;
 424   this_fine_rule       INT;
 425   this_max_fine_rule   INT;
 426   rcd                  config.rule_circ_duration%ROWTYPE;
 427   rrf                  config.rule_recurring_fine%ROWTYPE;
 428   rmf                  config.rule_max_fine%ROWTYPE;
 429   n                    INT := 0;
 430   n_circs              INT := 1;
 431
 432 BEGIN
 433
 434   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
 435
 436   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
 437
 438     -- Fetch the correct rules for this circulation
 439     EXECUTE ('
 440       SELECT
 441         circ_lib,
 442         target_copy,
 443         usr,
 444         CASE
 445           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
 446           ELSE FALSE
 447         END
 448       FROM ' || tablename || ' WHERE id = ' || circ || ';')
 449       INTO circ_lib, target_copy, usr, is_renewal ;
 450     SELECT
 451       INTO this_duration_rule,
 452            this_fine_rule,
 453            this_max_fine_rule
 454       (matchpoint).duration_rule,
 455       (matchpoint).recurring_fine_rule,
 456       (matchpoint).max_fine_rule
 457       FROM action.find_circ_matrix_matchpoint(
 458         circ_lib,
 459         target_copy,
 460         usr,
 461         is_renewal
 462         );
 463     SELECT INTO rcd * FROM config.rule_circ_duration
 464       WHERE id = this_duration_rule;
 465     SELECT INTO rrf * FROM config.rule_recurring_fine
 466       WHERE id = this_fine_rule;
 467     SELECT INTO rmf * FROM config.rule_max_fine
 468       WHERE id = this_max_fine_rule;
 469
 470     -- Apply the rules to this circulation
 471     EXECUTE ('UPDATE ' || tablename || ' c
 472     SET
 473       duration_rule = rcd.name,
 474       recurring_fine_rule = rrf.name,
 475       max_fine_rule = rmf.name,
 476       duration = rcd.normal,
 477       recurring_fine = rrf.normal,
 478       max_fine =
 479         CASE rmf.is_percent
 480           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
 481           ELSE rmf.amount
 482         END,
 483       renewal_remaining = rcd.max_renewals,
 484       grace_period = rrf.grace_period
 485     FROM
 486       config.rule_circ_duration rcd,
 487       config.rule_recurring_fine rrf,
 488       config.rule_max_fine rmf,
 489                         asset.copy ac
 490     WHERE
 491       rcd.id = ' || this_duration_rule || ' AND
 492       rrf.id = ' || this_fine_rule || ' AND
 493       rmf.id = ' || this_max_fine_rule || ' AND
 494                         ac.id = c.target_copy AND
 495       c.id = ' || circ || ';');
 496
 497     -- Keep track of where we are in the process
 498     n := n + 1;
 499     IF (n % 100 = 0) THEN
 500       RAISE INFO '%', n || ' of ' || n_circs
 501         || ' (' || (100*n/n_circs) || '%) circs updated.';
 502     END IF;
 503
 504   --END LOOP;
 505
 506   RETURN;
 507 END;
 508
 509 $$ LANGUAGE plpgsql;
 510
 511
 512
 513
 514 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
 515
 516 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
 517 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
 518
 519 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
 520 -- TODO: Add a similar tool for actor stat cats, which behave differently.
 521
 522 DECLARE
 523         c                    TEXT := schemaname || '.asset_copy_legacy';
 524         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
 525         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
 526         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
 527         stat_cat                                                 INT;
 528   stat_cat_entry       INT;
 529
 530 BEGIN
 531
 532   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
 533
 534                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
 535
 536                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
 537                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
 538                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
 539
 540   END LOOP;
 541
 542   RETURN;
 543 END;
 544
 545 $$ LANGUAGE plpgsql;
 546
 547 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
 548
 549 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
 550 --        This will assign standing penalties as needed.
 551
 552 DECLARE
 553   org_unit  INT;
 554   usr       INT;
 555
 556 BEGIN
 557
 558   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
 559
 560     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
 561
 562       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
 563
 564     END LOOP;
 565
 566   END LOOP;
 567
 568   RETURN;
 569
 570 END;
 571
 572 $$ LANGUAGE plpgsql;
 573
 574
 575 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
 576
 577 BEGIN
 578   INSERT INTO metabib.metarecord (fingerprint, master_record)
 579     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
 580       FROM  biblio.record_entry b
 581       WHERE NOT b.deleted
 582         AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
 583         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
 584       ORDER BY b.fingerprint, b.quality DESC;
 585   INSERT INTO metabib.metarecord_source_map (metarecord, source)
 586     SELECT  m.id, r.id
 587       FROM  biblio.record_entry r
 588       JOIN  metabib.metarecord m USING (fingerprint)
 589      WHERE  NOT r.deleted;
 590 END;
 591
 592 $$ LANGUAGE plpgsql;
 593
 594
 595 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
 596
 597 BEGIN
 598   INSERT INTO metabib.metarecord (fingerprint, master_record)
 599     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
 600       FROM  biblio.record_entry b
 601       WHERE NOT b.deleted
 602         AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
 603         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
 604       ORDER BY b.fingerprint, b.quality DESC;
 605   INSERT INTO metabib.metarecord_source_map (metarecord, source)
 606     SELECT  m.id, r.id
 607       FROM  biblio.record_entry r
 608         JOIN metabib.metarecord m USING (fingerprint)
 609       WHERE NOT r.deleted
 610         AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
 611 END;
 612
 613 $$ LANGUAGE plpgsql;
 614
 615
 616 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
 617
 618 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
 619 --        Then SELECT migration_tools.create_cards('m_foo');
 620
 621 DECLARE
 622         u                    TEXT := schemaname || '.actor_usr_legacy';
 623         c                    TEXT := schemaname || '.actor_card';
 624
 625 BEGIN
 626
 627         EXECUTE ('DELETE FROM ' || c || ';');
 628         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
 629         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
 630
 631   RETURN;
 632
 633 END;
 634
 635 $$ LANGUAGE plpgsql;
 636
 637
 638 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
 639
 640   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 641
 642   my ($marcxml, $shortname) = @_;
 643
 644   use MARC::Record;
 645   use MARC::File::XML;
 646
 647   my $xml = $marcxml;
 648
 649   eval {
 650     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 651
 652     foreach my $field ( $marc->field('856') ) {
 653       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
 654            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
 655         $field->add_subfields( '9' => $shortname );
 656                                 $field->update( ind2 => '0');
 657       }
 658     }
 659
 660     $xml = $marc->as_xml_record;
 661     $xml =~ s/^<\?.+?\?>$//mo;
 662     $xml =~ s/\n//sgo;
 663     $xml =~ s/>\s+</></sgo;
 664   };
 665
 666   return $xml;
 667
 668 $$ LANGUAGE PLPERLU STABLE;
 669
 670 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
 671
 672   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 673
 674   my ($marcxml, $shortname) = @_;
 675
 676   use MARC::Record;
 677   use MARC::File::XML;
 678
 679   my $xml = $marcxml;
 680
 681   eval {
 682     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
 683
 684     foreach my $field ( $marc->field('856') ) {
 685       if ( ! $field->as_string('9') ) {
 686         $field->add_subfields( '9' => $shortname );
 687       }
 688     }
 689
 690     $xml = $marc->as_xml_record;
 691     $xml =~ s/^<\?.+?\?>$//mo;
 692     $xml =~ s/\n//sgo;
 693     $xml =~ s/>\s+</></sgo;
 694   };
 695
 696   return $xml;
 697
 698 $$ LANGUAGE PLPERLU STABLE;
 699
 700
 701 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
 702
 703 DECLARE
 704   old_volume   BIGINT;
 705   new_volume   BIGINT;
 706   bib          BIGINT;
 707   owner        INTEGER;
 708   old_label    TEXT;
 709   remainder    BIGINT;
 710
 711 BEGIN
 712
 713   -- Bail out if asked to change the label to ##URI##
 714   IF new_label = '##URI##' THEN
 715     RETURN;
 716   END IF;
 717
 718   -- Gather information
 719   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
 720   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
 721   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
 722
 723   -- Bail out if the label already is ##URI##
 724   IF old_label = '##URI##' THEN
 725     RETURN;
 726   END IF;
 727
 728   -- Bail out if the call number label is already correct
 729   IF new_volume = old_volume THEN
 730     RETURN;
 731   END IF;
 732
 733   -- Check whether we already have a destination volume available
 734   SELECT id INTO new_volume FROM asset.call_number
 735     WHERE
 736       record = bib AND
 737       owning_lib = owner AND
 738       label = new_label AND
 739       NOT deleted;
 740
 741   -- Create destination volume if needed
 742   IF NOT FOUND THEN
 743     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
 744       VALUES (1, 1, bib, owner, new_label, cn_class);
 745     SELECT id INTO new_volume FROM asset.call_number
 746       WHERE
 747         record = bib AND
 748         owning_lib = owner AND
 749         label = new_label AND
 750         NOT deleted;
 751   END IF;
 752
 753   -- Move copy to destination
 754   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
 755
 756   -- Delete source volume if it is now empty
 757   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
 758   IF NOT FOUND THEN
 759     DELETE FROM asset.call_number WHERE id = old_volume;
 760   END IF;
 761
 762 END;
 763
 764 $$ LANGUAGE plpgsql;
 765
 766 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
 767
 768         my $input = $_[0];
 769         my %zipdata;
 770
 771         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
 772
 773         while (<FH>) {
 774                 chomp;
 775                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
 776                 $zipdata{$zip} = [$city, $state, $county];
 777         }
 778
 779         if (defined $zipdata{$input}) {
 780                 my ($city, $state, $county) = @{$zipdata{$input}};
 781                 return [$city, $state, $county];
 782         } elsif (defined $zipdata{substr $input, 0, 5}) {
 783                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
 784                 return [$city, $state, $county];
 785         } else {
 786                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
 787         }
 788
 789 $$ LANGUAGE PLPERLU STABLE;
 790
 791 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
 792
 793 DECLARE
 794   ou  INT;
 795         org_unit_depth INT;
 796         ou_parent INT;
 797         parent_depth INT;
 798   errors_found BOOLEAN;
 799         ou_shortname TEXT;
 800         parent_shortname TEXT;
 801         ou_type_name TEXT;
 802         parent_type TEXT;
 803         type_id INT;
 804         type_depth INT;
 805         type_parent INT;
 806         type_parent_depth INT;
 807         proper_parent TEXT;
 808
 809 BEGIN
 810
 811         errors_found := FALSE;
 812
 813 -- Checking actor.org_unit_type
 814
 815         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
 816
 817                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
 818                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
 819
 820                 IF type_parent IS NOT NULL THEN
 821
 822                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
 823
 824                         IF type_depth - type_parent_depth <> 1 THEN
 825                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
 826                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
 827                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
 828                                         ou_type_name, type_depth, parent_type, type_parent_depth;
 829                                 errors_found := TRUE;
 830
 831                         END IF;
 832
 833                 END IF;
 834
 835         END LOOP;
 836
 837 -- Checking actor.org_unit
 838
 839   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
 840
 841                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
 842                 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
 843                 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
 844                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
 845                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
 846                 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
 847                 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
 848
 849                 IF ou_parent IS NOT NULL THEN
 850
 851                         IF      (org_unit_depth - parent_depth <> 1) OR (
 852                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
 853                         ) THEN
 854                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
 855                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
 856                                 errors_found := TRUE;
 857                         END IF;
 858
 859                 END IF;
 860
 861   END LOOP;
 862
 863         IF NOT errors_found THEN
 864                 RAISE INFO 'No errors found.';
 865         END IF;
 866
 867   RETURN;
 868
 869 END;
 870
 871 $$ LANGUAGE plpgsql;
 872
 873
 874 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
 875
 876 BEGIN
 877
 878         DELETE FROM asset.opac_visible_copies;
 879
 880         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
 881                 SELECT DISTINCT
 882                         cp.id, cp.circ_lib, cn.record
 883                 FROM
 884                         asset.copy cp
 885                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
 886                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
 887                         JOIN asset.copy_location cl ON (cp.location = cl.id)
 888                         JOIN config.copy_status cs ON (cp.status = cs.id)
 889                         JOIN biblio.record_entry b ON (cn.record = b.id)
 890                 WHERE
 891                         NOT cp.deleted AND
 892                         NOT cn.deleted AND
 893                         NOT b.deleted AND
 894                         cs.opac_visible AND
 895                         cl.opac_visible AND
 896                         cp.opac_visible AND
 897                         a.opac_visible AND
 898                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
 899
 900 END;
 901
 902 $$ LANGUAGE plpgsql;
 903
 904
 905 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
 906
 907 DECLARE
 908   old_volume     BIGINT;
 909   new_volume     BIGINT;
 910   bib            BIGINT;
 911   old_owning_lib INTEGER;
 912         old_label      TEXT;
 913   remainder      BIGINT;
 914
 915 BEGIN
 916
 917   -- Gather information
 918   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
 919   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
 920   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
 921
 922         -- Bail out if the new_owning_lib is not the ID of an org_unit
 923         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
 924                 RAISE WARNING
 925                         '% is not a valid actor.org_unit ID; no change made.',
 926                                 new_owning_lib;
 927                 RETURN;
 928         END IF;
 929
 930   -- Bail out discreetly if the owning_lib is already correct
 931   IF new_owning_lib = old_owning_lib THEN
 932     RETURN;
 933   END IF;
 934
 935   -- Check whether we already have a destination volume available
 936   SELECT id INTO new_volume FROM asset.call_number
 937     WHERE
 938       record = bib AND
 939       owning_lib = new_owning_lib AND
 940       label = old_label AND
 941       NOT deleted;
 942
 943   -- Create destination volume if needed
 944   IF NOT FOUND THEN
 945     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
 946       VALUES (1, 1, bib, new_owning_lib, old_label);
 947     SELECT id INTO new_volume FROM asset.call_number
 948       WHERE
 949         record = bib AND
 950         owning_lib = new_owning_lib AND
 951         label = old_label AND
 952         NOT deleted;
 953   END IF;
 954
 955   -- Move copy to destination
 956   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
 957
 958   -- Delete source volume if it is now empty
 959   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
 960   IF NOT FOUND THEN
 961     DELETE FROM asset.call_number WHERE id = old_volume;
 962   END IF;
 963
 964 END;
 965
 966 $$ LANGUAGE plpgsql;
 967
 968
 969 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
 970
 971 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
 972
 973 DECLARE
 974         new_owning_lib  INTEGER;
 975
 976 BEGIN
 977
 978         -- Parse the new_owner as an org unit ID or shortname
 979         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
 980                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
 981                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
 982         ELSIF new_owner ~ E'^[0-9]+$' THEN
 983                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
 984                         RAISE INFO
 985                                 '%',
 986                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
 987                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
 988                         new_owning_lib := new_owner::INTEGER;
 989                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
 990                 END IF;
 991         ELSE
 992                 RAISE WARNING
 993                         '% is not a valid actor.org_unit shortname or ID; no change made.',
 994                         new_owning_lib;
 995                 RETURN;
 996         END IF;
 997
 998 END;
 999
1000 $$ LANGUAGE plpgsql;
1001
1002 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1003 BEGIN
1004    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1005            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1006            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
1007    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1008            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1009            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
1010    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1011            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1012            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
1013    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1014            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1015            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
1016    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1017            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1018            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1019    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1020            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1021            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
1022    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1023            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1024            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
1025    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
1026    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
1027    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
1028    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
1029    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
1030 END;
1031 $FUNC$ LANGUAGE PLPGSQL;
1032
1033 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1034 BEGIN
1035    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
1036    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
1037    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
1038    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
1039    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
1040    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
1041    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
1042
1043    -- import any new circ rules
1044    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1045    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1046    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1047    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1048
1049    -- and permission groups
1050    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1051
1052 END;
1053 $FUNC$ LANGUAGE PLPGSQL;
1054
1055
1056 CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$
1057 DECLARE
1058     name TEXT;
1059     loopq TEXT;
1060     existsq TEXT;
1061     ct INTEGER;
1062     cols TEXT[];
1063     copyst TEXT;
1064 BEGIN
1065     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1066     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1067     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
1068     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1069     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1070     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1071     FOR name IN EXECUTE loopq LOOP
1072        EXECUTE existsq INTO ct USING name;
1073        IF ct = 0 THEN
1074            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1075            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1076                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1077            EXECUTE copyst USING name;
1078        END IF;
1079     END LOOP;
1080 END;
1081 $FUNC$ LANGUAGE PLPGSQL;
1082
1083 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
1084 DECLARE
1085     id BIGINT;
1086     loopq TEXT;
1087     cols TEXT[];
1088     splitst TEXT;
1089 BEGIN
1090     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
1091     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
1092     FOR id IN EXECUTE loopq USING delimiter LOOP
1093        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
1094        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
1095                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
1096        EXECUTE splitst USING id, delimiter;
1097     END LOOP;
1098 END;
1099 $FUNC$ LANGUAGE PLPGSQL;
1100
1101 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
1102 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
1103     DECLARE
1104         target_event_def ALIAS FOR $1;
1105         orgs ALIAS FOR $2;
1106     BEGIN
1107         DROP TABLE IF EXISTS new_atevdefs;
1108         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1109         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1110             INSERT INTO action_trigger.event_definition (
1111                 active
1112                 ,owner
1113                 ,name
1114                 ,hook
1115                 ,validator
1116                 ,reactor
1117                 ,cleanup_success
1118                 ,cleanup_failure
1119                 ,delay
1120                 ,max_delay
1121                 ,usr_field
1122                 ,opt_in_setting
1123                 ,delay_field
1124                 ,group_field
1125                 ,template
1126                 ,granularity
1127                 ,repeat_delay
1128             ) SELECT
1129                 'f'
1130                 ,orgs[i]
1131                 ,name || ' (clone of '||target_event_def||')'
1132                 ,hook
1133                 ,validator
1134                 ,reactor
1135                 ,cleanup_success
1136                 ,cleanup_failure
1137                 ,delay
1138                 ,max_delay
1139                 ,usr_field
1140                 ,opt_in_setting
1141                 ,delay_field
1142                 ,group_field
1143                 ,template
1144                 ,granularity
1145                 ,repeat_delay
1146             FROM
1147                 action_trigger.event_definition
1148             WHERE
1149                 id = target_event_def
1150             ;
1151             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1152             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1153             INSERT INTO action_trigger.environment (
1154                 event_def
1155                 ,path
1156                 ,collector
1157                 ,label
1158             ) SELECT
1159                 currval('action_trigger.event_definition_id_seq')
1160                 ,path
1161                 ,collector
1162                 ,label
1163             FROM
1164                 action_trigger.environment
1165             WHERE
1166                 event_def = target_event_def
1167             ;
1168             INSERT INTO action_trigger.event_params (
1169                 event_def
1170                 ,param
1171                 ,value
1172             ) SELECT
1173                 currval('action_trigger.event_definition_id_seq')
1174                 ,param
1175                 ,value
1176             FROM
1177                 action_trigger.event_params
1178             WHERE
1179                 event_def = target_event_def
1180             ;
1181         END LOOP;
1182         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1183     END;
1184 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1185
1186 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
1187 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
1188     DECLARE
1189         target_event_def ALIAS FOR $1;
1190         orgs ALIAS FOR $2;
1191         new_interval ALIAS FOR $3;
1192     BEGIN
1193         DROP TABLE IF EXISTS new_atevdefs;
1194         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1195         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
1196             INSERT INTO action_trigger.event_definition (
1197                 active
1198                 ,owner
1199                 ,name
1200                 ,hook
1201                 ,validator
1202                 ,reactor
1203                 ,cleanup_success
1204                 ,cleanup_failure
1205                 ,delay
1206                 ,max_delay
1207                 ,usr_field
1208                 ,opt_in_setting
1209                 ,delay_field
1210                 ,group_field
1211                 ,template
1212                 ,granularity
1213                 ,repeat_delay
1214             ) SELECT
1215                 'f'
1216                 ,orgs[i]
1217                 ,name || ' (clone of '||target_event_def||')'
1218                 ,hook
1219                 ,validator
1220                 ,reactor
1221                 ,cleanup_success
1222                 ,cleanup_failure
1223                 ,new_interval
1224                 ,max_delay
1225                 ,usr_field
1226                 ,opt_in_setting
1227                 ,delay_field
1228                 ,group_field
1229                 ,template
1230                 ,granularity
1231                 ,repeat_delay
1232             FROM
1233                 action_trigger.event_definition
1234             WHERE
1235                 id = target_event_def
1236             ;
1237             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1238             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1239             INSERT INTO action_trigger.environment (
1240                 event_def
1241                 ,path
1242                 ,collector
1243                 ,label
1244             ) SELECT
1245                 currval('action_trigger.event_definition_id_seq')
1246                 ,path
1247                 ,collector
1248                 ,label
1249             FROM
1250                 action_trigger.environment
1251             WHERE
1252                 event_def = target_event_def
1253             ;
1254             INSERT INTO action_trigger.event_params (
1255                 event_def
1256                 ,param
1257                 ,value
1258             ) SELECT
1259                 currval('action_trigger.event_definition_id_seq')
1260                 ,param
1261                 ,value
1262             FROM
1263                 action_trigger.event_params
1264             WHERE
1265                 event_def = target_event_def
1266             ;
1267         END LOOP;
1268         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1269     END;
1270 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1271
1272 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
1273 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
1274     DECLARE
1275         org ALIAS FOR $1;
1276         target_event_defs ALIAS FOR $2;
1277     BEGIN
1278         DROP TABLE IF EXISTS new_atevdefs;
1279         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
1280         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
1281             INSERT INTO action_trigger.event_definition (
1282                 active
1283                 ,owner
1284                 ,name
1285                 ,hook
1286                 ,validator
1287                 ,reactor
1288                 ,cleanup_success
1289                 ,cleanup_failure
1290                 ,delay
1291                 ,max_delay
1292                 ,usr_field
1293                 ,opt_in_setting
1294                 ,delay_field
1295                 ,group_field
1296                 ,template
1297                 ,granularity
1298                 ,repeat_delay
1299             ) SELECT
1300                 'f'
1301                 ,org
1302                 ,name || ' (clone of '||target_event_defs[i]||')'
1303                 ,hook
1304                 ,validator
1305                 ,reactor
1306                 ,cleanup_success
1307                 ,cleanup_failure
1308                 ,delay
1309                 ,max_delay
1310                 ,usr_field
1311                 ,opt_in_setting
1312                 ,delay_field
1313                 ,group_field
1314                 ,template
1315                 ,granularity
1316                 ,repeat_delay
1317             FROM
1318                 action_trigger.event_definition
1319             WHERE
1320                 id = target_event_defs[i]
1321             ;
1322             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
1323             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
1324             INSERT INTO action_trigger.environment (
1325                 event_def
1326                 ,path
1327                 ,collector
1328                 ,label
1329             ) SELECT
1330                 currval('action_trigger.event_definition_id_seq')
1331                 ,path
1332                 ,collector
1333                 ,label
1334             FROM
1335                 action_trigger.environment
1336             WHERE
1337                 event_def = target_event_defs[i]
1338             ;
1339             INSERT INTO action_trigger.event_params (
1340                 event_def
1341                 ,param
1342                 ,value
1343             ) SELECT
1344                 currval('action_trigger.event_definition_id_seq')
1345                 ,param
1346                 ,value
1347             FROM
1348                 action_trigger.event_params
1349             WHERE
1350                 event_def = target_event_defs[i]
1351             ;
1352         END LOOP;
1353         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
1354     END;
1355 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1356
1357 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
1358     UPDATE
1359         action_trigger.event
1360     SET
1361          start_time = NULL
1362         ,update_time = NULL
1363         ,complete_time = NULL
1364         ,update_process = NULL
1365         ,state = 'pending'
1366         ,template_output = NULL
1367         ,error_output = NULL
1368         ,async_output = NULL
1369     WHERE
1370         id = $1;
1371 $$ LANGUAGE SQL;
1372
1373 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
1374     SELECT action.find_hold_matrix_matchpoint(
1375         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1376         (SELECT request_lib FROM action.hold_request WHERE id = $1),
1377         (SELECT current_copy FROM action.hold_request WHERE id = $1),
1378         (SELECT usr FROM action.hold_request WHERE id = $1),
1379         (SELECT requestor FROM action.hold_request WHERE id = $1)
1380     );
1381 $$ LANGUAGE SQL;
1382
1383 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
1384     SELECT action.hold_request_permit_test(
1385         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
1386         (SELECT request_lib FROM action.hold_request WHERE id = $1),
1387         (SELECT current_copy FROM action.hold_request WHERE id = $1),
1388         (SELECT usr FROM action.hold_request WHERE id = $1),
1389         (SELECT requestor FROM action.hold_request WHERE id = $1)
1390     );
1391 $$ LANGUAGE SQL;
1392
1393 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
1394     SELECT action.find_circ_matrix_matchpoint(
1395         (SELECT circ_lib FROM action.circulation WHERE id = $1),
1396         (SELECT target_copy FROM action.circulation WHERE id = $1),
1397         (SELECT usr FROM action.circulation WHERE id = $1),
1398         (SELECT COALESCE(
1399                 NULLIF(phone_renewal,false),
1400                 NULLIF(desk_renewal,false),
1401                 NULLIF(opac_renewal,false),
1402                 false
1403             ) FROM action.circulation WHERE id = $1
1404         )
1405     );
1406 $$ LANGUAGE SQL;
1407
1408 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
1409     DECLARE
1410         test ALIAS FOR $1;
1411     BEGIN
1412         IF NOT test THEN
1413             RAISE EXCEPTION 'assertion';
1414         END IF;
1415     END;
1416 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1417
1418 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
1419     DECLARE
1420         test ALIAS FOR $1;
1421         msg ALIAS FOR $2;
1422     BEGIN
1423         IF NOT test THEN
1424             RAISE EXCEPTION '%', msg;
1425         END IF;
1426     END;
1427 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1428
1429 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
1430     DECLARE
1431         test ALIAS FOR $1;
1432         fail_msg ALIAS FOR $2;
1433         success_msg ALIAS FOR $3;
1434     BEGIN
1435         IF NOT test THEN
1436             RAISE EXCEPTION '%', fail_msg;
1437         END IF;
1438         RETURN success_msg;
1439     END;
1440 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1441
1442 -- push bib sequence and return starting value for reserved range
1443 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
1444     DECLARE
1445         bib_count ALIAS FOR $1;
1446         output BIGINT;
1447     BEGIN
1448         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
1449         FOR output IN
1450             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
1451         LOOP
1452             RETURN output;
1453         END LOOP;
1454     END;
1455 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1456
1457 -- set a new salted password
1458
1459 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
1460     DECLARE
1461         usr_id              ALIAS FOR $1;
1462         plain_passwd        ALIAS FOR $2;
1463         plain_salt          TEXT;
1464         md5_passwd          TEXT;
1465     BEGIN
1466
1467         SELECT actor.create_salt('main') INTO plain_salt;
1468
1469         SELECT MD5(plain_passwd) INTO md5_passwd;
1470
1471         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
1472
1473         RETURN TRUE;
1474
1475     END;
1476 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1477
1478
1479 -- convenience functions for handling copy_location maps
1480 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1481     SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
1482 $$ LANGUAGE SQL;
1483
1484 CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
1485     DECLARE
1486         table_schema ALIAS FOR $1;
1487         table_name ALIAS FOR $2;
1488         org_shortname ALIAS FOR $3;
1489         org_range ALIAS FOR $4;
1490         make_assertion ALIAS FOR $5;
1491         proceed BOOLEAN;
1492         org INTEGER;
1493         -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
1494         -- though we'll still use the passed org for the full path traversal when needed
1495         x_org_found BOOLEAN;
1496         x_org INTEGER;
1497         org_list INTEGER[];
1498         o INTEGER;
1499         row_count NUMERIC;
1500     BEGIN
1501         EXECUTE 'SELECT EXISTS (
1502             SELECT 1
1503             FROM information_schema.columns
1504             WHERE table_schema = $1
1505             AND table_name = $2
1506             and column_name = ''desired_shelf''
1507         )' INTO proceed USING table_schema, table_name;
1508         IF NOT proceed THEN
1509             RAISE EXCEPTION 'Missing column desired_shelf';
1510         END IF;
1511
1512         EXECUTE 'SELECT EXISTS (
1513             SELECT 1
1514             FROM information_schema.columns
1515             WHERE table_schema = $1
1516             AND table_name = $2
1517             and column_name = ''x_org''
1518         )' INTO x_org_found USING table_schema, table_name;
1519
1520         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1521         IF org IS NULL THEN
1522             RAISE EXCEPTION 'Cannot find org by shortname';
1523         END IF;
1524
1525         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1526
1527         EXECUTE 'ALTER TABLE '
1528             || quote_ident(table_name)
1529             || ' DROP COLUMN IF EXISTS x_shelf';
1530         EXECUTE 'ALTER TABLE '
1531             || quote_ident(table_name)
1532             || ' ADD COLUMN x_shelf INTEGER';
1533
1534         IF x_org_found THEN
1535             RAISE INFO 'Found x_org column';
1536             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1537                 || ' SET x_shelf = b.id FROM asset_copy_location b'
1538                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1539                 || ' AND b.owning_lib = x_org'
1540                 || ' AND NOT b.deleted';
1541             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1542                 || ' SET x_shelf = b.id FROM asset.copy_location b'
1543                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1544                 || ' AND b.owning_lib = x_org'
1545                 || ' AND x_shelf IS NULL'
1546                 || ' AND NOT b.deleted';
1547         ELSE
1548             RAISE INFO 'Did not find x_org column';
1549             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1550                 || ' SET x_shelf = b.id FROM asset_copy_location b'
1551                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1552                 || ' AND b.owning_lib = $1'
1553                 || ' AND NOT b.deleted'
1554             USING org;
1555             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1556                 || ' SET x_shelf = b.id FROM asset_copy_location b'
1557                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1558                 || ' AND b.owning_lib = $1'
1559                 || ' AND x_shelf IS NULL'
1560                 || ' AND NOT b.deleted'
1561             USING org;
1562         END IF;
1563
1564         FOREACH o IN ARRAY org_list LOOP
1565             RAISE INFO 'Considering org %', o;
1566             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1567                 || ' SET x_shelf = b.id FROM asset.copy_location b'
1568                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
1569                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
1570                 || ' AND NOT b.deleted'
1571             USING o;
1572             GET DIAGNOSTICS row_count = ROW_COUNT;
1573             RAISE INFO 'Updated % rows', row_count;
1574         END LOOP;
1575
1576         IF make_assertion THEN
1577             EXECUTE 'SELECT migration_tools.assert(
1578                 NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
1579                 ''Cannot find a desired location'',
1580                 ''Found all desired locations''
1581             );';
1582         END IF;
1583
1584     END;
1585 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1586
1587 -- convenience functions for handling circmod maps
1588
1589 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
1590     DECLARE
1591         table_schema ALIAS FOR $1;
1592         table_name ALIAS FOR $2;
1593         proceed BOOLEAN;
1594     BEGIN
1595         EXECUTE 'SELECT EXISTS (
1596             SELECT 1
1597             FROM information_schema.columns
1598             WHERE table_schema = $1
1599             AND table_name = $2
1600             and column_name = ''desired_circmod''
1601         )' INTO proceed USING table_schema, table_name;
1602         IF NOT proceed THEN
1603             RAISE EXCEPTION 'Missing column desired_circmod';
1604         END IF;
1605
1606         EXECUTE 'ALTER TABLE '
1607             || quote_ident(table_name)
1608             || ' DROP COLUMN IF EXISTS x_circmod';
1609         EXECUTE 'ALTER TABLE '
1610             || quote_ident(table_name)
1611             || ' ADD COLUMN x_circmod TEXT';
1612
1613         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1614             || ' SET x_circmod = code FROM config.circ_modifier b'
1615             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
1616
1617         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1618             || ' SET x_circmod = code FROM config.circ_modifier b'
1619             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
1620             || ' AND x_circmod IS NULL';
1621
1622         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1623             || ' SET x_circmod = code FROM config.circ_modifier b'
1624             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
1625             || ' AND x_circmod IS NULL';
1626
1627         EXECUTE 'SELECT migration_tools.assert(
1628             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
1629             ''Cannot find a desired circulation modifier'',
1630             ''Found all desired circulation modifiers''
1631         );';
1632
1633     END;
1634 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1635
1636 -- convenience functions for handling item status maps
1637
1638 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
1639     DECLARE
1640         table_schema ALIAS FOR $1;
1641         table_name ALIAS FOR $2;
1642         proceed BOOLEAN;
1643     BEGIN
1644         EXECUTE 'SELECT EXISTS (
1645             SELECT 1
1646             FROM information_schema.columns
1647             WHERE table_schema = $1
1648             AND table_name = $2
1649             and column_name = ''desired_status''
1650         )' INTO proceed USING table_schema, table_name;
1651         IF NOT proceed THEN
1652             RAISE EXCEPTION 'Missing column desired_status';
1653         END IF;
1654
1655         EXECUTE 'ALTER TABLE '
1656             || quote_ident(table_name)
1657             || ' DROP COLUMN IF EXISTS x_status';
1658         EXECUTE 'ALTER TABLE '
1659             || quote_ident(table_name)
1660             || ' ADD COLUMN x_status INTEGER';
1661
1662         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1663             || ' SET x_status = id FROM config.copy_status b'
1664             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
1665
1666         EXECUTE 'SELECT migration_tools.assert(
1667             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
1668             ''Cannot find a desired copy status'',
1669             ''Found all desired copy statuses''
1670         );';
1671
1672     END;
1673 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1674
1675 -- convenience functions for handling org maps
1676
1677 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
1678     DECLARE
1679         table_schema ALIAS FOR $1;
1680         table_name ALIAS FOR $2;
1681         proceed BOOLEAN;
1682     BEGIN
1683         EXECUTE 'SELECT EXISTS (
1684             SELECT 1
1685             FROM information_schema.columns
1686             WHERE table_schema = $1
1687             AND table_name = $2
1688             and column_name = ''desired_org''
1689         )' INTO proceed USING table_schema, table_name;
1690         IF NOT proceed THEN
1691             RAISE EXCEPTION 'Missing column desired_org';
1692         END IF;
1693
1694         EXECUTE 'ALTER TABLE '
1695             || quote_ident(table_name)
1696             || ' DROP COLUMN IF EXISTS x_org';
1697         EXECUTE 'ALTER TABLE '
1698             || quote_ident(table_name)
1699             || ' ADD COLUMN x_org INTEGER';
1700
1701         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1702             || ' SET x_org = b.id FROM actor.org_unit b'
1703             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
1704
1705         EXECUTE 'SELECT migration_tools.assert(
1706             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
1707             ''Cannot find a desired org unit'',
1708             ''Found all desired org units''
1709         );';
1710
1711     END;
1712 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1713
1714 -- convenience function for handling desired_not_migrate
1715
1716 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
1717     DECLARE
1718         table_schema ALIAS FOR $1;
1719         table_name ALIAS FOR $2;
1720         proceed BOOLEAN;
1721     BEGIN
1722         EXECUTE 'SELECT EXISTS (
1723             SELECT 1
1724             FROM information_schema.columns
1725             WHERE table_schema = $1
1726             AND table_name = $2
1727             and column_name = ''desired_not_migrate''
1728         )' INTO proceed USING table_schema, table_name;
1729         IF NOT proceed THEN
1730             RAISE EXCEPTION 'Missing column desired_not_migrate';
1731         END IF;
1732
1733         EXECUTE 'ALTER TABLE '
1734             || quote_ident(table_name)
1735             || ' DROP COLUMN IF EXISTS x_migrate';
1736         EXECUTE 'ALTER TABLE '
1737             || quote_ident(table_name)
1738             || ' ADD COLUMN x_migrate BOOLEAN';
1739
1740         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1741             || ' SET x_migrate = CASE'
1742             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
1743             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
1744             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
1745             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
1746             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
1747             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
1748             || ' END';
1749
1750         EXECUTE 'SELECT migration_tools.assert(
1751             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
1752             ''Not all desired_not_migrate values understood'',
1753             ''All desired_not_migrate values understood''
1754         );';
1755
1756     END;
1757 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1758
1759 -- convenience function for handling desired_not_migrate
1760
1761 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
1762     DECLARE
1763         table_schema ALIAS FOR $1;
1764         table_name ALIAS FOR $2;
1765         proceed BOOLEAN;
1766     BEGIN
1767         EXECUTE 'SELECT EXISTS (
1768             SELECT 1
1769             FROM information_schema.columns
1770             WHERE table_schema = $1
1771             AND table_name = $2
1772             and column_name = ''desired_barred_or_blocked''
1773         )' INTO proceed USING table_schema, table_name;
1774         IF NOT proceed THEN
1775             RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
1776         END IF;
1777
1778         EXECUTE 'ALTER TABLE '
1779             || quote_ident(table_name)
1780             || ' DROP COLUMN IF EXISTS x_barred';
1781         EXECUTE 'ALTER TABLE '
1782             || quote_ident(table_name)
1783             || ' ADD COLUMN x_barred BOOLEAN';
1784
1785         EXECUTE 'ALTER TABLE '
1786             || quote_ident(table_name)
1787             || ' DROP COLUMN IF EXISTS x_blocked';
1788         EXECUTE 'ALTER TABLE '
1789             || quote_ident(table_name)
1790             || ' ADD COLUMN x_blocked BOOLEAN';
1791
1792         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1793             || ' SET x_barred = CASE'
1794             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
1795             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
1796             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
1797             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
1798             || ' END';
1799
1800         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1801             || ' SET x_blocked = CASE'
1802             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
1803             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
1804             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
1805             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
1806             || ' END';
1807
1808         EXECUTE 'SELECT migration_tools.assert(
1809             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
1810             ''Not all desired_barred_or_blocked values understood'',
1811             ''All desired_barred_or_blocked values understood''
1812         );';
1813
1814     END;
1815 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1816
1817 -- convenience function for handling desired_profile
1818
1819 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
1820     DECLARE
1821         table_schema ALIAS FOR $1;
1822         table_name ALIAS FOR $2;
1823         proceed BOOLEAN;
1824     BEGIN
1825         EXECUTE 'SELECT EXISTS (
1826             SELECT 1
1827             FROM information_schema.columns
1828             WHERE table_schema = $1
1829             AND table_name = $2
1830             and column_name = ''desired_profile''
1831         )' INTO proceed USING table_schema, table_name;
1832         IF NOT proceed THEN
1833             RAISE EXCEPTION 'Missing column desired_profile';
1834         END IF;
1835
1836         EXECUTE 'ALTER TABLE '
1837             || quote_ident(table_name)
1838             || ' DROP COLUMN IF EXISTS x_profile';
1839         EXECUTE 'ALTER TABLE '
1840             || quote_ident(table_name)
1841             || ' ADD COLUMN x_profile INTEGER';
1842
1843         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
1844             || ' SET x_profile = b.id FROM permission.grp_tree b'
1845             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
1846
1847         EXECUTE 'SELECT migration_tools.assert(
1848             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
1849             ''Cannot find a desired profile'',
1850             ''Found all desired profiles''
1851         );';
1852
1853     END;
1854 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1855
1856 -- convenience function for handling desired actor stat cats
1857
1858 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1859     DECLARE
1860         table_schema ALIAS FOR $1;
1861         table_name ALIAS FOR $2;
1862         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
1863         org_shortname ALIAS FOR $4;
1864         proceed BOOLEAN;
1865         org INTEGER;
1866         org_list INTEGER[];
1867         sc TEXT;
1868         sce TEXT;
1869     BEGIN
1870
1871         SELECT 'desired_sc' || field_suffix INTO sc;
1872         SELECT 'desired_sce' || field_suffix INTO sce;
1873
1874         EXECUTE 'SELECT EXISTS (
1875             SELECT 1
1876             FROM information_schema.columns
1877             WHERE table_schema = $1
1878             AND table_name = $2
1879             and column_name = $3
1880         )' INTO proceed USING table_schema, table_name, sc;
1881         IF NOT proceed THEN
1882             RAISE EXCEPTION 'Missing column %', sc;
1883         END IF;
1884         EXECUTE 'SELECT EXISTS (
1885             SELECT 1
1886             FROM information_schema.columns
1887             WHERE table_schema = $1
1888             AND table_name = $2
1889             and column_name = $3
1890         )' INTO proceed USING table_schema, table_name, sce;
1891         IF NOT proceed THEN
1892             RAISE EXCEPTION 'Missing column %', sce;
1893         END IF;
1894
1895         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
1896         IF org IS NULL THEN
1897             RAISE EXCEPTION 'Cannot find org by shortname';
1898         END IF;
1899         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
1900
1901         -- caller responsible for their own truncates though we try to prevent duplicates
1902         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
1903             SELECT DISTINCT
1904                  $1
1905                 ,BTRIM('||sc||')
1906             FROM
1907                 ' || quote_ident(table_name) || '
1908             WHERE
1909                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
1910                 AND NOT EXISTS (
1911                     SELECT id
1912                     FROM actor.stat_cat
1913                     WHERE owner = ANY ($2)
1914                     AND name = BTRIM('||sc||')
1915                 )
1916                 AND NOT EXISTS (
1917                     SELECT id
1918                     FROM actor_stat_cat
1919                     WHERE owner = ANY ($2)
1920                     AND name = BTRIM('||sc||')
1921                 )
1922             ORDER BY 2;'
1923         USING org, org_list;
1924
1925         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
1926             SELECT DISTINCT
1927                 COALESCE(
1928                     (SELECT id
1929                         FROM actor.stat_cat
1930                         WHERE owner = ANY ($2)
1931                         AND BTRIM('||sc||') = BTRIM(name))
1932                    ,(SELECT id
1933                         FROM actor_stat_cat
1934                         WHERE owner = ANY ($2)
1935                         AND BTRIM('||sc||') = BTRIM(name))
1936                 )
1937                 ,$1
1938                 ,BTRIM('||sce||')
1939             FROM
1940                 ' || quote_ident(table_name) || '
1941             WHERE
1942                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
1943                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
1944                 AND NOT EXISTS (
1945                     SELECT id
1946                     FROM actor.stat_cat_entry
1947                     WHERE stat_cat = (
1948                         SELECT id
1949                         FROM actor.stat_cat
1950                         WHERE owner = ANY ($2)
1951                         AND BTRIM('||sc||') = BTRIM(name)
1952                     ) AND value = BTRIM('||sce||')
1953                     AND owner = ANY ($2)
1954                 )
1955                 AND NOT EXISTS (
1956                     SELECT id
1957                     FROM actor_stat_cat_entry
1958                     WHERE stat_cat = (
1959                         SELECT id
1960                         FROM actor_stat_cat
1961                         WHERE owner = ANY ($2)
1962                         AND BTRIM('||sc||') = BTRIM(name)
1963                     ) AND value = BTRIM('||sce||')
1964                     AND owner = ANY ($2)
1965                 )
1966             ORDER BY 1,3;'
1967         USING org, org_list;
1968     END;
1969 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1970
1971 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1972     DECLARE
1973         table_schema ALIAS FOR $1;
1974         table_name ALIAS FOR $2;
1975         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
1976         org_shortname ALIAS FOR $4;
1977         proceed BOOLEAN;
1978         org INTEGER;
1979         org_list INTEGER[];
1980         o INTEGER;
1981         sc TEXT;
1982         sce TEXT;
1983     BEGIN
1984         SELECT 'desired_sc' || field_suffix INTO sc;
1985         SELECT 'desired_sce' || field_suffix INTO sce;
1986         EXECUTE 'SELECT EXISTS (
1987             SELECT 1
1988             FROM information_schema.columns
1989             WHERE table_schema = $1
1990             AND table_name = $2
1991             and column_name = $3
1992         )' INTO proceed USING table_schema, table_name, sc;
1993         IF NOT proceed THEN
1994             RAISE EXCEPTION 'Missing column %', sc;
1995         END IF;
1996         EXECUTE 'SELECT EXISTS (
1997             SELECT 1
1998             FROM information_schema.columns
1999             WHERE table_schema = $1
2000             AND table_name = $2
2001             and column_name = $3
2002         )' INTO proceed USING table_schema, table_name, sce;
2003         IF NOT proceed THEN
2004             RAISE EXCEPTION 'Missing column %', sce;
2005         END IF;
2006
2007         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2008         IF org IS NULL THEN
2009             RAISE EXCEPTION 'Cannot find org by shortname';
2010         END IF;
2011
2012         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2013
2014         EXECUTE 'ALTER TABLE '
2015             || quote_ident(table_name)
2016             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
2017         EXECUTE 'ALTER TABLE '
2018             || quote_ident(table_name)
2019             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
2020         EXECUTE 'ALTER TABLE '
2021             || quote_ident(table_name)
2022             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
2023         EXECUTE 'ALTER TABLE '
2024             || quote_ident(table_name)
2025             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
2026
2027
2028         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2029             SET
2030                 x_sc' || field_suffix || ' = id
2031             FROM
2032                 (SELECT id, name, owner FROM actor_stat_cat
2033                     UNION SELECT id, name, owner FROM actor.stat_cat) u
2034             WHERE
2035                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2036                 AND u.owner = ANY ($1);'
2037         USING org_list;
2038
2039         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2040             SET
2041                 x_sce' || field_suffix || ' = id
2042             FROM
2043                 (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry
2044                     UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u
2045             WHERE
2046                     u.stat_cat = x_sc' || field_suffix || '
2047                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2048                 AND u.owner = ANY ($1);'
2049         USING org_list;
2050
2051         EXECUTE 'SELECT migration_tools.assert(
2052             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
2053             ''Cannot find a desired stat cat'',
2054             ''Found all desired stat cats''
2055         );';
2056
2057         EXECUTE 'SELECT migration_tools.assert(
2058             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
2059             ''Cannot find a desired stat cat entry'',
2060             ''Found all desired stat cat entries''
2061         );';
2062
2063     END;
2064 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2065
2066 -- convenience functions for adding shelving locations
2067 DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
2068 CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2069 DECLARE
2070     return_id   INT;
2071     d           INT;
2072     cur_id      INT;
2073 BEGIN
2074     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2075     WHILE d >= 0
2076     LOOP
2077         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2078         SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2079         IF return_id IS NOT NULL THEN
2080                 RETURN return_id;
2081         END IF;
2082         d := d - 1;
2083     END LOOP;
2084
2085     RETURN NULL;
2086 END
2087 $$ LANGUAGE plpgsql;
2088
2089 -- may remove later but testing using this with new migration scripts and not loading acls until go live
2090
2091 DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT);
2092 CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$
2093 DECLARE
2094     return_id   INT;
2095     d           INT;
2096     cur_id      INT;
2097 BEGIN
2098     SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id);
2099     WHILE d >= 0
2100     LOOP
2101         SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d);
2102
2103         SELECT INTO return_id id FROM
2104             (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x
2105             WHERE owning_lib = cur_id AND name ILIKE shelf_name;
2106         IF return_id IS NOT NULL THEN
2107                 RETURN return_id;
2108         END IF;
2109         d := d - 1;
2110     END LOOP;
2111
2112     RETURN NULL;
2113 END
2114 $$ LANGUAGE plpgsql;
2115
2116 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2117     DECLARE
2118         table_schema ALIAS FOR $1;
2119         table_name ALIAS FOR $2;
2120         foreign_column_name ALIAS FOR $3;
2121         main_column_name ALIAS FOR $4;
2122         btrim_desired ALIAS FOR $5;
2123         proceed BOOLEAN;
2124     BEGIN
2125         EXECUTE 'SELECT EXISTS (
2126             SELECT 1
2127             FROM information_schema.columns
2128             WHERE table_schema = $1
2129             AND table_name = $2
2130             and column_name = $3
2131         )' INTO proceed USING table_schema, table_name, foreign_column_name;
2132         IF NOT proceed THEN
2133             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2134         END IF;
2135
2136         EXECUTE 'SELECT EXISTS (
2137             SELECT 1
2138             FROM information_schema.columns
2139             WHERE table_schema = $1
2140             AND table_name = ''asset_copy_legacy''
2141             and column_name = $2
2142         )' INTO proceed USING table_schema, main_column_name;
2143         IF NOT proceed THEN
2144             RAISE EXCEPTION 'No %.asset_copy_legacy with column %', table_schema, main_column_name;
2145         END IF;
2146
2147         EXECUTE 'ALTER TABLE '
2148             || quote_ident(table_name)
2149             || ' DROP COLUMN IF EXISTS x_item';
2150         EXECUTE 'ALTER TABLE '
2151             || quote_ident(table_name)
2152             || ' ADD COLUMN x_item BIGINT';
2153
2154         IF btrim_desired THEN
2155             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2156                 || ' SET x_item = b.id FROM asset_copy_legacy b'
2157                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2158                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2159         ELSE
2160             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2161                 || ' SET x_item = b.id FROM asset_copy_legacy b'
2162                 || ' WHERE a.' || quote_ident(foreign_column_name)
2163                 || ' = b.' || quote_ident(main_column_name);
2164         END IF;
2165
2166         --EXECUTE 'SELECT migration_tools.assert(
2167         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_item IS NULL),
2168         --    ''Cannot link every barcode'',
2169         --    ''Every barcode linked''
2170         --);';
2171
2172     END;
2173 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2174
2175 -- convenience function for linking to the user staging table
2176
2177 CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2178     DECLARE
2179         table_schema ALIAS FOR $1;
2180         table_name ALIAS FOR $2;
2181         foreign_column_name ALIAS FOR $3;
2182         main_column_name ALIAS FOR $4;
2183         btrim_desired ALIAS FOR $5;
2184         proceed BOOLEAN;
2185     BEGIN
2186         EXECUTE 'SELECT EXISTS (
2187             SELECT 1
2188             FROM information_schema.columns
2189             WHERE table_schema = $1
2190             AND table_name = $2
2191             and column_name = $3
2192         )' INTO proceed USING table_schema, table_name, foreign_column_name;
2193         IF NOT proceed THEN
2194             RAISE EXCEPTION '%.% missing column %', table_schema, table_name, foreign_column_name;
2195         END IF;
2196
2197         EXECUTE 'SELECT EXISTS (
2198             SELECT 1
2199             FROM information_schema.columns
2200             WHERE table_schema = $1
2201             AND table_name = ''actor_usr_legacy''
2202             and column_name = $2
2203         )' INTO proceed USING table_schema, main_column_name;
2204         IF NOT proceed THEN
2205             RAISE EXCEPTION 'No %.actor_usr_legacy with column %', table_schema, main_column_name;
2206         END IF;
2207
2208         EXECUTE 'ALTER TABLE '
2209             || quote_ident(table_name)
2210             || ' DROP COLUMN IF EXISTS x_user';
2211         EXECUTE 'ALTER TABLE '
2212             || quote_ident(table_name)
2213             || ' ADD COLUMN x_user INTEGER';
2214
2215         IF btrim_desired THEN
2216             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2217                 || ' SET x_user = b.id FROM actor_usr_legacy b'
2218                 || ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
2219                 || ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
2220         ELSE
2221             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
2222                 || ' SET x_user = b.id FROM actor_usr_legacy b'
2223                 || ' WHERE a.' || quote_ident(foreign_column_name)
2224                 || ' = b.' || quote_ident(main_column_name);
2225         END IF;
2226
2227         --EXECUTE 'SELECT migration_tools.assert(
2228         --    NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(foreign_column_name) || ' <> '''' AND x_user IS NULL),
2229         --    ''Cannot link every barcode'',
2230         --    ''Every barcode linked''
2231         --);';
2232
2233     END;
2234 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2235
2236 -- convenience function for linking two tables
2237 -- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false);
2238 CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2239     DECLARE
2240         table_schema ALIAS FOR $1;
2241         table_a ALIAS FOR $2;
2242         column_a ALIAS FOR $3;
2243         table_b ALIAS FOR $4;
2244         column_b ALIAS FOR $5;
2245         column_x ALIAS FOR $6;
2246         btrim_desired ALIAS FOR $7;
2247         proceed BOOLEAN;
2248     BEGIN
2249         EXECUTE 'SELECT EXISTS (
2250             SELECT 1
2251             FROM information_schema.columns
2252             WHERE table_schema = $1
2253             AND table_name = $2
2254             and column_name = $3
2255         )' INTO proceed USING table_schema, table_a, column_a;
2256         IF NOT proceed THEN
2257             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2258         END IF;
2259
2260         EXECUTE 'SELECT EXISTS (
2261             SELECT 1
2262             FROM information_schema.columns
2263             WHERE table_schema = $1
2264             AND table_name = $2
2265             and column_name = $3
2266         )' INTO proceed USING table_schema, table_b, column_b;
2267         IF NOT proceed THEN
2268             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2269         END IF;
2270
2271         EXECUTE 'ALTER TABLE '
2272             || quote_ident(table_b)
2273             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2274         EXECUTE 'ALTER TABLE '
2275             || quote_ident(table_b)
2276             || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT';
2277
2278         IF btrim_desired THEN
2279             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2280                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2281                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2282                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2283         ELSE
2284             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2285                 || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
2286                 || ' WHERE a.' || quote_ident(column_a)
2287                 || ' = b.' || quote_ident(column_b);
2288         END IF;
2289
2290     END;
2291 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2292
2293 -- convenience function for linking two tables, but copying column w into column x instead of "id"
2294 -- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false);
2295 CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$
2296     DECLARE
2297         table_schema ALIAS FOR $1;
2298         table_a ALIAS FOR $2;
2299         column_a ALIAS FOR $3;
2300         table_b ALIAS FOR $4;
2301         column_b ALIAS FOR $5;
2302         column_w ALIAS FOR $6;
2303         column_x ALIAS FOR $7;
2304         btrim_desired ALIAS FOR $8;
2305         proceed BOOLEAN;
2306     BEGIN
2307         EXECUTE 'SELECT EXISTS (
2308             SELECT 1
2309             FROM information_schema.columns
2310             WHERE table_schema = $1
2311             AND table_name = $2
2312             and column_name = $3
2313         )' INTO proceed USING table_schema, table_a, column_a;
2314         IF NOT proceed THEN
2315             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2316         END IF;
2317
2318         EXECUTE 'SELECT EXISTS (
2319             SELECT 1
2320             FROM information_schema.columns
2321             WHERE table_schema = $1
2322             AND table_name = $2
2323             and column_name = $3
2324         )' INTO proceed USING table_schema, table_b, column_b;
2325         IF NOT proceed THEN
2326             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2327         END IF;
2328
2329         EXECUTE 'ALTER TABLE '
2330             || quote_ident(table_b)
2331             || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x);
2332         EXECUTE 'ALTER TABLE '
2333             || quote_ident(table_b)
2334             || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT';
2335
2336         IF btrim_desired THEN
2337             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2338                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2339                 || ' WHERE BTRIM(a.' || quote_ident(column_a)
2340                 || ') = BTRIM(b.' || quote_ident(column_b) || ')';
2341         ELSE
2342             EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2343                 || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2344                 || ' WHERE a.' || quote_ident(column_a)
2345                 || ' = b.' || quote_ident(column_b);
2346         END IF;
2347
2348     END;
2349 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2350
2351 -- convenience function for linking two tables, but copying column w into column x instead of "id". Unlike handle_link2, this one won't drop the target column, and it also doesn't have a final boolean argument for btrim
2352 -- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
2353 CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2354     DECLARE
2355         table_schema ALIAS FOR $1;
2356         table_a ALIAS FOR $2;
2357         column_a ALIAS FOR $3;
2358         table_b ALIAS FOR $4;
2359         column_b ALIAS FOR $5;
2360         column_w ALIAS FOR $6;
2361         column_x ALIAS FOR $7;
2362         proceed BOOLEAN;
2363     BEGIN
2364         EXECUTE 'SELECT EXISTS (
2365             SELECT 1
2366             FROM information_schema.columns
2367             WHERE table_schema = $1
2368             AND table_name = $2
2369             and column_name = $3
2370         )' INTO proceed USING table_schema, table_a, column_a;
2371         IF NOT proceed THEN
2372             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2373         END IF;
2374
2375         EXECUTE 'SELECT EXISTS (
2376             SELECT 1
2377             FROM information_schema.columns
2378             WHERE table_schema = $1
2379             AND table_name = $2
2380             and column_name = $3
2381         )' INTO proceed USING table_schema, table_b, column_b;
2382         IF NOT proceed THEN
2383             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2384         END IF;
2385
2386         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2387             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2388             || ' WHERE a.' || quote_ident(column_a)
2389             || ' = b.' || quote_ident(column_b);
2390
2391     END;
2392 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2393
2394 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2395     DECLARE
2396         table_schema ALIAS FOR $1;
2397         table_a ALIAS FOR $2;
2398         column_a ALIAS FOR $3;
2399         table_b ALIAS FOR $4;
2400         column_b ALIAS FOR $5;
2401         column_w ALIAS FOR $6;
2402         column_x ALIAS FOR $7;
2403         proceed BOOLEAN;
2404     BEGIN
2405         EXECUTE 'SELECT EXISTS (
2406             SELECT 1
2407             FROM information_schema.columns
2408             WHERE table_schema = $1
2409             AND table_name = $2
2410             and column_name = $3
2411         )' INTO proceed USING table_schema, table_a, column_a;
2412         IF NOT proceed THEN
2413             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2414         END IF;
2415
2416         EXECUTE 'SELECT EXISTS (
2417             SELECT 1
2418             FROM information_schema.columns
2419             WHERE table_schema = $1
2420             AND table_name = $2
2421             and column_name = $3
2422         )' INTO proceed USING table_schema, table_b, column_b;
2423         IF NOT proceed THEN
2424             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2425         END IF;
2426
2427         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2428             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2429             || ' WHERE a.' || quote_ident(column_a)
2430             || ' = b.' || quote_ident(column_b)
2431             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
2432
2433     END;
2434 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2435
2436 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2437     DECLARE
2438         table_schema ALIAS FOR $1;
2439         table_a ALIAS FOR $2;
2440         column_a ALIAS FOR $3;
2441         table_b ALIAS FOR $4;
2442         column_b ALIAS FOR $5;
2443         column_w ALIAS FOR $6;
2444         column_x ALIAS FOR $7;
2445         proceed BOOLEAN;
2446     BEGIN
2447         EXECUTE 'SELECT EXISTS (
2448             SELECT 1
2449             FROM information_schema.columns
2450             WHERE table_schema = $1
2451             AND table_name = $2
2452             and column_name = $3
2453         )' INTO proceed USING table_schema, table_a, column_a;
2454         IF NOT proceed THEN
2455             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2456         END IF;
2457
2458         EXECUTE 'SELECT EXISTS (
2459             SELECT 1
2460             FROM information_schema.columns
2461             WHERE table_schema = $1
2462             AND table_name = $2
2463             and column_name = $3
2464         )' INTO proceed USING table_schema, table_b, column_b;
2465         IF NOT proceed THEN
2466             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2467         END IF;
2468
2469         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2470             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2471             || ' WHERE a.' || quote_ident(column_a)
2472             || ' = b.' || quote_ident(column_b)
2473             || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
2474
2475     END;
2476 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2477
2478 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2479     DECLARE
2480         table_schema ALIAS FOR $1;
2481         table_a ALIAS FOR $2;
2482         column_a ALIAS FOR $3;
2483         table_b ALIAS FOR $4;
2484         column_b ALIAS FOR $5;
2485         column_w ALIAS FOR $6;
2486         column_x ALIAS FOR $7;
2487         proceed BOOLEAN;
2488     BEGIN
2489         EXECUTE 'SELECT EXISTS (
2490             SELECT 1
2491             FROM information_schema.columns
2492             WHERE table_schema = $1
2493             AND table_name = $2
2494             and column_name = $3
2495         )' INTO proceed USING table_schema, table_a, column_a;
2496         IF NOT proceed THEN
2497             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2498         END IF;
2499
2500         EXECUTE 'SELECT EXISTS (
2501             SELECT 1
2502             FROM information_schema.columns
2503             WHERE table_schema = $1
2504             AND table_name = $2
2505             and column_name = $3
2506         )' INTO proceed USING table_schema, table_b, column_b;
2507         IF NOT proceed THEN
2508             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2509         END IF;
2510
2511         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2512             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2513             || ' WHERE a.' || quote_ident(column_a)
2514             || ' = b.' || quote_ident(column_b)
2515             || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
2516
2517     END;
2518 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2519
2520 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2521     DECLARE
2522         table_schema ALIAS FOR $1;
2523         table_a ALIAS FOR $2;
2524         column_a ALIAS FOR $3;
2525         table_b ALIAS FOR $4;
2526         column_b ALIAS FOR $5;
2527         column_w ALIAS FOR $6;
2528         column_x ALIAS FOR $7;
2529         proceed BOOLEAN;
2530     BEGIN
2531         EXECUTE 'SELECT EXISTS (
2532             SELECT 1
2533             FROM information_schema.columns
2534             WHERE table_schema = $1
2535             AND table_name = $2
2536             and column_name = $3
2537         )' INTO proceed USING table_schema, table_a, column_a;
2538         IF NOT proceed THEN
2539             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2540         END IF;
2541
2542         EXECUTE 'SELECT EXISTS (
2543             SELECT 1
2544             FROM information_schema.columns
2545             WHERE table_schema = $1
2546             AND table_name = $2
2547             and column_name = $3
2548         )' INTO proceed USING table_schema, table_b, column_b;
2549         IF NOT proceed THEN
2550             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2551         END IF;
2552
2553         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2554             || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
2555             || ' WHERE a.' || quote_ident(column_a)
2556             || ' = b.' || quote_ident(column_b)
2557             || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
2558
2559     END;
2560 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2561
2562 CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2563     DECLARE
2564         table_schema ALIAS FOR $1;
2565         table_a ALIAS FOR $2;
2566         column_a ALIAS FOR $3;
2567         table_b ALIAS FOR $4;
2568         column_b ALIAS FOR $5;
2569         column_w ALIAS FOR $6;
2570         column_x ALIAS FOR $7;
2571         proceed BOOLEAN;
2572     BEGIN
2573         EXECUTE 'SELECT EXISTS (
2574             SELECT 1
2575             FROM information_schema.columns
2576             WHERE table_schema = $1
2577             AND table_name = $2
2578             and column_name = $3
2579         )' INTO proceed USING table_schema, table_a, column_a;
2580         IF NOT proceed THEN
2581             RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
2582         END IF;
2583
2584         EXECUTE 'SELECT EXISTS (
2585             SELECT 1
2586             FROM information_schema.columns
2587             WHERE table_schema = $1
2588             AND table_name = $2
2589             and column_name = $3
2590         )' INTO proceed USING table_schema, table_b, column_b;
2591         IF NOT proceed THEN
2592             RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
2593         END IF;
2594
2595         EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
2596             || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
2597             || ' WHERE a.' || quote_ident(column_a)
2598             || ' = b.' || quote_ident(column_b)
2599             || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
2600
2601     END;
2602 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2603
2604 -- convenience function for handling desired asset stat cats
2605
2606 CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2607     DECLARE
2608         table_schema ALIAS FOR $1;
2609         table_name ALIAS FOR $2;
2610         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2611         org_shortname ALIAS FOR $4;
2612         proceed BOOLEAN;
2613         org INTEGER;
2614         org_list INTEGER[];
2615         sc TEXT;
2616         sce TEXT;
2617     BEGIN
2618
2619         SELECT 'desired_sc' || field_suffix INTO sc;
2620         SELECT 'desired_sce' || field_suffix INTO sce;
2621
2622         EXECUTE 'SELECT EXISTS (
2623             SELECT 1
2624             FROM information_schema.columns
2625             WHERE table_schema = $1
2626             AND table_name = $2
2627             and column_name = $3
2628         )' INTO proceed USING table_schema, table_name, sc;
2629         IF NOT proceed THEN
2630             RAISE EXCEPTION 'Missing column %', sc;
2631         END IF;
2632         EXECUTE 'SELECT EXISTS (
2633             SELECT 1
2634             FROM information_schema.columns
2635             WHERE table_schema = $1
2636             AND table_name = $2
2637             and column_name = $3
2638         )' INTO proceed USING table_schema, table_name, sce;
2639         IF NOT proceed THEN
2640             RAISE EXCEPTION 'Missing column %', sce;
2641         END IF;
2642
2643         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2644         IF org IS NULL THEN
2645             RAISE EXCEPTION 'Cannot find org by shortname';
2646         END IF;
2647         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2648
2649         -- caller responsible for their own truncates though we try to prevent duplicates
2650         EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
2651             SELECT DISTINCT
2652                  $1
2653                 ,BTRIM('||sc||')
2654             FROM
2655                 ' || quote_ident(table_name) || '
2656             WHERE
2657                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2658                 AND NOT EXISTS (
2659                     SELECT id
2660                     FROM asset.stat_cat
2661                     WHERE owner = ANY ($2)
2662                     AND name = BTRIM('||sc||')
2663                 )
2664                 AND NOT EXISTS (
2665                     SELECT id
2666                     FROM asset_stat_cat
2667                     WHERE owner = ANY ($2)
2668                     AND name = BTRIM('||sc||')
2669                 )
2670             ORDER BY 2;'
2671         USING org, org_list;
2672
2673         EXECUTE 'INSERT INTO asset_stat_cat_entry (stat_cat, owner, value)
2674             SELECT DISTINCT
2675                 COALESCE(
2676                     (SELECT id
2677                         FROM asset.stat_cat
2678                         WHERE owner = ANY ($2)
2679                         AND BTRIM('||sc||') = BTRIM(name))
2680                    ,(SELECT id
2681                         FROM asset_stat_cat
2682                         WHERE owner = ANY ($2)
2683                         AND BTRIM('||sc||') = BTRIM(name))
2684                 )
2685                 ,$1
2686                 ,BTRIM('||sce||')
2687             FROM
2688                 ' || quote_ident(table_name) || '
2689             WHERE
2690                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
2691                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
2692                 AND NOT EXISTS (
2693                     SELECT id
2694                     FROM asset.stat_cat_entry
2695                     WHERE stat_cat = (
2696                         SELECT id
2697                         FROM asset.stat_cat
2698                         WHERE owner = ANY ($2)
2699                         AND BTRIM('||sc||') = BTRIM(name)
2700                     ) AND value = BTRIM('||sce||')
2701                     AND owner = ANY ($2)
2702                 )
2703                 AND NOT EXISTS (
2704                     SELECT id
2705                     FROM asset_stat_cat_entry
2706                     WHERE stat_cat = (
2707                         SELECT id
2708                         FROM asset_stat_cat
2709                         WHERE owner = ANY ($2)
2710                         AND BTRIM('||sc||') = BTRIM(name)
2711                     ) AND value = BTRIM('||sce||')
2712                     AND owner = ANY ($2)
2713                 )
2714             ORDER BY 1,3;'
2715         USING org, org_list;
2716     END;
2717 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2718
2719 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
2720     DECLARE
2721         table_schema ALIAS FOR $1;
2722         table_name ALIAS FOR $2;
2723         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
2724         org_shortname ALIAS FOR $4;
2725         proceed BOOLEAN;
2726         org INTEGER;
2727         org_list INTEGER[];
2728         o INTEGER;
2729         sc TEXT;
2730         sce TEXT;
2731     BEGIN
2732         SELECT 'desired_sc' || field_suffix INTO sc;
2733         SELECT 'desired_sce' || field_suffix INTO sce;
2734         EXECUTE 'SELECT EXISTS (
2735             SELECT 1
2736             FROM information_schema.columns
2737             WHERE table_schema = $1
2738             AND table_name = $2
2739             and column_name = $3
2740         )' INTO proceed USING table_schema, table_name, sc;
2741         IF NOT proceed THEN
2742             RAISE EXCEPTION 'Missing column %', sc;
2743         END IF;
2744         EXECUTE 'SELECT EXISTS (
2745             SELECT 1
2746             FROM information_schema.columns
2747             WHERE table_schema = $1
2748             AND table_name = $2
2749             and column_name = $3
2750         )' INTO proceed USING table_schema, table_name, sce;
2751         IF NOT proceed THEN
2752             RAISE EXCEPTION 'Missing column %', sce;
2753         END IF;
2754
2755         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2756         IF org IS NULL THEN
2757             RAISE EXCEPTION 'Cannot find org by shortname';
2758         END IF;
2759
2760         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2761
2762         EXECUTE 'ALTER TABLE '
2763             || quote_ident(table_name)
2764             || ' DROP COLUMN IF EXISTS x_sc' || field_suffix;
2765         EXECUTE 'ALTER TABLE '
2766             || quote_ident(table_name)
2767             || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER';
2768         EXECUTE 'ALTER TABLE '
2769             || quote_ident(table_name)
2770             || ' DROP COLUMN IF EXISTS x_sce' || field_suffix;
2771         EXECUTE 'ALTER TABLE '
2772             || quote_ident(table_name)
2773             || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER';
2774
2775
2776         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2777             SET
2778                 x_sc' || field_suffix || ' = id
2779             FROM
2780                 (SELECT id, name, owner FROM asset_stat_cat
2781                     UNION SELECT id, name, owner FROM asset.stat_cat) u
2782             WHERE
2783                     BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || '))
2784                 AND u.owner = ANY ($1);'
2785         USING org_list;
2786
2787         EXECUTE 'UPDATE ' || quote_ident(table_name) || '
2788             SET
2789                 x_sce' || field_suffix || ' = id
2790             FROM
2791                 (SELECT id, stat_cat, owner, value FROM asset_stat_cat_entry
2792                     UNION SELECT id, stat_cat, owner, value FROM asset.stat_cat_entry) u
2793             WHERE
2794                     u.stat_cat = x_sc' || field_suffix || '
2795                 AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || '))
2796                 AND u.owner = ANY ($1);'
2797         USING org_list;
2798
2799         EXECUTE 'SELECT migration_tools.assert(
2800             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL),
2801             ''Cannot find a desired stat cat'',
2802             ''Found all desired stat cats''
2803         );';
2804
2805         EXECUTE 'SELECT migration_tools.assert(
2806             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL),
2807             ''Cannot find a desired stat cat entry'',
2808             ''Found all desired stat cat entries''
2809         );';
2810
2811     END;
2812 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2813
2814 DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
2815 CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2816  LANGUAGE plpgsql
2817 AS $function$
2818 DECLARE
2819     c_name     TEXT;
2820 BEGIN
2821
2822     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2823             table_name = t_name
2824             AND table_schema = s_name
2825             AND (data_type='text' OR data_type='character varying')
2826             AND column_name like 'l_%'
2827     LOOP
2828        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
2829     END LOOP;
2830
2831     RETURN TRUE;
2832 END
2833 $function$;
2834
2835 DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
2836 CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2837  LANGUAGE plpgsql
2838 AS $function$
2839 DECLARE
2840     c_name     TEXT;
2841 BEGIN
2842
2843     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2844             table_name = t_name
2845             AND table_schema = s_name
2846             AND (data_type='text' OR data_type='character varying')
2847     LOOP
2848        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
2849     END LOOP;
2850
2851     RETURN TRUE;
2852 END
2853 $function$;
2854
2855 DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
2856 CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2857  LANGUAGE plpgsql
2858 AS $function$
2859 DECLARE
2860     c_name     TEXT;
2861 BEGIN
2862
2863     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2864             table_name = t_name
2865             AND table_schema = s_name
2866             AND (data_type='text' OR data_type='character varying')
2867             AND column_name like 'l_%'
2868     LOOP
2869        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
2870     END LOOP;
2871
2872     RETURN TRUE;
2873 END
2874 $function$;
2875
2876 DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
2877 CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
2878  LANGUAGE plpgsql
2879 AS $function$
2880 DECLARE
2881     c_name     TEXT;
2882 BEGIN
2883
2884     FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
2885             table_name = t_name
2886             AND table_schema = s_name
2887             AND (data_type='text' OR data_type='character varying')
2888     LOOP
2889        EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
2890     END LOOP;
2891
2892     RETURN TRUE;
2893 END
2894 $function$;
2895
2896
2897 -- convenience function for handling item barcode collisions in asset_copy_legacy
2898
2899 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
2900 DECLARE
2901     x_barcode TEXT;
2902     x_id BIGINT;
2903     row_count NUMERIC;
2904     internal_collision_count NUMERIC := 0;
2905     incumbent_collision_count NUMERIC := 0;
2906 BEGIN
2907     FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
2908     LOOP
2909         FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
2910         LOOP
2911             UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
2912             GET DIAGNOSTICS row_count = ROW_COUNT;
2913             internal_collision_count := internal_collision_count + row_count;
2914         END LOOP;
2915     END LOOP;
2916     RAISE INFO '% internal collisions', internal_collision_count;
2917     FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
2918     LOOP
2919         FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
2920         LOOP
2921             UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
2922             GET DIAGNOSTICS row_count = ROW_COUNT;
2923             incumbent_collision_count := incumbent_collision_count + row_count;
2924         END LOOP;
2925     END LOOP;
2926     RAISE INFO '% incumbent collisions', incumbent_collision_count;
2927 END
2928 $function$ LANGUAGE plpgsql;
2929
2930 -- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
2931 -- this should be ran prior to populating actor_card
2932
2933 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
2934 DECLARE
2935     x_barcode TEXT;
2936     x_id BIGINT;
2937     row_count NUMERIC;
2938     internal_collision_count NUMERIC := 0;
2939     incumbent_barcode_collision_count NUMERIC := 0;
2940     incumbent_usrname_collision_count NUMERIC := 0;
2941 BEGIN
2942     FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
2943     LOOP
2944         FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2945         LOOP
2946             UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
2947             GET DIAGNOSTICS row_count = ROW_COUNT;
2948             internal_collision_count := internal_collision_count + row_count;
2949         END LOOP;
2950     END LOOP;
2951     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
2952
2953     FOR x_barcode IN
2954         SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
2955     LOOP
2956         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2957         LOOP
2958             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
2959             GET DIAGNOSTICS row_count = ROW_COUNT;
2960             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
2961         END LOOP;
2962     END LOOP;
2963     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
2964
2965     FOR x_barcode IN
2966         SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
2967     LOOP
2968         FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
2969         LOOP
2970             UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
2971             GET DIAGNOSTICS row_count = ROW_COUNT;
2972             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
2973         END LOOP;
2974     END LOOP;
2975     RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
2976 END
2977 $function$ LANGUAGE plpgsql;