sqlite3的简单使用

项目中用到了sqlite3，它作为一个小巧高效的基于文本的数据库，与微软的Access差不多，简单记录下用法

1.删除记录

DELETE_ONE_RECORD_SQL "delete from video_record where id =%d"

bool HistoryManager::deleteOneRecord(int id)
{
    char sql[512];
    int rc;
    sprintf(sql,DELETE_ONE_RECORD_SQL,id);
    rc = sqlite3_exec(db,sql,NULL,NULL,&errorMessage);
    if(rc == SQLITE_OK)
    {
        printf("exec sql:%s==>success\n",sql);
        return true;
    }
    printf("exec sql:%s==>failure\n",sql);
    return false;
}

2.查询记录

#define SELECT_FROM_SQL "select * from video_record limit ? offset ?" //limit 范围 offset 超时


History_Video_Record_Node* HistoryManager::getRecords(int startIndex,int endIndex)
{
    //releaseNode(videoRecordHead);

    int rc;
    int offset = 0;
    int ncols = 0;
    History_Video_Record_Node* temp = NULL;
    History_Video_Record_Node* next = NULL;
    
    offset = startIndex -1 < 0 ? 0 : startIndex -1;
    sqlite3_stmt* stmt = NULL;
    const char* tail = NULL;
    rc = sqlite3_prepare(db,SELECT_FROM_SQL,strlen(SELECT_FROM_SQL),&stmt,&tail); //

    if(rc != SQLITE_OK)
        return NULL;
    
    rc = sqlite3_bind_int(stmt,1,endIndex - startIndex + 1 > 0 ? endIndex - startIndex + 1 : 0);//limit

    if(rc != SQLITE_OK)
        return NULL;
    rc = sqlite3_bind_int(stmt,2,offset); //offset 绑定数据?

    if(rc != SQLITE_OK)
        return NULL;
        
    rc = sqlite3_step(stmt); //保存到结构stmt中

    if(rc == SQLITE_ROW)
    {
        printf("exec sql:%s ========>success\n",SELECT_FROM_SQL);
    }
    releaseNode(videoRecordHead);
    ncols = sqlite3_column_count(stmt);
    if(ncols <= 0)
        return NULL;
        
    printf("HERE %d\n",ncols);
    while(rc == SQLITE_ROW) //构建History_video_Record_Node 链表

    {
        temp = new History_Video_Record_Node;
        memset(temp,0,sizeof(History_Video_Record_Node));
        if(next)
        {
            next->next = temp;
        }
        next = temp;
        
        if(!videoRecordHead)
            videoRecordHead = temp;
        for(int i = 0 ; i < ncols ; i++)
        {
            if(strcasecmp(sqlite3_column_name(stmt,i),"id") == 0)//定位到id列

            {
                if(sqlite3_column_text(stmt,i))//获取id的值

                {
                    memcpy(temp->id,(const char*)sqlite3_column_text(stmt,i),strlen((const char*)sqlite3_column_text(stmt,i)));
                }
            }
            if(strcasecmp(sqlite3_column_name(stmt,i),"name") == 0)
            {
                if(sqlite3_column_text(stmt,i))
                {
                    memcpy(temp->name,(constchar*)sqlite3_column_text(stmt,i),strlen((const char*)sqlite3_column_text(stmt,i)));
                }
            }
            if(strcasecmp(sqlite3_column_name(stmt,i),"url") == 0)
            {
                if(sqlite3_column_text(stmt,i))
                {
                    memcpy(temp->url,(const char*)sqlite3_column_text(stmt,i),strlen((const char*)sqlite3_column_text(stmt,i)));
                }
            }
            if(strcasecmp(sqlite3_column_name(stmt,i),"time") == 0)
            {
                if(sqlite3_column_text(stmt,i))
                {
                    memcpy(temp->time,(const char*)sqlite3_column_text(stmt,i),strlen((const char*)sqlite3_column_text(stmt,i)));
                }
            }
        }
        rc = sqlite3_step(stmt);
    }

    sqlite3_finalize(stmt); //析构stmt

    return videoRecordHead; //返回链表头结点

    

3.增加记录

#define INSERT_INTO_SQL "insert into video_record(name,url) values(\"%s\",\"%s\")"
bool HistoryManager::addOneRecord(History_Video_Record_Node* node)
{
    if(!node)
        return false;
    char sql[512];
    int rc;
    sprintf(sql,INSERT_INTO_SQL,node->name,node->url);
    rc = sqlite3_exec(db,sql,NULL,NULL,&errorMessage);
    if(rc == SQLITE_OK)
    {
        printf("exec sql:%s==>success\n",sql);
        return true;
    }
    printf("exec sql:%s==>failure error = %s\n",sql,errorMessage);
    return false;
    
}

4.创建数据库

#define CREATE_TABLE_SQL "create table video_record(id INTEGER PRIMARY KEY,name TEXT NOT NULL,url TEXT,time default(date('now')))"

bool HistoryManager::loadDB(const char* dbName)
{
    int rc;
    printf("before sqlite open\n");
    rc = sqlite3_open(dbName, &db);
    printf("after sqlite open\n");
    if( rc )
        {
            fprintf(stderr, "Can't open database:%s\n",sqlite3_errmsg(db));
            return false;
        }
    printf("before sqlite3_exec open\n");
    rc = sqlite3_exec(db,CREATE_TABLE_SQL,NULL,NULL,&errorMessage);
    printf("after sqlite3_exec open\n");
    if(rc == SQLITE_OK)
    {
        printf("the table video_record has not existed,create a newone\n");
    }
    else
        printf("the table video_record has existed!\n");
    return true;
}