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_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_constant(self): name = qp.get_expression_name(qp.Constant(17)) self.assertEqual(name, '17') name = qp.get_expression_name(qp.Constant(datetime.date(2014, 1, 1))) self.assertEqual(name, '2014-01-01') name = qp.get_expression_name(qp.Constant('abc')) self.assertEqual(name, "'abc'")
def test_journal_with_account_func_and_from(self): journal = self.parse("JOURNAL 'liabilities' AT cost FROM year = 2014;") select = qc.transform_journal(journal) self.assertEqual( select, qp.Select([ qp.Target(qp.Column('date'), None), qp.Target(qp.Column('flag'), None), qp.Target( qp.Function('maxwidth', [qp.Column('payee'), qp.Constant(48)]), None), qp.Target( qp.Function('maxwidth', [qp.Column('narration'), qp.Constant(80)]), None), qp.Target(qp.Column('account'), None), qp.Target(qp.Function('cost', [qp.Column('position')]), None), qp.Target(qp.Function('cost', [qp.Column('balance')]), None), ], qp.From(qp.Equal(qp.Column('year'), qp.Constant(2014)), None, None, None), qp.Match(qp.Column('account'), qp.Constant('liabilities')), None, None, None, None, None))
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_expr_numerical(self): expected = qSelect( [qp.Target(qp.Add(qp.Constant(2), qp.Constant(3)), None)]) self.assertParse(expected, "SELECT 2+(3);") expected = qSelect( [qp.Target(qp.Sub(qp.Constant(2), qp.Constant(3)), None)]) self.assertParse(expected, "SELECT 2-(3);")
def test_expr_constant_date(self): self.assertParse( qSelect([qp.Target(qp.Constant(datetime.date(1972, 5, 28)), None)]), "SELECT 1972-05-28;") self.assertParse( qSelect([qp.Target(qp.Constant(datetime.date(1972, 5, 28)), None)]), "SELECT #'May 28, 1972';")
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 test_expr_constant_decimal(self): self.assertParse(qSelect([qp.Target(qp.Constant(D('17.345')), None)]), "SELECT 17.345;") self.assertParse(qSelect([qp.Target(qp.Constant(D('.345')), None)]), "SELECT .345;") self.assertParse(qSelect([qp.Target(qp.Constant(D('17.')), None)]), "SELECT 17.;")
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_expr_match(self): self.assertParse( qSelect([ qp.Target( qp.Match(qp.Column('a'), qp.Constant('Assets:.*:Checking')), None) ]), "SELECT a ~ 'Assets:.*:Checking';")
def test_balances_from_with_transformer_simple(self): self.assertParse( "BALANCES AT units WHERE date = 2014-01-01;", qp.Balances( 'units', None, qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1)))))
def test_print_from(self): self.assertParse( qp.Print( qp.From( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1))), None, True, None)), "PRINT FROM date = 2014-01-01 CLOSE;")
def test_expr_binaryop(self): self.assertEqual( qc.EvalEqual(qe.DateColumn(), qc.EvalConstant(datetime.date(2014, 1, 1))), qc.compile_expression( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1))), qe.TargetsEnvironment()))
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_journal_from(self): self.assertParse( qp.Journal( None, None, qp.From( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1))), None, True, None)), "JOURNAL FROM date = 2014-01-01 CLOSE;")
def test_balances_from_with_transformer(self): self.assertParse( "BALANCES AT units FROM date = 2014-01-01 CLOSE;", qp.Balances( 'units', qp.From( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1))), None, True, None), None))
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_balances_from(self): self.assertParse( "BALANCES FROM date = 2014-01-01 CLOSE;", qp.Balances( None, qp.From( qp.Equal(qp.Column('date'), qp.Constant(datetime.date(2014, 1, 1))), None, True, None), None))
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 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_journal(self): journal = self.parse("JOURNAL;") select = qc.transform_journal(journal) self.assertEqual( qp.Select([ qp.Target(qp.Column('date'), None), qp.Target(qp.Column('flag'), None), qp.Target( qp.Function('maxwidth', [qp.Column('payee'), qp.Constant(48)]), None), qp.Target( qp.Function('maxwidth', [qp.Column('narration'), qp.Constant(80)]), None), qp.Target(qp.Column('account'), None), qp.Target(qp.Column('position'), None), qp.Target(qp.Column('balance'), None), ], None, None, None, None, None, None, None, None), select)
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_explain_select(self): self.assertParse( qp.Explain( qSelect([ qp.Target(qp.Column('date'), None), qp.Target(qp.Column('account'), None) ], where_clause=qp.Match(qp.Column('account'), qp.Constant('etrade')))), "EXPLAIN SELECT date, account WHERE account ~ 'etrade';")
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_balance_with_units_and_from(self): balance = self.parse("BALANCES AT cost FROM year = 2014;") select = qc.transform_balances(balance) self.assertEqual( qp.Select([ qp.Target(qp.Column('account'), None), qp.Target( qp.Function( 'sum', [qp.Function('cost', [qp.Column('position')])]), None), ], qp.From(qp.Equal(qp.Column('year'), qp.Constant(2014)), None, None, None), None, self.group_by, self.order_by, None, None, None, None), select)
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_literals(self): # null self.assertParseTarget("SELECT NULL;", qp.Constant(None)) # bool self.assertParseTarget("SELECT TRUE;", qp.Constant(True)) self.assertParseTarget("SELECT FALSE;", qp.Constant(False)) # int self.assertParseTarget("SELECT 17;", qp.Constant(17)) # decimal self.assertParseTarget("SELECT 17.345;", qp.Constant(D('17.345'))) self.assertParseTarget("SELECT .345;", qp.Constant(D('.345'))) self.assertParseTarget("SELECT 17.;", qp.Constant(D('17.'))) # string self.assertParseTarget("SELECT 'rainy-day';", qp.Constant('rainy-day')) # date self.assertParseTarget("SELECT 1972-05-28;", qp.Constant(datetime.date(1972, 5, 28))) self.assertParseTarget("SELECT #'May 28, 1972';", qp.Constant(datetime.date(1972, 5, 28))) # not a list self.assertParseTarget("SELECT (1);", qp.Constant(1)) # list self.assertParseTarget("SELECT (1, );", qp.Constant([1])) self.assertParseTarget("SELECT (1, 2);", qp.Constant([1, 2])) self.assertParseTarget("SELECT (1, 2, );", qp.Constant([1, 2])) self.assertParseTarget("SELECT ('x', 'y', 'z');", qp.Constant(['x', 'y', 'z'])) # column self.assertParseTarget("SELECT date;", qp.Column('date'))
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')]))