db abstraction: expressions
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: db abstraction: expressions
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:arborint wrote:The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement.
Code: Select all
$where ('key > ?', 10);Code: Select all
array ('key1' => 10);That should also allow:
Code: Select all
$select->where ('SUBSTRING(name, 1, 1) > ?', $k);Re: db abstraction: expressions
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. Butarborint wrote:I have obviously missed something. Why are you doing that?koen.h wrote:Have been playing with:
Code: Select all
fx.concat(fx.substr(a, 1, 1), b) fx.eq c
Code: Select all
$q->where ('key>','value');- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: db abstraction: expressions
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.koen.h wrote:in your example seems mixing the two syntax wise.Code: Select all
$q->where ('key>','value');
(#10850)
Re: db abstraction: expressions
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
What about this:
How this could work:
This returns the expression ready to be bound by parameters.
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();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)
Re: db abstraction: expressions
I see I haven't been really consistent with static calls later and $db->expr->fx() first. But the idea should be clear.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: db abstraction: expressions
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 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?
No. It seems like either fluent calls, or just functions (rather than static method calls) would be cleaner.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.
(#10850)
Re: db abstraction: expressions
So I'm currently at the following:arborint wrote:No. It seems like either fluent calls, or just functions (rather than static method calls) would be cleaner.
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();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)?I think it would be really rare for me to use a variable as a column name.
Re: db abstraction: expressions
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);
}
Code: Select all
$sql = '`' . cleanField($fieldName) . '`';
Re: db abstraction: expressions
I don't think this will be sufficient for international character sets.jshpro2 wrote:Just make sure when you use it you do it like soCode: 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); }Code: Select all
$sql = '`' . cleanField($fieldName) . '`';
http://dev.mysql.com/doc/refman/5.0/en/ ... mysql.html
Re: db abstraction: expressions
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