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.
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.
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.
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 **.
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.
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. ** **
I asked for 3 points for the ER diagram generation tool.
After searching around for a while, I found a tool called ** SchemaSpy **. The usage is briefly summarized below.
** SchemaSpy ** is made of Java, so ** JRE ** is required.
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) |
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
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
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.)
EC-CUBE / ec-cube I tried to generate an ER diagram by connecting to the DB immediately after setup.
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