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 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 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 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 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 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 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 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 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 create(data):
if dbConnection.notExists(entity="user", identificator="email", value=data["email"]):
isAnonymous = False
if data["isAnonymous"]:
isAnonymous = data["isAnonymous"]
dbConnection.execQuery("INSERT user (username,about,name,email,isAnonymous) VALUES (%s,%s,%s,%s,%s)",(data["username"],data["about"],data["name"],data["email"], isAnonymous, ))
dataRequest={}
dataRequest["user"] = data["email"]
dataRequest["related"] = []
return details(dataRequest)
else:
raise Exception({"code":"USER EXISTS","message":"User with email '" + data["email"] + "' is already exists"})
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 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 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 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="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 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 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 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 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 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 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 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 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 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"],)))