The Need for A Different Approach to DB extendibility

 Original Text by Frank Bergmann

• The following posting is a kind of high-level requirements document explaining the business that finally lead to the creation of FlexBase.

Hi,

We're currently facing an old question again: How to build a large scale OpenACS application (composed by several modules) that can be customized so that it suits more then one customer? Sounds easy, but it isn't if you want to avoid a copy-paste-modify approach.

Let's take a simple example to explain the requirements: Let's consider user management. OpenACS provides several standard user management screens with the fields "first_names" and "second name". However, people in Spain have two first names and two second names, such as "Juan José Ruiz Mart�nez". And this time we are working for a demanding customer who requires us to do it "their way" and to use their design standards. So we actually have to include the four pieces of the name in one line so that the users screen needs to look like:

Name: 	[First1] [First2] [Second1] [Second2]
Username:	[Username]
Email:	[Email]
Password: [Password]
URL:		[Url]

However, another customer from the US may requires us to add a field for a middle name such as in "Frank W. Bergmann" and a third customer requires us to add a second email address for the private email (just to put an example).

Option 1: The Current Copy-Past-Modify Approach

The standard approach in the OpenACS community (and also in many other Web-based content & community tools) for such a situation is to take the OpenACS code as a base and to extend it, adding the necessary fields "manually".

This works pretty well for the first and maybe for the second customer, but after that you're getting a holy mess of different versions that are difficult to maintain. Imagine that you need to upgrade to the next version of OpenACS or that you have developed an improvement for one of the customers that might be useful for the others as well.

Extensible Architecture Requirements

But if you start thinking about how to unify the user management code for all customers, you immediately get to the question of how to extend the unified code to accommodate the different requirement and you get to a list of quite ugly requirements:

• Adding new fields to a business object:
  We want to be able to add any number of new fields to a user or another object without touching the "core" code. These new fields should support validation and referential integrity such as all other fields.
• Integrating new packages:
  We want to be able to add new packages to the system, so that they are integrated with the rest of the system. Let's consider adding a "bookmark list". We may want to be able to show a list of bookmarks on the users main page, even though the users page didn't "know" about bookmarks before. And please remember, we don't want to touch the TCL or ADP code, because they are common to all of our customers.
  Also, we want to add a link "add a bookmark" in another part of the page and we want to add a new item in the global site menu such as "Bookmark Management".
• Customized layout and design:
  Customers are picky, so we want to be able to adapt to all of their design preferences, particular in terms of form layout. Colors and stuff are covered by CSS stylesheets anyway.

Taking into account the overall TCL/ADP structure of OpenACS pages, we can translate these requirements into technical issues that we have to tackle:

• Customizing ADPs:
  How can we add dynamicallynew pieces of code to an ADP page to display new contents or links?
  How do we dynamically add new fields to a form or new columns to a list view?
• Customizing TCLs:
  How can we dynamically add business logic to TCLs?
• Customizing SQLs:
  How can we patch SQL statements to include new fields from new "extension tables" or dynamic attributes? How do we sort the results according to an extension field that didn't exist at the time when we wrote the SQL?
• Menus and Navigation:
  How can we dynamically adapt the navigation to reflect the presence of new packages?
• Links and References:
  How do we link from "core" pages to pages in new add-on packages that didn't exist at the time of writing the "core" pages?

Option 2: Extensibility Using User Exits

So let's come back to our user registration example in order to explore how "User Exits" could help us to build a single page that would serve all of our fictitious customers.

The ADP Page

Here we could add several "user exits" to the ADP page that would look like this:

<%=[ad_call_proc_if_exists TCL_library_routine] %>

We could then write a TCL_library_routine implementation for a specific customer that would add the right HTML code in order to create the new fields. Also, we could call ADP includes on an similar "if exists" base to include pieces of content.

The TCL Page

The TCL page has to provide the ADP page with additional business logic for the new fields. So we could use same "user exits" trick and call a TCL library routine at the end of the TCL if it exists.

The SQL

This is more complicated. Let's imagine that the new user name fields are implemented via a "user_extension_table". How do we "join" the contents of this table into our exiting SQL?

One option is to use SQL "views". The TCL page would do a "select * from my_users" where "my_users" is an SQL view that by default only performs a "select * from cc_users". However, our extension module could now overwrite this view with a new version that joins cc_users with the user_extension_table. This approach may cause problems when there is more then one package adding fields to a user, but it's simple and straight-forward.

Menus, Navigation, Links and References

We could again use the "user exits" to implement flexible menus and references.

Pros & Cons

The advantage of this "architecture" is that it's quite simple, transparent and easy to understand. It is actually already being used in the request processor using the ad_call_proc_if_exists routine. Also, it provides a simple "migration path" to migrate an existing hard-coded system towards a more flexible one without rewriting the whole code. However, there may be "extension conflicts" between different modules that extend the same business object, and the code may become very ugly ("spaghetti") with the time.

Option 3: Store Everything in the Database

The current ]project-open[ architecture stores all variable elements in the database, such as menus, links, "components" (ADP includes), table columns and form fields. Table columns include the TCL code to render a table cell content and they include the "order by" clause if a user wants to sort a list by a specific column. Here is the comlete documentation: http://www.project-open.org/doc/intranet-core/

Pros & Cons

This is a very straight-forward approach that allows for great flexibility and performance. An extension module can just add a new column to a table and define some extra_select, extra_from and extra_where pieces for the SQL clause. However, the approach requires a considerable initial effort and storing TCL code in the database isn't really an elegant solution. So this is why we are considering alternatives in a project that is not related to ]project-open[.

Option 4: Extending ad_form and Templating

The last option that we explored is based on the OpenACS templating system and ad_forms. These modules use a list of fields in order to control the rendering of forms and tables. Normally, these lists of fields are defined statically as part of the TCL page as in the following example:

ad_form -form {
   menu_id:key
   {name:text(text) {label Name} {html {size 40}}}
   {label:text(text) {label Label} {html {size 30}}}
   {url:text(text) {label URL} {html {size 100}}}
   {sort_order:text(text) {label "Sort Order"} {html {size 10}}}
   } [...]

However, the definition of these fields could be moved out of the ad_form procedure call into a variable. And once it is within a variable, we could overwrite this variable in the case that an exension module has added more fields in a database table:

set field_list {
   menu_id:key
   {name:text(text) {label Name} {html {size 40}}}
   {label:text(text) {label Label} {html {size 30}}}
   {url:text(text) {label URL} {html {size 100}}}
   {sort_order:text(text) {label "Sort Order"} {html {size 10}}}
}
if {[check_the_database]} {
   set field_list [get_field_list_from_the_database]
}
ad_form -form $field_list [...]

This "architecture" would allow for a simple and convenient default configuration defined in the TCL page, while allowing for full extensibility by extension modules.

Another shortcoming of ad_form is its current HTML layout inflexibility. ad_form renders the form fields as a vertical list by default. There is no easy way to say that first_name and second_name should go together into the first line of the form. However, ad_form allows for custom rendering "form templates", so that we could tackle this issue by introducing new field parameters for field positioning (absolute horizontal/vertical or relative line/column) and by creating a customized version of a form template to implement something similar to a "layout manager" in Java.

Also, there are facilities in ad_form to handle dynamic fields via acs_attributes and the OpenACS SQL metadata system. However, the implementation of the acs_attributes feature is not very "transparent" (you don't understand easily what it happening) and doesn't seem to be commonly used. The only place that I have seen is group_type maintenance, and this is an incomplete implementation error with an error when trying to use default values.

Pros & Cons

ad_form and templating could allow for a flexible architecture without storing TCL code in the database. It would provide a very elegant solution if the integration with acs_attributes would work in real-world applications.

However, I personally don't like the "hide as much as possible" philosophy of ad_form, and I have lost many hours debugging relatively simple issues due to the lack of transparency and documentation.

How to Proceed?

How should we go forward? I currently believe that the third option (extending ad_forms with or without acs_attributes) is the most promising one, but I see a lot of risks related to it. Is there an interest from the community and the package maintainers to support our efforts? How should we organize the development so that our modifications could be incorporated into OpenACS?

We are exposed to a certain pressure in order to come up with a viable plan within a few days...