pyhedgehog / database-diagrams

automatic database documentation solution using shemaspy

database-diagrams

Please support a project by simply putting a Github star. Share this library with friends on Twitter and everywhere else you can.

Thanks to the SchemaSpy developers

This code was made to implementing full database documentation per server that wasn't implementing in the SchemaSpy originally project. This project also include a crontab scheduling, a nginx server to show the results and a java application to manager and coordinate the execution and build a main page to redirect to all databases sites made.

Run Docker Pull

docker pull krismorte/databasediagrams:2.1

Run a test

This project include a docker-compose file to test the solution locally running against 2 mysql and 1 postgres containers

git clone https://github.com/krismorte/database-diagrams.git
cd database-diagrams/
docker-compose up

on dbconf folder you will find .prop files with the database servers configuration and also a mysql and postgres script used to create the diagram_test database when the container is created.

Run Docker Container

To use this solution you have to edit the .prop files with your own servers and run this follow command:

docker run -it --rm -p 80:80 -v $PWD/dbconf:/dbconf --name databasediagrams krismorte/databasediagrams:2.1

*this command will show the output and lock the terminal, to run in production please change the -it option for -d

You can put your company name at the main page using the option -e COMPANY_NAME=MyCompany

access the url http://localhost/

The output don't show the schemaspy log to see that run the follow command:

docker exec databasediagrams cat /var/log/script.log

Depends on the amount of databases and tables this process can take a long time to run, be aware about this. By default the script will run everyday at as 07AM you can overwrite the env CRN

, but you can run manually to see the result. This process can take several minutes depends the database sizes

This solution was prepared and tested on mysql, sql server, postgres and oracle. The Schemaspy support much more databases as you can see here but my solution wrapper the schemaspy so I will add more database as soon as possible.

schemaspy supported by database-diagrams are mssql, mssql05, mssql08, mysql, mariadb, postgres, postgres11, orathin

Oracle Details

As Oracle remove jdbc jar from maven repository you need to download the jdbc manually on the official site and put the jar file on the same folder with your .prop files (ojdbc6.jar, ojdbc7.jar or ojdbc8.jar). Also you have to inform the SID on oracle prop file db.oracsid= and the db.query has no efect on Oracle.

Build the Image

#docker build -t krismorte/databasediagrams:2.1 .

the full documentarion of the container is here

PROP file

• Required
• optional

File's configuration

• schemaspy.db.type=mysql this fild is necessary to schemaspy knows about the database
• db.server=172.17.0.1 server name
• db.user=root database user name
• db.password=secret user's password
• db.port=3307 just necessary if you are using a different port
• db.query=SELECT DISTINCT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME ='performance_schema' you can customize the database query to include or exclude some database. Has no effect on Oracle.
• db.oracsid=xe required only when the type was orathin

Under the hook

The entry point will config the cron job, config the oracle on the local maven repository when it's available, running the application for the first time and up the nginx server. The cron job call a second script who contains the main java application.