{ This example project demonstrates a simple DISQLite3 GUI, storing Rich Text
  (RTF) in a database. It uses the DISQLite native API.

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

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

interface

uses
  {$IFDEF HAS_UNITSCOPE}
  System.UITypes, System.Classes, Vcl.Forms, Vcl.Menus, Vcl.ImgList,
  Vcl.Controls, Vcl.StdCtrls, Vcl.ComCtrls, Vcl.ExtCtrls, Vcl.Samples.Spin,
  Vcl.ToolWin,
  {$ELSE HAS_UNITSCOPE}
  Classes, Forms, Menus, ImgList, Controls, StdCtrls, ComCtrls, ExtCtrls, Spin,
  ToolWin,
  {$ENDIF HAS_UNITSCOPE}
  DISystemCompat, DISQLite3Api;

type
  TfrmMain = class(TForm)
    ToolbarImages: TImageList;
    tbFormat: TToolBar;
    btnBold: TToolButton;
    btnItalic: TToolButton;
    btnUnderline: TToolButton;
    btnColor: TToolButton;
    cbxFontName: TComboBox;
    edtFontSize: TSpinEdit;
    btnAlignLeft: TToolButton;
    btnAlignCenter: TToolButton;
    btnAlignRight: TToolButton;
    btnBullets: TToolButton;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    LVItems: TListView;
    menuMain: TMainMenu;
    mnuFile: TMenuItem;
    mnuFile_NewDatabase: TMenuItem;
    mnuFile_OpenDatabase: TMenuItem;
    mnuFile_CloseDatabase: TMenuItem;
    pnlDetails: TPanel;
    edtItemName: TEdit;
    edtItemRtf: TRichEdit;
    Label1: TLabel;
    Label2: TLabel;
    Splitter1: TSplitter;
    mnuData: TMenuItem;
    mnuData_New: TMenuItem;
    mnuData_Delete: TMenuItem;
    mnuData_Save: TMenuItem;
    n1: TMenuItem;
    mnuFile_SaveRtf: TMenuItem;
    mnuFile_LoadRtf: TMenuItem;
    n2: TMenuItem;
    mnuData_Reload: TMenuItem;
    n3: TMenuItem;
    mnuData_Vacuum: TMenuItem;
    procedure lvItems_Data(Sender: TObject; Item: TListItem);
    procedure Form_Create(Sender: TObject);
    procedure mnuFile_NewDatabase_Click(Sender: TObject);
    procedure mnuFile_OpenDatabase_Click(Sender: TObject);
    procedure mnuFile_CloseDatabase_Click(Sender: TObject);
    procedure Form_Destroy(Sender: TObject);
    procedure lvItems_Change(Sender: TObject; Item: TListItem; Change: TItemChange);
    procedure lvItems_Resize(Sender: TObject);
    procedure edtItemName_Change(Sender: TObject);
    procedure edtItemRtf_Change(Sender: TObject);
    procedure edtItemRtf_SelectionChange(Sender: TObject);
    procedure btnBold_Click(Sender: TObject);
    procedure btnItalic_Click(Sender: TObject);
    procedure btnUnderline_Click(Sender: TObject);
    procedure btnBullets_Click(Sender: TObject);
    procedure btnAlignLeft_Click(Sender: TObject);
    procedure btnAlignCenter_Click(Sender: TObject);
    procedure btnAlignRight_Click(Sender: TObject);
    procedure edtFontSize_Change(Sender: TObject);
    procedure cbxFontName_Change(Sender: TObject);
    procedure mnuData_New_Click(Sender: TObject);
    procedure mnuData_Delete_Click(Sender: TObject);
    procedure mnuFile_Click(Sender: TObject);
    procedure mnuData_Click(Sender: TObject);
    procedure mnuData_SaveNow_Click(Sender: TObject);
    procedure mnuFile_SaveRtf_Click(Sender: TObject);
    procedure mnuFile_LoadRtf_Click(Sender: TObject);
    procedure mnuData_Reload_Click(Sender: TObject);
    procedure btnColor_Click(Sender: TObject);
    procedure mnuData_Vacuum_Click(Sender: TObject);
  private
    FDb: sqlite3_ptr;
    FStmt_SelectName: sqlite3_stmt_ptr;
    FStmt_SelectRTF: sqlite3_stmt_ptr;
    FStmt_SelectNameRTF: sqlite3_stmt_ptr;
    FStmt_UpdateNameRTF: sqlite3_stmt_ptr;
    FDbFileName: string;
    FData: TList;
    FItemIdx: Integer;
    FItemChanged: Boolean; // True if an item was edited and has been changed.
    FReading: Boolean; // True if we are currently reading an item from the database.
    procedure DeleteSelected;
    procedure EnableControls(const AValue: Boolean);
    procedure FinalizeStatements;
    procedure InternalCloseDatabase;
    procedure InternalOpenDatabase(const AFileName: string; const ACreateNew: Boolean = False);
    procedure PrepareStatements;
    procedure ReadItemFromDB(const AIdx: Integer);
    function ReadNameFromDB(const AIdx: Integer; out AName: string): Boolean;
    procedure RefreshData;
    procedure WriteItemToDB;
  end;

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

var
  frmMain: TfrmMain;

implementation

uses
  {$IFDEF HAS_UNITSCOPE}
  System.RTLConsts, Winapi.Windows, System.SysUtils, Vcl.Graphics, Vcl.Dialogs
  {$ELSE HAS_UNITSCOPE}
  Windows, {$IFDEF HAS_UNIT_RTLCONSTS}RTLConsts{$ELSE}Consts{$ENDIF}, SysUtils,
  Graphics, Dialogs
  {$ENDIF HAS_UNITSCOPE};

{$R *.dfm}

const
  OPEN_DIALOG_OPTIONS = [ofEnableSizing, ofPathMustExist];
  SAVE_DIALOG_OPTIONS = [ofEnableSizing, ofOverwritePrompt, ofPathMustExist];

  DIALOG_DATABASE_DEFAULTEXT = 'db3';
  DIALOG_DATABASE_FILTER = 'SQLite3 Database (*.db3;*db)|*.db3;*db|Any file (*.*)|*.*';

  DIALOG_RTF_DEFAULTEXT = 'rtf';
  DIALOG_RTF_FILTER = 'RTF file (*.rtf)|*.rtf|Any file (*.*)|*.*';

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

procedure ListView_ClearSelection(const LV: TListView);
var
  i: Integer;
begin
  with LV, Items do
    begin
      BeginUpdate;
      try
        for i := 0 to Count - 1 do
          Items[i].Selected := False;
      finally
        EndUpdate;
      end;
    end;
end;

type
  //------------------------------------------------------------------------------
  // TSetMemoryStream class
  //------------------------------------------------------------------------------

  { A simple TCustomMemoryStream descendant which can be pointed to an existing
    block of memory which is already allocated. This is a read-only stream. }
  TSetMemoryStream = class(TCustomMemoryStream)
  public
    procedure SetPointer(Ptr: Pointer; Size: Integer);
    function Write(const Buffer; Count: LongInt): LongInt; override;
  end;

procedure TSetMemoryStream.SetPointer(Ptr: Pointer; Size: Integer);
begin
  inherited SetPointer(Ptr, Size);
end;

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

function TSetMemoryStream.Write(const Buffer; Count: LongInt): LongInt;
begin
  raise EStreamError.Create(SWriteError);
end;

//------------------------------------------------------------------------------
// TfrmMain class
//------------------------------------------------------------------------------

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

  cbxFontName.Items.Assign(Screen.Fonts);
  FData := TList.Create;

  InternalOpenDatabase(ExtractFilePath(ParamStr(0)) + 'Products.db');

  r := LVItems.Items.Count;
  if r > 0 then
    begin
      Randomize;
      r := Random(r);
      ListView_ClearSelection(LVItems);
      LVItems.Items[r].Selected := True;
      LVItems.Items[r].Focused := True;
    end;

  edtItemRtf.SelStart := 0; // Force a selection
  edtItemRtf_SelectionChange(nil);
end;

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

procedure TfrmMain.Form_Destroy(Sender: TObject);
begin
  InternalCloseDatabase;
  FData.Free;
end;

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

{ Deletes all selected items from the database and updates the controls. }
procedure TfrmMain.DeleteSelected;
var
  Stmt: sqlite3_stmt_ptr;
  i, Idx: Integer;
  LI: TListItem;
begin
  if MessageDlg('Delete selected records?', mtConfirmation, mbOkCancel, 0) <> mrOk then
    Exit;

  LVItems.Items.BeginUpdate;
  try
    sqlite3_exec_fast(FDb, 'BEGIN TRANSACTION');
    sqlite3_check(sqlite3_prepare_v2(
      FDb, // Database handle.
      'DELETE FROM "Items" WHERE "RowID"=?',
      -1, // Length of SQL or -1 if #0-terminated.
      @Stmt, // Pointer to variable storing the prepared statement.
      nil) // Pointer receiving end of SQL statement or nil.
      , FDb);
    try
      if Assigned(Stmt) then
        try
          i := LVItems.Items.Count - 1;
          while i >= 0 do
            begin
              LI := LVItems.Items[i];
              if LI.Selected then
                begin
                  Idx := Integer(FData[i]);
                  sqlite3_check(sqlite3_bind_int(Stmt, 1, Idx), FDb);
                  sqlite3_check(sqlite3_step(Stmt), FDb);
                  sqlite3_check(sqlite3_reset(Stmt), FDb);
                  FData.Delete(i);
                end;
              Dec(i);
            end;
          LVItems.Items.Count := FData.Count;
        finally
          sqlite3_finalize(Stmt);
        end;
      RefreshData;
      sqlite3_exec_fast(FDb, 'COMMIT TRANSACTION');
    except
      sqlite3_exec_fast(FDb, 'ROLLBACK TRANSACTION');
      raise;
    end;
  finally
    LVItems.Items.EndUpdate;
  end;
end;

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

procedure TfrmMain.EnableControls(const AValue: Boolean);
begin
  LVItems.Enabled := AValue;
  edtItemName.Enabled := AValue;
  edtItemRtf.Enabled := AValue;
  tbFormat.Enabled := AValue;
  tbFormat.Invalidate;

  if not AValue then
    begin
      edtItemName.Clear;
      edtItemRtf.Clear;
    end;
end;

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

{ Finalizes all prepared statements before closing the database. }
procedure TfrmMain.FinalizeStatements;
  procedure FinalizeAndNil(var Stmt: sqlite3_stmt_ptr);
  begin
    sqlite3_finalize(Stmt);
    Stmt := nil;
  end;
begin
  FinalizeAndNil(FStmt_SelectName);
  FinalizeAndNil(FStmt_SelectRTF);
  FinalizeAndNil(FStmt_SelectNameRTF);
  FinalizeAndNil(FStmt_UpdateNameRTF);
end;

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

{ Closes the database and updates the controls. }
procedure TfrmMain.InternalCloseDatabase;
begin
  if Assigned(FDb) then
    begin
      { Write current item if modified. }
      WriteItemToDB;
      { Clear the controls. }
      EnableControls(False);
      LVItems.Items.Count := 0;
      LVItems.Refresh;
      edtItemName.Clear;
      edtItemRtf.Clear;
      { Clear the data cache. }
      FData.Clear;
      { Finalize all statements and close the database. }
      FinalizeStatements;
      sqlite3_check(sqlite3_close(FDb), FDb);
      FDb := nil;
    end;
end;

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

{ Creates a new or opens an existing database files. For new files, it
  initializes the database by creating necessary tables and indexes.
  Once opened, it also updates the form's controls to list the records. }
procedure TfrmMain.InternalOpenDatabase(const AFileName: string; const ACreateNew: Boolean = False);
begin
  InternalCloseDatabase;
  if FileExists(AFileName) or ACreateNew then
    begin
      if ACreateNew then
        DeleteFile(AFileName);
      sqlite3_check(sqlite3_open(PUtf8Char(sqlite3_encode_utf8(AFileName)), @FDb), FDb);
      FDbFileName := AFileName;

      if ACreateNew then
        sqlite3_exec_fast(FDb,
          'CREATE TABLE IF NOT EXISTS "Items" ("Name" STRING, "RTF" BLOB);' +
          'CREATE INDEX IF NOT EXISTS "Items_Name" ON "Items" ("Name");');

      PrepareStatements;
      FItemChanged := False;
    end;
  RefreshData;
end;

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

{ Prepares some frequently used SQL database statements. This saves time to
  prepare theses statements many times when they are used repeatedly. }
procedure TfrmMain.PrepareStatements;
  procedure PrepareStatement(const SQL: Utf8String; var Stmt: sqlite3_stmt_ptr);
  begin
    sqlite3_check(sqlite3_prepare_v2(
      FDb, // Database handle.
      PUtf8Char(SQL),
      Length(SQL), // Length of SQL or -1 if #0-terminated.
      @Stmt, // Pointer to variable storing the prepared statement.
      nil) // Pointer receiving end of SQL statement or nil.
      , FDb);
  end;
begin
  PrepareStatement('SELECT "Name" FROM "Items" WHERE "RowID" = ?;', FStmt_SelectName);
  PrepareStatement('SELECT "RTF" FROM "Items" WHERE "RowID"=?;', FStmt_SelectRTF);
  PrepareStatement('SELECT "Name","RTF" FROM "Items" WHERE "RowID"=?;', FStmt_SelectNameRTF);
  PrepareStatement('UPDATE "Items" SET "Name"=?,"RTF"=? WHERE RowID=?;', FStmt_UpdateNameRTF);
end;

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

{ Reads the name column from the database. }
function TfrmMain.ReadNameFromDB(const AIdx: Integer; out AName: string): Boolean;
begin
  Result := Assigned(FStmt_SelectName);
  if Result then
    begin
      sqlite3_check(sqlite3_bind_int(FStmt_SelectName, 1, AIdx), FDb);
      Result := sqlite3_check(sqlite3_step(FStmt_SelectName), FDb) = SQLITE_ROW;
      if Result then
        AName := sqlite3_decode_utf8(sqlite3_column_str(FStmt_SelectName, 0));
      sqlite3_check(sqlite3_reset(FStmt_SelectName), FDb);
    end;
end;

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

{ Reads a full item from the database directly into the form's controls. }
procedure TfrmMain.ReadItemFromDB(const AIdx: Integer);
var
  BlobLen: Integer;
  BlobPtr: Pointer;
  ms: TSetMemoryStream;
begin
  if Assigned(FStmt_SelectNameRTF) then
    try
      FReading := True;
      sqlite3_check(sqlite3_bind_int(FStmt_SelectNameRTF, 1, AIdx), FDb);
      if sqlite3_check(sqlite3_step(FStmt_SelectNameRTF), FDb) = SQLITE_ROW then
        begin
          // Assert(sqlite3_column_type(FStmt_SelectNameRTF, 0) = sqlite_text);
          edtItemName.Text := sqlite3_decode_utf8(sqlite3_column_str(FStmt_SelectNameRTF, 0));

          edtItemRtf.Clear;
          edtItemRtf.DefAttributes.Name := 'Tahoma';
          edtItemRtf.DefAttributes.Size := 12;
          edtItemRtf.DefAttributes.Style := [];
          edtItemRtf.DefAttributes.Color := clBlack;

          { Create a memory stream to load the RTF data. }
          // Assert(sqlite3_column_type(FStmt_SelectNameRTF, 1) = sqlite_blob);
          ms := TSetMemoryStream.Create;
          try
            { Call sqlite3_column_blob before sqlite3_column_bytes.
              See help on sqlite3_column_blob for details. }
            BlobPtr := sqlite3_column_blob(FStmt_SelectNameRTF, 1);
            BlobLen := sqlite3_column_bytes(FStmt_SelectNameRTF, 1);
            ms.SetPointer(BlobPtr, BlobLen);
            edtItemRtf.Lines.LoadFromStream(ms);
          finally
            ms.Free;
          end;

          sqlite3_check(sqlite3_reset(FStmt_SelectNameRTF), FDb);

          FItemIdx := AIdx;
          FItemChanged := False;
          edtItemRtf_SelectionChange(nil);
        end;
    finally
      FReading := False;
    end;
end;

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

{ Rereads all records from the database and updates all controls to
  reflect the latest changes. }
procedure TfrmMain.RefreshData;
var
  i: Integer;
  RowID: Integer;
  Stmt: sqlite3_stmt_ptr;
begin
  LVItems.Items.BeginUpdate;
  FReading := True;
  try
    ListView_ClearSelection(LVItems);
    FData.Clear;

    if Assigned(FDb) then
      begin
        sqlite3_check(sqlite3_prepare_v2(
          FDb, // Database handle.
          'SELECT "RowID" FROM "Items" ORDER BY "Name" COLLATE NOCASE;',
          -1, // Length of SQL or -1 if #0-terminated.
          @Stmt, // Pointer to variable holding the prepared statement.
          nil) // If assigned, receives end of SQL statement.
          , FDb);

        if Assigned(Stmt) then
          try
            while sqlite3_check(sqlite3_step(Stmt), FDb) = SQLITE_ROW do
              begin
                RowID := sqlite3_column_int(Stmt, 0);
                FData.Add(Pointer(RowID));
              end;
          finally
            sqlite3_finalize(Stmt);
          end;
      end;

    { Initialize the virtual listview with the number of items. }
    i := FData.Count;
    LVItems.Items.Count := i;
    if i > 0 then
      begin
        LVItems.Items[0].Selected := True;
        LVItems.Items[0].Focused := True;
      end;
    LVItems.Refresh;
    EnableControls(i > 0);
  finally
    FReading := False;
    LVItems.Items.EndUpdate;
  end;
end;

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

{ Writes a full item directly from the controls to the database. }
procedure TfrmMain.WriteItemToDB;
var
  ItemName: string;
  ItemRtf: TMemoryStream;
begin
  if not FItemChanged then Exit;

  ItemName := edtItemName.Text;

  ItemRtf := TMemoryStream.Create;
  try
    edtItemRtf.Lines.SaveToStream(ItemRtf);

    // Bind the Item's Name
    sqlite3_check(sqlite3_bind_str(FStmt_UpdateNameRTF, 1, sqlite3_encode_utf8(ItemName)), FDb);

    // Bind the Item's RTF
    sqlite3_check(sqlite3_bind_blob(
      FStmt_UpdateNameRTF, // The prepared statement to execute.
      2, // The parameter index.
      ItemRtf.Memory, // Pointer to the BLOB's memory.
      ItemRtf.Size, // Size of the BLOB's memory.
      SQLITE_STATIC), // Memory is static and does not change until commited.
      FDb);

    // Bind the Item's RowID
    sqlite3_check(sqlite3_bind_int(FStmt_UpdateNameRTF, 3, FItemIdx), FDb);

    try
      // Execute the statement ...
      sqlite3_check(sqlite3_step(FStmt_UpdateNameRTF), FDb);
    finally
      // ... and reset.
      sqlite3_reset(FStmt_UpdateNameRTF);
    end;

    FItemChanged := False;
  finally
    ItemRtf.Free;
  end;
  LVItems.Refresh;
end;

//------------------------------------------------------------------------------
// Form Events
//------------------------------------------------------------------------------

procedure TfrmMain.btnAlignCenter_Click(Sender: TObject);
begin
  edtItemRtf.Paragraph.Alignment := taCenter;
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnAlignLeft_Click(Sender: TObject);
begin
  edtItemRtf.Paragraph.Alignment := taLeftJustify;
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnAlignRight_Click(Sender: TObject);
begin
  edtItemRtf.Paragraph.Alignment := taRightJustify;
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnBold_Click(Sender: TObject);
begin
  with edtItemRtf.SelAttributes do
    if btnBold.Down then
      Style := Style - [fsBold]
    else
      Style := Style + [fsBold];
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnBullets_Click(Sender: TObject);
begin
  with edtItemRtf.Paragraph do
    if btnBullets.Down then
      Numbering := nsNone
    else
      Numbering := nsBullet;
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnColor_Click(Sender: TObject);
begin
  with TColorDialog.Create(nil) do
    try
      Color := edtItemRtf.SelAttributes.Color;
      Options := [cdAnyColor, cdFullOpen];
      if Execute then
        begin
          edtItemRtf.SelAttributes.Color := Color;
          edtItemRtf_SelectionChange(Sender);
        end;
    finally
      Free;
    end;
end;

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

procedure TfrmMain.btnItalic_Click(Sender: TObject);
begin
  with edtItemRtf.SelAttributes do
    if btnItalic.Down then
      Style := Style - [fsItalic]
    else
      Style := Style + [fsItalic];
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.btnUnderline_Click(Sender: TObject);
begin
  with edtItemRtf.SelAttributes do
    if btnUnderline.Down then
      Style := Style - [fsUnderline]
    else
      Style := Style + [fsUnderline];
  edtItemRtf_SelectionChange(Sender);
end;

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

procedure TfrmMain.cbxFontName_Change(Sender: TObject);
begin
  edtItemRtf.SelAttributes.Name := cbxFontName.Text;
end;

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

procedure TfrmMain.edtFontSize_Change(Sender: TObject);
var
  i: Integer;
begin
  i := StrToIntDef(edtFontSize.Text, 0);
  if i > 0 then
    edtItemRtf.SelAttributes.Size := i;
end;

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

procedure TfrmMain.edtItemName_Change(Sender: TObject);
begin
  if not FReading then
    FItemChanged := True;
end;

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

procedure TfrmMain.edtItemRtf_Change(Sender: TObject);
begin
  if not FReading then
    FItemChanged := True;
end;

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

procedure TfrmMain.edtItemRtf_SelectionChange(Sender: TObject);
begin
  with edtItemRtf.SelAttributes do
    begin
      cbxFontName.ItemIndex := cbxFontName.Items.IndexOf(Name);
      edtFontSize.Text := IntToStr(Size);

      btnBold.Indeterminate := not (caBold in ConsistentAttributes);
      if not btnBold.Indeterminate then
        btnBold.Down := fsBold in Style;

      btnItalic.Indeterminate := not (caItalic in ConsistentAttributes);
      if not btnItalic.Indeterminate then
        btnItalic.Down := fsItalic in Style;

      btnUnderline.Indeterminate := not (caUnderline in ConsistentAttributes);
      if not btnUnderline.Indeterminate then
        btnUnderline.Down := fsUnderline in Style;
    end;

  case edtItemRtf.Paragraph.Alignment of
    taLeftJustify: btnAlignLeft.Down := True;
    taRightJustify: btnAlignRight.Down := True;
    taCenter: btnAlignCenter.Down := True;
  end;

  btnBullets.Down := edtItemRtf.Paragraph.Numbering <> nsNone;
end;

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

procedure TfrmMain.lvItems_Change(Sender: TObject; Item: TListItem; Change: TItemChange);
var
  Idx: Integer;
begin
  case Change of
    ctState:
      // Is this the focued Item?
      if Assigned(Item) and Item.Focused then
        begin
          Idx := Integer(FData[Item.Index]);
          if Idx <> FItemIdx then
            begin
              WriteItemToDB;
              ReadItemFromDB(Idx);
            end;
        end;
  end;
end;

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

procedure TfrmMain.lvItems_Data(Sender: TObject; Item: TListItem);
var
  Idx: Integer;
  NewCaption: string;
begin
  if not Assigned(Item) then Exit;
  Idx := Integer(FData[Item.Index]);
  if ReadNameFromDB(Idx, NewCaption) then
    Item.Caption := NewCaption;
end;

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

procedure TfrmMain.lvItems_Resize(Sender: TObject);
begin
  LVItems.Columns[0].Width := LVItems.ClientWidth;
end;

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

procedure TfrmMain.mnuData_Click(Sender: TObject);
begin
  mnuData_Save.Enabled := Assigned(FDb) and (FItemIdx > 0) and FItemChanged;
  mnuData_New.Enabled := Assigned(FDb);
  mnuData_Delete.Enabled := Assigned(FDb) and (FItemIdx > 0);
  mnuData_Reload.Enabled := Assigned(FDb);
  mnuData_Vacuum.Enabled := Assigned(FDb);
end;

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

procedure TfrmMain.mnuData_Delete_Click(Sender: TObject);
begin
  DeleteSelected;
end;

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

procedure TfrmMain.mnuData_New_Click(Sender: TObject);
var
  NewRowID: Integer;
  i: Integer;
begin
  sqlite3_exec_fast(FDb, 'INSERT INTO "Items"("Name")VALUES(''New Item'');');
  NewRowID := sqlite3_last_insert_rowid(FDb);
  i := FData.Add(Pointer(NewRowID));

  ListView_ClearSelection(LVItems);
  with LVItems.Items do
    begin
      Count := FData.Count;
      with Item[i] do
        begin
          Selected := True;
          Focused := True;
          MakeVisible(False);
        end;
    end;
  EnableControls(True);
  edtItemName.SetFocus;
end;

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

procedure TfrmMain.mnuData_Reload_Click(Sender: TObject);
begin
  RefreshData;
end;

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

procedure TfrmMain.mnuData_SaveNow_Click(Sender: TObject);
begin
  WriteItemToDB;
end;

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

procedure TfrmMain.mnuData_Vacuum_Click(Sender: TObject);
begin
  {$IFDEF DISQLite3_Personal}
  ShowMessage('Hint: "VACUUM" has no effect in DISQLite3 Personal.');
  {$ENDIF DISQLite3_Personal}
  LVItems.Items.BeginUpdate;
  FinalizeStatements;
  try
    sqlite3_exec_fast(FDb, 'VACUUM;');
  finally
    PrepareStatements;
    LVItems.Items.EndUpdate;
  end;
end;

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

procedure TfrmMain.mnuFile_Click(Sender: TObject);
begin
  mnuFile_CloseDatabase.Enabled := Assigned(FDb);
end;

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

procedure TfrmMain.mnuFile_CloseDatabase_Click(Sender: TObject);
begin
  InternalCloseDatabase;
end;

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

procedure TfrmMain.mnuFile_LoadRtf_Click(Sender: TObject);
begin
  with TOpenDialog.Create(nil) do
    try
      DefaultExt := DIALOG_RTF_DEFAULTEXT;
      Filter := DIALOG_RTF_FILTER;
      Options := OPEN_DIALOG_OPTIONS;
      if Execute then
        edtItemRtf.Lines.LoadFromFile(FileName);
    finally
      Free;
    end;
end;

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

procedure TfrmMain.mnuFile_NewDatabase_Click(Sender: TObject);
begin
  with TSaveDialog.Create(nil) do
    try
      DefaultExt := DIALOG_DATABASE_DEFAULTEXT;
      Filter := DIALOG_DATABASE_FILTER;
      Options := SAVE_DIALOG_OPTIONS;
      if Execute then
        InternalOpenDatabase(FileName, True);
    finally
      Free;
    end;
end;

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

procedure TfrmMain.mnuFile_OpenDatabase_Click(Sender: TObject);
begin
  with TOpenDialog.Create(nil) do
    try
      DefaultExt := DIALOG_DATABASE_DEFAULTEXT;
      Filter := DIALOG_DATABASE_FILTER;
      Options := OPEN_DIALOG_OPTIONS;
      if Execute then
        InternalOpenDatabase(FileName);
    finally
      Free;
    end;
end;

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

procedure TfrmMain.mnuFile_SaveRtf_Click(Sender: TObject);
begin
  with TSaveDialog.Create(nil) do
    try
      DefaultExt := DIALOG_RTF_DEFAULTEXT;
      Filter := DIALOG_RTF_FILTER;
      Options := SAVE_DIALOG_OPTIONS;
      if Execute then
        edtItemRtf.Lines.SaveToFile(FileName);
    finally
      Free;
    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.

