[컴] SchemaSpy 사용

reverse engineering / db schema / how to get db schema / ERD / er diagram

SchemaSpy 사용하기

• SchemaSpy • Database Documentation Built Easy.
• SchemaSpy

docker 사용

docker를 사용하면 간단하게 작업할 수 있다. 참고하자.

docker run -v "f:\sspy\:/output" schemaspy/schemaspy:latest -t mariadb -host 172.23.176.1 -port 3306 -db myappdb -u schemaspy -p schemaspypw -dp mariadb-java-client-3.0.8.jar

windows 에서 설치

• Installation — SchemaSpy 6.0.0 documentation  

• Graphviz 설치 : Graphviz Download

  • path 에 bin folder 위치를 추가해주자. 설치할 때 추가할 지를 묻는다.

• PostgreSQL JDBC 설치 : PostgreSQL JDBC Download

  • 여기서는 postgresql-42.2.20.jar 를 이용했다.

사용가능한 database

사용가능한 db 의 종류는 아래 command 로 확인할 수 있다.

java -jar schemaSpy_5.0.0.jar -dbhelp

run

여기서는 postgresql 의 database 에 대한 작업을 할 것이다.

postgresql 은 pgsql 로 -t pgsql 을 이용하면 된다. 옵션에 대한 설명은 Commonly used parameters | SchemaSpy 6.0.0 documentation 를 참고하자.

%java% -jar schemaSpy_5.0.0.jar -t pgsql -host <localhost> -db <database_name> -u <user-id> -p <password> -o f:\ -dp postgresql-42.2.20.jar

그리고 난 후 index.html 을 열어보면 된다.

<output_dir>/index.html

최근(2022-11-04)에 mysql 에 대한 작업을 할 때는 제대로 동작하지 않았다. -debug -all 옵션을 넣은경우, error 는 보이지만, 분석된 내용은 만들어졌다.

다음과 같은 error 가 보여졌다.

org.schemaspy.model.InvalidConfigurationException: Schema (-s/-schemas) was not provided and unable to deduce schema, schema is sometimes referred to as user/owner/database

%java% -jar schemaSpy_5.0.0.jar -t pgsql -host <localhost> -db <database_name> -u <user-id> -p <password> -o f:\ -dp postgresql-42.2.20.jar -debug -all

실행모습

D:\a\apps\schemaSpy>d:\a\apps\java\openjdk-jdk-15.0.1\bin\java.exe -jar schemaspy-6.1.0.jar -t pgsql -host localhost:5432 -db mydb -u mytestuser -p mypw -dp postgresql-42.2.20.jar -o f:\schema
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              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 DESKTOP-3TJIL3S with PID 13112 (D:\a\apps\schemaSpy\schemaspy-6.1.0.jar started by namh in D:\a\apps\schemaSpy)
INFO  - The following profiles are active: default
INFO  - Started Main in 0.771 seconds (JVM running for 1.047)
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 13.2
INFO  - Gathering schema details
Gathering schema details...............................................................WARN  - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
오류: p.proisagg 칼럼 없음
  Hint: 아마 "p.prolang" 칼럼을 참조하는 것 같습니다.
  Position: 34
(0sec)
Connecting relationships...............................................................(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
INFO  - Graphviz rendered set to ''
........(8sec)
Writing/diagramming detailsINFO  - Completed summary in 8 seconds
INFO  - Writing/diagramming details
............................................................(15sec)
Wrote relationship details of 60 tables/views to directory 'f:\schema' in 25 seconds.
View the results by opening f:\schema\index.html
INFO  - Wrote table details in 15 seconds
INFO  - Wrote relationship details of 60 tables/views to directory 'f:\schema' in 25 seconds.
INFO  - View the results by opening f:\schema\index.html
INFO  - StackTraces have been omitted, use `-debug` when executing SchemaSpy to see them

D:\a\apps\schemaSpy>

See Also

1. How to Create an ER Diagram in DBeaver | InMotion Hosting : dbeaver 에서도 ER diagram 을 확인할 수 있다. 개인적으로 이쪽이 가장 간편하고 좋은 듯 하다.