protected override void AnalyzeStructure()
{
using (QSqlBase s = GetSql())
{
s.Open(@"
select table_name,row_format,table_rows,avg_row_length,data_length,index_length
from information_schema.tables
where table_type='BASE TABLE' and table_schema='" + databaseName + "'");
while (s.GetRow())
{
DbTable dbTable = tables[s["table_name"]];
dbTable.tableRows = s.GetInt(2);
dbTable.avgRowLength = s.GetInt(3);
dbTable.dataLength = s.GetInt(4);
dbTable.indexLength = s.GetInt(5);
// tables.Add(dbTable.name, table);
}
s.Open(@"
select c.table_name,c.constraint_name,c.constraint_type,s.non_unique,s.seq_in_index,s.column_name,s.cardinality,s.index_type
from information_schema.table_constraints c
inner join information_schema.statistics s on s.table_name=c.table_name and c.table_schema=s.table_schema
and s.index_name=c.constraint_name
where c.table_schema = '" + databaseName + "' order by c.table_name, s.seq_in_index");
while (s.GetRow())
{
DbTableConstraint constraint = tables[s["table_name"]].GetOrAddConstraint(s["constraint_name"], s["constraint_type"]);
constraint.AddColumn(constraint.dbTable.columns[s["column_name"]], s.GetInt("seq_in_index"), s.GetBool("non_unique"), s.GetInt("cardinality"));
}
s.Open(@"
select constraint_name,table_name,column_name,ordinal_position,position_in_unique_constraint,
referenced_table_name,referenced_column_name
from information_schema.KEY_COLUMN_USAGE
where table_schema= '" + databaseName + "'");
while (s.GetRow())
{
DbTableConstraint constraint = tables[s["table_name"]].GetOrAddConstraint(s["constraint_name"], "FOREIGN_KEY");
DbTable refTable = s["referenced_table_name"] != "" ? tables[s["referenced_table_name"]] : null;
if (refTable != null)
{
constraint.AddReference(s["column_name"], refTable, s["referenced_column_name"], s.GetInt("ordinal_position"),
s.GetInt("position_in_unique_constraint"));
}
}
}
}
public int FindInferredRelationships()
{
DbColumn primaryKeyColumn = GetSinglePrimaryKeyColumn();
if (primaryKeyColumn == null)
{
return(0);
}
QList <DbColumn> references = new QList <DbColumn>(QListSort.Descending);
foreach (DbColumn c in DbColumn.allNames.Each(name.ToLower()))
{
references.Add(c, 10);
}
foreach (DbColumn c in DbColumn.allNameStems.Each(name.Stem().ToLower()))
{
references.Add(c, 7);
}
foreach (DbColumn c in DbColumn.allWords.Each(name.ToLower()))
{
if (c.likeIdentifier)
{
references.Add(c, 4 - c.objectNameWords.Count);
}
}
foreach (DbColumn c in DbColumn.allWordStems.Each(name.Stem().ToLower()))
{
if (c.likeIdentifier)
{
references.Add(c, 3 - c.objectNameWords.Count);
}
}
/*
* if (!primaryKeyColumn.objectName.In("id,code,name,label"))
* {
* if (primaryKeyColumn.dataType == typeof(int) || primaryKeyColumn.dataType == typeof(string))
* {
* foreach (DbColumn c in DbColumn.allNames.Each(primaryKeyColumn.objectName.ToLower()))
* references.Add(c, 5);
* }
* }
*/
// todo: should these fields have zero empty values?
int ct = 0;
List <string> tablesAdded = new List <string>();
foreach (DbColumn c in references.Each())
{
if (c != primaryKeyColumn && !tablesAdded.Contains(c.objectName))
{
if (c.dataType == primaryKeyColumn.dataType && c.columnLength == primaryKeyColumn.columnLength)
{
if (c.table.GetForeignKey(objectName, primaryKeyColumn.objectName) == null)
{
DbTableConstraint constraint = c.table.GetOrAddConstraint("inferred" + objectName, "inferred");
constraint.AddInferredRelationship(c.objectName, this, primaryKeyColumn);
tablesAdded.Add(c.table.name);
ct++;
}
}
}
}
return(ct);
}
public TablePath(DbTableConstraint c, TablePath p, bool r = false)
{
constraint = c;
isReference = r;
pathThrough = p;
}
public DbTableConstraintColumn(DbTableConstraint con, DbColumn c)
{
constraint = con;
dbColumn = c;
c.constraints.Add(this);
}