[Iot smart gateway-16] successfully transplanted SQLite (stm32. Net MF platform)

Preface

The official code of the. NET micro framework system does not support any databases, which is inconvenient for some user-Managed Web servers, RFID data collection and complex host machine applications.

I have known SQLite for a long time, but I have not studied it in depth. net micro framework systems are becoming more and more mature, and users are increasingly demanding database support. Therefore, a database platform is urgently needed. Considering the porting difficulty and code size, I originally planned to change the memory yfiodb database in the yfios system to the file version, which is the fastest and the code is relatively small, but the disadvantage is obvious, it does not support select and other related SQL statements. for users who are familiar with database applications, it is better to use files to understand database operation methods.

In this process, I also learned about other embedded databases, such as fastdb and Berkeley dB. However, their user base and popularity are far inferior to that of SQLite, considering that IOT middleware itself is a framework and platform for secondary development, the more familiar users are, the more preferred technology.

Are you sure you want to port the SQLite database? Or earlier versions with relatively small code? This really gave me a long wait.

SQLite v2xAndV3xComparison

Project	V2.8.17	V3.7.15
Number of API Interfaces	44	207
Number of source files	44	89
Source code Byte Count	1.32 m	4.0 m
Win32 dll library size	209 K	591 K
Text Encoding support	UTF-8 or iso8859	UTF-8, UTF-16
Binary data (BLOB)	Not Supported	Supported
Row number	32 bytes	64 bytes
Concurrency	Multi-read, single-write	Improved concurrency

. Net micro Framework's core code is only about K. If it supports a database that is larger than its core, it's a bit of a pony's feeling of pulling the car, so on the basis of satisfying the basic functions (I used to worry about the international application of v2x version, for example, whether it supports Chinese), the code size is my most concern.

After testing SQLite v2.8.17 on the Windows platform, we decided to port SQLite of v2x (of course, we did not rule out porting version 3.0 later, the size of the release SQLite version is about KB.

SQLite v2xAndV3x. NET FrameworkDevelopment

Considering. net micro framework is. net Framework, so the initial research is system. data. SQLite. dll library, and later found that the first is system. data. SQLite. DLL encapsulation is too complicated. Second, it is system. data. SQLite. DLL pair. the framework of the. NET Framework is very dependent, and it is difficult to directly copy the framework for different platforms (must be installed). Third, it does not support the SQLite v2x version.

Therefore, we finally decided to use InterOP to directly access the SQLite. dll of Win32. After searching online, there are examples of sqlite3, and SQLite v2x does not exist. So I first studied the sqlite3 interface application, and then, according to the C ++ related interface definition, I reversed the C # interface of SQLite v2x.

If there is no problem with testing the SQLite v2x interface (for example) on the windows. NET Framework Platform, port SQLite v2x on the. NET micro Framework Platform.

 

SQLite v2x. Net micro frameworkPort

Theoretically, you only need to implement. Net MF on the interfaces in the OS. C code (the standard OS. c file already supports windows, UNIX, and Mac OS platforms ). However, the actual transplantation is far from that simple.

(1) SQLite code is standard C language, but OS. c needs to be called. net mf c ++ code operation interface. Therefore, all c extensions of SQLite source code are modified to CPP interfaces and compiled using the CPP compiler, there will be many compilation errors, most of which are type conversion errors.

(2 ),. net micro framework platform does not support (or is not recommended) using standard strings directly. h. ctype. h, math. H and stdio. H .. Net micro framework code already has some implementation of string operations, but in order to achieve the correct compilation of SQLite, you must complete relevant operation functions by yourself. Time, file, and other operation functions must also be converted to. Net micro Framework Platform or implemented by yourself.

(3) memory allocation functions. net micro framework has two types: one is the function starting with private _ and the other is the memory operation function supported by tinyclr. Because the two use different heap spaces (the custom_heap operation starting with private, this is generally relatively small), so when I debug a function that starts with private _, the actual application is a tinyclr memory operation function. Modify the SQLite memory-related operation function to. Net micro framework.

(4) because the underlying code of. Net micro framework is relatively simple in file operations, I cleared the temporary file generated by SQLite in the upper-layer C # code.

(5) Lock operation ,. net micro framework cannot lock files at the underlying layer. net micro framework itself (single process, multi-thread), so a simple lock implementation in the upper C # code (the read/write locks cannot be the same ).

(6) There are many SQLite pointer applications, and the system will encounter exceptions if you are slightly careful. Therefore, during the porting process, be sure to thoroughly understand the actual meanings of various interface pointers (for example, to define char *** P). Otherwise, the debugging process will be a nightmare.

SQLite. Net MFApplication Development

. The net mf c # interface is encapsulated again. First, the interface should be as close as possible to the system. data. SQLite. DLL compatibility. Second, necessary memory release and other processing (such as system formatting, creating temporary directories, and disk refresh ).

In fact, for relatively simple embedded applications, databases are nothing more than creating tables, adding and modifying data, deleting and obtaining table data. These functions can be implemented through SQL statements, so there are many functions, but the interface is very simple (as shown in ).

 

The test code is as follows:

Public static void main () {string dbpath = "\ Root \ mftest. DB "; using (SQLite DB = new SQLite (dbpath) //": Memory: ") {// create a table dB. executenonquery ("create table student (ID integer, name varchar (20), sex varchar (2);"); // insert a data dB. executenonquery ("insert into student values (1, 'red', 'femal');"); dB. executenonquery ("insert into student values (2, 'lil', 'male');"); dB. executenonquery ("insert into student values (3, 'xiaoming ', 'male');"); // read the table yfdatatable table = dB. executequery ("select * from student"); // Where name = 'lily'; "); string info =" "; foreach (yfcolumn Col in table. columns) {info + = Col. name + "";} debug. print (Info); foreach (yfdatarow row in table. rows) {debug. print (row [0]. tostring () + "," + row [1] + "," + row [2]) ;}} thread. sleep (timeout. infinite );}

Shows the debugging scenarios:

 

----------------------------------------------------------------------

 

Mf Introduction: http://blog.csdn.net/yefanqiu/article/details/5711770

Mf data: http://www.sky-walker.com.cn/News.asp? Id = 25

Related hardware: http://www.sky-walker.com.cn/Products.asp? Id = 24