- 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

6 files changed, 276 insertions, 161 deletions

diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
index c105691f..18450551 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
@@ -103,6 +103,16 @@
  */
 - (NSArray*)allSystemDatabaseNames;
 
+/**
+ *
+ */
+- (NSArray*)allTableNames;
+
+/**
+ *
+ */
+- (NSArray*)allViewNames;
+
 @end
 
 @interface MCPConnection : NSObject 
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
index 8fc42a3e..3c6bb9e7 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
@@ -1860,11 +1860,11 @@ void performThreadedKeepAlive(void *ptr)
 {
 	NSAutoreleasePool *queryPool = [[NSAutoreleasePool alloc] init];
 
-	if (!isQueryingDbStructure && [self serverMajorVersion] >= 5) {
+	if (!isQueryingDbStructure) {
 
-		NSString *SPUniqueSchemaDelimiter = @"￸";
+// NSLog(@"queryDbStructureWithUserInfo called");
 
-		
+		NSString *SPUniqueSchemaDelimiter = @"￸";
 
 		NSString *connectionID;
 		if([delegate respondsToSelector:@selector(connectionID)])
@@ -1875,20 +1875,21 @@ void performThreadedKeepAlive(void *ptr)
 		if(![structure valueForKey:connectionID])
 			[structure setObject:[NSMutableDictionary dictionary] forKey:connectionID];
 
-		// Add all known database coming from connection
+		// Add all known database coming from connection if they aren't parsed yet
 		NSArray *dbs = [[NSString stringWithFormat:@"%@%@%@",
 			[[[self delegate] allSystemDatabaseNames] componentsJoinedByString:SPUniqueSchemaDelimiter], 
 			SPUniqueSchemaDelimiter, 
 			[[[self delegate] allDatabaseNames] componentsJoinedByString:SPUniqueSchemaDelimiter]] 
 			componentsSeparatedByString:SPUniqueSchemaDelimiter];
 		for(id db in dbs) {
-			if(![[self delegate] navigatorSchemaPathExistsForDatabase:db]) {
+			if(![[structure valueForKey:connectionID] objectForKey:[NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db]]) {
 				[[structure valueForKey:connectionID] setObject:db forKey:[NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db]];
 			}
 		}
 
-		// Remove deleted databases and keys in allKeysofDbStructure
-		NSArray *keys = [[structure valueForKey:connectionID] allKeys];
+		// 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];
@@ -1913,22 +1914,22 @@ void performThreadedKeepAlive(void *ptr)
 			return;
 		}
 
-		if([currentDatabase isEqualToString:@"mysql"]) {
-			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
-				if(removeFlag)
-					[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
-				[queryPool release];
-				return;
-			}
-		}
-		if([currentDatabase isEqualToString:@"information_schema"]) {
-			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
-				if(removeFlag)
-					[[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;
+		// 	}
+		// }
 
 		if(userInfo == nil || ![userInfo objectForKey:@"forceUpdate"]) {
 			if([[self delegate] navigatorSchemaPathExistsForDatabase:currentDatabase]) {
@@ -1939,7 +1940,34 @@ void performThreadedKeepAlive(void *ptr)
 			}
 		}
 
-		NSString *currentDatabaseEscaped = [currentDatabase stringByReplacingOccurrencesOfString:@"'" withString:@"\'"];
+		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];
+
+		// For future usage
+		NSString *affectedItem = nil;
+		NSInteger affectedItemType = -1;
+		if(userInfo && [userInfo objectForKey:@"affectedItem"]) {
+			affectedItem = [userInfo objectForKey:@"affectedItem"];
+			if([userInfo objectForKey:@"affectedItemType"])
+				affectedItemType = [[userInfo objectForKey:@"affectedItemType"] intValue];
+			else
+				affectedItem = nil;
+		}
+
+		// Delete all stored data for to be queried db
+		[[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];
+
+
+		NSString *currentDatabaseEscaped = [currentDatabase stringByReplacingOccurrencesOfString:@"`" withString:@"``"];
 
 		MYSQL *structConnection = mysql_init(NULL);
 		if (structConnection) {
@@ -1980,106 +2008,164 @@ void performThreadedKeepAlive(void *ptr)
 				MYSQL_ROW row;
 
 				NSStringEncoding theConnectionEncoding = [MCPConnection encodingForMySQLEncoding:mysql_character_set_name(structConnection)];
+				NSString *charset;
 
-				// Set connection to UTF-8 since the information_schema is encoded in UTF-8
-				NSString *query = @"SET NAMES 'utf8'";
-				NSData *encodedSetNameData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
-				const char *setNameCString = [encodedSetNameData bytes];
-				unsigned long setNameCStringLength = [encodedSetNameData length];
-				if (mysql_real_query(structConnection, setNameCString, setNameCStringLength) != 0) {
-					isQueryingDbStructure = NO;
-					[queryPool release];
-					return;
-				}
-				
-				NSUInteger numberOfItems = 20000;
-				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];
-				if (mysql_real_query(structConnection, setNameCString, setNameCStringLength) != 0) {
+				if(numberOfTables > 1000) {
+					NSLog(@"%ld items in database %@. Stop parsing.", numberOfTables, currentDatabase);
 					isQueryingDbStructure = NO;
 					[queryPool release];
 					return;
 				}
-				theResult = mysql_use_result(structConnection);
-				row = mysql_fetch_row(theResult);
-				if(row)
-					numberOfItems = [[self stringWithUTF8CString:row[0]] longLongValue];
-				mysql_free_result(theResult);
 
-				if(numberOfItems > 10000) {
-					isQueryingDbStructure = NO;
-					[queryPool release];
-					return;
-				}
-				NSLog(@"QUERY INFO");
-				// Query the desired data
-				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 v.TABLE_SCHEMA = '%@' AND c.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' 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' AND ROUTINE_SCHEMA = '%@'", currentDatabaseEscaped, currentDatabaseEscaped, currentDatabaseEscaped, currentDatabaseEscaped, currentDatabaseEscaped];
-				
-				NSData *encodedQueryData = NSStringDataUsingLossyEncoding(queryDbString, theConnectionEncoding, 1);
+// NSLog(@"queryDbStructureWithUserInfo started");
+
+				NSUInteger uniqueCounter = 0; // used to make field data unique
+
+				NSString *query = @"SET NAMES 'utf8'";
+				NSData *encodedQueryData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
 				const char *queryCString = [encodedQueryData bytes];
 				unsigned long queryCStringLength = [encodedQueryData length];
+				if (mysql_real_query(structConnection, queryCString, queryCStringLength) != 0) {
+					;
+				}
 
-				if (mysql_real_query(structConnection, queryCString, queryCStringLength) == 0) {
+				// Query all tables
+				for(NSString* table in tables) {
+					NSString *query = [NSString stringWithFormat:@"SHOW FULL COLUMNS FROM `%@` FROM `%@`", 
+						[table stringByReplacingOccurrencesOfString:@"`" withString:@"``"],
+						currentDatabaseEscaped];
+					NSData *encodedQueryData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
+					const char *queryCString = [encodedQueryData bytes];
+					unsigned long queryCStringLength = [encodedQueryData length];
+					if (mysql_real_query(structConnection, queryCString, queryCStringLength) != 0) {
+						// NSLog(@"error %@", table);
+						continue;
+					}
 					theResult = mysql_use_result(structConnection);
-
-					NSUInteger cnt = 0; // used to make field data unique
-
+					NSString *table_id = [NSString stringWithFormat:@"%@%@%@", db_id, SPUniqueSchemaDelimiter, table];
 					while(row = mysql_fetch_row(theResult)) {
-						NSString *db = [self stringWithUTF8CString:row[0]];
-						NSString *db_id = [NSString stringWithFormat:@"%@%@%@", connectionID, SPUniqueSchemaDelimiter, db];
-						NSString *table = [self stringWithUTF8CString:row[1]];
-						NSString *table_id = [NSString stringWithFormat:@"%@%@%@", db_id, SPUniqueSchemaDelimiter, table];
-						NSString *field = [self stringWithUTF8CString:row[2]];
+						NSString *field = [self stringWithUTF8CString:row[0]];
+						NSString *type = [self stringWithUTF8CString:row[1]];
+						NSString *coll = [self stringWithUTF8CString:row[2]];
+						NSString *isnull = [self stringWithUTF8CString:row[3]];
+						NSString *key = [self stringWithUTF8CString:row[4]];
+						NSString *def = [self stringWithUTF8CString:row[5]];
+						NSString *extra = [self stringWithUTF8CString:row[6]];
+						NSString *priv = [self stringWithUTF8CString:row[7]];
+						NSString *comment = [self stringWithUTF8CString:row[8]];
 						NSString *field_id = [NSString stringWithFormat:@"%@%@%@", table_id, SPUniqueSchemaDelimiter, field];
-						NSString *type = [self stringWithUTF8CString:row[3]];
-						NSString *charset = (row[4]) ? [self stringWithUTF8CString:row[4]] : @"";
-						NSString *structtype = [self stringWithUTF8CString:row[5]];
-						NSString *key = [self stringWithUTF8CString:row[6]];
-						NSString *extra = [self stringWithUTF8CString:row[7]];
-						NSString *priv = [self stringWithUTF8CString:row[8]];
-						NSString *coll = [self stringWithUTF8CString:row[9]];
-						NSString *def = [self stringWithUTF8CString:row[10]];
-						NSString *isnull = [self stringWithUTF8CString:row[11]];
-						NSString *comment = [self stringWithUTF8CString:row[12]];
+						NSArray *a = [coll componentsSeparatedByString:@"_"];
+						charset = ([a count]) ? [a objectAtIndex:0] : @"";
 
 						[allKeysofDbStructure addObject:db_id];
 						[allKeysofDbStructure addObject:table_id];
 						[allKeysofDbStructure addObject:field_id];
-
-						if(!cnt || ![[structure valueForKey:connectionID] valueForKey:db_id]) {
+					
+						if(![[structure valueForKey:connectionID] valueForKey:db_id] || [[[structure valueForKey:connectionID] valueForKey:db_id] isKindOfClass:[NSString class]] )
 							[[structure valueForKey:connectionID] setObject:[NSMutableDictionary dictionary] forKey:db_id];
-						}
-
-						if(![[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id]) {
+					
+						if(![[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id])
 							[[[structure valueForKey:connectionID] valueForKey:db_id] setObject:[NSMutableDictionary dictionary] forKey:table_id];
-						}
-
-						[[[[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++;
+					
+						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:[NSArray arrayWithObjects:type, def, isnull, charset, coll, key, extra, priv, comment, [NSNumber numberWithUnsignedLongLong:uniqueCounter], nil] forKey:field_id];
+						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:@"0" forKey:@"  struct_type  "];
+						uniqueCounter++;
 					}
+					mysql_free_result(theResult);
+					usleep(10);
+				}
+				// Query all views
+				for(NSString* table in tableviews) {
+					NSString *query = [NSString stringWithFormat:@"SHOW FULL COLUMNS FROM `%@` FROM `%@`", 
+						[table stringByReplacingOccurrencesOfString:@"`" withString:@"``"],
+						currentDatabaseEscaped];
+					NSData *encodedQueryData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
+					const char *queryCString = [encodedQueryData bytes];
+					unsigned long queryCStringLength = [encodedQueryData length];
+					if (mysql_real_query(structConnection, queryCString, queryCStringLength) != 0) {
+						// NSLog(@"error %@", table);
+						continue;
+					}
+					theResult = mysql_use_result(structConnection);
+					NSString *table_id = [NSString stringWithFormat:@"%@%@%@", db_id, SPUniqueSchemaDelimiter, table];
+					NSString *charset;
+					while(row = mysql_fetch_row(theResult)) {
+						NSString *field = [self stringWithUTF8CString:row[0]];
+						NSString *type = [self stringWithUTF8CString:row[1]];
+						NSString *coll = [self stringWithUTF8CString:row[2]];
+						NSString *isnull = [self stringWithUTF8CString:row[3]];
+						NSString *key = [self stringWithUTF8CString:row[4]];
+						NSString *def = [self stringWithUTF8CString:row[5]];
+						NSString *extra = [self stringWithUTF8CString:row[6]];
+						NSString *priv = [self stringWithUTF8CString:row[7]];
+						NSString *comment = [self stringWithUTF8CString:row[8]];
+						NSString *field_id = [NSString stringWithFormat:@"%@%@%@", table_id, SPUniqueSchemaDelimiter, field];
+						NSArray *a = [coll componentsSeparatedByString:@"_"];
+						charset = ([a count]) ? [a objectAtIndex:0] : @"";
 
-
+						[allKeysofDbStructure addObject:db_id];
+						[allKeysofDbStructure addObject:table_id];
+						[allKeysofDbStructure addObject:field_id];
+					
+						if(![[structure valueForKey:connectionID] valueForKey:db_id] || [[[structure valueForKey:connectionID] valueForKey:db_id] isKindOfClass:[NSString class]] )
+							[[structure valueForKey:connectionID] setObject:[NSMutableDictionary dictionary] forKey:db_id];
+					
+						if(![[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id])
+							[[[structure valueForKey:connectionID] valueForKey:db_id] setObject:[NSMutableDictionary dictionary] forKey:table_id];
+					
+						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:[NSArray arrayWithObjects:type, def, isnull, charset, coll, key, extra, priv, comment, [NSNumber numberWithUnsignedLongLong:uniqueCounter], nil] forKey:field_id];
+						[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:@"1" forKey:@"  struct_type  "];
+						uniqueCounter++;
+					}
 					mysql_free_result(theResult);
-					mysql_close(structConnection);
+					usleep(10);
+				}
 
-					// Notify that the structure querying has been performed
-					[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
+				if([self serverMajorVersion] >= 5) {
+					// Query for procedures and functions
+					query = [NSString stringWithFormat:@"SELECT * FROM `information_schema`.`ROUTINES` WHERE `information_schema`.`ROUTINES`.`ROUTINE_SCHEMA` = '%@'", [currentDatabase stringByReplacingOccurrencesOfString:@"'" withString:@"\'"]];
+					encodedQueryData = NSStringDataUsingLossyEncoding(query, theConnectionEncoding, 1);
+					queryCString = [encodedQueryData bytes];
+					queryCStringLength = [encodedQueryData length];
+					if (mysql_real_query(structConnection, queryCString, queryCStringLength) == 0) {
+						theResult = mysql_use_result(structConnection);
+						NSUInteger numberOfFields = mysql_num_fields(theResult);
+						while(row = mysql_fetch_row(theResult)) {
+							NSString *field = [self stringWithUTF8CString:row[0]];
+							NSString *table_id = [NSString stringWithFormat:@"%@%@%@", db_id, SPUniqueSchemaDelimiter, field];
+							NSString *field_id = [NSString stringWithFormat:@"%@%@%@", table_id, SPUniqueSchemaDelimiter, field];
+							NSString *type = ([[self stringWithUTF8CString:row[4]] isEqualToString:@"FUNCTION"]) ? @"3" : @"2";
+							NSString *dtd = [self stringWithUTF8CString:row[5]];
+							NSString *det = [self stringWithUTF8CString:row[11]];
+							NSString *access = [self stringWithUTF8CString:row[12]];
+							NSString *security_type = [self stringWithUTF8CString:row[14]];
+							NSString *definer = [self stringWithUTF8CString:row[19]];
+
+							[allKeysofDbStructure addObject:db_id];
+							[allKeysofDbStructure addObject:table_id];
+							[allKeysofDbStructure addObject:field_id];
+				
+							if(![[structure valueForKey:connectionID] valueForKey:db_id] || [[[structure valueForKey:connectionID] valueForKey:db_id] isKindOfClass:[NSString class]] )
+								[[structure valueForKey:connectionID] setObject:[NSMutableDictionary dictionary] forKey:db_id];
+				
+							if(![[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id])
+								[[[structure valueForKey:connectionID] valueForKey:db_id] setObject:[NSMutableDictionary dictionary] forKey:table_id];
+				
+							[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:
+								[NSArray arrayWithObjects:dtd, access, det, security_type, definer, [NSNumber numberWithUnsignedLongLong:uniqueCounter], nil] forKey:field_id];
+							[[[[structure valueForKey:connectionID] valueForKey:db_id] valueForKey:table_id] setObject:type forKey:@"  struct_type  "];
+							uniqueCounter++;
+						}
+						mysql_free_result(theResult);
+					}
+				}
 
-					isQueryingDbStructure = NO;
-					[queryPool release];
+// NSLog(@"queryDbStructureWithUserInfo done");
 
-					return;
-				}
+				// Notify that the structure querying has been performed
+				[[NSNotificationCenter defaultCenter] postNotificationName:@"SPDBStructureWasUpdated" object:delegate];
+
+				// NSLog(@"QUERIED INFO");
 				mysql_close(structConnection);
 				isQueryingDbStructure = NO;
 			}
@@ -2099,7 +2185,7 @@ void performThreadedKeepAlive(void *ptr)
 
 	NSString *SPUniqueSchemaDelimiter = @"￸";
 
-	NSPredicate *predicate = [NSPredicate predicateWithFormat:@"SELF ENDSWITH %@", [NSString stringWithFormat:@"%@%@", SPUniqueSchemaDelimiter, term]];
+	NSPredicate *predicate = [NSPredicate predicateWithFormat:@"SELF ENDSWITH[c] %@", [NSString stringWithFormat:@"%@%@", SPUniqueSchemaDelimiter, [term lowercaseString]]];
 	NSArray *result = [[allKeysofDbStructure allObjects] filteredArrayUsingPredicate:predicate];
 
 	if([result count] < 1 ) return 0;
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 @"";
 }