def test_select_join(self):
"""Test the DBSelects' *_join mehtods"""
self._helper.query('CREATE TABLE a (x INTEGER)')
self._helper.query('CREATE TABLE b (y INTEGER)')
self._helper.insert('a', ({'x': 1}, {'x': 2}, {'x': 3}))
self._helper.insert('b', ({'y': 2}, {'y': 3}, {'y': 4}))
sql = DBSelect('a', {'n': 'COUNT(*)'}).inner_join('b', 'a.x = b.y', ())
self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 2)
sql = DBSelect('a', {'n': 'COUNT(*)'}).left_join('b', 'a.x = b.y', ())
self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
def test_select_from(self):
"""Test the SELECT <columns> FROM part of DBSelect"""
select = DBSelect('a')
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(sql, 'SELECT "a".* FROM "a"')
select.set_from('b', ())
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(sql, 'SELECT "a".* FROM "b" INNER JOIN "a"')
select.set_from('c', ('d', 'e'))
sql = re.sub(r'\s+', ' ', select.render())
self.assertEqual(
sql,
'SELECT "a".*, "c"."d", "c"."e" ' +
'FROM "c" INNER JOIN "b" INNER JOIN "a"'
)
count = self._helper.query(
DBSelect('test_table', {'count': 'COUNT(*)'}).render()
).fetchone()
self.assertTrue(type(count['count']) == int)
self.assertTrue(count['count'] >= 0)