Module | Name | Version | License | Source | Languages | Platforms | Type | Author | Description |
---|---|---|---|---|---|---|---|---|---|
PostgreSQL | DB PostgreSQL
|
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.
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:
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
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.
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 |
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:
# DB initialization $ service postgresql initdb # DB start $ service postgresql start
local all all trust host all all 127.0.0.1/32 trust
$ service postgresql restart
$ psql -U postgres -d template1 -c "ALTER USER postgres PASSWORD '123456'"
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 |
Modules/PostgreSQL/en - GFDL | December 2024 | OpenSCADA 1+r3000 |