Example #1
0
def add_forum(name, short_name, user):
    verify(table_name="Users", param="email", val=user)
    forum = selectQuery('SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
    if len(forum) == 0:
        ins_upd_delQuery('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user, ))
        forum = selectQuery('SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
    return forums_info(forum)
Example #2
0
def add_threads(forum, title, isClosed, user, date, message, slug, optional):
    verify(table_name="Users", param="email", val=user)
    verify(table_name="Forums", param="short_name", val=forum)
    isDeleted = 0
    if "isDeleted" in optional:
        isDeleted = optional["isDeleted"]
    thread = selectQuery(
        'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
        'FROM Threads WHERE slug = %s', (slug, )
    )
    if len(thread) == 0:
        ins_upd_delQuery(
            'INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted) '
            'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
            (forum, title, isClosed, user, date, message, slug, isDeleted, )
        )
        thread = selectQuery(
            'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
            'FROM Threads WHERE slug = %s', (slug, )
        )
    response = threads_info(thread)
    del response["dislikes"]
    del response["likes"]
    del response["points"]
    del response["posts"]
    return response
Example #3
0
def remove_follows(email1, email2):
    follows = selectQuery('SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
    if len(follows) != 0:
        ins_upd_delQuery('DELETE FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
    else:
        raise Exception("No such following")
    return users.details(email1)
Example #4
0
def add_subscriptions(email, thread_id):
    verify(table_name="Threads", param="id", val=thread_id)
    verify(table_name="Users", param="email", val=email)
    subscription = selectQuery(
        'select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
    )
    if len(subscription) == 0:
        ins_upd_delQuery('INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, ))
        subscription = selectQuery(
            'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
        )
    response = {
        "thread": subscription[0][0],
        "user": subscription[0][1]
    }
    return response
Example #5
0
def details(short_name, related):
    forum = selectQuery('SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
    if len(forum) == 0:
        raise ("No forum with exists short_name=" + short_name)
    forum = forums_info(forum)
    if "user" in related:
        forum["user"] = users.details(forum["user"])
    return forum
Example #6
0
def add_follows(email1, email2):
    verify(table_name="Users", param="email", val=email1)
    verify(table_name="Users", param="email", val=email2)
    if email1 == email2:
        raise Exception("User with email=" + email1 + " can't follow himself")
    follows = selectQuery('SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
    if len(follows) == 0:
        ins_upd_delQuery('INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (email1, email2, ))
    user = users.details(email1)
    return user
Example #7
0
def create(date, thread, message, user, forum, opt):
    verify(table_name="Threads", param="id", val=thread)
    verify(table_name="Forums", param="short_name", val=forum)
    verify(table_name="Users", param="email", val=user)
    if len(selectQuery("SELECT Threads.id FROM Threads,Forums WHERE Threads.forum = Forums.short_name "
                                "AND Threads.forum = %s AND Threads.id = %s", (forum, thread, ))) == 0:
        raise Exception("no thread with id = " + thread + " in forum " + forum)
    if "parent" in opt:
        if len(selectQuery("SELECT Posts.id FROM Posts, Threads WHERE Threads.id = Posts.thread "
                             "AND Posts.id = %s AND Threads.id = %s", (opt["parent"], thread, ))) == 0:
            raise Exception("No post with id = " + opt["parent"])
    query = "INSERT INTO Posts (message, user, forum, thread, date"
    values = "(%s, %s, %s, %s, %s"
    parameters = [message, user, forum, thread, date]
    for param in opt:
        query += ", "+param
        values += ", %s"
        parameters.append(opt[param])
    query += ") VALUES " + values + ")"
    update_thread_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
    con = Connector()
    con = con.connect()
    con.autocommit(False)
    with con:
        cursor = con.cursor()
        try:
            con.begin()
            cursor.execute(update_thread_posts, (thread, ))
            cursor.execute(query, parameters)
            con.commit()
        except Exception as e:
            con.rollback()
            raise Exception("Database error: " + e.message)
        #DatabaseConnection.connection.commit()
        post_id = cursor.lastrowid
        cursor.close()
    con.close()
    post = posts_query(post_id)
    del post["dislikes"]
    del post["likes"]
    del post["parent"]
    del post["points"]
    return post
Example #8
0
def followers(email, type):
    where = "followee"
    if type == "follower":
        where = "followee"
    if type == "followee":
        where = "follower"
    f_list = selectQuery(
        "SELECT " + type + " FROM Followers, Users WHERE Users.email = Followers." + type +
        " AND " + where + " = %s ", (email, )
    )
    return tuple_list(f_list)
Example #9
0
def details(id, related):
    thread = selectQuery(
        'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
        'FROM Threads WHERE id = %s', (id, )
    )
    if len(thread) == 0:
        raise Exception('No thread exists with id=' + str(id))
    thread = threads_info(thread)

    if "user" in related:
        thread["user"] = users.details(thread["user"])
    if "forum" in related:
        thread["forum"] = forums.details(short_name=thread["forum"], related=[])
    return thread
Example #10
0
def remove_subscriptions(email, thread_id):
    verify(table_name="Threads", param="id", val=thread_id)
    verify(table_name="Users", param="email", val=email)
    subscriptions = selectQuery(
        'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
    )
    if len(subscriptions) == 0:
        raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
    ins_upd_delQuery('DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
    response = {
        "thread": subscriptions[0][0],
        "user": subscriptions[0][1]
    }
    return response
Example #11
0
def list_users(short_name, opt):
    verify(table_name="Forums", param="short_name", val=short_name)

    query = "SELECT distinct email FROM Users, Posts, Forums WHERE Posts.user = Users.email " \
            " and Forums.short_name = Posts.forum and Posts.forum = %s "
    if "since_id" in opt:
        query += " AND Users.id >= " + str(opt["since_id"])
    if "order" in opt:
        query += " ORDER BY Users.id " + opt["order"]
    if "limit" in opt:
        query += " LIMIT " + str(opt["limit"])
    users_tuple = selectQuery(query, (short_name, ))
    user_array = []
    for user in users_tuple:
        user = user[0]
        user_array.append(users.details(user))
    return user_array
Example #12
0
def followers_list(email, type, params):
    verify(table_name="Users", param="email", val=email)
    if type == "follower":
        where = "followee"
    if type == "followee":
        where = "follower"
    query = "SELECT "+type+" FROM Followers, Users WHERE Users.email = Followers."+type+\
            " AND "+where+" = %s "
    if "since_id" in params:
        query += " AND Users.id >= "+str(params["since_id"])
    if "order" in params:
        query += " ORDER BY Users.name "+params["order"]
    else:
        query += " ORDER BY Users.name DESC "
    if "limit" in params:
        query += " LIMIT "+str(params["limit"])
    followers_ids_tuple = selectQuery(query=query, params=(email, ))
    followers_array = []
    for id in followers_ids_tuple:
        id = id[0]
        followers_array.append(users.details(email=id))
    return followers_array
Example #13
0
def threads_list(table_, parametr, related, params):
    if table_ == "forum":
        verify(table_name="Forums", param="short_name", val=parametr)
    if table_ == "user":
        verify(table_name="Users", param="email", val=parametr)
    query = "SELECT id FROM Threads WHERE " + table_ + " = %s "
    parameters = [parametr]
    if "since" in params:
        query += " AND date >= %s"
        parameters.append(params["since"])
    if "order" in params:
        query += " ORDER BY date " + params["order"]
    else:
        query += " ORDER BY date DESC "
    if "limit" in params:
        query += " LIMIT " + str(params["limit"])
    thread_ids_tuple = selectQuery(query=query, params=parameters)
    thread_array = []
    for id in thread_ids_tuple:
        id = id[0]
        thread_array.append(details(id=id, related=related))
    return thread_array
Example #14
0
def select_user(query, params):
    return selectQuery(query, params)
Example #15
0
def users_subscriptions(email):
    subscriptions_array = []
    subscriptions = selectQuery('select thread FROM Subscriptions WHERE user = %s', (email, ))
    for el in subscriptions:
        subscriptions_array.append(el[0])
    return subscriptions_array
Example #16
0
def select_posts(query, params):
    return selectQuery(query, params)
Example #17
0
def verify(table_name, param, val):
    if not len(selectQuery('SELECT id FROM ' + table_name + ' WHERE ' + param + ' = %s', (val, ))):
        raise Exception("Impossible to do something with element")
    return