[TASK] Encapsulate bootstrap base code
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_db.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2011 Kasper Skårhøj (kasperYYYY@typo3.com)
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 * A copy is found in the textfile GPL.txt and important notices to the license
17 * from the author is found in LICENSE.txt distributed with these scripts.
18 *
19 *
20 * This script is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
27
28 /**
29 * Contains the class "t3lib_db" containing functions for building SQL queries
30 * and mysql wrappers, thus providing a foundational API to all database
31 * interaction.
32 * This class is instantiated globally as $TYPO3_DB in TYPO3 scripts.
33 *
34 * TYPO3 "database wrapper" class (new in 3.6.0)
35 * This class contains
36 * - abstraction functions for executing INSERT/UPDATE/DELETE/SELECT queries ("Query execution"; These are REQUIRED for all future connectivity to the database, thus ensuring DBAL compliance!)
37 * - functions for building SQL queries (INSERT/UPDATE/DELETE/SELECT) ("Query building"); These are transitional functions for building SQL queries in a more automated way. Use these to build queries instead of doing it manually in your code!
38 * - mysql() wrapper functions; These are transitional functions. By a simple search/replace you should be able to substitute all mysql*() calls with $GLOBALS['TYPO3_DB']->sql*() and your application will work out of the box. YOU CANNOT (legally) use any mysql functions not found as wrapper functions in this class!
39 * See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise
40 *
41 * This class is not in itself a complete database abstraction layer but can be extended to be a DBAL (by extensions, see "dbal" for example)
42 * ALL connectivity to the database in TYPO3 must be done through this class!
43 * The points of this class are:
44 * - To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
45 * - To keep it very easy to use for developers used to MySQL in PHP - and preserve as much performance as possible when TYPO3 is used with MySQL directly...
46 * - To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
47 * - Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.
48 *
49 * USE:
50 * In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
51 * Eg. $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
52 *
53 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
54 * @package TYPO3
55 * @subpackage t3lib
56 */
57 class t3lib_DB {
58
59 // Set "TRUE" or "1" if you want database errors outputted. Set to "2" if you also want successful database actions outputted.
60 var $debugOutput = FALSE;
61 // Internally: Set to last built query (not necessarily executed...)
62 var $debug_lastBuiltQuery = '';
63 // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
64 var $store_lastBuiltQuery = FALSE;
65 // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask.
66 // There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
67 var $explainOutput = 0;
68
69 // Default link identifier:
70 var $link = FALSE;
71
72 // Default character set, applies unless character set or collation are explicitely set
73 var $default_charset = 'utf8';
74
75 /**
76 * @var t3lib_DB_preProcessQueryHook[]
77 */
78 protected $preProcessHookObjects = array();
79
80 /**
81 * @var t3lib_DB_postProcessQueryHook[]
82 */
83 protected $postProcessHookObjects = array();
84
85 /************************************
86 *
87 * Query execution
88 *
89 * These functions are the RECOMMENDED DBAL functions for use in your applications
90 * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
91 * They compile a query AND execute it immediately and then return the result
92 * This principle heightens our ability to create various forms of DBAL of the functions.
93 * Generally: We want to return a result pointer/object, never queries.
94 * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
95 *
96 **************************************/
97
98 /**
99 * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
100 * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
101 *
102 * @param string $table Table name
103 * @param array $fields_values 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.
104 * @param string/array $no_quote_fields See fullQuoteArray()
105 * @return pointer MySQL result pointer / DBAL object
106 */
107 function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
108 $res = mysql_query($this->INSERTquery($table, $fields_values, $no_quote_fields), $this->link);
109 if ($this->debugOutput) {
110 $this->debug('exec_INSERTquery');
111 }
112 foreach ($this->postProcessHookObjects as $hookObject) {
113 $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
114 }
115 return $res;
116 }
117
118 /**
119 * Creates and executes an INSERT SQL-statement for $table with multiple rows.
120 *
121 * @param string $table Table name
122 * @param array $fields Field names
123 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
124 * @param string/array $no_quote_fields See fullQuoteArray()
125 * @return pointer MySQL result pointer / DBAL object
126 */
127 public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
128 $res = mysql_query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields), $this->link);
129 if ($this->debugOutput) {
130 $this->debug('exec_INSERTmultipleRows');
131 }
132 foreach ($this->postProcessHookObjects as $hookObject) {
133 $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
134 }
135 return $res;
136 }
137
138 /**
139 * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
140 * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
141 *
142 * @param string $table Database tablename
143 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
144 * @param array $fields_values 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.
145 * @param string/array $no_quote_fields See fullQuoteArray()
146 * @return pointer MySQL result pointer / DBAL object
147 */
148 function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
149 $res = mysql_query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields), $this->link);
150 if ($this->debugOutput) {
151 $this->debug('exec_UPDATEquery');
152 }
153 foreach ($this->postProcessHookObjects as $hookObject) {
154 $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
155 }
156 return $res;
157 }
158
159 /**
160 * Creates and executes a DELETE SQL-statement for $table where $where-clause
161 *
162 * @param string $table Database tablename
163 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
164 * @return pointer MySQL result pointer / DBAL object
165 */
166 function exec_DELETEquery($table, $where) {
167 $res = mysql_query($this->DELETEquery($table, $where), $this->link);
168 if ($this->debugOutput) {
169 $this->debug('exec_DELETEquery');
170 }
171 foreach ($this->postProcessHookObjects as $hookObject) {
172 $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
173 }
174 return $res;
175 }
176
177 /**
178 * Creates and executes a SELECT SQL-statement
179 * Using this function specifically allow us to handle the LIMIT feature independently of DB.
180 *
181 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
182 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
183 * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
184 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
185 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
186 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
187 * @return pointer MySQL result pointer / DBAL object
188 */
189 function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
190 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
191 $res = mysql_query($query, $this->link);
192
193 if ($this->debugOutput) {
194 $this->debug('exec_SELECTquery');
195 }
196 if ($this->explainOutput) {
197 $this->explain($query, $from_table, $this->sql_num_rows($res));
198 }
199
200 return $res;
201 }
202
203 /**
204 * Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
205 * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
206 * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local / [$mm_table].uid_foreign <--> [$foreign_table].uid
207 * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
208 *
209 * @param string $select Field list for SELECT
210 * @param string $local_table Tablename, local table
211 * @param string $mm_table Tablename, relation table
212 * @param string $foreign_table Tablename, foreign table
213 * @param string $whereClause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
214 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
215 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
216 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
217 * @return pointer MySQL result pointer / DBAL object
218 * @see exec_SELECTquery()
219 */
220 function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
221 if ($foreign_table == $local_table) {
222 $foreign_table_as = $foreign_table . uniqid('_join');
223 }
224
225 $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
226 $mmWhere .= ($local_table AND $foreign_table) ? ' AND ' : '';
227
228 $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
229
230 if ($foreign_table) {
231 $mmWhere .= ($foreign_table_as ? $foreign_table_as : $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
232 $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
233 }
234
235 return $this->exec_SELECTquery(
236 $select,
237 $tables,
238 // whereClauseMightContainGroupOrderBy
239 $mmWhere . ' ' . $whereClause,
240 $groupBy,
241 $orderBy,
242 $limit
243 );
244 }
245
246 /**
247 * Executes a select based on input query parts array
248 *
249 * @param array $queryParts Query parts array
250 * @return pointer MySQL select result pointer / DBAL object
251 * @see exec_SELECTquery()
252 */
253 function exec_SELECT_queryArray($queryParts) {
254 return $this->exec_SELECTquery(
255 $queryParts['SELECT'],
256 $queryParts['FROM'],
257 $queryParts['WHERE'],
258 $queryParts['GROUPBY'],
259 $queryParts['ORDERBY'],
260 $queryParts['LIMIT']
261 );
262 }
263
264 /**
265 * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
266 *
267 * @param string $select_fields See exec_SELECTquery()
268 * @param string $from_table See exec_SELECTquery()
269 * @param string $where_clause See exec_SELECTquery()
270 * @param string $groupBy See exec_SELECTquery()
271 * @param string $orderBy See exec_SELECTquery()
272 * @param string $limit See exec_SELECTquery()
273 * @param string $uidIndexField If set, the result array will carry this field names value as index. Requires that field to be selected of course!
274 * @return array Array of rows.
275 */
276 function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '') {
277 $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
278 if ($this->debugOutput) {
279 $this->debug('exec_SELECTquery');
280 }
281
282 if (!$this->sql_error()) {
283 $output = array();
284
285 if ($uidIndexField) {
286 while ($tempRow = $this->sql_fetch_assoc($res)) {
287 $output[$tempRow[$uidIndexField]] = $tempRow;
288 }
289 } else {
290 while ($output[] = $this->sql_fetch_assoc($res)) {
291 }
292 array_pop($output);
293 }
294 $this->sql_free_result($res);
295 }
296 return $output;
297 }
298
299 /**
300 * Creates and executes a SELECT SQL-statement AND gets a result set and returns an array with a single record in.
301 * LIMIT is automatically set to 1 and can not be overridden.
302 *
303 * @param string $select_fields List of fields to select from the table.
304 * @param string $from_table Table(s) from which to select.
305 * @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->fullQuoteStr() yourself!
306 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
307 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
308 * @param boolean $numIndex If set, the result will be fetched with sql_fetch_row, otherwise sql_fetch_assoc will be used.
309 * @return array Single row or NULL if it fails.
310 */
311 public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = FALSE) {
312 $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
313 if ($this->debugOutput) {
314 $this->debug('exec_SELECTquery');
315 }
316
317 $output = NULL;
318 if ($res) {
319 if ($numIndex) {
320 $output = $this->sql_fetch_row($res);
321 } else {
322 $output = $this->sql_fetch_assoc($res);
323 }
324 $this->sql_free_result($res);
325 }
326 return $output;
327 }
328
329 /**
330 * Counts the number of rows in a table.
331 *
332 * @param string $field Name of the field to use in the COUNT() expression (e.g. '*')
333 * @param string $table Name of the table to count rows for
334 * @param string $where (optional) WHERE statement of the query
335 * @return mixed Number of rows counter (integer) or FALSE if something went wrong (boolean)
336 */
337 public function exec_SELECTcountRows($field, $table, $where = '') {
338 $count = FALSE;
339 $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
340 if ($resultSet !== FALSE) {
341 list($count) = $this->sql_fetch_row($resultSet);
342 $count = intval($count);
343 $this->sql_free_result($resultSet);
344 }
345 return $count;
346 }
347
348 /**
349 * Truncates a table.
350 *
351 * @param string $table Database tablename
352 * @return mixed Result from handler
353 */
354 public function exec_TRUNCATEquery($table) {
355 $res = mysql_query($this->TRUNCATEquery($table), $this->link);
356 if ($this->debugOutput) {
357 $this->debug('exec_TRUNCATEquery');
358 }
359 foreach ($this->postProcessHookObjects as $hookObject) {
360 $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
361 }
362 return $res;
363 }
364
365 /**************************************
366 *
367 * Query building
368 *
369 **************************************/
370
371 /**
372 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
373 *
374 * @param string $table See exec_INSERTquery()
375 * @param array $fields_values See exec_INSERTquery()
376 * @param string/array $no_quote_fields See fullQuoteArray()
377 * @return string Full SQL query for INSERT (unless $fields_values does not contain any elements in which case it will be FALSE)
378 */
379 function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
380
381 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
382 // function (contrary to values in the arrays which may be insecure).
383 if (is_array($fields_values) && count($fields_values)) {
384 foreach ($this->preProcessHookObjects as $hookObject) {
385 $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
386 }
387
388 // Quote and escape values
389 $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields);
390
391 // Build query
392 $query = 'INSERT INTO ' . $table .
393 ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' .
394 '(' . implode(',', $fields_values) . ')';
395
396 // Return query
397 if ($this->debugOutput || $this->store_lastBuiltQuery) {
398 $this->debug_lastBuiltQuery = $query;
399 }
400 return $query;
401 }
402 }
403
404 /**
405 * Creates an INSERT SQL-statement for $table with multiple rows.
406 *
407 * @param string $table Table name
408 * @param array $fields Field names
409 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
410 * @param string/array $no_quote_fields See fullQuoteArray()
411 * @return string Full SQL query for INSERT (unless $rows does not contain any elements in which case it will be FALSE)
412 */
413 public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
414 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
415 // function (contrary to values in the arrays which may be insecure).
416 if (count($rows)) {
417 foreach ($this->preProcessHookObjects as $hookObject) {
418 $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
419 }
420
421 // Build query
422 $query = 'INSERT INTO ' . $table .
423 ' (' . implode(', ', $fields) . ') VALUES ';
424
425 $rowSQL = array();
426 foreach ($rows as $row) {
427 // Quote and escape values
428 $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
429 $rowSQL[] = '(' . implode(', ', $row) . ')';
430 }
431
432 $query .= implode(', ', $rowSQL);
433
434 // Return query
435 if ($this->debugOutput || $this->store_lastBuiltQuery) {
436 $this->debug_lastBuiltQuery = $query;
437 }
438
439 return $query;
440 }
441 }
442
443 /**
444 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
445 *
446 * @param string $table See exec_UPDATEquery()
447 * @param string $where See exec_UPDATEquery()
448 * @param array $fields_values See exec_UPDATEquery()
449 * @param array $no_quote_fields See fullQuoteArray()
450 * @return string Full SQL query for UPDATE
451 */
452 function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
453 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
454 // function (contrary to values in the arrays which may be insecure).
455 if (is_string($where)) {
456 foreach ($this->preProcessHookObjects as $hookObject) {
457 $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
458 }
459
460 $fields = array();
461 if (is_array($fields_values) && count($fields_values)) {
462
463 // Quote and escape values
464 $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields);
465
466 foreach ($nArr as $k => $v) {
467 $fields[] = $k . '=' . $v;
468 }
469 }
470
471 // Build query
472 $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) .
473 (strlen($where) > 0 ? ' WHERE ' . $where : '');
474
475 if ($this->debugOutput || $this->store_lastBuiltQuery) {
476 $this->debug_lastBuiltQuery = $query;
477 }
478 return $query;
479 } else {
480 throw new InvalidArgumentException(
481 'TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !',
482 1270853880
483 );
484 }
485 }
486
487 /**
488 * Creates a DELETE SQL-statement for $table where $where-clause
489 *
490 * @param string $table See exec_DELETEquery()
491 * @param string $where See exec_DELETEquery()
492 * @return string Full SQL query for DELETE
493 */
494 function DELETEquery($table, $where) {
495 if (is_string($where)) {
496 foreach ($this->preProcessHookObjects as $hookObject) {
497 $hookObject->DELETEquery_preProcessAction($table, $where, $this);
498 }
499
500 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
501 $query = 'DELETE FROM ' . $table .
502 (strlen($where) > 0 ? ' WHERE ' . $where : '');
503
504 if ($this->debugOutput || $this->store_lastBuiltQuery) {
505 $this->debug_lastBuiltQuery = $query;
506 }
507 return $query;
508 } else {
509 throw new InvalidArgumentException(
510 'TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !',
511 1270853881
512 );
513 }
514 }
515
516 /**
517 * Creates a SELECT SQL-statement
518 *
519 * @param string $select_fields See exec_SELECTquery()
520 * @param string $from_table See exec_SELECTquery()
521 * @param string $where_clause See exec_SELECTquery()
522 * @param string $groupBy See exec_SELECTquery()
523 * @param string $orderBy See exec_SELECTquery()
524 * @param string $limit See exec_SELECTquery()
525 * @return string Full SQL query for SELECT
526 */
527 function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
528
529 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
530 // Build basic query
531 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table .
532 (strlen($where_clause) > 0 ? ' WHERE ' . $where_clause : '');
533
534 // Group by
535 $query .= (strlen($groupBy) > 0 ? ' GROUP BY ' . $groupBy : '');
536
537 // Order by
538 $query .= (strlen($orderBy) > 0 ? ' ORDER BY ' . $orderBy : '');
539
540 // Group by
541 $query .= (strlen($limit) > 0 ? ' LIMIT ' . $limit : '');
542
543 // Return query
544 if ($this->debugOutput || $this->store_lastBuiltQuery) {
545 $this->debug_lastBuiltQuery = $query;
546 }
547 return $query;
548 }
549
550 /**
551 * Creates a SELECT SQL-statement to be used as subquery within another query.
552 * BEWARE: This method should not be overriden within DBAL to prevent quoting from happening.
553 *
554 * @param string $select_fields List of fields to select from the table.
555 * @param string $from_table Table from which to select.
556 * @param string $where_clause Conditional WHERE statement
557 * @return string Full SQL query for SELECT
558 */
559 public function SELECTsubquery($select_fields, $from_table, $where_clause) {
560 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
561 // Build basic query:
562 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table .
563 (strlen($where_clause) > 0 ? ' WHERE ' . $where_clause : '');
564
565 // Return query
566 if ($this->debugOutput || $this->store_lastBuiltQuery) {
567 $this->debug_lastBuiltQuery = $query;
568 }
569
570 return $query;
571 }
572
573 /**
574 * Creates a TRUNCATE TABLE SQL-statement
575 *
576 * @param string $table See exec_TRUNCATEquery()
577 * @return string Full SQL query for TRUNCATE TABLE
578 */
579 public function TRUNCATEquery($table) {
580 foreach ($this->preProcessHookObjects as $hookObject) {
581 $hookObject->TRUNCATEquery_preProcessAction($table, $this);
582 }
583
584 // Table should be "SQL-injection-safe" when supplied to this function
585 // Build basic query:
586 $query = 'TRUNCATE TABLE ' . $table;
587
588 // Return query:
589 if ($this->debugOutput || $this->store_lastBuiltQuery) {
590 $this->debug_lastBuiltQuery = $query;
591 }
592
593 return $query;
594 }
595
596 /**
597 * Returns a WHERE clause that can find a value ($value) in a list field ($field)
598 * For instance a record in the database might contain a list of numbers,
599 * "34,234,5" (with no spaces between). This query would be able to select that
600 * record based on the value "34", "234" or "5" regardless of their position in
601 * the list (left, middle or right).
602 * The value must not contain a comma (,)
603 * Is nice to look up list-relations to records or files in TYPO3 database tables.
604 *
605 * @param string $field Field name
606 * @param string $value Value to find in list
607 * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
608 * @return string WHERE clause for a query
609 */
610 public function listQuery($field, $value, $table) {
611 $value = (string) $value;
612 if (strpos(',', $value) !== FALSE) {
613 throw new InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
614 }
615 $pattern = $this->quoteStr($value, $table);
616 $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
617 return $where;
618 }
619
620 /**
621 * Returns a WHERE clause which will make an AND search for the words in the $searchWords array in any of the fields in array $fields.
622 *
623 * @param array $searchWords Array of search words
624 * @param array $fields Array of fields
625 * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
626 * @return string WHERE clause for search
627 */
628 function searchQuery($searchWords, $fields, $table) {
629 $queryParts = array();
630
631 foreach ($searchWords as $sw) {
632 $like = ' LIKE \'%' . $this->quoteStr($sw, $table) . '%\'';
633 $queryParts[] = $table . '.' . implode($like . ' OR ' . $table . '.', $fields) . $like;
634 }
635 $query = '(' . implode(') AND (', $queryParts) . ')';
636 return $query;
637 }
638
639 /**************************************
640 *
641 * Prepared Query Support
642 *
643 **************************************/
644
645 /**
646 * Creates a SELECT prepared SQL statement.
647 *
648 * @param string $select_fields See exec_SELECTquery()
649 * @param string $from_table See exec_SELECTquery()
650 * @param string $where_clause See exec_SELECTquery()
651 * @param string $groupBy See exec_SELECTquery()
652 * @param string $orderBy See exec_SELECTquery()
653 * @param string $limit See exec_SELECTquery()
654 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
655 * @return t3lib_db_PreparedStatement Prepared statement
656 */
657 public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
658 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
659 /** @var $preparedStatement t3lib_db_PreparedStatement */
660 $preparedStatement = t3lib_div::makeInstance('t3lib_db_PreparedStatement', $query, $from_table, array());
661
662 // Bind values to parameters
663 foreach ($input_parameters as $key => $value) {
664 $preparedStatement->bindValue($key, $value, t3lib_db_PreparedStatement::PARAM_AUTOTYPE);
665 }
666
667 // Return prepared statement
668 return $preparedStatement;
669 }
670
671 /**
672 * Creates a SELECT prepared SQL statement based on input query parts array
673 *
674 * @param array $queryParts Query parts array
675 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
676 * @return t3lib_db_PreparedStatement Prepared statement
677 */
678 public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
679 return $this->prepare_SELECTquery(
680 $queryParts['SELECT'],
681 $queryParts['FROM'],
682 $queryParts['WHERE'],
683 $queryParts['GROUPBY'],
684 $queryParts['ORDERBY'],
685 $queryParts['LIMIT'],
686 $input_parameters
687 );
688 }
689
690 /**
691 * Executes a prepared query.
692 * This method may only be called by t3lib_db_PreparedStatement.
693 *
694 * @param string $query The query to execute
695 * @param array $queryComponents The components of the query to execute
696 * @return pointer MySQL result pointer / DBAL object
697 */
698 public function exec_PREPAREDquery($query, array $queryComponents) {
699 $res = mysql_query($query, $this->link);
700 if ($this->debugOutput) {
701 $this->debug('stmt_execute', $query);
702 }
703 return $res;
704 }
705
706 /**************************************
707 *
708 * Various helper functions
709 *
710 * Functions recommended to be used for
711 * - escaping values,
712 * - cleaning lists of values,
713 * - stripping of excess ORDER BY/GROUP BY keywords
714 *
715 **************************************/
716
717 /**
718 * Escaping and quoting values for SQL statements.
719 *
720 * @param string $str Input string
721 * @param string $table 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!).
722 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
723 * @see quoteStr()
724 */
725 function fullQuoteStr($str, $table) {
726 return '\'' . mysql_real_escape_string($str, $this->link) . '\'';
727 }
728
729 /**
730 * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
731 *
732 * @param array $arr Array with values (either associative or non-associative array)
733 * @param string $table Table name for which to quote
734 * @param string/array $noQuote List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
735 * @return array The input array with the values quoted
736 * @see cleanIntArray()
737 */
738 function fullQuoteArray($arr, $table, $noQuote = FALSE) {
739 if (is_string($noQuote)) {
740 $noQuote = explode(',', $noQuote);
741 // Sanity check
742 } elseif (!is_array($noQuote)) {
743 $noQuote = FALSE;
744 }
745
746 foreach ($arr as $k => $v) {
747 if ($noQuote === FALSE || !in_array($k, $noQuote)) {
748 $arr[$k] = $this->fullQuoteStr($v, $table);
749 }
750 }
751 return $arr;
752 }
753
754 /**
755 * Substitution for PHP function "addslashes()"
756 * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
757 * 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()!
758 *
759 * @param string $str Input string
760 * @param string $table 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!).
761 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
762 * @see quoteStr()
763 */
764 function quoteStr($str, $table) {
765 return mysql_real_escape_string($str, $this->link);
766 }
767
768 /**
769 * Escaping values for SQL LIKE statements.
770 *
771 * @param string $str Input string
772 * @param string $table Table name for which to escape 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!).
773 * @return string Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
774 * @see quoteStr()
775 */
776 function escapeStrForLike($str, $table) {
777 return addcslashes($str, '_%');
778 }
779
780 /**
781 * Will convert all values in the one-dimensional array to integers.
782 * Useful when you want to make sure an array contains only integers before imploding them in a select-list.
783 *
784 * @param array $arr Array with values
785 * @return array The input array with all values passed through intval()
786 * @see cleanIntList()
787 */
788 function cleanIntArray($arr) {
789 foreach ($arr as $k => $v) {
790 $arr[$k] = intval($arr[$k]);
791 }
792 return $arr;
793 }
794
795 /**
796 * Will force all entries in the input comma list to integers
797 * Useful when you want to make sure a commalist of supposed integers really contain only integers; You want to know that when you don't trust content that could go into an SQL statement.
798 *
799 * @param string $list List of comma-separated values which should be integers
800 * @return string The input list but with every value passed through intval()
801 * @see cleanIntArray()
802 */
803 function cleanIntList($list) {
804 return implode(',', t3lib_div::intExplode(',', $list));
805 }
806
807 /**
808 * Removes the prefix "ORDER BY" from the input string.
809 * This function is used when you call the exec_SELECTquery() function and want to pass the ORDER BY parameter by can't guarantee that "ORDER BY" is not prefixed.
810 * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
811 *
812 * @param string $str eg. "ORDER BY title, uid"
813 * @return string eg. "title, uid"
814 * @see exec_SELECTquery(), stripGroupBy()
815 */
816 function stripOrderBy($str) {
817 return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i', '', trim($str));
818 }
819
820 /**
821 * Removes the prefix "GROUP BY" from the input string.
822 * This function is used when you call the SELECTquery() function and want to pass the GROUP BY parameter by can't guarantee that "GROUP BY" is not prefixed.
823 * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
824 *
825 * @param string $str eg. "GROUP BY title, uid"
826 * @return string eg. "title, uid"
827 * @see exec_SELECTquery(), stripOrderBy()
828 */
829 function stripGroupBy($str) {
830 return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i', '', trim($str));
831 }
832
833 /**
834 * Takes the last part of a query, eg. "... uid=123 GROUP BY title ORDER BY title LIMIT 5,2" and splits each part into a table (WHERE, GROUPBY, ORDERBY, LIMIT)
835 * Work-around function for use where you know some userdefined end to an SQL clause is supplied and you need to separate these factors.
836 *
837 * @param string $str Input string
838 * @return array
839 */
840 function splitGroupOrderLimit($str) {
841 // Prepending a space to make sure "[[:space:]]+" will find a space there
842 // for the first element.
843 $str = ' ' . $str;
844 // Init output array:
845 $wgolParts = array(
846 'WHERE' => '',
847 'GROUPBY' => '',
848 'ORDERBY' => '',
849 'LIMIT' => '',
850 );
851
852 // Find LIMIT
853 $reg = array();
854 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
855 $wgolParts['LIMIT'] = trim($reg[2]);
856 $str = $reg[1];
857 }
858
859 // Find ORDER BY
860 $reg = array();
861 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
862 $wgolParts['ORDERBY'] = trim($reg[2]);
863 $str = $reg[1];
864 }
865
866 // Find GROUP BY
867 $reg = array();
868 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
869 $wgolParts['GROUPBY'] = trim($reg[2]);
870 $str = $reg[1];
871 }
872
873 // Rest is assumed to be "WHERE" clause
874 $wgolParts['WHERE'] = $str;
875
876 return $wgolParts;
877 }
878
879 /**************************************
880 *
881 * MySQL wrapper functions
882 * (For use in your applications)
883 *
884 **************************************/
885
886 /**
887 * Executes query
888 * mysql_query() wrapper function
889 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
890 * using exec_SELECTquery() and similar methods instead.
891 *
892 * @param string $query Query to execute
893 * @return pointer Result pointer / DBAL object
894 */
895 function sql_query($query) {
896 $res = mysql_query($query, $this->link);
897 if ($this->debugOutput) {
898 $this->debug('sql_query', $query);
899 }
900 return $res;
901 }
902
903 /**
904 * Returns the error status on the last sql() execution
905 * mysql_error() wrapper function
906 *
907 * @return string MySQL error string.
908 */
909 function sql_error() {
910 return mysql_error($this->link);
911 }
912
913 /**
914 * Returns the error number on the last sql() execution
915 * mysql_errno() wrapper function
916 *
917 * @return integer MySQL error number.
918 */
919 function sql_errno() {
920 return mysql_errno($this->link);
921 }
922
923 /**
924 * Returns the number of selected rows.
925 * mysql_num_rows() wrapper function
926 *
927 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
928 * @return integer Number of resulting rows
929 */
930 function sql_num_rows($res) {
931 if ($this->debug_check_recordset($res)) {
932 return mysql_num_rows($res);
933 } else {
934 return FALSE;
935 }
936 }
937
938 /**
939 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
940 * mysql_fetch_assoc() wrapper function
941 *
942 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
943 * @return array Associative array of result row.
944 */
945 function sql_fetch_assoc($res) {
946 if ($this->debug_check_recordset($res)) {
947 return mysql_fetch_assoc($res);
948 } else {
949 return FALSE;
950 }
951 }
952
953 /**
954 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
955 * The array contains the values in numerical indices.
956 * mysql_fetch_row() wrapper function
957 *
958 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
959 * @return array Array with result rows.
960 */
961 function sql_fetch_row($res) {
962 if ($this->debug_check_recordset($res)) {
963 return mysql_fetch_row($res);
964 } else {
965 return FALSE;
966 }
967 }
968
969 /**
970 * Free result memory
971 * mysql_free_result() wrapper function
972 *
973 * @param pointer $res MySQL result pointer to free / DBAL object
974 * @return boolean Returns TRUE on success or FALSE on failure.
975 */
976 function sql_free_result($res) {
977 if ($this->debug_check_recordset($res)) {
978 return mysql_free_result($res);
979 } else {
980 return FALSE;
981 }
982 }
983
984 /**
985 * Get the ID generated from the previous INSERT operation
986 * mysql_insert_id() wrapper function
987 *
988 * @return integer The uid of the last inserted record.
989 */
990 function sql_insert_id() {
991 return mysql_insert_id($this->link);
992 }
993
994 /**
995 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
996 * mysql_affected_rows() wrapper function
997 *
998 * @return integer Number of rows affected by last query
999 */
1000 function sql_affected_rows() {
1001 return mysql_affected_rows($this->link);
1002 }
1003
1004 /**
1005 * Move internal result pointer
1006 * mysql_data_seek() wrapper function
1007 *
1008 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
1009 * @param integer $seek Seek result number.
1010 * @return boolean Returns TRUE on success or FALSE on failure.
1011 */
1012 function sql_data_seek($res, $seek) {
1013 if ($this->debug_check_recordset($res)) {
1014 return mysql_data_seek($res, $seek);
1015 } else {
1016 return FALSE;
1017 }
1018 }
1019
1020 /**
1021 * Get the type of the specified field in a result
1022 * mysql_field_type() wrapper function
1023 *
1024 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
1025 * @param integer $pointer Field index.
1026 * @return string Returns the name of the specified field index
1027 */
1028 function sql_field_type($res, $pointer) {
1029 if ($this->debug_check_recordset($res)) {
1030 return mysql_field_type($res, $pointer);
1031 } else {
1032 return FALSE;
1033 }
1034 }
1035
1036 /**
1037 * Open a (persistent) connection to a MySQL server
1038 * mysql_pconnect() wrapper function
1039 *
1040 * @param string $TYPO3_db_host Database host IP/domain
1041 * @param string $TYPO3_db_username Username to connect with.
1042 * @param string $TYPO3_db_password Password to connect with.
1043 * @return pointer Returns a positive MySQL persistent link identifier on success, or FALSE on error.
1044 */
1045 function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
1046 // mysql_error() is tied to an established connection
1047 // if the connection fails we need a different method to get the error message
1048 @ini_set('track_errors', 1);
1049 @ini_set('html_errors', 0);
1050
1051 // Check if MySQL extension is loaded
1052 if (!extension_loaded('mysql')) {
1053 $message = 'Database Error: It seems that MySQL support for PHP is not installed!';
1054 throw new RuntimeException($message, 1271492606);
1055 }
1056
1057 // Check for client compression
1058 $isLocalhost = ($TYPO3_db_host == 'localhost' || $TYPO3_db_host == '127.0.0.1');
1059 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
1060 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1061 // We use PHP's default value for 4th parameter (new_link), which is FALSE.
1062 // See PHP sources, for example: file php-5.2.5/ext/mysql/php_mysql.c,
1063 // function php_mysql_do_connect(), near line 525
1064 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, FALSE, MYSQL_CLIENT_COMPRESS);
1065 } else {
1066 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1067 }
1068 } else {
1069 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1070 // See comment about 4th parameter in block above
1071 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, MYSQL_CLIENT_COMPRESS);
1072 } else {
1073 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1074 }
1075 }
1076
1077 $error_msg = $php_errormsg;
1078 @ini_restore('track_errors');
1079 @ini_restore('html_errors');
1080
1081 if (!$this->link) {
1082 t3lib_div::sysLog('Could not connect to MySQL server ' . $TYPO3_db_host .
1083 ' with user ' . $TYPO3_db_username . ': ' . $error_msg,
1084 'Core',
1085 4
1086 );
1087 } else {
1088 $setDBinit = t3lib_div::trimExplode(LF, str_replace("' . LF . '", LF, $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit']), TRUE);
1089 foreach ($setDBinit as $v) {
1090 if (mysql_query($v, $this->link) === FALSE) {
1091 t3lib_div::sysLog('Could not initialize DB connection with query "' . $v .
1092 '": ' . mysql_error($this->link),
1093 'Core',
1094 3
1095 );
1096 }
1097 }
1098 $this->setSqlMode();
1099 }
1100
1101 return $this->link;
1102 }
1103
1104 /**
1105 * Fixes the SQL mode by unsetting NO_BACKSLASH_ESCAPES if found.
1106 *
1107 * @return void
1108 */
1109 protected function setSqlMode() {
1110 $resource = $this->sql_query('SELECT @@SESSION.sql_mode;');
1111 if (is_resource($resource)) {
1112 $result = $this->sql_fetch_row($resource);
1113 if (isset($result[0]) && $result[0] && strpos($result[0], 'NO_BACKSLASH_ESCAPES') !== FALSE) {
1114 $modes = array_diff(
1115 t3lib_div::trimExplode(',', $result[0]),
1116 array('NO_BACKSLASH_ESCAPES')
1117 );
1118 $query = 'SET sql_mode=\'' . mysql_real_escape_string(implode(',', $modes)) . '\';';
1119 $success = $this->sql_query($query);
1120
1121 t3lib_div::sysLog(
1122 'NO_BACKSLASH_ESCAPES could not be removed from SQL mode: ' . $this->sql_error(),
1123 'Core',
1124 3
1125 );
1126 }
1127 }
1128 }
1129
1130 /**
1131 * Select a MySQL database
1132 * mysql_select_db() wrapper function
1133 *
1134 * @param string $TYPO3_db Database to connect to.
1135 * @return boolean Returns TRUE on success or FALSE on failure.
1136 */
1137 function sql_select_db($TYPO3_db) {
1138 $ret = @mysql_select_db($TYPO3_db, $this->link);
1139 if (!$ret) {
1140 t3lib_div::sysLog('Could not select MySQL database ' . $TYPO3_db . ': ' .
1141 mysql_error(),
1142 'Core',
1143 4
1144 );
1145 }
1146 return $ret;
1147 }
1148
1149 /**************************************
1150 *
1151 * SQL admin functions
1152 * (For use in the Install Tool and Extension Manager)
1153 *
1154 **************************************/
1155
1156 /**
1157 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1158 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
1159 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
1160 * Use in Install Tool only!
1161 *
1162 * @return array Each entry represents a database name
1163 */
1164 function admin_get_dbs() {
1165 $dbArr = array();
1166 $db_list = mysql_list_dbs($this->link);
1167 while ($row = mysql_fetch_object($db_list)) {
1168 if ($this->sql_select_db($row->Database)) {
1169 $dbArr[] = $row->Database;
1170 }
1171 }
1172 return $dbArr;
1173 }
1174
1175 /**
1176 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
1177 * In a DBAL this method should 1) look up all tables from the DBMS of
1178 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
1179 *
1180 * @return array Array with tablenames as key and arrays with status information as value
1181 */
1182 function admin_get_tables() {
1183 $whichTables = array();
1184
1185 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->link);
1186 if (!mysql_error()) {
1187 while ($theTable = mysql_fetch_assoc($tables_result)) {
1188 $whichTables[$theTable['Name']] = $theTable;
1189 }
1190
1191 $this->sql_free_result($tables_result);
1192 }
1193
1194 return $whichTables;
1195 }
1196
1197 /**
1198 * Returns information about each field in the $table (quering the DBMS)
1199 * In a DBAL this should look up the right handler for the table and return compatible information
1200 * This function is important not only for the Install Tool but probably for
1201 * DBALs as well since they might need to look up table specific information
1202 * in order to construct correct queries. In such cases this information should
1203 * probably be cached for quick delivery.
1204 *
1205 * @param string $tableName Table name
1206 * @return array Field information in an associative array with fieldname => field row
1207 */
1208 function admin_get_fields($tableName) {
1209 $output = array();
1210
1211 $columns_res = mysql_query('SHOW COLUMNS FROM `' . $tableName . '`', $this->link);
1212 while ($fieldRow = mysql_fetch_assoc($columns_res)) {
1213 $output[$fieldRow['Field']] = $fieldRow;
1214 }
1215
1216 $this->sql_free_result($columns_res);
1217
1218 return $output;
1219 }
1220
1221 /**
1222 * Returns information about each index key in the $table (quering the DBMS)
1223 * In a DBAL this should look up the right handler for the table and return compatible information
1224 *
1225 * @param string $tableName Table name
1226 * @return array Key information in a numeric array
1227 */
1228 function admin_get_keys($tableName) {
1229 $output = array();
1230
1231 $keyRes = mysql_query('SHOW KEYS FROM `' . $tableName . '`', $this->link);
1232 while ($keyRow = mysql_fetch_assoc($keyRes)) {
1233 $output[] = $keyRow;
1234 }
1235
1236 $this->sql_free_result($keyRes);
1237
1238 return $output;
1239 }
1240
1241 /**
1242 * Returns information about the character sets supported by the current DBM
1243 * This function is important not only for the Install Tool but probably for
1244 * DBALs as well since they might need to look up table specific information
1245 * in order to construct correct queries. In such cases this information should
1246 * probably be cached for quick delivery.
1247 *
1248 * This is used by the Install Tool to convert tables tables with non-UTF8 charsets
1249 * Use in Install Tool only!
1250 *
1251 * @return array Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
1252 */
1253 function admin_get_charsets() {
1254 $output = array();
1255
1256 $columns_res = mysql_query('SHOW CHARACTER SET', $this->link);
1257 if ($columns_res) {
1258 while (($row = mysql_fetch_assoc($columns_res))) {
1259 $output[$row['Charset']] = $row;
1260 }
1261
1262 $this->sql_free_result($columns_res);
1263 }
1264
1265 return $output;
1266 }
1267
1268 /**
1269 * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1270 *
1271 * @param string $query Query to execute
1272 * @return pointer Result pointer
1273 */
1274 function admin_query($query) {
1275 $res = mysql_query($query, $this->link);
1276 if ($this->debugOutput) {
1277 $this->debug('admin_query', $query);
1278 }
1279 return $res;
1280 }
1281
1282 /******************************
1283 *
1284 * Connecting service
1285 *
1286 ******************************/
1287
1288 /**
1289 * Connects to database for TYPO3 sites:
1290 *
1291 * @param string $host
1292 * @param string $user
1293 * @param string $password
1294 * @param string $db
1295 * @return void
1296 */
1297 function connectDB($host = TYPO3_db_host, $user = TYPO3_db_username, $password = TYPO3_db_password, $db = TYPO3_db) {
1298 // If no db is given we throw immediately. This is a sign for a fresh (not configured)
1299 // TYPO3 installation and is used in FE to redirect to 1-2-3 install tool
1300 if (!$db) {
1301 throw new RuntimeException(
1302 'TYPO3 Fatal Error: No database selected!',
1303 1270853882
1304 );
1305 }
1306
1307 if ($this->sql_pconnect($host, $user, $password)) {
1308 if (!$this->sql_select_db($db)) {
1309 throw new RuntimeException(
1310 'TYPO3 Fatal Error: Cannot connect to the current database, "' . $db . '"!',
1311 1270853883
1312 );
1313 }
1314 } else {
1315 throw new RuntimeException(
1316 'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1317 1270853884
1318 );
1319 }
1320
1321 // Prepare user defined objects (if any) for hooks which extend query methods
1322 $this->preProcessHookObjects = array();
1323 $this->postProcessHookObjects = array();
1324 if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1325 foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1326 $hookObject = t3lib_div::getUserObj($classRef);
1327
1328 if (!($hookObject instanceof t3lib_DB_preProcessQueryHook || $hookObject instanceof t3lib_DB_postProcessQueryHook)) {
1329 throw new UnexpectedValueException('$hookObject must either implement interface t3lib_DB_preProcessQueryHook or interface t3lib_DB_postProcessQueryHook', 1299158548);
1330 }
1331 if ($hookObject instanceof t3lib_DB_preProcessQueryHook) {
1332 $this->preProcessHookObjects[] = $hookObject;
1333 }
1334 if ($hookObject instanceof t3lib_DB_postProcessQueryHook) {
1335 $this->postProcessHookObjects[] = $hookObject;
1336 }
1337 }
1338 }
1339 }
1340
1341 /**
1342 * Checks if database is connected
1343 *
1344 * @return boolean
1345 */
1346 public function isConnected() {
1347 return is_resource($this->link);
1348 }
1349
1350 /******************************
1351 *
1352 * Debugging
1353 *
1354 ******************************/
1355
1356 /**
1357 * Debug function: Outputs error if any
1358 *
1359 * @param string $func Function calling debug()
1360 * @param string $query Last query if not last built query
1361 * @return void
1362 */
1363 function debug($func, $query = '') {
1364
1365 $error = $this->sql_error();
1366 if ($error || (int)$this->debugOutput === 2) {
1367 debug(
1368 array(
1369 'caller' => 't3lib_DB::' . $func,
1370 'ERROR' => $error,
1371 'lastBuiltQuery' => ($query ? $query : $this->debug_lastBuiltQuery),
1372 'debug_backtrace' => t3lib_utility_Debug::debugTrail(),
1373 ),
1374 $func,
1375 is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug')) ? '' : 'DB Error'
1376 );
1377 }
1378 }
1379
1380 /**
1381 * Checks if recordset is valid and writes debugging inormation into devLog if not.
1382 *
1383 * @param resource $res Recordset
1384 * @return boolean <code>FALSE</code> if recordset is not valid
1385 */
1386 function debug_check_recordset($res) {
1387 if (!$res) {
1388 $trace = FALSE;
1389 $msg = 'Invalid database result resource detected';
1390 $trace = debug_backtrace();
1391 array_shift($trace);
1392 $cnt = count($trace);
1393 for ($i = 0; $i < $cnt; $i++) {
1394 // Complete objects are too large for the log
1395 if (isset($trace['object'])) {
1396 unset($trace['object']);
1397 }
1398 }
1399 $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' .
1400 substr($trace[0]['file'], strlen(PATH_site) + 2) . ' in line ' .
1401 $trace[0]['line'];
1402 t3lib_div::sysLog($msg . '. Use a devLog extension to get more details.', 'Core/t3lib_db', 3);
1403 // Send to devLog if enabled
1404 if (TYPO3_DLOG) {
1405 $debugLogData = array(
1406 'SQL Error' => $this->sql_error(),
1407 'Backtrace' => $trace,
1408 );
1409 if ($this->debug_lastBuiltQuery) {
1410 $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1411 }
1412 t3lib_div::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
1413 }
1414
1415 return FALSE;
1416 }
1417 return TRUE;
1418 }
1419
1420 /**
1421 * Explain select queries
1422 * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
1423 * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
1424 *
1425 * TODO: Feature is not DBAL-compliant
1426 *
1427 * @param string $query SQL query
1428 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
1429 * @param integer $row_count Number of resulting rows
1430 * @return boolean TRUE if explain was run, FALSE otherwise
1431 */
1432 protected function explain($query, $from_table, $row_count) {
1433
1434 if ((int) $this->explainOutput == 1 || ((int) $this->explainOutput == 2 &&
1435 t3lib_div::cmpIP(t3lib_div::getIndpEnv('REMOTE_ADDR'), $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']))
1436 ) {
1437 // Raw HTML output
1438 $explainMode = 1;
1439 } elseif ((int) $this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1440 // Embed the output into the TS admin panel
1441 $explainMode = 2;
1442 } else {
1443 return FALSE;
1444 }
1445
1446 $error = $this->sql_error();
1447 $trail = t3lib_utility_Debug::debugTrail();
1448
1449 $explain_tables = array();
1450 $explain_output = array();
1451 $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1452 if (is_resource($res)) {
1453 while ($tempRow = $this->sql_fetch_assoc($res)) {
1454 $explain_output[] = $tempRow;
1455 $explain_tables[] = $tempRow['table'];
1456 }
1457 $this->sql_free_result($res);
1458 }
1459
1460 $indices_output = array();
1461 // Notice: Rows are skipped if there is only one result, or if no conditions are set
1462 if ($explain_output[0]['rows'] > 1 || t3lib_div::inList('ALL', $explain_output[0]['type'])) {
1463 // Only enable output if it's really useful
1464 $debug = TRUE;
1465
1466 foreach ($explain_tables as $table) {
1467 $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1468 $isTable = $this->sql_num_rows($tableRes);
1469 if ($isTable) {
1470 $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1471 if (is_resource($res)) {
1472 while ($tempRow = $this->sql_fetch_assoc($res)) {
1473 $indices_output[] = $tempRow;
1474 }
1475 $this->sql_free_result($res);
1476 }
1477 }
1478 $this->sql_free_result($tableRes);
1479 }
1480 } else {
1481 $debug = FALSE;
1482 }
1483
1484 if ($debug) {
1485 if ($explainMode) {
1486 $data = array();
1487 $data['query'] = $query;
1488 $data['trail'] = $trail;
1489 $data['row_count'] = $row_count;
1490
1491 if ($error) {
1492 $data['error'] = $error;
1493 }
1494 if (count($explain_output)) {
1495 $data['explain'] = $explain_output;
1496 }
1497 if (count($indices_output)) {
1498 $data['indices'] = $indices_output;
1499 }
1500
1501 if ($explainMode == 1) {
1502 t3lib_utility_Debug::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1503 } elseif ($explainMode == 2) {
1504 $GLOBALS['TT']->setTSselectQuery($data);
1505 }
1506 }
1507 return TRUE;
1508 }
1509
1510 return FALSE;
1511 }
1512 }
1513
1514 ?>