diff options
Diffstat (limited to 'Source')
| -rw-r--r-- | Source/SPExportController.m | 2 | ||||
| -rw-r--r-- | Source/SPExportInitializer.m | 15 | ||||
| -rw-r--r-- | Source/SPExporter.h | 4 | ||||
| -rw-r--r-- | Source/SPSQLExporter.h | 11 | ||||
| -rw-r--r-- | Source/SPSQLExporter.m | 1010 | ||||
| -rw-r--r-- | Source/SPSQLExporterDelegate.m | 26 |
6 files changed, 532 insertions, 536 deletions
diff --git a/Source/SPExportController.m b/Source/SPExportController.m index 7f8610ec..7357a9ee 100644 --- a/Source/SPExportController.m +++ b/Source/SPExportController.m @@ -190,7 +190,7 @@ // Export finished Growl notification [[SPGrowlController sharedGrowlController] notifyWithTitle:@"Export Finished" description:[NSString stringWithFormat:NSLocalizedString(@"Finished exporting to %@", @"description for finished exporting growl notification"), exportFilename] - document:[tableDocumentInstance parentWindow] + document:tableDocumentInstance notificationName:@"Export Finished"]; } diff --git a/Source/SPExportInitializer.m b/Source/SPExportInitializer.m index 20b38c5e..46eaf2b6 100644 --- a/Source/SPExportInitializer.m +++ b/Source/SPExportInitializer.m @@ -149,13 +149,10 @@ // Start the notification timer to allow notifications to be shown even if frontmost for long queries [[SPGrowlController sharedGrowlController] setVisibilityForNotificationName:@"Export Finished"]; - // Reset the interface - [[exportProgressTitle onMainThread] setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Exporting %@", @"text showing that the application is importing a supplied format"), exportTypeLabel]]; - [[exportProgressText onMainThread] setStringValue:NSLocalizedString(@"Writing...", @"text showing that app is writing text file")]; - - [[exportProgressText onMainThread] displayIfNeeded]; - [[exportProgressIndicator onMainThread] setDoubleValue:0]; - [[exportProgressIndicator onMainThread] displayIfNeeded]; + // Reset the progress interface + [exportProgressTitle setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Exporting %@", @"text showing that the application is importing a supplied format"), exportTypeLabel]]; + [exportProgressText setStringValue:NSLocalizedString(@"Writing...", @"text showing that app is writing text file")]; + [exportProgressIndicator setDoubleValue:0]; // Open the progress sheet [NSApp beginSheet:exportProgressWindow @@ -303,7 +300,7 @@ // Set the exporter's max progress [sqlExporter setExportMaxProgress:((NSInteger)[exportProgressIndicator bounds].size.width)]; - + // Set the progress bar's max value [exportProgressIndicator setMaxValue:[sqlExporter exportMaxProgress]]; @@ -315,7 +312,7 @@ // Create custom filename if required filename = (createCustomFilename) ? [self expandCustomFilenameFormatFromString:[exportCustomFilenameTokenField stringValue] usingTableName:nil] : [NSString stringWithFormat:@"%@_%@", [tableDocumentInstance database], [[NSDate date] descriptionWithCalendarFormat:@"%Y-%m-%d" timeZone:nil locale:nil]]; - SPFileHandle *fileHandle = [self getFileHandleForFilePath:[[exportPathField stringValue] stringByAppendingPathComponent:[filename stringByAppendingPathExtension:([exportCompressOutputFile state]) ? @"gz" : @"sql"]]]; + SPFileHandle *fileHandle = [self getFileHandleForFilePath:[[exportPathField stringValue] stringByAppendingPathComponent:[filename stringByAppendingPathExtension:([exportCompressOutputFile state]) ? @"sql.gz" : @"sql"]]]; [sqlExporter setExportOutputFileHandle:fileHandle]; diff --git a/Source/SPExporter.h b/Source/SPExporter.h index 805173c9..f3c01077 100644 --- a/Source/SPExporter.h +++ b/Source/SPExporter.h @@ -64,7 +64,7 @@ SPFileHandle *exportOutputFileHandle; NSStringEncoding exportOutputEncoding; - NSInteger exportMaxProgress; + double exportMaxProgress; } @property(readwrite, retain) MCPConnection *connection; @@ -78,6 +78,6 @@ @property(readwrite, retain) SPFileHandle *exportOutputFileHandle; @property(readwrite, assign) NSStringEncoding exportOutputEncoding; -@property(readwrite, assign) NSInteger exportMaxProgress; +@property(readwrite, assign) double exportMaxProgress; @end diff --git a/Source/SPSQLExporter.h b/Source/SPSQLExporter.h index 6b4702ce..735056aa 100644 --- a/Source/SPSQLExporter.h +++ b/Source/SPSQLExporter.h @@ -88,7 +88,12 @@ * Compress output */ BOOL sqlOutputCompressFile; - + + /** + * Number of tables processed by exporter + */ + NSUInteger sqlCurrentTableExportIndex; + /** * Table information */ @@ -111,7 +116,9 @@ @property(readwrite, assign) BOOL sqlOutputIncludeErrors; @property(readwrite, assign) BOOL sqlOutputCompressFile; -@property (readwrite, retain) NSDictionary *sqlTableInformation; +@property(readwrite, assign) NSUInteger sqlCurrentTableExportIndex; + +@property(readwrite, retain) NSDictionary *sqlTableInformation; /** * Initialise an instance of SPSQLExporter using the supplied delegate. diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m index 9b7cbaf6..99e42515 100644 --- a/Source/SPSQLExporter.m +++ b/Source/SPSQLExporter.m @@ -52,6 +52,7 @@ @synthesize sqlOutputEncodeBLOBasHex; @synthesize sqlOutputIncludeErrors; @synthesize sqlOutputCompressFile; +@synthesize sqlCurrentTableExportIndex; @synthesize sqlTableInformation; /** @@ -75,505 +76,399 @@ */ - (void)main { - @try { - NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init]; - NSAutoreleasePool *sqlExportPool = [[NSAutoreleasePool alloc] init]; - - MCPResult *queryResult; - MCPStreamingResult *streamingResult; - - NSArray *row; - NSString *tableName; - NSDictionary *tableDetails; - NSMutableArray *tableColumnNumericStatus; - SPTableType tableType = SPTableTypeTable; - - id createTableSyntax = nil; - NSUInteger i, j, t, s, rowCount, queryLength, lastProgressValue; - - BOOL sqlOutputIncludeStructure; - BOOL sqlOutputIncludeContent; - BOOL sqlOutputIncludeDropSyntax; + NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init]; + NSAutoreleasePool *sqlExportPool = [[NSAutoreleasePool alloc] init]; + + MCPResult *queryResult; + MCPStreamingResult *streamingResult; + + NSArray *row; + NSString *tableName; + NSDictionary *tableDetails; + NSMutableArray *tableColumnNumericStatus; + SPTableType tableType = SPTableTypeTable; + + id createTableSyntax = nil; + NSUInteger i, j, t, s, rowCount, queryLength, lastProgressValue; + + BOOL sqlOutputIncludeStructure; + BOOL sqlOutputIncludeContent; + BOOL sqlOutputIncludeDropSyntax; + + NSMutableArray *tables = [NSMutableArray array]; + NSMutableArray *procs = [NSMutableArray array]; + NSMutableArray *funcs = [NSMutableArray array]; + + NSMutableString *metaString = [NSMutableString string]; + NSMutableString *cellValue = [NSMutableString string]; + NSMutableString *errors = [[NSMutableString alloc] init]; + NSMutableString *sqlString = [[NSMutableString alloc] init]; + + NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary]; + + // Check that we have all the required info before starting the export + if ((![self sqlExportTables]) || ([[self sqlExportTables] count] == 0) || + (![self sqlTableInformation]) || ([[self sqlTableInformation] count] == 0) || + (![self sqlDatabaseHost]) || ([[self sqlDatabaseHost] isEqualToString:@""]) || + (![self sqlDatabaseName]) || ([[self sqlDatabaseName] isEqualToString:@""]) || + (![self sqlDatabaseVersion] || ([[self sqlDatabaseName] isEqualToString:@""]))) + { + [pool release]; + return; + } + + // Inform the delegate that the export process is about to begin + [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBegin:) withObject:self waitUntilDone:NO]; + + // Mark the process as running + [self setExportProcessIsRunning:YES]; + + // Clear errors + [self setSqlExportErrors:@""]; + + // Copy over the selected item names into tables in preparation for iteration + NSMutableArray *targetArray; + + for (NSArray *item in [self sqlExportTables]) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; + } - NSMutableArray *tables = [NSMutableArray array]; - NSMutableArray *procs = [NSMutableArray array]; - NSMutableArray *funcs = [NSMutableArray array]; + switch ([NSArrayObjectAtIndex(item, 4) intValue]) { + case SPTableTypeProc: + targetArray = procs; + break; + case SPTableTypeFunc: + targetArray = funcs; + break; + default: + targetArray = tables; + break; + } - NSMutableString *metaString = [NSMutableString string]; - NSMutableString *cellValue = [NSMutableString string]; - NSMutableString *errors = [[NSMutableString alloc] init]; - NSMutableString *sqlString = [[NSMutableString alloc] init]; + [targetArray addObject:item]; + } + + // If required write the UTF-8 Byte Order Mark + if ([self sqlOutputIncludeUTF8BOM]) { + [metaString setString:@"\xef\xbb\xbf"]; + [metaString appendString:@"# ************************************************************\n"]; + } + else { + [metaString setString:@"# ************************************************************\n"]; + } - NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary]; - - // Check that we have all the required info before starting the export - if ((![self sqlExportTables]) || ([[self sqlExportTables] count] == 0) || - (![self sqlTableInformation]) || ([[self sqlTableInformation] count] == 0) || - (![self sqlDatabaseHost]) || ([[self sqlDatabaseHost] isEqualToString:@""]) || - (![self sqlDatabaseName]) || ([[self sqlDatabaseName] isEqualToString:@""]) || - (![self sqlDatabaseVersion] || ([[self sqlDatabaseName] isEqualToString:@""]))) - { + // If required set the file handle to compress it's output + [[self exportOutputFileHandle] setShouldWriteWithGzipCompression:[self sqlOutputCompressFile]]; + + // Add the dump header to the dump file + [metaString appendString:@"# Sequel Pro SQL dump\n"]; + [metaString appendString:[NSString stringWithFormat:@"# Version %@\n#\n", [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]]; + [metaString appendString:[NSString stringWithFormat:@"# %@\n# %@\n#\n", SPHomePageURL, SPDevURL]]; + [metaString appendString:[NSString stringWithFormat:@"# Host: %@ (MySQL %@)\n", [self sqlDatabaseHost], [self sqlDatabaseVersion]]]; + [metaString appendString:[NSString stringWithFormat:@"# Database: %@\n", [self sqlDatabaseName]]]; + [metaString appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]]; + [metaString appendString:@"# ************************************************************\n\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 (sqlOutputIncludeDropSyntax) { + //[metaString appendString:@"/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n"]; + //} + + [metaString appendString:@"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"]; + [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"]; + + [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]]; + + // Loop through the selected tables + for (NSArray *table in [self sqlExportTables]) + { + // Check for cancellation flag + if ([self isCancelled]) { [pool release]; return; } - - // Inform the delegate that the export process is about to begin - [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBegin:) withObject:self waitUntilDone:NO]; - // Mark the process as running - [self setExportProcessIsRunning:YES]; + [self setSqlCurrentTableExportIndex:[self sqlCurrentTableExportIndex]+1]; + tableName = NSArrayObjectAtIndex(table, 0); + + sqlOutputIncludeStructure = [NSArrayObjectAtIndex(table, 1) boolValue]; + sqlOutputIncludeContent = [NSArrayObjectAtIndex(table, 2) boolValue]; + sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(table, 3) boolValue]; - // Clear errors - [self setSqlExportErrors:@""]; - - // Copy over the selected item names into tables in preparation for iteration - NSMutableArray *targetArray; + // Set the current table + [self setSqlExportCurrentTable:tableName]; - for (NSArray *item in [self sqlExportTables]) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; - } + // Inform the delegate that we are about to start fetcihing data for the current table + [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginFetchingData:) withObject:self waitUntilDone:NO]; + + lastProgressValue = 0; + + // Add the name of table + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName] dataUsingEncoding:[self exportOutputEncoding]]]; + + // Determine whether this table is a table or a view via the CREATE TABLE command, and keep the create table syntax + queryResult = [connection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]]; + + [queryResult setReturnDataAsStrings:YES]; + + if ([queryResult numOfRows]) { + tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; - switch ([NSArrayObjectAtIndex(item, 4) intValue]) { - case SPTableTypeProc: - targetArray = procs; - break; - case SPTableTypeFunc: - targetArray = funcs; - break; - default: - targetArray = tables; - break; + if ([tableDetails objectForKey:@"Create View"]) { + [viewSyntaxes setValue:[[[[tableDetails objectForKey:@"Create View"] copy] autorelease] createViewSyntaxPrettifier] forKey:tableName]; + createTableSyntax = [self _createViewPlaceholderSyntaxForView:tableName]; + tableType = SPTableTypeView; + } + else { + createTableSyntax = [[[tableDetails objectForKey:@"Create Table"] copy] autorelease]; + tableType = SPTableTypeTable; } - [targetArray addObject:item]; - } - - // If required write the UTF-8 Byte Order Mark - if ([self sqlOutputIncludeUTF8BOM]) { - [metaString setString:@"\xef\xbb\xbf"]; - [metaString appendString:@"# ************************************************************\n"]; - } - else { - [metaString setString:@"# ************************************************************\n"]; + [tableDetails release]; } + + if ([connection queryErrored]) { + [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; - // If required set the file handle to compress it's output - [[self exportOutputFileHandle] setShouldWriteWithGzipCompression:[self sqlOutputCompressFile]]; - - // Add the dump header to the dump file - [metaString appendString:@"# Sequel Pro SQL dump\n"]; - [metaString appendString:[NSString stringWithFormat:@"# Version %@\n#\n", [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]]; - [metaString appendString:[NSString stringWithFormat:@"# %@\n# %@\n#\n", SPHomePageURL, SPDevURL]]; - [metaString appendString:[NSString stringWithFormat:@"# Host: %@ (MySQL %@)\n", [self sqlDatabaseHost], [self sqlDatabaseVersion]]]; - [metaString appendString:[NSString stringWithFormat:@"# Database: %@\n", [self sqlDatabaseName]]]; - [metaString appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]]; - [metaString appendString:@"# ************************************************************\n\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"]; + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]]; + } + } - // 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 (sqlOutputIncludeDropSyntax) { - //[metaString appendString:@"/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n"]; - //} + // Add a 'DROP TABLE' command if required + if (sqlOutputIncludeDropSyntax) + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SPTableTypeTable) ? @"TABLE" : @"VIEW"), [tableName backtickQuotedString]] + dataUsingEncoding:[self exportOutputEncoding]]]; - [metaString appendString:@"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"]; - [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"]; - [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]]; - - // Loop through the selected tables - for (NSArray *table in [self sqlExportTables]) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; + // Add the create syntax for the table if specified in the export dialog + if (sqlOutputIncludeStructure && createTableSyntax) { + + if ([createTableSyntax isKindOfClass:[NSData class]]) { + createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:[self exportOutputEncoding]] autorelease]; } - tableName = NSArrayObjectAtIndex(table, 0); - - sqlOutputIncludeStructure = [NSArrayObjectAtIndex(table, 1) boolValue]; - sqlOutputIncludeContent = [NSArrayObjectAtIndex(table, 2) boolValue]; - sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(table, 3) boolValue]; - - // Set the current table - [self setSqlExportCurrentTable:tableName]; - - // Inform the delegate that we are about to start fetcihing data for the current table - [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginFetchingData:) withObject:self waitUntilDone:NO]; - - lastProgressValue = 0; - - // Add the name of table - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName] dataUsingEncoding:[self exportOutputEncoding]]]; - - // Determine whether this table is a table or a view via the CREATE TABLE command, and keep the create table syntax - queryResult = [connection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]]; + [[self exportOutputFileHandle] writeData:[createTableSyntax dataUsingEncoding:NSUTF8StringEncoding]]; + [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; + } + + // Add the table content if required + if (sqlOutputIncludeContent && (tableType == SPTableTypeTable)) { - [queryResult setReturnDataAsStrings:YES]; + // Retrieve the table details via the data class, and use it to build an array containing column numeric status + tableDetails = [NSDictionary dictionaryWithDictionary:[[self sqlTableInformation] objectForKey:tableName]]; + + NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count]; - if ([queryResult numOfRows]) { - tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; - - if ([tableDetails objectForKey:@"Create View"]) { - [viewSyntaxes setValue:[[[[tableDetails objectForKey:@"Create View"] copy] autorelease] createViewSyntaxPrettifier] forKey:tableName]; - createTableSyntax = [self _createViewPlaceholderSyntaxForView:tableName]; - tableType = SPTableTypeView; - } - else { - createTableSyntax = [[[tableDetails objectForKey:@"Create Table"] copy] autorelease]; - tableType = SPTableTypeTable; + tableColumnNumericStatus = [NSMutableArray arrayWithCapacity:colCount]; + + for (j = 0; j < colCount; j++) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; } - [tableDetails release]; - } - - if ([connection queryErrored]) { - [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; + NSString *tableColumnTypeGrouping = [NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], j) objectForKey:@"typegrouping"]; - if ([self sqlOutputIncludeErrors]) { - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]]; - } + [tableColumnNumericStatus addObject:[NSNumber numberWithBool:([tableColumnTypeGrouping isEqualToString:@"bit"] || [tableColumnTypeGrouping isEqualToString:@"integer"] || [tableColumnTypeGrouping isEqualToString:@"float"])]]; } + + // Retrieve the number of rows in the table for progress bar drawing + rowCount = [NSArrayObjectAtIndex([[connection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@", [tableName backtickQuotedString]]] fetchRowAsArray], 0) integerValue]; + + // Set up a result set in streaming mode + streamingResult = [[connection streamingQueryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([self exportUsingLowMemoryBlockingStreaming])] retain]; - // Add a 'DROP TABLE' command if required - if (sqlOutputIncludeDropSyntax) - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SPTableTypeTable) ? @"TABLE" : @"VIEW"), [tableName backtickQuotedString]] - dataUsingEncoding:[self exportOutputEncoding]]]; - + NSArray *fieldNames = [streamingResult fetchFieldNames]; - // Add the create syntax for the table if specified in the export dialog - if (sqlOutputIncludeStructure && createTableSyntax) { - - if ([createTableSyntax isKindOfClass:[NSData class]]) { - createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:[self exportOutputEncoding]] autorelease]; - } + // Inform the delegate that we are about to start writing data for the current table + [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO]; + + if (rowCount) { + queryLength = 0; - [[self exportOutputFileHandle] writeData:[createTableSyntax dataUsingEncoding:NSUTF8StringEncoding]]; - [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; - } - - // Add the table content if required - if (sqlOutputIncludeContent && (tableType == SPTableTypeTable)) { + // Lock the table for writing and disable keys if supported + [metaString setString:@""]; + [metaString appendString:[NSString stringWithFormat:@"LOCK TABLES %@ WRITE;\n", [tableName backtickQuotedString]]]; + [metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ DISABLE KEYS */;\n\n", [tableName backtickQuotedString]]]; - // Retrieve the table details via the data class, and use it to build an array containing column numeric status - tableDetails = [NSDictionary dictionaryWithDictionary:[[self sqlTableInformation] objectForKey:tableName]]; - - NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count]; + [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]]; - tableColumnNumericStatus = [NSMutableArray arrayWithCapacity:colCount]; - - for (j = 0; j < colCount; j++) + // Construct the start of the insertion command + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(", + [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:NSUTF8StringEncoding]]; + + // Iterate through the rows to construct a VALUES group for each + j = 0; + + sqlExportPool = [[NSAutoreleasePool alloc] init]; + + // Inform the delegate that we are about to start writing the data to disk + [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO]; + + while (row = [streamingResult fetchNextRowAsArray]) { // Check for cancellation flag if ([self isCancelled]) { + [connection cancelCurrentQuery]; + [streamingResult cancelResultLoad]; + [sqlExportPool release]; [pool release]; + return; } - NSString *tableColumnTypeGrouping = [NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], j) objectForKey:@"typegrouping"]; - - [tableColumnNumericStatus addObject:[NSNumber numberWithBool:([tableColumnTypeGrouping isEqualToString:@"bit"] || [tableColumnTypeGrouping isEqualToString:@"integer"] || [tableColumnTypeGrouping isEqualToString:@"float"])]]; - } - - // Retrieve the number of rows in the table for progress bar drawing - rowCount = [NSArrayObjectAtIndex([[connection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@", [tableName backtickQuotedString]]] fetchRowAsArray], 0) integerValue]; - - // Set up a result set in streaming mode - streamingResult = [[connection streamingQueryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([self exportUsingLowMemoryBlockingStreaming])] retain]; - - NSArray *fieldNames = [streamingResult fetchFieldNames]; - - // Inform the delegate that we are about to start writing data for the current table - [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO]; - - if (rowCount) { - queryLength = 0; - - // Lock the table for writing and disable keys if supported - [metaString setString:@""]; - [metaString appendString:[NSString stringWithFormat:@"LOCK TABLES %@ WRITE;\n", [tableName backtickQuotedString]]]; - [metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ DISABLE KEYS */;\n\n", [tableName backtickQuotedString]]]; - - [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]]; - - // Construct the start of the insertion command - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(", - [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:NSUTF8StringEncoding]]; - - // Iterate through the rows to construct a VALUES group for each - j = 0; - - sqlExportPool = [[NSAutoreleasePool alloc] init]; - - // Inform the delegate that we are about to start writing the data to disk - [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO]; + j++; + [sqlString setString:@""]; + + // Update the progress + NSUInteger progress = (j * ([self exportMaxProgress] / rowCount)); + if (progress > lastProgressValue) { + [self setExportProgressValue:progress]; + lastProgressValue = progress; + + // Inform the delegate that the export's progress has been updated + [delegate performSelectorOnMainThread:@selector(sqlExportProcessProgressUpdated:) withObject:self waitUntilDone:NO]; + } - while (row = [streamingResult fetchNextRowAsArray]) + for (t = 0; t < colCount; t++) { // Check for cancellation flag if ([self isCancelled]) { - [connection cancelCurrentQuery]; - [streamingResult cancelResultLoad]; [sqlExportPool release]; [pool release]; return; } - j++; - [sqlString setString:@""]; - - // Update the progress - if ((j * ([self exportMaxProgress] / rowCount)) > lastProgressValue) { - - NSInteger progress = (j * ([self exportMaxProgress] / rowCount)); - - [self setExportProgressValue:progress]; - - lastProgressValue = progress; + // Add NULL values directly to the output row + if ([NSArrayObjectAtIndex(row, t) isMemberOfClass:[NSNull class]]) { + [sqlString appendString:@"NULL"]; + } + // Add data types directly as hex data + else if ([NSArrayObjectAtIndex(row, t) isKindOfClass:[NSData class]]) { - // Inform the delegate that the export's progress has been updated - [delegate performSelectorOnMainThread:@selector(sqlExportProcessProgressUpdated:) withObject:self waitUntilDone:NO]; - } - - for (t = 0; t < colCount; t++) - { - // Check for cancellation flag - if ([self isCancelled]) { - [sqlExportPool release]; - [pool release]; - - return; + if ([self sqlOutputEncodeBLOBasHex]) { + [sqlString appendString:@"X'"]; + [sqlString appendString:[connection prepareBinaryData:NSArrayObjectAtIndex(row, t)]]; } - - // Add NULL values directly to the output row - if ([NSArrayObjectAtIndex(row, t) isMemberOfClass:[NSNull class]]) { - [sqlString appendString:@"NULL"]; - } - // Add data types directly as hex data - else if ([NSArrayObjectAtIndex(row, t) isKindOfClass:[NSData class]]) { + else { + [sqlString appendString:@"'"]; - if ([self sqlOutputEncodeBLOBasHex]) { - [sqlString appendString:@"X'"]; - [sqlString appendString:[connection prepareBinaryData:NSArrayObjectAtIndex(row, t)]]; - } - else { - [sqlString appendString:@"'"]; - - NSString *data = [[NSString alloc] initWithData:NSArrayObjectAtIndex(row, t) encoding:[self exportOutputEncoding]]; - - if (data == nil) { - data = [[NSString alloc] initWithData:NSArrayObjectAtIndex(row, t) encoding:NSASCIIStringEncoding]; - } - - [sqlString appendString:data]; - - [data release]; + NSString *data = [[NSString alloc] initWithData:NSArrayObjectAtIndex(row, t) encoding:[self exportOutputEncoding]]; + + if (data == nil) { + data = [[NSString alloc] initWithData:NSArrayObjectAtIndex(row, t) encoding:NSASCIIStringEncoding]; } - [sqlString appendString:@"'"]; + [sqlString appendString:data]; + + [data release]; + } + + [sqlString appendString:@"'"]; + } + else { + [cellValue setString:[NSArrayObjectAtIndex(row, t) description]]; + + // Add empty strings as a pair of quotes + if ([cellValue length] == 0) { + [sqlString appendString:@"''"]; } else { - [cellValue setString:[NSArrayObjectAtIndex(row, t) description]]; - - // Add empty strings as a pair of quotes - if ([cellValue length] == 0) { - [sqlString appendString:@"''"]; + // If this is a numeric column type, add the number directly. + if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) { + [sqlString appendString:cellValue]; } + // Otherwise add a quoted string with special characters escaped else { - // If this is a numeric column type, add the number directly. - if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) { - [sqlString appendString:cellValue]; - } - // Otherwise add a quoted string with special characters escaped - else { - [sqlString appendString:@"'"]; - [sqlString appendString:[connection prepareString:cellValue]]; - [sqlString appendString:@"'"]; - } + [sqlString appendString:@"'"]; + [sqlString appendString:[connection prepareString:cellValue]]; + [sqlString appendString:@"'"]; } } - - // Add the field separator if this isn't the last cell in the row - if (t != ([row count] - 1)) [sqlString appendString:@","]; } - queryLength += [sqlString length]; + // Add the field separator if this isn't the last cell in the row + if (t != ([row count] - 1)) [sqlString appendString:@","]; + } + + queryLength += [sqlString length]; + + // Close this VALUES group and set up the next one if appropriate + if (j != rowCount) { - // Close this VALUES group and set up the next one if appropriate - if (j != rowCount) { + // Add a new INSERT starter command every ~250k of data + if (queryLength > 250000) { + [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO %@ (%@)\nVALUES\n\t(", + [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]]]; + queryLength = 0; - // Add a new INSERT starter command every ~250k of data - if (queryLength > 250000) { - [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO %@ (%@)\nVALUES\n\t(", - [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]]]; - queryLength = 0; - - // Use the opportunity to drain and reset the autorelease pool - [sqlExportPool release]; - sqlExportPool = [[NSAutoreleasePool alloc] init]; - } - else { - [sqlString appendString:@"),\n\t("]; - } + // Use the opportunity to drain and reset the autorelease pool + [sqlExportPool release]; + sqlExportPool = [[NSAutoreleasePool alloc] init]; } else { - [sqlString appendString:@")"]; + [sqlString appendString:@"),\n\t("]; } - - // Write this row to the file - [[self exportOutputFileHandle] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]]; + } + else { + [sqlString appendString:@")"]; } - // Complete the command - [[self exportOutputFileHandle] 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"]; - - [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; - - // Drain the autorelease pool - [sqlExportPool release]; + // Write this row to the file + [[self exportOutputFileHandle] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]]; } - if ([connection queryErrored]) { - [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; - - if ([self sqlOutputIncludeErrors]) { - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] - dataUsingEncoding:NSUTF8StringEncoding]]; - } - } - - // Release the result set - [streamingResult release]; - - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]]; - - [queryResult setReturnDataAsStrings:YES]; + // Complete the command + [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; - if ([queryResult numOfRows]) { - - [metaString setString:@"\n"]; - [metaString appendString:@"DELIMITER ;;\n"]; - - for (s = 0; s < [queryResult numOfRows]; s++) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; - } - - NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; - - // Definer is user@host but we need to escape it to `user`@`host` - NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; - - NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@", - [NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString], - [NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString] - ]; - - [metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n", [triggers objectForKey:@"sql_mode"]]]; - [metaString appendString:@"/*!50003 CREATE */ "]; - [metaString appendString:[NSString stringWithFormat:@"/*!50017 DEFINER=%@ */ ", escapedDefiner]]; - [metaString appendString:[NSString stringWithFormat:@"/*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n", - [[triggers objectForKey:@"Trigger"] backtickQuotedString], - [triggers objectForKey:@"Timing"], - [triggers objectForKey:@"Event"], - [[triggers objectForKey:@"Table"] backtickQuotedString], - [triggers objectForKey:@"Statement"] - ]]; - - [triggers release]; - } - - [metaString appendString:@"DELIMITER ;\n"]; - [metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"]; - - [[self exportOutputFileHandle] writeData:[metaString 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"]; - if ([connection queryErrored]) { - [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; - - if ([self sqlOutputIncludeErrors]) { - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] - dataUsingEncoding:NSUTF8StringEncoding]]; - } - } + [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; + // Drain the autorelease pool + [sqlExportPool release]; } - // Add an additional separator between tables - [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; - } - - // Process any deferred views, adding commands to delete the placeholder tables and add the actual views - for (tableName in viewSyntaxes) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; - } - - [metaString setString:@"\n\n"]; - [metaString appendFormat:@"DROP TABLE %@;\n", [tableName backtickQuotedString]]; - [metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]]; - - [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; - } - - // Export procedures and functions - for (NSString *procedureType in [NSArray arrayWithObjects:@"PROCEDURE", @"FUNCTION", nil]) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; + if ([connection queryErrored]) { + [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; + + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] + dataUsingEncoding:NSUTF8StringEncoding]]; + } } - // Retrieve the array of selected procedures or functions, and skip export if not selected - NSMutableArray *items; - - if ([procedureType isEqualToString:@"PROCEDURE"]) items = procs; - else items = funcs; - - if ([items count] == 0) continue; + // Release the result set + [streamingResult release]; - // Retrieve the definitions - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */;", procedureType, - [[self sqlDatabaseName] tickQuotedString]]]; + queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]]; [queryResult setReturnDataAsStrings:YES]; if ([queryResult numOfRows]) { [metaString setString:@"\n"]; - [metaString appendString:@"--\n"]; - [metaString appendString:[NSString stringWithFormat:@"-- Dumping routines (%@) for database %@\n", procedureType, - [[self sqlDatabaseName] tickQuotedString]]]; - - [metaString appendString:@"--\n"]; [metaString appendString:@"DELIMITER ;;\n"]; - // Loop through the definitions, exporting if enabled for (s = 0; s < [queryResult numOfRows]; s++) { // Check for cancellation flag @@ -582,79 +477,32 @@ return; } - NSDictionary *proceduresList = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; - NSString *procedureName = [NSString stringWithFormat:@"%@", [proceduresList objectForKey:@"Name"]]; - - // Only proceed if the item was selected for export - if (![items containsObject:procedureName]) { - [proceduresList release]; - continue; - } - - // Only proceed if the item is in the list of items - for (NSArray *item in items) - { - // Check for cancellation flag - if ([self isCancelled]) { - [pool release]; - return; - } - - if ([NSArrayObjectAtIndex(item, 0) isEqualToString:procedureName]) { - sqlOutputIncludeStructure = [NSArrayObjectAtIndex(item, 1) boolValue]; - sqlOutputIncludeContent = [NSArrayObjectAtIndex(item, 2) boolValue]; - sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(item, 3) boolValue]; - } - } - - // Add the 'DROP' command if required - if (sqlOutputIncludeDropSyntax) { - [metaString appendString:[NSString stringWithFormat:@"/*!50003 DROP %@ IF EXISTS %@ */;;\n", procedureType, - [procedureName backtickQuotedString]]]; - } - - // Only continue if the 'CREATE SYNTAX' is required - if (sqlOutputIncludeStructure) { - [proceduresList release]; - continue; - } + NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; // Definer is user@host but we need to escape it to `user`@`host` - NSArray *procedureDefiner = [[proceduresList objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; + NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@", - [NSArrayObjectAtIndex(procedureDefiner, 0) backtickQuotedString], - [NSArrayObjectAtIndex(procedureDefiner, 1) backtickQuotedString] + [NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString], + [NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString] ]; - MCPResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */;;", procedureType, - [procedureName backtickQuotedString]]]; - - [createProcedureResult setReturnDataAsStrings:YES]; - - NSDictionary *procedureInfo = [[NSDictionary alloc] initWithDictionary:[createProcedureResult fetchRowAsDictionary]]; - - [metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\"*/;;\n", [procedureInfo objectForKey:@"sql_mode"]]]; - - NSString *createProcedure = [procedureInfo objectForKey:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]]; - NSRange procedureRange = [createProcedure rangeOfString:procedureType options:NSCaseInsensitiveSearch]; - NSString *procedureBody = [createProcedure substringFromIndex:procedureRange.location]; - - // /*!50003 CREATE*/ /*!50020 DEFINER=`sequelpro`@`%`*/ /*!50003 PROCEDURE `p`() - // BEGIN - // /* This procedure does nothing */ - // END */;; - // - // Build the CREATE PROCEDURE string to include MySQL Version limiters - [metaString appendString:[NSString stringWithFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n", escapedDefiner, procedureBody]]; - - [procedureInfo release]; - [proceduresList release]; - - [metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;\n"]; + [metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n", [triggers objectForKey:@"sql_mode"]]]; + [metaString appendString:@"/*!50003 CREATE */ "]; + [metaString appendString:[NSString stringWithFormat:@"/*!50017 DEFINER=%@ */ ", escapedDefiner]]; + [metaString appendString:[NSString stringWithFormat:@"/*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n", + [[triggers objectForKey:@"Trigger"] backtickQuotedString], + [triggers objectForKey:@"Timing"], + [triggers objectForKey:@"Event"], + [[triggers objectForKey:@"Table"] backtickQuotedString], + [triggers objectForKey:@"Statement"] + ]]; + + [triggers release]; } [metaString appendString:@"DELIMITER ;\n"]; + [metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"]; [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; } @@ -663,48 +511,196 @@ [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; if ([self sqlOutputIncludeErrors]) { - [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]]; + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] + dataUsingEncoding:NSUTF8StringEncoding]]; } } } - // Restore unique checks, foreign key checks, and other settings saved at the start - [metaString setString:@"\n"]; - [metaString appendString:@"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"]; - [metaString appendString:@"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"]; - [metaString appendString:@"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n"]; - - //if (sqlOutputIncludeDropSyntax) { - //[metaString appendString:@"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n"]; - //} + // Add an additional separator between tables + [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; + } + + // Process any deferred views, adding commands to delete the placeholder tables and add the actual views + for (tableName in viewSyntaxes) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; + } - // 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"]; + [metaString setString:@"\n\n"]; + [metaString appendFormat:@"DROP TABLE %@;\n", [tableName backtickQuotedString]]; + [metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]]; - // Write footer-type information to the file [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; - - // Set export errors - [self setSqlExportErrors:errors]; - - [errors release]; - [sqlString release]; + } + + // Export procedures and functions + for (NSString *procedureType in [NSArray arrayWithObjects:@"PROCEDURE", @"FUNCTION", nil]) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; + } - // Close the file - [[self exportOutputFileHandle] closeFile]; + // Retrieve the array of selected procedures or functions, and skip export if not selected + NSMutableArray *items; - // Mark the process as not running - [self setExportProcessIsRunning:NO]; + if ([procedureType isEqualToString:@"PROCEDURE"]) items = procs; + else items = funcs; - // Inform the delegate that the export process is complete - [delegate performSelectorOnMainThread:@selector(sqlExportProcessComplete:) withObject:self waitUntilDone:NO]; + if ([items count] == 0) continue; + + // Retrieve the definitions + queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */;", procedureType, + [[self sqlDatabaseName] tickQuotedString]]]; + + [queryResult setReturnDataAsStrings:YES]; + + if ([queryResult numOfRows]) { + + [metaString setString:@"\n"]; + [metaString appendString:@"--\n"]; + [metaString appendString:[NSString stringWithFormat:@"-- Dumping routines (%@) for database %@\n", procedureType, + [[self sqlDatabaseName] tickQuotedString]]]; + + [metaString appendString:@"--\n"]; + [metaString appendString:@"DELIMITER ;;\n"]; + + // Loop through the definitions, exporting if enabled + for (s = 0; s < [queryResult numOfRows]; s++) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; + } + + NSDictionary *proceduresList = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; + NSString *procedureName = [NSString stringWithFormat:@"%@", [proceduresList objectForKey:@"Name"]]; + + // Only proceed if the item was selected for export + if (![items containsObject:procedureName]) { + [proceduresList release]; + continue; + } + + // Only proceed if the item is in the list of items + for (NSArray *item in items) + { + // Check for cancellation flag + if ([self isCancelled]) { + [pool release]; + return; + } + + if ([NSArrayObjectAtIndex(item, 0) isEqualToString:procedureName]) { + sqlOutputIncludeStructure = [NSArrayObjectAtIndex(item, 1) boolValue]; + sqlOutputIncludeContent = [NSArrayObjectAtIndex(item, 2) boolValue]; + sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(item, 3) boolValue]; + } + } + + // Add the 'DROP' command if required + if (sqlOutputIncludeDropSyntax) { + [metaString appendString:[NSString stringWithFormat:@"/*!50003 DROP %@ IF EXISTS %@ */;;\n", procedureType, + [procedureName backtickQuotedString]]]; + } + + // Only continue if the 'CREATE SYNTAX' is required + if (sqlOutputIncludeStructure) { + [proceduresList release]; + continue; + } + + // Definer is user@host but we need to escape it to `user`@`host` + NSArray *procedureDefiner = [[proceduresList objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; + + NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@", + [NSArrayObjectAtIndex(procedureDefiner, 0) backtickQuotedString], + [NSArrayObjectAtIndex(procedureDefiner, 1) backtickQuotedString] + ]; + + MCPResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */;;", procedureType, + [procedureName backtickQuotedString]]]; + + [createProcedureResult setReturnDataAsStrings:YES]; + + NSDictionary *procedureInfo = [[NSDictionary alloc] initWithDictionary:[createProcedureResult fetchRowAsDictionary]]; + + [metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\"*/;;\n", [procedureInfo objectForKey:@"sql_mode"]]]; + + NSString *createProcedure = [procedureInfo objectForKey:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]]; + NSRange procedureRange = [createProcedure rangeOfString:procedureType options:NSCaseInsensitiveSearch]; + NSString *procedureBody = [createProcedure substringFromIndex:procedureRange.location]; + + // /*!50003 CREATE*/ /*!50020 DEFINER=`sequelpro`@`%`*/ /*!50003 PROCEDURE `p`() + // BEGIN + // /* This procedure does nothing */ + // END */;; + // + // Build the CREATE PROCEDURE string to include MySQL Version limiters + [metaString appendString:[NSString stringWithFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n", escapedDefiner, procedureBody]]; + + [procedureInfo release]; + [proceduresList release]; + + [metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;\n"]; + } + + [metaString appendString:@"DELIMITER ;\n"]; + + [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; + } + + if ([connection queryErrored]) { + [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; + + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]]; + } + } - [pool release]; } - @catch (NSException *e) {} + + // Restore unique checks, foreign key checks, and other settings saved at the start + [metaString setString:@"\n"]; + [metaString appendString:@"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"]; + [metaString appendString:@"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"]; + [metaString appendString:@"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n"]; + + //if (sqlOutputIncludeDropSyntax) { + //[metaString appendString:@"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n"]; + //} + + // 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 + [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; + + // Set export errors + [self setSqlExportErrors:errors]; + + [errors release]; + [sqlString release]; + + // Close the file + [[self exportOutputFileHandle] closeFile]; + + // Mark the process as not running + [self setExportProcessIsRunning:NO]; + + // Inform the delegate that the export process is complete + [delegate performSelectorOnMainThread:@selector(sqlExportProcessComplete:) withObject:self waitUntilDone:NO]; + + [pool release]; } /** diff --git a/Source/SPSQLExporterDelegate.m b/Source/SPSQLExporterDelegate.m index b9afe724..8be899f6 100644 --- a/Source/SPSQLExporterDelegate.m +++ b/Source/SPSQLExporterDelegate.m @@ -47,6 +47,7 @@ */ - (void)sqlExportProcessComplete:(SPSQLExporter *)exporter { + [exportProgressIndicator stopAnimation:self]; [NSApp endSheet:exportProgressWindow returnCode:0]; [exportProgressWindow orderOut:self]; @@ -69,8 +70,10 @@ */ - (void)sqlExportProcessProgressUpdated:(SPSQLExporter *)exporter { - //NSLog(@"updating: %f", [exporter exportProgressValue]); - + if ([exportProgressIndicator doubleValue] == 0) { + [exportProgressIndicator stopAnimation:self]; + [exportProgressIndicator setIndeterminate:NO]; + } [exportProgressIndicator setDoubleValue:[exporter exportProgressValue]]; } @@ -79,17 +82,12 @@ */ - (void)sqlExportProcessWillBeginFetchingData:(SPSQLExporter *)exporter { - // Update the current table export index - currentTableExportIndex = (exportTableCount - [exporters count]); + [exportProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %lu of %lu (%@): Fetching data...", @"export label showing that the app is fetching data for a specific table"), [exporter sqlCurrentTableExportIndex], exportTableCount, [exporter sqlExportCurrentTable]]]; - [[exportProgressText onMainThread] setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %lu of %lu (%@): Fetching data...", @"export label showing that the app is fetching data for a specific table"), currentTableExportIndex, exportTableCount, [exporter sqlExportCurrentTable]]]; - - [[exportProgressText onMainThread] displayIfNeeded]; - - [[exportProgressIndicator onMainThread] stopAnimation:self]; - [[exportProgressIndicator onMainThread] setUsesThreadedAnimation:NO]; - [[exportProgressIndicator onMainThread] setIndeterminate:NO]; - [[exportProgressIndicator onMainThread] setDoubleValue:0]; + [exportProgressIndicator startAnimation:self]; + [exportProgressIndicator setUsesThreadedAnimation:YES]; + [exportProgressIndicator setIndeterminate:YES]; + [exportProgressIndicator setDoubleValue:0]; } /** @@ -97,9 +95,7 @@ */ - (void)sqlExportProcessWillBeginWritingData:(SPSQLExporter *)exporter { - [[exportProgressText onMainThread] setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %lu of %lu (%@): Writing data...", @"export label showing app if writing data for a specific table"), currentTableExportIndex, exportTableCount, [exporter sqlExportCurrentTable]]]; - - [[exportProgressText onMainThread] displayIfNeeded]; + [exportProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %lu of %lu (%@): Writing data...", @"export label showing app if writing data for a specific table"), [exporter sqlCurrentTableExportIndex], exportTableCount, [exporter sqlExportCurrentTable]]]; } @end |