WebFund 2024F: Assignment 2

Please submit the answers to the following questions via Brightspace by October 16, 2024. There are 20 points in 8 questions.

Submit your answers as a plain text file following this template. Name your answer file "comp2406-assign2-<username>.txt" (where <username> is your MyCarletonOne username). Please make sure to use your correct student ID number otherwise your grades may not be properly recorded.

Your answers will be parsed by a script in order to help with grading so please preserve the format of the template. No other formats will be accepted.

Note that the file should be a text file, preferably a UNIX text file (so LF line endings, not CRLF). (See the Wikipedia page on text files to learn more.)

You may use this validator page to make sure your answer file is properly named and formatted. Submissions that do not validate are likely to receive an automatic grade of zero.

Please solve each question rather than look up their answers. Solutions where the answer was found through an online search or answered by a friend or AI will likely get partial or no marks. The questions can only be fully answered by documenting your journey to find the answer rather than the answer itself. Outside resources and other individuals should be used to help with specific technical problems (e.g., JavaScript syntax and semantics), not to solve the problem itself. Your solution should be your own, and the journey by which you reach it should be your own.

Having said this, for each question, please include what outside resources you used to complete each of your answers, including other students, man pages, and web resources (including AI services). You do not need to list help from the instructor, TA, or information covered in lecture or tutorial.

Questions

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.
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)?
3. [2] How is /add and /add.html treated differently in dbdemo2? Be specific, and explain how you arrived at your answer.
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.
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?
6. [4] How could you alphabetize the list of cities shown by /analyze...
   1. [2] ...by using JavaScript code, specifically the array sort() method?
   2. [2] ...by using SQL code?
   For both, be sure to explain how you developed and tested your answers and any issues that arose during both.
7. [4] How could you add the following to the /analysis page?
   1. [2] A list of unique countries?
   2. [2] The number of unique cities and countries?
   Be sure to explain the process by which you developed your solutions and any problems you encountered along the way.
8. [2] What is one way you could ensure that any added birthday is a valid date? How did you develop your solution?

Code

Download dbdemo2.zip

dbdemo2.js

// SPDX-License-Identifier: GPL-3.0-or-later
// Copyright (C) 2024 Anil Somayaji
//
// dbdemo2.js
// for COMP 2406 (Fall 2024), Carleton University
// 
// Initial version: October 7, 2024
//
// run with the following command:
//    deno run --allow-net --allow-read --allow-write dbdemo2.js
//

import { DB } from "https://deno.land/x/sqlite/mod.ts";

const status_NOT_FOUND = 404;
const status_OK = 200;
const status_NOT_IMPLEMENTED = 501;
const appTitle = "COMP 2406 DB Demo 2";
const dbFile = "people.db";
const table = "people";

const db = new DB(dbFile);

db.execute(`
  CREATE TABLE IF NOT EXISTS ${table} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    city TEXT,
    country TEXT,
    birthday TEXT,
    email TEXT
  )
`);


function addRecordDB(db, table, r) {
    return db.query(`INSERT INTO ${table} ` +
                    "(name, city, country, birthday, email) " +
                    "VALUES (?, ?, ?, ?, ?)",
                    [r.name, r.city, r.country, r.birthday, r.email]);
}


function getAllRecordsDB(db, table) {
    var state = [];
    const query =
          db.prepareQuery(
              "SELECT id, name, city, country, birthday, email FROM " +
                  table + " ORDER BY name ASC LIMIT 50");

    for (const [id, name, city, country, birthday, email] of query.iter()) {
        state.push({id, name, city, country, birthday, email});
    }

    query.finalize();
    
    return state;
}


function analyzeRecordsDB(db, table) {
    var analysis = {};

    analysis.count = db.query("SELECT COUNT(*) FROM " + table);
    analysis.cityList = db.query("SELECT DISTINCT CITY FROM " + table);
    
    return analysis;
}


function MIMEtype(filename) {

    const MIME_TYPES = {
        'css': 'text/css',
        'gif': 'image/gif',
        'htm': 'text/html',
        'html': 'text/html',
        'ico': 'image/x-icon',
        'jpeg': 'image/jpeg',
        'jpg': 'image/jpeg',
        'js': 'text/javascript',
        'json': 'application/json',
        'pdf': 'application/pdf',
        'png': 'image/png',
        'txt': 'text/text'
    };

    var extension = "";
    
    if (filename) {
        extension = filename.slice(filename.lastIndexOf('.')+1).toLowerCase();
    }

    return MIME_TYPES[extension] || "application/octet-stream";
};


function template_header(title) {
    const fullTitle = appTitle + ": " + title;
    
    return `<!DOCTYPE html>
<html>
  <head>
    <title>${fullTitle}</title>
    <link rel="stylesheet" href="/style.css">
  </head>
`
}

function template_notFound(path) {
    return template_header("Page not found") +
        `<body>
<h1>Page not found</h1>

<p>Sorry, the requested page was not found.</p>
</body>
</html>
`
}


function template_addRecord(obj) {
    return template_header("Record just added") +
        `<body>
  <body>
    <h1>Person just added</h1>
    <p>Name: ${obj.name}</p>
    <p>City: ${obj.city}</p>
    <p>Country: ${obj.country}</p>
    <p>Birthday: ${obj.birthday}</p>
    <p>Email: ${obj.email}</p>
    <form method="get" action="/">
      <button type="submit">Home</button>
    </form>
  </body>
</html>
`
}


function template_listRecords(state) {
     const pageTop = `  <body>
    <h1>People Listing</h1>
    <div>
      <div></div>
      <table>
        <thead>
          <th>Name</th>
          <th>City</th>
          <th>Country</th>
          <th>Birthday</th>
          <th>Email</th>
        </thead>
        <tbody>`;

    const pageBottom = `        </tbody>
      </table>
    </div>
    <form method="get" action="/">
      <button type="submit">Home</button>
    </form>
  </body>
</html>`;

    var row = [];
     
    function rowMarkup(s) {
        return "<td>" + s + "</td>";
    }

    for (let r of state) {
        row.push("<tr>")
        row.push(rowMarkup(r.name));
        row.push(rowMarkup(r.city));
        row.push(rowMarkup(r.country));
        row.push(rowMarkup(r.birthday));
        row.push(rowMarkup(r.email));
        row.push("</tr>")
    }

    return template_header("List of Records") +
        pageTop + row.join("\n") + pageBottom;
}


function listRecords() {
    var state = getAllRecordsDB(db, table);
    var response = { contentType: "text/html",
                     status: status_OK,
                     contents: template_listRecords(state),
                   };

    return response;
}


async function routeGet(req) {
    const path = new URL(req.url).pathname;
    if (path === "/list") {
        return listRecords();
    } else if (path === "/analyze") {
        return await showAnalysis();
    }  else {
        return null;
    }
}


async function addRecord(req) {
    var body = await req.formData();
    var obj = { name: body.get("name"),
                city: body.get("city"),
                country: body.get("country"),
                birthday: body.get("birthday"),
                email: body.get("email") };

    addRecordDB(db, table, obj);

    var response = { contentType: "text/html",
                     status: status_OK,
                     contents: template_addRecord(obj),
                   };

    return response;
}


async function showAnalysis() {
    var analysis = analyzeRecordsDB(db, table);
    var cityList = '<li>' + analysis.cityList.join('</li> <li>') + '</li>';
    
    var analysisBody = `  <body>
  <body>
    <h1>Database analysis</h1>
    <p># Records: ${analysis.count}</p>
    <p>Cities:
      <ol>
       ${cityList}
      </ol>
    </p>

    <form method="get" action="/">
      <button type="submit">Home</button>
    </form>
  </body>
</html>`

    var contents = template_header("Analysis") + analysisBody;

    var response = { contentType: "text/html",
                     status: status_OK,
                     contents: contents,
                   };
    
    return response;
}


async function routePost(req) {
    const path = new URL(req.url).pathname;    

    if (path === "/add") {
        return await addRecord(req);
    } else {
        return null;
    }
}


async function route(req) {

    if (req.method === "GET") {
        return await routeGet(req);
    } else if (req.method === "POST") {
        return await routePost(req);
    } else {
        return {
            contents: "Method not implemented.",
            status: status_NOT_IMPLEMENTED,
            contentType: "text/plain"
        };
    }
}


async function fileData(path) {
    var contents, status, contentType;
    
    try {
        contents = await Deno.readFile("./static" + path);
        status = status_OK;
        contentType = MIMEtype(path);
    } catch (e) {
        contents = template_notFound(path);
        status = status_NOT_FOUND;
        contentType = "text/html";
    }
    
    return { contents, status, contentType };
}


async function handler(req) {

    var origpath = new URL(req.url).pathname;
    var path = origpath;
    var r =  await route(req);
    
    if (!r) {
        if (path === "/") {
            path = "/index.html";
        }
        r = await fileData(path);
    }

    console.log(`${r.status} ${req.method} ${r.contentType} ${origpath}`); 

    return new Response(r.contents,
                        {status: r.status,
                         headers: {
                             "content-type": r.contentType,
                         }});
}


const ac = new AbortController();

const server = Deno.serve(
    {
        signal: ac.signal,
        port: 8000,
        hostname: "0.0.0.0"
    },
    handler);

Deno.addSignalListener("SIGINT", () => {
    console.log("SIGINT received, terminating...");
    ac.abort();
});

server.finished.then(() => {
    console.log("Server terminating, closing database.")
    db.close();
});

static/index.html

<!DOCTYPE html>
<html>
  <head>
    <title>COMP 2406 DB Demo 2</title>
    <link rel="stylesheet" href="/style.css">
  </head>
  <body>
    <h1>COMP 2406 DB Demo 2</h1>

    <p>Please select what you would like to do:
      <ol>
        <li><a href="/add.html">Add a record.</a></li>
        <li><a href="/list">List records.</a></li>
        <li><a href="/analyze">Analyze records.</a></li>        
      </ol>
      </p>
  </body>
</html>

static/add.html

<!DOCTYPE html>
<html>
  <head>
    <title>COMP 2406 DB Demo 2: Add Record</title>
    <link rel="stylesheet" href="/style.css">
  </head>
  <body>
    <h1>Add Record</h1>
    <div>
      <p>Fill out the info for the person to be added:</p>
      <form method="post" action="/add">
        <div>
          <input id="name" type="text" name="name">
          <label>Name</label>
        </div>
        <div>
          <input id="country" type="text" name="city">
          <label>City</label>
        </div>
        <div>
          <input id="country" type="text" name="country">
          <label>Country</label>
        </div>
        <div>
          <input id="birthday" type="text" name="birthday">
          <label>Birthday</label>
        </div>
        <div>
          <input id="email" type="text" name="email">
          <label>Email</label>
        </div>
        <button type="submit">Submit</button>
      </form>
    </div>
  </body>
</html>

static/style.css

body {
  padding: 50px;
  font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}

a {
  color: #00B7FF;
}

Solutions

Assignment 2 Solutions