Module of the subsystem "DB" <FireBird>
| Module: | FireBird |
| Name: | DB FireBird |
| Type: | DB |
| Source: | bd_FireBird.so |
| Version: | 1.4 |
| Author: | Roman Savochenko, Maxim Lysenko (2009) |
| Description: | DB module. It provides the support for DB FireBird. |
| License: | GPL |
Introduction
Module <FireBird> gives the system OpenSCADA support of DB FireBird and InterBase. DB FireBird is a small, embedded database, with the functions of a network database that supports SQL-queries. DB FireBird is built on a commercial DBMS Interbase and distributed under a free license. To familiarize with the DBMS it is possible on the website 
http://www.firebirdsql.org. The module is based on the library with API of the manufacturer of DBMS on the language "C". The module 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 system OpenSCADA opening of DB is its registration for further using of it in the system. It also supported the operation of requesting the list of tables in the database.
DB FireBird is addressed by specifying the database file name, user name and password. In general, the address database is written in this way: "{file};{user};{pass}[;{conTm}]". Where:
- file — the full name of the database file;
- user — user of the database on behalf of which the access is made;
- pass — password for the user on behalf of which the access is made;
- conTm — connection timeout (s).
2. Operations over the table
The operations of opening and closing of the table with the possibility of creating a new table when you open and deleting the existing one at the closing, and also the operation of the requesting of the table's structure are supported.
3. Operations over the contents of the table
- scanning of the records of the table;
- request the values of these records;
- setting the values of these records;
- removing the records.
API of subsystem “DB” suppose the access to the contents of the table on the value of key(s) fields. Thus, the operation of request of the record implies the preset of key columns of the object TConfig, which will fulfill the request. Creating a new record(string) is the installation of the values of record, which does not exist.
The module allows you to dynamically change the structure of the database tables FireBird. Thus, in the event of a discrepancy of the table and the structure determined by record, the structure of the table will be set to the required structure of record. In the case of the request of the value of the record, and mismatching of the structures of record and the table there will be available only to the values of common elements of the record and table. The module does not track the order of the elements in the record and in the structure of the table!
The module is implement support multilanguage text variables. For fields with multilanguage text variable create the column of separated language in format <lang>#<FldID> (en#NAME). In this time the base column contain value for base language. The columns of separated languages created by needs, in time saving to DB and execution OpenSCADA in correspond language. If for work language value no present then will used value for base language.
The types of the elements of DB FireBird correspond to types of elements of system OpenSCADA in the following way:
| The types of fields of the system OpenSCADA | Types of fields of database FireBird |
| TFld::String | VARCHAR, BLOB SUBTYPE TEXT |
| TFld::Integer | INTEGER |
| TFld::Real | DOUBLE |
| TFld::Boolean | SMALLINT |
4. DB access
Access rights to the database are defined by the rights to DB file.
Briefly we will look at the initial configuration of the MySQL server to connect for it using by this module:
- Install FireBird DBMS server by the package or by build.
- Start DB server:
# Start classic server
$ service firebird start
# Start by superserver processing
$ service xinetd restart
- Setup need pasword for system user "sysdba":
$ gsec -user sysdba -pass masterkey -mo sysdba -pw 123456
- Connect to DB by the module help, enter DB address: "/var/tmp/test.fbd;sysdba;123456"
5. Productivity of the DBMS
Measurement of productivity of DB were carried out by the test "DB" of the module of system tests "SystemTests", by 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>.
| Operation | AMD Turion L625 1.6, 2G, FireBird [2.1.3 Local SuperServer; 100Base-TX; 2.5.2 100Base-TX SuperServer ForcedWrites=Off; ForcedWrites=On] | Intel(R) Core(TM) i3 CPU 1.33GHz, 3G, HDD [2.5.2 100Base-TX SuperServer ForcedWrites=Off; ForcedWrites=On] |
| Creation of the 1000 records (sec.): | 0.93; 1.15; 4.4; 5.2 | 3.68; 4.67 |
| Updating of the 1000 records (sec.): | 2.74; 2.94; 5.1; 5.2 | 3.88; 4.54 |
| Getting of the 1000 records (sec.): | 1.64; 2.22; 5.0; 27.8 | 3.69; 28.8 |
| Seeking of the 1000 records (sec.): | - | 5.36; 32 |
| Seeking in preload of the 1000 records (sec.): | - | 0.045; 0.065 |
| Deleting of the 1000 record (sec.): | 0.65; 1.04; 2.0; 2.4 | 1.69; 1.66 |
6. Notes
The DBMS FireBird has more architecture's limits and problems by the InterBase inheritance and from parent OS MS Windows which prevent it wide using for more tasks. Known limits and problems:
- The DBMS is slow comparing to MySQL or PostgreSQL, see to previous chapter. Especially for mode ForcedWrites On.
- The DBMS has fixed limit for plain SQL statement size to 65536 kB, which prevents for big content placing just SQL.
- Classic server on Linux for mode ForcedWrites Off can dead hang.
- Time limits control for the connections miss, which caused to long and uncontrolled waiting for client applications.
Links