Lesson 5: Factorial: Querying tables

mutual {
  fun request(s) {
    <html>
     <body>
      <h1>Please type a number</h1>
       <form l:onsubmit="{response(t)}" l:onkeyup="{replaceDocument(request(t))}">
        <input type="text" value="{s}" l:name="t"/>
        {
        if (s =~ /^[0-9]+/)
         <input type="submit"/>
        else
         <input type="submit" disabled="disabled"/>
        }
       </form>
      </body>
    </html>
  }

  fun response(s) client {
   var n = stringToInt(s);

   replaceDocument(
    <html>
     <body>
      <h1>Factorials up to {intToXml(n)}</h1>
      <table><tbody>{
       for ((i=i,f=f) <- lookupFactorials(n))
        <tr>
         <td>{intToXml(i)}</td>
         <td>{stringToXml(f)}</td>
        </tr>
      }</tbody></table>
     </body>
    </html>
   )
  }

  fun lookupFactorials(n) server {
   var db = database "links";
   var factorials = table "factorials" with (i : Int, f : String) from db;

   query {
     for (row <-- factorials)
      where (row.i <= n)
      orderby (row.i)
       [(i=row.i, f=row.f)]
    }
  }
}

fun main() {
  addRoute("", fun (_) { 
    page
      <#>{request("")}</#>
  });
  servePages()
}

main()

Database setup

See the README for information regarding database setup for this example.

Overview

This program exercises (simple) database querying using Links's support for mapping comprehension syntax to queries. (This capability is a form of "language-integrated query", analogous to Microsoft's LINQ).

The main page is a simple form (constructed by function request) that accepts an integer, say 5. This form is a little more sophisticated than previous examples: it uses a second event, l:onkeyup, to validate the field contents and ensure that the text field can be parsed to an integer. Setting this event handler means that after each key is pressed, the form will be reconstructed by calling request with the new text value; this causes the if-then conditional logic to be re-evaluated, which tests whether the field matches a regular expression for nonempty digit sequences (s =~ /^[0-9]+/). If not, then the submit button is deactivated.

On submission, the response function is called with the field value, and replaces the document content with the results of a database query that pulls in all of the rows where i is less than or equal to the number, as a table showing the i and f values. This is done by the lookupFactorials function.

Queries

Because it involves several new features relating to database connections and querying, we'll go through this function line by line. The first line creates a reference to the database links, and binds it to variable db. (It is possible to refer to several databases from a single Links program, but writing a query that refers to tables in more than one database results in a run-time error.)

 var db = database "links";

The next line creates a reference to the factorials table, which was created by the factorial.sql script run earlier:

 var factorials = table "factorials" with (i : Int, f : String) from db;

The table reference specifies the names and (Links) types of the table fields, and the database db where the table lives.

Finally, the query expression runs a query (defined using a comprehension):

 query {
   for (row <-- factorials)
    where (row.i <= n)
    orderby (row.i)
     [(i=row.i, f=row.f)]
}

The for line says that the query will consider each row in the factorials table. The where line constrains attention to those rows whose i field is <= n. The orderby line sorts the rows by the i field. The last line says that for each iteration of the query we return a new row [(i=row.i, f=row.f)]. (It would be equivalent to just return [row].)

One important, but perhaps subtle, difference compared to the comprehension we used in the todo list (previous lesson) is that in the line for (row <-- factorials), we use a long arrow <--. The comprehension syntax for (x <- xs) with the short arrow is intended for use when xs is a list, not a table reference; for a table reference t we use the long arrow. If you get these mixed up, you will get a type error complaining that a list was provided where a table reference was expected, or vice versa.

This query will generate a single SQL query. Links can do many things that a SQL database cannot do, such as printing to the console, or evaluating a recursive function. Using the query keyword means that Links will check that the code inside the braces {...} can be performed on the database; if this is not the case (for example due to printing or recursive function calls) then Links will raise a compile-time type error. If the query keyword is omitted, then Links will do its best to turn comprehensions into queries, but may fail; that may mean that the query runs very inefficiently, for example by loading all of the data from a table into memory and running the query there. In general, if you expect a part of the program to be performed on the database, enclose it with query so that Links will check this.

Client and server annotations

The other new thing in this example is the annotations client and server in some of the function definitions. For example response has a client annotation and lookupFactorials has a server annotation. These annotations tell Links that these functions should only be executed on the Web client (browser) or only on the server. This is particularly important for database queries. Queries should be run from the server only, because database and table references amount to database connections which will not be meaningful to the Web client. Moreover, making these values available on the Web client can leak important information such as the username and password of the database user.

Exercises

What happens if you add a print statement or call a recursive function in the middle of a query? What happens if you do this after removing the query{...} surrounding the query code?
Modify the query to return just one row, the row matching the parameter n, or to return all rows whose factorial value is larger than n.
What happens if you submit a string such as 5xyz that starts with a number but includes non-digits? How might you change this behavior to rule this out?

Provide feedback

Saved searches