• fixed and improved several issues for sorting the Custom Query result table:

- better ORDER BY detection
- (try to) insert ORDER BY clause at the correct position (e.g. "SELECT * FROM A LIMIT 10 - ORDER clause has to be inserted before LIMIT)
- preserve the sort order after editing a field

1 files changed, 59 insertions, 8 deletions

diff --git a/Source/CustomQuery.m b/Source/CustomQuery.m
index 894e5cc0..6526c222 100644
--- a/Source/CustomQuery.m
+++ b/Source/CustomQuery.m
@@ -526,8 +526,7 @@
 	// if(!queriesSeparatedByDelimiter) // TODO: How to combine queries delimited by DELIMITER?
 	usedQuery = [[NSString stringWithString:[tempQueries componentsJoinedByString:@";\n"]] retain];
 	
-	if(!tableReloadAfterEditing)
-		lastExecutedQuery = [[tempQueries lastObject] retain];
+	lastExecutedQuery = [[tempQueries lastObject] retain];
 	
 	//perform empty query if no query is given
 	if ( !queryCount ) {
@@ -1452,17 +1451,69 @@
 	// Order by the column position number to avoid ambiguous name errors
 	NSString* newOrder = [NSString stringWithFormat:@" ORDER BY %i %@ ", [[tableColumn identifier] intValue]+1, (isDesc)?@"DESC":@"ASC"];
 	
-	//make queryString and perform query
 	[queryString replaceOccurrencesOfRegex:@"--.*?\n" withString:@""];
 	[queryString replaceOccurrencesOfRegex:@"--.*?$" withString:@""];
 	[queryString replaceOccurrencesOfRegex:@"/\\*(.|\n)*?\\*/" withString:@""];
-	if([queryString isMatchedByRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+(\\s+(DESC|ASC))?(\\s|\\n)+(?=(LI|PR|IN|FO|LO))"])
-		[queryString replaceOccurrencesOfRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+((\\s|\\n)+(DESC|ASC))?(\\s|\\n)+(?=(LI|PR|IN|FO|LO))" withString:newOrder];
-	else if ([queryString isMatchedByRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+((\\s|\\n)+(DESC|ASC))?"])
-		[queryString replaceOccurrencesOfRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+((\\s|\\n)+(DESC|ASC))?" withString:newOrder];
+
+	// Remove all quoted strings as a temp string to match the correct clauses
+	NSRange matchedRange;
+	int i;
+	NSMutableString *tmpString = [NSMutableString stringWithString:queryString];
+	NSMutableString *qq = [NSMutableString string];
+	matchedRange = [tmpString rangeOfRegex:@"\"(?:[^\"\\\\]*+|\\\\.)*\""];
+	// Replace all "..." with _'s
+	while(matchedRange.length) {
+		[qq setString:@""];
+		for(i=0; i<matchedRange.length; i++) [qq appendString:@"_"];
+		[tmpString replaceCharactersInRange:matchedRange withString:qq];
+		[tmpString flushCachedRegexData];
+		matchedRange = [tmpString rangeOfRegex:@"\"(?:[^\"\\\\]*+|\\\\.)*\""];
+	}
+	// Replace all '...' with _'s
+	matchedRange = [tmpString rangeOfRegex:@"'(?:[^'\\\\]*+|\\\\.)*'"];
+	while(matchedRange.length) {
+		[qq setString:@""];
+		for(i=0; i<matchedRange.length; i++) [qq appendString:@"_"];
+		[tmpString replaceCharactersInRange:matchedRange withString:qq];
+		[tmpString flushCachedRegexData];
+		matchedRange = [tmpString rangeOfRegex:@"'(?:[^'\\\\]*+|\\\\.)*'"];
+	}
+	// Replace all `...` with _'s
+	matchedRange = [tmpString rangeOfRegex:@"`(?:[^`\\\\]*+|\\\\.)*`"];
+	while(matchedRange.length) {
+		[qq setString:@""];
+		for(i=0; i<matchedRange.length; i++) [qq appendString:@"_"];
+		[tmpString replaceCharactersInRange:matchedRange withString:qq];
+		[tmpString flushCachedRegexData];
+		matchedRange = [tmpString rangeOfRegex:@"`(?:[^`\\\\]*+|\\\\.)*`"];
+	}
+
+	// Check for an existing ORDER clause (in the temp string),
+	// if so replace it by the new one (in the actual string)
+	// Test for ORDER clause inside a statement
+	if([tmpString isMatchedByRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+(\\s+(DESC|ASC))?(\\s|\\n)+(?=(LI|PR|IN|FO|LO))"])
+		{
+			matchedRange = [tmpString rangeOfRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+(\\s+(DESC|ASC))?(\\s|\\n)+(?=(LI|PR|IN|FO|LO))"];
+			[queryString replaceCharactersInRange:matchedRange withString:newOrder];
+		}
+	// Test for ORDER clause at the end
+	else if ([tmpString isMatchedByRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+((\\s|\\n)+(DESC|ASC))?"])
+		{
+			matchedRange = [tmpString rangeOfRegex:@"(?i)\\s+ORDER\\s+BY\\s+(.|\\n)+((\\s|\\n)+(DESC|ASC))?"];
+			[queryString replaceCharactersInRange:matchedRange withString:newOrder];
+		}
+	// No ORDER clause found
+	// ORDER clause has to be inserted before LIMIT, PROCEDURE, INTO, FOR, or LOCK due to MySQL syntax for SELECT
+	else if([tmpString isMatchedByRegex:@"(?i)\\bSELECT\\b((.|\\n)+?)\\s*(?=(\\sLIMIT\\s|\\sPROCEDURE\\s|\\sINTO\\s|\\sFOR\\s|\\sLOCK\\s))"])
+		{
+			matchedRange = [tmpString rangeOfRegex:@"(?i)\\bSELECT\\b((.|\\n)+?)(?=(\\sLIMIT\\s|\\sPROCEDURE\\s|\\sINTO\\s|\\sFOR\\s|\\sLOCK\\s))" capture:1];
+			NSString *orderHeader = [NSString stringWithFormat:@"%@ %@", [queryString substringWithRange:matchedRange], newOrder];
+			[queryString replaceCharactersInRange:matchedRange withString:orderHeader];
+		}
+	// Otherwise append the new ORDER clause at the end
 	else
 		[queryString appendFormat:@" %@", newOrder];
-		
+
 	tableReloadAfterEditing = YES;
 	queryIsTableSorter = YES;
 	[self performQueries:[NSArray arrayWithObject:queryString]];