OpenHub database schema is managed by Flyway.
It was selected because it is a simple tool that quite successfully addresses the issue of evolving DB schema. Typical problem of evolving applications with usage of relational databases. Flyway works on granularity of SQL files, that are automatically applied based on current schema on running database. Scripts are numbered by convention.
In OpenHub, these scripts are added for both currently supported DB engines - PostgreSQL & H2. By default, DB migration using flyway is enabled. If developer checks out the project, schema is created during application startup by Flyway. If any changes are introduced in form of new SQL script, flyway will apply them - scripts can be DDL or data migrations (if they make sense).
Prerequisite for successful flyway run with PostgreSQL:
- there is user/role (e.g. openhubusr)
- there is database (e.g. openhubdb)
- there is schema openhub
See db/migration/postgresql/V1_0_0__schema_init.sql init script for more details.
If you need to use not concurrent scheduled jobs with Quartz then you must create database structure manually from GitHub scripts.
Enabling/disabling flyway in application.properties
flyway.enabled = true/false
For more configuration properties, see application.properties in OpenHub source.
Manual db schema creation/update
Flyway does mainly simplify development. On production deployment you may want to manually create or update schema - for example with more priviliged user than is used running the application. Some options:
Flyway provides command-line tool that can be used to perform migration. It does setup metadata table as well, so after migration using this tool, even with enabled flyway it should be perfectly valid like done automatically. See https://flywaydb.org/documentation/commandline/ for more info.
Manual SQL scripts
You can sure just disable flyway and just run OpenHub SQL scripts. Downside would be missing metadata table, so you would have to keep track of applied/not applied scripts by your self. Combined approach is possible: apply all scripts, then setup flyway with baseline (flyway.baseline-on-migrate = true & flyway.baseline-version = XXX) to create metadata table with given version. If any new scripts are added, flyway will migrate only on top of baseline version, or fail trying.
There are also tables and functions for archivation of "production" tables for PostgreSQL. Archivation tables have prefix "archive_", e.g. archive_external_call
Function for archivation is called archive_records(integer) where input is number of months after that the message will be archived.