From: Sean Conner
Date: 02:06 on 19 Dec 2007
Subject: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
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
From: Peter da Silva
Date: 20:01 on 19 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
I think any time SQL is involved in a hate, you need to hate the
people who designed SQL for trying to make it "user-friendly" and
english-like instead of defining a statement syntax that clearly
distinguished components of a statement and sticking to it.
None of these optional "noise" words and other english-likeness
nonsense.
I'd love to access a database using code like:
(select column-list selector-expression)
(with table selector-expression)
(group column-list query grouping-expression)
(order query order-list)
(join query-list join-expression)
...
So instead of "SELECT crust,count(fillings) AS fillings FROM menu
WHERE cheese='extra' AND topping='liver' GROUP BY crust;"
You get (group ('crust ('fillings (count fillings))) (with 'menu
(select ('crust 'fillings) (and (eq cheese 'extra) (eq topping
'liver)))) ('crust))
Yes, it's longer, but it's unambiguous what it means and it describes
the transaction declaratively, and you're not mixing up the selection
with the grouping and the whole namespace issues about where you can
use "column-name" and where you need "table.column-name" go away,
because they're explicit in the "with" expression or they apply to
the *single* selection in scope.
From: Darrell Fuhriman
Date: 20:23 on 19 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
> You get (group ('crust ('fillings (count fillings))) (with 'menu
> (select ('crust 'fillings) (and (eq cheese 'extra) (eq topping
> 'liver)))) ('crust))
>
You just more or less defined an LDAP query. ;)
Darrell
From: Peter da Silva
Date: 21:08 on 19 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
On 2007-12-19, at 14:23, Darrell Fuhriman wrote:
>> You get (group ('crust ('fillings (count fillings))) (with 'menu
>> (select ('crust 'fillings) (and (eq cheese 'extra) (eq topping
>> 'liver)))) ('crust))
>
> You just more or less defined an LDAP query. ;)
Then I guess this syntax fails the "obviousness" test for a patent
application.
I didn't think this was anything particularly insightful... it was
mostly to provide a contrast with the staggering tower of ad-hocrity
that is SQL.
From: David King
Date: 21:44 on 19 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
> I'd love to access a database using code like:
> (select column-list selector-expression)
> (with table selector-expression)
> (group column-list query grouping-expression)
> (order query order-list)
> (join query-list join-expression)
erlsql[0] attempts to add this type of syntax to Erlang, but does so
in a hatefully inconsistent way. Maybe it's
{select,[random,'()','*',count],
{from,bar},
{where,{id,'=',5}}}
or maybe it's
{select,[{{call,random,[]},'*',count}],
from,{bar},
where,[{id,'=',5}]}
Depends on the query. Where does "order by" go? Maybe it works, maybe
it doesn't.
Does it work with expressions in the "order by" clause? Nope.
Bonus hate: yay for "documentation" that exists only as a blog entry[1]!
Bonus bonus hate: yay for totally (and badly) re-inventing prepared
statements and DBMS abstraction[2][3] instead of using the system
*built for that* that ships with the language, introducing a whole
slew of data- and database-dependent bugs[4]!
[0] http://yarivsblog.com/articles/2006/09/16/introducing-erlsql-easy-expression-and-generation-of-sql-statements-in-erlang/
[1] http://erlyweb.org/doc/erlsql.html is the official documentation.
It's basically empty.
[2] http://erlyweb.org/doc/erlydb_mysql.html
[3] http://erlyweb.org/doc/erlydb_psql.html
[4] http://forum.trapexit.org/mailinglists/viewtopic.php?t=11358
From: Peter da Silva
Date: 22:25 on 19 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
On 2007-12-19, at 15:44, David King wrote:
> {select,[random,'()','*',count],
> {from,bar},
> {where,{id,'=',5}}}
That kind of wrapper around SQL can be useful, but it doesn't
actually do anything about the hate that is SQL. The big problem with
the SQL select statement is that it's a horizontal syntax. "select
columns", "select rows", "join", "sort", "group", "store", and so on
are separate operations. The classical SQL select statement is like a
horizontal microcode operation that has five slots: the first is
"select columns", the second contains "select rows" combined with
"join", then there's a "sort", a "group" and a second "select rows"
by itself hanging on the end in the "having" clause.
So you take these and build a tree of subclauses, and pass it to the
database engine, that if it's any good breaks it down into a simpler
tree that treats "select foo from (select * from something where bar)
as a where zot" and "select foo from something where bar having zot"
as the same thing. And it's not like they didn't have Lisp back then.
From: Robert Rothenberg
Date: 06:37 on 20 Dec 2007
Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!)
On 19/12/07 20:01 Peter da Silva wrote:
> I think any time SQL is involved in a hate, you need to hate the people
> who designed SQL for trying to make it "user-friendly" and english-like
> instead of defining a statement syntax that clearly distinguished
> components of a statement and sticking to it.
> [...]
> I'd love to access a database using code like:
>
> (select column-list selector-expression)
> (with table selector-expression)
> [...]
It isn't the syntax that makes SQL hateful. It's that data is partitioned
into columns in tables (read: fields in flat files). SQL is 50s-era syntax
for 50s-era technology (invented in the 70s, of course).
Objects (despite their hatefulness) are a better way of organising data.
It's much better to say something like
person = database.fetch_by_name("Example", "Peter");
person.email = "peter@xxxxxxx.xxx";
than to do some kind of query to find Peter Example in a table and update
his e-mail address (possibly on a separate related table).
It's not a matter of hiding the gory details of how the data is stored. It's
about storing the data in a way that relates to how it will be used.
Why not use an underlying technology that's based on objects (with some form
of inheritance, etc.) and allows retrieval of arbitrary objects by arbitrary
properties (i.e.. search by any field)? If you want to retrieve or update
data, it's a matter of (de)serialisation, not about connecting linked tables.
(If one is stuck using tables, then it's nice to create a wrapper module
that provides some kind of object-centred interface so that programmer
doesn't have to worry about SQL... it also makes it much easier to change
databases or the structure of them later.)
But people keeping their data in relational databases because of tradition:
that's how everybody does it, and how everybody has always done it, so how
can there be another way to do it?
End of rant.
Rob
From: Juerd Waalboer Date: 14:57 on 20 Dec 2007 Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!) Robert Rothenberg skribis 2007-12-20 6:37 (+0000): > But people keeping their data in relational databases because of tradition And performance. Please, come up with an object based database that can compete. The world will love you.
From: Peter da Silva Date: 15:50 on 20 Dec 2007 Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!) On 2007-12-20, at 00:37, Robert Rothenberg wrote: > It isn't the syntax that makes SQL hateful. I hope you mean "it isn't JUST the syntax". > It's that data is partitioned > into columns in tables (read: fields in flat files). SQL is 50s- > era syntax > for 50s-era technology (invented in the 70s, of course). Come up with something less hateful than relational calculus that actually works better than relational calculus. I've gone through periods when I've been enamored of hierarchical databases and object- oriented databases and the like, but I've yet to see one that wasn't overflowing with even more hate.
From: Michael G Schwern Date: 05:14 on 20 Dec 2007 Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!) Sean Conner wrote: > 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? When there's a SQL compatibility question and MySQL's involved, you can be sure it's MySQL that's got it wrong. They love to bolt on all sorts of additional language extensions. Of course, I don't blame them too much as the SQL spec(s) is bloody enormous and doesn't make much sense in a lot of places and misses a lot of common features. They still don't have any standard for indexing, arguably an implementation detail, so everyone does it differently. And yes, hate PHP's massive jumble of undesigned functions. PHP is just now emerging from where Perl 4 was with it's specific interfaces for each database (oraperl, syperl, etc...)... 13 years ago. If they continue to follow Perl's pattern of maturing it'll be about 2015 before they really get it right.
From: Roger Burton West Date: 10:24 on 20 Dec 2007 Subject: Re: Web based applications wedded to MySQL (SugarCRM, I'm looking at you!) On Wed, Dec 19, 2007 at 09:14:45PM -0800, Michael G Schwern wrote: >When there's a SQL compatibility question and MySQL's involved, you can be >sure it's MySQL that's got it wrong. They love to bolt on all sorts of >additional language extensions. Which is why I have in my sigfile: TRADITIONAL: Make MySQL behave like a "traditional" SQL database system. A simple description of this mode is "give an error instead of a warning" when inserting an incorrect value into a column. -- http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html 'Nuff said. Roger
Generated at 10:25 on 16 Apr 2008 by mariachi