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)
{
_lst.Add(new MySqlView(cmd, dr[0] + ""));
}
}
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;
}
_lst.Add(new MySqlColumn(
dtDataType.Columns[i].ColumnName,
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"] + ""));
}
}
public MySqlColumnList(MySqlCommand cmd, string tableName)
{
_tableName = tableName;
var dtDataType = QueryExpress.GetTable(cmd, $"SELECT * FROM `{_tableName}` LIMIT 0;");
SqlShowFullColumns = $"SHOW FULL COLUMNS FROM `{_tableName}`;";
var dtColInfo = QueryExpress.GetTable(cmd, SqlShowFullColumns);
for (var i = 0; i < dtDataType.Columns.Count; i++)
{
var isNullStr = (dtColInfo.Rows[i]["Null"] + "").ToLower();
var isNull = isNullStr == "yes";
_lst.Add(new MySqlColumn(
dtDataType.Columns[i].ColumnName,
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"] + ""));
}
}
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)
{
_lst.Add(new MySqlProcedure(cmd, dr["Name"] + "", dr["Definer"] + ""));
}
}
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 void GetTotalRows(MySqlCommand cmd, GetTotalRowsMethod enumGetTotalRowsMode)
{
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));
int _tableCountTotalRow = 0;
foreach (DataRow dr in dtTotalRows.Rows)
{
string _tbname = dr["TABLE_NAME"] + "";
long _totalRowsThisTable = 0L;
long.TryParse(dr["TABLE_ROWS"] + "", out _totalRowsThisTable);
if (_listTable.Contains(_tbname))
{
_listTable[_tbname].SetTotalRows(_totalRowsThisTable);
}
}
}
else if (enumGetTotalRowsMode == GetTotalRowsMethod.SelectCount)
{
for (int i = 0; i < _listTable.Count; i++)
{
_listTable[i].GetTotalRowsByCounting(cmd);
if (GetTotalRowsProgressChanged != null)
{
GetTotalRowsProgressChanged(this, new GetTotalRowsArgs(_listTable.Count, i + 1));
}
}
}
}
public MySqlFunctionList(MySqlCommand cmd)
{
try
{
string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
_sqlShowFunctions = string.Format("SHOW FUNCTION STATUS WHERE UPPER(TRIM(Db))= UPPER(TRIM('{0}'));", dbname);
DataTable dt = QueryExpress.GetTable(cmd, _sqlShowFunctions);
foreach (DataRow dr in dt.Rows)
{
var name = dr["Name"].ToString();
_lst.Add(name, new MySqlFunction(cmd, name, dr["Definer"].ToString()));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
_allowAccess = false;
}
}
catch
{
throw;
}
}
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)
{
_lst.Add(new MySqlTable(cmd, dr[0] + ""));
}
}
Dictionary <string, string> Export_RearrangeTableOrderForForeignKey(Dictionary <string, string> dic1)
{
System.Data.DataTable dtForeignKeyTables = QueryExpress.GetTable(Command, string.Format("select table_name, referenced_table_name from information_schema.key_column_usage where constraint_schema='{0}' and referenced_table_name IS NOT NULL;", QueryExpress.EscapeStringSequence(_database.Name)));
if (dtForeignKeyTables.Rows.Count == 0)
{
return(dic1);
}
Dictionary <string, string> dic2 = new Dictionary <string, string>();
bool requireLoop = true;
while (requireLoop)
{
requireLoop = false;
foreach (var kv in dic1)
{
if (dic2.ContainsKey(kv.Key))
{
continue;
}
bool handledInForeignKeySearch = false;
for (int i = 0; i < dtForeignKeyTables.Rows.Count; i++)
{
string _tb_name = dtForeignKeyTables.Rows[i]["table_name"] + "";
string _fk_tb_name = dtForeignKeyTables.Rows[i]["referenced_table_name"] + "";
if (kv.Key == _tb_name)
{
handledInForeignKeySearch = true;
if (dic2.ContainsKey(_fk_tb_name))
{
dic2[kv.Key] = kv.Value;
requireLoop = true;
}
continue;
}
}
if (!handledInForeignKeySearch)
{
dic2[kv.Key] = kv.Value;
requireLoop = true;
}
}
}
return(dic2);
}
public void GetTotalRows(MySqlCommand cmd)
{
var dtTotalRows = QueryExpress.GetTable(cmd,
$"SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = '{Name}';");
var tableCountTotalRow = 0;
foreach (DataRow dr in dtTotalRows.Rows)
{
var thisTableName = dr["TABLE_NAME"] + "";
var totalRowsThisTable = 0L;
try
{
long.TryParse(dr["TABLE_ROWS"] + "", out totalRowsThisTable);
}
catch
{
// ignored
}
foreach (var t in Tables)
{
if (t.Name != thisTableName)
{
continue;
}
tableCountTotalRow = tableCountTotalRow + 1;
t.SetTotalRows(totalRowsThisTable);
GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(Tables.Count, tableCountTotalRow));
break;
}
}
//for (int i = 0; i < _listTable.Count; i++)
//{
// _listTable[i].GetTotalRows(cmd);
// if (GetTotalRowsProgressChanged != null)
// {
// GetTotalRowsProgressChanged(this, new GetTotalRowsArgs(_listTable.Count, i + 1));
// }
//}
}
public void GetTotalRows(MySqlCommand cmd)
{
DataTable dtTotalRows = QueryExpress.GetTable(cmd, string.Format("SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = '{0}';", _name));
int _tableCountTotalRow = 0;
foreach (DataRow dr in dtTotalRows.Rows)
{
string _thisTableName = dr["TABLE_NAME"] + "";
long _totalRowsThisTable = 0L;
try
{
long.TryParse(dr["TABLE_ROWS"] + "", out _totalRowsThisTable);
}
catch { }
for (int i = 0; i < _listTable.Count; i++)
{
if (_listTable[i].Name != _thisTableName)
{
continue;
}
_tableCountTotalRow = _tableCountTotalRow + 1;
_listTable[i].SetTotalRows(_totalRowsThisTable);
if (GetTotalRowsProgressChanged != null)
{
GetTotalRowsProgressChanged(this, new GetTotalRowsArgs(_listTable.Count, _tableCountTotalRow));
}
break;
}
}
//for (int i = 0; i < _listTable.Count; i++)
//{
// _listTable[i].GetTotalRows(cmd);
// if (GetTotalRowsProgressChanged != null)
// {
// GetTotalRowsProgressChanged(this, new GetTotalRowsArgs(_listTable.Count, i + 1));
// }
//}
}
public MySqlView(MySqlCommand cmd, string viewName)
{
_name = viewName;
string sqlShowCreate = string.Format("SHOW CREATE VIEW `{0}`;", viewName);
System.Data.DataTable dtView = QueryExpress.GetTable(cmd, sqlShowCreate);
_createViewSQL = dtView.Rows[0]["Create View"] + ";";
_createViewSQL = _createViewSQL.Replace("\r\n", "^~~~~~~~~~~~~~~^");
_createViewSQL = _createViewSQL.Replace("\n", "^~~~~~~~~~~~~~~^");
_createViewSQL = _createViewSQL.Replace("\r", "^~~~~~~~~~~~~~~^");
_createViewSQL = _createViewSQL.Replace("^~~~~~~~~~~~~~~^", "\r\n");
_createViewSQLWithoutDefiner = QueryExpress.EraseDefiner(_createViewSQL);
}
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 MySqlTriggerList(MySqlCommand cmd)
{
SqlShowTriggers = "SHOW TRIGGERS;";
try
{
var dt = QueryExpress.GetTable(cmd, SqlShowTriggers);
foreach (DataRow dr in dt.Rows)
{
_lst.Add(new MySqlTrigger(cmd, dr["Trigger"] + "", dr["Definer"] + ""));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
AllowAccess = false;
}
}
}
public MySqlEventList(MySqlCommand cmd)
{
try
{
var dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
SqlShowEvent = $"SHOW EVENTS WHERE UPPER(TRIM(Db))=UPPER(TRIM('{dbname}'));";
var dt = QueryExpress.GetTable(cmd, SqlShowEvent);
foreach (DataRow dr in dt.Rows)
{
_lst.Add(new MySqlEvent(cmd, dr["Name"] + "", dr["Definer"] + ""));
}
}
catch (MySqlException myEx)
{
if (myEx.Message.ToLower().Contains("access denied"))
{
AllowAccess = false;
}
}
}
