def reset_db():
"""
A "secret" route for resetting database specs and content.
"""
with app.app_context():
db = get_db()
with app.open_resource('DatabaseSpecs/database-definition-queries.sql',
mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
return "Database reset :)"
def signup():
form = RegistrationForm()
if form.validate_on_submit():
db = get_db()
# db.row_factory = sqlite3.Row
c = db.cursor()
error = None
# (Redundant) check for uesrname and password entries
if not form.username.data:
error = "Username is required."
elif not form.password.data:
error = "Password is required."
elif c.execute('SELECT id FROM Users WHERE username = ?',
(form.username.data,)).fetchone() is not None:
error = 'User {} already exists. Please try again with a different username, or log in.'.format(
form.username.data)
elif c.execute('SELECT id FROM Users WHERE email = ?',
(form.email.data,)).fetchone() is not None:
error = 'User with email {} already exists. Please try again with a different email, or log in.'.format(
form.email.data)
if error is None:
c.execute("""INSERT INTO Users (
'username',
'password',
'email',
'fName',
'lName',
'streetAddress',
'city',
'state',
'postCode')
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(form.username.data,
form.password.data,
form.email.data,
form.fName.data,
form.lName.data,
form.streetAddress.data,
form.city.data,
form.state.data,
form.postCode.data))
app.logger.info(
f"Signup: account created for {form.username.data}, " +
f"with User id {c.lastrowid}")
flash(f'Account created for {form.email.data}!', 'success')
session['user_num'] = c.lastrowid
c.execute("INSERT INTO Wishlists (userId) VALUES (?)", (c.lastrowid,)) # Default wishlist for user
db.commit()
return redirect(url_for('account'))
flash(error, 'warning')
return render_template('signup.html', form=form)
def remove_book():
db = get_db()
db.row_factory = sqlite3.Row
c = db.cursor()
bookID = req.args.get("bookRem")
c.execute("DELETE FROM UserBooks WHERE id = ?",
(bookID,))
new_points = c.execute("SELECT points FROM Users WHERE id = (?)", (session['user_num'],)).fetchone()['points'] - 0.1
c.execute("""UPDATE Users SET points = (?) WHERE id = (?)""", (new_points, session['user_num']))
db.commit()
db.close()
app.logger.info(f"Book {bookID} removed from user {session['user_num']}")
flash("Book removed from your BookSwap library.", "success")
return redirect('/my-books')
def remove_wish():
db = get_db()
db.row_factory = sqlite3.Row
c = db.cursor()
wishID = req.args.get("wishlistRem")
bookID = req.args.get("bookRem")
app.logger.info(f"Removing book {bookID} from wishlist {wishID} for " +
f" user [session['user_num']")
c.execute("DELETE FROM WishlistsBooks WHERE wishlistId = ? AND bookId = (SELECT id FROM Books WHERE title = ?)",
(wishID, bookID))
db.commit()
db.close()
return redirect('/wishlist')
def add_to_wish(bookid=None):
bsdb = get_bsdb()
db = get_db()
db.row_factory = sqlite3.Row
# Queries used for SELECTing and INSERTing
get_books_isbn_query = 'SELECT * FROM Books WHERE ISBN = ?'
get_wishlist_books_query = 'SELECT * FROM WishlistsBooks WHERE wishlistId = ? AND bookId = ?'
insert_wishlist_query = 'INSERT INTO WishlistsBooks (wishlistId, bookId) VALUES (?, ?)'
# Special path for browse-books route
if bookid is not None:
c = db.cursor()
c.execute(get_wishlist_books_query,
(session['user_num'], bookid))
# if the book was already in the wishlist, don't add it
if c.fetchall():
flash("Book already in your wishlist", "warning")
app.logger.warning(f"Book {bookid} already in " +
f"user {session['user_num']}'s wishlist")
# otherwise, add book to the wishlist
else:
c.execute(insert_wishlist_query,
(session['user_num'], bookid))
flash("Book added to your wishlist", "success")
app.logger.info(f"Book {id} successfully added to " +
f"user {session['user_num']}'s wishlist")
db.commit()
db.close()
return redirect(url_for('browse_books'))
data = req.args.get("isbn")
if data == "":
return redirect('/wishlist')
c = db.cursor()
c.execute(get_books_isbn_query, (data,))
bookId = c.fetchall()[0]['id']
# need to get Users Wishlist.id number
c.execute("""
SELECT
id
FROM
Wishlists
WHERE
Wishlists.userId = ?
""",
(session['user_num'],))
wishlist = c.fetchone()['id']
c.execute(get_wishlist_books_query,
(wishlist, bookId))
if not c.fetchall():
flash("Book successfully added to your wishlist", "success")
app.logger.info(f"Book {bookId} added to wishlist {wishlist}")
c.execute(insert_wishlist_query,
(wishlist, bookId))
else:
flash("Book already in your wishlist.", "warning")
app.logger.warning(f"Book {bookId} attempted to add to wishlist {wishlist}, but it was already in that list.")
db.commit()
db.close()
return redirect('/wishlist')