Each user-visible page in your package has, typically, three parts. The xql file contains any database queries, the tcl file holds the procedural logic for the page and does things like check permissions, invoke the database queries, and modify variables, and the adp page holds html. The default page in any directory is index, so we'll build that first, starting with the tcl file:

[service0@yourserver postgresql]$ cd /var/lib/aolserver/service0/myfirstpackages/www
[service0@yourserver www emacs index.tcl

Paste this into the file.

ad_page_contract {
    This is the main page for the package.  It displays all of the Notes and provides links to edit them and to create new Notes.

    @author rhs@mit.edu
    @creation-date 2000-10-23
    @cvs-id $Id: tutorial-pages.html,v 1.12 2003/11/05 14:46:52 joela Exp $
    @param orderby indicates when the user clicks on a column to order by that \column
    @return table_html preformatting html table constructed by querying the sam\plenotes table

} {
    {orderby:optional {title}}
} -properties {
    table_html
}
# define the columns in the table
set table_def   {
    {title "Note"}
    {body "Contents"}
    {edit "" {} {<td><a href="note-edit?note_id=$note_id">Edit</a></td>}}
}

# construct an html table from the samplenotes database table
set table_html [ad_table -Torderby $orderby notes_query { *SQL* } $table_def]

There are several things to note about the file:

Put the database query into a separate file. If the database query is exactly the same for Oracle and PostgreSQL, it can go into a file with the same name as the tcl file but an xql extension, e.g., index.xql. If it is database-specific, it goes in index-oracle.xql or index-postgresql.xql. The format is the same in each case, an XML structure that contains the SQL query. Create the file now.

[service0@yourserver www]$ emacs index.xql

Note that the name parameter of the fullquery tag exactly matches the SQL query name specified in the ad_table call. Also, the SQL query ends with a tcl function call that generates a SQL ORDER BY clause using several TCL variables.

<?xml version="1.0"?>
<queryset>
  <fullquery name="notes_query">
    <querytext>
    select note_id,
           title,
           body
      from samplenote
    [ad_order_by_from_sort_spec $orderby $table_def]
    </querytext>
  </fullquery>
</queryset>

Create the user-visible page.

[service0@yourserver www]$ emacs index.adp

The first line indicates that this page should be rendered within the the master template, which defaults to /web/service0/www/default-master. The second line passes a title variable to the master template. The third line inserts the contents of the variable table_html. The last line is a link to a page we haven't created yet.

<master>
<property name="title">Sample Notes</property>
@table_html@
<p><a href="note-edit">Add a note</a></p>

Before we can test these files, we have to notify the package manager that they exist. (More precisely, the tcl and adp will work fine as-is, but the xql file will not be recognized until we tell the APM about it.).

Now that the APM is aware of your files, check to make sure that the self-documenting code is working.

Go to http://yourserver.test:8000/note/. You should see this:

Sample Notes
Your Workspace : Main Site : Sample Note 

No data found.

Add a note.

foo@yourserver.test

Since our table is empty, it's a pretty boring page. So next we'll make it possible to add records.

If you get any other output, such as an error message, skip to Section , “Debugging and Automated Testing”.

We'll create a single page to handle both adding and editing records. In this recursive approach, the same tcl function can present a blank HTML form, present the same form pre-loaded with an existing record, and handle the resulting submission of either updated or new records. This recursive approach reduces the total amount of code and files. First, create the tcl:

[service0@yourserver www]$ emacs note-edit.tcl

Paste and save and edit:

ad_page_contract {
        Simple add/edit form for samplenote.
} {
    note_id:integer,optional
}
set user_id [ad_maybe_redirect_for_registration]
set title "Add a note"

if {[exists_and_not_null note_id]} {
    set title "Edit a note"
}

ad_form -name note -form {
    note_id:key
    {title:text
        {label "Title"}
    }
    {body:text(textarea)
        {label "Body"}
    }
} -select_query_name note_query -new_data {
db_1row do_insert { *SQL* }
} -edit_data {
db_dml do_update { *SQL* }
} -after_submit {
ad_returnredirect "index"
}

We use ad_form to automate most of the work here. Ad_form is a wrapper for the template functions for creating HTML forms. These functions should always be used for HTML forms; this promotes consistency and, since all template functions use the same stylesheet system, makes it easy to change the appearance of forms.

The page takes a single, optional input parameter, note_id. If it's present, ad_form will assume that we're editing an existing record, look up that record, and pre-populate the form. We'll also check and change the page title if necessary. We check user_id with ad_maybe_redirect_for_registration, which will redirect to the login page (with an automatic return path to bring them back after login or registration) if the visitor isn't logged in. Then we call ad_form, specifying the primary key of the table, the fields we want to edit, and functions for insert and update.

Next, we create the database functions.

[service0@yourserver www]$ emacs note-edit.xql

<?xml version="1.0"?>
<queryset>
  <fullquery name="do_insert">
    <querytext>
        select samplenote__new(null,:title, :body,null,:user_id,null,null)
    </querytext>
  </fullquery>
  <fullquery name="do_update">
    <querytext>
       update samplenote
          set title = :title,
              body = :body
        where note_id = :note_id
    </querytext>
  </fullquery>
  <fullquery name="note_query">
    <querytext>
      select title,
             body
        from samplenote
       where note_id = :note_id
    </querytext>
  </fullquery>
</queryset>

Create the user-visible page:

[service0@yourserver www]$ emacs note-edit.adp

<master>
<property name="title">@title@</property>
<property name="context">{@title@}</property>
<formtemplate id="note"></formtemplate>

The property tags are passed to the master template, which uses their values to set the page title and context bar (breadcrumb trail). We use the same variable, title, for both variables but wrap it in curly brackets for context so that the spaces aren't interpreted separators. The formtemplate tag outputs the form html with the matching name.

Go to the APM as before and reload. Then test all this by going to the package home page and adding and editing a few records.

Put your new work into source control.

[service0@yourserver www]$ cvs add *.adp *.tcl *.xql
cvs add: cannot add special file `CVS'; skipping
cvs add: doc/CVS already exists
cvs add: scheduling file `index.adp' for addition
cvs add: scheduling file `index.tcl' for addition
cvs add: scheduling file `index.xql' for addition
cvs add: scheduling file `note-edit.adp' for addition
cvs add: scheduling file `note-edit.tcl' for addition
cvs add: scheduling file `note-edit.xql' for addition
cvs add: use 'cvs commit' to add these files permanently
[service0@yourserver www]$  cvs commit -m "new work"
/cvsroot/service0/packages/samplenote/www/note-edit.xql~,v  <--  note-edit.xql
(many lines omitted)
initial revision: 1.1
done
[service0@yourserver www]$