Attachment #85155 for bug #13515

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




$DBversion = 'XXX';  # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {

    unless( foreign_key_exists( 'messages', 'borrowernumber' ) ) {
        $dbh->do(q|
            DELETE m FROM messages m
            LEFT JOIN borrowers b ON m.borrowernumber=b.borrowernumber
            WHERE b.borrowernumber IS NULL
        |);

        $dbh->do(q|
            ALTER TABLE messages
            ADD CONSTRAINT messages_borrowernumber
            FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
        |);
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 13515 - Add a FOREIGN KEY constaint on messages.borrowernumber)\n";
}

Lines 2695-2701 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here

(-)a/installer/data/mysql/kohastructure.sql (-2 / +2 lines)
2695	`message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written	2695	`message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
2696	`manager_id` int(11) default NULL, -- creator of message	2696	`manager_id` int(11) default NULL, -- creator of message
2697	PRIMARY KEY (`message_id`),	2697	PRIMARY KEY (`message_id`),
2698	CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL	2698	CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL,
		2699	CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2699	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2700	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2700		2701
2701	--	2702	--
2702	-

Return to bug 13515

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_13515.perl (+20 lines)
	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	}