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_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 compute_expected_relation(database: Database) -> Relation:
connection = sqlite3.connect(":memory:")
create_database_sqlite(database, connection)
query = """
SELECT
sale.sale_id,
sale.time_id,
sale.location_id,
sale.product_id,
sale.quantity,
product.name,
product.category,
product.subcategory,
product.price
FROM
sale,
product
WHERE
sale.product_id = product.product_id
"""
start_time: float = perf_counter()
relation: Relation = []
with connection:
cursor = connection.cursor()
attributes = [
"sale_id",
"time_id",
"location_id",
"product_id",
"quantity",
"name",
"category",
"subcategory",
"price",
]
for tup in cursor.execute(query):
relation.append(dict(zip(attributes, tup)))
elapsed_time: float = perf_counter() - start_time
print(f"For comparison, the SQL query took {elapsed_time:.4f} seconds.")
return relation
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 compute_expected_relation(
database: Database,
year: int,
state: str,
category: str,
) -> Relation:
connection = sqlite3.connect(":memory:")
create_database_sqlite(database, connection)
query = """
SELECT
product.price,
sale.quantity
FROM
sale,
time,
product,
location
WHERE
sale.time_id = time.time_id AND
sale.product_id = product.product_id AND
sale.location_id = location.location_id AND
time.year = ? AND
location.state = ? AND
product.category = ?
"""
start_time = perf_counter()
relation: Relation = []
with connection:
cursor = connection.cursor()
for price, quantity in cursor.execute(query, (year, state, category)):
tup = {
"price": price,
"quantity": quantity,
}
relation.append(tup)
elapsed_time = perf_counter() - start_time
print(f"For comparison, the SQL query took {elapsed_time:.4f} seconds.\n")
return relation