由于sqlite对多进程操作支持效果不太理想,在项目中,为了避免频繁读写 文件数据库带来的性能损耗,我们可以采用操作sqlite内存数据库,并将内存数据库定时同步到文件数据库中的方法。
实现思路如下:
1、创建文件数据库;
2、创建内存数据库(文件数据库、内存数据库的内幕表结构需要一致);
3、在内存数据库中attach文件数据库,这样可以保证文件数据库中的内容在内存数据库中可见;
4、对于insert、select操作,在内存数据库中完成,对于delete、update操作,需要同时访问内存、文件数据库;
5、定时将内存数据库中的内容flush到文件数据库。
通过sqlite的cAPI实现代码如下:
- const char* file_database_path = "/home/tom/test/database/filedb"; //文件数据库存放路径
- const char* sql_create_data = "CREATE TABLE testinfo (id TEXT PRIMARY KEY, 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 SELECT * FROM testdb.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;"; //内存数据库中的内容同步结束后,清空
- int InsertRecord(DATA_TYPE 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) {
- fprintf(stderr, "cat't add record to memory database %s, sqlcmd=%s, err:%s ", map_data_table[type].data_table_name, sqlcmd, errMsg);
- return -1;
- }
- return 0;
- }
- int UpdateRecord(DATA_TYPE 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 ", map_data_table[type].data_table_name, errMsg);
- return -1;
- }
- return 0;
- }
- int DeleteRecord(DATA_TYPE 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 ", map_data_table[type].data_table_name, errMsg);
- return -1;
- }
- return 0;
- }
- int QueryMessage(DATA_TYPE 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 (SQLITE_OK != rc) {
- fprintf(stderr, "cat't open database:%s ", sqlite3_errmsg(filedb));
- sqlite3_close(filedb);
- return -1;
- }
- snprintf(sqlcmd, sizeof(sqlcmd), sql_search_data, startTime, endTime, startTime, endTime);
- rc = sqlite3_get_table(filedb, sqlcmd, &pRecord, &row, &column, &errMsg);
- if (SQLITE_OK != rc) {
- fprintf(stderr, "cat't get table from%s:%s ", map_data_table[type].data_table_name, errMsg);
- return -1;
- }
- int i;
- printf("row = %d, column = %d ", row, column);
- for(i = 0; i < 2*column; i++)
- {
- printf("%s ", pRecord[i]);
- }
- printf(" ");
- 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 ", map_data_table[i].data_table_name, sqlite3_errmsg(memdb));
- sqlite3_close(memdb);
- return -1;
- }
- snprintf(sqlcmd, sizeof(sqlcmd), sql_delete_memory_table);
- rc = sqlite3_exec(memdb, sqlcmd, NULL, NULL, &errMsg);
- 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 (SQLITE_OK != rc) {
- fprintf(stderr, "cat't open database:%s ", sqlite3_errmsg(db));
- sqlite3_close(db);
- return -1;
- }
- 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 ", errMsg);
- sqlite3_close(db);
- return -1;
- }
- 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 ", 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 ", errMsg);
- sqlite3_close(memdb);
- return -1;
- }
- 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 ", file_database_path, errMsg);
- sqlite3_close(memdb);
- return -1;
- }
- 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 ", file_database_path, errMsg);
- sqlite3_close(memdb);
- return -1;
- }
- return 0;
- }
- //初始化数据库,分别创建文件数据库、内存数据库并把文件数据库attach到内存数据库上
- int InitSqliteDb()
- {
- int retval = 0;
- retval = CreateDbOnFile();
- if (retval != 0) {
- return retval;
- }
- retval = CreateDbOnMemery();
- if (retval != 0) {
- return retval;
- }
- retval = AttachDb();
- if (retval != 0) {
- return retval;
- }
- return 0;
- }