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

Collapse
Posted by Tom Jackson on

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.