{ This project shows how to manage data retrieved from a DISQLite3 database
  in a grid like fashion. It uses a cache mechanism to buffer a certain number
  of database rows and reduce the number of database reads during data display.
  This keeps memory requirements low and increases performance.

  The grid is realized by a TVirtualStringTree, an open source treeview control
  which can work both as a tree as well as a grid. TVirtualStringTree is part
  of the Virtual Trees package available from:

    https://github.com/JAM-Software/Virtual-TreeView

  This demo accesses the Demo.db3 database and displays the RandomTable data.

  You must compile and run the DISQLite3_Bind_Params example project to create
  the Demo.db3 database and fill the RandomTable before you run this project.

  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_Buffered_Grid_Form_Main;

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

interface

uses
  DISystemCompat, Windows, SysUtils, Classes, Graphics, Controls, Forms,
  ToolWin, StdCtrls, ComCtrls, ExtCtrls,
  VirtualTrees, // Unit missing? Read comment on top.
  DISQLite3Api, DISQLite3Cache;

type

  { Type to store the data as returned from one table row. }
  TRowData = record
    RandomText: Utf8String;
    RandomInt: Utf8String;
  end;
  PRowData = ^TRowData;

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

  { Customized cache class which initializes and finalizes TRowData as defined
    above. }
  TOurCache = class(TDISQLite3Cache)
  protected
    { Virtual method called to initialize an item which is no longer being used. }
    procedure DoInitializeItem(const AItem: Pointer); override;
    { Virtual method called to finalize an item which is no longer being used. }
    procedure DoFinalizeItem(const AItem: Pointer); override;
  end;

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

  { The main form. }
  TfrmMain = class(TForm)
    vt: TVirtualStringTree;
    ToolBar: TToolBar;
    btnReload: TToolButton;
    StatusBar: TStatusBar;
    Timer: TTimer;
    btnEdit: TToolButton;
    btnDelete: TToolButton;
    btnAdd: TToolButton;
    cbxMemory: TCheckBox;
    procedure Form_Create(Sender: TObject);
    procedure Form_Destroy(Sender: TObject);
    procedure btnReload_Click(Sender: TObject);
    procedure Timer_Timer(Sender: TObject);
    procedure btnAdd_Click(Sender: TObject);
    procedure btnDelete_Click(Sender: TObject);
    procedure btnEdit_Click(Sender: TObject);
    procedure cbxMemory_Click(Sender: TObject);
    procedure vt_FocusChanging(Sender: TBaseVirtualTree; OldNode, NewNode: PVirtualNode; OldColumn, NewColumn: TColumnIndex; var Allowed: Boolean);
    procedure vt_GetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType; var CellText: UnicodeString);
    procedure vt_HeaderDragging(Sender: TVTHeader; Column: TColumnIndex; var Allowed: Boolean);
    procedure vt_NewText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; NewText: UnicodeString);
    procedure vt_BeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas; Node: PVirtualNode; Column: TColumnIndex; CellPaintMode: TVTCellPaintMode; CellRect: TRect; var ContentRect: TRect);
    procedure vt_MouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; x, y: Integer);
  private
    FCache: TOurCache;
    FDatabaseReads: Cardinal;
    FReadDataStmt: sqlite3_stmt_ptr;
  protected
    function AddRow: Int64;
    procedure DeleteRow(const AID: Int64);
    procedure LoadTable;
    function ReadRow(const AID: Int64): PRowData;
    procedure WriteRow_Int(const AID: Int64; const AInt: Integer);
    procedure WriteRow_Text(const AID: Int64; const AText: Utf8String);
  end;

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

var
  frmMain: TfrmMain;

implementation

uses
  Dialogs,
  DISQLite3_Demos_Common;

{$R *.dfm}

type
  { The data type associated with each node of the VirtualStringTree:
    Stores a RowID returned from DISQLite3. }
  TNodeData = Int64;
  PNodeData = ^TNodeData;

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

  { Set up some VirtualStringTree properties to have it work as a grid.
    This is of course also possible using the Object Inspector, but
    doing so in code allows for some comments. }

  vt.DefaultText := '';
  { Allocated memory with each node to store the RowID of a single table record. }
  vt.NodeDataSize := SizeOf(TNodeData);

  { Add the columns. }
  with vt.Header.Columns.Add do
    begin
      Alignment := taRightJustify;
      Options := Options + [coFixed];
      Text := 'RowID';
      Width := 60;
    end;
  with vt.Header.Columns.Add do
    begin
      Text := 'RandomText';
      Width := 200;
    end;
  with vt.Header.Columns.Add do
    begin
      Alignment := taRightJustify;
      Text := 'RandomInt';
      Width := 100;
    end;

  { Focusing column 0 is not allowd, so focus on 1st column. }
  vt.FocusedColumn := 1;

  { Show the header. }
  vt.Header.Options := vt.Header.Options + [hoVisible];

  vt.TreeOptions.MiscOptions := vt.TreeOptions.MiscOptions +
    [toEditable, toGridExtensions];
  vt.TreeOptions.PaintOptions := vt.TreeOptions.PaintOptions -
    [toShowRoot, toShowTreeLines];
  vt.TreeOptions.PaintOptions := vt.TreeOptions.PaintOptions +
    [toShowHorzGridLines, toShowVertGridLines];
  vt.TreeOptions.SelectionOptions := vt.TreeOptions.SelectionOptions +
    [toExtendedFocus];

  { Create our cache which buffers the most recently accessed data. }
  FCache := TOurCache.Create(SizeOf(TRowData));
  { Limit the amount of buffered data to keep memory requirements low.
    Larger numbers will result in fewer database reads at the cost of
    increased memory requirements. A reasonable number is about three to four
    times the number of items visible in the grid. This allows some scrolling
    without reading the database. }
  FCache.MaxCount := 256;

  Open_Demo_Database;
  Create_Demo_Tables;
  LoadTable;
end;

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

procedure TfrmMain.Form_Destroy(Sender: TObject);
begin
  { Finalize the prepared statement before closing the database. }
  sqlite3_finalize(FReadDataStmt);
  Close_Demo_Database;
  FCache.Free;
end;

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

procedure TfrmMain.LoadTable;
var
  Stmt: sqlite3_stmt_ptr;
  Node: PVirtualNode;
  NodeData: PNodeData;
  TC: Cardinal;
begin
  { Prevent the grid from updating while adding new records. }
  vt.BeginUpdate;
  try
    { Clear items from the Virtual String Tree. }
    vt.Clear;
    { Invalidate our cache. This will delete all items without actually freeing
      their memory. The memory will be reused when adding new items. }
    FCache.Invalidate;
    FDatabaseReads := 0;

    { Simple time measurement. }
    TC := GetTickCount;

    { Prepare an SQL statement which retrieves all records and returns the RowID
      for each of them. The block below adds all records to the grid and stores
      the RowID with each node. The grid does reuse the RowID during painting
      to retrieve the associated record data from the cache. }
    sqlite3_check(sqlite3_prepare_v2(
      DB, // Database handle.
      'SELECT RowID FROM RandomTable;', // SQL to prepare.
      -1, // Length of SQL or -1 if null-terminated.
      @Stmt, // Pointer to store statement to.
      nil), DB);
    try
      { Iterate all matching records of the SQL query. }
      while sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW do
        begin
          { Add a new row to the grid. }
          Node := vt.AddChild(nil);
          { Retrieve the data associated with that row. }
          NodeData := vt.GetNodeData(Node);
          { Retrieve the RowID from the database and store it to the row's data. }
          NodeData^ := sqlite3_column_int64(Stmt, 0);
        end;
    finally
      sqlite3_finalize(Stmt);
    end;

    { Simple time measurement. }
    TC := GetTickCount - TC;
    StatusBar.Panels[0].Text :=
      Format('%d rows loaded in %d ms', [vt.RootNodeCount, TC]);

    if not Assigned(FReadDataStmt) then
      try
        { Prepare a statment to read a row of data from the database. This is
          used to read the data from the DB which is not already in the cache. }
        sqlite3_check(sqlite3_prepare_v2(
          DB, // Database handle.
          'SELECT RandomText, RandomInt FROM RandomTable WHERE RowID=?;', // SQL to prepare.
          -1, // Length of SQL or -1 if null-terminated.
          @FReadDataStmt, // Pointer to store statement to.
          nil), DB);
      except
        FReadDataStmt := nil;
        raise;
      end;
  finally
    vt.EndUpdate;
  end;
end;

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

function TfrmMain.AddRow: Int64;
var
  Stmt: sqlite3_stmt_ptr;
begin
  sqlite3_check(sqlite3_prepare_v2(
    DB, // Database handle.
    'INSERT INTO RandomTable VALUES (NULL, NULL);', // SQL to prepare.
    -1, // Length of SQL or -1 if null-terminated.
    @Stmt, // Pointer to store statement to.
    nil), DB);
  try
    { Invalidate this item in the cache. This removes the record from the cache
      but does not free its memory which can be reused later. }
    sqlite3_check(sqlite3_step(Stmt), DB);
    Result := sqlite3_last_insert_rowid(DB);
  finally
    sqlite3_finalize(Stmt);
  end;
end;

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

procedure TfrmMain.DeleteRow(const AID: Int64);
var
  Stmt: sqlite3_stmt_ptr;
begin
  sqlite3_check(sqlite3_prepare_v2(
    DB, // Database handle.
    'DELETE FROM RandomTable WHERE RowID=?;', // SQL to prepare.
    -1, // Length of SQL or -1 if null-terminated.
    @Stmt, // Pointer to store statement to.
    nil), DB);
  try
    { Invalidate this item in the cache. This removes the record from the cache
      but does not free its memory which can be reused later. }
    FCache.InvalidateItem(AID);
    sqlite3_check(sqlite3_bind_int64(Stmt, 1, AID), DB);
    sqlite3_check(sqlite3_step(Stmt), DB);
  finally
    sqlite3_finalize(Stmt);
  end;
end;

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

function TfrmMain.ReadRow(const AID: Int64): PRowData;
begin
  { First try to retrieve the row from the cache. If it is already cached,
    we are done and do not need to access the database. }
  Result := FCache.GetItem(AID);
  if not Assigned(Result) then
    begin
      { If the row was not cached, add a new row to the cache. If the new count
        exceeds MaxCount, the least recently used row will automatically be
        reused for the new row. }
      Result := FCache.AddItem(AID);
      { Bind the ID to the SQL statment's WHERE clause. }
      sqlite3_check(sqlite3_bind_int64(FReadDataStmt, 1, AID), DB);
      try
        { Execute the prepared statement ... }
        if sqlite3_check(sqlite3_step(FReadDataStmt), DB) = SQLITE_ROW then
          begin
            { ... and store the data to the row in the buffer. }
            Result^.RandomText := sqlite3_column_str(FReadDataStmt, 0);
            Result^.RandomInt := sqlite3_column_str(FReadDataStmt, 1);
          end;
      finally
        { Always reset the statement immediately after using it to avoid
          conflicting database statments. }
        sqlite3_reset(FReadDataStmt);
      end;
      { Report that we have accessed the database. }
      Inc(FDatabaseReads);
      StatusBar.Panels[1].Text := Format('%d reads', [FDatabaseReads]);
    end;
end;

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

procedure TfrmMain.WriteRow_Int(const AID: Int64; const AInt: Integer);
var
  Stmt: sqlite3_stmt_ptr;
begin
  sqlite3_check(sqlite3_prepare_v2(
    DB, // Database handle.
    'UPDATE RandomTable SET RandomInt=? WHERE RowID=?;', // SQL to prepare.
    -1, // Length of SQL or -1 if null-terminated.
    @Stmt, // Pointer to store statement to.
    nil), DB);
  try
    { Invalidate this item in the cache to force the next draw to requrey the
      database and return the most recent value. }
    FCache.InvalidateItem(AID);
    sqlite3_check(sqlite3_bind_int(Stmt, 1, AInt), DB);
    sqlite3_check(sqlite3_bind_int64(Stmt, 2, AID), DB);
    sqlite3_check(sqlite3_step(Stmt), DB);
  finally
    sqlite3_finalize(Stmt);
  end;
end;

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

procedure TfrmMain.WriteRow_Text(const AID: Int64; const AText: Utf8String);
var
  Stmt: sqlite3_stmt_ptr;
begin
  sqlite3_check(sqlite3_prepare_v2(
    DB, // Database handle.
    'UPDATE RandomTable SET RandomText=? WHERE RowID=?;', // SQL to prepare.
    -1, // Length of SQL or -1 if null-terminated.
    @Stmt, // Pointer to store statement to.
    nil), DB);
  try
    { Invalidate this item in the cache to force the next draw to requrey the
      database and return the most recent value. }
    FCache.InvalidateItem(AID);
    sqlite3_check(sqlite3_bind_str(Stmt, 1, AText), DB);
    sqlite3_check(sqlite3_bind_int64(Stmt, 2, AID), DB);
    sqlite3_check(sqlite3_step(Stmt), DB);
  finally
    sqlite3_finalize(Stmt);
  end;
end;

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

procedure TfrmMain.vt_BeforeCellPaint(
  Sender: TBaseVirtualTree;
  TargetCanvas: TCanvas;
  Node: PVirtualNode;
  Column: TColumnIndex;
  CellPaintMode: TVTCellPaintMode;
  CellRect: TRect;
  var ContentRect: TRect);
const
  Triangle: array[0..2] of TPoint =
    ((x: 4; y: 5), (x: 8; y: 9), (x: 4; y: 13));
begin
  if Column = 0 then
    begin
      { Simulate a fixed column by filling the main column with an edge similar to that of TCustomGrid. }
      DrawEdge(TargetCanvas.Handle, CellRect, BDR_RAISEDINNER, BF_MIDDLE or BF_RECT or BF_SOFT);
      if Node = Sender.FocusedNode then
        begin
          TargetCanvas.Brush.Color := clBlack;
          TargetCanvas.Polygon(Triangle);
        end;

    end;
end;

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

procedure TfrmMain.vt_FocusChanging(
  Sender: TBaseVirtualTree;
  OldNode, NewNode: PVirtualNode;
  OldColumn, NewColumn: TColumnIndex;
  var Allowed: Boolean);
begin
  { Don't allow focus to change to the fixed RowID column. }
  Allowed := (NewColumn > NoColumn) and
    not (coFixed in vt.Header.Columns[NewColumn].Options);
end;

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

procedure TfrmMain.vt_GetText(
  Sender: TBaseVirtualTree;
  Node: PVirtualNode;
  Column: TColumnIndex;
  TextType: TVSTTextType;
  var CellText: UnicodeString);
var
  NodeData: PNodeData;
begin
  NodeData := Sender.GetNodeData(Node);
  case Column of
    0:
      begin
        CellText := IntToStr(NodeData^);
      end;
    1:
      begin
        CellText := sqlite3_decode_utf8(ReadRow(NodeData^)^.RandomText);
      end;
    2:
      begin
        CellText := sqlite3_decode_utf8(ReadRow(NodeData^)^.RandomInt);
      end;
  end;
end;

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

procedure TfrmMain.vt_HeaderDragging(
  Sender: TVTHeader;
  Column: TColumnIndex;
  var Allowed: Boolean);
begin
  { Don't allow dragging the fixed RowID column. }
  Allowed := (Column > NoColumn) and
    not (coFixed in Sender.Columns[Column].Options)
end;

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

procedure TfrmMain.vt_MouseDown(
  Sender: TObject;
  Button: TMouseButton;
  Shift: TShiftState;
  x, y: Integer);
var
  HitInfo: THitInfo;
begin
  if tsLeftButtonDown in vt.TreeStates then
    begin
      vt.GetHitTestInfoAt(x, y, True, HitInfo);
      if Assigned(HitInfo.HitNode) and
        (HitInfo.HitColumn >= 0) and
        (coFixed in vt.Header.Columns[HitInfo.HitColumn].Options) then
        vt.FocusedNode := HitInfo.HitNode;
    end;
end;

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

procedure TfrmMain.vt_NewText(
  Sender: TBaseVirtualTree;
  Node: PVirtualNode;
  Column: TColumnIndex;
  NewText: UnicodeString);
var
  NodeData: PNodeData;
  RowData: TRowData;
  i: Integer;
begin
  { A record has been edited. Write the new text to the database. }
  NodeData := Sender.GetNodeData(Node);
  RowData := ReadRow(NodeData^)^;
  case Column of
    1:
      WriteRow_Text(NodeData^, sqlite3_encode_utf8(NewText));
    2:
      try
        i := StrToInt(NewText);
        WriteRow_Int(NodeData^, i);
      except
        on e: Exception do
          ShowMessage(e.Message);
      end;
  else
    Exit;
  end;

end;

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

procedure TfrmMain.btnAdd_Click(Sender: TObject);
var
  NewID: Int64;
  NewNode: PVirtualNode;
  newnodedata: PNodeData;
begin
  NewID := AddRow;
  NewNode := vt.AddChild(nil);
  newnodedata := vt.GetNodeData(NewNode);
  newnodedata^ := NewID;
  vt.EditNode(NewNode, 1);
end;

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

procedure TfrmMain.btnDelete_Click(Sender: TObject);
var
  FN: PVirtualNode;
  NodeData: PNodeData;
begin
  FN := vt.FocusedNode;
  if Assigned(FN) then
    begin
      NodeData := vt.GetNodeData(FN);
      DeleteRow(NodeData^);
      vt.DeleteNode(FN);
    end
  else
    ShowMessage('Please select a record to delete.');
end;

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

procedure TfrmMain.btnEdit_Click(Sender: TObject);
var
  FN: PVirtualNode;
begin
  FN := vt.FocusedNode;
  if Assigned(FN) then
    vt.EditNode(FN, vt.FocusedColumn)
  else
    ShowMessage('Please select a cell to edit.');
end;

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

procedure TfrmMain.btnReload_Click(Sender: TObject);
begin
  LoadTable;
end;

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

procedure TfrmMain.cbxMemory_Click(Sender: TObject);
begin
  Timer.Enabled := cbxMemory.Checked;
  if Timer.Enabled then
    Timer_Timer(nil)
  else
    StatusBar.Panels[2].Text := '';
end;

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

procedure TfrmMain.Timer_Timer(Sender: TObject);
begin
  StatusBar.Panels[2].Text :=
    Format('%d KB memory used', [(MemoryUsed + 1) div 1024]);
end;

//------------------------------------------------------------------------------
// TOurCache
//------------------------------------------------------------------------------

procedure TOurCache.DoFinalizeItem(const AItem: Pointer);
begin
  Finalize(PRowData(AItem)^);
end;

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

procedure TOurCache.DoInitializeItem(const AItem: Pointer);
begin
  FillChar(AItem^, ItemSize, 0);
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.

