From 3381e9f906edc5c1bfd6ed14fba8f9aa689848b7 Mon Sep 17 00:00:00 2001 From: bamse16 Date: Mon, 11 Jan 2010 19:15:09 +0000 Subject: Issue 517: Export MySQL Dump does not include triggers or functions First draft on export triggers. Known issues: - Now it exports only triggers for the table that have some content in it. If I move the write triggers part outside of the if (rowCount), the SHOW TRIGGERS query never finishes. - Sequel Pro complains when it tries to import the same file again. The error message is related to DELIMITER command. Still, the triggers seem to be present. This might be a different issue with Sequel Pro. --- Source/TableDump.m | 51 +++++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 43 insertions(+), 8 deletions(-) (limited to 'Source') diff --git a/Source/TableDump.m b/Source/TableDump.m index 5640388b..2b404549 100644 --- a/Source/TableDump.m +++ b/Source/TableDump.m @@ -1591,18 +1591,53 @@ [metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ ENABLE KEYS */;\n", [tableName backtickQuotedString]]]; [metaString appendString:@"UNLOCK TABLES;\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]]; + + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]]; + [queryResult setReturnDataAsStrings:YES]; + if ( [queryResult numOfRows] ) { + [metaString setString:@"\n"]; + [metaString appendString:@"DELIMITER ;;\n"]; + + 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:@"%@@%@", + [[triggersDefiner objectAtIndex:0] backtickQuotedString], + [[triggersDefiner objectAtIndex: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]; } - } - + + [metaString appendString:@"DELIMITER ;\n"]; + [metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"]; + [fileHandle writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]]; + } + // Drain the autorelease pool [exportAutoReleasePool drain]; } + + 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]]; + } + } // Release the result set [streamingResult release]; -- cgit v1.2.3