Translation update done using Pootle.
[phpmyadmin-themes.git] / libraries / Table.class.php
bloba29900e7b594196a04cfbdd802ed303fcc1d1e6f
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
5 * @package phpMyAdmin
6 */
8 /**
9 * @todo make use of PMA_Message and PMA_Error
10 * @package phpMyAdmin
12 class PMA_Table
15 static $cache = array();
17 /**
18 * @var string table name
20 var $name = '';
22 /**
23 * @var string database name
25 var $db_name = '';
27 /**
28 * @var string engine (innodb, myisam, bdb, ...)
30 var $engine = '';
32 /**
33 * @var string type (view, base table, system view)
35 var $type = '';
37 /**
38 * @var array settings
40 var $settings = array();
42 /**
43 * @var array errors occured
45 var $errors = array();
47 /**
48 * @var array messages
50 var $messages = array();
52 /**
53 * Constructor
55 * @param string $table_name table name
56 * @param string $db_name database name
58 function __construct($table_name, $db_name)
60 $this->setName($table_name);
61 $this->setDbName($db_name);
64 /**
65 * @see PMA_Table::getName()
67 function __toString()
69 return $this->getName();
72 function getLastError()
74 return end($this->errors);
77 function getLastMessage()
79 return end($this->messages);
82 /**
83 * sets table name
85 * @uses $this->name to set it
86 * @param string $table_name new table name
88 function setName($table_name)
90 $this->name = $table_name;
93 /**
94 * returns table name
96 * @uses $this->name as return value
97 * @param boolean whether to quote name with backticks ``
98 * @return string table name
100 function getName($backquoted = false)
102 if ($backquoted) {
103 return PMA_backquote($this->name);
105 return $this->name;
109 * sets database name for this table
111 * @uses $this->db_name to set it
112 * @param string $db_name
114 function setDbName($db_name)
116 $this->db_name = $db_name;
120 * returns database name for this table
122 * @uses $this->db_name as return value
123 * @param boolean whether to quote name with backticks ``
124 * @return string database name for this table
126 function getDbName($backquoted = false)
128 if ($backquoted) {
129 return PMA_backquote($this->db_name);
131 return $this->db_name;
135 * returns full name for table, including database name
137 * @param boolean whether to quote name with backticks ``
139 function getFullName($backquoted = false)
141 return $this->getDbName($backquoted) . '.' . $this->getName($backquoted);
144 static public function isView($db = null, $table = null)
146 if (strlen($db) && strlen($table)) {
147 return PMA_Table::_isView($db, $table);
150 if (isset($this) && strpos($this->get('TABLE TYPE'), 'VIEW')) {
151 return true;
154 return false;
158 * sets given $value for given $param
160 * @uses $this->settings to add or change value
161 * @param string param name
162 * @param mixed param value
164 function set($param, $value)
166 $this->settings[$param] = $value;
170 * returns value for given setting/param
172 * @uses $this->settings to return value
173 * @param string name for value to return
174 * @return mixed value for $param
176 function get($param)
178 if (isset($this->settings[$param])) {
179 return $this->settings[$param];
182 return null;
186 * loads structure data
187 * (this function is work in progress? not yet used)
189 function loadStructure()
191 $table_info = PMA_DBI_get_tables_full($this->getDbName(), $this->getName());
193 if (false === $table_info) {
194 return false;
197 $this->settings = $table_info;
199 if ($this->get('TABLE_ROWS') === null) {
200 $this->set('TABLE_ROWS', PMA_Table::countRecords($this->getDbName(),
201 $this->getName(), true));
204 $create_options = explode(' ', $this->get('TABLE_ROWS'));
206 // export create options by its name as variables into gloabel namespace
207 // f.e. pack_keys=1 becomes available as $pack_keys with value of '1'
208 foreach ($create_options as $each_create_option) {
209 $each_create_option = explode('=', $each_create_option);
210 if (isset($each_create_option[1])) {
211 $this->set($$each_create_option[0], $each_create_option[1]);
217 * Checks if this "table" is a view
219 * @deprecated
220 * @todo see what we could do with the possible existence of $table_is_view
221 * @param string the database name
222 * @param string the table name
224 * @return boolean whether this is a view
226 * @access public
228 static protected function _isView($db, $table)
230 // maybe we already know if the table is a view
231 if (isset($GLOBALS['tbl_is_view']) && $GLOBALS['tbl_is_view']) {
232 return true;
235 // Since phpMyAdmin 3.2 the field TABLE_TYPE is properly filled by PMA_DBI_get_tables_full()
236 $type = PMA_Table::sGetStatusInfo($db, $table, 'TABLE_TYPE');
237 return $type == 'VIEW';
241 * Checks if this is a merge table
243 * If the ENGINE of the table is MERGE or MRG_MYISAM (alias), this is a merge table.
245 * @param string the database name
246 * @param string the table name
247 * @return boolean true if it is a merge table
248 * @access public
250 static public function isMerge($db = null, $table = null)
252 // if called static, with parameters
253 if (! empty($db) && ! empty($table)) {
254 $engine = PMA_Table::sGetStatusInfo($db, $table, 'ENGINE', null, true);
256 // if called as an object
257 // does not work yet, because $this->settings[] is not filled correctly
258 else if (! empty($this)) {
259 $engine = $this->get('ENGINE');
262 return (! empty($engine) && ((strtoupper($engine) == 'MERGE') || (strtoupper($engine) == 'MRG_MYISAM')));
265 static public function sGetToolTip($db, $table)
267 return PMA_Table::sGetStatusInfo($db, $table, 'Comment')
268 . ' (' . PMA_Table::countRecords($db, $table) . ')';
272 * Returns full table status info, or specific if $info provided
274 * this info is collected from information_schema
276 * @todo PMA_DBI_get_tables_full needs to be merged somehow into this class or at least better documented
277 * @param string $db
278 * @param string $table
279 * @param string $info
280 * @param boolean $force_read
281 * @param boolean if true, disables error message
282 * @return mixed
284 static public function sGetStatusInfo($db, $table, $info = null, $force_read = false, $disable_error = false)
286 if (! isset(PMA_Table::$cache[$db][$table]) || $force_read) {
287 PMA_DBI_get_tables_full($db, $table);
290 if (! isset(PMA_Table::$cache[$db][$table])) {
291 // happens when we enter the table creation dialog
292 // or when we really did not get any status info, for example
293 // when $table == 'TABLE_NAMES' after the user tried SHOW TABLES
294 return '';
297 if (null === $info) {
298 return PMA_Table::$cache[$db][$table];
301 if (! isset(PMA_Table::$cache[$db][$table][$info])) {
302 if (! $disable_error) {
303 trigger_error('unknown table status: ' . $info, E_USER_WARNING);
305 return false;
308 return PMA_Table::$cache[$db][$table][$info];
312 * generates column/field specification for ALTER or CREATE TABLE syntax
314 * @todo move into class PMA_Column
315 * @todo on the interface, some js to clear the default value when the default
316 * current_timestamp is checked
317 * @static
318 * @param string $name name
319 * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
320 * @param string $length length ('2', '5,2', '', ...)
321 * @param string $attribute
322 * @param string $collation
323 * @param string $null with 'NULL' or 'NOT NULL'
324 * @param string $default_type whether default is CURRENT_TIMESTAMP,
325 * NULL, NONE, USER_DEFINED
326 * @param boolean $default_value default value for USER_DEFINED default type
327 * @param string $extra 'AUTO_INCREMENT'
328 * @param string $comment field comment
329 * @param array &$field_primary list of fields for PRIMARY KEY
330 * @param string $index
331 * @return string field specification
333 static function generateFieldSpec($name, $type, $length = '', $attribute = '',
334 $collation = '', $null = false, $default_type = 'USER_DEFINED',
335 $default_value = '', $extra = '', $comment = '',
336 &$field_primary, $index, $default_orig = false)
339 $is_timestamp = strpos(' ' . strtoupper($type), 'TIMESTAMP') == 1;
342 * @todo include db-name
344 $query = PMA_backquote($name) . ' ' . $type;
346 if ($length != ''
347 && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT)$@i', $type)) {
348 $query .= '(' . $length . ')';
351 if ($attribute != '') {
352 $query .= ' ' . $attribute;
355 if (!empty($collation) && $collation != 'NULL'
356 && preg_match('@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', $type)) {
357 $query .= PMA_generateCharsetQueryPart($collation);
360 if ($null !== false) {
361 if ($null == 'NULL') {
362 $query .= ' NULL';
363 } else {
364 $query .= ' NOT NULL';
368 switch ($default_type) {
369 case 'USER_DEFINED' :
370 if ($is_timestamp && $default_value === '0') {
371 // a TIMESTAMP does not accept DEFAULT '0'
372 // but DEFAULT 0 works
373 $query .= ' DEFAULT 0';
374 } elseif ($type == 'BIT') {
375 $query .= ' DEFAULT b\'' . preg_replace('/[^01]/', '0', $default_value) . '\'';
376 } else {
377 $query .= ' DEFAULT \'' . PMA_sqlAddslashes($default_value) . '\'';
379 break;
380 case 'NULL' :
381 case 'CURRENT_TIMESTAMP' :
382 $query .= ' DEFAULT ' . $default_type;
383 break;
384 case 'NONE' :
385 default :
386 break;
389 if (!empty($extra)) {
390 $query .= ' ' . $extra;
391 // Force an auto_increment field to be part of the primary key
392 // even if user did not tick the PK box;
393 if ($extra == 'AUTO_INCREMENT') {
394 $primary_cnt = count($field_primary);
395 if (1 == $primary_cnt) {
396 for ($j = 0; $j < $primary_cnt && $field_primary[$j] != $index; $j++) {
397 //void
399 if (isset($field_primary[$j]) && $field_primary[$j] == $index) {
400 $query .= ' PRIMARY KEY';
401 unset($field_primary[$j]);
403 // but the PK could contain other columns so do not append
404 // a PRIMARY KEY clause, just add a member to $field_primary
405 } else {
406 $found_in_pk = false;
407 for ($j = 0; $j < $primary_cnt; $j++) {
408 if ($field_primary[$j] == $index) {
409 $found_in_pk = true;
410 break;
412 } // end for
413 if (! $found_in_pk) {
414 $field_primary[] = $index;
417 } // end if (auto_increment)
419 if (!empty($comment)) {
420 $query .= " COMMENT '" . PMA_sqlAddslashes($comment) . "'";
422 return $query;
423 } // end function
426 * Counts and returns (or displays) the number of records in a table
428 * Revision 13 July 2001: Patch for limiting dump size from
429 * vinay@sanisoft.com & girish@sanisoft.com
431 * @param string the current database name
432 * @param string the current table name
433 * @param boolean whether to force an exact count
435 * @return mixed the number of records if "retain" param is true,
436 * otherwise true
438 * @access public
440 static public function countRecords($db, $table, $force_exact = false, $is_view = null)
442 if (isset(PMA_Table::$cache[$db][$table]['ExactRows'])) {
443 $row_count = PMA_Table::$cache[$db][$table]['ExactRows'];
444 } else {
445 $row_count = false;
447 if (null === $is_view) {
448 $is_view = PMA_Table::isView($db, $table);
451 if (! $force_exact) {
452 if (! isset(PMA_Table::$cache[$db][$table]['Rows']) && ! $is_view) {
453 $tmp_tables = PMA_DBI_get_tables_full($db, $table);
454 if (isset($tmp_tables[$table])) {
455 PMA_Table::$cache[$db][$table] = $tmp_tables[$table];
458 if (isset(PMA_Table::$cache[$db][$table]['Rows'])) {
459 $row_count = PMA_Table::$cache[$db][$table]['Rows'];
460 } else {
461 $row_count = false;
465 // for a VIEW, $row_count is always false at this point
466 if (false === $row_count || $row_count < $GLOBALS['cfg']['MaxExactCount']) {
467 if (! $is_view) {
468 $row_count = PMA_DBI_fetch_value(
469 'SELECT COUNT(*) FROM ' . PMA_backquote($db) . '.'
470 . PMA_backquote($table));
471 } else {
472 // For complex views, even trying to get a partial record
473 // count could bring down a server, so we offer an
474 // alternative: setting MaxExactCountViews to 0 will bypass
475 // completely the record counting for views
477 if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
478 $row_count = 0;
479 } else {
480 // Counting all rows of a VIEW could be too long, so use
481 // a LIMIT clause.
482 // Use try_query because it can fail (when a VIEW is
483 // based on a table that no longer exists)
484 $result = PMA_DBI_try_query(
485 'SELECT 1 FROM ' . PMA_backquote($db) . '.'
486 . PMA_backquote($table) . ' LIMIT '
487 . $GLOBALS['cfg']['MaxExactCountViews'],
488 null, PMA_DBI_QUERY_STORE);
489 if (!PMA_DBI_getError()) {
490 $row_count = PMA_DBI_num_rows($result);
491 PMA_DBI_free_result($result);
495 PMA_Table::$cache[$db][$table]['ExactRows'] = $row_count;
499 return $row_count;
500 } // end of the 'PMA_Table::countRecords()' function
503 * @see PMA_Table::generateFieldSpec()
505 static public function generateAlter($oldcol, $newcol, $type, $length,
506 $attribute, $collation, $null, $default_type, $default_value,
507 $extra, $comment = '', &$field_primary, $index, $default_orig)
509 return PMA_backquote($oldcol) . ' '
510 . PMA_Table::generateFieldSpec($newcol, $type, $length, $attribute,
511 $collation, $null, $default_type, $default_value, $extra,
512 $comment, $field_primary, $index, $default_orig);
513 } // end function
516 * Inserts existing entries in a PMA_* table by reading a value from an old entry
518 * @param string The array index, which Relation feature to check
519 * ('relwork', 'commwork', ...)
520 * @param string The array index, which PMA-table to update
521 * ('bookmark', 'relation', ...)
522 * @param array Which fields will be SELECT'ed from the old entry
523 * @param array Which fields will be used for the WHERE query
524 * (array('FIELDNAME' => 'FIELDVALUE'))
525 * @param array Which fields will be used as new VALUES. These are the important
526 * keys which differ from the old entry.
527 * (array('FIELDNAME' => 'NEW FIELDVALUE'))
529 * @global string relation variable
532 static public function duplicateInfo($work, $pma_table, $get_fields, $where_fields,
533 $new_fields)
535 $last_id = -1;
537 if (isset($GLOBALS['cfgRelation']) && $GLOBALS['cfgRelation'][$work]) {
538 $select_parts = array();
539 $row_fields = array();
540 foreach ($get_fields as $get_field) {
541 $select_parts[] = PMA_backquote($get_field);
542 $row_fields[$get_field] = 'cc';
545 $where_parts = array();
546 foreach ($where_fields as $_where => $_value) {
547 $where_parts[] = PMA_backquote($_where) . ' = \''
548 . PMA_sqlAddslashes($_value) . '\'';
551 $new_parts = array();
552 $new_value_parts = array();
553 foreach ($new_fields as $_where => $_value) {
554 $new_parts[] = PMA_backquote($_where);
555 $new_value_parts[] = PMA_sqlAddslashes($_value);
558 $table_copy_query = '
559 SELECT ' . implode(', ', $select_parts) . '
560 FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
561 . PMA_backquote($GLOBALS['cfgRelation'][$pma_table]) . '
562 WHERE ' . implode(' AND ', $where_parts);
564 // must use PMA_DBI_QUERY_STORE here, since we execute another
565 // query inside the loop
566 $table_copy_rs = PMA_query_as_controluser($table_copy_query, true,
567 PMA_DBI_QUERY_STORE);
569 while ($table_copy_row = @PMA_DBI_fetch_assoc($table_copy_rs)) {
570 $value_parts = array();
571 foreach ($table_copy_row as $_key => $_val) {
572 if (isset($row_fields[$_key]) && $row_fields[$_key] == 'cc') {
573 $value_parts[] = PMA_sqlAddslashes($_val);
577 $new_table_query = '
578 INSERT IGNORE INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db'])
579 . '.' . PMA_backquote($GLOBALS['cfgRelation'][$pma_table]) . '
580 (' . implode(', ', $select_parts) . ',
581 ' . implode(', ', $new_parts) . ')
582 VALUES
583 (\'' . implode('\', \'', $value_parts) . '\',
584 \'' . implode('\', \'', $new_value_parts) . '\')';
586 PMA_query_as_controluser($new_table_query);
587 $last_id = PMA_DBI_insert_id();
588 } // end while
590 PMA_DBI_free_result($table_copy_rs);
592 return $last_id;
595 return true;
596 } // end of 'PMA_Table::duplicateInfo()' function
600 * Copies or renames table
603 static public function moveCopy($source_db, $source_table, $target_db, $target_table, $what, $move, $mode)
605 global $err_url;
607 /* Try moving table directly */
608 if ($move && $what == 'data') {
609 $tbl = new PMA_Table($source_table, $source_db);
610 $result = $tbl->rename($target_table, $target_db, PMA_Table::isView($source_db, $source_table));
611 if ($result) {
612 $GLOBALS['message'] = $tbl->getLastMessage();
613 return true;
617 // set export settings we need
618 $GLOBALS['sql_backquotes'] = 1;
619 $GLOBALS['asfile'] = 1;
621 // Ensure the target is valid
622 if (! $GLOBALS['pma']->databases->exists($source_db, $target_db)) {
623 if (! $GLOBALS['pma']->databases->exists($source_db)) {
624 $GLOBALS['message'] = PMA_Message::rawError('source database `'
625 . htmlspecialchars($source_db) . '` not found');
627 if (! $GLOBALS['pma']->databases->exists($target_db)) {
628 $GLOBALS['message'] = PMA_Message::rawError('target database `'
629 . htmlspecialchars($target_db) . '` not found');
631 return false;
634 $source = PMA_backquote($source_db) . '.' . PMA_backquote($source_table);
635 if (! isset($target_db) || ! strlen($target_db)) {
636 $target_db = $source_db;
639 // Doing a select_db could avoid some problems with replicated databases,
640 // when moving table from replicated one to not replicated one
641 PMA_DBI_select_db($target_db);
643 $target = PMA_backquote($target_db) . '.' . PMA_backquote($target_table);
645 // do not create the table if dataonly
646 if ($what != 'dataonly') {
647 require_once './libraries/export/sql.php';
649 $no_constraints_comments = true;
650 $GLOBALS['sql_constraints_query'] = '';
652 $sql_structure = PMA_getTableDef($source_db, $source_table, "\n", $err_url, false, false);
653 unset($no_constraints_comments);
654 $parsed_sql = PMA_SQP_parse($sql_structure);
655 $analyzed_sql = PMA_SQP_analyze($parsed_sql);
656 $i = 0;
657 if (empty($analyzed_sql[0]['create_table_fields'])) {
658 // this is not a CREATE TABLE, so find the first VIEW
659 $target_for_view = PMA_backquote($target_db);
660 while (true) {
661 if ($parsed_sql[$i]['type'] == 'alpha_reservedWord' && $parsed_sql[$i]['data'] == 'VIEW') {
662 break;
664 $i++;
667 unset($analyzed_sql);
668 $server_sql_mode = PMA_DBI_fetch_value("SHOW VARIABLES LIKE 'sql_mode'", 0, 1);
669 // ANSI_QUOTES might be a subset of sql_mode, for example
670 // REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
671 if (false !== strpos($server_sql_mode, 'ANSI_QUOTES')) {
672 $table_delimiter = 'quote_double';
673 } else {
674 $table_delimiter = 'quote_backtick';
676 unset($server_sql_mode);
678 /* Find table name in query and replace it */
679 while ($parsed_sql[$i]['type'] != $table_delimiter) {
680 $i++;
683 /* no need to PMA_backquote() */
684 if (isset($target_for_view)) {
685 // this a view definition; we just found the first db name
686 // that follows DEFINER VIEW
687 // so change it for the new db name
688 $parsed_sql[$i]['data'] = $target_for_view;
689 // then we have to find all references to the source db
690 // and change them to the target db, ensuring we stay into
691 // the $parsed_sql limits
692 $last = $parsed_sql['len'] - 1;
693 $backquoted_source_db = PMA_backquote($source_db);
694 for (++$i; $i <= $last; $i++) {
695 if ($parsed_sql[$i]['type'] == $table_delimiter && $parsed_sql[$i]['data'] == $backquoted_source_db) {
696 $parsed_sql[$i]['data'] = $target_for_view;
699 unset($last,$backquoted_source_db);
700 } else {
701 $parsed_sql[$i]['data'] = $target;
704 /* Generate query back */
705 $sql_structure = PMA_SQP_formatHtml($parsed_sql, 'query_only');
706 // If table exists, and 'add drop table' is selected: Drop it!
707 $drop_query = '';
708 if (isset($GLOBALS['drop_if_exists'])
709 && $GLOBALS['drop_if_exists'] == 'true') {
710 if (PMA_Table::_isView($target_db,$target_table)) {
711 $drop_query = 'DROP VIEW';
712 } else {
713 $drop_query = 'DROP TABLE';
715 $drop_query .= ' IF EXISTS '
716 . PMA_backquote($target_db) . '.'
717 . PMA_backquote($target_table);
718 PMA_DBI_query($drop_query);
720 $GLOBALS['sql_query'] .= "\n" . $drop_query . ';';
722 // If an existing table gets deleted, maintain any
723 // entries for the PMA_* tables
724 $maintain_relations = true;
727 @PMA_DBI_query($sql_structure);
728 $GLOBALS['sql_query'] .= "\n" . $sql_structure . ';';
730 if (($move || isset($GLOBALS['add_constraints']))
731 && !empty($GLOBALS['sql_constraints_query'])) {
732 $parsed_sql = PMA_SQP_parse($GLOBALS['sql_constraints_query']);
733 $i = 0;
735 // find the first $table_delimiter, it must be the source table name
736 while ($parsed_sql[$i]['type'] != $table_delimiter) {
737 $i++;
738 // maybe someday we should guard against going over limit
739 //if ($i == $parsed_sql['len']) {
740 // break;
744 // replace it by the target table name, no need to PMA_backquote()
745 $parsed_sql[$i]['data'] = $target;
747 // now we must remove all $table_delimiter that follow a CONSTRAINT
748 // keyword, because a constraint name must be unique in a db
750 $cnt = $parsed_sql['len'] - 1;
752 for ($j = $i; $j < $cnt; $j++) {
753 if ($parsed_sql[$j]['type'] == 'alpha_reservedWord'
754 && strtoupper($parsed_sql[$j]['data']) == 'CONSTRAINT') {
755 if ($parsed_sql[$j+1]['type'] == $table_delimiter) {
756 $parsed_sql[$j+1]['data'] = '';
761 // Generate query back
762 $GLOBALS['sql_constraints_query'] = PMA_SQP_formatHtml($parsed_sql,
763 'query_only');
764 if ($mode == 'one_table') {
765 PMA_DBI_query($GLOBALS['sql_constraints_query']);
767 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
768 if ($mode == 'one_table') {
769 unset($GLOBALS['sql_constraints_query']);
772 } else {
773 $GLOBALS['sql_query'] = '';
776 // Copy the data unless this is a VIEW
777 if (($what == 'data' || $what == 'dataonly') && ! PMA_Table::_isView($target_db,$target_table)) {
778 $sql_insert_data =
779 'INSERT INTO ' . $target . ' SELECT * FROM ' . $source;
780 PMA_DBI_query($sql_insert_data);
781 $GLOBALS['sql_query'] .= "\n\n" . $sql_insert_data . ';';
784 $GLOBALS['cfgRelation'] = PMA_getRelationsParam();
786 // Drops old table if the user has requested to move it
787 if ($move) {
789 // This could avoid some problems with replicated databases, when
790 // moving table from replicated one to not replicated one
791 PMA_DBI_select_db($source_db);
793 if (PMA_Table::_isView($source_db,$source_table)) {
794 $sql_drop_query = 'DROP VIEW';
795 } else {
796 $sql_drop_query = 'DROP TABLE';
798 $sql_drop_query .= ' ' . $source;
799 PMA_DBI_query($sql_drop_query);
801 // Move old entries from PMA-DBs to new table
802 if ($GLOBALS['cfgRelation']['commwork']) {
803 $remove_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info'])
804 . ' SET table_name = \'' . PMA_sqlAddslashes($target_table) . '\', '
805 . ' db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
806 . ' WHERE db_name = \'' . PMA_sqlAddslashes($source_db) . '\''
807 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
808 PMA_query_as_controluser($remove_query);
809 unset($remove_query);
812 // updating bookmarks is not possible since only a single table is moved,
813 // and not the whole DB.
815 if ($GLOBALS['cfgRelation']['displaywork']) {
816 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_info'])
817 . ' SET db_name = \'' . PMA_sqlAddslashes($target_db) . '\', '
818 . ' table_name = \'' . PMA_sqlAddslashes($target_table) . '\''
819 . ' WHERE db_name = \'' . PMA_sqlAddslashes($source_db) . '\''
820 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
821 PMA_query_as_controluser($table_query);
822 unset($table_query);
825 if ($GLOBALS['cfgRelation']['relwork']) {
826 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['relation'])
827 . ' SET foreign_table = \'' . PMA_sqlAddslashes($target_table) . '\','
828 . ' foreign_db = \'' . PMA_sqlAddslashes($target_db) . '\''
829 . ' WHERE foreign_db = \'' . PMA_sqlAddslashes($source_db) . '\''
830 . ' AND foreign_table = \'' . PMA_sqlAddslashes($source_table) . '\'';
831 PMA_query_as_controluser($table_query);
832 unset($table_query);
834 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['relation'])
835 . ' SET master_table = \'' . PMA_sqlAddslashes($target_table) . '\','
836 . ' master_db = \'' . PMA_sqlAddslashes($target_db) . '\''
837 . ' WHERE master_db = \'' . PMA_sqlAddslashes($source_db) . '\''
838 . ' AND master_table = \'' . PMA_sqlAddslashes($source_table) . '\'';
839 PMA_query_as_controluser($table_query);
840 unset($table_query);
844 * @todo Can't get moving PDFs the right way. The page numbers
845 * always get screwed up independently from duplication because the
846 * numbers do not seem to be stored on a per-database basis. Would
847 * the author of pdf support please have a look at it?
850 if ($GLOBALS['cfgRelation']['pdfwork']) {
851 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_coords'])
852 . ' SET table_name = \'' . PMA_sqlAddslashes($target_table) . '\','
853 . ' db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
854 . ' WHERE db_name = \'' . PMA_sqlAddslashes($source_db) . '\''
855 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
856 PMA_query_as_controluser($table_query);
857 unset($table_query);
859 $pdf_query = 'SELECT pdf_page_number '
860 . ' FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['table_coords'])
861 . ' WHERE db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
862 . ' AND table_name = \'' . PMA_sqlAddslashes($target_table) . '\'';
863 $pdf_rs = PMA_query_as_controluser($pdf_query);
865 while ($pdf_copy_row = PMA_DBI_fetch_assoc($pdf_rs)) {
866 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['pdf_pages'])
867 . ' SET db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
868 . ' WHERE db_name = \'' . PMA_sqlAddslashes($source_db) . '\''
869 . ' AND page_nr = \'' . PMA_sqlAddslashes($pdf_copy_row['pdf_page_number']) . '\'';
870 $tb_rs = PMA_query_as_controluser($table_query);
871 unset($table_query);
872 unset($tb_rs);
877 if ($GLOBALS['cfgRelation']['designerwork']) {
878 $table_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['designer_coords'])
879 . ' SET table_name = \'' . PMA_sqlAddslashes($target_table) . '\','
880 . ' db_name = \'' . PMA_sqlAddslashes($target_db) . '\''
881 . ' WHERE db_name = \'' . PMA_sqlAddslashes($source_db) . '\''
882 . ' AND table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
883 PMA_query_as_controluser($table_query);
884 unset($table_query);
887 $GLOBALS['sql_query'] .= "\n\n" . $sql_drop_query . ';';
888 // end if ($move)
889 } else {
890 // we are copying
891 // Create new entries as duplicates from old PMA DBs
892 if ($what != 'dataonly' && !isset($maintain_relations)) {
893 if ($GLOBALS['cfgRelation']['commwork']) {
894 // Get all comments and MIME-Types for current table
895 $comments_copy_query = 'SELECT
896 column_name, ' . PMA_backquote('comment') . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . '
897 FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info']) . '
898 WHERE
899 db_name = \'' . PMA_sqlAddslashes($source_db) . '\' AND
900 table_name = \'' . PMA_sqlAddslashes($source_table) . '\'';
901 $comments_copy_rs = PMA_query_as_controluser($comments_copy_query);
903 // Write every comment as new copied entry. [MIME]
904 while ($comments_copy_row = PMA_DBI_fetch_assoc($comments_copy_rs)) {
905 $new_comment_query = 'REPLACE INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($GLOBALS['cfgRelation']['column_info'])
906 . ' (db_name, table_name, column_name, ' . PMA_backquote('comment') . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ') '
907 . ' VALUES('
908 . '\'' . PMA_sqlAddslashes($target_db) . '\','
909 . '\'' . PMA_sqlAddslashes($target_table) . '\','
910 . '\'' . PMA_sqlAddslashes($comments_copy_row['column_name']) . '\''
911 . ($GLOBALS['cfgRelation']['mimework'] ? ',\'' . PMA_sqlAddslashes($comments_copy_row['comment']) . '\','
912 . '\'' . PMA_sqlAddslashes($comments_copy_row['mimetype']) . '\','
913 . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation']) . '\','
914 . '\'' . PMA_sqlAddslashes($comments_copy_row['transformation_options']) . '\'' : '')
915 . ')';
916 PMA_query_as_controluser($new_comment_query);
917 } // end while
918 PMA_DBI_free_result($comments_copy_rs);
919 unset($comments_copy_rs);
922 // duplicating the bookmarks must not be done here, but
923 // just once per db
925 $get_fields = array('display_field');
926 $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
927 $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
928 PMA_Table::duplicateInfo('displaywork', 'table_info', $get_fields, $where_fields, $new_fields);
932 * @todo revise this code when we support cross-db relations
934 $get_fields = array('master_field', 'foreign_table', 'foreign_field');
935 $where_fields = array('master_db' => $source_db, 'master_table' => $source_table);
936 $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'master_table' => $target_table);
937 PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
940 $get_fields = array('foreign_field', 'master_table', 'master_field');
941 $where_fields = array('foreign_db' => $source_db, 'foreign_table' => $source_table);
942 $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'foreign_table' => $target_table);
943 PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
946 $get_fields = array('x', 'y', 'v', 'h');
947 $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
948 $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
949 PMA_Table::duplicateInfo('designerwork', 'designer_coords', $get_fields, $where_fields, $new_fields);
952 * @todo Can't get duplicating PDFs the right way. The
953 * page numbers always get screwed up independently from
954 * duplication because the numbers do not seem to be stored on a
955 * per-database basis. Would the author of pdf support please
956 * have a look at it?
958 $get_fields = array('page_descr');
959 $where_fields = array('db_name' => $source_db);
960 $new_fields = array('db_name' => $target_db);
961 $last_id = PMA_Table::duplicateInfo('pdfwork', 'pdf_pages', $get_fields, $where_fields, $new_fields);
963 if (isset($last_id) && $last_id >= 0) {
964 $get_fields = array('x', 'y');
965 $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
966 $new_fields = array('db_name' => $target_db, 'table_name' => $target_table, 'pdf_page_number' => $last_id);
967 PMA_Table::duplicateInfo('pdfwork', 'table_coords', $get_fields, $where_fields, $new_fields);
972 return true;
976 * checks if given name is a valid table name,
977 * currently if not empty, trailing spaces, '.', '/' and '\'
979 * @todo add check for valid chars in filename on current system/os
980 * @see http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
981 * @param string $table_name name to check
982 * @return boolean whether the string is valid or not
984 function isValidName($table_name)
986 if ($table_name !== trim($table_name)) {
987 // trailing spaces
988 return false;
991 if (! strlen($table_name)) {
992 // zero length
993 return false;
996 if (preg_match('/[.\/\\\\]+/i', $table_name)) {
997 // illegal char . / \
998 return false;
1001 return true;
1005 * renames table
1007 * @param string new table name
1008 * @param string new database name
1009 * @param boolean is this for a VIEW rename?
1010 * @return boolean success
1012 function rename($new_name, $new_db = null, $is_view = false)
1014 if (null !== $new_db && $new_db !== $this->getDbName()) {
1015 // Ensure the target is valid
1016 if (! $GLOBALS['pma']->databases->exists($new_db)) {
1017 $this->errors[] = __('Invalid database') . ': ' . $new_db;
1018 return false;
1020 } else {
1021 $new_db = $this->getDbName();
1024 $new_table = new PMA_Table($new_name, $new_db);
1026 if ($this->getFullName() === $new_table->getFullName()) {
1027 return true;
1030 if (! PMA_Table::isValidName($new_name)) {
1031 $this->errors[] = __('Invalid table name') . ': ' . $new_table->getFullName();
1032 return false;
1035 if (! $is_view) {
1036 $GLOBALS['sql_query'] = '
1037 RENAME TABLE ' . $this->getFullName(true) . '
1038 TO ' . $new_table->getFullName(true) . ';';
1039 } else {
1040 $GLOBALS['sql_query'] = '
1041 ALTER TABLE ' . $this->getFullName(true) . '
1042 RENAME ' . $new_table->getFullName(true) . ';';
1044 // I don't think a specific error message for views is necessary
1045 if (! PMA_DBI_query($GLOBALS['sql_query'])) {
1046 $this->errors[] = sprintf(__('Error renaming table %1$s to %2$s'), $this->getFullName(), $new_table->getFullName());
1047 return false;
1050 $old_name = $this->getName();
1051 $old_db = $this->getDbName();
1052 $this->setName($new_name);
1053 $this->setDbName($new_db);
1056 * @todo move into extra function PMA_Relation::renameTable($new_name, $old_name, $new_db, $old_db)
1058 // Move old entries from comments to new table
1059 $GLOBALS['cfgRelation'] = PMA_getRelationsParam();
1060 if ($GLOBALS['cfgRelation']['commwork']) {
1061 $remove_query = '
1062 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1063 . PMA_backquote($GLOBALS['cfgRelation']['column_info']) . '
1064 SET `db_name` = \'' . PMA_sqlAddslashes($new_db) . '\',
1065 `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1066 WHERE `db_name` = \'' . PMA_sqlAddslashes($old_db) . '\'
1067 AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1068 PMA_query_as_controluser($remove_query);
1069 unset($remove_query);
1072 if ($GLOBALS['cfgRelation']['displaywork']) {
1073 $table_query = '
1074 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1075 . PMA_backquote($GLOBALS['cfgRelation']['table_info']) . '
1076 SET `db_name` = \'' . PMA_sqlAddslashes($new_db) . '\',
1077 `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1078 WHERE `db_name` = \'' . PMA_sqlAddslashes($old_db) . '\'
1079 AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1080 PMA_query_as_controluser($table_query);
1081 unset($table_query);
1084 if ($GLOBALS['cfgRelation']['relwork']) {
1085 $table_query = '
1086 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1087 . PMA_backquote($GLOBALS['cfgRelation']['relation']) . '
1088 SET `foreign_db` = \'' . PMA_sqlAddslashes($new_db) . '\',
1089 `foreign_table` = \'' . PMA_sqlAddslashes($new_name) . '\'
1090 WHERE `foreign_db` = \'' . PMA_sqlAddslashes($old_db) . '\'
1091 AND `foreign_table` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1092 PMA_query_as_controluser($table_query);
1094 $table_query = '
1095 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1096 . PMA_backquote($GLOBALS['cfgRelation']['relation']) . '
1097 SET `master_db` = \'' . PMA_sqlAddslashes($new_db) . '\',
1098 `master_table` = \'' . PMA_sqlAddslashes($new_name) . '\'
1099 WHERE `master_db` = \'' . PMA_sqlAddslashes($old_db) . '\'
1100 AND `master_table` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1101 PMA_query_as_controluser($table_query);
1102 unset($table_query);
1105 if ($GLOBALS['cfgRelation']['pdfwork']) {
1106 $table_query = '
1107 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1108 . PMA_backquote($GLOBALS['cfgRelation']['table_coords']) . '
1109 SET `db_name` = \'' . PMA_sqlAddslashes($new_db) . '\',
1110 `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1111 WHERE `db_name` = \'' . PMA_sqlAddslashes($old_db) . '\'
1112 AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1113 PMA_query_as_controluser($table_query);
1114 unset($table_query);
1117 if ($GLOBALS['cfgRelation']['designerwork']) {
1118 $table_query = '
1119 UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.'
1120 . PMA_backquote($GLOBALS['cfgRelation']['designer_coords']) . '
1121 SET `db_name` = \'' . PMA_sqlAddslashes($new_db) . '\',
1122 `table_name` = \'' . PMA_sqlAddslashes($new_name) . '\'
1123 WHERE `db_name` = \'' . PMA_sqlAddslashes($old_db) . '\'
1124 AND `table_name` = \'' . PMA_sqlAddslashes($old_name) . '\'';
1125 PMA_query_as_controluser($table_query);
1126 unset($table_query);
1129 $this->messages[] = sprintf(__('Table %s has been renamed to %s'),
1130 htmlspecialchars($old_name), htmlspecialchars($new_name));
1131 return true;
1135 * Get all unique columns
1137 * returns an array with all columns with unqiue content, in fact these are
1138 * all columns being single indexed in PRIMARY or UNIQUE
1140 * f.e.
1141 * - PRIMARY(id) // id
1142 * - UNIQUE(name) // name
1143 * - PRIMARY(fk_id1, fk_id2) // NONE
1144 * - UNIQUE(x,y) // NONE
1147 * @param boolean whether to quote name with backticks ``
1148 * @return array
1150 public function getUniqueColumns($backquoted = true)
1152 $sql = 'SHOW INDEX FROM ' . $this->getFullName(true) . ' WHERE Non_unique = 0';
1153 $uniques = PMA_DBI_fetch_result($sql, array('Key_name', null), 'Column_name');
1155 $return = array();
1156 foreach ($uniques as $index) {
1157 if (count($index) > 1) {
1158 continue;
1160 $return[] = $this->getFullName($backquoted) . '.' . ($backquoted ? PMA_backquote($index[0]) : $index[0]);
1163 return $return;
1167 * Get all indexed columns
1169 * returns an array with all columns make use of an index, in fact only
1170 * first columns in an index
1172 * f.e. index(col1, col2) would only return col1
1173 * @param boolean whether to quote name with backticks ``
1174 * @return array
1176 public function getIndexedColumns($backquoted = true)
1178 $sql = 'SHOW INDEX FROM ' . $this->getFullName(true) . ' WHERE Seq_in_index = 1';
1179 $indexed = PMA_DBI_fetch_result($sql, 'Column_name', 'Column_name');
1181 $return = array();
1182 foreach ($indexed as $column) {
1183 $return[] = $this->getFullName($backquoted) . '.' . ($backquoted ? PMA_backquote($column) : $column);
1186 return $return;