How to Convert a MySQL Schema to GitHub Wiki Markdown Using Stored Procedures?

Converting MySQL Schema to GitHub Wiki Markdown

The original issue raised concerns about exporting a MySQL database schema into Markdown format, specifically as tables. To address this, a detailed response provides a solution involving the use of two stored procedures.

First Stored Procedure: describeTables_v2a

This procedure takes a database name as input and generates an output that resembles the output of DESCRIBE myTable for all tables in that database. It achieves this by utilizing the INFORMATION_SCHEMA database and manipulating the results to provide a more detailed and organized output. The output is stored in the reportDataDefs table of the Reporting101a database.

Parameters:

• dbName: The database name to report on.
• theSession: An OUT parameter to hold the session number assigned for this operation.
• deleteSessionRows: A boolean indicating whether to delete rows from the reportDataDefs table for this session after generating the output.
• callTheSecondStoredProc: A boolean indicating whether to automatically call the second stored procedure for pretty printing (DESCRIBE-like) output.

Steps:

1. Creates temporary tables to store the intermediate data.
2. Inserts data into the temporary tables from the INFORMATION_SCHEMA database, considering table and column names, types, nullability, keys, and extra information.
3. Populates the reportDataDefs table with the data from the temporary tables, including additional columns for column and type maximum lengths and counters for null, key, default, and extra values.
4. If callTheSecondStoredProc is TRUE, it calls another stored procedure named Print_Tables_Like_Describe that generates pretty-printed output and adds it to the reportOutput table.
5. If callTheSecondStoredProc is FALSE, it returns a result set of the data in the reportDataDefs table for the given session number.

Second Stored Procedure: Print_Tables_Like_Describe

This procedure takes a session number as input and retrieves the data from the reportDataDefs table. It then generates a Markdown-formatted output that resembles the DESCRIBE myTable output but for every table in the specified database.

Steps:

1. Iterates over the rows in the reportDataDefs table, extracting the necessary data.
2. Generates a table header for each table with the field name, type, nullability, key, default value, and extra information.
3. Formats each column's data into a consistent width and alignment.
4. Separates the formatted columns with vertical bars.
5. Returns the formatted output as a result set.

Usage:

To use the stored procedures, the user can provide the required database name and other parameters. Here's an example of the usage:

SET @theOutVar =-1; -- A variable used as the OUT variable below

-- Note: with `TRUE` as the 4th parameter, this is a one call deal. Meaning, you are done.
call Reporting101a.describeTables_v2a('stackoverflow',@theOutVar,false,true);

-- Primarily used if the 4th parameter above is false
call Reporting101a.Print_Tables_Like_Describe(@theOutVar); -- loads data for prettier results in chunk format.

This usage would first call the Reporting101a.describeTables_v2a stored procedure and retrieve the session number. Then, it would automatically call the Reporting101a.Print_Tables_Like_Describe stored procedure with that session number to generate the pretty-printed output. The output would be returned as a result set, which can be consumed and formatted further, such as converting it to a Markdown-formatted table.

The above is the detailed content of How to Convert a MySQL Schema to GitHub Wiki Markdown Using Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!