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;
}
