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