At the beginning of the project, the database table design is from the outline design to the detailed design, and then to the table structure in the database, with a complete set of documents;
However, with the evolution of the project and the changes in requirements, the database table structure has undergone a relatively large change (adding tables, adding fields, this type, etc.). For most small company projects, due to the irregular project management process, At this time, there is a high probability that the database design documents are not synchronized and then outdated;
Now, the problem is coming. For various reasons, you need to provide the design documents of the current database (you can't write the tables and fields one by one...);
So, how to automatically export a relatively neat word document from the existing database table?
Read from the Internet, some people provides a solution \ (☆ o ☆) /, can be a key export
.docfiles, see references; I am here on the basis of the author on the realization of a key export
Things here to open-source platform for
jetlinksproject data sheet as an example
PostgreSQLto export database design.
MySQLVersion of the implementation is to write a
RESTfulinterface for generating documentation, there is provided a method in a unit test class:
toWord()test method, can be generated directly
- Query all table names
select relname as table_name,(select description from pg_description where objoid=oid and objsubid=0) as table_comment from pg_class where relkind ='r' and relname NOT LIKE 'pg%' AND relname NOT LIKE 'sql_%' order by table_name;
- Query the field information of each table
select a.attname as 字段名称, format_type(a.atttypid,a.atttypmod) as 类型, (case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey=attnum and contype='p')>0 then 'PRI' when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey=attnum and contype='u')>0 then 'UNI' when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey=attnum and contype='f')>0 then 'FRI' else '' end) as 索引, (case when a.attnotnull=true then 'NO' else 'YES' end) as 允许为空, col_description(a.attrelid,a.attnum) as 说明 from pg_attribute a where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='ok');
There are three branches
MySQLExport the data table structure as
word(basically the original author's code).
- postgresql: Export the PostgreSQL data table structure as
- combined: Through multiple data sources, dynamically switch
If you only use this tool to generate database design documents and time is short, you can use it directly
If you want to understand the configuration of multiple data sources and dynamically switch data sources, you can switch to the
postgresql branch code
Based on the original author of the branch code, achieving the
PostgreSQLexport data word as a function table structure.
Implemented as a small tool, in order to avoid introducing complexity (
PostgreSQLmultiple data sources), a separate project is directly built here;
.docThe generated address is also hard-coded in the code:
If you have any questions or any bugs are found, please feel free to contact me.
Your comments and suggestions are welcome!