def test_14_join_multi_tables(self):
q = "select a.val,b.val,c.val from tablea,tableb,tablec"
exp = {
'c.val': [
'100', '200', '100', '200', '100', '200', '100', '200', '100',
'200', '100', '200', '100', '200', '100', '200', '100', '200',
'100', '200', '100', '200', '100', '200', '100', '200', '100',
'200', '100', '200', '100', '200'
],
'b.val': [
'1', '1', '2', '2', '3', '3', '1', '1', '1', '1', '2', '2',
'3', '3', '1', '1', '1', '1', '2', '2', '3', '3', '1', '1',
'1', '1', '2', '2', '3', '3', '1', '1'
],
'a.val': [
'1', '1', '1', '1', '1', '1', '1', '1', '2', '2', '2', '2',
'2', '2', '2', '2', '3', '3', '3', '3', '3', '3', '3', '3',
'1', '1', '1', '1', '1', '1', '1', '1'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joining 3 tables': " + "EXPECTED: " +
str(exp) + " GOT: " + str(res))
def test_11_col_gt_col(self):
q = "select a.a from tablea where a.a>a.val"
exp = {'a.a': ['4']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "selecting: where col1>col2': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_03_multiple_column(self):
q = 'select a.val,a.a from tablea'
exp = {'a.a': ['1', '2', '3', '4'], 'a.val': ['1', '2', '3', '1']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "multiple columns from a single table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_09_col_gt_val(self):
q = "select a.a from tablea where a.val>'1'"
exp = {'a.a': ['2', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "selecting: where col>'val': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_07_two_tables_all_cols(self):
q = 'select * from tablea,tableb'
exp = {
'b.b': [
'1', '2', '3', '3', '1', '2', '3', '3', '1', '2', '3', '3',
'1', '2', '3', '3'
],
'b.str': [
'one', 'two', 'three', 'one', 'one', 'two', 'three', 'one',
'one', 'two', 'three', 'one', 'one', 'two', 'three', 'one'
],
'a.str': [
'one', 'one', 'one', 'one', 'two', 'two', 'two', 'two',
'three', 'three', 'three', 'three', 'one', 'one', 'one', 'one'
],
'b.val': [
'1', '2', '3', '1', '1', '2', '3', '1', '1', '2', '3', '1',
'1', '2', '3', '1'
],
'a.val': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'1', '1', '1', '1'
],
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, all columns: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_01_single_column_unique(self):
q = 'select a.a from tablea'
exp = {'a.a': ['1', '2', '3', '4']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "single column all unique: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_02_single_column_repeats(self):
q = 'select b.b from tableb'
exp = {'b.b': ['1', '2', '3', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "single column with repeats: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_12_join_col_eq_col(self):
q = "select a.val,b.val from tablea,tableb where a.a=b.b"
exp = {'b.val': ['1', '2', '3', '1'], 'a.val': ['1', '2', '3', '3']}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp),
"selecting from joined tables: where col1=col2': " + "EXPECTED: " +
str(exp) + " GOT: " + str(res))
def test_07_read_table_multi_col_multi_rows_diff_col_names(self):
p1 = 'multi_multi_colnames.csv'
r = a3.read_table(p1)
d = {'atest': ['A1', 'A2'], 'btest': ['B1', 'B2']}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(
e,
"table with multiple columns, multiple rows, column names not following letter.string format")
def test_09_cartesian_product_zero_one_rows(self):
t1 = Table()
t1.set_dict({'a': []})
t2 = Table()
t2.set_dict({'e': ['f']})
expected = {'a': [], 'e': []}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"no rows and one row")
def test_03_cartesian_product_two_col2_one_row(self):
t1 = Table()
t1.set_dict({'a': ['b'], 'x': ['y']})
t2 = Table()
t2.set_dict({'c': ['d']})
expected = {'a': ['b'], 'c': ['d'], 'x': ['y']}
res = a3.cartesian_product(t2, t1)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns in second table, one row")
def test_01_cartesian_product_one_col_one_row(self):
t1 = Table()
t1.set_dict({'a': ['b']})
t2 = Table()
t2.set_dict({'c': ['d']})
expected = {'a': ['b'], 'c': ['d']}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"one column one row")
def test_04_star_column(self):
q = 'select * from tablea'
exp = {
'a.str': ['one', 'two', 'three', 'one'],
'a.val': ['1', '2', '3', '1'],
'a.a': ['1', '2', '3', '4']
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "all columns from a single table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_05_two_tables_one_col(self):
q = 'select a.a from tablea,tableb'
exp = {
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, single column: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_15_join_multi_tables_with_selection(self):
q = "select a.val,b.val,c.val from tablea,tableb,tablec where a.a=b.b,a.a>c.c"
exp = {
'c.val': ['100', '100', '200', '100', '200'],
'a.val': ['2', '3', '3', '3', '3'],
'b.val': ['2', '3', '3', '1', '1']
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joining 3 tables with selection': " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_04_cartesian_product_two_col_two_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c'], 'x': ['y', 'z']})
t2 = Table()
t2.set_dict({'d': ['e', 'f'], 'u': ['v', 'w']})
expected = {
'a': ['b', 'c', 'b', 'c'],
'x': ['y', 'z', 'y', 'z'],
'd': ['e', 'e', 'f', 'f'],
'u': ['v', 'v', 'w', 'w']
}
res = a3.cartesian_product(t2, t1)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns two rows")
def test_06_cartesian_product_two_col_three_three_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c', 'q'], 'x': ['y', 'z', 'r']})
t2 = Table()
t2.set_dict({'d': ['e', 'f', 'l'], 'u': ['v', 'w', 'm']})
expected = {
'a': ['b', 'c', 'q', 'b', 'c', 'q', 'b', 'c', 'q'],
'x': ['y', 'z', 'r', 'y', 'z', 'r', 'y', 'z', 'r'],
'd': ['e', 'e', 'e', 'f', 'f', 'f', 'l', 'l', 'l'],
'u': ['v', 'v', 'v', 'w', 'w', 'w', 'm', 'm', 'm']
}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns three rows")
def test_06_two_tables_two_cols(self):
q = 'select a.a,b.b from tablea,tableb'
exp = {
'a.a': [
'1', '1', '1', '1', '2', '2', '2', '2', '3', '3', '3', '3',
'4', '4', '4', '4'
],
'b.b': [
'1', '2', '3', '3', '1', '2', '3', '3', '1', '2', '3', '3',
'1', '2', '3', '3'
]
}
res = a3.run_query(self.dbo, q).get_dict()
self.assertTrue(
equiv_tables(res, exp), "joined tables, one column per table: " +
"EXPECTED: " + str(exp) + " GOT: " + str(res))
def test_07_cartesian_product_one_col_three_six_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c', 'd']})
t2 = Table()
t2.set_dict({'e': ['f', 'g', 'h', 'i', 'j', 'k']})
expected = {
'a': [
'b', 'c', 'd', 'b', 'c', 'd', 'b', 'c', 'd', 'b', 'c', 'd',
'b', 'c', 'd', 'b', 'c', 'd'
],
'e': [
'f', 'f', 'f', 'g', 'g', 'g', 'h', 'h', 'h', 'i', 'i', 'i',
'j', 'j', 'j', 'k', 'k', 'k'
]
}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns three and six rows")
def test_01_read_table_single_col_single_row(self):
p1 = 'single_single.csv'
r = a3.read_table(p1)
d = {'a.test': ['A']}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with one column, one row")
def test_06_read_table_multi_col_no_rows(self):
p1 = 'multi_none.csv'
r = a3.read_table(p1)
d = {'a.test': [], 'b.test': []}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with multiple column, no rows")
def test_05_read_table_single_col_no_rows(self):
p1 = 'single_none.csv'
r = a3.read_table(p1)
d = {'a.test': []}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with one column, no rows")
def test_04_read_table_multi_col_multi_row(self):
p1 = 'multi_multi.csv'
r = a3.read_table(p1)
d = {'a.test': ['A1', 'A2'], 'b.test': ['B1', 'B2']}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with multiple columns, multiple rows")
def test_03_read_table_multi_col_single_row(self):
p1 = 'multi_single.csv'
r = a3.read_table(p1)
d = {'a.test': ['A'], 'b.test': ['B']}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with multiple columns, single row")
def test_02_read_table_single_col_multi_row(self):
p1 = 'single_multi.csv'
r = a3.read_table(p1)
d = {'a.test': ['A', 'B']}
e = equiv.equiv_tables(r.get_dict(), d)
self.assertTrue(e, "table with one column, multiple rows")
