From: Date: May 27 2008 11:01pm
Subject: Connector/NET commit: r1303 - in trunk: . MySql.Data/Provider/Source MySql.Data/Tests/Source
List-Archive: http://lists.mysql.com/commits/47111
X-Bug: 36836
Message-Id: <200805272101.m4RL1BJi024961@bk-internal.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Modified:
trunk/CHANGES
trunk/MySql.Data/Provider/Source/Statement.cs
trunk/MySql.Data/Tests/Source/Syntax.cs
Log:
Improved sql tokenizing speed greatly. Patch submitted by Maxim Mass (bug #36836)
Also added some tests for our tokenizer
Modified: trunk/CHANGES
===================================================================
--- trunk/CHANGES 2008-05-23 16:04:48 UTC (rev 1302)
+++ trunk/CHANGES 2008-05-27 21:01:08 UTC (rev 1303)
@@ -1,5 +1,6 @@
Version 5.3
- Massive speedups
+- Improved sql tokenizing speed greatly. Patch submitted by Maxim Mass (bug #36836)
Version 5.2.2 -
- Fixed profile provider that would throw an exception if you were updating
Modified: trunk/MySql.Data/Provider/Source/Statement.cs
===================================================================
--- trunk/MySql.Data/Provider/Source/Statement.cs 2008-05-23 16:04:48 UTC (rev 1302)
+++ trunk/MySql.Data/Provider/Source/Statement.cs 2008-05-27 21:01:08 UTC (rev 1303)
@@ -244,112 +244,48 @@
return true;
}
- ///
- /// THis code is not used yet but will likely be used in the future.
- ///
- ///
- ///
- private ArrayList TokenizeSql2(string sql)
- {
- ArrayList sqlChunks = new ArrayList();
- StringBuilder currentChunk = new StringBuilder();
- bool batch = Connection.Settings.AllowBatch & Driver.SupportsBatch;
-
- int lastPos = 0;
- SqlTokenizer tokenizer = new SqlTokenizer(sql);
- string sql_mode = Connection.driver.Property("sql_mode");
- if (sql_mode != null)
- {
- sql_mode = sql_mode.ToString().ToLower();
- tokenizer.AnsiQuotes = sql_mode.IndexOf("ansi_quotes") != -1;
- tokenizer.BackslashEscapes = sql_mode.IndexOf("no_backslash_escapes") == -1;
- }
-
- string token = tokenizer.NextToken();
- while (token != null)
- {
- if (token == ";" && !batch)
- {
- sqlChunks.Add(currentChunk.ToString());
- currentChunk.Remove(0, currentChunk.Length);
- }
-
- else if (token.Length >= 2 &&
- ((token[0] == '@' && token[1] != '@') ||
- token[0] == '?'))
- {
- sqlChunks.Add(currentChunk.ToString());
- currentChunk.Remove(0, currentChunk.Length);
- }
- else
- {
- currentChunk.Append(sql.Substring(lastPos, tokenizer.Index - lastPos+1));
- lastPos = tokenizer.Index;
- }
- token = tokenizer.NextToken();
- }
- if (currentChunk.Length > 0)
- sqlChunks.Add(currentChunk.ToString());
- return sqlChunks;
- }
-
- ///
- /// Breaks the given SQL up into 'tokens' that are easier to output
- /// into another form (bytes, preparedText, etc).
- ///
- /// SQL to be tokenized
- /// Array of tokens
- /// The SQL is tokenized at parameter markers ('?') and at
- /// (';') sql end markers if the server doesn't support batching.
- ///
public ArrayList TokenizeSql(string sql)
{
- bool batch = Connection.Settings.AllowBatch & Driver.SupportsBatch;
+ bool batch = Connection.Settings.AllowBatch && Driver.SupportsBatch;
char delim = Char.MinValue;
- StringBuilder sqlPart = new StringBuilder();
bool escaped = false;
ArrayList tokens = new ArrayList();
- sql = sql.TrimStart(';').TrimEnd(';');
- char c = Char.MinValue;
- char lastChar;
+ sql = sql.Trim(';');
+ int startIndex = 0;
for (int i = 0; i < sql.Length; i++)
{
- lastChar = c;
- c = sql[i];
+ char c = sql[i];
if (escaped)
- escaped = !escaped;
+ escaped = false;
else if (c == delim)
delim = Char.MinValue;
else if (c == ';' && !escaped && delim == Char.MinValue && !batch)
{
- tokens.Add(sqlPart.ToString());
+ tokens.Add(sql.Substring(startIndex, i - startIndex));
tokens.Add(";");
- sqlPart.Remove(0, sqlPart.Length);
+ startIndex = i + 1;
continue;
}
else if ((c == '\'' || c == '\"' || c == '`') && !escaped && delim == Char.MinValue)
delim = c;
else if (c == '\\')
escaped = !escaped;
- else if (sqlPart.Length == 1 && sqlPart[0] == '@' && c == '@') { }
- else if (sqlPart.Length > 0 && sqlPart[0] == '?' && c == '@') { }
else if ((c == '@' || c == '?') && delim == Char.MinValue && !escaped)
{
- tokens.Add(sqlPart.ToString());
- sqlPart.Remove(0, sqlPart.Length);
+ tokens.Add(sql.Substring(startIndex, i - startIndex));
+ startIndex = i;
}
- else if (sqlPart.Length > 0 && (sqlPart[0] == '@' || sqlPart[0] == '?') &&
+ else if (i > startIndex && (sql[startIndex] == '@' || sql[startIndex] == '?') &&
!Char.IsLetterOrDigit(c) && c != '_' && c != '.' && c != '$')
{
- tokens.Add(sqlPart.ToString());
- sqlPart.Remove(0, sqlPart.Length);
+ tokens.Add(sql.Substring(startIndex, i - startIndex));
+ startIndex = i;
}
-
- sqlPart.Append(c);
}
- tokens.Add(sqlPart.ToString());
+ tokens.Add(sql.Substring(startIndex, sql.Length - startIndex));
return tokens;
}
+
}
}
Modified: trunk/MySql.Data/Tests/Source/Syntax.cs
===================================================================
--- trunk/MySql.Data/Tests/Source/Syntax.cs 2008-05-23 16:04:48 UTC (rev 1302)
+++ trunk/MySql.Data/Tests/Source/Syntax.cs 2008-05-27 21:01:08 UTC (rev 1303)
@@ -22,6 +22,7 @@
using System.Data;
using System.IO;
using NUnit.Framework;
+using System.Collections;
namespace MySql.Data.MySqlClient.Tests
{
@@ -423,5 +424,43 @@
Assert.IsTrue(row["Command"].GetType().Name == "String");
}
}
- }
+
+ private ArrayList GetTokenizerOutput(string sql)
+ {
+ MySqlCommand cmd = new MySqlCommand("", conn);
+
+ object o = typeof(MySqlConnection).Assembly.CreateInstance("MySql.Data.MySqlClient.PreparableStatement",
+ false, System.Reflection.BindingFlags.CreateInstance, null, new
+ object[] { cmd, "" }, null, null);
+ ArrayList tokens = (ArrayList)o.GetType().InvokeMember("TokenizeSql",
+ System.Reflection.BindingFlags.InvokeMethod,
+ null, o, new object[] { sql });
+ return tokens;
+ }
+
+ [Test]
+ public void TestTokenizer()
+ {
+ ArrayList tokens = GetTokenizerOutput("SELECT * FROM Test");
+ Assert.AreEqual(1, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * FROM Test WHERE id=@id");
+ Assert.AreEqual(2, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * /* this is a comment @test */ FROM Test WHERE id=@id");
+ Assert.AreEqual(2, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * /* this is a comment @test */ FROM Test WHERE id=@@id");
+ Assert.AreEqual(1, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * /* this is a comment @test */ FROM Test WHERE id=?id");
+ Assert.AreEqual(2, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * /* this is a comment @test */ FROM Test WHERE id=?id AND id <> @@id");
+ Assert.AreEqual(3, tokens.Count);
+
+ tokens = GetTokenizerOutput("SELECT * /* this is a comment @test */ FROM Test WHERE id='?id' AND id <> @@id");
+ Assert.AreEqual(1, tokens.Count);
+ }
+ }
}