SQLiteの組込方法

h.omae @pigeon6

そういえばsqliteの話まだしてなかった。

h.omae @pigeon6

アプリケーションにsqliteを統合する場合の要点を幾つか書いておきます。

h.omae @pigeon6

まずは、当然ですがamalgamationされたバージョンを使う事。ソース１つ、ヘッダ１つで済みとてもお手軽です。

TAM! @y_tamura

@pigeon6 sqlite最高！！！！fopenは死ぬと良いのに！！！と思う最近です。うんがうぐ。

h.omae @pigeon6

ちなみに余談ですがamalgamationされたファイルをVisualStudio2003等の古いコンパイラでビルドすると、「行数が65535行を超えました。これ以上カウントできません」という珍しいワーニングを見る事が出来ます。

TAM! @y_tamura

プログラマはもっとSQLとか勉強した方が良い、という気がするんだけど、そうでもない空気を感じて凹む

h.omae @pigeon6

まずsqliteの最初の落とし穴ですが、sqliteは基本は(少なくともデータをファイルに保存する場合)ファイル化されたデータベース(hoge.db)を開き、そこに対して処理をする構造です。

h.omae @pigeon6

つまり、複数のプロセスが同じdbファイルを開いて処理する事を可能にしています。これはどういうことかというと、insertだのupdateは、１処理ごとにトランザクション化され、ディスクに同期されます。

h.omae @pigeon6

かいつまんで言うと、インサート処理やアップデート処理のようなディスクに書き込む操作は、Begin/Commit文をつかって１つのトランザクションにまとめておかないと、メチャクチャ時間を食います。sqliteは、アップデート処理は何でもトランザクション化すること！

h.omae @pigeon6

たぶんこれが最初の落とし穴です。

h.omae @pigeon6

あと、sqliteはマルチスレッドで処理可能なシステムにはなっていますが、あるスレッドで開いたデータベースは、同じスレッドで処理される事を期待します。つまり、同じDBを複数のスレッドから触りまくる事は出来ません。設計時にこの点を頭にたたっこんでおきましょう。

h.omae @pigeon6

ちなみに、トランザクションを使う場合と使わない場合の速度差ですが、僕の例で言うと、使わない＝６０秒、使う＝0.19秒 くらいの差がありました。

@pgitohshu

なんか通常のDBでやられる事にデメリットがあると萎える、、RT @pigeon6: あと、sqliteはマルチスレッドで処理可能なシステムにはなっていますが、あるスレッドで開いたデータベースは、同じスレッドで処理される事を期待します。つまり、同じDBを複数のスレッドから触りまくる

TAM! @y_tamura

@pigeon6 トランザクションひどすｗ sqliteはマルチスレッドだととたんに重くなりますよね……。

h.omae @pigeon6

次に、sqliteはあんまり複雑なSQL文はサポートしていません（外部結合系）。結合系は左外部結合のみのサポートです。

h.omae @pigeon6

アプリケーションにsqliteを統合したい場合、幾つかの動機があると思います。データ構造の柔軟化、検索性能の向上＋柔軟化などが上げられると思います。テーブルの設計は、私的には、KISS原則(keep it simple & stupid)をここでも推奨したい。

h.omae @pigeon6

基本方針として、一つのデータ集合は一つのテーブルで持ち、重複は上等、くらいの気持ちで作る事をオススメします。データが新しく必要になったら、カラムをバンバン増やすくらいの勢いでやった方がいいでしょう。

h.omae @pigeon6

あと、忘れてはならないのが、dbにファイル形式のバージョン番号を保存するテーブルを一つ作っておく事です。DBを開いたら、まずこのテーブルを検索してバージョン番号を確認することで、上位互換性や下位互換性、DBのアップグレード処理などを大変書きやすくなります。

えぴくす @epi_x

sqlite は、シングルスレッドだけれども軽くて速い組み込みDBなことに価値があると思っているので、その仕様に違和感はないです。複数プロセスからの同時オープンなんて考えなくてもいいのに、というくらい。

h.omae @pigeon6

僕もコレでOKだと思っています。ただ、知っておく必要があるだけです。 RT @epics_jp: sqlite は、シングルスレッドだけれども軽くて速い組み込みDBなことに価値があると思っているので、その仕様に違和感はないです。複数プロセスからの同時オープンなんて考えなくて...

h.omae @pigeon6

願わくば、db単位ではなくテーブル単位でスレッドへの貼り付けが出来ると、もうすこし楽&便利になりそうな気がしますが・・。

h.omae @pigeon6

あと、当然の当然ですが、インデックスは作っておきましょう。プライマリキーにはデフォルトでインデックスが作られると思いましたが、とにかく、検索するものはインデックスです。また、TEXTフィールドへの全文検索は量が多くなる事を踏まえてなるべくスレッド化しましょう。

h.omae @pigeon6

最後に、仮想リストのように、10000個のうちの20個を順次表示するような場合の対応です。sqliteというかSQLの話ですが、select ほげほげ limit 20 offset 1300 のように、limitとoffsetを使って対象を絞りましょう。

h.omae @pigeon6

意外とoffsetが知られていないので、念のため書いておきました。（このあたりはSQLの方言もあるし）

h.omae @pigeon6

他にも自分の思うsqliteのいいところは、データが何かおかしいと思ったら、中身をダンプしたり精査したりするための対話的なツールをすぐに作れる事です。これは本当にすばらしくて、変な事がおきたらファイルをもらってきて、すぐに調べられる。