Creating a PostgreSQL Database

Introduction

This document explains how to create a PostgreSQL database for BOC Management Office® products. You can create the database automatically using the tool Database Administration (adbinstws.exe) or create the database manually using pgAdmin or create the database from script.

Prerequisites

Before you can create a BOC Management Office® database with PostgreSQL, you need to have the following software:

• The latest 64 Bit version PostgreSQL server on the database server.
• pgAdmin, a database management software for PostgreSQL on the database server, if you prefer to create the database manually. When you install PostgreSQL server, pgAdmin is installed along with the other tools.
• The latest 64 Bit Unicode PostgreSQL ODBC driver which allows the BOC Management Office® product to access the PostgreSQL database on the application server.

Creating a Database Automatically

BOC Management Office® products require a dedicated database that can be created automatically using the tool Database Administration (adbinstws.exe). This tool can be launched from the BOC Management Office® product start menu by selecting Create new database, or from the installation directory by running adbinstws.exe.

To create a new database, proceed as follows:

1. Select Create new database, and then click Next.
2. From the Database system list, select PostgreSQL. Enter the name of the new database in the Database name box. In the Library File and Licence File boxes, select the library file and the licence file provided by your BOC account manager. The file extension of the library file is "*.axl" and the extension of the licence file "*.xxl". The licence also indicates which components are available in the tool and the number of users who can use the application in parallel.
3. Enter the name and password of the database administrator in the DB-administrator user name and DB-administrator password boxes.
4. Optionally, activate Use customary schema name/DB user if you want to use another database login than »ADOxx« to connect to the database and enter name and password of the user to be used in the fields DB-user name and DB-user password.
5. Make sure that the settings are correct, and then click Create. The database is created. From the Selected primary language list, select the governing language for creating models and objects. The primary language serves as fallback language if some text is not available in other languages. Confirm with OK.

The database is ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).

Creating a Database Manually

Wskazówka

The following complex procedure is only necessary if creating a database automatically is not possible in a specific scenario (e.g. because of specific organisational restrictions). Otherwise you can use the tool Database Administration (adbinstws.exe), which is installed together with every version of any BOC Management Office® product. It creates and configures BOC Management Office® databases automatically (see Creating a Database Automatically).

The following sections explain in detail, how a database for your BOC Management Office® product can be created and configured manually using pgAdmin. pgAdmin is a graphical administration tool for PostgreSQL. It is part of the standard setup of PostgreSQL server.

Start pgAdmin

1. Start pgAdmin and enter the master password you have set during the PostgreSQL server installation.

Create New Role »ADOxx«

The login to a BOC Management Office® database requires a role »ADOxx«.

1. In the Browser pane, under Servers\<my-server-name>, right-click Login/Group Roles, point to Create, and then click Login/Group Role.
2. In the Name box, enter the name »ADOxx«.
3. Go to the Definition tab. In the Password box, enter the password »r0KaQIFA]cPd2Ave«.
4. Go to the Privileges tab. Turn on Can login?, and then click Save.

Create New Role »ADOXX_BOOT«

The role »ADOXX_BOOT« is needed to change the password of the role »ADOxx«.

1. In the Browser pane, under Servers\<my-server-name>, right-click Login/Group Roles, point to Create, and then click Login/Group Role.
2. In the Name box, enter the name »ADOXX_BOOT«.
3. Go to the Definition tab. In the Password box, enter the password »iCfCK!lHP8S1L]Ry«.
4. Go to the Privileges tab. Turn on Can login?, and then click Save.

Create New Database

1. In the Browser pane, under Servers\<my-server-name>, right-click Databases, point to Create, and then click Database.
2. In the Database box, enter the name of the new database. From the Owner list, select the role »ADOxx«.
3. Go to the Security tab. In the Privileges pane, click + Add row twice to add two rows. Add »ADOxx« and »ADOXX_BOOT« as grantees and assign them both Connect privileges to the database. Then, click Save.

The new database will be created.

Create New Schema »ADOxx«

Creating tables in a BOC Management Office® database requires a database schema »ADOxx«.

1. In the Browser pane, under Servers\<my-server-name>\Databases, right-click your database, point to Create, and then click Schema.
2. In the Name box, enter the name »ADOxx«.
3. Go to the Security tab. In the Privileges pane, click + Add row twice to add two rows. Add »ADOxx« and »ADOXX_BOOT« as grantees and assign them both Usage privileges to the schema.
4. Go to the Default privileges tab. In the Tables tab, click + Add row to add a row. Add »ADOxx« as a grantee and assign them the Insert, Select, Update and the Delete privileges for tables. Then, click Save.

The new schema »ADOxx« will be created.

Create ODBC Data Source

Create an ODBC data source for PostgreSQL server on the server where pgAdmin is installed, as well as on each computer with a BOC Management Office® product installation.

The ODBC data source must be created using the 64-bit version of the odbcad32.exe file located in the <WINDIR>\system32 folder. <WINDIR> is the path to your Windows installation directory.

1. Go to the System DSN tab and click Add.
2. Select the PostgreSQL Unicode(x64) driver and click Finish.
3. Enter the following data: Data Source: A descriptive name for the ODBC data source. Can be the same as the database name. Database: The name of the database. Server: The host name or IP address of the database server. Port: The port on which the PostgreSQL services are running. The default value is «5432».
4. In the Options area, click the Datasource button and go to Page 2. Clear the LF <-> CR/LF conversion check box and then click OK.
5. Click Save to finish creating the ODBC data source.

Create Tables and Initialise PostgreSQL Database

Fill the new database using the tool Database Administration (adbinstws.exe). You can start this tool via the BOC Management Office® product start menu via the entry Create new database or in the installation directory using the file adbinstws.exe.

1. Select Create new database, and then click Next.

2. From the Database system list, select PostgreSQL. In the Database name box, enter the name of the ODBC data source you created. In the Library File and Licence File boxes, select the library file and the licence file provided by your BOC account manager. Clear the Create new database check box. Make sure that the Create tables check box is selected. Enter the name and password of the database administrator in the fields DB-administrator user name and DB-administrator password, and then click Create.

The database is ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).

Creating a database from Script

As an alternative to creating the database automatically or manually using PgAdmin, you can create a database from script.

The following scripts will be used:

• prepareDB.sql
• postgresql.sql

You can find these scripts in the installation package of your BOC Management Office® product in the folder 02 Rich Client\dbinfo.

1. Launch psql and enter the required information such as Server, Database, Port, Username and Password. Press Enter to use the default values in the square brackets. You can go with the default database and the database superuser »postgres«.
2. First, the roles »ADOxx« and »ADOXX_BOOT«, the new database and the schema »ADOxx« need to be created with the script prepareDB.sql. Open prepareDB.sql in a text editor and set the following parameter: /set .vDBName 'adoxxdb': Replace adoxxdb with the name of your new database. To run the script in psql, type \i <pathname>/prepareDB.sql.
3. Next, the tables need to prepared with the script postgresql.sql. To run the script in psql, type \i <pathname>/postgresql.sql.
4. Create an ODBC data source on every computer with an installation of a BOC Management Office® product (see Create ODBC Data Source).
5. Finally, the new database needs to be initialised. Launch the Windows Command Prompt and navigate to the BOC Management Office® product installation directory. Adapt the following command with your actual values and execute it: amain.exe -ws dbiws -dt PostgreSQL -db <my-database-name> -dbadmin postgres -dbadminpw <my-database-admin-password> -dbimode initonly -lib "<pathname>/library.axl" -lic "<pathname>/license.xxl".

The database is initialised and is then ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).

Change Password of role »ADOxx« (Optional)

The role »ADOxx« is required for login to an ADOxx database. For security reasons, the password of the role »ADOxx« should be changed after the initialisation of the database.

To change the password of the role »ADOxx«, the role »ADOXX_BOOT« is required. The password can be changed using the tool Database Administration (adbinstws.exe).

To change the password of the role »ADOxx«, proceed as follows:

1. Select Change database user password for an existing database, and then click Next.
2. Enter the name and password of the role »ADOxx« or a database administrator in the DB-user name and DB-user password boxes. Enter the name of the database in the Database name box. From the Database system list, select PostgreSQL, and then click Next.
3. Enter the new password in the New DB-user password and New DB-user password (confirmation), and then click Change.

The password of the role »ADOxx« is changed.