目录
本项目的交流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条件，后面有用到了再做举例。