public static IDbBinary UpdateBinary(this IDbBinary whereClause, IDbBinary predicate, IDbObjectFactory dbFactory)
{
if (predicate == null)
{
return(whereClause);
}
return(whereClause != null
? dbFactory.BuildBinary(whereClause, DbOperator.And, predicate)
: predicate);
}
private static void UpdateIncludeSelectAndProcessToNodes(
IncludeNode graphNode, IDbSelect includedSelect, IModelInfoProvider infoProvider,
IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator, AbstractMethodTranslator[] addons)
{
// create temp table
var entityRef = includedSelect.GetReturnEntityRef();
var returnTable = (IDbTable)entityRef.Referee;
var newIncludedSelect = dbFactory.BuildSelect(returnTable);
newIncludedSelect.From.Alias = nameGenerator.GenerateAlias(newIncludedSelect, returnTable.TableName);
var tempTableName = TranslationConstants.TempTablePreix + nameGenerator.GenerateAlias(null, returnTable.TableName, true);
var tempTable = dbFactory.BuildTempTable(tempTableName, includedSelect);
var tempSelect = dbFactory.BuildSelect(tempTable);
tempSelect.From.Alias = nameGenerator.GenerateAlias(tempSelect, tempTable.TableName);
var joinToTemp = MakeJoin(newIncludedSelect, tempSelect, dbFactory, nameGenerator);
var joinTo = joinToTemp.To;
newIncludedSelect.Joins.Add(joinToTemp);
foreach (var pk in returnTable.PrimaryKeys)
{
var fromPkCol = dbFactory.BuildColumn(entityRef, pk.Name, pk.ValType);
includedSelect.Selection.Add(fromPkCol);
var toPkCol = dbFactory.BuildColumn(tempSelect.From, pk.Name, pk.ValType);
tempSelect.Selection.Add(toPkCol);
tempSelect.GroupBys.Add(toPkCol);
toPkCol = dbFactory.BuildColumn(joinTo, pk.Name, pk.ValType);
var binary = dbFactory.BuildBinary(fromPkCol, DbOperator.Equal, toPkCol);
joinToTemp.Condition = joinToTemp.Condition.UpdateBinary(binary, dbFactory);
}
var orderCol = dbFactory.BuildColumn(tempSelect.From, tempTable.RowNumberColumnName, typeof(int));
tempSelect.Selection.Add(orderCol);
orderCol = dbFactory.BuildColumn(joinTo, tempTable.RowNumberColumnName, typeof(int));
newIncludedSelect.OrderBys.Add(orderCol);
graphNode.Select = newIncludedSelect;
graphNode.TempTable = tempTable;
foreach (var toNode in graphNode.ToNodes)
{
TranslateGraphNode(toNode, infoProvider, dbFactory, nameGenerator, addons);
}
}
public static IDbBinary ToBinary(this IDbObject dbElement, IDbObjectFactory dbFactory)
{
switch (dbElement)
{
case null:
return(null);
case IDbBinary dbBinary:
return(dbBinary);
}
var one = dbFactory.BuildConstant(true);
return(dbFactory.BuildBinary(dbElement, DbOperator.Equal, one));
}
public static IDbBinary ToBinary(this IDbObject dbElement, IDbObjectFactory dbFactory)
{
if (dbElement == null)
{
return(null);
}
var dbBinary = dbElement as IDbBinary;
if (dbBinary != null)
{
return(dbBinary);
}
var one = dbFactory.BuildConstant(true);
return(dbFactory.BuildBinary(dbElement, DbOperator.Equal, one));
}
/// Create a join for the relation
/// For parent relation, we create a join that joins to the parent table
/// For child relation, we will create a sub select that returns the child table,
/// and then joins to the sub select.
/// The reason for joining to sub select for child relation, is that we want to be
/// able to group on the join key, so that we will not repeat the parent row.
private IDbJoin GetOrCreateJoin(EntityRelation relation, DbReference fromRef, IDbRefColumn refCol)
{
var dbSelect = fromRef.OwnerSelect;
var tupleKey = Tuple.Create(dbSelect, relation);
if (!relation.IsChildRelation && _state.CreatedJoins.ContainsKey(tupleKey))
{
return(_state.CreatedJoins[tupleKey]);
}
var toEntity = relation.ToEntity;
var dbTable = _dbFactory.BuildTable(toEntity);
DbReference joinTo;
DbReference childRef = null;
IDbSelect childSelect = null;
// Create the join. For parent join, we just need to join to a Ref to the table
// For child relation, we will firstly create a sub select that return the child table
// and then join to then sub select
if (!relation.IsChildRelation)
{
var tableAlias = _nameGenerator.GenerateAlias(dbSelect, dbTable.TableName);
joinTo = _dbFactory.BuildRef(dbTable, tableAlias);
}
else
{
childRef = _dbFactory.BuildRef(dbTable);
childSelect = _dbFactory.BuildSelect(childRef);
childRef.Alias = _nameGenerator.GenerateAlias(childSelect, dbTable.TableName);
var tableAlias = _nameGenerator.GenerateAlias(dbSelect, TranslationConstants.SubSelectPrefix, true);
joinTo = _dbFactory.BuildRef(childSelect, tableAlias);
}
var dbJoin = _dbFactory.BuildJoin(joinTo, dbSelect);
dbSelect.Joins.Add(dbJoin);
// build join condition
IDbBinary condition = null;
for (var i = 0; i < relation.FromKeys.Count; i++)
{
var fromKey = relation.FromKeys[i];
var toKey = relation.ToKeys[i];
var fromColumn = _dbFactory.BuildColumn(fromRef, fromKey.DbName, fromKey.ValType);
var toColumn = _dbFactory.BuildColumn(joinTo, toKey.DbName, toKey.ValType);
// If we have created a sub for child relation, we need to the columns
// that are used in join condition selected from the sub select.
if (childRef != null && childSelect != null)
{
var alias = _nameGenerator.GenerateAlias(childSelect, toKey.DbName + TranslationConstants.JoinKeySuffix, true);
var childColumn = _dbFactory.BuildColumn(childRef, toKey.DbName, toKey.ValType, alias, true);
/**
* We need to also put the join key in the group of the sub select.
* This is to make sure the sub select is grouped by the key so that
* the parent (outer select) will not be repeated
* This operation needs to happen here not in the aggregation method call.
* The reason is that in aggregtion method calls we do not know which column
* from the entity is used in relation, so they will not be able to create
* the correct column
*/
childSelect.Selection.Add(_dbFactory.BuildRefColumn(childRef));
childSelect.Selection.Add(childColumn);
childSelect.GroupBys.Add(childColumn);
toColumn.Name = alias;
toColumn.Alias = string.Empty;
}
// if the relation is found on a fromRef which is referring a sub-select,
// it means the from key of the join is not on a table but a derived select.
// In this case, we need to add the from key into the derived select, as we will
// be using it in the join
if (fromRef.Referee is IDbSelect)
{
var alias = _nameGenerator.GenerateAlias(dbSelect, toKey.DbName + TranslationConstants.JoinKeySuffix, true);
fromColumn.Name = alias;
fromColumn.Alias = string.Empty;
// try to recursively add the join key to all connected sub select.
refCol.RefTo?.AddToReferedSelect(_dbFactory, fromKey.DbName, fromKey.ValType, alias);
}
var binary = _dbFactory.BuildBinary(fromColumn, DbOperator.Equal, toColumn);
condition = condition.UpdateBinary(binary, _dbFactory);
}
dbJoin.Condition = condition;
// all relations need to follow the join type
if (fromRef.OwnerJoin != null)
{
dbJoin.Type = fromRef.OwnerJoin.Type;
}
if (relation.IsChildRelation)
{
dbJoin.Type = DbJoinType.LeftOuter;
}
return(_state.CreatedJoins[tupleKey] = dbJoin);
}
private static void UpdateFromNodeTempTable(
IncludeNode fromNode, IDbObject dbObject, IDbSelect includedSelect,
IDbObjectFactory dbFactory, UniqueNameGenerator nameGenerator)
{
var fromSelect = fromNode.Select;
var dbJoin = fromSelect.Joins.Single(
j => dbObject is DbReference ? ReferenceEquals(j.To, dbObject) : ReferenceEquals(j.To.Referee, dbObject));
// remove the join to included relation from fromSelect
fromSelect.Joins.Remove(dbJoin);
if (dbObject is IDbSelect)
{
var refCol = includedSelect.Selection.Single(c => c is IDbRefColumn);
includedSelect.Selection.Remove(refCol);
}
var keys = dbJoin.Condition.GetDbObjects <IDbColumn>().ToArray();
var fromKeys = keys.Where(c => !ReferenceEquals(c.Ref, dbJoin.To)).ToArray();
var toKeys = keys.Where(c => ReferenceEquals(c.Ref, dbJoin.To)).ToArray();
var tempTable = fromNode.TempTable;
var sourceSelect = tempTable.SourceSelect;
var fromRef = sourceSelect.GetReturnEntityRef();
var returnRef = includedSelect.GetReturnEntityRef();
var tempSelect = dbFactory.BuildSelect(tempTable);
tempSelect.From.Alias = nameGenerator.GenerateAlias(tempSelect, tempTable.TableName);
var joinToTemp = MakeJoin(includedSelect, tempSelect, dbFactory, nameGenerator);
includedSelect.Joins.Add(joinToTemp);
for (var i = 0; i < fromKeys.Length; i++)
{
var fromKey = fromKeys[i];
var toKey = toKeys[i];
var fromPkCol = dbFactory.BuildColumn(fromRef, fromKey.Name, fromKey.ValType);
sourceSelect.Selection.Add(fromPkCol);
var tempPkCol = dbFactory.BuildColumn(tempSelect.From, fromKey.Name, fromKey.ValType);
tempSelect.Selection.Add(tempPkCol);
tempSelect.GroupBys.Add(tempPkCol);
if (dbObject is IDbSelect)
{
var key = toKey;
toKey = (IDbColumn)includedSelect.Selection.Single(c => c.GetAliasOrName() == key.GetNameOrAlias());
includedSelect.Selection.Remove(toKey);
includedSelect.GroupBys.Remove(toKey);
}
fromPkCol = dbFactory.BuildColumn(joinToTemp.To, fromKey.Name, fromKey.ValType);
var toPkCol = dbFactory.BuildColumn(returnRef, toKey.Name, toKey.ValType);
var binary = dbFactory.BuildBinary(toPkCol, DbOperator.Equal, fromPkCol);
joinToTemp.Condition = joinToTemp.Condition.UpdateBinary(binary, dbFactory);
}
}