From 5e9d03d507aad324a803eb8795e0eed6fb671761 Mon Sep 17 00:00:00 2001
From: guowenxue <guowenxue@gmail.com>
Date: Mon, 10 Jul 2023 15:24:52 +0800
Subject: [PATCH] Merge branch 'master' of http://master.iot-yun.club:8088/r/raspberrypi
---
apue/project_socket/src/sqlite_blob.c | 266 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 266 insertions(+), 0 deletions(-)
diff --git a/apue/project_socket/src/sqlite_blob.c b/apue/project_socket/src/sqlite_blob.c
new file mode 100644
index 0000000..be70c59
--- /dev/null
+++ b/apue/project_socket/src/sqlite_blob.c
@@ -0,0 +1,266 @@
+/********************************************************************************
+ * Copyright: (C) 2020 LingYun IoT System Studio
+ * All rights reserved.
+ *
+ * Filename: sqlite_blob.c
+ * Description: This library used to operate blob packet in sqlite database.
+ *
+ * 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 <stdio.h>
+#include <stdlib.h>
+#include <unistd.h>
+#include <string.h>
+#include "sqlite_blob.h"
+#include "logger.h"
+
+/* Blob packet table name */
+#define TABLE_NAME "PackTable"
+
+/* Use static global handler here in order to simplify the API,
+ * But it will make this library not thread safe
+ */
+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 database_init(const char *db_file)
+{
+ char sql[SQL_COMMAND_LEN]={0};
+ char *errmsg = NULL;
+
+ if( !db_file )
+ {
+ log_error("%s() Invalid input arguments\n", __func__);
+ return -1;
+ }
+
+ /*+------------------------------------------+
+ *| database already exist, just open it |
+ *+------------------------------------------+*/
+ if( 0==access(db_file, F_OK) )
+ {
+ if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
+ {
+ log_error("open database file '%s' failure\n", db_file);
+ return -2;
+ }
+ log_info("open database file '%s' ok\n", db_file);
+ return 0;
+ }
+
+ /*+-----------------------------------------+
+ *| database not exist, create and init it |
+ *+-----------------------------------------+*/
+
+ if( SQLITE_OK != sqlite3_open(db_file, &s_clidb) )
+ {
+ log_error("create 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 */
+ snprintf(sql, sizeof(sql), "CREATE TABLE %s(packet BLOB);", TABLE_NAME);
+ if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg) )
+ {
+ log_error("create data_table in 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 */
+ return -3;
+ }
+
+ log_info("create and init database file '%s' ok\n", db_file);
+ return 0;
+}
+
+
+/* description: close sqlite database handler
+ * return value: none
+ */
+void database_term(void)
+{
+ log_warn("close sqlite database now\n");
+ sqlite3_close(s_clidb);
+
+ return ;
+}
+
+
+/* description: push a blob packet into database
+ * input args:
+ * $pack: blob packet data address
+ * $size: blob packet data bytes
+ * return value: <0: failure 0:ok
+ */
+int blobdb_push_packet(void *pack, int size)
+{
+ char sql[SQL_COMMAND_LEN]={0};
+ int rv = 0;
+ char *errmsg = NULL;
+ sqlite3_stmt *stat = NULL;
+
+ if( !pack || size<=0 )
+ {
+ log_error("%s() Invalid input arguments\n", __func__);
+ return -1;
+ }
+
+ if( ! s_clidb )
+ {
+ log_error("sqlite database not opened\n");
+ return -2;
+ }
+
+ snprintf(sql, sizeof(sql), "insert into %s(packet) values(?)", TABLE_NAME);
+ rv = sqlite3_prepare_v2(s_clidb, sql, -1, &stat, NULL);
+ if(SQLITE_OK!=rv || !stat)
+ {
+ log_error("blob add sqlite3_prepare_v2 failure\n");
+ rv = -2;
+ goto OUT;
+ }
+
+ if( SQLITE_OK != sqlite3_bind_blob(stat, 1, pack, size, NULL) )
+ {
+ log_error("blob add sqlite3_bind_blob failure\n");
+ rv = -3;
+ goto OUT;
+ }
+
+ rv = sqlite3_step(stat);
+ if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
+ {
+ log_error("blob add sqlite3_step failure\n");
+ rv = -4;
+ goto OUT;
+ }
+
+OUT:
+ sqlite3_finalize(stat);
+
+ if( rv < 0 )
+ log_error("add new blob packet into database failure, rv=%d\n", rv);
+ else
+ log_info("add new blob packet into database ok\n");
+
+ return rv;
+}
+
+
+/* description: pop the first blob packet from database
+ * input args:
+ * $pack: blob packet output buffer address
+ * $size: blob packet output buffer size
+ * $byte: blob packet bytes
+ * return value: <0: failure 0:ok
+ */
+int blobdb_pop_packet(void *pack, int size, int *bytes)
+{
+ char sql[SQL_COMMAND_LEN]={0};
+ int rv = 0;
+ sqlite3_stmt *stat = NULL;
+ const void *blob_ptr;
+
+ if( !pack || size<=0 )
+ {
+ log_error("%s() Invalid input arguments\n", __func__);
+ return -1;
+ }
+
+ if( ! s_clidb )
+ {
+ log_error("sqlite database not opened\n");
+ return -2;
+ }
+
+ /* Only query the first packet record */
+ snprintf(sql, sizeof(sql), "select packet from %s limit 0,1;", TABLE_NAME);
+ rv = sqlite3_prepare_v2(s_clidb, sql, -1, &stat, NULL);
+ if(SQLITE_OK!=rv || !stat)
+ {
+ log_error("firehost sqlite3_prepare_v2 failure\n");
+ rv = -3;
+ goto out;
+ }
+
+ rv = sqlite3_step(stat);
+ if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
+ {
+ log_error("firehost sqlite3_step failure\n");
+ rv = -5;
+ goto out;
+ }
+
+ /* 1rd argument<0> means first segement is packet */
+ blob_ptr = sqlite3_column_blob(stat, 0);
+ if( !blob_ptr )
+ {
+ rv = -6;
+ goto out;
+ }
+
+ *bytes = sqlite3_column_bytes(stat, 0);
+
+ if( *bytes > size )
+ {
+ log_error("blob packet bytes[%d] larger than bufsize[%d]\n", *bytes, size);
+ *bytes = 0;
+ rv = -1;
+ }
+
+ memcpy(pack, blob_ptr, *bytes);
+ rv = 0;
+
+out:
+ sqlite3_finalize(stat);
+ return rv;
+}
+
+
+/* description: remove the first blob packet from database
+ * input args: none
+ * return value: <0: failure 0:ok
+ */
+int blobdb_del_packet(void)
+{
+ char sql[SQL_COMMAND_LEN]={0};
+ char *errmsg = NULL;
+
+ if( ! s_clidb )
+ {
+ log_error("sqlite database not opened\n");
+ return -2;
+ }
+
+ /* remove packet from db */
+ memset(sql, 0, sizeof(sql));
+ snprintf(sql, sizeof(sql), "delete from %s limit 0,1;", TABLE_NAME);
+ if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, 0, &errmsg) )
+ {
+ log_error("delete first blob packet from database failure: %s\n", errmsg);
+ sqlite3_free(errmsg);
+ return -2;
+ }
+ log_warn("delete first blob packet from database ok\n");
+
+ /* Vacuum the database */
+ sqlite3_exec(s_clidb, "VACUUM;", NULL, 0, NULL);
+
+ return 0;
+}
+
--
Gitblit v1.9.1