Inheritance: SqlStatement, IPlSqlStatement
        public void SelectAllColumns()
        {
            var query = (SqlQueryExpression) SqlExpression.Parse("SELECT * FROM test_table");
            var statement = new SelectStatement(query);

            ITable result = statement.Execute(Query);
            Assert.IsNotNull(result);
            Assert.AreEqual(3, result.RowCount);
        }
        public static void AllWithJoin()
        {
            var query = (SqlQueryExpression) SqlExpression.Parse("SELECT * FROM t1, t2 WHERE t1.id = t2.other_id");
            var statement = new SelectStatement(query);

            var sql = statement.ToString();
            var expected = "SELECT * FROM t1, t2 WHERE t1.id = t2.other_id";
            Assert.AreEqual(expected, sql);
        }
        public static void SimpleAll()
        {
            var query = (SqlQueryExpression) SqlExpression.Parse("SELECT * FROM t1");
            var statement = new SelectStatement(query);

            var sql = statement.ToString();
            var expected = "SELECT * FROM t1";

            Assert.AreEqual(expected, sql);
        }
        public static void AllWithInnerJoint()
        {
            var query = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.other_id");
            var statement = new SelectStatement(query);

            var sql = statement.ToString();
            var expected = "SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.other_id";

            Assert.AreEqual(expected, sql);
        }
        public void SelectAliasedWithGroupedExpression()
        {
            var query = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM test_table t0 WHERE (t0.id = 1 AND t0.id <> 0)");
            var statement = new SelectStatement(query);

            var result = statement.Execute(Query);

            Assert.IsNotNull(result);
            Assert.AreEqual(1, result.RowCount);
        }
 protected override void BuildStatement(SqlCodeObjectBuilder builder)
 {
     var queryExpression = (SqlQueryExpression) ExpressionBuilder.Build(QueryExpression);
     if (QueryExpression.IntoClause != null) {
         var refExp = ExpressionBuilder.Build(QueryExpression.IntoClause);
         builder.AddObject(new SelectIntoStatement(queryExpression, refExp));
     } else {
         var orderBy = BuildOrderBy(OrderBy);
         var statement = new SelectStatement(queryExpression, orderBy);
         statement.Limit = BuildLimit(Limit);
         builder.AddObject(statement);
     }
 }
Beispiel #7
0
        public static SqlStatement Build(PlSqlParser.SelectStatementContext context)
        {
            IntoClause into;
            var query = Subquery.Form(context.subquery(), out into);

            if (into != null) {
                SqlExpression reference;
                if (into.TableName != null) {
                    reference = SqlExpression.Reference(into.TableName);
                } else {
                    var vars = into.Variables;
                    reference = SqlExpression.Tuple(vars.Select(SqlExpression.VariableReference).Cast<SqlExpression>().ToArray());
                }

                return new SelectIntoStatement(query, reference);
            }

            var statement = new SelectStatement(query);

            var orderBy = context.orderByClause();
            var forUpdate = context.forUpdateClause();

            if (orderBy != null) {
                var sortColumns = orderBy.orderByElements().orderByElement().Select(x => {
                    bool asc = x.DESC() == null;
                    var exp = Expression.Build(x.expression());
                    return new SortColumn(exp, asc);
                });

                statement.OrderBy = sortColumns;
            }

            statement.ForUpdate = forUpdate != null;

            var limit = context.queryLimitClause();
            if (limit != null) {
                var n1 = Number.PositiveInteger(limit.n1);
                var n2 = Number.PositiveInteger(limit.n2);

                if (n1 == null)
                    throw new ParseCanceledException("Invalid LIMIT clause");

                if (n2 != null) {
                    statement.Limit = new QueryLimit(n1.Value, n2.Value);
                } else {
                    statement.Limit = new QueryLimit(n1.Value);
                }
            }

            return statement;
        }
        public void SimpleOrderedSelect()
        {
            var query = (SqlQueryExpression) SqlExpression.Parse("SELECT * FROM test_table");
            var sort = new[] {new SortColumn(SqlExpression.Reference(new ObjectName("birth_date")), false)};
            var statement = new SelectStatement(query, sort);

            var result = statement.Execute(Query);
            Assert.IsNotNull(result);
            Assert.AreEqual(3, result.RowCount);

            var firstName = result.GetValue(0, 1);

            Assert.AreEqual("Roger", firstName.Value.ToString());
        }
Beispiel #9
0
 protected virtual SqlStatement VisitSelect(SelectStatement statement)
 {
     return new SelectStatement(statement.QueryExpression, statement.Limit, statement.OrderBy);
 }
        public void SerializeSelect()
        {
            var expression = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM table1 WHERE a = 1");
            var statement = new SelectStatement(expression);

            SerializeAndAssert(statement, (serialized, deserialized) => {
                Assert.IsNotNull(deserialized);
                Assert.IsInstanceOf<SelectStatement>(deserialized);

                Assert.IsNotNull(deserialized.QueryExpression);
            });
        }
Beispiel #11
0
 /* --------------- General Productions ends here --------------- */
 /* ----------- SQL productions start here ----------------- */
 public SelectStatement SelectStatement()
 {
     SelectStatement statement = new SelectStatement();
       TableSelectExpression tableSelect;
     tableSelect = SelectWithoutOrder();
       statement.SelectExpression = tableSelect;
     switch ((mcc_ntk==-1)?mcc_mntk():mcc_ntk) {
     case K_ORDER:
       OrderByClause(statement.OrderBy);
       break;
     default:
       mcc_la1[135] = mcc_gen;
       ;
       break;
     }
     switch ((mcc_ntk==-1)?mcc_mntk():mcc_ntk) {
     case K_FOR:
       ForUpdateClause();
       break;
     default:
       mcc_la1[136] = mcc_gen;
       ;
       break;
     }
     switch ((mcc_ntk==-1)?mcc_mntk():mcc_ntk) {
     case K_SKIP:
       mcc_consume_token(K_SKIP);
       ID("LOCKED");
       break;
     default:
       mcc_la1[137] = mcc_gen;
       ;
       break;
     }
       {return statement;}
     throw new Exception("Missing return statement in function");
 }
Beispiel #12
0
 public override void VisitSelect(SelectStatementNode node)
 {
     var queryExpression = (SqlQueryExpression) Expression(node.QueryExpression);
     if (node.QueryExpression.IntoClause != null) {
         var refExp = Expression(node.QueryExpression.IntoClause);
         statements.Add(new SelectIntoStatement(queryExpression, refExp));
     } else {
         var orderBy = OrderBy(node.OrderBy);
         var statement = new SelectStatement(queryExpression, orderBy);
         statements.Add(statement);
     }
 }
Beispiel #13
0
        protected override Statement Prepare(IQueryContext context)
        {
            var selectStatement = new SelectStatement {
                SelectExpression = SelectExpression,
                OrderBy = OrderBy
            };

            // check to see if the construct is the special one for
            // selecting the latest IDENTITY value from a table
            if (IsIdentitySelect(selectStatement.SelectExpression)) {
                selectStatement.SelectExpression.Columns.RemoveAt(0);
                FromTable fromTable = ((IList<FromTable>)selectStatement.SelectExpression.From.AllTables)[0];
                var idExp = Expression.FunctionCall("identity", Expression.Constant(DataObject.String(fromTable.Name.ToString())));
                selectStatement.SelectExpression.Columns.Add(new SelectColumn(idExp));
            }

            if (selectStatement.SelectExpression.Into != null)
                selectStatement.intoClause = selectStatement.SelectExpression.Into;

            // Generate the TableExpressionFromSet hierarchy for the expression,
            TableExpressionFromSet fromSet = Planner.GenerateFromSet(selectStatement.SelectExpression, context.Connection);

            // Form the plan
            selectStatement.Plan = Planner.FormQueryPlan(context.Connection, selectStatement.SelectExpression, fromSet, selectStatement.OrderBy);
            return selectStatement;
        }