def test_xxx(self): rows = list(go(""" SELECT * FROM OneToTen WHERE `OneToTen`.val = `OneToTen`.val OR `OneToTen`.val = '0' OR `OneToTen`.val = '0' """)) self.assertEqual(len(rows), 10)
def test_false(self): rows = list(go(""" SELECT * FROM TableC WHERE false """)) self.assertEqual(len(rows), 0)
def test_and_false(self): rows = list(go(""" SELECT * FROM TableC WHERE `TableC`.val = `TableC`.val AND false """)) self.assertEqual(len(rows), 0)
def test_offset(self): rows = list(go(""" SELECT * FROM OneToFive as a LIMIT 3, 2 """)) self.assertEqual(rows, [[4], [5]])
def test_integer_column_coerces_string_into_integer(self): rows = list(go(""" SELECT * FROM `OneToTen` WHERE `OneToTen`.val = '1' """)) self.assertEqual(rows, [[1]])
def test_limit(self): rows = list(go(""" SELECT * FROM OneToFive as a LIMIT 3 """)) self.assertEqual(rows, [[1], [2], [3]])
def test_left_outer_join(self): rows = list(go(""" SELECT * FROM OneToFive as a LEFT OUTER JOIN OneToTen as b on a.val = b.val """)) self.assertEqual(rows, [[1, 1], [2, 2], [3, 3], [4, 4], [5, 5]])
def test_table_name_as_alias(self): rows = list(go(""" SELECT * FROM OneToTen as a WHERE OneToTen.val = 5 """)) self.assertEqual(len(rows), 1) self.assertEqual(rows[0][0], 5)
def test_list_all_items(self): rows = list(go(u"SELECT * FROM OneToFive")) self.assertEqual(len(rows), 5) self.assertEqual(rows[0][0], 1) self.assertEqual(rows[1][0], 2) self.assertEqual(rows[2][0], 3) self.assertEqual(rows[3][0], 4) self.assertEqual(rows[4][0], 5)
def test_select_column(self): rows = list(go(u"SELECT val FROM OneToFive")) self.assertEqual(len(rows), 5) self.assertEqual(len(rows[0]), 1) self.assertEqual(rows[0][0], 1) self.assertEqual(rows[1][0], 2) self.assertEqual(rows[2][0], 3) self.assertEqual(rows[3][0], 4) self.assertEqual(rows[4][0], 5)
def test_gt_and_lt(self): rows = list(go(""" SELECT * FROM OneToTen WHERE val < 9 and 5 < val """)) self.assertEqual(len(rows), 3) self.assertEqual(rows[0][0], 6) self.assertEqual(rows[1][0], 7) self.assertEqual(rows[2][0], 8)
def test_distinct_on_short_val(self): rows = list(go(""" SELECT distinct val FROM TableC """)) self.assertEqual(len(rows), 4) self.assertEqual(rows[0], ['A', ]) self.assertEqual(rows[1], ['B', ]) self.assertEqual(rows[2], ['C', ]) self.assertEqual(rows[3], ['D', ])
def test_and(self): rows = list(go(""" SELECT * FROM OneToTen WHERE val > 4 AND val < 6 """)) self.assertEqual(len(rows), 1) self.assertEqual(rows[0][0], 5)
def test_or(self): rows = list(go(""" SELECT * FROM OneToTen WHERE val = 5 OR val = 6 """)) self.assertEqual(len(rows), 2) self.assertEqual(rows[0][0], 5) self.assertEqual(rows[1][0], 6)
def test_nested_or(self): rows = list(go(""" SELECT * FROM OneToTen WHERE (val = 5 OR val = 6) OR val = 1 """)) self.assertEqual(len(rows), 3) self.assertEqual(rows[0][0], 1) self.assertEqual(rows[1][0], 5) self.assertEqual(rows[2][0], 6)
def test_or_false(self): rows = list(go(""" SELECT * FROM TableC WHERE `TableC`.val = `TableC`.val OR false """)) self.assertEqual(len(rows), 5) self.assertEqual(rows[0], ['A', ]) self.assertEqual(rows[1], ['A', ]) self.assertEqual(rows[2], ['B', ]) self.assertEqual(rows[3], ['C', ]) self.assertEqual(rows[4], ['D', ])
def test_right_outer_join(self): rows = list(go(""" SELECT * FROM OneToFive as a RIGHT OUTER JOIN OneToTen as b on a.val = b.val """)) self.assertEqual(rows, [ [1, 1], [2, 2], [3, 3], [4, 4], [5, 5], [None, 6], [None, 7], [None, 8], [None, 9], [None, 10] ])
def test_column_equal_column(self): rows = list(go(""" SELECT * FROM OneToTen WHERE `OneToTen`.val = `OneToTen`.val """)) self.assertEqual(len(rows), 10) self.assertEqual(rows[0][0], 1) self.assertEqual(rows[1][0], 2) self.assertEqual(rows[2][0], 3) self.assertEqual(rows[3][0], 4) self.assertEqual(rows[4][0], 5) self.assertEqual(rows[5][0], 6) self.assertEqual(rows[6][0], 7) self.assertEqual(rows[7][0], 8) self.assertEqual(rows[8][0], 9) self.assertEqual(rows[9][0], 10)
def test_where(self, query): self.maxDiff = None # Fetch SQLite results sqllite_rows = list(sqlhose.conn.execute(query)) # Convert to query SQLhild format query = str(query).replace('"', '`') # Fetch SQLhild results rows = list(go(query)) rows = [tuple(row) for row in rows] # Compare # print('sqlhild:', rows) # print('sqllite:', sqllite_rows) # TODO: consider if sorting is correct # it probably is correct unless there's an ORDER BY try: self.assertEqual(sorted(rows), sorted(sqllite_rows)) except: print(query) raise
def test_quoted_table_name(self): rows = list(go(u'SELECT * FROM `OneToFive` WHERE 2 < val')) self.assertEqual(len(rows), 3) self.assertEqual(rows[0][0], 3) self.assertEqual(rows[1][0], 4) self.assertEqual(rows[2][0], 5)
def test_select_nonexisting_table(self): with self.assertRaises(TableDoesNotExist): list(go(u"SELECT * FROM NonExistingTable"))
def test_lt2(self): rows = list(go(u"SELECT * FROM OneToFive WHERE val < 3")) self.assertEqual(len(rows), 2) self.assertEqual(rows[0][0], 1) self.assertEqual(rows[1][0], 2)
def test_gt(self): rows = list(go(u"SELECT * FROM OneToFive WHERE 3 < val")) self.assertEqual(len(rows), 2) self.assertEqual(rows[0][0], 4) self.assertEqual(rows[1][0], 5)
def test_select_nonexisting_column_gives_error(self): with self.assertRaises(UnknownColumn): list(go(u"SELECT nonexisting FROM OneToFive"))