1<?php 2 3namespace Doctrine\DBAL\Schema; 4 5use Doctrine\DBAL\Platforms\MariaDb1027Platform; 6use Doctrine\DBAL\Platforms\MySqlPlatform; 7use Doctrine\DBAL\Types\Type; 8 9use function array_change_key_case; 10use function array_shift; 11use function array_values; 12use function assert; 13use function explode; 14use function is_string; 15use function preg_match; 16use function strpos; 17use function strtok; 18use function strtolower; 19use function strtr; 20 21use const CASE_LOWER; 22 23/** 24 * Schema manager for the MySql RDBMS. 25 */ 26class MySqlSchemaManager extends AbstractSchemaManager 27{ 28 /** 29 * @see https://mariadb.com/kb/en/library/string-literals/#escape-sequences 30 */ 31 private const MARIADB_ESCAPE_SEQUENCES = [ 32 '\\0' => "\0", 33 "\\'" => "'", 34 '\\"' => '"', 35 '\\b' => "\b", 36 '\\n' => "\n", 37 '\\r' => "\r", 38 '\\t' => "\t", 39 '\\Z' => "\x1a", 40 '\\\\' => '\\', 41 '\\%' => '%', 42 '\\_' => '_', 43 44 // Internally, MariaDB escapes single quotes using the standard syntax 45 "''" => "'", 46 ]; 47 48 /** 49 * {@inheritdoc} 50 */ 51 protected function _getPortableViewDefinition($view) 52 { 53 return new View($view['TABLE_NAME'], $view['VIEW_DEFINITION']); 54 } 55 56 /** 57 * {@inheritdoc} 58 */ 59 protected function _getPortableTableDefinition($table) 60 { 61 return array_shift($table); 62 } 63 64 /** 65 * {@inheritdoc} 66 */ 67 protected function _getPortableUserDefinition($user) 68 { 69 return [ 70 'user' => $user['User'], 71 'password' => $user['Password'], 72 ]; 73 } 74 75 /** 76 * {@inheritdoc} 77 */ 78 protected function _getPortableTableIndexesList($tableIndexes, $tableName = null) 79 { 80 foreach ($tableIndexes as $k => $v) { 81 $v = array_change_key_case($v, CASE_LOWER); 82 if ($v['key_name'] === 'PRIMARY') { 83 $v['primary'] = true; 84 } else { 85 $v['primary'] = false; 86 } 87 88 if (strpos($v['index_type'], 'FULLTEXT') !== false) { 89 $v['flags'] = ['FULLTEXT']; 90 } elseif (strpos($v['index_type'], 'SPATIAL') !== false) { 91 $v['flags'] = ['SPATIAL']; 92 } 93 94 $v['length'] = isset($v['sub_part']) ? (int) $v['sub_part'] : null; 95 96 $tableIndexes[$k] = $v; 97 } 98 99 return parent::_getPortableTableIndexesList($tableIndexes, $tableName); 100 } 101 102 /** 103 * {@inheritdoc} 104 */ 105 protected function _getPortableDatabaseDefinition($database) 106 { 107 return $database['Database']; 108 } 109 110 /** 111 * {@inheritdoc} 112 */ 113 protected function _getPortableTableColumnDefinition($tableColumn) 114 { 115 $tableColumn = array_change_key_case($tableColumn, CASE_LOWER); 116 117 $dbType = strtolower($tableColumn['type']); 118 $dbType = strtok($dbType, '(), '); 119 assert(is_string($dbType)); 120 121 $length = $tableColumn['length'] ?? strtok('(), '); 122 123 $fixed = null; 124 125 if (! isset($tableColumn['name'])) { 126 $tableColumn['name'] = ''; 127 } 128 129 $scale = null; 130 $precision = null; 131 132 $type = $this->_platform->getDoctrineTypeMapping($dbType); 133 134 // In cases where not connected to a database DESCRIBE $table does not return 'Comment' 135 if (isset($tableColumn['comment'])) { 136 $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type); 137 $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type); 138 } 139 140 switch ($dbType) { 141 case 'char': 142 case 'binary': 143 $fixed = true; 144 break; 145 146 case 'float': 147 case 'double': 148 case 'real': 149 case 'numeric': 150 case 'decimal': 151 if (preg_match('([A-Za-z]+\(([0-9]+)\,([0-9]+)\))', $tableColumn['type'], $match)) { 152 $precision = $match[1]; 153 $scale = $match[2]; 154 $length = null; 155 } 156 157 break; 158 159 case 'tinytext': 160 $length = MySqlPlatform::LENGTH_LIMIT_TINYTEXT; 161 break; 162 163 case 'text': 164 $length = MySqlPlatform::LENGTH_LIMIT_TEXT; 165 break; 166 167 case 'mediumtext': 168 $length = MySqlPlatform::LENGTH_LIMIT_MEDIUMTEXT; 169 break; 170 171 case 'tinyblob': 172 $length = MySqlPlatform::LENGTH_LIMIT_TINYBLOB; 173 break; 174 175 case 'blob': 176 $length = MySqlPlatform::LENGTH_LIMIT_BLOB; 177 break; 178 179 case 'mediumblob': 180 $length = MySqlPlatform::LENGTH_LIMIT_MEDIUMBLOB; 181 break; 182 183 case 'tinyint': 184 case 'smallint': 185 case 'mediumint': 186 case 'int': 187 case 'integer': 188 case 'bigint': 189 case 'year': 190 $length = null; 191 break; 192 } 193 194 if ($this->_platform instanceof MariaDb1027Platform) { 195 $columnDefault = $this->getMariaDb1027ColumnDefault($this->_platform, $tableColumn['default']); 196 } else { 197 $columnDefault = $tableColumn['default']; 198 } 199 200 $options = [ 201 'length' => $length !== null ? (int) $length : null, 202 'unsigned' => strpos($tableColumn['type'], 'unsigned') !== false, 203 'fixed' => (bool) $fixed, 204 'default' => $columnDefault, 205 'notnull' => $tableColumn['null'] !== 'YES', 206 'scale' => null, 207 'precision' => null, 208 'autoincrement' => strpos($tableColumn['extra'], 'auto_increment') !== false, 209 'comment' => isset($tableColumn['comment']) && $tableColumn['comment'] !== '' 210 ? $tableColumn['comment'] 211 : null, 212 ]; 213 214 if ($scale !== null && $precision !== null) { 215 $options['scale'] = (int) $scale; 216 $options['precision'] = (int) $precision; 217 } 218 219 $column = new Column($tableColumn['field'], Type::getType($type), $options); 220 221 if (isset($tableColumn['characterset'])) { 222 $column->setPlatformOption('charset', $tableColumn['characterset']); 223 } 224 225 if (isset($tableColumn['collation'])) { 226 $column->setPlatformOption('collation', $tableColumn['collation']); 227 } 228 229 return $column; 230 } 231 232 /** 233 * Return Doctrine/Mysql-compatible column default values for MariaDB 10.2.7+ servers. 234 * 235 * - Since MariaDb 10.2.7 column defaults stored in information_schema are now quoted 236 * to distinguish them from expressions (see MDEV-10134). 237 * - CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE are stored in information_schema 238 * as current_timestamp(), currdate(), currtime() 239 * - Quoted 'NULL' is not enforced by Maria, it is technically possible to have 240 * null in some circumstances (see https://jira.mariadb.org/browse/MDEV-14053) 241 * - \' is always stored as '' in information_schema (normalized) 242 * 243 * @link https://mariadb.com/kb/en/library/information-schema-columns-table/ 244 * @link https://jira.mariadb.org/browse/MDEV-13132 245 * 246 * @param string|null $columnDefault default value as stored in information_schema for MariaDB >= 10.2.7 247 */ 248 private function getMariaDb1027ColumnDefault(MariaDb1027Platform $platform, ?string $columnDefault): ?string 249 { 250 if ($columnDefault === 'NULL' || $columnDefault === null) { 251 return null; 252 } 253 254 if (preg_match('/^\'(.*)\'$/', $columnDefault, $matches)) { 255 return strtr($matches[1], self::MARIADB_ESCAPE_SEQUENCES); 256 } 257 258 switch ($columnDefault) { 259 case 'current_timestamp()': 260 return $platform->getCurrentTimestampSQL(); 261 262 case 'curdate()': 263 return $platform->getCurrentDateSQL(); 264 265 case 'curtime()': 266 return $platform->getCurrentTimeSQL(); 267 } 268 269 return $columnDefault; 270 } 271 272 /** 273 * {@inheritdoc} 274 */ 275 protected function _getPortableTableForeignKeysList($tableForeignKeys) 276 { 277 $list = []; 278 foreach ($tableForeignKeys as $value) { 279 $value = array_change_key_case($value, CASE_LOWER); 280 if (! isset($list[$value['constraint_name']])) { 281 if (! isset($value['delete_rule']) || $value['delete_rule'] === 'RESTRICT') { 282 $value['delete_rule'] = null; 283 } 284 285 if (! isset($value['update_rule']) || $value['update_rule'] === 'RESTRICT') { 286 $value['update_rule'] = null; 287 } 288 289 $list[$value['constraint_name']] = [ 290 'name' => $value['constraint_name'], 291 'local' => [], 292 'foreign' => [], 293 'foreignTable' => $value['referenced_table_name'], 294 'onDelete' => $value['delete_rule'], 295 'onUpdate' => $value['update_rule'], 296 ]; 297 } 298 299 $list[$value['constraint_name']]['local'][] = $value['column_name']; 300 $list[$value['constraint_name']]['foreign'][] = $value['referenced_column_name']; 301 } 302 303 $result = []; 304 foreach ($list as $constraint) { 305 $result[] = new ForeignKeyConstraint( 306 array_values($constraint['local']), 307 $constraint['foreignTable'], 308 array_values($constraint['foreign']), 309 $constraint['name'], 310 [ 311 'onDelete' => $constraint['onDelete'], 312 'onUpdate' => $constraint['onUpdate'], 313 ] 314 ); 315 } 316 317 return $result; 318 } 319 320 /** 321 * {@inheritdoc} 322 */ 323 public function listTableDetails($name) 324 { 325 $table = parent::listTableDetails($name); 326 327 $platform = $this->_platform; 328 assert($platform instanceof MySqlPlatform); 329 $sql = $platform->getListTableMetadataSQL($name); 330 331 $tableOptions = $this->_conn->fetchAssoc($sql); 332 333 if ($tableOptions === false) { 334 return $table; 335 } 336 337 $table->addOption('engine', $tableOptions['ENGINE']); 338 339 if ($tableOptions['TABLE_COLLATION'] !== null) { 340 $table->addOption('collation', $tableOptions['TABLE_COLLATION']); 341 } 342 343 if ($tableOptions['AUTO_INCREMENT'] !== null) { 344 $table->addOption('autoincrement', $tableOptions['AUTO_INCREMENT']); 345 } 346 347 $table->addOption('comment', $tableOptions['TABLE_COMMENT']); 348 $table->addOption('create_options', $this->parseCreateOptions($tableOptions['CREATE_OPTIONS'])); 349 350 return $table; 351 } 352 353 /** 354 * @return string[]|true[] 355 */ 356 private function parseCreateOptions(?string $string): array 357 { 358 $options = []; 359 360 if ($string === null || $string === '') { 361 return $options; 362 } 363 364 foreach (explode(' ', $string) as $pair) { 365 $parts = explode('=', $pair, 2); 366 367 $options[$parts[0]] = $parts[1] ?? true; 368 } 369 370 return $options; 371 } 372} 373