Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » PostgreSQL native support

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

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

Page: 1 2
Go to End
Author Topic: 5386 Views
  • Enlightened_One
    Avatar
    Community Member
    18 Posts

    PostgreSQL native support Link to this post

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

    http://www.postgresql.org/

  • Sigurd
    Avatar
    Forum Moderator
    628 Posts

    Re: PostgreSQL native support Link to this post

    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.

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: PostgreSQL native support Link to this post

    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?

  • Enlightened_One
    Avatar
    Community Member
    18 Posts

    Re: PostgreSQL native support Link to this post

    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.

  • Anonymous user
    Avatar
    22 Posts

    Re: PostgreSQL native support Link to this post

    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.

  • Enlightened_One
    Avatar
    Community Member
    18 Posts

    Re: PostgreSQL native support Link to this post

    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.

  • Enlightened_One
    Avatar
    Community Member
    18 Posts

    Re: PostgreSQL native support Link to this post

    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

  • Sigurd
    Avatar
    Forum Moderator
    628 Posts

    Re: PostgreSQL native support Link to this post

    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...

    5386 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.