Forum OpenACS Development: E-commerce updates - Please comment

Collapse
Posted by Roberto Mello on
Hi all,

I've been working with the improved e-commerce package that Bart
worked on (thanks Berklee College!) along with Janine (thanks for the
payflow pro module). I've bumped into some things that will require
changes to the package, so I'm bringing them to the community:

1) PostgreSQL 7.2 has milisecond precision on timestamps now. This
breaks some functionality that relies on Tcl's clock scan command. The
way to cope with that is to change the queries and the Tcl proc. But
to change the queries to the efficient way (using epoch) will break
the Oracle version.

The problem is that I don't have time right now to fix the Oracle
version (which I already broken, I hear). But I commit to do so in a
couple weeks.

2) The ec_financial_transactions table has creditcard payments hard
wired. For what I'm working on, that's not a good assumption since
we'll take Paypal and other forms of payment. The way to solve that
would be with another table (say ec_payment_map) that would map the
transaction_id to the id of whatever payment method (e.g.
creditcard_id, paypal_account_id, etc.) This would require changes to
3 .xql files. Not much I think.

The ugly way to do this without modifying the e-commerce datamodel
would be by creating a separate table for
paypal_financial_transactions, almost duplicating what's in
ec_financial_transactions, with the exception of the creditcard_id.

So what do people think about these issues? Is credit card-only an
assumption that we want to keep?

Thanks in advance.

Collapse
Posted by Roberto Mello on
Let me expand a little bit...

On (1) the reason why it's more efficient to use epoch is because we can save a clock scan (ec_formatted_full_date (sp?) does a [clock format [clock scan]]).

Another way to fix this without modifying the Tcl proc (which would mean the Oracle version would not need to be modified) is to modify every PG query that uses ec_formatted_full_date to become date_trunc('second', column).

However, the Oracle version is _already_ broken, so we could just fix this for PG and then later on fix it during the Oracle cleanup process. That'd make it more efficient for everybody.

Collapse
Posted by Don Baccus on
How is the Oracle version broken, exactly?  We need this to be fixed ...

I'm really tired of the PG gang changing date semantics with each friggin' major release.

Collapse
Posted by Roberto Mello on
Don, honestly I don't know all the details. I just know it's broken because Bart told me so and because of a quick examining of the oracle datamodel.

For example, the service contract definition files are absent from the ecommerce/sql/oracle/ directory. I am comitting to fixing the Oracle version as soon as I finish this project though. I have some Oracle installations at work that I can use.

Regarding the date semantics in PG, I couldn't agree with you more. I wish they had made the milisecond precision thing an option, because I think most projects really don't need it.

Collapse
Posted by Don Baccus on
OK, it's not completed for Oracle, then, that's cool, that much I knew.  It's great that you're going to pick this up and finish it ...

I've made my opinion public in the past as to the competency of the person who does datetime stuff in PG so I'll just zip my lip this time.

Collapse
Posted by Randy Kunkee on
Regarding #1, PG 7.2.1 allows specification of the precision, so you can use a datatype of timestamp(0). Selections from columns so defined will have no precision on them, just like previous versions of Postgresql. Perhaps this is where the change should be made instead of fixing code in other places.
Collapse
Posted by Don Baccus on
That's useful ... thanks!
Collapse
Posted by Roberto Mello on
Randy,

That's perfect! If no one objects, I'll use that instead. Thanks for the tip.