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); } }
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()); }
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); }); }
/* --------------- 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"); }
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); } }
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; }