QueryFavorite::getQueries() - Code Metrics - Inspection of "Validate user inputs for stored queries." - lagdo/jaxon-dbadmin - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 28b00c...2eb7a0 )

by Thierry

created 2025-09-30 16:15 UTC

QueryFavorite::getQueries() A

↳ Parent: QueryFavorite

Complexity

Conditions	4
Paths	6

Size

Total Lines	21
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	4
eloc	13
c	1
b	0
f	0
nc	6
nop	2
dl	0
loc	21
rs	9.8333

<?php

namespace Lagdo\DbAdmin\Service\DbAdmin;

use Lagdo\DbAdmin\Config\AuthInterface;
use Lagdo\DbAdmin\Driver\DriverInterface;
use Lagdo\Facades\Logger;

use function implode;

/**
 * SQL queries logging and storage.
 */
class QueryFavorite
{
    use ConnectionTrait;

    /**
     * @var bool
     */
    private bool $enabled;

    /**
     * @var int
     */
    private int $limit;

    /**
     * The constructor
     *
     * @param AuthInterface $auth
     * @param DriverInterface $driver
     * @param array $database
     * @param array $options
     */
    public function __construct(private AuthInterface $auth,
        private DriverInterface $driver, array $database, array $options)
    {
        $this->enabled = (bool)($options['enduser']['enabled'] ?? false);
        $this->limit = (int)($options['enduser']['limit'] ?? 15);
        if (!$this->enabled) {
            return;
        }

        // Connect to the logging database.
        $this->connect($driver, $database);
    }

    /**
     * @var string
     */
    protected function user(): string
    {
        return $this->auth->user();
    }

    /**
     * @param array $values
     *
     * @return bool
     */
    public function createQuery(array $values): bool
    {
        $values = [
            'title' => $values['title'],
            'query' => $values['query'],
            'driver' => $values['driver'],
            'last_update' => $this->currentTime(),
            'owner_id' => $this->getOwnerId(),
        ];
        $sql = "insert into dbadmin_stored_commands" .
            "(title,query,driver,last_update,owner_id) " .
            "values(:title,:query,:driver,:last_update,:owner_id)";
        $statement = $this->executeQuery($sql, $values);
        if ($statement !== false) {
            return true;
        }

        Logger::warning('Unable to save command in the query logging database.', [
            'error' => $this->connection->error(),
        ]);
        return false;
    }

    /**
     * @param int $queryId
     * @param array $values
     *
     * @return bool
     */
    public function updateQuery(int $queryId, array $values): bool
    {
        $values = [
            'title' => $values['title'],
            'query' => $values['query'],
            'driver' => $values['driver'],
            'last_update' => $this->currentTime(),
            'owner_id' => $this->getOwnerId(),
            'query_id' => $queryId,
        ];
        $sql = "update dbadmin_stored_commands set title=:title," .
            "query=:query,driver=:driver,last_update=:last_update " .
            "where id=:query_id and owner_id=:owner_id";
        $statement = $this->executeQuery($sql, $values);
        if ($statement !== false) {
            return true;
        }

        Logger::warning('Unable to save command in the query logging database.', [
            'error' => $this->connection->error(),
        ]);
        return false;
    }

    /**
     * @param int $queryId
     *
     * @return bool
     */
    public function deleteQuery(int $queryId): bool
    {
        $values = [
            'owner_id' => $this->getOwnerId(),
            'query_id' => $queryId,
        ];
        $sql = "delete from dbadmin_stored_commands where " .
            "id=:query_id and owner_id=:owner_id";
        $statement = $this->executeQuery($sql, $values);
        if ($statement !== false) {
            return true;
        }

        Logger::warning('Unable to save command in the query logging database.', [
            'error' => $this->connection->error(),
        ]);
        return false;
    }

    /**
     * @return int
     */
    public function getLimit(): int
    {
        return $this->limit;
    }

    /**
     * @param array $filters
     *
     * @return array
     */
    private function getWhereClause(array $filters): array
    {
        $values = [
            'owner_id' => $this->getOwnerId(),
        ];
        $clauses = ['c.owner_id=:owner_id'];
        if (isset($filters['title'])) {
            $values['title'] = "%{$filters['title']}%";
            $clauses[] = "c.title like :title";
        }
        if (isset($filters['driver'])) {
            $values['driver'] = $filters['driver'];
            $clauses[] = "c.driver=:driver";
        }
        if (isset($filters['from'])) {
            $values['from'] = $filters['from'];
            $clauses[] = "c.last_update>=:from";
        }
        if (isset($filters['to'])) {
            $values['to'] = $filters['to'];
            $clauses[] = "c.last_update<=:to";
        }
        return [$values, 'where ' . implode(' and ', $clauses)];
    }

    /**
     * @param array $filters
     *
     * @return int
     */
    public function getQueryCount(array $filters): int
    {
        [$values, $whereClause] = $this->getWhereClause($filters);
        $sql = "select count(*) as cnt from dbadmin_stored_commands c $whereClause";
        $statement = $this->executeQuery($sql, $values);
        return !$statement || !($row = $statement->fetchAssoc()) ? 0 : $row['cnt'];
    }

    /**
     * @param array $filters
     * @param int $page
     *
     * @return array
     */
    public function getQueries(array $filters, int $page): array
    {
        [$values, $whereClause] = $this->getWhereClause($filters);
        $offsetClause = $page > 1 ? 'offset ' . ($page - 1) * $this->limit : '';
        // PostgreSQL doesn't allow the use of distinct and order by
        // a field not in the select clause in the same SQL query.
        $sql = "select c.* from dbadmin_stored_commands c $whereClause " .
            "order by c.last_update desc,c.id desc limit {$this->limit} $offsetClause";
        $statement = $this->executeQuery($sql, $values);
        if ($statement !== false) {
            $commands = [];
            while (($row = $statement->fetchAssoc())) {
                $commands[] = $row;
            }
            return $commands;
        }

        Logger::warning('Unable to read commands from the query logging database.', [
            'error' => $this->connection->error(),
        ]);
        return [];
    }

    /**
     * @param int $queryId
     *
     * @return array|null
     */
    public function getQuery(int $queryId): ?array
    {
        $values = [
            'query_id' => $queryId,
            'owner_id' => $this->getOwnerId(),
        ];
        $sql = "select c.* from dbadmin_stored_commands c where " .
            "id=:query_id and owner_id=:owner_id";
        $statement = $this->executeQuery($sql, $values);
        return !$statement ? null : $statement->fetchAssoc();
    }
}


1			<?php
2
3			namespace Lagdo\DbAdmin\Service\DbAdmin;
4
5			use Lagdo\DbAdmin\Config\AuthInterface;
6			use Lagdo\DbAdmin\Driver\DriverInterface;
7			use Lagdo\Facades\Logger;
8
9			use function implode;
10
11			/**
12			* SQL queries logging and storage.
13			*/
14			class QueryFavorite
15			{
16			use ConnectionTrait;
17
18			/**
19			* @var bool
20			*/
21			private bool $enabled;
22
23			/**
24			* @var int
25			*/
26			private int $limit;
27
28			/**
29			* The constructor
30			*
31			* @param AuthInterface $auth
32			* @param DriverInterface $driver
33			* @param array $database
34			* @param array $options
35			*/
36			public function __construct(private AuthInterface $auth,
37			private DriverInterface $driver, array $database, array $options)
38			{
39			$this->enabled = (bool)($options['enduser']['enabled'] ?? false);
40			$this->limit = (int)($options['enduser']['limit'] ?? 15);
41			if (!$this->enabled) {
42			return;
43			}
44
45			// Connect to the logging database.
46			$this->connect($driver, $database);
47			}
48
49			/**
50			* @var string
51			*/
52			protected function user(): string
53			{
54			return $this->auth->user();
55			}
56
57			/**
58			* @param array $values
59			*
60			* @return bool
61			*/
62			public function createQuery(array $values): bool
63			{
64			$values = [
65			'title' => $values['title'],
66			'query' => $values['query'],
67			'driver' => $values['driver'],
68			'last_update' => $this->currentTime(),
69			'owner_id' => $this->getOwnerId(),
70			];
71			$sql = "insert into dbadmin_stored_commands" .
72			"(title,query,driver,last_update,owner_id) " .
73			"values(:title,:query,:driver,:last_update,:owner_id)";
74			$statement = $this->executeQuery($sql, $values);
75			if ($statement !== false) {
76			return true;
77			}
78
79			Logger::warning('Unable to save command in the query logging database.', [
80			'error' => $this->connection->error(),
81			]);
82			return false;
83			}
84
85			/**
86			* @param int $queryId
87			* @param array $values
88			*
89			* @return bool
90			*/
91			public function updateQuery(int $queryId, array $values): bool
92			{
93			$values = [
94			'title' => $values['title'],
95			'query' => $values['query'],
96			'driver' => $values['driver'],
97			'last_update' => $this->currentTime(),
98			'owner_id' => $this->getOwnerId(),
99			'query_id' => $queryId,
100			];
101			$sql = "update dbadmin_stored_commands set title=:title," .
102			"query=:query,driver=:driver,last_update=:last_update " .
103			"where id=:query_id and owner_id=:owner_id";
104			$statement = $this->executeQuery($sql, $values);
105			if ($statement !== false) {
106			return true;
107			}
108
109			Logger::warning('Unable to save command in the query logging database.', [
110			'error' => $this->connection->error(),
111			]);
112			return false;
113			}
114
115			/**
116			* @param int $queryId
117			*
118			* @return bool
119			*/
120			public function deleteQuery(int $queryId): bool
121			{
122			$values = [
123			'owner_id' => $this->getOwnerId(),
124			'query_id' => $queryId,
125			];
126			$sql = "delete from dbadmin_stored_commands where " .
127			"id=:query_id and owner_id=:owner_id";
128			$statement = $this->executeQuery($sql, $values);
129			if ($statement !== false) {
130			return true;
131			}
132
133			Logger::warning('Unable to save command in the query logging database.', [
134			'error' => $this->connection->error(),
135			]);
136			return false;
137			}
138
139			/**
140			* @return int
141			*/
142			public function getLimit(): int
143			{
144			return $this->limit;
145			}
146
147			/**
148			* @param array $filters
149			*
150			* @return array
151			*/
152			private function getWhereClause(array $filters): array
153			{
154			$values = [
155			'owner_id' => $this->getOwnerId(),
156			];
157			$clauses = ['c.owner_id=:owner_id'];
158			if (isset($filters['title'])) {
159			$values['title'] = "%{$filters['title']}%";
160			$clauses[] = "c.title like :title";
161			}
162			if (isset($filters['driver'])) {
163			$values['driver'] = $filters['driver'];
164			$clauses[] = "c.driver=:driver";
165			}
166			if (isset($filters['from'])) {
167			$values['from'] = $filters['from'];
168			$clauses[] = "c.last_update>=:from";
169			}
170			if (isset($filters['to'])) {
171			$values['to'] = $filters['to'];
172			$clauses[] = "c.last_update<=:to";
173			}
174			return [$values, 'where ' . implode(' and ', $clauses)];
175			}
176
177			/**
178			* @param array $filters
179			*
180			* @return int
181			*/
182			public function getQueryCount(array $filters): int
183			{
184			[$values, $whereClause] = $this->getWhereClause($filters);
185			$sql = "select count(*) as cnt from dbadmin_stored_commands c $whereClause";
186			$statement = $this->executeQuery($sql, $values);
187			return !$statement \|\| !($row = $statement->fetchAssoc()) ? 0 : $row['cnt'];
188			}
189
190			/**
191			* @param array $filters
192			* @param int $page
193			*
194			* @return array
195			*/
196			public function getQueries(array $filters, int $page): array
197			{
198			[$values, $whereClause] = $this->getWhereClause($filters);
199			$offsetClause = $page > 1 ? 'offset ' . ($page - 1) * $this->limit : '';
200			// PostgreSQL doesn't allow the use of distinct and order by
201			// a field not in the select clause in the same SQL query.
202			$sql = "select c.* from dbadmin_stored_commands c $whereClause " .
203			"order by c.last_update desc,c.id desc limit {$this->limit} $offsetClause";
204			$statement = $this->executeQuery($sql, $values);
205			if ($statement !== false) {
206			$commands = [];
207			while (($row = $statement->fetchAssoc())) {
208			$commands[] = $row;
209			}
210			return $commands;
211			}
212
213			Logger::warning('Unable to read commands from the query logging database.', [
214			'error' => $this->connection->error(),
215			]);
216			return [];
217			}
218
219			/**
220			* @param int $queryId
221			*
222			* @return array\|null
223			*/
224			public function getQuery(int $queryId): ?array
225			{
226			$values = [
227			'query_id' => $queryId,
228			'owner_id' => $this->getOwnerId(),
229			];
230			$sql = "select c.* from dbadmin_stored_commands c where " .
231			"id=:query_id and owner_id=:owner_id";
232			$statement = $this->executeQuery($sql, $values);
233			return !$statement ? null : $statement->fetchAssoc();
234			}
235			}
236

lagdo / jaxon-dbadmin

Push — main ( 28b00c...2eb7a0 )

QueryFavorite::getQueries() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like