disqlite3_buffered_grid_form_main.pas

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· PAS 代码 · 共 606 行 · 第 1/2 页

{ 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:

    http://www.soft-gems.net

  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/delphi/

  Copyright (c) 2005-2007 Ralf Junker, The Delphi Inspiration <[e-mail]>

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

unit DISQLite3_Buffered_Grid_Form_Main;

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

interface

uses
  Windows, SysUtils, Classes, Graphics, Controls, Forms, ToolWin, StdCtrls, ComCtrls, ExtCtrls,
  VirtualTrees, // Open source from http://www.soft-gems.net, read introduction above.
  DISQLite3Api, DISQLite3Cache;

type

  { Type to store the data as returned from one table row. }
  TRowData = record
    RandomText: AnsiString;
    RandomInt: AnsiString;
  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: WideString);
    procedure vt_HeaderDragging(Sender: TVTHeader; Column: TColumnIndex; var Allowed: Boolean);
    procedure vt_NewText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; NewText: WideString);
    procedure vt_BeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas; Node: PVirtualNode; Column: TColumnIndex; CellRect: TRect);
    procedure vt_MouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; x, y: Integer);
  private
    FCache: TOurCache;
    FDatabaseReads: Cardinal;
    FReadDataStmt: TDISQLite3StatementHandle;
  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: AnsiString);
  end;

const
  APP_TITLE = 'DISQLite3 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_check(sqlite3_finalize(FReadDataStmt), DB);
  Close_Demo_Database;
  FCache.Free;
end;

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

procedure TfrmMain.LoadTable;
var
  Stmt: TDISQLite3StatementHandle;
  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(
      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_check(sqlite3_finalize(Stmt), DB);
    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 will
    be used if the data is not already in the cache. }
  sqlite3_check(sqlite3_prepare(
    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: TDISQLite3StatementHandle;
begin
  sqlite3_check(sqlite3_prepare(
    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_check(sqlite3_finalize(Stmt), DB);
  end;
end;

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

procedure TfrmMain.DeleteRow(const AID: Int64);
var
  Stmt: TDISQLite3StatementHandle;
begin
  sqlite3_check(sqlite3_prepare(
    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_check(sqlite3_finalize(Stmt), DB);
  end;
end;