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; }