Exception
2 years ago
Visitor
9 months ago
AbstractAsset.php
2 years ago
AbstractSchemaManager.php
9 months ago
Column.php
2 years ago
ColumnDiff.php
2 years ago
Comparator.php
2 years ago
Constraint.php
2 years ago
DB2SchemaManager.php
2 years ago
DefaultSchemaManagerFactory.php
2 years ago
ForeignKeyConstraint.php
9 months ago
Identifier.php
2 years ago
Index.php
2 years ago
LegacySchemaManagerFactory.php
2 years ago
MySQLSchemaManager.php
9 months ago
OracleSchemaManager.php
2 years ago
PostgreSQLSchemaManager.php
9 months ago
SQLServerSchemaManager.php
9 months ago
Schema.php
2 years ago
SchemaConfig.php
2 years ago
SchemaDiff.php
2 years ago
SchemaException.php
2 years ago
SchemaManagerFactory.php
2 years ago
Sequence.php
2 years ago
SqliteSchemaManager.php
8 months ago
Table.php
2 years ago
TableDiff.php
2 years ago
UniqueConstraint.php
2 years ago
View.php
2 years ago
SQLServerSchemaManager.php
390 lines
| 1 | <?php |
| 2 | namespace MailPoetVendor\Doctrine\DBAL\Schema; |
| 3 | if (!defined('ABSPATH')) exit; |
| 4 | use MailPoetVendor\Doctrine\DBAL\Exception; |
| 5 | use MailPoetVendor\Doctrine\DBAL\Platforms\SQLServer; |
| 6 | use MailPoetVendor\Doctrine\DBAL\Platforms\SQLServerPlatform; |
| 7 | use MailPoetVendor\Doctrine\DBAL\Result; |
| 8 | use MailPoetVendor\Doctrine\DBAL\Types\Type; |
| 9 | use MailPoetVendor\Doctrine\Deprecations\Deprecation; |
| 10 | use function array_change_key_case; |
| 11 | use function assert; |
| 12 | use function count; |
| 13 | use function explode; |
| 14 | use function implode; |
| 15 | use function is_string; |
| 16 | use function preg_match; |
| 17 | use function sprintf; |
| 18 | use function str_replace; |
| 19 | use function strpos; |
| 20 | use function strtok; |
| 21 | use const CASE_LOWER; |
| 22 | class SQLServerSchemaManager extends AbstractSchemaManager |
| 23 | { |
| 24 | private ?string $databaseCollation = null; |
| 25 | public function listTableNames() |
| 26 | { |
| 27 | return $this->doListTableNames(); |
| 28 | } |
| 29 | public function listTables() |
| 30 | { |
| 31 | return $this->doListTables(); |
| 32 | } |
| 33 | public function listTableDetails($name) |
| 34 | { |
| 35 | Deprecation::triggerIfCalledFromOutside('doctrine/dbal', 'https://github.com/doctrine/dbal/pull/5595', '%s is deprecated. Use introspectTable() instead.', __METHOD__); |
| 36 | return $this->doListTableDetails($name); |
| 37 | } |
| 38 | public function listTableColumns($table, $database = null) |
| 39 | { |
| 40 | return $this->doListTableColumns($table, $database); |
| 41 | } |
| 42 | public function listTableIndexes($table) |
| 43 | { |
| 44 | return $this->doListTableIndexes($table); |
| 45 | } |
| 46 | public function listTableForeignKeys($table, $database = null) |
| 47 | { |
| 48 | return $this->doListTableForeignKeys($table, $database); |
| 49 | } |
| 50 | public function listSchemaNames() : array |
| 51 | { |
| 52 | return $this->_conn->fetchFirstColumn(<<<'SQL' |
| 53 | SELECT name |
| 54 | FROM sys.schemas |
| 55 | WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys') |
| 56 | SQL |
| 57 | ); |
| 58 | } |
| 59 | protected function _getPortableSequenceDefinition($sequence) |
| 60 | { |
| 61 | return new Sequence($sequence['name'], (int) $sequence['increment'], (int) $sequence['start_value']); |
| 62 | } |
| 63 | protected function _getPortableTableColumnDefinition($tableColumn) |
| 64 | { |
| 65 | $dbType = strtok($tableColumn['type'], '(), '); |
| 66 | assert(is_string($dbType)); |
| 67 | $fixed = null; |
| 68 | $length = (int) $tableColumn['length']; |
| 69 | $default = $tableColumn['default']; |
| 70 | if (!isset($tableColumn['name'])) { |
| 71 | $tableColumn['name'] = ''; |
| 72 | } |
| 73 | if ($default !== null) { |
| 74 | $default = $this->parseDefaultExpression($default); |
| 75 | } |
| 76 | switch ($dbType) { |
| 77 | case 'nchar': |
| 78 | case 'ntext': |
| 79 | // Unicode data requires 2 bytes per character |
| 80 | $length /= 2; |
| 81 | break; |
| 82 | case 'nvarchar': |
| 83 | if ($length === -1) { |
| 84 | break; |
| 85 | } |
| 86 | // Unicode data requires 2 bytes per character |
| 87 | $length /= 2; |
| 88 | break; |
| 89 | case 'varchar': |
| 90 | // TEXT type is returned as VARCHAR(MAX) with a length of -1 |
| 91 | if ($length === -1) { |
| 92 | $dbType = 'text'; |
| 93 | } |
| 94 | break; |
| 95 | case 'varbinary': |
| 96 | if ($length === -1) { |
| 97 | $dbType = 'blob'; |
| 98 | } |
| 99 | break; |
| 100 | } |
| 101 | if ($dbType === 'char' || $dbType === 'nchar' || $dbType === 'binary') { |
| 102 | $fixed = \true; |
| 103 | } |
| 104 | $type = $this->_platform->getDoctrineTypeMapping($dbType); |
| 105 | $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type); |
| 106 | $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type); |
| 107 | $options = ['unsigned' => \false, 'fixed' => (bool) $fixed, 'default' => $default, 'notnull' => (bool) $tableColumn['notnull'], 'scale' => $tableColumn['scale'], 'precision' => $tableColumn['precision'], 'autoincrement' => (bool) $tableColumn['autoincrement'], 'comment' => $tableColumn['comment'] !== '' ? $tableColumn['comment'] : null]; |
| 108 | if ($length !== 0 && ($type === 'text' || $type === 'string' || $type === 'binary')) { |
| 109 | $options['length'] = $length; |
| 110 | } |
| 111 | $column = new Column($tableColumn['name'], Type::getType($type), $options); |
| 112 | if (isset($tableColumn['collation']) && $tableColumn['collation'] !== 'NULL') { |
| 113 | $column->setPlatformOption('collation', $tableColumn['collation']); |
| 114 | } |
| 115 | return $column; |
| 116 | } |
| 117 | private function parseDefaultExpression(string $value) : ?string |
| 118 | { |
| 119 | while (preg_match('/^\\((.*)\\)$/s', $value, $matches) === 1) { |
| 120 | $value = $matches[1]; |
| 121 | } |
| 122 | if ($value === 'NULL') { |
| 123 | return null; |
| 124 | } |
| 125 | if (preg_match('/^\'(.*)\'$/s', $value, $matches) === 1) { |
| 126 | $value = str_replace("''", "'", $matches[1]); |
| 127 | } |
| 128 | if ($value === 'getdate()') { |
| 129 | return $this->_platform->getCurrentTimestampSQL(); |
| 130 | } |
| 131 | return $value; |
| 132 | } |
| 133 | protected function _getPortableTableForeignKeysList($tableForeignKeys) |
| 134 | { |
| 135 | $foreignKeys = []; |
| 136 | foreach ($tableForeignKeys as $tableForeignKey) { |
| 137 | $name = $tableForeignKey['ForeignKey']; |
| 138 | if (!isset($foreignKeys[$name])) { |
| 139 | $referencedTableName = $tableForeignKey['ReferenceTableName']; |
| 140 | if ($tableForeignKey['ReferenceSchemaName'] !== 'dbo') { |
| 141 | $referencedTableName = $tableForeignKey['ReferenceSchemaName'] . '.' . $referencedTableName; |
| 142 | } |
| 143 | $foreignKeys[$name] = ['local_columns' => [$tableForeignKey['ColumnName']], 'foreign_table' => $referencedTableName, 'foreign_columns' => [$tableForeignKey['ReferenceColumnName']], 'name' => $name, 'options' => ['onUpdate' => str_replace('_', ' ', $tableForeignKey['update_referential_action_desc']), 'onDelete' => str_replace('_', ' ', $tableForeignKey['delete_referential_action_desc'])]]; |
| 144 | } else { |
| 145 | $foreignKeys[$name]['local_columns'][] = $tableForeignKey['ColumnName']; |
| 146 | $foreignKeys[$name]['foreign_columns'][] = $tableForeignKey['ReferenceColumnName']; |
| 147 | } |
| 148 | } |
| 149 | return parent::_getPortableTableForeignKeysList($foreignKeys); |
| 150 | } |
| 151 | protected function _getPortableTableIndexesList($tableIndexes, $tableName = null) |
| 152 | { |
| 153 | foreach ($tableIndexes as &$tableIndex) { |
| 154 | $tableIndex['non_unique'] = (bool) $tableIndex['non_unique']; |
| 155 | $tableIndex['primary'] = (bool) $tableIndex['primary']; |
| 156 | $tableIndex['flags'] = $tableIndex['flags'] ? [$tableIndex['flags']] : null; |
| 157 | } |
| 158 | return parent::_getPortableTableIndexesList($tableIndexes, $tableName); |
| 159 | } |
| 160 | protected function _getPortableTableForeignKeyDefinition($tableForeignKey) |
| 161 | { |
| 162 | return new ForeignKeyConstraint($tableForeignKey['local_columns'], $tableForeignKey['foreign_table'], $tableForeignKey['foreign_columns'], $tableForeignKey['name'], $tableForeignKey['options']); |
| 163 | } |
| 164 | protected function _getPortableTableDefinition($table) |
| 165 | { |
| 166 | if ($table['schema_name'] !== 'dbo') { |
| 167 | return $table['schema_name'] . '.' . $table['table_name']; |
| 168 | } |
| 169 | return $table['table_name']; |
| 170 | } |
| 171 | protected function _getPortableDatabaseDefinition($database) |
| 172 | { |
| 173 | return $database['name']; |
| 174 | } |
| 175 | protected function getPortableNamespaceDefinition(array $namespace) |
| 176 | { |
| 177 | Deprecation::triggerIfCalledFromOutside('doctrine/dbal', 'https://github.com/doctrine/dbal/issues/4503', 'SQLServerSchemaManager::getPortableNamespaceDefinition() is deprecated,' . ' use SQLServerSchemaManager::listSchemaNames() instead.'); |
| 178 | return $namespace['name']; |
| 179 | } |
| 180 | protected function _getPortableViewDefinition($view) |
| 181 | { |
| 182 | // @todo |
| 183 | return new View($view['name'], $view['definition']); |
| 184 | } |
| 185 | public function alterTable(TableDiff $tableDiff) |
| 186 | { |
| 187 | $droppedColumns = $tableDiff->getDroppedColumns(); |
| 188 | if (count($droppedColumns) > 0) { |
| 189 | $tableName = ($tableDiff->getOldTable() ?? $tableDiff->getName($this->_platform))->getName(); |
| 190 | foreach ($droppedColumns as $col) { |
| 191 | foreach ($this->getColumnConstraints($tableName, $col->getName()) as $constraint) { |
| 192 | $this->_conn->executeStatement(sprintf('ALTER TABLE %s DROP CONSTRAINT %s', $tableName, $constraint)); |
| 193 | } |
| 194 | } |
| 195 | } |
| 196 | parent::alterTable($tableDiff); |
| 197 | } |
| 198 | private function getColumnConstraints(string $table, string $column) : iterable |
| 199 | { |
| 200 | return $this->_conn->iterateColumn(<<<'SQL' |
| 201 | SELECT o.name |
| 202 | FROM sys.objects o |
| 203 | INNER JOIN sys.objects t |
| 204 | ON t.object_id = o.parent_object_id |
| 205 | AND t.type = 'U' |
| 206 | INNER JOIN sys.default_constraints dc |
| 207 | ON dc.object_id = o.object_id |
| 208 | INNER JOIN sys.columns c |
| 209 | ON c.column_id = dc.parent_column_id |
| 210 | AND c.object_id = t.object_id |
| 211 | WHERE t.name = ? |
| 212 | AND c.name = ? |
| 213 | SQL |
| 214 | , [$table, $column]); |
| 215 | } |
| 216 | public function createComparator() : Comparator |
| 217 | { |
| 218 | return new SQLServer\Comparator($this->_platform, $this->getDatabaseCollation()); |
| 219 | } |
| 220 | private function getDatabaseCollation() : string |
| 221 | { |
| 222 | if ($this->databaseCollation === null) { |
| 223 | $databaseCollation = $this->_conn->fetchOne('SELECT collation_name FROM sys.databases WHERE name = ' . $this->_platform->getCurrentDatabaseExpression()); |
| 224 | // a database is always selected, even if omitted in the connection parameters |
| 225 | assert(is_string($databaseCollation)); |
| 226 | $this->databaseCollation = $databaseCollation; |
| 227 | } |
| 228 | return $this->databaseCollation; |
| 229 | } |
| 230 | protected function selectTableNames(string $databaseName) : Result |
| 231 | { |
| 232 | // The "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams |
| 233 | $sql = <<<'SQL' |
| 234 | SELECT name AS table_name, |
| 235 | SCHEMA_NAME(schema_id) AS schema_name |
| 236 | FROM sys.objects |
| 237 | WHERE type = 'U' |
| 238 | AND name != 'sysdiagrams' |
| 239 | ORDER BY name |
| 240 | SQL; |
| 241 | return $this->_conn->executeQuery($sql); |
| 242 | } |
| 243 | protected function selectTableColumns(string $databaseName, ?string $tableName = null) : Result |
| 244 | { |
| 245 | $sql = 'SELECT'; |
| 246 | if ($tableName === null) { |
| 247 | $sql .= ' obj.name AS table_name, scm.name AS schema_name,'; |
| 248 | } |
| 249 | $sql .= <<<'SQL' |
| 250 | col.name, |
| 251 | type.name AS type, |
| 252 | col.max_length AS length, |
| 253 | ~col.is_nullable AS notnull, |
| 254 | def.definition AS [default], |
| 255 | col.scale, |
| 256 | col.precision, |
| 257 | col.is_identity AS autoincrement, |
| 258 | col.collation_name AS collation, |
| 259 | -- CAST avoids driver error for sql_variant type |
| 260 | CAST(prop.value AS NVARCHAR(MAX)) AS comment |
| 261 | FROM sys.columns AS col |
| 262 | JOIN sys.types AS type |
| 263 | ON col.user_type_id = type.user_type_id |
| 264 | JOIN sys.objects AS obj |
| 265 | ON col.object_id = obj.object_id |
| 266 | JOIN sys.schemas AS scm |
| 267 | ON obj.schema_id = scm.schema_id |
| 268 | LEFT JOIN sys.default_constraints def |
| 269 | ON col.default_object_id = def.object_id |
| 270 | AND col.object_id = def.parent_object_id |
| 271 | LEFT JOIN sys.extended_properties AS prop |
| 272 | ON obj.object_id = prop.major_id |
| 273 | AND col.column_id = prop.minor_id |
| 274 | AND prop.name = 'MS_Description' |
| 275 | SQL; |
| 276 | // The "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams |
| 277 | $conditions = ["obj.type = 'U'", "obj.name != 'sysdiagrams'"]; |
| 278 | $params = []; |
| 279 | if ($tableName !== null) { |
| 280 | $conditions[] = $this->getTableWhereClause($tableName, 'scm.name', 'obj.name'); |
| 281 | } |
| 282 | $sql .= ' WHERE ' . implode(' AND ', $conditions); |
| 283 | return $this->_conn->executeQuery($sql, $params); |
| 284 | } |
| 285 | protected function selectIndexColumns(string $databaseName, ?string $tableName = null) : Result |
| 286 | { |
| 287 | $sql = 'SELECT'; |
| 288 | if ($tableName === null) { |
| 289 | $sql .= ' tbl.name AS table_name, scm.name AS schema_name,'; |
| 290 | } |
| 291 | $sql .= <<<'SQL' |
| 292 | idx.name AS key_name, |
| 293 | col.name AS column_name, |
| 294 | ~idx.is_unique AS non_unique, |
| 295 | idx.is_primary_key AS [primary], |
| 296 | CASE idx.type |
| 297 | WHEN '1' THEN 'clustered' |
| 298 | WHEN '2' THEN 'nonclustered' |
| 299 | ELSE NULL |
| 300 | END AS flags |
| 301 | FROM sys.tables AS tbl |
| 302 | JOIN sys.schemas AS scm |
| 303 | ON tbl.schema_id = scm.schema_id |
| 304 | JOIN sys.indexes AS idx |
| 305 | ON tbl.object_id = idx.object_id |
| 306 | JOIN sys.index_columns AS idxcol |
| 307 | ON idx.object_id = idxcol.object_id |
| 308 | AND idx.index_id = idxcol.index_id |
| 309 | JOIN sys.columns AS col |
| 310 | ON idxcol.object_id = col.object_id |
| 311 | AND idxcol.column_id = col.column_id |
| 312 | SQL; |
| 313 | $conditions = []; |
| 314 | $params = []; |
| 315 | if ($tableName !== null) { |
| 316 | $conditions[] = $this->getTableWhereClause($tableName, 'scm.name', 'tbl.name'); |
| 317 | $sql .= ' WHERE ' . implode(' AND ', $conditions); |
| 318 | } |
| 319 | $sql .= ' ORDER BY idx.index_id, idxcol.key_ordinal'; |
| 320 | return $this->_conn->executeQuery($sql, $params); |
| 321 | } |
| 322 | protected function selectForeignKeyColumns(string $databaseName, ?string $tableName = null) : Result |
| 323 | { |
| 324 | $sql = 'SELECT'; |
| 325 | if ($tableName === null) { |
| 326 | $sql .= ' OBJECT_NAME (f.parent_object_id) AS table_name, SCHEMA_NAME(f.schema_id) AS schema_name,'; |
| 327 | } |
| 328 | $sql .= <<<'SQL' |
| 329 | f.name AS ForeignKey, |
| 330 | SCHEMA_NAME (f.SCHEMA_ID) AS SchemaName, |
| 331 | OBJECT_NAME (f.parent_object_id) AS TableName, |
| 332 | COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName, |
| 333 | SCHEMA_NAME (o.SCHEMA_ID) ReferenceSchemaName, |
| 334 | OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, |
| 335 | COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName, |
| 336 | f.delete_referential_action_desc, |
| 337 | f.update_referential_action_desc |
| 338 | FROM sys.foreign_keys AS f |
| 339 | INNER JOIN sys.foreign_key_columns AS fc |
| 340 | INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id |
| 341 | ON f.OBJECT_ID = fc.constraint_object_id |
| 342 | SQL; |
| 343 | $conditions = []; |
| 344 | $params = []; |
| 345 | if ($tableName !== null) { |
| 346 | $conditions[] = $this->getTableWhereClause($tableName, 'SCHEMA_NAME(f.schema_id)', 'OBJECT_NAME(f.parent_object_id)'); |
| 347 | $sql .= ' WHERE ' . implode(' AND ', $conditions); |
| 348 | } |
| 349 | $sql .= ' ORDER BY fc.constraint_column_id'; |
| 350 | return $this->_conn->executeQuery($sql, $params); |
| 351 | } |
| 352 | protected function fetchTableOptionsByTable(string $databaseName, ?string $tableName = null) : array |
| 353 | { |
| 354 | $sql = <<<'SQL' |
| 355 | SELECT |
| 356 | scm.name AS schema_name, |
| 357 | tbl.name AS table_name, |
| 358 | p.value AS [table_comment] |
| 359 | FROM |
| 360 | sys.tables AS tbl |
| 361 | JOIN sys.schemas AS scm |
| 362 | ON tbl.schema_id = scm.schema_id |
| 363 | INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1 |
| 364 | SQL; |
| 365 | $conditions = ["p.name = N'MS_Description'"]; |
| 366 | if ($tableName !== null) { |
| 367 | $conditions[] = $this->getTableWhereClause($tableName, 'scm.name', 'tbl.name'); |
| 368 | } |
| 369 | $sql .= ' WHERE ' . implode(' AND ', $conditions); |
| 370 | $tableOptions = []; |
| 371 | foreach ($this->_conn->iterateAssociative($sql) as $data) { |
| 372 | $data = array_change_key_case($data, CASE_LOWER); |
| 373 | $tableOptions[$this->_getPortableTableDefinition($data)] = ['comment' => $data['table_comment']]; |
| 374 | } |
| 375 | return $tableOptions; |
| 376 | } |
| 377 | private function getTableWhereClause($table, $schemaColumn, $tableColumn) : string |
| 378 | { |
| 379 | if (strpos($table, '.') !== \false) { |
| 380 | [$schema, $table] = explode('.', $table); |
| 381 | $schema = $this->_platform->quoteStringLiteral($schema); |
| 382 | $table = $this->_platform->quoteStringLiteral($table); |
| 383 | } else { |
| 384 | $schema = 'SCHEMA_NAME()'; |
| 385 | $table = $this->_platform->quoteStringLiteral($table); |
| 386 | } |
| 387 | return sprintf('(%s = %s AND %s = %s)', $tableColumn, $table, $schemaColumn, $schema); |
| 388 | } |
| 389 | } |
| 390 |