嵌入式Linux之我行——嵌入式数据库sqlite在2440上的应用

嵌入式Linux之我行，主要讲述和总结了本人在学习嵌入式linux中的每个步骤。一为总结经验，二希望能给想入门嵌入式Linux的朋友提供方便。如有错误之处，谢请指正。

• 共享资源，欢迎转载：http://hbhuanggang.cublog.cn

一、开发环境

• 主  机：Fedora 9
• 编译器：arm-linux-gcc-3.4.1
• 编辑器： Eclipse
• 开发板：Mini2440
• 数据库：sqlite-3.6.16.tar.gz

二、示例代码

   本示例代码是嵌入式CGI WEB应用程序中的一部分，主要实现了用户的登录和用户的验证功能。代码结构分为三层，分别是数据库操作层、数据访问层、业务逻辑层。

1. 数据库操作层

sqlitedb.h

/*
 ============================================================================
 Name                : sqlitedb.h
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 21/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#ifndef SQLITEDB_H_
#define SQLITEDB_H_

#include <sqlite3.h>  //这个是移植sqlite数据库时编译生成的头文件，在移植篇里提到过
#include <stdio.h>
#include <string>

using namespace std;

/*
 *数据集类
 */
class Result
{
public:
    Result();
    ~Result();
    int getRowNum();            //获取数据集行数

    int getColNum();            //获取数据集列数

    int getResultNum();         //获取数据集记录行数

    char *getAt(int r, int c);  //获取数据集一条记录

    friend class SQLiteDB;
    
private:
    int row;
    int col;
    char **result;
};

class SQLiteDB
{
public:
    SQLiteDB();
    ~SQLiteDB();
    bool openDB(const char *db_name);        //打开数据库

    bool execute(const char *sql);           //执行SQL语句，无返回记录的

    Result* executeSelect(const char *sql);  //执行SQL语句，有返回记录的

    bool closeDB();                          //关闭数据库

private:
    sqlite3 *pDB;
    char *pErrMsg;
};

#endif /*SQLITEDB_H_*/

sqlitedb.cpp

/*
 ============================================================================
 Name                : sqlitedb.cpp
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 21/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#include "sqlitedb.h"

Result::Result()
{
    row = 0;
    col = 0;
    result = NULL;
}

Result::~Result()
{
    sqlite3_free_table(result);
}

int Result::getRowNum()
{
    return row + 1;
}

int Result::getColNum()
{
    return col;
}

int Result::getResultNum()
{
    return row;
}

char *Result::getAt(int r, int c)
{
    if(r <= row + 1 && c <= col)
    {
        return result[r * col + c];
    }
    else
    {
        return NULL;
    }
}


SQLiteDB::SQLiteDB()
{
    pDB = NULL;
    pErrMsg = NULL;
}

SQLiteDB::~SQLiteDB()
{
    if(pDB != NULL)
    {
        this->closeDB();
    }
}

bool SQLiteDB::openDB(const char *db_name)
{
    int res = 0;
    
    res = sqlite3_open(db_name, &pDB);
    
    if(res != SQLITE_OK)
    {
        return false;
    }
    
    return true;
}

bool SQLiteDB::execute(const char *sql)
{
    if(pDB == NULL)
    {
        return false;
    }
    
    sqlite3_exec(pDB, sql, 0, 0, &pErrMsg);
    
    if(pErrMsg != NULL)
    {
        pErrMsg = NULL;
        
        return false;
    }
    
    return true;
}

Result* SQLiteDB::executeSelect(const char *sql)
{
    if(pDB == NULL)
    {
        return NULL;
    }
    
    Result *rs = new Result();
    sqlite3_get_table(pDB, sql, &rs->result, &rs->row, &rs->col, &pErrMsg);
    
    if(pErrMsg != NULL)
    {
        pErrMsg = NULL;
        
        return NULL;
    }
    
    return rs;
}

bool SQLiteDB::closeDB()
{
    int res = 0;
    
    if(pDB != NULL)
    {
        res = sqlite3_close(pDB);
        
        if(res == SQLITE_BUSY)
        {
            return false;
        }
    }
    
    return true;
}


  2. 数据访问层

user_DAL.h

/*
 ============================================================================
 Name                : user_DAL.h
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 29/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#ifndef USER_DAL_H_
#define USER_DAL_H_

#include <sstream>
#include <string.h>
#include "sqlitedb.h"
#include "system_DAL.h"

using namespace std;

#define SQLITE_DATABASE_PATH    "/usr/bin/ems_service.db"    //数据库路径

class User_DAL
{
public:
    User_DAL();
    ~User_DAL();
    
    bool user_Login(char *name, char *pass, const char *sid); //用户登录

    int user_Validate(char *sid);                             //验证用户

    string get_CurrentTime();                                 //获取系统时间

private:
    typedef enum
    {
        LOG_OFF    = 0,
        LOG_ON    = 1
    } USER_STATE;        //用户登录状态

    SQLiteDB sqliteDB;
};

#endif /*USER_DAL_H_*/


user_DAL.cpp

/*
 ============================================================================
 Name                : user_DAL.cpp
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 29/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#include "user_DAL.h"

User_DAL::User_DAL(){}

User_DAL::~User_DAL(){}

bool User_DAL::user_Login(char *name, char *pass, const char *sid)
{
    bool ret = false;
    Result *result = NULL;
    
    ostringstream sql;
    sql << "SELECT id FROM SystemUsers WHERE name = '" << name << "' AND pass = '" << pass << "';";

    if(!sqliteDB.openDB(SQLITE_DATABASE_PATH))
    {
        return false;
    }
    
    result = sqliteDB.executeSelect((sql.str()).c_str());

    if(result->getResultNum() > 0)
    {
        sql << "UPDATE SystemUsers SET state = " << LOG_ON << ", time = '" << get_CurrentTime() << "', sid = '" << sid << "' WHERE name = '" << name << "';";
        
        ret = sqliteDB.execute((sql.str()).c_str());
    }
    
    sqliteDB.closeDB();
    
    return ret;
}

int User_DAL::user_Validate(char *sid)
{
    int ret = 0;
    Result *result = NULL;
    
    ostringstream sql;
    sql << "SELECT time FROM SystemUsers WHERE sid = '" << sid << "' AND state = " << LOG_ON << ";";
    
    if(!sqliteDB.openDB(SQLITE_DATABASE_PATH))
    {
        return 0;
    }
    
    result = sqliteDB.executeSelect((sql.str()).c_str());
    
    if(result->getResultNum() > 0)
    {
        System_DAL system_DAL;
        int session_timeout = system_DAL.get_SessionTimeout();
        
        if(session_timeout > 0)
        {
            char *login_time = result->getAt(1, 0);
            string curr_time = get_CurrentTime();
            
            struct tm* tmp_time = (struct tm*)malloc(sizeof(struct tm));
            
            strptime(login_time, "%Y-%m-%d %H:%M:%S", tmp_time);
            time_t login_t = mktime(tmp_time);
            
            strptime(curr_time.c_str(), "%Y-%m-%d %H:%M:%S", tmp_time);
            time_t curr_t = mktime(tmp_time);
            
            int dif_t = static_cast<int> (difftime(curr_t, login_t) / 60);
            
            if(session_timeout > dif_t)
            {
                sql << "UPDATE SystemUsers SET time = '" << get_CurrentTime() << "' WHERE sid = '" << sid << "' AND state = " << LOG_ON << ";";
                sqliteDB.execute((sql.str()).c_str());

                ret = 1;
            }
            else
            {
                sql << "UPDATE SystemUsers SET state = " << LOG_OFF << " WHERE sid = '" << sid << "' AND state = " << LOG_ON << ";";
                sqliteDB.execute((sql.str()).c_str());
                
                ret = -1;
            }
        }
        else
        {
            ret = 1;
        }        
    }
    
    sqliteDB.closeDB();
    
    return ret;
}

string User_DAL::get_CurrentTime()
{
    char s[32];
    time_t t = time(0);
    strftime(s, sizeof(s), "%Y-%m-%d %H:%M:%S", localtime(&t));
    return s;
}


  3. 业务逻辑层

user_manage.h

/*
 ============================================================================
 Name                : user_manage.h
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 29/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#ifndef USER_MANAGE_H_
#define USER_MANAGE_H_

#include "md5.h"
#include "parse.h"
#include "user_DAL.h"

class User_Manage
{
public:
    User_Manage();
    ~User_Manage();
    
    void user_login(Parse list);
    bool user_validate(Parse list);
    
private:
    User_DAL user_DAL;
};

extern User_Manage user_manage;

#endif /*USER_MANAGE_H_*/


user_manage.cpp

/*
 ============================================================================
 Name                : user_manage.cpp
 Author              : Huang Gang
 Version             : 1.0.0
 Date                : 29/07/2009
 Copyright           : (C) 2009 XXXXXXXX LTD
 Description         :
 Revision History    :
 ============================================================================
 */
#include "user_manage.h"

User_Manage user_manage;

User_Manage::User_Manage(){}

User_Manage::~User_Manage(){}

/*
 * 功能：系统用户登录
 * 参数: 客户端传来的查询字符串
 * 返回：无
 */
void User_Manage::user_login(Parse list)
{
    char *name = list.get_item_n("name");
    char *pass = list.get_item_n("pass");
    string sid = MD5Encode((string(name) + string(user_DAL.get_CurrentTime())).c_str());
    
    bool login = user_DAL.user_Login(name, pass, sid.c_str());

    if(login)
    {
        //用户登录成功，跳转到主页并返回Session ID
        printf("window.location.href='index.html?sid=%s';", sid.c_str());
    }
    else
    {
        //用户登录失败
        printf("用户名或密码错误！");
    }
}

/*
 * 功能：验证系统登录用户
 * 参数: 客户端传来的查询字符串
 * 返回：是否通过
 */
bool User_Manage::user_validate(Parse list)
{
    char *sid = list.get_item_n("sid");
    int state = user_DAL.user_Validate(sid);

    if(state == 0)
    {
        //用户没有登录，跳转到登录页
        printf("parent.window.location.href='login.html';");
        return false;
    }
    else if(state == -1)
    {
        //用户登录后长时间没操作Session超时
        printf("parent.window.location.href='error.html';");
        return false;
    }
    else
    {
        //用户验证通过
        return true;
    }
}


三、结束语

    从示例代码看，sqlite数据库在嵌入式中的应用是非常的简单，跟在Windows下开发Windows应用程序没两样。这里值得一提的是三层结构思想即这里的数据库操作层、数据访问层、业务逻辑层，这使得应用程序的条理和效率更高。通过本篇，相信你已对sqlite数据库的应用有了一定的了解。