• implemented a new approach to split a string into single SQL statements by using the lexer SPTokenizer

- the new method is called splitStringIntoRangesOfSQLQueries: in SPSQLParser
- in CustomQuery's method queryAtPosition: can be found a test case which is as default not activated
- must be improved further

5 files changed, 126 insertions, 17 deletions

diff --git a/Source/CustomQuery.m b/Source/CustomQuery.m
index 76c767d7..27f72b04 100644
--- a/Source/CustomQuery.m
+++ b/Source/CustomQuery.m
@@ -780,6 +780,16 @@ sets the tableView columns corresponding to the mysql-result
 	// Split the current text into queries
 	customQueryParser = [[SPSQLParser alloc] initWithString:[textView string]];
 	queries = [[NSArray alloc] initWithArray:[customQueryParser splitStringByCharacter:';']];
+	
+	// ========= test case for SQL splitting into ranges == START
+	// NSArray *qs; int qc;
+	// qs = [[NSArray alloc] initWithArray:[customQueryParser splitStringIntoRangesOfSQLQueries]];
+	// NSLog(@"---START---");
+	// for(qc=0;qc<[qs count];qc++) {
+	// 	NSLog(@"%d:", qc);
+	// 	NSLog(@"%@", [[textView string] substringWithRange:NSRangeFromString([qs objectAtIndex:qc])]);
+	// }
+	// ========= test case for SQL splitting into ranges == END
 	[customQueryParser release];
 
 	// Walk along the array of queries to identify the current query - taking into account
diff --git a/Source/SPSQLParser.h b/Source/SPSQLParser.h
index 4821d775..af60645a 100644
--- a/Source/SPSQLParser.h
+++ b/Source/SPSQLParser.h
@@ -213,6 +213,19 @@ typedef enum _SPCommentTypes {
 - (NSArray *) splitStringByCharacter:(unichar)character skippingBrackets:(BOOL)skipBrackets ignoringQuotedStrings:(BOOL)ignoreQuotedStrings;
 
 /*
+ * As splitStringByCharacter: ..., but allows control over quoting
+ * - it recognises CREATE ... BEGIN ... END statements
+ * - it can detect a SINGLE SQL statement in between
+ *     delimiter foo ... foo delimiter ; 
+ *     ['delimiter ;' MUST be given!]
+ * - it returns an array of ranges (as NSString "{loc, length}").
+ * FromPosition: is needed if a subrange is passed to sync the ranges 
+ * according to the CQ textView ones.
+ */
+- (NSArray *) splitStringIntoRangesOfSQLQueries;
+- (NSArray *) splitStringIntoRangesOfSQLQueriesFromPosition:(long)position;
+
+/*
  * Methods used internally by this class to power the methods above:
  */
 - (long) firstOccurrenceOfCharacter:(unichar)character ignoringQuotedStrings:(BOOL)ignoreQuotedStrings;
diff --git a/Source/SPSQLParser.m b/Source/SPSQLParser.m
index ce7cd07e..281c1b3d 100644
--- a/Source/SPSQLParser.m
+++ b/Source/SPSQLParser.m
@@ -24,6 +24,17 @@
 //  More info at <http://code.google.com/p/sequel-pro/>
 
 #import "SPSQLParser.h"
+#import "RegexKitLite.h"
+
+/*
+ * Include all the extern variables and prototypes required for flex (used for syntax highlighting)
+ */
+#import "SPSQLTokenizer.h"
+extern int tolex();
+extern int yyuoffset, yyuleng;
+typedef struct to_buffer_state *TO_BUFFER_STATE;
+void to_switch_to_buffer(TO_BUFFER_STATE);
+TO_BUFFER_STATE to_scan_string (const char *);
 
 /*
  * Please see the header files for a general description of the purpose of this class,
@@ -426,6 +437,77 @@
 	return resultsArray;
 }
 
+/*
+ * As splitStringByCharacter: ..., but allows control over quoting
+ * - it recognises CREATE ... BEGIN ... END statements
+ * - it can detect a SINGLE SQL statement in between
+ *     delimiter foo ... foo delimiter ; 
+ *     ['delimiter ;' MUST be given!]
+ * - it returns an array of ranges (as NSString "{loc, length}").
+ * FromPosition: is needed if a subrange is passed to sync the ranges 
+ * according to the CQ textView ones.
+ */
+- (NSArray *) splitStringIntoRangesOfSQLQueries
+{
+	return [self splitStringIntoRangesOfSQLQueriesFromPosition:0];
+}
+- (NSArray *) splitStringIntoRangesOfSQLQueriesFromPosition:(long)position
+{
+	NSMutableArray *resultsArray = [NSMutableArray array];
+
+	//initialise flex
+	yyuoffset = 0; yyuleng = 0;
+	to_switch_to_buffer(to_scan_string([string UTF8String]));
+
+	unsigned long token;
+	unsigned long lastFoundToken = 0;
+	unsigned long delimLength = 0;
+	unsigned long commentStart = 0;
+	unsigned long commentLength = 0;
+	
+	NSString *delimString;
+
+	//now loop through all queries
+	while (token=tolex()){
+		switch (token) {
+			case SP_SQL_TOKEN_SEMICOLON:
+				[resultsArray addObject:[NSString stringWithFormat:@"{%d,%d}", lastFoundToken+position, yyuoffset-lastFoundToken]];
+				break;
+			case SP_SQL_TOKEN_SINGLE_LINE_COMMENT:
+				commentStart = yyuoffset+position;
+				commentLength = yyuleng;
+				break;
+			case SP_SQL_TOKEN_DELIM_END:
+				[resultsArray addObject:[NSString stringWithFormat:@"{%d,%d}", lastFoundToken+position, yyuoffset-lastFoundToken-delimLength]];
+				delimLength = 0;
+				delimString = nil;
+				break;
+			case SP_SQL_TOKEN_DELIM_VALUE:
+				delimString = [string substringWithRange:NSMakeRange(yyuoffset,yyuleng)];
+				// NSLog(@"del: %@", delimString);
+				delimLength = yyuleng;
+				break;
+			case SP_SQL_TOKEN_COMPOUND_END:
+				[resultsArray addObject:[NSString stringWithFormat:@"{%d,%d}", lastFoundToken+position, yyuoffset+yyuleng-lastFoundToken]];
+				break;
+			default:
+				continue;
+		}
+		if(token<SP_SQL_TOKEN_IGNORE)
+		{
+			lastFoundToken = yyuoffset+yyuleng;
+			// ignore sinlge comment lines at the very beginning of a query
+			if(commentStart == lastFoundToken)
+				lastFoundToken += commentLength;
+		}
+	}
+
+	// add the last text chunk as query
+	if(lastFoundToken+1<[self length])
+		[resultsArray addObject:[NSString stringWithFormat:@"{%d,%d}", lastFoundToken+position, [self length]-lastFoundToken-delimLength]];
+
+	return resultsArray;
+}
 
 /*
  * A method intended for use by the functions above.
diff --git a/Source/SPSQLTokenizer.h b/Source/SPSQLTokenizer.h
index 90387acb..9a6fbc96 100644
--- a/Source/SPSQLTokenizer.h
+++ b/Source/SPSQLTokenizer.h
@@ -26,4 +26,5 @@
 #define SP_SQL_TOKEN_DELIM_END            2
 #define SP_SQL_TOKEN_SEMICOLON            3
 #define SP_SQL_TOKEN_COMPOUND_END         4
-#define SP_SQL_TOKEN_IGNORE               5
+#define SP_SQL_TOKEN_SINGLE_LINE_COMMENT  5
+#define SP_SQL_TOKEN_IGNORE               6
diff --git a/Source/SPSQLTokenizer.l b/Source/SPSQLTokenizer.l
index 756e9960..61fb3b8a 100644
--- a/Source/SPSQLTokenizer.l
+++ b/Source/SPSQLTokenizer.l
@@ -25,6 +25,7 @@
 //  More info at <http://code.google.com/p/sequel-pro/>
 
 #import "SPSQLTokenizer.h"
+
 int utf8strlenfortoken(const char * _s);
 int yyuoffset, yyuleng;
 
@@ -32,10 +33,13 @@ int yyuoffset, yyuleng;
 
 //keep track of the current utf-8 character (not byte) offset and token length
 #define YY_USER_ACTION { yyuoffset += yyuleng; yyuleng = utf8strlenfortoken(yytext); }
+//ignore the output of unmatched characters
+#define ECHO {}
 %}
 %option prefix="to"
 %option noyywrap
 %option case-insensitive
+%option nostdinit
 
 s			[ \t\n\r]
 dkey		"delimiter"
@@ -45,34 +49,33 @@ compstart	"begin"{s}
 compend		{s}"end"
 %x comment
 %x delim
-%x delimbody
+%x delimend
 %x comp
 %x compbody
 
 %%
 
-\"([^"\\]|\\(.|[\n\r]))*\"?			{ return SP_SQL_TOKEN_IGNORE; }
-'([^'\\]|\\(.|[\n\r]))*'?			{ return SP_SQL_TOKEN_IGNORE; }
-`[^`]*`?							{ return SP_SQL_TOKEN_IGNORE; }
+\"([^"\\]|\\(.|[\n\r]))*\"?			{ ; }
+'([^'\\]|\\(.|[\n\r]))*'?			{ ; }
+`[^`]*`?							{ ; }
 
-"/*"								{ BEGIN(comment); return SP_SQL_TOKEN_IGNORE; }
-<comment>[^*]* 						{ return SP_SQL_TOKEN_IGNORE; }
-<comment>"*"+						{ return SP_SQL_TOKEN_IGNORE; }
-<comment>"*"+"/" 					{ BEGIN(INITIAL); return SP_SQL_TOKEN_IGNORE; }
+"/*"								{ BEGIN(comment); }
+<comment>[^*]* 						{ ; }
+<comment>"*"+						{ ; }
+<comment>"*"+"/" 					{ BEGIN(INITIAL); }
 #[^\n\r]*(\n|\r)?			|
---[ \t][^\n\r]*(\n|\r)?				{ return SP_SQL_TOKEN_IGNORE; }
+--[ \t][^\n\r]*(\n|\r)?				{ return SP_SQL_TOKEN_SINGLE_LINE_COMMENT; }
 
-{s}+								{ return SP_SQL_TOKEN_IGNORE; }
+{s}+								{ ; }
 
-{s}*{dkey}{s}+						{ BEGIN(delim); return SP_SQL_TOKEN_IGNORE; }
-<delim>{dval}+						{ BEGIN(delimbody); return SP_SQL_TOKEN_DELIM_VALUE; }
-<delimbody>{s}+{dkey}{s}+{scol}{s}*	{ BEGIN(INITIAL); return SP_SQL_TOKEN_DELIM_END; }
-{compstart}							{ BEGIN(comp); return SP_SQL_TOKEN_IGNORE; }
-<comp>{dval}+						{ BEGIN(compbody); return SP_SQL_TOKEN_IGNORE; }
+{s}*{dkey}{s}+						{ BEGIN(delim); }
+<delim>{dval}+						{ BEGIN(delimend); return SP_SQL_TOKEN_DELIM_VALUE; }
+<delimend>{s}+{dkey}{s}+{scol}{s}*	{ BEGIN(INITIAL); return SP_SQL_TOKEN_DELIM_END; }
+{compstart}							{ BEGIN(comp); }
+<comp>{dval}+						{ BEGIN(compbody); }
 <compbody>{compend}{s}*{scol}		{ BEGIN(INITIAL); return SP_SQL_TOKEN_COMPOUND_END; }
 
 {scol}{s}*							{ return SP_SQL_TOKEN_SEMICOLON; }
-[.\r\n]+							{ return SP_SQL_TOKEN_IGNORE; }
 
 
 <<EOF>>   						{