sqlite使用样例与utf编码

个人随笔 (Owed by: 春夜喜雨 http://blog.csdn.net/chunyexiyu)

提起sqlite，可谓是大名鼎鼎，作为一个单机文件数据库，一个易用的关系数据库，使用起来确实很方便。
另外sqlite存储采用的B+树结构，该结构会为表数据做页面汇聚，查询效率是非常高的。

关于如何来使用sqlite，总结了一些简单的使用样例。
数据库使用样例中，有打开数据库，创建表，插入数据，查询数据，这几种DDL与DML操作。
另外由于sqlite的接口限制约束，要求字符串使用utf编码格式，所以也会涉及编码使用的部分知识。
注：样例运行环境在linux下，缺省的LANG编码设置为zh_CN.UTF8

打开数据库

关注两种方式，一种是使用sqlite3_open打开数据，一种是使用sqlite3_open16打开数据库；
其中sqlite3_open比较适合在linux下使用，在linux缺省编码格式是utf8，适合接口参数需要，filename采用char类型的utf8字符串。
其中sqlite3_open16比较适合在windows下使用，适合打开可能带中文路径的数据库，filename采用char16_t类型的utf16字符串。
原因见接口使用说明中：

The encoding used for the filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8, not whatever codepage is currently defined.
Filenames containing international characters must be converted to UTF-8 prior to passing them into sqlite3_open() or sqlite3_open_v2().

函数说明：

SQLITE_API int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
SQLITE_API int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */ 
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

使用sqlite3_open的样例：

#include "sqlite3.h"
#include "stdio.h"
#include "string.h"
int main(int argc, char** argv)
{
  int rc = SQLITE_OK;
  sqlite3* db;
  const char* dbname = "./mydb.db";
  rc = sqlite3_open(dbname, &db);
  if (rc != SQLITE_OK){
    printf("open db<%s> failed<%s>\n", dbname, sqlite3_errmsg(db));
    sqlite3_close(db);
    return -1; 
  }
  printf("open db<%s> succ!\n", dbname);

  sqlite3_close(db);
  return 0; 
}

使用sqlite3_open16的样例：

#include "sqlite3.h"
#include "stdio.h"
#include <string>
#include <locale>
#include <codecvt>
int main(int argc, char** argv)
{
  int rc = SQLITE_OK;
  sqlite3* db;
  std::u16string str = u"./mydb.db";
  std::wstring_convert<std::codecvt_utf8_utf16<char16_t>, char16_t> myconv;
  std::string utf8str = myconv.to_bytes(str);
  const char* dbname = utf8str.c_str(); 
  rc = sqlite3_open16(str.c_str(), &db);
  if (rc != SQLITE_OK){
    printf("open db<%s> failed<%s>\n", dbname, sqlite3_errmsg(db));
    sqlite3_close(db);
    return -1; 
  }
  printf("open db<%s> succ!\n", dbname);
  sqlite3_close(db);
  return 0; 
}

创建表

创建表使用sqlite3_exec执行建表语句
函数说明

// The sqlite3_exec() interface is a convenience wrapper around [sqlite3_prepare_v2()], [sqlite3_step()], and [sqlite3_finalize()],
// that allows an application to run multiple statements of SQL without having to use a lot of C code. 
//  The sqlite3_exec() interface runs zero or more UTF-8 encoded, 
// semicolon-separate SQL statements passed into its 2nd argument, 
// in the context of the [database connection] passed in as its 1st argument.
SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

创建表的语句如下：

  // create table
  const char* sqlstr = "create table if not exists testtbl(id integer(4), name TEXT, other integer(4))";
  char* errmsg;
  rc = sqlite3_exec(db, sqlstr, 0, 0, &errmsg);  
  if (rc != SQLITE_OK){
    printf("execute create table failed<%s>\n", errmsg);
    sqlite3_free(errmsg);
    return -1;
  }
  printf("create table succ!\n");

插入数据

插入数据一般使用sqlite3_stmt结构准备数据，然后使用sqlite3_step提交数据，使用sqlite3_finalize清理stmt数据；
准备数据过程中，首先使用sqlite3_prepare创建绑定stmt，使用sqlite3_bind_int/text绑定数据。
// The life-cycle of a prepared statement object usually goes like this:
// 1. Create the prepared statement object using [sqlite3_prepare_v2()].
// 2. Bind values to [parameters] using the sqlite3_bind_*() interfaces.
// 3. Run the SQL by calling [sqlite3_step()] one or more times.
// 4. Reset the prepared statement using [sqlite3_reset()] then go back to step 2. Do this zero or more times.
// 5. Destroy the object using [sqlite3_finalize()].
当然了，插入数据也可以像在工具中一样，拼装insert sql语句，使用sqlite3_exec方式直接执行提交插入数据。
使用stmt方式提交数据时，接口中也会区分出utf8方式和utf16方式，utf8是缺省方式。
注意事项：
需要注意一点，无论使用utf8接口，还是utf16接口，对于sqlite来说，数据都是按照utf8存储的；
对于utf16而言，接口中内部做了向utf8的转换，内部也是最终走的utf8接口写入数据。

函数说明：

// The use of the UTF-8 interfaces is preferred, as SQLite currently does all parsing using UTF-8.  
// The UTF-16 interfaces are providedas a convenience.  
// The UTF-16 interfaces work by converting the input text into UTF-8, then invoking the corresponding UTF-8 interface.
// parameters:
// The second argument, "zSql", is the statement to be compiled, encoded as either UTF-8 or UTF-16.  
// The sqlite3_prepare(), sqlite3_prepare_v2(), and sqlite3_prepare_v3() interfaces use UTF-8, 
// and sqlite3_prepare16(), sqlite3_prepare16_v2(), and sqlite3_prepare16_v3() use UTF-16.
// The use of the new "vX" interface is recommended for new applications but the legacy interface will continue to be supported.
SQLITE_API int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare16_v2(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_blob64(sqlite3_stmt*, int, const void*, sqlite3_uint64, void(*)(void*));
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt*, int);
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
SQLITE_API int sqlite3_bind_text64(sqlite3_stmt*, int, const char*, sqlite3_uint64, void(*)(void*), unsigned char encoding);
SQLITE_API int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
SQLITE_API int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void(*)(void*));
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
SQLITE_API int sqlite3_bind_zeroblob64(sqlite3_stmt*, int, sqlite3_uint64);
// In the legacy interface, the return value will be either [SQLITE_BUSY], [SQLITE_DONE], [SQLITE_ROW], [SQLITE_ERROR], or [SQLITE_MISUSE].
// With the "v2" interface, any of the other [result codes] or [extended result codes] might be returned as well.
// [SQLITE_BUSY] means that the database engine was unable to acquire the database locks it needs to do its job.
// [SQLITE_DONE] means that the statement has finished executing successfully.
// If the SQL statement being executed returns any data, then [SQLITE_ROW] is returned each time a new row of data is ready for processing by the caller.
// [SQLITE_ERROR] means that a run-time error (such as a constraint violation) has occurred. 
// [SQLITE_MISUSE] means that the this routine was called inappropriately.
SQLITE_API int sqlite3_step(sqlite3_stmt*);
// The sqlite3_finalize() function is called to delete a [prepared statement].
SQLITE_API int sqlite3_finalize(sqlite3_stmt *pStmt);

插入数据的语句如下：

  // insert data
  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2(db, "insert into testtbl values(?1, ?2, ?3)", -1, &stmt, NULL);
  if (rc != SQLITE_OK){
     printf("prepare failed<%s>\n", sqlite3_errmsg(db)); 
    sqlite3_close(db);
     return -1;
  }
  const char *msg ="hello";
  sqlite3_bind_int(stmt, 1, 1);
  sqlite3_bind_text(stmt, 2, msg, strlen(msg), NULL);
  sqlite3_bind_int(stmt, 3, 2);
  rc = sqlite3_step(stmt);
  if (rc == SQLITE_ERROR){ //SQLITE_DONE, SQLITE_OK
    printf("execute write failed<%s>\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return -1;
  }
  sqlite3_finalize(stmt);  
  printf("write data succ!\n");

查询数据

查询数据和插入数据有类似的地方：
插入数据是通过接口准备数据-绑定插入的数据；
查询数据是通过接口获取数据-绑定查询到的数据；
都会使用stmt这个结构，使用的方式有一些差异
使用到的函数有sqlite3_prepare准备sql语句，使用sqlite3_step执行查询出一条语句，使用sqlite3_column_*读取字段，然后使用sqlite3_step尝试读取下一条数据。
注意事项：
我们已经知道了，对于sqlite来说，内部数据都是按照utf8存储的；对于utf16接口而言，接口中内部做了与utf8的转换；
所以呢，如果我们写查询语句时，无论按照utf8接口，还是utf16接口，内部都是按照utf8查询匹配数据的。
同样的，对于读取出来的数据，当我们使用sqlite3_column_text16方式时，得到的结果也是通过查询到的utf8数据，转码为utf16得到的。

使用到的函数：

SQLITE_API int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_step(sqlite3_stmt*);
SQLITE_API int sqlite3_finalize(sqlite3_stmt *pStmt);
SQLITE_API const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
SQLITE_API double sqlite3_column_double(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_int(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
SQLITE_API const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
SQLITE_API const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_type(sqlite3_stmt*, int iCol);

查询样例：

  const char* querymsg = "select * from testtbl where 1=1";
  sqlite3_stmt *querystmt;
  rc = sqlite3_prepare_v2(db, querymsg, -1, &querystmt, NULL);
  if (rc != SQLITE_OK){
    printf("query failed<%s>\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return -1;
  }
  do{
    rc = sqlite3_step(querystmt);
    if (rc != SQLITE_ROW){
      break;
    }
    int id = sqlite3_column_int(querystmt, 0);
    const unsigned char* name = sqlite3_column_text(querystmt, 1);
    int other = sqlite3_column_int(querystmt, 2);
    printf("record: %d|%s|%d|\n", id, name, other);
  }while(rc != SQLITE_DONE);
  sqlite3_finalize(querystmt);  
  printf("query data succ!\n");

That’s all~~~

个人随笔 (Owed by: 春夜喜雨 http://blog.csdn.net/chunyexiyu)