Jump to content
jchd

SQLite backup API wrapper

Recommended Posts

10 minutes ago, jchd said:

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

yes, it's different:

------------------------------------------------
Running SQLite version 3.8.11.1
$hDB: pragma page_size = 1024
- _SQLite_Backup(): TimerDiff: 38256.9576890637
Backup status: 0 0
$hDbCopy: pragma page_size = 1024
Check status: 0 0
Vacuum status: 0 0
Done 256/810 (31.6%)
Done 512/810 (63.21%)
Done 768/810 (94.81%)
Done 810/810 (100%)
Backup status: 0 0
------------------------------------------------
Running SQLite version 3.19.3
$hDB: pragma page_size = 4096
- _SQLite_Backup(): TimerDiff: 0.0938820877202833
Backup status: 0 0
$hDbCopy: pragma page_size = no return value
Check status: 0 0
Vacuum status: 2 0
Backup status: 2 0
------------------------------------------------

how can I set it to a different default ?

Share this post


Link to post
Share on other sites

Ah, got it!

My mistake is in the line where I compare the DLL version.

Use a leading zero (silly quick workaround)

If $RetVal < '3.06.11' Then Return SetError(2, 0, $SQLITE_MISUSE)

or more correctly __SQLite_VersCmp (from the std UDF).

I apologize for the bad code.


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

Well I wrote this when I was developping several apps in a hurry and thought it was worth sharing, but didn't polish it as I should have done.

Since then I no longer have to use AutoIt for my needs and have only little spare time.


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

If $RetVal < '3.6.11' Then Return SetError(2, 0, $SQLITE_MISUSE) < is fine


https://www.sqlite.org/pgszchng2016.html
The Default Page Size Change of SQLite 3.12.0
https://www.sqlite.org/pragma.html#pragma_page_size
Query or set the page size of the database. The page size must be a power of two between 512 and 65536 inclusive.

Share this post


Link to post
Share on other sites
19 minutes ago, argumentum said:

If you can give me the command to do it

ConsoleWrite('$hDB: SET pragma page_size = ' & pragma_page_size($hDstDbCon, 1024) & @CRLF )
ConsoleWrite('$hDB: GET pragma page_size = ' & pragma_page_size($hDstDbCon) & @CRLF )

Func pragma_page_size($h__DB, $iSize = 0)
    Local $row, $sSql = "pragma page_size;"
    If $iSize Then $sSql = "pragma page_size = "& $iSize &";"
    _SQLite_QuerySingleRow($h__DB,$sSql, $row)
    If $row <> '' Then
        $rowsSelected = $row[0]
        Return $rowsSelected
    EndIf
    Return "no return value"
EndFunc

got it :) 

Share this post


Link to post
Share on other sites

ok, I tweaked it to work with any page size

#include-once
#include <Misc.au3>
#include <sqlite.au3>

; #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
; ===============================================================================================================================
Func _SQLite_Backup($hSrcDbCon, $sDstDbFile, $sSrcDbName = Default, $sDstDbName = Default, $iBlockSize = Default, $iSleepTime = Default, $hProgressBar = Default)
    If __SQLite_hChk($hSrcDbCon, 3) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    If IsKeyword($sSrcDbName) Then $sSrcDbName = 'main'
    If IsKeyword($sDstDbName) Then $sDstDbName = 'main'
    If IsKeyword($iBlockSize) Then $iBlockSize = 16
    If IsKeyword($iSleepTime) Then $iSleepTime = 250
    If IsKeyword($hProgressBar) Then $hProgressBar = 0
    Local $RetVal = _SQLite_LibVersion()
    If @error Then Return SetError(1, @error, 0)
    ; no backup API existed before SQLite v3.6.11
;~     If $RetVal < '3.6.11' Then Return SetError(2, 0, $SQLITE_MISUSE)
    If _VersionCompare($RetVal, '3.6.11') < 0 Then Return SetError(2, 0, $SQLITE_MISUSE)
    ; change dest DB pagesize if needed
    Local $tSrcDb8 = __SQLite_StringToUtf8Struct($sSrcDbName)
    If @error Then Return SetError(4, @error, 0)
    Local $tDstDb8 = __SQLite_StringToUtf8Struct($sDstDbName)
    If @error Then Return SetError(5, @error, 0)
    Local $hDstDbCon = _SQLite_Open($sDstDbFile)
    If @error Then Return SetError(6, @error, 0)
    _SQLite_SetTimeout($hSrcDbCon, 60000)
    If @error Then Return SetError(7, @error, 0)
    ; is this really necessary?
    _SQLite_SetTimeout($hDstDbCon, 60000)
    If @error Then Return SetError(8, @error, 0)
    Local $row
    $RetVal = _SQLite_QuerySingleRow($hSrcDbCon, "pragma page_size;", $row)
    Local $err = @error
    If $err Then
        _SQLite_Close($hDstDbCon)
        Return SetError(9, @error, 0)
    EndIf
    Local $SrcPagesize = $row[0]
    $RetVal = _SQLite_QuerySingleRow($hDstDbCon, "pragma page_size;", $row)
    $err = @error
    If $err Then
        _SQLite_Close($hDstDbCon)
        Return SetError(10, @error, 0)
    EndIf
    Local $DstPagesize = $row[0]
    ; we need to (try to) match the pagesize when the destination is :memory:
    ; if not possible, the backup will fail
    If $SrcPagesize <> $DstPagesize And ($sDstDbFile = '' Or $sDstDbFile = ':memory:') Then
        If Int($SrcPagesize) <> Int(pragma_page_size($hDstDbCon, $SrcPagesize)) Then
            _SQLite_Close($hDstDbCon)
            Return SetError(11, @error, 0)
        EndIf
;~      $RetVal = _SQLite_QuerySingleRow($hDstDbCon, "pragma page_size = " & $SrcPagesize & ";", $row)
;~      $err = @error
;~      If $err Then
;~          _SQLite_Close($hDstDbCon)
;~          Return SetError(11, @error, 0)
;~      EndIf
    EndIf
    ; init backup
    $RetVal = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_backup_init", _
            "ptr", $hDstDbCon, _                 ; Destination database connection
            "ptr", DllStructGetPtr($tDstDb8), _ ; UTF-8 name of destination base
            "ptr", $hSrcDbCon, _                 ; Source database connection
            "ptr", DllStructGetPtr($tSrcDb8)) ; UTF-8 name of source base
    $err = @error
    If $err Then
        _SQLite_Close($hDstDbCon)
        Return SetError(12, $err, $SQLITE_MISUSE)
    EndIf
    Local $hBackup = $RetVal[0]
    If Not $hBackup Then
        $err = _SQLite_ErrCode($hDstDbCon)
        _SQLite_Close($hDstDbCon)
        Return SetError(-1, $err, 10)
    EndIf
    Local $rc
    Do
        ; copy a block of pages
        $RetVal = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_step", "ptr", $hBackup, "int", $iBlockSize)
        $err = @error
        If $err Then
            _SQLite_Close($hDstDbCon)
            Return SetError(13, $err, 0)
        EndIf
        $rc = $RetVal[0]
        $RetVal = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_remaining", "ptr", $hBackup)
        $err = @error
        If $err Then
            _SQLite_Close($hDstDbCon)
            Return SetError(14, $err, 0)
        EndIf
        Local $iRemain = $RetVal[0]
        $RetVal = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_pagecount", "ptr", $hBackup)
        $err = @error
        If $err Then
            _SQLite_Close($hDstDbCon)
            Return SetError(15, $err, 0)
        EndIf
        Local $iPages = $RetVal[0]
        ; inform caller of progress
        If $iPages > 0 Then
            If $hProgressBar = -1 Then
                ConsoleWrite('Done ' & $iPages - $iRemain & '/' & $iPages & ' (' & Round(100 * ($iPages - $iRemain) / $iPages, 2) & '%)' & @LF)
            Else
                GUICtrlSetData($hProgressBar, 100 * ($iPages - $iRemain) / $iPages)
            EndIf
        EndIf
        If ($rc = $SQLITE_OK Or $rc = $SQLITE_BUSY Or $rc = $SQLITE_LOCKED) Then Sleep($iSleepTime)
    Until ($rc <> $SQLITE_OK And $rc <> $SQLITE_BUSY And $rc <> $SQLITE_LOCKED)
    $RetVal = DllCall($__g_hDll_SQLite, "none:cdecl", "sqlite3_backup_finish", "ptr", $hBackup)
    $err = @error
    If $err Then
        _SQLite_Close($hDstDbCon)
        Return SetError(16, $err, 0)
    EndIf
    Return $hDstDbCon
EndFunc   ;==>_SQLite_Backup

Func pragma_page_size($h__db, $Pagesize = 0)
    Local $i, $row, $RetVal, $err, $sSQL = "pragma page_size;", $rowsSelected = "no value returned"
    If $Pagesize Then $sSQL = "pragma page_size = " & $Pagesize & ";"
    $RetVal = _SQLite_QuerySingleRow($h__db, $sSQL, $row)
    $err = @error
    If $row <> '' Then $rowsSelected = $row[0]
    If $Pagesize Then
        $i = pragma_page_size($h__db)
        $err = @error
        Return SetError(Int(Int($Pagesize) <> Int($i)), $err, $i)
    EndIf
    If $err Then Return SetError($RetVal, @error, $rowsSelected)
    Return SetError(0, 0, $rowsSelected)
EndFunc   ;==>pragma_page_size

and changed the example ( not much )

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <sqlite.au3>
;~ #include <sqlite.dll.au3>

#include "sqlitebackup.au3"

;~ #include "..\include\helpers.au3"

;;========================
;;
;; SQLite backup examples
;;
;;
;; The SQLite backup API is intended to perform a backgroud backup of a database while it
;; is being used by other processes.  This live backup is a essentially a _slow_ process.
;; If you're in a hurry, then close your database and copy the database file as a whole,
;; or specify -1 as the page block size.  It will copy the whole base in one run but be
;; warned that then SQLite will hold an exclusive lock on the source database, which may
;; be difficult to obtain or may go against availability constraints for other processes.
;;
;; To use successfully (without locking errors), you have to take usual precautions, just
;; like with other shared resource.  Use _SQLite_SetTimeout with ample delay and wrap any
;; read/modify/write operations in an IMMEDIATE transaction. Groups (tight loops) of inserts
;; should always use a transaction anyway, for mere efficiency.  Of course, if there are
;; no other processes using the database, it's much faster to copy the database file like
;; any other file.
;;
;; This example shows that a backup can go on with reads and writes occuring concurrently
;; but you'll notice that the backup restarts every time it 'sees' that the database has
;; been written to.  If you expect that the rate of writes won't give the backup enough
;; time to complete, then the backup process will never finish, defeating its purpose.
;;
;; You can use this function to backup disk or memory databases to/from disk or memory.
;; There is provision to specify the name of source/destination database(s) to make the
;; backup act on 'main', temp' or any attached database which has been given an alias.
;;
;; To fully understand the process, please refer to the current SQLite documentation.
;;
;; Default parameters seem to give decent performance and reflect most usual cases.  Be
;; wise if you modify them: a sleeptime of 10ms is certainly not enough, 1 000 000 000ms
;; is probably too much...
;;
;;========================


#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <ProgressConstants.au3>
#include <SendMessage.au3>


Global $rowsInserted = 0
Global $rowsSelected = 0
Global $rowsUpdated = 0

Local $srcfile = ":memory:"
Local $dstfile = "testbackup.db3"
Local $Form1 = GUICreate("SQLite Backup demo", 400, 170)
Local $lbDoing = GUICtrlCreateLabel("", 50, 25, 300, 15, $SS_CENTER)
Local $pbPercent = GUICtrlCreateProgress(50, 50, 300, 20, $PBS_SMOOTH)
;~ GUICtrlSetStyle($pbPercent,0x040A)       ; démarrage
;~ _SendMessage(GUICtrlGetHandle($pbPercent), 0x040A, True, 30)     ; vitesse in 1-99

Local $lbSelects = GUICtrlCreateLabel("", 50, 80, 300, 15, $SS_CENTER)
Local $lbInserts = GUICtrlCreateLabel("", 50, 105, 300, 15, $SS_CENTER)
Local $lbUpdates = GUICtrlCreateLabel("", 50, 130, 300, 15, $SS_CENTER)
GUISetState()

GUICtrlSetData($lbDoing, 'Starting SQLite')
Local $sSQliteDll = _SQLite_Startup()
If @error Then
    MsgBox(8192 + 16, "SQLite Backup demo - fatal error", "SQLite3.dll can't be loaded.")
    Exit 2
Else
    ConsoleWrite('$sSQliteDll = "' & $sSQliteDll & '"' & @CRLF)
EndIf

; open memory database
GUICtrlSetData($lbDoing, 'Opening database')
Local $i, $hDB = _SQLite_Open($srcfile)
ConsoleWrite('Running SQLite version ' & _SQLite_LibVersion() & @LF)

; change the page size to a non standard one, just to test
$i = pragma_page_size($hDB, 2048)
If @error Then Exit @error
ConsoleWrite('--- $hDB: SET page_size = ' & $i & @CRLF)

; create a table
GUICtrlSetData($lbDoing, 'Creating a table')
;~ ConsoleWrite( '--- $hDB: page_size = ' & pragma_page_size($hDB,1024) & @CRLF)
_SQLite_Exec($hDB, "create table if not exists test (Id integer, Inserted integer, Updated integer, Data text);")


; populate the table with our "high-value" data ;-)
GUICtrlSetData($lbDoing, 'Populating the table with random data')
_SQLite_Exec($hDB, "begin;")
For $i = 1 To 32768
    _SQLite_Exec($hDB, "insert into test values(abs(round(random()/1048576)), 0, 0, " & $i & ");")
    If Mod($i, 1000) = 0 Then GUICtrlSetData($pbPercent, 100 * $i / 32768)
Next
_SQLite_Exec($hDB, "commit;")

; We show that we can continue using (read and write) the database while it is
; backed up, being careful not to modify the base faster than it's being saved!
;
; Each time a record is inserted or updated, the backup process needs to start
; again from scratch, except if the modification is made using the same SQLite
; connection AND the source database is disk-based.
;
; By default, _SQLite_Backup write 16 database pages then sleeps for 250ms
; to give a chance to concurrent accesses to take place, eventually.  Think of
; the defaults as a backgroupnd slow function.  You can force a backup in a single
; operation by supplying -1 as the backup page count, but it will block any
; concurrent read by other processes until it's done.
;
; Here we limit (by counting) the number of added records, to be sure the backup
; process will finish. We also launch random updates. these writes cause the backup
; to restart completely if the source is a memory database or if the source is disk
; -based and the modifications are made by using another connection.
;
; We use 3 different adlib delays so that it more or less mimics normal random activity
AdlibRegister("InsertData", 470) ;
AdlibRegister("UpdateData", 3190) ;

; we may read the base as well (not using index means a full scan)
AdlibRegister("SelectData", 1100) ;

; make a disk backup
GUICtrlSetData($lbDoing, 'Backing up the memory DB to disk (watch it restart at DB writes).')
Local $t = TimerInit()
Local $hDbCopy = _SQLite_Backup($hDB, $dstfile, Default, Default, Default, Default, $pbPercent)
ConsoleWrite('- _SQLite_Backup(): TimerDiff: ' & TimerDiff($t) & @CRLF)
ConsoleWrite("Backup status: " & @error & ' ' & @extended & @LF)

; $hDbCopy is the handle to the backup database, left open by the backup function (new interface)
; we may perform operations on it, like check integrity, vacuum and/or reindex
Local $rows, $nrows, $ncols
_SQLite_GetTable($hDbCopy, "pragma integrity_check;", $rows, $nrows, $ncols)
ConsoleWrite("Check status: " & @error & ' ' & @extended & @LF)
_ArrayDelete($rows, 0)
_ArrayDisplay($rows, "Integrity check result")

_SQLite_Exec($hDbCopy, "vacuum;")
ConsoleWrite("Vacuum status: " & @error & ' ' & @extended & @LF)

; stop using, then close the disk backup DB
_SQLite_Close($hDbCopy)

; stop using, then close the memory DB
AdlibUnRegister("SelectData") ;
AdlibUnRegister("UpdateData") ;
AdlibUnRegister("InsertData") ;

_SQLite_Close($hDB)

; reopen the disk base we just duplicated
Local $hDB2 = _SQLite_Open($dstfile)

; back it up into a new memory DB
GUICtrlSetData($lbDoing, 'Backup the disk file to a new memory DB')

; copy blocks of 256 pages at once, report progress to console
Local $hmemDb = _SQLite_Backup($hDB2, ':memory:', Default, Default, 256, Default, -1)

; close the (now source) disk DB
_SQLite_Close($hDB2)

; added this to verify that the db is complete, out of fear (by argumentum)
_SQLite_GetTable2d($hmemDb, "select count(*) from test;", $rows, $nrows, $ncols)
_ArrayDisplay($rows, "SELECT count(*) FROM test; ( of 32768 )")

; trim the memory table just loaded
; remove the large number of rows we didn't modify
GUICtrlSetData($lbDoing, 'Modify the base')
_SQLite_Exec($hmemDb, "delete from test where inserted = 0 and updated = 0;")

; look at this memory DB
_SQLite_GetTable2d($hmemDb, "select * from test order by id;", $rows, $nrows, $ncols)
_ArrayDisplay($rows, "Reading from the memory copy")


; close the clone memory DB
_SQLite_Close($hmemDb)

_SQLite_Shutdown()
FileDelete($dstfile)
GUICtrlSetStyle($pbPercent, 0) ; arrêt
;~ _SendMessage(GUICtrlGetHandle($pbPercent), 0x040A, False, 0)

Exit

;;========================

Func InsertData()
    If $rowsInserted >= 10 Then Return
    If Random(0, 3, 1) = 1 Then
        _SQLite_Exec($hDB, "insert into test (id, inserted, updated, data) values(abs(round(random()/1048576)), 1, 0, lower(hex(randomblob(4))));")
        $rowsInserted += 1
        GUICtrlSetData($lbInserts, $rowsInserted & " rows inserted during backup")
    EndIf
EndFunc   ;==>InsertData


Func UpdateData()
    If $rowsUpdated >= 5 Then Return
    Local $row, $cond
    _SQLite_Exec($hDB, "begin immediate;")
    _SQLite_QuerySingleRow($hDB, "select lower(hex(randomblob(2)));", $row)
    $cond = " where updated = 0 and data like '" & $row[0] & "%';"
    _SQLite_QuerySingleRow($hDB, "select count(*) from test" & $cond, $row)
    _SQLite_Exec($hDB, "update test set Updated = 1" & $cond)
    _SQLite_Exec($hDB, "commit;")
    If $row <> '' Then
        $rowsUpdated += Number($row[0])
        GUICtrlSetData($lbUpdates, $rowsUpdated & " rows updated during backup")
    EndIf
EndFunc   ;==>UpdateData


Func SelectData()
    Local $row
    _SQLite_QuerySingleRow($hDB, "select count(*) from test where inserted or updated;", $row)
    If $row <> '' Then
        $rowsSelected = $row[0]
        GUICtrlSetData($lbSelects, 'SELECT found ' & $row[0] & " rows modified during backup")
    EndIf
EndFunc   ;==>SelectData

Did not test x64 but I don't use it anyway.
Thanks for explaining the page size, I never took that into consideration ( I was clueless of it ).
So I learned a few things today ;)

Edited by argumentum
made a mistake in the code

Share this post


Link to post
Share on other sites
On 8/7/2017 at 1:17 AM, jchd said:

Ah, got it!

My mistake is in the line where I compare the DLL version.

Use a leading zero (silly quick workaround)

If $RetVal < '3.06.11' Then Return SetError(2, 0, $SQLITE_MISUSE)

or more correctly __SQLite_VersCmp (from the std UDF).

I apologize for the bad code.

I was also facing the same issue, when I tried the new SQLITE 3.20 DLL. I replaced the line with _VersionCompare Function.

Replaced this line:

If $RetVal < '3.6.11' Then Return SetError(2, 0, $SQLITE_MISUSE)

with :

#include <Misc.au3>
If (_VersionCompare($RetVal, '3.6.11') < 0) Then Return SetError(2, 0, $SQLITE_MISUSE)

and it fixed the issue

Share this post


Link to post
Share on other sites
On 8/4/2017 at 3:46 AM, jchd said:

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.

Thanks for the support and for this excellent UDF @jchd

I have used the latest SQLITE 3.20 DLL now, and changed the page size of DB to 4096 as well, and now, everything is working.

Infact, the copy to Memory speed is also around 5 times faster with the page size = 4096, and with 3.20 DLL.

Share this post


Link to post
Share on other sites

The change in SQLite library default page size has been made to accomodate the improvement of hardware and match new hard disks (rotating or solid) features as well as typical OS/filesystem sizes. And, unsurprisingly, page size significantly impact performance by impacting the number of I/O and cache(s) operations.

For instance, most SSDs use a 4kb blocksize (typical NAND blocksize) and using a smaller size decreases performance by a large factor.

Remember SQLite is by far the most widespread RDBMS engine ever as it's  used in a myriad of embedded devices like all smartphones and all tablets, e-books, TVs, GPSs, ADSL/cable/fiber modems and decoders, industrial devices, ..., as well as numerous desktop applications and small or huge websites. The choice of sensible defaults workable for all use cases in this large spectrum is pretty hard and that's why the library is so configurable. It's clear that specific applications may need to massage settings to drain the best performance out of it, like a small portable device running a primitive OS w/o even a filesystem, or a huge website (can't name it) operating a 126Tb and growing SQLite DB with an average of 45 simultaneous connections 24/7.

And all that for free!


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

×
×
  • Create New...