Modules/SQLite

English • ‎mRussian • ‎Українська

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

The module gives to OpenSCADA support of DB SQLite. DB SQLite is a small and 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 database website — http://sqlite.org. 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.

The module supports currently the concurrent work with common DB files, like to the libraries, from different processes of OpenSCADA by correct treating the files locking. So you will get warning messages while a DB file is locked, but you can successful write to it once it will be released by the transaction closing of a blocking process.

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 SQLite database is addressed by specifying the name of the database file in the following format "{FileDBPath}". Where:

FileDBPath — full path to the DB file (./oscada/Main.db).

Use the empty path to create a temporary database on the disk.

Use ":memory:" to create a temporary database in memory.

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 SQLite, by creating a new database with the necessary structure and copying data to it from the old one. 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.

The types of the elements of the DB SQLite correspond to the types of elements of OpenSCADA in the following way:

The types of fields of OpenSCADA	The types of fields of the database SQLite
TFld::String	TEXT
TFld::Integer, TFld::Boolean	INTEGER
TFld::Real	DOUBLE

4 Permissions

Access rights to the database are determined by the rights of access to a separate database file. The module supports work with SQLite database files in read-only mode, for example, in demos.

5 Productivity of the DB

Measurement of the performance of the database 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".

Operations for 1000 records, seconds	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
Creating:	3.3; 1.96	0.78; 0.43; 0.48	[0.49; 0.2]; [0.39; 0.22]; 0.056
Updating:	3.2; 2.47	0.7; 0.41; 0.45	[0.48; 0.19]; [0.40; 0.21]; 0.053
Getting:	2.6; 1.81	0.69; 0.38; 0.45	[0.36; 0.19]; [0.31; 0.21]; 0.051
Seeking:	3.1; 1.48	0.76; 0.42; 0.51	[-; 0.22]; [0.21; 0.22]; 0.052
Seeking in preload:	0.53; 0.29	0.13; 0.071; 0.088	[-; 0.028]; [0.027; 0.028]; 0.0073
Deleting:	1.18; 0.43	0.20; 0.10; 0.13	[0.08; 0.058]; [0.067; 0.069]; 0.012