Databases are needed for storing date in the various services. Currently the choice of database is between embedded Derby databases or PostgreSQL.

Connection to the databases is done through JDBC connections, as defined in the ReferenceSettings.xml configuration file for the respective service.

Common for both types of databases, is that the needed database schema (including migration) are included in the service distribution .tar, in the 'sql' directory.

See the following for a guide to migrate from Derby to PostgreSQL.

Derby

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

From BitRepository v1.1 and onward Derby databases will automatically be created and migrated at the startup of the respective service, provided that the service is configured to use a Derby database.

Upon service startup, it attempts 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 such a migration if needed.

PostgreSQL

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

In contrast to Derby databases, usage of PostgreSQL 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 PostgreSQL databases will require that the person installing the services are acquainted with installation, setup and maintenance of PostgreSQL.

Database creation

The database schema for PostgreSQL is found in the respective service .tar 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 (e.g. 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. The schema only provides for migrating from one version to the next (from N to N+1). So if migration from version N to N+2 is needed, the migration has to be done in two steps (from N to N+1, and from N+1 to N+2).

The migration schemas has the following naming convention

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

E.g. auditTrailServiceDBUpdate2to3.sql or auditTrailServiceDBUpdate3to4.sql.

To check the version of the database the 'tableversions' table in the given database can be queried. E.g. for the AuditTrail-Service database this could be done with the following command

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

From the provided version number the correct migration script can then be applied with

psql -d auditservicedb -f /path/to/extracted/service/tarball/sql/postgres/auditTrailServiceDBUpdate3to4.sql

The above example is for migrating the AuditTrail-Services database from version 3 to 4, as is mentioned in the name of the script.

Migrate from Derby to PostgreSQL

The following are rough notes on migrating from Derby to PostgreSQL for most databases in the reference code. The guide assumes that everything runs on Linux.

Migrating ReferencePillar or ChecksumPillar databases

In the following, assume that the Derby versions of both ChecksumDatabase and AuditTrailContributorDatabase needs to be migrated to a local PostgreSQL database.

Firstly, shutdown the pillar if it is running before continuing.

Setup

• Install PostgreSQL.
  • Ubuntu
  • RHEL, Fedora, Centos

Install databases in PostgreSQL, with a super-user named 'bitmag' (and with 'bitmag' password - used in settings)

• Run from linux console/terminal:
  • createuser -P -s -e bitmag
    • (write password (bitmag) twice)
  • createdb checksumdb
  • createdb auditdb
  • psql -d checksumdb -f /path/to/checksumDBCreation.sql
  • psql -d auditdb -f /path/to/auditContributorDBCreation.sql

Fix ReferenceSettings for the pillar

Replace existing AuditTrailContributerDatabase and ChecksumDatabase elements in PillarSettings in the ReferenceSettings with the following:

    <AuditTrailContributerDatabase>
     <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:auditdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailContributerDatabase>
    <ChecksumDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:checksumdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </ChecksumDatabase>  

Export from existing database

Use the derby tool 'ij' (assuming you are placed in the root installation folder for the pillar):

java -cp lib/derby-10.10.1.1.jar:lib/derbytools-10.10.1.1.jar:lib/derbyclient-10.10.1.1.jar org.apache.derby.tools.ij

Then run the following commands to extract the content of the checksum database:

• CONNECT 'jdbc:derby:/path/to/checksumdb'
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'CHECKSUMS', 'checksums', null, null, null);

And run the following command to extract the content of the AuditTrailContributor database:

• CONNECT 'jdbc:derby:/path/to/auditcontributerdb';
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'AUDITTRAIL', 'audittrails', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'FILE', 'files', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ACTOR', 'actors', null, null, null);

This should create the files: 'checksums', 'audittrails', 'files', and 'actors'.

Import the data to the PostgreSQL database

Run the 'psql' application on the konsole/terminale.

Log onto checksumdb and ingest data (and fix the automated sequences)

• \c checksumdb
• COPY checksums FROM '/path/to/checksums' with csv;
• SELECT SETVAL('checksums_guid_seq', (SELECT MAX(guid) FROM checksums));

Log onto auditdb and ingest data (and fix the automated sequences)

• \c auditdb
• COPY actor FROM '/path/to/actors' with csv;
• COPY file FROM '/path/to/files' with csv;
• COPY audittrail FROM '/path/to/audittrails' with csv;

• SELECT SETVAL('file_file_guid_seq', (SELECT MAX(file_guid) FROM file));

• SELECT SETVAL('actor_actor_guid_seq', (SELECT MAX(actor_guid) FROM actor));

• SELECT SETVAL('audittrail_sequence_number_seq', (SELECT MAX(sequence_number) FROM audittrail));

Migrating service databases

Assuming Derby versions of AuditTrailContributorDatabase for IntegrityService, AuditTrailServiceDatabase for AuditTrailService and AlarmDatabase for AlarmService should be migrated to a local PostgreSQL database.

We do not migrate the integrity database, since it automatically will be repopulated on first run of the CompleteIntegrityWorkflow, though we do create it.

Shut down the services before migrating, if they are running.

Setup

• Install PostgreSQL.
  • Ubuntu
  • RHEL, Fedora, Centos

Install databases in PostgreSQL, with a super-user named 'bitmag' (and with 'bitmag' password - used in settings)

• Run from linux console/terminal:
  • createuser -P -s -e bitmag
    • (write password (bitmag) twice)
  • createdb integritydb
  • psql -d integritydb -f /path/to/integrityDBCreation.sql
  • createdb auditdb
  • createdb integrity_auditdb
  • psql -d integrity_auditdb -f /path/to/auditContributorDBCreation.sql
  • createdb auditservicedb
  • psql -d auditservicedb -f /path/to/auditTrailServiceDBCreation.sql
  • createdb alarmdb
  • psql -d alarmdb -f /path/to/alarmServiceDBCreation.sql

Fix ReferenceSettings for IntegrityService

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

    ...
    <AuditTrailContributerDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:integrity_auditdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailContributerDatabase>

Fix ReferenceSettings for AuditTrailService

    <AuditTrailServiceDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:auditservicedb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailServiceDatabase>

Fix ReferenceSettings for AlarmService

    <AlarmServiceDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:alarmdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AlarmServiceDatabase>

Export/Import data for AlarmService

Run in 'ij'

• connect 'jdbc:derby:/path/to/alarmservicedb';
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'COMPONENT', 'alarm_component', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ALARM', 'alarm_alarm', null, null, null);

That should make 2 files: 'alarm_component' og 'alarm_alarm'.
Run in 'psql':

• \c alarmdb
• COPY component FROM '/path/to/alarm_component' with csv;
• COPY alarm FROM '/path/to/alarm_alarm' with csv;
• SELECT SETVAL('alarm_guid_seq', (SELECT MAX(guid) FROM alarm));
• SELECT SETVAL('component_component_guid_seq', (SELECT MAX(component_guid) FROM component));

Export/Import data for AuditTrailService

Run in 'ij'

• connect 'jdbc:derby:/path/to/auditservicedb';
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'ACTOR', 'audit_actor', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'COLLECTION', 'audit_collection', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'CONTRIBUTOR', 'audit_contributor', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'FILE', 'audit_file', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'PRESERVATION', 'audit_preservation', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'AUDITTRAIL', 'audit_audittrail', null, null, null);

That should make 6 files: 'audit_actor', 'audit_collection', 'audit_contributor', 'audit_file', 'audit_preservation' og 'audit_audittrail'.
Run in 'psql':

• \c auditservicedb
• COPY actor FROM '/path/to/audit_actor' with csv;
• COPY contributor FROM '/path/to/audit_contributor' with csv;
• COPY collection FROM '/path/to/audit_collection' with csv;
• COPY preservation FROM '/path/to/audit_preservation' with csv;
• COPY file FROM '/path/to/audit_file' with csv;
• COPY audittrail FROM '/path/to/audit_audittrail' with csv;

• SELECT SETVAL('preservation_preservation_key_seq', (SELECT MAX(preservation_key) FROM preservation));

• SELECT SETVAL('file_file_key_seq', (SELECT MAX(file_key) FROM file));

• SELECT SETVAL('contributor_contributor_key_seq', (SELECT MAX(contributor_key) FROM contributor));

• SELECT SETVAL('collection_collection_key_seq', (SELECT MAX(collection_key) FROM collection));

• SELECT SETVAL('audittrail_audit_key_seq', (SELECT MAX(audit_key) FROM audittrail));

• SELECT SETVAL('actor_actor_key_seq', (SELECT MAX(actor_key) FROM actor));

Export/Import data for AuditTrailContributor databasen for IntegrityService

Run in 'ij'

• CONNECT 'jdbc:derby:/path/to/auditcontributerdb';
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'AUDITTRAIL', 'integrity_audit_audittrails', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'FILE', 'integrity_audit_files', null, null, null);
• CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ACTOR', 'integrity_audit_actors', null, null, null);

That should make 3 files: 'integrity_audit_audittrails', 'integrity_audit_files' og 'integrity_audit_actors'.

Run in 'psql':

• \c integrity_auditdb
• COPY actor FROM '/path/to/integrity_audit_actors' with csv;
• COPY file FROM '/path/to/integrity_audit_files' with csv;
• COPY collection FROM '/path/to/integrity_audit_collection' with csv;

• SELECT SETVAL('file_file_guid_seq', (SELECT MAX(file_guid) FROM file));

• SELECT SETVAL('actor_actor_guid_seq', (SELECT MAX(actor_guid) FROM actor));

• SELECT SETVAL('audittrail_sequence_number_seq', (SELECT MAX(sequence_number) FROM audittrail));