A number of exporter improvements:

 - Improve interface validation on the SQL view, including fixing no-content toggling
 - Fix the export of views to correctly construct placeholder tables and fix view syntax export (this addresses Issue #707)
 - Fix logic controlling Stored Procedure and Function export - fix hangs and allow correct export
 - Handle permission errors when retrieving Stored Pro/Function syntax
 - Improve export of linebreaks in CSV quoted cells for improved Excel compatibility
 - SQL export now retrieves table syntax as it progresses through the tables - more accurate progress bar and removes initial pause when exporting lots of tables
 - Alter filename construction to use centralised filename string: fixes end export Growl notification
 - Improve dump comments

1 files changed, 97 insertions, 69 deletions

diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m
index eda13741..1c1ebadc 100644
--- a/Source/SPSQLExporter.m
+++ b/Source/SPSQLExporter.m
@@ -32,6 +32,7 @@
 #import "SPStringAdditions.h"
 #import "SPFileHandle.h"
 #import "SPExportUtilities.h"
+#import "SPTableData.h"
 
 @interface SPSQLExporter (PrivateAPI)
 
@@ -53,7 +54,6 @@
 @synthesize sqlOutputIncludeErrors;
 @synthesize sqlOutputCompressFile;
 @synthesize sqlCurrentTableExportIndex;
-@synthesize sqlTableInformation;
 
 /**
  * Initialise an instance of SPSQLExporter using the supplied delegate.
@@ -77,6 +77,9 @@
 - (void)main
 {
 	NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
+	sqlTableDataInstance = [[[SPTableData alloc] init] autorelease];
+	[sqlTableDataInstance setConnection:connection];
+
 	NSAutoreleasePool *sqlExportPool = [[NSAutoreleasePool alloc] init];
 			
 	MCPResult *queryResult;
@@ -108,7 +111,6 @@
 			
 	// 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:@""])))
@@ -192,7 +194,7 @@
 	[[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]];
 			
 	// Loop through the selected tables
-	for (NSArray *table in [self sqlExportTables]) 
+	for (NSArray *table in tables) 
 	{
 		// Check for cancellation flag
 		if ([self isCancelled]) {
@@ -268,7 +270,7 @@
 		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]];
+			tableDetails = [NSDictionary dictionaryWithDictionary:[sqlTableDataInstance informationForTable:tableName]];
 							
 			NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count];
 			
@@ -461,63 +463,62 @@
 			
 			// Release the result set
 			[streamingResult release];
+		}
 			
-			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 numOfRows]) {
 			
-			[queryResult setReturnDataAsStrings:YES];
+			[metaString setString:@"\n"];
+			[metaString appendString:@"DELIMITER ;;\n"];
 			
-			if ([queryResult numOfRows]) {
+			for (s = 0; s < [queryResult numOfRows]; s++) 
+			{
+				// Check for cancellation flag
+				if ([self isCancelled]) {
+					[pool release];
+					return;
+				}
 				
-				[metaString setString:@"\n"];
-				[metaString appendString:@"DELIMITER ;;\n"];
+				NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]];
 				
-				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];
-				}
+				// Definer is user@host but we need to escape it to `user`@`host`
+				NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"];
 				
-				[metaString appendString:@"DELIMITER ;\n"];
-				[metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"];
+				NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@", 
+											[NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString],
+											[NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString]
+											];
 				
-				[[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+				[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];
 			}
 			
-			if ([connection queryErrored]) {
-				[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
-				
-				if ([self sqlOutputIncludeErrors]) {
-					[[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]]
-										   dataUsingEncoding:NSUTF8StringEncoding]];
-				}
-			}
+			[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
@@ -534,6 +535,8 @@
 		}
 		
 		[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", [tableName backtickQuotedString]];
 		[metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]];
 		
@@ -571,7 +574,7 @@
 									  [[self sqlDatabaseName] tickQuotedString]]];
 			
 			[metaString appendString:@"--\n"];
-			[metaString appendString:@"DELIMITER ;;\n"];
+			[metaString appendString:@"DELIMITER ;;\n\n"];
 			
 			// Loop through the definitions, exporting if enabled
 			for (s = 0; s < [queryResult numOfRows]; s++) 
@@ -581,32 +584,37 @@
 					[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
+				BOOL itemFound = NO;
 				for (NSArray *item in items)
 				{
 					// Check for cancellation flag
 					if ([self isCancelled]) {
+						[proceduresList release];
 						[pool release];
 						return;
 					}
 					
 					if ([NSArrayObjectAtIndex(item, 0) isEqualToString:procedureName]) {
+						itemFound = YES;
 						sqlOutputIncludeStructure  = [NSArrayObjectAtIndex(item, 1) boolValue];
 						sqlOutputIncludeContent    = [NSArrayObjectAtIndex(item, 2) boolValue];
 						sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(item, 3) boolValue];
+						break;
 					}
 				}
-				
+				if (!itemFound) {
+					[proceduresList release];
+					continue;
+				}
+
+				if (sqlOutputIncludeStructure || sqlOutputIncludeDropSyntax)
+					[metaString appendFormat:@"# Dump of %@ %@\n# ------------------------------------------------------------\n\n", procedureType, procedureName];
+
 				// Add the 'DROP' command if required
 				if (sqlOutputIncludeDropSyntax) {
 					[metaString appendString:[NSString stringWithFormat:@"/*!50003 DROP %@ IF EXISTS %@ */;;\n", procedureType,
@@ -614,7 +622,7 @@
 				}
 				
 				// Only continue if the 'CREATE SYNTAX' is required
-				if (sqlOutputIncludeStructure) {
+				if (!sqlOutputIncludeStructure) {
 					[proceduresList release];
 					continue;
 				}
@@ -629,14 +637,35 @@
 				
 				MCPResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */;;", procedureType,
 																			[procedureName backtickQuotedString]]];
-				
 				[createProcedureResult setReturnDataAsStrings:YES];
+				if ([connection queryErrored]) {
+					[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
+					
+					if ([self sqlOutputIncludeErrors]) {
+						[[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+					}
+					[proceduresList release];
+					continue;
+				}
 				
 				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]]];			
+				NSString *createProcedure = [procedureInfo objectForKey:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]];
+				
+				// A NULL result indicates a permission problem
+				if ([createProcedure isNSNull]) {
+					NSString *errorString = [NSString stringWithFormat:NSLocalizedString(@"Could not export the %@ '%@' because of a permisions error.\n", @"Procedure/function export permission error"), procedureType, procedureName];
+					[errors appendString:errorString];
+					if ([self sqlOutputIncludeErrors]) {
+						[[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", errorString] dataUsingEncoding:NSUTF8StringEncoding]];
+					}
+					[proceduresList release];
+					[procedureInfo release];
+					continue;
+				}
+
 				NSRange procedureRange    = [createProcedure rangeOfString:procedureType options:NSCaseInsensitiveSearch];
 				NSString *procedureBody   = [createProcedure substringFromIndex:procedureRange.location];
 				
@@ -646,7 +675,7 @@
 				// END */;;
 				//
 				// Build the CREATE PROCEDURE string to include MySQL Version limiters
-				[metaString appendString:[NSString stringWithFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n", escapedDefiner, procedureBody]];
+				[metaString appendString:[NSString stringWithFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n\n", escapedDefiner, procedureBody]];
 				
 				[procedureInfo release];
 				[proceduresList release];
@@ -723,12 +752,12 @@
 	NSMutableString *placeholderSyntax;
 	
 	// Get structured information for the view via the SPTableData parsers
-	NSDictionary *viewInformation = [[self sqlTableInformation] objectForKey:viewName];
+	NSDictionary *viewInformation = [sqlTableDataInstance informationForView: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]];
 	
@@ -788,7 +817,7 @@
 	}
 	
 	// Append the remainder of the table string
-	[placeholderSyntax appendString:@") ENGINE=MyISAM;"];
+	[placeholderSyntax appendString:@") ENGINE=MyISAM"];
 	
 	// Clean up and return
 	[fieldString release];
@@ -806,7 +835,6 @@
 	[sqlDatabaseName release], sqlDatabaseName = nil;
 	[sqlExportCurrentTable release], sqlExportCurrentTable = nil;
 	[sqlDatabaseVersion release], sqlDatabaseVersion = nil;
-	[sqlTableInformation release], sqlTableInformation = nil;
 	
 	[super dealloc];
 }