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

(-)a/installer/data/mysql/atomicupdate/bug_22008.perl (+31 lines)
Line 0 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    # Add constraint for manager_id
5
    my $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_borrowers_2'|);
6
    $sth->execute;
7
    unless ($sth->fetchrow_hashref) {
8
        $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;");
9
        $dbh->do("UPDATE accountlines SET manager_id = NULL where manager_id NOT IN (SELECT borrowernumber FROM borrowers)");
10
        $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE");
11
    }
12
13
    # Add constraint for issue_id
14
    $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_issues'|);
15
    $sth->execute;
16
    unless ($sth->fetchrow_hashref) {
17
        $dbh->do("UPDATE accountlines SET issue_id = NULL where issue_id NOT IN (SELECT issue_id FROM issues)");
18
        $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");
19
    }
20
21
    # Rename accountlines_ibfk_2 to accountlines_ibfk_items
22
    $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_2'|);
23
    $sth->execute;
24
    if ($sth->fetchrow_hashref) {
25
        $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2");
26
        $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE");
27
    }
28
29
    SetVersion( $DBversion );
30
    print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints to accountlines)\n";
31
}
(-)a/installer/data/mysql/kohastructure.sql (-3 / +6 lines)
Lines 2719-2733 CREATE TABLE `accountlines` ( Link Here
2719
  `lastincrement` decimal(28,6) default NULL,
2719
  `lastincrement` decimal(28,6) default NULL,
2720
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2720
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2721
  `note` MEDIUMTEXT NULL default NULL,
2721
  `note` MEDIUMTEXT NULL default NULL,
2722
  `manager_id` int(11) NULL,
2722
  `manager_id` int(11) NULL DEFAULT NULL,
2723
  `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
2723
  `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
2724
  PRIMARY KEY (`accountlines_id`),
2724
  PRIMARY KEY (`accountlines_id`),
2725
  KEY `acctsborridx` (`borrowernumber`),
2725
  KEY `acctsborridx` (`borrowernumber`),
2726
  KEY `timeidx` (`timestamp`),
2726
  KEY `timeidx` (`timestamp`),
2727
  KEY `itemnumber` (`itemnumber`),
2727
  KEY `itemnumber` (`itemnumber`),
2728
  KEY `branchcode` (`branchcode`),
2728
  KEY `branchcode` (`branchcode`),
2729
  KEY `issue_id` (`issue_id`),
2730
  KEY `manager_id` (`manager_id`),
2729
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2731
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2730
  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
2732
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2733
  CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2734
  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2731
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2735
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2732
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2736
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2733
2737
2734
- 

Return to bug 22008