303 lines
10 KiB
C++
303 lines
10 KiB
C++
#include <db_ops.h>
|
||
#include <tools.h>
|
||
|
||
sqlite3 *create_db(const char *db_path, bool overwrite_file)
|
||
{
|
||
if (overwrite_file)
|
||
{
|
||
if (remove(db_path) != 0 && errno != ENOENT)
|
||
{
|
||
fprintf(stderr, "Failed to remove old DB file: %s\n", strerror(errno));
|
||
return NULL;
|
||
}
|
||
}
|
||
|
||
sqlite3 *db = NULL;
|
||
int result_code = sqlite3_open(db_path, &db);
|
||
if (result_code != SQLITE_OK)
|
||
{
|
||
fprintf(stderr, "Failed to open DB: %s[%d]\n", sqlite3_errmsg(db), result_code);
|
||
sqlite3_close(db);
|
||
return NULL;
|
||
}
|
||
return db;
|
||
}
|
||
|
||
void create_tables(sqlite3 *db)
|
||
{
|
||
const char *sql_nodes = R"(
|
||
CREATE TABLE IF NOT EXISTS nodes (
|
||
id INTEGER PRIMARY KEY,
|
||
pid INTEGER,
|
||
name TEXT NOT NULL,
|
||
t_x,
|
||
t_y,
|
||
t_z,
|
||
r_x,
|
||
r_y,
|
||
r_z,
|
||
s_x,
|
||
s_y,
|
||
s_z,
|
||
properties TEXT
|
||
);
|
||
)";
|
||
|
||
const char *sql_meshes = R"(
|
||
CREATE TABLE IF NOT EXISTS meshes (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
pid INTEGER,
|
||
name TEXT NOT NULL,
|
||
vertex_count INTEGER NOT NULL,
|
||
index_count INTEGER NOT NULL,
|
||
fbx_id TEXT UNIQUE NOT NULL
|
||
);
|
||
)";
|
||
|
||
const char *sql_materials = R"(
|
||
CREATE TABLE IF NOT EXISTS materials (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
shader_name TEXT,
|
||
fbx_id TEXT UNIQUE NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
)";
|
||
|
||
const char *sql_vertices = R"(
|
||
CREATE TABLE IF NOT EXISTS vertices (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
vert_id INTEGER NOT NULL,
|
||
mesh_id INTEGER NOT NULL,
|
||
position_x REAL NOT NULL,
|
||
position_y REAL NOT NULL,
|
||
position_z REAL NOT NULL,
|
||
FOREIGN KEY(mesh_id) REFERENCES meshes(id) ON DELETE CASCADE
|
||
);
|
||
)";
|
||
|
||
const char *sql_indices = R"(
|
||
CREATE TABLE IF NOT EXISTS indices (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
index_id INTEGER NOT NULL,
|
||
mesh_id INTEGER NOT NULL,
|
||
position_x REAL NOT NULL,
|
||
position_y REAL NOT NULL,
|
||
position_z REAL NOT NULL,
|
||
FOREIGN KEY(mesh_id) REFERENCES meshes(id) ON DELETE CASCADE
|
||
);
|
||
)";
|
||
|
||
const char *sql_material_props = R"(
|
||
CREATE TABLE IF NOT EXISTS material_properties (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
material_id INTEGER NOT NULL,
|
||
key TEXT NOT NULL,
|
||
value TEXT NOT NULL,
|
||
type TEXT NOT NULL,
|
||
FOREIGN KEY(material_id) REFERENCES materials(id) ON DELETE CASCADE,
|
||
UNIQUE(material_id, key)
|
||
);
|
||
)";
|
||
|
||
const char *sql[] = {sql_nodes, sql_meshes, sql_materials, sql_vertices, sql_indices, sql_material_props};
|
||
for (int i = 0; i < sizeof(sql) / sizeof(sql[0]); ++i)
|
||
{
|
||
printf("开始执行：%s", sql[i]);
|
||
// 检查前置条件：数据库连接和SQL语句必须有效
|
||
if (!db)
|
||
{
|
||
printf("db 是空的");
|
||
// 处理空连接错误（如log.Fatalf("db connection is NULL")）
|
||
return; // 或抛异常终止
|
||
}
|
||
if (!sql[i] || strlen(sql[i]) == 0)
|
||
{
|
||
printf("SQL Query 是空的");
|
||
// 处理空SQL错误（如log.Errorf("sql[%d] is NULL/empty", i)）
|
||
continue; // 或终止
|
||
}
|
||
|
||
char *err_msg = nullptr;
|
||
int ret = sqlite3_exec(db, sql[i], nullptr, nullptr, &err_msg);
|
||
printf("db ret: %d, err_msg: %s\n", ret, err_msg);
|
||
if (ret != SQLITE_OK)
|
||
{
|
||
// 必须处理错误：记录具体SQL和错误信息
|
||
// log.Criticalf("SQL error (i=%d): %s, SQL: %s", i, err_msg ? err_msg : "unknown", sql[i]);
|
||
sqlite3_free(err_msg); // 释放错误信息
|
||
return; // 或根据场景决定是否继续
|
||
}
|
||
// 成功执行无需释放err_msg（sqlite3_exec成功时err_msg为NULL）
|
||
}
|
||
}
|
||
|
||
// 函数定义
|
||
int save_mesh_to_table(FttContext *ctx,
|
||
sqlite3 *db,
|
||
uint id,
|
||
uint pid,
|
||
const std::string &name,
|
||
int vertex_count, int index_count,
|
||
const std::string &fbx_id)
|
||
{
|
||
|
||
if (!db || name.empty() || fbx_id.empty())
|
||
return -1;
|
||
|
||
sqlite3_stmt *stmt = nullptr;
|
||
const char *sql = "INSERT OR IGNORE INTO meshes (id, pid, name, vertex_count, index_count, fbx_id) VALUES (?, ?, ?, ?, ?, ?);";
|
||
int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
|
||
if (rc != SQLITE_OK)
|
||
{
|
||
fprintf(stderr, "SQL prepare failed: %s\n", sqlite3_errmsg(db));
|
||
return rc;
|
||
}
|
||
|
||
sqlite3_bind_int64(stmt, 1, id);
|
||
sqlite3_bind_int64(stmt, 2, pid);
|
||
sqlite3_bind_text(stmt, 3, name.c_str(), -1, SQLITE_STATIC);
|
||
sqlite3_bind_int(stmt, 4, vertex_count);
|
||
sqlite3_bind_int(stmt, 5, index_count);
|
||
sqlite3_bind_text(stmt, 6, fbx_id.c_str(), -1, SQLITE_STATIC);
|
||
|
||
rc = sqlite3_step(stmt);
|
||
if (rc != SQLITE_DONE && rc != SQLITE_CONSTRAINT)
|
||
{
|
||
fprintf(stderr, "SQL step failed: %s\n", sqlite3_errmsg(db));
|
||
}
|
||
|
||
sqlite3_finalize(stmt);
|
||
return rc == SQLITE_DONE ? 0 : -1;
|
||
}
|
||
|
||
int save_mesh_vertices(FttContext *ctx, uint mesh_id, std::vector<V3> vertices)
|
||
{
|
||
if (!ctx->db)
|
||
return -1;
|
||
|
||
sqlite3_stmt *stmt = nullptr;
|
||
const char *sql = "INSERT OR IGNORE INTO vertices (mesh_id, vert_id, position_x, position_y, position_z) VALUES (?, ?, ?, ?, ?);";
|
||
int rc = sqlite3_prepare_v2(ctx->db, sql, -1, &stmt, nullptr);
|
||
if (rc != SQLITE_OK)
|
||
{
|
||
fprintf(stderr, "SQL prepare failed: %s\n", sqlite3_errmsg(ctx->db));
|
||
return rc;
|
||
}
|
||
|
||
sqlite3_exec(ctx->db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); // 开启事务
|
||
|
||
for (int i = 0; i < vertices.size(); i++)
|
||
{
|
||
sqlite3_reset(stmt); // 重置语句
|
||
sqlite3_bind_int64(stmt, 1, mesh_id);
|
||
sqlite3_bind_int64(stmt, 2, i); // vert_id使用索引
|
||
sqlite3_bind_double(stmt, 3, std::get<0>(vertices[i])); // 修正为double类型绑定
|
||
sqlite3_bind_double(stmt, 4, std::get<1>(vertices[i]));
|
||
sqlite3_bind_double(stmt, 5, std::get<2>(vertices[i]));
|
||
|
||
rc = sqlite3_step(stmt);
|
||
if (rc != SQLITE_DONE && rc != SQLITE_CONSTRAINT)
|
||
{
|
||
fprintf(stderr, "SQL step failed at vertex %d: %s\n", i, sqlite3_errmsg(ctx->db));
|
||
sqlite3_exec(ctx->db, "ROLLBACK;", nullptr, nullptr, nullptr);
|
||
sqlite3_finalize(stmt);
|
||
return rc;
|
||
}
|
||
}
|
||
|
||
sqlite3_exec(ctx->db, "COMMIT;", nullptr, nullptr, nullptr); // 提交事务
|
||
sqlite3_finalize(stmt);
|
||
return 0;
|
||
}
|
||
|
||
int save_mesh_indices(FttContext *ctx, uint mesh_id, std::vector<unsigned int> indices)
|
||
{
|
||
if (!ctx->db)
|
||
return -1;
|
||
printf("save indices: %d\n", indices.size());
|
||
for (int i = 0; i < indices.size(); i++)
|
||
{
|
||
printf("%d ", indices[i]);
|
||
}
|
||
|
||
// sqlite3_stmt *stmt = nullptr;
|
||
// const char *sql = "INSERT OR IGNORE INTO indices (mesh_id, index_id, position_x, position_y, position_z) VALUES (?, ?, ?, ?, ?);";
|
||
// int rc = sqlite3_prepare_v2(ctx->db, sql, -1, &stmt, nullptr);
|
||
// if (rc != SQLITE_OK) {
|
||
// fprintf(stderr, "SQL prepare failed: %s\n", sqlite3_errmsg(ctx->db));
|
||
// return rc;
|
||
// }
|
||
|
||
// sqlite3_exec(ctx->db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); // 开启事务
|
||
|
||
// for (int i = 0; i < indices.size(); i++) {
|
||
// sqlite3_reset(stmt); // 重置语句
|
||
// sqlite3_bind_int64(stmt, 1, mesh_id);
|
||
// sqlite3_bind_int64(stmt, 2, i); // vert_id使用索引
|
||
// sqlite3_bind_double(stmt, 3, std::get<0>(indices[i])); // 修正为double类型绑定
|
||
// sqlite3_bind_double(stmt, 4, std::get<1>(indices[i]));
|
||
// sqlite3_bind_double(stmt, 5, std::get<2>(indices[i]));
|
||
|
||
// rc = sqlite3_step(stmt);
|
||
// if (rc != SQLITE_DONE && rc != SQLITE_CONSTRAINT) {
|
||
// fprintf(stderr, "SQL step failed at vertex %d: %s\n", i, sqlite3_errmsg(ctx->db));
|
||
// sqlite3_exec(ctx->db, "ROLLBACK;", nullptr, nullptr, nullptr);
|
||
// sqlite3_finalize(stmt);
|
||
// return rc;
|
||
// }
|
||
// }
|
||
|
||
// sqlite3_exec(ctx->db, "COMMIT;", nullptr, nullptr, nullptr); // 提交事务
|
||
// sqlite3_finalize(stmt);
|
||
return 0;
|
||
}
|
||
|
||
int save_nodes(FttContext *ctx, uint id, uint pid,
|
||
const std::string &name,
|
||
double t_x,
|
||
double t_y,
|
||
double t_z,
|
||
double r_x,
|
||
double r_y,
|
||
double r_z,
|
||
double s_x,
|
||
double s_y,
|
||
double s_z,
|
||
const std::string &properties)
|
||
{
|
||
if (!ctx->db)
|
||
return -1;
|
||
|
||
sqlite3_stmt *stmt = nullptr;
|
||
const char *sql = "INSERT OR IGNORE INTO nodes(id,pid,name,t_x,t_y,t_z,r_x,r_y,r_z,s_x,s_y,s_z,properties) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
|
||
int rc = sqlite3_prepare_v2(ctx->db, sql, -1, &stmt, nullptr);
|
||
if (rc != SQLITE_OK)
|
||
{
|
||
fprintf(stderr, "SQL prepare failed: %s\n", sqlite3_errmsg(ctx->db));
|
||
return rc;
|
||
}
|
||
|
||
sqlite3_bind_int64(stmt, 1, id);
|
||
sqlite3_bind_int64(stmt, 2, pid);
|
||
sqlite3_bind_text(stmt, 3, name.c_str(), -1, SQLITE_STATIC);
|
||
sqlite3_bind_double(stmt, 4, t_x);
|
||
sqlite3_bind_double(stmt, 5, t_y);
|
||
sqlite3_bind_double(stmt, 6, t_z);
|
||
sqlite3_bind_double(stmt, 7, r_x);
|
||
sqlite3_bind_double(stmt, 8, r_y);
|
||
sqlite3_bind_double(stmt, 9, r_z);
|
||
sqlite3_bind_double(stmt, 10, s_x);
|
||
sqlite3_bind_double(stmt, 11, s_y);
|
||
sqlite3_bind_double(stmt, 12, s_z);
|
||
sqlite3_bind_text(stmt, 13, properties.c_str(), -1, SQLITE_STATIC);
|
||
|
||
rc = sqlite3_step(stmt);
|
||
if (rc != SQLITE_DONE && rc != SQLITE_CONSTRAINT)
|
||
{
|
||
fprintf(stderr, "SQL step failed: %s\n", sqlite3_errmsg(ctx->db));
|
||
}
|
||
|
||
sqlite3_finalize(stmt);
|
||
return rc == SQLITE_DONE ? 0 : -1;
|
||
} |