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_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_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_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_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 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_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_expr_match(self): self.assertParse( qSelect([ qp.Target( qp.Match(qp.Column('a'), qp.Constant('Assets:.*:Checking')), None) ]), "SELECT a ~ 'Assets:.*:Checking';")
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') ]))
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_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_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 test_comments(self): self.assertParse( """SELECT first, /* comment */ second""", Select([ qp.Target(qp.Column('first'), None), qp.Target(qp.Column('second'), None) ])) self.assertParse( """SELECT first, /* comment */ second;""", Select([ qp.Target(qp.Column('first'), None), qp.Target(qp.Column('second'), None), ])) self.assertParse( """SELECT first, /**/ second;""", Select([ qp.Target(qp.Column('first'), None), qp.Target(qp.Column('second'), None), ])) self.assertParse( """SELECT first, /* /* */ second;""", Select([ qp.Target(qp.Column('first'), None), qp.Target(qp.Column('second'), None), ])) self.assertParse( """SELECT first, /* ; */ second;""", Select([ qp.Target(qp.Column('first'), None), qp.Target(qp.Column('second'), None), ]))
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_expr_div(self): self.assertParse( qSelect([qp.Target(qp.Div(qp.Column('a'), qp.Column('b')), None)]), r"SELECT a / b;")
def test_distinct(self): self.assertParse( "SELECT DISTINCT x;", Select([qp.Target(qp.Column('x'), None)], distinct=True))
def test_expr_not(self): self.assertParse(qSelect([qp.Target(qp.Not(qp.Column('a')), None)]), "SELECT NOT a;")
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_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_function__zero_args(self): self.assertParse(qSelect([qp.Target(qp.Function('random', []), None)]), "SELECT random();")
def test_target_one_as(self): self.assertParse(qSelect([qp.Target(qp.Column('date'), 'xdate')]), "SELECT date as xdate;")
def test_target_one(self): self.assertParse(qSelect([qp.Target(qp.Column('date'), None)]), "SELECT date;")
def test_expr_mul(self): self.assertParse( qSelect([qp.Target(qp.Mul(qp.Column('a'), qp.Column('b')), None)]), "SELECT a * b;")
def test_distinct(self): self.assertParse( qSelect([qp.Target(qp.Column('account'), None)], distinct=True), "SELECT DISTINCT account;")
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;")