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

Jeff Hubbs hbbs at attbi.com
Mon Dec 30 17:06:37 EST 2002 

I'm not a DB guru exactly, but I an very interested in these issues as
it's something I'm likely to encounter in future work.  

This is getting off-topic (so sue me!), but I have a memory of a DEC
layered product for VMS that I think was called RDBMS that worked at a
low enough level that you could change what kind of database it was. 
IIRC (and this was over ten years ago), it was a "network" database and
it was possible to implement a "hierarchical" database out of it, which
worked very well for dealing with physical objects that were composed of
other physical objects and you were trying to keep track of both
irreducible parts and assemblies made out of those parts, as well as
assemblies made out of those assemblies, and so on.  

RDBMS is something that has probably fallen off the face of the earth,
but my exposure to it suggests that it ought to be possible to have a
kind of DBMS that was kind of "stupider" than Oracle, SQL Server, DB2,
etc. and that you could code your stuff to implement whatever kind of
"higher-order" DBMS behavior you liked.  But, consider how much work
that puts on you as a developer, and Ricardo has a good point - do you
really benefit by being THAT flexible?  What you're trying to do may
fundamentally change and you may have to abandon work done up to a given
point and take on new work to adapt if you take the path I've suggested
before.

I have no idea if this is a real help, but one of the Linux mags from
several months back (sorry, can't pin it down, but it was more than 6
months ago) had a discussion of databases and it mentioned work being
done on XML databases, and my interpretation of what I was reading
suggested that it might have the kind of "stupidity" I'm alluding to. 
But, I feel like it's one of those cases where you'd might as well have
your app open, read, and write data files directly because at least then
you can code intelligently for what you're doing, whereas this so-called
XML database seemed to be anything but.

- Jeff

On Mon, 2002-12-30 at 15:55, Ricardo Davis wrote:
> 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


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

More information about the Ale mailing list