I'm working on an internal project and for reasons that get into business
type logic of which I'm not privy to, SugarCRM [1] seems to fit the bill
perfectly.
Well, almost perfectly. For other reasons that at the time I fully agreed
with but now am having a difficult time remembering, we decided against
using MySQL [2] and felt that PostgreSQL [3] was the better database engine
to use. But SugarCRM wasn't written to use PostgreSQL, but MySQL.
"Oh, it can't be *that* hard to port," says I. "SQL is SQL, right?" says
I. "PHP has been around long enough to support database engine
abstractions, right?" says I.
Nope.
It seems that to use MySQL, you use a bunch of specific MySQL calls, all
prefixed with "mysql_" to do your SQL queries. You open a connection using
mysql_connection(), then mysql_select_db(), then mysql_query() to make the
actual queries. Using PostgreSQL, it's pg_connect(), which handles both
connecting to the actual database engine *and* the database you want to
query, then you call pg_query(). The same mess exists for all other
database engines that PHP laughingly "supports."
Oh sure, PHP does have a database abstraction layer, but if you read up on
it [4]:
PDO provides a data-access abstraction layer, which means that,
regardless of which database you're using, you use the same
functions to issue queries and fetch data. PDO does not provide a
database abstraction; it doesn't rewrite SQL or emulate missing
features. You should use a full-blown abstraction layer if you need
that facility.
PDO ships with PHP 5.1, and is available as a PECL extension for PHP
5.0; PDO requires the new OO features in the core of PHP 5, and so
will not run with earlier versions of PHP.
Sad to think it took *five @#...@#$@ major revisions" to get this
functionality. Even sadder to think that PHP, being the "scripting language
du jour" that it is, means that applications are pretty much targetted
towards a particular version of PHP, and a program that worked under PHP
X.Y.Z will typically break in some mysterious way under X.Y.Z+1. So even if
I *wanted* to use PDO, I can't becuase SugarCRM was written for PHP 4.x, not
5.y.
"Okay," says I. "Just change calls to mysql_* to pg_*, with some munging,
and it'll be all okay, right?" Well, not really, because any work I do with
SugarCRM 4.x will be tossed right out when SugarCRM 4.x+1 is released.
Sure, I searched [5] for any work done on porting SugarCRM to PostgreSQL,
but it seems that all the work was last done in 2005, with some done in 2006
if you search hard enough [6]. The guys working on SugarCRM pay lipservice
to the notion of supporting other database engines than MySQL [7], but as
you read up on this crap, it's clear they don't want to even think about
this issue [8].
But I find a version of SugarCRM that has been ported to PostgreSQL. It's
not the latest version, but it's *a* version and it appears to even work.
Until the other day [9].
Apparently, there's a 3k character SQL query generated by SugarCRM that
PostgreSQL doesn't like, which disabuses me of any notion that SQL is SQL is
SQL. Apparently, PostgreSQL requires the use of AS, whereas such a little
detail is optional, and some parts of the codebase add it, and some don't.
And I apparently hit some less tested parts of the codebase. And the line
of PHP causing the error?
$result = pg_query($sql);
in a function, where $sql is a paramter.
I'll spare even more details of what I did because this is getting long,
but I will say that the *easiest* fix for this mess, the one thing that I
could do to get back on track, was this horrible gross hack of a fix
[10][11]:
$sql = preg_replace("/\s+\'\s+\'\s+([a-z]+)/"," ' ' AS \\1",$sql);
$result = pg_query($sql);
And at this point, I don't know where to direct my hate of software---is
it PHP and it's serious lack of database abstraction? It's encouragement of
shoddily written software with no pretentions of being portable? Is it the
SQL parsing of MySQL for making some parts optional? Is it PostgreSQL for
not supporting optional parts of SQL? Is it SugarCRM itself for ignoring
other databases?
-spc (Me? I'm willing to place this purely on PHP's hands, but than
again, I'm a programming language snob ... )
[1] http://www.sugarcrm.com/
[2] http://www.mysql.com/
[3] http://www.postgresql.org/
[4] http://www.php.net/manual/en/ref.pdo.php
[5] http://www.google.com/search?q=SugarCRM+PostgreSQL
[6] http://www.sugarcrm.com/forums/showthread.php?t=20138
yeah, it's dated 2007, but it's JANUARY, 2007.
[7] http://www.sugarcrm.com/forums/showpost.php?p=4641&postcount=10
[8] http://www.sugarcrm.com/forums/showpost.php?p=86781&postcount=3
[9] http://boston.conman.org/2007/12/17.1
[10] http://boston.conman.org/2007/12/17.2
[11] http://boston.conman.org/2007/12/18.1
