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

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2005-2009 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>

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

unit DISQLite3_Log_Inserts_DB;

{$I DI.inc}
{$I DISQLite3.inc}

interface

uses
  Classes, DISQLite3Database;

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

  { }
  TLogInsertsDB = class(TDISQLite3Database)
  private
    { Number of commits so far. }
    FCommitCount: Cardinal;
    { Prepared insert statement. }
    FInsertStmt: TDISQLite3Statement;
    { 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 transaction was started. }
    FTransactionTime: Cardinal;
    { Timeout in milliseconds after which to commit the transaction. }
    FTransactionTimeOut: Cardinal;
    FUseTransaction: Boolean;
    procedure SetTransactionInsertCountMax(const AValue: Cardinal);
    procedure SetTransactionTimeOut(const AValue: Cardinal);
    procedure SetUseTransaction(const AValue: Boolean);
  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: WideString;
      const AIntegerCol: Integer;
      const ARealCol: Double): Int64;
    property CommitCount: Cardinal read FCommitCount;
  published
    { 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;

implementation

uses
  Windows, DISQLite3Api;

function GetTickDiff(const AOldTickCount, ANewTickCount: Cardinal): Cardinal;
begin
  if ANewTickCount >= AOldTickCount then
    Result := ANewTickCount - AOldTickCount
  else
    Result := High(Cardinal) - AOldTickCount + ANewTickCount;
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: Cardinal; // Current tick count.
begin
  if FUseTransaction then
    begin
      tc := GetTickCount;
      if (FTransactionInsertCount >= FTransactionInsertCountMax) or
        (GetTickDiff(FTransactionTime, tc) > FTransactionTimeOut) then
        begin
          Commit;
          Inc(FCommitCount);
          StartTransaction;
          FTransactionInsertCount := 0;
          FTransactionTime := tc;
        end;
    end
  else
    begin
      { Count implicit commit of autocommit mode. }
      Inc(FCommitCount);
    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
  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.
  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: WideString;
  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(FTransactionInsertCount);
  { Call transaction control after the insertion. }
  CommitAndBeginTransaction;
  { Return RowID of newly inserted record. }
  Result := LastInsertRowID;
end;

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

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

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

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

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

procedure TLogInsertsDB.SetUseTransaction(const AValue: Boolean);
begin
  if FUseTransaction <> AValue then
    begin
      if AValue then
        begin
          StartTransaction;
          FTransactionInsertCount := 0;
          FTransactionTime := GetTickCount;
        end
      else
        begin
          Commit;
        end;
      FUseTransaction := AValue;
    end;
end;

end.

