示例#1
0
        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);
        }
示例#2
0
		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);
				}
			}
		}
示例#3
0
        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;
        }
示例#4
0
        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;
        }
示例#5
0
        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;
        }
示例#6
0
        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;
        }
示例#7
0
 public override void Visit(OrderByClause order_by_clause)
 {
     base.Visit(order_by_clause);
 }
示例#8
0
文件: DBDialect.cs 项目: npenin/uss
		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);
				}
			}
		}
示例#9
0
文件: DBDialect.cs 项目: npenin/uss
		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;
		}
示例#10
0
文件: DBDialect.cs 项目: npenin/uss
 public abstract ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize);