mysql blob 存结构体_sqlite使用blob类型存储/访问 结构体

/*open fire host and slora report data database*/

int open_report_db(void)

{int rv = -1;charsql[SQL_COMMAND_LEN];char *errmsg =NULL;const char *db_file =REPORT_DB_FILE;/*If report database already exist, then we will just open it*/

if( 0==access(db_file, F_OK) )

{if( SQLITE_OK != sqlite3_open(db_file, &s_report_db) )

{

log_err("open report database file '%s' failure\n", db_file);return -2;

}

log_nrml("open report database file '%s' ok\n", db_file);return 0;

}

pthread_mutex_lock(&s_reportdb_lock);/*If report database file not exist, then we create it now*/

if( SQLITE_OK != sqlite3_open(db_file, &s_report_db) )

{

log_err("create report database file '%s' failure\n", db_file);

pthread_mutex_unlock(&s_reportdb_lock);return -2;

}

log_nrml("open report database file '%s' ok\n", db_file);/*Auto increase/decrease*/snprintf(sql,sizeof(sql), "pragma auto_vacuum=2;");if( SQLITE_OK != sqlite3_exec(s_report_db, sql, NULL, NULL, &errmsg) )

{

log_err("set pragma auto_vacuum=2 failure: %s\n", db_file, errmsg);

rv= -4;gotoFAILED;

}/*Create firehost table in the database*/strncpy(sql,"CREATE TABLE firehost(id INTEGER PRIMARY KEY autoincrement, sent BOOL DEFAULT 0, packet BLOB);", sizeof(sql));if( SQLITE_OK != sqlite3_exec(s_report_db, sql, NULL, NULL, &errmsg) )

{

log_err("create firehost table in report database file '%s' failure: %s\n", db_file, errmsg);

rv= -5;gotoFAILED;

}/*Create elora table in the database*/strncpy(sql,"CREATE TABLE elora(id INTEGER PRIMARY KEY autoincrement, sent BOOL DEFAULT 0, packet BLOB);", sizeof(sql));if( SQLITE_OK != sqlite3_exec(s_report_db, sql, NULL, NULL, &errmsg) )

{

log_err("create elora table in report database file '%s' failure: %s\n", db_file, errmsg);

rv= -5;gotoFAILED;

}

log_nrml("create report database file '%s' ok\n", db_file);

pthread_mutex_unlock(&s_reportdb_lock);return 0;

FAILED:

log_err("Create report database '%s' failure and remove it\n", db_file);

sqlite3_close(s_report_db);

sqlite3_free(errmsg);

unlink(db_file);

pthread_mutex_unlock(&s_reportdb_lock);returnrv;

}int add_reportdb_firehost(firehost_pack_t *pack)

{intrv;

sqlite3_stmt*stat =NULL;if( !pack )

{

log_err("invalid input arguments\n");return -1;

}if(!s_report_db)

{

log_nrml("report database not opened, try to open it now\n");if( open_report_db() < 0)return -2;

}

pthread_mutex_lock(&s_reportdb_lock);

rv= sqlite3_prepare_v2(s_report_db, "insert into firehost(packet)values(?)", -1, &stat, NULL);if(SQLITE_OK!=rv || !stat)

{

log_err("firehost sqlite3_prepare_v2 failure\n");

rv= -3;goto out;

}

cp_log_dump(LOG_LEVEL_NRML, (char *)pack, sizeof(*pack));if( SQLITE_OK != sqlite3_bind_blob(stat, 1, pack, sizeof(*pack), NULL) )

{

log_err("firehost sqlite3_bind_blob failure\n");

rv= -4;goto out;

}

rv=sqlite3_step(stat);if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )

{

log_err("firehost sqlite3_step failure\n");

rv= -5;goto out;

}

sqlite3_finalize(stat);

pthread_mutex_unlock(&s_reportdb_lock);

log_nrml("add new firehost report data into database ok\n");return 0;out:

pthread_mutex_unlock(&s_reportdb_lock);

log_nrml("add new firehost report data into database failure, rv=%d\n", rv);returnrv;

}int mark_reportdb_firehost_sent(intrecord_id)

{charsql[SQL_COMMAND_LEN];char *errmsg =NULL;if(record_id <= 0)

{

log_err("invalid input arguments\n");return -1;

}if(!s_report_db)

{

log_nrml("report database not opened, try to open it now\n");if( open_report_db() < 0)return -2;

}

pthread_mutex_lock(&s_reportdb_lock);

memset(sql,0, sizeof(sql));

snprintf(sql,sizeof(sql), "update firehost set sent=1 where id=%d;", record_id);if( SQLITE_OK != sqlite3_exec(s_report_db, sql, NULL, NULL, &errmsg) )

{

log_err("mark firehost packet[%d] already sent from database failure: %s\n", record_id, errmsg);

sqlite3_free(errmsg);

pthread_mutex_unlock(&s_reportdb_lock);return -2;

}

pthread_mutex_unlock(&s_reportdb_lock);

log_nrml("mark firehost packet[%d] already sent from database ok\n", record_id);return 0;

}int query_reportdb_firehost_unsent(int *record_id, firehost_pack_t *pack)

{intrv;

sqlite3_stmt*stat =NULL;const void *blob_ptr;if( !record_id || !pack )

{

log_err("invalid input arguments\n");return -1;

}if(!s_report_db)

{

log_nrml("report database not opened, try to open it now\n");if( open_report_db() < 0)return -2;

}

pthread_mutex_lock(&s_reportdb_lock);/*Only query the first unsent packet record*/rv= sqlite3_prepare_v2(s_report_db, "select id,packet from firehost where sent=0 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 id*/

*record_id = sqlite3_column_int(stat, 0);/*2rd argument<1> means first segement is id*/

//blob_bytes = sqlite3_column_bytes(stat, 1 );

blob_ptr = sqlite3_column_blob(stat, 1);

memcpy(pack, blob_ptr,sizeof(*pack));

log_trace("Read firehost packet from database:\n");

cp_log_dump(LOG_LEVEL_TRACE, (char *)pack, sizeof(*pack));

sqlite3_finalize(stat);

pthread_mutex_unlock(&s_reportdb_lock);

log_nrml("query new firehost report data into database ok\n");return 0;out:

pthread_mutex_unlock(&s_reportdb_lock);

log_nrml("query new firehost report data into database failure, rv=%d\n", rv);returnrv;

}