QueryBuilder::batchInsert() - Code Metrics - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

QueryBuilder::batchInsert() C
last analyzed 2024-11-05 00:06 UTC

↳ Parent: QueryBuilder

Complexity

Conditions	13
Paths	85

Size

Total Lines	48
Code Lines	32

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	182

Importance

Changes

Metric	Value
cc	13
eloc	32
nc	85
nop	4
dl	0
loc	48
ccs	0
cts	32
cp	0
crap	182
rs	6.6166
c	0
b	0
f	0

How to fix Complexity

<?php
/**
 * @link https://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license https://www.yiiframework.com/license/
 */

namespace yii\db\oci;

use yii\base\InvalidArgumentException;
use yii\db\Connection;
use yii\db\Constraint;
use yii\db\Exception;
use yii\db\Expression;
use yii\db\Query;
use yii\helpers\StringHelper;
use yii\db\ExpressionInterface;

/**
 * QueryBuilder is the query builder for Oracle databases.
 *
 * @author Qiang Xue <[email protected]>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK => 'NUMBER(10) NOT NULL PRIMARY KEY',
filter:
    dependency_paths: ["lib/*"]
        Schema::TYPE_UPK => 'NUMBER(10) UNSIGNED NOT NULL PRIMARY KEY',
        Schema::TYPE_BIGPK => 'NUMBER(20) NOT NULL PRIMARY KEY',
        Schema::TYPE_UBIGPK => 'NUMBER(20) UNSIGNED NOT NULL PRIMARY KEY',
        Schema::TYPE_CHAR => 'CHAR(1)',
        Schema::TYPE_STRING => 'VARCHAR2(255)',
        Schema::TYPE_TEXT => 'CLOB',
        Schema::TYPE_TINYINT => 'NUMBER(3)',
        Schema::TYPE_SMALLINT => 'NUMBER(5)',
        Schema::TYPE_INTEGER => 'NUMBER(10)',
        Schema::TYPE_BIGINT => 'NUMBER(20)',
        Schema::TYPE_FLOAT => 'NUMBER',
        Schema::TYPE_DOUBLE => 'NUMBER',
        Schema::TYPE_DECIMAL => 'NUMBER',
        Schema::TYPE_DATETIME => 'TIMESTAMP',
        Schema::TYPE_TIMESTAMP => 'TIMESTAMP',
        Schema::TYPE_TIME => 'TIMESTAMP',
        Schema::TYPE_DATE => 'DATE',
        Schema::TYPE_BINARY => 'BLOB',
        Schema::TYPE_BOOLEAN => 'NUMBER(1)',
        Schema::TYPE_MONEY => 'NUMBER(19,4)',
    ];


    /**
     * {@inheritdoc}
     */
    protected function defaultExpressionBuilders()
    {
        return array_merge(parent::defaultExpressionBuilders(), [
            'yii\db\conditions\InCondition' => 'yii\db\oci\conditions\InConditionBuilder',
            'yii\db\conditions\LikeCondition' => 'yii\db\oci\conditions\LikeConditionBuilder',
        ]);
    }

    /**
     * {@inheritdoc}
     */
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
        }

        $filters = [];
        if ($this->hasOffset($offset)) {
            $filters[] = 'rowNumId > ' . $offset;
        }
        if ($this->hasLimit($limit)) {
            $filters[] = 'rownum <= ' . $limit;
        }
        if (empty($filters)) {
            return $sql;
        }

        $filter = implode(' AND ', $filters);
        return <<<EOD
WITH USER_SQL AS ($sql),
    PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
SELECT *
FROM PAGINATION
WHERE $filter
EOD;
    }

    /**
     * Builds a SQL statement for renaming a DB table.
     *
     * @param string $table the table to be renamed. The name will be properly quoted by the method.
     * @param string $newName the new table name. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB table.
     */
    public function renameTable($table, $newName)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
    }

    /**
     * Builds a SQL statement for changing the definition of a column.
     *
     * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
     * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
     * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
     * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
     * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
     * @return string the SQL statement for changing the definition of a column.
     */
    public function alterColumn($table, $column, $type)
    {
        $type = $this->getColumnType($type);

        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type);
    }

    /**
     * Builds a SQL statement for dropping an index.
     *
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping an index.
     */
    public function dropIndex($name, $table)
    {
        return 'DROP INDEX ' . $this->db->quoteTableName($name);
    }

    /**
     * {@inheritdoc}
     */
    public function executeResetSequence($table, $value = null)
    {
        $tableSchema = $this->db->getTableSchema($table);
        if ($tableSchema === null) {
            throw new InvalidArgumentException("Unknown table: $table");
        }
        if ($tableSchema->sequenceName === null) {
            throw new InvalidArgumentException("There is no sequence associated with table: $table");
        }

        if ($value !== null) {
            $value = (int) $value;
        } else {
            if (count($tableSchema->primaryKey) > 1) {
                throw new InvalidArgumentException("Can't reset sequence for composite primary key in table: $table");
            }
            // use master connection to get the biggest PK value
            $value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
                return $db->createCommand(
                    'SELECT MAX("' . $tableSchema->primaryKey[0] . '") FROM "' . $tableSchema->name . '"'
                )->queryScalar();
            }) + 1;
        }

        //Oracle needs at least two queries to reset sequence (see adding transactions and/or use alter method to avoid grants' issue?)
        $this->db->createCommand('DROP SEQUENCE "' . $tableSchema->sequenceName . '"')->execute();
        $this->db->createCommand('CREATE SEQUENCE "' . $tableSchema->sequenceName . '" START WITH ' . $value
            . ' INCREMENT BY 1 NOMAXVALUE NOCACHE')->execute();
    }

    /**
     * {@inheritdoc}
     */
    public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
    {
        $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
            . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
            . ' REFERENCES ' . $this->db->quoteTableName($refTable)
            . ' (' . $this->buildColumns($refColumns) . ')';
        if ($delete !== null) {
            $sql .= ' ON DELETE ' . $delete;
        }
        if ($update !== null) {
            throw new Exception('Oracle does not support ON UPDATE clause.');
        }

        return $sql;
    }

    /**
     * {@inheritdoc}
     */
    protected function prepareInsertValues($table, $columns, $params = [])
    {
        list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params);
        if (!$columns instanceof Query && empty($names)) {
            $tableSchema = $this->db->getSchema()->getTableSchema($table);
            if ($tableSchema !== null) {
                $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : [reset($tableSchema->columns)->name];
                foreach ($columns as $name) {
                    $names[] = $this->db->quoteColumnName($name);
                    $placeholders[] = 'DEFAULT';
                }
            }
        }
        return [$names, $placeholders, $values, $params];
    }

    /**
     * {@inheritdoc}
     * @see https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
     */
    public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        /** @var Constraint[] $constraints */
        list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }
        if ($updateNames === []) {
            // there are no columns to update
            $updateColumns = false;
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
            }
            $onCondition[] = $constraintCondition;
        }
        $on = $this->buildCondition($onCondition, $params);
        list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
        if (!empty($placeholders)) {
            $usingSelectValues = [];
            foreach ($insertNames as $index => $name) {
                $usingSelectValues[$name] = new Expression($placeholders[$index]);
            }
            $usingSubQuery = (new Query())
                ->select($usingSelectValues)
                ->from('DUAL');
            list($usingValues, $params) = $this->build($usingSubQuery, $params);
        }
        $mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' '
            . 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" '
            . "ON ($on)";
        $insertValues = [];
        foreach ($insertNames as $name) {
            $quotedName = $this->db->quoteColumnName($name);
            if (strrpos($quotedName, '.') === false) {
                $quotedName = '"EXCLUDED".' . $quotedName;
            }
            $insertValues[] = $quotedName;
        }
        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
            . ' VALUES (' . implode(', ', $insertValues) . ')';
        if ($updateColumns === false) {
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);
        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
    }

    /**
     * Generates a batch INSERT SQL statement.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
     *     ['Tom', 30],
     *     ['Jane', 20],
     *     ['Linda', 25],
     * ]);
     * ```
     *
     * Note that the values in each row must match the corresponding column names.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array $columns the column names
     * @param array|\Generator $rows the rows to be batch inserted into the table
     * @return string the batch INSERT SQL statement
     */
    public function batchInsert($table, $columns, $rows, &$params = [])
    {
        if (empty($rows)) {
            return '';
        }

        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }

        $values = [];
        foreach ($rows as $row) {
            $vs = [];
            foreach ($row as $i => $value) {
                if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }
                if (is_string($value)) {
                    $value = $schema->quoteValue($value);
                } elseif (is_float($value)) {
                    // ensure type cast always has . as decimal separator in all locales
                    $value = StringHelper::floatToString($value);
                } elseif ($value === false) {
                    $value = 0;
                } elseif ($value === null) {
                    $value = 'NULL';
                } elseif ($value instanceof ExpressionInterface) {
                    $value = $this->buildExpression($value, $params);
                }
                $vs[] = $value;
            }
            $values[] = '(' . implode(', ', $vs) . ')';
        }
        if (empty($values)) {
            return '';
        }

        foreach ($columns as $i => $name) {
            $columns[$i] = $schema->quoteColumnName($name);
        }

        $tableAndColumns = ' INTO ' . $schema->quoteTableName($table)
        . ' (' . implode(', ', $columns) . ') VALUES ';

        return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function selectExists($rawSql)
    {
        return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM DUAL';
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
    }
}


1			<?php
2			/**
3			* @link https://www.yiiframework.com/
4			* @copyright Copyright (c) 2008 Yii Software LLC
5			* @license https://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',
			0 ignored issues – show Bug introduced 2019-02-03 21:14 UTC by Report Bug Copy Issue Report The type `yii\db\oci\Schema` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
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 master connection to get the biggest PK value
158			$value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
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

yiisoft / yii2

QueryBuilder::batchInsert() C last analyzed 2024-11-05 00:06 UTC

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like

QueryBuilder::batchInsert() C
last analyzed 2024-11-05 00:06 UTC