因为要写关于数据库表说明的文档,需要有表结构,如果一个个表去建表格,然后在复制每个字段进去,那就太没效率了。其实程序很简单,用程序获取数据库Schema,再写入到markdown文件(文件格式md)里就可以了。因为项目是基于YII2框架,所以用YII2的命令工具来实现。大概的效果图如下:
<?php
namespace console\controllers;
use Yii;
use yii\console\Controller;
/**
* 数据库表结构文档自动生成工具
* Class TableSchemaController
* @package console\controllers
* @author wuzhc <wuzhc2016@163.com>
* @since 2018-01-18
*/
class TableSchemaController extends Controller
{
/**
* 数据库表生成
*/
public function actionCreate()
{
global $argv;
if (!$argv[2] || strcasecmp($argv[2], 'help') === 0) {
echo "Usage: ./yii table-schema/create [all|tablename] [filename]\n";
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 \n", $argv[2]);
exit;
}
$tables = (array)$argv[2];
}
// 当前数据库没有表
if (count(array_filter($tables)) == 0) {
echo "Database has not table \n";
}
$root = dirname(dirname(dirname(__FILE__)));
$filename = $argv[3] ? $argv[3] : '/docs/note/数据库设计及字典说明.md';
$filePath = $root . $filename;
$fp = fopen($filePath, 'a+');
if (!$fp) {
echo "Open file failed \n";
}
foreach ($tables as $table) {
$schema = $db->getTableSchema($table, true);
if (!$schema->columns) {
continue;
}
fwrite($fp, "#### $schema->name 表 \n");
// 表头
$header = "| 字段名 | 类型 | 说明 | \n";
$header .= "|:--------:|:---------:|:-------:| \n";
fwrite($fp, $header);
// 字段
$row = '';
foreach ($schema->columns as $col => $obj) {
$comment = $obj->isPrimaryKey ? '主键' : $obj->comment;
$row .= "| $obj->name | $obj->dbType | $comment | \n";
}
fwrite($fp, $row);
fwrite($fp, "\r\n\r\n");
echo "$schema->name successfully \n";
}
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]\n";
exit;
}
$root = dirname(dirname(dirname(__FILE__)));
$filePath = $argv[2] ? $argv[2] : '/docs/note/数据库设计及字典说明.md';
$filePath = $root . $filePath;
if (!is_file($filePath)) {
echo "$filePath isn't exists \n";
exit;
}
$fp = fopen($filePath, 'w');
if (!$fp) {
echo "Open file failed \n";
}
fwrite($fp, '');
fclose($fp);
echo "Clear successfully \n";
}
}
执行命令:
./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]
程序很简单,但是很实用~~
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。