QueryBuilder::batchInsert() - Code Metrics - Inspection of "Added more rules for 'cs-fixer'" - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 2.1 (#15718)

by Alex

created 2018-02-28 13:52 UTC

QueryBuilder::batchInsert() C

↳ Parent: QueryBuilder

Complexity

Conditions	12
Paths	73

Size

Total Lines	47
Code Lines	31

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	156

Importance

Changes

Metric	Value
dl	0
loc	47
ccs	0
cts	41
cp	0
rs	5.1384
c	0
b	0
f	0
cc	12
eloc	31
nc	73
nop	3
crap	156

How to fix Complexity

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://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;

/**
 * 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',
        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 resetSequence($table, $value = null)
    {
        $tableSchema = $this->db->getTableSchema($table);
        if ($tableSchema === null) {
            throw new InvalidArgumentException("Unknown table: $table");
        }
        if ($tableSchema->sequenceName === null) {
            return '';
        }

        if ($value !== null) {
            $value = (int) $value;
        } else {
            // 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}\") FROM \"{$tableSchema->name}\"")->queryScalar();
            }) + 1;
        }

        return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
            . "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
    }

    /**
     * {@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);
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
$collection = json_decode($data, true);
if ( ! is_array($collection)) {
    throw new \RuntimeException('$collection must be an array.');
}

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

yiisoft / yii2

Pull Request — 2.1 (#15718)

QueryBuilder::batchInsert() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like