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 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     $target = PMA_backquote($target_db) . '.' . PMA_backquote($new_name);
 138
 139     include('./libraries/build_dump.lib.php3');
 140
 141     $sql_structure = PMA_getTableDef($db, $table, "\n", $err_url);
 142     $parsed_sql =  PMA_SQP_parse($sql_structure);
 143     // no need to PMA_backquote()
 144     $parsed_sql[2]['data'] = $target;
 145     $sql_structure = PMA_SQP_formatHtml($parsed_sql, 'query_only');
 146
 147     // do not create the table if dataonly
 148     if ($what != 'dataonly') {
 149         // If table exists, and 'add drop table' is selected: Drop it!
 150         $drop_query = '';
 151         if (isset($drop_if_exists) && $drop_if_exists == 'true') {
 152             $drop_query = 'DROP TABLE IF EXISTS ' . PMA_backquote($target_db) . '.' . PMA_backquote($new_name);
 153             $result        = @PMA_mysql_query($drop_query);
 154             if (PMA_mysql_error()) {
 155                 include('./header.inc.php3');
 156                 PMA_mysqlDie('', $sql_structure, '', $err_url);
 157             }
 158
 159             if (isset($sql_query)) {
 160                 $sql_query .= "\n" . $drop_query . ';';
 161             } else {
 162                 $sql_query = $drop_query . ';';
 163             }
 164
 165             // garvin: If an existing table gets deleted, maintain any entries
 166             // for the PMA_* tables
 167             $maintain_relations = true;
 168         }
 169
 170         $result        = @PMA_mysql_query($sql_structure);
 171         if (PMA_mysql_error()) {
 172             include('./header.inc.php3');
 173             PMA_mysqlDie('', $sql_structure, '', $err_url);
 174         } else if (isset($sql_query)) {
 175             $sql_query .= "\n" . $sql_structure . ';';
 176         } else {
 177             $sql_query = $sql_structure . ';';
 178         }
 179     } else {
 180         $sql_query='';
 181     }
 182
 183     // Copy the data
 184     if ($result != FALSE && ($what == 'data' || $what == 'dataonly')) {
 185         // speedup copy table - staybyte - 22. Juni 2001
 186         if (PMA_MYSQL_INT_VERSION >= 32300) {
 187             $sql_insert_data = 'INSERT INTO ' . $target . ' SELECT * FROM ' . $source;
 188             $result          = @PMA_mysql_query($sql_insert_data);
 189             if (PMA_mysql_error()) {
 190                 include('./header.inc.php3');
 191                 PMA_mysqlDie('', $sql_insert_data, '', $err_url);
 192             }
 193         } // end MySQL >= 3.23
 194         else {
 195             $sql_insert_data = '';
 196             PMA_getTableContent($db, $table, 0, 0, 'PMA_myHandler', $err_url,'');
 197         } // end MySQL < 3.23
 198         $sql_query .= "\n\n" . $sql_insert_data;
 199     }
 200
 201     include('./libraries/relation.lib.php3');
 202     $cfgRelation = PMA_getRelationsParam();
 203
 204     // Drops old table if the user has requested to move it
 205     if (isset($submit_move)) {
 206         $sql_drop_table = 'DROP TABLE ' . $source;
 207         $result         = @PMA_mysql_query($sql_drop_table);
 208         if (PMA_mysql_error()) {
 209             include('./header.inc.php3');
 210             PMA_mysqlDie('', $sql_drop_table, '', $err_url);
 211         }
 212
 213         // garvin: Move old entries from PMA-DBs to new table
 214         if ($cfgRelation['commwork']) {
 215             $remove_query = 'UPDATE ' . PMA_backquote($cfgRelation['column_info'])
 216                           . ' SET     table_name = \'' . PMA_sqlAddslashes($new_name) . '\', '
 217                           . '        db_name    = \'' . PMA_sqlAddslashes($target_db) . '\''
 218                           . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 219                           . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 220             $rmv_rs    = PMA_query_as_cu($remove_query);
 221             unset($rmv_query);
 222         }
 223
 224         // garvin: updating bookmarks is not possible since only a single table is moved,
 225         // and not the whole DB.
 226         // if ($cfgRelation['bookmarkwork']) {
 227         //     $remove_query = 'UPDATE ' . PMA_backquote($cfgRelation['bookmark'])
 228         //                   . ' SET     dbase = \'' . PMA_sqlAddslashes($target_db) . '\''
 229         //                   . ' WHERE dbase  = \'' . PMA_sqlAddslashes($db) . '\'';
 230         //     $rmv_rs    = PMA_query_as_cu($remove_query);
 231         //     unset($rmv_query);
 232         // }
 233
 234         if ($cfgRelation['displaywork']) {
 235             $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['table_info'])
 236                             . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\', '
 237                             . '         table_name = \'' . PMA_sqlAddslashes($new_name) . '\''
 238                             . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 239                             . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 240             $tb_rs    = PMA_query_as_cu($table_query);
 241             unset($table_query);
 242             unset($tb_rs);
 243         }
 244
 245         if ($cfgRelation['relwork']) {
 246             $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['relation'])
 247                             . ' SET     foreign_table = \'' . PMA_sqlAddslashes($new_name) . '\','
 248                             . '         foreign_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 249                             . ' WHERE foreign_db  = \'' . PMA_sqlAddslashes($db) . '\''
 250                             . ' AND foreign_table = \'' . PMA_sqlAddslashes($table) . '\'';
 251             $tb_rs    = PMA_query_as_cu($table_query);
 252             unset($table_query);
 253             unset($tb_rs);
 254
 255             $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['relation'])
 256                             . ' SET     master_table = \'' . PMA_sqlAddslashes($new_name) . '\','
 257                             . '         master_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 258                             . ' WHERE master_db  = \'' . PMA_sqlAddslashes($db) . '\''
 259                             . ' AND master_table = \'' . PMA_sqlAddslashes($table) . '\'';
 260             $tb_rs    = PMA_query_as_cu($table_query);
 261             unset($table_query);
 262             unset($tb_rs);
 263         }
 264
 265         // garvin: [TODO] Can't get moving PDFs the right way. The page numbers always
 266         // get screwed up independently from duplication because the numbers do not
 267         // seem to be stored on a per-database basis. Would the author of pdf support
 268         // please have a look at it?
 269
 270         /*
 271         if ($cfgRelation['pdfwork']) {
 272             $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['table_coords'])
 273                             . ' SET     table_name = \'' . PMA_sqlAddslashes($new_name) . '\','
 274                             . '         db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 275                             . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 276                             . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 277             $tb_rs    = PMA_query_as_cu($table_query);
 278             unset($table_query);
 279             unset($tb_rs);
 280
 281             $pdf_query = 'SELECT pdf_page_number '
 282                        . ' FROM ' . PMA_backquote($cfgRelation['table_coords'])
 283                        . ' WHERE db_name  = \'' . PMA_sqlAddslashes($target_db) . '\''
 284                        . ' AND table_name = \'' . PMA_sqlAddslashes($new_name) . '\'';
 285             $pdf_rs = PMA_query_as_cu($pdf_query);
 286
 287             while ($pdf_copy_row = @PMA_mysql_fetch_array($pdf_rs)) {
 288                 $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['pdf_pages'])
 289                                 . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 290                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''
 291                                 . ' AND page_nr = \'' . PMA_sqlAddslashes($pdf_copy_row['pdf_page_number']) . '\'';
 292                 $tb_rs    = PMA_query_as_cu($table_query);
 293                 unset($table_query);
 294                 unset($tb_rs);
 295             }
 296         }
 297         */
 298
 299         $sql_query      .= "\n\n" . $sql_drop_table . ';';
 300         $db             = $target_db;
 301         $table          = $new_name;
 302     } else {
 303         // garvin: Create new entries as duplicates from old PMA DBs
 304         if ($what != 'dataonly' && !isset($maintain_relations)) {
 305             if ($cfgRelation['commwork']) {
 306                 // Get all comments and MIME-Types for current table
 307                 $comments_copy_query = 'SELECT
 308                                             column_name, ' . PMA_backquote('comment') . ($cfgRelation['mimework'] ? ', mimetype, transformation, transformation_options' : '') . '
 309                                         FROM ' . PMA_backquote($cfgRelation['column_info']) . '
 310                                         WHERE
 311                                             db_name = \'' . PMA_sqlAddslashes($db) . '\' AND
 312                                             table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 313                 $comments_copy_rs    = PMA_query_as_cu($comments_copy_query);
 314
 315                 // Write every comment as new copied entry. [MIME]
 316                 while ($comments_copy_row = @PMA_mysql_fetch_array($comments_copy_rs)) {
 317                     $new_comment_query = 'INSERT INTO ' . PMA_backquote($cfgRelation['column_info'])
 318                                 . ' (db_name, table_name, column_name, ' . PMA_backquote('comment') . ($cfgRelation['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ') '
 319                                 . ' VALUES('
 320                                 . '\'' . PMA_sqlAddslashes($target_db) . '\','
 321                                 . '\'' . PMA_sqlAddslashes($new_name) . '\','
 322                                 . '\'' . PMA_sqlAddslashes($comments_copy_row['column_name']) . '\''
 323                                 . ($cfgRelation['mimework'] ? ',\'' . PMA_sqlAddslashes($comments_copy_row['comment']) . '\','
 324                                         . '\'' . PMA_sqlAddslashes($comments_copy_row['mimetype']) . '\','
 325                                         . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation']) . '\','
 326                                         . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation_options']) . '\'' : '')
 327                                 . ')';
 328                     $new_comment_rs    = PMA_query_as_cu($new_comment_query);
 329                 } // end while
 330             }
 331
 332             if ($db != $target_db) {
 333                 $get_fields = array('user','label','query');
 334                 $where_fields = array('dbase' => $db);
 335                 $new_fields = array('dbase' => $target_db);
 336                 PMA_duplicate_table('bookmarkwork', 'bookmark', $get_fields, $where_fields, $new_fields);
 337             }
 338
 339             $get_fields = array('display_field');
 340             $where_fields = array('db_name' => $db, 'table_name' => $table);
 341             $new_fields = array('db_name' => $target_db, 'table_name' => $new_name);
 342             PMA_duplicate_table('displaywork', 'table_info', $get_fields, $where_fields, $new_fields);
 343
 344             $get_fields = array('master_field', 'foreign_db', 'foreign_table', 'foreign_field');
 345             $where_fields = array('master_db' => $db, 'master_table' => $table);
 346             $new_fields = array('master_db' => $target_db, 'master_table' => $new_name);
 347             PMA_duplicate_table('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 348
 349             $get_fields = array('foreign_field', 'master_db', 'master_table', 'master_field');
 350             $where_fields = array('foreign_db' => $db, 'foreign_table' => $table);
 351             $new_fields = array('foreign_db' => $target_db, 'foreign_table' => $new_name);
 352             PMA_duplicate_table('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 353
 354             // garvin: [TODO] Can't get duplicating PDFs the right way. The page numbers always
 355             // get screwed up independently from duplication because the numbers do not
 356             // seem to be stored on a per-database basis. Would the author of pdf support
 357             // please have a look at it?
 358             /*
 359             $get_fields = array('page_descr');
 360             $where_fields = array('db_name' => $db);
 361             $new_fields = array('db_name' => $target_db);
 362             $last_id = PMA_duplicate_table('pdfwork', 'pdf_pages', $get_fields, $where_fields, $new_fields);
 363
 364             if (isset($last_id) && $last_id >= 0) {
 365                 $get_fields = array('x', 'y');
 366                 $where_fields = array('db_name' => $db, 'table_name' => $table);
 367                 $new_fields = array('db_name' => $target_db, 'table_name' => $new_name, 'pdf_page_number' => $last_id);
 368                 PMA_duplicate_table('pdfwork', 'table_coords', $get_fields, $where_fields, $new_fields);
 369             }
 370             */
 371         }
 372     }
 373
 374     $message   = (isset($submit_move) ? $strMoveTableOK : $strCopyTableOK);
 375     $message   = sprintf($message, $source, $target);
 376     $reload    = 1;
 377     $js_to_run = 'functions.js';
 378     include('./header.inc.php3');
 379 } // end is target table name
 380
 381
 382 /**
 383  * No new name for the table!
 384  */
 385 else {
 386     include('./header.inc.php3');
 387     PMA_mysqlDie($strTableEmpty, '', '', $err_url);
 388 }
 389
 390
 391 /**
 392  * Back to the calling script
 393  */
 394 require('./tbl_properties.php3');
 395 ?>