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

Mon Dec 30 15:55:04 EST 2002

Jenn,

I think that the problem with universal abstraction (MySQL to FoxPro 
to Clipper to PostgreSQL to Oracle to DB2 ...) is one of changing 
requirements during an application's life cycle.

How many web applications written in PHP are going to need to scale 
from MySQL to Oracle Parallel Cluster?  And if you had such an 
application would the data model and business logic remain largely 
intact over the lifetime of the application?  And if it did would we 
still want the application in PHP?  Many times the answers to these 
questions are no.  So what happens is that solutions are crafted that 
either meet the "abstraction in PHP" requirement (such as PEAR and 
ADODB (http://php.weblogs.com/ADODB)) or the "middleware data engine" 
requirement (such as SequeLink - datadirect-technologies.com) or the 
"SQL API" requirement (such as SQLAPI++ - http://www.sqlapi.com/).

By the way, the DataDirect Tech web site has a nice paper on their 
web site, "Different Data Connectivity Methods", that deals with the 
issue of data connectivity and application requirements.  It's worth 
a read -- and you don't have to give them marketing info to read the 
paper.  :)

-Ricardo
---------------------------------------------------------------------
Ricardo Davis
PowerSystems Information Management, Inc.
Exceeding Customer Expectations in Information Systems Consulting
---------------------------------------------------------------------

"Everything should be made as simple as possible, but not simpler."
-Albert Einstein

---------------------------------------------------------------------

>Ben,
>
>Frankly, this is my business partner's holy grail.  I'm more or less
>content with the loose assortment of scripts I've created over the years.
>
>>From what I understand of this whole process, you are 100% correct about
>the issues surrounding a true abstraction.  There would have to be blocks
>of code that handle the subselects, etc, after parsing, per database.  I
>think the whole thing would have to be very modular and built around sort
>of a central framework that allowed new per-database features to be
>dropped in and handled (or error gracefully??).  It's not a trivial
>undertaking...but I would think that one would have to allow for the
>features of each database or else what would be the point of going to all
>the trouble to parse the statement?
>
>If a developer wants to be able to just trash his MySQL install and start
>using Oracle with an existing php application, there's no way that I know
>of right now to do that.  It seems like it would be a worthwhile quest to
>embark on. :)
>
>jenn
>
>
>
>Benjamin Dixon said:
>>
>>  Been reading from the sidelines and it seems to me that creating such a
>>  holy grail of abstraction would ultimately limit you to the smallest
>>  feature list of the set of databases you cater to, or force you to go
>>  out of your way to make your code work with both the most featureful and
>>  least featureful databases by adding all sorts of crazy extras to handle
>>  the idiosyncracies (read: non-compliance with the SQL standard) of each
>>  db. For example, with subselects, even if you could parse the SQL, what
>>  would you do in the case of MySQL which doesn't support subselects (yet,
>>  as far as I know). I'm sure we're getting way off topic but I'd be
>>  interested in hearing how it would be done.
>>
>>  Ben
>>
>>  On Mon, 30 Dec 2002, 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.
>>>
>>>  So it's not undoable.  I've used PEAR and my own lighter versions for
>>>  many projects because a true abstraction simply isn't out there.  From
>>>  my perspective, it's easier to write much lighter wrappers and call
>>>  them as needed if you're going to have to do all that garbage anyway,
>>>  you load far fewer lines of PHP for each time you need to talk to the
>>>  database. I don't think PEAR is a terribly efficient way of doing
>>>  things, but it certainly works.
>>>
>>>  Sorry for the long rant. I've been trying to find a solution to this
>>>  for awhile, I think the real solution is going to be to write a SQL
>>>  parser.   There is a pay product out there that I saw in linuxjournal
>>>  one day, but what it actually *does* or how much it costs, I have no
>>>  idea (and the name, of course, escapes me).  I can try to look it up
>>>  if you're interested.
>>>
>>>  Ciao
>>>  jenn
>>>
>>>
>>>
>>>  John Wells said:
>>>  > Jenn,
>>>  >
>>>  > Could you outline briefly the problems you've run into with PearDB?
>>>  >
>>>  > Thanks for the help!
>>>  >
>>>  > John
>>>  >
>>>  > J.M. Taylor said:
>>>  >> John,
>>>  >>
>>>  >> I've been looking for about a year and have yet to find what you're
>>>  talking about...you want true abstraction.  PEAR doesn't do much
>>>  IMHO.
>>>  >>
>>>  >> So far what I've done is write a wrapper script that handles the db
>>>  connection based on a paramter, and returns your result in an array
>>>  of assoc arrays.  In *theory* it works great (and it sure does make
>>>  calling the DB easier) but in practice I've never tried it on
>>>  vastly different DBs and frankly have no knowledge of how cross-db
>>>  my SQL statements would really be. :(
>>>  >>
>>>  >> What you (and I) really want is a PHP SQL parser.  It's on my list
>>>  of things to write but it's so far away that I'm afraid I can't be
>>>  of any more help than to offer the wrapper script.
>>>  >>
>>>  >> Anyone know of any other resourses for this?
>>>  >>
>>>  >> Jenn
>>>  >>
>>>  >>
>>>  >> John Wells said:
>>>  >>> Can anyone suggest a very mature db abstraction solution for PHP?
>>>  I'd like to support at the very least Oracle, MySql, and
>>>  optionally Postgres.  SqlServer would be nice as well, but not a
>>>  requirement.
>>>  >>>
>>>  >>> Thanks!
>>>  >>>
>  >> >>> John

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