Sqlite::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 10:29 UTC

Sqlite::bulkInsert() B

↳ Parent: Sqlite

Complexity

Conditions	6
Paths	5

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	42

Importance

Changes

Metric	Value
dl	0
loc	38
ccs	0
cts	0
cp	0
rs	8.6897
c	0
b	0
f	0
cc	6
nc	5
nop	3
crap	42

<?php

namespace ORM\Dbal;

use ORM\Entity;
use ORM\Exception;

/**
 * Database abstraction for SQLite databases
 *
 * @package ORM\Dbal
 * @author  Thomas Flori <[email protected]>
 */
class Sqlite extends Dbal
{
    protected static $typeMapping = [
        'integer' => Type\Number::class,
        'int'     => Type\Number::class,
        'double'  => Type\Number::class,
        'real'    => Type\Number::class,
        'float'   => Type\Number::class,
        'numeric' => Type\Number::class,
        'decimal' => Type\Number::class,

        'varchar'   => Type\VarChar::class,
        'character' => Type\VarChar::class,

        'text' => Type\Text::class,

        'boolean' => Type\Boolean::class,
        'json'    => Type\Json::class,

        'datetime' => Type\DateTime::class,
        'date'     => Type\DateTime::class,
        'time'     => Type\Time::class,
    ];

    public function insert(Entity $entity, $useAutoIncrement = true)
    {
        $statement = $this->buildInsertStatement($entity);
        $pdo       = $this->entityManager->getConnection();

        if ($useAutoIncrement && $entity::isAutoIncremented()) {
            $pdo->query($statement);
            $this->updateAutoincrement($entity, $pdo->lastInsertId());
        } else {
            $pdo->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.
     *
     * **WARNING**: This implementation assumes that the rows with the highest ids are the just inserted rows.
     *
     * @param Entity[] $entities
     * @param bool $update
     * @param bool $useAutoIncrement
     * @return bool
     */
    public function bulkInsert(array $entities, $update = true, $useAutoIncrement = true)
    {
        if (count($entities) === 0) {
            return false;
        }
        $statement = $this->buildInsertStatement(...$entities);
        $pdo = $this->entityManager->getConnection();

        if ($update) {
            $entity = reset($entities);
            if ($useAutoIncrement && $entity::isAutoIncremented()) {
                $table = $this->escapeIdentifier($entity::getTableName());
                $pKey = $this->escapeIdentifier($entity::getColumnName($entity::getPrimaryKeyVars()[0]));
                $pdo->beginTransaction();
                $pdo->query($statement);
                $rows = $pdo->query('SELECT * FROM ' . $table . ' WHERE ' . $pKey . ' <= ' . $pdo->lastInsertId() .
                    ' ORDER BY ' . $pKey . ' DESC LIMIT ' . count($entities))
                    ->fetchAll(\PDO::FETCH_ASSOC);
                $pdo->commit();

                /** @var Entity $entity */
                foreach (array_reverse($entities) as $key => $entity) {
                    $entity->setOriginalData($rows[$key]);
                    $entity->reset();
                    $this->entityManager->map($entity, true);
                }
                return true;
            } else {
                $pdo->query($statement);
            }

            $this->syncInserted(...$entities);
            return true;
        }

        $pdo->query($statement);
        return true;
    }

    public function describe($schemaTable)
    {
        $table = explode($this->identifierDivider, $schemaTable);
        list($schema, $table) = count($table) === 2 ? $table : [ null, $table[ 0 ] ];
        $schema = $schema !== null ? $this->escapeIdentifier($schema) . '.' : '';

        $result     = $this->entityManager->getConnection()->query(
            'PRAGMA ' . $schema . 'table_info(' . $this->escapeIdentifier($table) . ')'
        );
        $rawColumns = $result->fetchAll(\PDO::FETCH_ASSOC);

        if (count($rawColumns) === 0) {
            throw new Exception('Unknown table ' . $table);
        }

        $hasMultiplePrimaryKey = $this->hasMultiplePrimaryKey($rawColumns);

        $cols = array_map(function ($rawColumn) use ($hasMultiplePrimaryKey) {
            $columnDefinition = $this->normalizeColumnDefinition($rawColumn, $hasMultiplePrimaryKey);
            return new Column($this, $columnDefinition);
        }, $rawColumns);

        return new Table($cols);
    }

    /**
     * Checks $rawColumns for a multiple primary key
     *
     * @param array $rawColumns
     * @return bool
     */
    protected function hasMultiplePrimaryKey($rawColumns)
    {
        return count(array_filter(array_map(
            function ($rawColumn) {
                return $rawColumn[ 'pk' ];
            },
            $rawColumns
        ))) > 1;
    }

    /**
     * Normalize a column definition
     *
     * The column definition from "PRAGMA table_info(<table>)" is to special as useful. Here we normalize it to a more
     * ANSI-SQL style.
     *
     * @param array $rawColumn
     * @param bool  $hasMultiplePrimaryKey
     * @return array
     */
    protected function normalizeColumnDefinition($rawColumn, $hasMultiplePrimaryKey = false)
    {
        $definition = [];

        $definition['data_type'] = $this->normalizeType($rawColumn['type']);
        if (isset(static::$typeMapping[$definition['data_type']])) {
            $definition['type'] = static::$typeMapping[$definition['data_type']];
        }

        $definition['column_name']              = $rawColumn['name'];
        $definition['is_nullable']              = $rawColumn['notnull'] === '0';
        $definition['column_default']           = $rawColumn['dflt_value'];
        $definition['character_maximum_length'] = null;
        $definition['datetime_precision']       = null;

        switch ($definition['data_type']) {
            case 'varchar':
            case 'char':
                $definition['character_maximum_length'] = $this->extractParenthesis($rawColumn['type']);
                break;
            case 'datetime':
            case 'timestamp':
            case 'time':
                $definition['datetime_precision'] = $this->extractParenthesis($rawColumn['type']);
                break;
            case 'integer':
                if (!$definition['column_default'] && $rawColumn['pk'] === '1' && !$hasMultiplePrimaryKey) {
                    $definition['column_default'] = 'sequence(rowid)';
                }
                break;
        }

        return $definition;
    }
}


1		<?php
2
3		namespace ORM\Dbal;
4
5		use ORM\Entity;
6		use ORM\Exception;
7
8		/**
9		* Database abstraction for SQLite databases
10		*
11		* @package ORM\Dbal
12		* @author Thomas Flori <[email protected]>
13		*/
14		class Sqlite extends Dbal
15		{
16		protected static $typeMapping = [
17		'integer' => Type\Number::class,
18		'int' => Type\Number::class,
19		'double' => Type\Number::class,
20		'real' => Type\Number::class,
21		'float' => Type\Number::class,
22		'numeric' => Type\Number::class,
23		'decimal' => Type\Number::class,
24
25		'varchar' => Type\VarChar::class,
26		'character' => Type\VarChar::class,
27
28		'text' => Type\Text::class,
29
30		'boolean' => Type\Boolean::class,
31		'json' => Type\Json::class,
32
33		'datetime' => Type\DateTime::class,
34		'date' => Type\DateTime::class,
35		'time' => Type\Time::class,
36		];
37
38	2	public function insert(Entity $entity, $useAutoIncrement = true)
39		{
40	2	$statement = $this->buildInsertStatement($entity);
41	2	$pdo = $this->entityManager->getConnection();
42
43	2	if ($useAutoIncrement && $entity::isAutoIncremented()) {
44	1	$pdo->query($statement);
45	1	$this->updateAutoincrement($entity, $pdo->lastInsertId());
46		} else {
47	1	$pdo->query($statement);
48		}
49
50	2	return $this->entityManager->sync($entity, true);
51		}
52
53		/**
54		* Inserts $entities in one query
55		*
56		* If update is false the entities will not be synchronized after insert.
57		*
58		* WARNING: This implementation assumes that the rows with the highest ids are the just inserted rows.
59		*
60		* @param Entity[] $entities
61		* @param bool $update
62		* @param bool $useAutoIncrement
63		* @return bool
64		*/
65		public function bulkInsert(array $entities, $update = true, $useAutoIncrement = true)
66		{
67		if (count($entities) === 0) {
68		return false;
69		}
70		$statement = $this->buildInsertStatement(...$entities);
71		$pdo = $this->entityManager->getConnection();
72
73		if ($update) {
74		$entity = reset($entities);
75		if ($useAutoIncrement && $entity::isAutoIncremented()) {
76		$table = $this->escapeIdentifier($entity::getTableName());
77		$pKey = $this->escapeIdentifier($entity::getColumnName($entity::getPrimaryKeyVars()[0]));
78		$pdo->beginTransaction();
79		$pdo->query($statement);
80		$rows = $pdo->query('SELECT * FROM ' . $table . ' WHERE ' . $pKey . ' <= ' . $pdo->lastInsertId() .
81		' ORDER BY ' . $pKey . ' DESC LIMIT ' . count($entities))
82		->fetchAll(\PDO::FETCH_ASSOC);
83		$pdo->commit();
84
85		/** @var Entity $entity */
86		foreach (array_reverse($entities) as $key => $entity) {
87		$entity->setOriginalData($rows[$key]);
88		$entity->reset();
89		$this->entityManager->map($entity, true);
90		}
91		return true;
92		} else {
93		$pdo->query($statement);
94		}
95
96		$this->syncInserted(...$entities);
97		return true;
98		}
99
100		$pdo->query($statement);
101		return true;
102		}
103
104	32	public function describe($schemaTable)
105		{
106	32	$table = explode($this->identifierDivider, $schemaTable);
107	32	list($schema, $table) = count($table) === 2 ? $table : [ null, $table[ 0 ] ];
108	32	$schema = $schema !== null ? $this->escapeIdentifier($schema) . '.' : '';
109
110	32	$result = $this->entityManager->getConnection()->query(
111	32	'PRAGMA ' . $schema . 'table_info(' . $this->escapeIdentifier($table) . ')'
112		);
113	32	$rawColumns = $result->fetchAll(\PDO::FETCH_ASSOC);
114
115	32	if (count($rawColumns) === 0) {
116	2	throw new Exception('Unknown table ' . $table);
117		}
118
119	30	$hasMultiplePrimaryKey = $this->hasMultiplePrimaryKey($rawColumns);
120
121	30	$cols = array_map(function ($rawColumn) use ($hasMultiplePrimaryKey) {
122	30	$columnDefinition = $this->normalizeColumnDefinition($rawColumn, $hasMultiplePrimaryKey);
123	30	return new Column($this, $columnDefinition);
124	30	}, $rawColumns);
125
126	30	return new Table($cols);
127		}
128
129		/**
130		* Checks $rawColumns for a multiple primary key
131		*
132		* @param array $rawColumns
133		* @return bool
134		*/
135	30	protected function hasMultiplePrimaryKey($rawColumns)
136		{
137	30	return count(array_filter(array_map(
138	30	function ($rawColumn) {
139	30	return $rawColumn[ 'pk' ];
140	30	},
141		$rawColumns
142	30	))) > 1;
143		}
144
145		/**
146		* Normalize a column definition
147		*
148		* The column definition from "PRAGMA table_info(<table>)" is to special as useful. Here we normalize it to a more
149		* ANSI-SQL style.
150		*
151		* @param array $rawColumn
152		* @param bool $hasMultiplePrimaryKey
153		* @return array
154		*/
155	30	protected function normalizeColumnDefinition($rawColumn, $hasMultiplePrimaryKey = false)
156		{
157	30	$definition = [];
158
159	30	$definition['data_type'] = $this->normalizeType($rawColumn['type']);
160	30	if (isset(static::$typeMapping[$definition['data_type']])) {
161	29	$definition['type'] = static::$typeMapping[$definition['data_type']];
162		}
163
164	30	$definition['column_name'] = $rawColumn['name'];
165	30	$definition['is_nullable'] = $rawColumn['notnull'] === '0';
166	30	$definition['column_default'] = $rawColumn['dflt_value'];
167	30	$definition['character_maximum_length'] = null;
168	30	$definition['datetime_precision'] = null;
169
170	30	switch ($definition['data_type']) {
171	30	case 'varchar':
172	28	case 'char':
173	2	$definition['character_maximum_length'] = $this->extractParenthesis($rawColumn['type']);
174	2	break;
175	28	case 'datetime':
176	27	case 'timestamp':
177	27	case 'time':
178	2	$definition['datetime_precision'] = $this->extractParenthesis($rawColumn['type']);
179	2	break;
180	26	case 'integer':
181	9	if (!$definition['column_default'] && $rawColumn['pk'] === '1' && !$hasMultiplePrimaryKey) {
182	1	$definition['column_default'] = 'sequence(rowid)';
183		}
184	9	break;
185		}
186
187	30	return $definition;
188		}
189		}
190

tflori / orm

Pull Request — master (#49)

Sqlite::bulkInsert() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like