Rgd.Sqlite3

rgd.sqlite3

RGD.SQLITE3用於Delphi-輕巧，簡單，有效的SQLITE3接口單元

可以動態或靜態地鏈接SQLITE3庫。

要進行加密，請使用rgd.sqlite3fde.pas靜態地鏈接SQLite的FiREDAC加密版本和訪問FireDac加密數據庫。

學分：

該單元從https://github.com/plashenkov/sqlite3-delphi-fpc中藉鑑了Yury Plashenkov的想法，我一直以其天才的簡單性和清晰度而欽佩它。考慮到Yuri的概念，Delphi的RGD.SQLITE3使用接口對象和匿名方法實現，而Nick Hodges的“在Delphi中讀取“ Delphi in Coding”所獲得的想法，以及一些靈活的東西，用於綁定和獲取數據，並進行交易。

查詢模式：

 {Example Pattern 1 - Stmt := DB.Prepare() and while Stmt.Step...}
var
  Stmt: ISqlite3Statement;
begin
  Stmt := DB.Prepare(
    'SELECT Name,' +
    '       ID' +
    '  FROM Tasks');
  while Stmt.Step = SQLITE_ROW do
  being
    S0 := Stmt.SqlColumn[0].AsText;
    ID := Stmt.SqlColumn[1].AsInt;
    {...}
  end);
end;

{Pattern 2 - with DB.Prepare and Fetch(procedure)...}
with DB.Prepare(
  'SELECT Name,' +
  '       ID' +
  '  FROM Tasks') do Fetch(procedure
begin
  S0 := SqlColumn[0].AsText;
  ID := SqlColumn[1].AsInt;
  {...}
end);

{Pattern 3 - DB.Fetch(SQL, procedure(const Stmt: ISQlite3Statement)...}
DB.Fetch(
  'SELECT Name,' +
  '       ID'    +
  '  FROM Tasks', procedure(const Stmt: ISQlite3Statement)
begin
  S0 := Stmt.SqlColumn[0].AsText;
  ID := Stmt.SqlColumn[1].AsInt;
  {...}
end;

創建數據庫模式...

 procedure TMainForm.CreateDatabase;
begin
  DB := TSqlite3Database.Create;
  DB.Open(':memory:');
  
  {Create Table...}
  DB.Execute(
    ' CREATE TABLE Organizations ( ' +
    '   Name               TEXT,' +
    '   Website            TEXT,' +
    '   Country            TEXT,' +
    '   Description        TEXT,' +
    '   Founded            TEXT,' +
    '   Industry           TEXT,' +
    '   EmployeeCount      INTEGER,' +
    ' PRIMARY KEY (Name ASC))' +
    ' WITHOUT ROWID');

  Stmt_Description := DB.Prepare(
    'SELECT Description' +
    '  FROM Organizations' +
    ' WHERE Name = ?');
end;

示例：從CSV文件插入記錄...

 procedure TMainForm.ReadCsvIntoDatabase;
var
  Lines, Values: TStringlist;
begin
  Lines := TStringlist.Create;
  Values := TStringlist.Create;
  Values.StricDelimiter := True;
  try
    CreateDatabase;
    Lines.LoadFromFile('organizations-1000.csv');
    Lines.Delete(0); {Ignore Header}

    DB.Transaction(procedure
    begin
      with DB.Prepare('INSERT INTO Organizations VALUES (?, ?, ?, ?, ?, ?, ?, ?)') do
      begin
        for var S in Lines do
        begin
          Values.CommaText := S;
          Values.Delete(0); {Ignore first column in our sample .csv}
          BindAndStep(Values.ToStringArray);
        end;
      end;
    end);
  finally
    Values.Free;
    Lines.Free;
  end;
  DB.Execute('ANALYZE');
end;

示例：應用程序定義的功能

 procedure SqlAdf_SizeCategory(Context: Pointer; n: integer; args: PPSQLite3ValueArray); cdecl;
var
  Count: integer;
  Result: string;
begin
  Count := TSqlite3.ValueInt(Args[0]);
  case Count of
    0..500:       Result := 'Small';
    501..5000:    Result := 'Medium';
    5001..MaxInt: Result := 'Large';
  end;
  TSqlite3.ResultText(Context, Result);
end;

{...}

DB.CreateFunction('SizeCategory', 1, @SqlAdf_SizeCategory);

{...}

with DB.Prepare(
  'SELECT OrgID, Name, Website, Country, Industry, Founded, EmployeeCount, SizeCategory(EmployeeCount) as SizeCat' +
  '  FROM Organizations' +
  ' WHERE Country LIKE ?' +
  '   AND SizeCat LIKE ?' +
  ' ORDER BY 2') do BindAndFetch([FCountry, FSizeCat], procedure
begin
  {...}
end