SQLite backup API wrapper

[jchd]

Dear AutoIt + SQLite user,

I wrote this little function to benefit of the recently introduced SQLite backup API. "Recently" is somehow a distorsion of history, since its first appearance was in version 3.6.11.

Q) Is the thing easy to use?
A) Yes: there is only one call that takes care of everything (almost).

Q) Why should you use it, instead of simply copying the SQLite database file itself?
A) There are common siuations where you just can't do that. Either there are other uninterruptible processes using the database, or you managed to build a memory database but at some point decided that it would be wise to avoid loosing your work if ever your fine application crashes :ahem: a power loss occurs! In both cases, you have to be very, very careful duplicating your database as they are many potential pitfalls doing so.

Q) So it is possible to keep on reading the source database while it is being backed up?!?
A) Exactly.

Q) And it is possible to modify the source database during its backup?!?
A) Also correct, BUT each time the base is modified, the backup needs to restart, or write again a number of dirty pages. Thus, if you write at high rate to your source base, it's likely that the backup process will never complete.

Q) How should you use it?
A) Read the fine doc! No, seriously there is no proper documentation, just a short abstract.

Q) Is there a runable example to better understand how it works?
A) Yes, along the function itself, there is a usable example.

Please regard this as a beta: there may remain bugs in it and the interface may change in some future.
Don't rely on this for mission-critical data, you've been warned.

I'd like to receive your bug reports and suggestions.

2010-01-13 version 0.1
2010-03-14 version 0.2 changed return value to DB handle, so that the function can be used to load a disk-DB into memory

EDIT 18/06/2014: fixed new global variable name.
SQLitebackup.au3

Little demo in pseudo-real situation:
SQLitebackupTest.au3

EDIT: ignore the #include for helpers.au3 in the example.

Edited June 17, 2014 by jchd

[KaFu]

Ts, always me pressing the negative rep button ... will make this up in two other posts of yours....an now check out and test you API wrapper ...

Edit: Oh, someone made up my mistake , thanks! First tests look really good, was first just confused that I couldn't find the backup database afterwards... until I saw the FileDelete($file) in the example . Will implement this into SMF and report issues... if I encounter any! Hmmm, what do you think about adding an optional VACUUM parameter that cleans up the backup file at the end?

Edited January 13, 2010 by KaFu

[jchd]

what do you think about adding an optional VACUUM parameter that cleans up the backup file at the end?

I've just changed the interface so that memory backups from disk bases can be used! That was an overlook.

Besides, did you have any problem, bug?

Since the function now returns an open handle to the backed-up base, you are free to run any maintenance operation on it. I find it useful to be able to perform integrity checks on a regular basis on a bitwise copy of the base wihout changing anything in the applicattive codes. If ever error occurs (which BTW I've not yet encountered nor simulated) there is a fairly good chance (aka 100%) that the "live" base is corrupted as well. So I run this procedure every hour 24/7 on my main 650Mb base, just to be sure that if something turns wrong, I get notified quickly.

[Marlo]

Just what i needed Thanks pal.

[areeb]

I am getting the error 11 - Error changing destination Db page_size

What can be the possible reason ?

[argumentum]

1 hour ago, areeb said:

What can be the possible reason ?

You're gonna have to post your code or the part of your code that reproduces the event, otherwise, how can it be troubleshooted ?

[jchd]

3 hours ago, areeb said:

I am getting the error 11 - Error changing destination Db page_size

What can be the possible reason ?

SQlite error 11 (décimal) is SQLITE_CORRUPT, meaning that the Connection is to a corrupt DB.

Page sizes have nothing to do with error 11.

[areeb]

Yes, here is the script:

#include <File.au3>
#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include "sqlitebackup.au3"

_SQLite_Startup(@ScriptDir & "\sqlite3.dll")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

;$ORIG_HANDLE = _SQLite_Open("DB1.db")
$ORIG_HANDLE = _SQLite_Open("DB2.db")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't create a memory Database!")
    Exit -1
EndIf

$hHandleDb = _SQLite_Backup($ORIG_HANDLE, ':memory:', Default, Default, Default, Default, -1)
MsgBox(4096, "", @error & ' ' & @extended & @LF)
_SQLite_Close($ORIG_HANDLE)
_SQLite_Shutdown()

 

Actually, I have tried it with 2 different Sqlite Databases.

DB2 is 13KB in size, and the above script works perfectly with that. DB2.db does gets copied to memory, and I can perform operations from memory.

However, DB1 is around 2 MB in size, and I get @error = 11, which corresponds to 11 - Error changing destination Db page_size

Does it have something to do with the size of Database ? Do I need to execute any Pragma statements before, in-order to work with large size DB ? 

[areeb]

1 hour ago, jchd said:

SQlite error 11 (décimal) is SQLITE_CORRUPT, meaning that the Connection is to a corrupt DB.

Page sizes have nothing to do with error 11.

That is from Sqlite.au3

Global Const $SQLITE_CORRUPT = 11 ; /* The database disk image is malformed */

But from the UDF SqliteBackup.au3 attached in the Post: 

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Backup
; Version........: 0.2  2010-03-08 (new return value)
; Description ...: Backups an entire open SQLite Database, even while it's being used
; Syntax.........: _SQLite_Backup($hSrcDbCon, $sDstDbFile, Byref $hDstDbCon, $sSrcDbName = 'main', $sDstDbName = 'main', $iBlockSize = Default, $iSleepTime = Default, $hProgressBar = Default)
; Parameters ....: $hSrcDbCon - An Open Database connection, Use -1 To use Last Opened Database
;                  $sDstDbFile - The destination database filename
;                  $hDstDbCon - pass back the handle of a DB when restoring to memory
;                  $sSrcDbName - Optional: The name of the source database, defaults to 'main'
;                  $sDstDbName - Optional: The name of the destination database, defaults to 'main'
;                  $iBlockSize - Optional: The number of pages in every backup block, default to 16 pages.  Use -1 to copy the database in one shot.
;                  $iSleepTime - Optional: The sleep delay between block of pages writes, default to 250ms
;                  $hProgressBar - Optional: ID of a ProgressBar (returned by GUICtrlCreateProgress) to update, or -1 to list progress on console. Default is 0 for none.
; Return values .: Returns the handle of a memory DB when restoring from disk to memory
;                  @error Value(s):       -1 - SQLite Reported an Error (Check @extended Value)
;                  1 - Error returned by _SQLite_LibVersion
;                  2 - The active sqlite3.dll doesn't support the backup API. Minimum version is 3.6.11
;                  3 - Invalid source DB connection handle ($hSrcDbCon)
;                  4 - Error while converting $sSrcDbFile to UTF-8
;                  5 - Error while converting $sDstDbFile to UTF-8
;                  6 - Error reported by _SQLite_open
;                  7 - Error reported by _SQLite_SetTimeout on source DB
;                  8 - Error reported by _SQLite_SetTimeout on destination DB
;                  9 - Error querying source Db page_size
;                  10 - Error querying destination Db page_size
;                  11 - Error changing destination Db page_size
;                  12 - Error Calling SQLite API 'sqlite3_backup_init'
;                  13 - Error Calling SQLite API 'sqlite3_backup_step'
;                  14 - Error Calling SQLite API 'sqlite3_backup_remaining'
;                  15 - Error Calling SQLite API 'sqlite3_backup_pagecount'
;                  16 - Error Calling SQLite API 'sqlite3_backup_finish'
;                  17 - Error closing destination Db
;                  @extended Value(s): Can be compared against $SQLITE_* Constants
; Author ........: jchd
; ===============================================================================================================================

 

[areeb]

1 hour ago, jchd said:

SQlite error 11 (décimal) is SQLITE_CORRUPT, meaning that the Connection is to a corrupt DB.

Page sizes have nothing to do with error 11.

Moreover, I can successfully execute the queries on this database, while this DB stays on Disk.

But when I use _SQLite_Backup to copy this DB from Disk to Memory, then I get this error 

[Skysnake]

I think you need to create a second connection to a new ::memory:: db BEFORE you attempt to backup into it.

I am guessing SQLite does not know where to execute your query...

Like

Local $hondiskdb = ...

Local $hmemdb = ...

Then go from there...

Skysnake

 

Edited August 3, 2017 by Skysnake

[jchd]

Sorry I didn't realize the error was from the backup call. Indeed, page size have to match when the destinationDB is :memory:

From SQlite doc:

Quote

The sqlite3_backup_step() might return SQLITE_READONLY if

1. the destination database was opened read-only, or
2. the destination database is using write-ahead-log journaling and the destination and source page sizes differ, or
3. the destination database is an in-memory database and the destination and source page sizes differ.

Why do two disk DBs have distinct page sizes, something you never asked for? History explains that.

Again from SQLite doc:

Quote

When a new database is created, SQLite assigns a page size to the database based on platform and filesystem. For many years, the default page size was almost always 1024 bytes, but beginning with SQLite version 3.12.0 (2016-03-29), the default page size increased to 4096. The default page size is recommended for most applications.

In your case I guess that DB2 was created with an sqlite.dll version pre version 3.12.0 (2016-03-29) while DB1 was created more recently, well that is unless explicit page sizes where used at creation time or later in DB life.

You can easily change the page size of a DB.

[areeb]

6 hours ago, jchd said:

Sorry I didn't realize the error was from the backup call. Indeed, page size have to match when the destinationDB is :memory:

From SQlite doc:

Why do two disk DBs have distinct page sizes, something you never asked for? History explains that.

Again from SQLite doc:

In your case I guess that DB2 was created with an sqlite.dll version pre version 3.12.0 (2016-03-29) while DB1 was created more recently, well that is unless explicit page sizes where used at creation time or later in DB life.

You can easily change the page size of a DB.

 

This may be the case. When I checked the page size using :

pragma page_size;

Page size of created Memory DB = 1024.

Page Size of DB2 = 1024. (Created using a recent version of SQLiteStudio and it gets copied to Memory successfully)

Page Size of DB1 = 4096 (SQLite file copied from Android, and it shows Error 11 while copying.)

I think I need to try the latest version of SQLite, and then see what happens 

[jchd]

Of course a newer version will perform better (I mean faster or offering more features), but it won't change the fact that you'll get the same error.

Your best bet is to change the page size of DB1.

[argumentum]

@jchd , thanks to @areeb , I rediscovered your script and want to use it on a web site and run the DB from memory. Your backup allows me to do that. But where/how I'll use it, is of no importance for the issue.
Testing on a server 2012, it would fail and on my other PC ( Win7 ), it worked fine. So troubleshooting to find my mistake, I found that it runs fine with sqlite dll v3.8.11.1 but failed with v3.19.3 or v3.11.0, did not try any other versions. Could look into it ?, I can load the dll that works with it, but would be nice to run it with the newer DLL too. Thanks.

PS: I only tested the script you provided as example.

Edited August 6, 2017 by argumentum
added PS:

[jchd]

Do the failures denote an issue with the :memory: DB page size?

[argumentum]

2 minutes ago, jchd said:

Do the failures denote an issue with the :memory: DB page size?

-------------------------------------------------
Running SQLite version 3.8.11.1
- _SQLite_Backup(): TimerDiff: 54491.9061448446
Backup status: 0 0
Check status: 0 0
-------------------------------------------------
Running SQLite version 3.19.3
- _SQLite_Backup(): TimerDiff: 0.10524429476367
Backup status: 0 0
Check status: 1 0
Vacuum status: 2 0
Backup status: 2 0
-------------------------------------------------

I'd guess not, is straight from the example.

[jchd]

Which error code do you get and from which call?

[argumentum]

is there a way to ask the script what  DB page size is using ?

[jchd]

yes, _SQLite_QuerySingleRow with "pragma page_size" returns current page size.