From cd10eef059e9d9c34c927f3ac53ac43f4a9716c1 Mon Sep 17 00:00:00 2001
From: guowenxue <guowenxue@gmail.com>
Date: Sat, 06 Jun 2020 18:20:40 +0800
Subject: [PATCH] add TLV client database save and send support
---
prj1_tlv/lylib/sqlite_cli.c | 268 ++++++++++++++++++++++++++++++++++++++
prj1_tlv/lylib/sqlite_cli.h | 60 ++++++++
prj1_tlv/tlv_client.c | 81 ++++++-----
prj1_tlv/makefile | 2
4 files changed, 373 insertions(+), 38 deletions(-)
diff --git a/prj1_tlv/lylib/sqlite_cli.c b/prj1_tlv/lylib/sqlite_cli.c
new file mode 100644
index 0000000..be0bedf
--- /dev/null
+++ b/prj1_tlv/lylib/sqlite_cli.c
@@ -0,0 +1,268 @@
+/********************************************************************************
+ * 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;
+}
+
+
+
diff --git a/prj1_tlv/lylib/sqlite_cli.h b/prj1_tlv/lylib/sqlite_cli.h
new file mode 100644
index 0000000..9476752
--- /dev/null
+++ b/prj1_tlv/lylib/sqlite_cli.h
@@ -0,0 +1,60 @@
+/********************************************************************************
+ * Copyright: (C) 2020 LingYun IoT System Studio
+ * All rights reserved.
+ *
+ * Filename: sqlite_cli.h
+ * 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秒"
+ *
+ ********************************************************************************/
+#ifndef _SQLITE_CLI_H_
+#define _SQLITE_CLI_H_
+
+#include "sqlite3.h"
+#include "tlv_pack.h"
+
+#define SQL_COMMAND_LEN 256
+
+/* description: open or create sqlite database if not exist
+ * input args:
+ * $db_file: sqlite database file name
+ * return value: <0: failure 0:ok
+ * */
+extern int clidb_init(const char *db_file);
+
+
+/* description: close sqlite database handler
+ * return value: none
+ */
+extern void clidb_term(void);
+
+
+/* description: push TLV packet into database
+ * input args:
+ * $pack: tlv packet pointer
+ * return value: <0: failure 0:ok
+ */
+extern int clidb_push_tlvpack(tlv_buf_t *pack);
+
+
+/* description: pop one TLV packet from 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
+ */
+extern int clidb_pop_tlvpack(tlv_buf_t *pack, int *id);
+
+
+/* description: remove sepcified TLV packet from database
+ * input args:
+ * $id: remove item ID
+ * return value: <0: failure 0:ok
+ */
+extern int clidb_del_tlvpack(int id);
+
+
+#endif /* ----- #ifndef _SQLITE_CLI_H_ ----- */
diff --git a/prj1_tlv/makefile b/prj1_tlv/makefile
index 1246642..53bd854 100644
--- a/prj1_tlv/makefile
+++ b/prj1_tlv/makefile
@@ -17,7 +17,7 @@
# Linker flags
LDFLAGS=-L${SUBDIR1} -l${SUBDIR1}
-LDFLAGS+=-lpthread
+LDFLAGS+=-lpthread -lsqlite3
all: banner lib${SUBDIR1}
diff --git a/prj1_tlv/tlv_client.c b/prj1_tlv/tlv_client.c
index d3a307c..a8108bc 100644
--- a/prj1_tlv/tlv_client.c
+++ b/prj1_tlv/tlv_client.c
@@ -22,6 +22,7 @@
#include "ds18b20.h"
#include "socket.h"
#include "tlv_pack.h"
+#include "sqlite_cli.h"
#define PROG_VERSION "1.0.0"
#define RPI_SN "RPI00001"
@@ -130,15 +131,11 @@
}
}
-#if 1
if( !host )
{
printf("ERROR: No argument specify host server address and port, please refer to usage\n");
- // return 1;
+ return 1;
}
-
-
-#endif
/* check program already running or not, if already running then exit, or set running as daemon */
snprintf(pid_file, sizeof(pid_file), "/tmp/%s.pid", progname);
@@ -162,18 +159,16 @@
return 2;
}
+ log_nrml("Program start running\n");
+
/* install signal proc handler */
install_proc_signal();
- log_nrml("Program start running\n");
-
-#if 0
- if( initial_db(&sqldb, db_file) < 0)
+ if( clidb_init("./tlv_client.db")< 0)
{
- log_err("initialise sqlite database failure\n");
+ log_err("initial sqlite database failure\n");
return 3;
}
-#endif
last_time = 0;
@@ -181,7 +176,7 @@
while( !g_signal.stop )
{
time(&cur_time);
- if( cur_time-last_time > 3)
+ if( cur_time-last_time > 10)
{
log_nrml("start sample temperature now.\n");
rv = 0;
@@ -213,44 +208,49 @@
}
}
- if( (tlv.flag&TLV_FLAG_TX) && SOCK_STAT_CONNECTED==sock.status )
+ if( SOCK_STAT_CONNECTED == sock.status )
{
- if(0 == socket_send_tlv_pack(&sock, &tlv, DEF_ACK_TIMEOUT, DEF_RETRYTIMES) )
- {
- tlv.flag = 0;
+ /* send current new sample TLV packet */
+ if( tlv.flag&TLV_FLAG_TX )
+ {
+ if(0 == socket_send_tlv_pack(&sock, &tlv, DEF_ACK_TIMEOUT, DEF_RETRYTIMES) )
+ {
+ tlv.flag = 0;
+ }
}
- }
-#if 0
- /* need to send TLV packet in database now */
- for(i=0; i<5; i++)
- {
- /* get a TLV record from DB and send it by socket */
- /* rv<0: Erorr rv=0: No record >0: record count */
- rv = get_db_tlv_pack(sqldb, tlv, size) ;
- if( rv <= 0)
- break;
-
- if( 0 == socket_send_tlv_pack() )
+ /* get TLV apcket from database and send it by socket, maxim 5 records */
+ for(i=0; i<5; i++)
{
- /* delete the record from DB */
- del_db_tlv_pack();
+ int id;
+ tlv_buf_t tmp;
+
+ if( clidb_pop_tlvpack(&tmp, &id) <= 0 )
+ {
+ break;
+ }
+
+ /* send the TLV packet from database */
+ if(0 == socket_send_tlv_pack(&sock, &tmp, DEF_ACK_TIMEOUT, DEF_RETRYTIMES) )
+ {
+ /* delete the record from DB */
+ clidb_del_tlvpack(id);
+ }
}
+
}
-SAVE_DB:
- if( tlv_buf.flag & TLV_FLAG_DB)
+ if( tlv.flag & TLV_FLAG_DB )
{
- record_db_tlv(sqldb, tlv_buf, tlv_len);
+ clidb_push_tlvpack(&tlv);
+ tlv.flag = 0;
}
-#endif
sleep(1);
}
logger_term();
-
- //db_term();
+ clidb_term();
return 0;
}
@@ -309,6 +309,9 @@
int rv = 0;
char buf[128];
+ if( sock->status != SOCK_STAT_CONNECTED )
+ return -1;
+
log_info("start to send tlv packet from socket now\n");
for(i=0; i<retry_times; i++)
@@ -322,8 +325,12 @@
if( (rv=socket_recv(sock, buf, sizeof(buf), timeout)) < 0 )
{
log_err("read ACK from server failure, rv=%d\n", rv);
- return -2;
+ return 0;
}
+
+ /* todo: parser ACK from buf */
+ if( ACK )
+ break;
}
return 0;
--
Gitblit v1.9.1