Updated copyright year
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / class.ux_t3lib_sqlparser.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
7 * (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
8 * All rights reserved
9 *
10 * This script is part of the TYPO3 project. The TYPO3 project is
11 * free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * The GNU General Public License can be found at
17 * http://www.gnu.org/copyleft/gpl.html.
18 * A copy is found in the textfile GPL.txt and important notices to the license
19 * from the author is found in LICENSE.txt distributed with these scripts.
20 *
21 *
22 * This script is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 * GNU General Public License for more details.
26 *
27 * This copyright notice MUST APPEAR in all copies of the script!
28 ***************************************************************/
29 /**
30 * PHP SQL engine
31 *
32 * $Id$
33 *
34 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
35 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
36 * @author Xavier Perseguers <typo3@perseguers.ch>
37 */
38
39
40 /**
41 * PHP SQL engine / server
42 *
43 * @author Kasper Skaarhoj <kasper@typo3.com>
44 * @package TYPO3
45 * @subpackage t3lib
46 */
47 class ux_t3lib_sqlparser extends t3lib_sqlparser {
48
49 /**
50 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
51 * Can also compile field lists for ORDER BY and GROUP BY.
52 *
53 * @param array Array of select fields, (made with ->parseFieldList())
54 * @param boolean Whether comments should be compiled
55 * @param boolean Whether function mapping should take place
56 * @return string Select field string
57 * @see parseFieldList()
58 */
59 public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {
60 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
61 case 'native':
62 $output = parent::compileFieldList($selectFields, $compileComments);
63 break;
64 case 'adodb':
65 $output = '';
66 // Traverse the selectFields if any:
67 if (is_array($selectFields)) {
68 $outputParts = array();
69 foreach ($selectFields as $k => $v) {
70
71 // Detecting type:
72 switch($v['type']) {
73 case 'function':
74 $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
75 break;
76 case 'flow-control':
77 if ($v['flow-control']['type'] === 'CASE') {
78 $outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);
79 }
80 break;
81 case 'field':
82 $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
83 break;
84 }
85
86 // Alias:
87 if ($v['as']) {
88 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
89 }
90
91 // Specifically for ORDER BY and GROUP BY field lists:
92 if ($v['sortDir']) {
93 $outputParts[$k] .= ' ' . $v['sortDir'];
94 }
95 }
96 // TODO: Handle SQL hints in comments according to current DBMS
97 if (/* $compileComments */ FALSE && $selectFields[0]['comments']) {
98 $output = $selectFields[0]['comments'] . ' ';
99 }
100 $output .= implode(', ', $outputParts);
101 }
102 break;
103 }
104 return $output;
105 }
106
107 /**
108 * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
109 *
110 * @param array Array of case components, (made with ->parseCaseStatement())
111 * @param boolean Whether function mapping should take place
112 * @return string case when string
113 * @see parseCaseStatement()
114 */
115 protected function compileCaseStatement(array $components, $functionMapping = TRUE) {
116 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
117 case 'native':
118 $output = parent::compileCaseStatement($components);
119 break;
120 case 'adodb':
121 $statement = 'CASE';
122 if (isset($components['case_field'])) {
123 $statement .= ' ' . $components['case_field'];
124 } elseif (isset($components['case_value'])) {
125 $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
126 }
127 foreach ($components['when'] as $when) {
128 $statement .= ' WHEN ';
129 $statement .= $this->compileWhereClause($when['when_value'], $functionMapping);
130 $statement .= ' THEN ';
131 $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
132 }
133 if (isset($components['else'])) {
134 $statement .= ' ELSE ';
135 $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
136 }
137 $statement .= ' END';
138 $output = $statement;
139 break;
140 }
141 return $output;
142 }
143
144 /**
145 * Add slashes function used for compiling queries
146 * This method overrides the method from t3lib_sqlparser because
147 * the input string is already properly escaped.
148 *
149 * @param string Input string
150 * @return string Output string
151 */
152 protected function compileAddslashes($str) {
153 return $str;
154 }
155
156 /*************************
157 *
158 * Compiling queries
159 *
160 *************************/
161
162 /**
163 * Compiles an INSERT statement from components array
164 *
165 * @param array Array of SQL query components
166 * @return string SQL INSERT query
167 * @see parseINSERT()
168 */
169 function compileINSERT($components) {
170 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
171 case 'native':
172 $query = parent::compileINSERT($components);
173 break;
174 case 'adodb':
175 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
176 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$components['TABLE']];
177 $fc = 0;
178 foreach ($fields as $fn => $fd) {
179 $query[$fn] = $components['VALUES_ONLY'][$fc++][0];
180 }
181 } else {
182 // Initialize:
183 foreach ($components['FIELDS'] as $fN => $fV) {
184 $query[$fN]=$fV[0];
185 }
186 }
187 break;
188 }
189
190 return $query;
191 }
192
193 /**
194 * Compiles a DROP TABLE statement from components array
195 *
196 * @param array Array of SQL query components
197 * @return string SQL DROP TABLE query
198 * @see compileSQL()
199 */
200 private function compileDROPTABLE($components) {
201 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
202 case 'native':
203 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE'];
204 break;
205 case 'adodb':
206 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
207 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], $handlerKey, TRUE);
208 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$handlerKey]->DataDictionary->DropTableSQL($tableName);
209 break;
210 }
211
212 return $query;
213 }
214
215 /**
216 * Compiles a CREATE TABLE statement from components array
217 *
218 * @param array Array of SQL query components
219 * @return array array with SQL CREATE TABLE/INDEX command(s)
220 * @see parseCREATETABLE()
221 */
222 public function compileCREATETABLE($components) {
223 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
224 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]['type']) {
225 case 'native':
226 $query[] = parent::compileCREATETABLE($components);
227 break;
228 case 'adodb':
229 // Create fields and keys:
230 $fieldsKeys = array();
231 $indexKeys = array();
232
233 foreach ($components['FIELDS'] as $fN => $fCfg) {
234 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
235 $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
236 }
237
238 if (isset($components['KEYS']) && is_array($components['KEYS'])) {
239 foreach($components['KEYS'] as $kN => $kCfg) {
240 if ($kN === 'PRIMARYKEY') {
241 foreach ($kCfg as $n => $field) {
242 $fieldsKeys[$field] .= ' PRIMARY';
243 }
244 } elseif ($kN === 'UNIQUE') {
245 foreach ($kCfg as $n => $field) {
246 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));
247 }
248 } else {
249 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));
250 }
251 }
252 }
253
254 // Generally create without OID on PostgreSQL
255 $tableOptions = array('postgres' => 'WITHOUT OIDS');
256
257 // Fetch table/index generation query:
258 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
259 $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
260 break;
261 }
262
263 return $query;
264 }
265
266 /**
267 * Compiles an ALTER TABLE statement from components array
268 *
269 * @param array Array of SQL query components
270 * @return string SQL ALTER TABLE query
271 * @see parseALTERTABLE()
272 */
273 public function compileALTERTABLE($components) {
274 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
275 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
276 case 'native':
277 $query[] = parent::compileALTERTABLE($components);
278 break;
279 case 'adodb':
280 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
281 $fieldName = $GLOBALS['TYPO3_DB']->quoteName($components['FIELD'], NULL, TRUE);
282 switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {
283 case 'ADD':
284 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
285 break;
286 case 'CHANGE':
287 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
288 break;
289 case 'DROP':
290 case 'DROPKEY':
291 break;
292 case 'ADDKEY':
293 case 'ADDPRIMARYKEY':
294 $query .= ' (' . implode(',', $components['fields']) . ')';
295 break;
296 }
297 break;
298 }
299
300 return $query;
301 }
302
303 /**
304 * Compile field definition
305 *
306 * @param array Field definition parts
307 * @return string Field definition string
308 */
309 public function compileFieldCfg($fieldCfg) {
310 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
311 case 'native':
312 $cfg = parent::compileFieldCfg($fieldCfg);
313 break;
314 case 'adodb':
315 // Set type:
316 $type = $GLOBALS['TYPO3_DB']->MySQLMetaType($fieldCfg['fieldType']);
317 $cfg = $type;
318
319 // Add value, if any:
320 if (strlen($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
321 $cfg .= ' '.$fieldCfg['value'];
322 } elseif (!isset($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
323 $cfg .= ' 255'; // add 255 as length for varchar without specified length (e.g. coming from tinytext, tinyblob)
324 }
325
326 // Add additional features:
327 $noQuote = TRUE;
328 if (is_array($fieldCfg['featureIndex'])) {
329
330 // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here
331 // numeric fields get 0 as default, other fields an empty string
332 if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {
333 switch ($type) {
334 case 'I8':
335 case 'F':
336 case 'N':
337 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));
338 break;
339 default:
340 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));
341 }
342 }
343
344 foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {
345 switch (TRUE) {
346 // unsigned only for mysql, as it is mysql specific
347 case ($feature === 'UNSIGNED' && !$GLOBALS['TYPO3_DB']->runningADOdbDriver('mysql')):
348 // auto_increment is removed, it is handled by (emulated) sequences
349 case ($feature === 'AUTO_INCREMENT'):
350 // never add NOT NULL if running on Oracle and we have an empty string as default
351 case ($feature === 'NOTNULL' && $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8')):
352 continue;
353 case ($feature === 'NOTNULL'):
354 $cfg .= ' NOTNULL';
355 break;
356 default:
357 $cfg .= ' ' . $featureDef['keyword'];
358 }
359
360 // Add value if found:
361 if (is_array($featureDef['value'])) {
362 if ($featureDef['value'][0] === '') {
363 $cfg .= ' "\'\'"';
364 } else {
365 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
366 if (!is_numeric($featureDef['value'][0])) {
367 $noQuote = FALSE;
368 }
369 }
370 }
371 }
372 }
373 if ($noQuote) {
374 $cfg .= ' NOQUOTE';
375 }
376 break;
377 }
378
379 // Return field definition string:
380 return $cfg;
381 }
382
383 /**
384 * Checks if the submitted feature index contains a default value definition and the default value
385 *
386 * @param array $featureIndex A feature index as produced by parseFieldDef()
387 * @return boolean
388 * @see t3lib_sqlparser::parseFieldDef()
389 */
390 public function checkEmptyDefaultValue($featureIndex) {
391 if (is_array($featureIndex['DEFAULT']['value'])) {
392 if (!is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0])) {
393 return TRUE;
394 } else {
395 return FALSE;
396 }
397 }
398 return TRUE;
399 }
400
401 /**
402 * Implodes an array of WHERE clause configuration into a WHERE clause.
403 *
404 * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are:
405 * - the bitwise logical and (&)
406 * - the addition (+)
407 * - the substraction (-)
408 * - the multiplication (*)
409 * - the division (/)
410 * - the modulo (%)
411 *
412 * @param array WHERE clause configuration
413 * @return string WHERE clause as string.
414 * @see t3lib_sqlparser::parseWhereClause()
415 */
416 public function compileWhereClause($clauseArray, $functionMapping = TRUE) {
417 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
418 case 'native':
419 $output = parent::compileWhereClause($clauseArray);
420 break;
421 case 'adodb':
422 // Prepare buffer variable:
423 $output = '';
424
425 // Traverse clause array:
426 if (is_array($clauseArray)) {
427 foreach($clauseArray as $k => $v) {
428
429 // Set operator:
430 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
431
432 // Look for sublevel:
433 if (is_array($v['sub'])) {
434 $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
435 } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
436 $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
437 } else {
438
439 if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
440 $output .= ' ' . trim($v['modifier']);
441 switch (TRUE) {
442 case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):
443 $output .= ' CHARINDEX(';
444 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
445 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
446 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
447 $output .= ')';
448 break;
449 case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):
450 $output .= ' INSTR(';
451 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
452 $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
453 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
454 $output .= ')';
455 break;
456 default:
457 $output .= ' LOCATE(';
458 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
459 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
460 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
461 $output .= ')';
462 break;
463 }
464 } else {
465
466 // Set field/table with modifying prefix if any:
467 $output .= ' ' . trim($v['modifier']) . ' ';
468
469 // DBAL-specific: Set calculation, if any:
470 if ($v['calc'] === '&' && $functionMapping) {
471 switch(TRUE) {
472 case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):
473 // Oracle only knows BITAND(x,y) - sigh
474 $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
475 break;
476 default:
477 // MySQL, MS SQL Server, PostgreSQL support the &-syntax
478 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
479 break;
480 }
481 } elseif ($v['calc']) {
482 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
483 if (isset($v['calc_table'])) {
484 $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
485 } else {
486 $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
487 }
488 } elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {
489 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
490 }
491 }
492
493 // Set comparator:
494 if ($v['comparator']) {
495 switch (TRUE) {
496 case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping):
497 // Oracle cannot handle LIKE on CLOB fields - sigh
498 if (isset($v['value']['operator'])) {
499 $values = array();
500 foreach ($v['value']['args'] as $fieldDef) {
501 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
502 }
503 $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
504 } else {
505 $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
506 }
507 $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
508 break;
509 default:
510 $output .= ' ' . $v['comparator'];
511
512 // Detecting value type; list or plain:
513 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $v['comparator'])))) {
514 if (isset($v['subquery'])) {
515 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
516 } else {
517 $valueBuffer = array();
518 foreach ($v['value'] as $realValue) {
519 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
520 }
521 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
522 }
523 } else if (isset($v['value']['operator'])) {
524 $values = array();
525 foreach ($v['value']['args'] as $fieldDef) {
526 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
527 }
528 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
529 } else {
530 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
531 }
532 break;
533 }
534 }
535 }
536 }
537 }
538 break;
539 }
540
541 return $output;
542 }
543 }
544
545
546 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']) {
547 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']);
548 }
549
550 ?>