One-click export of PostgreSQL database tables designed as word documents

background

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 MySQLdatabase design .docfiles, see references; I am here on the basis of the author on the realization of a key export PostgreSQLdatabase design .docfile.

effect

Things here to open-source platform for jetlinksproject data sheet as an example PostgreSQLto export database design.

2021-06-09-ExportPostgreSQL.png

run

MySQLVersion of the implementation is to write a Controllerprovides a RESTfulinterface for generating documentation, there is provided a method in a unit test class:

Running com.heartsuit.db2word.postgresql.service.impl.DataSourceDetailServiceImplTestthe toWord()test method, can be generated directly .docfile.

Reference SQL

  • 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;
2021-06-09-TableName.png
  • 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[1]=attnum and contype='p')>0 then 'PRI' 
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'UNI'
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=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');
2021-06-09-TableColumn.png

Source code

There are three branches

  • mysql: MySQLExport the data table structure as word(basically the original author's code).
  • postgresql: Export the PostgreSQL data table structure as word.
  • combined: Through multiple data sources, dynamically switch MySQLand PostgreSQL.

Note:

If you only use this tool to generate database design documents and time is short, you can use it directly mysqlor postgresqlbranch;

If you want to understand the configuration of multiple data sources and dynamically switch data sources, you can switch to the combinedbranch.

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 ( MySQLand PostgreSQLmultiple data sources), a separate project is directly built here;

.docThe generated address is also hard-coded in the code: D:/data/dbDetail.doc(DataSourceDetailServiceImpl.toWord method);

Source Code

https://github.com/heartsuit/db2word

Reference

https://github.com/BeliveYourSelf/lv617DbTest

https://www.cnblogs.com/nami/p/4112339.html


If you have any questions or any bugs are found, please feel free to contact me.

Your comments and suggestions are welcome!