db abstraction: expressions

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

koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

db abstraction: expressions

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

Re: db abstraction: expressions

Post 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, '>');
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

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

Re: db abstraction: expressions

Post 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.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

I agree that's it is clearer and better looking. But it doesn't allow combining of conditions.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: db abstraction: expressions

Post 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?
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

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

Re: db abstraction: expressions

Post 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.
(#10850)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: db abstraction: expressions

Post by jayshields »

In my opinion, it's impossible to simplify SQL unless you are prepared to limit its capabilities.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: db abstraction: expressions

Post 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.
(#10850)
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

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

Re: db abstraction: expressions

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

Re: db abstraction: expressions

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

Re: db abstraction: expressions

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

Re: db abstraction: expressions

Post 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.
Post Reply