Fixed bug #14050: CleanUp - CGL format of t3lib files - t3lib_db_directory
[Packages/TYPO3.CMS.git] / t3lib / db / class.t3lib_db_preparedstatement.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2010 Xavier Perseguers <typo3@perseguers.ch>
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 * TYPO3 prepared statement for t3lib_db class.
30 *
31 * USE:
32 * In all TYPO3 scripts when you need to create a prepared query:
33 * <code>
34 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 'uid = :uid');
35 * $statement->execute(array(':uid' => 2));
36 * while (($row = $statement->fetch()) !== FALSE) {
37 * // ...
38 * }
39 * $statement->free();
40 * </code>
41 *
42 * @author Xavier Perseguers <typo3@perseguers.ch>
43 * @package TYPO3
44 * @subpackage t3lib
45 */
46 class t3lib_db_PreparedStatement {
47
48 /**
49 * Represents the SQL NULL data type.
50 * @var integer
51 */
52 const PARAM_NULL = 0;
53
54 /**
55 * Represents the SQL INTEGER data type.
56 * @var integer
57 */
58 const PARAM_INT = 1;
59
60 /**
61 * Represents the SQL CHAR, VARCHAR, or other string data type.
62 * @var integer
63 */
64 const PARAM_STR = 2;
65
66 /**
67 * Represents a boolean data type.
68 * @var integer
69 */
70 const PARAM_BOOL = 3;
71
72 /**
73 * Automatically detects underlying type
74 * @var integer
75 */
76 const PARAM_AUTOTYPE = 4;
77
78 /**
79 * Specifies that the fetch method shall return each row as an array indexed by
80 * column name as returned in the corresponding result set. If the result set
81 * contains multiple columns with the same name, t3lib_db_PreparedStatement::FETCH_ASSOC
82 * returns only a single value per column name.
83 * @var integer
84 */
85 const FETCH_ASSOC = 2;
86
87 /**
88 * Specifies that the fetch method shall return each row as an array indexed by
89 * column number as returned in the corresponding result set, starting at column 0.
90 * @var integer
91 */
92 const FETCH_NUM = 3;
93
94 /**
95 * Query to be executed.
96 * @var string
97 */
98 protected $query;
99
100 /**
101 * Components of the query to be executed.
102 * @var array
103 */
104 protected $precompiledQueryParts;
105
106 /**
107 * Table (used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
108 * @var string
109 */
110 protected $table;
111
112 /**
113 * Binding parameters.
114 * @var array
115 */
116 protected $parameters;
117
118 /**
119 * Default fetch mode.
120 * @var integer
121 */
122 protected $defaultFetchMode = self::FETCH_ASSOC;
123
124 /**
125 * MySQL result pointer (of SELECT query) / DBAL object.
126 * @var pointer
127 */
128 protected $resource;
129
130 /**
131 * Creates a new PreparedStatement. Either $query or $queryComponents
132 * should be used. Typically $query will be used by native MySQL TYPO3_DB
133 * on a ready-to-be-executed query. On the other hand, DBAL will have
134 * parse the query and will be able to safely know where parameters are used
135 * and will use $queryComponents instead.
136 * This constructor may only be used by t3lib_DB.
137 *
138 * @param string $query SQL query to be executed
139 * @param string FROM table, used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
140 * @param array $precompiledQueryParts Components of the query to be executed
141 * @access private
142 */
143 public function __construct($query, $table, array $precompiledQueryParts = array()) {
144 $this->query = $query;
145 $this->precompiledQueryParts = $precompiledQueryParts;
146 $this->table = $table;
147 $this->parameters = array();
148 $this->resource = NULL;
149 }
150
151 /**
152 * Binds an array of values to corresponding named or question mark placeholders in the SQL
153 * statement that was use to prepare the statement.
154 *
155 * Example 1:
156 * <code>
157 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
158 * $statement->bindValues(array('goofy', 'FIXED'));
159 * </code>
160 *
161 * Example 2:
162 * <code>
163 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
164 * $statement->bindValues(array(':nickname' => 'goofy', ':status' => 'FIXED'));
165 * </code>
166 *
167 * @param array $values The values to bind to the parameter. The PHP type of each array value will be used to decide which PARAM_* type to use (int, string, boolean, null), so make sure your variables are properly casted, if needed.
168 * @return t3lib_db_PreparedStatement The current prepared statement to allow method chaining
169 * @api
170 */
171 public function bindValues(array $values) {
172 foreach ($values as $parameter => $value) {
173 $key = is_int($parameter) ? $parameter + 1 : $parameter;
174 $this->bindValue($key, $value, self::PARAM_AUTOTYPE);
175 }
176
177 return $this;
178 }
179
180 /**
181 * Binds a value to a corresponding named or question mark placeholder in the SQL
182 * statement that was use to prepare the statement.
183 *
184 * Example 1:
185 * <code>
186 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
187 * $statement->bindValue(1, 'goofy');
188 * $statement->bindValue(2, 'FIXED');
189 * </code>
190 *
191 * Example 2:
192 * <code>
193 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
194 * $statement->bindValue(':nickname', 'goofy');
195 * $statement->bindValue(':status', 'FIXED');
196 * </code>
197 *
198 * @param mixed $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
199 * @param mixed $value The value to bind to the parameter.
200 * @param integer $data_type Explicit data type for the parameter using the t3lib_db_PreparedStatement::PARAM_* constants. If not given, the PHP type of the value will be used instead (int, string, boolean).
201 * @return t3lib_db_PreparedStatement The current prepared statement to allow method chaining
202 * @api
203 */
204 public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE) {
205 switch ($data_type) {
206 case self::PARAM_INT:
207 if (!is_int($value)) {
208 throw new InvalidArgumentException('$value is not an integer as expected: ' . $value, 1281868686);
209 }
210 break;
211 case self::PARAM_BOOL:
212 if (!is_bool($value)) {
213 throw new InvalidArgumentException('$value is not a boolean as expected: ' . $value, 1281868687);
214 }
215 break;
216 case self::PARAM_NULL:
217 if (!is_null($value)) {
218 throw new InvalidArgumentException('$value is not NULL as expected: ' . $value, 1282489834);
219 }
220 break;
221 }
222
223 $key = is_int($parameter) ? $parameter - 1 : $parameter;
224 $this->parameters[$key] = array(
225 'value' => $value,
226 'type' => ($data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type),
227 );
228
229 return $this;
230 }
231
232 /**
233 * Executes the prepared statement. If the prepared statement included parameter
234 * markers, you must either:
235 * <ul>
236 * <li>call {@link t3lib_db_PreparedStatement::bindParam()} to bind PHP variables
237 * to the parameter markers: bound variables pass their value as input</li>
238 * <li>or pass an array of input-only parameter values</li>
239 * </ul>
240 *
241 * $input_parameters behave as in {@link t3lib_db_PreparedStatement::bindParams()}
242 * and work for both named parameters and question mark parameters.
243 *
244 * Example 1:
245 * <code>
246 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
247 * $statement->execute(array('goofy', 'FIXED'));
248 * </code>
249 *
250 * Example 2:
251 * <code>
252 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
253 * $statement->execute(array(':nickname' => 'goofy', ':status' => 'FIXED'));
254 * </code>
255 *
256 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. The PHP type of each array value will be used to decide which PARAM_* type to use (int, string, boolean, null), so make sure your variables are properly casted, if needed.
257 * @return boolean Returns TRUE on success or FALSE on failure.
258 * @api
259 */
260 public function execute(array $input_parameters = array()) {
261 $query = $this->query;
262 $precompiledQueryParts = $this->precompiledQueryParts;
263 $parameterValues = $this->parameters;
264
265 if (count($input_parameters) > 0) {
266 $parameterValues = array();
267 foreach ($input_parameters as $key => $value) {
268 $parameterValues[$key] = array(
269 'value' => $value,
270 'type' => $this->guessValueType($value),
271 );
272 }
273 }
274
275 $this->replaceValuesInQuery($query, $precompiledQueryParts, $parameterValues);
276 if (count($precompiledQueryParts) > 0) {
277 $query = implode('', $precompiledQueryParts['queryParts']);
278 }
279 $this->resource = $GLOBALS['TYPO3_DB']->exec_PREPAREDquery($query, $precompiledQueryParts);
280
281 // Empty binding parameters
282 $this->parameters = array();
283
284 // Return the success flag
285 return ($this->resource ? TRUE : FALSE);
286 }
287
288 /**
289 * Fetches a row from a result set associated with a t3lib_db_PreparedStatement object.
290 *
291 * @param integer $fetch_style Controls how the next row will be returned to the caller. This value must be one of the t3lib_db_PreparedStatement::FETCH_* constants. If omitted, default fetch mode for this prepared query will be used.
292 * @return array Array of rows or FALSE if there are no more rows.
293 * @api
294 */
295 public function fetch($fetch_style = 0) {
296 if ($fetch_style == 0) {
297 $fetch_style = $this->defaultFetchMode;
298 }
299 switch ($fetch_style) {
300 case self::FETCH_ASSOC:
301 $row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($this->resource);
302 break;
303 case self::FETCH_NUM:
304 $row = $GLOBALS['TYPO3_DB']->sql_fetch_row($this->resource);
305 break;
306 default:
307 throw new InvalidArgumentException('$fetch_style must be either t3lib_db_PreparedStatement::FETCH_ASSOC or t3lib_db_PreparedStatement::FETCH_NUM', 1281646455);
308 }
309 return $row;
310 }
311
312 /**
313 * Moves internal result pointer.
314 *
315 * @param integer $rowNumber Where to place the result pointer (0 = start)
316 * @return boolean Returns TRUE on success or FALSE on failure.
317 * @api
318 */
319 public function seek($rowNumber) {
320 return $GLOBALS['TYPO3_DB']->sql_data_seek($this->resource, intval($rowNumber));
321 }
322
323 /**
324 * Returns an array containing all of the result set rows.
325 *
326 * @param integer $fetch_style Controls the contents of the returned array as documented in {@link t3lib_db_PreparedStatement::fetch()}.
327 * @return array Array of rows.
328 * @api
329 */
330 public function fetchAll($fetch_style = 0) {
331 $rows = array();
332 while (($row = $this->fetch($fetch_style)) !== FALSE) {
333 $rows[] = $row;
334 }
335 return $rows;
336 }
337
338 /**
339 * Releases the cursor. Should always be call after having fetched rows from
340 * a query execution.
341 *
342 * @return void
343 * @api
344 */
345 public function free() {
346 $GLOBALS['TYPO3_DB']->sql_free_result($this->resource);
347 }
348
349 /**
350 * Returns the number of rows affected by the last SQL statement.
351 *
352 * @return integer The number of rows.
353 * @api
354 */
355 public function rowCount() {
356 return $GLOBALS['TYPO3_DB']->sql_num_rows($this->resource);
357 }
358
359 /**
360 * Returns the error number on the last execute() call.
361 *
362 * @return integer Driver specific error code.
363 * @api
364 */
365 public function errorCode() {
366 return $GLOBALS['TYPO3_DB']->sql_errno();
367 }
368
369 /**
370 * Returns an array of error information about the last operation performed by this statement handle.
371 * The array consists of the following fields:
372 * <ol start="0">
373 * <li>Driver specific error code.</li>
374 * <li>Driver specific error message</li>
375 * </ol>
376 *
377 * @return array Array of error information.
378 */
379 public function errorInfo() {
380 return array(
381 $GLOBALS['TYPO3_DB']->sql_errno(),
382 $GLOBALS['TYPO3_DB']->sql_error(),
383 );
384 }
385
386 /**
387 * Sets the default fetch mode for this prepared query.
388 *
389 * @param integer $mode One of the t3lib_db_PreparedStatement::FETCH_* constants
390 * @return void
391 * @api
392 */
393 public function setFetchMode($mode) {
394 switch ($mode) {
395 case self::FETCH_ASSOC:
396 case self::FETCH_NUM:
397 $this->defaultFetchMode = $mode;
398 break;
399 default:
400 throw new InvalidArgumentException('$mode must be either t3lib_db_PreparedStatement::FETCH_ASSOC or t3lib_db_PreparedStatement::FETCH_NUM', 1281875340);
401 }
402 }
403
404 /**
405 * Guesses the type of a given value.
406 *
407 * @param mixed $value
408 * @return integer One of the t3lib_db_PreparedStatement::PARAM_* constants
409 */
410 protected function guessValueType($value) {
411 if (is_bool($value)) {
412 $type = self::PARAM_BOOL;
413 } elseif (is_int($value)) {
414 $type = self::PARAM_INT;
415 } elseif (is_null($value)) {
416 $type = self::PARAM_NULL;
417 } else {
418 $type = self::PARAM_STR;
419 }
420
421 return $type;
422 }
423
424 /**
425 * Replaces values for each parameter in a query.
426 *
427 * @param string $query
428 * @param array $precompiledQueryParts
429 * @param array $parameterValues
430 * @return void
431 */
432 protected function replaceValuesInQuery(&$query, array &$precompiledQueryParts, array $parameterValues) {
433 foreach ($parameterValues as $key => $typeValue) {
434 switch ($typeValue['type']) {
435 case self::PARAM_NULL:
436 $value = 'NULL';
437 break;
438 case self::PARAM_INT:
439 $value = intval($typeValue['value']);
440 break;
441 case self::PARAM_STR:
442 $value = $GLOBALS['TYPO3_DB']->fullQuoteStr($typeValue['value'], $this->table);
443 break;
444 case self::PARAM_BOOL:
445 $value = $typeValue['value'] ? 1 : 0;
446 break;
447 default:
448 throw new InvalidArgumentException(
449 sprintf('Unknown type %s used for parameter %s.', $typeValue['type'], $key),
450 1281859196
451 );
452 }
453
454 if (is_int($key)) {
455 if (count($precompiledQueryParts['queryParts']) > 0) {
456 $precompiledQueryParts['queryParts'][2 * $key + 1] = $value;
457 } else {
458 $parts = explode('?', $query, 2);
459 $parts[0] .= $value;
460 $query = implode('', $parts);
461 }
462 } else {
463 if (!preg_match('/^:[\w]+$/', $key)) {
464 throw new InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
465 }
466
467 for ($i = 1; $i < count($precompiledQueryParts['queryParts']); $i++) {
468 if ($precompiledQueryParts['queryParts'][$i] === $key) {
469 $precompiledQueryParts['queryParts'][$i] = $value;
470 }
471 }
472 // Replace the marker (not preceeded by a word character or a ':' but
473 // followed by a word boundary)
474 $query = preg_replace('/(?<![\w:])' . $key . '\b/', $value, $query);
475 }
476 }
477 }
478 }
479
480
481 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']) {
482 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']);
483 }
484
485 ?>