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")