From 1869ba8667ba594d55a39b36933a8e93ef359f51 Mon Sep 17 00:00:00 2001 From: bamse16 Date: Thu, 14 Jan 2010 14:57:56 +0000 Subject: 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. --- Source/TableDump.m | 76 ++++++++++++++++++++++++++++++++++++++++++++++++++---- 1 file 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]; -- cgit v1.2.3