CleanupCommand::getQueries() - Code Metrics - Inspection of "Feature/redis di" - php-telegram-bot/core - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — develop (#1493)

by Rabie

created 2025-07-16 11:44 UTC

CleanupCommand::getQueries() C

↳ Parent: CleanupCommand

Complexity

Conditions	10
Paths	129

Size

Total Lines	201
Code Lines	169

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	110

Importance

Changes	4
Bugs	1	Features	0

Metric	Value
cc	10
eloc	169
c	4
b	1
f	0
nc	129
nop	1
dl	0
loc	201
rs	5.94
ccs	0
cts	79
cp	0
crap	110

How to fix Long Method Complexity

<?php

/**
 * This file is part of the TelegramBot package.
 *
 * (c) Avtandil Kikabidze aka LONGMAN <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Longman\TelegramBot\Commands\AdminCommands;

use Longman\TelegramBot\Commands\AdminCommand;
use Longman\TelegramBot\Entities\ServerResponse;
use Longman\TelegramBot\Exception\TelegramException;
use Longman\TelegramBot\Request;
use Longman\TelegramBot\TelegramLog;
use PDOException;

/**
 * User "/cleanup" command
 *
 * Configuration options:
 *
 * $telegram->setCommandConfig('cleanup', [
 *     // Define which tables should be cleaned.
 *     'tables_to_clean' => [
 *         'message',
 *         'edited_message',
 *     ],
 *     // Define how old cleaned entries should be.
 *     'clean_older_than' => [
 *         'message'        => '7 days',
 *         'edited_message' => '30 days',
 *     ]
 * );
 */
class CleanupCommand extends AdminCommand
{
    /**
     * @var string
     */
    protected $name = 'cleanup';

    /**
     * @var string
     */
    protected $description = 'Clean up the database from old records';

    /**
     * @var string
     */
    protected $usage = '/cleanup [dry] <days> or /cleanup [dry] <count> <unit> (e.g. 3 weeks)';

    /**
     * @var string
     */
    protected $version = '1.1.0';

    /**
     * @var bool
     */
    protected $need_mysql = true;

    /**
     * Default tables to clean, cleaning 'chat', 'user' and 'user_chat' by default is bad practice!
     *
     * @var array
     */
    protected static $default_tables_to_clean = [
        'callback_query',
        'chosen_inline_result',
        'conversation',
        'edited_message',
        'inline_query',
        'message',
        'request_limiter',
        'telegram_update',
    ];

    /**
     * By default, remove records older than X days/hours/anything from these tables.
     *
     * @var array
     */
    protected static $default_clean_older_than = [
        'callback_query'       => '30 days',
        'chat'                 => '365 days',
        'chosen_inline_result' => '30 days',
        'conversation'         => '90 days',
        'edited_message'       => '30 days',
        'inline_query'         => '30 days',
        'message'              => '30 days',
        'poll'                 => '90 days',
        'request_limiter'      => '1 minute',
        'shipping_query'       => '90 days',
        'telegram_update'      => '30 days',
        'user'                 => '365 days',
        'user_chat'            => '365 days',
    ];

    /**
     * Set command config
     *
     * @param string $custom_time
     *
     * @return array
     */
    private function getSettings($custom_time = ''): array

    {
        $tables_to_clean      = self::$default_tables_to_clean;
        $user_tables_to_clean = $this->getConfig('tables_to_clean');
        if (is_array($user_tables_to_clean)) {
            $tables_to_clean = $user_tables_to_clean;
        }

        $clean_older_than      = self::$default_clean_older_than;
        $user_clean_older_than = $this->getConfig('clean_older_than');
        if (is_array($user_clean_older_than)) {
            $clean_older_than = array_merge($clean_older_than, $user_clean_older_than);
        }

        // Convert numeric-only values to days.
        array_walk($clean_older_than, function (&$time) use ($custom_time) {
            if (!empty($custom_time)) {
                $time = $custom_time;
            }
            if (is_numeric($time)) {
                $time .= ' days';
            }
        });

        return compact('tables_to_clean', 'clean_older_than');
    }

    /**
     * Get SQL queries array based on settings provided
     *
     * @param $settings
     *
     * @return array
     * @throws TelegramException
     */
    private function getQueries($settings): array

    {
        if (empty($settings) || !is_array($settings)) {
            throw new TelegramException('Settings variable is not an array or is empty!');
        }

        // Convert all clean_older_than times to correct format.
        $clean_older_than = $settings['clean_older_than'];
        foreach ($clean_older_than as $table => $time) {
            $clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time));
        }
        $tables_to_clean = $settings['tables_to_clean'];

        $queries = [];

        if (in_array('telegram_update', $tables_to_clean, true)) {
            $queries[] = sprintf(
                'DELETE FROM `%3$s`
                WHERE id IN (
                    SELECT id FROM (
                        SELECT id FROM `%3$s`
                        WHERE `id` != \'%1$s\'
                        AND `chat_id` NOT IN (
                          SELECT `id`
                          FROM `%4$s`
                          WHERE `%3$s`.`chat_id` = `id`
                          AND `updated_at` < \'%2$s\'
                        )
                        AND (
                          `message_id` IS NOT NULL
                          AND `message_id` IN (
                            SELECT `id`
                            FROM `%5$s`
                            WHERE `date` < \'%2$s\'
                          )
                        )
                        OR (
                          `edited_message_id` IS NOT NULL
                          AND `edited_message_id` IN (
                            SELECT `id`
                            FROM `%6$s`
                            WHERE `edit_date` < \'%2$s\'
                          )
                        )
                        OR (
                          `inline_query_id` IS NOT NULL
                          AND `inline_query_id` IN (
                            SELECT `id`
                            FROM `%7$s`
                            WHERE `created_at` < \'%2$s\'
                          )
                        )
                        OR (
                          `chosen_inline_result_id` IS NOT NULL
                          AND `chosen_inline_result_id` IN (
                            SELECT `id`
                            FROM `%8$s`
                            WHERE `created_at` < \'%2$s\'
                          )
                        )
                        OR (
                          `callback_query_id` IS NOT NULL
                          AND `callback_query_id` IN (
                            SELECT `id`
                            FROM `%9$s`
                            WHERE `created_at` < \'%2$s\'
                          )
                        )
                    ) a
                )
            ',
                $this->getUpdate()->getUpdateId(),
                $clean_older_than['telegram_update'],
                TB_TELEGRAM_UPDATE,
                TB_CHAT,
                TB_MESSAGE,
                TB_EDITED_MESSAGE,
                TB_INLINE_QUERY,
                TB_CHOSEN_INLINE_RESULT,
                TB_CALLBACK_QUERY
            );
        }

        if (in_array('user_chat', $tables_to_clean, true)) {
            $queries[] = sprintf(
                'DELETE FROM `%1$s`
                WHERE `user_id` IN (
                  SELECT `id`
                  FROM `%2$s`
                  WHERE `updated_at` < \'%3$s\'
                )
            ',
                TB_USER_CHAT,
                TB_USER,
                $clean_older_than['chat']
            );
        }

        // Simple.
        $simple_tables = [
            'user'            => ['table' => TB_USER, 'field' => 'updated_at'],
            'chat'            => ['table' => TB_CHAT, 'field' => 'updated_at'],
            'conversation'    => ['table' => TB_CONVERSATION, 'field' => 'updated_at'],
            'poll'            => ['table' => TB_POLL, 'field' => 'created_at'],
            'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'],
            'shipping_query'  => ['table' => TB_SHIPPING_QUERY, 'field' => 'created_at'],
        ];

        foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) {
            $queries[] = sprintf(
                'DELETE FROM `%1$s`
                WHERE `%2$s` < \'%3$s\'
            ',
                $simple_tables[$table_to_clean]['table'],
                $simple_tables[$table_to_clean]['field'],
                $clean_older_than[$table_to_clean]
            );
        }

        // Queries.
        $query_tables = [
            'inline_query'         => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'],
            'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'],
            'callback_query'       => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'],
        ];
        foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) {
            $queries[] = sprintf(
                'DELETE FROM `%1$s`
                WHERE `%2$s` < \'%3$s\'
                  AND `id` NOT IN (
                    SELECT `%4$s`
                    FROM `%5$s`
                    WHERE `%4$s` = `%1$s`.`id`
                  )
            ',
                $query_tables[$table_to_clean]['table'],
                $query_tables[$table_to_clean]['field'],
                $clean_older_than[$table_to_clean],
                $table_to_clean . '_id',
                TB_TELEGRAM_UPDATE
            );
        }

        // Messages
        if (in_array('edited_message', $tables_to_clean, true)) {
            $queries[] = sprintf(
                'DELETE FROM `%1$s`
                WHERE `edit_date` < \'%2$s\'
                  AND `id` NOT IN (
                    SELECT `message_id`
                    FROM `%3$s`
                    WHERE `edited_message_id` = `%1$s`.`id`
                  )
            ',
                TB_EDITED_MESSAGE,
                $clean_older_than['edited_message'],
                TB_TELEGRAM_UPDATE
            );
        }

        if (in_array('message', $tables_to_clean, true)) {
            $queries[] = sprintf(
                'DELETE FROM `%1$s`
                WHERE id IN (
                    SELECT id
                    FROM (
                        SELECT id
                        FROM  `%1$s`
                        WHERE `date` < \'%2$s\'
                          AND `id` NOT IN (
                            SELECT `message_id`
                            FROM `%3$s`
                            WHERE `message_id` = `%1$s`.`id`
                          )
                          AND `id` NOT IN (
                            SELECT `message_id`
                            FROM `%4$s`
                            WHERE `message_id` = `%1$s`.`id`
                          )
                          AND `id` NOT IN (
                            SELECT `message_id`
                            FROM `%5$s`
                            WHERE `message_id` = `%1$s`.`id`
                          )
                          AND `id` NOT IN (
                            SELECT a.`reply_to_message` FROM `%1$s` a
                            INNER JOIN `%1$s` b ON b.`id` = a.`reply_to_message` AND b.`chat_id` = a.`reply_to_chat`
                          )
                        ORDER BY `id` DESC
                     ) a
                 )
            ',
                TB_MESSAGE,
                $clean_older_than['message'],
                TB_EDITED_MESSAGE,
                TB_TELEGRAM_UPDATE,
                TB_CALLBACK_QUERY
            );
        }

        return $queries;
    }

    /**
     * Execution if MySQL is required but not available
     *
     * @return ServerResponse
     * @throws TelegramException
     */
    public function executeNoDb(): ServerResponse
    {
        return $this->replyToChat('*No database connection!*', ['parse_mode' => 'Markdown']);
    }

    /**
     * Command execute method
     *
     * @return ServerResponse
     * @throws TelegramException
     */
    public function execute(): ServerResponse
    {
        return $this->replyToChat('This command is not available because the database feature has been removed.' . PHP_EOL . 'Type /debug to see all available information.');
    }
}


1			<?php
2
3			/**
4			* This file is part of the TelegramBot package.
5			*
6			* (c) Avtandil Kikabidze aka LONGMAN <[email protected]>
7			*
8			* For the full copyright and license information, please view the LICENSE
9			* file that was distributed with this source code.
10			*/
11
12			namespace Longman\TelegramBot\Commands\AdminCommands;
13
14			use Longman\TelegramBot\Commands\AdminCommand;
15			use Longman\TelegramBot\Entities\ServerResponse;
16			use Longman\TelegramBot\Exception\TelegramException;
17			use Longman\TelegramBot\Request;
18			use Longman\TelegramBot\TelegramLog;
19			use PDOException;
20
21			/**
22			* User "/cleanup" command
23			*
24			* Configuration options:
25			*
26			* $telegram->setCommandConfig('cleanup', [
27			* // Define which tables should be cleaned.
28			* 'tables_to_clean' => [
29			* 'message',
30			* 'edited_message',
31			* ],
32			* // Define how old cleaned entries should be.
33			* 'clean_older_than' => [
34			* 'message' => '7 days',
35			* 'edited_message' => '30 days',
36			* ]
37			* );
38			*/
39			class CleanupCommand extends AdminCommand
40			{
41			/**
42			* @var string
43			*/
44			protected $name = 'cleanup';
45
46			/**
47			* @var string
48			*/
49			protected $description = 'Clean up the database from old records';
50
51			/**
52			* @var string
53			*/
54			protected $usage = '/cleanup [dry] <days> or /cleanup [dry] <count> <unit> (e.g. 3 weeks)';
55
56			/**
57			* @var string
58			*/
59			protected $version = '1.1.0';
60
61			/**
62			* @var bool
63			*/
64			protected $need_mysql = true;
65
66			/**
67			* Default tables to clean, cleaning 'chat', 'user' and 'user_chat' by default is bad practice!
68			*
69			* @var array
70			*/
71			protected static $default_tables_to_clean = [
72			'callback_query',
73			'chosen_inline_result',
74			'conversation',
75			'edited_message',
76			'inline_query',
77			'message',
78			'request_limiter',
79			'telegram_update',
80			];
81
82			/**
83			* By default, remove records older than X days/hours/anything from these tables.
84			*
85			* @var array
86			*/
87			protected static $default_clean_older_than = [
88			'callback_query' => '30 days',
89			'chat' => '365 days',
90			'chosen_inline_result' => '30 days',
91			'conversation' => '90 days',
92			'edited_message' => '30 days',
93			'inline_query' => '30 days',
94			'message' => '30 days',
95			'poll' => '90 days',
96			'request_limiter' => '1 minute',
97			'shipping_query' => '90 days',
98			'telegram_update' => '30 days',
99			'user' => '365 days',
100			'user_chat' => '365 days',
101			];
102
103			/**
104			* Set command config
105			*
106			* @param string $custom_time
107			*
108			* @return array
109			*/
110			private function getSettings($custom_time = ''): array
			0 ignored issues – show Unused Code introduced 2025-07-06 22:01 UTC by Report Bug Copy Issue Report The method `getSettings()` is not used, and could be removed. This check looks for private methods that have been defined, but are not used inside the class. Loading history...
111			{
112			$tables_to_clean = self::$default_tables_to_clean;
113			$user_tables_to_clean = $this->getConfig('tables_to_clean');
114			if (is_array($user_tables_to_clean)) {
115			$tables_to_clean = $user_tables_to_clean;
116			}
117
118			$clean_older_than = self::$default_clean_older_than;
119			$user_clean_older_than = $this->getConfig('clean_older_than');
120			if (is_array($user_clean_older_than)) {
121			$clean_older_than = array_merge($clean_older_than, $user_clean_older_than);
122			}
123
124			// Convert numeric-only values to days.
125			array_walk($clean_older_than, function (&$time) use ($custom_time) {
126			if (!empty($custom_time)) {
127			$time = $custom_time;
128			}
129			if (is_numeric($time)) {
130			$time .= ' days';
131			}
132			});
133
134			return compact('tables_to_clean', 'clean_older_than');
135			}
136
137			/**
138			* Get SQL queries array based on settings provided
139			*
140			* @param $settings
141			*
142			* @return array
143			* @throws TelegramException
144			*/
145			private function getQueries($settings): array
			0 ignored issues – show Unused Code introduced 2025-07-06 22:01 UTC by Report Bug Copy Issue Report The method `getQueries()` is not used, and could be removed. This check looks for private methods that have been defined, but are not used inside the class. Loading history...
146			{
147			if (empty($settings) \|\| !is_array($settings)) {
148			throw new TelegramException('Settings variable is not an array or is empty!');
149			}
150
151			// Convert all clean_older_than times to correct format.
152			$clean_older_than = $settings['clean_older_than'];
153			foreach ($clean_older_than as $table => $time) {
154			$clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time));
155			}
156			$tables_to_clean = $settings['tables_to_clean'];
157
158			$queries = [];
159
160			if (in_array('telegram_update', $tables_to_clean, true)) {
161			$queries[] = sprintf(
162			'DELETE FROM `%3$s`
163			WHERE id IN (
164			SELECT id FROM (
165			SELECT id FROM `%3$s`
166			WHERE `id` != \'%1$s\'
167			AND `chat_id` NOT IN (
168			SELECT `id`
169			FROM `%4$s`
170			WHERE `%3$s`.`chat_id` = `id`
171			AND `updated_at` < \'%2$s\'
172			)
173			AND (
174			`message_id` IS NOT NULL
175			AND `message_id` IN (
176			SELECT `id`
177			FROM `%5$s`
178			WHERE `date` < \'%2$s\'
179			)
180			)
181			OR (
182			`edited_message_id` IS NOT NULL
183			AND `edited_message_id` IN (
184			SELECT `id`
185			FROM `%6$s`
186			WHERE `edit_date` < \'%2$s\'
187			)
188			)
189			OR (
190			`inline_query_id` IS NOT NULL
191			AND `inline_query_id` IN (
192			SELECT `id`
193			FROM `%7$s`
194			WHERE `created_at` < \'%2$s\'
195			)
196			)
197			OR (
198			`chosen_inline_result_id` IS NOT NULL
199			AND `chosen_inline_result_id` IN (
200			SELECT `id`
201			FROM `%8$s`
202			WHERE `created_at` < \'%2$s\'
203			)
204			)
205			OR (
206			`callback_query_id` IS NOT NULL
207			AND `callback_query_id` IN (
208			SELECT `id`
209			FROM `%9$s`
210			WHERE `created_at` < \'%2$s\'
211			)
212			)
213			) a
214			)
215			',
216			$this->getUpdate()->getUpdateId(),
217			$clean_older_than['telegram_update'],
218			TB_TELEGRAM_UPDATE,
219			TB_CHAT,
220			TB_MESSAGE,
221			TB_EDITED_MESSAGE,
222			TB_INLINE_QUERY,
223			TB_CHOSEN_INLINE_RESULT,
224			TB_CALLBACK_QUERY
225			);
226			}
227
228			if (in_array('user_chat', $tables_to_clean, true)) {
229			$queries[] = sprintf(
230			'DELETE FROM `%1$s`
231			WHERE `user_id` IN (
232			SELECT `id`
233			FROM `%2$s`
234			WHERE `updated_at` < \'%3$s\'
235			)
236			',
237			TB_USER_CHAT,
238			TB_USER,
239			$clean_older_than['chat']
240			);
241			}
242
243			// Simple.
244			$simple_tables = [
245			'user' => ['table' => TB_USER, 'field' => 'updated_at'],
246			'chat' => ['table' => TB_CHAT, 'field' => 'updated_at'],
247			'conversation' => ['table' => TB_CONVERSATION, 'field' => 'updated_at'],
248			'poll' => ['table' => TB_POLL, 'field' => 'created_at'],
249			'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'],
250			'shipping_query' => ['table' => TB_SHIPPING_QUERY, 'field' => 'created_at'],
251			];
252
253			foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) {
254			$queries[] = sprintf(
255			'DELETE FROM `%1$s`
256			WHERE `%2$s` < \'%3$s\'
257			',
258			$simple_tables[$table_to_clean]['table'],
259			$simple_tables[$table_to_clean]['field'],
260			$clean_older_than[$table_to_clean]
261			);
262			}
263
264			// Queries.
265			$query_tables = [
266			'inline_query' => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'],
267			'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'],
268			'callback_query' => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'],
269			];
270			foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) {
271			$queries[] = sprintf(
272			'DELETE FROM `%1$s`
273			WHERE `%2$s` < \'%3$s\'
274			AND `id` NOT IN (
275			SELECT `%4$s`
276			FROM `%5$s`
277			WHERE `%4$s` = `%1$s`.`id`
278			)
279			',
280			$query_tables[$table_to_clean]['table'],
281			$query_tables[$table_to_clean]['field'],
282			$clean_older_than[$table_to_clean],
283			$table_to_clean . '_id',
284			TB_TELEGRAM_UPDATE
285			);
286			}
287
288			// Messages
289			if (in_array('edited_message', $tables_to_clean, true)) {
290			$queries[] = sprintf(
291			'DELETE FROM `%1$s`
292			WHERE `edit_date` < \'%2$s\'
293			AND `id` NOT IN (
294			SELECT `message_id`
295			FROM `%3$s`
296			WHERE `edited_message_id` = `%1$s`.`id`
297			)
298			',
299			TB_EDITED_MESSAGE,
300			$clean_older_than['edited_message'],
301			TB_TELEGRAM_UPDATE
302			);
303			}
304
305			if (in_array('message', $tables_to_clean, true)) {
306			$queries[] = sprintf(
307			'DELETE FROM `%1$s`
308			WHERE id IN (
309			SELECT id
310			FROM (
311			SELECT id
312			FROM `%1$s`
313			WHERE `date` < \'%2$s\'
314			AND `id` NOT IN (
315			SELECT `message_id`
316			FROM `%3$s`
317			WHERE `message_id` = `%1$s`.`id`
318			)
319			AND `id` NOT IN (
320			SELECT `message_id`
321			FROM `%4$s`
322			WHERE `message_id` = `%1$s`.`id`
323			)
324			AND `id` NOT IN (
325			SELECT `message_id`
326			FROM `%5$s`
327			WHERE `message_id` = `%1$s`.`id`
328			)
329			AND `id` NOT IN (
330			SELECT a.`reply_to_message` FROM `%1$s` a
331			INNER JOIN `%1$s` b ON b.`id` = a.`reply_to_message` AND b.`chat_id` = a.`reply_to_chat`
332			)
333			ORDER BY `id` DESC
334			) a
335			)
336			',
337			TB_MESSAGE,
338			$clean_older_than['message'],
339			TB_EDITED_MESSAGE,
340			TB_TELEGRAM_UPDATE,
341			TB_CALLBACK_QUERY
342			);
343			}
344
345			return $queries;
346			}
347
348			/**
349			* Execution if MySQL is required but not available
350			*
351			* @return ServerResponse
352			* @throws TelegramException
353			*/
354			public function executeNoDb(): ServerResponse
355			{
356			return $this->replyToChat('No database connection!', ['parse_mode' => 'Markdown']);
357			}
358
359			/**
360			* Command execute method
361			*
362			* @return ServerResponse
363			* @throws TelegramException
364			*/
365			public function execute(): ServerResponse
366			{
367			return $this->replyToChat('This command is not available because the database feature has been removed.' . PHP_EOL . 'Type /debug to see all available information.');
368			}
369			}
370

php-telegram-bot / core

Pull Request — develop (#1493)

CleanupCommand::getQueries() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like