apue.git - Gitblit

			@@ -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);
			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 database file */
			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,21 +93,23 @@
			}


			/* 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};
			static sqlite3_stmt *stmt = NULL;
			int rv = 0;
			sqlite3_stmt *stat = NULL;

			if( !pack \|\| size<=0 )
			{
			log_error("%s() Invalid input arguments\n", __func__);
			log_error("%s(): invalid input arguments\n", __func__);
			return -1;
			}

			@@ -125,59 +119,68 @@
			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;
			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;
			}

			@@ -187,78 +190,109 @@
			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 )
			{
			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;
			log_error("blob too large (%d > %d)\n", *bytes, size);
			rv = -6;
			goto out;
			}

			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;
			int changes = 0;
			static int count = 0;

			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 */
			/* 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;
			}