{ Main database access unit for the DISQLite3 Log Inserts demo.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

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

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

unit DISQLite3_Log_Inserts_DB;

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

interface

uses
  DISystemCompat, {$IFDEF HAS_UNITSCOPE}System.Classes{$ELSE}Classes{$ENDIF},
  DISQLite3Database;

type
  //------------------------------------------------------------------------------
  // TLogInsertsDB
  //------------------------------------------------------------------------------

  { }
  TLogInsertsDB = class(TDISQLite3Database)
  private
    { Number of commits so far. }
    FCommitCount: Cardinal;
    { Number of records inserted. }
    FInsertCount: Integer;
    { Prepared insert statement. }
    FInsertStmt: TDISQLite3Statement;
    { Number of records inserted per second. }
    FSpeed: Single;
    { Insert count when speed war last updated. }
    FSpeedInsertCount: Cardinal;
    { Tick count when speed was last updated. }
    FSpeedLastTick: Cardinal;
    { Number of uncommitted inserts in transaction. }
    FTransactionInsertCount: Cardinal;
    { Maximum number of transactions after which to commit and start a new one. }
    FTransactionInsertCountMax: Cardinal;
    { Tick count when the last transaction was started. }
    FTransactionLastTick: Cardinal;
    { Timeout in milliseconds after which to commit the transaction. }
    FTransactionTimeOut: Cardinal;
    FUseTransaction: Boolean;
    procedure SetTransactionInsertCountMax(const Value: Cardinal);
    procedure SetTransactionTimeOut(const Value: Cardinal);
    procedure SetUseTransaction(const Value: Boolean);
    procedure SetJournalMode(const Value: UnicodeString);
  protected
    procedure CommitAndBeginTransaction;
    procedure DoAfterConnect; override;
    procedure DoAfterCreateDatabase; override;
    procedure DoBeforeDisconnect; override;
  public
    constructor Create(AOwner: TComponent); override;
    { Call this procedure to insert data into the Log table. It automatically
      handles transaction and commit as controlled by the option properties. }
    function Insert(
      const ATextCol: UnicodeString;
      const AIntegerCol: Integer;
      const ARealCol: Double): Int64;
    { Total number of commits. }
    property CommitCount: Cardinal read FCommitCount;
    { Total number of records inserted. }
    property InsertCount: Integer read FInsertCount;
    { Return the number of records inserted per second. }
    property Speed: Single read FSpeed;
  published
    property JournalMode: UnicodeString write SetJournalMode;
    { Timeout in milli-seconds after which the transaction is commited. }
    property TransactionTimeOut: Cardinal read FTransactionTimeOut write SetTransactionTimeOut default 1000;
    { Number of inserts after which the transaction is committed. }
    property TransactionInsertCountMax: Cardinal read FTransactionInsertCountMax write SetTransactionInsertCountMax default 100;
    { True to use transaction to speed up inserts. The transaction is
      automatically commited and a new one started every TransactionInsertCount
      inserts or after TransactionTimeOut milli-seconds. }
    property UseTransaction: Boolean read FUseTransaction write SetUseTransaction default False;
  end;

function GetTickDiff(const OldTickCount, NewTickCount: Cardinal): Cardinal;

implementation

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

function GetTickDiff(const OldTickCount, NewTickCount: Cardinal): Cardinal;
begin
  if NewTickCount >= OldTickCount then
    Result := NewTickCount - OldTickCount
  else
    Result := High(OldTickCount) - OldTickCount + NewTickCount;
end;

//------------------------------------------------------------------------------
// TLogInsertsDB class
//------------------------------------------------------------------------------

constructor TLogInsertsDB.Create(AOwner: TComponent);
begin
  inherited;
  FTransactionTimeOut := 100; // 100 milliseconds.
  FTransactionInsertCountMax := 10; // Commit every 100 inserts.
end;

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

{ If using transactions, commit and begin a new transaction after
  TransactionInsertCount insertions or TransactionTimeOut milli-seconds. }
procedure TLogInsertsDB.CommitAndBeginTransaction;
var
  TC, TD: Cardinal; // Current tick count and tick diff
begin
  TC := GetTickCount;

  if FUseTransaction then
    begin
      if (FTransactionInsertCount >= FTransactionInsertCountMax) or
        (GetTickDiff(FTransactionLastTick, TC) > FTransactionTimeOut) then
        begin
          Commit;
          Inc(FCommitCount);
          StartTransaction;
          FTransactionInsertCount := 0;
          FTransactionLastTick := TC;
        end;
    end
  else
    begin
      { Count implicit commit of autocommit mode. }
      Inc(FCommitCount);
    end;

  TD := GetTickDiff(FSpeedLastTick, TC);
  if (TD > 1000) then
    begin
      if FSpeedInsertCount > 0 then
        FSpeed := FSpeedInsertCount / TD * 1000
      else
        FSpeed := 0;
      FSpeedInsertCount := 0;
      FSpeedLastTick := TC;
    end;

end;

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

{ This is called after the database is connected. Use it to prepare the
  INSERT statement once for the lifetime of the database. If is much faster
  to do this here in advance than to reprepare with each new INSERT. }
procedure TLogInsertsDB.DoAfterConnect;
begin
  { Prevent other users from accessing the database while we are inserting. }
  // Execute('PRAGMA locking_mode=EXCLUSIVE');

  { Set the number of pages the DB engine buffers in memory.
    400000 pages require about 450 MB of memory for this application.
    Increasing this value further will speed up insertions and index
    creation, but will also use more memory. Reduce if you have less
    memory available to avoid OS paging. }
  // Execute('PRAGMA cache_size=400000');

  { Noticably faster inserts at the cost of a little less ACID security. }
  // Execute('PRAGMA synchronous=OFF');

  FInsertStmt := Prepare('INSERT INTO Log (TextCol, IntCol, RealCol) VALUES (?, ?, ?)');
  inherited;
end;

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

{ This is called after a new database is created.
  Use it to set upt the table required by this demo. }
procedure TLogInsertsDB.DoAfterCreateDatabase;
begin
  Execute(
    'CREATE TABLE Log (' +
    'ID Integer PRIMARY KEY,' + // Unique identifier.
    'TextCol Text,' + // A column to store Text information
    'IntCol Integer,' + // A column to store Integer number information.
    'RealCol Real)'); // A column to store Floating point number information.

  {.$DEFINE Count_Trigger}
  {$IFDEF Count_Trigger}
  Execute(
    'CREATE TABLE Count (Log INTEGER);' +
    'INSERT INTO Count (Log) VALUES (0);' +

    'CREATE TRIGGER Log_ins AFTER INSERT ON Log' +
    '  BEGIN' +
    '    UPDATE Count SET Log = Log + 1;' +
    '  END;' + 
    
    'CREATE TRIGGER Log_del AFTER DELETE ON Log' +
    '  BEGIN' +
    '    UPDATE Count SET Log = Log - 1;' +
    '  END;');
  {$ENDIF Count_Trigger}

  inherited;
end;

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

{ This is called before the database disconnects. We use it to commit any open
  transaction and free the prepared INSERT statement. }
procedure TLogInsertsDB.DoBeforeDisconnect;
begin
  { Commit current transaction before closing the database. }
  if FUseTransaction and InTransaction then
    Commit;
  FInsertStmt.Free;
  inherited;
end;

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

function TLogInsertsDB.Insert(
  const ATextCol: UnicodeString;
  const AIntegerCol: Integer;
  const ARealCol: Double): Int64;
begin
  { Start a new transition if we have not yet done so. }
  if FUseTransaction and not InTransaction then
    StartTransaction;
  { Bind new values to the prepared statement. }
  FInsertStmt.Bind_Str16(1, ATextCol);
  FInsertStmt.Bind_Int(2, AIntegerCol);
  FInsertStmt.Bind_Double(3, ARealCol);
  { Execute statement and reset immediately. }
  FInsertStmt.StepAndReset;
  Inc(FInsertCount);
  Inc(FSpeedInsertCount);
  Inc(FTransactionInsertCount);
  { Call transaction control after the insertion. }
  CommitAndBeginTransaction;
  { Return RowID of newly inserted record. }
  Result := LastInsertRowID;
end;

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

procedure TLogInsertsDB.SetJournalMode(const Value: UnicodeString);
begin
  if FUseTransaction and InTransaction then
    Commit;
  Execute16('PRAGMA journal_mode=' + Value);
end;

procedure TLogInsertsDB.SetTransactionInsertCountMax(const Value: Cardinal);
begin
  if FTransactionInsertCountMax <> Value then
    begin
      FTransactionInsertCountMax := Value;
      CommitAndBeginTransaction;
    end;
end;

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

procedure TLogInsertsDB.SetTransactionTimeOut(const Value: Cardinal);
begin
  if FTransactionTimeOut <> Value then
    begin
      FTransactionTimeOut := Value;
      CommitAndBeginTransaction;
    end;
end;

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

procedure TLogInsertsDB.SetUseTransaction(const Value: Boolean);
var
  TC: Cardinal;
begin
  if FUseTransaction <> Value then
    begin
      if Value then
        begin
          StartTransaction;
          TC := GetTickCount;

          FSpeedInsertCount := 0;
          FSpeedLastTick := TC;

          FTransactionInsertCount := 0;
          FTransactionLastTick := TC;
        end
      else
        begin
          Commit;
        end;
      FUseTransaction := Value;
    end;
end;

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

end.

