|
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-2010 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 20096 2010-01-06 02:05:09Z bkarwin $ |
|
21 */ |
|
22 |
|
23 |
|
24 /** |
|
25 * @see Zend_Db_Adapter_Abstract |
|
26 */ |
|
27 require_once 'Zend/Db/Adapter/Abstract.php'; |
|
28 |
|
29 /** |
|
30 * @see Zend_Db_Profiler |
|
31 */ |
|
32 require_once 'Zend/Db/Profiler.php'; |
|
33 |
|
34 /** |
|
35 * @see Zend_Db_Select |
|
36 */ |
|
37 require_once 'Zend/Db/Select.php'; |
|
38 |
|
39 /** |
|
40 * @see Zend_Db_Statement_Mysqli |
|
41 */ |
|
42 require_once 'Zend/Db/Statement/Mysqli.php'; |
|
43 |
|
44 |
|
45 /** |
|
46 * @category Zend |
|
47 * @package Zend_Db |
|
48 * @subpackage Adapter |
|
49 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
50 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
51 */ |
|
52 class 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 } |