Last month, we continued our exploration of OpenACS by looking at how individual applications are packaged together using APM (the ArsDigita Package Manager). Every OpenACS application normally combines database tables with server-side programs, which means that installing or upgrading a package is more complicated than copying some files. As we saw last month, the APM application makes it relatively easy to install a package and then instantiate it under one or more URLs.

This is great if you care only about using existing APMs. But most OpenACS installations will require new, custom packages that have their own data models and programs. While it's theoretically possible to develop OpenACS applications without APMs, doing so makes it hard to distribute your software, track versions or standardize your installation procedure.

This month, we cover how to develop our own simple web/database application using APM. The final result is an application that someone else can load into their OpenACS system.

The first step in creating a new OpenACS application is making a new skeleton package via the web-based APM program. By default, this program is available only to the user with administration rights to an OpenACS system, so you might need to ask the administrator to modify the permissions when you begin your development work.

On most OpenACS systems, you can launch the APM program via the /acs-admin/apm/ URL. This program displays all of the installed APMs, including each one's name, version and number of files it includes. The file count normally includes .sql files (for creating and removing database definitions), .tcl files (containing Tcl program code), .adp files (for web templates similar to ASP or JSP) and .xql files (containing SQL queries). However, an APM can contain other files, including images, text files or even something more unusual, such as Flash.

As we saw last month, we can use this screen as an entry point to install, inspect and modify the packages on our system. But if we go down to the bottom of this list and click the create a new package link, we can begin the process of creating our own application.

The initial “create a package” screen asks for a number of parameters that will help APM create a .info file that describes the package to the system. Assuming that we want to create a “hello, world” application with a minimum amount of work, we can fill in a small number of fields:

When finished, ensure that the “write a package” specifier box is checked, and click the “create package” button at the bottom of the page. You will be taken to the management screen for this particular package. If you look in the packages subdirectory on the filesystem where your OpenACS toolkit was installed, you'll see an atf-hello directory, containing an atf-hello.info file in the correct XML format.

Now that OpenACS recognizes our package, we can get to work designing the data model for our package. As all experienced web/database developers know, designing the tables is the hard part; once you know how they will work, creating the applications that add, delete and modify that data is pretty easy.

Our application will give us a simple guest book, in which visitors to the site can enter comments on our page. (OpenACS comes with a more general and powerful facility for doing this on any page on the site, but we will ignore this in the interest of learning how to create a package.) Our data model will look like this:

CREATE TABLE atf_hello_postings (
  posting_id    SERIAL   NOT NULL,
  user_id       INTEGER  NOT NULL  REFERENCES users
                ON DELETE CASCADE
                ON UPDATE CASCADE,
  entry_date    TIMESTAMP  NOT NULL  DEFAULT NOW(),
  posting       TEXT     NOT NULL
                CHECK (posting <> ''),
  PRIMARY KEY(posting_id)
);

On a purely technical level, the previous table definition probably looks reasonable to anyone who has worked with PostgreSQL before. We set up posting_id as an integer primary key, user_id as a foreign key, a timestamp field (containing date and time information) and then a text field to contain the actual posting. Notice how our table name begins with atf_hello, reflecting the fact that it is in the atf-hello APM. Keeping table names consistent with package names is a primitive form of namespace management, but it works well enough if everyone sticks with it.

The above works under PostgreSQL but isn't guaranteed to work with Oracle. Given that the OpenACS community prides itself on working transparently with both databases, what should we do to avoid alienating our high-end colleagues?

The answer is that when installing the atf-hello package, APM looks for a file named sql/atf-hello-create.sql. If such a file exists, it is assumed to work on all support databases. If not, APM looks for subdirectories named postgresql and oracle and executes atf-hello-create.sql in the appropriate directory. So if your system uses PostgreSQL, the above SQL would be saved in a file named sql/postgresql/atf-hello-create.sql. Official OpenACS packages are supposed to work with both Oracle and PostgreSQL out of the box, which means that it's rare to find a package that works with only one brand or the other. (The examples in this month's column are guaranteed to work only with PostgreSQL, although porting them to Oracle should not be too difficult.)

OpenACS also allows us to create a cleanup script, named sql/PACKAGE-drop.sql, that removes all the tables and stored procedures that the create script defined. We thus create a file named sql/postgresql/atf-hello-drop.sql.

APM knows how to create the data model when a package is first installed, but not when you're doing development work. So to install atf-hello into the database, you'll need to handle it manually:

psql -f atf-hello-create.sql openacs4

Of course, this assumes that openacs4 is the name of your OpenACS database, that the PostgreSQL server is running on the same machine as the psql client and that your current user name has access rights to that database.

Now that our data model is installed, it's time to write an application that uses it. OpenACS 4 introduced a new templating system that builds upon ADP (the AOLserver equivalent of ASP or JSP), which I have found to be one of the best parts of OpenACS.

ASP-like pages are easier for nonprogrammers to understand than standard server-side programs. However, hybrids of HTML and programs tend to become bottlenecks, because the designer and developer cannot work on the file simultaneously.

OpenACS templates are a refreshing solution to this problem. We divide the page into two parts: one (the .tcl page) for the programmer and the other (the .adp page) for the designer. The .tcl page begins with a contract describing which values it expects to receive, as well as which values it will provide to the ADP page. The Tcl page ends with a call to ad_return_template, which looks for an .adp page of the same name, substitutes variables appropriately and then runs the ADP parser on the page.

The Tcl page can pass values to the ADP page in the form of data sources, a fancy term for variable. If the Tcl page says

set five 5

the ADP page can retrieve this value anywhere by surrounding the variable name with @ signs, as in @five@. If the variable five has not been defined or exported in the page contract, OpenACS produces a runtime error, complaining that no such variable exists.

This splitting of responsibilities means that the designer and programmer can work independently, so long as the agreed-to interface described in the page contract (for Tcl page inputs and outputs) remains intact.

OpenACS comes with a large number of functions designed to help programmers create HTML forms quickly and easily. For the purposes of this introduction, we won't use these functions; we will use simple, raw HTML instead.

Our application will consist of two URLs:

In other words, we will need two Tcl pages and one ADP page for our application. These will all go in the www subdirectory under atf-hello; if that directory doesn't exist, create it, double-checking that it is owned by the same user as AOLserver is running.

The first Tcl page (posting.tcl), shown in Listing 1, expects no parameters and exports a single data source named postings. The Tcl page begins with a call to ad_page_contract, which allows us to comment on the owner and purpose of the file (in the first parameter), the inputs we receive (in the second parameter, blank posting.tcl) and the data sources we export (in the third parameter, named properties for historical reasons). The Tcl page ends with a call to ad_return_template, which looks for an .adp file with the same name as the current .tcl file.

Listing 1. posting.tcl

While each data source is really a simple Tcl variable, the OpenACS templating system disguises the true nature of these variables somewhat, assigning each data source a name and a type (multirow, list, onevalue or onerow). In this particular case, our postings data source is a multirow, which means it will contain multiple result rows from our SELECT. The db_multirow procedure takes three arguments: the name of the variable into which the rows should be read, the name of the query and the SQL itself.

Named queries are both a blessing and a curse in OpenACS. They are a blessing, in that they make it possible to work with multiple databases by placing the query in an external .xql file (XML-formatted files appropriate for particular databases). The problem is that OpenACS first looks for an .xql file associated with the query name, only looking at the SQL in your .tcl page if no XML exists. Many beginning OpenACS programmers are surprised to find that the system is ignoring their SQL modifications in the .tcl page, looking instead at the .xql page.

When posting.tcl ends by invoking ad_return_template, the OpenACS templating system looks for posting.adp. Because our Tcl page contained all of the Tcl program code, we don't need the standard <% %> tags in our ADP page. However, we do need a way to translate our data source into something usable within HTML.

Listing 2. posting.adp

Shown in Listing 2 (posting.adp), the OpenACS templating system adds a few tags that make it possible to retrieve the values set in the .tcl page in a straightforward way:

Regardless of how many rows we display, posting.adp always includes a small HTML form that sends its contents to the posting-add program. This program, posting-add.tcl (shown in Listing 3), starts with an ad_page_contract that declares a single input variable (posting_text). Input variables are mandatory by default, although we can declare them as optional or assign a default value by modifying the entry in ad_page_contract. In this particular case, we ask OpenACS to remove any leading or trailing whitespace from the text input we receive.

Listing 3. posting-add.tcl

We then retrieve the current user ID using the built-in ad_get_user_id procedure, assigning that to the user_id variable. Next, we use db_dml to insert the posting into the database. Notice how we use colons (rather than dollar signs) before the variable names in db_dml; this is standard in the OpenACS database API and ensures that we will not encounter quoting problems when passing data to the database server.

Finally, posting-add.tcl ends by redirecting the user to posting, which invokes posting.tcl and displays posting.adp.

We can now return to APM and generate a package with our templates and database creation scripts. Click on the atf-hello package name, and then click on the manage file information link toward the bottom of the page. Now click on scan for additional files in this package. You should see a list of the .sql, .tcl and .adp pages we installed. Indicate that all of these files should be included in the package, and after returning to the main ATF Hello APM management screen, click on the generate a new atf-hello.info file link.

You're now set to create an APM that can be distributed to any other OpenACS user. Click on the generate a file link, and the distribution file information will indicate the size of the generated APM. If you click on this link, an APM should be downloaded to your system.

How do you install a new APM someone has sent you? The easiest method is to place the APM on the server filesystem. Then from within your web browser, return to the main APM page (/acs-admin/apm/) and click on the install link. Tell the system where the APM is located, and it will be placed under the packages directory. You will then be able to install it using the APM installer that we examined last month. The data model will be inserted into the database, and the web pages will be made available for any interested parties. And of course, once a package is installed in the system, you can use the ACS site map application to mount a new instance of the package under a URL of your choice.

This example package only scratches the surface of OpenACS application development, for example:

OpenACS is complex, and APM is not the simplest system to learn because it tries to handle so many complicated cases that web/database developers often encounter. At the same time, I haven't yet seen an easier way to distribute web/database applications with this degree of modularity, portability across databases and flexibility when it comes to the templates. The ease of creating such applications, combined with a rich data model and a large set of established applications makes OpenACS a viable and useful platform for on-line communities.