aboutsummaryrefslogtreecommitdiffstats
path: root/Source
diff options
context:
space:
mode:
authorrowanbeentje <rowan@beent.je>2009-04-06 23:54:25 +0000
committerrowanbeentje <rowan@beent.je>2009-04-06 23:54:25 +0000
commit6060ba57f5897bffdd35dbca438e6682b2960f75 (patch)
tree86c9d9e00b88f40c5f4cad36d050c07997f751b6 /Source
parent16aee03a9c48ffdaf33fd3f2c99f7b5a933ca0ee (diff)
downloadsequelpro-6060ba57f5897bffdd35dbca438e6682b2960f75.tar.gz
sequelpro-6060ba57f5897bffdd35dbca438e6682b2960f75.tar.bz2
sequelpro-6060ba57f5897bffdd35dbca438e6682b2960f75.zip
- 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.
Diffstat (limited to 'Source')
-rw-r--r--Source/CMMCPConnection.m7
-rw-r--r--Source/TableDocument.h2
-rw-r--r--Source/TableDocument.m17
-rw-r--r--Source/TableDump.m65
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];