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 rank_for_user(self, leaderboard_id, entry_id, dense=False):
entry = self.find(leaderboard_id, entry_id)
if entry:
if dense:
data = db.query_one('SELECT from_dense FROM score_buckets WHERE lid=%s AND score=%s', (leaderboard_id, entry.score))
from_rank = data[0]
rank = db.query_one('SELECT COUNT(eid) as rank FROM entries WHERE lid=%s AND eid<%s AND score=%s',
(leaderboard_id, entry_id, entry.score))[0]
entry.rank = from_rank + rank
else:
data = db.query_one('SELECT rank FROM score_buckets WHERE lid=%s AND score=%s', (leaderboard_id, entry.score))
entry.rank = data[0]
return entry
def sort(self, leaderboard_id, chunk_block=BUCKET_BLOCK):
res = db.query_one('SELECT max(score) as max_score, min(score) as min_score FROM entries WHERE lid=%s', (leaderboard_id,))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
if chunk_block is None and max_score > min_score:
chunk_block = (max_score - min_score) / (self.total(leaderboard_id)/ (max_score - min_score))
elif max_score == min_score:
chunk_block = BUCKET_BLOCK
rank, dense = 1, 1
buckets = []
self.clear_buckets(leaderboard_id)
to_score = max_score
from_score = to_score - chunk_block
from_score = max(min_score, from_score)
while to_score >= min_score:
dense_size = self._get_dense_size(leaderboard_id, from_score, to_score)
rank_size = self._get_rank_size(leaderboard_id, from_score, to_score)
buckets.append(BlockBucket(leaderboard_id, from_score, to_score, rank, rank + rank_size - 1, dense, dense + dense_size - 1))
if len(buckets) == 500:
self.save_buckets(buckets)
buckets[:] = []
to_score = from_score - 1
from_score = to_score - chunk_block
from_score = max(min_score, from_score)
dense += dense_size
rank += rank_size
self.save_buckets(buckets)
LOGGER.info('Block sort Leaderboard:%s takes %f (secs)', leaderboard_id, time.time() - start_time)
def create_lb(lid, name='test_3'):
r = db.query_one('SELECT lid from leaderboards WHERE name=%s', (name, ))
if not r:
db.execute('INSERT INTO leaderboards VALUES(%s, %s, "bucket")', (
lid,
name,
))
def sort(self, leaderboard_id, chunk_block=CHUNK_BLOCK):
res = db.query_one(
'SELECT max(score) as max_score, min(score) as min_score \
FROM entries WHERE lid=%s', (leaderboard_id, ))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
rank, dense = 0, 0
from_score = max_score
self.clear_buckets_by_score_range(leaderboard_id, from_score + 1, None)
while from_score >= min_score:
buckets, rank, dense = self._get_buckets(leaderboard_id,
from_score - chunk_block,
from_score, rank, dense)
self.clear_buckets_by_score_range(leaderboard_id,
from_score - chunk_block,
from_score)
self.save_buckets(buckets)
from_score -= chunk_block
self.clear_buckets_by_score_range(leaderboard_id, None, min_score - 1)
LOGGER.info('Score Bucket sort Leaderboard:%s takes %f (secs)',
leaderboard_id,
time.time() - start_time)
def rank_for_user(self, leaderboard_id, entry_id, dense=False):
entry = self.find(leaderboard_id, entry_id)
if entry:
if dense:
data = db.query_one('SELECT from_dense, to_score FROM chunk_buckets WHERE lid=%s AND from_score<=%s AND %s<=to_score', (leaderboard_id, entry.score, entry.score))
from_dense, to_score = data
rank = db.query_one('SELECT COUNT(eid) AS rank FROM entries WHERE lid=%s AND eid<%s AND %s<=score AND score<=%s',
(leaderboard_id, entry.entry_id, entry.score, to_score))
entry.rank = from_dense + rank[0]
else:
data = db.query_one('SELECT from_rank, to_score FROM chunk_buckets WHERE lid=%s AND from_score<=%s AND %s<=to_score', (leaderboard_id, entry.score, entry.score))
from_rank, to_score = data
rank = db.query_one('SELECT COUNT(DISTINCT(score)) AS rank FROM entries WHERE lid=%s AND %s<score AND score<=%s',
(leaderboard_id, entry.score, to_score))[0]
entry.rank = from_rank + rank
return entry
def rank_for_user(self, leaderboard_id, entry_id, dense=False):
entry = self.find(leaderboard_id, entry_id)
if entry:
if dense:
data = db.query_one(
'SELECT from_dense FROM score_buckets WHERE lid=%s AND score=%s',
(leaderboard_id, entry.score))
from_rank = data[0]
rank = db.query_one(
'SELECT COUNT(eid) as rank FROM entries WHERE lid=%s AND eid<%s AND score=%s',
(leaderboard_id, entry_id, entry.score))[0]
entry.rank = from_rank + rank
else:
data = db.query_one(
'SELECT rank FROM score_buckets WHERE lid=%s AND score=%s',
(leaderboard_id, entry.score))
entry.rank = data[0]
return entry
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):
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 sort(self, leaderboard_id, chunk_block=CHUNK_BLOCK):
res = db.query_one(
'SELECT max(score) as max_score, min(score) as min_score FROM entries WHERE lid=%s',
(leaderboard_id, ))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
rank, dense = 1, 1
buckets = []
self.clear_buckets(leaderboard_id)
to_score = max_score
chunk = DEFAULT_SCORE_CHUNK
from_score = to_score - chunk
from_score = max(min_score, from_score)
while to_score >= min_score:
while True:
dense_size = self._get_dense_size(leaderboard_id, from_score,
to_score)
if from_score == 0 or (
chunk_block /
2) < dense_size <= chunk_block or chunk == 1:
break
chunk += (chunk /
2) if chunk_block / 2 > dense_size else -(chunk / 2)
from_score = to_score - chunk
rank_size = self._get_rank_size(leaderboard_id, from_score,
to_score)
buckets.append(
ChunkBucket(leaderboard_id, from_score, to_score, rank,
rank + rank_size - 1, dense,
dense + dense_size - 1))
if len(buckets) == 500:
self.save_buckets(buckets)
buckets = []
to_score = from_score - 1
from_score = to_score - chunk
from_score = max(min_score, from_score)
dense += dense_size
rank += rank_size
self.save_buckets(buckets)
LOGGER.info('Chunk sort Leaderboard:%s takes %f (secs)',
leaderboard_id,
time.time() - start_time)
def sort(self, leaderboard_id, chunk_block=BUCKET_BLOCK):
res = db.query_one(
'SELECT max(score) as max_score, min(score) as min_score FROM entries WHERE lid=%s',
(leaderboard_id, ))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
if chunk_block is None and max_score > min_score:
chunk_block = (max_score -
min_score) / (self.total(leaderboard_id) /
(max_score - min_score))
elif max_score == min_score:
chunk_block = BUCKET_BLOCK
rank, dense = 1, 1
buckets = []
self.clear_buckets(leaderboard_id)
to_score = max_score
from_score = to_score - chunk_block
from_score = max(min_score, from_score)
while to_score >= min_score:
dense_size = self._get_dense_size(leaderboard_id, from_score,
to_score)
rank_size = self._get_rank_size(leaderboard_id, from_score,
to_score)
buckets.append(
BlockBucket(leaderboard_id, from_score, to_score, rank,
rank + rank_size - 1, dense,
dense + dense_size - 1))
if len(buckets) == 500:
self.save_buckets(buckets)
buckets[:] = []
to_score = from_score - 1
from_score = to_score - chunk_block
from_score = max(min_score, from_score)
dense += dense_size
rank += rank_size
self.save_buckets(buckets)
LOGGER.info('Block sort Leaderboard:%s takes %f (secs)',
leaderboard_id,
time.time() - start_time)
def sort(self, leaderboard_id, chunk_block=CHUNK_BLOCK):
res = db.query_one('SELECT max(score) as max_score, min(score) as min_score \
FROM entries WHERE lid=%s', (leaderboard_id,))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
rank, dense = 0, 0
from_score = max_score
self.clear_buckets_by_score_range(leaderboard_id, from_score + 1, None)
while from_score >= min_score:
buckets, rank, dense = self._get_buckets(leaderboard_id, from_score - chunk_block, from_score, rank, dense)
self.clear_buckets_by_score_range(leaderboard_id, from_score - chunk_block, from_score)
self.save_buckets(buckets)
from_score -= chunk_block
self.clear_buckets_by_score_range(leaderboard_id, None, min_score -1)
LOGGER.info('Score Bucket sort Leaderboard:%s takes %f (secs)', leaderboard_id, time.time() - start_time)
def sort(self, leaderboard_id, chunk_block=CHUNK_BLOCK):
res = db.query_one('SELECT max(score) as max_score, min(score) as min_score FROM entries WHERE lid=%s', (leaderboard_id,))
if not res:
LOGGER.info('Possibly not found Leaderboard:%d', leaderboard_id)
return
start_time = time.time()
max_score, min_score = res
rank, dense = 1, 1
buckets = []
self.clear_buckets(leaderboard_id)
to_score = max_score
chunk = DEFAULT_SCORE_CHUNK
from_score = to_score - chunk
from_score = max(min_score, from_score)
while to_score >= min_score:
while True:
dense_size = self._get_dense_size(leaderboard_id, from_score, to_score)
if from_score == 0 or (chunk_block / 2) < dense_size <= chunk_block or chunk == 1:
break
chunk += (chunk / 2) if chunk_block / 2 > dense_size else -(chunk / 2)
from_score = to_score - chunk
rank_size = self._get_rank_size(leaderboard_id, from_score, to_score)
buckets.append(ChunkBucket(leaderboard_id, from_score, to_score, rank, rank + rank_size - 1, dense, dense + dense_size - 1))
if len(buckets) == 500:
self.save_buckets(buckets)
buckets[:] = []
to_score = from_score - 1
from_score = to_score - chunk
from_score = max(min_score, from_score)
dense += dense_size
rank += rank_size
self.save_buckets(buckets)
LOGGER.info('Chunk sort Leaderboard:%s takes %f (secs)', leaderboard_id, time.time() - start_time)
def test_query_one(self):
res = db.query_one('select count(1) from users')[0]
self.assertEqual(99L, res)
def find(self, leaderboard_id, entry_id):
data = db.query_one('SELECT eid, lid, score, data, created FROM entries WHERE lid=%s AND eid=%s', (leaderboard_id, entry_id))
if data:
return self._load(data)
def rank_for_user(self, lid, eid, dense=False):
sql = self._build_rank_sql(dense)
sql += '\nWHERE lid=%s AND eid=%s'
data = db.query_one(sql, (lid, eid))
if data:
return self._load(data)
def total(self, leaderboard_id):
data = db.query_one('SELECT COUNT(1) FROM entries WHERE lid=%s', (leaderboard_id,))
return data[0]
def find_by_name(self, name):
data = db.query_one('SELECT * FROM leaderboards WHERE name=%s', (name,))
if data:
return self._load(data)
def find(self, leaderboard_id):
data = db.query_one('SELECT * FROM leaderboards WHERE lid=%s', (leaderboard_id,))
if data:
return self._load(data)
def create_lb(lid, name='test_3'):
r = db.query_one('SELECT lid from leaderboards WHERE name=%s', (name,))
if not r:
db.execute('INSERT INTO leaderboards VALUES(%s, %s, "bucket")', (lid, name,))
def _get_rank_size(self, leaderboard_id, from_score, to_score):
return db.query_one(
'SELECT COUNT(DISTINCT(score)) size FROM entries WHERE lid=%s AND %s<=score AND score<=%s',
(leaderboard_id, from_score, to_score))[0]
def create_lb(lid=2, name='unittest'):
r = db.query_one('SELECT lid from leaderboards WHERE lid=%s', (lid,))
if r:
return False
db.execute('INSERT INTO leaderboards VALUES(%s, %s, "base")', (lid, name,))
return True
def _get_rank_size(self, leaderboard_id, from_score, to_score):
return db.query_one('SELECT COUNT(DISTINCT(score)) size FROM entries WHERE lid=%s AND %s<=score AND score<=%s',
(leaderboard_id, from_score, to_score))[0]