Completed
Push — master ( 6d1075...fae82d )
by Armando
20:31 queued 14:28
created

CleanupCommand::getSettings()   B

Complexity

Conditions 5
Paths 4

Size

Total Lines 26
Code Lines 15

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 30

Importance

Changes 1
Bugs 0 Features 0
Metric Value
c 1
b 0
f 0
dl 0
loc 26
ccs 0
cts 22
cp 0
rs 8.439
cc 5
eloc 15
nc 4
nop 1
crap 30
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