public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
MySQLDatabaseInfo databaseInfo = database as MySQLDatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string sql = string.Format(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = {0}
UNION
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = {0};", paramPrefix + "schema");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, databaseInfo.Name);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
if (!isTableSourceToIgnore(tableName))
{
string qualifiedTableName = GetQualifiedIdentifier(databaseInfo.Name, tableName);
tables.Add(new MySQLTableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
return(tables);
}
public IDictionary <string, bool> CheckTableSourcesExist(params string[] tableSourcesNames)
{
MySQLDatabaseInfo dbInfo = databaseServices.ObjectFactory.CreateLocalDatabaseInfo() as MySQLDatabaseInfo;
if (dbInfo == null)
{
return(null);
}
HashSet <string> tableSourcesFound = new HashSet <string>();
IList <string> tableSourceNamesList = tableSourcesNames.ToList();
if (tableSourceNamesList.IsNullOrEmpty())
{
return(null);
}
using (IDbConnection conn = databaseServices.TransactionService.CreateConnection()) {
string tableCondition;
string paramPrefix = databaseServices.ExecutionService.ParameterPrefix;
if (tableSourceNamesList.Count == 1)
{
tableCondition = "=" + paramPrefix + "tablename";
}
else
{
tableCondition = " IN ('" + tableSourceNamesList.StrCat("','").ToUpper() + "')";
}
string sql = string.Format(@"(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME {1} AND TABLE_SCHEMA = '{0}')
UNION
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME {1} AND TABLE_SCHEMA = '{0}')", dbInfo.Name, tableCondition);
using (IDbCommand cmd = databaseServices.ExecutionService.CreateCommand(conn, sql)) {
if (tableSourceNamesList.Count == 1)
{
databaseServices.ExecutionService.CreateParameter(cmd,
paramPrefix + "tablename", DbType.String, tableSourceNamesList.First());
}
using (IDataReader reader = databaseServices.ExecutionService.ExecuteReader(cmd)) {
while (reader.Read())
{
tableSourcesFound.Add(Convert.ToString(reader["TABLE_NAME"]).ToUpper());
}
}
}
}
IDictionary <string, bool> result = new Dictionary <string, bool>();
foreach (var tableSource in tableSourceNamesList)
{
result.Add(tableSource, tableSourcesFound.Contains(tableSource.ToUpper()));
}
return(result);
}
private IDataReader GetTriggers(IDbConnection conn, IEnumerable <MySQLTableSourceInfo> tableSources)
{
MySQLDatabaseInfo dbInfo = databaseServices.ObjectFactory.CreateLocalDatabaseInfo() as MySQLDatabaseInfo;
if (dbInfo == null)
{
return(null);
}
string tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
string sql = string.Format(@"select trig.TRIGGER_NAME trigger_name, trig.EVENT_OBJECT_TABLE table_name
from INFORMATION_SCHEMA.TRIGGERS trig
where trig.EVENT_OBJECT_SCHEMA = '{0}' and trig.EVENT_OBJECT_TABLE IN ({1}) and UPPER(trig.TRIGGER_NAME) LIKE '{2}%'", dbInfo.Name,
tableNames, PlatformDatabaseObjectConstants.EventTriggerPrefix);
IDbCommand cmd = databaseServices.ExecutionService.CreateCommand(conn, sql);
return(databaseServices.ExecutionService.ExecuteReader(cmd));
}
public int GetDatabaseObjectsDefinitionHash(string objectNameFilter)
{
int hashResult = -1;
MySQLDatabaseInfo dbInfo = databaseServices.ObjectFactory.CreateLocalDatabaseInfo() as MySQLDatabaseInfo;
if (dbInfo == null)
{
return(hashResult);
}
using (IDbConnection conn = databaseServices.TransactionService.CreateConnection()) {
string columnSql = string.Format(@"(select crc32(group_concat(crc32(concat_ws(
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION,
CHARACTER_SET_NAME,
COLLATION_NAME,
COLUMN_TYPE,
COLUMN_KEY,
EXTRA,
PRIVILEGES,
COLUMN_COMMENT))
SEPARATOR '.'))
from information_schema.COLUMNS
where TABLE_SCHEMA = '{0}'
and TABLE_NAME like '%{1}%'
order by TABLE_NAME, COLUMN_NAME)", dbInfo.Name, objectNameFilter);
string viewDefinitionSql = string.Format(@"(select crc32(group_concat(crc32(concat_ws(
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
VIEW_DEFINITION,
CHECK_OPTION,
IS_UPDATABLE,
DEFINER,
SECURITY_TYPE,
CHARACTER_SET_CLIENT,
COLLATION_CONNECTION))
SEPARATOR '.'))
from information_schema.VIEWS
where TABLE_SCHEMA = '{0}'
and TABLE_NAME like '%{1}%'
order by TABLE_NAME)", dbInfo.Name, objectNameFilter);
string indexSql = string.Format(@"(select crc32(group_concat(crc32(concat_ws(
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
NON_UNIQUE,
INDEX_SCHEMA,
INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
SUB_PART,
PACKED,
NULLABLE,
INDEX_TYPE,
COMMENT,
INDEX_COMMENT))
SEPARATOR '.'))
from information_schema.STATISTICS
where TABLE_SCHEMA = '{0}'
and TABLE_NAME like '%{1}%'
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX)", dbInfo.Name, objectNameFilter);
int columnHash;
using (IDbCommand cmd = databaseServices.ExecutionService.CreateCommand(conn, columnSql)) {
cmd.CommandTimeout = QueryTimeout;
columnHash = Convert.ToString(databaseServices.ExecutionService.ExecuteScalar(cmd)).GetHashCode();
}
int viewDefinitionsHash;
using (IDbCommand cmd = databaseServices.ExecutionService.CreateCommand(conn, viewDefinitionSql)) {
cmd.CommandTimeout = QueryTimeout;
viewDefinitionsHash = Convert.ToString(databaseServices.ExecutionService.ExecuteScalar(cmd)).GetHashCode();
}
int indexHash;
using (IDbCommand cmd = databaseServices.ExecutionService.CreateCommand(conn, indexSql)) {
cmd.CommandTimeout = QueryTimeout;
indexHash = Convert.ToString(databaseServices.ExecutionService.ExecuteScalar(cmd)).GetHashCode();
}
string meshedHashes = columnHash + "" + viewDefinitionsHash + "" + indexHash;
hashResult = meshedHashes.GetHashCode();
}
return(hashResult);
}
