Homework on Ajax

The new part of this assignment is mostly about Ajax, but most of the work is conventional Flask work. Don't start on the JS/JQ too quickly.

fully updated for Fall 2020 If you'd like to see updates for Spring 2021, see flask ajax sp21

Goal and Idea¶

Ajax allows a front-end to communicate with the back-end by just transmitting the necessary changes and updating the database and front-end, rather than having the back-end transmit an entire page to the front end, particularly if the new page is almost exactly the same as the one it is replacing.

In this assignment, you'll be writing a web application that allows users to rate movies on a 1-5 star scale. The web application will display a selection of movies along with their current ratings and a way for the user to rate that movie. When the movie is rated, just the minimum data is sent to the back end (namely, the ID of the user, the ID of the movie and the rating 1-5). The back end will store the rating, re-calculate the movie's average rating, store that, and send the updated average rating to the front end. The front end will then display the updated average rating.

This assignment will demonstrate the following skills:

• SQL tables
• sessions
• multiple forms on a page
• event handling and DOM manipulation
• Ajax
• Progressive Enhancement

Time¶

Historically, this takes almost everyone less than 12 hours to complete. If it's taken you that long and you're not done, stop and get some help.

Practical Considerations¶

To allow you to test that different users can store different ratings and all of the ratings are taken into account, I'm going to have you provide a "login" form with a userid (the number of someone in the staff table). For simplicity, there is no password. This assignment isn't about logins.

So, the actual procedure is that you will (1) login to the app, choosing a userid, (2) rate a movie, seeing the updated average, (3) re-login with a different userid, (5) rate it again and see that both ratings are used.

Reference Solution¶

Here's a link to my reference solution. Using that app, try the procedure above, login as one user, rate a movie, maybe change its rating. Login as another user and give the same movie a different rating.

There is a video of me running my reference solution and doing this. Check the videos page.

Testing¶

1. login using some valid UID. The numbers 1-12 should work. So will your own UID. My app checks that they are valid staff UIDs. Yours doesn't have to.
2. rate a movie. A new page should be rendered with the new average flashed and the movie showing the new average. E.g. if the movie has never been rated, give it a 5 and the average should be a 5.
3. login as someone else.
4. refresh the page is you need to clear any flashed messages which would otherwise be confusing.
5. turn on progressive enhancement
6. open the developer tools network tab
7. click on a rating for the same movie as before. The network tab should show an XHR request, and the page should be updated to show a new average that incorporates the old rating and the new rating (since they are from different people). Continuing the example above, if the rating is 2, the average should be 3.5
8. re-rate the same movie. The new rating should replace the old rating and a new average calculated. Continuing the example above, if the rating is 1, the average should be 3.0

There is a video of me running my reference solution and doing this. Check the videos page.

Very High-Level View¶

You should attack this assignment in two phases. Only the second uses JavaScript and jQuery to implement the Ajax behavior.

Phase 1: Standard Flask¶

The first phase is standard Flask. There will be nao JS/JQ. Implement the code need to login and rate a movie. You'll have to figure out the session code, but that will be unaffected in the Ajax phase.

You'll write routes to:

• show the home page and allow the user to log in
• display the list of all movies with their ratings
  • each movie has a rating form with a submit button
• process a rating using normal flask processing with render_template and flashing.

Phase 2: Ajax¶

The second phase is to use JS/JQ to submit the rating and update the page, instead of a full form submission as in the first phase. Your JS/JQ code will be enabled by turning on progressive enhancement (in a real app, progressive enhancement would be on by default). The second phase will use much of what you implemented in the first phase, and so your new work will be:

• A. The delegated event handler gathers the needed data and POSTs it to the back end.
• B. The back end updates the database and computes a new average,
  • returning that information as JSON
• C. A response handler gets the JSON with the recomputed average and updates the page with that information.

The following diagram illustrates these three steps of the Ajax technology:

What You Must Do¶

The total amount of code you have to write for this assignment isn't a lot, but there are a lot of parts. Here's the high-level description, just to get the big picture. More detail to follow.

• You'll have to create a table for the individual ratings. The table design is up to you, but remember that each person can rate multiple movies and each movie can be rated by multiple people. You have to keep track of the individual ratings, so that a person can change their rating and such. Advice about this below.
• In addition, you'll have to add a column for the average rating for a movie in the main movie table. Each time a rating changes, you'll have to re-compute the average and update the main table. The reason for this is the standard tradeoff: in general, more people will look at the rating for a movie than will update it, so it's more efficient to pre-compute the average.

• You'll have to implement a login procedure for the app. Keep this simple: have the user supply the userid. Create a template with a form to collect this info. You can put this form on the home page; again for simplicity. When the userid is received, set it in the session.

• You'll implement a page/route that displays all the movies, their current ratings, and provides a set of radio buttons for rating each movie. If the user tries to rate a movie without being logged in, flash an error message and send them back. Each movie is associated with a form whose submission rates that movie. The rating is determined by the radio buttons, the movie id by a hidden input in the form (or a data- attribute, if you prefer), and the user id from the session. The Flask route for the form will be rateMovie and will process the movie rating when a form is POSTED, responding with a new, updated page, with a confirmation using flash().

That concludes phase 1. So far, this app is not using Ajax, but creates all the necessary functionality.

Now, add Ajax functionality:

• (Phase 2a) Add JavaScript/jQuery code to the front end to submit the information to the Ajax route. This JS/JQ code will: (1) add a delegated event handler that handles every radio button that will (2) collect the radio button value (the rating from 1-5), the tt of the movie (from the hidden input in the same form or from a data- attribute) and (3) will POST that data to the back end. (The userid will automatically be sent because it's stored in the session.)
• (Phase 2b) Add a new route to Flask named rateMovieAjax that handles POSTed form submission very much like the rateMovie handler from phase 1. The handler gets (from the form) two pieces of information: the tt and the rating (a number from 1-5). The uid will be in the session, just as in the non-Ajax route. What makes this an Ajax method is that its response is not a rendered template, but instead is just a JSON representation of two values: the tt and the new average rating for the movie. Note that flashing doesn't work with Ajax, so omit that code.
• (Phase 2c) Implement a callback that will receive a JSON response and will update the page with the new average rating for this movie.

Finally, and not any earlier, add progressive enhancement. That means using JS/JQ to hide the ugly radio buttons and the submit buttons. In my reference solution, I have a toggle to turn the progressive enhancement on/off. You don't have to do that. (Although, frankly, you could just steal, er, borrow, my code. I haven't hidden that from you.)

All told, this implementation requires:

• a dozen or so lines of SQL to create the new table and to alter the movie table. More on this below. You can learn how to alter a table using MySQL.
• A few dozen lines of Python to define functions to log users in, read movie listings, and insert/update a movie rating and recalculate the average rating.
• Flask routes for /setUID/, /rateMovie/ and /rateMovieAjax/, each of which is quite short (less than a dozen lines of Python). More importantly, the /rateMovie/ and /rateMovieAjax/ handler functions are almost identical, so once you've implemented the first, the second will be straightforward.
• A template for the /rateMovies/ page. You have to create a form for each movie, comprising the hidden input with the tt, the five radio buttons for the ratings, and the submit button. However, you can steal, er, be inspired by my code by viewing the page source in my reference solution.
• About two dozen lines of JS/JQ to handle the Ajax form submission and the response handling with updating the page. Unlike the HTML and CSS that I will permit you to look at for inspiration, I have obfuscated my solution JS, meaning I've rendered the JS code illegible. While it may be possible to undo the obfuscation, please don't. Write your own code. I will consider it a violation of the honor code to try to use my JS/JQ code. This is an opportunity to exercise your JS/JQ code. Indeed, that's kinda the point.
• The event handler will $.post() the data to the /rateMovieAjax/ route and receive the json response and then update the page.

Database Tables¶

As mentioned above, you'll need to create a table to represent the many-to-many relationship between staff (who rate movies) and movies. Each staff member can rate multiple movies and each movie can be rated by multiple staff members, but each staff member can only rate each movie once. If I think that Dr. Zhivago is the greatest movie of all time, I still only get to rate it once. Don't let me stuff the ballot box by rating it multiple times.

To do that, you'll need to keep the ratings in a table. (We saw this earlier this semester in the movie_lens_db.) In my solution, I called this table movie_viewer_rating.

The first time viewer V rates movie M, you'll want to insert that rating into the table. If they change their mind, you'll want to update their rating. Tricky. You might think that this involves checking to see whether the user has already rated this movie and, if so, updating their rating and, if not, inserting a row. That's annoying at best and inefficient at worst, so MySQL provides a shortcut: ON DUPLICATE KEY UPDATE. The idea is exactly what I just described: if the user has already rated a movie, the attempted INSERT will encounter a DUPLICATE KEY, which would usually be an error, but with this syntax, MySQL will instead execute an UPDATE statement.

Secondly, in real life, people rate movies far less often than movie ratings are viewed. So, it is a waste of computation to continually join with movie_viewer_rating, compute the average for the desired movie, and report that in our page. Furthermore, we'd have to do it for every movie on every render, since our app doesn't look at any subset of the movies.

Therefore, we should add a column to the movie table to store the pre-computed rating. We should update that rating whenever a movie gets a new or updated rating from any viewer.

The JSON Response¶

Most of our Flask routes return a rendered template or perhaps a redirect. But your route to handle Ajax updates is different. It will need to return a JSON data structure, appropriately serialized.

Fortunately, Flask makes this easy. The jsonify function takes a Python data structure (such as a dictionary) as its argument and returns an object that we can return as our response. We saw this in class. This will be the main difference between your rateMovie and rateMovieAjax routes.

The Event Handler¶

Your JS/JQ code to handle events has to start from the number that was clicked on and collect both the value of that radio button and the tt of the movie. That will require traversing the DOM tree to find appropriate information (unless you put the tt on every radio button, which I suppose is possible but strikes me as ugly). I used jQuery methods like closest and find to gather this information.

Secondly, the response handler will have to find the appropriate part of the page to update. I used those same methods again.

Hints¶

• It is not cheating to glean as much as you can from my solution, including looking at the markup (HTML) on the page, the forms on the page, the routes that are mentioned in action attributes, and even the non-obfuscated JavaScript (which, for example, defines the global variable for the URL to post to, and whether progressive enhancement is on). I only ask that you not try to reverse-engineer my obfuscated JavaScript.
• I put some console.log statements in my code; feel free to look at the console when running my solution.
• Disabling your event handler when progressive enhancement is off isn't hard. Here's the basic trick:

function (event) { 
   if(!progressive_on) return; 
   ... 
}

• You may find that your click handler gets called twice. This is harmless in this case, but is weird and annoying, and it should be fixed. Here's why it happens: I bound my click handler to the label elements that surround the input elements, since we will hide the input elements when we turn on progressive enhancement. It turns out that clicking on such a label also counts as clicking on the input element (after all, that's what the label is for), and so the click bubbles up a second time. To avoid this not-always-harmless double-execution, you should add the following to your event handler:

if( event.target != this) return;

Then, when the click from the INPUT element bubbles up to the LABEL, the event.target (the INPUT) doesn't match this (the LABEL) and so the second execution is avoided.

To see whether this is happening, look at the Python console or the Network tab in the Chrome debugger.

Aesthetics¶

The goal of this assignment is not aesthetics. If you want to tinker with CSS and such to make things look prettier than mine, you're welcome to do so, but I'm not providing extra credit for it.

One bit of aesthetics that I did that is also functional is to highlight in bold the movie rating that the user has just chosen. (If I had more time, I'd look up how the user rated each movie and display that using bold, but I didn't. I'll leave that as an exercise for the reader.)

How is this done? The event handler for clicks on rating numbers (the labels for the radio buttons) clearly knows the number and the DOM element that was clicked on, so it can "de-select" all the sibings and "select" the chosen one. By "select," I mean to add some CSS to it to mark it. The CSS in this case just made it bold, but you could do anything. Again, feel free to look at what I did in my solution.

Getting the director name¶

My solution shows a feature that you do not have to do: It shows the name of the movie's director in the second column (or "None" if unknown). Should you decide to do that, I'll teach you a useful technique, called outer join.

First, let's talk about what doesn't work. Inner join doesn't work because if the director is NULL, the movie is not listed, since there's no matching person in the person table:

use wmdb;
select title,name as director_name 
from movie inner join person on (director=nm);

That would omit the many, many movies where the WMDB lacks director information. Not good. Feel free to copy/paste that code into MySQL to see it in action. Add an limit clause to look at the first few entries. Notice the number of results; it's a lot more than the number of movies, which indicates all the omissions.

Second, it would be possible, but very inefficient, to get a list of all the movies and iterate over them, looking up the director's name in the person table if the director is not NULL. While we don't have to make a second database connection (we can reuse the one we have) and the database engine might be able to cache the execution plan, it's still hundreds of additional queries.

We can get all the movies, plus the director name if it's not NULL, by using an outer join. An outer join just means keeping (one copy) a movie the join condition is not true. We'll use a left outer join and list the movie table on the left, meaning that we want to keep the movie if the join condition is not true. In that copy, the unused columns (e.g. director_name) are all NULL. Here's more information on a left outer join. That page has a nice Venn diagram that might help visualize what's happening. There are many other tutorials on the web if you don't find that one helpful.

Here's the query:

use wmdb;
select title,name as director_name 
from movie left outer join person on (director=nm);

That's what my solution does. I encourage you to copy/paste that code into MySQL to see it in action. Add an limit clause to look at the first few entries. Notice the number of results; it's the same as the number of movies, which indicates no omissions.

Here's a sample input:

use wmdb;
select tt,title,name as director_name
from movie left outer join person
on (director=nm) where tt = 666 or tt = 98635;

And here's the corresponding output:

MariaDB [wmdb]> use wmdb;
Database changed
MariaDB [wmdb]> select tt,title,name as director_name
    -> from movie left outer join person
    -> on (director=nm) where tt = 666 or tt = 98635;
+-------+----------------------+---------------+
| tt    | title                | director_name |
+-------+----------------------+---------------+
|   666 | <script>             | NULL          |
| 98635 | When Harry Met Sally | Rob Reiner    |
+-------+----------------------+---------------+
2 rows in set (0.00 sec)

In some timings I did, the outer join approach is 2/3 the time of the extra loop of looking up the directors. It also avoids that tedious Python coding.

Again, this is not required, but I'm taking this as an opportunity to teach outer joins, which many of you have asked about.

Datatype of AVG()¶

This section is skippable on first reading, but discusses something that you might run into in your implementation.

It turns out that the MySQL avg() function does something smart but surprising. If all the data that is being averaged has exact values (integers and so forth), the computation results in a decimal value, which is exact, rather that float, which is not. If the data being averaged is inexact (float and so forth), then the result is a float. This is actually documented, but it's hard to find. Go to the page on aggregate functions and then search for avg().

That explains the following behavior, which you can test in the Python shell. Here's the code, which computes the average number of movies in the database, for each year since 2000. (This example makes sense because the counts will be integers, and so the avg() will be done on exact-value arguments.)

import cs304dbi as dbi
conn = dbi.connect()
curs = dbi.cursor(conn)
curs.execute('''select avg(c) from 
                (select count(*) as c 
                 from movie 
                 where `release` like '20%'
                 group by `release`) as T''')
curs.fetchone()

Here's what happens in the Python shell. Notice the datatype on the last line:

>>> import cs304dbi as dbi
>>> conn = dbi.connect()
>>> curs = dbi.cursor(conn)
>>> curs.execute('''select avg(c) from 
...                 (select count(*) as c 
...                  from movie 
...                  where `release` like '20%'
...                  group by `release`) as T''')
1
>>> curs.fetchone()
(Decimal('39.0909'),)

The weird Decimal('39.0909' is a Python datatype that you've probably never heard of before. It corresponds to the MySQL datatype of the same name. Both represent exact values (like integers) but with a decimal point in them:

>>> x
(Decimal('39.0909'),)
>>> y = x[0]
>>> y
Decimal('39.0909')
>>> type(y)
<class 'decimal.Decimal'>

If you run into something like this and it's confusing you or causing errors in your code, it's easy enough to convert the data to float:

>>> z = float(y)
>>> z
39.0909
>>> type(z)
<class 'float'>

So, don't let that concern you.

How to turn this in¶

As usual, the turn-in procedure for this will be very similar to the MySQL assignment. See that page for details. The essential checklist is:

• create your code in ~/cs304/assignments/ajax
• tar up the ajax folder into ajax.tar
• drop the ajax.tar file to the course account
• create a dummy gradescope submission, telling me what drop folder to look for your code in. E.g. "Hermione Granger worked alone, look in hgranger" or "Ron Weasley and I worked together, look in hpotter"
• fill out the time and work form (see below).

Sharing the Solution

In addition to dropping the solution to the course account, you should share the solution with your partner. Once your partner creates a drop folder, you can share it with them in the same way. See Using Drop to Transfer Files.

Gradescope

To save paper and to allow online grading, I want you to use Gradescope. Gradescope requires a PDF to be uploaded, so you'll have to generate such a PDF. Here's one way to do it:

• Create an empty Google Doc.
• Write in it a sentence like: Ron Weasley and I worked together on this; dropped from his account. If you worked alone, just say that.
• I can then grade the file dropped by Ron, giving credit to both.
• Use the File menu to download the page as a PDF (or print it and save as PDF).
• Upload the PDF to Gradescope.
• Enter your partner's name, if you worked with a partner

Time and Work

The following link has been updated for Fall 2021

Finally, when you have completed the assignment, make sure you fill out the time and work Fall 2021 That report is required.