interface/main/myadmin/tbl_move_copy.php

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5 // Check parameters
   6
   7 require_once('./libraries/grab_globals.lib.php');
   8 require_once('./libraries/common.lib.php');
   9
  10 PMA_checkParameters(array('db', 'table'));
  11
  12 /**
  13  * Insert data from one table to another one
  14  *
  15  * @param   string  the original insert statement
  16  *
  17  * @global  string  the database name
  18  * @global  string  the original table name
  19  * @global  string  the target database and table names
  20  * @global  string  the sql query used to copy the data
  21  */
  22 function PMA_myHandler($sql_insert = '')
  23 {
  24     global $db, $table, $target;
  25     global $sql_insert_data;
  26
  27     $sql_insert = preg_replace('~INSERT INTO (`?)' . $table . '(`?)~i', 'INSERT INTO ' . $target, $sql_insert);
  28     $result     = PMA_mysql_query($sql_insert) or PMA_mysqlDie('', $sql_insert, '', $GLOBALS['err_url']);
  29
  30     $sql_insert_data .= $sql_insert . ';' . "\n";
  31 } // end of the 'PMA_myHandler()' function
  32
  33 /**
  34  * Inserts existing entries in a PMA_* table by reading a value from an old entry
  35  *
  36  * @param   string  The array index, which Relation feature to check
  37  *                  ('relwork', 'commwork', ...)
  38  * @param   string  The array index, which PMA-table to update
  39  *                  ('bookmark', 'relation', ...)
  40  * @param   array   Which fields will be SELECT'ed from the old entry
  41  * @param   array   Which fields will be used for the WHERE query
  42  *                  (array('FIELDNAME' => 'FIELDVALUE'))
  43  * @param   array   Which fields will be used as new VALUES. These are the important
  44  *                  keys which differ from the old entry.
  45  *                  (array('FIELDNAME' => 'NEW FIELDVALUE'))
  46
  47  * @global  string  relation variable
  48  *
  49  * @author          Garvin Hicking <me@supergarv.de>
  50  */
  51 function PMA_duplicate_table($work, $pma_table, $get_fields, $where_fields, $new_fields) {
  52 global $cfgRelation;
  53
  54     $last_id = -1;
  55
  56     if ($cfgRelation[$work]) {
  57         $select_parts = array();
  58         $row_fields = array();
  59         foreach($get_fields AS $nr => $get_field) {
  60             $select_parts[] = PMA_backquote($get_field);
  61             $row_fields[$get_field] = 'cc';
  62         }
  63
  64         $where_parts = array();
  65         foreach($where_fields AS $_where => $_value) {
  66             $where_parts[] = PMA_backquote($_where) . ' = \'' . PMA_sqlAddslashes($_value) . '\'';
  67         }
  68
  69         $new_parts = array();
  70         $new_value_parts = array();
  71         foreach($new_fields AS $_where => $_value) {
  72             $new_parts[] = PMA_backquote($_where);
  73             $new_value_parts[] = PMA_sqlAddslashes($_value);
  74         }
  75
  76         $table_copy_query = 'SELECT ' . implode(', ', $select_parts)
  77                           . ' FROM ' . PMA_backquote($cfgRelation[$pma_table])
  78                           . ' WHERE ' . implode(' AND ', $where_parts);
  79         $table_copy_rs    = PMA_query_as_cu($table_copy_query);
  80
  81         while ($table_copy_row = @PMA_mysql_fetch_array($table_copy_rs)) {
  82             $value_parts = array();
  83             foreach($table_copy_row AS $_key => $_val) {
  84                 if (isset($row_fields[$_key]) && $row_fields[$_key] == 'cc') {
  85                     $value_parts[] = PMA_sqlAddslashes($_val);
  86                 }
  87             }
  88
  89             $new_table_query = 'INSERT IGNORE INTO ' . PMA_backquote($cfgRelation[$pma_table])
  90                             . ' (' . implode(', ', $select_parts) . ', ' . implode(', ', $new_parts) . ')'
  91                             . ' VALUES '
  92                             . ' (\'' . implode('\', \'', $value_parts) . '\', \'' . implode('\', \'', $new_value_parts) . '\')';
  93
  94             $new_table_rs    = PMA_query_as_cu($new_table_query);
  95             $last_id = (@function_exists('mysql_insert_id') ? @mysql_insert_id() : -1);
  96         } // end while
  97
  98         return $last_id;
  99     }
 100
 101     return true;
 102 } // end of 'PMA_duplicate_table()' function
 103
 104 /**
 105  * Gets some core libraries
 106  */
 107 require_once('./libraries/grab_globals.lib.php');
 108 require_once('./libraries/common.lib.php');
 109
 110
 111 /**
 112  * Defines the url to return to in case of error in a sql statement
 113  */
 114 $err_url = 'tbl_properties.php?' . PMA_generate_common_url($db, $table);
 115
 116
 117 /**
 118  * Selects the database to work with
 119  */
 120 PMA_mysql_select_db($db);
 121
 122
 123 /**
 124  * A target table name has been sent to this script -> do the work
 125  */
 126 if (isset($new_name) && trim($new_name) != '') {
 127     $use_backquotes = 1;
 128     $asfile         = 1;
 129
 130     // Ensure the target is valid
 131     if (count($dblist) > 0 &&
 132         (PMA_isInto($db, $dblist) == -1 || PMA_isInto($target_db, $dblist) == -1)) {
 133         exit();
 134     }
 135
 136     if ($db == $target_db && $new_name == $table) {
 137         $message   = (isset($submit_move) ? $strMoveTableSameNames : $strCopyTableSameNames);
 138     } else {
 139         $source = PMA_backquote($db) . '.' . PMA_backquote($table);
 140         if (empty($target_db)) $target_db = $db;
 141
 142         // This could avoid some problems with replicated databases, when
 143         // moving table from replicated one to not replicated one
 144         PMA_mysql_select_db($target_db);
 145
 146         $target = PMA_backquote($target_db) . '.' . PMA_backquote($new_name);
 147
 148         // do not create the table if dataonly
 149         if ($what != 'dataonly') {
 150             require('./libraries/export/sql.php');
 151
 152             $no_constraints_comments = true;
 153             $sql_structure = PMA_getTableDef($db, $table, "\n", $err_url);
 154             unset($no_constraints_comments);
 155
 156             $parsed_sql =  PMA_SQP_parse($sql_structure);
 157
 158             /* nijel: Find table name in query and replace it */
 159             $i = 0;
 160             while ($parsed_sql[$i]['type'] != 'quote_backtick') $i++;
 161
 162             /* no need to PMA_backquote() */
 163             $parsed_sql[$i]['data'] = $target;
 164
 165             /* Generate query back */
 166             $sql_structure = PMA_SQP_formatHtml($parsed_sql, 'query_only');
 167
 168             // If table exists, and 'add drop table' is selected: Drop it!
 169             $drop_query = '';
 170             if (isset($drop_if_exists) && $drop_if_exists == 'true') {
 171                 $drop_query = 'DROP TABLE IF EXISTS ' . PMA_backquote($target_db) . '.' . PMA_backquote($new_name);
 172                 $result        = @PMA_mysql_query($drop_query);
 173                 if (PMA_mysql_error()) {
 174                     require_once('./header.inc.php');
 175                     PMA_mysqlDie('', $sql_structure, '', $err_url);
 176                 }
 177
 178                 if (isset($sql_query)) {
 179                     $sql_query .= "\n" . $drop_query . ';';
 180                 } else {
 181                     $sql_query = $drop_query . ';';
 182                 }
 183
 184                 // garvin: If an existing table gets deleted, maintain any entries
 185                 // for the PMA_* tables
 186                 $maintain_relations = true;
 187             }
 188
 189             $result        = @PMA_mysql_query($sql_structure);
 190             if (PMA_mysql_error()) {
 191                 require_once('./header.inc.php');
 192                 PMA_mysqlDie('', $sql_structure, '', $err_url);
 193             } else if (isset($sql_query)) {
 194                 $sql_query .= "\n" . $sql_structure . ';';
 195             } else {
 196                 $sql_query = $sql_structure . ';';
 197             }
 198
 199             if ((isset($submit_move) || isset($constraints)) && isset($sql_constraints)) {
 200                 $parsed_sql =  PMA_SQP_parse($sql_constraints);
 201
 202                 $i = 0;
 203                 while ($parsed_sql[$i]['type'] != 'quote_backtick') $i++;
 204
 205                 /* no need to PMA_backquote() */
 206                 $parsed_sql[$i]['data'] = $target;
 207
 208                 /* Generate query back */
 209                 $sql_constraints = PMA_SQP_formatHtml($parsed_sql, 'query_only');
 210                 $result        = @PMA_mysql_query($sql_constraints);
 211                 if (PMA_mysql_error()) {
 212                     require_once('./header.inc.php');
 213                     PMA_mysqlDie('', $sql_structure, '', $err_url);
 214                 } else if (isset($sql_query)) {
 215                     $sql_query .= "\n" . $sql_constraints;
 216                 } else {
 217                     $sql_query = $sql_constraints;
 218                 }
 219             }
 220
 221         } else {
 222             $sql_query='';
 223         }
 224
 225         // Copy the data
 226         if ($result != FALSE && ($what == 'data' || $what == 'dataonly')) {
 227             $sql_insert_data = 'INSERT INTO ' . $target . ' SELECT * FROM ' . $source;
 228             $result          = @PMA_mysql_query($sql_insert_data);
 229             if (PMA_mysql_error()) {
 230                 require_once('./header.inc.php');
 231                 PMA_mysqlDie('', $sql_insert_data, '', $err_url);
 232             }
 233             $sql_query .= "\n\n" . $sql_insert_data . ';';
 234         }
 235
 236         require_once('./libraries/relation.lib.php');
 237         $cfgRelation = PMA_getRelationsParam();
 238
 239         // Drops old table if the user has requested to move it
 240         if (isset($submit_move)) {
 241
 242             // This could avoid some problems with replicated databases, when
 243             // moving table from replicated one to not replicated one
 244             PMA_mysql_select_db($db);
 245
 246             $sql_drop_table = 'DROP TABLE ' . $source;
 247             $result         = @PMA_mysql_query($sql_drop_table);
 248             if (PMA_mysql_error()) {
 249                 require_once('./header.inc.php');
 250                 PMA_mysqlDie('', $sql_drop_table, '', $err_url);
 251             }
 252
 253             // garvin: Move old entries from PMA-DBs to new table
 254             if ($cfgRelation['commwork']) {
 255                 $remove_query = 'UPDATE ' . PMA_backquote($cfgRelation['column_info'])
 256                               . ' SET     table_name = \'' . PMA_sqlAddslashes($new_name) . '\', '
 257                               . '        db_name    = \'' . PMA_sqlAddslashes($target_db) . '\''
 258                               . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 259                               . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 260                 $rmv_rs    = PMA_query_as_cu($remove_query);
 261                 unset($rmv_query);
 262             }
 263
 264             // garvin: updating bookmarks is not possible since only a single table is moved,
 265             // and not the whole DB.
 266             // if ($cfgRelation['bookmarkwork']) {
 267             //     $remove_query = 'UPDATE ' . PMA_backquote($cfgRelation['bookmark'])
 268             //                   . ' SET     dbase = \'' . PMA_sqlAddslashes($target_db) . '\''
 269             //                   . ' WHERE dbase  = \'' . PMA_sqlAddslashes($db) . '\'';
 270             //     $rmv_rs    = PMA_query_as_cu($remove_query);
 271             //     unset($rmv_query);
 272             // }
 273
 274             if ($cfgRelation['displaywork']) {
 275                 $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['table_info'])
 276                                 . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\', '
 277                                 . '         table_name = \'' . PMA_sqlAddslashes($new_name) . '\''
 278                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 279                                 . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 280                 $tb_rs    = PMA_query_as_cu($table_query);
 281                 unset($table_query);
 282                 unset($tb_rs);
 283             }
 284
 285             if ($cfgRelation['relwork']) {
 286                 $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['relation'])
 287                                 . ' SET     foreign_table = \'' . PMA_sqlAddslashes($new_name) . '\','
 288                                 . '         foreign_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 289                                 . ' WHERE foreign_db  = \'' . PMA_sqlAddslashes($db) . '\''
 290                                 . ' AND foreign_table = \'' . PMA_sqlAddslashes($table) . '\'';
 291                 $tb_rs    = PMA_query_as_cu($table_query);
 292                 unset($table_query);
 293                 unset($tb_rs);
 294
 295                 $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['relation'])
 296                                 . ' SET     master_table = \'' . PMA_sqlAddslashes($new_name) . '\','
 297                                 . '         master_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 298                                 . ' WHERE master_db  = \'' . PMA_sqlAddslashes($db) . '\''
 299                                 . ' AND master_table = \'' . PMA_sqlAddslashes($table) . '\'';
 300                 $tb_rs    = PMA_query_as_cu($table_query);
 301                 unset($table_query);
 302                 unset($tb_rs);
 303             }
 304
 305             // garvin: [TODO] Can't get moving PDFs the right way. The page numbers always
 306             // get screwed up independently from duplication because the numbers do not
 307             // seem to be stored on a per-database basis. Would the author of pdf support
 308             // please have a look at it?
 309
 310             if ($cfgRelation['pdfwork']) {
 311                 $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['table_coords'])
 312                                 . ' SET     table_name = \'' . PMA_sqlAddslashes($new_name) . '\','
 313                                 . '         db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 314                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 315                                 . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 316                 $tb_rs    = PMA_query_as_cu($table_query);
 317                 unset($table_query);
 318                 unset($tb_rs);
 319                 /*
 320                 $pdf_query = 'SELECT pdf_page_number '
 321                            . ' FROM ' . PMA_backquote($cfgRelation['table_coords'])
 322                            . ' WHERE db_name  = \'' . PMA_sqlAddslashes($target_db) . '\''
 323                            . ' AND table_name = \'' . PMA_sqlAddslashes($new_name) . '\'';
 324                 $pdf_rs = PMA_query_as_cu($pdf_query);
 325
 326                 while ($pdf_copy_row = @PMA_mysql_fetch_array($pdf_rs)) {
 327                     $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['pdf_pages'])
 328                                     . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 329                                     . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 330                                     . ' AND page_nr = \'' . PMA_sqlAddslashes($pdf_copy_row['pdf_page_number']) . '\'';
 331                     $tb_rs    = PMA_query_as_cu($table_query);
 332                     unset($table_query);
 333                     unset($tb_rs);
 334                 }
 335                 */
 336             }
 337
 338             $sql_query      .= "\n\n" . $sql_drop_table . ';';
 339         } else {
 340             // garvin: Create new entries as duplicates from old PMA DBs
 341             if ($what != 'dataonly' && !isset($maintain_relations)) {
 342                 if ($cfgRelation['commwork']) {
 343                     // Get all comments and MIME-Types for current table
 344                     $comments_copy_query = 'SELECT
 345                                                 column_name, ' . PMA_backquote('comment') . ($cfgRelation['mimework'] ? ', mimetype, transformation, transformation_options' : '') . '
 346                                             FROM ' . PMA_backquote($cfgRelation['column_info']) . '
 347                                             WHERE
 348                                                 db_name = \'' . PMA_sqlAddslashes($db) . '\' AND
 349                                                 table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 350                     $comments_copy_rs    = PMA_query_as_cu($comments_copy_query);
 351
 352                     // Write every comment as new copied entry. [MIME]
 353                     while ($comments_copy_row = @PMA_mysql_fetch_array($comments_copy_rs)) {
 354                         $new_comment_query = 'REPLACE INTO ' . PMA_backquote($cfgRelation['column_info'])
 355                                     . ' (db_name, table_name, column_name, ' . PMA_backquote('comment') . ($cfgRelation['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ') '
 356                                     . ' VALUES('
 357                                     . '\'' . PMA_sqlAddslashes($target_db) . '\','
 358                                     . '\'' . PMA_sqlAddslashes($new_name) . '\','
 359                                     . '\'' . PMA_sqlAddslashes($comments_copy_row['column_name']) . '\''
 360                                     . ($cfgRelation['mimework'] ? ',\'' . PMA_sqlAddslashes($comments_copy_row['comment']) . '\','
 361                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['mimetype']) . '\','
 362                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation']) . '\','
 363                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation_options']) . '\'' : '')
 364                                     . ')';
 365                         $new_comment_rs    = PMA_query_as_cu($new_comment_query);
 366                     } // end while
 367                 }
 368
 369                 if ($db != $target_db) {
 370                     $get_fields = array('user','label','query');
 371                     $where_fields = array('dbase' => $db);
 372                     $new_fields = array('dbase' => $target_db);
 373                     PMA_duplicate_table('bookmarkwork', 'bookmark', $get_fields, $where_fields, $new_fields);
 374                 }
 375
 376                 $get_fields = array('display_field');
 377                 $where_fields = array('db_name' => $db, 'table_name' => $table);
 378                 $new_fields = array('db_name' => $target_db, 'table_name' => $new_name);
 379                 PMA_duplicate_table('displaywork', 'table_info', $get_fields, $where_fields, $new_fields);
 380
 381                 $get_fields = array('master_field', 'foreign_db', 'foreign_table', 'foreign_field');
 382                 $where_fields = array('master_db' => $db, 'master_table' => $table);
 383                 $new_fields = array('master_db' => $target_db, 'master_table' => $new_name);
 384                 PMA_duplicate_table('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 385
 386                 $get_fields = array('foreign_field', 'master_db', 'master_table', 'master_field');
 387                 $where_fields = array('foreign_db' => $db, 'foreign_table' => $table);
 388                 $new_fields = array('foreign_db' => $target_db, 'foreign_table' => $new_name);
 389                 PMA_duplicate_table('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 390
 391                 // garvin: [TODO] Can't get duplicating PDFs the right way. The page numbers always
 392                 // get screwed up independently from duplication because the numbers do not
 393                 // seem to be stored on a per-database basis. Would the author of pdf support
 394                 // please have a look at it?
 395                 /*
 396                 $get_fields = array('page_descr');
 397                 $where_fields = array('db_name' => $db);
 398                 $new_fields = array('db_name' => $target_db);
 399                 $last_id = PMA_duplicate_table('pdfwork', 'pdf_pages', $get_fields, $where_fields, $new_fields);
 400
 401                 if (isset($last_id) && $last_id >= 0) {
 402                     $get_fields = array('x', 'y');
 403                     $where_fields = array('db_name' => $db, 'table_name' => $table);
 404                     $new_fields = array('db_name' => $target_db, 'table_name' => $new_name, 'pdf_page_number' => $last_id);
 405                     PMA_duplicate_table('pdfwork', 'table_coords', $get_fields, $where_fields, $new_fields);
 406                 }
 407                 */
 408             }
 409         }
 410
 411         $message   = (isset($submit_move) ? $strMoveTableOK : $strCopyTableOK);
 412         $message   = sprintf($message, htmlspecialchars($source), htmlspecialchars($target));
 413         $reload    = 1;
 414         $js_to_run = 'functions.js';
 415         /* Check: Work on new table or on old table? */
 416         if (isset($submit_move)) {
 417             $db        = $target_db;
 418             $table     = $new_name;
 419         } else {
 420             $pma_uri_parts = parse_url($cfg['PmaAbsoluteUri']);
 421             if (isset($switch_to_new) && $switch_to_new == 'true') {
 422                 setcookie('pma_switch_to_new', 'true', 0, substr($pma_uri_parts['path'], 0, strrpos($pma_uri_parts['path'], '/')), '', ($pma_uri_parts['scheme'] == 'https'));
 423                 $db             = $target_db;
 424                 $table          = $new_name;
 425             } else {
 426                 setcookie('pma_switch_to_new', '', 0, substr($pma_uri_parts['path'], 0, strrpos($pma_uri_parts['path'], '/')), '', ($pma_uri_parts['scheme'] == 'https'));
 427                 // garvin:Keep original table for work.
 428             }
 429         }
 430     }
 431     require_once('./header.inc.php');
 432 } // end is target table name
 433
 434
 435 /**
 436  * No new name for the table!
 437  */
 438 else {
 439     require_once('./header.inc.php');
 440     PMA_mysqlDie($strTableEmpty, '', '', $err_url);
 441 }
 442
 443
 444 /**
 445  * Back to the calling script
 446  */
 447
 448 require('./tbl_properties.php');
 449 ?>