def test_selectable_recipe(self):
"""Test a selectable that is a recipe"""
recipe = (Recipe(
shelf=mytable_shelf,
session=oven.Session()).metrics("age").dimensions("first"))
b = SQLAlchemyBuilder(selectable=recipe)
type_examples = """
[age] -> num
[first] -> str
[first] > "foo" -> bool
age * 2 -> num
"""
for field, expected_data_type in self.examples(type_examples):
_, data_type = b.parse(field)
self.assertEqual(data_type, expected_data_type)
sql_examples = """
[age] -> anon_1.age
[first] -> anon_1.first
[first] > "foo" -> anon_1.first > 'foo'
age * 2 -> anon_1.age * 2
"""
for field, expected_sql in self.examples(sql_examples):
expr, _ = b.parse(field, debug=True)
self.assertEqual(to_sql(expr), expected_sql)
def test_session(self):
recipe = self.recipe().metrics("age").dimensions("first")
assert (recipe.to_sql() == """SELECT foo.first AS first,
sum(foo.age) AS age
FROM foo
GROUP BY first""")
assert recipe.all()[0].first == "hi"
assert recipe.all()[0].age == 15
assert recipe.stats.rows == 1
sess = oven.Session()
recipe.reset()
recipe.session(sess)
def test_ser_deser(self):
# Can't tests with date conversions and freeze time :/
good_examples = f"""
sum([score]) -> sum(datatypes.score)
sum(score) -> sum(datatypes.score)
month(if([score] > 2, test_datetime)) -> date_trunc('month', CASE WHEN (datatypes.score > 2) THEN datatypes.test_datetime END)
if(test_datetime > date("2020-01-01"), test_datetime) -> CASE WHEN (datatypes.test_datetime > '2020-01-01 00:00:00') THEN datatypes.test_datetime END
month(if([score] > 2, test_datetime)) -> date_trunc('month', CASE WHEN (datatypes.score > 2) THEN datatypes.test_datetime END)
if(score<2,"babies",score<13,"children",score<20,"teens","oldsters") -> CASE WHEN (datatypes.score < 2) THEN 'babies' WHEN (datatypes.score < 13) THEN 'children' WHEN (datatypes.score < 20) THEN 'teens' ELSE 'oldsters' END
if((score)<2,"babies",(score)<13,"children",(score)<20,"teens","oldsters") -> CASE WHEN (datatypes.score < 2) THEN 'babies' WHEN (datatypes.score < 13) THEN 'children' WHEN (datatypes.score < 20) THEN 'teens' ELSE 'oldsters' END
"""
for field, expected_sql in self.examples(good_examples):
expr, _ = self.builder.parse(field,
forbid_aggregation=False,
debug=True)
ser = dumps(expr)
expr = loads(ser, self.builder.selectable.metadata, oven.Session())
self.assertEqual(to_sql(expr), expected_sql)
def setup(self):
self.session = oven.Session()
self.shelf = mytable_shelf