The ACS events service is primarily intended for use by writers of application packages and other service packages. The service allows developers to specify and manipulate relationships (possibly recurring) between a set of intervals in time, an activity, and an arbitrary number of parties. An activity can be associated with an arbitrary number of ACS objects.
The package doesn't provide for any interpretation of events, leaving that up to the applications that use the service. In particular, the package assumes that permissioning, and the related concept of approval, will be handled by the application. Similarly, notification is also the responsibility of the application (but probably via another service package.) Likewise, the package provides no UI support.
Possible application domains include include calendaring, room reservation, scheduling, project management, and event registration.
The requirements break the functionality into four main areas: events, time intervals, activities, and recurrences. The package meets the requirements for each of these areas in the following ways:
Events: The service creates a new subtype of acs_object: acs_event. It creates an auxiliary table for mapping events to parties. It provides an API for manipulating and querying events and their associated time interval sets, activities, recurrences, and parties.
Time Intervals: The service creates tables for storing time intervals and sets of time intervals. It provides an API for manipulating and querying time intervals and time interval sets.
Activities: The service creates a new subtype of acs_object: acs_activity. It creates an auxiliary table for mapping activities to objects. It provides an API for manipulating activities, their properties, and their associated objects.
Recurrences: The service creates a table for storing information on how an event recurs, including how the event recurs and when it stops recurring. It provides an API for manipulating recurrence information and recurring events. This includes a function to insert event recurrences in such a way as to reasonably limit the amount of information stored in the DB for a particular event. This is done by only partially populating the recurrences for certain events. The service also provides a view which simplifies querying to find partially populated recurring events that need recurrences added to the DB.
There are number of historical considerations surrounding the design of recurring events. Much of the current design can be traced pack to the original ACS 3.4 Calendar Package design, though the design has been cleaned up, modified to fit with the new events data model and slightly expanded.
One key consideration is exactly how recurring events are supported. There are two main choices. One choice is to insert only a single row for each recurring event, regardless of the number of times it will recur. This row contains all the information necessary to compute whether or not that event would recur on a particular day. The alternative is to insert a row for each recurrence.
I favored the second approach for the following reasons. First, one tradeoff is time vs. space. Computation, particularly if it might need to be done in Tcl and not solely in the database, is relatively expensive compared to storing additional information in the database. In many cases, the only information that will need to be stored for recurrences is the date and time of the recurrence.
I think it may be faster in Oracle even with a stored proc, at least with the month view and possibly the week view as well. This is because with 1 row per recurrence, the month and week view queries can pull all the relevant items out at once and can take advantage of the index on the start_date column to optimize the query. With the stored proc, it would be necessary to iterate over each day (up to 42 in the month view), calling the check repeat proc for each base repeating item who's repeat_until date was still relevant, and then effectively constructing the item to be displayed.
Another reason is that the first approach, to insert only a single row, seems to require a significantly more complex design. Thus the design, implementation and eventual maintenance time would be greater. It becomes even more complex when you allow exceptions. Now you need to maintain a separate table of exceptions and it becomes necessary to check through the exceptions table every time the check repeat proc is called. It the worst case, every recurrence is an exception, so you're essentially back to 1 row per recurrence, plus all the added complexity of using the check repeat proc.
This is not an unreasonable possibility and is in fact how Sloan operates. Each class is represented as a recurring item and it is very common for each instance to have a different set of files attached to it.
However, there are drawbacks to this approach. First, it will be more difficult to handle events that recur indefinitely. Second (but related) is that safeguards will need to be put in place to prevent pathological (accidental or intentional) cases from swamping the database.
In the ACS 3.4 Calendar Package, this was partially resolved in the following way. Users are limited to looking no more than 10 years in the past or 10 years in the future. (Actually, this is a system parameter and can be set more or less restrictive, but the default is 10 years.) This seemed reasonable given that other systems seem to have arbitrary, implementation driven limits. Yahoo and Excite have arbitrary limits between about 1970 and 2030. Palm seems to have no lower limit, but an upper limit of 2031.
The 4.0 ACS Events service doesn't enforce a particular policy to prevent problems, but it does provide mechanisms that a well-designed application can use. The keys are the event_recurrence.insert_events procedure and the partially_populated_events view.
insert_events takes either an event_id or a recurrence_id and a cutoff date. It either uses the recurrence_id, or gets it from the event_id, to retrieve the information needed to generate the dates of the recurrences. When inserting a recurring event for the first time, the application will need to call insert_events with a reasonable populate_until date. For calendar, for example, this could be sysdate + the lookahead limit.
It is the application's responsibility to determine if additional events need to be inserted into the DB to support the date being used in a query to view events. The application can do this by querying on partially_populated_events, using the date in question and any other limiting conditions to determine if there are any recurrences that might recur on the date in question which have not been populated up to that date. To insure reasonable performance, the application needs to be clever about tracking the current date viewed and the maximum date viewed so as to minimize the number of times this query is performed. The application should also pick a date reasonably far in the future for insert additional instances.
Another historical consideration is the choice of values for event_recurrence.interval_type. The original choice for the 3.4 calendar was based on the Palm DateBook which seemed fairly inclusive (covering both Yahoo Calendar and Excite Planner) though it didn't capture some of the more esoteric cases covered by Outlook or (particularly) Lotus Notes. The Events service maintains the original choices, but adds an additional choice, 'custom', which, when combined with the custom_func column, allows an application to generate an arbitrary recurrence function. The function must take a date and a number of intervals as arguments and return a new date greater than the given date. The number of intervals is guaranteed to be a positive integer.
For the days_of_week column, the representation chosen, a space-delimited list of integers, has a number of advantages. First, it is easy and reasonably efficient to generate the set of dates corresponding to the recurrences. insert_events takes each number in the list in turn and adds it to the date of the beginning of the week. Second, the Tcl and Oracle representations are equivalent and the translations to and from UI are straightforward. In particular, the set of checkboxes corresponding to days of the week are converted directly into a Tcl list which can be stored directly into the DB.
Since this is a low level service package, there is no direct competition.
Because this is a service package, tradeoffs were made only in areas of interest to developers. Indeed, the main design tradeoff was made at the very beginning, namely that this would be a narrowly-focussed service package. This had consequences in the following areas:
To simplify the package as much as possible, a number of possible features were left to be handled by other services or by the applications using the events package. This includes controlling access to events via permissions, providing an approval process, and providing support for notification. permissions app dependent, approval via workflow, separate notification service package
There was one significant, fairly complex feature that was included, namely the support for recurrences. It could have been left to the application developers or another service package. However, because the 3.4 Calendar package already had a model for recurring calendar items, it was straightforward to adapt this model for the rest of the events data model. The advantage of this is that this code is now in one place with no need for applications to reinvent the wheel. It also means that there is a consistent model across the toolkit.
Much thought was given to the needs of applications most likely to use this service, such as calendar, events, and room reservations. This has led to a well defined API which should be reusable by most applications that are concerned by events.
Because the API consists of well defined PL/SQL functions, it should be fairly easy to build a test suite using the PL/SQL testing tools.
The data model and PL/SQL API encapsulate the four main abstractions defined in the ACS Events service: events, time interval sets, activities, and recurrences. At present, there is no Tcl API, but if desired one could be added consisting primarily of wrappers around PL/SQL functions and procedures.
This is the main abstraction in the package. acs_event is a subtype of acs_object. In addition to the acs_events table, there is an acs_event_party_map table which maps between parties and events. The acs_event package defines new, delete, various procedures to set attributes and recurs_p indicating whether or not a particular event recurs.
Because time interval sets are so simple, there is no need to make them a subtype of acs_object. Interval sets are represented with one table to represent time intervals, and a second table which groups intervals into sets, with corresponding PL/SQL packages defining new, delete, and additional manipulation functions.
This is the secondary abstraction in the package. acs_activity is a subtype of acs_object. In addition to the acs_activities table, there is an acs_activity_object_map table which maps between objects and activities. The acs_activity package defines new, delete, and various procedures to set attributes and mappings.
Since recurrences are always associated with events, there seemed to be no need to make them objects. The information that determines how an event recurs is stored in the event_recurrences table.
The event_recurrence package defines new, delete, and other procedures related to recurrences. The key procedure is insert_events.
A view, partially_populated_events, is created which hides some of the details of retrieving recurrences that need to populated further.
This package does not provide a UI.
There are no parameters for this package.
If the system presently lacks useful/desirable features, note details here. You could also comment on non-functional improvements to the package, such as usability.
Note that a careful treatment of the earlier "competitive analysis" section can greatly facilitate the documenting of this section.
| Document Revision # | Action Taken, Notes | When? | By Whom? |
|---|---|---|---|
| 0.1 | Creation | 11/20/2000 | W. Scott Meeks |