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\DB; |
||
16 | use Longman\TelegramBot\Entities\ServerResponse; |
||
17 | use Longman\TelegramBot\Exception\TelegramException; |
||
18 | use Longman\TelegramBot\Request; |
||
19 | use Longman\TelegramBot\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 = ''): array |
||
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): array |
||
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 IN ( |
||
165 | SELECT id FROM ( |
||
166 | SELECT id FROM `%3$s` |
||
167 | WHERE `id` != \'%1$s\' |
||
168 | AND `chat_id` NOT IN ( |
||
169 | SELECT `id` |
||
170 | FROM `%4$s` |
||
171 | WHERE `%3$s`.`chat_id` = `id` |
||
172 | AND `updated_at` < \'%2$s\' |
||
173 | ) |
||
174 | AND ( |
||
175 | `message_id` IS NOT NULL |
||
176 | AND `message_id` IN ( |
||
177 | SELECT `id` |
||
178 | FROM `%5$s` |
||
179 | WHERE `date` < \'%2$s\' |
||
180 | ) |
||
181 | ) |
||
182 | OR ( |
||
183 | `edited_message_id` IS NOT NULL |
||
184 | AND `edited_message_id` IN ( |
||
185 | SELECT `id` |
||
186 | FROM `%6$s` |
||
187 | WHERE `edit_date` < \'%2$s\' |
||
188 | ) |
||
189 | ) |
||
190 | OR ( |
||
191 | `inline_query_id` IS NOT NULL |
||
192 | AND `inline_query_id` IN ( |
||
193 | SELECT `id` |
||
194 | FROM `%7$s` |
||
195 | WHERE `created_at` < \'%2$s\' |
||
196 | ) |
||
197 | ) |
||
198 | OR ( |
||
199 | `chosen_inline_result_id` IS NOT NULL |
||
200 | AND `chosen_inline_result_id` IN ( |
||
201 | SELECT `id` |
||
202 | FROM `%8$s` |
||
203 | WHERE `created_at` < \'%2$s\' |
||
204 | ) |
||
205 | ) |
||
206 | OR ( |
||
207 | `callback_query_id` IS NOT NULL |
||
208 | AND `callback_query_id` IN ( |
||
209 | SELECT `id` |
||
210 | FROM `%9$s` |
||
211 | WHERE `created_at` < \'%2$s\' |
||
212 | ) |
||
213 | ) |
||
214 | ) a |
||
215 | ) |
||
216 | ', |
||
217 | $this->getUpdate()->getUpdateId(), |
||
218 | $clean_older_than['telegram_update'], |
||
219 | TB_TELEGRAM_UPDATE, |
||
220 | TB_CHAT, |
||
221 | TB_MESSAGE, |
||
222 | TB_EDITED_MESSAGE, |
||
223 | TB_INLINE_QUERY, |
||
224 | TB_CHOSEN_INLINE_RESULT, |
||
225 | TB_CALLBACK_QUERY |
||
226 | ); |
||
227 | } |
||
228 | |||
229 | if (in_array('user_chat', $tables_to_clean, true)) { |
||
230 | $queries[] = sprintf( |
||
231 | 'DELETE FROM `%1$s` |
||
232 | WHERE `user_id` IN ( |
||
233 | SELECT `id` |
||
234 | FROM `%2$s` |
||
235 | WHERE `updated_at` < \'%3$s\' |
||
236 | ) |
||
237 | ', |
||
238 | TB_USER_CHAT, |
||
239 | TB_USER, |
||
240 | $clean_older_than['chat'] |
||
241 | ); |
||
242 | } |
||
243 | |||
244 | // Simple. |
||
245 | $simple_tables = [ |
||
246 | 'user' => ['table' => TB_USER, 'field' => 'updated_at'], |
||
247 | 'chat' => ['table' => TB_CHAT, 'field' => 'updated_at'], |
||
248 | 'conversation' => ['table' => TB_CONVERSATION, 'field' => 'updated_at'], |
||
249 | 'poll' => ['table' => TB_POLL, 'field' => 'created_at'], |
||
250 | 'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'], |
||
251 | 'shipping_query' => ['table' => TB_SHIPPING_QUERY, 'field' => 'created_at'], |
||
252 | ]; |
||
253 | |||
254 | foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) { |
||
255 | $queries[] = sprintf( |
||
256 | 'DELETE FROM `%1$s` |
||
257 | WHERE `%2$s` < \'%3$s\' |
||
258 | ', |
||
259 | $simple_tables[$table_to_clean]['table'], |
||
260 | $simple_tables[$table_to_clean]['field'], |
||
261 | $clean_older_than[$table_to_clean] |
||
262 | ); |
||
263 | } |
||
264 | |||
265 | // Queries. |
||
266 | $query_tables = [ |
||
267 | 'inline_query' => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'], |
||
268 | 'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'], |
||
269 | 'callback_query' => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'], |
||
270 | ]; |
||
271 | foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) { |
||
272 | $queries[] = sprintf( |
||
273 | 'DELETE FROM `%1$s` |
||
274 | WHERE `%2$s` < \'%3$s\' |
||
275 | AND `id` NOT IN ( |
||
276 | SELECT `%4$s` |
||
277 | FROM `%5$s` |
||
278 | WHERE `%4$s` = `%1$s`.`id` |
||
279 | ) |
||
280 | ', |
||
281 | $query_tables[$table_to_clean]['table'], |
||
282 | $query_tables[$table_to_clean]['field'], |
||
283 | $clean_older_than[$table_to_clean], |
||
284 | $table_to_clean . '_id', |
||
285 | TB_TELEGRAM_UPDATE |
||
286 | ); |
||
287 | } |
||
288 | |||
289 | // Messages |
||
290 | if (in_array('edited_message', $tables_to_clean, true)) { |
||
291 | $queries[] = sprintf( |
||
292 | 'DELETE FROM `%1$s` |
||
293 | WHERE `edit_date` < \'%2$s\' |
||
294 | AND `id` NOT IN ( |
||
295 | SELECT `message_id` |
||
296 | FROM `%3$s` |
||
297 | WHERE `edited_message_id` = `%1$s`.`id` |
||
298 | ) |
||
299 | ', |
||
300 | TB_EDITED_MESSAGE, |
||
301 | $clean_older_than['edited_message'], |
||
302 | TB_TELEGRAM_UPDATE |
||
303 | ); |
||
304 | } |
||
305 | |||
306 | if (in_array('message', $tables_to_clean, true)) { |
||
307 | $queries[] = sprintf( |
||
308 | 'DELETE FROM `%1$s` |
||
309 | WHERE id IN ( |
||
310 | SELECT id |
||
311 | FROM ( |
||
312 | SELECT id |
||
313 | FROM `%1$s` |
||
314 | WHERE `date` < \'%2$s\' |
||
315 | AND `id` NOT IN ( |
||
316 | SELECT `message_id` |
||
317 | FROM `%3$s` |
||
318 | WHERE `message_id` = `%1$s`.`id` |
||
319 | ) |
||
320 | AND `id` NOT IN ( |
||
321 | SELECT `message_id` |
||
322 | FROM `%4$s` |
||
323 | WHERE `message_id` = `%1$s`.`id` |
||
324 | ) |
||
325 | AND `id` NOT IN ( |
||
326 | SELECT `message_id` |
||
327 | FROM `%5$s` |
||
328 | WHERE `message_id` = `%1$s`.`id` |
||
329 | ) |
||
330 | AND `id` NOT IN ( |
||
331 | SELECT a.`reply_to_message` FROM `%1$s` a |
||
332 | INNER JOIN `%1$s` b ON b.`id` = a.`reply_to_message` AND b.`chat_id` = a.`reply_to_chat` |
||
333 | ) |
||
334 | ORDER BY `id` DESC |
||
335 | ) a |
||
336 | ) |
||
337 | ', |
||
338 | TB_MESSAGE, |
||
339 | $clean_older_than['message'], |
||
340 | TB_EDITED_MESSAGE, |
||
341 | TB_TELEGRAM_UPDATE, |
||
342 | TB_CALLBACK_QUERY |
||
343 | ); |
||
344 | } |
||
345 | |||
346 | return $queries; |
||
347 | } |
||
348 | |||
349 | /** |
||
350 | * Execution if MySQL is required but not available |
||
351 | * |
||
352 | * @return ServerResponse |
||
353 | * @throws TelegramException |
||
354 | */ |
||
355 | public function executeNoDb(): ServerResponse |
||
356 | { |
||
357 | return $this->replyToChat('*No database connection!*', ['parse_mode' => 'Markdown']); |
||
358 | } |
||
359 | |||
360 | /** |
||
361 | * Command execute method |
||
362 | * |
||
363 | * @return ServerResponse |
||
364 | * @throws TelegramException |
||
365 | */ |
||
366 | public function execute(): ServerResponse |
||
367 | { |
||
368 | $message = $this->getMessage(); |
||
369 | $text = $message->getText(true); |
||
370 | |||
371 | // Dry run? |
||
372 | $dry_run = strpos($text, 'dry') !== false; |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
373 | $text = trim(str_replace('dry', '', $text)); |
||
374 | |||
375 | $settings = $this->getSettings($text); |
||
376 | $queries = $this->getQueries($settings); |
||
377 | |||
378 | if ($dry_run) { |
||
379 | return $this->replyToUser('Queries:' . PHP_EOL . implode(PHP_EOL, $queries)); |
||
380 | } |
||
381 | |||
382 | $infos = []; |
||
383 | foreach ($settings['tables_to_clean'] as $table) { |
||
384 | $info = "*{$table}*"; |
||
385 | |||
386 | if (isset($settings['clean_older_than'][$table])) { |
||
387 | $info .= " ({$settings['clean_older_than'][$table]})"; |
||
388 | } |
||
389 | |||
390 | $infos[] = $info; |
||
391 | } |
||
392 | |||
393 | $data = [ |
||
394 | 'chat_id' => $message->getFrom()->getId(), |
||
395 | 'parse_mode' => 'Markdown', |
||
396 | ]; |
||
397 | |||
398 | $data['text'] = 'Cleaning up tables:' . PHP_EOL . implode(PHP_EOL, $infos); |
||
399 | Request::sendMessage($data); |
||
400 | |||
401 | $rows = 0; |
||
402 | $pdo = DB::getPdo(); |
||
403 | try { |
||
404 | $pdo->beginTransaction(); |
||
405 | |||
406 | foreach ($queries as $query) { |
||
407 | // Delete in chunks to not block / improve speed on big tables. |
||
408 | $query .= ' LIMIT 10000'; |
||
409 | while ($dbq = $pdo->query($query)) { |
||
410 | if ($dbq->rowCount() === 0) { |
||
411 | continue 2; |
||
412 | } |
||
413 | $rows += $dbq->rowCount(); |
||
414 | } |
||
415 | |||
416 | TelegramLog::error('Error while executing query: ' . $query); |
||
417 | } |
||
418 | |||
419 | // commit changes to the database and end transaction |
||
420 | $pdo->commit(); |
||
421 | |||
422 | $data['text'] = "*Database cleanup done!* _(removed {$rows} rows)_"; |
||
423 | } catch (PDOException $e) { |
||
424 | $data['text'] = '*Database cleanup failed!* _(check your error logs)_'; |
||
425 | |||
426 | // rollback changes on exception |
||
427 | // useful if you want to track down error you can't replicate it when some of the data is already deleted |
||
428 | $pdo->rollBack(); |
||
429 | |||
430 | TelegramLog::error($e->getMessage()); |
||
431 | } |
||
432 | |||
433 | return Request::sendMessage($data); |
||
434 | } |
||
435 | } |
||
436 |