CleanupCommand::getQueries() - Code Metrics - Inspection of "WIP - #1048: Better namespace" - php-telegram-bot/core - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — develop (#1077)

by Marco

created 2020-05-24 18:11 UTC

CleanupCommand::getQueries() C

↳ Parent: CleanupCommand

Complexity

Conditions	10
Paths	129

Size

Total Lines	196
Code Lines	164

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	110

Importance

Changes

Metric	Value
eloc	164
dl	0
loc	196
ccs	0
cts	182
cp	0
rs	5.94
c	0
b	0
f	0
cc	10
nc	129
nop	1
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 PhpTelegramBot\Core\Commands\AdminCommands;

use PhpTelegramBot\Core\Commands\AdminCommand;
use PhpTelegramBot\Core\DB;
use PhpTelegramBot\Core\Entities\ServerResponse;
use PhpTelegramBot\Core\Exception\TelegramException;
use PhpTelegramBot\Core\Request;
use PhpTelegramBot\Core\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 = '')
    {
        $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)
    {
        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` != \'%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\'
                    )
                  )
            ',
                $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()
    {
        return $this->replyToChat('*No database connection!*', ['parse_mode' => 'Markdown']);
    }

    /**
     * Command execute method
     *
     * @return ServerResponse
     * @throws TelegramException
     */
    public function execute()
    {
        $message = $this->getMessage();
        $text    = $message->getText(true);

        // Dry run?
        $dry_run = strpos($text, 'dry') !== false;
        $text    = trim(str_replace('dry', '', $text));

        $settings = $this->getSettings($text);
        $queries  = $this->getQueries($settings);

        if ($dry_run) {
            return $this->replyToUser('Queries:' . PHP_EOL . implode(PHP_EOL, $queries));
        }

        $infos = [];
        foreach ($settings['tables_to_clean'] as $table) {
            $info = "*{$table}*";

            if (isset($settings['clean_older_than'][$table])) {
                $info .= " ({$settings['clean_older_than'][$table]})";
            }

            $infos[] = $info;
        }

        $data = [
            'chat_id'    => $message->getFrom()->getId(),
            'parse_mode' => 'Markdown',
        ];

        $data['text'] = 'Cleaning up tables:' . PHP_EOL . implode(PHP_EOL, $infos);
        Request::sendMessage($data);

        $rows = 0;
        $pdo  = DB::getPdo();
        try {
            $pdo->beginTransaction();

            foreach ($queries as $query) {
                // Delete in chunks to not block / improve speed on big tables.
                $query .= ' LIMIT 10000';
                while ($dbq = $pdo->query($query)) {
                    if ($dbq->rowCount() === 0) {
                        continue 2;
                    }
                    $rows += $dbq->rowCount();
                }

                TelegramLog::error('Error while executing query: ' . $query);
            }

            // commit changes to the database and end transaction
            $pdo->commit();

            $data['text'] = "*Database cleanup done!* _(removed {$rows} rows)_";
        } catch (PDOException $e) {
            $data['text'] = '*Database cleanup failed!* _(check your error logs)_';

            // rollback changes on exception
            // useful if you want to track down error you can't replicate it when some of the data is already deleted
            $pdo->rollBack();

            TelegramLog::error($e->getMessage());
        }

        return Request::sendMessage($data);
    }
}


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 PhpTelegramBot\Core\Commands\AdminCommands;
13
14			use PhpTelegramBot\Core\Commands\AdminCommand;
15			use PhpTelegramBot\Core\DB;
16			use PhpTelegramBot\Core\Entities\ServerResponse;
17			use PhpTelegramBot\Core\Exception\TelegramException;
18			use PhpTelegramBot\Core\Request;
19			use PhpTelegramBot\Core\TelegramLog;
20			use PDOException;
21
22			/**
23			* User "/cleanup" command
24			*
25			* Configuration options:
26			*
27			* $telegram->setCommandConfig('cleanup', [
28			* // Define which tables should be cleaned.
29			* 'tables_to_clean' => [
30			* 'message',
31			* 'edited_message',
32			* ],
33			* // Define how old cleaned entries should be.
34			* 'clean_older_than' => [
35			* 'message' => '7 days',
36			* 'edited_message' => '30 days',
37			* ]
38			* );
39			*/
40			class CleanupCommand extends AdminCommand
41			{
42			/**
43			* @var string
44			*/
45			protected $name = 'cleanup';
46
47			/**
48			* @var string
49			*/
50			protected $description = 'Clean up the database from old records';
51
52			/**
53			* @var string
54			*/
55			protected $usage = '/cleanup [dry] <days> or /cleanup [dry] <count> <unit> (e.g. 3 weeks)';
56
57			/**
58			* @var string
59			*/
60			protected $version = '1.1.0';
61
62			/**
63			* @var bool
64			*/
65			protected $need_mysql = true;
66
67			/**
68			* Default tables to clean, cleaning 'chat', 'user' and 'user_chat' by default is bad practice!
69			*
70			* @var array
71			*/
72			protected static $default_tables_to_clean = [
73			'callback_query',
74			'chosen_inline_result',
75			'conversation',
76			'edited_message',
77			'inline_query',
78			'message',
79			'request_limiter',
80			'telegram_update',
81			];
82
83			/**
84			* By default, remove records older than X days/hours/anything from these tables.
85			*
86			* @var array
87			*/
88			protected static $default_clean_older_than = [
89			'callback_query' => '30 days',
90			'chat' => '365 days',
91			'chosen_inline_result' => '30 days',
92			'conversation' => '90 days',
93			'edited_message' => '30 days',
94			'inline_query' => '30 days',
95			'message' => '30 days',
96			'poll' => '90 days',
97			'request_limiter' => '1 minute',
98			'shipping_query' => '90 days',
99			'telegram_update' => '30 days',
100			'user' => '365 days',
101			'user_chat' => '365 days',
102			];
103
104			/**
105			* Set command config
106			*
107			* @param string $custom_time
108			*
109			* @return array
110			*/
111			private function getSettings($custom_time = '')
112			{
113			$tables_to_clean = self::$default_tables_to_clean;
114			$user_tables_to_clean = $this->getConfig('tables_to_clean');
115			if (is_array($user_tables_to_clean)) {
116			$tables_to_clean = $user_tables_to_clean;
117			}
118
119			$clean_older_than = self::$default_clean_older_than;
120			$user_clean_older_than = $this->getConfig('clean_older_than');
121			if (is_array($user_clean_older_than)) {
122			$clean_older_than = array_merge($clean_older_than, $user_clean_older_than);
123			}
124
125			// Convert numeric-only values to days.
126			array_walk($clean_older_than, function (&$time) use ($custom_time) {
127			if (!empty($custom_time)) {
128			$time = $custom_time;
129			}
130			if (is_numeric($time)) {
131			$time .= ' days';
132			}
133			});
134
135			return compact('tables_to_clean', 'clean_older_than');
136			}
137
138			/**
139			* Get SQL queries array based on settings provided
140			*
141			* @param $settings
142			*
143			* @return array
144			* @throws TelegramException
145			*/
146			private function getQueries($settings)
147			{
148			if (empty($settings) \|\| !is_array($settings)) {
149			throw new TelegramException('Settings variable is not an array or is empty!');
150			}
151
152			// Convert all clean_older_than times to correct format.
153			$clean_older_than = $settings['clean_older_than'];
154			foreach ($clean_older_than as $table => $time) {
155			$clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time));
156			}
157			$tables_to_clean = $settings['tables_to_clean'];
158
159			$queries = [];
160
161			if (in_array('telegram_update', $tables_to_clean, true)) {
162			$queries[] = sprintf(
163			'DELETE FROM `%3$s`
164			WHERE `id` != \'%1$s\'
165			AND `chat_id` NOT IN (
166			SELECT `id`
167			FROM `%4$s`
168			WHERE `%3$s`.`chat_id` = `id`
169			AND `updated_at` < \'%2$s\'
170			)
171			AND (
172			`message_id` IS NOT NULL
173			AND `message_id` IN (
174			SELECT `id`
175			FROM `%5$s`
176			WHERE `date` < \'%2$s\'
177			)
178			)
179			OR (
180			`edited_message_id` IS NOT NULL
181			AND `edited_message_id` IN (
182			SELECT `id`
183			FROM `%6$s`
184			WHERE `edit_date` < \'%2$s\'
185			)
186			)
187			OR (
188			`inline_query_id` IS NOT NULL
189			AND `inline_query_id` IN (
190			SELECT `id`
191			FROM `%7$s`
192			WHERE `created_at` < \'%2$s\'
193			)
194			)
195			OR (
196			`chosen_inline_result_id` IS NOT NULL
197			AND `chosen_inline_result_id` IN (
198			SELECT `id`
199			FROM `%8$s`
200			WHERE `created_at` < \'%2$s\'
201			)
202			)
203			OR (
204			`callback_query_id` IS NOT NULL
205			AND `callback_query_id` IN (
206			SELECT `id`
207			FROM `%9$s`
208			WHERE `created_at` < \'%2$s\'
209			)
210			)
211			',
212			$this->getUpdate()->getUpdateId(),
213			$clean_older_than['telegram_update'],
214			TB_TELEGRAM_UPDATE,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...ands\TB_TELEGRAM_UPDATE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
215			TB_CHAT,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Commands\AdminCommands\TB_CHAT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
216			TB_MESSAGE,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...dminCommands\TB_MESSAGE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
217			TB_EDITED_MESSAGE,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...mands\TB_EDITED_MESSAGE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
218			TB_INLINE_QUERY,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...ommands\TB_INLINE_QUERY` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
219			TB_CHOSEN_INLINE_RESULT,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...TB_CHOSEN_INLINE_RESULT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
220			TB_CALLBACK_QUERY
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...mands\TB_CALLBACK_QUERY` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
221			);
222			}
223
224			if (in_array('user_chat', $tables_to_clean, true)) {
225			$queries[] = sprintf(
226			'DELETE FROM `%1$s`
227			WHERE `user_id` IN (
228			SELECT `id`
229			FROM `%2$s`
230			WHERE `updated_at` < \'%3$s\'
231			)
232			',
233			TB_USER_CHAT,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...inCommands\TB_USER_CHAT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
234			TB_USER,
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Commands\AdminCommands\TB_USER` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
235			$clean_older_than['chat']
236			);
237			}
238
239			// Simple.
240			$simple_tables = [
241			'user' => ['table' => TB_USER, 'field' => 'updated_at'],
242			'chat' => ['table' => TB_CHAT, 'field' => 'updated_at'],
243			'conversation' => ['table' => TB_CONVERSATION, 'field' => 'updated_at'],
244			'poll' => ['table' => TB_POLL, 'field' => 'created_at'],
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Commands\AdminCommands\TB_POLL` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
245			'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'],
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...ands\TB_REQUEST_LIMITER` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
246			'shipping_query' => ['table' => TB_SHIPPING_QUERY, 'field' => 'created_at'],
			0 ignored issues – show Bug introduced 2020-04-15 21:50 UTC by Report Bug Copy Issue Report The constant `PhpTelegramBot\Core\Comm...mands\TB_SHIPPING_QUERY` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
247			];
248
249			foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) {
250			$queries[] = sprintf(
251			'DELETE FROM `%1$s`
252			WHERE `%2$s` < \'%3$s\'
253			',
254			$simple_tables[$table_to_clean]['table'],
255			$simple_tables[$table_to_clean]['field'],
256			$clean_older_than[$table_to_clean]
257			);
258			}
259
260			// Queries.
261			$query_tables = [
262			'inline_query' => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'],
263			'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'],
264			'callback_query' => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'],
265			];
266			foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) {
267			$queries[] = sprintf(
268			'DELETE FROM `%1$s`
269			WHERE `%2$s` < \'%3$s\'
270			AND `id` NOT IN (
271			SELECT `%4$s`
272			FROM `%5$s`
273			WHERE `%4$s` = `%1$s`.`id`
274			)
275			',
276			$query_tables[$table_to_clean]['table'],
277			$query_tables[$table_to_clean]['field'],
278			$clean_older_than[$table_to_clean],
279			$table_to_clean . '_id',
280			TB_TELEGRAM_UPDATE
281			);
282			}
283
284			// Messages
285			if (in_array('edited_message', $tables_to_clean, true)) {
286			$queries[] = sprintf(
287			'DELETE FROM `%1$s`
288			WHERE `edit_date` < \'%2$s\'
289			AND `id` NOT IN (
290			SELECT `message_id`
291			FROM `%3$s`
292			WHERE `edited_message_id` = `%1$s`.`id`
293			)
294			',
295			TB_EDITED_MESSAGE,
296			$clean_older_than['edited_message'],
297			TB_TELEGRAM_UPDATE
298			);
299			}
300
301			if (in_array('message', $tables_to_clean, true)) {
302			$queries[] = sprintf(
303			'DELETE FROM `%1$s`
304			WHERE id IN (
305			SELECT id
306			FROM (
307			SELECT id
308			FROM `%1$s`
309			WHERE `date` < \'%2$s\'
310			AND `id` NOT IN (
311			SELECT `message_id`
312			FROM `%3$s`
313			WHERE `message_id` = `%1$s`.`id`
314			)
315			AND `id` NOT IN (
316			SELECT `message_id`
317			FROM `%4$s`
318			WHERE `message_id` = `%1$s`.`id`
319			)
320			AND `id` NOT IN (
321			SELECT `message_id`
322			FROM `%5$s`
323			WHERE `message_id` = `%1$s`.`id`
324			)
325			AND `id` NOT IN (
326			SELECT a.`reply_to_message` FROM `%1$s` a
327			INNER JOIN `%1$s` b ON b.`id` = a.`reply_to_message` AND b.`chat_id` = a.`reply_to_chat`
328			)
329			ORDER BY `id` DESC
330			) a
331			)
332			',
333			TB_MESSAGE,
334			$clean_older_than['message'],
335			TB_EDITED_MESSAGE,
336			TB_TELEGRAM_UPDATE,
337			TB_CALLBACK_QUERY
338			);
339			}
340
341			return $queries;
342			}
343
344			/**
345			* Execution if MySQL is required but not available
346			*
347			* @return ServerResponse
348			* @throws TelegramException
349			*/
350			public function executeNoDb()
351			{
352			return $this->replyToChat('No database connection!', ['parse_mode' => 'Markdown']);
353			}
354
355			/**
356			* Command execute method
357			*
358			* @return ServerResponse
359			* @throws TelegramException
360			*/
361			public function execute()
362			{
363			$message = $this->getMessage();
364			$text = $message->getText(true);
365
366			// Dry run?
367			$dry_run = strpos($text, 'dry') !== false;
368			$text = trim(str_replace('dry', '', $text));
369
370			$settings = $this->getSettings($text);
371			$queries = $this->getQueries($settings);
372
373			if ($dry_run) {
374			return $this->replyToUser('Queries:' . PHP_EOL . implode(PHP_EOL, $queries));
375			}
376
377			$infos = [];
378			foreach ($settings['tables_to_clean'] as $table) {
379			$info = "{$table}";
380
381			if (isset($settings['clean_older_than'][$table])) {
382			$info .= " ({$settings['clean_older_than'][$table]})";
383			}
384
385			$infos[] = $info;
386			}
387
388			$data = [
389			'chat_id' => $message->getFrom()->getId(),
390			'parse_mode' => 'Markdown',
391			];
392
393			$data['text'] = 'Cleaning up tables:' . PHP_EOL . implode(PHP_EOL, $infos);
394			Request::sendMessage($data);
395
396			$rows = 0;
397			$pdo = DB::getPdo();
398			try {
399			$pdo->beginTransaction();
400
401			foreach ($queries as $query) {
402			// Delete in chunks to not block / improve speed on big tables.
403			$query .= ' LIMIT 10000';
404			while ($dbq = $pdo->query($query)) {
405			if ($dbq->rowCount() === 0) {
406			continue 2;
407			}
408			$rows += $dbq->rowCount();
409			}
410
411			TelegramLog::error('Error while executing query: ' . $query);
412			}
413
414			// commit changes to the database and end transaction
415			$pdo->commit();
416
417			$data['text'] = "Database cleanup done! _(removed {$rows} rows)_";
418			} catch (PDOException $e) {
419			$data['text'] = 'Database cleanup failed! _(check your error logs)_';
420
421			// rollback changes on exception
422			// useful if you want to track down error you can't replicate it when some of the data is already deleted
423			$pdo->rollBack();
424
425			TelegramLog::error($e->getMessage());
426			}
427
428			return Request::sendMessage($data);
429			}
430			}
431

php-telegram-bot / core

Pull Request — develop (#1077)

CleanupCommand::getQueries() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like