Access native C ++ encapsulation of SQLite under WM/WinCE (use of cppsqlite3u)

Author: FIG Lin)

Address: http://www.cnblogs.com/procoder/archive/2009/10/19/1585733.html

Original article title: Access Native of Sqlite in Windows Mobile
C ++ Encapsulation

Background

In the current mobile device development field, Sqlite has almost become a de facto standard in terms of local data storage, 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, Windows
How can Mobile Miss Sqlite.

Introduction

Sqlite has almost become 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.
How to access Sqlite using Native C ++ on the Mobile platform, and how to implement and use an encapsulation class.

Sqlite source code

Sqlite source code can be downloaded to SQLite
Page download, I directly used sqlite.phxsoftware.com in Windows
Build project under Mobile.

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        {                CppSQLite3DB db;                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. The source code of Sqlite is as follows (see sqlite3.c ):

rc = openDatabase(zFilename8, ppDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);

Run DDL

TEST(SqliteHelper, CreateTable){        try        {                CppSQLite3DB db;                db.open(DB_FILE_NAME);                db.execDML(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, refer to Datatypes.
In SQLite Version 3.

Execute DML

TEST(SqliteHelper, InsertTable){        try        {                CppSQLite3DB db;                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 %d:%d:%d')", i, i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond);                        db.execDML(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         {                CppSQLite3DB db;                db.open(DB_FILE_NAME);                         int count = db.execScalar(L"SELECT COUNT(*) FROM T1;");                 char ch[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        {                CppSQLite3DB db;                db.open(DB_FILE_NAME);                CppSQLite3Query q = db.execQuery(L"SELECT * FROM T1;");                        std::string str;                char ch[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        {                CppSQLite3DB db;                db.open(DB_FILE_NAME);                        CString sqlStr;               time_t tmStart, tmEnd;                tmStart = time(0);                db.execDML(L"begin transaction;");                for(int i=0; i<max; ++i)                {                        SYSTEMTIME currentTime;                            GetLocalTime(¤tTime);                        sqlStr.Format(L"INSERT INTO T1 (F1, F2, F3) VALUES(%d, 'STR%d', '%d-%d-%d %d:%d:%d')", i, i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond);                        db.execDML(sqlStr);                }                db.execDML(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.execDML(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. Sqlite in terms of transaction processing, syntax and MS
SQL Server is a bit similar. The default is AutoCommit. I have written an article about transaction processing. If you are interested, refer to the following MS.
Differences between SQL Server and Oracle in database transaction processing. You can also refer to 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 details about Unit Test, refer to Wince and Windows.
Native C ++ unit test under Mobile and CppUnitLite for Windows Mobile output test results.
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