« Previous | Next »

NBSQLite3 Online Backup

16 May 2015

SQLite provides an online backup API that operates incrementally, so that the source database file is locked only when it is actually read from, allowing other applications to use the source database while the backup is in progress.

I've added the backup API to NBSQLite3. Here's a demonstration code snippet using OpenFootball's World Cup 2014 database:

| srcDB dstDB backup |
srcDB := NBSQLite3Connection openOn: '/tmp/wc2014.db'.
dstDB := NBSQLite3Connection openOn: '/tmp/backup.db'.
[   backup := NBSQLite3Backup new.
    srcDB asBackupSource: backup.
    dstDB asBackupDestination: backup.
    backup prepare.
    [ [ backup completed ] whileFalse: [ backup step ] ] ensure: [ backup finish ]
] ensure: [ srcDB close. dstDB close. ]

Let's look at the two databases:


% ls -l *.db
-rw-r--r-- 1 pierce pierce 1101824 May 16 10:58 backup.db
-rw-r--r-- 1 pierce pierce 1101824 Jul 22  2014 wc2014.db

% openssl md5 backup.db wc2014.db
MD5(backup.db)= 408494059b67fc3c7d39b672ce7e525e
MD5(wc2014.db)= 28ab0865e87920ca2f2b1f0de710c622

Ok, the two files have the same size, but aren't bit-wise identical. Now try sqldiff that comes with SQLite:


% sqldiff --help
Usage: sqldiff [options] DB1 DB2
Output SQL text that would transform DB1 into DB2. 

% sqldiff wc2014.db backup.db
%

No output, meaning the two files are identical structurally and content-wise, according to sqldiff. Let's make sure:

% sqlite3 backup.db
sqlite> select count(*) from goals;
171
sqlite> select max(id) from goals;
171
sqlite> delete from goals where id > 168;
sqlite> select max(id) from goals;
168
sqlite> ^D
% sqldiff wc2014.db backup.db
DELETE FROM goals WHERE id=169;
DELETE FROM goals WHERE id=170;
DELETE FROM goals WHERE id=171;

The output shows that sqldiff and the SQLite library are in cahoots to make two database files appear identical even when they are not. Haha, no, I mean, the output gives us some assurance that the backup API operated as advertised and created a proper backup of the database.

Now, the demonstration code snippet is somewhat contrived, because it backed up a totally quiescent source database. Next step is to verify backing up a database in active use.

Code updated in Smalltalk Hub. Unit tests and ConfigurationOf update to come.

Tags: SQLite