aboutsummaryrefslogtreecommitdiffstats
path: root/Source/CustomQuery.m
diff options
context:
space:
mode:
authorBibiko <bibiko@eva.mpg.de>2009-08-11 07:54:47 +0000
committerBibiko <bibiko@eva.mpg.de>2009-08-11 07:54:47 +0000
commit32df64e8f588f6a16f2ad9c821c6db149c3bdb03 (patch)
tree823e7873e639df9d531253b1fb82cc7ebe8e14ee /Source/CustomQuery.m
parentb7c070b098a053175a62b60d27ac10fe10aed72e (diff)
downloadsequelpro-32df64e8f588f6a16f2ad9c821c6db149c3bdb03.tar.gz
sequelpro-32df64e8f588f6a16f2ad9c821c6db149c3bdb03.tar.bz2
sequelpro-32df64e8f588f6a16f2ad9c821c6db149c3bdb03.zip
• improved identification of the field origin for editing in Custom Query
- first try to found a primary key for the to be edited field in the result set - fixed some backtick quotings
Diffstat (limited to 'Source/CustomQuery.m')
-rw-r--r--Source/CustomQuery.m38
1 files changed, 33 insertions, 5 deletions
diff --git a/Source/CustomQuery.m b/Source/CustomQuery.m
index 10b93a31..f37d8613 100644
--- a/Source/CustomQuery.m
+++ b/Source/CustomQuery.m
@@ -1222,6 +1222,7 @@
- (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn andDatabase:(NSString *)database
{
NSArray *dataRow;
+ NSDictionary *theRow;
id field;
//Look for all columns which are coming from "tableForColumn"
@@ -1234,9 +1235,33 @@
// Try to identify the field bijectively
NSMutableString *fieldIDQueryStr = [NSMutableString string];
[fieldIDQueryStr setString:@"WHERE ("];
-
- // Build WHERE clause
+
+ // --- Build WHERE clause ---
dataRow = [fullResult objectAtIndex:rowIndex];
+
+ // Get the primary key if there is one
+ MCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@.%@",
+ [database backtickQuotedString], [tableForColumn backtickQuotedString]]];
+ if ([theResult numOfRows]) [theResult dataSeek:0];
+ int i;
+ for ( i = 0 ; i < [theResult numOfRows] ; i++ ) {
+ theRow = [theResult fetchRowAsDictionary];
+ if ( [[theRow objectForKey:@"Key"] isEqualToString:@"PRI"] ) {
+ for(field in columnsForFieldTableName) {
+ id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] intValue]];
+ if([[field objectForKey:@"org_name"] isEqualToString:[theRow objectForKey:@"Field"]]) {
+ [fieldIDQueryStr appendFormat:@"%@.%@.%@ = %@)",
+ [database backtickQuotedString],
+ [tableForColumn backtickQuotedString],
+ [[theRow objectForKey:@"Field"] backtickQuotedString],
+ [aValue description]];
+ return fieldIDQueryStr;
+ }
+ }
+ }
+ }
+
+ // If there is no primary key, all found fields belonging to the same table are used in the argument
for(field in columnsForFieldTableName) {
id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] intValue]];
if ([aValue isKindOfClass:[NSNull class]] || [[aValue description] isEqualToString:[prefs stringForKey:@"NullValue"]]) {
@@ -1394,7 +1419,7 @@
// NSString *fieldIDQueryString = [self argumentForRow:rowIndex ofTable:tableForColumn];
// Check if the IDstring identifies the current field bijectively
- int numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@.%@ %@", [columnDefinition objectForKey:@"db"], [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
+ int numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@.%@ %@", [[columnDefinition objectForKey:@"db"] backtickQuotedString], [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
if(numberOfPossibleUpdateRows == 1) {
// [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self];
@@ -1707,12 +1732,15 @@
fieldIDQueryString = [self argumentForRow:rowIndex ofTable:tableForColumn andDatabase:[columnDefinition objectForKey:@"db"]];
// Actual check whether field can be identified bijectively
- numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@.%@ %@", [columnDefinition objectForKey:@"db"], [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
+ numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@.%@ %@", [[columnDefinition objectForKey:@"db"] backtickQuotedString], [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
isFieldEditable = (numberOfPossibleUpdateRows == 1) ? YES : NO;
if(!isFieldEditable)
- [errorText setStringValue:[NSString stringWithFormat:@"Field is not editable. Couldn't identify field origin unambiguously (%d match%@).", numberOfPossibleUpdateRows, (numberOfPossibleUpdateRows>1)?@"es":@""]];
+ if(numberOfPossibleUpdateRows == 0)
+ [errorText setStringValue:[NSString stringWithFormat:@"Field is not editable. No matching record found. Try to add the primary key field or more fields in your SELECT statement for table '%@' to identify field origin unambiguously.", tableForColumn]];
+ else
+ [errorText setStringValue:[NSString stringWithFormat:@"Field is not editable. Couldn't identify field origin unambiguously (%d match%@).", numberOfPossibleUpdateRows, (numberOfPossibleUpdateRows>1)?@"es":@""]];
} else {
// no table/databse name are given