Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

PostgreSQL native support


Go to End
Reply


9 Posts   5497 Views

Avatar
Enlightened_One

Community Member, 18 Posts

6 February 2007 at 1:15pm

I would like to see SilverStripe CMS work with PostgreSQL right out of the box.

http://www.postgresql.org/

Avatar
Sigurd

Forum Moderator, 628 Posts

12 February 2007 at 10:42am

Would you mind explaining your rationale? I have used postgresql in the past and can understand how the two different databases (i.e. MySQL) run, but it is helpful for us to know what is going on in your head so that we can ensure the development roadmap is catering for everyone's needs.

Specifically, we need to ask questions like "is it better to build new feature X, which may be used by all users, vs building support for PostgreSQL, which does not really add much in terms of features, and only provides functionality for a subset of users".

One reason I can see for a need for this is for those with existing postgres database/applications; it would be easier to tie them to SilverStripe if it supported postgres natively.

Avatar
Sam

Administrator, 685 Posts

12 February 2007 at 12:38pm

Enlightened_One - do you know very much about the differences in SQL syntax between Postgres and MySQL? This will be the biggest hurdle to overcome...

Specifically:
* Create table and alter table (add index and add field) syntaxes
* Auto-increment index creation
* Selects / Joins / Where
* Table / Field delimeters (`backticks` in MySQL)
* string & date functions available, IN () clauses, INTERVAL clauses

If you could share your knowledge in these areas, we'd be able to work out how big a job it would be. Perhaps you could even assist in the development of a PostgreSQL plug-in?

Avatar
Enlightened_One

Community Member, 18 Posts

16 February 2007 at 1:14pm

Edited: 16/02/2007 1:29pm

Why PostgreSQL? I have in my head the ambitious concept of a ready to roll OpenBSD based PHP server that includes a CMS a.k.a. ~SilverStripe CMS pre-installed...or ready to be installed. I have chosen SilverStripe because of the BSD license. I am interested in using PostgreSQL because PostgreSQL is BSD licensed. I am impressed that you use lighttpd, as it is BSD licensed also. I had not heard of lighttpd until joining this forum.

OpenBSD ships with Apache 1.3.xx + lighttpd is a package of OpenBSD. So that part is easy. Configuration is another game. Anyone want to share running configurations of lighttpd running PHP with SilverStripe CMS on FreeBSD ????

I am calling this project oBAPP (OpenBSD-Apache-PostgreSQL-PHP)
BSD licensed (c) Gregory L. Magnusson 2007. If the SilverStripe CMS used PostreSQL as a database the SilverStripe CMS could be included in this package. I am reluctant to include MySQL, as that would involve licensing fees. What I see is a PHP server that will be (mostly) pre-configured providing a ready to roll content management system in a somewhat secure manner (in an OpenBSD jail). Ideally, this system would be pure BSD + PHP licensed.

The oBAMP project is similar in concept to the Ubuntu LAMP server.
http://blogs.ittoolbox.com/webdesign/php/archives/lamp-installation-on-ubuntu6061server-12373

I recognize that a project of this magnitude will take some time to complete. I have been doing research into methods of pre-configuring OpenBSD for some time. I am also intrigued by the FrenzyBSD project, a FreeBSD based forensic analysis toolkit that may easily configure into a FreeBSD-lighttpd-PostgreSQL-PHP server that would work as a live CD, on Hard-Disk or USB pen.

fBLPP (FreeBSD-lighttpd-PostgreSQL-PHP) BSD licensed (c) Gregory L. Magnusson 2007

Neither of the above projects has a web page. This is the first public utterance of these aforementioned projects. You see. I am working on installing the SilverStripe CMS to use as a CMS for these, and other, BSD projects. Am I ahead of myself? Yes, because before I begin working on PostgreSQL support I should really install a working copy of the SilverStripe CMS.

http://blogs.ittoolbox.com/webdesign/php/archives/mysql-5027-on-openbsd-38-14409

Am I willing to work on PostgreSQL support? Absolutely! I expect to learn a lot from SilverStripe.

I believe the answer to Database connectivity will come from the BSD licensed PEAR MDB2
http://pear.php.net/package/MDB2
http://pear.php.net/manual/en/package.database.mdb2.php

I will keep you posted with my progress. Thanks for your responses.

Avatar
Anonymous user

22 Posts

21 February 2007 at 1:11pm

Enlightened_One, thanks for your interest. I'm quite keen to get Postgres support for SilverStripe as I'm more of a Postgres fan than a MySQL fan. The other advantage of Postgres is that it's more of a professional RDBMS than MySQL and can be used in environments where DBA's care about things like robust triggering support, referential integrity that has been around for some time, etc.

A couple of years ago, I was looking into the PEAR database abstraction stuff and there were issues with performance so I shied away from PEAR at that time.

I don't think Postgres support would be a plugin because database access is throughout the SilverStripe system. The nice thins with SilverStripe is that, to my knowledge, all the database access is abstracted into SilverStripe-specific classes.

Harder issues exist around pieces of SQL that are scattered throughout the code. Those character strings are not tied to MySQL per se, but there's not assurance at all that those strings would be compliant Postgres SQL.

One approach could be like how you port C software to different flavours of Unix: Try it with Postgres, see what breaks, and do an inline "if DB_IS_POSTGRES do something diffeerent" for the situations where the SQL breaks. This approach has the advantage of being simpler and not having to deal with the whole abstraction issue, which IMO, would be a nightmare.

Avatar
Enlightened_One

Community Member, 18 Posts

21 February 2007 at 8:39pm

Perhaps you would be interested in the mafiasession login script code. I have just been reviewing this today, and I like the outline + the way that both MySQL and PostgreSQL are accessed by the script. This would be an interesting way of authenticating users. Your thoughts?

http://www.phpclasses.org/browse/package/3705.html

I must admit that I must review much more of the SilverStripe code before I can assess a realistic solution.

Avatar
Enlightened_One

Community Member, 18 Posts

23 February 2007 at 10:17am

PostgreSQL database access wrapper is a PHP5 class to establish database connection and execute SQL querries. BSD Licensed (c) Martin Maros .

http://www.phpclasses.org/browse/package/3462.html

class must Specifically:
* Create table and alter table (add index and add field) syntaxes
* Auto-increment index creation
* Selects / Joins / Where
* Table / Field delimeters (`backticks` in MySQL)
* string & date functions available, IN () clauses, INTERVAL clauses

class does
gets affected rows
gets results from select query
gets one col from a table
gets one row from a table
runs query - wrapper for ExecQuery
gets no of rows
gets last inserted id
gets results from select query
gets one var from a table
inserts rows
updates row
deletes rows
locks table fields for insert or update
sets SQL statement for IN items
sets SQL statements for NOT IN items
escapes string for safe use in a query

class can be downloaded from www.phpclasses.org
http://www.phpclasses.org/browse/package/3462/download/targz.html

Avatar
Sigurd

Forum Moderator, 628 Posts

23 February 2007 at 8:57pm

Greg, your project sounds neat, so thanks for letting us know why you were interested in postgres: it was certainly a (good) surprise... I was much more expecting a motivation along the lines of Postgres' features rather than license freedom.

By the way, my surname is also Magnusson :) So you might just get a bit more love from the SilverStripe team... :P

Go to Top