[yunqa.de] Re: Problem with DISQLITE Hot backup
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Tue, 04 Jan 2011 12:01:03 +0100
On 04.01.2011 10:49, Edwin Yip wrote:
> Attached an example that reproduces this problem.
Your attachment contains a single unit file only. By itself, this does
not compile as its corresponding form and project files are missing.
I converted your code snippets from Unit1.pas into a console application
and was thus able to reproduce your problem.
> I hope you can fix it in a quick manner :)
The fix is described in the header comment of the attached
SQLite_HotBackup_Problem_Fix.dpr file. I have also corrected the code -
the project now compiles and runs without errors.
Please note that you did not mention the real cause of your problem
(SQLite functions called after sqlite3_shutdown) in your initial problem
description. Because users are sometimes unaware of the big differences
which can result from small code changes, I always insist on fully
compilable projects. They are the only reliable way for me to reproduce
user's problems exactly as experienced.
Ralf
{ This is a fixed console version of the buggy code snippets in Unit1.pas as
sent by the original poster.
The AV seen by the OP results from the fact that the original code still calls
DISQLite3 functions AFTER running sqlite3_shutdown(). According to the
documentation, all open database connections must be closed and all other
SQLite resources must be deallocated prior to invoking sqlite3_shutdown().
The solution is to move sqlite3_shutdown() to the very end of the
application (see below). }
program SQLite_HotBackup_Problem_Fix;
{$APPTYPE CONSOLE}
{$I DI.inc}
uses
DISystemCompat, SysUtils, DISQLite3Api, DISQLite3Database;
function GetCreateDbSqls: UnicodeString;
begin
Result :=
'CREATE TABLE [MyTable] (' + sLineBreak +
'[StrField] TEXT);'
end;
type
THotCopyProgressFunc = procedure(const ARemaining, APageCount: Int64);
function CopyDatabase(
aSrcFile, aDestFile: UnicodeString;
aCompressTarget: Boolean = True): Boolean;
function l_CopyDatabase(
const aSrcDb: sqlite3_ptr;
const aDestFileName: Utf8String;
const AProgressProc: THotCopyProgressFunc = nil;
const ABackupStepPages: Integer = 500;
const ABackupSleepPages: Integer = 0): Integer;
var
cnnDestDb: sqlite3_ptr; { Database connection opened on aDestFileName. }
pBackup: sqlite3_backup_ptr; { Backup handle used to copy data. }
begin
{ Open the database file identified by aDestFileName. }
Result := sqlite3_open(PUtf8Char(aDestFileName), @cnnDestDb);
if Result <> SQLITE_OK then
Exit;
try
{ Open the sqlite3_backup object used to accomplish the transfer }
pBackup := sqlite3_backup_init(cnnDestDb, 'main', aSrcDb, 'main');
if Assigned(pBackup) then
begin
try
{ Each iteration of this loop copies ABackupStepPages database pages
from database aSrcDb to the backup database. If the return value
of
backup_step() indicates that there are still further pages to
copy,
sleep for SLEEP_MS ms before repeating. }
repeat
Result := sqlite3_backup_step(pBackup, ABackupStepPages);
if Assigned(AProgressProc) then
AProgressProc(sqlite3_backup_remaining(pBackup),
sqlite3_backup_pagecount(pBackup));
if Result in [SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED] then
sqlite3_sleep(ABackupSleepPages);
until not (Result in [SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED]);
finally
{ Release resources allocated by backup_init(). }
sqlite3_backup_finish(pBackup);
Result := sqlite3_errcode(cnnDestDb);
end;
end;
finally
{ Close the database connection opened on database file FileName. }
sqlite3_close(cnnDestDb);
end;
end;
var
DbSource, dbDest: sqlite3_ptr;
utf8SrcFile, utf8DestFile: Utf8String;
begin
utf8SrcFile := sqlite3_encode_utf8(aSrcFile);
utf8DestFile := sqlite3_encode_utf8(aDestFile);
{ Open the source database file. }
sqlite3_check(sqlite3_open_v2(PUtf8Char(utf8SrcFile), @DbSource,
SQLITE_OPEN_READONLY, nil));
try
{ Run the hot copy. }
sqlite3_check(l_CopyDatabase(DbSource, utf8DestFile, nil, 500, 0),
DbSource);
if aCompressTarget then
begin
{ Open the target database file. }
sqlite3_check(sqlite3_open(PUtf8Char(utf8DestFile), @dbDest));
try
{compress the target db}
sqlite3_exec_fast(dbDest, 'VACUUM;');
finally
sqlite3_check(sqlite3_close(dbDest), dbDest);
end;
end;
finally
{ Close the source database file. }
sqlite3_check(sqlite3_close(DbSource), DbSource);
end;
Result := True;
end;
const
conSrcFile = 'test.db3';
var
DB: TDISQLite3Database;
Stmt: TDISQLite3Statement;
begin
{ Initialize the DISQLite3 library prior to using any other DISQLite3
functionality. See also sqlite3_shutdown() below.}
sqlite3_check(sqlite3_initialize);
try
DB := TDISQLite3Database.Create(nil);
DB.DatabaseName := conSrcFile;
if not FileExists(DB.DatabaseName) then
begin
DB.CreateDatabase;
DB.Execute(GetCreateDbSqls);
end;
DB.Open;
CopyDatabase(conSrcFile, 'test-copy.db3');
Stmt := DB.Prepare('select * from MyTable');
Stmt.Step;
Stmt.Free;
FreeAndNil(DB)
finally
{ Deallocate any resources that were allocated by sqlite3_initialize()
above. }
sqlite3_check(sqlite3_shutdown);
end;
end.
Other related posts:
