im/app/command/Database.php

<?php

namespace app\command;

use Exception;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Output\OutputInterface;
use support\think\Db;
use app\model\User as UserModel;
use \think\db\PDOConnection;
use think\db\exception\PDOException;
use think\db\exception\InvalidArgumentException;


class Database extends Command
{
    protected static $defaultName = 'Db';
    protected static $defaultDescription = 'Database 优化';

    /**
     * @return void
     */
    protected function configure()
    {
        $this->addOption('action','a', InputOption::VALUE_OPTIONAL, '要做什么操作');
        $this->addOption('table','t', InputOption::VALUE_OPTIONAL, '表名');
        $this->addOption('domain','ym', InputOption::VALUE_OPTIONAL, 'domain');
        $this->addOption('connection','c', InputOption::VALUE_OPTIONAL, '数据库链接名,默认mysql','mysql');
        $this->addOption('dir','d', InputOption::VALUE_OPTIONAL, '缓存目录');
        $this->addOption('robot_id','rid', InputOption::VALUE_OPTIONAL, 'robot_id');
    }

    /**
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int
     */
    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $action = $input->getOption('action');
        if(method_exists($this, $action)){
            return $this->$action($input, $output);
        }
        cp('操作不存在:'.$action);
        return 0;
    }
    function optimize_schema(InputInterface $input, OutputInterface $output)
    {
        $table = $input->getOption('table');
        try {
            if ($table) {
                $this->cacheTable($table, $input->getOption('connection'));
            } else {
                $dirs = ((array) $input->getOption('dir')) ?: $this->getDefaultDirs();
                foreach ($dirs as $dir) {
                    $this->cacheModel($dir);
                }
            }
        } catch (\Exception $e) {
            $output->write($e->getMessage());
            return self::FAILURE;
        }

        $output->write('Succeed!');
        return self::SUCCESS;
    }
    function prototype(InputInterface $input, OutputInterface $output){
        $table = $input->getOption('table');
        // 获取表前缀并构建完整表名
        $prefix = config('thinkorm.connections.mysql.prefix', '');
        $fullTableName = '`' . $prefix . $table . '`';
        
        // 查询表结构
        $res = Db::query('SHOW FULL COLUMNS FROM ' . $fullTableName);
        
        if (empty($res)) {
            return "// 表 {$table} 不存在或没有字段";
        }
        
        $annotations = [];
        $annotations[] = '/**';
        
        foreach ($res as $row) {
            $field = $row['Field'];
            $type = $row['Type'];
            $comment = $row['Comment'] ?: '无注释';
            
            // 处理字段类型映射
            $phpType = $this->mapMysqlTypeToPhp($type);
            
            // 处理特殊字段
            if ($field === 'id') {
                $annotations[] = " * @property integer \${$field} 主键(ID) - {$comment}";
            } else {
                $annotations[] = " * @property {$phpType} \${$field} {$comment}";
            }
        }
        
        $annotations[] = ' */';
        
        cp( implode("\n", $annotations));
        return self::SUCCESS;
    }
    
    /**
     * 将MySQL字段类型映射到PHP类型
     * 
     * @param string $mysqlType MySQL字段类型
     * @return string PHP类型
     */
    protected function mapMysqlTypeToPhp($mysqlType)
    {
        $mysqlType = strtolower($mysqlType);
        
        // 整数类型
        if (preg_match('/^(tinyint|smallint|mediumint|int|bigint)/', $mysqlType)) {
            // 检查是否为无符号
            if (strpos($mysqlType, 'unsigned') !== false) {
                return 'integer'; // 无符号整数也返回integer
            }
            return 'integer';
        }
        
        // 浮点类型
        if (preg_match('/^(float|double|decimal)/', $mysqlType)) {
            return 'float';
        }
        
        // 字符串类型
        if (preg_match('/^(varchar|char|text|tinytext|mediumtext|longtext|enum|set)/', $mysqlType)) {
            return 'string';
        }
        
        // 日期时间类型
        if (preg_match('/^(date|time|datetime|timestamp|year)/', $mysqlType)) {
            return 'string'; // 或者可以返回 '\\DateTime' 如果需要更精确的类型
        }
        
        // 二进制类型
        if (preg_match('/^(blob|tinyblob|mediumblob|longblob|binary|varbinary)/', $mysqlType)) {
            return 'string'; // 或者根据需求返回其他类型
        }
        
        // JSON类型
        if (strpos($mysqlType, 'json') !== false) {
            return 'array'; // 或者 'mixed'
        }
        
        // 布尔类型（tinyint(1)通常用作布尔值）
        if ($mysqlType === 'tinyint(1)' || $mysqlType === 'boolean' || $mysqlType === 'bool') {
            return 'boolean';
        }
        
        // 默认返回混合类型
        return 'mixed';
    }
    

    protected function buildModelSchema(string $class): void
    {
        $reflect = new \ReflectionClass($class);
        if ($reflect->isAbstract() || ! $reflect->isSubclassOf('\think\Model')) {
            return;
        }
        try {
            /** @var \think\Model $model */
            $model = new $class;
            $connection = $model->db()->getConnection();
            if ($connection instanceof PDOConnection) {
                $table = $model->getTable();
                //预读字段信息
                $connection->getSchemaInfo($table, true);
            }
        } catch (Exception $e) {
        }
    }

    protected function buildDataBaseSchema(PDOConnection $connection, array $tables, string $dbName): void
    {
        foreach ($tables as $table) {
            //预读字段信息
            $connection->getSchemaInfo("{$dbName}.{$table}", true);
        }
    }

    /**
     * 缓存表
     */
    private function cacheTable(string $table, ?string $connectionName = null): void
    {
        $connection = Db::connect($connectionName);
        if (! $connection instanceof PDOConnection) {
            throw new Exception('only PDO connection support schema cache!');
        }

        if (str_contains($table, '.')) {
            [$dbName, $table] = explode('.', $table);
        } else {
            $dbName = $connection->getConfig('database');
        }

        if ($table == '*') {
            $table = $connection->getTables($dbName);
        }

        $this->buildDataBaseSchema($connection, (array) $table, $dbName);
    }

    /**
     * 缓存模型
     */
    private function cacheModel(?string $dir = null): void
    {
        if ($dir) {
            $modelDir = app_path('model') . $dir . DIRECTORY_SEPARATOR;
            $namespace = 'app\\' . $dir;
        } else {
            $modelDir = app_path('model').DIRECTORY_SEPARATOR;
            $namespace = 'app';
        }
        if (! is_dir($modelDir)) {
            throw new InvalidArgumentException("{$modelDir} directory does not exist");
        }

        /** @var \SplFileInfo[] $iterator */
        $iterator = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($modelDir, \RecursiveDirectoryIterator::SKIP_DOTS),
            \RecursiveIteratorIterator::SELF_FIRST
        );

        foreach ($iterator as $fileInfo) {
            $relativePath = substr($fileInfo->getRealPath(), strlen($modelDir));
            if (! str_ends_with($relativePath, '.php')) {
                continue;
            }
            // 去除 .php
            $relativePath = substr($relativePath, 0, -4);

            $class = '\\' . $namespace . '\\model\\' . str_replace('/', '\\', $relativePath);
            if (! class_exists($class)) {
                continue;
            }

            $this->buildModelSchema($class);
        }
    }

    /**
     * 获取默认目录名
     * @return array<int, ?string>
     */
    private function getDefaultDirs(): array
    {
        // 包含默认的模型目录
        $dirs = [null];
        return $dirs;
    }
}