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_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)
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_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_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_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_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_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_function(self):
name = qp.get_expression_name(
qp.Function('length', [qp.Column('date')]))
self.assertEqual(name, 'length(date)')
def test_expr_function(self):
self.assertEqual(
qe.SumPosition([qe.PositionColumn()]),
qc.compile_expression(qp.Function('sum', [qp.Column('position')]),
qe.TargetsEnvironment()))
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_function(self):
self.assertEqual(
'length_date',
qp.get_expression_name(qp.Function('length', [qp.Column('date')])))
def test_expr_function__zero_args(self):
self.assertParse(qSelect([qp.Target(qp.Function('random', []), None)]),
"SELECT random();")
def test_expr_function__two_args(self):
self.assertParse(
qSelect([
qp.Target(qp.Function(
'min', [qp.Column('a'), qp.Column('b')]), None)
]), "SELECT min(a, b);")
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')]),
qp.Ordering.ASC)
]
def test_balance(self):
balance = self.parse("BALANCES;")
select = qc.transform_balances(balance)
self.assertEqual(
select,
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))
def test_balance_with_units(self):
balance = self.parse("BALANCES AT cost;")
select = qc.transform_balances(balance)
self.assertEqual(
select,
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))
def test_balance_with_units_and_from(self):
balance = self.parse("BALANCES AT cost FROM year = 2014;")
select = qc.transform_balances(balance)
self.assertEqual(
select,
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))
def test_print(self):
self.assertCompile(qc.EvalPrint(None), "PRINT;")
def test_print_from(self):
self.assertCompile(
qc.EvalPrint(
qc.EvalFrom(
qc.Operator(qp.Equal, [
qe.YearEntryColumn(),
qc.EvalConstant(2014),
]), None, None, None)), """PRINT FROM year = 2014;""")
