From e2b5e5b16cff3e3ca9ca15ee331d550ac14aa934 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Fri, 21 Feb 2014 14:10:18 -0800 Subject: [PATCH] CRM_Utils_SQL_Select - Backport --- CRM/Utils/SQL/Select.php | 208 +++++++++++++++++++++ tests/phpunit/CRM/Utils/SQL/SelectTest.php | 133 +++++++++++++ 2 files changed, 341 insertions(+) create mode 100644 CRM/Utils/SQL/Select.php create mode 100644 tests/phpunit/CRM/Utils/SQL/SelectTest.php diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php new file mode 100644 index 0000000000..f8d6031cc2 --- /dev/null +++ b/CRM/Utils/SQL/Select.php @@ -0,0 +1,208 @@ +join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id') + * ->where('activity_type_id = #type', array('#type' => 234)) + * ->where('status_id IN (#statuses)', array('#statuses' => array(1,2,3)) + * ->where('subject like @subj', array('@subj' => '%hello%')) + * ->where('!dynamicColumn = 1', array('!dynamicColumn' => 'coalesce(is_active,0)')) + * ->where('!column = @value', array( + * '!column' => $customField->column_name, + * '@value' => $form['foo'] + * )) + * echo $select->toSQL(); + * + * Design principles: + * - Portable + * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString) + * - No knowledge of the underlying data model + * - Single file + * - SQL clauses correspond to PHP functions ($select->where("foo_id=123")) + * - Variable escaping is concise and controllable based on prefixes, eg + * - similar to Drupal's t() + * - use "@varname" to insert the escaped value + * - use "!varname" to insert raw (unescaped) values + * - use "#varname" to insert a numerical value (these are validated but not escaped) + * - to disable any preprocessing, simply omit the variable list + * - Variables may be individual values or arrays; arrays are imploded with commas + * - Conditionals are AND'd; if you need OR's, do it yourself + */ +class CRM_Utils_SQL_Select { + private $selects = array(); + private $from; + private $joins = array(); + private $wheres = array(); + private $groupBys = array(); + private $havings = array(); + private $orderBys = array(); + + /** + * Create a new SELECT query + * + * @param $from + * @return CRM_Utils_SQL_Select + */ + public static function from($from) { + return new self($from); + } + + public function __construct($from) { + $this->from = $from; + } + + public function join($name, $expr, $args = NULL) { + $this->joins[$name] = $this->interpolate($expr, $args); + return $this; + } + + /** + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Select + */ + public function select($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $this->selects[$expr] = $this->interpolate($expr, $args); + } + return $this; + } + + /** + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Select + */ + public function where($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $this->wheres[$expr] = $this->interpolate($expr, $args); + } + return $this; + } + + /** + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Select + */ + public function groupBy($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $this->groupBys[$expr] = $this->interpolate($expr, $args); + } + return $this; + } + + /** + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Select + */ + public function having($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $this->havings[$expr] = $this->interpolate($expr, $args); + } + return $this; + } + + /** + * @param string|array $exprs list of SQL expressions + * @param null|array $args use NULL to disable interpolation; use an array of variables to enable + * @return CRM_Utils_SQL_Select + */ + public function orderBy($exprs, $args = NULL) { + $exprs = (array) $exprs; + foreach ($exprs as $expr) { + $this->orderBys[$expr] = $this->interpolate($expr, $args); + } + return $this; + } + + /** + * Given a string like "field_name = @value", replace "@value" with an escaped SQL string + * + * @param string SQL expression + * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: + * prefix '@' => escape SQL + * prefix '#' => literal number, skip escaping but do validation + * prefix '!' => literal, skip escaping and validation + * if a value is an array, then it will be imploded + * + * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. + * + * @return string SQL expression + */ + public function interpolate($expr, $args) { + if ($args === NULL) { + return $expr; + } + else { + foreach (array_keys($args) as $key) { + $values = is_array($args[$key]) ? $args[$key] : array($args[$key]); + if ($key{0} == '@') { + $parts = array_map(array($this, 'escapeString'), $values); + $args[$key] = implode(', ', $parts); + } + elseif ($key{0} == '!') { + $args[$key] = implode(', ', $values); + } + elseif ($key{0} == '#') { + foreach ($values as $valueKey => $value) { + if ($value === NULL) { + $values[$valueKey] = 'NULL'; + } + elseif (!is_numeric($value)) { + //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE)); + throw new CRM_Core_Exception("Failed encoding non-numeric value"); + } + } + $args[$key] = implode(', ', $values); + } + else { + throw new CRM_Core_Exception("Bad SQL parameter key: $key"); + } + } + return strtr($expr, $args); + } + } + + protected function escapeString($value) { + return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; + } + + /** + * @return string SQL statement + */ + public function toSQL() { + if ($this->selects) { + $sql = 'SELECT ' . implode(', ', $this->selects) . "\n"; + } + else { + $sql = 'SELECT *' . "\n"; + } + $sql .= 'FROM ' . $this->from . "\n"; + foreach ($this->joins as $join) { + $sql .= $join . "\n"; + } + if ($this->wheres) { + $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n"; + } + if ($this->groupBys) { + $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n"; + } + if ($this->havings) { + $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n"; + } + if ($this->orderBys) { + $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n"; + } + return $sql; + } +} diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php new file mode 100644 index 0000000000..5d2f9f7bf5 --- /dev/null +++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php @@ -0,0 +1,133 @@ +assertLike('SELECT * FROM foo bar', $select->toSQL()); + } + + function testGetFields() { + $select = CRM_Utils_SQL_Select::from('foo') + ->select('bar') + ->select(array('whiz', 'bang')); + $this->assertLike('SELECT bar, whiz, bang FROM foo', $select->toSQL()); + } + + function testWherePlain() { + $select = CRM_Utils_SQL_Select::from('foo') + ->where('foo = bar') + ->where(array('whiz = bang', 'frob > nicate')); + $this->assertLike('SELECT * FROM foo WHERE (foo = bar) AND (whiz = bang) AND (frob > nicate)', $select->toSQL()); + } + + function testWhereArg() { + $select = CRM_Utils_SQL_Select::from('foo') + ->where('foo = @value', array('@value' => 'not"valid')) + ->where(array('whiz > @base', 'frob != @base'), array('@base' => 'in"valid')); + $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL()); + } + + function testGroupByPlain() { + $select = CRM_Utils_SQL_Select::from('foo') + ->groupBy("bar_id") + ->groupBy(array('whiz_id*2', 'lower(bang)')); + $this->assertLike('SELECT * FROM foo GROUP BY bar_id, whiz_id*2, lower(bang)', $select->toSQL()); + } + + function testHavingPlain() { + $select = CRM_Utils_SQL_Select::from('foo') + ->groupBy("bar_id") + ->having('count(id) > 2') + ->having(array('sum(id) > 10', 'avg(id) < 200')); + $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 200)', $select->toSQL()); + } + + function testHavingArg() { + $select = CRM_Utils_SQL_Select::from('foo') + ->groupBy("bar_id") + ->having('count(id) > #mincnt', array('#mincnt' => 2)) + ->having(array('sum(id) > #whiz', 'avg(id) < #whiz'), array('#whiz' => 10)); + $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 10)', $select->toSQL()); + } + + function testBig() { + $select = CRM_Utils_SQL_Select::from('foo') + ->select('foo.id') + ->join('rel1', 'INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id') + ->join('rel2', 'LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id') + ->where('foo.type = @type', array('@type' => 'mytype')) + ->groupBy("foo.id") + ->having('sum(rel1.stat) > 10'); + $this->assertLike( + "SELECT foo.id FROM foo" + . " INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id" + . " LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id " + . " WHERE (foo.type = \"mytype\")" + . " GROUP BY foo.id" + . " HAVING (sum(rel1.stat) > 10)", + $select->toSQL() + ); + } + + function testInterpolate() { + $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( + '@escaped !unescaped #validated', + array( + '@escaped' => 'foo"bar', + '!unescaped' => 'concat(foo,bar)', + '#validated' => 15.2 + ) + ); + $this->assertLike('"foo\"bar" concat(foo,bar) 15.2', $actual); + } + + function testInterpolateArray() { + $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( + '(@escaped) (!unescaped) (#validated)', + array( + '@escaped' => array('foo"bar', "whiz", "null", null, "bang"), + '!unescaped' => array('foo"bar', 'bar'), + '#validated' => array(1, 10, null, 100.1) + ) + ); + $this->assertLike('("foo\\"bar", "whiz", "null", NULL, "bang") (foo"bar, bar) (1, 10, NULL, 100.1)', $actual); + } + + function testInterpolateBadNumber() { + try { + $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array( + '#num' => '5not-a-number5' + )); + $this->fail('Expected exception; got: ' . var_export($result, TRUE)); + } catch (CRM_Core_Exception $e) { + $this->assertTrue(TRUE, "Caught expected exception"); + } + + try { + $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array( + '#num' => array(1, '5not-a-number5', 2) + )); + $this->fail('Expected exception; got: ' . var_export($result, TRUE)); + } catch (CRM_Core_Exception $e) { + $this->assertTrue(TRUE, "Caught expected exception"); + } + } + + function testInterpolateBadKey() { + try { + $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('this is a {var}', array( + '{var}' => 'not a well-formed variable name' + )); + $this->fail('Expected exception; got: ' . var_export($result, TRUE)); + } catch (CRM_Core_Exception $e) { + $this->assertTrue(TRUE, "Caught expected exception"); + } + } + + function assertLike($expected, $actual, $message = '') { + $expected = trim((preg_replace('/[ \r\n\t]+/', ' ', $expected))); + $actual = trim((preg_replace('/[ \r\n\t]+/', ' ', $actual))); + $this->assertEquals($expected, $actual, $message); + } +} \ No newline at end of file -- 2.25.1