def __init__(self, qtn_id, user_id, title, subject, qtn_desc):
DatabaseConnection.__init__(self)
self.user_id = user_id
self.qtn_id = qtn_id
self.title = title
self.subject = subject
self.qtn_desc = qtn_desc
def create_questions_table(self):
try:
with DatabaseConnection() as cursor:
sql = "CREATE TABLE IF NOT EXISTs questions(qtn_id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, title VARCHAR(100) NOT NULL UNIQUE, subject VARCHAR(200) NOT NULL, qtn_desc VARCHAR(100) NOT NULL)"
cursor.execute(sql)
except Exception as e:
return e
def login_user():
""" Allows users to log into their accounts"""
try:
with DatabaseConnection() as cursor:
if not request.get_json():
return make_response(
jsonify({"message": "Request should be json"}), 400)
email = request.get_json()['email']
password = request.get_json()['password']
sql = "select user_id, email, password from users where email = %s and password = %s"
cursor.execute(sql, (email, password))
user_id = cursor.fetchone()
if user_id:
token = User.encode_auth_token(user_id)
if token:
response = {
'user_id': user_id[0],
'message': 'You logged in successfully',
'token': token,
'email': email
}
return make_response(jsonify(response))
return make_response(jsonify({"message": "Please log in"}))
else:
return make_response(
jsonify({"message":
"wrong password or email credentials"}))
except Exception as e:
logging.error(e)
return make_response(jsonify({'message': str(e)}), 401)
def tearDown(self):
"""
Method to drop tables after the test is run
"""
with DatabaseConnection() as cursor:
cursor.execute("DROP TABLE IF EXISTS users CASCADE")
cursor.execute("DROP TABLE IF EXISTS questions CASCADE")
cursor.execute("DROP TABLE IF EXISTS answers CASCADE")
def check_approve_status(answer_id):
"""check the status of the answer whether approved or not"""
with DatabaseConnection() as cursor:
cursor.execute(
"SELECT approve FROM answers WHERE answer_id = '%s'",
[answer_id])
approve = cursor.fetchone()
if approve == "Yeah":
return "Best answer"
def post_answer(self):
sql = "INSERT INTO answers(qtn_id, user_id, answer_desc, approve) VALUES(%s, %s, %s, %s) RETURNING qtn_id"
try:
with DatabaseConnection() as cursor:
cursor.execute(sql, (self.qtn_id, self.user_id,
self.answer_desc, self.approve))
cursor.execute("SELECT * FROM answers WHERE qtn_id = '%s'" %
self.qtn_id)
result = cursor.fetchone()
return jsonify(self.answer_dict(result))
except Exception as e:
raise e
def fetch_by_id(user_id, qtn_id):
try:
with DatabaseConnection() as cursor:
sql = "SELECT * from questions WHERE qtn_id = %s"
cursor.execute(sql, [qtn_id])
result = cursor.fetchone()
print(result)
if result:
return result
return {"message": "question not found"}
except Exception as e:
return e
def delete_answer(answer_id, qtn_id):
with DatabaseConnection() as cursor:
cursor.execute("SELECT * FROM answers WHERE answer_id = '%s'" %
answer_id)
if not cursor.fetchone():
return {
"message":
"The answer you are trying to delete doesn't exist"
}
cursor.execute(
"DELETE FROM answers where answer_id = %s AND user_id = %s",
[answer_id, qtn_id])
return {"message": "DELETE"}
def update_qtn(qtn_id, title, subject, qtn_desc):
"""This method enables a user to update question by id"""
try:
with DatabaseConnection() as cursor:
sql = "UPDATE questions SET title = %s, subject = %s, qtn_desc = %s WHERE qtn_id = %s RETURNING *"
question = cursor.execute(sql,
(title, subject, qtn_desc, qtn_id))
if question:
return {"update": Question.qtn_dict(question)}
except Exception as e:
logging.error(e)
return make_response(jsonify({'message': str(e)}), 500)
def retrieve_all_questions(user_id):
results = []
try:
with DatabaseConnection() as cursor:
cursor.execute("SELECT * FROM questions")
questions = cursor.fetchall()
if questions:
for question in questions:
results.append(Question.qtn_dict(question))
return results
return {'message': 'No questions found'}
except Exception as e:
return e
def delete_question(qtn_id, user_id):
with DatabaseConnection() as cursor:
try:
query = "SELECT * FROM questions WHERE qtn_id = '%s'" % qtn_id
cursor.execute(query)
question = cursor.fetchone()
print(question)
if not question:
return {"message": "Question doesn't exist"}
sql = "DELETE FROM questions WHERE qtn_id = %s AND user_id = %s"
cursor.execute(sql, [qtn_id, user_id])
return {"message": "Question deleted"}
except Exception as e:
return e
def insert_user_data(self):
sql = "INSERT INTO users(username, email, password) VALUES(%s, %s, %s) "
try:
with DatabaseConnection() as cursor:
cursor.execute("SELECT * FROM users WHERE email = '%s'" % self.email)
if cursor.fetchone():
return make_response(jsonify({"message": "Email already in use"}), 409)
else:
cursor.execute(sql, (self.username, self.email, self.password))
cursor.execute(
"SELECT * FROM users WHERE email = '%s'" % self.email)
return make_response(jsonify({"message": "You have successfully registered"}), 201)
except Exception as e:
logging.error(e)
return make_response(jsonify({'message': str(e)}), 500)
def create_question(self):
sql = "INSERT INTO questions(user_id, title, subject, qtn_desc) VALUES(%s, %s, %s, %s) RETURNING title"
try:
with DatabaseConnection() as cursor:
cursor.execute("SELECT * FROM questions WHERE title = '%s'" %
self.title)
if cursor.fetchone():
return make_response(
jsonify({"message": "Question already exists"}))
else:
cursor.execute(sql, (self.user_id, self.title,
self.subject, self.qtn_desc))
cursor.execute(
"SELECT * FROM questions WHERE title = '%s'" %
self.title)
return make_response(
jsonify({"message": "Question created successfully"}),
201)
except Exception as e:
return e
def __init__(self, user_id, qtn_id, answer_desc, approve):
DatabaseConnection.__init__(self)
self.answer_desc = answer_desc
self.qtn_id = qtn_id
self.user_id = user_id
self.approve = approve
def __init__(self, user_id, username, email, password):
DatabaseConnection.__init__(self)
self.user_id = user_id
self.username = username
self.email = email
self.password = password
def setUp(self):
self.client = app.test_client(self)
with DatabaseConnection() as cursor:
cursor.execute("CREATE TABLE IF NOT EXISTs users( user_id SERIAL PRIMARY KEY, username VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(12) NOT NULL)")
cursor.execute("CREATE TABLE IF NOT EXISTs answers(answer_id_id SERIAL PRIMARY KEY, qtn_id INT NOT NULL, user_id INT NOT NULL, answer_desc VARCHAR(100) NOT NULL, approve VARCHAR(100) NOT NULL)")
cursor.execute("CREATE TABLE IF NOT EXISTs questions(qtn_id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, title VARCHAR(100) NOT NULL UNIQUE, subject VARCHAR(200) NOT NULL, qtn_desc VARCHAR(100) NOT NULL)")