def test_hash_join(self):
signups = Schema(
"signups",
Column("id", DataType.INT, ColumnAttr.PRIMARY_KEY),
Column("name", DataType.STRING),
)
table2 = MemTable(signups)
query = Select(
("students.id", "signups.id"),
From(
Join(
("students", "signups"),
On(
BinExpr("=", Symbol("students.name"),
Symbol("signups.name"))),
JoinKind.INNER,
)),
)
expected = ColumnProjection(
HashJoin(
Scan(self.table),
Scan(table2),
students.columnid("name"),
signups.columnid("name"),
),
(students.columnid("id"),
len(students.columns) + signups.columnid("id")),
)
planner = SimplePlanner({"students": self.table, "signups": table2})
plan = planner.plan(query)
self.assertEqual(plan, expected)
def test_indexed_join(self):
table2 = MemTable(
Schema("signups",
Column("sid", DataType.INT, ColumnAttr.PRIMARY_KEY)))
query = Select(
("students.name", "signups.sid"),
From(
Join(
("students", "signups"),
On(
BinExpr("=", Symbol("students.id"),
Symbol("signups.sid"))),
JoinKind.INNER,
)),
)
assert any(table2.indexes("sid"))
expected = ColumnProjection(
IndexedJoin(
Scan(self.table),
table2.schema().columnid("sid"),
table2.indexes("sid")[0],
table2,
),
(
students.columnid("name"),
len(students.columns) + table2.schema().columnid("sid"),
),
)
planner = SimplePlanner({"students": self.table, "signups": table2})
plan = planner.plan(query)
self.assertEqual(plan, expected)
def test_indexed_lookup(self):
assert any(self.table.indexes("id")), "missing index"
query = Select(
students.column_names(),
From("students"),
Where(BinExpr("=", Symbol("id"), Const(42))),
)
plan = self.planner.plan(query)
index = self.table.indexes("id")[0]
expected = IndexedLookup(self.table, index, 42)
self.assertEqual(plan, expected)
def test_filtered_scan(self):
query = Select(
("name", "age"),
From("students"),
Where(BinExpr("=", Symbol("name"), Const("matt"))),
)
plan = self.planner.plan(query)
expected = ColumnProjection(
FilteredScan(
self.table,
ValueComp(BinOp.EQ, students.columnid("name"), "matt")),
(students.columnid("name"), students.columnid("age")),
)
self.assertEqual(plan, expected)
def test_select_with_name_filter(self):
students = [
(0, "ark", 10),
(1, "bam", 11),
(2, "bam", 12),
]
self._insert(*students)
results = self.db.exec(
Select(
STUDENTS_SCHEMA.column_names(),
From("students"),
Where(BinExpr("=", Symbol("name"), Const("bam"))),
))
self.assertEqual(list(results), students[1:])
def test_select_by_primary_key(self):
students = [
(0, "ark", 10),
(1, "bam", 11),
(2, "cam", 12),
]
self._insert(*students)
results = self.db.exec(
Select(
STUDENTS_SCHEMA.column_names(),
From("students"),
Where(BinExpr("=", Symbol("id"), Const(1))),
))
self.assertEqual(list(results), [students[1]])
def test_delete(self):
students = [create_test_student(x) for x in range(10)]
self._insert(*students)
self.db.exec(Delete(From("students")))
results = list(
self.db.exec(
Select(STUDENTS_SCHEMA.column_names(), From("students"))))
self.assertEqual(results, [])
students = [(x, str(x % 2), 99) for x in range(10)]
self.db.exec(Delete(From("students")),
Where(BinExpr("=", Symbol("name"), Const("0"))))
results = list(
self.db.exec(
Select(STUDENTS_SCHEMA.column_names(), From("students"))))
self.assertEqual(results, [s for s in students if s[1] != "0"])
def test_select_join(self):
signups_schema = Schema(
"signups",
Column("id", DataType.INT, ColumnAttr.PRIMARY_KEY,
ColumnAttr.AUTO_INCREMENT),
Column("sid", DataType.INT),
Column("timestamp", DataType.INT),
)
self.db.exec(CreateTable(signups_schema))
students = [
(0, "abe", 20),
(1, "bark", 30),
(2, "cab", 40),
]
signups = [
(0, 0, 100),
(1, 1, 101),
(2, 2, 103),
]
for student in students:
self._insert(student)
for signup in signups:
self.db.exec(
Insert("signups", signups_schema.column_names(), signup))
results = self.db.exec(
Select(
("students.name", "signups.timestamp"),
From(
Join(
("students", "signups"),
On(
BinExpr("=", Symbol("students.id"),
Symbol("signups.sid"))),
)),
))
expected = [
("abe", 100),
("bark", 101),
("cab", 103),
]
self.assertEqual(list(results), expected)