/library/Zend/Db/Adapter/Mysqli.php
PHP | 549 lines | 283 code | 41 blank | 225 comment | 43 complexity | 42dd91a8f635d2a3769cc285857fd216 MD5 | raw file
Possible License(s): AGPL-1.0
1<?php
2/**
3 * Zend Framework
4 *
5 * LICENSE
6 *
7 * This source file is subject to the new BSD license that is bundled
8 * with this package in the file LICENSE.txt.
9 * It is also available through the world-wide-web at this URL:
10 * http://framework.zend.com/license/new-bsd
11 * If you did not receive a copy of the license and are unable to
12 * obtain it through the world-wide-web, please send an email
13 * to license@zend.com so we can send you a copy immediately.
14 *
15 * @category Zend
16 * @package Zend_Db
17 * @subpackage Adapter
18 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
19 * @license http://framework.zend.com/license/new-bsd New BSD License
20 * @version $Id: Mysqli.php 24594 2012-01-05 21:27:01Z matthew $
21 */
22
23
24/**
25 * @see Zend_Db_Adapter_Abstract
26 */
27require_once 'Zend/Db/Adapter/Abstract.php';
28
29/**
30 * @see Zend_Db_Profiler
31 */
32require_once 'Zend/Db/Profiler.php';
33
34/**
35 * @see Zend_Db_Select
36 */
37require_once 'Zend/Db/Select.php';
38
39/**
40 * @see Zend_Db_Statement_Mysqli
41 */
42require_once 'Zend/Db/Statement/Mysqli.php';
43
44
45/**
46 * @category Zend
47 * @package Zend_Db
48 * @subpackage Adapter
49 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
50 * @license http://framework.zend.com/license/new-bsd New BSD License
51 */
52class Zend_Db_Adapter_Mysqli extends Zend_Db_Adapter_Abstract
53{
54
55 /**
56 * Keys are UPPERCASE SQL datatypes or the constants
57 * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
58 *
59 * Values are:
60 * 0 = 32-bit integer
61 * 1 = 64-bit integer
62 * 2 = float or decimal
63 *
64 * @var array Associative array of datatypes to values 0, 1, or 2.
65 */
66 protected $_numericDataTypes = array(
67 Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
68 Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
69 Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
70 'INT' => Zend_Db::INT_TYPE,
71 'INTEGER' => Zend_Db::INT_TYPE,
72 'MEDIUMINT' => Zend_Db::INT_TYPE,
73 'SMALLINT' => Zend_Db::INT_TYPE,
74 'TINYINT' => Zend_Db::INT_TYPE,
75 'BIGINT' => Zend_Db::BIGINT_TYPE,
76 'SERIAL' => Zend_Db::BIGINT_TYPE,
77 'DEC' => Zend_Db::FLOAT_TYPE,
78 'DECIMAL' => Zend_Db::FLOAT_TYPE,
79 'DOUBLE' => Zend_Db::FLOAT_TYPE,
80 'DOUBLE PRECISION' => Zend_Db::FLOAT_TYPE,
81 'FIXED' => Zend_Db::FLOAT_TYPE,
82 'FLOAT' => Zend_Db::FLOAT_TYPE
83 );
84
85 /**
86 * @var Zend_Db_Statement_Mysqli
87 */
88 protected $_stmt = null;
89
90 /**
91 * Default class name for a DB statement.
92 *
93 * @var string
94 */
95 protected $_defaultStmtClass = 'Zend_Db_Statement_Mysqli';
96
97 /**
98 * Quote a raw string.
99 *
100 * @param mixed $value Raw string
101 *
102 * @return string Quoted string
103 */
104 protected function _quote($value)
105 {
106 if (is_int($value) || is_float($value)) {
107 return $value;
108 }
109 $this->_connect();
110 return "'" . $this->_connection->real_escape_string($value) . "'";
111 }
112
113 /**
114 * Returns the symbol the adapter uses for delimiting identifiers.
115 *
116 * @return string
117 */
118 public function getQuoteIdentifierSymbol()
119 {
120 return "`";
121 }
122
123 /**
124 * Returns a list of the tables in the database.
125 *
126 * @return array
127 */
128 public function listTables()
129 {
130 $result = array();
131 // Use mysqli extension API, because SHOW doesn't work
132 // well as a prepared statement on MySQL 4.1.
133 $sql = 'SHOW TABLES';
134 if ($queryResult = $this->getConnection()->query($sql)) {
135 while ($row = $queryResult->fetch_row()) {
136 $result[] = $row[0];
137 }
138 $queryResult->close();
139 } else {
140 /**
141 * @see Zend_Db_Adapter_Mysqli_Exception
142 */
143 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
144 throw new Zend_Db_Adapter_Mysqli_Exception($this->getConnection()->error);
145 }
146 return $result;
147 }
148
149 /**
150 * Returns the column descriptions for a table.
151 *
152 * The return value is an associative array keyed by the column name,
153 * as returned by the RDBMS.
154 *
155 * The value of each array element is an associative array
156 * with the following keys:
157 *
158 * SCHEMA_NAME => string; name of database or schema
159 * TABLE_NAME => string;
160 * COLUMN_NAME => string; column name
161 * COLUMN_POSITION => number; ordinal position of column in table
162 * DATA_TYPE => string; SQL datatype name of column
163 * DEFAULT => string; default expression of column, null if none
164 * NULLABLE => boolean; true if column can have nulls
165 * LENGTH => number; length of CHAR/VARCHAR
166 * SCALE => number; scale of NUMERIC/DECIMAL
167 * PRECISION => number; precision of NUMERIC/DECIMAL
168 * UNSIGNED => boolean; unsigned property of an integer type
169 * PRIMARY => boolean; true if column is part of the primary key
170 * PRIMARY_POSITION => integer; position of column in primary key
171 * IDENTITY => integer; true if column is auto-generated with unique values
172 *
173 * @param string $tableName
174 * @param string $schemaName OPTIONAL
175 * @return array
176 */
177 public function describeTable($tableName, $schemaName = null)
178 {
179 /**
180 * @todo use INFORMATION_SCHEMA someday when
181 * MySQL's implementation isn't too slow.
182 */
183
184 if ($schemaName) {
185 $sql = 'DESCRIBE ' . $this->quoteIdentifier("$schemaName.$tableName", true);
186 } else {
187 $sql = 'DESCRIBE ' . $this->quoteIdentifier($tableName, true);
188 }
189
190 /**
191 * Use mysqli extension API, because DESCRIBE doesn't work
192 * well as a prepared statement on MySQL 4.1.
193 */
194 if ($queryResult = $this->getConnection()->query($sql)) {
195 while ($row = $queryResult->fetch_assoc()) {
196 $result[] = $row;
197 }
198 $queryResult->close();
199 } else {
200 /**
201 * @see Zend_Db_Adapter_Mysqli_Exception
202 */
203 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
204 throw new Zend_Db_Adapter_Mysqli_Exception($this->getConnection()->error);
205 }
206
207 $desc = array();
208
209 $row_defaults = array(
210 'Length' => null,
211 'Scale' => null,
212 'Precision' => null,
213 'Unsigned' => null,
214 'Primary' => false,
215 'PrimaryPosition' => null,
216 'Identity' => false
217 );
218 $i = 1;
219 $p = 1;
220 foreach ($result as $key => $row) {
221 $row = array_merge($row_defaults, $row);
222 if (preg_match('/unsigned/', $row['Type'])) {
223 $row['Unsigned'] = true;
224 }
225 if (preg_match('/^((?:var)?char)\((\d+)\)/', $row['Type'], $matches)) {
226 $row['Type'] = $matches[1];
227 $row['Length'] = $matches[2];
228 } else if (preg_match('/^decimal\((\d+),(\d+)\)/', $row['Type'], $matches)) {
229 $row['Type'] = 'decimal';
230 $row['Precision'] = $matches[1];
231 $row['Scale'] = $matches[2];
232 } else if (preg_match('/^float\((\d+),(\d+)\)/', $row['Type'], $matches)) {
233 $row['Type'] = 'float';
234 $row['Precision'] = $matches[1];
235 $row['Scale'] = $matches[2];
236 } else if (preg_match('/^((?:big|medium|small|tiny)?int)\((\d+)\)/', $row['Type'], $matches)) {
237 $row['Type'] = $matches[1];
238 /**
239 * The optional argument of a MySQL int type is not precision
240 * or length; it is only a hint for display width.
241 */
242 }
243 if (strtoupper($row['Key']) == 'PRI') {
244 $row['Primary'] = true;
245 $row['PrimaryPosition'] = $p;
246 if ($row['Extra'] == 'auto_increment') {
247 $row['Identity'] = true;
248 } else {
249 $row['Identity'] = false;
250 }
251 ++$p;
252 }
253 $desc[$this->foldCase($row['Field'])] = array(
254 'SCHEMA_NAME' => null, // @todo
255 'TABLE_NAME' => $this->foldCase($tableName),
256 'COLUMN_NAME' => $this->foldCase($row['Field']),
257 'COLUMN_POSITION' => $i,
258 'DATA_TYPE' => $row['Type'],
259 'DEFAULT' => $row['Default'],
260 'NULLABLE' => (bool) ($row['Null'] == 'YES'),
261 'LENGTH' => $row['Length'],
262 'SCALE' => $row['Scale'],
263 'PRECISION' => $row['Precision'],
264 'UNSIGNED' => $row['Unsigned'],
265 'PRIMARY' => $row['Primary'],
266 'PRIMARY_POSITION' => $row['PrimaryPosition'],
267 'IDENTITY' => $row['Identity']
268 );
269 ++$i;
270 }
271 return $desc;
272 }
273
274 /**
275 * Creates a connection to the database.
276 *
277 * @return void
278 * @throws Zend_Db_Adapter_Mysqli_Exception
279 */
280 protected function _connect()
281 {
282 if ($this->_connection) {
283 return;
284 }
285
286 if (!extension_loaded('mysqli')) {
287 /**
288 * @see Zend_Db_Adapter_Mysqli_Exception
289 */
290 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
291 throw new Zend_Db_Adapter_Mysqli_Exception('The Mysqli extension is required for this adapter but the extension is not loaded');
292 }
293
294 if (isset($this->_config['port'])) {
295 $port = (integer) $this->_config['port'];
296 } else {
297 $port = null;
298 }
299
300 $this->_connection = mysqli_init();
301
302 if(!empty($this->_config['driver_options'])) {
303 foreach($this->_config['driver_options'] as $option=>$value) {
304 if(is_string($option)) {
305 // Suppress warnings here
306 // Ignore it if it's not a valid constant
307 $option = @constant(strtoupper($option));
308 if($option === null)
309 continue;
310 }
311 mysqli_options($this->_connection, $option, $value);
312 }
313 }
314
315 // Suppress connection warnings here.
316 // Throw an exception instead.
317 $_isConnected = @mysqli_real_connect(
318 $this->_connection,
319 $this->_config['host'],
320 $this->_config['username'],
321 $this->_config['password'],
322 $this->_config['dbname'],
323 $port
324 );
325
326 if ($_isConnected === false || mysqli_connect_errno()) {
327
328 $this->closeConnection();
329 /**
330 * @see Zend_Db_Adapter_Mysqli_Exception
331 */
332 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
333 throw new Zend_Db_Adapter_Mysqli_Exception(mysqli_connect_error());
334 }
335
336 if (!empty($this->_config['charset'])) {
337 mysqli_set_charset($this->_connection, $this->_config['charset']);
338 }
339 }
340
341 /**
342 * Test if a connection is active
343 *
344 * @return boolean
345 */
346 public function isConnected()
347 {
348 return ((bool) ($this->_connection instanceof mysqli));
349 }
350
351 /**
352 * Force the connection to close.
353 *
354 * @return void
355 */
356 public function closeConnection()
357 {
358 if ($this->isConnected()) {
359 $this->_connection->close();
360 }
361 $this->_connection = null;
362 }
363
364 /**
365 * Prepare a statement and return a PDOStatement-like object.
366 *
367 * @param string $sql SQL query
368 * @return Zend_Db_Statement_Mysqli
369 */
370 public function prepare($sql)
371 {
372 $this->_connect();
373 if ($this->_stmt) {
374 $this->_stmt->close();
375 }
376 $stmtClass = $this->_defaultStmtClass;
377 if (!class_exists($stmtClass)) {
378 require_once 'Zend/Loader.php';
379 Zend_Loader::loadClass($stmtClass);
380 }
381 $stmt = new $stmtClass($this, $sql);
382 if ($stmt === false) {
383 return false;
384 }
385 $stmt->setFetchMode($this->_fetchMode);
386 $this->_stmt = $stmt;
387 return $stmt;
388 }
389
390 /**
391 * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
392 *
393 * As a convention, on RDBMS brands that support sequences
394 * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
395 * from the arguments and returns the last id generated by that sequence.
396 * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
397 * returns the last value generated for such a column, and the table name
398 * argument is disregarded.
399 *
400 * MySQL does not support sequences, so $tableName and $primaryKey are ignored.
401 *
402 * @param string $tableName OPTIONAL Name of table.
403 * @param string $primaryKey OPTIONAL Name of primary key column.
404 * @return string
405 * @todo Return value should be int?
406 */
407 public function lastInsertId($tableName = null, $primaryKey = null)
408 {
409 $mysqli = $this->_connection;
410 return (string) $mysqli->insert_id;
411 }
412
413 /**
414 * Begin a transaction.
415 *
416 * @return void
417 */
418 protected function _beginTransaction()
419 {
420 $this->_connect();
421 $this->_connection->autocommit(false);
422 }
423
424 /**
425 * Commit a transaction.
426 *
427 * @return void
428 */
429 protected function _commit()
430 {
431 $this->_connect();
432 $this->_connection->commit();
433 $this->_connection->autocommit(true);
434 }
435
436 /**
437 * Roll-back a transaction.
438 *
439 * @return void
440 */
441 protected function _rollBack()
442 {
443 $this->_connect();
444 $this->_connection->rollback();
445 $this->_connection->autocommit(true);
446 }
447
448 /**
449 * Set the fetch mode.
450 *
451 * @param int $mode
452 * @return void
453 * @throws Zend_Db_Adapter_Mysqli_Exception
454 */
455 public function setFetchMode($mode)
456 {
457 switch ($mode) {
458 case Zend_Db::FETCH_LAZY:
459 case Zend_Db::FETCH_ASSOC:
460 case Zend_Db::FETCH_NUM:
461 case Zend_Db::FETCH_BOTH:
462 case Zend_Db::FETCH_NAMED:
463 case Zend_Db::FETCH_OBJ:
464 $this->_fetchMode = $mode;
465 break;
466 case Zend_Db::FETCH_BOUND: // bound to PHP variable
467 /**
468 * @see Zend_Db_Adapter_Mysqli_Exception
469 */
470 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
471 throw new Zend_Db_Adapter_Mysqli_Exception('FETCH_BOUND is not supported yet');
472 break;
473 default:
474 /**
475 * @see Zend_Db_Adapter_Mysqli_Exception
476 */
477 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
478 throw new Zend_Db_Adapter_Mysqli_Exception("Invalid fetch mode '$mode' specified");
479 }
480 }
481
482 /**
483 * Adds an adapter-specific LIMIT clause to the SELECT statement.
484 *
485 * @param string $sql
486 * @param int $count
487 * @param int $offset OPTIONAL
488 * @return string
489 */
490 public function limit($sql, $count, $offset = 0)
491 {
492 $count = intval($count);
493 if ($count <= 0) {
494 /**
495 * @see Zend_Db_Adapter_Mysqli_Exception
496 */
497 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
498 throw new Zend_Db_Adapter_Mysqli_Exception("LIMIT argument count=$count is not valid");
499 }
500
501 $offset = intval($offset);
502 if ($offset < 0) {
503 /**
504 * @see Zend_Db_Adapter_Mysqli_Exception
505 */
506 require_once 'Zend/Db/Adapter/Mysqli/Exception.php';
507 throw new Zend_Db_Adapter_Mysqli_Exception("LIMIT argument offset=$offset is not valid");
508 }
509
510 $sql .= " LIMIT $count";
511 if ($offset > 0) {
512 $sql .= " OFFSET $offset";
513 }
514
515 return $sql;
516 }
517
518 /**
519 * Check if the adapter supports real SQL parameters.
520 *
521 * @param string $type 'positional' or 'named'
522 * @return bool
523 */
524 public function supportsParameters($type)
525 {
526 switch ($type) {
527 case 'positional':
528 return true;
529 case 'named':
530 default:
531 return false;
532 }
533 }
534
535 /**
536 * Retrieve server version in PHP style
537 *
538 *@return string
539 */
540 public function getServerVersion()
541 {
542 $this->_connect();
543 $version = $this->_connection->server_version;
544 $major = (int) ($version / 10000);
545 $minor = (int) ($version % 10000 / 100);
546 $revision = (int) ($version % 100);
547 return $major . '.' . $minor . '.' . $revision;
548 }
549}