def delete(url):
name = url
is_book = db_session.query(Book.id).filter(Book.name == name).first()
is_author = db_session.query(Author.id).filter(Author.name == name).first()
if is_book:
methods.del_book(name)
return redirect(url_for('show_library'))
elif is_author:
methods.del_author(name)
return redirect(url_for('show_library'))
def delete(url):
name = url
is_book = db_session.query(Book.id).filter(Book.name == name).first()
is_author = db_session.query(Author.id).filter(Author.name == name).first()
if is_book:
methods.del_book(name)
return redirect(url_for("show_library"))
elif is_author:
methods.del_author(name)
return redirect(url_for("show_library"))
def del_author(author_name):
#SELECT ALL BOOK_ID FROM MATCHES WHERE AUTHOR_ID = HASH(AUTHOR_NAME)
author_id = db_session.query(Author.id).filter_by(name = author_name).first()
books_ids = db_session.query(Matches.book_id).filter_by(author_id = author_id[0]).all()
#FROM LIST OF TUPLES CREATE LIST OF BOOK_IDS
books_ids = [each[0] for each in books_ids]
#DELETE FROM MATCHES WHERE BOOK_ID IN BOOK_IDS
Matches.query.filter(Matches.book_id.in_((books_ids))).delete(synchronize_session='fetch')
Book.query.filter(Book.id.in_((books_ids))).delete(synchronize_session='fetch')
Author.query.filter(Author.id == author_id[0]).delete(synchronize_session='fetch')
db_session.commit()
def get_book(book_name):
book_id = db_session.query(Book.id).filter(func.lower(Book.name.like(func.lower("%{0}%".format(book_name))))).first()
if book_id:
book_id = book_id[0]
book_name = db_session.query(Book.name).filter(Book.id == book_id).first()
author_id = db_session.query(Matches.author_id).filter(Matches.book_id == book_id).first()
author_id = author_id[0]
author_name = db_session.query(Author.name).filter(Author.id == author_id).first()
return (author_name[0], book_name[0])
else:
return False
def get_book_by_author(author_name):
author_id = db_session.query(Author.id).filter(func.lower(Author.name.like(func.lower("%{0}%".format(author_name))))).first()
if author_id:
author_id = author_id[0]
author_name = db_session.query(Author.name).filter(Author.id == author_id).first()
books_ids = db_session.query(Matches.book_id).filter_by(author_id = author_id).all()
books_ids = [each[0] for each in books_ids]
if books_ids:
books = db_session.query(Book.name).filter(Book.id.in_((books_ids))).all()
return (author_name[0], [each[0] for each in books])
else:
return False
def get_all_authors_and_books():
lib = {}
authors = db_session.query(Author.name).distinct().all()
authors = [each[0] for each in authors]
author_id_name = db_session.query(Author.id, Author.name).filter(Author.name.in_((authors))).all()
for author_id, author_name in author_id_name:
author_books = db_session.query(Matches.book_id).filter_by(author_id = author_id).all()
author_books = [each[0] for each in author_books]
books = db_session.query(Book.name).filter(Book.id.in_(author_books)).all()
books = [each[0] for each in books]
lib[author_name] = books
return lib
def del_book(book_name):
book_id = db_session.query(Book.id).filter_by(name = book_name).first()
author_id = db_session.query(Matches.author_id).filter(Matches.book_id == book_id[0]).all()
author_id = [each[0] for each in author_id][0]
author_books = db_session.query(Matches.book_id).filter_by(author_id = author_id).all()
author_books = [each[0] for each in author_books]
if len(author_books) == 1:
author_name = db_session.query(Author.name).filter(Author.id == author_id).all()
author_name = [each[0] for each in author_name][0]
del_author(author_name)
elif len(author_books) > 1:
Matches.query.filter(Matches.book_id == book_id[0]).delete(synchronize_session='fetch')
Book.query.filter(Book.name == book_name).delete(synchronize_session='fetch')
db_session.commit()
def del_author(author_name):
#SELECT ALL BOOK_ID FROM MATCHES WHERE AUTHOR_ID = HASH(AUTHOR_NAME)
author_id = db_session.query(Author.id).filter_by(name=author_name).first()
books_ids = db_session.query(
Matches.book_id).filter_by(author_id=author_id[0]).all()
#FROM LIST OF TUPLES CREATE LIST OF BOOK_IDS
books_ids = [each[0] for each in books_ids]
#DELETE FROM MATCHES WHERE BOOK_ID IN BOOK_IDS
Matches.query.filter(Matches.book_id.in_(
(books_ids))).delete(synchronize_session='fetch')
Book.query.filter(Book.id.in_(
(books_ids))).delete(synchronize_session='fetch')
Author.query.filter(Author.id == author_id[0]).delete(
synchronize_session='fetch')
db_session.commit()
def get_all_authors_and_books():
lib = {}
authors = db_session.query(Author.name).distinct().all()
authors = [each[0] for each in authors]
author_id_name = db_session.query(Author.id, Author.name).filter(
Author.name.in_((authors))).all()
for author_id, author_name in author_id_name:
author_books = db_session.query(
Matches.book_id).filter_by(author_id=author_id).all()
author_books = [each[0] for each in author_books]
books = db_session.query(Book.name).filter(
Book.id.in_(author_books)).all()
books = [each[0] for each in books]
lib[author_name] = books
return lib
def get_book(book_name):
book_id = db_session.query(Book.id).filter(
func.lower(Book.name.like(func.lower(
"%{0}%".format(book_name))))).first()
if book_id:
book_id = book_id[0]
book_name = db_session.query(
Book.name).filter(Book.id == book_id).first()
author_id = db_session.query(
Matches.author_id).filter(Matches.book_id == book_id).first()
author_id = author_id[0]
author_name = db_session.query(
Author.name).filter(Author.id == author_id).first()
return (author_name[0], book_name[0])
else:
return False
def save_author_book(author, book):
author_id = db_session.query(Author.id).filter(func.lower(Author.name) == func.lower(author)).first()
"""check if author is already present"""
if author_id:
save_book(book)
book_id = db_session.query(Book.id).filter(Book.name == book).first()
m = Matches(author_id=author_id[0], book_id=book_id[0])
db_session.add(m)
db_session.commit()
else:
save_author(author)
save_book(book)
author_id = db_session.query(Author.id).filter(Author.name == author).first()
book_id = db_session.query(Book.id).filter(Book.name == book).first()
m = Matches(author_id=author_id[0], book_id=book_id[0] )
db_session.add(m)
db_session.commit()
def check_if_admin(username, password):
find_password = db_session.query(User.password).filter(User.username == username).first()
if find_password:
find_password = find_password[0]
check = check_password_hash(find_password, password)
if check:
session["username"] = username
is_authorized = db_session.query(User.is_admin).filter(User.username == username).first()
is_authorized = is_authorized[0]
if is_authorized:
session["permission"] = "authenticated"
else:
session["permission"] = "authorized"
else:
session["permission"] = "incorrect"
else:
session["permission"] = "denied"
def get_book_by_author(author_name):
author_id = db_session.query(Author.id).filter(
func.lower(Author.name.like(func.lower(
"%{0}%".format(author_name))))).first()
if author_id:
author_id = author_id[0]
author_name = db_session.query(
Author.name).filter(Author.id == author_id).first()
books_ids = db_session.query(
Matches.book_id).filter_by(author_id=author_id).all()
books_ids = [each[0] for each in books_ids]
if books_ids:
books = db_session.query(Book.name).filter(Book.id.in_(
(books_ids))).all()
return (author_name[0], [each[0] for each in books])
else:
return False
def del_book(book_name):
book_id = db_session.query(Book.id).filter_by(name=book_name).first()
author_id = db_session.query(
Matches.author_id).filter(Matches.book_id == book_id[0]).all()
author_id = [each[0] for each in author_id][0]
author_books = db_session.query(
Matches.book_id).filter_by(author_id=author_id).all()
author_books = [each[0] for each in author_books]
if len(author_books) == 1:
author_name = db_session.query(
Author.name).filter(Author.id == author_id).all()
author_name = [each[0] for each in author_name][0]
del_author(author_name)
elif len(author_books) > 1:
Matches.query.filter(Matches.book_id == book_id[0]).delete(
synchronize_session='fetch')
Book.query.filter(Book.name == book_name).delete(
synchronize_session='fetch')
db_session.commit()
def check_if_admin(username, password):
find_password = db_session.query(
User.password).filter(User.username == username).first()
if find_password:
find_password = find_password[0]
check = check_password_hash(find_password, password)
if check:
session["username"] = username
is_authorized = db_session.query(
User.is_admin).filter(User.username == username).first()
is_authorized = is_authorized[0]
if is_authorized:
session["permission"] = "authenticated"
else:
session["permission"] = "authorized"
else:
session["permission"] = "incorrect"
else:
session["permission"] = "denied"
def save_author_book(author, book):
author_id = db_session.query(Author.id).filter(
func.lower(Author.name) == func.lower(author)).first()
"""check if author is already present"""
if author_id:
save_book(book)
book_id = db_session.query(Book.id).filter(Book.name == book).first()
m = Matches(author_id=author_id[0], book_id=book_id[0])
db_session.add(m)
db_session.commit()
else:
save_author(author)
save_book(book)
author_id = db_session.query(
Author.id).filter(Author.name == author).first()
book_id = db_session.query(Book.id).filter(Book.name == book).first()
m = Matches(author_id=author_id[0], book_id=book_id[0])
db_session.add(m)
db_session.commit()
def db_test_data():
admin_present = db_session.query(User).filter_by(username = '******').first()
users_present = db_session.query(User.id).distinct().all()
author_book_present = db_session.query(Author.name).distinct().all()
if admin_present:
users_present = [each[0] for each in users_present]
if len(users_present) >= 3:
pass
else:
add_user('Mary', '123')
add_user('John', '123')
else:
add_user('admin', '123', is_admin=True)
add_user('Mary', '123')
add_user('John', '123')
if author_book_present:
pass
else:
save_author_book('Terry Pratchett', 'Sourcery')
save_author_book('Terry Pratchett', 'Magic')
save_author_book('Victor Hugo', 'Notre-Dame de Paris')
def db_test_data():
admin_present = db_session.query(User).filter_by(username='******').first()
users_present = db_session.query(User.id).distinct().all()
author_book_present = db_session.query(Author.name).distinct().all()
if admin_present:
users_present = [each[0] for each in users_present]
if len(users_present) >= 3:
pass
else:
add_user('Mary', '123')
add_user('John', '123')
else:
add_user('admin', '123', is_admin=True)
add_user('Mary', '123')
add_user('John', '123')
if author_book_present:
pass
else:
save_author_book('Terry Pratchett', 'Sourcery')
save_author_book('Terry Pratchett', 'Magic')
save_author_book('Victor Hugo', 'Notre-Dame de Paris')
def add_record():
forbidden_pattern = "[0-9'\.&@:?!()$#^]"
form = AddRecordForm(request.form)
if (request.method == "POST") and form.validate():
book = form.book.data.strip()
author = (form.author.data).strip()
if re.match(forbidden_pattern, author):
flash("Author name cant be number")
return render_template("add.html", form=form)
else:
present_book = db_session.query(Book.id).filter(func.lower(Book.name) == func.lower(book)).first()
if present_book:
flash("Such book already exist in library")
return render_template("add.html", form=form)
else:
methods.save_author_book(author=author, book=book)
return redirect(url_for("show_library"))
elif request.method == "POST":
flash("Fill in fields, please")
return render_template("add.html", form=form)
return render_template("add.html", form=form)
def add_record():
forbidden_pattern = "[0-9'\.&@:?!()$#^]"
form = AddRecordForm(request.form)
if (request.method == 'POST') and form.validate():
book = form.book.data.strip()
author = (form.author.data).strip()
if re.match(forbidden_pattern, author):
flash("Author name cant be number")
return render_template('add.html', form=form)
else:
present_book = db_session.query(Book.id).filter(
func.lower(Book.name) == func.lower(book)).first()
if present_book:
flash('Such book already exist in library')
return render_template('add.html', form=form)
else:
methods.save_author_book(author=author, book=book)
return redirect(url_for('show_library'))
elif request.method == 'POST':
flash("Fill in fields, please")
return render_template('add.html', form=form)
return render_template('add.html', form=form)
def edit(prev_name, new_name):
is_book = db_session.query(Book.id).filter(Book.name == prev_name).first()
is_author = db_session.query(
Author.id).filter(Author.name == prev_name).first()
if is_author:
is_author = is_author[0]
check_new_name_id = db_session.query(Author.id).filter(
func.lower(Author.name) == func.lower(new_name)).first()
if check_new_name_id:
check_new_name_id = check_new_name_id[0]
prev_author_book_id = db_session.query(
Matches.book_id).filter_by(author_id=is_author).all()
prev_author_book_id = [each[0] for each in prev_author_book_id]
for each_book in prev_author_book_id:
m = Matches(author_id=check_new_name_id, book_id=each_book)
db_session.add(m)
db_session.commit()
Matches.query.filter(Matches.author_id == is_author).delete(
synchronize_session='fetch')
Author.query.filter(Author.id == is_author).delete(
synchronize_session='fetch')
db_session.commit()
else:
prev_author_id = db_session.query(
Author.id).filter_by(name=prev_name).first()
prev_author_id = prev_author_id[0]
db_session.query(Author).filter_by(id = prev_author_id).\
update({"name":new_name}, synchronize_session='fetch')
db_session.commit()
elif is_book:
check_new_name_id = db_session.query(Book.id).filter(
func.lower(Book.name) == func.lower(new_name)).first()
if check_new_name_id:
return False
else:
prev_book_id = db_session.query(
Book.id).filter_by(name=prev_name).first()
prev_book_id = prev_book_id[0]
db_session.query(Book).filter_by(id = prev_book_id).\
update({"name":new_name}, synchronize_session='fetch')
db_session.commit()
def edit(prev_name, new_name):
is_book = db_session.query(Book.id).filter(Book.name == prev_name).first()
is_author = db_session.query(Author.id).filter(Author.name == prev_name).first()
if is_author:
is_author = is_author[0]
check_new_name_id = db_session.query(Author.id).filter(func.lower(Author.name) == func.lower(new_name)).first()
if check_new_name_id:
check_new_name_id = check_new_name_id[0]
prev_author_book_id = db_session.query(Matches.book_id).filter_by(author_id = is_author).all()
prev_author_book_id = [each[0] for each in prev_author_book_id]
for each_book in prev_author_book_id:
m = Matches(author_id=check_new_name_id, book_id = each_book)
db_session.add(m)
db_session.commit()
Matches.query.filter(Matches.author_id == is_author).delete(synchronize_session='fetch')
Author.query.filter(Author.id == is_author).delete(synchronize_session='fetch')
db_session.commit()
else:
prev_author_id = db_session.query(Author.id).filter_by(name = prev_name).first()
prev_author_id = prev_author_id[0]
db_session.query(Author).filter_by(id = prev_author_id).\
update({"name":new_name}, synchronize_session='fetch')
db_session.commit()
elif is_book:
check_new_name_id = db_session.query(Book.id).filter(func.lower(Book.name) == func.lower(new_name)).first()
if check_new_name_id:
return False
else:
prev_book_id = db_session.query(Book.id).filter_by(name = prev_name).first()
prev_book_id = prev_book_id[0]
db_session.query(Book).filter_by(id = prev_book_id).\
update({"name":new_name}, synchronize_session='fetch')
db_session.commit()
