Dbal::bulkInsert() - Code Metrics - Inspection of "WIP: Feature bulk insert" - tflori/orm - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#49)

by Thomas

created 2018-09-12 05:38 UTC

Dbal::bulkInsert() A

↳ Parent: Dbal

Complexity

Conditions	5
Paths	4

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	30

Importance

Changes

Metric	Value
dl	0
loc	18
ccs	0
cts	11
cp	0
rs	9.3554
c	0
b	0
f	0
cc	5
nc	4
nop	3
crap	30

<?php

namespace ORM\Dbal;

use ORM\Entity;
use ORM\EntityManager;
use ORM\Exception\InvalidArgument;
use ORM\Exception\NotScalar;
use ORM\Exception\UnsupportedDriver;

/**
 * Base class for database abstraction
 *
 * @package ORM
 * @author  Thomas Flori <[email protected]>
 */
abstract class Dbal
{
    /** @var array */
    protected static $typeMapping = [];

    /** @var EntityManager */
    protected $entityManager;
    /** @var string */
    protected $quotingCharacter = '"';
    /** @var string */
    protected $identifierDivider = '.';
    /** @var string */
    protected $booleanTrue = '1';
    /** @var string */
    protected $booleanFalse = '0';

    /**
     * Dbal constructor.
     *
     * @param EntityManager $entityManager
     * @param array         $options
     */
    public function __construct(EntityManager $entityManager, array $options = [])
    {
        $this->entityManager = $entityManager;

        foreach ($options as $option => $value) {
            $this->setOption($option, $value);
        }
    }

    /**
     * Set $option to $value
     *
     * @param string $option
     * @param mixed  $value
     * @return self
     */
    public function setOption($option, $value)
    {
        switch ($option) {
            case EntityManager::OPT_IDENTIFIER_DIVIDER:
                $this->identifierDivider = $value;
                break;

            case EntityManager::OPT_QUOTING_CHARACTER:
                $this->quotingCharacter = $value;
                break;

            case EntityManager::OPT_BOOLEAN_TRUE:
                $this->booleanTrue = $value;
                break;

            case EntityManager::OPT_BOOLEAN_FALSE:
                $this->booleanFalse = $value;
                break;
        }
        return $this;
    }

    /**
     * Returns $identifier quoted for use in a sql statement
     *
     * @param string $identifier Identifier to quote
     * @return string
     */
    public function escapeIdentifier($identifier)
    {
        $quote = $this->quotingCharacter;
        $divider = $this->identifierDivider;
        return $quote . str_replace($divider, $quote . $divider . $quote, $identifier) . $quote;
    }

    /**
     * Returns $value formatted to use in a sql statement.
     *
     * @param  mixed $value The variable that should be returned in SQL syntax
     * @return string
     * @throws NotScalar
     */
    public function escapeValue($value)
    {
        $type   = is_object($value) ? get_class($value) : gettype($value);
        $method = [ $this, 'escape' . ucfirst($type) ];

        if (is_callable($method)) {
            return call_user_func($method, $value);
        } else {
            throw new NotScalar('$value has to be scalar data type. ' . gettype($value) . ' given');
        }
    }

    /**
     * Describe a table
     *
     * @param string $table
     * @return Table|Column[]
     * @throws UnsupportedDriver
     */
    public function describe($table)
    {
        throw new UnsupportedDriver('Not supported for this driver');
    }

    /**
     * Inserts $entity in database and synchronizes the entity
     *
     * Returns whether the insert was successful or not.
     *
     * @param Entity $entity
     * @param bool   $useAutoIncrement
     * @return bool
     * @throws UnsupportedDriver
     */
    public function insert(Entity $entity, $useAutoIncrement = true)
    {
        $statement = $this->buildInsertStatement($entity);

        if ($useAutoIncrement && $entity::isAutoIncremented()) {
            throw new UnsupportedDriver('Auto incremented column for this driver is not supported');
        }

        $this->entityManager->getConnection()->query($statement);
        return $this->entityManager->sync($entity, true);
    }

    /**
     * Inserts $entities in one query
     *
     * If update is false the entities will not be synchronized after insert.
     *
     * @param Entity[] $entities
     * @param bool $update
     * @param bool $useAutoIncrement
     * @return bool
     * @throws UnsupportedDriver
     * @throws InvalidArgument
     * @throws \ORM\Exception\NoConnection
     */
    public function bulkInsert(array $entities, $update = true, $useAutoIncrement = true)
    {
        if (count($entities) === 0) {
            throw new InvalidArgument('$entities should not be empty');
        }
        $statement = $this->buildInsertStatement(...$entities);

        $entity = reset($entities);
        if ($useAutoIncrement && $entity::isAutoIncremented()) {
            throw new UnsupportedDriver('Auto incremented column for this driver is not supported');
        }

        $pdo = $this->entityManager->getConnection()->query($statement);
$myVar = 'Value';
$higher = false;

if (rand(1, 6) > 3) {
    $higher = true;
} else {
    $higher = false;
}
        if ($update) {
            $this->syncInserted(...$entities);
        }
        return true;
    }

    /**
     * Update $entity in database and returns success
     *
     * @param Entity $entity
     * @return bool
     * @internal
     */
    public function update(Entity $entity)
    {
        $data       = $entity->getData();
        $primaryKey = $entity->getPrimaryKey();

        $where = [];
        foreach ($primaryKey as $attribute => $value) {
            $col     = $entity::getColumnName($attribute);
            $where[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
            if (isset($data[$col])) {
                unset($data[$col]);
            }
        }

        $set = [];
        foreach ($data as $col => $value) {
            $set[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
        }

        $statement = 'UPDATE ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
                     'SET ' . implode(',', $set) . ' ' .
                     'WHERE ' . implode(' AND ', $where);
        $this->entityManager->getConnection()->query($statement);

        return $this->entityManager->sync($entity, true);
    }

    /**
     * Delete $entity from database
     *
     * This method does not delete from the map - you can still receive the entity via fetch.
     *
     * @param Entity $entity
     * @return bool
     */
    public function delete(Entity $entity)
    {
        $primaryKey = $entity->getPrimaryKey();
        $where      = [];
        foreach ($primaryKey as $attribute => $value) {
            $col     = $entity::getColumnName($attribute);
            $where[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
        }

        $statement = 'DELETE FROM ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
                     'WHERE ' . implode(' AND ', $where);
        $this->entityManager->getConnection()->query($statement);

        return true;
    }

    /**
     * Build the insert statement for $entity
     *
     * @param Entity $entity
     * @param Entity[] $entities
     * @return string
     */
    protected function buildInsertStatement(Entity $entity, Entity ...$entities)
    {
        array_unshift($entities, $entity);
        $cols = [];
        $rows = [];
        foreach ($entities as $entity) {
            $data = $entity->getData();
            $cols = array_unique(array_merge($cols, array_keys($data)));
            $rows[] = $data;
        }

        $cols = array_combine($cols, array_map([$this, 'escapeIdentifier'], $cols));

        $statement = 'INSERT INTO ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
                     '(' . implode(',', $cols) . ') VALUES ';

        $statement .= implode(',', array_map(function ($values) use ($cols) {
            $result = [];
            foreach ($cols as $key => $col) {
                $result[] = isset($values[$key]) ? $this->escapeValue($values[$key]) : $this->escapeNULL();
            }
            return '(' . implode(',', $result) . ')';
        }, $rows));

        return $statement;
    }

    /**
     * Update the autoincrement value
     *
     * @param Entity     $entity
     * @param int|string $value
     */
    protected function updateAutoincrement(Entity $entity, $value)
    {
        $var    = $entity::getPrimaryKeyVars()[0];
        $column = $entity::getColumnName($var);

        $entity->setOriginalData(array_merge($entity->getData(), [ $column => $value ]));
        $entity->__set($var, $value);
    }

    protected function syncInserted(Entity ...$entities)
    {
        if (count($entities) === 0) {
            throw new Exception\InvalidArgument('$entities should not be empty');
        }

        $entity = reset($entities);
        $vars = $entity::getPrimaryKeyVars();
        $cols = array_map([$entity, 'getColumnName'], $vars);
        $primary = array_combine($vars, $cols);

        $query = "SELECT * FROM " . $this->escapeIdentifier($entity::getTableName()) . " WHERE ";
        $query .= count($cols) > 1 ? '(' . implode(',', array_map([$this, 'escapeIdentifier'], $cols)) . ')' : $cols[0];
        $query .= ' IN (';
        $pKeys = [];
        foreach ($entities as $entity) {
            $pKey = array_map([$this, 'escapeValue'], $entity->getPrimaryKey());
            $pKeys[] = count($cols) > 1 ? '(' . implode(',', $pKey) . ')' : reset($pKey);
        }
        $query .= implode(',', $pKeys) . ')';

        $statement = $this->entityManager->getConnection()->query($query);
        $left = $entities;
        while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) {
            foreach ($left as $k => $entity) {
                foreach ($primary as $var => $col) {
                    if ($entity->$var != $row[$col]) {
                        continue 2;
                    }
                }

                $this->entityManager->map($entity, true);
                $entity->setOriginalData($row);
                $entity->reset();
                unset($left[$k]);
                break;
            }
        }
    }

    /**
     * Normalize $type
     *
     * The type returned by mysql is for example VARCHAR(20) - this function converts it to varchar
     *
     * @param string $type
     * @return string
     */
    protected function normalizeType($type)
    {
        $type = strtolower($type);

        if (($pos = strpos($type, '(')) !== false && $pos > 0) {
            $type = substr($type, 0, $pos);
        }

        return trim($type);
    }

    /**
     * Extract content from parenthesis in $type
     *
     * @param string $type
     * @return string
     */
    protected function extractParenthesis($type)
    {
        if (preg_match('/\((.+)\)/', $type, $match)) {
            return $match[1];
        }

        return null;
    }

    /**
     * Escape a string for query
     *
     * @param string $value
     * @return string
     */
    protected function escapeString($value)
    {
        return $this->entityManager->getConnection()->quote($value);
    }

    /**
     * Escape an integer for query
     *
     * @param int $value
     * @return string
     */
    protected function escapeInteger($value)
    {
        return (string) $value;
    }

    /**
     * Escape a double for Query
     *
     * @param double $value
     * @return string
     */
    protected function escapeDouble($value)
    {
        return (string) $value;
    }

    /**
     * Escape NULL for query
     *
     * @return string
     */
    protected function escapeNULL()
    {
        return 'NULL';
    }

    /**
     * Escape a boolean for query
     *
     * @param bool $value
     * @return string
     */
    protected function escapeBoolean($value)
    {
        return ($value) ? $this->booleanTrue : $this->booleanFalse;
    }

    /**
     * Escape a date time object for query
     *
     * @param \DateTime $value
     * @return mixed
     */
    protected function escapeDateTime(\DateTime $value)
    {
        $value->setTimezone(new \DateTimeZone('UTC'));
        return $this->escapeString($value->format('Y-m-d\TH:i:s.u\Z'));
    }
}


1		<?php
2
3		namespace ORM\Dbal;
4
5		use ORM\Entity;
6		use ORM\EntityManager;
7		use ORM\Exception\InvalidArgument;
8		use ORM\Exception\NotScalar;
9		use ORM\Exception\UnsupportedDriver;
10
11		/**
12		* Base class for database abstraction
13		*
14		* @package ORM
15		* @author Thomas Flori <[email protected]>
16		*/
17		abstract class Dbal
18		{
19		/** @var array */
20		protected static $typeMapping = [];
21
22		/** @var EntityManager */
23		protected $entityManager;
24		/** @var string */
25		protected $quotingCharacter = '"';
26		/** @var string */
27		protected $identifierDivider = '.';
28		/** @var string */
29		protected $booleanTrue = '1';
30		/** @var string */
31		protected $booleanFalse = '0';
32
33		/**
34		* Dbal constructor.
35		*
36		* @param EntityManager $entityManager
37		* @param array $options
38		*/
39	746	public function __construct(EntityManager $entityManager, array $options = [])
40		{
41	746	$this->entityManager = $entityManager;
42
43	746	foreach ($options as $option => $value) {
44	4	$this->setOption($option, $value);
45		}
46	746	}
47
48		/**
49		* Set $option to $value
50		*
51		* @param string $option
52		* @param mixed $value
53		* @return self
54		*/
55	22	public function setOption($option, $value)
56		{
57		switch ($option) {
58	22	case EntityManager::OPT_IDENTIFIER_DIVIDER:
59	1	$this->identifierDivider = $value;
60	1	break;
61
62	21	case EntityManager::OPT_QUOTING_CHARACTER:
63	1	$this->quotingCharacter = $value;
64	1	break;
65
66	20	case EntityManager::OPT_BOOLEAN_TRUE:
67	19	$this->booleanTrue = $value;
68	19	break;
69
70	19	case EntityManager::OPT_BOOLEAN_FALSE:
71	19	$this->booleanFalse = $value;
72	19	break;
73		}
74	22	return $this;
75		}
76
77		/**
78		* Returns $identifier quoted for use in a sql statement
79		*
80		* @param string $identifier Identifier to quote
81		* @return string
82		*/
83	191	public function escapeIdentifier($identifier)
84		{
85	191	$quote = $this->quotingCharacter;
86	191	$divider = $this->identifierDivider;
87	191	return $quote . str_replace($divider, $quote . $divider . $quote, $identifier) . $quote;
88		}
89
90		/**
91		* Returns $value formatted to use in a sql statement.
92		*
93		* @param mixed $value The variable that should be returned in SQL syntax
94		* @return string
95		* @throws NotScalar
96		*/
97	184	public function escapeValue($value)
98		{
99	184	$type = is_object($value) ? get_class($value) : gettype($value);
100	184	$method = [ $this, 'escape' . ucfirst($type) ];
101
102	184	if (is_callable($method)) {
103	183	return call_user_func($method, $value);
104		} else {
105	1	throw new NotScalar('$value has to be scalar data type. ' . gettype($value) . ' given');
106		}
107		}
108
109		/**
110		* Describe a table
111		*
112		* @param string $table
113		* @return Table\|Column[]
114		* @throws UnsupportedDriver
115		*/
116	1	public function describe($table)
117		{
118	1	throw new UnsupportedDriver('Not supported for this driver');
119		}
120
121		/**
122		* Inserts $entity in database and synchronizes the entity
123		*
124		* Returns whether the insert was successful or not.
125		*
126		* @param Entity $entity
127		* @param bool $useAutoIncrement
128		* @return bool
129		* @throws UnsupportedDriver
130		*/
131	2	public function insert(Entity $entity, $useAutoIncrement = true)
132		{
133	2	$statement = $this->buildInsertStatement($entity);
134
135	2	if ($useAutoIncrement && $entity::isAutoIncremented()) {
136	1	throw new UnsupportedDriver('Auto incremented column for this driver is not supported');
137		}
138
139	1	$this->entityManager->getConnection()->query($statement);
140	1	return $this->entityManager->sync($entity, true);
141		}
142
143		/**
144		* Inserts $entities in one query
145		*
146		* If update is false the entities will not be synchronized after insert.
147		*
148		* @param Entity[] $entities
149		* @param bool $update
150		* @param bool $useAutoIncrement
151		* @return bool
152		* @throws UnsupportedDriver
153		* @throws InvalidArgument
154		* @throws \ORM\Exception\NoConnection
155		*/
156		public function bulkInsert(array $entities, $update = true, $useAutoIncrement = true)
157		{
158		if (count($entities) === 0) {
159		throw new InvalidArgument('$entities should not be empty');
160		}
161		$statement = $this->buildInsertStatement(...$entities);
162
163		$entity = reset($entities);
164		if ($useAutoIncrement && $entity::isAutoIncremented()) {
165		throw new UnsupportedDriver('Auto incremented column for this driver is not supported');
166		}
167
168		$pdo = $this->entityManager->getConnection()->query($statement);
		0 ignored issues – show Unused Code introduced 2018-09-12 05:59 UTC by Report Bug Copy Issue Report `$pdo` is not used, you could remove the assignment. This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently. $myVar = 'Value'; $higher = false; if (rand(1, 6) > 3) { $higher = true; } else { $higher = false; } Both the `$myVar` assignment in line 1 and the `$higher` assignment in line 2 are dead. The first because `$myVar` is never used and the second because `$higher` is always overwritten for every possible time line. Loading history...
169		if ($update) {
170		$this->syncInserted(...$entities);
171		}
172		return true;
173		}
174
175		/**
176		* Update $entity in database and returns success
177		*
178		* @param Entity $entity
179		* @return bool
180		* @internal
181		*/
182	6	public function update(Entity $entity)
183		{
184	6	$data = $entity->getData();
185	6	$primaryKey = $entity->getPrimaryKey();
186
187	6	$where = [];
188	6	foreach ($primaryKey as $attribute => $value) {
189	6	$col = $entity::getColumnName($attribute);
190	6	$where[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
191	6	if (isset($data[$col])) {
192	6	unset($data[$col]);
193		}
194		}
195
196	6	$set = [];
197	6	foreach ($data as $col => $value) {
198	6	$set[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
199		}
200
201	6	$statement = 'UPDATE ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
202	6	'SET ' . implode(',', $set) . ' ' .
203	6	'WHERE ' . implode(' AND ', $where);
204	6	$this->entityManager->getConnection()->query($statement);
205
206	3	return $this->entityManager->sync($entity, true);
207		}
208
209		/**
210		* Delete $entity from database
211		*
212		* This method does not delete from the map - you can still receive the entity via fetch.
213		*
214		* @param Entity $entity
215		* @return bool
216		*/
217	6	public function delete(Entity $entity)
218		{
219	6	$primaryKey = $entity->getPrimaryKey();
220	6	$where = [];
221	6	foreach ($primaryKey as $attribute => $value) {
222	6	$col = $entity::getColumnName($attribute);
223	6	$where[] = $this->escapeIdentifier($col) . ' = ' . $this->escapeValue($value);
224		}
225
226	6	$statement = 'DELETE FROM ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
227	6	'WHERE ' . implode(' AND ', $where);
228	6	$this->entityManager->getConnection()->query($statement);
229
230	4	return true;
231		}
232
233		/**
234		* Build the insert statement for $entity
235		*
236		* @param Entity $entity
237		* @param Entity[] $entities
238		* @return string
239		*/
240	12	protected function buildInsertStatement(Entity $entity, Entity ...$entities)
241		{
242	12	array_unshift($entities, $entity);
243	12	$cols = [];
244	12	$rows = [];
245	12	foreach ($entities as $entity) {
246	12	$data = $entity->getData();
247	12	$cols = array_unique(array_merge($cols, array_keys($data)));
248	12	$rows[] = $data;
249		}
250
251	12	$cols = array_combine($cols, array_map([$this, 'escapeIdentifier'], $cols));
252
253	12	$statement = 'INSERT INTO ' . $this->escapeIdentifier($entity::getTableName()) . ' ' .
254	12	'(' . implode(',', $cols) . ') VALUES ';
255
256	12	$statement .= implode(',', array_map(function ($values) use ($cols) {
257	12	$result = [];
258	12	foreach ($cols as $key => $col) {
259	11	$result[] = isset($values[$key]) ? $this->escapeValue($values[$key]) : $this->escapeNULL();
260		}
261	12	return '(' . implode(',', $result) . ')';
262	12	}, $rows));
263
264	12	return $statement;
265		}
266
267		/**
268		* Update the autoincrement value
269		*
270		* @param Entity $entity
271		* @param int\|string $value
272		*/
273	3	protected function updateAutoincrement(Entity $entity, $value)
274		{
275	3	$var = $entity::getPrimaryKeyVars()[0];
276	3	$column = $entity::getColumnName($var);
277
278	3	$entity->setOriginalData(array_merge($entity->getData(), [ $column => $value ]));
279	3	$entity->__set($var, $value);
280	3	}
281
282		protected function syncInserted(Entity ...$entities)
283		{
284		if (count($entities) === 0) {
285		throw new Exception\InvalidArgument('$entities should not be empty');
286		}
287
288		$entity = reset($entities);
289		$vars = $entity::getPrimaryKeyVars();
290		$cols = array_map([$entity, 'getColumnName'], $vars);
291		$primary = array_combine($vars, $cols);
292
293		$query = "SELECT * FROM " . $this->escapeIdentifier($entity::getTableName()) . " WHERE ";
294		$query .= count($cols) > 1 ? '(' . implode(',', array_map([$this, 'escapeIdentifier'], $cols)) . ')' : $cols[0];
295		$query .= ' IN (';
296		$pKeys = [];
297		foreach ($entities as $entity) {
298		$pKey = array_map([$this, 'escapeValue'], $entity->getPrimaryKey());
299		$pKeys[] = count($cols) > 1 ? '(' . implode(',', $pKey) . ')' : reset($pKey);
300		}
301		$query .= implode(',', $pKeys) . ')';
302
303		$statement = $this->entityManager->getConnection()->query($query);
304		$left = $entities;
305		while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) {
306		foreach ($left as $k => $entity) {
307		foreach ($primary as $var => $col) {
308		if ($entity->$var != $row[$col]) {
309		continue 2;
310		}
311		}
312
313		$this->entityManager->map($entity, true);
314		$entity->setOriginalData($row);
315		$entity->reset();
316		unset($left[$k]);
317		break;
318		}
319		}
320		}
321
322		/**
323		* Normalize $type
324		*
325		* The type returned by mysql is for example VARCHAR(20) - this function converts it to varchar
326		*
327		* @param string $type
328		* @return string
329		*/
330	79	protected function normalizeType($type)
331		{
332	79	$type = strtolower($type);
333
334	79	if (($pos = strpos($type, '(')) !== false && $pos > 0) {
335	33	$type = substr($type, 0, $pos);
336		}
337
338	79	return trim($type);
339		}
340
341		/**
342		* Extract content from parenthesis in $type
343		*
344		* @param string $type
345		* @return string
346		*/
347	24	protected function extractParenthesis($type)
348		{
349	24	if (preg_match('/\((.+)\)/', $type, $match)) {
350	16	return $match[1];
351		}
352
353	8	return null;
354		}
355
356		/**
357		* Escape a string for query
358		*
359		* @param string $value
360		* @return string
361		*/
362	121	protected function escapeString($value)
363		{
364	121	return $this->entityManager->getConnection()->quote($value);
365		}
366
367		/**
368		* Escape an integer for query
369		*
370		* @param int $value
371		* @return string
372		*/
373	51	protected function escapeInteger($value)
374		{
375	51	return (string) $value;
376		}
377
378		/**
379		* Escape a double for Query
380		*
381		* @param double $value
382		* @return string
383		*/
384	4	protected function escapeDouble($value)
385		{
386	4	return (string) $value;
387		}
388
389		/**
390		* Escape NULL for query
391		*
392		* @return string
393		*/
394	1	protected function escapeNULL()
395		{
396	1	return 'NULL';
397		}
398
399		/**
400		* Escape a boolean for query
401		*
402		* @param bool $value
403		* @return string
404		*/
405	20	protected function escapeBoolean($value)
406		{
407	20	return ($value) ? $this->booleanTrue : $this->booleanFalse;
408		}
409
410		/**
411		* Escape a date time object for query
412		*
413		* @param \DateTime $value
414		* @return mixed
415		*/
416	1	protected function escapeDateTime(\DateTime $value)
417		{
418	1	$value->setTimezone(new \DateTimeZone('UTC'));
419	1	return $this->escapeString($value->format('Y-m-d\TH:i:s.u\Z'));
420		}
421		}
422

tflori / orm

Pull Request — master (#49)

Dbal::bulkInsert() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like