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