OpenACS is designed to support multiple RDBMS back-ends (currently, Oracle and PostgreSQL are supported). Unfortunately, despite the SQL92 and SQL99 standards, various RDBMS platforms implement differing dialects of SQL and extend the base language in various ways. In particular, the SQL standards do not specify a language for stored programs. Oracle and PostgreSQL (and all other popular database products other than MySQL) support built-in programmatic languages, but while similar in style and capability they differ in detail.

There are two basic approaches possible to support multiple RDBMS platforms:

1. Execute queries stored in Tcl script files, with the proper query chosen through if or switch statements.
2. Store the queries elsewhere, and provide a mechanism for choosing the proper query to execute depending on the RDBMS platform in use.

The first approach is simple, but queries are often large and when one considers the possibility of supporting three, or four, or even more RDBMS platforms in the future, would lead to large and somewhat hard-to-read Tcl files filled with if or switch statements and a lot of SQL code only of interest to users of an RDBMS platform that the reader doesn't use.

The second approach is initially a bit more awkward as it requires the creation of files to hold queries, information about the RDBMS platform the query works for, and a mechanism to efficiently choose and execute the proper query at runtime. Despite the drawbacks, we decided this approach was cleaner, long-term, than than the first approach.

Query files, by convention, are derived from the name of the Tcl file they are associated with. Query resolution, however, is by query name - provided to the db_* API call and in the query file itself.

Query file basics:

• tcl-file-name.xql By convention a file named in this manner contains queries which are standard SQL and which work with any standard-supporting platform. We call such queries generic queries.

• tcl-file-name-RDBMS-name.xql Files named in this manner contain queries that are specific to the given RDBMS platform.

For example, the file foo.tcl might have query files named foo.xql, foo-postgresql.xql, and foo-oracle.xql associated with it.