- Rework TableContent row count fetching, loading it in the correct locations, and correcting the logic for fetching the count of rows so that the query is not used where not necessary.

 - Update the Table Info pane and tab with a new row count if one is known - this addresses Issue #141
 - This reverts r1090, and so needs to be discussed with stuart02 - the rest of the row logic changes may have fixed the 'larger issue' described there?

6 files changed, 69 insertions, 43 deletions

diff --git a/Source/SPExtendedTableInfo.m b/Source/SPExtendedTableInfo.m
index 11a0188e..0ac716cb 100644
--- a/Source/SPExtendedTableInfo.m
+++ b/Source/SPExtendedTableInfo.m
@@ -375,8 +375,8 @@
 			
 			value = [dateFormatter stringFromDate:[NSDate dateWithNaturalLanguageString:value]];						
 		}
-		// Prefix number of rows with '~' if this is not a MyISAM table. Only MyISAM tables report the exact row count.
-		else if (([key isEqualToString:@"Rows"]) && (![[infoDict objectForKey:@"Engine"] isEqualToString:@"MyISAM"])) {
+		// Prefix number of rows with '~' if it is not an accurate count
+		else if ([key isEqualToString:@"Rows"] && ![[infoDict objectForKey:@"RowsCountAccurate"] boolValue]) {
 			value = [@"~" stringByAppendingString:value];
 		}
 	}
diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index 007dd201..e61eb552 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -746,14 +746,21 @@
 	if ([status objectForKey:@"Type"]) {
 		[status setObject:[status objectForKey:@"Type"] forKey:@"Engine"];
 	}
-	
+
+	// Add a note for whether the row count is accurate or not - only for MyISAM
+	if ([[status objectForKey:@"Engine"] isEqualToString:@"MyISAM"]) {
+		[status setObject:@"y" forKey:@"RowsCountAccurate"];
+	} else {
+		[status setObject:@"n" forKey:@"RowsCountAccurate"];
+	}
+
 	// [status objectForKey:@"Rows"] is NULL then try to get the number of rows via SELECT COUNT(*) FROM `foo`
 	// this happens e.g. for db "information_schema"
 	if([[status objectForKey:@"Rows"] isKindOfClass:[NSNull class]]) {
 		tableStatusResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@", [escapedTableName backtickQuotedString] ]];
 		if ([[mySQLConnection getLastErrorMessage] isEqualToString:@""])
 			[status setObject:[[tableStatusResult fetchRowAsArray] objectAtIndex:0] forKey:@"Rows"];
-
+			[status setObject:@"y" forKey:@"RowsCountAccurate"];
 	}
 
 	return TRUE;
diff --git a/Source/SPTableInfo.h b/Source/SPTableInfo.h
index 13db641f..c0016080 100644
--- a/Source/SPTableInfo.h
+++ b/Source/SPTableInfo.h
@@ -35,4 +35,6 @@
 	NSMutableArray *info;
 }
 
+- (void)tableChanged:(NSNotification *)notification;
+
 @end
diff --git a/Source/SPTableInfo.m b/Source/SPTableInfo.m
index 2cb14bf2..07e6d895 100644
--- a/Source/SPTableInfo.m
+++ b/Source/SPTableInfo.m
@@ -128,7 +128,7 @@
 			
 			// Check for 'Rows' == NULL - information_schema database doesn't report row count for it's tables
 			if (![[tableStatus objectForKey:@"Rows"] isNSNull]) {
-				[info addObject:[NSString stringWithFormat:([[tableStatus objectForKey:@"Engine"] isEqualToString:@"MyISAM"]) ? @"rows: %@" : @"rows: ~%@", [tableStatus objectForKey:@"Rows"]]];
+				[info addObject:[NSString stringWithFormat:[[tableStatus objectForKey:@"RowsCountAccurate"] boolValue] ? @"rows: %@" : @"rows: ~%@", [tableStatus objectForKey:@"Rows"]]];
 			} 
 						
 			[info addObject:[NSString stringWithFormat:@"size: %@", [NSString stringForByteSize:[[tableStatus objectForKey:@"Data_length"] intValue]]]];
diff --git a/Source/TableContent.h b/Source/TableContent.h
index ea09a325..e389c8df 100644
--- a/Source/TableContent.h
+++ b/Source/TableContent.h
@@ -28,13 +28,14 @@
 #import <Cocoa/Cocoa.h>
 #import <MCPKit/MCPKit.h>
 
-@class CMCopyTable, SPTextAndLinkCell, SPHistoryController;
+@class CMCopyTable, SPTextAndLinkCell, SPHistoryController, SPTableInfo;
 
 @interface TableContent : NSObject 
 {	
 	IBOutlet id tableDocumentInstance;
 	IBOutlet id tablesListInstance;
 	IBOutlet id tableDataInstance;
+	IBOutlet SPTableInfo *tableInfoInstance;
 	IBOutlet SPHistoryController *spHistoryControllerInstance;
 	
 	IBOutlet id tableWindow;
@@ -59,9 +60,9 @@
 	NSString *compareType;
 	NSNumber *sortCol;
 	BOOL isEditingRow, isEditingNewRow, isSavingRow, isDesc, setLimit;
-	BOOL isFiltered, isLimited;
+	BOOL isFiltered, isLimited, maxNumRowsIsEstimate;
 	NSUserDefaults *prefs;
-	int numRows, currentlyEditingRow, maxNumRowsOfCurrentTable;
+	int currentlyEditingRow, maxNumRows;
 
 	BOOL sortColumnToRestoreIsAsc;
 	NSString *sortColumnToRestore;
@@ -105,7 +106,7 @@
 - (BOOL)tableContainsBlobOrTextColumns;
 - (NSString *)fieldListForQuery;
 - (void)sheetDidEnd:(NSWindow *)sheet returnCode:(int)returnCode contextInfo:(NSString *)contextInfo;
-- (int)getNumberOfRows;
+- (void)updateNumberOfRows;
 - (int)fetchNumberOfRows;
 - (BOOL)saveRowOnDeselect;
 
diff --git a/Source/TableContent.m b/Source/TableContent.m
index a14375e3..5ebb15f9 100644
--- a/Source/TableContent.m
+++ b/Source/TableContent.m
@@ -29,6 +29,7 @@
 
 #import "TableContent.h"
 #import "TableDocument.h"
+#import "SPTableInfo.h"
 #import "TablesList.h"
 #import "CMImageView.h"
 #import "CMCopyTable.h"
@@ -190,13 +191,6 @@
 			[dataColumns replaceObjectAtIndex:columnIndex withObject:rowDictionary];
 		}
 	}
-
-	// Retrieve the total number of rows of the current table
-	// to adjustify "Limit From:"
-	maxNumRowsOfCurrentTable = [[[tableDataInstance statusValues] objectForKey:@"Rows"] intValue];
-
-	// Retrieve the number of rows in the table
-	numRows = [self getNumberOfRows];
 	
 	NSString *nullValue = [prefs objectForKey:@"NullValue"];
 	
@@ -326,8 +320,9 @@
 	// Enable or disable the limit fields according to preference setting
 	if ( [prefs boolForKey:@"LimitResults"] ) {
 
-		// Attempt to preserve the limit value if it's still valid
-		if (limitStartPositionToRestore < 1 || limitStartPositionToRestore >= numRows) limitStartPositionToRestore = 1;
+		// Preserve the limit field - if this is beyond the current number of rows,
+		// reloadData will reset as necessary.
+		if (limitStartPositionToRestore < 1) limitStartPositionToRestore = 1;
 		[limitRowsField setStringValue:[NSString stringWithFormat:@"%u", limitStartPositionToRestore]];
 
 		[limitRowsField setEnabled:YES];
@@ -431,7 +426,6 @@
 	// Run the query and capture the result
 	queryResult = [mySQLConnection queryString:queryString];
 	[tableValues setArray:[self fetchResultAsArray:queryResult]];
-	numRows = [self getNumberOfRows];
 
 	// If the result is empty, and a limit is active, reset the limit
 	if ([prefs boolForKey:@"LimitResults"] && queryStringBeforeLimit && ![tableValues count]) {
@@ -440,7 +434,6 @@
 		[self setUsedQuery:queryString];
 		queryResult = [mySQLConnection queryString:queryString];
 		[tableValues setArray:[self fetchResultAsArray:queryResult]];
-		numRows = [self getNumberOfRows];
 	}
 
 	if ([prefs boolForKey:@"LimitResults"]
@@ -452,6 +445,9 @@
 		isLimited = NO;
 	}
 
+	// Update the rows count as necessary
+	[self updateNumberOfRows];
+
 	// Set the filter text
 	[self updateCountText];
 	
@@ -639,18 +635,18 @@
 
 	// If a limit is active, display a string suggesting a limit is active
 	} else if (!isFiltered && isLimited) {
-		[countString appendFormat:NSLocalizedString(@"Rows %d-%d from table", @"text showing how many rows are in the limited result"), [limitRowsField intValue], [limitRowsField intValue]+[tableValues count]-1];
+		[countString appendFormat:NSLocalizedString(@"Rows %d-%d of %@%d from table", @"text showing how many rows are in the limited result"), [limitRowsField intValue], [limitRowsField intValue]+[tableValues count]-1, maxNumRowsIsEstimate?@"~":@"", maxNumRows];
 
 	// If just a filter is active, show a count and an indication a filter is active
 	} else if (isFiltered && !isLimited) {
 		if ([tableValues count] == 1)
-			[countString appendFormat:NSLocalizedString(@"%d row matches filter", @"text showing how a single rows matched filter"), [tableValues count]];
+			[countString appendFormat:NSLocalizedString(@"%d row of %@%d matches filter", @"text showing how a single rows matched filter"), [tableValues count], maxNumRowsIsEstimate?@"~":@"", maxNumRows];
 		else
-			[countString appendFormat:NSLocalizedString(@"%d rows match filter", @"text showing how many rows matched filter"), [tableValues count]];
+			[countString appendFormat:NSLocalizedString(@"%d rows of %@%d match filter", @"text showing how many rows matched filter"), [tableValues count], maxNumRowsIsEstimate?@"~":@"", maxNumRows];
 
 	// If both a filter and limit is active, display full string
 	} else {
-		[countString appendFormat:NSLocalizedString(@"Rows %d-%d rows from filter matches", @"text showing how many rows are in the limited filter match"), [limitRowsField intValue], [limitRowsField intValue]+[tableValues count]-1];
+		[countString appendFormat:NSLocalizedString(@"Rows %d-%d from filtered matches", @"text showing how many rows are in the limited filter match"), [limitRowsField intValue], [limitRowsField intValue]+[tableValues count]-1];
 	}
 
 	// If rows are selected, append selection count
@@ -707,8 +703,8 @@
 	}
 
 	// If limitRowsField > number of total table rows show the last limitRowsValue rows
-	if ( [prefs boolForKey:@"LimitResults"] && [limitRowsField intValue] >= maxNumRowsOfCurrentTable ) {
-		int newLimit = maxNumRowsOfCurrentTable - [prefs integerForKey:@"LimitResultsValue"];
+	if ( [prefs boolForKey:@"LimitResults"] && [limitRowsField intValue] >= maxNumRows ) {
+		int newLimit = maxNumRows - [prefs integerForKey:@"LimitResultsValue"];
 		[limitRowsField setStringValue:[[NSNumber numberWithInt:(newLimit<1)?1:newLimit] stringValue]];
 	}
 
@@ -860,9 +856,7 @@
 	
 	NSString *contextInfo = @"removerow";
 	
-	if (([tableContentView numberOfSelectedRows] == [tableContentView numberOfRows]) && 
-		(([prefs boolForKey:@"LimitResults"] && [tableContentView numberOfSelectedRows] == [self fetchNumberOfRows]) ||
-		 (![prefs boolForKey:@"LimitResults"] && [tableContentView numberOfSelectedRows] == [self getNumberOfRows]))) {
+	if (([tableContentView numberOfSelectedRows] == [tableContentView numberOfRows]) && !isFiltered && !isLimited) {
 		
 		contextInfo = @"removeallrows";
 		
@@ -1113,7 +1107,7 @@
 	if ( [limitRowsStepper intValue] > 0 ) {
 		int newStep = [limitRowsField intValue]+[prefs integerForKey:@"LimitResultsValue"];
 		// if newStep > the total number of rows in the current table retain the old value
-		[limitRowsField setIntValue:(newStep>maxNumRowsOfCurrentTable)?[limitRowsField intValue]:newStep];
+		[limitRowsField setIntValue:(newStep>maxNumRows)?[limitRowsField intValue]:newStep];
 	} else {
 		if ( ([limitRowsField intValue]-[prefs integerForKey:@"LimitResultsValue"]) < 1 ) {
 			[limitRowsField setIntValue:1];
@@ -1576,7 +1570,6 @@
 						[tempResult addObject:NSArrayObjectAtIndex(tableValues, i)];
 				}
 				[tableValues setArray:tempResult];
-				numRows = [self getNumberOfRows];
 				[tableContentView reloadData];
 			}
 			[tableContentView deselectAll:self];
@@ -1745,21 +1738,44 @@
 #pragma mark Table drawing and editing
 
 /**
- * Returns the number of rows in the selected table
- * Queries the number from MySQL if enabled in prefs and result is limited, otherwise just return the fullResult count.
+ * Updates the number of rows in the selected table.
+ * Attempts to use the fullResult count if available, also updating the
+ * table data store; otherwise, uses the table data store if accurate or
+ * falls back to a fetch if necessary and set in preferences.
+ * The prefs option "fetch accurate row counts" is used as a last resort as
+ * it can be very slow on large InnoDB tables which require a full table scan.
  */
-- (int)getNumberOfRows
-{	
-	if ([prefs boolForKey:@"LimitResults"] && [prefs boolForKey:@"FetchCorrectRowCount"]) {
-		numRows = [self fetchNumberOfRows];
+- (void)updateNumberOfRows
+{
+
+	// For unfiltered and non-limited tables, use the result count - and update the status count
+	if (!isLimited && !isFiltered) {
+		maxNumRows = [tableValues count];
+		maxNumRowsIsEstimate = NO;
+		[tableDataInstance setStatusValue:[NSString stringWithFormat:@"%d", maxNumRows] forKey:@"Rows"];
+		[tableDataInstance setStatusValue:@"y" forKey:@"RowsCountAccurate"];
+		[tableInfoInstance tableChanged:nil];
+		[[tableDocumentInstance valueForKey:@"extendedTableInfoInstance"] loadTable:selectedTable];
+
+	// Otherwise, if the table status value is accurate, use it
+	} else if ([[tableDataInstance statusValueForKey:@"RowsCountAccurate"] boolValue]) {
+		maxNumRows = [[tableDataInstance statusValueForKey:@"Rows"] intValue];
+		maxNumRowsIsEstimate = NO;
+
+	// Choose whether to display an estimate, or to fetch the correct row count, based on prefs
+	} else if ([prefs boolForKey:@"FetchCorrectRowCount"]) {
+		maxNumRows = [self fetchNumberOfRows];
+		maxNumRowsIsEstimate = NO;
+		[tableDataInstance setStatusValue:[NSString stringWithFormat:@"%d", maxNumRows] forKey:@"Rows"];
+		[tableDataInstance setStatusValue:@"y" forKey:@"RowsCountAccurate"];
+		[tableInfoInstance tableChanged:nil];
+		[[tableDocumentInstance valueForKey:@"extendedTableInfoInstance"] loadTable:selectedTable];
+
+	// Use the estimate count
 	} else {
-		numRows = [tableValues count];
+		maxNumRows = [[tableDataInstance statusValueForKey:@"Rows"] intValue];
+		maxNumRowsIsEstimate = YES;
 	}
-		
-	// Update table data cache with the more accurate row count
-	//[tableDataInstance setStatusValue:[NSString stringWithFormat:@"%d", numRows] forKey:@"Rows"];
-	
-	return numRows;
 }
 
 /*