MySqlSchemaManager.php 11 KB

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