- db structure for tables and views will now be queried by using SHOW… statements

- completion now works for server versions < 5 as well
- proc/func info will be queried by using information_schema for mysql >= 5
- changed completion logic to handle new format
- completion will get the info from SPNavigator since this controller caches all information about db structure

- optimization and fine-tuning will follow very soon

4 files changed, 79 insertions, 60 deletions

diff --git a/Source/CMTextView.m b/Source/CMTextView.m
index 7a39a067..abc37865 100644
--- a/Source/CMTextView.m
+++ b/Source/CMTextView.m
@@ -33,6 +33,7 @@
 #import "SPQueryController.h"
 #import "SPTooltip.h"
 #import "TablesList.h"
+#import "SPNavigatorController.h"
 
 #pragma mark -
 #pragma mark lex init
@@ -308,9 +309,10 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 		else
 			connectionID = @"_";
 
-		NSDictionary *dbs = [NSDictionary dictionaryWithDictionary:[[mySQLConnection getDbStructure] objectForKey:connectionID]];
+		// NSDictionary *dbs = [NSDictionary dictionaryWithDictionary:[[mySQLConnection getDbStructure] objectForKey:connectionID]];
+		NSDictionary *dbs = [NSDictionary dictionaryWithDictionary:[[SPNavigatorController sharedNavigatorController] dbStructureForConnection:connectionID]];
 
-		if(dbs != nil && [dbs count]) {
+		if(dbs != nil && [dbs isKindOfClass:[NSDictionary class]] && [dbs count]) {
 			NSMutableArray *allDbs = [NSMutableArray array];
 			[allDbs addObjectsFromArray:[dbs allKeys]];
 
@@ -366,7 +368,10 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 				NSInteger uniqueSchemaKind = [mySQLConnection getUniqueDbIdentifierFor:[aTableName lowercaseString]];
 
 				// If no db name but table name check if table name is a valid name in the current selected db
-			 	if(aTableName && [aTableName length] && [dbs objectForKey:currentDb] && [[dbs objectForKey:currentDb] objectForKey:[NSString stringWithFormat:@"%@%@%@", currentDb, SPUniqueSchemaDelimiter, aTableName]] && uniqueSchemaKind == 2) {
+			 	if(aTableName && [aTableName length] 
+						&& [dbs objectForKey:currentDb] && [[dbs objectForKey:currentDb] isKindOfClass:[NSDictionary class]]
+						&& [[dbs objectForKey:currentDb] objectForKey:[NSString stringWithFormat:@"%@%@%@", currentDb, SPUniqueSchemaDelimiter, aTableName]] 
+						&& uniqueSchemaKind == 2) {
 					aTableNameExists = YES;
 					aDbName_id = [NSString stringWithString:currentDb];
 				}
@@ -378,7 +383,9 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 				}
 
 			} else if (aDbName && [aDbName length]) {
-				if(aTableName && [aTableName length] && [dbs objectForKey:aDbName_id] && [[dbs objectForKey:aDbName_id] objectForKey:[NSString stringWithFormat:@"%@%@%@", aDbName_id, SPUniqueSchemaDelimiter, aTableName]]) {
+				if(aTableName && [aTableName length] 
+						&& [dbs objectForKey:aDbName_id]  && [[dbs objectForKey:aDbName_id] isKindOfClass:[NSDictionary class]]
+						&& [[dbs objectForKey:aDbName_id] objectForKey:[NSString stringWithFormat:@"%@%@%@", aDbName_id, SPUniqueSchemaDelimiter, aTableName]]) {
 					aTableNameExists = YES;
 				}
 			}
@@ -399,6 +406,8 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 					continue;
 				}
 
+				NSString *dbpath = [db substringFromIndex:[db rangeOfString:SPUniqueSchemaDelimiter].location];
+
 				NSMutableArray *sortedTables = [NSMutableArray array];
 				if(aTableNameExists) {
 					[sortedTables addObject:aTableName_id];
@@ -411,24 +420,25 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 					}
 				}
 				for(id table in sortedTables) {
-					NSDictionary * theTable = [[dbs objectForKey:db] objectForKey:table];
+					NSDictionary *theTable = [[dbs objectForKey:db] objectForKey:table];
+					NSString *tablepath = [table substringFromIndex:[table rangeOfString:SPUniqueSchemaDelimiter].location];
 					NSArray *allFields = [theTable allKeys];
 					NSInteger structtype = [[theTable objectForKey:@"  struct_type  "] intValue];
 					BOOL breakFlag = NO;
 					if(!aTableNameExists)
 						switch(structtype) {
 							case 0:
-							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"table-small-square", @"image", db, @"path", @"", @"isRef", nil]];
+							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"table-small-square", @"image", tablepath, @"path", @"", @"isRef", nil]];
 							break;
 							case 1:
-							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"table-view-small-square", @"image", db, @"path", @"", @"isRef", nil]];
+							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"table-view-small-square", @"image", tablepath, @"path", @"", @"isRef", nil]];
 							break;
 							case 2:
-							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"proc-small", @"image", db, @"path", @"", @"isRef", nil]];
+							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"proc-small", @"image", tablepath, @"path", @"", @"isRef", nil]];
 							breakFlag = YES;
 							break;
 							case 3:
-							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"func-small", @"image", db, @"path", @"", @"isRef", nil]];
+							[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[table componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"func-small", @"image", tablepath, @"path", @"", @"isRef", nil]];
 							breakFlag = YES;
 							break;
 						}
@@ -436,6 +446,7 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 						NSArray *sortedFields = [allFields sortedArrayUsingDescriptors:[NSArray arrayWithObject:desc]];
 						for(id field in sortedFields) {
 							if(![field hasPrefix:@"  "]) {
+								NSString *fieldpath = [field substringFromIndex:[field rangeOfString:SPUniqueSchemaDelimiter].location];
 								NSArray *def = [theTable objectForKey:field];
 								NSString *typ = [NSString stringWithFormat:@"%@ %@ %@", [def objectAtIndex:0], [def objectAtIndex:3], [def objectAtIndex:5]];
 								// Check if type definition contains a , if so replace the bracket content by … and add 
@@ -446,7 +457,7 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 									[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:
 										[[field componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", 
 										@"field-small-square", @"image", 
-										table, @"path", 
+										fieldpath, @"path", 
 										t, @"type", 
 										lst, @"list", 
 										@"", @"isRef", 
@@ -455,7 +466,7 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 									[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:
 										[[field componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", 
 										@"field-small-square", @"image", 
-										table, @"path",
+										fieldpath, @"path",
 										typ, @"type", 
 										@"", @"isRef", 
 										nil]];
@@ -467,9 +478,8 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 			}
 			if(desc) [desc release];
 		} else {
-			// Fallback for MySQL < 5 and if the data gathering is in progress
-			if(mySQLmajorVersion > 4)
-				[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:NSLocalizedString(@"fetching table data…", @"fetching table data for completion in progress message"), @"path", @"", @"noCompletion", nil]];
+
+			// [possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:NSLocalizedString(@"fetching table data…", @"fetching table data for completion in progress message"), @"path", @"", @"noCompletion", nil]];
 
 			// Add all database names to completions list
 			for (id obj in [[[[self window] delegate] valueForKeyPath:@"tablesListInstance"] valueForKey:@"allDatabaseNames"])
@@ -511,6 +521,9 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 
 - (void) doAutoCompletion
 {
+
+	if(completionIsOpen) return;
+
 	NSRange r = [self selectedRange];
 
 	if(![[self delegate] isKindOfClass:[CustomQuery class]] || r.length || snippetControlCounter > -1) return;
@@ -528,7 +541,13 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 - (void) doCompletionByUsingSpellChecker:(BOOL)isDictMode fuzzyMode:(BOOL)fuzzySearch autoCompleteMode:(BOOL)autoCompleteMode
 {
 
-	if(![self isEditable]) return;
+	if(![self isEditable] || (completionIsOpen && !completionWasReinvokedAutomatically)) return;
+
+	// Cancel autocompletion trigger
+	if([prefs boolForKey:SPCustomQueryAutoComplete])
+		[NSObject cancelPreviousPerformRequestsWithTarget:self 
+								selector:@selector(doAutoCompletion) 
+								object:nil];
 
 	[self breakUndoCoalescing];
 
@@ -709,6 +728,8 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 		filter = [NSString stringWithString:currentWord];
 	}
 
+	completionIsOpen = YES;
+
 	SPNarrowDownCompletion* completionPopUp = [[SPNarrowDownCompletion alloc] initWithItems:[self suggestionsForSQLCompletionWith:currentWord dictMode:isDictMode browseMode:dbBrowseMode withTableName:tableName withDbName:dbName] 
 					alreadyTyped:filter 
 					staticPrefix:prefix 
@@ -745,7 +766,6 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 	pos.y -= [[self font] pointSize]*1.25;
 	
 	[completionPopUp setCaretPos:pos];
-	completionIsOpen = YES;
 	[completionPopUp orderFront:self];
 	if(!autoCompleteMode)
 		[completionPopUp insertCommonPrefix];
diff --git a/Source/SPNarrowDownCompletion.m b/Source/SPNarrowDownCompletion.m
index 40cbd2d9..cf5abb49 100644
--- a/Source/SPNarrowDownCompletion.m
+++ b/Source/SPNarrowDownCompletion.m
@@ -432,7 +432,10 @@
 				NSMenu *m = [[NSMenu alloc] init];
 				for(id p in [[[[[filtered objectAtIndex:rowIndex] objectForKey:@"path"] componentsSeparatedByString:SPUniqueSchemaDelimiter] reverseObjectEnumerator] allObjects])
 					[m addItemWithTitle:p action:NULL keyEquivalent:@""];
-				[m removeItemAtIndex:[m numberOfItems]-1];
+				if([m numberOfItems]>2) {
+					[m removeItemAtIndex:[m numberOfItems]-1];
+					[m removeItemAtIndex:0];
+				}
 				[b setMenu:m];
 				[m release];
 				[b setPreferredEdge:NSMinXEdge];
@@ -492,7 +495,7 @@
 						c = [[self filterString] characterAtIndex:i];
 						if(c != '`') {
 							if(c == '.')
-								[fuzzyRegexp appendString:[NSString stringWithFormat:@".*?",SPUniqueSchemaDelimiter]];
+								[fuzzyRegexp appendString:[NSString stringWithFormat:@".*?%@",SPUniqueSchemaDelimiter]];
 							else if (c == '(' || c == ')' || c == '[' || c == ']' || c == '{' || c == '}')
 								[fuzzyRegexp appendString:[NSString stringWithFormat:@".*?\\%c",c]];
 							else
@@ -805,9 +808,10 @@
 		if([selectedItem objectForKey:@"isRef"] 
 				&& ([[NSApp currentEvent] modifierFlags] & (NSShiftKeyMask))
 				&& [[selectedItem objectForKey:@"path"] length]) {
-			NSString *path = [NSString stringWithFormat:@"%@.%@", 
-				[[[selectedItem objectForKey:@"path"] componentsSeparatedByString:SPUniqueSchemaDelimiter] componentsJoinedByPeriodAndBacktickQuotedAndIgnoreFirst],
-				[candidateMatch backtickQuotedString]];
+			// NSString *path = [NSString stringWithFormat:@"%@.%@", 
+			// 	[[[selectedItem objectForKey:@"path"] componentsSeparatedByString:SPUniqueSchemaDelimiter] componentsJoinedByPeriodAndBacktickQuotedAndIgnoreFirst],
+			// 	[candidateMatch backtickQuotedString]];
+			NSString *path = [[[selectedItem objectForKey:@"path"] componentsSeparatedByString:SPUniqueSchemaDelimiter] componentsJoinedByPeriodAndBacktickQuotedAndIgnoreFirst];
 
 			// Check if path's db name is the current selected db name
 			NSRange r = [path rangeOfString:[currentDb backtickQuotedString] options:NSCaseInsensitiveSearch range:NSMakeRange(0, [[currentDb backtickQuotedString] length])];
diff --git a/Source/SPNavigatorController.h b/Source/SPNavigatorController.h
index c0ff46fc..9181dbaf 100644
--- a/Source/SPNavigatorController.h
+++ b/Source/SPNavigatorController.h
@@ -45,6 +45,7 @@
 	NSMutableDictionary *schemaDataFiltered;
 	NSMutableDictionary *allSchemaKeys;
 	NSMutableArray *infoArray;
+	NSMutableSet *updatingConnections;
 	NSMutableDictionary *expandStatus1;
 	NSMutableDictionary *expandStatus2;
 
@@ -65,7 +66,12 @@
 
 - (void)updateEntriesForConnection:(NSString*)connectionID;
 - (NSString*)tableInfoLabelForIndex:(NSInteger)index ofType:(NSInteger)type;
+
 - (void)updateNavigator:(NSNotification *)aNotification;
+- (void)isUpdatingNavigator:(NSNotification *)aNotification;
+
+- (NSDictionary *)dbStructureForConnection:(NSString*)connectionID;
+
 - (void)restoreSelectedItems;
 - (void)setIgnoreUpdate:(BOOL)flag;
 - (void)selectPath:(NSString*)schemaPath;
diff --git a/Source/SPNavigatorController.m b/Source/SPNavigatorController.m
index 8c460a91..dcc22c63 100644
--- a/Source/SPNavigatorController.m
+++ b/Source/SPNavigatorController.m
@@ -70,6 +70,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 		expandStatus1       = [[NSMutableDictionary alloc] init];
 		expandStatus2       = [[NSMutableDictionary alloc] init];
 		infoArray           = [[NSMutableArray alloc] init];
+		updatingConnections = [[NSMutableSet alloc] initWithCapacity:1];
 		selectedKey1        = @"";
 		selectedKey2        = @"";
 		ignoreUpdate        = NO;
@@ -89,6 +90,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	if(allSchemaKeys) [allSchemaKeys release];
 	if(schemaData) [schemaData release];
 	if(infoArray)  [infoArray release];
+	if(updatingConnections)  [updatingConnections release];
 	if(expandStatus1) [expandStatus1 release];
 	if(expandStatus2) [expandStatus2 release];
 }
@@ -124,6 +126,9 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	[[NSNotificationCenter defaultCenter] addObserver:self selector:@selector(updateNavigator:)
 												 name:@"SPDBStructureWasUpdated" object:nil];
 
+	[[NSNotificationCenter defaultCenter] addObserver:self selector:@selector(isUpdatingNavigator:)
+												 name:@"SPDBStructureIsUpdating" object:nil];
+
 }
 
 - (NSString *)windowFrameAutosaveName
@@ -215,7 +220,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 		[outlineSchema2 collapseItem:[item objectForKey:[pathArray objectAtIndex:0]] collapseChildren:YES];
 		for(NSInteger i=0; i < [pathArray count]; i++) {
 			[aKey appendString:[pathArray objectAtIndex:i]];
-			if(![item objectForKey:aKey]) break;
+			if(!item || ![item isKindOfClass:[NSDictionary class]] || ![item objectForKey:aKey]) break;
 			item = [item objectForKey:aKey];
 			[outlineSchema2 expandItem:item];
 			[aKey appendString:SPUniqueSchemaDelimiter];
@@ -377,11 +382,14 @@ static SPNavigatorController *sharedNavigatorController = nil;
 		[self performSelectorOnMainThread:@selector(updateEntriesForConnection:) withObject:nil waitUntilDone:YES];
 }
 
+- (void)isUpdatingNavigator:(NSNotification *)aNotification
+{
+	// todo
+}
+
 - (void)updateEntriesForConnection:(NSString*)connectionID
 {
 
-	if(![[self window] isVisible]) return;
-	NSLog(@"UPDATE NAVIGATOR called");
 	if(ignoreUpdate) {
 		ignoreUpdate = NO;
 		return;
@@ -390,7 +398,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	[self saveSelectedItems];
 
 	[infoArray removeAllObjects];
-	
+
 	if ([[[NSDocumentController sharedDocumentController] documents] count]) {
 		for(id doc in [[NSDocumentController sharedDocumentController] documents]) {
 
@@ -406,7 +414,6 @@ static SPNavigatorController *sharedNavigatorController = nil;
 				[schemaData setObject:[NSMutableDictionary dictionary] forKey:connectionName];
 			}
 
-
 			NSArray *dbs = [doc allDatabaseNames];
 			NSArray *keys = [[schemaData objectForKey:connectionName] allKeys];
 			for(id db in keys) {
@@ -420,17 +427,9 @@ static SPNavigatorController *sharedNavigatorController = nil;
 					[[schemaData objectForKey:connectionName] setObject:[[[theConnection getDbStructure] objectForKey:connectionName] objectForKey:item] forKey:item];
 				[allSchemaKeys setObject:[theConnection getAllKeysOfDbStructure] forKey:connectionName];
 			} else {
-
-				if([theConnection serverMajorVersion] > 4) {
-					[schemaData setObject:[NSDictionary dictionary] forKey:[NSString stringWithFormat:@"%@&DEL&no data loaded yet", connectionName]];
-					[allSchemaKeys setObject:[NSArray array] forKey:connectionName];
-				} else {
-					[schemaData setObject:[NSDictionary dictionary] forKey:[NSString stringWithFormat:@"%@&DEL&no data for this server version", connectionName]];
-					[allSchemaKeys setObject:[NSArray array] forKey:connectionName];
-				}
-
+				[schemaData setObject:[NSDictionary dictionary] forKey:[NSString stringWithFormat:@"%@&DEL&no data loaded yet", connectionName]];
+				[allSchemaKeys setObject:[NSArray array] forKey:connectionName];
 			}
-
 		}
 
 		[outlineSchema1 reloadData];
@@ -442,10 +441,9 @@ static SPNavigatorController *sharedNavigatorController = nil;
 
 	[self syncButtonAction:self];
 
-	if(isFiltered)
+	if(isFiltered && [[self window] isVisible])
 		[self filterTree:self];
 	
-
 }
 
 - (BOOL)schemaPathExistsForConnection:(NSString*)connectionID andDatabase:(NSString*)dbname
@@ -467,6 +465,13 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	[outlineSchema2 reloadData];
 }
 
+- (NSDictionary *)dbStructureForConnection:(NSString*)connectionID
+{
+	if([schemaData objectForKey:connectionID])
+		return [NSDictionary dictionaryWithDictionary:[schemaData objectForKey:connectionID]];
+	return nil;
+}
+
 #pragma mark -
 #pragma mark IBActions
 
@@ -1070,42 +1075,26 @@ static SPNavigatorController *sharedNavigatorController = nil;
 			case 0:
 			return @"DTD Identifier";
 			case 1:
-			return NSLocalizedString(@"Default", @"default label");
-			case 2:
 			return @"SQL Data Access";
+			case 2:
+			return @"Is Deterministic";
 			case 3:
-			return NSLocalizedString(@"Encoding", @"encoding label");
+			return NSLocalizedString(@"Execution Privilege", @"execution privilege label");
 			case 4:
-			return NSLocalizedString(@"Collation", @"collation label");
-			case 5:
-			return @"Is Deterministic";
-			case 6:
-			return @"Security Type";
-			case 7:
 			return @"Definer";
-			case 8:
-			return NSLocalizedString(@"Comment", @"comment label");
 		}
 	if(type == 3) // FUNCTION
 		switch(index) {
 			case 0:
 			return NSLocalizedString(@"Return Type", @"return type label");
 			case 1:
-			return NSLocalizedString(@"Default", @"default label");
-			case 2:
 			return @"SQL Data Access";
-			case 3:
-			return NSLocalizedString(@"Encoding", @"encoding label");
-			case 4:
-			return NSLocalizedString(@"Collation", @"collation label");
-			case 5:
+			case 2:
 			return @"Is Deterministic";
-			case 6:
+			case 3:
 			return NSLocalizedString(@"Execution Privilege", @"execution privilege label");
-			case 7:
+			case 4:
 			return @"Definer";
-			case 8:
-			return NSLocalizedString(@"Comment", @"comment label");
 		}
 	return @"";
 }