def attach_editor_to_book(user_id, book_id, is_main=False):
context = DbContext()
query = "INSERT INTO book_editor VALUES ({user_id}, {book_id}, %s)".format(
user_id=user_id, book_id=book_id)
context.update(query, [is_main])
del context
def delete_author(author_id):
context = DbContext()
query = "DELETE FROM author WHERE author_id = {author_id}".format(
author_id=author_id)
context.update(query, ())
del context
def delete_book(book_id):
context = DbContext()
query = "DELETE FROM book WHERE book_id = {book_id}".format(
book_id=book_id)
context.update(query, ())
del context
def change_editor_status(editor_id, book_id, status):
context = DbContext()
if status == 1:
query = f'INSERT INTO book_editor VALUES ({editor_id}, {book_id}, false)'
elif status == 2:
query = f'DELETE FROM book_editor WHERE editor_id = {editor_id} AND book_id = {book_id}'
else:
raise Exception("Invalid editor status")
context.update(query, ())
del context
def update_book_and_return_updated(book):
context = DbContext()
# Check all authors exist
for i in range(len(book['authors'])):
query = "SELECT author_id FROM author WHERE author_full_name = %s"
result = context.select(query, [book['authors'][i]['author_name']])
if len(result) == 0:
return book, 1
else:
book['authors'][i]['author_id'] = result[0][0]
# Delete old book -> authors relation and add new
query = "DELETE FROM author_book WHERE book_id = {book_id}".format(
book_id=book['book_id'])
context.update(query, ())
for author in book['authors']:
context.update(
"INSERT INTO author_book VALUES (%s, %s, %s)",
[author['author_id'], book['book_id'], author['author_fee']])
# Update book data
query = "UPDATE book SET circulation = {circ}, release_date = %s, cost_price = {cp}".format(
circ=book['circulation'], cp=book['cost_price'])
context.update(query, [book['release_date']])
query = "SELECT * FROM book WHERE book_id = {book_id}".format(
book_id=book['book_id'])
updated = context.select(query, ())
del context
return updated[0], 0
def update_author_and_return_updated(author_id, author):
context = DbContext()
query = "UPDATE author " \
"SET author_full_name = %s, " \
"author_itn = %s, " \
"author_passport = %s, " \
"author_address = %s, " \
"author_phone_number = %s " \
"WHERE author_id = {author_id}".format(author_id=author_id)
context.update(query, [
author['author_full_name'], author['author_itn'],
author['author_passport'], author['author_address'],
author['author_phone_number']
])
updated_author = context.select(
"SELECT * FROM author WHERE author_id = {author_id}".format(
author_id=author_id), ())
del context
return updated_author[0]
def add_book(book):
context = DbContext()
book, check_data_status = _check_book_data(book)
if check_data_status != 0:
return None, check_data_status
# Save book
query = "INSERT INTO book VALUES (DEFAULT, %s, %s, %s, %s)"
context.update(query, [
book['title'], book['circulation'], book['release_date'],
book['cost_price']
])
book_id = context.select("SELECT book_id FROM book WHERE book_title = %s",
[book['title']])[0][0]
# Save book -> authors relation
for author in book['authors']:
context.update("INSERT INTO author_book VALUES (%s, %s, %s)",
[author['author_id'], book_id, author['author_fee']])
del context
return book_id, 0
def add_author(author):
context = DbContext()
result = context.select(
"SELECT author_id FROM author WHERE author_full_name = %s",
[author['author_full_name']])
if len(result) == 0:
context.update(
"INSERT INTO author VALUES (DEFAULT, %s, %s, %s, %s, %s)", [
author['author_full_name'], author['author_itn'],
author['author_passport'], author['author_address'],
author['author_phone_number']
])
author_id = int(
context.select(
"SELECT author_id FROM author WHERE author_full_name = %s",
[author['author_full_name']])[0][0])
else:
author_id = 0
del context
return author_id
def update_employee_and_return_updated(employee_old, employee_new, update_password):
context = DbContext()
# Check password
if not check_password_hash(employee_old[10], employee_new['employee_password']):
return employee_old, 1
# Check employee with specified phone number does not exist
if int(employee_new['employee_phone_number']) != employee_old[5]:
query = "SELECT * FROM employee WHERE employee_phone_number = {phone}".format(
phone=employee_new['employee_phone_number']
)
if len(context.select(query, ())) > 0:
return employee_old, 2
# Save new data
query = "UPDATE employee SET " \
"employee_full_name = %s, " \
"employee_itn = {itn}, " \
"employee_passport = {passport}, " \
"employee_address = %s, " \
"employee_phone_number = {phone}, " \
"employee_sex = %s, " \
"employee_birthdate = %s, " \
"employee_salary = {salary} " \
"WHERE employee_id = {emp_id}".format(
itn=employee_new['employee_itn'],
passport=employee_new['employee_passport'],
phone=employee_new['employee_phone_number'],
salary=employee_new['employee_salary'],
emp_id=employee_old[0]
)
context.update(
query,
[
employee_new['employee_full_name'],
employee_new['employee_address'],
employee_new['employee_sex'],
employee_new['employee_birthdate']
]
)
# Update password if necessary
if update_password:
new_password_hash = generate_password_hash(employee_new['new_password'])
query = "UPDATE employee SET password_hash = %s WHERE employee_id = {emp_id}".format(
emp_id=employee_old[0]
)
context.update(query, [new_password_hash])
# Retrieve updated employee
query = "SELECT * FROM employee WHERE employee_id = {employee_id}".format(
employee_id=employee_old[0]
)
result = context.select(query, ())
del context
return result[0], 0