SQLite/pl

This is an old revision of SQLite/pl, edited by Slawek at . The current revision is 144002.

/* TSQLite3Dataset and TSQLiteDataset */ tłumaczenie na j. polski

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 https://forum.lazarus.freepascal.org/index.php?topic=46118.msg327940#msg327940 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:

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:

Wady:

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

Using the SQLdb components with SQLite

These instructions are focused on SQLDB (the TSQLite3Connection) specifics for SQLite. For a general overview, have a look at SqlDBHowto which has some useful information about the SQLdb components.

See SQLdb_Tutorial1 for a tutorial on creating a GUI database-enabled program that is written for SQLite/SQLDB (as well as for Firebird/SQLDB, PostgreSQL/SQLDB, basically any RDBMS SQLDB supports).

We will use a combination of three components from the Lazarus SQLdb tab: TSQLite3Connection, TSQLTransaction and TSQLQuery. The TSQLQuery acts as our TDataset; in the simplest case it just represents one of our tables. For the sake of simplicity: make sure you already have an existing SQLite database file and don't need to create a new one now. TSQLite3Connection can be found in the sqlite3conn unit, if you want to declare it yourself or are working in FreePascal.

The three components are connected with each other as usual: In the TSQLQuery set the properties Database and Transaction, in the TSQLTransaction set the property Database. There is not much to do in the Transaction and Connection components, most of the interesting things will be done in the TSQLQuery. Configure the components as follows:

TSQLite3Connection:

Note: To set the full library path (if you place your sqlite dll/so/dylib in a place where the OS won't find it, like the application directory on Linux/OSX), you can set the SQLiteLibraryName property (BEFORE any connection is established e.g. in the OnCreate event of the main form), like this:

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

SELECT * FROM foo

After the above is all set up correctly, you should now be able to use the TSQLQuery like any other TDataset, either by manipulating its data programmatically or by placing a TDatasouce on the Form, connecting it to the TSQLQuery and then using data contols like TDBGrid etc.

Creating a Database

The TSQLite3Connection.CreateDB method inherited from the parent class actually does nothing; to create a database if no file exists yet, you simply have to write table data as in the following example:

(Code extracted from sqlite_encryption_pragma example that ships with Lazarus 1.3 onwards)

var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  try
    // Since we're making this database for the first time,
    // check whether the file already exists
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // Create the database and the tables
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // Here we're setting up a table named "DATA" in the new database
        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);');

        // Creating an index based upon id in the DATA Table
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit;

        ShowMessage('Succesfully created database.');
      except
        ShowMessage('Unable to Create new Database');
      end;
    end;
  except
    ShowMessage('Unable to check if database file exists');
  end;
 end;

Creating user defined collations

// utf8 case-sensitive compare callback function
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;

// utf8 case-insensitive compare callback function
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;

// register collation using SQLite3 API (requires sqlite3dyn unit):
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// or using method of TSQLite3Connection:
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// now we can use case-insensitive comparison in SQL like:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'

// but this does not work for LIKE operator
// in order to support also LIKE operator we must overload default LIKE function using 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

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

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

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

Before initiating a lazarus project, ensure that:

How To Use (Basic Usage)

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

At design time, set the following properties:

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:

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:

If one of the two conditions is set, just call

ApplyUpdates;

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

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

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

See also