def update_book():
decoded_token = au.validate_jwt_token(request.cookies.get('token'))
if decoded_token['role'] == 'admin':
data = request.json['form_input']
book_id = data['book_id']
title = data['title']
author = data['author']
isbn = data['isbn']
pub_year = data['publication_year']
description = data['description']
query = "UPDATE books SET books_title=%s, books_author_id=%s, books_isbn=%s, books_pub_year=%s, books_description=%s WHERE books_id=%s;"
values = (title, author, isbn, pub_year, description, book_id)
conn = DatabaseConnection()
conn.db_connect()
result = conn.db_write(query = query, vals = values)
if result['response'] == 200:
query = "SELECT * FROM books b LEFT JOIN authors a ON b.books_author_id = a.authors_id WHERE b.books_id = %s"
values = (book_id,)
book = conn.db_read(query=query, vals=values, format_type="book")
conn.db_close()
return make_response({"books": book}, 200)
else:
conn.db_close()
return Response(status=result['response'])
else:
return Response(status=401)
def create_book_record():
decoded_token = au.validate_jwt_token(request.cookies.get('token'))
print(request.json)
if decoded_token['role'] == 'admin':
data = request.json['form_input']
title = data['title']
author = data['author']
isbn = data['isbn']
pub_year = data['publication_year']
date = datetime.datetime.now()
query = "INSERT INTO books(books_title, books_author_id, books_isbn, books_pub_year, books_date_added) VALUES (%s, %s, %s, %s, %s);"
values = (title, author, isbn, pub_year, date)
conn = DatabaseConnection()
conn.db_connect()
result = conn.db_write(query = query, vals = values)
if result['response'] == 200:
query = "SELECT * FROM books b LEFT JOIN authors a ON b.books_author_id = a.authors_id WHERE b.books_title = %s AND b.books_author_id = %s AND b.books_date_added = %s"
values = (title, author, date)
book = conn.db_read(query=query, vals=values, format_type="book")
conn.db_close()
return make_response({"books": book}, 200)
else:
conn.db_close()
return Response(status=result['response'])
else:
return Response(status=401)
def recent_titles():
query = "SELECT * FROM books b LEFT JOIN authors a ON b.books_author_id = a.authors_id ORDER BY b.books_date_added DESC LIMIT 10"
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_read(query=query, format_type="book")
conn.db_close()
return jsonify({"books": resp})
def get_one_author(id):
query = "SELECT * FROM authors WHERE authors_id = %s"
values = (id, )
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_read(query=query, vals=values, format_type="author")
conn.db_close()
return jsonify({"authors": resp})
def get_all_authors():
query = "SELECT * FROM authors"
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_read(query=query, format_type="author")
conn.db_close()
return jsonify({"authors": resp})
def get_all_books():
query = "SELECT * FROM books b LEFT JOIN authors a ON b.books_author_id = a.authors_id"
conn = DatabaseConnection()
conn.db_connect()
result = conn.db_read(format_type="book", query=query)
conn.db_close()
return make_response({"books": result}, 200)
def get_one_book(id):
query = "SELECT * FROM books b LEFT JOIN authors a ON b.books_author_id = a.authors_id WHERE b.books_id = %s"
values = (id,)
conn = DatabaseConnection()
conn.db_connect()
result = conn.db_read(format_type="book", query=query, vals=values)
conn.db_close()
return make_response({"books": result}, 200)
def get_author_biblio(id):
books_query = "SELECT * FROM authors a LEFT JOIN books b ON a.authors_id = b.books_author_id WHERE a.authors_id = %s"
author_query = "SELECT * FROM authors WHERE authors_id = %s"
values = (id, )
conn = DatabaseConnection()
conn.db_connect()
author = conn.db_read(query=author_query,
vals=values,
format_type="author")
books = conn.db_read(query=books_query, vals=values, format_type="book")
conn.db_close()
return jsonify({"authors": author, "books": books})
def login_user():
user_email = request.json['email']
user_password = request.json['password']
query = "SELECT * FROM users WHERE users_email = %s"
values = (user_email, )
conn = DatabaseConnection()
conn.db_connect()
current_user = conn.db_read(query=query, vals=values, format_type="user")
if len(current_user) > 0:
user_info = current_user[0]
user_token = au.validate_user(current_user=user_info,
password=user_password)
if user_token:
expire_date = datetime.datetime.now()
expire_date = expire_date + datetime.timedelta(days=365)
resp = make_response(
{
"user": {
"email": user_info['users_email'],
"id": user_info['users_id'],
"user_role": user_info['users_role']
}
}, 200)
resp.headers["content-type"] = "application/json"
resp.set_cookie('token',
user_token,
secure=True,
httponly=False,
expires=expire_date,
samesite="None")
return resp
else:
return Response(status=401)
else:
return Response(status=404)
def search_db():
search_string = request.json["search_string"].lower().split("+")
terms = ""
idx = 0
while idx < len(search_string):
if idx < (len(search_string) - 1):
terms += f"('%{search_string[idx]}%'),"
idx += 1
elif idx == (len(search_string) - 1):
terms += f"('%{search_string[idx]}%')"
idx += 1
create_terms_temp_table_query = "CREATE TEMPORARY TABLE search (term VARCHAR(100));"
drop_terms_temp_table_query = "DROP TEMPORARY TABLE search;"
insert_terms_query = f"INSERT INTO search VALUES {terms};"
select_books_query = """
SELECT COUNT(b.books_id) AS hits, b.*, a.* FROM books b
LEFT JOIN authors a ON b.books_author_id = a.authors_id
JOIN search s ON (LOWER(b.books_title) LIKE LOWER(s.term) OR LOWER(a.authors_first_name) LIKE LOWER(s.term) OR LOWER(a.authors_last_name) LIKE LOWER(s.term))
GROUP BY b.books_id
ORDER BY hits DESC;
"""
conn = DatabaseConnection()
conn.db_connect()
create_table = conn.db_write(query=create_terms_temp_table_query)
if create_table['result']:
insert_terms = conn.db_write(query=insert_terms_query)
if insert_terms['result']:
resp = conn.db_read(query=select_books_query, format_type="book")
conn.db_write(query=drop_terms_temp_table_query)
return make_response({"books": resp}, 200) ##jsonify({"books": resp })
else:
return make_response("query terms insert failed", 500)
else:
return make_response("failed to create terms table", 500)
def delete_author():
decoded_token = au.validate_jwt_token(request.cookies.get('token'))
if decoded_token['role'] == 'admin':
query = "DELETE FROM authors WHERE authors_id=%s"
values = (request.json["author_id"], )
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_write(query=query, vals=values)
conn.db_close()
return resp
else:
return Response(status=401)
def delete_book(id):
decoded_token = au.validate_jwt_token(request.cookies.get('token'))
if decoded_token['role'] == 'admin':
query = "DELETE FROM books WHERE books_id=%s"
values = (id,)
conn = DatabaseConnection()
conn.db_connect()
result = conn.db_write(query = query, vals = values)
conn.db_close()
return Response(status=result['response'])
else:
return Response(status=401)
def register_user():
user_email = request.json["email"]
user_password = request.json["password"]
user_confirm_password = request.json["confirm_password"]
if user_password == user_confirm_password and au.validate_user_input(
'authentication', email=user_email, password=user_password):
password_salt = au.generate_salt()
password_hash = au.generate_hash(user_password, password_salt)
query = "INSERT INTO users (users_email, users_password, users_password_salt, users_role) VALUES (%s, %s, %s, 'admin')"
values = (user_email, password_hash, password_salt)
conn = DatabaseConnection()
conn.db_connect()
created = conn.db_write(query=query, vals=values)
if created['result']:
user_query = "SELECT * FROM users WHERE users_email = %s"
current_user = conn.db_read(query=user_query,
vals=(user_email, ),
format_type="user")
if len(current_user) > 0:
user_info = current_user[0]
user_token = au.validate_user(current_user=user_info,
password=user_password)
if user_token:
expire_date = datetime.datetime.now()
expire_date = expire_date + datetime.timedelta(days=365)
resp = make_response(
{
"user": {
"email": user_info['users_email'],
"id": user_info['users_id'],
"user_role": user_info['users_role']
}
}, 200)
resp.headers["content-type"] = "application/json"
resp.set_cookie('token',
user_token,
secure=True,
httponly=False,
expires=expire_date)
return resp
else:
return Response(status=401)
else:
return Response(status=400)
else:
return Response(status=400)
def create_author():
decoded_token = au.validate_jwt_token(request.cookies.get('token'))
if decoded_token['role'] == 'admin':
last_name = request.json['last_name']
first_name = request.json['first_name']
query = "INSERT INTO authors(authors_last_name, authors_first_name) VALUES(%s, %s);"
values = (last_name, first_name)
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_write(query=query, vals=values)
conn.db_close()
return jsonify({"authors": resp})
else:
return Response(status=401)
def update_author():
decoded_token = au.validate_jwt_token(request.json["jwt_token"])
if decoded_token['role'] == 'admin':
data = request.json['form_input']
author_id = data['author_id']
last_name = data['last_name']
first_name = data['first_name']
query = "UPDATE authors SET authors_last_name=%s, authors_first_name=%s WHERE authors_id=%s;"
values = (last_name, first_name, author_id)
conn = DatabaseConnection()
conn.db_connect()
resp = conn.db_write(query=query, vals=values)
conn.db_close()
return resp
else:
return Response(status=401)
def update_user_password():
user_id = request.json['id']
old_password = request.json['password']
new_password = request.json['new_password']
confirm_password = request.json['confirm_password']
query = "SELECT * FROM users WHERE users_id = %s"
conn = DatabaseConnection()
conn.db_connect()
current_user = conn.db_read(query=query,
vals=(user_id, ),
format_type="user")
if len(current_user) > 0:
user_info = current_user[0]
user_password = user_info['users_password']
user_password_salt = user_info['users_password_salt']
old_password_hash = au.generate_hash(old_password, user_password_salt)
if old_password_hash == user_password:
if new_password == confirm_password:
new_password_hash = au.generate_hash(new_password,
user_password_salt)
update_query = "UPDATE users SET users_password = %s WHERE users_id = %s"
values = (new_password_hash, user_id)
conn.db_write(query=update_query, vals=values)
conn.db_close()
return Response(status=200)
else:
conn.db_close()
return Response(status=406)
else:
conn.db_close()
return Response(status=401)
else:
conn.db_close()
return Response(status=404)