今天复习到epoll的一些知识,接下来的部分需要模拟到服务器连接数据库的功能,这里笔者就install了sqlite3并对其方法进行了一些封装方便服务器调用接口对数据库进行基本的增删改查(暂不支持复合查询逻辑),因为笔者是用qt的编译器,为了打印日志使用了qDebug(),使用者可以将qDebug()直接replace成std::cout,头文件qdebug去掉,源码如下:
sqlite3头文件:
#ifndef SQLITE3TOOL_H
#define SQLITE3TOOL_H
#include<QDebug>
#include<sqlite3.h>
#include<iostream>
#include<string>
struct sqlite3TableItem
{
std::string _name;
std::string _type;
std::string _sqlRestrict;
};
typedef enum _operation
{
_bigger,
_smaller,
_like,
_equal
}_opt;
struct sqlite3WhereCondition
{
std::string _name;
_opt _op;
std::string _value;
// and/or
std::string _operation;
};
struct sqlite3SetCondition
{
std::string _name;
std::string _value;
};
class Sqlite3Tool
{
public:
Sqlite3Tool();
~Sqlite3Tool();
sqlite3*
createSqlite3File(char *_fileName);
void
setSqlite3File(sqlite3* _fd);
/*this function uses to create a db table,
*the map have three items,the first item is
* the name,the second is the type,
* and the third is the restrict.
*/
void
createSqlTable(std::string _tableName,
struct sqlite3TableItem* _items,
int size);
void
insertIntoSqlTable(std::__cxx11::string _tableName,
std::string* _items, std::__cxx11::string *_colName, int _size);
void
deleteFromSqlTable(std::__cxx11::string _tableName,
struct sqlite3WhereCondition* _items, int _size);
void
updateSqlTable(std::string _tableName,
struct sqlite3SetCondition* _setItems,
struct sqlite3WhereCondition* _restrictItems,
int _setItemSize, int _restrictItemSize);
void selectSqlTable(std::string _tableName,
std::string* _findItems,
struct sqlite3WhereCondition* _restrictItems,
int _restrictItemsSize,
int _findItemsSize,
int _resRows,
int _resCols,
char** _resultData,
std::string _groudby="",
std::string _having="",
std::string _orderby="",
bool _isDesc = false
);
inline void
sql_exec(const char* _sql);
private:
sqlite3* _db=NULL;
//this variable is declared to keep errLog
char* _errMSg;
};
#endif // SQLITE3TOOL_H
.cpp:
#include "sqlite3tool.h"
Sqlite3Tool::Sqlite3Tool()
{
}
Sqlite3Tool::~Sqlite3Tool()
{
sqlite3_close(_db);
}
sqlite3 *
Sqlite3Tool::createSqlite3File(char *_fileName)
{
int _ret=sqlite3_open(_fileName,&_db);
if(_ret)
{
qDebug()<<"create db fail\n";
return NULL;
}
else
{
return _db;
}
}
void
Sqlite3Tool::setSqlite3File(sqlite3 *_fd)
{
this->_db=_fd;
}
void
Sqlite3Tool::createSqlTable(std::string _tableName,
struct sqlite3TableItem *_items,
int _size)
{
std::string _sql="CREATE TABLE ";
_sql+=_tableName;
_sql+="(" ;
sqlite3TableItem* _ptr=_items;
while(_size)
{
_sql+=_ptr->_name;
_sql+=" ";
_sql+=_ptr->_type;
_sql+=" ";
_sql+=_ptr->_sqlRestrict;
++_ptr;
--_size;
if(_size!=0)
{
_sql+=",";
}
}
_sql+=");";
sql_exec(_sql.data());
}
void
Sqlite3Tool::insertIntoSqlTable(std::string _tableName,
std::__cxx11::string *_items,
std::string *_colName,
int _size)
{
std::string _sql="insert into \"";
_sql+=_tableName;
_sql+="\" (";
int _mSize=_size;
std::string* _colPtr=_colName;
while(_mSize)
{
_sql+=*_colPtr;
++_colPtr;
--_mSize;
if(_mSize)
{
_sql+=",";
}
}
_sql+=") values(";
std::string* _ptr=_items;
while(_size)
{
_sql+="'";
_sql+=*_ptr;
_sql+="'";
++_ptr;
--_size;
if(_size)
{
_sql+=",";
}
}
_sql+=");";
sql_exec(_sql.data());
}
void
Sqlite3Tool::deleteFromSqlTable(std::string _tableName,
struct sqlite3WhereCondition *_items,
int _size)
{
std::string _sql="delete from ";
_sql+=_tableName;
if(_items!=NULL)
{
_sql+=" where ";
struct sqlite3WhereCondition* _ptr=_items;
while(_size)
{
_sql+=_ptr->_name;
switch (_ptr->_op) {
case _smaller:
_sql+="<";
break;
case _bigger:
_sql+=">";
break;
case _equal:
_sql+="=";
break;
case _like:
_sql+=" like ";
break;
}
_sql+=_ptr->_value;
_sql+=_ptr->_operation;
--_size;
++_ptr;
}
_sql+=";";
}
sql_exec(_sql.data());
}
void
Sqlite3Tool::updateSqlTable(std::__cxx11::string _tableName,
struct sqlite3SetCondition *_setItems,
struct sqlite3WhereCondition *_restrictItems,
int _setItemSize,
int _restrictItemSize)
{
std::string _sql="update ";
_sql+=_tableName;
_sql+=" set ";
struct sqlite3SetCondition* _setPtr=_setItems;
struct sqlite3WhereCondition* _restrictPtr=_restrictItems;
while(_setItemSize)
{
_sql+=_setPtr->_name;
_sql+="=";
_sql+="'";
_sql+=_setPtr->_value;
_sql+="'";
--_setItemSize;
++_setPtr;
if(_setItemSize)
{
_sql+=",";
}
}
if(_restrictPtr!=NULL)
{
_sql+=" where ";
while(_restrictItemSize)
{
_sql+=_restrictPtr->_name;
switch (_restrictPtr->_op) {
case _smaller:
_sql+="<";
break;
case _bigger:
_sql+=">";
break;
case _equal:
_sql+="=";
break;
case _like:
_sql+=" like ";
break;
}
_sql+=_restrictPtr->_value;
_sql+=_restrictPtr->_operation;
--_restrictItemSize;
++_restrictPtr;
}
}
_sql+=";";
sql_exec(_sql.data());
}
void Sqlite3Tool::selectSqlTable(std::__cxx11::string _tableName,
std::string *_findItems,
struct sqlite3WhereCondition *_restrictItems,
int _restrictItemsSize,
int _findItemsSize,
int _resRows,
int _resCols,
char **_resultData,
std::__cxx11::string _groudby,
std::__cxx11::string _having,
std::__cxx11::string _orderby,
bool _isDesc)
{
std::string _sql="select ";
std::string* _findPtr=_findItems;
while(_findItemsSize)
{
_sql+=*_findItems;
++_findPtr;
--_findItemsSize;
if(_findItemsSize)
{
_sql+=",";
}
}
_sql+=" from ";
_sql+=_tableName;
struct sqlite3WhereCondition* _restrictPtr=_restrictItems;
if(_restrictPtr!=NULL)
{
_sql+=" where ";
while(_restrictItemsSize)
{
_sql+=_restrictPtr->_name;
switch (_restrictPtr->_op) {
case _smaller:
_sql+="<";
break;
case _bigger:
_sql+=">";
break;
case _equal:
_sql+="=";
break;
case _like:
_sql+=" like ";
break;
}
_sql+="'";
_sql+=_restrictPtr->_value;
_sql+="'";
_sql+=_restrictPtr->_operation;
++_restrictPtr;
--_restrictItemsSize;
}
}
if(_groudby!="")
{
_sql+=" group by ";
_sql+=_groudby;
if(_having!="")
{
_sql+=" having ";
_sql+=_having;
}
}
if(_orderby!="")
{
_sql+=" order by ";
_sql+=_orderby;
if(_isDesc)
{
_sql+=" desc";
}
}
_sql+=";";
qDebug()<<"sql exec:"<<_sql.data()<<"\n";
sqlite3_get_table(_db,_sql.data(),
&_resultData,
&_resRows,
&_resCols,
&_errMSg);
int index=_resCols;
qDebug()<<"select result:"<<"\n";
for(int i=0;i<_resRows;++i)
{
qDebug()<<"the "<<i<<" row note:";
for(int j=0;j<_resCols;++j)
{
qDebug() <<_resultData[j]<<" "<<_resultData[index];
++index;
}
}
}
void
Sqlite3Tool::sql_exec(const char *_sql)
{
qDebug()<<"sql exec:"<<_sql<<"\n";
int _ret=sqlite3_exec(_db,_sql,0,0,&_errMSg);
if(_ret)
{
qDebug()<<"sqlTable exec fail:"<<_errMSg;
}
}
测试代码:
Sqlite3Tool a;
a.createSqlite3File("/home/mengying/db/testSqlite3.db");
struct sqlite3TableItem item[3]
={"ID","VARCHAR(10)","primary key",
"UserName","varchar(20)","unique",
"UserAge","INT","check(UserAge<150 and UserAge>0)"};
a.createSqlTable("mengying",item,3);
std::string colName[3]={"ID","UserName","UserAge"};
std::string data[3]={"1","mengyingno1","20"};
std::string data2[3]={"2","mengyingno2","21"};
std::string data3[3]={"3","mengyingno3","22"};
a.insertIntoSqlTable("mengying",data,colName,3);
a.insertIntoSqlTable("mengying",data2,colName,3);
a.insertIntoSqlTable("mengying",data3,colName,3);
std::string b[1]={"UserName"};
struct sqlite3WhereCondition c[1]={"ID",_bigger,"1",""};
int row,cols;
char** w;
a.selectSqlTable("mengying",b,c,1,1,row,cols,w);
struct sqlite3SetCondition con[1]={"UserName","mengyingno4"};
struct sqlite3WhereCondition c1[1]={"ID",_equal,"1",""};
a.updateSqlTable("mengying",con,c1,1,1);
qDebug()<<"=========================";
a.selectSqlTable("mengying",b,c1,1,1,row,cols,w);