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 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 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 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 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 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 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 setUp(self):
db.setup('localhost', 'test', 'test', 'leaf', pool_opt={'minconn': 3, 'maxconn': 10}, key='slave')
db.execute('DROP TABLE IF EXISTS `users`', key='slave')
res = db.execute("""CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`uid`))""", key='slave')
rows = []
for _ in range(1, 10):
rows.append('(%d , "name_%d")' % (_, _))
db.execute('INSERT INTO users VALUES ' + ', '.join(rows), key='slave')
def make_entries(total=1000000):
to = 0
rows = []
for uid in range(1, total + 1):
if len(rows) == 1000:
rows.append('(%d, %d, %d)' % (uid, 1, randint(0, 10000)))
db.execute('INSERT INTO entries VALUES ' + ', '.join(rows))
rows = []
else:
rows.append('(%d, %d, %d)' % (uid, 1, randint(0, 10000)))
db.execute('INSERT INTO entries VALUES ' + ', '.join(rows))
def setUp(self):
db.setup("localhost", "test", "test", "leaf", 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 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 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 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 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 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 tearDown(self):
db.execute("DELETE FROM cron WHERE name like 'job%'")
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 delete(self, leaderboard_id, entry_id):
return db.execute("DELETE FROM entries WHERE lid=%s AND eid=%s", (leaderboard_id, entry_id))
def save(self, leaderboard):
if leaderboard.leaderboard_id:
return db.execute('INSERT INTO leaderboards (name) VALUES(%s)', leaderboard.name)
else:
return db.execute('INSERT INTO leaderboard (lid, name) VALUES (%s, %s) ON DUPLICATE KEY UPDATE name=VALUES(name)',
(leaderboard.leaderboard_id, leaderboard.name,))
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 create_lb(name='test'):
r = db.query_one('SELECT lid from leaderboards WHERE name=%s', (name,))
if not r:
db.execute('INSERT INTO leaderboards VALUES(1, %s)', (name,))
def tearDown(self):
db.execute("DELETE FROM leaderboards WHERE name like 'lb%'")
def down(lid):
db.execute('DELETE FROM entries WHERE lid=%s', (lid,))
db.execute('DELETE FROM leaderboards WHERE lid=%s', (lid,))
def tearDown(self):
db.execute("DELETE FROM users", key="slave")
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 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 down(lid):
db.execute('DELETE FROM entries WHERE lid=%s', (lid, ))
db.execute('DELETE FROM leaderboards WHERE lid=%s', (lid, ))
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', key='slave')
def clear_buckets(self, leaderboard_id):
return db.execute('DELETE FROM score_buckets WHERE lid=%s', (leaderboard_id,))
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 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 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 delete(self, leaderboard_id, entry_id):
return db.execute('DELETE FROM entries WHERE lid=%s AND eid=%s',
(leaderboard_id, entry_id))
def save(self, entry):
return db.execute('INSERT INTO entries (eid, lid, score) VALUES (%s, %s, %s) \
ON DUPLICATE KEY UPDATE score=VALUES(score)',
(entry.entry_id, entry.leaderboard_id, entry.score))
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 clear_buckets(self, leaderboard_id):
return db.execute('DELETE FROM chunk_buckets WHERE lid=%s', (leaderboard_id,))
def tearDown(self):
db.execute("DELETE FROM cron WHERE name like 'job%'")
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 tearDown(self):
db.execute("DELETE FROM leaderboards WHERE name like 'lb%'")
def tearDown(self):
db.execute('DELETE FROM users')