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

[John Wells]

Jenn,

Thanks.  I know there are quite a bit of syntactical and conceptual
differences between databases.  I'm just searching for a solution that
takes it as far as possible.

I plan on having database-specific classes that will parse and return
optimal syntax for specific databases, but I'd like to plug them in to the
db abstraction layer based on the database in use...sort of like a
Strategy design pattern, I think.  iow, the db abstraction layer will
provide common methods for executing common query types, but the
db-specific classes will be responsible for "designing" these queries.

Sounds like PearDB or adoDB might allow me to accomplish just this sort of
thing.

Thanks!

John

J.M. Taylor said:
> 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



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