diff options
Diffstat (limited to 'Source/SPSQLExporter.m')
| -rw-r--r-- | Source/SPSQLExporter.m | 154 |
1 files changed, 74 insertions, 80 deletions
diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m index c4839444..d898e7a5 100644 --- a/Source/SPSQLExporter.m +++ b/Source/SPSQLExporter.m @@ -86,33 +86,10 @@ - (void)exportOperation { - sqlTableDataInstance = [[[SPTableData alloc] init] autorelease]; - [sqlTableDataInstance setConnection:connection]; - - SPMySQLResult *queryResult; - - NSString *tableName; - NSDictionary *tableDetails; - SPTableType tableType = SPTableTypeTable; - - id createTableSyntax = nil; - NSUInteger j, s; - - BOOL sqlOutputIncludeStructure; - BOOL sqlOutputIncludeContent; - BOOL sqlOutputIncludeDropSyntax; - - NSMutableArray *tables = [NSMutableArray array]; - NSMutableArray *procs = [NSMutableArray array]; - NSMutableArray *funcs = [NSMutableArray array]; - - NSMutableString *metaString = [NSMutableString string]; + // used in end_cleanup NSMutableString *errors = [[NSMutableString alloc] init]; NSMutableString *sqlString = [[NSMutableString alloc] init]; - - NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary]; - - NSString *oldSqlMode = nil; + NSString *oldSqlMode = nil; // Check that we have all the required info before starting the export if ((![self sqlExportTables]) || ([[self sqlExportTables] count] == 0) || @@ -123,6 +100,9 @@ goto end_cleanup; } + sqlTableDataInstance = [[[SPTableData alloc] init] autorelease]; + [sqlTableDataInstance setConnection:connection]; + // Inform the delegate that the export process is about to begin [delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBegin:) withObject:self waitUntilDone:NO]; @@ -132,14 +112,17 @@ // Clear errors [self setSqlExportErrors:@""]; + NSMutableArray *tables = [NSMutableArray array]; + NSMutableArray *procs = [NSMutableArray array]; + NSMutableArray *funcs = [NSMutableArray array]; + // Copy over the selected item names into tables in preparation for iteration - NSMutableArray *targetArray; - - for (NSArray *item in [self sqlExportTables]) + for (NSArray *item in [self sqlExportTables]) { // Check for cancellation flag if ([self isCancelled]) goto end_cleanup; - + + NSMutableArray *targetArray; switch ([NSArrayObjectAtIndex(item, 4) intValue]) { case SPTableTypeProc: targetArray = procs; @@ -156,6 +139,8 @@ [targetArray addObject:item]; } + NSMutableString *metaString = [NSMutableString string]; + // If required write the UTF-8 Byte Order Mark (BOM) if ([self sqlOutputIncludeUTF8BOM]) { [metaString appendString:@"\xef\xbb\xbf"]; @@ -253,15 +238,21 @@ oldSqlMode = nil; } } - // there is no point in writing out that the file should use a specific SQL mode when we don't even know which one was active during export - if(sqlModeIsValid) { - [metaString appendString:@"/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"]; - } + // TODO: + // * There is no point in writing out that the file should use a specific SQL mode when we don't even know which one was active during export. + // * Triggers, procs/funcs have their own SQL_MODE which is still set/reset below, though. + // + // But an unknown SQL_MODE (!sqlModeIsValid) could perhaps still affect how MySQL returns the "SHOW CREATE…" + // data for those objects (like it does for "SHOW CREATE TABLE") possibly rendering them invalid (need to check that), + // so it may be better to flat out refuse to export schema object DDL if we don't have a valid sql_mode. + [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 writeString:metaString]; + NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary]; + // Loop through the selected tables for (NSArray *table in tables) { @@ -269,11 +260,11 @@ if ([self isCancelled]) goto end_cleanup; [self setSqlCurrentTableExportIndex:[self sqlCurrentTableExportIndex]+1]; - tableName = NSArrayObjectAtIndex(table, 0); - - sqlOutputIncludeStructure = [NSArrayObjectAtIndex(table, 1) boolValue]; - sqlOutputIncludeContent = [NSArrayObjectAtIndex(table, 2) boolValue]; - sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(table, 3) boolValue]; + NSString *tableName = NSArrayObjectAtIndex(table, 0); + + BOOL sqlOutputIncludeStructure = [NSArrayObjectAtIndex(table, 1) boolValue]; + BOOL sqlOutputIncludeContent = [NSArrayObjectAtIndex(table, 2) boolValue]; + BOOL sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(table, 3) boolValue]; // Skip tables if not set to output any detail for them if (!sqlOutputIncludeStructure && !sqlOutputIncludeContent && !sqlOutputIncludeDropSyntax) { @@ -290,34 +281,38 @@ // Add the name of table [self writeString:[NSString stringWithFormat:@"# %@ %@\n# ------------------------------------------------------------\n\n", NSLocalizedString(@"Dump of table", @"sql export dump of table label"), tableName]]; - + + id createTableSyntax = nil; + SPTableType tableType = SPTableTypeTable; // 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 numberOfRows]) { - tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]]; - - 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; + { + SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]]; + + [queryResult setReturnDataAsStrings:YES]; + + if ([queryResult numberOfRows]) { + NSDictionary *tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult getRowAsDictionary]]; + + 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]; } - - [tableDetails release]; - } - if ([connection queryErrored]) { - [errors appendFormat:@"%@\n", [connection lastErrorMessage]]; - - [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection lastErrorMessage]]]; - - continue; + if ([connection queryErrored]) { + [errors appendFormat:@"%@\n", [connection lastErrorMessage]]; + + [self writeUTF8String:[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection lastErrorMessage]]]; + + continue; + } } // Add a 'DROP TABLE' command if required @@ -327,8 +322,9 @@ // Add the create syntax for the table if specified in the export dialog if (sqlOutputIncludeStructure && createTableSyntax) { - + if ([createTableSyntax isKindOfClass:[NSData class]]) { +#warning This doesn't make sense. If the NSData really contains a string it would be in utf8, utf8mb4 or a mysql pre-4.1 legacy charset, but not in the export output charset. This whole if() is likely a side effect of the BINARY flag confusion (#2700) createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:[self exportOutputEncoding]] autorelease]; } @@ -343,9 +339,8 @@ // 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:[sqlTableDataInstance informationForTable:tableName]]; + NSDictionary *tableDetails = [NSDictionary dictionaryWithDictionary:[sqlTableDataInstance informationForTable:tableName]]; NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count]; NSMutableArray *rawColumnNames = [NSMutableArray arrayWithCapacity:colCount]; @@ -355,7 +350,7 @@ BOOL *useRawHexDataForColumnAtIndex = calloc(colCount, sizeof(BOOL)); // Determine whether raw data can be used for each column during processing - safe numbers and hex-encoded data. - for (j = 0; j < colCount; j++) + for (NSUInteger j = 0; j < colCount; j++) { NSDictionary *theColumnDetail = NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], j); NSString *theTypeGrouping = [theColumnDetail objectForKey:@"typegrouping"]; @@ -404,7 +399,6 @@ NSUInteger rowCount = [NSArrayObjectAtIndex(rowArray, 0) integerValue]; if (rowCount) { - // Set up a result set in streaming mode SPMySQLStreamingResult *streamingResult = [[connection streamingQueryString:[NSString stringWithFormat:@"SELECT %@ FROM %@", [queryColumnDetails componentsJoinedByString:@", "], [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([self exportUsingLowMemoryBlockingStreaming])] retain]; @@ -458,7 +452,6 @@ [delegate performSelectorOnMainThread:@selector(sqlExportProcessProgressUpdated:) withObject:self waitUntilDone:NO]; } - // Set up the new row as appropriate. If a new INSERT statement should be created, // set one up; otherwise, set up a new row if ((([self sqlInsertDivider] == SPSQLInsertEveryNDataBytes) && (queryLength >= ([self sqlInsertAfterNValue] * 1024))) || @@ -593,7 +586,7 @@ // Add triggers if the structure export was enabled if (sqlOutputIncludeStructure) { - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */", [tableName tickQuotedString]]]; + SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */", [tableName tickQuotedString]]]; [queryResult setReturnDataAsStrings:YES]; @@ -602,7 +595,7 @@ [metaString setString:@"\n"]; [metaString appendString:@"DELIMITER ;;\n"]; - for (s = 0; s < [queryResult numberOfRows]; s++) + for (NSUInteger s = 0; s < [queryResult numberOfRows]; s++) { // Check for cancellation flag if ([self isCancelled]) goto end_cleanup; @@ -639,12 +632,12 @@ } } - // Add an additional separat or between tables + // Add an additional separator between tables [self writeUTF8String:@"\n\n"]; } // Process any deferred views, adding commands to delete the placeholder tables and add the actual views - for (tableName in viewSyntaxes) + for (NSString *viewName in viewSyntaxes) { // Check for cancellation flag if ([self isCancelled]) goto end_cleanup; @@ -652,9 +645,9 @@ [metaString setString:@"\n\n"]; // Add the name of table - [metaString appendFormat:@"# Replace placeholder table for %@ with correct view syntax\n# ------------------------------------------------------------\n\n", tableName]; - [metaString appendFormat:@"DROP TABLE %@;\n\n", [tableName backtickQuotedString]]; - [metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]]; + [metaString appendFormat:@"# Replace placeholder table for %@ with correct view syntax\n# ------------------------------------------------------------\n\n", viewName]; + [metaString appendFormat:@"DROP TABLE %@;\n\n", [viewName backtickQuotedString]]; + [metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:viewName]]; [self writeUTF8String:metaString]; } @@ -674,8 +667,8 @@ if ([items count] == 0) continue; // Retrieve the definitions - queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */", procedureType, - [[self sqlDatabaseName] tickQuotedString]]]; + SPMySQLResult *queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */", procedureType, + [[self sqlDatabaseName] tickQuotedString]]]; [queryResult setReturnDataAsStrings:YES]; @@ -686,7 +679,7 @@ [[self sqlDatabaseName] tickQuotedString]]; // Loop through the definitions, exporting if enabled - for (s = 0; s < [queryResult numberOfRows]; s++) + for (NSUInteger s = 0; s < [queryResult numberOfRows]; s++) { // Check for cancellation flag if ([self isCancelled]) goto end_cleanup; @@ -696,6 +689,8 @@ // Only proceed if the item is in the list of items BOOL itemFound = NO; + BOOL sqlOutputIncludeStructure = NO; + BOOL sqlOutputIncludeDropSyntax = NO; for (NSArray *item in items) { // Check for cancellation flag @@ -707,7 +702,6 @@ if ([NSArrayObjectAtIndex(item, 0) isEqualToString:procedureName]) { itemFound = YES; sqlOutputIncludeStructure = [NSArrayObjectAtIndex(item, 1) boolValue]; - sqlOutputIncludeContent = [NSArrayObjectAtIndex(item, 2) boolValue]; sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(item, 3) boolValue]; break; } |