and -oracle.xql files only. It doesn't create a -postgresql.xql
file. So am I correct in saying that when you are using Oracle, the
outer join defined in the -oracle.xql file takes precedence over the
outer join defined in the .xql file? Thanks.
Oracle on the other hand, uses a non-compliant syntax for outer joins, so it is necessary to have a separate version in -oracle.xql that is only used when oracle is the back-end db.
So in this case PG and any pther SQL92 compliant RDBMS will use the generic query present in the .xql file, while this SQL92 form will be overridden by the version in the *-oracle.xql file if you're running the Oracle version of OpenACS.
The only time we need queries to appear in both *-postgresql.xql and *-oracle.xql query files is when there's no SQL92 equivalent to the query or where neither RDBMS supports the SQL92 standard equivalent.
It's unfortunate, life would be easier if 8i had supported the SQL92 syntax and aD had been able to follow it, a big chunk of our porting job wouldn't even exist if that had been true!
I am totally open to future packages implemented by the user community
be restricted to 9i, though, in particular packages that begin life as
a PostgreSQL package. This lowers the port-to-Oracle effort for such work and makes it more likely that authors will support both RDBMS's out of the box.
I'm even more curious about the claim that coalesce will outperform nvl. These are both function calls, after all.
So a pointer to documentation supporting this argument would be deeply appareciated, if it isn't too much trouble.
As far as query file choosing based on RDBMS version ... I've thought about it. Mostly I'd like to avoid it if possible. If the query's there for "old Oracle" syntax, which we need for 8i, I don't mind using it for 9i unless there's a compelling reason not to (performance, apparently, according to your information, but I'd like confirmation before we spend time worrying about it).
coalesceis new. http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90237/analysis.htm#27503
Performance is the least of my concerns, as obviously in this
case it makes little difference. The OpenACS project has changed though, it is no longer just a port of Ad code, it is the whole project, and it supports two databases (so far).
Right now thinking just about 8i works and is the easiest thing to do, but as time goes on it will make life harder by forcing needless conversion to legacy syntax. Surely the less datamodels and queries diverge, the better.
If it's as easy as versioning xql files, great. If not...
Our database checking code isn't very smart about versioning issues at
the moment. We choose not to tackle this in the beginning as we had a
lot of questions about the viability of the queryfile approach, hacking on the APM/bootstrap/install code to deal transparently with different RDBMS engines, etc.
So we took the position that we are supporting base versions of an RDBMS and assume upward capatibility. With Oracle, this means 8i of course, and we're dependent on 9i being upward compatible, i.e. adding
"coalesce" hasn't led to "nvl" being stripped out.
I would like to maintain this simplicity for the time being, at least until we get our first OpenACS 4.x release out and more experience with it in the real world. Adding support for different RDBMS versions would be centralized work, much as adding the skeleton to support multiple RDBMS versions was. So I'm not worried about delaying any such support causing a lot of extra work down the line, if we later decide we need it.