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

[J.M. Taylor]

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
>> >
>> >
>> >
>> > _______________________________________________
>> > 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
>>
>
> Today's Random Quote--------------------------------------
>
>  43rd Law of Computing: Anything that can go wr .signature:
> Segmentation violation -- Core dumped
>
> -----------------------------------------------------------
>
> _______________________________________________
> 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