def test_table_9_fail():
"""
Simple create of table definition. No columns or indexes.
:return:
"""
cleanup()
print_test_separator("Starting test_table_9")
cat = CSVCatalog.CSVCatalog()
try:
t = cat.create_table(
"teams",
"../data/Teams.csv")
print("Teams table", json.dumps(
t.describe_table(), indent = 2))
t.add_column_definition(CSVCatalog.ColumnDefinition("Rank", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("AB", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("teamID", "text"))
t.drop_column_definition("Rank")
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.define_primary_key(["teamID"])
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.define_index("data1", ["AB", "HB"], "INDEX") # should raise an error -403 here since there's no "HB" in column definitions
except Exception as e:
print("Exception e = ", e)
print_test_separator("SUCCESS test_table_9 should fail.")
print_test_separator("Complete test_table_9")
def test_create_table_3_fail():
"""
Creates a table that includes several column definitions. This test should fail because one of the defined
columns is not in the underlying CSV file.
:return:
"""
print_test_separator("Starting test_create_table_3_fail")
cleanup()
cat = CSVCatalog.CSVCatalog('localhost',
3306,
'my_catalog',
'dbuser',
'dbuser',
debug_mode=None)
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
cds.append(CSVCatalog.ColumnDefinition("canary"))
try:
t = cat.create_table("people", "../data/People.csv", cds)
print_test_separator("FAILURE test_create_table_3")
print("People table", t.describe_table())
except Exception as e:
print("Exception e = ", e)
print_test_separator("Complete test_create_table_3_fail successfully")
def my_test_4():
"""
Try add_column_definition if the column already exists. This should fail.
:return:
"""
print_test_separator("Starting my_test_4")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
t = cat.create_table("people", "data/People.csv", cds)
print("People table", json.dumps(t.describe_table(), indent=2))
print("\nTry to add a duplicate column playerID:")
try:
c = CSVCatalog.ColumnDefinition("playerID")
t.add_column_definition(c)
except Exception as e:
print("Exception e = ", e)
print("\n##### Expected result: fail")
print_test_separator("Complete my_test_4")
def test_create_table_3_fail():
"""
Creates a table that includes several column definitions. This test should fail because one of the defined
columns is not in the underlying CSV file.
:return:
"""
print_test_separator("Starting test_create_table_3_fail")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
cds.append(CSVCatalog.ColumnDefinition("canary"))
try:
t = cat.create_table(
"people",
"/Users/donaldferguson/Dropbox/ColumbiaCourse/Courses/Fall2018/W4111-Projects/CSVDB/Data/core/People.csv",
cds)
print_test_separator("FAILURE test_create_table_3")
print("People table", json.dumps(t.describe_table(), indent=2))
except Exception as e:
print("Exception e = ", e)
print_test_separator("Complete test_create_table_3_fail successfully")
def test_to_json():
try:
print("\n*********** Testing to JSON. *******************\n")
cat = CSVCatalog.CSVCatalog()
cat.drop_table("teams")
cds = []
cds.append(CSVCatalog.ColumnDefinition('teamID', 'text', True))
cds.append(CSVCatalog.ColumnDefinition('yearID', 'text', True))
cds.append(CSVCatalog.ColumnDefinition('W', column_type='number'))
tbl = CSVCatalog.TableDefinition(
"teams", "data/Teams.csv", column_definitions=cds,
cnx=cat.cnx) # maybe this is inproper?
r = json.dumps(tbl.to_json(), indent=2)
print("Teams definition = \n", r)
with open("unit_tests_catalog_json.txt", "w") as result_file:
result_file.write(r)
print("\n\n")
except Exception as e:
print(
"My implementation throws a custom exception. You can print any meaningful error you want."
)
print("Could not create table. Exception = ", e)
print("\n*********** Testing to JSON. *******************\n")
def test_table_10_fail():
"""
Simple create of table definition. No columns or indexes.
:return:
"""
cleanup()
print_test_separator("Starting test_table_10")
cat = CSVCatalog.CSVCatalog()
try:
t = cat.create_table(
"teams",
"../data/Teams.csv")
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.add_column_definition(CSVCatalog.ColumnDefinition("Rank", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("AB", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("CG", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("teamID", "text", not_null=True))
t.drop_column_definition("Rank")
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.define_primary_key(["teamID"])
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.define_index("data1", ["AB"], "INDEX")
t.define_index("data2", ["CG"])
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.drop_column_definition("AB")
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.drop_index("data1") # should raise an error -403 here: because we dropped "AB", the "data1" index should disappear
except Exception as e:
print("Exception e = ", e)
print_test_separator("SUCCESS test_table_10 should fail.")
print_test_separator("Complete test_table_10")
def test_index_def_to_json():
print("\n*********** Testing index def to JSON. *******************\n")
cds = []
cds.append(CSVCatalog.ColumnDefinition('teamID', 'text', True))
cds.append(CSVCatalog.ColumnDefinition('yearID', 'text', True))
ind = CSVCatalog.IndexDefinition('INDEX_teamID_yearID', cds, "PRIMARY");
print(ind.to_json());
def test():
b = []
#a = CSVCatalog.ColumnDefinition("playerID", "text", True)
b.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
b.append(CSVCatalog.ColumnDefinition("playerID", "number", True))
#CSVCatalog.ColumnDefinition("playerID", "text", True).to_json()
#print(type(b[0]))
return b
def header():
people = CSVCatalog.get_header(
'/Users/apple/Desktop/HW3/src/Data/People.csv')
batting = CSVCatalog.get_header(
'/Users/apple/Desktop/HW3/src/Data/Batting.csv')
Teams = CSVCatalog.get_header(
'/Users/apple/Desktop/HW3/src/Data/Teams.csv')
print(people)
print(batting)
print(Teams)
def test_table_def_to_json():
print("\n*********** Testing table def to JSON. *******************\n")
cds = list()
cds.append(CSVCatalog.ColumnDefinition('teamID', 'text', True))
cds.append(CSVCatalog.ColumnDefinition('yearID', 'text', True))
cds.append(CSVCatalog.ColumnDefinition('AB', 'number'))
tbl = CSVCatalog.TableDefinition(
"batting",
"../data/Batting.csv",
column_definitions=cds)
print(tbl.to_json())
def test_drop_index():
print_test_separator("Starting test_drop_index_5")
# DO NOT CALL CLEANUP. Want to access preexisting table.
cat = CSVCatalog.CSVCatalog()
t = cat.get_table("batting")
print("Initial status of table = \n",
json.dumps(t.describe_table(), indent=2))
t.drop_column_(CSVCatalog.ColumnDefinition("G", "number"))
print("Modified status of table = \n",
json.dumps(t.describe_table(), indent=2))
print_test_separator("Success test_drop_index_5")
def test_create_table_5():
"""
Modifies a preexisting/precreated table definition.
:return:
"""
print_test_separator("Starting test_create_table_5")
# DO NOT CALL CLEANUP. Want to access preexisting table.
cat = CSVCatalog.CSVCatalog()
t = cat.get_table("batting")
print("Initial status of table = \n", json.dumps(t.describe_table(), indent=2))
t.add_column_definition(CSVCatalog.ColumnDefinition("HR", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("G", "number"))
t.define_index("team_year_idx", ['teamID', 'yearID'], "INDEX")
print("Modified status of table = \n", json.dumps(t.describe_table(), indent=2))
print_test_separator("Success test_create_table_5")
def test3():
print_test_separator("Starting test_create_table_1")
cat = CSVCatalog.CSVCatalog()
t = cat.create_table(
"people",
"/Users/Jinyang_Li/Dropbox/ColumbiaCourse/Courses/Fall2018/W4111/Data/People.csv"
)
def testt():
s = []
a = CSVCatalog.get_header_people()
print(a)
f = a.replace("bbrefID\n", "bbrefID")
f = f.split(",")
print(f)
def test_create_table_1():
cleanup()
print_test_separator("Starting test_create_table_1")
cat = CSVCatalog.CSVCatalog()
t = cat.create_table("people",
"/Users/apple/Desktop/HW3/src/Data/People.csv")
print("People table", json.dumps(t.describe_table()))
print_test_separator("Complete test_create_table_1")
def __load_info__(self):
"""
Loads metadata from catalog and sets __description__ to hold the information.
:return:
"""
cat = CSVCatalog.CSVCatalog()
t = cat.get_table(self.__table_name__)
self.__description__ = t.describe_table()
def test_create_table_3():
"""
Creates a table that includes several column definitions.
:return:
"""
print_test_separator("Starting test_create_table_3")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
t = cat.create_table("people", "../data/People.csv", cds)
print("People table", json.dumps(t.describe_table(), indent=2))
print_test_separator("Complete test_create_table_3")
def test_find_by_template(optimize=False):
cleanup()
print_test_separator("Starting test_optimizable_2, optimize = " + str(optimize))
cat = CSVCatalog.CSVCatalog('localhost', 3306, db_name, user_name, pwd, debug_mode=None)
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
cds.append(CSVCatalog.ColumnDefinition("birthCity", "text"))
cds.append(CSVCatalog.ColumnDefinition("birthCountry", "text"))
cds.append(CSVCatalog.ColumnDefinition("throws", column_type="text"))
t = cat.create_table(
"people",
data_dir + "People.csv",
cds)
print("People table metadata = \n", t.describe_table())
t.define_index("nameLast_idx", ['nameLast'], "INDEX")
people_tbl = CSVTable.CSVTable("people")
print("Loaded people table = \n", people_tbl)
tmp = {"nameLast": "Williams"}
result = people_tbl.find_by_template(tmp);
print("result = \n", len(result));
def test_create_table_4_fail():
"""
Creates a table that includes several column definitions and a primary key.
The primary key references an undefined column, which is an error.
NOTE: You should check for other errors. You do not need to check in the CSV file for uniqueness but
should test other possible failures.
:return:
"""
print_test_separator("Starting test_create_table_4_fail")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("teamID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("yearID", column_type="text", not_null=True))
cds.append(CSVCatalog.ColumnDefinition("stint", column_type="number", not_null=True))
cds.append(CSVCatalog.ColumnDefinition("H", column_type="number", not_null=False))
cds.append(CSVCatalog.ColumnDefinition("AB", column_type="number", not_null=False))
t = cat.create_table("batting",
"/Users/Yan/Desktop/hw3/data/Batting.csv",
cds)
try:
t.define_primary_key(['playerID', 'teamID', 'yearID', 'HR'])
print("Batting table", json.dumps(t.describe_table(), indent=2))
print_test_separator("FAILURES test_create_table_4_fail")
except Exception as e:
print("Exception e = ", e)
print_test_separator("SUCCESS test_create_table_4_fail should fail.")
def test_create_table_4():
"""
Creates a table that includes several column definitions.
:return:
"""
print_test_separator("Starting test_create_table_4")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("teamID", "text", True))
cds.append(
CSVCatalog.ColumnDefinition("yearID",
column_type="text",
not_null=True))
cds.append(
CSVCatalog.ColumnDefinition("stint",
column_type="number",
not_null=True))
cds.append(
CSVCatalog.ColumnDefinition("H", column_type="number", not_null=False))
cds.append(
CSVCatalog.ColumnDefinition("AB", column_type="number",
not_null=False))
t = cat.create_table(
"batting",
"/Users/donaldferguson/Dropbox/ColumbiaCourse/Courses/Fall2018/W4111-Projects/CSVDB/Data/core/Batting.csv",
cds)
t.define_primary_key(['playerID', 'teamID', 'yearID', 'stint'])
print("People table", json.dumps(t.describe_table(), indent=2))
print_test_separator("Complete test_create_table_4")
def test_find_by_template_index_batting():
print_test_separator("Starting test_find_by_template_index_batting")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("teamID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("yearID", column_type="text", not_null=True))
cds.append(CSVCatalog.ColumnDefinition("stint", column_type="number", not_null=True))
cds.append(CSVCatalog.ColumnDefinition("H", column_type="number", not_null=False))
cds.append(CSVCatalog.ColumnDefinition("AB", column_type="number", not_null=False))
t = cat.create_table("batting",
'/Users/apple/Desktop/HW3/src/Data/Batting.csv',
cds)
t.define_index("team_year_idx", ['teamID', 'yearID'], "INDEX")
batting_tbl = CSVTable.CSVTable("batting")
start_time = time.time()
tmp = {'playerID':'willite01','yearID':'1939','stint': '1','teamID':'BOS'}
fields = ['playerID','yearID','stint','teamID']
result = batting_tbl.find_by_template(tmp,fields)
print("Result = \n",json.dumps(result,indent=2))
print("table:\n")
print_out(result)
end_time = time.time()
elapsed_time = end_time - start_time
print("\nElapsed time to execute queries = ",elapsed_time)
print_test_separator("Starting test_find_by_template_index_batting")
def test_create_table_5_prep():
"""
Creates a table that includes several column definitions and a primary key.
:return:
"""
print_test_separator("Starting test_create_table_5_prep")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("teamID", "text", True))
cds.append(
CSVCatalog.ColumnDefinition("yearID",
column_type="text",
not_null=True))
cds.append(
CSVCatalog.ColumnDefinition("stint",
column_type="number",
not_null=True))
cds.append(
CSVCatalog.ColumnDefinition("H", column_type="number", not_null=False))
cds.append(
CSVCatalog.ColumnDefinition("AB", column_type="number",
not_null=False))
t = cat.create_table("batting", "../data/Batting.csv", cds)
t.define_primary_key(['playerID', 'teamID', 'yearID', 'stint'])
print("Batting table", json.dumps(t.describe_table(), indent=2))
print_test_separator("Completed test_create_table_5_prep")
def cleanup():
"""
Deletes previously created information to enable re-running tests.
:return: None
"""
cat = CSVCatalog.CSVCatalog()
cat.drop_table("people")
cat.drop_table("batting")
cat.drop_table("teams")
def test_create_table_1():
"""
Simple create of table definition. No columns or indexes.
:return:
"""
cat = CSVCatalog.CSVCatalog()
t = cat.create_table(
"people",
"../data/People.csv")
def test4_drop():
cat = CSVCatalog.CSVCatalog()
t = cat.get_table("people")
#print("t: ",t.describe_table())
print("Initial status of table = \n",
json.dumps(t.describe_table_test(), indent=2))
t = cat.drop_table('people')
print("Initial status of table = \n",
json.dumps(t.describe_table_test(), indent=2))
def cleanup():
"""
Deletes previously created information to enable re-running tests.
:return: None
"""
cat = CSVCatalog.CSVCatalog('localhost', 3306, db_name, user_name, pwd, debug_mode=None)
cat.drop_table("people")
cat.drop_table("batting")
cat.drop_table("teams")
def test_catalog_create():
catalog = CSVCatalog.CSVCatalog('localhost', 3306, 'my_catalog', 'dbuser', 'dbuser', debug_mode=None)
catalog.drop_table("People")
player = CSVCatalog.ColumnDefinition('playerID', 'text', True);
name_first = CSVCatalog.ColumnDefinition('nameFirst', 'text', True);
birth_year = CSVCatalog.ColumnDefinition('birthYear', 'number');
name_last = CSVCatalog.ColumnDefinition('nameLast', 'text', True)
columns = list()
columns.append(player)
columns.append(name_first)
columns.append(birth_year)
print("\n*********** Testing catalog create/drop table. *******************\n")
re = catalog.create_table("People", "../data/People.csv", columns, None)
print(re.to_json())
# catalog.drop_table("PeopleSmall")
print("\n*********** Testing catalog table. *******************\n")
table = catalog.get_table("People");
print(table.to_json())
print("\n*********** Testing catalog add column. *******************\n")
table.add_column_definition(name_last)
print(table.to_json())
print("\n*********** Testing catalog drop column. *******************\n")
table.drop_column_definition("birthYear")
print(table.to_json())
print("\n*********** Testing catalog add primary key. *******************\n")
table.define_primary_key(['playerID']);
print(table.to_json())
print("\n*********** Testing catalog add index. *******************\n")
table.define_index("People_nameFirst_nameLast_index", ["nameFirst","nameLast"], "INDEX");
print(table.to_json())
print("\n*********** Testing catalog drop index. *******************\n")
table.drop_index("People_nameFirst_nameLast_index");
print(table.to_json());
def test_create_table_1():
"""
Simple create of table definition. No columns or indexes.
:return:
"""
cleanup()
print_test_separator("Starting test_create_table_1")
cat = CSVCatalog.CSVCatalog()
t = cat.create_table("people", "../Data/People.csv")
print("People table", json.dumps(t.describe_table(), indent=2))
print_test_separator("Complete test_create_table_1")
def test_table_7_fail():
"""
Simple create of table definition. No columns or indexes.
:return:
"""
cleanup()
print_test_separator("Starting test_table_7")
cat = CSVCatalog.CSVCatalog()
try:
t = cat.create_table(
"teams",
"../data/Teams.csv")
print("Teams table", json.dumps(t.describe_table(), indent = 2))
t.add_column_definition(CSVCatalog.ColumnDefinition("Rank", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("AB", "number"))
t.add_column_definition(CSVCatalog.ColumnDefinition("playerID", "text")) # should raise an error -100 here since there's no "playerID" in "Teams"
except Exception as e:
print("Exception e = ", e)
print_test_separator("SUCCESS test_table_7 should fail.")
print_test_separator("Complete test_table_7")
def test_create_table_3():
"""
Creates a table that includes several column definitions.
:return:
"""
print_test_separator("Starting test_create_table_3")
cleanup()
cat = CSVCatalog.CSVCatalog()
cds = []
cds.append(CSVCatalog.ColumnDefinition("playerID", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameLast", "text", True))
cds.append(CSVCatalog.ColumnDefinition("nameFirst", column_type="text"))
t = cat.create_table(
"people",
"/Users/donaldferguson/Dropbox/ColumbiaCourse/Courses/Fall2018/W4111-Projects/CSVDB/Data/core/People.csv",
cds)
print("People table", json.dumps(t.describe_table(), indent=2))
print_test_separator("Complete test_create_table_3")
