internal string GetCommandTextForBatching()
{
if (batchableCommandText == null)
{
// if the command starts with insert and is "simple" enough, then
// we can use the multi-value form of insert
if (String.Compare(CommandText.Substring(0, 6), "INSERT", true) == 0)
{
MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection);
string sql_mode = cmd.ExecuteScalar().ToString().ToLower(CultureInfo.InvariantCulture);
SqlTokenizer tokenizer = new SqlTokenizer(CommandText);
tokenizer.AnsiQuotes = sql_mode.IndexOf("ansi_quotes") != -1;
tokenizer.BackslashEscapes = sql_mode.IndexOf("no_backslash_escapes") == -1;
string token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
while (token != null)
{
if (token.ToLower(CultureInfo.InvariantCulture) == "values" &&
!tokenizer.Quoted)
{
token = tokenizer.NextToken();
Debug.Assert(token == "(");
while (token != null && token != ")")
{
batchableCommandText += token;
token = tokenizer.NextToken();
}
if (token != null)
batchableCommandText += token;
token = tokenizer.NextToken();
if (token != null && (token == "," ||
token.ToLower(CultureInfo.InvariantCulture) == "on"))
{
batchableCommandText = null;
break;
}
}
token = tokenizer.NextToken();
}
}
if (batchableCommandText == null)
batchableCommandText = CommandText;
}
return batchableCommandText;
}
private void ParseProcedureBody(DataTable parametersTable, string body,
DataRow row, string nameToRestrict)
{
ArrayList modes = new ArrayList(new string[3] { "IN", "OUT", "INOUT" });
string sqlMode = row["SQL_MODE"].ToString();
int pos = 1;
SqlTokenizer tokenizer = new SqlTokenizer(body);
tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = tokenizer.NextToken();
// this block will scan for the opening paren while also determining
// if this routine is a function. If so, then we need to add a
// parameter row for the return parameter since it is ordinal position
// 0 and should appear first.
while (token != "(")
{
if (String.Compare(token, "FUNCTION", true) == 0 &&
nameToRestrict == null)
{
parametersTable.Rows.Add(parametersTable.NewRow());
InitParameterRow(row, parametersTable.Rows[0]);
}
token = tokenizer.NextToken();
}
token = tokenizer.NextToken(); // now move to the next token past the (
while (token != ")")
{
DataRow parmRow = parametersTable.NewRow();
InitParameterRow(row, parmRow);
parmRow["ORDINAL_POSITION"] = pos++;
// handle mode and name for the parameter
string mode = token.ToUpper(CultureInfo.InvariantCulture);
if (!tokenizer.Quoted && modes.Contains(mode))
{
parmRow["PARAMETER_MODE"] = mode;
token = tokenizer.NextToken();
}
parmRow["PARAMETER_NAME"] = token;
// now parse data type
token = ParseDataType(parmRow, tokenizer);
if (token == ",")
token = tokenizer.NextToken();
// now determine if we should include this row after all
// we need to parse it before this check so we are correctly
// positioned for the next parameter
if (nameToRestrict == null ||
String.Compare(parmRow["PARAMETER_NAME"].ToString(), nameToRestrict, true) == 0)
parametersTable.Rows.Add(parmRow);
}
// now parse out the return parameter if there is one.
token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
if (String.Compare(token, "returns", true) == 0)
{
DataRow parameterRow = parametersTable.Rows[0];
parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
ParseDataType(parameterRow, tokenizer);
}
}
/// <summary>
/// Parses out the elements of a procedure parameter data type.
/// </summary>
private string ParseDataType(DataRow row, SqlTokenizer tokenizer)
{
StringBuilder dtd = new StringBuilder(
tokenizer.NextToken().ToUpper(CultureInfo.InvariantCulture));
row["DATA_TYPE"] = dtd.ToString();
string type = row["DATA_TYPE"].ToString();
string token = tokenizer.NextToken();
if (tokenizer.IsSize)
{
dtd.AppendFormat(CultureInfo.InvariantCulture, "({0})", token);
if (type != "ENUM" && type != "SET")
ParseDataTypeSize(row, token);
token = tokenizer.NextToken();
}
else
dtd.Append(GetDataTypeDefaults(type, row));
while (token != ")" && token != "," && String.Compare(token, "begin", true) != 0)
{
if (String.Compare(token, "CHARACTER", true) == 0 ||
String.Compare(token, "BINARY", true) == 0)
{ } // we don't need to do anything with this
else if (String.Compare(token, "SET", true) == 0 ||
String.Compare(token, "CHARSET", true) == 0)
row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
else if (String.Compare(token, "ASCII", true) == 0)
row["CHARACTER_SET_NAME"] = "latin1";
else if (String.Compare(token, "UNICODE", true) == 0)
row["CHARACTER_SET_NAME"] = "ucs2";
else if (String.Compare(token, "COLLATE", true) == 0)
row["COLLATION_NAME"] = tokenizer.NextToken();
else
dtd.AppendFormat(CultureInfo.InvariantCulture, " {0}", token);
token = tokenizer.NextToken();
}
if (dtd.Length > 0)
row["DTD_IDENTIFIER"] = dtd.ToString();
// now default the collation if one wasn't given
if (row["COLLATION_NAME"].ToString().Length == 0)
row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
row["CHARACTER_SET_NAME"].ToString(), connection);
// now set the octet length
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
row["CHARACTER_OCTET_LENGTH"] =
CharSetMap.GetMaxLength(row["CHARACTER_SET_NAME"].ToString(), connection) *
(int)row["CHARACTER_MAXIMUM_LENGTH"];
return token;
}
/// <summary>
/// Attempts to parse a given token as a type attribute.
/// </summary>
/// <returns>True if the token was recognized as a type attribute,
/// false otherwise.</returns>
private static bool SetParameterAttribute(DataRow row, string token, bool isSize,
SqlTokenizer tokenizer)
{
string lcDataType = row["DATA_TYPE"].ToString().ToLower(CultureInfo.InvariantCulture);
if (isSize)
{
// if the data type if set or enum, then nothing to do.
if (lcDataType == "enum" || lcDataType == "set") return true;
string[] sizeParts = token.Split(new char[] { ',' });
if (MetaData.IsNumericType(lcDataType))
row["NUMERIC_PRECISION"] = Int32.Parse(sizeParts[0]);
else
row["CHARACTER_OCTET_LENGTH"] = Int32.Parse(sizeParts[0]);
if (sizeParts.Length == 2)
row["NUMERIC_SCALE"] = Int32.Parse(sizeParts[1]);
return true;
}
else
{
string lowerToken = token.ToLower(CultureInfo.InvariantCulture);
switch (lowerToken)
{
case "unsigned":
case "zerofill":
row["FLAGS"] = String.Format("{0} {1}", row["FLAGS"], token);
return true;
case "character":
case "charset":
if (lowerToken == "character")
{
string set = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
Debug.Assert(set == "set");
}
row["CHARACTER_SET"] = tokenizer.NextToken();
return true;
case "ascii":
row["CHARACTER_SET"] = "latin1";
return true;
case "unicode":
row["CHARACTER_SET"] = "ucs2";
return true;
case "binary":
return true;
}
}
return false;
}
private string GetProcedureParameterLine(DataRow isRow)
{
string sql = "SHOW CREATE {0} `{1}`.`{2}`";
sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"],
isRow["ROUTINE_NAME"]);
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
// if we are not the owner of this proc or have permissions
// then we will get null for the body
if (reader.IsDBNull(2)) return null;
string sql_mode = reader.GetString(1);
string body = reader.GetString(2);
SqlTokenizer tokenizer = new SqlTokenizer(body);
tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = tokenizer.NextToken();
while (token != "(")
token = tokenizer.NextToken();
int start = tokenizer.Index + 1;
token = tokenizer.NextToken();
while (token != ")" || tokenizer.Quoted)
{
token = tokenizer.NextToken();
// if we see another ( and we are not quoted then we
// are in a size element and we need to look for the closing paren
if (token == "(" && !tokenizer.Quoted)
{
while (token != ")" || tokenizer.Quoted)
token = tokenizer.NextToken();
token = tokenizer.NextToken();
}
}
return body.Substring(start, tokenizer.Index - start);
}
}
private void ParseProcedureBody(DataTable parametersTable, string body,
DataRow row, string nameToRestrict)
{
string sqlMode = row["SQL_MODE"].ToString();
int pos = 1;
SqlTokenizer tokenizer = new SqlTokenizer(body);
tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = tokenizer.NextToken();
// look for the opening paren
while (token != "(")
token = tokenizer.NextToken();
while (token != ")")
{
token = tokenizer.NextToken();
if (token == ")") break; /* handle the case where there are no parms */
DataRow parmRow = parametersTable.NewRow();
InitParameterRow(row, parmRow);
parmRow["ORDINAL_POSITION"] = pos++;
string parameterName = token;
if (!tokenizer.Quoted)
{
string mode = null;
string lowerToken = token.ToLower(CultureInfo.InvariantCulture);
if (lowerToken == "in")
mode = "IN";
else if (lowerToken == "inout")
mode = "INOUT";
else if (lowerToken == "out")
mode = "OUT";
if (mode != null)
{
parmRow["PARAMETER_MODE"] = mode;
parameterName = tokenizer.NextToken();
}
}
parmRow["PARAMETER_NAME"] = String.Format("{0}{1}",
connection.ParameterMarker, parameterName);
token = ParseDataType(parmRow, tokenizer);
if (nameToRestrict == null ||
parmRow["PARAMETER_NAME"].ToString().ToLower() ==
nameToRestrict)
parametersTable.Rows.Add(parmRow);
}
// now parse out the return parameter if there is one.
token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
if (token == "returns")
{
DataRow parameterRow = parametersTable.NewRow();
InitParameterRow(row, parameterRow);
parameterRow["PARAMETER_NAME"] = String.Format("{0}RETURN_VALUE",
connection.ParameterMarker);
parameterRow["IS_RESULT"] = "YES";
ParseDataType(parameterRow, tokenizer);
parametersTable.Rows.Add(parameterRow);
}
}
/// <summary>
/// Parses out the elements of a procedure parameter data type.
/// </summary>
private static string ParseDataType(DataRow row, SqlTokenizer tokenizer)
{
row["DATA_TYPE"] = tokenizer.NextToken().ToUpper(CultureInfo.InvariantCulture);
string token = tokenizer.NextToken();
while (SetParameterAttribute(row, token, tokenizer.IsSize, tokenizer))
token = tokenizer.NextToken();
return token;
}
private List<ScriptStatement> BreakIntoStatements(bool ansiQuotes, bool noBackslashEscapes)
{
int startPos = 0;
List<ScriptStatement> statements = new List<ScriptStatement>();
List<int> lineNumbers = BreakScriptIntoLines();
SqlTokenizer tokenizer = new SqlTokenizer(query);
tokenizer.AnsiQuotes = ansiQuotes;
tokenizer.BackslashEscapes = !noBackslashEscapes;
string token = tokenizer.NextToken();
while (token != null)
{
if (!tokenizer.Quoted &&
!tokenizer.IsSize)
{
int delimiterPos = token.IndexOf(Delimiter);
if (delimiterPos != -1)
{
int endPos = tokenizer.Index - token.Length + delimiterPos;
if (tokenizer.Index == query.Length-1)
endPos++;
string currentQuery = query.Substring(startPos, endPos-startPos);
ScriptStatement statement = new ScriptStatement();
statement.text = currentQuery.Trim();
statement.line = FindLineNumber(startPos, lineNumbers);
statement.position = startPos - lineNumbers[statement.line];
statements.Add(statement);
startPos = endPos + delimiter.Length;
}
}
token = tokenizer.NextToken();
}
// now clean up the last statement
if (tokenizer.Index > startPos)
{
string sqlLeftOver = query.Substring(startPos).Trim();
if (!String.IsNullOrEmpty(sqlLeftOver))
{
ScriptStatement statement = new ScriptStatement();
statement.text = sqlLeftOver;
statement.line = FindLineNumber(startPos, lineNumbers);
statement.position = startPos - lineNumbers[statement.line];
statements.Add(statement);
}
}
return statements;
}
private ArrayList ParseColumns(SqlTokenizer tokenizer)
{
ArrayList sc = new ArrayList();
string token = tokenizer.NextToken();
while (token != ")")
{
if (token != ",")
sc.Add(token);
token = tokenizer.NextToken();
}
return sc;
}
private void ParseConstraint(DataTable fkTable, DataRow table,
SqlTokenizer tokenizer, bool includeColumns)
{
string name = tokenizer.NextToken();
DataRow row = fkTable.NewRow();
// make sure this constraint is a FK
string token = tokenizer.NextToken();
if (token != "foreign" || tokenizer.Quoted)
return;
tokenizer.NextToken(); // read off the 'KEY' symbol
tokenizer.NextToken(); // read off the '(' symbol
row["CONSTRAINT_CATALOG"] = table["TABLE_CATALOG"];
row["CONSTRAINT_SCHEMA"] = table["TABLE_SCHEMA"];
row["TABLE_CATALOG"] = table["TABLE_CATALOG"];
row["TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
row["TABLE_NAME"] = table["TABLE_NAME"];
row["REFERENCED_TABLE_CATALOG"] = null;
row["CONSTRAINT_NAME"] = name;
ArrayList srcColumns = includeColumns ? ParseColumns(tokenizer) : null;
// now look for the references section
while (token != "references" || tokenizer.Quoted)
token = tokenizer.NextToken();
string target1 = tokenizer.NextToken();
string target2 = tokenizer.NextToken();
if (target2.StartsWith("."))
{
row["REFERENCED_TABLE_SCHEMA"] = target1;
row["REFERENCED_TABLE_NAME"] = target2.Substring(1);
tokenizer.NextToken(); // read off the '('
}
else
{
row["REFERENCED_TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
row["REFERENCED_TABLE_NAME"] = target1;
}
// if we are supposed to include columns, read the target columns
ArrayList targetColumns = includeColumns ? ParseColumns(tokenizer) : null;
if (includeColumns)
ProcessColumns(fkTable, row, srcColumns, targetColumns);
else
fkTable.Rows.Add(row);
}
/// <summary>
/// GetForeignKeysOnTable retrieves the foreign keys on the given table.
/// Since MySQL supports foreign keys on versions prior to 5.0, we can't use
/// information schema. MySQL also does not include any type of SHOW command
/// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing
/// the output.
/// </summary>
/// <param name="fkTable">The table to store the key info in.</param>
/// <param name="tableToParse">The table to get the foeign key info for.</param>
/// <param name="filterName">Only get foreign keys that match this name.</param>
/// <param name="includeColumns">Should column information be included in the table.</param>
private void GetForeignKeysOnTable(DataTable fkTable, DataRow tableToParse,
string filterName, bool includeColumns)
{
string sqlMode = GetSqlMode();
if (filterName != null)
filterName = filterName.ToLower(CultureInfo.InvariantCulture);
string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`",
tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]);
string lowerBody = null, body = null;
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
body = reader.GetString(1);
lowerBody = body.ToLower(CultureInfo.InvariantCulture);
}
SqlTokenizer tokenizer = new SqlTokenizer(lowerBody);
tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
while (true)
{
string token = tokenizer.NextToken();
// look for a starting contraint
while (token != null && (token != "constraint" || tokenizer.Quoted))
token = tokenizer.NextToken();
if (token == null) break;
ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns);
}
}
/// <summary>
/// THis code is not used yet but will likely be used in the future.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
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;
}