{ DISQLite3 incremental blob demo: The database class.

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

{$I DICompilers.inc}
{$I DISQLIte3.inc}

interface

uses
  {$IFDEF HAS_UNITSCOPE}System.Classes{$ELSE}Classes{$ENDIF},
  DISystemCompat, DISQLite3Database;

type
  TBlobUpdate = (
    buNoChange,
    buDelete,
    buReplace);

  //------------------------------------------------------------------------------
  // TBlobDatabase
  //------------------------------------------------------------------------------

  { Simple extension to TDISQLite3Database which automates buffered access to
    the Cities table. }
  TBlobDatabase = class(TDISQLite3Database)
  protected
    procedure DoAfterCreateDatabase; override;
  public
    function AddBlobFromFile(const AName, AComment, AFileName: UnicodeString): Int64;
    function AddBlobFromStream(const AName, AComment: UnicodeString; const AStream: TStream): Int64;
    procedure DeleteFromBlobs(const ARowID: Int64);
    function GetBlobSize(const ARowID: Int64): Integer;
    procedure SaveBlobToFile(const ARowID: Int64; const AFileName: UnicodeString);
    procedure SaveBlobToStream(const ARowID: Int64; const AStream: TStream);
    function UpdateBlobFromFile(const ARowID: Int64; AName, AComment, AFileName: UnicodeString; const ABlobUpdate: TBlobUpdate): Integer;
    { Returns the size of the blob >= 0 if the blob has changed.
      If result < 0, the blob did not change. }
    function UpdateBlobFromStream(const ARowID: Int64; AName, AComment: UnicodeString; const AStream: TStream; const ABlobUpdate: TBlobUpdate): Integer;
  end;

implementation

uses
  {$IFDEF HAS_UNITSCOPE}System.SysUtils{$ELSE}SysUtils{$ENDIF},
  DISQLite3Api;

//------------------------------------------------------------------------------
// TBlobDatabase class
//------------------------------------------------------------------------------

function TBlobDatabase.AddBlobFromFile(const AName, AComment, AFileName: UnicodeString): Int64;
var
  s: TFileStream;
begin
  s := TFileStream.Create(AFileName, fmOpenRead or fmShareDenyWrite);
  try
    Result := AddBlobFromStream(AName, AComment, s);
  finally
    s.Free;
  end;
end;

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

function TBlobDatabase.AddBlobFromStream(const AName, AComment: UnicodeString; const AStream: TStream): Int64;
var
  s: TStream;
  l: Integer;
  Stmt: TDISQLite3Statement;
begin
  if Assigned(AStream) then
    l := AStream.Size
  else
    l := 0;

  { Insert the row. }
  Stmt := Prepare('INSERT INTO Blobs (Name, Comment, Blob) VALUES (?,?,?);');
  try
    Stmt.Bind_Str16(1, AName);
    Stmt.Bind_Str16(2, AComment);
    Stmt.Bind_ZeroBlob(3, l);
    Stmt.Step;
    Result := LastInsertRowID;
  finally
    Stmt.Free;
  end;

  { Copy the blob data using the incremental blob stream. }
  if l > 0 then
    begin
      s := TDISQLite3IncrBlobStream.Create(Self, 'main', 'Blobs', 'Blob', Result, 1);
      try
        s.CopyFrom(AStream, l);
      finally
        s.Free;
      end;
    end;
end;

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

procedure TBlobDatabase.DeleteFromBlobs(const ARowID: Int64);
var
  Stmt: TDISQLite3Statement;
begin
  Stmt := Prepare('DELETE FROM Blobs WHERE Idx = ?;');
  try
    Stmt.Bind_Int64(1, ARowID);
    Stmt.Step;
  finally
    Stmt.Free;
  end;
end;

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

{ This method is called after a new database file has been created. It can be
  used to create new tables, indexes, triggers, views etc. which are required
  by the application. }
procedure TBlobDatabase.DoAfterCreateDatabase;
begin
  Execute(
    'CREATE TABLE IF NOT EXISTS Blobs (' + #13#10 +
    '  Idx INTEGER PRIMARY KEY,' + #13#10 +
    '  Name TEXT,' + #13#10 +
    '  Comment TEXT,' + #13#10 +
    '  Blob BLOB);');
  inherited;
end;

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

function TBlobDatabase.GetBlobSize(const ARowID: Int64): Integer;
var
  blob: sqlite3_blob_ptr;
begin
  if sqlite3_blob_open(Handle, 'main', 'Blobs', 'Blob', ARowID, 0, @blob) = SQLITE_OK then
    begin
      Result := sqlite3_blob_bytes(blob);
      sqlite3_blob_close(blob);
    end
  else
    Result := -1;
end;

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

procedure TBlobDatabase.SaveBlobToFile(const ARowID: Int64; const AFileName: UnicodeString);
var
  s: TFileStream;
begin
  s := TFileStream.Create(AFileName, fmCreate);
  try
    SaveBlobToStream(ARowID, s);
  finally
    s.Free;
  end;
end;

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

procedure TBlobDatabase.SaveBlobToStream(const ARowID: Int64; const AStream: TStream);
var
  s: TStream;
begin
  s := TDISQLite3IncrBlobStream.Create(Self, 'main', 'Blobs', 'Blob', ARowID, 0);
  try
    AStream.CopyFrom(s, 0);
  finally
    s.Free;
  end;
end;

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

function TBlobDatabase.UpdateBlobFromFile(const ARowID: Int64; AName, AComment, AFileName: UnicodeString; const ABlobUpdate: TBlobUpdate): Integer;
var
  s: TStream;
begin
  if ABlobUpdate = buReplace then
    begin
      s := TFileStream.Create(AFileName, fmOpenRead or fmShareDenyWrite);
      try
        Result := UpdateBlobFromStream(ARowID, AName, AComment, s, ABlobUpdate);
      finally
        s.Free;
      end;
    end
  else
    Result := UpdateBlobFromStream(ARowID, AName, AComment, nil, ABlobUpdate);
end;

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

function TBlobDatabase.UpdateBlobFromStream(const ARowID: Int64; AName, AComment: UnicodeString; const AStream: TStream; const ABlobUpdate: TBlobUpdate): Integer;
var
  s: TStream;
  SQL: Utf8String;
  Stmt: TDISQLite3Statement;
begin
  { Assemble SQL, depeinding on blob options. }
  SQL := 'UPDATE Blobs SET Name=?, Comment=?';
  case ABlobUpdate of
    buDelete:
      Result := 0;
    buReplace:
      if Assigned(AStream) then
        Result := AStream.Size
      else
        Result := 0;
  else
    Result := -1;
  end;

  if Result = 0 then
    SQL := SQL + ', Blob=zeroblob(0)'
  else
    if Result > 0 then
      SQL := SQL + ', Blob=?';

  SQL := SQL + ' WHERE Idx=:Idx';

  { Update row according to SQL and blob data. }
  Stmt := Prepare(SQL);
  try
    Stmt.Bind_Str16(1, AName);
    Stmt.Bind_Str16(2, AComment);
    if Result > 0 then Stmt.Bind_ZeroBlob(3, Result);
    Stmt.Bind_Int64_By_Name(':Idx', ARowID);
    Stmt.Step;
  finally
    Stmt.Free;
  end;

  { Copy the blob data using the incremental blob stream. }
  if Result > 0 then
    begin
      s := TDISQLite3IncrBlobStream.Create(Self, 'main', 'Blobs', 'Blob', ARowID, 1);
      try
        s.CopyFrom(AStream, Result);
      finally
        s.Free;
      end;
    end;
end;

end.

