COMP2406 2024F Assignment 2 Solutions

1. [2] Is the call to db.execute() on lines 24-33 necessary? Why or why not? Be sure to specify how you arrived at your answer.

A: These lines are only necessary if the people.db file does not exist or has not been properly initialized, as these line initializes the people table in the people.db SQLite database. I verified this by commenting out these lines and verifying that the app worked so long as people.db existed; when it was removed, the app crashed with an internal server error message.


2. [2] Is there a pattern to the page titles for all of the pages of dbdemo2? Is this pattern determined centrally (i.e., can the pattern be changed by modifying one part of the program)?

A: The pattern is that they all start with the string "COMP 2406 DB Demo 2", with pages other than the index having a : followed by the specific function of the page. However, this value is not determined in one place. The dynamic pages get the value from appTitle, which is used in template_header(). However, the HTML pages in the static/ subdirectory, specifically add.html and index.html, have their titles hard-coded. Thus to change the title, you'd have to edit all of the HTML files in static/ and change the value of appTitle.

This should be checked by attempting to change the title and noticing whether the title is consistent or not between all the pages of the app.


3. [2] How is /add and /add.html treated differently in dbdemo2? Be specific, and explain how you arrived at your answer.

A: /add.html is a static HTML page, located in static/, and as a result it is served by fileData(). In contrast, /add is a dynamic page generated by addRecord(), which is called by routePost() which in turn is called by route() when the incoming request is a POST request. Note that /add.html can be returned from a GET or POST request, while /add is only serviced through a POST request.

To verify this, you should have read the code and then made specific changes to the code and observed the results, e.g., commenting out the call to addRecord() and noting how /add no longer appears, moving add.html and noting what breaks. If you don't make the changes, you can't be sure that your observations are correct.


4. [2] How does the behavior of dbdemo2 change if line 338 (the call to ac.abort()) is deleted? Why? Again, explain how you arrived at or verified your answer.

A: If you remove the call to ac.abort(), the server no longer shuts down when you type Ctrl-C; it prints the message "SIGINT received, terminating..." but then continues serving content as before. I verified this by commenting out this line, running the server, and pressing Ctrl-C in the terminal.

This change makes sense as the call to ac.abort() is what tells Deno.serve() to shut down; deno.addSignalListener() provides alternative code to run when SIGINT is received other than the default which terminates the program.

(Full credit for reporting on observing what happens when ac.abort() is removed.)


5. [2] Is there a maximum number of records that will be shown by /list, and if so, what is it? How did you verify that your answer is correct?

A: The maximum number of records returned by /list is 50, as that is the limit in the SQL statement in getAllRecordsDB() ("LIMIT 50" at the end). You can verify this by changing the limit to 1 or another small number and observing that only that number of records is listed. Alternately, you could add over 50 records and observe that only 50 are displayed.


6a. [2] How could you alphabetize the list of cities shown by /analyze by using JavaScript code, specifically the array <tt>sort()</tt> method? Be sure to explain how you developed and tested your answers and any issues that arose during both.

A: We just need to add the following line to analyzeRecordsDB() just before it returns:

    analysis.cityList.sort();

I added this line and then visited the analysis page. With the provided database it reports Ottawa then Montreal by default, but with this change it shows Montreal then Ottawa. (It is better if you added more records and observed the changes.)


6b. [2] How could you alphabetize the list of cities shown by /analyze by using SQL code? Be sure to explain how you developed and tested your answers and any issues that arose during both.

A: We just have to add an " ORDER BY CITY" to the end of the db.query() call for cityList in analyzeRecordsDB():

     analysis.cityList = db.query("SELECT DISTINCT CITY FROM " + table + " ORDER BY CITY");

Note we can see the use of ORDER BY in getAllRecords().

Again, you can see this work with the default database as the order changes from Ottawa, Montreal to Montreal, Ottawa. (It is better if you added more records and observed the changes.)


7a. [2] How could you add a list of unique countries to the /analysis page? Be sure to explain the process by which you developed your solutions and any problems you encountered along the way.

A: To do this, we can just copy what is currently done to show a list of cities. First, add this line to analyzeRecordsDB() (just before the return line):

    analysis.countryList = db.query("SELECT DISTINCT COUNTRY FROM " + table);

(Note the list isn't sorted, we could sort it using the methods from the previous question.)

Next, add the following line to showAnalysis() to convert the returned array into HTML list items, just after the declaration of cityList:

       var countryList = '<li>' + analysis.countryList.join('</li> <li>') + '</li>';

Then, to add it to the page, insert the following into the middle of analysisBody:

   <p>Countries:
      <ol>
       ${countryList}
      </ol>
    </p>

And that's it!

To test this, I made the above changes and then ran the code to make sure I got what I expected. Since I created the original code I knew where the changes should be made. The process for most students will involve doing experiments to understand how the cityList is generated and displayed and then applying it to countryList.


7b. [2] How could you add the number of unique cities and countries to the /analysis page? Be sure to explain the process by which you developed your solutions and any problems you encountered along the way.

A: To solve this, you need to figure out how to count records with unique values. The basic SQL statement to get out the unique city is:

  select count(distinct city) from people;

(SQL is case insensitive for the most part, but it is traditional for keywords to be all caps.)

I developed and tested this on the command line using sqlite3.

We can combine all the requested counts into one SQL statement, assigning it to a new counts array, and then extract the individual counts. The following code should substitute for the analysis.counts line in analyzeRecordsDB():

    analysis.counts = db.query("SELECT COUNT(*), COUNT(DISTINCT CITY), " +
                               "COUNT(DISTINCT COUNTRY) FROM " + table)[0];

    analysis.count = analysis.counts[0];
    analysis.cityCount = analysis.counts[1];
    analysis.countryCount = analysis.counts[2];

Note that the [0] at the end of analysis.counts was needed because db.query encloses the result in an array for some reason.

(In general, it is better to make fewer SQL statements to make things faster, but with SQLite this strictly speaking isn't so necessary.)

Then, to use these values, we change showAnalysis() by adding the following to analysisBody (just after <p># Records line):

    <p># Cities: ${analysis.cityCount}</p>
    <p># Countries: ${analysis.countryCount}</p>

I then ran the code to make sure I got the right stats.

My original solution omitted the [0] at the end of the query, and as a result both the cities and countries were undefined on the page. I then did a console.log(analysis.counts) and saw its structure and then added in the [0].

(If you did separate queries you probably didn't have this issue.)

I personally used this page as a reference to learn about the relevant SQL for this answer, but any SQL reference is fine:

https://www.digitalocean.com/community/tutorials/sql-select-statement-with-count


8. [2] What is one way you could ensure that any added birthday is a valid date? How did you develop your solution?

A: There are many ways to ensure that only a valid date is entered into the birthday field. Probably the easiest way to do this would be to change the input type to be a "date" rather than "text" on the add.html page:

    <input id="birthday" type="date" name="birthday">

If you do this, now a date picker will appear that only allows for dates to be entered.

Now, if someone submitted the form info directly via a POST on the command line (rather than through the add.html submit button) they could submit something that wasn't a date. To prevent this, we can then verify the date on the server, with the add failing if the birthday isn't a valid date.

The two places to check this is 1) when processing the form data (with the check in JavaScript) or 2) when doing the SQL INSERT. Since SQLite doesn't have strong typing of column data, we could add in constraints to the table; however, it will be easier to do the checks in JavaScript.

Note that in SQLite you need to decide whether the date is encoded as text, a real (floating point) number, or an integer, as SQLite doesn't have a date or time type. This page gives a very clear explanation of the possibilities:

  https://www.sqlitetutorial.net/sqlite-date/

We might as well keep the field as a text field as that means we don't need to convert anything when displaying it.

The proper way to validate a date in JavaScript is generally to use a library like Moment.js (http://momentjs.com/), it has Deno support (https://deno.land/x/deno_moment@v1.1.2) and can be used to check for very specific date formats easily while also making sure the date is in fact a valid date.

However, if we use the standard HTML date picker, we know that the date returned will be in the format YYYY-MM-DD:

  https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/date

so we can just verify on the server that the date is in that format.

One way to do this is with a regular expression, there are lots of fancy ones out there that even check for invalid dates such as February 30th. An easier way, though, is with Date.parse():

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/parse

If Date.parse() returns NaN, it isn't a date.

To use this, we first define a Bad Request status code at the top of the program:

 const status_BAD_REQUEST = 400;

(See https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/400)

We then change the body of addRecord() to be the following:

    var response;

    if (Date.parse(obj.birthday)) {
        addRecordDB(db, table, obj);

        response = { contentType: "text/html",
                     status: status_OK,
                     contents: template_addRecord(obj),
                   };
    } else {
        response = { contentType: "text/plain",
                     status: status_BAD_REQUEST,
                     contents: "Bad Request: improperly formatted birthday",
                   };
    }

The if clause is the original body, minus the var declaration for response (that's moved to the top). The if test checks to see whether it is a date, and if it is not the else clause sets response to an appropriate error for a bad request.

I tested this by going back to a text picker for the birthday and made sure it generated an error.

In a normal app, you'd want to do validation both on the client (say with the right form element) and on the server.

When developing my solution, I originally had

    if (Date.parse(obj.birthday) === NaN) {
       ...
    }

with the clauses inverted. This was incorrect, however, because NaN is never equal to anything, including NaN!

(Note that changing the birthday form type to "date" would get full credit so long as you propery describe your development process.)