def test_groupby_numbers(self):
self.assertParse(
qSelect(qp.Wildcard(), group_by=qp.GroupBy([1], None)),
"SELECT * GROUP BY 1;")
self.assertParse(
qSelect(qp.Wildcard(), group_by=qp.GroupBy([2, 4, 5], None)),
"SELECT * GROUP BY 2, 4, 5;")
def test_groupby_expr(self):
self.assertParse(
qSelect(qp.Wildcard(),
group_by=qp.GroupBy([
qp.Greater(qp.Function('length', [qp.Column('a')]), qp.Constant(0)),
qp.Column('b')], None)),
"SELECT * GROUP BY length(a) > 0, b;")
def test_pivotby_many(self):
self.assertParse(
qSelect(qp.Wildcard(),
pivot_by=qp.PivotBy(
[qp.Column('a'),
qp.Column('b'),
qp.Column('c')])), "SELECT * PIVOT BY a, b , c;")
def test_groupby_many(self):
self.assertParse(
qSelect(qp.Wildcard(),
group_by=qp.GroupBy(
[qp.Column('a'),
qp.Column('b'),
qp.Column('c')], None)), "SELECT * GROUP BY a, b, c;")
def test_expr_function__and_plus_minus(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.And(qp.Add(qp.Column('a'), qp.Column('b')),
qp.Sub(qp.Column('c'),
qp.Column('d')))),
"SELECT * WHERE a + b AND c - d;")
def test_expr_function__and_eq(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.And(
qp.Equal(qp.Column('a'), qp.Constant(2)),
qp.Not(qp.Equal(qp.Column('b'), qp.Constant(3))))),
"SELECT * WHERE a = 2 AND b != 3;")
def test_expr_function__and_or(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.Or(qp.And(qp.Column('a'), qp.Column('b')),
qp.And(qp.Column('c'),
qp.Column('d')))),
"SELECT * WHERE a AND b OR c AND d;")
def test_orderby_many(self):
self.assertParse(
qSelect(qp.Wildcard(),
order_by=qp.OrderBy(
[qp.Column('a'),
qp.Column('b'),
qp.Column('c')], None)), "SELECT * ORDER BY a, b, c;")
def test_expr_function__membership_precedence(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.And(
qp.Contains(qp.Constant('orange'), qp.Column('tags')),
qp.Contains(qp.Constant('bananas'),
qp.Column('tags')))),
"SELECT * WHERE 'orange' IN tags AND 'bananas' IN tags;")
def test_groupby_having(self):
self.assertParse(
qSelect(qp.Wildcard(),
group_by=qp.GroupBy(
[qp.Column('a')],
qp.Equal(qp.Function('sum', [qp.Column('position')]),
qp.Constant(0)))),
"SELECT * GROUP BY a HAVING sum(position) = 0;")
def test_expr_function__mul_div_plus_minus(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.Sub(
qp.Add(
qp.Mul(qp.Column(name='a'), qp.Column(name='b')),
qp.Div(qp.Column(name='c'), qp.Column(name='d'))),
qp.Constant(value=3))),
"SELECT * WHERE a * b + c / d - 3;")
def test_from_select(self):
subselect = qSelect(
qp.Wildcard(),
qp.From(qp.Equal(qp.Column('date'),
qp.Constant(datetime.date(2014, 5, 2))),
None, None, None))
expected = qSelect([qp.Target(qp.Column('a'), None),
qp.Target(qp.Column('b'), None)],
subselect,
qp.Equal(qp.Column('c'), qp.Constant(5)),
limit=100)
self.assertParse(expected, """
SELECT a, b FROM (
SELECT * FROM date = 2014-05-02
) WHERE c = 5 LIMIT 100;
""")
def test_limit_and_flatten(self):
self.assertParse(qSelect(qp.Wildcard(), limit=100, flatten=True),
"SELECT * LIMIT 100 FLATTEN;")
def test_target_wildcard(self):
self.assertParse(qSelect(qp.Wildcard()), "SELECT *;")
def test_flatten(self):
self.assertParse(qSelect(qp.Wildcard(), flatten=True),
"SELECT * FLATTEN;")
def test_limit_present(self):
self.assertParse(qSelect(qp.Wildcard(), limit=45),
"SELECT * LIMIT 45;")
def test_expr_function__and_not(self):
self.assertParse(
qSelect(qp.Wildcard(),
where_clause=qp.And(qp.Not(qp.Column('a')),
qp.Column('b'))),
"SELECT * WHERE not a AND b;")
def test_pivotby_one(self):
self.assertParse(
qSelect(qp.Wildcard(), pivot_by=qp.PivotBy([qp.Column('a')])),
"SELECT * PIVOT BY a;")
def test_orderby_desc(self):
self.assertParse(
qSelect(qp.Wildcard(),
order_by=qp.OrderBy([qp.Column('a')], 'DESC')),
"SELECT * ORDER BY a DESC;")