tbl_move_copy.php3

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