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 samplenote]$ cd /web/service0/packages/samplenote/www
[service0@yourserver www]$ emacs index.tcl

Paste this into the file. There are several things to note about the file:

ad_page_contract {
    This is the main page for the package.  It displays all of the Sample 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.1.2.1 2003/03/30 20:33:12 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>}}
    {delete "" {} {<td><a href="note-delete?note_id=$note_id">Delete</a></td>}}
}

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

Now 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>

Now we 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. (To be precise, 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 pages are in the APM, check to make sure that the self-documenting code is working.

Go to http://192.168.0.2:8000/samplenote/. You should see something like this:

Sample Notes
Your Workspace : Main Site : Sample Note 

No data found.

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 the section called “Debugging and Automated Testing”. Note also that, while tcl and adp pages update automatically, xql pages get cached. So if you change an xql page, the change won't take effect unless you restart the server, reload the package via the APM, or put a watch on the file in the APM file list. With a watch, which lasts until the next service restart, the file will be updated each time it is changed on disk.

We'll create a single page to handle both adding and editing records. First, create the tcl:

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

The page takes a single, optional input parameter, note_id. If it's present, logic within ad_form will assume that we're editing an existing record. 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.

ad_page_contract {
        Simple add/edit form for samplenote.
} {
    note_id:optional
}
set user_id [ad_maybe_redirect_for_registration]

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"
ad_script_abort
}

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(: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>

And now the user-visible page:

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

<master>
<formtemplate id="note"></formtemplate>

Go to the APM as before and scan for new files and add your new work. Then test all this by going to the package home page and adding and editing a few records.

Now we need a way to delete records. We'll create a recursive confirmation page.

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

This page requires a note_id to determine which record should be deleted. It also looks for a confirmation variable, which should initially be absert. If it is absent, we create a form to allow the user to confirm the deletion. The form calls the same page, but with hidden variables carrying both note_id and confirm_p.

ad_page_contract {
    A page that gets confirmation and then delete notes.

    @author joel@aufrecht.org
    @creation-date 2003-02-12
    @cvs-id $Id: tutorial-pages.html,v 1.1.2.1 2003/03/30 20:33:12 joela Exp $
} {
    note_id:integer
    confirm_p:optional
}

set title "Delete Note"

if {[exists_and_not_null confirm]} {
    # if confirmed, call the database to delete the record
    db_1row do_delete { *SQL* }
    ad_returnredirect "index"
} else {
    # if not confirmed, display a form for confirmation
    set note_name [db_string get_name { *SQL }]
    set title "Delete $note_name"
    template::form::create note-del-confirm
    template::element::create note-del-confirm note_id -value $note_id -widget \hidden
    template::element::create note-del-confirm confirm_p -value 1 -widget hidden
}

Now the database calls:

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

<?xml version="1.0"?>
<queryset>
  <fullquery name="do_delete">
    <querytext>
      select samplenote__delete(:note_id)
    </querytext>
  </fullquery>
  <fullquery name="get_name">
    <querytext>
      select samplenote__name(:note_id)
    </querytext>
  </fullquery>
</queryset>

And the adp page:

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

<master>
<property name="title">@title@</property>
<h2>@title@</h2>
<formtemplate id="note-del-confirm"></formtemplate>
</form>

Now test it by adding the new files in the APM and then deleting a few samplenotes.

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-delete.adp' for addition
cvs add: scheduling file `note-delete.tcl' for addition
cvs add: scheduling file `note-delete.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]$