Simple Delphi WrapperでSQLite3サンプル

準備

組み込みデータベースのSQLite3とDelphiで書かれたSQLite3のラッパーSimple Delphi Wrapperを利用して簡単な住所・郵便番号検索データベースを作成したいと思います。

• 開発環境：Boland Delph7 Professional（Pro以外の無料版でも多分大丈夫）
• 日本郵便からお好きな郵便番号データをダウンロードして下さい。
• Simple Delphi Wrapperをダウンロード。

SQLite組み込み方法（+Simple Delphi Wrapper）

※サンプルではすでに組み込まれています。

1. A simple Delphi wrapper for SQLite 3からSimple Delphi Wrapperをダウンロードして解凍してください。
2. 解凍したら、sqlite3.dllとSQLite3.pasとSQLiteTable3.pasをアプリケーションフォルダに配置します。

SQLite3（+Simple Delphi Wrapper）ポイント

• DLLを一個アプリケーションフォルダに配置するだけで非常に手軽に組み込める。
• 低機能ゆえに、非常に高速。シンプルな機能に絞ってあるので使いやすい。
• 複数プロセスから同じデータベース利用も可能。
• Firebird Embeddedとは違い、LANを介しても利用可能。
• SQLite3データベースやAPIの文字列の文字コードはUTF-8やUTF-16を利用しなければならない。（ShiftJISが使えない。）
• Simple Delphi WrapperもShiftJISに対応していない。※ShiftJIS対応版を作ってみました。

サンプルソース

作成フォーム

DBコンポーネントを利用してないので、リスト表示にはTListViewを使用しました。

実行フォーム

入力欄に住所の一部を入力すると、DB内から文字列が含まれている住所をインクリメンタルサーチします。

ソース

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, ComCtrls, StdCtrls, Menus, SQLiteTable3;

type
  PPostRec = ^TPostRec;
  TPostRec = record
    PostCode: string[7];
    Address: string[100];
  end;

  TForm1 = class(TForm)
    MainMenu1: TMainMenu;
    File1: TMenuItem;
    Open1: TMenuItem;
    DeleteTable1: TMenuItem;
    N1: TMenuItem;
    Exit1: TMenuItem;
    Panel1: TPanel;
    Label1: TLabel;
    LabeledEdit1: TLabeledEdit;
    OpenDialog1: TOpenDialog;
    Memo1: TMemo;
    ListView1: TListView;
    Splitter1: TSplitter;
    ProgressBar1: TProgressBar;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure DeleteTable1Click(Sender: TObject);
    procedure Open1Click(Sender: TObject);
    procedure ListView1Data(Sender: TObject; Item: TListItem);
    procedure LabeledEdit1Change(Sender: TObject);
  private
    { Private 宣言 }
    DBFile: string;
    SQLiteDB: TSQLiteDatabase;
    VirtualList: TList;
    procedure ClearTable;
    procedure ClearList;
    procedure ListUp;
    procedure Query(Filter: string = '');
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//リストのクリア
procedure TForm1.ClearList;
var i: Integer;
begin
  //何十万件とリストアップした後などは
  //それなりに時間のかかる処理になります。
  for i := VirtualList.Count-1 downto 0 do Dispose(VirtualList[i]);
  VirtualList.Clear;
end;

//POST_TABLE内容のクリア
procedure TForm1.ClearTable;
begin
  SQLiteDB.ExecSQL('DELETE FROM POST_TABLE;');
end;

procedure TForm1.DeleteTable1Click(Sender: TObject);
begin
  ClearTable;
  Query;
end;

procedure TForm1.FormCreate(Sender: TObject);
var sql: string;
begin
  VirtualList := TList.Create;
  DBFile := 'post.db';
//  DBFile := ':memory:';  //インメモリデータベースの場合

  //今回ここでのUTF8Encodeは必要ないですが、
  //マルチバイト文字を含むファイルパスが渡されることもあるため
  SQLiteDB := TSQLiteDatabase.Create(UTF8Encode(DBFile));
  if not SQLiteDB.TableExists('POST_TABLE') then begin
    sql := 'CREATE TABLE POST_TABLE ([POST_CODE] VARCHAR (7), ' +
                                    '[ADDRESS] VARCHAR (100));';
    SQLiteDB.ExecSQL(sql);
  end;
  Query;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  ClearList;
  SQLiteDB.Free;
  VirtualList.Free;
end;

//リストビュー内容の更新
procedure TForm1.ListUp;
begin
  ListView1.Items.Count := VirtualList.Count;
end;

//郵便番号データのインポート
procedure TForm1.Open1Click(Sender: TObject);
var DataList, RecList: TStringList; i, Count: Integer;
  postcode, address, sql: string; StartTime: Cardinal;
begin
  OpenDialog1.InitialDir := ExtractFileDir(Application.ExeName);
  if OpenDialog1.Execute then begin
    DataList := TStringList.Create;  //郵便番号データリスト
    RecList := TStringList.Create;   //郵便番号レコードリスト
    try
      try
        //トランザクションの開始
        SQLiteDB.BeginTransaction;  //これをしないとめちゃくちゃ遅い
        ClearTable;
        DataList.LoadFromFile(OpenDialog1.FileName);
        Count := DataList.Count;
        ProgressBar1.Max := Count;
        StartTime := GetTickCount;  //INSERT前開始タイムを取得
        for i := 0 to Count-1 do begin
          RecList.CommaText := DataList[i];
            //郵便番号(7桁)
            postcode := RecList[2];
            postcode := UTF8Encode(postcode);
            //都道府県名 + 市区町村名 + 町域名
            address := RecList[6] + RecList[7] + RecList[8];
            address := UTF8Encode(address);  //UTF8に変換
            {INSERT処理}
            sql := 'INSERT INTO POST_TABLE VALUES (''' +
                      postcode + ''', ''' + address + '''); ';
            SQLiteDB.ExecSQL(sql);

            ProgressBar1.StepIt;
        end;
        SQLiteDB.Commit;  //コミットでトランザクション終了
        Memo1.Lines.Add('');
        Memo1.Lines.Add(ExtractFileName(OpenDialog1.FileName) + 'のインポート');
        Memo1.Lines.Add('INSERT数 = ' + IntToStr(Count));
        Memo1.Lines.Add('総INSERT時間 = ' +
          IntToStr(GetTickCount - StartTime) + 'ms'); 
        Memo1.Lines.Add('');

        Query;  //リストの更新
      except
        SQLiteDB.Rollback;  //エラーが起きた場合はロールバック
      end;
    finally
      ProgressBar1.Position := 0;
      DataList.Free;
      RecList.Free;
    end;
  end;
end;


procedure TForm1.ListView1Data(Sender: TObject; Item: TListItem);
var p: PPostRec;
begin
  p := VirtualList[Item.Index];
  Item.Data := p;
  Item.Caption := p^.PostCode;
  Item.SubItems.Add(UTF8Decode(p^.Address));
end;

//
procedure TForm1.Query(Filter: string);
var SQLIteTable: TSQLIteTable; p: PPostRec; sql: string; StartTime: Cardinal;
begin
  ClearList;
  //↑のリストの削除は時間がかかる処理かつ
  //クエリ以外の処理のためここから速度計測。
  //全ての処理を計測したい場合は
  //↓の行の位置を変更してください。
  StartTime := GetTickCount;
  sql := 'SELECT * FROM POST_TABLE';
  //引数Filterが空文字以外の場合はWHERE句を追加
  if Filter <> '' then sql := sql + ' WHERE ' + Filter;
  SQLIteTable := SQLiteDB.GetTable(sql);
  with SQLIteTable do begin
    MoveFirst;
    while not EOF do begin
      New(p);
      
      //カラム名からデータを取得（どちらの方法でも可）
      p^.PostCode := FieldByName['POST_CODE'];
      p^.Address := FieldByName['ADDRESS'];
//      p^.PostCode := FieldAsString(FieldIndex['POST_CODE']);
//      p^.Address := FieldAsString(FieldIndex['ADDRESS']);

      VirtualList.Add(p);
      Next;
    end;
  end;
  ListUp;
  SQLIteTable.Free;  
  Memo1.Lines.Add('クエリ時間 = ' +
          IntToStr(GetTickCount - StartTime) + 'ms');
end;

procedure TForm1.LabeledEdit1Change(Sender: TObject);
begin
  //WHERE区以降を引数にクエリする
  Query('[ADDRESS] LIKE ' + AnsiQuotedStr(
    '%' + UTF8Encode(LabeledEdit1.Text) + '%', ''''));
end;

end.

速度

上のソースを元に以下の環境でインサートとクエリ速度を測ってみました。

実行環境

CPU:           Intel Core 2 Duo E6600 (Conroe-4M, B2)
               2400 MHz (9.00x266.7) @ 2383 MHz (9.00x264.8)
Memory:        2048 MBytes @ 331 MHz
Graphics:      Intel 82945G Integrated Graphics Controller [AsusTek]
               Intel GMA950, 128 MB 
OS:            Microsoft Windows XP Professional Build 2600

同じ動作を３回ずつ計測します。

INSERT処理

東京都の全郵便番号データ（3580件）をINSERT
総INSERT時間 = 1422ms
総INSERT時間 = 1500ms
総INSERT時間 = 1578ms

全国の全郵便番号データ（122635件）をINSERT
総INSERT時間 = 54078ms
総INSERT時間 = 55703ms
総INSERT時間 = 58625ms

クエリ

東京都の全郵便番号データ（3580件）の住所から「亀有」を含むものを抽出
クエリ時間 = 0ms
クエリ時間 = 0ms
クエリ時間 = 0ms

全国の全郵便番号データ（122635件）の住所から「亀有」を含むものを抽出
クエリ時間 = 93ms
クエリ時間 = 78ms
クエリ時間 = 78ms

ダウンロード