• fixed several issue for completion lists

• improved gathering and caching of structure data coming from connection windows with the same connection
• made the structure querying more stable against threading issues
• moved getUniqueDbIdentifierFor from MCPConnection to SPNavigatorController to be up-to-date in all connection windows
• improved detection if db structure querying should be performed or not (not yet finished)

7 files changed, 162 insertions, 101 deletions

diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
index 18450551..bf5d4145 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
@@ -113,6 +113,11 @@
  */
 - (NSArray*)allViewNames;
 
+/**
+ *
+ */
+- (NSArray*)allSchemaKeys;
+
 @end
 
 @interface MCPConnection : NSObject 
@@ -276,9 +281,10 @@ void performThreadedKeepAlive(void *ptr);
 - (MCPResult *)listTablesFromDB:(NSString *)dbName like:(NSString *)tablesName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName like:(NSString *)fieldsName;
+
+// Structure querying
 - (void)queryDbStructureWithUserInfo:(NSDictionary*)userInfo;
 - (NSDictionary *)getDbStructure;
-- (NSInteger)getUniqueDbIdentifierFor:(NSString*)term;
 - (NSArray *)getAllKeysOfDbStructure;
 
 // Server information
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
index 3c6bb9e7..70249488 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
@@ -1862,7 +1862,11 @@ void performThreadedKeepAlive(void *ptr)
 
 	if (!isQueryingDbStructure) {
 
-// NSLog(@"queryDbStructureWithUserInfo called");
+// NSLog(@"queryDbStructureWithUserInfo called with %@ and db %@", [userInfo description], [[[self delegate] database] description]);
+
+		isQueryingDbStructure = YES;
+
+		[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureIsUpdating" object:delegate];
 
 		NSString *SPUniqueSchemaDelimiter = @"￸";
 
@@ -1872,10 +1876,15 @@ void performThreadedKeepAlive(void *ptr)
 		else
 			connectionID = @"_";
 
-		if(![structure valueForKey:connectionID])
-			[structure setObject:[NSMutableDictionary dictionary] forKey:connectionID];
+		// Re-init with already cached data from navigator controller
+		[structure removeAllObjects];
+		[structure setObject:[NSMutableDictionary dictionaryWithDictionary:[[self delegate] getDbStructure]] forKey:connectionID];
+		[allKeysofDbStructure removeAllObjects];
+		[allKeysofDbStructure addObjectsFromArray:[[self delegate] allSchemaKeys]];
+
+		BOOL removeAddFlag = NO;
 
-		// Add all known database coming from connection if they aren't parsed yet
+		// Add all known databases coming from connection if they aren't parsed yet
 		NSArray *dbs = [[NSString stringWithFormat:@"%@%@%@",
 			[[[self delegate] allSystemDatabaseNames] componentsJoinedByString:SPUniqueSchemaDelimiter], 
 			SPUniqueSchemaDelimiter, 
@@ -1883,6 +1892,8 @@ void performThreadedKeepAlive(void *ptr)
 			componentsSeparatedByString:SPUniqueSchemaDelimiter];
 		for(id db in dbs) {
 			if(![[structure valueForKey:connectionID] objectForKey:[NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db]]) {
+// NSLog(@"added db %@", db);
+				removeAddFlag = YES;
 				[[structure valueForKey:connectionID] setObject:db forKey:[NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db]];
 			}
 		}
@@ -1890,12 +1901,14 @@ void performThreadedKeepAlive(void *ptr)
 		// Remove deleted databases in structure and keys in allKeysofDbStructure
 		// Use a dict to avoid <NSCFDictionary> was mutated while being enumerated. while iterating via allKeys
 		NSArray *keys = [[NSDictionary dictionaryWithDictionary:[structure valueForKey:connectionID]] allKeys];
-		BOOL removeFlag = NO;
 		for(id key in keys) {
 			NSString *db = [[key componentsSeparatedByString:SPUniqueSchemaDelimiter] objectAtIndex:1];
 			if(![dbs containsObject:db]) {
-				removeFlag = YES;
-				[[structure valueForKey:connectionID] removeObjectForKey:key];
+// NSLog(@"removed db %@", db);
+				removeAddFlag = YES;
+				if([[structure valueForKey:connectionID] isKindOfClass:[NSDictionary class]]) {
+					[[structure valueForKey:connectionID] removeObjectForKey:key];
+				}
 				NSPredicate *predicate = [NSPredicate predicateWithFormat:@"NOT SELF BEGINSWITH %@", [NSString stringWithFormat:@"%@%@", key, SPUniqueSchemaDelimiter]];
 				[allKeysofDbStructure filterUsingPredicate:predicate];
 				[allKeysofDbStructure removeObject:key];
@@ -1907,33 +1920,41 @@ void performThreadedKeepAlive(void *ptr)
 			currentDatabase = [[self delegate] database];
 
 		if(!currentDatabase || (currentDatabase && ![currentDatabase length])) {
+// NSLog(@"no db - return");
 			isQueryingDbStructure = NO;
-			if(removeFlag)
+			if(removeAddFlag)
 				[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
 			[queryPool release];
 			return;
 		}
 
-		// if([currentDatabase isEqualToString:@"mysql"]) {
-		// 	if(![[structure valueForKey:connectionID] objectForKey:currentDatabase] || ![[[structure valueForKey:connectionID] objectForKey:currentDatabase] isKindOfClass:[NSDictionary class]]) {
-		// 		if(removeFlag)
-		// 			[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
-		// 		[queryPool release];
-		// 		return;
-		// 	}
-		// }
-		// if([currentDatabase isEqualToString:@"information_schema"]) {
-		// 	if(![[structure valueForKey:connectionID] objectForKey:currentDatabase] || ![[[structure valueForKey:connectionID] objectForKey:currentDatabase] isKindOfClass:[NSDictionary class]]) {
-		// 		if(removeFlag)
-		// 			[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
-		// 		[queryPool release];
-		// 		return;
-		// 	}
-		// }
+		NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, currentDatabase];
+
+		// mysql and information_schema's schema will never change thus query data only once
+		if([currentDatabase isEqualToString:@"mysql"]) {
+			if([[structure valueForKey:connectionID] objectForKey:db_id] && [[[structure valueForKey:connectionID] objectForKey:db_id] isKindOfClass:[NSDictionary class]]) {
+// NSLog(@"mysql was parsed - return");
+				if(removeAddFlag)
+					[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
+				[queryPool release];
+				return;
+			}
+		}
+		if([currentDatabase isEqualToString:@"information_schema"]) {
+			if([[structure valueForKey:connectionID] objectForKey:db_id] && [[[structure valueForKey:connectionID] objectForKey:db_id] isKindOfClass:[NSDictionary class]]) {
+// NSLog(@"information_schema was parsed - return");
+				if(removeAddFlag)
+					[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
+				[queryPool release];
+				return;
+			}
+		}
 
 		if(userInfo == nil || ![userInfo objectForKey:@"forceUpdate"]) {
-			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
-				if(removeFlag)
+			if([[structure valueForKey:connectionID] objectForKey:db_id] && [[[structure valueForKey:connectionID] objectForKey:db_id] isKindOfClass:[NSDictionary class]]) {
+// NSLog(@"no forceUpdate - return");
+				isQueryingDbStructure = NO;
+				if(removeAddFlag)
 					[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
 				[queryPool release];
 				return;
@@ -1943,8 +1964,6 @@ void performThreadedKeepAlive(void *ptr)
 		NSArray *tables = [[[self delegate] valueForKeyPath:@"tablesListInstance"] allTableNames];
 		NSArray *tableviews = [[[self delegate] valueForKeyPath:@"tablesListInstance"] allViewNames];
 
-		NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, currentDatabase];
-
 		NSUInteger numberOfTables = 0;
 		if(tables && [tables count]) numberOfTables += [tables count];
 		if(tableviews && [tableviews count]) numberOfTables += [tableviews count];
@@ -1961,11 +1980,13 @@ void performThreadedKeepAlive(void *ptr)
 		}
 
 		// Delete all stored data for to be queried db
-		[[structure valueForKey:connectionID] removeObjectForKey:db_id];
+		if([[structure valueForKey:connectionID] isKindOfClass:[NSDictionary class]])
+			[[structure valueForKey:connectionID] removeObjectForKey:db_id];
 		NSPredicate *predicate = [NSPredicate predicateWithFormat:@"NOT SELF BEGINSWITH %@", [NSString stringWithFormat:@"%@%@", db_id, SPUniqueSchemaDelimiter]];
 		[allKeysofDbStructure filterUsingPredicate:predicate];
 		[allKeysofDbStructure removeObject:db_id];
-
+		// Re-add currentDatabase in case that structure querying will fail
+		[[structure valueForKey:connectionID] setObject:currentDatabase forKey:db_id];
 
 		NSString *currentDatabaseEscaped = [currentDatabase stringByReplacingOccurrencesOfString:@"`" withString:@"``"];
 
@@ -1977,8 +1998,6 @@ void performThreadedKeepAlive(void *ptr)
 			const char *theSocket;
 			void *connectionSetupStatus;
 
-			isQueryingDbStructure = YES;
-
 			mysql_options(structConnection, MYSQL_OPT_CONNECT_TIMEOUT, (const void *)&connectionTimeout);
 
 			// Set up the host, socket and password as per the connect method
@@ -2165,62 +2184,28 @@ void performThreadedKeepAlive(void *ptr)
 				// Notify that the structure querying has been performed
 				[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
 
-				// NSLog(@"QUERIED INFO");
 				mysql_close(structConnection);
-				isQueryingDbStructure = NO;
 			}
 		}
+	} else {
+		// Notify that the structure querying has been performed
+		[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
 	}
 
+	isQueryingDbStructure = NO;
 	[queryPool release];
 }
 
 /**
- * Returns 1 for db and 2 for table name if table name is not a db name and versa visa.
- * Otherwise it return 0. Mainly used for completion to know whether a `foo`. can only be 
- * a db name or a table name.
- */
-- (NSInteger)getUniqueDbIdentifierFor:(NSString*)term
-{
-
-	NSString *SPUniqueSchemaDelimiter = @"￸";
-
-	NSPredicate *predicate = [NSPredicate predicateWithFormat:@"SELF ENDSWITH[c] %@", [NSString stringWithFormat:@"%@%@", SPUniqueSchemaDelimiter, [term lowercaseString]]];
-	NSArray *result = [[allKeysofDbStructure allObjects] filteredArrayUsingPredicate:predicate];
-
-	if([result count] < 1 ) return 0;
-	if([result count] == 1) {
-		NSArray *split = [[result objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter];
-		if([split count] == 2 ) return 1;
-		if([split count] == 3 ) return 2;
-		return 0;
-	}
-	// case if field is equal to a table or db name
-	NSMutableArray *arr = [NSMutableArray array];
-	for(NSString *item in result) {
-		if([[item componentsSeparatedByString:SPUniqueSchemaDelimiter] count] < 4)
-			[arr addObject:item];
-	}
-	if([arr count] < 1 ) return 0;
-	if([arr count] == 1) {
-		NSArray *split = [[arr objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter];
-		if([split count] == 2 ) return 1;
-		if([split count] == 3 ) return 2;
-		return 0;
-	}
-	return 0;
-}
-
-/**
- * Returns a dict containing the structure of all available databases (mainly for completion).
+ * Returns a dict containing the structure of all available databases
  */
 - (NSDictionary *)getDbStructure
 {
-	return [NSDictionary dictionaryWithDictionary:structure];;
+	return [NSDictionary dictionaryWithDictionary:structure];
 }
 
 /**
- * Returns all keys of the db structure.
+ * Returns all keys of the db structure
  */
 - (NSArray *)getAllKeysOfDbStructure
 {
diff --git a/Source/CMTextView.m b/Source/CMTextView.m
index abc37865..3884f993 100644
--- a/Source/CMTextView.m
+++ b/Source/CMTextView.m
@@ -363,9 +363,9 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 
 			BOOL aTableNameExists = NO;
 			if(!aDbName) {
-				// Try to suggest only items which are uniquely valid for the parsed string
 
-				NSInteger uniqueSchemaKind = [mySQLConnection getUniqueDbIdentifierFor:[aTableName lowercaseString]];
+				// Try to suggest only items which are uniquely valid for the parsed string
+				NSInteger uniqueSchemaKind = [[SPNavigatorController sharedNavigatorController] getUniqueDbIdentifierFor:[aTableName lowercaseString] andConnection:[[[self delegate] valueForKeyPath:@"tableDocumentInstance"] connectionID]];
 
 				// If no db name but table name check if table name is a valid name in the current selected db
 			 	if(aTableName && [aTableName length] 
diff --git a/Source/SPNavigatorController.h b/Source/SPNavigatorController.h
index 9181dbaf..4affe9ef 100644
--- a/Source/SPNavigatorController.h
+++ b/Source/SPNavigatorController.h
@@ -71,6 +71,10 @@
 - (void)isUpdatingNavigator:(NSNotification *)aNotification;
 
 - (NSDictionary *)dbStructureForConnection:(NSString*)connectionID;
+- (NSArray *)allSchemaKeysForConnection:(NSString*)connectionID;
+- (NSInteger)getUniqueDbIdentifierFor:(NSString*)term andConnection:(NSString*)connectionID;
+
+- (BOOL)isUpdatingConnection:(NSString*)connectionID;
 
 - (void)restoreSelectedItems;
 - (void)setIgnoreUpdate:(BOOL)flag;
diff --git a/Source/SPNavigatorController.m b/Source/SPNavigatorController.m
index dcc22c63..31a6b44d 100644
--- a/Source/SPNavigatorController.m
+++ b/Source/SPNavigatorController.m
@@ -384,7 +384,10 @@ static SPNavigatorController *sharedNavigatorController = nil;
 
 - (void)isUpdatingNavigator:(NSNotification *)aNotification
 {
-	// todo
+	id object = [aNotification object];
+	
+	if([object isKindOfClass:[TableDocument class]])
+		[updatingConnections addObject:[object connectionID]];
 }
 
 - (void)updateEntriesForConnection:(NSString*)connectionID
@@ -400,37 +403,43 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	[infoArray removeAllObjects];
 
 	if ([[[NSDocumentController sharedDocumentController] documents] count]) {
-		for(id doc in [[NSDocumentController sharedDocumentController] documents]) {
 
-			id theConnection = [doc valueForKeyPath:@"mySQLConnection"];
+		id doc = [[NSDocumentController sharedDocumentController] currentDocument];
+		id theConnection = [doc valueForKeyPath:@"mySQLConnection"];
 
-			if(!theConnection || ![theConnection isConnected]) continue;
+		if(!theConnection || ![theConnection isConnected]) return;
 
-			NSString *connectionName = [doc connectionID];
+		NSString *connectionName = [doc connectionID];
 
-			if(!connectionName || [connectionName isEqualToString:@"_"] || (connectionID && ![connectionName isEqualToString:connectionID]) ) continue;
+		if(!connectionName || [connectionName isEqualToString:@"_"] || (connectionID && ![connectionName isEqualToString:connectionID]) ) {
+// NSLog(@"navigator update skipped %@", connectionName);
+			return;
+		}
 
-			if(![schemaData objectForKey:connectionName]) {
-				[schemaData setObject:[NSMutableDictionary dictionary] forKey:connectionName];
-			}
+		if(![schemaData objectForKey:connectionName]) {
+			[schemaData setObject:[NSMutableDictionary dictionary] forKey:connectionName];
+		}
 
-			NSArray *dbs = [doc allDatabaseNames];
-			NSArray *keys = [[schemaData objectForKey:connectionName] allKeys];
-			for(id db in keys) {
-				if(![dbs containsObject:[[db componentsSeparatedByString:SPUniqueSchemaDelimiter] objectAtIndex:1]]) {
-					[[schemaData objectForKey:connectionName] removeObjectForKey:db];
-				}
+		// Remove deleted dbs
+		NSArray *dbs = [doc allDatabaseNames];
+		NSArray *keys = [[schemaData objectForKey:connectionName] allKeys];
+		for(id db in keys) {
+			if(![dbs containsObject:[[db componentsSeparatedByString:SPUniqueSchemaDelimiter] objectAtIndex:1]]) {
+				[[schemaData objectForKey:connectionName] removeObjectForKey:db];
 			}
+		}
+		id structureData = [theConnection getDbStructure];
+		if(structureData && [structureData objectForKey:connectionName] && [[structureData objectForKey:connectionName] isKindOfClass:[NSDictionary class]]) {
+			for(id item in [[structureData objectForKey:connectionName] allKeys])
+				[[schemaData objectForKey:connectionName] setObject:[[structureData objectForKey:connectionName] objectForKey:item] forKey:item];
 
-			if([theConnection getDbStructure] && [[theConnection getDbStructure] objectForKey:connectionName]) {
-				for(id item in [[[theConnection getDbStructure] objectForKey:connectionName] allKeys])
-					[[schemaData objectForKey:connectionName] setObject:[[[theConnection getDbStructure] objectForKey:connectionName] objectForKey:item] forKey:item];
+			if([theConnection getAllKeysOfDbStructure])
 				[allSchemaKeys setObject:[theConnection getAllKeysOfDbStructure] forKey:connectionName];
-			} else {
-				[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 loaded yet", connectionName]];
+			[allSchemaKeys setObject:[NSArray array] forKey:connectionName];
 		}
+		[updatingConnections removeObject:connectionName];
 
 		[outlineSchema1 reloadData];
 		[outlineSchema2 reloadData];
@@ -450,9 +459,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 {
 	NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, dbname];
 
-	if([[[schemaData objectForKey:connectionID] allKeys] containsObject:db_id] 
-		&& [[[schemaData objectForKey:connectionID] objectForKey:db_id] isKindOfClass:[NSDictionary class]]
-		&& [[[schemaData objectForKey:connectionID] objectForKey:db_id] count])
+	if([schemaData objectForKey:connectionID] && [[[schemaData objectForKey:connectionID] allKeys] containsObject:db_id])
 		return YES;
 
 	return NO;
@@ -472,6 +479,53 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	return nil;
 }
 
+- (NSArray *)allSchemaKeysForConnection:(NSString*)connectionID
+{
+	return [NSArray arrayWithArray:[allSchemaKeys objectForKey:connectionID]];
+}
+
+/**
+ * Returns 1 for db and 2 for table name if table name is not a db name and versa visa.
+ * Otherwise it return 0. Mainly used for completion to know whether a `foo`. can only be 
+ * a db name or a table name.
+ */
+- (NSInteger)getUniqueDbIdentifierFor:(NSString*)term andConnection:(NSString*)connectionID
+{
+
+	NSString *SPUniqueSchemaDelimiter = @"￸";
+
+	NSPredicate *predicate = [NSPredicate predicateWithFormat:@"SELF ENDSWITH[c] %@", [NSString stringWithFormat:@"%@%@", SPUniqueSchemaDelimiter, [term lowercaseString]]];
+	NSArray *result = [[allSchemaKeys objectForKey:connectionID] filteredArrayUsingPredicate:predicate];
+
+	if([result count] < 1 ) return 0;
+	if([result count] == 1) {
+		NSArray *split = [[result objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter];
+		if([split count] == 2 ) return 1;
+		if([split count] == 3 ) return 2;
+		return 0;
+	}
+	// case if field is equal to a table or db name
+	NSMutableArray *arr = [NSMutableArray array];
+	for(NSString *item in result) {
+		if([[item componentsSeparatedByString:SPUniqueSchemaDelimiter] count] < 4)
+			[arr addObject:item];
+	}
+	if([arr count] < 1 ) return 0;
+	if([arr count] == 1) {
+		NSArray *split = [[arr objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter];
+		if([split count] == 2 ) return 1;
+		if([split count] == 3 ) return 2;
+		return 0;
+	}
+	return 0;
+}
+
+
+- (BOOL)isUpdatingConnection:(NSString*)connectionID
+{
+	return ([updatingConnections containsObject:connectionID]) ? YES : NO;
+}
+
 #pragma mark -
 #pragma mark IBActions
 
diff --git a/Source/TableDocument.h b/Source/TableDocument.h
index 4a943302..5f173f9d 100644
--- a/Source/TableDocument.h
+++ b/Source/TableDocument.h
@@ -190,6 +190,8 @@
 - (NSArray *)allDatabaseNames;
 - (NSArray *)allSystemDatabaseNames;
 - (BOOL)navigatorSchemaPathExistsForDatabase:(NSString*)dbname;
+- (NSDictionary *)getDbStructure;
+- (NSArray *)allSchemaKeys;
 
 // Task progress and notification methods
 - (void)startTaskWithDescription:(NSString *)description;
diff --git a/Source/TableDocument.m b/Source/TableDocument.m
index 2a95c158..936792b2 100644
--- a/Source/TableDocument.m
+++ b/Source/TableDocument.m
@@ -1092,6 +1092,16 @@
 	return [[SPNavigatorController sharedNavigatorController] schemaPathExistsForConnection:[self connectionID] andDatabase:dbname];
 }
 
+- (NSDictionary*)getDbStructure
+{
+	return [[SPNavigatorController sharedNavigatorController] dbStructureForConnection:[self connectionID]];
+}
+
+- (NSArray *)allSchemaKeys
+{
+	return [[SPNavigatorController sharedNavigatorController] allSchemaKeysForConnection:[self connectionID]];
+}
+
 #pragma mark -
 #pragma mark Console methods