DbController::deleteDataByDataset() - Code Metrics - Inspection of "proper deletion of datasets:" - Rello/analytics - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 5493ac...37f48a )

by Marcel

created 2019-11-22 21:33 UTC

DbController::deleteDataByDataset() A

↳ Parent: DbController

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	4
nc	1
nop	1
dl	0
loc	6
rs	10
c	0
b	0
f	0

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

namespace OCA\Analytics\Controller;

use OCP\AppFramework\Controller;
use OCP\IDbConnection;
use OCP\IL10N;
use OCP\ILogger;
use OCP\IRequest;

/**
 * Controller class for main page.
 */
class DbController extends Controller
{

    private $userId;
    private $l10n;
    private $db;
    private $logger;

    public function __construct(
        $appName,
        IRequest $request,
        $userId,
        IL10N $l10n,
        IDbConnection $db,
        ILogger $logger
    )
    {
        parent::__construct($appName, $request);
        $this->userId = $userId;
        $this->l10n = $l10n;
        $this->db = $db;
        $this->logger = $logger;
    }


    /**
     * truncates fiels do DB-field size
     *
     * @param $string
     * @param $length
     * @param $dots
     * @return string
     */
    private function truncate($string, $length, $dots = "...")
    {
        return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string;
    }

    /**
     * Get file id for single track
     * @param int $dataset
     * @param  $objectDrilldown
     * @param  $dateDrilldown
     * @return array
     */
    public function getData(int $dataset, $objectDrilldown = null, $dateDrilldown = null)
    {
        $SQL = 'SELECT';
        if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
        if ($dateDrilldown === 'true') $SQL .= ' `dimension2`,';
        $SQL .= ' SUM(`dimension3`) AS `dimension3`';
        $SQL .= ' FROM `*PREFIX*analytics_facts`
                WHERE `dataset` = ?
                GROUP BY `dataset`';
        if ($objectDrilldown === 'true') $SQL .= ', `dimension1`';
        if ($dateDrilldown === 'true') $SQL .= ', `dimension2`';
        $SQL .= ' ORDER BY';
        if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
        $SQL .= ' `dimension2` ASC';

        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($dataset));
        return $stmt->fetchAll();
    }

    /**
     * update data
     */
    public function updateData()
    {
    }

    /**
     * delete all data of a dataset
     */
    public function deleteDataByDataset(int $datasetId)
    {
        $SQL = 'DELETE FROM `*PREFIX*analytics_facts` WHERE `user_id` = ? AND `dataset` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $datasetId));
        return true;
    }

    /**
     * create data
     * @param int $datasetId
     * @param $dimension1
     * @param $dimension2
     * @param $dimension3
     * @return string
     */
    public function createData(int $datasetId, $dimension1, $dimension2, $dimension3)
    {
        $SQL = 'SELECT `id` FROM `*PREFIX*analytics_facts` WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2));
        $row = $stmt->fetch();
        if ($row) {
            $SQL = 'UPDATE `*PREFIX*analytics_facts` SET `dimension3` = ? WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
            $stmt = $this->db->prepare($SQL);
            $stmt->execute(array($dimension3, $this->userId, $datasetId, $dimension1, $dimension2));
            //$stmt->fetch();
            return 'update';
        } else {
            $SQL = 'INSERT INTO `*PREFIX*analytics_facts` (`user_id`,`dataset`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?)';
            $stmt = $this->db->prepare($SQL);
            $stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2, $dimension3));
            return 'insert';
        }
    }

    /**
     * create dataset
     */
    public function createDataset()
    {
        $SQL = 'INSERT INTO `*PREFIX*analytics_dataset` (`user_id`,`name`,`type`,`parent`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?,?,?)';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $this->l10n->t('New'), 2, 0, $this->l10n->t('Objekt'), $this->l10n->t('Date'), $this->l10n->t('Value')));
        return true;
    }

    /**
     * update dataset
     * @param $id
     * @param $name
     * @param $parent
     * @param $type
     * @param $link
     * @param $visualization
     * @param $chart
     * @param $dimension1
     * @param $dimension2
     * @param $dimension3
     * @return
     */
    public function updateDataset($id, $name, $parent, $type, $link, $visualization, $chart, $dimension1, $dimension2, $dimension3)
    {
        $SQL = 'UPDATE `*PREFIX*analytics_dataset` SET `name`= ?, `type`= ?, `link`= ?, `visualization`= ?, `chart`= ?, `parent`= ?, `dimension1` = ?, `dimension2` = ?, `dimension3` = ? WHERE `user_id` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $name = $this->truncate($name, 64);
        $stmt->execute(array($name, $type, $link, $visualization, $chart, $parent, $dimension1, $dimension2, $dimension3, $this->userId, $id));
        return true;
    }

    /**
     * delete dataset
     * @param $id
     * @return
     */
    public function deleteDataset($id)
    {
        $SQL = 'DELETE FROM `*PREFIX*analytics_dataset` WHERE `user_id` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $id));
        return true;
    }

    /**
     * get datasets
     */
    public function getDatasets()
    {
        $SQL = 'SELECT id, name, type, parent FROM `*PREFIX*analytics_dataset` WHERE  `user_id` = ? ORDER BY `parent` ASC, `name` ASC';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId));
        return $stmt->fetchAll();
    }

    /**
     * get datasets
     * @param $id
     * @return
     */
    public function getOwnDataset($id)
    {
        $SQL = 'SELECT * FROM `*PREFIX*analytics_dataset` WHERE `id` = ? AND `user_id` = ?';
        //$this->logger->error($SQL);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($id, $this->userId));
        return $stmt->fetch();
    }

    /**
     * get datasets
     * @param $id
     * @return
     */
    public function getDatasetOptions($id)
    {
        $SQL = 'SELECT `name`, `visualization`, `chart` FROM `*PREFIX*analytics_dataset` WHERE `id` = ?';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($id));
        return $stmt->fetch();
    }

    // ********** SHARE ************
    // ********** SHARE ************
    // ********** SHARE ************

    /**
     * get datasets
     * @param $token
     * @return
     */
    public function getDatasetByToken($token)
    {
        $SQL = 'SELECT DS.*, SH.password AS password FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.token = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$token]);
        return $stmt->fetch();
        //$this->logger->error($results['password']);
    }

    /**
     * get shared datasets
     * @return
     */
    public function getSharedDatasets()
    {
        $SQL = 'SELECT DS.id, DS.name, \'99\' as type, 0 as parent FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? ORDER BY DS.name ASC';
        //$this->logger->error($this->userId);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId]);
        return $stmt->fetchAll();
    }

    /**
     * get shared datasets
     * @return
     */
    public function getSharedDataset($id)
    {
        $SQL = 'SELECT DS.* FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? AND DS.id = ?';
        //$this->logger->error($id);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $id]);
        return $stmt->fetch();
    }

    public function createShare($datasetId, $type, $uid_owner, $token)
    {
        $SQL = 'INSERT INTO `*PREFIX*analytics_share` (`dataset`,`type`,`uid_owner`,`uid_initiator`,`token`) VALUES(?,?,?,?,?)';
        //$this->logger->error($datasetId, $type, $uid_owner, $token);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($datasetId, $type, $uid_owner, $this->userId, $token));
        return true;
    }

    public function getShares($datasetId)
    {
        $SQL = 'SELECT id, type, uid_owner, token, (CASE  WHEN password IS NOT NULL THEN true ELSE false END) AS pass FROM `*PREFIX*analytics_share` WHERE uid_initiator = ? AND dataset = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $datasetId]);
        return $stmt->fetchAll();
    }

    public function getSharedReceiver($datasetId)
    {
        $SQL = 'SELECT uid_owner FROM `*PREFIX*analytics_share` WHERE uid_initiator = ? AND dataset = ? AND type = 0';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $datasetId]);
        return $stmt->fetchAll();
    }

    public function updateShare($shareId, $password)
    {
        $SQL = 'UPDATE `*PREFIX*analytics_share` SET `password`= ? WHERE `uid_initiator` = ? AND `id` = ?';
        //$this->logger->error($shareId. $password);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$password, $this->userId, $shareId]);
        return true;
    }

    public function deleteShare($shareId)
    {
        $SQL = 'DELETE FROM `*PREFIX*analytics_share` WHERE `uid_initiator` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $shareId]);
        return true;
    }

}


1			<?php
2			/**
3			* Data 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 Marcel Scherello
10			*/
11
12			namespace OCA\Analytics\Controller;
13
14			use OCP\AppFramework\Controller;
15			use OCP\IDbConnection;
16			use OCP\IL10N;
17			use OCP\ILogger;
18			use OCP\IRequest;
19
20			/**
21			* Controller class for main page.
22			*/
23			class DbController extends Controller
24			{
25
26			private $userId;
27			private $l10n;
28			private $db;
29			private $logger;
30
31			public function __construct(
32			$appName,
33			IRequest $request,
34			$userId,
35			IL10N $l10n,
36			IDbConnection $db,
37			ILogger $logger
38			)
39			{
40			parent::__construct($appName, $request);
41			$this->userId = $userId;
42			$this->l10n = $l10n;
43			$this->db = $db;
44			$this->logger = $logger;
45			}
46
47
48			/**
49			* truncates fiels do DB-field size
50			*
51			* @param $string
52			* @param $length
53			* @param $dots
54			* @return string
55			*/
56			private function truncate($string, $length, $dots = "...")
57			{
58			return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string;
59			}
60
61			/**
62			* Get file id for single track
63			* @param int $dataset
64			* @param $objectDrilldown
65			* @param $dateDrilldown
66			* @return array
67			*/
68			public function getData(int $dataset, $objectDrilldown = null, $dateDrilldown = null)
69			{
70			$SQL = 'SELECT';
71			if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
72			if ($dateDrilldown === 'true') $SQL .= ' `dimension2`,';
73			$SQL .= ' SUM(`dimension3`) AS `dimension3`';
74			$SQL .= ' FROM `PREFIXanalytics_facts`
75			WHERE `dataset` = ?
76			GROUP BY `dataset`';
77			if ($objectDrilldown === 'true') $SQL .= ', `dimension1`';
78			if ($dateDrilldown === 'true') $SQL .= ', `dimension2`';
79			$SQL .= ' ORDER BY';
80			if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
81			$SQL .= ' `dimension2` ASC';
82
83			//$this->logger->error($SQL);
84
85			$stmt = $this->db->prepare($SQL);
86			$stmt->execute(array($dataset));
87			return $stmt->fetchAll();
88			}
89
90			/**
91			* update data
92			*/
93			public function updateData()
94			{
95			}
96
97			/**
98			* delete all data of a dataset
99			*/
100			public function deleteDataByDataset(int $datasetId)
101			{
102			$SQL = 'DELETE FROM `PREFIXanalytics_facts` WHERE `user_id` = ? AND `dataset` = ?';
103			$stmt = $this->db->prepare($SQL);
104			$stmt->execute(array($this->userId, $datasetId));
105			return true;
106			}
107
108			/**
109			* create data
110			* @param int $datasetId
111			* @param $dimension1
112			* @param $dimension2
113			* @param $dimension3
114			* @return string
115			*/
116			public function createData(int $datasetId, $dimension1, $dimension2, $dimension3)
117			{
118			$SQL = 'SELECT `id` FROM `PREFIXanalytics_facts` WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
119			$stmt = $this->db->prepare($SQL);
120			$stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2));
121			$row = $stmt->fetch();
122			if ($row) {
123			$SQL = 'UPDATE `PREFIXanalytics_facts` SET `dimension3` = ? WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
124			$stmt = $this->db->prepare($SQL);
125			$stmt->execute(array($dimension3, $this->userId, $datasetId, $dimension1, $dimension2));
126			//$stmt->fetch();
127			return 'update';
128			} else {
129			$SQL = 'INSERT INTO `PREFIXanalytics_facts` (`user_id`,`dataset`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?)';
130			$stmt = $this->db->prepare($SQL);
131			$stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2, $dimension3));
132			return 'insert';
133			}
134			}
135
136			/**
137			* create dataset
138			*/
139			public function createDataset()
140			{
141			$SQL = 'INSERT INTO `PREFIXanalytics_dataset` (`user_id`,`name`,`type`,`parent`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?,?,?)';
142			//$this->logger->error($SQL);
143
144			$stmt = $this->db->prepare($SQL);
145			$stmt->execute(array($this->userId, $this->l10n->t('New'), 2, 0, $this->l10n->t('Objekt'), $this->l10n->t('Date'), $this->l10n->t('Value')));
146			return true;
147			}
148
149			/**
150			* update dataset
151			* @param $id
152			* @param $name
153			* @param $parent
154			* @param $type
155			* @param $link
156			* @param $visualization
157			* @param $chart
158			* @param $dimension1
159			* @param $dimension2
160			* @param $dimension3
161			* @return
162			*/
163			public function updateDataset($id, $name, $parent, $type, $link, $visualization, $chart, $dimension1, $dimension2, $dimension3)
164			{
165			$SQL = 'UPDATE `PREFIXanalytics_dataset` SET `name`= ?, `type`= ?, `link`= ?, `visualization`= ?, `chart`= ?, `parent`= ?, `dimension1` = ?, `dimension2` = ?, `dimension3` = ? WHERE `user_id` = ? AND `id` = ?';
166			$stmt = $this->db->prepare($SQL);
167			$name = $this->truncate($name, 64);
168			$stmt->execute(array($name, $type, $link, $visualization, $chart, $parent, $dimension1, $dimension2, $dimension3, $this->userId, $id));
169			return true;
170			}
171
172			/**
173			* delete dataset
174			* @param $id
175			* @return
176			*/
177			public function deleteDataset($id)
178			{
179			$SQL = 'DELETE FROM `PREFIXanalytics_dataset` WHERE `user_id` = ? AND `id` = ?';
180			$stmt = $this->db->prepare($SQL);
181			$stmt->execute(array($this->userId, $id));
182			return true;
183			}
184
185			/**
186			* get datasets
187			*/
188			public function getDatasets()
189			{
190			$SQL = 'SELECT id, name, type, parent FROM `PREFIXanalytics_dataset` WHERE `user_id` = ? ORDER BY `parent` ASC, `name` ASC';
191			//$this->logger->error($SQL);
192
193			$stmt = $this->db->prepare($SQL);
194			$stmt->execute(array($this->userId));
195			return $stmt->fetchAll();
196			}
197
198			/**
199			* get datasets
200			* @param $id
201			* @return
202			*/
203			public function getOwnDataset($id)
204			{
205			$SQL = 'SELECT * FROM `PREFIXanalytics_dataset` WHERE `id` = ? AND `user_id` = ?';
206			//$this->logger->error($SQL);
207			$stmt = $this->db->prepare($SQL);
208			$stmt->execute(array($id, $this->userId));
209			return $stmt->fetch();
210			}
211
212			/**
213			* get datasets
214			* @param $id
215			* @return
216			*/
217			public function getDatasetOptions($id)
218			{
219			$SQL = 'SELECT `name`, `visualization`, `chart` FROM `PREFIXanalytics_dataset` WHERE `id` = ?';
220			//$this->logger->error($SQL);
221
222			$stmt = $this->db->prepare($SQL);
223			$stmt->execute(array($id));
224			return $stmt->fetch();
225			}
226
227			// ******** SHARE **********
228			// ******** SHARE **********
229			// ******** SHARE **********
230
231			/**
232			* get datasets
233			* @param $token
234			* @return
235			*/
236			public function getDatasetByToken($token)
237			{
238			$SQL = 'SELECT DS., SH.password AS password FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.token = ?';
239			$stmt = $this->db->prepare($SQL);
240			$stmt->execute([$token]);
241			return $stmt->fetch();
242			//$this->logger->error($results['password']);
243			}
244
245			/**
246			* get shared datasets
247			* @return
248			*/
249			public function getSharedDatasets()
250			{
251			$SQL = 'SELECT DS.id, DS.name, \'99\' as type, 0 as parent FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIXanalytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? ORDER BY DS.name ASC';
252			//$this->logger->error($this->userId);
253			$stmt = $this->db->prepare($SQL);
254			$stmt->execute([$this->userId]);
255			return $stmt->fetchAll();
256			}
257
258			/**
259			* get shared datasets
260			* @return
261			*/
262			public function getSharedDataset($id)
263			{
264			$SQL = 'SELECT DS.* FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIXanalytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? AND DS.id = ?';
265			//$this->logger->error($id);
266			$stmt = $this->db->prepare($SQL);
267			$stmt->execute([$this->userId, $id]);
268			return $stmt->fetch();
269			}
270
271			public function createShare($datasetId, $type, $uid_owner, $token)
272			{
273			$SQL = 'INSERT INTO `PREFIXanalytics_share` (`dataset`,`type`,`uid_owner`,`uid_initiator`,`token`) VALUES(?,?,?,?,?)';
274			//$this->logger->error($datasetId, $type, $uid_owner, $token);
275			$stmt = $this->db->prepare($SQL);
276			$stmt->execute(array($datasetId, $type, $uid_owner, $this->userId, $token));
277			return true;
278			}
279
280			public function getShares($datasetId)
281			{
282			$SQL = 'SELECT id, type, uid_owner, token, (CASE WHEN password IS NOT NULL THEN true ELSE false END) AS pass FROM `PREFIXanalytics_share` WHERE uid_initiator = ? AND dataset = ?';
283			$stmt = $this->db->prepare($SQL);
284			$stmt->execute([$this->userId, $datasetId]);
285			return $stmt->fetchAll();
286			}
287
288			public function getSharedReceiver($datasetId)
289			{
290			$SQL = 'SELECT uid_owner FROM `PREFIXanalytics_share` WHERE uid_initiator = ? AND dataset = ? AND type = 0';
291			$stmt = $this->db->prepare($SQL);
292			$stmt->execute([$this->userId, $datasetId]);
293			return $stmt->fetchAll();
294			}
295
296			public function updateShare($shareId, $password)
297			{
298			$SQL = 'UPDATE `PREFIXanalytics_share` SET `password`= ? WHERE `uid_initiator` = ? AND `id` = ?';
299			//$this->logger->error($shareId. $password);
300			$stmt = $this->db->prepare($SQL);
301			$stmt->execute([$password, $this->userId, $shareId]);
302			return true;
303			}
304
305			public function deleteShare($shareId)
306			{
307			$SQL = 'DELETE FROM `PREFIXanalytics_share` WHERE `uid_initiator` = ? AND `id` = ?';
308			$stmt = $this->db->prepare($SQL);
309			$stmt->execute([$this->userId, $shareId]);
310			return true;
311			}
312
313			}
314

Rello / analytics

Push — master ( 5493ac...37f48a )

DbController::deleteDataByDataset() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like