• first preparations to improve the Custom Query database names/table names/field names + type and encoding completion

- after connecting and updating the table list a background task executed on a different connection will be performed to query the information_schema db (MySQL >= 5 only so far)
- the MCPConnection object holds a NSDictionary with all structural data

Note: not yet active

5 files changed, 159 insertions, 1 deletions

diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
index f9e0fa82..8398f4ca 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
@@ -105,6 +105,7 @@ static inline NSData* NSStringDataUsingLossyEncoding(NSString* self, NSInteger e
 	BOOL isMaxAllowedPacketEditable;
 	
 	NSString *serverVersionString;
+	NSDictionary *theDbStructure;
 	
 	NSTimer *keepAliveTimer;
 	pthread_t keepAliveThread;
@@ -115,6 +116,7 @@ static inline NSData* NSStringDataUsingLossyEncoding(NSString* self, NSInteger e
 	BOOL queryCancelUsedReconnect;
 	BOOL delegateQueryLogging;
 	BOOL delegateResponseToWillQueryString;
+	BOOL isQueryingDbStructure;
 	
 	// Pointers
 	IMP cStringPtr;
@@ -225,6 +227,8 @@ void performThreadedKeepAlive(void *ptr);
 - (MCPResult *)listTablesFromDB:(NSString *)dbName like:(NSString *)tablesName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName;
 - (MCPResult *)listFieldsFromTable:(NSString *)tableName like:(NSString *)fieldsName;
+- (void)queryDbStructure;
+- (NSDictionary *)getDbStructure;
 
 // Server information
 - (NSString *)clientInfo;
@@ -254,5 +258,6 @@ void performThreadedKeepAlive(void *ptr);
 - (const char *)cStringFromString:(NSString *)theString usingEncoding:(NSStringEncoding)encoding;
 - (NSString *)stringWithCString:(const char *)theCString;
 - (NSString *)stringWithText:(NSData *)theTextData;
+- (NSString *)stringWithUTF8CString:(const char *)theCString;
 
 @end
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
index 9f87c0d1..11f9cfec 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
@@ -109,6 +109,9 @@ static BOOL	sTruncateLongFieldInLogs = YES;
 		useKeepAlive      = YES; 
 		keepAliveInterval = 60;  
 		
+		theDbStructure = nil;
+		isQueryingDbStructure = NO;
+
 		connectionThreadId     = 0;
 		maxAllowedPacketSize   = -1;
 		lastQueryExecutionTime = 0;
@@ -378,6 +381,11 @@ static BOOL	sTruncateLongFieldInLogs = YES;
 		serverVersionString = nil;
 	}
 	
+	if (theDbStructure != nil) {
+		[theDbStructure release];
+		theDbStructure = nil;
+	}
+	
 	[self stopKeepAliveTimer];
 }
 
@@ -1797,6 +1805,123 @@ void performThreadedKeepAlive(void *ptr)
 	return theResult;
 }
 
+/**
+ * Updates the dict containing the structure of all available databases (mainly for completion)
+ * executed on a new connection.
+ */
+- (void)queryDbStructure
+{
+
+	return;
+
+	if (!isQueryingDbStructure && [self serverMajorVersion] >= 5) {
+
+		MYSQL *structConnection = mysql_init(NULL);
+		if (structConnection) {
+			const char *theLogin = [self cStringFromString:connectionLogin];
+			const char *theHost;
+			const char *thePass;
+			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
+			if (!connectionHost || ![connectionHost length]) {
+				theHost = NULL;
+			} else {
+				theHost = [self cStringFromString:connectionHost];
+			}
+			if (connectionSocket == nil || ![connectionSocket length]) {
+				theSocket = kMCPConnectionDefaultSocket;
+			} else {
+				theSocket = [self cStringFromString:connectionSocket];
+			}
+			if (!connectionPassword) {
+				if (delegate && [delegate respondsToSelector:@selector(keychainPasswordForConnection:)]) {
+					thePass = [self cStringFromString:[delegate keychainPasswordForConnection:self]];
+				}
+			} else {
+				thePass = [self cStringFromString:connectionPassword];
+			}
+			
+			// Connect
+			connectionSetupStatus = mysql_real_connect(structConnection, theHost, theLogin, thePass, NULL, connectionPort, theSocket, mConnectionFlags);
+			thePass = NULL;
+			if (connectionSetupStatus) {
+				MYSQL_RES *theResult;
+				MYSQL_ROW row;
+
+				NSStringEncoding theConnectionEncoding = [MCPConnection encodingForMySQLEncoding:mysql_character_set_name(structConnection)];
+
+				// Set connection to UTF-8 since the information_schema is encoded in UTF-8
+				NSString *setNameString = @"SET NAMES 'utf8'";
+				NSData *encodedSetNameData = NSStringDataUsingLossyEncoding(setNameString, theConnectionEncoding, 1);
+				const char *setNameCString = [encodedSetNameData bytes];
+				unsigned long setNameCStringLength = [encodedSetNameData length];
+				if (mysql_real_query(structConnection, setNameCString, setNameCStringLength) != 0) {
+					isQueryingDbStructure = NO;
+					return;
+				}
+
+				// 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` FROM `information_schema`.`COLUMNS`";
+				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];
+
+					while(row = mysql_fetch_row(theResult)) {
+						NSString *db = [self stringWithUTF8CString:row[0]];
+						NSString *table = [self stringWithUTF8CString:row[1]];
+						NSString *field = [self stringWithUTF8CString:row[2]];
+						NSString *type = [self stringWithUTF8CString:row[3]];
+						NSString *charset = (row[4]) ? [self stringWithUTF8CString:row[4]] : @"";
+
+						if(![structure valueForKey:db])
+							[structure setObject:[NSMutableDictionary dictionary] forKey:db];
+
+						if(![[structure valueForKey:db] valueForKey:table])
+							[[structure valueForKey:db] setObject:[NSMutableDictionary dictionary] forKey:table];
+
+						[[[structure valueForKey:db] valueForKey:table] setObject:[NSArray arrayWithObjects:type,charset,nil] forKey:field];
+
+					}
+
+					mysql_free_result(theResult);
+					mysql_close(structConnection);
+					if(theDbStructure != nil) {
+						[theDbStructure release];
+						theDbStructure = nil;
+					}
+
+					theDbStructure = [[NSDictionary dictionaryWithDictionary:structure] retain];
+					isQueryingDbStructure = NO;
+					return;
+
+				}
+				mysql_close(structConnection);
+				isQueryingDbStructure = NO;
+			}
+		}
+	}
+
+}
+
+
+/**
+ * Returns a dict containing the structure of all available databases (mainly for completion).
+ */
+- (NSDictionary *)getDbStructure
+{
+	return [theDbStructure retain];
+}
+
 #pragma mark -
 #pragma mark Server information
 
@@ -2154,6 +2279,26 @@ void performThreadedKeepAlive(void *ptr)
 }
 
 /**
+ * Returns a NSString from a C style string encoded with the character set of theMCPConnection.
+ */
+- (NSString *)stringWithUTF8CString:(const char *)theCString
+{
+	NSData	 *theData;
+	NSString *theString;
+	
+	if (theCString == NULL) return @"";
+	
+	theData = [NSData dataWithBytes:theCString length:(strlen(theCString))];
+	theString = [[NSString alloc] initWithData:theData encoding:NSUTF8StringEncoding];
+	
+	if (theString) {
+		[theString autorelease];
+	}
+	
+	return theString;
+}
+
+/**
  * Use the string encoding to convert the returned NSData to a string (for a Text field).
  */
 - (NSString *)stringWithText:(NSData *)theTextData
diff --git a/Source/CMTextView.m b/Source/CMTextView.m
index 8346525b..bebea5c0 100644
--- a/Source/CMTextView.m
+++ b/Source/CMTextView.m
@@ -142,6 +142,9 @@ NSInteger alphabeticSort(id string1, id string2, void *reverse)
 
 	if([mySQLConnection isConnected])
 	{
+
+		// NSLog(@"struct:\n %@", [mySQLConnection getDbStructure]);
+
 		// Add table names to completions list
 		for (id obj in [[[[self window] delegate] valueForKeyPath:@"tablesListInstance"] valueForKey:@"allTableNames"])
 			[possibleCompletions addObject:[NSDictionary dictionaryWithObjectsAndKeys:obj, @"display", @"table-small-square", @"image", nil]];
diff --git a/Source/TableDocument.m b/Source/TableDocument.m
index 85cb99c5..30645361 100644
--- a/Source/TableDocument.m
+++ b/Source/TableDocument.m
@@ -680,6 +680,8 @@
 														window:tableWindow
 											  notificationName:@"Connected"];
 
+	// Query the structure of all databases in the background (mainly for completion)
+	[mySQLConnection performSelector:@selector(queryDbStructure) withObject:nil afterDelay:0.1];
 
 	// Init Custom Query editor with the stored queries in a spf file if given.
 	[spfDocData setObject:[NSNumber numberWithBool:NO] forKey:@"save_editor_content"];
diff --git a/Source/TablesList.m b/Source/TablesList.m
index 5a98971b..2846a85a 100644
--- a/Source/TablesList.m
+++ b/Source/TablesList.m
@@ -86,6 +86,9 @@
 		// Notify listeners that a query has started
 		[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:tableDocumentInstance];
 
+		// Query the structure of all databases in the background (mainly for completion)
+		[mySQLConnection performSelector:@selector(queryDbStructure) withObject:nil afterDelay:0.1];
+
 		// 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.
 		theResult = [mySQLConnection queryString:@"SHOW /*!50002 FULL*/ TABLES"];
@@ -105,7 +108,7 @@
 				} else {
 					[tableTypes addObject:[NSNumber numberWithInteger:SP_TABLETYPE_TABLE]];
 				}
-			}		
+			}
 		}
 		
 		// Reorder the tables in alphabetical order