物联网实战--平台篇之(三)账户后台数据库

一、账户后台设计

二、账户数据库

三、数据库操作——增

四、数据库操作——改

五、数据库操作——查

本项目的交流QQ群:701889554

物联网实战--入门篇https://blog.csdn.net/ypp240124016/category_12609773.html

物联网实战--驱动篇https://blog.csdn.net/ypp240124016/category_12631333.html

本项目资源文件https://download.csdn.net/download/ypp240124016/89280540

一、账户后台设计

上图是应用服务器里的帐号相关服务，包括账户数据库和软件服务模块。在这里，我们的账户模块设计是比较基础简单的，就是常规的请求手机验证码、注册、登录和重置密码等流程，修改密码是登录后的操作，放在其它文章讲。这些操作是建立在上一篇文章的工程基础之上的，之前已经完成了MQTT网络部分和基础的数据库部分，那么这一章以账户后台为切入点，主要讲解下数据库的具体使用。

二、账户数据库

工欲善其事必先利其器，在设计注册、登录等流程之前，肯定要预先设计好数据库的，这是根本，对于用户的这些动作才能有对应的操作对象，比如用户登录帐号，把帐号密码发到服务器了，后台服务肯定要对比验证，那比较的对象在哪儿——自然就是数据库里的账户信息了。

之前说过，为了方便，我们采用的是sqlite文件数据库，上图是当前数据库的信息。根据数据库的基本知识，数据库是由一堆的库表组成的，每张表有各自的作用，表之间通过一些关键字段相互联系。先看账户列表的数据库表，即account_list_tb，包含了id、账户名、密码、权限、父账号、关联手机号、帐号下的所属应用和创建时间，接下来看下这个库表是如何建立的。

#ifndef ACCOUNTSQLITE_H
#define ACCOUNTSQLITE_H

#include <QObject>
#include "BaseSqlite.h"

class AccountSqlite : public BaseSqlite
{
    Q_OBJECT
public:
    typedef struct
    {
        QString account;  //账户名
        QString passWord;  //密码
        u32 auth;  //权限值
        QString parentAccount; //父账号
        QString phone; //关联手机号
        QList<u32>appList;  //名下的应用列表
        QString createTime; //创建时间
    }AccountNodeStruct;  //账户节点

    typedef struct
    {
        u32 appID;   //应用ID
        QString appName; //应用名称
        QString creator;  //建立者的账户名
        QString createTime; //创建时间
    }AppNodeStruct;  //应用节点
    
public:
    explicit AccountSqlite(QObject *parent = nullptr);
    
    bool createAccountListTable(void);
    bool addAccountNode(QString account, QString pass_word, u32 auth, QString parent_account, QString phone);
    bool updateAccountPassWord(QString account, QString pass_word);
    bool updateAccountPhone(QString account, QString phone);
    bool updateAccountAuth(QString account, u32 auth);
    bool updateAccountAppList(QString account, QList<u32>app_list);
    bool selectAccountByName(QString account, AccountNodeStruct &account_node);
    bool selectAccountByPhone(QString phone, AccountNodeStruct &account_node);
    bool selectChildAccountList(QString parent_account, QList<AccountNodeStruct>&child_list);
    bool searchAccountByName(QString account, AccountNodeStruct &account_node);
    bool searchAccountByPhone(QString phone, AccountNodeStruct &account_node);
    bool selectAccountAppList(QString account, QList<u32> &app_list);
    
    bool createAppListTable(void);
    bool addAppIDToList(u32 app_id, QString creator);
    bool updateAppName(u32 app_id, QString app_name);
    QString selectAppName(u32 app_id);
    bool selectAppInfo(u32 app_id, AppNodeStruct &app_node);
    bool selectLastAppInfo(AppNodeStruct &app_node);
    bool selectAppList(QList<u32>&app_list);
    u32 selectMaxAppID(void);
    u32 getAppCountFromAccount(QString account);

private:
    u8 m_keyBuff[16];
    DrvCommon drv_com;
signals:
    
};

#endif // ACCOUNTSQLITE_H

这里是账户数据库相关的头文件，首先可以看到它继承于之前创建的基础数据库驱动类BaseSqlite，接下来就是定义账户的数据结构体，这样便于后期的修改和查询；在功能上，账户相关数据库函数有建表、添加账户、更新密码、更新手机、更新权限、更新应用列表、根据账户名查询、根据手机号查询、子账户查询和应用列表查询等功能；具体每个功能是怎么实现的举个例子，相似度比较高。

//建立账户列表  数据库表
bool AccountSqlite::createAccountListTable(void)
{
    QString str_query = "CREATE TABLE If Not Exists account_list_tb ("
            "id INTEGER NOT NULL,"
            "account char(30) NOT NULL UNIQUE,"
            "pass_word varchar(50) NOT NULL,"
            "auth bigint DEFAULT 0,"
            "parent_account char(30) DEFAULT NULL,"
            "phone char(30) DEFAULT NULL UNIQUE,"
            "app_list varchar(5000) DEFAULT 0,"
            "create_time timestamp DEFAULT (datetime(\'now\',\'localtime\')),"
            "PRIMARY KEY (id)"
          ") ";


    if(runSqlQuery(str_query)==false)
    {
        qDebug("createAccountsTable error!");
        return false;
    }
    return true;
}

上面这个是创建账户库表的功能函数，可以发现，主要就是执行数据库语言的语句，runSqlQuery就是执行语句，不同的功能都是需要它去执行的，差异化在执行语句上。建表的语句是

CREATE TABLE If Not Exists account_list_tb

Not Exists表示不存在account_list_tb表的时候才建立新表，不会重复建表。

对创建账户数据库表内容进行分析，首先有个自增长的id号，NOT NULL表示不能为空。account行的char(30)表示最大存储30个字符，UNIQUE表示这个字段具有唯一性，所有记录中这个字段的内容不能重复，这是比较重要的，账户名重复了整个系统就乱套了，手机号也是这个道理。app_list字段存放的是这个账户创建的应用，采用base64编码存储，

解码前eyJhcHBfbGlzdCI6WzEyMzAwMl19，

解码后

{
"app_list": [
123002
]
}

另外，密码是需要加密存储的，所以在头文件里定义了一个密码缓存区，后续账户数据库里的加解密都用这个密码，可以看到密码在base64解码后是一串乱码，即使数据库被复制了也看不到密码。

三、数据库操作——增

数据库增加操作的语句是INSERT INTO.....VALUES....，根据内容组合起来，再用runSqlQuery执行即可，下面是增加一个账户的功能函数，要注意的是密码要进一步加密再存储，加密算法采用AES，密码就是之前所说的m_keyBuff；由于account字段是唯一性的，所以如果账户名重复了是会添加失败的。


//添加一个账户信息到数据库
bool AccountSqlite::addAccountNode(QString account, QString pass_word, u32 auth, QString parent_account, QString phone)
{
    if(phone.isEmpty())
    {
        phone=account;
    }
    if(pass_word.isEmpty())
    {
        pass_word="12345678";
    }
    u8 out_passwd[50]={0};
    int out_len=drv_com.aes_encrypt_buff((u8*)pass_word.toUtf8().data(), (u16)pass_word.toUtf8().size(), out_passwd, (u16)sizeof (out_passwd), m_keyBuff);//密码加密存储
    if(out_len<16)
        return  false;
    QByteArray pwd_ba((char*)out_passwd, out_len);
    pass_word=pwd_ba.toBase64();
    QString str_query = QString::asprintf("INSERT INTO account_list_tb (account, pass_word, auth, parent_account, phone) VALUES (  \"%s\", \"%s\", %u, \"%s\", \"%s\")",\
                                          account.toUtf8().data(), pass_word.toUtf8().data(), auth, \
                                          parent_account.toUtf8().data(), phone.toUtf8().data());
    if( runSqlQuery(str_query))
    {
        qDebug("addAccountNode ok!");
        return true;
    }

    qDebug("addAccountNode failed!");
    return false;
}

四、数据库操作——改

数据库修改语句是UPDATE....SET....WHERE条件，这里以更新账户密码为例，UPDATE后面是要更新的字段，WHERE后面跟的是条件，这里就是账户名匹配了。

//更新账户密码
bool AccountSqlite::updateAccountPassWord(QString account, QString pass_word)
{
    if(pass_word.isEmpty())
    {
        pass_word="12345678";
    }
    u8 out_passwd[50]={0};
    int out_len=drv_com.aes_encrypt_buff((u8*)pass_word.toUtf8().data(), (u16)pass_word.toUtf8().size(), out_passwd, (u16)sizeof (out_passwd), m_keyBuff);
    if(out_len<16)
        return  false;
    QByteArray pwd_ba((char*)out_passwd, out_len);
    pass_word=pwd_ba.toBase64();

    QString str_query = QString::asprintf("UPDATE account_list_tb SET pass_word=\"%s\"   WHERE account=\"%s\"",
                                          pass_word.toUtf8().data(), account.toUtf8().data());
//    qDebug()<<str_query;
    if( runSqlQuery(str_query))
    {
        qDebug("updateAccountPassWord ok!");
        return true;
    }
    return false;
}

下面这个更新应用列表也是比较重要的内容，每次创建新应用后都要更新一遍，应用列表是采用json数据格式，并转为base64编码保存的。

//更新账户的应用列表
bool AccountSqlite::updateAccountAppList(QString account, QList<u32> app_list)
{
    QJsonDocument json_doc;
    QJsonObject root_obj;
    QJsonArray app_array;
    int nSize=app_list.size();
    for(int i=0; i<nSize; i++)
    {
        u32 app_id=app_list.at(i);
        if(app_id>0)
        {
            app_array.append((qint64)app_id);
        }
    }
    root_obj.insert("app_list", app_array);
    json_doc.setObject(root_obj);
    QByteArray json_ba=json_doc.toJson(QJsonDocument::Compact);//转为数据保存
    qDebug()<<"json_ba="<<json_ba.data();
    QString str_query = QString::asprintf("UPDATE account_list_tb SET  app_list=\"%s\"   WHERE account=\"%s\"",
                                          json_ba.toBase64().data(), account.toUtf8().data());

//    qDebug()<<str_query;
    if( runSqlQuery(str_query))
    {
        qDebug("updateAccountAppList ok!");
        return true;
    }
    return false;
}

五、数据库操作——查

数据库查找操作是使用最频繁的了，语句格式是SELECT......WHERE条件，这里以查询账户信息为例，SELECT后面跟着要查找内容的字段，这里很多人喜欢用*，不建议这样做，*代表选取所有字段，一个是效率肯定有所降低了，另一个是对所选取的字段顺序和数量不明确，在后面的解析转换中会出错，比如过段时间在第一列又增加了个字段内容，那么之前的解析代码就不能用了，顺序不对了。我们这里选择了account, pass_word, auth, parent_account, phone, app_list, create_time这些字段，不管库表增加什么字段，对我们后面解析都没影响。解析的时候也是按照这个顺序和对应的类型一个个转化的，ptr就是索引，每转化一个就自增到下一个字段，保存的时候是什么流程，解析的时候就是一个逆过程，比如这里的密码，先进行base64解码，然后再AES解密，这样才能得到正确的密码。

另外一个注意点是返回之前要执行下m_sqlQuery.finish()语句，这样才能结束查询，避免出错。


//根据名称 获取帐号信息
bool AccountSqlite::selectAccountByName(QString account, AccountNodeStruct &account_node)
{
    QString str_query = QString::asprintf("SELECT account, pass_word, auth, parent_account, phone, app_list, create_time  FROM account_list_tb WHERE account=\"%s\"" , account.toUtf8().data());

//    qDebug()<<str_query;
    if(runSqlQuery(str_query)==false)
    {
        qDebug("selectAccountNode error_01!");
         return false;
    }
    while(m_sqlQuery.next())
    {
        int ptr=0;
        account_node.account=m_sqlQuery.value(ptr++).toString();
        QString pwd_str=m_sqlQuery.value(ptr++).toString();
        QByteArray pwd_ba=QByteArray::fromBase64(pwd_str.toUtf8());
        u8 out_passwd[50]={0};
        int out_len=drv_com.aes_decrypt_buff((u8*)pwd_ba.data(), (u16)pwd_ba.size(), out_passwd, sizeof (out_passwd), m_keyBuff);//存储密码解密
        if(out_len<=0)
        {
            m_sqlQuery.finish();
            return  false;
        }
        account_node.passWord=QString((char*)out_passwd);
        account_node.auth=m_sqlQuery.value(ptr++).toUInt();
        account_node.parentAccount=m_sqlQuery.value(ptr++).toString();
        account_node.phone=m_sqlQuery.value(ptr++).toString();

        QString json_str=m_sqlQuery.value(ptr++).toString();//应用列表JSON
        QByteArray json_ba=QByteArray::fromBase64(json_str.toUtf8());
        QJsonParseError json_error;
        QJsonDocument json_doc;
        json_doc = QJsonDocument(QJsonDocument::fromJson(json_ba, &json_error));//转为JSON格式
        if(json_error.error != QJsonParseError::NoError)
        {
            qDebug()<<"json error= "<<json_error.error;
        }
        else
        {
            QJsonObject root_obj = json_doc.object();
            if(root_obj.contains("app_list"))//应用列表
            {
                QJsonValue value=root_obj.value("app_list");
                if(value.isArray())
                {
                    QJsonArray app_array=value.toArray();
                    int nSize=app_array.size();
                    for(int i=0; i<nSize; i++)
                    {
                        QJsonValue value=app_array.at(i);
                        if(value.isDouble())
                        {
                            u32 app_id=(u32)value.toDouble();
                            if(app_id>0)
                            {
                                account_node.appList.append(app_id);//添加app_id
                            }
                        }
                    }
                }
            }
        }
        account_node.createTime=m_sqlQuery.value(ptr++).toString();
        m_sqlQuery.finish();
        return true;
    }
    qDebug("selectAccountNode error_02!");
    return false;
}

对于删除，这里暂时没用到，其实也很简单，就是DELETE....WHERE条件，后面有用到了再做举例。