Example #1
0
    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
Example #2
0
 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
Example #3
0
    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)
Example #4
0
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,
        ))
Example #5
0
    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)
Example #6
0
 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
Example #7
0
 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
Example #8
0
 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
Example #9
0
 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
Example #10
0
    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)
Example #11
0
    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)
Example #12
0
 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)
Example #13
0
    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)
Example #14
0
 def test_query_one(self):
     res = db.query_one('select count(1) from  users')[0]
     self.assertEqual(99L, res)
Example #15
0
 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)
Example #16
0
 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)
Example #17
0
 def total(self, leaderboard_id):
     data = db.query_one('SELECT COUNT(1) FROM entries WHERE lid=%s', (leaderboard_id,))
     return data[0]
Example #18
0
 def find_by_name(self, name):
     data = db.query_one('SELECT * FROM leaderboards WHERE name=%s', (name,))
     if data:
         return self._load(data)
Example #19
0
 def find(self, leaderboard_id):
     data = db.query_one('SELECT * FROM leaderboards WHERE lid=%s', (leaderboard_id,))
     if data:
         return self._load(data)
Example #20
0
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,))
Example #21
0
 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]
Example #22
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
Example #23
0
 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]