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