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

(-)a/installer/data/mysql/atomicupdate/bug_22421.perl (+55 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(q{
7
            ALTER TABLE accountlines 
8
            ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id
9
        });
10
        $dbh->do(q{
11
            ALTER TABLE accountlines 
12
            ADD CONSTRAINT `accountlines_ibfk_old_issues` 
13
              FOREIGN KEY (`old_issue_id`) 
14
              REFERENCES `old_issues` (`issue_id`) 
15
              ON DELETE SET NULL 
16
              ON UPDATE CASCADE
17
        });
18
        $dbh->do(q{
19
            UPDATE
20
              accountlines a
21
              LEFT JOIN old_issues o ON (a.issue_id = o.issue_id)
22
            SET
23
              a.old_issue_id = o.issue_id,
24
              a.issue_id = NULL
25
            WHERE
26
              o.issue_id IS NOT NULL
27
        });
28
29
        # Add constraint for issue_id
30
        unless (
31
            foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) )
32
        {
33
            $dbh->do(q{
34
                UPDATE
35
                  accountlines a
36
                  LEFT JOIN issues i ON (a.issue_id = i.issue_id)
37
                SET
38
                  a.issue_id = NULL
39
                WHERE
40
                  i.issue_id IS NULL
41
            });
42
            $dbh->do(q{
43
                ALTER TABLE accountlines
44
                ADD CONSTRAINT `accountlines_ibfk_issues` 
45
                  FOREIGN KEY (`issue_id`) 
46
                  REFERENCES `issues` (`issue_id`) 
47
                  ON DELETE SET NULL 
48
                  ON UPDATE CASCADE
49
            });
50
        }
51
    }
52
53
    SetVersion($DBversion);
54
    print "Upgrade to $DBversion done (Bug 22421 - Add old_issue_id to accountlines and setup appropriate constraints)\n";
55
}
(-)a/installer/data/mysql/kohastructure.sql (-1 / +5 lines)
Lines 2672-2677 DROP TABLE IF EXISTS `accountlines`; Link Here
2672
CREATE TABLE `accountlines` (
2672
CREATE TABLE `accountlines` (
2673
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2673
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2674
  `issue_id` int(11) NULL DEFAULT NULL,
2674
  `issue_id` int(11) NULL DEFAULT NULL,
2675
  `old_issue_id` int(11) NULL DEFAULT NULL,
2675
  `borrowernumber` int(11) DEFAULT NULL,
2676
  `borrowernumber` int(11) DEFAULT NULL,
2676
  `itemnumber` int(11) default NULL,
2677
  `itemnumber` int(11) default NULL,
2677
  `date` date default NULL,
2678
  `date` date default NULL,
Lines 2691-2699 CREATE TABLE `accountlines` ( Link Here
2691
  KEY `timeidx` (`timestamp`),
2692
  KEY `timeidx` (`timestamp`),
2692
  KEY `itemnumber` (`itemnumber`),
2693
  KEY `itemnumber` (`itemnumber`),
2693
  KEY `branchcode` (`branchcode`),
2694
  KEY `branchcode` (`branchcode`),
2695
  KEY `issue_id` (`issue_id`),
2696
  KEY `old_issue_id` (`old_issue_id`),
2694
  KEY `manager_id` (`manager_id`),
2697
  KEY `manager_id` (`manager_id`),
2695
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2698
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2696
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2699
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2700
  CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2701
  CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2697
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2702
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2698
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2703
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2699
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2704
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2700
- 

Return to bug 22421