Forum OpenACS Development: Eliminating 90% of DB-specific queries

I know that this is a slippery slope, but if we could have the query dispatcher automatically do this for us, we could probably eliminate 90% of the DB-specific queries.

Then, of course, the next step would be to automatically deal with things like 'from dual', and with 'now()' vs. 'sysdate', and similar things as well.

Is it worth going down this path?

/Lars

It is not entirely risk free but I think we would see fewer bugs because of this than we now see because we have two
parallel sets of queries when one would suffice.

However - date semantics are totally different.  I don't see much hope there until Oracle tracks the SQL99 date standard (PG is finally getting close to standard in this regard and 7.3 may finally be there)

The differences in the PL/[pg]SQL calls are much different than merely the package vs. non-package name convention.

In Oracle we make heavy use of default values and named parameters in function/procedure calls.  Can't have one without the other.  Default values are very useful IMO and I'd hate to see us give them up in our Oracle source just because PG doesn't support them.

And on the PG side at times we've had to disambiguate with explicit casts (rarely) or inventing new overloaded function defs (more commonly) when writing several shorthand forms of functions or functions that do slightly different things (like in the CR regarding file/lob/etc storage.)  The problem we have is that the bindvar hack in the PG driver surrounds values in quotes which means that PG has no idea what the type is and essentially picks the function that has the right number of params.  The bindvar hack must work this way, too, to give us the safety against SQL smuggling we seek (forces a type conversion with error checking.)

We thought about doing the name mapping (__ vs. .) when we first started porting code and these were some of the reasons we decided not to do it.

Now ... we did speak earlier about getting a lot of leverage by simply defining permission_p() for permission checking.  This function has no default params in Oracle therefore the named parameter style isn't necessary.  And everyone knows what the three parameters are for, anyway, since its use is so pervasive.

And getting rid of silly things like get_live_revision() vs. cr_items.live_revision helps too.

The CR has auto-SQL generation code that does stuff similar to what Tom's mentioning for content types, though it's not used outside the CMS.  I've thought about adopting the CR's view trick for general acs_objects.  That would make doing something like Tom mentions - autogenerating DML for inserts and updates - easier.

That would get rid of a lot of the PL/[pg]SQL entirely and, much like the new Tcl API for service contracts, would simplify code.

What do people think about trying to attack the problem by reducing dependence on PL/[pg]SQL?

The 'group type' creation code generates plpgsql and stores the necessary __new methods to create new groups of the type. Probably it does the same thing for oracle, so the differences between oracle and pg should be outlined in this code. The code isn't very general, and fails when your group type has more than a few attributes (unless you up the allowed number when you compile pg). My updated query-writer should produce code similar to what the group type code does, but it will write the result to a file instead of loading into the db.

To point out what Don has said: there is more than mapping __ to ., and the niceties of oracle should not be thrown away to make a query look like a pg query. We should be trying to make pg queries look like oracle queries. That requires some method of specifying defaults for attributes, specifying if an attribute can use a default, etc.

A fix should consider future development more than current code, since the current code already works. I wrote a package with over 500 files (only a few xql files, added before I finished query-writer), porting to oracle would only require .xql files for the select queries.

Here is an example my api used in the x-postgresql.xql files:

<querytext>
select [qw_write_fn supplier__new {
    party_id => :party_id
    name => :name
    address => :address
    phone => :phone
    email => :email
    contact => :contact
    creation_user => '1234'
    }]
</querytext>

The missing but required attributes are filled in with defaults. You can change the defaults by supplying a static value.

New development wouldn't use the .xql api, instead, you would use the tcl api:

set supplier(party_id) $party_id
set supplier(name) $name
set supplier(phone) $phone
set supplier(address) $address
set supplier(email) $email
set supplier(contact) $contact
set supplier(creation_user) "'1234'"

qw:new supplier supplier

Changing to oracle would require no changes in the tcl file, and no .xql files. I'm not recommending query-writer, just the concept of storing attribute information on an object basis and creating queries based on that information, not developer labor.

For instance being able to share outer join queries would remove a major source of pain when porting packages between PG and Oracle.

While we may not want to drop 8i support for the core in the near future, people who want to write new packages that support both PG and Oracle are free to decide to support whichever versions they want, IMO, especially if it simplifies the porting process (of course we have to support 9i before anyone can choose to not support 8i!)

Also ... should we just move ahead and for starters define permission_p() in each so we can at least remove this source of difference?  For 4.7?  I'm willing to personally go through and rip out all cr_item.get_live_revision()-style function calls for 4.7.

Just these two steps give us incremental progress in the right direction, no?

Though unfortunately we're still stuck with CONNECT BY vs. tree_sortkeys and many content queries will either use those or outer joins ...