def tearDown(self):
self.generating_connector.close()
connector = PostgresDatabaseConnector(None, autocommit=True)
if self.db_name is not None and connector.database_exists(
self.db_name):
connector.drop_database(self.db_name)
def tearDownClass(cls):
cls.index_selection.db_connector.close()
connector = PostgresDatabaseConnector(None, autocommit=True)
if connector.database_exists(cls.db_name):
connector.drop_database(cls.db_name)
def setUpClass(cls):
cls.db_name = "tpch_test_db_index_selection"
cls.index_selection = IndexSelection()
db = PostgresDatabaseConnector(None, autocommit=True)
SCALE_FACTOR = 0.001
table_generator = TableGenerator("tpch",
SCALE_FACTOR,
db,
explicit_database_name=cls.db_name)
db.close()
cls.index_selection.setup_db_connector(cls.db_name, "postgres")
# Filter worklaod
query_generator = QueryGenerator(
"tpch",
SCALE_FACTOR,
cls.index_selection.db_connector,
[3, 14],
table_generator.columns,
)
cls.small_tpch = Workload(query_generator.queries)
query_generator = QueryGenerator(
"tpch",
SCALE_FACTOR,
cls.index_selection.db_connector,
[5, 6],
table_generator.columns,
)
cls.tpch_5_and_6 = Workload(query_generator.queries)
def test_simple_statement(self):
db = PostgresDatabaseConnector(self.db_name, "postgres")
statement = "select count(*) from nation"
result = db.exec_fetch(statement)
self.assertEqual(result[0], 25)
db.close()
def tearDown(self):
connector = PostgresDatabaseConnector(None, autocommit=True)
dbs = [
"indexselection_tpch___0_001", "indexselection_tpcds___0_001",
"test_db"
]
for db in dbs:
if connector.database_exists(db):
connector.drop_database(db)
def test_generate_tpds(self):
table_generator = TableGenerator("tpcds", 0.001,
self.generating_connector)
# Check that lineitem table exists in TableGenerator
item_table = None
for table in table_generator.tables:
if table.name == "item":
item_table = table
break
self.assertIsNotNone(item_table)
# Check that i_item_sk column exists in TableGenerator and Table object
i_item_sk = Column("i_item_sk")
item_table.add_column(i_item_sk)
self.assertIn(i_item_sk, table_generator.columns)
self.assertIn(i_item_sk, table.columns)
database_connect = PostgresDatabaseConnector(
table_generator.database_name(), autocommit=True)
tpcds_tables = [
"call_center",
"catalog_page",
"catalog_returns",
"catalog_sales",
"customer",
"customer_address",
"customer_demographics",
"date_dim",
"household_demographics",
"income_band",
"inventory",
"item",
"promotion",
"reason",
"ship_mode",
"store",
"store_returns",
"store_sales",
"time_dim",
"warehouse",
"web_page",
"web_returns",
"web_sales",
"web_site",
]
for tpcds_table in tpcds_tables:
self.assertTrue(database_connect.table_exists(tpcds_table))
self.generating_connector.close()
database_connect.close()
class TestQueryGenerator(unittest.TestCase):
def setUp(self):
self.db_name = None
self.generating_connector = PostgresDatabaseConnector(None,
autocommit=True)
def tearDown(self):
self.generating_connector.close()
connector = PostgresDatabaseConnector(None, autocommit=True)
if self.db_name is not None and connector.database_exists(
self.db_name):
connector.drop_database(self.db_name)
def test_generate_tpch(self):
self.db_name = "tpch_test_db"
TableGenerator(
"tpch",
0.001,
self.generating_connector,
explicit_database_name=self.db_name,
)
db_connector = PostgresDatabaseConnector(self.db_name, autocommit=True)
query_generator = QueryGenerator("tpch", 0.001, db_connector, None, [])
queries = query_generator.queries
self.assertEqual(len(queries), 22)
db_connector.close()
def test_generate_tpcds(self):
self.db_name = "tpcds_test_db"
TableGenerator(
"tpcds",
0.001,
self.generating_connector,
explicit_database_name=self.db_name,
)
db_connector = PostgresDatabaseConnector(self.db_name, autocommit=True)
query_generator = QueryGenerator("tpcds", 1, db_connector, None, [])
queries = query_generator.queries
self.assertEqual(len(queries), 99)
db_connector.close()
def test_wrong_benchmark(self):
with self.assertRaises(NotImplementedError):
QueryGenerator("tpc-hallo", 1, self.generating_connector, None, [])
def test_generate_tpcds(self):
self.db_name = "tpcds_test_db"
TableGenerator(
"tpcds",
0.001,
self.generating_connector,
explicit_database_name=self.db_name,
)
db_connector = PostgresDatabaseConnector(self.db_name, autocommit=True)
query_generator = QueryGenerator("tpcds", 1, db_connector, None, [])
queries = query_generator.queries
self.assertEqual(len(queries), 99)
db_connector.close()
def setUpClass(cls):
cls.db_name = "tpch_test_db_database"
cls.scale_factor = 0.001
generating_connector = PostgresDatabaseConnector(None, autocommit=True)
table_generator = TableGenerator(
"tpch",
cls.scale_factor,
generating_connector,
explicit_database_name=cls.db_name,
)
cls.db = PostgresDatabaseConnector(cls.db_name)
query_generator = QueryGenerator("tpch", cls.scale_factor, cls.db,
[5, 6], table_generator.columns)
cls.workload = Workload(query_generator.queries)
generating_connector.close()
def setUpClass(cls):
cls.db_name = "tpch_test_db_algorithm"
cls.db_connector = PostgresDatabaseConnector(None, autocommit=True)
TableGenerator(
"tpch", 0.001, cls.db_connector, explicit_database_name=cls.db_name
)
cls.selection_algorithm = SelectionAlgorithm(cls.db_connector, {"test": 24})
cls.db_connector.close()
def test_calculate_best_only_executable_once(self):
workload = Workload([])
selection_algorithm = NoIndexAlgorithm(
PostgresDatabaseConnector(None, autocommit=True))
self.assertFalse(selection_algorithm.did_run)
selection_algorithm.calculate_best_indexes(workload)
self.assertTrue(selection_algorithm.did_run)
with self.assertRaises(AssertionError):
selection_algorithm.calculate_best_indexes(workload)
def test_generate_tpch(self):
table_generator = TableGenerator("tpch", 0.001,
self.generating_connector)
# Check that lineitem table exists in TableGenerator
lineitem_table = None
for table in table_generator.tables:
if table.name == "lineitem":
lineitem_table = table
break
self.assertIsNotNone(lineitem_table)
# Check that l_receiptdate column exists in TableGenerator and Table object
l_receiptdate = Column("l_receiptdate")
lineitem_table.add_column(l_receiptdate)
self.assertIn(l_receiptdate, table_generator.columns)
self.assertIn(l_receiptdate, table.columns)
database_connect = PostgresDatabaseConnector(
table_generator.database_name(), autocommit=True)
tpch_tables = [
"customer",
"lineitem",
"nation",
"orders",
"part",
"partsupp",
"region",
"supplier",
]
for tpch_table in tpch_tables:
self.assertTrue(database_connect.table_exists(tpch_table))
self.generating_connector.close()
database_connect.close()
def test_runtime_data_logging(self):
db = PostgresDatabaseConnector(self.db_name, "postgres")
query = Query(17, "SELECT count(*) FROM nation;")
db.get_cost(query)
self.assertEqual(db.cost_estimations, 1)
self.assertGreater(db.cost_estimation_duration, 0)
column_n_name = Column("n_name")
nation_table = Table("nation")
nation_table.add_column(column_n_name)
index = Index([column_n_name])
index_oid = db.simulate_index(index)[0]
self.assertGreater(db.index_simulation_duration, 0)
self.assertEqual(db.simulated_indexes, 1)
previou_simulation_duration = db.index_simulation_duration
db.drop_simulated_index(index_oid)
self.assertGreater(db.index_simulation_duration, previou_simulation_duration)
class TestTableGenerator(unittest.TestCase):
def setUp(self):
self.generating_connector = PostgresDatabaseConnector(None,
autocommit=True)
def tearDown(self):
connector = PostgresDatabaseConnector(None, autocommit=True)
dbs = [
"indexselection_tpch___0_001", "indexselection_tpcds___0_001",
"test_db"
]
for db in dbs:
if connector.database_exists(db):
connector.drop_database(db)
def test_database_name(self):
table_generator = TableGenerator("tpch", 0.001,
self.generating_connector)
self.assertEqual(table_generator.database_name(),
"indexselection_tpch___0_001")
self.assertTrue(
self.generating_connector.database_exists(
"indexselection_tpch___0_001"))
table_generator = TableGenerator("tpcds", 0.001,
self.generating_connector)
self.assertEqual(table_generator.database_name(),
"indexselection_tpcds___0_001")
self.assertTrue(
self.generating_connector.database_exists(
"indexselection_tpcds___0_001"))
table_generator = TableGenerator("tpch",
0.001,
self.generating_connector,
explicit_database_name="test_db")
self.assertEqual(table_generator.database_name(), "test_db")
self.assertTrue(self.generating_connector.database_exists("test_db"))
self.generating_connector.close()
def test_generate_tpch(self):
table_generator = TableGenerator("tpch", 0.001,
self.generating_connector)
# Check that lineitem table exists in TableGenerator
lineitem_table = None
for table in table_generator.tables:
if table.name == "lineitem":
lineitem_table = table
break
self.assertIsNotNone(lineitem_table)
# Check that l_receiptdate column exists in TableGenerator and Table object
l_receiptdate = Column("l_receiptdate")
lineitem_table.add_column(l_receiptdate)
self.assertIn(l_receiptdate, table_generator.columns)
self.assertIn(l_receiptdate, table.columns)
database_connect = PostgresDatabaseConnector(
table_generator.database_name(), autocommit=True)
tpch_tables = [
"customer",
"lineitem",
"nation",
"orders",
"part",
"partsupp",
"region",
"supplier",
]
for tpch_table in tpch_tables:
self.assertTrue(database_connect.table_exists(tpch_table))
self.generating_connector.close()
database_connect.close()
def test_generate_tpds(self):
table_generator = TableGenerator("tpcds", 0.001,
self.generating_connector)
# Check that lineitem table exists in TableGenerator
item_table = None
for table in table_generator.tables:
if table.name == "item":
item_table = table
break
self.assertIsNotNone(item_table)
# Check that i_item_sk column exists in TableGenerator and Table object
i_item_sk = Column("i_item_sk")
item_table.add_column(i_item_sk)
self.assertIn(i_item_sk, table_generator.columns)
self.assertIn(i_item_sk, table.columns)
database_connect = PostgresDatabaseConnector(
table_generator.database_name(), autocommit=True)
tpcds_tables = [
"call_center",
"catalog_page",
"catalog_returns",
"catalog_sales",
"customer",
"customer_address",
"customer_demographics",
"date_dim",
"household_demographics",
"income_band",
"inventory",
"item",
"promotion",
"reason",
"ship_mode",
"store",
"store_returns",
"store_sales",
"time_dim",
"warehouse",
"web_page",
"web_returns",
"web_sales",
"web_site",
]
for tpcds_table in tpcds_tables:
self.assertTrue(database_connect.table_exists(tpcds_table))
self.generating_connector.close()
database_connect.close()
def test_not_implemented(self):
with self.assertRaises(NotImplementedError):
TableGenerator("not_tpch", 0.001, self.generating_connector)
def test_tpcds_with_wrong_sf(self):
with self.assertRaises(Exception):
TableGenerator("tpcds", 0.002, self.generating_connector)
def test_postgres_index_simulation(self):
db = PostgresDatabaseConnector(self.db_name, "postgres")
self.assertTrue(db.supports_index_simulation())
db.close()
def tearDownClass(cls):
connector = PostgresDatabaseConnector(None, autocommit=True)
if connector.database_exists(cls.db_name):
connector.drop_database(cls.db_name)
def setUpClass(cls):
cls.db_name = "tpch_test_db_database"
db = PostgresDatabaseConnector(None, autocommit=True)
TableGenerator("tpch", 0.001, db, explicit_database_name=cls.db_name)
db.close()
def setUp(self):
self.generating_connector = PostgresDatabaseConnector(None,
autocommit=True)
