src/SystemDatabase.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin;
   6
   7 use PhpMyAdmin\ConfigStorage\Relation;
   8 use PhpMyAdmin\Dbal\ResultInterface;
   9
  10 use function count;
  11 use function sprintf;
  12
  13 class SystemDatabase
  14 {
  15     private Relation $relation;
  16
  17     /**
  18      * Get instance of SystemDatabase
  19      *
  20      * @param DatabaseInterface $dbi Database interface for the system database
  21      */
  22     public function __construct(protected DatabaseInterface $dbi)
  23     {
  24         $this->relation = new Relation($this->dbi);
  25     }
  26
  27     /**
  28      * Get existing data on transformations applied for
  29      * columns in a particular table
  30      *
  31      * @param string $db Database name looking for
  32      *
  33      * @return ResultInterface|false Result of executed SQL query
  34      */
  35     public function getExistingTransformationData(string $db): ResultInterface|false
  36     {
  37         $browserTransformationFeature = $this->relation->getRelationParameters()->browserTransformationFeature;
  38         if ($browserTransformationFeature === null) {
  39             return false;
  40         }
  41
  42         // Get the existing transformation details of the same database
  43         // from pma__column_info table
  44         $transformationSql = sprintf(
  45             'SELECT * FROM %s.%s WHERE `db_name` = %s',
  46             Util::backquote($browserTransformationFeature->database),
  47             Util::backquote($browserTransformationFeature->columnInfo),
  48             $this->dbi->quoteString($db),
  49         );
  50
  51         return $this->dbi->tryQuery($transformationSql);
  52     }
  53
  54     /**
  55      * Get SQL query for store new transformation details of a VIEW
  56      *
  57      * @param ResultInterface $transformationData Result set of SQL execution
  58      * @param SystemColumn[]  $columnMap          Details of VIEW columns
  59      * @param string          $viewName           Name of the VIEW
  60      * @param string          $db                 Database name of the VIEW
  61      *
  62      * @return string SQL query for new transformations
  63      */
  64     public function getNewTransformationDataSql(
  65         ResultInterface $transformationData,
  66         array $columnMap,
  67         string $viewName,
  68         string $db,
  69     ): string {
  70         $browserTransformationFeature = $this->relation->getRelationParameters()->browserTransformationFeature;
  71         if ($browserTransformationFeature === null) {
  72             return '';
  73         }
  74
  75         // Need to store new transformation details for VIEW
  76         $newTransformationsSql = sprintf(
  77             'INSERT INTO %s.%s ('
  78             . '`db_name`, `table_name`, `column_name`, '
  79             . '`comment`, `mimetype`, `transformation`, '
  80             . '`transformation_options`) VALUES',
  81             Util::backquote($browserTransformationFeature->database),
  82             Util::backquote($browserTransformationFeature->columnInfo),
  83         );
  84
  85         $columnCount = 0;
  86         $addComma = false;
  87
  88         /** @infection-ignore-all */
  89         while ($dataRow = $transformationData->fetchAssoc()) {
  90             foreach ($columnMap as $column) {
  91                 if (
  92                     $dataRow['table_name'] != $column->tableName
  93                     || $dataRow['column_name'] != $column->referringColumn
  94                 ) {
  95                     continue;
  96                 }
  97
  98                 $newTransformationsSql .= sprintf(
  99                     '%s (%s, %s, %s, %s, %s, %s, %s)',
 100                     $addComma ? ', ' : '',
 101                     $this->dbi->quoteString($db),
 102                     $this->dbi->quoteString($viewName),
 103                     $this->dbi->quoteString($column->realColumn ?? $column->referringColumn),
 104                     $this->dbi->quoteString($dataRow['comment']),
 105                     $this->dbi->quoteString($dataRow['mimetype']),
 106                     $this->dbi->quoteString($dataRow['transformation']),
 107                     $this->dbi->quoteString($dataRow['transformation_options']),
 108                 );
 109
 110                 $addComma = true;
 111                 $columnCount++;
 112                 break;
 113             }
 114
 115             if ($columnCount === count($columnMap)) {
 116                 break;
 117             }
 118         }
 119
 120         return $columnCount > 0 ? $newTransformationsSql : '';
 121     }
 122
 123     /**
 124      * @param string[] $viewColumns
 125      *
 126      * @return SystemColumn[]
 127      * @psalm-return list<SystemColumn>
 128      */
 129     public function getColumnMapFromSql(string $sqlQuery, array $viewColumns): array
 130     {
 131         $result = $this->dbi->tryQuery($sqlQuery);
 132
 133         if ($result === false) {
 134             return [];
 135         }
 136
 137         $columnMap = [];
 138
 139         foreach ($this->dbi->getFieldsMeta($result) as $i => $field) {
 140             $columnMap[] = new SystemColumn($field->table, $field->name, $viewColumns[$i] ?? null);
 141         }
 142
 143         return $columnMap;
 144     }
 145 }