Issue 517: Export MySQL Dump does not include triggers or functions

 - included export for proceduers. Need to add support for functions and test on different databases.

1 files changed, 71 insertions, 5 deletions

diff --git a/Source/TableDump.m b/Source/TableDump.m
index 88cb3ead..bcd595be 100644
--- a/Source/TableDump.m
+++ b/Source/TableDump.m
@@ -1609,8 +1609,8 @@
 			// Release the result set
 			[streamingResult release];
 			
-			
-			queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]];
+			queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", 
+														[tableName tickQuotedString]]];
 			[queryResult setReturnDataAsStrings:YES];
 			if ( [queryResult numOfRows] ) {
 				[metaString setString:@"\n"];
@@ -1619,7 +1619,6 @@
 				for (int t=0; t<[queryResult numOfRows]; t++) {
 					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:@"%@@%@", 
@@ -1627,9 +1626,11 @@
 												[[triggersDefiner objectAtIndex:1] backtickQuotedString]
 												];
 					
-					[metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n", [triggers objectForKey:@"sql_mode"]]];
+					[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:@"/*!50017 DEFINER=%@ */ ", 
+											  escapedDefiner]];
 					[metaString appendString:[NSString stringWithFormat:@"/*!50003 TRIGGER %@ %@ %@ ON %@ FOR EACH ROW %@ */;;\n",
 											  [[triggers objectForKey:@"Trigger"] backtickQuotedString],
 											  [triggers objectForKey:@"Timing"],
@@ -1658,6 +1659,71 @@
 		// Add an additional separator between tables
 		[fileHandle writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
 	}
+	
+	queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW PROCEDURE STATUS WHERE `Db` = %@ */;", 
+												[[tableDocumentInstance database] tickQuotedString]]];
+	[queryResult setReturnDataAsStrings:YES];
+	if ( [queryResult numOfRows] ) {
+		[metaString setString:@"\n"];
+		[metaString appendString:@"--\n"];
+		[metaString appendString:[NSString stringWithFormat:@"-- Dumping routines for database %@\n", 
+								  [[tableDocumentInstance database] tickQuotedString]]];
+		[metaString appendString:@"--\n"];
+		[metaString appendString:@"DELIMITER ;;\n"];
+		
+		for (int t=0; t<[queryResult numOfRows]; t++) {
+			NSDictionary *proceduresList = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]];
+			NSString *procedureName = [NSString stringWithFormat:@"%@", [proceduresList objectForKey:@"Name"]];
+			
+			[metaString appendString:[NSString stringWithFormat:@"/*!50003 DROP PROCEDURE IF EXISTS %@ */;;\n", 
+									  [procedureName backtickQuotedString]]];
+			
+			//Definer is user@host but we need to escape it to `user`@`host`
+			NSArray *procedureDefiner = [[proceduresList objectForKey:@"Definer"] componentsSeparatedByString:@"@"];
+			NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@", 
+										[[procedureDefiner objectAtIndex:0] backtickQuotedString],
+										[[procedureDefiner objectAtIndex:1] backtickQuotedString]
+										];
+			
+			MCPResult *createProcedureResult;
+			createProcedureResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE PROCEDURE %@ */;;", 
+														[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:@"Create Procedure"];			
+			NSRange procedureRange = [createProcedure rangeOfString:@"procedure" 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"];
+		[fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+	}
+	
+	if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+		[errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
+		if ( [addErrorsSwitch state] == NSOnState ) {
+			[fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]]
+								   dataUsingEncoding:NSUTF8StringEncoding]];
+		}
+	}
 
 	// Process any deferred views, adding commands to delete the placeholder tables and add the actual views
 	viewSyntaxEnumerator = [viewSyntaxes keyEnumerator];