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