def test_multi_component_bitmap_index_multiple():
database = generate_database()
conn = sqlite3.connect(":memory:")
create_database_sqlite(database, conn)
cursor = conn.cursor()
for first_month, first_day, last_month, last_day in [
(1, 1, 12, 31),
(1, 1, 1, 1),
(12, 31, 12, 31),
(2, 3, 11, 27),
(2, 1, 11, 31),
(5, 7, 5, 23),
(5, 1, 5, 2),
(3, 30, 3, 31),
(12, 30, 12, 31),
(12, 1, 12, 31),
]:
test_multi_component_bitmap_index(database, first_month, first_day,
last_month, last_day, cursor)
print("For comparison, our multi-component bitmap index query"
"took up to 2.5 milliseconds and the SQL query took "
"55 milliseconds on our test computer.")
def test_your_query():
database: Database = generate_database()
sale: RelationTuple = database["sale"][-1]
year: int = database["time"][sale["time_id"]]["year"]
state: str = database["location"][sale["location_id"]]["state"]
category: str = database["product"][sale["product_id"]]["category"]
expected_relation: Relation = compute_expected_relation(
database, year, state, category)
start_time: float = perf_counter()
# The example queries will run out of memory or take months to complete.
# relation = ex1_b.example_query_allowed_but_slow(database, year, state, category)
# relation = ex1_b.example_query_for_loops_not_allowed(database, year, state, category)
relation: Relation = ex1_b.your_query(database, year, state, category)
elapsed_time: float = perf_counter() - start_time
check_relations_equal(relation, expected_relation)
print(f"Your query took {elapsed_time:.4f} seconds.\n")
print(
"For comparison, the SQL query took 0.05 seconds and the Python query took 0.17 seconds on our test computer."
)
def test_campaign_revenue():
database: Database = generate_database()
start_time: float = perf_counter()
revenue: float = compute_campaign_revenue(database)
elapsed_time: float = perf_counter() - start_time
print(f"Your query took {elapsed_time:.4f} seconds.\n")
connection = sqlite3.connect(":memory:")
create_database_sqlite(database, connection)
query = """
SELECT
SUM(product.price * sale.quantity)
FROM
sale,
time,
product
WHERE
sale.time_id = time.time_id AND
sale.product_id = product.product_id AND
EXISTS (
SELECT * FROM campaign WHERE
campaign.timestamp_start <= time.timestamp AND
time.timestamp <= campaign.timestamp_end
)
"""
start_time: float = perf_counter()
with connection:
cursor = connection.cursor()
result = cursor.execute(query).fetchone()
expected_revenue: float = result[0]
if abs(revenue - expected_revenue) > 0.01:
raise ValueError(
f"You computed a revenue of {revenue}, but the value should have been {expected_revenue}."
)
elapsed_time: float = perf_counter() - start_time
print(f"The SQL query took {elapsed_time:.4f} seconds.\n")
print(
"For comparison, our Python implementation took 0.3 seconds and the SQL query took 7 seconds on our test computer."
)
def test_bitmap_index():
database: Database = generate_database()
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
create_database_sqlite(database, conn)
bitmap_indexes = build_bitmap_index_for_months(database)
for first_month in range(1, 10):
last_month = first_month + 3
test_bitmap_index_for_months(cursor, bitmap_indexes, first_month,
last_month)
def test_natural_join_large():
database: Database = generate_database()
start_time: float = perf_counter()
relation: Relation = natural_join(database["sale"], database["product"])
elapsed_time: float = perf_counter() - start_time
print(
f"Your natural_join implementation took {elapsed_time:.4f} seconds for the large test.\n"
)
expected_relation = compute_expected_relation(database)
print(
"On our test computer, the timing results are 0.2 seconds and 0.35 seconds respectively."
)
# Discard all but some products to make this test run faster.
relation = where_equal(relation, "quantity", 1)
expected_relation = where_equal(expected_relation, "quantity", 1)
check_relations_equal(relation, expected_relation)