;-- TIME_STAMP 2021-08-15 12:32:35 v 0.3 #include-once #include #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