def delete_user(data):#delete one user
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT * FROM Users WHERE personID = %s",(data,))
person=cursor.fetchone()
if(not person):
return {"error":"User not exists"},404
cursor.execute("DELETE FROM Passes WHERE personID=%s",(person[0],))
cursor.execute("SELECT * FROM Posts_ids WHERE personID = %s",(person[0],))
posts_of_person=cursor.fetchall()
cursor.execute("DELETE FROM Attendee_list WHERE personID=%s",(person[0],))
cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s",(person[0],))
cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s",(person[0],))
cursor.execute("DELETE FROM Friends_of_user WHERE FriendID=%s",(person[0],))
for i in posts_of_person:
cursor.execute("DELETE FROM Comments_list WHERE postID=%s",(i[0],))
if i[2]==0:
cursor.execute("DELETE FROM Posts WHERE postID=%s",(i[0],))
elif i[2]==1:
cursor.execute("DELETE FROM Events WHERE postID=%s",(i[0],))
elif i[2]==2:
cursor.execute("DELETE FROM Items WHERE postID=%s",(i[0],))
cursor.execute("DELETE FROM Posts_ids WHERE personID=%s",(person[0],))
cursor.execute("DELETE FROM Users WHERE personID=%s",(person[0],))
cursor.close()
db.commit()
db.close()
return {"succes":"user deleted"},200
def delete_post(data): #delete post
db = get_db()
cursor = db.cursor()
try:
cursor.execute("SELECT * FROM Posts_ids WHERE postID=%s", (data, ))
post_root = cursor.fetchone()
if not post_root:
return {"error": "post not found"}, 404
if str(post_root[2]) == "0":
cursor.execute("DELETE FROM Posts WHERE postID=%s",
(post_root[0], ))
elif str(post_root[2]) == "1":
cursor.execute("DELETE FROM Events WHERE postID=%s",
(post_root[0], ))
elif str(post_root[2]) == "2":
cursor.execute("DELETE FROM Items WHERE postID=%s",
(post_root[0], ))
cursor.execute("DELETE FROM Posts_ids WHERE postID=%s",
(post_root[0], ))
cursor.close()
db.commit()
db.close()
return {"success": "Post deleted"}, 200
except:
cursor.close()
db.close()
return {"error": "an error occured"}, 400
def update_one_user(data,payload):#update one user
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT * FROM Users WHERE personID = %s",(data,))
person=cursor.fetchone()
if(not person):
return {"error":"User not exists"},404
cursor.execute("SELECT hash_pass FROM Passes WHERE personID = %s",(data,))
hash_pass=cursor.fetchall()
password=hash_pass[0][0]
person_dict={
"personID":person[0],
"fname":person[1],
"lname":person[2],
"email":person[3],
"is_admin":person[4],
"bio":person[5],
"password":password
}
for key1 in person_dict:
for key2 in payload:
if key1==key2:
person_dict[key1]=payload[key2]
cursor.execute("UPDATE Users SET F_name=%(fname)s,L_name=%(lname)s,e_mail=%(email)s,is_admin=%(is_admin)s,bio=%(bio)s WHERE personID=%(personID)s",person_dict)
cursor.execute("UPDATE Passes SET hash_pass=%(password)s WHERE personID=%(personID)s",person_dict)
cursor.close()
db.commit()
db.close()
return jsonify(person_dict)
def update_post(data): #update one post
json_data = request.get_json()
if (isinstance(json_data, str)):
post = json.loads(json_data)
elif (isinstance(json_data, dict)):
post = json_data
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT * FROM Posts_ids WHERE postID = %s", (data, ))
post_root = cursor.fetchone()
if (not post_root):
return {"error": "User not exists"}, 404
if str(post_root[2]) == "0":
cursor.execute("SELECT * FROM Posts WHERE postID=%s", (post_root[0], ))
post_data = cursor.fetchone()
post_dict = {
"postID": post_data[0],
"payload": post_data[1],
"is_news": post_data[2],
"date": post_data[3]
}
elif str(post_root[2]) == "1":
cursor.execute("SELECT * FROM Events WHERE postID=%s",
(post_root[0], ))
post_data = cursor.fetchone()
post_dict = {
"postID": post_data[0],
"payload": post_data[1],
"date": post_data[2]
}
elif str(post_root[2]) == "2":
cursor.execute("SELECT * FROM Items WHERE postID=%s", (post_root[0], ))
post_data = cursor.fetchone()
post_dict = {
"postID": post_data[0],
"payload": post_data[1],
"price": post_data[2],
"date": post_data[3]
}
for key1 in post_dict:
for key2 in post:
if key1 == key2:
post_dict[key1] = post[key2]
if str(post_root[2]) == "0":
cursor.execute(
"UPDATE Posts SET payload=%(payload)s,is_news=%(is_news)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s",
post_dict)
elif str(post_root[2]) == "1":
cursor.execute(
"UPDATE Events SET payload=%(payload)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s",
post_dict)
elif str(post_root[2]) == "2":
cursor.execute(
"UPDATE Items SET payload=%(payload)s,price=%(price)s,publish_date=CURRENT_TIMESTAMP WHERE postID=%(postID)s",
post_dict)
cursor.close()
db.commit()
db.close()
return jsonify(post_dict)
def users_all(): #read all users
db=get_db()
cursor=db.cursor()
cursor.execute('SELECT * FROM Users;')
users=cursor.fetchall()
user_api=[]
for i in users:
user_api.append({"id":i[0],"fname":i[1],"lname":i[2],"email":i[3],"is_admin":i[4],"bio":i[5]})
return jsonify(user_api)
def auth_check(personID):#auth check
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT hash_pass FROM Passes WHERE personID=%s",(personID,))
user_pass=cursor.fetchone()
cursor.close()
db.close()
return {"hash_pass":user_pass},200
def delete_friendship(data):
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT * FROM Friends_of_user WHERE FriendshipID=%s",(data,))
response=cursor.fetchone()
cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s AND FriendID=%s",(response[0],response[1],))
cursor.execute("DELETE FROM Friends_of_user WHERE personID=%s AND FriendID=%s",(response[1],response[0],))
cursor.close()
db.commit()
db.close()
return jsonify({"success":"friendship is destroyed"})
def accept_friendship(data):
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT * FROM Friends_of_user WHERE FriendshipID=%s",(data,))
response=cursor.fetchone()
cursor.execute("UPDATE Friends_of_user SET accepted=TRUE WHERE personID=%s AND FriendID=%s",(response[0],response[1],))
cursor.execute("UPDATE Friends_of_user SET accepted=TRUE WHERE personID=%s AND FriendID=%s",(response[1],response[0],))
cursor.close()
db.commit()
db.close()
return jsonify({"success":"friends are shipped"})
def friendship_requests(data):
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT * FROM Friends_of_user WHERE personID=%s AND accepted=FALSE",(data,))
response=cursor.fetchall()
result=[]
for i in response:
cursor.execute("SELECT * FROM Users WHERE personID=%s",(i[1],))
friend_data=cursor.fetchone()
result.append({"FriendshipID":i[2],"personID":i[0],"FriendID":i[1],"accepted":i[3],"friend_data":{"personID":friend_data[0],"fname":friend_data[1],"lname":friend_data[2],"email":friend_data[3]}})
cursor.close()
db.close()
return jsonify(result)
def user_one(data):#read one user
db=get_db()
cursor=db.cursor()
if data.find("@")==-1:
cursor.execute("SELECT * FROM Users WHERE personID = %s;",(data,))
else:
cursor.execute("SELECT * FROM Users WHERE e_mail = %s;",(data,))
that_user=cursor.fetchone()
if that_user:
user_api={"personID":that_user[0],"fname":that_user[1],"lname":that_user[2],"email":that_user[3],"is_admin":that_user[4],"bio":that_user[5]}
else:
cursor.close()
db.close()
return {"error":"Not Found"}, 404
cursor.close()
db.close()
return jsonify(user_api)
def get_one_post(data): #get one post
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT * FROM Posts WHERE postID=%s", (data, ))
Posts = cursor.fetchone()
cursor.execute("SELECT * FROM Events WHERE postID=%s", (data, ))
Events = cursor.fetchone()
cursor.execute("SELECT * FROM Items WHERE postID=%s", (data, ))
Items = cursor.fetchone()
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s", (data, ))
personID = cursor.fetchone()
cursor.execute("SELECT F_name,L_name FROM Users WHERE personID=%s",
(personID[0], ))
name = cursor.fetchone()
res_dict = {}
if (Posts):
res_dict = {
"name": name[0] + " " + name[1],
"postID": Posts[0],
"payload": Posts[1],
"is_news": Posts[2],
"publish_date": Posts[3]
}
elif (Events):
res_dict = {
"name": name[0] + " " + name[1],
"postID": Events[0],
"payload": Events[1],
"publish_date": Events[2]
}
elif (Items):
res_dict = {
"name": name[0] + " " + name[1],
"postID": Items[0],
"payload": Items[1],
"price": Items[2],
"publish_date": Items[3]
}
else:
cursor.close()
db.close()
return {"error": "no post found"}, 404
cursor.close()
db.close()
return jsonify(res_dict)
def send_friendship(data):
db=get_db()
cursor=db.cursor()
json_data=request.get_json()
if(isinstance(json_data,str)):
friend_req=json.loads(json_data)
elif(isinstance(json_data,dict)):
friend_req=json_data
friend_req.update({"personID":data})
cursor.execute("INSERT INTO Friends_of_user (personID,FriendID,accepted) VALUES (%(personID)s,%(FriendID)s,%(accepted)s)",friend_req)
cursor.execute("INSERT INTO Friends_of_user (personID,FriendID,accepted) VALUES (%(FriendID)s,%(personID)s,%(accepted)s)",friend_req)
cursor.execute("SELECT FriendshipID FROM Friends_of_user WHERE personID=%(personID)s AND FriendID=%(FriendID)s",friend_req)
newly_inserted=cursor.fetchone()
friend_req.update({"FriendshipID":newly_inserted[0]})
cursor.close()
db.commit()
db.close()
return jsonify(friend_req)
def insert_one_user(data):#create one user
db=get_db()
cursor=db.cursor()
cursor.execute("SELECT COUNT(*) FROM Users WHERE e_mail = %(email)s",data)
check=cursor.fetchone()
if(check[0]>0):
return {"error":"User exists"},409
cursor.execute("INSERT INTO Users (F_name,L_name,e_mail,is_admin,bio) VALUES (%(fname)s,%(lname)s,%(email)s,%(is_admin)s,%(bio)s)",data)
cursor.execute("SELECT personID FROM Users WHERE e_mail = %(email)s",data)
that_user_id=cursor.fetchone()
hash_pass_dict={
'password':data['password'],
'personID':that_user_id[0]
}
cursor.execute("INSERT INTO Passes (hash_pass,personID) VALUES (%(password)s,%(personID)s)",hash_pass_dict)
data.update({"id":that_user_id[0]})
cursor.close()
db.commit()
db.close()
return data,201
def new_post(): #add post
json_data = request.get_json()
if (isinstance(json_data, str)):
data = json.loads(json_data)
elif (isinstance(json_data, dict)):
data = json_data
db = get_db()
cursor = db.cursor()
try:
cursor.execute(
"INSERT INTO Posts_ids (personID,iem_type) VALUES (%(personID)s,%(type)s)",
data)
cursor.execute(
"SELECT MAX(postID) FROM Posts_ids WHERE personID=%(personID)s",
data)
postID = cursor.fetchone()
data.update({"postID": postID[0]})
if data["type"] == "0":
cursor.execute(
"INSERT INTO Posts (postID,payload,is_news) VALUES (%(postID)s,%(payload)s,%(is_news)s)",
data)
elif data["type"] == "1":
cursor.execute(
"INSERT INTO Events (postID,payload) VALUES (%(postID)s,%(payload)s)",
data)
elif data["type"] == "2":
cursor.execute(
"INSERT INTO Items (postID,payload,price) VALUES (%(postID)s,%(payload)s,%(price)s)",
data)
cursor.close()
db.commit()
except:
cursor.close()
db.close()
return {"error": "error occured"}, 400
cursor.close()
db.close()
return data, 200
def get_all_posts(): #get all of the posts
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT * FROM Posts")
Posts = cursor.fetchall()
cursor.execute("SELECT * FROM Events")
Events = cursor.fetchall()
cursor.execute("SELECT * FROM Items")
Items = cursor.fetchall()
all_posts = {"posts": [], "events": [], "items": []}
for i in Posts:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
post = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"is_news": i[2],
"publish_date": i[3]
}
all_posts["posts"].append(post)
for i in Events:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
event = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"publish_date": i[2]
}
all_posts["events"].append(event)
for i in Items:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
item = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"price": i[2],
"publish_date": i[3]
}
all_posts["items"].append(item)
cursor.close()
db.close()
return jsonify(all_posts)
def get_friends_posts(data): #get all posts from users friends
db = get_db()
cursor = db.cursor()
cursor.execute(
"SELECT tb3.* FROM Posts tb3 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb4 ON tb4.postID=tb3.postID",
(data, ))
posts = cursor.fetchall()
cursor.execute(
"SELECT tb5.* FROM Events tb5 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb6 ON tb5.postID=tb6.postID",
(data, ))
events = cursor.fetchall()
cursor.execute(
"SELECT tb7.* FROM Items tb7 INNER JOIN ( SELECT tb1.postID FROM Posts_ids tb1 INNER JOIN Friends_of_user tb2 ON tb1.personID=tb2.FriendID AND tb2.personID=%s AND tb2.accepted=TRUE) tb8 ON tb7.postId=tb8.postID",
(data, ))
items = cursor.fetchall()
all_posts = {
"posts": [],
"events": [],
"items": [],
}
for i in posts:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
post = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"is_news": i[2],
"publish_date": i[3]
}
all_posts["posts"].append(post)
for i in events:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
event = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"publish_date": i[2]
}
all_posts["events"].append(event)
for i in items:
cursor.execute("SELECT personID FROM Posts_ids WHERE postID=%s",
(i[0], ))
personID = cursor.fetchone()
cursor.execute("SELECT * FROM Users WHERE personID=%s",
(personID[0], ))
user = cursor.fetchone()
item = {
"user_info": {
"personID": user[0],
"Name": user[1] + " " + user[2],
"email": user[3]
},
"postID": i[0],
"payload": i[1],
"price": i[2],
"publish_date": i[3]
}
all_posts["items"].append(item)
cursor.close()
db.close()
return jsonify(all_posts)
