/// <inheritdoc/>
public static MySqlDbType NameToType(string typeName, bool unsigned, MySqlConnection connection)
{
switch (StringUtility.ToUpperInvariant(typeName))
{
case "CHAR": return(MySqlDbType.String);
case "VARCHAR": return(MySqlDbType.VarChar);
case "DATE": return(MySqlDbType.Date);
case "DATETIME": return(MySqlDbType.DateTime);
case "NUMERIC":
case "DECIMAL":
case "DEC":
case "FIXED":
return(MySqlDbType.NewDecimal);
case "YEAR":
return(MySqlDbType.Year);
case "TIME":
return(MySqlDbType.Time);
case "TIMESTAMP":
return(MySqlDbType.Timestamp);
case "SET": return(MySqlDbType.Set);
case "ENUM": return(MySqlDbType.Enum);
case "BIT": return(MySqlDbType.Bit);
case "TINYINT":
return(unsigned ? MySqlDbType.UByte : MySqlDbType.Byte);
case "BOOL":
case "BOOLEAN":
return(MySqlDbType.Byte);
case "SMALLINT":
return(unsigned ? MySqlDbType.UInt16 : MySqlDbType.Int16);
case "MEDIUMINT":
return(unsigned ? MySqlDbType.UInt24 : MySqlDbType.Int24);
case "INT":
case "INTEGER":
return(unsigned ? MySqlDbType.UInt32 : MySqlDbType.Int32);
case "SERIAL":
return(MySqlDbType.UInt64);
case "BIGINT":
return(unsigned ? MySqlDbType.UInt64 : MySqlDbType.Int64);
case "FLOAT": return(MySqlDbType.Float);
case "DOUBLE": return(MySqlDbType.Double);
case "REAL":
return(MySqlDbType.Double);
case "TEXT":
return(MySqlDbType.Text);
case "BLOB":
return(MySqlDbType.Blob);
case "LONGBLOB":
return(MySqlDbType.LongBlob);
case "LONGTEXT":
return(MySqlDbType.LongText);
case "MEDIUMBLOB":
return(MySqlDbType.MediumBlob);
case "MEDIUMTEXT":
return(MySqlDbType.MediumText);
case "TINYBLOB":
return(MySqlDbType.TinyBlob);
case "TINYTEXT":
return(MySqlDbType.TinyText);
case "BINARY":
return(MySqlDbType.Binary);
case "VARBINARY":
return(MySqlDbType.VarBinary);
}
throw new Exception("Unhandled type encountered");
}
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", StringComparison.OrdinalIgnoreCase) == 0)
{
MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection);
string sql_mode = StringUtility.ToUpperInvariant(cmd.ExecuteScalar().ToString());
MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText);
tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = StringUtility.ToLowerInvariant(tokenizer.NextToken());
while (token != null)
{
if (StringUtility.ToUpperInvariant(token) == "VALUES" &&
!tokenizer.Quoted)
{
token = tokenizer.NextToken();
Debug.Assert(token == "(");
// find matching right paren, and ensure that parens
// are balanced.
int openParenCount = 1;
while (token != null)
{
BatchableCommandText += token;
token = tokenizer.NextToken();
if (token == "(")
{
openParenCount++;
}
else if (token == ")")
{
openParenCount--;
}
if (openParenCount == 0)
{
break;
}
}
if (token != null)
{
BatchableCommandText += token;
}
token = tokenizer.NextToken();
if (token != null && (token == "," ||
StringUtility.ToUpperInvariant(token) == "ON"))
{
BatchableCommandText = null;
break;
}
}
token = tokenizer.NextToken();
}
}
// Otherwise use the command verbatim
else
{
BatchableCommandText = CommandText;
}
}
return(BatchableCommandText);
}
/// <summary>
/// Executes this instance.
/// </summary>
/// <returns>The number of statements executed as part of the script.</returns>
public int Execute()
{
bool openedConnection = false;
if (Connection == null)
{
throw new InvalidOperationException(Resources.ConnectionNotSet);
}
if (IsNullOrEmpty(Query))
{
return(0);
}
// next we open up the connetion if it is not already open
if (Connection.State != ConnectionState.Open)
{
openedConnection = true;
Connection.Open();
}
// since we don't allow setting of parameters on a script we can
// therefore safely allow the use of user variables. no one should be using
// this connection while we are using it so we can temporarily tell it
// to allow the use of user variables
bool allowUserVars = Connection.Settings.AllowUserVariables;
Connection.Settings.AllowUserVariables = true;
try
{
string mode = Connection.driver.Property("sql_mode");
mode = StringUtility.ToUpperInvariant(mode);
bool ansiQuotes = mode.IndexOf("ANSI_QUOTES") != -1;
bool noBackslashEscapes = mode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
// first we break the query up into smaller queries
List <ScriptStatement> statements = BreakIntoStatements(ansiQuotes, noBackslashEscapes);
int count = 0;
MySqlCommand cmd = new MySqlCommand(null, Connection);
foreach (ScriptStatement statement in statements.Where(statement => !IsNullOrEmpty(statement.text)))
{
cmd.CommandText = statement.text;
try
{
cmd.ExecuteNonQuery();
count++;
OnQueryExecuted(statement);
}
catch (Exception ex)
{
if (Error == null)
{
throw;
}
if (!OnScriptError(ex))
{
break;
}
}
}
OnScriptCompleted();
return(count);
}
finally
{
Connection.Settings.AllowUserVariables = allowUserVars;
if (openedConnection)
{
Connection.Close();
}
}
}
public int Execute()
{
bool flag = false;
if (this.connection == null)
{
throw new InvalidOperationException(Resources.ConnectionNotSet);
}
if (this.query == null || this.query.Length == 0)
{
return(0);
}
if (this.connection.State != ConnectionState.Open)
{
flag = true;
this.connection.Open();
}
bool allowUserVariables = this.connection.Settings.AllowUserVariables;
this.connection.Settings.AllowUserVariables = true;
int result;
try
{
string text = this.connection.driver.Property("sql_mode");
text = StringUtility.ToUpperInvariant(text);
bool ansiQuotes = text.IndexOf("ANSI_QUOTES") != -1;
bool noBackslashEscapes = text.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
List <ScriptStatement> list = this.BreakIntoStatements(ansiQuotes, noBackslashEscapes);
int num = 0;
MySqlCommand mySqlCommand = new MySqlCommand(null, this.connection);
foreach (ScriptStatement current in list)
{
if (!string.IsNullOrEmpty(current.text))
{
mySqlCommand.CommandText = current.text;
try
{
mySqlCommand.ExecuteNonQuery();
num++;
this.OnQueryExecuted(current);
}
catch (Exception ex)
{
if (this.Error == null)
{
throw;
}
if (!this.OnScriptError(ex))
{
break;
}
}
}
}
this.OnScriptCompleted();
result = num;
}
finally
{
this.connection.Settings.AllowUserVariables = allowUserVariables;
if (flag)
{
this.connection.Close();
}
}
return(result);
}
/// <summary>
/// Parses out the elements of a procedure parameter data type.
/// </summary>
private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer)
{
StringBuilder dtd = new StringBuilder(
StringUtility.ToUpperInvariant(tokenizer.NextToken()));
row["DATA_TYPE"] = dtd.ToString();
string type = row["DATA_TYPE"].ToString();
string token = tokenizer.NextToken();
if (token == "(")
{
token = tokenizer.ReadParenthesis();
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", StringComparison.OrdinalIgnoreCase) != 0 &&
String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0)
{
if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 ||
String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0)
{
} // we don't need to do anything with this
else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 ||
String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
}
else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "latin1";
}
else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "ucs2";
}
else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 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 (string.IsNullOrEmpty(( string )row["COLLATION_NAME"]) &&
!string.IsNullOrEmpty(( string )row["CHARACTER_SET_NAME"]))
{
row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
row["CHARACTER_SET_NAME"].ToString(), connection);
}
// now set the octet length
if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
{
if (row["CHARACTER_SET_NAME"] == null)
{
row["CHARACTER_SET_NAME"] = "";
}
row["CHARACTER_OCTET_LENGTH"] =
CharSetMap.GetMaxLength(( string )row["CHARACTER_SET_NAME"], connection) *
(int)row["CHARACTER_MAXIMUM_LENGTH"];
}
return(token);
}
private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body,
MySqlSchemaRow row, string nameToRestrict)
{
List <string> modes = new List <string>(new string[3] {
"IN", "OUT", "INOUT"
});
string sqlMode = row["SQL_MODE"].ToString();
int pos = 1;
MySqlTokenizer tokenizer = new MySqlTokenizer(body);
tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
tokenizer.ReturnComments = false;
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", StringComparison.OrdinalIgnoreCase) == 0 &&
nameToRestrict == null)
{
parametersTable.AddRow();
InitParameterRow(row, parametersTable.Rows[0]);
}
token = tokenizer.NextToken();
}
token = tokenizer.NextToken(); // now move to the next token past the (
while (token != ")")
{
MySqlSchemaRow parmRow = parametersTable.NewRow();
InitParameterRow(row, parmRow);
parmRow["ORDINAL_POSITION"] = pos++;
// handle mode and name for the parameter
string mode = StringUtility.ToUpperInvariant(token);
if (!tokenizer.Quoted && modes.Contains(mode))
{
parmRow["PARAMETER_MODE"] = mode;
token = tokenizer.NextToken();
}
if (tokenizer.Quoted)
{
token = token.Substring(1, token.Length - 2);
}
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, StringComparison.OrdinalIgnoreCase) == 0)
{
parametersTable.Rows.Add(parmRow);
}
}
// now parse out the return parameter if there is one.
token = StringUtility.ToUpperInvariant(tokenizer.NextToken());
if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
{
MySqlSchemaRow parameterRow = parametersTable.Rows[0];
parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
ParseDataType(parameterRow, tokenizer);
}
}
public virtual MySqlSchemaCollection GetProcedures(string[] restrictions)
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedures");
dt.AddColumn("SPECIFIC_NAME", typeof(string));
dt.AddColumn("ROUTINE_CATALOG", typeof(string));
dt.AddColumn("ROUTINE_SCHEMA", typeof(string));
dt.AddColumn("ROUTINE_NAME", typeof(string));
dt.AddColumn("ROUTINE_TYPE", typeof(string));
dt.AddColumn("DTD_IDENTIFIER", typeof(string));
dt.AddColumn("ROUTINE_BODY", typeof(string));
dt.AddColumn("ROUTINE_DEFINITION", typeof(string));
dt.AddColumn("EXTERNAL_NAME", typeof(string));
dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string));
dt.AddColumn("PARAMETER_STYLE", typeof(string));
dt.AddColumn("IS_DETERMINISTIC", typeof(string));
dt.AddColumn("SQL_DATA_ACCESS", typeof(string));
dt.AddColumn("SQL_PATH", typeof(string));
dt.AddColumn("SECURITY_TYPE", typeof(string));
dt.AddColumn("CREATED", typeof(DateTime));
dt.AddColumn("LAST_ALTERED", typeof(DateTime));
dt.AddColumn("SQL_MODE", typeof(string));
dt.AddColumn("ROUTINE_COMMENT", typeof(string));
dt.AddColumn("DEFINER", typeof(string));
StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1");
if (restrictions != null)
{
if (restrictions.Length >= 2 && restrictions[1] != null)
{
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND db LIKE '{0}'", restrictions[1]);
}
if (restrictions.Length >= 3 && restrictions[2] != null)
{
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND name LIKE '{0}'", restrictions[2]);
}
if (restrictions.Length >= 4 && restrictions[3] != null)
{
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND type LIKE '{0}'", restrictions[3]);
}
}
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MySqlSchemaRow row = dt.AddRow();
row["SPECIFIC_NAME"] = reader.GetString("specific_name");
row["ROUTINE_CATALOG"] = DBNull.Value;
row["ROUTINE_SCHEMA"] = reader.GetString("db");
row["ROUTINE_NAME"] = reader.GetString("name");
string routineType = reader.GetString("type");
row["ROUTINE_TYPE"] = routineType;
row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ?
(object)reader.GetString("returns") : DBNull.Value;
row["ROUTINE_BODY"] = "SQL";
row["ROUTINE_DEFINITION"] = reader.GetString("body");
row["EXTERNAL_NAME"] = DBNull.Value;
row["EXTERNAL_LANGUAGE"] = DBNull.Value;
row["PARAMETER_STYLE"] = "SQL";
row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic");
row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access");
row["SQL_PATH"] = DBNull.Value;
row["SECURITY_TYPE"] = reader.GetString("security_type");
row["CREATED"] = reader.GetDateTime("created");
row["LAST_ALTERED"] = reader.GetDateTime("modified");
row["SQL_MODE"] = reader.GetString("sql_mode");
row["ROUTINE_COMMENT"] = reader.GetString("comment");
row["DEFINER"] = reader.GetString("definer");
}
}
return(dt);
}
private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer)
{
StringBuilder stringBuilder = new StringBuilder(StringUtility.ToUpperInvariant(tokenizer.NextToken()));
row["DATA_TYPE"] = stringBuilder.ToString();
string text = row["DATA_TYPE"].ToString();
string text2 = tokenizer.NextToken();
if (text2 == "(")
{
text2 = tokenizer.ReadParenthesis();
stringBuilder.AppendFormat(CultureInfo.InvariantCulture, "{0}", new object[]
{
text2
});
if (text != "ENUM" && text != "SET")
{
ISSchemaProvider.ParseDataTypeSize(row, text2);
}
text2 = tokenizer.NextToken();
}
else
{
stringBuilder.Append(ISSchemaProvider.GetDataTypeDefaults(text, row));
}
while (text2 != ")" && text2 != "," && string.Compare(text2, "begin", StringComparison.OrdinalIgnoreCase) != 0 && string.Compare(text2, "return", StringComparison.OrdinalIgnoreCase) != 0)
{
if (string.Compare(text2, "CHARACTER", StringComparison.OrdinalIgnoreCase) != 0 && string.Compare(text2, "BINARY", StringComparison.OrdinalIgnoreCase) != 0)
{
if (string.Compare(text2, "SET", StringComparison.OrdinalIgnoreCase) == 0 || string.Compare(text2, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
}
else if (string.Compare(text2, "ASCII", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "latin1";
}
else if (string.Compare(text2, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "ucs2";
}
else if (string.Compare(text2, "COLLATE", StringComparison.OrdinalIgnoreCase) == 0)
{
row["COLLATION_NAME"] = tokenizer.NextToken();
}
else
{
stringBuilder.AppendFormat(CultureInfo.InvariantCulture, " {0}", new object[]
{
text2
});
}
}
text2 = tokenizer.NextToken();
}
if (stringBuilder.Length > 0)
{
row["DTD_IDENTIFIER"] = stringBuilder.ToString();
}
if (string.IsNullOrEmpty((string)row["COLLATION_NAME"]) && !string.IsNullOrEmpty((string)row["CHARACTER_SET_NAME"]))
{
row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(row["CHARACTER_SET_NAME"].ToString(), this.connection);
}
if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
{
if (row["CHARACTER_SET_NAME"] == null)
{
row["CHARACTER_SET_NAME"] = "";
}
row["CHARACTER_OCTET_LENGTH"] = CharSetMap.GetMaxLength((string)row["CHARACTER_SET_NAME"], this.connection) * (int)row["CHARACTER_MAXIMUM_LENGTH"];
}
return(text2);
}
private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body, MySqlSchemaRow row, string nameToRestrict)
{
List <string> list = new List <string>(new string[]
{
"IN",
"OUT",
"INOUT"
});
string text = row["SQL_MODE"].ToString();
int num = 1;
MySqlTokenizer mySqlTokenizer = new MySqlTokenizer(body);
mySqlTokenizer.AnsiQuotes = (text.IndexOf("ANSI_QUOTES") != -1);
mySqlTokenizer.BackslashEscapes = (text.IndexOf("NO_BACKSLASH_ESCAPES") == -1);
mySqlTokenizer.ReturnComments = false;
string text2 = mySqlTokenizer.NextToken();
while (text2 != "(")
{
if (string.Compare(text2, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0 && nameToRestrict == null)
{
parametersTable.AddRow();
ISSchemaProvider.InitParameterRow(row, parametersTable.Rows[0]);
}
text2 = mySqlTokenizer.NextToken();
}
text2 = mySqlTokenizer.NextToken();
while (text2 != ")")
{
MySqlSchemaRow mySqlSchemaRow = parametersTable.NewRow();
ISSchemaProvider.InitParameterRow(row, mySqlSchemaRow);
mySqlSchemaRow["ORDINAL_POSITION"] = num++;
string text3 = StringUtility.ToUpperInvariant(text2);
if (!mySqlTokenizer.Quoted && list.Contains(text3))
{
mySqlSchemaRow["PARAMETER_MODE"] = text3;
text2 = mySqlTokenizer.NextToken();
}
if (mySqlTokenizer.Quoted)
{
text2 = text2.Substring(1, text2.Length - 2);
}
mySqlSchemaRow["PARAMETER_NAME"] = text2;
text2 = this.ParseDataType(mySqlSchemaRow, mySqlTokenizer);
if (text2 == ",")
{
text2 = mySqlTokenizer.NextToken();
}
if (nameToRestrict == null || string.Compare(mySqlSchemaRow["PARAMETER_NAME"].ToString(), nameToRestrict, StringComparison.OrdinalIgnoreCase) == 0)
{
parametersTable.Rows.Add(mySqlSchemaRow);
}
}
text2 = StringUtility.ToUpperInvariant(mySqlTokenizer.NextToken());
if (string.Compare(text2, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
{
MySqlSchemaRow mySqlSchemaRow2 = parametersTable.Rows[0];
mySqlSchemaRow2["PARAMETER_NAME"] = "RETURN_VALUE";
this.ParseDataType(mySqlSchemaRow2, mySqlTokenizer);
}
}
