SQLite Tutorial (ix): Online backup _sqlite

First, the common backup:

The following approach is a simpler and more common way to SQLite database backups, as follows:
1. Use the SQLite API or shell tool to add a shared lock to the source database file.
2. Use the shell tool (CP or copy) to copy the database files to the backup directory.
3. Unbind the shared lock on the database file.
The above 3 steps can be applied to most scenarios, and the speed is also relatively fast, but there are some rigid defects, such as:
1. All connections that intend to write on the source database have to be suspended until the entire copy process ends and the file share lock is released.
2. Cannot copy data to in-memory database.
3. During the copy process, the backup database may be compromised once any unexpected failure occurs on the host where the backup database resides.
In SQLite, a set of API functions (C interface) for online database backup is provided, which can solve the deficiencies of the above methods well. This group of functions allows you to copy content from the source database to another database while overwriting the data in the target database. The entire copy process can be done incrementally, in which case the source database does not need to be locked throughout the copy process, but only if the data is actually read with a shared lock. This way, other users will not be suspended when they access the source database.

Second, online backup APIs introduction:

SQLite provides the following 3 APIs to do this, and here is just the basic usage, as with details you can refer to SQLite's official website "APIs Reference" (http://www.sqlite.org/c3ref/ backup_finish.html).
1. Function Sqlite3_backup_init () is used to create a Sqlite3_backup object that passes the handle of this copy operation to the remaining two functions.
2. Function Sqlite3_backup_step () is used for data copying, if the second parameter of the function is-1, then the entire copy process will be completed in one call to the function.
3. Function Sqlite3_backup_finish () is used to release the resources requested by the Sqlite3_backup_init () function to avoid resource leaks.
If any errors occur during the entire copy process, we can get the specific error code by invoking the Sqlite3_errcode () function of the destination database connection. Also, if the Sqlite3_backup_step () call fails, the Sqlite3_backup_finish () function does not modify the current connection error code, so we can invoke the Sqlite3_backup_finish () And then gets the error code, which reduces error handling in the code. See the following code sample (from SQLite official website):

Copy Code code 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 a in-memory database,
* * But this function would also work fine if it is not.
**
* * Parameter zfilename points to a nul-terminated string containing the
* * Name of the database file ' disk to load ' 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, a 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 the 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'll be set to NULL and a 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 the associated with the Pbackup object. If an
* * error occurred, then an error code with
* * Connection PTo. If no error occurred, then the error code belonging
* * To PTo are 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;
}

third, advanced application skills:

In the above example, we completed the entire copy process through a call to the Sqlite3_backup_step () function. In order to solve this problem, we will continue to introduce another, more advanced implementation--a fragmented copy--with the implementation of the previous issue of pending other write-access connections, as follows:
1. Function Sqlite3_backup_init () is used to create a Sqlite3_backup object that passes the handle of this copy operation to the remaining two functions.
2. The function Sqlite3_backup_step () is invoked to copy data, unlike the previous method, the second argument of the function is no longer-1, but a normal positive integer indicating the number of pages that will be copied each time, such as 5.
3. If there are still more pages that need to be copied after the function sqlite3_backup_step () call, we will actively hibernate the 250MS and repeat step 2.
4. Function Sqlite3_backup_finish () is used to release the resources requested by the Sqlite3_backup_init () function to avoid resource leaks.
In step 3 above, we actively hibernate 250ms, during which the copy operation does not hold any read locks on the source database, so that other database connections will not be suspended while the write operation is in progress. However, during hibernation, if another thread or process writes to the source database, SQLite detects the event and restarts the entire copy process the next time the Sqlite3_backup_step () function is called. The only exception is if the source database is not a 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 at the time of the operation, the data in the destination database will also be automatically modified by this operation. The next time you call Sqlite3_backup_step (), there will be no impact.
In fact, two additional auxiliary functions backup_remaining () and Backup_pagecount () are still available in SQLite, in which the former will return the number of pages that need to be copied in the current backup operation. The latter will return the total number of pages to be copied for this operation. It is obvious that, through the results of the return of these two functions, we can display the overall progress of the backup operation in real time, the formula is as follows:
completion = 100% * (PageCount ()-remaining ())/PageCount ()
See the following code sample (from SQLite official website):

Copy Code code as follows:

/*
* * Perform a online backup of the 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 MS, then repeats the
* * Process until the entire database is backed up.
**
* * The third argument passed to this function must being a pointer to a progress
* * function. After each set of 5 pages are 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 is used, for example, to update a GUI progress bar.
**
* * While this function is running, another thread could 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, a 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
* * Repeating MS before. */
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 of 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;
}