jchd

SQLite backup API wrapper

32 posts in this topic

#1 ·  Posted (edited)

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 by jchd
1 person likes this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Edit: Oh, someone made up my mistake :D, 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 by KaFu

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#5 ·  Posted

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

What can be the possible reason ?


Areeb Qaisar

Share this post


Link to post
Share on other sites

#7 ·  Posted

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.

2 people like this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#8 ·  Posted

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 Qaisar

Share this post


Link to post
Share on other sites

#9 ·  Posted

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 Qaisar

Share this post


Link to post
Share on other sites

#10 ·  Posted

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 


Areeb Qaisar

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by Skysnake

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

#12 ·  Posted

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#13 ·  Posted

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 


Areeb Qaisar

Share this post


Link to post
Share on other sites

#14 ·  Posted

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

@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 by argumentum
added PS:

Share this post


Link to post
Share on other sites

#16 ·  Posted

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#17 ·  Posted

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.

Share this post


Link to post
Share on other sites

#18 ·  Posted

Which error code do you get and from which call?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#20 ·  Posted

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now