SmartPDCode/f407/applications/db.c

#include <stddef.h>
#include <string.h>
#include "db.h"
#include "log.h"
#include "sys.h"
#include "lock.h"
#include "paras.h"
#include "sensor.h"
#include "power.h"
#include "breaker.h"
#include "sqlite3.h"

//#define USE_STMTS


enum {
    DB_CFG=0,
    DB_REC,
    
    DB_MAX
};
enum {
    DB_READ=0,
    DB_WRITE,
};

enum {
    CMD_EXIST=0,
    CMD_COUNT,
    CMD_QUERY,
    CMD_OLDEST,
    CMD_NEWEST,
    CMD_CLEAR,
    CMD_REMOVE,
    
    CMD_CREATE,
    CMD_UPDATE,
    
    CMD_MAX,
};
enum {
    CMD_SPEC_CREATE=0,
    CMD_SPEC_UPDATE,
    
    CMD_SPEC_MAX,
};

typedef struct {
    char       *tab;
    int        max_cnt;
    const char *sql[CMD_SPEC_MAX];
}tab_info_t;


//PRAGMA auto_vacuum = 1;
static const char *common_cmd[CMD_MAX]={
    "SELECT NAME FROM sqlite_master WHERE TYPE='TABLE' AND NAME='%s';",
    "SELECT COUNT(*) FROM %s;",
    "SELECT * FROM %s;",
    "SELECT MIN(time) FROM %s;",
    "SELECT MAX(time) FROM %s;",
    "DELETE FROM %s;",
    "DELETE FROM %s %s;",
    NULL,
    NULL,
};
static tab_info_t tab_info[DB_TAB_MAX]={
    {
        "tab_ntp", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "mode INTEGER,"
                                "server TEXT,"
                                "ip TEXT,"
                                "port TEXT,"
                                "cid TEXT,"
                                "user TEXT,"
                                "password TEXT,"
                                "cert TEXT);",
           "INSERT OR REPLACE INTO %s (X,X,X,X,X,X) VALUES (?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_user", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "userID INTEGER,"
                                "userName TEXT,"
                                "userPW TEXT,"
                                "nickName TEXT,"
                                "roleID TEXT,"
                                "createTime TEXT);",
           "INSERT OR REPLACE INTO %s (id, userID, userName, userPW, nickName, roleID, createTime) VALUES (%d,%d,'%s','%s','%s',%d,'%s');",
        }
    },
    
    {
        "tab_shed", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "userID INTEGER,"
                                "userName TEXT,"
                                "userPW TEXT,"
                                "nickName TEXT,"
                                "roleID TEXT,"
                                "createTime TEXT);",
           "INSERT OR REPLACE INTO %s (id, userID, userName, userPW, nickName, roleID, createTime) VALUES (%d,%d,'%s','%s','%s',%d,'%s');",
        }
    },
    
    {
        "tab_snmp", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "proto_type INTEGER,"
                                "enable INTEGER,"
                                "public TEXT,"
                                "TrapEnable INTEGER,"
                                "user TEXT,"
                                "auth_alg TEXT,"
                                "auth_passwd TEXT,"
                                "priv_alg TEXT,"
                                "priv_passwd TEXT,"
                                "nmsIP TEXT);",
           "INSERT OR REPLACE INTO %s (id,proto_type,enable,public,TrapEnable,user,auth_alg,auth_passwd,priv_alg,priv_passwd,nmsIP) VALUES (%d,%d,%d,'%s',%d,'%s','%s','%s','%s','%s','%s');",
        }
    },
    
    {
        "tab_mail", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s("
                            "id INTEGER PRIMARY KEY,"
                            "mode INTEGER,"
                            "account TEXT,"
                            "password TEXT,"
                            "server TEXT,"
                            "port TEXT,"
                            "auth TEXT);",
           "INSERT OR REPLACE INTO %s (id,mode,account,password,server,port,auth) VALUES (?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_mqtt", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "mode INTEGER,"
                                "server TEXT,"
                                "ip TEXT,"
                                "port TEXT,"
                                "cid TEXT,"
                                "user TEXT,"
                                "password TEXT,"
                                "cert TEXT);",
           "INSERT OR REPLACE INTO %s (id,mode,name,server,port,cid,user,password,cert) VALUES (?,?,?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_group", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                              "id INTEGER PRIMARY KEY,"
                                "groupID INTEGER,"
                                "groupName TEXT,"
                                "groupChList TEXT);",
           "INSERT OR REPLACE INTO %s (id, groupID, groupName, groupChList) VALUES (%d,%d,'%s','%s');",
        }
    },
    
    {
        "tab_power", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "name TEXT,"
                                "type INTEGER,"
                                "addr INTEGER,"
                                "ch INTEGER,"
                                "sch INTEGER,"
                                "ph_id INTEGER,"
                                "ph_val INTEGER,"
                                "open_delay INTEGER,"
                                "close_delay INTEGER,"
                                "v_upper.en INTEGER,"
                                "v_upper.val INTEGER,"
                                "v_upper.act INTEGER,"
                                "v_upper.para TEXT,"
                                
                                "v_lower.en INTEGER,"
                                "v_lower.val INTEGER,"
                                "v_lower.act INTEGER,"
                                "v_lower.para TEXT,"
                                
                                "c_upper.en INTEGER,"
                                "c_upper.val INTEGER,"
                                "c_upper.act INTEGER,"
                                "c_upper.para TEXT,"
                                
                                "p_upper.en INTEGER,"
                                "p_upper.val INTEGER,"
                                "p_upper.act INTEGER,"
                                "p_upper.para TEXT,"
                                
                                "w_upper.en INTEGER,"
                                "w_upper.val INTEGER,"
                                "w_upper.act INTEGER,"
                                "w_upper.para TEXT,"
                                
                                "time INTEGER);",
           "INSERT OR REPLACE INTO %s (id,name,type,addr,ch,sch,ph_id,ph_val,open_delay,close_delay,"
                                      "v_upper.en,v_upper.val,v_upper.act,v_upper.para,"
                                      "v_lower.en,v_lower.val,v_lower.act,v_lower.para,"
                                      "c_upper.en,c_upper.val,c_upper.act,c_upper.para,"
                                      "p_upper.en,p_upper.val,p_upper.act,p_upper.para,"
                                      "w_upper.en,w_upper.val,w_upper.act,w_upper.para,"
                                      "time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_sensor", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "name TEXT,"
                                "type INTEGER,"
                                "subtype INTEGER,"
                                "addr INTEGER,"
                                "subaddr INTEGER,"
                                "val_cnt INTEGER,"
                                "value1 REAL,"
                                "thr1_en INTEGER,"
                                "thr1_val REAL,"
                                "thr1_act INTEGER,"
                                "thr1_para TEXT,"
                                "value2 REAL,"
                                "thr2_en INTEGER,"
                                "thr2_val REAL,"
                                "thr2_act INTEGER,"
                                "thr2_para TEXT,"
                                "time INTEGER);",
           "INSERT OR REPLACE INTO tab_sensor (id,name,type,subtype,addr,subaddr,val_cnt,value1,thr1_en,thr1_val,thr1_act,thr1_para,"
                                              "value2,thr2_en,thr2_val,thr2_act,thr2_para,time) "
                                      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_breaker", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "name TEXT,"
                                "addr INTEGER,"
                                "nid INTEGER,"
                                "sw INTEGER,"
                                "time TEXT);",
           "INSERT OR REPLACE INTO %s (id,name,addr,nid,sw,time) VALUES (?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_log", 1,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "type INTEGER,"
                                "content TEXT,"
                                "time INTEGER);",
           "INSERT OR REPLACE INTO %s (id,type,content,time) VALUES (?,?,?,?,?);",
        }
    },
    
    {
        "tab_power_rec", 400*10000,
        {
            "SELECT NAME FROM sqlite_master WHERE TYPE='TABLE' AND NAME='%s';",
            "CREATE TABLE %s ("
                                "id INTEGER PRIMARY KEY,"
                                "voltage REAL,"
                                "current REAL,"
                                "power REAL,"
                                "freq REAL,"
                                "factor REAL,"
                                "consump REAL,"
                                "active REAL,"
                                "reactive REAL,"
                                "ph_out INTEGER,"
                                "status INTEGER,"
                                "nwire INTEGER,"
                                "time INTEGER);",
           "INSERT OR REPLACE INTO %s (id,voltage,current,power,freq,factor,consump,active,reactive,ph_out,status,nwire,time) "
                              "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_alarm_rec", 50*10000,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "ch INTEGER,"
                                "v_upper INTEGER,"
                                "v_lower INTEGER,"
                                "c_upper INTEGER,"
                                "p_upper INTEGER,"
                                "w_upper INTEGER,"
                                "time TEXT);",
           "INSERT OR REPLACE INTO %s (id,ch,v_upper,v_lower,c_upper,p_upper,w_upper,time) VALUES (?,?,?,?,?,?,?,?);",
        }
    },
    
    {
        "tab_log_rec", 50*10000,
        {
            "CREATE TABLE IF NOT EXISTS %s ("
                                "id INTEGER PRIMARY KEY,"
                                "type INTEGER,"
                                "content TEXT,"
                                "time INTEGER);",
           "INSERT OR REPLACE INTO %s (id,type,content,time) VALUES (?,?,?,?);",
        }
    },
    
};

typedef struct {
    sqlite3_stmt *stmt[CMD_MAX];
}tab_stmt_t;

typedef struct {
    sqlite3      *db[DB_MAX];
    lock_t       lck[DB_MAX];
    
#ifdef USE_STMTS
    tab_stmt_t   stmts[DB_TAB_MAX];
#endif
    char         *buf;
    int          buflen;
    uint8_t      inited;
}db_handle_t;
static db_handle_t dbHandle={0};


static int stmt_bind(sqlite3_stmt *stmt, int tab_id, int cmd_id, handle_t list, int max);

/////////////////////////////////////////////////////////////
static int ends_with(char *str, char *suffix)
{
    size_t str_len = strlen(str);
    size_t suffix_len = strlen(suffix);
 
    if (suffix_len > str_len) {
        return 0;
    }
    return strcmp(str + str_len - suffix_len, suffix) == 0;
}
static sqlite3* get_db(db_handle_t *h, int tab_id)
{
    return (tab_id>=DB_TAB_REC_POWER)?h->db[DB_REC]:h->db[DB_CFG];
}
static lock_t get_lck(db_handle_t *h, int tab_id)
{
    return (tab_id>=DB_TAB_REC_POWER)?h->lck[DB_REC]:h->lck[DB_CFG];
}
static node_t get_node(db_handle_t *h, int tab_id)
{
    node_t nd={0};
    paras_data_t *p=paras_get();
    
    switch(tab_id) {
        case DB_TAB_CFG_NTP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_USER:
        {
            
        }
        break;
        
        case DB_TAB_CFG_SHED:
        {
            
        }
        break;
        
        case DB_TAB_CFG_SNMP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_MAIL:
        {
            
        }
        break;
        
        case DB_TAB_CFG_MQTT:
        {
            
        }
        break;
        
        case DB_TAB_CFG_GROUP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_POWER:
        case DB_TAB_REC_POWER:
        {
            power_all_t all;
            
            power_data_get(&all);
            nd.buf = all.pch;
            nd.blen = sizeof(power_ch_t)*all.chs;
            nd.dlen = nd.blen;
            nd.tp = 0;
        }
        break;
        
        case DB_TAB_CFG_SENSOR:
        {
            
        }
        break;
        
        case DB_TAB_CFG_BREAKER:
        {
            
        }
        break;
        
        case DB_TAB_CFG_LOG:
        {
            
        }
        break;        
        
        case DB_TAB_REC_ALARM:
        {
            
        }
        break;
        
        case DB_TAB_REC_LOG:
        {
            
        }
        break;
    }
    
    return nd;
}

static sqlite3_stmt* get_stmt(db_handle_t *h, int tab_id, int cmd)
{
    sqlite3_stmt *s=NULL;
    
#ifdef USE_STMTS
    s =  h->stmts[tab_id].stmt[cmd];
#endif
    return s;
}

static int tab_cmd(db_handle_t *h, int tab_id, int cmd_id, handle_t list)
{
    int r=-1;
    sqlite3_stmt *stmt;
    sqlite3 *db=get_db(h,tab_id);

    if(cmd_id>=CMD_CREATE) {
        snprintf(h->buf, h->buflen, tab_info[tab_id].sql[cmd_id-CMD_CREATE], tab_info[tab_id].tab);
    }
    else {
        snprintf(h->buf, h->buflen, common_cmd[cmd_id], tab_info[tab_id].tab);
    }
    
    r = sqlite3_prepare_v2(db, h->buf, -1, &stmt, NULL);
    if (r == SQLITE_OK) {
        
        if(cmd_id>=CMD_CREATE) {
            stmt_bind(stmt, tab_id, cmd_id, list, 1);
        }
        while(1) {
            r = sqlite3_step(stmt);
            if (r == SQLITE_ROW) {
                r = 0;
            } else if (r == SQLITE_DONE) {
                break;
            } else {
                LOGE("Error: %s\n", sqlite3_errmsg(db));
                break;
            }
        }
        sqlite3_finalize(stmt);
    } else {
        LOGE("Error: %s\n", sqlite3_errmsg(db));
    }
 
    return r;
}

static int stmt_init(db_handle_t *h)
{
    int i,j,r;
    sqlite3 *db=NULL;
    node_t node;
    list_cfg_t lc={0,0,10};
    handle_t list;
    
    list = list_init(&lc);
    for(i=0; i<DB_TAB_MAX; i++) {
        db = get_db(h, i);
        if(db) {
#ifdef USE_STMTS
            for(j=0; j<CMD_MAX; j++) {
                if(j>=CMD_CREATE) {
                    snprintf(h->buf, h->buflen, common_cmd[j-CMD_CREATE], tab_info[i].tab);
                }
                else {
                    snprintf(h->buf, h->buflen, tab_info[i].sql[j], tab_info[i].tab);
                }
                
                r = sqlite3_prepare_v2(db, h->buf, -1, &h->stmts[i].stmt[j], 0);
                if (r != SQLITE_OK) {
                    LOGE("sqlite3_prepare_v2 failed, sql: %s, %s\n", h->buf, sqlite3_errmsg(db));
                }
            }
#endif
            if(tab_cmd(h, i, CMD_EXIST, NULL)!=0) {
                r = tab_cmd(h, i, CMD_CREATE, NULL);
                if(r==0) {
                    
                    if(i<DB_TAB_REC_POWER) {
                        node = get_node(h, i);
                        list_append(list, 0, node.buf, node.dlen);
                        tab_cmd(h, i, CMD_UPDATE, list);
                        list_clear(list);
                    }
                }
            }
        }
    }
    list_free(list);
    
    return 0;
}
static int stmt_deinit(db_handle_t *h)
{
    int i,j;
    
#ifdef USE_STMTS
    for(i=0; i<DB_TAB_MAX; i++) {
        for(j=0; j<CMD_MAX; j++) {
            sqlite3_finalize(h->stmts[i].stmt[j]);
            h->stmts[i].stmt[j] = NULL;
        }
    }
#endif
    
    return 0;
}
static int get_count(db_handle_t *h, int tab_id, int brk)
{
    int r;
    sqlite3_stmt *stmt=get_stmt(h, tab_id, CMD_COUNT);
 
    int count = 0;
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        count++;
        if(brk) break;
    }
    
    return count;
}
static void chr_to_u8(char *c, uint8_t *u)
{
    if(*c>='0' && *c<='9') {
        *u = *c - '0';
    }
    else if(*c>='a' && *c<='f') {
        *u = *c - 'a';
    }
}
static void u8_to_chr(uint8_t *u, char *c)
{
    uint8_t u1=(*u)>>4;
    uint8_t u2=(*u)&0x0f;
    if((*u)&0x80) {
        *c = *(c+1) = 0;
    }
    else {
        *c = (u1>9)?(u1+32):(u1+97);
        *(c+1) = (u2>9)?(u2+32):(u2+97);
    }
}

static void str_to_array(char *s, uint8_t *a, int alen)
{
    int i=0;
    uint8_t c1,c2;
    while(s[i]) {
        chr_to_u8(s+i,&c1);
        chr_to_u8(s+i+1, &c2);
        a[i] = (c1<<4)+c2;
        i += 2;
    }
}
static void array_to_str(uint8_t *a, int alen, char *s)
{
    int i;
    for(i=0; i<alen; i++) {
        u8_to_chr(a+i, s+i*2);
    }
}
static int stmt_bind(sqlite3_stmt *stmt, int tab_id, int cmd_id, handle_t list, int max)
{
    int i,j,k,r=0,rc,idx;
    list_node_t *ln=NULL;
    
    if(cmd_id!=CMD_UPDATE && cmd_id!=CMD_QUERY) {
        return -1;
    }
    
    switch(tab_id) {
        case DB_TAB_CFG_NTP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_USER:
        {
            
        }
        break;
        
        case DB_TAB_CFG_SHED:
        {
            
        }
        break;
        
        case DB_TAB_CFG_SNMP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_MAIL:
        {
            
        }
        break;
        
        case DB_TAB_CFG_MQTT:
        {
            
        }
        break;
        
        case DB_TAB_CFG_GROUP:
        {
            
        }
        break;
        
        case DB_TAB_CFG_POWER:
        {
            char *p=NULL,para[30];
            
            if(cmd_id==CMD_UPDATE) {
                power_ch_t *pch=NULL;
                
                while(1) {
                    rc = list_get_node(list, &ln, 0);
                    if(rc) {
                        break;
                    }
                    pch = (power_ch_t*)ln->data.buf;
                    
                    i = 1;
                    i++;//sqlite3_bind_int(stmt,    i++, p->id);
                    sqlite3_bind_text(stmt,   i++, pch->info.name,   -1, NULL);
                    sqlite3_bind_int(stmt,    i++, pch->info.type);
                    sqlite3_bind_int(stmt,    i++, pch->info.addr);
                    sqlite3_bind_int(stmt,    i++, pch->info.ch);
                    sqlite3_bind_int(stmt,    i++, pch->info.sch);
                    sqlite3_bind_int(stmt,    i++, pch->info.ph_id);
                    sqlite3_bind_int(stmt,    i++, pch->info.ph_val);
                    sqlite3_bind_int(stmt,    i++, pch->info.open_delay);
                    sqlite3_bind_int(stmt,    i++, pch->info.close_delay);
                                              
                    sqlite3_bind_int(stmt,    i++, pch->thr.v_upper.en);
                    sqlite3_bind_double(stmt, i++, pch->thr.v_upper.val);
                    sqlite3_bind_int(stmt,    i++, pch->thr.v_upper.act);
                    array_to_str(pch->thr.v_upper.para,sizeof(pch->thr.v_upper.para),para);
                    sqlite3_bind_text(stmt,   i++, para, -1, NULL);
                    
                    sqlite3_bind_int(stmt,    i++, pch->thr.v_lower.en);
                    sqlite3_bind_double(stmt, i++, pch->thr.v_lower.val);
                    sqlite3_bind_int(stmt,    i++, pch->thr.v_lower.act);
                    array_to_str(pch->thr.v_lower.para,sizeof(pch->thr.v_lower.para),para);
                    sqlite3_bind_text(stmt,   i++, para, -1, NULL);
                    
                    sqlite3_bind_int(stmt,    i++, pch->thr.c_upper.en);
                    sqlite3_bind_double(stmt, i++, pch->thr.c_upper.val);
                    sqlite3_bind_int(stmt,    i++, pch->thr.c_upper.act);
                    array_to_str(pch->thr.c_upper.para,sizeof(pch->thr.c_upper.para),para);
                    sqlite3_bind_text(stmt,   i++, para, -1, NULL);
                    
                    sqlite3_bind_int(stmt,    i++, pch->thr.p_upper.en);
                    sqlite3_bind_double(stmt, i++, pch->thr.p_upper.val);
                    sqlite3_bind_int(stmt,    i++, pch->thr.p_upper.act);
                    array_to_str(pch->thr.p_upper.para,sizeof(pch->thr.p_upper.para),para);
                    sqlite3_bind_text(stmt,   i++, para, -1, NULL);
                    
                    sqlite3_bind_int(stmt,    i++, pch->thr.w_upper.en);
                    sqlite3_bind_double(stmt, i++, pch->thr.w_upper.val);
                    sqlite3_bind_int(stmt,    i++, pch->thr.w_upper.act);
                    array_to_str(pch->thr.w_upper.para,sizeof(pch->thr.w_upper.para),para);
                    sqlite3_bind_text(stmt,   i++, para, -1, NULL);
                    
                    rc = sqlite3_step(stmt);
                    if (rc != SQLITE_ROW) {
                        if (rc != SQLITE_DONE) {
                            LOGE("___data_bind, sqlite3_step port failed, tab_id: %d\n", tab_id);
                            r = -1;
                        }
                        break;
                    }
                }
            }
            else {
                int cnt=0;
                power_ch_t tmp,*pch=NULL;
                
                pch = &tmp;
                while(1) {
                    rc = sqlite3_step(stmt);
                    if (rc != SQLITE_ROW) {
                        if (rc != SQLITE_DONE) {
                            LOGE("___data_bind, sqlite3_step port failed, tab_id: %d\n", tab_id);
                            r = -1;
                        }
                        break;
                    }
 
                    i = 0;
                    i++;//p->id = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    if(p) strcpy(pch->info.name, p); else pch->info.name[0] = 0;
                    
                    pch->info.type        = sqlite3_column_int(stmt, i++);
                    pch->info.addr        = sqlite3_column_int(stmt, i++);
                    pch->info.ch          = sqlite3_column_int(stmt, i++);
                    pch->info.sch         = sqlite3_column_int(stmt, i++);
                    pch->info.ph_id       = sqlite3_column_int(stmt, i++);
                    pch->info.ph_val      = sqlite3_column_int(stmt, i++);
                    pch->info.open_delay  = sqlite3_column_int(stmt, i++);
                    pch->info.close_delay = sqlite3_column_int(stmt, i++);
                    
                    pch->thr.v_upper.en   = sqlite3_column_int(stmt, i++);
                    pch->thr.v_upper.val  = sqlite3_column_double(stmt, i++);
                    pch->thr.v_upper.act  = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    str_to_array(p,pch->thr.v_upper.para, sizeof(pch->thr.v_upper.para));
                    
                    pch->thr.v_lower.en   = sqlite3_column_int(stmt, i++);
                    pch->thr.v_lower.val  = sqlite3_column_double(stmt, i++);
                    pch->thr.v_lower.act  = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    str_to_array(p,pch->thr.v_lower.para, sizeof(pch->thr.v_lower.para));
                    
                    pch->thr.c_upper.en   = sqlite3_column_int(stmt, i++);
                    pch->thr.c_upper.val  = sqlite3_column_double(stmt, i++);
                    pch->thr.c_upper.act  = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    str_to_array(p,pch->thr.c_upper.para, sizeof(pch->thr.c_upper.para));
                    
                    pch->thr.p_upper.en   = sqlite3_column_int(stmt, i++);
                    pch->thr.p_upper.val  = sqlite3_column_double(stmt, i++);
                    pch->thr.p_upper.act  = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    str_to_array(p,pch->thr.p_upper.para, sizeof(pch->thr.p_upper.para));
                    
                    pch->thr.w_upper.en   = sqlite3_column_int(stmt, i++);
                    pch->thr.w_upper.val  = sqlite3_column_double(stmt, i++);
                    pch->thr.w_upper.act  = sqlite3_column_int(stmt, i++);
                    p = (char*)sqlite3_column_text(stmt, i++);
                    str_to_array(p,pch->thr.w_upper.para, sizeof(pch->thr.w_upper.para));
                    
                    cnt++;
                    if(cnt>max) {
                        break;
                    }
                    
                    r = list_append(list, 0, pch, sizeof(power_ch_t));
                    if(r) {
                        LOGE("___ list_append failed, tab_id: %d\n", tab_id);
                        break;
                    }
                }
            }
        }
        break;
        
        case DB_TAB_CFG_SENSOR:
        {
            
            if(cmd_id==CMD_UPDATE) {
                //sensor_data_t *p=(sensor_data_t*)data;
            }
            else {
                
            }
        }
        break;
        
        case DB_TAB_CFG_BREAKER:
        {
            
        }
        break;
        
        case DB_TAB_CFG_LOG:
        {
            
        }
        break;
        
        case DB_TAB_REC_POWER:
        {
            power_ch_t *p=NULL;
            
            uint8_t times=((p->info.type==PDU_AC_I3O3)?3:1);
            
            if(cmd_id==CMD_UPDATE) {
                while(1) {
                    rc = list_get_node(list, &ln, 0);
                    if(rc) {
                        break;
                    }
                    
                    i = 1;
                    for(j=0; j<times; j++) {
                        i++;//sqlite3_bind_int(stmt,    i++, p->id);
                        sqlite3_bind_double(stmt, i++, p->power[j].voltage);
                        sqlite3_bind_double(stmt, i++, p->power[j].current);
                        sqlite3_bind_double(stmt, i++, p->power[j].power);
                        sqlite3_bind_double(stmt, i++, p->power[j].freq);
                        sqlite3_bind_double(stmt, i++, p->power[j].factor);
                        sqlite3_bind_double(stmt, i++, p->power[j].consump);
                        sqlite3_bind_double(stmt, i++, p->power[j].active);
                        sqlite3_bind_double(stmt, i++, p->power[j].reactive);
                        sqlite3_bind_int(stmt,    i++, p->power[j].ph_out);
                        sqlite3_bind_int(stmt,    i++, p->power[j].status);
                        sqlite3_bind_int(stmt,    i++, p->power[j].nwire);
                        
                        rc = sqlite3_step(stmt);
                        if (rc != SQLITE_DONE) {
                            LOGE("___data_bind, sqlite3_step port failed, tab_id: %d\n", tab_id);
                            r = -1; break;
                        }
                    }
                }
            }
            else {
                rc = sqlite3_step(stmt);
                if (rc != SQLITE_DONE) {
                    LOGE("___data_bind, sqlite3_step port failed, tab_id: %d\n", tab_id);
                    r = -1; break;
                }
                
                i = 0;
                for(j=0; j<times; j++) {
                    i++;//pch->id   = sqlite3_column_int(stmt, i++);
                    p->power[j].voltage  = sqlite3_column_double(stmt, i++);
                    p->power[j].current  = sqlite3_column_double(stmt, i++);
                    p->power[j].power    = sqlite3_column_double(stmt, i++);
                    p->power[j].freq     = sqlite3_column_double(stmt, i++);
                    p->power[j].factor   = sqlite3_column_double(stmt, i++);
                    p->power[j].consump  = sqlite3_column_double(stmt, i++);
                    p->power[j].active   = sqlite3_column_double(stmt, i++);
                    p->power[j].reactive = sqlite3_column_double(stmt, i++);
                    p->power[j].ph_out   = sqlite3_column_int(stmt, i++);
                    p->power[j].status   = sqlite3_column_int(stmt, i++);
                    p->power[j].nwire    = sqlite3_column_int(stmt, i++);
                }
            }
        }
        break;
        
        case DB_TAB_REC_ALARM:
        {
            
        }
        break;
        
        case DB_TAB_REC_LOG:
        {
            
        }
        break;
        
        default:
        r = -1;
    }
    
    if(r==0) {
        sqlite3_reset(stmt);
    }
    
    return r;
}

/////////////////////////////////////////////////////////////
int db_init(void)
{
    int i,r;
    sqlite3 *db=NULL;
    char path[100];
    db_handle_t *h=&dbHandle;
    char *errmsg=NULL;
    char *ext[DB_MAX]={"cfg","rec"};
    
    memset(h, 0, sizeof(db_handle_t));
    h->buflen = (1024*2);
    h->buf = (char*)malloc(h->buflen);
    if(!h->buf) {
        LOGE("___db buf malloc failed!\n");
        return -1;
    }
    
    sqlite3_initialize();
    for(i=0; i<DB_MAX; i++) {
        sprintf(path, "%s/%s_%s.db", DB_PATH, DB_NAME, ext[i]);
        r = sqlite3_open(path, &h->db[i]);
        if(r != SQLITE_OK) {
            LOGE("___%s open failed, %s\n", path, sqlite3_errstr(r));
            return -1 ;
        }
        sqlite3_exec(h->db[i], "PRAGMA cache_size=0;" , NULL, NULL, NULL);
        h->lck[i] = lock_init();
    }
    stmt_init(h);
    h->inited = 1;
    
    return 0;
}


int db_deinit(void)
{
    int i;
    db_handle_t *h=&dbHandle;
    
    for(i=0; i<DB_MAX; i++) {
        lock_deinit(h->lck[i]);
        sqlite3_close(h->db[i]);
    }
    stmt_deinit(h);
    
    return 0;
}


int db_load(void)
{
    int i;
    for(i=0; i<DB_MAX; i++) {
        //
    }
    return 0;
}


int db_reset_tab(int tab_id)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}
int db_reset(void)
{
    int i;
    for(i=0; i<DB_TAB_REC_POWER; i++) {
        db_reset_tab(i);
    }
}

int db_clear_tab(int tab_id)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}
int db_clear(void)
{
    int i;
    for(i=DB_TAB_REC_POWER; i<DB_TAB_MAX; i++) {
        db_clear_tab(i);
    }
    return 0;
}


int db_import(int tab_id, char *path)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}


int db_export(int tab_id, char *path)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}


int db_remove(int tab_id, char *contiion)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}


int db_read(int tab_id, char *contiion, handle_t list)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}


int db_write(int tab_id, char *contiion, handle_t list)
{
    int r;
    db_handle_t *h=&dbHandle;
    lock_t lck=get_lck(h,tab_id);
    
    lock_on(lck);
    
    lock_off(lck);
    
    return 0;
}