- Add a new -engineTypeQueryName method to SPServerSupport, with a correct split between TYPE or ENGINE depending on database version.

 - Update CREATE TABLE and ALERT TABLE queries to use this, fixing custom-type table creation on MySQL <4 and table type changes on MySQL >= 5.5.  This addresses Issue #947.

4 files changed, 14 insertions, 3 deletions

diff --git a/Source/SPExtendedTableInfo.m b/Source/SPExtendedTableInfo.m
index a765f6df..6f039659 100644
--- a/Source/SPExtendedTableInfo.m
+++ b/Source/SPExtendedTableInfo.m
@@ -98,7 +98,7 @@
 	}
 
 	// Alter table's storage type
-	[connection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ TYPE = %@", [selectedTable backtickQuotedString], newType]];
+	[connection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ %@ = %@", [selectedTable backtickQuotedString], [[tableDocumentInstance serverSupport] engineTypeQueryName], newType]];
 
 	if ([connection getLastErrorID] == 0) {
 		// Reload the table's data
diff --git a/Source/SPServerSupport.h b/Source/SPServerSupport.h
index fc16b426..bf18a8e8 100644
--- a/Source/SPServerSupport.h
+++ b/Source/SPServerSupport.h
@@ -65,6 +65,7 @@
 	BOOL supportsShowPrivileges;
 	
 	// Storage engines
+	NSString *engineTypeQueryName;
 	BOOL supportsInformationSchemaEngines;
 	BOOL supportsPre41StorageEngines;
 	BOOL supportsBlackholeStorageEngine;
@@ -172,6 +173,11 @@
 @property (readonly) BOOL supportsShowPrivileges;
 
 /**
+ * @property engineTypeQueryName Returns the appropriate query part for specifying table engine - ENGINE or TYPE
+ */
+@property (readonly) NSString *engineTypeQueryName;
+
+/**
  * @property supportsInformationSchemaEngines Indicates if the server supports the information_schema.engines table
  */
 @property (readonly) BOOL supportsInformationSchemaEngines;
diff --git a/Source/SPServerSupport.m b/Source/SPServerSupport.m
index 0b3ba79d..6f8213d6 100644
--- a/Source/SPServerSupport.m
+++ b/Source/SPServerSupport.m
@@ -54,6 +54,7 @@
 @synthesize supportsFullDropUser;
 @synthesize supportsUserMaxVars;
 @synthesize supportsShowPrivileges;
+@synthesize engineTypeQueryName;
 @synthesize supportsInformationSchemaEngines;
 @synthesize supportsPre41StorageEngines;
 @synthesize supportsBlackholeStorageEngine;
@@ -150,7 +151,10 @@
 	
 	// The SHOW PRIVILEGES statement wasn't added until MySQL 4.1.0
 	supportsShowPrivileges = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:0];
-	
+
+	// MySQL 4.0.18+ and 4.1.2+ changed the TYPE option to ENGINE, but 4.x supports both
+	engineTypeQueryName = [self isEqualToOrGreaterThanMajorVersion:5 minor:0 release:0]?@"ENGINE":@"TYPE";
+
 	// Before MySQL 4.1 the MEMORY engine was known as HEAP and the ISAM engine was available
 	supportsPre41StorageEngines = (![self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:0]);
 	
@@ -245,6 +249,7 @@
 	supportsFullDropUser               = NO;
 	supportsUserMaxVars                = NO;
 	supportsShowPrivileges             = NO;
+	engineTypeQueryName                = @"ENGINE";
 	supportsInformationSchemaEngines   = NO;
 	supportsPre41StorageEngines        = NO;
 	supportsBlackholeStorageEngine     = NO;
diff --git a/Source/SPTablesList.m b/Source/SPTablesList.m
index da787a6d..6d96956c 100644
--- a/Source/SPTablesList.m
+++ b/Source/SPTablesList.m
@@ -2007,7 +2007,7 @@
 
 	// If there is a type selected other than the default we must specify it in CREATE TABLE statement
 	if ([tableTypeButton indexOfSelectedItem] > 0) {
-		engineStatement = [NSString stringWithFormat:@"ENGINE = %@", [tableType backtickQuotedString]];
+		engineStatement = [NSString stringWithFormat:@"%@ = %@", [[tableDocumentInstance serverSupport] engineTypeQueryName], [tableType backtickQuotedString]];
 	}
 
 	NSString *createStatement = [NSString stringWithFormat:@"CREATE TABLE %@ (%@) %@ %@", [tableName backtickQuotedString], ([tableType isEqualToString:@"CSV"]) ? @"id INT NOT NULL" : @"id INT", charSetStatement, engineStatement];