def entry_index():
"""
Display the default page for the entry interfaces.
:return: rendered template of entry index page
"""
if not check_moderator():
# deny access if not moderator
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry'))
return render_template('entry/index.html')
def poster_entry():
"""
Handle file uploads for poster data entry. POST request must contain fields mid and img. mid must be present in
movies relation and img must be an openable image file.
:return: rendered template including list of movies ids
"""
# check for authorization
if not check_moderator():
# deny access if not allowed
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/poster'))
# get the list of movies for dropdown
curs = db_connection.cursor()
if request.method == 'GET':
# handle get requests with blank message and movie dropdown
return render_template('entry/poster.html', message=None, image_name=None)
elif request.method == 'POST':
# handle post requests as upload
if 'img' not in request.files:
# show error if no file in post data
message = 'no file uploaded'
return render_template('entry/poster.html', message=message, image_name=None), 400
try:
# try to get field and file
mid = request.form['mid']
img = request.files['img']
except KeyError:
# show error if file not uploaded, or form data bad
message = 'bad form data'
return render_template('entry/poster.html', message=message, image_name=None), 400
if not re.match(r'[0-9]+', mid):
# test for mid being strictly numeric
message = 'mid must be numeric'
return render_template('entry/poster.html', message=message, image_name=None), 400
# check for file validity
if img.filename == '':
# show error for empty filename
message = 'no file selected'
return render_template('entry/poster.html', message=message, image_name=None), 400
# check that it is an image file
if not allowed_file(img.filename, IMAGES):
# not image file
message = 'must be one of these filetypes: ' + str(IMAGES)
return render_template('entry/poster.html', message=message, image_name=None), 400
try:
# create new filename
filename = mid_filename(mid)
# try to enter new filename in database
curs.execute('REPLACE INTO POSTER VALUES (?, ?, ?)', (mid, filename, session['uid']))
# don't commit yet, wait until file saves
except sqlite3.Error as err:
# should update if exists, so this is a real problem
db_connection.rollback()
# show error message on bad value
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/poster.html', message=message, image_name=None), 400
if img:
# save the file
img.save(os.path.join(POSTER_DIR, filename))
db_connection.commit()
# get the tuple
curs.execute('SELECT img FROM POSTER WHERE MID==?', (mid,))
filename = str(curs.fetchone()[0])
# show success message
message = 'successfully added poster file for movie id: ' + str(mid)
return render_template('entry/poster.html', message=message, image_name=filename), 201
else:
# image not saved, so rollback the database entry
db_connection.rollback()
# image not allowed
message = 'file must be png'
return render_template('entry/poster.html', message=message, image_name=None), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)
def acted_entry():
"""
Handle requests for entry into ACTED_IN relation. Post requests must have fields mid, uid, and character_role. MID
and UID must be present in MOVIE and USER. Must not be present already in the relation (i.e., no actors playing
multiple roles in the same movie).
:return: rendered template including dropdowns for mid and uid of movies and actors
"""
# check user authorization
if not check_moderator():
# deny access if not allowed
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/acted'))
# default empty message
message = None
# get movies for mid dropdown
curs = db_connection.cursor()
curs.execute("SELECT MID, title FROM MOVIE ORDER BY MID")
movies = curs.fetchall()
# get users for uid dropdown
curs.execute("SELECT ACTOR.UID, u_name FROM USER, ACTOR WHERE USER.UID == ACTOR.UID ORDER BY ACTOR.UID")
users = curs.fetchall()
if request.method == 'GET':
# handle get requests with empty message and movie/user dropdowns
return render_template('entry/acted.html', movies=movies, users=users, message=message)
elif request.method == 'POST':
# handle post requests as data entry
try:
# get required form fields
mid = request.form['mid']
uid = request.form['uid']
character_role = request.form['character_role']
except KeyError:
# show error message for missing fields
message = 'bad form data'
return render_template('entry/acted.html', movies=movies, users=users, message=message), 400
if not re.match(r'[0-9]+', mid):
# test for mid being strictly numeric
message = 'mid must be numeric'
return render_template('entry/acted.html', movies=movies, users=users, message=message), 400
if not re.match(r'[0-9]+', uid):
# test for uid being strictly numeric
message = 'uid must be numeric'
return render_template('entry/acted.html', movies=movies, users=users, message=message), 400
if (not re.match(r'[0-9a-zA-Z\'\- ]+', character_role)) or len(character_role) > 20:
# test character role for being alphanumeric with spaces, apostrophes, hyphens, or spaces
message = 'character_role must be alphanumeric with spaces, apostrophes, hyphens, or spaces less than 20 ' \
'characters'
return render_template('entry/acted.html', movies=movies, users=users, message=message), 400
try:
# try to enter the new role
curs.execute('INSERT INTO ACTED_IN VALUES (?, ?, ?)', (mid, uid, character_role))
db_connection.commit()
# get the newly entered row
inserted_row = curs.execute("SELECT * FROM ACTED_IN WHERE MID == ? AND UID == ?", (mid, uid))
# show success message
message = 'inserted new role successfully: ' + str(inserted_row.fetchone())
return render_template('entry/acted.html', movies=movies, users=users, message=message), 201
except sqlite3.Error as err:
# handle sql errors (probably mid, uid already existing)
db_connection.rollback()
# show error message on bad value
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/acted.html', movies=movies, users=users, message=message), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)
def review_entry():
"""
Handle review entry requests. Post requests must have fields: MID, UID, text, and rating. Uses current time as
created date. MID and UID must exist in movies and users respectively. Text can be empty, but must only contain
letters, numbers, spaces, and punctuation. Rating must be between 0 and 5.
:return: rendered template including list of movies and users
"""
# check that user is authorized
if not check_moderator():
# deny access if not allowed
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/review'))
# default empty message
message = None
# get movies for mid dropdown
curs = db_connection.cursor()
curs.execute("SELECT MID, title FROM MOVIE ORDER BY MID")
movies = curs.fetchall()
# get users for uid dropdown
curs.execute("SELECT UID, u_name FROM USER ORDER BY UID")
users = curs.fetchall()
if request.method == 'GET':
# handle get requests with empty message and movie/user dropdowns
return render_template('entry/review.html', movies=movies, users=users, message=message)
elif request.method == 'POST':
# handle post requests as data entry
try:
mid = request.form['mid']
uid = request.form['uid']
text = request.form['text']
rating = int(request.form['rating'])
except KeyError:
# show error message for missing fields
message = 'bad form data'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
if not re.match(r'[0-9]+', mid):
# test for mid being strictly numeric
message = 'mid must be numeric'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
if not re.match(r'[0-9]+', uid):
# test for uid being strictly numeric
message = 'uid must be numeric'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
if not re.match(r'[0-9a-zA-Z_.,"\'()!@$*=\-+&:]*', text):
# test for text matching only alphanumeric and punctuation
message = 'text must be alphanumeric with punctuation (no carats, braces, or octothorpes)'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
if rating < 0 or rating > 5:
# make sure rating is [0:5]
message = 'rating must be from zero to five'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
try:
# try to insert the new review entry
# pass now as created date for this review
curs.execute("INSERT INTO REVIEW VALUES (?, ?, ?, ?, strftime('%s', 'now'))",
(mid, uid, text, rating))
# commit changes
db_connection.commit()
# get the newly entered row
inserted_row = curs.execute("SELECT * FROM REVIEW WHERE MID == ? AND UID == ?", (mid, uid))
# show success message
message = 'inserted new review successfully: ' + str(inserted_row.fetchone())
return render_template('entry/review.html', movies=movies, users=users, message=message), 201
except sqlite3.Error as err:
# handle sql errors (probably mid, uid already existing)
db_connection.rollback()
# show error message on bad value
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/review.html', movies=movies, users=users, message=message), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)
def movie_entry():
"""
Handle requests for movie entries. Post requests must have fields: director_uid, title, release_date, and
entered_uid. release_date must be in the form of YYYY-mm-dd. Uses dbms default next MID for primary key, uses now
for the entered date.
:return: rendered template, including list of potential directors and entered_by values
"""
# check user authorization
if not check_moderator():
# deny access if not allowed
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/moderator'))
# default empty message
message = None
# get potential directors for listing in form
curs = db_connection.cursor()
curs.execute("SELECT DIRECTOR.UID, u_name FROM USER, DIRECTOR WHERE USER.UID == DIRECTOR.UID ORDER BY DIRECTOR.UID")
directors = curs.fetchall()
if request.method == 'GET':
# handle get requests with empty message and list of directors and admins
return render_template('entry/movie.html', directors=directors, message=message)
elif request.method == 'POST':
# handle post requests as data entry
try:
# try to get required form fields
director_uid = request.form['director_uid']
title = request.form['title']
# parse date into datetime object
release_date = datetime.strptime(request.form['release_date'], '%Y-%m-%d')
entered_uid = session['uid']
except KeyError:
# show error message if any fields missing
message = 'bad form data'
return render_template('entry/movie.html', directors=directors, message=message), 400
except ValueError:
# show error message if unable to parse date
message = 'bad date format'
return render_template('entry/movie.html', directors=directors, message=message), 400
if not re.match(r'[0-9]+', director_uid):
# show error message if director uid is not strictly numeric
message = "director uid must be numeric"
return render_template('entry/movie.html', directors=directors, message=message), 400
if len(title) < 1 or len(title) > 40:
# show error message if title is empty or longer than 40 characters
message = "title must be between 1 and 40 characters long (inclusive)"
return render_template('entry/movie.html', directors=directors, message=message), 400
try:
# try to insert the new movie entry
# pass null into MID to let dbms decide next value
curs.execute("INSERT INTO MOVIE VALUES (NULL, ?, ?, ?, ?, strftime('%s', 'now'))",
(director_uid, title, unix_time(release_date), entered_uid))
db_connection.commit()
# get the movie ID of the recently added movie
mid = curs.lastrowid
# get the inserted row for success message
inserted_row = curs.execute("SELECT * FROM MOVIE WHERE MID=?", (mid,))
message = 'inserted new movie successfully: ' + str(inserted_row.fetchone())
# display success message and already found list of admins and directors
return render_template('entry/movie.html', directors=directors, message=message), 201
except sqlite3.Error as err:
# handle error (like director foreign key constraint or entered_by foreign key)
db_connection.rollback()
# show error message on bad value
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/movie.html', directors=directors, message=message), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)
def actor_entry():
"""
Handle actor entry requests. Post form must contain uid, and dob. dob must be in format of
YYYY-mm-dd for parsing. Generates list of potential users for promotion to actor.
:return: rendered template including list of users who are not actors
"""
# check for authorization
if not check_moderator():
# deny access if not allowed
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/actor'))
# default empty message
message = None
# get list of users who are not already actors for prompting form
curs = db_connection.cursor()
if request.method == 'GET':
# handle get requests with empty message and list of users
return render_template('entry/actor.html', message=message)
elif request.method == 'POST':
# handle post requests as data entry
try:
# try to get the required field forms
uid = request.form['uid']
name = request.form['name']
# cast dob to datetime for later converting to unix time
dob = datetime.strptime(request.form['dob'], '%Y-%m-%d')
except KeyError:
# show error on missing field forms
message = 'bad form data'
return render_template('entry/actor.html', message=message), 400
except ValueError:
# show error for bad date formatting
message = 'bad date format'
return render_template('entry/actor.html', message=message), 400
if not re.match(r'[0-9]+', uid):
# check that user id is only numeric
message = "uid must be numeric"
return render_template('entry/actor.html', message=message), 400
if (not re.match(r'[a-zA-z ]+', name)) or len(name) > 40:
# check validation on given name too
message = "name must be alpha characters and spaces and at most 40 characters"
return render_template('entry/actor.html', message=message), 400
try:
# try to insert actor
# insert on case that given name is same as stage name
curs.execute("INSERT INTO ACTOR VALUES (?, ?, ?)", (uid, name, unix_time(dob)))
db_connection.commit()
# get the uid of the newly added actor
uid = curs.lastrowid
# get the newly added row
inserted_row = curs.execute("SELECT * FROM ACTOR WHERE UID=?", (uid,))
# success message with added row
message = 'inserted new actor successfully: ' + str(inserted_row.fetchone())
# show success message and new list of users
return render_template('entry/actor.html', message=message), 201
except sqlite3.Error as err:
# handle errors for key constraint (foreign and unique)
db_connection.rollback()
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/actor.html', message=message), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)
def director_entry():
"""
Handle requests for director entries. Displays available users and movies for potential directors and movies that
made him/her famous. Post request must have form fields: uid, mid, given_name, dob. dob must be in the form of
YYYY-mm-dd.
:return: Rendered template, including lists of user and movie ids
"""
# check for user authorization
if not check_moderator():
# deny access if not moderator
return redirect(url_for('accounts_api.forbidden', account_type='moderator', resource='/entry/director'))
# default empty message
message = None
# get users who are not already directors for prompting form
curs = db_connection.cursor()
if request.method == 'GET':
# handle get requests with blank message and potential users/movies
return render_template('entry/director.html', message=message)
elif request.method == 'POST':
# handle post requests as data entry
try:
# try to get required form fields
uid = request.form['uid']
famous_for = request.form['mid']
given_name = request.form['given_name']
# cast dob to a datetime object (later converted to unix time)
dob = datetime.strptime(request.form['dob'], '%Y-%m-%d')
except KeyError:
# show error message if any form fields are missing
message = 'bad form data'
return render_template('entry/director.html', message=message), 400
except ValueError:
# show error message if the date formatting fails
message = 'bad date format'
return render_template('entry/director.html', message=message), 400
if not re.match(r'[0-9]+', uid):
# check that uid is just numbers, show error if not
message = "uid must be numeric"
return render_template('entry/director.html', message=message), 400
if not (famous_for is None or re.match(r'[0-9]+', famous_for) or famous_for == ""):
# check that famous_for is a proper MID or NULL, show error if not
message = "famous for mid must be numeric or empty"
return render_template('entry/director.html', message=message), 400
if (not re.match(r'[a-zA-z ]+', given_name)) or len(given_name) > 40:
# check that given name contains only letters and spaces and is no more than 40 characters
message = "name must be alpha characters and spaces and at most 40 characters"
return render_template('entry/director.html', message=message), 400
try:
# try to insert the director values
if famous_for == "NULL":
# insert with null MID (most cases)
curs.execute("INSERT INTO DIRECTOR VALUES (?, NULL, ?, ?)", (uid, given_name, unix_time(dob)))
else:
# insert with given MID (not really sure when this would be possible)
curs.execute("INSERT INTO DIRECTOR VALUES (?, ?, ?, ?)", (uid, famous_for, given_name, unix_time(dob)))
db_connection.commit()
# get the user id of the added director
uid = curs.lastrowid
# get the inserted director
inserted_row = curs.execute("SELECT * FROM DIRECTOR WHERE UID=?", (uid,))
# create success message with inserted data
message = 'inserted new director successfully: ' + str(inserted_row.fetchone())
# show the template with potential users, movie ids, and with success message
return render_template('entry/director.html', message=message), 201
except sqlite3.Error as err:
# catch sql errors, usually the foreign key constraint and unique constraint
db_connection.rollback()
# show error message
message = 'error inserting tuple (' + str(err) + ')'
return render_template('entry/director.html', message=message), 400
else:
# if not get or post, abort (should never happen, but just in case)
abort(405)