public static Dictionary <string, List <string> > Get_SimilarColumns(ColumnBasicProperties _CBP)
{
Dictionary <string, List <string> > _Ret = new Dictionary <string, List <string> > ();
string sql_cmd_str = $@"
SELECT
'['+CS.TABLE_SCHEMA+'].['+CS.TABLE_NAME+']',
CS.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS CS
LEFT JOIN INFORMATION_SCHEMA.TABLES TS
ON CS.TABLE_SCHEMA=TS.TABLE_SCHEMA AND CS.TABLE_NAME=TS.TABLE_NAME
WHERE
CS.DATA_TYPE = '{_CBP.Datatype}' AND
CS.CHARACTER_MAXIMUM_LENGTH {(_CBP.CharMaxLen.HasValue ? $"= {_CBP.CharMaxLen.Value}" : $"IS NULL")} AND
CS.CHARACTER_OCTET_LENGTH {(_CBP.CharOctLen.HasValue ? $"= {_CBP.CharOctLen.Value}" : $"IS NULL")} AND
CS.NUMERIC_PRECISION {(_CBP.NumPrecision.HasValue ? $"= {_CBP.NumPrecision.Value}" : $"IS NULL")} AND
CS.NUMERIC_PRECISION_RADIX {(_CBP.NumPrecisionRad.HasValue ? $"= {_CBP.NumPrecisionRad.Value}" : $"IS NULL")} AND
CS.NUMERIC_SCALE {(_CBP.NumScale.HasValue ? $"= {_CBP.NumScale.Value}" : $"IS NULL")} AND
CS.DATETIME_PRECISION {(_CBP.DTPrecision.HasValue ? $"= {_CBP.DTPrecision.Value}" : $"IS NULL")} AND
CS.TABLE_SCHEMA+CS.TABLE_NAME+CS.COLUMN_NAME NOT IN (
SELECT CCU.TABLE_SCHEMA+CCU.TABLE_NAME+CCU.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_SCHEMA+TC.CONSTRAINT_NAME=CCU.CONSTRAINT_SCHEMA+CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
AND TS.TABLE_TYPE = 'BASE TABLE';
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
while (_DR.Read())
{
string _rel = _DR.IsDBNull(0) ? null : _DR.GetString(0);
string _atr = _DR.IsDBNull(1) ? null : _DR.GetString(1);
if (string.IsNullOrWhiteSpace(_rel) || string.IsNullOrWhiteSpace(_atr))
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
if (_Ret.Keys.Contains(_rel))
{
_Ret[_rel].Add(_atr);
}
else
{
_Ret.Add(_rel, new List <string>(new string[] { _atr }));
}
}
_DR.Close();
return(_Ret);
}
public static AErgRelationen Analysis(string Database)
{
AErgRelationen _Ret = new AErgRelationen(Database);
_Ret.DocumentedReferences = DPRelationen_Helper.Get_DocumentedReferences();
Dictionary <string, PossibleKey> RelationsWithKeys = DPRelationen_Helper.Find_RelationsWithKeys();
int _i = 0;
//Primary Key required for Foreign Key
if (RelationsWithKeys.Count > 0)
{
_Ret.FoundReferences = new List <PossibleReference>();
foreach (string _r in RelationsWithKeys.Keys)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name} - {_i}/{RelationsWithKeys.Count} - {_r}");
//Only test keys with 1 Attribute - - Double cross to complex
if (RelationsWithKeys[_r].Attributes.Count == 1)
{
string _a = RelationsWithKeys[_r].Attributes.First();
ColumnBasicProperties _a_p = DPRelationen_Helper.Get_ColumnBasicProps(_r, _a);
int _j = 0, _v = 0;
Dictionary <string, List <string> > _a_similar = DPRelationen_Helper.Get_SimilarColumns(_a_p);
_a_similar.Remove(_r);
/*Filter out unfitting*/
//_a_similar = DPRelationen_Helper.Remove_MinMax(_a_similar, DPAnalysis.AttributAnalyse_Results_Sort[_r][_a]);
//Not used because valid Orphans could be included in this
foreach (string __r in _a_similar.Keys)
{
_v = 0;
foreach (string __a in _a_similar[__r])
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name} - {_i}/{RelationsWithKeys.Count} - {_r} - Checking {_j} ({__r}) on {_v}({__a})");
if (DPAnalysis.AttributAnalyse_Results_Sort[__r][__a].Count_Attribute > 0)
{
_Ret.FoundReferences.Add(DPRelationen_Helper.Test_Reference(RelationsWithKeys[_r], __r, __a));
}
_v++;
}
_j++;
}
}
_i++;
}
_Ret.FoundReferences.Sort((a, b) => a.GetEvaluation().CompareTo(b.GetEvaluation()));
}
return(_Ret);
}
public static ColumnBasicProperties Get_ColumnBasicProps(string Relation, string Attribute)
{
ColumnBasicProperties _Ret = new ColumnBasicProperties();
string sql_cmd_str = $@"
SELECT
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' = '{Relation}' AND COLUMN_NAME = '{Attribute}';
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
_Ret.Datatype = _DR.IsDBNull(0) ? null : _DR.GetString(0);
_Ret.CharMaxLen = _DR.IsDBNull(1) ? (int?)null : _DR.GetInt32(1);
_Ret.CharOctLen = _DR.IsDBNull(2) ? (int?)null : _DR.GetInt32(2);
_Ret.NumPrecision = _DR.IsDBNull(3) ? (byte?)null : _DR.GetByte(3);
_Ret.NumPrecisionRad = _DR.IsDBNull(4) ? (short?)null : _DR.GetInt16(4);
_Ret.NumScale = _DR.IsDBNull(5) ? (int?)null : _DR.GetInt32(5);
_Ret.DTPrecision = _DR.IsDBNull(6) ? (short?)null : _DR.GetInt16(6);
_DR.Close();
if (string.IsNullOrWhiteSpace(_Ret.Datatype))
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
return(_Ret);
}
