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.