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