webconfig/apps/sql_api.c

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include "sqlite3.h"
#include "sql_api.h"

int callback_db(void *ptr, int count)
{
    usleep(500000);
    return -1;
}

int do_cmd_str(char* sql)
{
	int rc;
	sqlite3* db;
	char *errmsg = NULL;

	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR: open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}
	
	sqlite3_busy_handler(db, callback_db, (void*)db);
	rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if (rc != SQLITE_OK )
	{
		printf("ERROR: %s-%s \r\n", sql, errmsg);
		sqlite3_close(db);
		return -1;
	}
	
	sqlite3_close(db);
	return 0;
}

//初始化设备数据库
int init_device_db(void)
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"%s","create table if not exists device_db(device_id text primary key , device_name text, device_addr integer unique, application_id text);");
	if(do_cmd_str(cmd) != 0)
		return -1;
	return 0;
}

//插入设备到数据库
int insert_device_item(char*device_id,char*device_name,uint8_t device_addr, char* application_id)
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"insert into device_db(device_id,device_name,device_addr,application_id) values('%s','%s',%d,'%s');", device_id,device_name,device_addr,application_id);
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}


//更新设备到数据库
int update_device_by_device_id(char*device_id,char*device_name,uint8_t device_addr,char *application_id)
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"update device_db set device_id = '%s',device_name = '%s', device_addr = %d, application_id = '%s' where device_id = '%s';", device_id,device_name,device_addr,application_id,device_id);
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}

//删除设备通过设备id
int delete_device_by_device_id( char*device_id )
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"delete from device_db where device_id = '%s';", device_id);
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}


int update_attrtab_item_by_device_id(char*device_id, char*attr_id, char*attr_value)
{
	char cmd[MAX_TXET_LEN];
	int rc,nRow,nColumn,i=0;
	sqlite3* db;
	char **result=NULL, *errmsg = NULL;
	
	
	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR:open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}
	
	sprintf(cmd,"select * from device_db limit 0;");
	rc = sqlite3_get_table(db,cmd,&result,&nRow,&nColumn,&errmsg);
	if (rc != SQLITE_OK)
	{
		printf("ERROR: get table %s \r\n", errmsg);
		sqlite3_close(db);
		return -1;
	}

	for(i=0; i<nColumn ;i++)
	{	
		if(strcmp(attr_id, result[i])==0)
			break;
	}
	
	if( i == nColumn)
	{
		sprintf(cmd, "alter table device_db add column %s text", attr_id);
		sqlite3_busy_handler(db, callback_db, (void*)db);
		sqlite3_exec(db,cmd,NULL,NULL,&errmsg);
		
		sprintf(cmd, "update device_db set  %s = '%s' where device_id = '%s';", attr_id,attr_value,device_id);
		sqlite3_busy_handler(db, callback_db, (void*)db);
		sqlite3_exec(db,cmd,NULL,NULL,&errmsg);
	}
	else
	{
		sprintf(cmd, "update device_db set  %s = '%s' where device_id = '%s';", attr_id,attr_value,device_id);
		sqlite3_busy_handler(db, callback_db, (void*)db);
		sqlite3_exec(db,cmd,NULL,NULL,&errmsg);
	}
	
	sqlite3_free_table(result);
	sqlite3_close(db);
	
	return 1;	
}



//更新数据到数据库通过设备id 
int update_tab_item_by_device_id(char*device_id,char *attr_id, char *attr_value)
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"update device_db set %s = '%s' where device_id = '%s';", attr_id,attr_value,device_id);
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}
//更新数据到数据库通过设备地址
int update_tab_item_by_device_addr(uint8_t device_addr,char *attr_id, char *attr_value, uint8_t application_id)
{
	char cmd[MAX_TXET_LEN];
	sprintf(cmd,"update device_db set %s = '%s' where device_addr = %d and application_id = %d;", attr_id,attr_value,device_addr,application_id);
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}

//查询设备列表
int select_all_device(struct json_object **rsp_obj)
{
	int rc,nRow,nColumn,i=0;
	sqlite3* db;
	char **result=NULL, *errmsg = NULL, cmd[MAX_TXET_LEN]={0};
	struct json_object *device_obj =NULL, *device_array=NULL;
	 
	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR:open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}

	sprintf(cmd,"select device_id, device_name, device_addr, application_id from device_db;");
	rc = sqlite3_get_table(db,cmd,&result,&nRow,&nColumn,&errmsg);
	if (rc != SQLITE_OK)
	{
		printf("ERROR: get table %s \r\n", errmsg);
		sqlite3_close(db);
		return -1;
	}

	*rsp_obj = json_object_new_object();
	if(*rsp_obj == NULL)
	{
		sqlite3_free_table(result);
		sqlite3_close(db);
		return -1;
	}

	device_array = json_object_new_array();
	if(device_array == NULL)
	{
		sqlite3_free_table(result);
		sqlite3_close(db);
		json_object_put(*rsp_obj);
		return -1;
	}
	

	for(i=1; i<=nRow ;i++)
	{	
		device_obj = json_object_new_object();
		json_object_object_add(device_obj, "device_id", json_object_new_string(result[i*nColumn+0]));
		json_object_object_add(device_obj, "device_name", json_object_new_string(result[i*nColumn+1]));
		json_object_object_add(device_obj, "device_addr", json_object_new_string(result[i*nColumn+2]));
		json_object_object_add(device_obj, "application_id", json_object_new_string(result[i*nColumn+3]));
		json_object_array_add(device_array, device_obj);
		
	}

	json_object_object_add(*rsp_obj, "devices", device_array);
	json_object_object_add(*rsp_obj, "state", json_object_new_string("success"));


	sqlite3_free_table(result);
	sqlite3_close(db);

	return nRow;
}

int select_device_id_by_device_addr(uint8_t device_addr,uint8_t *device_id, uint8_t application_id)
{
	int rc,nRow,nColumn,i=0;
	sqlite3* db;
	char **result=NULL, *errmsg = NULL, cmd[MAX_TXET_LEN]={0};
	 
	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR:open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}

	sprintf(cmd,"select device_id from device_db where device_addr = %d and application_id = %d;", device_addr,application_id);
	rc = sqlite3_get_table(db,cmd,&result,&nRow,&nColumn,&errmsg);
	if (rc != SQLITE_OK)
	{
		printf("ERROR: get table %s \r\n", errmsg);
		sqlite3_close(db);
		return -1;
	}


	if(nRow == 0)
	{
		memset(device_id,'\0', 17);
	}
	else
	{
		memset(device_id,'\0', 17);
		memcpy(device_id,result[1*nColumn+0],16);
	}

	sqlite3_free_table(result);
	sqlite3_close(db);

	return nRow;
}

//通过设备地址查询设备数据
int select_attr_by_device_addr(uint16_t *buffer, uint8_t device_addr, char*attr_id, uint8_t application_id)
{
	int rc,nRow,nColumn,i=0;
	sqlite3* db;
	char **result=NULL, *errmsg = NULL, cmd[MAX_TXET_LEN]={0};
	uint16_t value =0 ;
	 
	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR:open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}

	sprintf(cmd,"select %s from device_db where device_addr = %d and application_id = %d;", attr_id,device_addr,application_id);
	rc = sqlite3_get_table(db,cmd,&result,&nRow,&nColumn,&errmsg);
	if (rc != SQLITE_OK)
	{
		*buffer =0;
		sqlite3_close(db);
		return -1;
	}

	if(nRow == 0)
	{
	  	*buffer =0;
	}
	else
	{
		*buffer = atoi(result[1*nColumn+0]);
	}
	//sscanf(result[1*nColumn+0],"%04d",&value);
	//*buffer = value;
	//strtol(result[1*nColumn+0], buffer, 16);

	sqlite3_free_table(result);
	sqlite3_close(db);

	return nRow;
}

#if 0
//读取数据库
int get_tab_item(int numofday)
{
	int rc,nRow,nColumn,i=0;
	sqlite3* db;
	char **result;
	char *errmsg = NULL;
	char cmd[512];

	rc = sqlite3_open(SQL_NAME, &db);	
	if ( rc != SQLITE_OK )	
	{		
		printf("ERROR:open sqlite %s \r\n",sqlite3_errmsg(db));		
		sqlite3_close(db);		
		return -1;
	}

	sprintf(cmd,"select * from log_db where date(logtime) between date('now','-%d day','localtime') and date('now','localtime');", numofday);

	rc = sqlite3_get_table(db,cmd,&result,&nRow,&nColumn,&errmsg);
	if (rc != SQLITE_OK)
	{
		printf("ERROR: get table %s \r\n", errmsg);
		sqlite3_close(db);
		return -1;
	}

	for(i=0;i<nRow;i++)
	{
		printf("%s %s %s %s %s\r\n",result[i*nColumn],result[i*nColumn+1],result[i*nColumn+2],result[i*nColumn+3],result[i*nColumn+4]);
		
	}

	sqlite3_free_table(result);
	sqlite3_close(db);

	return 0;
}

#endif

#if 0
//删除30天前的日志
int del_30d_item()
{
	char cmd[512];

	sprintf(cmd,"%s","delete from log_db where date('now', '-30 day') >= date(logtime);");
	if(do_cmd_str(cmd)!=0)
		return -1;
	return 0;
}

//用户写日志
int log_write(char*type,char*level,char *log)
{
	insert_tab_item(type,level,log);
}
//用户读日志
void log_read(struct json_object *root)
{
	get_tab_item(root);
}

#endif

#if 0
int main(void)
{
	uint16_t buffer16;
	uint8_t tmp[5];
	uint16_t attr_value;
	uint8_t j=0;
	uint8_t device_id[17];

	init_device_db();
	//insert_device_item("0102030405060708","test0",1, "lora");
	//insert_device_item("0102030405060709","test1",2, "lora");
	//insert_device_item("010203040506070a","test2",3, "lora");
	
	update_tab_item_by_device_id("0102030405060709","0000", "0");
	update_tab_item_by_device_id("0102030405060709","0001", "1");
	update_tab_item_by_device_id("0102030405060709","0002", "2");
	update_tab_item_by_device_id("0102030405060709","0003", "3");

	update_tab_item_by_device_addr(2, "0000", "8");
	update_tab_item_by_device_addr(2, "0001", "8");
	update_tab_item_by_device_addr(2, "0002", "8");
	update_tab_item_by_device_addr(2, "0003", "8");

	update_tab_item_by_device_addr(3, "0000", "8");

	update_tab_item_by_device_addr(4, "0000", "8");

	select_device_id_by_device_addr(4,device_id);
	printf("addr:%d ---id:0x%s\r\n",4,device_id);

	select_device_id_by_device_addr(2,device_id);
	printf("addr:%d ---id:0x%s\r\n",2,device_id);


	for(j=1; j<4; j++)
	{ 
		sprintf(tmp, "%04x",j);	
		tmp[4]='\0';
		printf("%s----",tmp);
		printf("nrow=%d\r\n",select_attr_by_device_addr(&attr_value, 2, tmp));
		printf("maddr:%d, attrid:%s, buffer16:%d \r\n",2, tmp, attr_value);
	}

	for(j=1; j<4; j++)
	{ 
		sprintf(tmp, "%04x",j);	
		tmp[4]='\0';
		printf("%s----",tmp);
		printf("nrow=%d\r\n",select_attr_by_device_addr(&attr_value, 3, tmp));
		printf("maddr:%d, attrid:%s, buffer16:%d \r\n",3, tmp, attr_value);
	}
	
}
#endif