diff options
Diffstat (limited to 'Source/SPSQLExporter.m')
| -rw-r--r-- | Source/SPSQLExporter.m | 772 |
1 files changed, 770 insertions, 2 deletions
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 <http://code.google.com/p/sequel-pro/> +#import <MCPKit/MCPKit.h> + #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 |