61e623ed838966fb158eacf7ab8c8e9188d720fe
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / class.ux_t3lib_db.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2010 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * (c) 2004-2010 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 * Contains a database abstraction layer class for TYPO3
31 *
32 * $Id: class.ux_t3lib_db.php 29977 2010-02-13 13:18:32Z xperseguers $
33 *
34 * @author Kasper Skaarhoj <kasper@typo3.com>
35 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
36 * @author Xavier Perseguers <typo3@perseguers.ch>
37 */
38 /**
39 * [CLASS/FUNCTION INDEX of SCRIPT]
40 *
41 *
42 *
43 * 123: class ux_t3lib_DB extends t3lib_DB
44 * 169: function ux_t3lib_DB()
45 * 184: function initInternalVariables()
46 *
47 * SECTION: Query Building (Overriding parent methods)
48 * 217: function exec_INSERTquery($table,$fields_values)
49 * 275: function exec_UPDATEquery($table,$where,$fields_values)
50 * 334: function exec_DELETEquery($table,$where)
51 * 387: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy = '',$orderBy = '',$limit = '')
52 *
53 * SECTION: Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
54 * 533: function SELECTquery($select_fields,$from_table,$where_clause,$groupBy = '',$orderBy = '',$limit = '')
55 * 556: function quoteSelectFields(&$select_fields)
56 * 573: function quoteFromTables(&$from_table)
57 * 595: function quoteWhereClause(&$where_clause)
58 * 620: function quoteGroupBy(&$groupBy)
59 * 637: function quoteOrderBy(&$orderBy)
60 *
61 * SECTION: Various helper functions
62 * 663: function quoteStr($str, $table)
63 *
64 * SECTION: SQL wrapper functions (Overriding parent methods)
65 * 707: function sql_error()
66 * 734: function sql_num_rows(&$res)
67 * 760: function sql_fetch_assoc(&$res)
68 * 808: function sql_fetch_row(&$res)
69 * 842: function sql_free_result(&$res)
70 * 868: function sql_insert_id()
71 * 893: function sql_affected_rows()
72 * 919: function sql_data_seek(&$res,$seek)
73 * 946: function sql_field_type(&$res,$pointer)
74 *
75 * SECTION: Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
76 * 987: function sql($db,$query)
77 * 999: function sql_query($query)
78 * 1035: function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
79 * 1055: function sql_select_db($TYPO3_db)
80 *
81 * SECTION: SQL admin functions
82 * 1086: function admin_get_tables()
83 * 1149: function admin_get_fields($tableName)
84 * 1210: function admin_get_keys($tableName)
85 * 1270: function admin_query($query)
86 *
87 * SECTION: Handler management
88 * 1333: function handler_getFromTableList($tableList)
89 * 1379: function handler_init($handlerKey)
90 *
91 * SECTION: Table/Field mapping
92 * 1488: function map_needMapping($tableList,$fieldMappingOnly = FALSE)
93 * 1524: function map_assocArray($input,$tables,$rev = FALSE)
94 * 1573: function map_remapSELECTQueryParts(&$select_fields,&$from_table,&$where_clause,&$groupBy,&$orderBy)
95 * 1615: function map_sqlParts(&$sqlPartArray, $defaultTable)
96 * 1650: function map_genericQueryParsed(&$parsedQuery)
97 * 1717: function map_fieldNamesInArray($table,&$fieldArray)
98 *
99 * SECTION: Debugging
100 * 1758: function debugHandler($function,$execTime,$inData)
101 * 1823: function debug_log($query,$ms,$data,$join,$errorFlag)
102 * 1849: function debug_explain($query)
103 *
104 * TOTAL FUNCTIONS: 41
105 * (This index is automatically created/updated by the extension "extdeveval")
106 *
107 */
108 /**
109 * TYPO3 database abstraction layer
110 *
111 * @author Kasper Skaarhoj <kasper@typo3.com>
112 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
113 * @package TYPO3
114 * @subpackage tx_dbal
115 */
116 class ux_t3lib_DB extends t3lib_DB {
117
118 // Internal, static:
119 var $printErrors = FALSE; // Enable output of SQL errors after query executions. Set through TYPO3_CONF_VARS, see init()
120 var $debug = FALSE; // Enable debug mode. Set through TYPO3_CONF_VARS, see init()
121 var $conf = array(); // Configuration array, copied from TYPO3_CONF_VARS in constructor.
122
123 var $mapping = array(); // See manual.
124 var $table2handlerKeys = array(); // See manual.
125 var $handlerCfg = array( // See manual.
126 '_DEFAULT' => array(
127 'type' => 'native',
128 'config' => array(
129 'username' => '', // Set by default (overridden)
130 'password' => '', // Set by default (overridden)
131 'host' => '', // Set by default (overridden)
132 'database' => '', // Set by default (overridden)
133 'driver' => '', // ONLY "adodb" type; eg. "mysql"
134 'sequenceStart' => 1, // ONLY "adodb", first number in sequences/serials/...
135 'useNameQuote' => 0 // ONLY "adodb", whether to use NameQuote() method from ADOdb to quote names
136 )
137 ),
138 );
139
140
141 // Internal, dynamic:
142 var $handlerInstance = array(); // Contains instance of the handler objects as they are created. Exception is the native mySQL calls which are registered as an array with keys "handlerType" = "native" and "link" pointing to the link resource for the connection.
143 var $lastHandlerKey = ''; // Storage of the handler key of last ( SELECT) query - used for subsequent fetch-row calls etc.
144 var $lastQuery = ''; // Storage of last SELECT query
145 var $lastParsedAndMappedQueryArray = array(); // Query array, the last one parsed
146
147 var $resourceIdToTableNameMap = array(); // Mapping of resource ids to table names.
148
149 // Internal, caching:
150 var $cache_handlerKeyFromTableList = array(); // Caching handlerKeys for table lists
151 var $cache_mappingFromTableList = array(); // Caching mapping information for table lists
152 var $cache_autoIncFields = array(); // parsed SQL from standard DB dump file
153 var $cache_fieldType = array(); // field types for tables/fields
154 var $cache_primaryKeys = array(); // primary keys
155
156 /**
157 * SQL parser
158 *
159 * @var tx_dbal_sqlengine
160 */
161 var $SQLparser;
162
163 /**
164 * Installer
165 *
166 * @var t3lib_install
167 */
168 var $Installer;
169
170
171 /**
172 * Constructor.
173 * Creates SQL parser object and imports configuration from $TYPO3_CONF_VARS['EXTCONF']['dbal']
174 */
175 public function __construct() {
176 // Set SQL parser object for internal use:
177 $this->SQLparser = t3lib_div::makeInstance('tx_dbal_sqlengine');
178 $this->Installer = t3lib_div::makeInstance('t3lib_install');
179
180 // Set internal variables with configuration:
181 $this->conf = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
182 $this->initInternalVariables();
183 }
184
185 /**
186 * Setting internal variables from $this->conf.
187 *
188 * @return void
189 */
190 protected function initInternalVariables() {
191 // Set outside configuration:
192 if (isset($this->conf['mapping'])) {
193 $this->mapping = $this->conf['mapping'];
194 }
195 if (isset($this->conf['table2handlerKeys'])) {
196 $this->table2handlerKeys = $this->conf['table2handlerKeys'];
197 }
198 if (isset($this->conf['handlerCfg'])) {
199 $this->handlerCfg = $this->conf['handlerCfg'];
200 }
201
202 $this->cacheFieldInfo();
203 // Debugging settings:
204 $this->printErrors = $this->conf['debugOptions']['printErrors'] ? TRUE : FALSE;
205 $this->debug = $this->conf['debugOptions']['enabled'] ? TRUE : FALSE;
206 }
207
208 /**
209 * Clears the cached field information file.
210 *
211 * @return void
212 */
213 public function clearCachedFieldInfo() {
214 if (file_exists(PATH_typo3conf . 'temp_fieldInfo.php')) {
215 unlink(PATH_typo3conf . 'temp_fieldInfo.php');
216 }
217 }
218
219 /**
220 * Caches the field information.
221 *
222 * @return void
223 */
224 public function cacheFieldInfo() {
225 $extSQL = '';
226 $parsedExtSQL = array();
227
228 // try to fetch cached file first
229 // file is removed when admin_query() is called
230 if (file_exists(PATH_typo3conf . 'temp_fieldInfo.php')) {
231 $fdata = unserialize(t3lib_div::getUrl(PATH_typo3conf . 'temp_fieldInfo.php'));
232 $this->cache_autoIncFields = $fdata['incFields'];
233 $this->cache_fieldType = $fdata['fieldTypes'];
234 $this->cache_primaryKeys = $fdata['primaryKeys'];
235 } else {
236 // handle stddb.sql, parse and analyze
237 $extSQL = t3lib_div::getUrl(PATH_site . 't3lib/stddb/tables.sql');
238 $parsedExtSQL = $this->Installer->getFieldDefinitions_fileContent($extSQL);
239 $this->analyzeFields($parsedExtSQL);
240
241 // loop over all installed extensions
242 foreach ($GLOBALS['TYPO3_LOADED_EXT'] as $ext => $v) {
243 if (!is_array($v) || !isset($v['ext_tables.sql'])) {
244 continue;
245 }
246
247 // fetch db dump (if any) and parse it, then analyze
248 $extSQL = t3lib_div::getUrl($v['ext_tables.sql']);
249 $parsedExtSQL = $this->Installer->getFieldDefinitions_fileContent($extSQL);
250 $this->analyzeFields($parsedExtSQL);
251 }
252
253 $cachedFieldInfo = array('incFields' => $this->cache_autoIncFields, 'fieldTypes' => $this->cache_fieldType, 'primaryKeys' => $this->cache_primaryKeys);
254 $cachedFieldInfo = serialize($this->mapCachedFieldInfo($cachedFieldInfo));
255
256 // write serialized content to file
257 t3lib_div::writeFile(PATH_typo3conf . 'temp_fieldInfo.php', $cachedFieldInfo);
258
259 if (strcmp(t3lib_div::getUrl(PATH_typo3conf . 'temp_fieldInfo.php'), $cachedFieldInfo)) {
260 die('typo3temp/temp_incfields.php was NOT updated properly (written content didn\'t match file content) - maybe write access problem?');
261 }
262 }
263 }
264
265 /**
266 * Analyzes fields and adds the extracted information to the field type, auto increment and primary key info caches.
267 *
268 * @param array $parsedExtSQL The output produced by t3lib_install::getFieldDefinitions_fileContent()
269 * @return void
270 * @see t3lib_install::getFieldDefinitions_fileContent()
271 */
272 protected function analyzeFields($parsedExtSQL) {
273 foreach ($parsedExtSQL as $table => $tdef) {
274 if (is_array($tdef['fields'])) {
275 foreach ($tdef['fields'] as $field => $fdef) {
276 $fdef = $this->SQLparser->parseFieldDef($fdef);
277 $this->cache_fieldType[$table][$field]['type'] = $fdef['fieldType'];
278 $this->cache_fieldType[$table][$field]['metaType'] = $this->MySQLMetaType($fdef['fieldType']);
279 $this->cache_fieldType[$table][$field]['notnull'] = (isset($fdef['featureIndex']['NOTNULL']) && !$this->SQLparser->checkEmptyDefaultValue($fdef['featureIndex'])) ? 1 : 0;
280 if (isset($fdef['featureIndex']['DEFAULT'])) {
281 $default = $fdef['featureIndex']['DEFAULT']['value'][0];
282 if (isset($fdef['featureIndex']['DEFAULT']['value'][1])) {
283 $default = $fdef['featureIndex']['DEFAULT']['value'][1] . $default . $fdef['featureIndex']['DEFAULT']['value'][1];
284 }
285 $this->cache_fieldType[$table][$field]['default'] = $default;
286 }
287 if (isset($fdef['featureIndex']['AUTO_INCREMENT'])) {
288 $this->cache_autoIncFields[$table] = $field;
289 }
290 if (isset($tdef['keys']['PRIMARY'])) {
291 $this->cache_primaryKeys[$table] = substr($tdef['keys']['PRIMARY'], 13, -1);
292 }
293 }
294 }
295 }
296 }
297
298 /**
299 * This function builds all definitions for mapped tables and fields
300 * @see cacheFieldInfo()
301 */
302 protected function mapCachedFieldInfo($fieldInfo) {
303 if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'])) {
304 foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'] as $mappedTable => $mappedConf) {
305 if (array_key_exists($mappedTable, $fieldInfo['incFields'])) {
306 $mappedTableAlias = $mappedConf['mapTableName'];
307 if (isset($mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]])) {
308 $fieldInfo['incFields'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]];
309 } else {
310 $fieldInfo['incFields'][$mappedTableAlias] = $fieldInfo['incFields'][$mappedTable];
311 }
312 }
313
314 if (array_key_exists($mappedTable, $fieldInfo['fieldTypes'])) {
315 foreach ($fieldInfo['fieldTypes'][$mappedTable] as $field => $fieldConf) {
316 $tempMappedFieldConf[$mappedConf['mapFieldNames'][$field]] = $fieldConf;
317 }
318
319 $fieldInfo['fieldTypes'][$mappedConf['mapTableName']] = $tempMappedFieldConf;
320 }
321
322 if (array_key_exists($mappedTable, $fieldInfo['primaryKeys'])) {
323 $mappedTableAlias = $mappedConf['mapTableName'];
324 if (isset($mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]])) {
325 $fieldInfo['primaryKeys'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]];
326 } else {
327 $fieldInfo['primaryKeys'][$mappedTableAlias] = $fieldInfo['primaryKeys'][$mappedTable];
328 }
329 }
330 }
331 }
332
333 return $fieldInfo;
334 }
335
336
337 /************************************
338 *
339 * Query Building (Overriding parent methods)
340 * These functions are extending counterparts in the parent class.
341 *
342 **************************************/
343
344 /* From the ADOdb documentation, this is what we do (_Execute for SELECT, _query for the other actions)
345
346 Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead.
347 Both these functions share the same parameters as Execute().
348
349 If you do not have any bind parameters or your database supports binding (without emulation), then you can call _Execute() directly.
350 Calling this function bypasses bind emulation. Debugging is still supported in _Execute().
351
352 If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query.
353 This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging, and recordset handling. Either
354 the resultid, TRUE or FALSE are returned by _query().
355 */
356
357 /**
358 * Inserts a record for $table from the array with field/value pairs $fields_values.
359 *
360 * @param string Table name
361 * @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
362 * @param mixed List/array of keys NOT to quote (eg. SQL functions)
363 * @return mixed Result from handler, usually TRUE when success and FALSE on failure
364 */
365 public function exec_INSERTquery($table, $fields_values, $no_quote_fields = '') {
366
367 if ($this->debug) {
368 $pt = t3lib_div::milliseconds();
369 }
370
371 // Do field mapping if needed:
372 $ORIG_tableName = $table;
373 if ($tableArray = $this->map_needMapping($table)) {
374
375 // Field mapping of array:
376 $fields_values = $this->map_assocArray($fields_values, $tableArray);
377
378 // Table name:
379 if ($this->mapping[$table]['mapTableName']) {
380 $table = $this->mapping[$table]['mapTableName'];
381 }
382 }
383 // Select API:
384 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
385 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
386 case 'native':
387 $this->lastQuery = $this->INSERTquery($table,$fields_values,$no_quote_fields);
388 if (is_string($this->lastQuery)) {
389 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
390 } else {
391 $sqlResult = mysql_query($this->lastQuery[0], $this->handlerInstance[$this->lastHandlerKey]['link']);
392 foreach ($this->lastQuery[1] as $field => $content) {
393 mysql_query('UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table) . ' WHERE ' . $this->quoteWhereClause($where), $this->handlerInstance[$this->lastHandlerKey]['link']);
394 }
395 }
396 break;
397 case 'adodb':
398 // auto generate ID for auto_increment fields if not present (static import needs this!)
399 // should we check the table name here (static_*)?
400 if (isset($this->cache_autoIncFields[$table])) {
401 if (isset($fields_values[$this->cache_autoIncFields[$table]])) {
402 $new_id = $fields_values[$this->cache_autoIncFields[$table]];
403 if ($table != 'tx_dbal_debuglog') {
404 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
405 }
406 } else {
407 $new_id = $this->handlerInstance[$this->lastHandlerKey]->GenID($table.'_'.$this->cache_autoIncFields[$table], $this->handlerInstance[$this->lastHandlerKey]->sequenceStart);
408 $fields_values[$this->cache_autoIncFields[$table]] = $new_id;
409 if ($table != 'tx_dbal_debuglog') {
410 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
411 }
412 }
413 }
414
415 $this->lastQuery = $this->INSERTquery($table,$fields_values,$no_quote_fields);
416 if (is_string($this->lastQuery)) {
417 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
418 } else {
419 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
420 if (strlen($this->lastQuery[0])) {
421 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0],FALSE);
422 }
423 if (is_array($this->lastQuery[1])) {
424 foreach ($this->lastQuery[1] as $field => $content) {
425 if (empty($content)) continue;
426
427 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
428 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($this->cache_autoIncFields[$table].'='.$new_id));
429 } elseif (isset($this->cache_primaryKeys[$table])) {
430 $where = '';
431 $pks = explode(',', $this->cache_primaryKeys[$table]);
432 foreach ($pks as $pk) {
433 if (isset($fields_values[$pk]))
434 $where .= $pk.'='.$this->fullQuoteStr($fields_values[$pk], $table).' AND ';
435 }
436 $where = $this->quoteWhereClause($where.'1=1');
437 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$where);
438 } else {
439 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
440 die('Could not update BLOB >>>> no WHERE clause found!'); // should never ever happen
441 }
442 }
443 }
444 if (is_array($this->lastQuery[2])) {
445 foreach ($this->lastQuery[2] as $field => $content) {
446 if (empty($content)) continue;
447
448 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
449 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($this->cache_autoIncFields[$table].'='.$new_id));
450 } elseif (isset($this->cache_primaryKeys[$table])) {
451 $where = '';
452 $pks = explode(',', $this->cache_primaryKeys[$table]);
453 foreach ($pks as $pk) {
454 if (isset($fields_values[$pk]))
455 $where .= $pk.'='.$this->fullQuoteStr($fields_values[$pk], $table).' AND ';
456 }
457 $where = $this->quoteWhereClause($where.'1=1');
458 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$where);
459 } else {
460 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
461 die('Could not update CLOB >>>> no WHERE clause found!'); // should never ever happen
462 }
463 }
464 }
465 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
466 }
467 break;
468 case 'userdefined':
469 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_INSERTquery($table,$fields_values,$no_quote_fields);
470 break;
471 }
472
473 if ($this->printErrors && $this->sql_error()) {
474 debug(array($this->lastQuery, $this->sql_error()));
475 }
476
477 if ($this->debug) {
478 $this->debugHandler(
479 'exec_INSERTquery',
480 t3lib_div::milliseconds()-$pt,
481 array(
482 'handlerType' => $hType,
483 'args' => array($table,$fields_values),
484 'ORIG_tablename' => $ORIG_tableName
485 )
486 );
487 }
488 // Return output:
489 return $sqlResult;
490 }
491
492 /**
493 * Creates and executes an INSERT SQL-statement for $table with multiple rows.
494 * This method uses exec_INSERTquery() and is just a syntax wrapper to it.
495 *
496 * @param string Table name
497 * @param array Field names
498 * @param array Table rows. Each row should be an array with field values mapping to $fields
499 * @param string/array See fullQuoteArray()
500 * @return mixed Result from last handler, usually TRUE when success and FALSE on failure
501 */
502 public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
503 if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
504 return parent::exec_INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
505 }
506
507 foreach ($rows as $row) {
508 $fields_values = array();
509 foreach ($fields as $key => $value) {
510 $fields_values[$value] = $row[$key];
511 }
512 $res = $this->exec_INSERTquery($table, $fields_values, $no_quote_fields);
513 }
514
515 return $res;
516 }
517
518 /**
519 * Updates a record from $table
520 *
521 * @param string Database tablename
522 * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
523 * @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
524 * @param mixed List/array of keys NOT to quote (eg. SQL functions)
525 * @return mixed Result from handler, usually TRUE when success and FALSE on failure
526 */
527 public function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields = '') {
528 if ($this->debug) {
529 $pt = t3lib_div::milliseconds();
530 }
531
532 // Do table/field mapping:
533 $ORIG_tableName = $table;
534 if ($tableArray = $this->map_needMapping($table)) {
535
536 // Field mapping of array:
537 $fields_values = $this->map_assocArray($fields_values,$tableArray);
538
539 // Where clause table and field mapping:
540 $whereParts = $this->SQLparser->parseWhereClause($where);
541 $this->map_sqlParts($whereParts,$tableArray[0]['table']);
542 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
543
544 // Table name:
545 if ($this->mapping[$table]['mapTableName']) {
546 $table = $this->mapping[$table]['mapTableName'];
547 }
548 }
549
550 // Select API
551 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
552 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
553 case 'native':
554 $this->lastQuery = $this->UPDATEquery($table,$where,$fields_values,$no_quote_fields);
555 if (is_string($this->lastQuery)) {
556 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
557 }
558 else {
559 $sqlResult = mysql_query($this->lastQuery[0], $this->handlerInstance[$this->lastHandlerKey]['link']);
560 foreach ($this->lastQuery[1] as $field => $content) {
561 mysql_query('UPDATE '.$this->quoteFromTables($table).' SET '.$this->quoteFromTables($field).'='.$this->fullQuoteStr($content,$table).' WHERE '.$this->quoteWhereClause($where), $this->handlerInstance[$this->lastHandlerKey]['link']);
562 }
563 }
564 break;
565 case 'adodb':
566 $this->lastQuery = $this->UPDATEquery($table,$where,$fields_values,$no_quote_fields);
567 if (is_string($this->lastQuery)) {
568 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
569 } else {
570 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
571 if (strlen($this->lastQuery[0])) {
572 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0],FALSE);
573 }
574 if (is_array($this->lastQuery[1])) {
575 foreach ($this->lastQuery[1] as $field => $content) {
576 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($where));
577 }
578 }
579 if (is_array($this->lastQuery[2])) {
580 foreach ($this->lastQuery[2] as $field => $content) {
581 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($where));
582 }
583 }
584 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
585 }
586 break;
587 case 'userdefined':
588 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields);
589 break;
590 }
591
592 if ($this->printErrors && $this->sql_error()) {
593 debug(array($this->lastQuery, $this->sql_error()));
594 }
595
596 if ($this->debug) {
597 $this->debugHandler(
598 'exec_UPDATEquery',
599 t3lib_div::milliseconds()-$pt,
600 array(
601 'handlerType' => $hType,
602 'args' => array($table,$where, $fields_values),
603 'ORIG_from_table' => $ORIG_tableName
604 )
605 );
606 }
607
608 // Return result:
609 return $sqlResult;
610 }
611
612 /**
613 * Deletes records from table
614 *
615 * @param string Database tablename
616 * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
617 * @return mixed Result from handler
618 */
619 public function exec_DELETEquery($table, $where) {
620 if ($this->debug) {
621 $pt = t3lib_div::milliseconds();
622 }
623
624 // Do table/field mapping:
625 $ORIG_tableName = $table;
626 if ($tableArray = $this->map_needMapping($table)) {
627
628 // Where clause:
629 $whereParts = $this->SQLparser->parseWhereClause($where);
630 $this->map_sqlParts($whereParts,$tableArray[0]['table']);
631 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
632
633 // Table name:
634 if ($this->mapping[$table]['mapTableName']) {
635 $table = $this->mapping[$table]['mapTableName'];
636 }
637 }
638
639 // Select API
640 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
641 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
642 case 'native':
643 $this->lastQuery = $this->DELETEquery($table,$where);
644 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
645 break;
646 case 'adodb':
647 $this->lastQuery = $this->DELETEquery($table,$where);
648 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
649 break;
650 case 'userdefined':
651 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_DELETEquery($table,$where);
652 break;
653 }
654
655 if ($this->printErrors && $this->sql_error()) {
656 debug(array($this->lastQuery, $this->sql_error()));
657 }
658
659 if ($this->debug) {
660 $this->debugHandler(
661 'exec_DELETEquery',
662 t3lib_div::milliseconds()-$pt,
663 array(
664 'handlerType' => $hType,
665 'args' => array($table,$where),
666 'ORIG_from_table' => $ORIG_tableName
667 )
668 );
669 }
670
671 // Return result:
672 return $sqlResult;
673 }
674
675 /**
676 * Selects records from Data Source
677 *
678 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
679 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
680 * @param string $where_clause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQquoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
681 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
682 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
683 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
684 * @return mixed Result from handler. Typically object from DBAL layers.
685 */
686 public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
687 if ($this->debug) {
688 $pt = t3lib_div::milliseconds();
689 }
690
691 // Map table / field names if needed:
692 $ORIG_tableName = $from_table; // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
693 if ($tableArray = $this->map_needMapping($ORIG_tableName)) {
694 $this->map_remapSELECTQueryParts($select_fields,$from_table,$where_clause,$groupBy,$orderBy); // Variables passed by reference!
695 }
696
697 // Get handler key and select API:
698 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
699 $hType = (string)$this->handlerCfg[$this->lastHandlerKey]['type'];
700 switch ($hType) {
701 case 'native':
702 $this->lastQuery = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
703 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
704 $this->resourceIdToTableNameMap[(string)$sqlResult] = $ORIG_tableName;
705 break;
706 case 'adodb':
707 if ($limit != '') {
708 $splitLimit = t3lib_div::intExplode(',', $limit); // Splitting the limit values:
709 if ($splitLimit[1]) { // If there are two parameters, do mapping differently than otherwise:
710 $numrows = $splitLimit[1];
711 $offset = $splitLimit[0];
712 } else {
713 $numrows = $splitLimit[0];
714 $offset = 0;
715 }
716
717 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy), $numrows, $offset);
718 $this->lastQuery = $sqlResult->sql;
719 } else {
720 $this->lastQuery = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy);
721 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_Execute($this->lastQuery);
722 }
723
724 $sqlResult->TYPO3_DBAL_handlerType = 'adodb'; // Setting handler type in result object (for later recognition!)
725 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
726 break;
727 case 'userdefined':
728 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
729 if (is_object($sqlResult)) {
730 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined'; // Setting handler type in result object (for later recognition!)
731 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
732 }
733 break;
734 }
735
736 if ($this->printErrors && $this->sql_error()) {
737 debug(array($this->lastQuery, $this->sql_error()));
738 }
739
740 if ($this->debug) {
741 $this->debugHandler(
742 'exec_SELECTquery',
743 t3lib_div::milliseconds()-$pt,
744 array(
745 'handlerType' => $hType,
746 'args' => array($from_table,$select_fields,$where_clause,$groupBy,$orderBy,$limit),
747 'ORIG_from_table' => $ORIG_tableName
748 )
749 );
750 }
751
752 // Return result handler.
753 return $sqlResult;
754 }
755
756 /**
757 * Truncates a table.
758 *
759 * @param string Database tablename
760 * @return mixed Result from handler
761 */
762 public function exec_TRUNCATEquery($table) {
763 if ($this->debug) {
764 $pt = t3lib_div::milliseconds();
765 }
766
767 // Do table/field mapping:
768 $ORIG_tableName = $table;
769 if ($tableArray = $this->map_needMapping($table)) {
770 // Table name:
771 if ($this->mapping[$table]['mapTableName']) {
772 $table = $this->mapping[$table]['mapTableName'];
773 }
774 }
775
776 // Select API
777 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
778 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
779 case 'native':
780 $this->lastQuery = $this->TRUNCATEquery($table);
781 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
782 break;
783 case 'adodb':
784 $this->lastQuery = $this->TRUNCATEquery($table);
785 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
786 break;
787 case 'userdefined':
788 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_TRUNCATEquery($table,$where);
789 break;
790 }
791
792 if ($this->printErrors && $this->sql_error()) {
793 debug(array($this->lastQuery, $this->sql_error()));
794 }
795
796 if ($this->debug) {
797 $this->debugHandler(
798 'exec_TRUNCATEquery',
799 t3lib_div::milliseconds() - $pt,
800 array(
801 'handlerType' => $hType,
802 'args' => array($table),
803 'ORIG_from_table' => $ORIG_tableName
804 )
805 );
806 }
807
808 // Return result:
809 return $sqlResult;
810 }
811
812 /**
813 * Executes a query.
814 * EXPERIMENTAL since TYPO3 4.4.
815 *
816 * @param array $queryParts SQL parsed by method parseSQL() of t3lib_sqlparser
817 * @return pointer Result pointer / DBAL object
818 * @see ux_t3lib_db::sql_query()
819 */
820 protected function exec_query(array $queryParts) {
821 switch ($queryParts['type']) {
822 case 'SELECT':
823 $selectFields = $this->SQLparser->compileFieldList($queryParts['SELECT']);
824 $fromTables = $this->SQLparser->compileFromTables($queryParts['FROM']);
825 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
826 $groupBy = isset($queryParts['GROUPBY']) ? $this->SQLparser->compileWhereClause($queryParts['GROUPBY']) : '';
827 $orderBy = isset($queryParts['GROUPBY']) ? $this->SQLparser->compileWhereClause($queryParts['ORDERBY']) : '';
828 $limit = isset($queryParts['LIMIT']) ? $this->SQLparser->compileWhereClause($queryParts['LIMIT']) : '';
829 return $this->exec_SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy, $limit);
830
831 case 'UPDATE':
832 $table = $queryParts['TABLE'];
833 $fields = array();
834 foreach ($components['FIELDS'] as $fN => $fV) {
835 $fields[$fN] = $fV[0];
836 }
837 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
838 return $this->exec_UPDATEquery($table, $whereClause, $fields);
839
840 case 'INSERT':
841 $table = $queryParts['TABLE'];
842 $values = array();
843 if (isset($queryParts['VALUES_ONLY']) && is_array($queryParts['VALUES_ONLY'])) {
844 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$table];
845 $fc = 0;
846 foreach ($fields as $fn => $fd) {
847 $values[$fn] = $queryParts['VALUES_ONLY'][$fc++][0];
848 }
849 } else {
850 foreach ($queryParts['FIELDS'] as $fN => $fV) {
851 $values[$fN] = $fV[0];
852 }
853 }
854 return $this->exec_INSERTquery($table, $values);
855
856 case 'DELETE':
857 $table = $queryParts['TABLE'];
858 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
859 return $this->exec_DELETEquery($table, $whereClause);
860
861 case 'TRUNCATETABLE':
862 $table = $queryParts['TABLE'];
863 return $this->exec_TRUNCATEquery($table);
864 }
865 }
866
867
868
869 /**************************************
870 *
871 * Query building
872 *
873 **************************************/
874
875 /**
876 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
877 * Usage count/core: 4
878 *
879 * @param string See exec_INSERTquery()
880 * @param array See exec_INSERTquery()
881 * @param mixed See exec_INSERTquery()
882 * @return mixed Full SQL query for INSERT as string or array (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields
883 */
884 public function INSERTquery($table, $fields_values, $no_quote_fields = '') {
885 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
886 if (is_array($fields_values) && count($fields_values)) {
887
888 if (is_string($no_quote_fields)) {
889 $no_quote_fields = explode(',', $no_quote_fields);
890 } elseif (!is_array($no_quote_fields)) {
891 $no_quote_fields = array();
892 }
893
894 $blobfields = array();
895 $nArr = array();
896 foreach ($fields_values as $k => $v) {
897 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
898 // we skip the field in the regular INSERT statement, it is only in blobfields
899 $blobfields[$this->quoteFieldNames($k)] = $v;
900 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
901 // we skip the field in the regular INSERT statement, it is only in clobfields
902 $clobfields[$this->quoteFieldNames($k)] = $v;
903 } else {
904 // Add slashes old-school:
905 // cast numerical values
906 $mt = $this->sql_field_metatype($table, $k);
907 if ($mt{0} == 'I') {
908 $v = (int)$v;
909 } else if ($mt{0} == 'F') {
910 $v = (double)$v;
911 }
912
913 $nArr[$this->quoteFieldNames($k)] = (!in_array($k,$no_quote_fields)) ? $this->fullQuoteStr($v, $table) : $v;
914 }
915 }
916
917 if (count($blobfields) || count($clobfields)) {
918 if (count($nArr)) {
919 $query[0] = 'INSERT INTO ' . $this->quoteFromTables($table) . '
920 (
921 ' . implode(',
922 ', array_keys($nArr)) . '
923 ) VALUES (
924 ' . implode(',
925 ', $nArr) . '
926 )';
927 }
928 if (count($blobfields)) $query[1] = $blobfields;
929 if (count($clobfields)) $query[2] = $clobfields;
930 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query[0];
931 } else {
932 $query = 'INSERT INTO '.$this->quoteFromTables($table).'
933 (
934 ' . implode(',
935 ', array_keys($nArr)) . '
936 ) VALUES (
937 ' . implode(',
938 ', $nArr) . '
939 )';
940
941 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
942 }
943
944 return $query;
945 }
946 }
947
948 /**
949 * Creates an INSERT SQL-statement for $table with multiple rows.
950 * This method will create multiple INSERT queries concatenated with ';'
951 *
952 * @param string Table name
953 * @param array Field names
954 * @param array Table rows. Each row should be an array with field values mapping to $fields
955 * @param string/array See fullQuoteArray()
956 * @return array Full SQL query for INSERT as array of strings (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned for each row, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields.
957 */
958 public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
959 if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
960 return parent::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
961 }
962
963 $result = array();
964
965 foreach ($rows as $row) {
966 $fields_values = array();
967 foreach ($fields as $key => $value) {
968 $fields_values[$value] = $row[$key];
969 }
970 $rowQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
971 if (is_array($rowQuery)) {
972 $result[] = $rowQuery;
973 } else {
974 $result[][0] = $rowQuery;
975 }
976 }
977
978 return $result;
979 }
980
981 /**
982 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
983 * Usage count/core: 6
984 *
985 * @param string See exec_UPDATEquery()
986 * @param string See exec_UPDATEquery()
987 * @param array See exec_UPDATEquery()
988 * @param mixed See exec_UPDATEquery()
989 * @return mixed Full SQL query for UPDATE as string or array (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields
990 */
991 public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = '') {
992 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
993 if (is_string($where)) {
994 $fields = array();
995 $blobfields = array();
996 $clobfields = array();
997 if (is_array($fields_values) && count($fields_values)) {
998
999 if (is_string($no_quote_fields)) {
1000 $no_quote_fields = explode(',', $no_quote_fields);
1001 } elseif (!is_array($no_quote_fields)) {
1002 $no_quote_fields = array();
1003 }
1004
1005 $nArr = array();
1006 foreach ($fields_values as $k => $v) {
1007 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
1008 // we skip the field in the regular UPDATE statement, it is only in blobfields
1009 $blobfields[$this->quoteFieldNames($k)] = $v;
1010 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
1011 // we skip the field in the regular UPDATE statement, it is only in clobfields
1012 $clobfields[$this->quoteFieldNames($k)] = $v;
1013 } else {
1014 // Add slashes old-school:
1015 // cast numeric values
1016 $mt = $this->sql_field_metatype($table, $k);
1017 if ($mt{0} == 'I') {
1018 $v = (int)$v;
1019 } else if ($mt{0} == 'F') {
1020 $v = (double)$v;
1021 }
1022 $nArr[] = $this->quoteFieldNames($k) . '=' . ((!in_array($k, $no_quote_fields)) ? $this->fullQuoteStr($v, $table) : $v);
1023 }
1024 }
1025 }
1026
1027 if (count($blobfields) || count($clobfields)) {
1028 if (count($nArr)) {
1029 $query[0] = 'UPDATE ' . $this->quoteFromTables($table) . '
1030 SET
1031 ' . implode(',
1032 ', $nArr) .
1033 (strlen($where) > 0 ? '
1034 WHERE
1035 ' . $this->quoteWhereClause($where) : '');
1036 }
1037 if (count($blobfields)) {
1038 $query[1] = $blobfields;
1039 }
1040 if (count($clobfields)) {
1041 $query[2] = $clobfields;
1042 }
1043 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1044 $this->debug_lastBuiltQuery = $query[0];
1045 }
1046 } else {
1047 $query = 'UPDATE ' . $this->quoteFromTables($table) . '
1048 SET
1049 ' . implode(',
1050 ', $nArr) .
1051 (strlen($where) > 0 ? '
1052 WHERE
1053 ' . $this->quoteWhereClause($where) : '');
1054
1055 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1056 $this->debug_lastBuiltQuery = $query;
1057 }
1058 }
1059 return $query;
1060 } else {
1061 throw new InvalidArgumentException(
1062 'TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !',
1063 1270853880
1064 );
1065 }
1066 }
1067
1068 /**
1069 * Creates a DELETE SQL-statement for $table where $where-clause
1070 * Usage count/core: 3
1071 *
1072 * @param string See exec_DELETEquery()
1073 * @param string See exec_DELETEquery()
1074 * @return string Full SQL query for DELETE
1075 */
1076 public function DELETEquery($table, $where) {
1077 if (is_string($where)) {
1078 $table = $this->quoteFromTables($table);
1079 $where = $this->quoteWhereClause($where);
1080
1081 $query = parent::DELETEquery($table, $where);
1082
1083 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
1084 return $query;
1085 } else {
1086 die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
1087 }
1088 }
1089
1090 /**
1091 * Creates a SELECT SQL-statement
1092 * Usage count/core: 11
1093 *
1094 * @param string See exec_SELECTquery()
1095 * @param string See exec_SELECTquery()
1096 * @param string See exec_SELECTquery()
1097 * @param string See exec_SELECTquery()
1098 * @param string See exec_SELECTquery()
1099 * @param string See exec_SELECTquery()
1100 * @return string Full SQL query for SELECT
1101 */
1102 public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
1103 $this->lastHandlerKey = $this->handler_getFromTableList($from_table);
1104 $hType = (string)$this->handlerCfg[$this->lastHandlerKey]['type'];
1105 if ($hType === 'adodb' && $this->runningADOdbDriver('postgres')) {
1106 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1107 $splitLimit = t3lib_div::intExplode(',', $limit); // Splitting the limit values:
1108 if ($splitLimit[1]) { // If there are two parameters, do mapping differently than otherwise:
1109 $numrows = $splitLimit[1];
1110 $offset = $splitLimit[0];
1111 $limit = $numrows . ' OFFSET ' . $offset;
1112 }
1113 }
1114
1115 $select_fields = $this->quoteFieldNames($select_fields);
1116 $from_table = $this->quoteFromTables($from_table);
1117 $where_clause = $this->quoteWhereClause($where_clause);
1118 $groupBy = $this->quoteGroupBy($groupBy);
1119 $orderBy = $this->quoteOrderBy($orderBy);
1120
1121 // Call parent method to build actual query
1122 $query = parent::SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
1123
1124 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
1125
1126 return $query;
1127 }
1128
1129 /**
1130 * Creates a TRUNCATE TABLE SQL-statement
1131 *
1132 * @param string See exec_TRUNCATEquery()
1133 * @return string Full SQL query for TRUNCATE TABLE
1134 */
1135 public function TRUNCATEquery($table) {
1136 $table = $this->quoteFromTables($table);
1137
1138 // Call parent method to build actual query
1139 $query = parent::TRUNCATEquery($table);
1140
1141 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1142 $this->debug_lastBuiltQuery = $query;
1143 }
1144
1145 return $query;
1146 }
1147
1148
1149 /**************************************
1150 *
1151 * Functions for quoting table/field names
1152 *
1153 **************************************/
1154
1155 /**
1156 * Quotes components of a SELECT subquery.
1157 *
1158 * @param array $components Array of SQL query components
1159 * @return array
1160 */
1161 protected function quoteSELECTsubquery(array $components) {
1162 $components['SELECT'] = $this->_quoteFieldNames($components['SELECT']);
1163 $components['FROM'] = $this->_quoteFromTables($components['FROM']);
1164 $components['WHERE'] = $this->_quoteWhereClause($components['WHERE']);
1165 return $components;
1166 }
1167
1168 /**
1169 * Quotes field (and table) names with the quote character suitable for the DB being used
1170 * Use quoteFieldNames instead!
1171 *
1172 * @param string List of fields to be selected from DB
1173 * @return string Quoted list of fields to be selected from DB
1174 * @deprecated since TYPO3 4.0
1175 */
1176 public function quoteSelectFields($select_fields) {
1177 $this->quoteFieldNames($select_fields);
1178 }
1179
1180 /**
1181 * Quotes field (and table) names with the quote character suitable for the DB being used
1182 *
1183 * @param string List of fields to be used in query to DB
1184 * @return string Quoted list of fields to be in query to DB
1185 */
1186 public function quoteFieldNames($select_fields) {
1187 if ($select_fields == '') return '';
1188 if ($this->runningNative()) return $select_fields;
1189
1190 $select_fields = $this->SQLparser->parseFieldList($select_fields);
1191 if ($this->SQLparser->parse_error) {
1192 die($this->SQLparser->parse_error . ' in ' . __FILE__ . ' : ' . __LINE__);
1193 }
1194 $select_fields = $this->_quoteFieldNames($select_fields);
1195
1196 return $this->SQLparser->compileFieldList($select_fields);
1197 }
1198
1199 /**
1200 * Quotes field (and table) names in a SQL SELECT clause acccording to DB rules
1201 *
1202 * @param array $select_fields The parsed fields to quote
1203 * @return array
1204 * @see quoteFieldNames()
1205 */
1206 protected function _quoteFieldNames(array $select_fields) {
1207 foreach ($select_fields as $k => $v) {
1208 if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*' && !is_numeric($select_fields[$k]['field'])) {
1209 $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
1210 }
1211 if ($select_fields[$k]['table'] != '' && !is_numeric($select_fields[$k]['table'])) {
1212 $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
1213 }
1214 if ($select_fields[$k]['as'] != '') {
1215 $select_fields[$k]['as'] = $this->quoteName($select_fields[$k]['as']);
1216 }
1217 if (isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*'){
1218 $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
1219 $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
1220 }
1221 if (isset($select_fields[$k]['flow-control'])) {
1222 // Quoting flow-control statements
1223 if ($select_fields[$k]['flow-control']['type'] === 'CASE') {
1224 if (isset($select_fields[$k]['flow-control']['case_field'])) {
1225 $select_fields[$k]['flow-control']['case_field'] = $this->quoteFieldNames($select_fields[$k]['flow-control']['case_field']);
1226 }
1227 foreach ($select_fields[$k]['flow-control']['when'] as $key => $when) {
1228 $select_fields[$k]['flow-control']['when'][$key]['when_value'] = $this->_quoteWhereClause($when['when_value']);
1229 }
1230 }
1231 }
1232 }
1233
1234 return $select_fields;
1235 }
1236
1237 /**
1238 * Quotes table names with the quote character suitable for the DB being used
1239 *
1240 * @param string List of tables to be selected from DB
1241 * @return string Quoted list of tables to be selected from DB
1242 */
1243 public function quoteFromTables($from_table) {
1244 if ($from_table == '') return '';
1245 if ($this->runningNative()) return $from_table;
1246
1247 $from_table = $this->SQLparser->parseFromTables($from_table);
1248 $from_table = $this->_quoteFromTables($from_table);
1249 return $this->SQLparser->compileFromTables($from_table);
1250 }
1251
1252 /**
1253 * Quotes table names in a SQL FROM clause acccording to DB rules
1254 *
1255 * @param array $from_table The parsed FROM clause to quote
1256 * @return array
1257 * @see quoteFromTables()
1258 */
1259 protected function _quoteFromTables(array $from_table) {
1260 foreach ($from_table as $k => $v) {
1261 $from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
1262 if ($from_table[$k]['as'] != '') {
1263 $from_table[$k]['as'] = $this->quoteName($from_table[$k]['as']);
1264 }
1265 if (is_array($v['JOIN'])) {
1266 foreach ($v['JOIN'] as $joinCnt => $join) {
1267 $from_table[$k]['JOIN'][$joinCnt]['withTable'] = $this->quoteName($join['withTable']);
1268 $from_table[$k]['JOIN'][$joinCnt]['as'] = ($join['as']) ? $this->quoteName($join['as']) : '';
1269 foreach ($from_table[$k]['JOIN'][$joinCnt]['ON'] as &$condition) {
1270 $condition['left']['table'] = ($condition['left']['table']) ? $this->quoteName($condition['left']['table']) : '';
1271 $condition['left']['field'] = $this->quoteName($condition['left']['field']);
1272 $condition['right']['table'] = ($condition['right']['table']) ? $this->quoteName($condition['right']['table']) : '';
1273 $condition['right']['field'] = $this->quoteName($condition['right']['field']);
1274 }
1275 }
1276 }
1277 }
1278
1279 return $from_table;
1280 }
1281
1282 /**
1283 * Quotes the field (and table) names within a where clause with the quote character suitable for the DB being used
1284 *
1285 * @param string A where clause that can e parsed by parseWhereClause
1286 * @return string Usable where clause with quoted field/table names
1287 */
1288 public function quoteWhereClause($where_clause) {
1289 if ($where_clause === '' || $this->runningNative()) return $where_clause;
1290
1291 $where_clause = $this->SQLparser->parseWhereClause($where_clause);
1292 if (is_array($where_clause)) {
1293 $where_clause = $this->_quoteWhereClause($where_clause);
1294 $where_clause = $this->SQLparser->compileWhereClause($where_clause);
1295 } else {
1296 die('Could not parse where clause in ' . __FILE__ . ' : ' . __LINE__);
1297 }
1298
1299 return $where_clause;
1300 }
1301
1302 /**
1303 * Quotes field names in a SQL WHERE clause acccording to DB rules
1304 *
1305 * @param array $where_clause The parsed WHERE clause to quote
1306 * @return array
1307 * @see quoteWhereClause()
1308 */
1309 protected function _quoteWhereClause(array $where_clause) {
1310 foreach ($where_clause as $k => $v) {
1311 // Look for sublevel:
1312 if (is_array($where_clause[$k]['sub'])) {
1313 $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
1314 } elseif (isset($v['func'])) {
1315 switch ($where_clause[$k]['func']['type']) {
1316 case 'EXISTS':
1317 $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
1318 break;
1319 case 'IFNULL':
1320 case 'LOCATE':
1321 if ($where_clause[$k]['func']['table'] != '') {
1322 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1323 }
1324 if ($where_clause[$k]['func']['field'] != '') {
1325 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1326 }
1327 break;
1328 }
1329 } else {
1330 if ($where_clause[$k]['table'] != '') {
1331 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1332 }
1333 if (!is_numeric($where_clause[$k]['field'])) {
1334 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1335 }
1336 if (isset($where_clause[$k]['calc_table'])) {
1337 if ($where_clause[$k]['calc_table'] != '') {
1338 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1339 }
1340 if ($where_clause[$k]['calc_field'] != '') {
1341 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1342 }
1343 }
1344 }
1345 if ($where_clause[$k]['comparator']) {
1346 if (isset($v['value']['operator'])) {
1347 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1348 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1349 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1350 }
1351 } else {
1352 // Detecting value type; list or plain:
1353 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ',"\n", "\r", "\t"), '', $where_clause[$k]['comparator'])))) {
1354 if (isset($v['subquery'])) {
1355 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1356 }
1357 } else {
1358 if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')) {
1359 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1360 }
1361 }
1362 }
1363 }
1364 }
1365
1366 return $where_clause;
1367 }
1368
1369 /**
1370 * [Describe function...]
1371 *
1372 * @param [type] $$groupBy: ...
1373 * @return [type] ...
1374 */
1375 protected function quoteGroupBy($groupBy) {
1376 if ($groupBy === '') return '';
1377 if ($this->runningNative()) return $groupBy;
1378
1379 $groupBy = $this->SQLparser->parseFieldList($groupBy);
1380 foreach ($groupBy as $k => $v) {
1381 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1382 if ($groupBy[$k]['table'] != '') {
1383 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1384 }
1385 }
1386 return $this->SQLparser->compileFieldList($groupBy);
1387 }
1388
1389 /**
1390 * [Describe function...]
1391 *
1392 * @param [type] $$orderBy: ...
1393 * @return [type] ...
1394 */
1395 protected function quoteOrderBy($orderBy) {
1396 if ($orderBy === '') return '';
1397 if ($this->runningNative()) return $orderBy;
1398
1399 $orderBy = $this->SQLparser->parseFieldList($orderBy);
1400 foreach ($orderBy as $k => $v) {
1401 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1402 if ($orderBy[$k]['table'] != '') {
1403 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1404 }
1405 }
1406 return $this->SQLparser->compileFieldList($orderBy);
1407 }
1408
1409
1410
1411 /**************************************
1412 *
1413 * Various helper functions
1414 *
1415 **************************************/
1416
1417 /**
1418 * Escaping and quoting values for SQL statements.
1419 *
1420 * @param string Input string
1421 * @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
1422 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1423 * @see quoteStr()
1424 */
1425 public function fullQuoteStr($str, $table) {
1426 return '\'' . $this->quoteStr($str, $table) . '\'';
1427 }
1428
1429 /**
1430 * Substitution for PHP function "addslashes()"
1431 * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
1432 *
1433 * @param string Input string
1434 * @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
1435 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1436 * @see quoteStr()
1437 */
1438 public function quoteStr($str, $table) {
1439 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1440 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1441 case 'native':
1442 if ($this->handlerInstance[$this->lastHandlerKey]['link']) {
1443 $str = mysql_real_escape_string($str, $this->handlerInstance[$this->lastHandlerKey]['link']);
1444 } else {
1445 // link may be null when unit testing DBAL
1446 $str = str_replace('\'', '\\\'', $str);
1447 }
1448 break;
1449 case 'adodb':
1450 $str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str), 1, -1);
1451 break;
1452 case 'userdefined':
1453 $str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
1454 break;
1455 default:
1456 die('No handler found!!!');
1457 break;
1458 }
1459
1460 return $str;
1461 }
1462
1463 /**
1464 * Quotes an object name (table name, field, ...)
1465 *
1466 * @param string Object's name
1467 * @param string Handler key
1468 * @param boolean If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
1469 * @return string Properly-quoted object's name
1470 */
1471 public function quoteName($name, $handlerKey = NULL, $useBackticks = FALSE) {
1472 $handlerKey = $handlerKey ? $handlerKey : $this->lastHandlerKey;
1473 $useNameQuote = isset($this->handlerCfg[$handlerKey]['config']['useNameQuote']) ? $this->handlerCfg[$handlerKey]['config']['useNameQuote'] : FALSE;
1474 if ($useNameQuote) {
1475 return $this->handlerInstance[$handlerKey]->DataDictionary->NameQuote($name);
1476 } else {
1477 $quote = $useBackticks ? '`' : $this->handlerInstance[$handlerKey]->nameQuote;
1478 return $quote . $name . $quote;
1479 }
1480 }
1481
1482 /**
1483 * Return MetaType for native field type (ADOdb only!)
1484 *
1485 * @param string native type as reported by admin_get_fields()
1486 * @param string Table name for which query type string. Important for detection of DBMS handler of the query!
1487 * @return string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1488 */
1489 public function MetaType($type, $table, $max_length = -1) {
1490 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1491 $str = '';
1492 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1493 case 'native':
1494 $str = $type;
1495 break;
1496 case 'adodb':
1497 if (in_array($table, $this->cache_fieldType)) {
1498 $rs = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit('SELECT * FROM ' . $this->quoteFromTables($table), 1);
1499 $str = $rs->MetaType($type, $max_length);
1500 }
1501 break;
1502 case 'userdefined':
1503 $str = $this->handlerInstance[$this->lastHandlerKey]->MetaType($str,$table,$max_length);
1504 break;
1505 default:
1506 die('No handler found!!!');
1507 break;
1508 }
1509
1510 return $str;
1511 }
1512
1513
1514 /**
1515 * Return MetaType for native MySQL field type
1516 *
1517 * @param string native type as reported as in mysqldump files
1518 * @return string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1519 */
1520 public function MySQLMetaType($t) {
1521
1522 switch (strtoupper($t)) {
1523 case 'STRING':
1524 case 'CHAR':
1525 case 'VARCHAR':
1526 case 'TINYBLOB':
1527 case 'TINYTEXT':
1528 case 'ENUM':
1529 case 'SET': return 'C';
1530
1531 case 'TEXT':
1532 case 'LONGTEXT':
1533 case 'MEDIUMTEXT': return 'XL';
1534
1535 case 'IMAGE':
1536 case 'LONGBLOB':
1537 case 'BLOB':
1538 case 'MEDIUMBLOB': return 'B';
1539
1540 case 'YEAR':
1541 case 'DATE': return 'D';
1542
1543 case 'TIME':
1544 case 'DATETIME':
1545 case 'TIMESTAMP': return 'T';
1546
1547 case 'FLOAT':
1548 case 'DOUBLE': return 'F';
1549
1550 case 'INT':
1551 case 'INTEGER':
1552 case 'TINYINT':
1553 case 'SMALLINT':
1554 case 'MEDIUMINT':
1555 case 'BIGINT': return 'I8'; // we always return I8 to be on the safe side. Under some circumstances the fields are to small otherwise...
1556
1557 default: return 'N';
1558 }
1559 }
1560
1561 /**
1562 * Return actual MySQL type for meta field type
1563 *
1564 * @param string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1565 * @return string native type as reported as in mysqldump files, uppercase
1566 */
1567 public function MySQLActualType($meta) {
1568 switch (strtoupper($meta)) {
1569 case 'C': return 'VARCHAR';
1570 case 'XL':
1571 case 'X': return 'LONGTEXT';
1572
1573 case 'C2': return 'VARCHAR';
1574 case 'X2': return 'LONGTEXT';
1575
1576 case 'B': return 'LONGBLOB';
1577
1578 case 'D': return 'DATE';
1579 case 'T': return 'DATETIME';
1580 case 'L': return 'TINYINT';
1581
1582 case 'I':
1583 case 'I1':
1584 case 'I2':
1585 case 'I4':
1586 case 'I8': return 'BIGINT'; // we only have I8 in DBAL, see MySQLMetaType()
1587
1588 case 'F': return 'DOUBLE';
1589 case 'N': return 'NUMERIC';
1590
1591 default: return $meta;
1592 }
1593 }
1594
1595
1596
1597
1598 /**************************************
1599 *
1600 * SQL wrapper functions (Overriding parent methods)
1601 * (For use in your applications)
1602 *
1603 **************************************/
1604
1605 /**
1606 * Returns the error status on the most recent sql() execution (based on $this->lastHandlerKey)
1607 *
1608 * @return string Handler error strings
1609 */
1610 public function sql_error() {
1611 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1612 case 'native':
1613 $output = mysql_error($this->handlerInstance[$this->lastHandlerKey]['link']);
1614 break;
1615 case 'adodb':
1616 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorMsg();
1617 break;
1618 case 'userdefined':
1619 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_error();
1620 break;
1621 }
1622 return $output;
1623 }
1624
1625 /**
1626 * Returns the error number on the most recent sql() execution (based on $this->lastHandlerKey)
1627 *
1628 * @return int Handler error number
1629 */
1630 public function sql_errno() {
1631 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1632 case 'native':
1633 $output = mysql_errno($this->handlerInstance[$this->lastHandlerKey]['link']);
1634 break;
1635 case 'adodb':
1636 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorNo();
1637 break;
1638 case 'userdefined':
1639 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_errno();
1640 break;
1641 }
1642 return $output;
1643 }
1644
1645 /**
1646 * Returns the number of selected rows.
1647 *
1648 * @param pointer Result pointer / DBAL object
1649 * @return integer Number of resulting rows.
1650 */
1651 public function sql_num_rows(&$res) {
1652 if ($res === FALSE) return 0;
1653
1654 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1655 switch ($handlerType) {
1656 case 'native':
1657 $output = mysql_num_rows($res);
1658 break;
1659 case 'adodb':
1660 $output = method_exists($res, 'RecordCount') ? $res->RecordCount() : 0;
1661 break;
1662 case 'userdefined':
1663 $output = $res->sql_num_rows();
1664 break;
1665 }
1666 return $output;
1667 }
1668
1669 /**
1670 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
1671 *
1672 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1673 * @return array Associative array of result row.
1674 */
1675 public function sql_fetch_assoc(&$res) {
1676 $output = array();
1677
1678 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : (is_resource($res) ? 'native' : FALSE);
1679 switch ($handlerType) {
1680 case 'native':
1681 $output = mysql_fetch_assoc($res);
1682 $tableList = $this->resourceIdToTableNameMap[(string)$res]; // Reading list of tables from SELECT query:
1683 break;
1684 case 'adodb':
1685 // Check if method exists for the current $res object.
1686 // If a table exists in TCA but not in the db, a error
1687 // occured because $res is not a valid object.
1688 if (method_exists($res, 'FetchRow')) {
1689 $output = $res->FetchRow();
1690 $tableList = $res->TYPO3_DBAL_tableList; // Reading list of tables from SELECT query:
1691
1692 // Removing all numeric/integer keys.
1693 // A workaround because in ADOdb we would need to know what we want before executing the query...
1694 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
1695 // we don't need to remove anything.
1696 if (is_array($output)) {
1697 if ($this->runningADOdbDriver('mssql')) {
1698 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
1699 foreach ($output as $key => $value) {
1700 if ($value === ' ') {
1701 $output[$key] = '';
1702 }
1703 }
1704 } else {
1705 foreach ($output as $key => $value) {
1706 if (is_integer($key)) {
1707 unset($output[$key]);
1708 }
1709 }
1710 }
1711 }
1712 }
1713 break;
1714 case 'userdefined':
1715 $output = $res->sql_fetch_assoc();
1716 $tableList = $res->TYPO3_DBAL_tableList; // Reading list of tables from SELECT query:
1717 break;
1718 }
1719
1720 // Table/Fieldname mapping:
1721 if (is_array($output)) {
1722 if ($tables = $this->map_needMapping($tableList,TRUE)) {
1723 $output = $this->map_assocArray($output,$tables,1);
1724 }
1725 }
1726
1727 // Return result:
1728 return $output;
1729 }
1730
1731 /**
1732 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
1733 * The array contains the values in numerical indices.
1734 *
1735 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1736 * @return array Array with result rows.
1737 */
1738 public function sql_fetch_row(&$res) {
1739 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1740 switch ($handlerType) {
1741 case 'native':
1742 $output = mysql_fetch_row($res);
1743 break;
1744 case 'adodb':
1745 // Check if method exists for the current $res object.
1746 // If a table exists in TCA but not in the db, a error
1747 // occured because $res is not a valid object.
1748 if (method_exists($res, 'FetchRow')) {
1749 $output = $res->FetchRow();
1750
1751 // Removing all assoc. keys.
1752 // A workaround because in ADOdb we would need to know what we want before executing the query...
1753 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
1754 // we need to convert resultset.
1755 if (is_array($output)) {
1756 $keyIndex = 0;
1757 foreach ($output as $key => $value) {
1758 unset($output[$key]);
1759 if (is_integer($key) || $this->runningADOdbDriver('mssql')) {
1760 $output[$keyIndex] = $value;
1761 if ($value === ' ') {
1762 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
1763 $output[$keyIndex] = '';
1764 }
1765 $keyIndex++;
1766 }
1767 }
1768 }
1769 }
1770 break;
1771 case 'userdefined':
1772 $output = $res->sql_fetch_row();
1773 break;
1774 }
1775 return $output;
1776 }
1777
1778 /**
1779 * Free result memory / unset result object
1780 *
1781 * @param pointer MySQL result pointer to free / DBAL object
1782 * @return boolean Returns TRUE on success or FALSE on failure.
1783 */
1784 public function sql_free_result(&$res) {
1785 if ($res === FALSE) return FALSE;
1786
1787 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1788 switch ($handlerType) {
1789 case 'native':
1790 $output = mysql_free_result($res);
1791 break;
1792 case 'adodb':
1793 if (method_exists($res, 'Close')) {
1794 $res->Close();
1795 unset($res);
1796 $output = TRUE;
1797 } else {
1798 $output = FALSE;
1799 }
1800 break;
1801 case 'userdefined':
1802 unset($res);
1803 break;
1804 }
1805 return $output;
1806 }
1807
1808 /**
1809 * Get the ID generated from the previous INSERT operation
1810 *
1811 * @return integer The uid of the last inserted record.
1812 */
1813 public function sql_insert_id() {
1814 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1815 case 'native':
1816 $output = mysql_insert_id($this->handlerInstance[$this->lastHandlerKey]['link']);
1817 break;
1818 case 'adodb':
1819 $output = $this->handlerInstance[$this->lastHandlerKey]->last_insert_id;
1820 break;
1821 case 'userdefined':
1822 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_insert_id();
1823 break;
1824 }
1825 return $output;
1826 }
1827
1828 /**
1829 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1830 *
1831 * @return integer Number of rows affected by last query
1832 */
1833 public function sql_affected_rows() {
1834 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1835 case 'native':
1836 $output = mysql_affected_rows();
1837 break;
1838 case 'adodb':
1839 $output = $this->handlerInstance[$this->lastHandlerKey]->Affected_Rows();
1840 break;
1841 case 'userdefined':
1842 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_affected_rows();
1843 break;
1844 }
1845 return $output;
1846 }
1847
1848 /**
1849 * Move internal result pointer
1850 *
1851 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1852 * @param integer Seek result number.
1853 * @return boolean Returns TRUE on success or FALSE on failure.
1854 */
1855 public function sql_data_seek(&$res, $seek) {
1856 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1857 switch ($handlerType) {
1858 case 'native':
1859 $output = mysql_data_seek($res,$seek);
1860 break;
1861 case 'adodb':
1862 $output = $res->Move($seek);
1863 break;
1864 case 'userdefined':
1865 $output = $res->sql_data_seek($seek);
1866 break;
1867 }
1868 return $output;
1869 }
1870
1871 /**
1872 * Get the type of the specified field in a result
1873 *
1874 * If the first parameter is a string, it is used as table name for the lookup.
1875 *
1876 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
1877 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
1878 * @return string Returns the type of the specified field index
1879 */
1880 public function sql_field_metatype($table, $field) {
1881 // If $table and/or $field are mapped, use the original names instead
1882 foreach ($this->mapping as $tableName => $tableMapInfo) {
1883 if (isset($tableMapInfo['mapTableName']) && $tableMapInfo['mapTableName'] === $table) {
1884 // Table name is mapped => use original name
1885 $table = $tableName;
1886 }
1887
1888 if (isset($tableMapInfo['mapFieldNames'])) {
1889 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
1890 if ($fieldMapInfo === $field) {
1891 // Field name is mapped => use original name
1892 $field = $fieldName;
1893 }
1894 }
1895 }
1896 }
1897
1898 return $this->cache_fieldType[$table][$field]['metaType'];
1899 }
1900
1901 /**
1902 * Get the type of the specified field in a result
1903 *
1904 * If the first parameter is a string, it is used as table name for the lookup.
1905 *
1906 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
1907 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
1908 * @return string Returns the type of the specified field index
1909 */
1910 public function sql_field_type(&$res,$pointer) {
1911 if ($res === null) {
1912 debug(array('no res in sql_field_type!'));
1913 return 'text';
1914 }
1915 elseif (is_string($res)){
1916 if ($res === 'tx_dbal_debuglog') return 'text';
1917 $handlerType = 'adodb';
1918 }
1919 else {
1920 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1921 }
1922
1923 switch ($handlerType) {
1924 case 'native':
1925 $output = mysql_field_type($res,$pointer);
1926 break;
1927 case 'adodb':
1928 if (is_string($pointer)){
1929 $output = $this->cache_fieldType[$res][$pointer]['type'];
1930 }
1931
1932 break;
1933 case 'userdefined':
1934 $output = $res->sql_field_type($pointer);
1935 break;
1936 }
1937
1938 return $output;
1939 }
1940
1941
1942
1943
1944
1945
1946
1947
1948 /**********
1949 *
1950 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
1951 * Deprecated or still experimental.
1952 *
1953 **********/
1954
1955 /**
1956 * Executes query (on DEFAULT handler!)
1957 * DEPRECATED - use exec_* functions from this class instead!
1958 *
1959 * @param string Database name
1960 * @param string Query to execute
1961 * @return pointer Result pointer
1962 * @deprecated since TYPO3 4.1
1963 */
1964 public function sql($db,$query) {
1965 return $this->sql_query($query);
1966 }
1967
1968 /**
1969 * Executes a query
1970 * EXPERIMENTAL - This method will make its best to handle the query correctly
1971 * but if it cannot, it will simply pass the query to DEFAULT handler.
1972 *
1973 * You should use exec_* function from this class instead!
1974 * If you don't, anything that does not use the _DEFAULT handler will probably break!
1975 *
1976 * This method was deprecated in TYPO3 4.1 but is considered experimental since TYPO3 4.4
1977 * as it tries to handle the query correctly anyway.
1978 *
1979 * @param string Query to execute
1980 * @return pointer Result pointer / DBAL object
1981 */
1982 public function sql_query($query) {
1983 // This method is heavily used by Extbase, try to handle it with DBAL-native methods
1984 $queryParts = $this->SQLparser->parseSQL($query);
1985 if (is_array($queryParts) && t3lib_div::inList('SELECT,UPDATE,INSERT,DELETE', $queryParts['type'])) {
1986 return $this->exec_query($queryParts);
1987 }
1988
1989 switch ($this->handlerCfg['_DEFAULT']['type']) {
1990 case 'native':
1991 $sqlResult = mysql_query($query, $this->handlerInstance['_DEFAULT']['link']);
1992 break;
1993 case 'adodb':
1994 $sqlResult = $this->handlerInstance['_DEFAULT']->Execute($query);
1995 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
1996 break;
1997 case 'userdefined':
1998 $sqlResult = $this->handlerInstance['_DEFAULT']->sql_query($query);
1999 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
2000 break;
2001 }
2002
2003 if ($this->printErrors && $this->sql_error()) {
2004 debug(array($this->lastQuery, $this->sql_error()));
2005 }
2006
2007 return $sqlResult;
2008 }
2009
2010 /**
2011 * Opening the _DEFAULT connection handler to the database.
2012 * This is typically done by the scripts "init.php" in the backend or "index_ts.php" in the frontend (tslib_fe->connectToMySQL())
2013 * You wouldn't need to use this at any time - let TYPO3 core handle this.
2014 *
2015 * @param string Database host IP/domain
2016 * @param string Username to connect with.
2017 * @param string Password to connect with.
2018 * @return mixed Returns handler connection value
2019 * @deprecated since TYPO3 4.1
2020 * @see handler_init()
2021 */
2022 public function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
2023 // Overriding the _DEFAULT handler configuration of username, password, localhost and database name:
2024 $this->handlerCfg['_DEFAULT']['config']['username'] = $TYPO3_db_username;
2025 $this->handlerCfg['_DEFAULT']['config']['password'] = $TYPO3_db_password;
2026 $this->handlerCfg['_DEFAULT']['config']['host'] = $TYPO3_db_host;
2027 $this->handlerCfg['_DEFAULT']['config']['database'] = TYPO3_db;
2028
2029 // Initializing and output value:
2030 $sqlResult = $this->handler_init('_DEFAULT');
2031 return $sqlResult;
2032 }
2033
2034 /**
2035 * Select database for _DEFAULT handler.
2036 *
2037 * @param string Database to connect to.
2038 * @return boolean Always returns TRUE; function is obsolete, database selection is made in handler_init() function!
2039 * @deprecated since TYPO3 4.1
2040 */
2041 public function sql_select_db($TYPO3_db) {
2042 return TRUE;
2043 }
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059 /**************************************
2060 *
2061 * SQL admin functions
2062 * (For use in the Install Tool and Extension Manager)
2063 *
2064 **************************************/
2065
2066 /**
2067 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
2068 * Use in Install Tool only!
2069 * Usage count/core: 1
2070 *
2071 * @return array Each entry represents a database name
2072 */
2073 public function admin_get_dbs() {
2074 $dbArr = array();
2075 switch ($this->handlerCfg['_DEFAULT']['type']) {
2076 case 'native':
2077 $db_list = mysql_list_dbs($this->link);
2078 while ($row = mysql_fetch_object($db_list)) {
2079 if ($this->sql_select_db($row->Database)) {
2080 $dbArr[] = $row->Database;
2081 }
2082 }
2083 break;
2084 case 'adodb':
2085 // check needed for install tool - otherwise it will just die because the call to
2086 // MetaDatabases is done on a stdClass instance
2087 if (method_exists($this->handlerInstance['_DEFAULT'],'MetaDatabases')) {
2088 $sqlDBs = $this->handlerInstance['_DEFAULT']->MetaDatabases();
2089 if (is_array($sqlDBs)) {
2090 foreach ($sqlDBs as $k => $theDB) {
2091 $dbArr[] = $theDB;
2092 }
2093 }
2094 }
2095 break;
2096 case 'userdefined':
2097 $dbArr = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2098 break;
2099 }
2100
2101 return $dbArr;
2102 }
2103
2104 /**
2105 * Returns the list of tables from the system (quering the DBMSs)
2106 * It looks up all tables from the DBMS of the _DEFAULT handler and then add all tables *configured* to be managed by other handlers
2107 *
2108 * When fetching the tables, it skips tables whose names begin with BIN$, as this is taken as a table coming from the "Recycle Bin" on Oracle.
2109 *
2110 * @return array Tables in an array (tablename is in both key and value)
2111 * @todo Should the check for Oracle Recycle Bin stuff be moved elsewhere?
2112 * @todo Should return table details in value! see t3lib_db::admin_get_tables()
2113 */
2114 public function admin_get_tables() {
2115 $whichTables = array();
2116
2117 // Getting real list of tables:
2118 switch ($this->handlerCfg['_DEFAULT']['type']) {
2119 case 'native':
2120 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->handlerInstance['_DEFAULT']['link']);
2121 if (!$this->sql_error()) {
2122 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
2123 $whichTables[current($theTable)] = current($theTable);
2124 }
2125 }
2126 break;
2127 case 'adodb':
2128 // check needed for install tool - otherwise it will just die because the call to
2129 // MetaTables is done on a stdClass instance
2130 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaTables')) {
2131 $sqlTables = $this->handlerInstance['_DEFAULT']->MetaTables('TABLES');
2132 while (list($k, $theTable) = each($sqlTables)) {
2133 if (preg_match('/BIN\$/', $theTable)) continue; // skip tables from the Oracle 10 Recycle Bin
2134 $whichTables[$theTable] = $theTable;
2135 }
2136 }
2137 break;
2138 case 'userdefined':
2139 $whichTables = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2140 break;
2141 }
2142
2143 // Check mapping:
2144 if (is_array($this->mapping) && count($this->mapping)) {
2145
2146 // Mapping table names in reverse, first getting list of real table names:
2147 $tMap = array();
2148 foreach ($this->mapping as $tN => $tMapInfo) {
2149 if (isset($tMapInfo['mapTableName'])) $tMap[$tMapInfo['mapTableName']]=$tN;
2150 }
2151
2152 // Do mapping:
2153 $newList=array();
2154 foreach ($whichTables as $tN) {
2155 if (isset($tMap[$tN])) $tN = $tMap[$tN];
2156 $newList[$tN] = $tN;
2157 }
2158
2159 $whichTables = $newList;
2160 }
2161
2162 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
2163 if (is_array($this->table2handlerKeys)) {
2164 foreach ($this->table2handlerKeys as $key => $handlerKey) {
2165 $whichTables[$key] = $key;
2166 }
2167 }
2168
2169 return $whichTables;
2170 }
2171
2172 /**
2173 * Returns information about each field in the $table (quering the DBMS)
2174 * In a DBAL this should look up the right handler for the table and return compatible information
2175 * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery
2176 *
2177 * @param string Table name
2178 * @return array Field information in an associative array with fieldname => field row
2179 */
2180 public function admin_get_fields($tableName) {
2181 $output = array();
2182
2183 // Do field mapping if needed:
2184 $ORIG_tableName = $tableName;
2185 if ($tableArray = $this->map_needMapping($tableName)) {
2186
2187 // Table name:
2188 if ($this->mapping[$tableName]['mapTableName']) {
2189 $tableName = $this->mapping[$tableName]['mapTableName'];
2190 }
2191 }
2192
2193 // Find columns
2194 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
2195 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2196 case 'native':
2197 $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
2198 while($fieldRow = mysql_fetch_assoc($columns_res)) {
2199 $output[$fieldRow['Field']] = $fieldRow;
2200 }
2201 break;
2202 case 'adodb':
2203 $fieldRows = $this->handlerInstance[$this->lastHandlerKey]->MetaColumns($tableName, FALSE);
2204 if (is_array($fieldRows)) {
2205 foreach ($fieldRows as $k => $fieldRow) {
2206 settype($fieldRow, 'array');
2207 $fieldRow['Field'] = $fieldRow['name'];
2208 $ntype = $this->MySQLActualType($this->MetaType($fieldRow['type'],$tableName));
2209 $ntype .= (($fieldRow['max_length'] != -1) ? (($ntype == 'INT') ? '(11)' :'('.$fieldRow['max_length'].')') : '');
2210 $fieldRow['Type'] = strtolower($ntype);
2211 $fieldRow['Null'] = '';
2212 $fieldRow['Key'] = '';
2213 $fieldRow['Default'] = $fieldRow['default_value'];
2214 $fieldRow['Extra'] = '';
2215 $output[$fieldRow['name']] = $fieldRow;
2216 }
2217 }
2218 break;
2219 case 'userdefined':
2220 $output = $this->handlerInstance[$this->lastHandlerKey]->admin_get_fields($tableName);
2221 break;
2222 }
2223
2224 // mapping should be done:
2225 if (is_array($tableArray) && is_array($this->mapping[$ORIG_tableName]['mapFieldNames'])) {
2226 $revFields = array_flip($this->mapping[$ORIG_tableName]['mapFieldNames']);
2227
2228 $newOutput = array();
2229 foreach ($output as $fN => $fInfo) {
2230 if (isset($revFields[$fN])) {
2231 $fN = $revFields[$fN];
2232 $fInfo['Field'] = $fN;
2233 }
2234 $newOutput[$fN] = $fInfo;
2235 }
2236 $output = $newOutput;
2237 }
2238
2239 return $output;
2240 }
2241
2242 /**
2243 * Returns information about each index key in the $table (quering the DBMS)
2244 * In a DBAL this should look up the right handler for the table and return compatible information
2245 *
2246 * @param string Table name
2247 * @return array Key information in a numeric array
2248 */
2249 public function admin_get_keys($tableName) {
2250 $output = array();
2251
2252 // Do field mapping if needed:
2253 $ORIG_tableName = $tableName;
2254 if ($tableArray = $this->map_needMapping($tableName)) {
2255
2256 // Table name:
2257 if ($this->mapping[$tableName]['mapTableName']) {
2258 $tableName = $this->mapping[$tableName]['mapTableName'];
2259 }
2260 }
2261
2262 // Find columns
2263 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
2264 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2265 case 'native':
2266 $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
2267 while($keyRow = mysql_fetch_assoc($keyRes)) {
2268 $output[] = $keyRow;
2269 }
2270 break;
2271 case 'adodb':
2272 $keyRows = $this->handlerInstance[$this->lastHandlerKey]->MetaIndexes($tableName);
2273 if ($keyRows !== FALSE) {
2274 while (list($k, $theKey) = each($keyRows)) {
2275 $theKey['Table'] = $tableName;
2276 $theKey['Non_unique'] = (int) !$theKey['unique'];
2277 $theKey['Key_name'] = str_replace($tableName.'_','',$k);
2278
2279 // the following are probably not needed anyway...
2280 $theKey['Collation'] = '';
2281 $theKey['Cardinality'] = '';
2282 $theKey['Sub_part'] = '';
2283 $theKey['Packed'] = '';
2284 $theKey['Null'] = '';
2285 $theKey['Index_type'] = '';
2286 $theKey['Comment'] = '';
2287
2288 // now map multiple fields into multiple rows (we mimic MySQL, remember...)
2289 $keycols = $theKey['columns'];
2290 while (list($c, $theCol) = each($keycols)) {
2291 $theKey['Seq_in_index'] = $c+1;
2292 $theKey['Column_name'] = $theCol;
2293 $output[] = $theKey;
2294 }
2295 }
2296 }
2297 $priKeyRow = $this->handlerInstance[$this->lastHandlerKey]->MetaPrimaryKeys($tableName);
2298 $theKey = array();
2299 $theKey['Table'] = $tableName;
2300 $theKey['Non_unique'] = 0;
2301 $theKey['Key_name'] = 'PRIMARY';
2302
2303 // the following are probably not needed anyway...
2304 $theKey['Collation'] = '';
2305 $theKey['Cardinality'] = '';
2306 $theKey['Sub_part'] = '';
2307 $theKey['Packed'] = '';
2308 $theKey['Null'] = '';
2309 $theKey['Index_type'] = '';
2310 $theKey['Comment'] = '';
2311
2312 // now map multiple fields into multiple rows (we mimic MySQL, remember...)
2313 if ($priKeyRow !== FALSE) {
2314 while (list($c, $theCol) = each($priKeyRow)) {
2315 $theKey['Seq_in_index'] = $c+1;
2316 $theKey['Column_name'] = $theCol;
2317 $output[] = $theKey;
2318 }
2319 }
2320 break;
2321 case 'userdefined':
2322 $output = $this->handlerInstance[$this->lastHandlerKey]->admin_get_keys($tableName);
2323 break;
2324 }
2325
2326 // mapping should be done:
2327 if (is_array($tableArray) && is_array($this->mapping[$ORIG_tableName]['mapFieldNames'])) {
2328 $revFields = array_flip($this->mapping[$ORIG_tableName]['mapFieldNames']);
2329
2330 $newOutput = array();
2331 foreach ($output as $kN => $kInfo) {
2332 // Table:
2333 $kInfo['Table'] = $ORIG_tableName;
2334
2335 // Column
2336 if (isset($revFields[$kInfo['Column_name']])) {
2337 $kInfo['Column_name'] = $revFields[$kInfo['Column_name']];
2338 }
2339
2340 // Write it back:
2341 $newOutput[$kN] = $kInfo;
2342 }
2343 $output = $newOutput;
2344 }
2345
2346 return $output;
2347 }
2348
2349 /**
2350 * mysql() wrapper function, used by the Install Tool.
2351 *
2352 * @return array
2353 */
2354 public function admin_get_charsets() {
2355 return array();
2356 }
2357
2358 /**
2359 * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
2360 *
2361 * @param string Query to execute
2362 * @return pointer Result pointer
2363 */
2364 public function admin_query($query) {
2365 $parsedQuery = $this->SQLparser->parseSQL($query);
2366 $ORIG_table = $parsedQuery['TABLE'];
2367
2368 if (is_array($parsedQuery)) {
2369
2370 // Process query based on type:
2371 switch ($parsedQuery['type']) {
2372 case 'CREATETABLE':
2373 case 'ALTERTABLE':
2374 case 'DROPTABLE':
2375 if (file_exists(PATH_typo3conf.'temp_fieldInfo.php')) unlink(PATH_typo3conf.'temp_fieldInfo.php');
2376 $this->map_genericQueryParsed($parsedQuery);
2377 break;
2378 case 'INSERT':
2379 case 'TRUNCATETABLE':
2380 $this->map_genericQueryParsed($parsedQuery);
2381 break;
2382 case 'CREATEDATABASE':
2383 die('Creating a database with DBAL is not supported. Did you really read the manual?');
2384 break;
2385 default:
2386 die('ERROR: Invalid Query type ('.$parsedQuery['type'].') for ->admin_query() function!: "'.htmlspecialchars($query).'"');
2387 break;
2388 }
2389
2390 // Setting query array (for other applications to access if needed)
2391 $this->lastParsedAndMappedQueryArray = $parsedQuery;
2392
2393 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
2394 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_table);
2395 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2396 case 'native':
2397 // Compiling query:
2398 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2399
2400 if ($this->lastParsedAndMappedQueryArray['type']=='INSERT') {
2401 return mysql_query($compiledQuery, $this->link);
2402 }
2403 return mysql_query($compiledQuery[0], $this->link);
2404 break;
2405 case 'adodb':
2406 // Compiling query:
2407 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2408 switch ($this->lastParsedAndMappedQueryArray['type']) {
2409 case 'INSERT':
2410 return $this->exec_INSERTquery($this->lastParsedAndMappedQueryArray['TABLE'], $compiledQuery);
2411 case 'TRUNCATETABLE':
2412 return $this->exec_TRUNCATEquery($this->lastParsedAndMappedQueryArray['TABLE']);
2413 }
2414 return $this->handlerInstance[$this->lastHandlerKey]->DataDictionary->ExecuteSQLArray($compiledQuery);
2415 break;
2416 case 'userdefined':
2417 // Compiling query:
2418 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2419
2420 return $this->handlerInstance[$this->lastHandlerKey]->admin_query($compiledQuery);
2421 break;
2422 }
2423 } else die('ERROR: Query could not be parsed: "'.htmlspecialchars($parsedQuery).'". Query: "'.htmlspecialchars($query).'"');
2424 }
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435 /************************************
2436 *
2437 * Handler management
2438 *
2439 **************************************/
2440
2441 /**
2442 * Return the handler key pointing to an appropriate database handler as found in $this->handlerCfg array
2443 * Notice: TWO or more tables in the table list MUST use the SAME handler key - otherwise a fatal error is thrown! (Logically, no database can possibly join two tables from separate sources!)
2444 *
2445 * @param string Table list, eg. "pages" or "pages, tt_content" or "pages AS A, tt_content AS B"
2446 * @return string Handler key (see $this->handlerCfg array) for table
2447 */
2448 public function handler_getFromTableList($tableList) {
2449
2450 $key = $tableList;
2451
2452 if (!isset($this->cache_handlerKeyFromTableList[$key])) {
2453
2454 // Get tables separated:
2455 $_tableList = $tableList;
2456 $tableArray = $this->SQLparser->parseFromTables($_tableList);
2457
2458 // If success, traverse the tables:
2459 if (is_array($tableArray) && count($tableArray)) {
2460 $outputHandlerKey = '';
2461
2462 foreach ($tableArray as $vArray) {
2463 // Find handler key, select "_DEFAULT" if none is specifically configured:
2464 $handlerKey = $this->table2handlerKeys[$vArray['table']] ? $this->table2handlerKeys[$vArray['table']] : '_DEFAULT';
2465
2466 // In case of separate handler keys for joined tables:
2467 if ($outputHandlerKey && $handlerKey != $outputHandlerKey) {
2468 die('DBAL fatal error: Tables in this list "'.$tableList.'" didn\'t use the same DB handler!');
2469 }
2470
2471 $outputHandlerKey = $handlerKey;
2472 }
2473
2474 // Check initialized state; if handler is NOT initialized (connected) then we will connect it!
2475 if (!isset($this->handlerInstance[$outputHandlerKey])) {
2476 $this->handler_init($outputHandlerKey);
2477 }
2478
2479 // Return handler key:
2480 $this->cache_handlerKeyFromTableList[$key] = $outputHandlerKey;
2481 } else {
2482 die('DBAL fatal error: No handler found in handler_getFromTableList() for: "'.$tableList.'" ('.$tableArray.')');
2483 }
2484 }
2485
2486 return $this->cache_handlerKeyFromTableList[$key];
2487 }
2488
2489 /**
2490 * Initialize handler (connecting to database)
2491 *
2492 * @param string Handler key
2493 * @return boolean If connection went well, return TRUE
2494 * @see handler_getFromTableList()
2495 */
2496 public function handler_init($handlerKey) {
2497
2498 // Find handler configuration:
2499 $cfgArray = $this->handlerCfg[$handlerKey];
2500 $handlerType = (string)$cfgArray['type'];
2501 $output = FALSE;
2502
2503 if (is_array($cfgArray)) {
2504 if (!$cfgArray['config']['database']) {
2505 // Configuration is incomplete
2506 return;
2507 }
2508 switch ($handlerType) {
2509 case 'native':
2510 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
2511 $link = mysql_connect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''), $cfgArray['config']['username'], $cfgArray['config']['password'], TRUE);
2512 } else {
2513 $link = mysql_pconnect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''), $cfgArray['config']['username'], $cfgArray['config']['password']);
2514 }
2515
2516 // Set handler instance:
2517 $this->handlerInstance[$handlerKey] = array('handlerType' => 'native', 'link' => $link);
2518
2519 // If link succeeded:
2520 if ($link) {
2521 // For default, set ->link (see t3lib_DB)
2522 if ($handlerKey == '_DEFAULT') {
2523 $this->link = $link;
2524 }
2525
2526 // Select database as well:
2527 if (mysql_select_db($cfgArray['config']['database'], $link)) {
2528 $output = TRUE;
2529 }
2530 $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'], 1);
2531 foreach ($setDBinit as $v) {
2532 if (mysql_query($v, $link) === FALSE) {
2533 t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'".','Core',3);
2534 }
2535 }
2536 } else {
2537 t3lib_div::sysLog('Could not connect to MySQL server '.$cfgArray['config']['host'].' with user '.$cfgArray['config']['username'].'.','Core',4);
2538 }
2539 break;
2540 case 'adodb':
2541 $output = TRUE;
2542 require_once(t3lib_extMgm::extPath('adodb').'adodb/adodb.inc.php');
2543 if (!defined('ADODB_FORCE_NULLS')) define('ADODB_FORCE_NULLS', 1);
2544 $GLOBALS['ADODB_FORCE_TYPE'] = ADODB_FORCE_VALUE;
2545 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_BOTH;
2546
2547 $this->handlerInstance[$handlerKey] = &ADONewConnection($cfgArray['config']['driver']);
2548
2549 // Set driver-specific options
2550 if (isset($cfgArray['config']['driverOptions'])) {
2551 foreach ($cfgArray['config']['driverOptions'] as $optionName => $optionValue) {
2552 $optionSetterName = 'set' . ucfirst($optionName);
2553 if (method_exists($this->handlerInstance[$handlerKey], $optionSetterName)) {
2554 $this->handlerInstance[$handlerKey]->$optionSetterName($optionValue);
2555 } else {
2556 $this->handlerInstance[$handlerKey]->$optionName = $optionValue;
2557 }
2558 }
2559 }
2560
2561 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
2562 $this->handlerInstance[$handlerKey]->Connect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''),$cfgArray['config']['username'],$cfgArray['config']['password'],$cfgArray['config']['database']);
2563 } else {
2564 $this->handlerInstance[$handlerKey]->PConnect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''),$cfgArray['config']['username'],$cfgArray['config']['password'],$cfgArray['config']['database']);
2565 }
2566 if (!$this->handlerInstance[$handlerKey]->isConnected()) {
2567 $dsn = $cfgArray['config']['driver'].'://'.$cfgArray['config']['username'].
2568 (strlen($cfgArray['config']['password']) ? ':XXXX@' : '').
2569 $cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : '').'/'.$cfgArray['config']['database'].
2570 ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'] ? '' : '?persistent=1');
2571 t3lib_div::sysLog('Could not connect to DB server using ADOdb on '.$cfgArray['config']['host'].' with user '.$cfgArray['config']['username'].'.','Core',4);
2572 error_log('DBAL error: Connection to '.$dsn.' failed. Maybe PHP doesn\'t support the database?');
2573 $output = FALSE;
2574 } else {
2575 $this->handlerInstance[$handlerKey]->DataDictionary = NewDataDictionary($this->handlerInstance[$handlerKey]);
2576 $this->handlerInstance[$handlerKey]->last_insert_id = 0;
2577 if (isset($cfgArray['config']['sequenceStart'])) {
2578 $this->handlerInstance[$handlerKey]->sequenceStart = $cfgArray['config']['sequenceStart'];
2579 } else {
2580 $this->handlerInstance[$handlerKey]->sequenceStart = 1;
2581 }
2582 }
2583 break;
2584 case 'userdefined':
2585 // Find class file:
2586 $fileName = t3lib_div::getFileAbsFileName($cfgArray['config']['classFile']);
2587 if (@is_file($fileName)) {
2588 require_once($fileName);
2589 } else die('DBAL error: "'.$fileName.'" was not a file to include.');
2590
2591 // Initialize:
2592 $this->handlerInstance[$handlerKey] = t3lib_div::makeInstance($cfgArray['config']['class']);
2593 $this->handlerInstance[$handlerKey]->init($cfgArray,$this);
2594
2595 if (is_object($this->handlerInstance[$handlerKey])) {
2596 $output = TRUE;
2597 }
2598 break;
2599 default:
2600 die('ERROR: Invalid handler type: "'.$cfgArray['type'].'"');
2601 break;
2602 }
2603
2604 return $output;
2605 } else die('ERROR: No handler for key "'.$handlerKey.'"');
2606 }
2607
2608
2609 /**
2610 * Checks if database is connected.
2611 *
2612 * @return boolean
2613 */
2614 public function isConnected() {
2615 $result = FALSE;
2616 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2617 case 'native':
2618 $result = is_resource($this->link);
2619 break;
2620 case 'adodb':
2621 case 'userdefined':
2622 $result = is_object($this->handlerInstance[$this->lastHandlerKey]) && $this->handlerInstance[$this->lastHandlerKey]->isConnected();
2623 break;
2624 }
2625 return $result;
2626 }
2627
2628
2629 /**
2630 * Checks whether the DBAL is currently inside an operation running on the "native" DB handler (i.e. MySQL)
2631 *
2632 * @return boolean True if running on "native" DB handler (i.e. MySQL)
2633 */
2634 public function runningNative() {
2635 return ((string)$this->handlerCfg[$this->lastHandlerKey]['type']==='native');
2636 }
2637
2638
2639 /**
2640 * Checks whether the ADOdb handler is running with a driver that contains the argument
2641 *
2642 * @param string $driver Driver name, matched with strstr().
2643 * @return boolean True if running with the given driver
2644 */
2645 public function runningADOdbDriver($driver) {
2646 return strstr($this->handlerCfg[$this->lastHandlerKey]['config']['driver'], $driver);
2647 }
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658 /************************************
2659 *
2660 * Table/Field mapping
2661 *
2662 **************************************/
2663
2664 /**
2665 * Checks if mapping is needed for a table(list)
2666 *
2667 * @param string List of tables in query
2668 * @param boolean If TRUE, it will check only if FIELDs are configured and ignore the mapped table name if any.
2669 * @return mixed Returns an array of table names (parsed version of input table) if mapping is needed, otherwise just FALSE.
2670 */
2671 protected function map_needMapping($tableList, $fieldMappingOnly = FALSE) {
2672 $key = $tableList.'|'.$fieldMappingOnly;
2673 if (!isset($this->cache_mappingFromTableList[$key])) {
2674 $this->cache_mappingFromTableList[$key] = FALSE; // Default:
2675
2676 $tables = $this->SQLparser->parseFromTables($tableList);
2677 if (is_array($tables)) {
2678 foreach ($tables as $tableCfg) {
2679 if ($fieldMappingOnly) {
2680 if (is_array($this->mapping[$tableCfg['table']]['mapFieldNames'])) {
2681 $this->cache_mappingFromTableList[$key] = $tables;
2682 } elseif (is_array($tableCfg['JOIN'])) {
2683 foreach ($tableCfg['JOIN'] as $join) {
2684 if (is_array($this->mapping[$join['withTable']]['mapFieldNames'])) {
2685 $this->cache_mappingFromTableList[$key] = $tables;
2686 break;
2687 }
2688 }
2689 }
2690 } else {
2691 if (is_array($this->mapping[$tableCfg['table']])) {
2692 $this->cache_mappingFromTableList[$key] = $tables;
2693 } elseif (is_array($tableCfg['JOIN'])) {
2694 foreach ($tableCfg['JOIN'] as $join) {
2695 if (is_array($this->mapping[$join['withTable']])) {
2696 $this->cache_mappingFromTableList[$key] = $tables;
2697 break;
2698 }
2699 }
2700 }
2701 }
2702 }
2703 }
2704 }
2705
2706 return $this->cache_mappingFromTableList[$key];
2707 }
2708
2709 /**
2710 * Takes an associated array with field => value pairs and remaps the field names if configured for this table in $this->mapping array.
2711 * Be careful not to map a field name to another existing fields name (although you can use this to swap fieldnames of course...:-)
2712 * Observe mapping problems with join-results (more than one table): Joined queries should always prefix the table name to avoid problems with this.
2713 * Observe that alias fields are not mapped of course (should not be a problem though)
2714 *
2715 * @param array Input array, associative keys
2716 * @param array Array of tables from the query. Normally just one table; many tables in case of a join. NOTICE: for multiple tables (with joins) there MIGHT occur trouble with fields of the same name in the two tables: This function traverses the mapping information for BOTH tables and applies mapping without checking from which table the field really came!
2717 * @param boolean If TRUE, reverse direction. Default direction is to map an array going INTO the database (thus mapping TYPO3 fieldnames to PHYSICAL field names!)
2718 * @return array Output array, with mapped associative keys.
2719 */
2720 protected function map_assocArray($input, $tables, $rev = FALSE) {
2721 // Traverse tables from query (hopefully only one table):
2722 foreach ($tables as $tableCfg) {
2723 $tableKey = $this->getMappingKey($tableCfg['table']);
2724 if (is_array($this->mapping[$tableKey]['mapFieldNames'])) {
2725
2726 // Get the map (reversed if needed):
2727 if ($rev) {
2728 $theMap = array_flip($this->mapping[$tableKey]['mapFieldNames']);
2729 } else {
2730 $theMap = $this->mapping[$tableKey]['mapFieldNames'];
2731 }
2732
2733 // Traverse selected record, map fieldnames:
2734 $output = array();
2735 foreach ($input as $fN => $value) {
2736
2737 // Set the field name, change it if found in mapping array:
2738 if ($theMap[$fN]) {
2739 $newKey = $theMap[$fN];
2740 } else {
2741 $newKey = $fN;
2742 }
2743
2744 // Set value to fieldname:
2745 $output[$newKey] = $value;
2746 }
2747
2748 // When done, override the $input array with the result:
2749 $input = $output;
2750 }
2751 }
2752
2753 // Return input array (which might have been altered in the mean time)
2754 return $input;
2755 }
2756
2757 /**
2758 * Remaps table/field names in a SELECT query's parts
2759 * Notice: All arguments are passed by reference!
2760 *
2761 * @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
2762 * @param string Table(s) from which to select. This is what comes right after "FROM ...". Require value.
2763 * @param string Where clause. This is what comes right after "WHERE ...". Can be blank.
2764 * @param string Group by field(s)
2765 * @param string Order by field(s)
2766 * @return void
2767 * @see exec_SELECTquery()
2768 */
2769 protected function map_remapSELECTQueryParts(&$select_fields, &$from_table, &$where_clause, &$groupBy, &$orderBy) {
2770 // Backup current mapping as it may be altered if aliases on mapped tables are found
2771 $backupMapping = $this->mapping;
2772
2773 // Tables:
2774 $tables = $this->SQLparser->parseFromTables($from_table);
2775 $defaultTable = $tables[0]['table'];
2776 // Prepare mapping for aliased tables. This will copy the definition of the original table name.
2777 // The alias is prefixed with a database-incompatible character to prevent naming clash with real table name
2778 // Further access to $this->mapping should be made through $this->getMappingKey() method
2779 foreach ($tables as $k => $v) {
2780 if ($v['as'] && is_array($this->mapping[$v['table']]['mapFieldNames'])) {
2781 $mappingKey = $this->getFreeMappingKey($v['as']);
2782 $this->mapping[$mappingKey]['mapFieldNames'] =& $this->mapping[$v['table']]['mapFieldNames'];
2783 }
2784 if (is_array($v['JOIN'])) {
2785 foreach ($v['JOIN'] as $joinCnt => $join) {
2786 if ($join['as'] && is_array($this->mapping[$join['withTable']]['mapFieldNames'])) {
2787 $mappingKey = $this->getFreeMappingKey($join['as']);
2788 $this->mapping[$mappingKey]['mapFieldNames'] =& $this->mapping[$join['withTable']]['mapFieldNames'];
2789 }
2790 }
2791 }
2792 }
2793 foreach ($tables as $k => $v) {
2794 $tableKey = $this->getMappingKey($v['table']);
2795 if ($this->mapping[$tableKey]['mapTableName']) {
2796 $tables[$k]['table'] = $this->mapping[$tableKey]['mapTableName'];
2797 }
2798 // Mapping JOINS
2799 if (is_array($v['JOIN'])) {
2800 foreach($v['JOIN'] as $joinCnt => $join) {
2801 // Mapping withTable of the JOIN
2802 $withTableKey = $this->getMappingKey($join['withTable']);
2803 if ($this->mapping[$withTableKey]['mapTableName']) {
2804 $tables[$k]['JOIN'][$joinCnt]['withTable'] = $this->mapping[$withTableKey]['mapTableName'];
2805 }
2806 $onPartsArray = array();
2807 // Mapping ON parts of the JOIN
2808 if (is_array($tables[$k]['JOIN'][$joinCnt]['ON'])) {
2809 foreach ($tables[$k]['JOIN'][$joinCnt]['ON'] as &$condition) {
2810 // Left side of the comparator
2811 $leftTableKey = $this->getMappingKey($condition['left']['table']);
2812 if (isset($this->mapping[$leftTableKey]['mapFieldNames'][$condition['left']['field']])) {
2813 $condition['left']['field'] = $this->mapping[$leftTableKey]['mapFieldNames'][$condition['left']['field']];
2814 }
2815 if (isset($this->mapping[$leftTableKey]['mapTableName'])) {
2816 $condition['left']['table'] = $this->mapping[$leftTableKey]['mapTableName'];
2817 }
2818 // Right side of the comparator
2819 $rightTableKey = $this->getMappingKey($condition['right']['table']);
2820 if (isset($this->mapping[$rightTableKey]['mapFieldNames'][$condition['right']['field']])) {
2821 $condition['right']['field'] = $this->mapping[$rightTableKey]['mapFieldNames'][$condition['right']['field']];
2822 }
2823 if (isset($this->mapping[$rightTableKey]['mapTableName'])) {
2824 $condition['right']['table'] = $this->mapping[$rightTableKey]['mapTableName'];
2825 }
2826 }
2827 }
2828 }
2829 }
2830 }
2831 $from_table = $this->SQLparser->compileFromTables($tables);
2832
2833 // Where clause:
2834 $whereParts = $this->SQLparser->parseWhereClause($where_clause);
2835 $this->map_sqlParts($whereParts,$defaultTable);
2836 $where_clause = $this->SQLparser->compileWhereClause($whereParts, FALSE);
2837
2838 // Select fields:
2839 $expFields = $this->SQLparser->parseFieldList($select_fields);
2840 $this->map_sqlParts($expFields,$defaultTable);
2841 $select_fields = $this->SQLparser->compileFieldList($expFields, FALSE, FALSE);
2842
2843 // Group By fields
2844 $expFields = $this->SQLparser->parseFieldList($groupBy);
2845 $this->map_sqlParts($expFields,$defaultTable);
2846 $groupBy = $this->SQLparser->compileFieldList($expFields);
2847
2848 // Order By fields
2849 $expFields = $this->SQLparser->parseFieldList($orderBy);
2850 $this->map_sqlParts($expFields,$defaultTable);
2851 $orderBy = $this->SQLparser->compileFieldList($expFields);
2852
2853 // Restore the original mapping
2854 $this->mapping = $backupMapping;
2855 }
2856
2857 /**
2858 * Returns the key to be used when retrieving information from $this->mapping. This ensures
2859 * that mapping from aliased tables is properly retrieved.
2860 *
2861 * @param string $tableName
2862 * @return string
2863 */
2864 protected function getMappingKey($tableName) {
2865 // Search deepest alias mapping
2866 while (isset($this->mapping['*' . $tableName])) {
2867 $tableName = '*' . $tableName;
2868 }
2869 return $tableName;
2870 }
2871
2872 /**
2873 * Returns a free key to be used to store mapping information in $this->mapping.
2874 *
2875 * @param string $tableName
2876 * @return string
2877 */
2878 protected function getFreeMappingKey($tableName) {
2879 while (isset($this->mapping[$tableName])) {
2880 $tableName = '*' . $tableName;
2881 }
2882 return $tableName;
2883 }
2884
2885 /**
2886 * Generic mapping of table/field names arrays (as parsed by tx_dbal_sqlengine)
2887 *
2888 * @param array Array with parsed SQL parts; Takes both fields, tables, where-parts, group and order-by. Passed by reference.
2889 * @param string Default table name to assume if no table is found in $sqlPartArray
2890 * @return void
2891 * @access private
2892 * @see map_remapSELECTQueryParts()
2893 */
2894 protected function map_sqlParts(&$sqlPartArray, $defaultTable) {
2895 $defaultTableKey = $this->getMappingKey($defaultTable);
2896 // Traverse sql Part array:
2897 if (is_array($sqlPartArray)) {
2898 foreach ($sqlPartArray as $k => $v) {
2899
2900 if (isset($sqlPartArray[$k]['type'])) {
2901 switch ($sqlPartArray[$k]['type']) {
2902 case 'flow-control':
2903 $temp = array($sqlPartArray[$k]['flow-control']);
2904 $this->map_sqlParts($temp, $defaultTable); // Call recursively!
2905 $sqlPartArray[$k]['flow-control'] = $temp[0];
2906 break;
2907 case 'CASE':
2908 if (isset($sqlPartArray[$k]['case_field'])) {
2909 $fieldArray = explode('.', $sqlPartArray[$k]['case_field']);