def listPostsInForum(request):
cursor = connection.cursor()
shortName = request.GET['forum']
since = request.GET.get('since',None)
limit = request.GET.get('limit',None)
order = request.GET.get('order','DESC')#sort order (by date)
related = request.GET.getlist('related',[])#Possible values: ['thread', 'forum', 'user']. Default: []
query = '''SELECT idPost AS id,forum,idThread AS thread,user,parent,CAST(datePost AS CHAR) AS `date`,message,
isEdited,isDeleted,isSpam,isHighlighted,isApproved,likes,dislikes, likes - dislikes AS points
FROM Post
WHERE forum = '%s'
'''%(shortName,)
if since is not None:
query += "AND `datePost` >= '%s' "%since
if order is not None:
query += "ORDER BY datePost %s"%order
if limit is not None:
query += " LIMIT %d "%int(limit)
cursor.execute(query)
posts = dictfetchall(cursor)
for post in posts:
if 'user' in related:
user = DetailsUser(cursor,post['user'])
post.update({'user': user})
if 'forum' in related:
forum = getForumByShortName(cursor,post["forum"])[0]
#forum.update({"posts": countPostInForum(cursor,forum['short_name'])})
post.update({'forum': forum})
if 'thread' in related:
cursor.execute('''SELECT CAST(dateThread AS CHAR) AS `date`,idThread AS id,isClosed,isDeleted,
message,slug,title,user,forum,likes,dislikes,posts,likes - dislikes AS points,posts
FROM Thread
WHERE idThread = %d
'''%(post['thread'],))
thread = dictfetchall(cursor)[0]
#thread.update({"posts": countPostInThread(cursor,thread['id'])})
post.update({'thread': thread})
code = 0
response = { "code": code, "response": posts }
response = json.dumps(response,ensure_ascii=False, encoding='utf8')
cursor.close()
return HttpResponse(response,content_type="application/json")
def getPostById(cursor,idPost):#Post(idPost)
cursor.execute('''SELECT idPost AS id,forum,idThread AS thread,Post.user,parent,CAST(datePost AS CHAR) AS `date`,
message,isEdited,isDeleted,isSpam,isHighlighted,isApproved,likes,dislikes,likes-dislikes AS points
FROM Post
WHERE Post.idPost = %d
'''%(idPost,))
return dictfetchall(cursor)
def listPostsUser(request):
cursor = connection.cursor()
email = request.GET["user"]
order = request.GET.get("order", 'DESC')
limit = request.GET.get("limit", None)
since = request.GET.get("since", None)#дата посты старше которых нужно вывести
query = '''SELECT idPost AS id,forum,idThread AS thread,Post.user,parent,CAST(datePost AS CHAR) AS `date`,
message,isEdited,isDeleted,isSpam,isHighlighted,isApproved,likes,dislikes,likes-dislikes AS points
FROM Post
WHERE user = '******'
''' % email
if since is not None:
query += " AND `datePost` >= '%s' "%since
query +=" ORDER BY datePost %s " %order
if limit is not None:
query += " LIMIT %d "%int(limit)
cursor.execute(query)
response = dictfetchall(cursor)
code = 0
response = { "code": code, "response":response }
response = json.dumps(response,ensure_ascii=False, encoding='utf8')
cursor.close()
return HttpResponse(response,content_type="application/json")
def listFollowersUser(request):#список подпищиков
cursor = connection.cursor()
#вроде как эта штука работает
email = request.GET["user"]
order = request.GET.get("order", 'DESC')
limit = request.GET.get("limit", None)
since_id = request.GET.get("since_id", None)
query = '''SELECT about,email,idUser AS id,isAnonymous,name,username
FROM User JOIN Followers
ON Followers.follower = User.email
WHERE user = '******'
''' %email
if since_id is not None:
query += " AND User.idUser >= %d "% int(since_id)
query += " ORDER BY name %s " % order
if limit is not None:
query += " LIMIT %d "%int(limit)
cursor.execute(query)
users = dictfetchall(cursor)
for user in users:
user.update(getFFS(cursor,user["email"]))
code = 0
responce = { "code": code, "response":users }
responce = json.dumps(responce)
cursor.close()
return HttpResponse(responce,content_type="application/json")
def getThreadById(cursor,idThread):#Thread(idThread)
#countPost = countPostInThread(cursor,idThread)
cursor.execute('''SELECT CAST(dateThread AS CHAR) AS `date`,idThread AS id,isClosed,isDeleted,
message,slug,title,user,forum,likes,dislikes,likes-dislikes AS points,posts
FROM Thread
WHERE idThread = %d
'''%idThread )
response = dictfetchall(cursor)
#response[0].setdefault('posts',countPost)
return response
def listThreadsInForum(request):
cursor = connection.cursor()
shortName = request.GET['forum']
since = request.GET.get('since',None)#дата треды старше которой нам нужны
limit = request.GET.get('limit',None)
if limit is not None:
limit = int(limit)
order = request.GET.get('order','DESC')
related = request.GET.getlist('related',[])#массив Possible values: ['user', 'forum']. Default: []
query = '''SELECT CAST(dateThread AS CHAR) AS `date`,idThread AS id,isClosed,isDeleted,
message,slug,title,user,forum,likes - dislikes AS points,likes,dislikes,posts
FROM Thread
WHERE forum = '%s'
'''%(shortName,)
if since is not None:
query += "AND `dateThread` >= '%s' "%since
if order is not None:
query += "ORDER BY dateThread %s"%order
if limit is not None:
query += " LIMIT %d "%limit
cursor.execute(query)
threads = dictfetchall(cursor)
for thread in threads:
if 'user' in related:
user = DetailsUser(cursor,thread['user'])
thread.update({'user': user})
if 'forum' in related:
forum = getForumByShortName(cursor,thread['forum'])[0]
#forum.update({"posts": countPostInForum(cursor,forum['short_name'])})
thread.update({'forum': forum})
#thread.update({"posts": countPostInThread(cursor,thread['id'])})
code = 0
response = { "code": code, "response": threads }
response = json.dumps(response,ensure_ascii=False, encoding='utf8')
cursor.close()
return HttpResponse(response,content_type="application/json")
def listThread(request):#GET
cursor = connection.cursor()
#Requried forum or user
forum = request.GET.get('forum',None)
user = request.GET.get('user',None)
#Optional
since = request.GET.get('since',None)
limit = request.GET.get('limit',None)
if limit is not None:
limit = int(limit)
order = request.GET.get('order','DESC')#sort order (by date)
query = '''SELECT CAST(dateThread AS CHAR) AS `date`,idThread AS id,isClosed,isDeleted,
message,slug,title,user,forum,likes - dislikes AS points,likes,dislikes,posts
FROM Thread
'''
if forum is not None:
query += "WHERE forum = '%s'"%forum
if user is not None:
query += "WHERE user = '******' "%user
if since is not None:
query += "AND `dateThread` >= '%s' "%since
if order is not None:
query += "ORDER BY dateThread %s"%order
if limit is not None:
query += " LIMIT %d "%limit
cursor.execute(query)
posts = dictfetchall(cursor)
#for post in posts:
# post.update({"posts": countPostInThread(cursor,post['id'])})
code = 0
response = { "code": code, "response": posts }
response = json.dumps(response)
cursor.close()
return HttpResponse(response,content_type="application/json")
def listUsersInForum(request):
cursor = connection.cursor()
shortName = request.GET["forum"]
order = request.GET.get("order", 'desc')#sort order (by name)
limit = request.GET.get("limit", None)
if limit is not None:
limit = int(limit)
since_id = request.GET.get("since_id", None)
if since_id is not None:
since_id = int(since_id)
query = '''SELECT STRAIGHT_JOIN User.email,User.about,User.idUser AS id,User.isAnonymous,User.name,User.username
FROM User FORCE INDEX(name_idUser) JOIN Post FORCE INDEX (forum_user)
ON User.email = Post.user
WHERE Post.forum = '%s'
''' % (shortName)#FORCE INDEX(name_idUser) FORCE INDEX (forum_user)
if since_id is not None:
query += " AND User.idUser >= %d "%(since_id)
query += "GROUP BY name,idUser "#хак за место DISTINCT
query += "ORDER BY name %s, idUser %s "%(order,order)
if limit is not None:
query += " LIMIT %d"%(limit)
cursor.execute(query)
users = dictfetchall(cursor)
for user in users:
user.update(getFFS(cursor,user['email']))
#нужно видимо хранить в Post и idForum
#получаем массив мыассивов пользователей, теперь надо как то составить ответ
code = 0
response = { "code": code, "response": users }
response = json.dumps(response)
cursor.close()
return HttpResponse(response,content_type="application/json")
def listPost(request):#Post(forum,datePost) Post(idThread,datePost) #шикарные индексы
cursor = connection.cursor()
forum = request.GET.get('forum',None)#приходит или форум или тема
thread = request.GET.get('thread',None)
if thread is not None:
thread = int(thread)
since = request.GET.get('since',None)
limit = request.GET.get('limit',None)
if limit is not None:
limit = int(limit)
order = request.GET.get('order','DESC')#sort order (by date)
query = '''SELECT idPost AS id,forum,idThread AS thread,Post.user,parent,CAST(datePost AS CHAR) AS `date`,message,
isEdited,isDeleted,isSpam,isHighlighted,isApproved,likes,dislikes,points
FROM Post
'''
# что делать с тем что у нас параметры опциональные и они могут поломать индекс?
if forum is not None:
query += "WHERE forum = '%s'"%forum
if thread is not None:
query += "WHERE idThread = %d "%thread
if since is not None:
query += "AND `datePost` >= '%s' "%since
if order is not None:
query += "ORDER BY datePost %s"%order
if limit is not None:
query += " LIMIT %d "%limit
cursor.execute(query)
posts = dictfetchall(cursor)
code = 0
response = { "code": code, "response": posts }
response = json.dumps(response)
cursor.close()
return HttpResponse(response,content_type="application/json")
def listPosts(request):
cursor = connection.cursor()
#Requried
thread = int(request.GET.get('thread',None))
#Optional
since = request.GET.get('since',None)
limit = request.GET.get('limit',None)
if limit is not None:
limit = int(limit)
order = request.GET.get('order','DESC')#sort order (by date)
sort = request.GET.get('sort','flat')
query = '''SELECT idPost AS id,forum,idThread AS thread,Post.user,parent,CAST(datePost AS CHAR) AS `date`,message,
isEdited,isDeleted,isSpam,isHighlighted,isApproved,likes,dislikes,likes - dislikes AS points
FROM Post
WHERE idThread = %d
''' %thread
if since is not None:
query += "AND `datePost` >= '%s' "%since
if sort is not None:
if sort == 'flat':
query += "ORDER BY datePost %s"%order
if sort == 'tree':#видими 2 нижних метода не вызываются
query += "ORDER BY `path` %s"%order
if sort == 'parent_tree':#тут надо доработать
query += ''' AND CAST( LEFT( `path`, INSTR(`path`,".")-1 ) AS INT ) > %d
ORDER BY `path` %s''' % (limit,order)
if limit is not None and not sort == 'parent_tree':
query += " LIMIT %d "%limit
cursor.execute(query)
posts = dictfetchall(cursor)
code = 0
response = { "code": code, "response": posts }
response = json.dumps(response)
cursor.close()
return HttpResponse(response,content_type="application/json")
def detailsForum(request):#Forum(short_name)
cursor = connection.cursor()
shortName = request.GET['forum']
related = request.GET.getlist('related',[])
cursor.execute('''SELECT short_name,name,idForum AS id,user
FROM Forum
WHERE short_name = '%s';
''' % (shortName,))
responce = dictfetchall(cursor)[0]
if 'user' in related:
email = responce.get('user','')
user = DetailsUser(cursor,email)
responce.update({'user':user})
code = 0
responce = { "code": code, "response":responce }
responce = json.dumps(responce)
cursor.close()
return HttpResponse(responce,content_type="application/json")
def listFollowingUser(request):
#копипаста прошлого
cursor = connection.cursor()
email = request.GET["user"]
order = request.GET.get("order", 'DESC')
limit = request.GET.get("limit", None)
since_id = request.GET.get("since_id", None)
query = '''SELECT about,email,idUser AS id,isAnonymous,name,username
FROM Followers JOIN User
ON Followers.user = User.email
WHERE follower = '%s'
''' % email
if since_id is not None:#????????????? надо узнать чей это id
query += " AND User.idUser >= %d "% int(since_id)
query += " ORDER BY name %s" % order
if limit is not None:
query += " LIMIT %d "%int(limit)
cursor.execute(query)
#following = [item[0] for item in cursor.fetchall()]
users = dictfetchall(cursor)
for user in users:
user.update(getFFS(cursor,user["email"]))
code = 0
response = { "code": code, "response":users }
response = json.dumps(response)
cursor.close()
return HttpResponse(response,content_type="application/json")
def getForumByShortName(cursor,shortName):#Forum(short_name)
cursor.execute('''SELECT idForum AS id,name,short_name,user
FROM Forum
WHERE short_name = '%s'
'''%(shortName,))
return dictfetchall(cursor)
def getUserByEmail(cursor,email):#User(email)
cursor.execute('''SELECT about,email,idUser AS id,isAnonymous,name,username
FROM User
WHERE email = '%s'
'''%email )
return dictfetchall(cursor)
