From OpenSCADAWiki
Jump to: navigation, search
Other languages:
Constr.png The translation checking and actualizing
Module Name Version License Source Languages Platforms Type Author Description
SQLite DB SQLite 2.3 GPL2 en,uk,ru,de x86,x86_64,ARM DB Roman Savochenko
Maxim Lysenko (2009) — the page translation
DB module. It provides the support for DB SQLite.

1 Introduction

Module "SQLite" gives to OpenSCADA support of DB SQLite. DB SQLite is a small, embedded database which supports the SQL-queries. SQLite DB is distributed under a free license. To familiarize with the database it is possible on the website of the database — The module is based on the library with API of the manufacturer of DB SQLite on the language "C". The module allows you to perform operations over databases, tables and contents of tables.

2 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 it in the system. It also supports the operation of requesting the list of tables in the database.

SQLite database is addressed by specifying the name of the database file in the following format "{FileDBPath}". Where:

  • FileDBPath — full path to DB file (./oscada/Main.db).
Use empty path for a private, temporary on-disk database create.
Use ":memory:" for a private, temporary in-memory database create.

3 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.

4 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 SQLite. 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 SQLite 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 SQLite
TFld::String TEXT
TFld::Integer, TFld::Boolean INTEGER

5 Access rights

Access rights to the database are defined by the rights of access to the separately taken file of the database. Module supports the work with SQLite database files in read-only mode, such as demonstrations.

6 Productivity of the DB

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 Nokia N800, SD 4GClass6
Nokia N900
RPi2 Debian8 Class10 UHS1;
RPi3 Debian8 Class10 UHS1;
OrangePiZero Debian8 Class2
AMD Turion L625 1.6, 2G [HDD; SSD]
Intel(R) Core(TM) i3 CPU 1.33GHz, 3G [HDD; SSD];
Intel(R) Core(TM) i7 CPU 2.6GHz, 12G FastSSD
Creation of the 1000 records, seconds: 3.3; 1.96 0.78; 0.43; 0.48 [0.49; 0.2]; [0.39; 0.22]; 0.056
Updating of the 1000 records, seconds: 3.2; 2.47 0.7; 0.41; 0.45 [0.48; 0.19]; [0.40; 0.21]; 0.053
Getting of the 1000 records, seconds: 2.6; 1.81 0.69; 0.38; 0.45 [0.36; 0.19]; [0.31; 0.21]; 0.051
Seeking of the 1000 records, seconds: 3.1; 1.48 0.76; 0.42; 0.51 [-; 0.22]; [0.21; 0.22]; 0.052
Seeking in preload of the 1000 records, seconds: 0.53; 0.29 0.13; 0.071; 0.088 [-; 0.028]; [0.027; 0.028]; 0.0073
Deleting of the 1000 record, seconds: 1.18; 0.43 0.20; 0.10; 0.13 [0.08; 0.058]; [0.067; 0.069]; 0.012