public SelectStatement(ITagMapping tag): base(tag)
{
SelectList = new ExpressionCollection();
_IsDistinct = false;
_SelectedAllColumns = false;
_Limit = 0;
_Offset = 0;
_FromClause = new FromClause();
_WhereClause = new WhereClause(BinaryLogicOperator.And);
_OrderByClause = new OrderByClause(this);
}
public override void Visit(OrderByClause order_by_clause)
{
// Access doesn't manage column alias in order by clause
// Use columns positions instead (positions : from 1 to n)
if (order_by_clause.Count != 0)
{
_Query.Append(ORDERBY).Append(SPACE);
foreach(OrderByClauseColumn column in order_by_clause)
{
if(column.ColumnName == null || column.ColumnName == string.Empty)
continue;
int pos = 2;
SelectStatement sel = order_by_clause.Select;
if(sel as UnionStatement != null && (sel as UnionStatement).SelectExpressions.Count > 0)
sel = (sel as UnionStatement).SelectExpressions[0];
while(sel.SelectList.Count == 0 && sel.FromClause.Count > 0 && sel.FromClause[0] as SelectStatement != null)
{
sel = sel.FromClause[0] as SelectStatement;
if(sel as UnionStatement != null && (sel as UnionStatement).SelectExpressions.Count > 0)
sel = (sel as UnionStatement).SelectExpressions[0];
}
for(int i=0; i<sel.SelectList.Count; i++)
{
ISQLExpression cur = sel.SelectList[i];
if(cur as Column == null)
continue;
if((cur as Column).ColumnName == column.ColumnName || (cur as Column).Alias == column.ColumnName)
{
pos = i+1;
break;
}
}
if(pos != 0)
_Query.Append(string.Concat(Convert.ToString(pos), " "));
if(column.Desc)
_Query.Append(DESC);
if (order_by_clause.IndexOf(column) != order_by_clause.Count -1 )
_Query.Append(COMMA);
}
}
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id
// {SortSelect}
// FROM ( {OPathQuery} ) e1
// {SortFrom}
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
select.Limit = -1;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM XXX WHERE XXX
//ORDER BY [orderByClause]
select.Limit = pageSize;
}
return select;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
//SELECT XXX
//FROM XXX WHERE XXX
//ORDER BY [orderByClause]
//[LIMIT pageSize OFFSET topPage]
select.Limit = pageSize;
select.Offset = topPage;
return select;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id, {SortSelect}
// FROM
// (
// SELECT Type, Id, {SortSelect}
// FROM {OPathQuery without order by}
// ) e1
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// put in a subquery because access cannot order with alias in select
SelectStatement selectZ = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectZ.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
selectZ.SelectedAllColumns = select.SelectedAllColumns;
selectZ.FromClause.Add(select as Table);
selectZ.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectZ;
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM (select x from y where z) as x WHERE XXX
//ORDER BY [orderByClause]
// put in a subquery because access cannot order with alias in select
SelectStatement selectX = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectX.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
if (select.SelectList.Count == 0)
selectX.SelectedAllColumns = true;
selectX.Limit = pageSize;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectX;
}
return select;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1 || pageSize != 0)
{
// paging
//SELECT y.* FROM
//(
// SELECT x.*, [rownum n]
// FROM
// (
// SELECT e1.*
// FROM ( {OPathQuery} ) e1
// ORDER BY [SortOrderBy]
// )x
//)y
//WHERE n Between [topPage] and [topPage + pageSize - 1]
// ORDER BY n <-- add this sort to not loose order when putting this query in another one
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
select.TableAlias = "x";
selectX.SelectList.Add(new SystemObject("x", MULT, String.Empty)); // use SystemObject to have the table prefix
selectX.SelectList.Add(new SystemObject("rownum", "n"));
selectX.FromClause.Add(select as Table);
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
if(topPage > 1)
selectY.WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, "n"), BinaryLogicOperator.GreaterOrEquals, new Constant(topPage, DbType.Int32)));
if(pageSize > 0)
selectY.WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, "n"), BinaryLogicOperator.LesserOrEquals, new Constant(topPage + pageSize - 1, DbType.Int32)));
selectY.OrderByClause.Add(new OrderByClauseColumn("n"));
select = selectY;
}
return select;
}
public override void Visit(OrderByClause order_by_clause)
{
base.Visit(order_by_clause);
}
public virtual void Visit(OrderByClause order_by_clause)
{
if (order_by_clause.Count != 0)
{
_Query.Append(ORDERBY).Append(SPACE);
foreach(OrderByClauseColumn column in order_by_clause)
{
column.Accept(this);
if (order_by_clause.IndexOf(column) != order_by_clause.Count -1 )
_Query.Append(COMMA);
}
}
}
protected SelectStatement CheckAndAdjustExpression(ISQLExpression sqlExp, OrderByClause orderby)
{
SelectStatement select = null;
if(sqlExp is UnionStatement)
{
// put expression in a select statement
UnionStatement union = (UnionStatement)sqlExp;
union.TableAlias = "DERIVEDTBL";
select = new SelectStatement(null);
select.SelectedAllColumns = true;
select.FromClause.Add(union as Table);
foreach(OrderByClauseColumn oc in orderby)
select.OrderByClause.Add(oc);
if(union.OrderByClause.Count > 0)
union.OrderByClause.Clear();
}
else if (sqlExp is SelectStatement)
{
((SelectStatement)sqlExp).OrderByClause = orderby;
select = (SelectStatement)sqlExp;
}
return select;
}
public abstract ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize);