Creating a PostgreSQL Database
Introduction
This document explains how to create a PostgreSQL database for ADOxx 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 an ADOxx 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 ADOxx product to access the PostgreSQL database on the application server.
Creating a Database Automatically
ADOxx products require a dedicated database that can be created automatically using the tool Database Administration (adbinstws.exe
). This tool can be launched from the ADOxx 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. 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 main 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
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 ADOxx product. It creates and configures ADOxx databases automatically (see Creating a Database Automatically).
The following sections explain in detail, how a database for your ADOxx 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 an ADOxx 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 an ADOxx 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 an ADOxx 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:
| |
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 Test to test the newly created data source. If the test is successful, the data source can be used. 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 ADOxx 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. 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 ADOxx 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:
| |
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 an ADOxx product (see Create ODBC Data Source). | |
5. Finally, the new database needs to be initialised. Launch the Windows Command Prompt and navigate to the ADOxx 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.