For some reason table creation on MySQL 4.0 servers doesn't like the engine/type being quoted.

4 files changed, 44 insertions, 33 deletions

diff --git a/Source/SPServerSupport.h b/Source/SPServerSupport.h
index bf18a8e8..fd4535a2 100644
--- a/Source/SPServerSupport.h
+++ b/Source/SPServerSupport.h
@@ -71,6 +71,7 @@
 	BOOL supportsBlackholeStorageEngine;
 	BOOL supportsArchiveStorageEngine;
 	BOOL supportsCSVStorageEngine;
+	BOOL supportsQuotingEngineTypeInCreateSyntax;
 	
 	// Triggers
 	BOOL supportsTriggers;
@@ -213,6 +214,12 @@
  */
 @property (readonly) BOOL supportsIndexKeyBlockSize;
 
+/**
+ * @property supportsQuotingEngineTypeInCreateSyntax Indicates whether the server supports quoting the engine
+ *                                                   type in the create syntax.
+ */
+@property (readonly) BOOL supportsQuotingEngineTypeInCreateSyntax;
+
 - (id)initWithMajorVersion:(NSInteger)majorVersion minor:(NSInteger)minorVersion release:(NSInteger)releaseVersion;
 
 - (void)evaluate;
diff --git a/Source/SPServerSupport.m b/Source/SPServerSupport.m
index 15c3555f..937069f8 100644
--- a/Source/SPServerSupport.m
+++ b/Source/SPServerSupport.m
@@ -62,6 +62,7 @@
 @synthesize supportsCSVStorageEngine;
 @synthesize supportsTriggers;
 @synthesize supportsIndexKeyBlockSize;
+@synthesize supportsQuotingEngineTypeInCreateSyntax;
 @synthesize serverMajorVersion;
 @synthesize serverMinorVersion;
 @synthesize serverReleaseVersion;
@@ -153,7 +154,7 @@
 	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";
+	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]);
@@ -171,7 +172,10 @@
 	supportsTriggers = [self isEqualToOrGreaterThanMajorVersion:5 minor:0 release:2];
 	
 	// Support for specifying an index's key block size wasn't added until MySQL 5.1.10
-	supportsIndexKeyBlockSize = [self isEqualToOrGreaterThanMajorVersion:5 minor:1 release:10];	
+	supportsIndexKeyBlockSize = [self isEqualToOrGreaterThanMajorVersion:5 minor:1 release:10];
+	
+	// MySQL 4.0 doesn't seem to like having the ENGINE/TYPE quoted in a table's create syntax
+	supportsQuotingEngineTypeInCreateSyntax = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:0];
 }
 
 /**
@@ -234,29 +238,30 @@
  */
 - (void)_invalidate
 {
-	isMySQL3                           = NO;
-	isMySQL4                           = NO;
-	isMySQL5                           = NO;
-	isMySQL6                           = NO;
-	
-	supportsInformationSchema          = NO;
-	supportsSpatialExtensions          = NO;
-	supportsShowCharacterSet           = NO;
-	supportsCharacterSetDatabaseVar    = NO;
-	supportsPost41CharacterSetHandling = NO;
-	supportsCreateUser                 = NO;
-	supportsDropUser                   = NO;
-	supportsFullDropUser               = NO;
-	supportsUserMaxVars                = NO;
-	supportsShowPrivileges             = NO;
-	engineTypeQueryName                = @"ENGINE";
-	supportsInformationSchemaEngines   = NO;
-	supportsPre41StorageEngines        = NO;
-	supportsBlackholeStorageEngine     = NO;
-	supportsArchiveStorageEngine       = NO;
-	supportsCSVStorageEngine           = NO;
-	supportsTriggers                   = NO;
-	supportsIndexKeyBlockSize          = NO;
+	isMySQL3 = NO;
+	isMySQL4 = NO;
+	isMySQL5 = NO;
+	isMySQL6 = NO;
+	
+	supportsInformationSchema               = NO;
+	supportsSpatialExtensions               = NO;
+	supportsShowCharacterSet                = NO;
+	supportsCharacterSetDatabaseVar         = NO;
+	supportsPost41CharacterSetHandling      = NO;
+	supportsCreateUser                      = NO;
+	supportsDropUser                        = NO;
+	supportsFullDropUser                    = NO;
+	supportsUserMaxVars                     = NO;
+	supportsShowPrivileges                  = NO;
+	engineTypeQueryName                     = @"ENGINE";
+	supportsInformationSchemaEngines        = NO;
+	supportsPre41StorageEngines             = NO;
+	supportsBlackholeStorageEngine          = NO;
+	supportsArchiveStorageEngine            = NO;
+	supportsCSVStorageEngine                = NO;
+	supportsTriggers                        = NO;
+	supportsIndexKeyBlockSize               = NO;
+	supportsQuotingEngineTypeInCreateSyntax = NO;
 }
 
 /**
diff --git a/Source/SPTableRelations.m b/Source/SPTableRelations.m
index 5a81e756..eacdc8f6 100644
--- a/Source/SPTableRelations.m
+++ b/Source/SPTableRelations.m
@@ -38,7 +38,7 @@ static NSString *SPRelationColumnsKey   = @"columns";
 static NSString *SPRelationFKTableKey   = @"fk_table";
 static NSString *SPRelationFKColumnsKey = @"fk_columns";
 static NSString *SPRelationOnUpdateKey  = @"on_update";
-static NSString *SPRelationOnDeleteKey = @"on_delete";
+static NSString *SPRelationOnDeleteKey  = @"on_delete";
 
 @interface SPTableRelations ()
 
@@ -144,9 +144,9 @@ static NSString *SPRelationOnDeleteKey = @"on_delete";
 	
 	NSString *query = [NSString stringWithFormat:@"ALTER TABLE %@ ADD ",[thisTable backtickQuotedString]];
 	
-	//set constraint name?
-	if([[constraintName stringValue] length] > 0) {
-		query = [query stringByAppendingString:[NSString stringWithFormat:@"CONSTRAINT %@ ",[[constraintName stringValue] backtickQuotedString]]];
+	// Set constraint name?
+	if ([[constraintName stringValue] length] > 0) {
+		query = [query stringByAppendingString:[NSString stringWithFormat:@"CONSTRAINT %@ ", [[constraintName stringValue] backtickQuotedString]]];
 	}
 	
 	query = [query stringByAppendingString:[NSString stringWithFormat:@"FOREIGN KEY (%@) REFERENCES %@ (%@)",
@@ -154,7 +154,8 @@ static NSString *SPRelationOnDeleteKey = @"on_delete";
 												[thatTable backtickQuotedString],
 												[thatColumn backtickQuotedString]]];
 	
-	NSArray *onActions = [NSArray arrayWithObjects:@"RESTRICT",@"CASCADE",@"SET NULL",@"NO ACTION",nil];
+	NSArray *onActions = [NSArray arrayWithObjects:@"RESTRICT", @"CASCADE", @"SET NULL", @"NO ACTION", nil];
+	
 	// If required add ON DELETE
 	if ([onDeletePopUpButton selectedTag] >= 0) {
 		query = [query stringByAppendingString:[NSString stringWithFormat:@" ON DELETE %@", [onActions objectAtIndex:[onDeletePopUpButton selectedTag]]]];
@@ -266,7 +267,6 @@ static NSString *SPRelationOnDeleteKey = @"on_delete";
 	if (changeEncoding) [connection restoreStoredEncoding];
 
 	[self selectReferenceTable:nil];
-
 	[self openRelationSheet:self];
 }
 
diff --git a/Source/SPTablesList.m b/Source/SPTablesList.m
index f82207dc..97055611 100644
--- a/Source/SPTablesList.m
+++ b/Source/SPTablesList.m
@@ -2248,7 +2248,6 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
  */
 - (void)_addTable
 {
-
 	// Ensure the task is performed on a background thread to group addition and loads
 	if ([NSThread isMainThread]) {
 		[NSThread detachNewThreadSelector:@selector(_addTable) toTarget:self withObject:nil];
@@ -2279,7 +2278,7 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 
 	// 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:@"%@ = %@", [[tableDocumentInstance serverSupport] engineTypeQueryName], [tableType backtickQuotedString]];
+		engineStatement = [NSString stringWithFormat:@"%@ = %@", [[tableDocumentInstance serverSupport] engineTypeQueryName], [[tableDocumentInstance serverSupport] supportsQuotingEngineTypeInCreateSyntax] ? [tableType backtickQuotedString] : tableType];
 	}
 
 	NSString *createStatement = [NSString stringWithFormat:@"CREATE TABLE %@ (id INT(11) UNSIGNED NOT NULL%@) %@ %@", [tableName backtickQuotedString], [tableType isEqualToString:@"CSV"] ? @"" : @" PRIMARY KEY AUTO_INCREMENT", charSetStatement, engineStatement];