db_operations.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * handles miscellaneous db operations:
   5  *  - move/rename
   6  *  - copy
   7  *  - changing collation
   8  *  - changing comment
   9  *  - adding tables
  10  *  - viewing PDF schemas
  11  *
  12  * @package phpMyAdmin
  13  */
  14
  15 /**
  16  * requirements
  17  */
  18 require_once './libraries/common.inc.php';
  19 require_once './libraries/mysql_charsets.lib.php';
  20
  21 // add blobstreaming library functions
  22 require_once "./libraries/blobstreaming.lib.php";
  23
  24 // add a javascript file for jQuery functions to handle Ajax actions
  25 // also add jQueryUI
  26 $GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.custom.js';
  27 $GLOBALS['js_include'][] = 'db_operations.js';
  28
  29 /**
  30  * Rename/move or copy database
  31  */
  32 if (strlen($db) && (! empty($db_rename) || ! empty($db_copy))) {
  33
  34     if (! empty($db_rename)) {
  35         $move = true;
  36     } else {
  37         $move = false;
  38     }
  39
  40     if (!isset($newname) || !strlen($newname)) {
  41         $message = PMA_Message::error(__('The database name is empty!'));
  42     } else {
  43         $sql_query = ''; // in case target db exists
  44         $_error = false;
  45         if ($move ||
  46          (isset($create_database_before_copying) && $create_database_before_copying)) {
  47             // lower_case_table_names=1 `DB` becomes `db`
  48             $lower_case_table_names = PMA_DBI_fetch_value('SHOW VARIABLES LIKE "lower_case_table_names"', 0, 1);
  49             if ($lower_case_table_names === '1') {
  50                 $newname = strtolower($newname);
  51             }
  52
  53             $local_query = 'CREATE DATABASE ' . PMA_backquote($newname);
  54             if (isset($db_collation)) {
  55                 $local_query .= ' DEFAULT' . PMA_generateCharsetQueryPart($db_collation);
  56             }
  57             $local_query .= ';';
  58             $sql_query = $local_query;
  59             // save the original db name because Tracker.class.php which
  60             // may be called under PMA_DBI_query() changes $GLOBALS['db']
  61             // for some statements, one of which being CREATE DATABASE
  62             $original_db = $db;
  63             PMA_DBI_query($local_query);
  64             $db = $original_db;
  65             unset($original_db);
  66
  67             // rebuild the database list because PMA_Table::moveCopy
  68             // checks in this list if the target db exists
  69             $GLOBALS['pma']->databases->build();
  70         }
  71
  72         if (isset($GLOBALS['add_constraints']) || $move) {
  73             $GLOBALS['sql_constraints_query_full_db'] = array();
  74         }
  75
  76         $tables_full = PMA_DBI_get_tables_full($db);
  77         $views = array();
  78
  79         // remove all foreign key constraints, otherwise we can get errors
  80         require_once './libraries/export/sql.php';
  81         foreach ($tables_full as $each_table => $tmp) {
  82             $sql_constraints = '';
  83             $sql_drop_foreign_keys = '';
  84             $sql_structure = PMA_getTableDef($db, $each_table, "\n", '', false, false);
  85             if ($move && ! empty($sql_drop_foreign_keys)) {
  86                 PMA_DBI_query($sql_drop_foreign_keys);
  87             }
  88             // keep the constraint we just dropped
  89             if (! empty($sql_constraints)) {
  90                 $GLOBALS['sql_constraints_query_full_db'][] = $sql_constraints;
  91             }
  92         }
  93         unset($sql_constraints, $sql_drop_foreign_keys, $sql_structure);
  94
  95
  96         foreach ($tables_full as $each_table => $tmp) {
  97             // to be able to rename a db containing views, we
  98             // first collect in $views all the views we find and we
  99             // will handle them after the tables
 100             /**
 101              * @todo support a view of a view
 102              */
 103             if (PMA_Table::isView($db, $each_table)) {
 104                 $views[] = $each_table;
 105                 continue;
 106             }
 107
 108             $back = $sql_query;
 109             $sql_query = '';
 110
 111             // value of $what for this table only
 112             $this_what = $what;
 113
 114             // do not copy the data from a Merge table
 115             // note: on the calling FORM, 'data' means 'structure and data'
 116             if (PMA_Table::isMerge($db, $each_table)) {
 117                 if ($this_what == 'data') {
 118                     $this_what = 'structure';
 119                 }
 120                 if ($this_what == 'dataonly') {
 121                     $this_what = 'nocopy';
 122                 }
 123             }
 124
 125             if ($this_what != 'nocopy') {
 126                 // keep the triggers from the original db+table
 127                 // (third param is empty because delimiters are only intended
 128                 //  for importing via the mysql client or our Import feature)
 129                 $triggers = PMA_DBI_get_triggers($db, $each_table, '');
 130
 131                 if (! PMA_Table::moveCopy($db, $each_table, $newname, $each_table,
 132                     isset($this_what) ? $this_what : 'data', $move, 'db_copy'))
 133                 {
 134                     $_error = true;
 135                     // $sql_query is filled by PMA_Table::moveCopy()
 136                     $sql_query = $back . $sql_query;
 137                     break;
 138                 }
 139                 // apply the triggers to the destination db+table
 140                 if ($triggers) {
 141                     PMA_DBI_select_db($newname);
 142                     foreach ($triggers as $trigger) {
 143                         PMA_DBI_query($trigger['create']);
 144                     }
 145                     unset($trigger);
 146                 }
 147                 unset($triggers);
 148
 149                 // this does not apply to a rename operation
 150                 if (isset($GLOBALS['add_constraints']) && !empty($GLOBALS['sql_constraints_query'])) {
 151                     $GLOBALS['sql_constraints_query_full_db'][] = $GLOBALS['sql_constraints_query'];
 152                     unset($GLOBALS['sql_constraints_query']);
 153                 }
 154             }
 155             // $sql_query is filled by PMA_Table::moveCopy()
 156             $sql_query = $back . $sql_query;
 157         } // end (foreach)
 158         unset($each_table);
 159
 160         // handle the views
 161         if (! $_error) {
 162             foreach ($views as $view) {
 163                 if (! PMA_Table::moveCopy($db, $view, $newname, $view,
 164                  'structure', $move, 'db_copy')) {
 165                     $_error = true;
 166                     break;
 167                 }
 168             }
 169         }
 170         unset($view, $views);
 171
 172         // now that all tables exist, create all the accumulated constraints
 173         if (! $_error && count($GLOBALS['sql_constraints_query_full_db']) > 0) {
 174             PMA_DBI_select_db($newname);
 175             foreach ($GLOBALS['sql_constraints_query_full_db'] as $one_query) {
 176                 PMA_DBI_query($one_query);
 177             // and prepare to display them
 178                 $GLOBALS['sql_query'] .= "\n" . $one_query;
 179             }
 180
 181             unset($GLOBALS['sql_constraints_query_full_db'], $one_query);
 182         }
 183
 184         if (PMA_MYSQL_INT_VERSION >= 50000) {
 185             // here I don't use DELIMITER because it's not part of the
 186             // language; I have to send each statement one by one
 187
 188             // to avoid selecting alternatively the current and new db
 189             // we would need to modify the CREATE definitions to qualify
 190             // the db name
 191             $procedure_names = PMA_DBI_get_procedures_or_functions($db, 'PROCEDURE');
 192             if ($procedure_names) {
 193                 foreach($procedure_names as $procedure_name) {
 194                     PMA_DBI_select_db($db);
 195                     $tmp_query = PMA_DBI_get_definition($db, 'PROCEDURE', $procedure_name);
 196                     // collect for later display
 197                     $GLOBALS['sql_query'] .= "\n" . $tmp_query;
 198                     PMA_DBI_select_db($newname);
 199                     PMA_DBI_query($tmp_query);
 200                 }
 201             }
 202
 203             $function_names = PMA_DBI_get_procedures_or_functions($db, 'FUNCTION');
 204             if ($function_names) {
 205                 foreach($function_names as $function_name) {
 206                     PMA_DBI_select_db($db);
 207                     $tmp_query = PMA_DBI_get_definition($db, 'FUNCTION', $function_name);
 208                     // collect for later display
 209                     $GLOBALS['sql_query'] .= "\n" . $tmp_query;
 210                     PMA_DBI_select_db($newname);
 211                     PMA_DBI_query($tmp_query);
 212                 }
 213             }
 214         }
 215         // go back to current db, just in case
 216         PMA_DBI_select_db($db);
 217
 218         // Duplicate the bookmarks for this db (done once for each db)
 219         if (! $_error && $db != $newname) {
 220             $get_fields = array('user', 'label', 'query');
 221             $where_fields = array('dbase' => $db);
 222             $new_fields = array('dbase' => $newname);
 223             PMA_Table::duplicateInfo('bookmarkwork', 'bookmark', $get_fields,
 224                 $where_fields, $new_fields);
 225         }
 226
 227         if (! $_error && $move) {
 228             /**
 229              * cleanup pmadb stuff for this db
 230              */
 231             require_once './libraries/relation_cleanup.lib.php';
 232             PMA_relationsCleanupDatabase($db);
 233
 234             // if someday the RENAME DATABASE reappears, do not DROP
 235             $local_query = 'DROP DATABASE ' . PMA_backquote($db) . ';';
 236             $sql_query .= "\n" . $local_query;
 237             PMA_DBI_query($local_query);
 238
 239             $message = PMA_Message::success(__('Database %s has been renamed to %s'));
 240             $message->addParam($db);
 241             $message->addParam($newname);
 242         } elseif (! $_error)  {
 243             $message = PMA_Message::success(__('Database %s has been copied to %s'));
 244             $message->addParam($db);
 245             $message->addParam($newname);
 246         }
 247         $reload     = true;
 248
 249         /* Change database to be used */
 250         if (! $_error && $move) {
 251             $db = $newname;
 252         } elseif (! $_error) {
 253             if (isset($switch_to_new) && $switch_to_new == 'true') {
 254                 $GLOBALS['PMA_Config']->setCookie('pma_switch_to_new', 'true');
 255                 $db = $newname;
 256             } else {
 257                 $GLOBALS['PMA_Config']->setCookie('pma_switch_to_new', '');
 258             }
 259         }
 260
 261         if ($_error && ! isset($message)) {
 262             $message = PMA_Message::error();
 263         }
 264     }
 265
 266     /**
 267      * Database has been successfully renamed/moved.  If in an Ajax request,
 268      * generate the output with {@link PMA_ajaxResponse} and exit
 269      */
 270     if( $GLOBALS['is_ajax_request'] == true) {
 271         $extra_data['newname'] = $newname;
 272         $extra_data['sql_query'] = PMA_showMessage(NULL, $sql_query);
 273         PMA_ajaxResponse($message, $message->isSuccess(), $extra_data);
 274     };
 275 }
 276
 277
 278 /**
 279  * Settings for relations stuff
 280  */
 281
 282 $cfgRelation = PMA_getRelationsParam();
 283
 284 /**
 285  * Check if comments were updated
 286  * (must be done before displaying the menu tabs)
 287  */
 288 if (isset($_REQUEST['comment'])) {
 289     PMA_setDbComment($db, $comment);
 290 }
 291
 292 /**
 293  * Prepares the tables list if the user where not redirected to this script
 294  * because there is no table in the database ($is_info is true)
 295  */
 296 if (empty($is_info)) {
 297     require './libraries/db_common.inc.php';
 298     $url_query .= '&amp;goto=db_operations.php';
 299
 300     // Gets the database structure
 301     $sub_part = '_structure';
 302     require './libraries/db_info.inc.php';
 303     echo "\n";
 304
 305     if (isset($message)) {
 306         PMA_showMessage($message, $sql_query);
 307         unset($message);
 308     }
 309 }
 310
 311 $db_collation = PMA_getDbCollation($db);
 312 if ($db == 'information_schema') {
 313     $is_information_schema = true;
 314 } else {
 315     $is_information_schema = false;
 316 }
 317
 318 if (!$is_information_schema) {
 319
 320     require './libraries/display_create_table.lib.php';
 321
 322     if ($cfgRelation['commwork']) {
 323         /**
 324          * database comment
 325          */
 326         ?>
 327     <form method="post" action="db_operations.php">
 328     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 329     <fieldset>
 330         <legend>
 331         <?php echo PMA_getIcon('b_comment.png', __('Database comment: '), false, true); ?>
 332         </legend>
 333         <input type="text" name="comment" class="textfield" size="30"
 334             value="<?php
 335             echo htmlspecialchars(PMA_getDBComment($db)); ?>" />
 336     </fieldset>
 337     <fieldset class="tblFooters">
 338         <input type="submit" value="<?php echo __('Go'); ?>" />
 339     </fieldset>
 340     </form>
 341         <?php
 342     }
 343     /**
 344      * rename database
 345      */
 346     ?>
 347     <form id="rename_db_form" method="post" action="db_operations.php"
 348         onsubmit="return emptyFormElements(this, 'newname')">
 349         <?php
 350     if (isset($db_collation)) {
 351         echo '<input type="hidden" name="db_collation" value="' . $db_collation
 352             .'" />' . "\n";
 353     }
 354         ?>
 355     <input type="hidden" name="what" value="data" />
 356     <input type="hidden" name="db_rename" value="true" />
 357     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 358     <fieldset>
 359         <legend>
 360     <?php
 361     if ($cfg['PropertiesIconic']) {
 362         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 363             .' alt="" width="16" height="16" />';
 364     }
 365     echo __('Rename database to') . ':';
 366     ?>
 367         </legend>
 368         <input type="text" name="newname" size="30" class="textfield" value="" />
 369         <?php
 370     echo '(' . __('Command') . ': ';
 371     /**
 372      * @todo (see explanations above in a previous todo)
 373      */
 374     //if (PMA_MYSQL_INT_VERSION >= XYYZZ) {
 375     //    echo 'RENAME DATABASE';
 376     //} else {
 377         echo 'INSERT INTO ... SELECT';
 378     //}
 379     echo ')'; ?>
 380     </fieldset>
 381     <fieldset class="tblFooters">
 382         <input id="rename_db_input" type="submit" value="<?php echo __('Go'); ?>" />
 383     </fieldset>
 384     </form>
 385 <?php
 386 // Drop link if allowed
 387 // Don't even try to drop information_schema. You won't be able to. Believe me. You won't.
 388 // Don't allow to easilly drop mysql database, RFE #1327514.
 389 if (($is_superuser || $GLOBALS['cfg']['AllowUserDropDatabase']) && ! $db_is_information_schema && ($db != 'mysql')) {
 390 ?>
 391 <fieldset class="caution">
 392  <legend><?php
 393 if ($cfg['PropertiesIconic']) {
 394     echo '<img class="icon" src="' . $pmaThemeImage . 'b_deltbl.png"'
 395         .' alt="" width="16" height="16" />';
 396 }
 397 echo __('Remove database');
 398 ?></legend>
 399
 400 <ul>
 401 <?php
 402     $this_sql_query = 'DROP DATABASE ' . PMA_backquote($GLOBALS['db']);
 403     $this_url_params = array(
 404             'sql_query' => $this_sql_query,
 405             'back' => 'db_operations.php',
 406             'goto' => 'main.php',
 407             'reload' => '1',
 408             'purge' => '1',
 409             'zero_rows' => sprintf(__('Database %s has been dropped.'), htmlspecialchars(PMA_backquote($db))),
 410             'db' => NULL,
 411         );
 412     ?>
 413     <li><a href="sql.php<?php echo PMA_generate_common_url($this_url_params); ?>" onclick="return confirmLinkDropDB(this, '<?php echo PMA_jsFormat($this_sql_query); ?>')">
 414             <?php echo __('Drop the database (DROP)'); ?></a>
 415         <?php echo PMA_showMySQLDocu('SQL-Syntax', 'DROP_DATABASE'); ?>
 416     </li>
 417 </ul>
 418 </fieldset>
 419 <?php } ?>
 420     <?php
 421     /**
 422      * Copy database
 423      */
 424     ?>
 425     <form id="copy_db_form" method="post" action="db_operations.php"
 426         onsubmit="return emptyFormElements(this, 'newname')">
 427     <?php
 428     if (isset($db_collation)) {
 429         echo '<input type="hidden" name="db_collation" value="' . $db_collation
 430             .'" />' . "\n";
 431     }
 432     echo '<input type="hidden" name="db_copy" value="true" />' . "\n";
 433     echo PMA_generate_common_hidden_inputs($db);
 434     ?>
 435     <fieldset>
 436         <legend>
 437     <?php
 438     if ($cfg['PropertiesIconic']) {
 439         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 440             .' alt="" width="16" height="16" />';
 441     }
 442     echo __('Copy database to') . ':';
 443     $drop_clause = 'DROP TABLE / DROP VIEW';
 444     ?>
 445         </legend>
 446         <input type="text" name="newname" size="30" class="textfield" value="" /><br />
 447 <?php
 448         $choices = array(
 449             'structure' => __('Structure only'),
 450             'data'      => __('Structure and data'),
 451             'dataonly'  => __('Data only'));
 452         PMA_display_html_radio('what', $choices, 'data', true);
 453         unset($choices);
 454 ?>
 455         <input type="checkbox" name="create_database_before_copying" value="1"
 456             id="checkbox_create_database_before_copying"
 457             checked="checked" />
 458         <label for="checkbox_create_database_before_copying">
 459             <?php echo __('CREATE DATABASE before copying'); ?></label><br />
 460         <input type="checkbox" name="drop_if_exists" value="true"
 461             id="checkbox_drop" />
 462         <label for="checkbox_drop"><?php echo sprintf(__('Add %s'), $drop_clause); ?></label><br />
 463         <input type="checkbox" name="sql_auto_increment" value="1" checked="checked"
 464             id="checkbox_auto_increment" />
 465         <label for="checkbox_auto_increment">
 466             <?php echo __('Add AUTO_INCREMENT value'); ?></label><br />
 467         <input type="checkbox" name="add_constraints" value="1"
 468             id="checkbox_constraints" />
 469         <label for="checkbox_constraints">
 470             <?php echo __('Add constraints'); ?></label><br />
 471     <?php
 472     unset($drop_clause);
 473
 474     if (isset($_COOKIE) && isset($_COOKIE['pma_switch_to_new'])
 475       && $_COOKIE['pma_switch_to_new'] == 'true') {
 476         $pma_switch_to_new = 'true';
 477     }
 478     ?>
 479         <input type="checkbox" name="switch_to_new" value="true"
 480             id="checkbox_switch"
 481             <?php echo ((isset($pma_switch_to_new) && $pma_switch_to_new == 'true') ? ' checked="checked"' : ''); ?>
 482             />
 483         <label for="checkbox_switch"><?php echo __('Switch to copied database'); ?></label>
 484     </fieldset>
 485     <fieldset class="tblFooters">
 486         <input type="submit" name="submit_copy" value="<?php echo __('Go'); ?>" />
 487     </fieldset>
 488     </form>
 489
 490     <?php
 491
 492     /**
 493      * Change database charset
 494      */
 495     echo '<form id="change_db_charset_form" method="post" action="./db_operations.php">' . "\n"
 496        . PMA_generate_common_hidden_inputs($db, $table)
 497        . '<fieldset>' . "\n"
 498        . '    <legend>';
 499     if ($cfg['PropertiesIconic']) {
 500         echo '<img class="icon" src="' . $pmaThemeImage . 's_asci.png"'
 501             .' alt="" width="16" height="16" />';
 502     }
 503     echo '    <label for="select_db_collation">' . __('Collation') . ':</label>' . "\n"
 504        . '    </legend>' . "\n"
 505        . PMA_generateCharsetDropdownBox(PMA_CSDROPDOWN_COLLATION,
 506             'db_collation', 'select_db_collation', $db_collation, false, 3)
 507        . '</fieldset>'
 508        . '<fieldset class="tblFooters">'
 509        . '    <input type="submit" name="submitcollation"'
 510        . ' value="' . __('Go') . '" />' . "\n"
 511        . '</fieldset>' . "\n"
 512        . '</form>' . "\n";
 513
 514     if ($num_tables > 0
 515       && !$cfgRelation['allworks'] && $cfg['PmaNoRelation_DisableWarning'] == false) {
 516         $message = PMA_Message::notice(__('The phpMyAdmin configuration storage has been deactivated. To find out why click %shere%s.'));
 517         $message->addParam('<a href="' . $cfg['PmaAbsoluteUri'] . 'chk_rel.php?' . $url_query . '">', false);
 518         $message->addParam('</a>', false);
 519         /* Show error if user has configured something, notice elsewhere */
 520         if (!empty($cfg['Servers'][$server]['pmadb'])) {
 521             $message->isError(true);
 522         }
 523         $message->display();
 524     } // end if
 525 } // end if (!$is_information_schema)
 526
 527
 528 // not sure about displaying the PDF dialog in case db is information_schema
 529 if ($cfgRelation['pdfwork'] && $num_tables > 0) { ?>
 530     <!-- Work on PDF Pages -->
 531
 532     <?php
 533     // We only show this if we find something in the new pdf_pages table
 534
 535     $test_query = '
 536          SELECT *
 537            FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['pdf_pages']) . '
 538           WHERE db_name = \'' . PMA_sqlAddslashes($db) . '\'';
 539     $test_rs    = PMA_query_as_controluser($test_query, null, PMA_DBI_QUERY_STORE);
 540
 541     /*
 542      * Export Relational Schema View
 543      */
 544     echo '<fieldset><a href="schema_edit.php?' . $url_query . '">';
 545     if ($cfg['PropertiesIconic']) {
 546         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 547             .' alt="" width="16" height="16" />';
 548     }
 549     echo __('Edit or export relational schema') . '</a></fieldset>';
 550 } // end if
 551
 552 /**
 553  * Displays the footer
 554  */
 555 require './libraries/footer.inc.php';
 556 ?>