static public bool IfNicknameExist(string nickname)
{
using (var connection = ConnectionGetter.GetConnection())
{
return(connection.QueryFirst <int>("Select count(UserId) from User where Nickname=@nickname", new { nickname }) == 1);
}
}
static public User CheckPassword(string nickname, string password)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select * from user where nickname=@nickname and password=@password";
return(connection.QueryFirstOrDefault <User>(sql, new { nickname, password }));
}
}
static public bool AddUser(string nickname, string password)
{
using (var connection = ConnectionGetter.GetConnection())
{
string sql_str = "Insert into User(Nickname,Password) values(@nickname,@password)";
return(connection.Execute(sql_str, new { nickname, password }) == 1);
}
}
static public Problem GetProblem(string title)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select * from problem where title=@title";
return(connection.QueryFirstOrDefault <Problem>(sql, new { title }));
}
}
static public bool AddProblem(Problem problem)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "insert into problem(Title,MaxTime,MaxMemory) values(@Title,@MaxTime,@MaxMemory)";
return(connection.Execute(sql, problem) == 1);
}
}
static public List <Problem> GetProblems(int start_from, int length)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select * from problem limit @start_from,@length";
return(connection.Query <Problem>(sql, new { start_from, length }).ToList());
}
}
static public bool IfProblemExists(string title)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select count(ProblemId) from problem where Title=@title";
return(connection.QueryFirst <int>(sql, new { title }) == 1);
}
}
/// <summary>
///
/// </summary>
/// <param name="submission"></param>
/// <returns></returns>
/// 需要提供Status,Time,Memory,Uuid
static public bool SetResult(Submission submission)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "update Submission set Status=@Status,Time=@Time,Memory=@Memory where Uuid=@Uuid";
return(connection.Execute(sql, submission) == 1);
}
}
/// <summary>
///
/// </summary>
/// <param name="submission"></param>
/// <returns></returns>
/// 需要提供Status,Uuid
static public bool ChangeSubmissionStatus(Submission submission)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "update Submission set Status=@Status where Uuid=@Uuid";
return(connection.Execute(sql, submission) == 1);
}
}
/// <summary>
///
/// </summary>
/// <param name="submission"></param>
/// <returns></returns>
/// 需要提供UserId,ProblemId,Code,Uuid,Lang
static public bool AddSubmission(Submission submission)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "insert into submission(UserId,ProblemId,Code,Lang,Uuid) values(@UserId,@ProblemId,@Code,@Lang,@Uuid)";
return(connection.Execute(sql, submission) == 1);
}
}
static public User SearchUser(string nickname)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select User.UserId, Nickname, count(distinct ProblemId) as SolvedNumber" +
" from User left join (select * from submission where Status='Accept') as tmp " +
" on User.UserId=tmp.UserId where nickname=@nickname";
return(connection.QueryFirst <User>(sql, new { nickname }));
}
}
static public List <Submission> GetSubmissions(int start_from, int length)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select * from submission natural join (select UserId,Nickname from User) as tmp1 " +
" natural join (select ProblemId,Title from Problem) as tmp2 " +
" order by CreateTime DESC limit @start_from,@length";
return(connection.Query <Submission>(sql, new { start_from, length }).ToList());
}
}
static public List <User> GetUsers(int start_from, int length)
{
using (var connection = ConnectionGetter.GetConnection())
{
var sql = "select User.UserId, Nickname, count(distinct ProblemId) as SolvedNumber" +
" from User left join (select * from submission where Status='Accept') as tmp" +
" on User.UserId=tmp.UserId " +
" group by UserId order by SolvedNumber DESC limit @start_from,@length";
return(connection.Query <User>(sql, new { start_from, length }).ToList());
}
}