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 save_buckets(self, buckets):
if not buckets:
return
sql = 'INSERT INTO score_buckets(score, size, lid, from_dense, to_dense, rank) VALUES '
rows = []
for bucket in buckets:
rows.append('(%d, %d, %d, %d, %d, %d)' % (bucket.score, bucket.size,
bucket.leaderboard_id, bucket.from_dense, bucket.to_dense, bucket.rank))
db.execute(sql + ','.join(rows))
def save_buckets(self, buckets):
if not buckets:
return
sql = 'INSERT INTO chunk_buckets(lid, from_score, to_score, from_rank, to_rank, from_dense, to_dense) VALUES '
rows = []
for bucket in buckets:
rows.append('(%d, %d, %d, %d, %d, %d, %d)' % (bucket.leaderboard_id, bucket.from_score,
bucket.to_score, bucket.from_rank, bucket.to_rank, bucket.from_dense, bucket.to_dense))
db.execute(sql + ','.join(rows))
def save_buckets(self, buckets):
if not buckets:
return
sql = 'INSERT INTO score_buckets(score, size, lid, from_dense, to_dense, rank) VALUES '
rows = []
for bucket in buckets:
rows.append('(%d, %d, %d, %d, %d, %d)' %
(bucket.score, bucket.size, bucket.leaderboard_id,
bucket.from_dense, bucket.to_dense, bucket.rank))
db.execute(sql + ','.join(rows))
def make_entries(lid=2, total=1000000):
to = 0
rows = []
for uid in range(1, total + 1):
data = r'{\"user\": \"user_%d\"}' %(uid)
rows.append('(%d, %d, %d, "%s", "2014-08-17 12:49:01")' % (uid, lid, (total - uid)/3, data))
if len(rows) == 1000:
db.execute('INSERT INTO entries VALUES ' + ', '.join(rows))
rows = []
db.execute('INSERT INTO entries VALUES ' + ', '.join(rows))
def clear_buckets_by_score_range(self, leaderboard_id, from_score,
to_score):
if to_score is None:
return db.execute(
'DELETE FROM score_buckets WHERE lid=%s AND %s<score',
(leaderboard_id, from_score))
if from_score is None:
return db.execute(
'DELETE FROM score_buckets WHERE lid=%s AND score<=%s',
(leaderboard_id, to_score))
return db.execute(
'DELETE FROM score_buckets WHERE lid=%s AND %s<score AND score<=%s',
(leaderboard_id, from_score, to_score))
def setUp(self):
db.setup('localhost',
'test',
'test',
'nagi',
pool_opt={
'minconn': 3,
'maxconn': 10
})
db.execute('DROP TABLE IF EXISTS `users`')
res = db.execute("""CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`uid`))""")
rows = []
for _ in range(1, 100):
rows.append('(%d , "name_%d")' % (_, _))
db.execute('INSERT INTO users VALUES ' + ', '.join(rows))
def save(self, job):
if job.cron_id is None:
return db.execute(
'INSERT INTO cron(job_id, name, event, next_run, last_run) VALUES (%s, %s, %s, %s, %s)',
(
job.job_id,
job.name,
job.event,
job.next_run,
job.last_run,
))
return db.execute(
'INSERT INTO cron VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE cron_id=VALUES(cron_id), job_id=VALUES(job_id), event=VALUES(event), next_run=VALUES(next_run), last_run=VALUES(last_run)',
(
job.cron_id,
job.job_id,
job.name,
job.event,
job.next_run,
job.last_run,
))
def save(self, entry):
if entry.data:
entry.data = dumps(entry.data)
return db.execute('INSERT INTO entries (eid, lid, score, data, created) VALUES (%s, %s, %s, %s, %s) \
ON DUPLICATE KEY UPDATE score=VALUES(score)',
(entry.entry_id, entry.leaderboard_id, entry.score, entry.data, entry.created))
def delete(self, leaderboard):
db.execute('DELETE FROM entries WHERE lid=%s', (leaderboard.leaderboard_id,))
db.execute('DELETE FROM leaderboards WHERE lid=%s', (leaderboard.leaderboard_id,))
def save(self, leaderboard):
if not leaderboard.leaderboard_id:
return db.execute('INSERT INTO leaderboards (name, adapter) VALUES(%s, %s)', (leaderboard.name, leaderboard.adapter))
else:
return db.execute('INSERT INTO leaderboards VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE name=VALUES(name), adapter=VALUES(adapter)',
(leaderboard.leaderboard_id, leaderboard.name, leaderboard.adapter))
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 tearDown(self):
db.execute("DELETE FROM leaderboards WHERE name like 'lb%'")
def claim(self):
self.job_id = self.gen_job_id()
db.execute('UPDATE cron SET job_id=%s WHERE job_id IS NULL AND next_run <= %s LIMIT %s',
(self.job_id, datetime.now(), self.limit))
return Thing('job').find_by_job_id(self.job_id)
def delete(self, job):
return db.execute('DELETE FROM cron WHERE cron_id=%s', (job.cron_id,))
def tearDown(self):
db.execute('DELETE FROM users')
def claim(self):
self.job_id = self.gen_job_id()
db.execute(
'UPDATE cron SET job_id=%s WHERE job_id IS NULL AND next_run <= %s LIMIT %s',
(self.job_id, datetime.now(), self.limit))
return Thing('job').find_by_job_id(self.job_id)
def clear_buckets(self, leaderboard_id):
return db.execute('DELETE FROM score_buckets WHERE lid=%s',
(leaderboard_id, ))
def tearDown(self):
db.execute('DELETE FROM users', key='slave')
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 down(lid):
db.execute('DELETE FROM entries WHERE lid=%s', (lid,))
db.execute('DELETE FROM leaderboards WHERE lid=%s', (lid,))
def delete(self, leaderboard_id, entry_id):
return db.execute('DELETE FROM entries WHERE lid=%s AND eid=%s', (leaderboard_id, entry_id))
def tearDown(self):
db.execute("DELETE FROM cron WHERE name like 'job%'")
def save(self, job):
if job.cron_id is None:
return db.execute('INSERT INTO cron(job_id, name, event, next_run, last_run) VALUES (%s, %s, %s, %s, %s)',
(job.job_id, job.name, job.event, job.next_run, job.last_run,))
return db.execute('INSERT INTO cron VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE cron_id=VALUES(cron_id), job_id=VALUES(job_id), event=VALUES(event), next_run=VALUES(next_run), last_run=VALUES(last_run)',
(job.cron_id, job.job_id, job.name, job.event, job.next_run, job.last_run,))
def setUp(self):
db.execute("DELETE FROM leaderboards WHERE name like 'lb%'")
db.execute('INSERT INTO leaderboards VALUES (101, %s, %s )', ('lb101', 'base'))
db.execute('INSERT INTO leaderboards VALUES (102, %s, %s)', ('lb102', 'bucket'))
self.lbthing = LeaderboardThing()
self.lb= Leaderboard(103, 'lb103', 'bucket')
def setUp(self):
db.execute("DELETE FROM cron WHERE name like 'job%'")
db.execute('INSERT INTO cron VALUES (1, NULL, %s, %s, NULL, NULL)', ('job_name1', 'every 10'))
db.execute('INSERT INTO cron VALUES (2, "job_id", %s, %s, NULL, NULL)', ('job_name2', 'every 10'))
self.jobthing = JobThing()
self.job = Job(3, 'job_3', 'job_name3', 'every 5')
def delete(self, job):
return db.execute('DELETE FROM cron WHERE cron_id=%s', (job.cron_id, ))
def clear_buckets(self, leaderboard_id):
return db.execute('DELETE FROM chunk_buckets WHERE lid=%s', (leaderboard_id,))
def clear_buckets_by_score_range(self, leaderboard_id, from_score, to_score):
if to_score is None:
return db.execute('DELETE FROM score_buckets WHERE lid=%s AND %s<score', (leaderboard_id, from_score))
if from_score is None:
return db.execute('DELETE FROM score_buckets WHERE lid=%s AND score<=%s', (leaderboard_id, to_score))
return db.execute('DELETE FROM score_buckets WHERE lid=%s AND %s<score AND score<=%s', (leaderboard_id, from_score, to_score))
