public function getTableMetadata(
        Connection $connection,
        string $databaseName,
        string $tableName
    ): TableMetadata {
        // Get top-level table information
        $tableStatement = $connection
            ->createQueryBuilder()
            ->select([
                'tables.TABLE_NAME AS name',
                'tables.ENGINE AS engine',
                'tables.TABLE_COLLATION AS collation',
                'tables.TABLE_ROWS AS row_count',
                'charset.CHARACTER_SET_NAME AS charset'
            ])
            ->from('information_schema.TABLES', 'tables')
            ->leftJoin(
                'tables',
                'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY',
                'charset',
                'tables.TABLE_COLLATION = charset.COLLATION_NAME'
            )
            ->where('TABLE_NAME = :table_name')
            ->setParameter('table_name', $tableName)
            ->execute();

        $tableRow = $tableStatement->fetch(\PDO::FETCH_ASSOC);

        // Get column information
        $columnsStatement = $connection
            ->createQueryBuilder()
            ->select([
                'columns.COLUMN_NAME AS name',
                'columns.COLUMN_TYPE AS type',
                'columns.CHARACTER_MAXIMUM_LENGTH AS max_length',
                '"PRI" = columns.COLUMN_KEY AS is_primary_key',
                '"YES" = columns.IS_NULLABLE AS is_nullable',
                '(
                    SELECT COUNT(*) 
                    FROM   INFORMATION_SCHEMA.STATISTICS 
                    WHERE  TABLE_SCHEMA = :schema 
                    AND    TABLE_NAME = :table_name 
                    AND    COLUMN_NAME = columns.COLUMN_NAME
                ) AS is_indexed'
            ])
            ->from('information_schema.COLUMNS', 'columns')
            ->where('TABLE_SCHEMA = :schema')
            ->andWhere('TABLE_NAME = :table_name')
            ->setParameters([
                'schema' => $databaseName,
                'table_name' => $tableName
            ])
            ->execute();

        // Build column metadata
        $columnMetadataList = [];
        foreach ($columnsStatement->fetchAll(\PDO::FETCH_ASSOC) as $columnsRow) {
            $columnMetadataList[] = new ColumnMetadata(
                $databaseName,
                $tableName,
                $columnsRow['name'],
                $columnsRow['type'],
                is_null($columnsRow['max_length']) ? null : intval($columnsRow['max_length']),
                boolval($columnsRow['is_primary_key']),
                boolval($columnsRow['is_nullable']),
                boolval($columnsRow['is_indexed'])
            );
        }

        return new TableMetadata(
            $databaseName,
            $tableName,
            $tableRow['engine'],
            $tableRow['collation'],
            $tableRow['charset'],
            intval($tableRow['row_count']),
            $columnMetadataList
        );
    }
public function getTableMetadata(
        Connection $connection,
        string $databaseName,
        string $tableName
    ): TableMetadata {
        // Get top-level table information
        $tableStatement = $connection
            ->createQueryBuilder()
            ->select([
                'tables.TABLE_NAME AS name',
                'tables.ENGINE AS engine',
                'tables.TABLE_COLLATION AS collation',
                'tables.TABLE_ROWS AS row_count',
                'charset.CHARACTER_SET_NAME AS charset'
            ])
            ->from('information_schema.TABLES', 'tables')
            ->leftJoin(
                'tables',
                'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY',
                'charset',
                'tables.TABLE_COLLATION = charset.COLLATION_NAME'
            )
            ->where('TABLE_NAME = :table_name')
            ->setParameter('table_name', $tableName)
            ->execute();

        $tableRow = $tableStatement->fetch(\PDO::FETCH_ASSOC);

        // Get column information
        $columnsStatement = $connection
            ->createQueryBuilder()
            ->select([
                'columns.COLUMN_NAME AS name',
                'columns.COLUMN_TYPE AS type',
                'columns.CHARACTER_MAXIMUM_LENGTH AS max_length',
                '"PRI" = columns.COLUMN_KEY AS is_primary_key',
                '"YES" = columns.IS_NULLABLE AS is_nullable',
                '(
                    SELECT COUNT(*) 
                    FROM   INFORMATION_SCHEMA.STATISTICS 
                    WHERE  TABLE_SCHEMA = :schema 
                    AND    TABLE_NAME = :table_name 
                    AND    COLUMN_NAME = columns.COLUMN_NAME
                ) AS is_indexed'
            ])
            ->from('information_schema.COLUMNS', 'columns')
            ->where('TABLE_SCHEMA = :schema')
            ->andWhere('TABLE_NAME = :table_name')
            ->setParameters([
                'schema' => $databaseName,
                'table_name' => $tableName
            ])
            ->execute();

        // Build column metadata
        $columnMetadataList = [];
        foreach ($columnsStatement->fetchAll(\PDO::FETCH_ASSOC) as $columnsRow) {
            $columnMetadataList[] = new ColumnMetadata(
                $databaseName,
                $tableName,
                $columnsRow['name'],
                $columnsRow['type'],
                is_null($columnsRow['max_length']) ? null : intval($columnsRow['max_length']),
                boolval($columnsRow['is_primary_key']),
                boolval($columnsRow['is_nullable']),
                boolval($columnsRow['is_indexed'])
            );
        }

        return new TableMetadata(
            $databaseName,
            $tableName,
            $tableRow['engine'],
            $tableRow['collation'],
            $tableRow['charset'],
            intval($tableRow['row_count']),
            $columnMetadataList
        );
    }
public function getTableMetadata ( Connection $ connection , string $ databaseName , string $ tableName ) : TableMetadata { // Get top-level table information $ tableStatement = $ connection -> createQueryBuilder ( ) -> select ( [ 'tables.TABLE_NAME AS name' , 'tables.ENGINE AS engine' , 'tables.TABLE_COLLATION AS collation' , 'tables.TABLE_ROWS AS row_count' , 'charset.CHARACTER_SET_NAME AS charset' ] ) -> from ( 'information_schema.TABLES' , 'tables' ) -> leftJoin ( 'tables' , 'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY' , 'charset' , 'tables.TABLE_COLLATION = charset.COLLATION_NAME' ) -> where ( 'TABLE_NAME = :table_name' ) -> setParameter ( 'table_name' , $ tableName ) -> execute ( ) ; $ tableRow = $ tableStatement -> fetch ( \ PDO :: FETCH_ASSOC ) ; // Get column information $ columnsStatement = $ connection -> createQueryBuilder ( ) -> select ( [ 'columns.COLUMN_NAME AS name' , 'columns.COLUMN_TYPE AS type' , 'columns.CHARACTER_MAXIMUM_LENGTH AS max_length' , '"PRI" = columns.COLUMN_KEY AS is_primary_key' , '"YES" = columns.IS_NULLABLE AS is_nullable' , '(
                    SELECT COUNT(*) 
                    FROM   INFORMATION_SCHEMA.STATISTICS 
                    WHERE  TABLE_SCHEMA = :schema 
                    AND    TABLE_NAME = :table_name 
                    AND    COLUMN_NAME = columns.COLUMN_NAME
                ) AS is_indexed' ] ) -> from ( 'information_schema.COLUMNS' , 'columns' ) -> where ( 'TABLE_SCHEMA = :schema' ) -> andWhere ( 'TABLE_NAME = :table_name' ) -> setParameters ( [ 'schema' => $ databaseName , 'table_name' => $ tableName ] ) -> execute ( ) ; // Build column metadata $ columnMetadataList = [ ] ; foreach ( $ columnsStatement -> fetchAll ( \ PDO :: FETCH_ASSOC ) as $ columnsRow ) { $ columnMetadataList [ ] = new ColumnMetadata ( $ databaseName , $ tableName , $ columnsRow [ 'name' ] , $ columnsRow [ 'type' ] , is_null ( $ columnsRow [ 'max_length' ] ) ? null : intval ( $ columnsRow [ 'max_length' ] ) , boolval ( $ columnsRow [ 'is_primary_key' ] ) , boolval ( $ columnsRow [ 'is_nullable' ] ) , boolval ( $ columnsRow [ 'is_indexed' ] ) ) ; } return new TableMetadata ( $ databaseName , $ tableName , $ tableRow [ 'engine' ] , $ tableRow [ 'collation' ] , $ tableRow [ 'charset' ] , intval ( $ tableRow [ 'row_count' ] ) , $ columnMetadataList ) ; }
Query the server and build table metadata DTO.
Query the server and build table metadata DTO .