...

For embedded Derby databases the JDBC driver of "org.apache.derby.jdbc.EmbeddedDriver" is used.

From Bitrepository 1.1 and onwards Derby databases will be automatically created and migrated at the start-up of the respective service, provided that the service is configured to use a Derby database.

Upon service start-up it is attempted to create a connection to the database, if this fails, a new database is created.

When a connection is obtained, the database schema is checked to see if it needs migration, followed by the needed migration (if needed).

Postgres

For Postgres databases the JDBC driver of "org.postgresql.Driver" is used.

In contrast to Derby databases, usage of Postgres databases will require manual work, as the databases are not automatically created nor migrated. Attempting to start a service on a database with an old schema will make the service log message that the database is of the wrong version and shutdown.

Using of Postgres for databases will require that person installing the services are acquainted with installation, setup and maintenance of Postgres. As such this is not documented here.

Database creation

The database schemas for Postgres is found in the respective service tar-ball under the 'sql/postgres' directory. The full database schema for the given release has the suffix 'Creation.sql'. It should be noted that certain services needs two databases (i.e. the integrity service), in which case there will be two SQL-files with the suffix 'Creation.sql'. 

    <IntegrityDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:integritydb</DatabaseURL>
      <Username>user</Username>
      <Password>pass</Password>
    </IntegrityDatabase>

Assuming the above configuration for the IntegrityDatabase the database schema can be loaded with the following command:

psql -d integritydb -f /path/to/extracted/service/tarball/sql/postgres/integrityDBCreation.sql

Depending on setup, a username, password, port number etc. may be needed.

Migration

Migration of databases is slightly more complex. Schemas are only provided for migrating from one version to the next (from N to N+1). So if it is needed to migrate from two version (from N to N+2), the migration has to be done in two steps (from N to N+1, and N+1 to N+2).

The migration schemas are named in the form:

<databasename>Update<from-version>to<to-version>.sql

I.e. auditTrailServiceDBUpdate2to3.sql or auditTrailServiceDBUpdate3to4.sql.

To check the version of the database the 'tableversions' table in the given database can be queried. For the Audittrail service database this could be done with:

psql -d auditservicedb -c "SELECT version FROM tableversions WHERE tablename='auditservicedb';"

From the provided version number the correct migration script can be applied.