1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Tracking changes on databases, tables and views |
4
|
|
|
*/ |
5
|
|
|
|
6
|
|
|
declare(strict_types=1); |
7
|
|
|
|
8
|
|
|
namespace PhpMyAdmin\Tracking; |
9
|
|
|
|
10
|
|
|
use PhpMyAdmin\Config; |
11
|
|
|
use PhpMyAdmin\ConfigStorage\Features\TrackingFeature; |
12
|
|
|
use PhpMyAdmin\ConfigStorage\Relation; |
13
|
|
|
use PhpMyAdmin\Current; |
14
|
|
|
use PhpMyAdmin\DatabaseInterface; |
15
|
|
|
use PhpMyAdmin\Dbal\ConnectionType; |
16
|
|
|
use PhpMyAdmin\Plugins; |
17
|
|
|
use PhpMyAdmin\Plugins\Export\ExportSql; |
18
|
|
|
use PhpMyAdmin\SqlParser\Parser; |
19
|
|
|
use PhpMyAdmin\SqlParser\Statements\AlterStatement; |
20
|
|
|
use PhpMyAdmin\SqlParser\Statements\CreateStatement; |
21
|
|
|
use PhpMyAdmin\SqlParser\Statements\DeleteStatement; |
22
|
|
|
use PhpMyAdmin\SqlParser\Statements\DropStatement; |
23
|
|
|
use PhpMyAdmin\SqlParser\Statements\InsertStatement; |
24
|
|
|
use PhpMyAdmin\SqlParser\Statements\RenameStatement; |
25
|
|
|
use PhpMyAdmin\SqlParser\Statements\TruncateStatement; |
26
|
|
|
use PhpMyAdmin\SqlParser\Statements\UpdateStatement; |
27
|
|
|
use PhpMyAdmin\Util; |
28
|
|
|
|
29
|
|
|
use function preg_quote; |
30
|
|
|
use function preg_replace; |
31
|
|
|
use function serialize; |
32
|
|
|
use function sprintf; |
33
|
|
|
use function str_ends_with; |
34
|
|
|
use function str_starts_with; |
35
|
|
|
use function trim; |
36
|
|
|
|
37
|
|
|
/** |
38
|
|
|
* This class tracks changes on databases, tables and views. |
39
|
|
|
*/ |
40
|
|
|
class Tracker |
41
|
|
|
{ |
42
|
|
|
private static bool $enabled = false; |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* Cache to avoid quering tracking status multiple times. |
46
|
|
|
* |
47
|
|
|
* @var mixed[] |
48
|
|
|
*/ |
49
|
|
|
protected static array $trackingCache = []; |
50
|
|
|
|
51
|
|
|
/** |
52
|
|
|
* Actually enables tracking. This needs to be done after all |
53
|
|
|
* underlaying code is initialized. |
54
|
|
|
*/ |
55
|
12 |
|
public static function enable(): void |
56
|
|
|
{ |
57
|
12 |
|
self::$enabled = true; |
58
|
|
|
} |
59
|
|
|
|
60
|
120 |
|
public static function disable(): void |
61
|
|
|
{ |
62
|
120 |
|
self::$enabled = false; |
63
|
|
|
} |
64
|
|
|
|
65
|
12 |
|
public static function isEnabled(): bool |
66
|
|
|
{ |
67
|
12 |
|
return self::$enabled; |
68
|
|
|
} |
69
|
|
|
|
70
|
|
|
/** |
71
|
|
|
* Gets the on/off value of the Tracker module, starts initialization. |
72
|
|
|
*/ |
73
|
8 |
|
public static function isActive(): bool |
74
|
|
|
{ |
75
|
8 |
|
if (! self::$enabled) { |
76
|
4 |
|
return false; |
77
|
|
|
} |
78
|
|
|
|
79
|
8 |
|
$relation = new Relation(DatabaseInterface::getInstance()); |
|
|
|
|
80
|
8 |
|
$relationParameters = $relation->getRelationParameters(); |
81
|
|
|
|
82
|
8 |
|
return $relationParameters->trackingFeature !== null; |
83
|
|
|
} |
84
|
|
|
|
85
|
|
|
/** |
86
|
|
|
* Gets the tracking status of a table, is it active or disabled ? |
87
|
|
|
* |
88
|
|
|
* @param string $dbName name of database |
89
|
|
|
* @param string $tableName name of table |
90
|
|
|
*/ |
91
|
4 |
|
public static function isTracked(string $dbName, string $tableName): bool |
92
|
|
|
{ |
93
|
4 |
|
if (! self::$enabled) { |
94
|
4 |
|
return false; |
95
|
|
|
} |
96
|
|
|
|
97
|
4 |
|
if (isset(self::$trackingCache[$dbName][$tableName])) { |
98
|
|
|
return self::$trackingCache[$dbName][$tableName]; |
99
|
|
|
} |
100
|
|
|
|
101
|
4 |
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
102
|
4 |
|
$relation = new Relation($dbi); |
103
|
4 |
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
104
|
4 |
|
if ($trackingFeature === null) { |
105
|
4 |
|
return false; |
106
|
|
|
} |
107
|
|
|
|
108
|
4 |
|
$sqlQuery = sprintf( |
109
|
4 |
|
'SELECT tracking_active FROM %s.%s WHERE db_name = %s AND table_name = %s' |
110
|
4 |
|
. ' ORDER BY version DESC LIMIT 1', |
111
|
4 |
|
Util::backquote($trackingFeature->database), |
112
|
4 |
|
Util::backquote($trackingFeature->tracking), |
113
|
4 |
|
$dbi->quoteString($dbName, ConnectionType::ControlUser), |
114
|
4 |
|
$dbi->quoteString($tableName, ConnectionType::ControlUser), |
115
|
4 |
|
); |
116
|
|
|
|
117
|
4 |
|
$result = $dbi->fetchValue($sqlQuery, 0, ConnectionType::ControlUser) == 1; |
118
|
|
|
|
119
|
4 |
|
self::$trackingCache[$dbName][$tableName] = $result; |
120
|
|
|
|
121
|
4 |
|
return $result; |
122
|
|
|
} |
123
|
|
|
|
124
|
|
|
/** |
125
|
|
|
* Returns the comment line for the log. |
126
|
|
|
* |
127
|
|
|
* @return string Comment, contains date and username |
128
|
|
|
*/ |
129
|
12 |
|
public static function getLogComment(): string |
130
|
|
|
{ |
131
|
12 |
|
$date = Util::date('Y-m-d H:i:s'); |
132
|
12 |
|
$user = preg_replace('/\s+/', ' ', Config::getInstance()->selectedServer['user']); |
|
|
|
|
133
|
|
|
|
134
|
12 |
|
return '# log ' . $date . ' ' . $user . "\n"; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
/** |
138
|
|
|
* Creates tracking version of a table / view |
139
|
|
|
* (in other words: create a job to track future changes on the table). |
140
|
|
|
* |
141
|
|
|
* @param string $dbName name of database |
142
|
|
|
* @param string $tableName name of table |
143
|
|
|
* @param string $version version |
144
|
|
|
* @param string $trackingSet set of tracking statements |
145
|
|
|
* @param bool $isView if table is a view |
146
|
|
|
*/ |
147
|
4 |
|
public static function createVersion( |
148
|
|
|
string $dbName, |
149
|
|
|
string $tableName, |
150
|
|
|
string $version, |
151
|
|
|
string $trackingSet = '', |
152
|
|
|
bool $isView = false, |
153
|
|
|
): bool { |
154
|
4 |
|
$GLOBALS['export_type'] ??= null; |
155
|
4 |
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
156
|
4 |
|
$relation = new Relation($dbi); |
157
|
|
|
|
158
|
4 |
|
$config = Config::getInstance(); |
|
|
|
|
159
|
4 |
|
if ($trackingSet === '') { |
160
|
|
|
$trackingSet = $config->selectedServer['tracking_default_statements']; |
161
|
|
|
} |
162
|
|
|
|
163
|
4 |
|
$exportSqlPlugin = Plugins::getPlugin('export', 'sql', [ |
164
|
4 |
|
'export_type' => (string) $GLOBALS['export_type'], |
165
|
4 |
|
'single_table' => false, |
166
|
4 |
|
]); |
167
|
4 |
|
if (! $exportSqlPlugin instanceof ExportSql) { |
168
|
|
|
return false; |
169
|
|
|
} |
170
|
|
|
|
171
|
4 |
|
$exportSqlPlugin->useSqlBackquotes(true); |
172
|
|
|
|
173
|
4 |
|
$date = Util::date('Y-m-d H:i:s'); |
174
|
|
|
|
175
|
|
|
// Get data definition snapshot of table |
176
|
|
|
|
177
|
4 |
|
$columns = []; |
178
|
4 |
|
foreach ($dbi->getColumns($dbName, $tableName, true) as $column) { |
179
|
4 |
|
$columns[] = [ |
180
|
4 |
|
'Field' => $column->field, |
181
|
4 |
|
'Type' => $column->type, |
182
|
4 |
|
'Collation' => $column->collation, |
183
|
4 |
|
'Null' => $column->isNull ? 'YES' : 'NO', |
184
|
4 |
|
'Key' => $column->key, |
185
|
4 |
|
'Default' => $column->default, |
186
|
4 |
|
'Extra' => $column->extra, |
187
|
4 |
|
'Comment' => $column->comment, |
188
|
4 |
|
]; |
189
|
|
|
} |
190
|
|
|
|
191
|
4 |
|
$indexes = $dbi->getTableIndexes($dbName, $tableName); |
192
|
|
|
|
193
|
4 |
|
$snapshot = ['COLUMNS' => $columns, 'INDEXES' => $indexes]; |
194
|
4 |
|
$snapshot = serialize($snapshot); |
195
|
|
|
|
196
|
|
|
// Get DROP TABLE / DROP VIEW and CREATE TABLE SQL statements |
197
|
4 |
|
$createSql = ''; |
198
|
|
|
|
199
|
4 |
|
if ($config->selectedServer['tracking_add_drop_table'] == true && ! $isView) { |
200
|
|
|
$createSql .= self::getLogComment() |
201
|
|
|
. 'DROP TABLE IF EXISTS ' . Util::backquote($tableName) . ";\n"; |
202
|
|
|
} |
203
|
|
|
|
204
|
4 |
|
if ($config->selectedServer['tracking_add_drop_view'] == true && $isView) { |
205
|
4 |
|
$createSql .= self::getLogComment() |
206
|
4 |
|
. 'DROP VIEW IF EXISTS ' . Util::backquote($tableName) . ";\n"; |
207
|
|
|
} |
208
|
|
|
|
209
|
4 |
|
$createSql .= self::getLogComment() . $exportSqlPlugin->getTableDef($dbName, $tableName); |
210
|
|
|
|
211
|
|
|
// Save version |
212
|
4 |
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
213
|
4 |
|
if ($trackingFeature === null) { |
214
|
|
|
return false; |
215
|
|
|
} |
216
|
|
|
|
217
|
4 |
|
$sqlQuery = sprintf( |
218
|
4 |
|
'/*NOTRACK*/' . "\n" . 'INSERT INTO %s.%s (db_name, table_name, version,' |
219
|
4 |
|
. ' date_created, date_updated, schema_snapshot, schema_sql, data_sql, tracking)' |
220
|
4 |
|
. ' values (%s, %s, %s, %s, %s, %s, %s, %s, %s)', |
221
|
4 |
|
Util::backquote($trackingFeature->database), |
222
|
4 |
|
Util::backquote($trackingFeature->tracking), |
223
|
4 |
|
$dbi->quoteString($dbName, ConnectionType::ControlUser), |
224
|
4 |
|
$dbi->quoteString($tableName, ConnectionType::ControlUser), |
225
|
4 |
|
$dbi->quoteString($version, ConnectionType::ControlUser), |
226
|
4 |
|
$dbi->quoteString($date, ConnectionType::ControlUser), |
227
|
4 |
|
$dbi->quoteString($date, ConnectionType::ControlUser), |
228
|
4 |
|
$dbi->quoteString($snapshot, ConnectionType::ControlUser), |
229
|
4 |
|
$dbi->quoteString($createSql, ConnectionType::ControlUser), |
230
|
4 |
|
$dbi->quoteString("\n", ConnectionType::ControlUser), |
231
|
4 |
|
$dbi->quoteString($trackingSet, ConnectionType::ControlUser), |
232
|
4 |
|
); |
233
|
|
|
|
234
|
4 |
|
$dbi->queryAsControlUser($sqlQuery); |
235
|
|
|
|
236
|
|
|
// Deactivate previous version |
237
|
4 |
|
return self::deactivateTracking($dbName, $tableName, (string) ((int) $version - 1)); |
238
|
|
|
} |
239
|
|
|
|
240
|
|
|
/** |
241
|
|
|
* Creates tracking version of a database |
242
|
|
|
* (in other words: create a job to track future changes on the database). |
243
|
|
|
* |
244
|
|
|
* @param string $dbName name of database |
245
|
|
|
* @param string $version version |
246
|
|
|
* @param string $query query |
247
|
|
|
* @param string $trackingSet set of tracking statements |
248
|
|
|
*/ |
249
|
4 |
|
public static function createDatabaseVersion( |
250
|
|
|
string $dbName, |
251
|
|
|
string $version, |
252
|
|
|
string $query, |
253
|
|
|
string $trackingSet = 'CREATE DATABASE,ALTER DATABASE,DROP DATABASE', |
254
|
|
|
): bool { |
255
|
4 |
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
256
|
4 |
|
$relation = new Relation($dbi); |
257
|
|
|
|
258
|
4 |
|
$date = Util::date('Y-m-d H:i:s'); |
259
|
|
|
|
260
|
4 |
|
$config = Config::getInstance(); |
|
|
|
|
261
|
4 |
|
if ($trackingSet === '') { |
262
|
|
|
$trackingSet = $config->selectedServer['tracking_default_statements']; |
263
|
|
|
} |
264
|
|
|
|
265
|
4 |
|
$createSql = ''; |
266
|
|
|
|
267
|
4 |
|
if ($config->selectedServer['tracking_add_drop_database'] == true) { |
268
|
|
|
$createSql .= self::getLogComment() . 'DROP DATABASE IF EXISTS ' . Util::backquote($dbName) . ";\n"; |
269
|
|
|
} |
270
|
|
|
|
271
|
4 |
|
$createSql .= self::getLogComment() . $query; |
272
|
|
|
|
273
|
4 |
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
274
|
4 |
|
if ($trackingFeature === null) { |
275
|
|
|
return false; |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
// Save version |
279
|
4 |
|
$sqlQuery = sprintf( |
280
|
4 |
|
'/*NOTRACK*/' . "\n" . 'INSERT INTO %s.%s (db_name, table_name, version,' |
281
|
4 |
|
. ' date_created, date_updated, schema_snapshot, schema_sql, data_sql, tracking)' |
282
|
4 |
|
. ' values (%s, %s, %s, %s, %s, %s, %s, %s, %s)', |
283
|
4 |
|
Util::backquote($trackingFeature->database), |
284
|
4 |
|
Util::backquote($trackingFeature->tracking), |
285
|
4 |
|
$dbi->quoteString($dbName, ConnectionType::ControlUser), |
286
|
4 |
|
$dbi->quoteString('', ConnectionType::ControlUser), |
287
|
4 |
|
$dbi->quoteString($version, ConnectionType::ControlUser), |
288
|
4 |
|
$dbi->quoteString($date, ConnectionType::ControlUser), |
289
|
4 |
|
$dbi->quoteString($date, ConnectionType::ControlUser), |
290
|
4 |
|
$dbi->quoteString('', ConnectionType::ControlUser), |
291
|
4 |
|
$dbi->quoteString($createSql, ConnectionType::ControlUser), |
292
|
4 |
|
$dbi->quoteString("\n", ConnectionType::ControlUser), |
293
|
4 |
|
$dbi->quoteString($trackingSet, ConnectionType::ControlUser), |
294
|
4 |
|
); |
295
|
|
|
|
296
|
4 |
|
return (bool) $dbi->queryAsControlUser($sqlQuery); |
297
|
|
|
} |
298
|
|
|
|
299
|
|
|
/** |
300
|
|
|
* Changes tracking of a table. |
301
|
|
|
* |
302
|
|
|
* @param string $dbName name of database |
303
|
|
|
* @param string $tableName name of table |
304
|
|
|
* @param string $version version |
305
|
|
|
* @param int $newState the new state of tracking |
306
|
|
|
*/ |
307
|
16 |
|
private static function changeTracking( |
308
|
|
|
string $dbName, |
309
|
|
|
string $tableName, |
310
|
|
|
string $version, |
311
|
|
|
int $newState, |
312
|
|
|
): bool { |
313
|
16 |
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
314
|
16 |
|
$relation = new Relation($dbi); |
315
|
16 |
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
316
|
16 |
|
if ($trackingFeature === null) { |
317
|
|
|
return false; |
318
|
|
|
} |
319
|
|
|
|
320
|
16 |
|
$sqlQuery = sprintf( |
321
|
16 |
|
'UPDATE %s.%s SET `tracking_active` = %d' |
322
|
16 |
|
. ' WHERE `db_name` = %s AND `table_name` = %s AND `version` = %s', |
323
|
16 |
|
Util::backquote($trackingFeature->database), |
324
|
16 |
|
Util::backquote($trackingFeature->tracking), |
325
|
16 |
|
$newState, |
326
|
16 |
|
$dbi->quoteString($dbName, ConnectionType::ControlUser), |
327
|
16 |
|
$dbi->quoteString($tableName, ConnectionType::ControlUser), |
328
|
16 |
|
$dbi->quoteString($version, ConnectionType::ControlUser), |
329
|
16 |
|
); |
330
|
|
|
|
331
|
16 |
|
return (bool) $dbi->queryAsControlUser($sqlQuery); |
332
|
|
|
} |
333
|
|
|
|
334
|
|
|
/** |
335
|
|
|
* Activates tracking of a table. |
336
|
|
|
* |
337
|
|
|
* @param string $dbname name of database |
338
|
|
|
* @param string $tablename name of table |
339
|
|
|
* @param string $version version |
340
|
|
|
*/ |
341
|
4 |
|
public static function activateTracking(string $dbname, string $tablename, string $version): bool |
342
|
|
|
{ |
343
|
4 |
|
return self::changeTracking($dbname, $tablename, $version, 1); |
344
|
|
|
} |
345
|
|
|
|
346
|
|
|
/** |
347
|
|
|
* Deactivates tracking of a table. |
348
|
|
|
* |
349
|
|
|
* @param string $dbname name of database |
350
|
|
|
* @param string $tablename name of table |
351
|
|
|
* @param string $version version |
352
|
|
|
*/ |
353
|
8 |
|
public static function deactivateTracking(string $dbname, string $tablename, string $version): bool |
354
|
|
|
{ |
355
|
8 |
|
return self::changeTracking($dbname, $tablename, $version, 0); |
356
|
|
|
} |
357
|
|
|
|
358
|
|
|
/** |
359
|
|
|
* Gets the newest version of a tracking job |
360
|
|
|
* (in other words: gets the HEAD version). |
361
|
|
|
* |
362
|
|
|
* @param string $dbname name of database |
363
|
|
|
* @param string $tablename name of table |
364
|
|
|
* @param string|null $statement tracked statement |
365
|
|
|
* |
366
|
|
|
* @return int (-1 if no version exists | > 0 if a version exists) |
367
|
|
|
*/ |
368
|
|
|
private static function getVersion(string $dbname, string $tablename, string|null $statement = null): int |
369
|
|
|
{ |
370
|
|
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
371
|
|
|
$relation = new Relation($dbi); |
372
|
|
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
373
|
|
|
if ($trackingFeature === null) { |
374
|
|
|
return -1; |
375
|
|
|
} |
376
|
|
|
|
377
|
|
|
$sqlQuery = sprintf( |
378
|
|
|
'SELECT MAX(version) FROM %s.%s WHERE `db_name` = %s AND `table_name` = %s', |
379
|
|
|
Util::backquote($trackingFeature->database), |
380
|
|
|
Util::backquote($trackingFeature->tracking), |
381
|
|
|
$dbi->quoteString($dbname, ConnectionType::ControlUser), |
382
|
|
|
$dbi->quoteString($tablename, ConnectionType::ControlUser), |
383
|
|
|
); |
384
|
|
|
|
385
|
|
|
if ($statement != '') { |
386
|
|
|
$sqlQuery .= " AND FIND_IN_SET('" . $statement . "',tracking) > 0"; |
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
$result = $dbi->tryQueryAsControlUser($sqlQuery); |
390
|
|
|
|
391
|
|
|
if ($result === false) { |
392
|
|
|
return -1; |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
$row = $result->fetchRow(); |
396
|
|
|
|
397
|
|
|
return (int) ($row[0] ?? -1); |
398
|
|
|
} |
399
|
|
|
|
400
|
|
|
/** |
401
|
|
|
* Parses a query. Gets |
402
|
|
|
* - statement identifier (UPDATE, ALTER TABLE, ...) |
403
|
|
|
* - type of statement, is it part of DDL or DML ? |
404
|
|
|
* - tablename |
405
|
|
|
* |
406
|
|
|
* @param string $query query |
407
|
|
|
* |
408
|
|
|
* @return mixed[] containing identifier, type and tablename. |
409
|
|
|
* |
410
|
|
|
* @todo: using PMA SQL Parser when possible |
411
|
|
|
* @todo: support multi-table/view drops |
412
|
|
|
*/ |
413
|
84 |
|
public static function parseQuery(string $query): array |
414
|
|
|
{ |
415
|
|
|
// Usage of PMA_SQP does not work here |
416
|
|
|
// |
417
|
|
|
// require_once("libraries/sqlparser.lib.php"); |
418
|
|
|
// $parsed_sql = PMA_SQP_parse($query); |
419
|
|
|
// $sql_info = PMA_SQP_analyze($parsed_sql); |
420
|
|
|
|
421
|
84 |
|
$parser = new Parser($query); |
422
|
|
|
|
423
|
84 |
|
$tokens = $parser->list->tokens; |
424
|
|
|
|
425
|
|
|
// Parse USE statement, need it for SQL dump imports |
426
|
84 |
|
if ($tokens[0]->value === 'USE') { |
427
|
|
|
Current::$database = $tokens[2]->value; |
428
|
|
|
} |
429
|
|
|
|
430
|
84 |
|
$result = []; |
431
|
|
|
|
432
|
84 |
|
if ($parser->statements !== []) { |
433
|
84 |
|
$statement = $parser->statements[0]; |
434
|
84 |
|
$options = $statement->options?->options; |
435
|
|
|
|
436
|
|
|
// DDL statements |
437
|
84 |
|
$result['type'] = 'DDL'; |
438
|
|
|
|
439
|
|
|
// Parse CREATE statement |
440
|
84 |
|
if ($statement instanceof CreateStatement) { |
441
|
28 |
|
if ($options === null || $options === [] || ! isset($options[6])) { |
442
|
|
|
return $result; |
443
|
|
|
} |
444
|
|
|
|
445
|
28 |
|
if ($options[6] === 'VIEW' || $options[6] === 'TABLE') { |
446
|
12 |
|
$result['identifier'] = 'CREATE ' . $options[6]; |
447
|
12 |
|
$result['tablename'] = $statement->name?->table; |
448
|
16 |
|
} elseif ($options[6] === 'DATABASE') { |
449
|
4 |
|
$result['identifier'] = 'CREATE DATABASE'; |
450
|
4 |
|
$result['tablename'] = ''; |
451
|
|
|
|
452
|
|
|
// In case of CREATE DATABASE, database field of the CreateStatement is the name of the database |
453
|
4 |
|
Current::$database = $statement->name?->database; |
454
|
|
|
} elseif ( |
455
|
12 |
|
$options[6] === 'INDEX' |
456
|
8 |
|
|| $options[6] === 'UNIQUE INDEX' |
457
|
4 |
|
|| $options[6] === 'FULLTEXT INDEX' |
458
|
12 |
|
|| $options[6] === 'SPATIAL INDEX' |
459
|
|
|
) { |
460
|
12 |
|
$result['identifier'] = 'CREATE INDEX'; |
461
|
|
|
|
462
|
|
|
// In case of CREATE INDEX, we have to get the table name from body of the statement |
463
|
12 |
|
$result['tablename'] = $statement->body[3]->value === '.' ? $statement->body[4]->value |
464
|
14 |
|
: $statement->body[2]->value; |
465
|
|
|
} |
466
|
56 |
|
} elseif ($statement instanceof AlterStatement) { // Parse ALTER statement |
467
|
12 |
|
if ($options === null || $options === [] || ! isset($options[3])) { |
468
|
|
|
return $result; |
469
|
|
|
} |
470
|
|
|
|
471
|
12 |
|
if ($options[3] === 'VIEW' || $options[3] === 'TABLE') { |
472
|
8 |
|
$result['identifier'] = 'ALTER ' . $options[3]; |
473
|
8 |
|
$result['tablename'] = $statement->table->table; |
474
|
4 |
|
} elseif ($options[3] === 'DATABASE') { |
475
|
4 |
|
$result['identifier'] = 'ALTER DATABASE'; |
476
|
4 |
|
$result['tablename'] = ''; |
477
|
|
|
|
478
|
8 |
|
Current::$database = $statement->table->table; |
479
|
|
|
} |
480
|
44 |
|
} elseif ($statement instanceof DropStatement) { // Parse DROP statement |
481
|
24 |
|
if ($options === null || $options === [] || ! isset($options[1])) { |
482
|
|
|
return $result; |
483
|
|
|
} |
484
|
|
|
|
485
|
24 |
|
if ($options[1] === 'VIEW' || $options[1] === 'TABLE') { |
486
|
16 |
|
$result['identifier'] = 'DROP ' . $options[1]; |
487
|
16 |
|
$result['tablename'] = $statement->fields[0]->table; |
488
|
8 |
|
} elseif ($options[1] === 'DATABASE') { |
489
|
4 |
|
$result['identifier'] = 'DROP DATABASE'; |
490
|
4 |
|
$result['tablename'] = ''; |
491
|
|
|
|
492
|
4 |
|
Current::$database = $statement->fields[0]->table; |
493
|
4 |
|
} elseif ($options[1] === 'INDEX') { |
494
|
4 |
|
$result['identifier'] = 'DROP INDEX'; |
495
|
14 |
|
$result['tablename'] = $statement->table->table; |
496
|
|
|
} |
497
|
20 |
|
} elseif ($statement instanceof RenameStatement) { // Parse RENAME statement |
498
|
4 |
|
$result['identifier'] = 'RENAME TABLE'; |
499
|
4 |
|
$result['tablename'] = $statement->renames[0]->old->table; |
500
|
4 |
|
$result['tablename_after_rename'] = $statement->renames[0]->new->table; |
501
|
|
|
} |
502
|
|
|
|
503
|
84 |
|
if (isset($result['identifier'])) { |
504
|
68 |
|
return $result; |
505
|
|
|
} |
506
|
|
|
|
507
|
|
|
// DML statements |
508
|
16 |
|
$result['type'] = 'DML'; |
509
|
|
|
|
510
|
|
|
// Parse UPDATE statement |
511
|
16 |
|
if ($statement instanceof UpdateStatement) { |
512
|
4 |
|
$result['identifier'] = 'UPDATE'; |
513
|
4 |
|
$result['tablename'] = $statement->tables[0]->table; |
514
|
|
|
} |
515
|
|
|
|
516
|
|
|
// Parse INSERT INTO statement |
517
|
16 |
|
if ($statement instanceof InsertStatement) { |
518
|
4 |
|
$result['identifier'] = 'INSERT'; |
519
|
4 |
|
$result['tablename'] = $statement->into->dest->table; |
520
|
|
|
} |
521
|
|
|
|
522
|
|
|
// Parse DELETE statement |
523
|
16 |
|
if ($statement instanceof DeleteStatement) { |
524
|
4 |
|
$result['identifier'] = 'DELETE'; |
525
|
4 |
|
$result['tablename'] = $statement->from[0]->table; |
526
|
|
|
} |
527
|
|
|
|
528
|
|
|
// Parse TRUNCATE statement |
529
|
16 |
|
if ($statement instanceof TruncateStatement) { |
530
|
4 |
|
$result['identifier'] = 'TRUNCATE'; |
531
|
4 |
|
$result['tablename'] = $statement->table->table; |
532
|
|
|
} |
533
|
|
|
} |
534
|
|
|
|
535
|
16 |
|
return $result; |
536
|
|
|
} |
537
|
|
|
|
538
|
|
|
/** |
539
|
|
|
* Analyzes a given SQL statement and saves tracking data. |
540
|
|
|
* |
541
|
|
|
* @param string $query a SQL query |
542
|
|
|
*/ |
543
|
4 |
|
public static function handleQuery(string $query): void |
544
|
|
|
{ |
545
|
|
|
// If query is marked as untouchable, leave |
546
|
4 |
|
if (str_starts_with($query, '/*NOTRACK*/')) { |
547
|
|
|
return; |
548
|
|
|
} |
549
|
|
|
|
550
|
4 |
|
if (! str_ends_with($query, ';')) { |
551
|
4 |
|
$query .= ";\n"; |
552
|
|
|
} |
553
|
|
|
|
554
|
|
|
// Get database name |
555
|
4 |
|
$dbname = trim(Current::$database, '`'); |
556
|
|
|
// $dbname can be empty, for example when coming from Synchronize |
557
|
|
|
// and this is a query for the remote server |
558
|
4 |
|
if ($dbname === '') { |
559
|
4 |
|
return; |
560
|
|
|
} |
561
|
|
|
|
562
|
|
|
$dbi = DatabaseInterface::getInstance(); |
|
|
|
|
563
|
|
|
$relation = new Relation($dbi); |
564
|
|
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
565
|
|
|
if ($trackingFeature === null) { |
566
|
|
|
return; |
567
|
|
|
} |
568
|
|
|
|
569
|
|
|
if (! self::isAnyTrackingInProgress($dbi, $trackingFeature, $dbname)) { |
570
|
|
|
return; |
571
|
|
|
} |
572
|
|
|
|
573
|
|
|
// Get some information about query |
574
|
|
|
$result = self::parseQuery($query); |
575
|
|
|
|
576
|
|
|
// If we found a valid statement |
577
|
|
|
if (! isset($result['identifier'])) { |
578
|
|
|
return; |
579
|
|
|
} |
580
|
|
|
|
581
|
|
|
// The table name was not found, see issue: #16837 as an example |
582
|
|
|
// Also checks if the value is not null |
583
|
|
|
if (! isset($result['tablename'])) { |
584
|
|
|
return; |
585
|
|
|
} |
586
|
|
|
|
587
|
|
|
$version = self::getVersion($dbname, $result['tablename'], $result['identifier']); |
588
|
|
|
|
589
|
|
|
// If version not exists and auto-creation is enabled |
590
|
|
|
if (Config::getInstance()->selectedServer['tracking_version_auto_create'] == true && $version == -1) { |
|
|
|
|
591
|
|
|
// Create the version |
592
|
|
|
|
593
|
|
|
switch ($result['identifier']) { |
594
|
|
|
case 'CREATE TABLE': |
595
|
|
|
self::createVersion($dbname, $result['tablename'], '1'); |
596
|
|
|
break; |
597
|
|
|
case 'CREATE VIEW': |
598
|
|
|
self::createVersion($dbname, $result['tablename'], '1', '', true); |
599
|
|
|
break; |
600
|
|
|
case 'CREATE DATABASE': |
601
|
|
|
self::createDatabaseVersion($dbname, '1', $query); |
602
|
|
|
break; |
603
|
|
|
} |
604
|
|
|
} |
605
|
|
|
|
606
|
|
|
// If version exists |
607
|
|
|
if ($version == -1) { |
608
|
|
|
return; |
609
|
|
|
} |
610
|
|
|
|
611
|
|
|
if (! self::isTracked($dbname, $result['tablename'])) { |
612
|
|
|
return; |
613
|
|
|
} |
614
|
|
|
|
615
|
|
|
$saveTo = match ($result['type']) { |
616
|
|
|
'DDL' => 'schema_sql', |
617
|
|
|
'DML' => 'data_sql', |
618
|
|
|
default => '', |
619
|
|
|
}; |
620
|
|
|
|
621
|
|
|
$date = Util::date('Y-m-d H:i:s'); |
622
|
|
|
|
623
|
|
|
// Cut off `dbname`. from query |
624
|
|
|
$query = preg_replace( |
625
|
|
|
'/`' . preg_quote($dbname, '/') . '`\s?\./', |
626
|
|
|
'', |
627
|
|
|
$query, |
628
|
|
|
); |
629
|
|
|
|
630
|
|
|
// Add log information |
631
|
|
|
$query = self::getLogComment() . $query; |
632
|
|
|
|
633
|
|
|
$relation = new Relation($dbi); |
634
|
|
|
$trackingFeature = $relation->getRelationParameters()->trackingFeature; |
635
|
|
|
if ($trackingFeature === null) { |
636
|
|
|
return; |
637
|
|
|
} |
638
|
|
|
|
639
|
|
|
// Mark it as untouchable |
640
|
|
|
$sqlQuery = sprintf( |
641
|
|
|
'/*NOTRACK*/' . "\n" . 'UPDATE %s.%s SET %s = CONCAT(%s, %s), `date_updated` = %s', |
642
|
|
|
Util::backquote($trackingFeature->database), |
643
|
|
|
Util::backquote($trackingFeature->tracking), |
644
|
|
|
Util::backquote($saveTo), |
645
|
|
|
Util::backquote($saveTo), |
646
|
|
|
$dbi->quoteString("\n" . $query, ConnectionType::ControlUser), |
647
|
|
|
$dbi->quoteString($date, ConnectionType::ControlUser), |
648
|
|
|
); |
649
|
|
|
|
650
|
|
|
// If table was renamed we have to change |
651
|
|
|
// the tablename attribute in pma_tracking too |
652
|
|
|
if ($result['identifier'] === 'RENAME TABLE') { |
653
|
|
|
$sqlQuery .= ', `table_name` = ' |
654
|
|
|
. $dbi->quoteString($result['tablename_after_rename'], ConnectionType::ControlUser) |
655
|
|
|
. ' '; |
656
|
|
|
} |
657
|
|
|
|
658
|
|
|
// Save the tracking information only for |
659
|
|
|
// 1. the database |
660
|
|
|
// 2. the table / view |
661
|
|
|
// 3. the statements |
662
|
|
|
// we want to track |
663
|
|
|
$sqlQuery .= sprintf( |
664
|
|
|
" WHERE FIND_IN_SET('" . $result['identifier'] . "',tracking) > 0" . |
665
|
|
|
' AND `db_name` = %s ' . |
666
|
|
|
' AND `table_name` = %s ' . |
667
|
|
|
' AND `version` = %s ', |
668
|
|
|
$dbi->quoteString($dbname, ConnectionType::ControlUser), |
669
|
|
|
$dbi->quoteString($result['tablename'], ConnectionType::ControlUser), |
670
|
|
|
$dbi->quoteString((string) $version, ConnectionType::ControlUser), |
671
|
|
|
); |
672
|
|
|
|
673
|
|
|
$dbi->queryAsControlUser($sqlQuery); |
674
|
|
|
} |
675
|
|
|
|
676
|
|
|
private static function isAnyTrackingInProgress( |
677
|
|
|
DatabaseInterface $dbi, |
678
|
|
|
TrackingFeature $trackingFeature, |
679
|
|
|
string $dbname, |
680
|
|
|
): bool { |
681
|
|
|
$sqlQuery = sprintf( |
682
|
|
|
'/*NOTRACK*/ SELECT 1 FROM %s.%s WHERE tracking_active = 1 AND db_name = %s LIMIT 1', |
683
|
|
|
Util::backquote($trackingFeature->database), |
684
|
|
|
Util::backquote($trackingFeature->tracking), |
685
|
|
|
$dbi->quoteString($dbname, ConnectionType::ControlUser), |
686
|
|
|
); |
687
|
|
|
|
688
|
|
|
return $dbi->queryAsControlUser($sqlQuery)->fetchValue() !== false; |
689
|
|
|
} |
690
|
|
|
} |
691
|
|
|
|
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.