Completed
Push — master ( 5d7c5f...9f383a )
by Alexander
11:41
created

QueryBuilder   C

Complexity

Total Complexity 54

Size/Duplication

Total Lines 387
Duplicated Lines 0 %

Coupling/Cohesion

Components 1
Dependencies 9

Test Coverage

Coverage 76.92%

Importance

Changes 0
Metric Value
wmc 54
lcom 1
cbo 9
dl 0
loc 387
ccs 100
cts 130
cp 0.7692
rs 6.4799
c 0
b 0
f 0

23 Methods

Rating   Name   Duplication   Size   Complexity  
A init() 0 6 1
A defaultExpressionBuilders() 0 6 1
B renameColumn() 0 28 6
A createIndex() 0 8 2
A dropForeignKey() 0 5 1
A dropPrimaryKey() 0 4 1
A dropUnique() 0 4 1
A addCheck() 0 4 1
A dropCheck() 0 4 1
A resetSequence() 0 19 5
A checkIntegrity() 0 4 2
A buildLimit() 0 17 4
A hasLimit() 0 5 1
A hasOffset() 0 6 2
A prepareInsertValues() 0 15 6
A upsert() 0 20 5
A addCommentOnColumn() 0 12 2
A addCommentOnTable() 0 4 1
A dropCommentFromColumn() 0 4 1
A dropCommentFromTable() 0 4 1
B getColumnDefinition() 0 23 6
A defaultTimeTypeMap() 0 18 2
A supportsFractionalSeconds() 0 5 1

How to fix   Complexity   

Complex Class

Complex classes like QueryBuilder often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use QueryBuilder, and based on these observations, apply Extract Interface, too.

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\InvalidArgumentException;
11
use yii\base\NotSupportedException;
12
use yii\db\Exception;
13
use yii\db\Expression;
14
use yii\db\Query;
15
16
/**
17
 * QueryBuilder is the query builder for MySQL databases.
18
 *
19
 * @author Qiang Xue <[email protected]>
20
 * @since 2.0
21
 */
22
class QueryBuilder extends \yii\db\QueryBuilder
23
{
24
    /**
25
     * @var array mapping from abstract column types (keys) to physical column types (values).
26
     */
27
    public $typeMap = [
28
        Schema::TYPE_PK => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
29
        Schema::TYPE_UPK => 'int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
30
        Schema::TYPE_BIGPK => 'bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY',
31
        Schema::TYPE_UBIGPK => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
32
        Schema::TYPE_CHAR => 'char(1)',
33
        Schema::TYPE_STRING => 'varchar(255)',
34
        Schema::TYPE_TEXT => 'text',
35
        Schema::TYPE_TINYINT => 'tinyint(3)',
36
        Schema::TYPE_SMALLINT => 'smallint(6)',
37
        Schema::TYPE_INTEGER => 'int(11)',
38
        Schema::TYPE_BIGINT => 'bigint(20)',
39
        Schema::TYPE_FLOAT => 'float',
40
        Schema::TYPE_DOUBLE => 'double',
41
        Schema::TYPE_DECIMAL => 'decimal(10,0)',
42
        Schema::TYPE_DATE => 'date',
43
        Schema::TYPE_BINARY => 'blob',
44
        Schema::TYPE_BOOLEAN => 'tinyint(1)',
45
        Schema::TYPE_MONEY => 'decimal(19,4)',
46
        Schema::TYPE_JSON => 'json'
47
    ];
48
49
    /**
50
     * {@inheritdoc}
51
     */
52 509
    public function init()
53
    {
54 509
        parent::init();
55
56 509
        $this->typeMap = array_merge($this->typeMap, $this->defaultTimeTypeMap());
57 509
    }
58
59
    /**
60
     * {@inheritdoc}
61
     */
62 509
    protected function defaultExpressionBuilders()
63
    {
64 509
        return array_merge(parent::defaultExpressionBuilders(), [
65 509
            'yii\db\JsonExpression' => 'yii\db\mysql\JsonExpressionBuilder',
66
        ]);
67
    }
68
69
    /**
70
     * Builds a SQL statement for renaming a column.
71
     * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
72
     * @param string $oldName the old name of the column. The name will be properly quoted by the method.
73
     * @param string $newName the new name of the column. The name will be properly quoted by the method.
74
     * @return string the SQL statement for renaming a DB column.
75
     * @throws Exception
76
     */
77
    public function renameColumn($table, $oldName, $newName)
78
    {
79
        $quotedTable = $this->db->quoteTableName($table);
80
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
81
        if ($row === false) {
82
            throw new Exception("Unable to find column '$oldName' in table '$table'.");
83
        }
84
        if (isset($row['Create Table'])) {
85
            $sql = $row['Create Table'];
86
        } else {
87
            $row = array_values($row);
88
            $sql = $row[1];
89
        }
90
        if (preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m', $sql, $matches)) {
91
            foreach ($matches[1] as $i => $c) {
92
                if ($c === $oldName) {
93
                    return "ALTER TABLE $quotedTable CHANGE "
94
                        . $this->db->quoteColumnName($oldName) . ' '
95
                        . $this->db->quoteColumnName($newName) . ' '
96
                        . $matches[2][$i];
97
                }
98
            }
99
        }
100
        // try to give back a SQL anyway
101
        return "ALTER TABLE $quotedTable CHANGE "
102
            . $this->db->quoteColumnName($oldName) . ' '
103
            . $this->db->quoteColumnName($newName);
104
    }
105
106
    /**
107
     * {@inheritdoc}
108
     * @see https://bugs.mysql.com/bug.php?id=48875
109
     */
110 12
    public function createIndex($name, $table, $columns, $unique = false)
111
    {
112
        return 'ALTER TABLE '
113 12
        . $this->db->quoteTableName($table)
114 12
        . ($unique ? ' ADD UNIQUE INDEX ' : ' ADD INDEX ')
115 12
        . $this->db->quoteTableName($name)
116 12
        . ' (' . $this->buildColumns($columns) . ')';
117
    }
118
119
    /**
120
     * Builds a SQL statement for dropping a foreign key constraint.
121
     * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
122
     * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
123
     * @return string the SQL statement for dropping a foreign key constraint.
124
     */
125 3
    public function dropForeignKey($name, $table)
126
    {
127 3
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
128 3
            . ' DROP FOREIGN KEY ' . $this->db->quoteColumnName($name);
129
    }
130
131
    /**
132
     * Builds a SQL statement for removing a primary key constraint to an existing table.
133
     * @param string $name the name of the primary key constraint to be removed.
134
     * @param string $table the table that the primary key constraint will be removed from.
135
     * @return string the SQL statement for removing a primary key constraint from an existing table.
136
     */
137 2
    public function dropPrimaryKey($name, $table)
138
    {
139 2
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' DROP PRIMARY KEY';
140
    }
141
142
    /**
143
     * {@inheritdoc}
144
     */
145 2
    public function dropUnique($name, $table)
146
    {
147 2
        return $this->dropIndex($name, $table);
148
    }
149
150
    /**
151
     * {@inheritdoc}
152
     * @throws NotSupportedException this is not supported by MySQL.
153
     */
154
    public function addCheck($name, $table, $expression)
155
    {
156
        throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
157
    }
158
159
    /**
160
     * {@inheritdoc}
161
     * @throws NotSupportedException this is not supported by MySQL.
162
     */
163
    public function dropCheck($name, $table)
164
    {
165
        throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
166
    }
167
168
    /**
169
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
170
     * The sequence will be reset such that the primary key of the next new row inserted
171
     * will have the specified value or 1.
172
     * @param string $tableName the name of the table whose primary key sequence will be reset
173
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
174
     * the next new row's primary key will have a value 1.
175
     * @return string the SQL statement for resetting sequence
176
     * @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table.
177
     */
178 11
    public function resetSequence($tableName, $value = null)
179
    {
180 11
        $table = $this->db->getTableSchema($tableName);
181 11
        if ($table !== null && $table->sequenceName !== null) {
182 11
            $tableName = $this->db->quoteTableName($tableName);
183 11
            if ($value === null) {
184 1
                $key = reset($table->primaryKey);
185 1
                $value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
186
            } else {
187 11
                $value = (int) $value;
188
            }
189
190 11
            return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
191
        } elseif ($table === null) {
192
            throw new InvalidArgumentException("Table not found: $tableName");
193
        }
194
195
        throw new InvalidArgumentException("There is no sequence associated with table '$tableName'.");
196
    }
197
198
    /**
199
     * Builds a SQL statement for enabling or disabling integrity check.
200
     * @param bool $check whether to turn on or off the integrity check.
201
     * @param string $schema the schema of the tables. Meaningless for MySQL.
202
     * @param string $table the table name. Meaningless for MySQL.
203
     * @return string the SQL statement for checking integrity
204
     */
205 4
    public function checkIntegrity($check = true, $schema = '', $table = '')
206
    {
207 4
        return 'SET FOREIGN_KEY_CHECKS = ' . ($check ? 1 : 0);
208
    }
209
210
    /**
211
     * {@inheritdoc}
212
     */
213 405
    public function buildLimit($limit, $offset)
214
    {
215 405
        $sql = '';
216 405
        if ($this->hasLimit($limit)) {
217 31
            $sql = 'LIMIT ' . $limit;
218 31
            if ($this->hasOffset($offset)) {
219 31
                $sql .= ' OFFSET ' . $offset;
220
            }
221 398
        } elseif ($this->hasOffset($offset)) {
222
            // limit is not optional in MySQL
223
            // http://stackoverflow.com/a/271650/1106908
224
            // http://dev.mysql.com/doc/refman/5.0/en/select.html#idm47619502796240
225 2
            $sql = "LIMIT $offset, 18446744073709551615"; // 2^64-1
226
        }
227
228 405
        return $sql;
229
    }
230
231
    /**
232
     * {@inheritdoc}
233
     */
234 405
    protected function hasLimit($limit)
235
    {
236
        // In MySQL limit argument must be nonnegative integer constant
237 405
        return ctype_digit((string) $limit);
238
    }
239
240
    /**
241
     * {@inheritdoc}
242
     */
243 405
    protected function hasOffset($offset)
244
    {
245
        // In MySQL offset argument must be nonnegative integer constant
246 405
        $offset = (string) $offset;
247 405
        return ctype_digit($offset) && $offset !== '0';
248
    }
249
250
    /**
251
     * {@inheritdoc}
252
     */
253 203
    protected function prepareInsertValues($table, $columns, $params = [])
254
    {
255 203
        list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params);
256 200
        if (!$columns instanceof Query && empty($names)) {
257 1
            $tableSchema = $this->db->getSchema()->getTableSchema($table);
258 1
            if ($tableSchema !== null) {
259 1
                $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : [reset($tableSchema->columns)->name];
260 1
                foreach ($columns as $name) {
261 1
                    $names[] = $this->db->quoteColumnName($name);
262 1
                    $placeholders[] = 'DEFAULT';
263
                }
264
            }
265
        }
266 200
        return [$names, $placeholders, $values, $params];
267
    }
268
269
    /**
270
     * {@inheritdoc}
271
     * @see https://downloads.mysql.com/docs/refman-5.1-en.pdf
272
     */
273 38
    public function upsert($table, $insertColumns, $updateColumns, &$params)
274
    {
275 38
        $insertSql = $this->insert($table, $insertColumns, $params);
276 38
        list($uniqueNames, , $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns);
277 38
        if (empty($uniqueNames)) {
278 3
            return $insertSql;
279
        }
280
281 35
        if ($updateColumns === true) {
282 25
            $updateColumns = [];
283 25
            foreach ($updateNames as $name) {
284 25
                $updateColumns[$name] = new Expression('VALUES(' . $this->db->quoteColumnName($name) . ')');
285
            }
286 10
        } elseif ($updateColumns === false) {
287 4
            $name = $this->db->quoteColumnName(reset($uniqueNames));
288 4
            $updateColumns = [$name => new Expression($this->db->quoteTableName($table) . '.' . $name)];
289
        }
290 35
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
291 35
        return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
292
    }
293
294
    /**
295
     * {@inheritdoc}
296
     * @since 2.0.8
297
     */
298 2
    public function addCommentOnColumn($table, $column, $comment)
299
    {
300
        // Strip existing comment which may include escaped quotes
301 2
        $definition = trim(preg_replace("/COMMENT '(?:''|[^'])*'/i", '',
302 2
            $this->getColumnDefinition($table, $column)));
303
304 2
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
305 2
            . ' CHANGE ' . $this->db->quoteColumnName($column)
306 2
            . ' ' . $this->db->quoteColumnName($column)
307 2
            . (empty($definition) ? '' : ' ' . $definition)
308 2
            . ' COMMENT ' . $this->db->quoteValue($comment);
309
    }
310
311
    /**
312
     * {@inheritdoc}
313
     * @since 2.0.8
314
     */
315 1
    public function addCommentOnTable($table, $comment)
316
    {
317 1
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' COMMENT ' . $this->db->quoteValue($comment);
318
    }
319
320
    /**
321
     * {@inheritdoc}
322
     * @since 2.0.8
323
     */
324 2
    public function dropCommentFromColumn($table, $column)
325
    {
326 2
        return $this->addCommentOnColumn($table, $column, '');
327
    }
328
329
    /**
330
     * {@inheritdoc}
331
     * @since 2.0.8
332
     */
333 1
    public function dropCommentFromTable($table)
334
    {
335 1
        return $this->addCommentOnTable($table, '');
336
    }
337
338
339
    /**
340
     * Gets column definition.
341
     *
342
     * @param string $table table name
343
     * @param string $column column name
344
     * @return null|string the column definition
345
     * @throws Exception in case when table does not contain column
346
     */
347 2
    private function getColumnDefinition($table, $column)
348
    {
349 2
        $quotedTable = $this->db->quoteTableName($table);
350 2
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
351 2
        if ($row === false) {
352
            throw new Exception("Unable to find column '$column' in table '$table'.");
353
        }
354 2
        if (isset($row['Create Table'])) {
355 2
            $sql = $row['Create Table'];
356
        } else {
357
            $row = array_values($row);
358
            $sql = $row[1];
359
        }
360 2
        if (preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m', $sql, $matches)) {
361 2
            foreach ($matches[1] as $i => $c) {
362 2
                if ($c === $column) {
363 2
                    return $matches[2][$i];
364
                }
365
            }
366
        }
367
368
        return null;
369
    }
370
371
    /**
372
     * Checks the ability to use fractional seconds.
373
     *
374
     * @return bool
375
     * @see https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
376
     */
377 509
    private function supportsFractionalSeconds()
378
    {
379 509
        $version = $this->db->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION);
380 509
        return version_compare($version, '5.6.4', '>=');
381
    }
382
383
    /**
384
     * Returns the map for default time type.
385
     * If the version of MySQL is lower than 5.6.4, then the types will be without fractional seconds,
386
     * otherwise with fractional seconds.
387
     *
388
     * @return array
389
     */
390 509
    private function defaultTimeTypeMap()
391
    {
392
        $map = [
393 509
            Schema::TYPE_DATETIME => 'datetime',
394
            Schema::TYPE_TIMESTAMP => 'timestamp',
395
            Schema::TYPE_TIME => 'time',
396
        ];
397
398 509
        if ($this->supportsFractionalSeconds()) {
399
            $map = [
400 509
                Schema::TYPE_DATETIME => 'datetime(0)',
401
                Schema::TYPE_TIMESTAMP => 'timestamp(0)',
402
                Schema::TYPE_TIME => 'time(0)',
403
            ];
404
        }
405
406 509
        return $map;
407
    }
408
}
409