Convert table create SQL to markdown table, Convert markdown table to table create SQL

format-mdtable2sql.php

<?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='用户表';

*/

format-sql2mdtable.php

<?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` |  | 创建时间
*/