[【]日本語](README_ja.md)

DB Administrator

The SI-Toolkit for DB Administrator is a tool for DB migration, Java Persistence API (JPA) reverse engineering, and DB design document generation.

Key features include :

• Runs a SQL DDL/DML script to create a DB object using Flyway (schema versioning with https://flywaydb.org/)).
• Hibernate Tools (Use https://hibernate.org/tools/) to generate JPA entity java source files from the database schema.
• Schemaspy (Use http://schemaspy.org/) to generate database design documents containing table lists and ER diagrams from the actual DB metadata.

Software Requirements

DBAdministrator requires the following software :

• JDK11
• Docker or DBMS
• Maven

If Docker is not available for any reason, see xxx.

Introduction

To see how DBAdministrator works, run the sample project.

Sample Project

You can run the sample project with the following commands :

git clone https://github.com/sitoolkit/sit-dba.git
cd sit-dba

# windows
mvnw


# macOS
./mvnw

This maven build results in the following artifacts :

• DB schema
• JPA entities
• DB design documentation

To view them, run the following command :

# Windows
## Open phpPgAdmin
start http://localhost
## Open directory which JPA Entity java files are generated in.
start sit-dba-entity¥target¥generated-sources¥hibernate-tools¥
## Open DB design document
start target¥schemaspy¥index.html



# macOS
## Open phpPgAdmin
open http://localhost
## Open directory which JPA Entity java files are generated in.
open sit-dba-entity/target/generated-sources/hibernate-tools
## Open DB design document
open target/schemaspy/index.html

How it works

The build process for the above Maven commands includes four phases :

1. DB Migration
2. Reverse Engineering
3. Testing and Packaging
4. Generating DB Design Documents

#DB Migration

During the DB migration phase, SQL scripts are executed by Flyway. The script files are located in the sit-dba-migration/src/main/resources/db/migration directory, which is defined as the location of the Flyway's resources.

The parameters for connecting the DB are defined in the root pom. xml and passed to Flyway in the Maven build process.

  <properties>
    <db.name>postgres</db.name>
    <db.host>localhost</db.host>
    <db.port>5432</db.port>
    <db.jdbc.url>jdbc:postgresql://${db.host}:${db.port}/${db.name}?currentSchema=${db.schema}</db.jdbc.url>
    <db.username>postgres</db.username>
    <db.password>postgres</db.password>
    <db.schema>sit_db</db.schema>
  </properties>

These parameters are used in all phases.

Reverse Engineering

During the reverse engineering phase, Hibernate Tools generates java files that define JPA entity classes based on DB schema information (tables and relationships). The output directory is the additional java source directory for the sit-dba-entity/target/generated-sources/hibernate-tools, sit-dba-entity project.

Testing and Packaging

During the testing and packaging phase, Maven runs JUnit tests for sit-dba-sample-spring.

Generating DB Design Documents

In DB Design Document Generation, document files (html, css, javascript) are generated by Schemaspy and implemented as a docker-compose service.

Project Structure

There are three Maven projects in the DBAdministrator :

• sit-dba-migration
• sit-dba-entity
• sit-dba-sample-spring

sit-dba-migration

This project is for maintaining DDL/DML scripts to create DB schemas for applications. This project is packaged in an executable jar that supports Flyway CLI (https://flywaydb.org/documentation/commandline/)).

java -jar sit-db-migration-xxx-SNAPSHOT.jar --config=flyway.properties

The flyway. properties specification is [here] (defined in https://flywaydb.org/documentation/configfiles)).

sit-dba-entity

This project is packaged into a generic library jar that contains JPA entities and is used in the application java project.

sit-dba-sample-spring

This project uses Spring Boot and Spring Data Spring Boot and Spring Data JPA.

Use as a project

The Maven archetype allows you to use sit-dba as your project. The basic use of sit-dba is as follows :

1. Generate the project.
2. Modify the DB connection parameters.
3. Modify the DDL/DML script.
4. Modify the target table generated for the JPA entity.

Generate project

You can generate a new project using the following maven new project : (Replace xxx, yyy with groupId and artifactId.)

mvn archetype:generate -B \
    -DarchetypeGroupId=io.sitoolkit.dba \
    -DarchetypeArtifactId=sit-dba-archetype \
    -DarchetypeVersion=1.1.0 \
    -DgroupId=xxx \
    -DartifactId=yyy

Modifying DB Connection Parameters

After the project is generated, change the DB connection parameter in pom. xml of the root project to the development environment.

Modifying DDL/DML Scripts

Modify the DDL/DML script to create the DB schema in the yyy-migration/src/main/resources/db/migration directory. The name of the script file follows the Flyway rule .

You can run these script files with the following commands :

# Widows
mvnw -f yyy-migration -P migrate

# macOS
./mvnw -f yyy-migration -P migrate

Modifying the target tables generated for JPA entities

Yyy - Modify the entity / hibernate. reveng. xml file to add target tables to be generated for JPA entities.

<hibernate-reverse-engineering>

<! -- - Since this is a sample, delete it after modifying the DB schema -->
<table-filter match-name="person" package="io.sitoolkit.dba.domain.person"></table-filter>


<! -- - Add target tables and their entity packages -->
<table-filter match-name="YOUR TABLE" package="YOUR PACKAGE OF ENTITY CLASS"></table-filter>


</hibernate-reverse-engineering>

After modification, you can generate entities using the command.

# Windows
mvnw -f yyy-entity -P reveng

# macOS
./mvnw -f yyy-entity -P reveng