void RankTablesByAffinity(string affinityTableName, int ct, double weight)
{
QList <string> top = GetTop(affinityTableName, ct);
int rank = ct;
foreach (string t in top.Each())
{
tables[t].importance += T.Normalize(rank--, 0, ct, weight);
}
}
public void AddAllColumnsInQuery()
{
QList <Column> list = new QList <Column>(QListSort.Descending);
foreach (Column c in Query.rootQuery.allColumns.tokens)
{
list.Add(c, c.significance);
}
foreach (Column c in list.Each())
{
AddColumn(c);
}
}
public static void LogTop(string table, QList <string> list, int ct)
{
Log("top " + table);
int i = 0;
foreach (string t in list.Each())
{
Log(" " + t + " " + list.GetPosition(t));
i++;
if (i >= ct)
{
break;
}
}
}
public override void GetSuggestions(SuggestionList s)
{
if (parentQuery.select == null)
{
return;
}
SuggestionContext sc = s.suggestionContext;
Table rightTable = null;
Table leftTable = null;
if (sc.last == null)
{
sc.suggestTables = true;
}
else
{
rightTable = sc.lastTable.token != null ? sc.lastTable.token as Table : null;
leftTable = rightTable == null ? null : sc.tokens.GetNearestTokenBefore(rightTable, TokenType.Table) as Table;
if (rightTable == null)
{
sc.suggestTables = true;
}
else if (sc.last.type.IsOneOf("conjunction"))
{
sc.suggestColumns = true;
}
else if (sc.last.type == "operator")
{
sc.suggestColumns = true;
if (sc.lastColumn.token != null)
{
Column c = sc.lastColumn.token as Column;
sc.suggestColumnsDataType = c.dbColumn.dataType;
}
}
else if (sc.last.type.IsOneOf("identifier", "column", "literal"))
{
sc.suggestOperators = sc.lastOperator.pos < sc.lastConjunction.pos;
sc.suggestPrimaryKeywords = sc.suggestConjunctions = sc.lastOperator.pos == 0 || sc.lastOperator.pos > sc.lastConjunction.pos;
sc.suggestJoin = sc.lastOperator.pos > sc.lastKeyword.pos;
}
else if (sc.last.type == "table")
{
sc.suggestPrimaryKeywords = (sc.lastKeyword.isJoin == false);
if (sc.last.hasComma)
{
sc.suggestTables = true;
}
else
{
sc.suggestAlias = true;
sc.suggestJoin = sc.lastKeyword.isJoin == false;
sc.suggestJoinExpression = sc.suggestOn = !sc.suggestJoin;
}
}
else if (sc.last.isOn)
{
sc.suggestColumns = true;
sc.suggestJoinExpression = true;
}
else if (sc.last.isJoin)
{
sc.suggestTables = true;
}
}
if (sc.suggestJoinExpression && s.enableSuggestJoins && rightTable != null && leftTable != null)
{
string join = leftTable.dbTable.RenderJoinCols(rightTable.name, s.includeAliases);
if (join != null)
{
if (sc.suggestOn)
{
join = "on " + join;
}
s.Add(new Suggestion(TokenType.Expression, join));
}
}
if (sc.suggestJoin && s.enableSuggestKeywords)
{
s.AddKeyword("inner join", true);
}
if (sc.suggestOn && s.enableSuggestKeywords)
{
s.AddKeyword("on", true);
}
if (sc.suggestAlias && s.enableSuggestAliases && rightTable != null && rightTable.dbTable != null)
{
foreach (string alias in rightTable.dbTable.aliases.Keys)
{
s.Add(new Suggestion(TokenType.Identifier, alias));
}
s.Add(new Suggestion(TokenType.Identifier, Db.GetAliasForTable(rightTable.dbTable.name, false)));
}
if (sc.suggestColumns && s.enableSuggestColumns)
{
if (rightTable != null && rightTable.dbTable != null)
{
sc.AddColumnsInTable(leftTable, true);
sc.AddColumnsInTable(rightTable, true);
sc.AddColumnsInTable(leftTable);
sc.AddColumnsInTable(rightTable);
sc.AddColumnsInFromTables();
}
}
if (sc.suggestTables && s.enableSuggestTables)
{
QList <string> names = new QList <string>();
QList <string> list = new QList <string>();
foreach (Column c in parentQuery.select.columns.tokens)
{
if (c.table != null && c.table.dbTable != null && (c.columnType == Column.ColumnType.Db))
{
list.AddIfNotExists(c.table.dbTable.name);
}
sc.AddTable(c.tableName);
}
foreach (DbColumn c in Query.columnHints)
{
sc.AddTable(c.table.name);
}
if (rightTable != null && rightTable.dbTable != null)
{
/*
* if (rightTable.tableAlias != null)
* aliasFound = true;
* // need to sort these tables by most recently used
* names.MergeRange(Db.relationships.GetRelatedTables(rightTable.dbTable.name));
* foreach (string name in names.Each())
* sc.AddTable(name, aliasFound ? Db.GetAliasForTable(name, true) : "");
* names.Clear();
*/
}
if (s.textEntered.Trim() != "")
{
foreach (Column c in parentQuery.select.columns.tokens)
{
if ((c.tableAlias == null || c.columnType == Column.ColumnType.Proposed) &&
c.tableName != null && Db.tables.ContainsKey(c.tableName))
{
list.AddIfNotExists(c.tableName);
names.AddIfNotExists(c.tableName);
}
}
foreach (string srcTable in list.Each())
{
names.MergeRange(Db.TableNamesSortedByUsage(Db.tables[srcTable].accessibleTableNames));
}
names.MergeRange(Db.tables.Keys);
foreach (string name in names.Each())
{
sc.AddTable(name);
}
}
}
}
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);
}