aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBibiko <bibiko@eva.mpg.de>2009-07-28 16:03:37 +0000
committerBibiko <bibiko@eva.mpg.de>2009-07-28 16:03:37 +0000
commitf30882885fd781d8a25900896e026138ef9ae5a7 (patch)
tree24ddd9708e7fba0b88be6bdfac3958eba2591e32
parent9c06d1219c66acc043b5f13ab29379f60eb00350 (diff)
downloadsequelpro-f30882885fd781d8a25900896e026138ef9ae5a7.tar.gz
sequelpro-f30882885fd781d8a25900896e026138ef9ae5a7.tar.bz2
sequelpro-f30882885fd781d8a25900896e026138ef9ae5a7.zip
• 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
-rw-r--r--Source/CustomQuery.m67
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]];