From f30882885fd781d8a25900896e026138ef9ae5a7 Mon Sep 17 00:00:00 2001
From: Bibiko <bibiko@eva.mpg.de>
Date: Tue, 28 Jul 2009 16:03:37 +0000
Subject: =?UTF-8?q?=E2=80=A2=20fixed=20and=20improved=20several=20issues?=
 =?UTF-8?q?=20for=20sorting=20the=20Custom=20Query=20result=20table:=20-?=
 =?UTF-8?q?=20better=20ORDER=20BY=20detection=20-=20(try=20to)=20insert=20?=
 =?UTF-8?q?ORDER=20BY=20clause=20at=20the=20correct=20position=20(e.g.=20"?=
 =?UTF-8?q?SELECT=20*=20FROM=20A=20LIMIT=2010=20-=20ORDER=20clause=20has?=
 =?UTF-8?q?=20to=20be=20inserted=20before=20LIMIT)=20-=20preserve=20the=20?=
 =?UTF-8?q?sort=20order=20after=20editing=20a=20field?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 Source/CustomQuery.m | 67 +++++++++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 59 insertions(+), 8 deletions(-)

(limited to 'Source/CustomQuery.m')

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]];
-- 
cgit v1.2.3