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: }
550: