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_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_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_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_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__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__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_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__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 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_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_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_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_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)
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_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_balances_from_with_transformer_simple(self):
self.assertParse(
qp.Balances(
'units', None,
qp.Equal(qp.Column('date'),
qp.Constant(datetime.date(2014, 1, 1)))),
"BALANCES AT units WHERE date = 2014-01-01;")
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_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(self):
self.assertParse(
qp.Balances('units',
qp.From(qp.Equal(qp.Column('date'),
qp.Constant(datetime.date(2014, 1, 1))),
None, True, None),
None),
"BALANCES AT units FROM date = 2014-01-01 CLOSE;")
def test_balances_from(self):
self.assertParse(
qp.Balances(
None,
qp.From(
qp.Equal(qp.Column('date'),
qp.Constant(datetime.date(2014, 1, 1))), None,
True, None)), "BALANCES FROM date = 2014-01-01 CLOSE;")
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_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_journal_with_account_and_from(self):
journal = self.parse("JOURNAL 'liabilities' FROM year = 2014;")
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),
],
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, None),
select)
def compile_targets(targets, environ):
"""Compile the targets and check for their validity. Process wildcard.
Args:
targets: A list of target expressions from the parser.
environ: A compilation context for the targets.
Returns:
A list of compiled target expressions with resolved names.
"""
# Bind the targets expressions to the execution context.
if isinstance(targets, query_parser.Wildcard):
# Insert the full list of available columns.
targets = [
query_parser.Target(query_parser.Column(name), None)
for name in environ.wildcard_columns
]
# Compile targets.
c_targets = []
target_names = set()
for target in targets:
c_expr = compile_expression(target.expression, environ)
target_name = find_unique_name(
target.name or query_parser.get_expression_name(target.expression),
target_names)
target_names.add(target_name)
c_targets.append(EvalTarget(c_expr, target_name, is_aggregate(c_expr)))
# Figure out if this query is an aggregate query and check validity of each
# target's aggregation type.
for index, c_target in enumerate(c_targets):
columns, aggregates = get_columns_and_aggregates(c_target.c_expr)
# Check for mixed aggregates and non-aggregates.
if columns and aggregates:
raise CompilationError(
"Mixed aggregates and non-aggregates are not allowed")
if aggregates:
# Check for aggregates of aggregates.
for aggregate in aggregates:
for child in aggregate.childnodes():
if is_aggregate(child):
raise CompilationError(
"Aggregates of aggregates are not allowed")
return c_targets
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)
