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-2012, 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-2012, 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(PDO::FETCH_NUM)) {
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(PDO::FETCH_ASSOC);
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:         $key = $this->fullTableName($model, false);
301:         $cache = parent::describe($key);
302:         if ($cache != null) {
303:             return $cache;
304:         }
305:         $table = $this->fullTableName($model);
306: 
307:         $fields = false;
308:         $cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table);
309:         if (!$cols) {
310:             throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
311:         }
312: 
313:         while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
314:             $fields[$column->Field] = array(
315:                 'type' => $this->column($column->Type),
316:                 'null' => ($column->Null === 'YES' ? true : false),
317:                 'default' => $column->Default,
318:                 'length' => $this->length($column->Type),
319:             );
320:             if (!empty($column->Key) && isset($this->index[$column->Key])) {
321:                 $fields[$column->Field]['key'] = $this->index[$column->Key];
322:             }
323:             foreach ($this->fieldParameters as $name => $value) {
324:                 if (!empty($column->{$value['column']})) {
325:                     $fields[$column->Field][$name] = $column->{$value['column']};
326:                 }
327:             }
328:             if (isset($fields[$column->Field]['collate'])) {
329:                 $charset = $this->getCharsetName($fields[$column->Field]['collate']);
330:                 if ($charset) {
331:                     $fields[$column->Field]['charset'] = $charset;
332:                 }
333:             }
334:         }
335:         $this->_cacheDescription($key, $fields);
336:         $cols->closeCursor();
337:         return $fields;
338:     }
339: 
340: /**
341:  * Generates and executes an SQL UPDATE statement for given model, fields, and values.
342:  *
343:  * @param Model $model
344:  * @param array $fields
345:  * @param array $values
346:  * @param mixed $conditions
347:  * @return array
348:  */
349:     public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
350:         if (!$this->_useAlias) {
351:             return parent::update($model, $fields, $values, $conditions);
352:         }
353: 
354:         if ($values == null) {
355:             $combined = $fields;
356:         } else {
357:             $combined = array_combine($fields, $values);
358:         }
359: 
360:         $alias = $joins = false;
361:         $fields = $this->_prepareUpdateFields($model, $combined, empty($conditions), !empty($conditions));
362:         $fields = implode(', ', $fields);
363:         $table = $this->fullTableName($model);
364: 
365:         if (!empty($conditions)) {
366:             $alias = $this->name($model->alias);
367:             if ($model->name == $model->alias) {
368:                 $joins = implode(' ', $this->_getJoins($model));
369:             }
370:         }
371:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
372: 
373:         if ($conditions === false) {
374:             return false;
375:         }
376: 
377:         if (!$this->execute($this->renderStatement('update', compact('table', 'alias', 'joins', 'fields', 'conditions')))) {
378:             $model->onError();
379:             return false;
380:         }
381:         return true;
382:     }
383: 
384: /**
385:  * Generates and executes an SQL DELETE statement for given id/conditions on given model.
386:  *
387:  * @param Model $model
388:  * @param mixed $conditions
389:  * @return boolean Success
390:  */
391:     public function delete(Model $model, $conditions = null) {
392:         if (!$this->_useAlias) {
393:             return parent::delete($model, $conditions);
394:         }
395:         $alias = $this->name($model->alias);
396:         $table = $this->fullTableName($model);
397:         $joins = implode(' ', $this->_getJoins($model));
398: 
399:         if (empty($conditions)) {
400:             $alias = $joins = false;
401:         }
402:         $complexConditions = false;
403:         foreach ((array)$conditions as $key => $value) {
404:             if (strpos($key, $model->alias) === false) {
405:                 $complexConditions = true;
406:                 break;
407:             }
408:         }
409:         if (!$complexConditions) {
410:             $joins = false;
411:         }
412: 
413:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
414:         if ($conditions === false) {
415:             return false;
416:         }
417:         if ($this->execute($this->renderStatement('delete', compact('alias', 'table', 'joins', 'conditions'))) === false) {
418:             $model->onError();
419:             return false;
420:         }
421:         return true;
422:     }
423: 
424: /**
425:  * Sets the database encoding
426:  *
427:  * @param string $enc Database encoding
428:  * @return boolean
429:  */
430:     public function setEncoding($enc) {
431:         return $this->_execute('SET NAMES ' . $enc) !== false;
432:     }
433: 
434: /**
435:  * Returns an array of the indexes in given datasource name.
436:  *
437:  * @param string $model Name of model to inspect
438:  * @return array Fields in table. Keys are column and unique
439:  */
440:     public function index($model) {
441:         $index = array();
442:         $table = $this->fullTableName($model);
443:         $old = version_compare($this->getVersion(), '4.1', '<=');
444:         if ($table) {
445:             $indices = $this->_execute('SHOW INDEX FROM ' . $table);
446:             // @codingStandardsIgnoreStart
447:             // MySQL columns don't match the cakephp conventions.
448:             while ($idx = $indices->fetch(PDO::FETCH_OBJ)) {
449:                 if ($old) {
450:                     $idx = (object)current((array)$idx);
451:                 }
452:                 if (!isset($index[$idx->Key_name]['column'])) {
453:                     $col = array();
454:                     $index[$idx->Key_name]['column'] = $idx->Column_name;
455:                     $index[$idx->Key_name]['unique'] = intval($idx->Non_unique == 0);
456:                 } else {
457:                     if (!empty($index[$idx->Key_name]['column']) && !is_array($index[$idx->Key_name]['column'])) {
458:                         $col[] = $index[$idx->Key_name]['column'];
459:                     }
460:                     $col[] = $idx->Column_name;
461:                     $index[$idx->Key_name]['column'] = $col;
462:                 }
463:             }
464:             // @codingStandardsIgnoreEnd
465:             $indices->closeCursor();
466:         }
467:         return $index;
468:     }
469: 
470: /**
471:  * Generate a MySQL Alter Table syntax for the given Schema comparison
472:  *
473:  * @param array $compare Result of a CakeSchema::compare()
474:  * @param string $table
475:  * @return array Array of alter statements to make.
476:  */
477:     public function alterSchema($compare, $table = null) {
478:         if (!is_array($compare)) {
479:             return false;
480:         }
481:         $out = '';
482:         $colList = array();
483:         foreach ($compare as $curTable => $types) {
484:             $indexes = $tableParameters = $colList = array();
485:             if (!$table || $table == $curTable) {
486:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
487:                 foreach ($types as $type => $column) {
488:                     if (isset($column['indexes'])) {
489:                         $indexes[$type] = $column['indexes'];
490:                         unset($column['indexes']);
491:                     }
492:                     if (isset($column['tableParameters'])) {
493:                         $tableParameters[$type] = $column['tableParameters'];
494:                         unset($column['tableParameters']);
495:                     }
496:                     switch ($type) {
497:                         case 'add':
498:                             foreach ($column as $field => $col) {
499:                                 $col['name'] = $field;
500:                                 $alter = 'ADD ' . $this->buildColumn($col);
501:                                 if (isset($col['after'])) {
502:                                     $alter .= ' AFTER ' . $this->name($col['after']);
503:                                 }
504:                                 $colList[] = $alter;
505:                             }
506:                         break;
507:                         case 'drop':
508:                             foreach ($column as $field => $col) {
509:                                 $col['name'] = $field;
510:                                 $colList[] = 'DROP ' . $this->name($field);
511:                             }
512:                         break;
513:                         case 'change':
514:                             foreach ($column as $field => $col) {
515:                                 if (!isset($col['name'])) {
516:                                     $col['name'] = $field;
517:                                 }
518:                                 $colList[] = 'CHANGE ' . $this->name($field) . ' ' . $this->buildColumn($col);
519:                             }
520:                         break;
521:                     }
522:                 }
523:                 $colList = array_merge($colList, $this->_alterIndexes($curTable, $indexes));
524:                 $colList = array_merge($colList, $this->_alterTableParameters($curTable, $tableParameters));
525:                 $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
526:             }
527:         }
528:         return $out;
529:     }
530: 
531: /**
532:  * Generate a MySQL "drop table" statement for the given Schema object
533:  *
534:  * @param CakeSchema $schema An instance of a subclass of CakeSchema
535:  * @param string $table Optional.  If specified only the table name given will be generated.
536:  *                      Otherwise, all tables defined in the schema are generated.
537:  * @return string
538:  */
539:     public function dropSchema(CakeSchema $schema, $table = null) {
540:         $out = '';
541:         foreach ($schema->tables as $curTable => $columns) {
542:             if (!$table || $table === $curTable) {
543:                 $out .= 'DROP TABLE IF EXISTS ' . $this->fullTableName($curTable) . ";\n";
544:             }
545:         }
546:         return $out;
547:     }
548: 
549: /**
550:  * Generate MySQL table parameter alteration statements for a table.
551:  *
552:  * @param string $table Table to alter parameters for.
553:  * @param array $parameters Parameters to add & drop.
554:  * @return array Array of table property alteration statements.
555:  * @todo Implement this method.
556:  */
557:     protected function _alterTableParameters($table, $parameters) {
558:         if (isset($parameters['change'])) {
559:             return $this->buildTableParameters($parameters['change']);
560:         }
561:         return array();
562:     }
563: 
564: /**
565:  * Generate MySQL index alteration statements for a table.
566:  *
567:  * @param string $table Table to alter indexes for
568:  * @param array $indexes Indexes to add and drop
569:  * @return array Index alteration statements
570:  */
571:     protected function _alterIndexes($table, $indexes) {
572:         $alter = array();
573:         if (isset($indexes['drop'])) {
574:             foreach ($indexes['drop'] as $name => $value) {
575:                 $out = 'DROP ';
576:                 if ($name == 'PRIMARY') {
577:                     $out .= 'PRIMARY KEY';
578:                 } else {
579:                     $out .= 'KEY ' . $name;
580:                 }
581:                 $alter[] = $out;
582:             }
583:         }
584:         if (isset($indexes['add'])) {
585:             foreach ($indexes['add'] as $name => $value) {
586:                 $out = 'ADD ';
587:                 if ($name == 'PRIMARY') {
588:                     $out .= 'PRIMARY ';
589:                     $name = null;
590:                 } else {
591:                     if (!empty($value['unique'])) {
592:                         $out .= 'UNIQUE ';
593:                     }
594:                 }
595:                 if (is_array($value['column'])) {
596:                     $out .= 'KEY ' . $name . ' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
597:                 } else {
598:                     $out .= 'KEY ' . $name . ' (' . $this->name($value['column']) . ')';
599:                 }
600:                 $alter[] = $out;
601:             }
602:         }
603:         return $alter;
604:     }
605: 
606: /**
607:  * Returns an detailed array of sources (tables) in the database.
608:  *
609:  * @param string $name Table name to get parameters
610:  * @return array Array of table names in the database
611:  */
612:     public function listDetailedSources($name = null) {
613:         $condition = '';
614:         if (is_string($name)) {
615:             $condition = ' WHERE name = ' . $this->value($name);
616:         }
617:         $result = $this->_connection->query('SHOW TABLE STATUS ' . $condition, PDO::FETCH_ASSOC);
618: 
619:         if (!$result) {
620:             $result->closeCursor();
621:             return array();
622:         } else {
623:             $tables = array();
624:             foreach ($result as $row) {
625:                 $tables[$row['Name']] = (array)$row;
626:                 unset($tables[$row['Name']]['queryString']);
627:                 if (!empty($row['Collation'])) {
628:                     $charset = $this->getCharsetName($row['Collation']);
629:                     if ($charset) {
630:                         $tables[$row['Name']]['charset'] = $charset;
631:                     }
632:                 }
633:             }
634:             $result->closeCursor();
635:             if (is_string($name) && isset($tables[$name])) {
636:                 return $tables[$name];
637:             }
638:             return $tables;
639:         }
640:     }
641: 
642: /**
643:  * Converts database-layer column types to basic types
644:  *
645:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
646:  * @return string Abstract column type (i.e. "string")
647:  */
648:     public function column($real) {
649:         if (is_array($real)) {
650:             $col = $real['name'];
651:             if (isset($real['limit'])) {
652:                 $col .= '(' . $real['limit'] . ')';
653:             }
654:             return $col;
655:         }
656: 
657:         $col = str_replace(')', '', $real);
658:         $limit = $this->length($real);
659:         if (strpos($col, '(') !== false) {
660:             list($col, $vals) = explode('(', $col);
661:         }
662: 
663:         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
664:             return $col;
665:         }
666:         if (($col === 'tinyint' && $limit == 1) || $col === 'boolean') {
667:             return 'boolean';
668:         }
669:         if (strpos($col, 'int') !== false) {
670:             return 'integer';
671:         }
672:         if (strpos($col, 'char') !== false || $col === 'tinytext') {
673:             return 'string';
674:         }
675:         if (strpos($col, 'text') !== false) {
676:             return 'text';
677:         }
678:         if (strpos($col, 'blob') !== false || $col === 'binary') {
679:             return 'binary';
680:         }
681:         if (strpos($col, 'float') !== false || strpos($col, 'double') !== false || strpos($col, 'decimal') !== false) {
682:             return 'float';
683:         }
684:         if (strpos($col, 'enum') !== false) {
685:             return "enum($vals)";
686:         }
687:         return 'text';
688:     }
689: 
690: /**
691:  * Gets the schema name
692:  *
693:  * @return string The schema name
694:  */
695:     public function getSchemaName() {
696:         return $this->config['database'];
697:     }
698: 
699: }
700: