Access native C ++ encapsulation of SQLite in Windows Mobile

Background

Currently, in the mobile device development field, SQLite has almost become a de facto standard in terms of local data storage, andriod (Android. database. SQLite), iPhone (SQLite for iPhone SDK and fmdb for iPhone), palm WebOS (WebOS SQL tutorial ), the new version of Symbian is also directly built-in SQLite (20 million Symbian smartphones shipped in Q3 2007 newer versions of the symbianos have SQLite built in .). As an important member of the mobile device field, how can Windows Mobile Miss SQLite.

 

Introduction

SQLite almost sets up a de facto standard for data storage in the mobile device development field. SQLite has been widely used on platforms such as andriod, iPhone, WebOS, and Symbian. This article describes how to use native C ++ to access SQLite on Windows Mobile platform, it also describes the implementation and use of an encapsulation class.

 

SQLite source code

SQLite source code can be downloaded from SQLite download page. I used sqlite.phxsoftware.com's build project on Windows Mobile to save time.

 

C ++ encapsulation of SQLite

Encapsulation I used the encapsulation cppsqlite3u of tyushkov Nikolay. Here, I would like to thank egmkang for its recommendation. Cppsqlite3u encapsulation is an oo c ++ encapsulation for the original pure C APIs of SQLite. It mainly encapsulates the following categories:

1. cppsqlitedb class, used to create a database, open the close link, and execute DDL and DML.

2. cppsqlite3statement is used to execute parameterized SQL statements. Cppsqlitedb can execute SQL statements but does not support parameterization.

3. cppsqlite3query is used to read the query results after the SELECT statement is executed.

4. cppsqlite3exception is used to catch exceptions.

Simple and clear encapsulation of SQLite.

 

Use of encapsulation classes

The usage is derived from the unit test on the cppsqlite3u class. See sqlitehelpertest. h In the source file.

 

Create Database Files

 
Test (sqlitehelper, createdatabase)
{
Try
{
Cppsqlitedb;
Deletefile (db_file_name );
DB. Open (db_file_name );
DB. Close ();
}
Catch(Cppsqlite3exception E)
{
Fail (tostring (E. errormessage (). c_str ());
}
Trace ("Create database successful .");
}
 

When you call the open () function of cppsqlitedb, a new database file is created if no database file is found. SQLiteSource codeAs follows (see sqlite3.c ):

 
Rc = opendatabase (zfilename8, ppdb,
Sqlite_open_readwrite | sqlite_open_create, 0 );
 

 

Run DDL

 
Test (sqlitehelper, createtable)
{
Try
{
Cppsqlitedb;
DB. Open (db_file_name );
Db.exe cdml (L"Create table T1 (F1 int, F2 char (20), F3 char (20 ));");

DB. Close ();
}
Catch(Cppsqlite3exception E)
{
Fail (tostring (E. errormessage (). c_str ());
}
Trace ("Create Table successful .");
}
 

Execute the execdml () function of cppsqlitedb to execute DDL. The Data Type Definition of sqlite3 is very different from other common relational databases. The data type definition information of sqlite3 is bound to specific data instead of field definition, which is dynamic, different records of the same field can store different data types. Therefore, it is not necessary to define the field type when defining a table. For details, see datatypes in SQLite version 3.

 

Execute DML

Test (sqlitehelper, inserttable)
{
 Try
 {
Cppsqlitedb;
DB. Open (db_file_name );

Cstring sqlstr;
Time_t tmstart, tmend;
Tmstart = time (0 );
 For ( Int I = 0; I <Max; ++ I)
{
Systemtime currenttime;
Getlocaltime (& currenttime );
Sqlstr. Format (L "Insert into T1 (F1, F2, F3) values (% d, 'str % d', '% d-% d: % D ')" ,
I, I, currenttime. wyear, currenttime. wmonth, currenttime. wday, currenttime. whour, currenttime. wminute, currenttime. wsecond );
Db.exe cdml (sqlstr );
}
Tmend = time (0 );
 Char Ch [255];
Sprintf (CH, "Insert Table successful in % d seconds" , Tmend-tmstart );
Trace (CH );
DB. Close ();
}
 Catch (Cppsqlite3exception E)
{
Fail (tostring (E. errormessage (). c_str ());
}
}

The execdml () function of cppsqlitedb can not only execute DDL, but also execute DML. Similarly, the update and delete statements are the same.

 

Execute scalar

Test (sqlitehelper, selectscalarbeforeinsert)
{
Try
{
Cppsqlitedb;
DB. Open (db_file_name );

IntCount = db.exe cscalar (L"Select count (*) from T1 ;");
CharCh [255];
Sprintf (CH,"% D rows in T1 table", Count );
Trace (CH );
DB. Close ();
}
Catch(Cppsqlite3exception E)
{
Fail (tostring (E. errormessage (). c_str ());
}
Trace ("Select scalar before insert successful .");
}
 

The execscalar () function of cppsqlitedb imitates the sqlcommand. executescalar of ADO. Net to obtain the value of a field in the first record. It is generally used for query by clustering functions.

 

Execute Query

Test (sqlitehelper, selectafterinsert)
{
Try
{
Cppsqlitedb;
DB. Open (db_file_name );
Cppsqlite3query q = db.exe cquery (L"Select * from T1 ;");

STD: String STR;
CharCh [255];
While(! Q. EOF ())
{
Sprintf (CH,"F1 = % d, f2 = % s, F3 = % s \ n", Q. getintfield (0), Q. getstringfield (1), Q. getstringfield (2 ));
STR + = CH;
Q. nextrow ();
}
Trace (Str. c_str ());
DB. Close ();
}
Catch(Cppsqlite3exception E)
{
Fail (tostring (E. errormessage (). c_str ());
}
}
 

Cppsqlite3query is used to retrieve the query results. The EOF () function determines whether or not it is finished. Nextrow () moves to the next record. The getintfield () and getstringfield () functions are the values of the specific fields in the current record.

 

Use transactions

Test (sqlitehelper, inserttablewithtransaction)
{
 Try
 {
Cppsqlitedb;
DB. Open (db_file_name );

Cstring sqlstr;
Time_t tmstart, tmend;
Tmstart = time (0 );
Db.exe cdml (L "Begin transaction ;" );
 For ( Int I = 0; I <Max; ++ I)
{
Systemtime currenttime;
Getlocaltime (& currenttime );
Sqlstr. Format (L "Insert into T1 (F1, F2, F3) values (% d, 'str % d', '% d-% d: % D ')" ,
I, I, currenttime. wyear, currenttime. wmonth, currenttime. wday, currenttime. whour, currenttime. wminute, currenttime. wsecond );
Db.exe cdml (sqlstr );
}
Db.exe cdml (L "Commit transaction ;" );
Tmend = time (0 );
 Char Ch [255];
Sprintf (CH, "Insert Table successful in % d seconds" , Tmend-tmstart );
Trace (CH );
DB. Close ();
}
 Catch (Cppsqlite3exception E)
{
Db.exe cdml (L "Rollback transaction ;" );
Fail (tostring (E. errormessage (). c_str ());
}
}

Transactions on SQLite are very simple to use. Use the execdml () function of cppsqlitedb to open, commit, and roll back transactions. In terms of transaction processing, SQLite is similar in syntax to ms SQL Server. By default, it is an automatic transaction (autocommit). I have written an article about transaction processing before.ArticleIf you are interested, please refer to the differences between ms SQL Server and Oracle for database transaction processing. You can also refer

Begin transaction. From the test results, the processing time of batch data is significantly different between explicit and automatic transactions.

When 100 pieces of data are inserted, the transaction is completed in less than 1 second explicitly, while automatic transactions take 4 seconds. Why is there such a big difference? I did not carefully study the source code of SQLite. I will explain from the general database concept that one of the features of a transaction is durability ), that is to say, all the data submitted for the transaction must be persistent, and the hard disk needs to be written for persistence. The mobile device is flash, the speed of writing permanent storage devices is far slower than the speed of writing memory, so the speed difference is at Io.

 

Unit Test

TDD is used in project development. For unit test, refer to unit test of native C ++ in Windows Mobile and cppunitlite in Windows Mobile.

About Projects

I moved the project host to codeplex. The project homepage link is as follows:

Sqlitehelper-native C ++ Wrapper class for SQLite on Windows Mobile & WinCE

The link for checking and downloading the latest version is as follows:

Http://sqlitehelper.codeplex.com/SourceControl/ListDownloadableCommits.aspx

 

If you like this project, ifCodePlease reply. I made a sqlcehelper last time. For more information, see the encapsulation of access to sqlce by native C ++ in Windows Mobile. One day I received a message that one person told me that he had not completed sqlce for a week after Google. After reading my article, I was very happy to help others.