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

[Greg M. <drk...@co...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jim Wilson wrote:
> <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.

The problem appears from developers using tools like phpmyadmin to
extract a .sql file for a module.  The tool is a great aid in the
development process but it has some drawbacks. phpmyadmin tacks on
quotes that make the create statement fail on postgresql.  Postgresql
and Oracle then treat these column and table names as mixed case.  If
the double quotes are removed, then they are created as uppercase in the
database.  You can send an undoublequoted table name like MyTableName
and both Oracle and Postgresql will use MYTABLENAME and find the correct
table.

Moreover, these phpmyadmin scripts then create index clauses between the
last column definition and the closing );  MySQl must create these in a
non-standard way.  If the index clauses are moved to table alter
statements after the create table statement, then that makes the .sql
file more portable.

The other problem is that Postgresql and Oracle allow only 32 chars for
a table name whereas mysql allows for 100 or so.  So what is the SQL
standard? Once you add a table prefix, and one for the module,mod_ to a
long table name along with its supporting PEAR autonumber table, then
the 32 char table name limit can be exceeded very quickly.  If Oracle
support is desired, then some of the phpWebSite tables create more than
one text column where a smaller varchar column would work.  Oracle
converts these mysql text columns to their long column format.  Oracle
limits one long column per table because the LONG column supports 2gig+
in characters.  Here's another post with examples
http://sourceforge.net/mailarchive/forum.php?thread_id=7340026&forum_id=34704
.

A 0.10.3 release is in the works.  I'd think that your patches would be
a good idea depending on how far the other developers want to take the
0.10.3 release.  I think I have read in these emails that Matt is
developing the next major release 1.0.0 or fallout on Postgresql.  That
in itself will provide your desired portability.  However, third part
module developers have to get the religion too.  Again I think your
patches would be great addition because the community would gain some
experience with postgresql installs on 0.10.3+ before fallout is released.

In addition, MySql may have added GIS support in 5.x.  I don't recall.
I think some slick business oriented phpWebSite modules could be
developed with PostGIS http://postgis.refractions.net/ .  GIS is not
just for business.  Some slick user modules could be developed too.  At
some point the PostGIS developers plan to submit PostGIS for OGIS
www.opengis.org/ certification.

Again, I'd say it would be a good idea but I do more documenting that
programming on this project.  What do the other developers feel?

Greg
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFD92Txxyxe5L6mr7IRAhPWAJ9joxFO4enCoZQAlF3IEkLTWcUkRQCgmLRS
g7HdrmXadfA5Y+RDmiq4CcY=
=SFkK
-----END PGP SIGNATURE-----