Jump to content

SQLite - Backup, Load from File to Memory (or vice versa)


Recommended Posts

I had found a script for the SQLite backup in the forum. However, this was exclusively for backing up an open database to a file. There, the pagesize was also checked, which is no longer necessary with the current SQLite version, as different pagesizes are adjusted internally.
However, the backup functions of the SQLite DLL also allow loading a database file into a :memory: database and vice versa saving from a :memory: database into a database file. A database loaded into memory allows much faster access than via the file handle.
I created the backup function for a running database in such a way that the backup can run completely in the background while the database continues to be worked with (implemented with AdlibRegister). Optionally, a progress function can be passed, which e.g. shows the percentage progress of the currently active backup operation in the status bar.
Since the AdlibRegister function does not allow a return from the backup function, there are additional functions to query the current status (running / not running) and the last SQLite error.
Another function makes it possible to output the SQLite error codes as a literal.
All of this can be understood with the examples.

SQLite_Backup.au3

;-- TIME_STAMP   2021-08-15 12:32:35   v 0.3

#include-once
#include <SQLite.au3>

#cs
    First implementation of sqlite3_backup_XXX() in version 3.7.4
    https://www.sqlite.org/backup.html
    Quote:
    "The online backup API allows the contents of one database to be copied into another database,
    overwriting the original contents of the target database. The copy operation may be done incrementally,
    in which case the source database does not need to be locked for the duration of the copy,
    only for the brief periods of time when it is actually being read from.
    This allows other database users to continue uninterrupted while a backup of an online database is made."

    Also useful to know: Default Page Size Change of SQLite 3.12.0
    https://www.sqlite.org/pgszchng2016.html

    Here tested with 32-bit DLL (x86) for SQLite version 3.36.0
    https://www.sqlite.org/download.html
    Paragraph: Precompiled Binaries for Windows
#ce


#cs
    FUNCTIONS
    ------------------------------------------------------------------------------------------------

    _SQLite_Backup_LoadOrSaveDB
        Loads a database from file into memory or vice versa

    _SQLite_Backup_RunningDB
        Creates a backup of a running database. The database is not locked and can be used
        during the backup process. An optional progress function can be passed.

    _SQLite_Backup_RunningDB_Status
        Returns the status of _SQLite_Backup_RunningDB()
        $SQLITE_BACKUP_NOTRUNNING = 0 or $SQLITE_BACKUP_RUNNING = 1

    _SQLite_Backup_RunningDB_LastError
        Returns the last error from _SQLite_Backup_RunningDB()

    _SQLite_ErrCodeLiteral
        Returns the passed SQLite error code as corresponding literal

    ------------------------------------------------------------------------------------------------

    DLL FUNCTIONS
    ------------------------------------------------------------------------------------------------

    _SQLite_Backup_Init
        Initialises a sqlite3_backup object

    _SQLite_Backup_Step
        Copy up to N pages between the source and destination databases specified by sqlite3_backup object

    _SQLite_Backup_Remaining
        Returns the number of pages still to be backed up at the conclusion of the most
        recent _SQLite_Backup_Step(). The value is updated each time _SQLite_Backup_Step() is called.

    _SQLite_Backup_Pagecount
        Returns the total number of pages in the source database at the conclusion of the most
        recent _SQLite_Backup_Step(). The value is updated each time _SQLite_Backup_Step() is called.

    _SQLite_Backup_Finish
        Releases all resources associated with the sqlite3_backup object

    ------------------------------------------------------------------------------------------------
#ce




; Error constants (not included in SQLite.au3) from sqlite3.c
If Not IsDeclared('SQLITE_FORMAT')  Then Global Const $SQLITE_FORMAT  = 24
If Not IsDeclared('SQLITE_RANGE')   Then Global Const $SQLITE_RANGE   = 25
If Not IsDeclared('SQLITE_NOTADB')  Then Global Const $SQLITE_NOTADB  = 26
If Not IsDeclared('SQLITE_NOTICE')  Then Global Const $SQLITE_NOTICE  = 27
If Not IsDeclared('SQLITE_WARNING') Then Global Const $SQLITE_WARNING = 28

; Time (ms) between 2 backup_step operations in _SQLite_Backup_RunningDB().
; Required in order not to block other file accesses. Please do not reduce too much.
Global Const $BACKUP_STEP_WAIT = 100

; Global constants, variables to use with backup on running database
Global Const $SQLITE_BACKUP_NOTRUNNING = 0
Global Const $SQLITE_BACKUP_RUNNING = 1
Global $g_Backup_pDB, $g_Backup_pFile, $g_Backup_nPage, $g_Backup_fProgress, $g_Backup_iErr, $g_Backup_iStatus = $SQLITE_BACKUP_NOTRUNNING



;===================================================================================================
; Function Name....: _SQLite_Backup_LoadOrSaveDB
; Description......: Loads a database from file into memory or vice versa
; Parameter(s).....: $_FileDB       The database file path. The database is loaded into the memory from there
; .................:                or the database is saved from the memory to there.
; .........ByRef!!.: $_MemDB        If you create this variable without any value (none DB pointer), it triggers the creation
; .................:                of a new :memory: database.
; .................:                This also determines the backup direction "file to memory".
; .................:                If $_MemDB is a DB pointer (holds the handle from a :memory: database) the backup direction
; .................:                is "memory to file". The file is overwritten without confirmation if it exists!
; .......optional..: $_sDBschemes   Database Names ("To From") associated with the databases. (Scheme names: "main", "temp" or AS-name from ATTACH statement)
; .................:                Default: "", means: "main main". If not default, pass both names separated by a space. (e.g. "temp MYCOPY")
; .................:                Please note: Scheme names are case sensitive
; Return Value(s)..: Success        $SQLITE_OK
; .................: Failure        $SQLITE_ERROR  If "_SQLite_Open(FILE)" fails: @error=error from this call, @extended=possible SQLite error there
; Remarks(s).......: The page parameter for _SQLite_Backup_Step() will not passed, the default (-1) copies all pages at once.
;===================================================================================================
Func _SQLite_Backup_LoadOrSaveDB($_FileDB, ByRef $_MemDB, $_sDBschemes='')
    Local $pFile = _SQLite_Open($_FileDB)
    Local $iErr = @error, $iExt = @extended
    If $iErr Then Return SetError($iErr,$iExt,$SQLITE_ERROR)
    Local $bSaveToFile = True, $iRet, $pBackup, $pTo, $pFrom, $iErr
    If Not IsPtr($_MemDB) Then
        $bSaveToFile = False
        $_MemDB = _SQLite_Open()
    EndIf
    $pFrom = $bSaveToFile ? $_MemDB : $pFile
    $pTo = $bSaveToFile ? $pFile : $_MemDB
    Local $aSchemes[] = ['main','main']
    If $_sDBschemes <> '' Then $aSchemes = StringSplit($_sDBschemes, ' ', 2)
    $pBackup = _SQLite_Backup_Init($pTo, $aSchemes[0], $pFrom, $aSchemes[1])
    If @error = $SQLITE_OK And $pBackup <> 0 Then
        $iErr = _SQLite_Backup_Step($pBackup)
        If $iErr <> $SQLITE_ERROR Then _SQLite_Backup_Finish($pBackup)
        $iRet = _SQLite_ErrCode($pTo)
    Else
        $iRet = $SQLITE_ERROR
    EndIf
    _SQLite_Close($pFile)
    Return $iRet
EndFunc  ;==>_SQLite_Backup_LoadOrSaveDB


;===================================================================================================
; Function Name....: _SQLite_Backup_RunningDB
; Description......: Creates a backup of a running database. The database is not locked and can be used
; .................: during the backup process.
; Parameter(s).....: $_pDB          Handle to an open database
; .................: $_FileDB       The file path for backing up the database. The file is overwritten without confirmation if it exists!
; .......optional..: $_funcProgress The progress function like: progress(remaining, pagecount)
; .................:                If defined, this function is called, so the percentage completion
; .................:                of the process may be calculated as:
; .................:                completion = 100% * (pagecount - remaining) / pagecount
; .......optional..: $_sDBschemes   Database Names ("To From") associated with the databases. (Scheme names: "main", "temp" or AS-name from ATTACH statement)
; .................:                Default: "", means: "main main". If not default, pass both names separated by a space. (e.g. "temp MYCOPY")
; .................:                Please note: Scheme names are case sensitive
; .......optional..: $_iMaxSteps    The max. number of step operations (default: 100). The sum of the pages
; .................:                is divided by this value to determine the number of pages per step.
; Return Value(s)..: None           Get it with call of _SQLite_Backup_RunningDB_LastError()
; Remark(s)........: You can get the current status ($SQLITE_BACKUP_NOTRUNNING = 0 or $SQLITE_BACKUP_RUNNING = 1)
; .................: with call of _SQLite_Backup_RunningDB_Status()
;===================================================================================================
; Uses AdlibRegister
; In Adlib-function needed values as Global: $g_Backup_pDB, $g_Backup_pFile, $g_Backup_nPage, $g_Backup_fProgress, $g_Backup_iErr, $g_Backup_iStatus
Func _SQLite_Backup_RunningDB($_pDB, $_FileDB, $_funcProgress=Null, $_sDBschemes='', $_iMaxSteps=100)
    $g_Backup_nPage = 1
    $g_Backup_pFile = _SQLite_Open($_FileDB)
    If @error Then
        $g_Backup_iErr = $SQLITE_CANTOPEN
        Return
    EndIf
    Local $aSchemes[] = ['main','main']
    If $_sDBschemes <> '' Then $aSchemes = StringSplit($_sDBschemes, ' ', 2)
    $g_Backup_pDB = _SQLite_Backup_Init($g_Backup_pFile, $aSchemes[0], $_pDB, $aSchemes[1])
    $g_Backup_iErr = @error
    If $g_Backup_iErr = $SQLITE_OK And $g_Backup_pDB <> 0 Then
        $g_Backup_iStatus = $SQLITE_BACKUP_RUNNING

        ; run once
        ; _SQLite_Backup_Pagecount() needs first a call of _SQLite_Backup_Step()
        $g_Backup_iErr = _SQLite_Backup_Step($g_Backup_pDB, $g_Backup_nPage) ; 1st step with one page ($g_Backup_nPage=1)
        $g_Backup_nPage = Int(_SQLite_Backup_Pagecount($g_Backup_pDB)/$_iMaxSteps) ; calculate the number of pages per step for passed $_iMaxSteps
        If $g_Backup_nPage < 1 Then $g_Backup_nPage = 1 ; result may be <1, so correct it
        $g_Backup_fProgress = $_funcProgress
        If $g_Backup_fProgress <> Null Then
            $g_Backup_fProgress(_SQLite_Backup_Remaining($g_Backup_pDB), _SQLite_Backup_Pagecount($g_Backup_pDB))
        EndIf

        If ($g_Backup_iErr = $SQLITE_OK Or $g_Backup_iErr = $SQLITE_BUSY Or $g_Backup_iErr = $SQLITE_LOCKED) Then
            AdlibRegister(__SQLite_Backup_StepWait, $BACKUP_STEP_WAIT)
        ; The ElseIf branch is only executed if the entire database was copied the first time _SQLite_Backup_Step() was called.
        ElseIf ($g_Backup_iErr <> $SQLITE_OK And $g_Backup_iErr <> $SQLITE_BUSY And $g_Backup_iErr <> $SQLITE_LOCKED) Then
            _SQLite_Backup_Finish($g_Backup_pDB)
            $g_Backup_iErr = _SQLite_ErrCode($g_Backup_pFile)
            _SQLite_Close($g_Backup_pFile)
            $g_Backup_iStatus = $SQLITE_BACKUP_NOTRUNNING
        EndIf
    Else
        _SQLite_Close($g_Backup_pFile)
    EndIf
EndFunc  ;==>_SQLite_Backup_RunningDB


;===================================================================================================
; Function Name....: __SQLite_Backup_StepWait
; Description......: Internal AdlibRegister function, called from _SQLite_Backup_RunningDB
; Parameter(s).....: None
; Return Value(s)..: None
;===================================================================================================
Func __SQLite_Backup_StepWait()
    AdlibUnRegister(__SQLite_Backup_StepWait)
    $g_Backup_iStatus = $SQLITE_BACKUP_RUNNING
    $g_Backup_iErr = _SQLite_Backup_Step($g_Backup_pDB, $g_Backup_nPage)
    If $g_Backup_fProgress <> Null Then
        $g_Backup_fProgress(_SQLite_Backup_Remaining($g_Backup_pDB), _SQLite_Backup_Pagecount($g_Backup_pDB))
    EndIf
    If ($g_Backup_iErr = $SQLITE_OK Or $g_Backup_iErr = $SQLITE_BUSY Or $g_Backup_iErr = $SQLITE_LOCKED) Then
        AdlibRegister(__SQLite_Backup_StepWait, $BACKUP_STEP_WAIT)
    ElseIf ($g_Backup_iErr <> $SQLITE_OK And $g_Backup_iErr <> $SQLITE_BUSY And $g_Backup_iErr <> $SQLITE_LOCKED) Then
        _SQLite_Backup_Finish($g_Backup_pDB)
        $g_Backup_iErr = _SQLite_ErrCode($g_Backup_pFile)
        _SQLite_Close($g_Backup_pFile)
        $g_Backup_iStatus = $SQLITE_BACKUP_NOTRUNNING
    EndIf
EndFunc  ;==>__SQLite_Backup_StepWait


;===================================================================================================
; Function Name....: _SQLite_Backup_RunningDB_Status
; Description......: Returns the status of _SQLite_Backup_RunningDB()
; Parameter(s).....: None
; Return Value(s)..: $SQLITE_BACKUP_NOTRUNNING = 0 or $SQLITE_BACKUP_RUNNING = 1
;===================================================================================================
Func _SQLite_Backup_RunningDB_Status()
    Return $g_Backup_iStatus
EndFunc  ;==>_SQLite_Backup_RunningDB_Status


;===================================================================================================
; Function Name....: _SQLite_Backup_RunningDB_LastError
; Description......: Returns the last error from _SQLite_Backup_RunningDB()
; Parameter(s).....: None
; Return Value(s)..: The last error from _SQLite_Backup_RunningDB
;===================================================================================================
Func _SQLite_Backup_RunningDB_LastError()
    Return $g_Backup_iErr
EndFunc  ;==>_SQLite_Backup_RunningDB_LastError


;===================================================================================================
; Function Name....: _SQLite_ErrCodeLiteral
; Description......: Returns the passed SQLite error code as corresponding literal
; Parameter(s).....: $_iErrCode  The SQLite error code
; Return Value(s)..: The corresponding literal
;===================================================================================================
Func _SQLite_ErrCodeLiteral($_iErrCode)
    If $_iErrCode < 0 Or $_iErrCode > 101 Then Return "WRONG_OR_EXTENDED_ERROR_CODE"
    Local Static $aErrCodes[102] = ["$SQLITE_OK",        "$SQLITE_ERROR", _
                "$SQLITE_INTERNAL", "$SQLITE_PERM" ,     "$SQLITE_ABORT", _
                "$SQLITE_BUSY",     "$SQLITE_LOCKED",    "$SQLITE_NOMEM", _
                "$SQLITE_READONLY", "$SQLITE_INTERRUPT", "$SQLITE_IOERR", _
                "$SQLITE_CORRUPT",  "$SQLITE_NOTFOUND",  "$SQLITE_FULL", _
                "$SQLITE_CANTOPEN", "$SQLITE_PROTOCOL",  "$SQLITE_EMPTY", _
                "$SQLITE_SCHEMA",   "$SQLITE_TOOBIG",    "$SQLITE_CONSTRAINT", _
                "$SQLITE_MISMATCH", "$SQLITE_MISUSE",    "$SQLITE_NOLFS", _
                "$SQLITE_AUTH",     "$SQLITE_FORMAT",    "$SQLITE_RANGE", _
                "$SQLITE_NOTADB",   "$SQLITE_NOTICE",    "$SQLITE_WARNING"]
    $aErrCodes[100] = "$SQLITE_ROW"
    $aErrCodes[101] = "$SQLITE_DONE"
    Return $aErrCodes[$_iErrCode]
EndFunc  ;==>_SQLite_ErrCodeLiteral


;===================================================================================================
; Function Name....: _SQLite_Backup_Init
; Description......: Initialises a sqlite3_backup object
; Parameter(s).....: $_pTo          Database Connection Handle associated with the destination database.
; .................: $_NameToDB     Database Name associated with the destination database. ("main", "temp" or AS-name from ATTACH statement)
; .................: $_pFrom        Database Connection Handle associated with the source database.
; .................: $_NameFromDB   Database Name associated with the source database. ("main", "temp" or AS-name from ATTACH statement)
; Return Value(s)..: Success        The sqlite3_backup object if SQLITE_OK, otherwise null. @error=SQLITE_err_code (Dll call was successfull, the init operation may still have failed)
; .................: Failure        0, @errror=error from dll call
;===================================================================================================
Func _SQLite_Backup_Init($_pTo, $_NameToDB, $_pFrom, $_NameFromDB)
    Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_init", _
            "ptr", $_pTo, _
            "str", $_NameToDB, _
            "ptr", $_pFrom, _
            "str", $_NameFromDB)
    Local $iErr = @error
    If $iErr Then
        Return SetError($iErr,0,0)
    Else
        Return SetError(_SQLite_ErrCode($_pTo),0,$aRet[0])
    EndIf
EndFunc  ;==>_SQLite_Backup_Init


;===================================================================================================
; Function Name....: _SQLite_Backup_Step
; Description......: Copy up to N pages between the source and destination databases specified by sqlite3_backup object
; Parameter(s).....: $_pBackup  The sqlite3_backup object (result from _SQLite_Backup_Init).
; .................: $_nPages   Number of pages to be copied. "-1" (default) copies all at once.
; Return Value(s)..: Success    The SQLite error code (Dll call was successfull, the step operation may still have failed)
; .................:            SQLITE_OK       - successfully copies N pages and there are still more pages to be copied
; .................:            SQLITE_DONE     - successfully finishes copying all pages from source to destination
; .................:            SQLITE_err_code - If an error occurs while running _SQLite_Backup_Step
; .................:            As well as SQLITE_OK and SQLITE_DONE, a call to _SQLite_Backup_Step() may return
; .................:            SQLITE_READONLY, SQLITE_NOMEM, SQLITE_BUSY, SQLITE_LOCKED, or an SQLITE_IOERR_XXX extended error code.
; .................: Failure    $SQLITE_ERROR, @error=error from dll call
;===================================================================================================
Func _SQLite_Backup_Step($_pBackup, $_nPages=-1)
    Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_step", _
            "ptr", $_pBackup, _
            "int", $_nPages)
    Local $iErr = @error
    If $iErr Then
        Return SetError($iErr,0,$SQLITE_ERROR)
    Else
        Return $aRet[0]
    EndIf
EndFunc  ;==>_SQLite_Backup_Step


;===================================================================================================
; Function Name....: _SQLite_Backup_Remaining
; Description......: Returns the number of pages still to be backed up at the conclusion of the most
; .................: recent _SQLite_Backup_Step(). The value is updated each time _SQLite_Backup_Step() is called.
; Parameter(s).....: $_pBackup  The sqlite3_backup object (result from _SQLite_Backup_Init).
; Return Value(s)..: Success    Number of pages
; .................: Failure    $SQLITE_ERROR, @error=error from dll call
;===================================================================================================
Func _SQLite_Backup_Remaining($_pBackup)
    Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_remaining", _
            "ptr", $_pBackup)
    Local $iErr = @error
    If $iErr Then
        Return SetError($iErr,0,$SQLITE_ERROR)
    Else
        Return $aRet[0]
    EndIf
EndFunc  ;==>_SQLite_Backup_Remaining


;===================================================================================================
; Function Name....: _SQLite_Backup_Pagecount
; Description......: Returns the total number of pages in the source database at the conclusion of the most
; .................: recent _SQLite_Backup_Step(). The value is updated each time _SQLite_Backup_Step() is called.
; Parameter(s).....: $_pBackup  The sqlite3_backup object (result from _SQLite_Backup_Init).
; Return Value(s)..: Success    Number of pages
; .................: Failure    $SQLITE_ERROR, @error=error from dll call
;===================================================================================================
Func _SQLite_Backup_Pagecount($_pBackup)
    Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_pagecount", _
            "ptr", $_pBackup)
    Local $iErr = @error
    If $iErr Then
        Return SetError($iErr,0,$SQLITE_ERROR)
    Else
        Return $aRet[0]
    EndIf
EndFunc  ;==>_SQLite_Backup_Pagecount


;===================================================================================================
; Function Name....: _SQLite_Backup_Finish
; Description......: Releases all resources associated with the sqlite3_backup object
; Parameter(s).....: $_pBackup  The sqlite3_backup object (result from _SQLite_Backup_Init).
; Return Value(s)..: Success    The SQLite error code (Dll call was successfull, the finish operation may still have failed)
; .................: Failure    $SQLITE_ERROR, @error=error from dll call
;===================================================================================================
Func _SQLite_Backup_Finish($_pBackup)
    Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_backup_finish", _
            "ptr", $_pBackup)
    Local $iErr = @error
    If $iErr Then
        Return SetError($iErr,0,$SQLITE_ERROR)
    Else
        Return $aRet[0]
    EndIf
EndFunc  ;==>_SQLite_Backup_Finish

SQLite_Backup_Example.au3

#include 'SQLite_Backup.au3'

; min. required: version 3.7.4
_SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, 1)

; Modify the pathes for your SQLite database files!
Global $g_sDBFile = @ScriptDir & "\germandict.db"
Global $g_sDBFileCopy = @ScriptDir & "\germandict_copy_running.db" ; Target file: _Example_Backup_RunningDB()

; Function: _SQLite_Backup_LoadOrSaveDB
; ByRef parameter, must be declared (no preassignment required for: File -> Memory)
Global $g_hDBMem

ConsoleWrite('LOAD FILE TO MEMORY' & @CRLF)
_Example_LoadFileToMemory()

ConsoleWrite('SAVE MEMORY TO FILE' & @CRLF)
_Example_SaveMemoryToFile()
_SQLite_Close($g_hDBMem)

ConsoleWrite('BACKUP RUNNING DB' & @CRLF)
_Example_Backup_RunningDB()


_SQLite_Shutdown()




Func _Example_LoadFileToMemory()

    Local $Timer = TimerInit()

    ; Since $g_hDBMem is not a database pointer, a memory database is automatically created.
    ; This also results in the copy direction: file to memory.
    Local $iResult = _SQLite_Backup_LoadOrSaveDB($g_sDBFile, $g_hDBMem)
#cs
    If you do not use the standard scheme names (To: 'main', From: 'main'), you can pass them as optional parameters.
    Local $iResult = _SQLite_Backup_LoadOrSaveDB($g_sDBFile, $g_hDBMem, 'temp MYCOPY')
#ce
    If $iResult = $SQLITE_OK Then
        ConsoleWrite('COPY-TIME File -> Memory: ' & StringFormat('%.1f s', TimerDiff($Timer)/1000) & ' ' & _SQLite_ErrCodeLiteral($iResult) & @CRLF & @CRLF)
    Else
        ConsoleWrite('ERROR: ' & _SQLite_ErrCodeLiteral($iResult) & @CRLF & @CRLF)
    EndIf

EndFunc  ;==>_Example_LoadFileToMemory


Func _Example_SaveMemoryToFile()

    Local $Timer = TimerInit()

    ; $g_hDBMem is now a database pointer (created and filled by _Example_LoadFileToMemory() )
    ; This also results in the copy direction: memory to file.
    Local $iResult = _SQLite_Backup_LoadOrSaveDB($g_sDBFile, $g_hDBMem)
#cs
    If you do not use the standard scheme names (To: 'main', From: 'main'), you can pass them as optional parameters.
    Local $iResult = _SQLite_Backup_LoadOrSaveDB($g_sDBFile, $g_hDBMem, 'temp MYCOPY')
#ce
    If $iResult = $SQLITE_OK Then
        ConsoleWrite('COPY-TIME Memory -> File: ' & StringFormat('%.1f s', TimerDiff($Timer)/1000) & ' ' & _SQLite_ErrCodeLiteral($iResult) & @CRLF & @CRLF)
    Else
        ConsoleWrite('ERROR: ' & _SQLite_ErrCodeLiteral($iResult) & @CRLF & @CRLF)
    EndIf

EndFunc  ;==>_Example_SaveMemoryToFile


Func _Example_Backup_RunningDB()

    Local $hDB = _SQLite_Open($g_sDBFile)

    ; Parameter: Handle running DB, Path backup file, [optional] progress function, [optional] string scheme names, [optional] max. number progress steps
    _SQLite_Backup_RunningDB($hDB, $g_sDBFileCopy, _progress, '', 100)

    Local $Timer = TimerInit(), $iStatus
    While True
        Sleep(20)
        $iStatus = _SQLite_Backup_RunningDB_Status()
        ConsoleWrite('TIMER: ' & StringFormat('%.2f', TimerDiff($Timer)/1000)  & ' BACKUP-STATUS: ' & ($iStatus ? '$SQLITE_BACKUP_RUNNING' : '$SQLITE_BACKUP_NOTRUNNING') & @CRLF)
        If Not $iStatus Then
            ConsoleWrite('FINISHED, LAST_ERROR: ' & _SQLite_ErrCodeLiteral(_SQLite_Backup_RunningDB_LastError()) & @CRLF)
            ExitLoop
        EndIf
    WEnd

    _SQLite_Close($hDB)

EndFunc  ;==>_Example_Backup_RunningDB


Func _progress($r, $p) ; $r=remaining-pages, $p=page-count
    ConsoleWrite('Completion: ' & StringFormat('%.1f %%', (100 * ($p - $r) / $p))  & @CRLF)
EndFunc  ;==>_progress

 

SQLite_Backup.au3 SQLite_Backup_Example.au3

Best Regards BugFix  

Link to post
Share on other sites
1 hour ago, BugFix said:

However, this was exclusively for backing up an open database to a file

If you're refering to my (old) post and code, then are you sure? The code works fine with memory DBs too.

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)

Link to post
Share on other sites
1 hour ago, jchd said:

If you're refering to my (old) post and code, then are you sure? The code works fine with memory DBs too.

OK, I didn't make myself clear. I meant that your script refers only to the backup. Loading from a file into a :memory: database is not possible with it, at least not without changing it.

Best Regards BugFix  

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...