SQLite tutorial (9): online backup and sqlite tutorial online backup

SQLite tutorial (9): online backup and sqlite tutorial online backup

I. Common backup:

The following method is a simple and commonly used SQLite database backup method, as shown in the following steps:
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 database files to the backup directory.
3). Remove the shared lock on the database file.
The above three steps can be used in most scenarios, and the speed is relatively fast, but there are some rigid defects, such:
1). All connections that intend to perform write operations on the source database must be suspended until the entire copy process ends and the file share lock is released.
2). Data cannot be copied to the in-memory database.
3) during the copy process, the backup database may be damaged once the host where the backup database is located experiences any unexpected faults.
SQLite provides a set of APIs functions (C interface) for online database backup, which can solve the shortcomings of the above methods. Using this group of functions, you can copy the content in the source database to another database and overwrite the data in the target database. The entire copy process can be completed in incremental mode. In this case, the source database does not need to be locked during the entire copy process, but only supports a shared lock when actually reading data. In this way, other users will not be suspended when accessing the source database.

II. Introduction to online backup APIs:

SQLite provides the following three APIs functions to complete this operation, here only give their basic usage, as to the use details can refer to the official SQLite Website "APIs Reference" (http://www.sqlite.org/c3ref/backup_finish.html ).
1) The sqlite3_backup_init () function is used to create the sqlite3_backup object. this object will be used as the handle of this copy operation and passed to the other two functions.
2) The sqlite3_backup_step () function is used for data copying. If the second parameter of the function is-1, the entire copy process will be completed in one call of the function.
3) The sqlite3_backup_finish () function is used to release the resources applied by the sqlite3_backup_init () function to avoid resource leakage.
If any errors occur during the entire copy process, you can call the sqlite3_errcode () function of the target database to obtain the specific error code. In addition, if sqlite3_backup_step () fails to be called, because the sqlite3_backup_finish () function does not modify the error code of the current connection, we can obtain the error code after calling sqlite3_backup_finish, this reduces the number of errors in the code. See the following code example (from the official 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
** 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
** 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
** 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
** 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
** 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;
}

Iii. Advanced application skills:

In the preceding example, the entire copy process is completed by calling the sqlite3_backup_step () function. This implementation method still has the problem of Suspending other write access connections. To solve this problem, we will continue to introduce another more advanced implementation method-multipart copy, the implementation steps are as follows:
1) The sqlite3_backup_init () function is used to create the sqlite3_backup object. this object will be used as the handle of this copy operation and passed to the other two functions.
2 ). the sqlite3_backup_step () function is called to copy data. Unlike the previous method, the second parameter of the function is no longer-1, but a normal positive integer, indicates the number of pages that will be copied in each call, for example, 5.
3) If more pages still need to be copied after sqlite3_backup_step () is called, we will take the initiative to sleep for 250 ms and repeat Step 2 ).
4) The sqlite3_backup_finish () function is used to release the resources applied by the sqlite3_backup_init () function to avoid resource leakage.
In step 3 above, we take the initiative to sleep for 250 ms. During this period, the copy operation will not hold any read locks on the source database, in this way, other database connections will not be suspended during write operations. However, during sleep, if another thread or process writes to the source database, SQLite will detect the event and call sqlite3_backup_step () next time () function. 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, the Operation also uses the same database connection handle, so the data in the target database will be automatically modified at the same time. The next time you call sqlite3_backup_step (), no effect will occur.
In fact, two other auxiliary functions backup_remaining () and backup_pagecount () are provided in SQLite. The former returns the number of pages to be copied in the current backup operation, the latter will return the total number of pages to be copied for this operation. Obviously, with the results returned by these two functions, we can display the overall progress of this backup operation in real time. The formula is as follows:
Complete = 100% * (pagecount ()-remaining ()/pagecount ()
See the following code example (from the official 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
** ZFilename, then unlocks pDb and sleeps for 250 MS, then repeats
** 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
** 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 */
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
** 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;
}