Forum OpenACS Development: Outer joins and .xql files

I noticed that the Query Extractor puts outer joins into the .xql
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.

To expand a bit on Dan's explanation, you're right - the db-specific (Oracle, in this example) query in a .xql file overrides the query in the generic .xql file if it is present.

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.

Thanks for the quick responses!

But previous versions don't, and thus far I think we want to continue supporting 8i, so that those who have it and aren't interested in upgrading for 9i if they can avoid it can still use OpenACS.

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 curious about the claim that case will outperform decode (well, not entirely in this case as decode's a function call - but it could be handled as a internal expression by the query compiler and I'm surprised it isn't, speaking as a grizzled compiler writer myself).

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).

Eventually we'll probably drop 8i support for the core and our packages, and there may well be packages supplied by others that don't support 8i, which will be fine by me.

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.