def post(self):
"""User sign up"""
userData = request.get_json()
firstname = userData['firstname']
secondname = userData['secondname']
confirmPassword = userData['confirm_password']
phone = userData['phone']
email = userData['email']
password = userData["password"]
if email == "" or phone.strip() == ""\
or confirmPassword.strip() == "" or password.strip() == ""\
or firstname.strip() == "" or secondname.strip() == "":
return {"message": "Please ensure all fields are non-empty."}, 400
if len(password) < 6:
return {'message': 'password should be 6 characters or more.'}, 400
if not validate_email(email):
return {"message": "Email is invalid"}, 400
if not password == confirmPassword:
return {'message': 'Passwords do not match'}, 400
try:
query = "select email from users where email='%s'\
or phone='%s'" % (email, phone)
result = db.execute(query)
user = result.fetchone()
if user is None:
if userData['driver'] == True:
userObject = Driver(userData)
userObject.save()
else:
userObject = Passenger(userData)
userObject.save()
return {'message': 'Account created.'}, 201
return {'message': 'User exists.'}, 409
except Exception as e:
print(e)
return {'message': 'Request not successful'}, 500
def book(book_id):
""" This function going to add reviews submited by the user and desplaying information about each book"""
form = ReviewSumbission()
if form.validate_on_submit():
db.execute(
"INSERT INTO reviews(review, stars, book_id, user_id) VALUES (:review, :stars, :book_id, :user_id)",
{
"review": form.review.data,
"stars": float(form.select.data),
"book_id": book_id,
"user_id": session['user_id']
})
db.commit()
#return redirect(url_for('book'))
# quering the data base for the book information and then rendring a page with all the info.
book_info = db.execute("SELECT * FROM books WHERE books.id= :id", {
"id": book_id
}).fetchall()[0]
book_isbn = book_info['isbn']
reviwes = db.execute("SELECT * FROM reviews WHERE reviews.book_id = :id", {
"id": book_id
}).fetchall()
rating = db.execute(
"SELECT AVG(stars) as rating FROM reviews WHERE reviews.book_id = :id",
{
"id": book_id
}).fetchall()[0]['rating']
review_user = db.execute(
"SELECT * FROM reviews WHERE reviews.book_id = :id and reviews.user_id = :user_id",
{
"id": book_id,
"user_id": session['user_id']
}).fetchall()
## get the goodreads infromations
res = requests.get("https://www.goodreads.com/book/review_counts.json",
params={
"key": KEY,
"isbns": book_isbn
})
data = res.json()
data = data['books'][0]
return render_template('book.html',
book_info=book_info,
rating=rating,
review_user=review_user,
reviwes=reviwes,
form=form,
data=data)
def login():
if session.get('username'):
return redirect(url_for('index'))
form = LoginForm()
if form.validate_on_submit():
email = form.email.data
password = form.password.data
user = db.execute("SELECT * FROM users WHERE email = :email", {
"email": email
}).fetchone()
if user and check_password_hash(user.password, password):
flash(f"{user.first_name}, you are sucessfully logged in!",
"success")
session['user_id'] = user.id
session['username'] = user.first_name
return redirect(url_for('index'))
else:
flash("Sorry, wrong username or password.", "danger")
return render_template("login.html", title="login", form=form, login=True)
def api_logged_in() -> bool:
"""
Check whether the user is logged in, using API key or session
"""
# Check session
if session and "user_id" in session and session["user_id"] > 0:
return True
# Get API key
key = None
if request.method == "GET" and "key" in request.args:
key = request.args["key"]
if request.method == "POST" and "key" in request.form:
key = request.form["key"]
if key is None:
return False
# Check API key
from application import db
user = db.execute("SELECT * FROM users WHERE api=?", request.args["key"])
return len(user) == 1
def checkDb(self):
check = db.execute("SELECT * FROM users WHERE email = :email", {
"email": self.email.data.lower()
}).rowcount
db.commit()
return check
def update_diary_entry(self, entry_id):
""" get diary entry by id """
query = "select * from entries where entry_id='{}'".format(entry_id)
result = db.execute(query)
entry = result.fetchone()
""" if not found """
if entry is None:
return {'message': 'diary entry with given id does not exist'}, 404
current_user_email = get_jwt_identity()
query = "select user_id from users where email='{}'"\
. format(current_user_email)
result = db.execute(query)
user_id = result.fetchone()
""" if entry owner id doesnot match id of current user """
if not entry[1] == user_id[0]:
return {'message': 'You cannot change \
details of diary entry that you do not own'}, 401
try:
data = request.get_json()
title_json = data['title']
body_json = data['body']
title = title_json.strip()
body = body_json.strip()
""" validate for duplicate entry titles """
query = "select * from entries where title='{}'".format(title)
result = db.execute(query)
rows = result.fetchall()
if(len(rows) > 0):
return {'message': 'diary entry with such title already exists'}, 403
""" validate for alphanumeric characters """
'''
if not re.match('^[a-zA-Z0-9_]+$',title):
return {'message':
'title can only be letters or numbers.'}, 403
'''
""" validate for empty fields """
if title and body:
""" retrieve date_created field of this entry """
date_created = entry[4]
""" convert string to date type """
datetime_obj = datetime.datetime.strptime(
date_created, '%Y-%m-%d').date()
""" compare date_created with current date """
if datetime_obj == datetime.date.today():
date_modified = datetime.date.today()
query = "update entries set title='{}',body='{}', date_modified='{}' where entry_id='{}'"\
.format(data['title'], data['body'], date_modified, int(entry_id))
db.execute(query)
return {'message': 'diary entry updated succesfully'}, 200
else:
return {'message': 'diary entry can only be updated on the day it was created'}, 403
else:
return {'message': 'Missing title or body fields.'}, 403
query = "select * from entries where entry_id='{}'".format(
entry_id)
result = db.execute(query)
entry = result.fetchone()
return {'id': entry[0], 'title': entry[2], 'body': entry[3], 'date_created': entry[4], 'date_modified': entry[5]}
except (KeyError):
return {'message': 'missing title or body keys'}, 403
def validate_email(self, field):
if db.execute("SELECT * FROM users WHERE email = :email", {
'email': field.data
}).fetchone():
raise ValidationError('Email already registered.')
def home():
form = SearchForm()
ids = tuple([random.randint(1, 5000) for _ in range(10)])
result = db.execute("SELECT * FROM books WHERE books.id IN :ids",
{"ids": ids})
return render_template("home.html", form=form, result=result)
def book(book_isbn):
form = ReviewForm()
# call application own API to get book details
r_api = requests.get(f'{api_host}{book_isbn}')
if r_api.status_code != 200:
return abort(404)
try:
book_json = r_api.json()
except JSONDecodeError:
return abort(500)
# call goodreads API to get book details
try:
r_gr_api = requests.get(
"https://www.goodreads.com/book/review_counts.json",
params={"key": gr_api_key, "isbns": book_isbn})
except requests.RequestException:
r_gr_api = None
try:
gr_api_json = r_gr_api.json()
except JSONDecodeError:
gr_api_json = None
# check for book id
book_id = db.execute(
'SELECT id FROM public.book '
'WHERE isbn = :isbn',
{'isbn': book_json['isbn']}
).fetchone()
# insert or update review if form submitted
if request.method == 'POST' and form.validate_on_submit():
db.execute(
'INSERT INTO public.user_book (user_id, book_id, score, review) '
'VALUES (:user_id, :book_id, :score, :review) '
'ON CONFLICT (user_id, book_id) '
'DO UPDATE SET score = :score, review = :review',
{"user_id": session['user_id'],
"book_id": book_id[0],
"score": form.rating.data,
"review": form.review.data
})
db.commit()
return redirect(url_for('book', book_isbn=book_isbn))
# get reviews (newest first)
r_q = db.execute(
'SELECT public.user_book.*, '
'public.user.name '
'FROM public.user_book '
'JOIN public.user '
'ON public.user.id = public.user_book.user_id '
'WHERE book_id = :book_id '
'ORDER BY (id) DESC ',
{"book_id": book_id[0]}
).fetchall()
# build list of reviews and check if current user already send a review
reviews = []
already_review = False
for r_api in r_q:
if r_api[5] == session['user']:
already_review = True
form.review.default = r_api[4]
form.rating.default = int(r_api[3])
form.process()
reviews.append(
{'user': r_api[5], 'rating': r_api[3], 'review': r_api[4]})
return render_template(
'book.html',
form=form,
book_json=book_json,
gr_api_json=gr_api_json,
reviews=reviews,
already_review=already_review
)
import csv
from application import db
f = open("books.csv")
reader = csv.reader(f)
for isbn, title, author, year in reader:
db.execute(
'INSERT INTO books(isbn, title, author, year) VALUES (:isbn, :title, :author, :year)',
{
'isbn': isbn,
'title': title,
'author': author,
'year': year
})
db.commit()
def post(self, ride_id):
"""Sends user request to join a ride offer"""
try:
# sample user
current_user_email = get_jwt_identity()
# Get user ID
query = "SELECT users.user_id \
from users where email='{}'"\
. format(current_user_email)
result = db.execute(query)
user_row = result.fetchone()
if user_row is None:
return {'message': 'User not found'}, 404
user_id = user_row[0]
# Find the particular ride offer to check its availability
query = "SELECT * from rides where ride_id = '{}'" . format(
ride_id)
result = db.execute(query)
row = result.fetchone()
if row is None:
return {'message': 'That ride does not exist'}, 404
# check whether this ride offer belongs to the user
if user_id == row[1]:
return {'message':
'You cannot request to join your own offer'}, 403
request_data = request.get_json()
if 'pick-up point' not in request_data or 'drop-off point' not in request_data or\
'seats_booked' not in request_data:
return {'message': 'provide pick-up and drop-off points, and number of seats you want to book. '}, 400
pick_up = request_data['pick-up point']
drop_off = request_data['drop-off point']
seats_booked = request_data['seats_booked']
status = 'pending'
# # check whether ride offer has any remaining space
if row[6] < seats_booked:
return {'message': 'No available space for you.'}, 403
# check whether ride offer is expired
time = (row[4])
if time > datetime.now():
# check whether users has alread requested given ride offer
query = "SELECT * from requests where user_id = (SELECT users.user_id \
from users where email='{}') and ride_id = {}"\
. format(current_user_email, ride_id)
result = db.execute(query)
result = result.fetchone()
if result is None:
# save user requests now
query = "INSERT INTO requests (date_created, ride_id, user_id, pick_up_point,\
drop_off_point, seats_booked, status)\
values('{}', '{}', '{}', '{}', '{}', '{}', '{}')"\
. format(datetime.now(), ride_id, user_id, pick_up, drop_off, \
seats_booked, status)
db.execute(query)
return {'message': 'Your request has been recorded.'}, 201
# user has already requested to join this ride offer
return{'message': 'You already requested this ride.'}, 403
else:
return {'message':
'The ride requested has already expired'}, 403
except Exception as e:
print(e)
return {'message': 'Request not successful.'}, 500
def validate_email(self, email):
if db.execute("SELECT email FROM users WHERE email = :email", {
"email": email.data
}).rowcount != 0:
raise ValidationError("Email is already in use. Pick another one.")
def idDb(self):
id = db.execute("SELECT id FROM books WHERE isbn = :isbn", {
"isbn": self.isbn
}).fetchall()
db.commit()
return id[0].id
def checkDb(self, id):
check = db.execute("SELECT * FROM users WHERE id = :id", {
"id": id
}).fetchall()
db.commit()
return check
def checkDbEmail(self):
check = db.execute("SELECT * FROM users WHERE email = :email", {
"email": self.email.data.lower()
}).fetchall()
db.commit()
return check
def infoDb(self):
info = db.execute("SELECT * FROM users WHERE id = :id", {
"id": self.id
}).fetchall()
db.commit()
return info
import csv
from application import db
with open('books.csv') as file:
reader = csv.reader(file)
for isbn, title, author, year in reader:
db.execute(
"INSERT INTO Books (isbn, title, author, year) VALUES(:isbn, :title, :author, :year) where isbn != :isbn",
{
"isbn": isbn,
"title": title,
"author": author,
"year": year,
"isbn": isbn
})
print(
f"Book with isbn{isbn} and title {title} and author{author} and year {year} is added"
)
db.commit()
def put(self, request_id):
"""Driver can accept or reject the ride offer.And users can take an accepted request"""
try:
action = request.args['action']
response = ''
# check whether driver already accepted the offer.
query = "select status,seats_booked,ride_id from requests where\
req_id='{}'".format(request_id)
result = db.execute(query)
result_rows = result.fetchone()
query = "select available_space from rides where ride_id='{}'"\
.format((result_rows[2]))
result = db.execute(query)
seats = result.fetchone()
available_seats = seats[0]
print(action)
if action.lower() == 'taken':
query = "update requests set status='{}' where requests.req_id='{}'"\
. format('taken', int(request_id))
db.execute(query)
return {'message': 'Your request has been updated.'}
elif action.lower() == 'abandoned':
query = "update requests set status='{}' where requests.req_id='{}'"\
. format('abadoned', int(request_id))
db.execute(query)
return {'message': 'Your request has been updated.'}
# check for action to take
elif action.lower() == 'accept':
if result_rows[0] == 'accepted':
return {'message': 'You already accepted this user request'},403
action = 'accepted'
# Decrement the available seats by one
available_seats -= result_rows[1]
# set message to be returned to user after request update cycle
response = {'message': 'Request accepted'}
else:
# Reject an already accepted request; this means available seats should be incremented
if result_rows[0] == 'accepted' :
available_seats += result_rows[1]
response = {'message': 'Request canceled'}
action = 'canceled'
elif result_rows[0] == 'rejected':
return {'message': 'Request already rejected'}
elif result_rows[0] == 'taken':
return {'message': 'Reuest which is already taken cannot be modified.'}
else:
action = 'rejected'
response = {'message': 'Request rejected'}
query = "update requests set status='{}' where requests.req_id='{}'" \
. format(action, int(request_id))
db.execute(query)
query = "update rides set available_space='{}' where ride_id='{}'"\
.format(int(available_seats), result_rows[2])
db.execute(query)
return response
except Exception as e: return e, 500
def checkPswDb(self):
checkPsw = db.execute("SELECT * FROM users WHERE email = :email", {
"email": self.reset_email.data.lower()
}).fetchall()
db.commit()
return checkPsw
from application import db
import requests
#api_key = "3ZdRtZuPYHNnTuIgTI7QHw"
#res = requests.get("https://www.goodreads.com/book/review_counts.json", params={"key": api_key, "isbns": "0060096187"})
#data = res.json()
#name = 'YOUSEF'
#print(name.swapcase())
row = db.execute("select id from books where isbn = :isbn ", {
'isbn': "0380795272"
}).fetchone()
print(row[0])
def updatePsw(self, hash_pass, id):
db.execute("UPDATE users SET hash_psw = :hash_psw WHERE id = :id", {
"hash_psw": hash_pass,
"id": id
})
db.commit()
import csv
from application import db
with open("books.csv") as f:
reader = csv.reader(f)
# Skip header
next(reader)
for isbn, title, author, pub_year in reader:
db.execute(
"INSERT INTO books(isbn, title, author, pub_year) VALUES(:isbn, :title, :author, :pub_year)",
{
"isbn": isbn,
"title": title,
"author": author,
"pub_year": pub_year
})
db.commit()
import csv
from application import db
# same import and setup statements as above
f = open("books.csv")
reader = csv.reader(f)
for isbn, title, author, year in reader: # loop gives each column a name
db.execute(
"INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)",
{
"isbn": isbn,
"title": title,
"author": author,
"year": int(year)
}
) # substitute values from CSV line into SQL command, as per this dict
db.commit() # transactions are assumed, so close the transaction finished
def update_dyn_score(contest_id, problem_id, update_curr_user=True):
from application import db
"""
Updates the dynamic scoring of contest_id/problem_id, using the db object
For details see: https://www.desmos.com/calculator/eifeir81wk
https://github.com/jdabtieu/CTFOJ/issues/2
Prereqs for using this function: user solve entry must already be in contest_solved
"""
db.execute("BEGIN")
check = db.execute(
("SELECT * FROM contest_problems WHERE contest_id=:cid AND "
"problem_id=:pid"),
cid=contest_id,
pid=problem_id)
solves = len(
db.execute(
"SELECT user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid",
cid=contest_id,
pid=problem_id))
N_min = check[0]["score_min"]
N_max = check[0]["score_max"]
N_users = check[0]["score_users"]
d = 11 * math.log(N_max - N_min) + N_users
old_points = min(math.ceil(math.e**((d - solves + 1) / 11) + N_min), N_max)
new_points = min(math.ceil(math.e**((d - solves) / 11) + N_min), N_max)
point_diff = new_points - old_points
# Set new point value of problem
db.execute(("UPDATE contest_problems SET point_value=:pv WHERE "
"contest_id=:cid AND problem_id=:pid"),
pv=new_points,
cid=contest_id,
pid=problem_id)
if update_curr_user:
db.execute(
("UPDATE contest_users SET lastAC=datetime('now'), "
"points=points+:points WHERE contest_id=:cid AND user_id=:uid"),
cid=contest_id,
points=old_points,
uid=session["user_id"])
# Update points of all users who previously solved the problem
db.execute(("UPDATE contest_users SET points=points+:point_change "
"WHERE contest_id=:cid AND user_id IN "
"(SELECT user_id FROM contest_solved WHERE "
"contest_id=:cid AND problem_id=:pid)"),
point_change=point_diff,
cid=contest_id,
pid=problem_id)
db.execute("COMMIT")
def get_wishes_by_user(uid):
stmt = text("SELECT Wish.id FROM Wish WHERE Wish.account_id = " + uid)
res = db.execute(smtm)
def rejudge_contest_problem(contest_id, problem_id, new_flag):
from application import db
"""
Rejudges a contest problem
"""
data = db.execute(
"SELECT * FROM contest_problems WHERE contest_id=:cid AND problem_id=:pid",
cid=contest_id,
pid=problem_id)[0]
# Reset all previously correct submissions
db.execute((
"UPDATE contest_users SET points=points-:points WHERE user_id IN (SELECT "
"user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid)"
),
points=data["point_value"],
cid=contest_id,
pid=problem_id)
db.execute(
"UPDATE submissions SET correct=0 WHERE contest_id=:cid AND problem_id=:pid",
cid=contest_id,
pid=problem_id)
db.execute(
"DELETE FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid",
cid=contest_id,
pid=problem_id)
if data["score_users"] >= 0: # Reset dynamic scoring
update_dyn_score(contest_id, problem_id, update_curr_user=False)
# Set all new correct submissions
db.execute(("UPDATE submissions SET correct=1 WHERE contest_id=:cid AND "
"problem_id=:pid AND submitted=:flag"),
cid=contest_id,
pid=problem_id,
flag=new_flag)
db.execute((
"INSERT INTO contest_solved (user_id, contest_id, problem_id) "
"SELECT DISTINCT user_id, contest_id, problem_id FROM submissions WHERE "
"contest_id=:cid AND problem_id=:pid AND correct=1"),
cid=contest_id,
pid=problem_id)
if data["score_users"] == -1: # Instructions for static scoring
old_points = data["point_value"]
else: # Instructions for dynamic scoring
old_points = data["score_max"]
update_dyn_score(contest_id, problem_id, update_curr_user=False)
db.execute((
"UPDATE contest_users SET points=points+:points WHERE user_id IN (SELECT "
"user_id FROM contest_solved WHERE contest_id=:cid AND problem_id=:pid)"
),
points=old_points,
cid=contest_id,
pid=problem_id)
import csv
# Get database info from the main app
from application import db
# Open csv file
with open("books.csv") as csvfile:
# Read as a dictionary
reader = csv.DictReader(csvfile)
for row in reader:
db.execute(
"INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)",
{
"isbn": row["isbn"],
"title": row["title"],
"author": row["author"],
"year": row["year"]
})
print(row)
# Commit to db
db.commit()
