phpmyadmin/libraries/tbl_tracking.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions used to generate table tracking
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 /**
  10  * Filters tracking entries
  11  *
  12  * @param array  $data           the entries to filter
  13  * @param string $filter_ts_from "from" date
  14  * @param string $filter_ts_to   "to" date
  15  * @param string $filter_users   users
  16  *
  17  * @return array filtered entries
  18  */
  19 function PMA_filterTracking(
  20     $data, $filter_ts_from, $filter_ts_to, $filter_users
  21 ) {
  22     $tmp_entries = array();
  23     $id = 0;
  24     foreach ($data as $entry) {
  25         $timestamp = strtotime($entry['date']);
  26         $filtered_user = in_array($entry['username'], $filter_users);
  27         if ($timestamp >= $filter_ts_from
  28             && $timestamp <= $filter_ts_to
  29             && (in_array('*', $filter_users) || $filtered_user)
  30         ) {
  31             $tmp_entries[] = array(
  32                 'id'        => $id,
  33                 'timestamp' => $timestamp,
  34                 'username'  => $entry['username'],
  35                 'statement' => $entry['statement']
  36             );
  37         }
  38         $id++;
  39     }
  40     return($tmp_entries);
  41 }
  42
  43 /**
  44  * Function to get html for data definition and data manipulation statements
  45  *
  46  * @param string $url_query    url query
  47  * @param int    $last_version last version
  48  *
  49  * @return string
  50  */
  51 function PMA_getHtmlForDataDefinitionAndManipulationStatements($url_query,
  52     $last_version
  53 ) {
  54     $html = '<div id="div_create_version">';
  55     $html .= '<form method="post" action="tbl_tracking.php?' . $url_query . '">';
  56     $html .= PMA_URL_getHiddenInputs($GLOBALS['db'], $GLOBALS['table']);
  57     $html .= '<fieldset>';
  58     $html .= '<legend>';
  59     $html .= sprintf(
  60         __('Create version %1$s of %2$s'),
  61         ($last_version + 1),
  62         htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table'])
  63     );
  64     $html .= '</legend>';
  65     $html .= '<input type="hidden" name="version" value="' . ($last_version + 1)
  66         . '" />';
  67     $html .= '<p>' . __('Track these data definition statements:')
  68         . '</p>';
  69     $html .= '<input type="checkbox" name="alter_table" value="true"'
  70         . ' checked="checked" /> ALTER TABLE<br/>';
  71     $html .= '<input type="checkbox" name="rename_table" value="true"'
  72         . ' checked="checked" /> RENAME TABLE<br/>';
  73     $html .= '<input type="checkbox" name="create_table" value="true"'
  74         . ' checked="checked" /> CREATE TABLE<br/>';
  75     $html .= '<input type="checkbox" name="drop_table" value="true"'
  76         . ' checked="checked" /> DROP TABLE<br/>';
  77     $html .= '<br/>';
  78     $html .= '<input type="checkbox" name="create_index" value="true"'
  79         . ' checked="checked" /> CREATE INDEX<br/>';
  80     $html .= '<input type="checkbox" name="drop_index" value="true"'
  81         . ' checked="checked" /> DROP INDEX<br/>';
  82     $html .= '<p>' . __('Track these data manipulation statements:') . '</p>';
  83     $html .= '<input type="checkbox" name="insert" value="true"'
  84         . ' checked="checked" /> INSERT<br/>';
  85     $html .= '<input type="checkbox" name="update" value="true"'
  86         . ' checked="checked" /> UPDATE<br/>';
  87     $html .= '<input type="checkbox" name="delete" value="true"'
  88         . ' checked="checked" /> DELETE<br/>';
  89     $html .= '<input type="checkbox" name="truncate" value="true"'
  90         . ' checked="checked" /> TRUNCATE<br/>';
  91     $html .= '</fieldset>';
  92
  93     $html .= '<fieldset class="tblFooters">';
  94     $html .= '<input type="hidden" name="submit_create_version" value="1" />';
  95     $html .= '<input type="submit" value="' . __('Create version') . '" />';
  96     $html .= '</fieldset>';
  97
  98     $html .= '</form>';
  99     $html .= '</div>';
 100
 101     return $html;
 102 }
 103
 104 /**
 105  * Function to get html for activate tracking
 106  *
 107  * @param string $url_query    url query
 108  * @param int    $last_version last version
 109  *
 110  * @return string
 111  */
 112 function PMA_getHtmlForActivateTracking($url_query, $last_version)
 113 {
 114     $html = '<div id="div_activate_tracking">';
 115     $html .= '<form method="post" action="tbl_tracking.php?' . $url_query . '">';
 116     $html .= '<fieldset>';
 117     $html .= '<legend>';
 118     $html .= sprintf(
 119         __('Activate tracking for %s'),
 120         htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table'])
 121     );
 122     $html .= '</legend>';
 123     $html .= '<input type="hidden" name="version" value="' . $last_version . '" />';
 124     $html .= '<input type="hidden" name="submit_activate_now" value="1" />';
 125     $html .= '<input type="submit" value="' . __('Activate now') . '" />';
 126     $html .= '</fieldset>';
 127     $html .= '</form>';
 128     $html .= '</div>';
 129
 130     return $html;
 131 }
 132
 133 /**
 134  * Function to get html for deactivating tracking
 135  *
 136  * @param string $url_query    url query
 137  * @param int    $last_version last version
 138  *
 139  * @return string
 140  */
 141 function PMA_getHtmlForDeactivateTracking($url_query, $last_version)
 142 {
 143     $html = '<div id="div_deactivate_tracking">';
 144     $html .= '<form method="post" action="tbl_tracking.php?' . $url_query . '">';
 145     $html .= '<fieldset>';
 146     $html .= '<legend>';
 147     $html .= sprintf(
 148         __('Deactivate tracking for %s'),
 149         htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table'])
 150     );
 151     $html .= '</legend>';
 152     $html .= '<input type="hidden" name="version" value="' . $last_version . '" />';
 153     $html .= '<input type="hidden" name="submit_deactivate_now" value="1" />';
 154     $html .= '<input type="submit" value="' . __('Deactivate now') . '" />';
 155     $html .= '</fieldset>';
 156     $html .= '</form>';
 157     $html .= '</div>';
 158
 159     return $html;
 160 }
 161
 162 /**
 163  * Function to get the list versions of the table
 164  *
 165  * @return array
 166  */
 167 function PMA_getListOfVersionsOfTable()
 168 {
 169     $sql_query = " SELECT * FROM " .
 170          PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." .
 171          PMA_Util::backquote($GLOBALS['cfg']['Server']['tracking']) .
 172          " WHERE db_name = '" . PMA_Util::sqlAddSlashes($_REQUEST['db']) . "' " .
 173          " AND table_name = '" . PMA_Util::sqlAddSlashes($_REQUEST['table']) . "' " .
 174          " ORDER BY version DESC ";
 175
 176     return PMA_queryAsControlUser($sql_query);
 177 }
 178
 179 /**
 180  * Function to get html for displaying last version number
 181  *
 182  * @param array  $sql_result   sql result
 183  * @param int    $last_version last version
 184  * @param array  $url_params   url parameters
 185  * @param string $url_query    url query
 186  *
 187  * @return string
 188  */
 189 function PMA_getHtmlForTableVersionDetails($sql_result, $last_version, $url_params,
 190     $url_query
 191 ) {
 192     $html = '<table id="versions" class="data">';
 193     $html .= '<thead>';
 194     $html .= '<tr>';
 195     $html .= '<th>' . __('Database') . '</th>';
 196     $html .= '<th>' . __('Table') . '</th>';
 197     $html .= '<th>' . __('Version') . '</th>';
 198     $html .= '<th>' . __('Created') . '</th>';
 199     $html .= '<th>' . __('Updated') . '</th>';
 200     $html .= '<th>' . __('Status') . '</th>';
 201     $html .= '<th>' . __('Show') . '</th>';
 202     $html .= '</tr>';
 203     $html .= '</thead>';
 204     $html .= '<tbody>';
 205
 206     $style = 'odd';
 207     $GLOBALS['dbi']->dataSeek($sql_result, 0);
 208     while ($version = $GLOBALS['dbi']->fetchArray($sql_result)) {
 209         if ($version['tracking_active'] == 1) {
 210             $version_status = __('active');
 211         } else {
 212             $version_status = __('not active');
 213         }
 214         if ($version['version'] == $last_version) {
 215             if ($version['tracking_active'] == 1) {
 216                 $tracking_active = true;
 217             } else {
 218                 $tracking_active = false;
 219             }
 220         }
 221         $html .= '<tr class="noclick ' . $style . '">';
 222         $html .= '<td>' . htmlspecialchars($version['db_name']) . '</td>';
 223         $html .= '<td>' . htmlspecialchars($version['table_name']) . '</td>';
 224         $html .= '<td>' . htmlspecialchars($version['version']) . '</td>';
 225         $html .= '<td>' . htmlspecialchars($version['date_created']) . '</td>';
 226         $html .= '<td>' . htmlspecialchars($version['date_updated']) . '</td>';
 227         $html .= '<td>' . $version_status . '</td>';
 228         $html .= '<td><a href="tbl_tracking.php';
 229         $html .= PMA_URL_getCommon(
 230             $url_params + array(
 231                 'report' => 'true', 'version' => $version['version']
 232             )
 233         );
 234         $html .= '">' . __('Tracking report') . '</a>';
 235         $html .= '&nbsp;|&nbsp;';
 236         $html .= '<a href="tbl_tracking.php';
 237         $html .= PMA_URL_getCommon(
 238             $url_params + array(
 239                 'snapshot' => 'true', 'version' => $version['version']
 240             )
 241         );
 242         $html .= '">' . __('Structure snapshot') . '</a>';
 243         $html .= '</td>';
 244         $html .= '</tr>';
 245
 246         if ($style == 'even') {
 247             $style = 'odd';
 248         } else {
 249             $style = 'even';
 250         }
 251     }
 252
 253     $html .= '</tbody>';
 254     $html .= '</table>';
 255
 256     if ($tracking_active) {
 257         $html .= PMA_getHtmlForDeactivateTracking($url_query, $last_version);
 258     } else {
 259         $html .= PMA_getHtmlForActivateTracking($url_query, $last_version);
 260     }
 261
 262     return $html;
 263 }
 264
 265 /**
 266  * Function to get the last version number of a table
 267  *
 268  * @param array $sql_result sql result
 269  *
 270  * @return int
 271  */
 272 function PMA_getTableLastVersionNumber($sql_result)
 273 {
 274     $maxversion = $GLOBALS['dbi']->fetchArray($sql_result);
 275     $last_version = $maxversion['version'];
 276
 277     return $last_version;
 278 }
 279
 280 /**
 281  * Function to get sql results for selectable tables
 282  *
 283  * @return array
 284  */
 285 function PMA_getSQLResultForSelectableTables()
 286 {
 287     include_once 'libraries/relation.lib.php';
 288
 289     $sql_query = " SELECT DISTINCT db_name, table_name FROM " .
 290              PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." .
 291              PMA_Util::backquote($GLOBALS['cfg']['Server']['tracking']) .
 292              " WHERE db_name = '" . PMA_Util::sqlAddSlashes($GLOBALS['db']) . "' " .
 293              " ORDER BY db_name, table_name";
 294
 295     return PMA_queryAsControlUser($sql_query);
 296 }
 297
 298 /**
 299  * Function to get html for selectable table rows
 300  *
 301  * @param array  $selectable_tables_sql_result sql results for selectable rows
 302  * @param string $url_query                    url query
 303  *
 304  * @return string
 305  */
 306 function PMA_getHtmlForSelectableTables($selectable_tables_sql_result, $url_query)
 307 {
 308     $html = '<form method="post" action="tbl_tracking.php?' . $url_query . '">';
 309     $html .= '<select name="table">';
 310     while ($entries = $GLOBALS['dbi']->fetchArray($selectable_tables_sql_result)) {
 311         if (PMA_Tracker::isTracked($entries['db_name'], $entries['table_name'])) {
 312             $status = ' (' . __('active') . ')';
 313         } else {
 314             $status = ' (' . __('not active') . ')';
 315         }
 316         if ($entries['table_name'] == $_REQUEST['table']) {
 317             $s = ' selected="selected"';
 318         } else {
 319             $s = '';
 320         }
 321         $html .= '<option value="' . htmlspecialchars($entries['table_name'])
 322             . '"' . $s . '>' . htmlspecialchars($entries['db_name']) . ' . '
 323             . htmlspecialchars($entries['table_name']) . $status . '</option>'
 324             . "\n";
 325     }
 326     $html .= '</select>';
 327     $html .= '<input type="hidden" name="show_versions_submit" value="1" />';
 328     $html .= '<input type="submit" value="' . __('Show versions') . '" />';
 329     $html .= '</form>';
 330
 331     return $html;
 332 }
 333
 334 /**
 335  * Function to get html for tracking report and tracking report export
 336  *
 337  * @param string  $url_query        url query
 338  * @param array   $data             data
 339  * @param array   $url_params       url params
 340  * @param boolean $selection_schema selection schema
 341  * @param boolean $selection_data   selection data
 342  * @param boolean $selection_both   selection both
 343  * @param int     $filter_ts_to     filter time stamp from
 344  * @param int     $filter_ts_from   filter time stamp tp
 345  * @param array   $filter_users     filter users
 346  *
 347  * @return string
 348  */
 349 function PMA_getHtmlForTrackingReport($url_query, $data, $url_params,
 350     $selection_schema, $selection_data, $selection_both, $filter_ts_to,
 351     $filter_ts_from, $filter_users
 352 ) {
 353     $html = '<h3>' . __('Tracking report')
 354         . '  [<a href="tbl_tracking.php?' . $url_query . '">' . __('Close')
 355         . '</a>]</h3>';
 356
 357     $html .= '<small>' . __('Tracking statements') . ' '
 358         . htmlspecialchars($data['tracking']) . '</small><br/>';
 359     $html .= '<br/>';
 360
 361     list($str1, $str2, $str3, $str4, $str5) = PMA_getHtmlForElementsOfTrackingReport(
 362         $selection_schema, $selection_data, $selection_both
 363     );
 364
 365     // Prepare delete link content here
 366     $drop_image_or_text = '';
 367     if (PMA_Util::showIcons('ActionsLinksMode')) {
 368         $drop_image_or_text .= PMA_Util::getImage(
 369             'b_drop.png', __('Delete tracking data row from report')
 370         );
 371     }
 372     if (PMA_Util::showText('ActionLinksMode')) {
 373         $drop_image_or_text .= __('Delete');
 374     }
 375
 376     /*
 377      *  First, list tracked data definition statements
 378      */
 379     if (count($data['ddlog']) == 0 && count($data['dmlog']) == 0) {
 380         $msg = PMA_Message::notice(__('No data'));
 381         $msg->display();
 382     }
 383
 384     $html .= PMA_getHtmlForTrackingReportExportForm1(
 385         $data, $url_params, $selection_schema, $selection_data, $selection_both,
 386         $filter_ts_to, $filter_ts_from, $filter_users, $str1, $str2, $str3,
 387         $str4, $str5, $drop_image_or_text
 388     );
 389
 390     $html .= PMA_getHtmlForTrackingReportExportForm2(
 391         $url_params, $str1, $str2, $str3, $str4, $str5
 392     );
 393
 394     $html .= "<br/><br/><hr/><br/>\n";
 395
 396     return $html;
 397 }
 398
 399 /**
 400  * Generate HTML element for report form
 401  *
 402  * @param boolean $selection_schema selection schema
 403  * @param boolean $selection_data   selection data
 404  * @param boolean $selection_both   selection both
 405  *
 406  * @return array
 407  */
 408 function PMA_getHtmlForElementsOfTrackingReport(
 409     $selection_schema, $selection_data, $selection_both
 410 ) {
 411     $str1 = '<select name="logtype">'
 412         . '<option value="schema"'
 413         . ($selection_schema ? ' selected="selected"' : '') . '>'
 414         . __('Structure only') . '</option>'
 415         . '<option value="data"'
 416         . ($selection_data ? ' selected="selected"' : '') . '>'
 417         . __('Data only') . '</option>'
 418         . '<option value="schema_and_data"'
 419         . ($selection_both ? ' selected="selected"' : '') . '>'
 420         . __('Structure and data') . '</option>'
 421         . '</select>';
 422     $str2 = '<input type="text" name="date_from" value="'
 423         . htmlspecialchars($_REQUEST['date_from']) . '" size="19" />';
 424     $str3 = '<input type="text" name="date_to" value="'
 425         . htmlspecialchars($_REQUEST['date_to']) . '" size="19" />';
 426     $str4 = '<input type="text" name="users" value="'
 427         . htmlspecialchars($_REQUEST['users']) . '" />';
 428     $str5 = '<input type="hidden" name="list_report" value="1" />'
 429         . '<input type="submit" value="' . __('Go') . '" />';
 430     return array($str1, $str2, $str3, $str4, $str5);
 431 }
 432
 433 /**
 434  * Generate HTML for export form
 435  *
 436  * @param array   $data               data
 437  * @param array   $url_params         url params
 438  * @param boolean $selection_schema   selection schema
 439  * @param boolean $selection_data     selection data
 440  * @param boolean $selection_both     selection both
 441  * @param int     $filter_ts_to       filter time stamp from
 442  * @param int     $filter_ts_from     filter time stamp tp
 443  * @param array   $filter_users       filter users
 444  * @param string  $str1               HTML for logtype select
 445  * @param string  $str2               HTML for "from date"
 446  * @param string  $str3               HTML for "to date"
 447  * @param string  $str4               HTML for user
 448  * @param string  $str5               HTML for "list report"
 449  * @param string  $drop_image_or_text HTML for image or text
 450  *
 451  * @return string HTML for form
 452  */
 453 function PMA_getHtmlForTrackingReportExportForm1(
 454     $data, $url_params, $selection_schema, $selection_data, $selection_both,
 455     $filter_ts_to, $filter_ts_from, $filter_users, $str1, $str2, $str3,
 456     $str4, $str5, $drop_image_or_text
 457 ) {
 458     $html = '<form method="post" action="tbl_tracking.php'
 459         . PMA_URL_getCommon(
 460             $url_params + array(
 461                 'report' => 'true', 'version' => $_REQUEST['version']
 462             )
 463         )
 464         . '">';
 465
 466     $html .= sprintf(
 467         __('Show %1$s with dates from %2$s to %3$s by user %4$s %5$s'),
 468         $str1, $str2, $str3, $str4, $str5
 469     );
 470
 471     if ($selection_schema || $selection_both && count($data['ddlog']) > 0) {
 472         list($temp, $ddlog_count) = PMA_getHtmlForDataDefinitionStatements(
 473             $data, $filter_users, $filter_ts_from, $filter_ts_to, $url_params,
 474             $drop_image_or_text
 475         );
 476         $html .= $temp;
 477         unset($temp);
 478     } //endif
 479
 480     /*
 481      *  Secondly, list tracked data manipulation statements
 482      */
 483     if (($selection_data || $selection_both) && count($data['dmlog']) > 0) {
 484         $html .= PMA_getHtmlForDataManipulationStatements(
 485             $data, $filter_users, $filter_ts_from, $filter_ts_to, $url_params,
 486             $ddlog_count, $drop_image_or_text
 487         );
 488     }
 489     $html .= '</form>';
 490     return $html;
 491 }
 492
 493 /**
 494  * Generate HTML for export form
 495  *
 496  * @param array  $url_params Parameters
 497  * @param string $str1       HTML for logtype select
 498  * @param string $str2       HTML for "from date"
 499  * @param string $str3       HTML for "to date"
 500  * @param string $str4       HTML for user
 501  * @param string $str5       HTML for "list report"
 502  *
 503  * @return string HTML for form
 504  */
 505 function PMA_getHtmlForTrackingReportExportForm2(
 506     $url_params, $str1, $str2, $str3, $str4, $str5
 507 ) {
 508     $html = '<form method="post" action="tbl_tracking.php'
 509         . PMA_URL_getCommon(
 510             $url_params + array(
 511                 'report' => 'true', 'version' => $_REQUEST['version']
 512             )
 513         )
 514         . '">';
 515     $html .= sprintf(
 516         __('Show %1$s with dates from %2$s to %3$s by user %4$s %5$s'),
 517         $str1, $str2, $str3, $str4, $str5
 518     );
 519     $html .= '</form>';
 520
 521     $html .= '<form class="disableAjax" method="post" action="tbl_tracking.php'
 522         . PMA_URL_getCommon(
 523             $url_params
 524             + array('report' => 'true', 'version' => $_REQUEST['version'])
 525         )
 526         . '">';
 527     $html .= '<input type="hidden" name="logtype" value="'
 528         . htmlspecialchars($_REQUEST['logtype']) . '" />';
 529     $html .= '<input type="hidden" name="date_from" value="'
 530         . htmlspecialchars($_REQUEST['date_from']) . '" />';
 531     $html .= '<input type="hidden" name="date_to" value="'
 532         . htmlspecialchars($_REQUEST['date_to']) . '" />';
 533     $html .= '<input type="hidden" name="users" value="'
 534         . htmlspecialchars($_REQUEST['users']) . '" />';
 535
 536     $str_export1 = '<select name="export_type">'
 537         . '<option value="sqldumpfile">' . __('SQL dump (file download)')
 538         . '</option>'
 539         . '<option value="sqldump">' . __('SQL dump') . '</option>'
 540         . '<option value="execution" onclick="alert(\''
 541         . PMA_escapeJsString(
 542             __('This option will replace your table and contained data.')
 543         )
 544         . '\')">' . __('SQL execution') . '</option>' . '</select>';
 545
 546     $str_export2 = '<input type="hidden" name="report_export" value="1" />'
 547         . '<input type="submit" value="' . __('Go') . '" />';
 548
 549     $html .= "<br/>" . sprintf(__('Export as %s'), $str_export1)
 550         . $str_export2 . "<br/>";
 551     $html .= '</form>';
 552     return $html;
 553 }
 554
 555 /**
 556  * Function to get html for data manipulation statements
 557  *
 558  * @param array  $data               data
 559  * @param array  $filter_users       filter users
 560  * @param int    $filter_ts_from     filter time staml from
 561  * @param int    $filter_ts_to       filter time stamp to
 562  * @param array  $url_params         url parameters
 563  * @param int    $ddlog_count        data definition log count
 564  * @param string $drop_image_or_text drop image or text
 565  *
 566  * @return string
 567  */
 568 function PMA_getHtmlForDataManipulationStatements($data, $filter_users,
 569     $filter_ts_from, $filter_ts_to, $url_params, $ddlog_count,
 570     $drop_image_or_text
 571 ) {
 572     $i = $ddlog_count;
 573     $html = '<table id="dml_versions" class="data" width="100%">';
 574     $html .= '<thead>';
 575     $html .= '<tr>';
 576     $html .= '<th width="18">#</th>';
 577     $html .= '<th width="100">' . __('Date') . '</th>';
 578     $html .= '<th width="60">' . __('Username') . '</th>';
 579     $html .= '<th>' . __('Data manipulation statement') . '</th>';
 580     $html .= '<th>' . __('Delete') . '</th>';
 581     $html .= '</tr>';
 582     $html .= '</thead>';
 583     $html .= '<tbody>';
 584
 585     $style = 'odd';
 586     foreach ($data['dmlog'] as $entry) {
 587         $html .= PMA_getHtmlForDataManipulationStatement(
 588             $entry, $filter_users, $filter_ts_from, $filter_ts_to, $style, $i,
 589             $url_params, $ddlog_count, $drop_image_or_text
 590         );
 591         if ($style == 'even') {
 592             $style = 'odd';
 593         } else {
 594             $style = 'even';
 595         }
 596         $i++;
 597     }
 598     $html .= '</tbody>';
 599     $html .= '</table>';
 600
 601     return $html;
 602 }
 603
 604 /**
 605  * Function to get html for one data manipulation statement
 606  *
 607  * @param array  $entry              entry
 608  * @param array  $filter_users       filter users
 609  * @param int    $filter_ts_from     filter time stamp from
 610  * @param int    $filter_ts_to       filter time stamp to
 611  * @param string $style              style
 612  * @param int    $i                  field number
 613  * @param array  $url_params         url parameters
 614  * @param int    $ddlog_count        data definition log count
 615  * @param string $drop_image_or_text drop image or text
 616  *
 617  * @return string
 618  */
 619 function PMA_getHtmlForDataManipulationStatement($entry, $filter_users,
 620     $filter_ts_from, $filter_ts_to, $style, $i, $url_params, $ddlog_count,
 621     $drop_image_or_text
 622 ) {
 623     $statement  = PMA_Util::formatSql($entry['statement'], true);
 624     $timestamp = strtotime($entry['date']);
 625     $filtered_user = in_array($entry['username'], $filter_users);
 626     $html = null;
 627
 628     if ($timestamp >= $filter_ts_from
 629         && $timestamp <= $filter_ts_to
 630         && (in_array('*', $filter_users) || $filtered_user)
 631     ) {
 632         $html = '<tr class="noclick ' . $style . '">';
 633         $html .= '<td><small>' . $i . '</small></td>';
 634         $html .= '<td><small>'
 635             . htmlspecialchars($entry['date']) . '</small></td>';
 636         $html .= '<td><small>'
 637             . htmlspecialchars($entry['username']) . '</small></td>';
 638         $html .= '<td>' . $statement . '</td>';
 639         $html .= '<td class="nowrap"><a href="tbl_tracking.php?'
 640             . PMA_URL_getCommon(
 641                 $url_params + array(
 642                     'report' => 'true',
 643                     'version' => $_REQUEST['version'],
 644                     'delete_dmlog' => ($i - $ddlog_count),
 645                 )
 646             )
 647             . '">'
 648             . $drop_image_or_text
 649             . '</a></td>';
 650         $html .= '</tr>';
 651     }
 652
 653     return $html;
 654 }
 655 /**
 656  * Function to get html for data definition statements in schema snapshot
 657  *
 658  * @param array  $data               data
 659  * @param array  $filter_users       filter users
 660  * @param int    $filter_ts_from     filter time stamp from
 661  * @param int    $filter_ts_to       filter time stamp to
 662  * @param array  $url_params         url parameters
 663  * @param string $drop_image_or_text drop image or text
 664  *
 665  * @return string
 666  */
 667 function PMA_getHtmlForDataDefinitionStatements($data, $filter_users,
 668     $filter_ts_from, $filter_ts_to, $url_params, $drop_image_or_text
 669 ) {
 670     $i = 1;
 671     $html  = '<table id="ddl_versions" class="data" width="100%">';
 672     $html .= '<thead>';
 673     $html .= '<tr>';
 674     $html .= '<th width="18">#</th>';
 675     $html .= '<th width="100">' . __('Date') . '</th>';
 676     $html .= '<th width="60">' . __('Username') . '</th>';
 677     $html .= '<th>' . __('Data definition statement') . '</th>';
 678     $html .= '<th>' . __('Delete') . '</th>';
 679     $html .= '</tr>';
 680     $html .= '</thead>';
 681     $html .= '<tbody>';
 682
 683     $style = 'odd';
 684     foreach ($data['ddlog'] as $entry) {
 685         $html .= PMA_getHtmlForDataDefinitionStatement(
 686             $entry, $filter_users, $filter_ts_from, $filter_ts_to, $style, $i,
 687             $url_params, $drop_image_or_text
 688         );
 689         if ($style == 'even') {
 690             $style = 'odd';
 691         } else {
 692             $style = 'even';
 693         }
 694         $i++;
 695     }
 696     $html .= '</tbody>';
 697     $html .= '</table>';
 698
 699     return array($html, $i);
 700 }
 701 /**
 702  * Function to get html for a data definition statement in schema snapshot
 703  *
 704  * @param array  $entry              entry
 705  * @param array  $filter_users       filter users
 706  * @param int    $filter_ts_from     filter time stamp from
 707  * @param int    $filter_ts_to       filter time stamp to
 708  * @param string $style              style
 709  * @param int    $i                  column number
 710  * @param array  $url_params         url parameters
 711  * @param string $drop_image_or_text drop image or text
 712  *
 713  * @return string
 714  */
 715 function PMA_getHtmlForDataDefinitionStatement($entry, $filter_users,
 716     $filter_ts_from, $filter_ts_to, $style, $i, $url_params, $drop_image_or_text
 717 ) {
 718     $statement  = PMA_Util::formatSql($entry['statement'], true);
 719     $timestamp = strtotime($entry['date']);
 720     $filtered_user = in_array($entry['username'], $filter_users);
 721     $html = null;
 722
 723     if ($timestamp >= $filter_ts_from
 724         && $timestamp <= $filter_ts_to
 725         && (in_array('*', $filter_users) || $filtered_user)
 726     ) {
 727         $html = '<tr class="noclick ' . $style . '">';
 728         $html .= '<td><small>' . $i . '</small></td>';
 729         $html .= '<td><small>'
 730             . htmlspecialchars($entry['date']) . '</small></td>';
 731         $html .= '<td><small>'
 732             . htmlspecialchars($entry['username']) . '</small></td>';
 733         $html .= '<td>' . $statement . '</td>';
 734         $html .= '<td class="nowrap"><a href="tbl_tracking.php'
 735             . PMA_URL_getCommon(
 736                 $url_params + array(
 737                     'report' => 'true',
 738                     'version' => $_REQUEST['version'],
 739                     'delete_ddlog' => ($i - 1),
 740                 )
 741             )
 742             . '">' . $drop_image_or_text
 743             . '</a></td>';
 744         $html .= '</tr>';
 745     }
 746
 747     return $html;
 748 }
 749 /**
 750  * Function to get html for schema snapshot
 751  *
 752  * @param string $url_query url query
 753  *
 754  * @return string
 755  */
 756 function PMA_getHtmlForSchemaSnapshot($url_query)
 757 {
 758     $html = '<h3>' . __('Structure snapshot')
 759         . '  [<a href="tbl_tracking.php?' . $url_query . '">' . __('Close')
 760         . '</a>]</h3>';
 761     $data = PMA_Tracker::getTrackedData(
 762         $_REQUEST['db'], $_REQUEST['table'], $_REQUEST['version']
 763     );
 764
 765     // Get first DROP TABLE/VIEW and CREATE TABLE/VIEW statements
 766     $drop_create_statements = $data['ddlog'][0]['statement'];
 767
 768     if (strstr($data['ddlog'][0]['statement'], 'DROP TABLE')
 769         || strstr($data['ddlog'][0]['statement'], 'DROP VIEW')
 770     ) {
 771         $drop_create_statements .= $data['ddlog'][1]['statement'];
 772     }
 773     // Print SQL code
 774     $html .= PMA_Util::getMessage(
 775         sprintf(
 776             __('Version %s snapshot (SQL code)'),
 777             htmlspecialchars($_REQUEST['version'])
 778         ),
 779         $drop_create_statements
 780     );
 781
 782     // Unserialize snapshot
 783     $temp = unserialize($data['schema_snapshot']);
 784     $columns = $temp['COLUMNS'];
 785     $indexes = $temp['INDEXES'];
 786     $html .= PMA_getHtmlForColumns($columns);
 787
 788     if (count($indexes) > 0) {
 789         $html .= PMA_getHtmlForIndexes($indexes);
 790     } // endif
 791     $html .= '<br /><hr /><br />';
 792
 793     return $html;
 794 }
 795
 796 /**
 797  * Function to get html for displaying columns in the schema snapshot
 798  *
 799  * @param array $columns columns
 800  *
 801  * @return string
 802  */
 803 function PMA_getHtmlForColumns($columns)
 804 {
 805     $html = '<h3>' . __('Structure') . '</h3>';
 806     $html .= '<table id="tablestructure" class="data">';
 807     $html .= '<thead>';
 808     $html .= '<tr>';
 809     $html .= '<th>' . __('Column') . '</th>';
 810     $html .= '<th>' . __('Type') . '</th>';
 811     $html .= '<th>' . __('Collation') . '</th>';
 812     $html .= '<th>' . __('Null') . '</th>';
 813     $html .= '<th>' . __('Default') . '</th>';
 814     $html .= '<th>' . __('Extra') . '</th>';
 815     $html .= '<th>' . __('Comment') . '</th>';
 816     $html .= '</tr>';
 817     $html .= '</thead>';
 818     $html .= '<tbody>';
 819     $style = 'odd';
 820     foreach ($columns as $field) {
 821         $html .= PMA_getHtmlForField($field, $style);
 822         if ($style == 'even') {
 823             $style = 'odd';
 824         } else {
 825             $style = 'even';
 826         }
 827     }
 828
 829     $html .= '</tbody>';
 830     $html .= '</table>';
 831
 832     return $html;
 833 }
 834
 835 /**
 836  * Function to get html for field
 837  *
 838  * @param array  $field field
 839  * @param string $style style
 840  *
 841  * @return string
 842  */
 843 function PMA_getHtmlForField($field, $style)
 844 {
 845     $html = '<tr class="noclick ' . $style . '">';
 846     if ($field['Key'] == 'PRI') {
 847         $html .= '<td><b><u>' . htmlspecialchars($field['Field']) . '</u></b></td>';
 848     } else {
 849         $html .= '<td><b>' . htmlspecialchars($field['Field']) . '</b></td>';
 850     }
 851     $html .= "\n";
 852     $html .= '<td>' . htmlspecialchars($field['Type']) . '</td>';
 853     $html .= '<td>' . htmlspecialchars($field['Collation']) . '</td>';
 854     $html .= '<td>' . (($field['Null'] == 'YES') ? __('Yes') : __('No')) . '</td>';
 855     $html .= '<td>';
 856     if (isset($field['Default'])) {
 857         $extracted_columnspec = PMA_Util::extractColumnSpec($field['Type']);
 858         if ($extracted_columnspec['type'] == 'bit') {
 859             // here, $field['Default'] contains something like b'010'
 860             $html .= PMA_Util::convertBitDefaultValue($field['Default']);
 861         } else {
 862             $html .= htmlspecialchars($field['Default']);
 863         }
 864     } else {
 865         if ($field['Null'] == 'YES') {
 866             $html .= '<i>NULL</i>';
 867         } else {
 868             $html .= '<i>' . _pgettext('None for default', 'None') . '</i>';
 869         }
 870     }
 871     $html .= '</td>';
 872     $html .= '<td>' . htmlspecialchars($field['Extra']) . '</td>';
 873     $html .= '<td>' . htmlspecialchars($field['Comment']) . '</td>';
 874     $html .= '</tr>';
 875
 876     return $html;
 877 }
 878
 879 /**
 880  * Fuunction to get html for the indexes in schema snapshot
 881  *
 882  * @param array $indexes indexes
 883  *
 884  * @return string
 885  */
 886 function PMA_getHtmlForIndexes($indexes)
 887 {
 888     $html = '<h3>' . __('Indexes') . '</h3>';
 889     $html .= '<table id="tablestructure_indexes" class="data">';
 890     $html .= '<thead>';
 891     $html .= '<tr>';
 892     $html .= '<th>' . __('Keyname') . '</th>';
 893     $html .= '<th>' . __('Type') . '</th>';
 894     $html .= '<th>' . __('Unique') . '</th>';
 895     $html .= '<th>' . __('Packed') . '</th>';
 896     $html .= '<th>' . __('Column') . '</th>';
 897     $html .= '<th>' . __('Cardinality') . '</th>';
 898     $html .= '<th>' . __('Collation') . '</th>';
 899     $html .= '<th>' . __('Null') . '</th>';
 900     $html .= '<th>' . __('Comment') . '</th>';
 901     $html .= '</tr>';
 902     $html .= '<tbody>';
 903
 904     $style = 'odd';
 905     foreach ($indexes as $index) {
 906         $html .= PMA_getHtmlForIndex($index, $style);
 907         if ($style == 'even') {
 908             $style = 'odd';
 909         } else {
 910             $style = 'even';
 911         }
 912     }
 913     $html .= '</tbody>';
 914     $html .= '</table>';
 915     return $html;
 916 }
 917
 918 /**
 919  * Funtion to get html for an index in schema snapshot
 920  *
 921  * @param array  $index index
 922  * @param string $style style
 923  *
 924  * @return string
 925  */
 926 function PMA_getHtmlForIndex($index, $style)
 927 {
 928     if ($index['Non_unique'] == 0) {
 929         $str_unique = __('Yes');
 930     } else {
 931         $str_unique = __('No');
 932     }
 933     if ($index['Packed'] != '') {
 934         $str_packed = __('Yes');
 935     } else {
 936         $str_packed = __('No');
 937     }
 938
 939     $html  = '<tr class="noclick ' . $style . '">';
 940     $html .= '<td><b>' . htmlspecialchars($index['Key_name']) . '</b></td>';
 941     $html .= '<td>' . htmlspecialchars($index['Index_type']) . '</td>';
 942     $html .= '<td>' . $str_unique . '</td>';
 943     $html .= '<td>' . $str_packed . '</td>';
 944     $html .= '<td>' . htmlspecialchars($index['Column_name']) . '</td>';
 945     $html .= '<td>' . htmlspecialchars($index['Cardinality']) . '</td>';
 946     $html .= '<td>' . htmlspecialchars($index['Collation']) . '</td>';
 947     $html .= '<td>' . htmlspecialchars($index['Null']) . '</td>';
 948     $html .= '<td>' . htmlspecialchars($index['Comment']) . '</td>';
 949     $html .= '</tr>';
 950
 951     return $html;
 952 }
 953
 954 /**
 955  * Function to handle the tracking report
 956  *
 957  * @param array &$data tracked data
 958  *
 959  * @return void
 960  */
 961 function PMA_deleteTrackingReportRows(&$data)
 962 {
 963     if (isset($_REQUEST['delete_ddlog'])) {
 964         // Delete ddlog row data
 965         PMA_handleDeleteDataDefinitionsLog($data);
 966     }
 967
 968     if (isset($_REQUEST['delete_dmlog'])) {
 969         // Delete dmlog row data
 970         PMA_handleDeleteDataManipulationLog($data);
 971     }
 972 }
 973
 974 /**
 975  * Function to handle the delete ddlog row data
 976  *
 977  * @param array &$data tracked data
 978  *
 979  * @return void
 980  */
 981 function PMA_handleDeleteDataDefinitionsLog(&$data)
 982 {
 983     $delete_id = $_REQUEST['delete_ddlog'];
 984
 985     // Only in case of valable id
 986     if ($delete_id == (int)$delete_id) {
 987         unset($data['ddlog'][$delete_id]);
 988
 989         $successfullyDeleted = PMA_Tracker::changeTrackingData(
 990             $_REQUEST['db'], $_REQUEST['table'],
 991             $_REQUEST['version'], 'DDL', $data['ddlog']
 992         );
 993         if ($successfullyDeleted) {
 994             $msg = PMA_Message::success(
 995                 __('Tracking data definition successfully deleted')
 996             );
 997         } else {
 998             $msg = PMA_Message::rawError(__('Query error'));
 999         }
1000         $msg->display();
1001     }
1002 }
1003
1004 /**
1005  * Function to handle the delete of fmlog rows
1006  *
1007  * @param array &$data tracked data
1008  *
1009  * @return void
1010  */
1011 function PMA_handleDeleteDataManipulationLog(&$data)
1012 {
1013     $delete_id = $_REQUEST['delete_dmlog'];
1014
1015     // Only in case of valable id
1016     if ($delete_id == (int)$delete_id) {
1017         unset($data['dmlog'][$delete_id]);
1018
1019         $successfullyDeleted = PMA_Tracker::changeTrackingData(
1020             $_REQUEST['db'], $_REQUEST['table'],
1021             $_REQUEST['version'], 'DML', $data['dmlog']
1022         );
1023         if ($successfullyDeleted) {
1024             $msg = PMA_Message::success(
1025                 __('Tracking data manipulation successfully deleted')
1026             );
1027         } else {
1028             $msg = PMA_Message::rawError(__('Query error'));
1029         }
1030         $msg->display();
1031     }
1032 }
1033
1034 /**
1035  * Function to export as sql dump
1036  *
1037  * @param array $entries entries
1038  *
1039  * @return void
1040  */
1041 function PMA_exportAsSQLDump($entries)
1042 {
1043     $new_query = "# "
1044         . __(
1045             'You can execute the dump by creating and using a temporary database. '
1046             . 'Please ensure that you have the privileges to do so.'
1047         )
1048         . "\n"
1049         . "# " . __('Comment out these two lines if you do not need them.') . "\n"
1050         . "\n"
1051         . "CREATE database IF NOT EXISTS pma_temp_db; \n"
1052         . "USE pma_temp_db; \n"
1053         . "\n";
1054
1055     foreach ($entries as $entry) {
1056         $new_query .= $entry['statement'];
1057     }
1058     $msg = PMA_Message::success(
1059         __('SQL statements exported. Please copy the dump or execute it.')
1060     );
1061     $msg->display();
1062
1063     $db_temp = $GLOBALS['db'];
1064     $table_temp = $GLOBALS['table'];
1065
1066     $GLOBALS['db'] = $GLOBALS['table'] = '';
1067     include_once './libraries/sql_query_form.lib.php';
1068
1069     PMA_getHtmlForSqlQueryForm($new_query, 'sql');
1070
1071     $GLOBALS['db'] = $db_temp;
1072     $GLOBALS['table'] = $table_temp;
1073 }
1074
1075 /**
1076  * Function to export as sql execution
1077  *
1078  * @param array $entries entries
1079  *
1080  * @return array
1081  */
1082 function PMA_exportAsSQLExecution($entries)
1083 {
1084     foreach ($entries as $entry) {
1085         $sql_result = $GLOBALS['dbi']->query("/*NOTRACK*/\n" . $entry['statement']);
1086     }
1087     $msg = PMA_Message::success(__('SQL statements executed.'));
1088     $msg->display();
1089
1090     return $sql_result;
1091 }
1092
1093 /**
1094  * Function to export as entries
1095  *
1096  * @param array $entries entries
1097  *
1098  * @return void
1099  */
1100 function PMA_exportAsFileDownload($entries)
1101 {
1102     @ini_set('url_rewriter.tags', '');
1103
1104     $dump = "# " . sprintf(
1105         __('Tracking report for table `%s`'), htmlspecialchars($_REQUEST['table'])
1106     )
1107     . "\n" . "# " . date('Y-m-d H:i:s') . "\n";
1108     foreach ($entries as $entry) {
1109         $dump .= $entry['statement'];
1110     }
1111     $filename = 'log_' . htmlspecialchars($_REQUEST['table']) . '.sql';
1112     PMA_downloadHeader($filename, 'text/x-sql', strlen($dump));
1113
1114     $response = PMA_Response::getInstance();
1115     $response->addHTML($dump);
1116
1117     exit();
1118 }
1119
1120 /**
1121  * Function to activate tracking
1122  *
1123  * @return void
1124  */
1125 function PMA_activateTracking()
1126 {
1127     $activated = PMA_Tracker::activateTracking(
1128         $GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version']
1129     );
1130     if ($activated) {
1131         $msg = PMA_Message::success(
1132             sprintf(
1133                 __('Tracking for %1$s was activated at version %2$s.'),
1134                 htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table']),
1135                 htmlspecialchars($_REQUEST['version'])
1136             )
1137         );
1138         $msg->display();
1139     }
1140 }
1141
1142 /**
1143  * Function to deactivate tracking
1144  *
1145  * @return void
1146  */
1147 function PMA_deactivateTracking()
1148 {
1149     $deactivated = PMA_Tracker::deactivateTracking(
1150         $GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version']
1151     );
1152     if ($deactivated) {
1153         $msg = PMA_Message::success(
1154             sprintf(
1155                 __('Tracking for %1$s was deactivated at version %2$s.'),
1156                 htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table']),
1157                 htmlspecialchars($_REQUEST['version'])
1158             )
1159         );
1160         $msg->display();
1161     }
1162 }
1163
1164 /**
1165  * Function to get tracking set
1166  *
1167  * @return string
1168  */
1169 function PMA_getTrackingSet()
1170 {
1171     $tracking_set = '';
1172
1173     if ($_REQUEST['alter_table'] == true) {
1174         $tracking_set .= 'ALTER TABLE,';
1175     }
1176     if ($_REQUEST['rename_table'] == true) {
1177         $tracking_set .= 'RENAME TABLE,';
1178     }
1179     if ($_REQUEST['create_table'] == true) {
1180         $tracking_set .= 'CREATE TABLE,';
1181     }
1182     if ($_REQUEST['drop_table'] == true) {
1183         $tracking_set .= 'DROP TABLE,';
1184     }
1185     if ($_REQUEST['create_index'] == true) {
1186         $tracking_set .= 'CREATE INDEX,';
1187     }
1188     if ($_REQUEST['drop_index'] == true) {
1189         $tracking_set .= 'DROP INDEX,';
1190     }
1191     if ($_REQUEST['insert'] == true) {
1192         $tracking_set .= 'INSERT,';
1193     }
1194     if ($_REQUEST['update'] == true) {
1195         $tracking_set .= 'UPDATE,';
1196     }
1197     if ($_REQUEST['delete'] == true) {
1198         $tracking_set .= 'DELETE,';
1199     }
1200     if ($_REQUEST['truncate'] == true) {
1201         $tracking_set .= 'TRUNCATE,';
1202     }
1203     $tracking_set = rtrim($tracking_set, ',');
1204
1205     return $tracking_set;
1206 }
1207
1208 /**
1209  * Function to create the tracking version
1210  *
1211  * @return void
1212  */
1213 function PMA_createTrackingVersion()
1214 {
1215     $tracking_set = PMA_getTrackingSet();
1216
1217     $versionCreated = PMA_Tracker::createVersion(
1218         $GLOBALS['db'],
1219         $GLOBALS['table'],
1220         $_REQUEST['version'],
1221         $tracking_set,
1222         PMA_Table::isView($GLOBALS['db'], $GLOBALS['table'])
1223     );
1224     if ($versionCreated) {
1225         $msg = PMA_Message::success(
1226             sprintf(
1227                 __('Version %1$s was created, tracking for %2$s is active.'),
1228                 htmlspecialchars($_REQUEST['version']),
1229                 htmlspecialchars($GLOBALS['db'] . '.' . $GLOBALS['table'])
1230             )
1231         );
1232         $msg->display();
1233     }
1234 }
1235
1236 /**
1237  * Function to get the entries
1238  *
1239  * @param array $data           data
1240  * @param int   $filter_ts_from filter time stamp from
1241  * @param int   $filter_ts_to   filter time stamp to
1242  * @param array $filter_users   filter users
1243  *
1244  * @return array
1245  */
1246 function PMA_getEntries($data, $filter_ts_from, $filter_ts_to, $filter_users)
1247 {
1248     $entries = array();
1249     // Filtering data definition statements
1250     if ($_REQUEST['logtype'] == 'schema'
1251         || $_REQUEST['logtype'] == 'schema_and_data'
1252     ) {
1253         $entries = array_merge(
1254             $entries,
1255             PMA_filterTracking(
1256                 $data['ddlog'], $filter_ts_from, $filter_ts_to, $filter_users
1257             )
1258         );
1259     }
1260
1261     // Filtering data manipulation statements
1262     if ($_REQUEST['logtype'] == 'data'
1263         || $_REQUEST['logtype'] == 'schema_and_data'
1264     ) {
1265         $entries = array_merge(
1266             $entries,
1267             PMA_filterTracking(
1268                 $data['dmlog'], $filter_ts_from, $filter_ts_to, $filter_users
1269             )
1270         );
1271     }
1272
1273     // Sort it
1274     $ids = $timestamps = $usernames = $statements = array();
1275     foreach ($entries as $key => $row) {
1276         $ids[$key]        = $row['id'];
1277         $timestamps[$key] = $row['timestamp'];
1278         $usernames[$key]  = $row['username'];
1279         $statements[$key] = $row['statement'];
1280     }
1281
1282     array_multisort(
1283         $timestamps, SORT_ASC, $ids, SORT_ASC, $usernames,
1284         SORT_ASC, $statements, SORT_ASC, $entries
1285     );
1286
1287     return $entries;
1288 }
1289 ?>