def test3_querying_the_database_does_not_cause_exception(self):
# Arrange
exception_raised = False
database = db._connect_to_database()
# Act
try:
db._query_database('show tables;')
except:
exception_raised = True
# Assert
self.assertFalse(exception_raised)
database.close()
def test1_database_should_have_5_tables(self):
# Arrange
database = db._connect_to_database()
# Act
response = db._query_database('show tables;')
# Assert
self.assertEqual(len(response), 5)
db._close_database(database)
def fetch_orders_from_round(round_id: int):
query = "SELECT * FROM orders WHERE round_id=" + str(round_id)
results = db._query_database(query)
orders = []
for result in results:
order = _parse_order_row(result)
orders.append(order)
return orders
def get_person_by_names(first_name:str,surname:str,slack_id:str):
try:
query = f'SELECT * FROM people WHERE first_name="{first_name}" AND surname="{surname}" AND slack_id="{slack_id}"'
results = db._query_database(query)
if len(results) > 0:
person = _parse_person_row(results[0])
return person
else:
print("There was no-one found that matched your query.")
except:
print("Connection Error.")
def get_drink(drink_name:str):
try:
query = f"SELECT * FROM drinks WHERE drink_name=\"{drink_name}\""
results = db._query_database(query)
if len(results) == 1:
drink = _parse_drink_row(results[0])
return drink
else:
print("There was no drink found with this name.")
except:
print("Connection Error.")
def get_preference(person_id:int):
try:
query = "SELECT drink_name FROM preferences WHERE person_id="+str(person_id)
results = db._query_database(query)
if len(results) == 1:
drink_name = results[0][0]
return drink_name
else:
print("There was no preference found for this person.")
except:
print("Connection Error.")
def get_round_by_id(round_id:int):
try:
query = "SELECT * FROM rounds WHERE round_id="+str(round_id)
results = db._query_database(query)
if len(results) == 1:
active_round = _parse_round_row(results[0])
return active_round
else:
print("There was no active round found.")
except:
print("Connection Error.")
def get_person_by_id(person_id:int):
try:
query = "SELECT * FROM people WHERE person_id = "+str(person_id)
results = db._query_database(query)
if len(results) == 1:
person = _parse_person_row(results[0])
return person
else:
print("There was no-one found that matched your query.")
except:
print("Connection Error.")
def get_active_round():
try:
query = "SELECT * FROM rounds WHERE active=1"
results = db._query_database(query)
if len(results) > 0:
active_round = _parse_round_row(results[0])
return active_round
else:
print("There was no active round found.")
except:
print("Connection Error.")
def test5_drinks_table_should_have_correct_columns(self):
# Arrange
database = db._connect_to_database()
passed = True
# Act
response = db._query_database('describe drinks;')
# Assert
if response[0] != ('drink_name', 'varchar(100)', 'NO', 'PRI', None,
''):
passed = False
if response[1] != ('drink_type', 'varchar(100)', 'YES', '', None, ''):
passed = False
self.assertTrue(passed)
db._close_database(database)
def test10_preferences_table_should_have_correct_columns(self):
# Arrange
database = db._connect_to_database()
passed = True
# Act
response = db._query_database('describe preferences;')
# Assert
if response[0] != ('person_id', 'int(11)', 'NO', 'PRI', None, ''):
passed = False
if response[1] != ('drink_name', 'varchar(100)', 'YES', 'MUL', None,
''):
passed = False
self.assertTrue(passed)
db._close_database(database)
def test7_rounds_table_should_have_correct_columns(self):
# Arrange
database = db._connect_to_database()
passed = True
# Act
response = db._query_database('describe rounds;')
# Assert
if response[0] != ('round_id', 'int(11)', 'NO', 'PRI', None,
'auto_increment'):
passed = False
if response[1] != ('owner_id', 'int(11)', 'YES', 'MUL', None, ''):
passed = False
if response[2] != ('active', 'tinyint(1)', 'YES', '', '1', ''):
passed = False
self.assertTrue(passed)
db._close_database(database)
def test3_people_table_should_have_correct_columns(self):
# Arrange
database = db._connect_to_database()
passed = True
# Act
response = db._query_database('describe people;')
# Assert
if response[0] != ('person_id', 'int(11)', 'NO', 'PRI', None,
'auto_increment'):
passed = False
if response[1] != ('first_name', 'varchar(100)', 'YES', '', None, ''):
passed = False
if response[2] != ('surname', 'varchar(100)', 'YES', '', None, ''):
passed = False
if response[3] != ('slack_id', 'varchar(100)', 'YES', '', None, ''):
passed = False
self.assertTrue(passed)
db._close_database(database)
def test8_orders_table_should_have_correct_columns(self):
# Arrange
database = db._connect_to_database()
passed = True
# Act
response = db._query_database('describe orders;')
# Assert
if response[0] != ('order_id', 'int(11)', 'NO', 'PRI', None,
'auto_increment'):
passed = False
if response[1] != ('person_id', 'int(11)', 'YES', 'MUL', None, ''):
passed = False
if response[2] != ('drink_name', 'varchar(100)', 'YES', 'MUL', None,
''):
passed = False
if response[3] != ('round_id', 'int(11)', 'YES', 'MUL', None, ''):
passed = False
self.assertTrue(passed)
db._close_database(database)
def alter_preference(person_id:int, drink_name:str):
try:
query = f"UPDATE preferences SET drink_name=\"{drink_name}\" WHERE person_id={str(person_id)}"
db._query_database(query,False)
except:
print("Connection Error.")
def set_round_inactive(round_id: int):
query = f"UPDATE rounds SET active=0 WHERE round_id={round_id}"
db._query_database(query, False)
def create_test_table():
db._query_database("CREATE TABLE "+test_table_name+" ( "+test_table_field+" VARCHAR(100) );")
def delete_test_table():
db._query_database(f"DROP TABLE {test_table_name};")
