add some backend api

Guo-HY · Nov 28, 2022 · b4518fc · b4518fc
1 parent c628b0e
commit b4518fc
Show file tree

Hide file tree

Showing 6 changed files with 297 additions and 19 deletions.
diff --git a/doc/前后端交互API.md b/doc/前后端交互API.md
@@ -10,22 +10,6 @@
 
 ![image-20221106231951650](assets/image-20221106231951650.png)
 
-## 社区
-
-![](E:\大三上\数据库\大作业\p2\BUAA_DB\doc\assets\社区.png)
-
-## 圈子
-
-![](E:\大三上\数据库\大作业\p2\BUAA_DB\doc\assets\圈子.png)
-
-## 帖子
-
-![](E:\大三上\数据库\大作业\p2\BUAA_DB\doc\assets\帖子.png)
-
-
-
-
-
 **修改或获取个人页面信息**
 
 ```
@@ -243,6 +227,8 @@ end->front
 
 ## 社区页面
 
+![](.\assets\社区.png)
+
 ```
 url: 'api/getHotGroupPic'
 front-end
@@ -299,6 +285,8 @@ end->front
 
 ## 圈子页面
 
+![](.\assets\圈子.png)
+
 ```
 url: 'api/getGroupInfo'
 front->end
@@ -361,6 +349,8 @@ end->front
 
 ## 帖子
 
+![](.\assets\帖子.png)
+
 ```
 url: 'api/getPostInfo'
 front->end

diff --git a/doc/系统设计文档.md b/doc/系统设计文档.md
@@ -118,7 +118,7 @@ makeFun社交平台是基于部分匿名消息和兴趣图谱的属于当代大
 | 字段名称        | 数据类型 | 字段大小 | 必填字段 | 主键 | 外键 | 说明             |
 | --------------- | -------- | -------- | -------- | ---- | ---- | ---------------- |
 | bottle_reply_id | int      | 20       | 是       | 是   | 否   | 自增主键         |
-| context         | varchar  | 10000    | 是       | 否   | 否   | 回复内容         |
+| reply           | varchar  | 10000    | 是       | 否   | 否   | 回复内容         |
 | user_id         | int      | 20       | 是       | 否   | 是   | 发起回复的用户id |
 | bottle_id       | int      | 20       | 是       | 否   | 是   | 回复给的漂流瓶id |
 

diff --git a/src/backend/database.sql b/src/backend/database.sql
@@ -82,7 +82,7 @@ CREATE TABLE `bottle`
 CREATE TABLE `bottle_reply`
 (
   `bottle_reply_id` INT(20)         NOT NULL AUTO_INCREMENT PRIMARY KEY ,
-  `context`         VARCHAR(10000)  NOT NULL ,
+  `reply`           VARCHAR(10000)  NOT NULL ,
   `user_id`         INT(20)         NOT NULL ,
   `bottle_id`       INT(20)         NOT NULL ,
   FOREIGN KEY(`user_id`) REFERENCES `user`(`user_id`) ,

diff --git a/src/backend/mysql.py b/src/backend/mysql.py
@@ -253,4 +253,148 @@ def addGroupTag(self, groupId, tagId):
       r = "fail"
     self.closeDataBase(connect, cursor)
     return r
+
+  def getAllBottle(self):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT * FROM bottle"
+    cursor.execute(sql)
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+  def getUserSendBottle(self, userId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT * FROM bottle WHERE user_id=%s"
+    cursor.execute(sql, [userId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+  def getMyReceivedBottleReplys(self, userId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT content, reply, bottle_reply.user_id FROM bottle, bottle_reply\
+            WHERE bottle.bottle_id=bottle_reply.bottle_id AND bottle.user_id=%s"
+
+    cursor.execute(sql, [userId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+  def addUserFriend(self, user1_id, user2_id):
+    connect, cursor = self.connectDataBase()
+    sql = "INSERT user_user(user1_id, user2_id) VALUES(%s, %s)"
+    r = "success"
+    try:
+      cursor.execute(sql, [user1_id, user2_id])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+    self.closeDataBase(connect, cursor)
+    return r
+
+  def getMyRepliedBottles(self, userId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT content, reply FROM bottle, bottle_reply \
+          WHERE bottle.bottle_id=bottle_reply.bottle_id AND bottle_reply.user_id=%s"
+
+    cursor.execute(sql, [userId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
 
+  def getOnePost(self, postId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT * FROM post WHERE post_id=%s"
+    cursor.execute(sql, [postId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+  def getPostComments(self, postId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT * FROM comment WHERE post_id=%s"
+    cursor.execute(sql, [postId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+  def userLikePost(self, userId, postId):
+    connect, cursor = self.connectDataBase()
+    sql = "INSERT user_like_post(user_id, post_id) VALUES(%s, %s)"
+    r = "success"
+    try:
+      cursor.execute(sql, [userId, postId])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+      self.closeDataBase(connect, cursor)
+      return r
+
+    sql = "UPDATE post SET likes_num=likes_num+1 WHERE post_id=%s"
+    try: 
+      cursor.execute(sql, [postId])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+
+    return r
+
+  def userLikeComment(self, userId, commentId):
+    connect, cursor = self.connectDataBase()
+    sql = "INSERT user_like_comment(user_id, comment_id) VALUES(%s, %s)"
+    r = "success"
+    try:
+      cursor.execute(sql, [userId, commentId])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+      self.closeDataBase(connect, cursor)
+      return r
+
+    sql = "UPDATE comment SET likes_num=likes_num+1 WHERE comment_id=%s"
+    try: 
+      cursor.execute(sql, [commentId])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+
+    return r
+
+  def userCreateComment(self, userId, postId, content, comment_time):
+    connect, cursor = self.connectDataBase()
+    sql = "INSERT comment(content, comment_time, likes_num, user_id, post_id)\
+            VALUES (%s, %s, %s, %s, %s)"
+
+    r = "success"
+    try:
+      cursor.execute(sql, [content, comment_time, '0', userId, postId])
+      connect.commit()
+    except Exception as e:
+      connect.rollback()
+      print(e)
+      r = "fail"
+
+    self.closeDataBase(connect, cursor)
+    return r
+
+  def getFriendsList(self, userId):
+    connect, cursor = self.connectDataBase()
+    sql = "SELECT * FROM user WHERE user_id IN \
+          (SELECT user2_id FROM user_user WHERE user1_id=%s)"
+
+    cursor.execute(sql, [userId])
+    result = cursor.fetchall()
+    self.closeDataBase(connect, cursor)
+    return result
+
+
diff --git a/src/backend/urls.py b/src/backend/urls.py
@@ -2,17 +2,36 @@
 from . import views
 
 urlpatterns = [
+    # 个人主页
     path('changeUserInfo',views.changeUserInfo.as_view()),
     path('userAddTag',views.userAddTag.as_view()),
     path('getUserInfo',views.getUserInfo.as_view()),
+    # 活动页面
     path('createActivity',views.createActivity.as_view()),
     path('getActivity',views.getActivity.as_view()),
+    # 漂流瓶主页面
+    path('getOneRandomDriftBottleContent',views.getOneRandomDriftBottleContent.as_view()),
+    path('getMySendDriftBottles', views.getMySendDriftBottles.as_view()),
+    path('getMyReceivedBottleReplys', views.getMyReceivedBottleReplys.as_view()),
+    path('agreeWithReply', views.agreeWithReply.as_view()),
+    path('getMyRepliedBottles', views.getMyRepliedBottles.as_view()),
+    # 登录页面
     path('userLogin', views.userLogin.as_view()),
+    #注册页面
     path('userRegister', views.userRegister.as_view()),
+    #社区页面
     path('getHotGroupPic', views.getHotGroupPic.as_view()),
     path('getHotGroupIntro', views.getHotGroupIntro.as_view()),
     path('userAddGroup', views.userAddGroup.as_view()),
+    # 圈子页面
     path('getGroupInfo', views.getGroupInfo.as_view()),
     path('userCreatePost', views.userCreatePost.as_view()),
     path('userAddTagToGroup', views.userAddTagToGroup.as_view()),
+    # 帖子页面
+    path('getPostInfo', views.getPostInfo.as_view()),
+    path('userLikePost', views.userLikePost.as_view()),
+    path('userLikeComment', views.userLikeComment.as_view()),
+    path('userCreateComment', views.userCreateComment.as_view()),
+    #好友列表
+    path('getFriendsList', views.getFriendsList.as_view()),
 ]
diff --git a/src/backend/views.py b/src/backend/views.py
@@ -3,7 +3,7 @@
 from rest_framework.response import Response
 
 from backend.mysql import Mysql
-# from .mysql import MySQL
+import random
 
 # Create your views here.
 
@@ -193,3 +193,128 @@ def post(self, request):
       return Response({'status':'fail'})
     result = sql.addGroupTag(groupId, tagId)
     return Response({'status':result})
+
+class getOneRandomDriftBottleContent(APIView):
+  def post(self, request):
+    print("---getOneRandomDriftBottleContent---")
+    userId = str(request.POST.get('userId', None))
+    sql = Mysql()
+    allBottles = sql.getAllBottle()
+    randPtr = 0
+    while True:
+      randPtr = random(0, len(allBottles) - 1)
+      if str(allBottles[randPtr][2]) != userId:
+        break
+
+    content = allBottles[randPtr][1]
+    return Response({'content' : content})
+
+class getMySendDriftBottles(APIView):
+  def post(self, request):
+    print("---getMySendDriftBottles---")
+    userId = str(request.POST.get('userId', None))
+    sql = Mysql()
+    sql_bottles = sql.getUserSendBottle(userId)
+    bottles = []
+    for item in sql_bottles:
+      bottles.append({'content' : item[1]})
+
+    return Response({'bottles' : bottles})
+
+class getMyReceivedBottleReplys(APIView):
+  def post(self, request):
+    print("---getMyReceivedBottleReplys---")
+    userId = str(request.POST.get('userId', None))
+    sql = Mysql()
+    sql_bottlesAndReplys = sql.getMyReceivedBottleReplys(userId)
+    bottlesAndReplys = []
+    for item in sql_bottlesAndReplys:
+      bottlesAndReplys.append({'content' : item[0], 'reply' : item[1], 'replyUserId' : item[2]})
+
+    return Response({'bottlesAndReplys' : bottlesAndReplys})
+
+class agreeWithReply(APIView):
+  def post(self, request):
+    print("---agreeWithReply---")
+    userId = str(request.POST.get('userId', None))
+    replyUserId = str(request.POST.get('replyUserId', None))
+    sql = Mysql()
+    r = sql.addUserFriend(userId, replyUserId)
+    print(r)
+    return Response({'status' : 'success'})
+
+class getMyRepliedBottles(APIView):
+  def post(self, request):
+    print("---getMyRepliedBottles---")
+    userId = str(request.POST.get('userId', None))
+    sql = Mysql()
+    sql_bottles = sql.getMyRepliedBottles(userId)
+    bottles = []
+    for item in sql_bottles:
+      bottles.append({'content' : item[0], 'myReply' : item[1]})
+
+    return Response({'bottles' : bottles})
+
+class getPostInfo(APIView):
+  def post(self, request):
+    print("---getPostInfo---")
+    postId = str(request.POST.get('postId', None))
+    sql = Mysql()
+    postinfo = sql.getOnePost(postId)
+    sql_comments = sql.getPostComments(postId)
+    create_user_name = sql.getUserInfo(postinfo[6])[0][1]
+    comments = []
+    for item in sql_comments:
+      comments.append({'comment_id' : item[0], 'content' : item[1], 
+                       'comment_time' : item[2], 'likes_num' : item[3], 
+                       'comment_user_id' : item[4]})
+
+    return Response({'post_name' : postinfo[1], 'content' : postinfo[2], 
+                     'post_time' : postinfo[3], 'comment_num' : postinfo[4],
+                     'likes_num' : postinfo[5], 'create_user_name' : create_user_name,
+                     'comments' : comments})
+
+class userLikePost(APIView):
+  def post(self, request):
+    print("---userLikePost---")
+    userId = str(request.POST.get('userId', None))
+    postId = str(request.POST.get('postId', None))
+    sql = Mysql()
+    r = sql.userLikePost(userId, postId)
+    print(r)
+    return Response({'status' : 'success'})
+
+class userLikeComment(APIView):
+  def post(self, request):
+    print("---userLikePost---")
+    userId = str(request.POST.get('userId', None))
+    commentId = str(request.POST.get('commentId', None))
+    sql = Mysql()
+    r = sql.userLikeComment(userId, commentId)
+    print(r)
+    return Response({'status' : 'success'})
+
+
+class userCreateComment(APIView):
+  def post(self, request):
+    print("---userCreateComment---")
+    userId = str(request.POST.get('userId', None))
+    postId = str(request.POST.get('postId', None))
+    content = str(request.POST.get('content', None))
+    comment_time = str(request.POST.get('comment_time', None))
+    sql = Mysql()
+    r = sql.userCreateComment(userId, postId, content, comment_time)
+    print(r)
+    return Response({'status' : 'success'})
+
+class getFriendsList(APIView):
+  def post(self, request):
+    print("---getFriendsList---")
+    userId = str(request.POST.get('userId', None))
+    sql = Mysql()
+    result = sql.getFriendsList(userId)
+    friends = []
+    for item in result:
+      friends.append({'userId': item[0], 'userName' : item[1]})
+
+    return Response({'friends' : friends})