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))