Homework on Ajax

Revised for Fall 2024

Ajax is a way for the front-end and back-end to communicate, but this semester we are focussing on back-end work, so this version of the assignment is solely back-end. I'll provide a testing script (using the Python requests module) and JavaScript and other means for testing your code.

If you'd like to see the older version that includes JavaScript

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.

This assignment does that by

  • A movie ratings page that works with your copy of our WMDB,
  • Allows users to login and rate movies, but with re-rendering the whole thing, then
  • Allows users to replace that behavior with incremental updates of particular movies

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
  • logins/sessions (simplified)
  • multiple forms on a page
  • Ajax

It will not include any JavaScript coding.

Time

This is a drastically revised assignment. I expect it to take less than 8 hours, but I could be wrong. If you're spending a lot of time on it, please ask for help.

In Fall 2022, all but one team reported 6-8 hours.

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" ability where the front-end supplies a userid (the number of someone in the staff table). For simplicity, there is no password. This assignment isn't primarily 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 an earlier version of my reference solution and doing this. Check the videos page.

Strict API

Your back-end needs to support the following endpoints with the given methods and semantics.

Non-Ajax

  1. GET / renders and returns the only page of the app. It will have
    • a login form
    • a button to toggle the "progressive enhancement" which is another way to say that when "progressive enhancement" is ON, the Ajax behavior is replaced by the Ajax behaviors. Initially off
    • a table of all the movies along with the ability to rate each one.
  2. POST /set-UID/ along with a UID in the request body (request.form). This endpoint logs the person in.
  3. POST /rate-one-movie/ along with data comprising
    • the tt of the movie, in request.form.get('tt')
    • the number of stars of the rating, in request.form.get('stars')
    • the UID of the person rating the movie, in the session.get('uid')

Both of those endpoints return a redirect to the main (only) page. (This is the POST-Redirect-GET pattern.)

Ajax

The Ajax routes to rate a movie always return the following structure (the values are just examples):

{'tt': 22, 'stars': 4, 'avg': 3.25}

Thus, we always return the new average rating of the movie.

The Ajax behavior adds these endpoints:

  1. POST /set-UID-ajax/ which works like the non-Ajax version, but just sets the UID in the session and returns any value; the front-end ignores it and assumes that login was successful.
  2. POST /rating/ which works the same as /rate-one-movie/ but returns a JSON dictionary that has the new average rating of this movie.
  3. GET /rating/<tt> returns a JSON dictionary that has the current average rating of this movie. Note that if the movie has no ratings from any users, the average rating should be the SQL value NULL, which is Python's None value, which jsonify will convert to the JavaScript value null.
  4. PUT /rating/<tt> replaces this user's rating of this movie and returns the usual JSON dictionary. Note that this behavior is just like the POST /rating/, except with a different interface.
  5. DELETE /rating/<tt> deletes this user's rating of this movie and returns the usual JSON dictionary. If the last rating is deleted, the average rating should be computed as NULL, as above.

Note that students are often confused by the small difference between POST /rating/ and PUT /rating/<tt>. The only difference is the endpoint. We could magnify the difference by requiring that:

  • POST produce an error if the the user has already rated this movie (since POST is supposed to be for new entities), and
  • PUT produce an error if the user has not already rated this movie (since PUT is supposed to replace existing entities).

However, I have chosen not to do that, since it seems kinder to the user to not generate errors when we can just as easily be helpful.

Special Route

Finally, for the purposes of the testing script, I will require you to implement one special-purpose route. The testing script will POST, GET, PUT and DELETE ratings for a particular movie (namely 666, but your code should not depend on this value). To have a consistent starting point, I ask that you implement a route that deletes all ratings for a given movie:

  • POST /delete-all-ratings/<tt> deletes all ratings for the given movie TT. It will redirect to the main page, using the POST-Redirect-GET pattern. There's a button to do this with movies. It does require you to be logged in.

Testing

I've given you two ways to test your back-end: one using the requests module and another using the browser. Both do the following conceptual testing:

  • login as, say, uid 11
  • delete all ratings for a movie, say, 22
  • rate 22: give it 3 stars. average should be 3
  • login as, uid 12
  • GET rating for 22, should be 3
  • rate 22: give it 4 stars. average should be 3.5
  • PUT rating of 5 stars. average should be 4
  • DELETE rating. average should go back to 3

Additional GET requests can be interspersed as necessary to assure that the correct average is returned.

Browser Testing

Start by testing the normal, non-Ajax behavior:

  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. choose a movie to play with; delete all its prior ratings. The page should re-render and the rating will be None.
  3. rate it. A new page should be rendered with the new average flashed and the movie showing the new average, which will be the same as your rating. E.g. if the movie has never been rated, give it a 5 and the average should be a 5.
  4. login as someone else.
  5. rate the same movie. The result should be an average of the two.

Now, we can start testing the Ajax/REST behavior.

Note that to test in the browser, your page will have to load my JavaScript/jQuery code, which is in the rate.js file I provided (in copy ajax file). So, you don't have to implement any JavaScript. However, your backend should support the requests that my JavaScript makes.

  1. refresh the page if you need to clear any flashed messages which would otherwise be confusing.
  2. turn on progressive enhancement
  3. open the developer tools network tab (if you're interested)
  4. 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
  5. 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
  6. login as someone else
  7. re-rate the movie. Again the rating should change correctly.

Finally, we can test particular JavaScript functions, which launch Ajax requests. This testing is described in section JS Functions below.

Testing with a script

I have written a testing script for you. It focuses on the Ajax routes which return JSON data. You're welcome to read the code; that business part is at the end and the part from line 100 to the end (line 135) is pretty readable. It prints the response from your app, so you can read what it sent and see if it seems right, given the context.

You can run the testing app like this:

~cs304flask/pub/bin/test-ajax

If you're curious, here's the code

Here's a transcript of the output from my solution:

[cs304@tempest ~] ~cs304/pub/bin/test-ajax 
POST http://cs.wellesley.edu:1942/set-UID-ajax/ {'uid': 1}
200
response text {
  "error": false
}

POST http://cs.wellesley.edu:1942/delete-all-ratings/666 {}
302
POST http://cs.wellesley.edu:1942/rating/ {'tt': '666', 'stars': 3}
200
response text {
  "avg": 3.0, 
  "stars": "3", 
  "tt": "666"
}

GET http://cs.wellesley.edu:1942/rating/666 {}
200
response text {
  "avg": 3.0, 
  "tt": "666"
}

average rating is  3.0 should be 3.0
POST http://cs.wellesley.edu:1942/set-UID-ajax/ {'uid': 2}
200
response text {
  "error": false
}

POST http://cs.wellesley.edu:1942/rating/ {'tt': '666', 'stars': 4}
200
response text {
  "avg": 3.5, 
  "stars": "4", 
  "tt": "666"
}

GET http://cs.wellesley.edu:1942/rating/666 {}
200
response text {
  "avg": 3.5, 
  "tt": "666"
}

average rating is  3.5 should be 3.5
PUT http://cs.wellesley.edu:1942/rating/666 {'stars': 5}
200
response text {
  "avg": 4.0, 
  "stars": "5", 
  "tt": "666"
}

GET http://cs.wellesley.edu:1942/rating/666 {}
200
response text {
  "avg": 4.0, 
  "tt": "666"
}

average rating is  4.0 should be 4.0
DELETE http://cs.wellesley.edu:1942/rating/666 {}
200
response text {
  "avg": 3.0, 
  "tt": "666"
}

GET http://cs.wellesley.edu:1942/rating/666 {}
200
response text {
  "avg": 3.0, 
  "tt": "666"
}

average rating is  3.0 should be 3.0
[cs304@tempest ~]

Let me know if you have any trouble.

Implementation: Very High-Level View

You should attack this assignment in two phases. Only the second phase has Ajax/REST processing with JSON responses

Phase 1: Standard Flask

The first phase is standard Flask. There is no Ajax or JSON. 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 with a login form and the table of all movies with their ratings
    • each movie has a rating form with a submit button
    • there's also a button to delete all ratings for the movie on that row
  • process a rating using normal flask processing with render_template and flashing.

See the strict API above.

I will provide two template files that you can use for your app, along with a JavaScript file. They are all in

ajax-files

You can copy them like this:

cp ~cs304flask/pub/assignments/ajax/ajax-files/* path/to/your/ajax/folder

Once you've copied them, you can move them to the appropriate subfolders.

You should read over the template files to determine:

  • what values you will have to supply when you render_template; in other words, find all the placeholders in the templates and determine what they want.
  • what URLs you will need to support
  • You can delete the two lines in movie-base.html that are marked "DELETE THIS LINE"

Testing: you should test as you go, using the strict API that I described above and the browser testing.

Logins

For this assignment, I'm taking a very simple approach to logins. (Your projects will have proper logins with encrypted passwords.) The main page that I'm giving you will always display a login form, even if the user is logged in. A proper app should not, but I'm not asking you to implement logout, so this will make it easier to login with a different UID.

Phase 2: Ajax

The second phase is to process REST-style requests and respond with JSON instead of rendering a template (or returning a redirect). My 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

  • implement the Ajax login route. It will set the UID in the session and return a simple JSON result (maybe just "ok"), which the front-end will ignore.
  • implement the POST /rating/ route. At this point, the browser testing should work. That is, the app should work with progressive enhancement turned on.
  • finally, implement the remaining REST-style endpoints. Then the JS functions I supplied should work:
    • getRating(tt) returns the average rating of that movie
    • rateMovie(tt, stars) rates that movie, using POST
    • putRating(tt, stars) rates that movie, using PUT
    • deleteRating(tt) deletes this user's rating of that movie

Note that none of these functions takes the UID as an argument. That comes from the session, so your login code needs to work.

JS functions

You can use the JavaScript functions in the rate.js file that I provided above (in copy ajax file) to test your app. In the example below, I've logged into my app and chosen a movie to rate (tt of 10). I then opened the JavaScript console (command-option k on a Mac) and executed the provided JS functions:

screenshot of JS console

Just to be clear:

  • rateMovie posts a new rating for a movie. Here I rated movie 10 giving it 3 stars.
  • getRating returns the current rating for a movie. Movie 10 has 2.5 stars
  • putRating replaces my prior rating of 3 stars with 4 stars, so movie 10 now has 2.75 stars
  • deleteRating deletes my rating, and movie 10 goes to 2.33 stars.
  • rateMovie starts over again.

Here's a screenshot of what shows in the Python console of my app:

screenshot of Python console

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. This will be in your personal database, not in the WMDB or anything like that. 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. (This is in your copy of the movie table in your personal database; the one you used for the CRUD assignment.) 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 and store it in the movie table.
  • You'll have to implement a login procedure for the app. Keep this simple: have the user supply the userid. My templates have forms for this. 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. My templates will do most of the work, but you'll have to figure out how to use them. 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 /rate-one-movie/ and will process the movie rating when a form is POSTED, responding with a new, updated page, with a confirmation using flash().

Now, add Ajax functionality.

  • POST /set-UID-ajax/
  • POST /rating/
  • GET, PUT, DELETE to /rating/<tt>

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 described above. Note that the Ajax and non-Ajax code to handle POST of a rating for a movie are almost identical, so once you've implemented the first, the second will be straightforward.
  • Flask routes for /rating/<tt> with GET, PUT and DELETE

Here's a bit info on those last routes. (Note that in all cases the UID comes from the session)

  • POST /rating/ sends 2 values in the body: tt and stars and basically acts like /rateMovieAjax/.
  • GET /rating/<tt> returns the current average rating of that movie
  • PUT /rating/<tt> sends the stars in the body, the tt in the URL, and replaces the person's rating and returns the new average rating (so it mostly acts like /rateMovieAjax/)
  • DELETE /rating/<tt> deletes this person's rating of that movie and returns the new average rating

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

Earlier in the course, 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 rate-one-movie and rating routes.

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.

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 of) a movie where 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 will almost certainly 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'>

Since our movie ratings are always integers from 1-5, the averages will definitely be Decimal rather than Float.

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'>

Of course, the data might also be SQL NULL which is converted into Python None, so you'll probably have to do something like this:

if avg is not None:
   avg = float(avg)

So, don't let that issue trip you up.

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
  • Make sure you have a dbi.conf(your_db) line in your app.py, so that when I run your code, it'll use your database and not mine.
  • 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).

Time and Work

The following link has been updated for Fall 2024.

Finally, when you have completed the assignment, make sure you fill out the Time and Work Fall 2024 That report is required.