StorageMapper::deleteDataSimulate() - Code Metrics - Inspection of "cleanup" - Rello/analytics - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( b7e0fc...f45de3 )

by Marcel

created 2021-02-27 19:28 UTC

StorageMapper::deleteDataSimulate() A

↳ Parent: StorageMapper

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
cc	1
eloc	13
c	2
b	0
f	0
nc	1
nop	3
dl	0
loc	17
rs	9.8333

<?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 2021 Marcel Scherello
 */

namespace OCA\Analytics\Db;

use OCP\DB\QueryBuilder\IQueryBuilder;
use OCP\IDBConnection;
use OCP\IL10N;
use OCP\ILogger;

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

    public function __construct(
        $userId,
        IL10N $l10n,
        IDBConnection $db,
        ILogger $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 int|null $timestamp
     * @return string
     */
    public function create(int $datasetId, $dimension1, $dimension2, $value, string $user_id = null, $timestamp = null)
    {
        $dimension1 = str_replace('*', '', $dimension1);
        $dimension2 = str_replace('*', '', $dimension2);
        $timestamp = $timestamp ?? time();

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

        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->addSelect('id')
            ->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('value', $sql->createNamedParameter($value))
                ->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)));
            $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)
    {
        $dimension1 = str_replace('*', '%', $dimension1);
        $dimension2 = str_replace('*', '%', $dimension2);

        $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
     */
    public function deleteSimulate(int $datasetId, $dimension1, $dimension2)
    {
        $dimension1 = str_replace('*', '%', $dimension1);
        $dimension2 = str_replace('*', '%', $dimension2);

        $sql = $this->db->getQueryBuilder();
        $sql->from(self::TABLE_NAME)
            ->addSelect($sql->func()->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 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('user_id', $sql->createNamedParameter($this->userId)))
            ->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
        $sql->execute();
        return true;
    }

    /**
     * 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 2021 Marcel Scherello
10			*/
11
12			namespace OCA\Analytics\Db;
13
14			use OCP\DB\QueryBuilder\IQueryBuilder;
15			use OCP\IDBConnection;
16			use OCP\IL10N;
17			use OCP\ILogger;
18
19			class StorageMapper
20			{
21			private $userId;
22			private $l10n;
23			private $db;
24			private $logger;
25			const TABLE_NAME = 'analytics_facts';
26
27			public function __construct(
28			$userId,
29			IL10N $l10n,
30			IDBConnection $db,
31			ILogger $logger
32			)
33			{
34			$this->userId = $userId;
35			$this->l10n = $l10n;
36			$this->db = $db;
37			$this->logger = $logger;
38			self::TABLE_NAME;
39			}
40
41			/**
42			* create data
43			* @param int $datasetId
44			* @param $dimension1
45			* @param $dimension2
46			* @param $value
47			* @param string\|null $user_id
48			* @param int\|null $timestamp
49			* @return string
50			*/
51			public function create(int $datasetId, $dimension1, $dimension2, $value, string $user_id = null, $timestamp = null)
52			{
53			$dimension1 = str_replace('*', '', $dimension1);
54			$dimension2 = str_replace('*', '', $dimension2);
55			$timestamp = $timestamp ?? time();
56
57			if ($user_id) $this->userId = $user_id;
58
59			$sql = $this->db->getQueryBuilder();
60			$sql->from(self::TABLE_NAME)
61			->addSelect('id')
62			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
63			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
64			->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
65			->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));
66			$statement = $sql->execute();
67			$result = $statement->fetch();
68			$statement->closeCursor();
69
70			if ($result) {
71			$sql = $this->db->getQueryBuilder();
72			$sql->update(self::TABLE_NAME)
73			->set('value', $sql->createNamedParameter($value))
74			->set('timestamp', $sql->createNamedParameter($timestamp))
75			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
76			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
77			->andWhere($sql->expr()->eq('dimension1', $sql->createNamedParameter($dimension1)))
78			->andWhere($sql->expr()->eq('dimension2', $sql->createNamedParameter($dimension2)));
79			$sql->execute();
80			return 'update';
81			} else {
82			$sql = $this->db->getQueryBuilder();
83			$sql->insert(self::TABLE_NAME)
84			->values([
85			'user_id' => $sql->createNamedParameter($this->userId),
86			'dataset' => $sql->createNamedParameter($datasetId),
87			'dimension1' => $sql->createNamedParameter($dimension1),
88			'dimension2' => $sql->createNamedParameter($dimension2),
89			'value' => $sql->createNamedParameter($value),
90			'timestamp' => $sql->createNamedParameter($timestamp),
91			]);
92			$sql->execute();
93			return 'insert';
94			}
95			}
96
97			/**
98			* read data for dataset
99			* @param int $dataset
100			* @param array $options
101			* @return array
102			*/
103			public function read(int $dataset, $options = '')
104			{
105			$sql = $this->db->getQueryBuilder();
106			$sql->from(self::TABLE_NAME)
107			->where($sql->expr()->eq('dataset', $sql->createNamedParameter($dataset)))
108			->addgroupBy('dataset');
109
110			// loop the available dimensions and check if any is hidden by the drilldown selection of the user
111			$availableDimensions = array('dimension1', 'dimension2');
112			foreach ($availableDimensions as $dimension) {
113			if (!isset($options['drilldown'][$dimension])) {
114			$sql->addSelect($dimension)
115			->addGroupBy($dimension)
116			->addOrderBy($dimension, 'ASC');
117			}
118			}
119
120			// value column deeds to be at the last position in the select. So it needs to be after the dynamic selects
121			$sql->addSelect($sql->func()->sum('value'));
122
123			// add the where clauses depending on the filter selection of the
124			if (isset($options['filter'])) {
125			foreach ($options['filter'] as $key => $value) {
126			$this->sqlWhere($sql, $key, $value['option'], $value['value']);
127			}
128			}
129
130			$statement = $sql->execute();
131			$rows = $statement->fetchAll();
132			$statement->closeCursor();
133
134			// reindex result to get rid of the column headers as the frontend works incremental
135			foreach ($rows as &$row) {
136			$row = array_values($row);
137			}
138			return $rows;
139			}
140
141			/**
142			* delete data
143			* @param int $datasetId
144			* @param $dimension1
145			* @param $dimension2
146			* @return bool
147			*/
148			public function delete(int $datasetId, $dimension1, $dimension2)
149			{
150			$dimension1 = str_replace('*', '%', $dimension1);
151			$dimension2 = str_replace('*', '%', $dimension2);
152
153			$sql = $this->db->getQueryBuilder();
154			$sql->delete(self::TABLE_NAME)
155			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
156			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
157			->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
158			->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
159			$sql->execute();
160
161			return true;
162			}
163
164			/**
165			* Simulate delete data
166			* @param int $datasetId
167			* @param $dimension1
168			* @param $dimension2
169			* @return array
170			*/
171			public function deleteSimulate(int $datasetId, $dimension1, $dimension2)
172			{
173			$dimension1 = str_replace('*', '%', $dimension1);
174			$dimension2 = str_replace('*', '%', $dimension2);
175
176			$sql = $this->db->getQueryBuilder();
177			$sql->from(self::TABLE_NAME)
178			->addSelect($sql->func()->count('*'))
179			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
180			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)))
181			->andWhere($sql->expr()->like('dimension1', $sql->createNamedParameter($dimension1)))
182			->andWhere($sql->expr()->like('dimension2', $sql->createNamedParameter($dimension2)));
183			$statement = $sql->execute();
184			$result = $statement->fetch();
185			$statement->closeCursor();
186
187			return $result;
188			}
189
190			/**
191			* delete all data of a dataset
192			* @param int $datasetId
193			* @return bool
194			*/
195			public function deleteByDataset(int $datasetId)
196			{
197			$sql = $this->db->getQueryBuilder();
198			$sql->delete(self::TABLE_NAME)
199			->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId)))
200			->andWhere($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId)));
201			$sql->execute();
202			return true;
203			}
204
205			/**
206			* Add where statements to a query builder
207			*
208			* @param IQueryBuilder $sql
209			* @param $column
210			* @param $option
211			* @param $value
212			*/
213			protected function sqlWhere(IQueryBuilder $sql, $column, $option, $value)
214			{
215			if ($option === 'EQ') {
216			$sql->andWhere($sql->expr()->eq($column, $sql->createNamedParameter($value)));
217			} elseif ($option === 'GT') {
218			$sql->andWhere($sql->expr()->gt($column, $sql->createNamedParameter($value)));
219			} elseif ($option === 'LT') {
220			$sql->andWhere($sql->expr()->lt($column, $sql->createNamedParameter($value)));
221			} elseif ($option === 'IN') {
222			$sql->andWhere($sql->expr()->in($column, $sql->createParameter('inValues')));
223			$sql->setParameter('inValues', explode(',', $value), IQueryBuilder::PARAM_STR_ARRAY);
224			} elseif ($option === 'LIKE') {
225			$sql->andWhere($sql->expr()->like($column, $sql->createNamedParameter('%' . $value . '%')));
226			}
227			}
228			}

Rello / analytics

Push — master ( b7e0fc...f45de3 )

StorageMapper::deleteDataSimulate() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like