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)
Esempio n. 3
0
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)
Esempio n. 4
0
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()
Esempio n. 6
0
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))
Esempio n. 7
0
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))
Esempio n. 8
0
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))
Esempio n. 13
0
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'))
Esempio n. 14
0
def join_languages_name(subquery,
                        subLanguageIndex,
                        isDeleted=ENUMResources().const.FALSE):
    return dao.query(subquery,
                     Languages.languageName,
                     Languages.languageVersion).\
               outerjoin(Languages,
                         Languages.languageIndex == subLanguageIndex)
Esempio n. 15
0
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))
Esempio n. 16
0
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
Esempio n. 19
0
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)
Esempio n. 21
0
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
Esempio n. 22
0
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)
Esempio n. 23
0
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)
Esempio n. 25
0
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))
Esempio n. 28
0
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))