On 2015-02-23 04:54 AM, Donald Shepherd wrote: > On Mon Feb 23 2015 at 1:41:31 PM Simon Slavin <slavins at bigfraud.org> wrote: > >> On 22 Feb 2015, at 11:15pm, Donald Shepherd <donald.shepherd at gmail.com> >> wrote: >> >>> If I use the backup API to create a copy of an SQLite database that uses >>> Write-Ahead Logging, will the resulting copy reflect the contents of both >>> the base database file and the -wal file? >> Until a transaction is COMMITted, it's not part of the database. Because >> the program could decide to ROLLBACK instead. >> > My understanding of WAL is that even if a transaction is COMMITted it will > sit in the -wal file until a checkpoint occurs, i.e. by default when the > -wal file reaches 4 MB. As a result that transaction will only be present > in the copied SQLite database if the backup API takes into account the > contents of both files. > > Further reading led me to a reference stating that under the covers the > backup API is treated the same as any other reader, so I take that to mean > that it looks at both files and compiles the copy from the two.
The WAL file is part and parcel of the database. Whatever database copy gets created by the BACKUP API will contain ALL current committed data in the database. For this to be true, it need not maintain the exact file structure, meaning the resulting database may be smaller (like when VACUUM was run) and will not have content-carrying WAL files with it yet. None of this matters because the reflected data in the new DB file will be exactly what is in the current state of the original database - precisely /how/ that original database used files to store that data is irrelevant. Typically you only need the exact data state of a database. If you need to copy the exact file structure along with the exact data, then you should copy the specific files after all transactions were closed and all write-locks released. I highly doubt anyone has ever needed this unless you are making a program that tries to recover or read SQLite3 files outside of a DB connection.
