ウィキのコピーミラー

これは pascal.chat のウィキのミラーです。編集、履歴、ログインはそちらで行います。

SQLite/ja

English · Español · Français · Polski · Русский · 中文（中国） · 日本語

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 と 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ドライバやアクセス方法と比較した場合の主な利点と欠点は次のとおりである:

利点:

欠点:

Note: 上記の情報から、多くのユーザーはSQLDBやZeosを利用することが一般的である。これらのツールを利用することで、SQLiteデータベースへの簡単な接続や操作が可能になる。

SQLiteでSQLdbコンポーネントを使用する

これらの手順は、特にSQLiteデータベースのSQLDBコンポーネント、特にTSQLite3Connectionの利用に焦点を当てている。SQLDBコンポーネントやそれらの使用方法についてより包括的に理解するには、SqlDBHowto/jaを参照のこと。これには、LazarusのSQLDBコンポーネントに関する一般的な概要や有用な情報が含まれている。

SQLdb_Tutorial1/jaでは、SQLite/SQLDB(およびFirebird/SQLDB、PostgreSQL/SQLDBなど、SQLDBがサポートするほとんどのRDBMS)向けに書かれたGUIデータベース対応プログラムのチュートリアルを見ることができる。

LazarusのSQLdbタブから3つのコンポーネントの組み合わせを使用する: TSQLite3Connection、TSQLTransaction、TSQLQueryである。TSQLQueryはTDatasetとして機能し、最も単純な場合にはテーブルの1つを表します。単純化のために、既存のSQLiteデータベースファイルがすでに存在しており、新しいものを作成する必要がないことを確認してください。 TSQLite3Connectionは、自分で宣言したい場合やFreePascalで作業している場合は、sqlite3connユニットで見つけることができる。

通常通り、これらの3つのコンポーネントは互いに接続される。TSQLQueryでは、DatabaseとTransactionのプロパティを設定する。TSQLTransactionでは、Databaseのプロパティを設定する。TransactionとConnectionのコンポーネントではあまりすることはない。興味深いことのほとんどは、TSQLQueryで行われます。以下のようにコンポーネントを設定する:

TSQLite3Connection:

注意: フルライブラリパスを設定するには(Linux/OSXのアプリケーションディレクトリなど、OSがそれを見つけられない場所にsqliteのdll/so/dylibを配置した場合)、SQLiteLibraryNameプロパティを設定できます(任意の接続が確立される前に、たとえばメインフォームのOnCreateイベント内で)以下のように：

pascal
SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

sql
SELECT * FROM foo

上記の設定がすべて正しく行われている場合、TSQLQueryを他の任意のTDatasetと同様に使用できるはずである。プログラムでデータを操作するか、フォームにTDatasouceを配置し、それをTSQLQueryに接続してから、TDBGridなどのデータコントロールを使用することができる。

データベースを作る

TSQLite3Connection.CreateDBメソッドは親クラスから継承されたもので実際には何も行わない。まだファイルが存在しない場合にデータベースを作成するには、次の例のようにテーブルデータを書き込む必要がある。 （Lazarus 1.3以降で提供されているsqlite_encryption_pragmaの例から抜粋されたコード）

pascal
var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // 開始時はコネクションが閉じていることを保証する

  try
    // このデータベースは初めて作られているので
    // ファイルが既にあるかどうか確かめる
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // データベースとテーブルを作る
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // 「DATA」という名前の新しいデータベースを設定する
        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);');

        // DATAテーブルのidに基づいてインデクスを作る
        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;

ユーザー定義の照合順序を作成する

pascal
// 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;

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

// SQLite3 APIを使用して照合順序を登録する(sqlite3dynユニットが必要):
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// もしくはTSQLite3Connectionのメソッドを使用:
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// ここでSQL様に大文字小文字を区別しない比較を用いることができる:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'

// これはLIKE演算子では機能しない
// また、LIKE演算子をサポートするためには、sqlite3_create_function()を用いてデフォルトのLIKEをオーバーロードしなければならない
// http://www.sqlite.org/lang_corefunc.html#like

ユーザー定義関数を作る

pascal
// デフォルトの提供されたLOWER()関数をユーザーにオーバーロードする例
// このデモを実行するにはuses節に 'sqlite3dyn' と 'ctypes'を記述する必要がある
// そして、定数'SQLITE_DETERMINISTIC'の値を$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;

// SQLite3 APIを用いてLOWER()関数を登録する(sqlite3dynユニットが必要である):
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);

SQLite3 と 日付

日付を文字列として保存している場合(たとえばBETWEEN関数を使用して)、比較は常に文字列の比較になることを覚えておくこと。したがって、比較結果は日付値の保存方法に依存する。

UTCの代わりにローカルタイムをデフォルトに用いる

CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP は現在のUTC日付および、もしくは時刻を返す。ローカルの日付、および、または時刻を用いるために以下のようにする:

text
 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 と SQLite のトラブルシューティング

pascal
interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);

//このプロシージャはデータセットをリフレッシュし、カーソルを最後のレコードに配置する
//データセットが自動インクリメントの主キーでソートされていることが保証されていない場合に使用する必要がある
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //データベースに最後に挿入されたIDを取得
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  //ここで、それぞれの行に戻る
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //それぞれの行に戻り、リフレッシュする
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
//このプロシージャはデータセットをリフレッシュし、カーソルを最後のレコードに配置する
//データセットが自動インクリメントの主キーでソートされていることが保証されていない場合に使用する必要がある
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //危険!
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //もしデータセットがプライマリキーでソートされているなら
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //もしデータセットがプライマリキーでソートされていることが不確かなら
end;

トランザクションとは別に行われるべきVacuum と 他のオペレーション

SQLDBは常に接続を必要とするようだが、PragmaやVacuumなどの一部の操作はトランザクションの外で行う必要がある。トランザクションを終了し、必要な操作を実行し、再びトランザクションを開始するトリックを使用する(これにより、SQLDBが混乱しないようする)。

pascal
  // ペンディング中のすべてのオペレーションをコミットするか、"fresh" sqlconnectionをもちいる
  Conn.ExecuteDirect('End Transaction');  // SQLdbによって開始されたトランザクションを終える
  Conn.ExecuteDirect('Vacuum');
  Conn.ExecuteDirect('Begin Transaction'); //SQLdbが用いるためトランザクションを開始

TSQLite3Dataset を使う

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

要求事項

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

使い方 (基本)

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

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

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

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

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

データを取得する

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

pascal
SQL := 'Select * from TABLENAME';

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

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

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

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

pascal
ApplyUpdates;

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

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

Master/detail のサンプル

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

注意事項

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

関連情報

From the Free Pascal wiki, page "SQLite/ja", as it stood on 2024-03-26 after a change by Ariben, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.

第2版、最終更新：WikiArchive 。