[ale] PHP SQL parser (was: DB abstraction for PHP)

[Joseph A Knapka]

J.M. Taylor wrote:
> John,
> 
> There aren't any problems as such, it works great for what it does.  It's
> just not really a true abstraction, ie, there's no intelligence behind
> what PEAR does with your sql statement.
> 
> Example: you write $query = "some query with nested subqueries";
> 
> Well that works great for Oracle and maybe OK with some others.  But you
> try to pass it to MySQL and MySQL just stares blankly at you.   So you have
> to do your own pre-processing and set up different scripts with different,
> database-specific queries.  And of course PEAR expects different
> parameters for whatever database you're making a connection to, so you
> have to handle that somehow (probably in a set of wrapper functions that
> set up the connection and load the db-specific queries).  And then your
> main scripts have to be smart enough to know what db and wrappers to use,
> and how to handle the different output from the different datbases.

This is, IMO, more of an issue with the SQL standard.
IMO, the nature of the SQL standard makes it very difficult to
take advantage of the very useful high-level features of
DBs that support them, while remaining DB-independent. As soon
as you use a query more complex than a simple
select/insert/update/delete, you have pretty much spotwelded
yourself to your DB vendor. Yes, you can interrogate the DB
and find out if it will let you do things the easy way, and
revert to the hard way if not -- but if you have to write
the "hard way" code anyhow, it seems a bit pointless. ("Performance!"
you cry; but presumably if you've bothered to implement
things the "hard way", your app can tolerate the performance
hit entailed thereby, which means that the "easy way" is
at most a convenience - and not a very convenient one at that.)
I almost always do the fiddly bits programatically to avoid these
sorts of issues.

The JDBC API gets this mess about as right as it can be:
as long as you avoid subselects, joins, and other SQL
arcana, you can pretty much drive any DB with code
written to use JDBC. (I hate Java for any number of reasons,
but they do get some stuff right :-) If you want to design
a good DB abstraction, you could do a lot worse than using
JDBC as a model.

Cheers,

-- Joe

_______________________________________________
Ale mailing list
Ale at ale.org
http://www.ale.org/mailman/listinfo/ale