def test_mem_index_scan__sort(self):
schema = ('id', 'name', 'age', 'major')
data = [(1, 'Brian', 30, 'eng'), (2, 'Jason', 33, 'econ'),
(3, 'Christie', 28, 'accounting'), (4, 'Gayle', 33, 'edu'),
(5, 'Carolyn', 33, 'econ'), (6, 'Michael', 65, 'law'),
(7, 'Lori', 62, 'business')]
# index on age
projection = lambda r: r[2]
operator = operators.LessThan('age', 34, schema)
result = list(
execute(
tree([
Sort(lambda r: (r[1], r[0])),
[
Projection(lambda r: (r[1], r[2])),
[BPlusTree(data, projection).search(operator)]
]
])))
expected = [
('Christie', '28'),
('Brian', '30'),
('Carolyn', '33'),
('Gayle', '33'),
('Jason', '33'),
]
self.assertEquals(result, expected)
def test_sort_merge_join(self):
expected_joins = [
('3', 'Christie', '28', 'accounting', '3', 'accounting', '3'),
('7', 'Lori', '62', 'business', '7', 'business', '4'),
('2', 'Jason', '33', 'econ', '2', 'econ', '2'),
('5', 'Carolyn', '33', 'econ', '2', 'econ', '2'),
('4', 'Gayle', '33', 'edu', '4', 'edu', '4.5'),
('1', 'Brian', '30', 'eng', '1', 'eng', '4'),
('6', 'Michael', '65', 'law', '6', 'law', '5'),
]
theta = lambda _row1, _row2: _row1[3] == _row2[1]
projection1 = lambda r: r[3]
projection2 = lambda r: r[1]
result = list(
execute(
tree([
SortMergeJoin(theta, projection1, projection2),
[Sort(projection1), [Scan(self._data, pop_headers=True)]],
[
Sort(projection2),
[Scan(self.majors_gpa, pop_headers=True)]
],
])))
self.assertEquals(result, expected_joins)
def test_mem_index_scan__sort__merge_join(self):
# index on age
schema = ('id', 'name', 'age', 'major')
data = [(1, 'Brian', 30, 'eng'), (2, 'Jason', 33, 'econ'),
(3, 'Christie', 28, 'accounting'), (4, 'Gayle', 33, 'edu'),
(5, 'Carolyn', 33, 'econ'), (6, 'Michael', 65, 'law'),
(7, 'Lori', 62, 'business')]
projection = lambda r: r[2]
operator = operators.LessThan('age', 34, schema)
# index on gpa
schema2 = ('id', 'major', 'gpa')
data2 = [
('3', 'accounting', 3),
('7', 'business', 4),
('2', 'econ', 2),
('4', 'edu', 4.5),
('1', 'eng', 4),
('6', 'law', 5),
]
projection2 = lambda r: r[2]
operator2 = operators.GreaterThan('gpa', 3, schema2)
theta = lambda r1, r2: r1[1] == r2[0]
# select d.name, d.major, d2.major, d2.gpa
# from data d, data2 d2
# where d.major = d2.major
# and d.age < 34
# and d2.gpa > 3
# since it's sorted on major, edu comes first
# since sort uses csv reader, everything's a string
expected_joins = [
('Gayle', 'edu', 'edu', '4.5'),
('Brian', 'eng', 'eng', '4'),
]
result = list(
execute(
tree([
SortMergeJoin(theta, lambda r: r[1], lambda r: r[0]),
[
Sort(lambda r: r[1]),
[
Projection(lambda r: (r[1], r[3])),
[BPlusTree(data, projection).search(operator)]
]
],
[
Sort(lambda r: r[0]),
[
Projection(lambda r: (r[1], r[2])),
[BPlusTree(data2, projection2).search(operator2)]
]
]
])))
self.assertEquals(result, expected_joins)
def test_tree__one(self):
scan_node = Scan(self._data)
result = tree([scan_node])
expected = scan_node
self.assertEquals(result, expected)
result = scan_node._inputs
expected = []
self.assertEquals(result, expected)
def test_eof(self):
main = tree([
Count(),
[
Projection(lambda a: a),
[Scan([1, 2, 3], pop_headers=False)],
],
])
self.assertEquals(next(main), (3, ))
result = next(main)
self.assertEquals(result, Count.EOF)
def test_next(self):
result = list(
execute(
tree([
Count(),
[
Projection(lambda a: a),
[Scan(self._data, pop_headers=True)],
],
])))
expected = [(7, )]
self.assertEquals(result, expected)
def test_tree__two(self):
selection_node = Selection(lambda a: a)
scan_node = Scan(self._data)
result = tree([selection_node, [scan_node]])
expected = selection_node
self.assertEquals(result, expected)
self.assertIn(
scan_node,
selection_node._inputs,
)
self.assertEquals(len(selection_node._inputs), 1)
def test_mem_index_scan__count(self):
schema = ('id', 'name', 'age', 'major')
data = [(1, 'Brian', '30', 'eng'), (2, 'Jason', '33', 'econ'),
(3, 'Christie', '28', 'accounting'), (4, 'Gayle', '33', 'edu'),
(5, 'Carolyn', '33', 'econ'), (6, 'Michael', '65', 'law'),
(7, 'Lori', '62', 'business')]
# index on id
projection = lambda r: r[0]
operator = operators.GreaterThan('id', 4, schema)
result = list(
execute(
tree([Count(), [BPlusTree(data,
projection).search(operator)]])))
expected = [(3, )]
self.assertEquals(result, expected)
def test_tree__nested(self):
projection_node = Projection(lambda a: a)
selection_node = Selection(lambda a: a)
scan_node = Scan([1, 2, 3])
result = tree([projection_node, [selection_node, [scan_node]]])
self.assertEquals(result, projection_node)
self.assertIn(
selection_node,
projection_node._inputs,
)
self.assertEquals(len(projection_node._inputs), 1)
self.assertIn(
scan_node,
selection_node._inputs,
)
self.assertEquals(len(selection_node._inputs), 1)
def test_nested_loop_join(self):
expected_joins = [
('1', 'Brian', '30', 'eng', '1', 'eng', '4'),
('2', 'Jason', '33', 'econ', '2', 'econ', '2'),
('3', 'Christie', '28', 'accounting', '3', 'accounting', '3'),
('4', 'Gayle', '33', 'edu', '4', 'edu', '4.5'),
('5', 'Carolyn', '33', 'econ', '2', 'econ', '2'),
('6', 'Michael', '65', 'law', '6', 'law', '5'),
('7', 'Lori', '62', 'business', '7', 'business', '4')
]
theta = lambda _row1, _row2: _row1[3] == _row2[1]
result = list(
execute(
tree([
NestedLoopJoin(theta),
[Scan(self._data, pop_headers=True)],
[Scan(self.majors_gpa, pop_headers=True)],
])))
self.assertEquals(result, expected_joins)