WebFund 2024F: Tutorial 4

From Soma-notes

In this tutorial you'll be playing with dbdemo, an enhanced version of Tutorial 3's formdemo that stores data persistently using sqlite, an in-process lightweight SQL database.

Once you have the application up and running you should do the following tasks.

Tasks

  1. Port the List People button and any other changes you made to formdemo to dbdemo. Could you just copy the code, or did you have to make significant modifications?
  2. How does the behavior of formdemo and dbdemo differ when Ctrl-C is used to interrupt the server? What code is the source of this difference?
  3. Create several records, terminate the server, restart it, and then visit the list page. Were your additions preserved or lost? How does this compare to formdemo?
  4. Use the sqlite3 command line program to examine the database created by dbdemo. How can you add records? List all records? List all records in a certain city?
  5. Change dbdemo so that it support sorting of the listed records. Specifically, it should support the following two requests:
    • /list?sort=alpha-name <--- alphabetical sort by name
    • /list?sort=ralpha-name <--- reverse alphabetical sort by name
  6. Try making the above sort happen at the database (through an SQL request) and on the server in JavaScript. Do you think it is better to have the database perform the sort, or is it better for the web server to do the sorting? Why?

Code

All code: dbdemo.zip

To install, unpack, and run dbdemo in the class VM: 

 wget https://homeostasis.scs.carleton.ca/~soma/webfund-2024f/code/dbdemo.zip
 unzip dbdemo.zip
 cd dbdemo
 deno run --allow-net --allow-read --allow-write dbdemo.js

dbdemo.js

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

const status_NOT_FOUND = 404;
const status_OK = 200;

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

const db = new DB("people.db");
const table = "people";

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);

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

    query.finalize();
    
    return state;
}

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',
        '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) {
    return `<!DOCTYPE html>
<html>
  <head>
    <title>${title}</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("Person 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>
  </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 {
        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 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 null;
    }
}

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 Simple form demo</title>
    <link rel="stylesheet" href="/style.css">
  </head>
  <body>
    <h1>COMP 2406 Simple form demo</h1>
    <div>
      <p>Fill out your info</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;
}
Retrieved from "https://homeostasis.scs.carleton.ca/wiki/index.php?title=WebFund_2024F:_Tutorial_4&oldid=24769"