Embedded Internet of Things Applications of SQLite Based on WinCE Mobile Terminal

At present, embedded applications on the WinCE platform are extending towards microservices and miniaturization. More embedded device application data requires small, embedded database systems to organize, store, and manage. The embedded database SQLite has many advantages such as zero-conguration, lightweight, multiple interfaces, easy portability, readable source code, and open source. It is currently widely used in the WinCE embedded operating system. This article discusses the technical characteristics of the SQLite database in detail, SQLite data manipulation, SQLite transplantation under the WinCE platform, and nally implements SQLite data management on WinCE mobile terminal based on MFC programming.


Introduction
In recent years, with the continuous development of computer technology, the importance of embedded development has been continuously enhanced, and the application of databases has become more and more prominent. Compared with other databases, the characteristics of SQLite and the working environment of WinCE Features have a large degree of t, so they are widely used. This article focuses on understanding this database and exploring its applications to some extent.
Windows CE system is a small embedded operating system introduced by Microsoft. It is designed for handheld electronic devices. This operating system allows existing Windows desktop technologies to be integrated with complete mobile technology. As we all know, many Microsoft Windows operating systems use the same standard, so even if the platform changes, the system can still run, but Windows CE is not like this, so it is only applicable to some speci c platforms. Therefore, to achieve a variety of product requirements, Windows CE is designed to be able to use a variety of different standard modes, which means that it can choose the required mode from which to customize its products. Also, its components can be some available models, which means that these modes can be selected from a set of available groups and then become standard models. Although Microsoft claims that "CE" in Windows CE has no special meaning, the embedded development eld has many different views on CE. One of the main points is that C in Windows CE stands for consumption, pocket-size, communication ability or companion, and E stands for electronic products. It seems that there is a certain relationship between Windows CE and the meaning of these words [6,7]. Windows CE is a new embedded operating system developed by Microsoft. Compared with the previous desktop operating system, all relevant source code of Windows CE system is developed by Microsoft, although the system interface of Windows CE is taken from the previous Desktop operating system, it is still a newly developed, new information device platform based on win32 API. Windows CE is a processor-independent system based on the Win32 application program interface. It also has the characteristics of structure and modularity. Windows CE, like desktop operating systems such as Windows 95/98, can use programming software such as VB and VC, and the functions and interface design styles used in the programming process have not changed much. In this way, when you want to use the application on the desktop operating system in Windows CE, you only need to modify it. The system architecture of Windows CE is hierarchical, as shown in Figure 1, divided into four layers from bottom to top. They are the hardware layer, OEM layer, operating system layer and application program layer.
Each layer is described in detail below:

1) Hardware Layer
Embedded hardware generally has the following characteristics: The architecture of the processor is not uniform.
Hardware resources are generally limited.
There are many types of external devices.
Requirements for real-time performance and reliability.

2) BSP Layer
The BSP layer corresponds to the OEM layer. The BSP is tied to the operating system, and the BIOS work at the beginning is similar, but most of them are different. BSP can add a system-independent drive, and can even be developed on the upper layer, while the BIOS is not.

3) Operating System Layer
The operating system layer implements WinCE as the main function of the operating system. WinCE's process management, thread management, scheduling, physical memory, virtual memory, le system, and device management are all implemented at this level.

4) Application Layer
The application layer of the Windows CE system is very similar to desktop Windows such as Windows 95/98 and is located at the highest level of the operating system hierarchy. From a system-level perspective, in Windows CE, each running application is considered to be a separate process in the system, and the Win32 API is still the interface between the application and the operating system. However, the API of the Windows CE operating system is only a part of the desktop system API, and because the system resources of the embedded operating system are limited, the API that occupies too many resources in the desktop API is di cult to implement or has a low utilization rate is selectively ltered. If you want to re-use these APIs, you can only write them yourself. Also, the Windows CE system adds some unique APIs.

Introduction To Sqlite Database
The original idea of the SQLite database originated from D. Richard Ship, who was writing a program for the guided-missile destroyer for the U.S. Navy. Because the Informix database used at the time was too large and time-consuming, Shipp and colleagues using the GNU DBM hash library as the backend, a simple embedded SQL database was created. Then with the next major upgrade, SQLite has developed steadily, and features and users have also grown. By mid-2001, many open source or commercial projects started using SQLite. After the extension of Java, Ruby, Python and other mainstream programming languages in the following years, the emergence of new extensions such as SQLite's ODBC interface has once again proved the widespread application and practical functions of SQLite. It has been 15 years since 2015, and SQLite has ushered in the release of SQLite3. SQLite3 is a brand new version of SQLite.
Although it was developed based on SQLite 2.8.13 code, it uses database formats and APIs that are not compatible with previous versions. SQLite3 meets the requirements of some new features, such as supporting UTF-16 encoding, supporting user-de ned text sorting methods, and indexing BLOBs elds. And the SQLite database version 3.0 and 2.X version APIs are very similar, but there are some important changes to note: All API interface functions and data structure pre xes have been changed from "sqlite_" to "sqlite3_". Link con icts occur when using SQLite 2.X and SQLite 3.0. Because there is no consistent speci cation of what data type C language should use to store UTF-16 encoded strings. Therefore, SQLite uses ordinary void * types to point to UTF-16 encoded strings. The client can use the void * maps to any data type suitable for their system. Three functions are most commonly used in SQLite 3.0 database operations: sqlite3_open(), sqlite3_exec(), and sqlite3_close(). If you want to better control the execution of the database engine, you can use the provided sqlite3_prepare() function to compile SQL statements into bytecode, and then use the sqlite3_step() function to execute the compiled bytecode.

SQLite Meaning
SQLite is a lightweight database and a relational database management system that complies with ACID. It is contained in a relatively small C library and implements a self-su cient, serverless, zerocon guration, transactional SQL database engine. Just like other databases, the SQLite engine is not a separate process and can be statically or dynamically connected as required by the application. SQLite directly accesses its stored les.

SQLite Features
SQLite is an embedded database, which is similar to Microsoft Access, but a .db format le. But unlike Access, it does not require any software to be installed and is very lightweight. It is used by many embedded applications, including Tencent QQ, Thunder (you can see a sqlite3.dll le in the Thunder installation directory, that is it), and now the well-known Android and so on. SQlite3 is its third major version. Speci cally, the SQLite database has the following characteristics:

SQLite database management is simple and very stable
SQLite is a single le, so it is easy to manage. The le format is very stable on many major versions. The characteristic of SQLite is that the database exists as a single le and has a stable format. Also, SQLite is easy to con gure. The functionality of SQLite can be managed in two ways: compilation ags and PRAGMA statements (runtime con guration). There is no such thing as a con guration le, you just need to build the library you want and then con gure the runtime options when you create the database connection.

Scalability and controllability of SQLite database
Since SQLite is embedded in the application, it runs in the same address space and can execute the application code on your behalf. Whether it is the Python standard library SQLite driver pysqlite or the optional driver aspw, it can provide APIs that de ne custom SQL functions, aggregation functions, and classi cations. aspw goes one step further and provides APIs that can also be used to de ne virtual tables and virtual le systems.

SQLite database is very fast
SQLite is very fast. Because it runs in the same address space, there is no line protocol, serialization, and no need to communicate through UNIX sockets. And it runs on the same computer, so it has no network burden when executing queries or reading results. Also, SQLite can run on mobile devices.

SQLite integration of BerkeleyDB
BerkeleyDB only needs to lock individual pages, not the entire database, so it can give application developers the concurrent database access required even better performance. Another bene t of BerkeleyDB is that it uses fewer system resources to improve system e ciency.

Design goals of SQLite3
The design goal of SQLite is to achieve an embedded, secure, e cient, stable, easy-to-manage database that takes up very low system resources. Especially in embedded devices, only a few hundred K of memory may be needed to ensure its smooth operation.

The internal structure of SQLite3
Inside the system, SQLite3 is mainly composed of the following components: SQL compiler, kernel, backend, and accessories. In an implementation, SQLite3 is a database that uses the virtual machine and VDBE (Virtual Database Engine) to debug, extend and modify the kernel of SQLite3. SQLite3 supports databases up to 2TB in size. Disk les storing each database can be moved between computers in different orders. SQLite3 obtains its database permissions based on the le system. In SQLite3, all SQL statements are compiled into human-readable assemblies that can be executed in the SQLite3 virtual

Create Operation
In SQLite, as with SQL Server and MySQL, the creation operations include creating tables, views, and indexes. Details as follows:

Fuzzy query
The fuzzy query is usually to give a range of data and then use like followed by conditions that require data to nd related data.
select * from <table name>; // all data; select * from table1 where name like "s%"; //% means any number of characters select * from table1 where name like "s_"; // _ means any character select * from table2 limit 0,3; // 0 means starting from // rst line, 3 means querying three records select * from student where age> 10; // conditional query The select statement is relatively complex in SQL, and it is also the essence of SQL. Syntactically, the select statement is composed of a series of words, and each word can complete a speci c operation. The select statement except select Except for the sentence, all the remaining words are optional. In the delete operation, you can also use where and like to perform precise deletion and fuzzy deletion. The use of where and like in delete is the same as that. Therefore, no speci c description is given here, but only a brief description in the above example. The same applies to views and indexes.

Increase Operation
The insert statement is used to increase the data. When using this statement, pay attention to the column names and values must be corresponding, you can not confuse the order, otherwise, the data insertion is prone to errors. Similarly, conditional statements can also be used when updating data. The method of use is to add conditional statements after the statement. The method is similar to the previous one, so it will not be described

Other SQLite output commands
As shown in Table 2 below [9][10]. After customization, WinCE can achieve the minimum mode required by existing systems, thereby reducing the storage of scripts and the operation of the operating system. WinCE is a compact, intact and scalable embedded operating system. It is a multi-threaded, priority-based preemptive operating system. It is especially suitable for hardware platforms with relatively few resources. The system is not safe, reducing the risk of damaging the system due to abnormal applications face to face. Its kernel can also be used for streamlining and customization. The products on the Windows CE platform are roughly divided into three product lines: Pocket PC (handheld computer) Handheld PC (handheld PC) and Auto PC. In recent years, the market share of embedded products has become higher and higher. The WinCE platform can be used in consumer electronics equipment, network terminals, Internet access equipment, industrial data acquisition controllers, mobile handheld devices and embedded devices, and provides modular software systems for them. The WinCE operating system is not inferior to the current mainstream Android operating system in the market, which can be re ected in the following four aspects: 1. Good security. The system is mature, well-closed, long used, stable, and easy to integrate with Windows systems.
2. Application software compatibility is better.
3. It still has a large market share.
4. Compared with the Android system, it supports more hardware platforms, such as x86, ARM, MIPS, SuperH and other mainstream CPU structures in the embedded eld.

Transplanting of SQLite on WinCE Platform
SQLite porting will require different projects depending on the platform. Here is an example of EVC to brie y introduce the transplantation process.
First, download the SQLite source code from the http://www.sqlite.org/ website, which includes les in two different formats, .c and .h, and extract the two les into the same directory. Secondly, you need to install and con gure the WinCE platform compiler. This requires you to install a software development kit customized by your development version in a certain development environment, such as Windows. After the installation, you will nd support under the compiler selection in EVC. Options for this development board.
Next, it is to create a suitable project le and create a new project in the Ethernet virtual link. Here we take the dynamic link library as an example for analysis. First, select the DLL in the project type, use "SQLite" as the project le name, and then select ARMV4I in the "CPU type" option. The next step is to generate the nal library le. Add the downloaded source le rst, and then write the interface de nition module, de nition le, and remove some external library les. Take version 4.3.1 as an example. Shell. c, tclSQLite. c, ICU. c is the three les that need to be removed. The rst le is mainly used to generate management tools. In the project of building DLL, this le does not affect. The second le is mainly used to support TCL. The script, but it is not used in embedded programming, so there is no need to exist. The third le is to fuse the ICU library and the SQLite library. In this process, the ICU library is not needed, so it needs to be used. Removed. The last step is to optimize the library les according to speci c needs.
As we all know, the WinCE platform is a relatively popular and widely used operating system in the embedded devices of smart mobile terminals. This system is characterized by small storage space, so when developing applications in the WinCE system, such as Microsoft's SQLServer database and Sybase's SQL Anywhere database and Oracle's Oracle database [11], it takes up a little more resources. Traditional databases are no longer suitable for this system. Based on the above analysis, this article will introduce a relatively compact and high-performance embedded database SQLite [2,12]. This database can more easily implement the storage and management of data in the system so that it can be e ciently developed. Applications are suitable for this system [5].

Results And Discussion Section
The characteristics of SQLite make it a good application in WinCE to achieve the storage and management of data. Below we will use Visual Studio2005 and SQLite database to connect with the mobile smart terminal on the WinCE platform and apply the database simply. Here are the steps for the application example: 1. Establish an MFC smart device application, import the library le generated in the previous migration process into the project and link accordingly.
2. The main application we implemented in the experiment is to implement a simple application of the smart device squadron database. Therefore, the design of the program is relatively simple. The MFC application we built uses the form of a dialog box. Therefore, consider the dialog box A button is added to call the corresponding database operation. Three buttons are added to create a database, create a table, and a query table, and a text box is used to display the contents of the table. The code of the corresponding control is as follows:

Conclusions
The previous application mainly refers to the application of databases to smart mobile devices based on the WinCE platform, but technology must have its practical value to receive attention. In practical applications, the application of databases has received more and more attention, especially Driven by big data, the application, and processing of data are becoming increasingly important. Also, the development prospects of embedded programming have become broader, with a large gap. Therefore, the application of databases in embedded programming has become more and more important. SQLite has its unique characteristics in embedded programming. It is widely used in programming. In this regard, the predecessors have done a lot of research and attempts and achieved certain results in combination with hardware processing.
There are two main types of mobile terminal applications. One is high-performance, which is only applicable to some industries. The other is a low-end platform that performs simple data collection, processing, and interaction. With the development of computers, the performance of high-end equipment has continued to increase and the price has decreased, and low-end platforms have gradually been replaced by low-end handheld computers. The "high-end" part tends to the elds that require precision and rigor, such as military and medical, while the "low-end" is more to meet the needs of individuals, communities, some enterprises, and elds.
For example, it is applied to the functions of phone, information and address book of ordinary smartphones; some functions of the mobile business hall. These have achieved great results. Also, SQLite based on the WinCE platform has appeared in the design of related tour guide systems. For example, some people in China have conducted in-depth research on tour guides in the eld of tourism and found that their freedom and comprehensiveness in obtaining travel information have defects.
Transplanted the SQLite embedded database application, and realized the optimization of the museum guide. Many other scholars have also carried out different levels of optimization research on tour guides.
Also, many scholars have been involved in the research of this database synchronization system. It can be seen that, in practical applications, SQLite is very important in WinCE embedded development. [13][14] Abbreviations We can provide the data.

Competing interests
These no potential competing interests in our paper. And all authors have seen the manuscript and approved it to submit to your journal. We con rm that the content of the manuscript has not been published or submitted for publication elsewhere.