From c08922180e83b0222dc3d54a292aaf49cc15000a Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 20 Sep 2016 16:55:40 +0100 Subject: [PATCH] Bug 17234: Add constraint_exists and column_exists to updatedatabase.pl These 2 subroutines will help us deal with the absense of ALTER IGNORE TABLE Signed-off-by: Mark Tompsett Signed-off-by: Mark Tompsett Signed-off-by: Kyle M Hall --- installer/data/mysql/updatedatabase.pl | 219 ++++++++++++++++--------- 1 file changed, 146 insertions(+), 73 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 47b18d8ce1..2bf6319cae 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -10422,15 +10422,21 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.19.00.041"; if ( CheckVersion($DBversion) ) { - $dbh->do(q| - ALTER TABLE suggestions ADD KEY status (STATUS) - |); - $dbh->do(q| - ALTER TABLE suggestions ADD KEY biblionumber (biblionumber) - |); - $dbh->do(q| - ALTER TABLE suggestions ADD KEY branchcode (branchcode) - |); + 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); } @@ -10444,12 +10450,14 @@ if ( CheckVersion($DBversion) ) { WHERE auth_types.authtypecode IS NULL }); - $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 - }); + 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); @@ -10565,31 +10573,58 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.007"; if ( CheckVersion($DBversion) ) { - $dbh->do(q| - ALTER TABLE aqbasket - ADD KEY authorisedby (authorisedby) - |); - $dbh->do(q| - ALTER TABLE aqbooksellers - ADD KEY name (name(255)) - |); - $dbh->do(q| - ALTER TABLE aqbudgets - ADD KEY budget_parent_id (budget_parent_id), - ADD KEY budget_code (budget_code), - ADD KEY budget_branchcode (budget_branchcode), - ADD KEY budget_period_id (budget_period_id), - ADD KEY budget_owner_id (budget_owner_id) - |); - $dbh->do(q| - ALTER TABLE aqbudgets_planning - ADD KEY budget_period_id (budget_period_id) - |); - $dbh->do(q| - ALTER TABLE aqorders - ADD KEY parent_ordernumber (parent_ordernumber), - ADD KEY orderstatus (orderstatus) - |); + 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); } @@ -10690,9 +10725,12 @@ if ( CheckVersion($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') }); - $dbh->do(q| - ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1 - |); + + 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); } @@ -10773,12 +10811,14 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.019"; if ( CheckVersion($DBversion) ) { - $dbh->do(q{ - ALTER TABLE reserves DROP constrainttype - }); - $dbh->do(q{ - ALTER TABLE old_reserves DROP constrainttype - }); + 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 }); @@ -10818,8 +10858,10 @@ if ( CheckVersion($DBversion) ) { my ($print_error) = $dbh->{PrintError}; $dbh->{RaiseError} = 0; $dbh->{PrintError} = 0; - $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}); + 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{ @@ -10917,28 +10959,32 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.028"; if ( CheckVersion($DBversion) ) { - $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 - }); + 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) ) { - $dbh->do(q{ - ALTER TABLE discharges - ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST - }); + 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); } @@ -12983,12 +13029,14 @@ if ( CheckVersion($DBversion) ) { $DBversion = "16.06.00.027"; if ( CheckVersion($DBversion) ) { - $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; - }); + 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'); }); @@ -13849,4 +13897,29 @@ sub CheckVersion { } } +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; -- 2.39.5