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)