• first steps to ease the structure querying for auto-completion and navigator

- now it accumulates the data and caches them db by db, ie one has to select a db before using its structure for completion and navigator
- next step is to avoid querying info_schema as much as possible
- it will only query the structure if something was changed
- next steps follows as soon as possible

9 files changed, 201 insertions, 85 deletions

diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
index 89739e23..0b239b5f 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
@@ -83,6 +83,21 @@
  */
 - (NSString *)connectionID;
 
+/**
+ *
+ */
+- (NSString *)database;
+
+/**
+ *
+ */
+- (BOOL)navigatorSchemaPathExistsForDatabase:(NSString*)dbname;
+
+/**
+ *
+ */
+- (NSArray*)allDatabaseNames;
+
 @end
 
 @interface MCPConnection : NSObject 
@@ -123,7 +138,7 @@
 	BOOL isMaxAllowedPacketEditable;
 	
 	NSString *serverVersionString;
-	NSDictionary *theDbStructure;
+	NSMutableDictionary *structure;
 	NSDictionary *uniqueDbIdentifier;
 	NSArray *allKeysofDbStructure;
 	
@@ -247,7 +262,7 @@ void performThreadedKeepAlive(void *ptr);
 - (MCPResult *)listTablesFromDB:(NSString *)dbName like:(NSString *)tablesName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName like:(NSString *)fieldsName;
-- (void)queryDbStructure;
+- (void)queryDbStructureAndForceUpdate:(NSNumber*)forceUpdate;
 - (NSDictionary *)getDbStructure;
 - (NSInteger)getUniqueDbIdentifierFor:(NSString*)term;
 - (NSArray *)getAllKeysOfDbStructure;
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
index 878d6844..bc7917bd 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
@@ -115,7 +115,7 @@ static BOOL sTruncateLongFieldInLogs = YES;
 		useKeepAlive      = YES; 
 		keepAliveInterval = 60;  
 		
-		theDbStructure = nil;
+		structure = [[NSMutableDictionary alloc] initWithCapacity:1];
 		allKeysofDbStructure = nil;
 		uniqueDbIdentifier = nil;
 		isQueryingDbStructure = NO;
@@ -422,7 +422,7 @@ static BOOL sTruncateLongFieldInLogs = YES;
 	}
 	
 	if (serverVersionString) [serverVersionString release], serverVersionString = nil;
-	if (theDbStructure) [theDbStructure release], theDbStructure = nil;
+	if (structure) [structure release], structure = nil;
 	if (allKeysofDbStructure) [allKeysofDbStructure release], allKeysofDbStructure = nil;
 	if (uniqueDbIdentifier) [uniqueDbIdentifier release], uniqueDbIdentifier = nil;	
 	if (pingThread != NULL) pthread_cancel(pingThread), pingThread = NULL;
@@ -1836,12 +1836,73 @@ void performThreadedKeepAlive(void *ptr)
  * Updates the dict containing the structure of all available databases (mainly for completion/navigator)
  * executed on a new connection.
  */
-- (void)queryDbStructure
+- (void)queryDbStructureAndForceUpdate:(NSNumber*)forceUpdate
 {
 	NSAutoreleasePool *queryPool = [[NSAutoreleasePool alloc] init];
 
 	if (!isQueryingDbStructure && [self serverMajorVersion] >= 5) {
 
+		NSString *SPUniqueSchemaDelimiter = @"￸";
+
+		NSString *connectionID;
+		if([delegate respondsToSelector:@selector(connectionID)])
+			connectionID = [NSString stringWithString:[[self delegate] connectionID]];
+		else
+			connectionID = @"_";
+
+		if(![structure valueForKey:connectionID])
+			[structure setObject:[NSMutableDictionary dictionary] forKey:connectionID];
+
+		// Add all known database coming from connection
+		NSArray *dbs = [[self delegate] allDatabaseNames];
+		for(id db in dbs) {
+			NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db];
+			if(![[structure valueForKey:connectionID] valueForKey:db_id]) {
+				[[structure valueForKey:connectionID] setObject:db forKey:db_id];
+			}
+		}
+
+		// Remove deleted databases
+		NSArray *keys = [[structure valueForKey:connectionID] allKeys];
+		for(id key in keys) {
+			NSString *db = [[key componentsSeparatedByString:SPUniqueSchemaDelimiter] objectAtIndex:1];
+			if(![dbs containsObject:db]) {
+				[[structure valueForKey:connectionID] removeObjectForKey:key];
+			}
+		}
+
+		NSString *currentDatabase = nil;
+		if([delegate respondsToSelector:@selector(database)])
+			currentDatabase = [[self delegate] database];
+
+		if(!currentDatabase || (currentDatabase && ![currentDatabase length])) {
+			isQueryingDbStructure = NO;
+			[queryPool release];
+			return;
+		}
+
+		if([currentDatabase isEqualToString:@"mysql"]) {
+			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
+				[queryPool release];
+				return;
+			}
+		}
+		if([currentDatabase isEqualToString:@"information_schema"]) {
+			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
+				[queryPool release];
+				return;
+			}
+		}
+
+		if(forceUpdate == nil) {
+			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
+				[queryPool release];
+				return;
+			}
+		}
+
+		NSString *currentDatabaseEscaped = [currentDatabase stringByReplacingOccurrencesOfString:@"'" withString:@"\'"];
+
 		MYSQL *structConnection = mysql_init(NULL);
 		if (structConnection) {
 			const char *theLogin = [self cStringFromString:connectionLogin];
@@ -1894,7 +1955,7 @@ void performThreadedKeepAlive(void *ptr)
 				}
 				
 				NSUInteger numberOfItems = 20000;
-				query = @"SELECT COUNT(*) FROM `information_schema`.`COLUMNS`";
+				query = [NSString stringWithFormat:@"SELECT COUNT(1) FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = '%@'", currentDatabaseEscaped];
 				encodedSetNameData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
 				setNameCString = [encodedSetNameData bytes];
 				setNameCStringLength = [encodedSetNameData length];
@@ -1916,42 +1977,29 @@ void performThreadedKeepAlive(void *ptr)
 				}
 				
 				// Query the desired data
-				NSString *queryDbString = @""
-					@"SELECT TABLE_SCHEMA AS `databases`, TABLE_NAME AS `tables`, COLUMN_NAME AS `fields`, COLUMN_TYPE AS `type`, CHARACTER_SET_NAME AS `charset`, '0' AS `structtype`, `COLUMN_KEY` AS `KEY`, `EXTRA` AS EXTRA, `PRIVILEGES` AS `PRIVILEGES`, `COLLATION_NAME` AS `collation`, `COLUMN_DEFAULT` AS `default`, `IS_NULLABLE` AS `is_nullable`, `COLUMN_COMMENT` AS `comment` FROM `information_schema`.`COLUMNS` "
+				NSString *queryDbString = [NSString stringWithFormat:@""
+					@"SELECT TABLE_SCHEMA AS `databases`, TABLE_NAME AS `tables`, COLUMN_NAME AS `fields`, COLUMN_TYPE AS `type`, CHARACTER_SET_NAME AS `charset`, '0' AS `structtype`, `COLUMN_KEY` AS `KEY`, `EXTRA` AS EXTRA, `PRIVILEGES` AS `PRIVILEGES`, `COLLATION_NAME` AS `collation`, `COLUMN_DEFAULT` AS `default`, `IS_NULLABLE` AS `is_nullable`, `COLUMN_COMMENT` AS `comment` FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = '%@'"
 					@"UNION "
-					@"SELECT c.TABLE_SCHEMA AS `DATABASES`, c.TABLE_NAME AS `TABLES`, c.COLUMN_NAME AS `fields`, c.COLUMN_TYPE AS `TYPE`, c.CHARACTER_SET_NAME AS `CHARSET`, '1' AS `structtype`, `COLUMN_KEY` AS `KEY`, `EXTRA` AS EXTRA, `PRIVILEGES` AS `PRIVILEGES`, `COLLATION_NAME` AS `collation`, `COLUMN_DEFAULT` AS `default`, `IS_NULLABLE` AS `is_nullable`, `COLUMN_COMMENT` AS `comment` FROM `information_schema`.`COLUMNS` AS c, `information_schema`.`VIEWS` AS v WHERE c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME "
+					@"SELECT c.TABLE_SCHEMA AS `DATABASES`, c.TABLE_NAME AS `TABLES`, c.COLUMN_NAME AS `fields`, c.COLUMN_TYPE AS `TYPE`, c.CHARACTER_SET_NAME AS `CHARSET`, '1' AS `structtype`, `COLUMN_KEY` AS `KEY`, `EXTRA` AS EXTRA, `PRIVILEGES` AS `PRIVILEGES`, `COLLATION_NAME` AS `collation`, `COLUMN_DEFAULT` AS `default`, `IS_NULLABLE` AS `is_nullable`, `COLUMN_COMMENT` AS `comment` FROM `information_schema`.`COLUMNS` AS c, `information_schema`.`VIEWS` AS v WHERE c.TABLE_SCHEMA = '%@' AND c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME "
 					@"UNION "
-					@"SELECT ROUTINE_SCHEMA AS `DATABASES`, ROUTINE_NAME AS `TABLES`, ROUTINE_NAME AS `fields`, `DTD_identifier` AS `TYPE`, '' AS `CHARSET`, '2' AS `structtype`, `IS_DETERMINISTIC` AS `KEY`, `SECURITY_TYPE` AS EXTRA, `DEFINER` AS `PRIVILEGES`, '' AS `collation`, '' AS `DEFAULT`, `SQL_DATA_ACCESS` AS `is_nullable`, '' AS `COMMENT` FROM `information_schema`.`ROUTINES` WHERE ROUTINE_TYPE = 'PROCEDURE' "
+					@"SELECT ROUTINE_SCHEMA AS `DATABASES`, ROUTINE_NAME AS `TABLES`, ROUTINE_NAME AS `fields`, `DTD_identifier` AS `TYPE`, '' AS `CHARSET`, '2' AS `structtype`, `IS_DETERMINISTIC` AS `KEY`, `SECURITY_TYPE` AS EXTRA, `DEFINER` AS `PRIVILEGES`, '' AS `collation`, '' AS `DEFAULT`, `SQL_DATA_ACCESS` AS `is_nullable`, '' AS `COMMENT` FROM `information_schema`.`ROUTINES` WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = '%@'"
 					@"UNION "
-					@"SELECT ROUTINE_SCHEMA AS `DATABASES`, ROUTINE_NAME AS `TABLES`, ROUTINE_NAME AS `fields`, `DTD_identifier` AS `TYPE`, '' AS `CHARSET`, '3' AS `structtype`, `IS_DETERMINISTIC` AS `KEY`, `SECURITY_TYPE` AS EXTRA, `DEFINER` AS `PRIVILEGES`, '' AS `collation`, '' AS `DEFAULT`, `SQL_DATA_ACCESS` AS `is_nullable`, '' AS `COMMENT` FROM `information_schema`.`ROUTINES` WHERE ROUTINE_TYPE = 'FUNCTION' "
-					@"ORDER BY `DATABASES`,`TABLES`,`fields`";
-
+					@"SELECT ROUTINE_SCHEMA AS `DATABASES`, ROUTINE_NAME AS `TABLES`, ROUTINE_NAME AS `fields`, `DTD_identifier` AS `TYPE`, '' AS `CHARSET`, '3' AS `structtype`, `IS_DETERMINISTIC` AS `KEY`, `SECURITY_TYPE` AS EXTRA, `DEFINER` AS `PRIVILEGES`, '' AS `collation`, '' AS `DEFAULT`, `SQL_DATA_ACCESS` AS `is_nullable`, '' AS `COMMENT` FROM `information_schema`.`ROUTINES` WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA = '%@'", currentDatabaseEscaped, currentDatabaseEscaped, currentDatabaseEscaped, currentDatabaseEscaped];
+				
 				NSData *encodedQueryData = NSStringDataUsingLossyEncoding(queryDbString, theConnectionEncoding, 1);
 				const char *queryCString = [encodedQueryData bytes];
 				unsigned long queryCStringLength = [encodedQueryData length];
 
 				if (mysql_real_query(structConnection, queryCString, queryCStringLength) == 0) {
 					theResult = mysql_use_result(structConnection);
-					NSMutableDictionary *structure = [NSMutableDictionary dictionary];
 					NSMutableSet *allKeys = [[NSMutableSet alloc] initWithCapacity:20];
 					NSMutableSet *namesSet = [[NSMutableSet alloc] initWithCapacity:20];
 					NSMutableArray *allDbNames = [NSMutableArray array];
 					NSMutableArray *allTableNames = [NSMutableArray array];
 
-					// id to make any key unique
-					NSString *connectionID;
-					if([delegate respondsToSelector:@selector(connectionID)])
-						connectionID = [NSString stringWithString:[[self delegate] connectionID]];
-					else
-						connectionID = @"_";
-
-					NSString *SPUniqueSchemaDelimiter = @"￸";
 					NSUInteger cnt = 0; // used to make field data unique
 
-					[structure setObject:[NSMutableDictionary dictionary] forKey:connectionID];
-
 					while(row = mysql_fetch_row(theResult)) {
-						cnt++;
 						NSString *db = [self stringWithUTF8CString:row[0]];
 						NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db];
 						NSString *table = [self stringWithUTF8CString:row[1]];
@@ -1977,7 +2025,7 @@ void performThreadedKeepAlive(void *ptr)
 						[allKeys addObject:table_id];
 						[allKeys addObject:field_id];
 
-						if(![[structure valueForKey:connectionID] valueForKey:db_id]) {
+						if(!cnt || ![[structure valueForKey:connectionID] valueForKey:db_id]) {
 							[[structure valueForKey:connectionID] setObject:[NSMutableDictionary dictionary] forKey:db_id];
 						}
 
@@ -1987,19 +2035,13 @@ void performThreadedKeepAlive(void *ptr)
 
 						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:[NSArray arrayWithObjects:type, def, isnull, charset, coll, key, extra, priv, comment, [NSNumber numberWithUnsignedLongLong:cnt], nil] forKey:field_id];
 						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:structtype forKey:@"  struct_type  "];
-
+						cnt++;
 					}
 
 
 					mysql_free_result(theResult);
 					mysql_close(structConnection);
 
-					if(theDbStructure != nil) {
-						[theDbStructure release];
-						theDbStructure = nil;
-					}
-					theDbStructure = [[NSDictionary dictionaryWithDictionary:structure] retain];
-
 					if(allKeysofDbStructure != nil) {
 						[allKeysofDbStructure release];
 						allKeysofDbStructure = nil;
@@ -2060,7 +2102,7 @@ void performThreadedKeepAlive(void *ptr)
  */
 - (NSDictionary *)getDbStructure
 {
-	return theDbStructure;
+	return [NSDictionary dictionaryWithDictionary:structure];;
 }
 
 /**
@@ -2493,7 +2535,7 @@ void performThreadedKeepAlive(void *ptr)
 	if (connectionSocket) [connectionSocket release];
 	if (connectionPassword) [connectionPassword release];
 	if (serverVersionString) [serverVersionString release], serverVersionString = nil;
-	if (theDbStructure) [theDbStructure release], theDbStructure = nil;
+	if (structure) [structure release], structure = nil;
 	if (allKeysofDbStructure) [allKeysofDbStructure release], allKeysofDbStructure = nil;
 	if (uniqueDbIdentifier) [uniqueDbIdentifier release], uniqueDbIdentifier = nil;
 	
diff --git a/Source/CMTextView.m b/Source/CMTextView.m
index 72a43bcc..7a39a067 100644
--- a/Source/CMTextView.m
+++ b/Source/CMTextView.m
@@ -390,7 +390,15 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 			}
 
 			for(id db in sortedDbs) {
-				NSArray *allTables = [[dbs objectForKey:db] allKeys];
+
+				NSArray *allTables;
+				if([[dbs objectForKey:db] isKindOfClass:[NSDictionary class]])
+					allTables = [[dbs objectForKey:db] allKeys];
+				else {
+					[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:[[[[dbs objectForKey:db] description] componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject], @"display", @"database-small", @"image", @"", @"isRef", nil]];
+					continue;
+				}
+
 				NSMutableArray *sortedTables = [NSMutableArray array];
 				if(aTableNameExists) {
 					[sortedTables addObject:aTableName_id];
diff --git a/Source/SPNavigatorController.h b/Source/SPNavigatorController.h
index fb4c5bc2..c0ff46fc 100644
--- a/Source/SPNavigatorController.h
+++ b/Source/SPNavigatorController.h
@@ -73,4 +73,6 @@
 - (void)removeConnection:(NSString*)connectionID;
 - (void)selectInActiveDocumentItem:(id)item fromView:(id)outlineView;
 
+- (BOOL)schemaPathExistsForConnection:(NSString*)connectionID andDatabase:(NSString*)dbname;
+- (void)removeDatabase:(NSString*)db_id forConnectionID:(NSString*)connectionID;
 @end
diff --git a/Source/SPNavigatorController.m b/Source/SPNavigatorController.m
index 230d6991..d8772b17 100644
--- a/Source/SPNavigatorController.m
+++ b/Source/SPNavigatorController.m
@@ -149,6 +149,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	for( i = 0; i < [outlineSchema1 numberOfRows]; i++ ) {
 		id item = [outlineSchema1 itemAtRow:i];
 		id parentObject = [outlineSchema1 parentForItem:item] ? [outlineSchema1 parentForItem:item] : schemaData;
+		if(!parentObject) return;
 		id parentKeys = [parentObject allKeysForObject:item];
 		if(parentKeys && [parentKeys count] == 1)
 			if( [expandStatus1 objectForKey:[parentKeys objectAtIndex:0]] )
@@ -175,6 +176,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 		selection = [outlineSchema1 selectedItem];
 		if(selection) {
 			id parentObject = [outlineSchema1 parentForItem:selection] ? [outlineSchema1 parentForItem:selection] : schemaData;
+			if(!parentObject) return;
 			id parentKeys = [parentObject allKeysForObject:selection];
 			if(parentKeys && [parentKeys count] == 1)
 				selectedKey1 = [[parentKeys objectAtIndex:0] description];
@@ -188,6 +190,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 		selection = [outlineSchema2 selectedItem];
 		if(selection) {
 			id parentObject = [outlineSchema2 parentForItem:selection] ? [outlineSchema2 parentForItem:selection] : schemaData;
+			if(!parentObject) return;
 			id parentKeys = [parentObject allKeysForObject:selection];
 			if(parentKeys && [parentKeys count] == 1)
 				selectedKey2 = [[parentKeys objectAtIndex:0] description];
@@ -329,6 +332,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	if([outlineView levelForItem:item] == 0) return;
 	
 	id parentObject = [outlineView parentForItem:item] ? [outlineView parentForItem:item] : schemaData;
+	if(!parentObject) return;
 	id parentKeys = [parentObject allKeysForObject:item];
 	if(parentKeys && [parentKeys count] == 1) {
 
@@ -374,6 +378,8 @@ static SPNavigatorController *sharedNavigatorController = nil;
 - (void)updateEntriesForConnection:(NSString*)connectionID
 {
 
+	if(![[self window] isVisible]) return;
+	NSLog(@"UPDATE NAVIGATOR called");
 	if(ignoreUpdate) {
 		ignoreUpdate = NO;
 		return;
@@ -382,18 +388,6 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	[self saveSelectedItems];
 
 	[infoArray removeAllObjects];
-	if(connectionID) {
-		[schemaDataFiltered removeObjectForKey:connectionID];
-		[schemaData removeObjectForKey:connectionID];
-		[allSchemaKeys removeObjectForKey:connectionID];
-	} else {
-		[schemaDataFiltered removeAllObjects];
-		[schemaData removeAllObjects];
-		[allSchemaKeys removeAllObjects];
-	}
-
-	[outlineSchema1 reloadData];
-	[outlineSchema2 reloadData];
 	
 	if ([[[NSDocumentController sharedDocumentController] documents] count]) {
 		for(id doc in [[NSDocumentController sharedDocumentController] documents]) {
@@ -407,22 +401,34 @@ static SPNavigatorController *sharedNavigatorController = nil;
 			if(!connectionName || [connectionName isEqualToString:@"_"] || (connectionID && ![connectionName isEqualToString:connectionID]) ) continue;
 
 			if(![schemaData objectForKey:connectionName]) {
+				[schemaData setObject:[NSMutableDictionary dictionary] forKey:connectionName];
+			}
 
-				if([theConnection getDbStructure] && [[theConnection getDbStructure] objectForKey:connectionName]) {
-					[schemaData setObject:[[theConnection getDbStructure] objectForKey:connectionName] forKey:connectionName];
-					[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];
-					}
+			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];
+				}
+			}
+
+			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];
+				[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];
 				}
+
 			}
+
 		}
 
 		[outlineSchema1 reloadData];
@@ -440,6 +446,25 @@ static SPNavigatorController *sharedNavigatorController = nil;
 
 }
 
+- (BOOL)schemaPathExistsForConnection:(NSString*)connectionID andDatabase:(NSString*)dbname
+{
+	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])
+		return YES;
+
+	return NO;
+}
+
+- (void)removeDatabase:(NSString*)db_id forConnectionID:(NSString*)connectionID
+{
+	[[schemaData objectForKey:connectionID] removeObjectForKey:db_id];
+	[outlineSchema1 reloadData];
+	[outlineSchema2 reloadData];
+}
+
 #pragma mark -
 #pragma mark IBActions
 
@@ -467,7 +492,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	if ([[[NSDocumentController sharedDocumentController] documents] count]) {
 		for(id doc in [[NSDocumentController sharedDocumentController] documents]) {
 			if(![[doc valueForKeyPath:@"mySQLConnection"] isConnected]) continue;
-			[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:[doc valueForKeyPath:@"mySQLConnection"] withObject:nil];
+			[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:[doc valueForKeyPath:@"mySQLConnection"] withObject:[NSNumber numberWithBool:YES]];
 		}
 	}
 
@@ -739,20 +764,27 @@ static SPNavigatorController *sharedNavigatorController = nil;
 				} else {
 					[[tableColumn dataCell] setImage:[NSImage imageNamed:@"database-small"]];
 				}
-				if([[parentObject allKeysForObject:item] count])
+				if([[parentObject allKeysForObject:item] count] == 1) {
 					return [[[[parentObject allKeysForObject:item] objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject];
-				else
+				} else {
 					return @"…";
+				}
 
 			} else {
 				if([[parentObject allKeysForObject:item] count]) {
-					// It's a field and use the key "  struct_type  " to increase the distance between node and first child
-					if(![[[parentObject allKeysForObject:item] objectAtIndex:0] hasPrefix:@"  "]) {
-						[[tableColumn dataCell] setImage:[NSImage imageNamed:@"field-small-square"]];
+					if([outlineView levelForItem:item] == 1) {
+						// It's a db name which wasn't queried yet
+						[[tableColumn dataCell] setImage:[NSImage imageNamed:@"database-small"]];
 						return [[[[parentObject allKeysForObject:item] objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject];
 					} else {
-						[[tableColumn dataCell] setImage:[NSImage imageNamed:@"dummy-small"]];
-						return nil;
+						// It's a field and use the key "  struct_type  " to increase the distance between node and first child
+						if(![[[parentObject allKeysForObject:item] objectAtIndex:0] hasPrefix:@"  "]) {
+							[[tableColumn dataCell] setImage:[NSImage imageNamed:@"field-small-square"]];
+							return [[[[parentObject allKeysForObject:item] objectAtIndex:0] componentsSeparatedByString:SPUniqueSchemaDelimiter] lastObject];
+						} else {
+							[[tableColumn dataCell] setImage:[NSImage imageNamed:@"dummy-small"]];
+							return nil;
+						}
 					}
 				}
 				return @"…";
@@ -796,7 +828,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 
 - (BOOL)outlineView:outlineView isGroupItem:(id)item
 {
-	if ([item isKindOfClass:[NSDictionary class]])
+	if ([item isKindOfClass:[NSDictionary class]] || [outlineView levelForItem:item] == 1)
 		return YES;
 		
 	return NO;
@@ -915,6 +947,7 @@ static SPNavigatorController *sharedNavigatorController = nil;
 	NSMutableArray *draggedItems = [NSMutableArray array];
 	for(id item in items) {
 		id parentObject = [outlineView parentForItem:item] ? [outlineView parentForItem:item] : schemaData;
+		if(!parentObject) return NO;
 		id parentKeys = [parentObject allKeysForObject:item];
 		if(parentKeys && [parentKeys count] == 1)
 			[draggedItems addObject:[[[parentKeys objectAtIndex:0] description] stringByReplacingOccurrencesOfRegex:[NSString stringWithFormat:@"^.*?%@", SPUniqueSchemaDelimiter] withString:@""]];
diff --git a/Source/TableDocument.h b/Source/TableDocument.h
index 18b8be2a..8919fea6 100644
--- a/Source/TableDocument.h
+++ b/Source/TableDocument.h
@@ -189,6 +189,7 @@
 - (IBAction)openCurrentConnectionInNewWindow:(id)sender;
 - (NSArray *)allDatabaseNames;
 - (NSArray *)allSystemDatabaseNames;
+- (BOOL)navigatorSchemaPathExistsForDatabase:(NSString*)dbname;
 
 // Task progress and notification methods
 - (void)startTaskWithDescription:(NSString *)description;
diff --git a/Source/TableDocument.m b/Source/TableDocument.m
index 163f6d8b..1b39a9c4 100644
--- a/Source/TableDocument.m
+++ b/Source/TableDocument.m
@@ -688,8 +688,8 @@
 														window:tableWindow
 											  notificationName:@"Connected"];
 
-	// Query the structure of all databases in the background (mainly for completion)
-	[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+	// // Query the structure of all databases in the background (mainly for completion)
+	// [NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:nil];
 
 	// Init Custom Query editor with the stored queries in a spf file if given.
 	[spfDocData setObject:[NSNumber numberWithBool:NO] forKey:@"save_editor_content"];
@@ -840,6 +840,8 @@
 	}
 
 	(![self database]) ? [chooseDatabaseButton selectItemAtIndex:0] : [chooseDatabaseButton selectItemWithTitle:[self database]];
+	
+	
 }
 
 /**
@@ -865,6 +867,7 @@
 
 	// Select the database
 	[self selectDatabase:[chooseDatabaseButton titleOfSelectedItem] item:[self table]];
+
 }
 
 /**
@@ -874,7 +877,7 @@
 {
 
 	// Do not update the navigator since nothing is changed
-	[[SPNavigatorController sharedNavigatorController] setIgnoreUpdate:YES];
+	[[SPNavigatorController sharedNavigatorController] setIgnoreUpdate:NO];
 
 	// If Navigator runs in syncMode let it follow the selection
 	if([[SPNavigatorController sharedNavigatorController] syncMode]) {
@@ -1014,10 +1017,6 @@
 	if ([contextInfo isEqualToString:@"removeDatabase"]) {
 		if (returnCode == NSAlertDefaultReturn) {
 			[self _removeDatabase];
-
-			// Query the structure of all databases in the background (mainly for completion)
-			[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
-
 		}
 	}
 	// Add a new database
@@ -1026,7 +1025,7 @@
 			[self _addDatabase];
 
 			// Query the structure of all databases in the background (mainly for completion)
-			[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+			[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 
 		} else {
 			// reset chooseDatabaseButton
@@ -1091,6 +1090,11 @@
 	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self];
 }
 
+- (BOOL)navigatorSchemaPathExistsForDatabase:(NSString*)dbname
+{
+	return [[SPNavigatorController sharedNavigatorController] schemaPathExistsForConnection:[self connectionID] andDatabase:dbname];
+}
+
 #pragma mark -
 #pragma mark Console methods
 
@@ -4093,7 +4097,9 @@
 		
 		return;
 	}
-	
+
+	[[SPNavigatorController sharedNavigatorController] removeDatabase:[self database] forConnectionID:[self connectionID]];
+
 	// Delete was successful
 	if (selectedDatabase) [selectedDatabase release], selectedDatabase = nil;
 	
@@ -4103,6 +4109,7 @@
 	[tableDumpInstance setConnection:mySQLConnection];
 	
 	[tableWindow setTitle:[self displaySPName]];
+
 }
 
 /**
@@ -4177,11 +4184,14 @@
 			[tablesListInstance selectItemWithName:targetItemName];
 		} else {
 			[[tablesListInstance onMainThread] setTableListSelectability:YES];
-			[[[tablesListInstance valueForKey:@"tablesListView"] onMainThread] deselectAll:self];		
+			[[[tablesListInstance valueForKey:@"tablesListView"] onMainThread] deselectAll:self];
 			[[tablesListInstance onMainThread] setTableListSelectability:NO];
 		}
 	}
 
+	// Query the structure of all databases in the background (mainly for completion)
+	[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:nil];
+
 	[self endTask];
 	[taskPool drain];
 }
diff --git a/Source/TableSource.m b/Source/TableSource.m
index 0358f821..e50f7021 100644
--- a/Source/TableSource.m
+++ b/Source/TableSource.m
@@ -193,6 +193,7 @@
 
 	// Send the query finished/work complete notification
 	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:tableDocumentInstance];
+	
 }
 
 /**
@@ -202,6 +203,10 @@
 {
 	[tableDataInstance resetAllData];
 	[tablesListInstance setStatusRequiresReload:YES];
+
+	// Query the structure of all databases in the background (mainly for completion)
+	[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
+
 	[self loadTable:selectedTable];
 }
 
@@ -859,7 +864,7 @@ closes the keySheet
 		[tablesListInstance setContentRequiresReload:YES];
 
 		// Query the structure of all databases in the background (mainly for completion)
-		[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+		[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 
 		return YES;
 	} 
@@ -1066,7 +1071,7 @@ closes the keySheet
 			if ([NSThread isMainThread]) {
 				[NSThread detachNewThreadSelector:@selector(_removeIndexAndForeignKey:) toTarget:self withObject:removeKey];
 				
-				[tableDocumentInstance enableTaskCancellationWithTitle:NSLocalizedString(@"Cancel", @"cancel button") callbackObject:self callbackFunction:NULL];				
+				[tableDocumentInstance enableTaskCancellationWithTitle:NSLocalizedString(@"Cancel", @"cancel button") callbackObject:self callbackFunction:NULL];
 			} 
 			else {
 				[self _removeIndexAndForeignKey:removeKey];
diff --git a/Source/TablesList.m b/Source/TablesList.m
index 6ef3d315..72448495 100644
--- a/Source/TablesList.m
+++ b/Source/TablesList.m
@@ -92,7 +92,7 @@
 		[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:tableDocumentInstance];
 
 		// Query the structure of all databases in the background (mainly for completion)
-		[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+		[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:nil];
 
 		// Select the table list for the current database.  On MySQL versions after 5 this will include
 		// views; on MySQL versions >= 5.0.02 select the "full" list to also select the table type column.
@@ -1348,7 +1348,7 @@
     [tableWindow setTitle:[tableDocumentInstance displaySPName]];
     
     // Query the structure of all databases in the background (mainly for completion)
-    [NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+    [NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 }
 
 #pragma mark -
@@ -1928,7 +1928,7 @@
 	[tablesListView deselectAll:self];
 
 	// Query the structure of all databases in the background (mainly for completion)
-	[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+	[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 
 }
 
@@ -2028,7 +2028,7 @@
 		[self updateSelectionWithTaskString:[NSString stringWithFormat:NSLocalizedString(@"Loading %@...", @"Loading table task string"), selectedTableName]];
 
 		// Query the structure of all databases in the background (mainly for completion)
-		[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+		[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 
 	} 
 	else {
@@ -2222,7 +2222,7 @@
 			[self updateSelectionWithTaskString:[NSString stringWithFormat:NSLocalizedString(@"Loading %@...", @"Loading table task string"), selectedTableName]];
 
 			// Query the structure of all databases in the background (mainly for completion)
-			[NSThread detachNewThreadSelector:@selector(queryDbStructure) toTarget:mySQLConnection withObject:nil];
+			[NSThread detachNewThreadSelector:@selector(queryDbStructureAndForceUpdate:) toTarget:mySQLConnection withObject:[NSNumber numberWithBool:YES]];
 
 		}
 	}