4 * Class CRM_Utils_SQL_SelectTest
7 class CRM_Utils_SQL_SelectTest
extends CiviUnitTestCase
{
8 public function testGetDefault() {
9 $select = CRM_Utils_SQL_Select
::from('foo bar');
10 $this->assertLike('SELECT * FROM foo bar', $select->toSQL());
13 public function testExecute_OK_fetch() {
14 $select = CRM_Utils_SQL_Select
::from('civicrm_contact')->select('count(*) as cnt');
15 $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
17 $select = CRM_Utils_SQL_Select
::from('civicrm_contact')
18 ->select('count(*) as cnt');
20 $dao = $select->execute();
21 while ($dao->fetch()) {
23 $this->assertTrue(is_numeric($dao->cnt
), "Expect query to execute");
25 $this->assertEquals(1, $rows);
28 public function testExecute_OK_fetchValue() {
29 $select = CRM_Utils_SQL_Select
::from('civicrm_contact')->select('count(*) as cnt');
30 $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
31 $this->assertTrue(is_numeric($select->execute()->fetchValue()));
34 public function testExecute_OK_fetchAll() {
35 $select = CRM_Utils_SQL_Select
::from('civicrm_contact')->select('count(*) as cnt');
36 $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
37 $records = $select->execute()->fetchAll();
38 $this->assertTrue(is_numeric($records[0]['cnt']));
41 public function testExecute_Error() {
42 $select = CRM_Utils_SQL_Select
::from('civicrm_contact')->select('snarb;barg');
46 $this->fail('Expected an exception');
48 catch (PEAR_Exception
$e) {
49 $this->assertTrue(TRUE, "Received expected exception");
53 public function testGetFields() {
54 $select = CRM_Utils_SQL_Select
::from('foo')
56 ->select(array('whiz', 'bang'));
57 $this->assertLike('SELECT bar, whiz, bang FROM foo', $select->toSQL());
60 public function testWherePlain() {
61 $select = CRM_Utils_SQL_Select
::from('foo')
63 ->where(array('whiz = bang', 'frob > nicate'));
64 $this->assertLike('SELECT * FROM foo WHERE (foo = bar) AND (whiz = bang) AND (frob > nicate)', $select->toSQL());
67 public function testWhereArg() {
68 $select = CRM_Utils_SQL_Select
::from('foo')
69 ->where('foo = @value', array('@value' => 'not"valid'))
70 ->where(array('whiz > @base', 'frob != @base'), array('@base' => 'in"valid'));
71 $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL());
74 public function testGroupByPlain() {
75 $select = CRM_Utils_SQL_Select
::from('foo')
77 ->groupBy(array('whiz_id*2', 'lower(bang)'));
78 $this->assertLike('SELECT * FROM foo GROUP BY bar_id, whiz_id*2, lower(bang)', $select->toSQL());
81 public function testHavingPlain() {
82 $select = CRM_Utils_SQL_Select
::from('foo')
84 ->having('count(id) > 2')
85 ->having(array('sum(id) > 10', 'avg(id) < 200'));
86 $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 200)', $select->toSQL());
89 public function testHavingArg() {
90 $select = CRM_Utils_SQL_Select
::from('foo')
92 ->having('count(id) > #mincnt', array('#mincnt' => 2))
93 ->having(array('sum(id) > #whiz', 'avg(id) < #whiz'), array('#whiz' => 10));
94 $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 10)', $select->toSQL());
97 public function testOrderByPlain() {
98 $select = CRM_Utils_SQL_Select
::from('foo bar')
99 ->orderBy('first asc')
100 ->orderBy(array('second desc', 'third'));
101 $this->assertLike('SELECT * FROM foo bar ORDER BY first asc, second desc, third', $select->toSQL());
104 public function testLimit_defaultOffset() {
105 $select = CRM_Utils_SQL_Select
::from('foo bar')
107 $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 0', $select->toSQL());
110 public function testLimit_withOffset() {
111 $select = CRM_Utils_SQL_Select
::from('foo bar')
113 $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 60', $select->toSQL());
116 public function testLimit_disable() {
117 $select = CRM_Utils_SQL_Select
::from('foo bar')
120 $this->assertLike('SELECT * FROM foo bar', $select->toSQL());
123 public function testModeOutput() {
124 $select = CRM_Utils_SQL_Select
::from('foo', array('mode' => 'out'))
125 ->where('foo = @value')
130 ->param('@value', 'not"valid')
132 '@base' => 'in"valid',
134 $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL());
137 CRM_Utils_SQL_Select
::from('foo', array('mode' => 'out'))
138 ->where('foo = @value', array('@value' => 'not"valid'));
139 $this->fail('In output mode, we should reject requests to interpolate inputs.');
141 catch (Exception
$e) {
142 $this->assertRegExp("/Cannot mix interpolation modes/", $e->getMessage());
145 $outputModeFragment = CRM_Utils_SQL_Select
::fragment()
146 ->param('value', 'whatever');
147 $inputModeFragment = CRM_Utils_SQL_Select
::fragment()
148 ->where('foo = @value', array('@value' => 'not"valid'));
150 $outputModeFragment->merge($inputModeFragment);
151 $this->fail('In output-mode, we should reject requests to merge from input-mode.');
153 catch (Exception
$e) {
154 $this->assertRegExp("/Cannot merge queries that use different interpolation modes/", $e->getMessage());
158 public function testBig() {
159 $select = CRM_Utils_SQL_Select
::from('foo')
161 ->join('rel1', 'INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id')
162 ->join('rel2', 'LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id')
163 ->where('foo.type = @theType', array('@theType' => 'mytype'))
165 ->having('sum(rel1.stat) > 10')
166 ->orderBy('rel2.whiz')
169 "SELECT foo.id FROM foo"
170 . " INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id"
171 . " LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id "
172 . " WHERE (foo.type = \"mytype\")"
174 . " HAVING (sum(rel1.stat) > 10)"
175 . " ORDER BY rel2.whiz"
176 . " LIMIT 100 OFFSET 300",
182 * Parameter-values could include control characters like
183 * '"@" or "!", but they should never be evaluated.
185 public function testNoIterativeInterpolation() {
186 $select = CRM_Utils_SQL_Select
::from('foo')
187 ->where('a = @a and b = @b and c = @c', array(
192 $this->assertLike('SELECT * FROM foo WHERE (a = "@b" and b = "@c" and c = "@a")', $select->toSQL());
195 public function testInterpolate() {
196 $actual = CRM_Utils_SQL_Select
::from('ignore')->interpolate(
197 '@escaped !unescaped #validated',
199 '@escaped' => 'foo"bar',
200 '!unescaped' => 'concat(foo,bar)',
201 '#validated' => 15.2,
204 $this->assertLike('"foo\"bar" concat(foo,bar) 15.2', $actual);
207 public function testInterpolateWildcard() {
208 $actual = CRM_Utils_SQL_Select
::from('ignore')->interpolate(
209 'escaped @escaped unescaped !unescaped validated #validated',
211 'escaped' => 'foo"bar',
212 'unescaped' => 'concat(foo,bar)',
216 $this->assertLike('escaped "foo\"bar" unescaped concat(foo,bar) validated 15.2', $actual);
219 public function testInterpolateUnknown() {
220 $actual = CRM_Utils_SQL_Select
::from('ignore')->interpolate(
221 'escaped @escaped unescaped !unescaped validated #validated',
223 'irrelevant' => 'foo',
226 $this->assertLike('escaped @escaped unescaped !unescaped validated #validated', $actual);
229 public function testInterpolateUnknownStrict() {
231 CRM_Utils_SQL_Select
::from('ignore')
233 ->interpolate('@johnMcClane',
235 'irrelevant' => 'foo',
238 $this->fail('Unknown variables should throw errors in strict mode.');
240 catch (Exception
$e) {
241 $this->assertRegExp('/Cannot build query. Variable "@johnMcClane" is unknown./', $e->getMessage());
245 public function testInterpolateArray() {
246 $actual = CRM_Utils_SQL_Select
::from('ignore')->interpolate(
247 '(@escaped) (!unescaped) (#validated)',
249 '@escaped' => array('foo"bar', "whiz", "null", NULL, "bang"),
250 '!unescaped' => array('foo"bar', 'bar'),
251 '#validated' => array(1, 10, NULL, 100.1),
254 $this->assertLike('("foo\\"bar", "whiz", "null", NULL, "bang") (foo"bar, bar) (1, 10, NULL, 100.1)', $actual);
257 public function testInterpolateBadNumber() {
259 $result = CRM_Utils_SQL_Select
::from('ignore')->interpolate('#num', array(
260 '#num' => '5not-a-number5',
262 $this->fail('Expected exception; got: ' . var_export($result, TRUE));
264 catch (CRM_Core_Exception
$e) {
265 $this->assertTrue(TRUE, "Caught expected exception");
269 $result = CRM_Utils_SQL_Select
::from('ignore')->interpolate('#num', array(
270 '#num' => array(1, '5not-a-number5', 2),
272 $this->fail('Expected exception; got: ' . var_export($result, TRUE));
274 catch (CRM_Core_Exception
$e) {
275 $this->assertTrue(TRUE, "Caught expected exception");
279 public function testMerge() {
280 $fragmentOutMode = CRM_Utils_SQL_Select
::fragment()
281 ->select(array('a', 'b'))
284 $fragmentAutoMode = CRM_Utils_SQL_Select
::fragment()
287 $query = CRM_Utils_SQL_Select
::from('foo')
288 ->select(array('c', 'd'))
291 ->merge($fragmentOutMode)
292 ->merge($fragmentAutoMode);
293 $this->assertLike('SELECT c, d, a, b, e FROM foo WHERE (c = "4") AND (a = 2) AND (whipit())', $query->toSQL());
296 public function testArrayGet() {
297 $select = CRM_Utils_SQL_Select
::from("foo")
298 ->param('hello', 'world');
299 $this->assertEquals('world', $select['hello']);
302 public function testInsertInto_WithDupes() {
303 $select = CRM_Utils_SQL_Select
::from('foo')
304 ->insertInto('bar', array('first', 'second', 'third', 'fourth'))
309 ->where('!field = #value', array('field' => 'zoo', 'value' => 3))
310 ->where('!field = #value', array('field' => 'aviary', 'value' => 3))
311 ->where('!field = #value', array('field' => 'zoo', 'value' => 3))
312 ->groupBy('!colName', array('colName' => 'noodle'))
313 ->groupBy('!colName', array('colName' => 'sauce'))
314 ->groupBy('!colName', array('colName' => 'noodle'));
315 $this->assertLike('INSERT INTO bar (first, second, third, fourth) SELECT fid, 1, fid, 1 FROM foo WHERE (zoo = 3) AND (aviary = 3) GROUP BY noodle, sauce', $select->toSQL());
321 * @param string $message
323 public function assertLike($expected, $actual, $message = '') {
324 $expected = trim((preg_replace('/[ \r\n\t]+/', ' ', $expected)));
325 $actual = trim((preg_replace('/[ \r\n\t]+/', ' ', $actual)));
326 $this->assertEquals($expected, $actual, $message);