CleanupCommand::executeNoDb() - Code Metrics - Inspection of "Version 0.58.0" - php-telegram-bot/core - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 7f7158...8d1ed5 )

by Armando

created 2019-06-26 16:58 UTC

CleanupCommand::executeNoDb() A

↳ Parent: CleanupCommand

Complexity

Conditions	1
Paths	1

Size

Total Lines	12
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	7
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	12
ccs	0
cts	10
cp	0
crap	2
rs	10

<?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\DB;
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 <days> or /cleanup <count> <unit> (e.g. 3 weeks)';

    /**
     * @var string
     */
    protected $version = '1.0.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 = [
        'chat'                 => '365 days',
        'callback_query'       => '30 days',
        'chosen_inline_result' => '30 days',
        'conversation'         => '30 days',
        'edited_message'       => '30 days',
        'inline_query'         => '30 days',
        'message'              => '30 days',
        'request_limiter'      => '1 minute',
        '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 `chat_id` = `%4$s`.`id`
                    AND `updated_at` < \'%1$s\'
                  )
                  AND (
                    `message_id` IS NOT NULL
                    AND `message_id` IN (
                      SELECT f.id
                      FROM `%5$s` f
                      WHERE `date` < \'%2$s\'
                    )
                  )
                  OR (
                    `edited_message_id` IS NOT NULL
                    AND `edited_message_id` IN (
                      SELECT f.id
                      FROM `%6$s` f
                      WHERE `edit_date` < \'%2$s\'
                    )
                  )
                  OR (
                    `inline_query_id` IS NOT NULL
                    AND `inline_query_id` IN (
                      SELECT f.id
                      FROM `%7$s` f
                      WHERE `created_at` < \'%2$s\'
                    )
                  )
                  OR (
                    `chosen_inline_result_id` IS NOT NULL
                    AND `chosen_inline_result_id` IN (
                      SELECT f.id
                      FROM `%8$s` f
                      WHERE `created_at` < \'%2$s\'
                    )
                  )
                  OR (
                    `callback_query_id` IS NOT NULL
                    AND `callback_query_id` IN (
                      SELECT f.id
                      FROM `%9$s` f
                      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 f.id
                  FROM `%2$s` f
                  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'],
            'request_limiter' => ['table' => TB_REQUEST_LIMITER, '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()
    {
        $message = $this->getMessage();
        $chat_id = $message->getChat()->getId();

        $data = [
            'chat_id'    => $chat_id,
            'parse_mode' => 'Markdown',
            'text'       => '*No database connection!*',
        ];

        return Request::sendMessage($data);
    }

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

        $data = [
            'chat_id'    => $user_id,
            'parse_mode' => 'Markdown',
        ];

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

        $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['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) {
                if ($dbq = $pdo->query($query)) {
                    $rows += $dbq->rowCount();
                } else {
                    TelegramLog::error('Error while executing query: ' . $query);
                }
            }

            $pdo->commit();     // commit changes to the database and end transaction
        } catch (PDOException $e) {
            $pdo->rollBack();   // 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...)

            $data['text'] = '*Database cleanup failed!* _(check your error logs)_';
            Request::sendMessage($data);

            throw new TelegramException($e->getMessage());
        }

        if ($rows > 0) {
            $data['text'] = '*Database cleanup done!* _(removed ' . $rows . ' rows)_';
        } else {
            $data['text'] = '*No data to clean!*';
        }

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


1			<?php
2			/**
3			* This file is part of the TelegramBot package.
4			*
5			* (c) Avtandil Kikabidze aka LONGMAN <[email protected]>
6			*
7			* For the full copyright and license information, please view the LICENSE
8			* file that was distributed with this source code.
9			*/
10
11			namespace Longman\TelegramBot\Commands\AdminCommands;
12
13			use Longman\TelegramBot\Commands\AdminCommand;
14			use Longman\TelegramBot\DB;
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 <days> or /cleanup <count> <unit> (e.g. 3 weeks)';
55
56			/**
57			* @var string
58			*/
59			protected $version = '1.0.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			'chat' => '365 days',
89			'callback_query' => '30 days',
90			'chosen_inline_result' => '30 days',
91			'conversation' => '30 days',
92			'edited_message' => '30 days',
93			'inline_query' => '30 days',
94			'message' => '30 days',
95			'request_limiter' => '1 minute',
96			'telegram_update' => '30 days',
97			'user' => '365 days',
98			'user_chat' => '365 days',
99			];
100
101			/**
102			* Set command config
103			*
104			* @param string $custom_time
105			*
106			* @return array
107			*/
108			private function getSettings($custom_time = '')
109			{
110			$tables_to_clean = self::$default_tables_to_clean;
111			$user_tables_to_clean = $this->getConfig('tables_to_clean');
112			if (is_array($user_tables_to_clean)) {
113			$tables_to_clean = $user_tables_to_clean;
114			}
115
116			$clean_older_than = self::$default_clean_older_than;
117			$user_clean_older_than = $this->getConfig('clean_older_than');
118			if (is_array($user_clean_older_than)) {
119			$clean_older_than = array_merge($clean_older_than, $user_clean_older_than);
120			}
121
122			// Convert numeric-only values to days.
123			array_walk($clean_older_than, function (&$time) use ($custom_time) {
124			if (!empty($custom_time)) {
125			$time = $custom_time;
126			}
127			if (is_numeric($time)) {
128			$time .= ' days';
129			}
130			});
131
132			return compact('tables_to_clean', 'clean_older_than');
133			}
134
135			/**
136			* Get SQL queries array based on settings provided
137			*
138			* @param $settings
139			*
140			* @return array
141			* @throws TelegramException
142			*/
143			private function getQueries($settings)
144			{
145			if (empty($settings) \|\| !is_array($settings)) {
146			throw new TelegramException('Settings variable is not an array or is empty!');
147			}
148
149			// Convert all clean_older_than times to correct format.
150			$clean_older_than = $settings['clean_older_than'];
151			foreach ($clean_older_than as $table => $time) {
152			$clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time));
153			}
154			$tables_to_clean = $settings['tables_to_clean'];
155
156			$queries = [];
157
158			if (in_array('telegram_update', $tables_to_clean, true)) {
159			$queries[] = sprintf(
160			'DELETE FROM `%3$s`
161			WHERE `id` != \'%1$s\'
162			AND `chat_id` NOT IN (
163			SELECT `id`
164			FROM `%4$s`
165			WHERE `chat_id` = `%4$s`.`id`
166			AND `updated_at` < \'%1$s\'
167			)
168			AND (
169			`message_id` IS NOT NULL
170			AND `message_id` IN (
171			SELECT f.id
172			FROM `%5$s` f
173			WHERE `date` < \'%2$s\'
174			)
175			)
176			OR (
177			`edited_message_id` IS NOT NULL
178			AND `edited_message_id` IN (
179			SELECT f.id
180			FROM `%6$s` f
181			WHERE `edit_date` < \'%2$s\'
182			)
183			)
184			OR (
185			`inline_query_id` IS NOT NULL
186			AND `inline_query_id` IN (
187			SELECT f.id
188			FROM `%7$s` f
189			WHERE `created_at` < \'%2$s\'
190			)
191			)
192			OR (
193			`chosen_inline_result_id` IS NOT NULL
194			AND `chosen_inline_result_id` IN (
195			SELECT f.id
196			FROM `%8$s` f
197			WHERE `created_at` < \'%2$s\'
198			)
199			)
200			OR (
201			`callback_query_id` IS NOT NULL
202			AND `callback_query_id` IN (
203			SELECT f.id
204			FROM `%9$s` f
205			WHERE `created_at` < \'%2$s\'
206			)
207			)
208			',
209			$this->getUpdate()->getUpdateId(),
210			$clean_older_than['telegram_update'],
211			TB_TELEGRAM_UPDATE,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...ands\TB_TELEGRAM_UPDATE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
212			TB_CHAT,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Commands\AdminCommands\TB_CHAT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
213			TB_MESSAGE,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...dminCommands\TB_MESSAGE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
214			TB_EDITED_MESSAGE,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...mands\TB_EDITED_MESSAGE` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
215			TB_INLINE_QUERY,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...ommands\TB_INLINE_QUERY` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
216			TB_CHOSEN_INLINE_RESULT,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...TB_CHOSEN_INLINE_RESULT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
217			TB_CALLBACK_QUERY
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...mands\TB_CALLBACK_QUERY` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
218			);
219			}
220
221			if (in_array('user_chat', $tables_to_clean, true)) {
222			$queries[] = sprintf(
223			'DELETE FROM `%1$s`
224			WHERE `user_id` IN (
225			SELECT f.id
226			FROM `%2$s` f
227			WHERE `updated_at` < \'%3$s\'
228			)
229			',
230			TB_USER_CHAT,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...inCommands\TB_USER_CHAT` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
231			TB_USER,
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Commands\AdminCommands\TB_USER` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
232			$clean_older_than['chat']
233			);
234			}
235
236			// Simple.
237			$simple_tables = [
238			'user' => ['table' => TB_USER, 'field' => 'updated_at'],
239			'chat' => ['table' => TB_CHAT, 'field' => 'updated_at'],
240			'conversation' => ['table' => TB_CONVERSATION, 'field' => 'updated_at'],
241			'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'],
			1 ignored issue – show Bug introduced 2019-06-14 07:34 UTC by Report Bug Copy Issue Report The constant `Longman\TelegramBot\Comm...ands\TB_REQUEST_LIMITER` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
242			];
243
244			foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) {
245			$queries[] = sprintf(
246			'DELETE FROM `%1$s`
247			WHERE `%2$s` < \'%3$s\'
248			',
249			$simple_tables[$table_to_clean]['table'],
250			$simple_tables[$table_to_clean]['field'],
251			$clean_older_than[$table_to_clean]
252			);
253			}
254
255			// Queries.
256			$query_tables = [
257			'inline_query' => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'],
258			'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'],
259			'callback_query' => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'],
260			];
261			foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) {
262			$queries[] = sprintf(
263			'DELETE FROM `%1$s`
264			WHERE `%2$s` < \'%3$s\'
265			AND `id` NOT IN (
266			SELECT `%4$s`
267			FROM `%5$s`
268			WHERE `%4$s` = `%1$s`.`id`
269			)
270			',
271			$query_tables[$table_to_clean]['table'],
272			$query_tables[$table_to_clean]['field'],
273			$clean_older_than[$table_to_clean],
274			$table_to_clean . '_id',
275			TB_TELEGRAM_UPDATE
276			);
277			}
278
279			// Messages
280			if (in_array('edited_message', $tables_to_clean, true)) {
281			$queries[] = sprintf(
282			'DELETE FROM `%1$s`
283			WHERE `edit_date` < \'%2$s\'
284			AND `id` NOT IN (
285			SELECT `message_id`
286			FROM `%3$s`
287			WHERE `edited_message_id` = `%1$s`.`id`
288			)
289			',
290			TB_EDITED_MESSAGE,
291			$clean_older_than['edited_message'],
292			TB_TELEGRAM_UPDATE
293			);
294			}
295
296			if (in_array('message', $tables_to_clean, true)) {
297			$queries[] = sprintf(
298			'DELETE FROM `%1$s`
299			WHERE id IN (
300			SELECT id
301			FROM (
302			SELECT id
303			FROM `%1$s`
304			WHERE `date` < \'%2$s\'
305			AND `id` NOT IN (
306			SELECT `message_id`
307			FROM `%3$s`
308			WHERE `message_id` = `%1$s`.`id`
309			)
310			AND `id` NOT IN (
311			SELECT `message_id`
312			FROM `%4$s`
313			WHERE `message_id` = `%1$s`.`id`
314			)
315			AND `id` NOT IN (
316			SELECT `message_id`
317			FROM `%5$s`
318			WHERE `message_id` = `%1$s`.`id`
319			)
320			AND `id` NOT IN (
321			SELECT a.`reply_to_message` FROM `%1$s` a
322			INNER JOIN `%1$s` b ON b.`id` = a.`reply_to_message` AND b.`chat_id` = a.`reply_to_chat`
323			)
324			ORDER BY `id` DESC
325			) a
326			)
327			',
328			TB_MESSAGE,
329			$clean_older_than['message'],
330			TB_EDITED_MESSAGE,
331			TB_TELEGRAM_UPDATE,
332			TB_CALLBACK_QUERY
333			);
334			}
335
336			return $queries;
337			}
338
339			/**
340			* Execution if MySQL is required but not available
341			*
342			* @return ServerResponse
343			* @throws TelegramException
344			*/
345			public function executeNoDb()
346			{
347			$message = $this->getMessage();
348			$chat_id = $message->getChat()->getId();
349
350			$data = [
351			'chat_id' => $chat_id,
352			'parse_mode' => 'Markdown',
353			'text' => 'No database connection!',
354			];
355
356			return Request::sendMessage($data);
357			}
358
359			/**
360			* Command execute method
361			*
362			* @return ServerResponse
363			* @throws TelegramException
364			*/
365			public function execute()
366			{
367			$message = $this->getMessage();
368			$user_id = $message->getFrom()->getId();
369			$text = $message->getText(true);
370
371			$data = [
372			'chat_id' => $user_id,
373			'parse_mode' => 'Markdown',
374			];
375
376			$settings = $this->getSettings($text);
377			$queries = $this->getQueries($settings);
378
379			$infos = [];
380			foreach ($settings['tables_to_clean'] as $table) {
381			$info = '' . $table . '';
382
383			if (isset($settings['clean_older_than'][$table])) {
384			$info .= ' (' . $settings['clean_older_than'][$table] . ')';
385			}
386
387			$infos[] = $info;
388			}
389
390			$data['text'] = 'Cleaning up tables:' . PHP_EOL . implode(PHP_EOL, $infos);
391
392			Request::sendMessage($data);
393
394			$rows = 0;
395			$pdo = DB::getPdo();
396			try {
397			$pdo->beginTransaction();
398
399			foreach ($queries as $query) {
400			if ($dbq = $pdo->query($query)) {
401			$rows += $dbq->rowCount();
402			} else {
403			TelegramLog::error('Error while executing query: ' . $query);
404			}
405			}
406
407			$pdo->commit(); // commit changes to the database and end transaction
408			} catch (PDOException $e) {
409			$pdo->rollBack(); // 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...)
410
411			$data['text'] = 'Database cleanup failed! _(check your error logs)_';
412			Request::sendMessage($data);
413
414			throw new TelegramException($e->getMessage());
415			}
416
417			if ($rows > 0) {
418			$data['text'] = 'Database cleanup done! _(removed ' . $rows . ' rows)_';
419			} else {
420			$data['text'] = 'No data to clean!';
421			}
422
423			return Request::sendMessage($data);
424			}
425			}
426

php-telegram-bot / core

Push — master ( 7f7158...8d1ed5 )

CleanupCommand::executeNoDb() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like