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...

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.

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.

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).

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 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.

Edmun:

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?