def test_cross_join():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, JoinOp(LoadOp('employees'), LoadOp('employees')))
evaluate = compile(q)
eq_(len(list(evaluate(dict(dataset=dataset)))), 9)
def test_selection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
SelectionOp(LoadOp('employees'), EqOp(Var('manager_id'), NullConst())))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
])
q = Query(
dataset,
SelectionOp(LoadOp('employees'),
NotOp(EqOp(Var('manager_id'), NullConst()))))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))),
[(4567, 'Sally Sanders', date(2010, 2, 24), 1234,
()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234,
('sales', 'marketing'))])
def test_decorator_function_calls():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
@dataset.function(returns=dict(name="initials", type="STRING"))
def initials(name):
if name:
return ''.join([p[0] for p in name.split()])
else:
return None
q = Query(
dataset,
ProjectionOp(LoadOp('employees'), Function('initials', Var('full_name')))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
('TT',),
('SS',),
('MM',),
]
)
def test_function_in_from():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(
Function('flatten', LoadOp('employees'), StringConst('roles')),
Var('manager_id'), Var('roles')))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))),
[(1234, 'sales'), (1234, 'marketing')])
q = Query(dataset,
Function('flatten', LoadOp('employees'), StringConst('roles')))
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[[8901, 'Mark Markty',
date(2010, 3, 1), 1234, 'sales'],
[8901, 'Mark Markty',
date(2010, 3, 1), 1234, 'marketing']])
def test_relpace_views():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
no_managers = SelectionOp(
LoadOp('employees'),
IsOp(Var('manager_id'), NullConst())
)
dataset.create_view(
'no_managers',
no_managers
)
eq_(
replace_views(LoadOp('no_managers'), dataset),
no_managers
)
eq_(
replace_views(
JoinOp(
LoadOp('no_managers'),
LoadOp('no_managers')
),
dataset
),
JoinOp(
no_managers,
no_managers
)
)
def test_self_join_with_projection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(
JoinOp(
AliasOp('manager', LoadOp('employees')),
AliasOp('employee', LoadOp('employees')),
EqOp(Var('manager.employee_id'), Var('employee.manager_id'))
),
SelectAllExpr('employee'),
RenameOp('manager', Var('manager.full_name'))
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, (), 'Tom Tompson'),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing'), 'Tom Tompson')
]
)
def test_query_builder():
dataset = DataSet()
adapter = MockAdapter()
dataset.add_adapter(adapter)
query = dataset.select('x')
eq_(isinstance(query, QueryBuilder), True)
eq_(query.dataset, dataset)
eq_(query.column_exps, 'x')
def test_query_builder():
dataset = DataSet()
adapter = MockAdapter()
dataset.add_adapter(adapter)
query = dataset.select('x')
eq_(isinstance(query, QueryBuilder), True)
eq_(query.dataset, dataset)
eq_(query.column_exps, 'x')
def test_aggregation_whole_table():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, ProjectionOp(LoadOp('employees'), Function('count')))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(3, ),
])
def test_paramgetter():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, ProjectionOp(LoadOp(''), ParamGetterOp(0)))
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset, params=('foo', )))), [('foo', )])
def test_limit():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, SliceOp(LoadOp('employees'), 1))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
])
def test_offset_and_limit():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, SliceOp(LoadOp('employees'), 1, 2))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
])
def test_projection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, ProjectionOp(LoadOp('employees'), Var('full_name')))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))),
[('Tom Tompson', ), ('Sally Sanders', ),
('Mark Markty', )])
def test_offset():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, SliceOp(LoadOp('employees'), 1, None))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing')),
])
def test_order_by_asc():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(dataset, OrderByOp(LoadOp('employees'), Asc(Var('employee_id'))))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing')),
])
def test_get_relation():
dataset = DataSet()
adapter = MockAdapter()
dataset.add_adapter(adapter)
s_table = adapter.get_relation('bogus')
table = dataset.get_relation('bogus')
eq_(table, s_table)
assert_sequence_equal(dataset.relations, [('bogus', s_table)])
def test_addition():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(LoadOp('employees'),
AddOp(Var('employee_id'), NumberConst(1))))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [(1235, ),
(4568, ),
(8902, )])
def test_aggregation_on_column():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
GroupByOp(
ProjectionOp(LoadOp('employees'), Var('manager_id'),
Function('count')), Var('manager_id')))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [(None, 1),
(1234, 2)])
def test_cross_join():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
JoinOp(LoadOp('employees'), LoadOp('employees'))
)
evaluate = compile(q)
eq_(
len(list(evaluate(dict(dataset=dataset)))),
9
)
def test_projection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(LoadOp('employees'), Var('full_name'))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[('Tom Tompson',), ('Sally Sanders',), ('Mark Markty',)]
)
def test_get_relation():
dataset = DataSet()
adapter = MockAdapter()
dataset.add_adapter(adapter)
s_table = adapter.get_relation('bogus')
table = dataset.get_relation('bogus')
eq_(table, s_table)
assert_sequence_equal(
dataset.relations,
[('bogus', s_table)]
)
def test_addition():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(LoadOp('employees'), AddOp(Var('employee_id'), NumberConst(1)))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[(1235,), (4568,), (8902,)]
)
def test_views():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
# create a view off of an existing table
dataset.select('x').frm('bogus').create_view('only_x')
view = dataset.get_view('only_x')
eq_(view, AliasOp('only_x', ProjectionOp(LoadOp('bogus'), Var('x'))))
# create a view off of a view
dataset.select('x').frm('only_x').create_view('only_x_from_x')
view = dataset.get_view('only_x_from_x')
compare(
view,
# Todo: Implement a query optimizer that eliminates
# redunant projections ops like the one we see below
AliasOp(
'only_x_from_x',
ProjectionOp(
AliasOp('only_x', ProjectionOp(LoadOp('bogus'), Var('x'))),
Var('x'))))
def test_views():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
# create a view off of an existing table
dataset.select('x').frm('bogus').create_view('only_x')
view = dataset.get_view('only_x')
eq_(
view,
AliasOp('only_x', ProjectionOp(LoadOp('bogus'), Var('x')))
)
# create a view off of a view
dataset.select('x').frm('only_x').create_view('only_x_from_x')
view = dataset.get_view('only_x_from_x')
compare(
view,
# Todo: Implement a query optimizer that eliminates
# redunant projections ops like the one we see below
AliasOp('only_x_from_x', ProjectionOp(
AliasOp('only_x',ProjectionOp(LoadOp('bogus'), Var('x'))),
Var('x')
)
))
def test_replace_view_within_a_view():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
dataset.create_view(
'view1',
LoadOp('bogus')
)
dataset.create_view(
'view2',
LoadOp('view1')
)
dataset.create_view(
'view3',
SelectionOp(LoadOp('view2'), IsOp(Var('x'), NullConst()))
)
v1 = replace_views(
LoadOp('view3'),
dataset
)
compare(
v1,
AliasOp('view3',
SelectionOp(
AliasOp('view2', AliasOp('view1',LoadOp('bogus'))),
IsOp(Var('x'), NullConst())
)
)
)
def test_replace_views():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
no_managers = SelectionOp(
LoadOp('bogus'),
IsOp(Var('manager_id'), NullConst())
)
dataset.create_view(
'no_managers',
no_managers
)
view = AliasOp('no_managers', no_managers)
compare(
replace_views(LoadOp('no_managers'), dataset),
view
)
compare(
replace_views(
JoinOp(
LoadOp('no_managers'),
LoadOp('no_managers')
),
dataset
),
JoinOp(
view,
view
)
)
def test_offset_and_limit():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
SliceOp(LoadOp('employees'), 1,2)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
]
)
def test_complier():
adapter = MockAdapter()
def compile(query):
return lambda ctx, *params: Table(
adapter,
'results!',
schema=dict(fields=[dict(name="?column?", type="INTEGER")]))
dataset = DataSet()
dataset.add_adapter(adapter)
dataset.set_compiler(compile)
query = dataset.frm('bogus').query
table = dataset.execute(query)
def test_limit():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
SliceOp(LoadOp('employees'), 1)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
]
)
def test_aggregation_whole_table():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(LoadOp('employees'), Function('count'))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(3,),
]
)
def test_paramgetter():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(LoadOp(''), ParamGetterOp(0))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset, params=('foo',)))),
[
('foo',)
]
)
def test_offset():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
SliceOp(LoadOp('employees'), 1,None)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing')),
]
)
def test_self_join():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
JoinOp(AliasOp('employee', LoadOp('employees')),
AliasOp('manager', LoadOp('employees')),
EqOp(Var('manager.employee_id'), Var('employee.manager_id'))))
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[(4567, 'Sally Sanders', date(2010, 2, 24), 1234,
(), 1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234,
('sales', 'marketing'), 1234, 'Tom Tompson', date(2009, 1, 17), None,
())])
def test_order_by_asc():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
OrderByOp(LoadOp('employees'), Asc(Var('employee_id')))
)
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset)))
,
[
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing')),
]
)
def test_function_calls():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
dataset.add_function(
name='initials',
function=lambda name: ''.join([p[0] for p in name.split()])
if name else None,
returns=dict(name="initials", type="STRING"))
q = Query(
dataset,
ProjectionOp(LoadOp('employees'), Function('initials',
Var('full_name'))))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))), [
('TT', ),
('SS', ),
('MM', ),
])
def test_self_join_with_projection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(
JoinOp(
AliasOp('manager', LoadOp('employees')),
AliasOp('employee', LoadOp('employees')),
EqOp(Var('manager.employee_id'), Var('employee.manager_id'))),
SelectAllExpr('employee'),
RenameOp('manager', Var('manager.full_name'))))
evaluate = compile(q)
assert_sequence_equal(list(evaluate(dict(dataset=dataset))),
[(4567, 'Sally Sanders', date(2010, 2, 24), 1234,
(), 'Tom Tompson'),
(8901, 'Mark Markty', date(2010, 3, 1), 1234,
('sales', 'marketing'), 'Tom Tompson')])
def test_aggregation_on_column():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
GroupByOp(
ProjectionOp(LoadOp('employees'), Var('manager_id'), Function('count')),
Var('manager_id')
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(None,1),
(1234,2)
]
)
def test_get_schema():
class Adapter(object):
def has(self, name):
return name == 'computed'
def evaluate(self, loc):
return loc.replace(Function('myschema'))
dataset = DataSet()
dataset.add_adapter(Adapter())
# Todo: figure out why I have to invoke this decorator here
@dataset.function(
returns=lambda: Schema([dict(name='field', type='string')]))
def myschema(ctx):
pass
schema = dataset.get_schema('computed')
eq_(schema, Schema([dict(name='field', type='string')]))
def test_selection():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
SelectionOp(
LoadOp('employees'),
EqOp(Var('manager_id'), NullConst())
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
]
)
q = Query(
dataset,
SelectionOp(
LoadOp('employees'),
NotOp(EqOp(Var('manager_id'), NullConst()))
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing'))
]
)
def test_get_schema():
class Adapter(object):
def has(self, name): return name == 'computed'
def evaluate(self, loc):
return loc.replace(Function('myschema'))
dataset = DataSet()
dataset.add_adapter(Adapter())
# Todo: figure out why I have to invoke this decorator here
@dataset.function(returns = lambda:Schema([dict(name='field', type='string')]))
def myschema(ctx):
pass
schema = dataset.get_schema('computed')
eq_(
schema,
Schema([dict(name='field', type='string')])
)
def test_complier():
adapter = MockAdapter()
def compile(query):
return lambda ctx, *params: Table(
adapter,
'results!',
schema = dict(
fields = [
dict(name="?column?", type="INTEGER")
]
)
)
dataset = DataSet()
dataset.add_adapter(adapter)
dataset.set_compiler(compile)
query = dataset.frm('bogus').query
table = dataset.execute(query)
def test_function_in_from():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
ProjectionOp(
Function('flatten', LoadOp('employees'), StringConst('roles')),
Var('manager_id'),
Var('roles')
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(1234, 'sales'),
(1234, 'marketing')
]
)
q = Query(
dataset,
Function('flatten', LoadOp('employees'), StringConst('roles'))
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
[8901, 'Mark Markty', date(2010, 3, 1), 1234, 'sales'],
[8901, 'Mark Markty', date(2010, 3, 1), 1234, 'marketing']
]
)
def test_self_join():
dataset = DataSet()
dataset.add_adapter(EmployeeAdapter())
q = Query(
dataset,
JoinOp(
AliasOp('employee', LoadOp('employees')),
AliasOp('manager', LoadOp('employees')),
EqOp(Var('manager.employee_id'), Var('employee.manager_id'))
)
)
evaluate = compile(q)
assert_sequence_equal(
list(evaluate(dict(dataset=dataset))),
[
(4567, 'Sally Sanders', date(2010, 2, 24), 1234, (), 1234, 'Tom Tompson', date(2009, 1, 17), None, ()),
(8901, 'Mark Markty', date(2010, 3, 1), 1234, ('sales', 'marketing'), 1234, 'Tom Tompson', date(2009, 1, 17), None, ())
]
)
def test_replace_views():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
no_managers = SelectionOp(LoadOp('bogus'),
IsOp(Var('manager_id'), NullConst()))
dataset.create_view('no_managers', no_managers)
view = AliasOp('no_managers', no_managers)
compare(replace_views(LoadOp('no_managers'), dataset), view)
compare(
replace_views(JoinOp(LoadOp('no_managers'), LoadOp('no_managers')),
dataset), JoinOp(view, view))
def test_replace_view_within_a_view():
dataset = DataSet()
adapter = dataset.add_adapter(MockAdapter())
dataset.create_view('view1', LoadOp('bogus'))
dataset.create_view('view2', LoadOp('view1'))
dataset.create_view(
'view3', SelectionOp(LoadOp('view2'), IsOp(Var('x'), NullConst())))
v1 = replace_views(LoadOp('view3'), dataset)
compare(
v1,
AliasOp(
'view3',
SelectionOp(AliasOp('view2', AliasOp('view1', LoadOp('bogus'))),
IsOp(Var('x'), NullConst()))))
def mock_data_set():
dataset = DataSet()
dataset.add_adapter(MockAdapter())
dataset.add_adapter(EmployeeAdapter())
return dataset
def mock_data_set(): dataset = DataSet() dataset.add_adapter(MockAdapter()) dataset.add_adapter(EmployeeAdapter()) return dataset