Rework SQL export:

 - Added an MCPStreamingResult class to MCPKit, to allow streaming results from the server including fast array access of each row
 - Tweak SQL export to use the streaming result class and to keep memory usage lower
End result is generally faster exports, more accurate progress bars, and much much lower (and consistent) memory usage.

5 files changed, 304 insertions, 17 deletions

diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
index 9639a742..932237d8 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.h
@@ -32,7 +32,7 @@
 
 #import "mysql.h"
 
-@class MCPResult;
+@class MCPResult, MCPStreamingResult;
 @protocol MCPConnectionProxy;
 
 /**
@@ -185,11 +185,15 @@ static inline NSData* NSStringDataUsingLossyEncoding(NSString* self, int encodin
 - (NSString *)prepareString:(NSString *)theString;
 - (NSString *)quoteObject:(id)theObject;
 - (MCPResult *)queryString:(NSString *)query;
-- (MCPResult *)queryString:(NSString *)query usingEncoding:(NSStringEncoding)encoding;
+- (MCPStreamingResult *)streamingQueryString:(NSString *)query;
+- (id)queryString:(NSString *) query usingEncoding:(NSStringEncoding) encoding streamingResult:(BOOL) streamResult;
 - (double)lastQueryExecutionTime;
 - (my_ulonglong)affectedRows;
 - (my_ulonglong)insertId;
 
+// Locking
+- (void)unlockConnection;
+
 // Database structure
 - (MCPResult *)listDBs;
 - (MCPResult *)listDBsLike:(NSString *)dbsName;
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
index 8ed2884b..b311ac14 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPConnection.m
@@ -28,6 +28,7 @@
 
 #import "MCPConnection.h"
 #import "MCPResult.h"
+#import "MCPStreamingResult.h"
 #import "MCPNumber.h"
 #import "MCPNull.h"
 #import "MCPConnectionProxy.h"
@@ -1230,15 +1231,26 @@ static void forcePingTimeout(int signalNumber)
  */
 - (MCPResult *)queryString:(NSString *)query
 {
-	return [self queryString:query usingEncoding:mEncoding];
+	return [self queryString:query usingEncoding:mEncoding streamingResult:NO];
+}
+
+/**
+ * Takes a query string and returns an MCPStreamingResult representing the result of the query.
+ * The returned MCPStreamingResult is retained and the client is responsible for releasing it.
+ * If no fields are present in the result, nil will be returned.
+ */
+- (MCPStreamingResult *)streamingQueryString:(NSString *)query
+{
+	return [self queryString:query usingEncoding:mEncoding streamingResult:YES];
 }
 
 /**
  * Error checks connection extensively - if this method fails due to a connection error, it will ask how to
  * proceed and loop depending on the status, not returning control until either the query has been executed
  * and the result can be returned or the connection and document have been closed.
+ * If using streamingResult, the caller is responsible for releasing the result set.
  */
-- (MCPResult *)queryString:(NSString *) query usingEncoding:(NSStringEncoding) encoding
+- (id)queryString:(NSString *) query usingEncoding:(NSStringEncoding) encoding streamingResult:(BOOL) streamResult
 {
 	MCPResult		*theResult = nil;
 	double			queryStartTime, queryExecutionTime;
@@ -1340,9 +1352,16 @@ static void forcePingTimeout(int signalNumber)
 		if (0 == queryResultCode) {
 			
 			if (mysql_field_count(mConnection) != 0) {
-				theResult = [[MCPResult alloc] initWithMySQLPtr:mConnection encoding:mEncoding timeZone:mTimeZone];
-
-				[queryLock unlock];
+				
+				// For normal result sets, fetch the results and unlock the connection
+				if (!streamResult) {
+					theResult = [[MCPResult alloc] initWithMySQLPtr:mConnection encoding:mEncoding timeZone:mTimeZone];
+					[queryLock unlock];
+				
+				// For streaming result sets, fetch the result pointer and leave the connection locked
+				} else {
+					theResult = [[MCPStreamingResult alloc] initWithMySQLPtr:mConnection encoding:mEncoding timeZone:mTimeZone connection:self];
+				}
 				
 				// Ensure no problem occurred during the result fetch
 				if (mysql_errno(mConnection) != 0) {
@@ -1356,9 +1375,13 @@ static void forcePingTimeout(int signalNumber)
 			
 			queryErrorMessage = [[NSString alloc] initWithString:@""];
 			queryErrorId = 0;
-			queryAffectedRows = mysql_affected_rows(mConnection);
+			if (!streamResult) {
+				queryAffectedRows = mysql_affected_rows(mConnection);
+			} else {
+				queryAffectedRows = 0;
+			}
 			
-			// On failure, set the error messages and IDs
+		// On failure, set the error messages and IDs
 		} else {
 			[queryLock unlock];
 			
@@ -1375,13 +1398,16 @@ static void forcePingTimeout(int signalNumber)
 		break;
 	}
 	
-	// If the mysql thread id has changed as a result of a connection error,
-	// ensure connection details are still correct
-	if (connectionThreadId != mConnection->thread_id) [self restoreConnectionDetails];
-	
-	// If max_allowed_packet was changed, reset it to default
-	if(currentMaxAllowedPacket > -1)
-		[self setMaxAllowedPacketTo:currentMaxAllowedPacket resetSize:YES];
+	if (!streamResult) {
+		
+		// If the mysql thread id has changed as a result of a connection error,
+		// ensure connection details are still correct
+		if (connectionThreadId != mConnection->thread_id) [self restoreConnectionDetails];
+		
+		// If max_allowed_packet was changed, reset it to default
+		if(currentMaxAllowedPacket > -1)
+			[self setMaxAllowedPacketTo:currentMaxAllowedPacket resetSize:YES];
+	}
 	
 	// Update error strings and IDs
 	lastQueryErrorId = queryErrorId;
@@ -1397,6 +1423,7 @@ static void forcePingTimeout(int signalNumber)
 	(void)(*startKeepAliveTimerResettingStatePtr)(self, startKeepAliveTimerResettingStateSEL, YES);
 	
 	if (!theResult) return nil;
+	if (streamResult) return theResult;
 	return [theResult autorelease];
 }
 
@@ -1435,6 +1462,17 @@ static void forcePingTimeout(int signalNumber)
 }
 
 #pragma mark -
+#pragma mark Connection locking
+
+/**
+ * Unlock the connection
+ */
+- (void)unlockConnection
+{
+	[queryLock unlock];
+}
+
+#pragma mark -
 #pragma mark Database structure
 
 /**
@@ -1844,7 +1882,7 @@ static void forcePingTimeout(int signalNumber)
 - (int)getMaxAllowedPacket
 {
 	MCPResult *r;
-	r = [self queryString:@"SELECT @@global.max_allowed_packet" usingEncoding:mEncoding];
+	r = [self queryString:@"SELECT @@global.max_allowed_packet" usingEncoding:mEncoding streamingResult:NO];
 	if (![[self getLastErrorMessage] isEqualToString:@""]) {
 		if ([self isConnected])
 			NSRunAlertPanel(@"Error", [NSString stringWithFormat:@"An error occured while retrieving max_allowed_packet size:\n\n%@", [self getLastErrorMessage]], @"OK", nil, nil);
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPKit.h b/Frameworks/MCPKit/MCPFoundationKit/MCPKit.h
index 549e4144..11d2c72e 100644
--- a/Frameworks/MCPKit/MCPFoundationKit/MCPKit.h
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPKit.h
@@ -31,6 +31,7 @@
 #import <MCPKit/MCPConstants.h>
 #import <MCPKit/MCPNull.h>
 #import <MCPKit/MCPResult.h>
+#import <MCPKit/MCPStreamingResult.h>
 #import <MCPKit/MCPConnection.h>
 #import <MCPKit/MCPNumber.h>
 #import <MCPKit/MCPResultPlus.h>
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.h b/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.h
new file mode 100644
index 00000000..168f85d3
--- /dev/null
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.h
@@ -0,0 +1,41 @@
+//
+//	$Id$
+//
+//  MCPStreamingResult.h
+//  sequel-pro
+//
+//  Created by Rowan Beentje on Aug 16, 2009
+//  Copyright 2009 Rowan Beentje. All rights reserved.
+//
+//  This program is free software; you can redistribute it and/or modify
+//  it under the terms of the GNU General Public License as published by
+//  the Free Software Foundation; either version 2 of the License, or
+//  (at your option) any later version.
+//
+//  This program is distributed in the hope that it will be useful,
+//  but WITHOUT ANY WARRANTY; without even the implied warranty of
+//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+//  GNU General Public License for more details.
+//
+//  You should have received a copy of the GNU General Public License
+//  along with this program; if not, write to the Free Software
+//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
+
+#import <Cocoa/Cocoa.h>
+#import "MCPResult.h"
+
+@class MCPConnection;
+
+@interface MCPStreamingResult : MCPResult
+{
+	MCPConnection *parentConnection;
+
+	MYSQL_FIELD		*fieldDefinitions;
+}
+
+- (id)initWithMySQLPtr:(MYSQL *)mySQLPtr encoding:(NSStringEncoding)theEncoding timeZone:(NSTimeZone *)theTimeZone connection:(MCPConnection *)theConnection;
+- (NSArray *)fetchNextRowAsArray;
+
+@end
diff --git a/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.m b/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.m
new file mode 100644
index 00000000..0b46ddc5
--- /dev/null
+++ b/Frameworks/MCPKit/MCPFoundationKit/MCPStreamingResult.m
@@ -0,0 +1,203 @@
+//
+//	$Id$
+//
+//  MCPStreamingResult.m
+//  sequel-pro
+//
+//  Created by Rowan Beentje on Aug 16, 2009
+//  Copyright 2009 Rowan Beentje. All rights reserved.
+//
+//  This program is free software; you can redistribute it and/or modify
+//  it under the terms of the GNU General Public License as published by
+//  the Free Software Foundation; either version 2 of the License, or
+//  (at your option) any later version.
+//
+//  This program is distributed in the hope that it will be useful,
+//  but WITHOUT ANY WARRANTY; without even the implied warranty of
+//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+//  GNU General Public License for more details.
+//
+//  You should have received a copy of the GNU General Public License
+//  along with this program; if not, write to the Free Software
+//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
+
+#import "MCPStreamingResult.h"
+#import "MCPConnection.h"
+#import "MCPNull.h"
+#import "MCPNumber.h"
+
+@implementation MCPStreamingResult : MCPResult
+
+#pragma mark -
+#pragma mark Setup and teardown
+
+/**
+ * Initialise a MCPStreamingResult in the same way as MCPResult - as used
+ * internally by the MCPConnection !{queryString:} method.
+ */
+- (id)initWithMySQLPtr:(MYSQL *)mySQLPtr encoding:(NSStringEncoding)theEncoding timeZone:(NSTimeZone *)theTimeZone connection:(MCPConnection *)theConnection
+{
+	if ((self = [super init])) {
+		mEncoding = theEncoding;
+		mTimeZone = [theTimeZone retain];
+		parentConnection = theConnection;
+		
+		if (mResult) {
+			mysql_free_result(mResult);
+			mResult = NULL;
+		}
+		
+		if (mNames) {
+			[mNames release];
+			mNames = NULL;
+		}
+		
+		mResult = mysql_use_result(mySQLPtr);
+		
+		if (mResult) {
+			mNumOfFields = mysql_num_fields(mResult);
+			fieldDefinitions = mysql_fetch_fields(mResult);
+		} else {
+			mNumOfFields = 0;
+		}
+		
+		if (mMySQLLocales == NULL) {
+			mMySQLLocales = [[MCPConnection getMySQLLocales] retain];
+		}
+	}
+	
+	return self;
+}
+
+/**
+ * Deallocate the result and unlock the parent connection for further use
+ */
+- (void) dealloc
+{
+	[parentConnection unlockConnection];
+	
+	[super dealloc];
+}
+
+#pragma mark -
+#pragma mark Results fetching
+
+/**
+ * Retrieve the next row of the result as an array.  Should be called in a loop
+ * until nil is returned to ensure all the results have been retrieved.
+ */
+- (NSArray *)fetchNextRowAsArray
+{
+	MYSQL_ROW theRow;
+	unsigned long *fieldLengths;
+	int i;
+	NSMutableArray *returnArray;
+
+	// Retrieve the next row
+	theRow = mysql_fetch_row(mResult);
+
+	// If no data was returned, we're at the end of the result set - return nil.
+	if (theRow == NULL) return nil;
+
+	// Retrieve the lengths of the returned data
+	fieldLengths = mysql_fetch_lengths(mResult);
+
+	// Initialise the array to return
+	returnArray = [NSMutableArray arrayWithCapacity:mNumOfFields];
+	for (i = 0; i < mNumOfFields; i++) {
+		id cellData;
+
+		// Use NSNulls for the NULL data type
+		if (theRow[i] == NULL) {
+			cellData = [NSNull null];
+
+		// Otherwise, switch by data type
+		} else {
+
+			// Create a null-terminated data string for processing
+			char *theData = calloc(sizeof(char), fieldLengths[i]+1);
+			memcpy(theData, theRow[i], fieldLengths[i]);
+			theData[fieldLengths[i]] = '\0';
+
+			switch (fieldDefinitions[i].type) {
+				case FIELD_TYPE_TINY:
+				case FIELD_TYPE_SHORT:
+				case FIELD_TYPE_INT24:
+				case FIELD_TYPE_LONG:
+				case FIELD_TYPE_LONGLONG:
+				case FIELD_TYPE_DECIMAL:
+				case FIELD_TYPE_NEWDECIMAL:
+				case FIELD_TYPE_FLOAT:
+				case FIELD_TYPE_DOUBLE:
+				case FIELD_TYPE_TIMESTAMP:
+				case FIELD_TYPE_DATE:
+				case FIELD_TYPE_TIME:
+				case FIELD_TYPE_DATETIME:
+				case FIELD_TYPE_YEAR:
+				case FIELD_TYPE_VAR_STRING:
+				case FIELD_TYPE_STRING:
+				case FIELD_TYPE_SET:
+				case FIELD_TYPE_ENUM:
+				case FIELD_TYPE_NEWDATE: // Don't know what the format for this type is...
+					cellData = [NSString stringWithCString:theData encoding:mEncoding];
+					break;
+					
+				case FIELD_TYPE_BIT:
+					cellData = [NSString stringWithFormat:@"%u", theData[0]];
+					break;
+					
+				case FIELD_TYPE_TINY_BLOB:
+				case FIELD_TYPE_BLOB:
+				case FIELD_TYPE_MEDIUM_BLOB:
+				case FIELD_TYPE_LONG_BLOB:
+					
+					// For binary data, return the data
+					if (fieldDefinitions[i].flags & BINARY_FLAG) {
+						cellData = [NSData dataWithBytes:theData length:fieldLengths[i]];
+
+					// For string data, convert to text
+					} else {
+						cellData = [[NSString alloc] initWithBytes:theData length:fieldLengths[i] encoding:mEncoding];
+						if (cellData) [cellData autorelease];
+					}
+					break;
+					
+				case FIELD_TYPE_NULL:
+					cellData = [NSNull null];
+					break;
+					
+				default:
+					NSLog(@"in fetchNextRowAsArray : Unknown type : %d for column %d, sending back a NSData object", (int)fieldDefinitions[i].type, (int)i);
+					cellData = [NSData dataWithBytes:theData length:fieldLengths[i]];
+					break;
+			}
+
+			free(theData);
+
+			// If a creator returned a nil object, replace with NSNull
+			if (cellData == nil) cellData = [NSNull null];
+		}
+
+		[returnArray insertObject:cellData atIndex:i];
+	}
+
+	return returnArray;
+}
+
+#pragma mark -
+#pragma mark Overrides for safety
+
+- (my_ulonglong) numOfRows
+{
+	NSLog(@"numOfRows cannot be used with streaming results");
+	return 0;
+}
+
+- (void) dataSeek:(my_ulonglong) row
+{
+	NSLog(@"dataSeek cannot be used with streaming results");
+}
+
+@end
\ No newline at end of file