Page 2 of 2

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 7:18 pm
by Christopher
Ahhhhh....

Re: db abstraction: expressions

Posted: Fri Sep 05, 2008 7:32 pm
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?

Re: db abstraction: expressions

Posted: Sat Sep 06, 2008 5:41 am
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.

Re: db abstraction: expressions

Posted: Sat Sep 06, 2008 7:56 pm
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.

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 5:20 am
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?

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 6:37 am
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.

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 12:27 pm
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.

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 2:22 pm
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.

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 2:40 pm
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)?

Re: db abstraction: expressions

Posted: Sun Sep 07, 2008 9:01 pm
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) . '`';
 

Re: db abstraction: expressions

Posted: Mon Sep 08, 2008 3:48 am
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

Re: db abstraction: expressions

Posted: Mon Sep 08, 2008 4:53 am
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