2 /* vim: set expandtab sw=4 ts=4 sts=4: */
5 * @author Alexander Rutkowski
11 require_once './libraries/common.inc.php';
12 require_once './libraries/Table.class.php';
14 require './libraries/tbl_common.php';
15 $url_query .= '&goto=tbl_tracking.php&back=tbl_tracking.php';
16 $url_params['goto'] = 'tbl_tracking.php';;
17 $url_params['back'] = 'tbl_tracking.php';
19 // Get relation settings
20 require_once './libraries/relation.lib.php';
22 // Init vars for tracking report
23 if (isset($_REQUEST['report']) ||
isset($_REQUEST['report_export'])) {
24 $data = PMA_Tracker
::getTrackedData($_REQUEST['db'], $_REQUEST['table'], $_REQUEST['version']);
26 $selection_schema = '';
30 if (!isset($_REQUEST['logtype'])) {
31 $_REQUEST['logtype'] = 'schema_and_data';
33 if ($_REQUEST['logtype'] == 'schema') {
34 $selection_schema = 'selected';
35 } elseif($_REQUEST['logtype'] == 'data') {
36 $selection_data = 'selected';
38 $selection_both = 'selected';
40 if (! isset($_REQUEST['date_from'])) {
41 $_REQUEST['date_from'] = $data['date_from'];
43 if (! isset($_REQUEST['date_to'])) {
44 $_REQUEST['date_to'] = $data['date_to'];
46 if (! isset($_REQUEST['users'])) {
47 $_REQUEST['users'] = '*';
49 $filter_ts_from = strtotime($_REQUEST['date_from']);
50 $filter_ts_to = strtotime($_REQUEST['date_to']);
51 $filter_users = array_map('trim', explode(',', $_REQUEST['users']));
55 if (isset($_REQUEST['report_export'])) {
56 // Filtering data definition statements
57 if ($_REQUEST['logtype'] == 'schema' ||
$_REQUEST['logtype'] == 'schema_and_data') {
59 foreach( $data['ddlog'] as $entry ) {
60 $timestamp = strtotime($entry['date']);
62 if ($timestamp >= $filter_ts_from && $timestamp <= $filter_ts_to &&
63 ( in_array('*', $filter_users) ||
in_array($entry['username'], $filter_users) ) ) {
64 $entries[] = array( 'id' => $id,
65 'timestamp' => $timestamp,
66 'username' => $entry['username'],
67 'statement' => $entry['statement']
74 // Filtering data manipulation statments
75 if ($_REQUEST['logtype'] == 'data' ||
$_REQUEST['logtype'] == 'schema_and_data') {
77 foreach( $data['dmlog'] as $entry ) {
78 $timestamp = strtotime($entry['date']);
80 if( $timestamp >= $filter_ts_from && $timestamp <= $filter_ts_to &&
81 ( in_array('*', $filter_users) or in_array($entry['username'], $filter_users) ) ) {
82 $entries[] = array( 'id' => $id,
83 'timestamp' => $timestamp,
84 'username' => $entry['username'],
85 'statement' => $entry['statement']
93 foreach ($entries as $key => $row) {
94 $ids[$key] = $row['id'];
95 $timestamps[$key] = $row['timestamp'];
96 $usernames[$key] = $row['username'];
97 $statements[$key] = $row['statement'];
100 array_multisort($timestamps, SORT_ASC
, $ids, SORT_ASC
, $usernames, SORT_ASC
, $statements, SORT_ASC
, $entries);
104 // Export as file download
105 if (isset($_REQUEST['report_export']) && $_REQUEST['export_type'] == 'sqldumpfile') {
106 @ini_set
('url_rewriter.tags','');
108 $dump = "# Tracking report for table `" . htmlspecialchars($_REQUEST['table']) . "`\n" .
109 "# " . date('Y-m-d H:i:s') . "\n";
110 foreach($entries as $entry) {
111 $dump .= $entry['statement'];
113 $filename = 'log_' . htmlspecialchars($_REQUEST['table']) . '.sql';
114 header('Content-Type: text/x-sql');
115 header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
116 header('Content-Disposition: attachment; filename="' . $filename . '"');
117 if (PMA_USR_BROWSER_AGENT
== 'IE') {
118 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
119 header('Pragma: public');
121 header('Pragma: no-cache');
122 header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
131 * Gets tables informations
135 * Displays top menu links
137 require_once './libraries/tbl_links.inc.php';
144 // Create tracking version
145 if (isset($_REQUEST['submit_create_version'])) {
148 if ($_REQUEST['alter_table'] == true) {
149 $tracking_set .= 'ALTER TABLE,';
151 if ($_REQUEST['rename_table'] == true) {
152 $tracking_set .= 'RENAME TABLE,';
154 if ($_REQUEST['create_table'] == true) {
155 $tracking_set .= 'CREATE TABLE,';
157 if ($_REQUEST['drop_table'] == true) {
158 $tracking_set .= 'DROP TABLE,';
160 if ($_REQUEST['create_index'] == true) {
161 $tracking_set .= 'CREATE INDEX,';
163 if ($_REQUEST['drop_index'] == true) {
164 $tracking_set .= 'DROP INDEX,';
166 if ($_REQUEST['insert'] == true) {
167 $tracking_set .= 'INSERT,';
169 if ($_REQUEST['update'] == true) {
170 $tracking_set .= 'UPDATE,';
172 if ($_REQUEST['delete'] == true) {
173 $tracking_set .= 'DELETE,';
175 if ($_REQUEST['truncate'] == true) {
176 $tracking_set .= 'TRUNCATE,';
178 $tracking_set = rtrim($tracking_set, ',');
180 if (PMA_Tracker
::createVersion($GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version'], $tracking_set )) {
181 $msg = PMA_Message
::success(sprintf($strTrackingVersionCreated, $_REQUEST['version'], $GLOBALS['db'], $GLOBALS['table']));
186 // Deactivate tracking
187 if (isset($_REQUEST['submit_deactivate_now'])) {
188 if (PMA_Tracker
::deactivateTracking($GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version'])) {
189 $msg = PMA_Message
::success(sprintf($strTrackingVersionDeactivated, $GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version']));
195 if (isset($_REQUEST['submit_activate_now'])) {
196 if (PMA_Tracker
::activateTracking($GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version'])) {
197 $msg = PMA_Message
::success(sprintf($strTrackingVersionActivated, $GLOBALS['db'], $GLOBALS['table'], $_REQUEST['version']));
202 // Export as SQL execution
203 if (isset($_REQUEST['report_export']) && $_REQUEST['export_type'] == 'execution') {
204 foreach($entries as $entry) {
205 $sql_result = PMA_DBI_query( "/*NOTRACK*/\n" . $entry['statement'] );
207 $msg = PMA_Message
::success($strTrackingSQLExecuted);
211 // Export as SQL dump
212 if (isset($_REQUEST['report_export']) && $_REQUEST['export_type'] == 'sqldump')
214 $new_query = "# You can execute the dump by creating and using a temporary table. Please ensure that you have the privileges to do that. \n" .
215 "# Comment out or remove these two lines if you don't need them. \n" .
217 "CREATE database IF NOT EXISTS pma_temp_db; \n" .
218 "USE pma_temp_db; \n" .
220 "/* BEGIN OF SQL DUMP */ \n";
222 foreach($entries as $entry) {
223 $new_query .= $entry['statement'];
225 $msg = PMA_Message
::success($strTrackingSQLExported);
229 $table_temp = $table;
232 $GLOBALS['js_include'][] = 'functions.js';
233 require_once './libraries/sql_query_form.lib.php';
235 PMA_sqlQueryForm($new_query, 'sql');
238 $table = $table_temp;
244 if (isset($_REQUEST['snapshot'])) {
246 <h3
><?php
echo $strTrackingStructureSnapshot;?
> [<a href
="tbl_tracking.php?<?php echo $url_query;?>"><?php
echo $strTrackingReportClose;?
></a
>]</h3
>
248 $data = PMA_Tracker
::getTrackedData($_REQUEST['db'], $_REQUEST['table'], $_REQUEST['version']);
250 // Get first DROP TABLE and CREATE TABLE statements
251 $drop_create_statements = $data['ddlog'][0]['statement'];
253 if (strstr($data['ddlog'][0]['statement'], 'DROP TABLE')) {
254 $drop_create_statements .= $data['ddlog'][1]['statement'];
257 PMA_showMessage(sprintf($strTrackingVersionSnapshotSQL, $_REQUEST['version']), $drop_create_statements);
259 // Unserialize snapshot
260 $temp = unserialize($data['schema_snapshot']);
261 $columns = $temp['COLUMNS'];
262 $indexes = $temp['INDEXES'];
264 <h3
><?php
echo $strStructure;?
></h3
>
265 <table id
="tablestructure" class="data">
268 <th
><?php
echo $strField; ?
></th
>
269 <th
><?php
echo $strType; ?
></th
>
270 <th
><?php
echo $strCollation; ?
></th
>
271 <th
><?php
echo $strNull; ?
></th
>
272 <th
><?php
echo $strDefault; ?
></th
>
273 <th
><?php
echo $strExtra; ?
></th
>
274 <th
><?php
echo $strComment; ?
></th
>
280 foreach($columns as $field_index => $field) {
282 <tr
class="<?php echo $style; ?>">
284 if ($field['Key'] == 'PRI') {
285 echo '<td><b><u>' . $field['Field'] . '</u></b></td>' . "\n";
287 echo '<td><b>' . $field['Field'] . '</b></td>' . "\n";
290 <td
><?php
echo $field['Type'];?
></td
>
291 <td
><?php
echo $field['Collation'];?
></td
>
292 <td
><?php
echo $field['Null'];?
></td
>
293 <td
><?php
echo $field['Default'];?
></td
>
294 <td
><?php
echo $field['Extra'];?
></td
>
295 <td
><?php
echo $field['Comment'];?
></td
>
298 if ($style == 'even') {
309 if (count($indexes) > 0) {
311 <h3
><?php
echo $strIndexes;?
></h3
>
312 <table id
="tablestructure_indexes" class="data">
315 <th
><?php
echo $strKeyname;?
></th
>
316 <th
><?php
echo $strType;?
></th
>
317 <th
><?php
echo $strUnique;?
></th
>
318 <th
><?php
echo $strPacked;?
></th
>
319 <th
><?php
echo $strField;?
></th
>
320 <th
><?php
echo $strCardinality;?
></th
>
321 <th
><?php
echo $strCollation;?
></th
>
322 <th
><?php
echo $strNull;?
></th
>
323 <th
><?php
echo $strComment;?
></th
>
328 foreach ($indexes as $indexes_index => $index) {
329 if ($index['Non_unique'] == 0) {
330 $str_unique = $strYes;
332 $str_unique = $strNo;
334 if ($index['Packed'] != '') {
335 $str_packed = $strYes;
337 $str_packed = $strNo;
340 <tr
class="<?php echo $style; ?>">
341 <td
><b
><?php
echo $index['Key_name'];?
></b
></td
>
342 <td
><?php
echo $index['Index_type'];?
></td
>
343 <td
><?php
echo $str_unique;?
></td
>
344 <td
><?php
echo $str_packed;?
></td
>
345 <td
><?php
echo $index['Column_name'];?
></td
>
346 <td
><?php
echo $index['Cardinality'];?
></td
>
347 <td
><?php
echo $index['Collation'];?
></td
>
348 <td
><?php
echo $index['Null'];?
></td
>
349 <td
><?php
echo $index['Comment'];?
></td
>
352 if ($style == 'even') {
367 // end of snapshot report
372 if (isset($_REQUEST['report']) ||
isset($_REQUEST['report_export'])) {
374 <h3
><?php
echo $strTrackingReport;?
> [<a href
="tbl_tracking.php?<?php echo $url_query;?>"><?php
echo $strTrackingReportClose;?
></a
>]</h3
>
376 <small
><?php
echo $strTrackingStatements . ' ' . $data['tracking']; ?
></small
><br
/>
379 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>&report=true&version=<?php echo $_REQUEST['version'];?>">
382 $str1 = '<select name="logtype">' .
383 '<option value="schema"' . $selection_schema . '>' . $strStrucOnly . '</option>' .
384 '<option value="data"' . $selection_data . '>' . $strDataOnly . '</option>' .
385 '<option value="schema_and_data"' . $selection_both . '>' . $strStrucData . '</option>' .
387 $str2 = '<input type="text" name="date_from" value="' . $_REQUEST['date_from'] . '" size="19">';
388 $str3 = '<input type="text" name="date_to" value="' . $_REQUEST['date_to'] . '" size="19">';
389 $str4 = '<input type="text" name="users" value="' . $_REQUEST['users'] . '">';
390 $str5 = '<input type="submit" name="list_report" value="' . $strGo . '">';
392 printf($strTrackingShowLogDateUsers, $str1, $str2, $str3, $str4, $str5);
396 * First, list tracked data defintion statements
399 if ($selection_schema == 'selected' ||
$selection_both == 'selected') {
401 <table id
="versions" class="data" width
="100%">
404 <th width
="18">#</th>
405 <th width
="100"><?php
echo $strTrackingDate;?
></th
>
406 <th width
="60"><?php
echo $strTrackingUsername;?
></th
>
407 <th
><?php
echo $strTrackingDataDefinitionStatement;?
></th
>
413 foreach ($data['ddlog'] as $entry) {
414 $parsed_sql = PMA_SQP_parse($entry['statement']);
415 $statement = PMA_formatSql($parsed_sql);
416 $timestamp = strtotime($entry['date']);
418 if ($timestamp >= $filter_ts_from && $timestamp <= $filter_ts_to &&
419 ( in_array('*', $filter_users) ||
in_array($entry['username'], $filter_users) ) ) {
421 <tr
class="<?php echo $style; ?>">
422 <td
><small
><?php
echo $i;?
></small
></td
>
423 <td
><small
><?php
echo $entry['date'];?
></small
></td
>
424 <td
><small
><?php
echo $entry['username']; ?
></small
></td
>
425 <td
><?php
echo $statement; ?
></td
>
428 if ($style == 'even') {
444 * Secondly, list tracked data manipulation statements
447 if (($selection_data == 'selected' ||
$selection_both == 'selected') && count($data['dmlog']) > 0) {
449 <table id
="versions" class="data" width
="100%">
452 <th width
="18">#</th>
453 <th width
="100"><?php
echo $strTrackingDate;?
></th
>
454 <th width
="60"><?php
echo $strTrackingUsername;?
></th
>
455 <th
><?php
echo $strTrackingDataManipulationStatement;?
></th
>
461 foreach ($data['dmlog'] as $entry) {
462 $parsed_sql = PMA_SQP_parse($entry['statement']);
463 $statement = PMA_formatSql($parsed_sql);
464 $timestamp = strtotime($entry['date']);
466 if ($timestamp >= $filter_ts_from && $timestamp <= $filter_ts_to &&
467 ( in_array('*', $filter_users) ||
in_array($entry['username'], $filter_users) ) ) {
469 <tr
class="<?php echo $style; ?>">
470 <td
><small
><?php
echo $i; ?
></small
></td
>
471 <td
><small
><?php
echo $entry['date']; ?
></small
></td
>
472 <td
><small
><?php
echo $entry['username']; ?
></small
></td
>
473 <td
><?php
echo $statement; ?
></td
>
476 if ($style == 'even') {
491 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>&report=true&version=<?php echo $_REQUEST['version'];?>">
493 printf($strTrackingShowLogDateUsers, $str1, $str2, $str3, $str4, $str5);
495 $str_export1 = '<select name="export_type">' .
496 '<option value="sqldumpfile">' . $strTrackingSQLDumpFile . '</option>' .
497 '<option value="sqldump">' . $strTrackingSQLDump . '</option>' .
498 '<option value="execution" onclick="alert(\'' . $strTrackingSQLExecutionAlert .'\')">' . $strTrackingSQLExecution . '</option>' .
501 $str_export2 = '<input type="submit" name="report_export" value="' . $strGo .'">';
504 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>&report=true&version=<?php echo $_REQUEST['version'];?>">
505 <input type
="hidden" name
="logtype" value
="<?php echo $_REQUEST['logtype'];?>" />
506 <input type
="hidden" name
="date_from" value
="<?php echo $_REQUEST['date_from'];?>" />
507 <input type
="hidden" name
="date_to" value
="<?php echo $_REQUEST['date_to'];?>" />
508 <input type
="hidden" name
="users" value
="<?php echo $_REQUEST['users'];?>" />
510 echo "<br/>" . sprintf($strTrackingExportAs, $str_export1) . $str_export2 . "<br/>";
514 echo "<br/><br/><hr/><br/>\n";
519 * List selectable tables
522 $sql_query = " SELECT DISTINCT db_name, table_name FROM " .
523 PMA_backquote($GLOBALS['cfg']['Server']['pmadb']) . "." .
524 PMA_backquote($GLOBALS['cfg']['Server']['tracking']) .
525 " WHERE " . PMA_backquote('db_name') . " = '" . PMA_sqlAddslashes($GLOBALS['db']) . "' " .
526 " ORDER BY ". PMA_backquote('db_name') . ", " . PMA_backquote('table_name');
528 $sql_result = PMA_query_as_controluser($sql_query);
530 if (PMA_DBI_num_rows($sql_result) > 0) {
532 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query;?>">
533 <select name
="table">
535 while ($entries = PMA_DBI_fetch_array($sql_result)) {
536 if (PMA_Tracker
::isTracked($entries['db_name'], $entries['table_name'])) {
541 if ($entries['table_name'] == $_REQUEST['table']) {
542 $s = ' selected="selected"';
546 echo '<option value="' . $entries['table_name'] . '"' . $s . '>' . $entries['db_name'] . ' . ' . $entries['table_name'] . $status . '</option>' . "\n";
550 <input type
="submit" name
="show_versions_submit" value
="<?php echo $strTrackingShowVersions;?>" />
559 * List versions of current table
562 $sql_query = " SELECT * FROM " .
563 PMA_backquote($GLOBALS['cfg']['Server']['pmadb']) . "." .
564 PMA_backquote($GLOBALS['cfg']['Server']['tracking']) .
565 " WHERE " . PMA_backquote('db_name') . " = '" . PMA_sqlAddslashes($_REQUEST['db']) . "' ".
566 " AND " . PMA_backquote('table_name') . " = '" . PMA_sqlAddslashes($_REQUEST['table']) ."' ".
567 " ORDER BY ". PMA_backquote('version') . " DESC ";
569 $sql_result = PMA_query_as_controluser($sql_query);
572 $maxversion = PMA_DBI_fetch_array($sql_result);
573 $last_version = $maxversion['version'];
575 if ($last_version > 0) {
577 <table id
="versions" class="data">
580 <th
><?php
echo $strDatabase;?
></th
>
581 <th
><?php
echo $strTable;?
></th
>
582 <th
><?php
echo $strTrackingThVersion;?
></th
>
583 <th
><?php
echo $strTrackingThCreated;?
></th
>
584 <th
><?php
echo $strTrackingThUpdated;?
></th
>
585 <th
><?php
echo $strStatus;?
></th
>
586 <th
><?php
echo $strShow;?
></th
>
592 PMA_DBI_data_seek($sql_result, 0);
593 while($version = PMA_DBI_fetch_array($sql_result)) {
594 if ($version['tracking_active'] == 1) {
595 $version_status = $strTrackingStatusActive;
597 $version_status = $strTrackingStatusNotActive;
599 if (($version['version'] == $last_version) && ($version_status == $strTrackingStatusNotActive)) {
600 $tracking_active = false;
602 if (($version['version'] == $last_version) && ($version_status == $strTrackingStatusActive)) {
603 $tracking_active = true;
606 <tr
class="<?php echo $style;?>">
607 <td
><?php
echo $version['db_name'];?
></td
>
608 <td
><?php
echo $version['table_name'];?
></td
>
609 <td
><?php
echo $version['version'];?
></td
>
610 <td
><?php
echo $version['date_created'];?
></td
>
611 <td
><?php
echo $version['date_updated'];?
></td
>
612 <td
><?php
echo $version_status;?
></td
>
613 <td
> <a href
="tbl_tracking.php?<?php echo $url_query;?>&report=true&version=<?php echo $version['version'];?>"><?php
echo $strTrackingReport;?
></a
> |
<a href
="tbl_tracking.php?<?php echo $url_query;?>&snapshot=true&version=<?php echo $version['version'];?>"><?php
echo $strTrackingStructureSnapshot;?
></a
></td
>
616 if ($style == 'even') {
625 <?php
if ($tracking_active == true) {?
>
626 <div id
="div_deactivate_tracking">
627 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>">
629 <legend
><?php
printf($strTrackingDeactivateTrackingFor, $GLOBALS['db'], $GLOBALS['table']); ?
></legend
>
630 <input type
="hidden" name
="version" value
="<?php echo $last_version; ?>" />
631 <input type
="submit" name
="submit_deactivate_now" value
="<?php echo $strTrackingDeactivateNow; ?>" />
638 <?php
if ($tracking_active == false) {?
>
639 <div id
="div_activate_tracking">
640 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>">
642 <legend
><?php
printf($strTrackingActivateTrackingFor, $GLOBALS['db'], $GLOBALS['table']); ?
></legend
>
643 <input type
="hidden" name
="version" value
="<?php echo $last_version; ?>" />
644 <input type
="submit" name
="submit_activate_now" value
="<?php echo $strTrackingActivateNow; ?>" />
653 <div id
="div_create_version">
654 <form method
="post" action
="tbl_tracking.php?<?php echo $url_query; ?>">
655 <?php
echo PMA_generate_common_hidden_inputs($GLOBALS['db'], $GLOBALS['table']); ?
>
657 <legend
><?php
printf($strTrackingCreateVersionOf, ($last_version +
1), $GLOBALS['db'], $GLOBALS['table']); ?
></legend
>
659 <input type
="hidden" name
="version" value
="<?php echo ($last_version + 1); ?>" />
661 <p
><?php
echo $strTrackingTrackDDStatements;?
></p
>
662 <input type
="checkbox" name
="alter_table" value
="true" checked
="checked" /> ALTER TABLE
<br
/>
663 <input type
="checkbox" name
="rename_table" value
="true" checked
="checked" /> RENAME TABLE
<br
/>
664 <input type
="checkbox" name
="create_table" value
="true" checked
="checked" /> CREATE TABLE
<br
/>
665 <input type
="checkbox" name
="drop_table" value
="true" checked
="checked" /> DROP TABLE
<br
/>
667 <input type
="checkbox" name
="create_index" value
="true" checked
="checked" /> CREATE INDEX
<br
/>
668 <input type
="checkbox" name
="drop_index" value
="true" checked
="checked" /> DROP INDEX
<br
/>
669 <p
><?php
echo $strTrackingTrackDMStatements;?
></p
>
670 <input type
="checkbox" name
="insert" value
="true" checked
="checked" /> INSERT
<br
/>
671 <input type
="checkbox" name
="update" value
="true" checked
="checked" /> UPDATE
<br
/>
672 <input type
="checkbox" name
="delete" value
="true" checked
="checked" /> DELETE
<br
/>
673 <input type
="checkbox" name
="truncate" value
="true" checked
="checked" /> TRUNCATE
<br
/>
676 <fieldset
class="tblFooters">
677 <input type
="submit" name
="submit_create_version" value
="<?php echo $strTrackingCreateVersion; ?>" />
682 <br
class="clearfloat"/>
686 * Displays the footer
688 require_once './libraries/footer.inc.php';