Attachment #55722 for bug #17234

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





$DBversion = "3.19.00.041";
if ( CheckVersion($DBversion) ) {
    unless ( constraint_exists( 'suggestions', 'status' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY status (STATUS)
        |);
    }
    unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
        |);
    }
    unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {
        $dbh->do(q|
            ALTER TABLE suggestions ADD KEY branchcode (branchcode)
        |);
    }
    print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
    SetVersion ($DBversion);
}

        WHERE auth_types.authtypecode IS NULL
    });

    unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
        $dbh->do(q{
            ALTER TABLE auth_subfield_structure
            ADD CONSTRAINT auth_subfield_structure_ibfk_1
            FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
            ON DELETE CASCADE ON UPDATE CASCADE
        });
    }

    print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
    SetVersion($DBversion);


$DBversion = "3.21.00.007";
if ( CheckVersion($DBversion) ) {
    unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbasket
                ADD KEY authorisedby (authorisedby)
        |);
    }
    unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbooksellers
                ADD KEY name (name(255))
        |);
    }
    unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_parent_id (budget_parent_id)|);
        }
    unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_code (budget_code)|);
    }
    unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_branchcode (budget_branchcode)|);
    }
    unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_period_id (budget_period_id)|);
    }
    unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets
                ADD KEY budget_owner_id (budget_owner_id)|);
    }
    unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
        $dbh->do(q|
            ALTER TABLE aqbudgets_planning
                ADD KEY budget_period_id (budget_period_id)|);
    }
    unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {
        $dbh->do(q|
            ALTER TABLE aqorders
                ADD KEY parent_ordernumber (parent_ordernumber)|);
    }
    unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {
        $dbh->do(q|
            ALTER TABLE aqorders
                ADD KEY orderstatus (orderstatus)|);
    }
    print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
    SetVersion ($DBversion);
}

        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
        VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
    });

    if ( constraint_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
        |);
    }
    print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
    SetVersion ($DBversion);
}


$DBversion = "3.21.00.019";
if ( CheckVersion($DBversion) ) {
    if ( column_exists( 'reserves', 'constrainttype' ) ) {
        $dbh->do(q{
            ALTER TABLE reserves DROP constrainttype
        });
        $dbh->do(q{
            ALTER TABLE old_reserves DROP constrainttype
        });
    }
    $dbh->do(q{
        DROP TABLE IF EXISTS reserveconstraints
    });

    my ($print_error) = $dbh->{PrintError};
    $dbh->{RaiseError} = 0;
    $dbh->{PrintError} = 0;
    if ( constraint_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});
    }
    $dbh->{PrintError} = $print_error;

    $dbh->do(q{


$DBversion = "3.21.00.028";
if ( CheckVersion($DBversion) ) {
    unless ( column_exists('uploaded_files', 'public') ) {
        $dbh->do(q{
            ALTER TABLE uploaded_files
                ADD COLUMN public tinyint,
                ADD COLUMN permanent tinyint
        });
        $dbh->do(q{
            UPDATE uploaded_files SET public=1, permanent=1
        });
        $dbh->do(q{
            ALTER TABLE uploaded_files
                CHANGE COLUMN categorycode uploadcategorycode tinytext
        });
    }
    print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
    SetVersion($DBversion);
}

$DBversion = "3.21.00.029";
if ( CheckVersion($DBversion) ) {
    unless ( column_exists('discharges', 'discharge_id') ) {
        $dbh->do(q{
            ALTER TABLE discharges
                ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
        });
    }
    print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
    SetVersion($DBversion);
}


$DBversion = "16.06.00.027";
if ( CheckVersion($DBversion) ) {
    unless ( column_exists('borrowers', 'lastseen') ) {
        $dbh->do(q{
            ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
        });
        $dbh->do(q{
            ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
        });
    }
    $dbh->do(q{
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
    });

    }
}

sub constraint_exists {
    my ( $table_name, $key_name ) = @_;
    my $dbh = C4::Context->dbh;
    my ($exists) = $dbh->selectrow_array(
        qq|
        SHOW INDEX FROM $table_name
        WHERE key_name = ?
        |, undef, $key_name
    );
    return $exists;
}

sub column_exists {
    my ( $table_name, $column_name ) = @_;
    my $dbh = C4::Context->dbh;
    my ($exists) = $dbh->selectrow_array(
        qq|
        SHOW COLUMNS FROM $table_name
        WHERE Field = ?
        |, undef, $column_name
    );
    return $exists;

}

exit;
- 

Return to bug 17234

Lines 10420-10434 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (-74 / +146 lines)
10420		10420
10421	$DBversion = "3.19.00.041";	10421	$DBversion = "3.19.00.041";
10422	if ( CheckVersion($DBversion) ) {	10422	if ( CheckVersion($DBversion) ) {
10423	$dbh->do(q\|	10423	unless ( constraint_exists( 'suggestions', 'status' ) ) {
10424	ALTER TABLE suggestions ADD KEY status (STATUS)	10424	$dbh->do(q\|
10425	\|);	10425	ALTER TABLE suggestions ADD KEY status (STATUS)
10426	$dbh->do(q\|	10426	\|);
10427	ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)	10427	}
10428	\|);	10428	unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {
10429	$dbh->do(q\|	10429	$dbh->do(q\|
10430	ALTER TABLE suggestions ADD KEY branchcode (branchcode)	10430	ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
10431	\|);	10431	\|);
		10432	}
		10433	unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {
		10434	$dbh->do(q\|
		10435	ALTER TABLE suggestions ADD KEY branchcode (branchcode)
		10436	\|);
		10437	}
10432	print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";	10438	print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
10433	SetVersion ($DBversion);	10439	SetVersion ($DBversion);
10434	}	10440	}
Lines 10442-10453 if ( CheckVersion($DBversion) ) { Link Here
10442	WHERE auth_types.authtypecode IS NULL	10448	WHERE auth_types.authtypecode IS NULL
10443	});	10449	});
10444		10450
10445	$dbh->do(q{	10451	unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
10446	ALTER TABLE auth_subfield_structure	10452	$dbh->do(q{
10447	ADD CONSTRAINT auth_subfield_structure_ibfk_1	10453	ALTER TABLE auth_subfield_structure
10448	FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)	10454	ADD CONSTRAINT auth_subfield_structure_ibfk_1
10449	ON DELETE CASCADE ON UPDATE CASCADE	10455	FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
10450	});	10456	ON DELETE CASCADE ON UPDATE CASCADE
		10457	});
		10458	}
10451		10459
10452	print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";	10460	print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
10453	SetVersion($DBversion);	10461	SetVersion($DBversion);
Lines 10563-10593 if ( CheckVersion($DBversion) ) { Link Here
10563		10571
10564	$DBversion = "3.21.00.007";	10572	$DBversion = "3.21.00.007";
10565	if ( CheckVersion($DBversion) ) {	10573	if ( CheckVersion($DBversion) ) {
10566	$dbh->do(q\|	10574	unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {
10567	ALTER TABLE aqbasket	10575	$dbh->do(q\|
10568	ADD KEY authorisedby (authorisedby)	10576	ALTER TABLE aqbasket
10569	\|);	10577	ADD KEY authorisedby (authorisedby)
10570	$dbh->do(q\|	10578	\|);
10571	ALTER TABLE aqbooksellers	10579	}
10572	ADD KEY name (name(255))	10580	unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {
10573	\|);	10581	$dbh->do(q\|
10574	$dbh->do(q\|	10582	ALTER TABLE aqbooksellers
10575	ALTER TABLE aqbudgets	10583	ADD KEY name (name(255))
10576	ADD KEY budget_parent_id (budget_parent_id),	10584	\|);
10577	ADD KEY budget_code (budget_code),	10585	}
10578	ADD KEY budget_branchcode (budget_branchcode),	10586	unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {
10579	ADD KEY budget_period_id (budget_period_id),	10587	$dbh->do(q\|
10580	ADD KEY budget_owner_id (budget_owner_id)	10588	ALTER TABLE aqbudgets
10581	\|);	10589	ADD KEY budget_parent_id (budget_parent_id)\|);
10582	$dbh->do(q\|	10590	}
10583	ALTER TABLE aqbudgets_planning	10591	unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {
10584	ADD KEY budget_period_id (budget_period_id)	10592	$dbh->do(q\|
10585	\|);	10593	ALTER TABLE aqbudgets
10586	$dbh->do(q\|	10594	ADD KEY budget_code (budget_code)\|);
10587	ALTER TABLE aqorders	10595	}
10588	ADD KEY parent_ordernumber (parent_ordernumber),	10596	unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {
10589	ADD KEY orderstatus (orderstatus)	10597	$dbh->do(q\|
10590	\|);	10598	ALTER TABLE aqbudgets
		10599	ADD KEY budget_branchcode (budget_branchcode)\|);
		10600	}
		10601	unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {
		10602	$dbh->do(q\|
		10603	ALTER TABLE aqbudgets
		10604	ADD KEY budget_period_id (budget_period_id)\|);
		10605	}
		10606	unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {
		10607	$dbh->do(q\|
		10608	ALTER TABLE aqbudgets
		10609	ADD KEY budget_owner_id (budget_owner_id)\|);
		10610	}
		10611	unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
		10612	$dbh->do(q\|
		10613	ALTER TABLE aqbudgets_planning
		10614	ADD KEY budget_period_id (budget_period_id)\|);
		10615	}
		10616	unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {
		10617	$dbh->do(q\|
		10618	ALTER TABLE aqorders
		10619	ADD KEY parent_ordernumber (parent_ordernumber)\|);
		10620	}
		10621	unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {
		10622	$dbh->do(q\|
		10623	ALTER TABLE aqorders
		10624	ADD KEY orderstatus (orderstatus)\|);
		10625	}
10591	print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";	10626	print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
10592	SetVersion ($DBversion);	10627	SetVersion ($DBversion);
10593	}	10628	}
Lines 10688-10696 if ( CheckVersion($DBversion) ) { Link Here
10688	INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)	10723	INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
10689	VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient\|persistent','Choice')	10724	VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient\|persistent','Choice')
10690	});	10725	});
10691	$dbh->do(q\|	10726
10692	ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1	10727	if ( constraint_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
10693	\|);	10728	$dbh->do(q\|
		10729	ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
		10730	\|);
		10731	}
10694	print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";	10732	print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
10695	SetVersion ($DBversion);	10733	SetVersion ($DBversion);
10696	}	10734	}
Lines 10771-10782 if ( CheckVersion($DBversion) ) { Link Here
10771		10809
10772	$DBversion = "3.21.00.019";	10810	$DBversion = "3.21.00.019";
10773	if ( CheckVersion($DBversion) ) {	10811	if ( CheckVersion($DBversion) ) {
10774	$dbh->do(q{	10812	if ( column_exists( 'reserves', 'constrainttype' ) ) {
10775	ALTER TABLE reserves DROP constrainttype	10813	$dbh->do(q{
10776	});	10814	ALTER TABLE reserves DROP constrainttype
10777	$dbh->do(q{	10815	});
10778	ALTER TABLE old_reserves DROP constrainttype	10816	$dbh->do(q{
10779	});	10817	ALTER TABLE old_reserves DROP constrainttype
		10818	});
		10819	}
10780	$dbh->do(q{	10820	$dbh->do(q{
10781	DROP TABLE IF EXISTS reserveconstraints	10821	DROP TABLE IF EXISTS reserveconstraints
10782	});	10822	});
Lines 10816-10823 if ( CheckVersion($DBversion) ) { Link Here
10816	my ($print_error) = $dbh->{PrintError};	10856	my ($print_error) = $dbh->{PrintError};
10817	$dbh->{RaiseError} = 0;	10857	$dbh->{RaiseError} = 0;
10818	$dbh->{PrintError} = 0;	10858	$dbh->{PrintError} = 0;
10819	$dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});	10859	if ( constraint_exists('course_reserves', 'course_reserves_ibfk_2') ) {
10820	$dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});	10860	$dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
		10861	$dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
		10862	}
10821	$dbh->{PrintError} = $print_error;	10863	$dbh->{PrintError} = $print_error;
10822		10864
10823	$dbh->do(q{	10865	$dbh->do(q{
Lines 10915-10942 if ( CheckVersion($DBversion) ) { Link Here
10915		10957
10916	$DBversion = "3.21.00.028";	10958	$DBversion = "3.21.00.028";
10917	if ( CheckVersion($DBversion) ) {	10959	if ( CheckVersion($DBversion) ) {
10918	$dbh->do(q{	10960	unless ( column_exists('uploaded_files', 'public') ) {
10919	ALTER TABLE uploaded_files	10961	$dbh->do(q{
10920	ADD COLUMN public tinyint,	10962	ALTER TABLE uploaded_files
10921	ADD COLUMN permanent tinyint	10963	ADD COLUMN public tinyint,
10922	});	10964	ADD COLUMN permanent tinyint
10923	$dbh->do(q{	10965	});
10924	UPDATE uploaded_files SET public=1, permanent=1	10966	$dbh->do(q{
10925	});	10967	UPDATE uploaded_files SET public=1, permanent=1
10926	$dbh->do(q{	10968	});
10927	ALTER TABLE uploaded_files	10969	$dbh->do(q{
10928	CHANGE COLUMN categorycode uploadcategorycode tinytext	10970	ALTER TABLE uploaded_files
10929	});	10971	CHANGE COLUMN categorycode uploadcategorycode tinytext
		10972	});
		10973	}
10930	print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";	10974	print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
10931	SetVersion($DBversion);	10975	SetVersion($DBversion);
10932	}	10976	}
10933		10977
10934	$DBversion = "3.21.00.029";	10978	$DBversion = "3.21.00.029";
10935	if ( CheckVersion($DBversion) ) {	10979	if ( CheckVersion($DBversion) ) {
10936	$dbh->do(q{	10980	unless ( column_exists('discharges', 'discharge_id') ) {
10937	ALTER TABLE discharges	10981	$dbh->do(q{
10938	ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST	10982	ALTER TABLE discharges
10939	});	10983	ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
		10984	});
		10985	}
10940	print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";	10986	print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
10941	SetVersion($DBversion);	10987	SetVersion($DBversion);
10942	}	10988	}
Lines 12983-12994 if ( CheckVersion($DBversion) ) { Link Here
12983		13029
12984	$DBversion = "16.06.00.027";	13030	$DBversion = "16.06.00.027";
12985	if ( CheckVersion($DBversion) ) {	13031	if ( CheckVersion($DBversion) ) {
12986	$dbh->do(q{	13032	unless ( column_exists('borrowers', 'lastseen') ) {
12987	ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;	13033	$dbh->do(q{
12988	});	13034	ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
12989	$dbh->do(q{	13035	});
12990	ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;	13036	$dbh->do(q{
12991	});	13037	ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
		13038	});
		13039	}
12992	$dbh->do(q{	13040	$dbh->do(q{
12993	INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');	13041	INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
12994	});	13042	});
Lines 13144-13147 sub CheckVersion { Link Here
13144	}	13192	}
13145	}	13193	}
13146		13194
		13195	sub constraint_exists {
		13196	my ( $table_name, $key_name ) = @_;
		13197	my $dbh = C4::Context->dbh;
		13198	my ($exists) = $dbh->selectrow_array(
		13199	qq\|
		13200	SHOW INDEX FROM $table_name
		13201	WHERE key_name = ?
		13202	\|, undef, $key_name
		13203	);
		13204	return $exists;
		13205	}
		13206
		13207	sub column_exists {
		13208	my ( $table_name, $column_name ) = @_;
		13209	my $dbh = C4::Context->dbh;
		13210	my ($exists) = $dbh->selectrow_array(
		13211	qq\|
		13212	SHOW COLUMNS FROM $table_name
		13213	WHERE Field = ?
		13214	\|, undef, $column_name
		13215	);
		13216	return $exists;
		13217
		13218	}
		13219
13147	exit;	13220	exit;
13148	-