Wouldn't it be wonderful if version 4.7 of E-commerce supported Postgres? There has been some work done with this on 4.6:

  1. store.module hack for Postgres which works around some problems that are present in CVS too, as far as I can see.
  2. translated schema 1
  3. translated schema 2
  4. translated schema 3 along with a description of some problems that occur when using it.

As a start, I made another schema conversion for Postgres, based on the file ecommerce.mysql from CVS. This file covers all the included modules, I think.

I think the schema is pretty much solid. One thing I'm unsure about is how to deal with MySQLs unsigned property on int columns. There is CHECK (col>=0) in Postgres, but I don't know if it's of any use to add this to every table. I added it in some places where I suspect that it's particularly important that the value is not negative. Someone who's more familiar with E-commerce should review it.

Like many before me, I encountered problems when trying to actually use E-commerce after applying the schema. The problems are like before related to pgsql:s stricter handling of data types. In particular, the modules generates queries that use int:s instead of booleans ("WHERE 1") and strings instead of ints (intcol='').

So, if some E-commerce developers would like to take a look at this, it'd be beautiful!

CommentFileSizeAuthor
#16 coupon_4.7_pgsql_patch_1.txt2.42 KBsammys
#14 ecommerce_pgsql_4.7_2.patch.txt2.88 KBsammys
#10 ecommerce_pgsql_support_4.7.patch.txt22.53 KBsammys
#1 ecommerce_cvs_mysql_to_pgsql.diff9.77 KBzoo33
ecommerce.pgsql8.27 KBzoo33

Comments

zoo33’s picture

zoo33 commented
StatusFileSize
new ecommerce_cvs_mysql_to_pgsql.diff9.77 KB

Here's a diff between ecommerce.mysql and ecommerce.pgsql for those who are curious about what changes I made.

zoo33’s picture

zoo33 commented

I searched for the string "WHERE 1" in all the modules and found three matches in store.module. They could simply be replaced by "WHERE 1=1" i guess, but I'm thinking that maybe they could be eliminated completely.

The first match reads:

... WHERE 1 AND p.nid = stp.nid ...

I don't see why we couldn't just remove "1 AND". Why is it there in the first place?

The other two matches are trickier. They look like this:

... WHERE 1 $w ...

I guess the idea is that $w can either be empty or contain a WHERE clause that begins with "AND". So in order to remove "WHERE 1" we'd have to examine $w first and remove the first "AND". Or make sure this is taken care of when $w is set.

But I suppose the easiest solution is to simply replace "1" with "1=1" in these two places.

That would solve one of the problems with E-commerse and PostgreSQL. The other problem is harder to solve: integer columns that are set to ''. I may have a look at that later.

Your thoughts, please!

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented

I'm rejoicing to see the work you've put into this. I got too busy to continue troubleshooting on my own, but I will test these modifications soon. I don't use MySQL, so I was wondering, do the changes cause problems to MySQL users?

zoo33’s picture

zoo33 commented

The changes I suggest shouldn't introduce any problems with MySQL, but we'd have to test it to know for sure.

I haven't had time to work any more on this, but I plan to make at least parts of ecommerse work with Postgres as I need it for myself. Unless someone else does it first.

proxyissues’s picture

proxyissues commented

For the WHERE 1 issue.
Replace with WHERE true

MySQL is okay with WHERE true, so it is safe if you switch the database again.

sammys’s picture

sammys commented

Is there a reason you used CONSTRAINT declarations for the primary keys? Postgres understands PRIMARY KEY syntax.

Would be good if this was incorporated into CVS. What needs to be done for this to happen?

gordon’s picture

gordon commented

This will not get into 4.7, it is just not going to be do in time. What I need is a patch which updates all the *.install files with the creation of the pgsql schema, and then once e-commerce is tagged for 4.7 I will put it into cvs.

sammys’s picture

sammys commented

i'm willing (and able) to do that. I feel pgsql support is required sooner than later. i.e I have to get it done in the next 24 hours or thereabouts. :)

sime’s picture

sime
any/any
Location Melbourne
commented

There threads seem to overlap quite a bit. Could postgre dudes please review and close redundant thread?
http://drupal.org/node/49294

Thanks, sime

sammys’s picture

sammys commented
Status: Active » Needs review
StatusFileSize
new ecommerce_pgsql_support_4.7.patch.txt22.53 KB

This patch is a complete one for ecommerce using postgresql. This will ONLY work for 4.7 or later versions and this will be kept up-to-date from 4.7 onwards. Gordon will be enabling postgres support as *EXPERIMENTAL* for 4.7 giving everyone the ability to use it. Provided it goes well postgres will be added as a supported platform for 4.8 and later.

I'm the contact for postgres maintenance. Contact me through drupal.org or, once my website is up and running, through that.

--
Sammy Spets
Synerger Pty Ltd
http://www.synerger.com

zoo33’s picture

zoo33 commented

Great work! I haven't tested it but I glanced through the patch and I just wanted to say: well done!

I'm not sure if you mean that the patch will be commited or if it will live as a patch for the whole 4.7 release cycle. I don't think that the patch would affect MySQL users in any negative way – the queries are just more general/standard this way. I say commit! (After testing of course.)

sime’s picture

sime
any/any
Location Melbourne
commented

It will be committed into HEAD, and probably be perfectly useful, but it's a bit late to branch it to 4.7. That's my reading of gordon's comments anyway.

gordon’s picture

gordon commented
Status: Needs review » Fixed

I have commited this to cvs. This will be in 4.7, but I am going to mark this as experimental in the readme.

sammys’s picture

sammys commented
Status: Fixed » Needs review
StatusFileSize
new ecommerce_pgsql_4.7_2.patch.txt2.88 KB

Here is a patch for the recent move of stuff from ecommerce core into contrib plus the removal of the ec_payment_adjust.

gordon’s picture

gordon commented
Status: Needs review » Fixed

I have commited this small update.

Thanks @sammy

sammys’s picture

sammys commented
StatusFileSize
new coupon_4.7_pgsql_patch_1.txt2.42 KB

Patch to fix query errors in the coupon code when using pgsql. Basically, postgresql requires all fields not used in aggregate functions to be declared in the GROUP BY clause.

sammys’s picture

sammys commented
Status: Fixed » Needs review

Forgot to flip the status

gordon’s picture

gordon commented
Status: Needs review » Fixed

applied to cvs. Thanks.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)