Modules/DBGate

Module	Name	Version	License	Source	Languages	Platforms	Type	Author	Description
DBGate	DB gate Features: SQL, LIST, STRUCT, GET, SEEK, PRELOAD, SET, DEL, FIX, TR, ERR	1.1	GPL2	bd_DBGate.so	en,uk	x86,x86_64,ARM	DB	Roman Savochenko	BD module. Allows to locate databases of the remote OpenSCADA stations to local ones. To Do: - implementation the local cache for the function fieldSeek() by the deep seeking to the XML-context on the remote part.

Main function of this module is the data reflection of the subsystem "Databases" of the remote OpenSCADA stations on the local ones. In its work, the module uses the OpenSCADA self protocol and service functions of the subsystem "Databases". This module can be useful:

• in multiple-network accessing to the remote station DB, built on the local ones' basis like to SQLite, such transforming their to the network ones;
• in proxy-access to the remote station DB, built on the network DBMS basis, through the OpenSCADA control interface, what can be also secured, using SSL;
• in spreading between network stations the complex user area functions with DAQ and UI parts linked on DB, like to Prescriptions.

The module realizes following functions:

• Enabling the remote DB at enabling the local DB object.
• Getting the available tables list of the remote DB.
• Adding with creating the new requested table on the remote DB.
• Complete removing the requested table on the remote DB.
• Transmitting the complete SQL-requests to the remote DB.
• Transmitting the OpenSCADA DB requests to the remote DB for: fieldStruct(), fieldSeek(), fieldGet(), fieldSet() and fieldDel().

1 Operations over the database

The operations of opening and closing of the database is supported only for the local DB object and without possibility of creating new database and deleting the existing ones at the close, on the remote station. In terms of the subsystem "DB" of OpenSCADA, opening of DB is its registration-connecting to the remote DB, for further using of the program. It also supports the operation of requesting the list of tables in the remote database.

The DB-object configuration page also reflects mostly properties of the remote DB, besides:

• "State > Enabled" — own state and command of dynamic enabling the object.
• "Configuration > Identifier" — own object identifier.
• "Configuration > {Name,Description}" — own values for not empty or the remote DB values.
• "Configuration > Address" — own address of a DB, further about its format.
• "Configuration > To Enable" — own state of it enabling at the program start.

The DBGate database is addressed by specifying-selecting the DB name in the format "{RemoteStation}.{DBModule}.{DB}", where:

• RemoteStation — remote OpenSCADA station, registered in the table of the OpenSCADA stations;
• DBModule — DB-module identifier of the remote station;
• DB — database identifier of the remote station.

2 Operations over the table

Supports transmitting to the remote station for operations of 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

Transmitting to DB of the remote station for the table operations:

• 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 table dynamically structure change depends on the linked remote DB type only.

The module implements the mechanism of support the multilingual text variables indirectly in the core functions of working with the configuration file: TBDS::dataSeek(), TBDS::dataGet() and TBDS::dataSet().

4 Permissions

Access rights to the end side database are determined by the rights specific to the remote DB type and by the rights of the remote station user accessing the subsystem DB in whole, for the rights described in the follow table:

Function/operation/service	Rights relatively to "root:BD:{other}"
Enabling DB	W:W:-
Available tables list	R:R:R
Creating table	W:W:-
Deleting table	W:W:-
SQL request (call:/serv/SQL)	W:W:-
Field structure (call:/serv/fieldStruct)	R:R:R
Field seek (call:/serv/fieldSeek)	R:R:R
Field get (call:/serv/fieldGet)	R:R:R
Field set (call:/serv/fieldSet)	W:W:-
Field delete (call:/serv/fieldDel)	W:W:-

5 Productivity of the DB

Measurement of the performance of the database, more precisely the gate, is carried out by testing the "database" of the module "SystemTests", by performing operations on records by the structure "name char (20), descr char (50), val double (10.2), id int (7), stat bool, reqKey string, reqVal string" twice, first for the remote environment DB, and then to the same DB, but through the gateway — this module.

Operations for 1000 records, seconds	AMD A8-6500 <Ethernet> AMD Phenom II X4 [RAID1] <> SQLite	AMD A8-6500 <Ethernet> AMD Phenom II X4 [RAID1] <> MySQL
Creating:	2.36 > 0.35	3.39 > 1.35
Updating:	2.31 > 0.31	2.97 > 0.94
Getting:	2.45 > 0.2	2.67 > 0.49
Seeking:	2.22 > 0.13	3.59 > 1.48
Seeking in preload*:	1.93 > 0.02	1.88 > 0.04
Deleting:	1.85 > 0.051	2.14 > 0.27

^* — this operation has no effect because preloading-caching is performed only on the remote station side.