Eleven - language reference guide

Joe Morrison
February 14, 2005

If you have questions about the Eleven programming language or suggestions for improving this documentation, please contact Joe Morrison at jdm@statesafe.org.

Table of contents

Introduction

Eleven is a high-level language for creating web applications, with a syntax is similar to the C programming language. All programs begin with an optional options block, followed by an optional labels block, followed by one or more statements. The syntax for comments is the same as in C or C++. Here is an example of a complete Eleven program:

  options
  {
    app_name     = "Counter demo";
    package_name = "counter";
    db_connect   = "DBI:mysql:database=eleven;host=localhost";
    db_user      = "www";
    db_pass      = "wwwpass";
  }
  
  /* generate ten HTML displays in sequence, each showing the next counter value */
  
  for (statesafe var i=1; i <= 10; i=i+1)
  {
    display
    {
      print ("The next number is ", i, ".");
    }
  }

When compiled into PHP and installed on a web server, the application looks like this:

Counter demo screenshot

The options block

The options block specifies compiler options. If present, it must be the first thing in the program. The following options are supported (shown with their default values):

  app_name = "Eleven application";

The name of the application (used to generate titles and headers).

  package_name = "eleven_application";

The name of the generated program. If compiling to PHP, the output filename will be "eleven_application.php". If compiling to mod_perl, the output filename will be "eleven_application.pm". This name is also used for authentication. Each row in the auth_continuation table in the Eleven database gives a particular user permission to run a particular package.

  auth_method = "auto";

Authentication method. auto means that when anonymous users visit this application, a random user id is automatically generated and the application starts immediately. The other option is login, which is more secure. The login method requires that user accounts be created in advance in the auth_continuation table in the Eleven database, like this:

INSERT INTO auth_continuation (package, login, creation_time, password, name, uid)
  VALUES ('hello', 'jdm', NOW(), MD5('xyz'), 'Joe Morrison', 1000);

This example authorizes user "jdm" (with password "xyz") to use the package "hello". The uid is 1000 (uid values must be unique).

When you run a program generated with the "login" authentication method, it starts with a login screen. Upon successful login, the application starts. At any time a user can move a session to a different computer by logging in from the new computer. Upon login, the application always resumes from the user's current display.

The Eleven Control Panel application (control.11) included with the Eleven distribution provides a simple web-based user interface for managing user accounts.

  db_connect = "DBI:mysql:database=eleven;host=localhost";
  db_user    = "www";
  db_pass    = "password";

Database connection parameters.

  style_sheet = "http://eleven.sourceforge.net/style/eleven.css";

Style sheet to be used by the generated program.

  fav_icon = "http://eleven.sourceforge.net/favicon.ico";

Favorites icon to be used by the generated program.

  display_header = "yes";

Controls whether the status message display is displayed on the top of each screen.

  expiry_every_approx   = "100";
  expiry_cutoff_minutes = "1440";

These parameters control the automatic expiry (deletion) of activity records. Activity records, also known as continuation records, are generated constantly by running Eleven applications. The expiry_every_approx option specifies the approximate number of pages to generate before performing each expiry. (When each web page is generated, the Eleven runtime chooses a random number ranging from 1 to this value; whenever the result is 1, the expiry procedure is executed.) The expiry_cutoff_minutes option specifies which activity records should be deleted whenever this happens. Any records older than this number of minutes are deleted.

Note: These values must be quoted, even though they are numeric.

Additional note: Whenever an activity record is deleted, a user can no longer return to the corresponding web page using the "back" button and resume the session from there. If that is attempted, the user will be immediately logged out and will have to log in again - at which point the session will be resumed from the latest point. So the expiry_cutoff_minutes parameter really means "how far can users go back within a session"?

  log_queries = "no";

This option enables or disables query logging. (The PHP code generator logs the exact queries being executed. The mod_perl code generator logs the queries with their placeholders, which isn't as useful.)

  char_encoding = "UTF-8";

By default, the Eleven compiler assumes UTF-8 encoding and includes the following in each HTML header:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

It is recommended that web sites use the UTF-8 encoding scheme. It is consistent with ASCII and also supports UTF-8 Unicode characters. But in some cases this is not desirable. For example, the GB2312 encoding scheme is still commonly used in China. The charset parameter can be changed using the char_encoding compiler option, e.g.

options
{
  char_encoding = "GB2312";
}

Eleven currently supports any encoding scheme in which multibyte characters have the high bit set in every byte. (Explanation: In many places Eleven looks for ASCII characters such as quotes, newlines, and so on - and the encoding scheme must guarantee that multibyte characters can never contain a byte that could be mistaken for an ASCII character.) The UTF-8 and GB2312 encoding schemes both have that property.

If none of that made any sense to you, then don't use this option - the default value of UTF-8 should work in virtually all cases (and many installations of Apache are configured to override this charset value anyway).

The labels block

The labels block makes it possible to override any or all automatically-generated text labels ("Save", "Logout", etc.) If present, it must follow the options block. Only the labels you want to override need to be specified. Here is an example of its use, showing all labels and their default values:

  labels
  {
    login_message     "Login:";
    password_message  "Password:";
    badlogin_message  "Account does not exist or password is incorrect.";
    session_message   "End of session.";
    add_message       "Add record:";
    modify_message    "Modify record:";
    remove_message    "Remove record:";
    continue_button   "Continue";
    logout_button     "Logout";
    session_button    "Start new session";
    add_button        "Add entry ...";
    modify_button     "Modify ...";
    remove_button     "Remove ...";
    save_button       "Save";
    cancel_button     "Cancel";
    confirm_button    "Confirm";
  }

Variables and core datatypes

Variables must be declared before use. Declarations can include initialization when declaring only a single variable.

Each variable has a storage class, which is either statesafe, meaning that the variable is stored securely in the database and is persistent across HTTP transactions, or transient, meaning that the variable forgets its value between HTTP transactions. (There are very few cases where transient variables are desirable; usually you want statesafe variables.)

var variables can store numbers or strings (like Perl scalar variables). For example:

  statesafe var a, b;

  statesafe var c = 88;

  statesafe var d = "joe's example";
    
  statesafe var e = 'c:\\WINDOWS\\Temp';   /* each \\ is a single backslash */

  transient var f, g;

  transient var h = 3.14159;

Strings can be delimited by single or double quotes. A single-quote delimited string can contain double quote characters and vice versa. A single-quote delimited string can also contain single quotes if they are escaped with a backslash, and likewise, a double-quote delimited string can contain double quotes if they are escaped with a backslash. Backslashes can be used to escape any character, including the backslash character.

text variables are like var variables, except that when printed or edited they display as multi-line text boxes rather than single-line text fields. For example:

  statesafe text i = 'There is a tide in the affairs of men...';

password variables are like var variables, except that when edited the actual characters being typed in are obscured (useful when prompting a user to enter a password). For example:

  statesafe password j;

table variables are like relational database tables. Each table variable holds an entire table. For example:

  /* this declaration creates three tables, each with two columns */

  statesafe table
  {
    var first "First name";
    var last  "Last name";
  } k, l, m;

enum variables are like C enumeration types. For example:

  statesafe enum
  {
    personal "Personal";
    business "Business";
  } n;

Values that aren't syntactically valid symbols can be specified by enclosing them in quotes. For example:

  statesafe enum
  {
    "2" "Two";
    "4" "Four";
    "6" "Six";
    "8" "Eight";
  } o;

If the values and the display labels are the same, only one need be supplied. For example:

  statesafe enum
  {
    "2";
    "4";
    "6";
    "8";
  } p;

array variables are like PHP arrays or Perl hashes. They can be statesafe or transient. For example:

  statesafe array q;

  q{'first_name'} = "Joe";
  q{'last_name'}  = "Morrison";
  q{'phone'}      = "222-3333";

  transient array r;

  r{'cust_id'}  = "12345678";
  r{'cust_loc'} = 21345;        /* values can be numeric */

Types can be given names (like typedefs in C). For example:

  typedef nametable = table
  {
    var first "First name";
    var last  "Last name";
  };
    
  statesafe nametable x, y, z;

Type declarations and statements can be intermixed. For example:

  statesafe var x = 10;
  x = x + 5;
  statesafe var y, z;
  y = x;

Core expressions

Boolean operations:

  expr || expr
  expr && expr
  ! expr

Arithmetic operations:

  expr + expr
  expr - expr
  expr * expr
  expr / expr
  - expr

String operations:

  expr . expr

Numeric comparison:

  expr == expr
  expr != expr
  expr <  expr
  expr <= expr
  expr >  expr
  expr >= expr

String comparison:

  expr eq expr
  expr ne expr

Operator precedence and associativity:

  left     * /
  left     + - .
  nonassoc < > <= >=
  nonassoc == != eq ne
  right    !
  left     &&
  left     ||

Parentheses can be used to override the default precedence rules. For example:

  statesafe var x = sqr (3 * (4 + 5));  /* square root of 27 */

Core functions

  sqr(expr)    /* square root */
  md5(expr)    /* md5 digest value (hex) */
  now()        /* timestamp suitable for insertion into database */
  uid()        /* numeric uid of currently logged in user */

The uid function can be used to read all data about the current user from the auth_continuation table. For example:

  statesafe livetable
  {
    db_table = "auth_continuation";
  
    var package;
    var login;
    var creation_time;
    var auto_created_flag;
    var password;
    var name;
    var uid [id];
  } accounts;
  
  statesafe array r;
  r.set (accounts, "uid = ?", uid ());
  
  display
  {
    print ("Your user account was created on ", r{'creation_time'});
  }

To understand the details of how this works, see the information about livetables and arrays later in this documentation.

Calling external PHP or Perl functions

External PHP or Perl functions (i.e. PHP or Perl functions not directly supported in Eleven) can be called by prefixing the function name with an underscore. For example:

  /* direct call to PHP substr function (if compiled to PHP)
     or Perl substr function (if compiled to mod_perl) */

  statesafe var s = _substr ("applejuice", 5, 5);

Using this feature results in non-portable programs. For example, if you use this feature to call an external PHP function, then your program will not work if recompiled to mod_perl (unless the identical function is available in both languages). But if you are planning to use only one target language (which most people are), then this is a very handy way to gain instant access to all of the functions supported by the target language.

External functions can be called in expression or statement context. In the latter case, the return value is ignored.

This feature can be used in conjunction with the Eleven import statement, which inserts arbitrary strings verbatim near the top of the compiled output program generated by Eleven. For example you can use the import statement to insert a require statement into your compiled program, then use the underscore syntax to call the external functions, e.g.:

  import ('require "foo.php";');
  ...
  statesafe var x = _foo (y);

Core statements

Assignment statements:

  x = 3;
  y = sqr(x);
  z = "hello";

Conditional statements:

  if (expr)
  {
    stmt;
    stmt;
    ...
  }

  if (expr)
  {
    stmt;
    stmt;
    ...
  }
  else
  {
    stmt;
    stmt;
    ...
  }

  if (expr)
  {
    stmt;
    stmt;
    ...
  }
  else if (expr)
  {
    stmt;
    stmt;
    ...
  }

Loop statements:

  while (expr)
  {
    stmt;
    stmt;
    ...
  }

  for (stmt; expr; stmt)
  {
    stmt;
    stmt;
    ...
  }

Control flow statements:

  break;    /* exits the innermost loop */

  exit;     /* terminates the program */

Displays

The display command saves all application state to the RDBMS, generates an HTML screen, and halts the program until the user performs an action. For example:

  display
  {
    print ("Hello.");
  }

Displays can be named. In this case, the name is displayed in the browser title bar, and is also used by the dump11 program to generate more readable dumps.

  display "initial greeting"
  {
    print ("Hello.");
  }

It is forbidden to assign to statesafe variables in display statements, since that would make it possible for reloading a display to change the application state (which is not considered good statesafe behavior). Therefore the following program is invalid:

  statesafe var i = 1;

  display "numbers"
  {
    print ("The next number is ", i, ".");
    i = i + 1;
  }

However transient variables can be assigned within a display. Transient variables are not stored in the database, so they lose their values every time you enter or exit a display, but they can still be useful. For example, the following prints the numbers from 1 to 10 within a single display:

  display "numbers"
  {
    for (transient var i=1; i <= 10; i=i+1)
    {
      print ("The next number is ", i, ".");
    }
  }

The print statement

The print statement accepts a comma-separated list of arguments, and prints each of them in sequence. It can only be used within a display statement, and can print anything - numbers, strings, and variables of any type. For example:

  print ("Welcome, ", firstname, " ", lastname, "!");

Printing a table variable causes the entire table to be neatly displayed with column headings. Printing an array variable displays a formatted list of all the name/value pairs.

The print statement accepts an optional display style argument in square brackets. This argument can specify one of five positioning directives: header, footer, left, main, or right, e.g.

  print [position="header"] ("Welcome, ", firstname, " ", lastname, "!");

The default is position="main" if no option is specified. The display style can also specify a CSS class to be used for the contents of the print statement, e.g.

  print [class="headline"] ("Welcome, ", firstname, " ", lastname, "!");

The formatting of this print statement can then be controlled by putting something like the following into your style sheet:

  .headline
  {
    background: #FF0000;
    ...
    ...
  }

Both a positioning and a CSS class argument can be specified together:

  print [position="header" class="headline"] ("Welcome!");

Treatment of HTML special characters

HTML special characters are automatically escaped when displayed, so that the following works as expected:

display
{
    transient var s = "Joe Morrison <jdm@statesafe.org>";
    print (s);
}

However in cases where the Eleven compiler is confident that the characters are intended to be interpreted as HTML, they are not escaped. For example:

display
{
    print ("<b>Joe Morrison</b>");
}

In this example the HTML is in a literal string. It did not originate from any user input or external database, and is immediately being printed, so the Eleven compiler assumes the HTML characters are intended to be active. To force this behavior in other situations, the htmlchars display style option can be used as follows:

display
{
    transient var s = "<b>Joe Morrison</b>";
    print [htmlchars="active"] (s);
}

The edit statement

The edit statement accepts two arguments; a label (which must be a literal string), and a statesafe variable to be edited. For example:

  display
  {
    edit ("Please enter your name: ", name);
  }

If the variable is of type var, the label is displayed with a single-line text entry field next to it. If the variable is of type text, a text input area is displayed. If the variable is of type table, the edit statement generates a complete table editor. If the variable is of type enum, a drop-down menu is displayed, and so on.

In all cases, the default value is the current setting of the variable:

  statesafe var name = "Default Name";
  display
  {
    edit ("Please enter your name: ", name);
  }

The edit statement accepts a display style argument in square brackets, exactly the same as for the print statement.

If editing a var, password, text, or enum variable, the attribute javascript="..." can be included in the display style, in which case the JavaScript code in quotes is included as an attribute of the INPUT element. For example:

  statesafe var name;
  display
  {
    edit [javascript="onClick='alert(\"Hello!\")'"] ("Enter your name:", name);
  }

Generates this HTML:

  <input type="text" name=... value="" onClick='alert("Hello!")' />

Also, if editing an enum variable, the attribute control="radio" can be included in the display style, in which case the enum is rendered using radio buttons rather than as a drop-down menu. For example:

  statesafe enum
  {
    ...
  } category;

  display
  {
    edit [control="radio"] ("Please choose a category: ", category);
  }

The browse statement

The browse statement allows you to print tables (regular tables or livetables) with a custom format. The general syntax is:

  browse mytable
    headerformat
    {
      cell { print ("First name"); }
      cell { print ("Last name"); }
    }
    dataformat (r)
    {
      cell { print (r {'first_name});  }
      cell { print (r {'last_name});   }
    }
    footerformat
    {
      cell { print (""); }
      cell { print (now()); }
    }

Each cell can contain arbitrary Eleven statements. The headerformat and footerformat clauses are both optional. The dataformat clause is repeated for each row in the table, with the given parameter (r in the example) defined as a transient array, successively bound to each row.

The callbutton statement

The browse statement is particularly useful in combination with the callbutton statement, which creates an HTML button with an arbitrary label. When clicked, it calls a specified function and passes it a single, specified argument. For example:

  sub editRecord (var uid)
  {
    ...
  }

  browse mytable
    headerformat
    {
      cell { print ("First name"); }
      cell { print ("Last name"); }
      cell { print (""); }
    }
    dataformat (r)
    {
      cell { print (r {'first_name});  }
      cell { print (r {'last_name});   }
      cell { callbutton ("Edit ...", editRecord, r{'uid'}); }
    }

The control.11 example program demonstrates the use of callbuttons in conjunction with the browse command to edit table rows and also to make headers clickable to change a table's sort order.

The tableformat statement

An arbitrary table can be created in any display using the "tableformat" statement. Inside the body of this statement, rows can be created using the "rowformat" statement, and individual cells can be created within rows using the "cell" statement. The "cell" statement accepts an optional numeric colspan argument. For example:

  display
  {
    tableformat
    {
      rowformat
      {
        cell 2 { print ("Heading..."); }  // colspan = 2
      }

      for (transient var i=0; i < 10; i=i+1)
      {
        rowformat
        {
          cell { print ("Next number is "); }
          cell { print (i); }
        }
      }
    }  
  }

This example generates roughly the following HTML:

  <table>
    <tr> <td colspan="2">Heading...</td> </tr>
    <tr> <td>Next number is </td> <td>0</td> </tr>
    <tr> <td>Next number is </td> <td>1</td> </tr>
    <tr> <td>Next number is </td> <td>2</td> </tr>
    ... several rows omitted ...
    <tr> <td>Next number is </td> <td>9</td> </tr>
  </table>

The body of the "tableformat" statement can contain arbitrary code, including "rowformat" statements. The "rowformat" statement can only contain "cell" statements. Each "cell" statement can contain arbitary code - typically only "print" statements are used.

Embedding an HTML OBJECT

The object statement embeds an HTML object. Object attributes can be specified in the display style (i.e. in square brackets), and the object contents are specified as the first argument. For example:

  object [data="canyon.png" type="image/png"] ("This is a closeup of the Grand Canyon.");

Generates this HTML:

  <object data="canyon.png" type="image/png">This is a closeup of the Grand Canyon.</object>

The attributes position, class, javascript, control, element, htmlchars, and all Eleven keywords are reserved, but any other word can be used as an object attribute just like data and type in the example (position and class have the same meanings as in the print and edit statements).

Embedding an image

The object statement can embed an HTML image by including element="img" in the display style. In this case, an argument must still be supplied but it is ignored (typically a null string is supplied). For example:

  object [element="img" src="dog.jpg" alt="Fido" width="128" height="128"] ("");

Generates this HTML:

  <img src="dog.jpg" alt="Fido" width="128" height="128" />

Embedding a SCRIPT

The object statement can embed an HTML script by including element="script" in the display style. In this case, the argument is used as the script contents. For example:

  object [element="script" type="text/javascript"] ("... some JavaScript...");

Generates this HTML:

  <script type="text/javascript">... some JavaScript...</script>

Suppressing the continue button

Normally every display includes a continue button at the bottom of the screen to resume the application. But sometimes it is undesirable to display the continue button. The no_continue option suppresses it:

  display no_continue "numbers"
  {
    for (transient var i=1; i <= 10; i=i+1)
    {
      print ("The next number is ", i, ".");
    }
  }

Subroutines

Subroutines are supported. Subroutine arguments are always considered statesafe. For example:

  sub myadd (var x, var y)
  {
    display
    {
      print ("Boo!");
    }
    return x + y;    /* this works because x and y are statesafe */
  }

Recursion is supported:

  sub fact (var x)
  {
    if (x <= 1)
    {
      return 1;
    }
    else
    {
      return x * fact (x - 1);
    }
  }

  statesafe var y = fact (6);

The Eleven implementation implements its own stack using PHP or Perl hash tables (associative arrays), so the maximum depth of the recursion is limited only by the amount of memory on the web application server. The popular term for this is a stackless implementation (a misnomer, but a catchy one).

Warning: Whenever a display statement is executed, the entire application state is saved to the database, including active stack frames. So if you execute a display statement while deep inside a recursion, you will be storing a lot of data.

Local variables of any type can be declared inside subroutines. Local and global variables are accessed using the same syntax, e.g.:

  statesafe var x = 4;

  sub test (var y)
  {
    statesafe var z = 5;
    return x + y + z;
  }  

Scoping notes: Subroutines cannot be defined within other subroutines, and are not first class. Scoping is lexical - dynamic scoping is not supported. Ambiguity is not allowed, so local variables cannot shadow global variables that have already been declared.

Eleven makes it easy to create menus that enable users to jump to different places within your application, while maintaining state safety. This is done using launcher variables. Launcher variables are used in conjunction with a statement called prepare/mission (i.e. the "launcher" is "prepared" for a "mission"). A typical code sequence looks like this:

  statesafe launcher menu;
  while (1)
  {
    prepare menu
    {
      "Review statements" { review   (); }
      "Transfer money"    { transfer (); }
    }
    mission
    {
      display
      {
        print [left] ("Functions: ", menu);
        print [main] ("Welcome to the ACME web banking service!");
      }
    }
  }

The prepare statement programs the launcher with a list of labels and corresponding code fragments for the duration of a mission. If the launcher is printed while the mission is in progress, Eleven displays a nicely-formatted menu showing the launch options. If the user clicks on an option, the corresponding code fragment is executed, after which the mission terminates immediately. After the mission terminates, the launcher is cleared (and is typically reset by looping back to the prepare/mission statement).

This may sound complicated, but it's actually quite straightforward once you get the hang of it. The code example above displays a menu along the left side of the screen, with two buttons labelled "Review statements" and "Transfer money". If clicked, the buttons call the "review()" and "transfer()" subroutines respectively. The enclosing while loop causes the program to reset the launcher and return to the main display after each function is completed. (See the acme.11 or birdseed.11 demos for good launcher examples.)

If the launcher variable is printed in the left or right sidebar, Eleven displays it as a vertical list of buttons, like this:

Review statements
Transfer money

If printed anywhere else, Eleven displays it horizontally:

[ Review statements | Transfer money ]

If a user action causes the mission to complete without selecting any launcher options, then the mission terminates (at which point the enclosing while loop returns the program to the main display).

Multiple launchers can be active at any time. It is easy to print multiple launchers in one navigation bar, for example:

  statesafe launcher menu1, menu2;
  prepare menu1
  {
    ....
  }
  mission
  {
    prepare menu2
    {
      ....
    }
    mission
    {
      display
      {
        print [left] ("Some functions", menu1, "More functions", menu2);
      }
    }
  }

Or alternatively, you can put one navigation bar on the left, and another on the right:

  ...
  print [left] ("Some functions", menu1);
  print [right] ("More functions", menu2);
  ...

Typically, code sequences triggered by a launcher contain other displays. It is common to print launchers in those displays identically to the launcher in the main display. So as a user navigates from screen to screen, the launcher appears fixed in place. So in the above example, you might write the review function this way:

  sub review ()
  {
    display
    {
      print [left] ("Functions: ", menu);
      print [main] ("Review statements");

      ... more review commands...
    }
  }

But if you prefer, your application can generate completely different launcher displays as the user navigates from screen to screen.

After any option in a prepare/mission statement a condition can be specified in square brackets. If the prepare/mission statement is executed and the condition is false, the option is not displayed. For example:

  prepare menu
  {
      "Welcome message"                  { welcome (); }
      "Enter transaction"                { enterTransaction (); }

      // only available for system administrator
      "Delete transaction" [userid == 0] { deleteTransaction (); }
  }
  mission
  {
      welcome ();
  }

Accessing external database tables

Possibly the most important feature in the Eleven language is the ability to easily access external database tables (that is, tables other than the private ones normally used by Eleven). This allows Eleven to be integrated with other database applications. For example, Eleven can be used to quickly create web-based reports for other applications.

In Eleven, external tables are accessed using livetable variables (pronounced like "livestock", not "Liverpool"). A livetable is like a SQL view. It doesn't store any data in and of itself, but instead stores information about the SQL tables you are interested in. When you initialize a livetable variable, no database queries are performed. The livetable just stores your query criteria. But when you print, edit, or perform various other operations on the livetable (which you can do just like for a regular Eleven table), Eleven spontaneously generates suitable queries and fetches the data on demand.

Eleven provides functions that operate on livetables to filter query results, sort them, group them, and display them one page at a time. These functions also do not cause queries to be performed. They only store query modifiers, and are implemented by including ORDER BY, GROUP BY, etc. clauses in the SQL queries when they are generated later.

Multiple livetables can be declared referring to the same underlying SQL tables if you wish to view a particular table several different ways in your application.

Here are some examples of livetable declarations:

  /* example 1  */

  statesafe livetable
  {
    db_connect = "DBI:mysql:database=eleven;host=localhost";
    db_user    = "www";
    db_pass    = "wwwpass";
    db_table   = "customer";

    var cust_id    "Customer identifier"    [auto];
    var cust_loc   "Customer location code" [id];
    var first_name "First name";
    var last_name  "Last name";
    var phone      "Phone number";
  } customers;

  /* example 2 */

  statesafe livetable
  {
    db_table = "resource";

    var name     "Resource name";
    var location "Resource location";
  } resources;

Separate db_connect, db_user, and db_pass parameters can be specified for each livetable. If not specified, these parameters default to the main program options. The db_table parameter is the name of the external table to be accessed, and must always be specified.

The remaining items are a list of the table columns. Attributes may be specified after each column in square brackets. Valid attributes are auto or id. Either of these attributes implies that the column is part of the unique row identifier (collectively, the auto and id columns must form a unique row identifier). The only difference is that auto designates an AUTO_INCREMENT column in the database, while id designates an identifier column that must be assigned explicitly by the program.

If you print the example livetables shown above, i.e.:

  display
  {
    print ("Customer list:", customers, "Resource list:", resources);
  }

Eleven automatically generates the following SQL queries:

  SELECT cust_id, cust_loc, first_name, last_name, phone FROM customer
  SELECT name, location FROM resource

The resulting display looks like this:

Livetable demo screenshot

The columns are always shown in the same order as they are listed in the livetable declaration. If you do not want to see all of the columns in your display, simply define your livetable with only the columns you want to see. (You can define multiple livetables on the same underlying SQL table with different combinations of columns if you like.)

If you wish to edit a table, you can simply write this:

  display
  {
    edit ("Update customer list:", customers);
  }

Eleven handles all of the editing automatically. If the user clicks add record, Eleven automatically generates an add record display (same as for regular tables). The user can enter values for all fields except for the ones with the auto attribute (since the database is expected to generate those automatically). When the user clicks save, the new record is added directly to the external table using a query like this:

  INSERT INTO customer (cust_loc, first_name, last_name, phone)
    VALUES ('1003', 'Joe', 'Morrison', '111-222-3333')

If the user clicks modify record, Eleven generates a modify record display similar to the one for regular tables, except that auto and id columns cannot be modified. When the user clicks save, Eleven executes a SQL update statement with the where clause constructed automatically from the conjunction of all auto and id columns, e.g.:

  UPDATE customer
    SET first_name = 'James', last_name = 'Johnson', phone = '890-567-1234'
    WHERE cust_id = '2' AND cust_loc = '1003'

Livetables without id or auto fields cannot be edited, only printed. The rules for delete are the same as for modify.

Accessing individual rows and columns

In addition to being printed or edited, livetables can also be assigned to regular statesafe tables. This causes a SELECT query to be generated (just as if the table were being printed). Filter, sort, and group criteria are taken into account. The results are then assigned to the statesafe table. Only the columns defined in the statesafe table are copied. If the statesafe table includes any columns that are not present in the livetable, they are set to "undefined".

After being assigned to a regular statesafe table, individual rows and columns can be accessed.

The rowcount method allows you to find out the number of rows in any table (regular or livetable). So to iterate over every row in a livetable, you would first assign it to a statesafe table, then use the rowcount method, e.g.

  statesafe livetable { ... } mylivetable;
  statesafe table { ... } mytable;

  mytable = mylivetable;

  for (statesafe var i=0; i < mytable.rowcount (); i=i+1)
  {
    ...
  }

Array variables are used to access individual table rows, with the column names as keys. The set method on arrays (statesafe or transient) copies a row from a regular statesafe table into an array. That allows access to the individual fields in a given row. The set method takes a table and an index as arguments (indices start at 0). For example:

  statesafe livetable { ... } mylivetable;

  statesafe table
  {
    var name   "Name";
    var number "Number";
  } mytable;

  /* copy name and number columns to statesafe table */
  mytable = mylivetable;

  /* copy row 8 to statesafe array */
  statesafe array r;
  r.set (mytable, 8);

  display
  {
    print ("The 8th name is ", r{'name'});
  }

The setlabel method is the same as set except that the field label is used as the key, rather than the field name (i.e. Name rather than name, Number rather than number, etc. This is convenient for printing a row, e.g.

  ...

  mytable = mylivetable;

  statesafe array r;
  r.setlabel (mytable, 8);  /* copy row 8 with labels */

  display
  {
    print ("The 8th row with labels:", r);
  }

As a shortcut, if you need to retrieve a single row from a livetable you can called the set method with a livetable argument, followed by selection criteria, e.g.

  statesafe array r;
  r.set (mylivetable, "deptcode = ?", 132);

Only one row is read from the livetable, after which the filter criteria are immediately forgotten. (They are not stored permanently, as in the "filter" method described below.) If the operation is successful, the string "ok" is returned, otherwise an error message is returned. (The return value is ignored if this operation is performed in statement context.)

Inserting rows

Any array (statesafe or transient) can be inserted into a regular table or a livetable. The details of how to perform the insertion are determined by the table that's being inserted into. Fields are read from the array as needed. For example, to insert a transient array into the customers livetable described above, you would write:

  transient array r;

  // ... assign array fields

  customers.insert (r);

This results in the SQL query:

  INSERT INTO customer (cust_loc, first_name, last_name, phone)
    VALUES (r{'cust_loc'}, r{'first_name'}, r{'last_name'}, r{'phone'})

Eleven automatically omits the cust_id field since it was declared to be an auto field, therefore the database is expected to generate its value automatically.

If required fields are missing from the array variable, the string 'undefined' is used instead.

If inserting an array into a regular table (rather than a livetable), then a unique row id is generated automatically for the new row (these row ids are not normally displayed). The generated row id is saved in the array using the key eleven_rowid in case it is needed later. For example:

  statesafe table
  {
    ...
  } mytable;

  transient array r;
  ...
  mytable.insert (r);

  statesafe var newid = r{"eleven_rowid"};

The insert method returns the string "ok" if the insertion was successful, otherwise it returns an error message. When inserting into livetables it is always best to write something like this:

  statesafe var status = mytable.insert (r);
  if (status ne "ok")
    {
      display
	{
	  print ("Error: ", status);
	}
    }

Updating or deleting rows

Any array (statesafe or transient) can be used to update or delete from a regular table or a livetable, like this:

  customers.update (r);
  customers.delete (r);

For a livetable, the "auto" and "id" fields in the row variable are collectively used as the unique row identifier, just as for the edit statement described earlier in this document.

For a regular table, Eleven uses the key eleven_rowid in the array to determine which row to update or delete.

The update and delete methods both return the string "ok" if the operation was successful, otherwise they return an error message. When working with livetables it is always best to write something like this:

  statesafe var status = mytable.update (r);  // similarly for delete
  if (status ne "ok")
    {
      display
	{
	  print ("Error: ", status);
	}
    }

It is also possible to delete a range of rows at once from a livetable, as follows:

  statesafe var status = mytable.delete ("(cust_loc = ?) AND (last_name = ?)", 1003, "Morrison");

The first argument is a SQL expression, and the remaining arguments are substituted for the question marks in sequence. All rows that match the filter criteria are deleted, after which the filter criteria are immediately forgotten. (They are not stored permanently, as in the "filter" method described below.)

Column aliases

If you want a livetable to include a computed column, you can specify a column expression in parens after the column name. The column expression is what actually gets selected, and the column name is used as an alias. For example:

  statesafe livetable
  {
    db_connect = "DBI:mysql:database=eleven;host=localhost";
    db_user    = "www";
    db_pass    = "wwwpass";
    db_table   = "customer";

    var cust_id                       "Customer identifier"    [auto];
    var cust_loc                      "Customer location code" [id];
    var now   ("NOW()")               "Current time";
    var sum   ("2+3")                 "Sum of 2 and 3";
    var first ("customer.first_name") "First name";
    var last  ("customer.last_name")  "Last name";
    var phone                         "Phone number";
  } customers;

  display
  {
    print (customers);
  }

This results in this query:

  SELECT
    cust_id,
    cust_loc,
    NOW() AS now,
    2+3 AS sum,
    customer.first_name AS first,
    customer.last_name AS last,
    phone
  FROM customer

Which produces the following display:

Column alias demo screenshot

Filtering, sorting, grouping, and paging

Filter criteria can be set at any point in your Eleven program, like this:

  customers.filter ("(cust_loc = ?) AND (last_name = ?)", 1003, "Morrison");

The first argument is a SQL expression, and the remaining arguments are substituted for the question marks in sequence. The filter command simply stores the criteria; it does not cause a query to be executed. When the table is printed, edited, or copied, the following query is generated:

  SELECT cust_id, cust_loc, first_name, last_name, phone FROM customer
    WHERE (cust_loc = '1003') AND (last_name = 'Morrison')

The filters are statesafe, and persist until cleared using this syntax:

  customers.filter ();

Sort criteria can also be set, like this:

  customers.sort ("last_name", "first_name DESC");

This example sets last_name as the primary sort field, and first_name (descending) as the secondary sort field. Just as for the filter criteria, the sort criteria are simply stored for use when the livetable needs to be displayed or accessed. At that point, the query is performed and takes into account the sort criteria:

  SELECT cust_id, cust_loc, first_name, last_name, phone FROM customer
    ORDER BY last_name, first_name DESC

Multiple sort fields can also be specified in a single argument:

  customers.sort ("last_name, first_name DESC");

The sort criteria can be cleared this way:

  customers.sort ();

All types of criteria can be used together in any combination. For example the filter and sort criteria can be used together like this:

  customers.filter ("cust_loc = ?", 1003);
  customers.sort ("last_name", "first_name DESC");

Which generates queries like this:

  SELECT cust_id, cust_loc, first_name, last_name, phone FROM customer
    WHERE cust_loc = '1003'
    ORDER BY last_name, first_name DESC

GROUP BY criteria can be set and cleared the same way as sort criteria. Just as for filter and sort criteria, GROUP BY criteria are simply stored for use when the livetable needs to be displayed or accessed.

Warning: GROUP BY criteria should be used with extreme caution. GROUP BY criteria should only be used with livetables whose columns are the same as the GROUP BY criteria, or are aliased to aggregate functions. For example:

  statesafe livetable
  {
    db_table = "product";

    var category                     "Category";      /* OK - same as GROUP BY criteria */
    var average_price ("AVG(price)") "Average price"; /* OK - aggregate */
    var max_price     ("MAX(price)") "Maximum price"; /* OK - aggregate */
  } productStatistics;

  productStatistics.group ("category");

  display
  {
    print (productStatistics);
  }

At the moment of the print statement, the following query is generated:

  SELECT
    category,
    AVG(price) AS average_price,
    MAX(price) AS max_price
  FROM product
  GROUP BY category;

In addition to filtering, sorting, and grouping, livetables can be viewed in pages like this:

  customers.pagesize (20);

This example limits the query to returning 20 records at a time. To find out the number of pages in your livetable given the current page size, you can use the pagecount function, e.g.

  statesafe var c = customers.pagecount ();

To set the current page, use the page function, e.g.

  customers.page (8);

This means that you would like the next print or edit statement to show the 8th page of customer information (page numbers start at zero). Given the settings above, the following query would be generated:

  SELECT cust_id, cust_loc, first_name, last_name, phone FROM customer LIMIT 160, 20

i.e. select 20 rows of data starting at row 160. To clear the paging mechanism and stop using limit clauses in the queries, write:

  customers.pagesize (0);

Filtering, sorting, grouping, and paging can generally be used together without conflict. Currently the only limitation is that the pagecount method does not work when grouping is being used.

The birdseed.11 demo contains example code showing how to do paging, and demonstrates how you can use a launcher to create buttons enabling the user to move back and forth from page to page. The control.11 application demonstrates more complicated uses of livetable features.

Inner and outer joins

Table joins can be expressed by listing multiple tables for the db_table parameter, e.g.:

  statesafe livetable
  {
    db_table = "customer", "transaction";
    ...
  } customerTransactions;

This results in a simple INNER JOIN query. To specify join criteria, use the filter method described above, e.g.

  customerTransactions.filter ("transaction.cust_id = customer.cust_id");

This results in the query:

  SELECT ... FROM customer, transaction
    WHERE transaction.cust_id = customer.cust_id

LEFT OUTER JOINs can be specified by putting the join criteria in parens next to the name of the table being joined. For example:

  statesafe livetable
  {
    db_table = "transaction",
               "customer" ("customer.cust_id = transaction.cust_id");
    ...
  } transactions;

This results in the query:

  SELECT ... FROM transaction
    LEFT OUTER JOIN customer ON customer.cust_id = transaction.cust_id

Multiple tables can be joined in a single livetable, with INNER and LEFT OUTER joins freely intermixed.

Livetables with joins can be used in conjunction with the filtering, sorting, grouping, and paging options, but they cannot be edited. They can only be printed, copied, or browsed.

Note: Sometimes a join can make a livetable column name ambiguous (if the tables being joined both have a column with the same name, and that column is listed in the livetable declaration). In that case the solution is to use a column alias to disambiguate the name. For example:

  statesafe livetable
  {
    db_table = "transaction",
               "customer" ("customer.cust_id = transaction.cust_id");

    cust_id ("customer.cust_id") "Customer identifier";
  } transactions;

In this example, cust_id is unambiguous, since it is aliased to "customer.cust_id".

Dynamic enumeration variables

Normally, the values and labels associated with enum variables are specified in the type definition. But enums can also be defined without any values or labels, like this:

  statesafe enum e;

In this case Eleven generates a dynamic enum, whose values and labels can be changed at runtime by calling the setvalues method. This method takes three arguments: a table containing the replacement values and labels (either a regular table or a livetable), the name of the table column containing the new enum values, and the name of the table column containing the new enum labels. For example:

  statesafe livetable
  {
    db_table = "demo_customer";
  
    var customer_id "Customer id" [auto];
    var name        "Name";
    var address     "Address";
  } customers;
  
  statesafe enum customer;    /* dynamic enum */

  /* load dynamic enum with current table values */
  customer.setvalues (customers, "customer_id", "name");

If the dynamic enum is being loaded from a livetable, a query is spontaneously generated to fetch the values and labels. If the livetable contains filter and sort criteria, those are taken into account when loading the dynamic enum. However paging criteria are ignored. Dynamic enums are statesafe, so the values and labels persist after the setvalues operation, even if the source table is deleted immediately afterward.

The Eleven Control Panel application

Eleven now includes a Control Panel application to manage user accounts, maintain the database, and review system activity. This program is written in Eleven (file control.11 in the Eleven distribution).

Here is an example screenshot:

Control Panel screenshot

Tracking program status

The status command allows programs to record the name of the current sequence of steps being carried out by the end user. For example, you might write:

  status ("Filling out tax return");  /* set status */

  ...
  /* long code sequence for user to fill out tax return */
  ...

  status ();  /* clear status */

In the future, the Eleven Control Panel will provide reports based on this information, for example a report showing all users with non-null status whose sessions have been idle for a long time. That would show how many people encountered a problem partway through a long sequence of steps, and where they got bogged down.


SourceForge.net Logo Valid XHTML 1.0!