使用 Qt/QSqlDatabase 导入 SQLite 数据库 [英] Import SQLite database using Qt/QSqlDatabase

问题描述

我有两个独立的应用程序，一个放置在生产环境中，另一个放置在办公室，我需要获取副本在生产端生成和更新的 sqlite 数据库.

I have two separate applications, one placed at the production another at an office, and I need to get a copy of an sqlite database generated and updated at production side.

到目前为止，我已经尝试了两种方法:

Until now I've tried two approches:

1. 从生产应用程序复制整个 sqlite 文件，并将我的 QSqlDatabase 句柄重定向"到该文件(无法让它工作，许多连接已经打开，但不是全部都可以关闭)
2. 通过网络访问 sqlite-db，查询所有数据并使用 sql 插入缺失的数据(有效，但由于数据量很大，需要太多时间)

是否有可能导入或覆盖现有的数据库(甚至单个表，放置在不同的文件中)，其中仍然有打开的连接?

Are there possibilities to import or maybe override an existing database (or even single tables, placed in different files), where there are still open connections?

使用:Qt 4.8、SQLite、Windows 7、VS2010

Using: Qt 4.8, SQLite, Windows 7, VS2010

推荐答案

所以最后我能够通过使用 sqlite 备份 api(在大多数 Qt 版本中以 .h 和 .c 分发)来达到我的目标.在文档页面 SQLite Backup 上有几个示例，其中从文件中复制数据库到内存数据库，或从内存到文件.就我而言，我使用了以下函数(来自文档页面的 1:1，仅删除了几条评论):

So finally I was able to reach my goal by using the sqlite backup api (which is distributed as .h and .c with most Qt versions). On the documentation page SQLite Backup there are a few examples, where a database is copied either from a file to an in-memory db, or from an in-memory to a file. In my case I used the following function (1:1 from doc page, only several comments removed):

int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave){
  int rc;                   /* Function return code */
  sqlite3 *pFile;           /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;  /* Backup object used to copy data */
  sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
  sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

  /* Open the database file identified by zFilename. Exit early if this fails
  ** for any reason. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    pFrom = (isSave ? pInMemory : pFile);
    pTo   = (isSave ? pFile     : pInMemory);

    pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if( pBackup ){
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pTo);
  }

  (void)sqlite3_close(pFile);
  return rc;
}

获取上述函数所需句柄的唯一附加步骤是:

The only additional steps to get the handle needed in the function above were:

1.从QSqlDatabase获取sqlite-handle

1.Get sqlite-handle from QSqlDatabase

QVariant destVar = database.driver()->handle();

2.检查句柄的有效性并转换为 sqlite3*

2.Check handle for validity and cast to sqlite3*

if(destVar.isValid() && qstrcmp(destVar.typeName(), "sqlite3*") == 0)
{
    sqlite3* destination = *static_cast<sqlite3 **>(destVar.data());
    ...
}

感谢 CL.(谁指出了正确的方向).

Thanks to CL. (who was pointing the right direction).

这篇关于使用 Qt/QSqlDatabase 导入 SQLite 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！