libraries/Tracker.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Tracking changes on databases, tables and views
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 namespace PMA\libraries;
   9
  10 use PMA\libraries\plugins\export\ExportSql;
  11
  12 /**
  13  * This class tracks changes on databases, tables and views.
  14  *
  15  * @package PhpMyAdmin
  16  *
  17  * @todo use stristr instead of strstr
  18  */
  19 class Tracker
  20 {
  21     /**
  22      * Whether tracking is ready.
  23      */
  24     static protected $enabled = false;
  25
  26     /**
  27      * Actually enables tracking. This needs to be done after all
  28      * underlaying code is initialized.
  29      *
  30      * @static
  31      *
  32      * @return void
  33      */
  34     static public function enable()
  35     {
  36         self::$enabled = true;
  37     }
  38
  39     /**
  40      * Gets the on/off value of the Tracker module, starts initialization.
  41      *
  42      * @static
  43      *
  44      * @return boolean (true=on|false=off)
  45      */
  46     static public function isActive()
  47     {
  48         if (! self::$enabled) {
  49             return false;
  50         }
  51         /* We need to avoid attempt to track any queries
  52          * from PMA_getRelationsParam
  53          */
  54         self::$enabled = false;
  55         $cfgRelation = PMA_getRelationsParam();
  56         /* Restore original state */
  57         self::$enabled = true;
  58         if (! $cfgRelation['trackingwork']) {
  59             return false;
  60         }
  61
  62         $pma_table = self::_getTrackingTable();
  63         if (isset($pma_table)) {
  64             return true;
  65         } else {
  66             return false;
  67         }
  68     }
  69
  70     /**
  71      * Parses the name of a table from a SQL statement substring.
  72      *
  73      * @param string $string part of SQL statement
  74      *
  75      * @static
  76      *
  77      * @return string the name of table
  78      */
  79     static protected function getTableName($string)
  80     {
  81         if (mb_strstr($string, '.')) {
  82             $temp = explode('.', $string);
  83             $tablename = $temp[1];
  84         } else {
  85             $tablename = $string;
  86         }
  87
  88         $str = explode("\n", $tablename);
  89         $tablename = $str[0];
  90
  91         $tablename = str_replace(';', '', $tablename);
  92         $tablename = str_replace('`', '', $tablename);
  93         $tablename = trim($tablename);
  94
  95         return $tablename;
  96     }
  97
  98
  99     /**
 100      * Gets the tracking status of a table, is it active or deactive ?
 101      *
 102      * @param string $dbname    name of database
 103      * @param string $tablename name of table
 104      *
 105      * @static
 106      *
 107      * @return boolean true or false
 108      */
 109     static public function isTracked($dbname, $tablename)
 110     {
 111         if (! self::$enabled) {
 112             return false;
 113         }
 114         /* We need to avoid attempt to track any queries
 115          * from PMA_getRelationsParam
 116          */
 117         self::$enabled = false;
 118         $cfgRelation = PMA_getRelationsParam();
 119         /* Restore original state */
 120         self::$enabled = true;
 121         if (! $cfgRelation['trackingwork']) {
 122             return false;
 123         }
 124
 125         $sql_query = " SELECT tracking_active FROM " . self::_getTrackingTable() .
 126         " WHERE db_name = '" . Util::sqlAddSlashes($dbname) . "' " .
 127         " AND table_name = '" . Util::sqlAddSlashes($tablename) . "' " .
 128         " ORDER BY version DESC";
 129
 130         $row = $GLOBALS['dbi']->fetchArray(PMA_queryAsControlUser($sql_query));
 131
 132         if (isset($row['tracking_active']) && $row['tracking_active'] == 1) {
 133             return true;
 134         } else {
 135             return false;
 136         }
 137     }
 138
 139     /**
 140      * Returns the comment line for the log.
 141      *
 142      * @return string Comment, contains date and username
 143      */
 144     static public function getLogComment()
 145     {
 146         $date = date('Y-m-d H:i:s');
 147
 148         return "# log " . $date . " " . $GLOBALS['cfg']['Server']['user'] . "\n";
 149     }
 150
 151     /**
 152      * Creates tracking version of a table / view
 153      * (in other words: create a job to track future changes on the table).
 154      *
 155      * @param string $dbname       name of database
 156      * @param string $tablename    name of table
 157      * @param string $version      version
 158      * @param string $tracking_set set of tracking statements
 159      * @param bool   $is_view      if table is a view
 160      *
 161      * @static
 162      *
 163      * @return int result of version insertion
 164      */
 165     static public function createVersion($dbname, $tablename, $version,
 166         $tracking_set = '', $is_view = false
 167     ) {
 168         global $sql_backquotes, $export_type;
 169
 170         if ($tracking_set == '') {
 171             $tracking_set
 172                 = $GLOBALS['cfg']['Server']['tracking_default_statements'];
 173         }
 174
 175         // get Export SQL instance
 176         include_once "libraries/plugin_interface.lib.php";
 177         /* @var $export_sql_plugin \PMA\libraries\plugins\export\ExportSql */
 178         $export_sql_plugin = PMA_getPlugin(
 179             "export",
 180             "sql",
 181             'libraries/plugins/export/',
 182             array(
 183                 'export_type' => $export_type,
 184                 'single_table' => false,
 185             )
 186         );
 187
 188         $sql_backquotes = true;
 189
 190         $date = date('Y-m-d H:i:s');
 191
 192         // Get data definition snapshot of table
 193
 194         $columns = $GLOBALS['dbi']->getColumns($dbname, $tablename, null, true);
 195         // int indices to reduce size
 196         $columns = array_values($columns);
 197         // remove Privileges to reduce size
 198         for ($i = 0, $nb = count($columns); $i < $nb; $i++) {
 199             unset($columns[$i]['Privileges']);
 200         }
 201
 202         $indexes = $GLOBALS['dbi']->getTableIndexes($dbname, $tablename);
 203
 204         $snapshot = array('COLUMNS' => $columns, 'INDEXES' => $indexes);
 205         $snapshot = serialize($snapshot);
 206
 207         // Get DROP TABLE / DROP VIEW and CREATE TABLE SQL statements
 208         $sql_backquotes = true;
 209
 210         $create_sql  = "";
 211
 212         if ($GLOBALS['cfg']['Server']['tracking_add_drop_table'] == true
 213             && $is_view == false
 214         ) {
 215             $create_sql .= self::getLogComment()
 216                 . 'DROP TABLE IF EXISTS ' . Util::backquote($tablename) . ";\n";
 217
 218         }
 219
 220         if ($GLOBALS['cfg']['Server']['tracking_add_drop_view'] == true
 221             && $is_view == true
 222         ) {
 223             $create_sql .= self::getLogComment()
 224                 . 'DROP VIEW IF EXISTS ' . Util::backquote($tablename) . ";\n";
 225         }
 226
 227         $create_sql .= self::getLogComment() .
 228             $export_sql_plugin->getTableDef($dbname, $tablename, "\n", "");
 229
 230         // Save version
 231
 232         $sql_query = "/*NOTRACK*/\n" .
 233         "INSERT INTO " . self::_getTrackingTable() . " (" .
 234         "db_name, " .
 235         "table_name, " .
 236         "version, " .
 237         "date_created, " .
 238         "date_updated, " .
 239         "schema_snapshot, " .
 240         "schema_sql, " .
 241         "data_sql, " .
 242         "tracking " .
 243         ") " .
 244         "values (
 245         '" . Util::sqlAddSlashes($dbname) . "',
 246         '" . Util::sqlAddSlashes($tablename) . "',
 247         '" . Util::sqlAddSlashes($version) . "',
 248         '" . Util::sqlAddSlashes($date) . "',
 249         '" . Util::sqlAddSlashes($date) . "',
 250         '" . Util::sqlAddSlashes($snapshot) . "',
 251         '" . Util::sqlAddSlashes($create_sql) . "',
 252         '" . Util::sqlAddSlashes("\n") . "',
 253         '" . Util::sqlAddSlashes($tracking_set)
 254         . "' )";
 255
 256         $result = PMA_queryAsControlUser($sql_query);
 257
 258         if ($result) {
 259             // Deactivate previous version
 260             self::deactivateTracking($dbname, $tablename, ($version - 1));
 261         }
 262
 263         return $result;
 264     }
 265
 266
 267     /**
 268      * Removes all tracking data for a table or a version of a table
 269      *
 270      * @param string $dbname    name of database
 271      * @param string $tablename name of table
 272      * @param string $version   version
 273      *
 274      * @static
 275      *
 276      * @return int result of version insertion
 277      */
 278     static public function deleteTracking($dbname, $tablename, $version = '')
 279     {
 280         $sql_query = "/*NOTRACK*/\n"
 281             . "DELETE FROM " . self::_getTrackingTable()
 282             . " WHERE `db_name` = '"
 283             . Util::sqlAddSlashes($dbname) . "'"
 284             . " AND `table_name` = '"
 285             . Util::sqlAddSlashes($tablename) . "'";
 286         if ($version) {
 287             $sql_query .= " AND `version` = '"
 288                 . Util::sqlAddSlashes($version) . "'";
 289         }
 290         $result = PMA_queryAsControlUser($sql_query);
 291
 292         return $result;
 293     }
 294
 295     /**
 296      * Creates tracking version of a database
 297      * (in other words: create a job to track future changes on the database).
 298      *
 299      * @param string $dbname       name of database
 300      * @param string $version      version
 301      * @param string $query        query
 302      * @param string $tracking_set set of tracking statements
 303      *
 304      * @static
 305      *
 306      * @return int result of version insertion
 307      */
 308     static public function createDatabaseVersion($dbname, $version, $query,
 309         $tracking_set = 'CREATE DATABASE,ALTER DATABASE,DROP DATABASE'
 310     ) {
 311         $date = date('Y-m-d H:i:s');
 312
 313         if ($tracking_set == '') {
 314             $tracking_set
 315                 = $GLOBALS['cfg']['Server']['tracking_default_statements'];
 316         }
 317
 318         $create_sql  = "";
 319
 320         if ($GLOBALS['cfg']['Server']['tracking_add_drop_database'] == true) {
 321             $create_sql .= self::getLogComment()
 322                 . 'DROP DATABASE IF EXISTS ' . Util::backquote($dbname) . ";\n";
 323         }
 324
 325         $create_sql .= self::getLogComment() . $query;
 326
 327         // Save version
 328         $sql_query = "/*NOTRACK*/\n" .
 329         "INSERT INTO " . self::_getTrackingTable() . " (" .
 330         "db_name, " .
 331         "table_name, " .
 332         "version, " .
 333         "date_created, " .
 334         "date_updated, " .
 335         "schema_snapshot, " .
 336         "schema_sql, " .
 337         "data_sql, " .
 338         "tracking " .
 339         ") " .
 340         "values (
 341         '" . Util::sqlAddSlashes($dbname) . "',
 342         '" . Util::sqlAddSlashes('') . "',
 343         '" . Util::sqlAddSlashes($version) . "',
 344         '" . Util::sqlAddSlashes($date) . "',
 345         '" . Util::sqlAddSlashes($date) . "',
 346         '" . Util::sqlAddSlashes('') . "',
 347         '" . Util::sqlAddSlashes($create_sql) . "',
 348         '" . Util::sqlAddSlashes("\n") . "',
 349         '" . Util::sqlAddSlashes($tracking_set)
 350         . "' )";
 351
 352         $result = PMA_queryAsControlUser($sql_query);
 353
 354         return $result;
 355     }
 356
 357
 358
 359     /**
 360      * Changes tracking of a table.
 361      *
 362      * @param string  $dbname    name of database
 363      * @param string  $tablename name of table
 364      * @param string  $version   version
 365      * @param integer $new_state the new state of tracking
 366      *
 367      * @static
 368      *
 369      * @return int result of SQL query
 370      */
 371     static private function _changeTracking($dbname, $tablename,
 372         $version, $new_state
 373     ) {
 374
 375         $sql_query = " UPDATE " . self::_getTrackingTable() .
 376         " SET `tracking_active` = '" . $new_state . "' " .
 377         " WHERE `db_name` = '" . Util::sqlAddSlashes($dbname) . "' " .
 378         " AND `table_name` = '" . Util::sqlAddSlashes($tablename) . "' " .
 379         " AND `version` = '" . Util::sqlAddSlashes($version) . "' ";
 380
 381         $result = PMA_queryAsControlUser($sql_query);
 382
 383         return $result;
 384     }
 385
 386     /**
 387      * Changes tracking data of a table.
 388      *
 389      * @param string       $dbname    name of database
 390      * @param string       $tablename name of table
 391      * @param string       $version   version
 392      * @param string       $type      type of data(DDL || DML)
 393      * @param string|array $new_data  the new tracking data
 394      *
 395      * @static
 396      *
 397      * @return bool result of change
 398      */
 399     static public function changeTrackingData($dbname, $tablename,
 400         $version, $type, $new_data
 401     ) {
 402         if ($type == 'DDL') {
 403             $save_to = 'schema_sql';
 404         } elseif ($type == 'DML') {
 405             $save_to = 'data_sql';
 406         } else {
 407             return false;
 408         }
 409         $date  = date('Y-m-d H:i:s');
 410
 411         $new_data_processed = '';
 412         if (is_array($new_data)) {
 413             foreach ($new_data as $data) {
 414                 $new_data_processed .= '# log ' . $date . ' ' . $data['username']
 415                     . Util::sqlAddSlashes($data['statement']) . "\n";
 416             }
 417         } else {
 418             $new_data_processed = $new_data;
 419         }
 420
 421         $sql_query = " UPDATE " . self::_getTrackingTable() .
 422         " SET `" . $save_to . "` = '" . $new_data_processed . "' " .
 423         " WHERE `db_name` = '" . Util::sqlAddSlashes($dbname) . "' " .
 424         " AND `table_name` = '" . Util::sqlAddSlashes($tablename) . "' " .
 425         " AND `version` = '" . Util::sqlAddSlashes($version) . "' ";
 426
 427         $result = PMA_queryAsControlUser($sql_query);
 428
 429         return (boolean) $result;
 430     }
 431
 432     /**
 433      * Activates tracking of a table.
 434      *
 435      * @param string $dbname    name of database
 436      * @param string $tablename name of table
 437      * @param string $version   version
 438      *
 439      * @static
 440      *
 441      * @return int result of SQL query
 442      */
 443     static public function activateTracking($dbname, $tablename, $version)
 444     {
 445         return self::_changeTracking($dbname, $tablename, $version, 1);
 446     }
 447
 448
 449     /**
 450      * Deactivates tracking of a table.
 451      *
 452      * @param string $dbname    name of database
 453      * @param string $tablename name of table
 454      * @param string $version   version
 455      *
 456      * @static
 457      *
 458      * @return int result of SQL query
 459      */
 460     static public function deactivateTracking($dbname, $tablename, $version)
 461     {
 462         return self::_changeTracking($dbname, $tablename, $version, 0);
 463     }
 464
 465
 466     /**
 467      * Gets the newest version of a tracking job
 468      * (in other words: gets the HEAD version).
 469      *
 470      * @param string $dbname    name of database
 471      * @param string $tablename name of table
 472      * @param string $statement tracked statement
 473      *
 474      * @static
 475      *
 476      * @return int (-1 if no version exists | >  0 if a version exists)
 477      */
 478     static public function getVersion($dbname, $tablename, $statement = null)
 479     {
 480         $sql_query = " SELECT MAX(version) FROM " . self::_getTrackingTable() .
 481         " WHERE `db_name` = '" . Util::sqlAddSlashes($dbname) . "' " .
 482         " AND `table_name` = '" . Util::sqlAddSlashes($tablename) . "' ";
 483
 484         if ($statement != "") {
 485             $sql_query .= " AND FIND_IN_SET('"
 486                 . $statement . "',tracking) > 0" ;
 487         }
 488         $row = $GLOBALS['dbi']->fetchArray(PMA_queryAsControlUser($sql_query));
 489         return isset($row[0])
 490             ? $row[0]
 491             : -1;
 492     }
 493
 494
 495     /**
 496      * Gets the record of a tracking job.
 497      *
 498      * @param string $dbname    name of database
 499      * @param string $tablename name of table
 500      * @param string $version   version number
 501      *
 502      * @static
 503      *
 504      * @return mixed record DDM log, DDL log, structure snapshot, tracked
 505      *         statements.
 506      */
 507     static public function getTrackedData($dbname, $tablename, $version)
 508     {
 509         $sql_query = " SELECT * FROM " . self::_getTrackingTable() .
 510             " WHERE `db_name` = '" . Util::sqlAddSlashes($dbname) . "' ";
 511         if (! empty($tablename)) {
 512             $sql_query .= " AND `table_name` = '"
 513                 . Util::sqlAddSlashes($tablename) . "' ";
 514         }
 515         $sql_query .= " AND `version` = '" . Util::sqlAddSlashes($version)
 516             . "' " . " ORDER BY `version` DESC LIMIT 1";
 517
 518         $mixed = $GLOBALS['dbi']->fetchAssoc(PMA_queryAsControlUser($sql_query));
 519
 520         // Parse log
 521         $log_schema_entries = explode('# log ',  $mixed['schema_sql']);
 522         $log_data_entries   = explode('# log ',  $mixed['data_sql']);
 523
 524         $ddl_date_from = $date = date('Y-m-d H:i:s');
 525
 526         $ddlog = array();
 527         $first_iteration = true;
 528
 529         // Iterate tracked data definition statements
 530         // For each log entry we want to get date, username and statement
 531         foreach ($log_schema_entries as $log_entry) {
 532             if (trim($log_entry) != '') {
 533                 $date      = mb_substr($log_entry, 0, 19);
 534                 $username  = mb_substr(
 535                     $log_entry, 20, mb_strpos($log_entry, "\n") - 20
 536                 );
 537                 if ($first_iteration) {
 538                     $ddl_date_from = $date;
 539                     $first_iteration = false;
 540                 }
 541                 $statement = rtrim(mb_strstr($log_entry, "\n"));
 542
 543                 $ddlog[] = array( 'date' => $date,
 544                                   'username'=> $username,
 545                                   'statement' => $statement );
 546             }
 547         }
 548
 549         $date_from = $ddl_date_from;
 550         $ddl_date_to = $date;
 551
 552         $dml_date_from = $date_from;
 553
 554         $dmlog = array();
 555         $first_iteration = true;
 556
 557         // Iterate tracked data manipulation statements
 558         // For each log entry we want to get date, username and statement
 559         foreach ($log_data_entries as $log_entry) {
 560             if (trim($log_entry) != '') {
 561                 $date      = mb_substr($log_entry, 0, 19);
 562                 $username  = mb_substr(
 563                     $log_entry, 20, mb_strpos($log_entry, "\n") - 20
 564                 );
 565                 if ($first_iteration) {
 566                     $dml_date_from = $date;
 567                     $first_iteration = false;
 568                 }
 569                 $statement = rtrim(mb_strstr($log_entry, "\n"));
 570
 571                 $dmlog[] = array( 'date' => $date,
 572                                   'username' => $username,
 573                                   'statement' => $statement );
 574             }
 575         }
 576
 577         $dml_date_to = $date;
 578
 579         // Define begin and end of date range for both logs
 580         $data = array();
 581         if (strtotime($ddl_date_from) <= strtotime($dml_date_from)) {
 582             $data['date_from'] = $ddl_date_from;
 583         } else {
 584             $data['date_from'] = $dml_date_from;
 585         }
 586         if (strtotime($ddl_date_to) >= strtotime($dml_date_to)) {
 587             $data['date_to'] = $ddl_date_to;
 588         } else {
 589             $data['date_to'] = $dml_date_to;
 590         }
 591         $data['ddlog']           = $ddlog;
 592         $data['dmlog']           = $dmlog;
 593         $data['tracking']        = $mixed['tracking'];
 594         $data['schema_snapshot'] = $mixed['schema_snapshot'];
 595
 596         return $data;
 597     }
 598
 599
 600     /**
 601      * Parses a query. Gets
 602      *  - statement identifier (UPDATE, ALTER TABLE, ...)
 603      *  - type of statement, is it part of DDL or DML ?
 604      *  - tablename
 605      *
 606      * @param string $query query
 607      *
 608      * @static
 609      * @todo: using PMA SQL Parser when possible
 610      * @todo: support multi-table/view drops
 611      *
 612      * @return mixed Array containing identifier, type and tablename.
 613      *
 614      */
 615     static public function parseQuery($query)
 616     {
 617         // Usage of PMA_SQP does not work here
 618         //
 619         // require_once("libraries/sqlparser.lib.php");
 620         // $parsed_sql = PMA_SQP_parse($query);
 621         // $sql_info = PMA_SQP_analyze($parsed_sql);
 622
 623         $query = str_replace("\n", " ", $query);
 624         $query = str_replace("\r", " ", $query);
 625
 626         $query = trim($query);
 627         $query = trim($query, ' -');
 628
 629         $tokens = explode(" ", $query);
 630         foreach ($tokens as $key => $value) {
 631             $tokens[$key] = mb_strtoupper($value);
 632         }
 633
 634         // Parse USE statement, need it for SQL dump imports
 635         if (mb_substr($query, 0, 4) == 'USE ') {
 636             $prefix = explode('USE ', $query);
 637             $GLOBALS['db'] = self::getTableName($prefix[1]);
 638         }
 639
 640         /*
 641          * DDL statements
 642          */
 643
 644         $result         = array();
 645         $result['type'] = 'DDL';
 646
 647         // Parse CREATE VIEW statement
 648         if (in_array('CREATE', $tokens) == true
 649             && in_array('VIEW', $tokens) == true
 650             && in_array('AS', $tokens) == true
 651         ) {
 652             $result['identifier'] = 'CREATE VIEW';
 653
 654             $index = array_search('VIEW', $tokens);
 655
 656             $result['tablename'] = mb_strtolower(
 657                 self::getTableName($tokens[$index + 1])
 658             );
 659         }
 660
 661         // Parse ALTER VIEW statement
 662         if (in_array('ALTER', $tokens) == true
 663             && in_array('VIEW', $tokens) == true
 664             && in_array('AS', $tokens) == true
 665             && ! isset($result['identifier'])
 666         ) {
 667             $result['identifier'] = 'ALTER VIEW';
 668
 669             $index = array_search('VIEW', $tokens);
 670
 671             $result['tablename'] = mb_strtolower(
 672                 self::getTableName($tokens[$index + 1])
 673             );
 674         }
 675
 676         // Parse DROP VIEW statement
 677         if (! isset($result['identifier'])
 678             && substr($query, 0, 10) == 'DROP VIEW '
 679         ) {
 680             $result['identifier'] = 'DROP VIEW';
 681
 682             $prefix  = explode('DROP VIEW ', $query);
 683             $str = str_replace('IF EXISTS', '', $prefix[1]);
 684             $result['tablename'] = self::getTableName($str);
 685         }
 686
 687         // Parse CREATE DATABASE statement
 688         if (! isset($result['identifier'])
 689             && substr($query, 0, 15) == 'CREATE DATABASE'
 690         ) {
 691             $result['identifier'] = 'CREATE DATABASE';
 692             $str = str_replace('CREATE DATABASE', '', $query);
 693             $str = str_replace('IF NOT EXISTS', '', $str);
 694
 695             $prefix = explode('DEFAULT ', $str);
 696
 697             $result['tablename'] = '';
 698             $GLOBALS['db'] = self::getTableName($prefix[0]);
 699         }
 700
 701         // Parse ALTER DATABASE statement
 702         if (! isset($result['identifier'])
 703             && substr($query, 0, 14) == 'ALTER DATABASE'
 704         ) {
 705             $result['identifier'] = 'ALTER DATABASE';
 706             $result['tablename'] = '';
 707         }
 708
 709         // Parse DROP DATABASE statement
 710         if (! isset($result['identifier'])
 711             && substr($query, 0, 13) == 'DROP DATABASE'
 712         ) {
 713             $result['identifier'] = 'DROP DATABASE';
 714             $str = str_replace('DROP DATABASE', '', $query);
 715             $str = str_replace('IF EXISTS', '', $str);
 716             $GLOBALS['db'] = self::getTableName($str);
 717             $result['tablename'] = '';
 718         }
 719
 720         // Parse CREATE TABLE statement
 721         if (! isset($result['identifier'])
 722             && substr($query, 0, 12) == 'CREATE TABLE'
 723         ) {
 724             $result['identifier'] = 'CREATE TABLE';
 725             $query   = str_replace('IF NOT EXISTS', '', $query);
 726             $prefix  = explode('CREATE TABLE ', $query);
 727             $suffix  = explode('(', $prefix[1]);
 728             $result['tablename'] = self::getTableName($suffix[0]);
 729         }
 730
 731         // Parse ALTER TABLE statement
 732         if (! isset($result['identifier'])
 733             && substr($query, 0, 12) == 'ALTER TABLE '
 734         ) {
 735             $result['identifier'] = 'ALTER TABLE';
 736
 737             $prefix  = explode('ALTER TABLE ', $query);
 738             $suffix  = explode(' ', $prefix[1]);
 739             $result['tablename']  = self::getTableName($suffix[0]);
 740         }
 741
 742         // Parse DROP TABLE statement
 743         if (! isset($result['identifier'])
 744             && substr($query, 0, 11) == 'DROP TABLE '
 745         ) {
 746             $result['identifier'] = 'DROP TABLE';
 747
 748             $prefix  = explode('DROP TABLE ', $query);
 749             $str = str_replace('IF EXISTS', '', $prefix[1]);
 750             $result['tablename'] = self::getTableName($str);
 751         }
 752
 753         // Parse CREATE INDEX statement
 754         if (! isset($result['identifier'])
 755             && (substr($query, 0, 12) == 'CREATE INDEX'
 756             || substr($query, 0, 19) == 'CREATE UNIQUE INDEX'
 757             || substr($query, 0, 20) == 'CREATE SPATIAL INDEX')
 758         ) {
 759              $result['identifier'] = 'CREATE INDEX';
 760              $prefix = explode('ON ', $query);
 761              $suffix = explode('(', $prefix[1]);
 762              $result['tablename'] = self::getTableName($suffix[0]);
 763         }
 764
 765         // Parse DROP INDEX statement
 766         if (! isset($result['identifier'])
 767             && substr($query, 0, 10) == 'DROP INDEX'
 768         ) {
 769              $result['identifier'] = 'DROP INDEX';
 770              $prefix = explode('ON ', $query);
 771              $result['tablename'] = self::getTableName($prefix[1]);
 772         }
 773
 774         // Parse RENAME TABLE statement
 775         if (! isset($result['identifier'])
 776             && substr($query, 0, 13) == 'RENAME TABLE '
 777         ) {
 778             $result['identifier'] = 'RENAME TABLE';
 779             $prefix = explode('RENAME TABLE ', $query);
 780             $names  = explode(' TO ', $prefix[1]);
 781             $result['tablename']      = self::getTableName($names[0]);
 782             $result["tablename_after_rename"]  = self::getTableName($names[1]);
 783         }
 784
 785         /*
 786          * DML statements
 787          */
 788
 789         if (! isset($result['identifier'])) {
 790             $result["type"]       = 'DML';
 791         }
 792         // Parse UPDATE statement
 793         if (! isset($result['identifier'])
 794             && substr($query, 0, 6) == 'UPDATE'
 795         ) {
 796             $result['identifier'] = 'UPDATE';
 797             $prefix  = explode('UPDATE ', $query);
 798             $suffix  = explode(' ', $prefix[1]);
 799             $result['tablename'] = self::getTableName($suffix[0]);
 800         }
 801
 802         // Parse INSERT INTO statement
 803         if (! isset($result['identifier'])
 804             && substr($query, 0, 11) == 'INSERT INTO'
 805         ) {
 806             $result['identifier'] = 'INSERT';
 807             $prefix  = explode('INSERT INTO', $query);
 808             $suffix  = explode('(', $prefix[1]);
 809             $result['tablename'] = self::getTableName($suffix[0]);
 810         }
 811
 812         // Parse DELETE statement
 813         if (! isset($result['identifier'])
 814             && substr($query, 0, 6) == 'DELETE'
 815         ) {
 816             $result['identifier'] = 'DELETE';
 817             $prefix  = explode('FROM ', $query);
 818             $suffix  = explode(' ', $prefix[1]);
 819             $result['tablename'] = self::getTableName($suffix[0]);
 820         }
 821
 822         // Parse TRUNCATE statement
 823         if (! isset($result['identifier'])
 824             && substr($query, 0, 8) == 'TRUNCATE'
 825         ) {
 826             $result['identifier'] = 'TRUNCATE';
 827             $prefix  = explode('TRUNCATE', $query);
 828             $result['tablename'] = self::getTableName($prefix[1]);
 829         }
 830
 831         return $result;
 832     }
 833
 834
 835     /**
 836      * Analyzes a given SQL statement and saves tracking data.
 837      *
 838      * @param string $query a SQL query
 839      *
 840      * @static
 841      *
 842      * @return void
 843      */
 844     static public function handleQuery($query)
 845     {
 846         // If query is marked as untouchable, leave
 847         if (mb_strstr($query, "/*NOTRACK*/")) {
 848             return;
 849         }
 850
 851         if (! (substr($query, -1) == ';')) {
 852             $query = $query . ";\n";
 853         }
 854         // Get some information about query
 855         $result = self::parseQuery($query);
 856
 857         // Get database name
 858         $dbname = trim(isset($GLOBALS['db']) ? $GLOBALS['db'] : '', '`');
 859         // $dbname can be empty, for example when coming from Synchronize
 860         // and this is a query for the remote server
 861         if (empty($dbname)) {
 862             return;
 863         }
 864
 865         // If we found a valid statement
 866         if (isset($result['identifier'])) {
 867             $version = self::getVersion(
 868                 $dbname, $result['tablename'], $result['identifier']
 869             );
 870
 871             // If version not exists and auto-creation is enabled
 872             if ($GLOBALS['cfg']['Server']['tracking_version_auto_create'] == true
 873                 && self::isTracked($dbname, $result['tablename']) == false
 874                 && $version == -1
 875             ) {
 876                 // Create the version
 877
 878                 switch ($result['identifier']) {
 879                 case 'CREATE TABLE':
 880                     self::createVersion($dbname, $result['tablename'], '1');
 881                     break;
 882                 case 'CREATE VIEW':
 883                     self::createVersion(
 884                         $dbname, $result['tablename'], '1', '', true
 885                     );
 886                     break;
 887                 case 'CREATE DATABASE':
 888                     self::createDatabaseVersion($dbname, '1', $query);
 889                     break;
 890                 } // end switch
 891             }
 892
 893             // If version exists
 894             if (self::isTracked($dbname, $result['tablename']) && $version != -1) {
 895                 if ($result['type'] == 'DDL') {
 896                     $save_to = 'schema_sql';
 897                 } elseif ($result['type'] == 'DML') {
 898                     $save_to = 'data_sql';
 899                 } else {
 900                     $save_to = '';
 901                 }
 902                 $date  = date('Y-m-d H:i:s');
 903
 904                 // Cut off `dbname`. from query
 905                 $query = preg_replace(
 906                     '/`' . preg_quote($dbname) . '`\s?\./',
 907                     '',
 908                     $query
 909                 );
 910
 911                 // Add log information
 912                 $query = self::getLogComment() . $query ;
 913
 914                 // Mark it as untouchable
 915                 $sql_query = " /*NOTRACK*/\n"
 916                     . " UPDATE " . self::_getTrackingTable()
 917                     . " SET " . Util::backquote($save_to)
 918                     . " = CONCAT( " . Util::backquote($save_to) . ",'\n"
 919                     . Util::sqlAddSlashes($query) . "') ,"
 920                     . " `date_updated` = '" . $date . "' ";
 921
 922                 // If table was renamed we have to change
 923                 // the tablename attribute in pma_tracking too
 924                 if ($result['identifier'] == 'RENAME TABLE') {
 925                     $sql_query .= ', `table_name` = \''
 926                         . Util::sqlAddSlashes($result['tablename_after_rename'])
 927                         . '\' ';
 928                 }
 929
 930                 // Save the tracking information only for
 931                 //     1. the database
 932                 //     2. the table / view
 933                 //     3. the statements
 934                 // we want to track
 935                 $sql_query .=
 936                 " WHERE FIND_IN_SET('" . $result['identifier'] . "',tracking) > 0" .
 937                 " AND `db_name` = '" . Util::sqlAddSlashes($dbname) . "' " .
 938                 " AND `table_name` = '"
 939                 . Util::sqlAddSlashes($result['tablename']) . "' " .
 940                 " AND `version` = '" . Util::sqlAddSlashes($version) . "' ";
 941
 942                 PMA_queryAsControlUser($sql_query);
 943             }
 944         }
 945     }
 946
 947     /**
 948      * Returns the tracking table
 949      *
 950      * @return string tracking table
 951      */
 952     private static function _getTrackingTable()
 953     {
 954         $cfgRelation = PMA_getRelationsParam();
 955         return Util::backquote($cfgRelation['db'])
 956             . '.' . Util::backquote($cfgRelation['tracking']);
 957     }
 958 }