disqlite3_encryption_citiesdb.pas

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· PAS 代码 · 共 195 行

unit DISQLite3_Encryption_CitiesDB;

interface

uses
  DISQLite3Cache, DISQLite3Database;

type
  { Record to store information about a city. }
  TCityRec = record
    City: WideString;
    Country: WideString;
    Population: Integer;
  end;
  PCityRec = ^TCityRec;

  //------------------------------------------------------------------------------
  // TCitiesCache class
  //------------------------------------------------------------------------------

  { Cache for TCityRec (above), used by TCitiesDatabase (below). }
  TCitiesCache = class(TDIAbstractSQLite3Cache)
    procedure DoInitializeItem(const AItem: Pointer); override;
    procedure DoFinalizeItem(const AItem: Pointer); override;
  end;

  //------------------------------------------------------------------------------
  // TCitiesDatabase
  //------------------------------------------------------------------------------

  { Simple extension to TDISQLite3Database which automates buffered access to
    the Cities table. }
  TCitiesDatabase = class(TDISQLite3Database)
  private
    FCitiesCache: TCitiesCache;
  protected
    procedure DoAfterConnect; override;
    procedure DoAfterCreateDatabase; override;
    procedure DoBeforeDisconnect; override;
  public
    function AddCity(const ACity, ACountry: WideString; const APopulation: Integer): Int64;
    procedure AddRandomCities(ACount: Integer);
    function AddRandomCity: Int64;
    procedure DeleteFromCities(const ARowID: Int64);
    function GetCity(const ARowID: Int64): PCityRec;

    property CitiesCache: TCitiesCache read FCitiesCache;
  end;

implementation

uses
  DISQLite3Api, Cities;

//------------------------------------------------------------------------------
// TCitiesCache class
//------------------------------------------------------------------------------

procedure TCitiesCache.DoInitializeItem(const AItem: Pointer);
begin
  Initialize(PCityRec(AItem)^);
end;

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

procedure TCitiesCache.DoFinalizeItem(const AItem: Pointer);
begin
  Finalize(PCityRec(AItem)^);
end;

//------------------------------------------------------------------------------
// TCitiesDatabase class
//------------------------------------------------------------------------------

function TCitiesDatabase.AddCity(const ACity, ACountry: WideString; const APopulation: Integer): Int64;
var
  Stmt: TDISQLite3Statement;
begin
  Stmt := Prepare16('INSERT INTO Cities (City, Country, Population) VALUES (?,?,?);');
  try
    Stmt.bind_Str16(1, ACity);
    Stmt.bind_Str16(2, ACountry);
    Stmt.bind_Int(3, APopulation);
    Stmt.Step;
    Result := LastInsertRowID;
  finally
    Stmt.Free;
  end;
end;

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

procedure TCitiesDatabase.AddRandomCities(ACount: Integer);
begin
  if ACount > 0 then
    begin
      StartTransaction;
      try
        repeat
          AddRandomCity;
          Dec(ACount);
        until ACount = 0;
      finally
        Commit;
      end;
    end;
end;

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

function TCitiesDatabase.AddRandomCity: Int64;
begin
  with xCities[Random(High(xCities) - Low(xCities))] do
    Result := AddCity(City, Country, Population);
end;

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

procedure TCitiesDatabase.DeleteFromCities(const ARowID: Int64);
var
  Stmt: TDISQLite3Statement;
begin
  Stmt := Prepare16('DELETE FROM Cities WHERE Idx = ?;');
  try
    Stmt.bind_Int64(1, ARowID);
    Stmt.Step;
    FCitiesCache.InvalidateItem(ARowID);
  finally
    Stmt.Free;
  end;
end;

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

{ This method is called after a connecting to / opening the database. }
procedure TCitiesDatabase.DoAfterConnect;
begin
  FCitiesCache := TCitiesCache.Create(SizeOf(TCityRec));
  FCitiesCache.MaxCount := 384;
  inherited;
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 TCitiesDatabase.DoAfterCreateDatabase;
begin
  Execute16(
    'CREATE TABLE IF NOT EXISTS Cities (' + #13#10 +
    '  Idx INTEGER PRIMARY KEY,' + #13#10 +
    '  City TEXT,' + #13#10 +
    '  Country TEXT,' + #13#10 +
    '  Population INTEGER);');
  inherited;
end;

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

{ This method is called before diconnecting from / closing the database. }
procedure TCitiesDatabase.DoBeforeDisconnect;
begin
  FCitiesCache.Free;
  inherited;
end;

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

function TCitiesDatabase.GetCity(const ARowID: Int64): PCityRec;
var
  Stmt: TDISQLite3Statement;
begin
  Result := FCitiesCache.GetItem(ARowID);
  if not Assigned(Result) then
    begin
      Stmt := Prepare16('SELECT City, Country, Population FROM Cities WHERE Idx = ?;');
      try
        Stmt.bind_Int(1, ARowID);
        if Stmt.Step = SQLITE_ROW then
          begin
            Result := FCitiesCache.AddItem(ARowID);
            Result^.City := Stmt.Column_Str16(0);
            Result^.Country := Stmt.Column_Str16(1);
            Result^.Population := Stmt.Column_Int(2);
          end;
      finally
        Stmt.Free;
      end;
    end;
end;

end.