Move to a production grade DB

[gokulprathin8]

What is this PR about?

Move to a production-grade database like MySQL or PostgreSQL. This change will increase trust in Geoweaver among the community and enhance its reliability.

Preparation

1. Assess Database Schema Compatibility:
   • Review the existing H2 database schema, including tables, indexes, and constraints.
   • Identify any H2-specific features or syntax that might not be directly compatible with MySQL or PostgreSQL.
2. Choose the Target Database:
   • Decide whether to migrate to MySQL or PostgreSQL based on your application's specific needs, considering factors like scalability, performance, and feature set.
3. Setup the Target Database:
   • Install MySQL or PostgreSQL on a development machine or server.
   • Create a new database that will be used by Geoweaver.

Configuration Changes

1. Database Driver Dependency:

   • Update your pom.xml (if using Maven) or build.gradle (if using Gradle) file to include the MySQL or PostgreSQL JDBC driver dependency and remove the H2 database dependency.

   For MySQL:

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>LATEST_VERSION</version>
   </dependency>
   
   

   For PostgreSQL:

   <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
       <version>LATEST_VERSION</version>
   </dependency>
   
   

2. Update Application Properties:

   • Modify the application.properties or application.yml file in your Spring Boot project to reflect the new database connection settings.

   For MySQL:

   spring.datasource.url=jdbc:mysql://localhost:3306/geoweaver_db?serverTimezone=UTC
   spring.datasource.username=your_username
   spring.datasource.password=your_password
   spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
   
   

   For PostgreSQL:

   spring.datasource.url=jdbc:postgresql://localhost:5432/geoweaver_db
   spring.datasource.username=your_username
   spring.datasource.password=your_password
   spring.datasource.driver-class-name=org.postgresql.Driver
   
   

3. Hibernate & JPA Configuration:

   • Ensure that Hibernate and JPA configurations are updated to match the dialect of the chosen database.

   For MySQL:

   spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
   
   

   For PostgreSQL:

   spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
   

Check for External Configuration File:

At application startup, add logic to check if $HOME_DIR/geoweaver/config.properties exists. This can be achieved using Spring's Environment class or Java's File class. Load External Configuration if Present:

If the config.properties file exists, use Spring's @PropertySource annotation or manually load the properties in a configuration class to override the default database settings.

Fallback to Default Configuration:

If the external configuration file does not exist, the application should automatically use the default application.properties or application.yml configuration that is bundled with the application.

Helpful Links

https://www.baeldung.com/spring-boot-hibernate

Expected Output

Working Production grade Database support for MySQL or PostgreSQL