Rgd.Sqlite3

Rgd.sqlite3

Rgd.sqlite3 untuk delphi - unit antarmuka sqlite3 ringan, sederhana, efektif

Dapat menggunakan sqlite3.dll secara dinamis atau statis menghubungkan pustaka sqlite3.

Untuk enkripsi, gunakan rgd.sqlite3fde.pas untuk secara statis menghubungkan versi enkripsi firedac dari basis data terenkripsi SQLITE dan Access Firedac.

Kredit:

Unit ini meminjam ide-ide dari Yury Plashenkov di https://github.com/plashenkov/sqlite3-delphi-fpc, yang selalu saya kagumi karena kesederhanaan dan kejelasan jenius. Dengan konsep -konsep Yuri dalam pikiran, RGD.SQLITE3 untuk Delphi diimplementasikan menggunakan objek antarmuka dan metode anonim, ide -ide yang saya dapatkan dengan membaca "Coding in Delphi" oleh Nick Hodges, ditambah beberapa barang fleksibel untuk mengikat dan mengambil data, dan melakukan transaksi.

Pola kueri:

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

Buat pola database ...

 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;

Contoh: Memasukkan catatan dari file 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;

Contoh: Fungsi yang ditentukan aplikasi

 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