Completed
Push — fix-unique-exists-expressions ( b0719b...8467f4 )
by Alexander
07:52
created

QueryBuilder::renameColumn()   B

Complexity

Conditions 6
Paths 7

Size

Total Lines 28
Code Lines 20

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 42

Importance

Changes 0
Metric Value
dl 0
loc 28
ccs 0
cts 19
cp 0
rs 8.439
c 0
b 0
f 0
cc 6
eloc 20
nc 7
nop 3
crap 42
1
<?php
2
/**
3
 * @link http://www.yiiframework.com/
4
 * @copyright Copyright (c) 2008 Yii Software LLC
5
 * @license http://www.yiiframework.com/license/
6
 */
7
8
namespace yii\db\mysql;
9
10
use yii\base\InvalidParamException;
11
use yii\db\Exception;
12
use yii\db\Expression;
13
14
/**
15
 * QueryBuilder is the query builder for MySQL databases.
16
 *
17
 * @author Qiang Xue <[email protected]>
18
 * @since 2.0
19
 */
20
class QueryBuilder extends \yii\db\QueryBuilder
21
{
22
    /**
23
     * @var array mapping from abstract column types (keys) to physical column types (values).
24
     */
25
    public $typeMap = [
26
        Schema::TYPE_PK => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
27
        Schema::TYPE_UPK => 'int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
28
        Schema::TYPE_BIGPK => 'bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY',
29
        Schema::TYPE_UBIGPK => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
30
        Schema::TYPE_CHAR => 'char(1)',
31
        Schema::TYPE_STRING => 'varchar(255)',
32
        Schema::TYPE_TEXT => 'text',
33
        Schema::TYPE_SMALLINT => 'smallint(6)',
34
        Schema::TYPE_INTEGER => 'int(11)',
35
        Schema::TYPE_BIGINT => 'bigint(20)',
36
        Schema::TYPE_FLOAT => 'float',
37
        Schema::TYPE_DOUBLE => 'double',
38
        Schema::TYPE_DECIMAL => 'decimal(10,0)',
39
        Schema::TYPE_DATETIME => 'datetime',
40
        Schema::TYPE_TIMESTAMP => 'timestamp',
41
        Schema::TYPE_TIME => 'time',
42
        Schema::TYPE_DATE => 'date',
43
        Schema::TYPE_BINARY => 'blob',
44
        Schema::TYPE_BOOLEAN => 'tinyint(1)',
45
        Schema::TYPE_MONEY => 'decimal(19,4)',
46
    ];
47
48
49
    /**
50
     * Builds a SQL statement for renaming a column.
51
     * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
52
     * @param string $oldName the old name of the column. The name will be properly quoted by the method.
53
     * @param string $newName the new name of the column. The name will be properly quoted by the method.
54
     * @return string the SQL statement for renaming a DB column.
55
     * @throws Exception
56
     */
57
    public function renameColumn($table, $oldName, $newName)
58
    {
59
        $quotedTable = $this->db->quoteTableName($table);
60
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
61
        if ($row === false) {
62
            throw new Exception("Unable to find column '$oldName' in table '$table'.");
63
        }
64
        if (isset($row['Create Table'])) {
65
            $sql = $row['Create Table'];
66
        } else {
67
            $row = array_values($row);
68
            $sql = $row[1];
69
        }
70
        if (preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m', $sql, $matches)) {
71
            foreach ($matches[1] as $i => $c) {
72
                if ($c === $oldName) {
73
                    return "ALTER TABLE $quotedTable CHANGE "
74
                        . $this->db->quoteColumnName($oldName) . ' '
75
                        . $this->db->quoteColumnName($newName) . ' '
76
                        . $matches[2][$i];
77
                }
78
            }
79
        }
80
        // try to give back a SQL anyway
81
        return "ALTER TABLE $quotedTable CHANGE "
82
            . $this->db->quoteColumnName($oldName) . ' '
83
            . $this->db->quoteColumnName($newName);
84
    }
85
86
    /**
87
     * @inheritdoc
88
     * @see https://bugs.mysql.com/bug.php?id=48875
89
     */
90
    public function createIndex($name, $table, $columns, $unique = false)
91
    {
92
        return 'ALTER TABLE '
93
        . $this->db->quoteTableName($table)
94
        . ($unique ? ' ADD UNIQUE INDEX ' : ' ADD INDEX ')
95
        . $this->db->quoteTableName($name)
96
        . ' (' . $this->buildColumns($columns) . ')';
97
    }
98
99
    /**
100
     * Builds a SQL statement for dropping a foreign key constraint.
101
     * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
102
     * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
103
     * @return string the SQL statement for dropping a foreign key constraint.
104
     */
105
    public function dropForeignKey($name, $table)
106
    {
107
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
108
            . ' DROP FOREIGN KEY ' . $this->db->quoteColumnName($name);
109
    }
110
111
    /**
112
     * Builds a SQL statement for removing a primary key constraint to an existing table.
113
     * @param string $name the name of the primary key constraint to be removed.
114
     * @param string $table the table that the primary key constraint will be removed from.
115
     * @return string the SQL statement for removing a primary key constraint from an existing table.
116
     */
117
    public function dropPrimaryKey($name, $table)
118
    {
119
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' DROP PRIMARY KEY';
120
    }
121
122
    /**
123
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
124
     * The sequence will be reset such that the primary key of the next new row inserted
125
     * will have the specified value or 1.
126
     * @param string $tableName the name of the table whose primary key sequence will be reset
127
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
128
     * the next new row's primary key will have a value 1.
129
     * @return string the SQL statement for resetting sequence
130
     * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
131
     */
132 1
    public function resetSequence($tableName, $value = null)
133
    {
134 1
        $table = $this->db->getTableSchema($tableName);
135
        if ($table !== null && $table->sequenceName !== null) {
136
            $tableName = $this->db->quoteTableName($tableName);
137
            if ($value === null) {
138
                $key = reset($table->primaryKey);
139
                $value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
140
            } else {
141
                $value = (int) $value;
142
            }
143
144
            return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
145
        } elseif ($table === null) {
146
            throw new InvalidParamException("Table not found: $tableName");
147
        } else {
148
            throw new InvalidParamException("There is no sequence associated with table '$tableName'.");
149
        }
150
    }
151
152
    /**
153
     * Builds a SQL statement for enabling or disabling integrity check.
154
     * @param bool $check whether to turn on or off the integrity check.
155
     * @param string $schema the schema of the tables. Meaningless for MySQL.
156
     * @param string $table the table name. Meaningless for MySQL.
157
     * @return string the SQL statement for checking integrity
158
     */
159
    public function checkIntegrity($check = true, $schema = '', $table = '')
160
    {
161
        return 'SET FOREIGN_KEY_CHECKS = ' . ($check ? 1 : 0);
162
    }
163
164
    /**
165
     * @inheritdoc
166
     */
167 98
    public function buildLimit($limit, $offset)
168
    {
169 98
        $sql = '';
170 98
        if ($this->hasLimit($limit)) {
171
            $sql = 'LIMIT ' . $limit;
172
            if ($this->hasOffset($offset)) {
173
                $sql .= ' OFFSET ' . $offset;
174
            }
175 98
        } elseif ($this->hasOffset($offset)) {
176
            // limit is not optional in MySQL
177
            // http://stackoverflow.com/a/271650/1106908
178
            // http://dev.mysql.com/doc/refman/5.0/en/select.html#idm47619502796240
179
            $sql = "LIMIT $offset, 18446744073709551615"; // 2^64-1
180
        }
181
182 98
        return $sql;
183
    }
184
185
    /**
186
     * @inheritdoc
187
     */
188 98
    protected function hasLimit($limit)
189
    {
190
        // In MySQL limit argument must be nonnegative integer constant
191 98
        return ctype_digit((string) $limit);
192
    }
193
194
    /**
195
     * @inheritdoc
196
     */
197 98
    protected function hasOffset($offset)
198
    {
199
        // In MySQL offset argument must be nonnegative integer constant
200 98
        $offset = (string) $offset;
201 98
        return ctype_digit($offset) && $offset !== '0';
202
    }
203
204
    /**
205
     * @inheritdoc
206
     */
207
    public function insert($table, $columns, &$params)
208
    {
209
        $schema = $this->db->getSchema();
210
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
211
            $columnSchemas = $tableSchema->columns;
212
        } else {
213
            $columnSchemas = [];
214
        }
215
        $names = [];
216
        $placeholders = [];
217
        $values = ' DEFAULT VALUES';
218
        if ($columns instanceof \yii\db\Query) {
219
            list($names, $values, $params) = $this->prepareInsertSelectSubQuery($columns, $schema, $params);
220
        } else {
221
            foreach ($columns as $name => $value) {
222
                $names[] = $schema->quoteColumnName($name);
223
                if ($value instanceof Expression) {
224
                    $placeholders[] = $value->expression;
225
                    foreach ($value->params as $n => $v) {
226
                        $params[$n] = $v;
227
                    }
228
                } elseif ($value instanceof \yii\db\Query) {
229
                    list($sql, $params) = $this->build($value, $params);
230
                    $placeholders[] = "($sql)";
231
                } else {
232
                    $phName = self::PARAM_PREFIX . count($params);
233
                    $placeholders[] = $phName;
234
                    $params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
235
                }
236
            }
237
            if (empty($names) && $tableSchema !== null) {
238
                $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : [reset($tableSchema->columns)->name];
239
                foreach ($columns as $name) {
240
                    $names[] = $schema->quoteColumnName($name);
241
                    $placeholders[] = 'DEFAULT';
242
                }
243
            }
244
        }
245
246
        return 'INSERT INTO ' . $schema->quoteTableName($table)
247
            . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
248
            . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values);
249
    }
250
251
    /**
252
     * @inheritdoc
253
     * @since 2.0.8
254
     */
255 1
    public function addCommentOnColumn($table, $column, $comment)
256
    {
257 1
        $definition = $this->getColumnDefinition($table, $column);
258
        $definition = trim(preg_replace("/COMMENT '(.*?)'/i", '', $definition));
259
260
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
261
            . ' CHANGE ' . $this->db->quoteColumnName($column)
262
            . ' ' . $this->db->quoteColumnName($column)
263
            . (empty($definition) ? '' : ' ' . $definition)
264
            . ' COMMENT ' . $this->db->quoteValue($comment);
265
    }
266
267
    /**
268
     * @inheritdoc
269
     * @since 2.0.8
270
     */
271 1
    public function addCommentOnTable($table, $comment)
272
    {
273 1
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' COMMENT ' . $this->db->quoteValue($comment);
274
    }
275
276
    /**
277
     * @inheritdoc
278
     * @since 2.0.8
279
     */
280
    public function dropCommentFromColumn($table, $column)
281
    {
282
        return $this->addCommentOnColumn($table, $column, '');
283
    }
284
285
    /**
286
     * @inheritdoc
287
     * @since 2.0.8
288
     */
289
    public function dropCommentFromTable($table)
290
    {
291
        return $this->addCommentOnTable($table, '');
292
    }
293
294
295
    /**
296
     * Gets column definition.
297
     *
298
     * @param string $table table name
299
     * @param string $column column name
300
     * @return null|string the column definition
301
     * @throws Exception in case when table does not contain column
302
     */
303 1
    private function getColumnDefinition($table, $column)
304
    {
305 1
        $quotedTable = $this->db->quoteTableName($table);
306 1
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
307
        if ($row === false) {
308
            throw new Exception("Unable to find column '$column' in table '$table'.");
309
        }
310
        if (isset($row['Create Table'])) {
311
            $sql = $row['Create Table'];
312
        } else {
313
            $row = array_values($row);
314
            $sql = $row[1];
315
        }
316
        if (preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m', $sql, $matches)) {
317
            foreach ($matches[1] as $i => $c) {
318
                if ($c === $column) {
319
                    return $matches[2][$i];
320
                }
321
            }
322
        }
323
        return null;
324
    }
325
}
326