OOP SQL Generators

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

OOP SQL Generators

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: OOP SQL Generators

Post 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
}
 
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: OOP SQL Generators

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: OOP SQL Generators

Post 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 :cry:
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: OOP SQL Generators

Post 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.
(#10850)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: OOP SQL Generators

Post by alex.barylski »

I'll look into Skeleton again...thanks
Post Reply