SQLite学习笔记

1、sqlite3_open系列函数：

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

【功能简述】：
打开一个数据库连接（若指定数据库名不存在，则创建一个新的数据库），并返回数据库连接句柄。
【参数说明】:
（1）sqlite3_open()和sqlite3_open_v2()以UTF-8编码格式解释filename参数，sqlite3_open16()以UTF-16（本地字节序，即大端还是小断视本地主机而定）解释filename参数。
（2）通过ppDb参数返回sqlite3对象指针（也称为数据库连接句柄），若SQLite为sqlite3对象分配内存失败，则ppDb返回为NULL。
（3）其它参数详情参见http://www.sqlite.org/c3ref/open.html。
【返回值】：
若打开（或创建）数据库成功，则返回SQLITE_OK，否则返回错误代号（error code），通过sqlite3_errmsg()或sqlite3_errmsg16()函数可以获取错误信息。
【附加说明】：
（1）使用sqlite3_open()和sqlite3_open_v2()打开或创建的数据库，默认以UTF-8格式编码，而使用sqlite3_open16()打开或创建的数据库以UTF-16格式的本地字节序形式编码。
（2）无论sqlite3_open()系列函数是否成功打开或创建数据库，都应该在不需要的时候调用sqlite3_close()来释放数据库资源。

2、sqlite3_prepare系列函数：

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 */
);
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 */
);
int sqlite3_prepare16(
  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 */
);
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 */
);

【功能简述】：
编译一条SQL语句，并返回
【参数说明】：
（1）参数db为sqlite3_open系列函数打开的数据库连接句柄。
（2）参数zSql用来指定需要编译的SQL语句。
（3）参数nByte用来指定一次编译SQL语句最大长度（若SQL语句长度超过nByte，则会被截断，之编译前nByte个字节），若nByte小于0，则编译长度为zSql语句的实际长度（strlen(zSql)）。
（4）参数ppStmt返回编译后的prepared statement对象，供后续sqlite3_step函数使用，若编译失败则返回NULL。
（5）参数pzTail若不为NULL，则返回截断后为编译的部分SQL语句的指针。
【返回值】：
编译成功返回SQLITE_OK，否则返回相应错误代号。
【附加说明】：
使用sqlite3_prepare系列函数编译、执行后，若不再使用编译语句，需要调用sqlite3_finalize函数删除编译语句。

3、sqlite3_bind系列函数：

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

【功能简述】：
绑定实际值到编译语句。
【参数说明】：
（1）第一个参数为prepared statement对象。
（2）第二个参数为SQL语句参数索引，如果是模板？NNN形式的SQL语句，则为对应的NNN值。其它模板形式请参考http://www.sqlite.org/c3ref/bind_blob.html。
（3）第三个参数为绑定值。
（4）第四个参数为绑定字节数，对sqlite3_bind_text()和sqlite3_bind_text16()，指定为负数则根据字符串长度决定。
（5）第五个参数为析构函数，对sqlite3_bind_text()和sqlite3_bind_text16()来说，若绑定字节内存不是在堆上分配的，可以使用指定为SQLITE_STATIC，即0，也就是说不许要free。

4、sqlite3_step函数：

int sqlite3_step(sqlite3_stmt*);

【功能简述】：
执行编译后的SQL语句。
【参数说明】：
编译后的prepared statement对象。
【返回值】：
（1）SQLITE_DONE：若成功执行create、update、delete等一次性可以完成的SQL语句。
（2）SQLITE_ROW：若执行查询语句，可能有多行结果，每次执行返回一行（查询完最后一行后再次调用返回SQLITE_DONE）。
（3）SQLITE_ERROR：运行出错。
（4）SQLITE_MISUSE：可能是由于prepared statement对象已经被删除，或者先前返回了SQLITE_ERROR或SQLITE_DONE。
（5）SQLITE_BUSY：数据库正忙（不能获得数据库锁），若执行的是COMMIT语句或这发生在explicit transaction之外，则可以重试，否则应该先回滚事物，再继续。（暂时不能完全理解这一点）

5、sqlite3_column系列函数：

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

【功能简述】：
获取记录字段值。
【参数说明】：
（1）第一个参数为prepared statement对象。
（2）第二个参数为字段索引（最左边字段为0）。

6、sqlite3_reset函数

int sqlite3_reset(sqlite3_stmt *pStmt);

【功能简述】：
重置prepared statement对象到初始状态。
【参数说明】：略。
【返回值】：略。

7、sqlite3_finalize函数

int sqlite3_finalize(sqlite3_stmt *pStmt);

 【功能简述】：
删除编译语句。

8、sqlite3_close函数

int sqlite3_close(sqlite3*);
int sqlite3_close_v2(sqlite3*);

【功能简述】：
删除数据库连接句柄，释放所有资源。

9、完整示例

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

#define SUCCESS 1
#define FAILED -1
#define DATABASE_NAME    "data.db"

struct sqlite3 *g_pdb;

int OpenDatabase();
void CloseDatabase();
int CreateTable();
void CreateNewUser(void* name, int* sex, int* age, void* email);
int InsertData(unsigned int count);
int QueryUserBetweenAge(int min, int max);

int main()
{
    if ( OpenDatabase() == FAILED )
        return 1;
    
    if ( CreateTable() == FAILED )
    {
        CloseDatabase();
        return 1;
    }

    // insert 100 records
    if ( InsertData(100) == FAILED )
    {
        CloseDatabase();
        return 1;
    }
    
    // query users whose age in range 18 to 30
    QueryUserBetweenAge(18, 30);
    
    CloseDatabase();
    return 0;
}

// open database, if not existed then create a new one
int OpenDatabase()
{
    printf("Try to open database.\n");
    int ret = sqlite3_open(DATABASE_NAME, &g_pdb);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: sqlite3_open() failed, error infomation: %s\n", sqlite3_errmsg(g_pdb));
        CloseDatabase();
        return FAILED;
    }
    printf("Open database success.\n");
    return SUCCESS;
}

void CloseDatabase()
{
    printf("Close database.\n");
    sqlite3_close(g_pdb);
}

int CreateTable()
{
    printf("Try to create a table.\n");
    
    const char* pSql = "create table if not exists USER_LIST (name char(16), sex integer, age integer, email char(32))";
    
    // compile create table statement
    sqlite3_stmt *pStmt;
    int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: compile create table statement failed: %s\n", sqlite3_errmsg(g_pdb));
        return FAILED;
    }
    printf("Compile create table statement success.\n");
    
    // execute create table statement
    ret = sqlite3_step(pStmt);
    if (ret != SQLITE_DONE)
    {
        fprintf(stderr, "Error: execute create table statement faild\n");
        sqlite3_finalize(pStmt);
        return FAILED;
    }
    
    // delete create table statement
    sqlite3_finalize(pStmt);
    printf("Create table success\n");
    return SUCCESS;
}

int InsertData(unsigned int count)
{
    printf("Try to inesrt %d data.\n", (int)count);
    
    const char* pSql = "insert into USER_LIST (name, sex, age, email) values (?1, ?2, ?3, ?4)";

    // compile insert statement
    sqlite3_stmt *pStmt;
    int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: compile insert statement failed: %s\n", sqlite3_errmsg(g_pdb));
        return FAILED;
    }
    printf("Compile insert statement success.\n");
    
    int i, sex, age;
    char name[4] = {0};
    char email[12] = {0};
    for (i = 0; i < count; i++)
    {
        CreateNewUser((void*)name, &sex, &age, (void*)email);
        //printf("[Name]: %s, [Sex]: %s, [Age]: %d, [Email]: %s\n", name, sex % 2 ? "male" : "female", age, email);
        sqlite3_bind_text(pStmt, 1, name, -1, SQLITE_STATIC);
        sqlite3_bind_int(pStmt, 2, sex);
        sqlite3_bind_int(pStmt, 3, age);
        sqlite3_bind_text(pStmt, 4, email, -1, SQLITE_STATIC);
        
        ret = sqlite3_step(pStmt);
        if (ret != SQLITE_DONE)
        {
            fprintf(stderr, "Error: execute insert statement %d faild\n", i);
            continue;
        }
        sqlite3_reset(pStmt);
    }
    
    // delete insert statement
    sqlite3_finalize(pStmt);
    printf("Insert data over\n");
    return SUCCESS;
}

void CreateNewUser(void*name, int* sex, int* age, void *email)
{
    ((char*)name)[0] = rand() % 25 + 'A';
    ((char*)name)[1] = rand() % 25 + 'A';
    ((char*)name)[2] = rand() % 25 + 'A';
    *sex = rand() % 2;       // 1: male, 0: female
    *age = rand() % 100 + 1; // age range 1-100
    ((char*)email)[0] = ((char*)name)[0] | 0x20; // convert case to lowercase
    ((char*)email)[1] = ((char*)name)[1] | 0x20; // convert case to lowercase
    ((char*)email)[2] = ((char*)name)[2] | 0x20; // convert case to lowercase
    ((char*)email)[3] = '@';
    ((char*)email)[4] = rand() % 10 + '0';
    ((char*)email)[5] = rand() % 10 + '0';
    ((char*)email)[6] = rand() % 10 + '0';
    ((char*)email)[7] = '.';
    ((char*)email)[8] = 'c';
    ((char*)email)[9] = 'o';
    ((char*)email)[10] = 'm';
    ((char*)email)[11] = '\0';
}

int QueryUserBetweenAge(int min, int max)
{
    printf("Try to query users whose age is between %d and %d.\n", min, max);
    
    char pSql[128] = {0};
    sprintf(pSql, "select * from USER_LIST where age > %d and age < %d order by age, name asc", min - 1, max + 1);
    
    // compile select statement
    sqlite3_stmt *pStmt;
    int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: compile select statement failed: %s\n", sqlite3_errmsg(g_pdb));
        return FAILED;
    }
    printf("Compile select statement success.\n");
    
    // execute select statement
    int count = 0;
    printf(" ID\t Name\t Sex\t Age\t Email\n");
    printf("------------------------------------------------------\n");
    while (sqlite3_step(pStmt) == SQLITE_ROW) // return SQLITE_DONE after last row
    {
        // display query result
        printf("%3d\t ", ++count);
        printf("%s\t ", sqlite3_column_text(pStmt, 0));
        printf("%s\t ", sqlite3_column_int(pStmt, 1) > 0 ? "male" : "female");
        printf("%d\t ", sqlite3_column_int(pStmt, 2));
        printf("%s\n", sqlite3_column_text(pStmt, 3));
    }
    printf("------------------------------------------------------\n");
    printf("Total: %d\n", count);
    
    // delete select statement
    sqlite3_finalize(pStmt);
    printf("Query data success\n");
    return SUCCESS;
}