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__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__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_complex_expressions(self): self.assertParseTarget( "SELECT a != (b != (42 AND 17));", qp.Not( qp.Equal( qp.Column('a'), qp.Not( qp.Equal(qp.Column('b'), qp.And(qp.Constant(42), qp.Constant(17)))))))
def setUp(self): super().setUp() self.targets = [ qp.Target(qp.Column('a'), None), qp.Target(qp.Column('b'), None) ] self.expr = qp.Equal( qp.Column('d'), qp.And(qp.Function('max', [qp.Column('e')]), qp.Constant(17)))
def test_from_and_where(self): expr = qp.Equal( qp.Column('d'), qp.And(qp.Function('max', [qp.Column('e')]), qp.Constant(17))) self.assertParse( "SELECT a, b FROM d = (max(e) and 17) WHERE d = (max(e) and 17);", Select([ qp.Target(qp.Column('a'), None), qp.Target(qp.Column('b'), None) ], qp.From(expr, None, None, None), expr))
def test_operators_precedence(self): self.assertParseTarget( "SELECT a AND b OR c AND d;", qp.Or(qp.And(qp.Column('a'), qp.Column('b')), qp.And(qp.Column('c'), qp.Column('d')))) self.assertParseTarget( "SELECT a = 2 AND b != 3;", qp.And(qp.Equal(qp.Column('a'), qp.Constant(2)), qp.Not(qp.Equal(qp.Column('b'), qp.Constant(3))))) self.assertParseTarget("SELECT not a AND b;", qp.And(qp.Not(qp.Column('a')), qp.Column('b'))) self.assertParseTarget( "SELECT a + b AND c - d;", qp.And(qp.Add(qp.Column('a'), qp.Column('b')), qp.Sub(qp.Column('c'), qp.Column('d')))) self.assertParseTarget( "SELECT a * b + c / d - 3;", 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))) self.assertParseTarget( "SELECT 'orange' IN tags AND 'bananas' IN tags;", qp.And(qp.Contains(qp.Constant('orange'), qp.Column('tags')), qp.Contains(qp.Constant('bananas'), qp.Column('tags'))))
def test_where(self): expr = qp.Equal( qp.Column('d'), qp.And(qp.Function('max', [qp.Column('e')]), qp.Constant(17))) self.assertParse( "SELECT a, b WHERE d = (max(e) and 17);", Select([ qp.Target(qp.Column('a'), None), qp.Target(qp.Column('b'), None) ], None, expr)) with self.assertRaises(qp.ParseError): self.parse("SELECT a, b WHERE;")
def test_expr_paren_multi2(self): self.assertParse( qSelect([ qp.Target( qp.Not( qp.Equal( qp.Column('a'), qp.Not( qp.Equal( qp.Column('b'), qp.And(qp.Constant(42), qp.Constant(17)))))), None) ]), "SELECT a != (b != (42 AND 17));")
def test_select_from(self): expr = qp.Equal( qp.Column('d'), qp.And(qp.Function('max', [qp.Column('e')]), qp.Constant(17))) with self.assertRaises(qp.ParseError): self.parse("SELECT a, b FROM;") # simple self.assertParseFrom("SELECT a, b FROM d = (max(e) and 17);", qp.From(expr, None, None, None)) # open dated self.assertParseFrom( "SELECT a, b FROM d = (max(e) and 17) OPEN ON 2014-01-01;", qp.From(expr, datetime.date(2014, 1, 1), None, None)) # close default self.assertParseFrom("SELECT a, b FROM d = (max(e) and 17) CLOSE;", qp.From(expr, None, True, None)) # close dated self.assertParseFrom( "SELECT a, b FROM d = (max(e) and 17) CLOSE ON 2014-10-18;", qp.From(expr, None, datetime.date(2014, 10, 18), None)) # close no expression self.assertParseFrom("SELECT a, b FROM CLOSE;", qp.From(None, None, True, None)) # close no expression dated self.assertParseFrom( "SELECT a, b FROM CLOSE ON 2014-10-18;", qp.From(None, None, datetime.date(2014, 10, 18), None)) # clear default self.assertParseFrom("SELECT a, b FROM d = (max(e) and 17) CLEAR;", qp.From(expr, None, None, True)) # open close clear self.assertParseFrom( "SELECT a, b FROM d = (max(e) and 17) OPEN ON 2013-10-25 CLOSE ON 2014-10-25 CLEAR;", qp.From(expr, datetime.date(2013, 10, 25), datetime.date(2014, 10, 25), True))
def test_binary(self): name = qp.get_expression_name(qp.And(qp.Column('a'), qp.Column('b'))) self.assertEqual(name, 'and(a, b)')
def test_expressions(self): # comparison operators self.assertParseTarget("SELECT a = 42;", qp.Equal(qp.Column('a'), qp.Constant(42))) self.assertParseTarget( "SELECT a != 42;", qp.Not(qp.Equal(qp.Column('a'), qp.Constant(42)))) self.assertParseTarget("SELECT a > 42;", qp.Greater(qp.Column('a'), qp.Constant(42))) self.assertParseTarget("SELECT a >= 42;", qp.GreaterEq(qp.Column('a'), qp.Constant(42))) self.assertParseTarget("SELECT a < 42;", qp.Less(qp.Column('a'), qp.Constant(42))) self.assertParseTarget("SELECT a <= 42;", qp.LessEq(qp.Column('a'), qp.Constant(42))) self.assertParseTarget("SELECT a ~ 'abc';", qp.Match(qp.Column('a'), qp.Constant('abc'))) self.assertParseTarget( "SELECT a != 42;", qp.Not(qp.Equal(qp.Column('a'), qp.Constant(42)))) self.assertParseTarget("SELECT not a;", qp.Not(qp.Column('a'))) self.assertParseTarget("SELECT a IS NULL;", qp.IsNull(qp.Column('a'))) self.assertParseTarget("SELECT a IS NOT NULL;", qp.IsNotNull(qp.Column('a'))) # bool expressions self.assertParseTarget("SELECT a AND b;", qp.And(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a OR b;", qp.Or(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT NOT a;", qp.Not(qp.Column('a'))) # math expressions with identifiers self.assertParseTarget("SELECT a * b;", qp.Mul(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a / b;", qp.Div(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a + b;", qp.Add(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a+b;", qp.Add(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a - b;", qp.Sub(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT a-b;", qp.Sub(qp.Column('a'), qp.Column('b'))) self.assertParseTarget("SELECT +a;", qp.Column('a')) self.assertParseTarget("SELECT -a;", qp.Neg(qp.Column('a'))) # math expressions with numerals self.assertParseTarget("SELECT 2 * 3;", qp.Mul(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2 / 3;", qp.Div(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2+(3);", qp.Add(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT (2)-3;", qp.Sub(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2 + 3;", qp.Add(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2+3;", qp.Add(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2 - 3;", qp.Sub(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT 2-3;", qp.Sub(qp.Constant(2), qp.Constant(3))) self.assertParseTarget("SELECT +2;", qp.Constant(2)) self.assertParseTarget("SELECT -2;", qp.Constant(-2)) # silly, fails at compile time self.assertParseTarget("SELECT -'abc';", qp.Neg(qp.Constant('abc'))) # functions self.assertParseTarget("SELECT random();", qp.Function('random', [])) self.assertParseTarget("SELECT min(a);", qp.Function('min', [qp.Column('a')])) self.assertParseTarget( "SELECT min(a, b);", qp.Function('min', [qp.Column('a'), qp.Column('b')]))
def test_binary(self): self.assertEqual( 'and_account_date', qp.get_expression_name( qp.And(qp.Column('account'), qp.Column('date'))))
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_expr_and(self): self.assertParse( qSelect([qp.Target(qp.And(qp.Column('a'), qp.Column('b')), None)]), "SELECT a AND b;")