Forum OpenACS Development: The query dispatcher and Oracle version dependency

1: The query dispatcher and Oracle version dependency

Posted by Peter Marklund on 01/23/03 06:39 PM

I stumbled on the fact that acs-automated-testing had specified Oracle version 8.1.7 (looked ok and innocent to me) and the query dispatcher would silently refuse to load those queries. I f people don't disagree terribly I would like to change the query dispatcher to issue an error message when a query is not loaded because of an unsupported database version.

All oracle xql files except three have version set to 8.1.6 and this is the highest version that the query dispatcher will accept. Are we staying with the requirement that any query in OACS requires at most 8.1.6 to be included?

2: Re: The query dispatcher and Oracle version dependency (response to 1)

Posted by Don Baccus on 01/23/03 11:31 PM

Well ... we don't run with 9i yet (should that be on our must-have list for 4.7?).

The idea behind not loading the queries was that Oracle and/or PG (mostly PG) might screw us by breaking backwards compatibility, meaning we'd need to provide separate queries for the two different versions.

In practice we've ended up dropping support for older PG versions other than the most-recently-obsolete one and haven't run into backwards compatibilies that have required us make use of the query dispatcher's support of versioned queries.

That was the thinking.

I'm curious to see what others think. What if we threw an error if a query was actually executed that has blank SQL? That would catch cases you're not going to catch at query file load time (i.e. when there's no query at all because someone forgot to add their query files to the CVS tree ...)

3: Re: The query dispatcher and Oracle version dependency (response to 1)

Posted by Jun Yamog on 01/24/03 03:50 AM

I encountered this once, although in postgres. My query files was pg 7.2 and it took me a few minutes why the deployed site was not fully running since the pg on the remote server was 7.1.3.

I think keeping versions is kinda hard. Most people would not even port to Oracle or Postgres. Why not just drop versions in the code and put it into the release notes. Like "OACS ver x.x requires/tested on PG x.x or Oracle x.x". The queries that is not supported on the db would likely throw an error which should make the developer read the release notes or find out that his current db version does not support XYZ feature. The latest CCM code I believe does not check the db version, just oracle or pg. Not sure though, but there is no indication it looks for the version.

Or maybe just on bootstrap it checks the version, I think someone already did something like this. And the query dispatcher does not check the versions anymore.

4: Re: The query dispatcher and Oracle version dependency (response to 1)

Posted by Peter Marklund on 01/24/03 09:50 AM

Just to clarify what happens - the query dispatcher silently refuses to load queries at bootstrap time that have a version number higher than what is returned by [db_version] (the value of which is hard coded in db-init-checks-oracle.tcl). When executing the query at page request time you get an error message saying that the query is missing. It is not obvious that this problem arises because of the version number in the xql file. This is why I want to issue an error message in the log file at bootstrap time.

Here is the comment where the database version is hardcoded:

# DRB: I've got the SQL to pick the version to drop in later...what we really want,
# though, is Oracle's "compat version" number and I'm not sure how to get it (it is
# reported as 8.1.0 during the Oracle installation process)
nsv_set ad_database_version . "8.1.6"

Can we replace this with a query for the real version? If we did, the problems with the 8.1.7 version numbers would go away for me. However, I'm sure those queries would run just fine with 8.1.6 so we should probably change to that. The fact that all version numbers for Oracle queries are set to 8.1.6 seems somewhat arbitrary to me. 99% of the queries would probably run fine with much older versions. It seems that what we currently have is a system wide requirement of 8.1.6. However if that is the case, why not remove all version numbers from the query files and instead check the db version number on bootstrap?

What seems to make the most sense to me is to have db version requirements on a package level. A user of a package won't really care if some queries require 8.1.1 if others require say 8.1.5. It will always be the higher number that matters.

5: Re: The query dispatcher and Oracle version dependency (response to 1)

Posted by Bruno Mattarollo on 01/24/03 02:56 PM

A way to check for the Oracle parameter "compatible" is to check the sys.v_$parameter view:

select name, value from sys.v_$parameter where name = 'compatible'

The only problem is that you need rights over the "SYS" tablespace for this.

6: Re: The query dispatcher and Oracle version dependency (response to 1)

Posted by Don Baccus on 01/25/03 12:14 AM

Well ... my comment's right ... what we really want is the "compatibility" version. Bruno's comment is disgusting, why do we have to have rights on SYS to read it? Grrr ... how annoying.

Perhaps just removing the version info from query files and letting the APM tell you if the package doesn't support your db version would be simplest and sufficient in practice?

A cheap hack is to just check the "8" and the "1" and ignore the "6" or "7" ... my guess is that the compat level more or less corresponds to "8.1" :)