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_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__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_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_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_target_multiple_as(self): self.assertParse( qSelect([ qp.Target(qp.Column('date'), 'xdate'), qp.Target(qp.Column('account'), None), qp.Target(qp.Column('position'), 'xposition') ]), "SELECT date as xdate, account, position as xposition;")
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__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 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_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_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_balance(self): balance = self.parse("BALANCES;") select = qc.transform_balances(balance) self.assertEqual( qp.Select([ qp.Target(qp.Column('account'), None), qp.Target(qp.Function('sum', [qp.Column('position')]), None), ], None, None, self.group_by, self.order_by, None, None, None, None), select)
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_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_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_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_arity(self): # Compile with the correct number of arguments. qc.compile_expression(qp.Function('sum', [qp.Column('number')]), qe.TargetsEnvironment()) # Compile with an incorrect number of arguments. with self.assertRaises(qc.CompilationError): qc.compile_expression( qp.Function('sum', [qp.Column('date'), qp.Column('account')]), qe.TargetsEnvironment())
def test_expr_function__five_args(self): self.assertParse( qSelect([ qp.Target( qp.Function('min', [ qp.Column('a'), qp.Column('b'), qp.Column('c'), qp.Column('d'), qp.Column('e') ]), None) ]), "SELECT min(a, b, c, d, e);")
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_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_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_expr_match(self): self.assertParse( qSelect([ qp.Target( qp.Match(qp.Column('a'), qp.Constant('Assets:.*:Checking')), None) ]), "SELECT a ~ 'Assets:.*:Checking';")
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;")
class TestTranslationBalance(CompileSelectBase): group_by = qp.GroupBy([ qp.Column('account'), qp.Function('account_sortkey', [qp.Column(name='account')]) ], None) order_by = qp.OrderBy( [qp.Function('account_sortkey', [qp.Column('account')])], None) def test_balance(self): balance = self.parse("BALANCES;") select = qc.transform_balances(balance) self.assertEqual( qp.Select([ qp.Target(qp.Column('account'), None), qp.Target(qp.Function('sum', [qp.Column('position')]), None), ], None, None, self.group_by, self.order_by, None, None, None, None), select) def test_balance_with_units(self): balance = self.parse("BALANCES AT cost;") 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) ], None, None, self.group_by, self.order_by, None, None, None, None), select) 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_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_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_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') ]))