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  * @version $Id$
  13  */
  14
  15 /**
  16  * requirements
  17  */
  18 require_once './libraries/common.inc.php';
  19 require_once './libraries/Table.class.php';
  20 require_once './libraries/mysql_charsets.lib.php';
  21
  22 /**
  23  * Rename/move or copy database
  24  */
  25 if (strlen($db) && (! empty($db_rename) || ! empty($db_copy))) {
  26
  27     if (! empty($db_rename)) {
  28         $move = true;
  29     } else {
  30         $move = false;
  31     }
  32
  33     if (!isset($newname) || !strlen($newname)) {
  34         $message = PMA_Message::error('strDatabaseEmpty');
  35     } else {
  36         $sql_query = ''; // in case target db exists
  37         $_error = false;
  38         if ($move ||
  39          (isset($create_database_before_copying) && $create_database_before_copying)) {
  40             /**
  41              * @todo activate this with the correct version of MySQL
  42              *       when they fix the problem when the db contains a VIEW
  43              *       (problem exists in 5.1.20)
  44              *       also, in 6.0.0 when the db contains a Falcon table,
  45              *       renaming it results in a unusable db!
  46              */
  47             //if (PMA_MYSQL_INT_VERSION >= 50107) {
  48             //    $local_query = 'RENAME DATABASE ' . PMA_backquote($db) . ' TO ' . PMA_backquote($newname) . ';';
  49             //    $sql_query = $local_query;
  50             //    PMA_DBI_query($local_query);
  51             //} else {
  52             // please indent ->
  53
  54             // lower_case_table_names=1 `DB` becomes `db`
  55             $lower_case_table_names = PMA_DBI_fetch_value('SHOW VARIABLES LIKE "lower_case_table_names"', 0, 1);
  56             if ($lower_case_table_names === '1') {
  57                 $newname = strtolower($newname);
  58             }
  59
  60             $local_query = 'CREATE DATABASE ' . PMA_backquote($newname);
  61             if (isset($db_collation)) {
  62                 $local_query .= ' DEFAULT' . PMA_generateCharsetQueryPart($db_collation);
  63             }
  64             $local_query .= ';';
  65             $sql_query = $local_query;
  66             PMA_DBI_query($local_query);
  67
  68             // rebuild the database list because PMA_Table::moveCopy
  69             // checks in this list if the target db exists
  70             $GLOBALS['PMA_List_Database']->build();
  71         }
  72
  73         if (isset($GLOBALS['add_constraints'])) {
  74             $GLOBALS['sql_constraints_query_full_db'] = '';
  75         }
  76
  77         $tables_full = PMA_DBI_get_tables_full($db);
  78         $views = array();
  79         foreach ($tables_full as $each_table => $tmp) {
  80             // to be able to rename a db containing views, we
  81             // first collect in $views all the views we find and we
  82             // will handle them after the tables
  83             /**
  84              * @todo support a view of a view
  85              */
  86             if (PMA_Table::isView($db, $each_table)) {
  87                 $views[] = $each_table;
  88                 continue;
  89             }
  90
  91             $back = $sql_query;
  92             $sql_query = '';
  93
  94             // value of $what for this table only
  95             $this_what = $what;
  96
  97             // do not copy the data from a Merge table
  98             // note: on the calling FORM, 'data' means 'structure and data'
  99             if ($tables_full[$each_table]['Engine'] == 'MRG_MyISAM') {
 100                 if ($this_what == 'data') {
 101                     $this_what = 'structure';
 102                 }
 103                 if ($this_what == 'dataonly') {
 104                     $this_what = 'nocopy';
 105                 }
 106             }
 107
 108             if ($this_what != 'nocopy') {
 109                 if (! PMA_Table::moveCopy($db, $each_table, $newname, $each_table,
 110                     isset($this_what) ? $this_what : 'data', $move, 'db_copy'))
 111                 {
 112                     $_error = true;
 113                     // $sql_query is filled by PMA_Table::moveCopy()
 114                     $sql_query = $back . $sql_query;
 115                     break;
 116                 }
 117                 if (isset($GLOBALS['add_constraints'])) {
 118                     $GLOBALS['sql_constraints_query_full_db'] .= $GLOBALS['sql_constraints_query'];
 119                     unset($GLOBALS['sql_constraints_query']);
 120                 }
 121             }
 122             // $sql_query is filled by PMA_Table::moveCopy()
 123             $sql_query = $back . $sql_query;
 124         } // end (foreach)
 125         unset($each_table);
 126
 127         // handle the views
 128         if (! $_error) {
 129             foreach ($views as $view) {
 130                 if (! PMA_Table::moveCopy($db, $view, $newname, $view,
 131                  'structure', $move, 'db_copy')) {
 132                     $_error = true;
 133                     break;
 134                 }
 135             }
 136         }
 137         unset($view, $views);
 138
 139         // now that all tables exist, create all the accumulated constraints
 140         if (! $_error && isset($GLOBALS['add_constraints'])) {
 141             /**
 142              * @todo this works with mysqli but not with mysql, because
 143              * mysql extension does not accept more than one statement; maybe
 144              * interface with the sql import plugin that handles statement delimiter
 145              */
 146             PMA_DBI_query($GLOBALS['sql_constraints_query_full_db']);
 147
 148             // and prepare to display them
 149             $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query_full_db'];
 150             unset($GLOBALS['sql_constraints_query_full_db']);
 151         }
 152 // see the previous todo
 153 //        } // end else MySQL < 50107
 154
 155         // Duplicate the bookmarks for this db (done once for each db)
 156         if (! $_error && $db != $newname) {
 157             $get_fields = array('user', 'label', 'query');
 158             $where_fields = array('dbase' => $db);
 159             $new_fields = array('dbase' => $newname);
 160             PMA_Table::duplicateInfo('bookmarkwork', 'bookmark', $get_fields,
 161                 $where_fields, $new_fields);
 162         }
 163
 164         if (! $_error && $move) {
 165             // cleanup pmadb stuff for this db
 166             require_once './libraries/relation_cleanup.lib.php';
 167             PMA_relationsCleanupDatabase($db);
 168
 169             if (PMA_MYSQL_INT_VERSION <  50107) {
 170                 $local_query = 'DROP DATABASE ' . PMA_backquote($db) . ';';
 171                 $sql_query .= "\n" . $local_query;
 172                 PMA_DBI_query($local_query);
 173             }
 174             $message = PMA_Message::success('strRenameDatabaseOK');
 175             $message->addParam($db);
 176             $message->addParam($newname);
 177         } elseif (! $_error)  {
 178             $message = PMA_Message::success('strCopyDatabaseOK');
 179             $message->addParam($db);
 180             $message->addParam($newname);
 181         }
 182         $reload     = true;
 183
 184         /* Change database to be used */
 185         if (! $_error && $move) {
 186             $db = $newname;
 187         } elseif (! $_error) {
 188             if (isset($switch_to_new) && $switch_to_new == 'true') {
 189                 PMA_setCookie('pma_switch_to_new', 'true');
 190                 $db = $newname;
 191             } else {
 192                 PMA_setCookie('pma_switch_to_new', '');
 193             }
 194         }
 195
 196         if ($_error && ! isset($message)) {
 197             $message = PMA_Message::error();
 198         }
 199     }
 200 }
 201 /**
 202  * Settings for relations stuff
 203  */
 204
 205 require_once './libraries/relation.lib.php';
 206 $cfgRelation = PMA_getRelationsParam();
 207
 208 /**
 209  * Check if comments were updated
 210  * (must be done before displaying the menu tabs)
 211  */
 212 if (isset($_REQUEST['comment'])) {
 213     PMA_setDbComment($db, $comment);
 214 }
 215
 216 /**
 217  * Prepares the tables list if the user where not redirected to this script
 218  * because there is no table in the database ($is_info is true)
 219  */
 220 if (empty($is_info)) {
 221     require './libraries/db_common.inc.php';
 222     $url_query .= '&amp;goto=db_operations.php';
 223
 224     // Gets the database structure
 225     $sub_part = '_structure';
 226     require './libraries/db_info.inc.php';
 227     echo "\n";
 228
 229     if (isset($message)) {
 230         PMA_showMessage($message, $sql_query);
 231         unset($message);
 232     }
 233 }
 234
 235 $db_collation = PMA_getDbCollation($db);
 236 if ($db == 'information_schema') {
 237     $is_information_schema = true;
 238 } else {
 239     $is_information_schema = false;
 240 }
 241
 242 if (!$is_information_schema) {
 243
 244     require './libraries/display_create_table.lib.php';
 245
 246     if ($cfgRelation['commwork']) {
 247         /**
 248          * database comment
 249          */
 250         ?>
 251     <form method="post" action="db_operations.php">
 252     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 253     <fieldset>
 254         <legend>
 255         <?php echo PMA_getIcon('b_comment.png', $strDBComment, false, true); ?>
 256         </legend>
 257         <input type="text" name="comment" class="textfield" size="30"
 258             value="<?php
 259             echo htmlspecialchars(PMA_getDbComment($db)); ?>" />
 260         <input type="submit" value="<?php echo $strGo; ?>" />
 261     </fieldset>
 262     </form>
 263         <?php
 264     }
 265     /**
 266      * rename database
 267      */
 268     ?>
 269     <form method="post" action="db_operations.php"
 270         onsubmit="return emptyFormElements(this, 'newname')">
 271         <?php
 272     if (isset($db_collation)) {
 273         echo '<input type="hidden" name="db_collation" value="' . $db_collation
 274             .'" />' . "\n";
 275     }
 276         ?>
 277     <input type="hidden" name="what" value="data" />
 278     <input type="hidden" name="db_rename" value="true" />
 279     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 280     <fieldset>
 281         <legend>
 282     <?php
 283     if ($cfg['PropertiesIconic']) {
 284         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 285             .' alt="" width="16" height="16" />';
 286     }
 287     echo $strDBRename . ':';
 288     ?>
 289         </legend>
 290         <input type="text" name="newname" size="30" class="textfield" value="" />
 291         <?php
 292     echo '(' . $strCommand . ': ';
 293     /**
 294      * @todo (see explanations above in a previous todo)
 295      */
 296     //if (PMA_MYSQL_INT_VERSION >= 50107) {
 297     //    echo 'RENAME DATABASE';
 298     //} else {
 299         echo 'INSERT INTO ... SELECT';
 300     //}
 301     echo ')'; ?>
 302         <input type="submit" value="<?php echo $strGo; ?>" />
 303     </fieldset>
 304     </form>
 305
 306     <?php
 307     /**
 308      * Copy database
 309      */
 310     ?>
 311     <form method="post" action="db_operations.php"
 312         onsubmit="return emptyFormElements(this, 'newname')">
 313     <?php
 314     if (isset($db_collation)) {
 315         echo '<input type="hidden" name="db_collation" value="' . $db_collation
 316             .'" />' . "\n";
 317     }
 318     echo '<input type="hidden" name="db_copy" value="true" />' . "\n";
 319     echo PMA_generate_common_hidden_inputs($db);
 320     ?>
 321     <fieldset>
 322         <legend>
 323     <?php
 324     if ($cfg['PropertiesIconic']) {
 325         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 326             .' alt="" width="16" height="16" />';
 327     }
 328     echo $strDBCopy . ':';
 329     $drop_clause = 'DROP TABLE / DROP VIEW';
 330     ?>
 331         </legend>
 332         <input type="text" name="newname" size="30" class="textfield" value="" /><br />
 333         <input type="radio" name="what" value="structure"
 334             id="radio_copy_structure" style="vertical-align: middle" />
 335         <label for="radio_copy_structure"><?php echo $strStrucOnly; ?></label><br />
 336         <input type="radio" name="what" value="data" id="radio_copy_data"
 337             checked="checked" style="vertical-align: middle" />
 338         <label for="radio_copy_data"><?php echo $strStrucData; ?></label><br />
 339         <input type="radio" name="what" value="dataonly"
 340             id="radio_copy_dataonly" style="vertical-align: middle" />
 341         <label for="radio_copy_dataonly"><?php echo $strDataOnly; ?></label><br />
 342
 343         <input type="checkbox" name="create_database_before_copying" value="1"
 344             id="checkbox_create_database_before_copying"
 345             style="vertical-align: middle" checked="checked" />
 346         <label for="checkbox_create_database_before_copying">
 347             <?php echo $strCreateDatabaseBeforeCopying; ?></label><br />
 348         <input type="checkbox" name="drop_if_exists" value="true"
 349             id="checkbox_drop" style="vertical-align: middle" />
 350         <label for="checkbox_drop"><?php echo sprintf($strAddClause, $drop_clause); ?></label><br />
 351         <input type="checkbox" name="sql_auto_increment" value="1"
 352             id="checkbox_auto_increment" style="vertical-align: middle" />
 353         <label for="checkbox_auto_increment">
 354             <?php echo $strAddAutoIncrement; ?></label><br />
 355         <input type="checkbox" name="add_constraints" value="1"
 356             id="checkbox_constraints" style="vertical-align: middle" />
 357         <label for="checkbox_constraints">
 358             <?php echo $strAddConstraints; ?></label><br />
 359     <?php
 360     unset($drop_clause);
 361
 362     if (isset($_COOKIE) && isset($_COOKIE['pma_switch_to_new'])
 363       && $_COOKIE['pma_switch_to_new'] == 'true') {
 364         $pma_switch_to_new = 'true';
 365     }
 366     ?>
 367         <input type="checkbox" name="switch_to_new" value="true"
 368             id="checkbox_switch"
 369             <?php echo ((isset($pma_switch_to_new) && $pma_switch_to_new == 'true') ? ' checked="checked"' : ''); ?>
 370             style="vertical-align: middle" />
 371         <label for="checkbox_switch"><?php echo $strSwitchToDatabase; ?></label>
 372     </fieldset>
 373     <fieldset class="tblFooters">
 374         <input type="submit" name="submit_copy" value="<?php echo $strGo; ?>" />
 375     </fieldset>
 376     </form>
 377
 378     <?php
 379     /**
 380      * Change database charset
 381      */
 382     echo '<form method="post" action="./db_operations.php">' . "\n"
 383        . PMA_generate_common_hidden_inputs($db, $table)
 384        . '<fieldset>' . "\n"
 385        . '    <legend>';
 386     if ($cfg['PropertiesIconic']) {
 387         echo '<img class="icon" src="' . $pmaThemeImage . 's_asci.png"'
 388             .' alt="" width="16" height="16" />';
 389     }
 390     echo '    <label for="select_db_collation">' . $strCollation . ':</label>' . "\n"
 391        . '    </legend>' . "\n"
 392        . PMA_generateCharsetDropdownBox(PMA_CSDROPDOWN_COLLATION,
 393             'db_collation', 'select_db_collation', $db_collation, false, 3)
 394        . '    <input type="submit" name="submitcollation"'
 395        . ' value="' . $strGo . '" style="vertical-align: middle" />' . "\n"
 396        . '</fieldset>' . "\n"
 397        . '</form>' . "\n";
 398
 399     if ($num_tables > 0
 400       && !$cfgRelation['allworks'] && $cfg['PmaNoRelation_DisableWarning'] == false) {
 401         $message = PMA_Message::notice('strRelationNotWorking');
 402         $message->addParam('<a href="' . $cfg['PmaAbsoluteUri'] . 'chk_rel.php?' . $url_query . '">', false);
 403         $message->addParam('</a>', false);
 404         /* Show error if user has configured something, notice elsewhere */
 405         if (!empty($cfg['Servers'][$server]['pmadb'])) {
 406             $message->isError(true);
 407         }
 408         $message->display();
 409     } // end if
 410 } // end if (!$is_information_schema)
 411
 412
 413 // not sure about displaying the PDF dialog in case db is information_schema
 414 if ($cfgRelation['pdfwork'] && $num_tables > 0) { ?>
 415     <!-- Work on PDF Pages -->
 416
 417     <?php
 418     // We only show this if we find something in the new pdf_pages table
 419
 420     $test_query = '
 421          SELECT *
 422            FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['pdf_pages']) . '
 423           WHERE db_name = \'' . PMA_sqlAddslashes($db) . '\'';
 424     $test_rs    = PMA_query_as_cu($test_query, null, PMA_DBI_QUERY_STORE);
 425
 426     if ($test_rs && PMA_DBI_num_rows($test_rs) > 0) { ?>
 427     <!-- PDF schema -->
 428     <form method="post" action="pdf_schema.php">
 429     <fieldset>
 430         <legend>
 431         <?php
 432         echo PMA_generate_common_hidden_inputs($db);
 433         if ($cfg['PropertiesIconic']) {
 434             echo '<img class="icon" src="' . $pmaThemeImage . 'b_view.png"'
 435                 .' alt="" width="16" height="16" />';
 436         }
 437         echo $strDisplayPDF;
 438         ?>:
 439         </legend>
 440         <label for="pdf_page_number_opt"><?php echo $strPageNumber; ?></label>
 441         <select name="pdf_page_number" id="pdf_page_number_opt">
 442         <?php
 443         while ($pages = @PMA_DBI_fetch_assoc($test_rs)) {
 444             echo '                <option value="' . $pages['page_nr'] . '">'
 445                 . $pages['page_nr'] . ': ' . $pages['page_descr'] . '</option>' . "\n";
 446         } // end while
 447         PMA_DBI_free_result($test_rs);
 448         unset($test_rs);
 449         ?>
 450         </select><br />
 451
 452         <input type="checkbox" name="show_grid" id="show_grid_opt" />
 453         <label for="show_grid_opt"><?php echo $strShowGrid; ?></label><br />
 454         <input type="checkbox" name="show_color" id="show_color_opt"
 455             checked="checked" />
 456         <label for="show_color_opt"><?php echo $strShowColor; ?></label><br />
 457         <input type="checkbox" name="show_table_dimension" id="show_table_dim_opt" />
 458         <label for="show_table_dim_opt"><?php echo $strShowTableDimension; ?>
 459             </label><br />
 460         <input type="checkbox" name="all_tab_same_wide" id="all_tab_same_wide" />
 461         <label for="all_tab_same_wide"><?php echo $strAllTableSameWidth; ?>
 462             </label><br />
 463         <input type="checkbox" name="with_doc" id="with_doc" checked="checked" />
 464         <label for="with_doc"><?php echo $strDataDict; ?></label><br />
 465
 466         <label for="orientation_opt"><?php echo $strShowDatadictAs; ?></label>
 467         <select name="orientation" id="orientation_opt">
 468             <option value="L"><?php echo $strLandscape;?></option>
 469             <option value="P"><?php echo $strPortrait;?></option>
 470         </select><br />
 471
 472         <label for="paper_opt"><?php echo $strPaperSize; ?></label>
 473         <select name="paper" id="paper_opt">
 474         <?php
 475             foreach ($cfg['PDFPageSizes'] AS $key => $val) {
 476                 echo '<option value="' . $val . '"';
 477                 if ($val == $cfg['PDFDefaultPageSize']) {
 478                     echo ' selected="selected"';
 479                 }
 480                 echo ' >' . $val . '</option>' . "\n";
 481             }
 482         ?>
 483         </select>
 484     </fieldset>
 485     <fieldset class="tblFooters">
 486         <input type="submit" value="<?php echo $strGo; ?>" />
 487     </fieldset>
 488     </form>
 489         <?php
 490     }   // end if
 491     echo '<br /><a href="pdf_pages.php?' . $url_query . '">';
 492     if ($cfg['PropertiesIconic']) {
 493         echo '<img class="icon" src="' . $pmaThemeImage . 'b_edit.png"'
 494             .' alt="" width="16" height="16" />';
 495     }
 496     echo $strEditPDFPages . '</a>';
 497 } // end if
 498
 499 /**
 500  * Displays the footer
 501  */
 502 require_once './libraries/footer.inc.php';
 503 ?>