https://developpaper.com/sqlite-tutorial-9-online-backup/
SQLite Tutorial (9): Online Backup - Develop Paper
1. Common backups: The following is a relatively simple and commonly used way to backup the SQLite database. See the following steps:Use SQLite API or shell tools to add shared locks to source database files.Copy database files to backup directories using
developpaper.com
1. Common backups:
The following is a relatively simple and commonly used way to backup the SQLite database. See the following steps:
Use SQLite API or shell tools to add shared locks to source database files.
Copy database files to backup directories using shell tools (cp or copy).
Remove shared locks on database files.
The above three steps can be applied to most scenarios, and the speed is relatively fast, but there are some rigid defects, such as:
All connections intended to perform write operations on the source database have to be suspended until the entire copy process ends and the file sharing lock is released.
2) Cannot copy data to in-memory database.
In the process of copying, the backup database may be destroyed once any sudden failure occurs on the host where the backup database is located.
A set of APIs (C interface) for online database backup is provided in SQLLite, which can solve the shortcomings of the above methods. With this set of functions, the content in the source database can be copied to another database, and the data in the target database can be covered at the same time. The whole copy process can be accomplished incrementally. In this case, the source database does not need to be locked during the whole copy process, but only shared locks 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 three APIs functions to complete this operation. Here only their basic usage is given. For details, you can refer to the official website of SQLite “APIs Reference” (http://www.sqlite.org/c3ref/backup_finish.html).
The function sqlite3_backup_init() is used to create the sqlite3_backup object, which will be passed to the other two functions as a handle to this copy operation.
The function sqlite3_backup_step() is used for data copy. If the second parameter of the function is -1, the whole copy process will be completed in one call of the function.
The function sqlite3_backup_finish() is used to release the resources requested by the sqlite3_backup_init() function to avoid resource leakage.
If there are any errors in the whole copy 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 retrieve the error code after calling sqlite3_backup_finish(), thus reducing an error handling in the code. See the following code example (from the SQLite website):
Copy codeThe code is as follows:
/*
** 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 example above, we completed the whole copy process through a single call to the sqlite3_backup_step() function. In order to solve the problem of suspending other write access connections, we will continue to introduce a more advanced implementation, fragmented copy, which is implemented as follows:
The function sqlite3_backup_init() is used to create the sqlite3_backup object, which will be passed to the other two functions as a handle to this copy operation.
Function sqlite3_backup_step() is called to copy data. Unlike previous methods, the second parameter of the function is no longer – 1, but a normal positive integer, indicating the number of pages that will be copied per call, such as 5.
If there are still more pages to be copied after the function sqlite3_backup_step() call, then we will sleep 250ms actively and then repeat step 2).
The function sqlite3_backup_finish() is used to release the resources requested by the sqlite3_backup_init() function to avoid resource leakage.
In step 3 above, we take the initiative to sleep for 250 ms, during which time, the copy operation will not hold any read locks on the source database, so that other database connections will not be suspended during the write operation. However, during hibernation, if another thread or process writes to the source database, SQLite detects the occurrence of the event and restarts the entire copy 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 done in the same process as the copy operation, and the same database connection handle is used in the operation, the data in the destination database will be automatically modified by the operation at the same time. Next time sqlite3_backup_step() is called, nothing will happen.
In fact, two additional auxiliary functions backup_remaining() and backup_pagecount () are still provided in SQLLite, in which the former will return how many pages need to be copied in the current backup operation, while the latter will return the total number of pages that need to be copied in the current backup operation. Obviously, through the return results of these two functions, we can show 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 the SQLite website):
Copy codeThe code is as follows:
/*
** 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;
}
