SQLite/ja

This is an old revision of SQLite/ja, edited by Ariben at . The current revision is 126932.

/* SQLite と FPC/Lazarus によるサポートについて */

Databases portal

References:

Tutorials/practical articles:

Databases

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

SQLite と FPC/Lazarus によるサポートについて

SQLiteは、FPCとLazarusアプリケーションで使用できる組み込み(非サーバー)の単一ユーザーデータベースである。さまざまなドライバーを使用して、FPC/LazarusプログラムからSQLiteにアクセスできる。すべてのドライバーは、動作するために実行可能ディレクトリ(プロジェクトディレクトリまたはLazarusプロジェクト設定に依存する)にSQLiteライブラリ/dllが必要である(および実行可能ファイルと一緒に配布される)。

Win64: FPC/Lazarus Win64に関しては、特定のFPC/Lazarus Win64バージョンを使用しないようにすることに関する警告がある。

SQLite への直接アクセス

LiteDAC(SQLite Data Access Components)は、Lazarus(およびFree Pascal)からWindows、Mac OS X、iOS、Android、Linux、FreeBSDの32ビットおよび64ビットプラットフォームに対して、SQLiteへのネイティブ接続を提供するコンポーネントのライブラリである。LiteDACは、プログラマーが追加のライブラリをデプロイすることなく、真にクロスプラットフォームなデスクトップおよびモバイルSQLiteデータベースアプリケーションを開発できるように設計されている。

商業的な製品のトライアルバージョンを以下でダウンロードできる: Lazarus components

ビルトインの SQLDB

FPC/Lazarusには、組み込みのSQLDBコンポーネントが含まれており、これにはSQLiteデータベースへのサポート(TSQLite3Connection/ja)が含まれている。これはComponent Palette/jaのSQLdb tab/jaからアクセスでき、例えばTDBGrid/jaなどのデータベースコンポーネントを使用したGUIを作成することができる。SQLDBを使用する利点は、プログラムをあまり変更することなく、FirebirdやPostgreSQLなどの異なるデータベースに簡単に変更できる。

詳細は以下を参照のこと

Spatialiteサポート

Spatialiteは、SQLiteにGIS(地理情報システム)機能を追加したもので、SQLDB内から使用することができる。詳細については、Spatialiteを参照のこと。

SQLiteの暗号化のサポート

最近のFree Pascal Compiler(FPC)のバージョンでは(2012年3月に実装された)、SQLDBはSQLite3の拡張バージョンのいくつかをサポートしており、これによりSQLiteデータベースファイルをAESアルゴリズムを使用して暗号化することができる。暗号化キーを設定するには、パスワードプロパティを使用する。

Examples:

sqlite3backup

sqlite3backupは、FPCに提供されているユニットであり、Lazarusには含まれていないが、プログラムで使用することができる。これは、SQLite3のバックアップ/リストア機能を提供する。sqlite3backupは、SQLDBのsqlite3connを使用する。

Zeos

Zeos

SQLitePass

SqlitePass components. Status: unknown.

TSQLite3Dataset and TSQLiteDataset

TSQLiteDataset(sqlitedsユニット)およびTSQLite3Dataset(sqlite3dsユニット)パッケージも提供されてる。APIリファレンス、チュートリアルに関しては使用法は以下を参照のこと sqlite4fpc homepage

TSQLiteDatasetとTSQLite3Datasetは、それぞれ2.8.xおよび3.x.xのSQLiteデータベースにアクセスする、TDataSetの派生クラスである。新しいプロジェクトでは、SQLite 3.xが現在のバージョンであるため、TSQLite3Datasetを使用することが一般的である。

他のFPC/LazarusのSQLiteドライバやアクセス方法と比較した場合の主な利点と欠点は次のとおりである:

利点:

欠点:

上記の情報から、多くのユーザーはSQLDBやZeosを利用することが一般的である。これらのツールを利用することで、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

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

TSQLite3Dataset を使う

この節では、SQLite データベースへアクセスするために、どのように TSQLite2Dataset または TSQLite3Dataset コンポーネント を用いるかを詳しく説明しています。 Luiz Américo による luizmed(at)oi(dot)com(dot)br

要求事項

Lazarus のプロジェクトを作る前に、次の事を確認してください。:

使い方 (基本)

/components/sqlite にあるパッケージをインストールしてください。(ここにその方法があります。)

設計時に下記のプロパティをセットして下さい。

テーブルを作成する(Dataset)

コンポーネントアイコンをダブルクックするか、右マウスボタンのクリックで現れるポップアップメニューより、'Create Table' を選択します。 簡単な説明のあるテーブルエディタが表示されます。

下記は TSqliteDataset と TSqlite3Dataset でサポートされているすべてのフィールドの型です。

データを取得する

テーブルを生成した後か、以前作ったテーブルで作業する場合、Open 手続きでデータセットを開いて下さい。 SQL プロパティがセットされていない場合、すべてのフィールドのすべてのレコードを取得するためには、次のような1文をSQLにセットします。:

SQL := 'Select * from TABLENAME';

データファイル上に変更を適用する

ApplyUpdates 関数を使うために, データセットは、主キーの条件を満たす少なくとも1つのフィールドを持っていなければなりません。(値は、UNIQUE であり、NULL ではない必要があります。)

変更を適用するために2つの方法があります。

2つの条件のうち1つをセットしたら、以下を呼び出してください。

ApplyUpdates;

もし、両方の条件がセットされたら、主キーに相当するフィールドが更新の適用に使われます。

主キーではないフィールドを主キーに設定することは、ApplyUpdate がコールされたときにデータのロスを引き起こします。ですから、選択されたフィールドは、 Null でなく Unique であることを、それを使う前に確認してください。

Master/detail のサンプル

master/detail 関係の様々なサンプル (例えば、顧客と注文の間の関係):

注意事項

一般的なサンプルが、SVNディレクトリの fpc/fcl-db/src/sqlite にあります。

関連情報