Home› General support

postgres support?

vshah Member
in General support
Does anyone know how much trouble it would be to modify ZP to work with pgsql instead of mysql? Database functions seem to be located in functions-db.php.

Alternatively, can I run without a DB? As far as I can tell, the basic functionality of ZP is filesystem-based. The DB stuff is only used for caching certain meta-data, right?

Thanks

Comments

  • trisweb Administrator
    Yeah, I've considered using a better DB library for the data handling so one could use postgres, MSSQL, oracle, firebird, etc. I'll add it to the list. If you just want to hack it, I'm pretty sure the queries are simple enough that they'd work with postgres, you'd just have to modify all the connection/query functions in functions-db.php as you said.

    Alternatively, yes, you could actually run without a database. Just make the connection/query functions always return true or something. Oh, I take it back, it needs some heavy hacking of classes.php not to look for data in the db... but that's fairly easily done as well.

    I've thought about adding the option, just for simplicity's sake. Maybe sometime in the future...
  • vshah Member
    I'll try getting it to work with pgsql. The environment that I have to work with involves: php5 and pgsql. Hopefully I can get something working.

    Thanks.
  • vshah Member
    So, converting the table initialization in setup.php and converting mysql functions to pgsql functions in functions-db.php wasn't bad (though I might not have done things completely correctly since my knowledge of DBs is poor). The problems are occuring whne trying to convert the SQL statements in classes.php. My SQL knowledge isn't good enough to figure out what the code is intended to do and why postgresql complains about it. e.g.
    classes.php line 352:
    `$entry = query_single_row("SELECT *, (date + 0) AS date FROM ".prefix("albums").

    " WHERE folder=".mysql_escape_string($folder)." LIMIT 1;");`
    doesn't make postgres happy:

    postgreSQL Query ( SELECT *, (date + 0) AS date FROM albums WHERE folder=test LIMIT 1; ) Failed. Error: ERROR: operator does not exist: timestamp without time zone + integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

    but damned if I know why.
  • trisweb Administrator
    That's a hack for mySQL to get the date as a number and not a date string... you'll have to find out how postgres returns dates and modify the "myts_date" function in functions.php (or maybe make a new one for postgres).

    If you finish all this and get it working, go ahead and send me the code and I'll see about adding it with a switch.

    The better way is still to use a database abstraction layer though, so I'll still plan that for 2.0.
  • sir Member
    Has there been any progress on this? I've just found zenphoto through google, and it looks great. I'd love to replace the stupidly bloated Gallery2 with zenphoto - the only problem is that I've got postgresql instead of mysql.
  • trisweb Administrator
    There has been no progress since then, no. I would really like to abstract the db classes still. I believe a ticket is open, so I will target it for 1.3.

    SQLite would be a hit as well I think :)
  • sir Member
    Great!

    There's no real need to write your own db abstraction layer by the way - several other GPL projects use similar things to allow usage with multiple databases. FluxBB / PunBB for example has one for Mysql, Mysqli, Postgresql and Sqlite (http://fluxbb.org/trac/browser/fluxbb/branches/fluxbb-1.3-dev/upload/include/dblayer)
  • spidersilk Member
    Count me as another person who'd be interested in seeing this happen. I had to beg one web host to install MySQL on my client's hosting account (the host in question are PostgreSQL specialists, so MySQL is not available by default) so that I could set up a ZP gallery for them, and it would have been so much easier just to have been able to use PostgreSQL in the first place.

    Generally, pretty much any time I build a PHP site from scratch instead of using an available open-source CMS or something, I go with PostgreSQL instead of MySQL because I like working with it a lot better... But that does pose problems if it becomes necessary to add on a feature later that ends up requiring MySQL, as in this case.
  • acrylian Administrator, Developer
    It's on the list. I have to admit that I don't know nothing about PostgreSQL nor do I have access to it.
  • gau242 Member
    Hi there,

    I too would love to have PostgreSQL support in Zenphoto. Any news here?

    Yours,
    Alex
  • acrylian Administrator, Developer
    No and it is not planned. First, we don't have access to any PostgreSQL database (no host I know has this and Zenphoto is aimed at standard shared host primarily), second, no real demand is known besides this old topic. Since our time is limited we have to choose what we do.

    In any case to be considered at all it would require a ticket on the issue tracker but no guarantee. We once tried to support SQLite but that being to limited in functionality to cover what we need we had to skip it.

    Also if you would like to contribute on that take a look at /zp-core and the file named functions-db-xxxx.php. Since we are set for MySQL we of course use MySQL queries all over the place in the core.
  • sbillard Member
    If you did want to do some work on this you could probably find a copy of the SQLite implementation in an older Zenphoto release. This would give you a good idea of how to map a foreign database into Zenphoto.

    Unfortunately, as acrylian has said, SQLite was simply too "lite" to support Zenphoto functionality.
  • acrylian Administrator, Developer
    I have no knowledge about PostgresSQL but I read somewhere that at least the latest (9.x?) has a MySQL compatibility mode.
Sign In or Register to comment.