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
|
|
|
|