From 6060ba57f5897bffdd35dbca438e6682b2960f75 Mon Sep 17 00:00:00 2001 From: rowanbeentje Date: Mon, 6 Apr 2009 23:54:25 +0000 Subject: - 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. --- Source/CMMCPConnection.m | 7 ++++++ Source/TableDocument.h | 2 ++ Source/TableDocument.m | 17 ++++++++++--- Source/TableDump.m | 65 ++++++++++++++++++++++++++++++++++-------------- 4 files changed, 68 insertions(+), 23 deletions(-) (limited to 'Source') 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]]; @@ -684,6 +685,14 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa return _encoding; } +/** + * 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 * 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]; -- cgit v1.2.3