/**
* Returns $table's CREATE definition
*
* @param string $db the database name
* @param string $table the table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case
* of error
* @param bool $show_dates whether to include creation/
* update/check dates
* @param bool $add_semicolon whether to add semicolon and
* end-of-line at the end
* @param bool $view whether we're handling a view
* @param bool $update_indexes_increments whether we need to update
* two global variables
* @param array $aliases Aliases of db/table/columns
*
* @return string resulting schema
*/
public function getTableDef($db, $table, $crlf, $error_url, $show_dates = false, $add_semicolon = true, $view = false, $update_indexes_increments = true, $aliases = array())
{
global $sql_drop_table, $sql_backquotes, $sql_constraints, $sql_constraints_query, $sql_indexes, $sql_indexes_query, $sql_auto_increments, $sql_drop_foreign_keys;
$db_alias = $db;
$table_alias = $table;
$this->initAlias($aliases, $db_alias, $table_alias);
$schema_create = '';
$auto_increment = '';
$new_crlf = $crlf;
if (isset($GLOBALS['sql_compatibility'])) {
$compat = $GLOBALS['sql_compatibility'];
} else {
$compat = 'NONE';
}
// need to use PMA\libraries\DatabaseInterface::QUERY_STORE
// with $GLOBALS['dbi']->numRows() in mysqli
$result = $GLOBALS['dbi']->query('SHOW TABLE STATUS FROM ' . Util::backquote($db) . ' WHERE Name = \'' . Util::sqlAddSlashes($table) . '\'', null, DatabaseInterface::QUERY_STORE);
if ($result != false) {
if ($GLOBALS['dbi']->numRows($result) > 0) {
$tmpres = $GLOBALS['dbi']->fetchAssoc($result);
// Here we optionally add the AUTO_INCREMENT next value,
// but starting with MySQL 5.0.24, the clause is already included
// in SHOW CREATE TABLE so we'll remove it below
if (isset($GLOBALS['sql_auto_increment']) && !empty($tmpres['Auto_increment'])) {
$auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' ';
}
if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) {
$schema_create .= $this->_exportComment(__('Creation:') . ' ' . Util::localisedDate(strtotime($tmpres['Create_time'])));
$new_crlf = $this->_exportComment() . $crlf;
}
if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) {
$schema_create .= $this->_exportComment(__('Last update:') . ' ' . Util::localisedDate(strtotime($tmpres['Update_time'])));
$new_crlf = $this->_exportComment() . $crlf;
}
if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) {
$schema_create .= $this->_exportComment(__('Last check:') . ' ' . Util::localisedDate(strtotime($tmpres['Check_time'])));
$new_crlf = $this->_exportComment() . $crlf;
}
}
$GLOBALS['dbi']->freeResult($result);
}
$schema_create .= $new_crlf;
// no need to generate a DROP VIEW here, it was done earlier
if (!empty($sql_drop_table) && !$GLOBALS['dbi']->getTable($db, $table)->isView()) {
$schema_create .= 'DROP TABLE IF EXISTS ' . Util::backquote($table_alias, $sql_backquotes) . ';' . $crlf;
}
// Complete table dump,
// Whether to quote table and column names or not
if ($sql_backquotes) {
$GLOBALS['dbi']->query('SET SQL_QUOTE_SHOW_CREATE = 1');
} else {
$GLOBALS['dbi']->query('SET SQL_QUOTE_SHOW_CREATE = 0');
}
// I don't see the reason why this unbuffered query could cause problems,
// because SHOW CREATE TABLE returns only one row, and we free the
// results below. Nonetheless, we got 2 user reports about this
// (see bug 1562533) so I removed the unbuffered mode.
// $result = $GLOBALS['dbi']->query('SHOW CREATE TABLE ' . backquote($db)
// . '.' . backquote($table), null, DatabaseInterface::QUERY_UNBUFFERED);
//
// Note: SHOW CREATE TABLE, at least in MySQL 5.1.23, does not
// produce a displayable result for the default value of a BIT
// column, nor does the mysqldump command. See MySQL bug 35796
$result = $GLOBALS['dbi']->tryQuery('SHOW CREATE TABLE ' . Util::backquote($db) . '.' . Util::backquote($table));
// an error can happen, for example the table is crashed
$tmp_error = $GLOBALS['dbi']->getError();
if ($tmp_error) {
return $this->_exportComment(__('in use') . '(' . $tmp_error . ')');
}
// Old mode is stored so it can be restored once exporting is done.
$old_mode = Context::$MODE;
$warning = '';
if ($result != false && ($row = $GLOBALS['dbi']->fetchRow($result))) {
$create_query = $row[1];
unset($row);
// Convert end of line chars to one that we want (note that MySQL
// doesn't return query it will accept in all cases)
if (mb_strpos($create_query, "(\r\n ")) {
$create_query = str_replace("\r\n", $crlf, $create_query);
} elseif (mb_strpos($create_query, "(\n ")) {
$create_query = str_replace("\n", $crlf, $create_query);
} elseif (mb_strpos($create_query, "(\r ")) {
$create_query = str_replace("\r", $crlf, $create_query);
}
/*
* Drop database name from VIEW creation.
*
* This is a bit tricky, but we need to issue SHOW CREATE TABLE with
* database name, but we don't want name to show up in CREATE VIEW
* statement.
*/
if ($view) {
$create_query = preg_replace('/' . preg_quote(Util::backquote($db)) . '\\./', '', $create_query);
}
// Substitute aliases in `CREATE` query.
$create_query = $this->replaceWithAliases($create_query, $aliases, $db, $table, $flag);
// One warning per view.
if ($flag && $view) {
$warning = $this->_exportComment() . $this->_exportComment(__('It appears your database uses views;')) . $this->_exportComment(__('alias export may not work reliably in all cases.')) . $this->_exportComment();
}
// Adding IF NOT EXISTS, if required.
if (isset($GLOBALS['sql_if_not_exists'])) {
$create_query = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query);
}
// Making the query MSSQL compatible.
if ($compat == 'MSSQL') {
$create_query = $this->_makeCreateTableMSSQLCompatible($create_query);
}
// Views have no constraints, indexes, etc. They do not require any
// analysis.
if (!$view) {
// Using appropriate quotes.
if ($compat === 'MSSQL' || $sql_backquotes === '"') {
Context::$MODE |= Context::ANSI_QUOTES;
}
/**
* Parser used for analysis.
*
* @var Parser
*/
$parser = new Parser($create_query);
}
if (!empty($parser->statements[0]->fields)) {
/**
* `CREATE TABLE` statement.
*
* @var SelectStatement
*/
$statement = $parser->statements[0];
/**
* Fragments containining definition of each constraint.
*
* @var array
*/
$constraints = array();
/**
* Fragments containining definition of each index.
*
* @var array
*/
$indexes = array();
/**
* Fragments containining definition of each FULLTEXT index.
*
* @var array
*/
$indexes_fulltext = array();
/**
* Fragments containining definition of each foreign key that will
* be dropped.
*
* @var array
*/
$dropped = array();
/**
* Fragment containining definition of the `AUTO_INCREMENT`.
*
* @var array
*/
$auto_increment = array();
// Scanning each field of the `CREATE` statement to fill the arrays
// above.
// If the field is used in any of the arrays above, it is removed
// from the original definition.
// Also, AUTO_INCREMENT attribute is removed.
/** @var CreateDefinition $field */
foreach ($statement->fields as $key => $field) {
if ($field->isConstraint) {
// Creating the parts that add constraints.
$constraints[] = $field::build($field);
unset($statement->fields[$key]);
} elseif (!empty($field->key)) {
// Creating the parts that add indexes (must not be
// constraints).
if ($field->key->type === 'FULLTEXT KEY') {
$indexes_fulltext[] = $field->build($field);
unset($statement->fields[$key]);
} else {
if (empty($GLOBALS['sql_if_not_exists'])) {
$indexes[] = $field->build($field);
unset($statement->fields[$key]);
}
}
}
// Creating the parts that drop foreign keys.
if (!empty($field->key)) {
if ($field->key->type === 'FOREIGN KEY') {
$dropped[] = 'FOREIGN KEY ' . Context::escape($field->name);
unset($statement->fields[$key]);
}
}
// Dropping AUTO_INCREMENT.
if (!empty($field->options)) {
if ($field->options->has('AUTO_INCREMENT') && empty($GLOBALS['sql_if_not_exists'])) {
$auto_increment[] = $field::build($field);
$field->options->remove('AUTO_INCREMENT');
}
}
}
/**
* The header of the `ALTER` statement (`ALTER TABLE tbl`).
*
* @var string
*/
$alter_header = 'ALTER TABLE ' . Util::backquoteCompat($table_alias, $compat, $sql_backquotes);
/**
* The footer of the `ALTER` statement (usually ';')
*
* @var string
*/
$alter_footer = ';' . $crlf;
// Generating constraints-related query.
if (!empty($constraints)) {
$sql_constraints_query = $alter_header . $crlf . ' ADD ' . implode(',' . $crlf . ' ADD ', $constraints) . $alter_footer;
$sql_constraints = $this->generateComment($crlf, $sql_constraints, __('Constraints for dumped tables'), __('Constraints for table'), $table_alias, $compat) . $sql_constraints_query;
}
// Generating indexes-related query.
$sql_indexes_query = '';
if (!empty($indexes)) {
$sql_indexes_query .= $alter_header . $crlf . ' ADD ' . implode(',' . $crlf . ' ADD ', $indexes) . $alter_footer;
}
if (!empty($indexes_fulltext)) {
// InnoDB supports one FULLTEXT index creation at a time.
// So FULLTEXT indexes are created one-by-one after other
// indexes where created.
$sql_indexes_query .= $alter_header . ' ADD ' . implode($alter_footer . $alter_header . ' ADD ', $indexes_fulltext) . $alter_footer;
}
if (!empty($indexes) || !empty($indexes_fulltext)) {
$sql_indexes = $this->generateComment($crlf, $sql_indexes, __('Indexes for dumped tables'), __('Indexes for table'), $table_alias, $compat) . $sql_indexes_query;
}
// Generating drop foreign keys-related query.
if (!empty($dropped)) {
$sql_drop_foreign_keys = $alter_header . $crlf . ' DROP ' . implode(',' . $crlf . ' DROP ', $dropped) . $alter_footer;
}
// Generating auto-increment-related query.
if (!empty($auto_increment) && $update_indexes_increments) {
$sql_auto_increments_query = $alter_header . $crlf . ' MODIFY ' . implode(',' . $crlf . ' MODIFY ', $auto_increment);
if (isset($GLOBALS['sql_auto_increment']) && $statement->entityOptions->has('AUTO_INCREMENT') !== false) {
$sql_auto_increments_query .= ', AUTO_INCREMENT=' . $statement->entityOptions->has('AUTO_INCREMENT');
}
$sql_auto_increments_query .= ';';
$sql_auto_increments = $this->generateComment($crlf, $sql_auto_increments, __('AUTO_INCREMENT for dumped tables'), __('AUTO_INCREMENT for table'), $table_alias, $compat) . $sql_auto_increments_query;
}
// Removing the `AUTO_INCREMENT` attribute from the `CREATE TABLE`
// too.
if (!empty($statement->entityOptions) && (empty($GLOBALS['sql_if_not_exists']) || empty($GLOBALS['sql_auto_increment']))) {
$statement->entityOptions->remove('AUTO_INCREMENT');
}
// Rebuilding the query.
$create_query = $statement->build();
}
$schema_create .= $create_query;
}
$GLOBALS['dbi']->freeResult($result);
// Restoring old mode.
Context::$MODE = $old_mode;
return $warning . $schema_create . ($add_semicolon ? ';' . $crlf : '');
}