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