<%= [openacs_header "Query Dispatcher"] %>
<h2 align=center>Query Dispatcher</h2>
<%= [openacs_menu] %><p>

<h3>Goals</h3>

OpenACS 4.x should be, to some degree, <b>database-independent</b>:
<ul>
<li> New RDBMS support should be implementable on a per-package basis without editing existing code.
<li> Packages should be storage-aware, and potentially multi-storage capable.
</ul>

<h3>Storage-Aware Architecture</h3>

Each ACS classic package contains storage-dependent pieces, including:
<ul>
<li> a data model for one RDBMS, usually Oracle 8
<li> default queries using the new database API, written for Oracle 8
</ul>

In order to safely extend the ACS package structure, an <b>OpenACS package</b> will include additional information, including:
<ul>
<li> data model files tagged by RDBMS type and version
<li> RDBMS type and version of default queries
<li> additional queries for other RDBMSs.
</ul>

If this additional information isn't included in a package's meta-data, the OpenACS package manager will assume:
<ul>
<li> the single data model provided is built for Oracle 8
<li> the default queries are written for Oracle 8
</ul>

Because we don't want to bias OpenACS for one database or another, and
because it is quite confusing that some queries are inline while
others are not, OpenACS will aim to quickly move all queries
<b><i>outside</i></b> of the Tcl code, including the Oracle queries.

<h3>Data Models</h3>
<p>
The data model of a package in ACS Classic 4.x is stored in <tt>package_root/sql</tt>. OpenACS 4.x will change this convention to allow for multiple data models. The <tt>package_root/sql</tt> location will be split into multiple directories, one per supported RDBMS. The directories will be named according to the RDBMS (and potentially version) they refer to. No attempt will be made at finding common data model components across RDBMSs, as this seems to complicate the situation more than simplify anything. However, it is expected that the SQL creation files are <b><i>exactly</i></b> parallel for all RDBMSs supported by a single package. While these specifications may not assume such a file hierarchy at this point in time, they may change to make such an assumption at a later date.
</p>

<p>
Thus, a directory <tt>package_root/sql</tt> might look like:
<ul>
<li> <tt>package_root/sql/oracle/</tt>
<li> <tt>package_root/sql/oracle-9/</tt>
<li> <tt>package_root/sql/postgres</tt>
<li> <tt>package_root/sql/postgres-7.0</tt>
</ul>
</p>

<p>
Such a structure would mean that an Oracle 8 installation would use the default <tt>oracle</tt> directory, while an Oracle 9 will use the specific <tt>oracle-9</tt> directory (this supposes that Oracle 9 has some special features). Similarly, PostgreSQL 7.1 will use the default <tt>postgres</tt> directory, while someone might choose to make the system also compatible with PostgreSQL 7.0 by adding a specific data model for it in <tt>postgres-7.0</tt>.
</p>

<p>
In order to make this work in the APM architecture, the <tt>.info</tt> file should include RDBMS information. The <tt><%= [ns_quotehtml "<file>"] %></tt> currently contains no additional information. The OpenACS package specification will add an RDBMS tag (<b><i>NOT an attribute!</b></i>) as follows:
</p>
<center><pre>
<%= [ns_quotehtml "<file type=\"data_model_create\" path=\"sql/oracle-9/acs-create.sql\"><rdbms><type>oracle</type><version>9</version></rdbms></file>"] %>
</pre></center>
<p>
 For now, this information will be redundant with the naming scheme selected above. Again, this <b><i>may change</i></b> at any time. Thus, both the naming convention and the <tt>.info</tt> additions must be respected.
</p>

<h3>A Full Query</h3>
A query in ACS Classic 4.x is currently tagged uniquely by Tcl script or Tcl procedure. Since it is relatively easy to determine in which Tcl page or procedure a DB call is being made, we have a means of uniquely identifying queries in a system-wide manner. We must also attach all required meta-data to a query. A <b><i>Full Query</i></b> is thus defined as:

<ul>
<li> the SQL of the query
<li> the bind variables expected (which may be implicit in the query)
<li> the RDBMS(s) the query is designed for
</ul>

The best way to specify a "full query" is via a human-readable, easily-parsable format. Using XML for this purpose is quite appropriate. A full query would then be defined as:

<pre>
<%= [ns_quotehtml "
<query name=acs-kernel.www.index.select_foo>
<querytext>select * from foo where foo_id= :foo_id</querytext>
<bindvars>
<bindvar>foo_id</bindvar>
</bindvars>
<rdbms><type>postgresql</type><version>7</version></rdbms>
</query>
"] %>
</pre>

<p>
The <tt><%= [ns_quotehtml "<version>"] %></tt> component can be left out, or the <tt><%= [ns_quotehtml "<rdbms>"] %></tt> can be left out altogether. The Query Dispatcher will pick the <b><i>most specific query</i></b> possible for a given setup. Thus, it is possible to specify a generic SQL92 query that will apply to all RDBMSs. Only when an RDBMS isn't compatible with that generic query will there need to be a more precisely specified query for that particular RDBMS. This allows developers to focus on SQL92 queries, and to work on RDBMS-specific query as a "diff" from the standard. (The fact that this encourages the use of compliant RDBMSs is not a bad thing, either). 
</p>

<p>
The full-query and query dispatching mechanism need to be well-abstracted for possible future enhancements in the means of storing and organizing these full queries. The following API will allow this:

<ul>
<li> <tt>db_fullquery_fetch <i>fullquery_global_name</i></tt><br>
This retrieves a FullQuery data structure using the global name. The FullQuery data structure is then used by the following API calls to obtain further query information.
<p>

<li> <tt>db_fullquery_get_text <i>fullquery {rdbms}</i></tt><br>
Retrieves the SQL query text from the FullQuery data structure. If the RDBMS parameter is provided, the text for that RDBMS is returned. Otherwise, the current RDBMS is used. If no query exists for this RDBMS, this throws an exception.
<p>

<li> <tt>db_fullquery_compatible_p <i>fullquery {rdbms}</i></tt><br>
Checks if a FullQuery is compatible with a given RDBMS. If no RDBMS parameter is provided, the FullQuery is checked against the current RDBMS.
<p>

<li> <tt>db_fullquery_get_bindvars <i>fullquery</i></tt><br>
Returns a Tcl list of bind variable names for the FullQuery.
<p>

<li> <tt>db_current_rdbms</tt><br>
Returns the current RDBMS, which can be detailed using the following two procedures.
<p>

<li> <tt>db_rdbms_get_type <i>rdbms</i></tt><br>
Returns the type of a given RDBMS.
<p>

<li> <tt>db_rdbms_get_version <i>rdbms</i></tt><br>
Returns the version of a given RDBMS.
<p>

<li> <tt>db_rdbms_match <i>rdbms_instance</i> <i>rdbms_pattern</i></tt><br>
Returns true if <tt>rdbms_instance</tt> matches the <tt>rdbms_patterns</tt>. <tt>rdbms_pattern</tt> might be something like oracle with no version, while <tt>rdbms_instance</tt> might be oracle 8.1.6, in which case the procedure returns true. If the rdbms_patterns is oracle version 9, though, the procedure will return false.
<p>
</ul>

<h3>The Simple Dispatching Process</h3>

The Query Dispatcher must be able to locate the various versions of
each query in order to create the FullQuery data structures. For this,
each FullQuery must have a global name and, from that global name, a
means of locating and loading the query.

<h4>Naming Scheme</h4>
The naming scheme for a FullQuery is specified as all-lowercase, as follows:
<center>
<pre>
package_name.section_name.chunk_name.query_name
</pre>
</center>

where :
<ul>
<li> the <b><tt>package_name</tt></b> is the name of the OpenACS
package to which this query belongs. Packages are uniquely named in
the system, and anything belonging to the kernel will be tagged
<tt>acs_kernel</tt> for the purposes of this naming scheme.
<li> the <b><tt>section_name</tt></b> is either <tt>tcl</tt> or
<tt>www</tt> depending on whether the query is in a Tcl procedure or
web page.
<li> the <b><tt>chunk_name</tt></b> is either a Tcl procedure name, or
a web page path.
<li> the <b><tt>query_name</tt></b> is the actual query tag in the
<tt>db_</tt> API call.
</ul>
</center>

<h4>Locating FullQuery Information</h4>

FullQuery information will be stored as XML text files. In order to
provide the same flexibility as Tcl procedure files and independent
web pages, FullQueries will be stored, by convention, in files that
parallel the directory hierarchy of the code they pertain to.  

<p> 

For example, in package <tt>acs-subsite</tt>, the file
<tt>www/register/deleted-user.tcl</tt> will be complemented by the
file <tt>www/register/deleted-user.postgres.sql</tt> and potentially
by the file <tt>www/register/deleted-user.oracle.sql</tt>.

<p>

The Query Dispatcher will look at any <tt>.sql</tt> files in the
<tt>tcl</tt> and <tt>www</tt> directories, and load the FullQuery
information from those files. The actual names of the files
(<tt>deleted-user</tt> and <tt>.oracle</tt> extension) don't matter, as
the fully-qualified name of the query and the RDBMS-compatibilities
are defined in the FullQuery XML itself. The file naming is a
convention to make it easier for developers to get at the right query
text.

<h4>Storing & Refreshing FullQuery Information</h4>

<p>
The Query Dispatcher will load up all queries and store them in an nsv
array at AOLserver/OpenNSD boot time. The file location of each query
will also be stored in the nsv array so that, at a later date, the
query information can be easily located and reloaded on a
per-query-file basis. We assume that, if the definition of a query
changes place (which shouldn't happen when users are following the
proper naming conventions), an AOLserver/OpenNSD reboot will be
necessary to properly reload the queries.
</p>

<p>
During development time, it is perfectly acceptable to reparse the
queries on every DB query. The first version of the Query Dispatcher
will not bother with much caching, in fact. The first production
release, however, will provide two means of caching:
<ul>
<li> all queries loaded up only once at server startup (i.e. no
ability to reload queries, good only for production sites)
<li> queries reloaded when the source file has changed
</ul>
</p>


<h3>Dynamic SQL Strategies</h3>
<i>under development</i>

<%= [openacs_footer] %>