PostgreSQL instead of MySQL

I read in one post (http://forum.opencart.com/viewtopic.php ... t=45#p3907) that it is possible to choose PostgreSQL for the database engine. However, since I am not able to find any other hits, I suspect that it is only a potential future capability.

I ask because I am configuring a small store that will have pretty low volume, but also needs to be low-maintenance, and be able to automatically manage customer_groups (used as membership levels) based on purchases of said memberships.

I have much more experience with PostgreSQL, than either PHP or MySQL. However, it wasn't too difficult to implement the necessary logic with MySQL's SQL/PSM. An INSERT in order_history would TRIGGER an UPDATE in the customer.customer_group_id where the order status was complete and the SKU matched a specific pattern.

This worked reliably when I tested it on my local virtual server, but when I tired to migrate it to the production server I discovered that the bargain hosting company has triggers disabled even though they do have MySQL 5.

Since, it turns out that the ISP's PostgreSQL server does not have the same limitations imposed, I thought I
could simply migrate over to PostgreSQL. Is sticking with the same ISP and pursuing PostgreSQL as the database engine promising, or should I really just upgrade the hosting plan, possibly to another ISP?

Thank you in advance if someone can point me in the right direction!

Thanks,
Randall

Take a look at the system/database/mysql.php class
You will also notice a postgre.php class. The file is empty. But perhaps if you know postgre you can use the mysql class and just change the syntax for postgre

Then in the config file you can change
define('DB_DRIVER', 'mysql');
to
define('DB_DRIVER', 'postgre');

I have implemented the changes needed to get it installing and working properly on PostgreSQL 9.2.

If anyone has interest in it, I'll gladly share the code, or if I get an invitation to submit my changes, I'll modify them so that they're properly integrated in code that is functional both for MySQL and PostgreSQL interchangeably. Shoot me an email to ziggy-at-veganmall-com-au.

I will continue implementing enhancements as time goes by, hopefully making it more portable in the future, I'll possibly work on support for MS-SQLServer too.

both PostgreSQL and MariaDB are gaining momentum after Oracle bought out SUN/MySQL.
we already started to see famous CMS or web application offering support to PostgreSQL and I thank you for this contribution.

I've prepared a patch and a copy of my site (including the install folder) for you to download from my website:

http://rbem.org/opencart-1.5.x-pg.patch
http://rbem.org/opencart-1.5.x-pg.tgz

The .tgz file is a copy of my website, so it may contain some of my graphics. If you have any trouble with it let me know and I'll try to assist you.

Cheers!

Ziggy (ziggy@rbem.org)

veganmall wrote:I've prepared a patch and a copy of my site (including the install folder) for you to download from my website:

http://rbem.org/opencart-1.5.x-pg.patch
http://rbem.org/opencart-1.5.x-pg.tgz

The .tgz file is a copy of my website, so it may contain some of my graphics. If you have any trouble with it let me know and I'll try to assist you.

Cheers!

Ziggy (ziggy@rbem.org)

Hi veganmall!
It is really good job and you my hero today!

But it seems that you forgot to place one function in to the dump you included!
public.digest(text,text)::text
What does this function gets & makes?

Thax again.

Which OpenCart version is compatible with Postgres because it is not working for me with the latest version 2.3.0.2 and also I had tried your zip file that was attached but not working?

Can you please provide me a zip file which works with latest version for postgres?

Thanks in Advance

demping wrote:

veganmall wrote:I've prepared a patch and a copy of my site (including the install folder) for you to download from my website:

http://rbem.org/opencart-1.5.x-pg.patch
http://rbem.org/opencart-1.5.x-pg.tgz

The .tgz file is a copy of my website, so it may contain some of my graphics. If you have any trouble with it let me know and I'll try to assist you.

Cheers!

Ziggy (ziggy@rbem.org)

Hi veganmall!
It is really good job and you my hero today!

But it seems that you forgot to place one function in to the dump you included!
public.digest(text,text)::text
What does this function gets & makes?

Thax again.

create extension pgcrypto;
CREATE EXTENSION