tbl_move_copy.php3

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