From 4d04f38f03981bf09a7b257afdc8f4d8a7b7f8e4 Mon Sep 17 00:00:00 2001 From: bamse16 Date: Thu, 14 Jan 2010 18:07:51 +0000 Subject: Issue 517: Export MySQL Dump does not include triggers or functions - added support for dump functions. If no errors are found, Issue 517 can be closed. --- Source/TableDump.m | 123 ++++++++++++++++++++++++++++------------------------- 1 file changed, 65 insertions(+), 58 deletions(-) (limited to 'Source/TableDump.m') diff --git a/Source/TableDump.m b/Source/TableDump.m index bcd595be..1d1987be 100644 --- a/Source/TableDump.m +++ b/Source/TableDump.m @@ -1660,69 +1660,76 @@ [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]; + for (NSString *procedureType in [NSArray arrayWithObjects:@"PROCEDURE", @"FUNCTION", nil]) { + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */;", + procedureType, + [[tableDocumentInstance database] tickQuotedString]]]; + [queryResult setReturnDataAsStrings:YES]; + if ( [queryResult numOfRows] ) { + [metaString setString:@"\n"]; + [metaString appendString:@"--\n"]; + [metaString appendString:[NSString stringWithFormat:@"-- Dumping routines (%@) for database %@\n", + procedureType, + [[tableDocumentInstance database] tickQuotedString]]]; + [metaString appendString:@"--\n"]; + [metaString appendString:@"DELIMITER ;;\n"]; - // /*!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]; + 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 %@ IF EXISTS %@ */;;\n", + procedureType, + [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 %@ %@ */;;", + procedureType, + [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:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]]; + NSRange procedureRange = [createProcedure rangeOfString:procedureType 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:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;\n"]; + [metaString appendString:@"DELIMITER ;\n"]; + [fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; } - [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]]; + 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 -- cgit v1.2.3