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 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, 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 find(self, name): res = db.query('SELECT * FROM cron WHERE name=%s', (name,)) if res: return self._load(res[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 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 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)', [(100L, 'thomas'), (101L, 'animer')]) res = db.query('SELECT count(*) FROM users WHERE uid>=100') self.assertEqual(2, res[0][0])