protected void DetectRelations(CsvDatabase db)
{
//limit maximum relations count
int maximumRelationsCount = db.Tables.Count * db.Tables.Count;
var tcl = (
from tab in db.Tables
from col in tab.Columns
where col.CsvColumnName.EndsWith("id", StringComparison.OrdinalIgnoreCase)
select new { tab, col }
).ToLookup(k => k.col.CsvColumnName, v => v, StringComparer.OrdinalIgnoreCase);
// t1.nameID -> name.ID
// t1.nameID -> names.ID
// t1.nameID -> name.nameID
// t1.nameID -> names.nameID
// t1.fishID -> fishes.fishID
// t1.fishID -> fishes.ID
var r1 = (
from t1 in db.Tables
let keyNamesForeign = GetTableForeignKeyPossibleNames(t1)
let keyNames = keyNamesForeign.Concat(new [] { "id" })
from c1 in t1.Columns
where keyNames.Contains(c1.CsvColumnName, StringComparer.OrdinalIgnoreCase)
from tc2 in keyNamesForeign.SelectMany(k => tcl[k])
where tc2.tab != t1
select new { t1, c1, t2 = tc2.tab, c2 = tc2.col }
);
//translate to relations
var relations = (
from r in
(
from r in (new[] { r1 }).SelectMany(r => r).Take(maximumRelationsCount)
select new[] { r, new { t1 = r.t2, c1 = r.c2, t2 = r.t1, c2 = r.c1 } } //add reverse direction
).SelectMany(r => r).Distinct()
select new CsvRelation()
{
CodeName = r.t2.CodeName,
SourceTable = r.t1,
SourceColumn = r.c1,
TargetTable = r.t2,
TargetColumn = r.c2,
}
);
//add relations to DB structure
int relationCount = 0;
foreach (var relationsGroup in relations.GroupBy(r => r.SourceTable))
{
foreach (var relation in relationsGroup)
{
relationsGroup.Key.Relations.Add(relation);
relationCount++;
}
}
Logger.Log("Relations detected {0} {1}", relationCount, relationCount >= maximumRelationsCount ? "Maximum limit reached" : "");
}
public CsvDatabase CreateModel()
{
string[] files = FileUtils.EnumFiles(Properties.Files.Split('\n'));
string baseDir = FileUtils.GetLongestCommonPrefixPath(files);
//create db structure
var db = new CsvDatabase()
{
Name = baseDir,
Tables = (
from file in files
where File.Exists(file)
let csvSeparator = Properties.CsvSeparatorChar ?? FileUtils.CsvDetectSeparator(file)
where !Properties.IgnoreInvalidFiles || FileUtils.CsvIsFormatValid(file, csvSeparator)
let fileName = Path.GetFileName(file)
let fileDir = (Path.GetDirectoryName(file.Remove(0, baseDir.Length) + "x") ?? "").TrimStart(Path.DirectorySeparatorChar)
select new CsvTable()
{
FilePath = file,
CodeName = CodeGenHelper.GetSafeCodeName(Path.GetFileNameWithoutExtension(fileName) + (string.IsNullOrWhiteSpace(fileDir) ? "" : ("_" + fileDir))),
DisplayName = fileName + (string.IsNullOrWhiteSpace(fileDir) ? "" : (" in " + fileDir)) + " " + FileUtils.GetFileSizeInfo(file) + "",
CsvSeparator = csvSeparator,
Columns = (
from col in FileUtils.CsvReadHeader(file, csvSeparator).Select((value, index) => new { value, index })
select new CsvColumn()
{
CodeName = CodeGenHelper.GetSafeCodeName(col.value),
DisplayName = "",
CsvColumnName = col.value ?? "",
CsvColumnIndex = col.index,
}
).ToList(),
}
).ToList(),
};
//unique code names
MakeCodeNamesUnique(db.Tables, t => t.CodeName, (t, n) => t.CodeName = n);
foreach (var table in db.Tables)
{
MakeCodeNamesUnique(table.Columns, c => c.CodeName, (c, n) => c.CodeName = n);
}
//relations
if (Properties.DetectRelations)
{
DetectRelations(db);
foreach (var table in db.Tables)
{
MakeCodeNamesUnique(table.Relations, c => c.CodeName, (c, n) => c.CodeName = n, table.Columns.Select(c => c.CodeName));
}
}
//adjust displaynames
foreach (var x in db.Tables)
{
x.DisplayName = x.CodeName + (string.IsNullOrWhiteSpace(x.DisplayName) ? "" : " (" + x.DisplayName + ")");
}
foreach (var x in db.Tables.SelectMany(t => t.Columns))
{
x.DisplayName = x.CodeName + (string.IsNullOrWhiteSpace(x.DisplayName) ? "" : " (" + x.DisplayName + ")");
}
foreach (var x in db.Tables.SelectMany(t => t.Relations))
{
x.DisplayName = x.CodeName + (string.IsNullOrWhiteSpace(x.DisplayName) ? "" : " (" + x.DisplayName + ")");
}
return(db);
}
/// <summary>
/// Get LINQPad Schema from CSV data model
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private static List<ExplorerItem> GetSchema(CsvDatabase db, ICsvDataContextDriverProperties props)
{
var schema = (
from table in db.Tables ?? Enumerable.Empty<CsvTable>()
select new ExplorerItem(table.DisplayName, ExplorerItemKind.QueryableObject, ExplorerIcon.Table) {
DragText = table.CodeName,
IsEnumerable = true,
ToolTipText = table.FilePath,
Children = (
from c in table.Columns ?? Enumerable.Empty<CsvColumn>()
select new ExplorerItem(c.DisplayName, ExplorerItemKind.Property, ExplorerIcon.Column)
{
DragText = c.CodeName,
ToolTipText = (c.CsvColumnIndex + 1) + ":" + c.CsvColumnName,
}
).Concat(
from r in table.Relations
select new ExplorerItem(r.DisplayName, ExplorerItemKind.CollectionLink, ExplorerIcon.ManyToMany)
{
DragText = r.CodeName,
ToolTipText = string.Format("Relation to {0} where {1}.{2} == {3}.{4}", r.TargetTable.CodeName, r.SourceTable.CodeName, r.SourceColumn.CodeName, r.TargetTable.CodeName, r.TargetColumn.CodeName ),
}
).ToList(),
}
).ToList();
return schema;
}