Completed
Pull Request — master (#268)
by
unknown
01:12
created

Xhgui_Storage_PDO::findOne()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 21

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 21
rs 9.584
c 0
b 0
f 0
cc 1
nc 1
nop 1
1
<?php
2
/**
3
 * Get profiles using PDO database connection
4
 */
5
class Xhgui_Storage_PDO extends Xhgui_Storage_Abstract implements
6
    \Xhgui_StorageInterface,
7
    \Xhgui_WatchedFunctionsStorageInterface
8
{
9
10
    /**
11
     * @var \PDO
12
     */
13
    protected $connection;
14
15
    /**
16
     * @var bool 
17
     */
18
    protected $jsonMode = false;
19
20
    /**
21
     * PDO constructor.
22
     * @param $config
23
     */
24
    public function __construct($config)
25
    {
26
        $this->connection = new \PDO(
27
            $config['db.dsn'],
28
            !empty($config['db.user'])      ? $config['db.user'] : null,
29
            !empty($config['db.password'])  ? $config['db.password'] : null,
30
            !empty($config['db.options'])   ? $config['db.options'] : []
31
        );
32
        $this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
33
        $this->connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
34
35
        if (!isset($config['db.json_mode']) && !empty($config['db.dsn'])) {
36
            $this->jsonMode = substr($config['db.dsn'], 0, strpos($config['db.dsn'], ':'));
0 ignored issues
show
Documentation Bug introduced by
The property $jsonMode was declared of type boolean, but substr($config['db.dsn']...config['db.dsn'], ':')) is of type string. Maybe add a type cast?

This check looks for assignments to scalar types that may be of the wrong type.

To ensure the code behaves as expected, it may be a good idea to add an explicit type cast.

$answer = 42;

$correct = false;

$correct = (bool) $answer;
Loading history...
37
        } else {
38
            $this->jsonMode = $config['db.json_mode'];
39
        }
40
    }
41
42
    /**
43
     * @inheritDoc
44
     * @param \Xhgui_Storage_Filter $filter
45
     * @param bool $projections
46
     * @return \Xhgui_Storage_ResultSet
47
     */
48
    public function find(\Xhgui_Storage_Filter $filter, $projections = false)
49
    {
50
        list($query, $params) = $this->getQuery($filter, false);
51
        
52
        try {
53
            $stmt = $this->connection->prepare($query);
54
            $stmt->execute($params);
55
        } catch (\Exception $e) {
56
            print_r($e->getMessage());
57
            exit;
58
        }
59
60
        $tmp = [];
61
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
62
            $meta = json_decode($row['meta'], true);
63 View Code Duplication
            if ($filter->getCookie()) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
64
                // because cookie is not parsed and stored in separate structure we need to double check if
65
                // value that we search is in fact in http cookie server field. SQL filter only checks whole
66
                // meta
67
                if (strpos($meta['SERVER']['HTTP_COOKIE'], $filter->getCookie()) === false) {
68
                    continue;
69
                }
70
            }
71
            $tmp[$row['id']] = $row;
72
            $tmp[$row['id']]['profile']    = json_decode($row['profiles'], true);
73
            $tmp[$row['id']]['meta']       = $meta;
74
        }
75
        
76
        return new \Xhgui_Storage_ResultSet($tmp);
77
    }
78
79
    /**
80
     * Get query that is used for both list and count
81
     *
82
     * @param \Xhgui_Storage_Filter $filter
83
     * @param bool $count
84
     * @return array
85
     */
86
    protected function getQuery(\Xhgui_Storage_Filter $filter, $count = false)
87
    {
88
        $params = [];
89
90
        if ($count === true) {
91
            $columns = ' count(*) as c ';
92
        } else {
93
            $columns = ' p.*, i.*, m.*, p.profile_id as _id, main_wt as duration ';
94
        }
95
96
        $sql = "
97
select 
98
    $columns
99
from 
100
    profiles as p left join 
101
    profiles_info as i on (p.profile_id = i.id) LEFT JOIN
102
    profiles_meta as m on (p.profile_id = m.profile_id)
103
";
104
105
        $where = [];
106
107
        foreach ([
108
            'url'               => 'url',
109
            'method'            => 'method',
110
            'application'       => 'application',
111
            'version'           => 'version',
112
            'branch'            => 'branch',
113
            'controller'        => 'controller',
114
            'action'            => 'action',
115
            'cookie'            => 'cookie',
116
            'remote_addr'       => 'ip',
117
            ] as $dbField => $field) {
118
            $method = 'get'.ucfirst($field);
119
120
            if ($filter->{$method}()) {
121
                switch($field) {
122
                    case 'url':
123
                        $url = $filter->{$method}();
124
                        $where[]              = ' ( url like :url OR simple_url like :simple_url)';
125
                        $params['url']        = '%'.$url.'%';
126
                        $params['simple_url'] = '%'.$url.'%';
127
                        break;
128
129
                    case 'action':
130 View Code Duplication
                    case 'controller':
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
131
                        $where[]        = ' '.$dbField.' like :'.$field.' ';
132
                        $params[$field] = ($filter->{$method}()).'%';
133
                        break;
134
135
                    case 'cookie':
136
                        // @todo move this to driver specific storage class
137
                        list($where, $params) = $this->compareWithJson(
138
                            $where,
139
                            $params,
140
                            $field,
141
                            $filter->{$method}(),
142
                            'meta',
143
                            ['SERVER', 'HTTP_COOKIE']
144
                        );
145
                        break;
146
147 View Code Duplication
                    default:
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
148
                        $where[]        = ' '.$dbField.' = :'.$field.' ';
149
                        $params[$field] = $filter->{$method}();
150
                        break;
151
                }
152
            }
153
        }
154
        
155
        if ($filter->getStartDate()) {
156
            $where[]                = ' request_time >= :startDate';
157
            $params['startDate']   = $this->getDateTimeFromString($filter->getStartDate(), 'start')
0 ignored issues
show
Documentation introduced by
$filter->getStartDate() is of type object<DateTime>, but the function expects a string|integer.

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...
158
                                          ->format('Y-m-d H:i:s');
159
        }
160
161
        if ($filter->getEndDate()) {
162
            $where[]                = ' request_time <= :endDate';
163
            $params['endDate']   = $this->getDateTimeFromString($filter->getEndDate(), 'end')
0 ignored issues
show
Documentation introduced by
$filter->getEndDate() is of type object<DateTime>, but the function expects a string|integer.

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...
164
                                        ->format('Y-m-d H:i:s');
165
        }
166
167
        if (!empty($where)) {
168
            $sql .= ' WHERE '.join(' AND ', $where);
169
        }
170
171
        if ($count === true) {
172
            return [$sql, $params];
173
        }
174
175
        switch ($filter->getSort()) {
176
            case 'ct':
177
                $sql .= ' order by main_ct';
178
                break;
179
180
            case 'wt':
181
                $sql .= ' order by main_wt';
182
                break;
183
184
            case 'cpu':
185
                $sql .= ' order by main_cpu';
186
                break;
187
188
            case 'mu':
189
                $sql .= ' order by main_mu';
190
                break;
191
192
            case 'pmu':
193
                $sql .= ' order by main_pmu';
194
                break;
195
196
            case 'controller':
197
                $sql .= ' order by controller';
198
                break;
199
200
            case 'action':
201
                $sql .= ' order by action';
202
                break;
203
204
            case 'application':
205
                $sql .= ' order by application';
206
                break;
207
208
            case 'branch':
209
                $sql .= ' order by branch';
210
                break;
211
212
            case 'version':
213
                $sql .= ' order by version';
214
                break;
215
216
            case 'time':
217
            default:
218
                $sql .= ' order by request_time';
219
                break;
220
        }
221
222
        switch ($filter->getDirection()) {
223
            case 'asc':
224
                $sql .= ' asc ';
225
                break;
226
227
            default:
228
            case 'desc':
0 ignored issues
show
Unused Code introduced by
case 'desc': $sql .= ' desc '; break; does not seem to be reachable.

This check looks for unreachable code. It uses sophisticated control flow analysis techniques to find statements which will never be executed.

Unreachable code is most often the result of return, die or exit statements that have been added for debug purposes.

function fx() {
    try {
        doSomething();
        return true;
    }
    catch (\Exception $e) {
        return false;
    }

    return false;
}

In the above example, the last return false will never be executed, because a return statement has already been met in every possible execution path.

Loading history...
229
                $sql .= ' desc ';
230
                break;
231
        }
232
233
        if ($filter->getPerPage()) {
234
            $sql            .= ' LIMIT :limit ';
235
            $params['limit'] = (int)$filter->getPerPage();
236
        }
237
238
        if ($filter->getPage()) {
239
            $sql                .= ' OFFSET :offset ';
240
            $params['offset']   = (int)($filter->getPerPage()*($filter->getPage()-1));
241
        }
242
        return [$sql, $params];
243
    }
244
245
    /**
246
     * @inheritDoc
247
     * @param Xhgui_Storage_Filter $filter
248
     * @param $col
249
     * @param int $percentile
250
     * @return array
251
     * @throws Exception
252
     */
253
    public function aggregate(\Xhgui_Storage_Filter $filter, $col, $percentile = 1)
254
    {
255
        $stmt = $this->connection->prepare('select 
256
    * 
257
from 
258
    profiles_info
259
where 
260
    simple_url = :simple_url OR url = :url
261
');
262
        $stmt->execute(['url'=> $filter->getUrl(), 'simple_url'=> $filter->getUrl()]);
263
        $aggregatedData = [];
264
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
265
            $date = new \DateTime($row['request_time']);
266
            $formattedDate = $date->format('Y-m-d H:i');
267 View Code Duplication
            if (empty($aggregatedData[$formattedDate])) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
268
                $aggregatedData[$formattedDate] = [
269
                    'wall_times'    => [],
270
                    'cpu_times'     => [],
271
                    'mu_times'      => [],
272
                    'pmu_times'     => [],
273
                    'row_count'     => 0
274
                ];
275
            }
276
277
            $aggregatedData[$formattedDate]['wall_times'][] = $row['main_wt'];
278
            $aggregatedData[$formattedDate]['cpu_times'][]  = $row['main_cpu'];
279
            $aggregatedData[$formattedDate]['mu_times'][]   = $row['main_mu'];
280
            $aggregatedData[$formattedDate]['pmu_times'][]  = $row['main_pmu'];
281
            $aggregatedData[$formattedDate]['row_count']++;
282
            $aggregatedData[$formattedDate]['_id']          = $date->format('Y-m-d H:i:s');
283
            $aggregatedData[$formattedDate]['raw_index']    =
284
                $aggregatedData[$formattedDate]['row_count']*($percentile/100);
285
        }
286
287
        $return = [
288
            'ok'    => 1,
289
            'result'=> array_values($aggregatedData),
290
        ];
291
        return $return;
292
    }
293
294
    /**
295
     * @inheritDoc
296
     * @@param Xhgui_Storage_Filter $filter
297
     * @return int
298
     */
299
    public function count(\Xhgui_Storage_Filter $filter)
300
    {
301
        list($query, $params) = $this->getQuery($filter, true);
302
        try {
303
            $stmt = $this->connection->prepare($query);
304
            $stmt->execute($params);
305
        } catch (\Exception $e) {
306
            print_r($e->getMessage());
307
            exit;
308
        }
309
310
        $ret = $stmt->fetch(\PDO::FETCH_ASSOC);
311
312
        if (!empty($ret['c'])) {
313
            return $ret['c'];
314
        }
315
        return 0;
316
    }
317
318
    /**
319
     * @inheritDoc
320
     * @param $id
321
     * @return mixed
322
     */
323
    public function findOne($id)
324
    {
325
        $stmt = $this->connection->prepare('
326
select 
327
    * 
328
from 
329
    profiles as p left join 
330
    profiles_info as i on (p.profile_id = i.id) LEFT JOIN
331
    profiles_meta as m on (p.profile_id = m.profile_id)
332
where 
333
    p.profile_id = :id
334
');
335
336
        $stmt->execute(['id'=>$id]);
337
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
338
        $row['profile'] = json_decode($row['profiles'], true);
339
        $row['meta']    = json_decode($row['meta'], true);
340
        $row['_id']     = $id;
341
342
        return $row;
343
    }
344
345
    /**
346
     * @inheritDoc
347
     * @param $id
348
     */
349
    public function remove($id)
350
    {
351
        $this->connection->beginTransaction();
352
        try {
353
            $profileStmt = $this->connection->prepare('delete from profiles where profile_id = :id');
354
            $profileStmt->execute(['id'=>$id]);
355
356
            $metaStmt = $this->connection->prepare('delete from profiles_meta where profile_id = :id');
357
            $metaStmt->execute(['id'=>$id]);
358
359
            $infoStmt = $this->connection->prepare('delete from profiles_info where id = :id');
360
            $infoStmt->execute(['id'=>$id]);
361
            
362
            $this->connection->commit();
363
        } catch (\Exception $e) {
364
            $this->connection->rollBack();
365
        }
366
    }
367
368
    /**
369
     * @inheritDoc
370
     * Remove all data from profile tables
371
     */
372
    public function drop()
373
    {
374
        $this->connection->exec('delete from profiles');
375
        $this->connection->exec('delete from profiles_meta');
376
        $this->connection->exec('delete from profiles_info');
377
    }
378
379
    /**
380
     * @inheritDoc
381
     * @return array
382
     */
383
    public function getWatchedFunctions()
384
    {
385
        $stmt = $this->connection->query('select * from watched order by name desc');
386
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
387
    }
388
389
    /**
390
     * @inheritDoc
391
     * @param $name
392
     * @return bool
393
     */
394
    public function addWatchedFunction($name)
395
    {
396
        $name = trim($name);
397
        if (empty($name)) {
398
            return false;
399
        }
400
        $stmt = $this->connection->prepare('INSERT INTO watched (name) VALUES (:name)');
401
        $stmt->execute(['name'=>trim($name)]);
402
        return true;
403
    }
404
405
    /**
406
     * @inheritDoc
407
     * @param $id
408
     * @param $name
409
     */
410
    public function updateWatchedFunction($id, $name)
411
    {
412
        $stmt = $this->connection->prepare('update watched set name=:name where id = :id');
413
        $stmt->execute(['id'=>$id, 'name'=>$name]);
414
    }
415
416
    /**
417
     * @inheritDoc
418
     * @param $id
419
     */
420
    public function removeWatchedFunction($id)
421
    {
422
        $stmt = $this->connection->prepare('delete from watched where id = :id');
423
        $stmt->execute(['id'=>$id]);
424
    }
425
426
    /**
427
     * This method will look into json stored data in native way (if db supports that) and it will match row based on that.
428
     *
429
     * @todo this should be moved to engine specific storage classes in the future.
430
     * @param array $where
431
     * @param array $params
432
     * @param $field
433
     * @param $value
434
     * @param $fieldToLookIn
435
     * @param array $path
436
     * @return array
437
     */
438
    protected function compareWithJson(array $where, array $params, $field, $value, $fieldToLookIn, array $path)
439
    {
440
        switch ($this->jsonMode) {
441
            case 'mysql':
442
                $where[] = ' JSON_EXTRACT(' .$fieldToLookIn.", '$.".join('.', $path)."') like :cookie";
443
                $params[$field] = '%' . $value . '%';
444
                break;
445
446
            case 'pgsql':
447
                // to match using like we need to cast last leaf to a string.
448
                $lastElement = array_pop($path);
449
                $where[] = ' ' .$fieldToLookIn."->'".join("'->'", $path)."'->>'".$lastElement."' like :cookie";
450
                $params[$field] = '%' . $value . '%';
451
452
                break;
453
            default:
454
                $where[] = ' '.$fieldToLookIn.' like :cookie ';
455
                $params[$field] = '%' . $value . '%';
456
                break;
457
        }
458
        return array($where, $params);
459
    }
460
}
461