aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBibiko <bibiko@eva.mpg.de>2009-07-26 21:03:17 +0000
committerBibiko <bibiko@eva.mpg.de>2009-07-26 21:03:17 +0000
commit9b827edbb16a50f3e0c42e0f1c21a9bca3e7a77b (patch)
treeb39ed9f4acd771278e293f712a0a1b093d41615c
parent1e12ff35d75508ab8e098bc2af38b2aacbeba017 (diff)
downloadsequelpro-9b827edbb16a50f3e0c42e0f1c21a9bca3e7a77b.tar.gz
sequelpro-9b827edbb16a50f3e0c42e0f1c21a9bca3e7a77b.tar.bz2
sequelpro-9b827edbb16a50f3e0c42e0f1c21a9bca3e7a77b.zip
• Editing in Custom Query:
- now fields are also identified via the database which the field belongs to in order to allow to select/join/etc. fields coming from different databases and to improve the unambiguity of the field origin - sped up editing check for fields with no table or database origin (then no "select count(*)" is needed)
-rw-r--r--Source/CustomQuery.h6
-rw-r--r--Source/CustomQuery.m72
2 files changed, 49 insertions, 29 deletions
diff --git a/Source/CustomQuery.h b/Source/CustomQuery.h
index 618bd0d0..c88cc879 100644
--- a/Source/CustomQuery.h
+++ b/Source/CustomQuery.h
@@ -99,7 +99,9 @@
BOOL queryIsTableSorter;
BOOL isDesc;
NSNumber *sortField;
-
+
+ NSString *fieldIDQueryString;
+
}
// IBAction methods
@@ -153,7 +155,7 @@
- (void)commentOut;
- (void)commentOutCurrentQueryTakingSelection:(BOOL)takeSelection;
- (NSString *)usedQuery;
-- (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn;
+- (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn andDatabase:(NSString *)database;
@end
diff --git a/Source/CustomQuery.m b/Source/CustomQuery.m
index bf15ff30..894e5cc0 100644
--- a/Source/CustomQuery.m
+++ b/Source/CustomQuery.m
@@ -1209,7 +1209,7 @@
* Collect all columns for a given 'tableForColumn' table and
* return a WHERE clause for identifying the field in quesyion.
*/
-- (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn
+- (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn andDatabase:(NSString *)database
{
NSArray *dataRow;
id field;
@@ -1222,34 +1222,34 @@
}
// Try to identify the field bijectively
- NSMutableString *fieldIDQueryString = [NSMutableString string];
- [fieldIDQueryString setString:@"WHERE ("];
+ NSMutableString *fieldIDQueryStr = [NSMutableString string];
+ [fieldIDQueryStr setString:@"WHERE ("];
// Build WHERE clause
dataRow = [fullResult objectAtIndex:rowIndex];
for(field in columnsForFieldTableName) {
id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] intValue]];
if ([aValue isKindOfClass:[NSNull class]] || [[aValue description] isEqualToString:[prefs stringForKey:@"NullValue"]]) {
- [fieldIDQueryString appendFormat:@"%@ IS NULL", [[field objectForKey:@"org_name"] backtickQuotedString]];
+ [fieldIDQueryStr appendFormat:@"%@ IS NULL", [[field objectForKey:@"org_name"] backtickQuotedString]];
} else {
- [fieldIDQueryString appendFormat:@"%@=", [[field objectForKey:@"org_name"] backtickQuotedString]];
+ [fieldIDQueryStr appendFormat:@"%@=", [[field objectForKey:@"org_name"] backtickQuotedString]];
if ([[field objectForKey:@"typegrouping"] isEqualToString:@"textdata"])
- [fieldIDQueryString appendFormat:@"'%@'", [mySQLConnection prepareString:aValue]];
+ [fieldIDQueryStr appendFormat:@"'%@'", [mySQLConnection prepareString:aValue]];
else if ([[field objectForKey:@"typegrouping"] isEqualToString:@"blobdata"])
- [fieldIDQueryString appendFormat:@"X'%@'", [mySQLConnection prepareBinaryData:aValue]];
+ [fieldIDQueryStr appendFormat:@"X'%@'", [mySQLConnection prepareBinaryData:aValue]];
else if ([[field objectForKey:@"typegrouping"] isEqualToString:@"integer"])
- [fieldIDQueryString appendFormat:@"%@", [aValue description]];
+ [fieldIDQueryStr appendFormat:@"%@", [aValue description]];
else
- [fieldIDQueryString appendFormat:@"'%@'", [mySQLConnection prepareString:aValue]];
+ [fieldIDQueryStr appendFormat:@"'%@'", [mySQLConnection prepareString:aValue]];
}
- [fieldIDQueryString appendString:@" AND "];
+ [fieldIDQueryStr appendString:@" AND "];
}
// Remove last " AND "
- if([fieldIDQueryString length]>12)
- [fieldIDQueryString replaceCharactersInRange:NSMakeRange([fieldIDQueryString length]-5,5) withString:@")"];
+ if([fieldIDQueryStr length]>12)
+ [fieldIDQueryStr replaceCharactersInRange:NSMakeRange([fieldIDQueryStr length]-5,5) withString:@")"];
- return fieldIDQueryString;
+ return fieldIDQueryStr;
}
@@ -1336,6 +1336,8 @@
// Field editing
+ if(fieldIDQueryString == nil) return;
+
NSDictionary *columnDefinition;
// Retrieve the column defintion
@@ -1358,10 +1360,10 @@
// Resolve the original column name if AS was used
NSString *columnName = [columnDefinition objectForKey:@"org_name"];
- NSString *fieldIDQueryString = [self argumentForRow:rowIndex ofTable:tableForColumn];
+ // 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 %@ %@", [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
+ int numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@.%@ %@", [columnDefinition objectForKey:@"db"], [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
if(numberOfPossibleUpdateRows == 1) {
// [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self];
@@ -1388,8 +1390,9 @@
}
[mySQLConnection queryString:
- [NSString stringWithFormat:@"UPDATE %@ SET %@=%@ %@ LIMIT 1",
- [tableForColumn backtickQuotedString], [columnName backtickQuotedString], newObject, fieldIDQueryString]];
+ [NSString stringWithFormat:@"UPDATE %@.%@ SET %@.%@.%@=%@ %@ LIMIT 1",
+ [columnDefinition objectForKey:@"db"], [tableForColumn backtickQuotedString],
+ [columnDefinition objectForKey:@"db"], [tableForColumn backtickQuotedString], [columnName backtickQuotedString], newObject, fieldIDQueryString]];
// [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self];
@@ -1585,7 +1588,9 @@
NSDictionary *columnDefinition;
BOOL noTableName = NO;
+ BOOL isFieldEditable;
BOOL isBlob;
+ int numberOfPossibleUpdateRows = -1;
// Retrieve the column defintion
for(id c in cqColumnDefinition) {
@@ -1604,21 +1609,34 @@
// Resolve the original table name for current column if AS was used
NSString *tableForColumn = [columnDefinition objectForKey:@"org_table"];
-
- // No table name found indicates that the field's column contains data from more than one table as for UNION
- // or the field data are not bound to any table as in SELECT 1
- if(!tableForColumn || ![tableForColumn length])
+
+ // Get the database name which the field belongs to
+ NSString *dbForColumn = [columnDefinition objectForKey:@"db"];
+
+ // No table/database name found indicates that the field's column contains data from more than one table as for UNION
+ // or the field data are not bound to any table as in SELECT 1 or if column database is unset
+ if(!tableForColumn || ![tableForColumn length] || ![dbForColumn length])
noTableName = YES;
- NSString *fieldIDQueryString = [self argumentForRow:rowIndex ofTable:tableForColumn];
+ if(!noTableName) {
+ // if table and database name are given check if field can be identified unambiguously
+ fieldIDQueryString = [self argumentForRow:rowIndex ofTable:tableForColumn andDatabase:[columnDefinition objectForKey:@"db"]];
- // Actual check whether field can be identified bijectively
- int numberOfPossibleUpdateRows = [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ %@", [tableForColumn backtickQuotedString], fieldIDQueryString]] fetchRowAsArray] objectAtIndex:0] intValue];
+ // 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];
- BOOL isFieldEditable = (!noTableName && numberOfPossibleUpdateRows == 1) ? YES : NO;
+ 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":@""]];
+
+ } else {
+ // no table/databse name are given
+ isFieldEditable = NO;
+ fieldIDQueryString = nil;
+ [errorText setStringValue:NSLocalizedString(@"Field is not editable. Field has no or multiple table or database origin(s).",@"field is not editable due to no table/database")];
+ }
- if(!isFieldEditable)
- [errorText setStringValue:[NSString stringWithFormat:@"Field is not editable. Couldn't identify field origin unambiguously (%d match%@).", numberOfPossibleUpdateRows, (numberOfPossibleUpdateRows>1)?@"es":@""]];
SPFieldEditorController *fieldEditor = [[SPFieldEditorController alloc] init];
id editData = [[fieldEditor editWithObject:[[fullResult objectAtIndex:rowIndex] objectAtIndex:[[aTableColumn identifier] intValue]]