def test_groupby_numbers(self): self.assertParse("SELECT * GROUP BY 1;", Select(qp.Wildcard(), group_by=qp.GroupBy([1], None))) self.assertParse( "SELECT * GROUP BY 2, 4, 5;", Select(qp.Wildcard(), group_by=qp.GroupBy([2, 4, 5], None)))
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_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_select(self): with self.assertRaises(qp.ParseError): self.parse("SELECT") with self.assertRaises(qp.ParseError): self.parse("SELECT ; ") self.assertParse("SELECT *;", Select(qp.Wildcard())) self.assertParse("SELECT date;", Select([qp.Target(qp.Column('date'), None)])) self.assertParse( "SELECT date, account", Select([ qp.Target(qp.Column('date'), None), qp.Target(qp.Column('account'), None) ])) self.assertParse("SELECT date as xdate;", Select([qp.Target(qp.Column('date'), 'xdate')])) self.assertParse( "SELECT date as x, account, position as y;", Select([ qp.Target(qp.Column('date'), 'x'), qp.Target(qp.Column('account'), None), qp.Target(qp.Column('position'), 'y') ]))
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_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_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_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_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_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_pivotby(self): with self.assertRaises(qp.ParseError): self.parse("SELECT * PIVOT BY;") with self.assertRaises(qp.ParseError): self.parse("SELECT * PIVOT BY a;") with self.assertRaises(qp.ParseError): self.parse("SELECT * PIVOT BY a, b, c") self.assertParse( "SELECT * PIVOT BY a, b", Select(qp.Wildcard(), pivot_by=qp.PivotBy([qp.Column('a'), qp.Column('b')]))) self.assertParse("SELECT * PIVOT BY 1, 2", Select(qp.Wildcard(), pivot_by=qp.PivotBy([1, 2])))
def test_orderby_many_asc_desc(self): self.assertParse( "SELECT * ORDER BY a ASC, b DESC, c;", Select(qp.Wildcard(), order_by=[ qp.OrderBy(qp.Column('a'), qp.Ordering.ASC), qp.OrderBy(qp.Column('b'), qp.Ordering.DESC), qp.OrderBy(qp.Column('c'), qp.Ordering.ASC) ]))
def test_from_select(self): self.assertParse( """ SELECT a, b FROM ( SELECT * FROM date = 2014-05-02 ) WHERE c = 5 LIMIT 100;""", Select([ qp.Target(qp.Column('a'), None), qp.Target(qp.Column('b'), None) ], Select( qp.Wildcard(), qp.From( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 5, 2))), None, None, None)), qp.Equal(qp.Column('c'), qp.Constant(5)), limit=100))
def test_limit_and_flatten(self): self.assertParse(qSelect(qp.Wildcard(), limit=100, flatten=True), "SELECT * LIMIT 100 FLATTEN;")
def test_limit_present(self): self.assertParse("SELECT * LIMIT 45;", Select(qp.Wildcard(), limit=45))
def test_orderby_desc(self): self.assertParse( "SELECT * ORDER BY a DESC;", Select(qp.Wildcard(), order_by=[qp.OrderBy(qp.Column('a'), qp.Ordering.DESC)]))
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_groupby_one(self): self.assertParse( "SELECT * GROUP BY a;", Select(qp.Wildcard(), group_by=qp.GroupBy([qp.Column('a')], None)))
def test_target_wildcard(self): self.assertParse(qSelect(qp.Wildcard()), "SELECT *;")
def test_orderby_desc(self): self.assertParse( qSelect(qp.Wildcard(), order_by=qp.OrderBy([qp.Column('a')], 'DESC')), "SELECT * ORDER BY a DESC;")
def test_flatten(self): self.assertParse(qSelect(qp.Wildcard(), flatten=True), "SELECT * FLATTEN;")
def test_pivotby_one(self): self.assertParse( qSelect(qp.Wildcard(), pivot_by=qp.PivotBy([qp.Column('a')])), "SELECT * PIVOT BY a;")