def InsertUser():
if request.method == 'POST':
executionState: bool = False
cur = get_db(DATABASE).cursor()
data = request.get_json(force=True)
try:
date_created = datetime.datetime.now()
is_active = 1
password = pwd_context.hash(data['password'])
cur.execute(
"INSERT INTO users ( username, password, display_name, date_created, is_active ) VALUES (:username, :password, :display_name, :date_created, :is_active )",
{
"username": data['username'],
"password": password,
"display_name": data['display_name'],
"date_created": date_created,
"is_active": is_active
})
if (cur.rowcount >= 1):
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState == True:
return jsonify(message="Data Inserted Sucessfully"), 201
else:
return jsonify(message="Failed to insert data"), 409
def addTagsToExistingArticle():
if request.method == 'PUT':
data = request.get_json(force=True)
tags =data['tags']
#return 204 if not found
print(tags)
executionState:bool = False
try:
for tag in tags:
cur = get_db(DATABASE).cursor()
cur.execute("SELECT tag_id FROM tags WHERE tag_name=:tag_name",{"tag_name":tag})
result = cur.fetchone()
if str(result)!="None":
tag_id =str(result[0])
#add the new tag here
#insert the relation if not exists
cur.execute("INSERT INTO tag_article_mapping(tag_id, article_id) SELECT (:tag_id),(:article_id) WHERE NOT EXISTS(SELECT 1 FROM tag_article_mapping WHERE tag_id= :tag_id AND article_id = :article_id)", {"tag_id":tag_id, "article_id":data['article_id']})
elif str(result)=="None":
cur.execute("INSERT INTO tags(tag_name) VALUES( :tag_name )",{"tag_name":tag})
new_tag_inserted_id =cur.lastrowid
cur.execute("INSERT INTO tag_article_mapping (tag_id, article_id) VALUES (:tag_id, :article_id)",{"tag_id":new_tag_inserted_id,"article_id":data['article_id']})
if (cur.rowcount >=1):
executionState =True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState:
return jsonify(message="Added Tags to an existing article"),201
else:
return jsonify(message="Failed to add tags to the article"),409
def deleteTagFromArticle():
if request.method == 'DELETE':
data = request.get_json(force=True)
#article_id = request.args.get('article_id')
#print(tag_name+article_id)
executionState: bool = False
cur = get_db().cursor()
try:
cur.execute(
"SELECT article_id FROM article WHERE article_id=:article_id",
{"article_id": data['article_id']})
result = cur.fetchone()
if str(result) != "None":
#check if tag name exists or not
cur.execute(
"DELETE from tag_article_mapping where tag_id IN ( Select tag_id from tags WHERE tag_name =:tag_name) AND article_id=:article_id",
{
"tag_name": data['tag_name'],
"article_id": data['article_id']
})
#check for query result
if (cur.rowcount >= 1):
executionState = True
get_db().commit()
except:
get_db().rollback()
print("Error")
finally:
if executionState:
return jsonify(message="Deleted Tag SucessFully"), 200
else:
return jsonify(
message="Failed to delete tags from article"), 409
def updateArticle():
if request.method == 'PUT':
executionState: bool = False
cur = get_db().cursor()
try:
data = request.get_json(force=True)
tmod = datetime.datetime.now()
uid = request.authorization["username"]
pwd = request.authorization["password"]
cur.execute("select * from article where article_id=?",
(data['article_id'], ))
res = cur.fetchall()
if len(res) > 0:
cur.execute(
"UPDATE article set content=?,date_modified=? where article_id=? and author =?",
(data['content'], tmod, data['article_id'], uid))
if (cur.rowcount >= 1):
executionState = True
get_db().commit()
else:
return jsonify(message="Article does not exist"), 409
except:
get_db().rollback()
print("Error in update")
finally:
if executionState:
return jsonify(message="Updated Article SucessFully"), 201
else:
return jsonify(message="Failed to update Article"), 409
def addTagstoArticle():
if request.method == 'POST':
data = request.get_json(force=True)
executionState: bool = False
print(str(data))
cur = get_db().cursor()
try:
#check if tag exists or not
#check if the article exists or not
cur.execute("SELECT * FROM article WHERE article_id=:article_id",
{"article_id": data['article_id']})
articleExists = cur.fetchall()
if articleExists != ():
cur.execute("INSERT INTO tags(tag_name) VALUES (:tag_name)",
{"tag_name": data['tag_name']})
tag_id = cur.lastrowid
cur.execute(
"INSERT INTO tag_article_mapping(tag_id, article_id) VALUES(:tag_id, :article_id) ",
{
"tag_id": tag_id,
"article_id": data['article_id']
})
if (cur.rowcount >= 1):
executionState = True
get_db().commit()
if articleExists == ():
return jsonify(message="Article does not exist"), 204
except:
get_db().rollback()
print("Error")
finally:
if executionState:
return jsonify(message="Tag inserted successfully \n"), 201
else:
return jsonify(message="Failed to insert tag"), 409
def deleteArticle():
if request.method == 'DELETE':
cur = get_db().cursor()
executionState: bool = False
try:
data = request.get_json(force=True)
uid = request.authorization["username"]
pwd = request.authorization["password"]
cur.execute("select * from article where article_id=?",
(data['article_id'], ))
res = cur.fetchall()
if len(res) > 0:
cur.execute(
"update article set is_active_article=0 where article_id= :article_id and author= :author AND EXISTS(SELECT 1 FROM article WHERE author=:author AND is_active_article=1)",
{
"article_id": data['article_id'],
"author": uid
})
row = cur.fetchall()
if cur.rowcount >= 1:
executionState = True
get_db().commit()
except:
get_db().rollback()
print("Error")
finally:
if executionState:
return jsonify(message="Deleted Article SucessFully"), 200
else:
return jsonify(message="Failed to delete Article"), 409
def deleteArticle(id):
if request.method == 'DELETE':
cur = get_db(DATABASE).cursor()
executionState: bool = False
try:
data = request.get_json(force=True)
cur.execute("select * from articles where article_id=?",
(data['article_id'], ))
res = cur.fetchall()
if len(res) > 0:
cur.execute(
"UPDATE articles where article_id= :article_id and author= :author AND EXISTS(SELECT 1 FROM articles WHERE author=:author)",
{
"article_id": data['article_id'],
"author": author
})
row = cur.fetchall()
if cur.rowcount >= 1:
executionState = True
get_db(DATABSE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState == True:
return jsonify(message="Deleted article successfully"), 200
else:
return jsonify(message="Failed to delete Article"), 409
def insertArticle():
if request.method == 'POST':
data = request.get_json(force=True)
executionState: bool = False
try:
cur = get_db(DATABASE).cursor()
current_time = datetime.datetime.now()
# uid = request.authorization["username"] Not required
# pwd = request.authorization["password"] Not required
cur.execute(
"INSERT INTO articles(title,author,content,date_created,date_modified) VALUES (:title, :author, :content, :date_created, :date_modified)",
{
"title": data['title'],
"author": data['author'],
"content": data['content'],
"date_created": current_time,
"date_modified": current_time
})
last_inserted_row = cur.lastrowid
url_article = ("http://127.0.0.1:5200/articles/" +
str(last_inserted_row) + "")
cur.execute("UPDATE articles set url=? where article_id=?",
(url_article, last_inserted_row))
if (cur.rowcount >= 1):
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState:
return jsonify(message="Data insert sucessfully"), 201
else:
return jsonify(message="Failed to insert data"), 409
def updateArticle(id):
if request.method == 'PUT':
cur = get_db(DATABASE).cursor()
executionState: bool = True
try:
data = request.get_json(force=True)
tmod = datetime.datetime.now()
cur.execute("select * from articles where article_id=?",
(data['article_id'], ))
res = cur.fetchall()
if len(res) > 0:
cur.execute(
"UPDATE articles set content=?,date_modified=? where article_id=? and author=?",
(data['content'], tmod, data['article_id'], id))
if (cur.rowcount >= 1):
executionState = True
get_db(DATABASE).commit()
else:
return jsonify(message="Article does not exist"), 409
except:
get_db(DATABASE).rollback()
print("Error in update")
finally:
if executionState == True:
return jsonify(message="Updated article successfully"), 201
else:
return jsonify(message="Failed to update Article"), 409
def metaList():
if request.method == 'GET': # try except
limit = request.args.get('limit') if request.args.get('limit') else 10
executionState: bool = True
cur = get_db(DATABASE).cursor()
try:
if limit is not None:
cur.execute(
"select title,author,date_created,date_modified from articles order by date_created desc limit :limit",
{"limit": limit})
row = cur.fetchall()
if len(row) == 0:
return "No such value exists\n", 204
return jsonify(row), 200
except:
get_db(DATABASE).rollback()
executionState = False
finally:
if executionState == False:
return jsonify(message="Fail to retrieve from db"), 409
else:
return jsonify(row), 200
def latestArticle():
if request.method == 'GET': # try except
limit = request.args.get('limit')
article_id = request.args.get('article_id')
metadata = request.args.get('metadata')
executionState: bool = True
cur = get_db().cursor()
print(metadata)
try:
if limit is not None:
cur.execute(
"select * from article where is_active_article = 1 order by date_created desc limit :limit",
{"limit": limit})
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
if limit is None and article_id is None and metadata is None:
cur.execute('''Select * from article''')
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
if article_id is not None:
cur.execute("SELECT * from article WHERE article_id=" +
article_id)
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
if metadata is not None:
cur.execute(
"select title,author,date_created,date_modified from article where is_active_article = 1 order by date_created desc limit :metadata",
{"metadata": metadata})
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
except:
get_db().rollback()
executionState = False
finally:
if executionState == False:
return jsonify(message="Fail to retrive from db"), 409
else:
return jsonify(row), 200
def getTagsFromArticle(article_id):
if request.method == 'GET':
cur = get_db().cursor()
cur.execute(
"SELECT tag_name from tags WHERE tag_id IN (SELECT tag_id from tag_article_mapping WHERE article_id=:article_id )",
{"article_id": article_id})
row = cur.fetchall()
return jsonify(row), 200
def retriveComments():
if request.method == 'GET':
executionState: bool = False
cur = get_db().cursor()
try: #move the try block after the below for test case if the data is none or not then only try db connection
data = request.args.get('article_id')
data1 = request.args.get('number')
executionState = True
if data is not None and data1 is not None:
cur.execute(
"SELECT timestamp, comment FROM(SELECT * FROM comments WHERE article_id="
+ data + " ORDER BY timestamp DESC LIMIT :data1)",
{"data1": data1})
retriveNcomments = cur.fetchall()
get_db().commit()
if list(retriveNcomments) == []:
return "No such value exists\n", 204
return jsonify(retriveNcomments), 200
if data is not None and data1 is None:
cur.execute("SELECT comment from comments WHERE article_id=" +
data)
retriveAllComments = cur.fetchall()
get_db().commit()
if list(retriveAllComments) == []:
return "No such value exists\n", 204
return jsonify(len(retriveAllComments)), 200
except:
get_db().rollback() #if it fails to execute rollback the database
executionState = False
finally:
if executionState == False:
return jsonify(message="Fail"), 204
def AddComment():
if request.method == 'POST':
executionState: bool = False
cur = get_db(DATABASE).cursor()
data = request.get_json(force=True)
try:
# if isAuthenticated == False:
# time_created = datetime.now()
# cur.execute("SELECT * FROM article WHERE article_id=?",(data['article_id'],))
# count = len(cur.fetchall())
# if count >=1:
# cur.execute("INSERT INTO comments (comment, user_name, article_id, timestamp) VALUES (:comment, :user_name,:article_id, :timestamp) ",{"comment":data['comment'], "user_name":"Anonymous Coward", "article_id":data['article_id'], "timestamp": time_created})
# get_db().commit()
# if cur.rowcount >= 1:
# executionState = True
# else:
#uid = request.authorization["username"]
#pwd = request.authorization["password"]
time_created = datetime.now()
#cur.execute("SELECT * FROM articles WHERE article_id=?",(data['article_id'],))
#count = len(cur.fetchall())
if True:
cur.execute(
"INSERT INTO comments (article_id, comment, username, display_name, timestamp) VALUES (:article_id, :comment, :display_name, :username, :timestamp) ",
{
"article_id": data['article_id'],
"comment": data['comment'],
"username": data['username'],
"display_name": data['display_name'],
"timestamp": time_created
})
get_db(DATABASE).commit()
if cur.rowcount >= 1:
executionState = True
except:
get_db(DATABASE).rollback(
) #if it fails to execute rollback the database
executionState = False
finally:
if executionState:
return jsonify(message="Passed"), 201
else:
return jsonify(
message="Fail"
), 409 #use 409 if value exists and send the message of conflict
def deleteComment():
if request.method == 'DELETE':
executionState: bool = False
cur = get_db(DATABASE).cursor()
try:
data = request.args.get('comment_id')
cur.execute("SELECT author FROM comments WHERE comment_id=" + data)
row = cur.fetchall()
if row[0][0] == "Anonymous Coward":
cur.execute(
"DELETE from comments WHERE author ='Anonymous Coward' AND comment_id="
+ data)
if cur.rowcount >= 1:
executionState = True
get_db(DATABASE).commit()
#if isAuthenticated == True:
#uid = #request.authorization["username"]
#pwd = request.authorization["password"]
#if row[0][0] == uid:
cur.execute("DELETE from comments WHERE author=? AND comment_id=?",
(uid, data))
if cur.rowcount >= 1:
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback(
) #if it fails to execute rollback the database
executionState = False
finally:
if executionState:
return jsonify(message="Passed"), 201
else:
return jsonify(message="Fail"), 409
def show():
cur = get_db(DATABASE).cursor()
article_id = request.args.get('article_id')
try:
if article_id is not None:
#cur.execute("SELECT * from articles WHERE article_id = " + article_id)
cur.execute("SELECT * from articles WHERE article_id=" +
article_id)
row = cur.fetchone()
except:
get_db(DATABASE).rollback()
return jsonify(message="Fail to retrieve from db"), 409
finally:
if row is None:
return jsonify(message="Article not found"), 404
return jsonify(row), 200
def check_auth(username, password):
cur = get_db().cursor().execute(
"SELECT user_name, hashed_password from users WHERE user_name=?",
(username, ))
row = cur.fetchall()
if row[0][0] == username and pwd_context.verify(password, row[0][1]):
return True
else:
return False
def list():
if request.method == 'GET': # try except
limit = request.args.get('limit') if request.args.get('limit') else 5
executionState: bool = True
cur = get_db(DATABASE).cursor()
print(limit)
try:
# Get the n most recent articles
if limit is not None:
cur.execute(
"select * from articles order by date_created desc limit :limit",
{"limit": limit})
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
if limit is None and article_id is None:
cur.execute('''Select * from articles''')
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
if article_id is not None:
cur.execute("SELECT * from articles WHERE article_id=" +
article_id)
row = cur.fetchall()
if list(row) == []:
return "No such value exists\n", 204
return jsonify(row), 200
except:
get_db(DATABASE).rollback()
executionState = False
finally:
if executionState == False:
return jsonify(message="Fail to retrieve from db"), 409
else:
return jsonify(row), 200
def getArticlesFromTag():
if request.method == 'GET':
data = request.args.get('tag')
cur = get_db().cursor()
cur.execute(
"Select * from article where article_id IN(Select article_id from tag_article_mapping where tag_id in (Select tag_id from tags WHERE tag_name =:tag_name ))",
{"tag_name": data})
row = cur.fetchall()
if len(row) == 0:
return "No articles containing the tags", 204
else:
return jsonify(row), 200
def DeleteUser():
if request.method == "DELETE":
executionState: bool = False
cur = get_db(DATABASE).cursor()
try:
uid = request.authorization["username"]
pwd = request.authorization["password"]
cur.execute(
"UPDATE users SET is_active =? WHERE username=? AND EXISTS(SELECT 1 FROM users WHERE username=? AND is_active=1)",
(0, uid, uid))
if cur.rowcount >= 1:
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState == True:
return jsonify(message="Data SucessFully deleted"), 200
else:
return jsonify(message="Failed to delete data"), 409
def UpdateUser():
if request.method == 'PATCH':
executionState: bool = False
cur = get_db(DATABASE).cursor()
try:
data = request.get_json(force=True)
uid = request.authorization["username"]
pwd = request.authorization["password"]
hash_password = pwd_context.hash(data['hashed_password'])
cur.execute(
"UPDATE users SET password=? WHERE username=? AND EXISTS(SELECT 1 FROM users WHERE username=? AND is_active=1)",
(hash_password, uid, uid))
if (cur.rowcount >= 1):
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback()
print("Error")
finally:
if executionState == True:
return jsonify(message="Updated SucessFully"), 201
else:
return jsonify(message="Failed to update the data"), 409
def UpdateComments():
if request.method == 'PUT':
executionState: bool = False
cur = get_db(DATABASE).cursor()
try:
data = request.get_json(force=True)
cur.execute("SELECT author FROM comments WHERE comment_id=?",
(data['comment_id']))
row = cur.fetchall()
timeCreated = datetime.now()
if row[0][0] == "Anonymous Coward":
cur.execute(
"UPDATE comments set comment = ?,timestamp=? where author = 'Anonymous Coward' AND comment_id =?",
(data['comment'], timeCreated, data['comment_id']))
if cur.rowcount >= 1:
executionState = True
get_db(DATABASE).commit()
# if isAuthenticated == True:
# uid = request.authorization["username"]
# pwd = request.authorization["password"]
if row[0][0] == uid:
cur.execute(
"UPDATE comments set comment = ?,timestamp=? where author =? AND comment_id =?",
(data['comment'], timeCreated, uid, data['comment_id']))
if cur.rowcount >= 1:
executionState = True
get_db(DATABASE).commit()
except:
get_db(DATABASE).rollback(
) #if it fails to execute rollback the database
executionState = False
finally:
if executionState:
return jsonify(message="Passed"), 201
else:
return jsonify(message="Fail"), 409
def root(article_id):
cur = get_db(DATABASE).cursor()
cur.execute("SELECT comment from comments WHERE article_id=" + article_id)
row = cur.fetchall()
return jsonify(row)
