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')]))
