From c7843fd69a05bf5f13b88bc4588c1105cd2cc411 Mon Sep 17 00:00:00 2001 From: rowanbeentje Date: Sat, 27 Jun 2009 23:20:19 +0000 Subject: - Correctly SQL export views with interdependencies on other views or tables, resolving Issue #313 --- Source/TableDump.h | 1 + Source/TableDump.m | 79 +++++++++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 79 insertions(+), 1 deletion(-) diff --git a/Source/TableDump.h b/Source/TableDump.h index 0bf7ffcc..3419465c 100644 --- a/Source/TableDump.h +++ b/Source/TableDump.h @@ -142,6 +142,7 @@ toFileHandle:(NSFileHandle *)fileHandle tableName:(NSString *)table withHeader:(BOOL)header silently:(BOOL)silently; - (NSString *)htmlEscapeString:(NSString *)string; +- (NSString *)createViewPlaceholderSyntaxForView:(NSString *)viewName; - (BOOL)exportTables:(NSArray *)selectedTables toFileHandle:(NSFileHandle *)fileHandle usingFormat:(NSString *)type usingMulti:(BOOL)multi; - (BOOL)exportSelectedTablesToFileHandle:(NSFileHandle *)fileHandle usingFormat:(NSString *)type; diff --git a/Source/TableDump.m b/Source/TableDump.m index 4cbd858c..70a8f6e8 100644 --- a/Source/TableDump.m +++ b/Source/TableDump.m @@ -833,12 +833,14 @@ NSArray *fieldNames; NSArray *theRow; NSMutableArray *selectedTables = [NSMutableArray array]; + NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary]; NSMutableString *metaString = [NSMutableString string]; NSMutableString *cellValue = [NSMutableString string]; NSMutableString *sqlString = [NSMutableString string]; NSMutableString *errors = [NSMutableString string]; NSDictionary *tableDetails; NSMutableArray *tableColumnNumericStatus; + NSEnumerator *viewSyntaxEnumerator; NSStringEncoding connectionEncoding = [mySQLConnection encoding]; id createTableSyntax = nil; BOOL previousConnectionEncodingViaLatin1; @@ -920,7 +922,8 @@ if ( [queryResult numOfRows] ) { tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]]; if ([tableDetails objectForKey:@"Create View"]) { - createTableSyntax = [[[[tableDetails objectForKey:@"Create View"] copy] autorelease] createViewSyntaxPrettifier]; + [viewSyntaxes setValue:[[[[tableDetails objectForKey:@"Create View"] copy] autorelease] createViewSyntaxPrettifier] forKey:tableName]; + createTableSyntax = [self createViewPlaceholderSyntaxForView:tableName]; tableType = SP_TABLETYPE_VIEW; } else { createTableSyntax = [[[tableDetails objectForKey:@"Create Table"] copy] autorelease]; @@ -1088,6 +1091,15 @@ [fileHandle writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]]; } + // Process any deferred views, adding commands to delete the placeholder tables and add the actual views + viewSyntaxEnumerator = [viewSyntaxes keyEnumerator]; + while (tableName = [viewSyntaxEnumerator nextObject]) { + [metaString setString:@"\n\n"]; + [metaString appendFormat:@"DROP TABLE %@;\n", [tableName backtickQuotedString]]; + [metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]]; + [fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; + } + // Restore unique checks, foreign key checks, and other settings saved at the start [metaString setString:@"\n\n\n"]; [metaString appendString:@"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"]; @@ -1980,6 +1992,71 @@ return [NSString stringWithString:mutableString]; } +/* + * 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 +{ + NSDictionary *viewInformation; + NSMutableString *placeholderSyntax, *fieldString; + NSArray *viewColumns; + NSDictionary *column; + int i; + + // Get structured information for the view via the SPTableData parsers + viewInformation = [tableDataInstance informationForView:viewName]; + if (!viewInformation) return nil; + 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]]; + 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++) { + column = [viewColumns objectAtIndex: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 { + [fieldString appendFormat:@" %@", [column objectForKey:@"type"]]; + } + + // Field specification details + if ([[column objectForKey:@"unsigned"] intValue] == 1) [fieldString appendString:@" UNSIGNED"]; + if ([[column objectForKey:@"zerofill"] intValue] == 1) [fieldString appendString:@" ZEROFILL"]; + if ([[column objectForKey:@"binary"] intValue] == 1) [fieldString appendString:@" BINARY"]; + if ([[column objectForKey:@"null"] intValue] == 0) [fieldString appendString:@" NOT NULL"]; + + // Provide the field default if appropriate + if ([column objectForKey:@"default"]) { + if ([[column objectForKey:@"default"] isEqualToString:@"NULL"]) { + [fieldString appendString:@" DEFAULT NULL"]; + } else if ([[column objectForKey:@"type"] isEqualToString:@"TIMESTAMP"] + && [[[column objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) { + [fieldString appendString:@" DEFAULT CURRENT_TIMESTAMP"]; + } else { + [fieldString appendFormat:@" DEFAULT '%@'", [mySQLConnection 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]; +} + /* * Split a string by the terminated-character if this is not escaped * if enclosed-character is given, ignores characters inside enclosed-characters -- cgit v1.2.3