StorageMapper::read() - Code Metrics - Inspection of "Preparation for key figure aggregation mode in dat..." - Rello/analytics - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 1876ce...d1e05a )

by Marcel

created 2023-01-31 16:26 UTC

StorageMapper::read() B

↳ Parent: StorageMapper

Complexity

Conditions	6
Paths	12

Size

Total Lines	36
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	6
eloc	20
nc	12
nop	2
dl	0
loc	36
rs	8.9777
c	0
b	0
f	0

<?php
/**
 * Analytics
 *
 * This file is licensed under the Affero General Public License version 3 or
 * later. See the LICENSE.md file.
 *
 * @author Marcel Scherello <[email protected]>
 * @copyright 2019-2022 Marcel Scherello
 */

namespace OCA\Analytics\Db;

use OCP\DB\Exception;
use OCP\DB\QueryBuilder\IQueryBuilder;
use OCP\IDBConnection;
use OCP\IL10N;
use Psr\Log\LoggerInterface;

class StorageMapper
{
    private $userId;
    private $l10n;
    private $db;
    private $logger;
    const TABLE_NAME = 'analytics_facts';

    public function __construct(
        $userId,
        IL10N $l10n,
        IDBConnection $db,
        LoggerInterface $logger
    )
    {
        $this->userId = $userId;
        $this->l10n = $l10n;
        $this->db = $db;
        $this->logger = $logger;
        self::TABLE_NAME;
    }

    /**
     * create data
     * @param int $datasetId
     * @param $dimension1
     * @param $dimension2
     * @param $value
     * @param string|null $user_id
     * @param null $timestamp

     * @param $bulkInsert
     * @param $aggregation
     * @return string
     * @throws Exception
     */
    public function create(int $datasetId, $dimension1, $dimension2, $value, string $user_id = null, $timestamp = null, $bulkInsert = null, $aggregation = null)
    {
        $dimension1 = str_replace('*', '', $dimension1);
        $dimension2 = str_replace('*', '', $dimension2);
        $timestamp = $timestamp ?? time();
        $result = '';

        if ($user_id) $this->userId = $user_id;

        // if the data source option to delete all date before loading is "true"
        // in this case, bulkInsert is set to true. Then no further checks for existing records is needed
        if ($bulkInsert === null) {
            $sql = $this->db->getQueryBuilder();
            $sql->from(self::TABLE_NAME)
                ->addSelect('value')
                ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
                ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
                ->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
                ->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));
            $statement = $sql->execute();
            $result = $statement->fetch();
            $statement->closeCursor();
        }

        if ($result) {
            $sql = $this->db->getQueryBuilder();
            $sql->update(self::TABLE_NAME)
                ->set('timestamp', $sql->createNamedParameter($timestamp))
                ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
                ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
                ->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
                ->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));

            if ($aggregation === 'summation') {
                // Feature not yet available
                // $this->logger->error('old value: ' . $result['value']);
                // $this->logger->error('new value: ' . $value + $result['value']);
                $sql->set('value', $sql->createNamedParameter($value + $result['value']));
            } else {
                $sql->set('value', $sql->createNamedParameter($value));
            }

            $sql->execute();
            return 'update';
        } else {
            $sql = $this->db->getQueryBuilder();
            $sql->insert(self::TABLE_NAME)
                ->values([
                    'user_id' => $sql->createNamedParameter($this->userId),
                    'dataset' => $sql->createNamedParameter($datasetId),
                    'dimension1' => $sql->createNamedParameter($dimension1),
                    'dimension2' => $sql->createNamedParameter($dimension2),
                    'value' => $sql->createNamedParameter($value),
                    'timestamp' => $sql->createNamedParameter($timestamp),
                ]);
            $sql->execute();
            return 'insert';
        }
    }

    /**
     * read data for dataset
     * @param int $dataset
     * @param array $options
     * @return array
     */
    public function read(int $dataset, $options = '')
    {
        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->where($sql->expr()->eq('dataset', $sql->createNamedParameter($dataset)))
            ->addgroupBy('dataset');

        // loop the available dimensions and check if any is hidden by the drilldown selection of the user
        $availableDimensions = array('dimension1', 'dimension2');
        foreach ($availableDimensions as $dimension) {
            if (!isset($options['drilldown'][$dimension])) {
                $sql->addSelect($dimension)
                    ->addGroupBy($dimension)
                    ->addOrderBy($dimension, 'ASC');
            }
        }

        // value column deeds to be at the last position in the select. So it needs to be after the dynamic selects
        $sql->addSelect($sql->func()->sum('value'));

        // add the where clauses depending on the filter selection of the
        if (isset($options['filter'])) {
            foreach ($options['filter'] as $key => $value) {
                $this->sqlWhere($sql, $key, $value['option'], $value['value']);
            }
        }

        $statement = $sql->execute();
        $rows = $statement->fetchAll();
        $statement->closeCursor();

        // reindex result to get rid of the column headers as the frontend works incremental
        foreach ($rows as &$row) {
            $row = array_values($row);
        }
        return $rows;
    }

    /**
     * delete data
     * @param int $datasetId
     * @param $dimension1
     * @param $dimension2
     * @return bool
     */
    public function delete(int $datasetId, $dimension1, $dimension2, string $user_id = null)
    {
        $dimension1 = str_replace('*', '%', $dimension1);
        $dimension2 = str_replace('*', '%', $dimension2);
        if ($user_id) $this->userId = $user_id;

        $sql = $this->db->getQueryBuilder();
        $sql->delete(self::TABLE_NAME)
            ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
            ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
            ->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
            ->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
        $sql->execute();

        return true;
    }

    /**
     * Simulate delete data
     * @param int $datasetId
     * @param $dimension1
     * @param $dimension2
     * @return array
     * @throws Exception
     */
    public function deleteSimulate(int $datasetId, $dimension1, $dimension2)
    {
        $dimension1 = str_replace('*', '%', $dimension1);
        $dimension2 = str_replace('*', '%', $dimension2);

        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->selectAlias($sql->func()->count('*'), 'count')
            ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
            ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
            ->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
            ->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
        $statement = $sql->execute();
        $result = $statement->fetch();
        $statement->closeCursor();

        return $result;
    }

    /**
     * delete data
     * @param int $datasetId
     * @param $options
     * @return bool
     * @throws Exception
     */
    public function deleteWithFilter(int $datasetId, $options)
    {
        $sql = $this->db->getQueryBuilder();
        $sql->delete(self::TABLE_NAME)
            ->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));

        // add the where clauses depending on the filter selection of the
        if (isset($options['filter'])) {
            foreach ($options['filter'] as $key => $value) {
                $this->sqlWhere($sql, $key, $value['option'], $value['value']);
            }
        }
        $sql->execute();

        return true;
    }

    /**
     * delete data
     * @param int $datasetId
     * @param $options
     * @return bool
     * @throws Exception
     */
    public function deleteWithFilterSimulate(int $datasetId, $options)
    {
        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->selectAlias($sql->func()->count('*'), 'count')
            ->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));

            // add the where clauses depending on the filter selection of the
            if (isset($options['filter'])) {
                foreach ($options['filter'] as $key => $value) {
                    $this->sqlWhere($sql, $key, $value['option'], $value['value']);
                }
            }

        $statement = $sql->execute();
        $result = $statement->fetch();
        $statement->closeCursor();
        return $result;
    }

        /**
     * delete all data of a dataset
     * @param int $datasetId
     * @return bool
     */
    public function deleteByDataset(int $datasetId)
    {
        $sql = $this->db->getQueryBuilder();
        $sql->delete(self::TABLE_NAME)
            ->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
        $sql->execute();
        return true;
    }

    /**
     * Simulate delete data
     * @param int $datasetId
     * @return array
     */
    public function getRecordCount(int $datasetId)
    {
        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->selectAlias($sql->func()->count('*'), 'count')
            ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
            ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
        $statement = $sql->execute();
        $result = $statement->fetch();
        $statement->closeCursor();
        return $result;
    }

    /**
     * Add where statements to a query builder
     *
     * @param IQueryBuilder $sql
     * @param $column
     * @param $option
     * @param $value
     */
    protected function sqlWhere(IQueryBuilder $sql, $column, $option, $value)
    {
        if ($option === 'EQ') {
            $sql->andWhere($sql->expr()->eq($column, $sql->createNamedParameter($value)));
        } elseif ($option === 'GT') {
            $sql->andWhere($sql->expr()->gt($column, $sql->createNamedParameter($value)));
        } elseif ($option === 'LT') {
            $sql->andWhere($sql->expr()->lt($column, $sql->createNamedParameter($value)));
        } elseif ($option === 'IN') {
            $sql->andWhere($sql->expr()->in($column, $sql->createParameter('inValues')));
            $sql->setParameter('inValues', explode(',', $value), IQueryBuilder::PARAM_STR_ARRAY);
        } elseif ($option === 'LIKE') {
            $sql->andWhere($sql->expr()->like($column, $sql->createNamedParameter('%' . $value . '%')));
        }
    }
}

1			<?php
2			/**
3			* Analytics
4			*
5			* This file is licensed under the Affero General Public License version 3 or
6			* later. See the LICENSE.md file.
7			*
8			* @author Marcel Scherello <[email protected]>
9			* @copyright 2019-2022 Marcel Scherello
10			*/
11
12			namespace OCA\Analytics\Db;
13
14			use OCP\DB\Exception;
15			use OCP\DB\QueryBuilder\IQueryBuilder;
16			use OCP\IDBConnection;
17			use OCP\IL10N;
18			use Psr\Log\LoggerInterface;
19
20			class StorageMapper
21			{
22			private $userId;
23			private $l10n;
24			private $db;
25			private $logger;
26			const TABLE_NAME = 'analytics_facts';
27
28			public function __construct(
29			$userId,
30			IL10N $l10n,
31			IDBConnection $db,
32			LoggerInterface $logger
33			)
34			{
35			$this->userId = $userId;
36			$this->l10n = $l10n;
37			$this->db = $db;
38			$this->logger = $logger;
39			self::TABLE_NAME;
40			}
41
42			/**
43			* create data
44			* @param int $datasetId
45			* @param $dimension1
46			* @param $dimension2
47			* @param $value
48			* @param string\|null $user_id
49			* @param null $timestamp
			0 ignored issues – show Documentation Bug introduced 2023-01-31 16:29 UTC by Report Bug Copy Issue Report Are you sure the doc-type for parameter `$timestamp` is correct as it would always require `null` to be passed? Loading history...
50			* @param $bulkInsert
51			* @param $aggregation
52			* @return string
53			* @throws Exception
54			*/
55			public function create(int $datasetId, $dimension1, $dimension2, $value, string $user_id = null, $timestamp = null, $bulkInsert = null, $aggregation = null)
56			{
57			$dimension1 = str_replace('*', '', $dimension1);
58			$dimension2 = str_replace('*', '', $dimension2);
59			$timestamp = $timestamp ?? time();
60			$result = '';
61
62			if ($user_id) $this->userId = $user_id;
63
64			// if the data source option to delete all date before loading is "true"
65			// in this case, bulkInsert is set to true. Then no further checks for existing records is needed
66			if ($bulkInsert === null) {
67			$sql = $this->db->getQueryBuilder();
68			$sql->from(self::TABLE_NAME)
69			->addSelect('value')
70			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
71			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
72			->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
73			->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));
74			$statement = $sql->execute();
75			$result = $statement->fetch();
76			$statement->closeCursor();
77			}
78
79			if ($result) {
80			$sql = $this->db->getQueryBuilder();
81			$sql->update(self::TABLE_NAME)
82			->set('timestamp', $sql->createNamedParameter($timestamp))
83			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
84			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
85			->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
86			->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));
87
88			if ($aggregation === 'summation') {
89			// Feature not yet available
90			// $this->logger->error('old value: ' . $result['value']);
91			// $this->logger->error('new value: ' . $value + $result['value']);
92			$sql->set('value', $sql->createNamedParameter($value + $result['value']));
93			} else {
94			$sql->set('value', $sql->createNamedParameter($value));
95			}
96
97			$sql->execute();
98			return 'update';
99			} else {
100			$sql = $this->db->getQueryBuilder();
101			$sql->insert(self::TABLE_NAME)
102			->values([
103			'user_id' => $sql->createNamedParameter($this->userId),
104			'dataset' => $sql->createNamedParameter($datasetId),
105			'dimension1' => $sql->createNamedParameter($dimension1),
106			'dimension2' => $sql->createNamedParameter($dimension2),
107			'value' => $sql->createNamedParameter($value),
108			'timestamp' => $sql->createNamedParameter($timestamp),
109			]);
110			$sql->execute();
111			return 'insert';
112			}
113			}
114
115			/**
116			* read data for dataset
117			* @param int $dataset
118			* @param array $options
119			* @return array
120			*/
121			public function read(int $dataset, $options = '')
122			{
123			$sql = $this->db->getQueryBuilder();
124			$sql->from(self::TABLE_NAME)
125			->where($sql->expr()->eq('dataset', $sql->createNamedParameter($dataset)))
126			->addgroupBy('dataset');
127
128			// loop the available dimensions and check if any is hidden by the drilldown selection of the user
129			$availableDimensions = array('dimension1', 'dimension2');
130			foreach ($availableDimensions as $dimension) {
131			if (!isset($options['drilldown'][$dimension])) {
132			$sql->addSelect($dimension)
133			->addGroupBy($dimension)
134			->addOrderBy($dimension, 'ASC');
135			}
136			}
137
138			// value column deeds to be at the last position in the select. So it needs to be after the dynamic selects
139			$sql->addSelect($sql->func()->sum('value'));
140
141			// add the where clauses depending on the filter selection of the
142			if (isset($options['filter'])) {
143			foreach ($options['filter'] as $key => $value) {
144			$this->sqlWhere($sql, $key, $value['option'], $value['value']);
145			}
146			}
147
148			$statement = $sql->execute();
149			$rows = $statement->fetchAll();
150			$statement->closeCursor();
151
152			// reindex result to get rid of the column headers as the frontend works incremental
153			foreach ($rows as &$row) {
154			$row = array_values($row);
155			}
156			return $rows;
157			}
158
159			/**
160			* delete data
161			* @param int $datasetId
162			* @param $dimension1
163			* @param $dimension2
164			* @return bool
165			*/
166			public function delete(int $datasetId, $dimension1, $dimension2, string $user_id = null)
167			{
168			$dimension1 = str_replace('*', '%', $dimension1);
169			$dimension2 = str_replace('*', '%', $dimension2);
170			if ($user_id) $this->userId = $user_id;
171
172			$sql = $this->db->getQueryBuilder();
173			$sql->delete(self::TABLE_NAME)
174			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
175			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
176			->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
177			->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
178			$sql->execute();
179
180			return true;
181			}
182
183			/**
184			* Simulate delete data
185			* @param int $datasetId
186			* @param $dimension1
187			* @param $dimension2
188			* @return array
189			* @throws Exception
190			*/
191			public function deleteSimulate(int $datasetId, $dimension1, $dimension2)
192			{
193			$dimension1 = str_replace('*', '%', $dimension1);
194			$dimension2 = str_replace('*', '%', $dimension2);
195
196			$sql = $this->db->getQueryBuilder();
197			$sql->from(self::TABLE_NAME)
198			->selectAlias($sql->func()->count('*'), 'count')
199			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
200			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
201			->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
202			->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
203			$statement = $sql->execute();
204			$result = $statement->fetch();
205			$statement->closeCursor();
206
207			return $result;
208			}
209
210			/**
211			* delete data
212			* @param int $datasetId
213			* @param $options
214			* @return bool
215			* @throws Exception
216			*/
217			public function deleteWithFilter(int $datasetId, $options)
218			{
219			$sql = $this->db->getQueryBuilder();
220			$sql->delete(self::TABLE_NAME)
221			->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
222
223			// add the where clauses depending on the filter selection of the
224			if (isset($options['filter'])) {
225			foreach ($options['filter'] as $key => $value) {
226			$this->sqlWhere($sql, $key, $value['option'], $value['value']);
227			}
228			}
229			$sql->execute();
230
231			return true;
232			}
233
234			/**
235			* delete data
236			* @param int $datasetId
237			* @param $options
238			* @return bool
239			* @throws Exception
240			*/
241			public function deleteWithFilterSimulate(int $datasetId, $options)
242			{
243			$sql = $this->db->getQueryBuilder();
244			$sql->from(self::TABLE_NAME)
245			->selectAlias($sql->func()->count('*'), 'count')
246			->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
247
248			// add the where clauses depending on the filter selection of the
249			if (isset($options['filter'])) {
250			foreach ($options['filter'] as $key => $value) {
251			$this->sqlWhere($sql, $key, $value['option'], $value['value']);
252			}
253			}
254
255			$statement = $sql->execute();
256			$result = $statement->fetch();
257			$statement->closeCursor();
258			return $result;
259			}
260
261			/**
262			* delete all data of a dataset
263			* @param int $datasetId
264			* @return bool
265			*/
266			public function deleteByDataset(int $datasetId)
267			{
268			$sql = $this->db->getQueryBuilder();
269			$sql->delete(self::TABLE_NAME)
270			->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
271			$sql->execute();
272			return true;
273			}
274
275			/**
276			* Simulate delete data
277			* @param int $datasetId
278			* @return array
279			*/
280			public function getRecordCount(int $datasetId)
281			{
282			$sql = $this->db->getQueryBuilder();
283			$sql->from(self::TABLE_NAME)
284			->selectAlias($sql->func()->count('*'), 'count')
285			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
286			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
287			$statement = $sql->execute();
288			$result = $statement->fetch();
289			$statement->closeCursor();
290			return $result;
291			}
292
293			/**
294			* Add where statements to a query builder
295			*
296			* @param IQueryBuilder $sql
297			* @param $column
298			* @param $option
299			* @param $value
300			*/
301			protected function sqlWhere(IQueryBuilder $sql, $column, $option, $value)
302			{
303			if ($option === 'EQ') {
304			$sql->andWhere($sql->expr()->eq($column, $sql->createNamedParameter($value)));
305			} elseif ($option === 'GT') {
306			$sql->andWhere($sql->expr()->gt($column, $sql->createNamedParameter($value)));
307			} elseif ($option === 'LT') {
308			$sql->andWhere($sql->expr()->lt($column, $sql->createNamedParameter($value)));
309			} elseif ($option === 'IN') {
310			$sql->andWhere($sql->expr()->in($column, $sql->createParameter('inValues')));
311			$sql->setParameter('inValues', explode(',', $value), IQueryBuilder::PARAM_STR_ARRAY);
312			} elseif ($option === 'LIKE') {
313			$sql->andWhere($sql->expr()->like($column, $sql->createNamedParameter('%' . $value . '%')));
314			}
315			}
316			}

Rello / analytics

Push — master ( 1876ce...d1e05a )

StorageMapper::read() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like