sqlite使用blob类型存储/访问 结构体
/* open fire host and slora report data database */ int open_report_db(void) { int rv = -1; char sql[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; goto FAILED; } /* 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; goto FAILED; } /* 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; goto FAILED; } 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); return rv; } int add_reportdb_firehost(firehost_pack_t *pack) { int rv; 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); return rv; } int mark_reportdb_firehost_sent(int record_id) { char sql[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) { int rv; 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); return rv; }