...
When creating the application with name Application over PostgreSQL, the user must select the existing database service, which ensures running of database, and password to its main user with name postgres. Then, D2MC will create the databases aplikacia_syscfg and aplikacia_logfile in this database service and save them into separate tablespaces (ts_aplikacia_syscfg, ts_aplikacia_logfile), placed in subdirectories syscfg and logfile in application directory of D2000 System. It also creates DSN records Aplikacia.SysCfg and Aplikacia.LogFile for these databases. The created user dba is the owner of these databases and has the access to them.
...
When creating the archive for Application over PostgreSQL, the user must select the existing database service, which ensures running of database, and password to its main user with name postgres. Then, D2MC will create the database aplikacia_archiv in this database service and save them into separate tablespaces (ts_aplikacia_archiv), placed in subdirectory archiv in application directory of D2000 System. It also creates DSN record Aplikacia.Archiv for this database. The created user dba is the owner of this database and has the access to it.
...
Each depository, or depository segment is implemented as the separate database. The names are derived from the application name (<application>_TS_#ID#, wher #ID# is depository number, or <application>_TS_#ID#_#SEG#, where #SEG# is the number of depository segment).
...
In the event that D2000 System should work with the external application database, for its integration user must create a system DSN record in ODBC Administrator on the computer with running D2000 DBManager.
Warning: Both 32 and 64 bit ODBC Administrators exist in 64-bit OS Windows. DSN record must be created in the same administrator as D2000 DBManager has been created!32/64 ODBC Administrator depending on version of D2000 DBManager (as 32-bit applications cannot use 64-bit DSN and vice versa).
In case of selecting more types of DSN records, user should select the one which contain Unicode in the name.
The configuration of DSN record for PostgreSQL should be done according to these steps:
...
- Write, correctly, these parameters in the main window:
Database – name of existing database.
Server – address of server, which PostgreSQL service runs on (or localhost in case of the same computer),
Port – port number, which PostgreSQL service listens on.
After entering the name and password and clicking the Test, D2000 should connect to database successfully. If the connection failed, you must check the entered data and settings of PostgreSQL service, which database runs on (see the section Connection to PostgreSQL database running on dedicated machine). Warning! For security reasons, do not keep the name and password filled in the final state!
For proper functioning with D2000 System, it is necessary to change other default parameters.
- In Datasource/Page1 tab, set the following parameters:
CacheSize can be optionalbe set at your discretion. - In Datasource/Page2 tab, keep these settings:
The highlighted parameters must be set as follows:
Updateable Cursors – the ability to use cursors to writes (enables updateable cursor emulation in the driver)
bytea as LO - enables support for writing Large Objects (blobs) into bytea columns
Server side prepare - compiled cursors (e.g. for parameterized queries) on the server side can improve performance. If possible, we recommend that you enable this option.
Level of rollback on the picture above! errors - if transaction operations are used and there may be an error during command execution (e.g. INSERT fails because of a unique index violation), then if the parameter is set to Statement, only the last command is rolled back. If the parameter is set to Transaction, the entire transaction is rolled back. On the other hand, the Statement setting makes "microcommits" (savepoints) after each operation, which both reduces performance and consumes transactional IDs, which in turn leads to increased vacuuming activity of PostgreSQL database.
This DSN record This DNS record can be used for connection to external PostgreSQL database in D2000 System.
...
In the event that D2000 System should connect to the remote server, it is necessary to configure DNS record DSN record in ODBC administrator on client according to the previous instructions with the only difference - into Server parameter enter the name or IP address of server, which PostgreSQL database runs on.
...
On dedicated machine with the running database, which you want to connect to, you must properly define the rights for connection. Default settings of PostgreSQL enables connection to the running database service only from the computer on which it is started. When necessary to connect from the network, you must change this setting by the configuration file pg_hba.conf, which is placed in the selected directory of database service.
...
It contains this default settings:
This setting enables connection to database server only for the user from the same computer (127.0.0.1 represents the localhost).
...
If the user wants to connect from any computer, the configuration file must contain this line:
This line enables to connect all users to all databases of running service from all addresses. However, the password is required (md5 method).
...
The setting, mentioned above, has the illustrative character and it is not recommended for use on the production systems because it can represent a security risk when using the weak passwords. The configuration file pg_hba.conf enables to limit the assigning the rights for connection to database (based on the user or database), which can be found for example on this link http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html.
...
The identifiers of tables and columns in PostgreSQL, which are not in quotation marks, are not case sensitive. It means, the column can be created in this way:
create table t (id integer)
Then, these queries can be created:
select id from t
select Id from t
select ID from t
select "id" from t
All forms of querying of the given column are functional.
...
It means, when creating this column:
create table t ("Id" integer)
it is possible to refer to it only in the same format:
select "Id" from t
Any other format of this record will not work.
...