Modules/MySQL

Module	Name	Version	License	Source	Languages	Platforms	Type	Author	Description
MySQL	DB MySQL Features: SQL, LIST, STRUCT, GET, SEEK, PRELOAD, SET, DEL, FIX, TR, ERR	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.

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.

DBMS MySQL addresses by a string of the following type: "{host};{user};{pass};{db};{port}[;{u_sock}[;{charset-collation-engine}[;{tms}]]]". Where:

• host — hostname on which the DBMS server MySQL works;
• user — user name of the DB;
• pass — password of the user for accessing the DB;
• bd — name of the DB;
• port — port, which listening by the DBMS server (default 3306);
• u_sock — UNIX-socket name, for local accessing to the DBMS (/var/lib/mysql/mysql.sock);
• charset-collation-engine — DB charset, collation and storage engine for "CREATE DATABASE" and "SET";
• tms — MySQL timeouts in the form "{connect},{read},{write}" and in seconds, the default value is "10,5,5".

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"

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 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)

4 DB access

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:

• Installing the MySQL DBMS server by the package or building.
• Starting the DBMS server, if not started:

$ service mysqld start

• Setting the correct password for the system user "root":

$ mysqladmin -u root password '123456'

• Connecting to the DBMS server locally and using this module by entering the database address "localhost;root;123456;test;;;utf8"
• Allowing external access:

• my.cnf: Change for value of "bind-address" to "0.0.0.0" for allow the access from every interfaces.
• Connect to the DBMS locally, to manage users:

$ mysql -u root -p

• Get the users list: "SELECT Host,User,Password FROM mysql.user;"
• Add an user: "CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';"
• Remove the user: "DROP USER 'newuser'@'%’;"
• Change the user password: "UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='newuser';"
• Set the user privileges: "GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';"
• Apply the privileges: "FLUSH PRIVILEGES;"

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	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]
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]