Page 1 of 2

db abstraction: expressions

Posted: Wed Sep 03, 2008 4:13 pm
by koen.h
I'm having difficulty finding a good way of creating expressions in my db abstraction class(es). Closest to what I'm aiming at comes eZ Components but it seems like so much clutter to create even simple things:

Code: Select all

 
$q
 ->update( 'quotes' )
 ->set( 'quote', 'His skin is cold... Like plastic...' )
 ->where( $q->expr->eq( 'id', 1 ) ); 
 
$q
 ->deleteFrom( 'quotes' )
 ->where( $q->expr->eq( 'name', $q->bindValue( 'Robert Foster' ) ) ); 
 
It's the [strike]bolded[/strike] part passed to the where() that is relevant. If writing SQL scares you, this will be worse. So what is an elegant way to create SQL expressions?

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 9:12 am
by allspiritseve
The majority of your where expressions should be equals, so...

Code: Select all

$q  -> delete
    -> from ('quotes')
    -> where ('name', 'Robert Foster');

And then if you need to do something different, maybe:

Code: Select all

$q  -> delete
    -> from ('quotes')
    -> where ('num', 12, '>');

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 9:37 am
by koen.h
Thanks. I've been thinking along those lines also. The problem with it is that it only allows one level of expressions. Eg the following doesn't seem to be possible with it:

WHERE SUBSTRING(name, 1, 1) > k

The other possibility is to use arrays to indicate an expression.

where (array('num', 12, '>'));

You can nest those but it becomes as unwieldly as eZ components' solution.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 9:44 am
by allspiritseve
For readability I'd prefer not to use arrays... I'd rather do:

Code: Select all

 
$q->where ('key','value','>');
$q->where ('key','value','>');
$q->where ('key','value','>');
But that's just my preference.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 12:37 pm
by koen.h
I agree that's it is clearer and better looking. But it doesn't allow combining of conditions.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 1:06 pm
by jayshields
Why are you making this if you think it's "scarier" than writing SQL?

There are probably things like this out there already anyway, so why reinvent the wheel?

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 1:11 pm
by koen.h
jayshields wrote:Why are you making this if you think it's "scarier" than writing SQL?

There are probably things like this out there already anyway, so why reinvent the wheel?
I'm looking for a way to abstract the expressions that don't look scarier than SQL.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 1:34 pm
by Christopher
As I recall when building these things, it simplified things to have the operator with the field name:

Code: Select all

 
$q->where ('key>','value');
$q->where ('key<','value');
$q->where ('key=NOW()');
There are also the issues of database specific escaping and dealing with nonstandard LIMIT syntax. Skeleton has a stand-alone component for this that jcart and pytrin worked on (Credit to Mordred for security audit/scolding ;)). The problem with this type of solution is the law of diminishing returns.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 1:41 pm
by jayshields
In my opinion, it's impossible to simplify SQL unless you are prepared to limit its capabilities.

Re: db abstraction: expressions

Posted: Thu Sep 04, 2008 1:51 pm
by Christopher
jayshields wrote:In my opinion, it's impossible to simplify SQL unless you are prepared to limit its capabilities.
This is very true. Writing SQL will always be easier, more flexible and more expressive. The law of diminshing returns was never more obvious that to writers of SQL and OR/M classes! ;)

But there are good reasons for writing these classes -- with limited functionality. In the Skeleton framework the main reason they exist is for internal use within the TableDataGateway, ActiveRecord, DataMapper and other DB classes that need to generate SQL from arrays containing data or conditions. They can be very handy for a lot of very common SQL. Especially INSERTs, UPDATEs, DELETEs and simple SELECTs. Using objects can actually be clearer, cleaner and more error free than writing SQL. Complex SELECTs are where the trouble comes.

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 3:07 pm
by koen.h
Have been playing with:

Code: Select all

fx.concat(fx.substr(a, 1, 1), b) fx.eq c
today. But regular expressions are a tad too slow. I could cheat and use str_replace for mysql-expressions and that seems about 10 times faster, but still too slow.

edit:

here's some (probably very naive) testing done on an older machine (processor AMD Athlon XP 2100 with 512MB ram, using ubuntu)

Code: Select all

function replace($match)
 
{
 
    $fx = array('concat'=>'concat', 'substr'=>'substr', 'eq'=>'=');
 
    $match = substr($match[0], 3);
 
    if (isset($fx[$match]))
 
    {
 
        return $fx[$match];
 
    }
 
    throw new Exception('Invalid expression.');
 
}
 
 
 
function microtime_float() {
 
    list($usec, $sec) = explode(" ", microtime());
 
    return ((float)$usec + (float)$sec);
 
}
 
 
$time_start = microtime_float();
 
 
 
for ($a=0; $a<100; $a++)
 
{
 
    $string = 'fx.concat(fx.substr(a, 1, 1), b) fx.eq c';
 
    $result = preg_replace_callback('#fx\.([a-z]+)#', 'replace', $string);
 
}
 
 
 
echo round(microtime_float()-$time_start, 6) .' seconds';
This gives 0.0025 seconds consistently.

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 6:24 pm
by Christopher
koen.h wrote:Have been playing with:

Code: Select all

fx.concat(fx.substr(a, 1, 1), b) fx.eq c
I have obviously missed something. Why are you doing that?

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 6:43 pm
by allspiritseve
arborint wrote:As I recall when building these things, it simplified things to have the operator with the field name:
The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters. It's making me think though ... I wonder if a system could be devised of statements or clauses that could be built on top of each other, for example: for a statement "key > :key" you could bind the value 1 to :key, and the statement could be bound to a parent statement "SELECT * FROM table WHERE :where", with the value being added to a final array that could be bound using PDO.That would scale well with the composite nature of SQL queries. The trick would be making it with a slim syntax in PHP... oh, what I wouldn't give for named parameters...

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 6:54 pm
by Christopher
allspiritseve wrote:The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters.
I'm not quite sure what you mean ... maybe I misunderstood your parameters ... but the OO SQL style is an alternative to prepared statements with bound parameters. So whether you do $where->select('key', 'value', '>') or $where->select('key>', 'value') you are "binding" the value to the column name in both cases. The real goal of any kind of "binding" is escaping the value. That's why I lean toward fewer parameters and better readability.

The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement. It is simplest to always quote though.

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 7:01 pm
by allspiritseve
arborint wrote:
allspiritseve wrote:The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters.
I'm not quite sure what you mean ... maybe I misunderstood your parameters ... but the OO SQL style is an alternative to prepared statements with bound parameters. So whether you do $where->select('key', 'value', '>') or $where->select('key>', 'value') you are "binding" the value to the column name in both cases. The real goal of any kind of "binding" is escaping the value. That's why I lean toward fewer parameters and better readability.

The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement.
I use PDO to make the actual statements... the query class would just work on top of PDO. Here's an example:

Code: Select all

$query->where ('name', 'Cory');
$query->where ('num', 1);
The class would then generate a statement like this:

Code: Select all

"SELECT * FROM users WHERE name = :name AND num = :num"
Which would be created with PDO, and bound with the following array:

Code: Select all

array (
 ':name' => 'Cory',
 ':num' => 1,
);
That solves a lot of problems with very little syntax. As soon as you throw anything else into the lefthand parameter though, you can't automatically generate the key for binding. Hence my third parameter. Also, I don't have to worry about escaping, because PDO sends the values separately from the statement.