public virtual async Task <DiagnoseResult> DiagnoseSelfReferenceSame()
{
this.Feedback("Begin to diagnose self reference with same value...");
DiagnoseResult result = new DiagnoseResult();
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Details
};
DbInterpreter interpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.connectionInfo, option);
this.Feedback("Begin to get foreign keys...");
List <TableForeignKey> foreignKeys = await interpreter.GetTableForeignKeysAsync();
this.Feedback("End get foreign keys.");
var groups = foreignKeys.Where(item => item.ReferencedTableName == item.TableName)
.GroupBy(item => new { item.Owner, item.TableName });
using (DbConnection dbConnection = interpreter.CreateConnection())
{
foreach (var group in groups)
{
foreach (TableForeignKey foreignKey in group)
{
string countSql = this.GetTableColumnReferenceSql(interpreter, foreignKey, true);
this.Feedback($@"Begin to get invalid record count for foreign key ""{foreignKey.Name}"" of table ""{foreignKey.TableName}""...");
int count = Convert.ToInt32(await interpreter.GetScalarAsync(dbConnection, countSql));
this.Feedback($@"End get invalid record count for column ""{foreignKey.Name}"" of table ""{foreignKey.TableName}"", the count is {count}.");
if (count > 0)
{
result.Details.Add(new DiagnoseResultItem()
{
DatabaseObject = foreignKey,
RecordCount = count,
Sql = this.GetTableColumnReferenceSql(interpreter, foreignKey, false)
});
}
}
}
}
this.Feedback("End diagnose self reference with same value.");
return(result);
}
public virtual async Task <DiagnoseResult> DiagnoseNotNullWithEmpty()
{
this.Feedback("Begin to diagnose not null fields with empty value...");
DiagnoseResult result = new DiagnoseResult();
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DbInterpreter interpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.connectionInfo, option);
this.Feedback("Begin to get table columns...");
List <TableColumn> columns = await interpreter.GetTableColumnsAsync();
this.Feedback("End get table columns.");
var groups = columns.Where(item => DataTypeHelper.IsCharType(item.DataType) && !item.IsNullable)
.GroupBy(item => new { item.Owner, item.TableName });
using (DbConnection dbConnection = interpreter.CreateConnection())
{
foreach (var group in groups)
{
foreach (TableColumn column in group)
{
string countSql = this.GetTableColumnEmptySql(interpreter, column, true);
this.Feedback($@"Begin to get invalid record count for column ""{column.Name}"" of table ""{column.TableName}""...");
int count = Convert.ToInt32(await interpreter.GetScalarAsync(dbConnection, countSql));
this.Feedback($@"End get invalid record count for column ""{column.Name}"" of table ""{column.TableName}"", the count is {count}.");
if (count > 0)
{
result.Details.Add(new DiagnoseResultItem()
{
DatabaseObject = column,
RecordCount = count,
Sql = this.GetTableColumnEmptySql(interpreter, column, false)
});
}
}
}
}
this.Feedback("End diagnose not null fields with empty value.");
return(result);
}
