private MyCatSchemaCollection GetTable(string sql)
{
MyCatSchemaCollection c = new MyCatSchemaCollection();
MyCatCommand cmd = new MyCatCommand(sql, connection);
MyCatDataReader reader = cmd.ExecuteReader();
// add columns
for (int i = 0; i < reader.FieldCount; i++)
{
c.AddColumn(reader.GetName(i), reader.GetFieldType(i));
}
using (reader)
{
while (reader.Read())
{
MyCatSchemaRow row = c.AddRow();
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader.GetValue(i);
}
}
}
return(c);
}
public virtual List <MyCatError> ReportWarnings(MyCatConnection connection)
{
List <MyCatError> warnings = new List <MyCatError>();
MyCatCommand cmd = new MyCatCommand("SHOW WARNINGS", connection);
cmd.InternallyCreated = true;
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
warnings.Add(new MyCatError(reader.GetString(0),
reader.GetInt32(1), reader.GetString(2)));
}
}
MyCatInfoMessageEventArgs args = new MyCatInfoMessageEventArgs();
args.errors = warnings.ToArray();
if (connection != null)
{
connection.OnInfoMessage(args);
}
return(warnings);
}
/// <summary>
/// Loads the properties from the connected server into a hashtable
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
private Dictionary <string, string> LoadServerProperties(MyCatConnection connection)
{
// load server properties
Dictionary <string, string> hash = new Dictionary <string, string>();
MyCatCommand cmd = new MyCatCommand("SHOW VARIABLES", connection);
try
{
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string key = reader.GetString(0);
string value = reader.GetString(1);
hash[key] = value;
}
}
// Get time zone offset as numerical value
timeZoneOffset = TimeZoneInfo.Local.BaseUtcOffset.Hours;
return(hash);
}
catch (Exception ex)
{
MyCatTrace.LogError(ThreadID, ex.Message);
throw;
}
}
private void FindTables(MyCatSchemaCollection schema, string[] restrictions)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
"SHOW TABLE STATUS FROM `{0}`", restrictions[1]);
if (restrictions != null && restrictions.Length >= 3 &&
restrictions[2] != null)
{
where.AppendFormat(CultureInfo.InvariantCulture,
" LIKE '{0}'", restrictions[2]);
}
sql.Append(where.ToString());
string table_type = restrictions[1].ToLower() == "information_schema"
?
"SYSTEM VIEW"
: "BASE TABLE";
MyCatCommand cmd = new MyCatCommand(sql.ToString(), connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyCatSchemaRow row = schema.AddRow();
row["TABLE_CATALOG"] = null;
row["TABLE_SCHEMA"] = restrictions[1];
row["TABLE_NAME"] = reader.GetString(0);
row["TABLE_TYPE"] = table_type;
row["ENGINE"] = GetString(reader, 1);
row["VERSION"] = reader.GetValue(2);
row["ROW_FORMAT"] = GetString(reader, 3);
row["TABLE_ROWS"] = reader.GetValue(4);
row["AVG_ROW_LENGTH"] = reader.GetValue(5);
row["DATA_LENGTH"] = reader.GetValue(6);
row["MAX_DATA_LENGTH"] = reader.GetValue(7);
row["INDEX_LENGTH"] = reader.GetValue(8);
row["DATA_FREE"] = reader.GetValue(9);
row["AUTO_INCREMENT"] = reader.GetValue(10);
row["CREATE_TIME"] = reader.GetValue(11);
row["UPDATE_TIME"] = reader.GetValue(12);
row["CHECK_TIME"] = reader.GetValue(13);
row["TABLE_COLLATION"] = GetString(reader, 14);
row["CHECKSUM"] = reader.GetValue(15);
row["CREATE_OPTIONS"] = GetString(reader, 16);
row["TABLE_COMMENT"] = GetString(reader, 17);
}
}
}
internal void GetParametersFromShowCreate(MyCatSchemaCollection parametersTable,
string[] restrictions, MyCatSchemaCollection routines)
{
// this allows us to pass in a pre-populated routines table
// and avoid the querying for them again.
// we use this when calling a procedure or function
if (routines == null)
{
routines = GetSchema("procedures", restrictions);
}
MyCatCommand cmd = connection.CreateCommand();
foreach (MyCatSchemaRow routine in routines.Rows)
{
string showCreateSql = String.Format("SHOW CREATE {0} `{1}`.`{2}`",
routine["ROUTINE_TYPE"], routine["ROUTINE_SCHEMA"],
routine["ROUTINE_NAME"]);
cmd.CommandText = showCreateSql;
try
{
string nameToRestrict = null;
if (restrictions != null && restrictions.Length == 5 &&
restrictions[4] != null)
{
nameToRestrict = restrictions[4];
}
using (MyCatDataReader reader = cmd.ExecuteReader())
{
reader.Read();
string body = reader.GetString(2);
#if NETSTANDARD1_3
reader.Dispose();
#else
reader.Close();
#endif
ParseProcedureBody(parametersTable, body, routine, nameToRestrict);
}
}
#if NETSTANDARD1_3
catch (MyCatNullValueException snex)
#else
catch (System.Data.SqlTypes.SqlNullValueException snex)
#endif
{
throw new InvalidOperationException(
String.Format(Resources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex);
}
}
}
private string GetProcedureParameterLine(MyCatSchemaRow isRow)
{
string sql = "SHOW CREATE {0} `{1}`.`{2}`";
sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"],
isRow["ROUTINE_NAME"]);
MyCatCommand cmd = new MyCatCommand(sql, connection);
using (MyCatDataReader 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);
MyCatTokenizer tokenizer = new MyCatTokenizer(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.StartIndex + 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.StartIndex - start));
}
}
/// <summary>
/// Executes a single command against a MySQL database, possibly inside an existing transaction.
/// </summary>
/// <param name="connection"><see cref="MyCatConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MyCatTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MyCatParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MyCatDataReader"/> object ready to read the results of the command</returns>
private static MyCatDataReader ExecuteReader(MyCatConnection connection, MyCatTransaction transaction, string commandText, MyCatParameter[] commandParameters, bool ExternalConn)
{
//create a command and prepare it for execution
MyCatCommand cmd = new MyCatCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
{
foreach (MyCatParameter p in commandParameters)
{
cmd.Parameters.Add(p);
}
}
//create a reader
MyCatDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (ExternalConn)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return(dr);
}
internal static void InitCollections(MyCatConnection connection)
{
defaultCollations = new Dictionary <string, string>();
maxLengths = new Dictionary <string, int>();
MyCatCommand cmd = new MyCatCommand("SHOW CHARSET", connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
defaultCollations.Add(reader.GetString(0), reader.GetString(2));
maxLengths.Add(reader.GetString(0), Convert.ToInt32(reader.GetValue(3)));
}
}
}
public virtual MyCatSchemaCollection GetUDF(string[] restrictions)
{
string sql = "SELECT name,ret,dl FROM mysql.func";
if (restrictions != null)
{
if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0]))
{
sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]);
}
}
MyCatSchemaCollection dt = new MyCatSchemaCollection("User-defined Functions");
dt.AddColumn("NAME", typeof(string));
dt.AddColumn("RETURN_TYPE", typeof(int));
dt.AddColumn("LIBRARY_NAME", typeof(string));
MyCatCommand cmd = new MyCatCommand(sql, connection);
try
{
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyCatSchemaRow row = dt.AddRow();
row[0] = reader.GetString(0);
row[1] = reader.GetInt32(1);
row[2] = reader.GetString(2);
}
}
}
catch (MyCatException ex)
{
if (ex.Number != (int)MyCatErrorCode.TableAccessDenied)
{
throw;
}
throw new MyCatException(Resources.UnableToEnumerateUDF, ex);
}
return(dt);
}
/// <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(MyCatSchemaCollection fkTable, MyCatSchemaRow tableToParse,
string filterName, bool includeColumns)
{
string sqlMode = GetSqlMode();
if (filterName != null)
{
filterName = StringUtility.ToLowerInvariant(filterName);
}
string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`",
tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]);
string lowerBody = null, body = null;
MyCatCommand cmd = new MyCatCommand(sql, connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
reader.Read();
body = reader.GetString(1);
lowerBody = StringUtility.ToLowerInvariant(body);
}
MyCatTokenizer tokenizer = new MyCatTokenizer(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);
}
}
private void ClearKillFlag()
{
// This query will silently crash because of the Kill call that happened before.
string dummyStatement = "SELECT * FROM bogus_table LIMIT 0"; /* dummy query used to clear kill flag */
MyCatCommand dummyCommand = new MyCatCommand(dummyStatement, connection);
dummyCommand.InternallyCreated = true;
try
{
dummyCommand.ExecuteReader(); // ExecuteReader catches the exception and returns null, which is expected.
}
catch (MyCatException ex)
{
if (ex.Number != (int)MyCatErrorCode.NoSuchTable)
{
throw;
}
}
}
public override void Close(MyCatDataReader reader)
{
base.Close(reader);
if (String.IsNullOrEmpty(outSelect))
{
return;
}
if ((reader.CommandBehavior & CommandBehavior.SchemaOnly) != 0)
{
return;
}
MyCatCommand cmd = new MyCatCommand(outSelect, command.Connection);
using (MyCatDataReader rdr = cmd.ExecuteReader(reader.CommandBehavior))
{
ProcessOutputParameters(rdr);
}
}
private void LoadTableColumns(MyCatSchemaCollection schemaCollection, string schema,
string tableName, string columnRestriction)
{
string sql = String.Format("SHOW FULL COLUMNS FROM `{0}`.`{1}`",
schema, tableName);
MyCatCommand cmd = new MyCatCommand(sql, connection);
int pos = 1;
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string colName = reader.GetString(0);
if (columnRestriction != null && colName != columnRestriction)
{
continue;
}
MyCatSchemaRow row = schemaCollection.AddRow();
row["TABLE_CATALOG"] = DBNull.Value;
row["TABLE_SCHEMA"] = schema;
row["TABLE_NAME"] = tableName;
row["COLUMN_NAME"] = colName;
row["ORDINAL_POSITION"] = pos++;
row["COLUMN_DEFAULT"] = reader.GetValue(5);
row["IS_NULLABLE"] = reader.GetString(3);
row["DATA_TYPE"] = reader.GetString(1);
row["CHARACTER_MAXIMUM_LENGTH"] = DBNull.Value;
row["CHARACTER_OCTET_LENGTH"] = DBNull.Value;
row["NUMERIC_PRECISION"] = DBNull.Value;
row["NUMERIC_SCALE"] = DBNull.Value;
row["CHARACTER_SET_NAME"] = reader.GetValue(2);
row["COLLATION_NAME"] = row["CHARACTER_SET_NAME"];
row["COLUMN_TYPE"] = reader.GetString(1);
row["COLUMN_KEY"] = reader.GetString(4);
row["EXTRA"] = reader.GetString(6);
row["PRIVILEGES"] = reader.GetString(7);
row["COLUMN_COMMENT"] = reader.GetString(8);
ParseColumnRow(row);
}
}
}
/// <summary>
/// Loads all the current character set names and ids for this server
/// into the charSets hashtable
/// </summary>
private void LoadCharacterSets(MyCatConnection connection)
{
MyCatCommand cmd = new MyCatCommand("SHOW COLLATION", connection);
// now we load all the currently active collations
try
{
using (MyCatDataReader reader = cmd.ExecuteReader())
{
charSets = new Dictionary <int, string>();
while (reader.Read())
{
charSets[Convert.ToInt32(reader["id"], NumberFormatInfo.InvariantInfo)] =
reader.GetString(reader.GetOrdinal("charset"));
}
}
}
catch (Exception ex)
{
MyCatTrace.LogError(ThreadID, ex.Message);
throw;
}
}
public virtual MyCatSchemaCollection GetProcedures(string[] restrictions)
{
MyCatSchemaCollection dt = new MyCatSchemaCollection("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]);
}
}
MyCatCommand cmd = new MyCatCommand(sql.ToString(), connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyCatSchemaRow 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);
}
public virtual MyCatSchemaCollection GetIndexColumns(string[] restrictions)
{
MyCatSchemaCollection dt = new MyCatSchemaCollection("IndexColumns");
dt.AddColumn("INDEX_CATALOG", typeof(string));
dt.AddColumn("INDEX_SCHEMA", typeof(string));
dt.AddColumn("INDEX_NAME", typeof(string));
dt.AddColumn("TABLE_NAME", typeof(string));
dt.AddColumn("COLUMN_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(int));
dt.AddColumn("SORT_ORDER", typeof(string));
int max = restrictions == null ? 4 : restrictions.Length;
string[] tableRestrictions = new string[Math.Max(max, 4)];
if (restrictions != null)
{
restrictions.CopyTo(tableRestrictions, 0);
}
tableRestrictions[3] = "BASE TABLE";
MyCatSchemaCollection tables = GetTables(tableRestrictions);
foreach (MyCatSchemaRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
table["TABLE_SCHEMA"], table["TABLE_NAME"]);
MyCatCommand cmd = new MyCatCommand(sql, connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME"));
string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME"));
if (restrictions != null)
{
if (restrictions.Length >= 4 && restrictions[3] != null &&
key_name != restrictions[3])
{
continue;
}
if (restrictions.Length >= 5 && restrictions[4] != null &&
col_name != restrictions[4])
{
continue;
}
}
MyCatSchemaRow row = dt.AddRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = key_name;
row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE"));
row["COLUMN_NAME"] = col_name;
row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX"));
row["SORT_ORDER"] = reader.GetString("COLLATION");
}
}
}
return(dt);
}
