phpmyadmin/libraries/Table.class.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  *
   5  * @version $Id$
   6  */
   7
   8 /**
   9  *
  10  */
  11 class PMA_Table {
  12
  13     /**
  14      * @var string  table name
  15      */
  16     var $name = '';
  17
  18     /**
  19      * @var string  database name
  20      */
  21     var $db_name = '';
  22
  23     /**
  24      * @var string  engine (innodb, myisam, bdb, ...)
  25      */
  26     var $engine = '';
  27
  28     /**
  29      * @var string  type (view, base table, system view)
  30      */
  31     var $type = '';
  32
  33     /**
  34      * @var array   settings
  35      */
  36     var $settings = array();
  37
  38     /**
  39      * @var array errors occured
  40      */
  41     var $errors = array();
  42
  43     /**
  44      * @var array messages
  45      */
  46     var $messages = array();
  47
  48     /**
  49      * Constructor
  50      *
  51      * @param   string  $table_name table name
  52      * @param   string  $db_name    database name
  53      */
  54     function __construct($table_name, $db_name)
  55     {
  56         $this->setName($table_name);
  57         $this->setDbName($db_name);
  58     }
  59
  60     /**
  61      * @see PMA_Table::getName()
  62      */
  63     function __toString()
  64     {
  65         return $this->getName();
  66     }
  67
  68     function getLastError()
  69     {
  70         return end($this->errors);
  71     }
  72
  73     function getLastMessage()
  74     {
  75         return end($this->messages);
  76     }
  77
  78     /**
  79      * sets table anme
  80      *
  81      * @uses    $this->name to set it
  82      * @param   string  $table_name new table name
  83      */
  84     function setName($table_name)
  85     {
  86         $this->name = $table_name;
  87     }
  88
  89     /**
  90      * returns table name
  91      *
  92      * @uses    $this->name as return value
  93      * @param   boolean wether to quote name with backticks ``
  94      * @return  string  table name
  95      */
  96     function getName($quoted = false)
  97     {
  98         if ($quoted) {
  99             return PMA_backquote($this->name);
 100         }
 101         return $this->name;
 102     }
 103
 104     /**
 105      * sets database name for this table
 106      *
 107      * @uses    $this->db_name  to set it
 108      * @param   string  $db_name
 109      */
 110     function setDbName($db_name)
 111     {
 112         $this->db_name = $db_name;
 113     }
 114
 115     /**
 116      * returns database name for this table
 117      *
 118      * @uses    $this->db_name  as return value
 119      * @param   boolean wether to quote name with backticks ``
 120      * @return  string  database name for this table
 121      */
 122     function getDbName($quoted = false)
 123     {
 124         if ($quoted) {
 125             return PMA_backquote($this->db_name);
 126         }
 127         return $this->db_name;
 128     }
 129
 130     /**
 131      * returns full name for table, including database name
 132      *
 133      * @param   boolean wether to quote name with backticks ``
 134      */
 135     function getFullName($quoted = false)
 136     {
 137         return $this->getDbName($quoted) . '.' . $this->getName($quoted);
 138     }
 139
 140     function isView($db = null, $table = null)
 141     {
 142         if (strlen($db) && strlen($table)) {
 143             return PMA_Table::_isView($db, $table);
 144         }
 145
 146         if (isset($this) && strpos($this->get('TABLE TYPE'), 'VIEW')) {
 147             return true;
 148         }
 149
 150         return false;
 151     }
 152
 153     /**
 154      * sets given $value for given $param
 155      *
 156      * @uses    $this->settings to add or change value
 157      * @param   string  param name
 158      * @param   mixed   param value
 159      */
 160     function set($param, $value)
 161     {
 162         $this->settings[$param] = $value;
 163     }
 164
 165     /**
 166      * returns value for given setting/param
 167      *
 168      * @uses    $this->settings to return value
 169      * @param   string  name for value to return
 170      * @return  mixed   value for $param
 171      */
 172     function get($param)
 173     {
 174         if (isset($this->settings[$param])) {
 175             return $this->settings[$param];
 176         }
 177
 178         return null;
 179     }
 180
 181     /**
 182      * loads structure data
 183      */
 184     function loadStructure()
 185     {
 186         $table_info = PMA_DBI_get_tables_full($this->getDbName(), $this->getName());
 187
 188         if (false === $table_info) {
 189             return false;
 190         }
 191
 192         $this->settings = $table_info;
 193
 194         if ($this->get('TABLE_ROWS') === null) {
 195             $this->set('TABLE_ROWS', PMA_Table::countRecords($this->getDbName(),
 196                 $this->getName(), true, true));
 197         }
 198
 199         $create_options = explode(' ', $this->get('TABLE_ROWS'));
 200
 201         // export create options by its name as variables into gloabel namespace
 202         // f.e. pack_keys=1 becomes available as $pack_keys with value of '1'
 203         foreach ($create_options as $each_create_option) {
 204             $each_create_option = explode('=', $each_create_option);
 205             if (isset($each_create_option[1])) {
 206                 $this->set($$each_create_option[0], $each_create_option[1]);
 207             }
 208         }
 209     }
 210
 211     /**
 212      * old PHP 4style constructor
 213      *
 214      * @see     PMA_Table::__construct()
 215      */
 216     function PMA_Table($table_name, $db_name)
 217     {
 218         $this->__construct($table_name, $db_name);
 219     }
 220
 221     /**
 222      * Checks if this "table" is a view
 223      *
 224      * @deprecated
 225      * @todo see what we could do with the possible existence of $table_is_view
 226      * @param   string   the database name
 227      * @param   string   the table name
 228      *
 229      * @return  boolean  whether this is a view
 230      *
 231      * @access  public
 232      */
 233     function _isView($db, $table) {
 234         // maybe we already know if the table is a view
 235         if (isset($GLOBALS['tbl_is_view']) && $GLOBALS['tbl_is_view']) {
 236             return true;
 237         }
 238         // old MySQL version: no view
 239         if (PMA_MYSQL_INT_VERSION < 50000) {
 240             return false;
 241         }
 242         // This would be the correct way of doing the check but at least in
 243         // MySQL 5.0.33 it's too slow when there are hundreds of databases
 244         // and/or tables (more than 3 minutes for 400 tables)
 245         /*if (false === PMA_DBI_fetch_value('SELECT TABLE_NAME FROM `information_schema`.`VIEWS` WHERE `TABLE_SCHEMA` = \'' . $db . '\' AND `TABLE_NAME` = \'' . $table . '\';')) {
 246             return false;
 247         } else {
 248             return true;
 249         } */
 250         // A more complete verification would be to check if all columns
 251         // from the result set are NULL except Name and Comment.
 252         // MySQL from 5.0.0 to 5.0.12 returns 'view',
 253         // from 5.0.13 returns 'VIEW'.
 254         $comment = strtoupper(PMA_DBI_fetch_value('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . $table . '\'', 0, 'Comment'));
 255         // use substr() because the comment might contain something like:
 256         // (VIEW 'BASE2.VTEST' REFERENCES INVALID TABLE(S) OR COLUMN(S) OR FUNCTION)
 257         return (substr($comment, 0, 4) == 'VIEW');
 258     }
 259
 260     /**
 261      * generates column/field specification for ALTER or CREATE TABLE syntax
 262      *
 263      * @todo    move into class PMA_Column
 264      * @todo on the interface, some js to clear the default value when the default
 265      * current_timestamp is checked
 266      * @static
 267      * @param   string  $name       name
 268      * @param   string  $type       type ('INT', 'VARCHAR', 'BIT', ...)
 269      * @param   string  $length     length ('2', '5,2', '', ...)
 270      * @param   string  $attribute
 271      * @param   string  $collation
 272      * @param   string  $null       with 'NULL' or 'NOT NULL'
 273      * @param   string  $default    default value
 274      * @param   boolean $default_current_timestamp  whether default value is
 275      *                                              CURRENT_TIMESTAMP or not
 276      *                                              this overrides $default value
 277      * @param   string  $extra      'AUTO_INCREMENT'
 278      * @param   string  $comment    field comment
 279      * @param   array   &$field_primary list of fields for PRIMARY KEY
 280      * @param   string  $index
 281      * @param   string  $default_orig
 282      * @return  string  field specification
 283      */
 284     function generateFieldSpec($name, $type, $length = '', $attribute = '',
 285         $collation = '', $null = false, $default = '',
 286         $default_current_timestamp = false, $extra = '', $comment = '',
 287         &$field_primary, $index, $default_orig = false)
 288     {
 289
 290         $is_timestamp = strpos(' ' . strtoupper($type), 'TIMESTAMP') == 1;
 291
 292         // $default_current_timestamp has priority over $default
 293
 294         /**
 295          * @todo include db-name
 296          */
 297         $query = PMA_backquote($name) . ' ' . $type;
 298
 299         if ($length != ''
 300             && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT)$@i', $type)) {
 301             $query .= '(' . $length . ')';
 302         }
 303
 304         if ($attribute != '') {
 305             $query .= ' ' . $attribute;
 306         }
 307
 308         if (PMA_MYSQL_INT_VERSION >= 40100 && !empty($collation)
 309           && $collation != 'NULL'
 310           && preg_match('@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', $type)) {
 311             $query .= PMA_generateCharsetQueryPart($collation);
 312         }
 313
 314         if ($null !== false) {
 315             if (!empty($null)) {
 316                 $query .= ' NOT NULL';
 317             } else {
 318                 $query .= ' NULL';
 319             }
 320         }
 321
 322         if ($default_current_timestamp && $is_timestamp) {
 323             $query .= ' DEFAULT CURRENT_TIMESTAMP';
 324         // auto_increment field cannot have a default value
 325         } elseif ($extra !== 'AUTO_INCREMENT'
 326           && (strlen($default) || $default != $default_orig)) {
 327             if (strtoupper($default) == 'NULL') {
 328                 $query .= ' DEFAULT NULL';
 329             } else {
 330                 if (strlen($default)) {
 331                     if ($is_timestamp && $default == '0') {
 332                         // a TIMESTAMP does not accept DEFAULT '0'
 333                         // but DEFAULT 0  works
 334                         $query .= ' DEFAULT ' . PMA_sqlAddslashes($default);
 335                     } elseif ($default && $type == 'BIT') {
 336                         $query .= ' DEFAULT b\'' . preg_replace('/[^01]/', '0', $default) . '\'';
 337                     } else {
 338                         $query .= ' DEFAULT \'' . PMA_sqlAddslashes($default) . '\'';
 339                     }
 340                 }
 341             }
 342         }
 343
 344         if (!empty($extra)) {
 345             $query .= ' ' . $extra;
 346             // Force an auto_increment field to be part of the primary key
 347             // even if user did not tick the PK box;
 348             if ($extra == 'AUTO_INCREMENT') {
 349                 $primary_cnt = count($field_primary);
 350                 if (1 == $primary_cnt) {
 351                     for ($j = 0; $j < $primary_cnt && $field_primary[$j] != $index; $j++) {
 352                         //void
 353                     }
 354                     if (isset($field_primary[$j]) && $field_primary[$j] == $index) {
 355                         $query .= ' PRIMARY KEY';
 356                         unset($field_primary[$j]);
 357                     }
 358                 // but the PK could contain other columns so do not append
 359                 // a PRIMARY KEY clause, just add a member to $field_primary
 360                 } else {
 361                     $found_in_pk = false;
 362                     for ($j = 0; $j < $primary_cnt; $j++) {
 363                         if ($field_primary[$j] == $index) {
 364                             $found_in_pk = true;
 365                             break;
 366                         }
 367                     } // end for
 368                     if (! $found_in_pk) {
 369                         $field_primary[] = $index;
 370                     }
 371                 }
 372             } // end if (auto_increment)
 373         }
 374         if (PMA_MYSQL_INT_VERSION >= 40100 && !empty($comment)) {
 375             $query .= " COMMENT '" . PMA_sqlAddslashes($comment) . "'";
 376         }
 377         return $query;
 378     } // end function
 379
 380     /**
 381      * Counts and returns (or displays) the number of records in a table
 382      *
 383      * Revision 13 July 2001: Patch for limiting dump size from
 384      * vinay@sanisoft.com & girish@sanisoft.com
 385      *
 386      * @param   string   the current database name
 387      * @param   string   the current table name
 388      * @param   boolean  whether to retain or to displays the result
 389      * @param   boolean  whether to force an exact count
 390      *
 391      * @return  mixed    the number of records if retain is required, true else
 392      *
 393      * @access  public
 394      */
 395     function countRecords($db, $table, $ret = false, $force_exact = false)
 396     {
 397         $row_count = false;
 398
 399         if (! $force_exact) {
 400             $row_count = PMA_DBI_fetch_value(
 401                 'SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \''
 402                     . PMA_sqlAddslashes($table, true) . '\';',
 403                 0, 'Rows');
 404         }
 405
 406         $tbl_is_view = PMA_Table::isView($db, $table);
 407
 408         // for a VIEW, $row_count is always false at this point
 409         if (false === $row_count || $row_count < $GLOBALS['cfg']['MaxExactCount']) {
 410             if (! $tbl_is_view) {
 411                 $row_count = PMA_DBI_fetch_value(
 412                     'SELECT COUNT(*) FROM ' . PMA_backquote($db) . '.'
 413                     . PMA_backquote($table));
 414             } else {
 415                 // For complex views, even trying to get a partial record
 416                 // count could bring down a server, so we offer an
 417                 // alternative: setting MaxExactCountViews to 0 will bypass
 418                 // completely the record counting for views
 419
 420                 if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
 421                     $row_count = 0;
 422                 } else {
 423                     // Counting all rows of a VIEW could be too long, so use
 424                     // a LIMIT clause.
 425                     // Use try_query because it can fail (a VIEW is based on
 426                     // a table that no longer exists)
 427                     $result = PMA_DBI_try_query(
 428                         'SELECT 1 FROM ' . PMA_backquote($db) . '.'
 429                             . PMA_backquote($table) . ' LIMIT '
 430                             . $GLOBALS['cfg']['MaxExactCountViews'],
 431                             null, PMA_DBI_QUERY_STORE);
 432                     if (!PMA_DBI_getError()) {
 433                         $row_count = PMA_DBI_num_rows($result);
 434                         PMA_DBI_free_result($result);
 435                     }
 436                 }
 437             }
 438         }
 439
 440         if ($ret) {
 441             return $row_count;
 442         }
 443
 444         /**
 445          * @deprecated at the moment nowhere is $return = false used
 446          */
 447         // Note: as of PMA 2.8.0, we no longer seem to be using
 448         // PMA_Table::countRecords() in display mode.
 449         echo PMA_formatNumber($row_count, 0);
 450         if ($tbl_is_view) {
 451             echo '&nbsp;'
 452                 . sprintf($GLOBALS['strViewMaxExactCount'],
 453                     $GLOBALS['cfg']['MaxExactCount'],
 454                     '[a@./Documentation.html#cfg_MaxExactCount@_blank]', '[/a]');
 455         }
 456     } // end of the 'PMA_Table::countRecords()' function
 457
 458     /**
 459      * @todo    add documentation
 460      */
 461     function generateAlter($oldcol, $newcol, $type, $length,
 462         $attribute, $collation, $null, $default, $default_current_timestamp,
 463         $extra, $comment='', $default_orig)
 464     {
 465         $empty_a = array();
 466         return PMA_backquote($oldcol) . ' '
 467             . PMA_Table::generateFieldSpec($newcol, $type, $length, $attribute,
 468                 $collation, $null, $default, $default_current_timestamp, $extra,
 469                 $comment, $empty_a, -1, $default_orig);
 470     } // end function
 471
 472     /**
 473      * Inserts existing entries in a PMA_* table by reading a value from an old entry
 474      *
 475      * @param   string  The array index, which Relation feature to check
 476      *                  ('relwork', 'commwork', ...)
 477      * @param   string  The array index, which PMA-table to update
 478      *                  ('bookmark', 'relation', ...)
 479      * @param   array   Which fields will be SELECT'ed from the old entry
 480      * @param   array   Which fields will be used for the WHERE query
 481      *                  (array('FIELDNAME' => 'FIELDVALUE'))
 482      * @param   array   Which fields will be used as new VALUES. These are the important
 483      *                  keys which differ from the old entry.
 484      *                  (array('FIELDNAME' => 'NEW FIELDVALUE'))
 485
 486      * @global  string  relation variable
 487      *
 488      * @author          Garvin Hicking <me@supergarv.de>
 489      */
 490     function duplicateInfo($work, $pma_table, $get_fields, $where_fields,
 491       $new_fields)
 492     {
 493         $last_id = -1;
 494
 495         if (isset($GLOBALS['cfgRelation']) && $GLOBALS['cfgRelation'][$work]) {
 496             $select_parts = array();
 497             $row_fields = array();
 498             foreach ($get_fields as $get_field) {
 499                 $select_parts[] = PMA_backquote($get_field);
 500                 $row_fields[$get_field] = 'cc';
 501             }
 502
 503             $where_parts = array();
 504             foreach ($where_fields as $_where => $_value) {
 505                 $where_parts[] = PMA_backquote($_where) . ' = \''
 506                     . PMA_sqlAddslashes($_value) . '\'';
 507             }
 508
 509             $new_parts = array();
 510             $new_value_parts = array();
 511             foreach ($new_fields as $_where => $_value) {
 512                 $new_parts[] = PMA_backquote($_where);
 513                 $new_value_parts[] = PMA_sqlAddslashes($_value);
 514             }
 515
 516             $table_copy_query = '
 517                 SELECT ' . implode(', ', $select_parts) . '
 518                   FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
 519                   . PMA_backquote($GLOBALS['cfgRelation'][$pma_table]) . '
 520                  WHERE ' . implode(' AND ', $where_parts);
 521
 522             // must use PMA_DBI_QUERY_STORE here, since we execute another
 523             // query inside the loop
 524             $table_copy_rs    = PMA_query_as_cu($table_copy_query, true,
 525                 PMA_DBI_QUERY_STORE);
 526
 527             while ($table_copy_row = @PMA_DBI_fetch_assoc($table_copy_rs)) {
 528                 $value_parts = array();
 529                 foreach ($table_copy_row as $_key => $_val) {
 530                     if (isset($row_fields[$_key]) && $row_fields[$_key] == 'cc') {
 531                         $value_parts[] = PMA_sqlAddslashes($_val);
 532                     }
 533                 }
 534
 535                 $new_table_query = '
 536                     INSERT IGNORE INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db'])
 537                         . '.' . PMA_backquote($GLOBALS['cfgRelation'][$pma_table]) . '
 538                     (' . implode(', ', $select_parts) . ',
 539                      ' . implode(', ', $new_parts) . ')
 540                     VALUES
 541                     (\'' . implode('\', \'', $value_parts) . '\',
 542                      \'' . implode('\', \'', $new_value_parts) . '\')';
 543
 544                 PMA_query_as_cu($new_table_query);
 545                 $last_id = PMA_DBI_insert_id();
 546             } // end while
 547
 548             PMA_DBI_free_result($table_copy_rs);
 549
 550             return $last_id;
 551         }
 552
 553         return true;
 554     } // end of 'PMA_Table::duplicateInfo()' function
 555
 556
 557     /**
 558      * Copies or renames table
 559      * @todo use RENAME for move operations
 560      *        - would work only if the databases are on the same filesystem,
 561      *          how can we check that? try the operation and
 562      *          catch an error?
 563      *        - for views, only if MYSQL > 50013
 564      *        - still have to handle pmadb synch.
 565      *
 566      * @author          Michal Cihar <michal@cihar.com>
 567      */
 568     function moveCopy($source_db, $source_table, $target_db, $target_table, $what, $move, $mode)
 569     {
 570         global $err_url;
 571
 572         // set export settings we need
 573         $GLOBALS['sql_backquotes'] = 1;
 574         $GLOBALS['asfile']         = 1;
 575
 576         // Ensure the target is valid
 577         if (! $GLOBALS['PMA_List_Database']->exists($source_db, $target_db)) {
 578             /**
 579              * @todo exit really needed here? or just a return?
 580              */
 581             exit;
 582         }
 583
 584         $source = PMA_backquote($source_db) . '.' . PMA_backquote($source_table);
 585         if (! isset($target_db) || ! strlen($target_db)) {
 586             $target_db = $source_db;
 587         }
 588
 589         // Doing a select_db could avoid some problems with replicated databases,
 590         // when moving table from replicated one to not replicated one
 591         PMA_DBI_select_db($target_db);
 592
 593         $target = PMA_backquote($target_db) . '.' . PMA_backquote($target_table);
 594
 595         // do not create the table if dataonly
 596         if ($what != 'dataonly') {
 597             require_once './libraries/export/sql.php';
 598
 599             $no_constraints_comments = true;
 600             $GLOBALS['sql_constraints_query'] = '';
 601
 602             $sql_structure = PMA_getTableDef($source_db, $source_table, "\n", $err_url, false, false);
 603             unset($no_constraints_comments);
 604             $parsed_sql =  PMA_SQP_parse($sql_structure);
 605             $analyzed_sql = PMA_SQP_analyze($parsed_sql);
 606             $i = 0;
 607             if (empty($analyzed_sql[0]['create_table_fields'])) {
 608             // this is not a CREATE TABLE, so find the first VIEW
 609                 $target_for_view = PMA_backquote($target_db);
 610                 while (true) {
 611                 if ($parsed_sql[$i]['type'] == 'alpha_reservedWord' && $parsed_sql[$i]['data'] == 'VIEW') {
 612                         break;
 613                     }
 614                     $i++;
 615                 }
 616             }
 617             unset($analyzed_sql);
 618             $server_sql_mode = PMA_DBI_fetch_value("SHOW VARIABLES LIKE 'sql_mode'", 0, 1);
 619             if ('ANSI_QUOTES' == $server_sql_mode) {
 620                 $table_delimiter = 'quote_double';
 621             } else {
 622                 $table_delimiter = 'quote_backtick';
 623             }
 624             unset($server_sql_mode);
 625
 626             /* nijel: Find table name in query and replace it */
 627             while ($parsed_sql[$i]['type'] != $table_delimiter) {
 628                 $i++;
 629             }
 630
 631             /* no need to PMA_backquote() */
 632             if (isset($target_for_view)) {
 633                 // this a view definition; we just found the first db name
 634                 // that follows DEFINER VIEW
 635                 // so change it for the new db name
 636                         $parsed_sql[$i]['data'] = $target_for_view;
 637                 // then we have to find all references to the source db
 638                 // and change them to the target db, ensuring we stay into
 639                 // the $parsed_sql limits
 640                 $last = $parsed_sql['len'] - 1;
 641                 $backquoted_source_db = PMA_backquote($source_db);
 642                 for (++$i; $i <= $last; $i++) {
 643                             if ($parsed_sql[$i]['type'] == $table_delimiter && $parsed_sql[$i]['data'] == $backquoted_source_db) {
 644                                 $parsed_sql[$i]['data'] = $target_for_view;
 645                     }
 646                 }
 647                 unset($last,$backquoted_source_db);
 648             } else {
 649                 $parsed_sql[$i]['data'] = $target;
 650             }
 651
 652             /* Generate query back */
 653             $sql_structure = PMA_SQP_formatHtml($parsed_sql, 'query_only');
 654             // If table exists, and 'add drop table' is selected: Drop it!
 655             $drop_query = '';
 656             if (isset($GLOBALS['drop_if_exists'])
 657               && $GLOBALS['drop_if_exists'] == 'true') {
 658                 if (PMA_Table::_isView($target_db,$target_table)) {
 659                     $drop_query = 'DROP VIEW';
 660                 } else {
 661                     $drop_query = 'DROP TABLE';
 662                 }
 663                 $drop_query .= ' IF EXISTS '
 664                     . PMA_backquote($target_db) . '.'
 665                     . PMA_backquote($target_table);
 666                 PMA_DBI_query($drop_query);
 667
 668                 $GLOBALS['sql_query'] .= "\n" . $drop_query . ';';
 669
 670                 // garvin: If an existing table gets deleted, maintain any
 671                 // entries for the PMA_* tables
 672                 $maintain_relations = true;
 673             }
 674
 675             @PMA_DBI_query($sql_structure);
 676             $GLOBALS['sql_query'] .= "\n" . $sql_structure . ';';
 677
 678             if (($move || isset($GLOBALS['add_constraints']))
 679               && !empty($GLOBALS['sql_constraints_query'])) {
 680                 $parsed_sql =  PMA_SQP_parse($GLOBALS['sql_constraints_query']);
 681                 $i = 0;
 682
 683                 // find the first $table_delimiter, it must be the source table name
 684                 while ($parsed_sql[$i]['type'] != $table_delimiter) {
 685                     $i++;
 686                     // maybe someday we should guard against going over limit
 687                     //if ($i == $parsed_sql['len']) {
 688                     //    break;
 689                     //}
 690                 }
 691
 692                 // replace it by the target table name, no need to PMA_backquote()
 693                 $parsed_sql[$i]['data'] = $target;
 694
 695                 // now we must remove all $table_delimiter that follow a CONSTRAINT
 696                 // keyword, because a constraint name must be unique in a db
 697
 698                 $cnt = $parsed_sql['len'] - 1;
 699
 700                 for ($j = $i; $j < $cnt; $j++) {
 701                     if ($parsed_sql[$j]['type'] == 'alpha_reservedWord'
 702                       && strtoupper($parsed_sql[$j]['data']) == 'CONSTRAINT') {
 703                         if ($parsed_sql[$j+1]['type'] == $table_delimiter) {
 704                             $parsed_sql[$j+1]['data'] = '';
 705                         }
 706                     }
 707                 }
 708
 709                 // Generate query back
 710                 $GLOBALS['sql_constraints_query'] = PMA_SQP_formatHtml($parsed_sql,
 711                     'query_only');
 712                 if ($mode == 'one_table') {
 713                     PMA_DBI_query($GLOBALS['sql_constraints_query']);
 714                 }
 715                 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
 716                 if ($mode == 'one_table') {
 717                     unset($GLOBALS['sql_constraints_query']);
 718                 }
 719             }
 720         } else {
 721             $GLOBALS['sql_query'] = '';
 722         }
 723
 724         // Copy the data unless this is a VIEW
 725         if (($what == 'data' || $what == 'dataonly') && ! PMA_Table::_isView($target_db,$target_table)) {
 726             $sql_insert_data =
 727                 'INSERT INTO ' . $target . ' SELECT * FROM ' . $source;
 728             PMA_DBI_query($sql_insert_data);
 729             $GLOBALS['sql_query']      .= "\n\n" . $sql_insert_data . ';';
 730         }
 731
 732         require_once './libraries/relation.lib.php';
 733         $GLOBALS['cfgRelation'] = PMA_getRelationsParam();
 734
 735         // Drops old table if the user has requested to move it
 736         if ($move) {
 737
 738             // This could avoid some problems with replicated databases, when
 739             // moving table from replicated one to not replicated one
 740             PMA_DBI_select_db($source_db);
 741
 742             if (PMA_Table::_isView($source_db,$source_table)) {
 743                 $sql_drop_query = 'DROP VIEW';
 744             } else {
 745                 $sql_drop_query = 'DROP TABLE';
 746             }
 747             $sql_drop_query .= ' ' . $source;
 748             PMA_DBI_query($sql_drop_query);
 749
 750             // garvin: Move old entries from PMA-DBs to new table
 751             if ($GLOBALS['cfgRelation']['commwork']) {
 752                 $remove_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info'])
 753                               . ' SET     table_name = \'' . PMA_sqlAddslashes($target_table) . '\', '
 754                               . '        db_name    = \'' . PMA_sqlAddslashes($target_db) . '\''
 755                               . ' WHERE db_name  = \'' . PMA_sqlAddslashes($source_db) . '\''
 756                               . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
 757                 PMA_query_as_cu($remove_query);
 758                 unset($remove_query);
 759             }
 760
 761             // garvin: updating bookmarks is not possible since only a single table is moved,
 762             // and not the whole DB.
 763
 764             if ($GLOBALS['cfgRelation']['displaywork']) {
 765                 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_info'])
 766                                 . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\', '
 767                                 . '         table_name = \'' . PMA_sqlAddslashes($target_table) . '\''
 768                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($source_db) . '\''
 769                                 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
 770                 PMA_query_as_cu($table_query);
 771                 unset($table_query);
 772             }
 773
 774             if ($GLOBALS['cfgRelation']['relwork']) {
 775                 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['relation'])
 776                                 . ' SET     foreign_table = \'' . PMA_sqlAddslashes($target_table) . '\','
 777                                 . '         foreign_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 778                                 . ' WHERE foreign_db  = \'' . PMA_sqlAddslashes($source_db) . '\''
 779                                 . ' AND foreign_table = \'' . PMA_sqlAddslashes($source_table) . '\'';
 780                 PMA_query_as_cu($table_query);
 781                 unset($table_query);
 782
 783                 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['relation'])
 784                                 . ' SET     master_table = \'' . PMA_sqlAddslashes($target_table) . '\','
 785                                 . '         master_db = \'' . PMA_sqlAddslashes($target_db) . '\''
 786                                 . ' WHERE master_db  = \'' . PMA_sqlAddslashes($source_db) . '\''
 787                                 . ' AND master_table = \'' . PMA_sqlAddslashes($source_table) . '\'';
 788                 PMA_query_as_cu($table_query);
 789                 unset($table_query);
 790             }
 791
 792             /**
 793              * @todo garvin: Can't get moving PDFs the right way. The page numbers
 794              * always get screwed up independently from duplication because the
 795              * numbers do not seem to be stored on a per-database basis. Would
 796              * the author of pdf support please have a look at it?
 797              */
 798
 799             if ($GLOBALS['cfgRelation']['pdfwork']) {
 800                 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_coords'])
 801                                 . ' SET     table_name = \'' . PMA_sqlAddslashes($target_table) . '\','
 802                                 . '         db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 803                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($source_db) . '\''
 804                                 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
 805                 PMA_query_as_cu($table_query);
 806                 unset($table_query);
 807                 /*
 808                 $pdf_query = 'SELECT pdf_page_number '
 809                            . ' FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_coords'])
 810                            . ' WHERE db_name  = \'' . PMA_sqlAddslashes($target_db) . '\''
 811                            . ' AND table_name = \'' . PMA_sqlAddslashes($target_table) . '\'';
 812                 $pdf_rs = PMA_query_as_cu($pdf_query);
 813
 814                 while ($pdf_copy_row = PMA_DBI_fetch_assoc($pdf_rs)) {
 815                     $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['pdf_pages'])
 816                                     . ' SET     db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 817                                     . ' WHERE db_name  = \'' . PMA_sqlAddslashes($source_db) . '\''
 818                                     . ' AND page_nr = \'' . PMA_sqlAddslashes($pdf_copy_row['pdf_page_number']) . '\'';
 819                     $tb_rs    = PMA_query_as_cu($table_query);
 820                     unset($table_query);
 821                     unset($tb_rs);
 822                 }
 823                 */
 824             }
 825
 826             if ($GLOBALS['cfgRelation']['designerwork']) {
 827                 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['designer_coords'])
 828                                 . ' SET     table_name = \'' . PMA_sqlAddslashes($target_table) . '\','
 829                                 . '         db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
 830                                 . ' WHERE db_name  = \'' . PMA_sqlAddslashes($source_db) . '\''
 831                                 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
 832                 PMA_query_as_cu($table_query);
 833                 unset($table_query);
 834             }
 835             $GLOBALS['sql_query']      .= "\n\n" . $sql_drop_query . ';';
 836         // end if ($move)
 837         } else {
 838             // we are copying
 839             // garvin: Create new entries as duplicates from old PMA DBs
 840             if ($what != 'dataonly' && !isset($maintain_relations)) {
 841                 if ($GLOBALS['cfgRelation']['commwork']) {
 842                     // Get all comments and MIME-Types for current table
 843                     $comments_copy_query = 'SELECT
 844                                                 column_name, ' . PMA_backquote('comment') . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . '
 845                                             FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info']) . '
 846                                             WHERE
 847                                                 db_name = \'' . PMA_sqlAddslashes($source_db) . '\' AND
 848                                                 table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
 849                     $comments_copy_rs    = PMA_query_as_cu($comments_copy_query);
 850
 851                     // Write every comment as new copied entry. [MIME]
 852                     while ($comments_copy_row = PMA_DBI_fetch_assoc($comments_copy_rs)) {
 853                         $new_comment_query = 'REPLACE INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info'])
 854                                     . ' (db_name, table_name, column_name, ' . PMA_backquote('comment') . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ') '
 855                                     . ' VALUES('
 856                                     . '\'' . PMA_sqlAddslashes($target_db) . '\','
 857                                     . '\'' . PMA_sqlAddslashes($target_table) . '\','
 858                                     . '\'' . PMA_sqlAddslashes($comments_copy_row['column_name']) . '\''
 859                                     . ($GLOBALS['cfgRelation']['mimework'] ? ',\'' . PMA_sqlAddslashes($comments_copy_row['comment']) . '\','
 860                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['mimetype']) . '\','
 861                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation']) . '\','
 862                                             . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation_options']) . '\'' : '')
 863                                     . ')';
 864                         PMA_query_as_cu($new_comment_query);
 865                     } // end while
 866                     PMA_DBI_free_result($comments_copy_rs);
 867                     unset($comments_copy_rs);
 868                 }
 869
 870                 // duplicating the bookmarks must not be done here, but
 871                 // just once per db
 872
 873                 $get_fields = array('display_field');
 874                 $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
 875                 $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
 876                 PMA_Table::duplicateInfo('displaywork', 'table_info', $get_fields, $where_fields, $new_fields);
 877
 878
 879                 /**
 880                  * @todo revise this code when we support cross-db relations
 881                  */
 882                 $get_fields = array('master_field', 'foreign_table', 'foreign_field');
 883                 $where_fields = array('master_db' => $source_db, 'master_table' => $source_table);
 884                 $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'master_table' => $target_table);
 885                 PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 886
 887
 888                 $get_fields = array('foreign_field', 'master_table', 'master_field');
 889                 $where_fields = array('foreign_db' => $source_db, 'foreign_table' => $source_table);
 890                 $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'foreign_table' => $target_table);
 891                 PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
 892
 893
 894                 $get_fields = array('x', 'y', 'v', 'h');
 895                 $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
 896                 $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
 897                 PMA_Table::duplicateInfo('designerwork', 'designer_coords', $get_fields, $where_fields, $new_fields);
 898
 899                 /**
 900                  * @todo garvin: Can't get duplicating PDFs the right way. The
 901                  * page numbers always get screwed up independently from
 902                  * duplication because the numbers do not seem to be stored on a
 903                  * per-database basis. Would the author of pdf support please
 904                  * have a look at it?
 905                  *
 906                 $get_fields = array('page_descr');
 907                 $where_fields = array('db_name' => $source_db);
 908                 $new_fields = array('db_name' => $target_db);
 909                 $last_id = PMA_Table::duplicateInfo('pdfwork', 'pdf_pages', $get_fields, $where_fields, $new_fields);
 910
 911                 if (isset($last_id) && $last_id >= 0) {
 912                     $get_fields = array('x', 'y');
 913                     $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
 914                     $new_fields = array('db_name' => $target_db, 'table_name' => $target_table, 'pdf_page_number' => $last_id);
 915                     PMA_Table::duplicateInfo('pdfwork', 'table_coords', $get_fields, $where_fields, $new_fields);
 916                 }
 917                  */
 918             }
 919         }
 920
 921     }
 922
 923     /**
 924      * checks if given name is a valid table name,
 925      * currently if not empty, trailing spaces, '.', '/' and '\'
 926      *
 927      * @todo    add check for valid chars in filename on current system/os
 928      * @see     http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
 929      * @param   string  $table_name name to check
 930      * @return  boolean whether the string is valid or not
 931      */
 932     function isValidName($table_name)
 933     {
 934         if ($table_name !== trim($table_name)) {
 935             // trailing spaces
 936             return false;
 937         }
 938
 939         if (! strlen($table_name)) {
 940             // zero length
 941             return false;
 942         }
 943
 944         if (preg_match('/[.\/\\\\]+/i', $table_name)) {
 945             // illegal char . / \
 946             return false;
 947         }
 948
 949         return true;
 950     }
 951
 952     /**
 953      * renames table
 954      *
 955      * @param   string  new table name
 956      * @param   string  new database name
 957      * @return  boolean success
 958      */
 959     function rename($new_name, $new_db = null)
 960     {
 961         if (null !== $new_db && $new_db !== $this->getDbName()) {
 962             // Ensure the target is valid
 963             if (! $GLOBALS['PMA_List_Database']->exists($new_db)) {
 964                 $this->errors[] = $GLOBALS['strInvalidDatabase'] . ': ' . $new_db;
 965                 return false;
 966             }
 967         } else {
 968             $new_db = $this->getDbName();
 969         }
 970
 971         $new_table = new PMA_Table($new_name, $new_db);
 972
 973         if ($this->getFullName() === $new_table->getFullName()) {
 974             return true;
 975         }
 976
 977         if (! PMA_Table::isValidName($new_name)) {
 978             $this->errors[] = $GLOBALS['strInvalidTableName'] . ': ' . $new_table->getFullName();
 979             return false;
 980         }
 981
 982         $GLOBALS['sql_query'] = '
 983             RENAME TABLE ' . $this->getFullName(true) . '
 984                       TO ' . $new_table->getFullName(true) . ';';
 985         if (! PMA_DBI_query($GLOBALS['sql_query'])) {
 986             $this->errors[] = sprintf($GLOBALS['strErrorRenamingTable'], $this->getFullName(), $new_table->getFullName());
 987             return false;
 988         }
 989
 990         $old_name = $this->getName();
 991         $old_db = $this->getDbName();
 992         $this->setName($new_name);
 993         $this->setDbName($new_db);
 994
 995         /**
 996          * @todo move into extra function PMA_Relation::renameTable($new_name, $old_name, $new_db, $old_db)
 997          */
 998         // garvin: Move old entries from comments to new table
 999         require_once './libraries/relation.lib.php';
1000         $GLOBALS['cfgRelation'] = PMA_getRelationsParam();
1001         if ($GLOBALS['cfgRelation']['commwork']) {
1002             $remove_query = '
1003                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1004                     . PMA_backquote($GLOBALS['cfgRelation']['column_info']) . '
1005                    SET `db_name`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1006                        `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1007                  WHERE `db_name`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1008                    AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1009             PMA_query_as_cu($remove_query);
1010             unset($remove_query);
1011         }
1012
1013         if ($GLOBALS['cfgRelation']['displaywork']) {
1014             $table_query = '
1015                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1016                     . PMA_backquote($GLOBALS['cfgRelation']['table_info']) . '
1017                    SET `db_name`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1018                        `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1019                  WHERE `db_name`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1020                    AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1021             PMA_query_as_cu($table_query);
1022             unset($table_query);
1023         }
1024
1025         if ($GLOBALS['cfgRelation']['relwork']) {
1026             $table_query = '
1027                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1028                     . PMA_backquote($GLOBALS['cfgRelation']['relation']) . '
1029                    SET `foreign_db`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1030                        `foreign_table` = \'' . PMA_sqlAddslashes($new_name) . '\'
1031                  WHERE `foreign_db`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1032                    AND `foreign_table` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1033             PMA_query_as_cu($table_query);
1034
1035             $table_query = '
1036                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1037                     . PMA_backquote($GLOBALS['cfgRelation']['relation']) . '
1038                    SET `master_db`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1039                        `master_table` = \'' . PMA_sqlAddslashes($new_name) . '\'
1040                  WHERE `master_db`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1041                    AND `master_table` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1042             PMA_query_as_cu($table_query);
1043             unset($table_query);
1044         }
1045
1046         if ($GLOBALS['cfgRelation']['pdfwork']) {
1047             $table_query = '
1048                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1049                     . PMA_backquote($GLOBALS['cfgRelation']['table_coords']) . '
1050                    SET `db_name`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1051                        `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1052                  WHERE `db_name`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1053                    AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1054             PMA_query_as_cu($table_query);
1055             unset($table_query);
1056         }
1057
1058         if ($GLOBALS['cfgRelation']['designerwork']) {
1059             $table_query = '
1060                 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1061                     . PMA_backquote($GLOBALS['cfgRelation']['designer_coords']) . '
1062                    SET `db_name`    = \'' . PMA_sqlAddslashes($new_db) . '\',
1063                        `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1064                  WHERE `db_name`    = \'' . PMA_sqlAddslashes($old_db) . '\'
1065                    AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1066             PMA_query_as_cu($table_query);
1067             unset($table_query);
1068         }
1069
1070         $this->messages[] = sprintf($GLOBALS['strRenameTableOK'],
1071             htmlspecialchars($old_name), htmlspecialchars($new_name));
1072         return true;
1073     }
1074 }
1075 ?>