{ DISQLite3 incremental blob demo. Using the incremental blob API, DISQLite3
  can conveniently handle large blobs with no strains to memory.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

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

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

unit DISQLite3_Incremental_Blob_fMain;

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

{$IFDEF DISQLite3_Personal}
!!! This project requires functionality unavailable in DISQLite3 Personal. !!!
!!! To compile, download DISQLite3 Pro from https://www.yunqa.de/delphi/   !!!
{$ENDIF DISQLite3_Personal}

interface

uses
  {$IFDEF HAS_UNITSCOPE}
  System.UITypes, System.Classes,
  Vcl.Controls, Vcl.Forms, Vcl.StdCtrls, Vcl.ComCtrls, Vcl.ExtCtrls,
  {$ELSE HAS_UNITSCOPE}
  Classes, Controls, Forms, StdCtrls, ComCtrls, ExtCtrls,
  {$ENDIF HAS_UNITSCOPE}
  DISQLite3_Incremental_Blob_DB;

type
  TfrmIncrementalBlob = class(TForm)
    ListView: TListView;
    pnlTop: TPanel;
    btnAdd: TButton;
    btnEdit: TButton;
    btnDelete: TButton;
    btnAddExtension: TButton;
    StatusBar: TStatusBar;
    btnSave: TButton;
    cbxExtension: TComboBox;
    procedure btnAddClick(Sender: TObject);
    procedure btnAddExtension_Click(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure cbxExtension_Change(Sender: TObject);
  private
    FDb: TBlobDatabase;
  protected
    function cbxExtension_GetExtension: string;
    procedure ReloadDatabase;
  published
    procedure FormCreate(Sender: TObject);
  end;

const
  APP_TITLE = 'DISQLite3 Demo: Incremental Blob';

var
  frmIncrementalBlob: TfrmIncrementalBlob;

implementation

{$R *.dfm}

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

uses
  {$IFDEF HAS_UNITSCOPE}
  System.SysUtils, Vcl.Dialogs, Vcl.FileCtrl,
  {$ELSE HAS_UNITSCOPE}
  SysUtils, Dialogs, FileCtrl,
  {$ENDIF HAS_UNITSCOPE}
  DISQLite3Api, DISQLite3Database,
  DISQLite3_Incremental_Blob_fAdd, DISQLite3_Incremental_Blob_fEdit;

const
  FILE_NAME = 'Blobs.db3';

  //------------------------------------------------------------------------------
  // TfrmIncrementalBlob form
  //------------------------------------------------------------------------------

procedure TfrmIncrementalBlob.FormCreate(Sender: TObject);
begin
  Caption := APP_TITLE;

  with cbxExtension, Items do
    begin
      Add('*.jpg');
      Add('*.png');
      Add('*.tif');
      ItemIndex := 0;
      OnChange(Self);
    end;

  { Create a database component and open the database. }
  FDb := TBlobDatabase.Create(nil);
  // FDb.Password := 'personal';
  FDb.DatabaseName := FILE_NAME;
  try
    FDb.Open;
  except
    on e: ESQLite3 do
      { If the database does not yet exist, try to create a new one. }
      if e.ErrorCode = SQLITE_CANTOPEN then
        FDb.CreateDatabase
      else
        raise;
    else
      raise;
  end;

  ReloadDatabase;
end;

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

procedure TfrmIncrementalBlob.FormDestroy(Sender: TObject);
begin
  FDb.Free;
end;

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

procedure TfrmIncrementalBlob.ReloadDatabase;
var
  BlobSize: Integer;
  LI: TListItem;
  Idx: Integer;
  Stmt: TDISQLite3Statement;
begin
  ListView.Items.BeginUpdate;
  try
    ListView.Items.Clear;

    Stmt := FDb.Prepare('SELECT Idx, Name, Comment FROM Blobs;');
    try
      while Stmt.Step = SQLITE_ROW do
        begin
          LI := ListView.Items.Add;
          Idx := Stmt.Column_Int64(0);
          LI.Data := Pointer(Idx);
          LI.Caption := Stmt.Column_Str16(1);
          LI.SubItems.Add(Stmt.Column_Str16(2));
          BlobSize := FDb.GetBlobSize(Idx);
          if BlobSize >= 0 then
            LI.SubItems.Add(IntToStr(BlobSize));
        end;
    finally
      Stmt.Free;
    end;
  finally
    ListView.Items.EndUpdate;
  end;
end;

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

{ Add a new blob record to the database. Fill the blob contents from a file. }
procedure TfrmIncrementalBlob.btnAddClick(Sender: TObject);
var
  AddForm: TfrmIncrementalBlobAdd;
  BlobSize: Integer;
  {$IFNDEF COMPILER_6_UP}
  i: Integer;
  {$ENDIF !COMPILER_6_UP}
  Idx: Int64;
  LI: TListItem;
begin
  { Show form to add new blob record. }
  AddForm := TfrmIncrementalBlobAdd.Create(Self);
  try
    if AddForm.ShowModal = mrOk then
      begin
        { Add to database. }
        Idx := FDb.AddBlobFromFile(
          AddForm.edtName.Text,
          AddForm.edtComment.Text,
          AddForm.edtBlobFileName.Text);

        { Add new item to listview ... }
        LI := ListView.Items.Add;
        LI.Data := Pointer(Idx);
        LI.Caption := AddForm.edtName.Text;
        LI.SubItems.Add(AddForm.edtComment.Text);
        BlobSize := FDb.GetBlobSize(Idx);
        if BlobSize >= 0 then
          LI.SubItems.Add(IntToStr(BlobSize));

        { ... and make it visible. }
        {$IFDEF COMPILER_6_UP}
        ListView.ClearSelection;
        {$ELSE COMPILER_6_UP}
        for i := 0 to ListView.Items.Count - 1 do
          ListView.Items[i].Selected := False;
        {$ENDIF COMPILER_6_UP}
        LI.Focused := True;
        LI.Selected := True;
        LI.MakeVisible(True);
      end;
  finally
    AddForm.Free;
  end;
end;

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

{ Search a folder for *.jpg files and add their contents to the database. }
procedure TfrmIncrementalBlob.btnAddExtension_Click(Sender: TObject);
var
  Ext, Folder, FileName: string;
  SR: TSearchRec;
begin
  Ext := cbxExtension_GetExtension;
  Folder := '';
  if Selectdirectory('Select folder with ' + Ext + ' files to add:', '', Folder) then
    begin
      {$IFDEF COMPILER_5_UP}
      Folder := IncludeTrailingPathDelimiter(Folder);
      {$ELSE COMPILER_5_UP}
      if (Length(Folder) = 0) or (Folder[Length(Folder)] <> '\') then
        Folder := Folder + '\';
      {$ENDIF COMPILER_5_UP}
      if FindFirst(Folder + Ext, 0, SR) = 0 then
        try
          FDb.StartTransaction;
          try
            repeat
              FileName := Folder + SR.Name;
              StatusBar.SimpleText := FileName;
              FDb.AddBlobFromFile(FileName, '', FileName);
            until FindNext(SR) <> 0;
            FDb.Commit;
          except;
            FDb.Rollback;
            raise;
          end;
        finally
          FindClose(SR);
          StatusBar.SimpleText := '';
        end;
      ReloadDatabase;
    end;
end;

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

{ Save a database blob to a file. }
procedure TfrmIncrementalBlob.btnSaveClick(Sender: TObject);
var
  LI: TListItem;
  SaveDlg: TSaveDialog;
begin
  LI := ListView.ItemFocused;
  if Assigned(LI) then
    begin
      SaveDlg := TSaveDialog.Create(nil);
      try
        SaveDlg.FileName := LI.Caption;
        if SaveDlg.Execute then
          begin
            FDb.SaveBlobToFile(Integer(LI.Data), SaveDlg.FileName);
          end;
      finally
        SaveDlg.Free;
      end;
    end;
end;

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

{ Edit the focused item and update the database accordingly. }
procedure TfrmIncrementalBlob.btnEditClick(Sender: TObject);
var
  BlobUpdate: TBlobUpdate;
  l: Integer;
  LI: TListItem;
  EditForm: TfrmIncrementalBlobEdit;
  RowID: Int64;
begin
  { Show form to edit blob record. }
  LI := ListView.ItemFocused;
  if Assigned(LI) then
    begin
      EditForm := TfrmIncrementalBlobEdit.Create(Self);
      try
        EditForm.edtName.Text := LI.Caption;
        EditForm.edtComment.Text := LI.SubItems[0];

        if EditForm.ShowModal = mrOk then
          begin
            { Update database record. }
            if EditForm.rbBlobDelete.Checked then
              BlobUpdate := buDelete
            else
              if EditForm.rbBlobReplace.Checked then
                BlobUpdate := buReplace
              else
                BlobUpdate := buNoChange;

            RowID := Integer(LI.Data);
            l := FDb.UpdateBlobFromFile(
              RowID,
              EditForm.edtName.Text,
              EditForm.edtComment.Text,
              EditForm.edtBlobFileName.Text,
              BlobUpdate);

            { Update listview item. }
            LI.Caption := EditForm.edtName.Text;
            LI.SubItems[0] := EditForm.edtComment.Text;
            if l >= 0 then
              LI.SubItems[1] := IntToStr(l);
          end;
      finally
        EditForm.Free;
      end;
    end;
end;

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

{ Delete all records selected in the listview from the database. }
procedure TfrmIncrementalBlob.btnDeleteClick(Sender: TObject);
var
  LI: TListItem;
begin
  LI := ListView.Selected;
  if Assigned(LI) and (MessageDlg('Delete selected?', mtConfirmation, mbOkCancel, 0) = mrOk) then
    try
      FDb.StartTransaction;
      repeat
        FDb.DeleteFromBlobs(Integer(LI.Data));
        LI := ListView.GetNextItem(LI, sdall, [isSelected]);
      until not Assigned(LI);
      FDb.Commit;
    except
      FDb.Rollback;
      raise;
    end;
  ReloadDatabase;
end;

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

function TfrmIncrementalBlob.cbxExtension_GetExtension: string;
begin
  Result := Trim(cbxExtension.Text);
  if Result = '' then
    Result := '*';
end;

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

procedure TfrmIncrementalBlob.cbxExtension_Change(Sender: TObject);
begin
  btnAddExtension.Caption := 'Add ' + cbxExtension_GetExtension + ' ...';
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.

