private ITableSource FindOrCreateJoinTable(ITableSource ownerTable, IRefEntityProperty refProperty)
{
var entityQuery = _query as TableQuery;
var refTableSource = entityQuery.AllJoinTables.FirstOrDefault(
ts => ts.OwnerTable == ownerTable && ts.Property == refProperty
);
if (refTableSource == null)
{
var refEntityType = refProperty.RefEntityType;
var refRepo = RepositoryFactoryHost.Factory.FindByEntity(refEntityType);
var refTable = f.Table(refRepo, NextTableAlias());
var joinType = refProperty.Nullable ? JoinType.LeftOuter : JoinType.Inner;
_query.From = f.Join(_query.From, refTable, f.Constraint(
ownerTable.Column(refProperty.RefIdProperty),
refTable.Column(Entity.IdProperty)
), joinType);
refTableSource = new SqlTableSource
{
OwnerTable = ownerTable,
Property = refProperty,
RefTable = refTable,
};
entityQuery.AllJoinTables.Add(refTableSource);
}
return(refTableSource.RefTable);
}
bool IsDependedOnJoin(SqlTableSource table, SqlJoinedTable testedJoin, HashSet <int> testedSources)
{
var dependent = false;
var currentSourceId = testedJoin.Table.SourceID;
// check everything that can be dependent on specific table
new QueryVisitor().VisitParentFirst(testedJoin, e =>
{
if (dependent)
{
return(false);
}
if (e is ISqlExpression expression)
{
var field = GetUnderlayingField(expression);
if (field != null)
{
var newField = GetNewField(field);
var local = testedSources.Contains(newField.SourceID);
if (local)
{
dependent = !CanWeReplaceField(table, newField, testedSources, currentSourceId);
}
}
}
return(!dependent);
});
return(dependent);
}
void RemoveSource(SqlTableSource fromTable, SqlJoinedTable join)
{
if (_removedSources == null)
{
_removedSources = new HashSet <int>();
}
_removedSources.Add(join.Table.SourceID);
if (_equalityMap != null)
{
var keys = _equalityMap.Keys.Where(k => k.SourceID == join.Table.SourceID).ToArray();
foreach (var key in keys)
{
var newField = MapToSource(fromTable, key, fromTable.SourceID);
if (newField != null)
{
ReplaceField(key, newField);
}
_equalityMap.Remove(key);
}
}
ResetFieldSearchCache(join.Table);
}
List <FoundEquality> SearchForFields(SqlTableSource manySource, SqlJoinedTable join)
{
var key = Tuple.Create(manySource, join.Table);
List <FoundEquality> found = null;
if (_fieldPairCache != null && _fieldPairCache.TryGetValue(key, out found))
{
return(found);
}
for (var i1 = 0; i1 < join.Condition.Conditions.Count; i1++)
{
var c = join.Condition.Conditions[i1];
if (c.IsOr)
{
found = null;
break;
}
if (c.ElementType != QueryElementType.Condition ||
c.Predicate.ElementType != QueryElementType.ExprExprPredicate ||
((SqlPredicate.ExprExpr)c.Predicate).Operator != SqlPredicate.Operator.Equal)
{
continue;
}
var predicate = (SqlPredicate.ExprExpr)c.Predicate;
var equality = new FoundEquality();
if (!MatchFields(manySource, join.Table,
GetUnderlayingField(predicate.Expr1),
GetUnderlayingField(predicate.Expr2),
equality))
{
continue;
}
equality.OneCondition = c;
if (found == null)
{
found = new List <FoundEquality>();
}
found.Add(equality);
}
if (_fieldPairCache == null)
{
_fieldPairCache = new Dictionary <Tuple <SqlTableSource, SqlTableSource>, List <FoundEquality> >();
}
_fieldPairCache.Add(key, found);
return(found);
}
bool MatchFields(SqlTableSource manySource, SqlTableSource oneSource, VirtualField field1, VirtualField field2, FoundEquality equality)
{
if (field1 == null || field2 == null)
{
return(false);
}
DetectField(manySource, oneSource, field1, equality);
DetectField(manySource, oneSource, field2, equality);
return(equality.OneField != null && equality.ManyField != null);
}
void DetectField(SqlTableSource?manySource, SqlTableSource oneSource, VirtualField field, FoundEquality equality)
{
field = GetNewField(field);
if (oneSource.Source.SourceID == field.SourceID)
{
equality.OneField = field;
}
else if (oneSource.Source is SelectQuery select && select.Select.From.Tables.Count == 1 && select.Select.From.Tables[0].SourceID == field.SourceID)
{
equality.OneField = field;
}
void ReplaceSource(SqlTableSource fromTable, SqlJoinedTable oldSource, SqlTableSource newSource)
{
var oldFields = GetFields(oldSource.Table.Source);
var newFields = GetFields(newSource.Source);
foreach (var old in oldFields)
{
var newField = newFields[old.Key];
ReplaceField(old.Value, newField);
}
RemoveSource(fromTable, oldSource);
}
void ResetFieldSearchCache(SqlTableSource table)
{
if (_fieldPairCache == null)
{
return;
}
var keys = _fieldPairCache.Keys.Where(k => k.Item2 == table || k.Item1 == table).ToArray();
foreach (var key in keys)
{
_fieldPairCache.Remove(key);
}
}
List <VirtualField[]> GetKeys(SqlTableSource tableSource)
{
if (_keysCache == null || !_keysCache.TryGetValue(tableSource.SourceID, out var keys))
{
keys = GetKeysInternal(tableSource);
if (_keysCache == null)
{
_keysCache = new Dictionary <int, List <VirtualField[]> >();
}
_keysCache.Add(tableSource.SourceID, keys);
}
return(keys);
}
void DetectField(SqlTableSource manySource, SqlTableSource oneSource, VirtualField field, FoundEquality equality)
{
field = GetNewField(field);
if (oneSource.Source.SourceID == field.SourceID)
{
equality.OneField = field;
}
else if (manySource.Source.SourceID == field.SourceID)
{
equality.ManyField = field;
}
else
{
equality.ManyField = MapToSource(manySource, field, manySource.Source.SourceID);
}
}
static IEnumerable <T> QueryTable <T>(IDataContext dataContext)
{
var query = new SqlSelectStatement();
var table = new SqlTable(typeof(T));
var tableSource = new SqlTableSource(table, "t");
query.SelectQuery.From.Tables.Add(tableSource);
var connection = (DataConnection)dataContext;
var sqlBuilder = connection.DataProvider.CreateSqlBuilder(connection.MappingSchema);
var sb = new StringBuilder();
sqlBuilder.BuildSql(0, query, sb, new OptimizationContext(new EvaluationContext(), new AliasesContext(), false));
return(connection.Query <T>(sb.ToString()));
}
static IEnumerable <T> QueryTable <T>(IDataContext dataContext)
{
var query = new SqlSelectStatement();
var table = new SqlTable(typeof(T));
var tableSource = new SqlTableSource(table, "t");
query.SelectQuery.From.Tables.Add(tableSource);
var connection = (DataConnection)dataContext;
var sqlBuilder = connection.DataProvider.CreateSqlBuilder();
var sb = new StringBuilder();
sqlBuilder.BuildSql(0, query, sb);
return(connection.Query <T>(sb.ToString()));
}
bool IsDependedBetweenJoins(SqlTableSource table, SqlJoinedTable testedJoin)
{
var testedSources = new HashSet <int>(testedJoin.Table.GetTables().Select(t => t.SourceID));
foreach (var tableJoin in table.Joins)
{
if (testedSources.Contains(tableJoin.Table.SourceID))
{
continue;
}
if (IsDependedOnJoin(table, tableJoin, testedSources))
{
return(true);
}
}
return(IsDependedExcludeJoins(testedSources));
}
VirtualField MapToSourceInternal(SqlTableSource fromTable, VirtualField field, int sourceId, HashSet <VirtualField> visited)
{
if (visited.Contains(field))
{
return(null);
}
if (field.SourceID == sourceId)
{
return(field);
}
visited.Add(field);
if (_equalityMap == null)
{
return(null);
}
var sourceIndex = GetSourceIndex(fromTable, sourceId);
HashSet <Tuple <int, VirtualField> > sameFields;
if (_equalityMap.TryGetValue(field, out sameFields))
{
foreach (var pair in sameFields)
{
var itemIndex = GetSourceIndex(fromTable, pair.Item1);
if (itemIndex >= 0 && (sourceIndex == 0 || itemIndex < sourceIndex))
{
var newField = MapToSourceInternal(fromTable, pair.Item2, sourceId, visited);
if (newField != null)
{
return(newField);
}
}
}
}
return(null);
}
bool CanWeReplaceFieldInternal(SqlTableSource table, VirtualField field, HashSet <int> excludeSourceIds,
int testedSourceIndex, HashSet <VirtualField> visited)
{
if (visited.Contains(field))
{
return(false);
}
if (!excludeSourceIds.Contains(field.SourceID) && !IsSourceRemoved(field.SourceID))
{
return(true);
}
visited.Add(field);
if (_equalityMap == null)
{
return(false);
}
if (testedSourceIndex < 0)
{
return(false);
}
HashSet <Tuple <int, VirtualField> > sameFields;
if (_equalityMap.TryGetValue(field, out sameFields))
{
foreach (var pair in sameFields)
{
if ((testedSourceIndex == 0 || GetSourceIndex(table, pair.Item1) > testedSourceIndex) &&
CanWeReplaceFieldInternal(table, pair.Item2, excludeSourceIds, testedSourceIndex, visited))
{
return(true);
}
}
}
return(false);
}
int GetSourceIndex(SqlTableSource table, int sourceId)
{
if (table == null || table.SourceID == sourceId || sourceId == -1)
{
return(0);
}
var i = 0;
while (i < table.Joins.Count)
{
if (table.Joins[i].Table.SourceID == sourceId)
{
return(i + 1);
}
++i;
}
return(-1);
}
void FlattenJoins(SqlTableSource table)
{
for (var i = 0; i < table.Joins.Count; i++)
{
var j = table.Joins[i];
FlattenJoins(j.Table);
if (j.JoinType == JoinType.Inner)
{
for (var si = 0; si < j.Table.Joins.Count; si++)
{
var sj = j.Table.Joins[si];
if ((sj.JoinType == JoinType.Inner || sj.JoinType == JoinType.Left) &&
table != j.Table && !HasDependencyWithParent(j, sj))
{
table.Joins.Insert(i + 1, sj);
j.Table.Joins.RemoveAt(si);
--si;
}
}
}
}
}
/// <summary>
/// Collects unique keys from different sources.
/// </summary>
/// <param name="tableSource"></param>
/// <returns>List of unique keys</returns>
List <VirtualField[]>?GetKeysInternal(SqlTableSource tableSource)
{
var knownKeys = new List <IList <ISqlExpression> >();
QueryHelper.CollectUniqueKeys(tableSource, knownKeys);
if (knownKeys.Count == 0)
{
return(null);
}
var result = new List <VirtualField[]>();
foreach (var v in knownKeys)
{
var fields = v.Select(GetUnderlayingField).ToArray();
if (fields.Length == v.Count)
{
result.Add(fields !);
}
}
return(result.Count > 0 ? result : null);
}
void RemoveSource(SqlTableSource fromTable, SqlJoinedTable join)
{
if (_removedSources == null)
{
_removedSources = new HashSet <int>();
}
_removedSources.Add(join.Table.SourceID);
if (_equalityMap != null)
{
var keys = _equalityMap.Keys.Where(k => k.SourceID == join.Table.SourceID).ToArray();
foreach (var key in keys)
{
var newField = MapToSource(fromTable, key, fromTable.SourceID);
if (newField != null)
{
ReplaceField(key, newField);
}
_equalityMap.Remove(key);
}
}
//TODO: investigate another ways when we can propagate keys up
if (join.JoinType == JoinType.Inner && join.Table.HasUniqueKeys)
{
var newFields = join.Table.UniqueKeys
.Select(uk => uk.Select(k => GetNewField(new VirtualField(k)).Element).ToArray());
fromTable.UniqueKeys.AddRange(newFields);
}
ResetFieldSearchCache(join.Table);
}
// here we can deal with LEFT JOIN and INNER JOIN
bool TryToRemoveIndepended(SqlTableSource fromTable, SqlTableSource manySource,
SqlJoinedTable join,
List <List <string> > uniqueKeys)
{
if (join.JoinType == JoinType.Inner)
{
return(false);
}
var found = SearchForFields(manySource, join);
if (found == null)
{
return(false);
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
if (join.JoinType == JoinType.Inner)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove from second
join.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to Where
if (join.Condition.Conditions.Count > 0)
{
AddSearchConditions(_selectQuery.Where.SearchCondition, join.Condition.Conditions);
join.Condition.Conditions.Clear();
}
// add filer for nullable fileds because after INNER JOIN records with nulls disappear
foreach (var item in found)
{
if (item.ManyField.CanBeNull)
{
AddSearchCondition(_selectQuery.Where.SearchCondition,
new SqlCondition(false, new SqlPredicate.IsNull(item.ManyField.Element, true)));
}
}
}
RemoveSource(fromTable, join);
return(true);
}
return(false);
}
protected void BuildIdentityInsert(SqlTableSource table, bool enable)
{
StringBuilder.Append("SET IDENTITY_INSERT ");
BuildTableName(table, true, false);
StringBuilder.AppendLine(enable ? " ON" : " OFF");
}
/// <summary>
/// Collects unique keys from different sources.
/// </summary>
/// <param name="tableSource"></param>
/// <returns>List of unique keys</returns>
List <VirtualField[]> GetKeysInternal(SqlTableSource tableSource)
{
var knownKeys = new Lazy <List <IList <ISqlExpression> > >(() => new List <IList <ISqlExpression> >());
if (tableSource.HasUniqueKeys)
{
knownKeys.Value.AddRange(tableSource.UniqueKeys);
}
switch (tableSource.Source)
{
case SqlTable table:
{
var keys = table.GetKeys(false);
if (keys != null && keys.Count > 0)
{
knownKeys.Value.Add(keys);
}
break;
}
case SelectQuery selectQuery:
{
if (selectQuery.HasUniqueKeys)
{
knownKeys.Value.AddRange(selectQuery.UniqueKeys);
}
if (selectQuery.Select.IsDistinct)
{
knownKeys.Value.Add(selectQuery.Select.Columns.OfType <ISqlExpression>().ToList());
}
if (!selectQuery.Select.GroupBy.IsEmpty)
{
var columns = selectQuery.Select.GroupBy.Items
.Select(i => selectQuery.Select.Columns.Find(c => c.Expression.Equals(i))).Where(c => c != null).ToArray();
if (columns.Length == selectQuery.Select.GroupBy.Items.Count)
{
knownKeys.Value.Add(columns.OfType <ISqlExpression>().ToList());
}
}
break;
}
}
if (!knownKeys.IsValueCreated)
{
return(null);
}
var result = new List <VirtualField[]>();
foreach (var v in knownKeys.Value)
{
var fields = v.Select(GetUnderlayingField).ToArray();
if (fields.Length == v.Count)
{
result.Add(fields);
}
}
return(result.Count > 0 ? result : null);
}
bool TryMergeWithTable(SqlTableSource fromTable, SqlJoinedTable join, List <List <string> > uniqueKeys)
{
if (join.Table.Joins.Count != 0)
{
return(false);
}
var hasLeftJoin = join.JoinType == JoinType.Left;
var found = SearchForFields(fromTable, join);
if (found == null)
{
return(false);
}
// for removing join with same table fields should be equal
found = found.Where(f => f.OneField.Name == f.ManyField.Name).ToList();
if (found.Count == 0)
{
return(false);
}
if (hasLeftJoin)
{
if (join.Condition.Conditions.Count != found.Count)
{
return(false);
}
// currently no dependecies in search condition allowed for left join
if (IsDependedExcludeJoins(join))
{
return(false);
}
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove unique key conditions
join.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to the Where section
if (join.Condition.Conditions.Count > 0)
{
AddSearchConditions(_selectQuery.Where.SearchCondition, join.Condition.Conditions);
join.Condition.Conditions.Clear();
}
// add check that previously joined fields is not null
foreach (var item in found)
{
if (item.ManyField.CanBeNull)
{
var newField = MapToSource(fromTable, item.ManyField, fromTable.SourceID);
AddSearchCondition(_selectQuery.Where.SearchCondition,
new SqlCondition(false, new SqlPredicate.IsNull(newField.Element, true)));
}
}
// add mapping to new source
ReplaceSource(fromTable, join, fromTable);
return(true);
}
return(false);
}
bool TryMergeJoins(SqlTableSource fromTable,
SqlTableSource manySource,
SqlJoinedTable join1, SqlJoinedTable join2,
List <List <string> > uniqueKeys)
{
var found1 = SearchForFields(manySource, join1);
if (found1 == null)
{
return(false);
}
var found2 = SearchForFields(manySource, join2);
if (found2 == null)
{
return(false);
}
var hasLeftJoin = join1.JoinType == JoinType.Left || join2.JoinType == JoinType.Left;
// left join should match exactly
if (hasLeftJoin)
{
if (join1.Condition.Conditions.Count != join2.Condition.Conditions.Count)
{
return(false);
}
if (found1.Count != found2.Count)
{
return(false);
}
if (join1.Table.Joins.Count != 0 || join2.Table.Joins.Count != 0)
{
return(false);
}
}
List <FoundEquality> found = null;
for (var i1 = 0; i1 < found1.Count; i1++)
{
var f1 = found1[i1];
for (var i2 = 0; i2 < found2.Count; i2++)
{
var f2 = found2[i2];
if (f1.ManyField.Name == f2.ManyField.Name && f1.OneField.Name == f2.OneField.Name)
{
if (found == null)
{
found = new List <FoundEquality>();
}
found.Add(f2);
}
}
}
if (found == null)
{
return(false);
}
if (hasLeftJoin)
{
// for left join each expression should be used
if (found.Count != join1.Condition.Conditions.Count)
{
return(false);
}
// currently no dependecies in search condition allowed for left join
if (IsDepended(join1, join2))
{
return(false);
}
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove from second
join2.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to first
if (join2.Condition.Conditions.Count > 0)
{
AddSearchConditions(join1.Condition, join2.Condition.Conditions);
join2.Condition.Conditions.Clear();
}
join1.Table.Joins.AddRange(join2.Table.Joins);
// add mapping to new source
ReplaceSource(fromTable, join2, join1.Table);
return(true);
}
return(false);
}
VirtualField MapToSource(SqlTableSource table, VirtualField field, int sourceId)
{
var visited = new HashSet <VirtualField>();
return(MapToSourceInternal(table, field, sourceId, visited));
}
bool CanWeReplaceField(SqlTableSource table, VirtualField field, HashSet <int> excludeSourceId, int testedSourceId)
{
var visited = new HashSet <VirtualField>();
return(CanWeReplaceFieldInternal(table, field, excludeSourceId, GetSourceIndex(table, testedSourceId), visited));
}
