1. Common backups:
The following method is a relatively simple and commonly used SQLite database backup method, see the following steps:
1). Use the SQLite API or Shell tool to add a shared lock on the source database file.
2). Use the Shell tool (cp or copy) to copy the database file to the backup directory.
3). Unlock the shared lock on the database file.
The above 3 steps can be applied to most scenarios, and the speed is relatively fast, but there are certain rigid defects, such as:
1). All connections that intend to perform write operations on the source database have to be suspended until the entire copy process is finished and the file share lock is released.
2). The data cannot be copied to the in-memory database.
3). During the copying process, once any burst failure occurs on the host where the backup database is located, the backup database may be corrupted.
SQLite provides a set of APIs functions (C interfaces) for online database backup, which can solve the shortcomings of the above methods well. Through this group of functions, the contents in the source database can be copied to another database, while overwriting the data in the target database. The entire copy process can be completed in incremental ways. In this case, the source database does not need to be locked during the entire copy process, but only adds a shared lock when the data is actually read. In this way, other users will not be suspended when accessing the source database.
2. Introduction to online backup APIs:
SQLite provides the following 3 APIs functions for this operation. Here are only the basic usages. For details, please refer to the SQLite official website "APIs Reference" (/c3ref/backup_finish.html)。
1). The function sqlite3_backup_init() is used to create a sqlite3_backup object, which will be passed to the other two functions as a handle to this copy operation.
2). The function sqlite3_backup_step() is used for data copying. If the second parameter of the function is -1, the entire copying process will be completed in a call of the function.
3). The function sqlite3_backup_finish() is used to release the resources applied by the sqlite3_backup_init() function to avoid resource leakage.
If any error occurs during the entire copying process, we can get the specific error code by calling the sqlite3_errcode() function of the destination database connection. In addition, if the sqlite3_backup_step() call fails, since the sqlite3_backup_finish() function does not modify the error code of the current connection, we can get the error code after calling sqlite3_backup_finish(), thereby reducing one error processing in the code. See the following code example (from SQLite official website):
/*
** 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;
}
3. Advanced application skills:
In the above example, we completed the entire copying process through a call to the sqlite3_backup_step() function. This implementation still has the problem of suspending other write access connections mentioned earlier. In order to solve this problem, we will continue to introduce another more advanced implementation method - shard copy, which implements the steps as follows:
1). The function sqlite3_backup_init() is used to create a sqlite3_backup object, which will be passed to the other two functions as a handle to this copy operation.
2). The function sqlite3_backup_step() is called to copy data. Unlike the previous method, the second parameter of the function is no longer -1, but an ordinary positive integer, indicating the number of pages to be copied each time, such as 5.
3). If there are still more pages to be copied after the call of the function sqlite3_backup_step(), we will actively sleep for 250ms and then repeat step 2).
4). The function sqlite3_backup_finish() is used to release the resources applied by the sqlite3_backup_init() function to avoid resource leakage.
In step 3 above, we actively sleep for 250ms. During this period, the copy operation will not hold any read locks on the source database, so that other database connections will not be suspended when writing operations. However, during sleep, if another thread or process writes the source database, SQLite will detect the occurrence of the event, thus restarting the entire copying process the next time the sqlite3_backup_step() function is called. The only exception is that if the source database is not an in-memory database, and the write operation is completed in the same process as the copy operation, and the same database connection handle is used during the operation, the data in the destination database will also be automatically modified by this operation. There will also be no impact on the next call to sqlite3_backup_step().
In fact, two other helper functions backup_remaining() and backup_pagecount() are still provided in SQLite. The former will return how many pages there are in the current backup operation to be copied, while the latter will return the total number of pages that need to be copied in this operation. It is obvious that through the return results of these two functions, we can display the overall progress of this backup operation in real time. The calculation formula is as follows:
Completion = 100% * (pagecount() - remaining()) / pagecount()
See the following code example (from SQLite official website):
/*
** 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;
}