Modules/PostgreSQL

---

Module	Name	Version	License	Source	Languages	Platforms	Type	Author	Description
PostgreSQL	DB PostgreSQL Features: SQL, LIST, STRUCT, GET, SEEK, PRELOAD, SET, DEL, FIX, TR, ERR	3.1	GPL2	bd_PostgreSQL.so	en,uk,ru,de	x86,x86_64,ARM	DB	Roman Savochenko   Maxim Lysenko (2010-2011)	DB module. Provides support of the DBMS PostgreSQL.

The module provides OpenSCADA with support the DBMS PostgreSQL, which is a powerful relational and multiplatform DBMS, available under a free license and developed by the PostgreSQL community. The module is based on the C language API library of the DBMS PostgreSQL developers and allows you to perform operations over databases, tables and contents of tables.

1 Operations over the database

The operations of opening and closing of the database is supported, with the possibility of creating a new database when you open and delete existing at the close. In terms of the subsystem "DB" of OpenSCADA, opening of DB is its registration for further using of the program. It also supports the operation of requesting the list of tables in the database.

DBMS PostgreSQL addresses by a string of the following type: {host};{hostaddr};{user};{pass};{db};{port}[;{connect_timeout}]. Where:

• host — hostname on which the DBMS server PostgreSQL works. If this begins with a slash ('/'), it specifies Unix socket rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored;
• hostaddr — numeric IP address of the host for connecting, which should be in the standard IPv4 address format, e.g., 172.28.40.9; if IPv6 is supported, you can also use those addresses; TCP/IP communication is always used when a nonempty string is specified for this parameter;
• user — user name of the DB;
• pass — password of the user for accessing the DB;
• bd — name of the DB;
• port — port, which listening by the DBMS server (default is 5432);
• connect_timeout — connection timeout, in seconds. Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds.

In the case of local access to the DBMS in the same host, the address string should be as follows: ;;user;password;OpenSCADA;;10

In the case of remote access to the DBMS you must use the hostname and port of the DBMS server, for example: server.nm.org;;user;password;OpenSCADA;;10

2 Operations over the table

Supports opening, closing tables, with the possibility of creating a new table when opening and removing an existing one when closed, as well as querying the structure of the table.

3 Operations over the table content

• scanning of the table records;
• requesting the values of specified records;
• setting the values of specified records;
• removing the records.

API of the subsystem "DB" suppose the access to the contents of the table on the value of key(s) fields. Thus, the operation of requesting of the record implies the preset of key columns of the object TConfig, which will do the request. Creating a new record (row) is performed by setting the record values that are missing.

The module allows you to dynamically change the structure of the database tables PostgreSQL. So, in the case of inconsistency between the structure of the table and the structure of the record being set, the structure of the table will be brought to the desired structure of the record. In the case of requesting the record values and discrepancies between record structures and tables, only the values of the general entries of the record and the table will be obtained. The module does not track the order of the elements in the record and in the structure of the table!

The module implements the mechanism of supporting multilingual text variables. For fields with a multilingual text variable, columns of individual languages are created in the format " {lang}#{FldID}" (en#NAME). In this case, the base column contains values for the base language. Columns of individual languages are created when needed when saving to a DB and when execution OpenSCADA in the corresponding locale. In the absence of values for a particular language, the value for the base language will be used.

Types of PostgreSQL database elements are corresponding with the types of OpenSCADA elements in the following way:

Types of OpenSCADA fields	Types of fields of DB PostgreSQL
TFld::String	character(n), character varying(n), text
TFld::Integer	integer, bigint, timestamp with time zone [for the fields with the flag TFld::DateTimeDec]
TFld::Real	double precision
TFld::Boolean	smallint

4 Access rights

The PostgreSQL DBMS contains a mechanism for sharing access, which consists of the privileges specified by the user database. The table below lists the necessary privileges for full-time work.

Operation	SQL-commands
Creation of the DB	CREATEDB
Creation of the connection	LOGIN

To access the database, the right must be allowed directly for the specified database, and in the case of the creation of a database, it is also necessary to allow access to the system database "template1", which is used to connect at the stage of creating a new database.

Briefly we will look at the initial configuration of the MySQL server to connect for it using by this module:

• Installing the PostgreSQL DBMS server by the package or building.
• Primary server initialization:

# DB  initialization
$ service postgresql initdb
# DB start
$ service postgresql start

• Enabling the trusted access from a local or a required subnet by editing the file /var/lib/pgsql/data/pg_hba.conf for the installation of "trust":

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

• Restarting the server after editing the access:

$ service postgresql restart

• Setting the password for the system user "postgres":

$ psql -U postgres -d template1 -c "ALTER USER postgres PASSWORD '123456'"

• Connecting the DBMS server using this module by entering the database address: "localhost;;postgres;123456;test"

5 Productivity of the DBMS

Measurement of the DBMS productivity were carried out by the test "DB" of the tests module "SystemTests", performing operations over the records of the structure: "name char (20), descr char (50), val double (10.2), id int (7), stat bool, reqKey string, reqVal string".

Operations for 1000 records, seconds	Nokia N800, PostgreSQL 8.3 (remote)	AMD Turion L625 1.6, 2G, PostgreSQL 9.1 [local; 100Base-TX]	Intel(R) Core(TM) i3 CPU 1.33GHz, 3G, HDD [100Base-TX]
Creating:	5	1.08; 1.6; 3.8	2.07
Updating:	4.8	1.12; 1.6; 3.9	2.35
Getting:	2.96	0.64; 1.2; 2.1	1.26
Seeking:	-	-	7.9
Seeking in preload:	-	-	0.038
Deleting:	1.73	0.39; 0.6; 1.6	0.94