def test_in_select(self):
query = query_builder.Query(DummyTableDesc())
inquery = query.in_select('d', 'test_table')
inquery.where('a = %s', 1)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE ('
'd IN'
' (SELECT d'
' FROM test_table'
' WHERE (a = %s)))', [1])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
inquery = query.in_select('d', 'test_table', op='NOT IN', columns=['e'])
inquery.where('b = %s', 2)
query.where('c = %s', 3)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE ('
'a = %s'
' AND d NOT IN'
' (SELECT e'
' FROM test_table'
' WHERE (b = %s))'
' AND c = %s)', [1, 2, 3])
def test_set_op(self):
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
union_query = query.union_query()
union_query.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s)'
' UNION'
' SELECT test_table.*'
' FROM test_table'
' WHERE (b = %s)', [1, 2])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
intersect_query = query.intersect_query()
intersect_query.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s)'
' INTERSECT'
' SELECT test_table.*'
' FROM test_table'
' WHERE (b = %s)', [1, 2])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
except_query = query.except_query()
except_query.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s)'
' EXCEPT'
' SELECT test_table.*'
' FROM test_table'
' WHERE (b = %s)', [1, 2])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
except_query = query.except_query()
except_query.where('c = %s', 3)
query.where('b = %s', 2)
except_query.where('d = %s', 4)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s'
' AND b = %s)'
' EXCEPT'
' SELECT test_table.*'
' FROM test_table'
' WHERE (c = %s'
' AND d = %s)', [1, 2, 3, 4])
def test_where_or(self):
query = query_builder.Query(DummyTableDesc(), conjunction = 'OR')
query.where('a = %s', 1)
query.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s OR b = %s)', [1, 2])
query = query_builder.Query(DummyTableDesc())
sub_expr = query.sub_expr()
sub_expr.where('a = %s', 1)
sub_expr.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE ((a = %s OR b = %s))', [1, 2])
def test_where_pkey(self):
query = query_builder.Query(DummyTableDesc())
query.by_primary_keys([(1, 2), (3, 4), (5, 6)])
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE ((pkey_a,pkey_b) IN ((%s,%s),(%s,%s),(%s,%s)))',
[1, 2, 3, 4, 5, 6])
def test_where_not(self):
query = query_builder.Query(DummyTableDesc(), negate=True)
query.where('a = %s', 1)
query.where('b = %s', 2)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE NOT (a = %s AND b = %s)',
[1, 2])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
notq = query.sub_expr(negate=True, conjunction='AND')
notq.where('b = %s', 2)
notq.where('c = %s', 3)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s AND NOT (b = %s AND c = %s))',
[1, 2, 3])
def test_fetchall(self):
table_desc = DummyTableDesc()
query = query_builder.Query(table_desc)
self._test_exec(table_desc, query.delete, (), {},
'DELETE FROM test_table', (), None)
query.where('a = %s', 1)
self._test_exec(table_desc, query.delete, (), {},
'DELETE FROM test_table WHERE (a = %s)', (1,),
None)
def test_aggregate(self):
table_desc = DummyTableDesc(fetch_result=[10])
query = query_builder.Query(table_desc)
self._test_exec(table_desc, query.aggregate, ('COUNT(*)',), {},
'SELECT COUNT(*) FROM test_table', (), 10)
query.where('a = %s', 1)
self._test_exec(table_desc, query.aggregate, ('COUNT(*)',), {},
'SELECT COUNT(*) FROM test_table WHERE (a = %s)',
(1,), 10)
def test_join(self):
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
query.join('JOIN foo USING (a)')
query.join('LEFT JOIN bah USING (b)')
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' JOIN foo USING (a)'
' LEFT JOIN bah USING (b)'
' WHERE (a = %s)', [1])
def preload(self, keys=()):
keys = set(keys)
if self.pending:
keys.update(self.pending)
keys = keys - set(self)
if keys:
query = query_builder.Query(self.table_desc)
query.where_in(self.primary_key_name, keys)
self.from_result(query.fetchall())
self.pending = None
def test_where_andor(self):
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
sub_expr = query.sub_expr()
sub_expr.where('b = %s', 2)
query.where('d = %s', 4)
sub_expr.where('c = %s', 3)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s AND (b = %s OR c = %s) AND d = %s)',
[1, 2, 3, 4])
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
sub_expr = query.sub_expr()
query.where('d = %s', 4)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s AND d = %s)',
[1, 4])
def test_sub_select_where(self):
query = query_builder.Query(DummyTableDesc())
subquery = query.sub_select()
subquery.where('b = %s', 2)
query.where('a = %s', 1)
self._test(query, 'SELECT test_table.*'
' FROM (SELECT test_table.*'
' FROM test_table'
' WHERE (b = %s))'
' AS test_table'
' WHERE (a = %s)', [2, 1])
def ptset(table_desc, master_col, slave_col, key=None, filter=None):
pt_info = PTInfo(table_desc, master_col, slave_col)
ptset = PTSet(pt_info, key)
if key is not None:
query = query_builder.Query(pt_info.table_desc, order_by=pt_info.pkey)
query.where('%s = %%s' % pt_info.master_col, key)
if filter is not None:
query.where(filter)
for row in query.fetchall():
ptset._add(row)
pt_info.slave_cache.preload()
return ptset
def test_where_in(self):
query = query_builder.Query(DummyTableDesc())
query.where_in('a', [])
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (false)',
[])
query = query_builder.Query(DummyTableDesc())
query.where_in('a', [1])
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a IN (%s))',
[1])
query = query_builder.Query(DummyTableDesc())
query.where_in('a', [1, 2])
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a IN (%s,%s))',
[1,2])
def preload(self, keys):
keys = [
key for key in keys if key is not None and key not in self.sets
]
if keys:
for key in keys:
self.sets[key] = PTSet(self.pt_info, key)
pt_info = self.pt_info
query = query_builder.Query(pt_info.table_desc,
order_by=pt_info.pkey)
query.where_in(pt_info.master_col, keys)
for row in query.fetchall():
key = getattr(row, pt_info.master_col)
self.sets[key]._add(row)
for key in keys:
self.sets[key].save_state()
pt_info.slave_cache.preload()
def preload_all(self):
query = query_builder.Query(self.table_desc)
self.from_result(query.fetchall())
def query(self, **kwargs):
self.query_obj = query_builder.Query(self.table_desc)
def query(self, table, **kwargs):
return query_builder.Query(self.get_table(table), **kwargs)
def test_distinct(self):
query = query_builder.Query(DummyTableDesc(), distinct = True)
self._test(query, 'SELECT DISTINCT test_table.* FROM test_table')
def test_simple(self):
query = query_builder.Query(DummyTableDesc())
self._test(query, 'SELECT test_table.* FROM test_table')
def test_where(self):
query = query_builder.Query(DummyTableDesc())
query.where('a = %s', 1)
self._test(query, 'SELECT test_table.*'
' FROM test_table'
' WHERE (a = %s)', [1])
def test_order_by(self):
query = query_builder.Query(DummyTableDesc(), order_by = 'a')
self._test(query, 'SELECT test_table.* FROM test_table ORDER BY a')
query = query_builder.Query(DummyTableDesc(), order_by = ('a', 'b'))
self._test(query, 'SELECT test_table.* FROM test_table ORDER BY a, b')
def test_for_update(self):
query = query_builder.Query(DummyTableDesc(), for_update = True)
self._test(query, 'SELECT test_table.* FROM test_table FOR UPDATE')
def test_limit(self):
query = query_builder.Query(DummyTableDesc(), limit = 100)
self._test(query, 'SELECT test_table.* FROM test_table LIMIT 100')
def test_keys_only(self):
query = query_builder.Query(DummyTableDesc())
self._test(query, 'SELECT test_table.pkey_a, test_table.pkey_b'
' FROM test_table',
[], columns = ['test_table.pkey_a', 'test_table.pkey_b'])
def test_sub_select(self):
query = query_builder.Query(DummyTableDesc())
subquery = query.sub_select()
self._test(query, 'SELECT test_table.*'
' FROM (SELECT test_table.* FROM test_table)'
' AS test_table')