SqlserverSchema::_convertColumn() - Code Metrics - cakephp/cakephp - Measure and Improve Code Quality continuously with Scrutinizer

SqlserverSchema::_convertColumn() F
last analyzed 2019-12-15 12:43 UTC

↳ Parent: SqlserverSchema

Complexity

Conditions	34
Paths	248

Size

Total Lines

Duplication

Lines	12
Ratio	15.79 %

Importance

Changes

Metric	Value
cc	34
nc	248
nop	4
dl	12
loc	76
rs	2.7333
c	0
b	0
f	0

How to fix Long Method Complexity

<?php
/**
 * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
 * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 *
 * Licensed under The MIT License
 * For full copyright and license information, please see the LICENSE.txt
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 * @link          https://cakephp.org CakePHP(tm) Project
 * @since         3.0.0
 * @license       https://opensource.org/licenses/mit-license.php MIT License
 */
namespace Cake\Database\Schema;

/**
 * Schema management/reflection features for SQLServer.
 */
class SqlserverSchema extends BaseSchema
{
    const DEFAULT_SCHEMA_NAME = 'dbo';

    /**
     * {@inheritDoc}
     */
    public function listTablesSql($config)
    {
        $sql = "SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = ?
            AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
            ORDER BY TABLE_NAME";
        $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];

        return [$sql, [$schema]];
    }

    /**
     * {@inheritDoc}
     */
    public function describeColumnSql($tableName, $config)
    {
        $sql = 'SELECT DISTINCT
            AC.column_id AS [column_id],
            AC.name AS [name],
            TY.name AS [type],
            AC.max_length AS [char_length],
            AC.precision AS [precision],
            AC.scale AS [scale],
            AC.is_identity AS [autoincrement],
            AC.is_nullable AS [null],
            OBJECT_DEFINITION(AC.default_object_id) AS [default],
            AC.collation_name AS [collation_name]
            FROM sys.[objects] T
            INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
            INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
            WHERE T.[name] = ? AND S.[name] = ?
            ORDER BY column_id';

        $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];

        return [$sql, [$tableName, $schema]];
    }

    /**
     * Convert a column definition to the abstract types.
     *
     * The returned type will be a type that
     * Cake\Database\Type can handle.
     *
     * @param string $col The column type
     * @param int|null $length the column length
     * @param int|null $precision The column precision
     * @param int|null $scale The column scale
     * @return array Array of column information.
     * @link https://technet.microsoft.com/en-us/library/ms187752.aspx
     */
    protected function _convertColumn($col, $length = null, $precision = null, $scale = null)
    {
        $col = strtolower($col);
        $length = $length !== null ? (int)$length : $length;
        $precision = $precision !== null ? (int)$precision : $precision;
        $scale = $scale !== null ? (int)$scale : $scale;

        if (in_array($col, ['date', 'time'])) {
            return ['type' => $col, 'length' => null];
        }
        if (strpos($col, 'datetime') !== false) {
            return ['type' => TableSchema::TYPE_TIMESTAMP, 'length' => null];
        }

        if ($col === 'tinyint') {
            return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $precision ?: 3];
        }
        if ($col === 'smallint') {
            return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $precision ?: 5];
        }
        if ($col === 'int' || $col === 'integer') {
            return ['type' => TableSchema::TYPE_INTEGER, 'length' => $precision ?: 10];
        }
        if ($col === 'bigint') {
            return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $precision ?: 20];
        }
        if ($col === 'bit') {
            return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null];
        }
        if (
            strpos($col, 'numeric') !== false ||
            strpos($col, 'money') !== false ||
            strpos($col, 'decimal') !== false
        ) {
            return ['type' => TableSchema::TYPE_DECIMAL, 'length' => $precision, 'precision' => $scale];
        }

        if ($col === 'real' || $col === 'float') {
            return ['type' => TableSchema::TYPE_FLOAT, 'length' => null];
        }
        // SqlServer schema reflection returns double length for unicode
        // columns because internally it uses UTF16/UCS2
        if ($col === 'nvarchar' || $col === 'nchar' || $col === 'ntext') {
            $length /= 2;
        }
        if (strpos($col, 'varchar') !== false && $length < 0) {
            return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
        }

        if (strpos($col, 'varchar') !== false) {
            return ['type' => TableSchema::TYPE_STRING, 'length' => $length ?: 255];
        }

        if (strpos($col, 'char') !== false) {
            return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length];
        }

        if (strpos($col, 'text') !== false) {
            return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
        }

        if ($col === 'image' || strpos($col, 'binary') !== false) {
            // -1 is the value for MAX which we treat as a 'long' binary
            if ($length == -1) {
                $length = TableSchema::LENGTH_LONG;
            }

            return ['type' => TableSchema::TYPE_BINARY, 'length' => $length];
        }

        if ($col === 'uniqueidentifier') {
            return ['type' => TableSchema::TYPE_UUID];
        }

        return ['type' => TableSchema::TYPE_STRING, 'length' => null];
    }

    /**
     * {@inheritDoc}
     */
    public function convertColumnDescription(TableSchema $schema, $row)
    {
        $field = $this->_convertColumn(
            $row['type'],
            $row['char_length'],
            $row['precision'],
            $row['scale']
        );
        if (!empty($row['default'])) {
            $row['default'] = trim($row['default'], '()');
        }
        if (!empty($row['autoincrement'])) {
            $field['autoIncrement'] = true;
        }
        if ($field['type'] === TableSchema::TYPE_BOOLEAN) {
            $row['default'] = (int)$row['default'];
        }

        $field += [
            'null' => $row['null'] === '1',
            'default' => $this->_defaultValue($row['default']),
            'collate' => $row['collation_name'],
        ];
        $schema->addColumn($row['name'], $field);
    }

    /**
     * Manipulate the default value.
     *
     * Sqlite includes quotes and bared NULLs in default values.
     * We need to remove those.
     *
     * @param string|null $default The default value.
     * @return string|null
     */
    protected function _defaultValue($default)
    {
        if ($default === 'NULL') {
            return null;
        }

        // Remove quotes
        if (preg_match("/^N?'(.*)'/", $default, $matches)) {
            return str_replace("''", "'", $matches[1]);
        }

        return $default;
    }

    /**
     * {@inheritDoc}
     */
    public function describeIndexSql($tableName, $config)
    {
        $sql = "SELECT
                I.[name] AS [index_name],
                IC.[index_column_id] AS [index_order],
                AC.[name] AS [column_name],
                I.[is_unique], I.[is_primary_key],
                I.[is_unique_constraint]
            FROM sys.[tables] AS T
            INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
            WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' AND T.[name] = ? AND S.[name] = ?
            ORDER BY I.[index_id], IC.[index_column_id]";

        $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];

        return [$sql, [$tableName, $schema]];
    }

    /**
     * {@inheritDoc}
     */
    public function convertIndexDescription(TableSchema $schema, $row)
    {
        $type = TableSchema::INDEX_INDEX;
        $name = $row['index_name'];
        if ($row['is_primary_key']) {
            $name = $type = TableSchema::CONSTRAINT_PRIMARY;
        }
        if ($row['is_unique_constraint'] && $type === TableSchema::INDEX_INDEX) {
            $type = TableSchema::CONSTRAINT_UNIQUE;
        }

        if ($type === TableSchema::INDEX_INDEX) {
            $existing = $schema->getIndex($name);
        } else {
            $existing = $schema->getConstraint($name);
        }

        $columns = [$row['column_name']];
        if (!empty($existing)) {
            $columns = array_merge($existing['columns'], $columns);
        }

        if ($type === TableSchema::CONSTRAINT_PRIMARY || $type === TableSchema::CONSTRAINT_UNIQUE) {
            $schema->addConstraint($name, [
                'type' => $type,
                'columns' => $columns,
            ]);

            return;
        }
        $schema->addIndex($name, [
            'type' => $type,
            'columns' => $columns,
        ]);
    }

    /**
     * {@inheritDoc}
     */
    public function describeForeignKeySql($tableName, $config)
    {
        $sql = 'SELECT FK.[name] AS [foreign_key_name], FK.[delete_referential_action_desc] AS [delete_type],
                FK.[update_referential_action_desc] AS [update_type], C.name AS [column], RT.name AS [reference_table],
                RC.name AS [reference_column]
            FROM sys.foreign_keys FK
            INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id
            INNER JOIN sys.tables T ON T.object_id = FKC.parent_object_id
            INNER JOIN sys.tables RT ON RT.object_id = FKC.referenced_object_id
            INNER JOIN sys.schemas S ON S.schema_id = T.schema_id AND S.schema_id = RT.schema_id
            INNER JOIN sys.columns C ON C.column_id = FKC.parent_column_id AND C.object_id = FKC.parent_object_id
            INNER JOIN sys.columns RC ON RC.column_id = FKC.referenced_column_id AND RC.object_id = FKC.referenced_object_id
            WHERE FK.is_ms_shipped = 0 AND T.name = ? AND S.name = ?';

        $schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];

        return [$sql, [$tableName, $schema]];
    }

    /**
     * {@inheritDoc}
     */
    public function convertForeignKeyDescription(TableSchema $schema, $row)
    {
        $data = [
            'type' => TableSchema::CONSTRAINT_FOREIGN,
            'columns' => [$row['column']],
            'references' => [$row['reference_table'], $row['reference_column']],
            'update' => $this->_convertOnClause($row['update_type']),
            'delete' => $this->_convertOnClause($row['delete_type']),
        ];
        $name = $row['foreign_key_name'];
        $schema->addConstraint($name, $data);
    }

    /**
     * {@inheritDoc}
     */
    protected function _foreignOnClause($on)
    {
        $parent = parent::_foreignOnClause($on);

        return $parent === 'RESTRICT' ? parent::_foreignOnClause(TableSchema::ACTION_SET_NULL) : $parent;
    }

    /**
     * {@inheritDoc}
     */
    protected function _convertOnClause($clause)
    {
        switch ($clause) {
            case 'NO_ACTION':
                return TableSchema::ACTION_NO_ACTION;
            case 'CASCADE':
                return TableSchema::ACTION_CASCADE;
            case 'SET_NULL':
                return TableSchema::ACTION_SET_NULL;
            case 'SET_DEFAULT':
                return TableSchema::ACTION_SET_DEFAULT;
        }

        return TableSchema::ACTION_SET_NULL;
    }

    /**
     * {@inheritDoc}
     */
    public function columnSql(TableSchema $schema, $name)
    {
        $data = $schema->getColumn($name);
        $out = $this->_driver->quoteIdentifier($name);
        $typeMap = [
            TableSchema::TYPE_TINYINTEGER => ' TINYINT',
            TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
            TableSchema::TYPE_INTEGER => ' INTEGER',
            TableSchema::TYPE_BIGINTEGER => ' BIGINT',
            TableSchema::TYPE_BINARY_UUID => ' UNIQUEIDENTIFIER',
            TableSchema::TYPE_BOOLEAN => ' BIT',
            TableSchema::TYPE_FLOAT => ' FLOAT',
            TableSchema::TYPE_DECIMAL => ' DECIMAL',
            TableSchema::TYPE_DATE => ' DATE',
            TableSchema::TYPE_TIME => ' TIME',
            TableSchema::TYPE_DATETIME => ' DATETIME',
            TableSchema::TYPE_TIMESTAMP => ' DATETIME',
            TableSchema::TYPE_UUID => ' UNIQUEIDENTIFIER',
            TableSchema::TYPE_JSON => ' NVARCHAR(MAX)',
        ];

        if (isset($typeMap[$data['type']])) {
            $out .= $typeMap[$data['type']];
        }

        if ($data['type'] === TableSchema::TYPE_INTEGER || $data['type'] === TableSchema::TYPE_BIGINTEGER) {
            if ([$name] === $schema->primaryKey() || $data['autoIncrement'] === true) {
                unset($data['null'], $data['default']);
                $out .= ' IDENTITY(1, 1)';
            }
        }

        if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) {
            $out .= ' NVARCHAR(MAX)';
        }

        if ($data['type'] === TableSchema::TYPE_BINARY) {
            if (
                !isset($data['length'])
                || in_array($data['length'], [TableSchema::LENGTH_MEDIUM, TableSchema::LENGTH_LONG], true)
            ) {
                $data['length'] = 'MAX';
            }

            if ($data['length'] === 1) {
                $out .= ' BINARY(1)';
            } else {
                $out .= ' VARBINARY';

                $out .= sprintf('(%s)', $data['length']);
            }
        }

        if (
            $data['type'] === TableSchema::TYPE_STRING ||
            ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY)
        ) {
            $type = ' NVARCHAR';

            if (!empty($data['fixed'])) {
                $type = ' NCHAR';
            }

            if (!isset($data['length'])) {
                $data['length'] = 255;
            }

            $out .= sprintf('%s(%d)', $type, $data['length']);
        }

        $hasCollate = [TableSchema::TYPE_TEXT, TableSchema::TYPE_STRING];
        if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') {
            $out .= ' COLLATE ' . $data['collate'];
        }

        if ($data['type'] === TableSchema::TYPE_FLOAT && isset($data['precision'])) {
            $out .= '(' . (int)$data['precision'] . ')';
        }

        if (
            $data['type'] === TableSchema::TYPE_DECIMAL &&
            (isset($data['length']) || isset($data['precision']))
        ) {
            $out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
        }

        if (isset($data['null']) && $data['null'] === false) {
            $out .= ' NOT NULL';
        }

        if (
            isset($data['default']) &&
            in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) &&
            strtolower($data['default']) === 'current_timestamp'
        ) {
            $out .= ' DEFAULT CURRENT_TIMESTAMP';
        } elseif (isset($data['default'])) {
            $default = is_bool($data['default']) ? (int)$data['default'] : $this->_driver->schemaValue($data['default']);
            $out .= ' DEFAULT ' . $default;
        } elseif (isset($data['null']) && $data['null'] !== false) {
            $out .= ' DEFAULT NULL';
        }

        return $out;
    }

    /**
     * {@inheritDoc}
     */
    public function addConstraintSql(TableSchema $schema)
    {
        $sqlPattern = 'ALTER TABLE %s ADD %s;';
        $sql = [];

        foreach ($schema->constraints() as $name) {
            $constraint = $schema->getConstraint($name);
            if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
                $tableName = $this->_driver->quoteIdentifier($schema->name());
                $sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name));
            }
        }

        return $sql;
    }

    /**
     * {@inheritDoc}
     */
    public function dropConstraintSql(TableSchema $schema)
    {
        $sqlPattern = 'ALTER TABLE %s DROP CONSTRAINT %s;';
        $sql = [];

        foreach ($schema->constraints() as $name) {
            $constraint = $schema->getConstraint($name);
            if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
                $tableName = $this->_driver->quoteIdentifier($schema->name());
                $constraintName = $this->_driver->quoteIdentifier($name);
                $sql[] = sprintf($sqlPattern, $tableName, $constraintName);
            }
        }

        return $sql;
    }

    /**
     * {@inheritDoc}
     */
    public function indexSql(TableSchema $schema, $name)
    {
        $data = $schema->getIndex($name);
        $columns = array_map(
            [$this->_driver, 'quoteIdentifier'],
            $data['columns']
        );

        return sprintf(
            'CREATE INDEX %s ON %s (%s)',
            $this->_driver->quoteIdentifier($name),
            $this->_driver->quoteIdentifier($schema->name()),
            implode(', ', $columns)
        );
    }

    /**
     * {@inheritDoc}
     */
    public function constraintSql(TableSchema $schema, $name)
    {
        $data = $schema->getConstraint($name);
        $out = 'CONSTRAINT ' . $this->_driver->quoteIdentifier($name);
        if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
            $out = 'PRIMARY KEY';
        }
        if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
            $out .= ' UNIQUE';
        }

        return $this->_keySql($out, $data);
    }

    /**
     * Helper method for generating key SQL snippets.
     *
     * @param string $prefix The key prefix
     * @param array $data Key data.
     * @return string
     */
    protected function _keySql($prefix, $data)
    {
        $columns = array_map(
            [$this->_driver, 'quoteIdentifier'],
            $data['columns']
        );
        if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
            return $prefix . sprintf(
                ' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
                implode(', ', $columns),
                $this->_driver->quoteIdentifier($data['references'][0]),
                $this->_convertConstraintColumns($data['references'][1]),
                $this->_foreignOnClause($data['update']),
                $this->_foreignOnClause($data['delete'])
            );
        }

        return $prefix . ' (' . implode(', ', $columns) . ')';
    }

    /**
     * {@inheritDoc}
     */
    public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
    {
        $content = array_merge($columns, $constraints);
        $content = implode(",\n", array_filter($content));
        $tableName = $this->_driver->quoteIdentifier($schema->name());
        $out = [];
        $out[] = sprintf("CREATE TABLE %s (\n%s\n)", $tableName, $content);
        foreach ($indexes as $index) {
            $out[] = $index;
        }

        return $out;
    }

    /**
     * {@inheritDoc}
     */
    public function truncateTableSql(TableSchema $schema)
    {
        $name = $this->_driver->quoteIdentifier($schema->name());
        $queries = [
            sprintf('DELETE FROM %s', $name),
        ];

        // Restart identity sequences
        $pk = $schema->primaryKey();
        if (count($pk) === 1) {
            $column = $schema->getColumn($pk[0]);
            if (in_array($column['type'], ['integer', 'biginteger'])) {
                $queries[] = sprintf(
                    "DBCC CHECKIDENT('%s', RESEED, 0)",
                    $schema->name()
                );
            }
        }

        return $queries;
    }
}


1		<?php
2		/**
3		* CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
4		* Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
5		*
6		* Licensed under The MIT License
7		* For full copyright and license information, please see the LICENSE.txt
8		* Redistributions of files must retain the above copyright notice.
9		*
10		* @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
11		* @link https://cakephp.org CakePHP(tm) Project
12		* @since 3.0.0
13		* @license https://opensource.org/licenses/mit-license.php MIT License
14		*/
15		namespace Cake\Database\Schema;
16
17		/**
18		* Schema management/reflection features for SQLServer.
19		*/
20		class SqlserverSchema extends BaseSchema
21		{
22		const DEFAULT_SCHEMA_NAME = 'dbo';
23
24		/**
25		* {@inheritDoc}
26		*/
27		public function listTablesSql($config)
28		{
29		$sql = "SELECT TABLE_NAME
30		FROM INFORMATION_SCHEMA.TABLES
31		WHERE TABLE_SCHEMA = ?
32		AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW')
33		ORDER BY TABLE_NAME";
34		$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
35
36		return [$sql, [$schema]];
37		}
38
39		/**
40		* {@inheritDoc}
41		*/
42	View Code Duplication	public function describeColumnSql($tableName, $config)
43		{
44		$sql = 'SELECT DISTINCT
45		AC.column_id AS [column_id],
46		AC.name AS [name],
47		TY.name AS [type],
48		AC.max_length AS [char_length],
49		AC.precision AS [precision],
50		AC.scale AS [scale],
51		AC.is_identity AS [autoincrement],
52		AC.is_nullable AS [null],
53		OBJECT_DEFINITION(AC.default_object_id) AS [default],
54		AC.collation_name AS [collation_name]
55		FROM sys.[objects] T
56		INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
57		INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
58		INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
59		WHERE T.[name] = ? AND S.[name] = ?
60		ORDER BY column_id';
61
62		$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
63
64		return [$sql, [$tableName, $schema]];
65		}
66
67		/**
68		* Convert a column definition to the abstract types.
69		*
70		* The returned type will be a type that
71		* Cake\Database\Type can handle.
72		*
73		* @param string $col The column type
74		* @param int\|null $length the column length
75		* @param int\|null $precision The column precision
76		* @param int\|null $scale The column scale
77		* @return array Array of column information.
78		* @link https://technet.microsoft.com/en-us/library/ms187752.aspx
79		*/
80		protected function _convertColumn($col, $length = null, $precision = null, $scale = null)
81		{
82		$col = strtolower($col);
83		$length = $length !== null ? (int)$length : $length;
84		$precision = $precision !== null ? (int)$precision : $precision;
85		$scale = $scale !== null ? (int)$scale : $scale;
86
87	View Code Duplication	if (in_array($col, ['date', 'time'])) {
88		return ['type' => $col, 'length' => null];
89		}
90		if (strpos($col, 'datetime') !== false) {
91		return ['type' => TableSchema::TYPE_TIMESTAMP, 'length' => null];
92		}
93
94		if ($col === 'tinyint') {
95		return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $precision ?: 3];
96		}
97		if ($col === 'smallint') {
98		return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $precision ?: 5];
99		}
100		if ($col === 'int' \|\| $col === 'integer') {
101		return ['type' => TableSchema::TYPE_INTEGER, 'length' => $precision ?: 10];
102		}
103		if ($col === 'bigint') {
104		return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $precision ?: 20];
105		}
106		if ($col === 'bit') {
107		return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null];
108		}
109	View Code Duplication	if (
110		strpos($col, 'numeric') !== false \|\|
111		strpos($col, 'money') !== false \|\|
112		strpos($col, 'decimal') !== false
113		) {
114		return ['type' => TableSchema::TYPE_DECIMAL, 'length' => $precision, 'precision' => $scale];
115		}
116
117		if ($col === 'real' \|\| $col === 'float') {
118		return ['type' => TableSchema::TYPE_FLOAT, 'length' => null];
119		}
120		// SqlServer schema reflection returns double length for unicode
121		// columns because internally it uses UTF16/UCS2
122		if ($col === 'nvarchar' \|\| $col === 'nchar' \|\| $col === 'ntext') {
123		$length /= 2;
124		}
125		if (strpos($col, 'varchar') !== false && $length < 0) {
126		return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
127		}
128
129		if (strpos($col, 'varchar') !== false) {
130		return ['type' => TableSchema::TYPE_STRING, 'length' => $length ?: 255];
131		}
132
133	View Code Duplication	if (strpos($col, 'char') !== false) {
134		return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length];
135		}
136
137		if (strpos($col, 'text') !== false) {
138		return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
139		}
140
141		if ($col === 'image' \|\| strpos($col, 'binary') !== false) {
142		// -1 is the value for MAX which we treat as a 'long' binary
143		if ($length == -1) {
144		$length = TableSchema::LENGTH_LONG;
145		}
146
147		return ['type' => TableSchema::TYPE_BINARY, 'length' => $length];
148		}
149
150		if ($col === 'uniqueidentifier') {
151		return ['type' => TableSchema::TYPE_UUID];
152		}
153
154		return ['type' => TableSchema::TYPE_STRING, 'length' => null];
155		}
156
157		/**
158		* {@inheritDoc}
159		*/
160		public function convertColumnDescription(TableSchema $schema, $row)
161		{
162		$field = $this->_convertColumn(
163		$row['type'],
164		$row['char_length'],
165		$row['precision'],
166		$row['scale']
167		);
168		if (!empty($row['default'])) {
169		$row['default'] = trim($row['default'], '()');
170		}
171		if (!empty($row['autoincrement'])) {
172		$field['autoIncrement'] = true;
173		}
174		if ($field['type'] === TableSchema::TYPE_BOOLEAN) {
175		$row['default'] = (int)$row['default'];
176		}
177
178		$field += [
179		'null' => $row['null'] === '1',
180		'default' => $this->_defaultValue($row['default']),
181		'collate' => $row['collation_name'],
182		];
183		$schema->addColumn($row['name'], $field);
184		}
185
186		/**
187		* Manipulate the default value.
188		*
189		* Sqlite includes quotes and bared NULLs in default values.
190		* We need to remove those.
191		*
192		* @param string\|null $default The default value.
193		* @return string\|null
194		*/
195	View Code Duplication	protected function _defaultValue($default)
196		{
197		if ($default === 'NULL') {
198		return null;
199		}
200
201		// Remove quotes
202		if (preg_match("/^N?'(.*)'/", $default, $matches)) {
203		return str_replace("''", "'", $matches[1]);
204		}
205
206		return $default;
207		}
208
209		/**
210		* {@inheritDoc}
211		*/
212	View Code Duplication	public function describeIndexSql($tableName, $config)
213		{
214		$sql = "SELECT
215		I.[name] AS [index_name],
216		IC.[index_column_id] AS [index_order],
217		AC.[name] AS [column_name],
218		I.[is_unique], I.[is_primary_key],
219		I.[is_unique_constraint]
220		FROM sys.[tables] AS T
221		INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
222		INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
223		INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
224		INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
225		WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' AND T.[name] = ? AND S.[name] = ?
226		ORDER BY I.[index_id], IC.[index_column_id]";
227
228		$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
229
230		return [$sql, [$tableName, $schema]];
231		}
232
233		/**
234		* {@inheritDoc}
235		*/
236		public function convertIndexDescription(TableSchema $schema, $row)
237		{
238		$type = TableSchema::INDEX_INDEX;
239		$name = $row['index_name'];
240		if ($row['is_primary_key']) {
241		$name = $type = TableSchema::CONSTRAINT_PRIMARY;
242		}
243		if ($row['is_unique_constraint'] && $type === TableSchema::INDEX_INDEX) {
244		$type = TableSchema::CONSTRAINT_UNIQUE;
245		}
246
247		if ($type === TableSchema::INDEX_INDEX) {
248		$existing = $schema->getIndex($name);
249		} else {
250		$existing = $schema->getConstraint($name);
251		}
252
253		$columns = [$row['column_name']];
254		if (!empty($existing)) {
255		$columns = array_merge($existing['columns'], $columns);
256		}
257
258		if ($type === TableSchema::CONSTRAINT_PRIMARY \|\| $type === TableSchema::CONSTRAINT_UNIQUE) {
259		$schema->addConstraint($name, [
260		'type' => $type,
261		'columns' => $columns,
262		]);
263
264		return;
265		}
266		$schema->addIndex($name, [
267		'type' => $type,
268		'columns' => $columns,
269		]);
270		}
271
272		/**
273		* {@inheritDoc}
274		*/
275	View Code Duplication	public function describeForeignKeySql($tableName, $config)
276		{
277		$sql = 'SELECT FK.[name] AS [foreign_key_name], FK.[delete_referential_action_desc] AS [delete_type],
278		FK.[update_referential_action_desc] AS [update_type], C.name AS [column], RT.name AS [reference_table],
279		RC.name AS [reference_column]
280		FROM sys.foreign_keys FK
281		INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id
282		INNER JOIN sys.tables T ON T.object_id = FKC.parent_object_id
283		INNER JOIN sys.tables RT ON RT.object_id = FKC.referenced_object_id
284		INNER JOIN sys.schemas S ON S.schema_id = T.schema_id AND S.schema_id = RT.schema_id
285		INNER JOIN sys.columns C ON C.column_id = FKC.parent_column_id AND C.object_id = FKC.parent_object_id
286		INNER JOIN sys.columns RC ON RC.column_id = FKC.referenced_column_id AND RC.object_id = FKC.referenced_object_id
287		WHERE FK.is_ms_shipped = 0 AND T.name = ? AND S.name = ?';
288
289		$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema'];
290
291		return [$sql, [$tableName, $schema]];
292		}
293
294		/**
295		* {@inheritDoc}
296		*/
297	View Code Duplication	public function convertForeignKeyDescription(TableSchema $schema, $row)
298		{
299		$data = [
300		'type' => TableSchema::CONSTRAINT_FOREIGN,
301		'columns' => [$row['column']],
302		'references' => [$row['reference_table'], $row['reference_column']],
303		'update' => $this->_convertOnClause($row['update_type']),
304		'delete' => $this->_convertOnClause($row['delete_type']),
305		];
306		$name = $row['foreign_key_name'];
307		$schema->addConstraint($name, $data);
308		}
309
310		/**
311		* {@inheritDoc}
312		*/
313		protected function _foreignOnClause($on)
314		{
315		$parent = parent::_foreignOnClause($on);
316
317		return $parent === 'RESTRICT' ? parent::_foreignOnClause(TableSchema::ACTION_SET_NULL) : $parent;
318		}
319
320		/**
321		* {@inheritDoc}
322		*/
323		protected function _convertOnClause($clause)
324		{
325		switch ($clause) {
326		case 'NO_ACTION':
327		return TableSchema::ACTION_NO_ACTION;
328		case 'CASCADE':
329		return TableSchema::ACTION_CASCADE;
330		case 'SET_NULL':
331		return TableSchema::ACTION_SET_NULL;
332		case 'SET_DEFAULT':
333		return TableSchema::ACTION_SET_DEFAULT;
334		}
335
336		return TableSchema::ACTION_SET_NULL;
337		}
338
339		/**
340		* {@inheritDoc}
341		*/
342		public function columnSql(TableSchema $schema, $name)
343		{
344		$data = $schema->getColumn($name);
345		$out = $this->_driver->quoteIdentifier($name);
346		$typeMap = [
347		TableSchema::TYPE_TINYINTEGER => ' TINYINT',
348		TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
349		TableSchema::TYPE_INTEGER => ' INTEGER',
350		TableSchema::TYPE_BIGINTEGER => ' BIGINT',
351		TableSchema::TYPE_BINARY_UUID => ' UNIQUEIDENTIFIER',
352		TableSchema::TYPE_BOOLEAN => ' BIT',
353		TableSchema::TYPE_FLOAT => ' FLOAT',
354		TableSchema::TYPE_DECIMAL => ' DECIMAL',
355		TableSchema::TYPE_DATE => ' DATE',
356		TableSchema::TYPE_TIME => ' TIME',
357		TableSchema::TYPE_DATETIME => ' DATETIME',
358		TableSchema::TYPE_TIMESTAMP => ' DATETIME',
359		TableSchema::TYPE_UUID => ' UNIQUEIDENTIFIER',
360		TableSchema::TYPE_JSON => ' NVARCHAR(MAX)',
361		];
362
363		if (isset($typeMap[$data['type']])) {
364		$out .= $typeMap[$data['type']];
365		}
366
367		if ($data['type'] === TableSchema::TYPE_INTEGER \|\| $data['type'] === TableSchema::TYPE_BIGINTEGER) {
368		if ([$name] === $schema->primaryKey() \|\| $data['autoIncrement'] === true) {
369		unset($data['null'], $data['default']);
370		$out .= ' IDENTITY(1, 1)';
371		}
372		}
373
374	View Code Duplication	if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) {
375		$out .= ' NVARCHAR(MAX)';
376		}
377
378		if ($data['type'] === TableSchema::TYPE_BINARY) {
379		if (
380		!isset($data['length'])
381		\|\| in_array($data['length'], [TableSchema::LENGTH_MEDIUM, TableSchema::LENGTH_LONG], true)
382		) {
383		$data['length'] = 'MAX';
384		}
385
386		if ($data['length'] === 1) {
387		$out .= ' BINARY(1)';
388		} else {
389		$out .= ' VARBINARY';
390
391		$out .= sprintf('(%s)', $data['length']);
392		}
393		}
394
395		if (
396		$data['type'] === TableSchema::TYPE_STRING \|\|
397		($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY)
398		) {
399		$type = ' NVARCHAR';
400
401		if (!empty($data['fixed'])) {
402		$type = ' NCHAR';
403		}
404
405		if (!isset($data['length'])) {
406		$data['length'] = 255;
407		}
408
409		$out .= sprintf('%s(%d)', $type, $data['length']);
410		}
411
412		$hasCollate = [TableSchema::TYPE_TEXT, TableSchema::TYPE_STRING];
413	View Code Duplication	if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') {
414		$out .= ' COLLATE ' . $data['collate'];
415		}
416
417	View Code Duplication	if ($data['type'] === TableSchema::TYPE_FLOAT && isset($data['precision'])) {
418		$out .= '(' . (int)$data['precision'] . ')';
419		}
420
421	View Code Duplication	if (
422		$data['type'] === TableSchema::TYPE_DECIMAL &&
423		(isset($data['length']) \|\| isset($data['precision']))
424		) {
425		$out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
426		}
427
428		if (isset($data['null']) && $data['null'] === false) {
429		$out .= ' NOT NULL';
430		}
431
432		if (
433		isset($data['default']) &&
434		in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) &&
435		strtolower($data['default']) === 'current_timestamp'
436		) {
437		$out .= ' DEFAULT CURRENT_TIMESTAMP';
438		} elseif (isset($data['default'])) {
439		$default = is_bool($data['default']) ? (int)$data['default'] : $this->_driver->schemaValue($data['default']);
440		$out .= ' DEFAULT ' . $default;
441		} elseif (isset($data['null']) && $data['null'] !== false) {
442		$out .= ' DEFAULT NULL';
443		}
444
445		return $out;
446		}
447
448		/**
449		* {@inheritDoc}
450		*/
451	View Code Duplication	public function addConstraintSql(TableSchema $schema)
452		{
453		$sqlPattern = 'ALTER TABLE %s ADD %s;';
454		$sql = [];
455
456		foreach ($schema->constraints() as $name) {
457		$constraint = $schema->getConstraint($name);
458		if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
459		$tableName = $this->_driver->quoteIdentifier($schema->name());
460		$sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name));
461		}
462		}
463
464		return $sql;
465		}
466
467		/**
468		* {@inheritDoc}
469		*/
470	View Code Duplication	public function dropConstraintSql(TableSchema $schema)
471		{
472		$sqlPattern = 'ALTER TABLE %s DROP CONSTRAINT %s;';
473		$sql = [];
474
475		foreach ($schema->constraints() as $name) {
476		$constraint = $schema->getConstraint($name);
477		if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) {
478		$tableName = $this->_driver->quoteIdentifier($schema->name());
479		$constraintName = $this->_driver->quoteIdentifier($name);
480		$sql[] = sprintf($sqlPattern, $tableName, $constraintName);
481		}
482		}
483
484		return $sql;
485		}
486
487		/**
488		* {@inheritDoc}
489		*/
490	View Code Duplication	public function indexSql(TableSchema $schema, $name)
491		{
492		$data = $schema->getIndex($name);
493		$columns = array_map(
494		[$this->_driver, 'quoteIdentifier'],
495		$data['columns']
496		);
497
498		return sprintf(
499		'CREATE INDEX %s ON %s (%s)',
500		$this->_driver->quoteIdentifier($name),
501		$this->_driver->quoteIdentifier($schema->name()),
502		implode(', ', $columns)
503		);
504		}
505
506		/**
507		* {@inheritDoc}
508		*/
509	View Code Duplication	public function constraintSql(TableSchema $schema, $name)
510		{
511		$data = $schema->getConstraint($name);
512		$out = 'CONSTRAINT ' . $this->_driver->quoteIdentifier($name);
513		if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
514		$out = 'PRIMARY KEY';
515		}
516		if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
517		$out .= ' UNIQUE';
518		}
519
520		return $this->_keySql($out, $data);
521		}
522
523		/**
524		* Helper method for generating key SQL snippets.
525		*
526		* @param string $prefix The key prefix
527		* @param array $data Key data.
528		* @return string
529		*/
530	View Code Duplication	protected function _keySql($prefix, $data)
531		{
532		$columns = array_map(
533		[$this->_driver, 'quoteIdentifier'],
534		$data['columns']
535		);
536		if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
537		return $prefix . sprintf(
538		' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
539		implode(', ', $columns),
540		$this->_driver->quoteIdentifier($data['references'][0]),
541		$this->_convertConstraintColumns($data['references'][1]),
542		$this->_foreignOnClause($data['update']),
543		$this->_foreignOnClause($data['delete'])
544		);
545		}
546
547		return $prefix . ' (' . implode(', ', $columns) . ')';
548		}
549
550		/**
551		* {@inheritDoc}
552		*/
553		public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
554		{
555		$content = array_merge($columns, $constraints);
556		$content = implode(",\n", array_filter($content));
557		$tableName = $this->_driver->quoteIdentifier($schema->name());
558		$out = [];
559		$out[] = sprintf("CREATE TABLE %s (\n%s\n)", $tableName, $content);
560		foreach ($indexes as $index) {
561		$out[] = $index;
562		}
563
564		return $out;
565		}
566
567		/**
568		* {@inheritDoc}
569		*/
570		public function truncateTableSql(TableSchema $schema)
571		{
572		$name = $this->_driver->quoteIdentifier($schema->name());
573		$queries = [
574		sprintf('DELETE FROM %s', $name),
575		];
576
577		// Restart identity sequences
578		$pk = $schema->primaryKey();
579		if (count($pk) === 1) {
580		$column = $schema->getColumn($pk[0]);
581		if (in_array($column['type'], ['integer', 'biginteger'])) {
582		$queries[] = sprintf(
583		"DBCC CHECKIDENT('%s', RESEED, 0)",
584		$schema->name()
585		);
586		}
587		}
588
589		return $queries;
590		}
591		}
592

cakephp / cakephp

SqlserverSchema::_convertColumn() F last analyzed 2019-12-15 12:43 UTC

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like

SqlserverSchema::_convertColumn() F
last analyzed 2019-12-15 12:43 UTC