Page 1 of 1

SQL Query Builder

Posted: Sun Jul 16, 2006 3:11 pm
by Ambush Commander
Not sure if this belongs in Databases, but it's mostly PHP code related.

I've been doing some reading of Domas Mituzas (the inhouse MySQL dev. for MediaWiki) and came across this interesting blog post: http://dammit.lt/2006/03/05/db-abstract ... mediawiki/ essentially describing the differences between Zend and MediaWiki's db abstraction layers.

However, what struck me was the idea that SQL was structured rather than just text, so you ought to be able to generate it with a query builder, like...

Code: Select all

$select->where('field = ?',$value);
rather than manually typing the SQL.

It seems to be a quite interesting approach (I've never heard of it before, but it makes sense). Do you use it? And also, do you think it would be a good idea to augment the database wrapper in a toolkit to support query building (is it a feature or a core item)?

Re: SQL Query Builder

Posted: Sun Jul 16, 2006 3:16 pm
by Chris Corbyn
Ambush Commander wrote:However, what struck me was the idea that SQL was structured rather than just text, so you ought to be able to generate it with a query builder, like...

Code: Select all

$select->where('field = ?',$value);
rather than manually typing the SQL.
This is not new to me. I've seen it other code before and I've used it in form classes myself :)

SQL is structured so yes, it's easy to do this nicely if you building a query. SQL is easier to parse than other languages too although analysing a query is not as simple is building the damn thing (I spent some time writing an SQL beautifier).

Posted: Sun Jul 16, 2006 3:38 pm
by Ambush Commander
But would you consider it's benefits great enough to mandate by a toolkit? That is, the Toolkit requires a DB wrapper class that gives this functionality.

Posted: Sun Jul 16, 2006 3:55 pm
by Christopher
I am of two minds about building SQL that way. If certainly seems interesting at first glance, but once you get into it -- as the article whined about ;)) you get into programmer preference about how to do things and also into some real gymnasitcs to do advanced things (and even simple ones).

The root of the problem is that SQL is very expressive and very mature so you can do almost anything coding in straight. So you end up with a ton of code to replace something that is already very good. However, there are things in SQL -- mainly building lists -- that are very tedious and building them in code makes it much easier to make error free changes with ease.

You might want to look into Hibernate and OQL t o see some of the cools stuff that has been done in this area -- it is certainly not new.

Posted: Sun Jul 16, 2006 3:58 pm
by Ambush Commander
Hmm... if that's the case, then I really can't use SQL building. Hrmm....

Makes for interesting research. I'll look into those two apps you mentioned. PHP? (Doesn't seem like it. Hibernate's Java)

Posted: Sun Jul 16, 2006 4:23 pm
by Christopher
Ambush Commander wrote:Hmm... if that's the case, then I really can't use SQL building. Hrmm....
Actually I think they are very useful. The problem is creating a one-size-fits-all that does everything that SQL does. It is very difficult and the code ends up huge. But if you limit yourself to a specific solution then I think it can work very well. I use these types of classes as the basis for or within Model classes all the time because it makes writing many types of code easy and handles all the escaping nonsense that is so tedious. But I always also have plenty of hand coded SQL because other stuff is dead simple done that way.
Ambush Commander wrote:Makes for interesting research. I'll look into those two apps you mentioned. PHP? (Doesn't seem like it. Hibernate's Java)
Yes, it's pretty much the standard Java persistence library.

Posted: Sun Jul 16, 2006 7:39 pm
by Jenk
Pear::DB allows the structured creation of query strings just like this.

Though from what I gather, there are other reasons why Pear::DB is not so widely used. Never used it myself.

I've used structured SQL in the past with forms for custom search criteria.. form has input for fields, operator and value.

Posted: Mon Jul 17, 2006 1:37 am
by Christopher
Actually PEAR :: DB is no longer supported I believe -- it's MDB2 now. It has a "OO Query Tool" or some such thing here:

http://pear.php.net/manual/en/package.d ... rytool.php

But I don't think that is exactly an elegant interface.

Posted: Mon Jul 17, 2006 4:17 am
by Jenk
I just remembered glancing over someone's code that had "$db->whereAdd('col =', $val);" or some such :)

Posted: Mon Jul 17, 2006 1:16 pm
by Ward
It's quite handy for displaying result tables. For example, if you have a table of DB records, and the user is allowed to do things like re-sort, filter by certain fields, etc, then a query builder is very useful.

Posted: Tue Jul 18, 2006 6:12 am
by Ambush Commander
I imagine so. In my inexperience, I've never actually had to allow such control over querying, but that definitely would be the case once I finish incrementally refactoring my primary application.

Specialized builder it is.