常用数据库之sqlite的使用

2.1 介绍

  sqlite为关系型数据库，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了.

  SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

2.2 优缺点

优点：

    1>sqlite占用的内存和cpu资源较少
    2>源代码开源，完全免费
    3>检索速度上十几兆、几十兆的数据库sqlite很快，但是上G的时候最慢
    4>管理简单，几乎无需管理。灵巧、快速和可靠性高
    5>功能简约，小型化，追求最大磁盘效率

缺点：

    1>不支持多用户多线程同时读写数据库
    2>数据库同一时间仅仅同意一个写操作。因此吞吐量有限。

2.3 在windows安装

    1>下载 sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip 压缩文件
    2>创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件
    3>添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令

2.4 在qt上的使用

database.h

#ifndef DATABASE_H
#define DATABASE_H

#include 
#include 
#include 
#include 
#include 
#include 
#include 

typedef struct db_user
{
    QString sUsername;
    QString sPassword;
    int nAuth;
} DB_USER_t;


typedef void (*db_callback)(void*pUser, void*pData);
typedef void (*db_callback2)(void*pUser1, void*pUser2, void*pData);


class Database : public QObject
{
    Q_OBJECT
public:
    virtual ~Database();

    static Database& getInstance();
    int db_init(const QString& sDbPath);
    int db_run_cmd(QString& sql);
    int db_query_cmd(QString& sql);

    //User 增删改查
    int db_user_delete(QString sUsername);
    int db_user_get_all(int* pUserNum ,DB_USER_t* user);
    int db_user_get_byname(QString &username, DB_USER_t* user);
    int db_user_add(DB_USER_t* user);
    int db_user_modify_pwd(DB_USER_t* user);

private:
    Database(QObject *parent = NULL);
    QMutex      *m_dbLock;
    QSqlDatabase m_db;
    QString      m_dbcon;
    QSqlQuery   *m_dbQuery;
    bool         m_bLock;

signals:
    void sig_databaseLock();
};

#endif // DATABASE_H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

database.cpp

#include "database.h"
#include 
#include 
#include 
#include 
#include 
#include 
#include "commondef.h"

static int g_nCon = 0;

#define DB_NAME         "db/mydb.db"
#define DB_TYPE         "QSQLITE"
#define DB_TBL_USEr     "User"


#define DB_CREATE_TBL_USER   \
    "CREATE TABLE [User](\
        [username] VARCHAR(32) NOT NULL, \
        [password] VARCHAR(32) NOT NULL, \
        [auth] INTEGER NOT NULL DEFAULT 0);"

Database::Database(QObject *parent)
    : QObject(parent)
{
    this->setObjectName("MyDB");
    QString sDbPath = QString("%1/%2").arg(QCoreApplication::applicationDirPath()).arg(DB_NAME);
    MY_DEBUG << "sDbPath:" << sDbPath;
    db_init(sDbPath);
}

Database &Database::getInstance()
{
    static Database s_db;
    return s_db;
}

int Database::db_init(const QString& sDbPath)
{
    m_dbLock = new QMutex(QMutex::Recursive);
    QMutexLocker mutexlock(m_dbLock);
    m_bLock = false;

    // 1. create connect.
    m_dbcon = QString("DatabaseCon%1").arg(g_nCon++);
    m_db = QSqlDatabase::addDatabase(DB_TYPE, m_dbcon);
    m_db.setDatabaseName(sDbPath);
    if(!m_db.open())
    {
        MY_DEBUG << "Db open failed!!!";
        return -1;
    }
    // 2. check if exsist table.
    m_dbQuery = new QSqlQuery(m_db);
    m_dbQuery->clear();
    bool isTableExist = m_dbQuery->exec(
                QString("select count(*) from sqlite_master where type='table' and name='%1'").\
                arg(DB_TBL_USEr));
    m_dbQuery->next();
    if(!isTableExist || m_dbQuery->value(0).toInt() == 0)
    {
        m_dbQuery->exec(DB_CREATE_TBL_USER);
        // 1. add default users.
        DB_USER_t stUser = {"admin", "123456", 0};
        return db_user_add(&stUser);
    }
    return 0;
}

Database::~Database()
{
    m_dbLock->lock();
    if(m_db.isOpen())
    {
        m_db.close();
    }
    QSqlDatabase::removeDatabase(m_dbcon);
    delete m_dbQuery;
    m_dbLock->unlock();
    delete m_dbLock;
}

int Database::db_run_cmd(QString &sql)
{
    QMutexLocker mutexlock(m_dbLock);

    // 1. check valid.
    if(!m_db.isOpen() || !m_db.isValid())
    {
        MY_DEBUG << "db is not valid";
        return -1;
    }

    // 2. clear old query.
    m_dbQuery->clear();

    // 3. run command.
    if(!m_dbQuery->exec(sql))
    {
        MY_DEBUG << "run sql:" << sql;
        MY_DEBUG << "m_dbQuery->exec fail: " << m_dbQuery->lastError();
        if(m_bLock == false && m_dbQuery->lastError().text().contains("locked"))
        {
            emit sig_databaseLock();
            m_bLock = true;
        }
        return -1;
    }
    return 0;
}

int Database::db_query_cmd(QString &sql)
{
    // 1. check valid.
    if(!m_db.isOpen() || !m_db.isValid())
    {
        MY_DEBUG << "db is not valid";
        return -1;
    }

    // 2. clear old query.
    m_dbQuery->clear();

    // 3. run command.
    if(!m_dbQuery->exec(sql))
    {
        MY_DEBUG << "run sql:" << sql;
        MY_DEBUG << "m_dbQuery->exec fail: " << m_dbQuery->lastError();
        return -1;
    }
    return 0;
}

//User
int Database::db_user_delete(QString sUsername)
{
    QString sql = QString("delete from User where username = '%1'").arg(sUsername);
    return db_run_cmd(sql);
}

int Database::db_user_get_all(int* pUserNum ,DB_USER_t* user)
{
    QMutexLocker mutexlock(m_dbLock);

    // 1. query exec.
    QString sql = QString("select username,password,auth from User");
    if(db_query_cmd(sql))
    {
        MY_DEBUG << "DBERR: " << __FUNCTION__ << __LINE__;
        return -1;
    }

    // 2. get all data.
    int i = 0;
    while(m_dbQuery->next() && i < MAX_USER_NUM)
    {
        user[i].sUsername = m_dbQuery->value(0).toString();
        user[i].sPassword = m_dbQuery->value(1).toString();
        user[i].nAuth = m_dbQuery->value(2).toInt();
        i++;
    }
    if(i == 0)
    {
        MY_DEBUG << "DBERR: " << __FUNCTION__ << __LINE__;
        return -1;
    }
    *pUserNum = i;
    return 0;
}

int Database::db_user_get_byname(QString &username, DB_USER_t *user)
{
    QMutexLocker mutexlock(m_dbLock);

    // 1. query exec.
    QString sql = QString("select username,password,auth from User where username='%1'").arg(username);
    if(db_query_cmd(sql))
    {
        MY_DEBUG << " db_query_cmd failed";
        return -1;
    }

    // 2. get user data.
    if(m_dbQuery->next())
    {
        user->sUsername = username;
        user->sPassword = m_dbQuery->value(1).toString();
        user->nAuth = m_dbQuery->value(2).toInt();
        return 0;
    }

    return -1;
}

int Database::db_user_add(DB_USER_t *user)
{
    QString sql = QString("insert into User(username, password, auth) values('%1', '%2', %3)").\
            arg(user->sUsername).arg(user->sPassword).arg(user->nAuth);
    return db_run_cmd(sql);
}

int Database::db_user_modify_pwd(DB_USER_t *user)
{
    QString sql = QString("UPDATE User set password = '%1', auth = %2 where username='%3'").\
            arg(user->sPassword).arg(user->nAuth).arg(user->sUsername);
    return db_run_cmd(sql);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208

main.cpp

#include 
#include "database.h"
#include "commondef.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    //增
    DB_USER_t stUser1 = {"test1", "test1_123456", 0};
    Database::getInstance().db_user_add(&stUser1);


    //查
    MY_DEBUG << "##############1##################";
    int nUserNum;
    DB_USER_t db_user[MAX_USER_NUM];
    Database::getInstance().db_user_get_all(&nUserNum, db_user);

    MY_DEBUG << "nUserNum:" << nUserNum;
    for(int i = 0; i < nUserNum; i++)
    {
        MY_DEBUG << "i:" << i << ", db_user->sUsername:" << db_user[i].sUsername << ", db_user->sPassword:" << db_user[i].sPassword << ", db_user->nAuth:" << db_user[i].nAuth;
    }

    //改
    DB_USER_t stUser2 = {"test1", "test1_654321", 0};
    Database::getInstance().db_user_modify_pwd(&stUser2);

    //查
    MY_DEBUG << "##############2##################";
    DB_USER_t stUser3;
    QString sUsername = "test1";
    Database::getInstance().db_user_get_byname(sUsername, &stUser3);
    MY_DEBUG << "stUser3.sUsername:" << stUser3.sUsername << ", stUser3.sPassword:" << stUser3.sPassword << ", stUser3.nAuth:" << stUser3.nAuth;

    //删
    Database::getInstance().db_user_delete("test1");

    //查
    MY_DEBUG << "##############3##################";
    int nUserNum2;
    DB_USER_t db_user2[MAX_USER_NUM];
    Database::getInstance().db_user_get_all(&nUserNum2, db_user2);

    for(int i = 0; i < nUserNum2; i++)
    {
        MY_DEBUG << "i:" << i << ", db_user2->sUsername:" << db_user2[i].sUsername << ", db_user2->sPassword:" << db_user2[i].sPassword << ", db_user2->nAuth:" << db_user2[i].nAuth;
    }
    return a.exec();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

2.5 qt-demo下载

下载链接：https://download.csdn.net/download/linyibin_123/86341593

2.6 sqlite的具体学习

菜鸟教程：https://www.runoob.com/sqlite/sqlite-intro.html