[CLEANUP] Replace strlen() with === for zero length check
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Classes / Database / SqlParser.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Database;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use TYPO3\CMS\Core\Utility\GeneralUtility;
18
19 /**
20 * PHP SQL engine / server
21 *
22 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
23 * @author Karsten Dambekalns <karsten@typo3.org>
24 * @author Xavier Perseguers <xavier@typo3.org>
25 */
26 class SqlParser extends \TYPO3\CMS\Core\Database\SqlParser {
27
28 /**
29 * @var DatabaseConnection
30 */
31 protected $databaseConnection;
32
33 /**
34 * @param DatabaseConnection $databaseConnection
35 */
36 public function __construct(DatabaseConnection $databaseConnection = NULL) {
37 parent::__construct();
38
39 $this->databaseConnection = $databaseConnection ?: $GLOBALS['TYPO3_DB'];
40 }
41
42 /**
43 * Gets value in quotes from $parseString.
44 *
45 * @param string $parseString String from which to find value in quotes. Notice that $parseString is passed by reference and is shortened by the output of this function.
46 * @param string $quote The quote used; input either " or '
47 * @return string The value, passed through parseStripslashes()!
48 */
49 protected function getValueInQuotes(&$parseString, $quote) {
50 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
51 case 'adodb':
52 if ($this->databaseConnection->runningADOdbDriver('mssql')) {
53 $value = $this->getValueInQuotesMssql($parseString, $quote);
54 } else {
55 $value = parent::getValueInQuotes($parseString, $quote);
56 }
57 break;
58 default:
59 $value = parent::getValueInQuotes($parseString, $quote);
60 }
61 return $value;
62 }
63
64 /**
65 * Gets value in quotes from $parseString. This method targets MSSQL exclusively.
66 *
67 * @param string $parseString String from which to find value in quotes. Notice that $parseString is passed by reference and is shortened by the output of this function.
68 * @param string $quote The quote used; input either " or '
69 * @return string
70 */
71 protected function getValueInQuotesMssql(&$parseString, $quote) {
72 $previousIsQuote = FALSE;
73 $inQuote = FALSE;
74 // Go through the whole string
75 for ($c = 0; $c < strlen($parseString); $c++) {
76 // If the parsed string character is the quote string
77 if ($parseString[$c] === $quote) {
78 // If we are already in a quote
79 if ($inQuote) {
80 // Was the previous a quote?
81 if ($previousIsQuote) {
82 // If yes, replace it by a \
83 $parseString[$c - 1] = '\\';
84 }
85 // Invert the state
86 $previousIsQuote = !$previousIsQuote;
87 } else {
88 // So we are in a quote since now
89 $inQuote = TRUE;
90 }
91 } elseif ($inQuote && $previousIsQuote) {
92 $inQuote = FALSE;
93 $previousIsQuote = FALSE;
94 } else {
95 $previousIsQuote = FALSE;
96 }
97 }
98 $parts = explode($quote, substr($parseString, 1));
99 $buffer = '';
100 foreach ($parts as $v) {
101 $buffer .= $v;
102 $reg = array();
103 preg_match('/\\\\$/', $v, $reg);
104 if ($reg && strlen($reg[0]) % 2) {
105 $buffer .= $quote;
106 } else {
107 $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
108 return $this->parseStripslashes($buffer);
109 }
110 }
111 return '';
112 }
113
114 /**
115 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
116 * Can also compile field lists for ORDER BY and GROUP BY.
117 *
118 * @param array $selectFields Array of select fields, (made with ->parseFieldList())
119 * @param bool $compileComments Whether comments should be compiled
120 * @param bool $functionMapping Whether function mapping should take place
121 * @return string Select field string
122 * @see parseFieldList()
123 */
124 public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {
125 $output = '';
126 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
127 case 'native':
128 $output = parent::compileFieldList($selectFields, $compileComments);
129 break;
130 case 'adodb':
131 // Traverse the selectFields if any:
132 if (is_array($selectFields)) {
133 $outputParts = array();
134 foreach ($selectFields as $k => $v) {
135 // Detecting type:
136 switch ($v['type']) {
137 case 'function':
138 $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
139 break;
140 case 'flow-control':
141 if ($v['flow-control']['type'] === 'CASE') {
142 $outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);
143 }
144 break;
145 case 'field':
146 $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
147 break;
148 }
149 // Alias:
150 if ($v['as']) {
151 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
152 }
153 // Specifically for ORDER BY and GROUP BY field lists:
154 if ($v['sortDir']) {
155 $outputParts[$k] .= ' ' . $v['sortDir'];
156 }
157 }
158 // @todo Handle SQL hints in comments according to current DBMS
159 if (FALSE && $selectFields[0]['comments']) {
160 $output = $selectFields[0]['comments'] . ' ';
161 }
162 $output .= implode(', ', $outputParts);
163 }
164 break;
165 }
166 return $output;
167 }
168
169 /**
170 * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
171 *
172 * @param array $components Array of case components, (made with ->parseCaseStatement())
173 * @param bool $functionMapping Whether function mapping should take place
174 * @return string case when string
175 * @see parseCaseStatement()
176 */
177 protected function compileCaseStatement(array $components, $functionMapping = TRUE) {
178 $output = '';
179 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
180 case 'native':
181 $output = parent::compileCaseStatement($components);
182 break;
183 case 'adodb':
184 $statement = 'CASE';
185 if (isset($components['case_field'])) {
186 $statement .= ' ' . $components['case_field'];
187 } elseif (isset($components['case_value'])) {
188 $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
189 }
190 foreach ($components['when'] as $when) {
191 $statement .= ' WHEN ';
192 $statement .= $this->compileWhereClause($when['when_value'], $functionMapping);
193 $statement .= ' THEN ';
194 $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
195 }
196 if (isset($components['else'])) {
197 $statement .= ' ELSE ';
198 $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
199 }
200 $statement .= ' END';
201 $output = $statement;
202 break;
203 }
204 return $output;
205 }
206
207 /**
208 * Add slashes function used for compiling queries
209 * This method overrides the method from \TYPO3\CMS\Core\Database\SqlParser because
210 * the input string is already properly escaped.
211 *
212 * @param string $str Input string
213 * @return string Output string
214 */
215 protected function compileAddslashes($str) {
216 return $str;
217 }
218
219 /*************************
220 *
221 * Compiling queries
222 *
223 *************************/
224 /**
225 * Compiles an INSERT statement from components array
226 *
227 * @param array Array of SQL query components
228 * @return string SQL INSERT query / array
229 * @see parseINSERT()
230 */
231 protected function compileINSERT($components) {
232 $query = '';
233 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
234 case 'native':
235 $query = parent::compileINSERT($components);
236 break;
237 case 'adodb':
238 $values = array();
239 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
240 $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);
241 $tableFields = array_keys($this->databaseConnection->cache_fieldType[$components['TABLE']]);
242 } else {
243 $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);
244 $tableFields = array_keys($valuesComponents[0]);
245 }
246 foreach ($valuesComponents as $valuesComponent) {
247 $fields = array();
248 $fc = 0;
249 foreach ($valuesComponent as $fV) {
250 $fields[$tableFields[$fc++]] = $fV[0];
251 }
252 $values[] = $fields;
253 }
254 $query = count($values) === 1 ? $values[0] : $values;
255 break;
256 }
257 return $query;
258 }
259
260 /**
261 * Compiles a CREATE TABLE statement from components array
262 *
263 * @param array $components Array of SQL query components
264 * @return array array with SQL CREATE TABLE/INDEX command(s)
265 * @see parseCREATETABLE()
266 */
267 public function compileCREATETABLE($components) {
268 $query = array();
269 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
270 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]['type']) {
271 case 'native':
272 $query[] = parent::compileCREATETABLE($components);
273 break;
274 case 'adodb':
275 // Create fields and keys:
276 $fieldsKeys = array();
277 $indexKeys = array();
278 foreach ($components['FIELDS'] as $fN => $fCfg) {
279 $handlerKey = $this->databaseConnection->handler_getFromTableList($components['TABLE']);
280 $fieldsKeys[$fN] = $this->databaseConnection->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
281 }
282 if (isset($components['KEYS']) && is_array($components['KEYS'])) {
283 foreach ($components['KEYS'] as $kN => $kCfg) {
284 if ($kN === 'PRIMARYKEY') {
285 foreach ($kCfg as $field) {
286 $fieldsKeys[$field] .= ' PRIMARY';
287 }
288 } elseif ($kN === 'UNIQUE') {
289 foreach ($kCfg as $n => $field) {
290 $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));
291 }
292 } else {
293 $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));
294 }
295 }
296 }
297 // Generally create without OID on PostgreSQL
298 $tableOptions = array('postgres' => 'WITHOUT OIDS');
299 // Fetch table/index generation query:
300 $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);
301 $query = array_merge($this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . LF, $fieldsKeys), $tableOptions), $indexKeys);
302 break;
303 }
304 return $query;
305 }
306
307 /**
308 * Compiles an ALTER TABLE statement from components array
309 *
310 * @param array Array of SQL query components
311 * @return string SQL ALTER TABLE query
312 * @see parseALTERTABLE()
313 */
314 public function compileALTERTABLE($components) {
315 $query = '';
316 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
317 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
318 case 'native':
319 $query = parent::compileALTERTABLE($components);
320 break;
321 case 'adodb':
322 $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);
323 $fieldName = $this->databaseConnection->quoteName($components['FIELD'], NULL, TRUE);
324 switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {
325 case 'ADD':
326 $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
327 break;
328 case 'CHANGE':
329 $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
330 break;
331 case 'DROP':
332
333 case 'DROPKEY':
334 break;
335 case 'ADDKEY':
336
337 case 'ADDPRIMARYKEY':
338
339 case 'ADDUNIQUE':
340 $query .= ' (' . implode(',', $components['fields']) . ')';
341 break;
342 case 'DEFAULTCHARACTERSET':
343
344 case 'ENGINE':
345 // @todo ???
346 break;
347 }
348 break;
349 }
350 return $query;
351 }
352
353 /**
354 * Compile field definition
355 *
356 * @param array $fieldCfg Field definition parts
357 * @return string Field definition string
358 */
359 public function compileFieldCfg($fieldCfg) {
360 $cfg = '';
361 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
362 case 'native':
363 $cfg = parent::compileFieldCfg($fieldCfg);
364 break;
365 case 'adodb':
366 // Set type:
367 $type = $this->databaseConnection->MySQLMetaType($fieldCfg['fieldType']);
368 $cfg = $type;
369 // Add value, if any:
370 if ((string)$fieldCfg['value'] !== '' && in_array($type, array('C', 'C2'))) {
371 $cfg .= ' ' . $fieldCfg['value'];
372 } elseif (!isset($fieldCfg['value']) && in_array($type, array('C', 'C2'))) {
373 $cfg .= ' 255';
374 }
375 // Add additional features:
376 $noQuote = TRUE;
377 if (is_array($fieldCfg['featureIndex'])) {
378 // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here
379 // numeric fields get 0 as default, other fields an empty string
380 if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {
381 switch ($type) {
382 case 'I8':
383
384 case 'F':
385
386 case 'N':
387 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));
388 break;
389 default:
390 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));
391 }
392 }
393 foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {
394 switch (TRUE) {
395 case $feature === 'UNSIGNED' && !$this->databaseConnection->runningADOdbDriver('mysql'):
396
397 case $feature === 'AUTO_INCREMENT':
398
399 case $feature === 'NOTNULL' && $this->databaseConnection->runningADOdbDriver('oci8'):
400 continue;
401 case $feature === 'NOTNULL':
402 $cfg .= ' NOTNULL';
403 break;
404 default:
405 $cfg .= ' ' . $featureDef['keyword'];
406 }
407 // Add value if found:
408 if (is_array($featureDef['value'])) {
409 if ($featureDef['value'][0] === '') {
410 $cfg .= ' "\'\'"';
411 } else {
412 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
413 if (!is_numeric($featureDef['value'][0])) {
414 $noQuote = FALSE;
415 }
416 }
417 }
418 }
419 }
420 if ($noQuote) {
421 $cfg .= ' NOQUOTE';
422 }
423 break;
424 }
425 // Return field definition string:
426 return $cfg;
427 }
428
429 /**
430 * Checks if the submitted feature index contains a default value definition and the default value
431 *
432 * @param array $featureIndex A feature index as produced by parseFieldDef()
433 * @return bool
434 * @see \TYPO3\CMS\Core\Database\SqlParser::parseFieldDef()
435 */
436 public function checkEmptyDefaultValue($featureIndex) {
437 if (!is_array($featureIndex['DEFAULT']['value'])) {
438 return TRUE;
439 }
440 return !is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0]);
441 }
442
443 /**
444 * Implodes an array of WHERE clause configuration into a WHERE clause.
445 *
446 * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are:
447 * - the bitwise logical and (&)
448 * - the addition (+)
449 * - the substraction (-)
450 * - the multiplication (*)
451 * - the division (/)
452 * - the modulo (%)
453 *
454 * @param array $clauseArray
455 * @param bool $functionMapping
456 * @return string WHERE clause as string.
457 * @see \TYPO3\CMS\Core\Database\SqlParser::parseWhereClause()
458 */
459 public function compileWhereClause($clauseArray, $functionMapping = TRUE) {
460 $output = '';
461 switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
462 case 'native':
463 $output = parent::compileWhereClause($clauseArray);
464 break;
465 case 'adodb':
466 // Prepare buffer variable:
467 $output = '';
468 // Traverse clause array:
469 if (is_array($clauseArray)) {
470 foreach ($clauseArray as $v) {
471 // Set operator:
472 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
473 // Look for sublevel:
474 if (is_array($v['sub'])) {
475 $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
476 } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
477 $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
478 } else {
479 if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
480 $output .= ' ' . trim($v['modifier']);
481 switch (TRUE) {
482 case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
483 $output .= ' CHARINDEX(';
484 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
485 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
486 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
487 $output .= ')';
488 break;
489 case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
490 $output .= ' INSTR(';
491 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
492 $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
493 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
494 $output .= ')';
495 break;
496 default:
497 $output .= ' LOCATE(';
498 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
499 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
500 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
501 $output .= ')';
502 }
503 } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
504 $output .= ' ' . trim($v['modifier']) . ' ';
505 switch (TRUE) {
506 case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
507 $output .= 'ISNULL';
508 break;
509 case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
510 $output .= 'NVL';
511 break;
512 default:
513 $output .= 'IFNULL';
514 }
515 $output .= '(';
516 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
517 $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
518 $output .= ')';
519 } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {
520 $output .= ' ' . trim($v['modifier']) . ' ';
521 if ($functionMapping) {
522 switch (TRUE) {
523 case $this->databaseConnection->runningADOdbDriver('mssql'):
524 $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
525 if (!isset($v['func']['str_like'])) {
526 $v['func']['str_like'] = $v['func']['str'][0];
527 }
528 $output .= '\',\'+' . $field . '+\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
529 break;
530 case $this->databaseConnection->runningADOdbDriver('oci8'):
531 $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
532 if (!isset($v['func']['str_like'])) {
533 $v['func']['str_like'] = $v['func']['str'][0];
534 }
535 $output .= '\',\'||' . $field . '||\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
536 break;
537 case $this->databaseConnection->runningADOdbDriver('postgres'):
538 $output .= ' FIND_IN_SET(';
539 $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
540 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
541 $output .= ') != 0';
542 break;
543 default:
544 $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
545 if (!isset($v['func']['str_like'])) {
546 $v['func']['str_like'] = $v['func']['str'][0];
547 }
548 $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
549 }
550 } else {
551 switch (TRUE) {
552 case $this->databaseConnection->runningADOdbDriver('mssql'):
553
554 case $this->databaseConnection->runningADOdbDriver('oci8'):
555
556 case $this->databaseConnection->runningADOdbDriver('postgres'):
557 $output .= ' FIND_IN_SET(';
558 $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
559 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
560 $output .= ')';
561 break;
562 default:
563 $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
564 if (!isset($v['func']['str_like'])) {
565 $v['func']['str_like'] = $v['func']['str'][0];
566 }
567 $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
568 }
569 }
570 } else {
571 // Set field/table with modifying prefix if any:
572 $output .= ' ' . trim($v['modifier']) . ' ';
573 // DBAL-specific: Set calculation, if any:
574 if ($v['calc'] === '&' && $functionMapping) {
575 switch (TRUE) {
576 case $this->databaseConnection->runningADOdbDriver('oci8'):
577 // Oracle only knows BITAND(x,y) - sigh
578 $output .= 'BITAND(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
579 break;
580 default:
581 // MySQL, MS SQL Server, PostgreSQL support the &-syntax
582 $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
583 }
584 } elseif ($v['calc']) {
585 $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'];
586 if (isset($v['calc_table'])) {
587 $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
588 } else {
589 $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
590 }
591 } elseif (!($this->databaseConnection->runningADOdbDriver('oci8') && preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']) && $functionMapping)) {
592 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
593 }
594 }
595 // Set comparator:
596 if ($v['comparator']) {
597 $isLikeOperator = preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']);
598 switch (TRUE) {
599 case $this->databaseConnection->runningADOdbDriver('oci8') && $isLikeOperator && $functionMapping:
600 // Oracle cannot handle LIKE on CLOB fields - sigh
601 if (isset($v['value']['operator'])) {
602 $values = array();
603 foreach ($v['value']['args'] as $fieldDef) {
604 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
605 }
606 $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
607 } else {
608 $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
609 }
610 if (GeneralUtility::isFirstPartOfStr($v['comparator'], 'NOT')) {
611 $output .= 'NOT ';
612 }
613 // To be on the safe side
614 $isLob = TRUE;
615 if ($v['table']) {
616 // Table and field names are quoted:
617 $tableName = substr($v['table'], 1, strlen($v['table']) - 2);
618 $fieldName = substr($v['field'], 1, strlen($v['field']) - 2);
619 $fieldType = $this->databaseConnection->sql_field_metatype($tableName, $fieldName);
620 $isLob = $fieldType === 'B' || $fieldType === 'XL';
621 }
622 if (strtoupper(substr($v['comparator'], -6)) === 'BINARY') {
623 if ($isLob) {
624 $output .= '(dbms_lob.instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';
625 } else {
626 $output .= '(instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';
627 }
628 } else {
629 if ($isLob) {
630 $output .= '(dbms_lob.instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
631 } else {
632 $output .= '(instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
633 }
634 }
635 break;
636 default:
637 if ($isLikeOperator && $functionMapping) {
638 if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) {
639 // Remap (NOT)? LIKE to (NOT)? ILIKE
640 // and (NOT)? LIKE BINARY to (NOT)? LIKE
641 switch ($v['comparator']) {
642 case 'LIKE':
643 $v['comparator'] = 'ILIKE';
644 break;
645 case 'NOT LIKE':
646 $v['comparator'] = 'NOT ILIKE';
647 break;
648 default:
649 $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
650 }
651 } else {
652 // No more BINARY operator
653 $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
654 }
655 }
656 $output .= ' ' . $v['comparator'];
657 // Detecting value type; list or plain:
658 $comparator = $this->normalizeKeyword($v['comparator']);
659 if (GeneralUtility::inList('NOTIN,IN', $comparator)) {
660 if (isset($v['subquery'])) {
661 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
662 } else {
663 $valueBuffer = array();
664 foreach ($v['value'] as $realValue) {
665 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
666 }
667
668 $dbmsSpecifics = $this->databaseConnection->getSpecifics();
669 if ($dbmsSpecifics === NULL) {
670 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
671 } else {
672 $chunkedList = $dbmsSpecifics->splitMaxExpressions($valueBuffer);
673 $chunkCount = count($chunkedList);
674
675 if ($chunkCount === 1) {
676 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
677 } else {
678 $listExpressions = array();
679 $field = trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
680
681 switch ($comparator) {
682 case 'IN':
683 $operator = 'OR';
684 break;
685 case 'NOTIN':
686 $operator = 'AND';
687 break;
688 default:
689 $operator = '';
690 }
691
692 for ($i = 0; $i < $chunkCount; ++$i) {
693 $listPart = trim(implode(',', $chunkedList[$i]));
694 $listExpressions[] = ' (' . $listPart . ')';
695 }
696
697 $implodeString = ' ' . $operator . ' ' . $field . ' ' . $v['comparator'];
698
699 // add opening brace before field
700 $lastFieldPos = strrpos($output, $field);
701 $output = substr_replace($output, '(', $lastFieldPos, 0);
702 $output .= implode($implodeString, $listExpressions) . ')';
703 }
704 }
705 }
706 } elseif (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {
707 $lbound = $v['values'][0];
708 $ubound = $v['values'][1];
709 $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];
710 $output .= ' AND ';
711 $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];
712 } elseif (isset($v['value']['operator'])) {
713 $values = array();
714 foreach ($v['value']['args'] as $fieldDef) {
715 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
716 }
717 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
718 } else {
719 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
720 }
721 }
722 }
723 }
724 }
725 }
726 break;
727 }
728 return $output;
729 }
730
731 /**
732 * Performs the ultimate test of the parser: Direct a SQL query in; You will get it back (through the parsed and re-compiled) if no problems, otherwise the script will print the error and exit
733 *
734 * @param string $SQLquery SQL query
735 * @return string Query if all is well, otherwise exit.
736 */
737 public function debug_testSQL($SQLquery) {
738 // Getting result array:
739 $parseResult = $this->parseSQL($SQLquery);
740 // If result array was returned, proceed. Otherwise show error and exit.
741 if (is_array($parseResult)) {
742 // Re-compile query:
743 $newQuery = $this->compileSQL($parseResult);
744 // TEST the new query:
745 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
746 // Return new query if OK, otherwise show error and exit:
747 if (!is_array($testResult)) {
748 return $newQuery;
749 } else {
750 debug(array('ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult), 'SQL parsing failed:');
751 die;
752 }
753 } else {
754 debug(array('query' => $SQLquery, 'ERROR MESSAGE' => $parseResult), 'SQL parsing failed:');
755 die;
756 }
757 }
758
759 }