def post_execute_priority(self, data={}):
required_args = [{
'name': '+priority',
'type': dict,
}]
err = form_validation(data, required_args)
if err: return (err, None)
priority = data['priority']
err, execute_list = yield from self.get_execute_list()
execute_list = [x['id'] for x in execute_list]
max_priority = len(execute_list)
for execute in execute_list:
execute = int(execute)
if execute not in priority:
return ((400, 'priority of execute_type.%s not found'%(str(execute))), None)
id = list(priority.keys())
for execute in priority:
execute = int(execute)
if execute not in execute_list:
return ((400, 'execute_type.%s not exist'%(str(execute))), None)
if execute > max_priority:
return ((400, 'priority of execute_type.%s error'%(str(execute))), None)
if id.count(execute) > 1:
return ((400, 'priority can not duplicate'), None)
for id, pri in priority.items():
yield self.db.execute('UPDATE execute_types SET priority=%s WHERE id=%s;', (pri, id,))
# self.rs.delete('execute@%s'%(str(id)))
# self.rs.delete('execute_list')
return (None, None)
def post_group_user_power(self, data={}):
required_args = [{
'name': '+user_id',
'type': int,
}, {
'name': '+group_id',
'type': int,
}, {
'name': '+power',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
err, current_power = yield from self.get_group_user_power(data)
print(current_power)
if int(data['power']) in current_power:
yield self.db.execute(
'DELETE FROM map_group_user_power WHERE user_id=%s AND group_id=%s AND power=%s;',
(
data['user_id'],
data['group_id'],
data['power'],
))
else:
sql, param = self.gen_insert_sql('map_group_user_power', data)
# yield self.db.execute('INSERT INTO map_group_user_power (user_id, group_id, power) VALUES(%s, %s, %s);', (uid, gid, power))
yield self.db.execute(sql, param)
# self.rs.delete('user_group_power@%s@%s' % (str(uid), str(gid)))
return (None, None)
def signin(self, data, req): #need to set cookie
'''
data(dict): account(str), passwd(str)
return id(str)
'''
### check required arguemts
required_args = [{
'name': '+account',
'type': str,
}, {
'name': '+passwd',
'type': str,
}]
err = form_validation(data, required_args)
if err: return (err, None)
### get hashed passwd
col = ['passwd', 'id', 'token']
sql = self.gen_select_sql('users', col)
res = yield self.db.execute(sql+' WHERE account = %s;', (data['account'],))
### check account
if res.rowcount == 0:
return ((404, 'User Not Exist'), None)
res = res.fetchone()
hpwd, id, token = res["passwd"], res["id"], res['token']
### check passwd
if self.hash_pwd(data['passwd']) != hpwd:
return ((400, 'Wrong Password'), None)
req.set_secure_cookie('token', token)
return (None, str(id))
def get_contest_user_problem_score(self, data={}):
required_args = [{
'name': '+user_id',
'type': int,
}, {
'name': '+problem',
'type': dict,
}, {
'name': '+start',
'type': datetime.datetime,
}, {
'name': '+end',
'type': datetime.datetime,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT COUNT(*) FROM submissions WHERE user_id=%s AND problem_id=%s AND %s<=created_at AND created_at<=%s;', (data['user_id'], data['problem']['id'], data['start'], data['end']))
res = res.fetchone()
if res['count'] == 0: ### not submit yet
res = {}
res['score'] = None
res['verdict'] = 0
res['submitted'] = 0
res['penalty'] = 0
return (None, res)
res = yield self.db.execute('SELECT * FROM submissions WHERE user_id=%s AND problem_id=%s AND %s<=created_at AND created_at<=%s AND verdict=7 ORDER BY id LIMIT 1;', (data['user_id'], data['problem']['id'], data['start'], data['end']))
if res.rowcount != 0:
res = res.fetchone()
data['end'] = min(data['end'], res['created_at'])
res = yield self.db.execute('SELECT verdict, COUNT(*) as submitted, MAX(s.score) as score FROM (SELECT FROM submissions WHERE s.user_id=%s AND s.problem_id=%s AND %s<=s.created_at AND s.created_at<=%s) as s, (SELECT m.id FROM map_verdict_string as m WHERE m.priority=(SELECT MAX(m.priority) FROM map_verdict_string as m WHERE m.id=s.verdict)) as verdict;', (data['user_id'], data['problem']['id'], data['start'], data['end']))
res = res.fetchone()
res['penalty'] = (res['submitted']-1) * data['problem']['penalty']
if score != '':
pass
return (None, res)
def get_execute(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
if int(data['id']) == 0:
res = {}
res['steps'] = []
res['id'] = 0
res['lang'] = 0
res['description'] = ''
res['cm_mode'] = ''
return (None, res)
# res = self.rs.get('execute@%s'%(str(data['id'])))
# if res: return (None, res)
sql = "SELECT e.*, u.account as setter_user FROM execute_types as e, users as u WHERE e.id=%s AND e.setter_user_id=u.id"
res = yield self.db.execute(sql, (data["id"], ))
if res.rowcount == 0:
return ((404, 'Error execute id'), None)
res = res.fetchone()
res['steps'] = (yield self.db.execute("SELECT execute_steps.* FROM execute_steps WHERE execute_type_id=%s ORDER BY id", (res['id'],))).fetchall()
for id, x in enumerate(res['steps']):
x['step'] = id + 1
# self.rs.set('execute@%s'%(str(data['id'])), res)
return (None, res)
def get_user_info_by_account_passwd(self, data={}):
required_args = [{
'name': '+account',
'type': str,
}, {
'name': '+passwd',
'type': str,
}]
err = form_validation(data, required_args)
if err: return (err, None)
### get hashed passwd
col = ['passwd', 'id', 'token']
sql = self.gen_select_sql('users', col)
res = yield self.db.execute(sql+' WHERE account = %s;', (data['account'],))
### check account
if res.rowcount == 0:
return ((404, 'User Not Exist'), None)
res = res.fetchone()
hpwd, id, token = res["passwd"], res["id"], res['token']
### check passwd
if self.hash_pwd(data['passwd']) != hpwd:
return ((400, 'Wrong Password'), None)
return (None, {
'id': id,
'token': token
})
def get_contest_submission_list_count(self, data):
required_args = [{
'name': '+id',
'type': int,
}, {
'name': '+user_id',
'type': int,
}, {
'name': '+current_group_power',
'type': set,
}]
err = form_validation(data, required_args)
if err: return (err, None)
err, res = yield from self.get_contest(data)
start = res['start']
end = res['end']
sql = """
SELECT count(*)
FROM submissions as s, map_contest_problem as mp, map_contest_user as mu, problems as p
WHERE """;
if map_group_power['contest_manage'] not in data['current_group_power']:
sql += "s.user_id=%s AND " % (int(data['user_id']))
sql += """
mu.user_id=s.user_id
AND mu.contest_id=%s AND mp.contest_id=mu.contest_id
AND mp.problem_id=s.problem_id
AND mp.problem_id=p.id
AND %s<=s.created_at AND s.created_at<=%s
""";
res = yield self.db.execute(sql, (res['id'], start, end, ))
return (None, res.fetchone()['count'])
def get_contest_submission_list_count(self, data):
required_args = [
{"name": "+id", "type": int},
{"name": "+user_id", "type": int},
{"name": "+current_group_power", "type": set},
]
err = form_validation(data, required_args)
if err:
return (err, None)
err, res = yield from self.get_contest(data)
start = res["start"]
end = res["end"]
sql = """
SELECT count(*)
FROM submissions as s, map_contest_problem as mp, map_contest_user as mu, problems as p
WHERE """
if map_group_power["contest_manage"] not in data["current_group_power"]:
sql += "s.user_id=%s AND " % (int(data["user_id"]))
sql += """
mu.user_id=s.user_id
AND mu.contest_id=%s AND mp.contest_id=mu.contest_id
AND mp.problem_id=s.problem_id
AND mp.problem_id=p.id
AND %s<=s.created_at AND s.created_at<=%s
"""
res = yield self.db.execute(sql, (res["id"], start, end))
return (None, res.fetchone()["count"])
def get_problem_list(self, data={}):
required_args = [{
'name': '+group_id',
'type': int,
}, {
'name': '+user_id',
'type': int,
}, {
'name': '+page',
'type': int,
}, {
'name': '+count',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
sql = """
SELECT
(SELECT COUNT(*) FROM (SELECT * FROM submissions WHERE problem_id=p.id AND user_id=%s AND verdict=9 limit 1) AS m) AS ac,
(SELECT COUNT(*) FROM (SELECT * FROM submissions WHERE problem_id=p.id AND user_id=%s limit 1) AS n) AS try,
p.id, p.title, p.source, p.visible, p.created_at,
u.id as setter_user_id, u.account as setter_user,
g.id as group_id, g.name as group_name
FROM problems as p, users as u, groups as g
WHERE u.id=p.setter_user_id AND g.id=p.group_id AND
"""
sql += """ (p.group_id=%s) """
sql += """ ORDER BY p.id limit %s OFFSET %s """
res = yield self.db.execute(sql, (data['user_id'], data['user_id'], data['group_id'], data['count'], (int(data["page"])-1)*int(data["count"]), ))
return (None, res.fetchall())
def get_contest_submission(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}, {
'name': '+user_id',
'type': int,
}, {
'name': '+current_group_power',
'type': set,
}, {
'name': '+submission_id',
'type': int,
}, {
'name': '+group_id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
err, res = yield from self.get_contest(data)
if err: return (err, None)
err, submission_res = yield from Service.Submission.get_submission({'id': data['submission_id'], 'group_id': data['group_id'],})
if err: return (err, None)
if map_group_power['contest_manage'] not in data['current_group_power']:
if int(submission_res['user_id']) != int(data['user_id']):
return ((404, 'No Submission id'), None)
return (None, submission_res)
def post_group_user(self, data={}):
required_args = [{
'name': '+user_id',
'type': int,
}, {
'name': '+group_id',
'type': int,
}, {
'name': 'force',
'type': bool,
}]
err = form_validation(data, required_args)
if err: return (err, None)
if 'force' in data:
force = data.pop('force')
else: force = False
err, group = yield from Service.Group.get_group({'id': data['group_id']})
if err: return (err, None)
if group['type'] == 0 or force == True: # public
if force:
self.db.execute('DELETE FROM map_inpublic_group_user WHERE group_id=%s AND user_id=%s;', (data['group_id'], data['user_id'],))
sql, param = self.gen_insert_sql('map_group_user', data)
try: res = yield self.db.execute(sql, param)
except: return ((400, 'Already in'), None)
id = res.fetchone()['id']
return (None, id)
elif group['type'] == 1: #inpublic
sql, param = self.gen_insert_sql('map_inpublic_group_user', data)
try: res = self.db.execute(sql, param)
except: return ((400, 'Already registered'), None)
return (None, None)
elif group['type'] == -1: #private
return ((403, 'This group is private'), None)
def put_bulletin(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}, {
'name': '+group_id',
'type': int,
}, {
'name': '+setter_user_id',
'type': int,
}, {
'name': '+title',
'type': str,
'xss': True,
}, {
'name': '+content',
'type': str,
'xss': True,
}]
err = form_validation(data, required_args)
if err: return (err, None)
err, res = yield from self.get_bulletin(data)
if err: return (err, None)
data.pop('id')
sql, parma = self.gen_update_sql("bulletins", data)
yield self.db.execute("%s WHERE id=%%s AND group_id=%%s;"%sql, parma+(res['id'],res['group_id'],))
return (None, None)
def post_verdict(self ,data={}):
required_args = [{
'name': '+title',
'type': str,
}, {
'name': '+execute_type_id',
'type': int,
}, {
'name': '+setter_user_id',
'type': int,
}, {
'name': '+code_file',
}]
err = form_validation(data, required_args)
if err: return (err, None)
code_file = None
if data['code_file'] is None:
return ((400, 'No code file'), None)
data['file_name'] = data['code_file']['filename']
code_file = data.pop('code_file')
sql, param = self.gen_insert_sql('verdicts', data)
id = (yield self.db.execute(sql, param)).fetchone()['id']
if code_file:
folder = '%s/data/verdicts/%s/' % (config.DATAROOT, str(id))
file_path = '%s/%s' % (folder, data['file_name'])
try: shutil.rmtree(folder)
except: pass
try: os.makedirs(folder)
except: pass
with open(file_path, 'wb+') as f:
f.write(code_file['body'])
return (None, str(id))
def get_contest_list(self, data={}):
required_args = [{
'name': '+group_id',
'type': int,
}, {
'name': '+page',
'type': int,
}, {
'name': '+count',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
sql = """
SELECT
c.*,
u.id as setter_user_id, u.account as setter_user,
g.id as group_id, g.name as group_name
FROM contests as c, users as u, groups as g
WHERE u.id=c.setter_user_id AND g.id=c.group_id AND
"""
sql += " (c.group_id=%s) "
sql += " ORDER BY c.id limit %s OFFSET %s "
res = yield self.db.execute(sql, (data['group_id'], data['count'], (int(data["page"])-1)*int(data["count"]), ))
return (None, res.fetchall())
def resettoken(self, data):
required_args = [{
'name': '+account',
'type': dict,
}, {
'name': '+passwd',
'type': str,
}]
err = form_validation(data, required_args)
if err: return (err, None)
id = data['account']['id']
token = data['account']['token']
sql = self.gen_select_sql('users', ['account', 'passwd'])
res = yield self.db.execute(sql + ' WHERE id = %s;', (id,))
if res.rowcount == 0:
return ('ID Not Exist', None)
res = res.fetchone()
hpasswd = res['passwd']
account = res['account']
if self.hash_pwd(data['passwd']) != hpasswd:
return ((400, 'Passwd Error'), None)
# self.rs.delete("user_basic@%s" % id)
# self.rs.delete("user_token@%s" % token)
token = self.gen_token(account)
sql, prama = self.gen_update_sql('users', {'token': token})
res = yield self.db.execute(sql + ' WHERE id = %s;', prama + (id,))
return (None, token)
def get_verdict(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
if int(data['id']) == 0:
col = ['id', 'title', 'execute_type_id', 'execute_type_id', 'file_name', 'setter_user_id']
res = {x: '' for x in col}
res['id'] = 0
return (None, res)
# res = self.rs.get('verdict@%s'%str(data['id']))
# if res: return (None, res)
res = yield self.db.execute('SELECT v.*, u.account as setter_user FROM verdicts as v, users as u WHERE v.id=%s AND v.setter_user_id=u.id;', (data['id'],))
if res.rowcount == 0:
return ((404, 'No Verdict ID'), None)
res = res.fetchone()
err, res['execute_type'] = yield from Service.Execute.get_execute({'id': res['execute_type_id']})
folder = '%s/data/verdicts/%s/' % (config.DATAROOT, str(res['id']))
file_path = '%s/%s' % (folder, res['file_name'])
try: os.makedirs(folder)
except: pass
with open(file_path) as f:
res['code'] = f.read()
res['code_line'] = len(open(file_path).readlines())
# self.rs.set('verdict@%s'%(str(data['id'])), res)
return (None, res)
def put_testdata(self, data={}):
required_args = [
{"name": "+id", "type": int},
{"name": "+problem_id", "type": int},
{"name": "input"},
{"name": "output"},
{"name": "+score", "type": int},
{"name": "+time_limit", "type": int},
{"name": "+memory_limit", "type": int},
{"name": "+output_limit", "type": int},
]
err = form_validation(data, required_args)
if err:
return (err, None)
# self.rs.delete('testdata@%s'%(str(data['id'])))
# self.rs.delete('testdata_list@problem@%s'%(str(data['problem_id'])))
required_args = ["time_limit", "memory_limit", "output_limit", "score"]
meta = {x: data[x] for x in required_args}
sql, parma = self.gen_update_sql("testdata", meta)
yield self.db.execute("%s WHERE id=%s" % (sql, data["id"]), parma)
""" create folder """
folder = "%s/data/testdata/%s" % (config.DATAROOT, str(data["id"]))
try:
os.makedirs(folder)
except Exception as e:
print(e)
""" save file and upload to ftp """
for x in ["input", "output"]:
if data[x] != None:
file_path = "%s/%s" % (folder, x)
with open(file_path, "wb+") as f:
f.write(data[x]["body"])
return (None, data["id"])
def get_submission_list_count(self, data):
required_args = [{
'name': '+group_id',
'type': int,
}, {
'name': 'problem_id',
'type': int,
}, {
'name': 'account',
'type': str,
}, {
'name': 'verdict',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
sql = "SELECT count(*) FROM submissions as s, problems as p"
sql += " WHERE s.problem_id = p.id AND p.group_id=%s"%(data['group_id'])
if data['problem_id']:
sql += " AND problem_id=%s " % (int(data['problem_id']))
if data['account']:
try:
user_id = (yield self.db.execute("SELECT id FROM users WHERE account=%s", (data['account'],))).fetchone()['id']
except:
user_id = 0
sql += " AND user_id=%s " % (user_id)
if data['verdict']:
sql += " AND s.verdict=%s" % (data['verdict'])
res = yield self.db.execute(sql)
return (None, res.fetchone()['count'])
def get_problem(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
if int(data['id']) == 0:
col = ["id", "title", "description", "input", "output", "sample_input", "sample_output", "hint", "source", "group_id", "setter_user_id", "visible", "interactive", "checker_id", "created_at", "updated_at","pdf", "score_type_id"]
res = { x: "" for x in col }
res['id'] = 0
res['visible'] = 0
res['verdict_id'] = 1
res['score_type_id'] = 1
return (None, res)
# res = self.rs.get('problem@%s' % str(data['id']))
res = None
if not res:
sql = "SELECT p.*, u.account as setter_user FROM problems as p, users as u WHERE p.setter_user_id=u.id AND p.id=%s"
res = yield self.db.execute(sql, (data["id"], ))
if res.rowcount == 0:
return ('No problem id', None)
res = res.fetchone()
# self.rs.set('problem@%s' % str(data['id']), res)
err, res['execute'] = yield from Service.Problem.get_problem_execute({'problem_id': data['id']})
err, res['testdata'] = yield from Service.Testdata.get_testdata_list_by_problem({'problem_id': data['id']})
return (None, res)
def get_contest(self, data={}):
required_args = [{"name": "+id", "type": int}]
err = form_validation(data, required_args)
if err:
return (err, None)
### new contest
if int(data["id"]) == 0:
col = ["id", "group_id", "visible", "title", "description", "setter_user_id", "type", "freeze"]
res = {x: "" for x in col}
col = ["register_start", "register_end", "start", "end"]
res.update({x: datetime.datetime.now() for x in col})
res["visible"] = 0
res["id"] = 0
res["problem"] = []
return (None, res)
# res = self.rs.get('contest@%s'%str(data['id']))
res = None
if not res:
res = yield self.db.execute(
"SELECT c.*, u.account as setter_user FROM contests as c, users as u WHERE c.setter_user_id=u.id AND c.id=%s;",
(data["id"],),
)
if res.rowcount == 0:
return ((404, "No Contest ID"), None)
res = res.fetchone()
# self.rs.set('contest@%s'%str(data['id']), res)
err, res["problem"] = yield from self.get_contest_problem_list(data)
err, res["user"] = yield from self.get_contest_user(data)
return (None, res)
def get_contest_submission_list(self, data={}):
required_args = [
{"name": "+id", "type": int},
{"name": "+user_id", "type": int},
{"name": "+current_group_power", "type": set},
{"name": "+page", "type": int},
{"name": "+count", "type": int},
]
err = form_validation(data, required_args)
if err:
return (err, None)
err, res = yield from self.get_contest(data)
start = res["start"]
end = res["end"]
sql = """
SELECT s.*, u.account as user, p.title as problem_title
FROM submissions as s, users as u, map_contest_problem as mp, map_contest_user as mu, problems as p
WHERE """
if map_group_power["contest_manage"] not in data["current_group_power"]:
sql += "u.id=%s AND " % (int(data["user_id"]))
sql += """
u.id=s.user_id AND mu.user_id=u.id
AND mu.contest_id=%s AND mp.contest_id=mu.contest_id
AND mp.problem_id=s.problem_id
AND mp.problem_id=p.id
AND %s<=s.created_at AND s.created_at<=%s
ORDER BY s.id DESC LIMIT %s OFFSET %s;
"""
submissions = yield self.db.execute(
sql, (res["id"], start, end, data["count"], int(data["page"] - 1) * int(data["count"]))
)
return (None, submissions.fetchall())
def put_contest(self, data={}):
required_args = [
{"name": "+id", "type": int},
{"name": "+group_id", "type": int},
{"name": "+setter_user_id", "type": int},
{"name": "+visible", "type": int},
{"name": "+title", "type": str},
{"name": "+description", "type": str, "xss": True},
{"name": "+register_start", "type": datetime.datetime},
{"name": "+register_end", "type": datetime.datetime},
{"name": "+start", "type": datetime.datetime},
{"name": "+end", "type": datetime.datetime},
{"name": "+freeze", "type": int},
{"name": "+type", "type": int},
]
err = form_validation(data, required_args)
if err:
return (err, None)
if not data["freeze"] or data["freeze"] == "":
data["freeze"] = 0
err, res = yield from self.get_contest(data)
if err:
return (err, None)
data.pop("id")
sql, param = self.gen_update_sql("contests", data)
yield self.db.execute(sql + " WHERE id=%s AND group_id=%s;", param + (res["id"], res["group_id"]))
# self.rs.delete('contest@%s'%str(res['id']))
return (None, res["id"])
def get_submission_list_count(self, data):
required_args = [{
'name': '+group_id',
'type': int,
}, {
'name': 'problem_id',
'type': int,
}, {
'name': 'account',
'type': str,
}, {
'name': 'verdict',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
sql = "SELECT count(*) FROM submissions as s, problems as p"
sql += " WHERE s.problem_id = p.id AND p.group_id=%s" % (
data['group_id'])
if data['problem_id']:
sql += " AND problem_id=%s " % (int(data['problem_id']))
if data['account']:
try:
user_id = (yield self.db.execute(
"SELECT id FROM users WHERE account=%s",
(data['account'], ))).fetchone()['id']
except:
user_id = 0
sql += " AND user_id=%s " % (user_id)
if data['verdict']:
sql += " AND s.verdict=%s" % (data['verdict'])
res = yield self.db.execute(sql)
return (None, res.fetchone()['count'])
def post_execute(self, data={}):
required_args = [{
'name': '+setter_user_id',
'type': int,
}, {
'name': '+lang',
'type': int,
}, {
'name': 'description',
'type': str,
}, {
'name': 'command',
'type': list,
}, {
'name': 'cm_mode',
'type': str,
}]
err = form_validation(data, required_args)
if err: return (err, None)
# self.rs.delete('execute_list')
command = data.pop('command')
sql, parma = self.gen_insert_sql("execute_types", data)
id = (yield self.db.execute(sql, parma)).fetchone()['id']
yield self.db.execute("DELETE FROM execute_steps WHERE execute_type_id=%s", (id,))
for x in command:
meta = {}
meta['command'] = x
meta['execute_type_id'] = id
sql, parma = self.gen_insert_sql("execute_steps", meta)
yield self.db.execute(sql, parma)
# self.rs.delete('execute@%s'%(str(id)))
return (None, id)
def get_contest(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
### new contest
if int(data['id']) == 0:
col = ['id', 'group_id', 'visible', 'title', 'description', 'setter_user_id', 'type', 'freeze']
res = { x: '' for x in col }
col = ['register_start', 'register_end', 'start', 'end']
res.update({ x: datetime.datetime.now() for x in col })
res['visible'] = 0
res['id'] = 0
res['problem'] = []
return (None, res)
# res = self.rs.get('contest@%s'%str(data['id']))
res = None
if not res:
res = yield self.db.execute('SELECT c.*, u.account as setter_user FROM contests as c, users as u WHERE c.setter_user_id=u.id AND c.id=%s;', (data['id'], ))
if res.rowcount == 0:
return ((404, 'No Contest ID'), None)
res = res.fetchone()
# self.rs.set('contest@%s'%str(data['id']), res)
err, res['problem'] = yield from self.get_contest_problem_list(data)
err, res['user'] = yield from self.get_contest_user(data)
return (None, res)
def post_submission(self, data):
required_args = [{
'name': '+problem_id',
'type': int,
}, {
'name': '+execute_type_id',
'type': int,
}, {
'name': '+user_id',
'type': int,
}, {
'name': '+ip',
'type': str,
}]
err = form_validation(data, required_args)
if err: return(err, None)
if data['code_file'] == None and len(data['plain_code']) == 0:
return ((400, 'No code'), None)
meta = { x['name']: data[x['name']] for x in required_args }
### check problem has execute_type
res = yield self.db.execute("SELECT * FROM map_problem_execute WHERE problem_id=%s and execute_type_id=%s", (data['problem_id'], data['execute_type_id'],))
if res.rowcount == 0:
return ((400, 'No execute type'), None)
err, data['execute'] = yield from Service.Execute.get_execute({'id': data['execute_type_id']})
### get file name and length
if data['code_file']:
meta['file_name'] = data['code_file']['filename']
meta['length'] = len(data['code_file']['body'])
else:
if data['plain_file_name'] is None:
data['plain_file_name'] = ''
if re.match('[\w\.]*', data['plain_file_name']).group(0) != data['plain_file_name']:
data['plain_file_name'] = ''
if data['plain_file_name'] != '':
meta['file_name'] = data['plain_file_name']
else:
meta['file_name'] = map_default_file_name[int(data['execute']['lang'])]
meta['length'] = len(data['plain_code'])
### save to db
sql, parma = self.gen_insert_sql("submissions", meta)
id = (yield self.db.execute(sql, parma)).fetchone()['id']
### save file
folder = '%s/data/submissions/%s/' % (config.DATAROOT, str(id))
file_path = '%s/%s' % (folder, meta['file_name'])
try: shutil.rmtree(folder)
except: pass
try: os.makedirs(folder)
except: pass
print(type(data['plain_code']))
try: print(type(data['code_file']['body']))
except: pass
if data['code_file']:
with open(file_path, 'wb+') as f:
f.write(data['code_file']['body'])
else:
with open(file_path, 'w+') as f:
f.write(data['plain_code'])
yield self.db.execute('INSERT INTO wait_submissions (submission_id) VALUES(%s);', (id,))
return (None, id)
def delete_testdata(self, data={}):
required_args = [{"name": "+id", "type": int}]
err = form_validation(data, required_args)
if err:
return (err, None)
# self.rs.delete('testdata@%s'%(str(data['id'])))
# self.rs.delete('testdata_list@problem@%s'%(data['problem_id']))
yield self.db.execute("DELETE FROM testdata WHERE id=%s", (data["id"],))
return (None, None)
def delete_bulletin(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
yield self.db.execute("DELETE FROM bulletins WHERE id=%s", (data['id'],))
return (None, None)
def get_bulletin_list_count(self, data={}):
required_args = [{
'name': '+group_id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute("SELECT COUNT(*) FROM bulletins WHERE group_id=%s", (data['group_id'],))
return (None, res.fetchone()['count'])
def get_contest_problem_list(self, data={}):
required_args = [{
'name': 'id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT p.id, p.title, m.score, m.penalty FROM map_contest_problem as m, problems as p WHERE p.id=m.problem_id AND m.contest_id=%s ORDER BY m.id ASC;', (data['id'],))
return (None, res.fetchall())
def get_inpublic_group_user(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT u.* FROM map_inpublic_group_user as m, users as u WHERE m.group_id=%s AND m.user_id=u.id;', (data['id'], ))
res = res.fetchall()
return (None, res)
def delete_user(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('DELETE FROM users WHERE id=%s;', (data['id'], ))
if res.rowcount == 0: return ((404, 'No such user'), None)
return (None, data['id'])
def get_contest_problem_list(self, data={}):
required_args = [{"name": "id", "type": int}]
err = form_validation(data, required_args)
if err:
return (err, None)
res = yield self.db.execute(
"SELECT p.id, p.title, m.score, m.penalty FROM map_contest_problem as m, problems as p WHERE p.id=m.problem_id AND m.contest_id=%s ORDER BY m.id ASC;",
(data["id"],),
)
return (None, res.fetchall())
def get_user_group_info(self, data={}):
required_args = [{
'name': '+id',
'type': int,
},]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute("SELECT g.* FROM groups as g, map_group_user as m where m.user_id=%s and g.id=m.group_id ORDER BY g.id", (data['id'],))
res = res.fetchall()
return (None, res)
def get_user_contest(self, data={}):
required_args = [{
'name': '+id',
'type': int,
},]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT contest_id FROM map_contest_user WHERE user_id=%s;', (data['id'],))
res = list({x['contest_id'] for x in res})
return (None, res)
def delete_problem_execute(self, data={}):
required_args = [{
'name': '+problem_id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
# self.rs.delete('execute@problem@%s' % str(data['problem_id']))
yield self.db.execute("DELETE FROM map_problem_execute WHERE problem_id=%s", (data['problem_id'],))
return (None, None)
def get_user_inpublic_group(self, data={}):
required_args = [{
'name': '+id',
'type': int,
},]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT * FROM map_inpublic_group_user WHERE user_id=%s;', (data['id'],))
res = res.fetchall()
return (None, res)
def get_user_power_info(self, data={}):
required_args = [{
'name': '+id',
'type': int,
},]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute("SELECT power from map_user_power WHERE user_id=%s", (data['id'],))
power = list({ x['power'] for x in res })
return (None, power)
def signup(self, data={}):
'''
data(dict): account(str), student_id(str), passwd(str), repasswd(str), email(str), school_id
return id(str)
'''
### check required arguemts
required_args = [{
'name': '+email',
'type': str,
'non_empty': True,
}, {
'name': '+account',
'type': str,
'non_empty': True,
}, {
'name': '+passwd',
'type': str,
'non_empty': True,
}, {
'name': '+repasswd',
'type': str,
'non_empty': True,
},]
err = form_validation(data, required_args)
if err: return (err, None)
### check data valadation
if data['passwd'] != data['repasswd']:
return ((400, 'Confirm Two Password'), None)
### check conflict
res = yield self.db.execute('SELECT * FROM users WHERE account = %s or email = %s', (data['account'], data['email']))
if res.rowcount != 0:
res = res.fetchone()
if res['email'] == data['email']:
return ((400, 'Email Exist'), None)
elif res['account'] == data['account']:
return ((400, 'Account Exist'), None)
### gen hashed passwd
hpasswd = self.hash_pwd(data['passwd'])
### gen sql query
data['passwd'] = hpasswd
data['token'] = self.gen_token(data['account'])
data.pop('repasswd')
sql, prama = self.gen_insert_sql('users', data)
res = yield self.db.execute(sql, prama)
res = yield self.db.execute('SELECT id FROM users '
'WHERE account = %s', (data['account'],))
if res.rowcount == 0:
return ((500, 'Something Wrong!!!'), None)
id = res.fetchone()["id"]
yield self.db.execute("INSERT INTO map_group_user (group_id, user_id) values (1, %s)", (id,))
# self.rs.delete('user_list_count')
return (None, str(id))
def post_group_addusers(self, data={}):
required_args = [
{
'name': '+group_id',
'type': int,
},
{
'name': '+user_ids',
'type': list,
},
{
'name': '+user_accounts',
'type': list,
},
]
err = form_validation(data, required_args)
if err: return (err, None)
data['user_ids'] = (x.strip() for x in data['user_ids']
if x.strip() != '')
data['user_accounts'] = (x.strip() for x in data['user_accounts']
if x.strip() != '')
# data['user_names'] = (x.strip() for x in data['user_names'] if x.strip() != '')
# data['user_student_ids'] = (x.strip() for x in data['user_student_ids'] if x.strip() != '')
print(data)
ids = set(data['user_ids'])
for account in data['user_accounts']:
res = yield self.db.execute(
'SELECT id FROM users WHERE account=%s;', (account, ))
try:
ids.add(res.fetchone()['id'])
except:
pass
# for name in data['user_names']:
# res = yield self.db.execute('SELECT id FROM users WHERE name=%s;', (name,))
# try: ids.add(res.fetchone()['id'])
# except: pass
# for student_id in data['user_student_ids']:
# res = yield self.db.execute('SELECT id FROM users WHERE student_id=%s;', (student_id,))
# try: ids.add(res.fetchone()['id'])
# except: pass
print(ids)
for id in ids:
try:
res = yield self.db.execute(
'INSERT INTO map_group_user (group_id, user_id) VALUES(%s, %s);',
(
data['group_id'],
id,
))
except Exception as e:
print(e)
return (None, None)
def post_tag(self, data={}):
required_args = [{
'name': '+tag',
'type': str,
}]
err = form_validation(data, required_args)
if err: return (err, None)
sql, param = self.gen_insert_sql('tags', data)
id = yield self.db.execute(sql, param)
id = id.fetchone()['id']
return (None, id)
def delete_testdata(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
# self.rs.delete('testdata@%s'%(str(data['id'])))
# self.rs.delete('testdata_list@problem@%s'%(data['problem_id']))
yield self.db.execute("DELETE FROM testdata WHERE id=%s", (data['id'],))
return (None, None)
def get_inpublic_group_user(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute(
'SELECT u.* FROM map_inpublic_group_user as m, users as u WHERE m.group_id=%s AND m.user_id=u.id;',
(data['id'], ))
res = res.fetchall()
return (None, res)
def get_school(self, data={}):
required_args = [{
'name': '+id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
res = yield self.db.execute('SELECT * FROM schools WHERE id=%s;',
(data['id'], ))
if res.rowcount == 0:
return ((404, 'No school ID'), None)
return (None, res.fetchone())
def delete_problem_execute(self, data={}):
required_args = [{
'name': '+problem_id',
'type': int,
}]
err = form_validation(data, required_args)
if err: return (err, None)
# self.rs.delete('execute@problem@%s' % str(data['problem_id']))
yield self.db.execute(
"DELETE FROM map_problem_execute WHERE problem_id=%s",
(data['problem_id'], ))
return (None, None)
def delete_tag(self, data={}):
required_args = ['id']
required_args = [{
'name': '+id',
'type': int,
}]
# err = self.check_required_args(required_args, data)
err = form_validation(data, required_args)
if err: return (err, None)
# self.rs.delete('tag@%s'%(str(data['id'])))
yield self.db.execute('DELETE FROM tags WHERE id=%s', (data['id'], ))
return (None, None)
