static DbColumnSegment CloneColumnSegment(DbColumnSegment rawColumnSeg, DbTable newBelongTable)
{
DbColumnAccessExpression columnAccessExp = new DbColumnAccessExpression(newBelongTable, DbColumn.MakeColumn(rawColumnSeg.Body, rawColumnSeg.Alias));
DbColumnSegment newColumnSeg = new DbColumnSegment(columnAccessExp, rawColumnSeg.Alias);
return(newColumnSeg);
}
void BuildGeneralSql(DbSqlQueryExpression exp)
{
if (exp.TakeCount != null || exp.SkipCount != null)
{
throw new ArgumentException();
}
this._sqlBuilder.Append("SELECT ");
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
this.AppendColumnSegment(column);
}
this._sqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
this.BuildOrderState(exp.Orderings);
}
void BuildGeneralSql(DbSqlQueryExpression exp)
{
this._sqlBuilder.Append("SELECT ");
this.AppendDistinct(exp.IsDistinct);
if (exp.TakeCount != null)
{
this._sqlBuilder.Append("TOP ", exp.TakeCount.ToString(), " ");
}
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
this.AppendColumnSegment(column);
}
this._sqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
this.BuildOrderState(exp.Orderings);
}
public static int?TryGetOrAddColumn(DbSqlQueryExpression sqlQuery, DbExpression exp, string addDefaultAlias = UtilConstants.DefaultColumnAlias)
{
if (exp == null)
{
return(null);
}
List <DbColumnSegment> columnList = sqlQuery.ColumnSegments;
DbColumnSegment columnSeg = null;
int?ordinal = null;
for (int i = 0; i < columnList.Count; i++)
{
var item = columnList[i];
if (DbExpressionEqualityComparer.EqualsCompare(item.Body, exp))
{
ordinal = i;
columnSeg = item;
break;
}
}
if (ordinal == null)
{
string alias = Utils.GenerateUniqueColumnAlias(sqlQuery, addDefaultAlias);
columnSeg = new DbColumnSegment(exp, alias);
columnList.Add(columnSeg);
ordinal = columnList.Count - 1;
}
return(ordinal.Value);
}
static void CopyColumnSegments(List <DbColumnSegment> sourceList, List <DbColumnSegment> destinationList, DbTable newTable)
{
for (int i = 0; i < sourceList.Count; i++)
{
DbColumnSegment newColumnSeg = CloneColumnSegment(sourceList[i], newTable);
destinationList.Add(newColumnSeg);
}
}
void BuildGeneralSql(DbSqlQueryExpression exp)
{
this._sqlBuilder.Append("SELECT ");
if (exp.IsDistinct)
{
this._sqlBuilder.Append("DISTINCT ");
}
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
this.AppendColumnSegment(column);
}
this._sqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
this.BuildOrderState(exp.Orderings);
if (exp.SkipCount != null || exp.TakeCount != null)
{
int skipCount = exp.SkipCount ?? 0;
long takeCount = long.MaxValue;
if (exp.TakeCount != null)
{
takeCount = exp.TakeCount.Value;
}
this._sqlBuilder.Append(" LIMIT ", takeCount.ToString(), " OFFSET ", skipCount.ToString());
}
DbTableSegment seg = exp.Table.Table;
if (seg.Lock == LockType.UpdLock)
{
this._sqlBuilder.Append(" FOR UPDATE");
}
else if (seg.Lock == LockType.Unspecified || seg.Lock == LockType.NoLock)
{
//Do nothing.
}
else
{
throw new NotSupportedException($"lock type: {seg.Lock.ToString()}");
}
}
public virtual GeneralQueryState AsSubQueryState()
{
DbSqlQueryExpression sqlQuery = this.CreateSqlQuery();
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
ResultElement result = new ResultElement();
DbTableSegment tableSeg = new DbTableSegment(subQuery, result.GenerateUniqueTableAlias());
DbFromTableExpression fromTable = new DbFromTableExpression(tableSeg);
result.FromTable = fromTable;
DbTable table = new DbTable(tableSeg.Alias);
//TODO 根据旧的生成新 MappingMembers
IMappingObjectExpression newMoe = this.Result.MappingObjectExpression.ToNewObjectExpression(sqlQuery, table);
result.MappingObjectExpression = newMoe;
//得将 subQuery.SqlQuery.Orders 告诉 以下创建的 result
//将 orderPart 传递下去
if (this.Result.Orderings.Count > 0)
{
for (int i = 0; i < this.Result.Orderings.Count; i++)
{
DbOrdering ordering = this.Result.Orderings[i];
DbExpression orderingExp = ordering.Expression;
string alias = null;
DbColumnSegment columnExpression = sqlQuery.ColumnSegments.Where(a => DbExpressionEqualityComparer.EqualsCompare(orderingExp, a.Body)).FirstOrDefault();
// 对于重复的则不需要往 sqlQuery.Columns 重复添加了
if (columnExpression != null)
{
alias = columnExpression.Alias;
}
else
{
alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
DbColumnSegment columnSeg = new DbColumnSegment(orderingExp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
}
DbColumnAccessExpression columnAccessExpression = new DbColumnAccessExpression(orderingExp.Type, table, alias);
result.Orderings.Add(new DbOrdering(columnAccessExpression, ordering.OrderType));
}
}
result.InheritOrderings = true;
GeneralQueryState queryState = new GeneralQueryState(result);
return(queryState);
}
public static DbColumnAccessExpression ParseColumnAccessExpression(DbSqlQueryExpression sqlQuery, DbTable table, DbExpression exp, string defaultAlias = UtilConstants.DefaultColumnAlias)
{
string alias = Utils.GenerateUniqueColumnAlias(sqlQuery, defaultAlias);
DbColumnSegment columnSeg = new DbColumnSegment(exp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
DbColumnAccessExpression cae = new DbColumnAccessExpression(exp.Type, table, alias);
return(cae);
}
public virtual GeneralQueryState AsSubQueryState()
{
DbSqlQueryExpression sqlQuery = this.CreateSqlQuery();
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
QueryModel newQueryModel = new QueryModel(this._queryModel.ScopeParameters, this._queryModel.ScopeTables, this._queryModel.IgnoreFilters);
DbTableSegment tableSeg = new DbTableSegment(subQuery, newQueryModel.GenerateUniqueTableAlias(), LockType.Unspecified);
DbFromTableExpression fromTable = new DbFromTableExpression(tableSeg);
newQueryModel.FromTable = fromTable;
DbTable aliasTable = new DbTable(tableSeg.Alias);
//TODO 根据旧的生成新 ResultModel
IObjectModel newResultModel = this.QueryModel.ResultModel.ToNewObjectModel(sqlQuery, aliasTable, fromTable);
newQueryModel.ResultModel = newResultModel;
//得将 subQuery.SqlQuery.Orders 告诉 以下创建的 result
//将 orderPart 传递下去
for (int i = 0; i < this.QueryModel.Orderings.Count; i++)
{
DbOrdering ordering = this.QueryModel.Orderings[i];
DbExpression orderingExp = ordering.Expression;
string alias = null;
DbColumnSegment columnExpression = sqlQuery.ColumnSegments.Find(a => DbExpressionEqualityComparer.EqualsCompare(orderingExp, a.Body));
// 对于重复的则不需要往 sqlQuery.Columns 重复添加了
if (columnExpression != null)
{
alias = columnExpression.Alias;
}
else
{
alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
DbColumnSegment columnSeg = new DbColumnSegment(orderingExp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
}
DbColumnAccessExpression columnAccessExpression = new DbColumnAccessExpression(aliasTable, DbColumn.MakeColumn(orderingExp, alias));
newQueryModel.Orderings.Add(new DbOrdering(columnAccessExpression, ordering.OrderType));
}
newQueryModel.InheritOrderings = true;
GeneralQueryState queryState = new GeneralQueryState(newQueryModel);
return(queryState);
}
void BuildGeneralSql(DbSqlQueryExpression exp)
{
this.SqlBuilder.Append("SELECT ");
if (exp.IsDistinct)
{
this.SqlBuilder.Append("DISTINCT ");
}
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this.SqlBuilder.Append(",");
}
this.AppendColumnSegment(column);
}
this.SqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
this.BuildOrderState(exp.Orderings);
if (exp.SkipCount == null && exp.TakeCount == null)
{
return;
}
int skipCount = exp.SkipCount ?? 0;
long takeCount = long.MaxValue;
if (exp.TakeCount != null)
{
takeCount = exp.TakeCount.Value;
}
this.SqlBuilder.Append(" LIMIT ", takeCount.ToString(), " OFFSET ", skipCount.ToString());
}
protected override void BuildLimitSql(DbExpressions.DbSqlQueryExpression exp)
{
//order by number offset 10 rows fetch next 20 rows only;
this.SqlBuilder.Append("SELECT ");
this.AppendDistinct(exp.IsDistinct);
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this.SqlBuilder.Append(",");
}
this.AppendColumnSegment(column);
}
this.SqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
List <DbOrdering> orderings = exp.Orderings;
if (orderings.Count == 0)
{
DbExpression orderingExp = DbExpression.Add(PublicConstants.DbParameter_1, DbConstantExpression.Zero, DbConstantExpression.Zero.Type, null);
DbOrdering ordering = new DbOrdering(orderingExp, DbOrderType.Asc);
orderings = new List <DbOrdering>(1);
orderings.Add(ordering);
}
this.BuildOrderState(orderings);
this.SqlBuilder.Append(" OFFSET ", exp.SkipCount.Value.ToString(), " ROWS");
if (exp.TakeCount != null)
{
this.SqlBuilder.Append(" FETCH NEXT ", exp.TakeCount.Value.ToString(), " ROWS ONLY");
}
}
public override DbExpression Visit(DbSqlQueryExpression exp)
{
if (exp.TakeCount != null)
{
DbSqlQueryExpression newSqlQuery = CloneWithoutLimitInfo(exp, "TTAKE");
if (exp.SkipCount == null)
{
AppendLimitCondition(newSqlQuery, exp.TakeCount.Value);
}
else
{
AppendLimitCondition(newSqlQuery, exp.TakeCount.Value + exp.SkipCount.Value);
newSqlQuery.SkipCount = exp.SkipCount.Value;
}
newSqlQuery.IsDistinct = exp.IsDistinct;
newSqlQuery.Accept(this);
return(exp);
}
else if (exp.SkipCount != null)
{
DbSqlQueryExpression subSqlQuery = CloneWithoutLimitInfo(exp, "TSKIP");
string row_numberName = GenRowNumberName(subSqlQuery.ColumnSegments);
DbColumnSegment row_numberSeg = new DbColumnSegment(OracleSemantics.DbMemberExpression_ROWNUM, row_numberName);
subSqlQuery.ColumnSegments.Add(row_numberSeg);
DbTable table = new DbTable("T");
DbSqlQueryExpression newSqlQuery = WrapSqlQuery(subSqlQuery, table, exp.ColumnSegments);
DbColumnAccessExpression columnAccessExp = new DbColumnAccessExpression(table, DbColumn.MakeColumn(row_numberSeg.Body, row_numberName));
newSqlQuery.Condition = DbExpression.GreaterThan(columnAccessExp, DbExpression.Constant(exp.SkipCount.Value));
newSqlQuery.IsDistinct = exp.IsDistinct;
newSqlQuery.Accept(this);
return(exp);
}
this.BuildGeneralSql(exp);
return(exp);
}
public override DbExpression Visit(DbExistsExpression exp)
{
this._sqlBuilder.Append("Exists ");
DbSqlQueryExpression rawSqlQuery = exp.SqlQuery;
DbSqlQueryExpression sqlQuery = new DbSqlQueryExpression()
{
TakeCount = rawSqlQuery.TakeCount,
SkipCount = rawSqlQuery.SkipCount,
Table = rawSqlQuery.Table,
Condition = rawSqlQuery.Condition,
HavingCondition = rawSqlQuery.HavingCondition,
};
sqlQuery.GroupSegments.AddRange(rawSqlQuery.GroupSegments);
DbColumnSegment columnSegment = new DbColumnSegment(DbExpression.Constant("1"), "C");
sqlQuery.ColumnSegments.Add(columnSegment);
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
return subQuery.Accept(this);
}
public static DbExpression TryGetOrAddNullChecking(DbSqlQueryExpression sqlQuery, DbTable table, DbExpression exp)
{
if (exp == null)
{
return(null);
}
List <DbColumnSegment> columnList = sqlQuery.ColumnSegments;
DbColumnSegment columnSeg = null;
columnSeg = columnList.Where(a => DbExpressionEqualityComparer.EqualsCompare(a.Body, exp)).FirstOrDefault();
if (columnSeg == null)
{
string alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
columnSeg = new DbColumnSegment(exp, alias);
columnList.Add(columnSeg);
}
DbColumnAccessExpression cae = new DbColumnAccessExpression(columnSeg.Body.Type, table, columnSeg.Alias);
return(cae);
}
protected virtual void BuildLimitSql(DbSqlQueryExpression exp)
{
bool shouldSortResults = false;
if (exp.TakeCount != null)
{
shouldSortResults = true;
}
else if (this._sqlBuilder.Length == 0)
{
shouldSortResults = true;
}
this._sqlBuilder.Append("SELECT ");
this.AppendDistinct(exp.IsDistinct);
if (exp.TakeCount != null)
{
this._sqlBuilder.Append("TOP ", exp.TakeCount.ToString(), " ");
}
string tableAlias = "T";
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
this.QuoteName(tableAlias);
this._sqlBuilder.Append(".");
this.QuoteName(column.Alias);
this._sqlBuilder.Append(" AS ");
this.QuoteName(column.Alias);
}
this._sqlBuilder.Append(" FROM ");
this._sqlBuilder.Append("(");
//------------------------//
this._sqlBuilder.Append("SELECT ");
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
DbValueExpressionTransformer.Transform(column.Body).Accept(this);
this._sqlBuilder.Append(" AS ");
this.QuoteName(column.Alias);
}
List <DbOrdering> orderings = exp.Orderings;
if (orderings.Count == 0)
{
DbOrdering ordering = new DbOrdering(PublicConstants.DbParameter_1, DbOrderType.Asc);
orderings = new List <DbOrdering>(1);
orderings.Add(ordering);
}
string row_numberName = GenRowNumberName(columns);
this._sqlBuilder.Append(",ROW_NUMBER() OVER(ORDER BY ");
this.ConcatOrderings(orderings);
this._sqlBuilder.Append(") AS ");
this.QuoteName(row_numberName);
this._sqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
//------------------------//
this._sqlBuilder.Append(")");
this._sqlBuilder.Append(" AS ");
this.QuoteName(tableAlias);
this._sqlBuilder.Append(" WHERE ");
this.QuoteName(tableAlias);
this._sqlBuilder.Append(".");
this.QuoteName(row_numberName);
this._sqlBuilder.Append(" > ");
this._sqlBuilder.Append(exp.SkipCount.ToString());
if (shouldSortResults)
{
this._sqlBuilder.Append(" ORDER BY ");
this.QuoteName(tableAlias);
this._sqlBuilder.Append(".");
this.QuoteName(row_numberName);
this._sqlBuilder.Append(" ASC");
}
}
internal void AppendColumnSegment(DbColumnSegment seg)
{
seg.Body.Accept(this.ValueExpressionVisitor);
this._sqlBuilder.Append(" AS ");
this.QuoteName(seg.Alias);
}
internal void AppendColumnSegment(DbColumnSegment seg)
{
DbValueExpressionTransformer.Transform(seg.Body).Accept(this);
this.SqlBuilder.Append(" AS ");
this.QuoteName(seg.Alias);
}
internal void AppendColumnSegment(DbColumnSegment seg)
{
seg.Body.Accept(this);
this.SqlBuilder.Append(" AS ");
this.QuoteName(seg.Alias);
}
public virtual GeneralQueryState AsSubQueryState()
{
DbSqlQueryExpression sqlQuery = this.CreateSqlQuery();
DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
ResultElement result = new ResultElement();
DbTableSegment tableSeg = new DbTableSegment(subQuery, result.GenerateUniqueTableAlias());
DbFromTableExpression fromTable = new DbFromTableExpression(tableSeg);
result.FromTable = fromTable;
DbTable table = new DbTable(tableSeg.Alias);
//TODO 根据旧的生成新 MappingMembers
IMappingObjectExpression newMoe = this.Result.MappingObjectExpression.ToNewObjectExpression(sqlQuery, table);
result.MappingObjectExpression = newMoe;
//得将 subQuery.SqlQuery.Orders 告诉 以下创建的 result
//将 orderPart 传递下去
if (this.Result.OrderSegments.Count > 0)
{
for (int i = 0; i < this.Result.OrderSegments.Count; i++)
{
DbOrderSegment orderPart = this.Result.OrderSegments[i];
DbExpression orderExp = orderPart.DbExpression;
string alias = null;
DbColumnSegment columnExpression = sqlQuery.ColumnSegments.Where(a => DbExpressionEqualityComparer.EqualsCompare(orderExp, a.Body)).FirstOrDefault();
// 对于重复的则不需要往 sqlQuery.Columns 重复添加了
if (columnExpression != null)
{
alias = columnExpression.Alias;
}
else
{
alias = Utils.GenerateUniqueColumnAlias(sqlQuery);
DbColumnSegment columnSeg = new DbColumnSegment(orderExp, alias);
sqlQuery.ColumnSegments.Add(columnSeg);
}
DbColumnAccessExpression columnAccessExpression = new DbColumnAccessExpression(orderExp.Type, table, alias);
result.OrderSegments.Add(new DbOrderSegment(columnAccessExpression, orderPart.OrderType));
}
}
result.IsOrderSegmentsFromSubQuery = true;
GeneralQueryState queryState = new GeneralQueryState(result);
return queryState;
}
protected virtual void BuildLimitSql(DbSqlQueryExpression exp)
{
this._sqlBuilder.Append("SELECT ");
if (exp.TakeCount != null)
{
this._sqlBuilder.Append("TOP (", exp.TakeCount.ToString(), ") ");
}
string tableAlias = "T";
List <DbColumnSegment> columns = exp.ColumnSegments;
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
this.QuoteName(tableAlias);
this._sqlBuilder.Append(".");
this.QuoteName(column.Alias);
this._sqlBuilder.Append(" AS ");
this.QuoteName(column.Alias);
}
this._sqlBuilder.Append(" FROM ");
this._sqlBuilder.Append("(");
//------------------------//
this._sqlBuilder.Append("SELECT ");
for (int i = 0; i < columns.Count; i++)
{
DbColumnSegment column = columns[i];
if (i > 0)
{
this._sqlBuilder.Append(",");
}
column.Body.Accept(this.ValueExpressionVisitor);
this._sqlBuilder.Append(" AS ");
this.QuoteName(column.Alias);
}
List <DbOrdering> orderings = exp.Orderings;
if (orderings.Count == 0)
{
DbOrdering ordering = new DbOrdering(UtilConstants.DbParameter_1, OrderType.Asc);
orderings = new List <DbOrdering>(1);
orderings.Add(ordering);
}
string row_numberName = CreateRowNumberName(columns);
this._sqlBuilder.Append(",ROW_NUMBER() OVER(ORDER BY ");
this.ConcatOrderings(orderings);
this._sqlBuilder.Append(") AS ");
this.QuoteName(row_numberName);
this._sqlBuilder.Append(" FROM ");
exp.Table.Accept(this);
this.BuildWhereState(exp.Condition);
this.BuildGroupState(exp);
//------------------------//
this._sqlBuilder.Append(")");
this._sqlBuilder.Append(" AS ");
this.QuoteName(tableAlias);
this._sqlBuilder.Append(" WHERE ");
this.QuoteName(tableAlias);
this._sqlBuilder.Append(".");
this.QuoteName(row_numberName);
this._sqlBuilder.Append(" > ");
this._sqlBuilder.Append(exp.SkipCount.ToString());
}