Implementation and Application of Embedded Databases in CNC Systems

Abstract : Addressing the problems of current CNC systems that commonly use text files for data storage and management, this paper proposes a novel technical solution for embedded CNC systems using a MySQL database for data management. Under the RTLinux operating system, a machine tool database was designed using MySQL, a user interface was created, and the connection between the database and the user interface developed based on the GTK+ library was established. After minimal database optimization, the system was successfully ported to the embedded CNC system hardware platform. Results show that the database management method in the embedded CNC system achieves effective management of a large number of parameters, significantly improving system performance.

Keywords : Embedded CNC system; MySQL database; Linux; MySQL C API; Porting

1 Introduction

This paper discusses the database development and application within the peripheral sub-project of human-computer interaction in the larger topic of CNC systems. CNC machining systems contain a large amount of data, including user-defined parameters, system parameters, coordinate axis parameters, axis compensation parameters, and tool characteristic parameters. This data is essential not only for CNC programmers but also for real-time process control systems, scheduling systems, process handling, inventory management systems, and tool maintenance management systems. The way this data is organized and managed significantly impacts the real-time performance and even the overall performance of the CNC system. Therefore, high-performance CNC systems must address the issue of effective data organization and management. Simply storing data in a table or character array makes modification and maintenance extremely difficult and complex, contradicting the CNC system's requirement for simple, convenient, and fast data operations. This organizational method cannot meet the requirements of CNC systems. Currently, the commonly used text file format for storing and managing data in CNC machining systems also suffers from poor real-time operability and significant disk space waste, both of which are crucial for high-performance embedded CNC systems. Therefore, in this research, the authors propose a novel technical solution for managing data in a CNC system based on a MySQL database under an embedded Linux ^[1] environment. Practical results show that using a database for data management in an embedded CNC system meets the requirements of high-performance embedded CNC systems in terms of real-time performance and storage space, improves system speed, and greatly enhances system performance.

Since the human-computer interface developed in this project uses the GTK+ graphics library, the application development based on the MySQL database in the system needs to solve the problem of the integration of MySQL and GTK+. GTK+ does not have controls for directly manipulating the database. Database operations can be performed directly through the MySQL C API, and then the display function of GTK+ can be called to complete the interaction with the user ^{[2] [3]} .

2. Database Implementation

2.1 Database Establishment

2.1.1 Create the work database

First, create a database named "work" containing all the tables the system needs, and connect to MySQL as the root user. The specific creation process is as follows:

% mysql –uroot mysql; // Enter the database

mysql> SET PASSWORD=PASSWORD('lyw00001'); // Assign a password to the root user

mysql> CREATE DATABASE work; // Create the work database

mysql> GRANT ALL ON work . * to lyw@localhost IDENTIFIED BY 'lyw00001'; // Create a user named lyw // Use the same password as the root user and grant the user full access to the database work

% mysql –ulyw –p lyw00001 work; // Connect to the database

2.1.2 Creating a table

For user interfaces designed based on the GTK+ library, there are several corresponding tables. The following section uses one interface as an example to explain in detail the connection process between MySQL and GTK+.

After connecting to the database, you can add content to it. Use the CREATE command to create a table named tbl_amend_amend.

CREATE TABLE tbl_amend_amend (number INT NOT NULL PRIMARY KEY, shapeh VARCHAR (10), deleteh VARCHAR (10), shaped VARCHAR (10), deleted VARCHAR (10),);

After creating a table, you can use the SQL INSERT command to populate it with data, but this method is very time-consuming when dealing with large amounts of data. Therefore, the author uses a batch processing method to populate the table with data. Specifically, first, create a text file named amend_data.sql to store the SQL statements, as shown in Figure 1; then execute the SQL statements:

% mysql –ulyw –p lyw00001 work < amend_data.sql

The data in Figure 1 has been added to the tbl_amend_amend table, as shown in Table 1. If you need to modify the data, you only need to do so in the amend_data.sql text file and then re-execute the statement mentioned above to easily complete the task.

Figure 1 amend_data text file

Table 1 tbl_amend_amend table

2.2 Connection Module

After creating the database and tables and having the relevant data, the next step is to implement a connection between the application and the database. The application uses the `connect_to_db()` module to implement the connection functionality with the `work` database. The module code is as follows:

The mysql_real_connect function is used to connect to a MySQL server named conx, whose host IP address is "127.0.0.1", username is "lyw", password is "lywcita001", and the database name is work.

After establishing a connection to the database, the next step is to populate the user interface with the database data. The application uses the `Fill_clist()` module to implement this data population functionality. The process is as follows: query the required data using MySQL; store the query results in an internal cache; and display the cached data on the user interface. The module code is as follows:

void Fill_clist() {

...;

gchar *amend_clist_row[5] = {,,,,}; // Define gtk+ array data pointers and variables

mysql_query(conx,select number,shapeh,deleteh,shaped,deleted from tbl_amend_amend);

result_set = mysql_store_result(conx); // Store the query results in the internal cache.

num_fields = mysql_num_fields(result_set); // Get the number of columns in the result set

while (db_row = mysql_fetch_row(result_set)) { lengths = mysql_fetch_lengths(result_set); // Get the length of the column

amend_clist_row[0] = db_row[0];

...;

amend_clist_row[4] = db_row[4]; // Assign the values ​​of the obtained db_row string array to amend_clist_row one by one.

gtk_clist_append(GTK_CLIST(modify_list), amend_clist_row); } // Fill the corresponding fields in the clist.

mysql_free_result(result_set); } // Release the memory used for the result set. Compile and run the insert program to implement the function of filling the database with data to the user interface, and obtain the interface shown in Figure 2.

When users modify system parameters on the interface, they want the new data to be written to the database for storage to ensure data consistency and continuity. Therefore, the application should have the function of updating the database according to changes in system parameters. Similar to the population module, the application uses the Update_database() module to implement the database update function.

3. Database migration

This CNC system uses an embedded PC platform, which is fully compatible with standard PCs. Therefore, the design and development can be done on a regular PC and then the software can be ported to the embedded PC. The system was developed using an embedded PC104 computer with a hardware level of P3, 128M memory, and a 128M CF card for program storage ^[4] .

3.2 Transplantation process

To use a database and user interface in a CNC system, the MySQL database and user interface application need to be migrated to a CF card. Considering the capacity limitations of the CF card, the database needs to be scaled down during the migration process.

3.2.1 Porting the mysqld runtime server

For MySQL to run on embedded systems, porting the mysqld executable is crucial. The command `ldd mysqld` queries for libraries related to the mysqld executable; the results are shown in Table 2.

The executable file mysqld is 3.3M in size, while its related library files are about 2M in size. By porting mysqld along with the aforementioned related library files to the CF card, mysqld can be effectively executed on the embedded hardware platform.

3.2.2 Porting of Application Files

Assuming the application's executable file is named 'app', the command `ldd app` can be used to find the libraries associated with the executable file 'app'. The application, along with its associated libraries, is then ported to the CF card. The application is approximately 100KB in size, and the associated libraries are approximately 8MB in size. Most of these 8MB of libraries are already included in the embedded Linux system and do not require further porting. This allows the user interface program to run on the embedded CNC system while the database program runs in the background.

4. Conclusion

CNC technology is an important component of modern machining technology. It is of great significance to continuously improve the CNC system on the basis of existing technology to meet new needs. With the further requirements of the machining field for the precision, efficiency, function and intelligence of CNC system ^[5] , the control panel, human-machine interface and system data organization and management methods need to be continuously improved and innovated. Compared with the text file management method commonly used in CNC systems, the database only needs to modify and update the data in an imported document to complete the maintenance of the data. The operation is simple, convenient and fast. Moreover, the database index enters the memory when the database starts. The index can easily and quickly complete the database query, data record deletion and addition. The database has a very small capacity after minimization. Therefore, it meets the requirements of high-performance embedded CNC system in terms of real-time performance and storage space, improves the system speed and greatly improves the system performance.

References:

[1] Yu Mingjian, Chen Xiangyang, Fang Han. Linux Programming: The Definitive Guide [M]. Beijing: China Machine Press, 2001. 22-337.

[2] Kong Lingfu, Cao Liqiang. Developing database applications using GTK+ and MySQL [J]. Computer Engineering and Science. 2001, 23(3): 45-46, 54.

[3]TCX. MySQL Reference Manual[EB/OL]. http://www.mysql.com/documentation/mysql/full/,2000-09-25．

[4] Li Hao et al. Typical applications of CF cards in large-capacity data storage systems [J]. Microcomputer Information (Measurement and Control Automation). 2005. Vol. 21, No. 11-1: 66-68

[5] Xiong Qingping. Development trend of CNC system technology [J]. Mechanical and Electrical Engineering Technology. 2004, (3): 91-94