WLG/dbaccess/sql_db.hpp

#ifndef SQL_DB_HPP_
#define SQL_DB_HPP_
extern "C" {
#include <sqlite3.h>
}
#include <stdio.h>
#include <string.h>
#include <map>
#include <vector>
#include <mutex>
#include <boost/container/detail/singleton.hpp>

extern const char *T_SENSOR_INFO1[];
extern const char *T_DATA_INFO1[];
extern const char *T_DATASTATIC_INFO1[];
extern const char *T_DATANODE_TIME1[];
extern const char *T_GATEWAY_INFO1[];
extern const char *T_BATTERY_INFO1[];
extern const char *T_SENSOR_BT_INFO1[];

#define T_SENSOR_INFO(x) T_SENSOR_INFO1[T_SENSOR_INFO_##x]
#define T_DATA_INFO(x) T_DATA_INFO1[T_DATA_INFO_##x]
#define T_DATASTATIC_INFO(x) T_DATASTATIC_INFO1[T_DATASTATIC_INFO_##x]
#define T_GATEWAY_INFO(x) T_GATEWAY_INFO1[T_GATEWAY_INFO_##x]
#define T_DATANODE_TIME(x) T_DATANODE_TIME1[T_DATANODE_TIME_##x]
#define T_BATTERY_INFO(x) T_BATTERY_INFO1[T_BATTERY_INFO_##x]

#define T_SENSOR_BT_INFO(x) T_SENSOR_BT_INFO1[T_SENSOR_BT_INFO_##x]

typedef std::map<std::string, std::string> map_t;
typedef std::vector<std::string> vec_t;
typedef std::vector<float> vec_Value;
typedef std::vector<vec_t> array_t;

class SqliteDB {
public:
    explicit SqliteDB();
    virtual ~SqliteDB();
    bool OpenDB(const char *pDbName);
    void SqliteInit(const char *pDbName);
    void SqliteInitDel(const char *pDbName);
    void Createtable(const char *ptableName);
    void Deletetable(const char *ptableName);
    void CreatedataStatictable(const char *ptableName);
    int TransBegin();
    int TransRollback();
    int TransCommit();
    int CreateTable(const char *sql);
    int ExeSql(const char *sql);
    int GetTableColumnCount(const char *tablename);
    int GetTableRows(const char *tablename, const char *whereCon);
    int AlterTable(const char *tablename, const char *column, bool isAdd = true);
    vec_t GetDataSingleLine(const char *tablename, const char *column, const char *whereCon);
    vec_t GetDataSingleLine(const char *sql);
    std::string GetData(const char *tablename, const char *column, const char *whereCon);    
    array_t GetDataMultiLine(const char *sql);
    array_t GetDataMultiLine(const char *tablename, const char *column, const char *whereCon);
    array_t GetDataMultiLineTransaction(const char *tablename, const char *column, const char *whereCon);
    vec_t GetDataMultiLineOfOneColumn(const char *tablename, const char *column, const char *whereCon);
    vec_Value GetDataMultiLineOfOneColumnDouble(const char *tablename, const char *column, const char *whereCon);
    int DeleteTableData(const char *tablename, const char *whereCond = NULL);
    int DeleteTableDataOneConditon(const char *tablename, const char *condColumnName, const char *condColumnValue);
    int UpdateTableData(const char *tablename, const char *updateColumn, const char *whereCond);
    int UpdateNodeNameData(const char *tablename, const char *updateColumn, const char *whereCond);
    int UpdateTableDataOneColumn(const char *tablename, const char *columnName, const char *columnValue, const char *whereColName = NULL, const char *whereColValue = NULL);
    int execute_sql_file(const char *filename);
    int UpdateTableData(const char *directSql);
    int InsertData(const char *tablename, const char *insertValues, int replace = 0, bool expandalbe = false);
    int InsertData(const char *insertSql);
    int CalculateBattery();
    int CalculateDip();
    int InintGateway();
    int QueryofflineData();
    int ClearExpireData();
    std::string GetNodeConfigureInfor(const char *whereCon);
    int CloseDB();

private:
    sqlite3 *GetDbHandle();
    sqlite3 *mDBAcess;
    std::mutex mtx_;
};

typedef boost::container::dtl::singleton_default<SqliteDB> sqlite_db_ctrl;

typedef enum {
    T_SENSOR_INFO_TNAME = 0,
    T_SENSOR_INFO_DATANODENO,
    T_SENSOR_INFO_DATANODENAME,
    T_SENSOR_INFO_INITFLAG,
    T_SENSOR_INFO_ACCFLAG,
    T_SENSOR_INFO_ZIGBEEFLAG,
    T_SENSOR_INFO_TEMTOPFLAG,
    T_SENSOR_INFO_TEMBOTFLAG,
    T_SENSOR_INFO_EQUIPSTA,
    T_SENSOR_INFO_HARDVERSION,
    T_SENSOR_INFO_SOFTVERSION,
    T_SENSOR_INFO_BPNO,
    T_SENSOR_INFO_SERIALNO,
    T_SENSOR_INFO_FIRSTPOWERNO,
    T_SENSOR_INFO_CONFIGFLAG,
    T_SENSOR_INFO_STARTBRANDS,
    T_SENSOR_INFO_STOPBRANDS,
    T_SENSOR_INFO_FEATUREINTERVAL,
    T_SENSOR_INFO_WAVEINTERVAL,
    T_SENSOR_INFO_SAMPLINGRATE,
    T_SENSOR_INFO_SCOPE,
    T_SENSOR_INFO_RANGE,
    T_SENSOR_INFO_ENVELOPEBANDPASS,
    T_SENSOR_INFO_FAULTFREQUENCY,
    T_SENSOR_INFO_ZIGBEEPANID,
    T_SENSOR_INFO_ZIGBEECHANNEL,
    T_SENSOR_INFO_ZIGBEESHORTADDR,
    T_SENSOR_INFO_ZIGBEELONGADDR,
    T_SENSOR_INFO_ZIGBEEDESADDR,
    T_SENSOR_INFO_STATUS,
    T_SENSOR_INFO_TIMESTAMP,
    T_SENSOR_INFO_VIFF,
    T_SENSOR_INFO_RSSI
} T_SENSOR_INFO_Index;

typedef enum {
    T_DATA_INFO_TNAME = 0,
    T_DATA_INFO_DATANODENO,
    T_DATA_INFO_CHANNELID,
    T_DATA_INFO_DIAGNOSISEAK,
    T_DATA_INFO_INTEGRATPK,
    T_DATA_INFO_INTEGRATRMS,
    T_DATA_INFO_RMSVALUES,
    T_DATA_INFO_ENVELOPNERGY,
    T_DATA_INFO_AMP1,
    T_DATA_INFO_AMP2,
    T_DATA_INFO_AMP3,
    T_DATA_INFO_AMP4,
    T_DATA_INFO_AMP5,
    T_DATA_INFO_PHASE1,
    T_DATA_INFO_PHASE2,
    T_DATA_INFO_PHASE3,
    T_DATA_INFO_PHASE4,
    T_DATA_INFO_STATICINDEX,
    T_DATA_INFO_TIMESTAMP,
    T_DATA_INFO_SENDMSG,
    T_DATA_INFO_NODERESEND
} T_DATA_INFO_Index;

typedef enum {
    T_DATASTATIC_INFO_TNAME = 0,
    T_DATASTATIC_INFO_DATANODENO,
    T_DATASTATIC_INFO_CHANNELID,
    T_DATASTATIC_INFO_TEMTOP,
    T_DATASTATIC_INFO_TEMBOT,
    T_DATASTATIC_INFO_DIP,
    T_DATASTATIC_INFO_VOLTAGE,
    T_DATASTATIC_INFO_STATICINDEX,
    T_DATASTATIC_INFO_TIMESTAMP,
    T_DATASTATIC_INFO_SENDMSG,
    T_DATASTATIC_INFO_NODERESEND
} T_DATASTATIC_INFO_Index;

typedef enum {
    T_DATANODE_TIME_TNAME = 0,
    T_DATANODE_TIME_DATANODENO,
    T_DATANODE_TIME_SHORTADDR,
    T_DATANODE_TIME_STATICCYCLE,
    T_DATANODE_TIME_WAVECYCLE,
    T_DATANODE_TIME_NODEGROUP,
    T_DATANODE_TIME_NODEINDEX,
    T_DATANODE_TIME_NODEWAVEINDEX,
    T_DATANODE_TIME_STATICTIME,
    T_DATANODE_TIME_STATICSTARTTIME
} T_DATANODE_TIME_Index;

typedef enum {
    T_GATEWAY_INFO_TNAME = 0,
    T_GATEWAY_INFO_GATEWAYMAC,
    T_GATEWAY_INFO_SENSORVER,
    T_GATEWAY_INFO_GATEWAYLOCATION,
    T_GATEWAY_INFO_ZIGBEEPANID,
    T_GATEWAY_INFO_ZIGBEECHANNEL,
    T_GATEWAY_INFO_COMMUNICATIONTYPE,
    T_GATEWAY_INFO_SIGNAL,
    T_GATEWAY_INFO_LOCALIP,
    T_GATEWAY_INFO_SYSTEMVERSION,
    T_GATEWAY_INFO_PROGRAMVERSION,
    T_GATEWAY_INFO_WEBVERSION,
    T_GATEWAY_INFO_SERVERIP,
    T_GATEWAY_INFO_SERVERPORT,
    T_GATEWAY_INFO_STATUS,
    T_GATEWAY_INFO_GATEWAYUPDATE,
    T_GATEWAY_INFO_MAC2,
} T_GATEWAY_INFO_Index;

typedef enum {
    T_BATTERY_INFO_TNAME = 0,
    T_BATTERY_INFO_DATANODENO,
    T_BATTERY_INFO_DIP,
    T_BATTERY_INFO_TEMBOT,
    T_BATTERY_INFO_NODEWORKTIME,
    T_BATTERY_INFO_NODESENDTIME,
    T_BATTERY_INFO_BATTERYVOLTAGE,
    T_BATTERY_INFO_BATTERYUSAGE,
    T_BATTERY_INFO_BATTERYREMAIN,
    T_BATTERY_INFO_TIMESTAMP

} T_BATTERY_INFO_Index;

typedef enum {
    T_SENSOR_BT_INFO_TNAME = 0,
    T_SENSOR_BT_INFO_MAC,
    T_SENSOR_BT_INFO_NAME,
    T_SENSOR_BT_INFO_TYPE,
    T_SENSOR_BT_INFO_STATUS,
    T_SENSOR_BT_INFO_CONFIG_DATE,

} T_SENSOR_BT_INFO_Index;

#endif // SQL_DB_HPP_