SQLServerSchemaManager.php 10.8 KB
Newer Older
romanb's avatar
romanb committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
Benjamin Eberlei's avatar
Benjamin Eberlei committed
16
 * and is licensed under the MIT license. For more information, see
Benjamin Morel's avatar
Benjamin Morel committed
17
 * <http://www.doctrine-project.org>.
romanb's avatar
romanb committed
18 19
 */

20
namespace Doctrine\DBAL\Schema;
romanb's avatar
romanb committed
21

22 23
use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Driver\DriverException;
24
use Doctrine\DBAL\Types\Type;
25 26 27 28 29 30 31 32
use function count;
use function in_array;
use function preg_replace;
use function sprintf;
use function str_replace;
use function strpos;
use function strtok;
use function trim;
33

romanb's avatar
romanb committed
34
/**
Benjamin Morel's avatar
Benjamin Morel committed
35
 * SQL Server Schema Manager.
romanb's avatar
romanb committed
36
 *
37
 * @license http://www.opensource.org/licenses/mit-license.php MIT
Benjamin Morel's avatar
Benjamin Morel committed
38 39 40 41 42
 * @author  Konsta Vesterinen <kvesteri@cc.hut.fi>
 * @author  Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
 * @author  Juozas Kaziukenas <juozas@juokaz.com>
 * @author  Steve Müller <st.mueller@dzh-online.de>
 * @since   2.0
romanb's avatar
romanb committed
43
 */
44
class SQLServerSchemaManager extends AbstractSchemaManager
45
{
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
    /**
     * {@inheritdoc}
     */
    public function dropDatabase($database)
    {
        try {
            parent::dropDatabase($database);
        } catch (DBALException $exception) {
            $exception = $exception->getPrevious();

            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);
        }
    }

74 75 76 77 78
    /**
     * {@inheritdoc}
     */
    protected function _getPortableSequenceDefinition($sequence)
    {
79
        return new Sequence($sequence['name'], (int) $sequence['increment'], (int) $sequence['start_value']);
80 81
    }

romanb's avatar
romanb committed
82
    /**
83
     * {@inheritdoc}
romanb's avatar
romanb committed
84
     */
85
    protected function _getPortableTableColumnDefinition($tableColumn)
romanb's avatar
romanb committed
86
    {
87
        $dbType = strtok($tableColumn['type'], '(), ');
88 89 90
        $fixed = null;
        $length = (int) $tableColumn['length'];
        $default = $tableColumn['default'];
romanb's avatar
romanb committed
91

92
        if (!isset($tableColumn['name'])) {
93
            $tableColumn['name'] = '';
romanb's avatar
romanb committed
94
        }
95 96

        while ($default != ($default2 = preg_replace("/^\((.*)\)$/", '$1', $default))) {
97
            $default = trim($default2, "'");
98 99 100 101

            if ($default == 'getdate()') {
                $default = $this->_platform->getCurrentTimestampSQL();
            }
102
        }
103

104 105 106 107 108 109 110 111 112 113 114 115 116 117
        switch ($dbType) {
            case 'nchar':
            case 'nvarchar':
            case 'ntext':
                // Unicode data requires 2 bytes per character
                $length = $length / 2;
                break;
            case 'varchar':
                // TEXT type is returned as VARCHAR(MAX) with a length of -1
                if ($length == -1) {
                    $dbType = 'text';
                }
                break;
        }
118

Steve Müller's avatar
Steve Müller committed
119 120 121 122
        if ('char' === $dbType || 'nchar' === $dbType || 'binary' === $dbType) {
            $fixed = true;
        }

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

127 128
        $options = [
            'length'        => ($length == 0 || !in_array($type, ['text', 'string'])) ? null : $length,
129 130 131 132 133 134
            'unsigned'      => false,
            'fixed'         => (bool) $fixed,
            'default'       => $default !== 'NULL' ? $default : null,
            'notnull'       => (bool) $tableColumn['notnull'],
            'scale'         => $tableColumn['scale'],
            'precision'     => $tableColumn['precision'],
135
            'autoincrement' => (bool) $tableColumn['autoincrement'],
136
            'comment'       => $tableColumn['comment'] !== '' ? $tableColumn['comment'] : null,
137
        ];
138

139
        $column = new Column($tableColumn['name'], Type::getType($type), $options);
140 141 142 143

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

        return $column;
romanb's avatar
romanb committed
146 147
    }

148 149 150 151 152
    /**
     * {@inheritdoc}
     */
    protected function _getPortableTableForeignKeysList($tableForeignKeys)
    {
153
        $foreignKeys = [];
154 155 156

        foreach ($tableForeignKeys as $tableForeignKey) {
            if ( ! isset($foreignKeys[$tableForeignKey['ForeignKey']])) {
157 158
                $foreignKeys[$tableForeignKey['ForeignKey']] = [
                    'local_columns' => [$tableForeignKey['ColumnName']],
159
                    'foreign_table' => $tableForeignKey['ReferenceTableName'],
160
                    'foreign_columns' => [$tableForeignKey['ReferenceColumnName']],
161
                    'name' => $tableForeignKey['ForeignKey'],
162
                    'options' => [
163 164
                        'onUpdate' => str_replace('_', ' ', $tableForeignKey['update_referential_action_desc']),
                        'onDelete' => str_replace('_', ' ', $tableForeignKey['delete_referential_action_desc'])
165 166
                    ]
                ];
167 168 169 170 171 172 173 174 175
            } else {
                $foreignKeys[$tableForeignKey['ForeignKey']]['local_columns'][] = $tableForeignKey['ColumnName'];
                $foreignKeys[$tableForeignKey['ForeignKey']]['foreign_columns'][] = $tableForeignKey['ReferenceColumnName'];
            }
        }

        return parent::_getPortableTableForeignKeysList($foreignKeys);
    }

romanb's avatar
romanb committed
176
    /**
177
     * {@inheritdoc}
romanb's avatar
romanb committed
178
     */
179
    protected function _getPortableTableIndexesList($tableIndexRows, $tableName=null)
romanb's avatar
romanb committed
180
    {
181 182 183
        foreach ($tableIndexRows as &$tableIndex) {
            $tableIndex['non_unique'] = (boolean) $tableIndex['non_unique'];
            $tableIndex['primary'] = (boolean) $tableIndex['primary'];
184
            $tableIndex['flags'] = $tableIndex['flags'] ? [$tableIndex['flags']] : null;
185
        }
romanb's avatar
romanb committed
186

187
        return parent::_getPortableTableIndexesList($tableIndexRows, $tableName);
romanb's avatar
romanb committed
188 189 190
    }

    /**
191
     * {@inheritdoc}
romanb's avatar
romanb committed
192
     */
193
    protected function _getPortableTableForeignKeyDefinition($tableForeignKey)
romanb's avatar
romanb committed
194
    {
195
        return new ForeignKeyConstraint(
196 197 198 199 200
            $tableForeignKey['local_columns'],
            $tableForeignKey['foreign_table'],
            $tableForeignKey['foreign_columns'],
            $tableForeignKey['name'],
            $tableForeignKey['options']
201
        );
romanb's avatar
romanb committed
202 203 204
    }

    /**
Benjamin Morel's avatar
Benjamin Morel committed
205
     * {@inheritdoc}
romanb's avatar
romanb committed
206
     */
207
    protected function _getPortableTableDefinition($table)
romanb's avatar
romanb committed
208
    {
209 210 211 212
        if (isset($table['schema_name']) && $table['schema_name'] !== 'dbo') {
            return $table['schema_name'] . '.' . $table['name'];
        }

213
        return $table['name'];
romanb's avatar
romanb committed
214
    }
215 216

    /**
Benjamin Morel's avatar
Benjamin Morel committed
217
     * {@inheritdoc}
218
     */
219
    protected function _getPortableDatabaseDefinition($database)
220 221 222
    {
        return $database['name'];
    }
223

224 225 226 227 228 229 230 231
    /**
     * {@inheritdoc}
     */
    protected function getPortableNamespaceDefinition(array $namespace)
    {
        return $namespace['name'];
    }

232
    /**
Benjamin Morel's avatar
Benjamin Morel committed
233
     * {@inheritdoc}
234
     */
235
    protected function _getPortableViewDefinition($view)
236
    {
237
        // @todo
238 239
        return new View($view['name'], null);
    }
240

241
    /**
Benjamin Morel's avatar
Benjamin Morel committed
242
     * {@inheritdoc}
243 244 245 246 247 248 249
     */
    public function listTableIndexes($table)
    {
        $sql = $this->_platform->getListTableIndexesSQL($table, $this->_conn->getDatabase());

        try {
            $tableIndexes = $this->_conn->fetchAll($sql);
250
        } catch (\PDOException $e) {
251
            if ($e->getCode() == "IMSSP") {
252
                return [];
253 254 255
            } else {
                throw $e;
            }
256
        } catch (DBALException $e) {
257
            if (strpos($e->getMessage(), 'SQLSTATE [01000, 15472]') === 0) {
258
                return [];
259 260 261
            } else {
                throw $e;
            }
262 263 264 265
        }

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

    /**
Benjamin Morel's avatar
Benjamin Morel committed
268
     * {@inheritdoc}
269 270 271
     */
    public function alterTable(TableDiff $tableDiff)
    {
Steve Müller's avatar
Steve Müller committed
272 273
        if (count($tableDiff->removedColumns) > 0) {
            foreach ($tableDiff->removedColumns as $col) {
274 275 276 277 278 279 280
                $columnConstraintSql = $this->getColumnConstraintSQL($tableDiff->name, $col->getName());
                foreach ($this->_conn->fetchAll($columnConstraintSql) as $constraint) {
                    $this->_conn->exec("ALTER TABLE $tableDiff->name DROP CONSTRAINT " . $constraint['Name']);
                }
            }
        }

Benjamin Morel's avatar
Benjamin Morel committed
281
        parent::alterTable($tableDiff);
282 283 284
    }

    /**
Benjamin Morel's avatar
Benjamin Morel committed
285 286 287 288 289 290
     * Returns the SQL to retrieve the constraints for a given column.
     *
     * @param string $table
     * @param string $column
     *
     * @return string
291 292 293 294 295 296 297 298 299 300 301
     */
    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]";
    }
302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322

    /**
     * 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)
            )
        );
    }
323
}