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)