Completed
Push — master ( 275c1f...cb75c2 )
by Emmanuel
05:43
created

DB::prepareInsert()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 13
Code Lines 8

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 8
CRAP Score 2

Importance

Changes 0
Metric Value
dl 0
loc 13
ccs 8
cts 8
cp 1
rs 9.4285
c 0
b 0
f 0
cc 2
eloc 8
nc 2
nop 0
crap 2
1
<?php
2
/**
3
 * neuralyzer : Data Anonymization Library and CLI Tool
4
 *
5
 * PHP Version 7.1
6
 *
7
 * @author Emmanuel Dyan
8
 * @author Rémi Sauvat
9
 * @copyright 2018 Emmanuel Dyan
10
 *
11
 * @package edyan/neuralyzer
12
 *
13
 * @license GNU General Public License v2.0
14
 *
15
 * @link https://github.com/edyan/neuralyzer
16
 */
17
18
namespace Edyan\Neuralyzer\Anonymizer;
19
20
use Doctrine\DBAL\Connection;
21
use Doctrine\DBAL\Configuration as DbalConfiguration;
22
use Doctrine\DBAL\DriverManager as DbalDriverManager;
23
use Doctrine\DBAL\Query\QueryBuilder;
24
use Edyan\Neuralyzer\Exception\NeuralizerException;
25
26
/**
27
 * Implement AbstractAnonymizer for DB, to read and write data via Doctrine DBAL
28
 */
29
class DB extends AbstractAnonymizer
30
{
31
    /**
32
     * Doctrine DB Adapter
33
     *
34
     * @var Connection
35
     */
36
    private $conn;
37
38
39
    /**
40
     * Primary Key
41
     *
42
     * @var string
43
     */
44
    private $priKey;
45
46
    /**
47
     * Limit the number of updates or create
48
     *
49
     * @var int
50
     */
51
    private $limit = 0;
52
53
    /**
54
     * Init connection
55
     *
56
     * @param $params   Parameters to send to Doctrine DB
57
     */
58 34
    public function __construct(array $params)
59
    {
60 34
        $this->conn = DbalDriverManager::getConnection($params, new DbalConfiguration());
61 33
    }
62
63
64
    /**
65
     * Get Doctrine Connection
66
     *
67
     * @return Connection
68
     */
69 22
    public function getConn(): Connection
70
    {
71 22
        return $this->conn;
72
    }
73
74
75
    /**
76
     * Set the limit for updates and creates
77
     *
78
     * @param int $limit
79
     */
80 5
    public function setLimit(int $limit): void
81
    {
82 5
        $this->limit = $limit;
83 5
    }
84
85
86
    /**
87
     * Process an entity by reading / writing to the DB
88
     *
89
     * @param string        $entity
90
     * @param callable|null $callback
91
     * @param bool          $pretend
92
     * @param bool          $returnRes
93
     *
94
     * @return void|array
95
     */
96 18
    public function processEntity(
97
        string $entity,
98
        callable $callback = null,
99
        bool $pretend = true,
100
        bool $returnRes = false
101
    ): array {
102 18
        $schema = $this->conn->getSchemaManager();
103 18
        if ($schema->tablesExist($entity) === false) {
0 ignored issues
show
Documentation introduced by
$entity is of type string, but the function expects a array.

It seems like the type of the argument is not accepted by the function/method which you are calling.

In some cases, in particular if PHP’s automatic type-juggling kicks in this might be fine. In other cases, however this might be a bug.

We suggest to add an explicit type cast like in the following example:

function acceptsInteger($int) { }

$x = '123'; // string "123"

// Instead of
acceptsInteger($x);

// we recommend to use
acceptsInteger((integer) $x);
Loading history...
104 1
            throw new NeuralizerException("Table $entity does not exist");
105
        }
106
107 17
        $this->entity = $entity;
108 17
        $this->priKey = $this->getPrimaryKey();
109 16
        $this->entityCols = $this->getTableCols();
110
111 16
        $queries = [];
112
113 16
        $actionsOnThatEntity = $this->whatToDoWithEntity();
114
115 14
        if ($actionsOnThatEntity & self::TRUNCATE_TABLE) {
116 4
            $where = $this->getWhereConditionInConfig();
117 4
            $query = $this->runDelete($where, $pretend);
118 3
            ($returnRes === true ? array_push($queries, $query) : '');
119
        }
120
121 13
        if ($actionsOnThatEntity & self::UPDATE_TABLE) {
122 10
            $queries = array_merge(
123 10
                $queries,
124 10
                $this->updateData($returnRes, $pretend, $callback)
0 ignored issues
show
Bug introduced by
It seems like $callback defined by parameter $callback on line 98 can also be of type null; however, Edyan\Neuralyzer\Anonymizer\DB::updateData() does only seem to accept callable, maybe add an additional type check?

This check looks at variables that have been passed in as parameters and are passed out again to other methods.

If the outgoing method call has stricter type requirements than the method itself, an issue is raised.

An additional type check may prevent trouble.

Loading history...
125
            );
126
        }
127
128 11
        if ($actionsOnThatEntity & self::INSERT_TABLE) {
129 3
            $queries = array_merge(
130 3
                $queries,
131 3
                $this->insertData($returnRes, $pretend, $callback)
0 ignored issues
show
Bug introduced by
It seems like $callback defined by parameter $callback on line 98 can also be of type null; however, Edyan\Neuralyzer\Anonymizer\DB::insertData() does only seem to accept callable, maybe add an additional type check?

This check looks at variables that have been passed in as parameters and are passed out again to other methods.

If the outgoing method call has stricter type requirements than the method itself, an issue is raised.

An additional type check may prevent trouble.

Loading history...
132
            );
133
        }
134
135 11
        return $queries;
136
    }
137
138
139
    /**
140
     * Identify the primary key for a table
141
     *
142
     * @return string Field's name
143
     */
144 17
    private function getPrimaryKey(): string
145
    {
146 17
        $schema = $this->conn->getSchemaManager();
147 17
        $tableDetails = $schema->listTableDetails($this->entity);
148 17
        if ($tableDetails->hasPrimaryKey() === false) {
149 1
            throw new NeuralizerException("Can't find a primary key for '{$this->entity}'");
150
        }
151
152 16
        return $tableDetails->getPrimaryKey()->getColumns()[0];
153
    }
154
155
156
    /**
157
     * Retrieve columns list for a table with type and length
158
     *
159
     * @return array $cols
160
     */
161 16
    private function getTableCols(): array
162
    {
163 16
        $schema = $this->conn->getSchemaManager();
164 16
        $tableCols = $schema->listTableColumns($this->entity);
165 16
        $cols = [];
166 16
        foreach ($tableCols as $col) {
167 16
            $cols[$col->getName()] = [
168 16
                'length' => $col->getLength(),
169 16
                'type'   => $col->getType(),
170 16
                'unsigned' => $col->getUnsigned(),
171
            ];
172
        }
173
174 16
        return $cols;
175
    }
176
177
178
    /**
179
     * Execute the Update with Doctrine QueryBuilder
180
     *
181
     * @param  string $primaryKeyVal  Primary Key's Value
182
     * @return QueryBuilder           Doctrine DBAL QueryBuilder
183
     */
184 9
    private function prepareUpdate($primaryKeyVal): QueryBuilder
185
    {
186 9
        $data = $this->generateFakeData();
187
188 7
        $queryBuilder = $this->conn->createQueryBuilder();
189 7
        $queryBuilder = $queryBuilder->update($this->entity);
190 7
        foreach ($data as $field => $value) {
191 7
            $queryBuilder = $queryBuilder->set($field, $this->getCondition($field));
192 7
            $queryBuilder = $queryBuilder->setParameter(":$field", $value);
193
        }
194 7
        $queryBuilder = $queryBuilder->where("{$this->priKey} = :{$this->priKey}");
195 7
        $queryBuilder = $queryBuilder->setParameter(":{$this->priKey}", $primaryKeyVal);
196
197 7
        return $queryBuilder;
198
    }
199
200
    /**
201
     * Execute the Update with Doctrine QueryBuilder
202
     *
203
     * @return QueryBuilder       Doctrine DBAL QueryBuilder
204
     */
205 3
    private function prepareInsert(): QueryBuilder
206
    {
207 3
        $data = $this->generateFakeData();
208
209 3
        $queryBuilder = $this->conn->createQueryBuilder();
210 3
        $queryBuilder = $queryBuilder->insert($this->entity);
211 3
        foreach ($data as $field => $value) {
212 3
            $queryBuilder = $queryBuilder->setValue($field, ":$field");
213 3
            $queryBuilder = $queryBuilder->setParameter(":$field", $value);
214
        }
215
216 3
        return $queryBuilder;
217
    }
218
219
220
    /**
221
     * To debug, build the final SQL (can be approximative)
222
     * @param  QueryBuilder $queryBuilder
223
     * @return string
224
     */
225 8
    private function getRawSQL(QueryBuilder $queryBuilder)
226
    {
227 8
        $sql = $queryBuilder->getSQL();
228 8
        foreach ($queryBuilder->getParameters() as $parameter => $value) {
229 8
            $sql = str_replace($parameter, "'$value'", $sql);
230
        }
231
232 8
        return $sql;
233
    }
234
235
236
    /**
237
     * Execute the Delete with Doctrine Query Builder
238
     *
239
     * @param string $where
240
     * @param bool   $pretend
241
     *
242
     * @return string
243
     */
244 4
    private function runDelete(string $where, bool $pretend): string
245
    {
246 4
        $queryBuilder = $this->conn->createQueryBuilder();
247 4
        $queryBuilder = $queryBuilder->delete($this->entity);
248 4
        if (!empty($where)) {
249 3
            $queryBuilder = $queryBuilder->where($where);
250
        }
251 4
        $sql = $queryBuilder->getSQL();
252
253 4
        if ($pretend === true) {
254 1
            return $sql;
255
        }
256
257
        try {
258 3
            $queryBuilder->execute();
259 1
        } catch (\Exception $e) {
260 1
            throw new NeuralizerException('Query DELETE Error (' . $e->getMessage() . ')');
261
        }
262
263 2
        return $sql;
264
    }
265
266
267
    /**
268
     * Build the condition by casting the value if needed
269
     *
270
     * @param  string $field
271
     * @return string
272
     */
273 7
    private function getCondition(string $field): string
274
    {
275 7
        $type = strtolower($this->entityCols[$field]['type']);
276
277 7
        $integerCast = $this->getIntegerCast($field);
278
279 7
        $condition = "(CASE $field WHEN NULL THEN NULL ELSE :$field END)";
280
281
        $typeToCast = [
282 7
            'date'     => 'DATE',
283 7
            'datetime' => 'DATE',
284 7
            'time'     => 'TIME',
285 7
            'smallint' => $integerCast,
286 7
            'integer'  => $integerCast,
287 7
            'bigint'   => $integerCast,
288 7
            'float'    => 'DECIMAL',
289 7
            'decimal'  => 'DECIMAL',
290
        ];
291
292
        // No cast required
293 7
        if (!array_key_exists($type, $typeToCast)) {
294 7
            return $condition;
295
        }
296
297 7
        return "CAST($condition AS {$typeToCast[$type]})";
298
    }
299
300
301
    /**
302
     * Get the right CAST for an INTEGER
303
     *
304
     * @param  string $field
305
     * @return string
306
     */
307 7
    private function getIntegerCast(string $field): string
308
    {
309 7
        $driver = $this->getConn()->getDriver();
310 7
        if ($driver->getName() === 'pdo_mysql') {
311 7
            return $this->entityCols[$field]['unsigned'] === true ? 'UNSIGNED' : 'SIGNED';
312
        }
313
314
        return 'INTEGER';
315
    }
316
317
318
    /**
319
     * Update data of table
320
     *
321
     * @param  bool   $returnRes
322
     * @param  bool   $pretend
323
     * @param  callable $callback
324
     * @return array
325
     */
326 10
    private function updateData(bool $returnRes, bool $pretend, $callback): array
327
    {
328
        // I need to read line by line if I have to update the table
329
        // to make sure I do update by update (slower but no other choice for now)
330 10
        $rowNum = 0;
331 10
        $queries = [];
332
333 10
        $queryBuilder = $this->conn->createQueryBuilder();
334 10
        $rows = $queryBuilder->select($this->priKey)->from($this->entity)->execute();
335
336 10
        foreach ($rows as $row) {
0 ignored issues
show
Bug introduced by
The expression $rows of type object<Doctrine\DBAL\Driver\Statement>|integer is not guaranteed to be traversable. How about adding an additional type check?

There are different options of fixing this problem.

  1. 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) { /** ... */ }
    
  2. 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) { /** .. */ }
    
  3. 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...
337 9
            $queryBuilder = $this->prepareUpdate($row[$this->priKey]);
338
339 7
            ($returnRes === true ? array_push($queries, $this->getRawSQL($queryBuilder)) : '');
340
341 7
            if ($pretend === false) {
342 4
                $queryBuilder->execute();
343
            }
344
345 7
            if (!is_null($callback)) {
346 3
                $callback(++$rowNum);
347
            }
348
349 7
            if ($this->limit > 0 && $rowNum >= $this->limit) {
350 7
                break;
351
            }
352
        }
353
354 8
        return $queries;
355
    }
356
357
358
    /**
359
     * Insert data into table
360
     *
361
     * @param  bool   $returnRes
362
     * @param  bool   $pretend
363
     * @param  callable $callback
364
     * @return array
365
     */
366 3
    private function insertData(bool $returnRes, bool $pretend, $callback): array
367
    {
368 3
        $queries = [];
369
370 3
        $queryBuilder = $this->conn->createQueryBuilder();
0 ignored issues
show
Unused Code introduced by
$queryBuilder 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...
371
372 3
        for ($rowNum = 1; $rowNum <= $this->limit; $rowNum++) {
373 3
            $queryBuilder = $this->prepareInsert();
374
375 3
            ($returnRes === true ? array_push($queries, $this->getRawSQL($queryBuilder)) : '');
376
377 3
            if ($pretend === false) {
378 2
                $queryBuilder->execute();
379
            }
380
381 3
            if (!is_null($callback)) {
382 1
                $callback($rowNum);
383
            }
384
        }
385
386 3
        return $queries;
387
    }
388
}
389