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

(-)a/installer/data/mysql/atomicupdate/bug_13515.perl (+20 lines)
Line 0 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    unless( foreign_key_exists( 'messages', 'borrowernumber' ) ) {
5
        $dbh->do(q|
6
            DELETE m FROM messages m
7
            LEFT JOIN borrowers b ON m.borrowernumber=b.borrowernumber
8
            WHERE b.borrowernumber IS NULL
9
        |);
10
11
        $dbh->do(q|
12
            ALTER TABLE messages
13
            ADD CONSTRAINT messages_borrowernumber
14
            FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
15
        |);
16
    }
17
18
    SetVersion( $DBversion );
19
    print "Upgrade to $DBversion done (Bug 13515 - Add a FOREIGN KEY constaint on messages.borrowernumber)\n";
20
}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +2 lines)
Lines 2694-2700 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2694
  `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
2694
  `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
2695
  `manager_id` int(11) default NULL, -- creator of message
2695
  `manager_id` int(11) default NULL, -- creator of message
2696
  PRIMARY KEY (`message_id`),
2696
  PRIMARY KEY (`message_id`),
2697
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL
2697
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL,
2698
  CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2698
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2699
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2699
2700
2700
--
2701
--
2701
- 

Return to bug 13515