yiisoft /
db-sqlite
| Total Complexity | 125 |
| Total Lines | 1124 |
| Duplicated Lines | 0 % |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
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.
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 |
||
| 39 | final class QueryBuilder extends BaseQueryBuilder |
||
| 40 | { |
||
| 41 | /** |
||
| 42 | * @var array mapping from abstract column types (keys) to physical column types (values). |
||
| 43 | */ |
||
| 44 | protected array $typeMap = [ |
||
| 45 | Schema::TYPE_PK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL', |
||
| 46 | Schema::TYPE_UPK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL', |
||
| 47 | Schema::TYPE_BIGPK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL', |
||
| 48 | Schema::TYPE_UBIGPK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL', |
||
| 49 | Schema::TYPE_CHAR => 'char(1)', |
||
| 50 | Schema::TYPE_STRING => 'varchar(255)', |
||
| 51 | Schema::TYPE_TEXT => 'text', |
||
| 52 | Schema::TYPE_TINYINT => 'tinyint', |
||
| 53 | Schema::TYPE_SMALLINT => 'smallint', |
||
| 54 | Schema::TYPE_INTEGER => 'integer', |
||
| 55 | Schema::TYPE_BIGINT => 'bigint', |
||
| 56 | Schema::TYPE_FLOAT => 'float', |
||
| 57 | Schema::TYPE_DOUBLE => 'double', |
||
| 58 | Schema::TYPE_DECIMAL => 'decimal(10,0)', |
||
| 59 | Schema::TYPE_DATETIME => 'datetime', |
||
| 60 | Schema::TYPE_TIMESTAMP => 'timestamp', |
||
| 61 | Schema::TYPE_TIME => 'time', |
||
| 62 | Schema::TYPE_DATE => 'date', |
||
| 63 | Schema::TYPE_BINARY => 'blob', |
||
| 64 | Schema::TYPE_BOOLEAN => 'boolean', |
||
| 65 | Schema::TYPE_MONEY => 'decimal(19,4)', |
||
| 66 | ]; |
||
| 67 | |||
| 68 | /** |
||
| 69 | * Contains array of default expression builders. Extend this method and override it, if you want to change default |
||
| 70 | * expression builders for this query builder. |
||
| 71 | * |
||
| 72 | * @return array |
||
| 73 | * |
||
| 74 | * See {@see ExpressionBuilder} docs for details. |
||
| 75 | */ |
||
| 76 | protected function defaultExpressionBuilders(): array |
||
| 77 | { |
||
| 78 | return array_merge(parent::defaultExpressionBuilders(), [ |
||
| 79 | LikeCondition::class => LikeConditionBuilder::class, |
||
| 80 | InCondition::class => InConditionBuilder::class, |
||
| 81 | ]); |
||
| 82 | } |
||
| 83 | |||
| 84 | /** |
||
| 85 | * Generates a batch INSERT SQL statement. |
||
| 86 | * |
||
| 87 | * For example, |
||
| 88 | * |
||
| 89 | * ```php |
||
| 90 | * $connection->createCommand()->batchInsert('user', ['name', 'age'], [ |
||
| 91 | * ['Tom', 30], |
||
| 92 | * ['Jane', 20], |
||
| 93 | * ['Linda', 25], |
||
| 94 | * ])->execute(); |
||
| 95 | * ``` |
||
| 96 | * |
||
| 97 | * Note that the values in each row must match the corresponding column names. |
||
| 98 | * |
||
| 99 | * @param string $table the table that new rows will be inserted into. |
||
| 100 | * @param array $columns the column names |
||
| 101 | * @param array|\Generator $rows the rows to be batch inserted into the table |
||
| 102 | * @param array $params |
||
| 103 | * |
||
| 104 | * @throws Exception |
||
| 105 | * @throws InvalidConfigException |
||
| 106 | * @throws InvalidArgumentException |
||
| 107 | * @throws NotSupportedException |
||
| 108 | * |
||
| 109 | * @return string the batch INSERT SQL statement. |
||
| 110 | */ |
||
| 111 | public function batchInsert(string $table, array $columns, $rows, array &$params = []): string |
||
| 112 | { |
||
| 113 | if (empty($rows)) { |
||
| 114 | return ''; |
||
| 115 | } |
||
| 116 | |||
| 117 | /** |
||
| 118 | * SQLite supports batch insert natively since 3.7.11. |
||
| 119 | * |
||
| 120 | * {@see http://www.sqlite.org/releaselog/3_7_11.html} |
||
| 121 | */ |
||
| 122 | $this->db->open(); |
||
| 123 | |||
| 124 | if (version_compare($this->db->getServerVersion(), '3.7.11', '>=')) { |
||
| 125 | return parent::batchInsert($table, $columns, $rows, $params); |
||
| 126 | } |
||
| 127 | |||
| 128 | $schema = $this->db->getSchema(); |
||
| 129 | |||
| 130 | if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
||
| 131 | $columnSchemas = $tableSchema->getColumns(); |
||
| 132 | } else { |
||
| 133 | $columnSchemas = []; |
||
| 134 | } |
||
| 135 | |||
| 136 | $values = []; |
||
| 137 | |||
| 138 | foreach ($rows as $row) { |
||
| 139 | $vs = []; |
||
| 140 | foreach ($row as $i => $value) { |
||
| 141 | if (isset($columnSchemas[$columns[$i]])) { |
||
| 142 | $value = $columnSchemas[$columns[$i]]->dbTypecast($value); |
||
| 143 | } |
||
| 144 | if (is_string($value)) { |
||
| 145 | $value = $schema->quoteValue($value); |
||
| 146 | } elseif (is_float($value)) { |
||
| 147 | /* ensure type cast always has . as decimal separator in all locales */ |
||
| 148 | $value = NumericHelper::normalize($value); |
||
| 149 | } elseif ($value === false) { |
||
| 150 | $value = 0; |
||
| 151 | } elseif ($value === null) { |
||
| 152 | $value = 'NULL'; |
||
| 153 | } elseif ($value instanceof ExpressionInterface) { |
||
| 154 | $value = $this->buildExpression($value, $params); |
||
| 155 | } |
||
| 156 | $vs[] = $value; |
||
| 157 | } |
||
| 158 | $values[] = implode(', ', $vs); |
||
| 159 | } |
||
| 160 | |||
| 161 | if (empty($values)) { |
||
| 162 | return ''; |
||
| 163 | } |
||
| 164 | |||
| 165 | foreach ($columns as $i => $name) { |
||
| 166 | $columns[$i] = $schema->quoteColumnName($name); |
||
| 167 | } |
||
| 168 | |||
| 169 | return 'INSERT INTO ' . $schema->quoteTableName($table) |
||
| 170 | . ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values); |
||
| 171 | } |
||
| 172 | |||
| 173 | /** |
||
| 174 | * Creates a SQL statement for resetting the sequence value of a table's primary key. |
||
| 175 | * |
||
| 176 | * The sequence will be reset such that the primary key of the next new row inserted will have the specified value |
||
| 177 | * or 1. |
||
| 178 | * |
||
| 179 | * @param string $tableName the name of the table whose primary key sequence will be reset. |
||
| 180 | * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, the next new |
||
| 181 | * row's primary key will have a value 1. |
||
| 182 | * |
||
| 183 | * @throws Exception |
||
| 184 | * @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table. |
||
| 185 | * @throws InvalidConfigException |
||
| 186 | * @throws NotSupportedException |
||
| 187 | * @throws \Throwable |
||
| 188 | * |
||
| 189 | * @return string the SQL statement for resetting sequence. |
||
| 190 | */ |
||
| 191 | public function resetSequence(string $tableName, $value = null): string |
||
| 192 | { |
||
| 193 | $db = $this->db; |
||
| 194 | |||
| 195 | $table = $db->getTableSchema($tableName); |
||
| 196 | |||
| 197 | if ($table !== null && $table->getSequenceName() !== null) { |
||
| 198 | $tableName = $db->quoteTableName($tableName); |
||
| 199 | if ($value === null) { |
||
| 200 | $pk = $table->getPrimaryKey(); |
||
| 201 | $key = $this->db->quoteColumnName(reset($pk)); |
||
| 202 | $value = $this->db->useMaster(static function (Connection $db) use ($key, $tableName) { |
||
| 203 | return $db->createCommand("SELECT MAX($key) FROM $tableName")->queryScalar(); |
||
| 204 | }); |
||
| 205 | } else { |
||
| 206 | $value = (int) $value - 1; |
||
| 207 | } |
||
| 208 | |||
| 209 | return "UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->getName()}'"; |
||
| 210 | } elseif ($table === null) { |
||
| 211 | throw new InvalidArgumentException("Table not found: $tableName"); |
||
| 212 | } |
||
| 213 | |||
| 214 | throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.'"); |
||
| 215 | } |
||
| 216 | |||
| 217 | /** |
||
| 218 | * Enables or disables integrity check. |
||
| 219 | * |
||
| 220 | * @param bool $check whether to turn on or off the integrity check. |
||
| 221 | * @param string $schema the schema of the tables. Meaningless for SQLite. |
||
| 222 | * @param string $table the table name. Meaningless for SQLite. |
||
| 223 | * |
||
| 224 | * @return string the SQL statement for checking integrity. |
||
| 225 | */ |
||
| 226 | public function checkIntegrity(string $schema = '', string $table = '', bool $check = true): string |
||
| 227 | { |
||
| 228 | return 'PRAGMA foreign_keys=' . (int) $check; |
||
| 229 | } |
||
| 230 | |||
| 231 | /** |
||
| 232 | * Builds a SQL statement for truncating a DB table. |
||
| 233 | * |
||
| 234 | * @param string $table the table to be truncated. The name will be properly quoted by the method. |
||
| 235 | * |
||
| 236 | * @throws Exception |
||
| 237 | * @throws InvalidConfigException |
||
| 238 | * @throws NotSupportedException |
||
| 239 | * |
||
| 240 | * @return string the SQL statement for truncating a DB table. |
||
| 241 | */ |
||
| 242 | public function truncateTable(string $table): string |
||
| 243 | { |
||
| 244 | return 'DELETE FROM ' . $this->db->quoteTableName($table); |
||
| 245 | } |
||
| 246 | |||
| 247 | /** |
||
| 248 | * Builds a SQL statement for dropping an index. |
||
| 249 | * |
||
| 250 | * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. |
||
| 251 | * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. |
||
| 252 | * |
||
| 253 | * @throws Exception |
||
| 254 | * @throws InvalidConfigException |
||
| 255 | * @throws NotSupportedException |
||
| 256 | * |
||
| 257 | * @return string the SQL statement for dropping an index. |
||
| 258 | */ |
||
| 259 | public function dropIndex(string $name, string $table): string |
||
| 260 | { |
||
| 261 | return 'DROP INDEX ' . $this->db->quoteTableName($name); |
||
| 262 | } |
||
| 263 | |||
| 264 | /** |
||
| 265 | * Builds a SQL statement for dropping a DB column. |
||
| 266 | * |
||
| 267 | * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. |
||
| 268 | * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. |
||
| 269 | * |
||
| 270 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 271 | * |
||
| 272 | * @return string the SQL statement for dropping a DB column. |
||
| 273 | */ |
||
| 274 | public function dropColumn(string $table, string $column): string |
||
| 275 | { |
||
| 276 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 277 | } |
||
| 278 | |||
| 279 | /** |
||
| 280 | * Builds a SQL statement for renaming a column. |
||
| 281 | * |
||
| 282 | * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. |
||
| 283 | * @param string $oldName the old name of the column. The name will be properly quoted by the method. |
||
| 284 | * @param string $newName the new name of the column. The name will be properly quoted by the method. |
||
| 285 | * |
||
| 286 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 287 | * |
||
| 288 | * @return string the SQL statement for renaming a DB column. |
||
| 289 | */ |
||
| 290 | public function renameColumn(string $table, string $oldName, string $newName): string |
||
| 291 | { |
||
| 292 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 293 | } |
||
| 294 | |||
| 295 | /** |
||
| 296 | * Builds a SQL statement for adding a foreign key constraint to an existing table. |
||
| 297 | * |
||
| 298 | * The method will properly quote the table and column names. |
||
| 299 | * |
||
| 300 | * @param string $name the name of the foreign key constraint. |
||
| 301 | * @param string $table the table that the foreign key constraint will be added to. |
||
| 302 | * @param string|array $columns the name of the column to that the constraint will be added on. If there are |
||
| 303 | * multiple columns, separate them with commas or use an array to represent them. |
||
| 304 | * @param string $refTable the table that the foreign key references to. |
||
| 305 | * @param string|array $refColumns the name of the column that the foreign key references to. If there are multiple |
||
| 306 | * columns, separate them with commas or use an array to represent them. |
||
| 307 | * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, |
||
| 308 | * SET DEFAULT, SET NULL. |
||
| 309 | * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, |
||
| 310 | * SET DEFAULT, SET NULL. |
||
| 311 | * |
||
| 312 | * @throws Exception |
||
| 313 | * @throws InvalidConfigException |
||
| 314 | * @throws InvalidParamException |
||
| 315 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 316 | * |
||
| 317 | * @return string the SQL statement for adding a foreign key constraint to an existing table. |
||
| 318 | */ |
||
| 319 | public function addForeignKey( |
||
| 320 | string $name, |
||
| 321 | string $table, |
||
| 322 | $columns, |
||
| 323 | string $refTable, |
||
| 324 | $refColumns, |
||
| 325 | ?string $delete = null, |
||
| 326 | ?string $update = null |
||
| 327 | ): string { |
||
| 328 | $schema = $refschema = ''; |
||
| 329 | |||
| 330 | if (($pos = strpos($table, '.')) !== false) { |
||
| 331 | $schema = $this->unquoteTableName(substr($table, 0, $pos)); |
||
| 332 | $table = substr($table, $pos + 1); |
||
| 333 | } |
||
| 334 | |||
| 335 | if (($pos_ref = strpos($refTable, '.')) !== false) { |
||
| 336 | $refschema = substr($refTable, 0, $pos_ref); |
||
| 337 | $refTable = substr($refTable, $pos_ref + 1); |
||
| 338 | } |
||
| 339 | |||
| 340 | if (($schema !== '' || ($refschema !== '' && $schema !== $refschema))) { |
||
| 341 | return '' ; |
||
| 342 | } |
||
| 343 | |||
| 344 | if ($schema !== '') { |
||
| 345 | $tmp_table_name = "temp_{$schema}_" . $this->unquoteTableName($table); |
||
| 346 | $schema .= '.'; |
||
| 347 | $unquoted_tablename = $schema . $this->unquoteTableName($table); |
||
| 348 | $quoted_tablename = $schema . $this->db->quoteTableName($table); |
||
| 349 | } else { |
||
| 350 | $unquoted_tablename = $this->unquoteTableName($table); |
||
| 351 | $quoted_tablename = $this->db->quoteTableName($table); |
||
| 352 | $tmp_table_name = "temp_" . $this->unquoteTableName($table); |
||
| 353 | } |
||
| 354 | |||
| 355 | $fields_definitions_tokens = $this->getFieldDefinitionsTokens($unquoted_tablename); |
||
| 356 | $ddl_fields_defs = $fields_definitions_tokens->getSql(); |
||
| 357 | $ddl_fields_defs .= ",\nCONSTRAINT " . $this->db->quoteColumnName($name) . " FOREIGN KEY (" . |
||
| 358 | implode(",", (array)$columns) . ") REFERENCES $refTable(" . implode(",", (array)$refColumns) . ")"; |
||
| 359 | |||
| 360 | if ($update !== null) { |
||
| 361 | $ddl_fields_defs .= " ON UPDATE $update"; |
||
| 362 | } |
||
| 363 | |||
| 364 | if ($delete !== null) { |
||
| 365 | $ddl_fields_defs .= " ON DELETE $delete"; |
||
| 366 | } |
||
| 367 | |||
| 368 | $foreign_keys_state = $this->foreignKeysState(); |
||
| 369 | $return_queries = []; |
||
| 370 | $return_queries[] = "PRAGMA foreign_keys = off"; |
||
| 371 | $return_queries[] = "SAVEPOINT add_foreign_key_to_$tmp_table_name"; |
||
| 372 | $return_queries[] = "CREATE TEMP TABLE " . $this->db->quoteTableName($tmp_table_name) |
||
| 373 | . " AS SELECT * FROM $quoted_tablename"; |
||
| 374 | $return_queries[] = "DROP TABLE $quoted_tablename"; |
||
| 375 | $return_queries[] = "CREATE TABLE $quoted_tablename (" . trim($ddl_fields_defs, " \n\r\t,") . ")"; |
||
| 376 | $return_queries[] = "INSERT INTO $quoted_tablename SELECT * FROM " . $this->db->quoteTableName($tmp_table_name); |
||
| 377 | $return_queries[] = "DROP TABLE " . $this->db->quoteTableName($tmp_table_name); |
||
| 378 | $return_queries = array_merge($return_queries, $this->getIndexSqls($unquoted_tablename)); |
||
| 379 | |||
| 380 | $return_queries[] = "RELEASE add_foreign_key_to_$tmp_table_name"; |
||
| 381 | $return_queries[] = "PRAGMA foreign_keys = $foreign_keys_state"; |
||
| 382 | |||
| 383 | return implode(";", $return_queries); |
||
| 384 | } |
||
| 385 | |||
| 386 | /** |
||
| 387 | * Builds a SQL statement for dropping a foreign key constraint. |
||
| 388 | * |
||
| 389 | * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted |
||
| 390 | * by the method. |
||
| 391 | * @param string $tableName |
||
| 392 | * |
||
| 393 | * @throws Exception |
||
| 394 | * @throws InvalidConfigException |
||
| 395 | * @throws InvalidParamException |
||
| 396 | * @throws NotSupportedException |
||
| 397 | * |
||
| 398 | * @return string the SQL statement for dropping a foreign key constraint. |
||
| 399 | */ |
||
| 400 | public function dropForeignKey(string $name, string $tableName): string |
||
| 401 | { |
||
| 402 | $return_queries = []; |
||
| 403 | $ddl_fields_def = ''; |
||
| 404 | $sql_fields_to_insert = []; |
||
| 405 | $skipping = false; |
||
| 406 | $foreign_found = false; |
||
| 407 | $quoted_foreign_name = $this->db->quoteColumnName($name); |
||
| 408 | |||
| 409 | $quoted_tablename = $this->db->quoteTableName($tableName); |
||
| 410 | $unquoted_tablename = $this->unquoteTableName($tableName); |
||
| 411 | |||
| 412 | $fields_definitions_tokens = $this->getFieldDefinitionsTokens($unquoted_tablename); |
||
| 413 | |||
| 414 | $offset = 0; |
||
| 415 | $constraint_pos = 0; |
||
| 416 | |||
| 417 | /* Traverse the tokens looking for either an identifier (field name) or a foreign key */ |
||
| 418 | while ($fields_definitions_tokens->offsetExists($offset)) { |
||
| 419 | $token = $fields_definitions_tokens[$offset++]; |
||
| 420 | |||
| 421 | /** |
||
| 422 | * These searchs could be done with another SqlTokenizer, but I don't konw how to do them, the documentation |
||
| 423 | * for sqltokenizer si really scarse. |
||
| 424 | */ |
||
| 425 | $tokenType = $token->getType(); |
||
| 426 | |||
| 427 | if ($tokenType === SqlToken::TYPE_IDENTIFIER) { |
||
| 428 | $identifier = (string) $token; |
||
| 429 | $sql_fields_to_insert[] = $identifier; |
||
| 430 | } elseif ($tokenType === SqlToken::TYPE_KEYWORD) { |
||
| 431 | $keyword = (string) $token; |
||
| 432 | |||
| 433 | if ($keyword === 'CONSTRAINT' || $keyword === 'FOREIGN') { |
||
| 434 | /* Constraint key found */ |
||
| 435 | $other_offset = $offset; |
||
| 436 | |||
| 437 | if ($keyword === 'CONSTRAINT') { |
||
| 438 | $constraint_name = $this->db->quoteColumnName( |
||
| 439 | $fields_definitions_tokens[$other_offset]->getContent() |
||
| 440 | ); |
||
| 441 | } else { |
||
| 442 | $constraint_name = $this->db->quoteColumnName(strval($constraint_pos)); |
||
| 443 | } |
||
| 444 | |||
| 445 | if (($constraint_name === $quoted_foreign_name) || (is_int($name) && $constraint_pos === $name)) { |
||
| 446 | /* Found foreign key $name, skip it */ |
||
| 447 | $foreign_found = true; |
||
| 448 | $skipping = true; |
||
| 449 | $offset = $other_offset; |
||
| 450 | } |
||
| 451 | $constraint_pos++; |
||
| 452 | } |
||
| 453 | } else { |
||
| 454 | throw new NotSupportedException("Unexpected: $token"); |
||
| 455 | } |
||
| 456 | |||
| 457 | if (!$skipping) { |
||
| 458 | $ddl_fields_def .= $token . " "; |
||
| 459 | } |
||
| 460 | |||
| 461 | /* Skip or keep until the next */ |
||
| 462 | while ($fields_definitions_tokens->offsetExists($offset)) { |
||
| 463 | $skip_token = $fields_definitions_tokens[$offset]; |
||
| 464 | $skip_next = $fields_definitions_tokens[$offset + 1]; |
||
| 465 | |||
| 466 | if (!$skipping) { |
||
| 467 | $ddl_fields_def .= (string) $skip_token . ($skip_next == ',' ? '' : ' '); |
||
| 468 | } |
||
| 469 | |||
| 470 | $skipTokenType = $skip_token->getType(); |
||
| 471 | |||
| 472 | if ($skipTokenType === SqlToken::TYPE_OPERATOR && $skip_token == ',') { |
||
| 473 | $ddl_fields_def .= "\n"; |
||
| 474 | ++$offset; |
||
| 475 | $skipping = false; |
||
| 476 | break; |
||
| 477 | } |
||
| 478 | |||
| 479 | ++$offset; |
||
| 480 | } |
||
| 481 | } |
||
| 482 | |||
| 483 | if (!$foreign_found) { |
||
| 484 | throw new InvalidParamException("foreign key constraint '$name' not found in table '$tableName'"); |
||
| 485 | } |
||
| 486 | |||
| 487 | $foreign_keys_state = $this->foreignKeysState(); |
||
| 488 | |||
| 489 | $return_queries[] = "PRAGMA foreign_keys = 0"; |
||
| 490 | $return_queries[] = "SAVEPOINT drop_column_$unquoted_tablename"; |
||
| 491 | $return_queries[] = "CREATE TABLE " . $this->db->quoteTableName("temp_$unquoted_tablename") |
||
| 492 | . " AS SELECT * FROM $quoted_tablename"; |
||
| 493 | $return_queries[] = "DROP TABLE $quoted_tablename"; |
||
| 494 | $return_queries[] = "CREATE TABLE $quoted_tablename (" . trim($ddl_fields_def, " \n\r\t,") . ")"; |
||
| 495 | $return_queries[] = "INSERT INTO $quoted_tablename SELECT " . implode(",", $sql_fields_to_insert) . " FROM " |
||
| 496 | . $this->db->quoteTableName("temp_$unquoted_tablename"); |
||
| 497 | $return_queries[] = "DROP TABLE " . $this->db->quoteTableName("temp_$unquoted_tablename"); |
||
| 498 | $return_queries = array_merge($return_queries, $this->getIndexSqls($unquoted_tablename)); |
||
| 499 | $return_queries[] = "RELEASE drop_column_$unquoted_tablename"; |
||
| 500 | $return_queries[] = "PRAGMA foreign_keys = $foreign_keys_state"; |
||
| 501 | |||
| 502 | return implode(";", $return_queries); |
||
| 503 | } |
||
| 504 | |||
| 505 | /** |
||
| 506 | * Builds a SQL statement for renaming a DB table. |
||
| 507 | * |
||
| 508 | * @param string $table the table to be renamed. The name will be properly quoted by the method. |
||
| 509 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
| 510 | * |
||
| 511 | * @throws Exception |
||
| 512 | * @throws InvalidConfigException |
||
| 513 | * @throws NotSupportedException |
||
| 514 | * |
||
| 515 | * @return string the SQL statement for renaming a DB table. |
||
| 516 | */ |
||
| 517 | public function renameTable(string $table, string $newName): string |
||
| 518 | { |
||
| 519 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName); |
||
| 520 | } |
||
| 521 | |||
| 522 | /** |
||
| 523 | * Builds a SQL statement for changing the definition of a column. |
||
| 524 | * |
||
| 525 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the |
||
| 526 | * method. |
||
| 527 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
| 528 | * @param string $type the new column type. The [[getColumnType()]] method will be invoked to convert abstract |
||
| 529 | * column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the |
||
| 530 | * generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become |
||
| 531 | * 'varchar(255) not null'. |
||
| 532 | * |
||
| 533 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 534 | * |
||
| 535 | * @return string the SQL statement for changing the definition of a column. |
||
| 536 | */ |
||
| 537 | public function alterColumn(string $table, string $column, string $type): string |
||
| 538 | { |
||
| 539 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 540 | } |
||
| 541 | |||
| 542 | /** |
||
| 543 | * Builds a SQL statement for adding a primary key constraint to an existing table. |
||
| 544 | * |
||
| 545 | * @param string $name the name of the primary key constraint. |
||
| 546 | * @param string $table the table that the primary key constraint will be added to. |
||
| 547 | * @param string|array $columns comma separated string or array of columns that the primary key will consist of. |
||
| 548 | * |
||
| 549 | * @throws Exception |
||
| 550 | * @throws InvalidConfigException |
||
| 551 | * @throws InvalidParamException |
||
| 552 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 553 | * |
||
| 554 | * @return string the SQL statement for adding a primary key constraint to an existing table. |
||
| 555 | */ |
||
| 556 | public function addPrimaryKey(string $name, string $table, $columns): string |
||
| 557 | { |
||
| 558 | $return_queries = []; |
||
| 559 | $schema = ''; |
||
| 560 | |||
| 561 | if (($pos = strpos($table, '.')) !== false) { |
||
| 562 | $schema = $this->unquoteTableName(substr($table, 0, $pos)); |
||
| 563 | $table = substr($table, $pos + 1); |
||
| 564 | $unquoted_tablename = $schema . '.' . $this->unquoteTableName($table); |
||
| 565 | $quoted_tablename = $schema . '.' . $this->db->quoteTableName($table); |
||
| 566 | $tmp_table_name = "temp_{$schema}_" . $this->unquoteTableName($table); |
||
| 567 | } else { |
||
| 568 | $unquoted_tablename = $this->unquoteTableName($table); |
||
| 569 | $quoted_tablename = $this->db->quoteTableName($table); |
||
| 570 | $tmp_table_name = "temp_" . $this->unquoteTableName($table); |
||
| 571 | } |
||
| 572 | |||
| 573 | $fields_definitions_tokens = $this->getFieldDefinitionsTokens($unquoted_tablename); |
||
| 574 | $ddl_fields_defs = $fields_definitions_tokens->getSql(); |
||
| 575 | $ddl_fields_defs .= ", CONSTRAINT " . $this->db->quoteColumnName($name) . " PRIMARY KEY (" . |
||
| 576 | implode(",", (array)$columns) . ")"; |
||
| 577 | $foreign_keys_state = $this->foreignKeysState(); |
||
| 578 | $return_queries[] = "PRAGMA foreign_keys = 0"; |
||
| 579 | $return_queries[] = "SAVEPOINT add_primary_key_to_$tmp_table_name"; |
||
| 580 | $return_queries[] = "CREATE TABLE " . $this->db->quoteTableName($tmp_table_name) . |
||
| 581 | " AS SELECT * FROM $quoted_tablename"; |
||
| 582 | $return_queries[] = "DROP TABLE $quoted_tablename"; |
||
| 583 | $return_queries[] = "CREATE TABLE $quoted_tablename (" . trim($ddl_fields_defs, " \n\r\t,") . ")"; |
||
| 584 | $return_queries[] = "INSERT INTO $quoted_tablename SELECT * FROM " . $this->db->quoteTableName($tmp_table_name); |
||
| 585 | $return_queries[] = "DROP TABLE " . $this->db->quoteTableName($tmp_table_name); |
||
| 586 | |||
| 587 | $return_queries = array_merge($return_queries, $this->getIndexSqls($unquoted_tablename)); |
||
| 588 | |||
| 589 | $return_queries[] = "RELEASE add_primary_key_to_$tmp_table_name"; |
||
| 590 | $return_queries[] = "PRAGMA foreign_keys = $foreign_keys_state"; |
||
| 591 | |||
| 592 | return implode(";", $return_queries); |
||
| 593 | } |
||
| 594 | |||
| 595 | /** |
||
| 596 | * Builds a SQL statement for removing a primary key constraint to an existing table. |
||
| 597 | * |
||
| 598 | * @param string $name the name of the primary key constraint to be removed. |
||
| 599 | * @param string $table the table that the primary key constraint will be removed from. |
||
| 600 | * |
||
| 601 | * @throws NotSupportedException this is not supported by SQLite. |
||
| 602 | * |
||
| 603 | * @return string the SQL statement for removing a primary key constraint from an existing table. |
||
| 604 | */ |
||
| 605 | public function dropPrimaryKey(string $name, string $table): string |
||
| 606 | { |
||
| 607 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 608 | } |
||
| 609 | |||
| 610 | /** |
||
| 611 | * Creates a SQL command for adding an unique constraint to an existing table. |
||
| 612 | * |
||
| 613 | * @param string $name the name of the unique constraint. The name will be properly quoted by the method. |
||
| 614 | * @param string $table the table that the unique constraint will be added to. The name will be properly quoted by |
||
| 615 | * the method. |
||
| 616 | * @param string|array $columns the name of the column to that the constraint will be added on. If there are |
||
| 617 | * multiple columns, separate them with commas. The name will be properly quoted by the method. |
||
| 618 | * |
||
| 619 | * @throws Exception |
||
| 620 | * @throws InvalidArgumentException |
||
| 621 | * @throws InvalidConfigException |
||
| 622 | * @throws NotSupportedException |
||
| 623 | * |
||
| 624 | * @return string the SQL statement for adding an unique constraint to an existing table. |
||
| 625 | */ |
||
| 626 | public function addUnique(string $name, string $table, $columns): string |
||
| 627 | { |
||
| 628 | return $this->createIndex($name, $table, $columns, true); |
||
| 629 | } |
||
| 630 | |||
| 631 | /** |
||
| 632 | * Creates a SQL command for dropping an unique constraint. |
||
| 633 | * |
||
| 634 | * @param string $name the name of the unique constraint to be dropped. The name will be properly quoted by the |
||
| 635 | * method. |
||
| 636 | * @param string $table the table whose unique constraint is to be dropped. The name will be properly quoted by the |
||
| 637 | * method. |
||
| 638 | * |
||
| 639 | * @return string the SQL statement for dropping an unique constraint. |
||
| 640 | */ |
||
| 641 | public function dropUnique(string $name, string $table): string |
||
| 642 | { |
||
| 643 | return "DROP INDEX $name"; |
||
| 644 | } |
||
| 645 | |||
| 646 | /** |
||
| 647 | * Creates a SQL command for adding a check constraint to an existing table. |
||
| 648 | * |
||
| 649 | * @param string $name the name of the check constraint. The name will be properly quoted by the method. |
||
| 650 | * @param string $table the table that the check constraint will be added to. The name will be properly quoted by |
||
| 651 | * the method. |
||
| 652 | * @param string $expression the SQL of the `CHECK` constraint. |
||
| 653 | * |
||
| 654 | * @throws Exception |
||
| 655 | * @throws NotSupportedException |
||
| 656 | * |
||
| 657 | * @return string the SQL statement for adding a check constraint to an existing table. |
||
| 658 | */ |
||
| 659 | public function addCheck(string $name, string $table, string $expression): string |
||
| 660 | { |
||
| 661 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 662 | } |
||
| 663 | |||
| 664 | /** |
||
| 665 | * Creates a SQL command for dropping a check constraint. |
||
| 666 | * |
||
| 667 | * @param string $name the name of the check constraint to be dropped. The name will be properly quoted by the |
||
| 668 | * method. |
||
| 669 | * @param string $table the table whose check constraint is to be dropped. The name will be properly quoted by the |
||
| 670 | * method. |
||
| 671 | * |
||
| 672 | * @throws Exception |
||
| 673 | * @throws NotSupportedException |
||
| 674 | * |
||
| 675 | * @return string the SQL statement for dropping a check constraint. |
||
| 676 | */ |
||
| 677 | public function dropCheck(string $name, string $table): string |
||
| 678 | { |
||
| 679 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 680 | } |
||
| 681 | |||
| 682 | /** |
||
| 683 | * Creates a SQL command for adding a default value constraint to an existing table. |
||
| 684 | * |
||
| 685 | * @param string $name the name of the default value constraint. The name will be properly quoted by the method. |
||
| 686 | * @param string $table the table that the default value constraint will be added to. The name will be properly |
||
| 687 | * quoted by the method. |
||
| 688 | * @param string $column the name of the column to that the constraint will be added on. The name will be properly |
||
| 689 | * quoted by the method. |
||
| 690 | * @param mixed $value default value. |
||
| 691 | * |
||
| 692 | * @throws Exception |
||
| 693 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
| 694 | * |
||
| 695 | * @return string the SQL statement for adding a default value constraint to an existing table. |
||
| 696 | */ |
||
| 697 | public function addDefaultValue(string $name, string $table, string $column, $value): string |
||
| 698 | { |
||
| 699 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 700 | } |
||
| 701 | |||
| 702 | /** |
||
| 703 | * Creates a SQL command for dropping a default value constraint. |
||
| 704 | * |
||
| 705 | * @param string $name the name of the default value constraint to be dropped. The name will be properly quoted by |
||
| 706 | * the method. |
||
| 707 | * @param string $table the table whose default value constraint is to be dropped. The name will be properly quoted |
||
| 708 | * by the method. |
||
| 709 | * |
||
| 710 | * @throws Exception |
||
| 711 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
| 712 | * |
||
| 713 | * @return string the SQL statement for dropping a default value constraint. |
||
| 714 | */ |
||
| 715 | public function dropDefaultValue(string $name, string $table): string |
||
| 718 | } |
||
| 719 | |||
| 720 | /** |
||
| 721 | * Builds a SQL command for adding comment to column. |
||
| 722 | * |
||
| 723 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the |
||
| 724 | * method. |
||
| 725 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the |
||
| 726 | * method. |
||
| 727 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
| 728 | * |
||
| 729 | * @throws Exception |
||
| 730 | * @throws NotSupportedException |
||
| 731 | * |
||
| 732 | * @return string the SQL statement for adding comment on column. |
||
| 733 | */ |
||
| 734 | public function addCommentOnColumn(string $table, string $column, string $comment): string |
||
| 735 | { |
||
| 736 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 737 | } |
||
| 738 | |||
| 739 | /** |
||
| 740 | * Builds a SQL command for adding comment to table. |
||
| 741 | * |
||
| 742 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the |
||
| 743 | * method. |
||
| 744 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
| 745 | * |
||
| 746 | * @throws Exception |
||
| 747 | * @throws NotSupportedException |
||
| 748 | * |
||
| 749 | * @return string the SQL statement for adding comment on table. |
||
| 750 | */ |
||
| 751 | public function addCommentOnTable(string $table, string $comment): string |
||
| 752 | { |
||
| 753 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 754 | } |
||
| 755 | |||
| 756 | /** |
||
| 757 | * Builds a SQL command for adding comment to column. |
||
| 758 | * |
||
| 759 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the |
||
| 760 | * method. |
||
| 761 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the |
||
| 762 | * method. |
||
| 763 | * |
||
| 764 | * @throws Exception |
||
| 765 | * @throws NotSupportedException |
||
| 766 | * |
||
| 767 | * @return string the SQL statement for adding comment on column. |
||
| 768 | */ |
||
| 769 | public function dropCommentFromColumn(string $table, string $column): string |
||
| 770 | { |
||
| 771 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 772 | } |
||
| 773 | |||
| 774 | /** |
||
| 775 | * Builds a SQL command for adding comment to table. |
||
| 776 | * |
||
| 777 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the |
||
| 778 | * method. |
||
| 779 | * |
||
| 780 | * @throws Exception |
||
| 781 | * @throws NotSupportedException |
||
| 782 | * |
||
| 783 | * @return string the SQL statement for adding comment on column. |
||
| 784 | */ |
||
| 785 | public function dropCommentFromTable(string $table): string |
||
| 786 | { |
||
| 787 | throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
||
| 788 | } |
||
| 789 | |||
| 790 | /** |
||
| 791 | * @param int|object|null $limit |
||
| 792 | * @param int|object|null $offset |
||
| 793 | * |
||
| 794 | * @return string the LIMIT and OFFSET clauses. |
||
| 795 | */ |
||
| 796 | public function buildLimit($limit, $offset): string |
||
| 797 | { |
||
| 798 | $sql = ''; |
||
| 799 | |||
| 800 | if ($this->hasLimit($limit)) { |
||
| 801 | $sql = 'LIMIT ' . $limit; |
||
| 802 | if ($this->hasOffset($offset)) { |
||
| 803 | $sql .= ' OFFSET ' . $offset; |
||
| 804 | } |
||
| 805 | } elseif ($this->hasOffset($offset)) { |
||
| 806 | /** |
||
| 807 | * limit is not optional in SQLite. |
||
| 808 | * |
||
| 809 | * {@see http://www.sqlite.org/syntaxdiagrams.html#select-stmt} |
||
| 810 | */ |
||
| 811 | $sql = "LIMIT 9223372036854775807 OFFSET $offset"; // 2^63-1 |
||
| 812 | } |
||
| 813 | |||
| 814 | return $sql; |
||
| 815 | } |
||
| 816 | |||
| 817 | /** |
||
| 818 | * Generates a SELECT SQL statement from a {@see Query} object. |
||
| 819 | * |
||
| 820 | * @param Query $query the {@see Query} object from which the SQL statement will be generated. |
||
| 821 | * @param array $params the parameters to be bound to the generated SQL statement. These parameters will be included |
||
| 822 | * in the result with the additional parameters generated during the query building process. |
||
| 823 | * |
||
| 824 | * @throws Exception |
||
| 825 | * @throws InvalidArgumentException |
||
| 826 | * @throws InvalidConfigException |
||
| 827 | * @throws NotSupportedException |
||
| 828 | * |
||
| 829 | * @return array the generated SQL statement (the first array element) and the corresponding parameters to be bound |
||
| 830 | * to the SQL statement (the second array element). The parameters returned include those provided in `$params`. |
||
| 831 | */ |
||
| 832 | public function build(Query $query, array $params = []): array |
||
| 833 | { |
||
| 834 | $query = $query->prepare($this); |
||
| 835 | |||
| 836 | $params = empty($params) ? $query->getParams() : array_merge($params, $query->getParams()); |
||
| 837 | |||
| 838 | $clauses = [ |
||
| 839 | $this->buildSelect($query->getSelect(), $params, $query->getDistinct(), $query->getSelectOption()), |
||
| 840 | $this->buildFrom($query->getFrom(), $params), |
||
| 841 | $this->buildJoin($query->getJoin(), $params), |
||
| 842 | $this->buildWhere($query->getWhere(), $params), |
||
| 843 | $this->buildGroupBy($query->getGroupBy()), |
||
| 844 | $this->buildHaving($query->getHaving(), $params), |
||
| 845 | ]; |
||
| 846 | |||
| 847 | $sql = implode($this->separator, array_filter($clauses)); |
||
| 848 | $sql = $this->buildOrderByAndLimit($sql, $query->getOrderBy(), $query->getLimit(), $query->getOffset()); |
||
| 849 | |||
| 850 | if (!empty($query->getOrderBy())) { |
||
| 851 | foreach ($query->getOrderBy() as $expression) { |
||
| 852 | if ($expression instanceof ExpressionInterface) { |
||
| 853 | $this->buildExpression($expression, $params); |
||
| 854 | } |
||
| 855 | } |
||
| 856 | } |
||
| 857 | |||
| 858 | if (!empty($query->getGroupBy())) { |
||
| 859 | foreach ($query->getGroupBy() as $expression) { |
||
| 860 | if ($expression instanceof ExpressionInterface) { |
||
| 861 | $this->buildExpression($expression, $params); |
||
| 862 | } |
||
| 863 | } |
||
| 864 | } |
||
| 865 | |||
| 866 | $union = $this->buildUnion($query->getUnion(), $params); |
||
| 867 | |||
| 868 | if ($union !== '') { |
||
| 869 | $sql = "$sql{$this->separator}$union"; |
||
| 870 | } |
||
| 871 | |||
| 872 | $with = $this->buildWithQueries($query->getWithQueries(), $params); |
||
| 873 | |||
| 874 | if ($with !== '') { |
||
| 875 | $sql = "$with{$this->separator}$sql"; |
||
| 876 | } |
||
| 877 | |||
| 878 | return [$sql, $params]; |
||
| 879 | } |
||
| 880 | |||
| 881 | /** |
||
| 882 | * Builds a SQL statement for creating a new index. |
||
| 883 | * |
||
| 884 | * @param string $name the name of the index. The name will be properly quoted by the method. |
||
| 885 | * @param string $table the table that the new index will be created for. The table name will be properly quoted by |
||
| 886 | * the method. |
||
| 887 | * @param string|array $columns the column(s) that should be included in the index. If there are multiple columns, |
||
| 888 | * separate them with commas or use an array to represent them. Each column name will be properly quoted by the |
||
| 889 | * method, unless a parenthesis is found in the name. |
||
| 890 | * @param bool $unique whether to add UNIQUE constraint on the created index. |
||
| 891 | * |
||
| 892 | * @throws Exception |
||
| 893 | * @throws InvalidArgumentException |
||
| 894 | * @throws InvalidConfigException |
||
| 895 | * @throws NotSupportedException |
||
| 896 | * |
||
| 897 | * @return string the SQL statement for creating a new index. |
||
| 898 | */ |
||
| 899 | public function createIndex(string $name, string $table, $columns, bool $unique = false): string |
||
| 900 | { |
||
| 901 | $tableParts = explode('.', $table); |
||
| 902 | |||
| 903 | $schema = null; |
||
| 904 | if (count($tableParts) === 2) { |
||
| 905 | [$schema, $table] = $tableParts; |
||
| 906 | } |
||
| 907 | |||
| 908 | return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ') |
||
| 909 | . $this->db->quoteTableName(($schema ? $schema . '.' : '') . $name) . ' ON ' |
||
| 910 | . $this->db->quoteTableName($table) |
||
| 911 | . ' (' . $this->buildColumns($columns) . ')'; |
||
| 912 | } |
||
| 913 | |||
| 914 | /** |
||
| 915 | * @param array $unions |
||
| 916 | * @param array $params the binding parameters to be populated. |
||
| 917 | * |
||
| 918 | * @throws Exception |
||
| 919 | * @throws InvalidArgumentException |
||
| 920 | * @throws InvalidConfigException |
||
| 921 | * @throws NotSupportedException |
||
| 922 | * |
||
| 923 | * @return string the UNION clause built from {@see Query::$union}. |
||
| 924 | */ |
||
| 925 | public function buildUnion(array $unions, array &$params = []): string |
||
| 926 | { |
||
| 927 | if (empty($unions)) { |
||
| 928 | return ''; |
||
| 929 | } |
||
| 930 | |||
| 931 | $result = ''; |
||
| 932 | |||
| 933 | foreach ($unions as $i => $union) { |
||
| 934 | $query = $union['query']; |
||
| 935 | if ($query instanceof Query) { |
||
| 936 | [$unions[$i]['query'], $params] = $this->build($query, $params); |
||
| 937 | } |
||
| 938 | |||
| 939 | $result .= ' UNION ' . ($union['all'] ? 'ALL ' : '') . ' ' . $unions[$i]['query']; |
||
| 940 | } |
||
| 941 | |||
| 942 | return trim($result); |
||
| 943 | } |
||
| 944 | |||
| 945 | /** |
||
| 946 | * Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique |
||
| 947 | * constraints), or update them if they do. |
||
| 948 | * |
||
| 949 | * For example, |
||
| 950 | * |
||
| 951 | * ```php |
||
| 952 | * $sql = $queryBuilder->upsert('pages', [ |
||
| 953 | * 'name' => 'Front page', |
||
| 954 | * 'url' => 'http://example.com/', // url is unique |
||
| 955 | * 'visits' => 0, |
||
| 956 | * ], [ |
||
| 957 | * 'visits' => new \Yiisoft\Db\Expression('visits + 1'), |
||
| 958 | * ], $params); |
||
| 959 | * ``` |
||
| 960 | * |
||
| 961 | * The method will properly escape the table and column names. |
||
| 962 | * |
||
| 963 | * @param string $table the table that new rows will be inserted into/updated in. |
||
| 964 | * @param array|Query $insertColumns the column data (name => value) to be inserted into the table or instance |
||
| 965 | * of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement. |
||
| 966 | * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist. |
||
| 967 | * If `true` is passed, the column data will be updated to match the insert column data. |
||
| 968 | * If `false` is passed, no update will be performed if the column data already exists. |
||
| 969 | * @param array $params the binding parameters that will be generated by this method. |
||
| 970 | * They should be bound to the DB command later. |
||
| 971 | * |
||
| 972 | * @throws Exception |
||
| 973 | * @throws InvalidConfigException |
||
| 974 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
| 975 | * |
||
| 976 | * @return string the resulting SQL. |
||
| 977 | */ |
||
| 978 | public function upsert(string $table, $insertColumns, $updateColumns, array &$params): string |
||
| 979 | { |
||
| 980 | /** @var Constraint[] $constraints */ |
||
| 981 | $constraints = []; |
||
| 982 | |||
| 983 | [$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns( |
||
| 984 | $table, |
||
| 985 | $insertColumns, |
||
| 986 | $updateColumns, |
||
| 987 | $constraints |
||
| 988 | ); |
||
| 989 | |||
| 990 | if (empty($uniqueNames)) { |
||
| 991 | return $this->insert($table, $insertColumns, $params); |
||
| 992 | } |
||
| 993 | |||
| 994 | [, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params); |
||
| 995 | |||
| 996 | $insertSql = 'INSERT OR IGNORE INTO ' . $this->db->quoteTableName($table) |
||
| 997 | . (!empty($insertNames) ? ' (' . implode(', ', $insertNames) . ')' : '') |
||
| 998 | . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values); |
||
| 999 | |||
| 1000 | if ($updateColumns === false) { |
||
| 1001 | return $insertSql; |
||
| 1002 | } |
||
| 1003 | |||
| 1004 | $updateCondition = ['or']; |
||
| 1005 | $quotedTableName = $this->db->quoteTableName($table); |
||
| 1006 | |||
| 1007 | foreach ($constraints as $constraint) { |
||
| 1008 | $constraintCondition = ['and']; |
||
| 1009 | foreach ($constraint->getColumnNames() as $name) { |
||
| 1010 | $quotedName = $this->db->quoteColumnName($name); |
||
| 1011 | $constraintCondition[] = "$quotedTableName.$quotedName=(SELECT $quotedName FROM `EXCLUDED`)"; |
||
| 1012 | } |
||
| 1013 | $updateCondition[] = $constraintCondition; |
||
| 1014 | } |
||
| 1015 | |||
| 1016 | if ($updateColumns === true) { |
||
| 1017 | $updateColumns = []; |
||
| 1018 | foreach ($updateNames as $name) { |
||
| 1019 | $quotedName = $this->db->quoteColumnName($name); |
||
| 1020 | |||
| 1021 | if (strrpos($quotedName, '.') === false) { |
||
| 1022 | $quotedName = "(SELECT $quotedName FROM `EXCLUDED`)"; |
||
| 1023 | } |
||
| 1024 | $updateColumns[$name] = new Expression($quotedName); |
||
| 1025 | } |
||
| 1026 | } |
||
| 1027 | |||
| 1028 | $updateSql = 'WITH "EXCLUDED" (' . implode(', ', $insertNames) |
||
| 1029 | . ') AS (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' |
||
| 1030 | : ltrim($values, ' ')) . ') ' . $this->update($table, $updateColumns, $updateCondition, $params); |
||
| 1031 | |||
| 1032 | return "$updateSql; $insertSql;"; |
||
| 1033 | } |
||
| 1034 | |||
| 1035 | private function unquoteTableName(string $tableName): string |
||
| 1038 | } |
||
| 1039 | |||
| 1040 | private function getFieldDefinitionsTokens($tableName) |
||
| 1041 | { |
||
| 1042 | $create_table = $this->getCreateTable($tableName); |
||
| 1043 | |||
| 1044 | /** Parse de CREATE TABLE statement to skip any use of this column, namely field definitions and FOREIGN KEYS */ |
||
| 1045 | $code = (new SqlTokenizer($create_table))->tokenize(); |
||
| 1046 | $pattern = (new SqlTokenizer('any CREATE any TABLE any()'))->tokenize(); |
||
| 1047 | if (!$code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) { |
||
| 1048 | throw new InvalidParamException("Table not found: $tableName"); |
||
| 1049 | } |
||
| 1050 | |||
| 1051 | /** Get the fields definition and foreign keys tokens */ |
||
| 1052 | return $code[0][$lastMatchIndex - 1]; |
||
| 1053 | } |
||
| 1054 | |||
| 1055 | private function getCreateTable($tableName) |
||
| 1073 | } |
||
| 1074 | |||
| 1075 | private function foreignKeysState() |
||
| 1076 | { |
||
| 1077 | return $this->db->createCommand("PRAGMA foreign_keys")->queryScalar(); |
||
| 1078 | } |
||
| 1079 | |||
| 1080 | private function getIndexSqls(string $tableName, $skipColumn = null, $newColumn = null): array |
||
| 1081 | { |
||
| 1082 | /** Get all indexes on this table */ |
||
| 1083 | $indexes = $this->db->createCommand( |
||
| 1084 | "select SQL from SQLite_Master where tbl_name = '$tableName' and type='index'" |
||
| 1085 | )->queryAll(); |
||
| 1086 | |||
| 1087 | if ($skipColumn === null) { |
||
| 1088 | return array_column($indexes, "sql"); |
||
| 1089 | } |
||
| 1090 | |||
| 1163 | } |
||
| 1164 | } |
||
| 1165 |
Loading history...
Let?s assume that you have a directory layout like this:
. |-- OtherDir | |-- Bar.php | `-- Foo.php `-- SomeDir `-- Foo.phpand let?s assume the following content of
Bar.php:If both files
OtherDir/Foo.phpandSomeDir/Foo.phpare loaded in the same runtime, you will see a PHP error such as the following:PHP Fatal error: Cannot use SomeDir\Foo as Foo because the name is already in use in OtherDir/Foo.phpHowever, as
OtherDir/Foo.phpdoes not necessarily have to be loaded and the error is only triggered if it is loaded beforeOtherDir/Bar.php, this problem might go unnoticed for a while. In order to prevent this error from surfacing, you must import the namespace with a different alias: