DbSchemaReader.php

Go to the documentation of this file.

<?php
namespace Magento\Framework\Setup\Declaration\Schema\Db\MySQL;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Sql\Expression;
use Magento\Framework\Setup\Declaration\Schema\Db\DbSchemaReaderInterface;
use Magento\Framework\Setup\Declaration\Schema\Db\DefinitionAggregator;
use Magento\Framework\Setup\Declaration\Schema\Dto\Constraint;

class DbSchemaReader implements DbSchemaReaderInterface
{
    const MYSQL_TABLE_TYPE = 'BASE TABLE';

    private $resourceConnection;

    private $definitionAggregator;

    public function __construct(
        ResourceConnection $resourceConnection,
        DefinitionAggregator $definitionAggregator
    ) {
        $this->resourceConnection = $resourceConnection;
        $this->definitionAggregator = $definitionAggregator;
    }

    public function getTableOptions($tableName, $resource)
    {
        $adapter = $this->resourceConnection->getConnection($resource);
        $dbName = $this->resourceConnection->getSchemaName($resource);
        $stmt = $adapter->select()
            ->from(
                ['i_tables' => 'information_schema.TABLES'],
                [
                    'engine' => 'ENGINE',
                    'comment' => 'TABLE_COMMENT',
                    'collation' => 'TABLE_COLLATION'
                ]
            )
            ->joinInner(
                ['charset_applicability' => 'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY'],
                'i_tables.table_collation = charset_applicability.collation_name',
                [
                    'charset' => 'charset_applicability.CHARACTER_SET_NAME'
                ]
            )
            ->where('TABLE_SCHEMA = ?', $dbName)
            ->where('TABLE_NAME = ?', $tableName);

        return $adapter->fetchRow($stmt);
    }

    public function readColumns($tableName, $resource)
    {
        $columns = [];
        $adapter = $this->resourceConnection->getConnection($resource);
        $dbName = $this->resourceConnection->getSchemaName($resource);
        $stmt = $adapter->select()
            ->from(
                'information_schema.COLUMNS',
                [
                    'name' => 'COLUMN_NAME',
                    'default' => 'COLUMN_DEFAULT',
                    'type' => 'DATA_TYPE',
                    'nullable' => new Expression('IF(IS_NULLABLE="YES", true, false)'),
                    'definition' => 'COLUMN_TYPE',
                    'extra' => 'EXTRA',
                    'comment' => new Expression('IF(COLUMN_COMMENT="", NULL, COLUMN_COMMENT)')
                ]
            )
            ->where('TABLE_SCHEMA = ?', $dbName)
            ->where('TABLE_NAME = ?', $tableName)
            ->order('ORDINAL_POSITION ASC');

        $columnsDefinition = $adapter->fetchAssoc($stmt);

        foreach ($columnsDefinition as $columnDefinition) {
            $column = $this->definitionAggregator->fromDefinition($columnDefinition);
            $columns[$column['name']] = $column;
        }

        return $columns;
    }

    public function readIndexes($tableName, $resource)
    {
        $indexes = [];
        $adapter = $this->resourceConnection->getConnection($resource);
        $condition = sprintf('`Non_unique` = 1');
        $sql = sprintf('SHOW INDEXES FROM %s WHERE %s', $tableName, $condition);
        $stmt = $adapter->query($sql);

        // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
        $indexesDefinition = $stmt->fetchAll(\Zend_Db::FETCH_ASSOC);

        foreach ($indexesDefinition as $indexDefinition) {
            $indexDefinition['type'] = 'index';
            $index = $this->definitionAggregator->fromDefinition($indexDefinition);

            if (!isset($indexes[$index['name']])) {
                $indexes[$index['name']] = [];
            }

            $indexes[$index['name']] = array_replace_recursive($indexes[$index['name']], $index);
        }

        return $indexes;
    }

    public function readReferences($tableName, $resource)
    {
        $createTableSql = $this->getCreateTableSql($tableName, $resource);
        $createTableSql['type'] = 'reference';
        return $this->definitionAggregator->fromDefinition($createTableSql);
    }

    public function getCreateTableSql($tableName, $resource)
    {
        $adapter = $this->resourceConnection->getConnection($resource);
        $sql = sprintf('SHOW CREATE TABLE %s', $tableName);
        $stmt = $adapter->query($sql);
        return $stmt->fetch(\Zend_Db::FETCH_ASSOC);
    }

    public function readConstraints($tableName, $resource)
    {
        $constraints = [];
        $adapter = $this->resourceConnection->getConnection($resource);
        $condition = sprintf('`Non_unique` = 0');
        $sql = sprintf('SHOW INDEXES FROM %s WHERE %s', $tableName, $condition);
        $stmt = $adapter->query($sql);

        // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
        $constraintsDefinition = $stmt->fetchAll(\Zend_Db::FETCH_ASSOC);

        foreach ($constraintsDefinition as $constraintDefinition) {
            $constraintDefinition['type'] = Constraint::TYPE;
            $constraint = $this->definitionAggregator->fromDefinition($constraintDefinition);

            if (!isset($constraints[$constraint['name']])) {
                $constraints[$constraint['name']] = [];
            }

            $constraints[$constraint['name']] = array_replace_recursive($constraints[$constraint['name']], $constraint);
        }

        return $constraints;
    }

    public function readTables($resource)
    {
        $adapter = $this->resourceConnection->getConnection($resource);
        $dbName = $this->resourceConnection->getSchemaName($resource);
        $stmt = $adapter->select()
            ->from(
                ['information_schema.TABLES'],
                ['TABLE_NAME']
            )
            ->where('TABLE_SCHEMA = ?', $dbName)
            ->where('TABLE_TYPE = ?', self::MYSQL_TABLE_TYPE);
        return $adapter->fetchCol($stmt);
    }
}