예제 #1
0
def unsubscribeThread(users_email, id):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "INSERT INTO subscribers (user_id, thread_id, subscribed) "
            "VALUES ((SELECT id FROM users WHERE email = %s), %s, FALSE) "
            "ON DUPLICATE KEY UPDATE subscribed=FALSE ",
            (users_email, int(id)),
        )
        connection.commit()
        cursor.execute(
            "SELECT "
            "threads.id AS thread, "
            "users.email AS user "
            "FROM threads JOIN subscribers ON threads.id = subscribers.thread_id "
            "JOIN users ON subscribers.user_id = users.id "
            "WHERE users.email = %s AND threads.id = %s AND subscribers.subscribed = FALSE ",
            (users_email, id),
        )
        thread = dictfetchone(cursor)
        cursor.close()
    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #2
0
def getAddedPost(forums_short_name, threads_id, users_email, date):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "posts.date AS date, "
            "forums.short_name AS forum, "
            "posts.id AS id, "
            "posts.isApproved AS isApproved, "
            "posts.isDeleted AS isDeleted, "
            "posts.isEdited AS isEdited, "
            "posts.isHighlighted AS isHighlighted, "
            "posts.isSpam AS isSpam, "
            "posts.message AS message, "
            "threads.id AS thread, "
            "users.email AS user "
            "FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id "
            "RIGHT JOIN forums ON threads.forum_id = forums.id "
            "JOIN users ON posts.user_id = users.id "
            "WHERE forums.short_name = %s AND threads.id = %s AND users.email=%s AND posts.date=%s""",
            (forums_short_name, threads_id, users_email, date))
        post = dictfetchone(cursor)
        cursor.close()
        post['date'] = post['date'].strftime("%Y-%m-%d %H:%M:%S")
        post['isApproved'] = bool(post['isApproved'])
        post['isEdited'] = bool(post['isEdited'])
        post['isHighlighted'] = bool(post['isHighlighted'])
        post['isSpam'] = bool(post['isSpam'])
        post['isDeleted'] = bool(post['isDeleted'])
    except Exception:
        raise
    else:
        return post
    finally:
        cursor.close()
예제 #3
0
def getAddedThread(forums_short_name, threads_slug):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "threads.date AS date, "
            "forums.short_name AS forum, "
            "threads.id AS id, "
            "threads.isClosed AS isClosed, "
            "threads.isDeleted AS isDeleted, "
            "threads.message AS message, "
            "threads.slug AS slug, "
            "threads.title AS title, "
            "users.email AS user "
            "FROM threads JOIN forums ON threads.forum_id = forums.id "
            "JOIN users ON threads.user_id = users.id "
            "WHERE forums.short_name = %s AND threads.slug = %s ",
            (forums_short_name, threads_slug),
        )
        thread = dictfetchone(cursor)
        cursor.close()
        if not thread:
            raise Exception("Thread doesn't exist")
        thread["isClosed"] = bool(thread["isClosed"])
        thread["isDeleted"] = bool(thread["isDeleted"])
        thread["date"] = thread["date"].strftime("%Y-%m-%d %H:%M:%S")
    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #4
0
def getDetailedForum(short_name, related):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "forums.id AS id, "
            "forums.name AS name, "
            "forums.short_name AS short_name, "
            "users.email AS user "
            "FROM forums JOIN users ON forums.user_id = users.id "
            "WHERE forums.short_name = %s """,
            (short_name,)
        )
        forum = dictfetchone(cursor)
        cursor.close()
        if not forum:
            raise Exception("Forum doesn't exists")
        if 'user' in related:
            from API.requests import user_requests
            forum['user'] = user_requests.getDetailedUser(forum['user'])
    except Exception:
        raise
    else:
        return forum
    finally:
        cursor.close()
예제 #5
0
def getDetailedPost(id, related):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "posts.date AS date, "
            "posts.dislikes AS dislikes, "
            "forums.short_name AS forum, "
            "posts.id AS id, "
            "posts.isApproved AS isApproved, "
            "posts.isDeleted AS isDeleted, "
            "posts.isEdited AS isEdited, "
            "posts.isHighlighted AS isHighlighted, "
            "posts.isSpam AS isSpam, "
            "posts.likes AS likes, "
            "posts.message AS message, "
            "posts.post_id AS parent, "
            "posts.likes - posts.dislikes AS points, "
            "posts.thread_id AS thread, "
            "users.email AS user "
            "FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id "
            "RIGHT JOIN forums ON threads.forum_id = forums.id "
            "JOIN users ON posts.user_id = users.id "
            "WHERE posts.id = %s """, (id,))
        post = dictfetchone(cursor)
        cursor.close()
        if not post:
            raise Exception("Post doesn't exist")
        if 'user' in related:
            from API.requests import user_requests
            post['user'] = user_requests.getDetailedUser(post['user'])
        if 'thread' in related:
            from API.requests import thread_requests
            post['thread'] = thread_requests.getDetailedThread(post['thread'],[])
        if 'forum' in related:
            from API.requests import forum_requests
            post['forum'] = forum_requests.getDetailedForum(post['forum'], [])

        post['isApproved'] = bool(post['isApproved'])
        post['isDeleted'] = bool(post['isDeleted'])
        post['isEdited'] = bool(post['isEdited'])
        post['isHighlighted'] = bool(post['isHighlighted'])
        post['isSpam'] = bool(post['isSpam'])
        post['date'] = post['date'].strftime("%Y-%m-%d %H:%M:%S")

    except Exception:
        raise
    else:
        return post
    finally:
        cursor.close()
예제 #6
0
def getDetailedThread(id, related):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "threads.date AS date, "
            "threads.dislikes AS dislikes, "
            "forums.short_name AS forum, "
            "threads.id AS id, "
            "threads.isClosed AS isClosed, "
            "threads.isDeleted AS isDeleted, "
            "threads.likes AS likes, "
            "threads.message AS message, "
            "threads.likes - threads.dislikes AS points, "
            "COUNT(posts.id) AS posts, "
            "threads.slug AS slug, "
            "threads.title AS title, "
            "users.email AS user "
            "FROM forums JOIN threads ON forums.id = threads.forum_id "
            "JOIN users ON threads.user_id = users.id "
            "JOIN posts ON posts.thread_id = threads.id "
            "WHERE threads.id = %s"
            "",
            (id,),
        )

        thread = dictfetchone(cursor)
        cursor.close()
        thread["isClosed"] = bool(thread["isClosed"])
        thread["isDeleted"] = bool(thread["isDeleted"])
        thread["date"] = thread["date"].strftime("%Y-%m-%d %H:%M:%S")

        if "user" in related:
            from API.requests import user_requests

            thread["user"] = user_requests.getDetailedUser(thread["user"])

        if "forum" in related:
            from API.requests import forum_requests

            thread["forum"] = forum_requests.getDetailedForum(thread["forum"], [])

    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #7
0
def removePost(id):
    cursor = connection.cursor()
    try:
        cursor.execute("UPDATE posts SET isDeleted = TRUE WHERE id = %s", (id,))
        connection.commit()
        cursor.execute("SELECT id AS post FROM posts WHERE id = %s AND isDeleted = TRUE", (id,))
        thread = dictfetchone(cursor)
        cursor.close()
        if not thread:
            raise Exception("Post doesn't exist")
    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #8
0
def restorePost(id):
    cursor = connection.cursor()
    try:
        cursor.execute("UPDATE posts SET isDeleted = FALSE WHERE id = %s", (id,))
        connection.commit()
        cursor.execute("SELECT id AS post FROM posts WHERE id = %s AND isDeleted = FALSE", (id,))
        post = dictfetchone(cursor)
        cursor.close()
        if not post:
            raise Exception("Post doesn't exist")
    except Exception:
        raise
    else:
        return post
    finally:
        cursor.close()
예제 #9
0
def restoreThread(id):
    cursor = connection.cursor()
    try:
        cursor.execute("UPDATE threads SET isDeleted = FALSE WHERE id = %s", (id,))
        connection.commit()
        cursor.execute("SELECT id AS thread FROM threads WHERE id = %s AND isDeleted = FALSE", (id,))
        thread = dictfetchone(cursor)
        cursor.close()
        if not thread:
            raise Exception("Thread doesn't exist")
    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #10
0
def votePost(id, vote):
    cursor = connection.cursor()
    try:
        if vote == 1:
            cursor.execute("UPDATE posts SET likes = likes + 1 WHERE id = %s ", (id,))
        elif vote == -1:
            cursor.execute("UPDATE posts SET dislikes = dislikes + 1 WHERE id = %s ", (id,))
        else:
            raise Exception("Wrong vote value")
        cursor.execute("SELECT id AS post FROM posts WHERE id = %s ", (id,))
        post = dictfetchone(cursor)
        cursor.close()
    except Exception:
        raise
    else:
        return post
    finally:
        cursor.close()
예제 #11
0
def voteThread(id, vote):
    cursor = connection.cursor()
    try:
        if vote == 1:
            cursor.execute("UPDATE threads SET likes = likes + 1 WHERE id = %s ", (id,))
        elif vote == -1:
            cursor.execute("UPDATE threads SET dislikes = dislikes + 1 WHERE id = %s ", (id,))
        else:
            raise Exception("Wrong vote value")
        cursor.execute("SELECT id AS thread FROM threads WHERE id = %s ", (id,))
        thread = dictfetchone(cursor)
        cursor.close()
    except Exception:
        raise
    else:
        return thread
    finally:
        cursor.close()
예제 #12
0
def getAddedForum(short_name):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "SELECT "
            "forums.id AS id, "
            "forums.name AS name, "
            "forums.short_name as short_name, "
            "users.email AS user "
            "FROM forums JOIN users ON forums.user_id = users.id "
            "WHERE forums.short_name = %s """,
            (short_name,))
        forum = dictfetchone(cursor)
        cursor.close()
        if not forum:
            raise Exception("Forum doesn't exist")
    except Exception:
        raise
    else:
        return forum
    finally:
        cursor.close()