def test_query(self):
res = db.query("select name from users limit 5")
self.assertEqual(len(res), 5)
res = db.query("select name from users limit %s", (100,), many=20)
rows = []
for r in res:
rows.append(r)
self.assertTrue(100, len(rows))
def test_query(self):
res = db.query('select name from users limit 5', key='slave')
self.assertEqual(len(res), 5)
res = db.query('select name from users limit %s', (100,), many=20, key='slave')
rows = []
for r in res:
rows.append(r)
self.assertTrue(10, len(rows))
def rank_for_users(self, leaderboard_id, entry_ids, dense=False):
"""Get the rank for by users"""
sql = self._build_rank_sql(dense)
sql += '\nWHERE lid=%s AND eid IN (' + ', '.join(
[str(_) for _ in entry_ids]) + ')'
results = db.query(sql, (leaderboard_id, ))
return [self._load(data) for data in results]
def rank(self, leaderboard_id, limit=1000, offset=0, dense=False):
to_score, from_rank, to_rank = db.query_one(
'SELECT score, from_dense, to_dense FROM score_buckets WHERE lid=%s AND from_dense<=%s AND %s<=to_dense',
(leaderboard_id, offset + 1, offset + 1))
if to_rank >= limit + offset + 1:
from_score = to_score
else:
from_score = db.query_one(
'SELECT score FROM score_buckets WHERE lid=%s AND from_dense<=%s AND %s<=to_dense',
(leaderboard_id, limit + offset + 1, limit + offset + 1))[0]
sql = 'SELECT * FROM entries WHERE lid=%s AND %s<=score AND score<=%s '
if dense:
sql += 'ORDER BY score DESC, eid ASC'
else:
sql += 'GROUP BY score, eid ORDER BY score DESC'
sql += ' LIMIT %s OFFSET %s'
res = db.query(sql, (leaderboard_id, from_score, to_score, limit,
offset - from_rank + 1))
res = [self._load(data) for data in res]
if res:
if not dense:
entry = self.rank_for_user(leaderboard_id, res[0].entry_id,
dense)
offset = entry.rank
else:
offset += 1
self._rank_entries(res, dense, offset)
return res
def _get_buckets(self, leaderboard_id, from_score, to_score, rank, dense):
res = db.query('SELECT score, COUNT(score) size FROM entries WHERE lid=%s AND %s<score AND score<=%s GROUP BY score ORDER BY score DESC',
(leaderboard_id, from_score, to_score))
buckets = []
for data in res:
buckets.append(ScoreBucket(leaderboard_id, data[0], data[1], dense + 1, dense + data[1], rank + 1))
dense += data[1]
rank += 1
return buckets, rank, dense
def _get_buckets(self, leaderboard_id, from_score, to_score, rank, dense):
res = db.query(
'SELECT score, COUNT(score) size FROM entries WHERE lid=%s AND %s<score AND score<=%s GROUP BY score ORDER BY score DESC',
(leaderboard_id, from_score, to_score))
buckets = []
for data in res:
buckets.append(
ScoreBucket(leaderboard_id, data[0], data[1], dense + 1,
dense + data[1], rank + 1))
dense += data[1]
rank += 1
return buckets, rank, dense
def rank(self, leaderboard_id, limit=1000, offset=0, dense=False):
sql = "SELECT * FROM entries WHERE lid=%s "
if dense:
sql += "ORDER BY score DESC, eid ASC"
else:
sql += "GROUP BY score, eid ORDER BY score DESC"
sql += " LIMIT %s OFFSET %s"
res = db.query(sql, (leaderboard_id, limit, offset))
res = [self._load(data) for data in res]
if res:
if not dense:
entry = self.rank_for_user(leaderboard_id, res[0].entry_id, dense)
offset = entry.rank
else:
offset += 1
self._rank_entries(res, dense, offset)
return res
def rank_at(self, leaderboard_id, rank, dense=False):
if dense:
data = db.query_one('SELECT from_dense, to_dense, score FROM score_buckets WHERE lid=%s AND from_dense <= %s AND %s <= to_dense',
(leaderboard_id, rank, rank))
res = db.query('SELECT * FROM entries WHERE lid=%s AND score=%s ORDER BY score DESC, eid ASC LIMIT 1 OFFSET %s',
(leaderboard_id, data[2], rank - data[0]))
entries = [self._load(data) for data in res]
for entry in entries:
entry.rank = rank
else:
score = None
data = db.query_one('SELECT score FROM score_buckets WHERE lid=%s AND from_dense <= %s AND %s <= to_dense',
(leaderboard_id, rank, rank))
if data:
score = data[0]
entries = self.find_by_score(leaderboard_id, score)
for entry in entries:
entry.rank = rank
return entries
def rank_at(self, leaderboard_id, rank, dense=False):
if dense:
data = db.query_one('SELECT from_dense, from_score, to_score FROM chunk_buckets WHERE lid=%s AND from_dense<=%s AND %s<=to_dense',
(leaderboard_id, rank, rank))
res = db.query('SELECT * FROM entries WHERE lid=%s AND %s<=score AND score<=%s ORDER BY score DESC, eid ASC LIMIT 1 OFFSET %s',
(leaderboard_id, data[1], data[2], rank - data[0]))
entries = [self._load(data) for data in res]
for entry in entries:
entry.rank = rank
else:
data = db.query_one('SELECT from_rank, from_score, to_score FROM chunk_buckets WHERE lid=%s AND from_rank<=%s AND %s<=to_rank',
(leaderboard_id, rank, rank))
if data:
score = db.query_one('SELECT score FROM entries WHERE lid=%s AND %s<=score AND score<=%s ORDER BY score DESC LIMIT 1 OFFSET %s',
(leaderboard_id, data[1], data[2], rank - data[0]))[0]
entries = self.find_by_score(leaderboard_id, score)
for entry in entries:
entry.rank = rank
return entries
def rank(self, leaderboard_id, limit=1000, offset=0, dense=False):
sql = 'SELECT * FROM entries WHERE lid=%s '
if dense:
sql += 'ORDER BY score DESC, eid ASC'
else:
sql += 'GROUP BY score, eid ORDER BY score DESC'
sql += ' LIMIT %s OFFSET %s'
res = db.query(sql, (leaderboard_id, limit, offset))
res = [self._load(data) for data in res]
if res:
if not dense:
entry = self.rank_for_user(leaderboard_id, res[0].entry_id,
dense)
offset = entry.rank
else:
offset += 1
self._rank_entries(res, dense, offset)
return res
def rank(self, leaderboard_id, limit=1000, offset=0, dense=False):
from_score, to_score, from_rank, to_rank = db.query_one('SELECT from_score, to_score, from_rank, to_rank FROM chunk_buckets WHERE lid=%s AND from_rank<=%s AND %s<=to_rank', (leaderboard_id, offset+1, offset+1))
if to_rank < limit + offset + 1:
from_score = db.query_one('SELECT from_score FROM chunk_buckets WHERE lid=%s AND from_rank<=%s AND %s<=to_rank', (leaderboard_id, limit+offset+1, limit+offset+1))[0]
sql = 'SELECT * FROM entries WHERE lid=%s AND %s<=score AND score<=%s '
if dense:
sql += 'ORDER BY score DESC, eid ASC'
else:
sql += 'GROUP BY score, eid ORDER BY score DESC'
sql += ' LIMIT %s OFFSET %s'
res = db.query(sql, (leaderboard_id, from_score, to_score, limit, offset - from_rank+1))
res = [self._load(data) for data in res]
if res:
if not dense:
entry = self.rank_for_user(leaderboard_id, res[0].entry_id, dense)
offset = entry.rank
else:
offset += 1
self._rank_entries(res, dense, offset)
return res
def q(n):
for i in range(10):
res = db.query("select count(*) from users")
self.assertEqual(99, res[0][0])
def test_excute(self):
res = db.execute("insert into users values(%s, %s)", [(10L, "thomas"), (11L, "animer")], key="slave")
res = db.query("SELECT count(*) FROM users WHERE uid>=10", key="slave")
self.assertEqual(2, res[0][0])
def find_by_entry_ids(self, leaderboard_id, entry_ids):
sql = 'SELECT eid, lid, score, data, created FROM entries WHERE lid=%%s AND eid IN (%s)' % (
', '.join([str(_) for _ in entry_ids]))
results = db.query(sql, (leaderboard_id, ))
return [self._load(data) for data in results]
def find_by_score(self, leaderboard_id, score):
results = db.query(
'SELECT eid, lid, score, data, created FROM entries WHERE lid=%s AND score=%s',
(leaderboard_id, score))
return [self._load(data) for data in results]
def test_excute(self):
res = db.execute('insert into users values(%s, %s)', [(10L, 'thomas'), (11L, 'animer')], key='slave')
res = db.query('SELECT count(*) FROM users WHERE uid>=10', key='slave')
self.assertEqual(2, res[0][0])
def find_by_job_id(self, job_id):
if job_id is None:
res = db.query('SELECT * FROM cron WHERE job_id IS NULL')
else:
res = db.query('SELECT * FROM cron WHERE job_id=%s', (job_id,))
return [self._load(data) for data in res]
def rank_for_users(self, leaderboard_id, entry_ids, dense=False):
"""Get the rank for by users"""
sql = self._build_rank_sql(dense)
sql += "\nWHERE lid=%s AND eid IN (" + ", ".join([str(_) for _ in entry_ids]) + ")"
results = db.query(sql, (leaderboard_id,))
return [self._load(data) for data in results]
def find_by_entry_ids(self, leaderboard_id, entry_ids):
sql = "SELECT eid, lid, score, data, created FROM entries WHERE lid=%%s AND eid IN (%s)" % (
", ".join([str(_) for _ in entry_ids])
)
results = db.query(sql, (leaderboard_id,))
return [self._load(data) for data in results]
def find_by_score(self, leaderboard_id, score):
results = db.query(
"SELECT eid, lid, score, data, created FROM entries WHERE lid=%s AND score=%s", (leaderboard_id, score)
)
return [self._load(data) for data in results]
def find(self, name):
res = db.query('SELECT * FROM cron WHERE name=%s', (name,))
if res:
return self._load(res[0])
def q(n):
for i in range(10):
res = db.query('select count(*) from users')
self.assertEqual(99, res[0][0])