Difference between revisions of "SQLite/pl"

From Lazarus wiki
Jump to navigationJump to search
(→‎Using the SQLdb components with SQLite: tłumaczenie na j. polski)
(→‎Creating user defined collations: tłumaczenie na j. polski)
Line 153: Line 153:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
===Creating user defined collations ===
+
===Tworzenie zestawień zdefiniowanych przez użytkownika===
  
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
// utf8 case-sensitive compare callback function
+
// Funkcja daje wynik porównania z rozróżnianiem wielkości liter w utf8
 
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 
var S1, S2: AnsiString;
 
var S1, S2: AnsiString;
Line 165: Line 165:
 
end;
 
end;
  
// utf8 case-insensitive compare callback function
+
// Funkcja daje wynik porównania bez rozróżniania wielkości liter w utf8
 
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 
var S1, S2: AnsiString;
 
var S1, S2: AnsiString;
Line 174: Line 174:
 
end;
 
end;
  
// register collation using SQLite3 API (requires sqlite3dyn unit):
+
// zarejestruj porządek sortowania za pomocą SQLite3 API (wymaga modułu sqlite3dyn):
 
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
 
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// or using method of TSQLite3Connection:
+
// lub za pomocą metody TSQLite3Connection:
 
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);   
 
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);   
  
// now we can use case-insensitive comparison in SQL like:
+
// teraz możemy użyć porównania bez rozróżniania wielkości liter w SQL, takich jak:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'
+
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'ą'
  
// but this does not work for LIKE operator
+
// ale to nie działa dla operatora LIKE
// in order to support also LIKE operator we must overload default LIKE function using sqlite3_create_function()
+
// w celu obsługi również operatora LIKE musimy przeciążyć domyślną funkcję LIKE za pomocą sqlite3_create_function()
 
// http://www.sqlite.org/lang_corefunc.html#like
 
// http://www.sqlite.org/lang_corefunc.html#like
 
</syntaxhighlight>
 
</syntaxhighlight>

Revision as of 02:25, 17 November 2021

English (en) español (es) français (fr) 日本語 (ja) polski (pl) русский (ru)

Portal bazy danych

Bibliografia:

Poradniki/porady praktyczne:

Bazy danych

Advantage - MySQL - MS SQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Obsługa SQLite i FPC/Lazarus

SQLite to wbudowana (nie serwerowa) baza danych dla jednego użytkownika, która może być używana w aplikacjach FPC i Lazarus. Aby uzyskać dostęp do SQLite z programów FPC/Lazarus, można użyć różnych sterowników. Wszystkie sterowniki do poprawnej pracy potrzebują biblioteki/dll SQLite umieszczonej w katalogu wykonywalnym (którym może być katalog projektu lub np. (katalog projektu)/lib/architecture/ w zależności od ustawień projektu Lazarus) (i dystrybuowane z plikiem wykonywalnym).

Biblioteka ta może być również umieszona w twoim katalogu Lazarus IDE. Zobacz [1] oraz czytaj dalej, szczególnie dla Zobacz poniżej

Większość dystrybucji Linuksa ma domyślnie zainstalowany sqlite3 (np. libsqlite3.so.0), ale dystrybucje Ubuntu wymagają również odpowiedniego pakietu Dev. Oba powinny zostać zainstalowane za pośrednictwem menedżera pakietów systemowych i oznaczone jako zależność, a nie dystrybuowane z aplikacją.

Win64: zobacz to ostrzeżenie o nieużywaniu niektórych wersji FPC/Lazarus Win64.

Bezpośredni dostęp do SQLite

Możesz w prosty sposób połączyć SQLite z Lazarusem. Komponenty, które nazywasz LiteDAC. SQLite Data Access Components (LiteDAC) to biblioteka komponentów, która zapewnia natywną łączność z SQLite z Lazarus (i Free Pascal) w systemach Windows, macOS, iOS, Android, Linux i FreeBSD dla platform 32-bitowych i 64-bitowych. LiteDAC jest przeznaczony dla programistów do tworzenia prawdziwie wieloplatformowych aplikacji bazodanowych SQLite na komputery stacjonarne i mobilne bez konieczności wdrażania dodatkowych bibliotek. Wersję próbną tego komercyjnego produktu można pobrać na stronie Komponenty Lazarusa. Uwaga, w linku zamień wyrażenie (dot) na kropkę.

Wbudowane komponenty SQLDB

FPC/Lazarus oferuje wbudowane komponenty SQLDB, które obejmują obsługę baz danych SQLite (TSQLite3Connection) na zakładce SQLdb Palety Komponentów, które pozwalają m.in. tworzyć GUI z komponentami bazy danych, takimi jak TDBGrids. Zaletą korzystania z komponentów baz danych SQLDB jest to, że dość łatwo jest przejść do innej bazy danych, takiej jak Firebird lub PostgreSQL, bez zbytniego zmieniania programu. Zobacz poniżej szczegóły.

Wsparcie dla Spatialite

Spatialite to rozszerzenia GIS do SQLite, których można używać z poziomu bazy danych SQL. Zobacz Spatialite.

Obsługa szyfrowania w SQLite

W ostatnich wersjach FPC (wdrożonych w marcu 2012 r.) SQLDB zawierał obsługę niektórych rozszerzonych wersji SQLite3, które szyfrują plik bazy danych SQLite przy użyciu algorytmu AES. Użyj właściwości hasła, aby ustawić klucz szyfrowania.

Przykłady:

  • SQLCipher: open source, lecz nie darmowe, dostępne są m.in. pliki binarne Windows (jednak musisz je skompilować samodzielnie)
  • System.Data.SQLite: open source, dostępne są binaria Windows (32, 64, CE), pobierz np. jeden z prekompilowanych plików binarnych i zmień nazwę SQLite.Interop.dll na sqlite3.dll (jeśli używasz statycznie dołączanych bibliotek, prawdopodobnie musisz zmienić nazwę System.Data.SQLite.DLL na sqlite3.dll)
  • wxSQLite3: open source
  • sqleet: open source, brak zależności od innych bibliotek, łatwo kompiluje się za pomocą GCC do plików wykonywalnych i pliki bibliotek, wieloplatformowy. Mechanizmy szyfrowania są dostępne zarówno interaktywnie, jak i poprzez sqleet API.

sqlite3backup

sqlite3backup to jest moduł dostarczany z FPC (nie z Lazarus, ale może być w nim używany programowo), który zapewnia funkcje tworzenia kopii zapasowych/przywracania dla SQLite3. Wykorzystuje sqlite3conn bazy danych SQLDB.

Zeos

Zeos

SQLitePass

Komponenty SQLitePass. Ostatnia aktualizacja kodu w 2010. Ostatnia aktywność na forum w 2011.

TSQLite3Dataset i TSQLiteDataset

Istnieją również oddzielne pakiety TSQLiteDataset (moduł sqliteds) i TSQLite3Dataset (moduł sqlite3ds); poniżej znajdziesz opis, jak z nich korzystać. Odwiedź stronę główną sqlite4fpc, aby znaleźć informacje o interfejsie API i więcej samouczków.

TSqliteDataset i TSqlite3Dataset są potomkami TDataSet, które uzyskują dostęp odpowiednio do baz danych sqlite 2.8.x i 3.x.x. W przypadku nowych projektów prawdopodobnie użyjesz TSQlite3Dataset, ponieważ SQLite 3.x jest aktualną wersją.

Poniżej znajduje się lista głównych zalet i wad w porównaniu z innymi sterownikami/metodami dostępu FPC/Lazarus SQLite:

Zalety:

  • Elastyczny: programiści mogą wybrać, czy chcą używać języka SQL, czy nie, co pozwala im pracować z prostymi układami tabel lub dowolnym złożonym układem, na który pozwala SQL/sqlite

Wady:

  • Zmiana na inne bazy danych jest trudniejsza niż w przypadku korzystania z komponentów SQLDB lub Zeos
Note-icon.png

Uwaga: Biorąc pod uwagę powyższe, wielu użytkowników będzie używać SQLDB lub Zeos ze względu na zalety, chyba że potrzebują dostępu niższego poziomu do biblioteki SQLite

Używanie komponentów SQLdb z SQLite

Te instrukcje koncentrują się na specyfice SQLDB (TSQLite3Connection) dla SQLite. Aby uzyskać ogólny przegląd, spójrz na SqlDBHowto, który zawiera przydatne informacje o komponentach SQLdb.

Zobacz SQLdb_Tutorial1, aby zapoznać się z samouczkiem na temat tworzenia GUI programu obsługującego bazę danych, który jest napisany dla SQLite/SQLDB (jak również dla Firebird/SQLDB, PostgreSQL/SQLDB, w zasadzie wszystkich obsługiwanych przez RDBMS SQLDB).

Wykorzystamy kombinację trzech komponentów z zakładki Lazarus SQLdb: TSQLite3Connection, TSQLTransaction oraz TSQLQuery. TSQLQuery działa jak nasz TDataSet; w najprostszym przypadku reprezentuje po prostu jedną z naszych tabel. Dla uproszczenia: upewnij się, że masz już istniejący plik bazy danych SQLite i nie musisz teraz tworzyć nowego. TSQLite3Connection można znaleźć w module sqlite3conn, ktory pownieneś zadeklarować samodzielnie jeśli pracujesz we FreePascalu.

Te trzy komponenty łączy się ze sobą zwykle tak: W TSQLQuery ustaw właściwości Database i Transaction, a w TSQLTransaction ustaw właściwość Database. Nie ma wiele do zrobienia w komponentach Transaction i Connection, większość interesujących rzeczy zostanie zrobione w TSQLQuery. Skonfiguruj komponenty w następujący sposób:

TSQLite3Connection:

  • DatabaseName: Ustaw tę właściwość na nazwę pliku (ścieżkę bezwzględną!) swojego pliku SQLite. Niestety, nie można po prostu użyć ścieżki względnej, która działa bez zmian w czasie projektowania i w czasie wykonywania ***czy to nadal prawda? Czy nie można po prostu skopiować plik db w skrypcie powłoki po kompilacji lub połączyć go symbolicznie?***. Należy upewnić się, że podczas uruchamiania aplikacji poprawna ścieżka do pliku jest zawsze ustawiana programowo, bez względu na to, co zawierała w czasie projektowania.

Uwaga: Aby ustawić pełną ścieżkę biblioteki (jeśli umieścisz swoją bibliotekę sqlite dll/so/dylib w miejscu, w którym system operacyjny go nie znajdzie, na przykład w katalogu aplikacji w systemie Linux/OSX), możesz ustawić właściwość SQLiteLibraryName (PRZED nawiązaniem jakiegokolwiek połączenia, np. w zdarzeniu OnCreate formularza głównego), np.:

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

  • SQL: Ustaw ją na proste zapytanie wybierające dane z jednej ze swoich tabel. Na przykład, jeśli masz tabelę „foo” i chcesz, aby ten zbiór danych reprezentował tę tabelę, użyj po prostu:
    SELECT * FROM foo
    
  • Active: Ustaw to na True z poziomu IDE, aby sprawdzić, czy wszystko jest poprawnie skonfigurowane. Spowoduje to również automatyczne aktywowanie transakcji i obiektów połączenia. Jeśli pojawi się błąd, oznacza to, że nazwa bazy danych połączenia jest niepoprawna lub zapytanie SQL jest nieprawidłowe. Później, gdy zakończymy dodawanie pól (patrz poniżej), ustaw je ponownie jako nieaktywne, nie chcemy, aby IDE blokowało bazę danych SQLite (pojedynczy użytkownik!) podczas testowania aplikacji.
  • Prawdopodobnie nie jest to konieczne do poprawnego działania - będzie wymagało sprawdzenia (czerwiec 2012) Teraz możemy dodać Pola do naszego TSQLQuery. Podczas gdy komponenty są nadal aktywne, kliknij prawym przyciskiem myszy i „edytuj pola...”. Kliknij przycisk „+” i dodaj pola. Wyświetli się lista wszystkich pól zwróconych przez Twoje zapytanie SQL. Dodaj wszystkie potrzebne pola, możesz również dodać tutaj pola wyszukiwania; w takim przypadku po prostu upewnij się, że zdefiniowałeś już wszystkie potrzebne pola w innych zestawach danych, zanim zaczniesz dodawać pola odnośników, które się do nich odwołują. Jeśli Twoja tabela ma wiele kolumn i nie potrzebujesz ich wszystkich, możesz je po prostu pominąć, możesz również nieco bardziej uszczegółowić swój kod SQL.
  • W swoim kodzie musisz wywołać zdarzenia SQLQuery.ApplyUpdates i SQLTransaction.Commit, TSQLQuery.AfterPost i AfterInsert, które są do tego dobrym miejscem, gdy używasz go z kontrolkami obsługującymi dane, ale oczywiście możesz również odłożyć te wywołania na później. Jeśli te wywołania nie zostaną wykonane, baza danych nie zostanie zaktualizowana.
  • „Baza danych jest zablokowana”: IDE może nadal blokować bazę danych (SQLite jest bazą danych jednego użytkownika), prawdopodobnie zapomniałeś ustawić komponenty jako nieaktywne i ponownie rozłączone po zakończeniu definiowania wszystkich pól obiektów TSQLQuery. Użyj zdarzenia OnCreate formularza, aby ustawić ścieżkę i aktywować obiekty tylko w czasie wykonywania. Większość rzeczy ustawianych w TSQLQuery z poziomu IDE nie wymaga (a niektóre nawet nie pozwalają) by były aktywne w czasie projektowania, jedynym wyjątkiem jest zdefiniowanie pól, z których chce się odczytać projekt tabeli, więc w czasie projektowania stan nieaktywny powinien być normalnym stanem.
  • Wszystkie Twoje tabele powinny mieć klucz podstawowy i musisz upewnić się, że odpowiednie pole ma pfInKey i nic więcej w swoich PoviderFlags (te flagi kontrolują, jak i gdzie pole jest używane podczas automatycznego tworzenia zapytań aktualizujących i usuwających).
  • Jeśli używasz pól wyszukiwania
    • upewnij się, że ProviderFlags dla pola wyszukiwania jest całkowicie pusty, aby nie próbowało użyć swojej nazwy w zapytaniu aktualizującym. Samo pole odnośnika nie jest polem danych, działa tylko na wartość innego pola, odpowiadającego pola klucza i tylko to pole klucza będzie później używane w zapytaniach aktualizujących. Możesz ustawić pole klucza na ukryte, ponieważ zwykle nie chcesz go widzieć w swoim DBGrid, ale należy je zdefiniować.
    • LookupCache musi być ustawiony na True. W chwili pisania tego tekstu z jakiegoś powodu pole odnośnika nie wyświetli nic innego (ale nadal będzie działać) i dziwnie dokładnie odwrotnie jest w przypadku pracy z TSQLite3Dataset lub innymi komponentami TXXXDataset, tutaj musi być ustawione na False. Nie jestem jeszcze pewien, czy jest to zamierzone zachowanie, czy błąd.
  • Zwykle w przypadku prostych tabel nie musisz ustawiać żadnych właściwości InsertSQL, UpdateSQL i DeleteSQL, po prostu zostaw je puste. Jeśli masz ustawione poprawnie flagi dostawcy wszystkich pól, to powinno być w stanie utworzyć potrzebny kod SQL w locie. Aby uzyskać więcej informacji na temat InsertSQL, UpdateSQL i DeleteSQL, zobacz Working_With_TSQLQuery#InsertSQL.2C_UpdateSQL_and_DeleteSQL_-_Basic_use_of_parameters.

Po poprawnym skonfigurowaniu powyższego, powinieneś być teraz w stanie używać TSQLQuery jak każdego innego zestawu TDataset, manipulując jego danymi programowo lub umieszczając TDatasouce w formularzu, łącząc go z TSQLQuery, a następnie używając kontroli danych, takich jak TDBGrid itp.

Tworzenie bazy danych

Metoda TSQLite3Connection.CreateDB odziedziczona z klasy nadrzędnej w rzeczywistości nic nie robi; aby utworzyć bazę danych, jeśli jeszcze nie istnieje plik bazy, wystarczy zapisać dane tabeli, jak w poniższym przykładzie:

(Kod wyodrębniony z przykładu sqlite_encryption_pragma, który jest dostarczany z Lazarus 1.3 i nowszymi)

var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // Upewnij się, że połączenie jest zamknięte, gdy zaczynamy

  try
    // Ponieważ tworzymy tę bazę danych po raz pierwszy,
    // sprawdź, czy plik już istnieje
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // Utwórz bazę danych i tabele
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // Tutaj konfigurujemy tabelę o nazwie „DATA” w nowej bazie danych
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "Current_Time" DateTime NOT NULL,'+
                    ' "User_Name" Char(128) NOT NULL,'+
                    ' "Info" Char(128) NOT NULL);');

        // Tworzenie indeksu na podstawie id w tabeli DATA
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit;

        ShowMessage('Pomyślnie utworzono bazę danych.');
      except
        ShowMessage('Nie można utworzyć nowej bazy danych');
      end;
    end;
  except
    ShowMessage('Nie można sprawdzić, czy plik bazy danych istnieje');
  end;
 end;

Tworzenie zestawień zdefiniowanych przez użytkownika

// Funkcja daje wynik porównania z rozróżnianiem wielkości liter w utf8
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
end;

// Funkcja daje wynik porównania bez rozróżniania wielkości liter w utf8
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
end;

// zarejestruj porządek sortowania za pomocą SQLite3 API (wymaga modułu sqlite3dyn):
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// lub za pomocą metody TSQLite3Connection:
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// teraz możemy użyć porównania bez rozróżniania wielkości liter w SQL, takich jak:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'ą'

// ale to nie działa dla operatora LIKE
// w celu obsługi również operatora LIKE musimy przeciążyć domyślną funkcję LIKE za pomocą sqlite3_create_function()
// http://www.sqlite.org/lang_corefunc.html#like

Creating user defined functions

// example overloading default LOWER() function with user supplied function
// to run this demo, you must add units 'sqlite3dyn' and 'ctypes' to your uses-clause
// and add a const 'SQLITE_DETERMINISTIC' with value $800

procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
var S: AnsiString;
begin
  SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
  S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
  sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
end;

// register function LOWER() using SQLite3 API (requires sqlite3dyn unit):
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);

SQLite3 and Dates

  • SQLite 3 doesn't store dates as a special DateTime value. It can stores them as strings, doubles or integers - see

http://www.sqlite.org/datatype3.html#datetime.

  • In strings, the date separator is '-' as per SQL standard/ISO 8601. Thus, if you do an INSERT using the built-in DATE function, it will store it as something like 'YYYY-MM-DD'.
  • Reading a DateTime value can cause problems for DataSets if they are stored as strings: the .AsDateTime qualifier can stall on an SQLite 'string date' but this can be overcome by using something like strftime(%d/%m/%Y,recdate) AS sqlite3recdate in your SQL SELECT statement, which forces SQLite3 to return the date record in a specified format. (the format string %d/%m/%d corresponds to your locale date format which .AsDateTime will understand) ==> Please open a bug report with an example application demonstrating the problemif this is the case
  • When comparing dates stored as strings (using for example the BETWEEN function) remember that the comparison will always be a string comparison, and will therefore depend on how you have stored the date value.

Default values in local time instead of UTC

CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP return current UTC date and/or time. For local date and/or times we can use:

 DEFAULT (datetime('now','localtime')) for datetime values formated YYYY-MM-DD HH:MM:SS
 DEFAULT (date('now','localtime')) for date value formated YYYY-MM-DD
 DEFAULT (time('now','localtime')) for time value formated HH:MM:SS

SQLDB And SQLite troubleshooting

  • Keep in mind that for designtime support to work (fields etc) Lazarus must find sqlite3.dll too.
  • The same goes for the database filename. Always use absolute path if you use components to extract e.g. fieldnames at designtime. Otherwise the IDE will create an empty file in its directory. In case of trouble, check if the lazarus/ directory doesn't hold a zero byte copy of the database file.
  • If you have master/detail relationship, you need to refresh master dataset after each insert, in order to get value for slave dataset foreign key field. You can do that in AfterPost event of the master dataset, by calling one of the following overloaded procedures:
interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
//This procedure refreshes a dataset and positions cursor to last record
//To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //Get last inserted ID in the database
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  //Now come back to respective row
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Refresh and go back to respective row
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
//This procedure refreshes a dataset and positions cursor to last record
//To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Dangerous!
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //If your dataset is sorted by primary key
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //if you are not sure that the dataset is always sorted by primary key
end;

Vacuum and other operations that must be done outside a transaction

SQLDB seems to always require a connection, but some operations like Pragma and Vacuum must be done outside a transaction. The trick is to end transaction, execute what you must and start transaction again (so that sqldb doesn't get confused:)

  // commit any pending operations or use a "fresh" sqlconnection
  Conn.ExecuteDirect('End Transaction');  // End the transaction started by SQLdb
  Conn.ExecuteDirect('Vacuum');
  Conn.ExecuteDirect('Begin Transaction'); //Start a transaction for SQLdb to use

Using TSQLite3Dataset

This section details how to use the TSQLite2Dataset and TSQLite3Dataset components to access SQlite databases. by Luiz Américo luizmed(at)oi(dot)com(dot)br


Requirements

  • For sqlite2 databases (legacy):
    • FPC 2.0.0 or higher
    • Lazarus 0.9.10 or higher
    • SQLite runtime library 2.8.15 or above*
  • Sqlite2 is not maintained anymore and the binary file cannot be found in the sqlite site
  • For sqlite3 databases:
    • FPC 2.0.2 or higher
    • Lazarus 0.9.11 (svn revision 8443) or higher
    • sqlite runtime library 3.2.1 or higer (get it from www.sqlite.org)

Before initiating a lazarus project, ensure that:

  • the sqlite library is either
    • in the system PATH or
    • in the executable output directory and Lazarus (or current project) directories - this option might work on Windows only
  • under Linux, put cmem as the first unit in uses clause of the main program
    • In Debian, Ubuntu and other Debian-like distros, in order to build Lazarus IDE you must install the packages libsqlite-dev/libsqlite3-dev, not only sqlite/sqlite3 (Also applies to OpenSuSe)

How To Use (Basic Usage)

Install the package found at /components/sqlite directory (see instructions here)

At design time, set the following properties:

  • FileName: path of the sqlite file [required]
  • TableName: name of the table used in the sql statement [required]
  • SQL: a SQL select statement [optional]
  • SaveOnClose: The default value is false, which means that changes are not saved. One can change it to true. [optional]
  • Active: Needs to be set at design time or at program startup. [required]

Creating a Table (Dataset)

Double-click the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button. A simple self-explaining table editor will be shown.

Here are all field types supported by TSqliteDataset and TSqlite3Dataset:

  • Integer
  • AutoInc
  • String
  • Memo
  • Bool
  • Float
  • Word
  • DateTime
  • Date
  • Time
  • LargeInt
  • Currency

Retrieving the data

After creating the table or with a previously created Table, open the dataset with the Open method. If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:

SQL := 'Select * from TABLENAME';

Applying changes to the underlying datafile

To use the ApplyUpdates function, the dataset must contain at least one field that fulfills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

  • Set PrimaryKey property to the name of a Primary Key field
  • Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key)

If one of the two conditions is set, just call

ApplyUpdates;
Note-icon.png

Uwaga: If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.

Note-icon.png

Uwaga: Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.

Master/detail example

Various examples of master/detail relations (e.g. the relation between customer and orders):

Remarks

  • Although it has been tested with 10,000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (especially with Memo Fields).
  • The same datafile (Filename property) can host several tables/datasets
  • Several datasets (different combinations of fields) can be created using the same table simultaneously
  • It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
  • It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
  • Setting filename to a sqlite datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.

Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory

See also