Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Minor edits to the backup application note. Integrate the same into the other documents. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d2614c5467a0b0907d12763f74000042 |
User & Date: | drh 2009-02-10 13:40:40.000 |
Context
2009-02-11
| ||
15:05 | Update the limits documentation with additional description and hyperlinks to the sqlite3_limit() interface. (check-in: a9b40186dd user: drh tags: trunk) | |
2009-02-10
| ||
13:40 | Minor edits to the backup application note. Integrate the same into the other documents. (check-in: d2614c5467 user: drh tags: trunk) | |
2009-02-05
| ||
19:45 | Change fileformat.in to use Tcl instead of javascript for toc generation etc.. (check-in: 0ca8a50964 user: dan tags: trunk) | |
Changes
Changes to pages/backup.in.
1 2 3 4 5 6 7 8 | <title>SQLite Backup API</title> <style> pre a:visited, pre a:link { text-decoration: none ; color: #40534b } pre { background: #F3F3F3; float: right; padding: 1ex 2ex; | > | 1 2 3 4 5 6 7 8 9 | <title>SQLite Backup API</title> <tcl>hd_keywords {Using the SQLite Online Backup API}</tcl> <style> pre a:visited, pre a:link { text-decoration: none ; color: #40534b } pre { background: #F3F3F3; float: right; padding: 1ex 2ex; |
︙ | ︙ | |||
39 40 41 42 43 44 45 | <li> If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery. </ul> <p> | | | | 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <li> If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery. </ul> <p> The [sqlite3_backup_init | Online Backup API] was created to address these concerns. The online backup API allows the contents of one database to be copied into another database, overwriting the original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue uninterrupted while a backup of an online database is made. <p> The online backup API is [sqlite3_backup_init | documented here]. The remainder of this page contains two C language examples illustrating common uses of the API and discussions thereof. Reading these examples is no substitute for reading the API documentation! <h2>Example 1: Loading and Saving In-Memory Databases</h2> <tcl> |
︙ | ︙ | |||
99 100 101 102 103 104 105 | ** ** 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 */ | | | | | | | > | | | | | | | | | | | | | > | | | | | | | | > | | | | | | | | < | | | | | | | | < < | | | > | | > | | | | 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 | ** ** 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 occured, then an error code and message will be left in ** connection pTo. If no error occured, 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. */ loaddb_out: (void)sqlite3_close(pFile); return rc; } } </tcl> <p> The C function to the right demonstrates of one of the simplest, and most common, uses of the backup API: loading and saving the contents of an in-memory database to a file on disk. The backup API is used as follows in this example: <ol> <li>Function [sqlite3_backup_init()] is called to create an [sqlite3_backup] object to copy data between the two databases (either from a file and into the in-memory database, or vice-versa). <li>Function [sqlite3_backup_step()] is called with a parameter of <tt>-1</tt> to copy the entire source database to the destination. <li>Function [sqlite3_backup_finish()] is called to clean up resources allocated by [sqlite3_backup_init()]. </ol> <p><b>Error handling</b> <p> If an error occurs in any of the the three main backup API routines then the [error code] and [sqlite3_errmsg | message] are attached to the destination [database connection]. Additionally, if [sqlite3_backup_step()] encounters an error, then the [error code] is returned by both the [sqlite3_backup_step()] call itself, and by the subsequent call to [sqlite3_backup_finish()]. So a call to [sqlite3_backup_finish()] does not overwrite an [error code] stored in the destination [database connection] by [sqlite3_backup_step()]. This feature is used in the example code to reduce amount of error handling required. The return values of the [sqlite3_backup_step()] and [sqlite3_backup_finish()] calls are ignored and the error code indicating the success or failure of the copy operation collected from the destination [database connection] afterward. <p><b>Possible Enhancements</b> <p> The implementation of this function could be enhanced in at least two ways: <ol> <li> Failing to obtain the lock on database file zFilename (an [SQLITE_BUSY] error) could be handled, and <li> Cases where the page-sizes of database pInMemory and zFilename are different could be handled better. </ol> <p> 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. <p> Usually, it does not matter if the page-sizes of the source database and the destination database are different before the contents of the destination are overwritten. The page-size of the destination database is simply changed as part of the backup operation. The exception is if the destination database happens to be an in-memory database. In this case, if the page sizes |
︙ | ︙ | |||
255 256 257 258 259 260 261 | ){ 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); | | > | | | | | | | | | | | | | | | < | | | | | | | | > < | | | | | > | | | | | | | | | | 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 | ){ 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; } } </tcl> <p> The function presented in the previous example copies the entire source database in one call to [sqlite3_backup_step()]. This requires holding a read-lock on the source database file for the duration of the operation, preventing any other database user from writing to the database. It also holds the mutex associated with database pInMemory throughout the copy, preventing any other thread from using it. The C function in this section, designed to be called by a background thread or process for creating a backup of an online database, avoids these problems using the following approach: <ol> <li>Function [sqlite3_backup_init()] is called to create an [sqlite3_backup] object to copy data from database pDb to the backup database file identified by zFilename. <li>Function [sqlite3_backup_step()] is called with a parameter of 5 to copy 5 pages of database pDb to the backup database (file zFilename). <li>If there are still more pages to copy from database pDb, then the function sleeps for 250 milliseconds (using the [sqlite3_sleep()] utility) and then returns to step 2. <li>Function [sqlite3_backup_finish()] is called to clean up resources allocated by [sqlite3_backup_init()]. </ol> <p><b>File and Database Connection Locking</b> <p> During the 250 ms sleep in step 3 above, no read-lock is held on the database file and the mutex associated with pDb is not held. This allows other threads to use [database connection] pDb and other connections to write to the underlying database file. <p> If another thread writes to database connection pDb while this function is sleeping, then the backup database (database connection pFile) is automatically updated along with pDb. The backup process is continued after the xSleep() call returns as if nothing had happened. If the database file underlying connection pDb is written to by a different process or thread using a different database connection while this function is sleeping, then SQLite detects this within the next call made to sqlite3_backup_step() and restarts the backup from the beginning. Either way, the backup database is kept up to date during the backup process so that when the operation is complete the backup database contains a consistent snapshot of the original. However: <ul> <li> Accounting for writes to the database by an external process or thread using a different database connection are significantly more expensive than accounting for writes made using pDb. <li> If the database is being written using a database connection other than pDb sufficiently often while the backupDb() function is running, it may never finish. </ul> <p><b>backup_remaining() and backup_pagecount()</b> <p> The backupDb() function uses the sqlite3_backup_remaining() and sqlite3_backup_pagecount() functions to report its progress via the |
︙ | ︙ | |||
368 369 370 371 372 373 374 | database is written to by another thread or process after the call to sqlite3_backup_step() returns but before the values returned by sqlite3_backup_remaining() and sqlite3_backup_pagecount() are used, the values may be technically incorrect. This is not usually a problem. <div style="clear:both"></div> | < | 372 373 374 375 376 377 378 | database is written to by another thread or process after the call to sqlite3_backup_step() returns but before the values returned by sqlite3_backup_remaining() and sqlite3_backup_pagecount() are used, the values may be technically incorrect. This is not usually a problem. <div style="clear:both"></div> |
Changes to pages/changes.in.
︙ | ︙ | |||
41 42 43 44 45 46 47 | hd_enable_main 1 } } chng {2009 Feb 18 (3.6.11)} { <li>Added the [sqlite3_backup_init | hot-backup interface]. <li>Added new commands ".backup" and ".restore" to the [CLI]. | | | 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | hd_enable_main 1 } } chng {2009 Feb 18 (3.6.11)} { <li>Added the [sqlite3_backup_init | hot-backup interface]. <li>Added new commands ".backup" and ".restore" to the [CLI]. <li>Added new methods <a href="tclsqlite.html#backup">backup</a> and <a href="tclsqlite.html#restore">restore</a> to the TCL interface. <li>Various minor bug fixes } chng {2009 Jan 15 (3.6.10)} { <li>Fix a cache coherency problem that could lead to database corruption. [Ticket #3584]. |
︙ | ︙ |
Changes to pages/docs.in.
︙ | ︙ | |||
34 35 36 37 38 39 40 41 42 43 44 45 46 47 | SQLite. } doc {Version 3 C/C++ API<br>Reference} {c3ref/intro.html} { This document describes each API function separately. } doc {Frequently Asked Questions} {faq.html} { The title of the document says all... } doc {Sharing Cache Mode} {sharedcache.html} { Version 3.3.0 and later supports the ability for two or more database connections to share the same page and schema cache. This feature is useful for certain specialized applications. } doc {Tcl API} {tclsqlite.html} { | > > > > > > | 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | SQLite. } doc {Version 3 C/C++ API<br>Reference} {c3ref/intro.html} { This document describes each API function separately. } doc {Frequently Asked Questions} {faq.html} { The title of the document says all... } doc {Using The Online Backup Interface} {backup.html} { The [sqlite3_backup_init | online-backup interface] can be used to copy content from a disk file into an in-memory database or vice versa and it can make a hot backup of a live database. This application note gives examples of how. } doc {Sharing Cache Mode} {sharedcache.html} { Version 3.3.0 and later supports the ability for two or more database connections to share the same page and schema cache. This feature is useful for certain specialized applications. } doc {Tcl API} {tclsqlite.html} { |
︙ | ︙ |
Changes to pages/news.in.
︙ | ︙ | |||
17 18 19 20 21 22 23 | {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt hd_resolve "<p>$txt</p>" hd_puts "<hr width=\"50%\">" } newsitem {2009-Feb-18} {Version 3.6.11} { SQLite [version 3.6.11] adds support for the | | | > | 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt hd_resolve "<p>$txt</p>" hd_puts "<hr width=\"50%\">" } newsitem {2009-Feb-18} {Version 3.6.11} { SQLite [version 3.6.11] adds support for the [sqlite3_backup_init | hot-backup] interface. This interface can be used to create a backup copy of an SQLite database while it is in use. The same interface can be used to initialize an in-memory database from a persistent disk image or to save an in-memory database into a persistent disk image. Usage examples can be found at [Using the SQLite Online Backup API]. } newsitem {2009-Jan-15} {Version 3.6.10} { SQLite [version 3.6.10] fixes a cache coherency bug (Ticket #3584) introduced by check-in <a href="http://www.sqlite.org/cvstrac/chngview?cn=5864">[5864]</a> which was part of [version 3.6.5]. This bug might lead to database |
︙ | ︙ |