SchemaSpy:輕松構(gòu)建數(shù)據(jù)庫文檔

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué),十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn),目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net


文章目錄

        SchemaSpy 簡介
        SchemaSpy 安裝
        SchemaSpy 使用
        總結(jié)

大家好,我是只談技術(shù)不剪發(fā)的 Tony 老師。

數(shù)據(jù)庫文檔對(duì)于系統(tǒng)開發(fā)至關(guān)重要,它可以幫助 DBA、開發(fā)和測試人員、數(shù)據(jù)分析師和數(shù)據(jù)科學(xué)家、技術(shù)顧問和維護(hù)人員以及業(yè)務(wù)人員等理解、開發(fā)、使用和維護(hù)數(shù)據(jù)庫。今天給大家介紹一個(gè)用于生成數(shù)據(jù)庫文檔的免費(fèi)開源軟件:SchemaSpy。

如果覺得文章有用,歡迎評(píng)論??、點(diǎn)贊??、推薦??
SchemaSpy 簡介

SchemaSpy 是一個(gè)基于 Java(Java 8 以上版本)的數(shù)據(jù)庫文檔工具,用于分析數(shù)據(jù)庫模式中的表結(jié)構(gòu)等元數(shù)據(jù)并生成基于瀏覽器的可視化文檔。用戶可以通過 HTML 鏈接和實(shí)體關(guān)系圖(ERD)查看數(shù)據(jù)庫中的子表和父表之間的層級(jí)關(guān)系。

SchemaSpy 是一個(gè)免費(fèi)的軟件,基于 LGPL V3 協(xié)議發(fā)布,源代碼位于 GitHub。SchemaSpy 的主要功能包括:

    支持絕大多數(shù) JDBC 兼容的數(shù)據(jù)庫 ;
    生成 ER 圖,支持保存為圖片;
    顯示字段之間的外鍵關(guān)聯(lián)和級(jí)聯(lián)操作;
    顯示存儲(chǔ)過程和函數(shù)源代碼;
    顯示視圖及定義;
    在注釋中支持 Markdown 渲染;
    支持導(dǎo)出為 Excel、CSV 以及 PDF 文件;
    支持額外的元數(shù)據(jù),具體參考 SchemaMeta;
    顯示數(shù)據(jù)庫中的異常情況,例如沒有任何索引的表。

SchemaSpy 提供了一個(gè)示例數(shù)據(jù)庫文檔,可以讓我們直觀地感受一下最終的文檔結(jié)果。例如表的結(jié)構(gòu):


整體的 ERD:


SchemaSpy 安裝

SchemaSpy 基于 Java 開發(fā),我們需要先安裝一個(gè) Java 運(yùn)行環(huán)境。安裝完成后查看一下版本:

PS C:\Users\tony> java -version
java version "1.8.0_281"
Java(TM) SE Runtime Environment (build 1.8.0_281-b09)
Java HotSpot(TM) Client VM (build 25.281-b09, mixed mode)
PS C:\Users\tony>

 

SchemaSpy 本身的安裝非常簡單,它就是一個(gè)可執(zhí)行的 jar 文件,我們可以直接點(diǎn)擊下載最新的文件 schemaspy-vX.Y.Z.jar。
SchemaSpy 使用

SchemaSpy 是一個(gè)命令行工具,輸入以下命令查看幫助信息:

C:\Users\tony>java -jar schemaspy-6.1.0.jar -help
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.1.0

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.1.0 on LAPTOP-DGRB6HD9 with PID 3228 (C:\Users\tony\schemaspy-6.1.0.jar started by tony in C:\Users\tony)
INFO  - The following profiles are active: default
INFO  - Started Main in 1.802 seconds (JVM running for 2.282)
INFO  - Usage: java -jar /C:/Users/tony/schemaspy-6.1.0.jar [options]
  Options:
    ?, -?, /?, -h, help, -help, --help
      show general usage and available options
    -dbHelp, -dbhelp, --dbHelp, --dbhelp
      Show built-in database types and their required connection parameters
    -l, --license
      Print license, it will first print GPL and then LGPL (LGPL is addition to GPL)
    -cat, --catalog, catalog, schemaspy.cat, schemaspy.catalog
      catalog
    -db, -database-name, schemaspy.db, schemaspy.database-name
      Name of database to connect to
    -t, --database-type, database-type, schemaspy.t, schemaspy.database-type
      type of database
      Default: ora
    -debug, --debug, debug, schemaspy.debug
      Enable debug logging
      Default: false
    -degree, schemaspy.degree
      Limit the degree of separation, allowed values 1 or 2, defaults to 2
      Default: 2
    -dp, --driverPath, driverPath, schemaspy.dp, schemaspy.driverPath
      path to look for JDBC drivers overrides driverPath in [databaseType].properties, supports directory, will add
      directory and recurse to add all content. Supports multiple paths using os dependent pathSeparator.
    -nohtml, --no-html, nohtml, schemaspy.nohtml
      Skip html generation
      Default: false
    -o, --outputDirectory, outputDirectory, schemaspy.o, schemaspy.outputDirectory
      directory to place the generated output in
    -port, --port, port, schemaspy.port

    -s, --schema, schema, schemaspy.s, schemaspy.schema
      name of the schema to analyze (defaults to the specified user)
    -sso, --single-sign-on, schemaspy.sso, schemaspy.single-sign-on
      Remove requirement for user
      Default: false
    -u, --user, user, schemaspy.u, schemaspy.user
      connect to the database with this user id
    -vizjs, schemaspy.vizjs
      Use viz.js instead of graphviz graphvizDot executable
      Default: false

Go to http://schemaspy.org for a complete list/description of additional parameters.

Sample usage using the default database type (implied -t ora):

 java -jar schemaSpy.jar -db mydb -s myschema -u devuser -p password -o output

 

運(yùn)行 SchemaSpy 時(shí)可以通過命令行指定參數(shù),也可以使用預(yù)定義的配置文件。SchemaSpy 默認(rèn)使用 ./schemaspy.properties 作為配置文件,我們也可以使用命令行參數(shù)指定配置文件:

java -jar schemaspy.jar -configFile path/to/config.file

 

以下是一個(gè)配置文件的示例:

# 指定數(shù)據(jù)庫類型. 使用 -dbhelp 參數(shù)查看詳細(xì)信息.
schemaspy.t=mssql
# 可選參數(shù),指定替代的 jdbc 驅(qū)動(dòng).
schemaspy.dp=path/to/drivers
# 數(shù)據(jù)庫連接屬性:主機(jī)、端口、數(shù)據(jù)庫名、用戶、密碼
schemaspy.host=server
schemaspy.port=port_num
schemaspy.db=db_name
schemaspy.u=database_user
schemaspy.p=database_password
# 輸出結(jié)果的存儲(chǔ)目錄
schemaspy.o=path/to/output
# 需要?jiǎng)?chuàng)建 ERD 的數(shù)據(jù)庫模式 Schema
schemaspy.s=dbo

 

使用命令行參數(shù)運(yùn)行的 SchemaSpy 命令如下:

java -jar schemaspy-vX.Y.Z.jar -t dbType -dp path/to/driver -db dbName -host server -port port_num [-s schema] -u user [-p password] -o outputDir -vizjs

 

命令行的參數(shù)和上面的配置文件一致,但是優(yōu)先級(jí)更高。例如,以下是一個(gè)連接 PostgreSQL 數(shù)據(jù)庫創(chuàng)建文檔的命令:

C:\Users\tony>java -jar schemaspy-6.1.0.jar -t pgsql11 -dp D:/Applications/DBeaver/drivers/postgresql-42.2.5.jar -host 127.0.0.1 -port 5432 -db hrdb -u postgres -p Pg@2020 -o ./pg -vizjs
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.1.0

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.1.0 on LAPTOP-DGRB6HD9 with PID 13932 (C:\Users\tony\schemaspy-6.1.0.jar started by tony in C:\Users\tony)
INFO  - The following profiles are active: default
INFO  - Started Main in 1.35 seconds (JVM running for 1.944)
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 13.1
INFO  - Gathering schema details
Gathering schema details......................................(0sec)
Connecting relationships......................................(1sec)
Writing/graphing summary.INFO  - Gathered schema details in 1 seconds
INFO  - Writing/graphing summary
........(38sec)
Writing/diagramming detailsINFO  - Completed summary in 38 seconds
INFO  - Writing/diagramming details
.................Java HotSpot(TM) Client VM warning: CodeCache is full. Compiler has been disabled.
Java HotSpot(TM) Client VM warning: Try increasing the code cache size using -XX:ReservedCodeCacheSize=
CodeCache: size=32768Kb used=31969Kb max_used=31969Kb free=798Kb
 bounds [0x02230000, 0x04230000, 0x04230000]
 total_blobs=6108 nmethods=5798 adapters=237
 compilation: disabled (not enough contiguous free space left)
..................(9sec)
Wrote relationship details of 35 tables/views to directory '.\pg' in 52 seconds.
View the results by opening .\pg\index.html
INFO  - Wrote table details in 9 seconds
INFO  - Wrote relationship details of 35 tables/views to directory '.\pg' in 52 seconds.
INFO  - View the results by opening .\pg\index.html

 

運(yùn)行成功后,打開輸出目錄中的 index.html 文件查看數(shù)據(jù)庫文檔。


關(guān)于命令行參數(shù)的詳細(xì)介紹,可以參考官方文檔。
總結(jié)

本文介紹了開源數(shù)據(jù)庫文檔 SchemaSpy 的安裝和使用。SchemaSpy 的高級(jí)功能包括設(shè)置連接屬性、自定義數(shù)據(jù)庫類型、添加額外的注釋、單點(diǎn)登錄、自定義查詢語句等,大家可以自行探討。