From bbe0f861dd4e3ab99aa3d555d3fc5db5ee5ae39d Mon Sep 17 00:00:00 2001 From: stuconnolly Date: Mon, 24 May 2010 18:07:43 +0000 Subject: Merge export redesign branch back into trunk. Includes a completely redesign approach to all export data types based on the use of NSOperation subclasses. CSV, SQL, XML and dot export types are currently functional, while the source files for PDF and HTML export types exist they are to be implemented, but are currently hidden from the interface. Also includes the following: - Completely redesigned export interface. - The ability to customize CSV NULL values. - The ability to specify whether the UTF-8 BOM should be used in SQL dumps. - The ability to specify whether BLOB fields are output as hex or plain text during SQL dumps. Defaults to hex. - Exporting currently selected tables via the tables list context menu. Outstanding issues: - Not all progress indicators for all export types are functional (or functioning correctly). - A few issues related to the introduction of only exporting the content and create and drop syntax of specific tables during SQL dumps. Needs some serious testing and benchmarking to ensure it replicates the current export functionality. --- Source/SPSQLExporter.m | 772 ++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 770 insertions(+), 2 deletions(-) (limited to 'Source/SPSQLExporter.m') diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m index db9e4532..9b7cbaf6 100644 --- a/Source/SPSQLExporter.m +++ b/Source/SPSQLExporter.m @@ -23,26 +23,794 @@ // // More info at +#import + #import "SPSQLExporter.h" +#import "TablesList.h" +#import "SPConstants.h" +#import "SPArrayAdditions.h" +#import "SPStringAdditions.h" +#import "SPFileHandle.h" +#import "SPExportUtilities.h" + +@interface SPSQLExporter (PrivateAPI) + +- (NSString *)_createViewPlaceholderSyntaxForView:(NSString *)viewName; + +@end @implementation SPSQLExporter +@synthesize delegate; +@synthesize sqlExportTables; +@synthesize sqlDatabaseHost; +@synthesize sqlDatabaseName; +@synthesize sqlDatabaseVersion; +@synthesize sqlExportCurrentTable; +@synthesize sqlExportErrors; +@synthesize sqlOutputIncludeUTF8BOM; +@synthesize sqlOutputEncodeBLOBasHex; +@synthesize sqlOutputIncludeErrors; +@synthesize sqlOutputCompressFile; +@synthesize sqlTableInformation; + +/** + * Initialise an instance of SPSQLExporter using the supplied delegate. + */ +- (id)initWithDelegate:(NSObject *)exportDelegate +{ + if ((self = [super init])) { + SPExportDelegateConformsToProtocol(exportDelegate, @protocol(SPSQLExporterProtocol)); + + [self setDelegate:exportDelegate]; + [self setSqlExportCurrentTable:nil]; + } + + return self; +} + /** - * Start the SQL data conversion process. This method is automatically called when an instance of this object + * Start the SQL export process. This method is automatically called when an instance of this class * is placed on an NSOperationQueue. Do not call it directly as there is no manual multithreading. */ - (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; + + 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; + } + + switch ([NSArrayObjectAtIndex(item, 4) intValue]) { + case SPTableTypeProc: + targetArray = procs; + break; + case SPTableTypeFunc: + targetArray = funcs; + break; + default: + targetArray = tables; + break; + } + + [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"]; + } + + // 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; + } + + 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]]]; + + [queryResult setReturnDataAsStrings:YES]; + + 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; + } + + [tableDetails release]; + } + + if ([connection queryErrored]) { + [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; + + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]]; + } + } + + // 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]]]; + + + // 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]; + } + + [[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)) { + + // 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]; + + tableColumnNumericStatus = [NSMutableArray arrayWithCapacity:colCount]; + + for (j = 0; j < colCount; j++) + { + // Check for cancellation flag + if ([self isCancelled]) { + [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]; + + while (row = [streamingResult fetchNextRowAsArray]) + { + // 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; + + // 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; + } + + // 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]]) { + + 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]; + } + + [sqlString appendString:@"'"]; + } + else { + [cellValue setString:[NSArrayObjectAtIndex(row, t) description]]; + + // Add empty strings as a pair of quotes + if ([cellValue length] == 0) { + [sqlString appendString:@"''"]; + } + 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:@"'"]; + } + } + } + + // 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) { + + // 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("]; + } + } + else { + [sqlString appendString:@")"]; + } + + // Write this row to the file + [[self exportOutputFileHandle] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]]; + } + + // 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]; + } + + 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]; + + 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]]; + } + + if ([connection queryErrored]) { + [errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]]; + + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] + dataUsingEncoding:NSUTF8StringEncoding]]; + } + } + + } + + // 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; + } + + // 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; + + // 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]]; + } + } + + } + + // 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]; } - @catch (NSException *e) { + @catch (NSException *e) {} +} + +/** + * Returns whether or not any export errors occurred by examing the length of the errors string. + */ +- (BOOL)didExportErrorsOccur +{ + return [[self sqlExportErrors] length]; +} + +/** + * Retrieve information for a view and use that to construct a CREATE TABLE string for an equivalent basic + * table. Allows the construction of placeholder tables to resolve view interdependencies in dumps. + */ +- (NSString *)_createViewPlaceholderSyntaxForView:(NSString *)viewName +{ + NSInteger i, j; + NSMutableString *placeholderSyntax; + + // Get structured information for the view via the SPTableData parsers + NSDictionary *viewInformation = [[self sqlTableInformation] objectForKey:viewName]; + + if (!viewInformation) return nil; + + NSArray *viewColumns = [viewInformation objectForKey:@"columns"]; + + // Set up the start of the placeholder string and initialise an empty field string + placeholderSyntax = [[NSMutableString alloc] initWithFormat:@"CREATE TABLE %@ (\n", [viewName backtickQuotedString]]; + + NSMutableString *fieldString = [[NSMutableString alloc] init]; + + // Loop through the columns, creating an appropriate column definition for each and appending it to the syntax string + for (i = 0; i < [viewColumns count]; i++) + { + NSDictionary *column = NSArrayObjectAtIndex(viewColumns, i); + + [fieldString setString:[[column objectForKey:@"name"] backtickQuotedString]]; + + // Add the type and length information as appropriate + if ([column objectForKey:@"length"]) { + [fieldString appendFormat:@" %@(%@)", [column objectForKey:@"type"], [column objectForKey:@"length"]]; + } + else if ([column objectForKey:@"values"]) { + [fieldString appendFormat:@" %@(", [column objectForKey:@"type"]]; + + for (j = 0; j < [[column objectForKey:@"values"] count]; j++) + { + [fieldString appendFormat:@"'%@'%@", [connection prepareString:NSArrayObjectAtIndex([column objectForKey:@"values"], j)], ((j + 1) == [[column objectForKey:@"values"] count]) ? @"" : @","]; + } + + [fieldString appendString:@")"]; + } + else { + [fieldString appendFormat:@" %@", [column objectForKey:@"type"]]; + } + + // Field specification details + if ([[column objectForKey:@"unsigned"] integerValue] == 1) [fieldString appendString:@" UNSIGNED"]; + if ([[column objectForKey:@"zerofill"] integerValue] == 1) [fieldString appendString:@" ZEROFILL"]; + if ([[column objectForKey:@"binary"] integerValue] == 1) [fieldString appendString:@" BINARY"]; + if ([[column objectForKey:@"null"] integerValue] == 0) [fieldString appendString:@" NOT NULL"]; + // Provide the field default if appropriate + if ([column objectForKey:@"default"]) { + + // Some MySQL server versions show a default of NULL for NOT NULL columns - don't export those + if ([column objectForKey:@"default"] == [NSNull null]) { + if ([[column objectForKey:@"null"] integerValue]) { + [fieldString appendString:@" DEFAULT NULL"]; + } + } + else if ([[column objectForKey:@"type"] isEqualToString:@"TIMESTAMP"] && [column objectForKey:@"default"] != [NSNull null] && [[[column objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) { + [fieldString appendString:@" DEFAULT CURRENT_TIMESTAMP"]; + } + else { + [fieldString appendFormat:@" DEFAULT '%@'", [connection prepareString:[column objectForKey:@"default"]]]; + } + } + + // Extras aren't required for the temp table + // Add the field string to the syntax string + [placeholderSyntax appendFormat:@" %@%@\n", fieldString, (i == [viewColumns count] - 1) ? @"" : @","]; } + + // Append the remainder of the table string + [placeholderSyntax appendString:@") ENGINE=MyISAM;"]; + + // Clean up and return + [fieldString release]; + + return [placeholderSyntax autorelease]; +} + +/** + * Dealloc + */ +- (void)dealloc +{ + [sqlExportTables release], sqlExportTables = nil; + [sqlDatabaseHost release], sqlDatabaseHost = nil; + [sqlDatabaseName release], sqlDatabaseName = nil; + [sqlExportCurrentTable release], sqlExportCurrentTable = nil; + [sqlDatabaseVersion release], sqlDatabaseVersion = nil; + [sqlTableInformation release], sqlTableInformation = nil; + + [super dealloc]; } @end -- cgit v1.2.3