Who (grantee_id) can do what (privilege) on which object (object_id).

The general permissions system has a flexible (and relatively complex) data model in OpenACS. Developers who have not had the time to learn the internals of the data model may end up writing seemingly correct code that crashes their system in weird ways. This writeup is the result of my running into such a piece of code and trying to understand exactly what went wrong. It is geared towards developers who understand the general permissions system to the extent that is described in the Groups, Context, Permissions documentation, but who have not had the opportunity to take a long, careful look at the system internals.

In OpenACS, most of the interesting tables are expected to extend (subtype) the acs_objects table, i.e. they are expected to have an integer primary key column that references the object_id column of acs_objects.

create table acs_objects (
      object_id             integer
          not null
          constraint acs_objects_pk primary key,
      object_type
          not null
          constraint acs_objects_object_type_fk references acs_object_types (object_type),
      context_id
          constraint acs_objects_context_id_fk references acs_objects(object_id),
      security_inherit_p	  char(1) default 't'
          not null,
      constraint acs_objects_sec_inherit_p_ck
          check (security_inherit_p in ('t', 'f')),
      creation_user         integer,
      creation_date         date default sysdate not null,
      creation_ip           varchar2(50),
      last_modified         date default sysdate not null,
      modifying_user        integer,
      modifying_ip          varchar2(50),
      constraint acs_objects_context_object_un
          unique (context_id, object_id) disable
);
    

This means that items that want to use the features of the OpenACS object system needs to have an entry in the acs_objects. This allows developers to define relationships between any two entities A and B by defining a relationship between their corresponding entries in the acs_objects table. One of the applications of this powerful capability is the general permissions system.

At the heart of the permission system are two tables: acs_privileges and acs_permissions.

  create table acs_privileges (
      privilege           varchar2(100) not null
          constraint acs_privileges_pk primary key,
      pretty_name         varchar2(100),
      pretty_plural       varchar2(100)
  );
    

  create table acs_permissions (
      object_id
          not null
          constraint acs_permissions_on_what_id_fk references acs_objects (object_id),
      grantee_id
          not null
          constraint acs_permissions_grantee_id_fk references parties (party_id),
      privilege
          not null
          constraint acs_permissions_priv_fk references acs_privileges (privilege),
      constraint acs_permissions_pk
          primary key (object_id, grantee_id, privilege)
  );
    

The acs_privileges table stores named privileges like read, write, delete, create, and admin. The acs_permissions table stores assertions of the form:

Who (grantee_id) can do what (privilege) on which object (object_id).

The micromanaging approach to system security would be to require application developers to store permission information explicitly about every object, i.e. if the system has 100,000 and 1,000 users who have the read privilege on all objects, then we would need to store 100,000,000 entries of the form:

Although quite feasible, this approach fails to take advantage of the fact that objects in the system are commonly organized hierarchally, and permissions usually follow the hierarchical structure, so that if user X has the read privilege on object A, she typically also has the read privilege on all objects attached under A.

The general permission system takes advantage of the hierarchical organization of objects to unburden developers of the necessity to explicitly maintain security information for every single object. There are three kinds of hierarchies involved. These are discussed in the following sections.

Suppose objects A, B, ..., and F form the following hierarchy.

This can be represented in the acs_objects table by the following entries:

The first entry tells us that object 20 is the descendant of object 10, and the third entry shows that object 40 is the descendant of object 20. By running a CONNECT BY query, we can compute that object 40 is the second-generation descendant of object 10. With this in mind, if we want to record the fact that user Joe has the read privilege on objects A, ..., F, we only need to record one entry in the acs_permissions table.

The fact that Joe can also read B, C, ..., and F can be derived by ascertaining that these objects are children of A by traversing the context hierarchy. As it turns out, hierarchical queries are expensive. As Rafael Schloming put it so aptly, Oracle can't deal with hierarchies for shit.

One way to solve this problem is to cache a flattened view of the context tree like so:

Note that the number of entries in the flattened view grows exponentially with respect to the depth of the context tree. For instance, if you have a fully populated binary tree with a depth of n, then the number of entries in its flattened view is

1 + 2*2 + 3*4 + 4*8 + 5*16 + ... + (n+1)*2ⁿ = n*2ⁿ⁺¹ + 1

Despite its potentially great storage costs, maintaining a flattened representation of the context tree is exactly what OpenACS does. The flattened context tree is stored in the acs_object_context_index table.

  create table acs_object_context_index (
      object_id
          not null
          constraint acs_obj_context_idx_obj_id_fk references acs_objects (object_id),
      ancestor_id
          not null
          constraint acs_obj_context_idx_anc_id_fk references acs_objects (object_id),
      n_generations	    integer
          not null
          constraint acs_obj_context_idx_n_gen_ck check (n_generations >= 0),
      constraint acs_object_context_index_pk
          primary key (object_id, ancestor_id)
  ) organization index;
    

A few things to note about this table are these. Number one, it is an index-organized table, which means it is substantially optimized for access by primary key. Number two, as the above computations suggest, the size of the table grows polynomially with respect to the average number of descendants that an object has, and exponentially with respect to the depth of the context tree.

The acs_object_context_index is kept in sync with the acs_objects table by triggers like this:

create or replace trigger acs_objects_context_id_in_tr
after insert on acs_objects
for each row
begin
    insert into acs_object_context_index
     (object_id, ancestor_id, n_generations)
    values
     (:new.object_id, :new.object_id, 0);

    if :new.context_id is not null and :new.security_inherit_p = 't' then
      insert into acs_object_context_index
       (object_id, ancestor_id,
        n_generations)
      select
       :new.object_id as object_id, ancestor_id,
       n_generations + 1 as n_generations
      from acs_object_context_index
      where object_id = :new.context_id;
    elsif :new.object_id != 0 then
      -- 0 is the id of the security context root object
      insert into acs_object_context_index
       (object_id, ancestor_id, n_generations)
      values
       (:new.object_id, 0, 1);
    end if;
end;

One final note about acs_objects. By setting an object's security_inherit_p column to 'f', you can stop permissions from cascading down the context tree. In the following example, Joe does not have the read permissions on C and F.

Privileges are also organized hierarchically. In addition to the five main system privileges defined in the ACS Kernel data model, application developers may define their own. Note, however, that this is no longer recommended practice.

By defining parent-child relationship between privileges, the OpenACS data model makes it easier for developers to manage permissions. Instead of granting a user explicit read, write, delete, and create privileges on an object, it is sufficient to grant the user the admin privilege to which the first four privileges are tied. Privileges are structured as follows.

Note that admin privileges are greater than read, write, create and delete privileges combined. Issuing someone read, write, create and delete privileges will not result in the person getting admin privileges.

The parent-child relationship between privileges is represented in the acs_privilege_hierarchy table:

  create table acs_privilege_hierarchy (
      privilege
          not null
          constraint acs_priv_hier_priv_fk references acs_privileges (privilege),
      child_privilege
          not null
          constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege),
      constraint acs_privilege_hierarchy_pk
          primary key (privilege, child_privilege)
  );
    

As in the case of the context hierarchy, it is convenient to have a flattened representation of this hierarchal structure. This is accomplished by defining the following view.

  create or replace view acs_privilege_descendant_map
  as
  select
    p1.privilege,
    p2.privilege as descendant
  from
    acs_privileges p1,
    acs_privileges p2
  where
    p2.privilege in
      (select
         child_privilege
       from
         acs_privilege_hierarchy
       start with
         privilege = p1.privilege
       connect by
         prior child_privilege = privilege
      )
    or p2.privilege = p1.privilege;
    

As the number of different privileges in the system is expected to be reasonably small, there is no pressing need to cache the flattened ansector-descendant view of the privilege hierarchy in a specially maintained table like it is done in the case of the context hierarchy.

Now for the third hierarchy playing a promiment role in the permission system. The party data model is set up as follows.

  create table parties (
      party_id
          not null
          constraint parties_party_id_fk references acs_objects (object_id)
          constraint parties_party_id_pk primary key,
      email               varchar2(100)
          constraint parties_email_un unique,
      url                 varchar2(200)
  );
    

  create table persons (
      person_id
          not null
          constraint persons_person_id_fk references parties (party_id)
          constraint persons_person_id_pk primary key,
      first_names          varchar2(100)
          not null,
      last_name            varchar2(100)
          not null
  );
    

  create table users (
      user_id
          not null
          constraint users_user_id_fk references persons (person_id)
          constraint users_user_id_pk primary key,
      password        char(40),
      -- other attributes
  );
    

  create table groups (
      group_id
          not null
          constraint groups_group_id_fk references parties (party_id)
          constraint groups_group_id_pk primary key,
      group_name           varchar2(100) not null
  );
    

Recall that the grantee_id column of the acs_permissions table references parties.party_id. This means that you can grant a privilege on an object to a party, person, user, or group. Groups represent aggregations of parties. The most common scenario that you are likely to encounter is a group that is a collection of users, although you could also have collections of persons, groups, parties, or any mix thereof.

Given that the most common use of groups is to partition users, how do you build groups? One way is to grant membership explicitly. If you have a group named Pranksters, you can assign membership to Pete, Poly, and Penelope. The fact that these users are members of the Pranksters group will be recorded in the membership_rels and acs_rels tables:

  create table acs_rels (
      rel_id
          not null
          constraint acs_rels_rel_id_fk references acs_objects (object_id)
          constraint acs_rels_pk primary key,
      rel_type
          not null
          constraint acs_rels_rel_type_fk references acs_rel_types (rel_type),
      object_id_one
          not null
          constraint acs_object_rels_one_fk references acs_objects (object_id),
      object_id_two
          not null
          constraint acs_object_rels_two_fk references acs_objects (object_id),
      constraint acs_object_rels_un
          unique (rel_type, object_id_one, object_id_two)
  );
    

  create table membership_rels (
      rel_id
          constraint membership_rel_rel_id_fk references acs_rels (rel_id)
          constraint membership_rel_rel_id_pk primary key,
      -- null means waiting for admin approval
      member_state         varchar2(20)
          constraint membership_rel_mem_ck
           check (member_state in ('approved', 'banned', 'rejected', 'deleted'))
  );
    

The acs_rels table entries would look like so:

Read acs_rels: right-side is a subset of left-side, ie object2 is a part of object1.

Another way of building up groups is by adding subgroups. Suppose we define Merry Pranksters and Sad Pranksters as subgroups of Pranksters. We say that the Pranksters group is composed of groups Merry Pranksters and Sad Pranksters. This information is stored in the acs_rels and composition_rels tables.

create table composition_rels (
    rel_id
        constraint composition_rel_rel_id_fk references acs_rels (rel_id)
        constraint composition_rel_rel_id_pk primary key
);
    

The relevant entries in the acs_rels look like so.

The composition relationship means that if I add Matt, Mel, and Mary to the Merry Pranksters, they should also automatically become members of the Pranksters group. The situation we are facing in trying to determine whether or not a user is member of a group is similar to the one discussed above in the case of the context hierarchy. Groups can form hierarchies with respect to the composition relationship. The compositon relationship is transitive. If G1 is a subgroup of G2, and G2 is a subgroup of G3, then G1 is a subgroup of G3; that is, any member of G1 is also a member of G3.

Traversing the group composition hierarchy requires running hierarchical queries, which are expensive in Oracle. As we saw in the Context Hierarchy section, one way of reducing the performance hit incurred by hierarchical queries is to cache query results in a table maintained by triggers. The OpenACS data model defines two such tables:

 create table group_component_index (
          group_id        not null
                          constraint group_comp_index_group_id_fk
                          references groups (group_id),
          component_id    not null
                          constraint group_comp_index_comp_id_fk
                          references groups (group_id),
          rel_id          not null
                          constraint group_comp_index_rel_id_fk
                          references composition_rels (rel_id),
          container_id    not null
                          constraint group_comp_index_cont_id_ck
                          references groups (group_id),
          constraint group_component_index_ck
          check (group_id != component_id),
          constraint group_component_index_pk
          primary key (group_id, component_id, rel_id)
  ) organization index;
    

  create table group_member_index (
      group_id
          not null
          constraint group_member_index_grp_id_fk references groups (group_id),
      member_id
          not null
          constraint group_member_index_mem_id_fk references parties (party_id),
      rel_id
          not null
          constraint group_member_index_rel_id_fk references membership_rels (rel_id),
      container_id
          not null
          constraint group_member_index_cont_id_fk references groups (group_id),
      constraint group_member_index_pk
          primary key (member_id, group_id, rel_id)
  ) organization index;
    

The group_component_index table stores a flattened representation of the group composition hierarchy that is maintained in sync with the acs_rels and composition_rels tables through triggers.

additional comments

As far as the group_member_index table goes, I am not sure I understand its purpose. It maintains group-member relationships that are resolved with respect to group composition. Note that information stored in group_member_index can be trivially derived by joining membership_rels, acs_rels, and group_component_index. Here is a view that does it. (This view is not part of the OpenACS Kernel data model.)

create or replace view group_member_view
as
select
  gci.group_id, r.object_id_two as member_id
from
  (
   select
     group_id, group_id as component_id
   from
     groups
   union
   select
     group_id, component_id
   from
     group_component_index
  ) gci,
  membership_rels mr,
  acs_rels r
where
  mr.rel_id = r.rel_id
  and r.object_id_one = gci.component_id;
    

A heuristic way to verify that group_member_view is essentially identical to group_member_index is to compute the symmetric difference between the two:

select
  group_id, member_id
from
  (
   select group_id, member_id from group_member_view
   minus
   select group_id, member_id from group_member_index
  )
union
select
  group_id, member_id
from
  (
   select group_id, member_id from group_member_index
   minus
   select group_id, member_id from group_member_view
  )
    

The query returns no rows. The important point is, if we have a flattened view of the composition hierarchy -- like one provided by the group_component_index table -- membership relationship resolution can be computed trivially with no hierarchical queries involved. There is no need to keep the view in a denormalized table, unless doing so results in substantial performance gains.

Security information is queried by calling the acs_permission.permission_p function in OpenACS. This is accessible from Tcl via the permission::permission_p procedure.

  create or replace package body acs_permission
  as
    -- some stuff removed for the sake of brevity

    function permission_p (
      object_id	 acs_objects.object_id%TYPE,
      party_id	 parties.party_id%TYPE,
      privilege	 acs_privileges.privilege%TYPE
    ) return char
    as
      exists_p char(1);
    begin
      -- XXX This must be fixed: -1 shouldn't be hardcoded (it is the public)
      select decode(count(*),0,'f','t') into exists_p
        from acs_object_party_privilege_map
       where object_id = permission_p.object_id
         and party_id in (permission_p.party_id, -1)
         and privilege = permission_p.privilege;
      return exists_p;
    end;

  end acs_permission;
    

problem avoidance

The function queries acs_object_party_privilege_map, which is a humongous view that joins three flattened hierarchies: the context tree, the privilege hierarchy, the party composition (and membership) hierarchy. It contains an extremely large number of rows. About the only kind of query you can run against it is the one performed by the acs_permission.permission_p function. Anything other than that would take forever to finish or would ultimately result in a query error.

For example, do not try to do things like

select count(*)
  from acs_object_party_privilege_map;
    

To give another example of things to avoid, I have seen code like this:

  declare
      cursor cur is
        select
           object_id, party_id
        from
           acs_object_party_privilege_map
        where
           privilege = 'foo_create';
  begin
      -- revoke all 'foo_create' permissions
      for rec in cur
      loop
          acs_permission.revoke_permission (
              object_id  => rec.object_id,
              grantee_id => rec.party_id,
              privilege  => 'foo_create'
          );
      end loop;

      acs_privilege.remove_child('admin','foo_create');
      acs_privilege.drop_privilege('foo');

  end;
  /
    

The acs_permission.revoke_permission function merely runs a delete statement like so:

  delete from
     acs_permissions
  where
     object_id = revoke_permission.object_id
     and grantee_id = revoke_permission.grantee_id
     and privilege = revoke_permission.privilege;
    

Note that in the above example, acs_permissions had only one entry that needed to be deleted:

The above script would never get around to deleting this entry because it had to loop through a gazillion rows in the humongous acs_object_party_privilege_map view.

create or replace view acs_object_party_privilege_map
as
select
  ogpm.object_id,
  gmm.member_id as party_id,
  ogpm.privilege
from
  acs_object_grantee_priv_map ogpm,
  group_member_map gmm
where
  ogpm.grantee_id = gmm.group_id
union
select
  object_id,
  grantee_id as party_id,
  privilege
from
  acs_object_grantee_priv_map;
    

create or replace view acs_object_grantee_priv_map
as
select
  a.object_id,
  a.grantee_id,
  m.descendant as privilege
from
  acs_permission_all a,
  acs_privilege_descendant_map m
where
  a.privilege = m.privilege;
    

create or replace view acs_permissions_all
as
select
  op.object_id,
  p.grantee_id,
  p.privilege
from
  acs_object_paths op,
  acs_permissions p
where
  op.ancestor_id = p.object_id;
    

create or replace view acs_object_paths
as
select
  object_id,
  ancestor_id,
  n_generations
from
  acs_object_context_index;
    

create or replace view group_member_map
as
select
  group_id,
  member_id,
  rel_id,
  container_id
from
  group_member_index;
    

OpenACS is distributed in many different ways:

Upgrades work by first changing the file system (via any of the previous methods), and then using the APM to scan the file system, find upgrade scripts, and execute them. Starting with OpenACS 5.0, the last method was added, which automatically changes the file system for you. If you are using the last method, you can skip this page. This page describes whether or not you need to be upgrading using this page or not: the section called “Upgrading an OpenACS 5.0.0 or greater installation”

Upgrading files for a site using the OpenACS CVS repository (cvs.openacs.org)

If you are upgrading a production OpenACS site which is on a private CVS tree, this process lets you do the upgrade without risking extended downtime or an unusable site:

Before OpenACS 3.3, the OpenACS startup process was extremely simple: after AOLserver performed its internal initialization (reading the configuration file, loading shared libraries and module code, etc.) it scanned through the Tcl library directory (generally /var/lib/aolserver/yourservername/tcl), sourcing each file in sequence.

While this overall structure for initialization is still intact, package management has thrown a wrench into the works - there are a few extra things to do during initialization, most notably:

This document examines in detail each of the steps involved in AOLserver/OpenACS startup.

As soon as the nsd daemon is executed by the init process (or otherwise), AOLserver reads its configuration file and chroots itself if necessary. It then loads shared libraries indicated in the .ini file (e.g., the Oracle driver and nssock), and sources Tcl module files (generally in /home/aol30/modules/tcl). This step is, and has always been, the same for all AOLservers, regardless of whether they are running OpenACS.

Next AOLserver sources, in lexicographical order, each file in the /tcl directory. The first such file is 0-acs-init.tcl, which doesn't do much directly except to determine the OpenACS path root (e.g., /var/lib/aolserver/yourservername) by trimming the final component from the path to the Tcl library directory (/var/lib/aolserver/yourservername/tcl). But 0-acs-init.tcl's has an important function, namely sourcing /packages/acs-core/bootstrap.tcl, which does the following:

At this point, bootstrap.tcl is done executing. AOLserver proceeds to source the remaining files in the /tcl directory (i.e., unpackaged libraries) and begins listening for connections.

The OpenACS 5.2.3rc1 Permissions system allows developers and administrators to set access control policies at the object level, that is, any application or system object represented by a row in the acs_objects table can be access-controlled via a PL/SQL or Tcl interface. The permissions system manages a data model that then allows scripts to check permissions using another API call.

Although object level permissions seems appropriate, no developer or administrator wants to explicitly set access control rights for every user and every object on a site. Therefore, OpenACS has two auxiliary mechanisms for making this easier:

The rest of this document discusses each of these parts, and how they fit together with the permissions system.

OpenACS 5.2.3rc1 has an abstraction called a party. Parties have a recursive definition. We can illustrate how it works with the following simplified data model. First, we define the parties table, where each party has an email address and a URL for contact information.

create table parties (
    party_id  integer not null references acs_objects(object_id),
    email varchar(100),
    url varchar(100)
)

Now we define two subtypes of party, one for persons, and one for groups:

create table groups (
    group_id  not null references parties(party_id),
    group_name varchar(100) not null
)

create table persons (
    person_id not null references parties(party_id),
    first_names varchar(100) not null,
    last_name varchar(100) not null
)

The users table is also defined in this data model as a subtype of person.

Finally, we define two relations, one for group membership and one for group composition.

The composition relation expresses that every member of group A should also be a member of group B. This relation allows us to define a hierarchy of groups.

The membership relation maps groups to parties. Each member of a group is a party rather than just a user. That is, groups consist of members that are either a person or an entire group. This allows us to say that group A should be a member of another group B.

The groups data model is recursive. Modelling parties as either a person or a group provides a way to model complex hierarchical groupings of persons and groups.

The full details of the groups data model is beyond the scope of this tutorial. See Parties in OpenACS or OpenACS 4 Groups Design for more details.

NOTE: Much more detailed information about the permissions system and how to use it is available in the OpenACS Permissions Tediously Explained document.

The permissions data model is a mapping between privileges, parties and objects. Parties and objects have already been discussed. Now we focus on privileges.

In OpenACS, a privilege describes the right to perform some operation on some object. Privileges are the basic units out of which we build access control policies. For example in the Unix filesystem, access is controlled by granting users some combination of read, write, or execute privileges on files and directories. In OpenACS 5.2.3rc1, the table of privileges is organized hierarchically so that developers can define privileges that aggregate some set of privileges together. For example, if we have read, write, create and delete privileges, it might be convenient to combine them into a new privilege called "admin". Then, when a user is granted "admin" privilege, she is automatically granted all the child privileges that the privilege contains. The OpenACS 5.2.3rc1 kernel data model defines these privileges:

#
begin
 acs_privilege.create_privilege('read');
 acs_privilege.create_privilege('write');
 acs_privilege.create_privilege('create');
 acs_privilege.create_privilege('delete');
 acs_privilege.create_privilege('admin');

 acs_privilege.add_child('admin', 'read');
 acs_privilege.add_child('admin', 'write');
 acs_privilege.add_child('admin', 'create');
 acs_privilege.add_child('admin', 'delete');

 commit;
end;

Note that a user does not gain admin privileges when granted read, write, create and delete privileges, because some operations explicitly require admin privileges. No substitutions.

To give a user permission to perform a particular operation on a particular object you call acs_permission.grant_permission like this:

# sql code
    acs_permission.grant_permission (
      object_id => some_object_id,
      grantee_id => some_party_id,
      privilege => 'some_privilege_name'
      );

Using just these mechanisms is enough for developers and administrators to effectively define access control for every object in a system.

Explicitly defining permissions to every object individually would become very tedious. OpenACS provides a object contexts as a means for controlling permissions of a large group of objects at the same time.

In OpenACS 5.2.3rc1, object context is a scoping mechanism. "Scoping" and "scope" are terms best explained by example: consider some hypothetical rows in the address_book table:

The first row represents an entry in User 123's personal address book, the second row represents an entry in User Group 456's shared address book, and the third row represents an entry in the site's public address book. In this way, the scoping columns identify the security context in which a given object belongs, where each context is either a person or a group of people or the general public (itself a group of people).

Every object lives in a single context. A context is just an another object that represents the security domain to which the object belongs. By convention, if an object A does not have any permissions explicitly attached to it, then the system will look at the context_id column in acs_objects and check the context object there for permissions. Two things control the scope of this search:

If security_inherit_p flag is set to 't', then the automatic search through the context happens, otherwise it does not. You might set this field to 'f' if you want to override the default permissions in a subtree of some context.

For an example of how to use context hierarchy, consider the forums application. With only row-level permissions it is not obvious how to reasonably initialize the access control list when creating a message. At best, we have to explicitly grant various read and write privileges whenever we create a message, which is tedious. A reasonable thing to do is to create an object representing a forum, and point the context_id field of a new message at the forum. Then, suppose we grant every user in the system read-access to this forum. By default, they will automatically have read-access to the new message we just inserted, since the system automatically checks permissions on the message's context. To allow the creator of the message to change the message after it has been posted we grant the user write-access on the message, and we are done.

This mechanism allows developers and administrators to define a hierarchy that matches the structure they need for access control in their application. The following picture shows a typical context hierarchy for a hypothetical site:

The top two contexts in the diagram are called "magic" numbers, because in some sense, they are created by default by OpenACS for a specific purpose. The object default_context represents the root of the context hierarchy for the entire site. All permission searches walk up the tree to this point and then stop. If you grant permissions on this object, then by default those permissions will hold for every object in the system, regardless of which subsite they happen to live in. The object security_context_root has a slightly different role. If some object has no permissions attached to it, and its value for security_inherit_p is 'f', or context_id is null, this context is used by default.

See the package developer tutorials for examples on how to use permissions code.

OpenACS 5.2.3rc1 defines three separate mechanisms for specifying access control in applications.

A PL/SQL or Tcl API is then used to check permissions in application pages.

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, and we have a coherent API for database access which makes this even easier.

More detailed information about the DB api is available at Database Access API.

The OpenACS database API is meant to save developers from making common mistakes and to provide a more structured syntax for specifying database operations, including transactions. Here's an example of the API.

set count 0
set tcl_var "foo"
set sql {
      	SELECT foo, bar, baz
       FROM some_table, some_other_table
       WHERE some_table.id = some_other_table.id
         and some_table.condition_p = :tcl_var
}

db_transaction {
    db_foreach my_example_query_name $sql {
        lappend rows [list $foo $bar $baz]
        incr count
    }
    foreach row $rows {
        call_some_proc $foo $bar $baz
    }
}

There are several things to note here:

Bind variables are placeholders for literal values in an SQL query being sent to the server. In the old way, data was generally passed to directly to the DB backend, via Tcl string interpolation. In the example above, the query would look like:

select foo, bar, baz
from some_table, some_other_table
where some_table.id=some_other_table.id
and some_table.condition_p = '$foo'

There are a few problems with this:

What the DB API (in conjuntion with the database drivers implemented for aolserver) do is send the SQL statement to the server for parsing, then bind values to the variables and sends those values along seperately as a second step. This seperate binding step is where the term bind variable comes from.

This split has several advantages. First, type checking happens on the literal. If the column we are comparing against holds numbers, and we send a string, we get a nice error. Second, since string literals are no longer in the query, no extra quoting is required. Third, substitution of bind variables cannot change the actual text of the query, only the literal values in the placeholders. The database API makes bind variables easy to use by hooking them smoothly into the Tcl runtime so you simply provide :tclvar and the value of $tclvar is sent to the backend to actually execute the query.

The database API parses the query and pulls out all the bind variable specifications and replaces them with generic placeholders. It then automatically pulls the values of the named Tcl vars out of the runtime environment of the script, and passes them to the database.

Note that while this looks like a simple syntactic change, it really is very different from how interpolated text queries work. You use bind variables to replace what would otherwise be a literal value in a query, and Tcl style string interpolation does not happen. So you cannot do something like:

set table "baz"
set condition "where foo = bar"

db_foreach my_query { select :table from some_table where :condition }
    

SQL will not allow a literal to occur where we've put the bind variables, so the query is syntactically incorrect. You have to remember that while the bind variable syntax looks similar to variable interpolation in Tcl, It is not the same thing at all.

Finally, the DB API has several different styles for passing bind variable values to queries. In general, use the style presented here because it is the most convenient.

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

      

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

      

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

      

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

      

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

The database library can transparently maintain pools of sequence values, so that each request for a new sequence value (using db_nextval) does not incur a roundtrip to the server. For instance, this functionality is very useful in the security/sessions library, which very frequently allocates values from the sec_id_seq sequence. To utilize this functionality for a particular sequence, register the sequence to be pooled, either using the db_register_pooled_sequence procedure at server startup time, or by including a configuration parameter of the form

PoolSequence.sequence_name_seq=count

in any configuration section in the yourservername.ini file, e.g.,

[ns/server/yourservername/acs/security]
PoolSequence.sec_id_seq=20

    

The database library will allocate this number of sequence values at server startup. It will periodically scan pools and allocate new values for sequences which are less than half-full. (This normally occurs every 60 seconds, and is configurable via the PooledSequenceUpdateInterval parameter in the [ns/server/yourservername/acs/database] configuration section.)

The Database API has several functions that wrap familiar parts of the AOLserver database API.

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.

The OpenACS templating system allows you to give your site a consistent look and feel. It also promotes code maintainability in the presentation layer, by allowing presentation components to be reused across multiple pages. If you need to change the layout for some reason, then you only need to make that change in one location, instead of across many files.

In this problem set you will familiarise yourself with the templating system in openacs. This will be achieved through customising an existing edit-this-page application template.

Before proceeding, it is strongly advised to read the templating documentation on your openacs installation (http://localhost:8000/doc/acs-templating). The documentation lists the special tags available for ADP files.

This problem set was originally written by Nick Carroll in August 2004 for the University of Sydney Course EBUS5002.

This material is copyright 2004 by Nick Carroll. It may be copied, reused, and modified, provided credit is given to the original author.