If you get tired of drawing an ER diagram

Introduction

Regardless of ** new development ** / ** maintenance development **, it is very important to understand the data structure during system development. ** ER Diagram (E-R Diagram) ** is useful for understanding the data structure.

Trouble with ER diagram creation

Documents and programs ** "dissociate" **

It is a very useful ** ER diagram **, but as with any document, there is a ** divergence from the entity (program) **.

In order to avoid ** "dissociation" **, we try to take synchronization regularly, but not all members always challenge development with a clear consciousness. The documentation will be devastated.

Documents and databases also ** "dissociate" **

Not only ** documents and programs **, but also ** documents and databases ** are dissociated.

** The definition of the database (table, index, view) ** that is already running as a service ** and the ** document ** are divergent.

Dealing with what is in service by making it the "creator"

A long-standing approach to dealing with the discrepancy between ** documents ** and ** programs ** is to wake up documents from what is in service (** reverse **). ** Program ** → ** Document ** and the contents are reflected.

In the same way, it would be nice if the contents could be reflected as ** database ** → ** document **.

You don't want to knead with the mouse, do you?

I like the command line.

I would like to finish my work without using a mouse if possible.

Contrary to my feelings, visual documents like ER diagrams force mouse operation.

Multi-platform support is required if multiple people are developing

If you're developing with multiple people, a tool that supports ** multi-platform ** is preferable. ** If it is a tool that can only be run on Windows, the load will be concentrated on some members. ** **

There are three requirements for the ER diagram generation tool

I asked for 3 points for the ER diagram generation tool.

  1. Can be reversed from the existing DB
  2. Can be executed with CUI
  3. Works in multiple environments (Windows / Mac / Unix OS)

After searching around for a while, I found a tool called ** SchemaSpy **. The usage is briefly summarized below.

** Try using SchemaSpy **

About the execution environment

** SchemaSpy ** is made of Java, so ** JRE ** is required.

About supported DB

We use ** JDBC --Wikipedia ** to connect to the DB, and connect to any DB for which a JDBC driver is provided. Is possible.

Type Description
db2 IBM DB2 with 'app' Driver
db2net IBM DB2 with 'net' Driver
udbt4 DB2 UDB Type 4 Driver
db2zos DB2 for z/OS
derby Derby (JavaDB) Embedded Server
derbynet Derby (JavaDB) Network Server
firebird Firebird
hsqldb HSQLDB Server
informix Informix
maxdb MaxDB
mssql Microsoft SQL Server
mssql05 Microsoft SQL Server 2005
mssql-jtds Microsoft SQL Server with jTDS Driver
mssql05-jtds Microsoft SQL Server 2005 with jTDS Driver
mysql MySQL
ora Oracle with OCI8 Driver
orathin Oracle with Thin Driver
pgsql PostgreSQL
sqlite SQLite
sybase Sybase Server with JDBC3 Driver
sybase2 Sybase Server with JDBC2 Driver
teradata Teradata (requires -connprops)

setup

Prepare a virtual environment for verification. Start ** Vagrant **.

$ echo '''\
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "centos7.1"
  config.ssh.insert_key = false
  config.vm.network "private_network", ip: "192.168.33.41"
  config.vm.provider "virtualbox" do |vb|
    vb.customize ["modifyvm", :id, "--memory", "1024"]
  end
end
''' > Vagrantfile

$ vagrant up

After booting, log in to the virtual environment.

$ vagrant ssh

Install the required application.

#Install JDK and Graphviz
$ sudo yum install -y \
    'graphviz*' \
    java-1.8.0-openjdk-devel \
;

# /usr/local/Place two Jar files in src.
$ sudo mkdir -p /usr/local/src

$ cd /usr/local/src/

# 1.SchemaSpy body
$ sudo wget http://jaist.dl.sourceforge.net/project/schemaspy/schemaspy/SchemaSpy%205.0.0/schemaSpy_5.0.0.jar

# 2.JDBC driver for connecting to PostgreSQL
$ sudo wget https://jdbc.postgresql.org/download/postgresql-9.4.1212.jar

Create a startup script to generate an ER diagram

Since there are many arguments when executing the command line, create a startup script with the name ** schemaspy ** and wrap the fixed arguments each time.

This time I accessed the local ** "ebis" ** database and set it to generate an ER diagram.

schemaspy


#!/usr/bin/env bash

#The directory where the JAR file was placed earlier
JDBC_DRIVER_DIR=/usr/local/src
JDBC_DRIVER_PATH=${JDBC_DRIVER_PATH:-$(echo ${JDBC_DRIVER_DIR}/*.jar| sed "s/ /:/g")}

#here"PostgreSQL"Specialized in
DATABASE_TYPE=${DATABASE_TYPE:-pgsql}

#Connection target DB host
DB_HOST=${DB_HOST:-localhost}
#Connection target DB name
DB_NAME=${DB_NAME:-ebis}
#Connection target DB user
DB_USER=${DB_USER:-postgres}
#Connection target DB password
DB_PASS=${DB_PASS:-}


[ ! -d "./${DB_NAME}" ] && mkdir -p "./${DB_NAME}"


# -hq           :High quality mode. The output result will be beautiful!
# -noimplied    :Turn off the function that "guesses the relationship between existing tables without foreign keys and reflects them in the ER diagram"
java \
  -jar      "/usr/local/src/schemaSpy_5.0.0.jar" \
  -hq \
  -noimplied \
  -o        "./${DB_NAME}" \
  -charset  utf-8 \
  -dp       "${JDBC_DRIVER_PATH}" \
  -t        "${DATABASE_TYPE}" \
  -host     "${DB_HOST}" \
  -s        "public" \
  -db       "${DB_NAME}" \
  -u        "${DB_USER}" \
  -p        "${DB_PASS}" \
;
#Grant execute permission
$ chmod u+x schemaspy

Run

Launch the script.

$ ./schemaspy

You can see that the directory ** "ebis" ** is created and the DB definition information and ER diagram are generated.

(Ebis is the DB name of the system called Ad Ebis that I was involved in before, but unfortunately I can't show you the DB information of Ad Ebis here.)

Output sample (eccube3)

EC-CUBE / ec-cube I tried to generate an ER diagram by connecting to the DB immediately after setup.

eccube3-er.png

QuickStart script

I made it into a shell script. If you are in a Redhad environment, it will automatically install what is not installed in advance and then execute it.

GitHub - genzouw/schemaspy-cli: schemaspy commandline interface


Very useful for getting an overview of the system. ** SchemaSpy ** If you know a better tool, please let me know.

that's all

Recommended Posts

If you get tired of drawing an ER diagram
If you get tired of "Spring Boot", why not try "jooby"?
What to do if you can't get the text of an element in Selenium
What to do if you get an error during rails db: reset
If you delete an immutable collection with JPA, you will naturally get an UnsupportedOperationException.
What to do if you get an uninitialized constant Likes Controller error
Get the type of an array element to determine if it is an array
What to do if you get an error when you hit Heroku logs
[RSpec] ArgumentError: wrong number of arguments If you get an error, you may have to worry about variable naming.
What to do if you get an error on heroku rake db: migrate
What to do if you get an Argument Error: wrong number of arguments (given 2, expected 0) in your RSpec test
Implementation of delete function (if you have foreign_key)
What to do if you get a wrong number of arguments error in binding.pry
What to do if you get the error Couldn't find Item without an ID