Forum OpenACS Q&A: v3.2.4 ecommerce custom fields functionality broken

Just a FYI to anybody thinking about using the v3.2.4 ecommerce module... the custom field functionality is broken. The problems stem from, amongst other things, the SQL used--it assumes Oracle syntax and functionality.

E.g., "alter table xxx add (...)" instead of "alter table xxx add ..." to add custom columns to ec_custom_product_field_values, etc. And, the error recovery code assumes that the DB has working drop column functionality, which PG7.1 doesn't have. Sigh...

Oh, I see... the code to drop and create new audit-related triggers on ec_custom_product_field_values, etc. in ~/admin/ecommerce/products/custom-field-add-3.tcl has not been rewritten for PG.
Want to take a shot and pass them off to Roberto?  Good practice for 4.x porting!  And since ecommerce3 is our initial ecommerce for 4.x, too (though ported to the 4.x environment) your changes will be useful  there, too.
I see the problems. Besides the "add (foo type)" problem, there's also
the constraint, which needs to be in a separate alter statement (in 7.1).

If you fix it and send me a patch, I'll commit it this afternoon. If
not, I will try do do it later today (after I finish the upgrade script).

OK, I'll send you the patches once I'm done. I'm still working on them... there are quite a few minor changes needed here and there. For example, ecommerce-widgets.tcl and ecommerce-utilities.tcl need to be patched for data-type changes, from Oracle's number to PG's float. The data-model needs minor mods too since one of the related triggers bumps into the 31 character limit of a standard PG make.

Big question though--what's the recommended workaround for PG's lack of an "alter table <...> drop column <...>" command? The standard procedure of copying a table to temp, dropping the original table, renaming the temp table, etc. is going to be an awful mess in this situation.

Make sure you do your patches against the CVS tree. There have been
changes to several portions of ecommerce, and some of your corrections
may have been applied already.

As to the alter table drop column issue, I don't know of another
approach besides the awful mess path. Anyone know of a better way (tm)?

DON'T USE FLOAT!  These need to be exact numbers.

Use numeric rather than number.  I think they use something like number(9,2) which can be directly translated into numeric(9,2).

Numeric is the SQL92 exact fixed-point arithmetic type.  Oracle supports both names but for some reason aD uses number exclusively (probably because of its prominence in the Oracle docs).

OK, I'll shun float...
Uh, sorry to be so dumb about CVS, but... if I've been working on an exported copy of OpenACS rather than a checked-out copy (don't ask me why--long story!), is there any way to sync up what I have now with what's on Sourceforge for the purposes of doing a diff on the ecommerce hacks I've been putting in?
What do you mean by "exported copy"? One way you can do it is have a checked-out copy in another directory and do a diff -r (if you are doing lots of files-dirs).
What I mean is that I've made patches to sources that were exported rather than checked out of the CVS repository. I could check out a fresh set and make changes, but I'm hoping to be able to avoid re-patching a separate source tree. The export was done recently. Exports don't have any CVS information in them.
Check out a fresh copy, then make the diff against your patched version. Then send me the diff (gzipped so the lines won't get munged on the e-mail).

Actually i think you don't have to port the drop column statement.

If you wrap both column creations (in one table and in its audit table) , and there's something wrong, the transaction will be aborted and none will be created, so you don't need to drop them explicity.

Could anybody correct me if i'm wrong?

Anyway, i've ported it, but i haven't tested it deep enough. I can send a copy of custom-field-add-3.tcl to anybody interested.

Some days later, i will clean it and send it to Roberto (i'm in the middle of a project right now).

What i haven't been able to port is the "alter data type" statement. I think that's not possible in PostgreSQL. Does anybody know about it?


OK, good point... I've sent Roberto some patchs yesterday (minus any fixes for the drop column bit). Had intended to take a closer look at the code to examine the issue, but I guess you've done this already--thanks!!!