/// <summary> /// Gets all primary keys from referenced tables. This function also /// recursively go throught all its RefTo ref columns. It is because the actual /// table that the ref column refering to maybe from sub selects. /// </summary> /// <returns></returns> public static IDbColumn[] GetPrimaryKeys(this IDbRefColumn refCol) { var pks = refCol.RefTo?.GetPrimaryKeys()?.ToArray() ?? (refCol.Ref.Referee as IDbTable)?.PrimaryKeys ?? new IDbColumn[0]; foreach (var pk in pks) { pk.Ref = refCol.Ref; } return(pks.ToArray()); }
/// <summary> /// Add selectable into the selection of the select which referred by the ref column. /// If the ref column has a RefTo ref column, this function will also recursively add /// the selectable to RefTo ref columns /// </summary> public static void AddToReferedSelect( this IDbRefColumn refCol, IDbObjectFactory factory, string colName, DbType colType, string alias = null) { if (refCol.RefTo != null) { refCol.RefTo.AddToReferedSelect(factory, colName, colType, alias); colName = alias ?? colName; } var column = factory.BuildColumn(refCol.Ref, colName, colType, alias); var selection = refCol.OwnerSelect.Selection; selection.Remove(refCol); selection.Add(column); }
public IDbRefColumn BuildRefColumn(DbReference dbRef, string alias = null, IDbRefColumn fromRefColumn = null) { var refCol = new SqlRefColumn { Ref = dbRef, Alias = alias, RefTo = fromRefColumn }; if (dbRef != null) { dbRef.ReferredRefColumn = refCol; } return(refCol); }
private IDbSelectable GetSelectable(IDbSelect fromSelect, IDbObject selection, DbReference toSelectRef) { var dbRef = selection as DbReference; if (dbRef != null) { IDbRefColumn toRefCol = null; if (dbRef.OwnerSelect != fromSelect) { var toSelect = (IDbSelect)toSelectRef.Referee; toRefCol = _dbFactory.BuildRefColumn(dbRef); toSelect.Selection.Add(toRefCol); dbRef = toSelectRef; } var refColumn = _dbFactory.BuildRefColumn(dbRef); refColumn.RefTo = toRefCol; return(refColumn); } var column = selection as IDbColumn; if (column != null) { if (column.Ref.OwnerSelect != fromSelect) { var toSelect = (IDbSelect)toSelectRef.Referee; toSelect.Selection.Add(column); column = _dbFactory.BuildColumn(column); column.Ref = toSelectRef; } return(column); } throw new NotSupportedException(); }
/// 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); }
/// <summary> /// Add selectable into the selection of the select which referred by the ref column. /// If the ref column has a RefTo ref column, this function will also recursively add /// the selectable to RefTo ref columns /// </summary> public static void AddToReferedSelect( this IDbRefColumn refCol, IDbObjectFactory factory, string colName, Type colType, string alias = null) { refCol.AddToReferedSelect(factory, colName, factory.BuildType(colType), alias); }