def test_no_where_clause(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table', group_by='col_a')
self.assertEqual(
sql, 'SELECT col_a, MIN(col_b) FROM my_table GROUP BY col_a')
self.assertEqual(bind_vars, {})
def select_by_ids(class_,
cursor,
where_column_value_pairs,
columns_list=None,
order_by=None,
group_by=None,
limit=None,
**kwargs):
"""This method is used to perform in-clause queries.
Such queries can cause vtgate to scatter over multiple shards.
This uses execute_entity_ids method of vtgate cursor and the entity
column and the associated entity_keyspace_id_map is computed based
on the routing used - sharding_key or entity_id_map.
"""
if class_.columns_list is None:
raise dbexceptions.ProgrammingError(
"DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(
columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit,
**kwargs)
entity_col_name = None
entity_id_keyspace_id_map = {}
if cursor.routing.sharding_key is not None:
# If the in-clause is based on sharding key
entity_col_name = class_.sharding_key_column_name
if db_object._is_iterable_container(cursor.routing.sharding_key):
for sk in list(cursor.routing.sharding_key):
entity_id_keyspace_id_map[sk] = pack_keyspace_id(
class_.sharding_key_to_keyspace_id(sk))
else:
sk = cursor.routing.sharding_key
entity_id_keyspace_id_map[sk] = pack_keyspace_id(
class_.sharding_key_to_keyspace_id(sk))
elif cursor.routing.entity_id_sharding_key_map is not None:
# If the in-clause is based on entity column
entity_col_name = cursor.routing.entity_column_name
for en_id, sk in cursor.routing.entity_id_sharding_key_map.iteritems(
):
entity_id_keyspace_id_map[en_id] = pack_keyspace_id(
class_.sharding_key_to_keyspace_id(sk))
else:
dbexceptions.ProgrammingError("Invalid routing method used.")
# cursor.routing.entity_column_name is set while creating shard routing.
rowcount = cursor.execute_entity_ids(query, bind_vars,
entity_id_keyspace_id_map,
entity_col_name)
rows = cursor.fetchall()
return [sql_builder.DBRow(columns_list, row) for row in rows]
def select_by_columns(class_,
cursor,
where_column_value_pairs,
columns_list=None,
order_by=None,
group_by=None,
limit=None,
**kwargs):
if class_.columns_list is None:
raise dbexceptions.ProgrammingError(
"DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(
columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit,
**kwargs)
rowcount = cursor.execute(query, bind_vars)
rows = cursor.fetchall()
return [sql_builder.DBRow(columns_list, row) for row in rows]
def test_no_where_clause(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table', group_by='col_a')
self.assertEqual(
sql, 'SELECT col_a, MIN(col_b) FROM my_table GROUP BY col_a')
self.assertEqual(bind_vars, {})
def select_by_ids(class_, cursor, where_column_value_pairs,
columns_list = None,order_by=None, group_by=None,
limit=None, **kwargs):
"""This method is used to perform in-clause queries.
Such queries can cause vtgate to scatter over multiple shards.
This uses execute_entity_ids method of vtgate cursor and the entity
column and the associated entity_keyspace_id_map is computed based
on the routing used - sharding_key or entity_id_map.
"""
if class_.columns_list is None:
raise dbexceptions.ProgrammingError("DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit,
**kwargs)
entity_col_name = None
entity_id_keyspace_id_map = {}
if cursor.routing.sharding_key is not None:
# If the in-clause is based on sharding key
entity_col_name = class_.sharding_key_column_name
if db_object._is_iterable_container(cursor.routing.sharding_key):
for sk in list(cursor.routing.sharding_key):
entity_id_keyspace_id_map[sk] = pack_keyspace_id(class_.sharding_key_to_keyspace_id(sk))
else:
sk = cursor.routing.sharding_key
entity_id_keyspace_id_map[sk] = pack_keyspace_id(class_.sharding_key_to_keyspace_id(sk))
elif cursor.routing.entity_id_sharding_key_map is not None:
# If the in-clause is based on entity column
entity_col_name = cursor.routing.entity_column_name
for en_id, sk in cursor.routing.entity_id_sharding_key_map.iteritems():
entity_id_keyspace_id_map[en_id] = pack_keyspace_id(class_.sharding_key_to_keyspace_id(sk))
else:
dbexceptions.ProgrammingError("Invalid routing method used.")
# cursor.routing.entity_column_name is set while creating shard routing.
rowcount = cursor.execute_entity_ids(query, bind_vars,
entity_id_keyspace_id_map,
entity_col_name)
rows = cursor.fetchall()
return [sql_builder.DBRow(columns_list, row) for row in rows]
def create_select_query(class_, where_column_value_pairs, columns_list=None,
order_by=None, group_by=None, limit=None):
if class_.columns_list is None:
raise dbexceptions.ProgrammingError("DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit)
return query, bind_vars
def test_simple(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table',
column_value_pairs=[('col_a', [1, 2, 3])],
order_by='col_b ASC', group_by='col_a', limit=10)
self.assertEqual(
sql,
'SELECT col_a, MIN(col_b) FROM my_table '
'WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) '
'GROUP BY col_a '
'ORDER BY col_b ASC LIMIT %(limit_row_count)s')
self.assertEqual(
bind_vars,
dict(col_a_1=1, col_a_2=2, col_a_3=3, limit_row_count=10))
def test_simple(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table',
column_value_pairs=[('col_a', [1, 2, 3])],
order_by='col_b ASC', group_by='col_a', limit=10)
self.assertEqual(
sql,
'SELECT col_a, MIN(col_b) FROM my_table '
'WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) '
'GROUP BY col_a '
'ORDER BY col_b ASC LIMIT %(limit_row_count)s')
self.assertEqual(
bind_vars,
dict(col_a_1=1, col_a_2=2, col_a_3=3, limit_row_count=10))
def create_select_query(class_, where_column_value_pairs, columns_list=None,
order_by=None, group_by=None, limit=None):
if class_.columns_list is None:
raise dbexceptions.ProgrammingError("DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit)
return query, bind_vars
def select_by_columns_streaming(class_, cursor, where_column_value_pairs,
columns_list = None,order_by=None, group_by=None,
limit=None, fetch_size=100, **kwargs):
if class_.columns_list is None:
raise dbexceptions.ProgrammingError("DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit,
**kwargs)
return class_._stream_fetch(cursor, query, bind_vars, fetch_size)
def test_simple(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=["col_a", sql_builder.Min("col_b")],
table_name="my_table",
column_value_pairs=[("col_a", [1, 2, 3])],
order_by="col_b ASC",
group_by="col_a",
limit=10,
)
self.assertEqual(
sql,
"SELECT col_a, MIN(col_b) FROM my_table "
"WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) "
"GROUP BY col_a "
"ORDER BY col_b ASC LIMIT %(limit_row_count)s",
)
self.assertEqual(bind_vars, dict(col_a_1=1, col_a_2=2, col_a_3=3, limit_row_count=10))
def select_by_columns(class_, cursor, where_column_value_pairs,
columns_list = None,order_by=None, group_by=None,
limit=None, **kwargs):
if class_.columns_list is None:
raise dbexceptions.ProgrammingError("DB class should define columns_list")
if columns_list is None:
columns_list = class_.columns_list
query, bind_vars = sql_builder.select_by_columns_query(columns_list,
class_.table_name,
where_column_value_pairs,
order_by=order_by,
group_by=group_by,
limit=limit,
**kwargs)
rowcount = cursor.execute(query, bind_vars)
rows = cursor.fetchall()
return [sql_builder.DBRow(columns_list, row) for row in rows]
def test_vt_routing(self):
key_range = "80-C0"
routing_sql, routing_bind_vars = vtrouting._create_where_clause_for_keyrange(key_range)
vt_routing_info = vtrouting.VTRoutingInfo(key_range, routing_sql, routing_bind_vars)
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=["col_a", sql_builder.Min("col_b")],
table_name="my_table",
column_value_pairs=[("col_a", [1, 2, 3])],
vt_routing_info=vt_routing_info,
)
self.assertEqual(
sql,
"SELECT col_a, MIN(col_b) FROM my_table "
"WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) "
"AND keyspace_id >= %(keyspace_id0)s "
"AND keyspace_id < %(keyspace_id1)s",
)
self.assertEqual(
bind_vars, dict(col_a_1=1, col_a_2=2, col_a_3=3, keyspace_id0=(0x80 << 56), keyspace_id1=(0xC0 << 56))
)
def test_vt_routing(self):
key_range = '80-C0'
routing_sql, routing_bind_vars = (
vtrouting._create_where_clause_for_keyrange(key_range))
vt_routing_info = vtrouting.VTRoutingInfo(
key_range, routing_sql, routing_bind_vars)
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table',
column_value_pairs=[('col_a', [1, 2, 3])],
vt_routing_info=vt_routing_info)
self.assertEqual(
sql,
'SELECT col_a, MIN(col_b) FROM my_table '
'WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) '
'AND keyspace_id >= %(keyspace_id0)s '
'AND keyspace_id < %(keyspace_id1)s')
self.assertEqual(
bind_vars,
dict(col_a_1=1, col_a_2=2, col_a_3=3,
keyspace_id0=(0x80 << 56), keyspace_id1=(0xC0 << 56)))
def test_vt_routing(self):
key_range = '80-C0'
routing_sql, routing_bind_vars = (
vtrouting._create_where_clause_for_keyrange(key_range))
vt_routing_info = vtrouting.VTRoutingInfo(
key_range, routing_sql, routing_bind_vars)
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=['col_a', sql_builder.Min('col_b')],
table_name='my_table',
column_value_pairs=[('col_a', [1, 2, 3])],
vt_routing_info=vt_routing_info)
self.assertEqual(
sql,
'SELECT col_a, MIN(col_b) FROM my_table '
'WHERE col_a IN (%(col_a_1)s, %(col_a_2)s, %(col_a_3)s) '
'AND keyspace_id >= %(keyspace_id0)s '
'AND keyspace_id < %(keyspace_id1)s')
self.assertEqual(
bind_vars,
dict(col_a_1=1, col_a_2=2, col_a_3=3,
keyspace_id0=(0x80 << 56), keyspace_id1=(0xC0 << 56)))
def test_no_where_clause(self):
sql, bind_vars = sql_builder.select_by_columns_query(
select_column_list=["col_a", sql_builder.Min("col_b")], table_name="my_table", group_by="col_a"
)
self.assertEqual(sql, "SELECT col_a, MIN(col_b) FROM my_table GROUP BY col_a")
self.assertEqual(bind_vars, {})