Attachment #58865 for bug #17234

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




BEGIN {
    require Exporter;
    @ISA = qw( Exporter );
    push @EXPORT, qw( foreign_key_exists index_exists column_exists );
};

=head1 NAME


}

sub foreign_key_exists {
    my ( $table_name, $constraint_name ) = @_;
    my $dbh = C4::Context->dbh;
    my (undef, $infos) = $dbh->selectrow_array(qq|SHOW CREATE TABLE $table_name|);
    return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY|;
}

sub index_exists {
    my ( $table_name, $key_name ) = @_;
    my $dbh = C4::Context->dbh;
    my ($exists) = $dbh->selectrow_array(





$DBversion = "3.19.00.041";
if ( CheckVersion($DBversion) ) {
    unless ( index_exists( 'suggestions', 'status' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY status (STATUS)
        |);
    }
    unless ( index_exists( 'suggestions', 'biblionumber' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
        |);
    }
    unless ( index_exists( 'suggestions', 'branchcode' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY branchcode (branchcode)
        |);

        WHERE auth_types.authtypecode IS NULL
    });

    unless ( foreign_key_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
        $dbh->do(q{
            ALTER TABLE auth_subfield_structure
            ADD CONSTRAINT auth_subfield_structure_ibfk_1


$DBversion = "3.21.00.007";
if ( CheckVersion($DBversion) ) {
    unless ( index_exists( 'aqbasket', 'authorisedby' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbasket
                ADD KEY authorisedby (authorisedby)
        |);
    }
    unless ( index_exists( 'aqbooksellers', 'name' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbooksellers
                ADD KEY name (name(255))
        |);
    }
    unless ( index_exists( 'aqbudgets', 'budget_parent_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_parent_id (budget_parent_id)|);
        }
    unless ( index_exists( 'aqbudgets', 'budget_code' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_code (budget_code)|);
    }
    unless ( index_exists( 'aqbudgets', 'budget_branchcode' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_branchcode (budget_branchcode)|);
    }
    unless ( index_exists( 'aqbudgets', 'budget_period_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_period_id (budget_period_id)|);
    }
    unless ( index_exists( 'aqbudgets', 'budget_owner_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_owner_id (budget_owner_id)|);
    }
    unless ( index_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets_planning
                ADD KEY budget_period_id (budget_period_id)|);
    }
    unless ( index_exists( 'aqorders', 'parent_ordernumber' ) ) {
        $dbh->do(q|
            ALTER TABLE aqorders
                ADD KEY parent_ordernumber (parent_ordernumber)|);
    }
    unless ( index_exists( 'aqorders', 'orderstatus' ) ) {
        $dbh->do(q|
            ALTER TABLE aqorders
                ADD KEY orderstatus (orderstatus)|);

        VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
    });

    if ( foreign_key_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
        $dbh->do(q|
            ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
        |);

    my ($print_error) = $dbh->{PrintError};
    $dbh->{RaiseError} = 0;
    $dbh->{PrintError} = 0;
    if ( foreign_key_exists('course_reserves', 'course_reserves_ibfk_2') ) {
        $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
        $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
    }

Lines 22-28 Link Here

(-)a/t/db_dependent/Installer.t (-4 / +6 lines)
22	# Add more tests here!!!	22	# Add more tests here!!!
23		23
24	use Modern::Perl;	24	use Modern::Perl;
25	use Test::More tests => 13;	25	use Test::More tests => 15;
26	use Koha::Database;	26	use Koha::Database;
27		27
28	BEGIN {	28	BEGIN {
Lines 59-63 ok( ! column_exists( 'borrowers', 'xxx'), 'Column xxx does not exist' ); Link Here
59		59
60	my @constraint_names = $source->unique_constraint_names();	60	my @constraint_names = $source->unique_constraint_names();
61	my $constraint_name = $constraint_names[0];	61	my $constraint_name = $constraint_names[0];
62	ok( constraint_exists( 'borrowers', $constraint_name), 'Known contraint does exist' );	62	ok( index_exists( 'borrowers', $constraint_name), 'Known contraint does exist' );
63	ok( ! constraint_exists( 'borrowers', 'xxx'), 'Constraint xxx does not exist' );	63	ok( ! index_exists( 'borrowers', 'xxx'), 'Constraint xxx does not exist' );
		64
		65	ok( foreign_key_exists( 'borrowers', 'borrowers_ibfk_1' ), 'FK borrowers_ibfk_1 exists' );
		66	ok( ! foreign_key_exists( 'borrowers', 'xxx' ), 'FK xxxx does not exist' );
64	-

Return to bug 17234

Lines 30-36 use vars qw(@ISA @EXPORT); Link Here

(-)a/C4/Installer.pm (-2 / +9 lines)
30	BEGIN {	30	BEGIN {
31	require Exporter;	31	require Exporter;
32	@ISA = qw( Exporter );	32	@ISA = qw( Exporter );
33	push @EXPORT, qw( constraint_exists column_exists );	33	push @EXPORT, qw( foreign_key_exists index_exists column_exists );
34	};	34	};
35		35
36	=head1 NAME	36	=head1 NAME
Lines 498-504 sub get_file_path_from_name { Link Here
498		498
499	}	499	}
500		500
501	sub constraint_exists {	501	sub foreign_key_exists {
		502	my ( $table_name, $constraint_name ) = @_;
		503	my $dbh = C4::Context->dbh;
		504	my (undef, $infos) = $dbh->selectrow_array(qq\|SHOW CREATE TABLE $table_name\|);
		505	return $infos =~ m\|CONSTRAINT `$constraint_name` FOREIGN KEY\|;
		506	}
		507
		508	sub index_exists {
502	my ( $table_name, $key_name ) = @_;	509	my ( $table_name, $key_name ) = @_;
503	my $dbh = C4::Context->dbh;	510	my $dbh = C4::Context->dbh;
504	my ($exists) = $dbh->selectrow_array(	511	my ($exists) = $dbh->selectrow_array(

Lines 10422-10438 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (-16 / +16 lines)
10422		10422
10423	$DBversion = "3.19.00.041";	10423	$DBversion = "3.19.00.041";
10424	if ( CheckVersion($DBversion) ) {	10424	if ( CheckVersion($DBversion) ) {
10425	unless ( constraint_exists( 'suggestions', 'status' ) ) {	10425	unless ( index_exists( 'suggestions', 'status' ) ) {
10426	$dbh->do(q\|	10426	$dbh->do(q\|
10427	ALTER TABLE suggestions ADD KEY status (STATUS)	10427	ALTER TABLE suggestions ADD KEY status (STATUS)
10428	\|);	10428	\|);
10429	}	10429	}
10430	unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {	10430	unless ( index_exists( 'suggestions', 'biblionumber' ) ) {
10431	$dbh->do(q\|	10431	$dbh->do(q\|
10432	ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)	10432	ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
10433	\|);	10433	\|);
10434	}	10434	}
10435	unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {	10435	unless ( index_exists( 'suggestions', 'branchcode' ) ) {
10436	$dbh->do(q\|	10436	$dbh->do(q\|
10437	ALTER TABLE suggestions ADD KEY branchcode (branchcode)	10437	ALTER TABLE suggestions ADD KEY branchcode (branchcode)
10438	\|);	10438	\|);
Lines 10450-10456 if ( CheckVersion($DBversion) ) { Link Here
10450	WHERE auth_types.authtypecode IS NULL	10450	WHERE auth_types.authtypecode IS NULL
10451	});	10451	});
10452		10452
10453	unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {	10453	unless ( foreign_key_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
10454	$dbh->do(q{	10454	$dbh->do(q{
10455	ALTER TABLE auth_subfield_structure	10455	ALTER TABLE auth_subfield_structure
10456	ADD CONSTRAINT auth_subfield_structure_ibfk_1	10456	ADD CONSTRAINT auth_subfield_structure_ibfk_1
Lines 10573-10626 if ( CheckVersion($DBversion) ) { Link Here
10573		10573
10574	$DBversion = "3.21.00.007";	10574	$DBversion = "3.21.00.007";
10575	if ( CheckVersion($DBversion) ) {	10575	if ( CheckVersion($DBversion) ) {
10576	unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {	10576	unless ( index_exists( 'aqbasket', 'authorisedby' ) ) {
10577	$dbh->do(q\|	10577	$dbh->do(q\|
10578	ALTER TABLE aqbasket	10578	ALTER TABLE aqbasket
10579	ADD KEY authorisedby (authorisedby)	10579	ADD KEY authorisedby (authorisedby)
10580	\|);	10580	\|);
10581	}	10581	}
10582	unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {	10582	unless ( index_exists( 'aqbooksellers', 'name' ) ) {
10583	$dbh->do(q\|	10583	$dbh->do(q\|
10584	ALTER TABLE aqbooksellers	10584	ALTER TABLE aqbooksellers
10585	ADD KEY name (name(255))	10585	ADD KEY name (name(255))
10586	\|);	10586	\|);
10587	}	10587	}
10588	unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {	10588	unless ( index_exists( 'aqbudgets', 'budget_parent_id' ) ) {
10589	$dbh->do(q\|	10589	$dbh->do(q\|
10590	ALTER TABLE aqbudgets	10590	ALTER TABLE aqbudgets
10591	ADD KEY budget_parent_id (budget_parent_id)\|);	10591	ADD KEY budget_parent_id (budget_parent_id)\|);
10592	}	10592	}
10593	unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {	10593	unless ( index_exists( 'aqbudgets', 'budget_code' ) ) {
10594	$dbh->do(q\|	10594	$dbh->do(q\|
10595	ALTER TABLE aqbudgets	10595	ALTER TABLE aqbudgets
10596	ADD KEY budget_code (budget_code)\|);	10596	ADD KEY budget_code (budget_code)\|);
10597	}	10597	}
10598	unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {	10598	unless ( index_exists( 'aqbudgets', 'budget_branchcode' ) ) {
10599	$dbh->do(q\|	10599	$dbh->do(q\|
10600	ALTER TABLE aqbudgets	10600	ALTER TABLE aqbudgets
10601	ADD KEY budget_branchcode (budget_branchcode)\|);	10601	ADD KEY budget_branchcode (budget_branchcode)\|);
10602	}	10602	}
10603	unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {	10603	unless ( index_exists( 'aqbudgets', 'budget_period_id' ) ) {
10604	$dbh->do(q\|	10604	$dbh->do(q\|
10605	ALTER TABLE aqbudgets	10605	ALTER TABLE aqbudgets
10606	ADD KEY budget_period_id (budget_period_id)\|);	10606	ADD KEY budget_period_id (budget_period_id)\|);
10607	}	10607	}
10608	unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {	10608	unless ( index_exists( 'aqbudgets', 'budget_owner_id' ) ) {
10609	$dbh->do(q\|	10609	$dbh->do(q\|
10610	ALTER TABLE aqbudgets	10610	ALTER TABLE aqbudgets
10611	ADD KEY budget_owner_id (budget_owner_id)\|);	10611	ADD KEY budget_owner_id (budget_owner_id)\|);
10612	}	10612	}
10613	unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {	10613	unless ( index_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
10614	$dbh->do(q\|	10614	$dbh->do(q\|
10615	ALTER TABLE aqbudgets_planning	10615	ALTER TABLE aqbudgets_planning
10616	ADD KEY budget_period_id (budget_period_id)\|);	10616	ADD KEY budget_period_id (budget_period_id)\|);
10617	}	10617	}
10618	unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {	10618	unless ( index_exists( 'aqorders', 'parent_ordernumber' ) ) {
10619	$dbh->do(q\|	10619	$dbh->do(q\|
10620	ALTER TABLE aqorders	10620	ALTER TABLE aqorders
10621	ADD KEY parent_ordernumber (parent_ordernumber)\|);	10621	ADD KEY parent_ordernumber (parent_ordernumber)\|);
10622	}	10622	}
10623	unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {	10623	unless ( index_exists( 'aqorders', 'orderstatus' ) ) {
10624	$dbh->do(q\|	10624	$dbh->do(q\|
10625	ALTER TABLE aqorders	10625	ALTER TABLE aqorders
10626	ADD KEY orderstatus (orderstatus)\|);	10626	ADD KEY orderstatus (orderstatus)\|);
Lines 10726-10732 if ( CheckVersion($DBversion) ) { Link Here
10726	VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient\|persistent','Choice')	10726	VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient\|persistent','Choice')
10727	});	10727	});
10728		10728
10729	if ( constraint_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {	10729	if ( foreign_key_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
10730	$dbh->do(q\|	10730	$dbh->do(q\|
10731	ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1	10731	ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
10732	\|);	10732	\|);
Lines 10858-10864 if ( CheckVersion($DBversion) ) { Link Here
10858	my ($print_error) = $dbh->{PrintError};	10858	my ($print_error) = $dbh->{PrintError};
10859	$dbh->{RaiseError} = 0;	10859	$dbh->{RaiseError} = 0;
10860	$dbh->{PrintError} = 0;	10860	$dbh->{PrintError} = 0;
10861	if ( constraint_exists('course_reserves', 'course_reserves_ibfk_2') ) {	10861	if ( foreign_key_exists('course_reserves', 'course_reserves_ibfk_2') ) {
10862	$dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});	10862	$dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
10863	$dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});	10863	$dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
10864	}	10864	}