SQLServerSchemaManager::getColumnConstraintSQL() - Code Metrics - Inspection of "Fixed handling special characters in default value..." - doctrine/dbal - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#2960)

by Sergei

created 2019-04-10 22:45 UTC

SQLServerSchemaManager::getColumnConstraintSQL() A

↳ Parent: SQLServerSchemaManager

Complexity

Conditions	1
Paths	1

Size

Total Lines	8
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
eloc	2
dl	0
loc	8
ccs	2
cts	2
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	2
crap	1

<?php

namespace Doctrine\DBAL\Schema;

use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Driver\DriverException;
use Doctrine\DBAL\Types\Type;
use PDOException;
use Throwable;
use function assert;
use function count;
use function in_array;
use function is_string;
use function preg_match;
use function sprintf;
use function str_replace;
use function strpos;
use function strtok;

/**
 * SQL Server Schema Manager.
 */
class SQLServerSchemaManager extends AbstractSchemaManager
{
    /**
     * {@inheritdoc}
     */
    public function dropDatabase($database)
    {
        try {
            parent::dropDatabase($database);
        } catch (DBALException $exception) {
            $exception = $exception->getPrevious();
            assert($exception instanceof Throwable);

            if (! $exception instanceof DriverException) {
                throw $exception;
            }

            // If we have a error code 3702, the drop database operation failed
            // because of active connections on the database.
            // To force dropping the database, we first have to close all active connections
            // on that database and issue the drop database operation again.
            if ($exception->getErrorCode() !== 3702) {
                throw $exception;
            }

            $this->closeActiveDatabaseConnections($database);

            parent::dropDatabase($database);
        }
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableSequenceDefinition($sequence)
    {
        return new Sequence($sequence['name'], (int) $sequence['increment'], (int) $sequence['start_value']);
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableColumnDefinition($tableColumn)
    {
        $dbType = strtok($tableColumn['type'], '(), ');
        assert(is_string($dbType));

        $fixed   = null;
        $length  = (int) $tableColumn['length'];
        $default = $tableColumn['default'];

        if (! isset($tableColumn['name'])) {
            $tableColumn['name'] = '';
        }

        if ($default !== null) {
            $default = $this->parseDefaultExpression($default);
        }

        switch ($dbType) {
            case 'nchar':
            case 'nvarchar':
            case 'ntext':
                // Unicode data requires 2 bytes per character
                $length /= 2;
                break;
            case 'varchar':
                // TEXT type is returned as VARCHAR(MAX) with a length of -1
                if ($length === -1) {
                    $dbType = 'text';
                }
                break;
        }

        if ($dbType === 'char' || $dbType === 'nchar' || $dbType === 'binary') {
            $fixed = true;
        }

        $type                   = $this->_platform->getDoctrineTypeMapping($dbType);
        $type                   = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
        $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);

        $options = [
            'length'        => $length === 0 || ! in_array($type, ['text', 'string']) ? null : $length,
            'unsigned'      => false,
            'fixed'         => (bool) $fixed,
            'default'       => $default !== 'NULL' ? $default : null,
            'notnull'       => (bool) $tableColumn['notnull'],
            'scale'         => $tableColumn['scale'],
            'precision'     => $tableColumn['precision'],
            'autoincrement' => (bool) $tableColumn['autoincrement'],
            'comment'       => $tableColumn['comment'] !== '' ? $tableColumn['comment'] : null,
        ];

        $column = new Column($tableColumn['name'], Type::getType($type), $options);

        if (isset($tableColumn['collation']) && $tableColumn['collation'] !== 'NULL') {
            $column->setPlatformOption('collation', $tableColumn['collation']);
        }

        return $column;
    }

    private function parseDefaultExpression(string $value) : string
    {
        while (preg_match('/^\((.*)\)$/s', $value, $matches)) {
            $value = $matches[1];
        }

        if (preg_match('/^\'(.*)\'$/s', $value, $matches)) {
            $value = str_replace("''", "'", $matches[1]);
        }

        if ($value === 'getdate()') {
            return $this->_platform->getCurrentTimestampSQL();
        }

        return $value;
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableForeignKeysList($tableForeignKeys)
    {
        $foreignKeys = [];

        foreach ($tableForeignKeys as $tableForeignKey) {
            if (! isset($foreignKeys[$tableForeignKey['ForeignKey']])) {
                $foreignKeys[$tableForeignKey['ForeignKey']] = [
                    'local_columns' => [$tableForeignKey['ColumnName']],
                    'foreign_table' => $tableForeignKey['ReferenceTableName'],
                    'foreign_columns' => [$tableForeignKey['ReferenceColumnName']],
                    'name' => $tableForeignKey['ForeignKey'],
                    'options' => [
                        'onUpdate' => str_replace('_', ' ', $tableForeignKey['update_referential_action_desc']),
                        'onDelete' => str_replace('_', ' ', $tableForeignKey['delete_referential_action_desc']),
                    ],
                ];
            } else {
                $foreignKeys[$tableForeignKey['ForeignKey']]['local_columns'][]   = $tableForeignKey['ColumnName'];
                $foreignKeys[$tableForeignKey['ForeignKey']]['foreign_columns'][] = $tableForeignKey['ReferenceColumnName'];
            }
        }

        return parent::_getPortableTableForeignKeysList($foreignKeys);
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableIndexesList($tableIndexRows, $tableName = null)
    {
        foreach ($tableIndexRows as &$tableIndex) {
            $tableIndex['non_unique'] = (bool) $tableIndex['non_unique'];
            $tableIndex['primary']    = (bool) $tableIndex['primary'];
            $tableIndex['flags']      = $tableIndex['flags'] ? [$tableIndex['flags']] : null;
        }

        return parent::_getPortableTableIndexesList($tableIndexRows, $tableName);
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableForeignKeyDefinition($tableForeignKey)
    {
        return new ForeignKeyConstraint(
            $tableForeignKey['local_columns'],
            $tableForeignKey['foreign_table'],
            $tableForeignKey['foreign_columns'],
            $tableForeignKey['name'],
            $tableForeignKey['options']
        );
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableDefinition($table)
    {
        if (isset($table['schema_name']) && $table['schema_name'] !== 'dbo') {
            return $table['schema_name'] . '.' . $table['name'];
        }

        return $table['name'];
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableDatabaseDefinition($database)
    {
        return $database['name'];
    }

    /**
     * {@inheritdoc}
     */
    protected function getPortableNamespaceDefinition(array $namespace)
    {
        return $namespace['name'];
    }

    /**
     * {@inheritdoc}
     */
    protected function _getPortableViewDefinition($view)
    {
        // @todo
        return new View($view['name'], '');
    }

    /**
     * {@inheritdoc}
     */
    public function listTableIndexes($table)
    {
        $sql = $this->_platform->getListTableIndexesSQL($table, $this->_conn->getDatabase());

        try {
            $tableIndexes = $this->_conn->fetchAll($sql);
        } catch (PDOException $e) {
            if ($e->getCode() === 'IMSSP') {
                return [];
            }

            throw $e;
        } catch (DBALException $e) {
            if (strpos($e->getMessage(), 'SQLSTATE [01000, 15472]') === 0) {
                return [];
            }

            throw $e;
        }

        return $this->_getPortableTableIndexesList($tableIndexes, $table);
    }

    /**
     * {@inheritdoc}
     */
    public function alterTable(TableDiff $tableDiff)
    {
        if (count($tableDiff->removedColumns) > 0) {
            foreach ($tableDiff->removedColumns as $col) {
                $columnConstraintSql = $this->getColumnConstraintSQL($tableDiff->name, $col->getName());
                foreach ($this->_conn->fetchAll($columnConstraintSql) as $constraint) {
                    $this->_conn->exec(
                        sprintf(
                            'ALTER TABLE %s DROP CONSTRAINT %s',
                            $tableDiff->name,
                            $constraint['Name']
                        )
                    );
                }
            }
        }

        parent::alterTable($tableDiff);
    }

    /**
     * Returns the SQL to retrieve the constraints for a given column.
     *
     * @param string $table
     * @param string $column
     *
     * @return string
     */
    private function getColumnConstraintSQL($table, $column)
    {
        return "SELECT SysObjects.[Name]
            FROM SysObjects INNER JOIN (SELECT [Name],[ID] FROM SysObjects WHERE XType = 'U') AS Tab
            ON Tab.[ID] = Sysobjects.[Parent_Obj]
            INNER JOIN sys.default_constraints DefCons ON DefCons.[object_id] = Sysobjects.[ID]
            INNER JOIN SysColumns Col ON Col.[ColID] = DefCons.[parent_column_id] AND Col.[ID] = Tab.[ID]
            WHERE Col.[Name] = " . $this->_conn->quote($column) . ' AND Tab.[Name] = ' . $this->_conn->quote($table) . '
            ORDER BY Col.[Name]';
    }

    /**
     * Closes currently active connections on the given database.
     *
     * This is useful to force DROP DATABASE operations which could fail because of active connections.
     *
     * @param string $database The name of the database to close currently active connections for.
     *
     * @return void
     */
    private function closeActiveDatabaseConnections($database)
    {
        $database = new Identifier($database);

        $this->_execSql(
            sprintf(
                'ALTER DATABASE %s SET SINGLE_USER WITH ROLLBACK IMMEDIATE',
                $database->getQuotedName($this->_platform)
            )
        );
    }
}


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

doctrine / dbal

Pull Request — master (#2960)

SQLServerSchemaManager::getColumnConstraintSQL() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like