Research and Implementation of SQLite Embedded Database System

Research and Implementation of SQLite Embedded Database System

[Date:]

Source: single-chip microcomputer and Embedded System Application Author: Chen Shuang, Jin Lin, guangbin Palace, Southwest Jiaotong University

[Font:Large Medium Small]

　

Introduction
With the wide application of embedded systems and the increasing demand of users for data processing and management, close integration of various smart devices and database technologies has been paid great attention. This type of database not only has the main functions of traditional databases, but also has two features: embedded and mobile technology. Therefore, it is usually used in handheld computers, PDAs, on-board devices, mobile phones and other embedded devices. With the rise of this database technology, people are no longer limited by a single operating system and can process businesses and transmit information anytime and anywhere. It can be said that the development of embedded databases improves the universality of data information access and makes it possible for people to obtain information anytime and anywhere.

1. architecture of the embedded database system
Embedded databases integrate database systems with operating systems and specific applications and run on various SMART embedded devices. Compared with traditional database systems, it is generally small in size, has a strong portability, ease of use, and more complete functions to achieve user data management operations. However, due to the resource restrictions of the embedded system, it cannot be used as a complete database to provide large-capacity data management, and embedded devices can be placed everywhere, which is greatly affected by the environment and has low data reliability. In practical applications, in order to make up for the shortcomings of low storage capacity and low reliability of embedded databases, the background database is usually configured on the PC to store and manage large data volumes. As a front-end device, the embedded database requires a GUI to implement human-computer interaction on the embedded terminal, and data exchange with the main data source on the PC through the serial port, it manages data on the system server, receives data uploaded by the embedded terminal, and downloads data to the embedded terminal.

2 SQLite architecture and development technology
SQLite is a powerful embedded relational database management system developed by D Richard sipe using a small C library. It provides most support for SQL92: Multiple tables, indexes, transactions, views, triggers, and a series of user interfaces and drivers.
2.1 SQLite Architecture
SQLite can be divided into eight major subsystems, as shown in 1. The top layer is the tokenizer and parser ). Highly optimized analysis generators can quickly generate efficient code. The bottom is the B-tree optimized based on Knuth. This allows you to run on a page buffer (pagecache) that can be adjusted to minimize disk search. Next is the page cache. It acts on the abstract layer of the OS, and such an arrangement helps move the database. The core of the architecture is the virtual database engine (VDBE ). VDBE completes all operations related to data operations and is an intermediate unit for information exchange between customers and storage. It is the core of SQLite. After the SQL statement is analyzed, VDBE starts to take effect. The Code Generator translates the analysis tree into a pocket program, which is then combined into a series of commands expressed in VDBE virtual machine language. In this way, VDBE executes each command to complete the query requirements specified by the SQL statement. The machine language of VDBE is composed of 128 operation codes (op-codes) managed around the database. Operation codes are available for opening tables, querying indexes, storing and deleting records, and many other database operations. Each Command in VDBE is composed of one operation code and three operands (operand. Some commands use all three operands, and some commands are not used either. This depends entirely on the nature of the command. For example, if the Open command is used to Open a table pointer, all three operands are used: 1st operands (P1), including the pointer idnumber, and 2nd operands (P2) specifies the root location of the table (or the homepage location of the Table). The first operand is the table name.

2.2 SQLite Development Technology
SQLite APIs are easy to use. Only three functions are required to execute SQL statements and obtain data. It is still extensible, allowing programmers to customize functions and then integrate them through the callback () function. The open source code group has expanded many customer interfaces, adapters, drivers, and so on, which makes it possible to use SQLite in other languages.
The C language API only requires three steps. First, provide the file name and access mode to call sqlite_open () to connect to the database. Then, run the callback () function. SQLite executes the callback () function for each record to obtain the results from the database. Finally, if you want to execute an SQL query and obtain a pointer to the callback () function, you can call sqlite_exec (). You can also call the sqlite3_get_table () function to obtain the data form content.
SQLite also provides methods to Access Binary large objects (BLOB). It also provides powerful and convenient technical support in thread security, database management, and API scaling.

3. Compile and copy SQLite on the ARM9.
Download the SQLite-3.3.8 version at SQLite official website www.sqlite.org and decompress it on the terminal to generate the SQLite-3.3.8 directory. Go to this directory and make certain changes to sqlite/src/sqliteInt. h to ensure that btree (B tree) has the correct variable size. Linux systems with different architecture may have some differences. For ARM-Linux, you can find the following parts:

In this way, "typedef INTPTR_TYPE ptr;" is defined as "int" instead of "long ".
Modify the content of the configure file and comment out the relevant code so that the system does not check the cross-compilation environment. Otherwise, an error occurs and the Makefile file cannot be generated. Create a folder named sqlite-arm-linux to save the library file to be generated. Run the following command to configure the file:

Makefile will be generated in this directory. Modify the following statement in the Makefile file to specify the host compiler as GCC:

Then you can run the make & make install command to start compilation. If no other error message is displayed /.. /sqlite-3.3.8/sqlite-arm-linux/lib directory to generate library files. There are libsqlite3.so, libsqlite3.so. O, and libsqlite3.so. O.8.6 files. Meanwhile, an executable sqlite3 file is generated in the bin folder. To reduce the execution file size, run the strip command to remove the debugging information.
After the library file is generated, you can copy the relevant library file to the ARM Development Board through NFS (Network File System), set the host NFS server, start the target board, and execute:

192.168.1.101 is the IP address of the NFS server. In this way, the folder where the sqlite library file is located is mounted to the target board. When copying the file, add the-ASON option because libsqlite3.so and libsqlite3.so. O is linked to Iibsqlite3.so. o.8.6.

4. Design and Implementation of an Embedded Database System
For embedded systems, the powerful graphic user interface enriches the functions of the system. At present, there are many guis available for use. Considering the authorization and price factors, we develop the database interface program on the basis of Qt/Embedded to complete the design of the Embedded database system. The excellent encapsulation mechanism of Qt makes Qt highly modular and reusable. The basic structure of the embedded database system 2 is shown.

You can use the QTable control of Qt to display data forms, and use controls such as QListView, QC20mBobox, and QTextEdit to select data forms and input data, use the PushButton control to create, delete, query, add, delete, and modify data tables. In this way, the basic management of the database system can be realized, and data interaction with the background database can be realized through the serial port. The system uses Qt Designer to implement rapid development of the database interface, and calls the SQLite API functions to implement basic database operations. The following describes the basic methods and key code for developing the database interface with QtDesigner. Create a new C ++ project, create a new widget, and add a Table control on the widget as the display interface of the data form. Add the PushButton control as needed to perform corresponding operations. Create a new slot in the window class and name it SelectSlot (). The key code for connecting the button signal is as follows:

The tableView function is used to display the queried data Table in the Table control. The function body program is as follows:

Enter the project directory under the terminal, modify the *. pro file, and add the following content:

The path of the library file required for compiling the program and the path of the sqlite. h header file are added here. After all this is done, start compiling.
Tmake-o Makefile EDBMS. pro generates Makefile, and changes the compilation tool to arm-linux-g ++, and adds related library files for cross-compilation. Then make compilation generates binary files that can be run on the ARM Development Board and mounted to the Development Board through NFS. Run./EDBMS and a window is displayed. Query the content of a data table and display it on the QTable control, as shown in Figure 3 on the main interface.

In addition, the system uses SQL Servqr. 2000 as a background database, developed using VB 6.o and connected to the database through ODBC, implements background database management and is responsible for receiving and processing client requests. The PC and embedded devices can communicate through the serial port to improve the system functions. In this way, a more complete embedded database system is built.

Knot
Based on the study of embedded database features, this paper designs an embedded database system architecture. Based on the characteristics of embedded databases, SQLite is used to build an embedded database system on the ARM-Linux platform. On this basis, the database interface is designed using Qt and the SQLite API is used to perform basic database operations. Finally, the configuration of the background database and the serial communication mechanism are briefly introduced, and the entire embedded database system is built.