ER Diagram 自動繪圖機 SchemaSpy

上學期教資料庫時發現 SchemaSpy 這個好物， 可以幫各種不同的資料庫畫 ER Diagram。

首先在 releases 頁面 下載最新版。 以目前來說， 最後我的下載指令是：

wget https://github.com/schemaspy/schemaspy/releases/download/v6.1.0/schemaspy-6.1.0.jar
sudo apt install graphviz

上面也順便安裝 graphviz 套件， 畫圖時要用到。

一、 sqlite

這一節拿 chinook database 來作為 sqlite 的示範資料庫。 主要參考 這個討論串。

wget https://github.com/xerial/sqlite-jdbc/releases/download/3.32.3.2/sqlite-jdbc-3.32.3.2.jar
wget https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
unzip chinook.zip

上面幾句分別下載 chinook 資料庫與 sqlite 的 "驅動程式， 因為 SchemaSpy 透過 jdbc 介面及驅動程式連接各種不同的資料庫如 sqlite、 mysql、 postgresql 等等。 再來建立 sqlite.properties 設定檔， 完整貼入以下內容 (<db> 一樣照貼， 不必更動)：

description=SQLite
connectionSpec=jdbc:sqlite:<db>
driver=org.sqlite.JDBC
driverPath=sqlite-jdbc-3.32.3.2.jar

最後 java -jar schemaspy-6.1.0.jar -debug -t sqlite -o /tmp -sso -cat chinook -s chinook -db chinook.db 這會在 /tmp 目錄底下產生很多檔案。 在 diagrams/summary 子目錄底下會看到幾個 .dot 檔。 執行 dot -Tsvg relationships.real.large.dot > ~/chinook.svg 就會產生右圖。

順便一提， 我覺得用 sqlite 幫新手上手 sql 語法是最快最簡單的方式， 可以完全省略複雜的系統與帳號管理等等。 更要大推 litecle 具有 command completion 功能的改良版 sqlite 命令列介面。

二、 mysql

本節參考 Gustavo Ponce 的文章。

順便先閃電筆記一下剛安裝好 mysql-server mysql-client 兩個套件之後的幾個初始動作 (因為我平常根本沒在用 艸)： 先以 linux root 的身份免密碼登入 mysql root， 然後：

CREATE USER 'ckhung'@'localhost' IDENTIFIED BY '神秘的密碼';
GRANT ALL PRIVILEGES ON *.* TO 'ckhung'@'localhost' WITH GRANT OPTION;

以後就可以用 ckhung 的身份這樣登入： mysql -p 。 再下載 範例資料庫， 解壓縮之後， mysql -p < mysqlsampledatabase.sql 如此就建立了一個名為 classicmodels 的資料庫。

處理 mysql 資料庫， 不需要建立 *.properties 設定檔， 但一樣需要 jdbc 驅動程式： 從 這裡 進去， 選擇作業系統、 選擇版本、 按 「Download」 下載 java_*.deb、 不需要註冊或登入， 直接點最下方的 「No thanks, just start my download」。 再用 sudo dpkg -i mysql-connector-java*.deb 安裝， 並用 dpkg -L mysql-connector-java 查出驅動程式的完整路徑。 以我所安裝的版本為例， 豆 /usr/share/java/mysql-connector-java-8.0.26.jar 。 於是： java -jar schemaspy-6.1.0.jar -t mysql -host localhost -u ckhung -p '神秘的密碼' -db classicmodels -s classicmodels -dp /usr/share/java/mysql-connector-java-8.0.26.jar -o /tmp 同樣會在 /tmp 目錄底下產生很多檔案， 包含 /tmp/diagrams/summary/relationships.real.large.dot 。

三、 postgresql

本節參考 這個 gist。 安裝好 postgresql postgresql-contrib 兩個套件之後， 先 sudo -i -u postgres 變身成 postgres 這個使用者， 並以他的身份執行：

createuser ckhung	# 建立 ckhung 這個用戶
wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
# 抓回範例資料庫
unzip dvdrental.zip	# 解壓縮
createdb dvdrental	# 建立範例資料庫
pg_restore -c --if-exists -d dvdrental dvdrental.tar
# 匯入範例資料庫

再 psql dvdrental 用 psql 命令列開啟範例資料庫， 然後：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ckhung;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ckhung;
ALTER USER ckhung WITH PASSWORD '神秘的密碼';

按兩次 ^d 離開 psql、 離開 sudo。 現在可以用我自己的身份 (ckhung) 免密碼開啟範例資料庫： psql dvdrental ， 這稱為 ident authentication。 不過如果要用 dbeaver 或 schemaspy 透過 jdbc 連線， 還是需要如上設定密碼。 再來安裝並找到 postgresql 的 jdbc 驅動程式完整路徑， 最後就可以畫圖了：

sudo apt install libpostgresql-jdbc-java
dpkg -L libpostgresql-jdbc-java | grep 'jdbc.*\.jar'
java -jar schemaspy-6.1.0.jar -t pgsql \
  -s public -db dvdrental -u ckhung -p '神秘的密碼' \
  -host localhost -o /tmp \
  -dp /usr/share/java/postgresql-jdbc4.jar

四、 補充說明

處理 sqlite 的指令反而比較囉嗦一點； 至於 mysql 跟 postgresql 都不需要另外建立 *.properties 設定檔。

產生 svg 檔時， 裡面會引用到 images/ 子目錄底下的 *Key*.png 。 必須把那些 png 檔複製到 svg 檔附近的目錄， 並且更改 svg 檔裡面提及它們的 <image .../> 指令裡的路徑， 才會顯示 primary key 與 foreign key 的圖像。

其實可以直接用瀏覽器開啟輸出目錄底下的 index.html 檔 (以本文而言， 就是 /tmp/index.html) 有更多更完整的分析資訊。 不過對於資料庫玩票性質的我來說， 能畫出 ER Diagram 就很開心了 :-) (連「資料庫」tag 都不想開，這篇套用的竟然是 「美工繪圖」 tag， ...)