Forum OpenACS Development: Query Dispatcher

When a script is executed, and it reaches a query, the query dispatcher checks to see which DB is being used and picks the correct cached query to execute. Is this correct?

Yes, I think so. Here's how I think it works... A tcl script (my_script.tcl contains a query with the name my_name. The query dispatcher notices this and checks which DB is installed and finds that it is my_database. It then checks if there is a query named my_name in my_script-my_database.xql. If so, it runs that query. If not, it checks if there is a query named my_name in my_script.xql, where generic SQL92 queries are kept. If so, it runs that query. If not, then it runs the query that is in the my_script.tcl file which is most likely oracle-specific, assuming that the package was born at aD.

So the porter's goal is to put oracle-specific queries in my_script-oracle.xql, postgresql-specific queries in my_script-postgresql.xql, and SQL92 compliant queries in my_script.xql. Remember, though that putting a SQL92 compliant query in my_script.xql may not be enough if postgresql or oracle doesn't understand that specific SQL92 syntax. In those cases, you need to port the SQL92 query to the noncompliant database and put it in that file. For the most part, I've found that the Query Extractor does this work for you.

Does the query dispatcher ignore that query completely and use the queries from the .xql files?

I think the query dispatcher only comes back to the .tcl file if it can't find a database-specific xql query or a SQL92 xql query.

For instance, if I needed to create a new content item, I would create a query in the survey-create-2-oracle.xql which did the oracle call for content_item.new function. And in the survey-create-2-postgresql.xql I would make a call to the content_item__new function. Is this correct?

That is correct. The one issue that I've been occasionally running against is that I don't know much about SQL92 syntax, so for the queries that I create, I simply put the oracle-specific stuff in my_script-oracle.xql and the postgres specific stuff in my_script-postgresql.xql. It may be possible that the postgres query may be SQL92 compliant and thus would be able to go in my_script.xql, obviating the need for a postgres-specific query.

Although the outcome is quite similar, let me describe how the
Query Dispatcher actually works to clear up any confusion.

When a package is loaded (at server startup time), its Tcl files
are loaded as well as its Query files (the .xql) files. The specific
naming of the query files (-oracle or -postgres) doesn't really
affect the Query Dispatcher: it's just loading up all query files as
spec'ed in the package's .info file.

Thus, when a query is loaded from a .xql file, it is checked (at
load-time! Not at execution time) against the current running DB
(Oracle or Postgres for now) for compatibility. If there is already a
compatible loaded query with that name, the queries are
compared for levels of compatibility: the more specific query is
kept, and the more generic query is discarded.

Thus, as soon as your OpenACS instance is up and running, it
has a cache of the most-compatible queries available for the
specific RDBMS on which it is running.

Then, when a call is made to execute a query, the Query
Dispatcher looks up the query name in the cache. If *no*
compatible query was loaded for that query name, then the
default SQL provided at the Tcl layer is used. HOWEVER, you
should expect that this behavior will eventually stop: we want to
remove all SQL from the Tcl code. Thus, even if the query is very
simple and SQL92 compliant, please move it out into a .xql file.

Now, even though the naming of the specific .xql file is currently
not used to determine DB compatibilty (instead, we use the
compatibility information in the XML of the query file), you should
still respect the convention of putting Oracle queries in -oracle,
Postgres in -postgres, and SQL92 in the generic file. At some
future date, we may begin to infer meaning from the filename
(this is mentioned in the spec!). So, please respect the
convention.

Finally, Gilbert, your description of how you would go about
porting the survey package is correct. These are good
questions, keep them coming to help make everyone's porting
job easier!