Attachment #83366 for bug #21065

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




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

    my $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_1'|);
    $sth->execute;
    if ($sth->fetchrow_hashref) {
        $dbh->do(q|
            ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1;
        |);
        $dbh->do(q|
            ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL;
        |);
        $dbh->do(q|
            ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_borrowers FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE;
        |);
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 21065 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n";

Lines 2722-2728 CREATE TABLE `accountlines` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-2 / +1 lines)
2722	KEY `acctsborridx` (`borrowernumber`),	2722	KEY `acctsborridx` (`borrowernumber`),
2723	KEY `timeidx` (`timestamp`),	2723	KEY `timeidx` (`timestamp`),
2724	KEY `itemnumber` (`itemnumber`),	2724	KEY `itemnumber` (`itemnumber`),
2725	CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2725	CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2726	CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL	2726	CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2727	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2727	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2728		2728
2729	-

Return to bug 21065

Lines 1-15 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_21065.perl (-9 / +13 lines)
1	$DBversion = 'XXX'; # will be replaced by the RM	1	$DBversion = 'XXX'; # will be replaced by the RM
2	if( CheckVersion( $DBversion ) ) {	2	if( CheckVersion( $DBversion ) ) {
3		3
4	$dbh->do(q\|	4	my $sth = $dbh->prepare(q\|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_1'\|);
5	ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1;	5	$sth->execute;
6	\|);	6	if ($sth->fetchrow_hashref) {
7	$dbh->do(q\|	7	$dbh->do(q\|
8	ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL;	8	ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1;
9	\|);	9	\|);
10	$dbh->do(q\|	10	$dbh->do(q\|
11	ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE;	11	ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL;
12	\|);	12	\|);
		13	$dbh->do(q\|
		14	ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_borrowers FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE;
		15	\|);
		16	}
13		17
14	SetVersion( $DBversion );	18	SetVersion( $DBversion );
15	print "Upgrade to $DBversion done (Bug 21065 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n";	19	print "Upgrade to $DBversion done (Bug 21065 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n";