SQLServerSchemaManager.php 9.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310
  1. <?php
  2. namespace Doctrine\DBAL\Schema;
  3. use Doctrine\DBAL\DBALException;
  4. use Doctrine\DBAL\Driver\DriverException;
  5. use Doctrine\DBAL\Types\Type;
  6. use PDOException;
  7. use function count;
  8. use function in_array;
  9. use function preg_replace;
  10. use function sprintf;
  11. use function str_replace;
  12. use function strpos;
  13. use function strtok;
  14. use function trim;
  15. /**
  16. * SQL Server Schema Manager.
  17. */
  18. class SQLServerSchemaManager extends AbstractSchemaManager
  19. {
  20. /**
  21. * {@inheritdoc}
  22. */
  23. public function dropDatabase($database)
  24. {
  25. try {
  26. parent::dropDatabase($database);
  27. } catch (DBALException $exception) {
  28. $exception = $exception->getPrevious();
  29. if (! $exception instanceof DriverException) {
  30. throw $exception;
  31. }
  32. // If we have a error code 3702, the drop database operation failed
  33. // because of active connections on the database.
  34. // To force dropping the database, we first have to close all active connections
  35. // on that database and issue the drop database operation again.
  36. if ($exception->getErrorCode() !== 3702) {
  37. throw $exception;
  38. }
  39. $this->closeActiveDatabaseConnections($database);
  40. parent::dropDatabase($database);
  41. }
  42. }
  43. /**
  44. * {@inheritdoc}
  45. */
  46. protected function _getPortableSequenceDefinition($sequence)
  47. {
  48. return new Sequence($sequence['name'], (int) $sequence['increment'], (int) $sequence['start_value']);
  49. }
  50. /**
  51. * {@inheritdoc}
  52. */
  53. protected function _getPortableTableColumnDefinition($tableColumn)
  54. {
  55. $dbType = strtok($tableColumn['type'], '(), ');
  56. $fixed = null;
  57. $length = (int) $tableColumn['length'];
  58. $default = $tableColumn['default'];
  59. if (! isset($tableColumn['name'])) {
  60. $tableColumn['name'] = '';
  61. }
  62. if ($default !== null) {
  63. while ($default !== ($default2 = preg_replace('/^\((.*)\)$/', '$1', $default))) {
  64. $default = trim($default2, "'");
  65. if ($default !== 'getdate()') {
  66. continue;
  67. }
  68. $default = $this->_platform->getCurrentTimestampSQL();
  69. }
  70. }
  71. switch ($dbType) {
  72. case 'nchar':
  73. case 'nvarchar':
  74. case 'ntext':
  75. // Unicode data requires 2 bytes per character
  76. $length /= 2;
  77. break;
  78. case 'varchar':
  79. // TEXT type is returned as VARCHAR(MAX) with a length of -1
  80. if ($length === -1) {
  81. $dbType = 'text';
  82. }
  83. break;
  84. }
  85. if ($dbType === 'char' || $dbType === 'nchar' || $dbType === 'binary') {
  86. $fixed = true;
  87. }
  88. $type = $this->_platform->getDoctrineTypeMapping($dbType);
  89. $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
  90. $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
  91. $options = [
  92. 'length' => $length === 0 || ! in_array($type, ['text', 'string']) ? null : $length,
  93. 'unsigned' => false,
  94. 'fixed' => (bool) $fixed,
  95. 'default' => $default !== 'NULL' ? $default : null,
  96. 'notnull' => (bool) $tableColumn['notnull'],
  97. 'scale' => $tableColumn['scale'],
  98. 'precision' => $tableColumn['precision'],
  99. 'autoincrement' => (bool) $tableColumn['autoincrement'],
  100. 'comment' => $tableColumn['comment'] !== '' ? $tableColumn['comment'] : null,
  101. ];
  102. $column = new Column($tableColumn['name'], Type::getType($type), $options);
  103. if (isset($tableColumn['collation']) && $tableColumn['collation'] !== 'NULL') {
  104. $column->setPlatformOption('collation', $tableColumn['collation']);
  105. }
  106. return $column;
  107. }
  108. /**
  109. * {@inheritdoc}
  110. */
  111. protected function _getPortableTableForeignKeysList($tableForeignKeys)
  112. {
  113. $foreignKeys = [];
  114. foreach ($tableForeignKeys as $tableForeignKey) {
  115. if (! isset($foreignKeys[$tableForeignKey['ForeignKey']])) {
  116. $foreignKeys[$tableForeignKey['ForeignKey']] = [
  117. 'local_columns' => [$tableForeignKey['ColumnName']],
  118. 'foreign_table' => $tableForeignKey['ReferenceTableName'],
  119. 'foreign_columns' => [$tableForeignKey['ReferenceColumnName']],
  120. 'name' => $tableForeignKey['ForeignKey'],
  121. 'options' => [
  122. 'onUpdate' => str_replace('_', ' ', $tableForeignKey['update_referential_action_desc']),
  123. 'onDelete' => str_replace('_', ' ', $tableForeignKey['delete_referential_action_desc']),
  124. ],
  125. ];
  126. } else {
  127. $foreignKeys[$tableForeignKey['ForeignKey']]['local_columns'][] = $tableForeignKey['ColumnName'];
  128. $foreignKeys[$tableForeignKey['ForeignKey']]['foreign_columns'][] = $tableForeignKey['ReferenceColumnName'];
  129. }
  130. }
  131. return parent::_getPortableTableForeignKeysList($foreignKeys);
  132. }
  133. /**
  134. * {@inheritdoc}
  135. */
  136. protected function _getPortableTableIndexesList($tableIndexRows, $tableName = null)
  137. {
  138. foreach ($tableIndexRows as &$tableIndex) {
  139. $tableIndex['non_unique'] = (bool) $tableIndex['non_unique'];
  140. $tableIndex['primary'] = (bool) $tableIndex['primary'];
  141. $tableIndex['flags'] = $tableIndex['flags'] ? [$tableIndex['flags']] : null;
  142. }
  143. return parent::_getPortableTableIndexesList($tableIndexRows, $tableName);
  144. }
  145. /**
  146. * {@inheritdoc}
  147. */
  148. protected function _getPortableTableForeignKeyDefinition($tableForeignKey)
  149. {
  150. return new ForeignKeyConstraint(
  151. $tableForeignKey['local_columns'],
  152. $tableForeignKey['foreign_table'],
  153. $tableForeignKey['foreign_columns'],
  154. $tableForeignKey['name'],
  155. $tableForeignKey['options']
  156. );
  157. }
  158. /**
  159. * {@inheritdoc}
  160. */
  161. protected function _getPortableTableDefinition($table)
  162. {
  163. if (isset($table['schema_name']) && $table['schema_name'] !== 'dbo') {
  164. return $table['schema_name'] . '.' . $table['name'];
  165. }
  166. return $table['name'];
  167. }
  168. /**
  169. * {@inheritdoc}
  170. */
  171. protected function _getPortableDatabaseDefinition($database)
  172. {
  173. return $database['name'];
  174. }
  175. /**
  176. * {@inheritdoc}
  177. */
  178. protected function getPortableNamespaceDefinition(array $namespace)
  179. {
  180. return $namespace['name'];
  181. }
  182. /**
  183. * {@inheritdoc}
  184. */
  185. protected function _getPortableViewDefinition($view)
  186. {
  187. // @todo
  188. return new View($view['name'], null);
  189. }
  190. /**
  191. * {@inheritdoc}
  192. */
  193. public function listTableIndexes($table)
  194. {
  195. $sql = $this->_platform->getListTableIndexesSQL($table, $this->_conn->getDatabase());
  196. try {
  197. $tableIndexes = $this->_conn->fetchAll($sql);
  198. } catch (PDOException $e) {
  199. if ($e->getCode() === 'IMSSP') {
  200. return [];
  201. }
  202. throw $e;
  203. } catch (DBALException $e) {
  204. if (strpos($e->getMessage(), 'SQLSTATE [01000, 15472]') === 0) {
  205. return [];
  206. }
  207. throw $e;
  208. }
  209. return $this->_getPortableTableIndexesList($tableIndexes, $table);
  210. }
  211. /**
  212. * {@inheritdoc}
  213. */
  214. public function alterTable(TableDiff $tableDiff)
  215. {
  216. if (count($tableDiff->removedColumns) > 0) {
  217. foreach ($tableDiff->removedColumns as $col) {
  218. $columnConstraintSql = $this->getColumnConstraintSQL($tableDiff->name, $col->getName());
  219. foreach ($this->_conn->fetchAll($columnConstraintSql) as $constraint) {
  220. $this->_conn->exec(
  221. sprintf(
  222. 'ALTER TABLE %s DROP CONSTRAINT %s',
  223. $tableDiff->name,
  224. $constraint['Name']
  225. )
  226. );
  227. }
  228. }
  229. }
  230. parent::alterTable($tableDiff);
  231. }
  232. /**
  233. * Returns the SQL to retrieve the constraints for a given column.
  234. *
  235. * @param string $table
  236. * @param string $column
  237. *
  238. * @return string
  239. */
  240. private function getColumnConstraintSQL($table, $column)
  241. {
  242. return "SELECT SysObjects.[Name]
  243. FROM SysObjects INNER JOIN (SELECT [Name],[ID] FROM SysObjects WHERE XType = 'U') AS Tab
  244. ON Tab.[ID] = Sysobjects.[Parent_Obj]
  245. INNER JOIN sys.default_constraints DefCons ON DefCons.[object_id] = Sysobjects.[ID]
  246. INNER JOIN SysColumns Col ON Col.[ColID] = DefCons.[parent_column_id] AND Col.[ID] = Tab.[ID]
  247. WHERE Col.[Name] = " . $this->_conn->quote($column) . ' AND Tab.[Name] = ' . $this->_conn->quote($table) . '
  248. ORDER BY Col.[Name]';
  249. }
  250. /**
  251. * Closes currently active connections on the given database.
  252. *
  253. * This is useful to force DROP DATABASE operations which could fail because of active connections.
  254. *
  255. * @param string $database The name of the database to close currently active connections for.
  256. *
  257. * @return void
  258. */
  259. private function closeActiveDatabaseConnections($database)
  260. {
  261. $database = new Identifier($database);
  262. $this->_execSql(
  263. sprintf(
  264. 'ALTER DATABASE %s SET SINGLE_USER WITH ROLLBACK IMMEDIATE',
  265. $database->getQuotedName($this->_platform)
  266. )
  267. );
  268. }
  269. }