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_readme_example(self):
schema = Schema(
"students",
Column("id", DataType.INT, ColumnAttr.PRIMARY_KEY),
Column("name", DataType.STRING),
)
with pydb.connect("mem:test") as db:
db.exec(CreateTable(schema))
db.exec(Insert("students", ("id", "name"), (0, "ack")))
result = db.exec(Select(("id", "name"), From("students")))
assert list(result) == [(0, "ack")]
def test_create_table_bad_schema(self):
with self.assertRaises(SchemaError):
self.db.exec(CreateTable(Schema("")))
with self.assertRaises(SchemaError):
self.db.exec(CreateTable(Schema("foo")))
with self.assertRaises(SchemaError):
self.db.exec(
CreateTable(
Schema(
"foo",
Column("bar", DataType.STRING),
Column("bar", DataType.INT),
)))
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_mem_table(self):
schema = Schema(
"students",
Column("id", DataType.INT, ColumnAttr.PRIMARY_KEY,
ColumnAttr.AUTO_INCREMENT),
Column("name", DataType.STRING),
Column("age", DataType.INT),
)
table = MemTable(schema)
self.assertEqual(table.schema(), schema)
self.assertEqual(table.get(0), None)
self.assertEqual(list(table.rows()), [])
self.assertEqual(len(table.indexes()), 1)
student = (0, "rohit", 20)
rowid, row = table.insert(student)
self.assertEqual(row, student)
self.assertEqual(table.get(rowid), row)
self.assertEqual(list(table.rows()), [row])
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)
from pydb.query import (
BinExpr,
Const,
From,
Join,
JoinKind,
On,
Select,
Symbol,
Where,
)
from pydb.table import Column, ColumnAttr, DataType, Schema
students = Schema(
"students",
Column("id", DataType.INT, ColumnAttr.PRIMARY_KEY,
ColumnAttr.AUTO_INCREMENT),
Column("name", DataType.STRING),
Column("age", DataType.INT),
)
class SimplePlannerTestCase(unittest.TestCase):
def setUp(self):
self.table = MemTable(students)
self.planner = SimplePlanner({"students": self.table})
def test_full_scan(self):
query = Select(students.column_names(), From("students"))
plan = self.planner.plan(query)
expected = Scan(self.table)
self.assertEqual(plan, expected)