YII2命令行實(shí)現(xiàn)數(shù)據(jù)庫(kù)表結(jié)構(gòu)文檔自動(dòng)生成

Jason_Geng 發(fā)布于2019-06-28 18:17 / 3303人閱讀

摘要：因?yàn)橐獙戧P(guān)于數(shù)據(jù)庫(kù)表說明的文檔，需要有表結(jié)構(gòu)，如果一個(gè)個(gè)表去建表格，然后在復(fù)制每個(gè)字段進(jìn)去，那就太沒效率了。其實(shí)程序很簡(jiǎn)單，用程序獲取數(shù)據(jù)庫(kù)，再寫入到文件文件格式里就可以了。因?yàn)轫?xiàng)目是基于框架，所以用的命令工具來實(shí)現(xiàn)。

因?yàn)橐獙戧P(guān)于數(shù)據(jù)庫(kù)表說明的文檔，需要有表結(jié)構(gòu)，如果一個(gè)個(gè)表去建表格，然后在復(fù)制每個(gè)字段進(jìn)去，那就太沒效率了。其實(shí)程序很簡(jiǎn)單，用程序獲取數(shù)據(jù)庫(kù)Schema，再寫入到markdown文件（文件格式md）里就可以了。因?yàn)轫?xiàng)目是基于YII2框架，所以用YII2的命令工具來實(shí)現(xiàn)。大概的效果圖如下：


 * @since 2018-01-18
 */
class TableSchemaController extends Controller
{
    /**
     * 數(shù)據(jù)庫(kù)表生成
     */
    public function actionCreate()
    {
        global $argv;

        if (!$argv[2] || strcasecmp($argv[2], "help") === 0) {
            echo "Usage: ./yii table-schema/create [all|tablename] [filename]
";
            exit;
        }

        $db = Yii::$app->db;
        $allTables = $db->getSchema()->getTableNames();

        if ("all" === $argv[2]) {
            $tables = array_diff($allTables, $this->filterTables());
        } else {
            if (!in_array($argv[2], $allTables)) {
                echo sprintf("%s isn"t exist 
", $argv[2]);
                exit;
            }
            $tables = (array)$argv[2];
        }

        // 當(dāng)前數(shù)據(jù)庫(kù)沒有表
        if (count(array_filter($tables)) == 0) {
            echo "Database has not table 
";
        }

        $root = dirname(dirname(dirname(__FILE__)));
        $filename = $argv[3] ? $argv[3] : "/docs/note/數(shù)據(jù)庫(kù)設(shè)計(jì)及字典說明.md";
        $filePath = $root . $filename;

        $fp = fopen($filePath, "a+");
        if (!$fp) {
            echo "Open file failed 
";
        }

        foreach ($tables as $table) {
            $schema = $db->getTableSchema($table, true);
            if (!$schema->columns) {
                continue;
            }

            fwrite($fp, "#### $schema->name 表 
");

            // 表頭
            $header = "| 字段名 | 類型 | 說明 | 
";
            $header .= "|:--------:|:---------:|:-------:| 
";
            fwrite($fp, $header);

            // 字段
            $row = "";

            foreach ($schema->columns as $col => $obj) {
                $comment = $obj->isPrimaryKey ? "主鍵" : $obj->comment;
                $row .= "| $obj->name | $obj->dbType | $comment | 
";
            }

            fwrite($fp, $row);
            fwrite($fp, "

");

            echo "$schema->name successfully 
";
        }

        fclose($fp);
    }

    /**
     * 需要過濾的表(不希望生成文檔的表)
     * @return array
     */
    protected function filterTables()
    {
        $filterTables = [
            "tbmigration",
            "tbAuthAssignment",
            "tbAuthItemChild",
            "tbAuthRule",
            "tbItemTable"
        ];

        return $filterTables;
    }

    /**
     * 所有表
     * @return string[]
     */
    protected function allTables()
    {
        return Yii::$app->db->getSchema()->getTableNames();
    }

    /**
     * 清空
     */
    public function actionClear()
    {
        global $argv;

        if (!$argv[2] || strcasecmp($argv[2], "help") === 0) {
            echo "Usage: ./yii table-schema/clear [filename]
";
            exit;
        }

        $root = dirname(dirname(dirname(__FILE__)));
        $filePath = $argv[2] ? $argv[2] : "/docs/note/數(shù)據(jù)庫(kù)設(shè)計(jì)及字典說明.md";
        $filePath = $root . $filePath;

        if (!is_file($filePath)) {
            echo "$filePath isn"t exists 
";
            exit;
        }

        $fp = fopen($filePath, "w");
        if (!$fp) {
            echo "Open file failed 
";
        }

        fwrite($fp, "");
        fclose($fp);

        echo "Clear successfully 
";
    }
}

執(zhí)行命令：

./yii table-schema/create [all|tablename] [filename] 
# 例如生成所有表到test.md文件 
./yii table-schema/create all test.md
# 生成user表到test.md文件
./yii table-schema/create user test.md
# 清空文檔
./yii table-schema/clear [filename]

程序很簡(jiǎn)單，但是很實(shí)用～～