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\oci; |
||
9 | |||
10 | use yii\base\InvalidArgumentException; |
||
11 | use yii\db\Connection; |
||
12 | use yii\db\Constraint; |
||
13 | use yii\db\Exception; |
||
14 | use yii\db\Expression; |
||
15 | use yii\db\Query; |
||
16 | use yii\helpers\StringHelper; |
||
17 | use yii\db\ExpressionInterface; |
||
18 | |||
19 | /** |
||
20 | * QueryBuilder is the query builder for Oracle databases. |
||
21 | * |
||
22 | * @author Qiang Xue <[email protected]> |
||
23 | * @since 2.0 |
||
24 | */ |
||
25 | class QueryBuilder extends \yii\db\QueryBuilder |
||
26 | { |
||
27 | /** |
||
28 | * @var array mapping from abstract column types (keys) to physical column types (values). |
||
29 | */ |
||
30 | public $typeMap = [ |
||
31 | Schema::TYPE_PK => 'NUMBER(10) NOT NULL PRIMARY KEY', |
||
32 | Schema::TYPE_UPK => 'NUMBER(10) UNSIGNED NOT NULL PRIMARY KEY', |
||
33 | Schema::TYPE_BIGPK => 'NUMBER(20) NOT NULL PRIMARY KEY', |
||
34 | Schema::TYPE_UBIGPK => 'NUMBER(20) UNSIGNED NOT NULL PRIMARY KEY', |
||
35 | Schema::TYPE_CHAR => 'CHAR(1)', |
||
36 | Schema::TYPE_STRING => 'VARCHAR2(255)', |
||
37 | Schema::TYPE_TEXT => 'CLOB', |
||
38 | Schema::TYPE_TINYINT => 'NUMBER(3)', |
||
39 | Schema::TYPE_SMALLINT => 'NUMBER(5)', |
||
40 | Schema::TYPE_INTEGER => 'NUMBER(10)', |
||
41 | Schema::TYPE_BIGINT => 'NUMBER(20)', |
||
42 | Schema::TYPE_FLOAT => 'NUMBER', |
||
43 | Schema::TYPE_DOUBLE => 'NUMBER', |
||
44 | Schema::TYPE_DECIMAL => 'NUMBER', |
||
45 | Schema::TYPE_DATETIME => 'TIMESTAMP', |
||
46 | Schema::TYPE_TIMESTAMP => 'TIMESTAMP', |
||
47 | Schema::TYPE_TIME => 'TIMESTAMP', |
||
48 | Schema::TYPE_DATE => 'DATE', |
||
49 | Schema::TYPE_BINARY => 'BLOB', |
||
50 | Schema::TYPE_BOOLEAN => 'NUMBER(1)', |
||
51 | Schema::TYPE_MONEY => 'NUMBER(19,4)', |
||
52 | ]; |
||
53 | |||
54 | |||
55 | /** |
||
56 | * {@inheritdoc} |
||
57 | */ |
||
58 | protected function defaultExpressionBuilders() |
||
59 | { |
||
60 | return array_merge(parent::defaultExpressionBuilders(), [ |
||
61 | 'yii\db\conditions\InCondition' => 'yii\db\oci\conditions\InConditionBuilder', |
||
62 | 'yii\db\conditions\LikeCondition' => 'yii\db\oci\conditions\LikeConditionBuilder', |
||
63 | ]); |
||
64 | } |
||
65 | |||
66 | /** |
||
67 | * {@inheritdoc} |
||
68 | */ |
||
69 | public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
70 | { |
||
71 | $orderBy = $this->buildOrderBy($orderBy); |
||
72 | if ($orderBy !== '') { |
||
73 | $sql .= $this->separator . $orderBy; |
||
74 | } |
||
75 | |||
76 | $filters = []; |
||
77 | if ($this->hasOffset($offset)) { |
||
78 | $filters[] = 'rowNumId > ' . $offset; |
||
79 | } |
||
80 | if ($this->hasLimit($limit)) { |
||
81 | $filters[] = 'rownum <= ' . $limit; |
||
82 | } |
||
83 | if (empty($filters)) { |
||
84 | return $sql; |
||
85 | } |
||
86 | |||
87 | $filter = implode(' AND ', $filters); |
||
88 | return <<<EOD |
||
89 | WITH USER_SQL AS ($sql), |
||
90 | PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL) |
||
91 | SELECT * |
||
92 | FROM PAGINATION |
||
93 | WHERE $filter |
||
94 | EOD; |
||
95 | } |
||
96 | |||
97 | /** |
||
98 | * Builds a SQL statement for renaming a DB table. |
||
99 | * |
||
100 | * @param string $table the table to be renamed. The name will be properly quoted by the method. |
||
101 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
102 | * @return string the SQL statement for renaming a DB table. |
||
103 | */ |
||
104 | public function renameTable($table, $newName) |
||
105 | { |
||
106 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName); |
||
107 | } |
||
108 | |||
109 | /** |
||
110 | * Builds a SQL statement for changing the definition of a column. |
||
111 | * |
||
112 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. |
||
113 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
114 | * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any) |
||
115 | * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. |
||
116 | * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. |
||
117 | * @return string the SQL statement for changing the definition of a column. |
||
118 | */ |
||
119 | public function alterColumn($table, $column, $type) |
||
120 | { |
||
121 | $type = $this->getColumnType($type); |
||
122 | |||
123 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type); |
||
124 | } |
||
125 | |||
126 | /** |
||
127 | * Builds a SQL statement for dropping an index. |
||
128 | * |
||
129 | * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. |
||
130 | * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. |
||
131 | * @return string the SQL statement for dropping an index. |
||
132 | */ |
||
133 | public function dropIndex($name, $table) |
||
134 | { |
||
135 | return 'DROP INDEX ' . $this->db->quoteTableName($name); |
||
136 | } |
||
137 | |||
138 | /** |
||
139 | * {@inheritdoc} |
||
140 | */ |
||
141 | public function executeResetSequence($table, $value = null) |
||
142 | { |
||
143 | $tableSchema = $this->db->getTableSchema($table); |
||
144 | if ($tableSchema === null) { |
||
145 | throw new InvalidArgumentException("Unknown table: $table"); |
||
146 | } |
||
147 | if ($tableSchema->sequenceName === null) { |
||
148 | throw new InvalidArgumentException("There is no sequence associated with table: $table"); |
||
149 | } |
||
150 | |||
151 | if ($value !== null) { |
||
152 | $value = (int) $value; |
||
153 | } else { |
||
154 | if (count($tableSchema->primaryKey)>1) { |
||
155 | throw new InvalidArgumentException("Can't reset sequence for composite primary key in table: $table"); |
||
156 | } |
||
157 | // use primary connection to get the biggest PK value |
||
158 | $value = $this->db->useMaster(function (Connection $db) use ($tableSchema) { |
||
0 ignored issues
–
show
|
|||
159 | return $db->createCommand( |
||
160 | 'SELECT MAX("' . $tableSchema->primaryKey[0] . '") FROM "'. $tableSchema->name . '"' |
||
161 | )->queryScalar(); |
||
162 | }) + 1; |
||
163 | } |
||
164 | |||
165 | //Oracle needs at least two queries to reset sequence (see adding transactions and/or use alter method to avoid grants' issue?) |
||
166 | $this->db->createCommand('DROP SEQUENCE "' . $tableSchema->sequenceName . '"')->execute(); |
||
167 | $this->db->createCommand('CREATE SEQUENCE "' . $tableSchema->sequenceName . '" START WITH ' . $value |
||
168 | . ' INCREMENT BY 1 NOMAXVALUE NOCACHE')->execute(); |
||
169 | } |
||
170 | |||
171 | /** |
||
172 | * {@inheritdoc} |
||
173 | */ |
||
174 | public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null) |
||
175 | { |
||
176 | $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) |
||
177 | . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name) |
||
178 | . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')' |
||
179 | . ' REFERENCES ' . $this->db->quoteTableName($refTable) |
||
180 | . ' (' . $this->buildColumns($refColumns) . ')'; |
||
181 | if ($delete !== null) { |
||
182 | $sql .= ' ON DELETE ' . $delete; |
||
183 | } |
||
184 | if ($update !== null) { |
||
185 | throw new Exception('Oracle does not support ON UPDATE clause.'); |
||
186 | } |
||
187 | |||
188 | return $sql; |
||
189 | } |
||
190 | |||
191 | /** |
||
192 | * {@inheritdoc} |
||
193 | */ |
||
194 | protected function prepareInsertValues($table, $columns, $params = []) |
||
195 | { |
||
196 | list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params); |
||
197 | if (!$columns instanceof Query && empty($names)) { |
||
198 | $tableSchema = $this->db->getSchema()->getTableSchema($table); |
||
199 | if ($tableSchema !== null) { |
||
200 | $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : [reset($tableSchema->columns)->name]; |
||
201 | foreach ($columns as $name) { |
||
202 | $names[] = $this->db->quoteColumnName($name); |
||
203 | $placeholders[] = 'DEFAULT'; |
||
204 | } |
||
205 | } |
||
206 | } |
||
207 | return [$names, $placeholders, $values, $params]; |
||
208 | } |
||
209 | |||
210 | /** |
||
211 | * {@inheritdoc} |
||
212 | * @see https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606 |
||
213 | */ |
||
214 | public function upsert($table, $insertColumns, $updateColumns, &$params) |
||
215 | { |
||
216 | /** @var Constraint[] $constraints */ |
||
217 | list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints); |
||
218 | if (empty($uniqueNames)) { |
||
219 | return $this->insert($table, $insertColumns, $params); |
||
220 | } |
||
221 | if ($updateNames === []) { |
||
222 | // there are no columns to update |
||
223 | $updateColumns = false; |
||
224 | } |
||
225 | |||
226 | $onCondition = ['or']; |
||
227 | $quotedTableName = $this->db->quoteTableName($table); |
||
228 | foreach ($constraints as $constraint) { |
||
229 | $constraintCondition = ['and']; |
||
230 | foreach ($constraint->columnNames as $name) { |
||
231 | $quotedName = $this->db->quoteColumnName($name); |
||
232 | $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName"; |
||
233 | } |
||
234 | $onCondition[] = $constraintCondition; |
||
235 | } |
||
236 | $on = $this->buildCondition($onCondition, $params); |
||
237 | list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params); |
||
238 | if (!empty($placeholders)) { |
||
239 | $usingSelectValues = []; |
||
240 | foreach ($insertNames as $index => $name) { |
||
241 | $usingSelectValues[$name] = new Expression($placeholders[$index]); |
||
242 | } |
||
243 | $usingSubQuery = (new Query()) |
||
244 | ->select($usingSelectValues) |
||
245 | ->from('DUAL'); |
||
246 | list($usingValues, $params) = $this->build($usingSubQuery, $params); |
||
247 | } |
||
248 | $mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' ' |
||
249 | . 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" ' |
||
250 | . "ON ($on)"; |
||
251 | $insertValues = []; |
||
252 | foreach ($insertNames as $name) { |
||
253 | $quotedName = $this->db->quoteColumnName($name); |
||
254 | if (strrpos($quotedName, '.') === false) { |
||
255 | $quotedName = '"EXCLUDED".' . $quotedName; |
||
256 | } |
||
257 | $insertValues[] = $quotedName; |
||
258 | } |
||
259 | $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' |
||
260 | . ' VALUES (' . implode(', ', $insertValues) . ')'; |
||
261 | if ($updateColumns === false) { |
||
262 | return "$mergeSql WHEN NOT MATCHED THEN $insertSql"; |
||
263 | } |
||
264 | |||
265 | if ($updateColumns === true) { |
||
266 | $updateColumns = []; |
||
267 | foreach ($updateNames as $name) { |
||
268 | $quotedName = $this->db->quoteColumnName($name); |
||
269 | if (strrpos($quotedName, '.') === false) { |
||
270 | $quotedName = '"EXCLUDED".' . $quotedName; |
||
271 | } |
||
272 | $updateColumns[$name] = new Expression($quotedName); |
||
273 | } |
||
274 | } |
||
275 | list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params); |
||
276 | $updateSql = 'UPDATE SET ' . implode(', ', $updates); |
||
277 | return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql"; |
||
278 | } |
||
279 | |||
280 | /** |
||
281 | * Generates a batch INSERT SQL statement. |
||
282 | * |
||
283 | * For example, |
||
284 | * |
||
285 | * ```php |
||
286 | * $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [ |
||
287 | * ['Tom', 30], |
||
288 | * ['Jane', 20], |
||
289 | * ['Linda', 25], |
||
290 | * ]); |
||
291 | * ``` |
||
292 | * |
||
293 | * Note that the values in each row must match the corresponding column names. |
||
294 | * |
||
295 | * @param string $table the table that new rows will be inserted into. |
||
296 | * @param array $columns the column names |
||
297 | * @param array|\Generator $rows the rows to be batch inserted into the table |
||
298 | * @return string the batch INSERT SQL statement |
||
299 | */ |
||
300 | public function batchInsert($table, $columns, $rows, &$params = []) |
||
301 | { |
||
302 | if (empty($rows)) { |
||
303 | return ''; |
||
304 | } |
||
305 | |||
306 | $schema = $this->db->getSchema(); |
||
307 | if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
||
308 | $columnSchemas = $tableSchema->columns; |
||
309 | } else { |
||
310 | $columnSchemas = []; |
||
311 | } |
||
312 | |||
313 | $values = []; |
||
314 | foreach ($rows as $row) { |
||
315 | $vs = []; |
||
316 | foreach ($row as $i => $value) { |
||
317 | if (isset($columns[$i], $columnSchemas[$columns[$i]])) { |
||
318 | $value = $columnSchemas[$columns[$i]]->dbTypecast($value); |
||
319 | } |
||
320 | if (is_string($value)) { |
||
321 | $value = $schema->quoteValue($value); |
||
322 | } elseif (is_float($value)) { |
||
323 | // ensure type cast always has . as decimal separator in all locales |
||
324 | $value = StringHelper::floatToString($value); |
||
325 | } elseif ($value === false) { |
||
326 | $value = 0; |
||
327 | } elseif ($value === null) { |
||
328 | $value = 'NULL'; |
||
329 | } elseif ($value instanceof ExpressionInterface) { |
||
330 | $value = $this->buildExpression($value, $params); |
||
331 | } |
||
332 | $vs[] = $value; |
||
333 | } |
||
334 | $values[] = '(' . implode(', ', $vs) . ')'; |
||
335 | } |
||
336 | if (empty($values)) { |
||
337 | return ''; |
||
338 | } |
||
339 | |||
340 | foreach ($columns as $i => $name) { |
||
341 | $columns[$i] = $schema->quoteColumnName($name); |
||
342 | } |
||
343 | |||
344 | $tableAndColumns = ' INTO ' . $schema->quoteTableName($table) |
||
345 | . ' (' . implode(', ', $columns) . ') VALUES '; |
||
346 | |||
347 | return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL'; |
||
348 | } |
||
349 | |||
350 | /** |
||
351 | * {@inheritdoc} |
||
352 | * @since 2.0.8 |
||
353 | */ |
||
354 | public function selectExists($rawSql) |
||
355 | { |
||
356 | return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM DUAL'; |
||
357 | } |
||
358 | |||
359 | /** |
||
360 | * {@inheritdoc} |
||
361 | * @since 2.0.8 |
||
362 | */ |
||
363 | public function dropCommentFromColumn($table, $column) |
||
364 | { |
||
365 | return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''"; |
||
366 | } |
||
367 | |||
368 | /** |
||
369 | * {@inheritdoc} |
||
370 | * @since 2.0.8 |
||
371 | */ |
||
372 | public function dropCommentFromTable($table) |
||
373 | { |
||
374 | return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''"; |
||
375 | } |
||
376 | } |
||
377 |
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.