小. 快速. 可靠.
选择任意三个.

Using the SQLite Online Backup API

历史上,SQLite数据库的备份(副本)是使用以下方法创建的:

  1. 使用SQLite API(即Shell工具)在数据库文件上建立共享锁.
  2. 使用外部工具(例如,unix'cp'实用程序或DOS'copy'命令)复制数据库文件.
  3. 放弃在步骤1中获得的数据库文件上的共享锁.

此过程在许多情况下都能很好地运行,并且通常非常快. 但是,该技术具有以下缺点:

创建在线备份API就是为了解决这些问题. 在线备份API允许将一个数据库的内容复制到另一个数据库中,从而覆盖目标数据库的原始内容. 复制操作可以增量进行,在这种情况下,不需要在复制期间锁定源数据库,而只需要在实际读取源数据库的短暂时间内锁定源数据库即可. 这样,在进行联机数据库备份时,其他数据库用户可以继续不间断地运行.

在线备份API 在此处记录 . 该页面的其余部分包含两个C语言示例,这些示例说明了API的常见用法及其讨论. 阅读这些示例不能代替阅读API文档!

更新:SQLite版本3.27.0(2019-02-07)中引入的VACUUM INTO命令可以替代备份API.

Example 1: Loading and Saving In-Memory Databases

/*
** This function is used to load the contents of a database file on disk 
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database, 
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are 
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
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 ){

    /* If this is a 'load' operation (isSave==0), then data is copied
    ** from the database file just opened to database pInMemory. 
    ** Otherwise, if this is a 'save' operation (isSave==1), then data
    ** is copied from pInMemory to pFile.  Set the variables pFrom and
    ** pTo accordingly. */
    pFrom = (isSave ? pInMemory : pFile);
    pTo   = (isSave ? pFile     : pInMemory);

    /* Set up the backup procedure to copy from the "main" database of 
    ** connection pFile to the main database of connection pInMemory.
    ** If something goes wrong, pBackup will be set to NULL and an error
    ** code and message left in connection pTo.
    **
    ** If the backup object is successfully created, call backup_step()
    ** to copy data from pFile to pInMemory. Then call backup_finish()
    ** to release resources associated with the pBackup object.  If an
    ** error occurred, then an error code and message will be left in
    ** connection pTo. If no error occurred, then the error code belonging
    ** to pTo is set to SQLITE_OK.
    */
    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);
  }

  /* Close the database connection opened on database file zFilename
  ** and return the result of this function. */
  (void)sqlite3_close(pFile);
  return rc;
}

右边的C函数演示了备份API的最简单,最常见的用法之一:将内存数据库的内容加载并保存到磁盘上的文件中. 在此示例中,备份API的用法如下:

  1. 调用函数sqlite3_backup_init()创建一个sqlite3_backup对象,以在两个数据库之间复制数据(从文件复制到内存数据库,反之亦然).
  2. 使用参数-1调用函数sqlite3_backup_step()可以将整个源数据库复制到目标.
  3. 调用函数sqlite3_backup_finish()清理由sqlite3_backup_init()分配的资源.

错误处理

如果这三个主要备份API例程中的任何一个发生错误,则错误代码消息将附加到目标数据库连接 . 此外,如果sqlite3_backup_step()遇到一个错误,则该错误码是由两个返回sqlite3_backup_step()调用自身通过到后续呼叫,并sqlite3_backup_finish() . 因此,调用sqlite3_backup_finish()不会覆盖由sqlite3_backup_step()存储在目标数据库连接中错误代码 . 示例代码中使用了此功能,以减少所需的错误处理量. sqlite3_backup_step()sqlite3_backup_finish()调用的返回值将被忽略,并且此后指示从目标数据库连接收集的复制操作成功或失败的错误代码.

可能的增强

可以通过至少两种方式来增强此功能的实现:

  1. 未能获取数据库文件zFilename的锁定失败( SQLITE_BUSY错误),并且
  2. 数据库pInMemory和zFilename的页面大小不同的情况可以得到更好的处理.

Since database zFilename is a file on disk, then it may be accessed externally by another process. This means that when the call to sqlite3_backup_step() attempts to read from or write data to it, it may fail to obtain the required file lock. If this happens, this implementation will fail, returning SQLITE_BUSY immediately. The solution would be to register a busy-handler callback or timeout with database connection pFile using sqlite3_busy_handler() or sqlite3_busy_timeout() as soon as it is opened. If it fails to obtain a required lock immediately, sqlite3_backup_step() uses any registered busy-handler callback or timeout in the same way as sqlite3_step() or sqlite3_exec() does.

通常,在覆盖目标内容之前,源数据库和目标数据库的页面大小是否不同并不重要. 在备份操作中,只需更改目标数据库的页面大小即可. 如果目标数据库恰好是内存数据库,则例外. 在这种情况下,如果在备份操作开始时页面大小不同,则该操作将失败并显示SQLITE_READONLY错误. 不幸的是,使用函数loadOrSaveDb()将数据库映像从文件加载到内存数据库中时,可能会发生这种情况.

但是,如果在传递给函数loadOrSaveDb()之前刚刚打开内存数据库pInMemory(因此它完全为空),则仍然可以使用SQLite" PRAGMA page_size"命令更改其页面大小. 函数loadOrSaveDb()可以检测到这种情况,并在调用联机备份API函数之前尝试将内存数据库的页面大小设置为数据库zFilename的页面大小.

Example 2: Online Backup of a Running Database

/*
** Perform an online backup of database pDb to the database file named
** by zFilename. This function copies 5 database pages from pDb to
** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the
** process until the entire database is backed up.
** 
** The third argument passed to this function must be a pointer to a progress
** function. After each set of 5 pages is backed up, the progress function
** is invoked with two integer parameters: the number of pages left to
** copy, and the total number of pages in the source file. This information
** may be used, for example, to update a GUI progress bar.
**
** While this function is running, another thread may use the database pDb, or
** another process may access the underlying database file via a separate 
** connection.
**
** If the backup process is successfully completed, SQLITE_OK is returned.
** Otherwise, if an error occurs, an SQLite error code is returned.
*/
int backupDb(
  sqlite3 *pDb,               /* Database to back up */
  const char *zFilename,      /* Name of file to back up to */
  void(*xProgress)(int, int)  /* Progress function to invoke */     
){
  int rc;                     /* Function return code */
  sqlite3 *pFile;             /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;    /* Backup handle used to copy data */

  /* Open the database file identified by zFilename. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    /* Open the sqlite3_backup object used to accomplish the transfer */
    pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
    if( pBackup ){

      /* Each iteration of this loop copies 5 database pages from database
      ** pDb to the backup database. If the return value of backup_step()
      ** indicates that there are still further pages to copy, sleep for
      ** 250 ms before repeating. */
      do {
        rc = sqlite3_backup_step(pBackup, 5);
        xProgress(
            sqlite3_backup_remaining(pBackup),
            sqlite3_backup_pagecount(pBackup)
        );
        if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
          sqlite3_sleep(250);
        }
      } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

      /* Release resources allocated by backup_init(). */
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pFile);
  }
  
  /* Close the database connection opened on database file zFilename
  ** and return the result of this function. */
  (void)sqlite3_close(pFile);
  return rc;
}

上一个示例中介绍的函数通过一次调用将整个源数据库复制到sqlite3_backup_step() . 这要求在操作期间在源数据库文件上保持读取锁定,以防止任何其他数据库用户写入数据库. 它还在整个副本中保留与数据库pInMemory相关联的互斥锁,从而防止任何其他线程使用它. 本节中的C函数旨在由后台线程或创建联机数据库备份的进程调用,它使用以下方法避免了这些问题:

  1. 调用函数sqlite3_backup_init()创建一个sqlite3_backup对象,以将数据从数据库pDb复制到zFilename标识的备份数据库文件.
  2. 使用参数5调用函数sqlite3_backup_step()可以将数据库pDb的5页复制到备份数据库(文件zFilename).
  3. 如果还有更多页面要从数据库pDb复制,则该函数休眠250毫秒(使用sqlite3_sleep()实用程序),然后返回到步骤2.
  4. 调用函数sqlite3_backup_finish()清理由sqlite3_backup_init()分配的资源.

文件和数据库连接锁定

在上述步骤3的250 ms睡眠期间,数据库文件未保留任何读取锁定,并且未保留与pDb相关联的互斥锁. 这允许其他线程使用数据库连接 pDb和其他连接来写入基础数据库文件.

如果在此函数处于休眠状态时另一个线程或进程将其写入源数据库,则SQLite会检测到此情况,并通常在下次调用sqlite3_backup_step()时重新启动备份进程. 此规则有一个例外:如果源数据库不是内存数据库,并且写入操作是在与备份操作相同的过程中执行的,并且使用相同的数据库句柄(pDb),则目标数据库(即一个使用连接pFile打开的文件)会与源一起自动更新. 然后,在sqlite3_sleep()调用返回之后,就可以继续执行备份过程,就好像什么都没有发生一样.

备份过程是否由于在备份过程中对源数据库的写操作而重新启动,用户可以确定,在备份操作完成后,备份数据库将包含原始数据库的一致且最新的快照. 然而:

backup_remaining()和backup_pagecount()

backupDb()函数使用sqlite3_backup_remaining()和sqlite3_backup_pagecount()函数通过用户提供的xProgress()回调报告其进度. 函数sqlite3_backup_remaining()返回要复制的页面数,而sqlite3_backup_pagecount()返回源数据库(在本例中为pDb打开的数据库)中的页面总数. 因此,该过程的完成百分比可以计算为:

完成= 100%*(pagecount()-剩余())/ pagecount()

sqlite3_backup_remaining()和sqlite3_backup_pagecount()API报告上一次调用sqlite3_backup_step()所存储的值,但它们实际上并未检查源数据库文件. 这意味着如果在调用sqlite3_backup_step()返回之后但使用sqlite3_backup_remaining()和sqlite3_backup_pagecount()返回的值之前,源数据库是由另一个线程或进程写入的,则这些值在技术上可能是错误的. 这通常不是问题.

by  ICOPY.SITE