4f1739a130f25630df360b7452581ab2dc5aa9b7
[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 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 * @return string Select field string
56 * @see parseFieldList()
57 */
58 public function compileFieldList($selectFields, $compileComments = TRUE) {
59 // TODO: Handle SQL hints in comments according to current DBMS
60 return parent::compileFieldList($selectFields, FALSE);
61 }
62
63 /*************************
64 *
65 * Compiling queries
66 *
67 *************************/
68
69 /**
70 * Compiles an INSERT statement from components array
71 *
72 * @param array Array of SQL query components
73 * @return string SQL INSERT query
74 * @see parseINSERT()
75 */
76 function compileINSERT($components) {
77 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
78 case 'native':
79 $query = parent::compileINSERT($components);
80 break;
81 case 'adodb':
82 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
83 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$components['TABLE']];
84 $fc = 0;
85 foreach ($fields as $fn => $fd) {
86 $query[$fn] = $components['VALUES_ONLY'][$fc++][0];
87 }
88 } else {
89 // Initialize:
90 foreach ($components['FIELDS'] as $fN => $fV) {
91 $query[$fN]=$fV[0];
92 }
93 }
94 break;
95 }
96
97 return $query;
98 }
99
100 /**
101 * Compiles a DROP TABLE statement from components array
102 *
103 * @param array Array of SQL query components
104 * @return string SQL DROP TABLE query
105 * @see compileSQL()
106 */
107 private function compileDROPTABLE($components) {
108 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
109 case 'native':
110 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE'];
111 break;
112 case 'adodb':
113 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
114 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], $handlerKey, TRUE);
115 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$handlerKey]->DataDictionary->DropTableSQL($tableName);
116 break;
117 }
118
119 return $query;
120 }
121
122 /**
123 * Compiles a CREATE TABLE statement from components array
124 *
125 * @param array Array of SQL query components
126 * @return array array with SQL CREATE TABLE/INDEX command(s)
127 * @see parseCREATETABLE()
128 */
129 public function compileCREATETABLE($components) {
130 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
131 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]['type']) {
132 case 'native':
133 $query[] = parent::compileCREATETABLE($components);
134 break;
135 case 'adodb':
136 // Create fields and keys:
137 $fieldsKeys = array();
138 $indexKeys = array();
139
140 foreach ($components['FIELDS'] as $fN => $fCfg) {
141 $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
142 $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
143 }
144
145 if (isset($components['KEYS']) && is_array($components['KEYS'])) {
146 foreach($components['KEYS'] as $kN => $kCfg) {
147 if ($kN === 'PRIMARYKEY') {
148 foreach ($kCfg as $n => $field) {
149 $fieldsKeys[$field] .= ' PRIMARY';
150 }
151 } elseif ($kN === 'UNIQUE') {
152 foreach ($kCfg as $n => $field) {
153 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));
154 }
155 } else {
156 $indexKeys = array_merge($indexKeys, $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));
157 }
158 }
159 }
160
161 // Generally create without OID on PostgreSQL
162 $tableOptions = array('postgres' => 'WITHOUT OIDS');
163
164 // Fetch table/index generation query:
165 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
166 $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
167 break;
168 }
169
170 return $query;
171 }
172
173 /**
174 * Compiles an ALTER TABLE statement from components array
175 *
176 * @param array Array of SQL query components
177 * @return string SQL ALTER TABLE query
178 * @see parseALTERTABLE()
179 */
180 public function compileALTERTABLE($components) {
181 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
182 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
183 case 'native':
184 $query[] = parent::compileALTERTABLE($components);
185 break;
186 case 'adodb':
187 $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
188 $fieldName = $GLOBALS['TYPO3_DB']->quoteName($components['FIELD'], NULL, TRUE);
189 switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {
190 case 'ADD':
191 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
192 break;
193 case 'CHANGE':
194 $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
195 break;
196 case 'DROP':
197 case 'DROPKEY':
198 break;
199 case 'ADDKEY':
200 case 'ADDPRIMARYKEY':
201 $query .= ' (' . implode(',', $components['fields']) . ')';
202 break;
203 }
204 break;
205 }
206
207 return $query;
208 }
209
210 /**
211 * Compile field definition
212 *
213 * @param array Field definition parts
214 * @return string Field definition string
215 */
216 public function compileFieldCfg($fieldCfg) {
217 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
218 case 'native':
219 $cfg = parent::compileFieldCfg($fieldCfg);
220 break;
221 case 'adodb':
222 // Set type:
223 $type = $GLOBALS['TYPO3_DB']->MySQLMetaType($fieldCfg['fieldType']);
224 $cfg = $type;
225
226 // Add value, if any:
227 if (strlen($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
228 $cfg .= ' '.$fieldCfg['value'];
229 } elseif (!isset($fieldCfg['value']) && (in_array($type, array('C', 'C2')))) {
230 $cfg .= ' 255'; // add 255 as length for varchar without specified length (e.g. coming from tinytext, tinyblob)
231 }
232
233 // Add additional features:
234 $noQuote = TRUE;
235 if (is_array($fieldCfg['featureIndex'])) {
236
237 // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here
238 // numeric fields get 0 as default, other fields an empty string
239 if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {
240 switch ($type) {
241 case 'I8':
242 case 'F':
243 case 'N':
244 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));
245 break;
246 default:
247 $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));
248 }
249 }
250
251 foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {
252 switch (TRUE) {
253 // unsigned only for mysql, as it is mysql specific
254 case ($feature === 'UNSIGNED' && !$GLOBALS['TYPO3_DB']->runningADOdbDriver('mysql')):
255 // auto_increment is removed, it is handled by (emulated) sequences
256 case ($feature === 'AUTO_INCREMENT'):
257 // never add NOT NULL if running on Oracle and we have an empty string as default
258 case ($feature === 'NOTNULL' && $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8')):
259 continue;
260 case ($feature === 'NOTNULL'):
261 $cfg .= ' NOTNULL';
262 break;
263 default:
264 $cfg .= ' ' . $featureDef['keyword'];
265 }
266
267 // Add value if found:
268 if (is_array($featureDef['value'])) {
269 if ($featureDef['value'][0] === '') {
270 $cfg .= ' "\'\'"';
271 } else {
272 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
273 if (!is_numeric($featureDef['value'][0])) {
274 $noQuote = FALSE;
275 }
276 }
277 }
278 }
279 }
280 if ($noQuote) {
281 $cfg .= ' NOQUOTE';
282 }
283 break;
284 }
285
286 // Return field definition string:
287 return $cfg;
288 }
289
290 /**
291 * Checks if the submitted feature index contains a default value definition and the default value
292 *
293 * @param array $featureIndex A feature index as produced by parseFieldDef()
294 * @return boolean
295 * @see t3lib_sqlparser::parseFieldDef()
296 */
297 public function checkEmptyDefaultValue($featureIndex) {
298 if (is_array($featureIndex['DEFAULT']['value'])) {
299 if (!is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0])) {
300 return TRUE;
301 } else {
302 return FALSE;
303 }
304 }
305 return TRUE;
306 }
307
308 /**
309 * Implodes an array of WHERE clause configuration into a WHERE clause.
310 *
311 * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are:
312 * - the bitwise logical and (&)
313 * - the addition (+)
314 * - the substraction (-)
315 * - the multiplication (*)
316 * - the division (/)
317 * - the modulo (%)
318 *
319 * @param array WHERE clause configuration
320 * @return string WHERE clause as string.
321 * @see t3lib_sqlparser::parseWhereClause()
322 */
323 public function compileWhereClause($clauseArray, $functionMapping = TRUE) {
324 switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
325 case 'native':
326 $output = parent::compileWhereClause($clauseArray);
327 break;
328 case 'adodb':
329 // Prepare buffer variable:
330 $output = '';
331
332 // Traverse clause array:
333 if (is_array($clauseArray)) {
334 foreach($clauseArray as $k => $v) {
335
336 // Set operator:
337 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
338
339 // Look for sublevel:
340 if (is_array($v['sub'])) {
341 $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
342 } elseif (isset($v['func'])) {
343 $output .= ' ' . trim($v['modifier']) . ' ' . $v['func']['type'] . ' (' . $this->compileSELECT($v['func']['subquery']) . ')';
344 } else {
345
346 // Set field/table with modifying prefix if any:
347 $output .= ' ' . trim($v['modifier']) . ' ';
348
349 // DBAL-specific: Set calculation, if any:
350 if ($v['calc'] === '&' && $functionMapping) {
351 switch(TRUE) {
352 case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):
353 // Oracle only knows BITAND(x,y) - sigh
354 $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
355 break;
356 default:
357 // MySQL, MS SQL Server, PostgreSQL support the &-syntax
358 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
359 break;
360 }
361 } elseif ($v['calc']) {
362 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
363 if (isset($v['calc_table'])) {
364 $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
365 } else {
366 $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
367 }
368 } elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {
369 $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
370 }
371
372 // Set comparator:
373 if ($v['comparator']) {
374 switch (TRUE) {
375 case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping):
376 // Oracle cannot handle LIKE on CLOB fields - sigh
377 if (isset($v['value']['operator'])) {
378 $values = array();
379 foreach ($v['value']['args'] as $fieldDef) {
380 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
381 }
382 $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
383 } else {
384 $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
385 }
386 $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
387 break;
388 default:
389 $output .= ' ' . $v['comparator'];
390
391 // Detecting value type; list or plain:
392 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $v['comparator'])))) {
393 if (isset($v['subquery'])) {
394 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
395 } else {
396 $valueBuffer = array();
397 foreach ($v['value'] as $realValue) {
398 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
399 }
400 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
401 }
402 } else if (isset($v['value']['operator'])) {
403 $values = array();
404 foreach ($v['value']['args'] as $fieldDef) {
405 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
406 }
407 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
408 } else {
409 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
410 }
411 break;
412 }
413 }
414 }
415 }
416 }
417 break;
418 }
419
420 return $output;
421 }
422 }
423
424
425 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']) {
426 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']);
427 }
428
429 ?>