BulkInsertQuery::execute() - Code Metrics - Inspection of "Bulk inserts" - doctrine/dbal - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Pull Request — master (#2762)

by Evgenij

created 2017-06-29 20:27 UTC

BulkInsertQuery::execute() B

↳ Parent: BulkInsertQuery

Complexity

Conditions	5
Paths	3

Size

Total Lines	24
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	15
CRAP Score	5

Importance

Changes

Metric	Value
dl	0
loc	24
ccs	15
cts	15
cp	1
rs	8.5125
c	0
b	0
f	0
cc	5
eloc	15
nc	3
nop	0
crap	5

<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the MIT license. For more information, see
 * <http://www.doctrine-project.org>.
 */
declare (strict_types=1);

namespace Doctrine\DBAL\Query;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Identifier;

/**
 * Provides functionality to generate and execute bulk INSERT statements.
 *
 * Intended for row based inserts, not from SELECT statements.
 *
 * @author Steve Müller <[email protected]>
 * @link   www.doctrine-project.org
 * @since  2.6
 */
class BulkInsertQuery
{
    /**
     * @var string[]
     */
    private $columns;

    /**
     * @var Connection
     */
    private $connection;

    /**
     * @var Identifier
     */
    private $table;

    /**
     * @var array
     */
    private $parameters = array();

    /**
     * @var array
     */
    private $types = array();

    /**
     * @var array
     */
    private $values = array();

    /**
     * Constructor.
     *
     * @param Connection $connection The connection to use for query execution.
     * @param string     $table      The name of the table to insert rows into.
     * @param string[]   $columns    The names of the columns to insert values into.
     *                               Can be left empty to allow arbitrary table row inserts
     *                               based on the table's column order.
     */
    public function __construct(Connection $connection, string $table, array $columns = array())
    {
        $this->connection = $connection;
        $this->table = new Identifier($table);
        $this->columns = $columns;
    }

    /**
     * Adds a set of values to the bulk insert query to be inserted as a row into the specified table.
     *
     * @param array $values The set of values to be inserted as a row into the table.
     *                      If no columns have been specified for insertion, this can be
     *                      an arbitrary list of values to be inserted into the table.
     *                      Otherwise the values' keys have to match either one of the
     *                      specified column names or indexes.
     * @param array $types  The types for the given values to bind to the query.
     *                      If no columns have been specified for insertion, the types'
     *                      keys will be matched against the given values' keys.
     *                      Otherwise the types' keys will be matched against the
     *                      specified column names and indexes.
     *                      Non-matching keys will be discarded, missing keys will not
     *                      be bound to a specific type.
     *
     * @throws \InvalidArgumentException if columns were specified for this query
     *                                   and either no value for one of the specified
     *                                   columns is given or multiple values are given
     *                                   for a single column (named and indexed) or
     *                                   multiple types are given for a single column
     *                                   (named and indexed).
     *
     * @todo add support for expressions.
     */
    public function addValues(array $values, array $types = array())
    {
        if (empty($this->columns)) {
            $this->addValuesInDbOrder($values, $types);
        } else {
            $this->addValuesByColumnDefinition($values, $types);
        }
    }

    /**
     * Executes this INSERT query using the bound parameters and their types.
     *
     * @return integer The number of affected rows.
     *
     * @throws \LogicException if this query contains more rows than acceptable
     *                         for a single INSERT statement by the underlying platform.
     */
    public function execute(): int
    {
        $platform = $this->connection->getDatabasePlatform();
        $insertMaxRows = $platform->getInsertMaxRows();
        $totalValues = count($this->values);

        if ($insertMaxRows > 0 && $totalValues > $insertMaxRows && !$this->connection->isTransactionActive()) {
            throw QueryException::transactionRequired();
        }

        $offset = 0;
        $result = 0;
        while ($offset < $totalValues) {
            $values = array_slice($this->values, $offset, $insertMaxRows);
            $parameters = array_slice($this->parameters, $offset, $insertMaxRows);
            $types = array_slice($this->types, $offset, $insertMaxRows);

            $result += $this->connection->executeUpdate($this->getSqlForValues($values), $parameters, $types);

            $offset += $insertMaxRows;
        }

        return $result;
    }

    /**
     * Returns the parameters for this INSERT query being constructed indexed by parameter index.
     *
     * @return array
     */
    public function getParameters(): array
    {
        return $this->parameters;
    }

    /**
     * Returns the parameter types for this INSERT query being constructed indexed by parameter index.
     *
     * @return array
     */
    public function getParameterTypes(): array
    {
        return $this->types;
    }

    /**
     * Returns the SQL formed by the current specifications of this INSERT query.
     *
     * @return string
     *
     * @throws \LogicException if no values have been specified yet.
     */
    public function getSQL(): string
    {
        if (empty($this->values)) {
            throw new \LogicException('You need to add at least one set of values before generating the SQL.');
        }

        return $this->getSqlForValues($this->values);
    }

    /**
     * This method handles the situation when user did not specified any columns at all
     *
     * @param array $values Values to be inserted
     * @param array $types  Corresponding value types
     *
     * @return void
     */
    private function addValuesInDbOrder(array $values, array $types): void
    {
        $valueSet = [];
        foreach ($values as $index => $value) {
            $this->parameters[] = $value; // todo: allow expressions.
            $this->types[]      = $types[$index] ?? null;
            $valueSet[]         = '?'; // todo: allow expressions.
        }

        $this->values[] = $valueSet;
    }

    /**
     * Handles adding values when user has specified column names
     *
     * @param array $values Values to be inserted
     * @param array $types  Corresponding value types
     *
     * @return void
     */
    private function addValuesByColumnDefinition(array $values, array $types): void
    {
        $valueSet = array();
        foreach ($this->columns as $index => $column) {
            $namedValue      = isset($values[$column]) || array_key_exists($column, $values);
            $positionalValue = isset($values[$index]) || array_key_exists($index, $values);

            if (!$namedValue && !$positionalValue) {
                throw new \InvalidArgumentException(
                    sprintf('No value specified for column %s (index %d).', $column, $index)
                );
            }

            if ($namedValue && $positionalValue && $values[$column] !== $values[$index]) {
                throw new \InvalidArgumentException(
                    sprintf('Multiple values specified for column %s (index %d).', $column, $index)
                );
            }

            $this->parameters[] = $namedValue ? $values[$column] : $values[$index]; // todo: allow expressions.
            $valueSet[] = '?'; // todo: allow expressions.

            if (isset($types[$column], $types[$index]) && $types[$column] !== $types[$index]) {
                throw new \InvalidArgumentException(
                    sprintf('Multiple types specified for column %s (index %d).', $column, $index)
                );
            }

            $this->types[] = $types[$column] ?? $types[$index] ?? null;
        }

        $this->values[] = $valueSet;
    }

    /**
     * Return query for given list of values
     *
     * @param array $values Values to be inserted
     *
     * @return string
     */
    private function getSqlForValues(array $values): string
    {
        $platform   = $this->connection->getDatabasePlatform();
        $columnList = '';

        if (!empty($this->columns)) {
            $columnList = sprintf(
                ' (%s)',
                implode(
                    ', ',
                    array_map(
                        function ($column) use ($platform) {
                            $column = new Identifier($column);

                            return $column->getQuotedName($platform);
                        },
                        $this->columns
                    )
                )
            );
        }

        return sprintf(
            'INSERT INTO %s%s VALUES (%s)',
            $this->table->getQuotedName($platform),
            $columnList,
            implode(
                '), (',
                array_map(
                    function (array $valueSet) {
                        return implode(', ', $valueSet);
                    },
                    $values
                )
            )
        );
    }
}


1		<?php
2		/*
3		* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
4		* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
5		* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
6		* A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
7		* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
8		* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
9		* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
10		* DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
11		* THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
12		* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
13		* OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
14		*
15		* This software consists of voluntary contributions made by many individuals
16		* and is licensed under the MIT license. For more information, see
17		* <http://www.doctrine-project.org>.
18		*/
19		declare (strict_types=1);
20
21		namespace Doctrine\DBAL\Query;
22
23		use Doctrine\DBAL\Connection;
24		use Doctrine\DBAL\Schema\Identifier;
25
26		/**
27		* Provides functionality to generate and execute bulk INSERT statements.
28		*
29		* Intended for row based inserts, not from SELECT statements.
30		*
31		* @author Steve Müller <[email protected]>
32		* @link www.doctrine-project.org
33		* @since 2.6
34		*/
35		class BulkInsertQuery
36		{
37		/**
38		* @var string[]
39		*/
40		private $columns;
41
42		/**
43		* @var Connection
44		*/
45		private $connection;
46
47		/**
48		* @var Identifier
49		*/
50		private $table;
51
52		/**
53		* @var array
54		*/
55		private $parameters = array();
56
57		/**
58		* @var array
59		*/
60		private $types = array();
61
62		/**
63		* @var array
64		*/
65		private $values = array();
66
67		/**
68		* Constructor.
69		*
70		* @param Connection $connection The connection to use for query execution.
71		* @param string $table The name of the table to insert rows into.
72		* @param string[] $columns The names of the columns to insert values into.
73		* Can be left empty to allow arbitrary table row inserts
74		* based on the table's column order.
75		*/
76	13	public function __construct(Connection $connection, string $table, array $columns = array())
77		{
78	13	$this->connection = $connection;
79	13	$this->table = new Identifier($table);
80	13	$this->columns = $columns;
81	13	}
82
83		/**
84		* Adds a set of values to the bulk insert query to be inserted as a row into the specified table.
85		*
86		* @param array $values The set of values to be inserted as a row into the table.
87		* If no columns have been specified for insertion, this can be
88		* an arbitrary list of values to be inserted into the table.
89		* Otherwise the values' keys have to match either one of the
90		* specified column names or indexes.
91		* @param array $types The types for the given values to bind to the query.
92		* If no columns have been specified for insertion, the types'
93		* keys will be matched against the given values' keys.
94		* Otherwise the types' keys will be matched against the
95		* specified column names and indexes.
96		* Non-matching keys will be discarded, missing keys will not
97		* be bound to a specific type.
98		*
99		* @throws \InvalidArgumentException if columns were specified for this query
100		* and either no value for one of the specified
101		* columns is given or multiple values are given
102		* for a single column (named and indexed) or
103		* multiple types are given for a single column
104		* (named and indexed).
105		*
106		* @todo add support for expressions.
107		*/
108	12	public function addValues(array $values, array $types = array())
109		{
110	12	if (empty($this->columns)) {
111	4	$this->addValuesInDbOrder($values, $types);
112		} else {
113	8	$this->addValuesByColumnDefinition($values, $types);
114		}
115	9	}
116
117		/**
118		* Executes this INSERT query using the bound parameters and their types.
119		*
120		* @return integer The number of affected rows.
121		*
122		* @throws \LogicException if this query contains more rows than acceptable
123		* for a single INSERT statement by the underlying platform.
124		*/
125	2	public function execute(): int
126		{
127	2	$platform = $this->connection->getDatabasePlatform();
128	2	$insertMaxRows = $platform->getInsertMaxRows();
129	2	$totalValues = count($this->values);
130
131	2	if ($insertMaxRows > 0 && $totalValues > $insertMaxRows && !$this->connection->isTransactionActive()) {
132	1	throw QueryException::transactionRequired();
133		}
134
135	1	$offset = 0;
136	1	$result = 0;
137	1	while ($offset < $totalValues) {
138	1	$values = array_slice($this->values, $offset, $insertMaxRows);
139	1	$parameters = array_slice($this->parameters, $offset, $insertMaxRows);
140	1	$types = array_slice($this->types, $offset, $insertMaxRows);
141
142	1	$result += $this->connection->executeUpdate($this->getSqlForValues($values), $parameters, $types);
143
144	1	$offset += $insertMaxRows;
145		}
146
147	1	return $result;
148		}
149
150		/**
151		* Returns the parameters for this INSERT query being constructed indexed by parameter index.
152		*
153		* @return array
154		*/
155	8	public function getParameters(): array
156		{
157	8	return $this->parameters;
158		}
159
160		/**
161		* Returns the parameter types for this INSERT query being constructed indexed by parameter index.
162		*
163		* @return array
164		*/
165	8	public function getParameterTypes(): array
166		{
167	8	return $this->types;
168		}
169
170		/**
171		* Returns the SQL formed by the current specifications of this INSERT query.
172		*
173		* @return string
174		*
175		* @throws \LogicException if no values have been specified yet.
176		*/
177	8	public function getSQL(): string
178		{
179	8	if (empty($this->values)) {
180	1	throw new \LogicException('You need to add at least one set of values before generating the SQL.');
181		}
182
183	7	return $this->getSqlForValues($this->values);
184		}
185
186		/**
187		* This method handles the situation when user did not specified any columns at all
188		*
189		* @param array $values Values to be inserted
190		* @param array $types Corresponding value types
191		*
192		* @return void
193		*/
194	4	private function addValuesInDbOrder(array $values, array $types): void
195		{
196	4	$valueSet = [];
197	4	foreach ($values as $index => $value) {
198	2	$this->parameters[] = $value; // todo: allow expressions.
199	2	$this->types[] = $types[$index] ?? null;
200	2	$valueSet[] = '?'; // todo: allow expressions.
201		}
202
203	4	$this->values[] = $valueSet;
204	4	}
205
206		/**
207		* Handles adding values when user has specified column names
208		*
209		* @param array $values Values to be inserted
210		* @param array $types Corresponding value types
211		*
212		* @return void
213		*/
214	8	private function addValuesByColumnDefinition(array $values, array $types): void
215		{
216	8	$valueSet = array();
217	8	foreach ($this->columns as $index => $column) {
218	8	$namedValue = isset($values[$column]) \|\| array_key_exists($column, $values);
219	8	$positionalValue = isset($values[$index]) \|\| array_key_exists($index, $values);
220
221	8	if (!$namedValue && !$positionalValue) {
222	1	throw new \InvalidArgumentException(
223	1	sprintf('No value specified for column %s (index %d).', $column, $index)
224		);
225		}
226
227	7	if ($namedValue && $positionalValue && $values[$column] !== $values[$index]) {
228	1	throw new \InvalidArgumentException(
229	1	sprintf('Multiple values specified for column %s (index %d).', $column, $index)
230		);
231		}
232
233	7	$this->parameters[] = $namedValue ? $values[$column] : $values[$index]; // todo: allow expressions.
234	7	$valueSet[] = '?'; // todo: allow expressions.
235
236	7	if (isset($types[$column], $types[$index]) && $types[$column] !== $types[$index]) {
237	1	throw new \InvalidArgumentException(
238	1	sprintf('Multiple types specified for column %s (index %d).', $column, $index)
239		);
240		}
241
242	7	$this->types[] = $types[$column] ?? $types[$index] ?? null;
243		}
244
245	5	$this->values[] = $valueSet;
246	5	}
247
248		/**
249		* Return query for given list of values
250		*
251		* @param array $values Values to be inserted
252		*
253		* @return string
254		*/
255	8	private function getSqlForValues(array $values): string
256		{
257	8	$platform = $this->connection->getDatabasePlatform();
258	8	$columnList = '';
259
260	8	if (!empty($this->columns)) {
261	5	$columnList = sprintf(
262	5	' (%s)',
263	5	implode(
264	5	', ',
265	5	array_map(
266		function ($column) use ($platform) {
267	5	$column = new Identifier($column);
268
269	5	return $column->getQuotedName($platform);
270	5	},
271	5	$this->columns
272		)
273		)
274		);
275		}
276
277	8	return sprintf(
278	8	'INSERT INTO %s%s VALUES (%s)',
279	8	$this->table->getQuotedName($platform),
280	8	$columnList,
281	8	implode(
282	8	'), (',
283	8	array_map(
284	8	function (array $valueSet) {
285	8	return implode(', ', $valueSet);
286	8	},
287	8	$values
288		)
289		)
290		);
291		}
292		}
293

doctrine / dbal

Pull Request — master (#2762)

BulkInsertQuery::execute() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like