PostgresSQL

[mezzin]

Well, I will definitely be interested in your progress! And I would think it would only be a matter of time as Zen Cart grows before others would pick up on it! There surely must have been inquiries in the past. I am sure those would be interested unless they have now become tied to something else. It could be most of Zen Cart's users simply do not realize the difference of what you are offering.

I just think that allmost nobody knows there is whole world of better databases than mysql. But there where questions about postgresql support in the past but most likely noboby had the time to update / implement it. (it was a lot of work a round 20 hours+ and counting.)

guess the main question is, are you doing this, such that mysql and postgresql become selectable modules, such that zen cart remains independently upgradable as it is improved so that the maintainers of Zen Cart do not have to upgrade two different versions? I would think THAT VERSION would be invaluable to them!!!

MySQL and PostgreSQL are now selectable modules so you can install either mysql or postgresql It is fully intergrated but it will take time to intergrate my updated source into de source tree of zencart. But we will see how it go. But first I need to get my own webshop going (that started this quest anyway) and release my code to zencart for review by the developers.

[leapofFAITH]

Thanks for all your hard work and expertise on this mezzin! I have been hesitant to commit to any cart, because I wanted postgre, so, I have been working on other aspects instead, dragging my feet not wanting to have to make a mysql based decision.

I still have some other things to finish, so I am hoping DrByte just happens to finish integrating your imporvements just about the same time I am ready for the next step. You couldn't have appeared on the scene at a better time.....as other than mysql, I really like what Zen Cart has to offer and it's direction.

Let us know how the implementation and your experience goes for you on your site.

Have a great day!

[ikekrull]

I'm on my second day of porting Zencart to Postgres - its pretty much all done, except for cleaning up queries in non-core parts of the system.

I haven't bothered to try and cleanly separate the two dbs as yet - i might try doing this, but the current architecture of zencart really doesn't lend itself to this well at all.

Basically what I have is a postgres fork of Zencart 1.3.7 - there is no way this sucker will run with mySQL now.

It's taken me 2 days of effort to get the installer, the admin interface and the public part of the site mostly working (90%+) - there are still a few oddball MySQL queries, and since the queries are sprinkled through the php UI its a pain to track them all down and fix them, but I havent found anything that can't be trivially rewritten for Postgres.

Still, for anyone else thinking of doing this, the major issues are:

Installation - the installer, and it's SQL needs to be hacked up a bit to get the schema loaded into the database. there is a mysql2pgsql script on the postgres dev site that does the majority of this, though it messes up INSERTs where php functions are passed.

I pretty much chopped the INSERT statements out of the mysql .sql file and used them instead of the converted INSERTs.

I think i also had to comment out a validity check for postgres INDEX syntax to be accepted by the installer query parser.

there are a couple of things to fix up with zone/country inserts but that was minor.

Postgres Sequences - MySQL supports returning the last inserted id of any table, with a single function taking no args - Postgres needs, at minimum, to be passed a table name to figure out this info, because it needs to call last_value on the appropriate sequence. I created a TABLE_NAME_SEQ define for each table (wrote a script to generate these from the define_tables.php and other table-definition php files, and rewrote the insert_ID() functions to take a sequence name as an arg. i then grepped through the codebase and replaced all calls to insert_ID() with insert_ID(<relevant_table_sequence>).

Lots of use of MySQL-specific functions, such as IF - there is a set of mysql compatibility functions on the postgres dev. site which helps out a lot with this, although the IF implementation in that script is broken with non-bleeding edge versions of postgres 8.1 and 8.2 (can work around by specifying multiple IF functions with different argument types, or by rewriting the IFs with postgres CASE statements.)

split_page_results needs to be trivially modified to handle the postgres 'OFFSET x, LIMIT x' instead of mysqls 'LIMIT x,x' syntax.

And thats it really - its far from the easiest DB migration i've had to do, but it's not too bad, all things considered.

I don't see how the current codebase would really deal with database independence in any useful way at the moment, there is just too much mysql-specific stuff sprinkled around to cleanly break it out.

So I wouldn't hold my breath for an end-user installable 'select postgres from the dropdown and install' version of Zencart anytime soon, but it is definitely possible for it to run happily on postgres - i wonder how the main zencart devs would feel about a user-supported postgres fork of zencart being hosted on the main site?

[mezzin]

ikekrull
I did allready and full db conversion for ZenCart. So that will save you some time i think.

There are some bugs still outstanding but it runs flawless on my server for over 6 months now in production :) And it doesn't break the MySQL support and all of the query that were mysql only are now cross database compliant :) or abstracted.

Also zencart developers are not so quick yet in picking up my changes

Regards,

If you want an copy send me an mail.

MeZZiN ([email protected])

[Rand McNatt]

Hi, mezzin, ikekrull.

Your posts on the ZenCart forum are a touch of deja-vu. It's a shame you have had to go to all that trouble to use PostgreSQL with Zen Cart, but I know just what you've been going through.

I'm a returning Zenner, having hung around the forums and working with v. 1.2 back in 2003. Back then my boss gave me a full month to look into adding an ecommerce system to our existing point-of-sale and appointment system. For a number of reasons, and especially because of it's supposed support for PostgreSQL, I chose ZenCart, spent a few weeks learning PHP, and dove in. Like you, I was disappointed by the level of support for pgsql, but actually managed to get a version of ZC converted and working pretty well and then posted the package in case anyone else wanted to use it. (I used the nickname 'rand', and that account is still there, but all my earlier posts are gone and now the email address I had is closed, so I can't get into the old account.)

My boss pulled me off to work on other projects (he still doesn't have an integrated system, which serves him right!) and I went on to other things.

Now, I'm out on my own and thinking again about adding appointment, POS, and production management functions to an existing ecommerce system, and Zen Cart still looks good...except for the lack of support for pgsql. For me, MySql is simply not an option.

Back in September you offered up your version of ZC+Pgsql. I'm hoping the offer is still open; it would save me a lot of work re-creating what I did back then. I'm also wondering if there's enough coding talent and interest in starting a Sourceforge project to track and convert Zen Cart for PostgreSQL, like the Kubuntu version of Ubuntu

Regards,
Rand

[toritaiyo]

Now, I'm out on my own and thinking again about adding appointment, POS, and production management functions to an existing ecommerce system, and Zen Cart still looks good.

Appointment functions... That is on a few people's wishlist

[drmoms]

Hi everyone. I too am looking for Postgres support. Has the current version of ZenCart been stable with the Postgres version?

[Merlinpa1969]

No,
Zen Cart is not running postgress yet

[mezzin]

Yes I have a full working stable version of zencart working on postgresql.

I didn't reply on some messages because I just got a little baby girl.

Fair Products this site is working for a year on a stable version of the zencart code converted to postgresql.

I will update the sourceforge project this weekend with a cleaned up version of zencart with postgresql support. It is fully backwards compatible with the less then standard implementation of the mysql sql.

Anyone willing to help to maintain it is most welcome.

MeZZiN
Sorry for the late replies all.

[drmoms]

Hi Mezzin! Congrats on your baby!