import unittest
import math
import sqlite3
class PrimaryKeyTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "Primary Key"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['X', 9999, 10, 10, 5.1, 5.1]
#add the extra lake
stu_cursor.execute(command, params)
failed = False
#try adding it again, which should fail
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Primary Key was not propertly integrated into the table.")
command = "SELECT * FROM lakes WHERE name = ?"
params = ("X",)
stu_cursor.execute(command, params)
results = stu_cursor.fetchall()
test = len(results) == 1
message = "Couldn't add a normal record."
self.assertTrue(test, message)
command = "DELETE FROM lakes WHERE name = ?"
params = ('X')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class NoNullNamesTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "No NULL names"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = [None, 9999, 10, 10, 5.1, 5.1]
failed = False
#try adding the NULL-named lake
try:
stu_cursor.execute(command)
except:
failed = True
self.assertTrue(failed, "Lakes can have NULL names.")
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class NonNegativeSurfaceAreasTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "Non-negative surface areas"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['X', -.05, 10, 10, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have negative surface areas.")
params = ['Z', 9999, 10, 10, 5.1, 5.1]
success = False
#try adding the legal lake
try:
stu_cursor.execute(command, params)
success = True
except:
sucesss = False
self.assertTrue(success, "Lakes cannot have some positive surface areas.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('X')
stu_cursor.execute(command, params)
command = "DELETE FROM lakes WHERE name = ?"
params = ('Z')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class PositiveVolumesTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "Positive Volumes"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['X', 9999, -50, 10, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have negative volumes.")
params = ['X', 9999, 0, 10, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have zero volume.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('X')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class PositiveMaxDepthsTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "Positive max depths"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['X', 9999, 10, -3, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have negative max depths.")
params = ['X', 9999, 10, 0, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have zero max depth.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('X')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class LegitimateLatitudesTests(unittest.TestCase):
def points(self):
return 15
def test_name(self):
return "Legitimate Latitudes"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['A', 9999, 10, 10, -90.5, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have latitudes that are too low.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('A')
stu_cursor.execute(command, params)
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['B', 9999, 10, 10, 90.5, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have latitudes that are too high.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('B')
stu_cursor.execute(command, params)
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['Y', 9999, 10, 10, 89, 5.1]
success = False
#try adding the legal lake
try:
stu_cursor.execute(command, params)
success = True
except:
sucesss = False
self.assertTrue(success, "Lakes cannot have some legal positive latitudes.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('Y')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class LegitimateLongitudesTests(unittest.TestCase):
def points(self):
return 15
def test_name(self):
return "Legitimate Longitudes"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['A', 9999, 10, 10, 5.1, -180.00001]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have longitudes that are too low.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('A')
stu_cursor.execute(command, params)
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['B', 9999, 10, 10, 90.5, 181]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have longitudes that are too high.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('B')
stu_cursor.execute(command, params)
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['Y', 9999, 10, 10, 5.1, 179.9]
success = False
#try adding the legal lake
try:
stu_cursor.execute(command, params)
success = True
except:
sucesss = False
self.assertTrue(success, "Lakes cannot have some legal positive longitudes.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('Y')
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class NonEmptyNamesTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "Non-empty Names"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['', 9999, 10, 10, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "A lake can have a name that's the empty string.")
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = [' ', 9999, 10, 10, 5.1, 5.1]
failed = False
#try adding the illegal lake
try:
stu_cursor.execute(command, params)
except:
failed = True
self.assertTrue(failed, "Lakes can have names that are just spaces.")
command = "DELETE FROM lakes WHERE name = ?"
params = (' ',)
stu_cursor.execute(command, params)
command = "INSERT INTO lakes (name, surface_area, volume, max_depth, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?)"
params = ['Y', 9999, 10, 10, 5.1, 5.1]
success = False
#try adding the legal lake
try:
stu_cursor.execute(command, params)
success = True
except:
sucesss = False
self.assertTrue(success, "Lakes cannot have names with regular characters.")
command = "DELETE FROM lakes WHERE name = ?"
params = ('Y',)
stu_cursor.execute(command, params)
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
class AddLakeTests(unittest.TestCase):
def points(self):
return 10
def test_name(self):
return "add_lake"
def test_default_case(self):
stu_connection = sqlite3.connect('lakeland-lakes.db')
try:
stu_cursor = stu_connection.cursor()
params = ['X', 9999, 10, 10, 5.1, 5.1]
main.add_lake(stu_connection, *params)
query = "SELECT * FROM lakes WHERE name = ?"
stu_cursor.execute(query, ['X'])
results = stu_cursor.fetchall()
num_lakes = 1
self.assertTrue(len(results) == num_lakes, "Doesn't add legitimate lakes.")
#try to add two lakes at once
params = ['Y\', 10, 10, 10, 5.1, 5.1), (\'Z', 10, 10, 10, 5.1, 5.1]
main.add_lake(stu_connection, *params)
query = "SELECT * FROM lakes"
stu_cursor.execute(query, [])
results = stu_cursor.fetchall()
num_lakes = [39, 40] #okay to add a weirdly-named one or just crash.
too_many = 41
self.assertTrue(len(results) < too_many, "Allows my illegal SQL inputs to add two lakes at once.")
self.assertTrue(len(results) in num_lakes, "Doesn't wind up with the correct number of lakes.")
stu_connection.close()
except Exception as e:
stu_connection.close()
raise e
test_classes = [
PrimaryKeyTests,
NoNullNamesTests,
NonNegativeSurfaceAreasTests,
PositiveVolumesTests,
PositiveMaxDepthsTests,
LegitimateLatitudesTests,
LegitimateLongitudesTests,
NonEmptyNamesTests,
AddLakeTests
]
#Actually run the tests:
if __name__ == "__main__":
import importlib
file_name = input("What is your team's name?\n")
student_file_prefix = file_name + "_project1"
total_score = 0
total_max = 0
total_output = ""
main = importlib.import_module(student_file_prefix)
for test_class in test_classes:
tests = test_class()
test_points = tests.points()
score = 0
output = ""
try:
tests.test_default_case()
output = "All tests passed!"
score = test_points
except Exception as e:
output += str(e)
total_output = total_output + tests.test_name() + ": " + str(score) + "/" + str(test_points) + "\n " + output + "\n\n"
total_score += score
total_max += test_points
total_score_output = "Total score: " + str(total_score) + "/" + str(total_max)
total_output = total_score_output + "\n\n" + total_output
print("Scored", score, "points!")
print(total_output[:-2])
print(total_score_output)