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