Skip to content

Lesson 6: Database TODO list: Updating tables

Jump to bottom
vcgalpin edited this page Nov 24, 2020 · 5 revisions 
var db = database "links";
var items = table "todo" with (name : String) from db;

mutual {
  fun showList() server {
   page
    <html>
     <body>
      <form l:action="{add(item)}" method="POST">
        <input l:name="item"/>
        <button type="submit">Add item</button>
      </form>
      <table>
       {for (item <- query {for (item <-- items) [item]})
          <tr><td>{stringToXml(item.name)}</td>
              <td><form l:action="{remove(item.name)}" method="POST">
                   <button type="submit">Done</button>
                  </form>
              </td>
          </tr>}
       </table>
      </body>
    </html>
  }

  fun add(name) server {
   insert items values [(name=name)];
   showList()
  }

  fun remove(name) server {
   delete (r <-- items) where ((name=r.name).name == name);
   showList()
  }

}

fun mainPage (_) {
  showList()
}

fun main() {
  addRoute("",mainPage);
  servePages()
}

main()

As with the previous lesson, to run this example program you need to have a working Links database configuration. Before running the program, you should run the SQL file todo.sql to create the table expected by the program, then running as follows should work:

linx --config=config todo_db.links

Overview

This example program is superficially similar to lesson 4's TODO list. However, instead of storing the TODO list in a list value on the client, this program stores it in the database. So, there are two important differences:

  • Todo list items are persistent in the database and will survive if the Links program terminates and is restarted. (By the same token, they could also be accessed through the database interactive interface or by other applications with access to the database.)
  • Todo list items are centralized and different copies of the todo list program running in different browser windows can see the same data.

The user interface for this version of the TODO list program is the same as before, so we won't explain it again. The main differences are in how the list is displayed and changed.

In showList, instead of using a list comprehension over an in-memory list, we use a query query {for (item <-- items) [item]}. This generates a simple SQL querty that just returns all of the list items. Because showList queries the database, we annotate it server so that this happens on the server.

The actions for the insert and remove buttons are also different: they simply call the add and remove functions. These functions use Links's syntax for database table updates to insert a new element or remove an existing todo list item (by name) from the table:

insert items values [(name=name)];

inserts a new row (name=name) into the items table, and

delete (r <-- items) where (r.name == name);

deletes items matching a Boolean condition.

As before, add and remove are annotated server so that these will be performed on the server.

Links also supports update syntax, for example:

update (i <-- items)
   where (i.name == oldname)
    set (name=newname)

renames an item from oldname to newname.

Exercises

  1. What happens if the server annotation is removed from showList, add or remove?

This should still work, but no longer guarantees that the queries or database/table data stays on the server. Queries encountered in client code will incur a server-side call to perform the query, but this may mean that database access information such as username/password could be visible to hostile clients.

  1. What happens if you replace the server annotation with client in the above functions?

Currently this results in gobbledegook if the page is visited or if add or remove are called. This seems to be a bug in Links 0.6.1, we should catch this and return a type error.

  1. Using update, modify todo_db.links to allow renaming an existing item.

  2. If you're familiar with SQL constraints/keys, modify the SQL table definition to force the elements of the table to be unique. What happens if you try to add duplicate entries? How might one avoid this problem?

If a key constraint is added, then at most one row with a given name will be allowed. Attempting to add such a duplicate todo list item will result in a Links run-time error. This could be handled more smoothly by checking whether a todo list item already exists in the table before trying to add it. Links doesn't currently provide a way to recover from such errors.

  1. This version of the TODO list uses l:action to handle the form responses by POSTing to the server. Some server communication is unavoidable because we need to get data from the database, or update the database, but it should be possible to rewrite this program to use l:onsubmit to avoid completely rebuilding the page whenever the a button is clicked, using a similar approach to the client-side form in lessons 3 or 5. Modify todo_db.links to work this way.
Clone this wiki locally