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