This page is frozen, The module <MySQL> of the subsystem "DB"
| Module: | MySQL |
| Name: | DB MySQL |
| Type: | DB |
| Source: | bd_MySQL.so |
| Version: | 2.6 |
| Author: | Roman Savochenko, Maxim Lysenko (2009) |
| Description: | DB module. It provides the support for DB MySQL. |
| License: | GPL |
Introduction
Module <MySQL> gives to the system OpenSCADA support of DB MySQL. MySQL database is a powerful multi-platform database available for free license. Manufacturer of
DB MySQL currently is Oracle corporation. Fully free implementing of MySQL is project MariaDB. The module is based on the library with API of DB MySQL 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 MySQL address by string of following type: "{host};{user};{pass};{db};{port}[;{u_sock}[;{charset-collation-engine}[;{tms}]]]". Where:
- host — the name of the host on which the database server MySQL works;
- user — the name of the user of database;
- pass — user password to access the database;
- bd — the name of the database;
- port — port to listen to by the database server (default is 3306);
- u_sock — the name of UNIX-socket in the case of local access to the database (/var/lib/mysql/mysql.sock);
- charset-collation-engine — DB charset, collation and storage engine for "CREATE DATABASE" and "SET";
- tms — MySQL timeouts in form "{connect},{read},{write}" and in seconds.
In the case of local access to the database in the same host, you must use the 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 database you must use the host name and port of the server of the database. For example: "server.nm.org;roman;123456;OpenSCADA;3306"
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 MySQL. 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 MySQL correspond to types of elements of system OpenSCADA in the following way:
| The types of fields of the system OpenSCADA | 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) |
4. DB access
MySQL database provides a powerful mechanism for the separation of access, which is to selectively identify the access for user of the database to specific SQL-commands. The following table lists the operation over the database and the required access to the commands of these operations.| Operation | SQL-commands |
| Creation of the database and tables | CREATE |
| Deleting of the database and tables | DROP |
| Adding of records | INSERT |
| Deleting the values of records | DELETE |
| Getting the values of records | SELECT |
| Setting the values of 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:
- Install MySQL DBMS server by the package or by build.
- Start the DB server, if no start:
$ service mysqld start
- Setup need password for system user "root":
$ mysqladmin -u root password '123456'
- Connect to the DB local by the module help, enter DB address: "localhost;root;123456;test;;;utf8"
- External access permit:
- my.cnf: Change value of "bind-address" to "0.0.0.0" for allow the access from every interfaces.
- Connect to the DBMS local for the users management "$ mysql -u root -p":
- The users list getting: "SELECT Host,User,Password FROM mysql.user;"
- The user adding: "CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';"
- The user removing: "DROP USER 'newuser'@'%’;"
- The user password changing: "UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='newuser';
- The user privileges setting: "GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';"
- Apply the privileges: "FLUSH PRIVILEGES;"
5. Productivity of 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, MySQL 5.0.89 MyISAM WLAN | AMD Turion L625 1.6, 2G, MySQL [5.0.51 MyISAM 100Base-TX; WLAN]; [5.1.62 MyISAM local; 100Base-TX]; [5.5.41 MyISAM local; InnoDB; MyISAM 100Base-TX; InnoDB] | Intel(R) Core(TM) i3 CPU 1.33GHz, 3G, HDD [5.5.49 MyISAM 100Base-TX; InnoDB] |
| Creation of the 1000 records (sec.): | 4.53 | [1.1; 3.6]; [0.7; 1.00]; [0.57; 0.73; 2.1; 2.0] | [1.68; 1.56] |
| Updating of the 1000 records (sec.): | 4.2 | [1.12; 3.8]; [0.7; 1.07]; [0.6; 0.66; 2.0; 2.0] | [1.75; 1.87] |
| Getting of the 1000 records (sec.): | 2.88 | [0.8; 2.1]; [0.38; 0.69]; [0.38; 0.4; 1.3; 1.4] | [1.056; 1.12] |
| Seeking of the 1000 records (sec.): | - | - | [1.89; 2.23] |
| Seeking in preload of the 1000 records (sec.): | - | - | [0.03; 0.03] |
| Deleting of the 1000 record (sec.): | 1.47 | [0.43; 1.7]; [0.27; 0.42]; [0.23; 0.2; 0.79; 0.6] | [0.66; 0.67] |