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\Exception\TelegramException; |
16
|
|
|
use Longman\TelegramBot\Request; |
17
|
|
|
use Longman\TelegramBot\TelegramLog; |
18
|
|
|
use PDOException; |
19
|
|
|
|
20
|
|
|
/** |
21
|
|
|
* User "/cleanup" command |
22
|
|
|
* |
23
|
|
|
* Configuration options: |
24
|
|
|
* |
25
|
|
|
* $telegram->setCommandConfig('cleanup', [ |
26
|
|
|
* // Define which tables should be cleaned. |
27
|
|
|
* 'tables_to_clean' => [ |
28
|
|
|
* 'message', |
29
|
|
|
* 'edited_message', |
30
|
|
|
* ], |
31
|
|
|
* // Define how old cleaned entries should be. |
32
|
|
|
* 'clean_older_than' => [ |
33
|
|
|
* 'message' => '7 days', |
34
|
|
|
* 'edited_message' => '30 days', |
35
|
|
|
* ] |
36
|
|
|
* ); |
37
|
|
|
*/ |
38
|
|
|
class CleanupCommand extends AdminCommand |
39
|
|
|
{ |
40
|
|
|
/** |
41
|
|
|
* @var string |
42
|
|
|
*/ |
43
|
|
|
protected $name = 'cleanup'; |
44
|
|
|
|
45
|
|
|
/** |
46
|
|
|
* @var string |
47
|
|
|
*/ |
48
|
|
|
protected $description = 'Clean up the database from old records'; |
49
|
|
|
|
50
|
|
|
/** |
51
|
|
|
* @var string |
52
|
|
|
*/ |
53
|
|
|
protected $usage = '/cleanup <days> or /cleanup <count> <unit> (e.g. 3 weeks)'; |
54
|
|
|
|
55
|
|
|
/** |
56
|
|
|
* @var string |
57
|
|
|
*/ |
58
|
|
|
protected $version = '1.0.0'; |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* Default tables to clean, cleaning 'chat', 'user' and 'user_chat' by default is bad practice! |
62
|
|
|
* |
63
|
|
|
* @var array |
64
|
|
|
*/ |
65
|
|
|
protected static $default_tables_to_clean = [ |
66
|
|
|
'botan_shortener', |
67
|
|
|
'callback_query', |
68
|
|
|
'chosen_inline_result', |
69
|
|
|
'conversation', |
70
|
|
|
'edited_message', |
71
|
|
|
'inline_query', |
72
|
|
|
'message', |
73
|
|
|
'request_limiter', |
74
|
|
|
'telegram_update', |
75
|
|
|
]; |
76
|
|
|
|
77
|
|
|
/** |
78
|
|
|
* By default, remove records older than X days/hours/anything from these tables. |
79
|
|
|
* |
80
|
|
|
* @var array |
81
|
|
|
*/ |
82
|
|
|
protected static $default_clean_older_than = [ |
83
|
|
|
'botan_shortener' => '30 days', |
84
|
|
|
'chat' => '365 days', |
85
|
|
|
'callback_query' => '30 days', |
86
|
|
|
'chosen_inline_result' => '30 days', |
87
|
|
|
'conversation' => '30 days', |
88
|
|
|
'edited_message' => '30 days', |
89
|
|
|
'inline_query' => '30 days', |
90
|
|
|
'message' => '30 days', |
91
|
|
|
'request_limiter' => '1 minute', |
92
|
|
|
'telegram_update' => '30 days', |
93
|
|
|
'user' => '365 days', |
94
|
|
|
'user_chat' => '365 days', |
95
|
|
|
]; |
96
|
|
|
|
97
|
|
|
/** |
98
|
|
|
* Set command config |
99
|
|
|
* |
100
|
|
|
* @param string $custom_time |
101
|
|
|
* |
102
|
|
|
* @return array |
103
|
|
|
*/ |
104
|
|
|
private function getSettings($custom_time = '') |
105
|
|
|
{ |
106
|
|
|
$tables_to_clean = self::$default_tables_to_clean; |
107
|
|
|
$user_tables_to_clean = $this->getConfig('tables_to_clean'); |
108
|
|
|
if (is_array($user_tables_to_clean)) { |
109
|
|
|
$tables_to_clean = $user_tables_to_clean; |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
$clean_older_than = self::$default_clean_older_than; |
113
|
|
|
$user_clean_older_than = $this->getConfig('clean_older_than'); |
114
|
|
|
if (is_array($user_clean_older_than)) { |
115
|
|
|
$clean_older_than = array_merge($clean_older_than, $user_clean_older_than); |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
// Convert numeric-only values to days. |
119
|
|
|
array_walk($clean_older_than, function (&$time) use ($custom_time) { |
120
|
|
|
if (!empty($custom_time)) { |
121
|
|
|
$time = $custom_time; |
122
|
|
|
} |
123
|
|
|
if (is_numeric($time)) { |
124
|
|
|
$time .= ' days'; |
125
|
|
|
} |
126
|
|
|
}); |
127
|
|
|
|
128
|
|
|
return compact('tables_to_clean', 'clean_older_than'); |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* Get SQL queries array based on settings provided |
133
|
|
|
* |
134
|
|
|
* @param $settings |
135
|
|
|
* |
136
|
|
|
* @return array |
137
|
|
|
* @throws TelegramException |
138
|
|
|
*/ |
139
|
|
|
private function getQueries($settings) |
140
|
|
|
{ |
141
|
|
|
if (empty($settings) || !is_array($settings)) { |
142
|
|
|
throw new TelegramException('Settings variable is not an array or is empty!'); |
143
|
|
|
} |
144
|
|
|
|
145
|
|
|
// Convert all clean_older_than times to correct format. |
146
|
|
|
$clean_older_than = $settings['clean_older_than']; |
147
|
|
|
foreach ($clean_older_than as $table => $time) { |
148
|
|
|
$clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time)); |
149
|
|
|
} |
150
|
|
|
$tables_to_clean = $settings['tables_to_clean']; |
151
|
|
|
|
152
|
|
|
$queries = []; |
153
|
|
|
|
154
|
|
|
if (in_array('telegram_update', $tables_to_clean, true)) { |
155
|
|
|
$queries[] = sprintf( |
156
|
|
|
'DELETE FROM `%3$s` |
157
|
|
|
WHERE `id` != \'%1$s\' |
158
|
|
|
AND `chat_id` NOT IN ( |
159
|
|
|
SELECT `id` |
160
|
|
|
FROM `%4$s` |
161
|
|
|
WHERE `chat_id` = `%4$s`.`id` |
162
|
|
|
) |
163
|
|
|
AND ( |
164
|
|
|
`message_id` IS NOT NULL |
165
|
|
|
AND `message_id` IN ( |
166
|
|
|
SELECT f.id |
167
|
|
|
FROM `%5$s` f |
168
|
|
|
WHERE `date` < \'%2$s\' |
169
|
|
|
) |
170
|
|
|
) |
171
|
|
|
OR ( |
172
|
|
|
`edited_message_id` IS NOT NULL |
173
|
|
|
AND `edited_message_id` IN ( |
174
|
|
|
SELECT f.id |
175
|
|
|
FROM `%6$s` f |
176
|
|
|
WHERE `edit_date` < \'%2$s\' |
177
|
|
|
) |
178
|
|
|
) |
179
|
|
|
OR ( |
180
|
|
|
`inline_query_id` IS NOT NULL |
181
|
|
|
AND `inline_query_id` IN ( |
182
|
|
|
SELECT f.id |
183
|
|
|
FROM `%7$s` f |
184
|
|
|
WHERE `created_at` < \'%2$s\' |
185
|
|
|
) |
186
|
|
|
) |
187
|
|
|
OR ( |
188
|
|
|
`chosen_inline_result_id` IS NOT NULL |
189
|
|
|
AND `chosen_inline_result_id` IN ( |
190
|
|
|
SELECT f.id |
191
|
|
|
FROM `%8$s` f |
192
|
|
|
WHERE `created_at` < \'%2$s\' |
193
|
|
|
) |
194
|
|
|
) |
195
|
|
|
OR ( |
196
|
|
|
`callback_query_id` IS NOT NULL |
197
|
|
|
AND `callback_query_id` IN ( |
198
|
|
|
SELECT f.id |
199
|
|
|
FROM `%9$s` f |
200
|
|
|
WHERE `created_at` < \'%2$s\' |
201
|
|
|
) |
202
|
|
|
) |
203
|
|
|
', |
204
|
|
|
$this->getUpdate()->getUpdateId(), |
205
|
|
|
$clean_older_than['telegram_update'], |
206
|
|
|
TB_TELEGRAM_UPDATE, |
207
|
|
|
TB_CHAT, |
208
|
|
|
TB_MESSAGE, |
209
|
|
|
TB_EDITED_MESSAGE, |
210
|
|
|
TB_INLINE_QUERY, |
211
|
|
|
TB_CHOSEN_INLINE_RESULT, |
212
|
|
|
TB_CALLBACK_QUERY |
213
|
|
|
); |
214
|
|
|
} |
215
|
|
|
|
216
|
|
|
if (in_array('user_chat', $tables_to_clean, true)) { |
217
|
|
|
$queries[] = sprintf( |
218
|
|
|
'DELETE FROM `%1$s` |
219
|
|
|
WHERE `user_id` IN ( |
220
|
|
|
SELECT f.id |
221
|
|
|
FROM `%2$s` f |
222
|
|
|
WHERE `updated_at` < \'%3$s\' |
223
|
|
|
) |
224
|
|
|
', |
225
|
|
|
TB_USER_CHAT, |
226
|
|
|
TB_USER, |
227
|
|
|
$clean_older_than['chat'] |
228
|
|
|
); |
229
|
|
|
} |
230
|
|
|
|
231
|
|
|
// Simple. |
232
|
|
|
$simple_tables = [ |
233
|
|
|
'user' => ['table' => TB_USER, 'field' => 'updated_at'], |
234
|
|
|
'chat' => ['table' => TB_CHAT, 'field' => 'updated_at'], |
235
|
|
|
'conversation' => ['table' => TB_CONVERSATION, 'field' => 'updated_at'], |
236
|
|
|
'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'], |
237
|
|
|
]; |
238
|
|
|
|
239
|
|
|
// Botan table is only available if enabled. |
240
|
|
|
if (defined('TB_BOTAN_SHORTENER')) { |
241
|
|
|
$simple_tables['botan_shortener'] = ['table' => TB_BOTAN_SHORTENER, 'field' => 'created_at']; |
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 `date` < \'%2$s\' |
300
|
|
|
AND `id` NOT IN ( |
301
|
|
|
SELECT `message_id` |
302
|
|
|
FROM `%3$s` |
303
|
|
|
WHERE `message_id` = `%1$s`.`id` |
304
|
|
|
) |
305
|
|
|
AND `id` NOT IN ( |
306
|
|
|
SELECT `message_id` |
307
|
|
|
FROM `%4$s` |
308
|
|
|
WHERE `message_id` = `%1$s`.`id` |
309
|
|
|
) |
310
|
|
|
', |
311
|
|
|
TB_MESSAGE, |
312
|
|
|
$clean_older_than['message'], |
313
|
|
|
TB_TELEGRAM_UPDATE, |
314
|
|
|
TB_CALLBACK_QUERY |
315
|
|
|
); |
316
|
|
|
} |
317
|
|
|
|
318
|
|
|
return $queries; |
319
|
|
|
} |
320
|
|
|
|
321
|
|
|
/** |
322
|
|
|
* Command execute method |
323
|
|
|
* |
324
|
|
|
* @return \Longman\TelegramBot\Entities\ServerResponse |
325
|
|
|
* @throws \Longman\TelegramBot\Exception\TelegramException |
326
|
|
|
*/ |
327
|
|
|
public function execute() |
328
|
|
|
{ |
329
|
|
|
$message = $this->getMessage(); |
330
|
|
|
$chat_id = $message->getFrom()->getId(); |
331
|
|
|
$text = $message->getText(true); |
332
|
|
|
|
333
|
|
|
$data = [ |
334
|
|
|
'chat_id' => $chat_id, |
335
|
|
|
'parse_mode' => 'Markdown', |
336
|
|
|
]; |
337
|
|
|
|
338
|
|
|
if (!$message->getChat()->isPrivateChat()) { |
339
|
|
|
$data['text'] = '/cleanup command is only available in a private chat.'; |
340
|
|
|
|
341
|
|
|
return Request::sendMessage($data); |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
$settings = $this->getSettings($text); |
345
|
|
|
$queries = $this->getQueries($settings); |
346
|
|
|
|
347
|
|
|
$infos = []; |
348
|
|
|
foreach ($settings['tables_to_clean'] as $table) { |
349
|
|
|
$info = '*' . $table . '*'; |
350
|
|
|
|
351
|
|
|
if (isset($settings['clean_older_than'][$table])) { |
352
|
|
|
$info .= ' (' . $settings['clean_older_than'][$table] . ')'; |
353
|
|
|
} |
354
|
|
|
|
355
|
|
|
$infos[] = $info; |
356
|
|
|
} |
357
|
|
|
|
358
|
|
|
$data['text'] = 'Cleaning up tables:' . PHP_EOL . implode(PHP_EOL, $infos); |
359
|
|
|
|
360
|
|
|
Request::sendMessage($data); |
361
|
|
|
|
362
|
|
|
$rows = 0; |
363
|
|
|
$pdo = DB::getPdo(); |
364
|
|
|
try { |
365
|
|
|
$pdo->beginTransaction(); |
366
|
|
|
|
367
|
|
|
foreach ($queries as $query) { |
368
|
|
|
if ($dbq = $pdo->query($query)) { |
369
|
|
|
$rows += $dbq->rowCount(); |
370
|
|
|
} else { |
371
|
|
|
TelegramLog::error('Error while executing query: ' . $query); |
372
|
|
|
} |
373
|
|
|
} |
374
|
|
|
|
375
|
|
|
$pdo->commit(); // commit changes to the database and end transaction |
376
|
|
|
} catch (PDOException $e) { |
377
|
|
|
$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...) |
378
|
|
|
|
379
|
|
|
$data['text'] = '*Database cleanup failed!* _(check your error logs)_'; |
380
|
|
|
Request::sendMessage($data); |
381
|
|
|
|
382
|
|
|
throw new TelegramException($e->getMessage()); |
383
|
|
|
} |
384
|
|
|
|
385
|
|
|
if ($rows > 0) { |
386
|
|
|
$data['text'] = '*Database cleanup done!* _(removed ' . $rows . ' rows)_'; |
387
|
|
|
} else { |
388
|
|
|
$data['text'] = '*No data to clean!*'; |
389
|
|
|
} |
390
|
|
|
|
391
|
|
|
return Request::sendMessage($data); |
392
|
|
|
} |
393
|
|
|
} |
394
|
|
|
|