def get_list(username, list_name):
if list_name not in ('favorites', 'have-read', 'to-read'):
abort(404)
user = get_user(username)
if not user:
abort(404)
tables = {
'favorites': 'users_favorites',
'have-read': 'users_have_read',
'to-read': 'users_to_read'
}
statement = """
select * from %s list
join books b on list.book_id = b.book_id
join books_authors ba on b.book_id = ba.book_id
join authors a on a.author_id = ba.author_id
where (list.user_id = %s);"""
book_list = execute_statement(statement,
(AsIs(tables[list_name]), user['user_id']),
True)
if book_list:
return jsonify({"data": book_list, "status": True}), 200
else:
return jsonify({
"status": False,
"message": "Could not get list data."
}), 200
def delete_book():
if 'book_id' not in request.json:
return jsonify({"status": False, "message": "Book id is missing."})
statement = """delete from books where book_id = %s"""
status = execute_statement(statement, (request.json['book_id'], ))
if status:
return jsonify({"status": True}), 200
else:
return jsonify({"status": False, "message": "Could not delete book."})
def get_books(author_id):
statement = """select b.* from
books b join books_authors ba on b.book_id = ba.book_id
join authors a on ba.author_id = a.author_id
where a.author_id = %s"""
data = execute_statement(statement, (author_id, ), True)
if data:
return jsonify({'status': True, 'data': data})
else:
return jsonify({'status': False, 'message': "Author books not found."})
def search_authors():
value = request.json['value'].lower()
statement = """select * from authors a
join birthplaces bp on bp.birthplace_id = a.birthplace_id
where lower(name) like """ + "'%%" + '%s' + "%%'"
data = execute_statement(statement, (AsIs(value), ), True)
if data:
return jsonify({"status": True, "data": data}), 200
else:
return jsonify({"status": False, "message": "Could not found."})
def get_book_with_id(book_id):
statement = """select b.*, a.* from
books b join books_authors ba on b.book_id = ba.book_id
join authors a on ba.author_id = a.author_id
where (b.book_id = %s);"""
data = execute_statement(statement, (book_id, ), True, False)
if data:
return jsonify({"status": True, "data": data}), 200
else:
return jsonify({"status": False, "message": "Book not found."})
def get_birthplace_with_id(bp_id):
statement = """select * from birthplaces
where (birthplace_id = %s)"""
birthplace = execute_statement(statement, (bp_id, ), True, False)
if birthplace:
return jsonify({'status': True, 'data': birthplace}), 200
else:
return jsonify({
'status': False,
'message': "Birthplace not found."
}), 200
def create_book():
data = request.json['data']
print("---------------------")
print(data)
# check if author id is present in request.data
if 'author_id' not in data:
return jsonify({'status': False, 'message': 'Author id is not set.'})
get_author = "select * from authors where author_id = %s"
author = execute_statement(get_author, (data['author_id'], ), True, False)
print("---------------------")
print("author: ", author)
if not author or 'author_id' not in author:
return jsonify({'status': False, 'message': 'Author id is not valid.'})
# author id is valid here
# create the book
statement = """insert into books (title, pages, publish_date, publish_place, publisher)
values (%s, %s, %s, %s, %s) returning book_id;"""
params = (data['title'], data['pages'], data['publish_date'],
data['publish_place'], data['publisher'])
book = execute_statement(statement, params, True, False)
print("---------------------")
print(book)
# check if book is created succesfully
if not book or 'book_id' not in book:
return jsonify({'status': False, 'message': 'Book cannot be created'})
# insert book_id author_id values to books_authors
insert_statement = """insert into books_authors (book_id, author_id)
values (%s, %s);"""
params = (book['book_id'], author['author_id'])
print("---------------------")
print(params)
inserted = execute_statement(insert_statement, params)
if not inserted:
return jsonify({
'status':
False,
'message':
'Book is created but could not matched with author.'
})
else:
return jsonify({'status': True, 'book_id': book['book_id']}), 201
def delete_author():
if 'author_id' not in request.json:
return jsonify({"status": False, "message": "Author id is missing."})
statement = """delete from authors where author_id = %s"""
status = execute_statement(statement, (request.json['author_id'], ))
if status:
return jsonify({"status": True}), 200
else:
return jsonify({
"status": False,
"message": "Could not delete author."
})
def update_author(author_id):
get_author = """select * from authors where author_id = %s limit 1"""
author = execute_statement(get_author, (author_id, ), True, False)
if not author:
return jsonify({
'status': False,
'message': 'Author does not exists.'
}), 200
print("---------")
print(request.json)
update = {**author, **request.json['data']}
statement = "update authors set "
for key in update:
if key is 'author_id':
continue
statement += key + ' = ' + ' %s, '
statement = statement[:len(statement) - 2] + " where (author_id = %s)"
params = tuple(update[key] for key in update
if key is not 'author_id') + (author['author_id'], )
status = execute_statement(statement, params, False)
return jsonify({'status': status}), 200
def search_birthplaces():
value = request.json['value'].lower()
print(request.json)
statement = """select * from birthplaces
where lower(birthplace) like """ + "'%%" + '%s' + "%%'"
data = execute_statement(statement, (AsIs(value), ), True)
if data:
return jsonify({"status": True, "data": data}), 200
else:
return jsonify({"status": False, "message": "Could not found."})
#TODO: filter authors
def search_books():
value = request.json['value'].lower()
print("SEARCH VALUE: ", value)
statement = """select b.*, a.* from
books b join books_authors ba on b.book_id = ba.book_id
join authors a on ba.author_id = a.author_id
where lower(title) like """ + "'%%" + '%s' + "%%'"
data = execute_statement(statement, (AsIs(value), ), True)
print(data)
if data:
return jsonify({"status": True, "data": data}), 200
else:
return jsonify({"status": False, "message": "Could not find."})
def get_author_with_id(author_id):
statement = """select * from authors a
join birthplaces bp on bp.birthplace_id = a.birthplace_id
where (author_id = %s)"""
author = execute_statement(statement, (author_id, ), True, False)
if author:
author['birth_date'] = author['birth_date'].strftime(
'%Y-%m-%d') if author['birth_date'] else None
author['death_date'] = author['death_date'].strftime(
'%Y-%m-%d') if author['death_date'] else None
return jsonify({'status': True, 'data': author})
else:
return jsonify({'status': False, 'message': "Author not found."})
def update_user(username):
user = get_user(username)
print(request.json)
update = {**user, **request.json['data']}
statement = "update users set "
for key in update:
statement += key + ' = ' + ' %s, '
statement = statement[:len(statement) -
2] + " where (user_id = %s and username = %s)"
params = tuple(update[key]
for key in update) + (user['user_id'], user['username'])
status = execute_statement(statement, params, False)
return jsonify({'status': status}), 200
def create_author():
data = request.json['data']
print(data)
statement = """insert into authors (name, gender, birthplace_id, birth_date, death_date)
values (%s, %s, %s, %s, %s) returning author_id;"""
params = (data['name'], data['gender'], data['birthplace_id'],
data['birth_date'], data['death_date'])
author = execute_statement(statement, params, True, False)
# author_id = execute_statement("""select author_id from author
# where (name = %s and gender = %s and birthplace_id = %s and birth_date = %s and death_date = %s);""")
print(author)
if author:
return jsonify({'status': True, 'author_id': author['author_id']}), 201
else:
return jsonify({'status': False})
def delete_user():
print("(--------------------)")
print(request.json)
if 'user_id' not in request.json:
return jsonify({"status": False, "message": "User id is missing."})
# check if the user is deleting other users
token = request.headers['Authorization']
user = is_token_valid(token)
if user['user_id'] != request.json['user_id']:
return jsonify({"status": False, "message": "User is unauthorized."})
statement = """delete from users where user_id = %s"""
status = execute_statement(statement, (request.json['user_id'], ))
if status:
return jsonify({"status": True}), 200
else:
return jsonify({"status": False, "message": "Could not delete user."})
def delete_book_from_list(username, list_name):
if list_name not in ('favorites', 'have-read', 'to-read'):
abort(404)
req = request.json
user = get_user(username)
if not user:
abort(404)
tables = {
'favorites': 'users_favorites',
'have-read': 'users_have_read',
'to-read': 'users_to_read'
}
statement = """delete from %s where (user_id = %s and book_id = %s);"""
status = execute_statement(
statement, (AsIs(tables[list_name]), user['user_id'], req['book_id']),
False)
message = 'Book succesfully deleted from the list.' if status else 'Could not delete book from the list.'
return jsonify({'status': status, 'message': message}), 200
def add_book_to_list(username, list_name):
if list_name not in ('favorites', 'have-read', 'to-read'):
abort(404)
req = request.json
user = get_user(username)
if not user:
abort(404)
tables = {
'favorites': 'users_favorites',
'have-read': 'users_have_read',
'to-read': 'users_to_read'
}
statement = """insert into %s (user_id, book_id) values (%s, %s);"""
status = execute_statement(
statement, (AsIs(tables[list_name]), user['user_id'], req['book_id']),
False)
message = 'Book succesfully added to the list.' if status else 'Could not add book to the list.'
return jsonify({'status': status, 'message': message}), 200
def is_unique(table, column, value):
statement = "select * from %s where (%s = %s) limit 1;"
params = (AsIs(table), AsIs(column), value)
data = execute_statement(statement, params, True, False)
print("is unique: ", data)
return
def update_book(book_id):
get_book = """select * from books where book_id = %s limit 1"""
book = execute_statement(get_book, (book_id, ), True, False)
if not book:
return jsonify({
'status': False,
'message': 'Book does not exists.'
}), 200
print("---------------------")
print("request.data: ", request.json['data'])
update = {**book, **request.json['data']}
# if author is changed, delete it for now
# we will deal with it after updating the books table
if 'author_id' in update:
del update['author_id']
print("---------------------")
print("update: ", update)
statement = "update books set "
for key in book:
if key is 'book_id':
continue
statement += key + ' = ' + ' %s, '
statement = statement[:len(statement) - 2] + " where (book_id = %s)"
params = tuple(update[key] for key in update
if key is not 'book_id') + (book['book_id'], )
# update book
status = execute_statement(statement, params, False)
# check updates on book's author
# this is done if book is succesfully updated and author id is sent in request
if status and 'author_id' in request.json['data']:
new_author_id = request.json['data']['author_id'] # from request
# check if new author id from request is valid
get_new_author_id = "select author_id from authors where author_id = %s"
new_author = execute_statement(get_new_author_id, (new_author_id, ),
True, False)
print("---------------------")
print("new author: ", new_author)
if not new_author or 'author_id' not in new_author:
return jsonify({
'status': False,
'message': 'Author id is not valid.'
})
# new author id is valid here
new_author_id = new_author['author_id']
get_current_author_id = """select a.author_id from
books b join books_authors ba on b.book_id = ba.book_id
join authors a on ba.author_id = a.author_id
where (b.book_id = %s) limit 1;"""
current_author = execute_statement(get_current_author_id,
(book['book_id'], ), True, False)
print("current author: ", current_author)
# if book does not have an author or it is updated
if not current_author or 'author_id' not in current_author or current_author[
'author_id'] != new_author_id:
print("new author is assigning to the book")
# match book and author inserting the relation to books_authors
insert_statement = """update books_authors set author_id=%s where book_id = %s;"""
inserted = execute_statement(insert_statement,
(new_author_id, book['book_id']))
if not inserted:
return jsonify({
'status':
False,
'message':
'Cannot match the book and the author.'
})
return jsonify({'status': status}), 200
