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

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 »

Ahhhhh....
(#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:The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement.
Again, I'm not worried about quoting, since I'm using PDO... but I'm thinking about this idea a bit. I first turned it down, because if you have a lot of nested statements, the order of values and their respective ? may be lost. But now I'm thinking, maybe every time you bind a value, it could replace the ? with an incrementing key, kept by the query object. For example:

Code: Select all

$where ('key > ?', 10);
When called, select() replaces ? with :key1, and places the value in an array:

Code: Select all

array ('key1' => 10);
If there are no question marks, the system could just assume the left parameter is a key, and use that instead of the system-generated key.

That should also allow:

Code: Select all

$select->where ('SUBSTRING(name, 1, 1) > ?', $k);
Which the OP was trying to find an elegant solution for. Thoughts?
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

arborint wrote:
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?
Because it allows me to abstract functions and operators in an expression in a (imo) cleaner way than what I have seen from others. It's no OO style, so I can't say I really like it. But

Code: Select all

$q->where ('key>','value');
in your example seems mixing the two syntax wise.
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:

Code: Select all

$q->where ('key>','value');
in your example seems mixing the two syntax wise.
Not really because the intention is different. The above code is dividing a WHERE condition into two parts 1) the value for escaping, 2) everything else. The whole point is to automate/guarantee escaping.
(#10850)
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

I agree with the point of it (adding abstraction as a second concern). But how do you know the key in your example wasn't a variable?
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

What about this:

Code: Select all

$db = Factory::create($dsn);
$select = $db->select()->from('mytable')->where($db->expr->eq($db->expr->concat('?', '?'), $db->expr->substr('?', 1, 5)));
$stmt = $db->prepare($select);
$stmt->bindParams('var1', 'var2', 'var3');
$stmt->execute();
How this could work:

Code: Select all

 
 
class Db
{
    public static function eq($var1, $var2)
    {
        return array('? = ?', array($var1, $var2));
    }
 
    public static function concat($var1, $var2)
    {
        return array('concat(?, ?)', array($var1, $var2));
    }
 
    public static function substr($var1, $start, $end)
    {
        return array('substr(?, ?, ?)', array($var1, $start, $end));
    }
}
 
class Select
{
    public function where(array $expr)
    {
//      array('? = ?', array(
//                          array('concat(?, ?)', array($var1, $var2)),
//                          array('substr(?, ?, ?)', array($var1, $start, $end))
//                          ));
//      array(expr, array(values))
        return $this->format($expr[0], $expr[1]);
    }
 
    private function format($expr, array $vals)
    {
        $expr = preg_replace('#\?#', '%s', $expr);
        $replacements = array($expr);
        foreach ($vals as $val)
        {
            if (is_array($val)) // use expr=>
            {
                $val = $this->format($val[0], $val[1]);
            }
            $replacements[] = $val;
        }
        return call_user_func_array('sprintf', $replacements);
    }
}
 
$select = new Select();
echo $select->where(Db::eq(Db::concat('?', '?'), Db::substr('?', 1, 5)));
 // concat(?, ?) = substr(?, 1, 5)
 
This returns the expression ready to be bound by parameters.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

I see I haven't been really consistent with static calls later and $db->expr->fx() first. But the idea should be clear.
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:I agree with the point of it (adding abstraction as a second concern). But how do you know the key in your example wasn't a variable?
I think it would be really rare for me to use a variable as a column name. And I don't think I would ever take the chance on using a value from the user as a column name. In the rare case that I did I would put it in an if(), but you could easily do $where("$column>", $value). For me WHERE is different that UPDATE SET or INSERT VALUES where the column names often come from array keys.
koen.h wrote:I see I haven't been really consistent with static calls later and $db->expr->fx() first. But the idea should be clear.
No. It seems like either fluent calls, or just functions (rather than static method calls) would be cleaner.
(#10850)
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

arborint wrote:No. It seems like either fluent calls, or just functions (rather than static method calls) would be cleaner.
So I'm currently at the following:

Code: Select all

$db = Factory::create($dsn);
$select = $db->select()
                  ->from('mytable')
                  ->where($db->expr->eq($db->expr->concat(':param1', ':param2'), $db->expr->substr(':param3', 1, 5)));
$stmt = $db->prepare($select);
$stmt->bindParam('param1', 'var1');
$stmt->bindParam('param2', 'var2');
$stmt->bindParam('param3', 'var3');
$stmt->execute();
$expr = $db->expr; makes it a little shorter.
I think it would be really rare for me to use a variable as a column name.
You're right, it doesn't happen much. But it's also not that uncommon. And if you have a way to filter all data, why not do so anyway (in case other users forget about binding a parameter)?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: db abstraction: expressions

Post by josh »

Code: Select all

 
/**
* @desc Cleans a MySql field name by removing all non alphanumerical characters
* ( leaves underscores, periods and dashes in place ), string cast is if your field name came from a simpleXML object
*/
function cleanField( $field )
{
    return preg_replace('/[^A-Z0-9_\.-]/i', '', (string)$field);
}
 
Just make sure when you use it you do it like so

Code: Select all

 
$sql = '`' . cleanField($fieldName) . '`';
 
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: db abstraction: expressions

Post by koen.h »

jshpro2 wrote:

Code: Select all

 
/**
* @desc Cleans a MySql field name by removing all non alphanumerical characters
* ( leaves underscores, periods and dashes in place ), string cast is if your field name came from a simpleXML object
*/
function cleanField( $field )
{
    return preg_replace('/[^A-Z0-9_\.-]/i', '', (string)$field);
}
 
Just make sure when you use it you do it like so

Code: Select all

 
$sql = '`' . cleanField($fieldName) . '`';
 
I don't think this will be sufficient for international character sets.

http://dev.mysql.com/doc/refman/5.0/en/ ... mysql.html
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: db abstraction: expressions

Post by josh »

True, I only use western characters in my field / table names.. you still have to make sure they're not selecting a field they don't have permissions to also. I opt to use SQL wherever possible, encapsulating code to generate common fragments, for instance I have an object that just generates different pieces of SQL I need to build pagination, I call different helper classes to build different pieces of SQL, string it together, and use the regular mysql functions
Post Reply