SQLite use Log

I recently tested the memory database SQLite and found that the function has been enhanced a lot. For example, the multi-thread support (implemented using the file lock method) can also be fully implemented in memory (using ": memory:.

 

Multi-thread support is implemented through the file lock of the operating system. Therefore, multithreading is not supported when SQLite is opened in a full-memory database (that is, no disk file, read/write locks must be implemented independently for mutual exclusion. Ace read/write locks are recommended.

 

Since SQLite locks are implemented by means of File locks, they cannot implement table-level locks, but they can only be full database filelock. In addition, threads cannot share data by sharing database pointers. Instead, you must open an SQLite instance to enable the mutual exclusion mechanism. Therefore, if you use the ": Memory:" method to open SQLite, it is difficult for different threads to share a database (each thread opens a new instance ), if you simply share data through pointers, the mutex mechanism may not work properly. I have not found a solution to this conflict.

After testing, we found that there is still a certain gap between the performance of SQLite and its own data structures (such as STL, for example, the time for traversing a query result set is usually several dozen milliseconds (the time is mainly related to the size and size of the record set, and dozens of milliseconds are the results of dozens to hundreds of records ), however, considering convenient storage and indexing and transaction functions, this overhead is worthwhile.

 

Note that if you want to use the display transaction (the default DML statement operation uses the autocommit mode), you need to control retry on your own, instead of like a general network database, the database connection is used for blocking and wait. SQLite will return directly when you apply for a transaction, regardless of whether the transaction is successful or not. However, you can use the following methods to control it:

2. Int iret;
3. Do
4. {
5. Iret = cppsqlitedb: exedml ("begin immediate ;");
6. If (iret = sqlite_busy)
7. Sleep (20 );
8. } While (iret = sqlite_busy );

During the test, I used several encapsulation classes published on the codeproject Website:
1) ODBC encapsulation class codbcdatabase and codbcrecordset
2) SQLite encapsulation class cppsqlite
3) Encapsulation class poolmgr of the Object pool

These classes are good encapsulation classes and can be easily integrated into their own applications. I also specifically implemented several universal functions that directly convert the content of the network database to SQLite through ODBC. In this way, you can easily initialize local application data (especially when the initial data comes from a network database). You can also create a proper index for the data in a few words, play the role of a real memory database.