Menu
I have an application which uses the QSQLITE driver with a QSqlDatabse on a file on the local filesystem. I want to write a backup function which will save a snapshot of the database.
Simply copying the file seems like an obvious, easy way to do it but I'm not sure when it is safe to do so.
The application modifies the database at well-defined points. Each time, a new QSqlQuery object is created, used, and immediately destroyed. Explicitly locking/flushing is an acceptable solution, but the Qt API doesn't seem to expose this.
I can't find any documentation on when Qt commits the database to disk. I imagine the QSqlDatabase destructor would do it, but even then I also don't know if (on Windows or Linux) copying the file is guaranteed to result in the most-recent changes being copied (as opposed to, say, only those changes which have been finalised in the filesystem journal). Can someone confirm or deny this? Does it make any difference if the writing filehandle is closed before the copy is executed?
Maybe the only safe way is to do an online copy but I am already using the Qt API and don't know how this would interact.
Any advice would be appreciated.
255
You can use the SQLite . clone dot command to create a clone of the current database. The way it works is quite simple. You simply use .
A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. SQLite will normally work fine as the database backend to a website.
An SQLite database can become corrupt if the file content changes due to a disk drive or flash memory failure. It is very rare, but disks will occasionally flip a bit in the middle of a sector.
SQLite Always Validates Its Inputs. SQLite should never crash, overflow a buffer, leak memory, or exhibit any other harmful behavior, even when presented with maliciously malformed SQL inputs or database files. SQLite should always detect erroneous inputs and raise an error, not crash or corrupt memory.
It's trivial to copy a SQLite database but it's less trivial to do this in a way that won't corrupt it. This will give you a nice clean backup that's sure to be in a proper state, since writing to the database half-way through your copying process is impossible.
QSqlQuery qry(db);
qry.prepare( "BEGIN IMMEDIATE;");
qry.exec();
QFile::copy(databaseName, destination);
qry.prepare( "ROLLBACK;");
qry.exec();
After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.
114
This has very little to do with Qt. It is database related. This procedure will work with any ACID compliant database, and SQLite is one of these.
From http://www.sqlite.org/transactional.html
SQLite is Transactional
A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.
This does not mean you can copy the file and it will be consistent. You should probably use block level snapshots for this before you copy. If you are using Linux, read this,
http://tldp.org/HOWTO/LVM-HOWTO/snapshotintro.html
The procedure would then be,
Snapshots are global "freeze" of file system, which is consistent because of ACID. File copy is linear operation, which cannot be guaranteed to be consistent without halting all DB operations for duration of copy. This means straight copy is not safe for online databases (in general).
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
