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

(-)a/installer/data/mysql/atomicupdate/bug_22421.perl (+36 lines)
Line 0 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    # Add old_issue_id field
5
    unless( column_exists( 'accountlines', 'old_issue_id' )) {
6
        $dbh->do("ALTER TABLE accountlines ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id");
7
        $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE");
8
        $dbh->do(q{
9
            UPDATE
10
              accountlines a
11
              LEFT JOIN old_issues o ON (a.issue_id = o.issue_id)
12
            SET
13
              a.old_issue_id = o.issue_id,
14
              a.issue_id = NULL
15
            WHERE
16
              o.issue_id IS NOT NULL
17
        });
18
        
19
        # Add constraint for issue_id
20
        unless( foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) {
21
            $dbh->do(q{
22
                UPDATE
23
                  accountlines a
24
                  LEFT JOIN issues i ON (a.issue_id = i.issue_id)
25
                SET
26
                  a.issue_id = NULL
27
                WHERE
28
                  i.issue_id IS NULL
29
            });
30
            $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE");
31
        }
32
    }
33
34
    SetVersion( $DBversion );
35
    print "Upgrade to $DBversion done (Bug 22421 - Add old_issue_id to accountlines and setup appropriate constraints)\n";
36
}
(-)a/installer/data/mysql/kohastructure.sql (-1 / +5 lines)
Lines 2707-2712 DROP TABLE IF EXISTS `accountlines`; Link Here
2707
CREATE TABLE `accountlines` (
2707
CREATE TABLE `accountlines` (
2708
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2708
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2709
  `issue_id` int(11) NULL DEFAULT NULL,
2709
  `issue_id` int(11) NULL DEFAULT NULL,
2710
  `old_issue_id` int(11) NULL DEFAULT NULL,
2710
  `borrowernumber` int(11) DEFAULT NULL,
2711
  `borrowernumber` int(11) DEFAULT NULL,
2711
  `accountno` smallint(6) NOT NULL default 0,
2712
  `accountno` smallint(6) NOT NULL default 0,
2712
  `itemnumber` int(11) default NULL,
2713
  `itemnumber` int(11) default NULL,
Lines 2726-2734 CREATE TABLE `accountlines` ( Link Here
2726
  KEY `timeidx` (`timestamp`),
2727
  KEY `timeidx` (`timestamp`),
2727
  KEY `itemnumber` (`itemnumber`),
2728
  KEY `itemnumber` (`itemnumber`),
2728
  KEY `branchcode` (`branchcode`),
2729
  KEY `branchcode` (`branchcode`),
2730
  KEY `issue_id` (`issue_id`),
2731
  KEY `old_issue_id` (`old_issue_id`),
2729
  KEY `manager_id` (`manager_id`),
2732
  KEY `manager_id` (`manager_id`),
2730
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2733
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2731
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2734
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2735
  CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2736
  CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2732
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2737
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2733
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2738
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2734
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2739
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2735
- 

Return to bug 22421