SQL builder library

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

SQL builder library

Post by alex.barylski »

I've been building an OO/Procedural SQL building library that will hopefully assist me in writing advanced SQL queries. SELECT is obviously the trick one, I have tried probably every known OO SQL builder, from the hardcore (entirely OO fluent interface driven) to ones that accept arrays. Mine is somewhere in the middle.

The idea is, is basically you write out the SQL with all possible filters applied, and the framework itself figures out which to drop from the query based on the input.

Code: Select all

SELECT * FROM persons WHERE (fname = 'Alex' AND lname = 'Gallagher' AND address = '123 Test Street') OR (age = 31 AND gender = 'male')
The framework will then essentially parse the query (although not quite thats already done so I'm working with an parse tree) and based on the values assigned to the query (prepared statements so bind comes into play) will re-construct the above SELECT accordingly. So if you only POST fname to the server and otehrs are NULL or empty, they are left out of the query, thus freeing the developer from having to write out mundane conditional code in the select() methods.

The conditional tests for each filter can be whatever you imagine, but the default is to ignore when NULL or empty which results in a much more declarative SELECT query using an OO/Procedural interface.

So my question becomes, does anyone know of any libraries that support something similar, that I may find interesting to borrow ideas or maybe even collaborate with other developers to help speed development along?

Cheers,
Alex
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SQL builder library

Post by Benjamin »

:arrow: Moved to PHP - Theory and Design
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SQL builder library

Post by Christopher »

It seems like a strange feature to remove conditions from a query if the bound value is null. I guess I think of this as application logic for building special kinds of queries such as searches based on user input.
(#10850)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: SQL builder library

Post by alex.barylski »

It seems like a strange feature to remove conditions from a query if the bound value is null. I guess I think of this as application logic for building special kinds of queries such as searches based on user input.
That may be the case, however I am not removing conditionals, so much as I am abstracting them, so they are expressed in a more declarative way, as opposed to imperative. Just like SQL itself, when performing queries you do not worry about 'how' just the 'what', I see this as yet another layer of abstraction over top of SQL.

NULL is just the most logical default, but you can override that default to be whatever you want, if a string = 'ALEX' you could chose to remove it from the query. Actually I think I am going to look into changing this to isset() as a default, that way, if you post with 10 query fields set to something, the query would use all 10.

I see what your saying though, where do you draw the line? Do you use only the most limited SQL features, such as:

Code: Select all

SELECT * FROM students
Then do you iterate the records and apply business logic, such as, showing only male students, age 25 to 30, whose weigh exceeds 200Lbs? Or do you throw that code into the query and be done with it?

I considered this long and hard and ultimately decided that, while keeping some business logic in SQL means I am moving it from the business logic layer and into the data access layer, I favor code readability and maintainability over seperation of concerns and testability. Reading declarative code such as:

Code: Select all

SELECT * FROM students WHERE gender = 'male' AND age > 25 AND age < 30 AND weight > 200
I find much easier to read, maintain and comprehend than a dozen or so lines of conditional testing, within in a foreach() all my abstraction does is further shift some of the responsibility from business logic into a data abstraction layer.

Code: Select all

$gender = null;
$weight = 200;
$gateway->select($table, array('gender' => $gender, 'weight' => $weight));
No conditional testing for whether the weight is specified or gender, and whether they need to be included in the resulting query, that is done for you by the underlying data abstraction layer. Again whether the default is NULL or isset() or some random value is of no significance.

Cheers,
Alex
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: SQL builder library

Post by josh »

I'd rather look at conditional code than have a framework try to guess how the query should look. As for something similar, Zend_Select would be the obvious suggestion. I think you could easily avoid using conditional code and build ontop of that, without all that parse tree complexity.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: SQL builder library

Post by alex.barylski »

'd rather look at conditional code than have a framework try to guess how the query should look
It's not guessing, you still express your intent, but declaratively, not imperatively. There are pros and cons to both, I am not sure if you read my reply to arborint, but I justify my reasoning there, so i won't repeat myself here. :)
I think you could easily avoid using conditional code and build ontop of that, without all that parse tree complexity.
I've looked into Zend_Select already, mind you not extensively but still. I'm curious, assuming you can capture the WHERE clause expression-set before rendering into a string form, how would you say, drop this conditional and wrap the entire set with an AND trash = 0 using Zend_Select???

The nice thing about having (something of a parse tree) is the ability to propagate/reuse the same standard query and/or easily modify any query before rendering and execution.

Looking at this example:

Code: Select all

$select = $db->select()->from(array('p' => 'products'), array('product_id', 'cost_plus_tax' => '(p.cost * 1.08)'));
I can see even if you could gain access to the WHERE clause as an array, finding the cost_plus_tax would require traversing a nested array and changing the 1.08 to something altogather different would be a nightmare-ish hack. With a parse tree, everything is already parsed, if that weren't already obvious, changing 1.08 to something custom override is a snap.

Cheers,
Alex
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SQL builder library

Post by Christopher »

You should look at the Skeleton OO SQL classes. A bunch of people have worked on them -- John Cartwright and pytrin did the major rewrite a while back. There is a lot of code there. allspiritseve and I did a pass over the code a while back to do some cleanup and add features.
(#10850)
Post Reply