PHP script automatically generates data dictionary for MySQL database tables

Why do you need a data dictionary

Through Navicat and other database management tools, we can also see the structure design of the data table, but if we make all the structure design of the data table can be previewed online, will it be more clear and clear, and also easier to find problems and timely optimization, more efficient.

A way to generate a data dictionary

Here I mainly use the showdoc online document to realize the online view of the data dictionary, mainly said that there are two ways to achieve: the official shell script and THE PHP script I wrote;

Official shell script: only supported in Linux servers running, official documents address: www.showdoc.com.cn/page/312209…
I wrote PHP scripts that support both Linux and Windows operating systems and are more flexible and controllable

Showdoc provides an open API interface to upload data structure information to the specified project in Markdown format. The file address is: www.showdoc.com.cn/page/102098

Key Information Configuration

Since the configuration information for connecting to the database and the address of the project to be uploaded are also different, these information need to be configured separately. I will not go into details, but look at the following code and comment information

// Database connection configuration information
private $host = '127.0.0.1';
private $user_name = 'root';
private $password = 'root';
private $db_name = 'test';
private $port = 3306;
private $conn;
/ / showdoc document API key configuration, access methods: https://www.showdoc.com.cn/page/741656402509783
private $api_key = '6b0ddb543b53f5002f6033cb2b00cec01908536369';
private $api_token = '9da3190d0dda1118de0e8bde08907fc51712469974';
Copy the code

Connect to and close the MySQL database

In order to facilitate fast cross-platform use, I use PHP native writing, so connecting to the database and querying data is written in PHP native writing, using the syntax features of PHP classes, connecting to the database in the constructor and closing the connection in the destructor.

GetMysqlDict constructor. */
public function __construct()
{
    // Create a connection
    $this->conn = new mysqli($this->host, $this->user_name, $this->password, $this->db_name, $this->port);
    // Check the connection
    if ($this->conn->connect_error) {
        exit("Database connection failed:" . $this->conn->connect_error);
    }
    $this->echoMsg('Database connection successful');
}

/** * destructor closes database connection */
public function __destruct()
{
    $this->conn->close();
    $this->echoMsg('Database connection closed');
}
Copy the code

Query table structure information

Connect to the database, then we can query the database information using Sql statement, use the statement show table status; You can find out all the data table information of the current connected library, and then query the data structure information of a specific table on the information_schema.COLUMNS table, and assemble an array and return it for use.

/** * get table list *@return array
 */
private function getTableList()
{
    // Check all tables
    $sql = 'show table status; ';
    $result = $this->conn->query($sql);
    // loop to get table data
    $table_list = array(a);while ($row = $result->fetch_assoc()) {
        $table_list[] = $row;
    }
    return $table_list;
}

/** * get table structure information *@param string $table
 * @return array
 */
private function getDictList($table = ' ')
{
    COLUMN_NAME,COLUMN_TYPE,NUMERIC_SCALE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT)
    $sql = "select * from information_schema.COLUMNS where table_schema='" . $this->db_name . "' and table_name='" . $table . "';";
    $result = $this->conn->query($sql);
    $dict_list = array(a);while ($row = $result->fetch_assoc()) {
        $dict_list[] = $row;
    }
    return $dict_list;
}
Copy the code

Upload to project via API interface

Access to the table data structure information, we can assemble the field information, and through an open apis interface to implement the project, you can see one of my test project www.showdoc.com.cn/13837363006… , you need to upload your project, you just need to follow the above instructions change the configuration, if you don’t want to upload showdoc official domain name, can use open source code on your own server setup, and deployment of good, can also be uploaded to their building project, specific can check related document.

/** * send interface request, generate document *@paramString $title page title (make sure it's unique) *@paramString $content Page content (Markdown and HTML supported) *@paramString $name Directory name (optional) *@paramInt $number specifies the page number (default: 99) *@return array
 */
private function apiPost($title = ' '.$content = ' '.$name = ' '.$number = 99)
{
    / / interface address, if it is their use of open source building, the interface address is: http://xx.com/server/index.php?s=/api/item/updateByApi
    $url = 'https://www.showdoc.cc/server/api/item/updateByApi';
    // Request parameters
    $data = array(
        'api_key'= >$this->api_key,
        'api_token'= >$this->api_token,
        'cat_name'= >$name.'page_title'= >$title.'page_content'= >$content.'s_number'= >$number
    );
    // Send a POST request
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
    $response = curl_exec($ch);
    curl_close($ch);
    return json_decode($response.true);
}
Copy the code

Scripts perform core processing

With the data fetch and upload methods above, you can loop through all the table structure fields, assemble them into Markdown format, and upload them to the project. The open API interface has a request frequency limit, that is, only 1000 requests per 10 minutes. Therefore, if there are too many requests in a single time, frequency control is needed to prevent the requests from failing and being successfully uploaded to the project.

/** * execute entry */
public function run()
{
    // Get the data table
    $table_list = $this->getTableList();
    $this->echoMsg('Total number of tables:' . count($table_list));
    // Loop through the table to get structure information
    $request_num = 0;
    foreach ($table_list as $table) {
        // Only 1000 requests in 10 minutes
        if ($request_num> =1000) {
            $request_num = 0;
            $this->echoMsg('Frequency control, please wait 10 minutes to continue.');
            sleep(600);
        }
        // Get the data structure
        $msg = 'table name:' . $table['Name'].'（' . $table['Comment'].') ';
        // Dictionary header information
        $table_dict = '# # # #' . $table['Name'].' ' . $table['Comment'] . PHP_EOL;
        $table_dict. ='type name | | field length whether | NULL default value | | |' comments . PHP_EOL;
        $table_dict. ='| -- - | -- - | -- - | -- - | -- - |' . PHP_EOL;
        // Get table field information
        $dict_list = $this->getDictList($table['Name']);
        foreach ($dict_list as $dict) {
            $c_name = $dict['COLUMN_NAME'];
            $c_type = $dict['COLUMN_TYPE'];
            $c_null = $dict['IS_NULLABLE'];
            $c_default = $dict['COLUMN_DEFAULT'];
            $c_comment = $dict['COLUMN_COMMENT'];
            $table_dict. ='|' . $c_name . '|' . $c_type . '|' . $c_null . '|' . $c_default . '|' . $c_comment . '|' . PHP_EOL;
        }
        // Display the data dictionary online using showdoc documents
        $response = $this->apiPost($table['Name'].$table_dict);
        if ($response['error_code'] = =0) {
            $msg. ='Document generation successful';
        } else {
            $msg. ='Failed to generate document (' . $response['error_message'].') ';
        }
        $request_num+ +;$this->echoMsg($msg); }}Copy the code

Complete source code

Above the split part, basically has put the core code are listed, but for everyone more convenient and quick use and feedback problems, a key copy of the complete source code directly run, I uploaded the code to the dating website Github, at the same time, also put the complete source code posted below, please call me contemporary living Lei Feng…… (It’s not easy for me, don’t mock me for taking up a lot of code.)


      

* User: gxcuizy * Date: 2021/05/100011 * Time: 15:17 PM * Class GetMysqlDict */
class GetMysqlDict
{
    // Database connection configuration information
    private $host = '127.0.0.1';
    private $user_name = 'root';
    private $password = 'root';
    private $db_name = 'test';
    private $port = 3306;
    private $conn;
    / / showdoc document API key configuration, access methods: https://www.showdoc.com.cn/page/741656402509783
    private $api_key = '6b0ddb543b53f5002f6033cb2b00cec01908536369';
    private $api_token = '9da3190d0dda1118de0e8bde08907fc51712469974';

    GetMysqlDict constructor. */
    public function __construct()
    {
        // Create a connection
        $this->conn = new mysqli($this->host, $this->user_name, $this->password, $this->db_name, $this->port);
        // Check the connection
        if ($this->conn->connect_error) {
            exit("Database connection failed:" . $this->conn->connect_error);
        }
        $this->echoMsg('Database connection successful');
    }

    /** * execute entry */
    public function run()
    {
        // Get the data table
        $table_list = $this->getTableList();
        $this->echoMsg('Total number of tables:' . count($table_list));
        // Loop through the table to get structure information
        $request_num = 0;
        foreach ($table_list as $table) {
            // Only 1000 requests in 10 minutes
            if ($request_num> =1000) {
                $request_num = 0;
                $this->echoMsg('Frequency control, please wait 10 minutes to continue.');
                sleep(600);
            }
            // Get the data structure
            $msg = 'table name:' . $table['Name'].'（' . $table['Comment'].') ';
            // Dictionary header information
            $table_dict = '# # # #' . $table['Name'].' ' . $table['Comment'] . PHP_EOL;
            $table_dict. ='type name | | field length whether | NULL default value | | |' comments . PHP_EOL;
            $table_dict. ='| -- - | -- - | -- - | -- - | -- - |' . PHP_EOL;
            // Get table field information
            $dict_list = $this->getDictList($table['Name']);
            foreach ($dict_list as $dict) {
                $c_name = $dict['COLUMN_NAME'];
                $c_type = $dict['COLUMN_TYPE'];
                $c_null = $dict['IS_NULLABLE'];
                $c_default = $dict['COLUMN_DEFAULT'];
                $c_comment = $dict['COLUMN_COMMENT'];
                $table_dict. ='|' . $c_name . '|' . $c_type . '|' . $c_null . '|' . $c_default . '|' . $c_comment . '|' . PHP_EOL;
            }
            // Display the data dictionary online using showdoc documents
            $response = $this->apiPost($table['Name'].$table_dict);
            if ($response['error_code'] = =0) {
                $msg. ='Document generation successful';
            } else {
                $msg. ='Failed to generate document (' . $response['error_message'].') ';
            }
            $request_num+ +;$this->echoMsg($msg); }}/** * get table list *@return array
     */
    private function getTableList()
    {
        // Check all tables
        $sql = 'show table status; ';
        $result = $this->conn->query($sql);
        // loop to get table data
        $table_list = array(a);while ($row = $result->fetch_assoc()) {
            $table_list[] = $row;
        }
        return $table_list;
    }

    /** * get table structure information *@param string $table
     * @return array
     */
    private function getDictList($table = ' ')
    {
        COLUMN_NAME,COLUMN_TYPE,NUMERIC_SCALE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT)
        $sql = "select * from information_schema.COLUMNS where table_schema='" . $this->db_name . "' and table_name='" . $table . "';";
        $result = $this->conn->query($sql);
        $dict_list = array(a);while ($row = $result->fetch_assoc()) {
            $dict_list[] = $row;
        }
        return $dict_list;
    }

    /** * send interface request, generate document *@paramString $title page title (make sure it's unique) *@paramString $content Page content (Markdown and HTML supported) *@paramString $name Directory name (optional) *@paramInt $number specifies the page number (default: 99) *@return array
     */
    private function apiPost($title = ' '.$content = ' '.$name = ' '.$number = 99)
    {
        / / interface address, if it is their use of open source building, the interface address is: http://xx.com/server/index.php?s=/api/item/updateByApi
        $url = 'https://www.showdoc.cc/server/api/item/updateByApi';
        // Request parameters
        $data = array(
            'api_key'= >$this->api_key,
            'api_token'= >$this->api_token,
            'cat_name'= >$name.'page_title'= >$title.'page_content'= >$content.'s_number'= >$number
        );
        // Send a POST request
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
        $response = curl_exec($ch);
        curl_close($ch);
        return json_decode($response.true);
    }

    /** * Prints information *@param string $msg
     */
    private function echoMsg($msg = ' ')
    {
        if (!empty($msg)) {
            $msg = "[" . date("Y-m-d H:i:s")."]" . $msg . PHP_EOL;
            echo $msg; @ob_flush(); @flush(); }}/** * destructor closes database connection */
    public function __destruct()
    {
        $this->conn->close();
        $this->echoMsg('Database connection closed'); }}// Instantiate the class and execute
$obj = new GetMysqlDict;
$obj->run();
Copy the code

The last

All the tools are for the convenience of you, me and others. I hope we can get along with more and better tools and methods to complete the work better and faster. If you have any good ideas, please share them with me.