SQLite内存数据库+ATTACH附加文件数据库

最新推荐文章于 2025-06-20 21:51:38 发布

原创最新推荐文章于 2025-06-20 21:51:38 发布 · 2.7k 阅读

8 ·

CC 4.0 BY-SA版权

sqlite 专栏收录该内容

32 篇文章

订阅专栏

本文详细介绍了一种在内存数据库和文件数据库之间进行数据交互的方法，包括数据的插入、更新、查询和同步过程。通过具体代码示例展示了如何实现数据库的附加、数据插入、更新、查询及同步操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 数据库附加功能使用

详见：https://blog.youkuaiyun.com/zhanglianyu00/article/details/78436764

2. 内存数据库使用（以下代码经过验证ok，源码是其它网友的，真的是坑，没有经过验证就发到网上...）

const char* file_database_path =  USER_DATA_SQLITE3_PATH"filedb";; //文件数据库存放路径



const char* sql_create_data = "CREATE TABLE testinfo (id TEXT, message TEXT, offset INTEGER, timestamp INTEGER);";
const char* sql_insert_data = "INSERT OR REPLACE INTO MAIN.testinfo VALUES('%s', '%s', %d, %d);";
const char* sql_delete_data = "DELETE FROM MAIN.testinfo WHERE id = '%s'; DELETE FROM filedb.testinfo WHERE id = '%s';"; //删除数据库，需同时删除内存、文件数据库中的内容
const char* sql_update_data = "UPDATE MAIN.testinfo SET message = '%s', offset = %d, timestamp = %d where id = '%s'; UPDATE filedb.testinfo SET message = '%s', offset = %d, timestamp = %d where id = '%s';";//更新数据库，需同时更新内存、文件数据库中的内容
const char* sql_search_data = "SELECT * FROM MAIN.testinfo WHERE timestamp BETWEEN %d AND %d union all SELECT * FROM filedb.testinfo WHERE timestamp BETWEEN %d AND %d;"; //查找数据库，将内存、文件数据库中查找出的内容合并
//const char* sql_search_data = "SELECT * FROM MAIN.testinfo WHERE timestamp BETWEEN %d AND %d;"; //查找数据库，将内存、文件数据库中查找出的内容合并
const char* sql_transfer_data = "INSERT OR REPLACE INTO filedb.testinfo SELECT * FROM testinfo;";   //将内存数据库中的信息同步到文件数据库中
const char* sql_delete_memory_table = "DELETE FROM testinfo;";	//内存数据库中的内容同步结束后，清空

sqlite3 *memdb;

#if _MSC_VER
#define snprintf _snprintf
#endif

int InsertRecord(int type, const char* id, const char* message, int offset, int timestamp)
{
	int      rc              =  0;
	char*    errMsg          =  NULL;
	char     sqlcmd[512]     =  {0};
	time_t   insertTimestamp =  0;

	snprintf(sqlcmd, sizeof(sqlcmd), sql_insert_data, id, message, offset, timestamp);
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		//rintf(stderr, "cat't add record to memory database %s, sqlcmd=%s, err:%s\n", map_data_table[type].data_table_name, sqlcmd, errMsg);
		return -1;
	}
	TRACE("%s\n", sqlcmd);

	return 0;
}

int UpdateRecord(int type, const char* id, const char* message, int offset, int timestamp)
{
	int      rc              = 0;
	char*    errMsg          = NULL;
	char     sqlCmd[512]  = {0};

	snprintf(sqlCmd, sizeof(sqlCmd), sql_update_data, message, offset, timestamp, id, message, offset, timestamp, id);
	rc = sqlite3_exec(memdb, sqlCmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		//fprintf(stderr, "cat't update record %s:%s\n", map_data_table[type].data_table_name, errMsg);
		return -1;
	}
	TRACE("%s\n", sqlCmd);

	return 0;
}

int DeleteRecord(int type, const char* id)
{
	int      rc              =  0;
	char*    errMsg          =  NULL;
	char     sqlcmd[512]     =  {0};

	snprintf(sqlcmd, sizeof(sqlcmd), sql_delete_data, id,  id);
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		//fprintf(stderr, "cat't delete record %s:%s\n", map_data_table[type].data_table_name, errMsg);
		return -1;
	}
	TRACE("%s\n", sqlcmd);

	return 0;
}

int QueryMessage(int type, int startTime, int endTime)
{
	int      rc              = 0;
	char     *errMsg         = NULL;
	sqlite3  *filedb         = NULL;
	char**   pRecord         = NULL;
	int      row             = 0;
	int      column          = 0;
	char     sqlcmd[512]     = {0};

//	if (type > VEP_NELEMS(map_data_table) || type < 0) {
//		return -1;
//	}

	rc = sqlite3_open(file_database_path, &filedb);

#if 0
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't open database:%s\n", sqlite3_errmsg(filedb));
		sqlite3_close(filedb);
		return -1;
	}
#endif

	snprintf(sqlcmd, sizeof(sqlcmd), sql_search_data,  startTime, endTime,  startTime, endTime);

	//rc = sqlite3_get_table(filedb, sqlcmd, &pRecord, &row, &column, &errMsg);
	rc = sqlite3_get_table(memdb, sqlcmd, &pRecord, &row, &column, &errMsg);
	if (SQLITE_OK != rc) {
		//fprintf(stderr, "cat't get table from%s:%s\n", map_data_table[type].data_table_name, errMsg);
		return -1;
	}

	TRACE("%s\n", sqlcmd);

	TRACE("\nrow = %d, column = %d\n", row, column);
	for (int RowIdx=0; RowIdx<row+1; RowIdx++) //特例：row必须加1，否则无法输出数据字段
	{
		for (int iColIdx=0; iColIdx<column; iColIdx++)
			TRACE("%s ", pRecord[RowIdx*column + iColIdx]);
		TRACE("\n");
	}

	return 0;
}

//定时调用此函数将内存数据中的内容同步到文件数据库
int Flush(){
	int      i            = 0;
	int      rc           = 0;
	char*    errMsg       = NULL;
	char     sqlcmd[512]  = {0};

	snprintf(sqlcmd, sizeof(sqlcmd), sql_transfer_data);
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		//fprintf(stderr, "cat't transfer memory database %s to file databasede:%s\n", map_data_table[i].data_table_name, sqlite3_errmsg(memdb));
		sqlite3_close(memdb);
		return -1;
	}
	TRACE("%s\n", sqlcmd);

	snprintf(sqlcmd, sizeof(sqlcmd), sql_delete_memory_table);
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	TRACE("%s\n", sqlcmd);

	return 0;
}

//创建文件数据库
int CreateDbOnFile()
{
	sqlite3 *db           = NULL;
	int      rc           = 0;
	char*    errMsg       = NULL;
	char     sqlcmd[512]  = {0};
	int      i            = 0;

	rc = sqlite3_open(file_database_path, &db);
#if 0
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't open database:%s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return -1;
	}
#endif

	snprintf(sqlcmd, sizeof(sqlcmd), sql_create_data);
	rc = sqlite3_exec(db, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't create file database testinfo:%s\n", errMsg);
		sqlite3_close(db);
		return -1;
	}
	TRACE("%s\n", sqlcmd);

	sqlite3_close(db);
	return 0;
}

//创建内存数据库
int CreateDbOnMemery()
{
	int      rc           = 0;
	char*    errMsg       = NULL;
	char     sqlcmd[512]  = {0};
	int      i            = 0;

	rc = sqlite3_open(":memory:", &memdb);
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't open database:%s\n", sqlite3_errmsg(memdb));
		sqlite3_close(memdb);
		return -1;
	}

	snprintf(sqlcmd, sizeof(sqlcmd), sql_create_data);
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't create memory database %s\n", errMsg);
		sqlite3_close(memdb);
		return -1;
	}
	TRACE("%s\n", sqlcmd);

	return 0;
}

//解绑数据库
int DetachDb()
{
	int      rc           =  0;
	char*    errMsg       =  NULL;
	char     sqlcmd[512]  =  {0};

	snprintf(sqlcmd, sizeof(sqlcmd), "DETACH '%s'", "filedb");
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		fprintf(stderr, "detach file database failed:%s:%s\n", file_database_path, errMsg);
		sqlite3_close(memdb);
		return -1;
	}

	TRACE("%s\n", sqlcmd);

	return 0;
}

//将文件数据库作为内存数据库的附加数据库
int AttachDb()
{
	int      rc           =  0;
	char*    errMsg       =  NULL;
	char     sqlcmd[512]  =  {0};

	snprintf(sqlcmd, sizeof(sqlcmd), "ATTACH '%s' AS %s", file_database_path, "filedb");
	rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
	if (SQLITE_OK != rc) {
		fprintf(stderr, "cat't attach database %s:%s\n", file_database_path, errMsg);
		sqlite3_close(memdb);
		return -1;
	}

	TRACE("%s\n", sqlcmd);

	return 0;
}

3. main函数

//初始化数据库，分别创建文件数据库、内存数据库并把文件数据库attach到内存数据库上
int main(void)
{
	int retval = 0;

	retval =  CreateDbOnFile();
// 	if (retval != 0) {
// 		return retval;
// 	}

	retval =  CreateDbOnMemery();
	if (retval != 0) {
		return retval;
	}

	retval =  AttachDb();
// 	if (retval != 0) {
// 		return retval;
// 	}

	QueryMessage(1, 2, 4); 

	//1. 增加记录到内存，查看数据库文件表里是否有数据
	InsertRecord(1, "1", "hello", 1, 3);
 	InsertRecord(1, "2", "world", 1, 3); 
	InsertRecord(1, "3", "Linux", 1, 3); 

	//2. 数据查询：先查看内存，再查看文件
	QueryMessage(1, 2, 4); 

	//3. 内存数据更新到文件里
	Flush(); 

	//4. 数据查询：内存数据更新到文件里
	QueryMessage(1, 2, 4);

	return 0;
}

4. 运行结果

CREATE TABLE testinfo (id TEXT, message TEXT, offset INTEGER, timestamp INTEGER);
CREATE TABLE testinfo (id TEXT, message TEXT, offset INTEGER, timestamp INTEGER);
ATTACH 'e:\fafiles-45\data\sqlite3\filedb' AS filedb
SELECT * FROM MAIN.testinfo WHERE timestamp BETWEEN 2 AND 4 union all SELECT * FROM filedb.testinfo WHERE timestamp BETWEEN 2 AND 4;

row = 0, column = 0

INSERT OR REPLACE INTO MAIN.testinfo VALUES('1', 'hello', 1, 3);
INSERT OR REPLACE INTO MAIN.testinfo VALUES('2', 'world', 1, 3);
INSERT OR REPLACE INTO MAIN.testinfo VALUES('3', 'Linux', 1, 3);
SELECT * FROM MAIN.testinfo WHERE timestamp BETWEEN 2 AND 4 union all SELECT * FROM filedb.testinfo WHERE timestamp BETWEEN 2 AND 4;

row = 3, column = 4
id message offset timestamp 
1 hello 1 3 
2 world 1 3 
3 Linux 1 3 
INSERT OR REPLACE INTO filedb.testinfo SELECT * FROM testinfo;
DELETE FROM testinfo;
SELECT * FROM MAIN.testinfo WHERE timestamp BETWEEN 2 AND 4 union all SELECT * FROM filedb.testinfo WHERE timestamp BETWEEN 2 AND 4;

row = 3, column = 4
id message offset timestamp 
1 hello 1 3 
2 world 1 3 
3 Linux 1 3