class RepliesOnBoard(Base):
__tablename__ = 'RepliesOnBoard'
boardReplyIndex = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
articleIndex = Column(INTEGER(unsigned=True),
ForeignKey(ArticlesOnBoard.articleIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
boardReplierIdIndex = Column(INTEGER(unsigned=True),
ForeignKey(Members.memberIdIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
boardReplyContent = Column(TEXT, nullable=False)
sumOfLikeCount = Column(INTEGER(unsigned=True), default=0, nullable=False)
boardReplierIp = Column(VARCHAR(20), nullable=False)
boardRepliedDate = Column(DATETIME, nullable=False)
isDeleted = Column(ENUM(ENUMResources().const.TRUE,
ENUMResources().const.FALSE),
default=ENUMResources().const.FALSE,
nullable=False)
class Problems(Base):
__tablename__ = 'Problems'
problemIndex = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
problemName = Column(VARCHAR(255), nullable=False, unique=True)
problemDifficulty = Column(ENUM(ENUMResources().const.BRONZE,
ENUMResources().const.SILVER,
ENUMResources().const.GOLD),
default=ENUMResources().const.BRONZE,
nullable=False)
solutionCheckType = Column(ENUM(ENUMResources().const.SOLUTION,
ENUMResources().const.CHECKER),
default=ENUMResources().const.CHECKER,
nullable=False)
numberOfTestCase = Column(INTEGER(unsigned=True),
default=0,
nullable=False)
limitedTime = Column(INTEGER(unsigned=True), default=3000,
nullable=False) #ms
limitedMemory = Column(INTEGER(unsigned=True),
default=1024,
nullable=False) #MB
problemPath = Column(TEXT, nullable=True)
isDeleted = Column(ENUM(ENUMResources().const.TRUE,
ENUMResources().const.FALSE),
default=ENUMResources().const.FALSE,
nullable=False)
class Members(Base):
__tablename__ = 'Members'
memberIdIndex = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
memberId = Column(VARCHAR(255), nullable=False, unique=True)
password = Column(VARCHAR(1024), nullable=False)
memberName = Column(VARCHAR(1024), nullable=False)
contactNumber = Column(VARCHAR(20), nullable=True)
emailAddress = Column(VARCHAR(1024), nullable=True)
detailInformation = Column(VARCHAR(1024), nullable=True)
authority = Column(SET(SETResources().const.ADMINISTRATOR,
SETResources().const.USER),
default=SETResources().const.USER,
nullable=False)
limitedUseDate = Column(DATETIME, default=None, nullable=True)
signedInDate = Column(DATETIME, nullable=False)
lastAccessDate = Column(DATETIME, nullable=True)
comment = Column(TEXT, nullable=True)
isDeleted = Column(ENUM(ENUMResources().const.TRUE,
ENUMResources().const.FALSE),
default=ENUMResources().const.FALSE,
nullable=False)
def code_reply_like_click(submissionReplyIndex):
# 댓글 좋아요
# 내가 Reply에 누른 좋아요 정보
try:
isReplyLike = select_replies_on_code_is_like(submissionReplyIndex,
memberIdIndex = session[SessionResources().const.MEMBER_ID_INDEX]).first().\
isLikeCancelled
except Exception:
# Non-Exist Case
isReplyLike = None
# 좋아요를 누른적 없을 때
if not isReplyLike:
# Insert Like
dao.add(
insert_likes_on_reply_of_code(
submissionReplyIndex,
memberIdIndex=session[
SessionResources().const.MEMBER_ID_INDEX]))
# Counting +1
LIKE_INCREASE = 1
else:
# 다시 좋아요 누를 때
if isReplyLike == ENUMResources().const.TRUE:
# Counting +1
LIKE_INCREASE = 1
isLikeCancelled = ENUMResources().const.FALSE
# 좋아요 취소 할 때
else: # if it's already exist then change the value of 'pushedLike'
# Counting -1
LIKE_INCREASE = -1
isLikeCancelled = ENUMResources().const.TRUE
# Update Like
update_replies_on_code_is_like(
submissionReplyIndex,
memberIdIndex=session[SessionResources().const.MEMBER_ID_INDEX],
isLikeCancelled=isLikeCancelled)
# Like or UnLIke
update_replies_on_code_like_counting(submissionReplyIndex,
LIKE_INCREASE=LIKE_INCREASE)
try:
dao.commit()
# return like count
try:
count = select_replies_on_code(submissionIndex = None,
submissionReplyIndex = submissionReplyIndex).first().\
sumOfLikeCount
except Exception:
count = 0
return Response(str(count))
except Exception:
dao.rollback()
return Response()
def article_like_click(articleIndex):
# 게시글 좋아요 Push
# 내가 게시글에 누른 좋아요 정보
try:
isLikeCancelled = select_article_is_like(articleIndex,
memberIdIndex = session[SessionResources().const.MEMBER_ID_INDEX]).first().\
isLikeCancelled
except Exception:
# Non-Exist Case
isLikeCancelled = None
# 좋아요를 누른적 없을 때
if not isLikeCancelled:
# Insert Like
dao.add(
insert_likes_on_board(
articleIndex,
memberIdIndex=session[
SessionResources().const.MEMBER_ID_INDEX]))
# Counting +1
LIKE_INCREASE = 1
else:
# 다시 좋아요 누를 때
if isLikeCancelled == ENUMResources().const.TRUE:
# Counting +1
LIKE_INCREASE = 1
isLikeCancelled = ENUMResources().const.FALSE
# 좋아요 취소 할 때
else: # if it's already exist then change the value of 'pushedLike'
# Counting -1
LIKE_INCREASE = -1
isLikeCancelled = ENUMResources().const.TRUE
# Update Like
update_article_is_like(
articleIndex,
memberIdIndex=session[SessionResources().const.MEMBER_ID_INDEX],
isLikeCancelled=isLikeCancelled)
# Article 좋아요 갯수 올리기
update_article_like_counting(articleIndex, LIKE_INCREASE=LIKE_INCREASE)
try:
dao.commit()
# return like count
try:
count = select_article(articleIndex = articleIndex).first().\
sumOfLikeCount
except Exception:
count = 0
return Response(str(count))
except Exception:
dao.rollback()
return Response()
def select_ranks(submissions):
# # Total Submission Count (Rank Page Server Error Exception)
submissionCount = dao.query(submissions.c.memberIdIndex,
func.sum(submissions.c.solutionCheckCount).label('solutionCheckCount')).\
group_by(submissions.c.memberIdIndex).\
subquery()
# 중복 제거푼 문제숫
sumOfSolvedProblemCount = dao.query(submissions.c.memberIdIndex).\
join(Submissions,
and_(Submissions.status == ENUMResources().const.SOLVED,
Submissions.submissionIndex == submissions.c.submissionIndex)).\
group_by(Submissions.submissionIndex).\
subquery()
sumOfSolvedProblemCount = dao.query(sumOfSolvedProblemCount,
func.count(sumOfSolvedProblemCount.c.memberIdIndex).label('sumOfSolvedProblemCount')).\
group_by(sumOfSolvedProblemCount.c.memberIdIndex).\
subquery()
#SubmitCount and SolvedCount Join
return dao.query(submissionCount.c.memberIdIndex,
submissionCount.c.solutionCheckCount,
sumOfSolvedProblemCount.c.sumOfSolvedProblemCount,
(sumOfSolvedProblemCount.c.sumOfSolvedProblemCount / submissionCount.c.solutionCheckCount * 100).label('solvedRate')).\
join(sumOfSolvedProblemCount,
and_(submissionCount.c.memberIdIndex == sumOfSolvedProblemCount.c.memberIdIndex))
def update_code_is_like(submissionIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnSubmission).\
filter(and_(LikesOnSubmission.submissionIndex == submissionIndex,
LikesOnSubmission.codeLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
def select_replies_on_code(submissionIndex,
submissionReplyIndex=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(RepliesOnSubmission).\
filter((RepliesOnSubmission.submissionIndex == submissionIndex if submissionIndex
else RepliesOnSubmission.submissionReplyIndex == submissionReplyIndex),
RepliesOnSubmission.isDeleted == isDeleted)
def select_problems(problemDifficulty=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(Problems).\
filter((Problems.problemDifficulty == problemDifficulty if problemDifficulty
else Problems.problemDifficulty != problemDifficulty),
Problems.isDeleted == isDeleted).\
order_by(Problems.problemIndex.asc())
def select_problem(problemIndex,
problemName=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(Problems).\
filter(and_((Problems.problemIndex == problemIndex if problemIndex
else Problems.problemName == problemName),
Problems.isDeleted == isDeleted))
def update_article_is_like(articleIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnBoard).\
filter(and_(LikesOnBoard.articleIndex == articleIndex,
LikesOnBoard.boardLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
def select_replies_on_board(articleIndex,
boardReplyIndex=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(RepliesOnBoard).\
filter(and_((RepliesOnBoard.articleIndex == articleIndex if articleIndex
else RepliesOnBoard.boardReplyIndex == boardReplyIndex),
RepliesOnBoard.isDeleted == isDeleted))
def select_solved_problems_counts(submissions):
submissions = dao.query(submissions).\
filter(submissions.c.status == ENUMResources().const.SOLVED).\
group_by(submissions.c.problemIndex).\
subquery()
return dao.query(
func.count(submissions.c.memberId).label('sumOfSolvedProblemCount'))
def join_languages_name(subquery,
subLanguageIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Languages.languageName,
Languages.languageVersion).\
outerjoin(Languages,
Languages.languageIndex == subLanguageIndex)
def update_recent_access_date(memberIdIndex,
lastAceesDate,
isDeleted=ENUMResources().const.FALSE):
''' doesnt need to add exception handler here? '''
dao.query(Members).\
filter(and_(Members.memberIdIndex == memberIdIndex,
Members.isDeleted == isDeleted)).\
update(dict(lastAccessDate = lastAceesDate))
def join_member_id(subquery,
subMemberIdIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Members.memberId).\
join(Members,
and_(Members.memberIdIndex == subMemberIdIndex,
Members.isDeleted == isDeleted))
def update_replies_on_board_is_like(
boardReplyIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnReplyOfBoard).\
filter(and_(LikesOnReplyOfBoard.boardReplyIndex == boardReplyIndex,
LikesOnReplyOfBoard.boardReplyLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
class Submissions(Base):
__tablename__ = 'Submissions'
submissionIndex = Column(INTEGER(unsigned=True),
ForeignKey(DataOfSubmissionBoard.submissionIndex,
onupdate='CASCADE',
ondelete='NO ACTION'),
primary_key=True,
autoincrement=False,
nullable=False)
submissionCount = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=False,
default=0,
nullable=False)
solutionCheckCount = Column(INTEGER(unsigned=True),
default=0,
nullable=False)
status = Column(ENUM(ENUMResources().const.NEVER_SUBMITTED,
ENUMResources().const.JUDGING,
ENUMResources().const.SOLVED,
ENUMResources().const.TIME_OVER,
ENUMResources().const.MEMORY_OVERFLOW,
ENUMResources().const.WRONG_ANSWER,
ENUMResources().const.COMPILE_ERROR,
ENUMResources().const.RUNTIME_ERROR,
ENUMResources().const.SERVER_ERROR),
default=ENUMResources().const.NEVER_SUBMITTED,
nullable=False)
score = Column(INTEGER(unsigned=True), default=0, nullable=False)
codeSubmissionDate = Column(DATETIME, nullable=False)
compileErrorMessage = Column(TEXT, default=None, nullable=True)
wrongTestCaseNumber = Column(INTEGER(unsigned=True),
default=None,
nullable=True)
runTime = Column(INTEGER(unsigned=True), default=0, nullable=False)
usedMemory = Column(INTEGER(unsigned=True), default=0, nullable=False)
usedLanguageIndex = Column(INTEGER(unsigned=True),
ForeignKey(Languages.languageIndex,
onupdate='CASCADE',
ondelete='NO ACTION'),
nullable=False)
sumOfSubmittedFileSize = Column(INTEGER(unsigned=True),
nullable=False) # Byte
def insert_to_submissions(submissionIndex, submissionCount, solutionCheckCount, usedLanguageIndex, sumOfSubmittedFileSize):
submissions = Submissions(submissionIndex = submissionIndex,
submissionCount = submissionCount,
solutionCheckCount = solutionCheckCount,
status = ENUMResources().const.JUDGING,
codeSubmissionDate = datetime.now(),
sumOfSubmittedFileSize = sumOfSubmittedFileSize,
usedLanguageIndex = usedLanguageIndex)
dao.add(submissions)
def join_problems_name(subquery,
subProblemIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Problems.problemName,
Problems.solutionCheckType,
Problems.problemPath).\
outerjoin(Problems,
Problems.problemIndex == subProblemIndex)
def select_members(Administrator=SETResources().const.ADMINISTRATOR,
User=SETResources().const.USER,
isDeleted=ENUMResources().const.FALSE):
# 자동 완성을 위한 모든 유저기록
members = dao.query(Members).\
filter(Members.authority == User,
Members.isDeleted == isDeleted)
return members
class LikesOnSubmission(Base):
__tablename__ = 'LikesOnSubmission'
submissionIndex = Column(INTEGER(unsigned=True),
ForeignKey(DataOfSubmissionBoard.submissionIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True,
autoincrement=False,
nullable=False)
codeLikerIdIndex = Column(INTEGER(unsigned=True),
ForeignKey(Members.memberIdIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True,
autoincrement=False,
nullable=False)
isLikeCancelled = Column(ENUM(ENUMResources().const.TRUE,
ENUMResources().const.FALSE),
default=ENUMResources().const.FALSE,
nullable=False)
class LikesOnReplyOfBoard(Base):
__tablename__ = 'LikesOnReplyOfBoard'
boardReplyIndex = Column(INTEGER(unsigned=True),
ForeignKey(RepliesOnBoard.boardReplyIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True,
autoincrement=False,
nullable=False)
boardReplyLikerIdIndex = Column(INTEGER(unsigned=True),
ForeignKey(Members.memberIdIndex,
onupdate='CASCADE',
ondelete='CASCADE'),
primary_key=True,
autoincrement=False,
nullable=False)
isLikeCancelled = Column(ENUM(ENUMResources().const.TRUE,
ENUMResources().const.FALSE),
default=ENUMResources().const.FALSE,
nullable=False)
def insert_problem(problemName,
problemDifficulty,
solutionCheckType,
limitedTime,
limitedMemory,
problemPath,
isDeleted=ENUMResources().const.FALSE):
return Problems(problemName=problemName,
problemDifficulty=problemDifficulty,
solutionCheckType=solutionCheckType,
limitedTime=limitedTime,
limitedMemory=limitedMemory,
problemPath=problemPath)
def manage_problem(problemLevel, pageNum, error=None):
try:
# Upload Problems Files
if request.method == 'POST':
if is_authority(session[SessionResources().const.AUTHORITY])[0]:
error = post_problem(request)
else:
error = LanguageResources().const.GetOutHere
# GET, POST 공통 사항
problems = select_problems(None if problemLevel == LanguageResources().
const.All[1] else problemLevel)
try:
count = select_count(problems.subquery().c.problemIndex).first().\
count
problemRecords = get_page_record(problems, pageNum=pageNum).all()
except Exception:
count = 0
problemRecords = []
return render_template('/manage_problem.html',
types=[
ENUMResources().const.SOLUTION,
ENUMResources().const.CHECKER
],
levels=[
LanguageResources().const.GoldLevel,
LanguageResources().const.SilverLevel,
LanguageResources().const.BronzeLevel
],
problemLevel=problemLevel,
problemRecords=problemRecords,
pages=get_page_pointed(pageNum, count),
error=error)
except Exception as e:
return unknown_error(e)
def select_sorted_articles(articlesOnBoard,
filterFindParameter=None,
articleType=ENUMResources().const.NOTICE,
isAll=False):
if articleType == ENUMResources().const.NOTICE:
# All Notice get
if isAll:
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType == ENUMResources().const.NOTICE).\
subquery()
# Filter Case
if filterFindParameter\
and filterFindParameter.filterCondition\
and filterFindParameter.filterCondition != ' ':
articlesOnBoard = search_articles(
articlesOnBoard, filterFindParameter).subquery()
# 5 Get notice
else:
# Server written notice get 2
notice = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType == articleType).\
order_by(articlesOnBoard.c.updateDate.desc()).\
limit(OtherResources().const.VIEW_NOTICE)
return notice
else:
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType != ENUMResources().const.NOTICE).\
subquery()
# Filter Case
if filterFindParameter\
and filterFindParameter.filterCondition\
and filterFindParameter.filterCondition != ' ':
articlesOnBoard = search_articles(articlesOnBoard,
filterFindParameter).subquery()
return dao.query(articlesOnBoard).\
order_by(articlesOnBoard.c.articleIndex.desc())
def update_article_modify(articleIndex,
problemIndex,
articleType,
articleParameter,
isDeleted=ENUMResources().const.FALSE):
dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.articleIndex == articleIndex,
ArticlesOnBoard.isDeleted == isDeleted).\
update(dict(problemIndex = problemIndex,
articleType = articleType,
title = articleParameter.title,
content = articleParameter.content,
updateIp = articleParameter.updateIp,
updateDate = articleParameter.updateDate))
def search_members(members,
filterFindParameter,
isDeleted=ENUMResources().const.FALSE):
# condition은 All, ID, Name로 나누어서 검새
if filterFindParameter.filterCondition == LanguageResources(
).const.All[1]: # Filters[0] is '모두'
members = dao.query(members).\
filter(or_(members.c.memberId == filterFindParameter.keyWord,
members.c.memberName.like('%' + filterFindParameter.keyWord + '%')),
members.c.isDeleted == isDeleted)
elif filterFindParameter.filterCondition == LanguageResources(
).const.ID[1]: # Filters[1] is ID
members = dao.query(members).\
filter(members.c.memberId == filterFindParameter.keyWord,
members.c.isDeleted == isDeleted)
else: # Filters[2] is 'NAme'
members = dao.query(members).\
filter(members.c.memberName.like('%'+ filterFindParameter.keyWord + '%'),
members.c.isDeleted == isDeleted)
return members
def select_server_notices(articleType=ENUMResources().const.NOTICE,
isDeleted=ENUMResources().const.FALSE):
return dao.query(ArticlesOnBoard).\
filter(and_(ArticlesOnBoard.articleType == articleType,
ArticlesOnBoard.isDeleted == isDeleted))
def update_replies_on_board_delete(boardReplyIndex,
isDeleted=ENUMResources().const.TRUE):
dao.query(RepliesOnBoard).\
filter(RepliesOnBoard.boardReplyIndex == boardReplyIndex).\
update(dict(isDeleted = isDeleted))