- Select 'drop tables' option for MySQL export by default, to improve backup-type process and more closely match mysqldump, and correctly drop views

 - Consistently set and restore the encoding appropriately for SQL import/export, and export to UTF8 files to correctly store all characters.  Goes a good way towards addressing Issue #116.

4 files changed, 68 insertions, 23 deletions

diff --git a/Source/CMMCPConnection.m b/Source/CMMCPConnection.m
index abb61d82..ac75819f 100644
--- a/Source/CMMCPConnection.m
+++ b/Source/CMMCPConnection.m
@@ -136,6 +136,7 @@ static void forcePingTimeout(int signalNumber);
 - (BOOL) reconnect
 {
 	NSString *currentEncoding = nil;
+	BOOL currentEncodingUsesLatin1Transport = NO;
 	NSString *currentDatabase = nil;
 
 	// Store the current database and encoding so they can be re-set if reconnection was successful
@@ -145,6 +146,9 @@ static void forcePingTimeout(int signalNumber);
 	if (delegate && [delegate valueForKey:@"_encoding"]) {
 		currentEncoding = [NSString stringWithString:[delegate valueForKey:@"_encoding"]];
 	}
+	if (delegate && [delegate boolForKey:@"_encodingViaLatin1"]) {
+		currentEncodingUsesLatin1Transport = [delegate boolForKey:@"_encodingViaLatin1"];
+	}
 
 	// Close the connection if it exists.
 	if (mConnected) {
@@ -176,6 +180,9 @@ static void forcePingTimeout(int signalNumber);
 		if (currentEncoding) {
 			[self queryString:[NSString stringWithFormat:@"SET NAMES '%@'", currentEncoding]];
 			[self setEncoding:[CMMCPConnection encodingForMySQLEncoding:[currentEncoding UTF8String]]];
+			if (currentEncodingUsesLatin1Transport) {
+				[self queryString:@"SET CHARACTER_SET_RESULTS=latin1"];			
+			}
 		}
 	} else if (parentWindow) {
 
diff --git a/Source/TableDocument.h b/Source/TableDocument.h
index f9334c39..5bb29a9f 100644
--- a/Source/TableDocument.h
+++ b/Source/TableDocument.h
@@ -89,6 +89,7 @@
 	NSMenu *selectEncodingMenu;
 	BOOL _supportsEncoding;
 	NSString *_encoding;
+	BOOL _encodingViaLatin1;
 
 	NSToolbar *mainToolbar;
 	NSToolbarItem *chooseDatabaseToolbarItem;
@@ -131,6 +132,7 @@
 - (void)setConnectionEncoding:(NSString *)mysqlEncoding reloadingViews:(BOOL)reloadViews;
 - (NSString *)databaseEncoding;
 - (NSString *)connectionEncoding;
+- (BOOL)connectionEncodingViaLatin1;
 - (IBAction)chooseEncoding:(id)sender;
 - (BOOL)supportsEncoding;
 - (void)updateEncodingMenuWithSelectedEncoding:(NSString *)encoding;
diff --git a/Source/TableDocument.m b/Source/TableDocument.m
index 72742cf6..15bc444b 100644
--- a/Source/TableDocument.m
+++ b/Source/TableDocument.m
@@ -635,11 +635,11 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa
  */
 - (void)setConnectionEncoding:(NSString *)mysqlEncoding reloadingViews:(BOOL)reloadViews
 {
-	BOOL uselatin1results = NO;
+	_encodingViaLatin1 = NO;
 
 	// Special-case UTF-8 over latin 1 to allow viewing/editing of mangled data.
 	if ([mysqlEncoding isEqualToString:@"utf8-"]) {
-		uselatin1results = YES;
+		_encodingViaLatin1 = YES;
 		mysqlEncoding = @"utf8";
 	}
 	
@@ -647,13 +647,14 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa
 	[mySQLConnection queryString:[NSString stringWithFormat:@"SET NAMES '%@'", mysqlEncoding]];
 	
 	if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
-		if (uselatin1results)
+		if (_encodingViaLatin1)
 			[mySQLConnection queryString:@"SET CHARACTER_SET_RESULTS=latin1"];
 		[mySQLConnection setEncoding:[CMMCPConnection encodingForMySQLEncoding:[mysqlEncoding UTF8String]]];
 		[_encoding autorelease];
 		_encoding = [mysqlEncoding retain];
 	} else {
 		[mySQLConnection queryString:[NSString stringWithFormat:@"SET NAMES '%@'", [self databaseEncoding]]];
+		_encodingViaLatin1 = NO;
 		if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
 			NSLog(@"Error: could not set encoding to %@ nor fall back to database encoding on MySQL %@", mysqlEncoding, [self mySQLVersion]);
 			return;
@@ -661,7 +662,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa
 	}
 		
 	// update the selected menu item
-	if (uselatin1results) {
+	if (_encodingViaLatin1) {
 		[self updateEncodingMenuWithSelectedEncoding:[self encodingNameFromMySQLEncoding:[NSString stringWithFormat:@"%@-", mysqlEncoding]]];
 	} else {
 		[self updateEncodingMenuWithSelectedEncoding:[self encodingNameFromMySQLEncoding:mysqlEncoding]];
@@ -685,6 +686,14 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa
 }
 
 /**
+ * Returns whether the current encoding should display results via Latin1 transport for backwards compatibility
+ */
+- (BOOL)connectionEncodingViaLatin1
+{
+	return _encodingViaLatin1;
+}
+
+/**
  * updates the currently selected item in the encoding menu
  * 
  * @param NSString *encoding - the title of the menu item which will be selected
diff --git a/Source/TableDump.m b/Source/TableDump.m
index 077ba053..8dce751f 100644
--- a/Source/TableDump.m
+++ b/Source/TableDump.m
@@ -755,7 +755,7 @@
 {
 	int i,j,t,rowCount, colCount, progressBarWidth, lastProgressValue, queryLength, tableType;
 	CMMCPResult *queryResult;
-	NSString *tableName, *tableColumnTypeGrouping;
+	NSString *tableName, *tableColumnTypeGrouping, *previousConnectionEncoding;
 	NSArray *fieldNames;
 	NSArray *theRow;
 	NSMutableArray *selectedTables = [NSMutableArray array];
@@ -767,6 +767,7 @@
 	NSMutableArray *tableColumnNumericStatus;
 	NSStringEncoding connectionEncoding = [mySQLConnection encoding];
 	id createTableSyntax = nil;
+	BOOL previousConnectionEncodingViaLatin1;
 	
 	// Reset the interface
 	[errorsView setString:@""];
@@ -800,6 +801,12 @@
 	[metaString appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]];
 	[metaString appendString:@"# ************************************************************\n\n"];
 
+	// Add commands to store the client encodings used when importing and set to UTF8 to preserve data
+	[metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"];
+	[metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"];
+	[metaString appendString:@"/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"];
+	[metaString appendString:@"/*!40101 SET NAMES utf8 */;\n"];
+	
 	// Add commands to store and disable unique checks, foreign key checks, mode and notes where supported.
 	// Include trailing semicolons to ensure they're run individually.  Use mysql-version based comments.
 	if ( [addDropTableSwitch state] == NSOnState )
@@ -808,7 +815,14 @@
 	[metaString appendString:@"/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"];
 	[metaString appendString:@"/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n\n\n"];
 
-	[fileHandle writeData:[metaString dataUsingEncoding:connectionEncoding]];
+	[fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
+	// Store the current connection encoding so it can be restored after the dump.
+	previousConnectionEncoding = [tableDocumentInstance connectionEncoding];
+	previousConnectionEncodingViaLatin1 = [tableDocumentInstance connectionEncodingViaLatin1];
+	
+	// Set the connection to UTF8 to be able to export correctly.
+	[tableDocumentInstance setConnectionEncoding:@"utf8" reloadingViews:NO];
 	
 	// Loop through the selected tables
 	for ( i = 0 ; i < [selectedTables count] ; i++ ) {
@@ -824,15 +838,9 @@
 		
 		// Add the name of table
 		[fileHandle writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName]
-							   dataUsingEncoding:connectionEncoding]];
+							   dataUsingEncoding:NSUTF8StringEncoding]];
 		
 		
-		// Add a "drop table" command if specified in the export dialog
-		if ( [addDropTableSwitch state] == NSOnState )
-			[fileHandle writeData:[[NSString stringWithFormat:@"DROP TABLE IF EXISTS %@;\n\n", [tableName backtickQuotedString]]
-								   dataUsingEncoding:connectionEncoding]];
-		
-
 		// Determine whether this table is a table or a view via the create table command, and keep the create table syntax
 		queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]];
 		if ( [queryResult numOfRows] ) {
@@ -849,17 +857,24 @@
 		if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
 			[errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
 			if ( [addErrorsSwitch state] == NSOnState ) {
-				[fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]] dataUsingEncoding:connectionEncoding]];
+				[fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
 			}
 		}
 
+
+		// Add a "drop table" command if specified in the export dialog
+		if ( [addDropTableSwitch state] == NSOnState )
+			[fileHandle writeData:[[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SP_TABLETYPE_TABLE)?@"TABLE":@"VIEW"), [tableName backtickQuotedString]]
+								   dataUsingEncoding:NSUTF8StringEncoding]];
+		
+
 		// Add the create syntax for the table if specified in the export dialog
 		if ( [addCreateTableSwitch state] == NSOnState && createTableSyntax) {
 			if ( [createTableSyntax isKindOfClass:[NSData class]] ) {
 				createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:connectionEncoding] autorelease];
 			}
-			[fileHandle writeData:[createTableSyntax dataUsingEncoding:connectionEncoding]];
-			[fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:connectionEncoding]];
+			[fileHandle writeData:[createTableSyntax dataUsingEncoding:NSUTF8StringEncoding]];
+			[fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
 		}
 		
 		// Add the table content if required
@@ -903,7 +918,7 @@
 
 				// Construct the start of the insertion command
 				[fileHandle writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(",
-										[tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:connectionEncoding]];
+										[tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:NSUTF8StringEncoding]];
 				
 				// Iterate through the rows to construct a VALUES group for each
 				for ( j = 0 ; j < rowCount ; j++ ) {
@@ -973,30 +988,30 @@
 					}
 					
 					// Write this row to the file
-					[fileHandle writeData:[sqlString dataUsingEncoding:connectionEncoding]];
+					[fileHandle writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]];
 				}
 				
 				// Complete the command
-				[fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:connectionEncoding]];
+				[fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
 
 				// Unlock the table and re-enable keys if supported
 				[metaString setString:@""];
 				[metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ ENABLE KEYS */;\n", [tableName backtickQuotedString]]];
 				[metaString appendString:@"UNLOCK TABLES;\n"];
-				[fileHandle writeData:[metaString dataUsingEncoding:connectionEncoding]];
+				[fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
 				
 				if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
 					[errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
 					if ( [addErrorsSwitch state] == NSOnState ) {
 						[fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]]
-											   dataUsingEncoding:connectionEncoding]];
+											   dataUsingEncoding:NSUTF8StringEncoding]];
 					}
 				}
 			}
 		}
 		
 		// Add an additional separator between tables
-		[fileHandle writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:connectionEncoding]];
+		[fileHandle writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
 	}
 	
 	// Restore unique checks, foreign key checks, and other settings saved at the start
@@ -1006,7 +1021,19 @@
 	[metaString appendString:@"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n"];
 	if ( [addDropTableSwitch state] == NSOnState )
 		[metaString appendString:@"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n"];
-	[fileHandle writeData:[metaString dataUsingEncoding:connectionEncoding]];
+
+	// Restore the client encoding to the original encoding before import
+	[metaString appendString:@"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"];
+	[metaString appendString:@"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"];
+	[metaString appendString:@"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n"];
+
+	// Write footer-type information to the file
+	[fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
+	// Restore the connection character set to pre-export details
+	[tableDocumentInstance
+	 setConnectionEncoding:[NSString stringWithFormat:@"%@%@", previousConnectionEncoding, previousConnectionEncodingViaLatin1?@"-":@""]
+	 reloadingViews:NO];
 	
 	// Close the progress sheet
 	[NSApp endSheet:singleProgressSheet];