/********************************************************************************
|
* Copyright: (C) 2020 LingYun IoT System Studio
|
* All rights reserved.
|
*
|
* Filename: database.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 "database.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 db_open(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;
|
}
|
|
/* open database */
|
if( SQLITE_OK != sqlite3_open_v2(db_file, &s_clidb, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL) )
|
{
|
log_error("open/create database '%s' failed: %s\n", db_file, sqlite3_errmsg(s_clidb));
|
sqlite3_close(s_clidb);
|
return -2;
|
}
|
|
/* ---------------------------------------------
|
* 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 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 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("database '%s' initialized ok (table=%s)\n", db_file, TABLE_NAME);
|
return 0;
|
}
|
|
|
/* description: close sqlite database handler
|
* return value: none
|
*/
|
void db_close(void)
|
{
|
log_warn("close sqlite database now\n");
|
sqlite3_close(s_clidb);
|
|
return ;
|
}
|
|
|
/*
|
* description: write a blob packet into database
|
* input args:
|
* pack: blob packet data address
|
* size: blob packet data length
|
* return value:
|
* <0: failure
|
* 0: ok
|
*/
|
int db_write(const void *pack, int size)
|
{
|
static sqlite3_stmt *stmt = NULL;
|
int rv = 0;
|
|
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;
|
}
|
|
/* Prepare statement once */
|
if (!stmt)
|
{
|
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;
|
}
|
}
|
|
/* Bind blob */
|
if( SQLITE_OK != sqlite3_bind_blob(stmt, 1, pack, size, SQLITE_STATIC) )
|
{
|
log_error("sqlite3_bind_blob failed: %s\n", sqlite3_errmsg(s_clidb));
|
rv = -4;
|
goto out;
|
}
|
|
/* Execute */
|
if( SQLITE_DONE != sqlite3_step(stmt) )
|
{
|
log_error("sqlite3_step failed: %s\n", sqlite3_errmsg(s_clidb));
|
rv = -5;
|
goto out;
|
}
|
|
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_error("add new blob packet(size=%d) into database failed, rv=%d\n", rv);
|
|
return rv;
|
}
|
|
|
/*
|
* description: read the first blob packet and its rowid from database
|
* input args:
|
* 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 db_read(void *pack, int size, int *bytes, int *rowid)
|
{
|
static sqlite3_stmt *stmt = NULL;
|
const void *blob_ptr;
|
int rv = 0;
|
|
if (!pack || size <= 0 || !bytes || !rowid)
|
{
|
log_error("%s(): invalid input arguments\n", __func__);
|
return -1;
|
}
|
|
if (!s_clidb)
|
{
|
log_error("sqlite database not opened\n");
|
return -2;
|
}
|
|
/* Prepare statement once */
|
if (!stmt)
|
{
|
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;
|
}
|
|
/* 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("invalid blob data (bytes=%d)\n", *bytes);
|
rv = -5;
|
goto out;
|
}
|
|
if (*bytes > size)
|
{
|
log_error("blob too large (%d > %d)\n", *bytes, size);
|
rv = -6;
|
goto out;
|
}
|
|
memcpy(pack, blob_ptr, *bytes);
|
rv = 0;
|
|
out:
|
sqlite3_reset(stmt);
|
return rv;
|
}
|
|
|
/*
|
* description: remove a blob packet by rowid
|
* input args:
|
* rowid : rowid of the record to delete
|
* return value: <0: failure 0:ok
|
*/
|
int db_remove(int rowid)
|
{
|
char sql[SQL_COMMAND_LEN] = {0};
|
char *errmsg = NULL;
|
int changes = 0;
|
static int count = 0;
|
|
if (!s_clidb)
|
{
|
log_error("sqlite database not opened\n");
|
return -1;
|
}
|
|
if (rowid <= 0)
|
{
|
log_error("invalid rowid (%d)\n", rowid);
|
return -2;
|
}
|
|
/* 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;
|
}
|
