Module | Name | Version | License | Source | Languages | Platforms | Type | Author | Description |
---|---|---|---|---|---|---|---|---|---|
MySQL | DB MySQL
|
4.0 | GPL2 | bd_MySQL.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 MySQL. |
The module gives to OpenSCADA support of the DBMS MySQL, which is a powerful multi-platform database management system available for a free license. Manufacturer of DBMS MySQL currently is the Oracle corporation. Fully free implementing of MySQL is the project MariaDB. The module is based on the library with API of DBMS MySQL on the language "C". The module allows you to perform operations over databases, tables and contents of tables.
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.
DBMS MySQL addresses by a string of the following type: "{host};{user};{pass};{db};{port}[;{u_sock}[;{charset-collation-engine}[;{tms}]]]". Where:
In the case of local access to the DBMS, within a single host, you need to use a UNIX socket, for example: ";roman; 123456;OpenSCADA;;/var/lib/mysql/mysql.sock;utf8-utf8_general_ci-MyISAM;5,2,2"
In the case of remote access to the DBMS you must use the hostname and port of the DBMS server, for example: "server.nm.org;user;password;OpenSCADA;3306"
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.
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 MySQL. 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.
Types of MySQL database elements are corresponding with the types of OpenSCADA elements in the following way:
Types of OpenSCADA fields | Types of fields of DB MySQL |
---|---|
TFld::String | char (n), text, mediumtext |
TFld::Integer | int (n), DATETIME [for fields with a flag TFld::DateTimeDec] |
TFld::Real | double(n, m) |
TFld::Boolean | tinyint(1) |
The MySQL database contains a powerful mechanism for the distribution of access, which consists in the selective specifying of access the database user to individual SQL commands. The table below lists the operations on the database and requires access to commands for these operations.
Operation | SQL-commands |
---|---|
Creation of the database and tables | CREATE |
Deleting of the database and tables | DROP |
Adding of records | INSERT |
Deleting records | DELETE |
Getting values of the records | SELECT |
Setting values of the records | UPDATE |
Manipulation with the structure of the table | ALTER |
Briefly we will look at the initial configuration of the MySQL server to connect for it using by this module:
$ service mysqld start
$ mysqladmin -u root password '123456'
$ mysql -u root -p
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 | Nokia N800, MySQL 5.0.89 MyISAM WLAN |
AMD Turion L625 1.6, 2G, MySQL |
Intel(R) Core(TM) i3 CPU 1.33GHz, 3G, HDD |
---|---|---|---|
Creating: | 4.53 | [1.1; 3.6]; [0.7; 1.00]; [0.57; 0.73; 2.1; 2.0] | [1.68; 1.56] |
Updating: | 4.2 | [1.12; 3.8]; [0.7; 1.07]; [0.6; 0.66; 2.0; 2.0] | [1.75; 1.87] |
Getting: | 2.88 | [0.8; 2.1]; [0.38; 0.69]; [0.38; 0.4; 1.3; 1.4] | [1.056; 1.12] |
Seeking: | - | - | [1.89; 2.23] |
Seeking in preload: | - | - | [0.03; 0.03] |
Deleting: | 1.47 | [0.43; 1.7]; [0.27; 0.42]; [0.23; 0.2; 0.79; 0.6] | [0.66; 0.67] |
Modules/MySQL/en - GFDL | December 2024 | OpenSCADA 1+r3000 |