1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* @link http://www.yiiframework.com/ |
5
|
|
|
* @copyright Copyright (c) 2008 Yii Software LLC |
6
|
|
|
* @license http://www.yiiframework.com/license/ |
7
|
|
|
*/ |
8
|
|
|
|
9
|
|
|
namespace edgardmessias\db\firebird; |
10
|
|
|
|
11
|
|
|
use yii\base\InvalidParamException; |
12
|
|
|
use yii\base\NotSupportedException; |
13
|
|
|
use yii\db\Expression; |
14
|
|
|
use yii\db\Query; |
15
|
|
|
|
16
|
|
|
/** |
17
|
|
|
* |
18
|
|
|
* @author Edgard Lorraine Messias <[email protected]> |
19
|
|
|
* @since 2.0 |
20
|
|
|
*/ |
21
|
|
|
class QueryBuilder extends \yii\db\QueryBuilder |
22
|
|
|
{ |
23
|
|
|
|
24
|
|
|
/** |
25
|
|
|
* @var array mapping from abstract column types (keys) to physical column types (values). |
26
|
|
|
*/ |
27
|
|
|
public $typeMap = [ |
28
|
|
|
Schema::TYPE_PK => 'integer NOT NULL PRIMARY KEY', |
29
|
|
|
Schema::TYPE_UPK => 'integer NOT NULL PRIMARY KEY', |
30
|
|
|
Schema::TYPE_BIGPK => 'bigint NOT NULL PRIMARY KEY', |
31
|
|
|
Schema::TYPE_UBIGPK => 'bigint NOT NULL PRIMARY KEY', |
32
|
|
|
Schema::TYPE_CHAR => 'char(1)', |
33
|
|
|
Schema::TYPE_STRING => 'varchar(255)', |
34
|
|
|
Schema::TYPE_TEXT => 'blob sub_type text', |
35
|
|
|
Schema::TYPE_SMALLINT => 'smallint', |
36
|
|
|
Schema::TYPE_INTEGER => 'integer', |
37
|
|
|
Schema::TYPE_BIGINT => 'bigint', |
38
|
|
|
Schema::TYPE_FLOAT => 'float', |
39
|
|
|
Schema::TYPE_DOUBLE => 'double precision', |
40
|
|
|
Schema::TYPE_DECIMAL => 'numeric(10,0)', |
41
|
|
|
Schema::TYPE_DATETIME => 'timestamp', |
42
|
|
|
Schema::TYPE_TIMESTAMP => 'timestamp', |
43
|
|
|
Schema::TYPE_TIME => 'time', |
44
|
|
|
Schema::TYPE_DATE => 'date', |
45
|
|
|
Schema::TYPE_BINARY => 'blob', |
46
|
|
|
Schema::TYPE_BOOLEAN => 'smallint', |
47
|
|
|
Schema::TYPE_MONEY => 'numeric(18,4)', |
48
|
|
|
]; |
49
|
|
|
|
50
|
|
|
public function init() |
51
|
|
|
{ |
52
|
|
|
if (version_compare($this->db->firebird_version, '3.0.0', '>=')) { |
53
|
|
|
$this->typeMap[Schema::TYPE_PK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'; |
54
|
|
|
$this->typeMap[Schema::TYPE_UPK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'; |
55
|
|
|
$this->typeMap[Schema::TYPE_BIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'; |
56
|
|
|
$this->typeMap[Schema::TYPE_UBIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'; |
57
|
|
|
$this->typeMap[Schema::TYPE_BOOLEAN] = 'boolean'; |
58
|
|
|
} |
59
|
181 |
|
|
60
|
|
|
parent::init(); |
61
|
181 |
|
} |
62
|
|
|
|
63
|
181 |
|
/** |
64
|
|
|
* Generates a SELECT SQL statement from a [[Query]] object. |
65
|
|
|
* @param Query $query the [[Query]] object from which the SQL statement will be generated. |
66
|
181 |
|
* @param array $params the parameters to be bound to the generated SQL statement. These parameters will |
67
|
181 |
|
* be included in the result with the additional parameters generated during the query building process. |
68
|
181 |
|
* @return array the generated SQL statement (the first array element) and the corresponding |
69
|
181 |
|
* parameters to be bound to the SQL statement (the second array element). The parameters returned |
70
|
181 |
|
* include those provided in `$params`. |
71
|
181 |
|
*/ |
72
|
|
|
public function build($query, $params = []) |
73
|
|
|
{ |
74
|
181 |
|
$query = $query->prepare($this); |
75
|
181 |
|
|
76
|
|
|
$params = empty($params) ? $query->params : array_merge($params, $query->params); |
77
|
181 |
|
|
78
|
34 |
|
$clauses = [ |
79
|
34 |
|
$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption), |
80
|
34 |
|
$this->buildFrom($query->from, $params), |
81
|
|
|
$this->buildJoin($query->join, $params), |
82
|
|
|
$this->buildWhere($query->where, $params), |
83
|
|
|
$this->buildGroupBy($query->groupBy), |
84
|
181 |
|
$this->buildHaving($query->having, $params), |
85
|
2 |
|
]; |
86
|
2 |
|
|
87
|
2 |
|
$sql = implode($this->separator, array_filter($clauses)); |
88
|
|
|
$sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset); |
89
|
|
|
|
90
|
|
|
if (!empty($query->orderBy)) { |
91
|
|
|
foreach ($query->orderBy as $expression) { |
92
|
181 |
|
if ($expression instanceof Expression) { |
93
|
181 |
|
$params = array_merge($params, $expression->params); |
94
|
2 |
|
} |
95
|
|
|
} |
96
|
|
|
} |
97
|
181 |
|
if (!empty($query->groupBy)) { |
98
|
|
|
foreach ($query->groupBy as $expression) { |
99
|
|
|
if ($expression instanceof Expression) { |
100
|
|
|
$params = array_merge($params, $expression->params); |
101
|
|
|
} |
102
|
|
|
} |
103
|
181 |
|
} |
104
|
|
|
|
105
|
181 |
|
$union = $this->buildUnion($query->union, $params); |
106
|
53 |
|
if ($union !== '') { |
107
|
53 |
|
$sql = "$sql{$this->separator}$union"; |
108
|
3 |
|
} |
109
|
|
|
|
110
|
52 |
|
return [$sql, $params]; |
111
|
52 |
|
} |
112
|
21 |
|
|
113
|
21 |
|
/** |
114
|
|
|
* @inheritdoc |
115
|
52 |
|
*/ |
116
|
|
|
public function buildSelect($columns, &$params, $distinct = false, $selectOption = null) |
117
|
|
|
{ |
118
|
|
|
if (is_array($columns)) { |
119
|
|
|
foreach ($columns as $i => $column) { |
120
|
181 |
|
if (!is_string($column)) { |
121
|
|
|
continue; |
122
|
|
|
} |
123
|
|
|
$matches = []; |
124
|
|
|
if (preg_match('/^(COUNT|SUM|AVG|MIN|MAX)\([\{\[]{0,2}(\w+|\*)[\}\]]{0,2}\)$/i', $column, $matches)) { |
125
|
|
|
$function = $matches[1]; |
126
|
5 |
|
$alias = $matches[2] != '*' ? $matches[2] : 'ALL'; |
127
|
|
|
|
128
|
5 |
|
$columns[$i] = "{$column} AS {$function}_{$alias}"; |
129
|
5 |
|
} |
130
|
5 |
|
} |
131
|
|
|
} |
132
|
5 |
|
|
133
|
5 |
|
return parent::buildSelect($columns, $params, $distinct, $selectOption); |
134
|
5 |
|
} |
135
|
5 |
|
|
136
|
5 |
|
/** |
137
|
5 |
|
* @inheritdoc |
138
|
5 |
|
*/ |
139
|
5 |
|
protected function buildCompositeInCondition($operator, $columns, $values, &$params) |
140
|
|
|
{ |
141
|
5 |
|
$quotedColumns = []; |
142
|
|
|
foreach ($columns as $i => $column) { |
143
|
|
|
$quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column; |
144
|
5 |
|
} |
145
|
|
|
$vss = []; |
146
|
5 |
|
foreach ($values as $value) { |
147
|
|
|
$vs = []; |
148
|
|
|
foreach ($columns as $i => $column) { |
149
|
|
|
if (isset($value[$column])) { |
150
|
|
|
$phName = self::PARAM_PREFIX . count($params); |
151
|
|
|
$params[$phName] = $value[$column]; |
152
|
181 |
|
$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName; |
153
|
|
|
} else { |
154
|
|
|
$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL'; |
155
|
181 |
|
} |
156
|
181 |
|
} |
157
|
34 |
|
$vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')'; |
158
|
|
|
} |
159
|
|
|
return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')'; |
160
|
181 |
|
} |
161
|
181 |
|
|
162
|
|
|
/** |
163
|
181 |
|
* @inheritdoc |
164
|
180 |
|
*/ |
165
|
|
|
public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
166
|
|
|
{ |
167
|
|
|
|
168
|
3 |
|
$orderBy = $this->buildOrderBy($orderBy); |
169
|
2 |
|
if ($orderBy !== '') { |
170
|
2 |
|
$sql .= $this->separator . $orderBy; |
171
|
2 |
|
} |
172
|
|
|
|
173
|
|
|
$limit = $limit !== null ? intval($limit) : -1; |
174
|
|
|
$offset = $offset !== null ? intval($offset) : -1; |
175
|
2 |
|
// If ignoring both params then do nothing |
176
|
2 |
|
if ($offset < 0 && $limit < 0) { |
177
|
2 |
|
return $sql; |
178
|
2 |
|
} |
179
|
|
|
// If we are ignoring limit then return full result set starting |
180
|
|
|
// from $offset. In Firebird this can only be done with SKIP |
181
|
1 |
|
if ($offset >= 0 && $limit < 0) { |
182
|
1 |
|
$count = 1; //Only do it once |
183
|
1 |
|
$sql = preg_replace('/^SELECT /i', 'SELECT SKIP ' . (int) $offset . ' ', $sql, $count); |
184
|
1 |
|
return $sql; |
185
|
|
|
} |
186
|
|
|
// If we are ignoring $offset then return $limit rows. |
187
|
|
|
// ie, return the first $limit rows in the set. |
188
|
|
|
if ($offset < 0 && $limit >= 0) { |
189
|
|
|
$count = 1; //Only do it once |
190
|
|
|
$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' ', $sql, $count); |
191
|
|
|
return $sql; |
192
|
|
|
} |
193
|
|
|
// Otherwise apply the params and return the amended sql. |
194
|
|
|
if ($offset >= 0 && $limit >= 0) { |
195
|
|
|
$count = 1; //Only do it once |
196
|
181 |
|
$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' SKIP ' . (int) $offset . ' ', $sql, $count); |
197
|
|
|
return $sql; |
198
|
181 |
|
} |
199
|
181 |
|
// If we have fallen through the cracks then just pass |
200
|
|
|
// the sql back. |
201
|
|
|
return $sql; |
202
|
2 |
|
} |
203
|
|
|
|
204
|
2 |
|
/** |
205
|
2 |
|
* @param array $unions |
206
|
2 |
|
* @param array $params the binding parameters to be populated |
207
|
2 |
|
* @return string the UNION clause built from [[Query::$union]]. |
208
|
|
|
*/ |
209
|
|
|
public function buildUnion($unions, &$params) |
210
|
2 |
|
{ |
211
|
|
|
if (empty($unions)) { |
212
|
|
|
return ''; |
213
|
2 |
|
} |
214
|
|
|
|
215
|
|
|
$result = ''; |
216
|
|
|
|
217
|
|
|
foreach ($unions as $i => $union) { |
218
|
|
|
$query = $union['query']; |
219
|
|
|
if ($query instanceof Query) { |
220
|
|
|
list($unions[$i]['query'], $params) = $this->build($query, $params); |
221
|
3 |
|
} |
222
|
|
|
|
223
|
3 |
|
$result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . $unions[$i]['query'] . ' '; |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
return trim($result); |
227
|
|
|
} |
228
|
|
|
|
229
|
3 |
|
/** |
230
|
|
|
* |
231
|
3 |
|
* @param Expression $value |
232
|
|
|
* @return Expression |
233
|
|
|
*/ |
234
|
3 |
|
protected function convertExpression($value) |
235
|
|
|
{ |
236
|
|
|
if (!($value instanceof Expression)) { |
237
|
|
|
return $value; |
238
|
|
|
} |
239
|
|
|
|
240
|
23 |
|
$expressionMap = [ |
241
|
|
|
"strftime('%Y')" => "EXTRACT(YEAR FROM TIMESTAMP 'now')" |
242
|
23 |
|
]; |
243
|
23 |
|
|
244
|
23 |
|
if (isset($expressionMap[$value->expression])) { |
245
|
|
|
return new Expression($expressionMap[$value->expression]); |
246
|
|
|
} |
247
|
|
|
return $value; |
248
|
|
|
} |
249
|
|
|
|
250
|
23 |
|
/** |
251
|
1 |
|
* @inheritdoc |
252
|
1 |
|
*/ |
253
|
1 |
|
public function insert($table, $columns, &$params) |
254
|
1 |
|
{ |
255
|
|
|
$schema = $this->db->getSchema(); |
256
|
|
|
if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
257
|
|
|
$columnSchemas = $tableSchema->columns; |
258
|
|
|
} else { |
259
|
23 |
|
$columnSchemas = []; |
260
|
23 |
|
} |
261
|
23 |
|
|
262
|
1 |
|
//Empty insert |
263
|
23 |
|
if (empty($columns) && !empty($columnSchemas)) { |
264
|
23 |
|
$columns = []; |
265
|
|
|
foreach ($columnSchemas as $columnSchema) { |
266
|
|
|
if (!$columnSchema->autoIncrement) { |
267
|
|
|
$columns[$columnSchema->name] = $columnSchema->defaultValue; |
268
|
|
|
} |
269
|
23 |
|
} |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
if (is_array($columns)) { |
273
|
|
|
foreach ($columns as $name => $value) { |
274
|
|
|
if ($value instanceof Expression) { |
275
|
|
|
$columns[$name] = $this->convertExpression($value); |
276
|
|
|
} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) { |
277
|
|
|
$columns[$name] = [$value, \PDO::PARAM_LOB]; |
278
|
|
|
} |
279
|
|
|
} |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
return parent::insert($table, $columns, $params); |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
/** |
286
|
|
|
* @inheritdoc |
287
|
|
|
*/ |
288
|
|
|
protected function prepareInsertSelectSubQuery($columns, $schema, $params = []) |
289
|
|
|
{ |
290
|
13 |
|
/** |
291
|
|
|
* @see https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-insert.html#fblangref25-dml-insert-select-unstable |
292
|
13 |
|
*/ |
293
|
13 |
|
if (version_compare($this->db->firebird_version, '3.0.0', '<')) { |
294
|
13 |
|
throw new NotSupportedException('Firebird < 3.0.0 has the "Unstable Cursor" problem'); |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
return parent::prepareInsertSelectSubQuery($columns, $schema, $params = []); |
298
|
13 |
|
} |
299
|
13 |
|
|
300
|
2 |
|
/** |
301
|
11 |
|
* @inheritdoc |
302
|
13 |
|
*/ |
303
|
|
|
public function update($table, $columns, $condition, &$params) |
304
|
|
|
{ |
305
|
13 |
|
$schema = $this->db->getSchema(); |
306
|
|
|
if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
307
|
|
|
$columnSchemas = $tableSchema->columns; |
308
|
|
|
} else { |
309
|
|
|
$columnSchemas = []; |
310
|
|
|
} |
311
|
8 |
|
foreach ($columns as $name => $value) { |
312
|
|
|
if ($value instanceof Expression) { |
313
|
8 |
|
$columns[$name] = $this->convertExpression($value); |
314
|
2 |
|
} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) { |
315
|
|
|
$columns[$name] = [$value, \PDO::PARAM_LOB]; |
316
|
|
|
} |
317
|
7 |
|
} |
318
|
7 |
|
return parent::update($table, $columns, $condition, $params); |
319
|
7 |
|
} |
320
|
|
|
|
321
|
|
|
/** |
322
|
|
|
* @inheritdoc |
323
|
|
|
*/ |
324
|
7 |
|
public function batchInsert($table, $columns, $rows, &$params = []) |
325
|
7 |
|
{ |
326
|
7 |
|
if (empty($rows)) { |
327
|
7 |
|
return ''; |
328
|
7 |
|
} |
329
|
4 |
|
|
330
|
|
|
$schema = $this->db->getSchema(); |
331
|
7 |
|
if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
332
|
4 |
|
$columnSchemas = $tableSchema->columns; |
333
|
4 |
|
} else { |
334
|
1 |
|
$columnSchemas = []; |
335
|
4 |
|
} |
336
|
3 |
|
|
337
|
|
|
$values = []; |
338
|
7 |
|
foreach ($rows as $row) { |
339
|
|
|
$vs = []; |
340
|
7 |
|
foreach ($row as $i => $value) { |
341
|
7 |
|
if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) { |
342
|
|
|
$value = $columnSchemas[$columns[$i]]->dbTypecast($value); |
343
|
|
|
} |
344
|
7 |
|
if (is_string($value)) { |
345
|
6 |
|
$value = $schema->quoteValue($value); |
346
|
|
|
} elseif ($value === false) { |
347
|
|
|
$value = 0; |
348
|
7 |
|
} elseif ($value === null) { |
349
|
|
|
$value = 'NULL'; |
350
|
|
|
} elseif ($value instanceof ExpressionInterface) { |
|
|
|
|
351
|
|
|
$value = $this->buildExpression($value, $params); |
352
|
|
|
} |
353
|
|
|
$vs[] = $value; |
354
|
1 |
|
} |
355
|
|
|
$values[] = 'INSERT INTO ' . $schema->quoteTableName($table) |
356
|
1 |
|
. ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $vs) . ');'; |
357
|
|
|
} |
358
|
|
|
|
359
|
|
|
foreach ($columns as $i => $name) { |
360
|
|
|
$columns[$i] = $schema->quoteColumnName($name); |
361
|
|
|
} |
362
|
2 |
|
|
363
|
|
|
return 'EXECUTE block AS BEGIN ' . implode(' ', $values) . ' END;'; |
364
|
2 |
|
} |
365
|
|
|
|
366
|
|
|
/** |
367
|
|
|
* {@inheritdoc} |
368
|
|
|
* @see https://www.firebirdsql.org/refdocs/langrefupd21-update-or-insert.html |
369
|
|
|
* @see https://www.firebirdsql.org/refdocs/langrefupd21-merge.html |
370
|
1 |
|
*/ |
371
|
|
|
public function upsert($table, $insertColumns, $updateColumns, &$params) |
372
|
1 |
|
{ |
373
|
1 |
|
/** @var Constraint[] $constraints */ |
374
|
|
|
list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints); |
375
|
|
|
if (empty($uniqueNames)) { |
376
|
|
|
return 'UPDATE OR ' . $this->insert($table, $insertColumns, $params); |
377
|
|
|
} |
378
|
|
|
|
379
|
1 |
|
$onCondition = ['or']; |
380
|
|
|
$quotedTableName = $this->db->quoteTableName($table); |
381
|
1 |
|
foreach ($constraints as $constraint) { |
382
|
1 |
|
$constraintCondition = ['and']; |
383
|
1 |
|
foreach ($constraint->columnNames as $name) { |
|
|
|
|
384
|
|
|
$quotedName = $this->db->quoteColumnName($name); |
385
|
|
|
$constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName"; |
386
|
|
|
} |
387
|
|
|
$onCondition[] = $constraintCondition; |
388
|
|
|
} |
389
|
3 |
|
$on = $this->buildCondition($onCondition, $params); |
390
|
|
|
list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params); |
391
|
3 |
|
if (!empty($placeholders)) { |
392
|
3 |
|
$usingSelectValues = []; |
393
|
3 |
|
foreach ($insertNames as $index => $name) { |
394
|
|
|
$usingSelectValues[$name] = new Expression($placeholders[$index]); |
395
|
3 |
|
} |
396
|
|
|
$usingSubQuery = (new Query()) |
397
|
3 |
|
->select($usingSelectValues) |
398
|
|
|
->from('RDB$DATABASE'); |
399
|
3 |
|
list($usingValues, $params) = $this->build($usingSubQuery, $params); |
400
|
|
|
} |
401
|
3 |
|
$mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' ' |
402
|
3 |
|
. 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" ' |
403
|
2 |
|
. "ON ($on)"; |
404
|
2 |
|
$insertValues = []; |
405
|
2 |
|
foreach ($insertNames as $name) { |
406
|
2 |
|
$quotedName = $this->db->quoteColumnName($name); |
407
|
|
|
if (strrpos($quotedName, '.') === false) { |
408
|
|
|
$quotedName = '"EXCLUDED".' . $quotedName; |
409
|
|
|
} |
410
|
3 |
|
$insertValues[] = $quotedName; |
411
|
3 |
|
} |
412
|
3 |
|
$insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' |
413
|
|
|
. ' VALUES (' . implode(', ', $insertValues) . ')'; |
414
|
3 |
|
if ($updateColumns === false) { |
415
|
|
|
return "$mergeSql WHEN NOT MATCHED THEN $insertSql"; |
416
|
|
|
} |
417
|
|
|
|
418
|
|
|
if ($updateColumns === true) { |
419
|
|
|
$updateColumns = []; |
420
|
|
|
foreach ($updateNames as $name) { |
421
|
|
|
$quotedName = $this->db->quoteColumnName($name); |
422
|
|
|
if (strrpos($quotedName, '.') === false) { |
423
|
|
|
$quotedName = '"EXCLUDED".' . $quotedName; |
424
|
|
|
} |
425
|
|
|
$updateColumns[$name] = new Expression($quotedName); |
426
|
|
|
} |
427
|
|
|
} |
428
|
|
|
list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params); |
429
|
|
|
$updateSql = 'UPDATE SET ' . implode(', ', $updates); |
430
|
|
|
return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql"; |
431
|
|
|
} |
432
|
|
|
|
433
|
|
|
/** |
434
|
|
|
* @inheritdoc |
435
|
|
|
*/ |
436
|
|
|
public function renameTable($oldName, $newName) |
437
|
|
|
{ |
438
|
|
|
throw new \yii\base\NotSupportedException($this->db->getDriverName() . ' does not support rename table.'); |
439
|
|
|
} |
440
|
|
|
|
441
|
|
|
/** |
442
|
|
|
* @inheritdoc |
443
|
|
|
*/ |
444
|
|
|
public function truncateTable($table) |
445
|
3 |
|
{ |
446
|
2 |
|
return 'DELETE FROM ' . $this->db->quoteTableName($table); |
447
|
|
|
} |
448
|
|
|
|
449
|
2 |
|
/** |
450
|
2 |
|
* @inheritdoc |
451
|
2 |
|
*/ |
452
|
|
|
public function dropColumn($table, $column) |
453
|
|
|
{ |
454
|
|
|
return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
455
|
|
|
. ' DROP ' . $this->db->quoteColumnName($column); |
456
|
|
|
} |
457
|
2 |
|
|
458
|
2 |
|
/** |
459
|
2 |
|
* @inheritdoc |
460
|
|
|
*/ |
461
|
2 |
|
public function renameColumn($table, $oldName, $newName) |
462
|
2 |
|
{ |
463
|
|
|
return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
464
|
|
|
. ' ALTER ' . $this->db->quoteColumnName($oldName) |
465
|
|
|
. ' TO ' . $this->db->quoteColumnName($newName); |
466
|
|
|
} |
467
|
|
|
|
468
|
|
|
/** |
469
|
1 |
|
* @inheritdoc |
470
|
|
|
*/ |
471
|
1 |
|
public function alterColumn($table, $column, $type) |
472
|
|
|
{ |
473
|
|
|
$schema = $this->db->getSchema(); |
474
|
|
|
$tableSchema = $schema->getTableSchema($table); |
475
|
|
|
$columnSchema = $tableSchema->getColumn($column); |
476
|
|
|
|
477
|
1 |
|
$allowNullNewType = !preg_match('/not +null/i', $type); |
478
|
|
|
|
479
|
1 |
|
$type = preg_replace('/ +(not)? *null/i', '', $type); |
480
|
1 |
|
|
481
|
|
|
$hasType = false; |
482
|
|
|
|
483
|
1 |
|
$matches = []; |
484
|
|
|
if (isset($this->typeMap[$type])) { |
485
|
|
|
$hasType = true; |
486
|
|
|
} elseif (preg_match('/^(\w+)[\( ]/', $type, $matches)) { |
487
|
1 |
|
if (isset($this->typeMap[$matches[1]])) { |
488
|
1 |
|
$hasType = true; |
489
|
|
|
} |
490
|
|
|
} |
491
|
1 |
|
|
492
|
1 |
|
$baseSql = 'ALTER TABLE ' . $this->db->quoteTableName($table) |
493
|
1 |
|
. ' ALTER ' . $this->db->quoteColumnName($column) |
494
|
1 |
|
. (($hasType) ? ' TYPE ' : ' ') . $this->getColumnType($type); |
495
|
1 |
|
|
496
|
1 |
|
if (version_compare($this->db->firebird_version, '3.0.0', '>=')) { |
497
|
|
|
$nullSql = false; |
498
|
|
|
|
499
|
1 |
|
if ($columnSchema->allowNull != $allowNullNewType) { |
500
|
|
|
$nullSql = 'ALTER TABLE ' . $this->db->quoteTableName($table) |
501
|
|
|
. ' ALTER ' . $this->db->quoteColumnName($column) |
502
|
1 |
|
. ($allowNullNewType ? ' DROP' : ' SET') |
503
|
1 |
|
. ' NOT NULL'; |
504
|
|
|
} |
505
|
|
|
|
506
|
1 |
|
$sql = 'EXECUTE BLOCK AS BEGIN' |
507
|
|
|
. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';'; |
508
|
|
|
|
509
|
|
|
/** |
510
|
|
|
* In any case (whichever option you choose), make sure that the column doesn't have any NULLs. |
511
|
|
|
* Firebird will not check it for you. Later when you backup the database, everything is fine, |
512
|
5 |
|
* but restore will fail as the NOT NULL column has NULLs in it. To be safe, each time you change from NULL to NOT NULL. |
513
|
|
|
*/ |
514
|
5 |
|
if (!$allowNullNewType) { |
515
|
|
|
$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0' |
516
|
5 |
|
. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;'; |
517
|
5 |
|
} |
518
|
|
|
|
519
|
|
|
if ($nullSql) { |
|
|
|
|
520
|
|
|
$sql .= ' EXECUTE STATEMENT ' . $this->db->quoteValue($nullSql) . ';'; |
521
|
5 |
|
} |
522
|
|
|
|
523
|
4 |
|
$sql .= ' END'; |
524
|
|
|
return $sql; |
525
|
|
|
} |
526
|
|
|
|
527
|
4 |
|
if ($columnSchema->allowNull == $allowNullNewType) { |
528
|
4 |
|
return $baseSql; |
529
|
|
|
} else { |
530
|
|
|
$sql = 'EXECUTE BLOCK AS BEGIN' |
531
|
|
|
. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';' |
532
|
|
|
. ' UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = ' . ($allowNullNewType ? 'NULL' : '1') |
533
|
|
|
. ' WHERE UPPER(RDB$FIELD_NAME) = UPPER(\'' . $column . '\') AND UPPER(RDB$RELATION_NAME) = UPPER(\'' . $table . '\');'; |
534
|
4 |
|
/** |
535
|
4 |
|
* In any case (whichever option you choose), make sure that the column doesn't have any NULLs. |
536
|
4 |
|
* Firebird will not check it for you. Later when you backup the database, everything is fine, |
537
|
4 |
|
* but restore will fail as the NOT NULL column has NULLs in it. To be safe, each time you change from NULL to NOT NULL. |
538
|
|
|
*/ |
539
|
|
|
if (!$allowNullNewType) { |
540
|
5 |
|
$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0' |
541
|
|
|
. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;'; |
542
|
|
|
} |
543
|
|
|
$sql .= ' END'; |
544
|
1 |
|
return $sql; |
545
|
|
|
} |
546
|
|
|
} |
547
|
|
|
|
548
|
|
|
/** |
549
|
|
|
* @inheritdoc |
550
|
3 |
|
*/ |
551
|
|
|
public function dropIndex($name, $table) |
552
|
3 |
|
{ |
553
|
|
|
return 'DROP INDEX ' . $this->db->quoteTableName($name); |
554
|
3 |
|
} |
555
|
3 |
|
|
556
|
1 |
|
/** |
557
|
|
|
* {@inheritdoc} |
558
|
|
|
*/ |
559
|
|
|
public function addDefaultValue($name, $table, $column, $value) |
560
|
|
|
{ |
561
|
|
|
return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN ' |
562
|
2 |
|
. $this->db->quoteColumnName($column) . ' SET DEFAULT ' . $this->db->quoteValue($value); |
563
|
2 |
|
} |
564
|
2 |
|
|
565
|
|
|
/** |
566
|
2 |
|
* {@inheritdoc} |
567
|
|
|
*/ |
568
|
|
|
public function dropDefaultValue($name, $table) |
569
|
|
|
{ |
570
|
|
|
return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN ' |
571
|
|
|
. $this->db->quoteColumnName($name) . ' DROP DEFAULT'; |
572
|
|
|
} |
573
|
|
|
|
574
|
|
|
/** |
575
|
|
|
* @inheritdoc |
576
|
3 |
|
*/ |
577
|
|
|
public function resetSequence($table, $value = null) |
578
|
3 |
|
{ |
579
|
|
|
$tableSchema = $this->db->getTableSchema($table); |
580
|
|
|
if ($tableSchema === null) { |
581
|
|
|
throw new InvalidParamException("Table not found: $table"); |
|
|
|
|
582
|
|
|
} |
583
|
|
|
if ($tableSchema->sequenceName === null) { |
584
|
|
|
throw new InvalidParamException("There is not sequence associated with table '$table'."); |
|
|
|
|
585
|
|
|
} |
586
|
|
|
|
587
|
|
|
if ($value !== null) { |
588
|
|
|
$value = (int) $value; |
589
|
|
|
} else { |
590
|
|
|
// use master connection to get the biggest PK value |
591
|
|
|
$value = $this->db->useMaster(function (Connection $db) use ($tableSchema) { |
592
|
|
|
$key = false; |
593
|
|
|
foreach ($tableSchema->primaryKey as $name) { |
594
|
|
|
if ($tableSchema->columns[$name]->autoIncrement) { |
595
|
|
|
$key = $name; |
596
|
|
|
break; |
597
|
|
|
} |
598
|
|
|
} |
599
|
|
|
if ($key === false) { |
600
|
|
|
return 0; |
601
|
|
|
} |
602
|
|
|
return $db->createCommand("SELECT MAX({$this->db->quoteColumnName($key)}) FROM {$this->db->quoteTableName($tableSchema->name)}")->queryScalar(); |
603
|
|
|
}) + 1; |
604
|
|
|
} |
605
|
|
|
|
606
|
|
|
return "ALTER SEQUENCE {$this->db->quoteColumnName($tableSchema->sequenceName)} RESTART WITH $value"; |
607
|
|
|
} |
608
|
|
|
|
609
|
|
|
/** |
610
|
|
|
* @inheritdoc |
611
|
|
|
*/ |
612
|
|
|
public function createTable($table, $columns, $options = null) |
613
|
|
|
{ |
614
|
|
|
$sql = parent::createTable($table, $columns, $options); |
615
|
|
|
|
616
|
|
|
if (version_compare($this->db->firebird_version, '3.0.0', '>=')) { |
617
|
|
|
return $sql; |
618
|
|
|
} |
619
|
|
|
|
620
|
|
|
foreach ($columns as $name => $type) { |
621
|
|
|
if (!is_string($name)) { |
622
|
|
|
continue; |
623
|
|
|
} |
624
|
|
|
|
625
|
|
|
if (strpos($type, Schema::TYPE_PK) === 0 || strpos($type, Schema::TYPE_BIGPK) === 0) { |
626
|
|
|
$sqlTrigger = <<<SQLTRIGGER |
627
|
|
|
CREATE TRIGGER tr_{$table}_{$name} FOR {$this->db->quoteTableName($table)} |
628
|
|
|
ACTIVE BEFORE INSERT POSITION 0 |
629
|
|
|
AS |
630
|
|
|
BEGIN |
631
|
|
|
if (NEW.{$this->db->quoteColumnName($name)} is NULL) then NEW.{$this->db->quoteColumnName($name)} = NEXT VALUE FOR seq_{$table}_{$name}; |
632
|
|
|
END |
633
|
|
|
SQLTRIGGER; |
634
|
|
|
|
635
|
|
|
$sqlBlock = <<<SQL |
636
|
|
|
EXECUTE block AS |
637
|
|
|
BEGIN |
638
|
|
|
EXECUTE STATEMENT {$this->db->quoteValue($sql)}; |
639
|
|
|
EXECUTE STATEMENT {$this->db->quoteValue("CREATE SEQUENCE seq_{$table}_{$name}")}; |
640
|
|
|
EXECUTE STATEMENT {$this->db->quoteValue($sqlTrigger)}; |
641
|
|
|
END; |
642
|
|
|
SQL; |
643
|
|
|
|
644
|
|
|
return $sqlBlock; |
645
|
|
|
} |
646
|
|
|
} |
647
|
|
|
|
648
|
|
|
return $sql; |
649
|
|
|
} |
650
|
|
|
|
651
|
|
|
/** |
652
|
|
|
* @inheritdoc |
653
|
|
|
*/ |
654
|
|
|
public function dropTable($table) |
655
|
|
|
{ |
656
|
|
|
$sql = parent::dropTable($table); |
657
|
|
|
|
658
|
|
|
$tableSchema = $this->db->getTableSchema($table); |
659
|
|
|
if ($tableSchema === null || $tableSchema->sequenceName === null) { |
660
|
|
|
return $sql; |
661
|
|
|
} |
662
|
|
|
|
663
|
|
|
/** |
664
|
|
|
* Not drop sequence for sequence "GENERATED BY DEFAULT AS IDENTITY" |
665
|
|
|
*/ |
666
|
|
|
if (version_compare($this->db->firebird_version, '3.0.0', '>=')) { |
667
|
|
|
$sqlUserSquence = 'SELECT 1 FROM RDB$GENERATORS |
668
|
|
|
WHERE RDB$SYSTEM_FLAG = 0 AND RDB$GENERATOR_NAME = :name'; |
669
|
|
|
|
670
|
|
|
$is_user_sequence = $this->db->createCommand($sqlUserSquence, [':name' => $tableSchema->sequenceName])->queryScalar(); |
671
|
|
|
|
672
|
|
|
if (!$is_user_sequence) { |
673
|
|
|
return $sql; |
674
|
|
|
} |
675
|
|
|
} |
676
|
|
|
|
677
|
|
|
$sqlBlock = <<<SQL |
678
|
|
|
EXECUTE block AS |
679
|
|
|
BEGIN |
680
|
|
|
EXECUTE STATEMENT {$this->db->quoteValue($sql)}; |
681
|
|
|
EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")}; |
682
|
|
|
END; |
683
|
|
|
SQL; |
684
|
|
|
return $sqlBlock; |
685
|
|
|
} |
686
|
|
|
|
687
|
|
|
/** |
688
|
|
|
* Creates a SELECT EXISTS() SQL statement. |
689
|
|
|
* @param string $rawSql the subquery in a raw form to select from. |
690
|
|
|
* @return string the SELECT EXISTS() SQL statement. |
691
|
|
|
* |
692
|
|
|
* @since 2.0.8 |
693
|
|
|
*/ |
694
|
|
|
public function selectExists($rawSql) |
695
|
|
|
{ |
696
|
|
|
return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM RDB$DATABASE'; |
697
|
|
|
} |
698
|
|
|
} |
699
|
|
|
|
This error could be the result of:
1. Missing dependencies
PHP Analyzer uses your
composer.json
file (if available) to determine the dependencies of your project and to determine all the available classes and functions. It expects thecomposer.json
to be in the root folder of your repository.Are you sure this class is defined by one of your dependencies, or did you maybe not list a dependency in either the
require
orrequire-dev
section?2. Missing use statement
PHP does not complain about undefined classes in
ìnstanceof
checks. For example, the following PHP code will work perfectly fine:If you have not tested against this specific condition, such errors might go unnoticed.