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.

• Supports the following DBs. *

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.

• If there is a foreign key, determine the relationship between the tables based on the foreign key
• If there is no foreign key, delete the option ** "-no implied" ** and execute it to automatically determine the relation from the column name and add it. ――However, this function is ** logic that searches for a column with the same name as the primary key column and connects it as a relation **, and an unintended relation may be created. (The id column is in every table, etc.)

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.

• You can click each box in the table. --Click to check details such as type and Not Null. --The ER diagram is displayed only for the tables that are directly connected. For applications with a large number of tables, it is very good to see and visualize noise.

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