Commit | Line | Data |
---|---|---|
e2b5e5b1 | 1 | <?php |
e2b5e5b1 | 2 | |
aba1cd8b EM |
3 | /** |
4 | * Class CRM_Utils_SQL_SelectTest | |
acb109b7 | 5 | * @group headless |
aba1cd8b | 6 | */ |
e2b5e5b1 | 7 | class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase { |
00be9182 | 8 | public function testGetDefault() { |
e2b5e5b1 TO |
9 | $select = CRM_Utils_SQL_Select::from('foo bar'); |
10 | $this->assertLike('SELECT * FROM foo bar', $select->toSQL()); | |
11 | } | |
12 | ||
77e74ae1 TO |
13 | public function testExecute_OK() { |
14 | // We need some SQL query. | |
15 | $select = CRM_Utils_SQL_Select::from('civicrm_contact') | |
16 | ->select('count(*) as cnt'); | |
17 | $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL()); | |
18 | ||
19 | // Try with typical fetch(). | |
20 | $rows = 0; | |
21 | $dao = $select->execute(); | |
22 | while ($dao->fetch()) { | |
23 | $rows++; | |
24 | $this->assertTrue(is_numeric($dao->cnt), "Expect query to execute"); | |
25 | } | |
26 | $this->assertEquals(1, $rows); | |
27 | ||
28 | // Try with fetchValue(). | |
29 | $this->assertTrue(is_numeric($select->execute()->fetchValue())); | |
30 | ||
31 | // Try with fetchAll() | |
32 | $records = $select->execute()->fetchAll(); | |
33 | $this->assertTrue(is_numeric($records[0]['cnt'])); | |
34 | } | |
35 | ||
36 | public function testExecute_Error() { | |
37 | try { | |
38 | CRM_Utils_SQL_Select::from('civicrm_contact')->select('snarb;barg')->execute(); | |
39 | $this->fail('Expected an exception'); | |
40 | } | |
41 | catch (PEAR_Exception $e) { | |
42 | $this->assertTrue(TRUE, "Received expected exception"); | |
43 | } | |
44 | } | |
45 | ||
00be9182 | 46 | public function testGetFields() { |
e2b5e5b1 TO |
47 | $select = CRM_Utils_SQL_Select::from('foo') |
48 | ->select('bar') | |
49 | ->select(array('whiz', 'bang')); | |
50 | $this->assertLike('SELECT bar, whiz, bang FROM foo', $select->toSQL()); | |
51 | } | |
52 | ||
00be9182 | 53 | public function testWherePlain() { |
e2b5e5b1 TO |
54 | $select = CRM_Utils_SQL_Select::from('foo') |
55 | ->where('foo = bar') | |
56 | ->where(array('whiz = bang', 'frob > nicate')); | |
57 | $this->assertLike('SELECT * FROM foo WHERE (foo = bar) AND (whiz = bang) AND (frob > nicate)', $select->toSQL()); | |
58 | } | |
59 | ||
00be9182 | 60 | public function testWhereArg() { |
e2b5e5b1 TO |
61 | $select = CRM_Utils_SQL_Select::from('foo') |
62 | ->where('foo = @value', array('@value' => 'not"valid')) | |
63 | ->where(array('whiz > @base', 'frob != @base'), array('@base' => 'in"valid')); | |
64 | $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL()); | |
65 | } | |
66 | ||
00be9182 | 67 | public function testGroupByPlain() { |
e2b5e5b1 TO |
68 | $select = CRM_Utils_SQL_Select::from('foo') |
69 | ->groupBy("bar_id") | |
70 | ->groupBy(array('whiz_id*2', 'lower(bang)')); | |
71 | $this->assertLike('SELECT * FROM foo GROUP BY bar_id, whiz_id*2, lower(bang)', $select->toSQL()); | |
72 | } | |
73 | ||
00be9182 | 74 | public function testHavingPlain() { |
e2b5e5b1 TO |
75 | $select = CRM_Utils_SQL_Select::from('foo') |
76 | ->groupBy("bar_id") | |
77 | ->having('count(id) > 2') | |
78 | ->having(array('sum(id) > 10', 'avg(id) < 200')); | |
79 | $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 200)', $select->toSQL()); | |
80 | } | |
81 | ||
00be9182 | 82 | public function testHavingArg() { |
e2b5e5b1 TO |
83 | $select = CRM_Utils_SQL_Select::from('foo') |
84 | ->groupBy("bar_id") | |
85 | ->having('count(id) > #mincnt', array('#mincnt' => 2)) | |
86 | ->having(array('sum(id) > #whiz', 'avg(id) < #whiz'), array('#whiz' => 10)); | |
87 | $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 10)', $select->toSQL()); | |
88 | } | |
89 | ||
00be9182 | 90 | public function testOrderByPlain() { |
c3a8e3e5 TO |
91 | $select = CRM_Utils_SQL_Select::from('foo bar') |
92 | ->orderBy('first asc') | |
93 | ->orderBy(array('second desc', 'third')); | |
94 | $this->assertLike('SELECT * FROM foo bar ORDER BY first asc, second desc, third', $select->toSQL()); | |
95 | } | |
96 | ||
00be9182 | 97 | public function testLimit_defaultOffset() { |
c3a8e3e5 TO |
98 | $select = CRM_Utils_SQL_Select::from('foo bar') |
99 | ->limit(20); | |
100 | $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 0', $select->toSQL()); | |
101 | } | |
102 | ||
00be9182 | 103 | public function testLimit_withOffset() { |
c3a8e3e5 TO |
104 | $select = CRM_Utils_SQL_Select::from('foo bar') |
105 | ->limit(20, 60); | |
106 | $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 60', $select->toSQL()); | |
107 | } | |
108 | ||
00be9182 | 109 | public function testLimit_disable() { |
c3a8e3e5 TO |
110 | $select = CRM_Utils_SQL_Select::from('foo bar') |
111 | ->limit(20, 60) | |
112 | ->limit(NULL, NULL); | |
113 | $this->assertLike('SELECT * FROM foo bar', $select->toSQL()); | |
114 | } | |
115 | ||
c5458931 TO |
116 | public function testModeOutput() { |
117 | $select = CRM_Utils_SQL_Select::from('foo', array('mode' => 'out')) | |
118 | ->where('foo = @value') | |
119 | ->where(array( | |
120 | 'whiz > @base', | |
121 | 'frob != @base', | |
122 | )) | |
123 | ->param('@value', 'not"valid') | |
124 | ->param(array( | |
125 | '@base' => 'in"valid', | |
126 | )); | |
127 | $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL()); | |
128 | ||
129 | try { | |
130 | CRM_Utils_SQL_Select::from('foo', array('mode' => 'out')) | |
131 | ->where('foo = @value', array('@value' => 'not"valid')); | |
132 | $this->fail('In output mode, we should reject requests to interpolate inputs.'); | |
133 | } | |
134 | catch (Exception $e) { | |
135 | $this->assertRegExp("/Cannot mix interpolation modes/", $e->getMessage()); | |
136 | } | |
137 | ||
138 | $outputModeFragment = CRM_Utils_SQL_Select::fragment() | |
139 | ->param('value', 'whatever'); | |
140 | $inputModeFragment = CRM_Utils_SQL_Select::fragment() | |
141 | ->where('foo = @value', array('@value' => 'not"valid')); | |
142 | try { | |
143 | $outputModeFragment->merge($inputModeFragment); | |
144 | $this->fail('In output-mode, we should reject requests to merge from input-mode.'); | |
145 | } | |
146 | catch (Exception $e) { | |
147 | $this->assertRegExp("/Cannot merge queries that use different interpolation modes/", $e->getMessage()); | |
148 | } | |
149 | } | |
150 | ||
00be9182 | 151 | public function testBig() { |
e2b5e5b1 TO |
152 | $select = CRM_Utils_SQL_Select::from('foo') |
153 | ->select('foo.id') | |
154 | ->join('rel1', 'INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id') | |
155 | ->join('rel2', 'LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id') | |
c5458931 | 156 | ->where('foo.type = @theType', array('@theType' => 'mytype')) |
e2b5e5b1 | 157 | ->groupBy("foo.id") |
c3a8e3e5 TO |
158 | ->having('sum(rel1.stat) > 10') |
159 | ->orderBy('rel2.whiz') | |
160 | ->limit(100, 300); | |
e2b5e5b1 TO |
161 | $this->assertLike( |
162 | "SELECT foo.id FROM foo" | |
92915c55 TO |
163 | . " INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id" |
164 | . " LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id " | |
165 | . " WHERE (foo.type = \"mytype\")" | |
166 | . " GROUP BY foo.id" | |
167 | . " HAVING (sum(rel1.stat) > 10)" | |
168 | . " ORDER BY rel2.whiz" | |
169 | . " LIMIT 100 OFFSET 300", | |
e2b5e5b1 TO |
170 | $select->toSQL() |
171 | ); | |
172 | } | |
173 | ||
c5458931 TO |
174 | /** |
175 | * Parameter-values could include control characters like | |
176 | * '"@" or "!", but they should never be evaluated. | |
177 | */ | |
178 | public function testNoIterativeInterpolation() { | |
179 | $select = CRM_Utils_SQL_Select::from('foo') | |
180 | ->where('a = @a and b = @b and c = @c', array( | |
181 | 'a' => '@b', | |
182 | 'b' => '@c', | |
183 | 'c' => '@a', | |
184 | )); | |
185 | $this->assertLike('SELECT * FROM foo WHERE (a = "@b" and b = "@c" and c = "@a")', $select->toSQL()); | |
186 | } | |
187 | ||
00be9182 | 188 | public function testInterpolate() { |
e2b5e5b1 TO |
189 | $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( |
190 | '@escaped !unescaped #validated', | |
191 | array( | |
192 | '@escaped' => 'foo"bar', | |
193 | '!unescaped' => 'concat(foo,bar)', | |
21dfd5f5 | 194 | '#validated' => 15.2, |
e2b5e5b1 TO |
195 | ) |
196 | ); | |
197 | $this->assertLike('"foo\"bar" concat(foo,bar) 15.2', $actual); | |
198 | } | |
199 | ||
c5458931 TO |
200 | public function testInterpolateWildcard() { |
201 | $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( | |
202 | 'escaped @escaped unescaped !unescaped validated #validated', | |
203 | array( | |
204 | 'escaped' => 'foo"bar', | |
205 | 'unescaped' => 'concat(foo,bar)', | |
206 | 'validated' => 15.2, | |
207 | ) | |
208 | ); | |
209 | $this->assertLike('escaped "foo\"bar" unescaped concat(foo,bar) validated 15.2', $actual); | |
210 | } | |
211 | ||
212 | public function testInterpolateUnknown() { | |
213 | $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( | |
214 | 'escaped @escaped unescaped !unescaped validated #validated', | |
215 | array( | |
216 | 'irrelevant' => 'foo', | |
217 | ) | |
218 | ); | |
219 | $this->assertLike('escaped @escaped unescaped !unescaped validated #validated', $actual); | |
220 | } | |
221 | ||
222 | public function testInterpolateUnknownStrict() { | |
223 | try { | |
224 | CRM_Utils_SQL_Select::from('ignore') | |
225 | ->strict() | |
226 | ->interpolate('@johnMcClane', | |
227 | array( | |
228 | 'irrelevant' => 'foo', | |
229 | ) | |
230 | ); | |
231 | $this->fail('Unknown variables should throw errors in strict mode.'); | |
232 | } | |
233 | catch (Exception $e) { | |
234 | $this->assertRegExp('/Cannot build query. Variable "@johnMcClane" is unknown./', $e->getMessage()); | |
235 | } | |
236 | } | |
237 | ||
00be9182 | 238 | public function testInterpolateArray() { |
e2b5e5b1 TO |
239 | $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate( |
240 | '(@escaped) (!unescaped) (#validated)', | |
241 | array( | |
6c6e6187 | 242 | '@escaped' => array('foo"bar', "whiz", "null", NULL, "bang"), |
e2b5e5b1 | 243 | '!unescaped' => array('foo"bar', 'bar'), |
21dfd5f5 | 244 | '#validated' => array(1, 10, NULL, 100.1), |
e2b5e5b1 TO |
245 | ) |
246 | ); | |
247 | $this->assertLike('("foo\\"bar", "whiz", "null", NULL, "bang") (foo"bar, bar) (1, 10, NULL, 100.1)', $actual); | |
248 | } | |
249 | ||
00be9182 | 250 | public function testInterpolateBadNumber() { |
e2b5e5b1 TO |
251 | try { |
252 | $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array( | |
21dfd5f5 | 253 | '#num' => '5not-a-number5', |
e2b5e5b1 TO |
254 | )); |
255 | $this->fail('Expected exception; got: ' . var_export($result, TRUE)); | |
0db6c3e1 TO |
256 | } |
257 | catch (CRM_Core_Exception $e) { | |
e2b5e5b1 TO |
258 | $this->assertTrue(TRUE, "Caught expected exception"); |
259 | } | |
260 | ||
261 | try { | |
262 | $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array( | |
21dfd5f5 | 263 | '#num' => array(1, '5not-a-number5', 2), |
e2b5e5b1 TO |
264 | )); |
265 | $this->fail('Expected exception; got: ' . var_export($result, TRUE)); | |
0db6c3e1 TO |
266 | } |
267 | catch (CRM_Core_Exception $e) { | |
e2b5e5b1 TO |
268 | $this->assertTrue(TRUE, "Caught expected exception"); |
269 | } | |
270 | } | |
271 | ||
c5458931 TO |
272 | public function testMerge() { |
273 | $fragmentOutMode = CRM_Utils_SQL_Select::fragment() | |
274 | ->select(array('a', 'b')) | |
275 | ->where('a = #two') | |
276 | ->param('two', 2); | |
277 | $fragmentAutoMode = CRM_Utils_SQL_Select::fragment() | |
278 | ->select('e') | |
279 | ->where('whipit()'); | |
280 | $query = CRM_Utils_SQL_Select::from('foo') | |
281 | ->select(array('c', 'd')) | |
282 | ->where('c = @four') | |
283 | ->param('four', 4) | |
284 | ->merge($fragmentOutMode) | |
285 | ->merge($fragmentAutoMode); | |
286 | $this->assertLike('SELECT c, d, a, b, e FROM foo WHERE (c = "4") AND (a = 2) AND (whipit())', $query->toSQL()); | |
287 | } | |
288 | ||
289 | public function testArrayGet() { | |
290 | $select = CRM_Utils_SQL_Select::from("foo") | |
291 | ->param('hello', 'world'); | |
292 | $this->assertEquals('world', $select['hello']); | |
e2b5e5b1 TO |
293 | } |
294 | ||
6e6685dc C |
295 | public function testInsertInto_WithDupes() { |
296 | $select = CRM_Utils_SQL_Select::from('foo') | |
297 | ->insertInto('bar', array('first', 'second', 'third', 'fourth')) | |
298 | ->select('fid') | |
299 | ->select('1') | |
300 | ->select('fid') | |
301 | ->select('1') | |
302 | ->where('!field = #value', array('field' => 'zoo', 'value' => 3)) | |
303 | ->where('!field = #value', array('field' => 'aviary', 'value' => 3)) | |
304 | ->where('!field = #value', array('field' => 'zoo', 'value' => 3)) | |
305 | ->groupBy('!colName', array('colName' => 'noodle')) | |
306 | ->groupBy('!colName', array('colName' => 'sauce')) | |
307 | ->groupBy('!colName', array('colName' => 'noodle')); | |
308 | $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()); | |
309 | } | |
310 | ||
4cbe18b8 EM |
311 | /** |
312 | * @param $expected | |
313 | * @param $actual | |
314 | * @param string $message | |
315 | */ | |
00be9182 | 316 | public function assertLike($expected, $actual, $message = '') { |
e2b5e5b1 TO |
317 | $expected = trim((preg_replace('/[ \r\n\t]+/', ' ', $expected))); |
318 | $actual = trim((preg_replace('/[ \r\n\t]+/', ' ', $actual))); | |
319 | $this->assertEquals($expected, $actual, $message); | |
320 | } | |
96025800 | 321 | |
4cbe18b8 | 322 | } |