From 805b28fc2e633bcd9c823a4b23614378ebfd50a1 Mon Sep 17 00:00:00 2001
From: guowenxue <guowenxue@gmail.com>
Date: Tue, 12 May 2026 16:52:28 +0800
Subject: [PATCH] update APUE socket project
---
project/2.socketd/booster/database.c | 262 +++++++++++++++++++++++++++++----------------------
1 files changed, 148 insertions(+), 114 deletions(-)
diff --git a/project/2.socketd/booster/database.c b/project/2.socketd/booster/database.c
index 93f386a..b114da4 100644
--- a/project/2.socketd/booster/database.c
+++ b/project/2.socketd/booster/database.c
@@ -32,7 +32,7 @@
* $db_file: sqlite database file name
* return value: <0: failure 0:ok
* */
-int database_init(const char *db_file)
+int db_open(const char *db_file)
{
char sql[SQL_COMMAND_LEN]={0};
char *errmsg = NULL;
@@ -43,48 +43,40 @@
return -1;
}
- /*+------------------------------------------+
- *| database already exist, just open it |
- *+------------------------------------------+*/
- if( 0==access(db_file, F_OK) )
+ /* open database */
+ if( SQLITE_OK != sqlite3_open_v2(db_file, &s_clidb, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL) )
{
- 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);
+ log_error("open/create database '%s' failed: %s\n", db_file, sqlite3_errmsg(s_clidb));
+ sqlite3_close(s_clidb);
return -2;
}
- /* SQLite continues without syncing as soon as it has handed data off to the operating system */
+ /* ---------------------------------------------
+ * SQLite performance & storage tuning
+ * --------------------------------------------- */
+
+ /* Disable synchronous for higher performance */
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);
+
+ /* ---------------------------------------------
+ * Create table if not exists
+ * --------------------------------------------- */
+
+ snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS %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 */
+ log_error("create table '%s' failed: %s\n", TABLE_NAME, errmsg);
+ sqlite3_free(errmsg); /* free errmsg */
+ sqlite3_close(s_clidb); /* close databse */
+ unlink(db_file); /* remove file */
return -3;
}
- log_info("create and init database file '%s' ok\n", db_file);
+ log_info("database '%s' initialized ok (table=%s)\n", db_file, TABLE_NAME);
return 0;
}
@@ -92,7 +84,7 @@
/* description: close sqlite database handler
* return value: none
*/
-void database_term(void)
+void db_close(void)
{
log_warn("close sqlite database now\n");
sqlite3_close(s_clidb);
@@ -101,164 +93,206 @@
}
-/* description: push a blob packet into database
+/*
+ * description: write a blob packet into database
* input args:
- * $pack: blob packet data address
- * $size: blob packet data bytes
- * return value: <0: failure 0:ok
+ * pack: blob packet data address
+ * size: blob packet data length
+ * return value:
+ * <0: failure
+ * 0: ok
*/
-int database_push_packet(void *pack, int size)
+int db_write(const void *pack, int size)
{
- char sql[SQL_COMMAND_LEN]={0};
- int rv = 0;
- sqlite3_stmt *stat = NULL;
+ static sqlite3_stmt *stmt = NULL;
+ int rv = 0;
- if( !pack || size<=0 )
+ if (!pack || size <= 0)
{
- log_error("%s() Invalid input arguments\n", __func__);
+ log_error("%s(): invalid input arguments\n", __func__);
return -1;
}
- if( ! s_clidb )
+ 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)
+ /* Prepare statement once */
+ if (!stmt)
{
- log_error("blob add sqlite3_prepare_v2 failure\n");
- rv = -2;
- goto OUT;
+ const char *sql = "INSERT INTO " TABLE_NAME " (packet) VALUES (?);";
+ if( SQLITE_OK != sqlite3_prepare_v2(s_clidb, sql, -1, &stmt, NULL) )
+ {
+ log_error("sqlite3_prepare_v2 failed: %s\n", sqlite3_errmsg(s_clidb));
+ return -3;
+ }
}
- if( SQLITE_OK != sqlite3_bind_blob(stat, 1, pack, size, NULL) )
+ /* Bind blob */
+ if( SQLITE_OK != sqlite3_bind_blob(stmt, 1, pack, size, SQLITE_STATIC) )
{
- 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");
+ log_error("sqlite3_bind_blob failed: %s\n", sqlite3_errmsg(s_clidb));
rv = -4;
- goto OUT;
+ goto out;
}
-OUT:
- sqlite3_finalize(stat);
+ /* Execute */
+ if( SQLITE_DONE != sqlite3_step(stmt) )
+ {
+ log_error("sqlite3_step failed: %s\n", sqlite3_errmsg(s_clidb));
+ rv = -5;
+ goto out;
+ }
- if( rv < 0 )
- log_error("add new blob packet into database failure, rv=%d\n", rv);
+ rv = 0;
+out:
+ sqlite3_clear_bindings(stmt);
+ sqlite3_reset(stmt);
+
+ if (rv == 0)
+ log_debug("add new blob packet(size=%d) into database ok\n", size);
else
- log_info("add new blob packet into database ok\n");
+ log_error("add new blob packet(size=%d) into database failed, rv=%d\n", rv);
return rv;
}
-/* description: pop the first blob packet from database
+/*
+ * description: read the first blob packet and its rowid 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
+ * pack : blob output buffer
+ * size : buffer size
+ * bytes : actual blob size (output)
+ * rowid : rowid of the record (output)
+ *
+ * return value:
+ * <0 : failure
+ * 0 : ok
*/
-int database_pop_packet(void *pack, int size, int *bytes)
+int db_read(void *pack, int size, int *bytes, int *rowid)
{
- char sql[SQL_COMMAND_LEN]={0};
- int rv = 0;
- sqlite3_stmt *stat = NULL;
- const void *blob_ptr;
+ static sqlite3_stmt *stmt = NULL;
+ const void *blob_ptr;
+ int rv = 0;
- if( !pack || size<=0 )
+ if (!pack || size <= 0 || !bytes || !rowid)
{
- log_error("%s() Invalid input arguments\n", __func__);
+ log_error("%s(): invalid input arguments\n", __func__);
return -1;
}
- if( ! s_clidb )
+ 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 WHERE rowid = (SELECT rowid FROM %s LIMIT 1);", TABLE_NAME, TABLE_NAME);
- rv = sqlite3_prepare_v2(s_clidb, sql, -1, &stat, NULL);
- if(SQLITE_OK!=rv || !stat)
+ /* Prepare statement once */
+ if (!stmt)
{
- log_error("firehost sqlite3_prepare_v2 failure\n");
- rv = -3;
+ const char *sql = "SELECT rowid, packet FROM " TABLE_NAME " ORDER BY rowid ASC LIMIT 1;";
+ if( SQLITE_OK != sqlite3_prepare_v2(s_clidb, sql, -1, &stmt, NULL) )
+ {
+ log_error("sqlite3_prepare_v2 failed: %s\n", sqlite3_errmsg(s_clidb));
+ return -3;
+ }
+ }
+
+ /* Step to first row */
+ if ( SQLITE_ROW != sqlite3_step(stmt) )
+ {
+ rv = -4;
goto out;
}
- rv = sqlite3_step(stat);
- if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
+ /* get rowid */
+ *rowid = (int)sqlite3_column_int64(stmt, 0);
+
+ /* get blob */
+ blob_ptr = sqlite3_column_blob(stmt, 1);
+ *bytes = sqlite3_column_bytes(stmt, 1);
+
+ if (!blob_ptr || *bytes <= 0)
{
- log_error("firehost sqlite3_step failure\n");
+ log_error("invalid blob data (bytes=%d)\n", *bytes);
rv = -5;
goto out;
}
- /* 1rd argument<0> means first segement is packet */
- blob_ptr = sqlite3_column_blob(stat, 0);
- if( !blob_ptr )
+ if (*bytes > size)
{
+ log_error("blob too large (%d > %d)\n", *bytes, size);
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);
+ sqlite3_reset(stmt);
return rv;
}
-/* description: remove the first blob packet from database
- * input args: none
+/*
+ * description: remove a blob packet by rowid
+ * input args:
+ * rowid : rowid of the record to delete
* return value: <0: failure 0:ok
*/
-int database_del_packet(void)
+int db_remove(int rowid)
{
- char sql[SQL_COMMAND_LEN]={0};
- char *errmsg = NULL;
+ char sql[SQL_COMMAND_LEN] = {0};
+ char *errmsg = NULL;
+ int changes = 0;
+ static int count = 0;
- if( ! s_clidb )
+ if (!s_clidb)
{
log_error("sqlite database not opened\n");
- return -2;
+ return -1;
}
- /* remove packet from db */
- memset(sql, 0, sizeof(sql));
- snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE rowid = (SELECT rowid FROM %s LIMIT 1);", TABLE_NAME, TABLE_NAME);
- if( SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, 0, &errmsg) )
+ if (rowid <= 0)
{
- log_error("delete first blob packet from database failure: %s\n", errmsg);
- sqlite3_free(errmsg);
+ log_error("invalid rowid (%d)\n", rowid);
return -2;
}
- log_warn("delete first blob packet from database ok\n");
- /* Vacuum the database */
- sqlite3_exec(s_clidb, "VACUUM;", NULL, 0, NULL);
+ /* Execute SQL */
+ snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE rowid = %d;", TABLE_NAME, rowid);
+ if (SQLITE_OK != sqlite3_exec(s_clidb, sql, NULL, NULL, &errmsg))
+ {
+ log_error("remove packet failed (rowid=%d): %s\n",
+ rowid, errmsg ? errmsg : "unknown error");
+ sqlite3_free(errmsg);
+ return -3;
+ }
+
+ /* Check how many rows were actually deleted */
+ changes = sqlite3_changes(s_clidb);
+ if (changes == 0)
+ {
+ log_warn("no record deleted (rowid=%d)\n", rowid);
+ return -4;
+ }
+
+ log_debug("remove packet ok (rowid=%d)\n", rowid);
+
+ /*
+ * Optional: vacuum after N successful deletions
+ * WARNING: VACUUM is expensive and blocks DB access
+ */
+ if (++count >= 500)
+ {
+ log_info("trigger database VACUUM\n");
+ sqlite3_exec(s_clidb, "VACUUM;", NULL, 0, NULL);
+ count = 0;
+ }
return 0;
}
--
Gitblit v1.9.1