数据库Sqlit3基本操作
数据库可视化软件
sudo apt-get install sqlitebrowser
打开数据库
sqlitebrowser test.db
SQLITE3 STMT 机制操作
sqlite3: sqlite3_step 函数
选中表
#include "sqlite3.h"
void FixdCamParam(const std::string& database_path, const std::string& FixedCamPath, std::unordered_map<std::string, int>& id_map){
sqlite3 *db;
sqlite3_stmt * stmt = NULL;
const char *z_tail;
char *err_msg;
int rc = sqlite3_open(db_path.c_str(), &db); // 打开
if(rc != SQLITE_OK){
printf("error sqlite3_open\n");
return;
}
if (sqlite3_prepare_v2(db, "SELECT params, name FROM cameras;", -1, &stmt, &z_tail) != SQLITE_OK) {
printf("error SELECT params, name FROM cameras\n");
return;
}
}
创建表
#define SQLITE3_EXEC(database, sql, callback) \
{ \
char* err_msg = nullptr; \
const int result_code = \
sqlite3_exec(database, sql, callback, nullptr, &err_msg); \
if (result_code != SQLITE_OK) { \
fprintf(stderr, "SQLite error [%s, line %i]: %s\n", __FILE__, __LINE__, \
err_msg); \
sqlite3_free(err_msg); \
} \
}
void CreateCameraTable(sqlite3** save_database_){
const std::string sql =
"CREATE TABLE IF NOT EXISTS cameras"
" (camera_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
" model INTEGER NOT NULL,"
" width INTEGER NOT NULL,"
" height INTEGER NOT NULL,"
" params BLOB,"
" prior_focal_length INTEGER NOT NULL);";
SQLITE3_EXEC(*save_database_, sql.c_str(), nullptr);
}
读取、删除、写入表示例
// 读取表，colmap的database为例
void CamParamRead(const std::string& db_path){
sqlite3 *db; // 一个打开的数据库实例
sqlite3_stmt * stmt = NULL; //stmt语句句柄
const char *z_tail;
char *err_msg;
int rc = sqlite3_open(db_path.c_str(), &db); //打开数据库
if(rc != SQLITE_OK){
printf("error sqlite3_open\n");
return;
}
//进行插入前的准备工作——检查语句合法性
//-1代表系统会自动计算SQL语句的长度
//可以实现对sql语句(模板)的解析和编译，生成了可以被执行的 sql语句实例。
// if (sqlite3_prepare_v2(db, "SELECT camera_id,model FROM cameras;", -1, &stmt, &z_tail) != SQLITE_OK) { //从表cameras中选中params列
if (sqlite3_prepare_v2(db, "SELECT * FROM cameras;", -1, &stmt, &z_tail) != SQLITE_OK) { //从表cameras中选中params列
printf("error SELECT * FROM cameras\n");
return;
}
// SQLITE_ROW：表示当前的返回结果中包含一行的结果数据。
while( sqlite3_step(stmt) == SQLITE_ROW ) {
// The focal length, principal point, and extra parameters. If the camera
// model is not specified, this vector is empty.
std::vector<double> params_(8); //OPENCV
int camera_id = sqlite3_column_int( stmt, 0); //这里得到imageId
int model = sqlite3_column_int(stmt, 1); //这里得到model
int width = sqlite3_column_int(stmt, 2);
int height = sqlite3_column_int(stmt, 3);
int prior_focal_length = sqlite3_column_int(stmt, 5);
const size_t num_params_bytes =
static_cast<size_t>(sqlite3_column_bytes(stmt, 4));
const size_t num_params = num_params_bytes / sizeof(double);
memcpy(params_.data(), sqlite3_column_blob(stmt, 4),
num_params_bytes);
printf("CamParamRead---imageId:%d, model:%d, width:%d, height:%d, prior_focal_length:%d ", camera_id, model, width,height, prior_focal_length );
std::cout<<"params:"<<" ";
for(int i=0;i<params_.size();i++){
std::cout<<params_[i]<<" ";
}
std::cout<<std::endl;
}
sqlite3_close(db);
}
// 重写colmap的database.db camera表
void FixdCamParamWrite(const std::string& db_path, std::vector<CameraInfo> &FixedCamVec){
int camNum = FixedCamVec.size();
printf("camNum:%d\n", camNum);
sqlite3 *db; // 一个打开的数据库实例
sqlite3_stmt * stmt = NULL; //stmt语句句柄
const char *z_tail;
char *err_msg;
int rc = sqlite3_open(db_path.c_str(), &db); //打开数据库
if(rc != SQLITE_OK){
printf("error sqlite3_open\n");
return;
}
// 删除原始cameras记录
if (sqlite3_prepare_v2(db, "DELETE FROM cameras;", -1, &stmt, &z_tail) != SQLITE_OK) {
printf("error DELETE FROM cameras\n");
return;
}
if(sqlite3_step(stmt) != SQLITE_DONE) {
printf("error DELETE FROM cameras\n");
return;
}
for(int camIdx=0; camIdx<camNum; ++camIdx){
std::string sql =
"INSERT INTO cameras(camera_id, model, width, height, params, "
"prior_focal_length) VALUES(?, ?, ?, ?, ?, ?);";
if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, &z_tail) != SQLITE_OK) { //从表cameras中选中params列
printf("error SELECT camera_id, name FROM cameras\n");
return;
}
CameraInfo FixedCam = FixedCamVec[camIdx];
int camera_id = FixedCam.camera_id;
int model = FixedCam.model;
int width = FixedCam.width;
int height = FixedCam.height;
std::string imageName = FixedCam.imageName;
std::vector<double> camParam = FixedCam.Params;
int prior_focal_length = 0;
// std::cout<<"FixdCamParamWrite---imageName:"<<imageName<<" camera_id:"<<camera_id<< std::endl;
sqlite3_bind_int64(stmt, 1, static_cast<sqlite3_int64>(camera_id));
sqlite3_bind_int64(stmt, 2, model);
sqlite3_bind_int64(stmt, 3, static_cast<sqlite3_int64>(width));
sqlite3_bind_int64(stmt, 4, static_cast<sqlite3_int64>(height));
const size_t num_params_bytes = sizeof(double) * camParam.size();
sqlite3_bind_blob(stmt, 5, camParam.data(), static_cast<int>(num_params_bytes), SQLITE_STATIC);
sqlite3_bind_int64(stmt, 6, prior_focal_length);
sqlite3_step(stmt);
sqlite3_reset(stmt);
// uint32_t rowid = sqlite3_last_insert_rowid(db);
// std::cout<<"rowid:"<<rowid<< std::endl;
}
sqlite3_close(db);
}
