Forum OpenACS Development: Replacing SQL queries - non-dynamic SQL

Posted by Ben Adida on
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

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

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.

Posted by Dan Wickstrom on
With regards to the second issue, it seems that doing the bind variable emualtion in the pg driver would be pretty straight forward.  I've looked at the oracle driver, and it appears that we could lift the bind variable parsing routine.  We still wouldn't get the type checking, but at least it would offer us a relatively transparent porting mechanism.
Posted by Stefan Larsson on
One way to handle dynamic and non-dynamic queries would be to match each query against a pattern, possibly a regexp, or some extended format if that's not flexible enough. For each matched pattern there would be an "action" that rewrites the query in a postgresql compatible way. Either you could write a rule for each query in the source, but it should also be possible to write the rules more generic so they would be reused for many different queries.
If this is done in the postgresql driver you wouldn't have to modify any of the acs sourcecode.

Maybe something inspired from perl's regexp substitution syntax could be used, like for example:

s/select (.*) from (.*) where (oracle outer join-matcher)/select  from  where rewrite-join-for-pg()/

Sometimes it might be better to solve the problem once and for all by pitching in and help the postgresql guys improve their database instead of ugly hacks just for openacs.

Just an idea to consider, what do you think?