SQLiteの最近の記事

2008年4月 5日

DBIx::Simpleを利用してSQLliteを使用した際に、Web実行でSelectはできるがUpdateでエラーになる場合

ピンポイントすぎますが、1年後に同じコトをしでかしそうなのでメモ代わりに

エラー内容

DBI error: unable to open database file(1) at dbdimp.c line 402


エラー理由


sqliteのdbを配置しているフォルダに書き込み権源がないため発生している。
dbファイルと併せてフォルダにも書き込み権限を与える必要がある。

chmod 707 data/
chmod 606 data/hoge.db

linuxの知識の基礎知識が弱すぎて思わぬところではまります。

2007年9月 4日

PerlでSQLiteとCSVのどちらを使ったほうが検索が速いかを検証

目的



  • PerlでSQLiteとCSVのどちらを使ったほうが検索が速いかを検証する

検証方法



  • データの最後尾にあるレコードを検索する際の速度を比べる。

  • DBの検索項目にはIndexを追加した。

結果



  • 1秒間に実行できる回数(回数が多いほど高速)






    検索対象データ件数CSVSQLite
    100件3333回714回
    1,000件333回666回
    10,000件33回714回
    100,000件3回714回


  • SQLiteはIndexで検索を行っているので、検索速度がデータ件数に依存しなかった。

  • CSVの読み込みはデータの件数分順次リードを行っている為、検索速度がデータ件数に依存した。

  • データが100件の場合は、CSVのほうが速かった。

  • データが1,000件以上の場合は、SQLiteのほうが速かった。

結論



  • 扱うデータが1,000件以上になってきた場合はSQLite等のDBを使うことを考えたほうが良いかもしれない。
    データを正規化できるメリットやUPDATEの便利さも大きいし(CSVだとUPDATEがぐだぐだに)

検証データ・コード・結果一式



  • 検証に使用したデータ:日本郵政公社(全国郵便番号)
  • 検証で使用したプログラムコード

    #!/usr/local/bin/perl --
    use strict;
    use warnings;
    use Benchmark qw(timethese cmpthese);

    my $keyZipCode = '"9071801"';
    my $result = timethese(100,{
    'funcCsvRead' => 'funcCsvRead',
    'funcDbRead' => 'funcDbRead',
    });
    cmpthese($result);

    sub funcCsvRead{
    open(FILE,"< ./KEN_ALL.CSV") || die 'file open error.';
    while (){
    my(undef,undef,$new_zip) = split(/,/);
    if ($keyZipCode eq $new_zip) {
    # ヒット後の処理
    last;
    }
    }
    close(FILE);
    }

    sub funcDbRead{
    use DBIx::Simple;
    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;
    my $result;

    $result = $db->query('select * from zip where new_zip = ?', $keyZipCode) || die $db->error;
    while ($result->into(my(undef, undef, $new_zip))) {
    # ヒット後の処理
    last;
    }

    }



  • ベンチマーク結果

    データが100件の場合
    Benchmark: timing 100 iterations of funcCsvRead, funcDbRead...
    funcCsvRead: 0 wallclock secs ( 0.03 usr + 0.00 sys = 0.03 CPU) @ 3333.33/s (n=100)
    (warning: too few iterations for a reliable count)
    funcDbRead: 0 wallclock secs ( 0.01 usr + 0.13 sys = 0.14 CPU) @ 714.29/s (n=100)
    (warning: too few iterations for a reliable count)
    Rate funcDbRead funcCsvRead
    funcDbRead 714/s -- -79%
    funcCsvRead 3333/s 367% --

    データが1,000件の場合
    Benchmark: timing 100 iterations of funcCsvRead, funcDbRead...
    funcCsvRead: 0 wallclock secs ( 0.27 usr + 0.03 sys = 0.30 CPU) @ 333.33/s (n=100)
    (warning: too few iterations for a reliable count)
    funcDbRead: 0 wallclock secs ( 0.01 usr + 0.14 sys = 0.15 CPU) @ 666.67/s (n=100)
    (warning: too few iterations for a reliable count)
    Rate funcCsvRead funcDbRead
    funcCsvRead 333/s -- -50%
    funcDbRead 667/s 100% --

    データが10,000件の場合
    Benchmark: timing 100 iterations of funcCsvRead, funcDbRead...
    funcCsvRead: 3 wallclock secs ( 2.60 usr + 0.37 sys = 2.97 CPU) @ 33.67/s (n=100)
    funcDbRead: 0 wallclock secs ( 0.02 usr + 0.12 sys = 0.14 CPU) @ 714.29/s (n=100)
    (warning: too few iterations for a reliable count)
    Rate funcCsvRead funcDbRead
    funcCsvRead 33.7/s -- -95%
    funcDbRead 714/s 2021% --

    データが100,000件の場合
    Benchmark: timing 100 iterations of funcCsvRead, funcDbRead...
    funcCsvRead: 29 wallclock secs (18.93 usr + 10.62 sys = 29.55 CPU) @ 3.38/s (n=100)
    funcDbRead: 0 wallclock secs ( 0.01 usr + 0.13 sys = 0.14 CPU) @ 714.29/s (n=100)
    (warning: too few iterations for a reliable count)
    Rate funcCsvRead funcDbRead
    funcCsvRead 3.38/s -- -100%
    funcDbRead 714/s 21007% --

PerlでSQLiteを操作する

PerlからSQLiteを操作する方法について

DBIx::Simpleモジュールをインストールする。



  • 今回は多機能を求めていないので、DBに接続するラッパーはDBIx::Simpleを利用します。

    % perl -MCPAN -e 'install DBIx::Simple'

  • データベースの内容
    
    

    BEGIN TRANSACTION;
    CREATE TABLE spell (id integer primary key, name text, message text);
    INSERT INTO spell VALUES(1,'ホイミ','HPを少し回復する。');
    INSERT INTO spell VALUES(2,'ベホイミ','HPをかなり回復する。');
    INSERT INTO spell VALUES(3,'ベホマ','HPを全回復する。');
    COMMIT;

データベースへの接続



  • サンプルソース

    #!/usr/local/bin/perl --
    use DBIx::Simple;
    use strict;
    use warnings;

    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;

  • dbname=の箇所にデータベースファイルのパスを指定します。

レコードを取得(SELECT)する

  • spell テーブルの全レコードの取得と条件を指定した取得を行います。
  • サンプルソース
    
    

    #!/usr/local/bin/perl --
    use DBIx::Simple;
    use strict;
    use warnings;

    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;
    my $result;

    print "\n■spell テーブルの内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

    print "\n■spell テーブルの内容で name にベホが含まれる内容を表示する\n";
    $result = $db->query('select id, name, message from spell where name like ?', '%ベホ%') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

  • 実行結果
    
    

    ■spell テーブルの内容を全て表示する
    ホイミ, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。

    ■spell テーブルの内容で name にベホが含まれる内容を表示する
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。

レコードを追加(INSERT)する



  • spell テーブルに対して1レコード追加を行います。

  • サンプルソース

    #!/usr/local/bin/perl --
    use DBIx::Simple;
    use strict;
    use warnings;

    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;
    my $result;

    print "\n■spell テーブル(追加前)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

    $db->query('insert into spell(name, message) values(?, ?)', 'ベホマズン', '全員のHPを全回復する。') || die $db->error;

    print "\n■spell テーブル(追加後)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

  • 実行結果
    
    

    ■spell テーブル(追加前)の内容を全て表示する
    ホイミ, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。

    ■spell テーブル(追加後)の内容を全て表示する
    ホイミ, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。
    ベホマズン, 全員のHPを全回復する。

レコードを更新(UPDATE)する



  • spell テーブルに対して1レコード更新を行います。

  • サンプルソース

    #!/usr/local/bin/perl --
    use DBIx::Simple;
    use strict;
    use warnings;

    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;
    my $result;

    print "\n■spell テーブル(更新前)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

    $db->query('update spell set name = ? where name = ?', 'ケアル', 'ホイミ') || die $db->error;

    print "\n■spell テーブル(更新後)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

  • 実行結果
    
    

    ■spell テーブル(更新前)の内容を全て表示する
    ホイミ, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。
    ベホマズン, 全員のHPを全回復する。

    ■spell テーブル(更新後)の内容を全て表示する
    ケアル, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。
    ベホマズン, 全員のHPを全回復する

レコードを削除(DELETE)する



  • spell テーブルに対して1レコード削除を行います。

  • サンプルソース

    #!/usr/local/bin/perl --
    use DBIx::Simple;
    use strict;
    use warnings;

    my $db = DBIx::Simple->connect('dbi:SQLite:dbname=./mydb.db3') || die DBIx::Simple->error;
    my $result;

    print "\n■spell テーブル(削除前)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

    $db->query('delete from spell where name = ?', 'ケアル') || die $db->error;

    print "\n■spell テーブル(削除後)の内容を全て表示する\n";
    $result = $db->query('select id, name, message from spell') || die $db->error;
    while ($result->into(my(undef, $name, $message))) {
    print " $name, $message\n";
    }

  • 実行結果
    
    

    ■spell テーブル(削除前)の内容を全て表示する
    ケアル, HPを少し回復する。
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。
    ベホマズン, 全員のHPを全回復する。

    ■spell テーブル(削除後)の内容を全て表示する
    ベホイミ, HPをかなり回復する。
    ベホマ, HPを全回復する。
    ベホマズン, 全員のHPを全回復する。

他参考になるサイト



2007年9月 3日

GUIでSQLiteを操作する

WindowsのGUIからSQLiteを操作する方法について

6つぐらいのGUIツールを試してみて、SQLite Database Browserが一番使いやすかったので、
ここではSQLite Database Browserを利用した場合の説明を記載します。

SQLite Database Browserが使いやすかった理由としては
・日本語(utf-8)が利用できる
・安定している
・直感的に操作がわかりやすい
といったところです。
欲をいえばExcelのようなグリッドでデータを編集したいですね。

副作用:
とてもお手軽にSQLが実行できる環境が構築できるので、
基本情報技術者試験とかのSQLの勉強にも役立てることができます。

・SQLiteのGUIツールをインストールする。


・データベースの作成


・テーブルを作成する


・レコードを追加(INSERT)する


・レコードを更新(UPDATE)する


・レコードを削除(DELETE)する


・他情報

SQLiteのGUIツールをインストールする。

http://sourceforge.net/projects/sqlitebrowser/ へアクセスする。
・Download SQLite Database Browser のボタンを選択する。
・sqlitebrowser-1.3-win.zip をダウンロードする。(1.3は20070903時点での最新)
・ダウンロードしたバイナリを解凍する。

データベースの作成

・SQLite Database Browser.exeを実行する。
・青丸のアイコンを選択する。
 sqlite001.gif
・ファイル名に mydb.db3 と入力して、保存ボタンを選択する。
・下記のテーブル作成の入力につづく

テーブルを作成する

・テーブル spell を作成する為、下記の入力を行う。
 sqlite003.gif
・Createボタンを選択する。
・テーブルが追加されたことを確認する。
 sqlite004.gif

・データベースへの変更を保存する
・青丸のアイコンを選択する。
 sqlite005.gif

・プログラムを終了する。
・mydb.db3というファイル名が追加されている。
これがSQLiteのデータベースファイル。
 sqlite002.gif


レコードを追加(INSERT)する

・SQLite Database Browser.exeを実行する。
・青い丸のアイコンを選択する。
 sqlite006.gif
・さきほど作成したmydb.db3を開く
・Browse Dataのタブを選択して、青い丸のNew Recordのボタンを選択する。
 sqlite007.gif
・入力したい値のセルでダブルクリックして入力画面を開く。
・入力画面に値を入力して Apply Changes のボタンを選択する。

・レコードが追加されていることを確認する。
・Execute SQLのタブを選択して、SQL stringの欄に 「select * from spell」と入力する。
・Execute query のボタンを選択する。
 sqlite008.gif

レコードを更新(UPDATE)する

・Browse Dataのタブを選択する。
・入力したい値のセルでダブルクリックして入力画面を開く。
・入力画面に値を入力して Apply Changes のボタンを選択する。
・表示されている内容を絞りたい場合は虫めがねのボタンを選択して
 表示条件を入力して絞る

レコードを削除(DELETE)する

・Browse Dataのタブを選択する。
・削除したいレコードを選択する。
・Delete Record のボタンを選択する。

他情報

・利用できるSQL一覧:http://www.sqlite.org/lang.html
・メニュー>File>Importで値のインポートやSQLファイルの実行が可能
・メニュー>File>Exportで値のエクスポートやDDLの出力が可能

コマンドラインでSQLiteを操作する

LinuxのターミナルからSQLiteをコマンドラインで操作する方法について

・SQLiteのコマンドラインツールをインストールする。


・データベースの作成


・テーブルを作成する


・レコードを追加(INSERT)する


・レコードを更新(UPDATE)する


・レコードを削除(DELETE)する


・他情報

SQLiteのコマンドラインツールをインストールする。

http://www.sqlite.org/ へアクセスする。
・右上にある download のページを選択する。
・sqlite3-3.4.2.bin.gz をダウンロードする。(3-3.4.2は20070903時点での最新)
・ダウンロードしたバイナリを解凍する。

#解凍前

% ls
sqlite3-3.4.2.bin.gz

#解凍

% gunzip ./sqlite3-3.4.2.bin.gz

#解凍後

% ls
sqlite3-3.4.2.bin

データベースの作成


#作成



% ./sqlite3-3.4.2.bin mydb.db3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>

#終了する

sqlite> .q

#確認

% ls
mydb.db3  sqlite3-3.4.2.bin

mydb.db3というファイル名が追加されている。
これがSQLiteのデータベースファイル。

テーブルを作成する


#さきほど作成したmydb.db3へ接続する



% ./sqlite3-3.4.2.bin mydb.db3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>

#テーブル spell を作成する

sqlite> CREATE TABLE spell (id integer primary key, name text, message text);

#テーブルが追加されていることを確認する

sqlite> SELECT name FROM sqlite_master WHERE type='table';
spell

#終了する

sqlite> .q

レコードを追加(INSERT)する


#さきほど作成したmydb.db3へ接続する



% ./sqlite3-3.4.2.bin mydb.db3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>

#テーブル spell に2レコード追加する(idは自動採番を利用)

sqlite> INSERT INTO spell (name) VALUES ('ホイミ');
sqlite> INSERT INTO spell (name) VALUES ('ベホイミ');

#レコードが追加されていることを確認する。

sqlite> select * from spell;
1|ホイミ|
2|ベホイミ|

#終了する

sqlite> .q

レコードを更新(UPDATE)する


#さきほど作成したmydb.db3へ接続する



% ./sqlite3-3.4.2.bin mydb.db3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>

#テーブル spell のid=1を更新する

sqlite> UPDATE spell SET message='HPを少し回復する。' WHERE id=1;

#id1のレコードが更新されていることを確認する。

sqlite> select * from spell;
1|ホイミ|HPを少し回復する。
2|ベホイミ|

#終了する

sqlite> .q

レコードを削除(DELETE)する


#さきほど作成したmydb.db3へ接続する



% ./sqlite3-3.4.2.bin mydb.db3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>

#テーブル spell のid=2を削除する

sqlite> DELETE FROM spell WHERE id=2;

#id2のレコードが削除されていることを確認する。

sqlite> select * from spell;
1|ホイミ|HPを少し回復する。

#終了する

sqlite> .q

他情報

利用できるSQL一覧:http://www.sqlite.org/lang.html

SQLiteを利用する

ここではSQLiteを利用するにあたってのメモを公開します。
間違いがありましたらご指摘ください。

バックボーン:
検索のシステムでCSVでデータを扱っていたが件数が増えてきた(3000件ほど)
・RDBを利用したほうが処理の効率がよいかもしれない
・CSVとSQLiteの速度の比較検証してみよう

検証のデータベースにSQLiteを選んだ理由:
・レンタルサーバの場合、DB(主にMySQL)が利用できてもWebサーバとDBサーバが別で、
 負荷ピーク時にDBサーバが死んでいて使い物にならない印象がある。
 SQLiteはWebサーバに配置する為そのようなことはない。
・インストールがお手軽である(1ファイルをコピーするのみでインストール完了)
・リッチなデータベース機能は不要である

私の開発環境:
・SQLiteのバージョン:3.*
・DB配置:Debian(coLinux)、SambaでWindowsと共有
・GUI操作:Windows
・エンコーディング:utf-8