Page 1 of 1
OOP SQL Generators
Posted: Sat Jun 21, 2008 12:22 pm
by alex.barylski
Having followed some disscussions on OO SQL generators I'm seriously considering implementing one for my own framework. I really like the idea of a fluent OO interface for generating SQL.
I'm curious though, are there any major issues any of you have experienced in using such a technique? Do you hit the same road blocks as ORM type solutions when dealing with complex JOIN's or anything?
Seems to me, SQL generators would be the best solution for making SQL more reusable, extendable and easy to use with having to resort to phrasebook approach -- which I have used hitherto.
p.s-If I have misunderstood the extent of ORM please feel free to clarify or add too, but also please stick to the topic at hand as well, thank you.
Comments?
Re: OOP SQL Generators
Posted: Sat Jun 21, 2008 1:08 pm
by Zoxive
Something I considered when I was programming my own, was the performance loss when tossing around the big arrays that I would sometimes be getting out of the database. So instead of looping threw the data twice, (Once to get it out of the database, then into an array to then return from the function, and then again to output the data) I made mine so the result was also an object. Which basically just contained the result link and the connection link, for mysql_last_insert_id.
Code: Select all
<?php
while($Row = $Result->fetch()){
// Only one loop
}
As opposed to..
Code: Select all
<?php
// Done in the Obj.. but just to show..
function getResult($Query){
// Blah blah
while($Row = mysql_fetch_assoc($Result)){
$Array[] = $Row;
}
return $Array;
}
foreach($Array as $Row){
// Blah
}
Re: OOP SQL Generators
Posted: Sat Jun 21, 2008 1:20 pm
by allspiritseve
Zoxive wrote:Something I considered when I was programming my own, was the performance loss when tossing around the big arrays that I would sometimes be getting out of the database
How much of a performance loss is it, really? I never understood that rationale, because you can't have more than one result floating around, correct? What happens if you want data from two separate queries? Loop through one but not the other?
Re: OOP SQL Generators
Posted: Sat Jun 21, 2008 1:27 pm
by Zoxive
allspiritseve wrote:
How much of a performance loss is it, really? I never understood that rationale, because you can't have more than one result floating around, correct? What happens if you want data from two separate queries? Loop through one but not the other?
It still works, because it uses the resource links, that is given back from mysql.
It works just like this works..
Code: Select all
<?php
$Result = mysql_query($SQL);
$Result2 = mysql_query($SQL2);
while($Row = mysql_fetch_array($Result2)){
// Looping through result #2
}
while($Row = mysql_fetch_array($Result)){
// Looping through result #1
}
I haven't done a direct comparison, of the performance, but I know that it will be noticeable on larger querys, and most of the time you do multiple querys per page, so it just keeps racking up and up. It would also use less memory. I might just write up a comparison one of these days.
Sorry for hijacking the thread

Re: OOP SQL Generators
Posted: Sat Jun 21, 2008 3:48 pm
by Christopher
Take a look at the SQL classes in the
Skeleton framework (SVN is the latest). They have gone through many, many design iterations because the problem is a thorny one. They are mainly used internally to centralize SQL generation for ActiveRecord, TableDataGateway, DataMapper and DB wrapper classes. It was Mordred who raked those classes over the coals initially to get rid of the security problems they had. I think Jcart and pytrin have come up with a good solution for JOINs recently, but I don't know if it is fully implemented yet.
Re: OOP SQL Generators
Posted: Sat Jun 21, 2008 5:12 pm
by alex.barylski
I'll look into Skeleton again...thanks