表结构
创建表的代码为
"CREATE TABLE IF NOT EXISTS %s (data blob, name varchar(%d), id INTEGER PRIMARY KEY, mask blob, %s INTEGER)",
DATABASE_TABLE, NAME_LEN, DATABASE_VERSION
创建好后为:
从图中我们可以看出,这个表有以下几列:
data blob
name varchar(256)
id INTEGER PRIMARY KEY
mask blob
version_0 INTEGER
插入函数
int database_insert(void *data, size_t size, const char *name, size_t n_size, int id, bool sync_flag, void *mask, size_t mask_size)
{
char cmd[256];
sqlite3_stmt *stat = NULL;
//名字是否过长
if (n_size > NAME_LEN) {
printf("%s n_size error\n", __func__);
return -1;
}
//加线程锁,保证线程安全
pthread_mutex_lock(&g_mutex);
//SQL语句,用REPLACE
snprintf(cmd, sizeof(cmd), "REPLACE INTO %s VALUES(?, '%s', %d, ?, 0);", DATABASE_TABLE, name, id);
//创建并初始化
if (sqlite3_prepare(g_db, cmd, -1, &stat, 0) != SQLITE_OK) {
pthread_mutex_unlock(&g_mutex);
return -1;
}
//事务,要么全执行,要么全不执行
sqlite3_exec(g_db, "begin transaction", NULL, NULL, NULL);
//数据绑定到?
sqlite3_bind_blob(stat, 1, data, size, NULL);
sqlite3_bind_blob(stat, 2, mask, mask_size, NULL);
//执行sql语句
sqlite3_step(stat);
//释放内存,否则会残留
sqlite3_finalize(stat);
//提交事务
sqlite3_exec(g_db, "commit transaction", NULL, NULL, NULL);
//同步
if (sync_flag) {
sync();
database_bak();
}
//解锁
pthread_mutex_unlock(&g_mutex);
return 0;
}
这样,我们创建一个size为1032 byte 的随机数据,名字为 username1,保存到数据库看看
int i = 0;
char data[1032] = {0};
char mask[1032] = {0};
char *name = "username1";
int id = 10;
for(i = 0; i < 1032; i++) {
data[i] = random() % 124 + '0';
}
for(i = 0; i < 1032; i++) {
mask[i] = random() % 124 + '0';
}
//插入数据
ret = database_insert(data, 1032, name, 255, id, true, mask, 1032);
printf("ret: %d", ret);
编译,运行, 结果为
sqlite_test/src$ make clean
/sqlite_test/src$ make
Scanning dependencies of target sqlite_test
[ 33%] Building C object CMakeFiles/sqlite_test.dir/database.c.o
[ 66%] Building CXX object CMakeFiles/sqlite_test.dir/main.cpp.o
[100%] Linking CXX executable sqlite_test
[100%] Built target sqlite_test
sqlite_test/src$ ./sqlite_test
ret: 0
因为是用了replace,id为主键,如果插入的数据中,id值在表中已经存在,则只是修改该行不同的数值,如果id值不存在,则创建一行。