Attachment #169403 for bug #18086

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    $dbh->do(q{
        UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
    });

    $dbh->do(q{
        DELETE overduerules FROM overduerules
        LEFT JOIN categories USING ( categorycode )
        WHERE
            overduerules.categorycode IS NOT NULL
          AND
            categories.categorycode IS NULL;
    });

    $dbh->do(q{
        UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
    });

    $dbh->do(q{
        DELETE overduerules FROM overduerules
        LEFT JOIN branches USING ( branchcode )
        WHERE
            branches.branchcode IS NULL
          AND
            overduerules.branchcode IS NOT NULL
    });

    $dbh->do(q{
        ALTER TABLE overduerules
        MODIFY `branchcode` varchar(10) NULL default NULL,
        MODIFY `categorycode` varchar(10) NOT NULL,
        ADD CONSTRAINT `fk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
        ADD CONSTRAINT `fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
    });

    NewVersion( $DBversion, 18086, "Add FK constraints for branchcode and categorycode");
}




use Modern::Perl;
use Koha::Installer::Output qw(say_warning say_failure say_success say_info);

return {
    bug_number  => "18086",
    description => "Add foreign key constraints to table overduerule",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        unless ( foreign_key_exists( 'overduerules', 'branchcode_ibfk' ) ) {
            $dbh->do(q{
                ALTER TABLE overduerules
                MODIFY `branchcode` varchar(10) NULL default NULL;
            });

            $dbh->do(q{
                UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
            });

            $dbh->do(q{
                DELETE overduerules FROM overduerules
                WHERE
                    branchcode NOT IN (SELECT branchcode FROM branches);
            });

            $dbh->do(q{
                ALTER TABLE overduerules
                ADD CONSTRAINT `branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
            });
            say $out "Added constraint to column of table 'overduerules.branchcode'";
        }


        unless ( foreign_key_exists( 'overduerules', 'categorycode_ibfk' ) ) {
            $dbh->do(q{
                ALTER TABLE overduerules
                MODIFY `categorycode` varchar(10) NULL default NULL;
            });

            $dbh->do(q{
                ALTER TABLE overduerules
                ADD CONSTRAINT `categorycode_ibfk` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
            });

            $dbh->do(q{
                UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
            });

            $dbh->do(q{
                DELETE overduerules FROM overduerules
                WHERE
                    categorycode NOT IN (SELECT categorycode FROM categories);
            });

            say $out "Added constraint to column of table 'overduerules.categorycode'";


        }

    },
};

Lines 4975-4982 CREATE TABLE `overduerules` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-3 / +2 lines)
4975	`debarred3` int(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',	4975	`debarred3` int(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',
4976	PRIMARY KEY (`overduerules_id`),	4976	PRIMARY KEY (`overduerules_id`),
4977	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`),	4977	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`),
4978	CONSTRAINT `fk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,	4978	CONSTRAINT `branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
4979	CONSTRAINT `fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE	4979	CONSTRAINT `categorycode_ibfk` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
4980	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	4980	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4981	/!40101 SET character_set_client = @saved_cs_client /;	4981	/!40101 SET character_set_client = @saved_cs_client /;
4982		4982
4983	-

Return to bug 18086

Lines 1-38 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_18086.perl (-38 lines)
1	$DBversion = 'XXX'; # will be replaced by the RM
2	if( CheckVersion( $DBversion ) ) {
3	$dbh->do(q{
4	UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
5	});
6
7	$dbh->do(q{
8	DELETE overduerules FROM overduerules
9	LEFT JOIN categories USING ( categorycode )
10	WHERE
11	overduerules.categorycode IS NOT NULL
12	AND
13	categories.categorycode IS NULL;
14	});
15
16	$dbh->do(q{
17	UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
18	});
19
20	$dbh->do(q{
21	DELETE overduerules FROM overduerules
22	LEFT JOIN branches USING ( branchcode )
23	WHERE
24	branches.branchcode IS NULL
25	AND
26	overduerules.branchcode IS NOT NULL
27	});
28
29	$dbh->do(q{
30	ALTER TABLE overduerules
31	MODIFY `branchcode` varchar(10) NULL default NULL,
32	MODIFY `categorycode` varchar(10) NOT NULL,
33	ADD CONSTRAINT `fk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
34	ADD CONSTRAINT `fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
35	});
36
37	NewVersion( $DBversion, 18086, "Add FK constraints for branchcode and categorycode");
38	}

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_18086.pl (+62 lines)
	1	use Modern::Perl;
	2	use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
	3
	4	return {
	5	bug_number => "18086",
	6	description => "Add foreign key constraints to table overduerule",
	7	up => sub {
	8	my ($args) = @_;
	9	my ( $dbh, $out ) = @$args{qw(dbh out)};
	10
	11	unless ( foreign_key_exists( 'overduerules', 'branchcode_ibfk' ) ) {
	12	$dbh->do(q{
	13	ALTER TABLE overduerules
	14	MODIFY `branchcode` varchar(10) NULL default NULL;
	15	});
	16
	17	$dbh->do(q{
	18	UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
	19	});
	20
	21	$dbh->do(q{
	22	DELETE overduerules FROM overduerules
	23	WHERE
	24	branchcode NOT IN (SELECT branchcode FROM branches);
	25	});
	26
	27	$dbh->do(q{
	28	ALTER TABLE overduerules
	29	ADD CONSTRAINT `branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
	30	});
	31	say $out "Added constraint to column of table 'overduerules.branchcode'";
	32	}
	33
	34
	35	unless ( foreign_key_exists( 'overduerules', 'categorycode_ibfk' ) ) {
	36	$dbh->do(q{
	37	ALTER TABLE overduerules
	38	MODIFY `categorycode` varchar(10) NULL default NULL;
	39	});
	40
	41	$dbh->do(q{
	42	ALTER TABLE overduerules
	43	ADD CONSTRAINT `categorycode_ibfk` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
	44	});
	45
	46	$dbh->do(q{
	47	UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
	48	});
	49
	50	$dbh->do(q{
	51	DELETE overduerules FROM overduerules
	52	WHERE
	53	categorycode NOT IN (SELECT categorycode FROM categories);
	54	});
	55
	56	say $out "Added constraint to column of table 'overduerules.categorycode'";
	57
	58
	59	}
	60
	61	},
	62	};