public MySqlProcedureList(MySqlCommand cmd)
{
try
{
string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
_sqlShowProcedures = string.Format("SHOW PROCEDURE STATUS WHERE UPPER(TRIM(Db))= UPPER(TRIM('{0}'));", dbname);
DataTable dt = QueryExpress.GetTable(cmd, _sqlShowProcedures);
foreach (DataRow dr in dt.Rows)
{
var name = dr["Name"].ToString();
_lst.Add(name, new MySqlProcedure(cmd, name, dr["Definer"].ToString()));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
_allowAccess = false;
}
}
catch
{
throw;
}
}
public MySqlViewList(MySqlCommand cmd)
{
try
{
string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
_sqlShowViewList = string.Format("SHOW FULL TABLES FROM `{0}` WHERE Table_type = 'VIEW';", dbname);
DataTable dt = QueryExpress.GetTable(cmd, _sqlShowViewList);
foreach (DataRow dr in dt.Rows)
{
var nome = dr[0].ToString();
_lst.Add(nome, new MySqlView(cmd, nome));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
_allowAccess = false;
}
}
catch
{
throw;
}
}
public MySqlTriggerList(MySqlCommand cmd)
{
_sqlShowTriggers = "SHOW TRIGGERS;";
try
{
DataTable dt = QueryExpress.GetTable(cmd, _sqlShowTriggers);
foreach (DataRow dr in dt.Rows)
{
var name = dr["Trigger"].ToString();
_lst.Add(name, new MySqlTrigger(cmd, name, dr["Definer"].ToString()));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
_allowAccess = false;
}
}
catch
{
throw;
}
}
public MySqlColumnList(MySqlCommand cmd, string tableName)
{
_tableName = tableName;
DataTable dtDataType = QueryExpress.GetTable(cmd, string.Format("SELECT * FROM `{0}` where 1 = 2;", tableName));
_sqlShowFullColumns = string.Format("SHOW FULL COLUMNS FROM `{0}`;", tableName);
DataTable dtColInfo = QueryExpress.GetTable(cmd, _sqlShowFullColumns);
for (int i = 0; i < dtDataType.Columns.Count; i++)
{
string isNullStr = (dtColInfo.Rows[i]["Null"] + "").ToLower();
bool isNull = false;
if (isNullStr == "yes")
{
isNull = true;
}
var name = dtDataType.Columns[i].ColumnName;
_lst.Add(
name,
new MySqlColumn(
name,
dtDataType.Columns[i].DataType,
dtColInfo.Rows[i]["Type"] + "",
dtColInfo.Rows[i]["Collation"] + "",
isNull,
dtColInfo.Rows[i]["Key"] + "",
dtColInfo.Rows[i]["Default"] + "",
dtColInfo.Rows[i]["Extra"] + "",
dtColInfo.Rows[i]["Privileges"] + "",
dtColInfo.Rows[i]["Comment"] + "")
);
}
}
private void Export_GetConditionString(MySqlDataReader rdr, MySqlTable table, StringBuilder sb)
{
bool isFirst = true;
for (int i = 0; i < rdr.FieldCount; i++)
{
string colName = rdr.GetName(i);
var col = table.Columns[colName];
if (col.IsPrimaryKey)
{
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append(" and ");
}
sb.Append("`");
sb.Append(colName);
sb.Append("`=");
//sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
sb.Append(QueryExpress.ConvertToSqlFormat(rdr[i], true, true, col, ExportInfo.BlobExportMode));
}
}
}
private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < rdr.FieldCount; i++)
{
string columnName = rdr.GetName(i);
if (table.Columns[columnName].IsGeneratedColumn)
{
continue;
}
if (sb.Length == 0)
{
sb.AppendFormat("(");
}
else
{
sb.AppendFormat(",");
}
object ob = rdr[i];
var col = table.Columns[columnName];
//sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
sb.Append(QueryExpress.ConvertToSqlFormat(ob, true, true, col, ExportInfo.BlobExportMode));
}
sb.AppendFormat(")");
return(sb.ToString());
}
public MySqlTable(MySqlCommand cmd, string name)
{
_name = name;
string sql = string.Format("SHOW CREATE TABLE `{0}`;", name);
_createTableSql = QueryExpress.ExecuteScalarStr(cmd, sql, 1).Replace(Environment.NewLine, "^~~~~~~^").Replace("\r", "^~~~~~~^").Replace("\n", "^~~~~~~^").Replace("^~~~~~~^", Environment.NewLine).Replace("CREATE TABLE ", "CREATE TABLE IF NOT EXISTS ") + ";";
_createTableSqlWithoutAutoIncrement = RemoveAutoIncrement(_createTableSql);
_lst = new MySqlColumnList(cmd, name);
GetInsertStatementHeaders();
}
public MySqlTableList(MySqlCommand cmd)
{
_sqlShowFullTables = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';";
DataTable dtTableList = QueryExpress.GetTable(cmd, _sqlShowFullTables);
foreach (DataRow dr in dtTableList.Rows)
{
var table = new MySqlTable(cmd, dr[0] + "");
_lst.Add(table.Name, table);
}
}
public void GetServerInfo(MySqlCommand cmd)
{
_edition = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'version_comment';", 1);
_versionNumber = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'version';", 1);
_characterSetServer = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_server';", 1);
_characterSetSystem = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_system';", 1);
_characterSetConnection = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_connection';", 1);
_characterSetDatabase = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_database';", 1);
_currentUserClientHost = QueryExpress.ExecuteScalarStr(cmd, "SELECT current_user;");
string[] ca = _currentUserClientHost.Split('@');
_currentUser = ca[0];
_currentClientHost = ca[1];
GetMajorVersionNumber();
}
public MySqlProcedure(MySqlCommand cmd, string procedureName, string definer)
{
_name = procedureName;
string sql = string.Format("SHOW CREATE PROCEDURE `{0}`;", procedureName);
_createProcedureSQL = QueryExpress.ExecuteScalarStr(cmd, sql, 2);
_createProcedureSQL = _createProcedureSQL.Replace("\r\n", "^~~~~~~~~~~~~~~^");
_createProcedureSQL = _createProcedureSQL.Replace("\n", "^~~~~~~~~~~~~~~^");
_createProcedureSQL = _createProcedureSQL.Replace("\r", "^~~~~~~~~~~~~~~^");
_createProcedureSQL = _createProcedureSQL.Replace("^~~~~~~~~~~~~~~^", "\r\n");
string[] sa = definer.Split('@');
definer = string.Format(" DEFINER=`{0}`@`{1}`", sa[0], sa[1]);
_createProcedureSQLWithoutDefiner = _createProcedureSQL.Replace(definer, string.Empty);
}
public void GetTotalRows(MySqlCommand cmd, GetTotalRowsMethod enumGetTotalRowsMode)
{
int i = 0;
var timer = new Timer
{
Interval = 10000
};
timer.Elapsed += (sender, e) =>
{
GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, i));
};
if (enumGetTotalRowsMode == GetTotalRowsMethod.InformationSchema)
{
DataTable dtTotalRows = QueryExpress.GetTable(cmd, string.Format("SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = '{0}';", _name));
timer.Start();
foreach (DataRow dr in dtTotalRows.Rows)
{
i++;
var _tbname = dr["TABLE_NAME"] + "";
long.TryParse(dr["TABLE_ROWS"] + "", out var _totalRowsThisTable);
if (_listTable.Contains(_tbname))
{
_listTable[_tbname].SetTotalRows((long)(_totalRowsThisTable * 1.1)); // Adiciona 10% de erro
}
}
timer.Stop();
GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, _listTable.Count));
}
else if (enumGetTotalRowsMode == GetTotalRowsMethod.SelectCount)
{
timer.Start();
foreach (var table in _listTable)
{
i++;
table.GetTotalRowsByCounting(cmd);
}
timer.Stop();
GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, _listTable.Count));
}
}
public void GetDatabaseInfo(MySqlCommand cmd, GetTotalRowsMethod enumGetTotalRowsMode)
{
_name = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
_defaultCharSet = QueryExpress.ExecuteScalarStr(cmd, "SHOW VARIABLES LIKE 'character_set_database';", 1);
_createDatabaseSql = QueryExpress.ExecuteScalarStr(cmd, string.Format("SHOW CREATE DATABASE `{0}`;", _name), 1).Replace("CREATE DATABASE", "CREATE DATABASE IF NOT EXISTS") + ";";
_dropDatabaseSql = string.Format("DROP DATABASE IF EXISTS `{0}`;", _name);
_listTable = new MySqlTableList(cmd);
_listProcedure = new MySqlProcedureList(cmd);
_listFunction = new MySqlFunctionList(cmd);
_listTrigger = new MySqlTriggerList(cmd);
_listEvent = new MySqlEventList(cmd);
_listView = new MySqlViewList(cmd);
if (enumGetTotalRowsMode != GetTotalRowsMethod.Skip)
{
GetTotalRows(cmd, enumGetTotalRowsMode);
}
}
/// <summary>
/// Gets the list of document headers.
/// </summary>
/// <param name="cmd">The MySqlCommand that will be used to retrieve the database default character set.</param>
/// <returns>List of document headers.</returns>
public List <string> GetDocumentHeaders(MySqlCommand cmd)
{
if (_documentHeaders == null)
{
_documentHeaders = new List <string>();
string databaseCharSet = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_database';", 1);
_documentHeaders.Add("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;");
_documentHeaders.Add("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;");
_documentHeaders.Add("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;");
_documentHeaders.Add(string.Format("/*!40101 SET NAMES {0} */;", databaseCharSet));
//_documentHeaders.Add("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;");
//_documentHeaders.Add("/*!40103 SET TIME_ZONE='+00:00' */;");
_documentHeaders.Add("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
_documentHeaders.Add("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
_documentHeaders.Add("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
_documentHeaders.Add("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;");
}
return(_documentHeaders);
}
public static string ConvertToSqlFormat(object ob, bool wrapStringWithSingleQuote, bool escapeStringSequence, MySqlColumn col, BlobDataExportMode blobExportMode)
{
StringBuilder sb = new StringBuilder();
if (ob == null || ob is System.DBNull)
{
sb.AppendFormat("NULL");
}
else if (ob is System.String)
{
string str = (string)ob;
if (escapeStringSequence)
{
str = QueryExpress.EscapeStringSequence(str);
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(str);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.Boolean)
{
sb.AppendFormat(Convert.ToInt32(ob).ToString());
}
else if (ob is System.Byte[])
{
if (((byte[])ob).Length == 0)
{
if (wrapStringWithSingleQuote)
{
return("''");
}
else
{
return("");
}
}
else
{
if (blobExportMode == BlobDataExportMode.HexString)
{
sb.AppendFormat(CryptoExpress.ConvertByteArrayToHexString((byte[])ob));
}
else if (blobExportMode == BlobDataExportMode.BinaryChar)
{
if (wrapStringWithSingleQuote)
{
sb.Append("'");
}
foreach (byte b in ((byte[])ob))
{
char ch = (char)b;
escape_string(sb, ch);
}
if (wrapStringWithSingleQuote)
{
sb.Append("'");
}
}
}
}
else if (ob is short)
{
sb.AppendFormat(((short)ob).ToString(_numberFormatInfo));
}
else if (ob is int)
{
sb.AppendFormat(((int)ob).ToString(_numberFormatInfo));
}
else if (ob is long)
{
sb.AppendFormat(((long)ob).ToString(_numberFormatInfo));
}
else if (ob is ushort)
{
sb.AppendFormat(((ushort)ob).ToString(_numberFormatInfo));
}
else if (ob is uint)
{
sb.AppendFormat(((uint)ob).ToString(_numberFormatInfo));
}
else if (ob is ulong)
{
sb.AppendFormat(((ulong)ob).ToString(_numberFormatInfo));
}
else if (ob is double)
{
sb.AppendFormat(((double)ob).ToString(_numberFormatInfo));
}
else if (ob is decimal)
{
sb.AppendFormat(((decimal)ob).ToString(_numberFormatInfo));
}
else if (ob is float)
{
sb.AppendFormat(((float)ob).ToString(_numberFormatInfo));
}
else if (ob is byte)
{
sb.AppendFormat(((byte)ob).ToString(_numberFormatInfo));
}
else if (ob is sbyte)
{
sb.AppendFormat(((sbyte)ob).ToString(_numberFormatInfo));
}
else if (ob is TimeSpan)
{
TimeSpan ts = (TimeSpan)ob;
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(((int)ts.TotalHours).ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Duration().Minutes.ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Duration().Seconds.ToString().PadLeft(2, '0'));
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.DateTime)
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
if (col.TimeFractionLength > 0)
{
sb.Append(".");
string _microsecond = ((DateTime)ob).ToString("".PadLeft(col.TimeFractionLength, 'f'));
sb.Append(_microsecond);
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
//else if (ob is MySql.Data.Types.MySqlDateTime)
//{
// MySql.Data.Types.MySqlDateTime mdt = (MySql.Data.Types.MySqlDateTime)ob;
// if (mdt.IsNull)
// {
// sb.AppendFormat("NULL");
// }
// else
// {
// if (mdt.IsValidDateTime)
// {
// DateTime dtime = mdt.Value;
// if (wrapStringWithSingleQuote)
// sb.AppendFormat("'");
// if (col.MySqlDataType == "datetime")
// sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
// else if (col.MySqlDataType == "date")
// sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
// else if (col.MySqlDataType == "time")
// sb.AppendFormat("{0}:{1}:{2}", mdt.Hour, mdt.Minute, mdt.Second);
// //sb.AppendFormat(dtime.ToString("HH:mm:ss", _dateFormatInfo));
// else
// sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
// if (col.TimeFractionLength > 0)
// {
// sb.Append(".");
// sb.Append(((MySql.Data.Types.MySqlDateTime)ob).Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
// }
// if (wrapStringWithSingleQuote)
// sb.AppendFormat("'");
// }
// else
// {
// if (wrapStringWithSingleQuote)
// sb.AppendFormat("'");
// if (col.MySqlDataType == "datetime")
// sb.AppendFormat("0000-00-00 00:00:00");
// else if (col.MySqlDataType == "date")
// sb.AppendFormat("0000-00-00");
// else if (col.MySqlDataType == "time")
// sb.AppendFormat("00:00:00");
// else
// sb.AppendFormat("0000-00-00 00:00:00");
// if (col.TimeFractionLength > 0)
// {
// sb.Append(".".PadRight(col.TimeFractionLength, '0'));
// }
// if (wrapStringWithSingleQuote)
// sb.AppendFormat("'");
// }
// }
//}
else if (ob is System.Guid)
{
if (col.MySqlDataType == "binary(16)")
{
sb.Append(CryptoExpress.ConvertByteArrayToHexString(((Guid)ob).ToByteArray()));
}
else if (col.MySqlDataType == "char(36)")
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
}
else
{
throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
}
return(sb.ToString());
}
public void GetTotalRowsByCounting(MySqlCommand cmd)
{
string sql = string.Format("SELECT COUNT(1) FROM `{0}`;", _name);
_totalRows = QueryExpress.ExecuteScalarLong(cmd, sql);
}
