OpenACS Full Text Search requires several pieces: the OpenFTS code, some database functions, and the OpenFTS Engine. This section describes how to upgrade OpenFTS from 0.2 to 0.3.2 and upgrade the search engine on an OpenACS site at the same time.

An OpenACS database created in PostGreSQL 7.2 will not work correctly in PostGreSQL 7.3. This is because 7.2 truncates function names to 31 characters, but 7.3 does not. This does not cause problems in 7.2, because truncation occurs both at function creation and at function calling, so they still match. But if you use a database created in 7.2 in 7.3, the function names in the database remain truncated but the function calls are not, and so they don't match. Also some functions use casting commands that no longer work in 7.3 and these functions must be recreated.

To upgrade an OpenACS site from PostGreSQL 7.2 to 7.3, first upgrade the kernel to 4.6.3. Then, dump the database, run the upgrade script /var/lib/aolserver/$OPENACS_SERVICE_NAME/bin/pg_7.2to7.3_upgrade_helper.pl on the dump file, and reply the dump. See Forum OpenACS Q&A: PG 7.2->7.3 upgrade gotcha?. Example:

If you want full text search, and you are running PostgreSQL, install this module to support FTS. Do this step after you have installed both PostgreSQL and AOLserver. You will need the openfts tarball in /var/tmp.

If you are installing Full Text Search, add required packages to the new database. (In order for full text search to work, you must also install the PostgreSQL OpenFTS module and prerequisites.)

[$OPENACS_SERVICE_NAME $OPENACS_SERVICE_NAME]$ /usr/local/pgsql/bin/psql $OPENACS_SERVICE_NAME -f /usr/local/src/postgresql-7.3.4/contrib/tsearch/tsearch.sql
BEGIN
CREATE
(many lines omitted)
INSERT 0 1
COMMIT
[$OPENACS_SERVICE_NAME $OPENACS_SERVICE_NAME]$ /usr/local/pgsql/bin/psql $OPENACS_SERVICE_NAME -f /usr/local/src/postgresql-7.3.4/contrib/pgsql_contrib_openfts/openfts.sql
CREATE
CREATE
[$OPENACS_SERVICE_NAME $OPENACS_SERVICE_NAME]$
/usr/local/pgsql/bin/psql $OPENACS_SERVICE_NAME -f /usr/local/src/postgresql-7.3.4/contrib/tsearch/tsearch.sql
/usr/local/pgsql/bin/psql $OPENACS_SERVICE_NAME -f /usr/local/src/postgresql-7.3.4/contrib/pgsql_contrib_openfts/openfts.sql

pg_config --pkglibdir

If you have installed OpenFTS, you can enable it for this service. Uncomment this line from config.tcl. (To uncomment a line in a tcl file, remove the # at the beginning of the line.)

#ns_param   nsfts           ${bindir}/nsfts.so

Enabling Full Text Search in packages at the moment is not trivial. It involves a couple of steps, which I will illustrate taking lars-blogger as an example package

If you are lucky, Full Text Search is enabled now, if not consult http://openacs.org/forums/message-view?message_id=154759. This link also contains some hints on how to make sure it is enabled.

In general terms, a package is a unit of software that serves a single well-defined purpose. That purpose may be to provide a service directly to one or more classes of end-user, (e.g., discussion forums and file storage for community members, user profiling tools for the site publisher), or it may be to act as a building block for other packages (e.g., an application programming interface (API) for storing and querying access control rules, or an API for scheduling email alerts). Thus, packages fall into one of two categories:

An installation of the OpenACS includes the OpenACS Kernel, some services that extend the kernel's functionality, and some applications intended for end-users. Packages function as individual pieces of subsites. A subsite can contain multiple application and service instances that provide the end-user with capabilities and content customized to the particular subsite.

This architecture supports the growth of collaborative commerce. For example, Jane User starts a forum focusing on the merits of View Cameras by creating an instance of the Forum application for her personal subsite on an OpenACS Installation. Jack User discovers Jane's forum and includes a link to it in his subsite. As interest in Jane's forum grows, she creates a subsite specializing in providing information about View cameras. This subsite now includes several package instances beyond Forum; it could potentially include its own Ecommerce capabilities (ala Yahoo! Shopping). This could include a knowledge management application that allows users to spread expertise about view cameras and a portal application that links to reliable camera models and resellers. Any subsite enabled package that is added to the OpenACS installation through APM is another potential package instance that can become part of Jane's View Camera subsite.

The APM provides an architecture for packaging software, making instances of that software available to subsites, specifying configuration parameters for each instance, and managing the creation and release of new packages.

Prior to ACS 3.3, all packages were lumped together into one monolithic distribution without explicit boundaries; the only way to ascertain what comprised a given package was to look at the top of the corresponding documentation page, where, by convention, the package developer would specify where to find:

Experience has shown us that this lack of explicit boundaries causes a number of maintainability problems for pre-3.3 installations:

Consistent use of the APM format and tools will go a long way toward solving the maintainability problems listed above. Moreover, APM is the substrate that will enable us to establish a central package repository, where developers will be able publish their packages for other OpenACS users to download and install.

For a simple illustration of the difference between ACS without APM (pre-3.3) and ACS with APM (3.3 and beyond), consider a hypothetical ACS installation that uses only two of the thirty-odd modules available circa ACS 3.2 (say, bboard and e-commerce):

APM itself is part of a package, the OpenACS Kernel, an OpenACS service that is the only mandatory component of an OpenACS installation.

The OpenACS is a platform for web-based application software, and any software platform has the potential to develop problems like those described above. Fortunately, there are many precedents for systematic solutions, including:

Borrowing from all of the above, OpenACS 3.3 introduces its own package management system, the OpenACS Package Manager (APM), which consists of:

The design chosen for APM was meant to satisfy the following constraints:

All of these requirements were met, but at the cost of development simplicity. As Packages demonstrates, a set of strict directory conventions are required in order for a package to use APM. This contrasts with the apparent simplicity available to developers of the OpenACS 3.3 system. However, while the system has become more complex for developers to build packages, this complexity is easily managed and is compensated for by additional capabilities.

For example, to make a new application available to the system, a developer must:

While this is complex, especially to a new OpenACS developer, the documentation walks the developer through each of these steps. Moreover, from following these steps, the package can be subsite specific, available to subsites across the system, and be available for distribution to other OpenACS installations without doing a monolithic upgrade or reinstall.

The APM is composed of systems for accomplishing a set of package-related tasks. Each of these tasks comprise a feature area that has an API, data model, and a UI:

Authoring a Package

Full instructions on how to prepare an OpenACS package are available in Packages. The API here can be invoked manually by a package's data model creation script, but need not to be used. This API is part of the APM PL/SQL package.

-- Informs the APM that this application is available for use.
procedure register_application (
    package_key         in apm_package_types.package_key%TYPE,
    pretty_name         in apm_package_types.pretty_name%TYPE,
    pretty_plural       in apm_package_types.pretty_plural%TYPE,
    package_uri         in apm_package_types.package_uri%TYPE,
    singleton_p         in apm_package_types.singleton_p%TYPE
                                default 'f',
    spec_file_path      in apm_package_types.spec_file_path%TYPE
                                default null,
    spec_file_mtime     in apm_package_types.spec_file_mtime%TYPE
                                default null
);

The procedure above registers an OpenACS application in the APM. It creates a new OpenACS object and stores information about the package, such as its name, in the APM data model. There is an analogous procedure for OpenACS services, called apm.register_service.

To remove an application from the system, there are the calls apm.unregister_application and apm.unregister_service.

-- Remove the application from the system.
procedure unregister_application (
    package_key     in apm_package_types.package_key%TYPE,
    -- Delete all objects associated with this application.
    cascade_p       in char default 'f'
);

Use the cascade_p only if you want to completely remove the package from the OpenACS.

In order to determine if a particular package exists in the system, use the register_p predicate. It returns 1 if the specified package_key exists in the system, 0 otherwise.

function register_p (
    package_key     in apm_package_types.package_key%TYPE
) return integer;

Maintaining Multiple Versions of a Package

While the package authoring API provides a means for registering a package, some information about a package is version dependent. For example, between versions, the owner of a package, its vendor, its URI, and its dependency information may change. The API for package versions allows this information to be specified. All of these APIs are part of the apm_package_version PL/SQL package.

To create a new package version, use the apm_package_version.new constructor function.

function new (
    version_id          in apm_package_versions.version_id%TYPE
                default null,
    package_key         in apm_package_versions.package_key%TYPE,
    version_name        in apm_package_versions.version_name%TYPE
                                default null,
    version_uri         in apm_package_versions.version_uri%TYPE,
    summary         in apm_package_versions.summary%TYPE,
    description_format      in apm_package_versions.description_format%TYPE,
    description         in apm_package_versions.description%TYPE,
    release_date        in apm_package_versions.release_date%TYPE,
    vendor          in apm_package_versions.vendor%TYPE,
    vendor_uri          in apm_package_versions.vendor_uri%TYPE,
    installed_p         in apm_package_versions.installed_p%TYPE
                                default 'f',
    data_model_loaded_p     in apm_package_versions.data_model_loaded_p%TYPE
                        default 'f'
) return apm_package_versions.version_id%TYPE;

In order to use this function, an existing package_key must be specified. The version_name parameter must follow a strict convention:

In addition, the letters d, a, and b may be followed by another integer, indicating a version within the release.

For those who like regular expressions:

version_number := ^[0-9]+((\.[0-9]+)+((d|a|b|)[0-9]?)?)$

So the following is a valid progression for version numbers:

To delete a given version of a package, use the apm_package_version.delete procedure:

procedure delete (
    package_id      in apm_packages.package_id%TYPE
);

After creating a version, it is possible to edit the information associated with it using apm_package_version.edit.

function edit (
      new_version_id        in apm_package_versions.version_id%TYPE
                default null,
      version_id        in apm_package_versions.version_id%TYPE,
      version_name      in apm_package_versions.version_name%TYPE
                default null,
      version_uri       in apm_package_versions.version_uri%TYPE,
      summary           in apm_package_versions.summary%TYPE,
      description_format    in apm_package_versions.description_format%TYPE,
      description       in apm_package_versions.description%TYPE,
      release_date      in apm_package_versions.release_date%TYPE,
      vendor            in apm_package_versions.vendor%TYPE,
      vendor_uri        in apm_package_versions.vendor_uri%TYPE,
      installed_p       in apm_package_versions.installed_p%TYPE
                default 'f',
      data_model_loaded_p   in apm_package_versions.data_model_loaded_p%TYPE
                default 'f'
) return apm_package_versions.version_id%TYPE;

Versions can be enabled or disabled. Enabling a version instructs APM to source the package's libraries on startup and to make the package available to the OpenACS.

procedure enable (
    version_id          in apm_package_versions.version_id%TYPE
);

procedure disable (
    version_id          in apm_package_versions.version_id%TYPE
);

Files associated with a version can be added and removed. The path is relative to the package-root which is acs-server-root/packages/package-key.

-- Add a file to the indicated version.
function add_file(
    file_id             in apm_package_files.file_id%TYPE
                        default null,
    version_id in       apm_package_versions.version_id%TYPE,
    path                in apm_package_files.path%TYPE,
    file_type           in apm_package_file_types.file_type_key%TYPE
) return apm_package_files.file_id%TYPE;

-- Remove a file from the indicated version.
procedure remove_file(
    version_id          in apm_package_versions.version_id%TYPE,
    path                in apm_package_files.path%TYPE
);

Package versions need to indicate that they provide interfaces for other software. An interface is an API that other packages can access and utilize. Interfaces are identified as a URI and a version name, that comply with the specification of a version name for package URIs.

-- Add an interface provided by this version.
function add_interface(
    interface_id        in apm_package_dependencies.dependency_id%TYPE
                    default null,
    version_id          in apm_package_versions.version_id%TYPE,
    interface_uri       in apm_package_dependencies.service_uri%TYPE,
    interface_version       in apm_package_dependencies.service_version%TYPE
) return apm_package_dependencies.dependency_id%TYPE;

procedure remove_interface(
    interface_id        in apm_package_dependencies.dependency_id%TYPE,
    version_id          in apm_package_versions.version_id%TYPE
);

procedure remove_interface(
    interface_uri       in apm_package_dependencies.service_uri%TYPE,
    interface_version       in apm_package_dependencies.service_version%TYPE,
    version_id          in apm_package_versions.version_id%TYPE
);

The primary use of interfaces is for other packages to specify required interfaces, known as dependencies. A package cannot be correctly installed unless all of its dependencies have been satisfied.

-- Add a requirement for this version.  A requirement is some interface that this
-- version depends on.
function add_dependency(
    requirement_id      in apm_package_dependencies.dependency_id%TYPE
                    default null,
    version_id          in apm_package_versions.version_id%TYPE,
    requirement_uri     in apm_package_dependencies.service_uri%TYPE,
    requirement_version     in apm_package_dependencies.service_version%TYPE
) return apm_package_dependencies.dependency_id%TYPE;

procedure remove_dependency(
    requirement_id      in apm_package_dependencies.dependency_id%TYPE,
    version_id          in apm_package_versions.version_id%TYPE
);

procedure remove_dependency(
    requirement_uri     in apm_package_dependencies.service_uri%TYPE,
    requirement_version     in apm_package_dependencies.service_version%TYPE,
    version_id          in apm_package_versions.version_id%TYPE
);

As new versions of packages are created, it is necessary to compare their version names. These two functions assist in that task.

-- Given a version_name (e.g. 3.2a), return
-- something that can be lexicographically sorted.
function sortable_version_name (
    version_name        in apm_package_versions.version_name%TYPE
) return varchar;

-- Given two version names, return 1 if one > two, -1 if two > one, 0 otherwise.
-- Deprecate?
function compare(
    version_name_one        in apm_package_versions.version_name%TYPE,
    version_name_two        in apm_package_versions.version_name%TYPE
) return integer;

Creating Instances of a Package

Once a package is registered in the system, it is possible to create instances of it. Each instance can maintain its own content and parameters.

create or replace package apm_application
as

function new (
    application_id  in acs_objects.object_id%TYPE default null,
    instance_name   in apm_packages.instance_name%TYPE
            default null,
    package_key     in apm_package_types.package_key%TYPE,
    object_type     in acs_objects.object_type%TYPE
               default 'apm_application',
    creation_date   in acs_objects.creation_date%TYPE default sysdate,
    creation_user   in acs_objects.creation_user%TYPE default null,
    creation_ip     in acs_objects.creation_ip%TYPE default null,
    context_id      in acs_objects.context_id%TYPE default null
) return acs_objects.object_id%TYPE;

procedure delete (
    application_id      in acs_objects.object_id%TYPE
);
end apm_application;

Just creating a package instance is not sufficient for it to be served from the web server. A corresponding site node must be created for it. As an example, here is how the OpenACS API Documentation service makes itself available on the OpenACS main site:

declare
    api_doc_id integer;
begin
    api_doc_id := apm_service.new (
      instance_name => 'OpenACS API Browser',
      package_key => 'acs-api-browser',
      context_id => main_site_id
    );

    apm_package.enable(api_doc_id);

    api_doc_id := site_node.new (
      parent_id => site_node.node_id('/'),
      name => 'api-doc',
      directory_p => 't',
      pattern_p => 't',
      object_id => api_doc_id
    );

    commit;
end;
/
show errors

Specifying Configuration Parameters for each Instance

A parameter is a setting that can be changed on a package instance basis. Parameters are registered on each package_key, and the values are associated with each instance. Parameters can have default values and can be of type 'string' or 'number.' There is support with this API for setting a number of minimum and maximum values for each parameter, but for most instances, the minimum and maximum should be 1. It is useful to allow or require multiple values for packages that need to store multiple pieces of information under one parameter. Default values are automatically set when instances are created, but can be changed for each instance.

All of the functions below are in the APM PL/SQL package.

-- Indicate to APM that a parameter is available to the system.
function register_parameter (
    parameter_id        in apm_parameters.parameter_id%TYPE
                default null,
    parameter_name      in apm_parameters.parameter_name%TYPE,
    description         in apm_parameters.description%TYPE
                default null,
    package_key         in apm_parameters.package_key%TYPE,
    datatype            in apm_parameters.datatype%TYPE
                default 'string',
    default_value       in apm_parameters.default_value%TYPE
                default null,
    section_name        in apm_parameters.section_name%TYPE
                default null,
    min_n_values        in apm_parameters.min_n_values%TYPE
                default 1,
    max_n_values        in apm_parameters.max_n_values%TYPE
                default 1
) return apm_parameters.parameter_id%TYPE;

function update_parameter (
    parameter_id        in apm_parameters.parameter_id%TYPE,
    parameter_name      in apm_parameters.parameter_name%TYPE,
    description         in apm_parameters.description%TYPE
                default null,
    package_key         in apm_parameters.package_key%TYPE,
    datatype            in apm_parameters.datatype%TYPE
                default 'string',
    default_value       in apm_parameters.default_value%TYPE
                default null,
    section_name        in apm_parameters.section_name%TYPE
                default null,
    min_n_values        in apm_parameters.min_n_values%TYPE
                default 1,
    max_n_values        in apm_parameters.max_n_values%TYPE
                default 1
) return apm_parameters.parameter_name%TYPE;

-- Remove any uses of this parameter.
procedure unregister_parameter (
    parameter_id        in apm_parameters.parameter_id%TYPE
                default null
);

The following functions are used to associate values with parameters and instances:

-- Return the value of this parameter for a specific package and parameter.
function get_value (
    parameter_id        in apm_parameter_values.parameter_id%TYPE,
    package_id          in apm_packages.package_id%TYPE
) return apm_parameter_values.attr_value%TYPE;

function get_value (
    package_id          in apm_packages.package_id%TYPE,
    parameter_name      in apm_parameters.parameter_name%TYPE
) return apm_parameter_values.attr_value%TYPE;

-- Sets a value for a parameter for a package instance.
procedure set_value (
    parameter_id        in apm_parameter_values.parameter_id%TYPE,
    package_id          in apm_packages.package_id%TYPE,
    attr_value          in apm_parameter_values.attr_value%TYPE
);

procedure set_value (
    package_id          in apm_packages.package_id%TYPE,
    parameter_name      in apm_parameters.parameter_name%TYPE,
    attr_value          in apm_parameter_values.attr_value%TYPE
);

The central piece of the data model is the apm_package_types table where each package is registered. When a new application or service is installed on an OpenACS instance, a corresponding row in this table is inserted with information about the type of package, e.g. if the forum package is installed on your OpenACS server, a row in apm_package_types will be created, noting that it's an application package type.

The apm_packages table is used to contain information about the instances of packages currently created in the system. The package_key column references the apm_package_types table to ensure that no package instance can be created for a type that does not exist.

The apm_package_versions table contains information specific to a particular version of a package. Several tables reference this one to provide further information about the particular version:

Parameter information is maintained through two tables:

A number of views are available for obtaining information about packages registered in the APM.

The APM's user interface is part of the OpenACS Administration Service. The UI is the primary point of contact with APM by developers and administrators. It is part of OpenACS Administration, because only the site-wide administrator should be able to access it. Thus in order to develop a package, the developer must be granted site-wide administration.

APM has two parameters for configuring how it interacts with the UNIX filesystem, accessible via the Site Map admin page. These parameters need not be changed under most circumstances, but may need to be tweaked for Windows compatibility.

APM has been in production since OpenACS 3.3, and as of version 4.0 offers a stable set of features. One major feature planned is integration with the OpenACS Package Repository for automatic dependency satisfaction. When a user tries to install a package that depends on other packages, the APM will contact the package repository, determine what packages depend on it, and offer the user a chance to download and install them all. This improvement offers value to end users by facilitating the extension of their OpenACS systems.

Architecturally, minor improvements to the data model and the specification file are planned to increase modularity. The current implementation puts all package specification information in a single file. This approach has certain advantages, such as centralization, but splitting this information into several files allows for flexible extensions to the APM architecture over time.

APM packages currently lack provisions to verify security information. There are plans to add MD5 time stamps and PGP signatures to packages to enable secure authentication of packages. These steps are necessary for APM to be usable as a scalable method to distribute packages on multiple repositories worldwide.

Another anticipated change is to split the APM UI into separate systems for authoring, maintaining, and installing packages. The current UI presents all of this functionality in one interface and it can be confusing from a usability perspective.

Developing data models in OpenACS 5.2.3rc1 is much like developing data models for OpenACS 3, save for the implementation. As usual, you need to examine how to model the information that the application must store and manipulate, and define a suitable set of SQL tables. In our Notes application, we have to be able to keep track of who entered a particular note, when they did it, and the actual text of the notes that users have entered. A simple data model might look like this:

create table notes (
    note_id           integer primary key,
    owner_id          integer references users(user_id),
    creation_user     references(user_id) not null,
    creation_date     date not null,
    last_modified     date not null,
    title             varchar(255) not null,
    body              varchar(1024)
)

We've omitted constraint names for the purpose of clarity.

Thinking further ahead, we can imagine doing any of the following things with Notes as well:

In OpenACS, the key to enabling these types of services on your application data is to take advantage of the Object System. The first question, then, is "Just what are objects, and what do you use them for anyway?". The short answer: objects are anything represented in the application's data model that will need to be managed by any central service in OpenACS, or that may be reusable in the context of future applications. Every object in the system is represented using a row in the acs_objects table. This table defines all the standard attributes that are stored on every object, including its system-wide unique ID, object type, and some generic auditing columns.

To make use of the object system, you as the application developer have to write your data model in a way that is slightly more complex than in the ACS 3.x days. What you get for this extra work includes:

Using ACS objects is straightforward: all that's required are a few extra steps in the design of your application data model.

In order to hook our Notes application into the object system, we make some calls to use our notes table as the basis for a new object type. Object types are analogous to classes in programming languages such as C++ and Java. In Java, a class defines a set of attributes that store data and a set of methods that run code. In OpenACS, we use one or more database tables to store the data attributes, and we define a stored procedure package to hold procedures to define the programming interface to the data model.

The object type itself is described using data in the acs_object_types and acs_attributes tables, which play a role similar to the data dictionary in Oracle. As in Java, object types can inherit attributes from a parent type, so the type system forms a hierarchy. Unlike Java, Oracle does not support this inheritance transparently, so we have to make sure we add our own bookkeeping code to keep everything consistent. Below you'll find the code needed to describe a new object type called notes in your system.

Fire up your text editor and open the ROOT/packages/notes/sql/oracle/notes-create.sql (ROOT/packages/notes/sql/postgresql/notes-create.sql for the PG version) file created when we created the package. Then, do the following:

begin
  acs_object_type.create_type (
    supertype     => 'acs_object',
    object_type   => 'note',
    pretty_name   => 'Note',
    pretty_plural => 'Notes',
    table_name    => 'NOTES',
    id_column     => 'NOTE_ID'
  );
end;
/
show errors;

declare
 attr_id acs_attributes.attribute_id%TYPE;
begin
  attr_id := acs_attribute.create_attribute (
    object_type    => 'note',
    attribute_name => 'TITLE',
    pretty_name    => 'Title',
    pretty_plural  => 'Titles',
    datatype       => 'string'
  );

  attr_id := acs_attribute.create_attribute (
    object_type    => 'note',
    attribute_name => 'BODY',
    pretty_name    => 'Body',
    pretty_plural  => 'Bodies',
    datatype       => 'string'
  );
end;
/
show errors;

create table notes (
    note_id    integer references acs_objects(object_id) primary key,
    owner_id   integer references users(user_id),
    title      varchar(255) not null,
    body       varchar(1024)
)

create or replace package note
as
  function new (
    note_id             in notes.note_id%TYPE default null,
    owner_id            in notes.owner_id%TYPE default null,
    title               in notes.title%TYPE,
    body                in notes.body%TYPE,
    object_type         in acs_object_types.object_type%TYPE default 'note',
    creation_date       in acs_objects.creation_date%TYPE
                           default sysdate,
    creation_user       in acs_objects.creation_user%TYPE
                           default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null,
    context_id          in acs_objects.context_id%TYPE default null
  ) return notes.note_id%TYPE;

  procedure delete (
    note_id      in notes.note_id%TYPE
  );
end note;
/
show errors

create or replace package body note
as

  function new (
    note_id             in notes.note_id%TYPE default null,
    owner_id            in notes.owner_id%TYPE default null,
    title               in notes.title%TYPE,
    body                in notes.body%TYPE,
    object_type         in acs_object_types.object_type%TYPE default 'note',
    creation_date       in acs_objects.creation_date%TYPE
                           default sysdate,
    creation_user       in acs_objects.creation_user%TYPE
                           default null,
    creation_ip         in acs_objects.creation_ip%TYPE default null,
    context_id          in acs_objects.context_id%TYPE default null
  ) return notes.note_id%TYPE
  is
    v_note_id integer;
  begin
    v_note_id := acs_object.new (
      object_id     => note_id,
      object_type   => object_type,
      creation_date => creation_date,
      creation_user => creation_user,
      creation_ip   => creation_ip,
      context_id    => context_id
    );

    insert into notes
     (note_id, owner_id, title, body)
    values
     (v_note_id, owner_id, title, body);

     return v_note_id;
  end new;

  procedure delete (
    note_id      in notes.note_id%TYPE
  )
  is
  begin
    delete from notes
    where note_id = note.delete.note_id;

    acs_object.del(note_id);
  end delete;

end note;
/
show errors;

begin
  acs_object_type.drop_type ('note');
end;
/
show errors

drop package note;
drop table notes;

While it is hard to give general design advice without knowing anything about a particular application, you should follow the following rule of thumb when deciding when to hook part of your data model to the object system:

Anything in your data model that needs to be available to general OpenACS services such as user comments, permissions, and so on should be a subtype of acs_object. In addition, if you want your data model to take advantage of attributes that exist in some object type that is a subtype of acs_object, then you should use the object system.

For example, for most applications, you will want to use objects to represent the data in your application that is user visible and thus requires access control. But other internal tables, views, mapping tables and so on probably don't need to be objects. As before, this kind of design decision is mostly made on an application-by-application basis, but this is a good baseline from which to start.

In this section we cover some overall guidelines for designing data models that are meant to be integrated with the OpenACS object system.

There are two basic rules you should follow when designing OpenACS 5.2.3rc1 data models:

The reason behind these two rules is pretty straightforward: First, the OpenACS Object system itself is meant to be a generic and reusable tool for any application to use for basic services. Second, in order for this to work, the various parts of the OpenACS Objects data model must be interpreted in the same way by all applications that use the data model. Therefore, assigning any application-specific semantics to any part of the core data model is a bad thing to do, because then the semantics of the data model are no longer independent of the application. This would make it impossible to build the generic tools that the data model is trying to support.

Another less important reason for these two rules is to not introduce any joins against the acs_objects table in SQL queries in your application that you do not absolutely need.

In the Notes example, the result of applying these rules is that we are careful to define our own attribute for owner_id rather than overloading creation_user from the objects table. But, since we will probably use creation_date and so on for their intended purposes, we don't bother to define our own attributes to store that data again. This will entail joins with acs_objects but that's OK because it makes the overall data model cleaner. The real lesson is that deciding exactly how and when to use inherited attributes is fairly straightforward, but requires a good amount of thought at design time even for simple applications.

Hooking into the OpenACS 5.2.3rc1 object system brings the application developer numerous benefits, and doing it involves only four easy steps:

In versions of the OpenACS prior to 3.4, the standard place to document Tcl files (both Tcl pages and Tcl library files) was in a comment at the top of the file:

#
# path from server home/filename
#
# Brief description of the file's purpose
#
# author's email address, file creation date
#
# $Id: tcl-doc.html,v 1.43 2006/07/17 05:38:32 torbenb Exp $
#

In addition, the inputs expected by a Tcl page (i.e., form variables) would be enumerated in a call to ad_page_variables, in effect, documenting the page's argument list.

The problem with these practices is that the documentation is only accessible by reading the source file itself. For this reason, ACS 3.4 introduces a new API for documenting Tcl files and, on top of that, a web-based user interface for browsing the documentation:

This has the following benefits:

Currently ad_page_contract serves mostly as a replacement for ad_page_variables. Eventually, it will be integrated closely with the documents API so that each script's contract will document precisely the set of properties available to graphical designers in templates. (Document API integration is subject to change, so we don't decsribe it here yet; for now, you can just consider ad_page_contract a newer, better, documented ad_page_variables.)

Let's look at an example usage of ad_page_contract:

# /packages/acs-kernel/api-doc/www/package-view.tcl
ad_page_contract {
    version_id:integer
    public_p:optional
    kind
    { format "html" }
} {
    Shows APIs for a particular package.

    @param version_id the ID of the version whose API to view.
    @param public_p view only public APIs?
    @param kind view the type of API to view. One of <code>procs_files</code>,
        <code>procs</code>, <code>content</code>, <code>types</code>, or
        <code>gd</code>.
    @param format the format for the documentation. One of <code>html</code> or <code>xml</code>.

    @author Jon Salz (jsalz@mit.edu)
    @creation-date 3 Jul 2000
    @cvs-id $Id: tcl-doc.html,v 1.43 2006/07/17 05:38:32 torbenb Exp $
}

Note that:

ad_library provides a replacement for the informal documentation (described above) found at the beginning of every Tcl page. Instead of:

# /packages/acs-kernel/00-proc-procs.tcl
#
# Routines for defining procedures and libraries of procedures (-procs.tcl files).
#
# jsalz@mit.edu, 7 Jun 2000
#
# $Id: tcl-doc.html,v 1.43 2006/07/17 05:38:32 torbenb Exp $

you'll now write:

# /packages/acs-kernel/00-proc-procs.tcl
ad_library {

    Routines for defining procedures and libraries of procedures (<code>-procs.tcl</code>
    files).

    @creation-date 7 Jun 2000
    @author Jon Salz (jsalz@mit.edu)
    @cvs-id $Id: tcl-doc.html,v 1.43 2006/07/17 05:38:32 torbenb Exp $

}

Note that format is derived heavily from Javadoc: a general description of the script's functionality, followed optionally by a series of named attributes tagged by at symbols (@). HTML formatting is allowed. You are encouraged to provide:

One of OpenACS's great strengths is that code written for it is very close to the database. It is very easy to interact with the database from anywhere within OpenACS. Our goal is to develop a coherent API for database access which makes this even easier.

There were four significant problems with the way OpenACS previously used the database (i.e., directly through the ns_db interface):

The Database Access API addresses the first three problems by:

It lays the groundwork for addressing the fourth problem by assigning each SQL statement a logical name. In a future version of the OpenACS Core, this API will translate logical statement names into actual SQL, based on the type of database in use. (To smooth the learning curve, we provide a facility for writing SQL inline for a "default SQL dialect", which we assume to be Oracle for now.)

To be clear, SQL abstraction is not fully implemented in OpenACS 3.3.1. The statement names supplied to each call are not used by the API at all. The API's design for SQL abstraction is in fact incomplete; unresolved issues include:

So why is the incremental change of adding statement naming to the API worth the effort? It is worth the effort because we know that giving each SQL statement a logical name will be required by the complete SQL abstraction design. Therefore, we know that the effort will not be wasted, and taking advantage of the new support for bind variables will already require code that uses 3.3.0 version of the API to be updated.

set_variables_after_query is gone! (Well, it's still there, but you'll never need to use it.) The new API routines set local variables automatically. For instance:

db_1row select_names "select first_names, last_name from users where user_id = [ad_get_user_id]"
doc_body_append "Hello, $first_names $last_name!"

Like ns_db 1row, this will bomb if the query doesn't return any rows (no such user exists). If this isn't what you want, you can write:

if { [db_0or1row select_names "select first_names, last_name from users where user_id = [ad_get_user_id]"] } {
    doc_body_append "Hello, $first_names $last_name!"
} else {
    # Executed if the query returns no rows.
    doc_body_append "There's no such user!"
}

Selecting a bunch of rows is a lot prettier now:

db_foreach select_names "select first_names, last_name from users" {
     doc_body_append "Say hi to $first_names $last_name for me!<br>"
}

That's right, db_foreach is now like ns_db select plus a while loop plus set_variables_after_query plus an if statement (containing code to be executed if no rows are returned).

db_foreach select_names "select first_names, last_name from users where last_name like 'S%'" {
     doc_body_append "Say hi to $first_names $last_name for me!<br>"
} if_no_rows {
     doc_body_append "There aren't any users with last names beginnings with S!"
}

The new API keeps track of which handles are in use, and automatically allocates new handles when they are necessary (e.g., to perform subqueries while a select is active). For example:

doc_body_append "<ul>"
db_foreach select_names "select first_names, last_name, user_id from users" {
    # Automatically allocated a database handle from the main pool.
    doc_body_append "<li>User $first_names $last_name\n<ul>"

    db_foreach select_groups "select group_id from user_group_map where user_id = $user_id" {
        # There's a selection in progress, so we allocated a database handle
        # from the subquery pool for this selection.
        doc_body_append "<li>Member of group #$group_id.\n"
    } if_no_rows {
        # Not a member of any groups.
        doc_body_append "<li>Not a member of any group.\n"
    }
}
doc_body_append "</ul>"
db_release_unused_handles

A new handle isn't actually allocated and released for every selection, of course - as a performance optimization, the API keeps old handles around until db_release_unused_handles is invoked (or the script terminates).

Note that there is no analogue to ns_db gethandle - the handle is always automatically allocated the first time it's needed.

Introduction

Most SQL statements require that the code invoking the statement pass along data associated with that statement, usually obtained from the user. For instance, in order to delete a WimpyPoint presentation, a Tcl script might use the SQL statement

delete from wp_presentations where presentation_id = some_presentation_id

where some_presentation_id is a number which is a valid presentation ID of the presentation I want to delete. It's easy to write code handling situations like this since SQL statements can include bind variables, which represent placeholders for actual data. A bind variable is specified as a colon followed by an identifier, so the statement above can be coded as:

db_dml presentation_delete {
    delete from wp_presentations where presentation_id = :some_presentation_id
}

When this SQL statement is invoked, the value for the bind variable :some_presentation_id is pulled from the Tcl variable $some_presentation_id (in the caller's environment). Note that bind variables are not limited to one per statement; you can use an arbitrary number, and each will pull from the correspondingly named Tcl variable. (Alternatively, you can also specify an list or ns_set providing bind variables' values; see Usage.)

The value of a bind variable is taken literally by the database driver, so there is never any need to put single-quotes around the value for a bind variable, or to use db_quote to escape single-quotes contained in the value. The following works fine, despite the apostrophe:

set exclamation "That's all, folks!"
db_dml exclamation_insert { insert into exclamations(exclamation) values(:exclamation) }

Note that you can use a bind variable in a SQL statement only where you could use a literal (a number or single-quoted string). Bind variables cannot be placeholders for things like SQL keywords, table names, or column names, so the following will not work, even if $table_name is set properly:

select * from :table_name

Why Bind Variables Are Useful

Why bother with bind variables at all - why not just write the Tcl statement above like this:

db_dml presentation_delete "
    delete from wp_presentations where presentation_id = $some_presentation_id
"

(Note the use of double-quotes to allow the variable reference to $some_presentation_id to be interpolated in.) This will work, but consider the case where some devious user causes some_presentation_id to be set to something like '3 or 1 = 1', which would result in the following statement being executed:

delete from wp_presentations where presentation_id = 3 or 1 = 1

This deletes every presentation in the database! Using bind variables eliminates this gaping security hole: since bind variable values are taken literally. Oracle will attempt to delete presentations whose presentation ID is literally '3 or 1 = 1' (i.e., no presentations, since '3 or 1 = 1' can't possibly be a valid integer primary key for wp_presentations. In general, since Oracle always considers the values of bind variables to be literals, it becomes more difficult for users to perform URL surgery to trick scripts into running dangerous queries and DML.

Usage

Every db_* command accepting a SQL command as an argument supports bind variables. You can either

The default behavior (i.e., if the -bind switch is omitted) is that these procedures expect to find local variables that correspond in name to the referenced bind variables, e.g.:

set user_id 123456
set role "administrator"

db_foreach user_group_memberships_by_role {
    select g.group_id, g.group_name
    from user_groups g, user_group_map map
    where g.group_id = map.user_id
    and map.user_id = :user_id
    and map.role = :role
} {
    # do something for each group of which user 123456 is in the role
    # of "administrator"
}

The value of the local Tcl variable user_id (123456) is bound to the user_id bind variable.

The -bind switch can takes the name of an ns_set containing keys for each bind variable named in the query, e.g.:

set bind_vars [ns_set create]
ns_set put $bind_vars user_id 123456
ns_set put $bind_vars role "administrator"

db_foreach user_group_memberships_by_role {
    select g.group_id, g.group_name
    from user_groups g, user_group_map map
    where g.group_id = map.user_id
    and map.user_id = :user_id
    and map.role = :role
} -bind $bind_vars {
    # do something for each group in which user 123456 has the role
    # of "administrator"
}

Alternatively, as an argument to -bind you can specify a list of alternating name/value pairs for bind variables:

db_foreach user_group_memberships_by_role {
    select g.group_id, g.group_name
    from user_groups g, user_group_map map
    where g.group_id = map.user_id
    and map.user_id = :user_id
    and map.role = :role
} -bind [list user_id 123456 role "administrator"] {
    # do something for each group in which user 123456 has the role
    # of "administrator"
}

Nulls and Bind Variables

When processing a DML statement, Oracle coerces empty strings into null. (This coercion does not occur in the WHERE clause of a query, i.e. col = '' and col is null are not equivalent.)

As a result, when using bind variables, the only way to make Oracle set a column value to null is to set the corresponding bind variable to the empty string, since a bind variable whose value is the string "null" will be interpreted as the literal string "null".

These Oracle quirks complicate the process of writing clear and abstract DML difficult. Here is an example that illustrates why:

#
# Given the table:
#
#   create table foo (
#           bar        integer,
#           baz        varchar(10)
#   );
#

set bar ""
set baz ""

db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# the values of the "bar" and "baz" columns in the new row are both
# null, because Oracle has coerced the empty string (even for the
# numeric column "bar") into null in both cases

Since databases other than Oracle do not coerce empty strings into null, this code has different semantics depending on the underlying database (i.e., the row that gets inserted may not have null as its column values), which defeats the purpose of SQL abstraction.

Therefore, the Database Access API provides a database-independent way to represent null (instead of the Oracle-specific idiom of the empty string): db_null.

Use it instead of the empty string whenever you want to set a column value explicitly to null, e.g.:

set bar [db_null]
set baz [db_null]

db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# sets the values for both the "bar" and "baz" columns to null

We now require that each SQL statement be assigned a logical name for the statement that is unique to the procedure or page in which it is defined. This is so that (eventually) we can implement logically named statements with alternative SQL for non-Oracle databases (e.g., Postgres). More on this later.

Normally, db_foreach, db_0or1row, and db_1row places the results of queries in Tcl variables, so you can say:

db_foreach users_select "select first_names, last_name from users" {
    doc_body_append "<li>$first_names $last_name\n"
}

However, sometimes this is not sufficient: you may need to examine the rows returned, to dynamically determine the set of columns returned by the query, or to avoid collisions with existing variables. You can use the -column_array and -column_set switches to db_foreach, db_0or1row, and db_1row to instruct the database routines to place the results in a Tcl array or ns_set, respectively, where the keys are the column names and the values are the column values. For example:

db_foreach users_select "select first_names, last_name from users" -column_set columns {
    # Now $columns is an ns_set.
    doc_body_append "<li>"
    for { set i 0 } { $i < [ns_set size $columns] } { incr i } {
        doc_body_append "[ns_set key $columns $i] is [ns_set value $columns $i]. \n"
    }
}

will write something like:

Note that you never have to use ns_db anymore (including ns_db gethandle)! Just start doing stuff, and (if you want) call db_release_unused_handles when you're done as a hint to release the database handle.