diff options
Diffstat (limited to 'Source/SPSQLExporter.m')
| -rw-r--r-- | Source/SPSQLExporter.m | 147 |
1 files changed, 80 insertions, 67 deletions
diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m index 607f34d5..dab27872 100644 --- a/Source/SPSQLExporter.m +++ b/Source/SPSQLExporter.m @@ -29,7 +29,7 @@ #import "SPExportUtilities.h" #import "SPExportFile.h" #import "SPTableData.h" -#import "SPMySQL.h" +#import <SPMySQL/SPMySQL.h> @interface SPSQLExporter (PrivateAPI) @@ -106,7 +106,6 @@ NSMutableArray *funcs = [NSMutableArray array]; NSMutableString *metaString = [NSMutableString string]; - NSMutableString *cellValue = [NSMutableString string]; NSMutableString *errors = [[NSMutableString alloc] init]; NSMutableString *sqlString = [[NSMutableString alloc] init]; @@ -280,6 +279,8 @@ { // Check for cancellation flag if ([self isCancelled]) { + [errors release]; + [sqlString release]; [pool release]; return; } @@ -338,50 +339,58 @@ [streamingResult cancelResultLoad]; [streamingResult release]; [sqlExportPool release]; + [errors release]; + [sqlString release]; [pool release]; - + return; } - + j++; k++; - + [sqlString setString:@""]; - - // Update the progress + + // Update the progress NSUInteger progress = (NSUInteger)(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]; } - - for (t = 0; t < colCount; t++) + + for (t = 0; t < colCount; t++) { // Check for cancellation flag if ([self isCancelled]) { + [connection cancelCurrentQuery]; + [streamingResult cancelResultLoad]; + [streamingResult release]; [sqlExportPool release]; + [errors release]; + [sqlString release]; [pool release]; - + return; } - + id object = NSArrayObjectAtIndex(row, t); - - // Add NULL values directly to the output row + + // Add NULL values directly to the output row; use a pointer comparison to the singleton + // instance for speed. if (object == [NSNull null]) { [sqlString appendString:@"NULL"]; - } + } // If the field is off type BIT, the values need a binary prefix of b'x'. else if ([[NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], t) objectForKey:@"type"] isEqualToString:@"BIT"]) { [sqlString appendFormat:@"b'%@'", [object description]]; } // Add data types directly as hex data else if ([object isKindOfClass:[NSData class]]) { - + if ([self sqlOutputEncodeBLOBasHex]) { [sqlString appendString:[connection escapeAndQuoteData:object]]; } @@ -407,19 +416,18 @@ [sqlString appendString:[connection escapeAndQuoteData:[object data]]]; } else { - [cellValue setString:[object description]]; // Add empty strings as a pair of quotes - if ([cellValue length] == 0) { + if ([object length] == 0) { [sqlString appendString:@"''"]; } else { if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) { - [sqlString appendString:cellValue]; + [sqlString appendString:object]; } // Otherwise add a quoted string with special characters escaped else { - [sqlString appendString:[connection escapeAndQuoteString:cellValue]]; + [sqlString appendString:[connection escapeAndQuoteString:object]]; } } } @@ -486,56 +494,59 @@ } } } + + // Add triggers if the structure export was enabled + if (sqlOutputIncludeStructure) { + queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */", [tableName tickQuotedString]]]; - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]]; - - [queryResult setReturnDataAsStrings:YES]; - - if ([queryResult numberOfRows]) { - - [metaString setString:@"\n"]; - [metaString appendString:@"DELIMITER ;;\n"]; + [queryResult setReturnDataAsStrings:YES]; - for (s = 0; s < [queryResult numberOfRows]; s++) - { - // Check for cancellation flag - if ([self isCancelled]) { - [errors release]; - [sqlString release]; - [pool release]; - return; + if ([queryResult numberOfRows]) { + + [metaString setString:@"\n"]; + [metaString appendString:@"DELIMITER ;;\n"]; + + for (s = 0; s < [queryResult numberOfRows]; s++) + { + // Check for cancellation flag + if ([self isCancelled]) { + [errors release]; + [sqlString release]; + [pool release]; + return; + } + + NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]]; + + // Definer is user@host but we need to escape it to `user`@`host` + NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; + + [metaString appendFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n/*!50003 CREATE */ ", [triggers objectForKey:@"sql_mode"]]; + [metaString appendFormat:@"/*!50017 DEFINER=%@@%@ */ /*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n", + [NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString], + [NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString], + [[triggers objectForKey:@"Trigger"] backtickQuotedString], + [triggers objectForKey:@"Timing"], + [triggers objectForKey:@"Event"], + [[triggers objectForKey:@"Table"] backtickQuotedString], + [triggers objectForKey:@"Statement"] + ]; + + [triggers release]; } - NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]]; + [metaString appendString:@"DELIMITER ;\n/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"]; - // Definer is user@host but we need to escape it to `user`@`host` - NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"]; - - [metaString appendFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n/*!50003 CREATE */ ", [triggers objectForKey:@"sql_mode"]]; - [metaString appendFormat:@"/*!50017 DEFINER=%@@%@ */ /*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n", - [NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString], - [NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString], - [[triggers objectForKey:@"Trigger"] backtickQuotedString], - [triggers objectForKey:@"Timing"], - [triggers objectForKey:@"Event"], - [[triggers objectForKey:@"Table"] backtickQuotedString], - [triggers objectForKey:@"Statement"] - ]; - - [triggers release]; + [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; } - [metaString appendString:@"DELIMITER ;\n/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"]; - - [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; - } - - if ([connection queryErrored]) { - [errors appendFormat:@"%@\n", [connection lastErrorMessage]]; - - if ([self sqlOutputIncludeErrors]) { - [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]] - dataUsingEncoding:NSUTF8StringEncoding]]; + if ([connection queryErrored]) { + [errors appendFormat:@"%@\n", [connection lastErrorMessage]]; + + if ([self sqlOutputIncludeErrors]) { + [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection lastErrorMessage]] + dataUsingEncoding:NSUTF8StringEncoding]]; + } } } @@ -583,7 +594,7 @@ if ([items count] == 0) continue; // Retrieve the definitions - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */;", procedureType, + queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */", procedureType, [[self sqlDatabaseName] tickQuotedString]]]; [queryResult setReturnDataAsStrings:YES]; @@ -658,7 +669,7 @@ [NSArrayObjectAtIndex(procedureDefiner, 1) backtickQuotedString] ]; - SPMySQLResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */;;", procedureType, + SPMySQLResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */", procedureType, [procedureName backtickQuotedString]]]; [createProcedureResult setReturnDataAsStrings:YES]; if ([connection queryErrored]) { @@ -824,7 +835,8 @@ // 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 + // Some MySQL server versions show a default of NULL for NOT NULL columns - don't export those. + // Check against the NSNull singleton instance for speed. if ([column objectForKey:@"default"] == [NSNull null]) { if ([[column objectForKey:@"null"] integerValue]) { [fieldString appendString:@" DEFAULT NULL"]; @@ -862,6 +874,7 @@ [sqlDatabaseName release], sqlDatabaseName = nil; [sqlExportCurrentTable release], sqlExportCurrentTable = nil; [sqlDatabaseVersion release], sqlDatabaseVersion = nil; + [sqlExportErrors release], sqlExportErrors = nil; [super dealloc]; } |