{ DISQLite3 example project to allow playing with transactions and view their
  effect on insert performance and hard disk activity.

  This demo uses a TTimer to trigger continuous database INSERTs of random data.
  Checking "Use Transaction" wraps inserts by a transaction. The transaction
  will automatically be commited and a new one started every Max number INSERTs
  or after a timeout period. Both values can be adjusted at runtime to view
  their effect on hard disk activity (watch the HDD control LED).

  NOTE: Because of the limited TTimer.Interval and trigger frequency, the timer
  does not even nearly match up to the full INSERT capabilities of DISQLite3.
  For truly fast performance use run this demo in "Boost" mode.

  The source code in DISQLite3_Log_Inserts_DB.pas will hopefully also serve as
  an example for how to implement fast, resource efficient logging with
  DISQLite3.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

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

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

unit DISQLite3_Log_Inserts_fMain;

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

interface

uses
  {$IFDEF HAS_UNITSCOPE}
  System.Classes, Vcl.Controls, Vcl.Forms, Vcl.StdCtrls, Vcl.ComCtrls,
  Vcl.ExtCtrls,
  {$ELSE HAS_UNITSCOPE}
  Classes, Controls, Forms, StdCtrls, ComCtrls, ExtCtrls,
  {$ENDIF HAS_UNITSCOPE}
  DISQLite3_Log_Inserts_DB;

type
  TfrmLogInserts = class(TForm)
    Timer: TTimer;
    btnStartTimer: TButton;
    tbTimerInterval: TTrackBar;
    lblTimerInterval: TLabel;
    ProgressBar: TProgressBar;
    StatusBar: TStatusBar;
    gbTransactionControl: TGroupBox;
    lblTransactionTimeout: TLabel;
    lblTransactionInsertCountMax: TLabel;
    tbTransactionTimeout: TTrackBar;
    tbTransactionInsertCountMax: TTrackBar;
    cbxUseTransaction: TCheckBox;
    btnStartBoost: TButton;
    lblJournalMode: TLabel;
    cbxJournalMode: TComboBox;
    procedure Form_Create(Sender: TObject);
    procedure tbTimerInterval_Change(Sender: TObject);
    procedure btnStartTimer_Click(Sender: TObject);
    procedure tbTransactionTimeout_Change(Sender: TObject);
    procedure tbTransactionInsertCountMax_Change(Sender: TObject);
    procedure Timer_Timer(Sender: TObject);
    procedure cbxUseTransaction_Click(Sender: TObject);
    procedure btnStartBoost_Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure cbxJournalMode_Change(Sender: TObject);
  private
    FDatabase: TLogInsertsDB;
    FBoost: Boolean;
    FLastProgressTick: Cardinal;
    procedure InsertRecord;
  end;

const
  APP_TITLE = 'DISQLite3' + {$IFDEF DISQLite3_Personal} ' Personal' + {$ENDIF} ': Log Inserts Demo';

var
  frmLogInserts: TfrmLogInserts;

implementation

uses
  {$IFDEF HAS_UNITSCOPE}
  Winapi.Windows, System.SysUtils
  {$ELSE HAS_UNITSCOPE}
  Windows, SysUtils
  {$ENDIF HAS_UNITSCOPE},
  DISQLite3Api;

{$R *.dfm}

procedure TfrmLogInserts.Form_Create(Sender: TObject);
begin
  Caption := APP_TITLE;

  FDatabase := TLogInsertsDB.Create(Self);
  FDatabase.DatabaseName := ChangeFileExt(ParamStr(0), '.db3');
  try
    FDatabase.Open;
  except
    FDatabase.CreateDatabase;
  end;

  cbxUseTransaction.Checked := FDatabase.UseTransaction;
  cbxUseTransaction_Click(nil);
  tbTransactionInsertCountMax.Position := FDatabase.TransactionInsertCountMax;
  tbTransactionTimeout.Position := FDatabase.TransactionTimeOut;

  with cbxJournalMode, Items do
    begin
      Add('DELETE');
      Add('TRUNCATE');
      Add('PERSIST');
      Add('MEMORY');
      {$IFNDEF SQLITE_OMIT_WAL}Add('WAL'); {$ENDIF}
      Add('OFF');
      ItemIndex := 0;
    end;

  tbTimerInterval.Position := Timer.Interval;
end;

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

procedure TfrmLogInserts.FormDestroy(Sender: TObject);
begin
  FDatabase.Free;
end;

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

procedure TfrmLogInserts.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  { Make sure app can close even while in boost mode. }
  FBoost := False;
end;

procedure TfrmLogInserts.cbxUseTransaction_Click(Sender: TObject);
var
  b: Boolean;
begin
  b := cbxUseTransaction.Checked;

  lblTransactionTimeout.Enabled := b;
  tbTransactionTimeout.Enabled := b;

  lblTransactionInsertCountMax.Enabled := b;
  tbTransactionInsertCountMax.Enabled := b;

  FDatabase.UseTransaction := b;
end;

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

procedure TfrmLogInserts.cbxJournalMode_Change(Sender: TObject);
begin
  FDatabase.JournalMode := cbxJournalMode.Text;
end;

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

procedure TfrmLogInserts.tbTimerInterval_Change(Sender: TObject);
var
  i: Integer;
begin
  i := tbTimerInterval.Position;
  Timer.Interval := i;
  lblTimerInterval.Caption := Format('Insert Interval (milliseconds): %d', [i]);
end;

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

procedure TfrmLogInserts.tbTransactionTimeout_Change(Sender: TObject);
var
  i: Integer;
begin
  i := tbTransactionTimeout.Position;
  FDatabase.TransactionTimeOut := i;
  lblTransactionTimeout.Caption := Format('Transaction Timeout (milliseconds): %d', [i]);
end;

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

procedure TfrmLogInserts.tbTransactionInsertCountMax_Change(Sender: TObject);
var
  i: Integer;
begin
  i := tbTransactionInsertCountMax.Position;
  FDatabase.TransactionInsertCountMax := i;
  lblTransactionInsertCountMax.Caption := Format('Maximum Number of Inserts per Transaction: %d', [i]);
end;

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

procedure TfrmLogInserts.btnStartTimer_Click(Sender: TObject);
const
  BTN_CAPTION: array[Boolean] of string = ('Stop Timer', 'Start Timer');
var
  b: Boolean;
begin
  Timer.Enabled := not Timer.Enabled;

  b := not Timer.Enabled;
  btnStartTimer.Caption := BTN_CAPTION[b];
  btnStartBoost.Enabled := b;
end;

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

procedure TfrmLogInserts.Timer_Timer(Sender: TObject);
begin
  InsertRecord;
end;

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

procedure TfrmLogInserts.btnStartBoost_Click(Sender: TObject);
const
  BTN_CAPTION: array[Boolean] of string = ('Stop Boost', 'Start Boost');
var
  b: Boolean;
begin
  FBoost := not FBoost;

  b := not FBoost;
  btnStartBoost.Caption := BTN_CAPTION[b];
  lblTimerInterval.Enabled := b;
  tbTimerInterval.Enabled := b;
  btnStartTimer.Enabled := b;

  if FBoost then
    repeat
      InsertRecord;
    until not FBoost;
end;

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

procedure TfrmLogInserts.InsertRecord;
var
  TC: Cardinal;
begin
  { Insert one record. }
  FDatabase.Insert('Log ' + IntToStr(Random(MaxInt)), Random(MaxInt), Random * 1000000);

  { Indicate progress. This is time consuming so do it sparesly only. }
  TC := GetTickCount;
  if GetTickDiff(FLastProgressTick, TC) > 333 then
    begin
      { Update progress info. }
      ProgressBar.Position := FDatabase.InsertCount mod ProgressBar.Max;
      StatusBar.Panels[0].Text := Format('%d total inserts', [FDatabase.InsertCount]);
      StatusBar.Panels[1].Text := Format('%n inserts per sec', [FDatabase.Speed]);
      StatusBar.Panels[2].Text := Format('%d commits', [FDatabase.CommitCount]);
      { Stay responsive. }
      Application.ProcessMessages;
      FLastProgressTick := TC;
    end;
end;

initialization
  { Initialize the DISQLite3 library prior to using any other DISQLite3
    functionality. See also sqlite3_shutdown() below.}
  sqlite3_initialize;

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

end.

