sqlite3学习

• 基本API和过程：
  

参考： http://www.blogjava.net/xylz/archive/2012/09/25/388519.html\

官网：http://www.sqlite.org/cintro.html

http://www.cnblogs.com/stephen-liu74/archive/2012/03/05/2340780.html

http://www.cnblogs.com/nbsofer/archive/2012/05/02/2479168.html

http://www.cnblogs.com/memset/archive/2012/05/02/2479156.html

例子程序：

#include <stdio.h>
#include <assert.h>
#include <sqlite3.h>

int main()
{
    int ret;
    sqlite3* pDb = NULL;
    char* pErrMsg = NULL;
    printf("Open db\n");
    ret = sqlite3_open("./my.db", &pDb);//Open the db file
    assert(SQLITE_OK == ret);

    printf("Clear data\n");
    sqlite3_exec(pDb, "drop table if exists myTable\n", NULL, NULL, NULL);
    
    printf("Create table\n");
    ret = sqlite3_exec(pDb, "create table if not exists myTable(id integer primary key, name text)", NULL, NULL, &pErrMsg);//Create a table
    if (SQLITE_OK != ret) printf("%s\n", pErrMsg);
    assert(SQLITE_OK == ret);
    sqlite3_free(pErrMsg);//TODO:ÊÇ·ñҪÿ´Îsqlite3_exec¶¼free¶ø²»ÊÇÖ»freeÒ»´Î?
    
    printf("Insert some lines\n");
    ret = sqlite3_exec(pDb, "insert into myTable(id,name) values(2,'2222222')", NULL, NULL, &pErrMsg);
    ret = sqlite3_exec(pDb, "insert into myTable(id,name) values(3,'3333333')", NULL, NULL, &pErrMsg);
    ret = sqlite3_exec(pDb, "insert into myTable(id,name) values(11,'11111')", NULL, NULL, &pErrMsg);
    //if (SQLITE_OK != ret) printf("%s\n", pErrMsg);
    assert(SQLITE_OK == ret);
    
    printf("Insert a line that contained ' \n");
    char* sql = sqlite3_mprintf("insert into myTable(id,name) values(14,'%q')", "luo'jiao");
    printf("sql: %s\n", sql);
    ret = sqlite3_exec(pDb, sql, NULL, NULL, NULL);
    assert(SQLITE_OK == ret);
    sqlite3_free(sql);
    
    printf("Delete some lines\n");
    //ret = sqlite3_exec(pDb, "delete from myTable where id < 10", NULL, NULL, &pErrMsg);

    printf("Use prepare&step to insert a line\n");
    sqlite3_stmt *stmt;
    ret = sqlite3_prepare_v2(pDb, "insert into myTable(id,name) values(?,?)", -1, &stmt, NULL);
    assert(SQLITE_OK == ret);
    for (int i = 30; i < 33; i++)
    {
        sqlite3_bind_int(stmt, 1, i);
        char text[50] = {0};
        sprintf(text, "prepare line id%d", i);
        sqlite3_bind_text(stmt, 2, text, -1, NULL);
        sqlite3_step(stmt);
        sqlite3_reset(stmt);
    }
    sqlite3_finalize(stmt);
    assert(SQLITE_OK == ret);

    printf("Select some data from table, result:\n");
    sqlite3_stmt *stmt1;
    const char* sql1 = "select * from myTable where id > 10";
    printf("Sql: select * from myTable where id > 10\nid  name\n");
    ret = sqlite3_prepare_v2(pDb, sql1, -1, &stmt1, NULL);
    assert(SQLITE_OK == ret);
    while (SQLITE_ROW == sqlite3_step(stmt1))
    {
        int id = sqlite3_column_int(stmt1, 0);
        const unsigned char* pName = sqlite3_column_text(stmt1, 1);
        printf("%-4d%s\n", id, pName);
    }
    ret = sqlite3_finalize(stmt1);
    assert(SQLITE_OK == ret);

    sqlite3_close(pDb);
    return 0;
}

• 基本SQL语句
  

更新：UPDATE myTable SET address = 'Nanjing', name = 'luojiao'WHEREid = 1;

复合插入：

INSERT OR REPLACE：如果不存在就插入，存在就更新
INSERT OR IGNORE：如果不存在就插入，存在就忽略

模糊匹配：用like替代等号并使用通配符%，例如： select * from mytable where id like '1111%';  select * from mytable where name like 'line%';

like 使用%代表任何字符
like 使用?代表单个字符
matches使用*代表任何字符
matches使用_代表单个字符
例如：
select * from t_test where name matches "*"

• 根据表创建新表

举例：

CREATE TABLE newTable AS SELECT id, name FROM existedTable WHERE id < 10;

• 触发器（Trigger）：

参考http://blog.csdn.net/dliyuedong/article/details/40565407

CREATE TRIGGER < [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] >
ON <tableName>
FOR EACH ROW //可选，不写也一样
BEGIN
--do something; //记得句末一定要加分号
END ;

例如：

CREATE TRIGGER myTrigger1 after delete on myTable

BEGIN

    delete from relatedTable where relatedTable.id = old.id;

END;

其中前面作用表的新旧数据使用new和old进行指向，例如insert时new指向插入的行数据，delete时old指向删除的行数据，update时类推。

注意：sqlite3的触发器语法跟SqlServer等大型数据库不太一样，参照SqlServer的方式来写会无法执行，例如不需要关键字as，也不能用关键字for（只能用before/after），一定要有begin/end，一定要do something语句后加分号

• 虚拟表virtual table
  

参考官网：http://www.sqlite.org/vtab.html

create/drop table时，调用xCreate/xConnect和xDestroy/xDisconnect，在这四个函数里实现表的创建销毁。

select时，xBestIndex、xOpen、xFilter、xEof、xColumn、xRowid、xNext、xClose配合使用，调用顺序就是我列的顺序。

xBestIndex 用来处理SQL语句中的WHERE/GROUP BY/ORDER BY这样的约束条件的。入参是sqlite3_index_info中的 /* Inputs */ 部分，出参是sqlite3_index_info中的 /* Outputs */ 部分。每一个WHERE约束条件对应一个aConstraint结构，每一个ORDER  BY等对应一个aOrderBy结构。

解析完成后，如果希望哪个约束条件aConstraint[i]被传递到XFilter的argv[n]，那就填写aConstraintUsage[i].argvIndex = n;

idxNum和idxStr的含义自己定义，会传给xFilter，含义由使用者自定，比较灵活

XFilter 用来进行行选择，根据xBestIndex传过来的条件，选出符合条件的行。

例子代码： 每次执行前要把my.db或里面的virTable表删除，否则会创建失败。加了if not exists会失败。

#include <stdio.h>
#include <assert.h>
#include <string.h>
#include <sqlite3.h>
//#include <sqlite3ext.h>

/************************* VT的数据库表信息 *************************/
/*这里建一个最简单的数据库table: 
vtable(id int, name text)
       1,      aaa
       2,      bbb
       3,      ccc
       4,      ddd
       5,      eee
*/
const int DATA_COUNT = 5;//table大小即行数
static int gIDinTable[DATA_COUNT] = {1,2,3,4,5};//table中的id列数据
const int NAME_LENGTH = 10;
static char gNameinTable[DATA_COUNT][NAME_LENGTH] = {"aaa","bbb","ccc","ddd","eee"};//table中的name列数据
static int curPos = 0;//当前cursor
/************************* VT的数据库表信息 *************************/

static int MyDestructor(sqlite3_vtab *pVtab) // finished: 100%
{
    printf("MyDestructor\n");
    return SQLITE_OK;
}

static int MyCreate( sqlite3 *db, void *p_aux, int argc, const char * const* argv , sqlite3_vtab **pp_vt,   char **pzErr ) // finished: 100%
{
    printf("MyCreate\n");
    int ret = sqlite3_declare_vtab(db, "create table vtable(id int, name text)");
    if (SQLITE_OK != ret)
    {
        return SQLITE_ERROR;
    }
    sqlite3_vtab* pVtab = (sqlite3_vtab*)sqlite3_malloc(sizeof(sqlite3_vtab));
    *pp_vt = (sqlite3_vtab *)pVtab;
    return SQLITE_OK;
}

static int MyConnect( sqlite3 *db, void *p_aux, int argc, const char * const *argv, sqlite3_vtab **pp_vt, char **pzErr ) // finished: 100%
{
    printf("MyConnect\n");
    return MyCreate(db, p_aux, argc, argv, pp_vt, pzErr);
    return SQLITE_OK;
}

static int MyBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo) // finished: 100%
{
    printf("MyBestIndex\n");
    //TODO: 这里的aConstraintUsage是谁申请的内存? 应该是只有select语句有条件约束这个就自动申请了?需要看下
    //pIdxInfo->aConstraintUsage[0].argvIndex = 1;
    return SQLITE_OK;
}

static int MyDisconnect(sqlite3_vtab *pVtab) // finished: 100%
{
    printf("MyDisconnect\n");
    sqlite3_free(pVtab);
    return SQLITE_OK;
}

static int MyDestroy(sqlite3_vtab *pVtab) // finished: 100%
{
    printf("MyDestroy\n");

    return MyDisconnect(pVtab);;
}

static int MyOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **pp_cursor) // finished: 100%
{
    printf("MyOpen\n");
    sqlite3_vtab_cursor* pCursor = (sqlite3_vtab_cursor*)sqlite3_malloc(sizeof(sqlite3_vtab_cursor));
    if (pCursor == NULL)
    {
        return SQLITE_NOMEM;
    }
    *pp_cursor = pCursor;
    //严格来说，这里应该扩展sqlite3_vtab_cursor来包含curPos，作为cursor的记录。例子就从简了,用全局变量
    curPos = 0;//Open时把cursor初始化
    
    return SQLITE_OK;
}

static int MyClose(sqlite3_vtab_cursor *cur) // finished: 100%
{
    printf("MyClose\n");
    if (cur)
    {
        sqlite3_free(cur);
    }
    return SQLITE_OK;
}

static int MyFilter( sqlite3_vtab_cursor *p_vtc, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ) // finished: 80%
{
    printf("MyFilter\n");
    //根据条件来筛选出符合条件的行
    return SQLITE_OK;
}

static int MyNext(sqlite3_vtab_cursor *cur) // finished: 100%
{
    printf("MyNext\n");
    curPos++;//Next时把cursor递增。严格说应该是cur结构体中带过来筛选条件，在这里根据筛选条件找Next，但例子就从简了
    return SQLITE_OK;
}

static int MyEof(sqlite3_vtab_cursor *cur) // finished: 100%
{
    printf("MyEof\n");
    //严格说应该是判断，符合cur结构体中带过来筛选条件的行是否结束，但例子就从简了
    if (curPos >= DATA_COUNT)
    {
        return 1;
    }
    else
    {//0 ~ DATA_COUNT-1
        return 0;
    }
}

static int MyColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int column) // finished: 80%
{
    printf("MyColumn\n");
    switch (column)

    {
    case 0://id
        sqlite3_result_int(ctx, gIDinTable[curPos]);
        break;
    case 1://name
        sqlite3_result_text(ctx, gNameinTable[curPos], strlen(gNameinTable[curPos]), SQLITE_STATIC);
        break;
    default:
        break;
    }
    return SQLITE_OK;
}

static int MyRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *p_rowid) // finished: 100%
{
    printf("MyRowid\n");
    *p_rowid = (sqlite_int64)curPos;
    return SQLITE_OK;
}

static int MyUpdate(sqlite3_vtab *pVTab,int argc,sqlite3_value **argv,sqlite_int64 *pRowid)
{
    printf("MyUpdate\n");
    return SQLITE_OK;
}

static int MyFindFunction( sqlite3_vtab *pVtab, int nArg,const char *zName,
                             void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
                             void **ppArg )
{
    printf("MyFindFunction\n");
    return SQLITE_OK;
}

static sqlite3_module myModule =
{
    1, /* iVersion */
    MyCreate, /* xCreate - create a vtable */
    MyConnect, /* xConnect - associate a vtable with a connection */
    MyBestIndex, /* xBestIndex - best index */
    MyDisconnect, /* xDisconnect - disassociate a vtable with a connection */
    MyDestroy, /* xDestroy - destroy a vtable */
    MyOpen, /* xOpen - open a cursor */
    MyClose, /* xClose - close a cursor */
    MyFilter, /* xFilter - configure scan constraints */
    MyNext, /* xNext - advance a cursor */
    MyEof, /* xEof - inidicate end of result set*/
    MyColumn, /* xColumn - read data */
    MyRowid, /* xRowid - read data */
    MyUpdate, /* xUpdate - write data */
    NULL, /* xBegin - begin transaction */
    NULL, /* xSync - sync transaction */
    NULL, /* xCommit - commit transaction */
    NULL, /* xRollback - rollback transaction */
    MyFindFunction, /*  xFindFunction - function overloading */
    NULL,
    NULL,
    NULL,
    NULL
};

int main()
{
    int ret;
    sqlite3* pDb = NULL;
    printf("Open db\n");
    ret = sqlite3_open("./my.db", &pDb);//Open the db file
    assert(SQLITE_OK == ret);

    //Create virtual table
    ret = sqlite3_create_module(pDb, "MyVT", &myModule, NULL);
    assert(SQLITE_OK == ret);
    printf("Create virtual table\n");
    ret = sqlite3_exec(pDb, "create virtual table virTable using MyVT", NULL, NULL, NULL);//Create a table
    assert(SQLITE_OK == ret);

    //Execute a select clause
    ret = sqlite3_exec(pDb, "select * from virTable", NULL, NULL, NULL);//Create a table
    assert(SQLITE_OK == ret);

    //Execute a select clause and get result
    sqlite3_stmt *stmt;
    ret = sqlite3_prepare_v2(pDb, "select * from virTable", -1, &stmt, NULL);
    printf("id  name\n");
    while (SQLITE_ROW == sqlite3_step(stmt))
    {
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char* pName = sqlite3_column_text(stmt, 1);//pName会被sqlite3自动释放，不用外界释放
        printf("%-4d %s\n", id, pName);
    }
    sqlite3_finalize(stmt);
    assert(SQLITE_OK == ret);

    sqlite3_close(pDb);
    return 0;
}

• rowid
  

SQL默认有一个rowid列，int64格式，这个列是自动生成的，一般是递增的，不用去指定。可以select语句把它显示出来。

• 效率提升

BEGIN TRANSACTION - COMMIT ：对于Insert和Update这样的有IO的操作，如果每个语句都单独执行，大量操作时就会有大量的IO操作，会非常慢。例如Insert 10000条数据会耗费十几秒的时间。

加速方法： 在批量语句前加 BEGIN TRANSACTION, 之后加 COMMIT, 这样所有的IO操作会被缓存，到COMMIT时统一执行只进行一次IO操作。效果非常明显，几百倍的速度提升。

还有一个"PRAGMA synchronous = OFF"语句，但测试过不起作用，速度没加快。