public DbObjectTree(string name, string type, DbObjectTree parent, List <DbObjectTree> children)
{
this.name = name;
this.objtype = type;
this.parent = parent;
this.children = children;
}
public void GroupChildrenBySchema()
{
DbObjectTree cschema;
int idx;
List <DbObjectTree> schemas = new List <DbObjectTree>();
foreach (DbObjectTree child in children)
{
child.GroupChildrenBySchema();
idx = schemas.FindIndex(x => x.name.ToUpper() == child.database.ToUpper());
if (idx < 0)
{
cschema = new DbObjectTree(child.database, "D", this, new List <DbObjectTree>());
schemas.Add(cschema);
}
else
{
cschema = schemas[idx];
}
cschema.children.Add(child);
}
this.children = schemas;
/*
* var groupedCustomerList = children
* .GroupBy(u => u.schema)
* .Select(grp => grp.ToList())
* .ToList();
*/
}
public DbObjectTree(DbObject obj, DbObjectTree parent)
{
this.name = obj.Name;
this.objtype = obj.Type;
this.database = obj.Database;
this.parent = parent;
this.children = new List <DbObjectTree>();
}
public DbObjectTree getDbObjectTree()
//list of the all objects
{
DbObjectTree dot = new DbObjectTree("(all)", "R", null, null); //root level - no parent
List <DbObjectTree> tree = DbObjectBus.Values
//must be some filter here like: .Where(d => d.Sources.Count == 0)
.Select(d => getChildSubTree(d, dot))
.ToList();
dot.children = tree;
return(dot);
}
public DbObjectTree getDbObjectTreeFlowEnd()
//list of the objects rooted by the objects which have no targets (the last ones in the data flow chain)
{
DbObjectTree dot = new DbObjectTree("(dfe)", "R", null, null); //root level - no parent
List <DbObjectTree> tree = DbObjectBus.Values
.Where(d => d.Targets.Count == 0)
.Select(d => getChildSubTree(d, dot))
.ToList();
dot.children = tree;
return(dot);
}
private DbObjectTree getChildSubTree(DbObject child, DbObjectTree parent)
{
DbObjectTree tree = new DbObjectTree(child, parent);
//this checks for circular references otherwise the tree will never end
if (!parent.ParentExists(child.Name))
{
foreach (string src_name in DbObjectBus[child.FullName].Sources.Keys)
{
tree.children.Add(getChildSubTree(DbObjectBus[src_name], tree));
}
}
return(tree);
}
static void readTeradata()
{
string db_name = @"DPRD_SSL_MDM_V"; // @"LabBICC_Test";LabBICC_FIN_DYI
string queryTemplate = @"
SELECT TableName, tablekind, case tablekind when 'V' then RequestText else null end as RequestText
FROM dbc.tablesv
WHERE tablekind in ('V', 'T') AND databasename IN ('{0}')
AND TableName = 'vD_GeoSite'
--AND TableName LIKE 'vD_Equipment_%'
";
//and (TableName like 'MSBI_vF_Fixed%' or TableName like 'MSBI_vD_Report%')
// AND TableName like 'MSBI_%'
//in ('MSBI_vD_KeyFigureGroup', 'vD_KeyFigureGroup', 'vD_KeyFigureGroupCateg')
DbObjectMaster objMaster = new DbObjectMaster();
objMaster.DefaultDatabase = db_name;
string queryString = String.Format(queryTemplate, db_name);
TdConnection cn = new TdConnection();
string connectionString = @"Data Source=maersk6;Database=LabBICC_Test;User Id=UADL_BICC_LOADUSER;Password=Lab@BICC123;Connection Timeout=300;";
string obj_text = "", obj_name = "", obj_type = "";
using (TdConnection connection = new TdConnection(connectionString))
{
//connection.ConnectionTimeout = 300; //covered by connection string
TdCommand cmd = new TdCommand(queryString, connection);
cmd.CommandTimeout = 180;
//cmd.Parameters.Add(new TdParameter("@viewname", "MSBI_vD_Company"));
//cmd.CommandText = queryString;
Console.WriteLine("Acquiring the connection....");
connection.Open();
Console.WriteLine("Getting database object list....");
TdDataReader reader = cmd.ExecuteReader();
//Console.WriteLine("{0} tables found.", reader.RecordsAffected);
while (reader.Read())
{
obj_name = reader["TableName"].ToString().Trim();
obj_type = reader["tablekind"].ToString().Trim().ToUpper();
obj_text = reader["RequestText"].ToString().Trim();
//str = Convert.ToString(cmd.ExecuteScalar());
//str = (string)cmd.ExecuteScalar();
//obj_text = compressQueryText(obj_text);
//str = "[" + str + "]";
obj_name = obj_name.IndexOf(".") >= 0 ? obj_name : db_name + "." + obj_name;
//DbObject obj = new DbObject(obj_name, obj_type, objMaster, db_name, obj_text);
DbObject obj = objMaster.AddNew(obj_name, obj_type, db_name, obj_text);
Console.WriteLine(obj_name);
//Console.WriteLine(obj_text);
Console.WriteLine("::: source objects :::");
foreach (DbObject src in obj.Sources.Values)
{
Console.WriteLine(src.Name);
}
Console.WriteLine("*******************************************");
//objMaster.Add(obj);
}
cmd.Dispose();
connection.Close();
}
objMaster.BuildReferences();
//var json = ApiResponse
//var json = JsonConvert.SerializeObject(objMaster);
//this gets all objects and user drills down to their sources (if any)
//this way some objects may appear in different branches of the tree
DbObjectTree tree = objMaster.getDbObjectTree();
//this starts from the objects that have no targets (no one is sourced from them)
//and user drills down to the sources, nvigating to the other objects this way
//DbObjectTree tree = objMaster.getDbObjectTreeFlowEnd();
tree.GroupChildrenBySchema();
tree.SortTree();
tree.AddIcons();
//tree.CleanParents();
var json = JsonConvert.SerializeObject(tree);
//Console.WriteLine(json);
File.WriteAllText(@"C:\TEMP\views.json", json);
//File.WriteAllText(@"\\SCRBADLDK003868\db\views.json", json);
}
