View | Details | Raw Unified | Return to bug 23066
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_23066.perl (+17 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    # Add constraint for branchcode
5
    unless ( foreign_key_exists( 'issues', 'issues_ibfk_3' ) ) {
6
        $dbh->do(q{UPDATE issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
7
        $dbh->do(q{ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
8
    }
9
    unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_3' ) ) {
10
        $dbh->do(q{UPDATE old_issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
11
        $dbh->do(q{ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
12
    }
13
14
    # Always end with this (adjust the bug info)
15
    SetVersion( $DBversion );
16
    print "Upgrade to $DBversion done (Bug 23066 - Add foreign key for issues tables to branches table for branchcodes)\n";
17
}
(-)a/installer/data/mysql/kohastructure.sql (-6 / +5 lines)
Lines 1681-1687 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1681
  KEY `branchcode_idx` (`branchcode`),
1681
  KEY `branchcode_idx` (`branchcode`),
1682
  KEY `bordate` (`borrowernumber`,`timestamp`),
1682
  KEY `bordate` (`borrowernumber`,`timestamp`),
1683
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1683
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1684
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
1684
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1685
  CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
1685
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1686
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1686
1687
1687
--
1688
--
Lines 1711-1720 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1711
  KEY `old_issuesitemidx` (`itemnumber`),
1712
  KEY `old_issuesitemidx` (`itemnumber`),
1712
  KEY `branchcode_idx` (`branchcode`),
1713
  KEY `branchcode_idx` (`branchcode`),
1713
  KEY `old_bordate` (`borrowernumber`,`timestamp`),
1714
  KEY `old_bordate` (`borrowernumber`,`timestamp`),
1714
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1715
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1715
    ON DELETE SET NULL ON UPDATE SET NULL,
1716
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1716
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1717
  CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
1717
    ON DELETE SET NULL ON UPDATE SET NULL
1718
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1718
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1719
1719
1720
--
1720
--
1721
- 

Return to bug 23066