Visible improvements in this build:

 - Significantly reduce the queries that have to be performed, improving lag - especially over slow connections (Issue #118; see new controller info under headline code changes).
 - Fix Issue #117 properly (export numeric quoting - we now have access to column types and so can quote appropriately).
 - Fix Issue #145 (loss of unsigned/null/default attributes when reordering columns).
 - Fixes Issue #90 (support for filtering DECIMAL column types)
 - Improve table scrolling speed when the table contains long items. (Added a NSFormatter to automatically truncate strings > 150 chars for display purposes only)
 - Improved SQL compatibility - for example /* C style comments */ are now correctly ignored in imports and custom queries.
 - Add text and symbols emphasising that the table info pane / status view row count is an approximation (partially addresses Issue #141)
 - Fixes a major memory leak whenever opening or scrolling tables containing text/blob data.
 - SQL import is now faster (SQL parsing part is 3x faster).
 - Speed up SQL export (1.5x faster for numeric data; 1.1x faster for string data) and slightly speed up CSV export (~1.1x faster).
 - Display sizes on the status view using the byte size formatter, as per table info pane.
 

Headline code changes:
 - Add a new NSMutableString subclass, SPSQLParser.  See the header file for documentation and overview, but in short it's a centralised place for SQL parsing.  Centralises and improves parsing, improves comment support, improves quoting support.  Despite the improved featureset this is also faster than the previous distributed implementations - for example, when used to replace the old splitQueries:, > 3x speedup.
 - Implement a new controller which handles a structure and status cache for the current table, and provides structure parsing for specified tables.  This cache is now used throughout the code, reducing the queries that have to be performed and providing additional information about the table structure for use; I think it also improves column type format slightly.
 - The table info pane and the status view now draw all their data from the cache.


Tweaks:
 - Table encoding is now detected directly instead of being derived from the collation - increased accuracy and cope with the DEFAULT encoding.
 - Comments and formatting cleaned up in bits I was working on, obviously.
 - A couple of methods - particularly [tablesListInstance table] and [tableDocument encoding] - have been renamed to avoid conflicts and fix code warnings.


Future improvements now possible:
 - As we now have access to column types and other information, we can provide per-type behaviour where desired.
 - The table parsing doesn't currently pull out comments or table indices, together with one or two other attributes.  Some of this would be useful for display; some, such as indices, could be used to draw the table structure view as long as we're happy discarding a couple of columns (ie cardinality!)

1 files changed, 63 insertions, 43 deletions

diff --git a/Source/TableSource.m b/Source/TableSource.m
index b9b1a3c1..d9c7a595 100644
--- a/Source/TableSource.m
+++ b/Source/TableSource.m
@@ -24,6 +24,7 @@
 
 #import "TableSource.h"
 #import "TablesList.h"
+#import "SPTableData.h"
 
 
 @implementation TableSource
@@ -41,19 +42,16 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab
 	NSEnumerator *extrasEnumerator;
 	id extra;
 	int i;
-  
+
 	selectedTable = aTable;
 	[tableSourceView deselectAll:self];
-	
+
 	if ( isEditingRow )
 		return;
-  
+
 	// empty variables
 	[enumFields removeAllObjects];
-  
-	//query started
-	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self];
-  
+
 	if ( [aTable isEqualToString:@""] || !aTable ) {
 		[tableFields removeAllObjects];
 		[indexes removeAllObjects];
@@ -70,14 +68,14 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab
 		[tableTypeButton setEnabled:NO];
 		tableType = nil;
 
-		//query finished
-		[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self];
-
 		[scanner release];
 
 		return;
 	}
 	
+	//query started
+	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self];
+  
 	//perform queries and load results in array (each row as a dictionary)
 	tableSourceResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM `%@`", selectedTable]] retain];
 	
@@ -91,17 +89,10 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab
 	//	[indexes setArray:[[self fetchResultAsArray:indexResult] retain]];
 	[indexes setArray:[self fetchResultAsArray:indexResult]];
 	[indexResult release];
-	
-	CMMCPResult *tableStatusResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW TABLE STATUS LIKE '%@'", selectedTable]];
+
+	// Retrieve the table type via the table data's status cache (which automatically maps Type to Engine)
 	[tableType release];
-	
-	NSDictionary *tempRow = [tableStatusResult fetchRowAsDictionary];
-	if ( [tempRow objectForKey:@"Type"]) {
-		tableType = [tempRow objectForKey:@"Type"];
-	} else {
-		tableType = [tempRow objectForKey:@"Engine"];
-	}
-	[tableType retain];
+	tableType = [[NSString stringWithString:[tableDataInstance statusValueForKey:@"Engine"]] retain];
 	
 	//get table default values
 	if ( defaultValues ) {
@@ -384,8 +375,9 @@ opens alertsheet and asks for confirmation
 			
 //			[[NSNotificationCenter defaultCenter] postNotificationName:@"SelectedTableStatusHasChanged" object:self];		
 
-			// Mark the content table for refresh
+			// Mark the content table for refresh and update column caches
 			[tablesListInstance setContentRequiresReload:YES];
+			[tableDataInstance resetColumnData];
 		} else {
 			[sender selectItemWithTitle:tableType];
 			NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
@@ -655,8 +647,9 @@ returns YES if no row is beeing edited and nothing has to be written to db
 		isEditingNewRow = NO;
 		[self loadTable:selectedTable];
 
-		// Mark the content table for refresh
+		// Mark the content table and column caches for refresh
 		[tablesListInstance setContentRequiresReload:YES];
+		[tableDataInstance resetColumnData];
 
 		return YES;
 	} else {
@@ -711,8 +704,9 @@ returns YES if no row is beeing edited and nothing has to be written to db
 			if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
 				[self loadTable:selectedTable];
 
-				// Mark the content table for refresh
+				// Mark the content table and column cache for refresh
 				[tablesListInstance setContentRequiresReload:YES];
+				[tableDataInstance resetColumnData];
 			} else {
 				NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
 					[NSString stringWithFormat:NSLocalizedString(@"Couldn't remove field %@.\nMySQL said: %@", @"message of panel when field cannot be removed"),
@@ -768,7 +762,7 @@ returns an array containing the field names of the selected table
 	
 	//load table if not already done
 	if ( ![tablesListInstance structureLoaded] ) {
-		[self loadTable:(NSString *)[tablesListInstance table]];
+		[self loadTable:[tablesListInstance tableName]];
 	}
 	
 	//get field names
@@ -883,37 +877,61 @@ would result in a position change.
 }
 
 /*
-Having validated a drop, perform the field/column reordering to match.
-*/
-- (BOOL)tableView:(NSTableView*)tableView acceptDrop:(id <NSDraggingInfo>)info row:(int)destinationRow dropOperation:(NSTableViewDropOperation)operation
+ * Having validated a drop, perform the field/column reordering to match.
+ */
+- (BOOL)tableView:(NSTableView*)tableView acceptDrop:(id <NSDraggingInfo>)info row:(int)destinationRowIndex dropOperation:(NSTableViewDropOperation)operation
 {
-	int originalRow;
+	int originalRowIndex;
 	NSMutableString *queryString;
+	NSDictionary *originalRow;
 
-	// Extract the original row position from the pasteboard.
-	originalRow = [[[info draggingPasteboard] stringForType:@"SequelProPasteboard"] intValue];
+	// Extract the original row position from the pasteboard and retrieve the details
+	originalRowIndex = [[[info draggingPasteboard] stringForType:@"SequelProPasteboard"] intValue];
+	originalRow = [[NSDictionary alloc] initWithDictionary:[tableFields objectAtIndex:originalRowIndex]];
 
 	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self];
 
 	// Begin construction of the reordering query
 	queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` MODIFY COLUMN `%@` %@", selectedTable,
-		[[tableFields objectAtIndex:originalRow] objectForKey:@"Field"],
-		[[tableFields objectAtIndex:originalRow] objectForKey:@"Type"]];
+		[originalRow objectForKey:@"Field"],
+		[originalRow objectForKey:@"Type"]];
 
 	// Add the length parameter if necessary
-	if ( [[tableFields objectAtIndex:originalRow] objectForKey:@"Length"] &&
-		![[[tableFields objectAtIndex:originalRow] objectForKey:@"Length"] isEqualToString:@""])
-	{
-		[queryString appendString:[NSString stringWithFormat:@"(%@)",
-			[[tableFields objectAtIndex:originalRow] objectForKey:@"Length"]]];
+	if ( [originalRow objectForKey:@"Length"] && ![[originalRow objectForKey:@"Length"] isEqualToString:@""]) {
+		[queryString appendString:[NSString stringWithFormat:@"(%@)", [originalRow objectForKey:@"Length"]]];
+	}
+
+	// Add unsigned, zerofill, binary, not null if necessary
+	if ([[originalRow objectForKey:@"unsigned"] isEqualToString:@"1"]) {
+		[queryString appendString:@" UNSIGNED"];
+	}
+	if ([[originalRow objectForKey:@"zerofill"] isEqualToString:@"1"]) {
+		[queryString appendString:@" ZEROFILL"];
+	}
+	if ([[originalRow objectForKey:@"binary"] isEqualToString:@"1"]) {
+		[queryString appendString:@" BINARY"];
+	}
+	if ([[originalRow objectForKey:@"Null"] isEqualToString:@"NO"] ) {
+		[queryString appendString:@" NOT NULL"];
+	}
+
+	// Add the default value
+	if ([[originalRow objectForKey:@"Default"] isEqualToString:[prefs objectForKey:@"nullValue"]]) {
+		if ([[originalRow objectForKey:@"Null"] isEqualToString:@"YES"]) {
+			[queryString appendString:@" DEFAULT NULL"];
+		}
+	} else if ( [[originalRow objectForKey:@"Type"] isEqualToString:@"timestamp"] && ([[[originalRow objectForKey:@"Default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) ) {
+			[queryString appendString:@" DEFAULT CURRENT_TIMESTAMP"];
+	} else {
+		[queryString appendString:[NSString stringWithFormat:@" DEFAULT '%@'", [mySQLConnection prepareString:[originalRow objectForKey:@"Default"]]]];
 	}
 
 	// Add the new location
-	if ( destinationRow == 0 ){
+	if ( destinationRowIndex == 0 ){
 		[queryString appendString:@" FIRST"];
 	} else {
 		[queryString appendString:[NSString stringWithFormat:@" AFTER `%@`",
-						[[tableFields objectAtIndex:destinationRow-1] objectForKey:@"Field"]]];
+						[[tableFields objectAtIndex:destinationRowIndex-1] objectForKey:@"Field"]]];
 	}
 
 	// Run the query; report any errors, or reload the table on success
@@ -923,18 +941,20 @@ Having validated a drop, perform the field/column reordering to match.
 			[NSString stringWithFormat:NSLocalizedString(@"Couldn't move field. MySQL said: %@", @"message of panel when field cannot be added in drag&drop operation"), [mySQLConnection getLastErrorMessage]]);
 	} else {
 		[self loadTable:selectedTable];
-		if ( originalRow < destinationRow ) {
-			[tableSourceView selectRow:destinationRow-1 byExtendingSelection:NO];
+		if ( originalRowIndex < destinationRowIndex ) {
+			[tableSourceView selectRow:destinationRowIndex-1 byExtendingSelection:NO];
 		} else {
-			[tableSourceView selectRow:destinationRow byExtendingSelection:NO];
+			[tableSourceView selectRow:destinationRowIndex byExtendingSelection:NO];
 		}
 	}
 
 	[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self];
 
-	// Mark the content table for refresh
+	// Mark the content table and column caches for refresh
 	[tablesListInstance setContentRequiresReload:YES];
+	[tableDataInstance resetColumnData];
 	
+	[originalRow release];
 	return YES;
 }