Modules/FireBird

---

Module	Name	Version	License	Source	Languages	Platforms	Type	Author	Description
FireBird	DB FireBird Features: SQL, LIST, STRUCT, GET, SEEK, PRELOAD, SET, DEL, FIX, TR	3.0	GPL2	bd_FireBird.so	en,uk,ru,de	x86,x86_64,ARM	DB	Roman Savochenko   Maxim Lysenko, 2009 — the page initial translation	DB module. Provides support of the DBMS FireBird.

The module provides OpenSCADA with support for the FireBird DBMS, and InterBase, which is a small and embedded DBMS with network functions and support for SQL queries. FireBird is built on the basis of the commercial InterBase DBMS and is distributed under a free license. You can familiarize yourself with the DBMS at http://www.firebirdsql.org. The module is based on the database API's library in the language "C" and it allows you to perform actions on databases, tables and table contents.

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.

The FireBird DBMS is addressed by specifying the database file name, user name and password. In general, address of the database is written in this way: {file};{user};{pass}[;{conTm}]. Where:

• file — 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, seconds.

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 FireBird. 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, in addition to this, the operation of changing the column type is not safe and the data in the changing columns will be lost!

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 elements of the FireBird DBMS correspond to types of elements of OpenSCADA in the following way:

Types of OpenSCADA fields	Types of fields of DBMS 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 the DB file.

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

• Install FireBird DBMS by the package or by build.
• Start DBMS:

  # Start the classic server
  $ service firebird start
  # Start by the superserver processing
  $ service xinetd restart

• Setup need pasword for system user "sysdba":

  $ gsec -user sysdba -pass masterkey -mo sysdba -pw 123456

• Connect to the DBMS by the module help, enter DB address: "/var/tmp/test.fbd;sysdba;123456"

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	FireBird 2.5 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]	FireBird 3 Intel(R) Core(TM) i7 CPU 2.6GHz, 12G, FastSSD, Local network
Creating:	[0.93; 1.15; 4.4; 5.2]; [3.68; 4.67]	0.5
Updating:	[2.74; 2.94; 5.1; 5.2]; [3.88; 4.54]	0.47
Getting:	[1.64; 2.22; 5.0; 27.8]; [3.69; 28.8]	2.95
Seeking:	- ; [5.36; 32]	3.3
Seeking in preload:	- ; [0.045; 0.065]	0.047
Deleting:	[0.65; 1.04; 2.0; 2.4]; [1.69; 1.66]	0.17

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 the previous chapter. Especially for the mode ForcedWrites On(sync).
• 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 disabling the mode ForcedWrites, can dead hang.
• Time limits control for the connections is missed, which caused to long and uncontrolled waiting for client applications.