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