pictcode / lib / Cake / Model / Datasource / Database / Mysql.php @ 0b1b8047
履歴 | 表示 | アノテート | ダウンロード (23.533 KB)
| 1 | 635eef61 | spyder1211 | <?php
|
|---|---|---|---|
| 2 | /**
|
||
| 3 | * MySQL layer for DBO
|
||
| 4 | *
|
||
| 5 | * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
|
||
| 6 | * Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
|
||
| 7 | *
|
||
| 8 | * Licensed under The MIT License
|
||
| 9 | * For full copyright and license information, please see the LICENSE.txt
|
||
| 10 | * Redistributions of files must retain the above copyright notice.
|
||
| 11 | *
|
||
| 12 | * @copyright Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
|
||
| 13 | * @link http://cakephp.org CakePHP(tm) Project
|
||
| 14 | * @package Cake.Model.Datasource.Database
|
||
| 15 | * @since CakePHP(tm) v 0.10.5.1790
|
||
| 16 | * @license http://www.opensource.org/licenses/mit-license.php MIT License
|
||
| 17 | */
|
||
| 18 | |||
| 19 | App::uses('DboSource', 'Model/Datasource'); |
||
| 20 | |||
| 21 | /**
|
||
| 22 | * MySQL DBO driver object
|
||
| 23 | *
|
||
| 24 | * Provides connection and SQL generation for MySQL RDMS
|
||
| 25 | *
|
||
| 26 | * @package Cake.Model.Datasource.Database
|
||
| 27 | */
|
||
| 28 | class Mysql extends DboSource { |
||
| 29 | |||
| 30 | /**
|
||
| 31 | * Datasource description
|
||
| 32 | *
|
||
| 33 | * @var string
|
||
| 34 | */
|
||
| 35 | public $description = "MySQL DBO Driver"; |
||
| 36 | |||
| 37 | /**
|
||
| 38 | * Base configuration settings for MySQL driver
|
||
| 39 | *
|
||
| 40 | * @var array
|
||
| 41 | */
|
||
| 42 | protected $_baseConfig = array( |
||
| 43 | 'persistent' => true, |
||
| 44 | 'host' => 'localhost', |
||
| 45 | 'login' => 'root', |
||
| 46 | 'password' => '', |
||
| 47 | 'database' => 'cake', |
||
| 48 | 'port' => '3306', |
||
| 49 | 'flags' => array() |
||
| 50 | ); |
||
| 51 | |||
| 52 | /**
|
||
| 53 | * Reference to the PDO object connection
|
||
| 54 | *
|
||
| 55 | * @var PDO
|
||
| 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 bool
|
||
| 77 | */
|
||
| 78 | protected $_useAlias = true; |
||
| 79 | |||
| 80 | /**
|
||
| 81 | * List of engine specific additional field parameters used on table creating
|
||
| 82 | *
|
||
| 83 | * @var array
|
||
| 84 | */
|
||
| 85 | public $fieldParameters = array( |
||
| 86 | 'charset' => array('value' => 'CHARACTER SET', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault'), |
||
| 87 | 'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => ' ', 'column' => 'Collation', 'position' => 'beforeDefault'), |
||
| 88 | 'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => ' ', 'column' => 'Comment', 'position' => 'afterDefault'), |
||
| 89 | 'unsigned' => array( |
||
| 90 | 'value' => 'UNSIGNED', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault', |
||
| 91 | 'noVal' => true, |
||
| 92 | 'options' => array(true), |
||
| 93 | 'types' => array('integer', 'float', 'decimal', 'biginteger') |
||
| 94 | ) |
||
| 95 | ); |
||
| 96 | |||
| 97 | /**
|
||
| 98 | * List of table engine specific parameters used on table creating
|
||
| 99 | *
|
||
| 100 | * @var array
|
||
| 101 | */
|
||
| 102 | public $tableParameters = array( |
||
| 103 | 'charset' => array('value' => 'DEFAULT CHARSET', 'quote' => false, 'join' => '=', 'column' => 'charset'), |
||
| 104 | 'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => '=', 'column' => 'Collation'), |
||
| 105 | 'engine' => array('value' => 'ENGINE', 'quote' => false, 'join' => '=', 'column' => 'Engine'), |
||
| 106 | 'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => '=', 'column' => 'Comment'), |
||
| 107 | ); |
||
| 108 | |||
| 109 | /**
|
||
| 110 | * MySQL column definition
|
||
| 111 | *
|
||
| 112 | * @var array
|
||
| 113 | */
|
||
| 114 | public $columns = array( |
||
| 115 | 'primary_key' => array('name' => 'NOT NULL AUTO_INCREMENT'), |
||
| 116 | 'string' => array('name' => 'varchar', 'limit' => '255'), |
||
| 117 | 'text' => array('name' => 'text'), |
||
| 118 | 'biginteger' => array('name' => 'bigint', 'limit' => '20'), |
||
| 119 | 'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'), |
||
| 120 | 'float' => array('name' => 'float', 'formatter' => 'floatval'), |
||
| 121 | 'decimal' => array('name' => 'decimal', 'formatter' => 'floatval'), |
||
| 122 | 'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'), |
||
| 123 | 'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'), |
||
| 124 | 'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'), |
||
| 125 | 'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'), |
||
| 126 | 'binary' => array('name' => 'blob'), |
||
| 127 | 'boolean' => array('name' => 'tinyint', 'limit' => '1') |
||
| 128 | ); |
||
| 129 | |||
| 130 | /**
|
||
| 131 | * Mapping of collation names to character set names
|
||
| 132 | *
|
||
| 133 | * @var array
|
||
| 134 | */
|
||
| 135 | protected $_charsets = array(); |
||
| 136 | |||
| 137 | /**
|
||
| 138 | * Connects to the database using options in the given configuration array.
|
||
| 139 | *
|
||
| 140 | * MySQL supports a few additional options that other drivers do not:
|
||
| 141 | *
|
||
| 142 | * - `unix_socket` Set to the path of the MySQL sock file. Can be used in place
|
||
| 143 | * of host + port.
|
||
| 144 | * - `ssl_key` SSL key file for connecting via SSL. Must be combined with `ssl_cert`.
|
||
| 145 | * - `ssl_cert` The SSL certificate to use when connecting via SSL. Must be
|
||
| 146 | * combined with `ssl_key`.
|
||
| 147 | * - `ssl_ca` The certificate authority for SSL connections.
|
||
| 148 | *
|
||
| 149 | * @return bool True if the database could be connected, else false
|
||
| 150 | * @throws MissingConnectionException
|
||
| 151 | */
|
||
| 152 | public function connect() { |
||
| 153 | $config = $this->config; |
||
| 154 | $this->connected = false; |
||
| 155 | |||
| 156 | $flags = $config['flags'] + array( |
||
| 157 | PDO::ATTR_PERSISTENT => $config['persistent'], |
||
| 158 | PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, |
||
| 159 | PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION |
||
| 160 | ); |
||
| 161 | |||
| 162 | if (!empty($config['encoding'])) { |
||
| 163 | $flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding']; |
||
| 164 | } |
||
| 165 | if (!empty($config['ssl_key']) && !empty($config['ssl_cert'])) { |
||
| 166 | $flags[PDO::MYSQL_ATTR_SSL_KEY] = $config['ssl_key']; |
||
| 167 | $flags[PDO::MYSQL_ATTR_SSL_CERT] = $config['ssl_cert']; |
||
| 168 | } |
||
| 169 | if (!empty($config['ssl_ca'])) { |
||
| 170 | $flags[PDO::MYSQL_ATTR_SSL_CA] = $config['ssl_ca']; |
||
| 171 | } |
||
| 172 | if (empty($config['unix_socket'])) { |
||
| 173 | $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']}"; |
||
| 174 | } else {
|
||
| 175 | $dsn = "mysql:unix_socket={$config['unix_socket']};dbname={$config['database']}"; |
||
| 176 | } |
||
| 177 | |||
| 178 | try {
|
||
| 179 | $this->_connection = new PDO( |
||
| 180 | $dsn,
|
||
| 181 | $config['login'], |
||
| 182 | $config['password'], |
||
| 183 | $flags
|
||
| 184 | ); |
||
| 185 | $this->connected = true; |
||
| 186 | if (!empty($config['settings'])) { |
||
| 187 | foreach ($config['settings'] as $key => $value) { |
||
| 188 | $this->_execute("SET $key=$value"); |
||
| 189 | } |
||
| 190 | } |
||
| 191 | } catch (PDOException $e) { |
||
| 192 | throw new MissingConnectionException(array( |
||
| 193 | 'class' => get_class($this), |
||
| 194 | 'message' => $e->getMessage() |
||
| 195 | )); |
||
| 196 | } |
||
| 197 | |||
| 198 | $this->_charsets = array(); |
||
| 199 | $this->_useAlias = (bool)version_compare($this->getVersion(), "4.1", ">="); |
||
| 200 | |||
| 201 | return $this->connected; |
||
| 202 | } |
||
| 203 | |||
| 204 | /**
|
||
| 205 | * Check whether the MySQL extension is installed/loaded
|
||
| 206 | *
|
||
| 207 | * @return bool
|
||
| 208 | */
|
||
| 209 | public function enabled() { |
||
| 210 | return in_array('mysql', PDO::getAvailableDrivers()); |
||
| 211 | } |
||
| 212 | |||
| 213 | /**
|
||
| 214 | * Returns an array of sources (tables) in the database.
|
||
| 215 | *
|
||
| 216 | * @param mixed $data List of tables.
|
||
| 217 | * @return array Array of table names in the database
|
||
| 218 | */
|
||
| 219 | public function listSources($data = null) { |
||
| 220 | $cache = parent::listSources(); |
||
| 221 | if ($cache) { |
||
| 222 | return $cache; |
||
| 223 | } |
||
| 224 | $result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database'])); |
||
| 225 | |||
| 226 | if (!$result) { |
||
| 227 | $result->closeCursor();
|
||
| 228 | return array(); |
||
| 229 | } |
||
| 230 | $tables = array(); |
||
| 231 | |||
| 232 | while ($line = $result->fetch(PDO::FETCH_NUM)) { |
||
| 233 | $tables[] = $line[0]; |
||
| 234 | } |
||
| 235 | |||
| 236 | $result->closeCursor();
|
||
| 237 | parent::listSources($tables); |
||
| 238 | return $tables; |
||
| 239 | } |
||
| 240 | |||
| 241 | /**
|
||
| 242 | * Builds a map of the columns contained in a result
|
||
| 243 | *
|
||
| 244 | * @param PDOStatement $results The results to format.
|
||
| 245 | * @return void
|
||
| 246 | */
|
||
| 247 | public function resultSet($results) { |
||
| 248 | $this->map = array(); |
||
| 249 | $numFields = $results->columnCount(); |
||
| 250 | $index = 0; |
||
| 251 | |||
| 252 | while ($numFields-- > 0) { |
||
| 253 | $column = $results->getColumnMeta($index); |
||
| 254 | if ($column['len'] === 1 && (empty($column['native_type']) || $column['native_type'] === 'TINY')) { |
||
| 255 | $type = 'boolean'; |
||
| 256 | } else {
|
||
| 257 | $type = empty($column['native_type']) ? 'string' : $column['native_type']; |
||
| 258 | } |
||
| 259 | if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) { |
||
| 260 | $this->map[$index++] = array($column['table'], $column['name'], $type); |
||
| 261 | } else {
|
||
| 262 | $this->map[$index++] = array(0, $column['name'], $type); |
||
| 263 | } |
||
| 264 | } |
||
| 265 | } |
||
| 266 | |||
| 267 | /**
|
||
| 268 | * Fetches the next row from the current result set
|
||
| 269 | *
|
||
| 270 | * @return mixed array with results fetched and mapped to column names or false if there is no results left to fetch
|
||
| 271 | */
|
||
| 272 | public function fetchResult() { |
||
| 273 | if ($row = $this->_result->fetch(PDO::FETCH_NUM)) { |
||
| 274 | $resultRow = array(); |
||
| 275 | foreach ($this->map as $col => $meta) { |
||
| 276 | list($table, $column, $type) = $meta; |
||
| 277 | $resultRow[$table][$column] = $row[$col]; |
||
| 278 | if ($type === 'boolean' && $row[$col] !== null) { |
||
| 279 | $resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]); |
||
| 280 | } |
||
| 281 | } |
||
| 282 | return $resultRow; |
||
| 283 | } |
||
| 284 | $this->_result->closeCursor();
|
||
| 285 | return false; |
||
| 286 | } |
||
| 287 | |||
| 288 | /**
|
||
| 289 | * Gets the database encoding
|
||
| 290 | *
|
||
| 291 | * @return string The database encoding
|
||
| 292 | */
|
||
| 293 | public function getEncoding() { |
||
| 294 | return $this->_execute('SHOW VARIABLES LIKE ?', array('character_set_client'))->fetchObject()->Value; |
||
| 295 | } |
||
| 296 | |||
| 297 | /**
|
||
| 298 | * Query charset by collation
|
||
| 299 | *
|
||
| 300 | * @param string $name Collation name
|
||
| 301 | * @return string Character set name
|
||
| 302 | */
|
||
| 303 | public function getCharsetName($name) { |
||
| 304 | if ((bool)version_compare($this->getVersion(), "5", "<")) { |
||
| 305 | return false; |
||
| 306 | } |
||
| 307 | if (isset($this->_charsets[$name])) { |
||
| 308 | return $this->_charsets[$name]; |
||
| 309 | } |
||
| 310 | $r = $this->_execute( |
||
| 311 | 'SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = ?',
|
||
| 312 | array($name) |
||
| 313 | ); |
||
| 314 | $cols = $r->fetch(PDO::FETCH_ASSOC); |
||
| 315 | |||
| 316 | if (isset($cols['CHARACTER_SET_NAME'])) { |
||
| 317 | $this->_charsets[$name] = $cols['CHARACTER_SET_NAME']; |
||
| 318 | } else {
|
||
| 319 | $this->_charsets[$name] = false; |
||
| 320 | } |
||
| 321 | return $this->_charsets[$name]; |
||
| 322 | } |
||
| 323 | |||
| 324 | /**
|
||
| 325 | * Returns an array of the fields in given table name.
|
||
| 326 | *
|
||
| 327 | * @param Model|string $model Name of database table to inspect or model instance
|
||
| 328 | * @return array Fields in table. Keys are name and type
|
||
| 329 | * @throws CakeException
|
||
| 330 | */
|
||
| 331 | public function describe($model) { |
||
| 332 | $key = $this->fullTableName($model, false); |
||
| 333 | $cache = parent::describe($key); |
||
| 334 | if ($cache) { |
||
| 335 | return $cache; |
||
| 336 | } |
||
| 337 | $table = $this->fullTableName($model); |
||
| 338 | |||
| 339 | $fields = false; |
||
| 340 | $cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table); |
||
| 341 | if (!$cols) { |
||
| 342 | throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table)); |
||
| 343 | } |
||
| 344 | |||
| 345 | while ($column = $cols->fetch(PDO::FETCH_OBJ)) { |
||
| 346 | $fields[$column->Field] = array( |
||
| 347 | 'type' => $this->column($column->Type), |
||
| 348 | 'null' => ($column->Null === 'YES' ? true : false), |
||
| 349 | 'default' => $column->Default, |
||
| 350 | 'length' => $this->length($column->Type) |
||
| 351 | ); |
||
| 352 | if (in_array($fields[$column->Field]['type'], $this->fieldParameters['unsigned']['types'], true)) { |
||
| 353 | $fields[$column->Field]['unsigned'] = $this->_unsigned($column->Type); |
||
| 354 | } |
||
| 355 | if (in_array($fields[$column->Field]['type'], array('timestamp', 'datetime')) && strtoupper($column->Default) === 'CURRENT_TIMESTAMP') { |
||
| 356 | $fields[$column->Field]['default'] = null; |
||
| 357 | } |
||
| 358 | if (!empty($column->Key) && isset($this->index[$column->Key])) { |
||
| 359 | $fields[$column->Field]['key'] = $this->index[$column->Key]; |
||
| 360 | } |
||
| 361 | foreach ($this->fieldParameters as $name => $value) { |
||
| 362 | if (!empty($column->{$value['column']})) { |
||
| 363 | $fields[$column->Field][$name] = $column->{$value['column']}; |
||
| 364 | } |
||
| 365 | } |
||
| 366 | if (isset($fields[$column->Field]['collate'])) { |
||
| 367 | $charset = $this->getCharsetName($fields[$column->Field]['collate']); |
||
| 368 | if ($charset) { |
||
| 369 | $fields[$column->Field]['charset'] = $charset; |
||
| 370 | } |
||
| 371 | } |
||
| 372 | } |
||
| 373 | $this->_cacheDescription($key, $fields); |
||
| 374 | $cols->closeCursor();
|
||
| 375 | return $fields; |
||
| 376 | } |
||
| 377 | |||
| 378 | /**
|
||
| 379 | * Generates and executes an SQL UPDATE statement for given model, fields, and values.
|
||
| 380 | *
|
||
| 381 | * @param Model $model The model to update.
|
||
| 382 | * @param array $fields The fields to update.
|
||
| 383 | * @param array $values The values to set.
|
||
| 384 | * @param mixed $conditions The conditions to use.
|
||
| 385 | * @return array
|
||
| 386 | */
|
||
| 387 | public function update(Model $model, $fields = array(), $values = null, $conditions = null) { |
||
| 388 | if (!$this->_useAlias) { |
||
| 389 | return parent::update($model, $fields, $values, $conditions); |
||
| 390 | } |
||
| 391 | |||
| 392 | if (!$values) { |
||
| 393 | $combined = $fields; |
||
| 394 | } else {
|
||
| 395 | $combined = array_combine($fields, $values); |
||
| 396 | } |
||
| 397 | |||
| 398 | $alias = $joins = false; |
||
| 399 | $fields = $this->_prepareUpdateFields($model, $combined, empty($conditions), !empty($conditions)); |
||
| 400 | $fields = implode(', ', $fields); |
||
| 401 | $table = $this->fullTableName($model); |
||
| 402 | |||
| 403 | if (!empty($conditions)) { |
||
| 404 | $alias = $this->name($model->alias); |
||
| 405 | if ($model->name === $model->alias) { |
||
| 406 | $joins = implode(' ', $this->_getJoins($model)); |
||
| 407 | } |
||
| 408 | } |
||
| 409 | $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model); |
||
| 410 | |||
| 411 | if ($conditions === false) { |
||
| 412 | return false; |
||
| 413 | } |
||
| 414 | |||
| 415 | if (!$this->execute($this->renderStatement('update', compact('table', 'alias', 'joins', 'fields', 'conditions')))) { |
||
| 416 | $model->onError();
|
||
| 417 | return false; |
||
| 418 | } |
||
| 419 | return true; |
||
| 420 | } |
||
| 421 | |||
| 422 | /**
|
||
| 423 | * Generates and executes an SQL DELETE statement for given id/conditions on given model.
|
||
| 424 | *
|
||
| 425 | * @param Model $model The model to delete from.
|
||
| 426 | * @param mixed $conditions The conditions to use.
|
||
| 427 | * @return bool Success
|
||
| 428 | */
|
||
| 429 | public function delete(Model $model, $conditions = null) { |
||
| 430 | if (!$this->_useAlias) { |
||
| 431 | return parent::delete($model, $conditions); |
||
| 432 | } |
||
| 433 | $alias = $this->name($model->alias); |
||
| 434 | $table = $this->fullTableName($model); |
||
| 435 | $joins = implode(' ', $this->_getJoins($model)); |
||
| 436 | |||
| 437 | if (empty($conditions)) { |
||
| 438 | $alias = $joins = false; |
||
| 439 | } |
||
| 440 | $complexConditions = false; |
||
| 441 | foreach ((array)$conditions as $key => $value) { |
||
| 442 | if (strpos($key, $model->alias) === false) { |
||
| 443 | $complexConditions = true; |
||
| 444 | break;
|
||
| 445 | } |
||
| 446 | } |
||
| 447 | if (!$complexConditions) { |
||
| 448 | $joins = false; |
||
| 449 | } |
||
| 450 | |||
| 451 | $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model); |
||
| 452 | if ($conditions === false) { |
||
| 453 | return false; |
||
| 454 | } |
||
| 455 | if ($this->execute($this->renderStatement('delete', compact('alias', 'table', 'joins', 'conditions'))) === false) { |
||
| 456 | $model->onError();
|
||
| 457 | return false; |
||
| 458 | } |
||
| 459 | return true; |
||
| 460 | } |
||
| 461 | |||
| 462 | /**
|
||
| 463 | * Sets the database encoding
|
||
| 464 | *
|
||
| 465 | * @param string $enc Database encoding
|
||
| 466 | * @return bool
|
||
| 467 | */
|
||
| 468 | public function setEncoding($enc) { |
||
| 469 | return $this->_execute('SET NAMES ' . $enc) !== false; |
||
| 470 | } |
||
| 471 | |||
| 472 | /**
|
||
| 473 | * Returns an array of the indexes in given datasource name.
|
||
| 474 | *
|
||
| 475 | * @param string $model Name of model to inspect
|
||
| 476 | * @return array Fields in table. Keys are column and unique
|
||
| 477 | */
|
||
| 478 | public function index($model) { |
||
| 479 | $index = array(); |
||
| 480 | $table = $this->fullTableName($model); |
||
| 481 | $old = version_compare($this->getVersion(), '4.1', '<='); |
||
| 482 | if ($table) { |
||
| 483 | $indexes = $this->_execute('SHOW INDEX FROM ' . $table); |
||
| 484 | // @codingStandardsIgnoreStart
|
||
| 485 | // MySQL columns don't match the cakephp conventions.
|
||
| 486 | while ($idx = $indexes->fetch(PDO::FETCH_OBJ)) { |
||
| 487 | if ($old) { |
||
| 488 | $idx = (object)current((array)$idx); |
||
| 489 | } |
||
| 490 | if (!isset($index[$idx->Key_name]['column'])) { |
||
| 491 | $col = array(); |
||
| 492 | $index[$idx->Key_name]['column'] = $idx->Column_name; |
||
| 493 | |||
| 494 | if ($idx->Index_type === 'FULLTEXT') { |
||
| 495 | $index[$idx->Key_name]['type'] = strtolower($idx->Index_type); |
||
| 496 | } else {
|
||
| 497 | $index[$idx->Key_name]['unique'] = (int)($idx->Non_unique == 0); |
||
| 498 | } |
||
| 499 | } else {
|
||
| 500 | if (!empty($index[$idx->Key_name]['column']) && !is_array($index[$idx->Key_name]['column'])) { |
||
| 501 | $col[] = $index[$idx->Key_name]['column']; |
||
| 502 | } |
||
| 503 | $col[] = $idx->Column_name; |
||
| 504 | $index[$idx->Key_name]['column'] = $col; |
||
| 505 | } |
||
| 506 | if (!empty($idx->Sub_part)) { |
||
| 507 | if (!isset($index[$idx->Key_name]['length'])) { |
||
| 508 | $index[$idx->Key_name]['length'] = array(); |
||
| 509 | } |
||
| 510 | $index[$idx->Key_name]['length'][$idx->Column_name] = $idx->Sub_part; |
||
| 511 | } |
||
| 512 | } |
||
| 513 | // @codingStandardsIgnoreEnd
|
||
| 514 | $indexes->closeCursor();
|
||
| 515 | } |
||
| 516 | return $index; |
||
| 517 | } |
||
| 518 | |||
| 519 | /**
|
||
| 520 | * Generate a MySQL Alter Table syntax for the given Schema comparison
|
||
| 521 | *
|
||
| 522 | * @param array $compare Result of a CakeSchema::compare()
|
||
| 523 | * @param string $table The table name.
|
||
| 524 | * @return array Array of alter statements to make.
|
||
| 525 | */
|
||
| 526 | public function alterSchema($compare, $table = null) { |
||
| 527 | if (!is_array($compare)) { |
||
| 528 | return false; |
||
| 529 | } |
||
| 530 | $out = ''; |
||
| 531 | $colList = array(); |
||
| 532 | foreach ($compare as $curTable => $types) { |
||
| 533 | $indexes = $tableParameters = $colList = array(); |
||
| 534 | if (!$table || $table === $curTable) { |
||
| 535 | $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n"; |
||
| 536 | foreach ($types as $type => $column) { |
||
| 537 | if (isset($column['indexes'])) { |
||
| 538 | $indexes[$type] = $column['indexes']; |
||
| 539 | unset($column['indexes']); |
||
| 540 | } |
||
| 541 | if (isset($column['tableParameters'])) { |
||
| 542 | $tableParameters[$type] = $column['tableParameters']; |
||
| 543 | unset($column['tableParameters']); |
||
| 544 | } |
||
| 545 | switch ($type) { |
||
| 546 | case 'add': |
||
| 547 | foreach ($column as $field => $col) { |
||
| 548 | $col['name'] = $field; |
||
| 549 | $alter = 'ADD ' . $this->buildColumn($col); |
||
| 550 | if (isset($col['after'])) { |
||
| 551 | $alter .= ' AFTER ' . $this->name($col['after']); |
||
| 552 | } |
||
| 553 | $colList[] = $alter; |
||
| 554 | } |
||
| 555 | break;
|
||
| 556 | case 'drop': |
||
| 557 | foreach ($column as $field => $col) { |
||
| 558 | $col['name'] = $field; |
||
| 559 | $colList[] = 'DROP ' . $this->name($field); |
||
| 560 | } |
||
| 561 | break;
|
||
| 562 | case 'change': |
||
| 563 | foreach ($column as $field => $col) { |
||
| 564 | if (!isset($col['name'])) { |
||
| 565 | $col['name'] = $field; |
||
| 566 | } |
||
| 567 | $alter = 'CHANGE ' . $this->name($field) . ' ' . $this->buildColumn($col); |
||
| 568 | if (isset($col['after'])) { |
||
| 569 | $alter .= ' AFTER ' . $this->name($col['after']); |
||
| 570 | } |
||
| 571 | $colList[] = $alter; |
||
| 572 | } |
||
| 573 | break;
|
||
| 574 | } |
||
| 575 | } |
||
| 576 | $colList = array_merge($colList, $this->_alterIndexes($curTable, $indexes)); |
||
| 577 | $colList = array_merge($colList, $this->_alterTableParameters($curTable, $tableParameters)); |
||
| 578 | $out .= "\t" . implode(",\n\t", $colList) . ";\n\n"; |
||
| 579 | } |
||
| 580 | } |
||
| 581 | return $out; |
||
| 582 | } |
||
| 583 | |||
| 584 | /**
|
||
| 585 | * Generate a "drop table" statement for the given table
|
||
| 586 | *
|
||
| 587 | * @param type $table Name of the table to drop
|
||
| 588 | * @return string Drop table SQL statement
|
||
| 589 | */
|
||
| 590 | protected function _dropTable($table) { |
||
| 591 | return 'DROP TABLE IF EXISTS ' . $this->fullTableName($table) . ";"; |
||
| 592 | } |
||
| 593 | |||
| 594 | /**
|
||
| 595 | * Generate MySQL table parameter alteration statements for a table.
|
||
| 596 | *
|
||
| 597 | * @param string $table Table to alter parameters for.
|
||
| 598 | * @param array $parameters Parameters to add & drop.
|
||
| 599 | * @return array Array of table property alteration statements.
|
||
| 600 | */
|
||
| 601 | protected function _alterTableParameters($table, $parameters) { |
||
| 602 | if (isset($parameters['change'])) { |
||
| 603 | return $this->buildTableParameters($parameters['change']); |
||
| 604 | } |
||
| 605 | return array(); |
||
| 606 | } |
||
| 607 | |||
| 608 | /**
|
||
| 609 | * Format indexes for create table
|
||
| 610 | *
|
||
| 611 | * @param array $indexes An array of indexes to generate SQL from
|
||
| 612 | * @param string $table Optional table name, not used
|
||
| 613 | * @return array An array of SQL statements for indexes
|
||
| 614 | * @see DboSource::buildIndex()
|
||
| 615 | */
|
||
| 616 | public function buildIndex($indexes, $table = null) { |
||
| 617 | $join = array(); |
||
| 618 | foreach ($indexes as $name => $value) { |
||
| 619 | $out = ''; |
||
| 620 | if ($name === 'PRIMARY') { |
||
| 621 | $out .= 'PRIMARY '; |
||
| 622 | $name = null; |
||
| 623 | } else {
|
||
| 624 | if (!empty($value['unique'])) { |
||
| 625 | $out .= 'UNIQUE '; |
||
| 626 | } |
||
| 627 | $name = $this->startQuote . $name . $this->endQuote; |
||
| 628 | } |
||
| 629 | if (isset($value['type']) && strtolower($value['type']) === 'fulltext') { |
||
| 630 | $out .= 'FULLTEXT '; |
||
| 631 | } |
||
| 632 | $out .= 'KEY ' . $name . ' ('; |
||
| 633 | |||
| 634 | if (is_array($value['column'])) { |
||
| 635 | if (isset($value['length'])) { |
||
| 636 | $vals = array(); |
||
| 637 | foreach ($value['column'] as $column) { |
||
| 638 | $name = $this->name($column); |
||
| 639 | if (isset($value['length'])) { |
||
| 640 | $name .= $this->_buildIndexSubPart($value['length'], $column); |
||
| 641 | } |
||
| 642 | $vals[] = $name; |
||
| 643 | } |
||
| 644 | $out .= implode(', ', $vals); |
||
| 645 | } else {
|
||
| 646 | $out .= implode(', ', array_map(array(&$this, 'name'), $value['column'])); |
||
| 647 | } |
||
| 648 | } else {
|
||
| 649 | $out .= $this->name($value['column']); |
||
| 650 | if (isset($value['length'])) { |
||
| 651 | $out .= $this->_buildIndexSubPart($value['length'], $value['column']); |
||
| 652 | } |
||
| 653 | } |
||
| 654 | $out .= ')'; |
||
| 655 | $join[] = $out; |
||
| 656 | } |
||
| 657 | return $join; |
||
| 658 | } |
||
| 659 | |||
| 660 | /**
|
||
| 661 | * Generate MySQL index alteration statements for a table.
|
||
| 662 | *
|
||
| 663 | * @param string $table Table to alter indexes for
|
||
| 664 | * @param array $indexes Indexes to add and drop
|
||
| 665 | * @return array Index alteration statements
|
||
| 666 | */
|
||
| 667 | protected function _alterIndexes($table, $indexes) { |
||
| 668 | $alter = array(); |
||
| 669 | if (isset($indexes['drop'])) { |
||
| 670 | foreach ($indexes['drop'] as $name => $value) { |
||
| 671 | $out = 'DROP '; |
||
| 672 | if ($name === 'PRIMARY') { |
||
| 673 | $out .= 'PRIMARY KEY'; |
||
| 674 | } else {
|
||
| 675 | $out .= 'KEY ' . $this->startQuote . $name . $this->endQuote; |
||
| 676 | } |
||
| 677 | $alter[] = $out; |
||
| 678 | } |
||
| 679 | } |
||
| 680 | if (isset($indexes['add'])) { |
||
| 681 | $add = $this->buildIndex($indexes['add']); |
||
| 682 | foreach ($add as $index) { |
||
| 683 | $alter[] = 'ADD ' . $index; |
||
| 684 | } |
||
| 685 | } |
||
| 686 | return $alter; |
||
| 687 | } |
||
| 688 | |||
| 689 | /**
|
||
| 690 | * Format length for text indexes
|
||
| 691 | *
|
||
| 692 | * @param array $lengths An array of lengths for a single index
|
||
| 693 | * @param string $column The column for which to generate the index length
|
||
| 694 | * @return string Formatted length part of an index field
|
||
| 695 | */
|
||
| 696 | protected function _buildIndexSubPart($lengths, $column) { |
||
| 697 | if ($lengths === null) { |
||
| 698 | return ''; |
||
| 699 | } |
||
| 700 | if (!isset($lengths[$column])) { |
||
| 701 | return ''; |
||
| 702 | } |
||
| 703 | return '(' . $lengths[$column] . ')'; |
||
| 704 | } |
||
| 705 | |||
| 706 | /**
|
||
| 707 | * Returns a detailed array of sources (tables) in the database.
|
||
| 708 | *
|
||
| 709 | * @param string $name Table name to get parameters
|
||
| 710 | * @return array Array of table names in the database
|
||
| 711 | */
|
||
| 712 | public function listDetailedSources($name = null) { |
||
| 713 | $condition = ''; |
||
| 714 | if (is_string($name)) { |
||
| 715 | $condition = ' WHERE name = ' . $this->value($name); |
||
| 716 | } |
||
| 717 | $result = $this->_connection->query('SHOW TABLE STATUS ' . $condition, PDO::FETCH_ASSOC); |
||
| 718 | |||
| 719 | if (!$result) { |
||
| 720 | $result->closeCursor();
|
||
| 721 | return array(); |
||
| 722 | } |
||
| 723 | $tables = array(); |
||
| 724 | foreach ($result as $row) { |
||
| 725 | $tables[$row['Name']] = (array)$row; |
||
| 726 | unset($tables[$row['Name']]['queryString']); |
||
| 727 | if (!empty($row['Collation'])) { |
||
| 728 | $charset = $this->getCharsetName($row['Collation']); |
||
| 729 | if ($charset) { |
||
| 730 | $tables[$row['Name']]['charset'] = $charset; |
||
| 731 | } |
||
| 732 | } |
||
| 733 | } |
||
| 734 | $result->closeCursor();
|
||
| 735 | if (is_string($name) && isset($tables[$name])) { |
||
| 736 | return $tables[$name]; |
||
| 737 | } |
||
| 738 | return $tables; |
||
| 739 | } |
||
| 740 | |||
| 741 | /**
|
||
| 742 | * Converts database-layer column types to basic types
|
||
| 743 | *
|
||
| 744 | * @param string $real Real database-layer column type (i.e. "varchar(255)")
|
||
| 745 | * @return string Abstract column type (i.e. "string")
|
||
| 746 | */
|
||
| 747 | public function column($real) { |
||
| 748 | if (is_array($real)) { |
||
| 749 | $col = $real['name']; |
||
| 750 | if (isset($real['limit'])) { |
||
| 751 | $col .= '(' . $real['limit'] . ')'; |
||
| 752 | } |
||
| 753 | return $col; |
||
| 754 | } |
||
| 755 | |||
| 756 | $col = str_replace(')', '', $real); |
||
| 757 | $limit = $this->length($real); |
||
| 758 | if (strpos($col, '(') !== false) { |
||
| 759 | list($col, $vals) = explode('(', $col); |
||
| 760 | } |
||
| 761 | |||
| 762 | if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) { |
||
| 763 | return $col; |
||
| 764 | } |
||
| 765 | if (($col === 'tinyint' && $limit === 1) || $col === 'boolean') { |
||
| 766 | return 'boolean'; |
||
| 767 | } |
||
| 768 | if (strpos($col, 'bigint') !== false || $col === 'bigint') { |
||
| 769 | return 'biginteger'; |
||
| 770 | } |
||
| 771 | if (strpos($col, 'int') !== false) { |
||
| 772 | return 'integer'; |
||
| 773 | } |
||
| 774 | if (strpos($col, 'char') !== false || $col === 'tinytext') { |
||
| 775 | return 'string'; |
||
| 776 | } |
||
| 777 | if (strpos($col, 'text') !== false) { |
||
| 778 | return 'text'; |
||
| 779 | } |
||
| 780 | if (strpos($col, 'blob') !== false || $col === 'binary') { |
||
| 781 | return 'binary'; |
||
| 782 | } |
||
| 783 | if (strpos($col, 'float') !== false || strpos($col, 'double') !== false) { |
||
| 784 | return 'float'; |
||
| 785 | } |
||
| 786 | if (strpos($col, 'decimal') !== false || strpos($col, 'numeric') !== false) { |
||
| 787 | return 'decimal'; |
||
| 788 | } |
||
| 789 | if (strpos($col, 'enum') !== false) { |
||
| 790 | return "enum($vals)"; |
||
| 791 | } |
||
| 792 | if (strpos($col, 'set') !== false) { |
||
| 793 | return "set($vals)"; |
||
| 794 | } |
||
| 795 | return 'text'; |
||
| 796 | } |
||
| 797 | |||
| 798 | /**
|
||
| 799 | * {@inheritDoc}
|
||
| 800 | */
|
||
| 801 | public function value($data, $column = null, $null = true) { |
||
| 802 | $value = parent::value($data, $column, $null); |
||
| 803 | if (is_numeric($value) && substr($column, 0, 3) === 'set') { |
||
| 804 | return $this->_connection->quote($value); |
||
| 805 | } |
||
| 806 | return $value; |
||
| 807 | } |
||
| 808 | |||
| 809 | /**
|
||
| 810 | * Gets the schema name
|
||
| 811 | *
|
||
| 812 | * @return string The schema name
|
||
| 813 | */
|
||
| 814 | public function getSchemaName() { |
||
| 815 | return $this->config['database']; |
||
| 816 | } |
||
| 817 | |||
| 818 | /**
|
||
| 819 | * Check if the server support nested transactions
|
||
| 820 | *
|
||
| 821 | * @return bool
|
||
| 822 | */
|
||
| 823 | public function nestedTransactionSupported() { |
||
| 824 | return $this->useNestedTransactions && version_compare($this->getVersion(), '4.1', '>='); |
||
| 825 | } |
||
| 826 | |||
| 827 | /**
|
||
| 828 | * Check if column type is unsigned
|
||
| 829 | *
|
||
| 830 | * @param string $real Real database-layer column type (i.e. "varchar(255)")
|
||
| 831 | * @return bool True if column is unsigned, false otherwise
|
||
| 832 | */
|
||
| 833 | protected function _unsigned($real) { |
||
| 834 | return strpos(strtolower($real), 'unsigned') !== false; |
||
| 835 | } |
||
| 836 | |||
| 837 | } |