Background of this document

It was slightly modified to be useful for OpenACS, mainly by substituting 'ACS' with 'OpenACS', replacing non-existent links with new ones and removing Arsdigita specific text, but the main content has not been changed. Thus there might be some stuff in it that is outdated and could be done differently/more efficient with new OpenACS techniques - so read this with a grain of salt. Consult an experienced OpenACS programmer for help if you have the opportunity to.

Also note that it is Oracle specific - if you are using OpenACS with PostgreSQL you have to adapt it accordingly.

Reading for this week

• http://openacs.org/doc/acs-dev (objects, permissioning, parties, packages, subsites, templating, workflow)
• http://openacs.org/doc/unix-install Installation of OpenACS on Unix/Linux.

Objectives

• the fundamentals of OpenACS 4.x
• software design skills emphasizing code modularity and generalization,
• how to build, install, and document a new OpenACS package using OpenACS engineering conventions.

In the first problem set, you learned how to use lots of new software tools. Mostly you needed to learn commands and procedure calls. You didn't care about the internals of Unix, Oracle, AOLserver, or the Tcl interpreter. In this second problem set, you will learn how to extend a library of source code. Thus, you'll have to learn about the internal structure of the toolkit. If you thought looking at your own source code was unpleasant, here's your chance to look at over 50,000 lines of someone else's :-)

Along the way we will introduce some standard practices that will help you work with other OACS programmers. These include APM packages, documentation standards, and techniques to generalize your code so that other applications may be built on top of it.

The Big Picture

Most software development projects start with a dialog between the users and the application programmers (you guys). From this dialog, the application programmers form a big picture of what needs to be done.

The discussion above contains the application's fundamental user scenarios. With these we can begin to formalize the requested functionality. The user scenarios text should go in section IV. User cases and User-scenarios of your Requirements document. Grab the template at http://openacs.org/doc/requirements-template for the proper format.

The meat of the requirements doc is in the (you guessed it) Requirements section. This is where you translate (and elaborate on) the client's specifications into a concrete list of the application functions. We have begun this process for you by translating the central function (user u schedules room r between start time s and end time e) into a formal requirement.

At this point you haven't made any design decisions for your application. You have created your system level requirements. Later when you start designing your implementation you may choose to assemble a number of software tools to fulfill your system's needs.

The Big (Software) Picture

You're building a program that keeps track of users, groups of users, rooms, and room reservations. You could build this program from scratch, but with OpenACS we already have a documented debugged toolkit, designed to support communities of users, subsets of which are collected into groups. You can probably get your program finished faster if you start with the toolkit, but more importantly you will achieve higher reliability (e.g., the user registration stuff has been debugged already) and higher maintainability (because the user and user groups portions of the software are documented in a book chapter residing at a permanent URL: http://philip.greenspun.com/panda/community).

Some of what you'll be doing in this problem set is creating RDBMS tables from scratch and writing Tcl procedures to perform transactions on those tables, just as you did in problem set 1. Much of what you're doing here, though, is figuring out clean ways of using and extending the toolkit. Sometimes this is as simple as visiting the toolkit admin pages (/admin) of your server and using Web forms to add user groups.

Exercise 0: Install OpenACS and Create some users

Download and follow detailed installation instructions for OpenACS 4.x. After successful configuration, you should be able to use the OpenACS installer to install the packages for your service.

Repeatedly visit /acs-admin/users to create a bunch of users for your community, i.e., pretend you are "billg@microsoft.com", "president@whitehouse.gov", "wile_e_coyote@iq207.supergenius.com" and fill out the registration form. Remember that these will be the folks who are reserving conference rooms. Make sure that at least a couple of them have real email addresses because you want to be sure that you can test your email alerts. If you don't have any cooperative friends, you might wish to make yourself a hotmail or yahoo email account. Sadly, you can't make several users with the same email address because we constrain the email column in the parties table to be unique.

Exercise 0.1: Notice that your server is self-documenting

Use a Web browser to connect to the /doc/ directory of your server and notice that you have a complete set of OpenACS documentation. You might find it useful to read the installation instructions and you will certainly want to read the developer's guide.

You will also want to look at the API Browser (/api-doc/). You should use ad_proc instead of proc when creating externally defined tcl procedures. Using ad_proc allows the API Browser to publish the source code and comments associated with the procedure you are defining. Please follow this convention for the rest of the semester.

Exercise 0.2: Creating an OpenACS Package and documenting Requirements

To improve portability, reuse and maintainability of your code, you will be creating an OpenACS package for this reservation system. As defined in the developer's guide, "a package is a structured collection of data models, code and documentation". ACS package manager (APM) guides you in creating and registering the package in your system. In addition, as you fill out the information including the key, name version etc., APM creates the .info file and keeps track of the dependencies. You will be following detailed directions for creating and implementing your package later but right now it is important to understand the structure of the files and documentation

As compared to the ACS 3.x versions, where everything you wanted to show about your server was under /web/yourserver/www/, now each package has its subdirectory tree and a mapping for its www directory. You should navigate to the package manager on your servers admin site (/acs-admin/apm/) add a new package (/acs-admin/apm/package-add) for your application. Now that you've added your package you will want to move your new requirements doc to its correct package location /web/yourserver/packages/reserve/www/doc/requirements.html. Note that you have to "enable" your package files by adding them to reserve package via its web interface under /acs-admin/apm.

Exercise 1: OpenACS Objects and documenting Package Design

Now that you have your application's requirements its time to plan your implementation. Here's where a design document comes in handy. You will want to build off this template. Stuff it in /web/youserver/packages/reserve/www/doc/design.html.

In essence, the Design document maps the application's requirements to higher level coding decisions. What database objects do you need? What are their attributes? What are the legal transactions that different classes of users can perform on these objects? Can you use any existing OpenACS services to speed your implementation and reduce redundant code and thus lessen what is necessary to maintain your application? What mechanisms that you create can be made available to other applications?

A good thing to do now is to step away from your computer with hardcopies of this problem set, your requirements doc, the introductionary documentation, and http://philip.greenspun.com/sql/data-modeling.html.

Read the problem set cover-to-cover before writing your design document. Otherwise, halfway through the pset, you might find yourself having to back up, alter tables, rewrite .tcl scripts, and rewrite documentation.

Here are some guidelines for your object design. Please note that this is not intended to be a complete listing of all the necessary steps.

• Remember that users of your system are already represented in the OpenACS System users object type; an Uber User will be recognized by membership in the Uber Users user group that you will create.

• A reservation object can be thought of as a relationship between a user object and a room object. Its fundamental attributes are start_time and end_time.

• There are at least two opportunities to make the reservation object more generic (and you will want to rename it to reflect its more generic nature).
  • By making the relationship between a user and any other type of object, we can create a generic scheduling service.
  • By going a bit further and exchanging the user object type for party object type in the relationship, both individuals and groups can schedule other objects for segments of time.

If there is an opportunity to generalize a part of your code, chances are that it should go in a separate OpenACS service package.

What does this do to our application's design? In this case we want to create two packages; the scheduling service package and the room reservation application package which is dependent on the API provided by the scheduling package.

Revisit /acs-admin/apm and create a new package named "schedule". Visit the reserve package's web interface and add the schedule package as a dependency.

If you haven't already, install the bboard package (need the specifics about where to grab it). Notice that it requires the package acs-messaging (which is one of the ACS kernel packages). Read /doc/bboard/design. It references the acs-messaging package (there should really be separate requirements and design docs for acs-messaging). Browse the bboard and acs-messaging code paying attention to how they relate.

Create new requirements and design docs for the scheduling package. Mine the reserve package's docs for information that is now more appropriate for the schedule package. In the end you should have two complimentary sets of documentation (the reserve docs should reference the schedule docs in their "Related Links" sections).

For this pset, the scheduling package should be composed solely of a data model and a PL/SQL API.

Exercise 1.1: Build the data model

Using the file naming and placement conventions for packages create the two SQL data model files to hold new object declarations, table definitions, PL/SQL packages, and permissions for your room reservation system (/web/yourserver/packages/schedule/sql/schedule-create.sql and /web/yourserver/packages/reserve/sql/reserve-create.sql).

As you create a package-name-create.sql file you should simultaneously build package-name-drop.sql so that your package can be uninstalled. Having a drop data model script also makes cleaning up, after sometimes messy development data models, a snap. Make sure to delete all rows inserted into other tables (acs_objects, acs_permissions) using their functions; drop all PL/SQL packages and tables created by your data models. A good rule of thumb is to drop things in the reverse order they were created. You might find /web/yourserver/bin/create-sql-drop-file.pl useful.

Here are a few pointers:

• OpenACS PL/SQL standards can be found at http://openacs.org/doc/eng-standards-plsql.
• You'll want to review the data modeling section of OpenACS 4.5 Data Models and the Object System for tips on PL/SQL packages and "constructor" functions. Take a look at some of the data model files found under /web/yourserver/acs-kernel/sql/ for examples.
• Standard auditing columns (creation_date, creation_ip, last_modified, etc.) are now in acs_objects.
• You will want to note any package dependencies (i.e. your table definition has a foreign key that references a table defined by another package's data model) that your data model (and later your tcl code, for that matter) has. Add them for your package via the web interface at /acs-admin/apm/.
• In the OpenACS data model permissions are a special type of object with an extensive API.
• Check out the Parties in OpenACS 4.5 to learn about how model using the established users API.

More OpenACS Data Model Standards

You should have a good understanding of what goes into a package's data model at this point. We'll use the room object as an example to cement some of the standards that you should follow:

Notice that we follow constraint naming standards in the create table statement:

create table rooms (
	room_id			not null
				constraint rooms_room_id_fk
				references acs_objects (object_id)
				constraint rooms_room_id_pk primary key
...

Consult the constraint naming standards for more details.

You should have also noticed that each new object type has a corresponding PL/SQL package (to be exact a package specification and body) which contains all the functions and procedures associated with it.

Exercise 1.2: Set down a list of legal transactions

Add transaction subsections to the Data Model Discussion sections of your design documents.

For each transaction, note the classes of user who can perform it, sketch the SQL and note any external implications, e.g., "administrator is sent email alert". From this set of transactions you should derive a permissions scheme. Make sure you read the permissions developer guide. Here is an example:

-- call the "constructor" function for the room object type
-- this has a call to the new function for acs_objects in it
-- which will insert the correct values into the correct tables
-- for a new object
room.new (
      room_name => 'room_name'
      creation_user => user_id,
      creation_ip => 'creation_ip',
      context_id => context_id
      ...
    );

From the permissions scheme you should have gathered the following:

• there are three different types of security inheritance:
  • by context_id,
  • group to group to user relationships,
  • and by privileges parent-child hierarchy

We mentioned earlier that you should create a user group for Uber Users. This is actually a design decision. You could achieve all the requested functionality strictly by allowing an administrator to grant uber privileges (the ability to bump) to individuals.

The determining factor is whether this subset of users will likely require any other tools attached to them as a group. For example, will someone want to spam members of this set? In this case, Uber Users are basically a group of managers and will probably have other desired functionality.

This goes beyond the scope of current listed requirements. In a real situation these are the questions you would want to ask in order to get the most complete specificaton.

Exercise 2: Get the basic admin pages done

We're not going to worry about being able to edit room names or designated room administrators for the moment. Instead, let's build the simplest possible system that enables the administrator to create rooms:

Use the OpenACS Templating System (see /doc/acs-templating/developer-guide and below) to create the following -

• Build an index page under packages/reserve/www/admin/ that shows an HTML unordered list (UL) of the names of all the rooms, a blank line, and then an "add a room" link. This page should simply handle the situation in which there are no rooms registered.
• Build packages/reserve/www/admin/room-add and packages/reserve/www/admin/room-add-2 that will insert a room into the database.

The templating system available with OpenACS 4.x separates the code that generates the data (.tcl) from the presentation details as returned to the browser (.adp). Copy packages/news/www/admin/index.tcl and index.adp to the admin/ directory of your packages and use these two files as starting point for developing your templates.

• The datasource (.tcl file) starts with the usual ad_page_contract to process the page input.
• The -properties block if present is for a feature of the templating system that was never implemented. Simply ignore it for now.
• The -validate and -errors block centralize the input processing and error reporting.

The dynamic data is generated and filled into the datasources by Tcl code. The ADP page provides the presentation layer for datasources from the Tcl page.

• It contains the HTML or other registered tags for formatting the datasources.
• Since all the logic for datasource processing is the responsibility of the Tcl pages, it should not contain <% or <%=tags.

A detailed guideline for creating forms and implementing the data sources including mechanisms for onevalue, onelist, onerow, multirow are provided here as a part of the developer guide.

Exercise 2.1: Write the basic user pages

We're not going to worry about uber-users or room approval for the moment. Instead, let's build a basic system:

• Build packages/reserve/www/index.tcl and packages/reserve/www/index.adp as the gateway to the room reservation system. The script should start out by checking for a user_id cookie (use ad_maybe_redirect_for_registration, defined in packages/acs-kernel/tcl/security-procs.tcl) and, if not present, redirecting users over to /register/index.

  For a logged-in user, the page starts out with an HTML form for requesting a new reservation. The form contains the following elements:

  • start and end date-times (or start time and duration; the user interface does not have to be consistent with the data model). If you want to make life easier for yourself, it is perfectly acceptable to force the user to enter date/times in ANSI timestamp format: "YYYY-MM-DD HH24:MI:SS". Then you can drop this right into Oracle using a to_date.
  • number of people who will be occupying the room
  Underneath this form, the page displays a personalized list of the rooms that the authenticated user has already reserved for the future, ordered by the start times. By showing this list automatically, you free yourself from having to write special pages to confirm reservations or check reservation history.

• Build packages/reserve/www/new.tcl and packages/reserve/www/new.adp that will take input from the form in index and find available rooms. This page should refrain from showing rooms that are booked for any part of the time period requested or rooms that aren't large enough for the number of people specified. For your query, it is wise to remember that
  • you can select from the rooms table with a WHERE clause that subqueries the room_reservations table.
  • Although SQL contains a BETWEEN operator, e.g., t1.start_time between t2.start_time and t2.end_time, it might not be the best way to catch all cases of overlapping rooms.
  • a professional SQL programmer would probably end up coding this with a where not exists (select 1 from ...)

  You'll have to pass the user's input from index through to new.tcl. You'll either be doing this with URL variables (if you've chosen an interface based on simple links) or HTML form hidden variables (if you've chosen a form interface with method=POST). In packages/acs-kernel/tcl/utilities.tcl we've defined export_url_vars and export_form_vars that facilitate this. If you have start_time and end_time defined as Tcl local variables, you can export them to the next page with new-2.tcl?[export_url_vars start_time end_time] or [export_form_vars start_time end_time] inside a form.

• Build packages/reserve/www/new-2.tcl to take input from new.tcl and ad_returnredirect back to index (where the user will see that the room has in fact been reserved). This page should make sure that there isn't a time conflict, then insert the reservation. If there is a time conflict, return a message to that effect and a link back to new with the user's original parameters in place (presumably this time new will not offer the already-booked room).

How could there be a time conflict given that the preceding page only showed free rooms? Here's the concurrency situation that we're worried about:

If you want to act like a professional Web programmer, you therefore have to run the same query in new-2.tcl that you ran in new.tcl to make sure that the room is in fact still free.

What do the users get from their professionally programmed systems? Double booked rooms. The average professional Web programmer probably won't handle the case where two users submit room requests at precisely the same second.

Hold yourself to a higher standard by making new-2.tcl lock the reservations table before querying to confirm room availability. Read the transactions chapter of SQL for Web Nerds (http://philip.greenspun.com/sql/) to find out how to lock tables.

Making it Real

We've got the rudiments. The site owner can define rooms. Users can book rooms. Reservations won't conflict. If you were a West Coast software company, you'd declare victory at this point. Issue some press releases, ship the product in a nice-looking shrink-wrapped box (as Release 3.2), and sell some shares to the public.

One of the ugly facets of offering a Web service, though, is that users will hammer you with email until you make the thing real.

Exercise 3: Finish the admin pages

Here's a step-by-step plan:

• Link each room in the packages/reserve/www/admin/index listing to room-edit where the site owner can change a room's name or mark a room as requiring approval. Part of marking a room as requiring approval is using /user-search [this may not be accurate anymore, might be /acs-admin/users/search] to let the site owner choose a room administrator from the users table.
• Build a packages/reserve/www/admin/reports/ directory. The index page in this new directory should list the rooms and, for each room, show the total number of hours that the room has been reserved and the average number of people in a reservation for that room. This list should be sortable by name or number of hours booked. All the stats should be limited to reservations in the preceding 12 months (use the ADD_MONTHS function in oracle SQL).

  Hints: You can do this report with a 4-line SQL query. You don't need to do any date arithmetic, summing, rounding, or averaging in Tcl (Oracle SQL has functions for these things). Keys to solving the problem are reading up on the SQL GROUP BY clause. Also note that order by 3 will order by the 3rd item in the select list.

  If you want to really do something nice, make the report include rooms for which there haven't been any reservations (showing the total hours as 0; hint: use NVL). If you want to preserve the one-year limit, you'll end up with a SQL query of the form

select ...
from table1, (select ... from table2 where ...) table2
where table1.col1 = table2.col2(+)

Here you're querying from a real table and a view created on-the-fly from stable2.

• Make the room names in the packages/reserve/www/admin/reports/index page hyperlink anchors. The targets for those anchors should be a details.tcl page where all the reservations are shown, most recent at the top.

Exercise 4: Finish the user pages

• Edit packages/reserve/www/new so that a member of the Uber User group sees both unreserved and reserved-but-bumpable (i.e., not reserved by another Uber User) rooms. A user who is not an Uber User should see a flag by rooms that require approval.

• Edit packages/reserve/www/new-2.tcl to recognize the following cases:
  • Uber User bumping regular user (delete original reservation; send email to regular user; place Uber User's reservation)

  • Regular user requesting approval-required room (insert reservation, email administrator, serve special page to user saying "your request has been submitted to ... and we'll let you know if it is approved")

  See the AOLserver Tcl Developer's Guide at aolserver.com for an explanation of the ns_sendmail API call

• We would like room administrators to be able to approve or deny room requests directly from their email client ("one-click approval"). So the email notice to a room administrator must contain a URL which, if visited, will approve the room request without the room administrator having to log in. An alternative URL in the same email message would allow the room admin to deny a reservation request. This mechanism should be reasonably secure, i.e., a user should not be able to approve his or her own request by doing some URL surgery.

  Hint: You can write a secret key into the reservations table at the time of the request and require that a one-click approval URL contain the secret key. Tcl doesn't contain a random number generator, though you can call sec_random_token, defined in our packages/acs-core/security-procs.tcl file. Alternatively, you can simply use the integer returned by ns_time.

You now have a working system as envisioned by your clients. Congratulations.

Exercise 5: Finish the Packages

Part of the coding process is testing if the latest feature you added works and seeing if it breaks any existing functionality. This is a continuous process and can easily lead to hours of tedium.

This is where the links to Arsdigita specific quality assurance resources used to be (they are not available anymore). If you want you can investigate in the following more recent testing efforts which are used in some parts of the OpenACS project: ACS automated testing package, which is part of your OpenACS installation; tclwebtest.

If this were a real project now would be the time to wrap it up and give it to the world. You will need to register the package as a part of that process your code will go through a review sequence.

Exercise 6 (extra credit): Find a Venture Capitalist

Take the code that you wrote for this problem set. Give it a name that includes the neologism "middleware". Find a venture capitalist to give you $6 million.

The Wide World of Oracle

We're going to shift gears now into a portion of the problem set designed to teach you more about Oracle and SQL.

Oraexercise 1: Concurrency and Isolation

Connect to Oracle in SQL*Plus and type "describe my_stocks" to make sure that your my_stocks table is still defined from problem set 1.

• start a second SQL*Plus session, connected as the same user as your first
• type "delete from my_stocks;" in Session 1
• type "select * from my_stocks;" in both sessions
• type "rollback;" in the Session 1

Oraexercise 2: The Importance of Commitment

• go back into SQL*Plus:

SQL> create table foobar (
        the_key  integer primary key
);
SQL> insert into foobar (the_key) 
     values (1);

1 row created.

Leave this session open and do not type COMMIT

• build an AOLserver .tcl page that gets a database handle and then executes the same insert into foobar (the_key) values (1) statement
• go to a Web browser and request your AOLserver .tcl page
• watch the Web browser hang
• type "COMMIT;" into SQL*Plus
• look at the AOLserver error log

Repeat the above sequence but use ROLLBACK at the end in SQL*Plus instead of COMMIT.

Who Wrote This and When

This problem set was originally written by Philip Greenspun and Hal Abelson in February 1999 for MIT Course 6.916. It was re-written to incorporate ACS4.0 standards in October 2000 by Walter McGunnis, Simon Hyunh and Ravi Jasuja.

The earlier version of this pset is permanently housed at http://philip.greenspun.com/teaching/psets/ps2/ps2.adp

This material is copyright 1999, 2000, by Philip Greenspun and Hal Abelson. It may be copied, reused, and modified, provided credit is given to the original authors with a hyperlink to the original document.