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

Collapse
Posted by Don Baccus on
Jeff, you're working from a false premise ...

Just because 612 query files don't contain named parameters doesn't mean they don't contain outer joins or date arithmetic or decode or other things that don't map directly into a common query form.

Now .. Oracle 9i *finally* supports standard SQL 92 outer join syntax.  If we can pick a date for retiring support of 8i we can then use common queries for these.

I don't know what the state of AQL-compliant date support is in Oracle but date arithmetic can't be done in a common query at the moment (there are special cases that can be done commonly)

Oracle 8i supports a limited form of the CASE statement so some decodes can now be removed and queries shared with PG.  But they don't support the most useful form which allows full removal of decode (CASE WHEN value THEN result WHEN value THEN result ELSE result) They only support the CASE WHEN boolexpr THEN result ELSE result.

At least that's all I could get to work in 8.1.6 and I just tested about a week ago.  8.1.5 didn't have any form of CASE.

Now ... the new calls can already be replaced with package_instantiate_object.  I'm in favor of defining the parameter strings required  by that proc right in the PG datamodelnow, if anyone wants to start down that path . The parameter string mechanism Dan implemented allows for the specifying of default values so the Tcl API to create an object is exactly the same for PG and Oracle. dotLRN (and forums and notifications) use it and it's slick.

This is a subset of what Tom's been trying to accomplish but it's very handy, though going the VIEW route is personally appealing to me.