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_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_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 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)
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 test_expr_or(self):
self.assertParse(
qSelect([qp.Target(qp.Or(qp.Column('a'), qp.Column('b')), None)]),
"SELECT a OR b;")
def test_expr_and(self):
self.assertParse(
qSelect([qp.Target(qp.And(qp.Column('a'), qp.Column('b')), None)]),
"SELECT a AND b;")
def test_expr_paren_multi(self):
self.assertParse(
qSelect([
qp.Target(qp.Not(qp.Equal(qp.Column('a'), qp.Constant(42))),
None)
]), "SELECT not (a = 42);")
def test_expr_lte(self):
self.assertParse(
qSelect(
[qp.Target(qp.LessEq(qp.Column('a'), qp.Constant(42)), None)]),
"SELECT a <= 42;")
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_target_one(self):
self.assertParse(qSelect([qp.Target(qp.Column('date'), None)]),
"SELECT date;")
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_mul(self):
self.assertParse(
qSelect([qp.Target(qp.Mul(qp.Column('a'), qp.Column('b')), None)]),
"SELECT a * b;")
def test_expr_function__zero_args(self):
self.assertParse(qSelect([qp.Target(qp.Function('random', []), None)]),
"SELECT random();")
def test_expr_constant_null(self):
self.assertParse(qSelect([qp.Target(qp.Constant(None), None)]),
"SELECT NULL;")
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_not(self):
self.assertParse(qSelect([qp.Target(qp.Not(qp.Column('a')), None)]),
"SELECT NOT a;")
def test_expr_sub(self):
expected = qSelect(
[qp.Target(qp.Sub(qp.Column('a'), qp.Column('b')), None)])
self.assertParse(expected, "SELECT a - b;")
self.assertParse(expected, "SELECT a-b;")
def test_expr_constant_boolean(self):
self.assertParse(qSelect([qp.Target(qp.Constant(True), None)]),
"SELECT TRUE;")
self.assertParse(qSelect([qp.Target(qp.Constant(False), None)]),
"SELECT FALSE;")
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_function__two_args(self):
self.assertParse(
qSelect([
qp.Target(qp.Function(
'min', [qp.Column('a'), qp.Column('b')]), None)
]), "SELECT min(a, b);")
def test_expr_constant_integer(self):
self.assertParse(qSelect([qp.Target(qp.Constant(17), None)]),
"SELECT 17;")
def test_expr_div(self):
self.assertParse(
qSelect([qp.Target(qp.Div(qp.Column('a'), qp.Column('b')), None)]),
r"SELECT a / b;")
def test_expr_constant_string(self):
self.assertParse(qSelect([qp.Target(qp.Constant('rainy-day'), None)]),
"SELECT 'rainy-day';")
def test_distinct(self):
self.assertParse(
qSelect([qp.Target(qp.Column('account'), None)], distinct=True),
"SELECT DISTINCT account;")
def test_expr_ne(self):
self.assertParse(
qSelect([
qp.Target(qp.Not(qp.Equal(qp.Column('a'), qp.Constant(42))),
None)
]), "SELECT a != 42;")
def test_target_one_as(self):
self.assertParse(qSelect([qp.Target(qp.Column('date'), 'xdate')]),
"SELECT date as xdate;")
def test_expr_gte(self):
self.assertParse(
qSelect([
qp.Target(qp.GreaterEq(qp.Column('a'), qp.Constant(42)), None)
]), "SELECT a >= 42;")
