1: <?php
  2: /**
  3:  * MySQL layer for DBO
  4:  *
  5:  * PHP 5
  6:  *
  7:  * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
  8:  * Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
  9:  *
 10:  * Licensed under The MIT License
 11:  * Redistributions of files must retain the above copyright notice.
 12:  *
 13:  * @copyright     Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
 14:  * @link          http://cakephp.org CakePHP(tm) Project
 15:  * @package       Cake.Model.Datasource.Database
 16:  * @since         CakePHP(tm) v 0.10.5.1790
 17:  * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 18:  */
 19: 
 20: App::uses('DboSource', 'Model/Datasource');
 21: 
 22: /**
 23:  * MySQL DBO driver object
 24:  *
 25:  * Provides connection and SQL generation for MySQL RDMS
 26:  *
 27:  * @package       Cake.Model.Datasource.Database
 28:  */
 29: class Mysql extends DboSource {
 30: 
 31: /**
 32:  * Datasource description
 33:  *
 34:  * @var string
 35:  */
 36:     public $description = "MySQL DBO Driver";
 37: 
 38: /**
 39:  * Base configuration settings for MySQL driver
 40:  *
 41:  * @var array
 42:  */
 43:     protected $_baseConfig = array(
 44:         'persistent' => true,
 45:         'host' => 'localhost',
 46:         'login' => 'root',
 47:         'password' => '',
 48:         'database' => 'cake',
 49:         'port' => '3306'
 50:     );
 51: 
 52: /**
 53:  * Reference to the PDO object connection
 54:  *
 55:  * @var PDO $_connection
 56:  */
 57:     protected $_connection = null;
 58: 
 59: /**
 60:  * Start quote
 61:  *
 62:  * @var string
 63:  */
 64:     public $startQuote = "`";
 65: 
 66: /**
 67:  * End quote
 68:  *
 69:  * @var string
 70:  */
 71:     public $endQuote = "`";
 72: 
 73: /**
 74:  * use alias for update and delete. Set to true if version >= 4.1
 75:  *
 76:  * @var boolean
 77:  */
 78:     protected $_useAlias = true;
 79: 
 80: /**
 81:  * Index of basic SQL commands
 82:  *
 83:  * @var array
 84:  */
 85:     protected $_commands = array(
 86:         'begin'    => 'START TRANSACTION',
 87:         'commit'   => 'COMMIT',
 88:         'rollback' => 'ROLLBACK'
 89:     );
 90: 
 91: /**
 92:  * List of engine specific additional field parameters used on table creating
 93:  *
 94:  * @var array
 95:  */
 96:     public $fieldParameters = array(
 97:         'charset' => array('value' => 'CHARACTER SET', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault'),
 98:         'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => ' ', 'column' => 'Collation', 'position' => 'beforeDefault'),
 99:         'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => ' ', 'column' => 'Comment', 'position' => 'afterDefault')
100:     );
101: 
102: /**
103:  * List of table engine specific parameters used on table creating
104:  *
105:  * @var array
106:  */
107:     public $tableParameters = array(
108:         'charset' => array('value' => 'DEFAULT CHARSET', 'quote' => false, 'join' => '=', 'column' => 'charset'),
109:         'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => '=', 'column' => 'Collation'),
110:         'engine' => array('value' => 'ENGINE', 'quote' => false, 'join' => '=', 'column' => 'Engine')
111:     );
112: 
113: /**
114:  * MySQL column definition
115:  *
116:  * @var array
117:  */
118:     public $columns = array(
119:         'primary_key' => array('name' => 'NOT NULL AUTO_INCREMENT'),
120:         'string' => array('name' => 'varchar', 'limit' => '255'),
121:         'text' => array('name' => 'text'),
122:         'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),
123:         'float' => array('name' => 'float', 'formatter' => 'floatval'),
124:         'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
125:         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
126:         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
127:         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
128:         'binary' => array('name' => 'blob'),
129:         'boolean' => array('name' => 'tinyint', 'limit' => '1')
130:     );
131: 
132: /**
133:  * Connects to the database using options in the given configuration array.
134:  *
135:  * @return boolean True if the database could be connected, else false
136:  * @throws MissingConnectionException
137:  */
138:     public function connect() {
139:         $config = $this->config;
140:         $this->connected = false;
141:         try {
142:             $flags = array(
143:                 PDO::ATTR_PERSISTENT => $config['persistent'],
144:                 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
145:                 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
146:             );
147:             if (!empty($config['encoding'])) {
148:                 $flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding'];
149:             }
150:             if (empty($config['unix_socket'])) {
151:                 $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']}";
152:             } else {
153:                 $dsn = "mysql:unix_socket={$config['unix_socket']};dbname={$config['database']}";
154:             }
155:             $this->_connection = new PDO(
156:                 $dsn,
157:                 $config['login'],
158:                 $config['password'],
159:                 $flags
160:             );
161:             $this->connected = true;
162:         } catch (PDOException $e) {
163:             throw new MissingConnectionException(array('class' => $e->getMessage()));
164:         }
165: 
166:         $this->_useAlias = (bool)version_compare($this->getVersion(), "4.1", ">=");
167: 
168:         return $this->connected;
169:     }
170: 
171: /**
172:  * Check whether the MySQL extension is installed/loaded
173:  *
174:  * @return boolean
175:  */
176:     public function enabled() {
177:         return in_array('mysql', PDO::getAvailableDrivers());
178:     }
179: 
180: /**
181:  * Returns an array of sources (tables) in the database.
182:  *
183:  * @param mixed $data
184:  * @return array Array of table names in the database
185:  */
186:     public function listSources($data = null) {
187:         $cache = parent::listSources();
188:         if ($cache != null) {
189:             return $cache;
190:         }
191:         $result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']));
192: 
193:         if (!$result) {
194:             $result->closeCursor();
195:             return array();
196:         } else {
197:             $tables = array();
198: 
199:             while ($line = $result->fetch()) {
200:                 $tables[] = $line[0];
201:             }
202: 
203:             $result->closeCursor();
204:             parent::listSources($tables);
205:             return $tables;
206:         }
207:     }
208: 
209: /**
210:  * Builds a map of the columns contained in a result
211:  *
212:  * @param PDOStatement $results
213:  * @return void
214:  */
215:     public function resultSet($results) {
216:         $this->map = array();
217:         $numFields = $results->columnCount();
218:         $index = 0;
219: 
220:         while ($numFields-- > 0) {
221:             $column = $results->getColumnMeta($index);
222:             if (empty($column['native_type'])) {
223:                 $type = ($column['len'] == 1) ? 'boolean' : 'string';
224:             } else {
225:                 $type = $column['native_type'];
226:             }
227:             if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
228:                 $this->map[$index++] = array($column['table'], $column['name'], $type);
229:             } else {
230:                 $this->map[$index++] = array(0, $column['name'], $type);
231:             }
232:         }
233:     }
234: 
235: /**
236:  * Fetches the next row from the current result set
237:  *
238:  * @return mixed array with results fetched and mapped to column names or false if there is no results left to fetch
239:  */
240:     public function fetchResult() {
241:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
242:             $resultRow = array();
243:             foreach ($this->map as $col => $meta) {
244:                 list($table, $column, $type) = $meta;
245:                 $resultRow[$table][$column] = $row[$col];
246:                 if ($type === 'boolean' && $row[$col] !== null) {
247:                     $resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]);
248:                 }
249:             }
250:             return $resultRow;
251:         }
252:         $this->_result->closeCursor();
253:         return false;
254:     }
255: 
256: /**
257:  * Gets the database encoding
258:  *
259:  * @return string The database encoding
260:  */
261:     public function getEncoding() {
262:         return $this->_execute('SHOW VARIABLES LIKE ?', array('character_set_client'))->fetchObject()->Value;
263:     }
264: 
265: /**
266:  * Gets the version string of the database server
267:  *
268:  * @return string The database encoding
269:  */
270:     public function getVersion() {
271:         return $this->_connection->getAttribute(PDO::ATTR_SERVER_VERSION);
272:     }
273: 
274: /**
275:  * Query charset by collation
276:  *
277:  * @param string $name Collation name
278:  * @return string Character set name
279:  */
280:     public function getCharsetName($name) {
281:         if ((bool)version_compare($this->getVersion(), "5", ">=")) {
282:             $r = $this->_execute('SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = ?', array($name));
283:             $cols = $r->fetch();
284: 
285:             if (isset($cols['CHARACTER_SET_NAME'])) {
286:                 return $cols['CHARACTER_SET_NAME'];
287:             }
288:         }
289:         return false;
290:     }
291: 
292: /**
293:  * Returns an array of the fields in given table name.
294:  *
295:  * @param Model|string $model Name of database table to inspect or model instance
296:  * @return array Fields in table. Keys are name and type
297:  * @throws CakeException
298:  */
299:     public function describe($model) {
300:         $cache = parent::describe($model);
301:         if ($cache != null) {
302:             return $cache;
303:         }
304:         $table = $this->fullTableName($model);
305: 
306:         $fields = false;
307:         $cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table);
308:         if (!$cols) {
309:             throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
310:         }
311: 
312:         foreach ($cols as $column) {
313:             $fields[$column->Field] = array(
314:                 'type' => $this->column($column->Type),
315:                 'null' => ($column->Null === 'YES' ? true : false),
316:                 'default' => $column->Default,
317:                 'length' => $this->length($column->Type),
318:             );
319:             if (!empty($column->Key) && isset($this->index[$column->Key])) {
320:                 $fields[$column->Field]['key'] = $this->index[$column->Key];
321:             }
322:             foreach ($this->fieldParameters as $name => $value) {
323:                 if (!empty($column->{$value['column']})) {
324:                     $fields[$column->Field][$name] = $column->{$value['column']};
325:                 }
326:             }
327:             if (isset($fields[$column->Field]['collate'])) {
328:                 $charset = $this->getCharsetName($fields[$column->Field]['collate']);
329:                 if ($charset) {
330:                     $fields[$column->Field]['charset'] = $charset;
331:                 }
332:             }
333:         }
334:         $this->_cacheDescription($this->fullTableName($model, false), $fields);
335:         $cols->closeCursor();
336:         return $fields;
337:     }
338: 
339: /**
340:  * Generates and executes an SQL UPDATE statement for given model, fields, and values.
341:  *
342:  * @param Model $model
343:  * @param array $fields
344:  * @param array $values
345:  * @param mixed $conditions
346:  * @return array
347:  */
348:     public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
349:         if (!$this->_useAlias) {
350:             return parent::update($model, $fields, $values, $conditions);
351:         }
352: 
353:         if ($values == null) {
354:             $combined = $fields;
355:         } else {
356:             $combined = array_combine($fields, $values);
357:         }
358: 
359:         $alias = $joins = false;
360:         $fields = $this->_prepareUpdateFields($model, $combined, empty($conditions), !empty($conditions));
361:         $fields = implode(', ', $fields);
362:         $table = $this->fullTableName($model);
363: 
364:         if (!empty($conditions)) {
365:             $alias = $this->name($model->alias);
366:             if ($model->name == $model->alias) {
367:                 $joins = implode(' ', $this->_getJoins($model));
368:             }
369:         }
370:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
371: 
372:         if ($conditions === false) {
373:             return false;
374:         }
375: 
376:         if (!$this->execute($this->renderStatement('update', compact('table', 'alias', 'joins', 'fields', 'conditions')))) {
377:             $model->onError();
378:             return false;
379:         }
380:         return true;
381:     }
382: 
383: /**
384:  * Generates and executes an SQL DELETE statement for given id/conditions on given model.
385:  *
386:  * @param Model $model
387:  * @param mixed $conditions
388:  * @return boolean Success
389:  */
390:     public function delete(Model $model, $conditions = null) {
391:         if (!$this->_useAlias) {
392:             return parent::delete($model, $conditions);
393:         }
394:         $alias = $this->name($model->alias);
395:         $table = $this->fullTableName($model);
396:         $joins = implode(' ', $this->_getJoins($model));
397: 
398:         if (empty($conditions)) {
399:             $alias = $joins = false;
400:         }
401:         $complexConditions = false;
402:         foreach ((array)$conditions as $key => $value) {
403:             if (strpos($key, $model->alias) === false) {
404:                 $complexConditions = true;
405:                 break;
406:             }
407:         }
408:         if (!$complexConditions) {
409:             $joins = false;
410:         }
411: 
412:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
413:         if ($conditions === false) {
414:             return false;
415:         }
416:         if ($this->execute($this->renderStatement('delete', compact('alias', 'table', 'joins', 'conditions'))) === false) {
417:             $model->onError();
418:             return false;
419:         }
420:         return true;
421:     }
422: 
423: /**
424:  * Sets the database encoding
425:  *
426:  * @param string $enc Database encoding
427:  * @return boolean
428:  */
429:     public function setEncoding($enc) {
430:         return $this->_execute('SET NAMES ' . $enc) !== false;
431:     }
432: 
433: /**
434:  * Returns an array of the indexes in given datasource name.
435:  *
436:  * @param string $model Name of model to inspect
437:  * @return array Fields in table. Keys are column and unique
438:  */
439:     public function index($model) {
440:         $index = array();
441:         $table = $this->fullTableName($model);
442:         $old = version_compare($this->getVersion(), '4.1', '<=');
443:         if ($table) {
444:             $indices = $this->_execute('SHOW INDEX FROM ' . $table);
445:             while ($idx = $indices->fetch()) {
446:                 if ($old) {
447:                     $idx = (object) current((array)$idx);
448:                 }
449:                 if (!isset($index[$idx->Key_name]['column'])) {
450:                     $col = array();
451:                     $index[$idx->Key_name]['column'] = $idx->Column_name;
452:                     $index[$idx->Key_name]['unique'] = intval($idx->Non_unique == 0);
453:                 } else {
454:                     if (!empty($index[$idx->Key_name]['column']) && !is_array($index[$idx->Key_name]['column'])) {
455:                         $col[] = $index[$idx->Key_name]['column'];
456:                     }
457:                     $col[] = $idx->Column_name;
458:                     $index[$idx->Key_name]['column'] = $col;
459:                 }
460:             }
461:             $indices->closeCursor();
462:         }
463:         return $index;
464:     }
465: 
466: /**
467:  * Generate a MySQL Alter Table syntax for the given Schema comparison
468:  *
469:  * @param array $compare Result of a CakeSchema::compare()
470:  * @param string $table
471:  * @return array Array of alter statements to make.
472:  */
473:     public function alterSchema($compare, $table = null) {
474:         if (!is_array($compare)) {
475:             return false;
476:         }
477:         $out = '';
478:         $colList = array();
479:         foreach ($compare as $curTable => $types) {
480:             $indexes = $tableParameters = $colList = array();
481:             if (!$table || $table == $curTable) {
482:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
483:                 foreach ($types as $type => $column) {
484:                     if (isset($column['indexes'])) {
485:                         $indexes[$type] = $column['indexes'];
486:                         unset($column['indexes']);
487:                     }
488:                     if (isset($column['tableParameters'])) {
489:                         $tableParameters[$type] = $column['tableParameters'];
490:                         unset($column['tableParameters']);
491:                     }
492:                     switch ($type) {
493:                         case 'add':
494:                             foreach ($column as $field => $col) {
495:                                 $col['name'] = $field;
496:                                 $alter = 'ADD ' . $this->buildColumn($col);
497:                                 if (isset($col['after'])) {
498:                                     $alter .= ' AFTER ' . $this->name($col['after']);
499:                                 }
500:                                 $colList[] = $alter;
501:                             }
502:                         break;
503:                         case 'drop':
504:                             foreach ($column as $field => $col) {
505:                                 $col['name'] = $field;
506:                                 $colList[] = 'DROP ' . $this->name($field);
507:                             }
508:                         break;
509:                         case 'change':
510:                             foreach ($column as $field => $col) {
511:                                 if (!isset($col['name'])) {
512:                                     $col['name'] = $field;
513:                                 }
514:                                 $colList[] = 'CHANGE ' . $this->name($field) . ' ' . $this->buildColumn($col);
515:                             }
516:                         break;
517:                     }
518:                 }
519:                 $colList = array_merge($colList, $this->_alterIndexes($curTable, $indexes));
520:                 $colList = array_merge($colList, $this->_alterTableParameters($curTable, $tableParameters));
521:                 $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
522:             }
523:         }
524:         return $out;
525:     }
526: 
527: /**
528:  * Generate a MySQL "drop table" statement for the given Schema object
529:  *
530:  * @param CakeSchema $schema An instance of a subclass of CakeSchema
531:  * @param string $table Optional.  If specified only the table name given will be generated.
532:  *                      Otherwise, all tables defined in the schema are generated.
533:  * @return string
534:  */
535:     public function dropSchema(CakeSchema $schema, $table = null) {
536:         $out = '';
537:         foreach ($schema->tables as $curTable => $columns) {
538:             if (!$table || $table === $curTable) {
539:                 $out .= 'DROP TABLE IF EXISTS ' . $this->fullTableName($curTable) . ";\n";
540:             }
541:         }
542:         return $out;
543:     }
544: 
545: /**
546:  * Generate MySQL table parameter alteration statements for a table.
547:  *
548:  * @param string $table Table to alter parameters for.
549:  * @param array $parameters Parameters to add & drop.
550:  * @return array Array of table property alteration statements.
551:  * @todo Implement this method.
552:  */
553:     protected function _alterTableParameters($table, $parameters) {
554:         if (isset($parameters['change'])) {
555:             return $this->buildTableParameters($parameters['change']);
556:         }
557:         return array();
558:     }
559: 
560: /**
561:  * Generate MySQL index alteration statements for a table.
562:  *
563:  * @param string $table Table to alter indexes for
564:  * @param array $indexes Indexes to add and drop
565:  * @return array Index alteration statements
566:  */
567:     protected function _alterIndexes($table, $indexes) {
568:         $alter = array();
569:         if (isset($indexes['drop'])) {
570:             foreach ($indexes['drop'] as $name => $value) {
571:                 $out = 'DROP ';
572:                 if ($name == 'PRIMARY') {
573:                     $out .= 'PRIMARY KEY';
574:                 } else {
575:                     $out .= 'KEY ' . $name;
576:                 }
577:                 $alter[] = $out;
578:             }
579:         }
580:         if (isset($indexes['add'])) {
581:             foreach ($indexes['add'] as $name => $value) {
582:                 $out = 'ADD ';
583:                 if ($name == 'PRIMARY') {
584:                     $out .= 'PRIMARY ';
585:                     $name = null;
586:                 } else {
587:                     if (!empty($value['unique'])) {
588:                         $out .= 'UNIQUE ';
589:                     }
590:                 }
591:                 if (is_array($value['column'])) {
592:                     $out .= 'KEY '. $name .' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
593:                 } else {
594:                     $out .= 'KEY '. $name .' (' . $this->name($value['column']) . ')';
595:                 }
596:                 $alter[] = $out;
597:             }
598:         }
599:         return $alter;
600:     }
601: 
602: /**
603:  * Returns an detailed array of sources (tables) in the database.
604:  *
605:  * @param string $name Table name to get parameters
606:  * @return array Array of table names in the database
607:  */
608:     public function listDetailedSources($name = null) {
609:         $condition = '';
610:         $params = array();
611:         if (is_string($name)) {
612:                 $condition = ' WHERE name = ' . $this->value($name);
613:                 $params = array($name);
614:         }
615:         $result = $this->_connection->query('SHOW TABLE STATUS ' . $condition, PDO::FETCH_ASSOC);
616: 
617:         if (!$result) {
618:                 $result->closeCursor();
619:                 return array();
620:         } else {
621:                 $tables = array();
622:                 foreach ($result as $row) {
623:                         $tables[$row['Name']] = (array) $row;
624:                         unset($tables[$row['Name']]['queryString']);
625:                         if (!empty($row['Collation'])) {
626:                                 $charset = $this->getCharsetName($row['Collation']);
627:                                 if ($charset) {
628:                                         $tables[$row['Name']]['charset'] = $charset;
629:                                 }
630:                         }
631:                 }
632:                 $result->closeCursor();
633:                 if (is_string($name) && isset($tables[$name])) {
634:                         return $tables[$name];
635:                 }
636:                 return $tables;
637:         }
638:     }
639: 
640: 
641: /**
642:  * Converts database-layer column types to basic types
643:  *
644:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
645:  * @return string Abstract column type (i.e. "string")
646:  */
647:     public function column($real) {
648:         if (is_array($real)) {
649:             $col = $real['name'];
650:             if (isset($real['limit'])) {
651:                 $col .= '(' . $real['limit'] . ')';
652:             }
653:             return $col;
654:         }
655: 
656:         $col = str_replace(')', '', $real);
657:         $limit = $this->length($real);
658:         if (strpos($col, '(') !== false) {
659:             list($col, $vals) = explode('(', $col);
660:         }
661: 
662:         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
663:             return $col;
664:         }
665:         if (($col === 'tinyint' && $limit == 1) || $col === 'boolean') {
666:             return 'boolean';
667:         }
668:         if (strpos($col, 'int') !== false) {
669:             return 'integer';
670:         }
671:         if (strpos($col, 'char') !== false || $col === 'tinytext') {
672:             return 'string';
673:         }
674:         if (strpos($col, 'text') !== false) {
675:             return 'text';
676:         }
677:         if (strpos($col, 'blob') !== false || $col === 'binary') {
678:             return 'binary';
679:         }
680:         if (strpos($col, 'float') !== false || strpos($col, 'double') !== false || strpos($col, 'decimal') !== false) {
681:             return 'float';
682:         }
683:         if (strpos($col, 'enum') !== false) {
684:             return "enum($vals)";
685:         }
686:         return 'text';
687:     }
688: }
689: