public static DbTable Materialize(IDbConnection connection, string name, int depth, char schemaSeperator, string[] ignorePrefixes)
{
Log.InfoFormat("Examining {0} with depth of {1}", name, depth);
var result = new DbTable
{
Fields = new List <FieldDefinition>(),
SampleData = new DataTable()
};
var splits = name.Split('.');
if (splits.Length > 1)
{
result.Schema = splits[0];
result.Name = splits[1];
}
else
{
result.Name = name;
}
//todo oracle dependency
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"select num_rows from all_tables where owner=:owner and table_name=:tablename";
cmd.Parameters.Add(DbTools.CreateParameter(cmd, "owner", result.Schema.ToUpper()));
cmd.Parameters.Add(DbTools.CreateParameter(cmd, "tablename", result.Name.ToUpper()));
var count = (long)(cmd.ExecuteScalar() as decimal? ?? default(decimal));
result.NumberOfRows = count;
}
result.SearchDepth = depth;
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = string.Format(@"
select * from {0} where rownum<{1}",
name, result.SearchDepth + 1);
using (var reader = cmd.ExecuteReader())
{
var wizard = new Wizard()
{
IgnorePrefixes = ignorePrefixes
};
result.Fields.Clear();
foreach (var item in wizard.GetFields(reader, name))
{
item.TableName = result.Name;
item.TableSchema = result.Schema;
result.Fields.Add(item);
}
result.SampleData.Load(reader);
foreach (DataRow row in result.SampleData.Rows)
{
foreach (var field in result.Fields.Where(o => o.Type.StartsWith("short")))
{
field.IsShortBool = true;
var val = row[field.DbFieldName] as short? ?? default(short);
if (val > 1 || val < -1)
{
field.IsShortBool = false;
}
}
}
}
}
return(result);
}
public static ExplorerResults Explore(IDbConnection connection, IEnumerable <string> tableNames, int searchDepth, string[] ignorePrefixes)
{
var schemaSeperator = ".";
var paramIdent = ":";
var result = tableNames.Select(name => DbTable.Materialize(connection, name, searchDepth, schemaSeperator[0], ignorePrefixes)).ToList();
var idx = 0;
//Look for relationships
foreach (var table in result)
{
idx++;
Log.InfoFormat("Working with {0} of {1}/{2}", table.Name, idx, result.Count);
table.Relationships = new List <DbRelationship>();
var id = table.Fields.FirstOrDefault(o => o.Name == "Id");
if (id != null && id.DbFieldName != "ID") //The Name ID is too generic. :(
{
var childTables = result.Where(o => o.Name != table.Name).ToArray();
foreach (var childTable in childTables)
{
var possibleFk =
childTable.Fields.FirstOrDefault(
o => o.DbFieldName.Equals(id.DbFieldName, StringComparison.OrdinalIgnoreCase));
if (possibleFk != null)
{
Log.InfoFormat("> Looking for children in {0} using {1}", childTable.Name, id.DbFieldName);
var isRelated = false;
var isMany = false;
foreach (DataRow row in table.SampleData.Rows)
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandTimeout = 60;
cmd.CommandText = string.Format(@"
select {3} from {0}{1}{2} where {3}={4}searchfor and rownum<{5}",
table.Schema, schemaSeperator, childTable.Name, possibleFk.DbFieldName,
paramIdent, table.SearchDepth + 1);
cmd.Parameters.Add(DbTools.CreateParameter(cmd, "searchFor", row[id.DbFieldName]));
try
{
var cnt = 0;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cnt++;
}
}
if (cnt > 0)
{
isRelated = true;
if (cnt > 1)
{
isMany = true;
break;
}
}
}
catch (Exception ex)
{
Log.ErrorFormat("SQL: \"{0}\" Exception: {1}", cmd.CommandText, ex);
//empty
}
}
}
if (isRelated)
{
table.Relationships.Add(new DbRelationship()
{
IsMany = isMany, //todo full joins? MANY to MANY???
Parent = id,
Child = possibleFk
});
}
}
}
}
}
return(new ExplorerResults()
{
Tables = result.ToArray()
});
}