Skip to content

Instantly share code, notes, and snippets.

@inhere
Last active June 2, 2021 02:15
Show Gist options
  • Save inhere/76d8ee3fd51dd240ef6909cebb958118 to your computer and use it in GitHub Desktop.
Save inhere/76d8ee3fd51dd240ef6909cebb958118 to your computer and use it in GitHub Desktop.
Convert table create SQL to markdown table, Convert markdown table to table create SQL
<?php
function format_mdtable2sql(string $mdStr): string
{
$lines = array_values(
array_filter(
explode("\n", trim($mdStr))
)
);
// $table = trim($lines[0], '# ');
[$comment, $table] = array_filter(explode(' ', trim($lines[0], '`:#\' ')));
$table = trim($table, '` ');
$title = trim($lines[1], '| ');
$number = count(explode('|', $title));
// rm name, title and split line
unset($lines[0], $lines[1], $lines[2]);
$sql = <<<SQL
CREATE TABLE `{{TABLE}}` (
{{FIELDS}},
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{{COMMENT}}';
SQL;
$fields = [];
// var_dump($lines);die;
foreach ($lines as $line) {
$line = str_replace(['(', ')'], ['(', ')'], trim($line, '| '));
$nodes = array_map(function ($value) {
return trim($value, '`\': ');
}, explode('|', $line));
$field = $nodes[0];
$type = $nodes[1];
$isInt = stripos($type, 'int') !== false;
$nodes[0] = " `{$field}`";
// create_time
$isTimeField = stripos($field, 'time') !== false;
if ($isTimeField) {
$nodes[1] = 'INT(10) UNSIGNED';
} else {
$upType = strtoupper($type);
if ($upType === 'STRING') {
$upType = 'VARCHAR(128)';
} elseif ($upType === 'INT') {
$upType = 'INT(11) UNSIGNED';
}
$nodes[1] = $upType;
}
// 默认值
if ($field === 'id') {
$nodes[1] = 'INT(11) UNSIGNED';
$nodes[3] = 'AUTO_INCREMENT';
unset($nodes[2]);
} else {
// 是否为空
$nodes[2] = in_array($nodes[2], ['否', 'No', 'no', 'n'], true) ? 'NOT NULL' : '';
if (isset($nodes[3])) {
$defValue = $isInt ? (int)$nodes[3] : $nodes[3];
$nodes[3] = "DEFAULT '{$defValue}'";
}
}
if (isset($nodes[4])) {
$nodes[4] = 'COMMENT ' . ($nodes[4] ? "'{$nodes[4]}'" : "'{$field}'");
}
$fields[] = implode(' ', $nodes);
}
return strtr($sql, [
'{{TABLE}}' => $table,
'{{FIELDS}}' => implode(",\n", $fields),
'{{COMMENT}}' => $comment,
]);
}
$mdStr = <<<MD
### 用户表 `user`
字段名 | 类型 | 是否为空 | 默认值 | 注释
-------|------|---------|--------|-----
`id` | `INT(11) UNSIGNED` | `No` | | 主键
`name` | `VARCHAR(32)` | `Yes` | | 名称
`appid` | `VARCHAR(64)` | `Yes` | | 应用ID
`description` | `VARCHAR(128)` | `Yes` | | 描述说明
`deleted` | `TINYINT(2) UNSIGNED` | `No` | `2` | 删除状态
`utime` | `INT(10) UNSIGNED` | `No` | `0` | 更新时间
`ctime` | `INT(10) UNSIGNED` | `No` | | 创建时间
MD;
echo format_mdtable2sql($mdStr), "\n";
/*
Will output:
CREATE TABLE `user` (
`id` INT(11) UNSIGNED AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(32) DEFAULT '' COMMENT '名称',
`appid` VARCHAR(64) DEFAULT '' COMMENT '应用ID',
`description` VARCHAR(128) DEFAULT '' COMMENT '描述说明',
`deleted` TINYINT(2) UNSIGNED NOT NULL DEFAULT '2' COMMENT '删除状态',
`utime` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '更新时间',
`ctime` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
*/
<?php
function format_sql2mdtable (string $sql)
{
$sql = trim($sql);
$rows = explode("\n", $sql);
$tableName = trim(array_shift($rows), '( ');
$tableName = trim(substr($tableName, 12));
$tableEngine = array_pop($rows);
if (($pos = stripos($tableEngine, ' comment')) !== false) {
$tableComment = trim(substr($tableEngine, $pos + 9), ';\'"');
}
$fields = [];
$mdNodes = [
' 字段名 | 类型 | 是否为空 | 默认值 | 注释 ',
'-------|------|---------|--------|-----'
];
$indexes = [];
foreach ($rows as $row) {
$line = '';
$row = trim($row, ', ');
if (is_index_setting($row)) {
$indexes[] = $row;
continue;
}
[$field, $other] = explode(' ', $row, 2);
[$type, $other] = explode(' ', trim($other), 2);
if (($pos = stripos($other, 'comment ')) !== false) {
$comment = trim(substr($other, $pos + 9), '\'"');
$other = substr($other, 0, $pos);
} else {
$comment = str_replace('_', ' ', $field);
}
$field = trim($field, '`');
$upOther = strtoupper($other);
$isInt = stripos($type, 'int') !== false;
$upType = strtoupper($type);
if ($isInt && strpos($upOther, 'UNSIGNED ') !== false) {
$upType .= ' UNSIGNED';
}
$allowNull = 'Yes';
if (strpos($other, ' NOT NULL') !== false) {
$allowNull = 'No';
}
$default = '';
if (($pos = strpos($other, 'DEFAULT ')) !== false) {
$default = trim(substr($other, $pos + 8), '\'" ');
}
$fields[] = $field;
$mdNodes[] = sprintf(
'`%s` | `%s` | `%s` | %s | %s',
$field,
$upType,
$allowNull,
$default !== '' ? '`' . $default . '`' : '',
$comment
);
}
echo "Fields: \n", implode("\n", $fields), "\n";
echo "### $tableComment $tableName\n\n", implode("\n", $mdNodes), "\n";
if ($indexes) {
echo "\n> INDEXS: " . implode(', ', $indexes), "\n";
}
}
/**
* is_index_setting
* @param string $row
* @return boolean
*/
function is_index_setting(string $row): bool
{
if (strpos($row, '`') === 0) {
return false;
}
if (stripos($row, 'PRIMARY KEY') === 0) {
return true;
}
if (stripos($row, 'UNIQUE KEY') === 0) {
return true;
}
if (stripos($row, 'INDEX KEY') === 0) {
return true;
}
return false;
}
$sql = <<<SQL
CREATE TABLE `user` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(32) DEFAULT '' COMMENT '名称',
`appid` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '应用ID',
`description` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '描述说明',
`deleted` TINYINT(2) UNSIGNED NOT NULL DEFAULT '2' COMMENT '删除状态',
`utime` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '更新时间',
`ctime` INT(10) UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
SQL;
format_sql2mdtable($sql);
/*
will output:
### 用户表 `user`
字段名 | 类型 | 是否为空 | 默认值 | 注释
-------|------|---------|--------|-----
`id` | `INT(11) UNSIGNED` | `No` | | 主键
`name` | `VARCHAR(32)` | `Yes` | | 名称
`appid` | `VARCHAR(64)` | `Yes` | | 应用ID
`description` | `VARCHAR(128)` | `Yes` | | 描述说明
`deleted` | `TINYINT(2) UNSIGNED` | `No` | `2` | 删除状态
`utime` | `INT(10) UNSIGNED` | `No` | `0` | 更新时间
`ctime` | `INT(10) UNSIGNED` | `No` | | 创建时间
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment