/********************************************************************************
|
* Copyright: (C) 2020 LingYun IoT System Studio
|
* All rights reserved.
|
*
|
* Filename: sqlite_cli.c
|
* Description: This head file is for client to save TLV packet in sqlite.
|
*
|
* Version: 1.0.0(2020年05月13日)
|
* Author: Guo Wenxue <guowenxue@gmail.com>
|
* ChangeLog: 1, Release initial version on "2020年05月13日 12时14分23秒"
|
*
|
********************************************************************************/
|
|
#include "sqlite_cli.h"
|
#include "logger.h"
|
|
static sqlite3 *s_clidb = NULL;
|
|
/* description: open or create sqlite database if not exist
|
* input args:
|
* $db_file: sqlite database file name
|
* return value: <0: failure 0:ok
|
* */
|
int clidb_init(const char *db_file)
|
{
|
char sql[256];
|
char *errmsg = NULL;
|
|
if( !db_file )
|
{
|
log_err("Invalid input arguments\n");
|
return -1;
|
}
|
|
/*+-------------------------------------------------+
|
*| TLV packet database already exist, just open it |
|
*+-------------------------------------------------+*/
|
if( 0==access(db_file, F_OK) )
|
{
|
if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
|
{
|
log_err("open TLV packet database file '%s' failure\n", db_file);
|
return -2;
|
}
|
log_nrml("open TLV packet database file '%s' ok\n", db_file);
|
return 0;
|
}
|
|
/*+---------------------------------------------------+
|
*| TLV packet database not exist, create and init it |
|
*+---------------------------------------------------+*/
|
|
if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
|
{
|
log_err("create TLV packet database file '%s' failure\n", db_file);
|
return -2;
|
}
|
|
/* SQLite continues without syncing as soon as it has handed data off to the operating system */
|
sqlite3_exec(s_clidb, "pragma synchronous = OFF; ", NULL, NULL, NULL);
|
|
/* enable full auto vacuum, Auto increase/decrease */
|
sqlite3_exec(s_clidb, "pragma auto_vacuum = 2 ; ", NULL, NULL, NULL);
|
|
/* Create firehost table in the database */
|
strncpy(sql, "create table tlvpack(packet blob);", sizeof(sql));
|
if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg) )
|
{
|
log_err("create table in tlv packet database file '%s' failure: %s\n", db_file, errmsg);
|
sqlite3_free(errmsg); /* free errmsg */
|
sqlite3_close(s_clidb); /* close databse */
|
unlink(db_file); /* remove database file */
|
}
|
|
log_nrml("create and init TLV packet database file '%s' ok\n", db_file);
|
return 0;
|
}
|
|
|
/* description: close sqlite database handler
|
* return value: none
|
*/
|
void clidb_term(void)
|
{
|
log_warn("close tlv client sqlite database now\n");
|
sqlite3_close(s_clidb);
|
|
return ;
|
|
}
|
|
|
/* description: push TLV packet into database
|
* input args:
|
* $pack: tlv packet pointer
|
* return value: <0: failure 0:ok
|
*/
|
int clidb_push_tlvpack(tlv_buf_t *pack)
|
{
|
int rv = 0;
|
char *errmsg = NULL;
|
sqlite3_stmt *stat = NULL;
|
|
if( !pack )
|
{
|
log_err("Invalid input arguments\n");
|
return -1;
|
}
|
|
if( ! s_clidb )
|
{
|
log_err("client tlv packet database not opened\n");
|
return -2;
|
}
|
|
rv = sqlite3_prepare_v2(s_clidb, "insert into tlvpack(packet) values(?)", -1, &stat, NULL);
|
if(SQLITE_OK!=rv || !stat)
|
{
|
log_err("blob add sqlite3_prepare_v2 failure\n");
|
rv = -2;
|
goto OUT;
|
}
|
|
if( SQLITE_OK != sqlite3_bind_blob(stat, 1, pack, sizeof(*pack), NULL) )
|
{
|
log_err("blob add sqlite3_bind_blob failure\n");
|
rv = -3;
|
goto OUT;
|
}
|
|
rv = sqlite3_step(stat);
|
if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
|
{
|
log_err("blob add sqlite3_step failure\n");
|
rv = -4;
|
goto OUT;
|
}
|
|
OUT:
|
sqlite3_finalize(stat);
|
|
if( rv < 0 )
|
log_nrml("add new TLV packet report data into database failure, rv=%d\n", rv);
|
else
|
log_nrml("add new TLV packet report data into database ok\n");
|
|
return rv;
|
}
|
|
|
/* description: push TLV packet into database
|
* input args:
|
* $pack: tlv packet pointer
|
* $id: item ID pointer, which can be used as key when remove
|
* return value: <0: failure 0:ok
|
*/
|
int clidb_pop_tlvpack(tlv_buf_t *pack, int *id)
|
{
|
int rv = 0;
|
sqlite3_stmt *stat = NULL;
|
const tlv_buf_t *blob_ptr;
|
|
if( !id || ! pack )
|
{
|
log_err("invalid input arguments\n");
|
return -1;
|
}
|
|
if( ! s_clidb )
|
{
|
log_err("client tlv packet database not opened\n");
|
return -2;
|
}
|
|
/* Only query the first packet record */
|
rv = sqlite3_prepare_v2(s_clidb, "select rowid,packet from tlvpack limit 0,1;", -1, &stat, NULL);
|
if(SQLITE_OK!=rv || !stat)
|
{
|
log_err("firehost sqlite3_prepare_v2 failure\n");
|
rv = -3;
|
goto out;
|
}
|
|
rv = sqlite3_step(stat);
|
if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
|
{
|
log_err("firehost sqlite3_step failure\n");
|
rv = -5;
|
goto out;
|
}
|
|
/* 2rd argument<0> means first segement is rowid */
|
*id = sqlite3_column_int(stat, 0);
|
if(*id <= 0)
|
{
|
rv = 0;
|
goto out;
|
}
|
|
/* 2rd argument<1> means first segement is packet */
|
blob_ptr = sqlite3_column_blob(stat, 1);
|
if( !blob_ptr )
|
{
|
rv = 0;
|
goto out;
|
}
|
|
memcpy(pack, blob_ptr, sizeof(*pack));
|
|
log_trace("Read TLV packet from database:\n");
|
logger_dump(LOG_LEVEL_TRACE, (char *)pack->buf, pack->len);
|
|
rv = 1;
|
|
out:
|
sqlite3_finalize(stat);
|
|
if( rv < 0)
|
log_err("query TLV packet from database failure, rv=%d\n", rv);
|
else if( rv > 0 )
|
log_nrml("query TLV packet from database ok\n");
|
|
return rv;
|
|
}
|
|
|
/* description: remove sepcified TLV packet from database
|
* input args:
|
* $id: remove item ID
|
* return value: <0: failure 0:ok
|
*/
|
int clidb_del_tlvpack(int id)
|
{
|
char sql[SQL_COMMAND_LEN];
|
char *errmsg = NULL;
|
|
if( !id )
|
{
|
log_err("invalid input arguments\n");
|
return -1;
|
}
|
|
if( ! s_clidb )
|
{
|
log_err("client tlv packet database not opened\n");
|
return -2;
|
}
|
|
/* remove packet from db */
|
memset(sql, 0, sizeof(sql));
|
snprintf(sql, sizeof(sql), "delete from tlvpack where rowid=%d;", id);
|
if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, 0, &errmsg) )
|
{
|
log_err("delete TLV packet ID[%d] from database failure: %s\n", id, errmsg);
|
sqlite3_free(errmsg);
|
return -2;
|
}
|
log_warn("delete TLV packet ID[%d] from database ok\n", id);
|
|
/* Vacuum the database */
|
sqlite3_exec(s_clidb, "VACUUM;", NULL, 0, NULL);
|
|
return 0;
|
}
|
