diff options
Diffstat (limited to 'srcs/phpmyadmin/libraries/classes/Controllers/Table')
8 files changed, 4064 insertions, 0 deletions
diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/AbstractController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/AbstractController.php new file mode 100644 index 0000000..35f01ac --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/AbstractController.php @@ -0,0 +1,54 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\AbstractController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\Controllers\AbstractController as Controller; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Response; +use PhpMyAdmin\Template; + +/** + * Handles table related logic + * + * @package PhpMyAdmin\Controllers + */ +abstract class AbstractController extends Controller +{ + /** + * @var string + */ + protected $db; + + /** + * @var string + */ + protected $table; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table + ) { + parent::__construct($response, $dbi, $template); + $this->db = $db; + $this->table = $table; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/ChartController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/ChartController.php new file mode 100644 index 0000000..b2c4176 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/ChartController.php @@ -0,0 +1,261 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\ChartController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Message; +use PhpMyAdmin\Response; +use PhpMyAdmin\SqlParser\Components\Limit; +use PhpMyAdmin\SqlParser\Parser; +use PhpMyAdmin\SqlParser\Statements\SelectStatement; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; + +/** + * Handles table related logic + * + * @package PhpMyAdmin\Controllers + */ +class ChartController extends AbstractController +{ + /** + * @var string + */ + protected $sql_query; + + /** + * @var string + */ + protected $url_query; + + /** + * @var array + */ + protected $cfg; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param string $sql_query Query + * @param string $url_query Query URL + * @param array $cfg Configuration + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $sql_query, + $url_query, + array $cfg + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + $this->sql_query = $sql_query; + $this->url_query = $url_query; + $this->cfg = $cfg; + } + + /** + * Execute the query and return the result + * + * @return void + */ + public function indexAction() + { + $response = Response::getInstance(); + if ($response->isAjax() + && isset($_REQUEST['pos']) + && isset($_REQUEST['session_max_rows']) + ) { + $this->ajaxAction(); + return; + } + + // Throw error if no sql query is set + if (! isset($this->sql_query) || $this->sql_query == '') { + $this->response->setRequestStatus(false); + $this->response->addHTML( + Message::error(__('No SQL query was set to fetch data.')) + ); + return; + } + + $this->response->getHeader()->getScripts()->addFiles( + [ + 'chart.js', + 'table/chart.js', + 'vendor/jqplot/jquery.jqplot.js', + 'vendor/jqplot/plugins/jqplot.barRenderer.js', + 'vendor/jqplot/plugins/jqplot.canvasAxisLabelRenderer.js', + 'vendor/jqplot/plugins/jqplot.canvasTextRenderer.js', + 'vendor/jqplot/plugins/jqplot.categoryAxisRenderer.js', + 'vendor/jqplot/plugins/jqplot.dateAxisRenderer.js', + 'vendor/jqplot/plugins/jqplot.pointLabels.js', + 'vendor/jqplot/plugins/jqplot.pieRenderer.js', + 'vendor/jqplot/plugins/jqplot.enhancedPieLegendRenderer.js', + 'vendor/jqplot/plugins/jqplot.highlighter.js', + ] + ); + + /** + * Extract values for common work + * @todo Extract common files + */ + $db = &$this->db; + $table = &$this->table; + $url_params = []; + + /** + * Runs common work + */ + if (strlen($this->table) > 0) { + $url_params['goto'] = Util::getScriptNameForOption( + $this->cfg['DefaultTabTable'], + 'table' + ); + $url_params['back'] = 'tbl_sql.php'; + include ROOT_PATH . 'libraries/tbl_common.inc.php'; + $this->dbi->selectDb($GLOBALS['db']); + } elseif (strlen($this->db) > 0) { + $url_params['goto'] = Util::getScriptNameForOption( + $this->cfg['DefaultTabDatabase'], + 'database' + ); + $url_params['back'] = 'sql.php'; + include ROOT_PATH . 'libraries/db_common.inc.php'; + } else { + $url_params['goto'] = Util::getScriptNameForOption( + $this->cfg['DefaultTabServer'], + 'server' + ); + $url_params['back'] = 'sql.php'; + include ROOT_PATH . 'libraries/server_common.inc.php'; + } + + $data = []; + + $result = $this->dbi->tryQuery($this->sql_query); + $fields_meta = $this->dbi->getFieldsMeta($result); + while ($row = $this->dbi->fetchAssoc($result)) { + $data[] = $row; + } + + $keys = array_keys($data[0]); + + $numeric_types = [ + 'int', + 'real', + ]; + $numeric_column_count = 0; + foreach ($keys as $idx => $key) { + if (in_array($fields_meta[$idx]->type, $numeric_types)) { + $numeric_column_count++; + } + } + + if ($numeric_column_count == 0) { + $this->response->setRequestStatus(false); + $this->response->addJSON( + 'message', + __('No numeric columns present in the table to plot.') + ); + return; + } + + $url_params['db'] = $this->db; + $url_params['reload'] = 1; + + /** + * Displays the page + */ + $this->response->addHTML( + $this->template->render('table/chart/tbl_chart', [ + 'url_query' => $this->url_query, + 'url_params' => $url_params, + 'keys' => $keys, + 'fields_meta' => $fields_meta, + 'numeric_types' => $numeric_types, + 'numeric_column_count' => $numeric_column_count, + 'sql_query' => $this->sql_query, + ]) + ); + } + + /** + * Handle ajax request + * + * @return void + */ + public function ajaxAction() + { + /** + * Extract values for common work + * @todo Extract common files + */ + $db = &$this->db; + $table = &$this->table; + + if (strlen($this->table) > 0 && strlen($this->db) > 0) { + include ROOT_PATH . 'libraries/tbl_common.inc.php'; + } + + $parser = new Parser($this->sql_query); + /** + * @var SelectStatement $statement + */ + $statement = $parser->statements[0]; + if (empty($statement->limit)) { + $statement->limit = new Limit( + $_REQUEST['session_max_rows'], + $_REQUEST['pos'] + ); + } else { + $start = $statement->limit->offset + $_REQUEST['pos']; + $rows = min( + $_REQUEST['session_max_rows'], + $statement->limit->rowCount - $_REQUEST['pos'] + ); + $statement->limit = new Limit($rows, $start); + } + $sql_with_limit = $statement->build(); + + $data = []; + $result = $this->dbi->tryQuery($sql_with_limit); + while ($row = $this->dbi->fetchAssoc($result)) { + $data[] = $row; + } + + if (empty($data)) { + $this->response->setRequestStatus(false); + $this->response->addJSON('message', __('No data to display')); + return; + } + $sanitized_data = []; + + foreach ($data as $data_row_number => $data_row) { + $tmp_row = []; + foreach ($data_row as $data_column => $data_value) { + $escaped_value = $data_value === null ? null : htmlspecialchars($data_value); + $tmp_row[htmlspecialchars($data_column)] = $escaped_value; + } + $sanitized_data[] = $tmp_row; + } + $this->response->setRequestStatus(true); + $this->response->addJSON('message', null); + $this->response->addJSON('chartData', json_encode($sanitized_data)); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/GisVisualizationController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/GisVisualizationController.php new file mode 100644 index 0000000..18e844f --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/GisVisualizationController.php @@ -0,0 +1,227 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\GisVisualizationController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\Core; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Gis\GisVisualization; +use PhpMyAdmin\Message; +use PhpMyAdmin\Response; +use PhpMyAdmin\Template; +use PhpMyAdmin\Url; + +/** + * Class GisVisualizationController + * + * @package PhpMyAdmin\Controllers + */ +class GisVisualizationController extends AbstractController +{ + /** + * @var array + */ + protected $url_params; + + /** + * @var string + */ + protected $sql_query; + + /** + * @var array + */ + protected $visualizationSettings; + + /** + * @var GisVisualization + */ + protected $visualization; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param string $sql_query SQL query for retrieving GIS data + * @param array $url_params array of URL parameters + * @param string $goto goto script + * @param string $back back script + * @param array $visualizationSettings visualization settings + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $sql_query, + array $url_params, + $goto, + $back, + array $visualizationSettings + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + require_once ROOT_PATH . 'libraries/common.inc.php'; + require_once ROOT_PATH . 'libraries/db_common.inc.php'; + + $this->sql_query = $sql_query; + $this->url_params = $url_params; + $this->url_params['goto'] = $goto; + $this->url_params['back'] = $back; + $this->visualizationSettings = $visualizationSettings; + } + + /** + * Save to file + * + * @return void + */ + public function saveToFileAction() + { + $this->response->disable(); + $file_name = $this->visualizationSettings['spatialColumn']; + $save_format = $_GET['fileFormat']; + $this->visualization->toFile($file_name, $save_format); + } + + /** + * Index + * + * @return void + */ + public function indexAction() + { + // Throw error if no sql query is set + if (! isset($this->sql_query) || $this->sql_query == '') { + $this->response->setRequestStatus(false); + $this->response->addHTML( + Message::error(__('No SQL query was set to fetch data.')) + ); + return; + } + + // Execute the query and return the result + $result = $this->dbi->tryQuery($this->sql_query); + // Get the meta data of results + $meta = $this->dbi->getFieldsMeta($result); + + // Find the candidate fields for label column and spatial column + $labelCandidates = []; + $spatialCandidates = []; + foreach ($meta as $column_meta) { + if ($column_meta->type == 'geometry') { + $spatialCandidates[] = $column_meta->name; + } else { + $labelCandidates[] = $column_meta->name; + } + } + + // Get settings if any posted + if (Core::isValid($_POST['visualizationSettings'], 'array')) { + $this->visualizationSettings = $_POST['visualizationSettings']; + } + + // Check mysql version + $this->visualizationSettings['mysqlVersion'] = $this->dbi->getVersion(); + + if (! isset($this->visualizationSettings['labelColumn']) + && isset($labelCandidates[0]) + ) { + $this->visualizationSettings['labelColumn'] = ''; + } + + // If spatial column is not set, use first geometric column as spatial column + if (! isset($this->visualizationSettings['spatialColumn'])) { + $this->visualizationSettings['spatialColumn'] = $spatialCandidates[0]; + } + + // Convert geometric columns from bytes to text. + $pos = isset($_GET['pos']) ? $_GET['pos'] + : $_SESSION['tmpval']['pos']; + if (isset($_GET['session_max_rows'])) { + $rows = $_GET['session_max_rows']; + } else { + if ($_SESSION['tmpval']['max_rows'] != 'all') { + $rows = $_SESSION['tmpval']['max_rows']; + } else { + $rows = $GLOBALS['cfg']['MaxRows']; + } + } + $this->visualization = GisVisualization::get( + $this->sql_query, + $this->visualizationSettings, + $rows, + $pos + ); + + if (isset($_GET['saveToFile'])) { + $this->saveToFileAction(); + return; + } + + $this->response->getHeader()->getScripts()->addFiles( + [ + 'vendor/openlayers/OpenLayers.js', + 'vendor/jquery/jquery.svg.js', + 'table/gis_visualization.js', + ] + ); + + // If all the rows contain SRID, use OpenStreetMaps on the initial loading. + if (! isset($_POST['displayVisualization'])) { + if ($this->visualization->hasSrid()) { + $this->visualizationSettings['choice'] = 'useBaseLayer'; + } else { + unset($this->visualizationSettings['choice']); + } + } + + $this->visualization->setUserSpecifiedSettings($this->visualizationSettings); + if ($this->visualizationSettings != null) { + foreach ($this->visualization->getSettings() as $setting => $val) { + if (! isset($this->visualizationSettings[$setting])) { + $this->visualizationSettings[$setting] = $val; + } + } + } + + /** + * Displays the page + */ + $this->url_params['sql_query'] = $this->sql_query; + $downloadUrl = 'tbl_gis_visualization.php' . Url::getCommon( + array_merge( + $this->url_params, + [ + 'saveToFile' => true, + 'session_max_rows' => $rows, + 'pos' => $pos, + ] + ) + ); + $html = $this->template->render('table/gis_visualization/gis_visualization', [ + 'url_params' => $this->url_params, + 'download_url' => $downloadUrl, + 'label_candidates' => $labelCandidates, + 'spatial_candidates' => $spatialCandidates, + 'visualization_settings' => $this->visualizationSettings, + 'sql_query' => $this->sql_query, + 'visualization' => $this->visualization->toImage('svg'), + 'draw_ol' => $this->visualization->asOl(), + 'pma_theme_image' => $GLOBALS['pmaThemeImage'], + ]); + + $this->response->addHTML($html); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/IndexesController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/IndexesController.php new file mode 100644 index 0000000..cdbfbb9 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/IndexesController.php @@ -0,0 +1,179 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\IndexesController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Index; +use PhpMyAdmin\Message; +use PhpMyAdmin\Response; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; + +/** + * Class IndexesController + * + * @package PhpMyAdmin\Controllers + */ +class IndexesController extends AbstractController +{ + /** + * @var Index + */ + protected $index; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param Index $index Index object + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $index + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + $this->index = $index; + } + + /** + * Index + * + * @return void + */ + public function indexAction() + { + if (isset($_POST['do_save_data'])) { + $this->doSaveDataAction(); + return; + } // end builds the new index + + $this->displayFormAction(); + } + + /** + * Display the form to edit/create an index + * + * @return void + */ + public function displayFormAction() + { + $this->dbi->selectDb($GLOBALS['db']); + $add_fields = 0; + if (isset($_POST['index']) && is_array($_POST['index'])) { + // coming already from form + if (isset($_POST['index']['columns']['names'])) { + $add_fields = count($_POST['index']['columns']['names']) + - $this->index->getColumnCount(); + } + if (isset($_POST['add_fields'])) { + $add_fields += $_POST['added_fields']; + } + } elseif (isset($_POST['create_index'])) { + $add_fields = $_POST['added_fields']; + } // end preparing form values + + // Get fields and stores their name/type + if (isset($_POST['create_edit_table'])) { + $fields = json_decode($_POST['columns'], true); + $index_params = [ + 'Non_unique' => $_POST['index']['Index_choice'] == 'UNIQUE' + ? '0' : '1', + ]; + $this->index->set($index_params); + $add_fields = count($fields); + } else { + $fields = $this->dbi->getTable($this->db, $this->table) + ->getNameAndTypeOfTheColumns(); + } + + $form_params = [ + 'db' => $this->db, + 'table' => $this->table, + ]; + + if (isset($_POST['create_index'])) { + $form_params['create_index'] = 1; + } elseif (isset($_POST['old_index'])) { + $form_params['old_index'] = $_POST['old_index']; + } elseif (isset($_POST['index'])) { + $form_params['old_index'] = $_POST['index']; + } + + $this->response->getHeader()->getScripts()->addFile('indexes.js'); + + $this->response->addHTML( + $this->template->render('table/index_form', [ + 'fields' => $fields, + 'index' => $this->index, + 'form_params' => $form_params, + 'add_fields' => $add_fields, + 'create_edit_table' => isset($_POST['create_edit_table']), + 'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'], + ]) + ); + } + + /** + * Process the data from the edit/create index form, + * run the query to build the new index + * and moves back to "tbl_sql.php" + * + * @return void + */ + public function doSaveDataAction() + { + $error = false; + + $sql_query = $this->dbi->getTable($this->db, $this->table) + ->getSqlQueryForIndexCreateOrEdit($this->index, $error); + + // If there is a request for SQL previewing. + if (isset($_POST['preview_sql'])) { + $this->response->addJSON( + 'sql_data', + $this->template->render('preview_sql', ['query_data' => $sql_query]) + ); + } elseif (! $error) { + $this->dbi->query($sql_query); + $response = Response::getInstance(); + if ($response->isAjax()) { + $message = Message::success( + __('Table %1$s has been altered successfully.') + ); + $message->addParam($this->table); + $this->response->addJSON( + 'message', + Util::getMessage($message, $sql_query, 'success') + ); + $this->response->addJSON( + 'index_table', + Index::getHtmlForIndexes( + $this->table, + $this->db + ) + ); + } else { + include ROOT_PATH . 'tbl_structure.php'; + } + } else { + $this->response->setRequestStatus(false); + $this->response->addJSON('message', $error); + } + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/RelationController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/RelationController.php new file mode 100644 index 0000000..558842c --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/RelationController.php @@ -0,0 +1,398 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\RelationController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\Core; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Index; +use PhpMyAdmin\Relation; +use PhpMyAdmin\Response; +use PhpMyAdmin\Table; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; + +/** + * Handles table relation logic + * + * @package PhpMyAdmin\Controllers + */ +class RelationController extends AbstractController +{ + /** + * @var array + */ + protected $options_array; + + /** + * @var array + */ + protected $cfgRelation; + + /** + * @var array + */ + protected $existrel; + + /** + * @var string + */ + protected $tbl_storage_engine; + + /** + * @var array + */ + protected $existrel_foreign; + + /** + * @var Table + */ + protected $upd_query; + + /** + * @var Relation + */ + private $relation; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param array|null $options_array Options + * @param array|null $cfgRelation Config relation + * @param string $tbl_storage_engine Table storage engine + * @param array|null $existrel Relations + * @param array|null $existrel_foreign External relations + * @param Table $upd_query Update query + * @param Relation $relation Relation instance + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $options_array, + $cfgRelation, + $tbl_storage_engine, + $existrel, + $existrel_foreign, + $upd_query, + Relation $relation + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + $this->options_array = $options_array; + $this->cfgRelation = $cfgRelation; + $this->tbl_storage_engine = $tbl_storage_engine; + $this->existrel = $existrel; + $this->existrel_foreign = $existrel_foreign; + $this->upd_query = $upd_query; + $this->relation = $relation; + } + + /** + * Index + * + * @return void + */ + public function indexAction() + { + // Send table of column names to populate corresponding dropdowns depending + // on the current selection + if (isset($_POST['getDropdownValues']) + && $_POST['getDropdownValues'] === 'true' + ) { + // if both db and table are selected + if (isset($_POST['foreignTable'])) { + $this->getDropdownValueForTableAction(); + } else { // if only the db is selected + $this->getDropdownValueForDbAction(); + } + return; + } + + $this->response->getHeader()->getScripts()->addFiles( + [ + 'table/relation.js', + 'indexes.js', + ] + ); + + // Set the database + $this->dbi->selectDb($this->db); + + // updates for Internal relations + if (isset($_POST['destination_db']) && $this->cfgRelation['relwork']) { + $this->updateForInternalRelationAction(); + } + + // updates for foreign keys + $this->updateForForeignKeysAction(); + + // Updates for display field + if ($this->cfgRelation['displaywork'] && isset($_POST['display_field'])) { + $this->updateForDisplayField(); + } + + // If we did an update, refresh our data + if (isset($_POST['destination_db']) && $this->cfgRelation['relwork']) { + $this->existrel = $this->relation->getForeigners( + $this->db, + $this->table, + '', + 'internal' + ); + } + if (isset($_POST['destination_foreign_db']) + && Util::isForeignKeySupported($this->tbl_storage_engine) + ) { + $this->existrel_foreign = $this->relation->getForeigners( + $this->db, + $this->table, + '', + 'foreign' + ); + } + + /** + * Dialog + */ + // Now find out the columns of our $table + // need to use DatabaseInterface::QUERY_STORE with $this->dbi->numRows() + // in mysqli + $columns = $this->dbi->getColumns($this->db, $this->table); + + $column_array = []; + $column_hash_array = []; + $column_array[''] = ''; + foreach ($columns as $column) { + if (strtoupper($this->tbl_storage_engine) == 'INNODB' + || ! empty($column['Key']) + ) { + $column_array[$column['Field']] = $column['Field']; + $column_hash_array[$column['Field']] = md5($column['Field']); + } + } + if ($GLOBALS['cfg']['NaturalOrder']) { + uksort($column_array, 'strnatcasecmp'); + } + + // common form + $engine = $this->dbi->getTable($this->db, $this->table)->getStorageEngine(); + $foreignKeySupported = Util::isForeignKeySupported($this->tbl_storage_engine); + $this->response->addHTML( + $this->template->render('table/relation/common_form', [ + 'is_foreign_key_supported' => Util::isForeignKeySupported($engine), + 'db' => $this->db, + 'table' => $this->table, + 'cfg_relation' => $this->cfgRelation, + 'tbl_storage_engine' => $this->tbl_storage_engine, + 'existrel' => isset($this->existrel) ? $this->existrel : [], + 'existrel_foreign' => is_array($this->existrel_foreign) && array_key_exists('foreign_keys_data', $this->existrel_foreign) + ? $this->existrel_foreign['foreign_keys_data'] : [], + 'options_array' => $this->options_array, + 'column_array' => $column_array, + 'column_hash_array' => $column_hash_array, + 'save_row' => array_values($columns), + 'url_params' => $GLOBALS['url_params'], + 'databases' => $GLOBALS['dblist']->databases, + 'dbi' => $this->dbi, + 'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'], + 'foreignKeySupported' => $foreignKeySupported, + 'displayIndexesHtml' => $foreignKeySupported ? Index::getHtmlForDisplayIndexes() : null, + ]) + ); + } + + /** + * Update for display field + * + * @return void + */ + public function updateForDisplayField() + { + if ($this->upd_query->updateDisplayField( + $_POST['display_field'], + $this->cfgRelation + ) + ) { + $this->response->addHTML( + Util::getMessage( + __('Display column was successfully updated.'), + '', + 'success' + ) + ); + } + } + + /** + * Update for FK + * + * @return void + */ + public function updateForForeignKeysAction() + { + $multi_edit_columns_name = isset($_POST['foreign_key_fields_name']) + ? $_POST['foreign_key_fields_name'] + : null; + $preview_sql_data = ''; + $seen_error = false; + + // (for now, one index name only; we keep the definitions if the + // foreign db is not the same) + if (isset($_POST['destination_foreign_db']) + && isset($_POST['destination_foreign_table']) + && isset($_POST['destination_foreign_column'])) { + list($html, $preview_sql_data, $display_query, $seen_error) + = $this->upd_query->updateForeignKeys( + $_POST['destination_foreign_db'], + $multi_edit_columns_name, + $_POST['destination_foreign_table'], + $_POST['destination_foreign_column'], + $this->options_array, + $this->table, + is_array($this->existrel_foreign) && array_key_exists('foreign_keys_data', $this->existrel_foreign) + ? $this->existrel_foreign['foreign_keys_data'] : [] + ); + $this->response->addHTML($html); + } + + // If there is a request for SQL previewing. + if (isset($_POST['preview_sql'])) { + Core::previewSQL($preview_sql_data); + } + + if (! empty($display_query) && ! $seen_error) { + $GLOBALS['display_query'] = $display_query; + $this->response->addHTML( + Util::getMessage( + __('Your SQL query has been executed successfully.'), + null, + 'success' + ) + ); + } + } + + /** + * Update for internal relation + * + * @return void + */ + public function updateForInternalRelationAction() + { + $multi_edit_columns_name = isset($_POST['fields_name']) + ? $_POST['fields_name'] + : null; + + if ($this->upd_query->updateInternalRelations( + $multi_edit_columns_name, + $_POST['destination_db'], + $_POST['destination_table'], + $_POST['destination_column'], + $this->cfgRelation, + isset($this->existrel) ? $this->existrel : null + ) + ) { + $this->response->addHTML( + Util::getMessage( + __('Internal relationships were successfully updated.'), + '', + 'success' + ) + ); + } + } + + /** + * Send table columns for foreign table dropdown + * + * @return void + * + */ + public function getDropdownValueForTableAction() + { + $foreignTable = $_POST['foreignTable']; + $table_obj = $this->dbi->getTable($_POST['foreignDb'], $foreignTable); + // Since views do not have keys defined on them provide the full list of + // columns + if ($table_obj->isView()) { + $columnList = $table_obj->getColumns(false, false); + } else { + $columnList = $table_obj->getIndexedColumns(false, false); + } + $columns = []; + foreach ($columnList as $column) { + $columns[] = htmlspecialchars($column); + } + if ($GLOBALS['cfg']['NaturalOrder']) { + usort($columns, 'strnatcasecmp'); + } + $this->response->addJSON('columns', $columns); + + // @todo should be: $server->db($db)->table($table)->primary() + $primary = Index::getPrimary($foreignTable, $_POST['foreignDb']); + if (false === $primary) { + return; + } + + $this->response->addJSON('primary', array_keys($primary->getColumns())); + } + + /** + * Send database selection values for dropdown + * + * @return void + * + */ + public function getDropdownValueForDbAction() + { + $tables = []; + $foreign = isset($_POST['foreign']) && $_POST['foreign'] === 'true'; + + if ($foreign) { + $query = 'SHOW TABLE STATUS FROM ' + . Util::backquote($_POST['foreignDb']); + $tables_rs = $this->dbi->query( + $query, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + + while ($row = $this->dbi->fetchArray($tables_rs)) { + if (isset($row['Engine']) + && mb_strtoupper($row['Engine']) == $this->tbl_storage_engine + ) { + $tables[] = htmlspecialchars($row['Name']); + } + } + } else { + $query = 'SHOW TABLES FROM ' + . Util::backquote($_POST['foreignDb']); + $tables_rs = $this->dbi->query( + $query, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + while ($row = $this->dbi->fetchArray($tables_rs)) { + $tables[] = htmlspecialchars($row[0]); + } + } + if ($GLOBALS['cfg']['NaturalOrder']) { + usort($tables, 'strnatcasecmp'); + } + $this->response->addJSON('tables', $tables); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/SearchController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/SearchController.php new file mode 100644 index 0000000..3f2ceae --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/SearchController.php @@ -0,0 +1,1244 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\SearchController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Relation; +use PhpMyAdmin\Response; +use PhpMyAdmin\Sql; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; + +/** + * Class SearchController + * + * @package PhpMyAdmin\Controllers + */ +class SearchController extends AbstractController +{ + /** + * Normal search or Zoom search + * + * @access private + * @var string + */ + private $_searchType; + /** + * Names of columns + * + * @access private + * @var array + */ + private $_columnNames; + /** + * Types of columns + * + * @access private + * @var array + */ + private $_columnTypes; + /** + * Collations of columns + * + * @access private + * @var array + */ + private $_columnCollations; + /** + * Null Flags of columns + * + * @access private + * @var array + */ + private $_columnNullFlags; + /** + * Whether a geometry column is present + * + * @access private + * @var boolean + */ + private $_geomColumnFlag; + /** + * Foreign Keys + * + * @access private + * @var array + */ + private $_foreigners; + /** + * Connection charset + * + * @access private + * @var string + */ + private $_connectionCharSet; + + protected $url_query; + + /** + * @var Relation + */ + private $relation; + + /** + * Constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param string $searchType Search type + * @param string $url_query URL query + * @param Relation $relation Relation instance + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $searchType, + $url_query, + Relation $relation + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + $this->url_query = $url_query; + $this->_searchType = $searchType; + $this->_columnNames = []; + $this->_columnNullFlags = []; + $this->_columnTypes = []; + $this->_columnCollations = []; + $this->_geomColumnFlag = false; + $this->_foreigners = []; + $this->relation = $relation; + // Loads table's information + $this->_loadTableInfo(); + $this->_connectionCharSet = $this->dbi->fetchValue( + "SELECT @@character_set_connection" + ); + } + + /** + * Gets all the columns of a table along with their types, collations + * and whether null or not. + * + * @return void + */ + private function _loadTableInfo() + { + // Gets the list and number of columns + $columns = $this->dbi->getColumns( + $this->db, + $this->table, + null, + true + ); + // Get details about the geometry functions + $geom_types = Util::getGISDatatypes(); + + foreach ($columns as $row) { + // set column name + $this->_columnNames[] = $row['Field']; + + $type = $row['Type']; + // check whether table contains geometric columns + if (in_array($type, $geom_types)) { + $this->_geomColumnFlag = true; + } + // reformat mysql query output + if (strncasecmp($type, 'set', 3) == 0 + || strncasecmp($type, 'enum', 4) == 0 + ) { + $type = str_replace(',', ', ', $type); + } else { + // strip the "BINARY" attribute, except if we find "BINARY(" because + // this would be a BINARY or VARBINARY column type + if (! preg_match('@BINARY[\(]@i', $type)) { + $type = str_ireplace("BINARY", '', $type); + } + $type = str_ireplace("ZEROFILL", '', $type); + $type = str_ireplace("UNSIGNED", '', $type); + $type = mb_strtolower($type); + } + if (empty($type)) { + $type = ' '; + } + $this->_columnTypes[] = $type; + $this->_columnNullFlags[] = $row['Null']; + $this->_columnCollations[] + = ! empty($row['Collation']) && $row['Collation'] != 'NULL' + ? $row['Collation'] + : ''; + } // end for + + // Retrieve foreign keys + $this->_foreigners = $this->relation->getForeigners($this->db, $this->table); + } + + /** + * Index action + * + * @return void + */ + public function indexAction() + { + global $goto; + switch ($this->_searchType) { + case 'replace': + if (isset($_POST['find'])) { + $this->findAction(); + + return; + } + $this->response + ->getHeader() + ->getScripts() + ->addFile('table/find_replace.js'); + + if (isset($_POST['replace'])) { + $this->replaceAction(); + } + + // Displays the find and replace form + $this->displaySelectionFormAction(); + break; + + case 'normal': + $this->response->getHeader() + ->getScripts() + ->addFiles( + [ + 'makegrid.js', + 'sql.js', + 'table/select.js', + 'table/change.js', + 'vendor/jquery/jquery.uitablefilter.js', + 'gis_data_editor.js', + ] + ); + + if (isset($_POST['range_search'])) { + $this->rangeSearchAction(); + + return; + } + + /** + * No selection criteria received -> display the selection form + */ + if (! isset($_POST['columnsToDisplay']) + && ! isset($_POST['displayAllColumns']) + ) { + $this->displaySelectionFormAction(); + } else { + $this->doSelectionAction(); + } + break; + + case 'zoom': + $this->response->getHeader() + ->getScripts() + ->addFiles( + [ + 'makegrid.js', + 'sql.js', + 'vendor/jqplot/jquery.jqplot.js', + 'vendor/jqplot/plugins/jqplot.canvasTextRenderer.js', + 'vendor/jqplot/plugins/jqplot.canvasAxisLabelRenderer.js', + 'vendor/jqplot/plugins/jqplot.dateAxisRenderer.js', + 'vendor/jqplot/plugins/jqplot.highlighter.js', + 'vendor/jqplot/plugins/jqplot.cursor.js', + 'table/zoom_plot_jqplot.js', + 'table/change.js', + ] + ); + + /** + * Handle AJAX request for data row on point select + * + * @var boolean Object containing parameters for the POST request + */ + if (isset($_POST['get_data_row']) + && $_POST['get_data_row'] == true + ) { + $this->getDataRowAction(); + + return; + } + /** + * Handle AJAX request for changing field information + * (value,collation,operators,field values) in input form + * + * @var boolean Object containing parameters for the POST request + */ + if (isset($_POST['change_tbl_info']) + && $_POST['change_tbl_info'] == true + ) { + $this->changeTableInfoAction(); + + return; + } + + //Set default datalabel if not selected + if (! isset($_POST['zoom_submit']) || $_POST['dataLabel'] == '') { + $dataLabel = $this->relation->getDisplayField($this->db, $this->table); + } else { + $dataLabel = $_POST['dataLabel']; + } + + // Displays the zoom search form + $this->displaySelectionFormAction($dataLabel); + + /* + * Handle the input criteria and generate the query result + * Form for displaying query results + */ + if (isset($_POST['zoom_submit']) + && $_POST['criteriaColumnNames'][0] != 'pma_null' + && $_POST['criteriaColumnNames'][1] != 'pma_null' + && $_POST['criteriaColumnNames'][0] != $_POST['criteriaColumnNames'][1] + ) { + if (! isset($goto)) { + $goto = Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabTable'], + 'table' + ); + } + $this->zoomSubmitAction($dataLabel, $goto); + } + break; + } + } + + /** + * Zoom submit action + * + * @param string $dataLabel Data label + * @param string $goto Goto + * + * @return void + */ + public function zoomSubmitAction($dataLabel, $goto) + { + //Query generation part + $sql_query = $this->_buildSqlQuery(); + $sql_query .= ' LIMIT ' . $_POST['maxPlotLimit']; + + //Query execution part + $result = $this->dbi->query( + $sql_query . ";", + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $fields_meta = $this->dbi->getFieldsMeta($result); + $data = []; + while ($row = $this->dbi->fetchAssoc($result)) { + //Need a row with indexes as 0,1,2 for the getUniqueCondition + // hence using a temporary array + $tmpRow = []; + foreach ($row as $val) { + $tmpRow[] = $val; + } + //Get unique condition on each row (will be needed for row update) + $uniqueCondition = Util::getUniqueCondition( + $result, // handle + count($this->_columnNames), // fields_cnt + $fields_meta, // fields_meta + $tmpRow, // row + true, // force_unique + false, // restrict_to_table + null // analyzed_sql_results + ); + //Append it to row array as where_clause + $row['where_clause'] = $uniqueCondition[0]; + + $tmpData = [ + $_POST['criteriaColumnNames'][0] => + $row[$_POST['criteriaColumnNames'][0]], + $_POST['criteriaColumnNames'][1] => + $row[$_POST['criteriaColumnNames'][1]], + 'where_clause' => $uniqueCondition[0], + ]; + $tmpData[$dataLabel] = $dataLabel ? $row[$dataLabel] : ''; + $data[] = $tmpData; + } + unset($tmpData); + + //Displays form for point data and scatter plot + $titles = [ + 'Browse' => Util::getIcon( + 'b_browse', + __('Browse foreign values') + ), + ]; + $column_names_hashes = []; + + foreach ($this->_columnNames as $columnName) { + $column_names_hashes[$columnName] = md5($columnName); + } + + $this->response->addHTML( + $this->template->render('table/search/zoom_result_form', [ + 'db' => $this->db, + 'table' => $this->table, + 'column_names' => $this->_columnNames, + 'column_names_hashes' => $column_names_hashes, + 'foreigners' => $this->_foreigners, + 'column_null_flags' => $this->_columnNullFlags, + 'column_types' => $this->_columnTypes, + 'titles' => $titles, + 'goto' => $goto, + 'data' => $data, + 'data_json' => json_encode($data), + 'zoom_submit' => isset($_POST['zoom_submit']), + 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'], + ]) + ); + } + + /** + * Change table info action + * + * @return void + */ + public function changeTableInfoAction() + { + $field = $_POST['field']; + if ($field == 'pma_null') { + $this->response->addJSON('field_type', ''); + $this->response->addJSON('field_collation', ''); + $this->response->addJSON('field_operators', ''); + $this->response->addJSON('field_value', ''); + return; + } + $key = array_search($field, $this->_columnNames); + $search_index + = (isset($_POST['it']) && is_numeric($_POST['it']) + ? intval($_POST['it']) : 0); + + $properties = $this->getColumnProperties($search_index, $key); + $this->response->addJSON( + 'field_type', + htmlspecialchars($properties['type']) + ); + $this->response->addJSON('field_collation', $properties['collation']); + $this->response->addJSON('field_operators', $properties['func']); + $this->response->addJSON('field_value', $properties['value']); + } + + /** + * Get data row action + * + * @return void + */ + public function getDataRowAction() + { + $extra_data = []; + $row_info_query = 'SELECT * FROM `' . $_POST['db'] . '`.`' + . $_POST['table'] . '` WHERE ' . $_POST['where_clause']; + $result = $this->dbi->query( + $row_info_query . ";", + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $fields_meta = $this->dbi->getFieldsMeta($result); + while ($row = $this->dbi->fetchAssoc($result)) { + // for bit fields we need to convert them to printable form + $i = 0; + foreach ($row as $col => $val) { + if ($fields_meta[$i]->type == 'bit') { + $row[$col] = Util::printableBitValue( + (int) $val, + (int) $fields_meta[$i]->length + ); + } + $i++; + } + $extra_data['row_info'] = $row; + } + $this->response->addJSON($extra_data); + } + + /** + * Do selection action + * + * @return void + */ + public function doSelectionAction() + { + /** + * Selection criteria have been submitted -> do the work + */ + $sql_query = $this->_buildSqlQuery(); + + /** + * Add this to ensure following procedures included running correctly. + */ + $sql = new Sql(); + $sql->executeQueryAndSendQueryResponse( + null, // analyzed_sql_results + false, // is_gotofile + $this->db, // db + $this->table, // table + null, // find_real_end + null, // sql_query_for_bookmark + null, // extra_data + null, // message_to_show + null, // message + null, // sql_data + $GLOBALS['goto'], // goto + $GLOBALS['pmaThemeImage'], // pmaThemeImage + null, // disp_query + null, // disp_message + null, // query_type + $sql_query, // sql_query + null, // selectedTables + null // complete_query + ); + } + + /** + * Display selection form action + * + * @param string $dataLabel Data label + * + * @return void + */ + public function displaySelectionFormAction($dataLabel = null) + { + global $goto; + $this->url_query .= '&goto=tbl_select.php&back=tbl_select.php'; + if (! isset($goto)) { + $goto = Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabTable'], + 'table' + ); + } + // Displays the table search form + $this->response->addHTML( + $this->template->render('secondary_tabs', [ + 'url_params' => [ + 'db' => $this->db, + 'table' => $this->table, + ], + 'sub_tabs' => $this->_getSubTabs(), + ]) + ); + + $column_names = $this->_columnNames; + $column_types = $this->_columnTypes; + $types = []; + if ($this->_searchType == 'replace') { + $num_cols = count($column_names); + for ($i = 0; $i < $num_cols; $i++) { + $types[$column_names[$i]] = preg_replace('@\\(.*@s', '', $column_types[$i]); + } + } + + $criteria_column_names = isset($_POST['criteriaColumnNames']) ? $_POST['criteriaColumnNames'] : null; + $keys = []; + for ($i = 0; $i < 4; $i++) { + if (isset($criteria_column_names[$i])) { + if ($criteria_column_names[$i] != 'pma_null') { + $keys[$criteria_column_names[$i]] = array_search($criteria_column_names[$i], $column_names); + } + } + } + + $this->response->addHTML( + $this->template->render('table/search/selection_form', [ + 'search_type' => $this->_searchType, + 'db' => $this->db, + 'table' => $this->table, + 'goto' => $goto, + 'self' => $this, + 'geom_column_flag' => $this->_geomColumnFlag, + 'column_names' => $column_names, + 'column_types' => $column_types, + 'types' => $types, + 'column_collations' => $this->_columnCollations, + 'data_label' => $dataLabel, + 'keys' => $keys, + 'criteria_column_names' => $criteria_column_names, + 'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'], + 'criteria_column_types' => isset($_POST['criteriaColumnTypes']) ? $_POST['criteriaColumnTypes'] : null, + 'sql_types' => $this->dbi->types, + 'max_rows' => intval($GLOBALS['cfg']['MaxRows']), + 'max_plot_limit' => ! empty($_POST['maxPlotLimit']) + ? intval($_POST['maxPlotLimit']) + : intval($GLOBALS['cfg']['maxRowPlotLimit']), + ]) + ); + } + + /** + * Range search action + * + * @return void + */ + public function rangeSearchAction() + { + $min_max = $this->getColumnMinMax($_POST['column']); + $this->response->addJSON('column_data', $min_max); + } + + /** + * Find action + * + * @return void + */ + public function findAction() + { + $useRegex = array_key_exists('useRegex', $_POST) + && $_POST['useRegex'] == 'on'; + + $preview = $this->getReplacePreview( + $_POST['columnIndex'], + $_POST['find'], + $_POST['replaceWith'], + $useRegex, + $this->_connectionCharSet + ); + $this->response->addJSON('preview', $preview); + } + + /** + * Replace action + * + * @return void + */ + public function replaceAction() + { + $this->replace( + $_POST['columnIndex'], + $_POST['findString'], + $_POST['replaceWith'], + $_POST['useRegex'], + $this->_connectionCharSet + ); + $this->response->addHTML( + Util::getMessage( + __('Your SQL query has been executed successfully.'), + null, + 'success' + ) + ); + } + + /** + * Returns HTML for previewing strings found and their replacements + * + * @param int $columnIndex index of the column + * @param string $find string to find in the column + * @param string $replaceWith string to replace with + * @param boolean $useRegex to use Regex replace or not + * @param string $charSet character set of the connection + * + * @return string HTML for previewing strings found and their replacements + */ + public function getReplacePreview( + $columnIndex, + $find, + $replaceWith, + $useRegex, + $charSet + ) { + $column = $this->_columnNames[$columnIndex]; + if ($useRegex) { + $result = $this->_getRegexReplaceRows( + $columnIndex, + $find, + $replaceWith, + $charSet + ); + } else { + $sql_query = "SELECT " + . Util::backquote($column) . "," + . " REPLACE(" + . Util::backquote($column) . ", '" . $find . "', '" + . $replaceWith + . "')," + . " COUNT(*)" + . " FROM " . Util::backquote($this->db) + . "." . Util::backquote($this->table) + . " WHERE " . Util::backquote($column) + . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we + // change the collation of the 2nd operand to a case sensitive + // binary collation to make sure that the comparison + // is case sensitive + $sql_query .= " GROUP BY " . Util::backquote($column) + . " ORDER BY " . Util::backquote($column) . " ASC"; + + $result = $this->dbi->fetchResult($sql_query, 0); + } + + return $this->template->render('table/search/replace_preview', [ + 'db' => $this->db, + 'table' => $this->table, + 'column_index' => $columnIndex, + 'find' => $find, + 'replace_with' => $replaceWith, + 'use_regex' => $useRegex, + 'result' => $result, + ]); + } + + /** + * Finds and returns Regex pattern and their replacements + * + * @param int $columnIndex index of the column + * @param string $find string to find in the column + * @param string $replaceWith string to replace with + * @param string $charSet character set of the connection + * + * @return array|bool Array containing original values, replaced values and count + */ + private function _getRegexReplaceRows( + $columnIndex, + $find, + $replaceWith, + $charSet + ) { + $column = $this->_columnNames[$columnIndex]; + $sql_query = "SELECT " + . Util::backquote($column) . "," + . " 1," // to add an extra column that will have replaced value + . " COUNT(*)" + . " FROM " . Util::backquote($this->db) + . "." . Util::backquote($this->table) + . " WHERE " . Util::backquote($column) + . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE " + . $charSet . "_bin"; // here we + // change the collation of the 2nd operand to a case sensitive + // binary collation to make sure that the comparison is case sensitive + $sql_query .= " GROUP BY " . Util::backquote($column) + . " ORDER BY " . Util::backquote($column) . " ASC"; + + $result = $this->dbi->fetchResult($sql_query, 0); + + if (is_array($result)) { + /* Iterate over possible delimiters to get one */ + $delimiters = [ + '/', + '@', + '#', + '~', + '!', + '$', + '%', + '^', + '&', + '_', + ]; + $found = false; + for ($i = 0, $l = count($delimiters); $i < $l; $i++) { + if (strpos($find, $delimiters[$i]) === false) { + $found = true; + break; + } + } + if (! $found) { + return false; + } + $find = $delimiters[$i] . $find . $delimiters[$i]; + foreach ($result as $index => $row) { + $result[$index][1] = preg_replace( + $find, + $replaceWith, + $row[0] + ); + } + } + return $result; + } + + /** + * Replaces a given string in a column with a give replacement + * + * @param int $columnIndex index of the column + * @param string $find string to find in the column + * @param string $replaceWith string to replace with + * @param boolean $useRegex to use Regex replace or not + * @param string $charSet character set of the connection + * + * @return void + */ + public function replace( + $columnIndex, + $find, + $replaceWith, + $useRegex, + $charSet + ) { + $column = $this->_columnNames[$columnIndex]; + if ($useRegex) { + $toReplace = $this->_getRegexReplaceRows( + $columnIndex, + $find, + $replaceWith, + $charSet + ); + $sql_query = "UPDATE " . Util::backquote($this->table) + . " SET " . Util::backquote($column) . " = CASE"; + if (is_array($toReplace)) { + foreach ($toReplace as $row) { + $sql_query .= "\n WHEN " . Util::backquote($column) + . " = '" . $this->dbi->escapeString($row[0]) + . "' THEN '" . $this->dbi->escapeString($row[1]) . "'"; + } + } + $sql_query .= " END" + . " WHERE " . Util::backquote($column) + . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE " + . $charSet . "_bin"; // here we + // change the collation of the 2nd operand to a case sensitive + // binary collation to make sure that the comparison + // is case sensitive + } else { + $sql_query = "UPDATE " . Util::backquote($this->table) + . " SET " . Util::backquote($column) . " =" + . " REPLACE(" + . Util::backquote($column) . ", '" . $find . "', '" + . $replaceWith + . "')" + . " WHERE " . Util::backquote($column) + . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we + // change the collation of the 2nd operand to a case sensitive + // binary collation to make sure that the comparison + // is case sensitive + } + $this->dbi->query( + $sql_query, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $GLOBALS['sql_query'] = $sql_query; + } + + /** + * Finds minimum and maximum value of a given column. + * + * @param string $column Column name + * + * @return array + */ + public function getColumnMinMax($column) + { + $sql_query = 'SELECT MIN(' . Util::backquote($column) . ') AS `min`, ' + . 'MAX(' . Util::backquote($column) . ') AS `max` ' + . 'FROM ' . Util::backquote($this->db) . '.' + . Util::backquote($this->table); + + return $this->dbi->fetchSingleRow($sql_query); + } + + /** + * Returns an array with necessary configurations to create + * sub-tabs in the table_select page. + * + * @return array Array containing configuration (icon, text, link, id, args) + * of sub-tabs + */ + private function _getSubTabs() + { + $subtabs = []; + $subtabs['search']['icon'] = 'b_search'; + $subtabs['search']['text'] = __('Table search'); + $subtabs['search']['link'] = 'tbl_select.php'; + $subtabs['search']['id'] = 'tbl_search_id'; + $subtabs['search']['args']['pos'] = 0; + + $subtabs['zoom']['icon'] = 'b_select'; + $subtabs['zoom']['link'] = 'tbl_zoom_select.php'; + $subtabs['zoom']['text'] = __('Zoom search'); + $subtabs['zoom']['id'] = 'zoom_search_id'; + + $subtabs['replace']['icon'] = 'b_find_replace'; + $subtabs['replace']['link'] = 'tbl_find_replace.php'; + $subtabs['replace']['text'] = __('Find and replace'); + $subtabs['replace']['id'] = 'find_replace_id'; + + return $subtabs; + } + + /** + * Builds the sql search query from the post parameters + * + * @return string the generated SQL query + */ + private function _buildSqlQuery() + { + $sql_query = 'SELECT '; + + // If only distinct values are needed + $is_distinct = isset($_POST['distinct']) ? 'true' : 'false'; + if ($is_distinct == 'true') { + $sql_query .= 'DISTINCT '; + } + + // if all column names were selected to display, we do a 'SELECT *' + // (more efficient and this helps prevent a problem in IE + // if one of the rows is edited and we come back to the Select results) + if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) { + $sql_query .= '* '; + } else { + $sql_query .= implode( + ', ', + Util::backquote($_POST['columnsToDisplay']) + ); + } // end if + + $sql_query .= ' FROM ' + . Util::backquote($_POST['table']); + $whereClause = $this->_generateWhereClause(); + $sql_query .= $whereClause; + + // if the search results are to be ordered + if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') { + $sql_query .= ' ORDER BY ' + . Util::backquote($_POST['orderByColumn']) + . ' ' . $_POST['order']; + } // end if + return $sql_query; + } + + /** + * Provides a column's type, collation, operators list, and criteria value + * to display in table search form + * + * @param integer $search_index Row number in table search form + * @param integer $column_index Column index in ColumnNames array + * + * @return array Array containing column's properties + */ + public function getColumnProperties($search_index, $column_index) + { + $selected_operator = (isset($_POST['criteriaColumnOperators'][$search_index]) + ? $_POST['criteriaColumnOperators'][$search_index] : ''); + $entered_value = (isset($_POST['criteriaValues']) + ? $_POST['criteriaValues'] : ''); + $titles = [ + 'Browse' => Util::getIcon( + 'b_browse', + __('Browse foreign values') + ), + ]; + //Gets column's type and collation + $type = $this->_columnTypes[$column_index]; + $collation = $this->_columnCollations[$column_index]; + //Gets column's comparison operators depending on column type + $typeOperators = $this->dbi->types->getTypeOperatorsHtml( + preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]), + $this->_columnNullFlags[$column_index], + $selected_operator + ); + $func = $this->template->render('table/search/column_comparison_operators', [ + 'search_index' => $search_index, + 'type_operators' => $typeOperators, + ]); + //Gets link to browse foreign data(if any) and criteria inputbox + $foreignData = $this->relation->getForeignData( + $this->_foreigners, + $this->_columnNames[$column_index], + false, + '', + '' + ); + $value = $this->template->render('table/search/input_box', [ + 'str' => '', + 'column_type' => (string) $type, + 'column_id' => 'fieldID_', + 'in_zoom_search_edit' => false, + 'foreigners' => $this->_foreigners, + 'column_name' => $this->_columnNames[$column_index], + 'column_name_hash' => md5($this->_columnNames[$column_index]), + 'foreign_data' => $foreignData, + 'table' => $this->table, + 'column_index' => $search_index, + 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'], + 'criteria_values' => $entered_value, + 'db' => $this->db, + 'titles' => $titles, + 'in_fbs' => true, + ]); + return [ + 'type' => $type, + 'collation' => $collation, + 'func' => $func, + 'value' => $value, + ]; + } + + /** + * Generates the where clause for the SQL search query to be executed + * + * @return string the generated where clause + */ + private function _generateWhereClause() + { + if (isset($_POST['customWhereClause']) + && trim($_POST['customWhereClause']) != '' + ) { + return ' WHERE ' . $_POST['customWhereClause']; + } + + // If there are no search criteria set or no unary criteria operators, + // return + if (! isset($_POST['criteriaValues']) + && ! isset($_POST['criteriaColumnOperators']) + && ! isset($_POST['geom_func']) + ) { + return ''; + } + + // else continue to form the where clause from column criteria values + $fullWhereClause = []; + foreach ($_POST['criteriaColumnOperators'] as $column_index => $operator) { + $unaryFlag = $this->dbi->types->isUnaryOperator($operator); + $tmp_geom_func = isset($_POST['geom_func'][$column_index]) + ? $_POST['geom_func'][$column_index] : null; + + $whereClause = $this->_getWhereClause( + $_POST['criteriaValues'][$column_index], + $_POST['criteriaColumnNames'][$column_index], + $_POST['criteriaColumnTypes'][$column_index], + $operator, + $unaryFlag, + $tmp_geom_func + ); + + if ($whereClause) { + $fullWhereClause[] = $whereClause; + } + } // end foreach + + if (! empty($fullWhereClause)) { + return ' WHERE ' . implode(' AND ', $fullWhereClause); + } + return ''; + } + + /** + * Return the where clause in case column's type is ENUM. + * + * @param mixed $criteriaValues Search criteria input + * @param string $func_type Search function/operator + * + * @return string part of where clause. + */ + private function _getEnumWhereClause($criteriaValues, $func_type) + { + if (! is_array($criteriaValues)) { + $criteriaValues = explode(',', $criteriaValues); + } + $enum_selected_count = count($criteriaValues); + if ($func_type == '=' && $enum_selected_count > 1) { + $func_type = 'IN'; + $parens_open = '('; + $parens_close = ')'; + } elseif ($func_type == '!=' && $enum_selected_count > 1) { + $func_type = 'NOT IN'; + $parens_open = '('; + $parens_close = ')'; + } else { + $parens_open = ''; + $parens_close = ''; + } + $enum_where = '\'' + . $this->dbi->escapeString($criteriaValues[0]) . '\''; + for ($e = 1; $e < $enum_selected_count; $e++) { + $enum_where .= ', \'' + . $this->dbi->escapeString($criteriaValues[$e]) . '\''; + } + + return ' ' . $func_type . ' ' . $parens_open + . $enum_where . $parens_close; + } + + /** + * Return the where clause for a geometrical column. + * + * @param mixed $criteriaValues Search criteria input + * @param string $names Name of the column on which search is submitted + * @param string $func_type Search function/operator + * @param string $types Type of the field + * @param bool $geom_func Whether geometry functions should be applied + * + * @return string part of where clause. + */ + private function _getGeomWhereClause( + $criteriaValues, + $names, + $func_type, + $types, + $geom_func = null + ) { + $geom_unary_functions = [ + 'IsEmpty' => 1, + 'IsSimple' => 1, + 'IsRing' => 1, + 'IsClosed' => 1, + ]; + $where = ''; + + // Get details about the geometry functions + $geom_funcs = Util::getGISFunctions($types, true, false); + + // If the function takes multiple parameters + if (strpos($func_type, "IS NULL") !== false || strpos($func_type, "IS NOT NULL") !== false) { + return Util::backquote($names) . " " . $func_type; + } elseif ($geom_funcs[$geom_func]['params'] > 1) { + // create gis data from the criteria input + $gis_data = Util::createGISData($criteriaValues, $this->dbi->getVersion()); + return $geom_func . '(' . Util::backquote($names) + . ', ' . $gis_data . ')'; + } + + // New output type is the output type of the function being applied + $type = $geom_funcs[$geom_func]['type']; + $geom_function_applied = $geom_func + . '(' . Util::backquote($names) . ')'; + + // If the where clause is something like 'IsEmpty(`spatial_col_name`)' + if (isset($geom_unary_functions[$geom_func]) + && trim($criteriaValues) == '' + ) { + $where = $geom_function_applied; + } elseif (in_array($type, Util::getGISDatatypes()) + && ! empty($criteriaValues) + ) { + // create gis data from the criteria input + $gis_data = Util::createGISData($criteriaValues, $this->dbi->getVersion()); + $where = $geom_function_applied . " " . $func_type . " " . $gis_data; + } elseif (strlen($criteriaValues) > 0) { + $where = $geom_function_applied . " " + . $func_type . " '" . $criteriaValues . "'"; + } + return $where; + } + + /** + * Return the where clause for query generation based on the inputs provided. + * + * @param mixed $criteriaValues Search criteria input + * @param string $names Name of the column on which search is submitted + * @param string $types Type of the field + * @param string $func_type Search function/operator + * @param bool $unaryFlag Whether operator unary or not + * @param bool $geom_func Whether geometry functions should be applied + * + * @return string generated where clause. + */ + private function _getWhereClause( + $criteriaValues, + $names, + $types, + $func_type, + $unaryFlag, + $geom_func = null + ) { + // If geometry function is set + if (! empty($geom_func)) { + return $this->_getGeomWhereClause( + $criteriaValues, + $names, + $func_type, + $types, + $geom_func + ); + } + + $backquoted_name = Util::backquote($names); + $where = ''; + if ($unaryFlag) { + $where = $backquoted_name . ' ' . $func_type; + } elseif (strncasecmp($types, 'enum', 4) == 0 && (! empty($criteriaValues) || $criteriaValues[0] === '0')) { + $where = $backquoted_name; + $where .= $this->_getEnumWhereClause($criteriaValues, $func_type); + } elseif ($criteriaValues != '') { + // For these types we quote the value. Even if it's another type + // (like INT), for a LIKE we always quote the value. MySQL converts + // strings to numbers and numbers to strings as necessary + // during the comparison + if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types) + || mb_strpos(' ' . $func_type, 'LIKE') + ) { + $quot = '\''; + } else { + $quot = ''; + } + + // LIKE %...% + if ($func_type == 'LIKE %...%') { + $func_type = 'LIKE'; + $criteriaValues = '%' . $criteriaValues . '%'; + } + if ($func_type == 'REGEXP ^...$') { + $func_type = 'REGEXP'; + $criteriaValues = '^' . $criteriaValues . '$'; + } + + if ('IN (...)' != $func_type + && 'NOT IN (...)' != $func_type + && 'BETWEEN' != $func_type + && 'NOT BETWEEN' != $func_type + ) { + return $backquoted_name . ' ' . $func_type . ' ' . $quot + . $this->dbi->escapeString($criteriaValues) . $quot; + } + $func_type = str_replace(' (...)', '', $func_type); + + //Don't explode if this is already an array + //(Case for (NOT) IN/BETWEEN.) + if (is_array($criteriaValues)) { + $values = $criteriaValues; + } else { + $values = explode(',', $criteriaValues); + } + // quote values one by one + $emptyKey = false; + foreach ($values as $key => &$value) { + if ('' === $value) { + $emptyKey = $key; + $value = 'NULL'; + continue; + } + $value = $quot . $this->dbi->escapeString(trim($value)) + . $quot; + } + + if ('BETWEEN' == $func_type || 'NOT BETWEEN' == $func_type) { + $where = $backquoted_name . ' ' . $func_type . ' ' + . (isset($values[0]) ? $values[0] : '') + . ' AND ' . (isset($values[1]) ? $values[1] : ''); + } else { //[NOT] IN + if (false !== $emptyKey) { + unset($values[$emptyKey]); + } + $wheres = []; + if (! empty($values)) { + $wheres[] = $backquoted_name . ' ' . $func_type + . ' (' . implode(',', $values) . ')'; + } + if (false !== $emptyKey) { + $wheres[] = $backquoted_name . ' IS NULL'; + } + $where = implode(' OR ', $wheres); + if (1 < count($wheres)) { + $where = '(' . $where . ')'; + } + } + } // end if + + return $where; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/SqlController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/SqlController.php new file mode 100644 index 0000000..a3975ac --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/SqlController.php @@ -0,0 +1,53 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\SqlController + * + * @package PhpMyAdmin\Controllers\Table + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\Config\PageSettings; +use PhpMyAdmin\SqlQueryForm; + +/** + * Table SQL executor + * @package PhpMyAdmin\Controllers\Table + */ +class SqlController extends AbstractController +{ + /** + * @param array $params Request parameters + * @param SqlQueryForm $sqlQueryForm SqlQueryForm instance + * + * @return string HTML + */ + public function index(array $params, SqlQueryForm $sqlQueryForm): string + { + global $url_query, $err_url, $goto, $back; + + PageSettings::showGroup('Sql'); + + require ROOT_PATH . 'libraries/tbl_common.inc.php'; + + $url_query .= '&goto=tbl_sql.php&back=tbl_sql.php'; + $err_url = 'tbl_sql.php' . $err_url; + + /** + * After a syntax error, we return to this script + * with the typed query in the textarea. + */ + $goto = 'tbl_sql.php'; + $back = 'tbl_sql.php'; + + return $sqlQueryForm->getHtml( + $params['sql_query'] ?? true, + false, + isset($params['delimiter']) + ? htmlspecialchars($params['delimiter']) + : ';' + ); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Controllers/Table/StructureController.php b/srcs/phpmyadmin/libraries/classes/Controllers/Table/StructureController.php new file mode 100644 index 0000000..7f59013 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Controllers/Table/StructureController.php @@ -0,0 +1,1648 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Controllers\Table\StructureController + * + * @package PhpMyAdmin\Controllers + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Controllers\Table; + +use PhpMyAdmin\CentralColumns; +use PhpMyAdmin\Charsets; +use PhpMyAdmin\CheckUserPrivileges; +use PhpMyAdmin\Config\PageSettings; +use PhpMyAdmin\Core; +use PhpMyAdmin\CreateAddField; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Engines\Innodb; +use PhpMyAdmin\Index; +use PhpMyAdmin\Message; +use PhpMyAdmin\ParseAnalyze; +use PhpMyAdmin\Partition; +use PhpMyAdmin\Relation; +use PhpMyAdmin\Response; +use PhpMyAdmin\Sql; +use PhpMyAdmin\SqlParser\Context; +use PhpMyAdmin\SqlParser\Parser; +use PhpMyAdmin\SqlParser\Statements\CreateStatement; +use PhpMyAdmin\StorageEngine; +use PhpMyAdmin\Table; +use PhpMyAdmin\TablePartitionDefinition; +use PhpMyAdmin\Template; +use PhpMyAdmin\Tracker; +use PhpMyAdmin\Transformations; +use PhpMyAdmin\Url; +use PhpMyAdmin\Util; +use stdClass; +use Symfony\Component\DependencyInjection\ContainerBuilder; + +/** + * Handles table structure logic + * + * @package PhpMyAdmin\Controllers + */ +class StructureController extends AbstractController +{ + /** + * @var Table The table object + */ + protected $table_obj; + /** + * @var string The URL query string + */ + protected $_url_query; + /** + * @var bool DB is information_schema + */ + protected $_db_is_system_schema; + /** + * @var bool Table is a view + */ + protected $_tbl_is_view; + /** + * @var string Table storage engine + */ + protected $_tbl_storage_engine; + /** + * @var int Number of rows + */ + protected $_table_info_num_rows; + /** + * @var string Table collation + */ + protected $_tbl_collation; + /** + * @var array Show table info + */ + protected $_showtable; + + /** + * @var CreateAddField + */ + private $createAddField; + + /** + * @var Relation + */ + private $relation; + + /** + * @var Transformations + */ + private $transformations; + + /** + * StructureController constructor + * + * @param Response $response Response object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Template $template Template object + * @param string $db Database name + * @param string $table Table name + * @param bool $db_is_system_schema DB is information_schema + * @param bool $tbl_is_view Table is a view + * @param string $tbl_storage_engine Table storage engine + * @param int $table_info_num_rows Number of rows + * @param string $tbl_collation Table collation + * @param array $showtable Show table info + * @param Relation $relation Relation instance + * @param Transformations $transformations Transformations instance + * @param CreateAddField $createAddField CreateAddField instance + */ + public function __construct( + $response, + $dbi, + Template $template, + $db, + $table, + $db_is_system_schema, + $tbl_is_view, + $tbl_storage_engine, + $table_info_num_rows, + $tbl_collation, + $showtable, + Relation $relation, + Transformations $transformations, + CreateAddField $createAddField + ) { + parent::__construct($response, $dbi, $template, $db, $table); + + $this->_db_is_system_schema = $db_is_system_schema; + $this->_url_query = Url::getCommonRaw(['db' => $db, 'table' => $table]); + $this->_tbl_is_view = $tbl_is_view; + $this->_tbl_storage_engine = $tbl_storage_engine; + $this->_table_info_num_rows = $table_info_num_rows; + $this->_tbl_collation = $tbl_collation; + $this->_showtable = $showtable; + $this->table_obj = $this->dbi->getTable($this->db, $this->table); + + $this->createAddField = $createAddField; + $this->relation = $relation; + $this->transformations = $transformations; + } + + /** + * Index action + * + * @param ContainerBuilder $containerBuilder ContainerBuilder instance + * + * @return void + */ + public function indexAction(ContainerBuilder $containerBuilder): void + { + global $sql_query; + + PageSettings::showGroup('TableStructure'); + + $checkUserPrivileges = new CheckUserPrivileges($this->dbi); + $checkUserPrivileges->getPrivileges(); + + $this->response->getHeader()->getScripts()->addFiles( + [ + 'table/structure.js', + 'indexes.js', + ] + ); + + /** + * Handle column moving + */ + if (isset($_POST['move_columns']) + && is_array($_POST['move_columns']) + && $this->response->isAjax() + ) { + $this->moveColumns(); + return; + } + + /** + * handle MySQL reserved words columns check + */ + if (isset($_POST['reserved_word_check'])) { + if ($GLOBALS['cfg']['ReservedWordDisableWarning'] === false) { + $columns_names = $_POST['field_name']; + $reserved_keywords_names = []; + foreach ($columns_names as $column) { + if (Context::isKeyword(trim($column), true)) { + $reserved_keywords_names[] = trim($column); + } + } + if (Context::isKeyword(trim($this->table), true)) { + $reserved_keywords_names[] = trim($this->table); + } + if (count($reserved_keywords_names) === 0) { + $this->response->setRequestStatus(false); + } + $this->response->addJSON( + 'message', + sprintf( + _ngettext( + 'The name \'%s\' is a MySQL reserved keyword.', + 'The names \'%s\' are MySQL reserved keywords.', + count($reserved_keywords_names) + ), + implode(',', $reserved_keywords_names) + ) + ); + } else { + $this->response->setRequestStatus(false); + } + return; + } + /** + * A click on Change has been made for one column + */ + if (isset($_GET['change_column'])) { + $this->displayHtmlForColumnChange(null, 'tbl_structure.php', $containerBuilder); + return; + } + + /** + * Adding or editing partitioning of the table + */ + if (isset($_POST['edit_partitioning']) + && ! isset($_POST['save_partitioning']) + ) { + $this->displayHtmlForPartitionChange(); + return; + } + + /** + * handle multiple field commands if required + * + * submit_mult_*_x comes from IE if <input type="img" ...> is used + */ + $submit_mult = $this->getMultipleFieldCommandType(); + + if (! empty($submit_mult)) { + if (isset($_POST['selected_fld'])) { + if ($submit_mult == 'browse') { + // browsing the table displaying only selected columns + $this->displayTableBrowseForSelectedColumns( + $GLOBALS['goto'], + $GLOBALS['pmaThemeImage'] + ); + } else { + // handle multiple field commands + // handle confirmation of deleting multiple columns + $action = 'tbl_structure.php'; + $GLOBALS['selected'] = $_POST['selected_fld']; + list( + $what_ret, $query_type_ret, $is_unset_submit_mult, + $mult_btn_ret, $centralColsError + ) + = $this->getDataForSubmitMult( + $submit_mult, + $_POST['selected_fld'], + $action, + $containerBuilder + ); + //update the existing variables + // todo: refactor mult_submits.inc.php such as + // below globals are not needed anymore + if (isset($what_ret)) { + $GLOBALS['what'] = $what_ret; + global $what; + } + if (isset($query_type_ret)) { + $GLOBALS['query_type'] = $query_type_ret; + global $query_type; + } + if ($is_unset_submit_mult) { + unset($submit_mult); + } + if (isset($mult_btn_ret)) { + $GLOBALS['mult_btn'] = $mult_btn_ret; + global $mult_btn; + } + include ROOT_PATH . 'libraries/mult_submits.inc.php'; + /** + * if $submit_mult == 'change', execution will have stopped + * at this point + */ + if (empty($message)) { + $message = Message::success(); + } + $this->response->addHTML( + Util::getMessage($message, $sql_query) + ); + } + } else { + $this->response->setRequestStatus(false); + $message = Message::error(__('No column selected.')); + $this->response->addJSON('message', $message); + } + } + + /** + * Modifications have been submitted -> updates the table + */ + if (isset($_POST['do_save_data'])) { + $regenerate = $this->updateColumns(); + if (! $regenerate) { + // continue to show the table's structure + unset($_POST['selected']); + } + } + + /** + * Modifications to the partitioning have been submitted -> updates the table + */ + if (isset($_POST['save_partitioning'])) { + $this->updatePartitioning(); + } + + /** + * Adding indexes + */ + if (isset($_POST['add_key']) + || isset($_POST['partition_maintenance']) + ) { + //todo: set some variables for sql.php include, to be eliminated + //after refactoring sql.php + $db = $this->db; + $table = $this->table; + $sql_query = $GLOBALS['sql_query']; + $cfg = $GLOBALS['cfg']; + $pmaThemeImage = $GLOBALS['pmaThemeImage']; + include ROOT_PATH . 'sql.php'; + $GLOBALS['reload'] = true; + } + + /** + * Gets the relation settings + */ + $cfgRelation = $this->relation->getRelationsParam(); + + /** + * Runs common work + */ + // set db, table references, for require_once that follows + // got to be eliminated in long run + $db = &$this->db; + $table = &$this->table; + $url_params = []; + include_once ROOT_PATH . 'libraries/tbl_common.inc.php'; + $this->_db_is_system_schema = $db_is_system_schema; + $this->_url_query = Url::getCommonRaw([ + 'db' => $db, + 'table' => $table, + 'goto' => 'tbl_structure.php', + 'back' => 'tbl_structure.php', + ]); + /* The url_params array is initialized in above include */ + $url_params['goto'] = 'tbl_structure.php'; + $url_params['back'] = 'tbl_structure.php'; + + // 2. Gets table keys and retains them + // @todo should be: $server->db($db)->table($table)->primary() + $primary = Index::getPrimary($this->table, $this->db); + $columns_with_index = $this->dbi + ->getTable($this->db, $this->table) + ->getColumnsWithIndex( + Index::UNIQUE | Index::INDEX | Index::SPATIAL + | Index::FULLTEXT + ); + $columns_with_unique_index = $this->dbi + ->getTable($this->db, $this->table) + ->getColumnsWithIndex(Index::UNIQUE); + + // 3. Get fields + $fields = (array) $this->dbi->getColumns( + $this->db, + $this->table, + null, + true + ); + + //display table structure + $this->response->addHTML( + $this->displayStructure( + $cfgRelation, + $columns_with_unique_index, + $url_params, + $primary, + $fields, + $columns_with_index + ) + ); + } + + /** + * Moves columns in the table's structure based on $_REQUEST + * + * @return void + */ + protected function moveColumns() + { + $this->dbi->selectDb($this->db); + + /* + * load the definitions for all columns + */ + $columns = $this->dbi->getColumnsFull($this->db, $this->table); + $column_names = array_keys($columns); + $changes = []; + + // @see https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-102/#information-schema + $usesLiteralNull = $this->dbi->isMariaDB() && $this->dbi->getVersion() >= 100200; + $defaultNullValue = $usesLiteralNull ? 'NULL' : null; + // move columns from first to last + for ($i = 0, $l = count($_POST['move_columns']); $i < $l; $i++) { + $column = $_POST['move_columns'][$i]; + // is this column already correctly placed? + if ($column_names[$i] == $column) { + continue; + } + + // it is not, let's move it to index $i + $data = $columns[$column]; + $extracted_columnspec = Util::extractColumnSpec($data['Type']); + if (isset($data['Extra']) + && $data['Extra'] == 'on update CURRENT_TIMESTAMP' + ) { + $extracted_columnspec['attribute'] = $data['Extra']; + unset($data['Extra']); + } + $current_timestamp = ($data['Type'] == 'timestamp' + || $data['Type'] == 'datetime') + && ($data['Default'] == 'CURRENT_TIMESTAMP' + || $data['Default'] == 'current_timestamp()'); + + // @see https://mariadb.com/kb/en/library/information-schema-columns-table/#examples + if ($data['Null'] === 'YES' && in_array($data['Default'], [$defaultNullValue, null])) { + $default_type = 'NULL'; + } elseif ($current_timestamp) { + $default_type = 'CURRENT_TIMESTAMP'; + } elseif ($data['Default'] === null) { + $default_type = 'NONE'; + } else { + $default_type = 'USER_DEFINED'; + } + + $virtual = [ + 'VIRTUAL', + 'PERSISTENT', + 'VIRTUAL GENERATED', + 'STORED GENERATED', + ]; + $data['Virtuality'] = ''; + $data['Expression'] = ''; + if (isset($data['Extra']) && in_array($data['Extra'], $virtual)) { + $data['Virtuality'] = str_replace(' GENERATED', '', $data['Extra']); + $expressions = $this->table_obj->getColumnGenerationExpression($column); + $data['Expression'] = $expressions[$column]; + } + + $changes[] = 'CHANGE ' . Table::generateAlter( + $column, + $column, + mb_strtoupper($extracted_columnspec['type']), + $extracted_columnspec['spec_in_brackets'], + $extracted_columnspec['attribute'], + isset($data['Collation']) ? $data['Collation'] : '', + $data['Null'] === 'YES' ? 'YES' : 'NO', + $default_type, + $current_timestamp ? '' : $data['Default'], + isset($data['Extra']) && $data['Extra'] !== '' ? $data['Extra'] + : false, + isset($data['COLUMN_COMMENT']) && $data['COLUMN_COMMENT'] !== '' + ? $data['COLUMN_COMMENT'] : false, + $data['Virtuality'], + $data['Expression'], + $i === 0 ? '-first' : $column_names[$i - 1] + ); + // update current column_names array, first delete old position + for ($j = 0, $ll = count($column_names); $j < $ll; $j++) { + if ($column_names[$j] == $column) { + unset($column_names[$j]); + } + } + // insert moved column + array_splice($column_names, $i, 0, $column); + } + if (empty($changes) && ! isset($_REQUEST['preview_sql'])) { // should never happen + $this->response->setRequestStatus(false); + return; + } + // query for moving the columns + $sql_query = sprintf( + 'ALTER TABLE %s %s', + Util::backquote($this->table), + implode(', ', $changes) + ); + + if (isset($_REQUEST['preview_sql'])) { // preview sql + $this->response->addJSON( + 'sql_data', + $this->template->render('preview_sql', [ + 'query_data' => $sql_query, + ]) + ); + } else { // move column + $this->dbi->tryQuery($sql_query); + $tmp_error = $this->dbi->getError(); + if ($tmp_error) { + $this->response->setRequestStatus(false); + $this->response->addJSON('message', Message::error($tmp_error)); + } else { + $message = Message::success( + __('The columns have been moved successfully.') + ); + $this->response->addJSON('message', $message); + $this->response->addJSON('columns', $column_names); + } + } + } + + /** + * Displays HTML for changing one or more columns + * + * @param array $selected the selected columns + * @param string $action target script to call + * @param ContainerBuilder $containerBuilder Container builder instance (Used in tbl_columns_definition_form.inc.php) + * + * @return void + */ + protected function displayHtmlForColumnChange($selected, $action, ContainerBuilder $containerBuilder) + { + // $selected comes from mult_submits.inc.php + if (empty($selected)) { + $selected[] = $_REQUEST['field']; + $selected_cnt = 1; + } else { // from a multiple submit + $selected_cnt = count($selected); + } + + /** + * @todo optimize in case of multiple fields to modify + */ + $fields_meta = []; + for ($i = 0; $i < $selected_cnt; $i++) { + $value = $this->dbi->getColumns( + $this->db, + $this->table, + $this->dbi->escapeString($selected[$i]), + true + ); + if (count($value) === 0) { + $message = Message::error( + __('Failed to get description of column %s!') + ); + $message->addParam($selected[$i]); + $this->response->addHTML($message); + } else { + $fields_meta[] = $value; + } + } + $num_fields = count($fields_meta); + // set these globals because tbl_columns_definition_form.inc.php + // verifies them + // @todo: refactor tbl_columns_definition_form.inc.php so that it uses + // protected function params + $GLOBALS['action'] = $action; + $GLOBALS['num_fields'] = $num_fields; + + /** + * Form for changing properties. + */ + $checkUserPrivileges = new CheckUserPrivileges($this->dbi); + $checkUserPrivileges->getPrivileges(); + + include ROOT_PATH . 'libraries/tbl_columns_definition_form.inc.php'; + } + + /** + * Displays HTML for partition change + * + * @return void + */ + protected function displayHtmlForPartitionChange() + { + $partitionDetails = null; + if (! isset($_POST['partition_by'])) { + $partitionDetails = $this->_extractPartitionDetails(); + } + + $partitionDetails = TablePartitionDefinition::getDetails($partitionDetails); + $this->response->addHTML( + $this->template->render('table/structure/partition_definition_form', [ + 'db' => $this->db, + 'table' => $this->table, + 'partition_details' => $partitionDetails, + ]) + ); + } + + /** + * Extracts partition details from CREATE TABLE statement + * + * @return array[]|null array of partition details + */ + private function _extractPartitionDetails() + { + $createTable = (new Table($this->table, $this->db))->showCreate(); + if (! $createTable) { + return null; + } + + $parser = new Parser($createTable); + /** + * @var CreateStatement $stmt + */ + $stmt = $parser->statements[0]; + + $partitionDetails = []; + + $partitionDetails['partition_by'] = ''; + $partitionDetails['partition_expr'] = ''; + $partitionDetails['partition_count'] = ''; + + if (! empty($stmt->partitionBy)) { + $openPos = strpos($stmt->partitionBy, "("); + $closePos = strrpos($stmt->partitionBy, ")"); + + $partitionDetails['partition_by'] + = trim(substr($stmt->partitionBy, 0, $openPos)); + $partitionDetails['partition_expr'] + = trim(substr($stmt->partitionBy, $openPos + 1, $closePos - ($openPos + 1))); + if (isset($stmt->partitionsNum)) { + $count = $stmt->partitionsNum; + } else { + $count = count($stmt->partitions); + } + $partitionDetails['partition_count'] = $count; + } + + $partitionDetails['subpartition_by'] = ''; + $partitionDetails['subpartition_expr'] = ''; + $partitionDetails['subpartition_count'] = ''; + + if (! empty($stmt->subpartitionBy)) { + $openPos = strpos($stmt->subpartitionBy, "("); + $closePos = strrpos($stmt->subpartitionBy, ")"); + + $partitionDetails['subpartition_by'] + = trim(substr($stmt->subpartitionBy, 0, $openPos)); + $partitionDetails['subpartition_expr'] + = trim(substr($stmt->subpartitionBy, $openPos + 1, $closePos - ($openPos + 1))); + if (isset($stmt->subpartitionsNum)) { + $count = $stmt->subpartitionsNum; + } else { + $count = count($stmt->partitions[0]->subpartitions); + } + $partitionDetails['subpartition_count'] = $count; + } + + // Only LIST and RANGE type parameters allow subpartitioning + $partitionDetails['can_have_subpartitions'] + = $partitionDetails['partition_count'] > 1 + && ($partitionDetails['partition_by'] == 'RANGE' + || $partitionDetails['partition_by'] == 'RANGE COLUMNS' + || $partitionDetails['partition_by'] == 'LIST' + || $partitionDetails['partition_by'] == 'LIST COLUMNS'); + + // Values are specified only for LIST and RANGE type partitions + $partitionDetails['value_enabled'] = isset($partitionDetails['partition_by']) + && ($partitionDetails['partition_by'] == 'RANGE' + || $partitionDetails['partition_by'] == 'RANGE COLUMNS' + || $partitionDetails['partition_by'] == 'LIST' + || $partitionDetails['partition_by'] == 'LIST COLUMNS'); + + $partitionDetails['partitions'] = []; + + for ($i = 0, $iMax = (int) $partitionDetails['partition_count']; $i < $iMax; $i++) { + if (! isset($stmt->partitions[$i])) { + $partitionDetails['partitions'][$i] = [ + 'name' => 'p' . $i, + 'value_type' => '', + 'value' => '', + 'engine' => '', + 'comment' => '', + 'data_directory' => '', + 'index_directory' => '', + 'max_rows' => '', + 'min_rows' => '', + 'tablespace' => '', + 'node_group' => '', + ]; + } else { + $p = $stmt->partitions[$i]; + $type = $p->type; + $expr = trim((string) $p->expr, '()'); + if ($expr == 'MAXVALUE') { + $type .= ' MAXVALUE'; + $expr = ''; + } + $partitionDetails['partitions'][$i] = [ + 'name' => $p->name, + 'value_type' => $type, + 'value' => $expr, + 'engine' => $p->options->has('ENGINE', true), + 'comment' => trim((string) $p->options->has('COMMENT', true), "'"), + 'data_directory' => trim((string) $p->options->has('DATA DIRECTORY', true), "'"), + 'index_directory' => trim((string) $p->options->has('INDEX_DIRECTORY', true), "'"), + 'max_rows' => $p->options->has('MAX_ROWS', true), + 'min_rows' => $p->options->has('MIN_ROWS', true), + 'tablespace' => $p->options->has('TABLESPACE', true), + 'node_group' => $p->options->has('NODEGROUP', true), + ]; + } + + $partition =& $partitionDetails['partitions'][$i]; + $partition['prefix'] = 'partitions[' . $i . ']'; + + if ($partitionDetails['subpartition_count'] > 1) { + $partition['subpartition_count'] = $partitionDetails['subpartition_count']; + $partition['subpartitions'] = []; + + for ($j = 0, $jMax = (int) $partitionDetails['subpartition_count']; $j < $jMax; $j++) { + if (! isset($stmt->partitions[$i]->subpartitions[$j])) { + $partition['subpartitions'][$j] = [ + 'name' => $partition['name'] . '_s' . $j, + 'engine' => '', + 'comment' => '', + 'data_directory' => '', + 'index_directory' => '', + 'max_rows' => '', + 'min_rows' => '', + 'tablespace' => '', + 'node_group' => '', + ]; + } else { + $sp = $stmt->partitions[$i]->subpartitions[$j]; + $partition['subpartitions'][$j] = [ + 'name' => $sp->name, + 'engine' => $sp->options->has('ENGINE', true), + 'comment' => trim($sp->options->has('COMMENT', true), "'"), + 'data_directory' => trim($sp->options->has('DATA DIRECTORY', true), "'"), + 'index_directory' => trim($sp->options->has('INDEX_DIRECTORY', true), "'"), + 'max_rows' => $sp->options->has('MAX_ROWS', true), + 'min_rows' => $sp->options->has('MIN_ROWS', true), + 'tablespace' => $sp->options->has('TABLESPACE', true), + 'node_group' => $sp->options->has('NODEGROUP', true), + ]; + } + + $subpartition =& $partition['subpartitions'][$j]; + $subpartition['prefix'] = 'partitions[' . $i . ']' + . '[subpartitions][' . $j . ']'; + } + } + } + + return $partitionDetails; + } + + /** + * Update the table's partitioning based on $_REQUEST + * + * @return void + */ + protected function updatePartitioning() + { + $sql_query = "ALTER TABLE " . Util::backquote($this->table) . " " + . $this->createAddField->getPartitionsDefinition(); + + // Execute alter query + $result = $this->dbi->tryQuery($sql_query); + + if ($result !== false) { + $message = Message::success( + __('Table %1$s has been altered successfully.') + ); + $message->addParam($this->table); + $this->response->addHTML( + Util::getMessage($message, $sql_query, 'success') + ); + } else { + $this->response->setRequestStatus(false); + $this->response->addJSON( + 'message', + Message::rawError( + __('Query error') . ':<br>' . $this->dbi->getError() + ) + ); + } + } + + /** + * Function to get the type of command for multiple field handling + * + * @return string|null + */ + protected function getMultipleFieldCommandType() + { + $types = [ + 'change', + 'drop', + 'primary', + 'index', + 'unique', + 'spatial', + 'fulltext', + 'browse', + ]; + + foreach ($types as $type) { + if (isset($_POST['submit_mult_' . $type . '_x'])) { + return $type; + } + } + + if (isset($_POST['submit_mult'])) { + return $_POST['submit_mult']; + } elseif (isset($_POST['mult_btn']) + && $_POST['mult_btn'] == __('Yes') + ) { + if (isset($_POST['selected'])) { + $_POST['selected_fld'] = $_POST['selected']; + } + return 'row_delete'; + } + + return null; + } + + /** + * Function to display table browse for selected columns + * + * @param string $goto goto page url + * @param string $pmaThemeImage URI of the pma theme image + * + * @return void + */ + protected function displayTableBrowseForSelectedColumns($goto, $pmaThemeImage) + { + $GLOBALS['active_page'] = 'sql.php'; + $fields = []; + foreach ($_POST['selected_fld'] as $sval) { + $fields[] = Util::backquote($sval); + } + $sql_query = sprintf( + 'SELECT %s FROM %s.%s', + implode(', ', $fields), + Util::backquote($this->db), + Util::backquote($this->table) + ); + + // Parse and analyze the query + $db = &$this->db; + list( + $analyzed_sql_results, + $db, + ) = ParseAnalyze::sqlQuery($sql_query, $db); + // @todo: possibly refactor + extract($analyzed_sql_results); + + $sql = new Sql(); + $this->response->addHTML( + $sql->executeQueryAndGetQueryResponse( + isset($analyzed_sql_results) ? $analyzed_sql_results : '', + false, // is_gotofile + $this->db, // db + $this->table, // table + null, // find_real_end + null, // sql_query_for_bookmark + null, // extra_data + null, // message_to_show + null, // message + null, // sql_data + $goto, // goto + $pmaThemeImage, // pmaThemeImage + null, // disp_query + null, // disp_message + null, // query_type + $sql_query, // sql_query + null, // selectedTables + null // complete_query + ) + ); + } + + /** + * Update the table's structure based on $_REQUEST + * + * @return boolean true if error occurred + * + */ + protected function updateColumns() + { + $err_url = 'tbl_structure.php' . Url::getCommon( + [ + 'db' => $this->db, + 'table' => $this->table, + ] + ); + $regenerate = false; + $field_cnt = count($_POST['field_name']); + $changes = []; + $adjust_privileges = []; + $columns_with_index = $this->dbi + ->getTable($this->db, $this->table) + ->getColumnsWithIndex( + Index::PRIMARY | Index::UNIQUE + ); + for ($i = 0; $i < $field_cnt; $i++) { + if (! $this->columnNeedsAlterTable($i)) { + continue; + } + + $changes[] = 'CHANGE ' . Table::generateAlter( + Util::getValueByKey($_POST, "field_orig.${i}", ''), + $_POST['field_name'][$i], + $_POST['field_type'][$i], + $_POST['field_length'][$i], + $_POST['field_attribute'][$i], + Util::getValueByKey($_POST, "field_collation.${i}", ''), + Util::getValueByKey($_POST, "field_null.${i}", 'NO'), + $_POST['field_default_type'][$i], + $_POST['field_default_value'][$i], + Util::getValueByKey($_POST, "field_extra.${i}", false), + Util::getValueByKey($_POST, "field_comments.${i}", ''), + Util::getValueByKey($_POST, "field_virtuality.${i}", ''), + Util::getValueByKey($_POST, "field_expression.${i}", ''), + Util::getValueByKey($_POST, "field_move_to.${i}", ''), + $columns_with_index + ); + + // find the remembered sort expression + $sorted_col = $this->table_obj->getUiProp( + Table::PROP_SORTED_COLUMN + ); + // if the old column name is part of the remembered sort expression + if (mb_strpos( + (string) $sorted_col, + Util::backquote($_POST['field_orig'][$i]) + ) !== false) { + // delete the whole remembered sort expression + $this->table_obj->removeUiProp(Table::PROP_SORTED_COLUMN); + } + + if (isset($_POST['field_adjust_privileges'][$i]) + && ! empty($_POST['field_adjust_privileges'][$i]) + && $_POST['field_orig'][$i] != $_POST['field_name'][$i] + ) { + $adjust_privileges[$_POST['field_orig'][$i]] + = $_POST['field_name'][$i]; + } + } // end for + + if (count($changes) > 0 || isset($_POST['preview_sql'])) { + // Builds the primary keys statements and updates the table + $key_query = ''; + /** + * this is a little bit more complex + * + * @todo if someone selects A_I when altering a column we need to check: + * - no other column with A_I + * - the column has an index, if not create one + * + */ + + // To allow replication, we first select the db to use + // and then run queries on this db. + if (! $this->dbi->selectDb($this->db)) { + Util::mysqlDie( + $this->dbi->getError(), + 'USE ' . Util::backquote($this->db) . ';', + false, + $err_url + ); + } + $sql_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' '; + $sql_query .= implode(', ', $changes) . $key_query; + $sql_query .= ';'; + + // If there is a request for SQL previewing. + if (isset($_POST['preview_sql'])) { + Core::previewSQL(count($changes) > 0 ? $sql_query : ''); + } + + $columns_with_index = $this->dbi + ->getTable($this->db, $this->table) + ->getColumnsWithIndex( + Index::PRIMARY | Index::UNIQUE | Index::INDEX + | Index::SPATIAL | Index::FULLTEXT + ); + + $changedToBlob = []; + // While changing the Column Collation + // First change to BLOB + for ($i = 0; $i < $field_cnt; $i++) { + if (isset($_POST['field_collation'][$i]) + && isset($_POST['field_collation_orig'][$i]) + && $_POST['field_collation'][$i] !== $_POST['field_collation_orig'][$i] + && ! in_array($_POST['field_orig'][$i], $columns_with_index) + ) { + $secondary_query = 'ALTER TABLE ' . Util::backquote( + $this->table + ) + . ' CHANGE ' . Util::backquote( + $_POST['field_orig'][$i] + ) + . ' ' . Util::backquote($_POST['field_orig'][$i]) + . ' BLOB'; + + if (isset($_POST['field_virtuality'][$i]) + && isset($_POST['field_expression'][$i])) { + if ($_POST['field_virtuality'][$i]) { + $secondary_query .= ' AS (' . $_POST['field_expression'][$i] . ') ' + . $_POST['field_virtuality'][$i]; + } + } + + $secondary_query .= ';'; + + $this->dbi->query($secondary_query); + $changedToBlob[$i] = true; + } else { + $changedToBlob[$i] = false; + } + } + + // Then make the requested changes + $result = $this->dbi->tryQuery($sql_query); + + if ($result !== false) { + $changed_privileges = $this->adjustColumnPrivileges( + $adjust_privileges + ); + + if ($changed_privileges) { + $message = Message::success( + __( + 'Table %1$s has been altered successfully. Privileges ' . + 'have been adjusted.' + ) + ); + } else { + $message = Message::success( + __('Table %1$s has been altered successfully.') + ); + } + $message->addParam($this->table); + + $this->response->addHTML( + Util::getMessage($message, $sql_query, 'success') + ); + } else { + // An error happened while inserting/updating a table definition + + // Save the Original Error + $orig_error = $this->dbi->getError(); + $changes_revert = []; + + // Change back to Original Collation and data type + for ($i = 0; $i < $field_cnt; $i++) { + if ($changedToBlob[$i]) { + $changes_revert[] = 'CHANGE ' . Table::generateAlter( + Util::getValueByKey($_POST, "field_orig.${i}", ''), + $_POST['field_name'][$i], + $_POST['field_type_orig'][$i], + $_POST['field_length_orig'][$i], + $_POST['field_attribute_orig'][$i], + Util::getValueByKey($_POST, "field_collation_orig.${i}", ''), + Util::getValueByKey($_POST, "field_null_orig.${i}", 'NO'), + $_POST['field_default_type_orig'][$i], + $_POST['field_default_value_orig'][$i], + Util::getValueByKey($_POST, "field_extra_orig.${i}", false), + Util::getValueByKey($_POST, "field_comments_orig.${i}", ''), + Util::getValueByKey($_POST, "field_virtuality_orig.${i}", ''), + Util::getValueByKey($_POST, "field_expression_orig.${i}", ''), + Util::getValueByKey($_POST, "field_move_to_orig.${i}", '') + ); + } + } + + $revert_query = 'ALTER TABLE ' . Util::backquote($this->table) + . ' '; + $revert_query .= implode(', ', $changes_revert) . ''; + $revert_query .= ';'; + + // Column reverted back to original + $this->dbi->query($revert_query); + + $this->response->setRequestStatus(false); + $this->response->addJSON( + 'message', + Message::rawError( + __('Query error') . ':<br>' . $orig_error + ) + ); + $regenerate = true; + } + } + + // update field names in relation + if (isset($_POST['field_orig']) && is_array($_POST['field_orig'])) { + foreach ($_POST['field_orig'] as $fieldindex => $fieldcontent) { + if ($_POST['field_name'][$fieldindex] != $fieldcontent) { + $this->relation->renameField( + $this->db, + $this->table, + $fieldcontent, + $_POST['field_name'][$fieldindex] + ); + } + } + } + + // update mime types + if (isset($_POST['field_mimetype']) + && is_array($_POST['field_mimetype']) + && $GLOBALS['cfg']['BrowseMIME'] + ) { + foreach ($_POST['field_mimetype'] as $fieldindex => $mimetype) { + if (isset($_POST['field_name'][$fieldindex]) + && strlen($_POST['field_name'][$fieldindex]) > 0 + ) { + $this->transformations->setMime( + $this->db, + $this->table, + $_POST['field_name'][$fieldindex], + $mimetype, + $_POST['field_transformation'][$fieldindex], + $_POST['field_transformation_options'][$fieldindex], + $_POST['field_input_transformation'][$fieldindex], + $_POST['field_input_transformation_options'][$fieldindex] + ); + } + } + } + return $regenerate; + } + + /** + * Adjusts the Privileges for all the columns whose names have changed + * + * @param array $adjust_privileges assoc array of old col names mapped to new + * cols + * + * @return boolean boolean whether at least one column privileges + * adjusted + */ + protected function adjustColumnPrivileges(array $adjust_privileges) + { + $changed = false; + + if (Util::getValueByKey($GLOBALS, 'col_priv', false) + && Util::getValueByKey($GLOBALS, 'is_reload_priv', false) + ) { + $this->dbi->selectDb('mysql'); + + // For Column specific privileges + foreach ($adjust_privileges as $oldCol => $newCol) { + $this->dbi->query( + sprintf( + 'UPDATE %s SET Column_name = "%s" + WHERE Db = "%s" + AND Table_name = "%s" + AND Column_name = "%s";', + Util::backquote('columns_priv'), + $newCol, + $this->db, + $this->table, + $oldCol + ) + ); + + // i.e. if atleast one column privileges adjusted + $changed = true; + } + + if ($changed) { + // Finally FLUSH the new privileges + $this->dbi->query("FLUSH PRIVILEGES;"); + } + } + + return $changed; + } + + /** + * Verifies if some elements of a column have changed + * + * @param integer $i column index in the request + * + * @return boolean true if we need to generate ALTER TABLE + * + */ + protected function columnNeedsAlterTable($i) + { + // these two fields are checkboxes so might not be part of the + // request; therefore we define them to avoid notices below + if (! isset($_POST['field_null'][$i])) { + $_POST['field_null'][$i] = 'NO'; + } + if (! isset($_POST['field_extra'][$i])) { + $_POST['field_extra'][$i] = ''; + } + + // field_name does not follow the convention (corresponds to field_orig) + if ($_POST['field_name'][$i] != $_POST['field_orig'][$i]) { + return true; + } + + $fields = [ + 'field_attribute', + 'field_collation', + 'field_comments', + 'field_default_value', + 'field_default_type', + 'field_extra', + 'field_length', + 'field_null', + 'field_type', + ]; + foreach ($fields as $field) { + if ($_POST[$field][$i] != $_POST[$field . '_orig'][$i]) { + return true; + } + } + return ! empty($_POST['field_move_to'][$i]); + } + + /** + * Displays the table structure ('show table' works correct since 3.23.03) + * + * @param array $cfgRelation current relation parameters + * @param array $columns_with_unique_index Columns with unique index + * @param mixed $url_params Contains an associative + * array with url params + * @param Index|false $primary_index primary index or false if + * no one exists + * @param array $fields Fields + * @param array $columns_with_index Columns with index + * + * @return string + */ + protected function displayStructure( + array $cfgRelation, + array $columns_with_unique_index, + $url_params, + $primary_index, + array $fields, + array $columns_with_index + ) { + // prepare comments + $comments_map = []; + $mime_map = []; + + if ($GLOBALS['cfg']['ShowPropertyComments']) { + $comments_map = $this->relation->getComments($this->db, $this->table); + if ($cfgRelation['mimework'] && $GLOBALS['cfg']['BrowseMIME']) { + $mime_map = $this->transformations->getMime($this->db, $this->table, true); + } + } + $centralColumns = new CentralColumns($this->dbi); + $central_list = $centralColumns->getFromTable( + $this->db, + $this->table + ); + $columns_list = []; + + $titles = [ + 'Change' => Util::getIcon('b_edit', __('Change')), + 'Drop' => Util::getIcon('b_drop', __('Drop')), + 'NoDrop' => Util::getIcon('b_drop', __('Drop')), + 'Primary' => Util::getIcon('b_primary', __('Primary')), + 'Index' => Util::getIcon('b_index', __('Index')), + 'Unique' => Util::getIcon('b_unique', __('Unique')), + 'Spatial' => Util::getIcon('b_spatial', __('Spatial')), + 'IdxFulltext' => Util::getIcon('b_ftext', __('Fulltext')), + 'NoPrimary' => Util::getIcon('bd_primary', __('Primary')), + 'NoIndex' => Util::getIcon('bd_index', __('Index')), + 'NoUnique' => Util::getIcon('bd_unique', __('Unique')), + 'NoSpatial' => Util::getIcon('bd_spatial', __('Spatial')), + 'NoIdxFulltext' => Util::getIcon('bd_ftext', __('Fulltext')), + 'DistinctValues' => Util::getIcon('b_browse', __('Distinct values')), + ]; + + $edit_view_url = ''; + if ($this->_tbl_is_view && ! $this->_db_is_system_schema) { + $edit_view_url = Url::getCommon([ + 'db' => $this->db, + 'table' => $this->table, + ]); + } + + /** + * Displays Space usage and row statistics + */ + // BEGIN - Calc Table Space + // Get valid statistics whatever is the table type + if ($GLOBALS['cfg']['ShowStats']) { + //get table stats in HTML format + $tablestats = $this->getTableStats(); + //returning the response in JSON format to be used by Ajax + $this->response->addJSON('tableStat', $tablestats); + } + // END - Calc Table Space + + $hideStructureActions = false; + if ($GLOBALS['cfg']['HideStructureActions'] === true) { + $hideStructureActions = true; + } + + // logic removed from Template + $rownum = 0; + $columns_list = []; + $attributes = []; + $displayed_fields = []; + $row_comments = []; + $extracted_columnspecs = []; + $collations = []; + foreach ($fields as &$field) { + $rownum += 1; + $columns_list[] = $field['Field']; + + $extracted_columnspecs[$rownum] = Util::extractColumnSpec($field['Type']); + $attributes[$rownum] = $extracted_columnspecs[$rownum]['attribute']; + if (strpos($field['Extra'], 'on update CURRENT_TIMESTAMP') !== false) { + $attributes[$rownum] = 'on update CURRENT_TIMESTAMP'; + } + + $displayed_fields[$rownum] = new stdClass(); + $displayed_fields[$rownum]->text = $field['Field']; + $displayed_fields[$rownum]->icon = ""; + $row_comments[$rownum] = ''; + + if (isset($comments_map[$field['Field']])) { + $displayed_fields[$rownum]->comment = $comments_map[$field['Field']]; + $row_comments[$rownum] = $comments_map[$field['Field']]; + } + + if ($primary_index && $primary_index->hasColumn($field['Field'])) { + $displayed_fields[$rownum]->icon .= + Util::getImage('b_primary', __('Primary')); + } + + if (in_array($field['Field'], $columns_with_index)) { + $displayed_fields[$rownum]->icon .= + Util::getImage('bd_primary', __('Index')); + } + + $collation = Charsets::findCollationByName( + $this->dbi, + $GLOBALS['cfg']['Server']['DisableIS'], + $field['Collation'] ?? '' + ); + if ($collation !== null) { + $collations[$collation->getName()] = [ + 'name' => $collation->getName(), + 'description' => $collation->getDescription(), + ]; + } + } + + $engine = $this->table_obj->getStorageEngine(); + return $this->template->render('table/structure/display_structure', [ + 'url_params' => [ + 'db' => $this->db, + 'table' => $this->table, + ], + 'collations' => $collations, + 'is_foreign_key_supported' => Util::isForeignKeySupported($engine), + 'displayIndexesHtml' => Index::getHtmlForDisplayIndexes(), + 'cfg_relation' => $this->relation->getRelationsParam(), + 'hide_structure_actions' => $hideStructureActions, + 'db' => $this->db, + 'table' => $this->table, + 'db_is_system_schema' => $this->_db_is_system_schema, + 'tbl_is_view' => $this->_tbl_is_view, + 'mime_map' => $mime_map, + 'url_query' => $this->_url_query, + 'titles' => $titles, + 'tbl_storage_engine' => $this->_tbl_storage_engine, + 'primary' => $primary_index, + 'columns_with_unique_index' => $columns_with_unique_index, + 'edit_view_url' => $edit_view_url, + 'columns_list' => $columns_list, + 'table_stats' => isset($tablestats) ? $tablestats : null, + 'fields' => $fields, + 'extracted_columnspecs' => $extracted_columnspecs, + 'columns_with_index' => $columns_with_index, + 'central_list' => $central_list, + 'comments_map' => $comments_map, + 'browse_mime' => $GLOBALS['cfg']['BrowseMIME'], + 'show_column_comments' => $GLOBALS['cfg']['ShowColumnComments'], + 'show_stats' => $GLOBALS['cfg']['ShowStats'], + 'relation_commwork' => $GLOBALS['cfgRelation']['commwork'], + 'relation_mimework' => $GLOBALS['cfgRelation']['mimework'], + 'central_columns_work' => $GLOBALS['cfgRelation']['centralcolumnswork'], + 'mysql_int_version' => $this->dbi->getVersion(), + 'is_mariadb' => $this->dbi->isMariaDB(), + 'pma_theme_image' => $GLOBALS['pmaThemeImage'], + 'text_dir' => $GLOBALS['text_dir'], + 'is_active' => Tracker::isActive(), + 'have_partitioning' => Partition::havePartitioning(), + 'partitions' => Partition::getPartitions($this->db, $this->table), + 'partition_names' => Partition::getPartitionNames($this->db, $this->table), + 'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'], + 'attributes' => $attributes, + 'displayed_fields' => $displayed_fields, + 'row_comments' => $row_comments, + ]); + } + + /** + * Get HTML snippet for display table statistics + * + * @return string + */ + protected function getTableStats() + { + if (empty($this->_showtable)) { + $this->_showtable = $this->dbi->getTable( + $this->db, + $this->table + )->getStatusInfo(null, true); + } + + if (empty($this->_showtable['Data_length'])) { + $this->_showtable['Data_length'] = 0; + } + if (empty($this->_showtable['Index_length'])) { + $this->_showtable['Index_length'] = 0; + } + + $is_innodb = (isset($this->_showtable['Type']) + && $this->_showtable['Type'] == 'InnoDB'); + + $mergetable = $this->table_obj->isMerge(); + + // this is to display for example 261.2 MiB instead of 268k KiB + $max_digits = 3; + $decimals = 1; + list($data_size, $data_unit) = Util::formatByteDown( + $this->_showtable['Data_length'], + $max_digits, + $decimals + ); + if ($mergetable === false) { + list($index_size, $index_unit) = Util::formatByteDown( + $this->_showtable['Index_length'], + $max_digits, + $decimals + ); + } + if (isset($this->_showtable['Data_free'])) { + list($free_size, $free_unit) = Util::formatByteDown( + $this->_showtable['Data_free'], + $max_digits, + $decimals + ); + list($effect_size, $effect_unit) = Util::formatByteDown( + $this->_showtable['Data_length'] + + $this->_showtable['Index_length'] + - $this->_showtable['Data_free'], + $max_digits, + $decimals + ); + } else { + list($effect_size, $effect_unit) = Util::formatByteDown( + $this->_showtable['Data_length'] + + $this->_showtable['Index_length'], + $max_digits, + $decimals + ); + } + list($tot_size, $tot_unit) = Util::formatByteDown( + $this->_showtable['Data_length'] + $this->_showtable['Index_length'], + $max_digits, + $decimals + ); + if ($this->_table_info_num_rows > 0) { + list($avg_size, $avg_unit) = Util::formatByteDown( + ($this->_showtable['Data_length'] + + $this->_showtable['Index_length']) + / $this->_showtable['Rows'], + 6, + 1 + ); + } else { + $avg_size = $avg_unit = ''; + } + /** @var Innodb $innodbEnginePlugin */ + $innodbEnginePlugin = StorageEngine::getEngine('Innodb'); + $innodb_file_per_table = $innodbEnginePlugin->supportsFilePerTable(); + + $engine = $this->dbi->getTable($this->db, $this->table)->getStorageEngine(); + + $tableCollation = []; + $collation = Charsets::findCollationByName( + $this->dbi, + $GLOBALS['cfg']['Server']['DisableIS'], + $this->_tbl_collation + ); + if ($collation !== null) { + $tableCollation = [ + 'name' => $collation->getName(), + 'description' => $collation->getDescription(), + ]; + } + return $this->template->render('table/structure/display_table_stats', [ + 'url_params' => [ + 'db' => $GLOBALS['db'], + 'table' => $GLOBALS['table'], + ], + 'is_foreign_key_supported' => Util::isForeignKeySupported($engine), + 'cfg_relation' => $this->relation->getRelationsParam(), + 'showtable' => $this->_showtable, + 'table_info_num_rows' => $this->_table_info_num_rows, + 'tbl_is_view' => $this->_tbl_is_view, + 'db_is_system_schema' => $this->_db_is_system_schema, + 'tbl_storage_engine' => $this->_tbl_storage_engine, + 'url_query' => $this->_url_query, + 'table_collation' => $tableCollation, + 'is_innodb' => $is_innodb, + 'mergetable' => $mergetable, + 'avg_size' => isset($avg_size) ? $avg_size : null, + 'avg_unit' => isset($avg_unit) ? $avg_unit : null, + 'data_size' => $data_size, + 'data_unit' => $data_unit, + 'index_size' => isset($index_size) ? $index_size : null, + 'index_unit' => isset($index_unit) ? $index_unit : null, + 'innodb_file_per_table' => $innodb_file_per_table, + 'free_size' => isset($free_size) ? $free_size : null, + 'free_unit' => isset($free_unit) ? $free_unit : null, + 'effect_size' => $effect_size, + 'effect_unit' => $effect_unit, + 'tot_size' => $tot_size, + 'tot_unit' => $tot_unit, + 'table' => $GLOBALS['table'], + ]); + } + + /** + * Gets table primary key + * + * @return string + */ + protected function getKeyForTablePrimary() + { + $this->dbi->selectDb($this->db); + $result = $this->dbi->query( + 'SHOW KEYS FROM ' . Util::backquote($this->table) . ';' + ); + $primary = ''; + while ($row = $this->dbi->fetchAssoc($result)) { + // Backups the list of primary keys + if ($row['Key_name'] == 'PRIMARY') { + $primary .= $row['Column_name'] . ', '; + } + } // end while + $this->dbi->freeResult($result); + + return $primary; + } + + /** + * Get List of information for Submit Mult + * + * @param string $submit_mult mult_submit type + * @param array $selected the selected columns + * @param string $action action type + * @param ContainerBuilder $containerBuilder Container builder instance + * + * @return array + */ + protected function getDataForSubmitMult($submit_mult, $selected, $action, ContainerBuilder $containerBuilder) + { + $centralColumns = new CentralColumns($this->dbi); + $what = null; + $query_type = null; + $is_unset_submit_mult = false; + $mult_btn = null; + $centralColsError = null; + switch ($submit_mult) { + case 'drop': + $what = 'drop_fld'; + break; + case 'primary': + // Gets table primary key + $primary = $this->getKeyForTablePrimary(); + if (empty($primary)) { + // no primary key, so we can safely create new + $is_unset_submit_mult = true; + $query_type = 'primary_fld'; + $mult_btn = __('Yes'); + } else { + // primary key exists, so lets as user + $what = 'primary_fld'; + } + break; + case 'index': + $is_unset_submit_mult = true; + $query_type = 'index_fld'; + $mult_btn = __('Yes'); + break; + case 'unique': + $is_unset_submit_mult = true; + $query_type = 'unique_fld'; + $mult_btn = __('Yes'); + break; + case 'spatial': + $is_unset_submit_mult = true; + $query_type = 'spatial_fld'; + $mult_btn = __('Yes'); + break; + case 'ftext': + $is_unset_submit_mult = true; + $query_type = 'fulltext_fld'; + $mult_btn = __('Yes'); + break; + case 'add_to_central_columns': + $centralColsError = $centralColumns->syncUniqueColumns( + $selected, + false + ); + break; + case 'remove_from_central_columns': + $centralColsError = $centralColumns->deleteColumnsFromList( + $_POST['db'], + $selected, + false + ); + break; + case 'change': + $this->displayHtmlForColumnChange($selected, $action, $containerBuilder); + // execution stops here but PhpMyAdmin\Response correctly finishes + // the rendering + exit; + case 'browse': + // this should already be handled by tbl_structure.php + } + + return [ + $what, + $query_type, + $is_unset_submit_mult, + $mult_btn, + $centralColsError, + ]; + } +} |