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.
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.
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.
|Users: We're the world's leading experts on computers. We have a big building full of researchers, hundreds of advanced computer systems, and a handful of conference rooms. We'd like a Web-based system for scheduling conference rooms.|
|You: How are you doing it now?|
|Users: Secretaries, pencils, and paper appointment books.|
|You: Ah. So how do you envision the computer-based system working?|
|Users: Jane Nerd comes to the server and asks for a room from Time X to Time Y, capable of holding N people. The server offers Jane a choice of rooms, each one accompanied by a description of the AV facilities, and she can pick whichever she likes the best.|
|You: Anything else special?|
|Users: There is a set of Uber-Users. If Alice Uber can't find a room that she likes, we want her to be able to boot some grunts out of their reserved spot. Alice should not be able to preempt another Uber-User, though. The booted grunt should get an email alert from the server saying "Alice booted you; come back and pick another room."|
|You: So any user can reserve any room?|
|Users: No. Most of the conference rooms are reservable by anyone. However, some rooms require approval of reservations by a designated administrator (who can be any user in the community), unless the reservation was requested by an Uber-User. The designated administrator is notified by email any time that a reservation is requested for a particular room, and should be able to approve or deny the request by clicking on one of two encoded URLs in the email message.|
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.
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.
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 "firstname.lastname@example.org",
"email@example.com", "firstname.lastname@example.org" 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
parties table to be unique.
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.
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.
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.
usersobject type; an Uber User will be recognized by membership in the Uber Users user group that you will create.
userobject and a
roomobject. Its fundamental attributes are
userand any other type of object, we can create a generic scheduling service.
userobject type for
partyobject 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.
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:
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.
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_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:
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.
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 -
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.
ad_page_contractto process the page input.
-propertiesblock if present is for a feature of the templating system that was never implemented. Simply ignore it for now.
-errorsblock 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.
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.
We're not going to worry about uber-users or room approval for the moment. Instead, let's build a basic system:
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:
roomstable with a WHERE clause that subqueries the
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.
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
facilitate this. If you have
end_time defined as Tcl local variables, you can export
them to the next page with
end_time] inside a form.
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:
|Olin Egghead says that he needs a room for 8 people at 3:00 pm tomorrow, for a 45-minute meeting.|
|Your package serves Olin the new page, showing that the Boesky Room is available.||Dorothy Alvelda says that she needs a room for 5 people tomorrow at 3:30 pm.|
|Olin is about to confirm the Boesky Room when the phone rings. A
Wall Street firm is offering him $250,000 to become a C programmer in
their commodities trading division. Olin immediately replies that he
wouldn't consider abandoning his sacred academic principles for a
An hour later, Olin is still on the phone.
|Since Olin has not yet booked any rooms,
new shows Dorothy that the Boesky Room is among the available rooms
at 3:30 tomorrow.
Dorothy confirms the Boesky Room and new-2.tcl inserts a row into the database.
|Olin hangs up and gets preoccupied with copying impressive-looking equations from the pizza delivery section of the Thessaloniki Yellow Pages into his latest research paper.|
|Olin remembers that he needs that room for tomorrow and unburies his browser window, but does not reload new (i.e., he is looking at the page generated a couple of hours ago). Oh yes, the Boesky Room will be fine. Olin clicks to reserve the Boesky Room at 3:00 pm tomorrow.|
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.
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.
Here's a step-by-step plan:
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
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
See the AOLserver Tcl Developer's Guide at aolserver.com for an explanation of
ns_sendmail API call
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
You now have a working system as envisioned by your clients. Congratulations.
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.
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.
We're going to shift gears now into a portion of the problem set designed to teach you more about Oracle and SQL.
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.
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
insert into foobar (the_key) values (1)statement
Repeat the above sequence but use ROLLBACK at the end in SQL*Plus instead of COMMIT.
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.
Changes/Suggestions: Tilmann Singer
©1999-2001 ArsDigita Corporation