def listUsers(data): dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) query = "SELECT email FROM post JOIN user ON post.user=user.email WHERE forum=%s" params = (data["forum"],) if "since_id" in data and data["since_id"]: if type(data["since_id"]) == list: query += " AND user.id>=" + data["since_id"][0] + " AND user.id<=" + data["since_id"][1] else: if type(data["since_id"]) == unicode: query += " AND user.id>=" + data["since_id"] else: raise Exception({"code":"UNCORRECT REQUEST","message":"since_id can't contains more than 2 elements"}) query += " GROUP BY email" if "order" in data and data["order"]: query += " ORDER BY user.name " + data["order"] else: query += " ORDER BY user.name DESC" if "limit" in data and data["limit"]: query += " LIMIT " + data["limit"] userEmails = dbConnection.execQuery(query,params) users = [] if userEmails: userEmails = sum(userEmails,()) dataRequest = {} dataRequest["related"] = [] for userEmail in userEmails: dataRequest["user"] = userEmail users.append(api.dbOperations.user.details(dataRequest)) return users
def update(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) dbConnection.execQuery("UPDATE user SET about=%s,name=%s WHERE email=%s;", (data["about"],data["name"],data["user"], )) dataRequest={} dataRequest["user"] = data["user"] dataRequest["related"] = [] return details(dataRequest)
def list(data): entity = None if "forum" in data and data["forum"]: dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) entity = "forum" if "user" in data and data["user"]: dbConnection.exists(entity="user", identificator="email", value=data["user"]) entity = "user" query = "SELECT id FROM thread WHERE " + entity + "=%s" params = (data[entity],) if "since" in data and data["since"]: query += " AND date>=%s" params += (data["since"],) if "order" in data and data["order"]: query += " ORDER BY date " + data["order"] else: query += " ORDER BY date DESC" if "limit" in data and data["limit"]: query += " LIMIT " + data["limit"] threadIds = dbConnection.execQuery(query,params) threads = [] if threadIds: threadIds = sum(threadIds,()) dataRequest = {} if "related" in data: dataRequest["related"] = data["related"] else: dataRequest["related"] = [] for threadId in threadIds: dataRequest["thread"] = threadId threads.append(api.dbOperations.thread.details(dataRequest)) return threads
def update(data): dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) dbConnection.execQuery("UPDATE thread SET message = %s, slug = %s WHERE id = %s;",(data["message"], data["slug"], data["thread"], )) dataRequest = {} dataRequest["thread"] = data["thread"] dataRequest["related"] = [] return details(dataRequest)
def listFollowing(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) query = "SELECT email_following FROM follow JOIN user ON follow.email_following=user.email WHERE email_follower=%s" params = (data["user"],) if "since_id" in data and data["since_id"]: if type(data["since_id"]) == list: query += " AND user.id>=" + data["since_id"][0] + " AND user.id<=" + data["since_id"][1] else: if type(data["since_id"]) == unicode: query += " AND user.id>=" + data["since_id"] else: raise Exception({"code":"UNCORRECT REQUEST","message":"since_id can't contains more than 2 elements"}) if "order" in data and data["order"]: query += " ORDER BY user.name " + data["order"] else: query += " ORDER BY user.name DESC" if "limit" in data and data["limit"]: query += " LIMIT " + data["limit"] followingsEmails = dbConnection.execQuery(query,params) followings = [] if followingsEmails: followingsEmails = sum(followingsEmails,()) dataRequest = {} dataRequest["related"] = [] for followingsEmail in followingsEmails: dataRequest["user"] = followingsEmail followings.append(api.dbOperations.user.details(dataRequest)) return followings
def unsubscribe(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) if (dbConnection.execQuery("SELECT COUNT(*) FROM subscribe WHERE email_subscriber = %s AND id_subscribing = %s;",(data["user"], data["thread"], )))[0][0]: subscribe = dbConnection.execQuery("SELECT id_subscribing,email_subscriber FROM subscribe WHERE id_subscribing = %s AND email_subscriber = %s;",(data["thread"], data["user"], )) dbConnection.execQuery("DELETE FROM subscribe WHERE email_subscriber = %s AND id_subscribing = %s;",(data["user"],data["thread"], )) return OrderedDict(zip(("thread","user"),subscribe[0]))
def create(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) if dbConnection.notExists(entity="forum", identificator="short_name", value=data["short_name"]): dbConnection.execQuery("INSERT forum (name,short_name,user) VALUES (%s,%s,%s)",(data["name"],data["short_name"],data["user"], )) dataRequest={} dataRequest["forum"] = data["short_name"] dataRequest["related"] = [] return details(dataRequest)
def update(data): dbConnection.exists(entity="post", identificator="id", value=data["post"]) message = dbConnection.execQuery("SELECT message FROM post WHERE id=%s;", (data["post"], ))[0][0] if message != data["message"]: dbConnection.execQuery("UPDATE post SET message = %s, isEdited = True WHERE id = %s;",(data["message"], data["post"], )) dataRequest = {} dataRequest["post"] = data["post"] dataRequest["related"] = [] return details(dataRequest)
def restore(data): dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) dbConnection.execQuery("UPDATE thread SET isDeleted=False WHERE id = %s;", (data["thread"], )) postsIds = dbConnection.execQuery("SELECT id FROM post WHERE thread = %s;", (data["thread"], )) dataRequest={} for postId in postsIds: dataRequest["post"] = postId dbConnection.execQuery("UPDATE post SET isDeleted = False WHERE id = %s",(dataRequest["post"], )) return OrderedDict(zip(("thread",),(data["thread"],)))
def unfollow(data): dbConnection.exists(entity="user", identificator="email", value=data["follower"]) dbConnection.exists(entity="user", identificator="email", value=data["followee"]) if dbConnection.execQuery("SELECT * FROM follow WHERE email_follower = %s AND email_following = %s;",(data["follower"],data["followee"], )): dbConnection.execQuery("DELETE FROM follow WHERE email_follower = %s AND email_following = %s;",(data["follower"],data["followee"], )) dataRequest={} dataRequest["user"] = data["follower"] dataRequest["related"] = [] return details(dataRequest)
def details(data): dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) forum = dbConnection.execQuery("SELECT id,name,short_name,user FROM forum WHERE short_name=%s",(data["forum"], )) forum = OrderedDict(zip(fields,forum[0])) if "user" in data["related"]: dataRequest = {} dataRequest["user"] = forum["user"] forum["user"] = api.dbOperations.user.details(dataRequest) return dbConnection.fieldsToBoolean(forum)
def follow(data): dbConnection.exists(entity="user", identificator="email", value=data["follower"]) dbConnection.exists(entity="user", identificator="email", value=data["followee"]) if not dbConnection.execQuery("SELECT * FROM follow WHERE email_follower = %s AND email_following = %s;",(data["follower"],data["followee"], )): dbConnection.execQuery("INSERT follow (email_follower,email_following) VALUES (%s,%s)",(data["follower"],data["followee"], )) dataRequest={} dataRequest["user"] = data["follower"] dataRequest["related"] = [] return details(dataRequest)
def create(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) isDeleted = "false" if data["isDeleted"]: isDeleted = data["isDeleted"] dataRequest={} dataRequest["thread"] = dbConnection.execQuery("INSERT thread (forum,title,isClosed,user,date,message,slug,isDeleted) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",(data["forum"],data["title"],data["isClosed"],data["user"],data["date"],data["message"],data["slug"],isDeleted, )) dataRequest["related"] = [] return details(dataRequest)
def vote(data): dbConnection.exists(entity="post", identificator="id", value=data["post"]) if data["vote"] == 1: dbConnection.execQuery("UPDATE post SET likes = likes + 1, points = points + 1 WHERE id=%s;",(data["post"], )) else: dbConnection.execQuery("UPDATE post SET dislikes = dislikes + 1, points = points - 1 WHERE id=%s;",(data["post"], )) dataRequest = {} dataRequest["post"] = data["post"] dataRequest["related"] = [] return details(dataRequest)
def details(data): dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) forum = dbConnection.execQuery( "SELECT id,name,short_name,user FROM forum WHERE short_name=%s", (data["forum"], )) forum = OrderedDict(zip(fields, forum[0])) if "user" in data["related"]: dataRequest = {} dataRequest["user"] = forum["user"] forum["user"] = api.dbOperations.user.details(dataRequest) return dbConnection.fieldsToBoolean(forum)
def details(data): dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) thread = dbConnection.execQuery("SELECT date,dislikes,forum,id,isClosed,isDeleted,likes,message,points,posts,slug,title,user FROM thread WHERE id=%s",(data["thread"], )) thread = OrderedDict(zip(fields,thread[0])) if "user" in data["related"]: dataRequest = {} dataRequest["user"] = thread["user"] dataRequest["related"] = [] thread["user"] = api.dbOperations.user.details(dataRequest) if "forum" in data["related"]: dataRequest = {} dataRequest["forum"] = thread["forum"] dataRequest["related"] = [] thread["forum"] = api.dbOperations.forum.details(dataRequest) thread["date"] = thread["date"].strftime("%Y-%m-%d %H:%M:%S") return dbConnection.fieldsToBoolean(thread)
def create(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) if dbConnection.notExists(entity="forum", identificator="short_name", value=data["short_name"]): dbConnection.execQuery( "INSERT forum (name,short_name,user) VALUES (%s,%s,%s)", ( data["name"], data["short_name"], data["user"], )) dataRequest = {} dataRequest["forum"] = data["short_name"] dataRequest["related"] = [] return details(dataRequest)
def details(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) user = dbConnection.execQuery("SELECT about,email,id,isAnonymous,name,username FROM user WHERE email = %s;",(data["user"], )) user = user[0] userList = list(user) userList.insert(2,[]) userList.insert(3,[]) userList.insert(7,[]) user = tuple(userList) user = OrderedDict(zip(fields,user)) followers = dbConnection.execQuery("SELECT email_follower FROM follow where email_following = %s;",(data["user"], )) following = dbConnection.execQuery("SELECT email_following FROM follow where email_follower = %s;",(data["user"], )) subscriptions = dbConnection.execQuery("SELECT id_subscribing FROM subscribe where email_subscriber = %s;",(data["user"], )) if followers: user["followers"] = sum(followers,()) if following: user["following"] = sum(following,()) if subscriptions: user["subscriptions"] = sum(subscriptions,()) return dbConnection.fieldsToBoolean(user)
def details(data): dbConnection.exists(entity="post", identificator="id", value=data["post"]) post = dbConnection.execQuery("SELECT date,dislikes,forum,id,isApproved,isDeleted,isEdited,isHighlighted,isSpam,likes,message,parent,points,thread,user FROM post WHERE id=%s",(data["post"], )) post = OrderedDict(zip(fields,post[0])) if "user" in data["related"]: dataRequest = {} dataRequest["user"] = post["user"] dataRequest["related"] = [] post["user"] = api.dbOperations.user.details(dataRequest) if "forum" in data["related"]: dataRequest = {} dataRequest["forum"] = post["forum"] dataRequest["related"] = [] post["forum"] = api.dbOperations.forum.details(dataRequest) if "thread" in data["related"]: dataRequest = {} dataRequest["thread"] = post["thread"] dataRequest["related"] = [] post["thread"] = api.dbOperations.thread.details(dataRequest) post["date"] = post["date"].strftime("%Y-%m-%d %H:%M:%S") return dbConnection.fieldsToBoolean(post)
def create(data): dbConnection.exists(entity="user", identificator="email", value=data["user"]) dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) if not (data["forum"] == dbConnection.execQuery("SELECT forum FROM thread WHERE id = %s;", (data["thread"], ))[0][0]): raise Exceptions({"code":"INVALID REQUEST","message":"Forum with short_name '" + data["forum"] + "' doesn't contain thread with id '" + str(data["thread"]) + "'"}) isApproved = False isDeleted = False isEdited = False isHighlighted = False isSpam = False parent = None if "isApproved" in data and data["isApproved"]: isApproved = data["isApproved"] if "isDeleted" in data and data["isDeleted"]: isDeleted = data["isDeleted"] if "isEdited" in data and data["isEdited"]: isEdited = data["isEdited"] if "isHighlighted" in data and data["isHighlighted"]: isHighlighted = data["isHighlighted"] if "isSpam" in data and data["isSpam"]: isSpam = data["isSpam"] if "parent" in data and data["parent"]: parent = data["parent"] path = None if parent: path = dbConnection.execQuery("SELECT CONCAT((SELECT path FROM post WHERE id = %s),'.',(SELECT COUNT(*)+1 FROM post WHERE parent = %s AND thread = %s));",(parent,parent,data["thread"], ))[0][0] else: path = dbConnection.execQuery("SELECT COUNT(*)+1 FROM post WHERE ISNULL(parent) AND thread = %s;",(data["thread"], ))[0][0] dataRequest={} dataRequest["post"] = dbConnection.execQuery("INSERT post (date,forum,isApproved,isDeleted,isEdited,isHighlighted,isSpam,message,parent,thread,user,path) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(data["date"],data["forum"],isApproved,isDeleted,isEdited,isHighlighted,isSpam,data["message"],parent,data["thread"],data["user"],path, )) dbConnection.execQuery("UPDATE thread SET posts = posts + 1 WHERE id = %s;", (data["thread"], )) dataRequest["related"] = [] return details(dataRequest)
def listUsers(data): dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) query = "SELECT email FROM post JOIN user ON post.user=user.email WHERE forum=%s" params = (data["forum"], ) if "since_id" in data and data["since_id"]: if type(data["since_id"]) == list: query += " AND user.id>=" + data["since_id"][ 0] + " AND user.id<=" + data["since_id"][1] else: if type(data["since_id"]) == unicode: query += " AND user.id>=" + data["since_id"] else: raise Exception({ "code": "UNCORRECT REQUEST", "message": "since_id can't contains more than 2 elements" }) query += " GROUP BY email" if "order" in data and data["order"]: query += " ORDER BY user.name " + data["order"] else: query += " ORDER BY user.name DESC" if "limit" in data and data["limit"]: query += " LIMIT " + data["limit"] userEmails = dbConnection.execQuery(query, params) users = [] if userEmails: userEmails = sum(userEmails, ()) dataRequest = {} dataRequest["related"] = [] for userEmail in userEmails: dataRequest["user"] = userEmail users.append(api.dbOperations.user.details(dataRequest)) return users
def list(data): entity = None if "forum" in data: dbConnection.exists(entity="forum", identificator="short_name", value=data["forum"]) entity = "forum" if "thread" in data: dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) entity = "thread" if "user" in data: dbConnection.exists(entity="user", identificator="email", value=data["user"]) entity = "user" query = "SELECT id FROM post WHERE " + entity + "=%s" params = (data[entity],) if "since" in data and data["since"]: query += " AND date>=%s" params += (data["since"], ) if "sort" in data and data["sort"]: if sort == "flat": query += " ORDER BY date" else: if sort == "tree": query += " ORDER BY path" else: if sort == "parent_tree": query += " ORDER BY path" else: raise Exception({"code":"INVALID REQUEST","message":"Invalid method of sorting '" + sort + "'"}) else: query += " ORDER BY date" if "order" in data and data["order"]: query += " " + data["order"] else: query += " DESC" if "limit" in data and data["limit"]: query += " LIMIT " + data["limit"] postIds = dbConnection.execQuery(query,params) posts = [] if postIds: postIds = sum(postIds,()) dataRequest = {} if "related" in data: dataRequest["related"] = data["related"] else: dataRequest["related"] = [] for postId in postIds: dataRequest["post"] = postId posts.append(api.dbOperations.post.details(dataRequest)) return posts
def open(data): dbConnection.exists(entity="thread", identificator="id", value=data["thread"]) dbConnection.execQuery("UPDATE thread SET isClosed=False WHERE id = %s;", (data["thread"], )) return OrderedDict(zip(("thread",),(data["thread"],)))
def restore(data): dbConnection.exists(entity="post", identificator="id", value=data["post"]) if dbConnection.execQuery("SELECT isDeleted FROM post WHERE id = %s;", (data["post"], ))[0][0]: dbConnection.execQuery("UPDATE post SET isDeleted=false WHERE id = %s;", (data["post"], )) dbConnection.execQuery("UPDATE thread SET posts = posts + 1 WHERE id = (SELECT thread FROM post WHERE id = %s);", (data["post"], )) return OrderedDict(zip(("post",),(data["post"],)))