{ DISQLite3 example project to read database on disk into a memory database.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2009-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

program DISQLite3_Backup_Memory;

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$I DISQLite3.inc}

uses
  {$IFDEF FastMM}{$IFDEF COMPILER_6_UP}FastMM4, {$ENDIF}{$ENDIF}
  DISystemCompat, Windows, SysUtils, DISQLite3Api;

{$R ..\Yunqa.res}

const
  { Default number of pages to transfer in a single backup step.
    Increase for speed, decrease for concurrency. }
  BACKUP_STEP_PAGES = 50;
  { Default number of milli-seconds to wait between two backup steps.
    Increase for concurrency, decrease for speed. }
  BACKUP_SLEEP_MS = 50;
  { Name of the disk database. }
  DISK_DATABASE = 'test.db3';

type
  { Type definition of callback function triggered periodically by backupDb
    (below) to indicate the backup progress. }
  TBackupProgressFunc = procedure(const ARemaining, APageCount: Int64);

  { Perform an online backup of DbSource to DbDest. This function copies
    BackupStepPages database pages from DbSource to DbDest, then unlocks
    DbSource and sleeps for SleepMS, then repeats the process until the entire
    database is backed up. Both DbSource and DbDest must be open database
    handles.

    The third argument passed to this function must be a pointer to a
    TBackupProgressFunc function. After each set of ABackupStepPages pages is
    backed up, the progress function is invoked with two integer parameters: the
    number of pages left to copy, and the total number of pages in the source
    file. This information may be used, for example, to update a GUI progress bar.

    While this function is running, another thread may use the database
    DbSource, or another process may access the underlying database file via a
    separate connection.

    If the backup process is successfully completed, SQLITE_OK is returned.
    Otherwise, if an error occurs, an SQLite error code is returned. }
function backupDb(
  const DbSource: sqlite3_ptr; { Database handle to read from. }
  const DbDest: sqlite3_ptr; { Database handle to write to. }
  const AProgress: TBackupProgressFunc = nil; { Progress function to invoke. }
  const BackupStepPages: Integer = BACKUP_STEP_PAGES;
  const SleepMS: Integer = BACKUP_SLEEP_MS
  ): Integer;
var
  pBackup: sqlite3_backup_ptr; { Backup handle used to copy data. }
begin
  { Open the sqlite3_backup object used to accomplish the transfer }
  pBackup := sqlite3_backup_init(DbDest, 'main', DbSource, 'main');
  if Assigned(pBackup) then
    try
      { Repeatedly backup the database in small chunks of BackupStepPages
        pages. }
      repeat
        Result := sqlite3_backup_step(pBackup, BackupStepPages);
        if not (Result in [SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED]) then
          Break;
        if Assigned(AProgress) then AProgress(
            sqlite3_backup_remaining(pBackup),
            sqlite3_backup_pagecount(pBackup));
        if SleepMS > 0 then
          sqlite3_sleep(SleepMS);
      until False;
    finally
      { Release resources allocated by backup_init(). }
      Result := sqlite3_backup_finish(pBackup);
    end
  else
    Result := SQLITE_ERROR;
end;

//------------------------------------------------------------------------------

procedure BackupProgress(const Remaining, PageCount: Int64);
begin
  if PageCount <> 0 then // Avoid division by zero error.
    WriteLn(100 * (PageCount - Remaining) div PageCount, '% done');
end;

//------------------------------------------------------------------------------

var
  DbDisk: sqlite3_ptr;
  DbMem: sqlite3_ptr;
  Stmt: sqlite3_stmt_ptr;
begin
  { Disable all FPU and SSE exceptions. }
  Set8087CW($133F); {$IFDEF CPUX64}SetMXCSR($1F80); {$ENDIF CPUX64}

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_check(sqlite3_initialize);
    try
      { Make sure we have a disk database to read into memory. }
      if not FileExists(DISK_DATABASE) then
        begin
          sqlite3_check(sqlite3_open(DISK_DATABASE, @DbDisk));
          try
            sqlite3_exec_fast(DbDisk, 'CREATE TABLE t (a text)');
            sqlite3_exec_fast(DbDisk, 'INSERT INTO t VALUES (''ok'')');
          finally
            sqlite3_check(sqlite3_close(DbDisk));
          end;
        end;

      { Open the memory database file. }
      sqlite3_check(sqlite3_open(':memory:', @DbMem));
      try

        { Open the disk database. }
        sqlite3_check(sqlite3_open(DISK_DATABASE, @DbDisk));
        try
          { Backup the disk database to memory. }
          sqlite3_check(backupDb(DbDisk, DbMem, BackupProgress), DbDisk);
        finally
          { Close the disk database file. }
          sqlite3_check(sqlite3_close(DbDisk), DbDisk);
        end;

        { Test that the database has been copied successfully. }
        sqlite3_check(sqlite3_prepare_v2(
          DbMem, 'SELECT * FROM t', -1, @Stmt, nil), DbMem);
        try
          if sqlite3_step(Stmt) = SQLITE_ROW then
            WriteLn(sqlite3_column_str(Stmt, 0));
        finally
          sqlite3_finalize(Stmt);
        end;

      finally
        { Close the memory database. }
        sqlite3_check(sqlite3_close(DbMem), DbMem);
      end;

    finally
      { Deallocate any resources that were allocated by sqlite3_initialize()
        above. }
      sqlite3_check(sqlite3_shutdown);
    end;

  except
    on e: Exception do
      begin
        WriteLn(e.Message);
        ExitCode := 1;
      end;
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.

