One of the toughest pieces of the OpenACS port will be the replacing
of Oracle SQL queries with Postgres queries. For non-dynamic SQL, we
can start solving this problem without touching the original source
code. This is crucial: if we can somehow port ACS to OpenACS without
touching a single line of original code, it means our OpenACS remains
Oracle-compatible.
The first step is to identify the query to be replaced. Every query is
now named, and can be uniquely identified by package, page, and query
name, or by package, tcl proc, and query name. We need to write a
simple way to identify that a query is in a particular page or in a
particular tcl proc. It might be a tad tricky, but probably not too
horrible. Once we know how to identify the query, where do we place
the replacement SQL, and how do we format it?
I'm all for defining some XML format that includes the query
identifier, the RDBMS name (postgres), the replacement SQL, and
potentially a description of the arguments (for example there might be
issues to define how an empty argument is treated, as NULL or as the
empty string). Is there someone interested in defining the XML DTD for
this if people think this is a decent idea?
The XML replacement strings could be stored in something like
pagename.sql in the same directory hierarchy as the TCL and ADP pages,
and in tclfile.sql in the same directory hierarchy as the file of Tcl
procs.
The second issue is the actual construction of the SQL given that ACS
now uses bind variables. Currently, the system uses bind variables
like ":name" or ":user_id". Postgres doesn't have bind variables, so
we can kludge them at one of two levels: the driver, or the DB API.
Lamar, Don, if you have some really good ideas about doing it at the
driver level, that could be interesting. I think it might be
preferable to parse the SQL at the Tcl level, and simply merge the
bind variables into the SQL string before execution. This would take
away the security gained by bind variables, but this security can be
regained by smart argument checking (by defining strong requirements
on variables in the XML spec of the SQL queries).
Comments? Ideas on making this a bit more real? Let's get this
discussion going.
