[Phpwebsite-developers] non-MySQL Database support in phpWebSite

[Jim W. <spi...@us...>]

<note>
This was submitted to the list a while back and for some reason (probably a local issue) it did not go through.  Here it is again with a few minor edits.
</note>

Does there continue to be any interest in making phpWebSite more portable in regards to databases?

Originally I was drawn toward phpWebSite for its features and a statement somewhere that it would work with PostgreSQL.  On
closer examination I found that it did not,  but read somewhere else that it would with minor modifications.  As it turned out the
modifications were minor, but involved changes on many lines of code.

At this point I'm trying to decide if I should redo those changes against the latest CVS or if I should just move on and try and
find a project that is more inclined to really support Postgres (and others).

The decision for me really hinges on whether or not the project is willing to accept these modifications.  I've been using a
modified version phpWebSite on several sites for a couple years now with pgsql backends,  backporting some security related
patches as they
came up.  My goal is to run something more in line with phpWebSite releases that uses PostgreSQL.


>From memory, the following problem areas needed to be addressed and were included in the modificatoins I made:

1).  PostgreSQL folds SQL identifiers (table names, column names, index names, function names, etc) to lower case.  This causes a
problem mapping to case sensitive variables in phpWebSite that were coded with camel case.  There seems to be a few ways to
address the problem.

One way (the one I have been using) is to make all the column names and corresponding php class variables lower case.  Basically
I added some underscore characters where CamelCase was previously used (e.g. "AdminEmail" becomes admin_email).  The
main problem with that approach is the fact that the SQL standard is to fold identifiers to UPPER case.  So while this technique
makes both PostgreSQL (that folds to lower) and MySQL (that doesn't fold at all) work,  it would present problems for folks using
Oracle or other systems.

An alternative approach would be to quote all identifiers.  This can bring some of its own issues (e.g. MySQL not in ansi mode).

There are likely other ways of handling this issues,  but I'm willing to implement a fix that makes sense to people.

2).  The function Database::sqlGetIndex(tablename) is a problem because it it uses the "show index" command from MySQL.
  There is no easy fix here.  We either have to restrict modules to always using a primary key column called "id" or
have a way for modules to register primary key column names, either as a property in the data class (e.g. PHPWS_Item) or in some
global location like a database table.

In our local copy I have worked around this with a PostgreSQL method,  but I would lean toward just changing all the modules so
they use "id" for primary key and eliminating the use of sqlGetIndex in the standard modules.

3).  The "Archive" function in phatform uses mysql specific utilities.  Currently we are not using archive on our
sites.  The data dumps are automated daily and the forms so far are fairly static.  In general users are discouraged from messing
with form data and as a result they pretty much limit usage to getting web form submissions via email.  

In order to support multiple databases, we could probably just code recode this function for individual database dump utilties
and simply return a message when the configured database isn't supported for the archive option.


My best recollection is these were the only areas of trouble for getting PostgreSQL to work.  My apologies for the long email,
but I wanted to be specific about my intentions.  Basically I'm volunteering to submit some work,  but before proceeding I need to
find out if the folks at Appalachian State feel that what I'm describing is compatible with the current goals the phpWebSite
project.


Best regards,

Jim Wilson