/libraries/classes/Table.php
PHP | 2713 lines | 1661 code | 344 blank | 708 comment | 303 complexity | 6ef938fb7bd36972c5f2f62e644b91f0 MD5 | raw file
Possible License(s): GPL-2.0, MIT, LGPL-3.0
Large files files are truncated, but you can click here to view the full file
- <?php
- declare(strict_types=1);
- namespace PhpMyAdmin;
- use PhpMyAdmin\Html\Generator;
- use PhpMyAdmin\Html\MySQLDocumentation;
- use PhpMyAdmin\Plugins\Export\ExportSql;
- use PhpMyAdmin\Query\Compatibility;
- use PhpMyAdmin\Query\Generator as QueryGenerator;
- use PhpMyAdmin\SqlParser\Components\Expression;
- use PhpMyAdmin\SqlParser\Components\OptionsArray;
- use PhpMyAdmin\SqlParser\Context;
- use PhpMyAdmin\SqlParser\Parser;
- use PhpMyAdmin\SqlParser\Statements\AlterStatement;
- use PhpMyAdmin\SqlParser\Statements\CreateStatement;
- use PhpMyAdmin\SqlParser\Statements\DropStatement;
- use PhpMyAdmin\SqlParser\Utils\Table as TableUtils;
- use Stringable;
- use function __;
- use function array_key_exists;
- use function array_map;
- use function count;
- use function end;
- use function explode;
- use function htmlspecialchars;
- use function implode;
- use function in_array;
- use function is_array;
- use function json_decode;
- use function json_encode;
- use function mb_stripos;
- use function mb_strlen;
- use function mb_substr;
- use function preg_match;
- use function preg_replace;
- use function rtrim;
- use function sprintf;
- use function str_contains;
- use function str_replace;
- use function stripos;
- use function strlen;
- use function strtolower;
- use function strtoupper;
- use function substr;
- use function substr_compare;
- use function trigger_error;
- use function trim;
- use const E_USER_WARNING;
- /**
- * Handles everything related to tables
- *
- * @todo make use of Message and Error
- */
- class Table implements Stringable
- {
- /**
- * UI preferences properties
- */
- public const PROP_SORTED_COLUMN = 'sorted_col';
- public const PROP_COLUMN_ORDER = 'col_order';
- public const PROP_COLUMN_VISIB = 'col_visib';
- /** @var string engine (innodb, myisam, bdb, ...) */
- public $engine = '';
- /** @var string type (view, base table, system view) */
- public $type = '';
- /** @var array UI preferences */
- public $uiprefs = [];
- /** @var array errors occurred */
- public $errors = [];
- /** @var array messages */
- public $messages = [];
- /** @var string table name */
- protected $name = '';
- /** @var string database name */
- protected $dbName = '';
- /** @var DatabaseInterface */
- protected $dbi;
- /** @var Relation */
- private $relation;
- /**
- * @param string $tableName table name
- * @param string $dbName database name
- * @param DatabaseInterface|null $dbi database interface for the table
- */
- public function __construct($tableName, $dbName, ?DatabaseInterface $dbi = null)
- {
- if (empty($dbi)) {
- $dbi = $GLOBALS['dbi'];
- }
- $this->dbi = $dbi;
- $this->name = $tableName;
- $this->dbName = $dbName;
- $this->relation = new Relation($this->dbi);
- }
- /**
- * returns table name
- *
- * @see Table::getName()
- */
- public function __toString(): string
- {
- return $this->getName();
- }
- /**
- * Table getter
- *
- * @param string $tableName table name
- * @param string $dbName database name
- * @param DatabaseInterface|null $dbi database interface for the table
- *
- * @return Table
- */
- public static function get($tableName, $dbName, ?DatabaseInterface $dbi = null)
- {
- return new Table($tableName, $dbName, $dbi);
- }
- /**
- * return the last error
- *
- * @return string the last error
- */
- public function getLastError()
- {
- return end($this->errors);
- }
- /**
- * return the last message
- *
- * @return string the last message
- */
- public function getLastMessage()
- {
- return end($this->messages);
- }
- /**
- * returns table name
- *
- * @param bool $backquoted whether to quote name with backticks ``
- *
- * @return string table name
- */
- public function getName($backquoted = false)
- {
- if ($backquoted) {
- return Util::backquote($this->name);
- }
- return $this->name;
- }
- /**
- * returns database name for this table
- *
- * @param bool $backquoted whether to quote name with backticks ``
- *
- * @return string database name for this table
- */
- public function getDbName($backquoted = false)
- {
- if ($backquoted) {
- return Util::backquote($this->dbName);
- }
- return $this->dbName;
- }
- /**
- * returns full name for table, including database name
- *
- * @param bool $backquoted whether to quote name with backticks ``
- *
- * @return string
- */
- public function getFullName($backquoted = false)
- {
- return $this->getDbName($backquoted) . '.'
- . $this->getName($backquoted);
- }
- /**
- * Checks the storage engine used to create table
- *
- * @param array|string $engine Checks the table engine against an
- * array of engine strings or a single string, should be uppercase
- */
- public function isEngine($engine): bool
- {
- $tableStorageEngine = $this->getStorageEngine();
- if (is_array($engine)) {
- foreach ($engine as $e) {
- if ($e == $tableStorageEngine) {
- return true;
- }
- }
- return false;
- }
- return $tableStorageEngine == $engine;
- }
- /**
- * returns whether the table is actually a view
- */
- public function isView(): bool
- {
- $db = $this->dbName;
- $table = $this->name;
- if (empty($db) || empty($table)) {
- return false;
- }
- // use cached data or load information with SHOW command
- if (
- $this->dbi->getCache()->getCachedTableContent([$db, $table]) != null
- || $GLOBALS['cfg']['Server']['DisableIS']
- ) {
- $type = $this->getStatusInfo('TABLE_TYPE');
- return $type === 'VIEW' || $type === 'SYSTEM VIEW';
- }
- // information_schema tables are 'SYSTEM VIEW's
- if ($db === 'information_schema') {
- return true;
- }
- // query information_schema
- $result = $this->dbi->fetchResult(
- 'SELECT TABLE_NAME'
- . ' FROM information_schema.VIEWS'
- . ' WHERE TABLE_SCHEMA = \'' . $this->dbi->escapeString((string) $db) . '\''
- . ' AND TABLE_NAME = \'' . $this->dbi->escapeString((string) $table) . '\''
- );
- return (bool) $result;
- }
- /**
- * Returns whether the table is actually an updatable view
- */
- public function isUpdatableView(): bool
- {
- if (empty($this->dbName) || empty($this->name)) {
- return false;
- }
- $result = $this->dbi->fetchResult(
- 'SELECT TABLE_NAME'
- . ' FROM information_schema.VIEWS'
- . ' WHERE TABLE_SCHEMA = \'' . $this->dbi->escapeString($this->dbName) . '\''
- . ' AND TABLE_NAME = \'' . $this->dbi->escapeString($this->name) . '\''
- . ' AND IS_UPDATABLE = \'YES\''
- );
- return (bool) $result;
- }
- /**
- * Checks if this is a merge table
- *
- * If the ENGINE of the table is MERGE or MRG_MYISAM (alias),
- * this is a merge table.
- */
- public function isMerge(): bool
- {
- return $this->isEngine(['MERGE', 'MRG_MYISAM']);
- }
- /**
- * Returns full table status info, or specific if $info provided
- * this info is collected from information_schema
- *
- * @param string $info specific information to be fetched
- * @param bool $forceRead read new rather than serving from cache
- * @param bool $disableError if true, disables error message
- *
- * @return mixed
- *
- * @todo DatabaseInterface::getTablesFull needs to be merged
- * somehow into this class or at least better documented
- */
- public function getStatusInfo(
- $info = null,
- $forceRead = false,
- $disableError = false
- ) {
- $db = $this->dbName;
- $table = $this->name;
- if (! empty($_SESSION['is_multi_query'])) {
- $disableError = true;
- }
- $cachedResult = $this->dbi->getCache()->getCachedTableContent([$db, $table]);
- // sometimes there is only one entry (ExactRows) so
- // we have to get the table's details
- if ($cachedResult === null || $forceRead || count($cachedResult) === 1) {
- $this->dbi->getTablesFull($db, $table);
- $cachedResult = $this->dbi->getCache()->getCachedTableContent([$db, $table]);
- }
- if ($cachedResult === null) {
- // happens when we enter the table creation dialog
- // or when we really did not get any status info, for example
- // when $table === 'TABLE_NAMES' after the user tried SHOW TABLES
- return '';
- }
- if ($info === null) {
- return $cachedResult;
- }
- // array_key_exists allows for null values
- if (! array_key_exists($info, $cachedResult)) {
- if (! $disableError) {
- trigger_error(
- __('Unknown table status:') . ' ' . $info,
- E_USER_WARNING
- );
- }
- return false;
- }
- return $this->dbi->getCache()->getCachedTableContent([$db, $table, $info]);
- }
- /**
- * Returns the Table storage Engine for current table.
- *
- * @return string Return storage engine info if it is set for
- * the selected table else return blank.
- */
- public function getStorageEngine(): string
- {
- $tableStorageEngine = $this->getStatusInfo('ENGINE', false, true);
- if ($tableStorageEngine === false) {
- return '';
- }
- return strtoupper((string) $tableStorageEngine);
- }
- /**
- * Returns the comments for current table.
- *
- * @return string Return comment info if it is set for the selected table or return blank.
- */
- public function getComment()
- {
- $tableComment = $this->getStatusInfo('TABLE_COMMENT', false, true);
- if ($tableComment === false) {
- return '';
- }
- return $tableComment;
- }
- /**
- * Returns the collation for current table.
- *
- * @return string Return blank if collation is empty else return the collation info from table info.
- */
- public function getCollation()
- {
- $tableCollation = $this->getStatusInfo('TABLE_COLLATION', false, true);
- if ($tableCollation === false) {
- return '';
- }
- return $tableCollation;
- }
- /**
- * Returns the info about no of rows for current table.
- *
- * @return int Return no of rows info if it is not null for the selected table or return 0.
- */
- public function getNumRows()
- {
- $tableNumRowInfo = $this->getStatusInfo('TABLE_ROWS', false, true);
- if ($tableNumRowInfo === false) {
- $tableNumRowInfo = $this->dbi->getTable($this->dbName, $GLOBALS['showtable']['Name'])
- ->countRecords(true);
- }
- return $tableNumRowInfo ?: 0;
- }
- /**
- * Returns the Row format for current table.
- *
- * @return string Return table row format info if it is set for the selected table or return blank.
- */
- public function getRowFormat()
- {
- $tableRowFormat = $this->getStatusInfo('ROW_FORMAT', false, true);
- if ($tableRowFormat === false) {
- return '';
- }
- return $tableRowFormat;
- }
- /**
- * Returns the auto increment option for current table.
- *
- * @return int Return auto increment info if it is set for the selected table or return blank.
- */
- public function getAutoIncrement()
- {
- $tableAutoIncrement = $this->getStatusInfo('AUTO_INCREMENT', false, true);
- return $tableAutoIncrement ?? '';
- }
- /**
- * Returns the array for CREATE statement for current table.
- *
- * @return array Return options array info if it is set for the selected table or return blank.
- */
- public function getCreateOptions()
- {
- $tableOptions = $this->getStatusInfo('CREATE_OPTIONS', false, true);
- $createOptionsTmp = empty($tableOptions) ? [] : explode(' ', $tableOptions);
- $createOptions = [];
- // export create options by its name as variables into global namespace
- // f.e. pack_keys=1 becomes available as $pack_keys with value of '1'
- // unset($pack_keys);
- foreach ($createOptionsTmp as $eachCreateOption) {
- $eachCreateOption = explode('=', $eachCreateOption);
- if (! isset($eachCreateOption[1])) {
- continue;
- }
- // ensure there is no ambiguity for PHP 5 and 7
- $createOptions[$eachCreateOption[0]] = $eachCreateOption[1];
- }
- // we need explicit DEFAULT value here (different from '0')
- $hasPackKeys = isset($createOptions['pack_keys']) && strlen($createOptions['pack_keys']) > 0;
- $createOptions['pack_keys'] = $hasPackKeys ? $createOptions['pack_keys'] : 'DEFAULT';
- return $createOptions;
- }
- /**
- * generates column specification for ALTER or CREATE TABLE syntax
- *
- * @param string $name name
- * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
- * @param string $length length ('2', '5,2', '', ...)
- * @param string $attribute attribute
- * @param string $collation collation
- * @param bool|string $null with 'NULL' or 'NOT NULL'
- * @param string $defaultType whether default is CURRENT_TIMESTAMP,
- * NULL, NONE, USER_DEFINED
- * @param string $defaultValue default value for USER_DEFINED
- * default type
- * @param string $extra 'AUTO_INCREMENT'
- * @param string $comment field comment
- * @param string $virtuality virtuality of the column
- * @param string $expression expression for the virtual column
- * @param string $moveTo new position for column
- * @param array $columnsWithIndex Fields having PRIMARY or UNIQUE KEY indexes
- * @param string $oldColumnName Old column name
- *
- * @return string field specification
- *
- * @todo move into class PMA_Column
- * @todo on the interface, some js to clear the default value when the
- * default current_timestamp is checked
- */
- public static function generateFieldSpec(
- $name,
- string $type,
- string $length = '',
- $attribute = '',
- $collation = '',
- $null = false,
- $defaultType = 'USER_DEFINED',
- $defaultValue = '',
- $extra = '',
- $comment = '',
- $virtuality = '',
- $expression = '',
- $moveTo = '',
- $columnsWithIndex = null,
- $oldColumnName = null
- ) {
- global $dbi;
- $strLength = strlen($length);
- $isTimestamp = mb_stripos($type, 'TIMESTAMP') !== false;
- $query = Util::backquote($name) . ' ' . $type;
- // allow the possibility of a length for TIME, DATETIME and TIMESTAMP
- // (will work on MySQL >= 5.6.4)
- //
- // MySQL permits a non-standard syntax for FLOAT and DOUBLE,
- // see https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
- $pattern = '@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
- . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN|UUID|JSON)$@i';
- if (
- $strLength !== 0
- && ! preg_match($pattern, $type)
- && Compatibility::isIntegersSupportLength($type, $length, $dbi)
- ) {
- // Note: The variable $length here can contain several other things
- // besides length - ENUM/SET value or length of DECIMAL (eg. 12,3)
- // so we can't just convert it to integer
- $query .= '(' . $length . ')';
- }
- if ($attribute != '') {
- $query .= ' ' . $attribute;
- if ($isTimestamp && stripos($attribute, 'TIMESTAMP') !== false && $strLength !== 0) {
- $query .= '(' . $length . ')';
- }
- }
- // if column is virtual, check if server type is Mysql as only Mysql server
- // supports extra column properties
- $isVirtualColMysql = $virtuality && Compatibility::isMySqlOrPerconaDb();
- // if column is virtual, check if server type is MariaDB as MariaDB server
- // supports no extra virtual column properties except CHARACTER SET for text column types
- $isVirtualColMariaDB = $virtuality && Compatibility::isMariaDb();
- $matches = preg_match('@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', $type);
- if (! empty($collation) && $collation !== 'NULL' && $matches) {
- $query .= Util::getCharsetQueryPart(
- $isVirtualColMariaDB ? (string) preg_replace('~_.+~s', '', $collation) : $collation,
- true
- );
- }
- if ($virtuality) {
- $query .= ' AS (' . $expression . ') ' . $virtuality;
- }
- if (! $virtuality || $isVirtualColMysql) {
- if ($null !== false) {
- if ($null === 'YES') {
- $query .= ' NULL';
- } else {
- $query .= ' NOT NULL';
- }
- }
- if (! $virtuality) {
- switch ($defaultType) {
- case 'USER_DEFINED':
- if ($isTimestamp && $defaultValue === '0') {
- // a TIMESTAMP does not accept DEFAULT '0'
- // but DEFAULT 0 works
- $query .= ' DEFAULT 0';
- } elseif (
- $isTimestamp
- && preg_match(
- '/^\'\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d(\.\d{1,6})?\'$/',
- (string) $defaultValue
- )
- ) {
- $query .= ' DEFAULT ' . (string) $defaultValue;
- } elseif ($type === 'BIT') {
- $query .= ' DEFAULT b\''
- . preg_replace('/[^01]/', '0', (string) $defaultValue)
- . '\'';
- } elseif ($type === 'BOOLEAN') {
- if (preg_match('/^1|T|TRUE|YES$/i', (string) $defaultValue)) {
- $query .= ' DEFAULT TRUE';
- } elseif (preg_match('/^0|F|FALSE|NO$/i', $defaultValue)) {
- $query .= ' DEFAULT FALSE';
- } else {
- // Invalid BOOLEAN value
- $query .= ' DEFAULT \''
- . $dbi->escapeString($defaultValue) . '\'';
- }
- } elseif ($type === 'BINARY' || $type === 'VARBINARY') {
- $query .= ' DEFAULT 0x' . $defaultValue;
- } else {
- $query .= ' DEFAULT \''
- . $dbi->escapeString((string) $defaultValue) . '\'';
- }
- break;
- /** @noinspection PhpMissingBreakStatementInspection */
- case 'NULL':
- // If user uncheck null checkbox and not change default value null,
- // default value will be ignored.
- if ($null !== false && $null !== 'YES') {
- break;
- }
- // else fall-through intended, no break here
- case 'CURRENT_TIMESTAMP':
- case 'current_timestamp()':
- $query .= ' DEFAULT ' . $defaultType;
- if (
- $strLength !== 0
- && $isTimestamp
- && $defaultType !== 'NULL' // Not to be added in case of NULL
- ) {
- $query .= '(' . $length . ')';
- }
- break;
- case 'NONE':
- default:
- break;
- }
- }
- if (! empty($extra)) {
- if ($virtuality) {
- $extra = trim((string) preg_replace('~^\s*AUTO_INCREMENT\s*~is', ' ', $extra));
- }
- $query .= ' ' . $extra;
- }
- }
- if (! empty($comment)) {
- $query .= " COMMENT '" . $dbi->escapeString($comment) . "'";
- }
- // move column
- if ($moveTo === '-first') { // dash can't appear as part of column name
- $query .= ' FIRST';
- } elseif ($moveTo != '') {
- $query .= ' AFTER ' . Util::backquote($moveTo);
- }
- if (! $virtuality && ! empty($extra)) {
- if ($oldColumnName === null) {
- if (is_array($columnsWithIndex) && ! in_array($name, $columnsWithIndex)) {
- $query .= ', add PRIMARY KEY (' . Util::backquote($name) . ')';
- }
- } else {
- if (is_array($columnsWithIndex) && ! in_array($oldColumnName, $columnsWithIndex)) {
- $query .= ', add PRIMARY KEY (' . Util::backquote($name) . ')';
- }
- }
- }
- return $query;
- }
- /**
- * Checks if the number of records in a table is at least equal to
- * $min_records
- *
- * @param int $minRecords Number of records to check for in a table
- */
- public function checkIfMinRecordsExist($minRecords = 0): bool
- {
- $checkQuery = 'SELECT ';
- $uniqueFields = $this->getUniqueColumns(true, false);
- if (count($uniqueFields) > 0) {
- $fieldsToSelect = implode(', ', $uniqueFields);
- } else {
- $indexedCols = $this->getIndexedColumns(true, false);
- if (count($indexedCols) > 0) {
- $fieldsToSelect = implode(', ', $indexedCols);
- } else {
- $fieldsToSelect = '*';
- }
- }
- $checkQuery .= $fieldsToSelect
- . ' FROM ' . $this->getFullName(true)
- . ' LIMIT ' . $minRecords;
- $res = $this->dbi->tryQuery($checkQuery);
- if ($res !== false) {
- $numRecords = $this->dbi->numRows($res);
- if ($numRecords >= $minRecords) {
- return true;
- }
- }
- return false;
- }
- /**
- * Counts and returns (or displays) the number of records in a table
- *
- * @param bool $forceExact whether to force an exact count
- *
- * @return mixed the number of records if "retain" param is true,
- * otherwise true
- */
- public function countRecords($forceExact = false)
- {
- $isView = $this->isView();
- $db = $this->dbName;
- $table = $this->name;
- if ($this->dbi->getCache()->getCachedTableContent([$db, $table, 'ExactRows']) != null) {
- return $this->dbi->getCache()->getCachedTableContent(
- [
- $db,
- $table,
- 'ExactRows',
- ]
- );
- }
- $rowCount = false;
- if (! $forceExact) {
- if (($this->dbi->getCache()->getCachedTableContent([$db, $table, 'Rows']) == null) && ! $isView) {
- $tmpTables = $this->dbi->getTablesFull($db, $table);
- if (isset($tmpTables[$table])) {
- $this->dbi->getCache()->cacheTableContent(
- [
- $db,
- $table,
- ],
- $tmpTables[$table]
- );
- }
- }
- if ($this->dbi->getCache()->getCachedTableContent([$db, $table, 'Rows']) != null) {
- $rowCount = $this->dbi->getCache()->getCachedTableContent(
- [
- $db,
- $table,
- 'Rows',
- ]
- );
- } else {
- $rowCount = false;
- }
- }
- // for a VIEW, $row_count is always false at this point
- if ($rowCount !== false && $rowCount >= $GLOBALS['cfg']['MaxExactCount']) {
- return $rowCount;
- }
- if (! $isView) {
- $rowCount = $this->dbi->fetchValue(
- 'SELECT COUNT(*) FROM ' . Util::backquote($db) . '.'
- . Util::backquote($table)
- );
- } else {
- // For complex views, even trying to get a partial record
- // count could bring down a server, so we offer an
- // alternative: setting MaxExactCountViews to 0 will bypass
- // completely the record counting for views
- if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
- $rowCount = false;
- } else {
- // Counting all rows of a VIEW could be too long,
- // so use a LIMIT clause.
- // Use try_query because it can fail (when a VIEW is
- // based on a table that no longer exists)
- $result = $this->dbi->tryQuery(
- 'SELECT 1 FROM ' . Util::backquote($db) . '.'
- . Util::backquote($table) . ' LIMIT '
- . $GLOBALS['cfg']['MaxExactCountViews'],
- DatabaseInterface::CONNECT_USER,
- DatabaseInterface::QUERY_STORE
- );
- if (! $this->dbi->getError()) {
- $rowCount = $this->dbi->numRows($result);
- $this->dbi->freeResult($result);
- }
- }
- }
- if ($rowCount) {
- $this->dbi->getCache()->cacheTableContent([$db, $table, 'ExactRows'], $rowCount);
- }
- return $rowCount;
- }
- /**
- * Generates column specification for ALTER syntax
- *
- * @see Table::generateFieldSpec()
- *
- * @param string $oldcol old column name
- * @param string $newcol new column name
- * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
- * @param string $length length ('2', '5,2', '', ...)
- * @param string $attribute attribute
- * @param string $collation collation
- * @param bool|string $null with 'NULL' or 'NOT NULL'
- * @param string $defaultType whether default is CURRENT_TIMESTAMP,
- * NULL, NONE, USER_DEFINED
- * @param string $defaultValue default value for USER_DEFINED default
- * type
- * @param string $extra 'AUTO_INCREMENT'
- * @param string $comment field comment
- * @param string $virtuality virtuality of the column
- * @param string $expression expression for the virtual column
- * @param string $moveTo new position for column
- * @param array $columnsWithIndex Fields having PRIMARY or UNIQUE KEY indexes
- *
- * @return string field specification
- */
- public static function generateAlter(
- $oldcol,
- $newcol,
- $type,
- $length,
- $attribute,
- $collation,
- $null,
- $defaultType,
- $defaultValue,
- $extra,
- $comment,
- $virtuality,
- $expression,
- $moveTo,
- $columnsWithIndex = null
- ) {
- return Util::backquote($oldcol) . ' '
- . self::generateFieldSpec(
- $newcol,
- $type,
- $length,
- $attribute,
- $collation,
- $null,
- $defaultType,
- $defaultValue,
- $extra,
- $comment,
- $virtuality,
- $expression,
- $moveTo,
- $columnsWithIndex,
- $oldcol
- );
- }
- /**
- * Inserts existing entries in a PMA_* table by reading a value from an old
- * entry
- *
- * @param string $work The array index, which Relation feature to check ('relwork', 'commwork', ...)
- * @param string $table The array index, which PMA-table to update ('bookmark', 'relation', ...)
- * @param array $getFields Which fields will be SELECT'ed from the old entry
- * @param array $whereFields Which fields will be used for the WHERE query (array('FIELDNAME' => 'FIELDVALUE'))
- * @param array $newFields Which fields will be used as new VALUES. These are the important keys which differ
- * from the old entry (array('FIELDNAME' => 'NEW FIELDVALUE'))
- *
- * @return int|bool
- */
- public static function duplicateInfo(
- $work,
- $table,
- array $getFields,
- array $whereFields,
- array $newFields
- ) {
- global $dbi;
- $relation = new Relation($dbi);
- $lastId = -1;
- if (! isset($GLOBALS['cfgRelation']) || ! $GLOBALS['cfgRelation'][$work]) {
- return true;
- }
- $selectParts = [];
- $rowFields = [];
- foreach ($getFields as $getField) {
- $selectParts[] = Util::backquote($getField);
- $rowFields[$getField] = 'cc';
- }
- $whereParts = [];
- foreach ($whereFields as $where => $value) {
- $whereParts[] = Util::backquote($where) . ' = \''
- . $dbi->escapeString((string) $value) . '\'';
- }
- $newParts = [];
- $newValueParts = [];
- foreach ($newFields as $where => $value) {
- $newParts[] = Util::backquote($where);
- $newValueParts[] = $dbi->escapeString((string) $value);
- }
- $tableCopyQuery = '
- SELECT ' . implode(', ', $selectParts) . '
- FROM ' . Util::backquote($GLOBALS['cfgRelation']['db']) . '.'
- . Util::backquote($GLOBALS['cfgRelation'][$table]) . '
- WHERE ' . implode(' AND ', $whereParts);
- // must use DatabaseInterface::QUERY_STORE here, since we execute
- // another query inside the loop
- $tableCopyRs = $relation->queryAsControlUser($tableCopyQuery, true, DatabaseInterface::QUERY_STORE);
- while ($tableCopyRow = @$dbi->fetchAssoc($tableCopyRs)) {
- $valueParts = [];
- foreach ($tableCopyRow as $key => $val) {
- if (! isset($rowFields[$key]) || $rowFields[$key] != 'cc') {
- continue;
- }
- $valueParts[] = $dbi->escapeString($val);
- }
- $newTableQuery = 'INSERT IGNORE INTO '
- . Util::backquote($GLOBALS['cfgRelation']['db'])
- . '.' . Util::backquote($GLOBALS['cfgRelation'][$table])
- . ' (' . implode(', ', $selectParts) . ', '
- . implode(', ', $newParts) . ') VALUES (\''
- . implode('\', \'', $valueParts) . '\', \''
- . implode('\', \'', $newValueParts) . '\')';
- $relation->queryAsControlUser($newTableQuery);
- $lastId = $dbi->insertId();
- }
- $dbi->freeResult($tableCopyRs);
- return $lastId;
- }
- /**
- * Copies or renames table
- *
- * @param string $sourceDb source database
- * @param string $sourceTable source table
- * @param string|null $targetDb target database
- * @param string $targetTable target table
- * @param string $what what to be moved or copied (data, dataonly)
- * @param bool $move whether to move
- * @param string $mode mode
- */
- public static function moveCopy(
- $sourceDb,
- $sourceTable,
- ?string $targetDb,
- $targetTable,
- $what,
- $move,
- $mode,
- bool $addDropIfExists
- ): bool {
- global $errorUrl, $dbi;
- $relation = new Relation($dbi);
- // Try moving the tables directly, using native `RENAME` statement.
- if ($move && $what === 'data') {
- $tbl = new Table($sourceTable, $sourceDb);
- if ($tbl->rename($targetTable, $targetDb)) {
- $GLOBALS['message'] = $tbl->getLastMessage();
- return true;
- }
- }
- // Setting required export settings.
- $GLOBALS['sql_backquotes'] = 1;
- $GLOBALS['asfile'] = 1;
- // Ensuring the target database is valid.
- if (! $GLOBALS['dblist']->databases->exists($sourceDb, $targetDb)) {
- if (! $GLOBALS['dblist']->databases->exists($sourceDb)) {
- $GLOBALS['message'] = Message::rawError(
- sprintf(
- __('Source database `%s` was not found!'),
- htmlspecialchars($sourceDb)
- )
- );
- }
- if (! $GLOBALS['dblist']->databases->exists($targetDb)) {
- $GLOBALS['message'] = Message::rawError(
- sprintf(
- __('Target database `%s` was not found!'),
- htmlspecialchars((string) $targetDb)
- )
- );
- }
- return false;
- }
- /**
- * The full name of source table, quoted.
- *
- * @var string $source
- */
- $source = Util::backquote($sourceDb)
- . '.' . Util::backquote($sourceTable);
- // If the target database is not specified, the operation is taking
- // place in the same database.
- if (! isset($targetDb) || strlen($targetDb) === 0) {
- $targetDb = $sourceDb;
- }
- // Selecting the database could avoid some problems with replicated
- // databases, when moving table from replicated one to not replicated one.
- $dbi->selectDb($targetDb);
- /**
- * The full name of target table, quoted.
- *
- * @var string $target
- */
- $target = Util::backquote($targetDb)
- . '.' . Util::backquote($targetTable);
- // No table is created when this is a data-only operation.
- if ($what !== 'dataonly') {
- /**
- * Instance used for exporting the current structure of the table.
- *
- * @var ExportSql $exportSqlPlugin
- */
- $exportSqlPlugin = Plugins::getPlugin('export', 'sql', [
- 'export_type' => 'table',
- 'single_table' => false,
- ]);
- $noConstraintsComments = true;
- $GLOBALS['sql_constraints_query'] = '';
- // set the value of global sql_auto_increment variable
- if (isset($_POST['sql_auto_increment'])) {
- $GLOBALS['sql_auto_increment'] = $_POST['sql_auto_increment'];
- }
- /**
- * The old structure of the table..
- *
- * @var string $sqlStructure
- */
- $sqlStructure = $exportSqlPlugin->getTableDef($sourceDb, $sourceTable, "\n", $errorUrl, false, false);
- unset($noConstraintsComments);
- // -----------------------------------------------------------------
- // Phase 0: Preparing structures used.
- /**
- * The destination where the table is moved or copied to.
- *
- * @var Expression
- */
- $destination = new Expression($targetDb, $targetTable, '');
- // Find server's SQL mode so the builder can generate correct
- // queries.
- // One of the options that alters the behaviour is `ANSI_QUOTES`.
- Context::setMode((string) $dbi->fetchValue('SELECT @@sql_mode'));
- // -----------------------------------------------------------------
- // Phase 1: Dropping existent element of the same name (if exists
- // and required).
- if ($addDropIfExists) {
- /**
- * Drop statement used for building the query.
- *
- * @var DropStatement $statement
- */
- $statement = new DropStatement();
- $tbl = new Table($targetDb, $targetTable);
- $statement->options = new OptionsArray(
- [
- $tbl->isView() ? 'VIEW' : 'TABLE',
- 'IF EXISTS',
- ]
- );
- $statement->fields = [$destination];
- // Building the query.
- $dropQuery = $statement->build() . ';';
- // Executing it.
- $dbi->query($dropQuery);
- $GLOBALS['sql_query'] .= "\n" . $dropQuery;
- // If an existing table gets deleted, maintain any entries for
- // the PMA_* tables.
- $maintainRelations = true;
- }
- // -----------------------------------------------------------------
- // Phase 2: Generating the new query of this structure.
- /**
- * The parser responsible for parsing the old queries.
- *
- * @var Parser $parser
- */
- $parser = new Parser($sqlStructure);
- if (! empty($parser->statements[0])) {
- /**
- * The CREATE statement of this structure.
- *
- * @var CreateStatement $statement
- */
- $statement = $parser->statements[0];
- // Changing the destination.
- $statement->name = $destination;
- // Building back the query.
- $sqlStructure = $statement->build() . ';';
- // This is to avoid some issues when renaming databases with views
- // See: https://github.com/phpmyadmin/phpmyadmin/issues/16422
- if ($move) {
- $dbi->selectDb($targetDb);
- }
- // Executing it
- $dbi->query($sqlStructure);
- $GLOBALS['sql_query'] .= "\n" . $sqlStructure;
- }
- // -----------------------------------------------------------------
- // Phase 3: Adding constraints.
- // All constraint names are removed because they must be unique.
- if (($move || isset($GLOBALS['add_constraints'])) && ! empty($GLOBALS['sql_constraints_query'])) {
- $parser = new Parser($GLOBALS['sql_constraints_query']);
- /**
- * The ALTER statement that generates the constraints.
- *
- * @var AlterStatement $statement
- */
- $statement = $parser->statements[0];
- // Changing the altered table to the destination.
- $statement->table = $destination;
- // Removing the name of the constraints.
- foreach ($statement->altered as $idx => $altered) {
- // All constraint names are removed because they must be unique.
- if (! $altered->options->has('CONSTRAINT')) {
- continue;
- }
- $altered->field = null;
- }
- // Building back the query.
- $GLOBALS['sql_constraints_query'] = $statement->build() . ';';
- // Executing it.
- if ($mode === 'one_table') {
- $dbi->query($GLOBALS['sql_constraints_query']);
- }
- $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
- if ($mode === 'one_table') {
- unset($GLOBALS['sql_constraints_query']);
- }
- }
- // -----------------------------------------------------------------
- // Phase 4: Adding indexes.
- // View phase 3.
- if (! empty($GLOBALS['sql_indexes'])) {
- $parser = new Parser($GLOBALS['sql_indexes']);
- $GLOBALS['sql_indexes'] = '';
- /**
- * The ALTER statement that generates the indexes.
- *
- * @var AlterStatement $statement
- */
- foreach ($parser->statements as $statement) {
- // Changing the altered table to the destination.
- $statement->table = $destination;
- // Removing the name of the constraints.
- foreach ($statement->altered as $idx => $altered) {
- // All constraint names are removed because they must be unique.
- if (! $altered->options->has('CONSTRAINT')) {
- continue;
- }
- $altered->field = null;
- }
- // Building back the query.
- $sqlIndex = $statement->build() . ';';
- // Executing it.
- if ($mode === 'one_table' || $mode === 'db_copy') {
- $dbi->query($sqlIndex);
- }
- $GLOBALS['sql_indexes'] .= $sqlIndex;
- }
- $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_indexes'];
- if ($mode === 'one_table' || $mode === 'db_copy') {
- unset($GLOBALS['sql_indexes']);
- }
- }
- // -----------------------------------------------------------------
- // Phase 5: Adding AUTO_INCREMENT.
- if (! empty($GLOBALS['sql_auto_increments'])) {
- if ($mode === 'one_table' || $mode === 'db_copy') {
- $parser = new Parser($GLOBALS['sql_auto_increments']);
- /**
- * The ALTER statement that alters the AUTO_INCREMENT value.
- *
- * @var AlterStatement $statement
- */
- $statement = $parser->statements[0];
- // Changing the altered table to the destination.
- $statement->table = $destination;
- // Building back the query.
- $GLOBALS['sql_auto_increments'] = $statement->build() . ';';
- // Executing it.
- $dbi->query($GLOBALS['sql_auto_increments']);
- $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_auto_increments'];
- unset($GLOBALS['sql_auto_increments']);
- }
- }
- } else {
- $GLOBALS['sql_query'] = '';
- }
- $table = new Table($targetTable, $targetDb);
- // Copy the data unless this is a VIEW
- if (($what === 'data' || $what === 'dataonly') && ! $table->isView()) {
- $sqlSetMode = "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'";
- $dbi->query($sqlSetMode);
- $GLOBALS['sql_query'] .= "\n\n" . $sqlSetMode . ';';
- $oldTable = new Table($sourceTable, $sourceDb);
- $nonGeneratedCols = $oldTable->getNonGeneratedColumns(true);
- if (count($nonGeneratedCols) > 0) {
- $sqlInsertData = 'INSERT INTO ' . $target . '('
- . implode(', ', $nonGeneratedCols)
- . ') SELECT ' . implode(', ', $nonGeneratedCols)
- . ' FROM ' . $source;
- $dbi->query($sqlInsertData);
- $GLOBALS['sql_query'] .= "\n\n" . $sqlInsertData . ';';
- }
- }
- $relation->getRelationsParam();
- // Drops old table if the user has requested to move it
- if ($move) {
- // This could avoid some problems with replicated databases, when
- // moving table from replicated one to not replicated one
- $dbi->selectDb($sourceDb);
- $sourceTableObj = new Table($sourceTable, $sourceDb);
- if ($sourceTableObj->isView()) {
- $sqlDropQuery = 'DROP VIEW';
- } else {
- $sqlDropQuery = 'DROP TABLE';
- }
- $sqlDropQuery .= ' ' . $source;
- $dbi->query($sqlDropQuery);
- // Rename table in configuration storage
- $relation->renameTable($sourceDb, $targetDb, $sourceTable, $targetTable);
- $GLOBALS['sql_query'] .= "\n\n" . $sqlDropQuery . ';';
- return true;
- }
- // we are copying
- // Create new entries as duplicates from old PMA DBs
- if ($what === 'dataonly' || isset($maintainRelations)) {
- return true;
- }
- if ($GLOBALS['cfgRelation']['commwork']) {
- // Get all comments and MIME-Types for current table
- $commentsCopyRs = $relation->queryAsControlUser(
- 'SELECT column_name, comment'
- . ($GLOBALS['cfgRelation']['mimework']
- ? ', mimetype, transformation, transformation_options'
- : '')
- . ' FROM '
- . Util::backquote($GLOBALS['cfgRelation']['db'])
- . '.'
- . Util::backquote($GLOBALS['cfgRelation']['column_info'])
- . ' WHERE '
- . ' db_name = \''
- . $dbi->escapeString($sourceDb) . '\''
- . ' AND '
- . ' table_name = \''
- . $dbi->escapeString((string) $sourceTable) . '\''
- );
- // Write every comment as new copied entry. [MIME]
- while ($commentsCopyRow = $dbi->fetchAssoc($commentsCopyRs)) {
- $newCommentQuery = 'REPLACE INTO '
- . Util::backquote($GLOBALS['cfgRelation']['db'])
- . '.' . Util::backquote($GLOBALS['cfgRelation']['column_info'])
- . ' (db_name, table_name, column_name, comment'
- . ($GLOBALS['cfgRelation']['mimework']
- ? ', mimetype, transformation, transformation_options'
- : '')
- . ') VALUES(\'' . $dbi->escapeString($targetDb)
- . '\',\'' . $dbi->escapeString($targetTable) . '\',\''
- . $dbi->escapeString($commentsCopyRow['column_name'])
- . '\',\''
- . $dbi->escapeString($commentsCopyRow['comment'])
- . '\''
- . ($GLOBALS['cfgRelation']['mimework']
- ? ',\'' . $dbi->escapeString($commentsCopyRow['mimetype'])
- . '\',\'' . $dbi->escapeString($commentsCopyRow['transformation'])
- . '\',\'' . $dbi->escapeString($commentsCopyRow['transformation_options'])
- . '\''
- : '')
- . ')';
- $relation->queryAsControlUser($newCommentQuery);
- }
- $dbi->freeResult($commentsCopyRs);
- unset($commentsCopyRs);
- }
- // duplicating the bookmarks must not be done here, but
- // just once per db
- $getFields = ['display_field'];
- $whereFields = [
- 'db_name' => $sourceDb,
- 'table_name' => $sourceTable,
- ];
- $newFields = [
- 'db_name' => $targetDb,
- 'table_name' => $targetTable,
- ];
- self::duplicateInfo('displaywork', 'table_info', $getFields, $whereFields, $newFields);
- /**
- * @todo revise this code when we support cross-db relations
- */
- $getFields = [
- 'master_field',
- 'foreign_table',
- 'foreign_field',
- ];
- $whereFields = [
- 'master_db' => $sourceDb,
- 'master_table' => $sourceTable,
- ];
- $newFields = [
- 'master_db' => $targetDb,
- 'foreign_db' => $targetDb,
- 'master_table' => $targetTable,
- ];
- self::duplicateInfo('relwork', 'relation', $getFields, $whereFields, $newFields);
- $getFields = [
- 'foreign_field',
- 'master_table',
- 'master_field',
- ];
- $whereFields = [
- 'foreign_db' => $sourceDb,
- 'foreign_table' => $sourceTable,
- ];
- $newFields = [
- 'master_db' => $targetDb,
- 'foreign_db' => $targetDb,
- 'foreign_table' => $targetTable,
- ];
- self::duplicateInfo('relwork', 'relation', $getFields, $whereFields, $newFields);
- /**
- * @todo Can't get duplicating PDFs the right way. The
- * page numbers always get screwed up independently from
- * duplication because the numbers do not seem to be stored on a
- * per-database basis. Would the author of pdf support please
- * have a look at it?
- *
- $get_fields = array('page_descr');
- $where_fields = array('db_name' => $source_db);
- $new_fields = array('db_name' => $target_db);
- $last_id = self::duplicateInfo(
- 'pdfwork',
- 'pdf_pages',
- $get_fields,
- $where_fields,
- $new_fields
- );
- if (isset($last_id) && $last_id >= 0) {
- $get_fields = arr…
Large files files are truncated, but you can click here to view the full file