Database Configuration Architecture

OpenPetra, obviously, requires a database, and this page gives you an overview of how that database – or those databases – are configured and maintained. Once everything is set up, your database configuration is most easily maintained through the OpenPetra Developers' Assistant. This uses the NAnt build tool, so understanding how the database is built is understanding how the NAnt configuration fits together.

​​​​OpenPetra.build.config

NAnt looks for buildfiles named *.build. These contain the list of targets, properties and instructions to perform a build. The top directory of your OpenPetra branch contains one OpenPetra.build file, making it the default buildfile. It (and the files it includes from inc\nant) contain defaults for many configuration parameters, but obviously you need to provide local settings too. OpenPetra.build is programmed to read the user-created configuration file OpenPetra.build.config to provide these settings. NAnt uses these in its own commands and to build the other config files used by OpenPetra.

<?xml version="1.0"?>
<project name="OpenPetra-userconfig">
    <property name="DBMS.Type" value="postgresql" />        <!-- This is the default anyway -->
    <property name="PostgreSQL.Version" value="9.3" />      <!-- NAnt needs this to locate the PostgreSQL installation e.g.
                                                                 C:\Program Files\PostgreSQL\9.3\bin -->
    <property name="DBMS.DBName" value="petra" />           <!-- You can have multiple databases configures; this controls which
                                                                 is current. The default is "openpetra" -->
    <property name="DBMS.Password" value="password" />      <!-- The password for the default database owner. You can override
                                                                 DBMS.UserName too, but it's not recommended. -->
    <property name="Server.DebugLevel" value="0"/>          <!-- If you want to increase your debuglevel, this is where to do it -->
    <property name="NUnitVersion" value="NUnit 2.6.1" />    <!-- The version of NUnit for running unit tests. Like PostgreSQL, NUnit 
                                                                 includes its version in the directory path so you can have multiple
                                                                 versions  installed -->
    <!-- To send email from OpenPetra, you can use a 3rd party email provider or locally-configured server: -->
    <property name="smtp.host" value="smtp.example.org" />
    <property name="smtp.port" value="25" />
    <property name="smtp.enablessl" value="true" />
    <property name="smtp.authenticationtype" value="config" />
    <property name="smtp.username" value="YourSmtpUser" />
    <property name="smtp.password" value="YourSmtpPassword" />
</project>

My Files

Not everything is set in OpenPetra.build.config. The client and server config files are built from templates in the inc\template\etc directory. You can override these by creating your own versions named *.config.my. Of particular interest to Developers is inc\template\etc\Server-postgresql.config._my. Note the underscore in the extension. The default Server-postgresql.config contains settings suitable for a production setup, but this sample .my file contains the settings suitable for development: such as increased timeout values to prevent your client being disconnected while you're debugging the server.

Copy Server-postgresql.config._my to Server-postgresql.config.my and this will become the template for building your configuration.​

​​​​​​Once you have created OpenPetra.build.config and any .my files, use ​nant initConfigFiles to write these values to the OpenPetra configuration files:

• etc\Client.config
• etc\Server.config
• etc\ServerAdmin.config
• etc\TestClient.config
• etc\TestServer.config

These config files are rebuilt automatically every time you start the server or client, so the only files you should ever change yourself are OpenPetra.build.config and your .my files.

OpenPetra Developers' Assistant helpfully copies your .my template files when you use it to create a new branch.​

Database Naming

You can call your own development database whatever you like. Good choices are petra or openpetra. But there is a special name, nantTest, recognised by the Developers' Assistant. When you run unit tests, the database your OpenPetra.build.config points to will be overwritten with test data. Helpfully, you get a warning about this, so that you can switch the configuration from your development database to your test one. But if you call your unit test database nantTest, you won't get interrupted by an unnecessary warning.

​Database Owner

One step that NAnt doesn't do for you is create the database user who owns the databases. To do that, you need to log in to PostgreSQL as the master user, postgres, and there isn't a NAnt property to store that password. So, use pgAdmin to create a new Login Role petraserver with Create Database access, and give it a password. Use that password as the value for DBMS.Password in OpenPetra.build.config.

​(Re)Create the Database

Now the user is configured, you can create the database. Slightly confusingly, nant createDatabaseUser issues the create database command for the configured database – or you can do it through pgAdmin, giving it the owner petraserver. Next create the tables with nant recreateDatabase, which is what the Developers' Assistant uses to re-create the complete database. This gives you a complete, empty, OpenPetra database.

​Reset the Database Content

An empty database isn't much good, so nant resetDatabase will ​populate it with some basic test data: SYSADMIN and DEMO users, and a few Partners.

Populating a Development Database

The best way to populate a development database is to use pgAdmin to make a backup of the data you want, and restore it into your own database. On the Restore database options page, the Restore Options #2 tab contains the useful Clean before restore option which drops the existing tables before trying to load them from the backup, or even Include CREATE DATABASE statement​ which drops the whole database first.